public List <CFinishHistory> GetOpFinishHistory(string orderuid)
{
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "SELECT eventMessage,eventTime,finishedQty,failedQty,planQty,jobQty,mesOperator FROM wapMesEventRec WHERE OrderUID = '" + orderuid + "' ORDER BY EventTime";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dthistory = new DataTable();
da.Fill(dthistory);
da.Dispose();
cmd.Connection.Close();
List <CFinishHistory> cFinishHistories = new List <CFinishHistory>();
foreach (DataRow item in dthistory.Rows)
{
CFinishHistory cFinish = new CFinishHistory
{
EventMessage = item["eventMessage"].ToString(),
EventTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(item["eventTime"]), 1),
FinishedQty = Convert.ToDouble(item["finishedQty"]),
FailedQty = Convert.ToDouble(item["failedQty"]),
PlannedQty = Convert.ToDouble(item["planQty"]),
JobQty = Convert.ToDouble(item["jobQty"]),
MesOperator = item["mesOperator"].ToString()
};
cFinishHistories.Add(cFinish);
}
return(cFinishHistories);
}
public List <COrderList> CanDownThisRes_Call(string resname, int dayshift)
{
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "SELECT * FROM User_MesDailyData WHERE resCanUsed like '%" + resname.Split(':')[0] + "%' and datatype = 'P' and dailyDate >='" + DateTime.Now + "' and TaskFinishState < '4' ORDER BY workid,productid,pmresname,pmopname,PlanStartTime Desc";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable data = new DataTable();
da.Fill(data);
da.Dispose();
cmd.Connection.Close();
//获取现有设备上的工单数据
cmd.CommandText = "SELECT pmresname,pmopname,workid FROM User_MesDailyData WHERE pmresname = '" + resname + "' and datatype ='P' and taskfinishstate < 4 and dailydate = '" + DateTime.Now.Date + "' and dayshift = '" + dayshift + "'";
da = new SqlDataAdapter(cmd);
DataTable dtthisrestasks = new DataTable();
da.Fill(dtthisrestasks);
da.Dispose();
cmd.Connection.Close();
//对拉取过来的数据去重
DataTable newdata = data.Clone();
string thisworkid, preworkid = string.Empty;
string thisproduct, preproduct = string.Empty;
string thisresname, preresname = string.Empty;
string thisopname, preopname = string.Empty;
for (int i = 0; i < data.Rows.Count; i++)
{
if (i == 0)
{
preworkid = data.Rows[i]["workid"].ToString();
preproduct = data.Rows[i]["productid"].ToString();
preresname = data.Rows[i]["pmresname"].ToString();
preopname = data.Rows[i]["pmopname"].ToString();
continue;
}
else
{
thisworkid = data.Rows[i]["workid"].ToString();
thisproduct = data.Rows[i]["productid"].ToString();
thisresname = data.Rows[i]["pmresname"].ToString();
thisopname = data.Rows[i]["pmopname"].ToString();
DataRow[] finddr = dtthisrestasks.Select("pmresname = '" + data.Rows[i]["pmresname"].ToString() + "' and pmopname = '"
+ data.Rows[i]["pmopname"].ToString() + "' and workid = '" + data.Rows[i]["workid"].ToString() + "'");
if (finddr.Count() > 0)
{
continue;
}
if (thisworkid == preworkid)
{
//工单一样,比较产品是否一样
if (thisproduct == preproduct)
{
//产品一样 比较设备是否一样
if (thisresname == preresname)
{
//如果设备一样,比较工序是否一样
if (thisopname == preopname)
{
continue;
}
else
{
DataRow dr = newdata.NewRow();
dr.ItemArray = data.Rows[i].ItemArray;
newdata.Rows.Add(dr);
}
}
else
{
//设备不一样 添加
DataRow dr = newdata.NewRow();
dr.ItemArray = data.Rows[i].ItemArray;
newdata.Rows.Add(dr);
}
}
else
{
//如果产品不一样,添加
DataRow dr = newdata.NewRow();
dr.ItemArray = data.Rows[i].ItemArray;
newdata.Rows.Add(dr);
}
}
else
{
//工单不一样,直接添加
DataRow dr = newdata.NewRow();
dr.ItemArray = data.Rows[i].ItemArray;
newdata.Rows.Add(dr);
}
preworkid = thisworkid;
preproduct = thisproduct;
preresname = thisresname;
preopname = thisopname;
}
}
DataView dv = newdata.DefaultView;
dv.Sort = "PlanStartTime ASC";
newdata = dv.ToTable();
List <COrderList> orderlist = new List <COrderList>();
if (newdata.Rows.Count > 0)
{
foreach (DataRow item in newdata.Rows)
{
DataRow checkeddata = PublicFunc.CheckEmptyVal(data, item);
COrderList cOrder = new COrderList()
{
MesResName = checkeddata["MesResName"].ToString(),
MesOpName = checkeddata["MesOpName"].ToString(),
MesOperator = checkeddata["bgPerson"].ToString(),
WorkID = checkeddata["WorkID"].ToString(),
PlanStartTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(checkeddata["PlanStartTime"]), 1),
Planendtime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(checkeddata["Planendtime"]), 1),
PmResName = checkeddata["PmResName"].ToString(),
PmOpName = checkeddata["PmOpName"].ToString(),
ProductID = checkeddata["ProductID"].ToString(),
TaskFinishState = Convert.ToInt32(checkeddata["TaskFinishState"]),
FinishedQty = Convert.ToDouble(checkeddata["FinishedQty"]),
Plannedqty = Convert.ToDouble(checkeddata["Plannedqty"]),
FailedQty = Convert.ToDouble(checkeddata["FailQty"]),
AllFinishedQty = Convert.ToDouble(checkeddata["AllFinishedQty"]),
JobQty = Convert.ToDouble(checkeddata["JobQty"]),
ItemAttr1 = checkeddata["ItemAttr1"].ToString(),
ItemAttr2 = checkeddata["ItemAttr2"].ToString(),
ItemAttr3 = checkeddata["ItemAttr3"].ToString(),
ItemAttr4 = checkeddata["ItemAttr4"].ToString(),
DayShift = Convert.ToInt32(checkeddata["DayShift"]),
ItemDesp = checkeddata["itemDesp"].ToString(),
WorkHours = Convert.ToDouble(checkeddata["workHour"]),
SetupTime = Convert.ToDouble(checkeddata["setupTime"]),
OrderUID = Convert.ToInt32(checkeddata["UID"]),
BomComused = Convert.ToDouble(checkeddata["AllJobTaskqty"]) / Convert.ToDouble(checkeddata["JobQty"]),
CanReport = true,
CanReportQty = Convert.ToDouble(checkeddata["AllJobTaskqty"]) - Convert.ToDouble(checkeddata["AllFinishedQty"]),
ChangeResName = string.Empty
};
orderlist.Add(cOrder);
}
bool hasoutput = false;
foreach (COrderList c in orderlist)
{
if (c.TaskFinishState != 0 && c.TaskFinishState != 3)
{
hasoutput = true;
}
else
{
continue;
}
}
if (hasoutput == true)
{
foreach (COrderList c in orderlist)
{
if (c.TaskFinishState != 0)
{
c.CanReport = true;
}
else
{
c.CanReport = false;
}
}
}
}
//处理重复的数据
return(orderlist);
}
public COpFinish GetOpFinishedData(string jsonedata)
{
COrderList order = JsonConvert.DeserializeObject <COrderList>(jsonedata);
SqlCommand cmd = PmConnections.SchCmd();
//获取计划数据
cmd.CommandText = "SELECT * FROM User_MesDailyData WHERE UID = '" + order.OrderUID + "'";
SqlDataReader rd = cmd.ExecuteReader();
rd.Read();
DateTime planstarttime = Convert.ToDateTime(rd["planStartTime"]);
DateTime planendtime = Convert.ToDateTime(rd["planEndTime"]);
double plannedsetuphours = Convert.ToDouble(rd["setupTime"]);
int setuptime = Convert.ToInt32(plannedsetuphours * 60);
double plannedqty = Convert.ToDouble(rd["plannedQty"]);
double plannedhours = Convert.ToDouble(rd["workHour"]);
double failedqty = Convert.ToDouble(rd["failQty"]);
double finishedqty = Convert.ToDouble(rd["finishedqty"]);
rd.Close();
DateTime plansetupstarttime = planstarttime.AddMinutes(-setuptime);
DateTime plansetupendtime = planstarttime;
//获取实际数据
cmd.CommandText = "SELECT * FROM wapMesEventRec WHERE OrderUID = '" + order.OrderUID + "' order by EventTime";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtmesdata = new DataTable();
da.Fill(dtmesdata);
da.Dispose();
cmd.Connection.Close();
//计算数据
COpFinish returndata = new COpFinish
{
PlanStartTime = PublicFunc.ForMatDateTimeStr(planstarttime, 1),
PlanEndTime = PublicFunc.ForMatDateTimeStr(planendtime, 1),
PlannedHours = plannedhours * 3600,
PlannedQty = plannedqty,
PlanSetupStartTime = PublicFunc.ForMatDateTimeStr(plansetupstarttime, 1),
PlanSetupEndTime = PublicFunc.ForMatDateTimeStr(plansetupendtime, 1),
PlannedSetupHours = plannedsetuphours * 3600,
FinishedQty = finishedqty,
FailedQty = failedqty
};
//开始计算mes数据
//获取mes切换开始时间
double messetuphours = 0;
double meshours = 0;
for (int i = 0; i < dtmesdata.Rows.Count; i++)
{
if (dtmesdata.Rows[i]["EventType"].ToString().Equals("S"))
{
messetuphours += (Convert.ToDateTime(dtmesdata.Rows[i + 1]["EventTime"]) - Convert.ToDateTime(dtmesdata.Rows[i]["EventTime"])).TotalSeconds;
if (dtmesdata.Rows[i + 1]["EventType"].ToString().Equals("E"))
{
returndata.MesSetupStartTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(dtmesdata.Rows[i]["EventTime"]), 1);
returndata.MesSetupEndTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(dtmesdata.Rows[i + 1]["EventTime"]), 1);
}
}
if (dtmesdata.Rows[i]["EventType"].ToString().Equals("E"))
{
if (dtmesdata.Rows[i + 1]["EventType"].ToString().Equals("R"))
{
meshours += (Convert.ToDateTime(dtmesdata.Rows[i + 1]["EventTime"]) - Convert.ToDateTime(dtmesdata.Rows[i]["EventTime"])).TotalSeconds;
returndata.MesStartTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(dtmesdata.Rows[i]["EventTime"]), 1);
returndata.MesEndTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(dtmesdata.Rows[i + 1]["EventTime"]), 1);
}
}
}
returndata.MesSetupHours = Convert.ToDouble(messetuphours.ToString("0.00"));
returndata.MesHours = Convert.ToDouble(meshours.ToString("0.00"));
return(returndata);
}
public List <COrderList> GetUnStartOrderList(string resName)
{
int dayshift = PublicFunc.GetDayShift(resName);
DateTime mesdailydate = PublicFunc.GetDailyDate(resName);
SqlCommand cmd = PmConnections.SchCmd();
cmd.CommandText = "SELECT * FROM User_MesDailyData WHERE pmResName = '" + resName + "' and datatype = 'P' and mesdailyDate ='" + mesdailydate.Date + "' and TaskFinishState < '4' and dayShift = '" + dayshift + "' ORDER BY mesdailyDate,planStartTime";
//cmd.CommandText = "SELECT * FROM User_MesDailyData WHERE pmResName = '" + resName + "' and datatype = 'P'and TaskFinishState < '4' and dayShift = '" + shift + "'ORDER BY dailyDate,planStartTime";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable returndata = new DataTable();
da.Fill(returndata);
da.Dispose();
cmd.Connection.Close();
if (returndata.Rows.Count < 1)
{
return(null);
}
else
{
List <COrderList> LiReturnData = new List <COrderList>();
foreach (DataRow item in returndata.Rows)
{
DataRow checkeddata = PublicFunc.CheckEmptyVal(returndata, item);
COrderList li = new COrderList
{
MesResName = checkeddata["MesResName"].ToString(),
MesOpName = checkeddata["MesOpName"].ToString(),
MesOperator = checkeddata["bgPerson"].ToString(),
WorkID = checkeddata["WorkID"].ToString(),
PlanStartTime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(checkeddata["PlanStartTime"]), 1),
Planendtime = PublicFunc.ForMatDateTimeStr(Convert.ToDateTime(checkeddata["Planendtime"]), 1),
PmResName = checkeddata["PmResName"].ToString(),
PmOpName = checkeddata["PmOpName"].ToString(),
ProductID = checkeddata["ProductID"].ToString(),
TaskFinishState = Convert.ToInt32(checkeddata["TaskFinishState"]),
FinishedQty = Convert.ToDouble(checkeddata["FinishedQty"]),
Plannedqty = Convert.ToDouble(checkeddata["Plannedqty"]),
FailedQty = Convert.ToDouble(checkeddata["FailQty"]),
AllFinishedQty = Convert.ToDouble(checkeddata["AllFinishedQty"]),
JobQty = Convert.ToDouble(checkeddata["JobQty"]),
ItemAttr1 = checkeddata["ItemAttr1"].ToString(),
ItemAttr2 = checkeddata["ItemAttr2"].ToString(),
ItemAttr3 = checkeddata["ItemAttr3"].ToString(),
ItemAttr4 = checkeddata["ItemAttr4"].ToString(),
DayShift = Convert.ToInt32(checkeddata["DayShift"]),
ItemDesp = checkeddata["itemDesp"].ToString(),
WorkHours = Convert.ToDouble(checkeddata["workHour"]),
SetupTime = Convert.ToDouble(checkeddata["setupTime"]),
OrderUID = Convert.ToInt32(checkeddata["UID"]),
BomComused = Convert.ToDouble(checkeddata["AllJobTaskqty"]) / Convert.ToDouble(checkeddata["JobQty"]),
CanReport = true,
CanReportQty = Convert.ToDouble(checkeddata["AllJobTaskqty"]) - Convert.ToDouble(checkeddata["AllFinishedQty"]) - Convert.ToDouble(checkeddata["FailQty"]),
ChangeResName = string.Empty,
ReportTime = Convert.ToDateTime(checkeddata["updateDatetime"]),
JobDemandDay = Convert.ToDateTime(checkeddata["jobDemandDay"]),
ScrappedQty = Convert.ToDouble(checkeddata["ScrappedQty"]),
Ajustment = Convert.ToBoolean(checkeddata["adjustment"]),
UnitPrice = Convert.ToDouble(checkeddata["unitPrice"]),
MesSetupStartTime = Convert.ToDateTime(checkeddata["setupStartTime"]),
MesSetupEndTime = Convert.ToDateTime(checkeddata["setupEndTime"]),
MesStartTime = Convert.ToDateTime(checkeddata["startDateTime"]),
MesEndTime = Convert.ToDateTime(checkeddata["endDateTime"]),
UserComment = checkeddata["userComment"].ToString()
};
LiReturnData.Add(li);
}
//public DateTime MesStartTime { get; set; }
//public DateTime MesEndTime { get; set; }
//public DateTime MesSetupStartTime { get; set; }
//public DateTime MesSetupEndtime { get; set; }
bool hasoutput = false;
foreach (COrderList c in LiReturnData)
{
if (c.TaskFinishState != 0 && c.TaskFinishState != 3)
{
hasoutput = true;
}
else
{
continue;
}
}
if (hasoutput == true)
{
foreach (COrderList c in LiReturnData)
{
if (c.TaskFinishState != 0)
{
c.CanReport = true;
}
else
{
c.CanReport = false;
}
}
}
return(LiReturnData);
}
}