MES-WEB-API.git

			@@ -9,6 +9,9 @@
			using System.Net.Http;
			using System.Web.Http;
			using WebAPI.Models;
			using System.Web;
			using WebAPI.Controllers.SCGL.日计划管理;
			using System.IO;

			namespace WebAPI.Controllers.SBGL
			{
			@@ -86,6 +89,70 @@
			}
			#endregion

			#region 设备点检规程单列表PDA
			[Route("Sb_EquipDotCheckRuleBill/GetEquipDotCheckRuleListPDA")]
			[HttpGet]
			public object GetEquipDotCheckRuleListPDA(string sWhere, string user)
			{
			try
			{
			List<object> columnNameList = new List<object>();
			if (!DBUtility.ClsPub.Security_Log("Sb_EquipDotCheckRuleBillList", 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_Sb_GetDotCheckRuleListByEquip " + sWhere , "h_v_Sb_GetDotCheckRuleListByEquip");
			}
			else
			{
			string sql1 = "select * from h_v_Sb_GetDotCheckRuleListByEquip where 1 = 1";
			string sql = sql1 + sWhere;
			ds = oCN.RunProcReturn(sql, "h_v_Sb_GetDotCheckRuleListByEquip");
			}

			//添加列名
			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列对象的列名
			}
			//if (ds.Tables[0].Rows.Count != 0 \|\| ds != null)
			//{
			objJsonResult.code = "1";
			objJsonResult.count = 1;
			objJsonResult.Message = "Sucess！";
			objJsonResult.data = ds.Tables[0];
			objJsonResult.list = columnNameList;
			return objJsonResult;
			//}
			//else
			//{
			//objJsonResult.code = "0";
			//objJsonResult.count = 0;
			//objJsonResult.Message = "无数据";
			//objJsonResult.data = null;
			//return objJsonResult;
			//}
			}
			catch (Exception ex)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "没有返回任何记录！" + ex.ToString();
			objJsonResult.data = null;
			return objJsonResult;
			}
			}
			#endregion

			#region 设备点检规程记录删除功能
			[Route("Sb_EquipDotCheckRuleBill/DeleteEquipDotCheckRuleBillList")]
			[HttpGet]
			@@ -113,6 +180,11 @@
			ds = oCN.RunProcReturn("select * from Sb_EquipDotCheckRuleBillMain where HInterID=" + HInterID, "Sb_EquipDotCheckRuleBillMain");
			if (ds.Tables[0].Rows.Count>0)
			{
			//数据库空值判断
			if (ds.Tables[0].Rows[0]["HBillStatus"] == DBNull.Value)
			{
			ds.Tables[0].Rows[0]["HBillStatus"] = 1;
			}
			if (int.Parse(ds.Tables[0].Rows[0]["HBillStatus"].ToString()) > 1)
			{
			objJsonResult.code = "0";
			@@ -470,5 +542,387 @@
			}
			}
			#endregion

			#region 设备点检规程单文件上传
			[Route("Sb_EquipDotCheckRuleBill/Sb_EquipDotCheckRuleBill_Excel")]
			[HttpPost]
			public object Sb_EquipDotCheckRuleBill_Excel()
			{
			try
			{

			//获取文件名称
			var file = HttpContext.Current.Request.Files[0];
			//获取文件物理路径
			string ExcelPath = HttpContext.Current.Server.MapPath("~/" + file.FileName);
			// 创建一个字典来存储name到单据号和HInterID的映射
			Dictionary<string, object[]> nameToValues = new Dictionary<string, object[]>();
			string HBillType = "3913";
			//保存文件
			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("HDotCheckItemID", typeof(Int32));//点检项目ID
			tb2.Columns.Add("HDotCheckItemClassID", typeof(Int32));//点检分类ID
			tb2.Columns.Add("HDotCheckItemMethodID", typeof(Int32));//点检方法ID
			tb2.Columns.Add("HEmpID", typeof(Int32));//负责人
			tb2.Columns.Add("单据号", typeof(string));//单据号
			tb2.Columns.Add("HInterID", typeof(Int64));//主键
			//添加数据
			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 (!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 HDotCheckItemNumber = "";
			string HDotCheckItemName = "";
			string HEmpNum = "";
			string HEmpName = "";

			HDotCheckItemNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["点检项目代码"].ToString());
			HDotCheckItemName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["点检项目名称"].ToString());
			HEmpNum = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["负责人代码"].ToString());
			HEmpName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["负责人名称"].ToString());
			string HDotCheckItemMethodNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["点检方法代码"].ToString());
			string HDotCheckItemMethodName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["点检方法名称"].ToString());

			string HEquipDotCheckRuleName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["点检规程名称"].ToString());
			string HCycleUnit = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["周期单位"].ToString());
			string HCheckCycle = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["点检周期"].ToString());
			string HExplanation = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["摘要"].ToString());
			string HMainRemark = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["表头备注"].ToString());
			string HDotCheckPart = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["点检部位"].ToString());
			string HClaim = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["具体要求"].ToString());
			string HSubRemark = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["表体备注"].ToString());

			int index = i + 1;
			//查询点检项目
			ds = oCN.RunProcReturn("select * from Gy_DotCheck where HNumber='" + HDotCheckItemNumber + "' and Hname='" + HDotCheckItemName + "'", "Gy_DotCheck");

			if (ds.Tables[0].Rows.Count == 0)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "第" + index + "行,点检项目不存在！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			else
			{
			tb2.Rows[i]["HDotCheckItemID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
			tb2.Rows[i]["HDotCheckItemClassID"] = ds.Tables[0].Rows[0]["HDotCheckItemClassID"].ToString();
			}
			//查询负责人
			ds = oCN.RunProcReturn("select * from Gy_Employee where HNumber='" + HEmpNum + "' and Hname='" + HEmpName + "'", "Gy_Employee");

			if (ds.Tables[0].Rows.Count == 0)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "第" + index + "行,该负责人:" + HEmpName + ",不存在！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			else
			{
			tb2.Rows[i]["HEmpID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
			}

			//查询点检方法
			ds = oCN.RunProcReturn("select * from Gy_DotCheckItemMethod where HNumber='" + HDotCheckItemMethodNumber + "' and Hname='" + HDotCheckItemMethodName + "'", "Gy_Employee");

			if (ds.Tables[0].Rows.Count == 0)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "第" + index + "行,该点检方法:" + HEmpName + ",不存在！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			else
			{
			tb2.Rows[i]["HDotCheckItemMethodID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
			}

			//设备点检规程名称
			if (HEquipDotCheckRuleName == "")
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "第" + index + "行,设备点检规程单名称不能为空！";
			objJsonResult.data = null;
			return objJsonResult;
			}

			//周期单位
			if (HCycleUnit == "" \|\| (HCycleUnit != "月" && HCycleUnit != "周" && HCycleUnit != "天"))
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "第" + index + "行,周期单位不能为空且只能为天，月，周！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			//点检周期
			if (HCheckCycle == "" )
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "第" + index + "行,点检周期不能为空！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			//设置单据HinterID和单据号


			Int64 HInterID;
			string HBillNo;
			object[] myArray = new object[2];
			// 如果字典中还没有这个点检规程的条目，就添加它
			if (!nameToValues.ContainsKey(HEquipDotCheckRuleName))
			{
			HInterID = DBUtility.ClsPub.CreateBillID(HBillType, ref DBUtility.ClsPub.sExeReturnInfo);
			HBillNo = DBUtility.ClsPub.CreateBillCode(HBillType, ref DBUtility.ClsPub.sExeReturnInfo, true);
			myArray[0] = HInterID;
			myArray[1] = HBillNo;
			nameToValues[HEquipDotCheckRuleName] = myArray;
			tb2.Rows[i]["HInterID"] = HInterID;
			tb2.Rows[i]["单据号"] = HBillNo;
			}
			// 如果字典中有这个年龄的条目，但是当前行的名称不是字典中的名称，就更新它
			else if (true)
			{
			tb2.Rows[i]["HInterID"] = nameToValues[HEquipDotCheckRuleName][0];
			tb2.Rows[i]["单据号"] = nameToValues[HEquipDotCheckRuleName][1];// 更新当前行单据号和id
			}

			}
			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("Sb_EquipDotCheckRuleBill/Sb_EquipDotCheckRuleBill_btnSave")]
			[HttpPost]
			public object Sb_EquipDotCheckRuleBill_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("Sb_EquipDotCheckRuleBill_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 = item["HInterID"].ToString();
			string HBillNo = item["单据号"].ToString();
			//string HInterID = item["点检规程名称"].ToString();
			string HCycleUnit = item["周期单位"].ToString();
			string HCheckCycle = item["点检周期"].ToString();
			string HExplanation = item["摘要"].ToString();
			string HMainRemark = item["表头备注"].ToString();
			string HDotCheckItemID = item["HDotCheckItemID"].ToString();
			string HDotCheckItemClassID = item["HDotCheckItemClassID"].ToString();
			string HDotCheckItemMethodID = item["HDotCheckItemMethodID"].ToString();
			string HDotCheckItem = item["点检项目名称"].ToString();
			string HDotCheckPart = item["点检部位"].ToString();
			string HClaim = item["具体要求"].ToString();
			string HEmpID = item["HEmpID"].ToString();
			string HSubRemark = item["表体备注"].ToString();
			string HBillType = "3913";
			string HBillSubType = "3913";
			DateTime HDate = DBUtility.ClsPub.isDate(DateTime.Now.ToString("yyyy-MM-dd"));
			Int64 HYear = DBUtility.ClsPub.isLong(DateTime.Now.Year);
			Int64 HPeriod = DBUtility.ClsPub.isLong(DateTime.Now.Month);
			DataSet Cs;
			Int64 HEntryID = 1;
			Cs = oCN.RunProcReturn("select MAX(HEntryID)HEntryID from Sb_EquipDotCheckRuleBillSub", "Sb_EquipDotCheckRuleBillSub");
			if (Cs.Tables[0].Rows.Count != 0 && ClsPub.isLong(Cs.Tables[0].Rows[0]["HEntryID"].ToString()) != 0)
			{
			HEntryID = ClsPub.isLong(Cs.Tables[0].Rows[0]["HEntryID"].ToString());
			HEntryID += 1;
			}

			ds = oCN.RunProcReturn("select * from Sb_EquipDotCheckRuleBillMain where HInterID =" + HInterID, "Sb_EquipDotCheckRuleBillMain");

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

			//主表
			oCN.RunProc("Insert Into Sb_EquipDotCheckRuleBillMain " +
			"(HBillType,HBillSubType,HInterID,HBillNo,HDate,HMaker,HMakeDate,HBillStatus" +
			",HYear,HPeriod,HRemark" +
			",HBeginDate,HEndDate,HCycleUnit,HCheckCycle,HExplanation" +
			") " +
			" values('" + HBillType + "','" + HBillSubType + "'," + HInterID.ToString() + ",'" + HBillNo + "','" + HDate + "','" + user + "',getdate(),1" +
			"," + HYear.ToString() + "," + HPeriod.ToString() + ",'" + HMainRemark + "'" +
			",getdate()" + ",getdate()" + ",'" + HCycleUnit + "','" + HCheckCycle + "','" + HExplanation + "'" +
			") ");
			//插入子表
			oCN.RunProc("Insert into Sb_EquipDotCheckRuleBillSub " +
			" (HInterID,HEntryID," +
			"HRemark," +
			"HDotCheckItemID,HDotCheckItem,HDotCheckPart,HClaim," +
			"HManagerID,HDotCheckItemClassID,HDotCheckItemMethodID" +
			") values("
			+ HInterID.ToString() + "," + HEntryID.ToString() +
			",'" + HSubRemark + "'" +
			",'" + HDotCheckItemID + "','" + HDotCheckItem + "','" + HDotCheckPart + "','" + HClaim +
			"'," + HEmpID + "," + HDotCheckItemClassID + "," + HDotCheckItemMethodID +
			") ");
			}
			else
			{
			oCN.RunProc("Insert into Sb_EquipDotCheckRuleBillSub " +
			" (HInterID,HEntryID," +
			"HRemark," +
			"HDotCheckItemID,HDotCheckItem,HDotCheckPart,HClaim," +
			"HManagerID,HDotCheckItemClassID,HDotCheckItemMethodID" +
			") values("
			+ HInterID.ToString() + "," + HEntryID.ToString() +
			",'" + HSubRemark + "'" +
			",'" + HDotCheckItemID + "','" + HDotCheckItem + "','" + HDotCheckPart + "','" + HClaim +
			"'," + HEmpID + "," + HDotCheckItemClassID + "," + HDotCheckItemMethodID +
			") ");
			}

			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

			}
			}