DEMOMySql/MicroServices/Business/Business.Application/SystemJobManagement/SystemJobAppService.cs

using Business.Core.Enum;
using Business.Core.Utilities;
using Business.Domain;
using Business.EntityFrameworkCore;
using Business.EntityFrameworkCore.SqlRepositories;
using Business.ResourceExamineManagement;
using Business.ResourceExamineManagement.Dto;
using Business.SystemJob;
using EFCore.BulkExtensions;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using MongoDB.Driver.Linq;
using Newtonsoft.Json;
using NLog;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Volo.Abp.Application.Services;
using Volo.Abp.Domain.Repositories;
using Volo.Abp.MultiTenancy;
using WkHtmlToPdfDotNet;

namespace Business.SystemJobManagement
{
    public class SystemJobAppService : ApplicationService, ISystemJobAppService
    {
        /// <summary>
        /// 物料bom
        /// </summary>
        private IRepository<ic_bom, long> _mysql_ic_bom;

        /// <summary>
        /// 物料bom子表
        /// </summary>
        private IRepository<ic_bom_child, long> _mysql_ic_bom_child;

        /// <summary>
        /// 物料
        /// </summary>
        private IRepository<ic_item, long> _mysql_ic_item;

        /// <summary>
        /// 供应商
        /// </summary>
        private IRepository<srm_supplier, long> _mysql_srm_supplier;

        /// <summary>
        /// 物料采购报价单
        /// </summary>
        private IRepository<srm_purchase, long> _mysql_srm_purchase;

        /// <summary>
        /// 物料采购报价单
        /// </summary>
        private IRepository<crm_customer, long> _mysql_crm_customer;

        /// <summary>
        /// 销售订单
        /// </summary>
        private IRepository<crm_seorder, long> _mysql_crm_seorder;

        /// <summary>
        /// 销售订单明细
        /// </summary>
        private IRepository<crm_seorderentry, long> _mysql_crm_seorderentry;

        private IRepository<srm_po_occupy, long> _mysql_srm_po_occupy;

        private IRepository<ic_item_stock, long> _mysql_ic_item_stock;

        /// <summary>
        /// 雪花算法
        /// </summary>
        SnowFlake help = new SnowFlake();
        private readonly BusinessDbContext _businessDbContext;
        private readonly ISqlRepository<CustMaster> _custMaster;
        private readonly ISqlRepository<SuppMaster> _suppMaster;
        private readonly ISqlRepository<ConsigneeAddressMaster> _consigneeAddressMaster;
        private readonly ISqlRepository<ItemMaster> _itemMaster;
        private readonly ISqlRepository<ProductStructureMaster> _productStructureMaster;

        private readonly ISqlRepository<PurOrdMaster> _purOrdMaster;
        private readonly ISqlRepository<PurOrdDetail> _purOrdDetail;


        /// <summary>
        /// 生产工单主表
        /// </summary>
        private readonly IRepository<mes_morder, long> _mysql_mes_morder;
        /// <summary>
        /// 生产工单子表
        /// </summary>
        private readonly IRepository<mes_moentry, long> _mysql_mes_moentry;
        /// <summary>
        /// 工单主表
        /// </summary>
        private readonly ISqlRepository<WorkOrdMaster> _workOrdMaster;

        /// <summary>
        /// 替代群组
        /// </summary>
        private IRepository<ic_substitute, long> _mysql_ic_substitute;

        /// <summary>
        /// 物料替代多群组
        /// </summary>
        private IRepository<ic_substitute_group, long> _mysql_ic_substitute_group;

        /// <summary>
        /// 物料替代多群组明细
        /// </summary>
        private IRepository<ic_substitute_group_detail, long> _mysql_ic_substitute_group_detail;

        /// <summary>
        /// 工艺路径
        /// </summary>
        private readonly IRepository<mo_mes_technique, long> _mes_technique;
        private IRepository<mes_technique, long> _mysql_mes_technique;

        /// <summary>
        /// 工序
        /// </summary>
        private readonly IRepository<mo_mes_process, long> _mes_process;
        private IRepository<mes_process, long> _mysql_mes_process;

        /// <summary>
        /// 工艺路径关联工序
        /// </summary>
        private readonly IRepository<mo_mes_tech_process, long> _mes_tech_process;
        private IRepository<mes_tech_process, long> _mysql_mes_tech_process;

        private IRepository<srm_po_main, long> _srm_po_main;
        private IRepository<srm_po_list, long> _srm_po_list;

        private IRepository<ic_item_stockoccupy, long> _ic_item_stockoccupy;
        private ISqlRepository<NbrMaster> _nbrMaster;
        private ISqlRepository<NbrDetail> _nbrDetail;
        private readonly ISqlRepository<ASNBOLShipperDetail> _ASNBOLShipperDetail;

        /// <summary>
        /// 生产排产
        /// </summary>
        private readonly ProductionScheduleAppService _productionScheduleAppService;

        public SystemJobAppService(
         IRepository<ic_bom, long> mysql_ic_bom,
         IRepository<ic_bom_child, long> mysql_ic_bom_child,
         IRepository<ic_item, long> mysql_ic_item,
         IRepository<srm_supplier, long> mysql_srm_supplier,
         IRepository<srm_purchase, long> mysql_srm_purchase,
         IRepository<crm_customer, long> mysql_crm_customer,
         IRepository<crm_seorder, long> mysql_crm_seorder,
         IRepository<crm_seorderentry, long> mysql_crm_seorderentry,
         IRepository<ic_substitute, long> mysql_ic_substitute,
         IRepository<ic_substitute_group, long> mysql_ic_substitute_group,
         IRepository<ic_substitute_group_detail, long> mysql_ic_substitute_group_detail,
         IRepository<mes_technique, long> mysql_mes_technique,
         IRepository<mes_process, long> mysql_mes_process,
         IRepository<mes_tech_process, long> mysql_mes_tech_process,
         ISqlRepository<CustMaster> custMaster,
         ISqlRepository<SuppMaster> suppMaster,
         IRepository<ic_item_stock, long> mysql_ic_item_stock,
         ISqlRepository<ConsigneeAddressMaster> consigneeAddressMaster,
         ISqlRepository<ItemMaster> itemMaster,
         ISqlRepository<ProductStructureMaster> productStructureMaster,
         IRepository<mo_mes_technique, long> mes_technique,
         IRepository<mo_mes_process, long> mes_process,
         IRepository<mo_mes_tech_process, long> mes_tech_process,
         ProductionScheduleAppService productionScheduleAppService,
         ISqlRepository<WorkOrdMaster> workOrdMaster,
         IRepository<mes_morder, long> mysql_mes_morder,
         IRepository<mes_moentry, long> mysql_mes_moentry,
         ISqlRepository<PurOrdMaster> purOrdMaster,
         ISqlRepository<PurOrdDetail> purOrdDetail,
         IRepository<srm_po_main, long> srm_po_main,
         IRepository<srm_po_list, long> srm_po_list,
         IRepository<srm_po_occupy, long> mysql_srm_po_occupy,
         ISqlRepository<NbrMaster> nbrMaster,
         ISqlRepository<NbrDetail> nbrDetail,
         IRepository<ic_item_stockoccupy, long> ic_item_stockoccupy,
         ISqlRepository<ASNBOLShipperDetail> ASNBOLShipperDetail,
         BusinessDbContext businessDbContext
         )
        {
            _mysql_ic_bom = mysql_ic_bom;
            _mysql_ic_bom_child = mysql_ic_bom_child;
            _mysql_ic_item = mysql_ic_item;
            _mysql_srm_supplier = mysql_srm_supplier;
            _mysql_srm_purchase = mysql_srm_purchase;
            _mysql_crm_customer = mysql_crm_customer;
            _mysql_crm_seorder = mysql_crm_seorder;
            _mysql_crm_seorderentry = mysql_crm_seorderentry;
            _mysql_ic_substitute = mysql_ic_substitute;
            _mysql_ic_substitute_group = mysql_ic_substitute_group;
            _mysql_ic_substitute_group_detail = mysql_ic_substitute_group_detail;
            _mysql_mes_technique = mysql_mes_technique;
            _mysql_mes_process = mysql_mes_process;
            _mysql_mes_tech_process = mysql_mes_tech_process;
            _custMaster = custMaster;
            _suppMaster = suppMaster;
            _consigneeAddressMaster = consigneeAddressMaster;
            _itemMaster = itemMaster;
            _productStructureMaster = productStructureMaster;
            _mes_technique = mes_technique;
            _mes_process = mes_process;
            _mes_tech_process = mes_tech_process;
            _businessDbContext = businessDbContext;
            _itemMaster = itemMaster;
            _productionScheduleAppService = productionScheduleAppService;
            _workOrdMaster = workOrdMaster;
            _mysql_mes_morder = mysql_mes_morder;
            _mysql_mes_moentry = mysql_mes_moentry;
            _purOrdMaster = purOrdMaster;
            _purOrdDetail = purOrdDetail;
            _srm_po_main = srm_po_main;
            _srm_po_list = srm_po_list;
            _nbrMaster = nbrMaster;
            _nbrDetail = nbrDetail;
            _mysql_ic_item_stock = mysql_ic_item_stock;
            _mysql_srm_po_occupy = mysql_srm_po_occupy;
            _ic_item_stockoccupy = ic_item_stockoccupy;
            _ASNBOLShipperDetail = ASNBOLShipperDetail;
        }

        public string SyncWMSDataToMySQLJob()
        {
            try
            {
                SyncCustMaster();
                SyncSuppMaster();
                //SyncSalesOrdMaster();
                //SyncSalesOrdMasterEntry();
                SyncItemMaster();
                SyncBom();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            return "同步完成";
        }

        public string LogInstallJob()
        {
            try
            {
                LogManager.Configuration.Install(new NLog.Config.InstallationContext());//每天0点执行一次
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            return "任务执行成功";

        }

        public async Task<string> SyncBaseDataToMongoDBJob()
        {
            try
            {
                //物料采购报价单
                List<srm_purchase> srm_purchases = _mysql_srm_purchase.GetListAsync().Result;
                if (srm_purchases.Count > 0)
                {
                    //先清空表数据
                    await MongoHelper<mo_srm_purchase>.DeleteManyAsync(p => p.mysql_id != -1);
                    var mosrm_purchase = ObjectMapper.Map<List<srm_purchase>, List<mo_srm_purchase>>(srm_purchases);
                    mosrm_purchase.ForEach(s => s.GenerateNewId(help.NextId()));
                    await MongoHelper<mo_srm_purchase>.InsertManyAsync(mosrm_purchase);
                }
                //同步物料BOM明细数据
                var icbom_childs = _mysql_ic_bom_child.GetListAsync().Result;
                if (icbom_childs.Count > 0)
                {
                    //先清空表数据
                    await MongoHelper<mo_ic_bom_child>.DeleteManyAsync(p => p.mysql_id != -1);
                    var moIcbom_childs = ObjectMapper.Map<List<ic_bom_child>, List<mo_ic_bom_child>>(icbom_childs);
                    moIcbom_childs.ForEach(s => s.GenerateNewId(help.NextId()));
                    await MongoHelper<mo_ic_bom_child>.InsertManyAsync(moIcbom_childs);
                }

                //同步物料Bom数据
                var icBoms = _mysql_ic_bom.GetListAsync().Result;
                if (icBoms.Count > 0)
                {
                    //先清空表数据
                    await MongoHelper<mo_ic_bom>.DeleteManyAsync(p => p.mysql_id != -1);
                    var moIcBoms = ObjectMapper.Map<List<ic_bom>, List<mo_ic_bom>>(icBoms);
                    moIcBoms.ForEach(s => s.GenerateNewId(help.NextId()));
                    await MongoHelper<mo_ic_bom>.InsertManyAsync(moIcBoms);
                }

                //同步物料数据
                var icItems = _mysql_ic_item.GetListAsync().Result;
                if (icItems.Count > 0)
                {
                    //先清空表数据
                    await MongoHelper<mo_ic_item>.DeleteManyAsync(p => p.mysql_id != -1);
                    var moIcItems = ObjectMapper.Map<List<ic_item>, List<mo_ic_item>>(icItems);
                    moIcItems.ForEach(s => s.GenerateNewId(help.NextId()));
                    await MongoHelper<mo_ic_item>.InsertManyAsync(moIcItems);
                }

                //同步替代群组数据
                var subtitutes = _mysql_ic_substitute.GetListAsync().Result;
                if (subtitutes.Count > 0)
                {
                    //先清空表数据
                    await MongoHelper<mo_ic_substitute>.DeleteManyAsync(p => p.mysql_id != -1);
                    var moSubtitutes = ObjectMapper.Map<List<ic_substitute>, List<mo_ic_substitute>>(subtitutes);
                    moSubtitutes.ForEach(s => s.GenerateNewId(help.NextId()));
                    await MongoHelper<mo_ic_substitute>.InsertManyAsync(moSubtitutes);
                }

                //同步物料替代多群组数据
                var subAlls = _mysql_ic_substitute_group.GetListAsync().Result;
                if (subAlls.Count > 0)
                {
                    //先清空表数据
                    await MongoHelper<mo_ic_substitute_group>.DeleteManyAsync(p => p.mysql_id != -1);
                    var moSubAlls = ObjectMapper.Map<List<ic_substitute_group>, List<mo_ic_substitute_group>>(subAlls);
                    moSubAlls.ForEach(s => s.GenerateNewId(help.NextId()));
                    await MongoHelper<mo_ic_substitute_group>.InsertManyAsync(moSubAlls);
                }

                //同步物料替代多群组明细数据
                var subAllDtls = _mysql_ic_substitute_group_detail.GetListAsync().Result;
                if (subAllDtls.Count > 0)
                {
                    //先清空表数据
                    await MongoHelper<mo_ic_substitute_group_detail>.DeleteManyAsync(p => p.mysql_id != -1);
                    var moSubAllDtls = ObjectMapper.Map<List<ic_substitute_group_detail>, List<mo_ic_substitute_group_detail>>(subAllDtls);
                    moSubAllDtls.ForEach(s => s.GenerateNewId(help.NextId()));
                    await MongoHelper<mo_ic_substitute_group_detail>.InsertManyAsync(moSubAllDtls);
                }

                //同步供应商数据
                var suppliers = _mysql_srm_supplier.GetListAsync().Result;
                if (suppliers.Count > 0)
                {
                    //先清空表数据
                    await MongoHelper<mo_srm_supplier>.DeleteManyAsync(p => p.mysql_id != -1);
                    var moSuppliers = ObjectMapper.Map<List<srm_supplier>, List<mo_srm_supplier>>(suppliers);
                    moSuppliers.ForEach(s => s.GenerateNewId(help.NextId()));
                    await MongoHelper<mo_srm_supplier>.InsertManyAsync(moSuppliers);
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            return "任务执行成功";
        }

        //同步客户
        public void SyncCustMaster()
        {
            var customerList = _mysql_crm_customer.GetListAsync().Result;
            List<CustMaster> custList = _custMaster.Select(a => a.IsActive);
            var addressList = _consigneeAddressMaster.Select(a => a.Typed == "Cust" && a.IsActive);
            List<crm_customer> ItemsAdd = new List<crm_customer>();
            if (custList != null && custList.Count > 0)
            {
                for (int i = 0; i < custList.Count; i++)
                {
                    long domain = Convert.ToInt64(custList[i].Domain);
                    long factory_id = domain;
                    long tenant_id = 1000;
                    var cust = customerList.Find(x => x.customer_no == custList[i].Cust);
                    if (cust is null)
                    {
                        ConsigneeAddressMaster address = addressList.Find(a => a.Address == custList[i].Cust);
                        var customer = new crm_customer
                        {
                            customer_no = custList[i].Cust,
                            customer_name = address is not null ? address.Name : "",
                            telephone = address is not null ? address.Telephone : "",
                            email = address is not null ? address.AttentionEmail : "",
                            contact = address is not null ? address.Attention1 : "",
                            post_code = address is not null ? address.Post : "",
                            country = address is not null ? address.Country : "",
                            province = address is not null ? address.State : "",
                            city = address is not null ? address.City : "",
                            region = address is not null ? address.State : "",
                            address = address is not null ? address.Address1 : "",
                            short_name = custList[i].SortName,
                            carrying_aos = custList[i].CustShippingLT.GetValueOrDefault(),
                            currency = custList[i].Curr,
                            employee_name = custList[i].Salesperson1,
                            factory_id = factory_id,
                            tenant_id=tenant_id,
                            company_id = tenant_id
                        };
                        customer.GenerateNewId(help.NextId());
                        ItemsAdd.Add(customer);
                    }
                }
                _businessDbContext.BulkInsert(ItemsAdd);
            }
        }

        //同步供应商
        public void SyncSuppMaster()
        {
            var suppList = _mysql_srm_supplier.GetListAsync().Result;
            List<SuppMaster> custList = _suppMaster.Select(a => a.IsActive);
            var addressList = _consigneeAddressMaster.Select(a => a.Typed == "Supp" && a.IsActive);
            List<srm_supplier> ItemsAdd = new List<srm_supplier>();
            if (custList != null && custList.Count > 0)
            {
                for (int i = 0; i < custList.Count; i++)
                {
                    long domain = Convert.ToInt64(custList[i].Domain.ToString());
                    long factory_id = domain;
                    long tenant_id = 1000;
                    var supp = suppList.Find(x => x.supplier_no == custList[i].Supp.ToString() && x.company_id == tenant_id && x.factory_id == factory_id);
                    if (supp is null)
                    {
                        ConsigneeAddressMaster address = addressList.Find(a => a.Address == custList[i].Supp);
                        var supplier = new srm_supplier
                        {
                            supplier_no = custList[i].Supp,
                            supplier_name = address is not null ? address.Name : "",
                            supplier_short_name = custList[i].SortName,
                            country = address is not null ? address.Country : "",
                            state = address is not null ? address.State : "",
                            region = address is not null ? address.City : "",
                            contact = address is not null ? address.Attention1 : "",
                            telephone = address is not null ? address.Telephone : "",
                            post_code = address is not null ? address.Post : "",
                            email = address is not null ? address.AttentionEmail : "",
                            currency = custList[i].Curr,
                            supply_type = custList[i].Type,
                            settlement = custList[i].CrTerms,
                            create_time = DateTime.Now,
                            factory_id = factory_id,
                            tenant_id = tenant_id,
                            company_id = tenant_id
                        };
                        supplier.GenerateNewId(help.NextId());
                        ItemsAdd.Add(supplier);
                    }
                }
                _businessDbContext.BulkInsert(ItemsAdd);
            }
        }
        //////同步销售订单
        ////public void SyncSalesOrdMaster()
        ////{
        ////    var customerList = _mysql_crm_customer.GetListAsync().Result;
        ////    var saleOrderList = _mysql_crm_seorder.GetListAsync().Result;
        ////    var ItemMasterDS = _repository.SelectDataBaseBySql("SELECT A.SalesOrd,C.SortName AS CustomName,A.SoldTo AS Cust,A.OrdDate,A.RequiredDate,A.Sequence,A.Curr,A.ExchRate,A.Salesperson1,A.Project,B.Descr AS ProjectName,A.[Domain] from SalesOrdMaster A INNER JOIN CustMaster C ON A.SoldTo=C.Cust and A.[Domain]=C.[Domain]\r\nLEFT JOIN ProjectMaster B On A.Project=B.Project AND A.[Domain]=B.[Domain] ", "SalesOrdMaster");
        ////    List<crm_seorder> ItemsAdd = new List<crm_seorder>();
        ////    List<crm_seorder> ItemsUpdate = new List<crm_seorder>();
        ////    if (ItemMasterDS != null && ItemMasterDS.Tables.Count > 0 && ItemMasterDS.Tables[0].Rows.Count > 0)
        ////    {
        ////        for (int i = 0; i < ItemMasterDS.Tables[0].Rows.Count; i++)
        ////        {
        ////            long domain = Convert.ToInt64(ItemMasterDS.Tables[0].Rows[i]["Domain"].ToString());
        ////            long factory_id = domain % 1000;
        ////            long tenant_id = domain - factory_id;
        ////            var custno = ItemMasterDS.Tables[0].Rows[i]["Cust"].ToString();
        ////            var cust = customerList.Find(a => a.customer_no == custno && a.factory_id == factory_id && a.tenant_id == tenant_id);
        ////            long? custom_id = cust is null ? cust.Id : null;
        ////            var saleorder = saleOrderList.Find(x => x.bill_no == ItemMasterDS.Tables[0].Rows[i]["SalesOrd"].ToString() && x.tenant_id == factory_id && x.factory_id == tenant_id);
        ////            if (saleorder is null)
        ////            {
        ////                var order = new crm_seorder
        ////                {
        ////                    bill_no = ItemMasterDS.Tables[0].Rows[i]["SalesOrd"].ToString(),
        ////                    custom_id = custom_id,
        ////                    custom_name = ItemMasterDS.Tables[0].Rows[i]["CustomName"].ToString(),
        ////                    custom_no = ItemMasterDS.Tables[0].Rows[i]["Cust"].ToString(),
        ////                    //custom_level = ItemMasterDS.Tables[0].Rows[i]["PurMfg"].ToString() == "P" ? "外购" : "自制",
        ////                    date = (DateTime?)ItemMasterDS.Tables[0].Rows[i]["OrdDate"],
        ////                    rdate = (DateTime?)ItemMasterDS.Tables[0].Rows[i]["RequiredDate"],
        ////                    //urgent = "",
        ////                    currency = ItemMasterDS.Tables[0].Rows[i]["Curr"].ToString() == "RMB" ? 1 : 0,
        ////                    exchange_rate = (decimal?)ItemMasterDS.Tables[0].Rows[i]["ExchRate"],
        ////                    //biller = ItemMasterDS.Tables[0].Rows[i]["NetWeightUM"].ToString(),
        ////                    //emp_no = (decimal)ItemMasterDS.Tables[0].Rows[i]["Length"],
        ////                    emp_name = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString(),
        ////                    //auditor = (decimal)ItemMasterDS.Tables[0].Rows[i]["Height"],
        ////                    //audit_date = ItemMasterDS.Tables[0].Rows[i]["PurMfg"].ToString() == "P" ? 1 : 0,
        ////                    //status = 1,
        ////                    //closed = "",
        ////                    //op_time = 1,
        ////                    bill_from = "",
        ////                    //project_name = Convert.ToBoolean(ItemMasterDS.Tables[0].Rows[i]["LotSerialControl"]) == true ? 1 : 0,
        ////                    //project_code = "",
        ////                    out_stock_type = 1,//TODO
        ////                    //sale_dept_id = "",
        ////                    //sale_dept_name = 1,
        ////                    //sale_dept_code = 1,
        ////                    //create_dept = 1
        ////                    factory_id = factory_id,
        ////                    tenant_id = tenant_id
        ////                };
        ////                order.GenerateNewId();
        ////                ItemsAdd.Add(order);
        ////            }
        ////            else
        ////            {
        ////                saleorder.bill_no = ItemMasterDS.Tables[0].Rows[i]["SalesOrd"].ToString();
        ////                saleorder.custom_id = custom_id;
        ////                saleorder.custom_name = ItemMasterDS.Tables[0].Rows[i]["CustomName"].ToString();
        ////                saleorder.custom_no = ItemMasterDS.Tables[0].Rows[i]["Cust"].ToString();
        ////                //custom_level = ItemMasterDS.Tables[0].Rows[i]["PurMfg"].ToString() == "P" ? "外购" : "自制",
        ////                saleorder.date = (DateTime?)ItemMasterDS.Tables[0].Rows[i]["OrdDate"];
        ////                saleorder.rdate = (DateTime?)ItemMasterDS.Tables[0].Rows[i]["RequiredDate"];
        ////                saleorder.urgent = (int?)ItemMasterDS.Tables[0].Rows[i]["Sequence"];
        ////                saleorder.currency = ItemMasterDS.Tables[0].Rows[i]["Curr"].ToString() == "RMB" ? 1 : 0;
        ////                saleorder.exchange_rate = (decimal?)ItemMasterDS.Tables[0].Rows[i]["ExchRate"];
        ////                //biller = ItemMasterDS.Tables[0].Rows[i]["NetWeightUM"].ToString(),
        ////                //emp_no = (decimal)ItemMasterDS.Tables[0].Rows[i]["Length"],
        ////                saleorder.emp_name = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString();
        ////                //auditor = (decimal)ItemMasterDS.Tables[0].Rows[i]["Height"],
        ////                //audit_date = ItemMasterDS.Tables[0].Rows[i]["PurMfg"].ToString() == "P" ? 1 : 0,
        ////                //status = 1,
        ////                //closed = "",
        ////                //op_time = 1,
        ////                saleorder.bill_from = "";
        ////                saleorder.project_name = ItemMasterDS.Tables[0].Rows[i]["ProjectName"].ToString();
        ////                saleorder.project_code = ItemMasterDS.Tables[0].Rows[i]["Project"].ToString();
        ////                saleorder.out_stock_type = 1;//TODO
        ////                //sale_dept_id = "",
        ////                //sale_dept_name = 1,
        ////                //sale_dept_code = 1,
        ////                //create_dept = 1
        ////                ItemsUpdate.Add(saleorder);
        ////            }
        ////        }
        ////        //_businessDbContext.crm_seorder.Delete();
        ////        _businessDbContext.crm_seorder.BulkInsert(ItemsAdd);
        ////        _businessDbContext.crm_seorder.BulkUpdate(ItemsUpdate);
        ////    }
        ////}

        //////同步销售订单明细
        ////public void SyncSalesOrdMasterEntry()
        ////{
        ////    var saleOrderEntryList = _mysql_crm_seorderentry.GetListAsync().Result;
        ////    var saleOrderList = _mysql_crm_seorder.GetListAsync().Result;
        ////    var ItemMasterDS = _repository.SelectDataBaseBySql("SELECT A.SalesOrd,A.Line,C.Descr,C.Descr1,A.ItemNum,A.UM,A.QtyOrded,A.Price,A.Disc,A.RequiredDate,A.Remark,B.CustPO,A.CustItem,A.Status,A.QtyShipped,A.QtyReturned,B.Contract,A.Domain from SalesOrdDetail A INNER JOIN SalesOrdMaster B On A.SalesOrd=B.SalesOrd and A.[Domain]=B.[Domain] INNER JOIN ItemMaster C on A.ItemNum=C.ItemNum and A.[Domain]=C.[Domain]", "SalesOrdDetail");
        ////    List<crm_seorderentry> ItemsAdd = new List<crm_seorderentry>();
        ////    List<crm_seorderentry> ItemsUpdate = new List<crm_seorderentry>();
        ////    if (ItemMasterDS != null && ItemMasterDS.Tables.Count > 0 && ItemMasterDS.Tables[0].Rows.Count > 0)
        ////    {
        ////        for (int i = 0; i < ItemMasterDS.Tables[0].Rows.Count; i++)
        ////        {
        ////            long domain = Convert.ToInt64(ItemMasterDS.Tables[0].Rows[i]["Domain"].ToString());
        ////            long factory_id = domain % 1000;
        ////            long tenant_id = domain - factory_id;
        ////            var bill_no = ItemMasterDS.Tables[0].Rows[i]["SalesOrd"].ToString();
        ////            var seorder = saleOrderList.Find(a => a.bill_no == bill_no && a.factory_id == factory_id && a.tenant_id == tenant_id);
        ////            long? seorder_id = seorder is null ? seorder.Id : null;
        ////            var saleorderentry = saleOrderEntryList.Find(x => x.bill_no == bill_no && x.tenant_id == factory_id && x.factory_id == tenant_id);
        ////            if (saleorderentry is null)
        ////            {
        ////                var entry = new crm_seorderentry
        ////                {
        ////                    seorder_id = seorder_id,
        ////                    bill_no = ItemMasterDS.Tables[0].Rows[i]["SalesOrd"].ToString(),
        ////                    entry_seq = (int?)ItemMasterDS.Tables[0].Rows[i]["Line"],
        ////                    item_number = ItemMasterDS.Tables[0].Rows[i]["ItemNum"].ToString(),
        ////                    item_name = ItemMasterDS.Tables[0].Rows[i]["Descr"].ToString(),
        ////                    specification = ItemMasterDS.Tables[0].Rows[i]["Descr1"].ToString(),
        ////                    //urgent = (int?)ItemMasterDS.Tables[0].Rows[i]["PLPriority"],
        ////                    bom_number = ItemMasterDS.Tables[0].Rows[i]["ItemNum"].ToString(),
        ////                    unit = ItemMasterDS.Tables[0].Rows[i]["UM"].ToString(),
        ////                    qty = (decimal?)ItemMasterDS.Tables[0].Rows[i]["QtyOrded"],
        ////                    //price = (decimal?)ItemMasterDS.Tables[0].Rows[i]["Price"],
        ////                    //tax_price = (decimal?)ItemMasterDS.Tables[0].Rows[i]["ExchRate"],
        ////                    //tax_rate = (decimal?)ItemMasterDS.Tables[0].Rows[i]["ExchRate"],
        ////                    amount = (decimal?)ItemMasterDS.Tables[0].Rows[i]["Price"],
        ////                    //discount_rate = (decimal?)ItemMasterDS.Tables[0].Rows[i]["ExchRate"],
        ////                    discount_amount = (decimal?)ItemMasterDS.Tables[0].Rows[i]["Disc"],
        ////                    //total_amount = (decimal?)ItemMasterDS.Tables[0].Rows[i]["ExchRate"],
        ////                    plan_date = (DateTime?)ItemMasterDS.Tables[0].Rows[i]["RequiredDate"],
        ////                    //date = (DateTime?)ItemMasterDS.Tables[0].Rows[i]["OrdDate"],
        ////                    //planner_name = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString(),
        ////                    //planner_no = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString(),
        ////                    remark = ItemMasterDS.Tables[0].Rows[i]["Remark"].ToString(),
        ////                    //soure_bill_no = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString(),
        ////                    custom_order_bill_no = ItemMasterDS.Tables[0].Rows[i]["CustPO"].ToString(),
        ////                    //custom_order_entryid = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString(),
        ////                    //sys_capacity_date = (DateTime?)ItemMasterDS.Tables[0].Rows[i]["OrdDate"],
        ////                    //adjust_date = (DateTime?)ItemMasterDS.Tables[0].Rows[i]["OrdDate"],
        ////                    //mrp_closed = Convert.ToBoolean(ItemMasterDS.Tables[0].Rows[i]["OrdDate"]),
        ////                    custom_order_itemno = ItemMasterDS.Tables[0].Rows[i]["CustItem"].ToString(),
        ////                    state = Convert.ToBoolean(ItemMasterDS.Tables[0].Rows[i]["Status"]),//TODO
        ////                    deliver_count = (decimal?)ItemMasterDS.Tables[0].Rows[i]["QtyShipped"],
        ////                    returned_count = (decimal?)ItemMasterDS.Tables[0].Rows[i]["QtyReturned"],
        ////                    //se_reject_reason = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString(),
        ////                    //out_stock_type = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString(),
        ////                    //is_checked = Convert.ToBoolean(ItemMasterDS.Tables[0].Rows[i]["OrdDate"]),
        ////                    //sys_material_date = Convert.ToBoolean(ItemMasterDS.Tables[0].Rows[i]["OrdDate"]),
        ////                    contract_no = ItemMasterDS.Tables[0].Rows[i]["Contract"].ToString(),
        ////                    //create_dept = Convert.ToBoolean(ItemMasterDS.Tables[0].Rows[i]["OrdDate"])
        ////                    factory_id = factory_id,
        ////                    tenant_id = tenant_id
        ////                };
        ////                entry.GenerateNewId();
        ////                ItemsAdd.Add(entry);
        ////            }
        ////            else
        ////            {
        ////                saleorderentry.seorder_id = seorder_id;
        ////                saleorderentry.bill_no = ItemMasterDS.Tables[0].Rows[i]["SalesOrd"].ToString();
        ////                saleorderentry.entry_seq = (int?)ItemMasterDS.Tables[0].Rows[i]["Line"];
        ////                saleorderentry.item_number = ItemMasterDS.Tables[0].Rows[i]["ItemNum"].ToString();
        ////                saleorderentry.item_name = ItemMasterDS.Tables[0].Rows[i]["Descr"].ToString();
        ////                saleorderentry.specification = ItemMasterDS.Tables[0].Rows[i]["Descr1"].ToString();
        ////                //urgent = (int?)ItemMasterDS.Tables[0].Rows[i]["PLPriority"],
        ////                saleorderentry.bom_number = ItemMasterDS.Tables[0].Rows[i]["ItemNum"].ToString();
        ////                saleorderentry.unit = ItemMasterDS.Tables[0].Rows[i]["UM"].ToString();
        ////                saleorderentry.qty = (decimal?)ItemMasterDS.Tables[0].Rows[i]["QtyOrded"];
        ////                //price = (decimal?)ItemMasterDS.Tables[0].Rows[i]["Price"],
        ////                //tax_price = (decimal?)ItemMasterDS.Tables[0].Rows[i]["ExchRate"],
        ////                //tax_rate = (decimal?)ItemMasterDS.Tables[0].Rows[i]["ExchRate"],
        ////                saleorderentry.amount = (decimal?)ItemMasterDS.Tables[0].Rows[i]["Price"];
        ////                //discount_rate = (decimal?)ItemMasterDS.Tables[0].Rows[i]["ExchRate"],
        ////                saleorderentry.discount_amount = (decimal?)ItemMasterDS.Tables[0].Rows[i]["Disc"];
        ////                //total_amount = (decimal?)ItemMasterDS.Tables[0].Rows[i]["ExchRate"],
        ////                saleorderentry.plan_date = (DateTime?)ItemMasterDS.Tables[0].Rows[i]["RequiredDate"];
        ////                //date = (DateTime?)ItemMasterDS.Tables[0].Rows[i]["OrdDate"],
        ////                //planner_name = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString(),
        ////                //planner_no = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString(),
        ////                saleorderentry.remark = ItemMasterDS.Tables[0].Rows[i]["Remark"].ToString();
        ////                //soure_bill_no = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString(),
        ////                saleorderentry.custom_order_bill_no = ItemMasterDS.Tables[0].Rows[i]["CustPO"].ToString();
        ////                //custom_order_entryid = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString(),
        ////                //sys_capacity_date = (DateTime?)ItemMasterDS.Tables[0].Rows[i]["OrdDate"],
        ////                //adjust_date = (DateTime?)ItemMasterDS.Tables[0].Rows[i]["OrdDate"],
        ////                //mrp_closed = Convert.ToBoolean(ItemMasterDS.Tables[0].Rows[i]["OrdDate"]),
        ////                saleorderentry.custom_order_itemno = ItemMasterDS.Tables[0].Rows[i]["CustItem"].ToString();
        ////                saleorderentry.state = Convert.ToBoolean(ItemMasterDS.Tables[0].Rows[i]["Status"]);//TODO
        ////                //rnumber = Convert.ToBoolean(ItemMasterDS.Tables[0].Rows[i]["OrdDate"]),
        ////                //deliver_notice_count = (decimal?)ItemMasterDS.Tables[0].Rows[i]["ExchRate"],
        ////                saleorderentry.deliver_count = (decimal?)ItemMasterDS.Tables[0].Rows[i]["QtyShipped"];
        ////                saleorderentry.returned_count = (decimal?)ItemMasterDS.Tables[0].Rows[i]["QtyReturned"];
        ////                //se_reject_reason = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString(),
        ////                //out_stock_type = ItemMasterDS.Tables[0].Rows[i]["Salesperson1"].ToString(),
        ////                //is_checked = Convert.ToBoolean(ItemMasterDS.Tables[0].Rows[i]["OrdDate"]),
        ////                //sys_material_date = Convert.ToBoolean(ItemMasterDS.Tables[0].Rows[i]["OrdDate"]),
        ////                saleorderentry.contract_no = ItemMasterDS.Tables[0].Rows[i]["Contract"].ToString();
        ////                //create_dept = Convert.ToBoolean(ItemMasterDS.Tables[0].Rows[i]["OrdDate"])
        ////                ItemsUpdate.Add(saleorderentry);
        ////            }
        ////        }
        ////        //_businessDbContext.crm_seorder.Delete();
        ////        _businessDbContext.crm_seorderentry.BulkInsert(ItemsAdd);
        ////        _businessDbContext.crm_seorderentry.BulkUpdate(ItemsUpdate);

        ////    }
        ////}

        //同步物料
        public void SyncItemMaster()
        {
            //bool isAll = true;//是否同步所有物料
            var ic_itemList = _mysql_ic_item.GetListAsync().Result;
            List<ItemMaster> custList = new List<ItemMaster>();
            custList = _itemMaster.Select(a => a.IsActive);
            List<ic_item> ItemsAdd = new List<ic_item>();
            List<ic_item_stock> stockAdd = new List<ic_item_stock>();
            List<ic_item> ItemsUpd = new List<ic_item>();
            List<srm_purchase> srm_purchaseAdd = new List<srm_purchase>();

            var icitemStokc = _mysql_ic_item_stock.GetListAsync().Result;

            if (custList != null && custList.Count > 0)
            {
                for (int i = 0; i < custList.Count; i++)
                {
                    var ic_item = ic_itemList.Find(x => x.number == custList[i].ItemNum);
                    int erpcls = 0;
                    string erpclsText = "配置类";
                    long itemId = help.NextId();
                    if (custList[i].PurMfg == "L")
                    {
                        if (custList[i].EMTType == "50")
                        {
                            erpcls = 4;
                            erpclsText = "虚拟";
                        }
                        else
                        {
                            erpcls = 1;
                            erpclsText = "自制";
                        }
                        
                    }
                    else
                    {
                        if (custList[i].EMTType == "30")
                        {
                            erpcls = 2;
                            erpclsText = "委外";
                        }
                        else
                        {
                            erpcls = 3;
                            erpclsText = "外购";
                        }
                    }
                    var addStock = icitemStokc.Find(x => x.icitem_number == custList[i].ItemNum);
                    if (ic_item == null)
                    {
                        ItemsAdd.Add(new ic_item(itemId)
                        {
                            number = custList[i].ItemNum, //物料编码
                            name = custList[i].Descr, //物料名称
                            model = custList[i].Descr1,//规格型号
                            fversion = custList[i].Rev,//版本号
                            erp_cls = erpcls,//物料属性
                            erp_cls_name = erpclsText,//物料属性名称
                            unit = custList[i].UM,//单位
                            item_level = 0,//物料等级
                            source = "",//来源
                            iskeyitem = custList[i].IsMainMas ? 1 : 0,//是否关键件
                            net_weight = (decimal)custList[i].NetWeight,//净重
                            maund = custList[i].NetWeightUM,//重量单位
                            length = (decimal)custList[i].Length,//长度
                            width = (decimal)custList[i].Width,//宽度
                            height = (decimal)custList[i].Height,//高度
                            allowpur = custList[i].PurMfg == "P" ? 1 : 0,//允许采购
                            allowsale = 1,//允许销售
                            allowmanu = custList[i].PurMfg == "L" ? 1 : 0,//允许生产
                            allowout = 1,//允许委外
                            allowbatch = custList[i].LotSerialControl ? 1 : 0,//批号管理
                            allowserial = custList[i].LotSerialControl ? 1 : 0,//序列号管理
                            photo = "",//图片
                            enable_warning = 1,//启用预警
                            factory_id = long.Parse(custList[i].Domain),
                            company_id = 1000,
                            tenant_id = 1000,
                            IsDeleted = false,
                            clean_leadtime = custList[i].MFGMTTR,
                            is_clean = custList[i].IssuePolicy ? 1 : 0,
                            batch_manager = custList[i].LotSerialControl ? 1 : 0,
                            minorderqty = custList[i].MinOrd,
                            minpackqty = 1,
                            ordissu_days = 1,
                            transportation_leadtime = 3,
                            stock_leadtime = 1,
                            production_leadtime = 4,
                            order_leadtime = 4,
                            fix_leadtime = 1,
                            order_inter_val = 1,
                            lead_time = 1,
                            bat_change_economy = 1,
                            total_tqq = 1,
                            order_point = 1,
                            secinv = custList[i].SafetyStk,
                            item_type= custList[i].ItemType,
                            secinv_ratio = 1,
                            self_inspection_date = custList[i].InsLT,
                            Warehousing_date = 1,
                            Shipping_date = 3,
                            PurLT = custList[i].PurLT
                        });
                    }
                    else
                    {
                        ic_item.name = custList[i].Descr;
                        ic_item.model = custList[i].Descr1;//规格型号
                        ic_item.fversion = custList[i].Rev;//版本号
                        ic_item.erp_cls = erpcls;//物料属性
                        ic_item.erp_cls_name = erpclsText;//物料属性名称
                        ic_item.unit = custList[i].UM;//单位
                        ic_item.is_clean = custList[i].IssuePolicy ? 1 : 0;
                        ic_item.clean_leadtime = custList[i].MFGMTTR;
                        ic_item.PurLT = custList[i].PurLT;
                        ItemsUpd.Add(ic_item);
                    }
                    if (addStock == null)
                    {
                        addStock = new ic_item_stock();
                        addStock.GenerateNewId(help.NextId());
                        addStock.icitem_id = ic_item == null ? itemId : ic_item.Id;
                        addStock.icitem_number = custList[i].ItemNum; //物料编码
                        addStock.icitem_name = custList[i].Descr; //物料名称
                        addStock.sqty = 0;
                        addStock.quantity_in_transit = 0;
                        addStock.fversion = "";
                        addStock.factory_id = long.Parse(custList[i].Domain);
                        addStock.company_id = 1000;
                        addStock.tenant_id = 1000;
                        addStock.create_time = DateTime.Now;
                        addStock.update_time = DateTime.Now;
                        addStock.create_by_name = "admin";
                        addStock.update_by_name = "admin";
                        stockAdd.Add(addStock);
                    }
                }
            }
            _businessDbContext.BulkInsert(ItemsAdd);
            _businessDbContext.BulkInsert(stockAdd);
            _businessDbContext.ic_item.UpdateRange(ItemsUpd);
        }

        public void SyncBom()
        {
            var ic_bomList = _mysql_ic_bom.GetListAsync().Result;
            var ic_itemList = _mysql_ic_item.GetListAsync().Result;
            var ic_bomchildList = _mysql_ic_bom_child.GetListAsync().Result;
            List<ProductStructureMaster> wmsBomList = _productStructureMaster.Select(a => a.IsActive);
            List<ItemMaster> custList = new List<ItemMaster>();
            custList = _itemMaster.Select(a => a.IsActive);
            List<ic_bom> ItemsAdd = new List<ic_bom>();
            List<ic_bom_child> childItemsAdd = new List<ic_bom_child>();
            List<ic_bom_child> childItemsUpd = new List<ic_bom_child>();
            List<ic_bom_child> childItemsDel = new List<ic_bom_child>();
            if (wmsBomList != null && wmsBomList.Count > 0)
            {
                var ItemMasterDS = wmsBomList.Select(a => a.ParentItem).Distinct().ToList();
                foreach (var c in ItemMasterDS)
                {
                    var icbom = ic_bomList.Find(x => x.item_number == c);
                    var ItemList = wmsBomList.Where(a => a.ParentItem == c);
                    var item = ic_itemList.Find(a => a.number == c);
                    if (item == null)
                    {
                        continue;
                    }
                    if (icbom == null)
                    {
                        long domain = Convert.ToInt64(ItemList.First().Domain);
                        long factory_id = domain % 8010;
                        //long tenant_id = domain - factory_id;
                        var bom = new ic_bom(help.NextId())
                        {
                            bom_number = c,
                            icitem_id = item.Id,
                            item_name = item.name,
                            item_number = c,
                            version = ItemList.First().Refs,
                            factory_id = item.factory_id,
                            company_id = item.company_id,
                            tenant_id = item.company_id,
                            IsDeleted = false,
                            use_status = 1
                        };
                        ItemsAdd.Add(bom);
                        foreach (var child in ItemList)
                        {
                            var itemchild = ic_itemList.Find(a => a.number == child.ComponentItem);
                            if (itemchild == null) { continue; }
                            var itemMaster = custList.Find(a => a.ItemNum == child.ComponentItem);
                            if (itemMaster == null || itemMaster.MRPRequired==0) { continue; }
                            decimal scrap = child.QtyExchd + child.Scrap;
                            /*//如果当前明细是BOM，则使用组件报废率
                            if (wmsBomList.Where(c => c.ParentItem == child.ComponentItem).Any())
                            {
                                scrap = child.Scrap;
                            }*/
                            var bomchild = new ic_bom_child(help.NextId())
                            {
                                bom_id = bom.Id,
                                bom_number = bom.bom_number,
                                icitem_id = itemchild == null ? long.MinValue : itemchild.Id,
                                item_number = child.ComponentItem,
                                item_name = itemchild == null ? "" : itemchild.name,
                                unit = child.UM,
                                qty = child.Qty,
                                entryid = child.SequenceNum,
                                erp_cls = itemchild == null ? 2 : itemchild.erp_cls,
                                begin_day = child.StartEff,
                                end_day = child.EndEff,
                                child_num = child.SequenceNum,
                                version = child.Refs,
                                factory_id = item.factory_id,
                                company_id = item.company_id,
                                tenant_id = item.company_id,
                                IsDeleted = false,
                                Op = child.Op,
                                use_status = 1,
                                scrap = scrap,
                                wastage = child.QtyConsumed
                            };
                            childItemsAdd.Add(bomchild);
                        }
                    }
                    else
                    {
                        var delchildList = ic_bomchildList.Where(s => s.bom_number == c && !ItemList.Select(v => v.ComponentItem).Contains(s.item_number)).ToList();
                        childItemsDel.AddRange(delchildList);
                        foreach (var child in ItemList)
                        {
                            var itemchild = ic_itemList.Find(a => a.number == child.ComponentItem);
                            var itemMaster = custList.Find(a => a.ItemNum == child.ComponentItem);

                            bool flag = itemchild == null || itemMaster == null || itemMaster.MRPRequired == 0;
                            var icbomchild = ic_bomchildList.Find(s => s.bom_number == c && s.item_number == child.ComponentItem);
                            decimal scrap = child.QtyExchd + child.Scrap;
                            /*//如果当前明细是BOM，则使用组件报废率
                            if (wmsBomList.Where(c => c.ParentItem == child.ComponentItem).Any())
                            {
                                scrap = child.Scrap;
                            }*/
                            if (icbomchild == null)
                            {
                                if (flag) { continue; }
                                var bomchild = new ic_bom_child(help.NextId())
                                {
                                    bom_id = icbom.Id,
                                    bom_number = icbom.bom_number,
                                    icitem_id = itemchild == null ? long.MinValue : itemchild.Id,
                                    item_number = child.ComponentItem,
                                    item_name = itemchild == null ? "" : itemchild.name,
                                    unit = child.UM,
                                    qty = child.Qty,
                                    entryid = child.SequenceNum,
                                    erp_cls = itemchild == null ? 2 : itemchild.erp_cls,
                                    begin_day = child.StartEff,
                                    end_day = child.EndEff,
                                    child_num = child.SequenceNum,
                                    version = child.Refs,
                                    factory_id = item.factory_id,
                                    company_id = item.company_id,
                                    tenant_id = item.company_id,
                                    IsDeleted = false,
                                    Op = child.Op,
                                    use_status = 1,
                                    scrap = scrap,
                                    wastage = child.QtyConsumed
                                };
                                childItemsAdd.Add(bomchild);
                            }
                            else
                            {
                                if (flag) { childItemsDel.Add(icbomchild); }
                                icbomchild.qty = child.Qty;
                                icbomchild.erp_cls = itemchild == null ? 2 : itemchild.erp_cls;
                                icbomchild.begin_day = child.StartEff;
                                icbomchild.end_day = child.EndEff;
                                icbomchild.version = child.Refs;
                                icbomchild.factory_id = item.factory_id;
                                icbomchild.company_id = item.company_id;
                                icbomchild.tenant_id = item.company_id;
                                icbomchild.Op = child.Op;
                                //icbomchild.scrap = child.Scrap;
                                icbomchild.scrap = scrap;
                                
                                icbomchild.wastage = child.QtyConsumed;
                                childItemsUpd.Add(icbomchild);
                            }
                        }
                    }
                }
                _businessDbContext.BulkInsert(ItemsAdd);
                _businessDbContext.BulkInsert(childItemsAdd);
                _businessDbContext.ic_bom_child.UpdateRange(childItemsUpd);
                _mysql_ic_bom_child.HardDeleteAsync(childItemsDel);
            }
        }

        public string DoProductScheduleJob()
        {
            try
            {
                _productionScheduleAppService.DoExt();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            return "任务执行成功";

        }

        public string TestTimeOut()
        {
            try
            {
                Thread.Sleep(100);
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            return "OK";
        }
        /// <summary>
        /// 同步工单和采购订单数据
        /// </summary>
        /// <returns></returns>
        public string SyncOrderAndPurOrd()
        {
            try
            {
                IConfiguration configuration = new ConfigurationBuilder().AddJsonFile("appsettings.json", optional: true, reloadOnChange: true).Build();
                string domain = configuration.GetConnectionString("Factory_id");
                string companyid = configuration.GetConnectionString("Company_id");
                if (string.IsNullOrEmpty(domain) || string.IsNullOrEmpty(companyid))
                {
                    return "请检查配置文件中公司和工厂编码是否维护。";
                }
                var sysResult =  SyncOrder(domain).Result;
                if (sysResult == "ok")
                {
                    sysResult = SyncPurOrd(domain, companyid).Result;
                }
                return sysResult;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        private async Task<string> SyncOrder(string domain)
        {
            try
            {
                DateTime time = DateTime.Now.AddMonths(-2).Date;
                //取数范围 需明确数据范围
                var workList = _workOrdMaster.Select(c => c.CreateTime >= time && c.Domain == domain && string.IsNullOrWhiteSpace(c.Status) == false).ToList();
                var mesMorder = _mysql_mes_morder.GetListAsync(x => x.factory_id.ToString() == domain && x.create_time >= time).Result;
                var mesMoentry = _mysql_mes_moentry.GetListAsync(x => x.factory_id.ToString() == domain && x.create_time >= time).Result;
                List<mes_morder> updateList = new List<mes_morder>();
                List<mes_moentry> updateMoentryList = new List<mes_moentry>();
                foreach (var work in workList)
                {
                    var morder = mesMorder.Find(x => x.morder_no == work.WorkOrd);
                    if (morder != null)
                    {
                        //修改
                        morder.moentry_sys_stime = work.OrdDate;
                        morder.moentry_sys_etime = work.DueDate;
                        morder.need_number = work.QtyOrded;
                        morder.morder_production_number = work.QtyOrded;
                        morder.morder_need_time = work.LbrVar;
                        string status = "";
                        switch (work.Status.ToUpper())
                        {
                            case "P":
                                status = MorderEnum.Initial_state;
                                break;
                            case "R":
                                status = MorderEnum.Xd_state;
                                break;
                            case "W":
                                status = MorderEnum.tc_state;
                                break;
                            case "C":
                                status = MorderEnum.Accomplish_state;
                                break;
                            case "S":
                                status = MorderEnum.Suspend_state;
                                break;
                            default:
                                status = MorderEnum.Initial_state;
                                break;
                        }
                        morder.inventory_number = work.QtyCompleted;
                        morder.morder_state = status;
                        updateList.Add(morder);
                        var moentry = mesMoentry.Find(x => x.moentry_moid == morder.Id);
                        if (moentry != null)
                        {
                            moentry.morder_production_number = morder.morder_production_number;
                            moentry.need_number = morder.need_number;
                            updateMoentryList.Add(moentry);
                        }
                    }
                }
                await _businessDbContext.BulkUpdateAsync(updateList);
                await _businessDbContext.BulkUpdateAsync(updateMoentryList);
            }
            catch (Exception e)
            {
                return e.Message;
            }
            return "ok";
        }

        private async Task<string> SyncPurOrd(string domain,string companyid)
        {
            //可以直接更新数据状态，留个记录
            _businessDbContext.Database.ExecuteSqlRaw("DELETE P FROM srm_po_main p LEFT JOIN PurOrdMaster m on p.po_billno=m.PurOrd WHERE m.PurOrd is null;" +
                "DELETE pl FROM srm_po_list pl left join PurOrdDetail pd on pl.po_billno=pd.PurOrd AND pl.ItemNum = pd.ItemNum AND pd.Line = pl.polist_row WHERE pd.ItemNum is null;" +
                "UPDATE a SET a.state=0 FROM srm_po_list a INNER JOIN PurOrdDetail b on a.ItemNum=b.ItemNum AND a.polist_row=b.Line WHERE ISNULL(b.Status, '')='C' AND a.state=1;");

            DateTime time = DateTime.Now.AddDays(-1).Date;

            var masters = _purOrdMaster.Select(x => x.Domain == domain && x.UpdateTime >= time).ToList();
            var details = _purOrdDetail.Select(x => x.Domain == domain && x.UpdateTime >= time).ToList();
            var supplierList = _mysql_srm_supplier.GetListAsync(x => x.factory_id.ToString() == domain && x.company_id.ToString() == companyid && masters.Select(c => c.Supp).Contains(x.supplier_no)).Result;

            //日期上多查询一天。
            var pomains = _srm_po_main.GetListAsync(x => x.factory_id.ToString() == domain && x.company_id.ToString() == companyid && masters.Select(c => c.PurOrd).Contains(x.po_billno)).Result;
            var polists = _srm_po_list.GetListAsync(x => x.factory_id.ToString() == domain && x.company_id.ToString() == companyid && pomains.Select(c => c.Id).Contains(x.po_id.GetValueOrDefault())).Result;
            List<ic_item> itemList = _mysql_ic_item.GetListAsync(x => x.factory_id.ToString() == domain && x.company_id.ToString() == companyid).Result;

            List<srm_po_occupy> occList = new List<srm_po_occupy>();
            List<srm_po_main> addPo = new List<srm_po_main>();
            List<srm_po_main> updatePo = new List<srm_po_main>();
            List<srm_po_list> addPolist = new List<srm_po_list>();
            List<srm_po_list> updatePolist = new List<srm_po_list>();

            try
            {
                foreach (var m in masters)
                {
                    var po = pomains.Find(x => x.po_billno == m.PurOrd);
                    if (po != null)
                    {
                        updatePo.Add(po);
                    }
                    else
                    {
                        //add PO Polist
                        po = new srm_po_main();
                        po.GenerateNewId(help.NextId());
                        po.po_billno = m.PurOrd;
                        //var pr = list.Find(s => s.Id == dto.polist[0].pr_id);
                        var itemsupplier = supplierList.Find(a => a.supplier_no == m.Supp);
                        if (itemsupplier == null)
                        {
                            continue;
                        }
                        //po.po_purchaser = m.;
                        //po.po_purchaser_no = m.Buyer;
                        po.po_ssend_date = m.OrdDate;
                        po.po_tax_rate = m.Tax1;
                        po.po_express = 1;
                        po.po_note = "";
                        po.po_order_type = 1;
                        po.po_total = 0;
                        po.supplier_type = m.USAGE;
                        po.currency = m.Curr == "CNY" ? 1 : 0;
                        po.bill_type = 1;
                        po.supplier_id = itemsupplier.Id;
                        po.supplier_name = itemsupplier.supplier_name;
                        po.supplier_no = m.Supp;
                        //po.contact = m.cus;
                        po.suppliertelephone = m.CustPhone;
                        //po.deliveryaddress = "";
                        po.supplierdddress = m.CustAddress;
                        po.confirmstate = 1;
                        po.logisticsstate = 0;
                        po.financialstate = 0;
                        po.create_time = DateTime.Now;
                        po.factory_id = long.Parse(domain);
                        po.company_id = long.Parse(companyid);
                        po.tenant_id = long.Parse(companyid);
                        po.po_delivery = 1;
                        addPo.Add(po);
                    }
                    po.state = GetPurOrdStatus(m.Status);
                    var dtls = details.Where(x => x.PurOrdRecID == m.RecID).ToList();
                    var plists = polists.Where(x => x.po_id == po.Id).ToList();
                    foreach (var dtl in dtls)
                    {
                        var plist = plists.Find(x => x.ItemNum == dtl.ItemNum && x.polist_row == dtl.Line);
                        if (plist != null)
                        {
                            plist.state = dtl.Status == null ? 1 : dtl.Status.ToUpper() == "C" ? 0 : 1;
                            updatePolist.Add(plist);
                        }
                        else
                        {
                            plist = new srm_po_list();
                            plist.po_id = po.Id;
                            plist.po_billno = po.po_billno;
                            plist.polist_row = dtl.Line;
                            plist.GenerateNewId(help.NextId());
                            plist.pr_purchase_id = po.supplier_id;
                            plist.pr_purchase_name = po.supplier_name;
                            var icitem = itemList.Find(a => a.number == dtl.ItemNum);
                            if (icitem == null)
                            {
                                continue;
                            }
                            plist.icitem_id = icitem.Id;
                            plist.model = icitem.model;
                            plist.ItemNum = icitem.number;
                            plist.icitem_name = icitem.name;
                            plist.qty = dtl.QtyOrded;
                            plist.netprice = dtl.PurCost.GetValueOrDefault();
                            plist.netmoney = dtl.QtyOrded * dtl.PurCost.GetValueOrDefault();
                            plist.rate = dtl.TaxRate;
                            plist.price = dtl.PurCost.GetValueOrDefault();
                            plist.total_price = dtl.QtyOrded * dtl.PurCost.GetValueOrDefault();
                            //plist.taxamount = item.pr_orderprice.GetValueOrDefault() - podetail.netmoney;
                            plist.plan_qty = dtl.QtyOrded;
                            plist.rqty = dtl.RctQty;
                            plist.unit = icitem.unit;
                            plist.rarrdate = dtl.DueDate;
                            plist.rnumber = 0;
                            plist.currencytype = po.currency;
                            plist.create_time = DateTime.Now;
                            plist.factory_id = po.factory_id;
                            plist.company_id = po.company_id;
                            plist.tenant_id = po.tenant_id;
                            plist.stock_id = 1;
                            plist.state = dtl.Status == null ? 1 : dtl.Status.ToUpper() == "C" ? 0 : 1;
                            plist.pr_billno = dtl.Req;
                            addPolist.Add(plist);
                        }
                    }
                }
                if (addPolist.Any())
                {
                    var srmPrs = _businessDbContext.srm_pr_main.Where(s => s.company_id.ToString() == companyid && addPolist.Select(x => x.pr_billno).Contains(s.pr_billno)).ToList();
                    var occupylist = _mysql_srm_po_occupy.GetListAsync(x => srmPrs.Select(c => c.Id).Contains(x.polist_id.GetValueOrDefault())).Result;
                    /*var occupyListLINQ = _businessDbContext.srm_po_occupy.Where(x => x.company_id.ToString() == companyid && !x.IsDeleted).Join(_businessDbContext.srm_pr_main.Where(s => s.company_id.ToString() == companyid && addPolist.Select(x => x.pr_billno).Contains(s.pr_billno) && s.state == 1), p => p.polist_id, pr => pr.Id, (p, pr) => new { p, pr }).ToList();
                    List<srm_po_occupy> occupylist = new List<srm_po_occupy>();
                    for (int i = 0; i < occupyListLINQ.Count; i++)
                    {
                        occupylist.Add(occupyListLINQ[i].p);
                    }*/
                    occupylist.ForEach(x =>
                    {
                        var pr = srmPrs.Find(c => c.Id == x.polist_id);
                        if (pr != null)
                        {
                            var addplist = addPolist.Find(f => f.pr_billno == pr.pr_billno);
                            if (addplist != null)
                            {
                                x.polist_id = addplist.Id;
                                occList.Add(x);
                            }
                        }
                    });
                }
                if (addPo.Any())
                {
                    await _businessDbContext.BulkInsertAsync(addPo);
                }
                if (updatePo.Any())
                {
                    await _businessDbContext.BulkUpdateAsync(updatePo);
                }
                if (addPolist.Any())
                {
                    await _businessDbContext.BulkInsertAsync(addPolist);
                }
                if (occList.Any())
                {
                    await _businessDbContext.BulkUpdateAsync(occList);
                }
                if (updatePolist.Any())
                {
                    await _businessDbContext.BulkUpdateAsync(updatePolist);
                }
            }
            catch(Exception ex)
            {
                return ex.Message;
            }
            return "ok";
        }

        private int GetPurOrdStatus(string status)
        {
            int poStatu = 0;
            switch (status)
            {
                case "":
                    poStatu = 0;
                    break;
                case "A":
                    poStatu = 1;
                    break;
                case "B":
                    poStatu = 2;
                    break;
                case "C":
                    poStatu = 3;
                    break;
                default:
                    poStatu = 0;
                    break;
            }
            return poStatu;
        }

        /// <summary>
        /// 同步清除库存占用记录（工单针对原材料，销售订单针对成品库存占用）
        /// </summary>
        /// <returns></returns>
        public async Task<string> SyncItemStockOccupyClear()
        {
            try
            {
                IConfiguration configuration = new ConfigurationBuilder().AddJsonFile("appsettings.json", optional: true, reloadOnChange: true).Build();
                string domain = configuration.GetConnectionString("Factory_id");
                string companyid = configuration.GetConnectionString("Company_id");
                if (string.IsNullOrEmpty(domain) || string.IsNullOrEmpty(companyid))
                {
                    return "请检查配置文件中公司和工厂编码是否维护。";
                }
                DateTime start = DateTime.Now.Date.AddDays(-1);

                //清除工单移除物料后的占用记录。
                _businessDbContext.Database.ExecuteSqlRaw("DELETE s FROM ic_item_stockoccupy s left join WorkOrdDetail d on s.morder_mo=d.WorkOrd AND s.icitem_number=d.ItemNum WHERE ISNULL(d.ItemNum, '')=''");

                //销售订单取过去两天和当天的记录，已发出的数据，进行清理库存占用表记录
                /*var ASNBOLdtls = _ASNBOLShipperDetail.Select(x => x.Domain == domain && x.IsActive && x.shtype == "SH" && x.Typed != "S" && x.RealQty > 0 && x.UpdateTime >= start).ToList();
                var ordnbr = ASNBOLdtls.Select(x => x.OrdNbr).ToList();
                var alldtls = ASNBOLdtls.Where(x => ordnbr.Contains(x.OrdNbr)).ToList();*/

                List<ic_item_stockoccupy> dellist = new List<ic_item_stockoccupy>();
                List<ic_item_stockoccupy> updtelist = new List<ic_item_stockoccupy>();

                /*var seorderentrys = _mysql_crm_seorderentry.GetListAsync(x => x.factory_id.ToString() == domain && ordnbr.Contains(x.bill_no)).Result;
                var occupys = _ic_item_stockoccupy.GetListAsync(x => x.factory_id.ToString() == domain && seorderentrys.Select(c => c.Id).Contains(x.orderentry_id.GetValueOrDefault())).Result;
                foreach (var se in seorderentrys)
                {
                    var seASNBOLdtl = alldtls.Where(x => x.OrdNbr == se.bill_no && x.ContainerItem == se.item_number).ToList();
                    if (seASNBOLdtl.Any())
                    {
                        var oup = occupys.Find(x => x.icitem_number == se.item_number);
                        if (oup != null)
                        {
                            //先按订单数量更新占用数量，然后通过占用数量-发货数量，计算更新占用数量。
                            oup.quantity = se.qty.GetValueOrDefault();
                            decimal cale = oup.quantity - seASNBOLdtl.Sum(x => x.QtyToShip.GetValueOrDefault());
                            if (cale > 0)
                            {
                                oup.quantity = cale;
                                updtelist.Add(oup);
                            }
                            else
                            {
                                dellist.Add(oup);
                            }
                        }
                    }
                }*/
                //工单按领料单已出库完成记录，清理掉库存占用记录
                List<NbrMaster> nbrlist = _nbrMaster.Select(x => x.Domain == domain && x.Type.ToUpper() == "SM" && x.UpdateTime >= start).ToList();
               
                var workords = nbrlist.Select(x => x.WorkOrd).Distinct();
                var occupy = _ic_item_stockoccupy.GetListAsync(x => workords.Contains(x.morder_mo)).Result;
                var nbrDtls = _nbrDetail.Select(x => x.Domain == domain && nbrlist.Select(c => c.RecID).Contains(x.NbrRecID)).ToList();
                foreach (var nbr in nbrlist)
                {
                    var dtls = nbrDtls.Where(x => x.NbrRecID == nbr.RecID).ToList();
                    foreach (var dtl in dtls)
                    {
                        var occ = occupy.Find(x => x.icitem_number == dtl.ItemNum && x.morder_mo == nbr.WorkOrd);
                        if (occ != null)
                        {
                            decimal differ = dtl.QtyOrd - dtl.QtyRec;
                            if (differ > 0)
                            {
                                occ.quantity = differ;
                                updtelist.Add(occ);
                            }
                            else
                            {
                                dellist.Add(occ);
                            }
                        }
                    }
                }
                if (updtelist.Any())
                {
                    _businessDbContext.BulkUpdate(updtelist);
                }
                if (dellist.Any())
                {
                    await _ic_item_stockoccupy.HardDeleteAsync(dellist);
                }
                return "ok";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }


        /// <summary>
        /// 获取采购预测数据,提供供应商代码查询
        /// </summary>
        /// <returns></returns>
        public async Task<string> GetProcurementForecastApi(string purchasenumber)
        {
            try
            {
                if (string.IsNullOrEmpty(purchasenumber))
                {
                    return "请求的供应商编码无效，请检查。";
                }
                //首先根据发布表，提取对应的物料号，供应商信息。
                string sql = string.Format("SELECT p.wlh,p.wlmc,p.wlgg,p.gysdm,p.gysmc,p.jbdw,p.sl,p.cgyf from (" +
                    "select wlh, wlmc, wlgg, gysdm, gysmc, jbdw, sl, left(rq, 4) + '-' + substring(rq, 5, 2) cgyf from scm_cgyc WHERE gysdm = '{0}'" +
                    " union " +
                    "SELECT i.number wlh, i.name wlmc, i.model wlgg, pr_purchasenumber gysdm, pr_purchasename gysmc, i.unit jbdw, p.pr_aqty sl, convert(varchar(7), p.pr_sarrive_date, 120) cgyf FROM srm_pr_main p left join ic_item i on p.icitem_id = i.id and p.company_id = i.company_id AND p.factory_id = i.factory_id " +
                    "WHERE p.state = 1 AND p.pr_sarrive_date is not null AND p.pr_purchasenumber = '{0}' AND isnull(analogcalcversion,'')<>'') p left join scm_cgycfb m on p.wlh = m.wlh and p.gysdm = m.gysdm WHERE m.fbrq is not null", purchasenumber);
                //正式测试需要加上  and isnull(analogcalcversion,'')<>''
                var list = _businessDbContext.ProcurementForecast.FromSqlRaw(sql).ToList();
                string result = "OK";
                if (list.Any())
                {
                    result += JsonConvert.SerializeObject(list);
                }
                return result;
            }
            catch (Exception ex)
            {
                new NLogHelper("SystemJobAppService").WriteLog("GetProcurementForecastApi", "瑞奇采购预测获取失败：" + ex.Message);
                return "NG瑞奇采购预测获取失败，请联系管理员。";
            }
        }
    }
}