private void ImportData() { string ERPInDirectory = Convert.ToString(ConfigurationManager.AppSettings["ERPIN"].ToString().Trim()); if (!Directory.Exists(ERPInDirectory)) { log.Info("ERPIN路径不存在。"); return; } string ExcelPath = ERPInDirectory; List <FileInfo> ExcelFiles = getFiles(ExcelPath, ".xls"); if (ExcelFiles.Count == 0) { log.Info("没有检测到Excel文件。"); return; } foreach (FileInfo fileInfo in ExcelFiles) { try { string FilePath = fileInfo.FullName; log.Info("正在处理:" + FilePath); MSExcel.Application excelApp = new MSExcel.Application { Visible = false//是打开可见 }; MSExcel.Workbooks _workbooks = excelApp.Workbooks; MSExcel._Workbook _workbook = _workbooks.Add(FilePath); MSExcel._Worksheet whs = _workbook.Sheets[1];//获取第2张工作表 TODO:生产环境改回1 whs.Activate(); JArray ModelArray = new JArray(); JObject ModelJson = new JObject(); JArray FOrderEntrys = new JArray(); List <string> ordenoList = new List <string>(); int excelIndex = 2; int BillNoLineNumber = 0; //表头单据编号所在行数 bool IsAllSuccess = true; //是否整个文件导入成功 bool HasSuccess = false; //是否整个文件有导入成功的订单 bool HasError = false; //是否整个文件有导入失败的订单 while (true) { try { MSExcel.Range rang = (MSExcel.Range)whs.Cells[excelIndex, 32];//ERP收料通知单单号 if (rang.Value != null) { string ERPBillNo = Convert.ToString(rang.Value); string PrevFBillNo = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex - 1, 32]).Value); //上一行的ERP收料通知单单号 string NextFBillNo = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex + 1, 32]).Value); //下一行的ERP收料通知单单号 string QISBillNo = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 1]).Value); //QIS检验单号 string FDate = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 2]).Value); string MaterialNumber = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 7]).Value); string ComputerResult = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 15]).Value); //电脑判定 double CheckQty = Convert.ToDouble(((MSExcel.Range)whs.Cells[excelIndex, 14]).Value); //批量数(报检数量) double RealQty = Convert.ToDouble(((MSExcel.Range)whs.Cells[excelIndex, 22]).Value); //实收数量(合格数量) string CheckResult = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 16]).Value); //检验结果 double unqualifiedQty = CheckQty - RealQty; //不合格数量 //ERP收料通知单单号和上一行不同,则是另一张单据 if (!PrevFBillNo.Equals(ERPBillNo)) { BillNoLineNumber = excelIndex; log.Info("正在读取:" + ERPBillNo); } string NeedPushEntryIds = ""; //excel的一行是相同物料合并数量的,在ERP查源单,查出来可能有多行物料 List <List <object> > Bills = PUR_ReceiveBill.GetAllBill(client, "FBillNo='" + ERPBillNo + "'" + " and FMaterialID.FNumber='" + MaterialNumber + "'"); for (int i = 0; i < Bills.Count; i++) { double FQty = 0; string SrcBillID = Convert.ToString(Bills[i][0]); string SrcBillNo = Convert.ToString(Bills[i][1]); string SrcEntryID = Convert.ToString(Bills[i][8]); double FActReceiveQty = Convert.ToDouble(Bills[i][7]); //交货数量 double FCheckJoinQty = Convert.ToDouble(Bills[i][9]); //检验关联数量 double NoCheckQty = FActReceiveQty - FCheckJoinQty; //剩余未检验数量 string SrcBillTypeID = Convert.ToString(Bills[i][10]); string SrcSeq = Convert.ToString(Bills[i][11]); if (NoCheckQty > CheckQty) { FQty = CheckQty; } else { FQty = NoCheckQty; } CheckQty = CheckQty - FQty; if (FQty <= 0) { continue; } NeedPushEntryIds = NeedPushEntryIds + SrcEntryID + ","; //一行下推成一单 string NewBillId = PUR_ReceiveBill.PushToInspectBill(client, SrcEntryID); if (!"".Equals(NewBillId)) { //修改数量 List <List <object> > InspectBills = QM_InspectBill.GetAllBill(client, "FID=" + NewBillId); if (InspectBills.Count > 0) { string NewBillNo = Convert.ToString(InspectBills[0][1]); ordenoList.Add(NewBillNo); JArray MultiLanguageTextArr = new JArray(); JObject MultiLanguageTextJson = new JObject(); MultiLanguageTextJson.Add("Key", 2052); MultiLanguageTextJson.Add("Value", "QIS导入,单号" + QISBillNo); JObject Entry = new JObject(); ModelJson.Add("FID", Convert.ToString(InspectBills[0][0])); ModelJson.Add("FEntity", FOrderEntrys); ModelJson.Add("FDescription", "QIS导入,单号" + QISBillNo); FOrderEntrys.Add(Entry); Entry.Add("FEntryID", Convert.ToString(InspectBills[0][2])); Entry.Add("FInspectQty", FQty); Entry.Add("FMemo", "QIS导入,单号" + QISBillNo); if (!"合格".Equals(ComputerResult)) { Entry.Add("FInspectResult", "2"); //检验结果=不合格 JArray FPolicyDetailEntrys = new JArray(); //使用决策 子单据体 JObject FPolicyDetailEntry1 = new JObject(); JObject FPolicyDetailEntry2 = new JObject(); switch (CheckResult) { case "退货": break; case "让步接收": Entry.Add("FPolicyDetail", FPolicyDetailEntrys); FPolicyDetailEntrys.Add(FPolicyDetailEntry1); FPolicyDetailEntry1.Add("FUsePolicy", "A"); FPolicyDetailEntry1.Add("FPolicyQty", RealQty); FPolicyDetailEntrys.Add(FPolicyDetailEntry2); FPolicyDetailEntry2.Add("FUsePolicy", "B"); FPolicyDetailEntry2.Add("FPolicyQty", unqualifiedQty); break; case "挑选": Entry.Add("FPolicyDetail", FPolicyDetailEntrys); FPolicyDetailEntrys.Add(FPolicyDetailEntry1); FPolicyDetailEntry1.Add("FUsePolicy", "A"); FPolicyDetailEntry1.Add("FPolicyQty", RealQty); FPolicyDetailEntrys.Add(FPolicyDetailEntry2); FPolicyDetailEntry2.Add("FUsePolicy", "E"); FPolicyDetailEntry2.Add("FPolicyQty", unqualifiedQty); break; } } else if ("合格".Equals(ComputerResult) && "内部原因".Equals(CheckResult)) { JArray FPolicyDetailEntrys = new JArray();//使用决策 子单据体 JObject FPolicyDetailEntry1 = new JObject(); Entry.Add("FPolicyDetail", FPolicyDetailEntrys); FPolicyDetailEntrys.Add(FPolicyDetailEntry1); FPolicyDetailEntry1.Add("FDetailID", Convert.ToString(InspectBills[0][3])); FPolicyDetailEntry1.Add("FMemo1", "内部原因"); } Dictionary <string, object> ImportResult = QM_InspectBill.SaveBill(client, new StringBuilder(ModelJson.ToString()), new List <string>(ordenoList)); //提交审核检验单 CommonOperate commonOperate = new CommonOperate(); commonOperate.SubmitBill(client, "QM_InspectBill", new StringBuilder("\"" + NewBillNo + "\"")); commonOperate.AuditBill(client, "QM_InspectBill", new StringBuilder("\"" + NewBillNo + "\"")); ModelJson = new JObject(); FOrderEntrys = new JArray(); if (!Convert.ToBoolean(ImportResult["IsSuccess"])) { HasError = true; IsAllSuccess = false; } else { HasSuccess = true; } } else { log.Info("下推的单据已被删除。"); } } } //移除掉最后一个"," if (NeedPushEntryIds.Length > 0) { NeedPushEntryIds = NeedPushEntryIds.Remove(NeedPushEntryIds.LastIndexOf(","), 1); } } else { break; } } catch (Exception ex) { log.Error(ex); } finally { excelIndex++; } } string ERPBackup = Convert.ToString(ConfigurationManager.AppSettings["ERPBackup"].ToString().Trim()); string ResultDirectory = ERPBackup; //处理完后文件保存到这里 if (!Directory.Exists(ResultDirectory)) //如果不存在就创建文件夹 { Directory.CreateDirectory(ResultDirectory); } whs.SaveAs(Path.Combine(ResultDirectory, fileInfo.Name), 51); //关闭对象 Marshal.ReleaseComObject(_workbook); Marshal.ReleaseComObject(whs); excelApp.Quit(); GC.Collect(); fileInfo.Delete(); } catch (Exception ex) { log.Error(ex); } } Executed = true; }
private void outPutReciveBill() { List <List <object> > Bills = PUR_ReceiveBill.GetAllBill(client, "FDocumentStatus = 'C' and FCheckInComing = 1 and F_PAEZ_Exported=0");//F_PAEZ_Exported=0 未导出 //List<List<object>> Bills = PUR_ReceiveBill.GetAllBill(client, "FBillNo='CGSL000328'");//F_PAEZ_Exported=0 未导出 if (Bills.Count == 0) { log.Info("没有可以导出的收料通知单。"); return; } MSExcel.Application excelApp = new MSExcel.Application { Visible = false//是打开可见 }; MSExcel.Workbooks _workbooks = excelApp.Workbooks; //MSExcel._Workbook _workbook = _workbooks.Add(AppDomain.CurrentDomain.BaseDirectory+"\\DD_20200901084512--ERP输出模板.XLS"); MSExcel._Workbook _workbook = _workbooks.Add(System.Reflection.Missing.Value); MSExcel._Worksheet whs = _workbook.Sheets[1];//获取第1张工作表 try { whs.Name = "Sheet1"; //headline whs.Cells[1, 1] = "进货单号"; whs.Cells[1, 2] = "产品编号"; whs.Cells[1, 3] = "物料名称"; whs.Cells[1, 4] = "物料规格"; whs.Cells[1, 5] = "厂商"; whs.Cells[1, 6] = "报检数量"; whs.Cells[1, 7] = "厂商编码"; whs.Cells[1, 8] = "物料分类名称"; whs.Cells[1, 9] = "实收数量"; whs.Cells[1, 10] = "不良数"; whs.Cells[1, 11] = "QIS报检单号"; //同一单据内,相同物料合并数量 Hashtable AllMaterialHT = new Hashtable(); for (int i = 0; i < Bills.Count; i++) { string BillNo = Convert.ToString(Bills[i][1]); string MaterialNumber = Convert.ToString(Bills[i][2]); double Qty = Convert.ToDouble(Bills[i][7]); if (AllMaterialHT.ContainsKey(BillNo + MaterialNumber)) { JObject jObject = AllMaterialHT[BillNo + MaterialNumber] as JObject; double OldQty = Convert.ToDouble(Convert.ToString(jObject["Qty"])); jObject["Qty"] = OldQty + Qty; } else { List <object> OtherInfo = Bills[i]; JObject jObject = new JObject(); jObject.Add("MaterialNumber", MaterialNumber); jObject.Add("Qty", Qty); jObject.Add("OtherInfoIndex", i); //原来的List的索引,保存以便在下方获取原来的其他数据 AllMaterialHT.Add(BillNo + MaterialNumber, jObject); } } string DateTimeStr = DateTime.Now.ToString("yyyyMMddHHmmssf"); Dictionary <string, string> BillIDsDic = new Dictionary <string, string>(); StringBuilder sbBillNos = new StringBuilder(); //for (int i = 0; i < Bills.Count; i++) int ExcelRow = 2; foreach (string BillAndMaterialNumber in AllMaterialHT.Keys) { JObject jObject = AllMaterialHT[BillAndMaterialNumber] as JObject; string MaterialNumber = Convert.ToString(jObject["MaterialNumber"]); double Qty = Convert.ToDouble(Convert.ToString(jObject["Qty"])); int i = Convert.ToInt32(Convert.ToString(jObject["OtherInfoIndex"])); List <object> BillInfo = Bills[i]; string BillID = Convert.ToString(Bills[i][0]); string BillNo = Convert.ToString(Bills[i][1]); string FDetailEntity_FSeq = Convert.ToString(Bills[i][11]); if (!BillIDsDic.ContainsKey(BillID)) { BillIDsDic.Add(BillID, BillNo); sbBillNos.Append("\"").Append(BillNo).Append("\","); } whs.Cells[ExcelRow, 1] = BillNo; whs.Cells[ExcelRow, 2] = Bills[i][2]; whs.Cells[ExcelRow, 3] = Bills[i][3]; whs.Cells[ExcelRow, 4] = Bills[i][4]; whs.Cells[ExcelRow, 5] = Bills[i][6]; whs.Cells[ExcelRow, 6] = Qty; whs.Cells[ExcelRow, 7] = Bills[i][5]; whs.Cells[ExcelRow, 8] = Bills[i][12]; whs.Cells[ExcelRow, 9] = 0; whs.Cells[ExcelRow, 10] = 0; whs.Cells[ExcelRow, 11] = "'" + DateTimeStr + (i + 1).ToString("000"); ExcelRow++; } if (sbBillNos.Length > 0) { sbBillNos.Remove(sbBillNos.Length - 1, 1);; //移除掉最后一个"," } string ERPOUTDirectory = Convert.ToString(ConfigurationManager.AppSettings["ERPOUT"].ToString().Trim()); if (!Directory.Exists(ERPOUTDirectory))//如果不存在就创建文件夹 { Directory.CreateDirectory(ERPOUTDirectory); } string fileName = ERPOUTDirectory + "K3Cloud_DD_" + DateTimeStr + ".XLS"; whs.SaveAs(fileName, 51); log.Info("正在更新该订单的同步状态。"); PUR_ReceiveBill.ExportedToQIS(client, sbBillNos); //UpdateSyncStatus("PUR_ReceiveBill", BillIDsDic); } catch (Exception ex) { log.Error(ex); } finally { //关闭对象 Marshal.ReleaseComObject(_workbook); Marshal.ReleaseComObject(whs); excelApp.Quit(); GC.Collect(); } }