Beispiel #1
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="inhouseSystemMode"></param>
        /// <param name="inhousePartClass"></param>
        /// <param name="partsBoxInfo"></param>
        /// <returns></returns>
        public bool UpdatePartsBoxInfo(PartsBoxInfo partsBoxInfo)
        {
            string sql = "update [LES].[TM_BAS_MAINTAIN_INHOUSE_LOGISTIC_STANDARD] "
                         + "set [PLANT] = @PLANT,[WORKSHOP] = @WORKSHOP,[ASSEMBLY_LINE] = @ASSEMBLY_LINE "
                         + "where [INHOUSE_SYSTEM_MODE] =@INHOUSE_SYSTEM_MODE and [INHOUSE_PART_CLASS] = @INHOUSE_PART_CLASS and [VALID_FLAG] = 1;";
            Database  db  = DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);

            db.AddInParameter(cmd, "@PLANT", DbType.AnsiString, partsBoxInfo.Plant);
            db.AddInParameter(cmd, "@WORKSHOP", DbType.AnsiString, partsBoxInfo.Workshop);
            db.AddInParameter(cmd, "@ASSEMBLY_LINE", DbType.AnsiString, partsBoxInfo.AssemblyLine);
            db.AddInParameter(cmd, "@INHOUSE_SYSTEM_MODE", DbType.AnsiString, partsBoxInfo.PullMode.ToString());
            db.AddInParameter(cmd, "@INHOUSE_PART_CLASS", DbType.AnsiString, partsBoxInfo.BoxParts);
            return(db.ExecuteNonQuery(cmd) > 0 ? true : false);
        }
Beispiel #2
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="fields"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool UpdateInfo(string fields, long id)
        {
            int cnt = dal.GetCounts("[ID] = " + id + " and [STATUS] = " + (int)BasicDataStatusConstants.Disabled + "");

            if (cnt > 0)
            {
                throw new Exception("MC:0x00000742");///已作废的零件类不能进行修改
            }
            ///来源存储区⑧与目标存储区⑩不能为同一存储区
            string sourceZoneNo = CommonBLL.GetFieldValue(fields, "SOURCE_ZONE_NO");
            string targetZoneNo = CommonBLL.GetFieldValue(fields, "TARGET_ZONE_NO");

            if (sourceZoneNo == targetZoneNo)
            {
                throw new Exception("MC:0x00000402");///来源存储区与目标存储区不可一致,请重新选取
            }
            string       partBoxCode  = CommonBLL.GetFieldValue(fields, "PART_BOX_CODE");
            string       plant        = CommonBLL.GetFieldValue(fields, "PLANT");
            string       workshop     = CommonBLL.GetFieldValue(fields, "WORKSHOP");
            string       assemblyLine = CommonBLL.GetFieldValue(fields, "ASSEMBLY_LINE");
            PartsBoxInfo partsBoxInfo = new PartsBoxInfo();

            partsBoxInfo.PullMode     = (int)PullModeConstants.Plan;
            partsBoxInfo.BoxParts     = partBoxCode;
            partsBoxInfo.Plant        = plant;
            partsBoxInfo.Workshop     = workshop;
            partsBoxInfo.AssemblyLine = assemblyLine;
            using (var trans = new TransactionScope())
            {
                new MaintainInhouseLogisticStandardDAL().UpdatePartsBoxInfo(partsBoxInfo);
                if (dal.UpdateInfo(fields, id) == 0)
                {
                    return(false);
                }
                trans.Complete();
            }
            return(true);
        }
Beispiel #3
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));
        }