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