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();
}
}
