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