添加了后端用户关联职业,用户关联供应商,用户关联工序的Excel导入保存功能

zrg

2023-12-29 dbf3e7077ff5af84e27b6b94705484d4c1567058

添加了后端用户关联职业,用户关联供应商,用户关联工序的Excel导入保存功能

 WebAPI/Controllers/BLL/Xt_UserController.cs

@@ -444,7 +444,7 @@
                }

                ////Excel模板缺少列 但需要从数据库中查询出来显示在页面的字段
                tb2.Columns.Add("HItemId", typeof(Int32));//供应商ID
                tb2.Columns.Add("HSupID", typeof(Int32));//供应商ID

                //添加数据
                for (int i = 1; i < ExcelDs.Tables[0].Rows.Count; i++)
@@ -513,10 +513,10 @@
                        }
                        else
                        {
                            tb2.Rows[i]["HItemID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); //供应商ID
                            tb2.Rows[i]["HSupID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); //供应商ID
                        }
                        //查询用户
                        ds = oCN.RunProcReturn("select * from h_v_Gy_UserList where  czybm='" + czybm + "'", "h_v_Gy_UserList");
                        ds = oCN.RunProcReturn("select * from Gy_Czygl where  czybm='" + czybm + "'", "Gy_Czygl");

                        if (ds.Tables[0].Rows.Count == 0)
                        {
@@ -528,7 +528,7 @@
                        }
                        else
                        {
                            tb2.Rows[i]["HItemID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
                            //tb2.Rows[i]["czybm"] = ds.Tables[0].Rows[0]["czybm"].ToString();
                        }

                        //审核代码是否合理
@@ -569,7 +569,7 @@
        }
  

        #region 提料计划信息 导入(保存)
        #region  导入(保存)
        [Route("Xt_User/UserSupperBill_btnSave")]
        [HttpPost]
        public object UserSupperBill_btnSave([FromBody] JObject sMainSub)
@@ -599,49 +599,27 @@
                int i = 1;
                foreach (Dictionary<string, string> item in list)
                {
                    string HSupID = item["HSupID"].ToString();//供应商
                    string HMaterID = item["HMaterID"].ToString();//物料ID
                    string HOrgID = item["HOrgID"].ToString();//组织ID
                    string HSupplierName = item["供应商"].ToString();
                    string HMaterName = item["物料名称"].ToString();
                    string HMaterModel = item["规格型号"].ToString();
                    string HUnitID = item["HUnitID"].ToString();//计量单位
                    string JITType = item["JIT物料分类"].ToString();
                    string JITPSL = item["JIT拉动安全库存量"].ToString();
                    string JITMDSC = item["JIT物料需求供货周期"].ToString();
                    string JIPEB = item["JIT提货经济批量"].ToString();
                    string JITPBCC = item["JIT提货批量对应周期（天）"].ToString();
                    string JISLT = item["JIT供货提前期（天）"].ToString();
                    string JIDLT = item["JIT到货前置期（天）"].ToString();
                    string JITDCD = item["JIT送货控制天数"].ToString();
                    string JIMLC = item["JIT最小起送量"].ToString();
                    string HRemark = item["备注"].ToString();
                    string HSupID = item["HSupID"].ToString();//供应商ID
                    string HUserID = item["用户代码"].ToString();//用户代码
                    string czymc = item["用户名称"].ToString();//用户名称
                    string HNumber = item["供应商代码"].ToString();//供应商代码
                    string HName = item["供应商名称"].ToString();//供应商名称

                    ds = oCN.RunProcReturn("select * from Cg_PODemandPlanConfigBillSub where HMaterID=" + HMaterID + " and HSupplierID=" + HSupID, "Cg_PODemandPlanConfigBillSub");
                    var ds = oCN.RunProcReturn("select * from Gy_UserSupplierRelation where  HSupID='" + HSupID + "'and HUserID='" + HUserID + "'", "Gy_UserSupplierRelation");

                    if (ds.Tables[0].Rows.Count == 0)

                    if (ds.Tables[0].Rows.Count >0)
                    {

                        long HInterID = DBUtility.ClsPub.CreateBillID_Prod("4611", ref DBUtility.ClsPub.sExeReturnInfo);//获得一个新的id
                        string HBillNo = DBUtility.ClsPub.CreateBillCode_Prod("4611", ref DBUtility.ClsPub.sExeReturnInfo, true);//获得一个新的单据号


                        string sql = "insert into Cg_PODemandPlanConfigBillMain(HInterID,HBillNo,HYear,HPeriod,HBillType,HBillSubType,HDate,HBillStatus,HMaker,HMakeDate,HOrgId)" +
                            $"values({HInterID}, '{HBillNo}',{DateTime.Now.Year}, {DateTime.Now.Month}, '4611', '4611', GETDATE(), 1, '{user}', GETDATE(), {HOrgID})";
                        string sql1 = "insert into Cg_PODemandPlanConfigBillSub(HInterID,HEntryID,HRemark,HSupplierID,HSupplierName,HMaterID," +
                       "HMaterName, HMaterModel, HJITMaterGroup, HJITSafeStock, HJITMaterDemand, HJITBatchQty, HDeliveryPeriod, " +
                       "HLeadTime, HLeadTime_Sec, HDeliveryMode, HDeliveryDaysCtrl,HUnitID,HJITMinDeliveryQty)values" +
                       $"({HInterID}, 1, '{HRemark}', {(HSupID == "" ? "0" : HSupID)}, '{HSupplierName}', {(HMaterID == "" ? "0" : HMaterID)}," +
                       $" '{HMaterName}', '{HMaterModel}', '{JITType}', {(JITPSL == "" ? "0" : JITPSL)}, {(JITMDSC == "" ? "0" : JITMDSC)},{(JIPEB == "" ? "0" : JIPEB)},{(JITPBCC == "" ? "0" : JITPBCC)}," +
                       $" {(JISLT == "" ? "0" : JISLT)}, {(JIDLT == "" ? "0" : JIDLT)}, '', {(JITDCD == "" ? "0" : JITDCD)} ,{HUnitID},{JIMLC})";
                        //主表
                        oCN.RunProc(sql);
                        //子表
                        oCN.RunProc(sql1);
                        oCN.RollBack();
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "第" + i + "行, 用户: " + czymc + " 与 供应商: "+ HName + ",已存在,或,插入表格有重复信息";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                    else
                    {
                        oCN.RunProc("    update  Cg_PODemandPlanConfigBillSub set HLeadTime=" + JISLT + "  where HMaterID=" + HMaterID + " and HSupplierID=" + HSupID);
                        oCN.RunProc("insert into Gy_UserSupplierRelation (HSupID,HUserID) values ('" + HSupID + "','" + HUserID + "')");
                    }

                    i++;
@@ -1197,7 +1175,7 @@
        }
        #endregion

        #region 用户关联职员保存
        #region 用户关联职员保存 Excel导入
        [Route("Xt_User/SaveUserEmployee")]
        [HttpPost]
        public object SaveUserEmployee([FromBody] JObject msg)
@@ -1237,6 +1215,243 @@
                objJsonResult.count = 0;
                objJsonResult.Message = "保存失败！" + e.ToString();
                objJsonResult.data = 1;
                return objJsonResult;
            }
        }

        /// <summary>
        /// 文件上传解析回显
        /// </summary>
        /// <returns></returns>
        [Route("Xt_User/UserEmployeeBill_Excel")]
        [HttpPost]
        public object UserEmployeeBill_Excel()
        {
            try
            {
                //获取文件名称
                var file = HttpContext.Current.Request.Files[0];
                //获取文件物理路径
                string ExcelPath = HttpContext.Current.Server.MapPath("~/" + file.FileName);
                //保存文件
                file.SaveAs(ExcelPath);

                NpoiHelper np = new NpoiHelper();
                DataSet ExcelDs = np.ReadExcel(ExcelPath, 1, 1, "0");

                //删除文件
                File.Delete(ExcelPath);

                //创建临时表
                DataTable tb2 = new DataTable("dt2");

                //添加列名
                for (int i = 0; i < ExcelDs.Tables[0].Columns.Count; i++)
                {
                    tb2.Columns.Add(ExcelDs.Tables[0].Rows[0][i].ToString());
                }

                ////Excel模板缺少列 但需要从数据库中查询出来显示在页面的字段
                tb2.Columns.Add("HEmployeeID", typeof(Int32));//工序ID

                //添加数据
                for (int i = 1; i < ExcelDs.Tables[0].Rows.Count; i++)
                {
                    DataRow row = tb2.NewRow();
                    for (int j = 0; j < ExcelDs.Tables[0].Columns.Count; j++)
                    {
                        row[j] = ExcelDs.Tables[0].Rows[i][j].ToString();
                    }
                    tb2.Rows.Add(row);
                }


                var error = "";

                ////查询用户关联供应商没有的列
                if (!tb2.Columns.Contains("用户代码"))
                    error += "没有找到【用户代码】的标题,";

                if (!tb2.Columns.Contains("用户名称"))
                    error += "没有找到【用户名称】的标题,";

                if (!tb2.Columns.Contains("职员代码"))
                    error += "没有找到【职员代码】的标题,";

                if (!tb2.Columns.Contains("职员名称"))
                    error += "没有找到【职员名称】的标题,";

                if (error.Length > 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = $"Excel模板存在错误,{error}\r\n";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                for (int i = 0; i <= tb2.Rows.Count - 1; i++)
                {
                    string czybm = "";
                    string czymc = "";
                    string HNumber = "";
                    string HName = "";

                    //HORGNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织编码"].ToString());
                    //HORGName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织"].ToString());
                    czybm = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["用户代码"].ToString());
                    czymc = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["用户名称"].ToString());
                    HNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["职员代码"].ToString());
                    HName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["职员名称"].ToString());

                    int index = i + 1;
                    //检查用户与工序代码是否都为空
                    if (czybm != "" || HNumber != "")
                    {
                        //查询工序
                        ds = oCN.RunProcReturn("select * from Gy_Employee where  HNumber='" + HNumber + "'", "Gy_Employee");

                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "第" + index + "行,职员名称:" + HName + ",不存在！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                        else
                        {
                            tb2.Rows[i]["HEmployeeID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); //职员ID
                        }
                        //查询用户
                        ds = oCN.RunProcReturn("select * from Gy_Czygl where  czybm='" + czybm + "'", "Gy_Czygl");

                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "第" + index + "行,用户: " + czymc + ",不存在！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                        else
                        {
                            //tb2.Rows[i]["czybm"] = ds.Tables[0].Rows[0]["czybm"].ToString();
                        }

                        //审核代码是否合理
                        if (!DBUtility.ClsPub.AllowNumber(czybm))
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "第" + index + "行,用户代码中不能出现连续‘.’并且首位末位不能为‘.’！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }

                    }
                    else
                    {
                        objJsonResult.code = "1";
                        objJsonResult.count = 1;
                        objJsonResult.Message = "第" + index + "行,用户代码为空";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = error;
                objJsonResult.data = tb2;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }


        /// <summary>
        /// 导入保存
        /// </summary>
        /// <param name="sMainSub"></param>
        /// <returns></returns>
        [Route("Xt_User/UserEmployeeBill_btnSave")]
        [HttpPost]
        public object UserEmployeeBill_btnSave([FromBody] JObject sMainSub)
        {
            var _value = sMainSub["sMainSub"].ToString();
            string msg1 = _value.ToString();
            string[] sArray = msg1.Split(new string[] { "&和" }, StringSplitOptions.RemoveEmptyEntries);
            string msg2 = sArray[0].ToString();
            string user = sArray[1].ToString();
            try
            {

                List<object> Excel = Newtonsoft.Json.JsonConvert.DeserializeObject<List<object>>(msg2);
                List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();

                foreach (JObject item in Excel)
                {
                    Dictionary<string, string> dic = new Dictionary<string, string>();
                    foreach (var itm in item.Properties())
                    {
                        dic.Add(itm.Name, itm.Value.ToString());
                    }
                    list.Add(dic);
                }

                oCN.BeginTran();
                int i = 1;
                foreach (Dictionary<string, string> item in list)
                {
                    string HEmployeeID = item["HEmployeeID"].ToString();//职员ID
                    string HUserID = item["用户代码"].ToString();//用户代码
                    string czymc = item["用户名称"].ToString();//用户名称
                    string HNumber = item["职员代码"].ToString();//职员代码
                    string HName = item["职员名称"].ToString();//职员名称

                    var ds = oCN.RunProcReturn("select * from Gy_UserEmployeeRelation where  HEmployeeID='" + HEmployeeID + "'and HUserID='" + HUserID + "'", "Gy_UserEmployeeRelation");


                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        oCN.RollBack();
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "第" + i + "行, 用户: " + czymc + " 与 工序名: " + HName + ",已存在,或,插入表格有重复信息";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                    else
                    {
                        oCN.RunProc("insert into Gy_UserEmployeeRelation (HEmployeeID,HUserID) values ('" + HEmployeeID + "','" + HUserID + "')");
                    }

                    i++;
                }

                oCN.Commit();

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "导入成功!";
                objJsonResult.data = null;
                return objJsonResult;
            }
            catch (Exception e)
            {
                LogService.Write(e);
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
@@ -1600,7 +1815,7 @@
        }
        #endregion

        #region 用户关联工序保存
        #region 用户关联工序保存  Excel导入
        [Route("Xt_User/SaveUserProcess")]
        [HttpPost]
        public object SaveUserProcess([FromBody] JObject msg)
@@ -1644,6 +1859,243 @@
                return objJsonResult;
            }
        }

        /// <summary>
        /// 文件上传解析回显
        /// </summary>
        /// <returns></returns>
        [Route("Xt_User/UserProcessBill_Excel")]
        [HttpPost]
        public object UserProcessBill_Excel()
        {
            try
            {
                //获取文件名称
                var file = HttpContext.Current.Request.Files[0];
                //获取文件物理路径
                string ExcelPath = HttpContext.Current.Server.MapPath("~/" + file.FileName);
                //保存文件
                file.SaveAs(ExcelPath);

                NpoiHelper np = new NpoiHelper();
                DataSet ExcelDs = np.ReadExcel(ExcelPath, 1, 1, "0");

                //删除文件
                File.Delete(ExcelPath);

                //创建临时表
                DataTable tb2 = new DataTable("dt2");

                //添加列名
                for (int i = 0; i < ExcelDs.Tables[0].Columns.Count; i++)
                {
                    tb2.Columns.Add(ExcelDs.Tables[0].Rows[0][i].ToString());
                }

                ////Excel模板缺少列 但需要从数据库中查询出来显示在页面的字段
                tb2.Columns.Add("HProcID", typeof(Int32));//工序ID

                //添加数据
                for (int i = 1; i < ExcelDs.Tables[0].Rows.Count; i++)
                {
                    DataRow row = tb2.NewRow();
                    for (int j = 0; j < ExcelDs.Tables[0].Columns.Count; j++)
                    {
                        row[j] = ExcelDs.Tables[0].Rows[i][j].ToString();
                    }
                    tb2.Rows.Add(row);
                }


                var error = "";

                ////查询用户关联供应商没有的列
                if (!tb2.Columns.Contains("用户代码"))
                    error += "没有找到【用户代码】的标题,";

                if (!tb2.Columns.Contains("用户名称"))
                    error += "没有找到【用户名称】的标题,";

                if (!tb2.Columns.Contains("工序代码"))
                    error += "没有找到【工序代码】的标题,";

                if (!tb2.Columns.Contains("工序名称"))
                    error += "没有找到【工序名称】的标题,";

                if (error.Length > 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = $"Excel模板存在错误,{error}\r\n";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                for (int i = 0; i <= tb2.Rows.Count - 1; i++)
                {
                    string czybm = "";
                    string czymc = "";
                    string HNumber = "";
                    string HName = "";

                    //HORGNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织编码"].ToString());
                    //HORGName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织"].ToString());
                    czybm = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["用户代码"].ToString());
                    czymc = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["用户名称"].ToString());
                    HNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["工序代码"].ToString());
                    HName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["工序名称"].ToString());

                    int index = i + 1;
                    //检查用户与工序代码是否都为空
                    if (czybm != "" || HNumber != "")
                    {
                        //查询工序
                        ds = oCN.RunProcReturn("select * from Gy_Process where  HNumber='" + HNumber + "'", "Gy_Process");

                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "第" + index + "行,工序名称:" + HName + ",不存在！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                        else
                        {
                            tb2.Rows[i]["HProcID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); //工序ID
                        }
                        //查询用户
                        ds = oCN.RunProcReturn("select * from Gy_Czygl where  czybm='" + czybm + "'", "Gy_Czygl");

                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "第" + index + "行,用户: " + czymc + ",不存在！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                        else
                        {
                            //tb2.Rows[i]["czybm"] = ds.Tables[0].Rows[0]["czybm"].ToString();
                        }

                        //审核代码是否合理
                        if (!DBUtility.ClsPub.AllowNumber(czybm))
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "第" + index + "行,用户代码中不能出现连续‘.’并且首位末位不能为‘.’！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }

                    }
                    else
                    {
                        objJsonResult.code = "1";
                        objJsonResult.count = 1;
                        objJsonResult.Message = "第" + index + "行,用户代码为空";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = error;
                objJsonResult.data = tb2;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }


        /// <summary>
        /// 导入保存
        /// </summary>
        /// <param name="sMainSub"></param>
        /// <returns></returns>
        [Route("Xt_User/UserProcessBill_btnSave")]
        [HttpPost]
        public object UserProcessBill_btnSave([FromBody] JObject sMainSub)
        {
            var _value = sMainSub["sMainSub"].ToString();
            string msg1 = _value.ToString();
            string[] sArray = msg1.Split(new string[] { "&和" }, StringSplitOptions.RemoveEmptyEntries);
            string msg2 = sArray[0].ToString();
            string user = sArray[1].ToString();
            try
            {

                List<object> Excel = Newtonsoft.Json.JsonConvert.DeserializeObject<List<object>>(msg2);
                List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();

                foreach (JObject item in Excel)
                {
                    Dictionary<string, string> dic = new Dictionary<string, string>();
                    foreach (var itm in item.Properties())
                    {
                        dic.Add(itm.Name, itm.Value.ToString());
                    }
                    list.Add(dic);
                }

                oCN.BeginTran();
                int i = 1;
                foreach (Dictionary<string, string> item in list)
                {
                    string HProcID = item["HProcID"].ToString();//工序ID
                    string HUserID = item["用户代码"].ToString();//用户代码
                    string czymc = item["用户名称"].ToString();//用户名称
                    string HNumber = item["工序代码"].ToString();//供应商代码
                    string HName = item["工序名称"].ToString();//供应商名称

                    var ds = oCN.RunProcReturn("select * from Gy_UserByProcess where  HProcID='" + HProcID + "'and HUserID='" + HUserID + "'", "Gy_UserByProcess");


                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        oCN.RollBack();
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "第" + i + "行, 用户: " + czymc + " 与 工序名: " + HName + ",已存在,或,插入表格有重复信息";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                    else
                    {
                        oCN.RunProc("insert into Gy_UserByProcess (HProcID,HUserID) values ('" + HProcID + "','" + HUserID + "')");
                    }

                    i++;
                }

                oCN.Commit();

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "导入成功!";
                objJsonResult.data = null;
                return objJsonResult;
            }
            catch (Exception e)
            {
                LogService.Write(e);
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
    }
}