/// <summary> /// 获取排班日期内一个排班类型里面的所有workcell /// </summary> /// <param name="date"></param> /// <param name="shift"></param> /// <returns></returns> public static List <string> GetDistinctBulidPlanBays(DateTime date, int shift, string workcell) { List <string> result = new List <string>(); string sql = $@"select distinct Bay from EKS_T_BulidPlanData where ShiftDate='{date.ToString("yyyy-MM-dd")}' and [shift]='{shift}' and workcell = '{workcell}' and bay <> '' "; try { DataTable dt = sqlser.QueryDataTable(sql, null); if (dt.Rows.Count == 0) { return(result); } foreach (DataRow dr in dt.Rows) { if (dr["Bay"] != DBNull.Value) { result.Add(dr["Bay"].ToString()); } } return(result); } catch (Exception ex) { ErrorBll.LogError("获取Bay异常!", ex); return(result); } }
/// <summary> /// 同步任务 /// </summary> public static void SynTask() { ErrorBll.LogInfo("开始同步生成Task任务", $"任务同步完成!"); // 执行存储过程进行排班 string sql = $@"P_Task_SYN"; DAL.ProcedureParameter[] para = new DAL.ProcedureParameter[] { new DAL.ProcedureParameter("@Status", 0, SqlDbType.Int, 8, ParameterDirection.Output), new DAL.ProcedureParameter("@Msg", "", SqlDbType.VarChar, 50, ParameterDirection.Output) }; para[0].Direction = ParameterDirection.Output; para[1].Direction = ParameterDirection.Output; try { sqlser.Execute(sql, para, true); var result = para[0].Value == null ? -1 : para[0].Value.GetInt(); if (result == 0) { ErrorBll.LogInfo("同步Task任务", $"任务同步完成!"); } else { ErrorBll.LogInfo("同步Task任务", $"任务同步失败!"); } } catch (Exception ex) { ErrorBll.LogError($"同步Task任务异常", ex); } }
/// <summary> /// 获取所有的排班日期 /// </summary> /// <returns></returns> public static List <DateTime> GetDistinctBulidPlanDate() { List <DateTime> result = new List <DateTime>(); string sql = $@"select distinct ShiftDate from EKS_T_BulidPlanData"; try { DataTable dt = sqlser.QueryDataTable(sql, null); if (dt.Rows.Count == 0) { return(result); } foreach (DataRow dr in dt.Rows) { if (dr["ShiftDate"] != DBNull.Value) { result.Add(dr["ShiftDate"].ToDate()); } } return(result); } catch (Exception ex) { ErrorBll.LogError("获取ShiftDate异常!", ex); return(result); } }
/// <summary> /// 获取排班日期内所有的排班类型 /// </summary> /// <returns></returns> public static List <int> GetDistinctBulidPlanShiftType(DateTime date) { List <int> result = new List <int>(); string sql = $@"select distinct [Shift] from EKS_T_BulidPlanData where ShiftDate='{date.ToString("yyyy-MM-dd")}'"; try { DataTable dt = sqlser.QueryDataTable(sql, null); if (dt.Rows.Count == 0) { return(result); } foreach (DataRow dr in dt.Rows) { if (dr["Shift"] != DBNull.Value) { result.Add(dr["Shift"].ToInt()); } } return(result); } catch (Exception ex) { ErrorBll.LogError("获取ShiftType异常!", ex); return(result); } }
/// <summary> /// 插入buildplan数据 /// </summary> /// <param name="item"></param> /// <returns></returns> public static bool InsertBulidPlanData(BuildPlanInfo item) { string sql = $@" INSERT INTO [dbo].[EKS_T_BulidPlanData] ([ID],[BuildPlanID] ,[Workcell],[Bay],[Station],[Model] ,[BuildPlan],[Shift],[ShiftDate],[StartTime] ,[EndTime],[UPH]) VALUES (@ID,@BuildPlanID,@Workcell,@Bay,@Station,@Model, @BuildPlan,@Shift,@ShiftDate,@StartTime,@EndTime,@UPH)"; DAL.ProcedureParameter[] para = new DAL.ProcedureParameter[] { new DAL.ProcedureParameter("@ID", item.ID), new DAL.ProcedureParameter("@BuildPlanID", item.BuildPlanID), new DAL.ProcedureParameter("@Workcell", item.Workcell), new DAL.ProcedureParameter("@Bay", item.Bay), new DAL.ProcedureParameter("@Station", item.Station), new DAL.ProcedureParameter("@Model", item.Model), new DAL.ProcedureParameter("@BuildPlan", item.BuildPlan), new DAL.ProcedureParameter("@Shift", item.Shift), new DAL.ProcedureParameter("@ShiftDate", item.ShiftDate), new DAL.ProcedureParameter("@StartTime", item.StartTime), new DAL.ProcedureParameter("@EndTime", item.EndTime), new DAL.ProcedureParameter("@UPH", item.UPH), }; try { return(sqlser.Execute(sql, para) > 0); } catch (Exception ex) { ErrorBll.LogError("同步buildplan数据时,插入buildplan数据异常!", ex); return(false); } }
public static SerResult UpdataTaskStatus(string taskID) { string sql = $@"if exists ( select 1 from [dbo].[EKS_T_Task] a where a.ID=@TaskID and Feeder is not null and Feeder <> '' and Stencil is not null and Stencil <>'' and DEK_Pallet is not null and DEK_Pallet <> '' and [Profile Board] is not null and [Profile Board] <>'' and Squeegee is not null and Squeegee <>'') begin update [dbo].[EKS_T_Task] set Status='1' where ID=@TaskID end "; ProcedureParameter[] para = new ProcedureParameter[] { new ProcedureParameter("@TaskID", taskID), }; try { new DAL.DbHelper().Execute(sql, para); } catch (Exception ex) { ErrorBll.LogError("更新任务状态异常!", ex); return(SerResult.Create(-1, "系统繁忙!")); } return(SerResult.Success()); }
/// <summary> /// 清理数据 /// </summary> public static void ClearBulidPlanData() { string sql = $@"truncate table EKS_T_BulidPlanData"; try { sqlser.Execute(sql, null); } catch (Exception ex) { ErrorBll.LogError("清除EKS_T_BulidPlanData表数据异常!", ex); } }
public static bool UpdateBuildPlanCostTime() { string sql = $@" update [dbo].[EKS_T_BulidPlanData] set CostTime=0; update [dbo].[EKS_T_BulidPlanData] set CostTime = Round(BuildPlan/UPH/0.9,2) where UPH <> 0 "; try { return(sqlser.Execute(sql, null) > 0); } catch (Exception ex) { ErrorBll.LogError("同步buildplan数据时,更新CostTime异常!", ex); return(false); } }
/// <summary> /// 释放任务,一个小时后自动释放 /// </summary> /// <returns></returns> public static SerResult FreeTask() { string sql = $@" update [dbo].[EKS_T_Task] set Enble =0 where datediff(MINUTE,ExpectedTime ,getdate())>60 and status='1' "; try { new DAL.DbHelper().Execute(sql); } catch (Exception ex) { ErrorBll.LogError("释放任务状态异常!", ex); return(SerResult.Create(-1, "系统繁忙!")); } return(SerResult.Success()); }
public static SerResult UpdataStencil(string taskID, string stencil) { string sql = $@" update [dbo].[EKS_T_Task] set Stencil=@Stencil,StencilCount=1 where ID=@ID "; ProcedureParameter[] para = new ProcedureParameter[] { new ProcedureParameter("@Stencil", stencil), new ProcedureParameter("@ID", taskID), }; try { new DAL.DbHelper().Execute(sql, para); } catch (Exception ex) { ErrorBll.LogError("更新钢网状态异常!", ex); return(SerResult.Create(-1, "系统繁忙!")); } return(SerResult.Success()); }
/// <summary> /// update Feeder Card status /// </summary> /// <param name="taskID"></param> /// <param name="FeederCarNo"></param> /// <returns></returns> public static SerResult UpdataFeederCard(string taskID, string FeederCarNo) { string sql = $@" update [dbo].[EKS_T_Task] set FeederCar=@FeederCar,Feeder='OK' where ID=@ID "; ProcedureParameter[] para = new ProcedureParameter[] { new ProcedureParameter("@FeederCar", FeederCarNo), new ProcedureParameter("@ID", taskID), }; try { new DAL.DbHelper().Execute(sql, para); } catch (Exception ex) { ErrorBll.LogError("更新喂料车状态异常!", ex); return(SerResult.Create(-1, "系统繁忙!")); } return(SerResult.Success()); }
/// <summary> /// bulidplan 数据转换为task /// </summary> public static void ExecuteBuildPlanData2Task() { object locko = new object(); lock (locko) { ErrorBll.LogInfo("开始同步BuildPlan数据", "开始同步BuildPlan数据"); //抓取数据 List <BuildPlanInfo> list = GetSynBulidPlanData(DateTime.Now.AddDays(-3).Date, DateTime.Now.AddDays(1).Date); if (list.Count == 0) { return; } // ClearBulidPlanData(); int allCount = list.Count; int successCount = 0; foreach (var it in list) { if (InsertBulidPlanData(it)) { successCount++; } } ErrorBll.LogInfo("插入BulidPlan数据到本地数据库", $"总记录数:[{allCount}],成功数:[{successCount}]"); //先计算CostTime UpdateBuildPlanCostTime(); //先计算时间,分别计算出 var dates = GetDistinctBulidPlanDate(); foreach (var date in dates) { var types = GetDistinctBulidPlanShiftType(date); foreach (var type in types) { var workcells = GetDistinctBulidPlanWorkcell(date, type); foreach (var wc in workcells) { var bays = GetDistinctBulidPlanBays(date, type, wc); foreach (var bay in bays) { // 执行存储过程进行排班 string sql = $@"P_BuildPlanShiftType2Task"; DAL.ProcedureParameter[] para = new DAL.ProcedureParameter[] { new DAL.ProcedureParameter("@shiftdate", date), new DAL.ProcedureParameter("@shift", type), new DAL.ProcedureParameter("@workcell", wc), new DAL.ProcedureParameter("@bay", bay), new DAL.ProcedureParameter("@Status", -1, SqlDbType.Int, 8, ParameterDirection.Output), new DAL.ProcedureParameter("@Msg", "", SqlDbType.VarChar, 50, ParameterDirection.Output) }; para[4].Direction = ParameterDirection.Output; para[5].Direction = ParameterDirection.Output; try { sqlser.Execute(sql, para, true); var result = para[4].Value == null ? -1 : para[4].Value.GetInt(); if (result == 0) { ErrorBll.LogInfo("计算单个生产计划的开始生产时间", $"[{date.ToString("yyyy-MM-dd")}] 班次:[{type}] workcell:[{wc}] bay:[{bay}] 当前班次的所有生产计划的具体生产时间成功!"); } else { ErrorBll.LogInfo("计算单个生产计划的开始生产时间", $"[{date.ToString("yyyy-MM-dd")}] 班次:[{type}] workcell:[{wc}] bay:[{bay}] 当前班次的所有生产计划的具体生产时间失败,[{para[5].Value}]"); } } catch (Exception ex) { ErrorBll.LogError($"计算单个生产计划的开始生产时间 [{date.ToString("yyyy-MM-dd")}] 班次:[{type}] workcell:[{wc}] bay:[{bay}] 当前班次的所有生产计划的具体生产时间失败,[{para[5].Value}]", ex); } } } } } //正式创建Task SynTask(); } }