예제 #1
0
        /// <summary>
        /// 执行导入EXCEL数据
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fieldNames"></param>
        /// <returns></returns>
        public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
        {
            List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardExcelInfos = CommonDAL.DatatableConvertToList <MaintainInhouseLogisticStandardInfo>(dataTable).ToList();

            if (maintainInhouseLogisticStandardExcelInfos.Count == 0)
            {
                throw new Exception("MC:1x00000043");///数据格式不符合导入规范
            }
            ///获取业务表中要变更的数据集合,准备对比
            List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardDAL().GetListForInterfaceDataSync(maintainInhouseLogisticStandardExcelInfos.Select(d => d.PartNo).ToList());
            List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsDAL().GetListForInterfaceDataSync(maintainInhouseLogisticStandardExcelInfos.Select(d => d.PartNo).ToList());
            List <PartsStockInfo>    partsStockInfos    = new PartsStockDAL().GetListForInterfaceDataSync(maintainInhouseLogisticStandardExcelInfos.Select(d => d.PartNo).ToList());
            List <PartsBoxInfo>      partsBoxInfos      = new PartsBoxDAL().GetList("", string.Empty);
            ///执行的SQL语句
            string sql = string.Empty;
            List <MaintainInhouseLogisticStandardInfo> standardInfos = new List <MaintainInhouseLogisticStandardInfo>();

            fieldNames.Add("SWmNo", "S_WM_NO");
            fieldNames.Add("SZoneNo", "S_ZONE_NO");
            fieldNames.Add("TWmNo", "T_WM_NO");
            fieldNames.Add("TZoneNo", "T_ZONE_NO");
            fieldNames.Add("Plant", "PLANT");
            fieldNames.Add("Workshop", "WORKSHOP");
            fieldNames.Add("AssemblyLine", "ASSEMBLY_LINE");
            List <string> fields = new List <string>(fieldNames.Keys);

            ///逐条处理中间表数据
            foreach (var maintainInhouseLogisticStandardExcelInfo in maintainInhouseLogisticStandardExcelInfos)
            {
                ///
                MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo);
                if (maintainPartsInfo == null)
                {
                    throw new Exception("MC:0x00000224");///物料基础信息数据错误
                }
                maintainInhouseLogisticStandardExcelInfo.Status = (int)BasicDataStatusConstants.Created;
                ///物料简称、物料中文描述、物料英文描述由基础数据中同步
                maintainInhouseLogisticStandardExcelInfo.PartCname    = maintainPartsInfo.PartCname;
                maintainInhouseLogisticStandardExcelInfo.PartEname    = maintainPartsInfo.PartEname;
                maintainInhouseLogisticStandardExcelInfo.PartNickname = maintainPartsInfo.PartNickname;
                ///
                PartsBoxInfo partsBoxInfo = partsBoxInfos.FirstOrDefault(d => d.PullMode.ToString() == maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode &&
                                                                         d.BoxParts == maintainInhouseLogisticStandardExcelInfo.InhousePartClass);
                if (partsBoxInfo == null)
                {
                    throw new Exception("MC:0x00000225");///拉动零件类数据错误
                }
                maintainInhouseLogisticStandardExcelInfo.SWmNo        = partsBoxInfo.SWmNo;
                maintainInhouseLogisticStandardExcelInfo.SZoneNo      = partsBoxInfo.SZoneNo;
                maintainInhouseLogisticStandardExcelInfo.TWmNo        = partsBoxInfo.TWmNo;
                maintainInhouseLogisticStandardExcelInfo.TZoneNo      = partsBoxInfo.TZoneNo;
                maintainInhouseLogisticStandardExcelInfo.Plant        = partsBoxInfo.Plant;
                maintainInhouseLogisticStandardExcelInfo.Workshop     = partsBoxInfo.Workshop;
                maintainInhouseLogisticStandardExcelInfo.AssemblyLine = partsBoxInfo.AssemblyLine;
                ///目标地点
                PartsStockInfo partsStockInfo = partsStockInfos.FirstOrDefault(d => d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo &&
                                                                               d.WmNo == maintainInhouseLogisticStandardExcelInfo.TWmNo &&
                                                                               d.ZoneNo == maintainInhouseLogisticStandardExcelInfo.TZoneNo);
                ///无维护先后要求
                if (partsStockInfo != null)
                {
                    maintainInhouseLogisticStandardExcelInfo.InboundPackageModel = partsStockInfo.InboundPackageModel;
                    maintainInhouseLogisticStandardExcelInfo.InboundPackage      = partsStockInfo.InboundPackage;
                }
                ///当所选拉动零件类⑥的拉动方式⑤为10时间窗且其配置为库存当量拉动时,MIN⑯和MAX⑰允许维护大于零的数据,且MIN⑯小于MAX⑰
                if (int.Parse(maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode) == (int)PullModeConstants.Twd)///TODO:缺少库存当量拉动的判断,等TWD表结构
                {
                    if (maintainInhouseLogisticStandardExcelInfo.Min.GetValueOrDefault() > maintainInhouseLogisticStandardExcelInfo.Max.GetValueOrDefault())
                    {
                        throw new Exception("MC:0x00000404");///MIN值必须小于MAX
                    }
                }
                if (maintainInhouseLogisticStandardExcelInfo.IsTriggerPull.GetValueOrDefault() == true)
                {
                    if (string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.WmNo))
                    {
                        throw new Exception("MC:0x00000405");///层级拉动仓库不允许为空
                    }
                    if (string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.ZoneNo))
                    {
                        throw new Exception("MC:0x00000406");///层级拉动存储区不允许为空
                    }
                }



                ///
                MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandardInfo = maintainInhouseLogisticStandardInfos.FirstOrDefault(d =>
                                                                                                                                              d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo &&
                                                                                                                                              d.InhouseSystemMode == maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode &&
                                                                                                                                              d.InhousePartClass == maintainInhouseLogisticStandardExcelInfo.InhousePartClass);
                if (maintainInhouseLogisticStandardInfo == null)
                {
                    if (string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.PartNo) ||
                        string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode) ||
                        string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.InhousePartClass))
                    {
                        throw new Exception("MC:0x00000226");///物料号、拉动模式、零件类为必填项
                    }
                    ///相同目标仓库存储区,同物料号同供应商,即使跨拉动方式也需要唯一
                    int cnt = maintainInhouseLogisticStandardInfos.Where(d =>
                                                                         d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo &&
                                                                         d.TWmNo == maintainInhouseLogisticStandardExcelInfo.TWmNo &&
                                                                         d.TZoneNo == maintainInhouseLogisticStandardExcelInfo.TZoneNo &&
                                                                         d.SupplierNum == maintainInhouseLogisticStandardExcelInfo.SupplierNum).Count();
                    if (cnt > 0)
                    {
                        throw new Exception("MC:0x00000408");///物料号、拉动方式、拉动零件类、供应商代码组合不唯一
                    }
                    ///物料号①、拉动方式⑤、拉动零件类⑥、供应商代码⑦组合唯一
                    cnt = maintainInhouseLogisticStandardInfos.Where(d =>
                                                                     d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo &&
                                                                     d.InhouseSystemMode == maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode &&
                                                                     d.InhousePartClass == maintainInhouseLogisticStandardExcelInfo.InhousePartClass &&
                                                                     d.SupplierNum == maintainInhouseLogisticStandardExcelInfo.SupplierNum).Count();
                    if (cnt > 0)
                    {
                        throw new Exception("MC:0x00000407");///物料号、拉动方式、拉动零件类、供应商代码组合不唯一
                    }
                    ///字段
                    string insertFieldString = string.Empty;
                    ///值
                    string insertValueString = string.Empty;
                    for (int i = 0; i < fields.Count; i++)
                    {
                        string valueStr = CommonDAL.GetFieldValueForSql <MaintainInhouseLogisticStandardInfo>(maintainInhouseLogisticStandardExcelInfo, fields[i]);
                        if (string.IsNullOrEmpty(valueStr))
                        {
                            throw new Exception("MC:1x00000043");///数据格式不符合导入规范
                        }
                        insertFieldString += "[" + fieldNames[fields[i]] + "],";
                        insertValueString += valueStr + ",";
                    }

                    sql += "if not exists (select * from LES.TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD with(nolock) "
                           + "where [PART_NO] = N'" + maintainInhouseLogisticStandardExcelInfo.PartNo + "' and [INHOUSE_SYSTEM_MODE] = N'" + maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode + "' and [INHOUSE_PART_CLASS] = N'" + maintainInhouseLogisticStandardExcelInfo.InhousePartClass + "' and [VALID_FLAG] = 1) "
                           + "insert into [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] ("
                           + "[FID],"
                           + insertFieldString
                           + "[CREATE_USER],"
                           + "[CREATE_DATE],"
                           + "[VALID_FLAG]"
                           + ") values ("
                           + "NEWID(),"              ///FID
                           + insertValueString
                           + "N'" + loginUser + "'," ///CREATE_USER
                           + "GETDATE(),"            ///CREATE_DATE
                           + "1"                     ///VALID_FLAG
                           + ");";
                    maintainInhouseLogisticStandardInfos.Add(maintainInhouseLogisticStandardExcelInfo);
                    continue;
                }
                ///
                if (string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.PartNo) ||
                    string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode) ||
                    string.IsNullOrEmpty(maintainInhouseLogisticStandardExcelInfo.InhousePartClass))
                {
                    throw new Exception("MC:0x00000226");///物料号、拉动模式、零件类为必填项
                }
                ///相同目标仓库存储区,同物料号同供应商,即使跨拉动方式也需要唯一
                int count = maintainInhouseLogisticStandardInfos.Where(d =>
                                                                       d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo &&
                                                                       d.TWmNo == maintainInhouseLogisticStandardExcelInfo.TWmNo &&
                                                                       d.TZoneNo == maintainInhouseLogisticStandardExcelInfo.TZoneNo &&
                                                                       d.SupplierNum == maintainInhouseLogisticStandardExcelInfo.SupplierNum &&
                                                                       d.Id != maintainInhouseLogisticStandardInfo.Id).Count();
                if (count > 0)
                {
                    throw new Exception("MC:0x00000408");///物料号、拉动方式、拉动零件类、供应商代码组合不唯一
                }
                ///物料号①、拉动方式⑤、拉动零件类⑥、供应商代码⑦组合唯一
                count = maintainInhouseLogisticStandardInfos.Where(d =>
                                                                   d.PartNo == maintainInhouseLogisticStandardExcelInfo.PartNo &&
                                                                   d.InhouseSystemMode == maintainInhouseLogisticStandardExcelInfo.InhouseSystemMode &&
                                                                   d.InhousePartClass == maintainInhouseLogisticStandardExcelInfo.InhousePartClass &&
                                                                   d.SupplierNum == maintainInhouseLogisticStandardExcelInfo.SupplierNum &&
                                                                   d.Id != maintainInhouseLogisticStandardInfo.Id).Count();
                if (count > 0)
                {
                    throw new Exception("MC:0x00000407");///物料号、拉动方式、拉动零件类、供应商代码组合不唯一
                }
                ///值
                string valueString = string.Empty;
                for (int i = 0; i < fields.Count; i++)
                {
                    string valueStr = CommonDAL.GetFieldValueForSql <MaintainInhouseLogisticStandardInfo>(maintainInhouseLogisticStandardExcelInfo, fields[i]);
                    if (string.IsNullOrEmpty(valueStr))
                    {
                        throw new Exception("MC:1x00000043");///数据格式不符合导入规范
                    }
                    valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
                }
                sql += "update [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] set "
                       + valueString
                       + "[MODIFY_USER] = N'" + loginUser + "',"
                       + "[MODIFY_DATE] = GETDATE() "
                       + "where [ID] = " + maintainInhouseLogisticStandardInfo.Id + ";";
            }
            ///
            if (string.IsNullOrEmpty(sql))
            {
                return(false);
            }

            return(CommonDAL.ExecuteNonQueryBySql(sql));
        }
예제 #2
0
        /// <summary>
        /// 执行导入EXCEL数据
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fieldNames"></param>
        /// <returns></returns>
        public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
        {
            List <PartsStockInfo> partsStockExcelInfos = CommonDAL.DatatableConvertToList <PartsStockInfo>(dataTable).ToList();

            if (partsStockExcelInfos.Count == 0)
            {
                throw new Exception("MC:1x00000043");///数据格式不符合导入规范
            }
            ///获取业务表中要变更的数据集合,准备对比
            List <PartsStockInfo>    partsStockInfos    = new PartsStockDAL().GetListForInterfaceDataSync(partsStockExcelInfos.Select(d => d.PartNo).ToList());
            List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsDAL().GetListForInterfaceDataSync(partsStockExcelInfos.Select(d => d.PartNo).ToList());
            ///执行的SQL语句
            string sql = string.Empty;

            List <string> fields = new List <string>(fieldNames.Keys);

            ///逐条处理中间表数据
            foreach (var partsStockExcelInfo in partsStockExcelInfos)
            {
                ///
                MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == partsStockExcelInfo.PartNo);
                if (maintainPartsInfo == null)
                {
                    throw new Exception("MC:0x00000224");///物料基础信息数据错误
                }
                ///物料简称、物料中文描述、物料英文描述由基础数据中同步
                partsStockExcelInfo.PartCname    = maintainPartsInfo.PartCname;
                partsStockExcelInfo.PartEname    = maintainPartsInfo.PartEname;
                partsStockExcelInfo.PartNickname = maintainPartsInfo.PartNickname;
                ///
                PartsStockInfo partsStockInfo = partsStockInfos.FirstOrDefault(d => d.PartNo == partsStockExcelInfo.PartNo &&
                                                                               d.SupplierNum == partsStockExcelInfo.SupplierNum &&
                                                                               d.WmNo == partsStockExcelInfo.WmNo &&
                                                                               d.ZoneNo == partsStockExcelInfo.ZoneNo);
                if (partsStockInfo == null)
                {
                    if (string.IsNullOrEmpty(partsStockExcelInfo.PartNo) ||
                        string.IsNullOrEmpty(partsStockExcelInfo.WmNo) ||
                        string.IsNullOrEmpty(partsStockExcelInfo.ZoneNo))
                    {
                        throw new Exception("MC:0x00000223");///物料号、仓库、存储区为必填项
                    }
                    ///字段
                    string insertFieldString = string.Empty;
                    ///值
                    string insertValueString = string.Empty;
                    for (int i = 0; i < fields.Count; i++)
                    {
                        string valueStr = CommonDAL.GetFieldValueForSql <PartsStockInfo>(partsStockExcelInfo, fields[i]);
                        if (string.IsNullOrEmpty(valueStr))
                        {
                            throw new Exception("MC:1x00000043");///数据格式不符合导入规范
                        }
                        insertFieldString += "[" + fieldNames[fields[i]] + "],";
                        insertValueString += valueStr + ",";
                    }

                    sql += "if not exists (select * from LES.TM_BAS_PARTS_STOCK with(nolock) "
                           + "where [PART_NO] = N'" + partsStockExcelInfo.PartNo + "' "
                           + "and [WM_NO] = N'" + partsStockExcelInfo.WmNo + "' "
                           + "and [ZONE_NO] = N'" + partsStockExcelInfo.ZoneNo + "' "
                           + "and [SUPPLIER_NUM] = N'" + partsStockExcelInfo.SupplierNum + "' and [VALID_FLAG] = 1) "
                           + "insert into [LES].[TM_BAS_PARTS_STOCK] ("
                           + "[FID],"
                           + insertFieldString
                           + "[CREATE_USER],"
                           + "[CREATE_DATE],"
                           + "[VALID_FLAG]"
                           + ") values ("
                           + "NEWID(),"              ///FID
                           + insertValueString
                           + "N'" + loginUser + "'," ///CREATE_USER
                           + "GETDATE(),"            ///CREATE_DATE
                           + "1"                     ///VALID_FLAG
                           + ");";
                    continue;
                }
                ///
                if (string.IsNullOrEmpty(partsStockExcelInfo.PartNo) ||
                    string.IsNullOrEmpty(partsStockExcelInfo.WmNo) ||
                    string.IsNullOrEmpty(partsStockExcelInfo.ZoneNo))
                {
                    throw new Exception("MC:0x00000223");///物料号、仓库、存储区为必填项
                }
                ///值
                string valueString = string.Empty;
                for (int i = 0; i < fields.Count; i++)
                {
                    string valueStr = CommonDAL.GetFieldValueForSql <PartsStockInfo>(partsStockExcelInfo, fields[i]);
                    if (string.IsNullOrEmpty(valueStr))
                    {
                        throw new Exception("MC:1x00000043");///数据格式不符合导入规范
                    }
                    valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
                }
                sql += "update [LES].[TM_BAS_PARTS_STOCK] set "
                       + valueString
                       + "[MODIFY_USER] = N'" + loginUser + "',"
                       + "[MODIFY_DATE] = GETDATE() "
                       + "where [ID] = " + partsStockInfo.Id + ";";
            }
            ///
            if (string.IsNullOrEmpty(sql))
            {
                return(false);
            }

            return(CommonDAL.ExecuteNonQueryBySql(sql));
        }
        /// <summary>
        /// 入库数据导入
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fieldNames"></param>
        /// <param name="loginUser"></param>
        /// <returns></returns>
        public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
        {
            List <ReceiveDetailInfo> receiveDetailInfos = CommonDAL.DatatableConvertToList <ReceiveDetailInfo>(dataTable).ToList();

            if (receiveDetailInfos.Count == 0)
            {
                throw new Exception("MC:1x00000043");///数据格式不符合导入规范
            }
            List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsDAL().GetListForInterfaceDataSync(receiveDetailInfos.Select(d => d.PartNo).ToList());
            ///SUPPLIER_NUM.60、BOOK_KEEPER、RECEIVE_TYPE.70、TRAN_TIME.80、ORGANIZATION_FID.90、WM_NO.30、COST_CENTER.100、CONTRACT_NO.110合并
            var groupReceives = receiveDetailInfos
                                .GroupBy(b => new { b.SupplierNum, b.BookKeeper, b.ReceiveType, b.TranTime, b.OrganizationFid, b.WmNo, b.CostCenter, b.ContractNo })
                                .Select(p => new { p.Key }).ToList();
            string sql = string.Empty;

            foreach (var groupReceive in groupReceives)
            {
                string receiveNo  = new SeqDefineDAL().GetCurrentCode("RECEIVE_NO");
                Guid   receiveFid = Guid.NewGuid();
                List <ReceiveDetailInfo> receiveDetailList = receiveDetailInfos.Where(d =>
                                                                                      d.SupplierNum == groupReceive.Key.SupplierNum &&
                                                                                      d.BookKeeper == groupReceive.Key.BookKeeper &&
                                                                                      d.ReceiveType == groupReceive.Key.ReceiveType &&
                                                                                      d.TranTime == groupReceive.Key.TranTime &&
                                                                                      d.OrganizationFid == groupReceive.Key.OrganizationFid &&
                                                                                      d.WmNo == groupReceive.Key.WmNo &&
                                                                                      d.CostCenter == groupReceive.Key.CostCenter &&
                                                                                      d.ContractNo == groupReceive.Key.ContractNo).ToList();
                int rowNo = 1;
                foreach (var receiveDetail in receiveDetailList)
                {
                    ///PART_NO.10、PART_ENAME.60、ZONE_NO.40、DLOC.50、ACTUAL_QTY.20
                    MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == receiveDetail.PartNo);
                    if (maintainPartsInfo == null)
                    {
                        if (string.IsNullOrEmpty(receiveDetail.PartNo))
                        {
                            throw new Exception("MC:0x00000227");///器材编号不能为空
                        }
                        sql += "insert into [LES].[TM_BAS_MAINTAIN_PARTS] "
                               + "(FID, PART_NO, PART_CNAME, PART_ENAME, PART_UNITS, PART_CLS, PART_GROUP, PURCHASE_UNIT_PRICE, VALID_FLAG, CREATE_USER, CREATE_DATE) values "
                               + "(NEWID(), "
                               + "N'" + receiveDetail.PartNo + "', "
                               + "N'" + receiveDetail.PartCname + "', "
                               + "N'" + receiveDetail.PartEname + "', "
                               + "N'" + receiveDetail.PartUnits + "', "
                               + "N'" + receiveDetail.PartCls + "', "
                               + "N'" + receiveDetail.PartGroup + "', "
                               + "" + receiveDetail.PurchaseUnitPrice.GetValueOrDefault() + ", "
                               + "1, N'" + loginUser + "', GETDATE());";
                    }
                    else
                    {
                        ///物料描述
                        receiveDetail.PartCname = maintainPartsInfo.PartCname;
                        ///规格型号
                        receiveDetail.PartCls = maintainPartsInfo.PartCls;
                        ///产地
                        // receiveDetail.OriginPlace = maintainPartsInfo.OriginPlace;
                        ///计量单位
                        receiveDetail.MeasuringUnitNo = maintainPartsInfo.PartUnits;
                        ///物料采购单价
                        //receiveDetail.PurchaseUnitPrice = maintainPartsInfo.PurchaseUnitPrice;
                    }

                    receiveDetail.PartPrice = receiveDetail.PurchaseUnitPrice.GetValueOrDefault() * receiveDetail.ActualQty.GetValueOrDefault();
                    sql += "insert into [LES].[TT_WMM_RECEIVE_DETAIL] "
                           + "(FID, RECEIVE_FID, SUPPLIER_NUM, TARGET_WM, TARGET_ZONE, TARGET_DLOC, PART_NO, PART_CNAME, PART_ENAME, MEASURING_UNIT_NO, ACTUAL_QTY, TRAN_NO, RUNSHEET_NO, ROW_NO, ORIGIN_PLACE, PURCHASE_UNIT_PRICE, PART_PRICE, PART_CLS, VALID_FLAG, CREATE_USER, CREATE_DATE) values "
                           + "(NEWID(), "
                           + "N'" + receiveFid + "', "
                           + "N'" + groupReceive.Key.SupplierNum + "', "
                           + "N'" + receiveDetail.WmNo + "', "
                           + "N'" + receiveDetail.ZoneNo + "', "
                           + "N'" + receiveDetail.Dloc + "', "
                           + "N'" + receiveDetail.PartNo + "', "
                           + "N'" + receiveDetail.PartCname + "', "
                           + "N'" + receiveDetail.PartEname + "', "
                           + "N'" + receiveDetail.PartUnits + "', "
                           + "" + receiveDetail.ActualQty.GetValueOrDefault() + ", "
                           + "N'" + receiveNo + "', "
                           + "N'" + groupReceive.Key.ContractNo + "', "
                           + "" + rowNo++ + ", "
                           + "N'" + receiveDetail.OriginPlace + "', "
                           + "" + receiveDetail.PurchaseUnitPrice.GetValueOrDefault() + ", "
                           + "" + receiveDetail.PartPrice.GetValueOrDefault() + ", "
                           + "N'" + receiveDetail.PartCls + "', "
                           + "1, N'" + loginUser + "', GETDATE());";
                }
                ///物料价格合计
                decimal sumOfPrice = receiveDetailList.Sum(d => d.PartPrice.GetValueOrDefault());
                ///物料合计数量
                decimal sumPartQty = receiveDetailList.Sum(d => d.ActualQty.GetValueOrDefault());

                if (!DateTime.TryParse(groupReceive.Key.TranTime.ToString("yyyy-MM-dd HH:mm:ss"), out DateTime tranTime))
                {
                    throw new Exception("MC:0x00000328");///入库时间不能为空
                }
                sql += "insert into [LES].[TT_WMM_RECEIVE] "
                       + "(FID, RECEIVE_NO, SUPPLIER_NUM, WM_NO, RECEIVE_TYPE, TRAN_TIME, BOOK_KEEPER, STATUS, RUNSHEET_NO, ORGANIZATION_FID, COST_CENTER, SUM_PART_QTY, SUM_OF_PRICE, VALID_FLAG, CREATE_USER, CREATE_DATE) values "
                       + "(N'" + receiveFid + "', "
                       + "N'" + receiveNo + "', "
                       + "N'" + groupReceive.Key.SupplierNum + "', "
                       + "N'" + groupReceive.Key.WmNo + "', "
                       + "" + groupReceive.Key.ReceiveType + ", "
                       + "N'" + tranTime.ToString("yyyy-MM-dd HH:mm:ss") + "', "
                       + "N'" + groupReceive.Key.BookKeeper + "', "
                       + "" + (int)WmmOrderStatusConstants.Created + ", "
                       + "N'" + groupReceive.Key.ContractNo + "', "
                       + "N'" + groupReceive.Key.OrganizationFid + "', "
                       + "N'" + groupReceive.Key.CostCenter + "', "
                       + "" + sumPartQty + ", "
                       + "" + sumOfPrice + ", "
                       + "1, N'" + loginUser + "', GETDATE());";
            }
            if (string.IsNullOrEmpty(sql))
            {
                return(false);
            }
            return(CommonDAL.ExecuteNonQueryBySql(sql));
        }
예제 #4
0
        /// <summary>
        /// 完成
        /// </summary>
        /// <param name="id"></param>
        /// <param name="loginUser"></param>
        /// <returns></returns>
        public bool GenerationdifferenceInfos(List <string> rowsKeyValues, string loginUser)
        {
            List <InventoryNoticeOrderInfo> inventoryNoticeOrderInfos = dal.GetList("[ID] IN (" + string.Join(",", rowsKeyValues) + ") and [STATUS] = " + (int)InventoryOrderStatusConstants.CONFIRMED + "", string.Empty);

            if (inventoryNoticeOrderInfos.Count == 0)
            {
                throw new Exception("MC:0x00000376");///已确认状态才能进行完成操作
            }
            StringBuilder stringBuilder = new StringBuilder();
            string        check         = new ConfigBLL().GetValueByCode("ENABLE_SAP_INVENTORY_REPORT_FLAG");

            if (check == "true")
            {
                List <InventoryNoticeOrderInfo> infos = new List <InventoryNoticeOrderInfo>();

                List <InventoryOrderPartInfo> orderPartInfos = new InventoryOrderPartDAL().GetList("[ORDER_FID] IN ('" + string.Join("','", inventoryNoticeOrderInfos.Select(d => d.Fid).ToArray()) + "')", string.Empty);

                List <InventoryPartBarcodeInfo> inventoryPartBarcodes = new InventoryPartBarcodeDAL().GetList("[PART_FID] IN ('" + string.Join("','", orderPartInfos.Select(d => d.Fid).ToArray()) + "')", string.Empty);
                Dictionary <string, Guid>       keyValues             = new Dictionary <string, Guid>();

                List <MaintainPartsInfo> maintainParts = new MaintainPartsDAL().GetList("[PART_NO] IN ('" + string.Join("','", orderPartInfos.Select(d => d.PartNo).ToArray()) + "')", string.Empty);

                foreach (var item in inventoryNoticeOrderInfos)
                {
                    ///生成SAP盘点报告中间表数据
                    Guid guid = Guid.NewGuid();
                    if (keyValues.Keys.Contains <string>(item.SapIblnr) == false)
                    {
                        keyValues.Add(item.SapIblnr, guid);
                    }

                    List <InventoryOrderPartInfo> partInfos = orderPartInfos.Where(d => d.OrderFid == item.Fid).ToList();
                    foreach (var items in partInfos)
                    {
                        string PartName = maintainParts.FirstOrDefault(d => d.PartNo == items.PartNo).PartCname;
                        stringBuilder.Append("insert into [LES].[TI_IFM_SAP_INVENTORY_CHECK_REPORT] ([FID],[LOG_FID],[IBLNR],[LGORT],[REMARKS],[MATNR],[MENGE],[ZLDAT],[AQTY],[DQTY],[PROCESS_FLAG],[PROCESS_TIME],[VALID_FLAG],[CREATE_USER],[CREATE_DATE])values(");
                        string partQty = string.Empty;
                        if (items.PartQty == null)
                        {
                            partQty = "NULL";
                        }
                        else
                        {
                            partQty = ((int)items.PartQty).ToString();
                        }
                        string sapDqty = string.Empty;
                        if (items.SapDqty == null)
                        {
                            sapDqty = "NULL";
                        }
                        else
                        {
                            sapDqty = ((int)items.SapDqty).ToString();
                        }

                        stringBuilder.Append("newid(),N'" + keyValues[item.SapIblnr] + "',N'" + item.SapIblnr + "',N'" + item.SapLgort + "',N'" + item.Comments + "',N'" + items.PartNo + "'," + partQty + ",N'" + item.SapZldat + "',N'" + items.SapMenge + "'," + sapDqty + ",N'" + item.Status + "',N'" + item.ModifyDate + "',1,N'" + loginUser + "',GETDATE());\n\n");

                        InventoryPartBarcodeInfo barcodeInfo = inventoryPartBarcodes.Where(d => d.PartFid == items.Fid).FirstOrDefault();
                        if (barcodeInfo != null)
                        {
                            if (barcodeInfo.PartQty != items.DifferenceQty)
                            {
                                stringBuilder.Append("update [LES].[TT_WMM_INVENTORY_PART_BARCODE] set [PART_QTY] = N'" + items.DifferenceQty + "',[MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' where [ID] = " + barcodeInfo.Id + ";\n\n");
                            }
                        }
                    }

                    ///新数据排除盘点计划号一致的
                    InventoryNoticeOrderInfo InventoryNoticeOrderInfos = infos.Where(d => d.SapIblnr == item.SapIblnr).FirstOrDefault();
                    if (InventoryNoticeOrderInfos == null)
                    {
                        infos.Add(item);
                    }
                }
                List <InventoryNoticeOrderInfo> inventoryNotices = dal.GetList(" [ID] not in  (" + string.Join(",", rowsKeyValues) + ") and [STATUS] = " + (int)InventoryOrderStatusConstants.CONFIRMED + " and [SAP_IBLNR] in ('" + string.Join("','", inventoryNoticeOrderInfos.Select(d => d.SapIblnr).ToArray()) + "')", string.Empty);
                foreach (var item in infos)
                {
                    List <InventoryNoticeOrderInfo> inventoryNoticeOrders = inventoryNotices.Where(d => d.SapIblnr == item.SapIblnr).ToList();
                    List <InventoryNoticeOrderInfo> inventories           = inventoryNotices.Where(d => d.SapIblnr == item.SapIblnr && d.Status == item.Status).ToList();
                    if (inventoryNoticeOrders.Count == inventories.Count)
                    {
                        //生成盘点报告发送任务
                        string targetSystem = "SAP";
                        string methodCode   = "LES-WMM-071";
                        string keyValue     = item.SapIblnr;
                        stringBuilder.AppendFormat(BLL.LES.CommonBLL.GetCreateOutboundLogSql(targetSystem, keyValues[item.SapIblnr], methodCode, keyValue, loginUser));
                    }
                }
            }
            using (TransactionScope trans = new TransactionScope())
            {
                if (!string.IsNullOrEmpty(stringBuilder.ToString()))
                {
                    stringBuilder.Append("\n\n update [LES].[TT_WMM_INVENTORY_NOTICE_ORDER] set [STATUS] = " + (int)InventoryOrderStatusConstants.COMPLETED + ",[MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' where [VALID_FLAG] = 1 and [ID] IN (" + string.Join(",", rowsKeyValues) + ") ");
                    CommonDAL.ExecuteNonQueryBySql(stringBuilder.ToString());
                }
                trans.Complete();
            }

            return(true);
        }
예제 #5
0
        /// <summary>
        /// 执行导入EXCEL数据
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fieldNames"></param>
        /// <returns></returns>
        public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
        {
            List <MaintainPartsInfo> maintainPartsExcelInfos = CommonDAL.DatatableConvertToList <MaintainPartsInfo>(dataTable).ToList();

            if (maintainPartsExcelInfos.Count == 0)
            {
                throw new Exception("MC:1x00000043");///数据格式不符合导入规范
            }
            ///获取业务表中要变更的数据集合,准备对比
            List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsDAL().GetListForInterfaceDataSync(maintainPartsExcelInfos.Select(d => d.PartNo).ToList());
            ///执行的SQL语句
            string sql = string.Empty;
            ///获取工厂信息
            List <PlantInfo> plantInfos = new PlantDAL().GetListForInterfaceDataSync();

            List <string> fields = new List <string>(fieldNames.Keys);

            ///逐条处理中间表数据
            foreach (var maintainPartsExcelInfo in maintainPartsExcelInfos)
            {
                ///导入时需要填写LES的工厂编号
                PlantInfo plantInfo = plantInfos.FirstOrDefault(d => d.Plant == maintainPartsExcelInfo.Plant);
                if (plantInfo == null)
                {
                    throw new Exception("MC:0x00000215");///工厂代码在系统中不存在
                }
                ///当前业务数据表中此工厂的该物料信息时需要新增
                MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == maintainPartsExcelInfo.PartNo && d.Plant == maintainPartsExcelInfo.Plant);
                if (maintainPartsInfo == null)
                {
                    ///物料号①、物料中文名称②为必填项
                    if (string.IsNullOrEmpty(maintainPartsExcelInfo.PartCname) || string.IsNullOrEmpty(maintainPartsExcelInfo.PartNo))
                    {
                        throw new Exception("MC:3x00000020");///物料号、物料中文名称为必填项
                    }
                    ///字段
                    string insertFieldString = string.Empty;
                    ///值
                    string insertValueString = string.Empty;
                    for (int i = 0; i < fields.Count; i++)
                    {
                        string valueStr = CommonDAL.GetFieldValueForSql <MaintainPartsInfo>(maintainPartsExcelInfo, fields[i]);
                        if (string.IsNullOrEmpty(valueStr))
                        {
                            throw new Exception("MC:1x00000043");///数据格式不符合导入规范
                        }
                        insertFieldString += "[" + fieldNames[fields[i]] + "],";
                        insertValueString += valueStr + ",";
                    }

                    sql += "if not exists (select * from LES.TM_BAS_MAINTAIN_PARTS with(nolock) where [PART_NO] = N'" + maintainPartsExcelInfo.PartNo + "' and [PLANT] = N'" + maintainPartsExcelInfo.Plant + "' and [VALID_FLAG] = 1) "
                           + "insert into [LES].[TM_BAS_MAINTAIN_PARTS] ("
                           + "[FID],"
                           + insertFieldString
                           + "[CREATE_USER],"
                           + "[CREATE_DATE],"
                           + "[VALID_FLAG]"
                           + ") values ("
                           + "NEWID(),"              ///FID
                           + insertValueString
                           + "N'" + loginUser + "'," ///CREATE_USER
                           + "GETDATE(),"            ///CREATE_DATE
                           + "1"                     ///VALID_FLAG
                           + ");";
                    continue;
                }
                ///物料中文名称②为必填项
                if (string.IsNullOrEmpty(maintainPartsExcelInfo.PartCname))
                {
                    throw new Exception("MC:3x00000020");///物料号、物料中文名称为必填项
                }
                ///值
                string valueString = string.Empty;
                for (int i = 0; i < fields.Count; i++)
                {
                    string valueStr = CommonDAL.GetFieldValueForSql <MaintainPartsInfo>(maintainPartsExcelInfo, fields[i]);
                    if (string.IsNullOrEmpty(valueStr))
                    {
                        throw new Exception("MC:1x00000043");///数据格式不符合导入规范
                    }
                    valueString += "[" + fieldNames[fields[i]] + "] = " + valueStr + ",";
                }
                sql += "update [LES].[TM_BAS_MAINTAIN_PARTS] set "
                       + valueString
                       + "[MODIFY_USER] = N'" + loginUser + "',"
                       + "[MODIFY_DATE] = GETDATE() "
                       + "where [ID] = " + maintainPartsInfo.Id + ";";
            }
            ///
            if (string.IsNullOrEmpty(sql))
            {
                return(false);
            }

            return(CommonDAL.ExecuteNonQueryBySql(sql));
        }