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();
|
}
|
}
|
}
|
}
|
