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

zrg

2023-12-29 dbf3e7077ff5af84e27b6b94705484d4c1567058

 WebAPI/Controllers/BLL/Xt_UserController.cs

@@ -6,7 +6,10 @@
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using System.Web.Http;
using WebAPI.Controllers.SCGL.日计划管理;
using WebAPI.Models;
namespace WebAPI.Controllers
{
@@ -22,10 +25,18 @@
        #region[用户列表查询]
        [Route("Xt_User/list")]
        [HttpGet]
        public object list(string sWhere)
        public object list(string sWhere,string user)
        {
            try
            {
                if (!DBUtility.ClsPub.Security_Log_second("Xt_User_Query", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无查看权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                if (sWhere == null || sWhere.Equals(""))
                {
                    ds = oCN.RunProcReturn("select * from h_v_IF_UserList " + sWhere, "h_v_IF_UserList");
@@ -154,6 +165,7 @@
            string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
            string msg2 = sArray[0].ToString();
            string msg3 = sArray[1].ToString();
            string msg4 = sArray[2].ToString();

            ListModels oListModels = new ListModels();
            try
@@ -162,6 +174,8 @@
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_Gy_Gy_User(msg2);

                var num = msg4.Split(',');

                //保存
                if (msg3=="Add") //新增
@@ -191,13 +205,37 @@
                    ",HSellManID,HSellMan,HDeptID,HDept " +
                    ",HWhID,HWHName,HSupID,HSupName " +
                    ",HSCWHID,HSCWHName " +
                    ",HCloudUserName,HCloudUserPsd,HUSEORGID) " +
                    ",HCloudUserName,HCloudUserPsd,HUSEORGID,HProcID" +
                    ",HGroupID,HSourceID) " +
                    "values ('" + lsmain[0].Czybm.ToString() + "','" + lsmain[0].Czymc.ToString() + "','" + ClsPub.StrToPsd(lsmain[0].Czmm.ToString()) + "','" + lsmain[0].Explain.ToString() + "','" + lsmain[0].HEmpID.ToString() + "',"+ lsmain[0].HK3UserID.ToString() + "" +
                    ","+ lsmain[0].HKeeperID.ToString() + ",'"+ lsmain[0].HKeeper.ToString() + "',"+ lsmain[0].HSecManagerID.ToString() + ",'"+ lsmain[0].HSecManager.ToString() + "'" +
                    "," + lsmain[0].HSellManID.ToString() + ",'" + lsmain[0].HSellMan.ToString() + "'," + lsmain[0].HDeptID.ToString() + ",'" + lsmain[0].HDept.ToString() + "'" +
                    "," + lsmain[0].HWhID.ToString() + ",'" + lsmain[0].HWHName.ToString() + "'," + lsmain[0].HSupID.ToString() + ",'" + lsmain[0].HSupName.ToString() + "'" +
                    "," + lsmain[0].HSCWHID.ToString() + ",'" + lsmain[0].HSCWHName.ToString() + "'" + 
                    ",'" + lsmain[0].HCloudUserName.ToString() + "','" + lsmain[0].HCloudUserPsd.ToString() + "',"+ lsmain[0].HOrgID.ToString() + ")");
                    ",'" + lsmain[0].HCloudUserName.ToString() + "','" + lsmain[0].HCloudUserPsd.ToString() + "',"+ lsmain[0].HOrgID.ToString() + "," + lsmain[0].HProcID.ToString() + "" +
                    ","+ lsmain[0].HGroupID.ToString() + ","+ lsmain[0].HSourceID.ToString() + ")");

                    //新增后查询  确保数据添加成功
                    ds = oCN.RunProcReturn("Select * from  Gy_Czygl where Czybm='" + lsmain[0].Czybm.ToString() + "'", "Gy_Czygl");

                    if (ds == null || ds.Tables[0].Rows.Count == 0)
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "角色新增失败！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                 
                    oCN.RunProc("delete from  System_UserGroupInfo where UserId='" + lsmain[0].Czybm.ToString() + "'");

                    if (num[0] != "-1")
                    {
                        for (int i = 0; i < num.Length; i++)
                        {
                            oCN.RunProc("insert into  System_UserGroupInfo (GroupId,UserId) values (" + num[i] + ",'" + lsmain[0].Czybm.ToString() + "')");
                        }
                    }
                }
                else //修改
                {
@@ -222,6 +260,9 @@
                        ",HSCWHID=" + lsmain[0].HSCWHID.ToString() +
                        ",HSCWHName='" + lsmain[0].HSCWHName.ToString() + "'" +
                        ",HSupID=" + lsmain[0].HSupID.ToString() +
                        ",HProcID=" + lsmain[0].HProcID.ToString() +
                        ",HSourceID=" + lsmain[0].HSourceID.ToString() +
                        ",HGroupID=" + lsmain[0].HGroupID.ToString() +
                        ",HSupName='" + lsmain[0].HSupName.ToString() + "'" +
                        ",HCloudUserName='" + lsmain[0].HCloudUserName.ToString() + "'" +
                        ",HCloudUserPsd='" + lsmain[0].HCloudUserPsd.ToString() + "'" +
@@ -247,11 +288,24 @@
                           ",HSCWHID=" + lsmain[0].HSCWHID.ToString() +
                           ",HSCWHName='" + lsmain[0].HSCWHName.ToString() + "'" +
                           ",HSupID=" + lsmain[0].HSupID.ToString() +
                            ",HProcID=" + lsmain[0].HProcID.ToString() +
                           ",HSourceID=" + lsmain[0].HSourceID.ToString() +
                           ",HGroupID=" + lsmain[0].HGroupID.ToString() +
                           ",HSupName='" + lsmain[0].HSupName.ToString() + "'" +
                           ",HCloudUserName='" + lsmain[0].HCloudUserName.ToString() + "'" +
                           ",HCloudUserPsd='" + lsmain[0].HCloudUserPsd.ToString() + "'" +
                           ",HUSEORGID=" + lsmain[0].HOrgID.ToString() +
                           "  where  Czybm='" + lsmain[0].Czybm.ToString() + "'");
                    }

                    oCN.RunProc("delete from  System_UserGroupInfo where UserId='" + lsmain[0].Czybm.ToString()+"'");

                    if (num[0] != "-1")
                    {
                        for (int i = 0; i < num.Length; i++)
                        {
                            oCN.RunProc("insert into  System_UserGroupInfo (GroupId,UserId) values (" + num[i] + ",'" + lsmain[0].Czybm.ToString() + "')");
                        }
                    }

                }
@@ -315,7 +369,7 @@
        }
        #endregion

        #region 用户关联供应商保存
        #region 用户关联供应商保存 Excel导入
        [Route("Xt_User/SaveUserSupper")]
        [HttpPost]
        public object SaveUserSupper([FromBody] JObject msg)
@@ -359,6 +413,237 @@
                return objJsonResult;
            }
        }

        /// 文件上传 Gy_Supplier
        [Route("Xt_User/UserSupperBill_Excel")]
        [HttpPost]
        public object UserSupperBill_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("HSupID", 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_Supplier where  HNumber='" + HNumber + "'", "Gy_Supplier");

                        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]["HSupID"] = 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;
            }
        }
  

        #region  导入(保存)
        [Route("Xt_User/UserSupperBill_btnSave")]
        [HttpPost]
        public object UserSupperBill_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 HSupID = item["HSupID"].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_UserSupplierRelation where  HSupID='" + HSupID + "'and HUserID='" + HUserID + "'", "Gy_UserSupplierRelation");


                    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_UserSupplierRelation (HSupID,HUserID) values ('" + HSupID + "','" + 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
        #endregion

        #region  根据用户编码查找已分配组织列表
@@ -689,6 +974,88 @@
        }
        #endregion

        #region  根据用户编码查找已分配工作中心列表
        [Route("Xt_User/UserWorkCenterlistPlaylist")]
        [HttpGet]
        public object UserWorkCenterlistPlaylist(string HUserID)
        {
            try
            {

                ds = oCN.RunProcReturn("select HWorkCenterID HItemID,工作中心代码 HNumber,工作中心名称 HName from h_v_Gy_UserWorkCenterRelationList where HUserID='" + HUserID + "' ", "h_v_Gy_UserWorkCenterRelationList");
                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无工作中心信息！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                else
                {
                    objJsonResult.code = "1";
                    objJsonResult.count = 1;
                    objJsonResult.Message = "Sucess！";
                    objJsonResult.data = ds.Tables[0];
                    return objJsonResult;
                }
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 用户关联工作中心保存
        [Route("Xt_User/SaveUserWorkCenter")]
        [HttpPost]
        public object SaveUserWorkCenter([FromBody] JObject msg)
        {
            var _value = msg["msg"].ToString();
            string msg1 = _value.ToString();
            string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
            string msg2 = sArray[0].ToString();
            string msg3 = sArray[1].ToString();

            ListModels oListModels = new ListModels();
            try
            {
                List<Models.WorkCenter> lsmain = new List<Models.WorkCenter>();
                msg2 = msg2.Substring(1, msg2.Length - 2);
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_WorkCenter(msg2);
                oCN.BeginTran();
                //删除已经关联的数据
                oCN.RunProc("Delete From Gy_UserWorkCenterRelation  where HUserID='" + msg3.ToString() + "'");
                foreach (Models.WorkCenter oItem in lsmain)
                {
                    //重新写入关联数据
                    oCN.RunProc("insert into Gy_UserWorkCenterRelation (HWorkCenterID,HUserID) values ('" + oItem.HItemID + "','" + msg3.ToString() + "')");
                }
                oCN.Commit();
                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "保存成功！";
                objJsonResult.data = 1;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "保存失败！" + e.ToString();
                objJsonResult.data = 1;
                return objJsonResult;
            }
        }
        #endregion

        #region  根据用户编码查找已分配工位列表
        [Route("Xt_User/UserByWorkStationlistPlaylist")]
        [HttpGet]
@@ -808,7 +1175,7 @@
        }
        #endregion

        #region 用户关联职员保存
        #region 用户关联职员保存 Excel导入
        [Route("Xt_User/SaveUserEmployee")]
        [HttpPost]
        public object SaveUserEmployee([FromBody] JObject msg)
@@ -848,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;
            }
        }
@@ -1022,12 +1626,73 @@
        #region  根据用户编码查找已分配客户列表
        [Route("Xt_User/CustomerPlaylist")]
        [HttpGet]
        public object CustomerPlaylist(string HUserID)
        public object CustomerPlaylist(string HUserID, string CurUserName)
        {
            try
            {
                //查询权限
                if (!DBUtility.ClsPub.Security_Log_second("Gy_UserRelationCustomer_Query", 1, false, CurUserName))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无查询权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                ds = oCN.RunProcReturn("select HCusID,客户代码 HCustomerNumber,客户名称 HCustomerName from h_v_Gy_UserCustomerList where HUserID='" + HUserID + "' order by HCusID", "h_v_Gy_UserCustomerList");
                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无客户信息！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                else
                {
                    objJsonResult.code = "1";
                    objJsonResult.count = 1;
                    objJsonResult.Message = "Sucess！";
                    objJsonResult.data = ds.Tables[0];
                    return objJsonResult;
                }
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
        #region  查询指定用户所关联客户中的客户
        [Route("Xt_User/CustomerPlaylistFromUserCustomerList")]
        [HttpGet]
        public object CustomerPlaylistFromUserCustomerList(string HUserID, string HCusNumber, string HCusName, string CurUserName)
        {
            try
            {
                //查询权限
                if (!DBUtility.ClsPub.Security_Log_second("Gy_UserRelationCustomer_Query", 1, false, CurUserName))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无查询权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                ds = oCN.RunProcReturn("select HCusID,客户代码 HCustomerNumber,客户名称 HCustomerName from h_v_Gy_UserCustomerList where HUserID='" + HUserID + "' ", "h_v_Gy_UserCustomerList");
                string sql = "select HCusID,客户代码 HCustomerNumber,客户名称 HCustomerName " +
                    "from h_v_Gy_UserCustomerList " +
                    "where HUserID='" + HUserID + "' " +
                    "and 客户代码 like '%" + HCusNumber + "%' " +
                    "and 客户名称 like '%" + HCusName + "%' " +
                    " order by 客户代码";


                ds = oCN.RunProcReturn(sql, "h_v_Gy_UserCustomerList");
                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
@@ -1066,6 +1731,17 @@
            string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
            string msg2 = sArray[0].ToString();
            string msg3 = sArray[1].ToString();
            string msg4 = sArray[2].ToString();

            //查询权限
            if (!DBUtility.ClsPub.Security_Log_second("Gy_UserRelationCustomer_Edit", 1, false, msg4))
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "无编辑权限！";
                objJsonResult.data = null;
                return objJsonResult;
            }

            ListModels oListModels = new ListModels();
            try
@@ -1102,5 +1778,324 @@
        }
        #endregion

        #region  根据用户编码查找已分配工序列表
        [Route("Xt_User/ProcessPlaylist")]
        [HttpGet]
        public object ProcessPlaylist(string HUserID)
        {
            try
            {

                ds = oCN.RunProcReturn("select HProcID,工序代码 HProcNumber,工序名称 HProcName from h_v_Gy_UserProcessList where HUserID='" + HUserID + "' ", "h_v_Gy_UserProcessList");
                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无工序信息！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                else
                {
                    objJsonResult.code = "1";
                    objJsonResult.count = 1;
                    objJsonResult.Message = "Sucess！";
                    objJsonResult.data = ds.Tables[0];
                    return objJsonResult;
                }
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 用户关联工序保存  Excel导入
        [Route("Xt_User/SaveUserProcess")]
        [HttpPost]
        public object SaveUserProcess([FromBody] JObject msg)
        {
            var _value = msg["msg"].ToString();
            string msg1 = _value.ToString();
            string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
            string msg2 = sArray[0].ToString();
            string msg3 = sArray[1].ToString();

            ListModels oListModels = new ListModels();
            try
            {
                List<Models.Gy_UserProcess> lsmain = new List<Models.Gy_UserProcess>();
                msg2 = msg2.Substring(1, msg2.Length - 2);
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_Gy_UserProcess(msg2);
                oCN.BeginTran();
                //删除已经关联的数据
                oCN.RunProc("Delete From Gy_UserByProcess  where HUserID='" + msg3.ToString() + "'");
                foreach (Models.Gy_UserProcess oItem in lsmain)
                {
                    //重新写入关联数据
                    oCN.RunProc("insert into Gy_UserByProcess (HProcID,HUserID) values ('" + oItem.HProcID + "','" + msg3.ToString() + "')");
                }
                oCN.Commit();
                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "保存成功！";
                //WebAPIController.Add_Log("送货单下推", UserName, "生成送货单");
                objJsonResult.data = 1;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "保存失败！" + e.ToString();
                objJsonResult.data = 1;
                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
    }
}