using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Pub_Class;
using System;
using System.Collections;
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
{
    public class Xt_UserController : ApiController
    {
        public DBUtility.ClsPub.Enum_BillStatus BillStatus;

        private json objJsonResult = new json();
        SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
        DataSet ds;


        #region[用户列表查询]
        [Route("Xt_User/list")]
        [HttpGet]
        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");
                }
                else
                {
                    string sql1 = "select * from h_v_IF_UserList where 1 = 1 ";
                    string sql = sql1 + sWhere;
                    ds = oCN.RunProcReturn(sql, "h_v_IF_UserList");
                }

                //if (ds.Tables[0].Rows.Count != 0 || ds != null)
                //{
                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = ds.Tables[0];
                return objJsonResult;
                //}
                //else
                //{
                //objJsonResult.code = "0";
                //objJsonResult.count = 0;
                //objJsonResult.Message = "无数据";
                //objJsonResult.data = null;
                //return objJsonResult;
                //}
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region [用户列表删除功能]
        /// <summary>
        /// 用户信息删除功能
        /// </summary>
        /// <returns></returns>
        [Route("Xt_User/DeltetUser")]
        [HttpGet]
        public object DeltetUser(string HCzybm)
        {
            try
            {
                //编辑权限
                //if (!DBUtility.ClsPub.Security_Log("Gy_UserByUserRelation_Delete", 1, false, DBUtility.ClsPub.CurUserName))
                //{
                //    objJsonResult.code = "0";
                //    objJsonResult.count = 0;
                //    objJsonResult.Message = "无删除权限！";
                //    objJsonResult.data = null;
                //    return objJsonResult;
                //}

                string lngBillKey = "";
                lngBillKey = DBUtility.ClsPub.isStrNull(HCzybm);
                if (lngBillKey == "")
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "用户编码为空！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                oCN.BeginTran();
                //删除用户信息
                oCN.RunProc("delete from  Gy_Czygl where Czybm='" + HCzybm + "'");
                //删除用户对应的角色信息
                oCN.RunProc("delete from  System_UserGroupInfo where UserId='" + HCzybm + "'");
                DBUtility.ClsPub.Add_Log(DBUtility.ClsPub.CurUserName, "删除用户:" + HCzybm, DBUtility.ClsPub.CurUserName);
                oCN.Commit();

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message ="删除信息成功!";
                objJsonResult.data = null;
                
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = e.Message;
                objJsonResult.data = null;
            }
            return objJsonResult;
        }

        #endregion

        #region[用户列表编辑时获取表头数据]
        [Route("Xt_User/EditUser")]
        [HttpGet]
        public ApiResult<DataSet> EditUser(string HID)
        {
            if (string.IsNullOrEmpty(HID))
                return new ApiResult<DataSet> { code = -1, msg = "ID不能为空" };
            SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();

            var dataSet = oCN.RunProcReturn("select top 1 * from h_v_IF_UserList_Edit  where 用户编码='" + HID + "' ", "h_v_IF_UserList_Edit");
            if (dataSet == null || dataSet.Tables[0].Rows.Count == 0)
                return new ApiResult<DataSet> { code = -1, msg = "不存在此用户" };

            return new ApiResult<DataSet> { code = 1, msg = "查询成功", data = dataSet };
        }
        #endregion

        #region 用户信息保存
        /// <summary>
        /// 
        /// </summary>
        /// <param name="msg"></param>
        /// <returns></returns>
        [Route("Xt_User/SaveUser")]
        [HttpPost]
        public object SaveUser([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();
            string msg4 = sArray[2].ToString();

            ListModels oListModels = new ListModels();
            try
            {
                List<Models.Gy_User> lsmain = new List<Models.Gy_User>();
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_Gy_Gy_User(msg2);

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

                //保存
                if (msg3=="Add") //新增
                {
                    ds = oCN.RunProcReturn("select * from  Gy_Czygl where Czybm='" + lsmain[0].Czybm.ToString() + "'", "Gy_Czygl");
                    if (ds.Tables[0].Rows.Count != 0)
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "* 编码重复！！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    ds = oCN.RunProcReturn("select * from  Gy_Czygl where Czymc='" + lsmain[0].Czymc.ToString() + "'", "Gy_Czygl");
                    if (ds.Tables[0].Rows.Count != 0)
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "* 用户名重复！！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    oCN.RunProc("insert into Gy_Czygl (Czybm,Czymc,Czmm,Explain,HempID,HK3UserID " +
                    ",HKeeperID,HKeeper,HSecManagerID,HSecManager " +
                    ",HSellManID,HSellMan,HDeptID,HDept " +
                    ",HWhID,HWHName,HSupID,HSupName " +
                    ",HSCWHID,HSCWHName " +
                    ",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].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 //修改
                {
                    ds = oCN.RunProcReturn("select * from  Gy_Czygl where Czybm='" + lsmain[0].Czybm.ToString() + "'", "Gy_Czygl");
                    if (ds.Tables[0].Rows[0]["Czmm"].ToString() !=  lsmain[0].Czmm)  //修改密码
                    {
                        oCN.RunProc("update Gy_Czygl set Czymc='" + lsmain[0].Czymc.ToString() +
                        "',Czmm='" + ClsPub.StrToPsd(lsmain[0].Czmm.ToString()) +
                        "',Explain='" + lsmain[0].Explain.ToString() + "'" +
                        ",HEmpID=" + lsmain[0].HEmpID.ToString() +
                        ",HK3UserID=" + lsmain[0].HK3UserID.ToString() +
                        ",HKeeperID=" + lsmain[0].HKeeperID.ToString() +
                        ",HKeeper='" + lsmain[0].HKeeper.ToString() + "'" +
                        ",HSecManagerID=" + lsmain[0].HSecManagerID.ToString() +
                        ",HSecManager='" + lsmain[0].HSecManager.ToString() + "'" +
                        ",HSellManID=" + lsmain[0].HSellManID.ToString() +
                        ",HSellMan='" + lsmain[0].HSellMan.ToString() + "'" +
                        ",HDeptID=" + lsmain[0].HDeptID.ToString() +
                        ",HDept='" + lsmain[0].HDept.ToString() + "'" +
                        ",HWhID=" + lsmain[0].HWhID.ToString() +
                        ",HWHName='" + lsmain[0].HWHName.ToString() + "'" +
                        ",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() + "'");
                    }
                    else
                    {
                        oCN.RunProc("update Gy_Czygl set Czymc='" + lsmain[0].Czymc.ToString() +
                           "',Explain='" + lsmain[0].Explain.ToString() + "'" +
                           ",HEmpID=" + lsmain[0].HEmpID.ToString() +
                           ",HK3UserID=" + lsmain[0].HK3UserID.ToString() +
                           ",HKeeperID=" + lsmain[0].HKeeperID.ToString() +
                           ",HKeeper='" + lsmain[0].HKeeper.ToString() + "'" +
                           ",HSecManagerID=" + lsmain[0].HSecManagerID.ToString() +
                           ",HSecManager='" + lsmain[0].HSecManager.ToString() + "'" +
                           ",HSellManID=" + lsmain[0].HSellManID.ToString() +
                           ",HSellMan='" + lsmain[0].HSellMan.ToString() + "'" +
                           ",HDeptID=" + lsmain[0].HDeptID.ToString() +
                           ",HDept='" + lsmain[0].HDept.ToString() + "'" +
                           ",HWhID=" + lsmain[0].HWhID.ToString() +
                           ",HWHName='" + lsmain[0].HWHName.ToString() + "'" +
                           ",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() + "')");
                        }
                    }

                }

                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;
            }
        }
        #endregion



        #region  根据用户编码查找已分配供应商列表
        [Route("Xt_User/SupperPlaylist")]
        [HttpGet]
        public object SupperPlaylist(string HUserID)
        {
            try
            {
               
                ds = oCN.RunProcReturn("select HSupID,供应商代码 HSupperNumber,供应商名称 HSupperName from h_v_Gy_UserSupplierList where HUserID='" + HUserID + "' ", "h_v_Gy_UserSupplierList");
                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/SaveUserSupper")]
        [HttpPost]
        public object SaveUserSupper([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_UserSupper> lsmain = new List<Models.Gy_UserSupper>();
                msg2 = msg2.Substring(1, msg2.Length - 2);
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_Gy_UserSupper(msg2);
                oCN.BeginTran();
                //删除已经关联的数据
                oCN.RunProc("Delete From Gy_UserSupplierRelation  where HUserID='" + msg3.ToString() + "'");
                foreach (Models.Gy_UserSupper oItem in lsmain)
                {
                    //重新写入关联数据
                    oCN.RunProc("insert into Gy_UserSupplierRelation (HSupID,HUserID) values ('" + oItem.HSupID + "','" + 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;
            }
        }

        /// 文件上传 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  根据用户编码查找已分配组织列表
        [Route("Xt_User/UserByOrgPlaylist")]
        [HttpGet]
        public object UserByOrgPlaylist(string HUserID)
        {
            try
            {

                ds = oCN.RunProcReturn("select HOrgID HItemID,对应组织代码 HNumber,对应组织名称 HName from h_v_Gy_UserByOrgRelationList where 用户编码='" + HUserID + "' ", "h_v_Gy_UserByOrgRelationList");
                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/SaveUserByOrg")]
        [HttpPost]
        public object SaveUserByOrg([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.Xt_ORGANIZATIONS> lsmain = new List<Models.Xt_ORGANIZATIONS>();
                msg2 = msg2.Substring(1, msg2.Length - 2);
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_Xt_ORGANIZATIONS(msg2);
                oCN.BeginTran();
                //删除已经关联的数据
                oCN.RunProc("Delete From Gy_UserByOrgRelation  where HUserID='" + msg3.ToString() + "'");
                foreach (Models.Xt_ORGANIZATIONS oItem in lsmain)
                {
                    //重新写入关联数据
                    oCN.RunProc("insert into Gy_UserByOrgRelation (HOrgID,HBillType,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/UserStocklistPlaylist")]
        [HttpGet]
        public object UserStocklistPlaylist(string HUserID)
        {
            try
            {

                ds = oCN.RunProcReturn("select HWHID HItemID,仓库代码 HNumber,仓库名称 HName from h_v_Gy_UserStockRelationList where HUserID='" + HUserID + "' ", "h_v_Gy_UserStockRelationList");
                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/Gy_UserStockRelationList")]
        [HttpGet]
        public object Gy_UserStockRelationList(string sWhere, string user)
        {
            try
            {
                List<object> columnNameList = new List<object>();

                //查看权限
                if (!DBUtility.ClsPub.Security_Log("Gy_UserStockRelation_Query", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无查看权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                string sql1 = "select * from h_v_Gy_UserStockRelation where 1 = 1";
                string sql = sql1 + sWhere + " order by 用户代码 ";
                ds = oCN.RunProcReturn(sql, "h_v_Gy_UserStockRelation");

                //添加列名
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    Type dataType = col.DataType;
                    string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = ds.Tables[0];
                objJsonResult.list = columnNameList;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 用户关联仓库列表 删除
        /// <summary>
        ///参数：string HInterID。
        ///返回值：object。
        /// </summary>
        [Route("Xt_User/Gy_UserStockRelationDrop")]
        [HttpGet]
        public object Gy_UserStockRelationDrop(string HItemID, string user)
        {
            try
            {

                //查看权限
                if (!DBUtility.ClsPub.Security_Log("Gy_UserStockRelation_Drop", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无删除权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                if (HItemID == null || HItemID.Equals(""))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "HItemID不能为空！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                oCN.BeginTran();

                string sql = "delete from Gy_UserStockRelation where HItemID = " + HItemID;
                oCN.RunProc(sql);

                oCN.Commit();

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "删除成功！";
                objJsonResult.data = null;
                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/SaveUserStock")]
        [HttpPost]
        public object SaveUserStock([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.Warehouse> lsmain = new List<Models.Warehouse>();
                msg2 = msg2.Substring(1, msg2.Length - 2);
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_Warehouse(msg2);
                oCN.BeginTran();
                //删除已经关联的数据
                oCN.RunProc("Delete From Gy_UserStockRelation  where HUserID='" + msg3.ToString() + "'");
                foreach (Models.Warehouse oItem in lsmain)
                {
                    //重新写入关联数据
                    oCN.RunProc("insert into Gy_UserStockRelation (HWHID,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/UserGrouplistPlaylist")]
        [HttpGet]
        public object UserGrouplistPlaylist(string HUserID)
        {
            try
            {

                ds = oCN.RunProcReturn("select HGroupID HItemID,班组代码 HNumber,班组名称 HName from h_v_Gy_UserGroupRelationList where HUserID='" + HUserID + "' ", "h_v_Gy_UserGroupRelationList");
                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/SaveUserGroup")]
        [HttpPost]
        public object SaveUserGroup([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_Group> lsmain = new List<Models.Gy_Group>();
                msg2 = msg2.Substring(1, msg2.Length - 2);
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_Gy_Group(msg2);
                oCN.BeginTran();
                //删除已经关联的数据
                oCN.RunProc("Delete From Gy_UserGroupRelation  where HUserID='" + msg3.ToString() + "'");
                foreach (Models.Gy_Group oItem in lsmain)
                {
                    //重新写入关联数据
                    oCN.RunProc("insert into Gy_UserGroupRelation (HGroupID,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/UserDeptlistPlaylist")]
        [HttpGet]
        public object UserDeptlistPlaylist(string HUserID)
        {
            try
            {

                ds = oCN.RunProcReturn("select HDeptID HItemID,部门代码 HNumber,部门名称 HName from h_v_Gy_UserDeptRelationList where HUserID='" + HUserID + "' ", "h_v_Gy_UserDeptRelationList");
                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/SaveUserDept")]
        [HttpPost]
        public object SaveUserDept([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.Department> lsmain = new List<Models.Department>();
                msg2 = msg2.Substring(1, msg2.Length - 2);
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_Department(msg2);
                oCN.BeginTran();
                //删除已经关联的数据
                oCN.RunProc("Delete From Gy_UserDeptRelation  where HUserID='" + msg3.ToString() + "'");
                foreach (Models.Department oItem in lsmain)
                {
                    //重新写入关联数据
                    oCN.RunProc("insert into Gy_UserDeptRelation (HDeptID,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/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]
        public object UserByWorkStationlistPlaylist(string HUserID)
        {
            try
            {

                ds = oCN.RunProcReturn("select HWorkStationID HItemID,工位代码 HNumber,工位名称 HName from h_v_Gy_UserByWorkStationList where HUserID='" + HUserID + "' ", "h_v_Gy_UserByWorkStationList");
                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/SaveUserByWorkStation")]
        [HttpPost]
        public object SaveUserByWorkStation([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.Department> lsmain = new List<Models.Department>();
                msg2 = msg2.Substring(1, msg2.Length - 2);
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_Department(msg2);
                oCN.BeginTran();
                //删除已经关联的数据
                oCN.RunProc("Delete From Gy_UserByWorkStationRelation  where HUserID='" + msg3.ToString() + "'");
                foreach (Models.Department oItem in lsmain)
                {
                    //重新写入关联数据
                    oCN.RunProc("insert into Gy_UserByWorkStationRelation (HWorkStationID,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/UserEmployeelistPlaylist")]
        [HttpGet]
        public object UserEmployeelistPlaylist(string HUserID)
        {
            try
            {

                ds = oCN.RunProcReturn("select HEmployeeID HItemID,职员代码 HNumber,职员名称 HName from h_v_Gy_UserEmployeeRelationList where HUserID='" + HUserID + "' ", "h_v_Gy_UserEmployeeRelationList");
                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/SaveUserEmployee")]
        [HttpPost]
        public object SaveUserEmployee([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.Employee> lsmain = new List<Models.Employee>();
                msg2 = msg2.Substring(1, msg2.Length - 2);
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_Employee(msg2);
                oCN.BeginTran();
                //删除已经关联的数据
                oCN.RunProc("Delete From Gy_UserEmployeeRelation  where HUserID='" + msg3.ToString() + "'");
                foreach (Models.Employee oItem in lsmain)
                {
                    //重新写入关联数据
                    oCN.RunProc("insert into Gy_UserEmployeeRelation (HEmployeeID,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;
            }
        }

        /// <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;
            }
        }
        #endregion

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

                ds = oCN.RunProcReturn("select HUserID2,用户代码 HUserNumber2,用户名称 HUserName2 from h_v_Gy_UserUserList where HUserID='" + HUserID + "' ", "h_v_Gy_UserUserList");
                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/SaveUserUser")]
        [HttpPost]
        public object SaveUserUser([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_UserUser> lsmain = new List<Models.Gy_UserUser>();
                msg2 = msg2.Substring(1, msg2.Length - 2);
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_Gy_UserUser(msg2);
                oCN.BeginTran();
                //删除已经关联的数据
                oCN.RunProc("Delete From Gy_UserByUserRelation  where HUserID='" + msg3.ToString() + "'");
                foreach (Models.Gy_UserUser oItem in lsmain)
                {
                    //重新写入关联数据
                    oCN.RunProc("insert into Gy_UserByUserRelation (HUserID2,HUserID,HBillType,HUseFlag) values ('" + oItem.HUserID2 + "','" + 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;
            }
        }
        #endregion

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

                ds = oCN.RunProcReturn("select HMaterID,物料代码 HMaterNumber,物料名称 HMaterName from h_v_Gy_UserMaterList where HUserID='" + HUserID + "' ", "h_v_Gy_UserMaterList");
                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/Gy_UserMaterList")]
        [HttpGet]
        public object Gy_UserMaterList(string sWhere, string user)
        {
            try
            {
                List<object> columnNameList = new List<object>();

                //查看权限
                if (!DBUtility.ClsPub.Security_Log("Gy_UserMaterList_Query", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无查看权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                string sql1 = "select * from h_v_Gy_UserMater where 1 = 1";
                string sql = sql1 + sWhere + " order by 用户代码 ";
                ds = oCN.RunProcReturn(sql, "h_v_Gy_UserMater");

                //添加列名
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    Type dataType = col.DataType;
                    string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = ds.Tables[0];
                objJsonResult.list = columnNameList;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 用户关联物料列表 删除
        /// <summary>
        ///参数：string HInterID。
        ///返回值：object。
        /// </summary>
        [Route("Xt_User/Gy_UserMaterRelationDrop")]
        [HttpGet]
        public object Gy_UserMaterRelationDrop(string HItemID, string user)
        {
            try
            {
                //查看权限
                if (!DBUtility.ClsPub.Security_Log("Gy_UserMaterList_Drop", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无删除权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                if (HItemID == null || HItemID.Equals(""))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "HItemID不能为空！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                oCN.BeginTran();

                string sql = "delete from Gy_UserMaterRelation where HItemID = " + HItemID;
                oCN.RunProc(sql);

                oCN.Commit();

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "删除成功！";
                objJsonResult.data = null;
                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/SaveUserMater")]
        [HttpPost]
        public object SaveUserMater([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_UserMater> lsmain = new List<Models.Gy_UserMater>();
                msg2 = msg2.Substring(1, msg2.Length - 2);
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_Gy_UserMater(msg2);
                oCN.BeginTran();
                //删除已经关联的数据
                oCN.RunProc("Delete From Gy_UserMaterRelation  where HUserID='" + msg3.ToString() + "'");
                foreach (Models.Gy_UserMater oItem in lsmain)
                {
                    //重新写入关联数据
                    oCN.RunProc("insert into Gy_UserMaterRelation (HMaterID,HUserID) values ('" + oItem.HMaterID + "','" + 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;
            }
        }
        #endregion

        #region  根据用户编码查找已分配客户列表
        [Route("Xt_User/CustomerPlaylist")]
        [HttpGet]
        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;
                }

                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";
                    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/SaveUserCustomer")]
        [HttpPost]
        public object SaveUserCustomer([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();
            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
            {
                List<Models.Gy_UserCustomer> lsmain = new List<Models.Gy_UserCustomer>();
                msg2 = msg2.Substring(1, msg2.Length - 2);
                msg2 = msg2.Replace("\\", "");
                msg2 = msg2.Replace("\n", "");  //\n
                lsmain = oListModels.getObjectByJson_Gy_UserCustomer(msg2);
                oCN.BeginTran();
                //删除已经关联的数据
                oCN.RunProc("Delete From Gy_UserCustomerRelation  where HUserID='" + msg3.ToString() + "'");
                foreach (Models.Gy_UserCustomer oItem in lsmain)
                {
                    //重新写入关联数据
                    oCN.RunProc("insert into Gy_UserCustomerRelation (HCusID,HUserID) values ('" + oItem.HCusID + "','" + 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;
            }
        }
        #endregion

        #region 用户关联客户 文件上传
        [Route("Xt_User/Gy_UserCustomer_ImportByExcel")]
        [HttpPost]
        public object Gy_UserCustomer_ImportByExcel()
        {
            try
            {
                //var WorkBookName = HttpContext.Current.Request["WorkBookName"];
                //DBUtility.ClsPub.HOrgID = long.Parse(HttpContext.Current.Request["HOrgID"]);
                //获取文件名称
                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());
                }

                //模板缺少列 但需要从数据库中查询出来显示在页面的字段
                tb2.Columns.Add("客户内码", typeof(string));//客户内码
                tb2.Columns.Add("判定结果", typeof(string));//判定结果

                //添加数据
                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);
                }

                //查询Excel文件中没有的列
                var error = "";
                if (!tb2.Columns.Contains("用户代码"))
                {
                    error += "没有找到【用户代码】的标题,";
                }
                if (!tb2.Columns.Contains("用户名称"))
                {
                    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 HUserID = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["用户代码"].ToString());
                    string HUserName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["用户名称"].ToString());
                    string HCusNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["客户代码"].ToString());
                    string HCusName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["客户名称"].ToString());
                    string HOrgNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织代码"].ToString());
                    string HOrgName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织名称"].ToString());
                    int index = i + 1;
                    //string sql = "";

                    ////判定用户是否存在并设置数据
                    //sql = "select * from Gy_Czygl where Czybm = '" + HUserID + "'";
                    //ds = oCN.RunProcReturn(sql, "Gy_Czygl");
                    //if (ds.Tables[0].Rows.Count > 0)
                    //{
                    //    tb2.Rows[i]["用户名称"] = ds.Tables[0].Rows[0]["Czymc"];
                    //}
                    //else
                    //{
                    //    sql = "select * from Gy_Czygl where Czymc = '" + HUserName + "'";
                    //    ds = oCN.RunProcReturn(sql, "Gy_Czygl");
                    //    if (ds.Tables[0].Rows.Count > 0)
                    //    {
                    //        tb2.Rows[i]["用户代码"] = ds.Tables[0].Rows[0]["Czybm"];
                    //    }
                    //}

                    ////判定客户是否存在并设置数据
                    //sql = "select c.HItemID 客户内码,c.HNumber 客户代码,c.HName 客户名称,isnull(c.HUSEORGID,0) 组织内码,isnull(o.HNumber,'') 组织代码,isnull(o.Hname,'') 组织名称 from " +
                    //    "Gy_Customer as c " +
                    //    "left join Xt_ORGANIZATIONS o on c.HUSEORGID = o.HItemID " +
                    //    "where " +
                    //        "c.HNumber = '" + HCusNumber + "' " +
                    //        "and isnull(o.HNumber,'')<>'' and isnull(o.HNumber,'') = '" + HOrgNumber + "'";
                    //ds = oCN.RunProcReturn(sql, "Gy_Customer");
                    //if(ds.Tables[0].Rows.Count > 0)
                    //{
                    //    tb2.Rows[i]["客户内码"] = ds.Tables[0].Rows[0]["客户内码"];
                    //    tb2.Rows[i]["客户名称"] = ds.Tables[0].Rows[0]["客户名称"];
                    //    tb2.Rows[i]["组织名称"] = ds.Tables[0].Rows[0]["组织名称"];
                    //}
                    //else
                    //{
                    //    sql = "select c.HItemID 客户内码,c.HNumber 客户代码,c.HName 客户名称,isnull(c.HUSEORGID,0) 组织内码,isnull(o.HNumber,'') 组织代码,isnull(o.Hname,'') 组织名称 from " +
                    //    "Gy_Customer as c " +
                    //    "left join Xt_ORGANIZATIONS o on c.HUSEORGID = o.HItemID " +
                    //    "where " +
                    //        "c.HNumber = '" + HCusNumber + "' " +
                    //        "and isnull(o.HName,'')<>'' and isnull(o.HName,'') = '" + HOrgName + "'";
                    //    ds = oCN.RunProcReturn(sql, "Gy_Customer");
                    //    if (ds.Tables[0].Rows.Count > 0)
                    //    {
                    //        tb2.Rows[i]["客户内码"] = ds.Tables[0].Rows[0]["客户内码"];
                    //        tb2.Rows[i]["客户名称"] = ds.Tables[0].Rows[0]["客户名称"];
                    //        tb2.Rows[i]["组织代码"] = ds.Tables[0].Rows[0]["组织代码"];
                    //    }
                    //}

                    tb2.Rows[i]["判定结果"] = "";
                }

                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;
            }
        }
        #endregion
        #region 用户关联客户 导入(数据验证)
        [Route("Xt_User/Gy_UserCustomer_ImportByExcel_Confirm")]
        [HttpPost]
        public object Gy_UserCustomer_ImportByExcel_Confirm([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();
            string[] importFieldList = sArray[2].ToString().Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
            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);
                }

                //创建临时表并设置列名
                DataTable tb2 = new DataTable("dt2");
                tb2.Columns.Add("用户代码", typeof(string));
                tb2.Columns.Add("用户名称", typeof(string));
                tb2.Columns.Add("客户内码", typeof(string));
                tb2.Columns.Add("客户代码", typeof(string));
                tb2.Columns.Add("客户名称", typeof(string));
                tb2.Columns.Add("组织代码", typeof(string));
                tb2.Columns.Add("组织名称", typeof(string));
                tb2.Columns.Add("判定结果", typeof(string));

                string error = "";
                int i = 1;
                string sql = "";
                foreach (Dictionary<string, string> item in list)
                {
                    //记录判定结果
                    string err = "";

                    DataRow row = tb2.NewRow();
                    tb2.Rows.Add(row);
                    string HUserID = item["用户代码"].ToString();
                    string HUserName = item["用户名称"].ToString();
                    string HCusID = item["客户内码"].ToString();
                    string HCusNumber = item["客户代码"].ToString();
                    string HCusName = item["客户名称"].ToString();
                    string HOrgNumber = item["组织代码"].ToString();
                    string HOrgName = item["组织名称"].ToString();

                    tb2.Rows[i - 1]["用户代码"] = HUserID;
                    tb2.Rows[i - 1]["用户名称"] = HUserName;
                    tb2.Rows[i - 1]["客户内码"] = HCusID;
                    tb2.Rows[i - 1]["客户代码"] = HCusNumber;
                    tb2.Rows[i - 1]["客户名称"] = HCusName;
                    tb2.Rows[i - 1]["组织代码"] = HOrgNumber;
                    tb2.Rows[i - 1]["组织名称"] = HOrgName;
                    tb2.Rows[i - 1]["判定结果"] = "";

                    //根据导入字段获取用户相关数据，并进行校验
                    sql = "";
                    if (importFieldList[0] == "HUserID")
                    {
                        sql = "select * from Gy_Czygl where Czybm = '" + HUserID + "'";
                    }
                    else if(importFieldList[0] == "HUserName")
                    {
                        sql = "select * from Gy_Czygl where Czymc = '" + HUserName + "'";
                    }
                    ds = oCN.RunProcReturn(sql, "Gy_Czygl");
                    if (ds.Tables[0].Rows.Count == 0)
                    {
                        err += "用户不存在；";
                    }
                    else
                    {
                        tb2.Rows[i - 1]["用户代码"] = ds.Tables[0].Rows[0]["Czybm"];
                        tb2.Rows[i - 1]["用户名称"] = ds.Tables[0].Rows[0]["Czymc"];

                        HUserID = ds.Tables[0].Rows[0]["Czybm"].ToString();
                        HUserName = ds.Tables[0].Rows[0]["Czymc"].ToString();
                    }

                    //根据导入字段获取客户相关数据，并进行校验
                    sql = "";
                    if (importFieldList[1] == "HOrgNumber")
                    {
                        sql = "select * from Xt_ORGANIZATIONS where HNumber = '" + HOrgNumber + "'";
                    }
                    else if (importFieldList[1] == "HOrgName")
                    {
                        sql = "select * from Xt_ORGANIZATIONS where HName = '" + HOrgName + "'";
                    }
                    ds = oCN.RunProcReturn(sql, "Xt_ORGANIZATIONS");
                    if (ds.Tables[0].Rows.Count == 0)
                    {
                        err += "组织不存在；";

                        HCusID = "0";
                        tb2.Rows[i - 1]["客户内码"] = "0";
                    }
                    else
                    {
                        tb2.Rows[i - 1]["组织代码"] = ds.Tables[0].Rows[0]["HNumber"];
                        tb2.Rows[i - 1]["组织名称"] = ds.Tables[0].Rows[0]["HName"];

                        sql = "select c.* from " +
                        "Gy_Customer as c " +
                        "left join Xt_ORGANIZATIONS o on c.HUSEORGID = o.HItemID " +
                        "where c.HNumber = '" + HCusNumber + "' " +
                        "and o.HNumber = '" + ds.Tables[0].Rows[0]["HNumber"] + "' " +
                        "and o.HName = '" + ds.Tables[0].Rows[0]["HName"] + "'";
                        ds = oCN.RunProcReturn(sql, "Gy_Customer");
                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            err += "该组织下客户不存在；";
                            HCusID = "0";
                            tb2.Rows[i - 1]["客户内码"] = "0";
                        }
                        else
                        {
                            tb2.Rows[i - 1]["客户内码"] = ds.Tables[0].Rows[0]["HItemID"];
                            tb2.Rows[i - 1]["客户代码"] = ds.Tables[0].Rows[0]["HNumber"];
                            tb2.Rows[i - 1]["客户名称"] = ds.Tables[0].Rows[0]["HName"];

                            HCusID = ds.Tables[0].Rows[0]["HItemID"].ToString();
                        }
                    }

                    //判定该用户与客户是否已经关联
                    sql = "select * from Gy_UserCustomerRelation where HUserID = '" + HUserID + "' and HCusID = " + HCusID;
                    ds = oCN.RunProcReturn(sql, "Gy_UserCustomerRelation");
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        err += "用户已经关联该客户；";
                    }

                    tb2.Rows[i - 1]["判定结果"] = err;
                    error += err;

                    i++;
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = (error.Length>0?"0":"1");
                objJsonResult.data = tb2;
                return objJsonResult;
            }
            catch (Exception e)
            {
                LogService.Write(e);
                oCN.RollBack();
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
        #region 用户关联客户 导入(保存)
        [Route("Xt_User/Gy_UserCustomer_ImportByExcel_Save")]
        [HttpPost]
        public object Gy_UserCustomer_ImportByExcel_Save([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();
            string saveType = sArray[2].ToString();
            try
            {
                if (!DBUtility.ClsPub.Security_Log("Gy_UserRelationCustomer_Edit", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无导入权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                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);
                }

                if(saveType == "1")
                {
                    oCN.BeginTran();
                    string err = "";
                    int i = 1;
                    string sql = "";
                    foreach (Dictionary<string, string> item in list)
                    {
                        string HUserID = item["用户代码"].ToString();//供应商
                        string HUserName = item["用户名称"].ToString();//物料ID
                        string HCusID = item["客户内码"].ToString();//组织ID
                        string HCusNumber = item["客户代码"].ToString();
                        string HCusName = item["客户名称"].ToString();
                        string HOrgNumber = item["组织代码"].ToString();
                        string HOrgName = item["组织名称"].ToString();

                        sql = "select * from Gy_UserCustomerRelation where HUserID = '" + HUserID + "' and HCusID = " + HCusID;
                        ds = oCN.RunProcReturn(sql, "Gy_UserCustomerRelation");
                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            sql = "insert into Gy_UserCustomerRelation(HUserID,HCusID)" +
                                "values(" +
                                "'" + HUserID + "'" +
                                "," + HCusID + "" +
                                ")";
                            oCN.RunProc(sql);
                        }
                        else
                        {
                            
                        }
                        i++;
                    }
                }else if(saveType == "2")
                {
                    oCN.BeginTran();
                    string err = "";
                    int i = 1;
                    string sql = "";
                    foreach (Dictionary<string, string> item in list)
                    {
                        string HUserID = item["用户代码"].ToString();//供应商
                        string HUserName = item["用户名称"].ToString();//物料ID
                        string HCusID = item["客户内码"].ToString();//组织ID
                        string HCusNumber = item["客户代码"].ToString();
                        string HCusName = item["客户名称"].ToString();
                        string HOrgNumber = item["组织代码"].ToString();
                        string HOrgName = item["组织名称"].ToString();

                        sql = "select * from Gy_UserCustomerRelation where HUserID = '" + HUserID + "' and HCusID = " + HCusID;
                        ds = oCN.RunProcReturn(sql, "Gy_UserCustomerRelation");
                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            sql = "insert into Gy_UserCustomerRelation(HUserID,HCusID)" +
                                "values(" +
                                "'" + HUserID + "'" +
                                "," + HCusID + "" +
                                ")";
                            oCN.RunProc(sql);
                        }
                        else
                        {
                            err += "第" + i + "行：用户已经关联该客户!";
                        }
                        i++;
                    }
                    //判断是否存在错误
                    if (err.Length > 0)
                    {
                        oCN.RollBack();
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "导入错误：" + err;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }
                
                oCN.Commit();
                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "导入成功!";
                objJsonResult.data = null;
                return objJsonResult;
            }
            catch (Exception e)
            {
                LogService.Write(e);
                oCN.RollBack();
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 用户关联客户维护列表
        /// <summary>
        ///参数：string sql。
        ///返回值：object。
        /// </summary>
        [Route("Xt_User/UserRelationCustomerlist")]
        [HttpGet]
        public object UserRelationCustomerlist(string sWhere, string user)
        {
            try
            {
                List<object> columnNameList = new List<object>();
                //查看权限
                if (!DBUtility.ClsPub.Security_Log("Gy_UserRelationCustomer_Query", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无查看权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                string sql = "";


                if (sWhere == null || sWhere.Equals(""))
                {
                    sql = "select * from h_v_Gy_UserCustomerRelationList_Query order by 用户代码 asc,客户代码 asc";
                    ds = oCN.RunProcReturn(sql, "Gy_UserCustomerRelation");
                }
                else
                {
                    sql = "select * from h_v_Gy_UserCustomerRelationList_Query where 1=1 ";
                    sql = sql + sWhere + " order by 用户代码 asc,客户代码 asc";
                    ds = oCN.RunProcReturn(sql, "Gy_UserCustomerRelation");
                }

                //添加列名
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    Type dataType = col.DataType;
                    string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = ds.Tables[0];
                objJsonResult.list = columnNameList;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
        #region 用户关联客户维护 删除
        /// <summary>
        ///参数：string HInterID。
        ///返回值：object。
        /// </summary>
        [Route("Xt_User/UserRelationCustomerlist_Drop")]
        [HttpGet]
        public object UserRelationCustomerlist_Drop(string HInterID, string user)
        {
            try
            {
                string s = "";

                //查看权限
                if (!DBUtility.ClsPub.Security_Log("Gy_UserRelationCustomer_Drop", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无删除权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                if (HInterID == null || HInterID.Equals(""))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "HInterID不能为空！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                oCN.BeginTran();

                string sql = "delete from Gy_UserCustomerRelation where HItemID = " + HInterID;
                oCN.RunProc(sql);

                oCN.Commit();

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = null;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
        #region 用户关联客户维护 编辑初始化
        /// <summary>
        ///参数：string sql。
        ///返回值：object。
        /// </summary>
        [Route("Xt_User/UserRelationCustomerlist_EditInit")]
        [HttpGet]
        public object UserRelationCustomerlist_EditInit(int HItemID, string user)
        {
            try
            {
                List<object> columnNameList = new List<object>();
                //查看权限
                if (!DBUtility.ClsPub.Security_Log("Gy_UserRelationCustomer_Query", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无查看权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                string sql = "select * from h_v_Gy_UserCustomerRelationList_Query where HItemID = " + HItemID;
                ds = oCN.RunProcReturn(sql, "h_v_Gy_UserCustomerRelationList_Query");

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = ds.Tables[0];
                objJsonResult.list = columnNameList;
                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/UserRelationCustomerlist_Save")]
        [HttpPost]
        public object UserRelationCustomerlist_Save([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();
            string saveType = sArray[2].ToString();
            try
            {
                if (!DBUtility.ClsPub.Security_Log("Gy_UserRelationCustomer_Edit", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无保存权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                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);
                }

                if (saveType == "1")
                {
                    oCN.BeginTran();
                    string err = "";
                    int i = 1;
                    string sql = "";
                    foreach (Dictionary<string, string> item in list)
                    {
                        string HItemID = item["HItemID"].ToString();        //
                        string HUserID = item["HUserID"].ToString();        //用户代码
                        string HUserName = item["HUserName"].ToString();    //用户名称
                        string HCusID = item["HCusID"].ToString();          //客户内码
                        string HCusNumber = item["HCusNumber"].ToString();  //客户代码
                        string HCusName = item["HCusName"].ToString();      //客户名称


                        sql = "select * from Gy_UserCustomerRelation where HUserID = '" + HUserID + "' and HCusID = " + HCusID;
                        ds = oCN.RunProcReturn(sql, "Gy_UserCustomerRelation");
                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            sql = "insert into Gy_UserCustomerRelation(HUserID,HCusID)" +
                                "values(" +
                                "'" + HUserID + "'" +
                                "," + HCusID + "" +
                                ")";
                            oCN.RunProc(sql);
                        }
                        else
                        {
                            err += "第" + i + "行：用户【" + HUserName + "】已经关联客户【" + HCusName + "】";
                        }
                        i++;
                    }
                    //判断是否存在错误
                    if (err.Length > 0)
                    {
                        oCN.RollBack();
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "保存失败：" + err;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }
                else if (saveType == "3")
                {
                    oCN.BeginTran();
                    string err = "";
                    int i = 1;
                    string sql = "";
                    foreach (Dictionary<string, string> item in list)
                    {
                        string HItemID = item["HItemID"].ToString();        //
                        string HUserID = item["HUserID"].ToString();        //用户代码
                        string HUserName = item["HUserName"].ToString();    //用户名称
                        string HCusID = item["HCusID"].ToString();          //客户内码
                        string HCusNumber = item["HCusNumber"].ToString();  //客户代码
                        string HCusName = item["HCusName"].ToString();      //客户名称

                        sql = "select * from Gy_UserCustomerRelation where HItemID = " + HItemID;
                        ds = oCN.RunProcReturn(sql, "Gy_UserCustomerRelation");
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            sql = "select * from Gy_UserCustomerRelation where HUserID = '" + HUserID + "' and HCusID = " + HCusID + " and HItemID <> " + HItemID;
                            ds = oCN.RunProcReturn(sql, "Gy_UserCustomerRelation");
                            if(ds.Tables[0].Rows.Count == 0)
                            {
                                sql = "update Gy_UserCustomerRelation set " +
                                      "HUserID = '" + HUserID + "' " +
                                      ",HCusID = " + HCusID + " " +
                                      "where HItemID = " + HItemID;
                                oCN.RunProc(sql);
                            }
                            else
                            {
                                err += "第" + i + "行：用户【" + HUserName + "】已经关联客户【" + HCusName + "】";
                            }
                        }
                        else
                        {
                            err += "第" + i + "行：记录不存在!";
                        }
                        i++;
                    }
                    //判断是否存在错误
                    if (err.Length > 0)
                    {
                        oCN.RollBack();
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "保存失败：" + err;
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }

                oCN.Commit();
                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "保存成功!";
                objJsonResult.data = null;
                return objJsonResult;
            }
            catch (Exception e)
            {
                LogService.Write(e);
                oCN.RollBack();
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #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

        #region  用户关联工序列表查询
        [Route("Xt_User/UserProcesslist")]
        [HttpGet]
        public object UserProcesslist(string sWhere, string user)
        {
            try
            {
                List<object> columnNameList = new List<object>();

                //查看权限
                if (!DBUtility.ClsPub.Security_Log("Gy_UserProcessList_Query", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无查看权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                string sql1 = "select * from h_v_Gy_UserProcess where 1 = 1";
                string sql = sql1 + sWhere + " order by 用户代码 ";
                ds = oCN.RunProcReturn(sql, "h_v_Gy_UserProcess");

                //添加列名
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    Type dataType = col.DataType;
                    string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                }

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "Sucess！";
                objJsonResult.data = ds.Tables[0];
                objJsonResult.list = columnNameList;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region 用户关联工序列表 删除
        /// <summary>
        ///参数：string HInterID。
        ///返回值：object。
        /// </summary>
        [Route("Xt_User/Gy_UserProcessDrop")]
        [HttpGet]
        public object Gy_UserProcessDrop(string HItemID, string user)
        {
            try
            {
                //查看权限
                if (!DBUtility.ClsPub.Security_Log("Gy_UserProcessList_Drop", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无删除权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }


                if (HItemID == null || HItemID.Equals(""))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "HInterID不能为空！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                oCN.BeginTran();

                string sql = "delete from Gy_UserByProcess where HItemID = " + HItemID;
                oCN.RunProc(sql);

                oCN.Commit();

                objJsonResult.code = "1";
                objJsonResult.count = 1;
                objJsonResult.Message = "删除成功！";
                objJsonResult.data = null;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion
    }
}