导入功能部分实现

wtt

2024-09-20 d3681038d9acf00d05af1e43f756e9613607c408

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

@@ -7,7 +7,10 @@
using System.Web;
using System.Web.Http;
using WebAPI.Models;

using System.Web;
using WebAPI.Controllers.SCGL.日计划管理;
using System.IO;
using SyntacticSugar.constant;
namespace WebAPI.Controllers.基础资料.基础资料
{
    public class Gy_MaterTypeBillController : ApiController
@@ -201,6 +204,327 @@
        }
        #endregion

        #region ç‰©æ–™åˆ†ç±» æ–‡ä»¶å¯¼å…¥ä¿å­˜
        #region ç‰©æ–™åˆ†ç±» æ–‡ä»¶ä¸Šä¼ 
        [Route("Gy_MaterType/Gy_MaterType_Excel")]
        [HttpPost]
        public object Gy_MaterType_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());
                }

                //模板缺少列 ä½†éœ€è¦ä»Žæ•°æ®åº“中查询出来显示在页面的字段
                tb2.Columns.Add("HOrgID", typeof(Int32));//组织ID
                tb2.Columns.Add("HParentID", 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 (!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 = CodeConstant.FAIL;
                    objJsonResult.count = CountConstant.FAIL;
                    objJsonResult.Message = $"Excel模板存在错误,{error}\r\n";
                    objJsonResult.data = null;
                    return objJsonResult;
                }

                for (int i = 0; i <= tb2.Rows.Count - 1; i++)
                {
                    string HMaterTypeName = "";
                    string HMaterTypeNum = "";
                    string HORGNumber = "";
                    string HORGName = "";
                    string HParentNumber = "";
                    string HParentName = "";

                    HMaterTypeName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["物料分类"].ToString());
                    HMaterTypeNum = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["物料分类代码"].ToString());
                    HParentName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["父类名称"].ToString());
                    HParentNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["父类代码"].ToString());
                    HORGNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织代码"].ToString());
                    HORGName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织名称"].ToString());

                    //检查物料
                    int index = i + 1;

                    if (HORGNumber != "")
                    {

                        //查询组织
                        ds = oCN.RunProcReturn("select * from Xt_ORGANIZATIONS where  HNumber='" + HORGNumber + "' and Hname='" + HORGName + "'", "Xt_ORGANIZATIONS");

                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            objJsonResult.code = CodeConstant.FAIL;
                            objJsonResult.count = CountConstant.FAIL;
                            objJsonResult.Message = "第" + index + "行,组织不存在！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                        else
                        {
                            tb2.Rows[i]["HOrgID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
                        }
                        string HORGid = ds.Tables[0].Rows[0]["HItemID"].ToString();

                        if(HParentNumber!="" && HParentName != "")
                        {
                            //查询父类id
                            ds = oCN.RunProcReturn("select * from Gy_MaterType where  HNumber='" + HParentNumber + "' and Hname='" + HParentName + "'", "Gy_MaterType");

                            if (ds.Tables[0].Rows.Count == 0)
                            {
                                objJsonResult.code = CodeConstant.FAIL;
                                objJsonResult.count = CountConstant.FAIL;
                                objJsonResult.Message = "第" + index + "行,父类不存在！";
                                objJsonResult.data = null;
                                return objJsonResult;
                            }
                            else
                            {
                                tb2.Rows[i]["HParentID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
                            }
                            string HParentID = ds.Tables[0].Rows[0]["HItemID"].ToString();
                        }
                        else {
                            tb2.Rows[i]["HParentID"] = "0";
                        }
                        

                        //物料分类名称
                        if (HMaterTypeName == "")
                        {
                            objJsonResult.code = CodeConstant.FAIL;
                            objJsonResult.count = CountConstant.FAIL;
                            objJsonResult.Message = "第" + index + "行,物料分类名称不能为空！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }

                        //物料分类名称
                        if (HMaterTypeNum == HParentNumber)
                        {
                            objJsonResult.code = CodeConstant.FAIL;
                            objJsonResult.count = CountConstant.FAIL;
                            objJsonResult.Message = "第" + index + "行,物料类型父类不能是自己！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }

                        //物料分类代码
                        if (HMaterTypeNum == "")
                        {
                            objJsonResult.code = CodeConstant.FAIL;
                            objJsonResult.count = CountConstant.FAIL;
                            objJsonResult.Message = "第" + index + "行,物料分类代码不能为空！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                    }
                    else
                    {
                        objJsonResult.code = CodeConstant.FAIL;
                        objJsonResult.count = CountConstant.FAIL;
                        objJsonResult.Message = "第" + index + "行,组织代码为空";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                }

                objJsonResult.code = CodeConstant.SUCCEED;
                objJsonResult.count = CountConstant.SUCCEED;
                objJsonResult.Message = error;
                objJsonResult.data = tb2;
                return objJsonResult;
            }
            catch (Exception e)
            {
                objJsonResult.code = CodeConstant.FAIL;
                objJsonResult.count = CountConstant.FAIL;
                objJsonResult.Message = "Exception！" + e.ToString();
                objJsonResult.data = null;
                return objJsonResult;
            }
        }
        #endregion

        #region ç‰©æ–™åˆ†ç±»é¡¹ç›® å¯¼å…¥(保存)
        [Route("Gy_MaterType/Gy_MaterType_btnSave")]
        [HttpPost]
        public object Gy_MaterType_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
            {
                if (!DBUtility.ClsPub.Security_Log("Gy_ErrType_Edit", 1, false, user))
                {
                    objJsonResult.code = CodeConstant.FAIL;
                    objJsonResult.count = CountConstant.FAIL;
                    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);
                }

                oCN.BeginTran();
                int i = 1;
                foreach (Dictionary<string, string> item in list)
                {
                    string HOrgID = item["HOrgID"].ToString();//组织ID
                    string HName = item["物料分类"].ToString();
                    string HNumber = item["物料分类代码"].ToString();
                    string HRemark = item["备注"].ToString();
                    string HHelpCode = item["助记码"].ToString();
                    string HParentID = item["HParentID"].ToString();
                    string sShortNumber;
                    sShortNumber = DBUtility.ClsPub.GetShortNumber(HNumber);//短代码
                    if (sShortNumber.Trim() == "")
                    {
                        objJsonResult.code = CodeConstant.FAIL;
                        objJsonResult.count = CountConstant.FAIL;
                        objJsonResult.Message = "保存失败！短代码为空！";
                        objJsonResult.data = 1;
                        return objJsonResult;
                    }
                    int HEndFlag = 1;//末级标志
                    int HLevel = 1; //等级
                    DataSet ds2 = oCN.RunProcReturn("select HLevel from  Gy_MaterType where HItemID =" + HParentID, "Gy_MaterType");//搜索父类等级
                    //oItem.HLevel = DBUtility.ClsPub.GetLevel(oItem.HNumber); //等级
                    if (ds2.Tables[0].Rows.Count > 0)
                    {
                        HLevel = (int)ds2.Tables[0].Rows[0]["HLevel"] + 1;
                    }
                    else
                    {
                        HLevel = 1;
                    }

                    if (!DBUtility.ClsPub.AllowNumber(HNumber.Trim()))
                    {
                        objJsonResult.code = CodeConstant.FAIL;
                        objJsonResult.count = CountConstant.FAIL;
                        objJsonResult.Message = "保存失败！代码中不能出现连续‘.’并且首位末位不能为‘.’！";
                        objJsonResult.data = 1;
                        return objJsonResult;
                    }
                    
                    ds = oCN.RunProcReturn("select * from Gy_MaterType where HNumber='" + HNumber + "'", "Gy_MaterType");

                    if (ds.Tables[0].Rows.Count == 0)
                    {
                            oCN.RunProc("Insert into "  + "Gy_MaterType" +
                        " (HNumber,HName,HHelpCode,HShortNumber,HParentID" +
                        ",HLevel,HEndFlag,HStopflag,HRemark,HMakeEmp,HMakeTime,HStopEmp,HStopTime,HUSEORGID,HCREATEORGID,HUseFlag) " +
                        " Values('" + HNumber + "','" + HName + "','" + HHelpCode + "','" + sShortNumber + "'," + HParentID.ToString() +
                        "," + HLevel + "," + HEndFlag + "," +  0 + ",'" + HRemark + "','" + user + "',getdate(),'" + "" + "','" +  "" + "'," + HOrgID + "," + HOrgID + ",'" + "未检测" + "')");
                        //修改上级为非末级代码
                        oCN.RunProc("Update Gy_MaterType" + " set HEndflag=0 where HItemID=" + HParentID);
                    }
                    else
                    {
                        oCN.RunProc("update  Gy_MaterType set HUSEORGID=" + HOrgID + ",HParentID = " + HParentID + ",HHelpCode = '" + HHelpCode + "',HRemark='" + HRemark + "'  where HNumber='" + HNumber + "'");
                        //修改上级为非末级代码
                        oCN.RunProc("Update Gy_MaterType" + " set HEndflag=0 where HItemID=" + HParentID);
                    }

                    i++;
                }

                oCN.Commit();

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


        #region ç‰©æ–™åˆ†ç±»  åˆ é™¤
        [Route("Gy_MaterType/DeleteMaterType")]