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