using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Diagnostics; using System.Drawing; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using Newtonsoft.Json; using Newtonsoft.Json.Linq; namespace WFormSynchronizeData_SMR { public partial class Form1 : Form { public DBHelper oCN = new DBHelper(); public Form1() { InitializeComponent(); } //读取数据 private void btnReadData_Click(object sender, EventArgs e) { Stopwatch timer = new Stopwatch(); timer.Start(); CustomWriteLog("1.计时器开始:"+timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); DataTable list = oCN.RunProcReturn("select top 150 * from Sb_EquipMentCollection_SN WITH(NOLOCK) where HFlag=0 order by HCreateTime", "Sb_EquipMentCollection_SN").Tables[0]; //循环集合 for (int i = 0; i < list.Rows.Count; i++) { bool flag = false; string HResult = list.Rows[i]["HResult"].ToString(); string HBadCodeSN = list.Rows[i]["HBarCode"].ToString(); string HCreateTime = list.Rows[i]["HCreateTime"].ToString(); //开始事务 oCN.BeginTran(); CustomWriteLog("2.循环开始:" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); if (HResult == "OK") { flag=HResultOK(list.Rows[i]); } else if (HResult == "NG") { flag = HResultNG(list.Rows[i]); } CustomWriteLog("2-1.循环结束:" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); if (flag) { oCN.RunProc("update Sb_EquipMentCollection_SN set HFlag=1 where HBarCode='" + HBadCodeSN + "' and HCreateTime='"+ HCreateTime + "'"); //结束事务 oCN.Commit(); } } CustomWriteLog("3.结束计时:" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); timer.Stop();//结束计时 } public bool HResultOK(DataRow dic) { try { Stopwatch timer = new Stopwatch(); timer.Start(); CustomWriteLog("2.1.开始ok:" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); bool flag = false; string HBadCodeSN = dic["HBarCode"].ToString(); HBadCodeSN = TM_ZH(HBadCodeSN); int num = SNHBardCodeList(HBadCodeSN); CustomWriteLog("2.2.条码生成:" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); if (num == 0) { flag = getOutBillAdd(dic, HBadCodeSN, "OK"); } else if (num == 1) { //判断这个条码之前的状态是否为NG DataSet ds = oCN.RunProcReturn(@"select * from Sb_EquipMentCollection_SN WITH(NOLOCK) where HBarCode='" + HBadCodeSN + "' and HCreateTime<'" + dic["HCreateTime"].ToString() + "' and HResult='NG'", "Sb_EquipMentCollection_SN"); //查询出站单是否包含条码 // DataSet ds1 = oCN.RunProcReturn(@"select a.HSourceID,a.HProcID,* from Sc_StationOutBillMain a WITH(NOLOCK) //inner join Sc_StationOutBillSub_SN sn WITH(NOLOCK) on a.HInterID=sn.HInterID //left join Gy_Source s WITH(NOLOCK) on a.HSourceID=s.HItemID //left join Gy_Process p WITH(NOLOCK) on a.HProcID=p.HItemID //where p.HNumber='" + dic["HProcNumber"].ToString() + "' and sn.HBarCode='" + HBadCodeSN + "'", "Sc_StationOutBillSub_SN"); DataSet ds1 = oCN.RunProcReturn(@"select a.HProcID from Sc_StationOutBillMain a WITH(NOLOCK) inner join Sc_StationOutBillSub_SN sn WITH(NOLOCK) on a.HInterID=sn.HInterID left join Gy_Process p WITH(NOLOCK) on a.HProcID=p.HItemID where p.HNumber='" + dic["HProcNumber"].ToString() + "' and sn.HBarCode='" + HBadCodeSN + "'", "Sc_StationOutBillSub_SN"); if (ds.Tables[0].Rows.Count > 0 && ds1.Tables[0].Rows.Count == 0) { flag = getOKOutBillAdd(dic, HBadCodeSN, "OK"); } else { if (ds1.Tables[0].Rows.Count > 0) { flag = true; //oCN.RollBack(); } else { flag = getOutBillAdd(dic, HBadCodeSN, "OK"); } } } else if (num == 2) { flag = false; } CustomWriteLog("2.5.结束计时:" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); timer.Stop();//结束计时 return flag; } catch (Exception e) { oCN.RollBack(); string HBadCodeSN = dic["HBarCode"].ToString(); CustomWriteLog("条码:" + HBadCodeSN + ",错误原因:" + e.Message, DateTime.Now.ToString("yyyy-MM-dd")); return false; } } public bool HResultNG(DataRow dic) { try { bool flag = false; int num = 0; string HBadCodeSN = dic["HBarCode"].ToString(); HBadCodeSN = TM_ZH(HBadCodeSN); num = SNHBardCodeList(HBadCodeSN); if (num == 0 || num == 1) { flag = getOutBillAdd(dic, HBadCodeSN, "NG"); if (flag) { //新增不良汇报 flag = getBadBillAdd(dic); //反写条码状态 oCN.RunProc("update Gy_BarCodeBill set HStatus='不良',HRemark='" + dic["HType"].ToString() + "' where HBarCode='" + HBadCodeSN + "'"); } } else if (num == 2) { flag = false; } return flag; } catch (Exception e) { //回滚事务 oCN.RollBack(); string HBadCodeSN = dic["HBarCode"].ToString(); CustomWriteLog("条码:" + HBadCodeSN + ",错误原因:"+e.Message, DateTime.Now.ToString("yyyy-MM-dd")); return false; } } //新增出站汇报单 public bool getOutBillAdd(DataRow dic, string HBadCodeSN, string HResult) { try { Stopwatch timer = new Stopwatch(); timer.Start(); CustomWriteLog("2.1.1.出站单新增开始:" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); //获取绑定流转卡 DataSet ds = oCN.RunProcReturn(@"select * from Gy_BarCodeBill WITH(NOLOCK) where HBarCode='" + HBadCodeSN + "'", "Gy_BarCodeBill"); string HProcExchBillNo = ds.Tables[0].Rows[0]["HSourceBillNo"].ToString(); string HProcNumber = dic["HProcNumber"].ToString(); CustomWriteLog("2.1.2.绑定流转卡:"+ HProcNumber+"----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); ds = oCN.RunProcReturn("select * from Gy_Source WITH(NOLOCK) where HNumber='" + dic["HSourceCode"].ToString() + "' ", "Gy_Source"); long HSourceID = 0; if (ds.Tables[0].Rows.Count != 0) { HSourceID = long.Parse(ds.Tables[0].Rows[0]["HItemID"].ToString());//生产资源ID } CustomWriteLog("2.1.3.获取生产资源:" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); //查询职员 ds = oCN.RunProcReturn("select * from Gy_Employee WITH(NOLOCK) where HNumber='" + dic["HEmpCode"].ToString() + "' ", "Gy_Employee"); long HEmpIDs = 0; string HEmpName = ""; if (ds.Tables[0].Rows.Count != 0) { HEmpIDs = long.Parse(ds.Tables[0].Rows[0]["HItemID"].ToString());//职员ID HEmpName = ds.Tables[0].Rows[0]["HName"].ToString(); } CustomWriteLog("2.1.4.查询职员信息:" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); long HDeptID = 0; long HPRDOrgIDs = 0; string sReturn = ""; if (GetSingleSystemParameter("WMS_CampanyName", ref sReturn) == "杭州斯莫尔") { HDeptID = 389505; HPRDOrgIDs = 100199; } else { //ds = oCN.RunProcReturn("select * from Gy_Department where HNumber='" + dic["HDeptNumber"].ToString() + "' ", "Gy_Department"); //if (ds.Tables[0].Rows.Count != 0) //{ // HDeptID = long.Parse(ds.Tables[0].Rows[0]["HItemID"].ToString());//生产部门 //} HDeptID = 0; } CustomWriteLog("2.1.5.获取客户信息:" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); ds = oCN.RunProcReturn(@"select a.HWorkShopID,b.HProcNo,b.HProcID,a.HMaterID,a.HQty,a.HInterID,HEntryID,a.HICMOInterID,a.HICMOEntryID,a.HICMOBillNo,b.HCenterID from Sc_ProcessExchangeBillMain a WITH(NOLOCK) inner join Sc_ProcessExchangeBillSub b WITH(NOLOCK) on a.HInterID=b.HInterID left join Gy_Process p WITH(NOLOCK) on b.HProcID=p.HItemID where a.HBillNo='" + HProcExchBillNo + "' and p.HNumber='" + HProcNumber + "'", "Sc_ProcessExchangeBillMain"); CustomWriteLog("2.1.6.查询流转卡信息:" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); string sExeReturnInfo = ""; long HMainInterID = 0; string BillType = "3791"; string HBillSubType = "3791"; string HWorkShopID= ds.Tables[0].Rows[0]["HWorkShopID"].ToString(); int HBillStatus = 1; string HMaker = HEmpName;//制单人 string HMouldNum = "";//模穴号 int HYear = DateTime.Now.Year; double HPeriod = DateTime.Now.Month; string HRemark = "";//备注 string HSourceName = "";//生产资源名称 double HPieceQty = double.Parse(ds.Tables[0].Rows[0]["HQty"].ToString());//进站PCS数 double HWasterQty = 0;//报废数量 double HPlanPieceQty = 0;//进站PNL数 double HBadPNL = 0;//报废PNL数 long HICMOInterID = long.Parse(ds.Tables[0].Rows[0]["HICMOInterID"].ToString());//任务单ID long HICMOEntryID = long.Parse(ds.Tables[0].Rows[0]["HICMOEntryID"].ToString());//任务单ID string HICMOBillNo = ds.Tables[0].Rows[0]["HICMOBillNo"].ToString();//任务单 int HProcPlanInterID = 0; int HProcPlanEntryID = 0; string HProcPlanBillNo = ""; long HProcExchInterID = long.Parse(ds.Tables[0].Rows[0]["HInterID"].ToString()); long HProcExchEntryID = long.Parse(ds.Tables[0].Rows[0]["HEntryID"].ToString()); long HMaterID = long.Parse(ds.Tables[0].Rows[0]["HMaterID"].ToString());//产品ID long HProcID = long.Parse(ds.Tables[0].Rows[0]["HProcID"].ToString());//当前工序ID double HICMOQty = double.Parse(ds.Tables[0].Rows[0]["HQty"].ToString());//任务单数量 double HPlanQty = double.Parse(ds.Tables[0].Rows[0]["HQty"].ToString());//移交PNL数 DateTime HStationOutTime = DateTime.Now;//汇报时间 long HPayProcID = 0;//核算工序ID long HGroupID = 22;//班组ID 白班 long HEmpID = HEmpIDs;//操作员ID long HEmpID2 = HEmpIDs;////操作员2ID string HBarCode = HProcExchBillNo;//条形码 string HAddr = ""; string HBarCodeMaker = ""; long HSourceID2 = 0;//生产资源2ID long HSourceID3 = 0;//生产资源3ID long HSourceID4 = 0;//生产资源4ID long HSourceID5 = 0;//生产资源5ID long HSupID = 0; double HQty = 0;//合格数量 double HPrice = 0; double HMoney = 0; double HBadCount = 0;//不良数量 long HCenterID = long.Parse(ds.Tables[0].Rows[0]["HCenterID"].ToString());//工作中心ID string HProcNo = ds.Tables[0].Rows[0]["HProcNo"].ToString();//流水号 string HOrderProcNO = "";//订单跟踪号 string HSourceNameList = "";//设备清单 string HMainSourceBillType = "3790"; bool HLastSubProc = false;//转下工序 long HEmpID3 = 0;//操作员3ID long HEmpID4 = 0;//操作员4ID long HEmpID5 = 0;//操作员5ID double HDSQty = 0;//折弯刀数 double HChongQty = 0;//NCT冲次数 double HPriceRate = 0;//系数 double HWorkTimes = 0;//工时 long HQCCheckID = 0;//检验员ID long HPRDOrgID = HPRDOrgIDs;//组织ID double HmaterOutqty = 0;//白坯发布 double HProcPriceRate = 0;//工价系数 int HTemporaryAreaID = 0;//暂放区 long HInterID = 0; string HBillNo = ""; int HShiftsID = 0; CustomWriteLog("2.1.7给字段赋值 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); DataSet set= oCN.RunProcReturn("exec h_p_Gy_GetWorkShiftInfo " + HWorkShopID + "," + HCenterID, "h_p_Gy_GetWorkShiftInfo"); if (set.Tables[0].Rows.Count > 0) { HShiftsID = int.Parse(set.Tables[0].Rows[0]["HInterID"].ToString()); } CustomWriteLog("2.1.8获取班次信息 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); //ds = oCN.RunProcReturn("exec h_p_Sc_SNBarcodeProcCtrl_S '" + HBadCodeSN + "'," + HProcID, "h_v_Gy_QualifiedRecordsList"); //if (ds.Tables[0].Rows.Count == 0) //{ // oCN.RollBack(); // CustomWriteLog("条码:" + HBadCodeSN + "工序:" + HProcID + ",工序控制查无数据!", DateTime.Now.ToString("yyyy-MM-dd")); // return false; //} //else if (ds.Tables[0].Rows[0]["HBack"].ToString() == "2") //{ // oCN.RollBack(); // CustomWriteLog("条码:" + HBadCodeSN + "工序:" + HProcID + "," + ds.Tables[0].Rows[0]["HBackRemark"].ToString() + "!", DateTime.Now.ToString("yyyy-MM-dd")); // return false; //} //判断当前流转卡的出站单 是否有数据 ds = oCN.RunProcReturn(@"select * from Sc_StationOutBillMain a WITH(NOLOCK) left join Gy_Process p WITH(NOLOCK) on a.HProcID=p.HItemID where HProcExchBillNo='" + HProcExchBillNo + "' and p.HNumber='" + HProcNumber + "' and CONVERT(varchar(10),HDate,20) ='" + DateTime.Now.ToString("yyyy-MM-dd") + "'", "Sc_StationOutBillMain"); CustomWriteLog("2.1.9获取是否有出站单 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); if (ds.Tables[0].Rows.Count != 0) { HInterID = long.Parse(ds.Tables[0].Rows[0]["HInterID"].ToString()); HBillNo = ds.Tables[0].Rows[0]["HBillNo"].ToString();//递入type得到的单据号 } if (ds.Tables[0].Rows.Count == 0) { HInterID = CreateBillID("3791", ref sExeReturnInfo);//递入type得到的单据ID HBillNo = CreateBillCode_Prod("3791", ref sExeReturnInfo, true);//递入type得到的单据号 long HMainSourceInterID = HInterID;//递入type得到的单据ID string HMainSourceBillNo = HBillNo;//递入type得到的单据号 oCN.RunProc("Insert Into Sc_StationOutBillMain " + "(HBillType,HBillSubType,HInterID,HBillNo,HBillStatus,HDate,HMaker,HMakeDate,HMouldNum" + ",HYear,HPeriod,HRemark,HSourceName,HPieceQty,HWasterQty,HPlanPieceQty,HBadPNL" + ",HICMOInterID,HICMOBillNo,HProcPlanInterID,HProcPlanEntryID,HProcPlanBillNo,HProcExchInterID,HProcExchEntryID" + ",HProcExchBillNo,HMaterID,HProcID,HICMOQty,HPlanQty,HStationOutTime,HSourceID,HPayProcID" + ",HGroupID,HDeptID,HEmpID,HBarCode,HAddr,HBarCodeMaker,HBarCodeMakeDate,HSourceID2,HSourceID3,HSourceID4,HSourceID5" + ",HSupID,HQty,HPrice,HMoney,HBadCount,HCenterID,HProcNo,HOrderProcNO,HSourceNameList" + ",HMainSourceInterID,HMainSourceBillNo,HMainSourceBillType,HLastSubProc" + ",HEmpID2,HEmpID3,HEmpID4,HEmpID5,HDSQty,HChongQty,HPriceRate,HWorkTimes,HQCCheckID,HMainInterID,HPRDOrgID" + ",HmaterOutqty,HProcPriceRate,HTemporaryAreaID,HShiftsID" + ") " + " values('" + BillType + "','" + HBillSubType + "'," + HInterID + ",'" + HBillNo + "'," + HBillStatus + ",getdate(),'" + HMaker + "',getdate(),'" + HMouldNum + "'" + "," + HYear + "," + HPeriod + ",'" + HRemark + "','" + HSourceName + "'," + HPieceQty + "," + HWasterQty + "," + HPlanPieceQty + "," + HBadPNL + "," + HICMOInterID + ",'" + HICMOBillNo + "'," + HProcPlanInterID + "," + HProcPlanEntryID + ",'" + HProcPlanBillNo + "'," + HProcExchInterID + "," + HProcExchEntryID + ",'" + HProcExchBillNo + "'," + HMaterID + "," + HProcID + "," + HICMOQty + "," + HPlanQty + ",getdate()," + HSourceID + "," + HPayProcID + "," + HGroupID + "," + HDeptID + "," + HEmpID + ",'" + HBarCode + "','" + HAddr + "','" + HBarCodeMaker + "',getdate()" + "," + HSourceID2 + "," + HSourceID3 + "," + HSourceID4 + "," + HSourceID5 + "," + HSupID + "," + HQty + "," + HPrice + "," + HMoney + "," + HBadCount + "," + HCenterID + "," + HProcNo + ",'" + HOrderProcNO + "'" + ",'" + HSourceNameList + "'" + "," + HMainSourceInterID + ",'" + HMainSourceBillNo + "','" + HMainSourceBillType + "'," + Convert.ToString(HLastSubProc ? 1 : 0) + "," + HEmpID2 + "," + HEmpID3 + "," + HEmpID4 + "," + HEmpID5 + "," + HDSQty + "," + HChongQty + "," + HPriceRate + "," + HWorkTimes + "," + HQCCheckID + "," + HMainInterID + "," + HPRDOrgID + "," + HmaterOutqty + "," + HProcPriceRate + "," + HTemporaryAreaID + ","+ HShiftsID + ") "); } CustomWriteLog("2.1.10 出站单新增 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); if (HResult == "OK") { CustomWriteLog("2.1.11 条码反写 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); ds = oCN.RunProcReturn("select * from Sc_StationOutBillSub_SN WITH(NOLOCK) where HInterID='" + HInterID + "' order by HEntryID desc", "Sc_StationOutBillSub_SN"); string HMakeTime = dic["HCreateTime"].ToString(); oCN.RunProc($@"insert into Sc_StationOutBillSub_SN(HInterID,HBillNo_bak,HEntryID,HBarCode,HBarCodeQty,HMakeTime,HRemark,HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney) values({ HInterID}, '{ HBillNo}', {(ds.Tables[0].Rows.Count == 0 ? 1 : int.Parse(ds.Tables[0].Rows[0]["HEntryID"].ToString()) + 1)}, '{HBadCodeSN}', 1, '"+ HMakeTime + "', '', " + HProcExchInterID + "," + HProcExchEntryID + ", '" + HProcExchBillNo + "', '', 0, 0)"); CustomWriteLog("条码:" + HBadCodeSN + "工序:" + HProcNumber + ",出站时间:" + HMakeTime, "TM" + DateTime.Now.ToString("yyyy-MM-dd")); //反写工序出站单的合格数量 oCN.RunProc("update Sc_StationOutBillMain set HQty+=1 where HInterID='" + HInterID + "'"); CustomWriteLog("2.1.12 条码反写 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); } else if (HResult == "NG") { CustomWriteLog("2.1.13 不合格反写 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); ds = oCN.RunProcReturn("select * from Sc_QualityReportBillSub WITH(NOLOCK) where HBarCode='" + HBadCodeSN + "' order by HMakeDate desc", "Sc_QualityReportBillSub"); if (ds.Tables[0].Rows.Count > 0) { string HBadDate = ds.Tables[0].Rows[0]["HMakeDate"].ToString(); ds = oCN.RunProcReturn("select * from Sc_SourceLineRepairBillMain WITH(NOLOCK) where HBarCode='" + HBadCodeSN + "' order by HMakeDate desc", "Sc_SourceLineRepairBillMain"); if (ds.Tables[0].Rows.Count > 0) { string HRepairDate = ds.Tables[0].Rows[0]["HMakeDate"].ToString(); if (DateTime.Parse(HBadDate) < DateTime.Parse(HRepairDate)) { //反写工序出站单的不良数量 oCN.RunProc("update Sc_StationOutBillMain set HBadCount+=1,HWasterQty_Work+=1 where HInterID='" + HInterID + "'"); } } } else { //反写工序出站单的不良数量 oCN.RunProc("update Sc_StationOutBillMain set HBadCount+=1,HWasterQty_Work+=1 where HInterID='" + HInterID + "'"); } CustomWriteLog("2.1.14 不合格反写 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); } else { oCN.RollBack(); CustomWriteLog("结果不为OK或NG!", DateTime.Now.ToString("yyyy-MM-dd")); return false; } // //查询出站数量是否超过流转卡数量 // ds = oCN.RunProcReturn($@"select (b.HQty -sum(isnull(ou.HQty,0))-sum(isnull(ou.HBadCount,0))) HQty from Sc_ProcessExchangeBillSub b WITH(NOLOCK) //left join Sc_StationOutBillMain ou WITH(NOLOCK) on b.HInterID=ou.HProcExchInterID and b.HEntryID=ou.HProcExchEntryID //where b.HInterID={HProcExchInterID} and b.HEntryID={HProcExchEntryID} //group by b.HInterID,b.HEntryID,b.HQty", "Sc_ProcessExchangeBill_Out"); // if (double.Parse(ds.Tables[0].Rows[0][0].ToString()) < 0) // { // oCN.RollBack(); // CustomWriteLog("流转卡:"+ HProcExchBillNo + ",出站数量超过流转卡数量!", DateTime.Now.ToString("yyyy-MM-dd")); // return false; // } CustomWriteLog("2.1.15 反写结束 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); //增加工序判断 if (HProcNumber == "013") { return true; } CustomWriteLog("2.1.16 工艺参数绑定 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); bool flag = false; flag = AddXt_XtRoutBill(HInterID, HBillNo, HBadCodeSN); if (!flag) { oCN.RollBack(); CustomWriteLog("系统参数保存失败,条码:" + HBadCodeSN, DateTime.Now.ToString("yyyy-MM-dd")); return false; } CustomWriteLog("2.1.17 工艺参数绑定结束 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); //增加产线组装追溯单 DataSet dataSet = oCN.RunProcReturn("exec h_p_Gy_BarCodeBillBomList '" + HProcExchBillNo + "'," + HProcID, "h_p_Gy_BarCodeBillBomList"); CustomWriteLog("2.1.18 产线组装追溯单查询 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); if (dataSet.Tables[0].Rows.Count > 0) { CustomWriteLog("2.1.19 产线组装追溯单数量判断 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); //判配件数量是否等于0 for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++) { double SYHQty = double.Parse(dataSet.Tables[0].Rows[i]["配件数量"].ToString()); string HMaterNamePJ = dataSet.Tables[0].Rows[i]["配件代码"].ToString(); string HMaterBarCode = dataSet.Tables[0].Rows[i]["HBarCode"].ToString(); if (SYHQty == 0) { oCN.RollBack(); CustomWriteLog("配件条码:"+ HMaterBarCode + ",配件代码:" + HMaterNamePJ + ",数量为0!", DateTime.Now.ToString("yyyy-MM-dd")); return false; } } CustomWriteLog("2.1.20 产线组装追溯单数量判断结束 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); //查询职员 ds = oCN.RunProcReturn("select * from Gy_Employee WITH(NOLOCK) where HNumber='" + dic["HEmpCode"].ToString() + "' ", "Gy_Employee"); string HMakers = ""; if (ds.Tables[0].Rows.Count != 0) { HEmpIDs = long.Parse(ds.Tables[0].Rows[0]["HItemID"].ToString());//职员ID HMakers = ds.Tables[0].Rows[0]["HName"].ToString(); } CustomWriteLog("2.1.21 职员查询 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); Int64 HInterID1 = CreateBillID("3727", ref sExeReturnInfo); string HBillNo1 = CreateBillCode_Prod("3727", ref sExeReturnInfo, true); //保存生产组装单主表 string sql = $@"Insert Into Sc_AssemblyBillMain(HYear,HPeriod,HBillType,HBillSubType,HInterID,HDate ,HBillNo,HBillStatus,HMaker,HMakeDate,HMainSourceInterID,HMainSourceEntryID,HMainSourceBillNo ,HICMOInterID,HICMOBillNo,HBarCode_P,HMaterID,HUnitID,HAssemblyStatus,HProdOrgID) values('{DateTime.Now.Year}','{DateTime.Now.Month}','3727','3727',{HInterID1},getdate() ,'{HBillNo1}','1','{HMakers}',getdate(),{HProcExchInterID},{HProcExchEntryID},'{HProcExchBillNo}' ,{HICMOInterID},'{HICMOBillNo}','{HBadCodeSN}',{HMaterID},0,'汇报',{HPRDOrgIDs})"; oCN.RunProc(sql); CustomWriteLog("2.1.22 保存主表 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++) { string HMaterIDs = dataSet.Tables[0].Rows[i]["HMaterID"].ToString(); string HBarCodes = dataSet.Tables[0].Rows[i]["HBarCode"].ToString(); //子表存储 string sq2 = $@"Insert Into Sc_AssemblyBillSub(HInterID,HBillNo_bak,HEntryID,HSourceInterID,HSourceEntryID,HSourceBillNo ,HSourceBillType,HMaterID,HSourceID,HEquipID,HUnitID,HQty ,HGroupID,HWorkerID,HScanDate,HBarCode,HBarCode_P,HSNNumber) values({HInterID1},'{HBillNo1}',{(i + 1)},0,0,'' ,'',{ HMaterIDs},0,0,0,1 ,0,0,getdate(),'{HBarCodes}','{HBadCodeSN}','') "; oCN.RunProc(sq2); } CustomWriteLog("2.1.23 保存子表 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); } CustomWriteLog("2.1.24计时器结束 :" + HProcNumber + "----" + timer.Elapsed, DateTime.Now.ToString("yyyy-MM-dd")); timer.Stop();//结束计时 return true; } catch (Exception e) { oCN.RollBack(); CustomWriteLog(e.Message, DateTime.Now.ToString("yyyy-MM-dd")); return false; } } //新增返修OK出站汇报单 public bool getOKOutBillAdd(DataRow dic, string HBadCodeSN, string HResult) { try { //获取绑定流转卡 DataSet ds = oCN.RunProcReturn(@"select HSourceBillNo from Gy_BarCodeBill WITH(NOLOCK) where HBarCode='" + HBadCodeSN + "'", "Gy_BarCodeBill"); string HProcExchBillNo = ds.Tables[0].Rows[0]["HSourceBillNo"].ToString(); string HProcNumber = dic["HProcNumber"].ToString(); ds = oCN.RunProcReturn("select HItemID from Gy_Source WITH(NOLOCK) where HNumber='" + dic["HSourceCode"].ToString() + "' ", "Gy_Source"); long HSourceID = 0; if (ds.Tables[0].Rows.Count != 0) { HSourceID = long.Parse(ds.Tables[0].Rows[0]["HItemID"].ToString());//生产资源ID } //查询职员 ds = oCN.RunProcReturn("select HItemID from Gy_Employee WITH(NOLOCK) where HNumber='" + dic["HEmpCode"].ToString() + "' ", "Gy_Employee"); long HEmpIDs = 0; if (ds.Tables[0].Rows.Count != 0) { HEmpIDs = long.Parse(ds.Tables[0].Rows[0]["HItemID"].ToString());//职员ID } long HDeptID = 0; long HPRDOrgIDs = 0; string sReturn = ""; if (GetSingleSystemParameter("WMS_CampanyName", ref sReturn) == "杭州斯莫尔") { HDeptID = 389505; HPRDOrgIDs = 100199; } else { //ds = oCN.RunProcReturn("select * from Gy_Department where HNumber='" + dic["HDeptNumber"].ToString() + "' ", "Gy_Department"); //if (ds.Tables[0].Rows.Count != 0) //{ // HDeptID = long.Parse(ds.Tables[0].Rows[0]["HItemID"].ToString());//生产部门 //} HDeptID = 0; } ds = oCN.RunProcReturn(@"select b.HProcNo,b.HProcID,a.HMaterID,a.HQty,a.HInterID,HEntryID,a.HICMOInterID,a.HICMOEntryID,a.HICMOBillNo from Sc_ProcessExchangeBillMain a WITH(NOLOCK) inner join Sc_ProcessExchangeBillSub b WITH(NOLOCK) on a.HInterID=b.HInterID left join Gy_Process p WITH(NOLOCK) on b.HProcID=p.HItemID where a.HBillNo='" + HProcExchBillNo + "' and p.HNumber='" + HProcNumber + "'", "Sc_ProcessExchangeBillMain"); long HICMOInterID = long.Parse(ds.Tables[0].Rows[0]["HICMOInterID"].ToString());//任务单ID long HICMOEntryID = long.Parse(ds.Tables[0].Rows[0]["HICMOEntryID"].ToString());//任务单ID string HICMOBillNo = ds.Tables[0].Rows[0]["HICMOBillNo"].ToString();//任务单 long HProcExchInterID = long.Parse(ds.Tables[0].Rows[0]["HInterID"].ToString()); long HProcExchEntryID = long.Parse(ds.Tables[0].Rows[0]["HEntryID"].ToString()); long HMaterID = long.Parse(ds.Tables[0].Rows[0]["HMaterID"].ToString());//产品ID long HProcID = long.Parse(ds.Tables[0].Rows[0]["HProcID"].ToString());//当前工序ID long HInterID = 0; string HBillNo = ""; //ds = oCN.RunProcReturn("exec h_p_Sc_SNBarcodeProcCtrl_S '" + HBadCodeSN + "'," + HProcID, "h_v_Gy_QualifiedRecordsList"); //if (ds.Tables[0].Rows.Count == 0) //{ // oCN.RollBack(); // CustomWriteLog("条码:" + HBadCodeSN + "工序:" + HProcID + ",工序控制查无数据!", DateTime.Now.ToString("yyyy-MM-dd")); // return false; //} //else if (ds.Tables[0].Rows[0]["HBack"].ToString() == "2") //{ // oCN.RollBack(); // CustomWriteLog("条码:" + HBadCodeSN + "工序:" + HProcID + "," + ds.Tables[0].Rows[0]["HBackRemark"].ToString() + "!", DateTime.Now.ToString("yyyy-MM-dd")); // return false; //} ds = oCN.RunProcReturn("select HDate from Sc_AssemblyBillMain WITH(NOLOCK) where HMainSourceInterID=" + HProcExchInterID + " and HMainSourceEntryID=" + HProcExchEntryID, "Sc_AssemblyBillMain"); DateTime HDate=DateTime.Now; //当产品没有配件的时候 查询 不良记录 if (ds.Tables[0].Rows.Count == 0) { ds = oCN.RunProcReturn("select HMakeDate from Sc_QualityReportBillSub WITH(NOLOCK) where HProcExchInterID=" + HProcExchInterID + " and HProcExchEntryID=" + HProcExchEntryID+ " and HBarCode='" + HBadCodeSN + "'", "Sc_AssemblyBillMain"); HDate = DateTime.Parse(ds.Tables[0].Rows[0]["HMakeDate"].ToString()); } else { HDate = DateTime.Parse(ds.Tables[0].Rows[0]["HDate"].ToString()); } //查询是否有返修记录 ds = oCN.RunProcReturn(@"select a.HBarCode,a.HProcess,b.HSourceInterID,b.HSourceEntryID,b.HRepairResult from Sc_SourceLineRepairBillMain a WITH(NOLOCK) inner join Sc_SourceLineRepairBillSub b WITH(NOLOCK) on a.HInterID = b.HInterID where a.HBarCode = '" + HBadCodeSN + "' and a.HProcess ="+ HProcID + " and b.HSourceInterID ="+ HProcExchInterID + " " + "and b.HSourceEntryID="+ HProcExchEntryID + " and b.HRepairResult = 'OK'", "Sc_SourceLineRepairBillMain"); if (ds.Tables[0].Rows.Count == 0) { oCN.RollBack(); CustomWriteLog("条码:" + HBadCodeSN + ",无返修记录", DateTime.Now.ToString("yyyy-MM-dd")); return false; } //判断当前流转卡的出站单 是否有数据 ds = oCN.RunProcReturn(@"select HInterID,HBillNo from Sc_StationOutBillMain a WITH(NOLOCK) left join Gy_Process p WITH(NOLOCK) on a.HProcID=p.HItemID where HProcExchBillNo='" + HProcExchBillNo + "' and p.HNumber='" + HProcNumber + "' and CONVERT(varchar(10),HDate,20) ='" + HDate.ToString("yyyy-MM-dd") + "'", "Sc_StationOutBillMain"); if (ds.Tables[0].Rows.Count != 0) { HInterID = long.Parse(ds.Tables[0].Rows[0]["HInterID"].ToString()); HBillNo = ds.Tables[0].Rows[0]["HBillNo"].ToString();//递入type得到的单据号 } if (HResult == "OK") { ds = oCN.RunProcReturn("select HInterID,HEntryID from Sc_StationOutBillSub_SN WITH(NOLOCK) where HInterID='" + HInterID + "' order by HEntryID desc", "Sc_StationOutBillSub_SN"); string HMakeTime = dic["HCreateTime"].ToString(); oCN.RunProc($@"insert into Sc_StationOutBillSub_SN(HInterID,HBillNo_bak,HEntryID,HBarCode,HBarCodeQty,HMakeTime,HRemark,HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney) values({ HInterID}, '{ HBillNo}', {(ds.Tables[0].Rows.Count == 0 ? 1 : int.Parse(ds.Tables[0].Rows[0]["HEntryID"].ToString()) + 1)}, '{HBadCodeSN}', 1, '" + HMakeTime + "', '', " + HProcExchInterID + "," + HProcExchEntryID + ", '" + HProcExchBillNo + "', '', 0, 0)"); CustomWriteLog("条码:" + HBadCodeSN + "工序:"+ HProcNumber + ",出站时间:" + HMakeTime, "TM" + DateTime.Now.ToString("yyyy-MM-dd")); //反写工序出站单的合格数量 //oCN.RunProc("update Sc_StationOutBillMain set HQty+=1,HBadCount-=1 where HInterID='" + HInterID + "'"); oCN.RunProc("update Sc_StationOutBillMain set HQty+=1 where HInterID='" + HInterID + "'"); //反写条码状态 oCN.RunProc("update Gy_BarCodeBill set HStatus='' where HBarCode='" + HBadCodeSN + "'"); } else { oCN.RollBack(); CustomWriteLog("结果不为OK!", DateTime.Now.ToString("yyyy-MM-dd")); return false; } return true; } catch (Exception e) { oCN.RollBack(); CustomWriteLog(e.Message, DateTime.Now.ToString("yyyy-MM-dd")); return false; } } //保存系统参数到出站单 public bool AddXt_XtRoutBill(long HInterID, string HBillNo,string HBadCodeSN) { string sql = ""; try { DataSet ds = oCN.RunProcReturn(@"select a.HProcExchInterID,a.HProcExchEntryID,a.HProcExchBillNo,a.HProcID,p.HNumber from Sc_StationOutBillMain a WITH(NOLOCK) left join Gy_Process p WITH(NOLOCK) on a.HProcID=p.HItemID where HInterID=" + HInterID + " and HBillNo='" + HBillNo + "'", "Sc_StationOutBillMain"); string HProcExchInterID = ds.Tables[0].Rows[0]["HProcExchInterID"].ToString(); string HProcExchEntryID = ds.Tables[0].Rows[0]["HProcExchEntryID"].ToString(); string HProcExchBillNo = ds.Tables[0].Rows[0]["HProcExchBillNo"].ToString(); string HProcID = ds.Tables[0].Rows[0]["HProcID"].ToString(); string HProcNum = ds.Tables[0].Rows[0]["HNumber"].ToString(); //注:因为 组装 拍照 时间间隔比较短 所以目前只明确组装工序才可以保存工艺参数 之后如果有其他工序的话 需要增加工序字段 if (HProcID == "88"|| HProcNum=="005") { ds = oCN.RunProcReturn(@"select s.HItemID HMouldID,t.HItemID HTechParamID,HCount,HCreateTime,a.HResult from Sb_EquipMentCollectionTechParam_SN a WITH(NOLOCK) inner join Gy_Source s WITH(NOLOCK) on a.HSourceCode=s.HNumber inner join Gy_TechnologyParameter t WITH(NOLOCK) on a.HType=t.HName where a.HFlag=0 and a.HBarCode='" + HBadCodeSN + "'", "Sb_EquipMentCollectionTechParam_SN"); //注:当前只有一道工序有参数 如果有多道工序的话 需要增加 工序的过滤 if (ds.Tables[0].Rows.Count != 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { string HMouldID = ds.Tables[0].Rows[i]["HMouldID"].ToString(); string HTechParamID = ds.Tables[0].Rows[i]["HTechParamID"].ToString(); string HCount = ds.Tables[0].Rows[i]["HCount"].ToString(); string HGetTime = ds.Tables[0].Rows[i]["HCreateTime"].ToString(); string HResult = ds.Tables[0].Rows[i]["HResult"].ToString(); DataSet dataSet = oCN.RunProcReturn("select * from Sc_StationOutBillSub_TechParam WITH(NOLOCK) where HInterID=" + HInterID + " and HBillNo_bak='" + HBillNo + "'", "Sc_StationOutBillSub_TechParam"); sql = "insert into Sc_StationOutBillSub_TechParam(HInterID,HBillNo_bak,HEntryID ,HSourceInterID,HSourceEntryID,HSourceBillNo,HTechParamID,HRelValue,HGetTime,HBarCode,HMouldID,HRemark)" + $"values({HInterID},'{HBillNo}',{(dataSet.Tables[0].Rows.Count + 1)},{HProcExchInterID},{HProcExchEntryID},'{HProcExchBillNo}',{HTechParamID},{HCount},'{HGetTime}','{HBadCodeSN}',{HMouldID},'{HResult}')"; oCN.RunProc(sql); } oCN.RunProc("update Sb_EquipMentCollectionTechParam_SN set HFlag=1 where HBarCode='" + HBadCodeSN + "'"); } } return true; } catch (Exception e) { oCN.RollBack(); CustomWriteLog("工艺参数保存:" + sql + e.Message, DateTime.Now.ToString("yyyy-MM-dd")); return false; } } //条码解析 public int SNHBardCodeList(string HBadCodeSN) { try { DataSet ds = oCN.RunProcReturn(@"select * from Gy_BarCodeBill where HBarCode='" + HBadCodeSN + "'", "Gy_BarCodeBill"); //判断条码是否存在条码档案 if (ds.Tables[0].Rows.Count > 0) { string HSourceBillNo = ds.Tables[0].Rows[0]["HSourceBillNo"].ToString(); DataSet dataTable = oCN.RunProcReturn(@"select * from Sc_ProcessExchangeBillMain where HBillNo='" + HSourceBillNo + "'", "Sc_ProcessExchangeBillMain"); //判断条码的源单 流转卡是否被删除 if (dataTable.Tables[0].Rows.Count == 0) { //如果源单已经删除 则条码也删除重新生成 oCN.RunProc("delete from Gy_BarCodeBill where HBarCode='" + HBadCodeSN + "'"); //重新查询条码档案 进入新增方法 ds = oCN.RunProcReturn(@"select * from Gy_BarCodeBill where HBarCode='" + HBadCodeSN + "'", "Gy_BarCodeBill"); } } //判断条码是否在数据库里 if (ds.Tables[0].Rows.Count == 0) { //判断长度是否为29位 无尘车间 if (HBadCodeSN.Length == 29) { string str1 = HBadCodeSN.Substring(18, 8); ds = oCN.RunProcReturn("select * from Sc_ProcessExchangeBillMain where HProjectNum like'" + str1 + "-1%'", "Sc_ProcessExchangeBillMain"); //判断是否能找到对应的流转卡 if (ds.Tables[0].Rows.Count > 0) { string HProcExchBillNo = ds.Tables[0].Rows[0]["HBillNo"].ToString(); get_HBardBillSave(HBadCodeSN, HProcExchBillNo); } else { str1 = HBadCodeSN.Substring(23, 3); ds = oCN.RunProcReturn("select * from Sc_ProcessExchangeBillMain where HProjectNum like'" + str1 + "-1%'", "Sc_ProcessExchangeBillMain"); //判断是否能找到对应的流转卡 if (ds.Tables[0].Rows.Count > 0) { string HProcExchBillNo = ds.Tables[0].Rows[0]["HBillNo"].ToString(); get_HBardBillSave(HBadCodeSN, HProcExchBillNo); } else { oCN.RollBack(); CustomWriteLog("条码:" + HBadCodeSN + ",流转卡不存在!", DateTime.Now.ToString("yyyy-MM-dd")); return 2; } } } //判断长度是否为50位 15车间 else if (HBadCodeSN.Length == 50) { string str1 = HBadCodeSN.Substring(42, 8); ds = oCN.RunProcReturn("select * from Sc_ProcessExchangeBillMain where HProjectNum like'" + str1 + "-1%'", "Sc_ProcessExchangeBillMain"); //判断是否能找到对应的流转卡 if (ds.Tables[0].Rows.Count > 0) { string HProcExchBillNo = ds.Tables[0].Rows[0]["HBillNo"].ToString(); get_HBardBillSave(HBadCodeSN, HProcExchBillNo); } else { oCN.RollBack(); CustomWriteLog("条码:" + HBadCodeSN + ",流转卡不存在!", DateTime.Now.ToString("yyyy-MM-dd")); return 2; } } else { oCN.RollBack(); CustomWriteLog("条码:" + HBadCodeSN + ",长度不等于29位!", DateTime.Now.ToString("yyyy-MM-dd")); return 2; } } else { return 1; } return 0; } catch (Exception e) { oCN.RollBack(); CustomWriteLog("条码:" + HBadCodeSN + "," + e.Message, DateTime.Now.ToString("yyyy-MM-dd")); return 2; } } //新增条码数据 public void get_HBardBillSave(string HBarCode, string HProcExchBillNo) { try { //查询流转卡数据 DataSet dataSet = oCN.RunProcReturn(@"select a.HPRDORGID, m.HModel 物料规格,m.HName 名物料称,a.HBillNo,a.HInterID,a.HMaterID,m.HNumber 物料代码,o.HNumber 组织代码,a.HUnitID,u.HNumber 单位代码 from Sc_ProcessExchangeBillMain a WITH(NOLOCK) left join Gy_Material m WITH(NOLOCK) on a.HMaterID=m.HItemID left join Xt_ORGANIZATIONS o WITH(NOLOCK) on a.HPRDORGID=o.HItemID left join Gy_Unit u WITH(NOLOCK) on a.HUnitID=u.HItemID where HBillNo='" + HProcExchBillNo + "'", "Sc_ProcessExchangeBillMain"); //判断流转卡不能为空 if (HBarCode.Trim() == "") { oCN.RollBack(); CustomWriteLog("条码:" + HBarCode + ",流转卡不能为空!", DateTime.Now.ToString("yyyy-MM-dd")); } //日期获取方式 string sDate = DateTime.Now.ToString(); string HWei = "0"; //尾数 string HBarCodeType = "唯一条码"; Int64 HMaterID = int.Parse(dataSet.Tables[0].Rows[0]["HMaterID"].ToString()); Int64 HAuxPropID = 0; Int64 HUnitID = int.Parse(dataSet.Tables[0].Rows[0]["HUnitID"].ToString()); double HQty2 = 1; string HBatchNo2 = ""; Int64 HSupID = 0; Int64 HGroupID = 0; int HPrintQty = 0; Int64 HSourceInterID = int.Parse(dataSet.Tables[0].Rows[0]["HInterID"].ToString()); Int64 HSourceEntryID = 1; string HSourceBillNo = dataSet.Tables[0].Rows[0]["HBillNo"].ToString(); string HSourceBillType = "3772"; Int64 HBarcodeNo = 0; //托号 Int64 HBarcodeQtys = 0; //总托数 Int64 HDeptID = 0; Int64 HWhID = 0; Int64 HSPID = 0; string HRemark = ""; string HMaterName = dataSet.Tables[0].Rows[0]["名物料称"].ToString(); string HMaterModel = dataSet.Tables[0].Rows[0]["物料规格"].ToString(); string HPinfan = ""; string HMTONo = ""; Int64 HCusID = 0; string HCusType = ""; DateTime HEndDate = DateTime.Now; string HWorkLineName = ""; string HSeOrderBillNo = ""; string HInnerBillNo = ""; bool HGiveAwayFlag = false; Int64 HEntryID = 1; string sExeReturnInfo = ""; Int64 HInterID = CreateBillID_Prod("85", ref sExeReturnInfo); int HOrgID = int.Parse(dataSet.Tables[0].Rows[0]["HPRDORGID"].ToString()); string HCoilNO2 = ""; string HFurnaceNO2 = ""; string HFactory2 = ""; decimal HAuxQty2 = 0; string HheatNO2 = ""; oCN.RunProc("insert into Gy_BarCodeBill (HBarCode,HBarCodeType,HMaterID,HUnitID,HQty" + ",HBatchNo,HSupID,HGroupID,HMaker,HMakeDate,HPrintQty,HinitQty" + ",HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HEndQty " + ",HBarcodeQtys,HBarcodeNo,HDeptID,HWhID,HSPID,HRemark " + ",HCusID,HCusType,HEndDate,HWorkLineName,HBarCodeDate " + ",HSTOCKORGID,HOWNERID,HSeOrderBillNo,HInterID,HEntryID " + ",HGiveAwayFlag " + ",HMaterName,HMaterModel,HPinfan,HAuxPropID,HMTONo,HInnerBillNo" + ",HProduceDate,HExpiryDate " + ") values (" + "'" + HBarCode + "','" + HBarCodeType + "'," + HMaterID.ToString() + "," + HUnitID.ToString() + "," + HQty2.ToString() + ",'" + HBatchNo2 + "'," + HSupID.ToString() + "," + HGroupID.ToString() + ",'',getdate()," + HPrintQty.ToString() + "," + HQty2.ToString() + ", " + HSourceInterID.ToString() + "," + HSourceEntryID.ToString() + ",'" + HSourceBillNo + "','" + HSourceBillType + "','" + HWei + "'" + ", " + HBarcodeQtys.ToString() + "," + HBarcodeNo.ToString() + "," + HDeptID.ToString() + "," + HWhID.ToString() + "," + HSPID.ToString() + ",'" + HRemark + "'" + ", " + HCusID.ToString() + ",'" + HCusType + "','" + HEndDate.ToShortDateString() + "','" + HWorkLineName + "','" + sDate + "'" + ", " + HOrgID.ToString() + "," + HOrgID.ToString() + ",'" + HSeOrderBillNo + "'," + HInterID.ToString() + "," + HEntryID.ToString() + "" + ", " + BoolToString(HGiveAwayFlag) + ",'" + HMaterName + "','" + HMaterModel + "','" + HPinfan + "'," + HAuxPropID.ToString() + ",'" + HMTONo + "','" + HInnerBillNo + "','','')"); } catch (Exception e) { oCN.RollBack(); CustomWriteLog("条码:" + HBarCode + "," + e.Message, DateTime.Now.ToString("yyyy-MM-dd")); } } //条码补充 public string TM_ZH(string HBadCodeSN) { try { //HBadCodeSN = "P1" + HBadCodeSN; //HBadCodeSN = HBadCodeSN.Substring(0, 13) + ":" + HBadCodeSN.Substring(13, 15); return HBadCodeSN; } catch (Exception e) { CustomWriteLog("条码:" + HBadCodeSN + "," + e.Message, DateTime.Now.ToString("yyyy-MM-dd")); return ""; } } //定时读取数据 private void timer1_Tick(object sender, EventArgs e) { DataTable list = oCN.RunProcReturn("select top 100 * from Sb_EquipMentCollection_SN WITH(NOLOCK) where HFlag=0 order by HCreateTime asc", "Sb_EquipMentCollection_SN").Tables[0]; //循环集合 for (int i = 0; i < list.Rows.Count; i++) { bool flag = false; string HResult = list.Rows[i]["HResult"].ToString(); string HBadCodeSN = list.Rows[i]["HBarCode"].ToString(); string HCreateTime = list.Rows[i]["HCreateTime"].ToString(); //HBadCodeSN = "P1099081-00-E:SDU324292189300"; //HResult = "OK"; //开始事务 oCN.BeginTran(); if (HResult == "OK") { flag = HResultOK(list.Rows[i]); } else if (HResult == "NG") { flag = HResultNG(list.Rows[i]); } if (flag) { oCN.RunProc("update Sb_EquipMentCollection_SN set HFlag=1 where HBarCode='" + HBadCodeSN + "' and HCreateTime='" + HCreateTime + "'"); //结束事务 oCN.Commit(); } } } //新增不良汇报单 public bool getBadBillAdd(DataRow dic) { try { //查询职员 DataSet ds = oCN.RunProcReturn("select * from Gy_Employee WITH(NOLOCK) where HNumber='" + dic["HEmpCode"].ToString() + "' ", "Gy_Employee"); long HEmpIDs = 0; string HMakers = ""; if (ds.Tables[0].Rows.Count != 0) { HEmpIDs = long.Parse(ds.Tables[0].Rows[0]["HItemID"].ToString());//职员ID HMakers = ds.Tables[0].Rows[0]["HName"].ToString(); } long HDeptID = 0;//车间 string sReturn = ""; if (GetSingleSystemParameter("WMS_CampanyName", ref sReturn) == "杭州斯莫尔") { HDeptID = 389505; } else { //ds = oCN.RunProcReturn("select * from Gy_Department where HNumber='" + dic["HDeptNumber"].ToString() + "' ", "Gy_Department"); //if (ds.Tables[0].Rows.Count != 0) //{ // HDeptID = long.Parse(ds.Tables[0].Rows[0]["HItemID"].ToString());//生产部门 //} HDeptID = 0; } string HProcExchInterID = "0"; string HProcExchEntryID = "0"; string HProcExchBillNo = ""; long HMaterID = 0; long HProcID = 0; long HUnitID = 0; long HICMOInterID = 0; long HICMOEntryID = 0; string HICMOBillNo = ""; ds = oCN.RunProcReturn(@"select b.HProcID, HMaterID,HUnitID,a.HICMOInterID,a.HICMOEntryID,a.HICMOBillNo,HBillNo,a.HInterID,b.HEntryID,a.HBillNo from Sc_ProcessExchangeBillMain a WITH(NOLOCK) inner join Sc_ProcessExchangeBillSub b WITH(NOLOCK) on a.HInterID=b.HInterID left join Gy_Process p WITH(NOLOCK) on b.HProcID=p.HItemID where HBillNo=(select HSourceBillNo from Gy_BarCodeBill WITH(NOLOCK) where HBarCode='" + dic["HBarCode"].ToString() + "') and p.HNumber='" + dic["HProcNumber"].ToString() + "' ", "Sc_ProcessExchangeBillMain"); if (ds.Tables[0].Rows.Count != 0) { HMaterID = int.Parse(ds.Tables[0].Rows[0]["HMaterID"].ToString()); HUnitID = int.Parse(ds.Tables[0].Rows[0]["HUnitID"].ToString()); HICMOInterID = int.Parse(ds.Tables[0].Rows[0]["HICMOInterID"].ToString()); HICMOEntryID = int.Parse(ds.Tables[0].Rows[0]["HICMOEntryID"].ToString()); HICMOBillNo = ds.Tables[0].Rows[0]["HICMOBillNo"].ToString(); HProcExchInterID = ds.Tables[0].Rows[0]["HInterID"].ToString(); HProcExchEntryID = ds.Tables[0].Rows[0]["HEntryID"].ToString(); HProcExchBillNo = ds.Tables[0].Rows[0]["HBillNo"].ToString(); HProcID = int.Parse(ds.Tables[0].Rows[0]["HProcID"].ToString()); } ds = oCN.RunProcReturn("select HItemID from Gy_Source WITH(NOLOCK) where HNumber='" + dic["HSourceCode"].ToString() + "' ", "Gy_Source"); long HSourceID = 0; if (ds.Tables[0].Rows.Count != 0) { HSourceID = long.Parse(ds.Tables[0].Rows[0]["HItemID"].ToString());//生产资源ID } string sExeReturnInfo = ""; long HInterID = CreateBillID("3717", ref sExeReturnInfo);//递入type得到的单据ID string HBillNo = CreateBillCode_Prod("3717", ref sExeReturnInfo, true);//递入type得到的单据号 DateTime HDate = DateTime.Parse(dic["HCreateTime"].ToString());//日期 string HRemark = "";//备注 long HEmpID = HEmpIDs;//质检员 long HGroupID = 22;//班组ID 白班 string HMaker = HMakers;//制单人 long HMainSourceInterID = HICMOInterID; long HMainSourceEntryID = HICMOEntryID; string HMainSourceBillNo = HICMOBillNo; //主表 oCN.RunProc(@"Insert Into Sc_QualityReportBillMain (HBillType,HBillSubType,HBillStatus,HInterID,HBillNo,HDate ,HYear,HPeriod,HRemark,HMaker,HMakeDate ,HEmpID,HGroupID,HDeptID,HMainSourceInterID,HMainSourceEntryID,HMainSourceBillNo) values('3717','3717',1," + HInterID + ",'" + HBillNo + "','" + HDate + "'" + "," + DateTime.Now.Year + "," + DateTime.Now.Month + ",'" + HRemark + "','" + HMaker + "',getdate()" + ",'" + HEmpID + "'," + HGroupID + "," + HDeptID + "," + HMainSourceInterID + "," + HMainSourceEntryID + ",'" + HMainSourceBillNo + "') "); string HBarCode = dic["HBarCode"].ToString(); string HMakeDate = dic["HCreateTime"].ToString(); string HResult = dic["HResult"].ToString(); oCN.RunProc($@"Insert into Sc_QualityReportBillSub (HInterID,HENTRYID,HBillNo_bak,HEmpID,HBarCode,HBadReasonID,HAddr,HMaker,HMakeDate ,HMaterID,HUnitID,HRemark,HSourceID,HICMOInterID,HICMOEntryID,HICMOBillNo,HReportType ,HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney ,HQty,HResult,HProcExchInterID,HProcExchEntryID,HProcExchBillNo,HProcID) values({HInterID},1,'{HBillNo}',{HEmpID},'{HBarCode}',219,'','{HMaker}','{HMakeDate}' ,{HMaterID},{ HUnitID},'',{HSourceID},{ HICMOInterID},{ HICMOEntryID},'{HICMOBillNo}',0 ,0,0,'','',0,0 ,1,'{HResult}',{ HProcExchInterID},{ HProcExchEntryID},'{ HProcExchBillNo}'," + HProcID + ")"); return true; } catch (Exception e) { oCN.RollBack(); CustomWriteLog("报错原因:" + e.Message + "!", DateTime.Now.ToString("yyyy-MM-dd")); return false; } } #region 基础方法 //查询系统参数 public string GetSingleSystemParameter(string sKey, ref string sReturn) { try { //查询主表 DataSet DsSub; //循环 DsSub = oCN.RunProcReturn("Select HValue from Xt_SystemParameter with(nolock) Where HKey='" + sKey + "' ", "Xt_SystemParameter"); if (DsSub == null || DsSub.Tables[0].Rows.Count == 0) { sReturn = "找不到该参数!"; return ""; } else { return isStrNull(DsSub.Tables[0].Rows[0]["HValue"]); } } catch (Exception e) { sReturn = e.Message; throw (e); } } public static Int64 CreateBillID_Prod(string BillCode, ref string sReturn) { try { string BillType = ""; DataSet Ds; DBHelper oCn = new DBHelper(); Int64 lID; Ds = oCn.RunProcReturn("exec h_p_Xt_GetMaxBillID '" + BillCode + "'", "h_p_Xt_GetMaxBillID"); if (Ds == null || Ds.Tables[0].Rows.Count == 0) { lID = -1; } else { lID = isLong(Ds.Tables[0].Rows[0]["HBillNo"]); } oCn.CnClose(); oCn.CnDispose(); oCn = null; Ds = null; return lID; } catch (Exception e) { return -2; } } //判断是否是INT64 public static Int64 isLong(object message) { try { return Convert.ToInt64(message); } catch (Exception e) { return 0; } } //布尔转为字符 public static string BoolToString(object b) { try { if (Convert.ToBoolean(b)) return "1"; else return "0"; } catch (Exception e) { return "0"; } } public static Int64 CreateBillID(string BillCode, ref string sReturn) { string BillType = ""; DataSet Ds; DBHelper oCn = new DBHelper(); Int64 lID; Ds = oCn.RunProcReturn("select * from Gy_BillNumber with (nolock) where BillCode='" + BillCode.Trim() + "'", "Gy_BillNumber"); if (Ds.Tables[0].Rows.Count != 0) { lID = isLong(Ds.Tables[0].Rows[0]["IDNow"].ToString()); BillType = Ds.Tables[0].Rows[0]["BillType"].ToString().Trim(); } else { lID = 0; } //同类型单据 自增1 if (BillType.Trim() != "") { oCn.RunProc("update Gy_BillNumber set IDNow=IDNow+1 where BillType='" + BillType.Trim() + "'"); } oCn.CnClose(); oCn.CnDispose(); oCn = null; Ds = null; return lID; } //得到最大单据号 //得到最大单据号 存储过程 public static string CreateBillCode_Prod(string BillCode, ref string sReturn, bool Add) { try { string BillType = ""; DataSet Ds; DBHelper oCn = new DBHelper(); string sBIllNO; Ds = oCn.RunProcReturn("exec h_p_Xt_GetMaxBillNo '" + BillCode + "'", "h_p_Xt_GetMaxBillNo"); if (Ds == null || Ds.Tables[0].Rows.Count == 0) { sBIllNO = "ERROR"; } else { sBIllNO = isStrNull(Ds.Tables[0].Rows[0]["HBillNo"]); } oCn.CnClose(); oCn.CnDispose(); oCn = null; Ds = null; return sBIllNO; } catch (Exception e) { return "ERROR"; } } //判断是否NULL public static string isStrNull(object message) { try { if (message == null) { return ""; } else { return message.ToString().Trim(); } } catch (Exception e) { return ""; } } private static readonly object lockObj = new object(); //写日志 public static void CustomWriteLog(object obj, string FileName, string filePath = "Vlog", bool isAppend = true) { try { lock (lockObj) { filePath = $@"{filePath}\{FileName}.txt"; filePath = AppDomain.CurrentDomain.BaseDirectory + filePath; if (!System.IO.Directory.Exists(Path.GetDirectoryName(filePath))) { System.IO.Directory.CreateDirectory(Path.GetDirectoryName(filePath)); } bool fileExists = System.IO.File.Exists(filePath); //不存在 则创建该文件 if (!fileExists) { System.IO.File.Create(filePath).Close(); } using (StreamWriter writer = new StreamWriter(filePath, isAppend)) { //存在的时候才写一行 if (fileExists && isAppend) { writer.WriteLine(); } var content = obj is string ? obj : JsonConvert.SerializeObject(obj); writer.WriteLine($"{DateTime.Now} {content}"); } } } catch (Exception ex) { } } #endregion private void Form1_FormClosing(object sender, FormClosingEventArgs e) { if (MessageBox.Show("确定要关闭吗？", "确认", MessageBoxButtons.YesNo) == DialogResult.No) { e.Cancel = true; } } } }