合并分支

chenhaozhe

20 小时以前 d00b4284626da680659b4f013e5c17d5dbe4ccf7

 WebAPI/Controllers/»ªÔ¶datamapping/Gy_PNLInfoController.cs

@@ -11,6 +11,10 @@
using SyntacticSugar.constant;
using DBUtility;
using System.Text;
using System.Web;
using WebAPI.Controllers.SCGL.日计划管理;
using System.IO;
using System.Text.RegularExpressions;

namespace WebAPI.Controllers.基础资料.基础资料
{
@@ -1084,5 +1088,346 @@
            }
        }
        #endregion

        #region PNL布局信息 æ–‡ä»¶ä¸Šä¼ 
        [Route("Gy_PNLInfo/Gy_PNLInfo_Excel")]
        [HttpPost]
        public object Gy_PNLInfo_Excel()
        {
            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("Set号", typeof(string));
                tb2.Columns.Add("行索引", typeof(Int32));
                tb2.Columns.Add("列索引", typeof(Int32));
                tb2.Columns.Add("HPNLInfoTypeID", typeof(Int32));

                //添加数据
                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();
                    }
                    //如果表格第i行的第一列为空，则判断为这一行的数据为空，跳出循环并且不把数据写入 tb2
                    if (ExcelDs.Tables[0].Rows[i][0].ToString() == "")
                    {
                        continue;
                    }
                    else
                    {
                        tb2.Rows.Add(row);
                    }
                }


                var error = "";

                //查询导入模板没有的列
                if (!tb2.Columns.Contains("料号"))
                    error += "上传文件中没有找到【料号】的标题,";

                if (!tb2.Columns.Contains("分类"))
                    error += "上传文件中没有找到【分类】的标题,";

                if (!tb2.Columns.Contains("PCS号"))
                    error += "上传文件中没有找到【PCS号】的标题,";


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

                string HLayOutName_temp = "";
                string HPNLInfoTypeName_temp = "";
                List<string> HPCSNo_Array = new List<string>();
                if (tb2.Rows.Count > 0)
                {
                    HLayOutName_temp = DBUtility.ClsPub.isStrNull(tb2.Rows[0]["料号"].ToString());
                    HPNLInfoTypeName_temp = DBUtility.ClsPub.isStrNull(tb2.Rows[0]["分类"].ToString());
                }

                for (int i = 0; i <= tb2.Rows.Count - 1; i++)
                {
                    string HLayOutName = "";                //料号
                    string HPNLInfoTypeName = "";           //分类
                    string HPCSNo = "";                     //PCS号

                    HLayOutName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["料号"].ToString());
                    HPNLInfoTypeName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["分类"].ToString());
                    HPCSNo = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["PCS号"].ToString());
                    
                    //检查数据是否完整
                    int index = i + 2;

                    if (HLayOutName != "")
                    {
                        if (HLayOutName != HLayOutName_temp)
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "第" + index + "行,【料号】与首行数据中【料号】不一致！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                        if (HPNLInfoTypeName != HPNLInfoTypeName_temp)
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "第" + index + "行,【分类】与首行数据中【分类】不一致！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                        if (HPCSNo_Array.Contains(HPCSNo))
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "第" + index + "行,【PCS号】存在重复！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                        else
                        {
                            HPCSNo_Array.Add(HPCSNo);
                        }


                        //查询料号资料是否已经存在
                        ds = oCN.RunProcReturn("select * from Gy_PNLInfo where HLayOutName = '" + HLayOutName + "' ", "Gy_PNLInfo");

                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "第" + index + "行,料号【" + HLayOutName + "】资料已经存在！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }

                        //查询 åˆ†ç±»
                        ds = oCN.RunProcReturn("select * from Gy_PNLInfoType where HName = '" + HPNLInfoTypeName + "' ", "Gy_PNLInfoType");
                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "第" + index + "行,分类【" + HPNLInfoTypeName + "】不存在！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                        else
                        {
                            tb2.Rows[i]["HPNLInfoTypeID"] = ds.Tables[0].Rows[0]["HInterID"];
                        }

                        //判断PCS号格式是否正确
                        if (!Regex.IsMatch(HPCSNo, @"^\d{6}$"))
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "第" + index + "行,PCS号【" + HPCSNo + "】格式错误，应设置为由6位数字组成！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }
                        else
                        {
                            tb2.Rows[i]["Set号"] = HPCSNo.Substring(0, 2);
                            tb2.Rows[i]["行索引"] = int.Parse(HPCSNo.Substring(2, 2));
                            tb2.Rows[i]["列索引"] = int.Parse(HPCSNo.Substring(4, 2));
                        }
                    }
                }

                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 PNL布局信息 å¯¼å…¥(保存)
        [Route("Gy_PNLInfo/Gy_PNLInfo_btnSave")]
        [HttpPost]
        public object Gy_PNLInfo_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_PNLInfo_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);
                }

                oCN.BeginTran();
                int i = 1;
                foreach (Dictionary<string, string> item in list)
                {
                    string HInterID = "0";
                    string HLayOutName = item["料号"].ToString();                //料号
                    string HPNLInfoTypeID = item["HPNLInfoTypeID"].ToString();   //分类ID
                    string HPNLInfoTypeName = item["分类"].ToString();           //分类
                    string HSetNo = item["Set号"].ToString();                    //Set号
                    string HPCSNo = item["PCS号"].ToString();                    //PCS号
                    string HPosition_x = item["行索引"].ToString();              //行索引
                    string HPosition_y = item["列索引"].ToString();              //列索引

                    //检查数据是否完整
                    int index = i;

                    if (i == 1)
                    {
                        //查询料号资料是否已经存在
                        ds = oCN.RunProcReturn("select * from Gy_PNLInfo where HLayOutName = '" + HLayOutName + "' ", "Gy_PNLInfo");
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            objJsonResult.code = "0";
                            objJsonResult.count = 0;
                            objJsonResult.Message = "第" + index + "行,料号【+ " + HLayOutName + "】资料已经存在！";
                            objJsonResult.data = null;
                            return objJsonResult;
                        }

                        string HIcon = "/img/智能芯片＊.png";
                        string HMaker = user;

                        string sql_main = "insert into Gy_PNLInfo(HLayOutName,HIcon,HMaker,HMakeDate,HPNLInfoTypeID) " +
                            "values(" +
                            "'" + HLayOutName + "'" +
                            ",'" + HIcon + "'" +
                            ",'" + HMaker + "'" +
                            ",getdate()" +
                            "," + HPNLInfoTypeID + "" +
                            ")";
                        oCN.RunProc(sql_main);
                    }

                    string sql_getInterID = "select * from Gy_PNLInfo where HLayOutName = '" + HLayOutName + "'";
                    ds = oCN.RunProcReturn(sql_getInterID, "Gy_PNLInfo");
                    if (ds == null || ds.Tables[0].Rows.Count == 0)
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "PNL布局信息导入失败！！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }
                    else
                    {
                        HInterID = DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HInterID"]);
                    }

                    //查询 åˆ†ç±»
                    ds = oCN.RunProcReturn("select * from Gy_PNLInfoType where HName = '" + HPNLInfoTypeName + "' ", "Gy_PNLInfoType");
                    if (ds.Tables[0].Rows.Count == 0)
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "第" + index + "行,分类【+ " + HPNLInfoTypeName + "】不存在！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    //判断PCS号格式是否正确
                    if (!Regex.IsMatch(HPCSNo, @"^\d{6}$"))
                    {
                        objJsonResult.code = "0";
                        objJsonResult.count = 0;
                        objJsonResult.Message = "第" + index + "行,PCS号【" + HPCSNo + "】格式错误，应设置为由6位数字组成！";
                        objJsonResult.data = null;
                        return objJsonResult;
                    }

                    string sql_sub = "insert into Gy_PNLInfoSub(HInterID,HLayOutName,HSetNo,HPCSNo,HPosition_x,HPosition_y) " +
                        "values(" +
                        "" + HInterID + "" +
                        ",'" + HLayOutName + "'" +
                        ",'" + HSetNo + "'" +
                        ",'" + HPCSNo + "'" +
                        "," + HPosition_x + "" +
                        "," + HPosition_y + "" +
                        ")";
                    oCN.RunProc(sql_sub);
                    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
    }
}