private int GetQty(string TenantID, string LineId, string OrderNo) { int result = 0; try { minimes_hoursreportService reportService = new minimes_hoursreportService(); List <minimes_hoursreport> reportList = reportService.GetModelList( ParamQuery.Instance() .AndWhere("LineId", LineId) .AndWhere("OrderNo", OrderNo) .AndWhere("TenantID", TenantID) .AndWhere("PlanDate", DateTime.Now.Date) ); result = reportList.Sum(v => v.Qty); } catch { } return(result); }
public dynamic Get(RequestWrapper query) { try { query.LoadSettingXmlString(@" <settings defaultOrderBy='StartTime DESC'> <select>DISTINCT LineId,LineName,WorkShopName,WorkShopId</select> <from>minimes_hoursreport</from> <where defaultForAll='true' defaultCp='equal' defaultIgnoreEmpty='true' > <field name='LineId' cp='equal'></field> <field name='StartTime' cp='DateRange'></field> <field name='WorkShopId' cp='equal'></field> </where> </settings>"); var service = new minimes_hoursreportService(); var pQuery = query.ToParamQuery(); string StartTime = query["StartTime"]; if (string.IsNullOrEmpty(StartTime)) { StartTime = DateTime.Now.ToString("yyyy-MM-dd"); } var result = service.GetDynamicListWithPaging(pQuery); foreach (var item in result.rows) { using (var db = Db.Context("MiniMES")) { //白班开始时间 string DayShiftBeginDate = Convert.ToDateTime(StartTime).AddDays(1).AddHours(-8).ToString("yyyy-MM-dd") + " 08:00:00"; //白班结束时间 string DayShiftEndDate = Convert.ToDateTime(StartTime).AddDays(1).AddHours(-8).ToString("yyyy-MM-dd") + " 20:00:00"; //晚班开始时间 string EveningShiftBeginDate = Convert.ToDateTime(StartTime).AddDays(1).AddHours(-8).ToString("yyyy-MM-dd") + " 20:00:00"; //晚班开始时间 string EveningShiftEndDate = Convert.ToDateTime(DayShiftBeginDate).AddDays(1).ToString("yyyy-MM-dd hh:mm:ss"); int DayShiftQty = 0; int DayShiftTime = 0; int DayShiftStopCount = 0; int DayShiftStopTime = 0; int EveningShiftQty = 0; int EveningShiftTime = 0; int EveningShiftStopCount = 0; int EveningShiftStopTime = 0; List <dynamic> DayShiftList = new List <dynamic>(); List <dynamic> DayShiftList1 = new List <dynamic>(); List <dynamic> EveningShiftList = new List <dynamic>(); List <dynamic> EveningShiftList1 = new List <dynamic>(); #region 白班 ((IDictionary <string, object>)item).Add("StartTime", StartTime); //班别 ((IDictionary <string, object>)item).Add("DayShift", "白班"); string DayShiftSql = string.Format(@" SELECT SUM(Qty) as 'DayShiftQty',round(sum(TIMESTAMPDIFF(SECOND,StartTime,LastTime))/60,2) as 'DayShiftTime' FROM minimes_hoursreport WHERE LineId = '{0}' AND StartTime >= '{1}' AND LastTime <= '{2}'", item.LineId, DayShiftBeginDate, DayShiftEndDate); DayShiftList = db.Sql(DayShiftSql).QueryMany <dynamic>(); if (DayShiftList.Count != 0) { DayShiftQty = Convert.ToInt32(DayShiftList[0].DayShiftQty); DayShiftTime = Convert.ToInt32(DayShiftList[0].DayShiftTime); } //产能 ((IDictionary <string, object>)item).Add("DayQty", DayShiftQty); //生产时间 ((IDictionary <string, object>)item).Add("DayTime", DayShiftTime); string DayShiftSql1 = string.Format(@"select round(sum(TIMESTAMPDIFF(SECOND,StartTime,EndTime))/60,2) as DayShiftStopTime,count(*) as DayShiftStopCount from minimes_status_record where LineId='{0}' and State=0 and StartTime>='{1}' and EndTime<= '{2}'GROUP BY LineName;", item.LineId, DayShiftBeginDate, DayShiftEndDate); DayShiftList1 = db.Sql(DayShiftSql1).QueryMany <dynamic>(); if (DayShiftList1.Count != 0) { DayShiftStopCount = Convert.ToInt32(DayShiftList1[0].DayShiftStopCount); DayShiftStopTime = Convert.ToInt32(DayShiftList1[0].DayShiftStopTime); } //停机次数 ((IDictionary <string, object>)item).Add("DayStopCount", DayShiftStopCount); //停机时间 ((IDictionary <string, object>)item).Add("DayStopTime", DayShiftStopTime); #endregion #region 晚班 //班别 ((IDictionary <string, object>)item).Add("EveningShift", "晚班"); string EveningShiftSql = string.Format(@" SELECT SUM(Qty) as 'EveningShiftQty',round(sum(TIMESTAMPDIFF(SECOND,StartTime,LastTime))/60,2) as 'EveningShiftTime' FROM minimes_hoursreport WHERE LineId = '{0}' AND StartTime >= '{1}' AND LastTime <= '{2}'", item.LineId, EveningShiftBeginDate, EveningShiftEndDate); EveningShiftList = db.Sql(EveningShiftSql).QueryMany <dynamic>(); if (EveningShiftList.Count != 0) { EveningShiftQty = Convert.ToInt32(EveningShiftList[0].EveningShiftQty); EveningShiftTime = Convert.ToInt32(EveningShiftList[0].EveningShiftTime); } //产能 ((IDictionary <string, object>)item).Add("EveningQty", EveningShiftQty); //生产时间 ((IDictionary <string, object>)item).Add("EveningTime", EveningShiftTime); string EveningShiftSql1 = string.Format(@"select round(sum(TIMESTAMPDIFF(SECOND,StartTime,EndTime))/60,2) as EveningShiftStopTime,count(*) as EveningShiftStopCount from minimes_status_record where LineId='{0}' and State=0 and StartTime>='{1}' and EndTime<= '{2}'GROUP BY LineName;", item.LineId, EveningShiftBeginDate, EveningShiftEndDate); EveningShiftList1 = db.Sql(EveningShiftSql1).QueryMany <dynamic>(); if (EveningShiftList1.Count != 0) { EveningShiftStopCount = Convert.ToInt32(EveningShiftList1[0].EveningShiftStopCount); EveningShiftStopTime = Convert.ToInt32(EveningShiftList1[0].EveningShiftStopTime); } //停机次数 ((IDictionary <string, object>)item).Add("EveningStopCount", EveningShiftStopCount); //停机时间 ((IDictionary <string, object>)item).Add("EveningStopTime", EveningShiftStopTime); #endregion } } return(result); } catch (Exception ex) { throw ex; } }
public dynamic GetReport(RequestWrapper query) { query.LoadSettingXmlString(@" <settings defaultOrderBy='OrderNo ASC'> <select>OrderNo,DATE_FORMAT(StartTime, '%Y-%m-%d') StartTime,LineId,LineName,Max(ProductCode) ProductCode,Max(ProductName) ProductName,'0' PlanCount,SUM(Qty) SumCount,'0' NGCount,'' NGRate</select> <from>minimes_hoursreport</from> <where defaultForAll='true' defaultCp='equal' defaultIgnoreEmpty='true' > <field name='minimes_hoursreport.StartTime' cp='DateRange'></field> <field name='minimes_hoursreport.LineId' cp='equal'></field> <field name='minimes_hoursreport.ProductCode' cp='equal'></field> <field name='minimes_hoursreport.ProductName' cp='equal'></field> <field name='minimes_hoursreport.OrderNo' cp='equal'></field> </where> </settings>"); var pQuery = query.ToParamQuery(); pQuery.GroupBy("DATE_FORMAT(StartTime,'%Y-%m-%d'),LineName,LineId,OrderNo "); pQuery.OrderBy("DATE_FORMAT(StartTime,'%Y-%m-%d'),LineName,LineId,OrderNo"); var service = new minimes_hoursreportService(); var result = service.GetDynamicListWithPaging(pQuery); if (result != null && result.rows != null && result.rows.Count != 0) { ParamQuery orderparam = new ParamQuery(); string orderWhere = string.IsNullOrEmpty(pQuery.GetData().WhereSql) ? "1=1" : pQuery.GetData().WhereSql; orderparam.Select(" minimes_order_record.OrderNo,minimes_order_record.PlanQty").From("minimes_hoursreport join minimes_order_record on minimes_hoursreport.OrderNo=minimes_order_record.OrderNo").AndWhere(orderWhere + " and 1", "1"); var orderList = service.GetDynamicList(orderparam); List <dynamic> allcheck = new List <dynamic>(); using (var db = Db.Context("QualityControl")) { ParamQuery paramQuery = new ParamQuery(); if (!string.IsNullOrEmpty(query["StartTime"])) { paramQuery.AndWhere("WorkDate", query["StartTime"], Cp.DateRange); } string where = string.IsNullOrEmpty(paramQuery.GetData().WhereSql) ? "1=1" : paramQuery.GetData().WhereSql; string sql = $"SELECT WorkNO, DATE_FORMAT(WorkDate, '%Y-%m-%d') WorkDate, LineCode, MAX(ProductCode) ProductCode, MAX(ProductName) ProductName, ProductType, MAX(PlanCount) PlanCount, SUM(ItemCount) ItemCount from allproductcheck where CheckType=1 AND {where} GROUP BY WorkNO, LineCode, ProductType, DATE_FORMAT(WorkDate, '%Y-%m-%d') order by DATE_FORMAT(WorkDate, '%Y-%m-%d'),WorkNO "; allcheck = db.Sql(sql).QueryMany <dynamic>(); } List <dynamic> dsLine = ApiDataSource.GetLineList(SysHelper.GetTenantId(), null, null, null, null).ToObject <List <dynamic> >(); foreach (var item in result.rows) { string OrderNo = item.OrderNo; string StartTime = item.StartTime; string LineId = item.LineId; string LineCode = ""; if (dsLine != null && dsLine.Count > 0) { List <dynamic> line = dsLine.FindAll(o => (Convert.ToString(o.LineId).Equals(LineId))); if (line != null && line.Count > 0) { LineCode = line[0].LineCode; } } double PlanCount = 0; double SumCount = 0; double NGCount = 0; string NGRate = "0%"; double.TryParse(Convert.ToString(item.SumCount), out SumCount); if (!string.IsNullOrEmpty(OrderNo) && allcheck != null && allcheck.Count > 0) { List <dynamic> findCheck = allcheck.FindAll(o => (Convert.ToString(o.WorkNO).Equals(OrderNo) && Convert.ToString(o.LineCode).Equals(LineCode) && Convert.ToString(o.WorkDate).Equals(StartTime) && Convert.ToString(o.ProductType).Equals("0"))); if (findCheck != null && findCheck.Count > 0) { NGCount = Convert.ToDouble(findCheck[0].ItemCount); } } if (!string.IsNullOrEmpty(OrderNo) && orderList != null && orderList.Count > 0) { List <dynamic> findCheck = orderList.FindAll(o => (Convert.ToString(o.OrderNo).Equals(OrderNo))); if (findCheck != null && findCheck.Count > 0) { PlanCount = Convert.ToDouble(findCheck[0].PlanQty); } } item.PlanCount = PlanCount; item.SumCount = SumCount; item.NGCount = NGCount; if (SumCount != 0 && NGCount != 0) { item.NGRate = ((NGCount / SumCount) * 100).ToString("0.00") + "%"; } else { item.NGRate = NGRate; } } } return(result); }