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