MES-WEB-API.git

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

			using System.Web;
			using WebAPI.Controllers.SCGL.日计划管理;
			using System.IO;
			using SyntacticSugar.constant;
			namespace WebAPI.Controllers.SBGL
			{
			public class Sb_EquipMaintainRuleBillController : ApiController
			@@ -616,5 +619,383 @@
			}
			}
			#endregion

			#region 设备保养规程文件导入保存
			#region 设备保养规程文件上传
			[Route("Sb_EquipMaintainRuleBill/Sb_EquipMaintainRuleBill_Excel")]
			[HttpPost]
			public object Sb_EquipMaintainRuleBill_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("HMaintainItemID", typeof(Int32));//保养项目ID
			tb2.Columns.Add("HManagerID", 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 (!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 HName = "";
			string HNum = "";
			string HORGNumber = "";
			string HORGName = "";
			string HManagerName = "";
			string HManagerNumber = "";
			string HMaintainItemNumber = "";
			string HMaintainItemName = "";

			HName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["规程编号"].ToString());
			HNum = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["规程名称"].ToString());
			HORGNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织代码"].ToString());
			HORGName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["组织名称"].ToString());
			HManagerNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["负责人代码"].ToString());
			HManagerName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["负责人"].ToString());
			HMaintainItemNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["保养项目代码"].ToString());
			HMaintainItemName = 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();
			}
			else
			{
			objJsonResult.code = CodeConstant.FAIL;
			objJsonResult.count = CountConstant.FAIL;
			objJsonResult.Message = "第" + index + "行,组织代码为空";
			objJsonResult.data = null;
			return objJsonResult;
			}
			//保养项目代码
			if (HMaintainItemNumber != "")
			{
			//查询组织
			ds = oCN.RunProcReturn("select * from Gy_Maintain where HNumber='" + HMaintainItemNumber + "' and Hname='" + HMaintainItemName + "'", "Gy_Maintain");

			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]["HMaintainItemID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
			}
			string HMaintainItemID = ds.Tables[0].Rows[0]["HItemID"].ToString();
			}
			else
			{
			objJsonResult.code = CodeConstant.FAIL;
			objJsonResult.count = CountConstant.FAIL;
			objJsonResult.Message = "第" + index + "行,保养项目代码为空";
			objJsonResult.data = null;
			return objJsonResult;
			}
			//负责人代码
			if (HManagerNumber != "")
			{
			//查询组织
			ds = oCN.RunProcReturn("select * from Gy_Employee where HNumber='" + HManagerNumber + "' and Hname='" + HManagerName + "'", "Gy_Employee");

			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]["HManagerID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
			}
			}
			//设备保养规程名称
			if (HName == "")
			{
			objJsonResult.code = CodeConstant.FAIL;
			objJsonResult.count = CountConstant.FAIL;
			objJsonResult.Message = "第" + index + "行,设备保养规程名称不能为空！";
			objJsonResult.data = null;
			return objJsonResult;
			}

			//设备保养规程代码
			if (HNum == "")
			{
			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("Sb_EquipMaintainRuleBill/Sb_EquipMaintainRuleBill_btnSave")]
			[HttpPost]
			public object Sb_EquipMaintainRuleBill_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_EquipMaintainRuleBill_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 HBillType = "3912";
			string HMaker = user;//制单人
			DateTime HDate = DBUtility.ClsPub.isDate(DateTime.Now.ToString("yyyy-MM-dd"));// --日期
			long HYear = DBUtility.ClsPub.isLong(DateTime.Now.Year);
			long HPeriod = DBUtility.ClsPub.isLong(DateTime.Now.Month);
			string HOrgID = item["HOrgID"].ToString();//组织ID
			string HName = item["规程名称"].ToString();
			string HBillNo = item["规程编号"].ToString();
			string HCycleUnit = item["周期单位"].ToString();
			string HCheckCycle = item["保养周期"].ToString();
			string HExplanation = item["摘要"].ToString();
			string HRemark = item["表头备注"].ToString();
			//子表
			string HMaintainItemID = item["HMaintainItemID"].ToString();//保养项目
			string HManagerID = item["HManagerID"].ToString() == "" ? "0" : item["HManagerID"].ToString();//负责人
			string HMaintainPart = item["保养部位"].ToString();
			string HClaim = item["具体要求"].ToString();
			string HSubRemark = item["表体备注"].ToString();
			string HMaintainItem = item["保养项目名称"].ToString();
			ds = oCN.RunProcReturn("select * from Sb_EquipMaintainRuleBillMain where HBillNo='" + HBillNo + "'", "Sb_EquipMaintainRuleBillMain");

			if (ds.Tables[0].Rows.Count == 0)
			{
			long HInterID = DBUtility.ClsPub.CreateBillID(HBillType, ref DBUtility.ClsPub.sExeReturnInfo);
			//插入主表
			oCN.RunProc("Insert Into Sb_EquipMaintainRuleBillMain " +
			"(HBillType,HBillSubType,HInterID,HBillNo,HDate,HMaker,HMakeDate" +
			",HYear,HPeriod,HRemark" +
			",HCheckCycle,HCycleUnit,HExplanation,HInnerBillNo,HMaintainLevID,HSafeDays,HErrWarDays" +
			") " +
			" values('" + HBillType + "','" + HBillType + "'," + HInterID.ToString() + ",'" + HBillNo + "','" + HDate + "','" + HMaker + "',getdate()" +
			"," + HYear.ToString() + "," + HPeriod.ToString() + ",'" + HRemark +
			"'," + HCheckCycle.ToString() + ",'" + HCycleUnit + "','" + HExplanation + "','" + "" + "'," + 0 + "," + 0 + ", " + 0 +
			") ");
			//插入子表
			oCN.RunProc("Insert into Sb_EquipMaintainRuleBillSub_Item" +
			" (HInterID,HEntryID,HMaintainItemID,HMaintainItem" +
			",HMaintainPart,HClaim" +
			",HManagerID" +
			",HCloseMan,HCloseType,HRemark" +
			",HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney" +
			") values("
			+ HInterID.ToString() + "," + 1 + "," + HMaintainItemID.ToString() + ",'" + HMaintainItem.ToString() + "'" +
			",'" + HMaintainPart.ToString() + "','" + HClaim.ToString() + "'" +
			"," + HManagerID.ToString() +
			",'" + "" + "'," + Convert.ToString(false ? 1 : 0) + ",'" + HSubRemark + "'" +
			"," + 0 + "," + 0 + ",'" + "" + "','" + "" + "'," + 0 + "," + 0 +
			") ");
			}
			else
			{
			long HInterID = long.Parse(ds.Tables[0].Rows[0]["HInterID"].ToString());
			//获取最大子id
			DataSet ds2 = oCN.RunProcReturn("select max(HEntryID) HEntryID from Sb_EquipMaintainRuleBillSub_Item where HInterID='" + HInterID + "'", "Sb_EquipMaintainRuleBillSub_Item");
			long HEntryID = 1;
			if (ds2.Tables[0].Rows.Count > 0)
			{
			HEntryID = long.Parse(ds2.Tables[0].Rows[0]["HEntryID"].ToString()) + 1;
			}
			//插入子表
			//插入子表
			oCN.RunProc("Insert into Sb_EquipMaintainRuleBillSub_Item" +
			" (HInterID,HEntryID,HMaintainItemID,HMaintainItem" +
			",HMaintainPart,HClaim" +
			",HManagerID" +
			",HCloseMan,HCloseType,HRemark" +
			",HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRelationQty,HRelationMoney" +
			") values("
			+ HInterID.ToString() + "," + HEntryID + "," + HMaintainItemID.ToString() + ",'" + HMaintainItem.ToString() + "'" +
			",'" + HMaintainPart.ToString() + "','" + HClaim.ToString() + "'" +
			"," + HManagerID.ToString() +
			",'" + "" + "'," + Convert.ToString(false ? 1 : 0) + ",'" + HSubRemark + "'" +
			"," + 0 + "," + 0 + ",'" + "" + "','" + "" + "'," + 0 + "," + 0 +
			") ");
			}

			i++;
			}

			oCN.Commit();

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

			}
			}