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