using System; using System.Collections.Generic; using System.Text; using System.Data; namespace DAL { public class ClsSc_ICMOAutoSortBill { public string MvarItemKey; public string MvarReportTitle; public long HRelationInterID; public List oDetailColl = new List(); public List MainColl = new List(); SQLHelper.ClsCN oCn = new SQLHelper.ClsCN(); public bool FillTag=false; public ClsSc_ICMOAutoSortBill() { MvarItemKey = "3708"; MvarReportTitle = "生产计划排程"; } #region 固定代码 ~ClsSc_ICMOAutoSortBill() { //DetailColl = null; MainColl = null; } //删除子表 public void DeleteBillSub(Int64 lngBillKey) { oCn.RunProc("Delete From Sc_ICMOAutoSortBillSub where HInterID=" + lngBillKey.ToString()); } //删除主表 public void DeleteBillMain(Int64 lngBillKey) { oCn.RunProc("Delete From Sc_ICMOAutoSortBillMain where HInterID=" + lngBillKey.ToString()); } //删除单据 public bool DeleteBill(Int64 lngBillKey, ref string sReturn) { try { oCn.BeginTran(); //删除关联 //删除明细表 DeleteBillSub(lngBillKey); //删除主表 DeleteBillMain(lngBillKey); sReturn = "删除单据成功！"; oCn.Commit(); return true; } catch (Exception e) { sReturn = e.Message; oCn.RollBack(); return false; } } #endregion //新增单据 public bool AddBill(Int64 sDeptID,ref string sReturn) { try { long lInterID = 0; string sBillNo = ""; //得到mainid HRelationInterID = DBUtility.ClsPub.CreateBillID(MvarItemKey, ref DBUtility.ClsPub.sExeReturnInfo); //若MAINDI重复则重新获取 oCn.BeginTran(); //按部门删除 oCn.RunProc("update Sc_ICMOAutoSortBillMain set HDeleteMan='System',HDeleteDate=getdate() where HDeleteMan='' and HDeptID=" + sDeptID.ToString()); // //主表 foreach (Model.ClsSc_ICMOAutoSortBillMain oMain in MainColl) { //lInterID = DBUtility.ClsPub.CreateBillID(MvarItemKey, ref DBUtility.ClsPub.sExeReturnInfo); oCn.RunProc("Insert Into Sc_ICMOAutoSortBillMain " + "(HYear,HPeriod,HInterID,HBillNo,HBillType,HDate" + ",HDeptID,HMaterID,HSourceID,HUnitID,HSplitNo,HYX" + ",HPlanBeginDate,HPlanEndDate,HPlanQty,HSplitPlanQty,HSplitRelationQty,HRelationQty" + ",HKTQ,HWorkQty,HReadyTimes,HRemark,HMaker,HMakeDate,HSourceInterID" + ",HSourceEntryID,HSourceBillNo,HSourceBillType,HICMOInterID,HICMOBillNo,HSeOrderBillNo" + ",HSeOrderInterID,HSeOrderEntryID,HRelationInterID,HWorkerID,HGroupID" + ") " + " values ('" + oMain.HYear.ToString() + "','" + oMain.HPeriod.ToString() + "'," + oMain.HInterID.ToString() + ",'" + oMain.HBillNo.Trim() + "','" + MvarItemKey + "','" + oMain.HDate.ToShortDateString() + "'" + "," + oMain.HDeptID.ToString() + "," + oMain.HMaterID.ToString() + "," + oMain.HSourceID.ToString() + "," + oMain.HUnitID.ToString() + ",'" + oMain.HSplitNo.Trim() + "'," + oMain.HYX.ToString() + ",'" + oMain.HPlanBeginDate.ToShortDateString() + "','" + oMain.HPlanEndDate.ToShortDateString() + "'," + oMain.HPlanQty.ToString() + "," + oMain.HSplitPlanQty.ToString() + "," + oMain.HSplitRelationQty.ToString() + "," + oMain.HRelationQty.ToString() + "," + DBUtility.ClsPub.BoolToString(oMain.HKTQ) + "," + oMain.HWorkQty.ToString() + "," + oMain.HReadyTimes.ToString() + ",'" + oMain.HRemark + "','" + DBUtility.ClsPub.CurUserName + "',getdate()," + oMain.HSourceInterID.ToString() + "," + oMain.HSourceEntryID.ToString() + ",'" + oMain.HSourceBillNo + "','" +oMain.HSourceBillType + "'," + oMain.HICMOInterID.ToString() + ",'" + oMain.HICMOBillNo.Trim() + "','" + oMain.HSeOrderBillNo.Trim() + "'" + "," + oMain.HSeOrderInterID.ToString() + "," + oMain.HSeOrderEntryID.ToString() + "," + HRelationInterID.ToString() + "," + oMain.HWorkerID.ToString() + "," + oMain.HGroupID.ToString() + ") "); //插入子表 foreach (Model.ClsSc_ICMOAutoSortBillSub oSub in oMain.DetailColl) { oCn.RunProc("Insert into Sc_ICMOAutoSortBillSub " + " (HInterID,HEntryID,HMasterDate,HDate" + ",HQty,HStatusTag,HiForeColor,HiBackColor" + ",HRemark,HCloseMan,HCloseType" + ") " + " values (" + oMain.HInterID.ToString() + "," + oSub.HEntryID.ToString() + ",'" + oSub.HMasterDate.ToShortDateString() + "','" + oSub.HDate.ToShortDateString() + "'" + "," +DBUtility.ClsPub.isSingle(oSub.HQty) + "," + oSub.HStatusTag.ToString() + ",'" + oSub.HiForeColor.ToString() + "','" + oSub.HiBackColor.ToString() + "','" + oSub.HRemark + "','" + oSub.HCloseMan + "'," + DBUtility.ClsPub.BoolToString(oSub.HCloseType) + ") "); } } sReturn = "新增单据成功！"; oCn.Commit(); ////存入生产状态表 oCn.RunProc("exec h_p_Sc_ICMOBillStatus_Add " + sDeptID.ToString()); // return true; } catch (Exception e) { sReturn = e.Message; oCn.RollBack(); return false; } } //显示单据 public bool ShowBill(Int64 lngBillKey, ref string sReturn) { try { //查询主表 DataSet Ds; DataSet DsSub; Ds = oCn.RunProcReturn("Select * from Sc_ICMOAutoSortBillMain Where HRelationInterID=" + lngBillKey.ToString() + " and HICMOInterID in (select HInterID from h_v_K3_ICMOBillUnCloseList) ", "Sc_ICMOAutoSortBillMain"); if(Ds.Tables[0].Rows.Count==0) { sReturn = "单据未找到！"; return false; } //赋值 MainColl.Clear(); for (int i = 0; i < Ds.Tables[0].Rows.Count; i++) { Model.ClsSc_ICMOAutoSortBillMain oMain = new Model.ClsSc_ICMOAutoSortBillMain(); oMain.HRelationInterID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[i]["HRelationInterID"]); oMain.HYear = DBUtility.ClsPub.isInt(Ds.Tables[0].Rows[i]["HYear"]); oMain.HPeriod = DBUtility.ClsPub.isInt(Ds.Tables[0].Rows[i]["HPeriod"] ); oMain.HInterID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[i]["HInterID"] ); oMain.HBillNo = Ds.Tables[0].Rows[i]["HBillNo"].ToString(); oMain.HDate = Convert.ToDateTime(Ds.Tables[0].Rows[i]["HDate"].ToString().Trim()); oMain.HDeptID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[i]["HDeptID"].ToString()); oMain.HMaterID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[i]["HMaterID"].ToString()); oMain.HGroupID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[i]["HGroupID"].ToString()); oMain.HWorkerID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[i]["HWorkerID"].ToString()); oMain.HUnitID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[i]["HUnitID"].ToString()); oMain.HSourceID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[i]["HSourceID"]); oMain.HSplitNo = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[i]["HSplitNo"].ToString()); oMain.HYX = DBUtility.ClsPub.isInt(Ds.Tables[0].Rows[i]["HYX"]); oMain.HPlanBeginDate = Convert.ToDateTime(Ds.Tables[0].Rows[i]["HPlanBeginDate"]); oMain.HPlanEndDate = Convert.ToDateTime(Ds.Tables[0].Rows[i]["HPlanEndDate"]); oMain.HPlanQty = DBUtility.ClsPub.isDoule(Ds.Tables[0].Rows[i]["HPlanQty"]); oMain.HSplitPlanQty = DBUtility.ClsPub.isDoule(Ds.Tables[0].Rows[i]["HSplitPlanQty"]); oMain.HSplitRelationQty = DBUtility.ClsPub.isDoule(Ds.Tables[0].Rows[i]["HSplitRelationQty"]); oMain.HRelationQty = DBUtility.ClsPub.isDoule(Ds.Tables[0].Rows[i]["HRelationQty"]); oMain.HKTQ = DBUtility.ClsPub.isBool(Ds.Tables[0].Rows[i]["HKTQ"]); oMain.HReadyTimes = DBUtility.ClsPub.isDoule(Ds.Tables[0].Rows[i]["HReadyTimes"]); oMain.HRemark = Ds.Tables[0].Rows[i]["HRemark"].ToString().Trim(); // oMain.HMaker = Ds.Tables[0].Rows[i]["HMaker"].ToString().Trim(); oMain.HMakeDate = Ds.Tables[0].Rows[i]["HMakeDate"].ToString().Trim(); oMain.HUpDateDate = Ds.Tables[0].Rows[i]["HUpDateDate"].ToString().Trim(); oMain.HUpDater = Ds.Tables[0].Rows[i]["HUpDater"].ToString().Trim(); oMain.HCheckDate = Ds.Tables[0].Rows[i]["HCheckDate"].ToString().Trim(); oMain.HChecker = Ds.Tables[0].Rows[i]["HChecker"].ToString().Trim(); oMain.HCloseDate = Ds.Tables[0].Rows[i]["HCloseDate"].ToString().Trim(); oMain.HCloseMan = Ds.Tables[0].Rows[i]["HCloseMan"].ToString().Trim(); oMain.HDeleteDate = Ds.Tables[0].Rows[i]["HDeleteDate"].ToString().Trim(); oMain.HDeleteMan = Ds.Tables[0].Rows[i]["HDeleteMan"].ToString().Trim(); // oMain.HSourceInterID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[i]["HSourceInterID"].ToString()); oMain.HSourceEntryID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[i]["HSourceEntryID"].ToString()); oMain.HSourceBillType = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[i]["HSourceBillType"].ToString()); oMain.HSourceBillNo = Ds.Tables[0].Rows[i]["HSourceBillNo"].ToString().Trim(); oMain.HICMOInterID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[i]["HICMOInterID"]); oMain.HICMOBillNo = Ds.Tables[0].Rows[i]["HICMOBillNo"].ToString().Trim(); oMain.HSeOrderInterID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[i]["HSeOrderInterID"]); oMain.HSeOrderEntryID = DBUtility.ClsPub.isLong(Ds.Tables[0].Rows[i]["HSeOrderEntryID"]); oMain.HSeOrderBillNo = Ds.Tables[0].Rows[i]["HSeOrderBillNo"].ToString().Trim(); // DsSub = oCn.RunProcReturn("Select * from Sc_ICMOAutoSortBillSub Where HInterID=" + oMain.HInterID.ToString(), "Sc_ICMOAutoSortBillSub"); oMain.DetailColl.Clear();//清空 for (int j = 0; j < DsSub.Tables[0].Rows.Count; j++) { Model.ClsSc_ICMOAutoSortBillSub oSub = new Model.ClsSc_ICMOAutoSortBillSub(); oSub.HInterID = DBUtility.ClsPub.isLong(DsSub.Tables[0].Rows[j]["HInterID"]); oSub.HEntryID = DBUtility.ClsPub.isLong(DsSub.Tables[0].Rows[j]["HEntryID"]); oSub.HMasterDate = DBUtility.ClsPub.isDate(DsSub.Tables[0].Rows[j]["HMasterDate"]); oSub.HDate = DBUtility.ClsPub.isDate(DsSub.Tables[0].Rows[j]["HDate"]); oSub.HQty = DBUtility.ClsPub.isDoule(DsSub.Tables[0].Rows[j]["HQty"]); oSub.HStatusTag = DBUtility.ClsPub.isLong(DsSub.Tables[0].Rows[j]["HStatusTag"]); oSub.HiForeColor = DsSub.Tables[0].Rows[j]["HiForeColor"].ToString(); oSub.HiBackColor = DsSub.Tables[0].Rows[j]["HiBackColor"].ToString(); oSub.HRemark = DsSub.Tables[0].Rows[j]["HRemark"].ToString().Trim(); oSub.HCloseMan = DsSub.Tables[0].Rows[j]["HCloseMan"].ToString().Trim(); oSub.HCloseType = (bool)DsSub.Tables[0].Rows[j]["HCloseType"]; oMain.DetailColl.Add(oSub); } // MainColl.Add(oMain); } //循环 sReturn = "显示单据成功！"; return true; } catch (Exception e) { sReturn = e.Message; return false; } } //得到该部门HRelationInterID public Int64 GetRelationInterIDByDept(Int64 sDeptID) { DataSet DS ; try { DS = oCn.RunProcReturn("Select isnull(max(HRelationInterID),0) HRelationInterID from Sc_ICMOAutoSortBillMain Where HDeptID=" + sDeptID + " and Hdeleteman='' ", "Sc_ICMOAutoSortBillMain"); if (DS.Tables[0].Rows.Count == 0) return 0; else { return DBUtility.ClsPub.isLong(DS.Tables[0].Rows[0]["HRelationInterID"]); } } catch (Exception e) { return 0; } } //设置本行 资源 产能，拆分汇报数量 public Single GetSourceWorkQty(Int64 sMaterID,Int64 sProcID,Int64 sSourceID) { DataSet DS ; try { DS = oCn.RunProcReturn("exec h_p_Sc_SetSourceWorkQty "+sMaterID.ToString()+","+sProcID.ToString()+","+sSourceID.ToString(), "xt_xtgnb"); if (DS.Tables[0].Rows.Count == 0) return 0; else { return DBUtility.ClsPub.isSingle(DS.Tables[0].Rows[0]["HWorkQty"]); } } catch (Exception e) { return 0; } } //设置本行 资源 ，拆分汇报数量 public bool GetSourceRelationQty(Int64 ICMOInterID, Int64 sSourceID, ref Single RelationQty, ref Single SplitRelationQty) { DataSet DS ; try { DS = oCn.RunProcReturn("exec h_p_Sc_SetSourceRelationQty " + ICMOInterID.ToString() + "," + sSourceID.ToString(), "xt_xtgnb"); if (DS.Tables[0].Rows.Count == 0) { RelationQty = 0; SplitRelationQty = 0; return false; } else { RelationQty = DBUtility.ClsPub.isSingle(DS.Tables[0].Rows[0]["RelationQty"]); SplitRelationQty = DBUtility.ClsPub.isSingle(DS.Tables[0].Rows[0]["SplitRelationQty"]); return true; } } catch (Exception e) { RelationQty = 0; SplitRelationQty = 0; return false; } } //得到最新排程时间 public DateTime GetNewSetup(Int64 HDeptID) { try { DataSet DS = oCn.RunProcReturn("select top 1 HMakeDate from Sc_ICMOAutoSortBillMain Where HDeptID=" + HDeptID.ToString() + " and HDeleteMan='' order by HDate desc", "Sc_ICMOAutoSortBillMain"); if (DS == null || DS.Tables[0].Rows.Count == 0) { return DateTime.Today; } else { return DBUtility.ClsPub.isDate(DS.Tables[0].Rows[0]["HMakeDate"]); } } catch (Exception e) { throw (e); } } //填充投料单 public void FillData(Int64 HDeptID) { string sSQL=""; FillTag = false; oDetailColl.Clear(); sSQL = "exec h_v_Sc_GetICMOSortPPBOMQty " + HDeptID.ToString(); try { DataSet Ds = oCn.RunProcReturn(sSQL, "gy_Material"); if (Ds == null || Ds.Tables[0].Rows.Count == 0) { return; } for (int i = 0; i < Ds.Tables[0].Rows.Count; i++) { Cls_ICTLDetail oTL = new Cls_ICTLDetail(); oTL.BLS = DBUtility.ClsPub.isSingle(Ds.Tables[0].Rows[i]["BLS"]); oTL.DWYL = DBUtility.ClsPub.isSingle(Ds.Tables[0].Rows[i]["DWYL"]); oTL.FAuxQty = DBUtility.ClsPub.isSingle(Ds.Tables[0].Rows[i]["FAuxQty"]); oTL.FqtyPick = DBUtility.ClsPub.isSingle(Ds.Tables[0].Rows[i]["FqtyPick"]); oTL.GGXH = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[i]["GGXH"]); oTL.JHTLS = DBUtility.ClsPub.isSingle(Ds.Tables[0].Rows[i]["JHTLS"]); oTL.JLDW = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[i]["JLDW"]); oTL.LLS = DBUtility.ClsPub.isSingle(Ds.Tables[0].Rows[i]["LLS"]); oTL.MainGGXH = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[i]["MainGGXH"]); oTL.MainWLDM = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[i]["MainWLDM"]); oTL.MainWLMC = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[i]["MainWLMC"]); oTL.OrderAmount = DBUtility.ClsPub.isSingle(Ds.Tables[0].Rows[i]["OrderAmount"]); oTL.RQ = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[i]["RQ"]); oTL.SCRWDH = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[i]["SCRWDH"]); oTL.StockAmount = DBUtility.ClsPub.isSingle(Ds.Tables[0].Rows[i]["StockAmount"]); oTL.TLDH = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[i]["TLDH"]); oTL.WFS = DBUtility.ClsPub.isSingle(Ds.Tables[0].Rows[i]["WFS"]); oTL.WLDM = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[i]["WLDM"]); oTL.WLMC = DBUtility.ClsPub.isStrNull(Ds.Tables[0].Rows[i]["WLMC"]); oTL.YFSL = DBUtility.ClsPub.isSingle(Ds.Tables[0].Rows[i]["YFSL"]); oDetailColl.Add(oTL); } FillTag = true; } catch (Exception e) { throw (e); } } // public List GetTLColl(string sSCRWDH) { List oColl=new List(); foreach(Cls_ICTLDetail oTL in oDetailColl) { if(oTL.SCRWDH.Trim()==sSCRWDH) { oColl.Add(oTL); } } return oColl; } //设置计划开工日期 public bool Sub_setPlanBeginDate(Int64 lngBillKey,DateTime sDate, ref string sReturn) { try { oCn.RunProc("exec h_p_K3_SetICMOSortBillBeginDate "+lngBillKey.ToString()+",'"+sDate.ToShortDateString()+"'" ); return true; } catch (Exception e) { sReturn = e.Message; return false; } } //设置计划完工日期 public bool Sub_setPlanEndDate(Int64 lngBillKey, DateTime sDate, ref string sReturn) { try { oCn.RunProc("exec h_p_K3_SetICMOSortBillEndDate " + lngBillKey.ToString() + ",'" + sDate.ToShortDateString() + "'"); return true; } catch (Exception e) { sReturn = e.Message; return false; } } //根据ID得到生产计划 public DataSet Sub_GetScInfoByMainID(Int64 lngBillKey, ref string sReturn) { try { DataSet ds; ds = oCn.RunProcReturn("select 生产任务单号,部门名称,总计划数,本日计划,产品代码,产品名称,产品规格,资源名称,日期 from h_v_Sc_ICMOAutoSortBillList Where HMainID="+lngBillKey.ToString(), "xt_xtgnb"); if (ds == null || ds.Tables[0].Rows.Count == 0) { return null; } return ds; } catch (Exception e) { sReturn = e.Message; return null; } } //根据信息 设置---是否缺料，确认时间 public bool Sub_SetStatusByInfo(Int64 lSourceID,Int64 lSourceInterID,DateTime dDate, Int64 HStatus, ref string sReturn) { try { oCn.BeginTran(); // oCn.RunProc("exec h_p_Sc_SetICMOStatus_ReadyMater " + lSourceID.ToString() + "," + lSourceInterID.ToString() + ",'" + dDate.ToShortDateString() + "'," + HStatus.ToString() +",'" +DBUtility.ClsPub.CurUserName + "'"); // oCn.Commit(); // return true; } catch (Exception e) { sReturn = e.Message; oCn.RollBack(); return false; } } //根据信息 设置 计划执行 public bool Sub_SetStartByInfo(Int64 lSourceID, Int64 lSourceInterID, DateTime dDate, Int64 HStatus, ref string sReturn) { try { oCn.BeginTran(); // oCn.RunProc("exec h_p_Sc_SetICMOStatus_ReadyStart " + lSourceID.ToString() + "," + lSourceInterID.ToString() + ",'" + dDate.ToShortDateString() + "'," + HStatus.ToString() + ",'" + DBUtility.ClsPub.CurUserName + "'"); // oCn.Commit(); // return true; } catch (Exception e) { sReturn = e.Message; oCn.RollBack(); return false; } } /// ///查询生产计划是否结案 /// ///生产状态表ID ///False为未结案，True为已结案 public bool Sub_GetICMOSortClose(Int64 lngBillKey, ref string sReturn) { try { DataSet Ds; Ds = oCn.RunProcReturn("exec h_p_Sc_GetICMOSortClose " + lngBillKey.ToString(), "xt_xtgnb"); if (Ds.Tables[0].Rows.Count == 0) { return false; } else { return true; } } catch (Exception e) { sReturn = e.Message; return false; } } } }