Merge branch 'master' of http://101.37.171.70:10101/r/MES-WEB-API

yusijie

2024-10-04 799a9c42f11f3f2112775c3714abab965ccce0fd

Merge branch 'master' of http://101.37.171.70:10101/r/MES-WEB-API

 Model/obj/Release/Model.csproj.AssemblyReference.cache

Binary files differ

 SyntacticSugar/constant/EmptyConstant.cs

New file
@@ -0,0 +1,16 @@
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SyntacticSugar.constant
{
    public static class EmptyConstant
    {
        /// <summary>
        /// ç©º
        /// </summary>
        public static int EMPTY = 0;
    }
}

 WebAPI/Controllers/BaseSet/Gy_BadReasonController.cs

@@ -4529,7 +4529,7 @@
                }

                string sql1 = "select * from  h_v_Gy_CusType where 1 = 1";
                string sql = sql1 + sWhere + " order by HNumber ";
                string sql = sql1 + sWhere;
                ds = oCN.RunProcReturn(sql, "h_v_Gy_CusType");

                //添加列名

 WebAPI/Controllers/BaseSet/Gy_CustomerController.cs

@@ -9,6 +9,12 @@
using System.Web.Http;
using WebAPI.Models;
using System.Text.RegularExpressions;
using SyntacticSugar.constant;
using System.Web;
using WebAPI.Controllers.SCGL.日计划管理;
using WebAPI.WebBLL.JCJM.GYZL;
using WebAPI.WebBLL.JCJM.IMPL;
using System.IO;
namespace WebAPI.Controllers
{
    public class Gy_CustomerController : ApiController
@@ -19,9 +25,10 @@

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


        #region è¿”回客户列表
        /// <summary>
        /// è¿”回客户列表
        ///参数：string sql。
@@ -93,6 +100,7 @@
                return objJsonResult;
            }
        }
        #endregion

        #region å®¢æˆ·å­è¡¨[关联用户] æŸ¥è¯¢
        /// <summary>
@@ -137,6 +145,7 @@
        }
        #endregion

        #region å®¢æˆ·åˆ—表修改按钮方法
        /// <summary>
        /// å®¢æˆ·åˆ—表修改按钮方法
        ///参数：string sql。
@@ -174,7 +183,7 @@
                return objJsonResult;
            }
        }

        #endregion

        #region èŽ·å–当前最大的客户代码
        [Route("Gy_Customer/getMaxCusNumber")]
@@ -214,6 +223,7 @@
        }
        #endregion

        #region èŽ·å–最大资料ID参数
        /// <summary>
        /// èŽ·å–最大基础资料ID
        ///参数：string sql。
@@ -242,6 +252,7 @@
                return objJsonResult;
            }
        }
        #endregion

        #region å®¢æˆ·æ ¹æ®åˆ†ç±»èŽ·å–最大客户编码
        [Route("Gy_Customer/getMaxNum")]
@@ -320,7 +331,7 @@
        }
        #endregion


        #region å®¢æˆ·åˆ—表树形结构
        /// <summary>
        /// å®¢æˆ·åˆ—表树形结构
        ///参数：string sql。
@@ -350,7 +361,9 @@
                return objJsonResult;
            }
        }
        #endregion

        #region æ–°å¢žå•æ®
        /// <summary>
        /// æ–°å¢žå•æ®-保存按钮
        ///参数：string sql。
@@ -506,9 +519,14 @@
                return objJsonResult;
            }
        }
        #endregion



        #region ä¿å­˜å®¢æˆ·åˆ—表
        /// <summary>
        /// ä¿å­˜å®¢æˆ·åˆ—表
        /// </summary>
        /// <param name="oMain"></param>
        /// <returns></returns>
        [Route("Gy_Customer/SaveGy_CustomerListApi")]
        [HttpPost]
        public object SaveGy_CustomerListApi([FromBody] JObject oMain)
@@ -632,9 +650,9 @@
                return objJsonResult;
            }
        }
        #endregion



        #region ä¿®æ”¹å•æ®-保存按钮
        /// <summary>
        /// ä¿®æ”¹å•æ®-保存按钮
        ///参数：string sql。
@@ -805,9 +823,9 @@
                return objJsonResult;
            }
        }
        #endregion



        # region å®¢æˆ·åˆ—表删除按钮
        /// <summary>
        /// å®¢æˆ·åˆ—表删除按钮
        ///参数：string sql。
@@ -898,7 +916,7 @@
                return objJsonResult;
            }
        }

        #endregion

        #region å®¢æˆ·å®¡æ ¸ã€åå®¡æ ¸
        /// <summary>
@@ -1090,7 +1108,6 @@
        }
        #endregion


        #region [同步基础资料]
        [Route("Gy_Customer/Gy_CustomerViewApi")]
        [HttpGet]
@@ -1253,7 +1270,7 @@
        }
        #endregion

         #region å®¢æˆ·åˆ†ç±» æ ‘形图(根据代码展开树状图)
        #region å®¢æˆ·åˆ†ç±» æ ‘形图(根据代码展开树状图)

        [Route("Gy_BadReason/Gy_CusrTypeTreeList")]
        [HttpGet]
@@ -1309,7 +1326,7 @@
        {
            try
            {
                string sql1 = string.Format("select hitemid,hnumber,hname,hparentid,hlevel from Gy_CusType order by hnumber");
                string sql1 = string.Format("select hitemid,hnumber,hname,hparentid,hlevel from Gy_CusType order by hnumber ");

                ds = oCN.RunProcReturn(sql1, "Gy_CusType");

@@ -1349,7 +1366,77 @@
        }
        #endregion

        #region å®¢æˆ·åˆ—表 æ–‡ä»¶ä¸Šä¼ 
        [Route("Gy_Customer/Gy_Process_Excel")]
        [HttpPost]
        public json Gy_Department_Excel()
        {
            json res = new json();
            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 provisional = customerService.UploadExcel(ExcelDs);             
                
                res.code = CodeConstant.SUCCEED;
                res.count = CountConstant.SUCCEED;
                res.Message = "上传成功";
                res.data = provisional;
                return res;
            }
            catch (Exception e)
            {
                res.code = "0";
                res.count = 0;
                res.Message = "Exception！" + e.ToString();
                res.data = null;
                return res;
            }
        }

        /// <summary>
        /// åˆ¤æ–­å½“前职员是否存在
        /// </summary>
        /// <param name="HEmpNumber"></param>
        /// <param name="HEmpName"></param>
        /// <returns></returns>
        private bool JudgeEmp(string HEmpNumber, string HEmpName)
        {
            if (!String.IsNullOrEmpty(HEmpNumber) && !String.IsNullOrEmpty(HEmpName))
            {
                DataSet emp = oCN.RunProcReturn("select * from Gy_Employee where HNumber = '" + HEmpNumber + "' and HName = '" + HEmpName + "'", "Gy_Employee");
                return emp.Tables[0].Rows.Count <= EmptyConstant.EMPTY ? true : false;
            }
            return false;
        }

        /// <summary>
        /// åˆ¤æ–­åˆ—
        /// </summary>
        /// <param name="provisional"></param>
        /// <returns></returns>
        private static string JudgmentColumns(DataTable provisional)
        {
            var error = "";

            //查询没有的列
            if (!provisional.Columns.Contains("部门代码"))
                error += "没有找到【部门代码】的标题,";

            if (!provisional.Columns.Contains("部门名称"))
                error += "没有找到【部门名称】的标题,";
            return error;
        }
        #endregion

        #region ç‰©æ–™åˆ†ç±» æ ‘形图(根据代码展开树状图)

@@ -1375,7 +1462,6 @@
                    getTreeByLevel(dt, tree[m].children, num + 1);//再次用子集去循环，拿出子集的子集
                }
            }

        }
        public class TreeModel
        {
@@ -1386,6 +1472,7 @@

        #endregion

        #region é€’归函数
        /// <summary>
        /// é€’归函数
        /// </summary>
@@ -1415,4 +1502,5 @@
        }

    }
    #endregion
}

 WebAPI/Controllers/BaseSet/Gy_DepartmentController.cs

@@ -1,12 +1,16 @@
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Pub_Class;
using SyntacticSugar.constant;
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
{
@@ -1161,5 +1165,243 @@
        }
        #endregion

        #region éƒ¨é—¨åˆ—表 æ–‡ä»¶ä¸Šä¼ 
        [Route("Gy_Department/Gy_Process_Excel")]
        [HttpPost]
        public json Gy_Department_Excel()
        {
            json res = new json();
            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 provisional = new DataTable("dt2");

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

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

                //判断列
                string error = JudgmentColumns(provisional);
                if (error.Length > 0)
                {
                    res.code = "0";
                    res.count = 0;
                    res.Message = $"Excel模板存在错误,{error}\r\n";
                    res.data = null;
                    return res;
                }

                for (int i = 0; i <= provisional.Rows.Count - 1; i++)
                {
                    string HNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["部门代码"].ToString());
                    string HName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["部门名称"].ToString());
                    string HEmpNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["负责人代码"].ToString());
                    string HEmpName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["负责人名称"].ToString());
                    string HBarCode = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["条形码"].ToString());
                    string HHelpCode = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["助记码"]);
                    string HRemark = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["备注"].ToString());

                    //判断负责人是否存在
                    if (JudgeEmp(HEmpNumber, HEmpName))
                    {
                        res.code = CodeConstant.FAIL;
                        res.count = CountConstant.FAIL;
                        res.Message = "当前职员不存在";
                        res.data = null;
                        return res;
                    }
                    //获取真实行数
                    int line = i + 1;
                }

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

        /// <summary>
        /// åˆ¤æ–­å½“前职员是否存在
        /// </summary>
        /// <param name="HEmpNumber"></param>
        /// <param name="HEmpName"></param>
        /// <returns></returns>
        private bool JudgeEmp(string HEmpNumber, string HEmpName)
        {
            if (!String.IsNullOrEmpty(HEmpNumber) && !String.IsNullOrEmpty(HEmpName))
            {
                DataSet emp = oCN.RunProcReturn("select * from Gy_Employee where HNumber = '" + HEmpNumber + "' and HName = '" + HEmpName + "'", "Gy_Employee");
                return emp.Tables[0].Rows.Count <= EmptyConstant.EMPTY ? true : false;
            }
            return false;
        }

        /// <summary>
        /// åˆ¤æ–­åˆ—
        /// </summary>
        /// <param name="provisional"></param>
        /// <returns></returns>
        private static string JudgmentColumns(DataTable provisional)
        {
            var error = "";

            //查询没有的列
            if (!provisional.Columns.Contains("部门代码"))
                error += "没有找到【部门代码】的标题,";

            if (!provisional.Columns.Contains("部门名称"))
                error += "没有找到【部门名称】的标题,";
            return error;
        }
        #endregion

        #region éƒ¨é—¨åˆ—表 å¯¼å…¥(保存)
        [Route("Gy_Department/Gy_Process_btnSave")]
        [HttpPost]
        public object Gy_InspectBasis_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();
            string organ = sArray[2].ToString();
            json res = new json();
            try
            {
                if (!DBUtility.ClsPub.Security_Log("Gy_Group_Edit", 1, false, user))
                {
                    res.code = "0";
                    res.count = 0;
                    res.Message = "无保存权限！";
                    res.data = null;
                    return res;
                }

                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 HNumber = item["部门代码"].ToString();
                    string HName = item["部门名称"].ToString();
                    string HEmpNumber = item["负责人代码"].ToString();
                    string HEmpName = item["负责人名称"].ToString();
                    string HBarCode = item["条形码"].ToString();
                    string HHelpCode = item["助记码"].ToString();
                    string HRemark = item["备注"].ToString();
                    int HEmpID = 0;
                    if (!String.IsNullOrEmpty(HEmpName) && !String.IsNullOrEmpty(HEmpNumber))
                    {
                        DataSet emp = oCN.RunProcReturn("select HItemID from Gy_Employee where HNumber = '" + HEmpNumber + "' and HName = '" + HEmpName + "'", "Gy_Employee");
                        HEmpID = (int)emp.Tables[0].Rows[0]["HItemID"];
                    }
                    string sShortNumber;
                    sShortNumber = DBUtility.ClsPub.GetShortNumber(HNumber);//短代码
                    if (sShortNumber.Trim() == "")
                    {
                        res.code = "0";
                        res.count = 0;
                        res.Message = "保存失败！短代码为空！";
                        res.data = 1;
                        return res;
                    }
                    int HEndFlag = 1;//末级标志
                    int HLevel = DBUtility.ClsPub.GetLevel(HNumber); //等级


                    if (!DBUtility.ClsPub.AllowNumber(HNumber.Trim()))
                    {
                        res.code = "0";
                        res.count = 0;
                        res.Message = "保存失败！代码中不能出现连续‘.’并且首位末位不能为‘.’！";
                        res.data = 1;
                        return res;
                    }

                    ds = oCN.RunProcReturn("select * from Gy_Department where HNumber='" + HNumber + "'", "Gy_InspectInstruMent");

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

                        string sql = "insert into Gy_Department (HName,HNumber,HShortNumber,HLevel,HHelpCode,HParentID,HStopflag,HEndFlag,HRemark,HUseFlag, HUSEORGID, HCREATEORGID, HEmpID, HBarCodeForBase)" +
                            $"values('{HName}', '{HNumber}', '{sShortNumber}',{HLevel},'',0,'0','0','{HRemark}', '未使用', '{organ}', '{organ}', '{HEmpID}', '{HBarCode}')";
                        oCN.RunProc(sql);
                    }
                    else
                    {
                        oCN.RunProc("update Gy_Department  set  HName ='" + HName + "' , HHelpCode = '" + HHelpCode + " ', HBarCodeForBase = '" + HBarCode + "', HEmpID = '" + HEmpID + "', HModifyEmp = '" + user + "', HModifyTime = '" + System.DateTime.Now.ToString("G") + "' where HNumber= '" + HNumber + "'");
                    }

                    i++;
                }

                oCN.Commit();

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

 WebAPI/Controllers/BaseSet/Gy_WarehouseController.cs

@@ -2,11 +2,14 @@
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Pub_Class;
using SyntacticSugar.constant;
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;
@@ -781,8 +784,6 @@
        }
        #endregion



        #region ç‰©æ–™åˆ†ç±»å®¡æ ¸ã€åå®¡æ ¸
        /// <summary>
        /// 
@@ -973,5 +974,242 @@
        }
        #endregion

        #region æ£€éªŒä¾æ®åˆ—表 æ–‡ä»¶ä¸Šä¼ 
        [Route("Gy_Warehouse/Gy_Process_Excel")]
        [HttpPost]
        public json Gy_InspectBasis_Excel()
        {
            json res = new json();
            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 provisional = new DataTable("dt2");

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

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

                //判断列
                string error = JudgmentColumns(provisional);
                if (error.Length > 0)
                {
                    res.code = "0";
                    res.count = 0;
                    res.Message = $"Excel模板存在错误,{error}\r\n";
                    res.data = null;
                    return res;
                }

                for (int i = 0; i <= provisional.Rows.Count - 1; i++)
                {
                    string HNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["仓库代码"].ToString());
                    string HName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["仓库名称"].ToString());
                    string phone = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["联系电话"].ToString());
                    string HEmpNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["职员代码"].ToString());
                    string HEmpName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["职员名称"].ToString());
                    string HHelpCode = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["助记码"]);
                    string HRemark = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["备注"].ToString());
                    if(JudgeEmp(HEmpNumber, HEmpName))
                    {
                        res.code = CodeConstant.FAIL;
                        res.count = CountConstant.FAIL;
                        res.Message = "当前职员不存在";
                        res.data = null;
                        return res;
                    }
                    //获取真实行数
                    int line = i + 1;
                }

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

        /// <summary>
        /// åˆ¤æ–­å½“前职员是否存在
        /// </summary>
        /// <param name="HEmpNumber"></param>
        /// <param name="HEmpName"></param>
        /// <returns></returns>
        private bool JudgeEmp(string HEmpNumber, string HEmpName)
        {
            if (!String.IsNullOrEmpty(HEmpNumber) && !String.IsNullOrEmpty(HEmpName))
            {
                DataSet emp = oCN.RunProcReturn("select * from Gy_Employee where HNumber = '" + HEmpNumber + "' and HName = '" + HEmpName + "'", "Gy_Employee");
                return emp.Tables[0].Rows.Count <= EmptyConstant.EMPTY ? true : false;
            }
            return false;
        }

        /// <summary>
        /// åˆ¤æ–­åˆ—
        /// </summary>
        /// <param name="provisional"></param>
        /// <returns></returns>
        private static string JudgmentColumns(DataTable provisional)
        {
            var error = "";

            //查询没有的列
            if (!provisional.Columns.Contains("仓库代码"))
                error += "没有找到【仓库代码】的标题,";

            if (!provisional.Columns.Contains("仓库名称"))
                error += "没有找到【仓库名称】的标题,";
            return error;
        }
        #endregion

        #region æ£€éªŒä¾æ®åˆ—表 å¯¼å…¥(保存)
        [Route("Gy_Warehouse/Gy_Process_btnSave")]
        [HttpPost]
        public object Gy_InspectBasis_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();
            string organ = sArray[2].ToString();
            json res = new json();
            try
            {
                if (!DBUtility.ClsPub.Security_Log("Gy_Group_Edit", 1, false, user))
                {
                    res.code = "0";
                    res.count = 0;
                    res.Message = "无保存权限！";
                    res.data = null;
                    return res;
                }

                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 HNumber = item["仓库代码"].ToString();
                    string HName = item["仓库名称"].ToString();
                    string phone = item["联系电话"].ToString();
                    string HEmpNumber = item["职员代码"].ToString();
                    string HEmpName = item["职员名称"].ToString();
                    string HHelpCode = item["助记码"].ToString();
                    string HRemark = item["备注"].ToString();
                    int HEmpID = 0;
                    if (!String.IsNullOrEmpty(HEmpName) && !String.IsNullOrEmpty(HEmpNumber))
                    {
                        DataSet emp = oCN.RunProcReturn("select HItemID from Gy_Employee where HNumber = '" + HEmpNumber + "' and HName = '" + HEmpName + "'", "Gy_Employee");
                        HEmpID = (int)emp.Tables[0].Rows[0]["HItemID"];
                    }
                    string sShortNumber;
                    sShortNumber = DBUtility.ClsPub.GetShortNumber(HNumber);//短代码
                    if (sShortNumber.Trim() == "")
                    {
                        res.code = "0";
                        res.count = 0;
                        res.Message = "保存失败！短代码为空！";
                        res.data = 1;
                        return res;
                    }
                    int HEndFlag = 1;//末级标志
                    int HLevel = DBUtility.ClsPub.GetLevel(HNumber); //等级


                    if (!DBUtility.ClsPub.AllowNumber(HNumber.Trim()))
                    {
                        res.code = "0";
                        res.count = 0;
                        res.Message = "保存失败！代码中不能出现连续‘.’并且首位末位不能为‘.’！";
                        res.data = 1;
                        return res;
                    }

                    ds = oCN.RunProcReturn("select * from Gy_Warehouse where HNumber='" + HNumber + "'", "Gy_InspectInstruMent");

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

                        string sql = "insert into Gy_Warehouse (HName,HNumber,HShortNumber,HLevel,HHelpCode,HParentID,HStopflag,HEndFlag,HRemark,HUseFlag, HUSEORGID, HCREATEORGID, HEmpID, HPhone)" +
                            $"values('{HName}', '{HNumber}', '{sShortNumber}',{HLevel},'',0,'0','0','{HRemark}', '未使用', '{organ}', '{organ}', '{HEmpID}', '{phone}')";
                        oCN.RunProc(sql);
                    }
                    else
                    {
                        oCN.RunProc("update Gy_Warehouse  set  HName ='" + HName + "' , HHelpCode = '" + HHelpCode + " ', HPhone= '" + phone + "', HEmpID = '" + HEmpID + "', HModifyEmp = '" + user + "', HModifyTime = '" + System.DateTime.Now.ToString("G") + "' where HNumber= '" + HNumber + "'");
                    }

                    i++;
                }

                oCN.Commit();

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

    }
}

 WebAPI/Controllers/CJGL/Cj_StationOutBillController.cs

@@ -2,6 +2,7 @@
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Pub_Class;
using SyntacticSugar.constant;
using System;
using System.Collections;
using System.Collections.Generic;
@@ -1715,67 +1716,32 @@
        }
        #endregion

        #region è¿”回工序出站汇报单列表     20240926新增
        [Route("Cj_StationOutBillController/GetSc_StationOutBillList_Json")]
        #region å·¥åºå‡ºç«™æ±‡æŠ¥å•åˆ—表
        [Route("Cj_StationOutBill/page")]
        [HttpGet]
        public object GetSc_StationOutBillList_Json(string sWhere, string HMaker, string HBillSubType, string OperationType, string ViewName)
        public json page(string sWhere, string user, int page, int size)
        {
            json res = new json();
            try
            {
                List<object> columnNameList = new List<object>();
                //判断权限
                if (HBillSubType == "SUB")
                {
                    //判断是否有查询权限
                    if (!DBUtility.ClsPub.Security_Log("Cj_StationOutBill_Sub_Query", 1, false, HMaker))
             if (!DBUtility.ClsPub.Security_Log("Cj_StationOutBill_Query", 1, false, user))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "完工单无查询权限!";
                        objJsonResult.data = null;
                        return objJsonResult;
                        res.code = "0";
                        res.count = 0;
                        res.Message = "出站单无查询权限!";
                        res.data = null;
                        return res;
                    }
                }
                else if(OperationType == "1")
                if (sWhere == null || sWhere.Equals(""))
                {
                    //判断是否有查询权限
                    if (!DBUtility.ClsPub.Security_Log("Cj_StationOutBill_Query", 1, false, HMaker))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "出站单无查询权限!";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                    ds = oCN.RunProcReturn("exec h_p_MES_StationOutBillList " + page + "," + size + ",''", "h_p_MES_StationOutBillList");
                }

                //获取系统参数
                string Ret = "";
                if (oSystemParameter.ShowBill(ref Ret))
                else
                {
                    //判断客户为龙山汽配
                    if (oSystemParameter.omodel.WMS_CampanyName == "龙山汽配")
                    {
                        //获取需要拼接的字符串
                        string sql_splice = DBUtility.ClsPub.SpliceSQL(HMaker, "工序汇报单列表");

                        if (sql_splice == "没有查询到相关用户信息")
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "没有查询到相关用户信息!";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                        else
                        {
                            sWhere += sql_splice;
                        }
                    }
                    ds = oCN.RunProcReturn("exec h_p_MES_StationOutBillList " + page + "," + size + ",'" + sWhere + "'", "h_p_MES_StationOutBillList");
                }

                //返回列表信息
                ds = oCN.RunProcReturn("select * from " + ViewName + " where 1=1 " + sWhere + " order by hmainid desc", ViewName);

                //添加列名
                foreach (DataColumn col in ds.Tables[0].Columns)
@@ -1784,24 +1750,24 @@
                    string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}";
                    columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名
                }
                objJsonResult.code = "0";
                objJsonResult.count = 1;
                objJsonResult.Message = "成功！";
                objJsonResult.data = ds.Tables[0];
                objJsonResult.list = columnNameList;
                return objJsonResult;

                res.code = CodeConstant.SUCCEED;
                res.count = int.Parse(ds.Tables[1].Rows[0]["count"].ToString());
                res.Message = "Sucess！";
                res.list = columnNameList;
                res.data = ds.Tables[0];
                return res;
            }
            catch (Exception e)
            {
                objJsonResult.code = "0";
                objJsonResult.count = 0;
                objJsonResult.Message = "查询列表信息失败！" + e.ToString();
                objJsonResult.data = null;
                res.code = CodeConstant.FAIL;
                res.count = CountConstant.FAIL;
                res.Message = "Exception！" + e.ToString();
                res.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region å·¥åºå‡ºç«™æ±‡æŠ¥å• å­è¡¨æŸ¥è¯¢
        [Route("Cj_StationOutBill/get_SubDisplay")]
        [HttpGet]

 WebAPI/Controllers/CJGL/Sc_ProcessExchangeBillController.cs

@@ -1,6 +1,7 @@
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Pub_Class;
using SyntacticSugar.constant;
using System;
using System.Collections;
using System.Collections.Generic;
@@ -1911,6 +1912,59 @@
        }
        #endregion

        #region å·¥åºæµè½¬å¡åˆ†é¡µåˆ—表
        [Route("Sc_ProcessExchangeBillList/page")]
        [HttpGet]
        public json page(string sWhere, string user, int page, int size)
        {
            json res = new json();
            try
            {
                List<object> columnNameList = new List<object>();
                //编辑权限
                if (!DBUtility.ClsPub.Security_Log_second("Sc_ProcessExchangeBill_SubQuery", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无查看权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                if (sWhere == null || sWhere.Equals(""))
                {
                    ds = oCN.RunProcReturn("exec h_p_Sc_ProcessExchangeBillList_Query " + page + "," + size + ",''", "h_p_Sc_ProcessExchangeBillList_Query");
                }
                else
                {
                    ds = oCN.RunProcReturn("exec h_p_Sc_ProcessExchangeBillList_Query " + page + "," + size + ",'" + sWhere + "'", "h_p_Sc_ProcessExchangeBillList_Query");
                }

                //添加列名
                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列对象的列名
                }

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

        #region å·¥åºæµè½¬å¡åˆ—表
        /// <summary>
        /// èŽ·å–工序流转卡列表信息

 WebAPI/Controllers/CKGL/TMZD/Gy_BarCodeBillListController.cs

New file
@@ -0,0 +1,77 @@
using Newtonsoft.Json;
using SyntacticSugar.constant;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using WebAPI.Models;

namespace WebAPI.Controllers.CKGL.TMZD
{
    public class Gy_BarCodeBillListController : ApiController
    {
        //获取系统参数
        Pub_Class.ClsXt_SystemParameter oSystemParameter = new Pub_Class.ClsXt_SystemParameter();
        public DBUtility.ClsPub.Enum_BillStatus BillStatus;
        public string sWhere = "";
        private json objJsonResult = new json();
        SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
        DataSet ds;

        #region æ¡ç ä¸»æ¡£åˆ†é¡µåˆ—表
        [Route("Gy_BarCodeBillList/page")]
        [HttpGet]
        public json page(string sWhere, string user, int page, int size)
        {
            json res = new json();
            try
            {
                List<object> columnNameList = new List<object>();
                //编辑权限
                if (!DBUtility.ClsPub.Security_Log_second("Gy_Material", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无查看权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                if (sWhere == null || sWhere.Equals(""))
                {
                    ds = oCN.RunProcReturn("exec h_p_IF_BarCodeBillList " + page + "," + size  + ",''", "h_p_IF_BarCodeBillList");
                }
                else
                {
                    ds = oCN.RunProcReturn("exec h_p_IF_BarCodeBillList " + page + "," + size  +  ",'" + sWhere + "'", "h_p_IF_BarCodeBillList");
                }

                //添加列名
                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列对象的列名
                }

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

 WebAPI/Controllers/LMESController.cs

@@ -1,6 +1,7 @@
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Pub_Class;
using SyntacticSugar.constant;
using System;
using System.Collections;
using System.Collections.Generic;
@@ -1114,6 +1115,60 @@

        #region å·¥è‰ºè·¯çº¿

        #region å·¥è‰ºè·¯çº¿åˆ†é¡µåˆ—表
        [Route("Gy_RoutingBill/page")]
        [HttpGet]
        public json Gy_RoutingBillPage(string sWhere, string user, int page, int size)
        {
            DataSet ds;
            json res = new json();
            try
            {
                List<object> columnNameList = new List<object>();
                //编辑权限
                if (!DBUtility.ClsPub.Security_Log_second("Gy_RoutingBill_Query", 1, false, user))
                {
                    res.code = "0";
                    res.count = 0;
                    res.Message = "无查看权限！";
                    res.data = null;
                    return res;
                }
                if (sWhere == null || sWhere.Equals(""))
                {
                    ds = oCN.RunProcReturn("exec h_p_Gy_RoutingBillList " + page + "," + size + ",''", "h_p_Gy_RoutingBillList");
                }
                else
                {
                    ds = oCN.RunProcReturn("exec h_p_Gy_RoutingBillList " + page + "," + size + ",'" + sWhere + "'", "h_p_Gy_RoutingBillList");
                }

                //添加列名
                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列对象的列名
                }

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

        #region å·¥è‰ºè·¯çº¿åˆ—表
        /// <summary>
        /// è¿”回工艺路线列表
@@ -1676,6 +1731,59 @@
            return GetObjectJson(ds);
        }

        #region ç”Ÿäº§è®¢å•åˆ†é¡µåˆ—表
        [Route("LEMS/MES_IF_ICMOBillListPage")]
        [HttpGet]
        public object MES_IF_ICMOBillListPage(string sWhere, string user, int page, int size)
        {
            DataSet ds;
            try
            {
                List<object> columnNameList = new List<object>();
                //编辑权限
                if (!DBUtility.ClsPub.Security_Log_second("Sc_ICMOBillQuery", 1, false, user))
                {
                    objJsonResult.code = "0";
                    objJsonResult.count = 0;
                    objJsonResult.Message = "无查看权限！";
                    objJsonResult.data = null;
                    return objJsonResult;
                }
                if (sWhere == null || sWhere.Equals(""))
                {
                    ds = oCN.RunProcReturn("exec h_p_IF_ICMOBillList " + page + "," + size + ",''", "h_p_IF_ICMOBillList");
                }
                else
                {
                    ds = oCN.RunProcReturn("exec h_p_IF_ICMOBillList " + page + "," + size + ",'" + sWhere + "'", "h_p_IF_ICMOBillList");
                }

                //添加列名
                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 = int.Parse(ds.Tables[1].Rows[0]["count"].ToString());
                objJsonResult.Message = "Sucess！";
                objJsonResult.list = columnNameList;
                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("LEMS/MES_IF_ProcessBillList_Json")]
        [HttpGet]

 WebAPI/Controllers/SBGL/SBBB/Sb_EquipMentCollectionTechParam_TempController.cs

@@ -1,4 +1,5 @@
using Newtonsoft.Json;
using Model;
using Newtonsoft.Json;
using SyntacticSugar.constant;
using System;
using System.Collections.Generic;
@@ -36,6 +37,8 @@
                string sql = "select * from h_v_Sb_EquipMentCollectionTechParam_TempList where 1 = 1" + sWhere;
                DataSet ds = oCN.RunProcReturn(sql, "h_v_Sb_EquipMentCollectionTechParam_TempList");



                //数据遍历, èŽ·å–列名
                List<object> columnNameList = new List<object>();
                foreach (DataColumn col in ds.Tables[0].Columns)

 WebAPI/Controllers/»ù´¡×ÊÁÏ/»ù´¡×ÊÁÏ/Gy_MaterTypeBillController.cs

@@ -868,11 +868,11 @@

        [Route("Gy_MaterType/Gy_MaterTypeTreeListByLevel")]
        [HttpGet]
        public object Gy_MaterTypeTreeListByLevel()
        public object Gy_MaterTypeTreeListByLevel(string HOrgID)
        {
            try
            {
                string sql1 = string.Format("select hitemid,hnumber,hname,hparentid,hlevel from Gy_MaterType order by hnumber");
                string sql1 = string.Format("select hitemid,hnumber,hname,hparentid,hlevel from Gy_MaterType where HUSEORGID = '" + HOrgID + "' order by hnumber");

                ds = oCN.RunProcReturn(sql1, "Gy_MaterType");


 WebAPI/Exceptions/EmptyException.cs

New file
@@ -0,0 +1,17 @@
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WebAPI.Exceptions
{
    /// <summary>
    /// ç©ºæ•°æ®å¼‚常
    /// </summary>
    public class EmptyException : Exception
    {
        public EmptyException(string message): base(message)
        { 
        }
    }
}

 WebAPI/Exceptions/ExcelException.cs

New file
@@ -0,0 +1,17 @@
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WebAPI.Exceptions
{
    /// <summary>
    /// EXCEL异常报错类
    /// </summary>
    public class ExcelException : Exception
    {
        public ExcelException(string message) : base(message)
        {
        }
    }
}

 WebAPI/Models/Query/QueryModel.cs

New file
@@ -0,0 +1,23 @@
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WebAPI.Models.Query
{
    /// <summary>
    /// æŸ¥è¯¢ç±»
    /// </summary>
    public class QueryModel
    {
        /// <summary>
        /// sql查询语句
        /// </summary>
        public string sWhere { get; set; }

        /// <summary>
        /// æŸ¥è¯¢ç”¨æˆ·
        /// </summary>
        public string user { get; set; }
    }
}

 WebAPI/Models/QueryModel.cs

New file
@@ -0,0 +1,21 @@
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Model
{
    public class QueryModel
    {
        /// <summary>
        /// sql查询语句
        /// </summary>
        public string sWhere { get; set; }

        /// <summary>
        /// æŸ¥è¯¢ç”¨æˆ·
        /// </summary>
        public string user { get; set; }
    }
}

 WebAPI/Utility/TreeUtil.cs

New file
@@ -0,0 +1,74 @@
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace WebAPI.Utility
{
    public class TreeUtil
    {
        public class TreeModel
        {
            public string id { get; set; }
            public string title { get; set; }
            public List<TreeModel> children = new List<TreeModel>();
        }

        #region é€’归函数
        /// <summary>
        /// é€’归函数
        /// </summary>
        public void digui(DataTable dt, List<TreeModel> tree, int num)
        {
            for (int m = 0; m < tree.Count; m++)
            {
                tree[m].children = new List<TreeModel>();
                for (int i = 0; i < dt.Rows.Count; i++)//第一次循环，得到所有根节点的子集
                {
                    var strLen = dt.Rows[i]["hnumber"].ToString().Split('.');
                    if (strLen.Length == num && dt.Rows[i]["hnumber"].ToString().Contains(tree[m].id + "."))
                    {
                        TreeModel tbjson = new TreeModel();
                        tbjson.id = dt.Rows[i]["hitemid"].ToString();
                        tbjson.title = dt.Rows[i]["hname"].ToString();
                        tree[m].children.Add(tbjson);
                    }
                }
                var strLens = tree[m].id.Split('.');
                for (int i = 0; i < tree[m].children.Count; i++)
                {
                    digui(dt, tree[m].children, strLens.Length + 2);//再次用子集去循环，拿出子集的子集
                }
            }

        }
        #endregion

        #region åˆ†ç±» æ ‘形图(根据代码展开树状图)
        public void getTreeByLevel(DataTable dt, List<TreeModel> tree, int num)
        {
            for (int m = 0; m < tree.Count; m++)
            {
                tree[m].children = new List<TreeModel>();
                for (int i = 0; i < dt.Rows.Count; i++)//第一次循环，得到所有根节点的子集
                {
                    var HLevel = (int)dt.Rows[i]["hlevel"];
                    var HParentID = dt.Rows[i]["hparentid"].ToString();
                    if (HLevel == num && HParentID == tree[m].id)
                    {
                        TreeModel tbjson = new TreeModel();
                        tbjson.id = dt.Rows[i]["hitemid"].ToString();
                        tbjson.title = dt.Rows[i]["hname"].ToString();
                        tree[m].children.Add(tbjson);
                    }
                }
                for (int i = 0; i < tree[m].children.Count; i++)
                {
                    getTreeByLevel(dt, tree[m].children, num + 1);//再次用子集去循环，拿出子集的子集
                }
            }
        }
        #endregion
    }
}

 WebAPI/WebAPI.csproj.user

@@ -10,7 +10,8 @@
    <NameOfLastUsedPublishProfile>FolderProfile</NameOfLastUsedPublishProfile>
    <NameOfLastUsedPublishProfile>D:\Git\houduan\WebAPI\Properties\PublishProfiles\FolderProfile.pubxml</NameOfLastUsedPublishProfile>
    <NameOfLastUsedPublishProfile>C:\Users\86130\Desktop\智云迈思\MES-WEB-API\WebAPI\Properties\PublishProfiles\FolderProfilewtt.pubxml</NameOfLastUsedPublishProfile>
    <LastActiveSolutionConfig>Debug|Any CPU</LastActiveSolutionConfig>
    <NameOfLastUsedPublishProfile>D:\网站发布\后端代码\MES-WEB-API\MES-WEB-API\WebAPI\Properties\PublishProfiles\FolderProfile10.pubxml</NameOfLastUsedPublishProfile>
    <LastActiveSolutionConfig>Release|Any CPU</LastActiveSolutionConfig>
    <UseIISExpress>true</UseIISExpress>
    <Use64BitIISExpress />
    <IISExpressSSLPort />

 WebAPI/WebBLL/JCJM/GYZL/Gy_CustomerService.cs

New file
@@ -0,0 +1,25 @@
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WebAPI.WebBLL.JCJM.GYZL
{
    interface Gy_CustomerService
    {
        /// <summary>
        /// å¯¼å…¥excel
        /// </summary>
        void ImportExcel();

        /// <summary>
        /// ä¸Šä¼ excel
        /// </summary>
        /// <returns></returns>
        DataTable UploadExcel(DataSet ExcelDs);
    }


}

 WebAPI/WebBLL/JCJM/IMPL/Gy_CustomerImpl.cs

New file
@@ -0,0 +1,207 @@
using SyntacticSugar.constant;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using System.Web;
using WebAPI.Exceptions;
using WebAPI.WebBLL.JCJM.GYZL;

namespace WebAPI.WebBLL.JCJM.IMPL
{
    public class Gy_CustomerImpl : Gy_CustomerService
    {
        // çº¿ç¨‹è®¡æ•°å™¨
        List<Task> taskList = new List<Task>();
        SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
        #region å¯¼å…¥EXCEL
        public void ImportExcel()
        {
            throw new NotImplementedException();
        }
        #endregion


        /// <summary>
        /// åˆ¤æ–­åˆ—
        /// </summary>
        /// <param name="provisional"></param>
        /// <returns></returns>
        private static string JudgmentColumns(DataTable provisional)
        {
            var error = "";
            //查询没有的列
            if (!provisional.Columns.Contains("客户代码"))
                error += "没有找到【客户代码】的标题,";

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

        #region ä¸Šä¼ EXCEL
        public DataTable UploadExcel(DataSet ExcelDs)
        {
            //创建临时表
            DataTable provisional = new DataTable("dt2");

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

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

            //判断列
            string error = JudgmentColumns(provisional);
            if (error.Length > 0)
            {
                throw new ExcelException($"Excel模板存在错误,{error}\r\n");
            }

            for (int i = 0; i <= provisional.Rows.Count - 1; i++)
            {
                string HRegionNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["地区代码"].ToString());
                string HRegionName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["地区名称"].ToString());
                string HCurrencyNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["默认货币代码"].ToString());
                string HCurrencyName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["默认货币名称"].ToString());
                //string HBillingNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["结算方式代码"].ToString());
                //string HBillingName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["结算方式名称"].ToString());

                string HCusTypeNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["客户分类代码"].ToString());
                string HCusTypeName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["客户分类名称"].ToString());

                //记录任务
                var tasks = new List<Task>();

                //如果当前地区代码和地区名称不为空则判断是否存在
                if (!String.IsNullOrEmpty(HRegionName) || !String.IsNullOrEmpty(HRegionNumber))
                {
                    tasks.Add(JudgeRegion(HRegionNumber, HRegionName));
                }

                // å¦‚果当前货币代码和货币名称不为空则判断是否存在
                if (!String.IsNullOrEmpty(HCurrencyName) || !String.IsNullOrEmpty(HCurrencyNumber))
                    {
                        tasks.Add(JudgeCurrency(HCurrencyNumber, HCurrencyName));
                    }

                //如果当前客户分类代码和名称
                if (!String.IsNullOrEmpty(HCusTypeName) || !String.IsNullOrEmpty(HCusTypeNumber))
                    {
                        tasks.Add(JudgeCusType(HCusTypeNumber, HCusTypeName));
                    }
                try
                {
                    //等待任务完成
                    Task.WaitAll(tasks.ToArray());
                }
                catch (AggregateException ae)
                {
                    string msg = ae.Message;
                    ae.Handle(ex => {
                        if (ex is EmptyException)
                            msg = ex.Message;
                            Console.WriteLine(ex.Message);
                        return ex is EmptyException;
                    });
                    throw new Exception("第" + i +1 +"有问题: " +msg);
                }
                  
    
                //获取真实行数
                int line = i + 1;
            }
            return provisional;
        }

        /// <summary>
        /// åˆ¤æ–­åœ°åŒºæ˜¯å¦æ­£ç¡®
        /// </summary>
        private Task<int> JudgeRegion(string HNumber, string HName)
        {
            return Task<int>.Factory.StartNew(() =>
            {
                try
                {
                    SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
                    DataSet ds = oCN.RunProcReturn("select * from Gy_AreaSet where HNumber = '" + HNumber + "' and HName = '" + HName + "'", "Gy_AreaSet");
                    if (ds.Tables[0].Rows.Count == EmptyConstant.EMPTY)
                    {
                        throw new EmptyException("当前地区不存在，请重新选择");
                    }
                    return (int)ds.Tables[0].Rows[0][0];
                }
                catch (Exception e)
                {
                    throw new EmptyException(e.Message);
                }
            });
        }

        /// <summary>
        /// åˆ¤æ–­è´§å¸æ˜¯å¦æ­£ç¡®
        /// </summary>
        private Task<int> JudgeCurrency(string HNumber, string HName)
        {
            {
                return Task<int>.Factory.StartNew(() =>
                {
                    try
                    {
                        SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
                        DataSet ds = oCN.RunProcReturn("select * from Gy_Currency where HNumber = '" + HNumber + "' and HName = '" + HName + "'", "Gy_Currency");
                        if (ds.Tables[0].Rows.Count == EmptyConstant.EMPTY)
                        {
                            throw new EmptyException("当前货币不存在，请重新选择");
                        }
                        return (int)ds.Tables[0].Rows[0][0];
                    }
                    catch (Exception e)
                    {
                        throw new EmptyException(e.Message);
                    }
                });
            }
        }

        /// <summary>
        /// åˆ¤æ–­å®¢æˆ·åˆ†ç±»
        /// </summary>
        private Task<int> JudgeCusType(string HNumber, string HName)
        {
            {
                return Task<int>.Factory.StartNew(() =>
                {
                    try
                    {
                        SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
                        DataSet ds = oCN.RunProcReturn("select * from Gy_CusType where HNumber = '" + HNumber + "' and HName = '" + HName + "'", "Gy_CusType");
                        if (ds.Tables[0].Rows.Count == EmptyConstant.EMPTY)
                        {
                            throw new EmptyException("当前分类不存在，请重新选择");
                        }
                        return (int)ds.Tables[0].Rows[0][0];
                    }
                    catch (Exception e)
                    {
                        throw new EmptyException(e.Message);
                    }
                });
            }
        }
        #endregion
    }

}