/// <summary>
///任务状态更新， 每隔半分钟扫描一次，判断钢网以及支撑台、刮刀、测温板,feedercar ,feeder 是否配足，过期的将不再处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void timer_RunProcess_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
// 每天晚上11点55分删除三天之前的日志
if (DateTime.Now.Hour == 23 && DateTime.Now.Minute > 55)
{
string deletesql = "delete from EKS_Tlog where LogTime<= DateAdd(d,-3, getdate()) ";
int deleterow = new DAL.DbHelper().Execute(deletesql);
}
try
{
System.Data.DataTable DataUpdate = new System.Data.DataTable();
// status =0, 未配料
string dataupdatesql = "select [ID],[Stencil],[Profile Board],[Squeegee],[DEK_Pallet],[FeederCar],[Feeder] from [EKS_T_Task] where Enble=1 and [Status]=0 and [ExpectedTime]<getdate()";
DataUpdate = new DAL.DbHelper().QueryDataTable(dataupdatesql);
string u_Status = "2";//无料
string u_ID = string.Empty;
string u_Stencil = string.Empty;
string u_ProfileBoard = string.Empty;
string u_Squeegee = string.Empty;
string u_DEK_Pallet = string.Empty;
string u_FeederCar = string.Empty;
string u_Feeder = string.Empty;
for (int i = 0; i < DataUpdate.Rows.Count; i++)
{
u_Status = "2";//无料
u_ID = Convert.ToString(DataUpdate.Rows[i]["ID"]);
u_Stencil = Convert.ToString(DataUpdate.Rows[i]["Stencil"]);
u_ProfileBoard = Convert.ToString(DataUpdate.Rows[i]["Profile Board"]);
u_Squeegee = Convert.ToString(DataUpdate.Rows[i]["Squeegee"]);
u_DEK_Pallet = Convert.ToString(DataUpdate.Rows[i]["DEK_Pallet"]);
u_FeederCar = Convert.ToString(DataUpdate.Rows[i]["FeederCar"]);
u_Feeder = Convert.ToString(DataUpdate.Rows[i]["Feeder"]);
if (!string.IsNullOrWhiteSpace(u_Stencil) && !string.IsNullOrWhiteSpace(u_ProfileBoard) && !string.IsNullOrWhiteSpace(u_Squeegee) &&
!string.IsNullOrWhiteSpace(u_DEK_Pallet) && !string.IsNullOrWhiteSpace(u_FeederCar) && !string.IsNullOrWhiteSpace(u_Feeder))
{
u_Status = "1";//料足
}
string u_sql = "UPDATE [EKS_T_Task] set [Status]=" + u_Status + " where [ID]=" + u_ID;
int temprow = new DAL.DbHelper().Execute(u_sql);
AddLog("判定状态", "ID:" + u_ID, u_sql);
}
}
catch (Exception ex)
{
AddLog("异常出错", "判定状态", ex.ToString());
}
}
/// <summary>
/// 钢网数据抓取
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void timer_RunProcess_Elapsed_Stencil(object sender, System.Timers.ElapsedEventArgs e)
{
try
{
// 在提醒时间内 是否还有任务处于未配料状态 且钢网数量为0
System.Data.DataTable dt_task = new System.Data.DataTable();
string dataupdatesql = "select [ID],[Stencil],[Profile Board],[Model],[Tool Side],[From_Tool Side],[From_Model] from [EKS_T_Task] where Enble=1 and [StencilCount]=0 and DateAdd(N, -1*[RemindPreTime], [ExpectedTime])<=getdate() and [Status]=0 and [ExpectedTime]>=getdate()";
dt_task = new DAL.DbHelper().QueryDataTable(dataupdatesql);
if (dt_task.Rows.Count == 0)
{
return;
}
// 获取所有的钢网型号
System.Data.DataTable dt_Model = new System.Data.DataTable();
string dt_Modelsql = "select distinct [Model] from [EKS_T_Task] where Enble=1 and [StencilCount]=0 and DateAdd(N, -1*[RemindPreTime], [ExpectedTime])<=getdate() and [Status]=0 and [ExpectedTime]>=getdate()";
dt_Model = new DAL.DbHelper().QueryDataTable(dt_Modelsql);
if (dt_Model.Rows.Count == 0)
{
return;
}
AddLog("当前分析(Stencil)的任务", "任务个数：" + dt_task.Rows.Count.ToString(), dataupdatesql);
DataTable DT_All = new DataTable();
#region For 找到所有的钢网信息
for (int i = 0; i < dt_Model.Rows.Count; i++)
{
// 找到型号相匹配的钢网，并且通过钢网的工具ID 把相关的几个扩展属性都检索出来（客户ID，库位ID，板面类型（B/T面）,modelID）
string _Model = Convert.ToString(dt_Model.Rows[i][0]);
string cmdText_Part = @"select M.ToolID,P.ProcessFieldName, M.ProcessFieldValue from dbo.T_Tools_Attribute " +
"as M inner join dbo.T_ProcessField as P on M.ProcessFieldID=P.ProcessFieldID " +
"where ToolID in (select distinct ToolID from " +
"dbo.T_Tools_Attribute where ProcessFieldValue like '%" + _Model + "%' ) " +
"and P.ProcessFieldName in ('Customer','Slot No','Model','Tool Side')"; //'Serial Number',工具-》工具类型-》工具制程-》制程相关的扩展属性-》扩展属性的值 ，所有扩展属性都为此工具的属性
DataTable dt_Part = SqlHelper.GetDataTableOfRecord(cmdText_Part);
dt_Part.TableName = "Data";
// 找到库存中的钢网(工具类型，工具SN 工具状态)
string cmdText_Main = @"select M.ToolID,T.ToolTypeName, M.ToolSN,P.ProcessDesc as 'Status',M.LastUpdatedDate from dbo.T_Tools as M " + //M.CustomerID,
"inner join dbo.T_Process P on M.Status=P.ProcessID inner join dbo.T_Type T on M.ToolTypeID=T.ToolTypeID " +
"where P.ProcessDesc='Storage' and M.ToolID in (select distinct ToolID from " +
"dbo.T_Tools_Attribute where ProcessFieldValue like '%" + _Model + "%' )";
DataTable dt_Main = SqlHelper.GetDataTableOfRecord(cmdText_Main);
// 增加6列数据
string[] NewField = new string[] { "Model", "Slot No", "Tool Side", "Customer", "Isuser", "IncludeModel" };
for (int j = 0; j < NewField.Length; j++)
{
dt_Main.Columns.Add(NewField[j]);
}
for (int k = 0; k < dt_Main.Rows.Count; k++) // 一行一行遍历
{
for (int j = 0; j < NewField.Length; j++) // 遍历新加的列
{
string fieldName = NewField[j];
if (fieldName == "Isuser")
{
dt_Main.Rows[k][fieldName] = 0;
continue;
}
var rows = dt_Part.Select("ToolID=" +
Convert.ToString(dt_Main.Rows[k]["ToolID"]) + " and ProcessFieldName='" + fieldName + "'");
if (rows.Length > 0)
{
if (fieldName == "Model")
{
dt_Main.Rows[k][fieldName] = _Model;
dt_Main.Rows[k]["IncludeModel"] = rows[0]["ProcessFieldValue"];// 取第一个值存入
continue;
}
dt_Main.Rows[k][fieldName] = rows[0]["ProcessFieldValue"];// 取第一个值存入
continue;
}
}
}
if (DT_All == null)
{
DT_All = dt_Main.Copy();
}
else
{
DT_All.Merge(dt_Main);
}
}
#endregion
#region 进行钢网的分配
Random ra = new Random();
for (int i = 0; i < dt_task.Rows.Count; i++)
{
string id = Convert.ToString(dt_task.Rows[i]["ID"]);
string Model = Convert.ToString(dt_task.Rows[i]["Model"]);
string Stencil_Slot_No = "";
string Stencil_Models = "";
string _StencilCount = "0";
string _stencil_json = "";
try
{
var _rows_all = DT_All.Select("Model='" + Model + "' and ToolTypeName='Stencil'");//查找所有的钢网
if (_rows_all.Length == 0)
{
continue;
}
var _rows = DT_All.Select("Isuser=0 and Model='" + Model + "' and ToolTypeName='Stencil'"); // 查找未使用的钢网
if (_rows.Length > 0)
{
int num = ra.Next(0, _rows.Length - 1);//在未使用的钢网中随机取一个。
Stencil_Slot_No = Convert.ToString(_rows[num]["Slot No"]);
Stencil_Models = Convert.ToString(_rows[num]["IncludeModel"]);
_rows[num]["Isuser"] = 1;
}
DataTable _stencil_db = ToDataTable(_rows_all);
_stencil_db.TableName = "dt";
_stencil_json = ConvertJson.ToJson(_stencil_db);
_StencilCount = _rows_all.Length.ToString();
}
catch (Exception ex)
{
AddLog("异常出错", "Stencil数据分析", ex.ToString());
}
if (Stencil_Slot_No.Length > 0 && _StencilCount != "0")// 如果分配了库位
{
string sql = "UPDATE [EKS_T_Task] set [Stencil]='" + Stencil_Slot_No + "',[Stencil_Models]='" + Stencil_Models + "',[Stencil_Detail]='" + _stencil_json + "',[StencilCount]=" + _StencilCount + " where [ID]=" + id;
new DAL.DbHelper().Execute(sql);
AddLog("保存数据", "Stencil ID:" + id.ToString(), sql);
}
}
#endregion
}
catch (Exception ex)
{
AddLog("异常出错", "Stencil", ex.ToString());
}
}
/// <summary>
/// 根据状态获取
/// </summary>
/// <param name="columnDeviceCount">工具数量</param>
/// <param name="columnDevice">工具的名称</param>
/// <param name="columnDevice_Share">工具是否共享</param>
/// <param name="columnDevice_Detail">工具的详情</param>
/// <param name="columnDevice_Models">工具的型号</param>
/// <param name="ToolTypeName">工具类型</param>
void processForSpecial(string columnDeviceCount, string columnDevice, string columnDevice_Share, string columnDevice_Detail, string columnDevice_Models, string ToolTypeName)
{
try
{
System.Data.DataTable dt_task = new System.Data.DataTable();
// 第一步查看当前任务某一工具所分配的数量是否为0，且当前任务状态为未分配，并且在提醒时间内(提醒时间是配置在bay 信息表里面的)
string dataupdatesql = "select * from [EKS_T_Task] where Enble=1 and [" + columnDeviceCount + "]=0 and DateAdd('n', -1*[RemindPreTime], [ExpectedTime])<=getdate() and [Status]=0 and [ExpectedTime]>=getdate()";
dt_task = new DAL.DbHelper().QueryDataTable(dataupdatesql);
if (dt_task.Rows.Count == 0)
{
return;
}
#region 确认是否跳过
for (int i = 0; i < dt_task.Rows.Count; i++)
{
string _Share = Convert.ToString(dt_task.Rows[i][columnDevice_Share]);
if (_Share != "0")
{
string sql = "UPDATE [EKS_T_Task] set [" + columnDevice + "]='" + "OK" + "',[" + columnDevice_Detail + "]='" + "share" + "',[" + columnDeviceCount + "]=" + "1" + " where [ID]=" + Convert.ToString(dt_task.Rows[i]["ID"]);
new DAL.DbHelper().Execute(sql);
}
}
#endregion
// 执行跳过步骤后 再一次判断是否有符合条件的任务
dt_task = new DAL.DbHelper().QueryDataTable(dataupdatesql);
if (dt_task.Rows.Count == 0)
{
return;
}
// 查找去重后的型号信息
System.Data.DataTable dt_Model = new System.Data.DataTable();
string dt_Modelsql = "select distinct [Model] from [EKS_T_Task] where Enble=1 and [" + columnDeviceCount + "]=0 and DateAdd('n', -1*[RemindPreTime], [ExpectedTime])<=getdate() and [Status]=0 and [ExpectedTime]>=getdate()";
dt_Model = new DAL.DbHelper().QueryDataTable(dt_Modelsql);
if (dt_Model.Rows.Count == 0)
{
return;
}
AddLog("当前分析(" + columnDevice + ")的任务", "任务个数：" + dt_task.Rows.Count.ToString(), dataupdatesql);
DataTable DT_All = new DataTable();
#region 与钢网一样的处理逻辑，首先根据钢网的型号获取到所有的工具ID，再根据工具ID取取所有的扩展属性。然后在扩展属性中查找出我们所需要的数据并存在DT_All 表中
for (int i = 0; i < dt_Model.Rows.Count; i++)
{
string _Model = Convert.ToString(dt_Model.Rows[i][0]);
string cmdText_Part = @"select M.ToolID,P.ProcessFieldName, M.ProcessFieldValue from dbo.T_Tools_Attribute " +
"as M inner join dbo.T_ProcessField as P on M.ProcessFieldID=P.ProcessFieldID " +
"where ToolID in (select distinct ToolID from " +
"dbo.T_Tools_Attribute where ProcessFieldValue like '%" + _Model + "%' ) " +
"and P.ProcessFieldName in ('Customer','Slot No','Model','Tool Side')"; //'Serial Number',
DataTable dt_Part = SqlHelper.GetDataTableOfRecord(cmdText_Part);
dt_Part.TableName = "Data";
string cmdText_Main = @"select M.ToolID,T.ToolTypeName, M.ToolSN,P.ProcessDesc as 'Status',M.LastUpdatedDate from dbo.T_Tools as M " + //M.CustomerID,
"inner join dbo.T_Process P on M.Status=P.ProcessID inner join dbo.T_Type T on M.ToolTypeID=T.ToolTypeID " +
"where P.ProcessDesc='Storage' and M.ToolID in (select distinct ToolID from " +
"dbo.T_Tools_Attribute where ProcessFieldValue like '%" + _Model + "%' )";
DataTable dt_Main = SqlHelper.GetDataTableOfRecord(cmdText_Main);
//给主表增加6列
string[] NewField = new string[] { "Model", "Slot No", "Tool Side", "Customer", "Isuser", "IncludeModel" };
for (int j = 0; j < NewField.Length; j++)
{
dt_Main.Columns.Add(NewField[j]);
}
//填充六列数据
for (int k = 0; k < dt_Main.Rows.Count; k++)
{
for (int j = 0; j < NewField.Length; j++)
{
//Isuser 赋值为0
string fieldName = NewField[j];
if (fieldName == "Isuser")
{
dt_Main.Rows[k][fieldName] = 0;
continue;
}
//从part表中查询数据
var rows = dt_Part.Select("ToolID=" +
Convert.ToString(dt_Main.Rows[k]["ToolID"]) + " and ProcessFieldName='" + fieldName + "'");
if (rows.Length > 0)
{
//如果为model
if (fieldName == "Model")
{
dt_Main.Rows[k][fieldName] = _Model;
dt_Main.Rows[k]["IncludeModel"] = rows[0]["ProcessFieldValue"];
continue;
}
dt_Main.Rows[k][fieldName] = rows[0]["ProcessFieldValue"];
continue;
}
}
}
if (DT_All == null)
{
DT_All = dt_Main.Copy();
}
else
{
DT_All.Merge(dt_Main);
}
}
#endregion
Random ra = new Random();
for (int i = 0; i < dt_task.Rows.Count; i++)
{
string id = Convert.ToString(dt_task.Rows[i]["ID"]);
string Model = Convert.ToString(dt_task.Rows[i]["Model"]);
string Slot_No = "";
string Device_Models = "";
string DeviceCount = "0";
string json = "";
try
{
var _rows_all = DT_All.Select("Model='" + Model + "' and ToolTypeName='" + ToolTypeName + "'");
var _rows = DT_All.Select("Isuser=0 and Model='" + Model + "' and ToolTypeName='" + ToolTypeName + "'");
if (_rows.Length > 0)
{
int num = ra.Next(0, _rows.Length - 1);
Slot_No = Convert.ToString(_rows[num]["Slot No"]);
Device_Models = Convert.ToString(_rows[num]["IncludeModel"]);
_rows[num]["Isuser"] = 1;
}
if (_rows_all.Length == 0)
{
continue;
}
DataTable _temp_db = ToDataTable(_rows_all);
_temp_db.TableName = "dt";
json = ConvertJson.ToJson(_temp_db);
DeviceCount = _rows_all.Length.ToString();
}
catch (Exception ex)
{
AddLog("异常出错", columnDevice + "数据分析", ex.ToString());
}
string sql = "UPDATE [EKS_T_Task] set [" + columnDevice + "]='" + Slot_No + "',[" + columnDevice_Models + "]='" + Device_Models + "',[" + columnDevice_Detail + "]='" + json + "',[" + columnDeviceCount + "]=" + DeviceCount + " where [ID]=" + id;
new DAL.DbHelper().Execute(sql);
AddLog("保存数据", columnDevice + " ID:" + id.ToString(), sql);
}
}
catch (Exception ex)
{
AddLog("异常出错", "Squeegee", ex.ToString());
}
}
