示例#1
0
        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;
        }