using IWareDataAccess.EF;
using IWareDataAccess.Entity.Summary;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace IWareDataAccess.Summary
{
   public  class SummaryStatisticsSqlFunc
    {
        #region 首页数据统计
       /// <summary>
       /// 获取器具的出入库数量
       /// </summary>
       /// <param name="startTime"></param>
       /// <param name="endTime"></param>
       /// <param name="msg"></param>
       /// <returns></returns>
        public static HomeBarEntity GetHomeBarContainerData(string startTime, string endTime, out string msg)
        {
            try
            {
                msg = "";
                using (Model edm = new Model())
                {
                    HomeBarEntity hbe = new HomeBarEntity();
                    string sql = @"	select A.TYPE as taskType,count(distinct A.TASKID) count,CONVERT(VARCHAR(10),A.CREATETIME,120)time,'1' as type   from  [dbo].[TASK_RECORD] A left join TASK_TASK B on A.TASKID=B.ID
			where (A.TYPE=1 or A.TYPE=2)  and A.ITEMID is not null and B.TASKSTATUS=N'完成'
			and A.CREATETIME>='{0}' and A.CREATETIME<='{1}'
			group by TYPE,CONVERT(VARCHAR(10),A.CREATETIME,120) union 
			select A.TYPE as taskType,count(distinct A.TASKID) count,CONVERT(VARCHAR(10),A.CREATETIME,120)time,'2' as type   from  [dbo].[TASK_RECORD] A left join TASK_TASK B on A.TASKID=B.ID
			where (A.TYPE=1 or A.TYPE=2) and   A.ITEMID is null and B.TASKSTATUS=N'完成'
			and A.CREATETIME>='{2}' and A.CREATETIME<='{3}'
			group by TYPE,CONVERT(VARCHAR(10),A.CREATETIME,120)";
                    sql = string.Format(sql, startTime, endTime, startTime, endTime);
                    List<SummaryHomeEntity> dalst = edm.Database.SqlQuery<SummaryHomeEntity>(sql).ToList();
                    List<string> timelst = new List<string>();
                    List<string> timelstNoYear = new List<string>();
                   string dt = DateTime.Parse(startTime).ToString("yyyy-MM-dd");
                   string edt = DateTime.Parse(endTime).AddDays(1).ToString("yyyy-MM-dd");
                   while (dt != edt) 
                   {
                       timelst.Add(dt);
                       timelstNoYear.Add(dt.Substring(5));
                       dt = DateTime.Parse(dt).AddDays(1).ToString("yyyy-MM-dd");
                   }
                        List<int> dt1 = new List<int>();
                        List<int> dt2 = new List<int>();
                        List<int> dt3 = new List<int>();
                        List<int> dt4 = new List<int>();
                        if (timelst != null && timelst.Count > 0)
                        {
                            timelst.ForEach(x =>
                            {
                                var d1 = dalst.FirstOrDefault(y => y.time == x && y.taskType == 1 && y.type == "2");
                                if (d1 != null)
                                {
                                    dt1.Add(d1.count);
                                }
                                else
                                {
                                    dt1.Add(0);
                                }
                                var d2 = dalst.FirstOrDefault(y => y.time == x && y.taskType == 1 && y.type == "1");
                                if (d2 != null)
                                {
                                    dt2.Add(d2.count);
                                }
                                else
                                {
                                    dt2.Add(0);
                                }
                                var d3 = dalst.FirstOrDefault(y => y.time == x && y.taskType == 2&&y.type=="2");
                                if (d3 != null)
                                {
                                    dt3.Add(d3.count);
                                }
                                else
                                {
                                    dt3.Add(0);
                                }
                                var d4 = dalst.FirstOrDefault(y => y.time == x && y.taskType == 2 && y.type == "1");
                                if (d4 != null)
                                {
                                    dt4.Add(d4.count);
                                }
                                else
                                {
                                    dt4.Add(0);
                                }

                            });
                        }
                        hbe.data = new List<Date>();
                        hbe.xdata = timelstNoYear;
                        hbe.legend = new List<string>() { "空器具入库", "带件器具入库","空器具出库", "带件器具出库"  };
                        Date dd1= new Date() { name = "空器具入库" };
                        dd1.xdata = new List<int>();
                        dd1.xdata.AddRange(dt1);
                        hbe.data.Add(dd1);

                        Date dd2 = new Date() { name = "带件器具入库" };
                        dd2.xdata = new List<int>();
                        dd2.xdata.AddRange(dt2);
                        hbe.data.Add(dd2);

                        Date dd3 = new Date() { name = "空器具出库" };
                        dd3.xdata = new List<int>();
                        dd3.xdata.AddRange(dt3);
                        hbe.data.Add(dd3);

                        Date dd4 = new Date() { name = "带件器具出库" };
                        dd4.xdata = new List<int>();
                        dd4.xdata.AddRange(dt4);
                        hbe.data.Add(dd4);
                    
              
                    return hbe;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new HomeBarEntity();
            }
        }
        /// <summary>
        /// 获取零件的出入库数量
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public static HomeBarEntity GetHomeBarItemData(string startTime, string endTime, out string msg)
        {
            try
            {
                msg = "";
                using (Model edm = new Model())
                {
                    HomeBarEntity hbe = new HomeBarEntity();
                    string sql = @"	select A.TYPE as taskType,SUM(A.ITEMCOUNT) count,CONVERT(VARCHAR(10),A.CREATETIME,120)time,'1' as type   from  [dbo].[TASK_RECORD] A left join TASK_TASK B on A.TASKID=B.ID
			where (A.TYPE=1 or A.TYPE=2)  and A.ITEMID is not null and B.TASKSTATUS=N'完成'
			and A.CREATETIME>='{0}' and A.CREATETIME<='{1}'
			group by TYPE,CONVERT(VARCHAR(10),A.CREATETIME,120)";

                    sql = string.Format(sql, startTime, endTime);
                    List<SummaryHomeEntity> dalst = edm.Database.SqlQuery<SummaryHomeEntity>(sql).ToList(); ;
                    List<string> timelst = new List<string>();
                    List<string> timelstNoYear = new List<string>();
                   string dt = DateTime.Parse(startTime).ToString("yyyy-MM-dd");
                   string edt = DateTime.Parse(endTime).AddDays(1).ToString("yyyy-MM-dd");
                   while (dt != edt) 
                   {
                       timelst.Add(dt);
                       timelstNoYear.Add(dt.Substring(5));
                       dt = DateTime.Parse(dt).AddDays(1).ToString("yyyy-MM-dd");
                   }
                        List<int> dt1 = new List<int>();
                        List<int> dt2 = new List<int>();
                        if (timelst != null && timelst.Count > 0)
                        {
                            timelst.ForEach(x =>
                            {
                                var d1 = dalst.FirstOrDefault(y => y.time == x && y.taskType == 1);
                                if (d1 != null)
                                {
                                    dt1.Add(d1.count);
                                }
                                else
                                {
                                    dt1.Add(0);
                                }
                                var d2 = dalst.FirstOrDefault(y => y.time == x && y.taskType == 2);
                                if (d2 != null)
                                {
                                    dt2.Add(d2.count);
                                }
                                else
                                {
                                    dt2.Add(0);
                                }

                            });
                        }
                        hbe.xdata = timelstNoYear;
                        hbe.legend = new List<string>() { "入库", "出库" };
                        hbe.data = new List<Date>();
                         Date dd1 = new Date() { name = "入库" };
                        dd1.xdata = new List<int>();
                        dd1.xdata.AddRange(dt1);
                        hbe.data.Add(dd1);
                         Date dd2 = new Date() { name = "出库" };
                        dd2.xdata = new List<int>();
                        dd2.xdata.AddRange(dt2);
                        hbe.data.Add(dd2);
                      
                    return hbe;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new HomeBarEntity();
            }
        }
        /// <summary>
        /// 获取各类型库位数量，占用率
        /// </summary>
        /// <param name="msg"></param>
        /// <returns></returns>
        public static List<HomePieDataEntity> GetHomePieData(out string msg)
        {
            msg = "";
            try
            {
                using (Model edm = new Model())
                {
       
                    List<HomePieDataEntity> hpdlist = new List<HomePieDataEntity>();
                    List<BASE_PLACETYPE> typelst = edm.BASE_PLACETYPE.ToList();
                    List<BASE_PLACE> placelst = edm.BASE_PLACE.Where(x => x.PLACE.Contains("-")).ToList();
                    typelst.ForEach(x =>
                    {
                        int totalplace = placelst.Where(y => y.PLACETYPEID == x.ID).ToList().Count;
                        int hascoun = placelst.Where(y => y.PLACETYPEID == x.ID && y.ISFULL == 1).ToList().Count;
                        HomePieDataEntity hpd = new HomePieDataEntity()
                        {
                             placeType=x.PLACETYPE+":"+x.LENGTH+"x"+x.WIDTH+"x"+x.HEIGHT,
                             placeTotalNum = totalplace,
                             placeFullNum = hascoun,
                             placeRate = hascoun == 0 ? "0.00%" : ((hascoun * 10000 / totalplace) / 100.00).ToString()+"%",
                             placeVal = hascoun,
                             placeOtherVal = totalplace - hascoun

                        };
                        hpdlist.Add(hpd);
                    });
                    return hpdlist;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new List<HomePieDataEntity>();
            }
        }

       /// <summary>
       /// 获取当天的出入库库零件，器具，超时数量
       /// </summary>
       /// <param name="msg"></param>
       /// <returns></returns>
        public static List<HomeNumDataEntity> GetHomeNumData(out string msg)
        {
            msg = "";
            try
            {
                using (Model edm = new Model())
                {
                    List<HomeNumDataEntity> hnelist = new List<HomeNumDataEntity>();
                    var sdt = DateTime.Parse((DateTime.Now.ToString("yyyy-MM-dd")));
                    var edt = DateTime.Parse(DateTime.Now.AddDays(1).ToString("yyyy-MM-dd"));
                    var dt = edm.View_TASK_RECORD.Where(x => x.createTime >= sdt && x.createTime <= edt).ToList();
                    var tdt = edm.TASK_RECORD.Where(x => x.CREATETIME >= sdt && x.CREATETIME <= edt).ToList();
                    var iitdt = dt.Where(x => x.taskType == 1 && !string.IsNullOrEmpty(x.itemName)).ToList().Sum(x=>x.itemCount);
                    var oitdt = dt.Where(x => x.taskType == 2 && !string.IsNullOrEmpty(x.itemName)).ToList().Sum(x => x.itemCount);
                    var icodt = tdt.Where(x => x.TASK_TASK.TASKTYPE == 1).ToList().Select(x=>x.TASKID).Distinct().Count();
                    var ocodt = tdt.Where(x => x.TASK_TASK.TASKTYPE == 2).ToList().Select(x => x.TASKID).Distinct().Count();
                    var overdatacoun = edm.View_BASE_PLACE_VS_CONTAINER.Where(x => x.stStatus=="超期").ToList().Count;
                    hnelist.Add(new HomeNumDataEntity() { no = 1, titel = "今日入库零件数", value = iitdt });
                    hnelist.Add(new HomeNumDataEntity() { no = 2, titel = "今日出库零件数", value = oitdt });
                    hnelist.Add(new HomeNumDataEntity() { no = 3, titel = "今日入库器具数", value = icodt });
                    hnelist.Add(new HomeNumDataEntity() { no = 4, titel = "今日出库器具数", value = ocodt });
                    hnelist.Add(new HomeNumDataEntity() { no = 5, titel = "超期零件数量", value = overdatacoun });

                    return hnelist;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new List<HomeNumDataEntity>();
            }
        }

        /// <summary>
        /// 获取指定天的出入库库零件，器具，超时数量
        /// </summary>
        /// <param name="msg"></param>
        /// <returns></returns>
        public static List<HomeNumDataEntityVsTime> GetHomeNumDataByDate(DateTime stday, DateTime edday, out string msg)
        {
            msg = "";
            try
            {
                using (Model edm = new Model())
                {
                   
                    //第一排
                    List<HomeNumDataEntityVsTime> hvtList = new List<HomeNumDataEntityVsTime>();
                    int dayNum = (DateTime.Parse((edday.ToString("yyyy-MM-dd"))) - DateTime.Parse((stday.AddDays(1).ToString("yyyy-MM-dd")))).Days;
                    for (int i = 0; i < dayNum; i++)
                    {
                        List<HomeNumDataEntity> hnelist = new List<HomeNumDataEntity>();
                        var sdt = DateTime.Parse((stday.AddDays(i).ToString("yyyy-MM-dd")));
                        var edt = DateTime.Parse(stday.AddDays(i + 1).ToString("yyyy-MM-dd"));
                        var dt = edm.View_TASK_RECORD.Where(x => x.createTime >= sdt && x.createTime <= edt).ToList();
                        var tdt = edm.TASK_RECORD.Where(x => x.CREATETIME >= sdt && x.CREATETIME <= edt).ToList();
                        var iitdt = dt.Where(x => x.taskType == 1 && !string.IsNullOrEmpty(x.itemName)).ToList().Sum(x => x.itemCount);
                        var oitdt = dt.Where(x => x.taskType == 2 && !string.IsNullOrEmpty(x.itemName)).ToList().Sum(x => x.itemCount);
                        var icodt = tdt.Where(x => x.TASK_TASK.TASKTYPE == 1).ToList().Select(x => x.TASKID).Distinct().Count();
                        var ocodt = tdt.Where(x => x.TASK_TASK.TASKTYPE == 2).ToList().Select(x => x.TASKID).Distinct().Count();
                        //var overdatacoun = edm.View_BASE_PLACE_VS_CONTAINER.Where(x => x.stStatus == "超期").ToList().Count;
                        hnelist.Add(new HomeNumDataEntity() { no = 1, titel = "入库零件数", value = iitdt });
                        hnelist.Add(new HomeNumDataEntity() { no = 2, titel = "出库零件数", value = oitdt });
                        hnelist.Add(new HomeNumDataEntity() { no = 3, titel = "入库器具数", value = icodt });
                        hnelist.Add(new HomeNumDataEntity() { no = 4, titel = "出库器具数", value = ocodt });
                        //hnelist.Add(new HomeNumDataEntity() { no = 5, titel = "超期零件数量", value = overdatacoun });
                        //第二排
                        var record = edm.RECORD_WAREHOUSEINFO.FirstOrDefault(x => x.time >= sdt && x.time<=edt);
                        if (record != null)
                        {
                            hnelist.Add(new HomeNumDataEntity() { no = 5, titel = "超期零件数量", value = record.itemOver ?? 0 });
                            hnelist.Add(new HomeNumDataEntity() { no = 6, titel = "库位总数", value = 2980 });
                            hnelist.Add(new HomeNumDataEntity() { no = 7, titel = "有货库位", value = record.placeFull ?? 0 });
                            hnelist.Add(new HomeNumDataEntity() { no = 8, titel = "带件器具", value = record.containerFull ?? 0 });
                            hnelist.Add(new HomeNumDataEntity() { no = 9, titel = "空器具数", value = record.containerEmpty ?? 0 });
                            hnelist.Add(new HomeNumDataEntity() { no = 10, titel = "制件总数", value = record.itemNum ?? 0 });
                        }
                        else
                        {
                            hnelist.Add(new HomeNumDataEntity() { no = 5, titel = "超期零件数量", value = 0 });
                            hnelist.Add(new HomeNumDataEntity() { no = 6, titel = "库位总数", value = 2980 });
                            hnelist.Add(new HomeNumDataEntity() { no = 7, titel = "有货库位", value = 0 });
                            hnelist.Add(new HomeNumDataEntity() { no = 8, titel = "带件器具", value = 0 });
                            hnelist.Add(new HomeNumDataEntity() { no = 9, titel = "空器具数", value = 0 });
                            hnelist.Add(new HomeNumDataEntity() { no = 10, titel = "制件总数", value = 0 });
                        }
                        HomeNumDataEntityVsTime hvt = new HomeNumDataEntityVsTime();
                        hvt.time = sdt;
                        hvt.homeNumDataEntityList = hnelist;
                        hvtList.Add(hvt);
                    }
                    return hvtList;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new List<HomeNumDataEntityVsTime>();
            }
        } 
        #endregion

       /// <summary>
        /// 库存分类统计
       /// </summary>
       /// <param name="msg"></param>
       /// <returns></returns>
        public static PieEntity GetStockClassificationData(int type,out string msg)
        {
            try
            {
                msg = "";
                using (Model edm = new Model())
                {
                    PieEntity hbe = new PieEntity();
                    string sql="";
                    if (type==1)
                    {
                        sql = @"select createLine as titel,SUM(itemNum) as value from(select itemName,itemNum,CASE WHEN createLine like '%李沧%' THEN '李沧产线'
ELSE createLine END  as createLine,containerName,place from  [dbo].[View_BASE_PLACE_VS_CONTAINER] 
where createLine is not null and createLine like '%即墨%' or createLine like '%李沧%') A   group by createLine ";
                    }else
                    {
                        sql = @"select createLine as titel,COUNT(distinct containerName) value from(select itemName,itemNum,CASE WHEN createLine like '%李沧%' THEN '李沧产线'
ELSE createLine END  as createLine,containerName,place from  [dbo].[View_BASE_PLACE_VS_CONTAINER] 
where createLine is not null and createLine like '%即墨%' or createLine like '%李沧%') A   group by createLine";
                    }

                    List<HomeNumDataEntity> dalst = edm.Database.SqlQuery<HomeNumDataEntity>(sql).ToList(); ;
                    List<string> timelst = new List<string>();
              
                    List<int> dt1 = new List<int>();

                    if (dalst != null && dalst.Count > 0)
                    {
                        hbe.legend = new List<string>();
                        hbe.legend.AddRange(dalst.Select(x=>x.titel).ToList());
                        List<PieDate> pdlst = new List<PieDate>();
                        double sumcount = double.Parse(dalst.Select(x => x.value).ToList().Sum().ToString());
                        dalst.ForEach(x =>
                        {
                            var pd = new PieDate();
                            pd.name = x.titel;
                            long val = string.IsNullOrEmpty(x.value.ToString()) ? 0 : long.Parse(x.value.ToString());
                            pd.data = ((val * 10000 / sumcount)/100).ToString("0.00");
                            pdlst.Add(pd);
                        });
                        hbe.data = new List<PieDate>();
                        hbe.data.AddRange(pdlst);
                    }
                    else 
                    {
                        hbe.legend = new List<string>();
                        hbe.legend.Add("即墨A线甲班");
                        hbe.legend.Add("即墨A线乙班");
                        hbe.legend.Add("即墨B线甲班");
                        hbe.legend.Add("即墨B线乙班");
                        hbe.data = new List<PieDate>();
                        hbe.data.Add(new PieDate() { name = "即墨A线甲班", data = "0.00" });
                        hbe.data.Add(new PieDate() { name = "即墨A线乙班", data = "0.00" });
                        hbe.data.Add(new PieDate() { name = "即墨B线甲班", data = "0.00" });
                        hbe.data.Add(new PieDate() { name = "即墨B线乙班", data = "0.00" });
                    }
                    return hbe;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new PieEntity();
            }
        }
       /// <summary>
        /// 出入库任务平均时长
       /// </summary>
       /// <param name="startTime"></param>
       /// <param name="endTime"></param>
       /// <param name="msg"></param>
       /// <returns></returns>
        public static BarEntity GetInOutAverageTime(string startTime, string endTime, out string msg)
        {
            try
            {
                msg = "";
                using (Model edm = new Model())
                {
                    BarEntity hbe = new BarEntity();
                    string sql = @"select    CASE
WHEN TASKTYPE=1 THEN '入库'
WHEN TASKTYPE=2 THEN '出库'
END as type,count(id)taskType,CONVERT(varchar(100), FINISHTIME, 23)time,sum(DATEDIFF ( mi , DOTIME, FINISHTIME))count from [dbo].[TASK_TASK] 
where FINISHTIME>='{0}' and FINISHTIME<='{1}' and TASKSTATUS='完成'
group by CONVERT(varchar(100), FINISHTIME, 23),TASKTYPE";
                    sql = string.Format(sql, startTime, endTime);
                    List<SummaryHomeEntity> dalst = edm.Database.SqlQuery<SummaryHomeEntity>(sql).ToList();
                    List<string> timelst = new List<string>();
                    List<string> timelstNoYear = new List<string>();

                    string dt = DateTime.Parse(startTime).ToString("yyyy-MM-dd");
                    string edt = DateTime.Parse(endTime).AddDays(1).ToString("yyyy-MM-dd");
                    while (dt != edt)
                    {
                        timelstNoYear.Add(dt.Substring(5));
                        timelst.Add(dt);
                        dt = DateTime.Parse(dt).AddDays(1).ToString("yyyy-MM-dd");
                    }
                    List<string> dt1 = new List<string>();
                    List<string> dt2 = new List<string>();
                    List<string> dt3 = new List<string>();
                    if (timelst != null && timelst.Count > 0)
                    {
                        timelst.ForEach(x =>
                        {
                            var d1 = dalst.FirstOrDefault(y => y.time == x && y.type == "入库");
                            if (d1 != null)
                            {
                                double taskcoun = long.Parse(d1.taskType.ToString());
                                long taskmin = long.Parse(d1.count.ToString());
                                string val = ((taskmin / taskcoun)).ToString("0.00");
                                dt1.Add(val);
                            }
                            else
                            {
                                dt1.Add("0.00");
                            }
                            var d2 = dalst.FirstOrDefault(y => y.time == x && y.type == "出库");
                            if (d2 != null)
                            {
                                double taskcoun = long.Parse(d2.taskType.ToString());
                                long taskmin = long.Parse(d2.count.ToString());
                                string val = ((taskmin/ taskcoun)).ToString("0.00");
                                dt2.Add(val);
                            }
                            else
                            {
                                dt2.Add("0.00");
                            }
                            var d3 = dalst.Where(y => y.time == x).ToList() ;
                            if (d3 != null && d3.Count>0)
                            {
                                double taskcoun=long.Parse(d3.Select(y=>y.taskType).Sum().ToString());
                                long  taskmin=long.Parse(d3.Select(y=>y.count).Sum().ToString());
                                string val = ((taskmin / taskcoun)).ToString("0.00");
                                dt3.Add(val);
                            }
                            else
                            {
                                dt3.Add("0.00");
                            }
                        });
                    }
                    hbe.data = new List<BarDate>();
                    hbe.xdata = timelstNoYear;
                    hbe.legend = new List<string>() { "入库", "出库", "平均" };
                    BarDate dd1 = new BarDate() { name = "入库" };
                    dd1.xdata = new List<string>();
                    dd1.xdata.AddRange(dt1);
                    hbe.data.Add(dd1);

                    BarDate dd2 = new BarDate() { name = "出库" };
                    dd2.xdata = new List<string>();
                    dd2.xdata.AddRange(dt2);
                    hbe.data.Add(dd2);

                    BarDate dd3 = new BarDate() { name = "平均" };
                    dd3.xdata = new List<string>();
                    dd3.xdata.AddRange(dt3);
                    hbe.data.Add(dd3);
                    return hbe;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new BarEntity();
            }
        }

        /// <summary>
        /// 每小时出入库数
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public static BarEntity GetInOutCountByHour(string startTime, string endTime, out string msg)
        {
            try
            {
                msg = "";
                using (Model edm = new Model())
                {
                    BarEntity hbe = new BarEntity();
                    string sql = @"select    CASE
WHEN TASKTYPE=1 THEN '入库'
WHEN TASKTYPE=2 THEN '出库'
END as type,count(id)taskType,CONVERT(varchar(100), FINISHTIME, 23) +' '+right('00'+(CONVERT(varchar(10),(DATEPART(hh,FINISHTIME)))),2) time,sum(DATEDIFF ( mi , DOTIME, FINISHTIME))count from [dbo].[TASK_TASK] 
where FINISHTIME>='{0}' and FINISHTIME<='{1}' and TASKSTATUS='完成'
group by CONVERT(varchar(100), FINISHTIME, 23)+' '+right('00'+(CONVERT(varchar(10),(DATEPART(hh,FINISHTIME)))),2),TASKTYPE";
                    string dt = DateTime.Parse(startTime).ToString("yyyy-MM-dd 00:00:00");
                    string edt = DateTime.Parse(endTime).AddDays(1).ToString("yyyy-MM-dd 00:00:00");

                    sql = string.Format(sql, dt, edt);
                    List<SummaryHomeEntity> dalst = edm.Database.SqlQuery<SummaryHomeEntity>(sql).ToList();
                    List<string> timelst = new List<string>();

                    while (dt != edt)
                    {
                        timelst.Add(dt);
                        dt = DateTime.Parse(dt).AddHours(1).ToString("yyyy-MM-dd HH:mm:ss");
                    }
                    List<string> dt1 = new List<string>();
                    List<string> dt2 = new List<string>();
                    List<string> dt3 = new List<string>();
                    if (timelst != null && timelst.Count > 0)
                    {
                        timelst.ForEach(x =>
                        {
                            var d1 = dalst.FirstOrDefault(y => y.time.Substring(0, 13) == x.Substring(0, 13) && y.type == "入库");
                            if (d1 != null)
                            {
                                double taskcoun = long.Parse(d1.taskType.ToString());
                                //long taskmin = long.Parse(d1.count.ToString());
                                //string val = ((taskmin / taskcoun)).ToString("0.00");
                                dt1.Add(taskcoun.ToString());
                            }
                            else
                            {
                                dt1.Add("0.00");
                            }
                            var d2 = dalst.FirstOrDefault(y => y.time.Substring(0, 13) == x.Substring(0, 13) && y.type == "出库");
                            if (d2 != null)
                            {
                                double taskcoun = long.Parse(d2.taskType.ToString());
                                //long taskmin = long.Parse(d2.count.ToString());
                                //string val = ((taskmin / taskcoun)).ToString("0.00");
                                dt2.Add(taskcoun.ToString());
                            }
                            else
                            {
                                dt2.Add("0.00");
                            }
                            var d3 = dalst.Where(y => y.time.Substring(0, 13) == x.Substring(0, 13)).ToList();
                            if (d3 != null && d3.Count > 0)
                            {
                                double taskcoun = long.Parse(d3.Select(y => y.taskType).Sum().ToString());
                                //long taskmin = long.Parse(d3.Select(y => y.count).Sum().ToString());
                                //string val = ((taskmin / taskcoun)).ToString("0.00");
                                dt3.Add(taskcoun.ToString());
                            }
                            else
                            {
                                dt3.Add("0.00");
                            }
                        });
                    }
                    hbe.data = new List<BarDate>();
                    hbe.xdata = timelst;
                    hbe.legend = new List<string>() { "入库", "出库", "总计" };
                    BarDate dd1 = new BarDate() { name = "入库" };
                    dd1.xdata = new List<string>();
                    dd1.xdata.AddRange(dt1);
                    hbe.data.Add(dd1);

                    BarDate dd2 = new BarDate() { name = "出库" };
                    dd2.xdata = new List<string>();
                    dd2.xdata.AddRange(dt2);
                    hbe.data.Add(dd2);

                    BarDate dd3 = new BarDate() { name = "总计" };
                    dd3.xdata = new List<string>();
                    dd3.xdata.AddRange(dt3);
                    hbe.data.Add(dd3);
                    return hbe;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new BarEntity();
            }
        }


        /// <summary>
        /// 任务节拍分析
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public static BarEntity GetTaskBeatAnalysis(string startTime, out string msg)
        {
            try
            {
                msg = "";
                using (Model edm = new Model())
                {
                    BarEntity hbe = new BarEntity();
                    string sql = @"select  CONVERT(varchar(100), FINISHTIME, 23)titel,DATEDIFF ( mi , DOTIME, FINISHTIME) as value from [dbo].[TASK_TASK] 
where CONVERT(varchar(100), FINISHTIME, 23)='{0}' and TASKSTATUS='完成'";
                    sql = string.Format(sql, startTime);
                    List<HomeNumDataEntity> dalst = edm.Database.SqlQuery<HomeNumDataEntity>(sql).ToList();
                    List<string> timelst = new List<string>();
                    List<int> timeintlst = new List<int>();
                    timeintlst.Add(2);
                    timeintlst.Add(3);
                    timeintlst.Add(4);
                    timeintlst.Add(5);
                    timeintlst.Add(6);
                    timeintlst.Add(7);
                    timeintlst.Add(8);
                    timeintlst.Add(9);
                    timeintlst.Add(10);
                    timeintlst.Add(20);
                    timeintlst.Add(30);
                    List<string> dt1 = new List<string>();
                    List<string> count1 = new List<string>();

                    if (timeintlst != null && timeintlst.Count > 0)
                    {
                        double sumcount = double.Parse(dalst.Count().ToString());
                        timeintlst.ForEach(x =>
                        {
                          
                            int st = x;
                            int et = 0;
                            if (x >= 10)
                            {
                                et = x + 10;
                            }
                            else
                            {
                                et = x + 1;
                            }
                            if (x >= 10)
                            {
                                et = x + 10;
                                timelst.Add("大于" + x + "分钟");

                            }
                            else 
                            {
                                timelst.Add(x + "分钟");
                            }
                            List<HomeNumDataEntity> d1 = new List<HomeNumDataEntity>();
                            if (st >= 30)
                            {
                                d1 = dalst.Where(y => y.value >= st).ToList();

                            }
                            else
                            {
                                d1 = dalst.Where(y => y.value >= st && y.value < et).ToList();
                            }
                            string val = (dalst.Where(y => y.value < et).Count() * 100 / sumcount).ToString("0.00");
                            dt1.Add(val);
                            if (d1 != null && d1.Count>0)
                            {

                                long count = long.Parse(d1.Count.ToString());
                                //string val = ((count*100 / sumcount)).ToString("0.00");
                                count1.Add(count.ToString());
                            }
                            else
                            {
                                //dt1.Add("0.00");
                                count1.Add("0");
                            }
                  
                        });
                    }
                    //for (int i = 0; i < dt1.Count; i++)
                    //{
                    //    if (i > 0)
                    //    {
                    //        dt1[i] = (double.Parse(dt1[i]) + double.Parse(dt1[i - 1])).ToString();
                    //    }
                    //}

                    int maxNum = 0;
                    foreach (var i in count1)
                    {
                        maxNum+=int.Parse(i);
                    }
                    hbe.data = new List<BarDate>();
                    hbe.xdata = timelst;
                    hbe.legend = new List<string>();
                    BarDate dd1 = new BarDate() { name = "占比" };
                    dd1.xdata = new List<string>();
                    dd1.xdata.AddRange(dt1);
                    hbe.data.Add(dd1);
                    BarDate dd2 = new BarDate() { name = "数量" };
                    dd2.xdata = new List<string>();
                    dd2.xdata.AddRange(count1);
                    hbe.data.Add(dd2);
                    hbe.legend.Add("占比");
                    hbe.legend.Add("数量");
                    //纵轴最大值
                    hbe.maxNum = maxNum;
                    return hbe;
                }
            }
            catch
            {
                msg = "数据获取失败";
                return new BarEntity();
            }
        }
       /// <summary>
        /// 货格利用率
       /// </summary>
       /// <param name="startTime"></param>
       /// <param name="endTime"></param>
       /// <param name="msg"></param>
       /// <returns></returns>
        public static BarEntity GetStorageVailability(string startTime, string endTime, out string msg)
        {
            try
            {
                msg = "";
                using (Model edm = new Model())
                {
                    BarEntity hbe = new BarEntity();
                    string sql = @"select A.PlaceCount as count1,isnull(B.EmplaceCount,0) as count2,A.Tm as time from  (
(select COUNT(distinct PLACEID)PlaceCount,CONVERT(varchar,CREATETIME,23) as Tm    
from [dbo].[STORAGERECORD] where CREATETIME>='{0}' and CREATETIME<='{1}' group by CONVERT(varchar,CREATETIME,23))A  left join
(select COUNT(distinct PLACEID)EmplaceCount,CONVERT(varchar,CREATETIME,23) as Tm   
from [dbo].[STORAGERECORD] where ITEMID is null and CREATETIME>='{2}' and CREATETIME<='{3}' group by CONVERT(varchar,CREATETIME,23)) B  on A.Tm=B.Tm
)";
                    sql = string.Format(sql, startTime, endTime, startTime, endTime);
                    List<SummaryCommEntity> dalst = edm.Database.SqlQuery<SummaryCommEntity>(sql).ToList();
                    List<string> timelst = new List<string>();
                    List<string> timelstNoYear = new List<string>();

                    string dt = DateTime.Parse(startTime).ToString("yyyy-MM-dd");
                    string edt = DateTime.Parse(endTime).AddDays(1).ToString("yyyy-MM-dd");
                    while (dt != edt)
                    {
                        timelstNoYear.Add(dt.Substring(5));
                        timelst.Add(dt);
                        dt = DateTime.Parse(dt).AddDays(1).ToString("yyyy-MM-dd");
                    }
                    int placeallcount = 2980;

                    List<string> dt1 = new List<string>();
                    List<string> dt2 = new List<string>();
                    List<string> dt3 = new List<string>();
                    if (timelst != null && timelst.Count > 0)
                    {
                        timelst.ForEach(x =>
                        {
                            var d1 = dalst.FirstOrDefault(y => y.time == x);
                            if (d1 != null)
                            {
                                double c1 = double.Parse(d1.count1.ToString());
                                double c2 = double.Parse(d1.count2.ToString());
                                string val1 = ((c1 / placeallcount)*100).ToString("0.00");
                                string val2 = ((c2 / placeallcount)*100).ToString("0.00");
                                dt1.Add(val1);
                                dt2.Add(val2);
                            }
                            else
                            {
                                dt1.Add("0.00");
                                dt2.Add("0.00");
                            }
                        });
                    }
                    hbe.data = new List<BarDate>();
                    hbe.xdata = timelstNoYear;
                    //hbe.legend = new List<string>() { "货格利用率", "空器具利用率"};
                    hbe.legend = new List<string>() { "货格利用率" };
                    BarDate dd1 = new BarDate() { name = "货格利用率" };
                    dd1.xdata = new List<string>();
                    dd1.xdata.AddRange(dt1);
                    hbe.data.Add(dd1);

                    //BarDate dd2 = new BarDate() { name = "空器具利用率" };
                    //dd2.xdata = new List<string>();
                    //dd2.xdata.AddRange(dt2);
                    //hbe.data.Add(dd2);

                    return hbe;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new BarEntity();
            }
        }

       /// <summary>
        /// 在制品资金分析
       /// </summary>
       /// <param name="startTime"></param>
       /// <param name="endTime"></param>
       /// <param name="msg"></param>
       /// <returns></returns>
        public static BarEntity GetStorageCapitalAnalysis(string startTime, string endTime, out string msg)
        {
            try
            {
                msg = "";
                using (Model edm = new Model())
                {
                    BarEntity hbe = new BarEntity();
                    string sql = @"select SUM(ITEMNUM*PRICE) as coast,CONVERT(varchar,CREATETIME,23) as time   from [dbo].[STORAGERECORD] where ITEMID is not  null and CREATETIME>='{0}' and CREATETIME<='{1}'  
                                  group by CONVERT(varchar,CREATETIME,23)";
                    string sqlCount = @"select CAST(SUM(ITEMNUM) as decimal(10,2)) as coast,CONVERT(varchar,CREATETIME,23) as time   from [dbo].[STORAGERECORD] where ITEMID is not  null and CREATETIME>='{0}' and CREATETIME<='{1}'  
                                  group by CONVERT(varchar,CREATETIME,23)";
                    sql = string.Format(sql, startTime, endTime);
                    sqlCount = string.Format(sqlCount, startTime, endTime);
                    List<SummaryCommEntity> dalst = edm.Database.SqlQuery<SummaryCommEntity>(sql).ToList();
                    List<SummaryCommEntity> countList = edm.Database.SqlQuery<SummaryCommEntity>(sqlCount).ToList();

                    List<string> timelst = new List<string>();
                    List<string> timelstNoYear = new List<string>();
                    string dt = DateTime.Parse(startTime).ToString("yyyy-MM-dd");
                    string edt = DateTime.Parse(endTime).AddDays(1).ToString("yyyy-MM-dd");
                    while (dt != edt)
                    {
                        timelst.Add(dt);
                        timelstNoYear.Add(dt.Substring(5));
                        dt = DateTime.Parse(dt).AddDays(1).ToString("yyyy-MM-dd");
                    }
                    decimal allcost = dalst.Select(x => x.coast).Sum();
                    decimal averahe = allcost / timelst.Count;

                    List<string> dt1 = new List<string>();
                    List<string> dt2 = new List<string>();
                    if (timelst != null && timelst.Count > 0)
                    {
                        timelst.ForEach(x =>
                        {
                            var d1 = dalst.FirstOrDefault(y => y.time == x);
                            var count1=countList.FirstOrDefault(y => y.time == x);
                            if (d1 != null)
                            {

                                string val = d1.coast.ToString("0.00"); ;
                                dt1.Add(val);

                                decimal a = d1.coast / count1.coast;
                                dt2.Add(a.ToString("0.00"));

                            }
                            else
                            {
                                dt1.Add("0.00");
                                dt2.Add("0.00");
                            }

                            //dt2.Add(averahe.ToString("0.00"));
                        });
                    }
                    hbe.data = new List<BarDate>();
                    hbe.xdata = timelstNoYear;
                    hbe.legend = new List<string>() { "在制总金额", "在制平均金额"};
                    BarDate dd1 = new BarDate() { name = "在制总金额" };
                    dd1.xdata = new List<string>();
                    dd1.xdata.AddRange(dt1);
                    hbe.data.Add(dd1);

                    BarDate dd2 = new BarDate() { name = "在制平均金额" };
                    dd2.xdata = new List<string>();
                    dd2.xdata.AddRange(dt2);
                    hbe.data.Add(dd2);
                    return hbe;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new BarEntity();
            }
        }
        
       /// <summary>
        /// 立库总览和制件总览
       /// </summary>
       /// <param name="msg"></param>
       /// <returns></returns>
        public static List<HomeNumDataEntity> GetStorageOverviewData(out string msg)
        {
            msg = "";
            try
            {
                using (Model edm = new Model())
                {
                    List<HomeNumDataEntity> hnelist = new List<HomeNumDataEntity>();
                    var overdatacoun = edm.View_BASE_PLACE_VS_CONTAINER.ToList();
                    int hasplace = overdatacoun.Select(x => x.place).Distinct().Count();
                    int emptyitemplace = overdatacoun.Where(x=>string.IsNullOrEmpty(x.itemName)).Select(x => x.place).Distinct().Count();
                    int itemcoun = (int)overdatacoun.Select(x => x.itemNum).Sum();
                    hnelist.Add(new HomeNumDataEntity() { no = 1, titel = "库位总数", value = 2980 });
                    hnelist.Add(new HomeNumDataEntity() { no = 2, titel = "有货库位", value = hasplace });
                    hnelist.Add(new HomeNumDataEntity() { no = 3, titel = "带件器具", value = (hasplace - emptyitemplace) });
                    hnelist.Add(new HomeNumDataEntity() { no = 4, titel = "空器具数", value = emptyitemplace });
                    hnelist.Add(new HomeNumDataEntity() { no = 5, titel = "制件总数", value = itemcoun });

                    return hnelist;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new List<HomeNumDataEntity>();
            }
        }

       /// <summary>
        /// 设备利用率
       /// </summary>
       /// <param name="startTime"></param>
       /// <param name="endTime"></param>
       /// <param name="msg"></param>
       /// <returns></returns>
        public static BarEntity GetDevAvailability(string startTime, string endTime, int type,out string msg)
        {
            try
            {
                msg = "";
                using (Model edm = new Model())
                {
                    string sql = "";
          
                    if (type != 1)//RGV
                    {
                        sql = @"select ('RGV' +cast(USERGVID as varchar(5))) as titel,SUM(datediff( minute, DOTIME, FINISHTIME )) as count1,CONVERT(varchar,FINISHTIME,23)as time FROM [dbo].[RgvTask] 
                           where FINISHTIME is not null  and FINISHTIME>='{0}' and FINISHTIME<='{1}' group by CONVERT(varchar,FINISHTIME,23),USERGVID";
                    }
                    else //堆垛机
                    {
                        sql = @"select ('堆垛机' +cast(USESRMID as varchar(5))) as titel,SUM(datediff( minute, DOTIME, FINISHTIME )) as count1,CONVERT(varchar,FINISHTIME,23)as time FROM [dbo].[SrmTask] 
                            where FINISHTIME is not null  and FINISHTIME>='{0}' and FINISHTIME<='{1}' group by CONVERT(varchar,FINISHTIME,23),USESRMID
                                 ";
                    }
                    BarEntity hbe = new BarEntity();
                    sql = string.Format(sql,startTime, endTime);
                    List<SummaryCommEntity> dalst = edm.Database.SqlQuery<SummaryCommEntity>(sql).ToList();
                    List<string> timelst = new List<string>();
                    List<string> devlst = new List<string>();
                    List<BarDate> bdlst = new List<BarDate>();
                    timelst = dalst.Select(x => x.time).OrderBy(x => x).Distinct().ToList();
                    devlst = dalst.Select(x => x.titel).Distinct().ToList();
                   
                    if (timelst != null && timelst.Count > 0)
                    {
                        timelst.ForEach(x =>
                        {
                            devlst.ForEach(y =>
                            {
                                string rat = "0.00";
                                var dt = dalst.FirstOrDefault(b => b.time == x && b.titel == y);
                                if (dt != null)
                                {
                                    rat = ((double.Parse(dt.count1.ToString()) / (18 * 60)) * 100).ToString("0.00");
                                }
                                var bd = bdlst.FirstOrDefault(a => a.name == y);
                                if (bd == null)
                                {
                                    BarDate dd1 = new BarDate() { name = y };
                                    dd1.xdata = new List<string>();
                                    dd1.xdata.Add(rat);
                                    bdlst.Add(dd1);
                                }
                                else 
                                {
                                    bd.xdata.Add(rat);
                                }
                       

                            });
                        });
                    }
                    hbe.data = new List<BarDate>();
                    //临时格式化时间
                    for (int i = 0; i < timelst.Count; i++)
                    {
                        timelst[i] = timelst[i].Substring(5);
                    }
                    hbe.xdata = timelst;
                    hbe.legend = new List<string>();
                    hbe.legend = devlst;

                    hbe.data.AddRange(bdlst);

                    return hbe;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new BarEntity();
            }
        }
        
       /// <summary>
        /// 设备可动率
       /// </summary>
       /// <param name="startTime"></param>
       /// <param name="endTime"></param>
       /// <param name="msg"></param>
       /// <returns></returns>
        public static BarEntity GetDevMovable(string startTime, string endTime,int type, out string msg)
        {
            try
            {
                msg = "";
                using (Model edm = new Model())
                {
                    List<string> devlst = new List<string>();
                    string devname="堆垛机";
                    if (type != 1)
                    {
                        devname = "RGV";
                        devlst.Add("RGV1");
                        devlst.Add("RGV2");
                        devlst.Add("RGV3");
                        devlst.Add("RGV4");
                    }
                    else 
                    {
                        devlst.Add("堆垛机1");
                        devlst.Add("堆垛机2");
                        devlst.Add("堆垛机3");
                        devlst.Add("堆垛机4");
                        devlst.Add("堆垛机5");
                        devlst.Add("堆垛机6");
                        devlst.Add("堆垛机7");
                        devlst.Add("堆垛机8");
                        devlst.Add("堆垛机9");
                    }
        
                    BarEntity hbe = new BarEntity();
                    string sql = @" select DEVICENAME as titel, COUNT(DEVICENAME)as count1,CONVERT(varchar,DAY,23) as time from  [dbo].[ALERT_ALERTTIME] 
                      where ISERROR=1 and DEVICENAME like '%{0}%' and  DAY>='{1}' and DAY<='{2}'   group by DAY,DEVICENAME";
                    sql = string.Format(sql,devname,startTime, endTime);
                    List<SummaryCommEntity> dalst = edm.Database.SqlQuery<SummaryCommEntity>(sql).ToList();
                    List<string> timelst = new List<string>();
               
                    List<BarDate>  bdlst = new List<BarDate>(); 
                    timelst = dalst.Select(x => x.time).OrderBy(x=>x).Distinct().ToList();
                    //devlst = dalst.Select(x => x.titel).Distinct().ToList();
                    if (timelst != null && timelst.Count > 0)
                    {
                        timelst.ForEach(x =>
                        {
                            devlst.ForEach(y =>
                            {
                                string rat = "100";
                                var dt = dalst.FirstOrDefault(b => b.time == x && b.titel == y);
                                if (dt!=null)
                                {
                                    if(((double.Parse(dt.count1.ToString()) / (24 * 60)) * 100)>100)
                                    {
                                        string st = "1";
                                    }
                                    rat = (100 - (double.Parse(dt.count1.ToString()) / (24 * 60)) * 100).ToString("0.00");
                                }
                                var bd = bdlst.FirstOrDefault(a => a.name == y);
                                if (bd==null)
                                {
                                    BarDate dd1 = new BarDate() { name = y };
                                    dd1.xdata = new List<string>();
                                    dd1.xdata.Add(rat);
                                    bdlst.Add(dd1);
                                }
                                else
                                {
                                    bd.xdata.Add(rat);
                                }
          

                            });
                        });
                    }
                    hbe.data = new List<BarDate>();
                    hbe.xdata = timelst;
                    //临时格式化时间
                    for (int i = 0; i < timelst.Count; i++)
                    {
                        timelst[i] = timelst[i].Substring(5);
                    }
                    hbe.legend = new List<string>();
                    hbe.legend = devlst;
    
                    hbe.data.AddRange(bdlst);

                    return hbe;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new BarEntity();
            }
        }

       /// <summary>
        /// 制件周转时间
       /// </summary>
       /// <param name="year"></param>
       /// <param name="stMonth"></param>
       /// <param name="edMonth"></param>
       /// <param name="stWeek"></param>
       /// <param name="edWeek"></param>
       /// <param name="msg"></param>
       /// <returns></returns>
        public static BarEntity GetPartTurnover(int stYear, int edYear, int stMonth, int edMonth, int stWeek, int edWeek, out string msg)
        {
            try
            {
                msg = "";
                string startTime = (DateTime.Parse((stYear + "-" + stMonth + "-" + (stWeek) * 7)).AddDays(-6)).ToString("yyyy-MM-dd");
                string endTime = edYear + "-" + edMonth + "-" + edWeek * 7;

                using (Model edm = new Model())
                {
                    BarEntity hbe = new BarEntity();
                    string stsql = @"select   SUM(ITEMNUM) as count1 ,Line as titel,CONVERT(datetime, CreateTime, 111) as dtime from  (select A.ITEMID,A.ITEMNUM,A.PRICE,CONVERT(varchar ,A.CREATETIME, 23) CreateTime,left(B.CREATELINE,4) Line
from [dbo].[STORAGERECORD] A left join BASE_ITEM B on A.ITEMID=B.ID where ITEMID is not null and CREATELINE like '%即墨%' and A.CREATETIME>='{0}' and A.CREATETIME<='{1}'
union
select A.ITEMID,A.ITEMNUM,A.PRICE,CONVERT(varchar ,A.CREATETIME, 23) CreateTime, '李沧产线' Line
from [dbo].[STORAGERECORD] A left join BASE_ITEM B on A.ITEMID=B.ID where ITEMID is not null and CREATELINE like '%李沧%' and A.CREATETIME>='{2}' and A.CREATETIME<='{3}')A
 group by Line,CreateTime";
                    string outsql = @" select   SUM(ITEMCOUNT) as count1 ,Line as titel,CONVERT(datetime, CreateTime, 111) as dtime from  (select A.ITEMID,A.ITEMCOUNT,CONVERT(varchar ,A.CREATETIME, 23) CreateTime,left(B.CREATELINE,4) Line
from [dbo].[TASK_RECORD] A left join BASE_ITEM B on A.ITEMID=B.ID where TYPE=2 and ITEMID is not null and CREATELINE like '%即墨%' and A.CREATETIME>='{0}' and A.CREATETIME<='{1}'
union
select A.ITEMID,A.ITEMCOUNT,CONVERT(varchar ,A.CREATETIME, 23) CreateTime, '李沧产线' Line
from [dbo].[TASK_RECORD] A left join BASE_ITEM B on A.ITEMID=B.ID where TYPE=2 and ITEMID is not null and CREATELINE like '%李沧%' and A.CREATETIME>='{2}' and A.CREATETIME<='{3}')A
 group by Line,CreateTime  having SUM(ITEMCOUNT)>20";
                    stsql = string.Format(stsql, startTime, endTime, startTime, endTime);
                    outsql = string.Format(outsql, startTime, endTime, startTime, endTime);
                    List<SummaryCommEntity> stdalst = edm.Database.SqlQuery<SummaryCommEntity>(stsql).ToList();
                    List<SummaryCommEntity> outdalst = edm.Database.SqlQuery<SummaryCommEntity>(outsql).ToList();
                    List<string> timelst = new List<string>();
                    List<string> devlst = new List<string>();
                    List<string> lst = new List<string>();
                    List<string> ti1lst = stdalst.Select(x => x.titel).ToList();
                    List<string> ti2lst = outdalst.Select(x => x.titel).ToList();
                    List<BarDate> bdlst = new List<BarDate>(); 
                    lst.AddRange(ti1lst);
                    lst.AddRange(ti2lst);
                    devlst = lst.Select(x=>x).Distinct().ToList() ;
                    int smon = stMonth;
                    int swek = stWeek;
                    int emon = edMonth;
                    int ewek = edWeek;
                    if (ewek == 4)
                    {
                        emon = emon + 1;
                        ewek = 1;
                    }
                    else 
                    {
                        ewek += 1;
                    }
                    while (smon != emon || swek != ewek)
                    {
                        if (smon < 10)
                        {
                            timelst.Add("0"+smon + "M" + swek + "W");
                        }
                        else
                        {
                            timelst.Add(smon + "M" + swek + "W");
                        }
                        swek =swek+ 1;
                        if (swek>4)
                        {
                            swek = 1;
                            smon = smon + 1;
                        }
                    }
                    List<string> dt1 = new List<string>();
                    List<string> dt2 = new List<string>();
                    if (timelst != null && timelst.Count > 0)
                    {
                        timelst.ForEach(x =>
                        {
                            devlst.ForEach(d =>
                            {

                                double con1 = 0;
                                double con2 = 0;
                                string st = stYear + "-" + int.Parse(x[0].ToString()) + int.Parse(x[1].ToString()) + "-" + (int.Parse(x[3].ToString()) * 7);
                                DateTime sdt = DateTime.Parse(st).AddDays(-6);
                                DateTime edt = DateTime.Parse(st);
                                var d1 = stdalst.Where(y => y.dtime >= sdt && y.dtime <= edt&&y.titel==d).ToList();
                                if (d1 != null && d1.Count>0)
                                {

                                    con1=d1.Select(a=>a.count1).Average();
        
                                }
                                var d2 = outdalst.Where(y => y.dtime >= sdt && y.dtime <= edt && y.titel == d).ToList(); ;
                                if (d2 != null && d2.Count > 0)
                                {

                                    con2 = d2.Select(a => a.count1).Average();
                                }
                                string dcon = con2==0?"0":(con1 / con2).ToString("0.0");
                                var bd = bdlst.FirstOrDefault(a => a.name == d);
                                if (bd == null)
                                {
                                    BarDate dd1 = new BarDate() { name = d };
                                    dd1.xdata = new List<string>();
                                    dd1.xdata.Add(dcon);
                                    bdlst.Add(dd1);
                                }
                                else
                                {
                                    bd.xdata.Add(dcon);
                                }
                            });
                        });
                    }
                    hbe.data = new List<BarDate>();
                    hbe.xdata = timelst;
                    hbe.legend = new List<string>();
                    hbe.legend = devlst;
                    hbe.data.AddRange(bdlst);
                    return hbe;
                }
            }
            catch (Exception ex)
            {
                msg = "数据获取失败";
                return new BarEntity();
            }
        }
    }
}