/// <summary>
/// 获取起止时间内的以产品序列号分析后的产量结果
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns>结果数据集合</returns>
private List <ModelView.DataAnalysisValuePairView> GetProductQtyByPartNo(DateTime startTime, DateTime endTime)
{
string sql = "select distinct part_no,count(serial_no) Qty from mes_fb_item where (fb_datetime between @startTime and @endTime) and eqm_no=(select eqm_no from pdm_eqm where eqm_index=(select max(eqm_index) from pdm_eqm)) group by part_no order by part_no desc;";
Dictionary <string, object> pms = new Dictionary <string, object>();
pms.Add("@startTime", startTime);
pms.Add("@endTime", endTime);
DataTable dt = new DataTable();
try
{
dt = DbEngine.QueryTable(sql, pms);
}
catch (Exception)
{
return(null);
}
List <ModelView.DataAnalysisValuePairView> list = new List <ModelView.DataAnalysisValuePairView>();
foreach (DataRow item in dt.Rows)
{
ModelView.DataAnalysisValuePairView model = new ModelView.DataAnalysisValuePairView();
try
{
decimal numbers = Math.Round((item.Field <int>("Qty") / 1.0M), 2);
model.xAxis_value = item.Field <string>("part_no");
model.yAxis_value = numbers.ToString();
}
catch (Exception)
{
continue;
}
list.Add(model);
}
return(list);
}
/// <summary>
/// 根据停机原因统计设备停机时间
/// </summary>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <returns>设备停机时间记录，秒级</returns>
private List <ModelView.DataAnalysisValuePairView> GetEqmJamTimeByCause(DateTime startTime, DateTime endTime)
{
string sql = "select jam_cause_name,sum(DATEDIFF(ss,submit_time,reply_time)) as [time] from eqm_jam_record where reply_time is not null and (reply_time between @startTime and @endTime) and (submit_time between @startTime and @endTime) group by jam_cause_name order by [time] desc;";
Dictionary <string, object> pms = new Dictionary <string, object>();
pms.Add("@startTime", startTime);
pms.Add("@endTime", endTime);
DataTable dt = new DataTable();
try
{
dt = DbEngine.QueryTable(sql, pms);
}
catch (Exception)
{
return(null);
}
List <ModelView.DataAnalysisValuePairView> list = new List <ModelView.DataAnalysisValuePairView>();
foreach (DataRow item in dt.Rows)
{
ModelView.DataAnalysisValuePairView model = new ModelView.DataAnalysisValuePairView();
try
{
decimal numbers = Math.Round((item.Field <int>("time") / 60.0M), 2);
model.xAxis_value = item.Field <string>("jam_cause_name");
model.yAxis_value = numbers.ToString();
}
catch (Exception)
{
continue;
}
list.Add(model);
}
return(list);
}
/// <summary>
/// 获取起止时间内的以产品序列号分析后的质量不良结果
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns>结果数据表</returns>
private List <ModelView.DataAnalysisValuePairView> GetQcmQaNgByPartNo(DateTime startTime, DateTime endTime)
{
string sql = "select distinct part_no,count(serial_no) Qty from (select distinct a.serial_no,a.submit_time,b.part_no from qcm_qa_record a left join mes_fb_item b on a.serial_no=b.serial_no) as t where (submit_time between @startTime and @endTime) group by part_no order by Qty desc,part_no desc;";
Dictionary <string, object> pms = new Dictionary <string, object>();
pms.Add("@startTime", startTime);
pms.Add("@endTime", endTime);
DataTable dt = new DataTable();
try
{
dt = DbEngine.QueryTable(sql, pms);
}
catch (Exception)
{
return(null);
}
List <ModelView.DataAnalysisValuePairView> list = new List <ModelView.DataAnalysisValuePairView>();
foreach (DataRow item in dt.Rows)
{
ModelView.DataAnalysisValuePairView model = new ModelView.DataAnalysisValuePairView();
try
{
decimal numbers = Math.Round((item.Field <int>("Qty") / 1.0M), 2);
model.xAxis_value = item.Field <string>("part_no");
model.yAxis_value = numbers.ToString();
}
catch (Exception)
{
continue;
}
list.Add(model);
}
return(list);
}
/// <summary>
/// 获取起止时间内的以时间分析后的产量结果
/// </summary>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="timeSpacy">时间精度</param>
/// <returns>结果数据集合</returns>
private List <ModelView.DataAnalysisValuePairView> GetProductQtyByTime(DateTime startTime, DateTime endTime, string timeSpacy)
{
string sql = "select distinct datepart(yyyy,fb_datetime)[Year],datepart(mm,fb_datetime)[Month],datepart(dd,fb_datetime)[Date],datepart(hh,fb_datetime)[Hour],datepart(mi,fb_datetime) [Minute],count(serial_no) [Qty] from mes_fb_item where (fb_datetime between @startTime and @endTime) and eqm_no=(select eqm_no from pdm_eqm where eqm_index=(select max(eqm_index) from pdm_eqm)) group by datepart(yyyy,fb_datetime),datepart(mm,fb_datetime),datepart(dd,fb_datetime),datepart(hh,fb_datetime),datepart(mi,fb_datetime) order by datepart(yyyy,fb_datetime) asc,datepart(mm,fb_datetime) asc,datepart(dd,fb_datetime) asc,datepart(hh,fb_datetime) asc,datepart(mi,fb_datetime) asc;";
Dictionary <string, object> pms = new Dictionary <string, object>();
pms.Add("@startTime", startTime);
pms.Add("@endTime", endTime);
DataTable dt = new DataTable();
try
{
dt = DbEngine.QueryTable(sql, pms);
}
catch (Exception)
{
return(null);
}
List <ModelView.DataAnalysisValuePairView> list = new List <ModelView.DataAnalysisValuePairView>();
DateTime dtStart = GetNextTimeSpot(startTime, timeSpacy);
List <DataRow> listRows = dt.AsEnumerable().ToList();
while (dtStart <= endTime)
{
ModelView.DataAnalysisValuePairView model = new ModelView.DataAnalysisValuePairView();
int year = dtStart.Date.Year;
int month = dtStart.Date.Month;
int date = dtStart.Date.Day;
int hour = dtStart.TimeOfDay.Hours;
int minute = dtStart.TimeOfDay.Minutes;
decimal decimalTmp = 0;
try
{
if (timeSpacy.ToLower() == "month")
{
model.xAxis_value = year.ToString() + "-" + month.ToString();
decimalTmp = listRows.FindAll(a => a.Field <int>("Year") == year && a.Field <int>("Month") == month).Sum(a => a.Field <int>("Qty"));
}
else if (timeSpacy.ToLower() == "date")
{
model.xAxis_value = year.ToString() + "-" + month.ToString() + "-" + date.ToString();
decimalTmp = listRows.FindAll(a => a.Field <int>("Year") == year && a.Field <int>("Month") == month && a.Field <int>("Date") == date).Sum(a => a.Field <int>("Qty"));
}
else if (timeSpacy.ToLower() == "hour")
{
model.xAxis_value = year.ToString() + "-" + month.ToString() + "-" + date.ToString() + " " + hour.ToString() + ":00:00";
decimalTmp = listRows.FindAll(a => a.Field <int>("Year") == year && a.Field <int>("Month") == month && a.Field <int>("Date") == date && a.Field <int>("Hour") == hour).Sum(a => a.Field <int>("Qty"));
}
else if (timeSpacy.ToLower() == "minute")
{
model.xAxis_value = year.ToString() + "-" + month.ToString() + "-" + date.ToString() + " " + hour.ToString() + ":" + minute.ToString() + ":00";
decimalTmp = listRows.FindAll(a => a.Field <int>("Year") == year && a.Field <int>("Month") == month && a.Field <int>("Date") == date && a.Field <int>("Hour") == hour && a.Field <int>("Minute") == minute).Sum(a => a.Field <int>("Qty"));
}
}
catch (Exception)
{
dtStart = GetNextTimeSpot(dtStart, timeSpacy);
continue;
}
model.yAxis_value = Math.Round(decimalTmp, 2).ToString();
list.Add(model);
dtStart = GetNextTimeSpot(dtStart, timeSpacy);
}
return(list);
}
/// <summary>
/// 根据停机时间统计设备停机时间的原始数据
/// </summary>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="timeSpacy">时间精度</param>
/// <returns>设备停机时间初始数据</returns>
private List <ModelView.DataAnalysisValuePairView> GetEqmJamTimeByTime(DateTime startTime, DateTime endTime, string timeSpacy)
{
string sql = "select reply_time,submit_time from eqm_jam_record where reply_time is not null and (reply_time between @startTime and @endTime) and (submit_time between @startTime and @endTime) order by reply_time asc;";
Dictionary <string, object> pms = new Dictionary <string, object>();
pms.Add("@startTime", startTime);
pms.Add("@endTime", endTime);
DataTable dt = new DataTable();
try
{
dt = DbEngine.QueryTable(sql, pms);
}
catch (Exception)
{
return(null);
}
if (dt.Rows.Count <= 0)
{
return(null);
}
DataTable dtTmp = new DataTable();
DataColumn dc1 = new DataColumn("Year");
DataColumn dc2 = new DataColumn("Month");
DataColumn dc3 = new DataColumn("Date");
DataColumn dc4 = new DataColumn("Hour");
DataColumn dc5 = new DataColumn("Minute");
DataColumn dc6 = new DataColumn("Time");
dtTmp.Columns.Add(dc1);
dtTmp.Columns.Add(dc2);
dtTmp.Columns.Add(dc3);
dtTmp.Columns.Add(dc4);
dtTmp.Columns.Add(dc5);
dtTmp.Columns.Add(dc6);
DateTime dtStart = startTime;
DateTime dtEnd = GetNextTimeSpot(dtStart, timeSpacy);
List <DataRow> dtRows = dt.AsEnumerable().ToList();
while (dtEnd <= endTime)
{
DataRow dr = dtTmp.NewRow();
dr["Year"] = dtStart.Year;
dr["Month"] = dtStart.Month;
dr["Date"] = dtStart.Day;
dr["Hour"] = dtStart.Hour;
dr["Minute"] = dtStart.Minute;
dr["Time"] = CalculateTime(dtRows, dtStart, dtEnd);//精确到分钟后小数点2位
dtStart = GetNextTimeSpot(dtStart, timeSpacy);
dtEnd = GetNextTimeSpot(dtEnd, timeSpacy);
dtTmp.Rows.Add(dr);
}
List <ModelView.DataAnalysisValuePairView> list = new List <ModelView.DataAnalysisValuePairView>();
List <DataRow> listRows = dtTmp.AsEnumerable().ToList();
dtStart = startTime;
while (dtStart <= endTime)
{
ModelView.DataAnalysisValuePairView model = new ModelView.DataAnalysisValuePairView();
string year = dtStart.Date.Year.ToString();
string month = dtStart.Date.Month.ToString();
string date = dtStart.Date.Day.ToString();
string hour = dtStart.TimeOfDay.Hours.ToString();
string minute = dtStart.TimeOfDay.Minutes.ToString();
decimal decimalTmp = 0;
try
{
if (timeSpacy.ToLower() == "month")
{
model.xAxis_value = year.ToString() + "-" + month.ToString();
decimalTmp = listRows.FindAll(a => a.Field <string>("Year") == year && a.Field <string>("Month") == month).Sum(a => decimal.Parse(a.Field <string>("Time")));
}
else if (timeSpacy.ToLower() == "date")
{
model.xAxis_value = year.ToString() + "-" + month.ToString() + "-" + date.ToString();
decimalTmp = listRows.FindAll(a => a.Field <string>("Year") == year && a.Field <string>("Month") == month && a.Field <string>("Date") == date).Sum(a => decimal.Parse(a.Field <string>("Time")));
}
else if (timeSpacy.ToLower() == "hour")
{
model.xAxis_value = year.ToString() + "-" + month.ToString() + "-" + date.ToString() + " " + hour.ToString() + ":00:00";
decimalTmp = listRows.FindAll(a => a.Field <string>("Year") == year && a.Field <string>("Month") == month && a.Field <string>("Date") == date && a.Field <string>("Hour") == hour).Sum(a => decimal.Parse(a.Field <string>("Time")));
}
else if (timeSpacy.ToLower() == "minute")
{
model.xAxis_value = year.ToString() + "-" + month.ToString() + "-" + date.ToString() + " " + hour.ToString() + ":" + minute.ToString() + ":00";
decimalTmp = listRows.FindAll(a => a.Field <string>("Year") == year && a.Field <string>("Month") == month && a.Field <string>("Date") == date && a.Field <string>("Hour") == hour && a.Field <string>("Minute") == minute).Sum(a => decimal.Parse(a.Field <string>("Time")));
}
}
catch (Exception)
{
dtStart = GetNextTimeSpot(dtStart, timeSpacy);
continue;
}
model.yAxis_value = Math.Round(decimalTmp, 2).ToString();
list.Add(model);
dtStart = GetNextTimeSpot(dtStart, timeSpacy);
}
return(list);
}
