~yxj/MES-WEB-API.git

			@@ -1,11 +1,16 @@
			using Newtonsoft.Json.Linq;
			using Newtonsoft.Json;
			using Newtonsoft.Json.Linq;
			using Pub_Class;
			using SQLHelper;
			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
			@@ -19,7 +24,13 @@
			SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
			DataSet ds;

			DAL.ClsIF_Material_View oMaterHlp = new DAL.ClsIF_Material_View();
			public DAL.ClsGy_Process_View oProcHlp = new DAL.ClsGy_Process_View();
			DAL.ClsGy_WorkCenter_View oCenHlp = new DAL.ClsGy_WorkCenter_View();
			Gy_RoutingBill_temporary omdelMian = new Gy_RoutingBill_temporary();
			public string BillType = "3301";

			#region 工艺路线列表
			/// <summary>
			/// 返回工艺路线列表
			///参数：string sql。
			@@ -68,6 +79,9 @@
			}
			}

			#endregion

			#region 工艺路线新增
			/// <summary>
			/// 新增单据-保存按钮
			///参数：string sql。
			@@ -122,7 +136,7 @@

			msg2 = "[" + msg2.ToString() + "]";
			List<Gy_RoutingBill> mainList = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Gy_RoutingBill>>(msg2);
			string BillType = "3301";

			long HInterID = mainList[0].HInterID;//递入type得到的单据ID
			string HBillNo = mainList[0].HBillNo;//递入type得到的单据号
			DateTime HDate = mainList[0].HDate;//日期
			@@ -150,7 +164,6 @@
			double HDelSourceRate = mainList[0].HDelSourceRate;//减资源减量
			int HPRDORGID = mainList[0].HPRDORGID; //生产组织
			int HOrgID = mainList[0].HOrgID; //组织

			string HPicNumVer = mainList[0].HPicNumVer;//图号版本
			string HPicNumAssemble = mainList[0].HPicNumAssemble;//总装图号
			string HMaterTexture = mainList[0].HMaterTexture;//材质
			@@ -292,6 +305,538 @@
			return objJsonResult;
			}

			//
			#endregion

			#region 工艺路线文件上传
			[Route("Gy_RoutingBill/Gy_RoutingBillimport")]
			[HttpPost]
			public object Gy_RoutingBillimport()
			{
			try
			{
			var WorkBookName = HttpContext.Current.Request["WorkBookName"];
			//获取文件名称
			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,2, WorkBookName);

			//删除文件
			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());
			}

			//添加数据
			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 (!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;
			}

			Checkdata(tb2);

			string hnumber = "";
			if (tb2.Rows.Count > 0)
			{
			hnumber = DBUtility.ClsPub.isStrNull(tb2.Rows[0]["物料代码"].ToString());
			}

			for (int i = 0; i <= tb2.Rows.Count - 1; i++)
			{
			string sHNumber = "";
			if (DBUtility.ClsPub.isStrNull(tb2.Rows[i]["物料代码"].ToString()) != "")
			{
			//
			sHNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["物料代码"].ToString());
			//审核代码是否合理
			if (!DBUtility.ClsPub.AllowNumber(sHNumber))
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "物料代码中不能出现连续‘.’并且首位末位不能为‘.’！";
			objJsonResult.data = null;
			return objJsonResult;
			}

			if (hnumber == DBUtility.ClsPub.isStrNull(tb2.Rows[i]["物料代码"].ToString()))
			{
			//得到物料内码
			if (!oMaterHlp.GetInfoByNumber(sHNumber))
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "第" + i.ToString() + "行," + sHNumber + "没有找到对应的物料！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			}
			else
			{
			DataSet Ds;
			ClsCN oCn = new ClsCN();
			bool b = false;
			// 判断是否存在工艺路线
			Ds = oCn.RunProcReturn("Select HMaterID from Gy_RoutingBillMain Where HMaterID=" + oMaterHlp.omodel.HItemID.ToString(), "Gy_RoutingBillMain");


			hnumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["物料代码"].ToString());
			sHNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["物料代码"].ToString());

			//审核代码是否合理
			if (!DBUtility.ClsPub.AllowNumber(sHNumber))
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "物料代码中不能出现连续‘.’并且首位末位不能为‘.’！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			//得到物料内码
			if (!oMaterHlp.GetInfoByNumber(sHNumber))
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "第" + i.ToString() + "行," + sHNumber + "没有找到对应的物料！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			}
			}
			else
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "第" + i.ToString() + "行,没有找到对应的物料！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			}

			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;
			}
			}

			//检查物料
			private object Checkdata(DataTable dt)
			{
			bool b = false;
			string sErrMsg = "";
			string sMsg = "";
			for (int i = 0; i <= dt.Rows.Count - 1; i++)
			{
			string sHNumber = "";
			string sHNamePoc = "";
			string sHNumberCen = "";
			Single sWorkQty = 0;
			Single sHPassRate = 0;

			//
			sHNumber = DBUtility.ClsPub.isStrNull(dt.Rows[i]["物料代码"].ToString());
			sHNumberCen = DBUtility.ClsPub.isStrNull(dt.Rows[i]["工作中心名称"].ToString());
			sHNamePoc = DBUtility.ClsPub.isStrNull(dt.Rows[i]["工序"].ToString());
			sWorkQty = DBUtility.ClsPub.isSingle(dt.Rows[i]["加工数量"].ToString());
			sHPassRate = DBUtility.ClsPub.isSingle(dt.Rows[i]["良率"].ToString());
			if (sHNumber != "")
			{
			//
			int index = i + 1;
			//加工数量不能小于等于零
			if (sWorkQty <= 0)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "第" + index + "行加工数量不能小于等于零！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			//良率不能小于等于零
			if (sHPassRate <= 0)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "第" + index + "行良率不能小于等于零！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			//审核代码是否合理
			if (!DBUtility.ClsPub.AllowNumber(sHNumber))
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "物料代码中不能出现连续‘.’并且首位末位不能为‘.’！";
			objJsonResult.data = null;
			return objJsonResult;
			}
			//得到物料内码
			int j = i + 2;
			if (!oMaterHlp.GetInfoByNumber(sHNumber))
			{
			sMsg = "[" + sHNumber + "]物料不存在";
			if (sErrMsg.Contains(sMsg) == false)
			{
			sErrMsg = sErrMsg + "[" + sHNumber + "]物料不存在\r\n";
			}
			b = true;
			}
			//得到工序内码
			if (!oProcHlp.GetInfoByName(sHNamePoc))
			{
			sMsg = "[" + sHNamePoc + "]工序不存在";
			if (sErrMsg.Contains(sMsg) == false)
			{
			sErrMsg = sErrMsg + "[" + sHNamePoc + "]工序不存在\r\n";
			}
			b = true;
			}

			//得到工作中心内码
			if (!oCenHlp.GetInfoByName(sHNumberCen))
			{
			sMsg = "[" + sHNumberCen + "]工作中心不存在";
			if (sErrMsg.Contains(sMsg) == false)
			{
			sErrMsg = sErrMsg + "[" + sHNumberCen + "]工作中心不存在\r\n";
			}
			b = true;

			}

			}
			}
			if (b == true)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = sErrMsg;
			objJsonResult.data = null;
			return objJsonResult;
			}
			else
			{
			objJsonResult.code = "1";
			objJsonResult.count = 1;
			objJsonResult.Message = sErrMsg;
			objJsonResult.data = null;
			return objJsonResult;
			}

			}
			#endregion

			#region 工艺路线导入(保存)
			//临时字段
			public class Gy_RoutingBill_temporary
			{
			public string HYear;
			public string HPeriod;
			public string DataAppend;
			public string HOrgID;
			}

			[Route("Gy_RoutingBill/Gy_RoutingBill_btnSave")]
			[HttpPost]
			public object Gy_RoutingBill_btnSave([FromBody] JObject sMainSub)
			{
			var _value = sMainSub["sMainSub"].ToString();
			string msg1 = _value.ToString();
			oCN.BeginTran();
			//保存主表
			objJsonResult = RoutAddBillMain(msg1);
			if (objJsonResult.code == "0")
			{
			oCN.RollBack();
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = objJsonResult.Message;
			objJsonResult.data = null;
			return objJsonResult;
			}
			oCN.Commit();
			objJsonResult.code = "1";
			objJsonResult.count = 1;
			objJsonResult.Message = "单据保存成功！";
			objJsonResult.data = null;
			return objJsonResult;
			}

			public json RoutAddBillMain(string msg1)
			{
			string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
			string msg2 = sArray[0].ToString();
			string msg3 = sArray[1].ToString();
			string user = sArray[2].ToString();

			try
			{
			if (!DBUtility.ClsPub.Security_Log("Gy_RoutingBill_Edit", 1, false, user))
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "无保存权限！";
			objJsonResult.data = null;
			return objJsonResult;
			}

			//表头字段
			omdelMian = Newtonsoft.Json.JsonConvert.DeserializeObject<Gy_RoutingBill_temporary>(msg2);

			//JSON序列化转换字典集合
			List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
			List<object> jb = JsonConvert.DeserializeObject<List<object>>(msg3);
			foreach (JObject item in jb)
			{
			Dictionary<string, string> dic = new Dictionary<string, string>();
			foreach (var itm in item.Properties())
			{
			dic.Add(itm.Name, itm.Value.ToString());
			}
			list.Add(dic);
			}



			int TrueCount = 0, SumCount = 0;

			for (int i = 0; i < list.Count; i++)
			{
			long HInterID = 0;
			var HBillNo = "";
			if (list[i]["单据号"].ToString() == "" && list[i]["hmainid"].ToString() == "")
			{
			HBillNo = DBUtility.ClsPub.CreateBillCode_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo, true);//获得一个新的单据号
			HInterID = DBUtility.ClsPub.CreateBillID_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo);//获得一个新的id
			var HICMOBillNo = list[i]["生产订单号*"].ToString();
			var HICMOEntrySEQ = list[i]["生产订单明细行号*"].ToString();
			var HWorkShopID = list[i]["HWorkShopID"].ToString();
			var HSourceID = list[i]["HSourceID"].ToString();
			var HYX = list[i]["优先级"].ToString();
			var HEmpID = list[i]["HEmpID"].ToString();
			var HMaterID = list[i]["HMaterID"].ToString();
			var HMaterName = list[i]["物料名称"].ToString();
			var HMaterModel = list[i]["规格型号"].ToString();
			var HUnitID = list[i]["HUnitID"].ToString();
			var HBatchNo = list[i]["批次号"].ToString();
			var HSeOrderBillQty = list[i]["销售订单数量"].ToString();
			var HPlanQty = list[i]["生产订单数量"].ToString();
			var HCompleteQty = list[i]["总齐套数量"].ToString();
			var HOrderType = "";
			var HDate = "";
			var HMainSourceInterID = list[i]["ICMOBillHInterID"].ToString();
			var HMainSourceEntryID = list[i]["ICMOBillHEntryID"].ToString();

			ds = oCN.RunProcReturn($"select * from Sc_WorkBillSortBillMain where HMainSourceInterID={HMainSourceInterID}" +
			$" and HMainSourceEntryID={HMainSourceEntryID} and HICMOBillNo='{HICMOBillNo}' and HICMOEntrySEQ={HICMOEntrySEQ} and HSourceID={HSourceID}", "Sc_WorkBillSortBillMain");
			if (ds.Tables[0].Rows.Count > 0)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = $"第{i + 1}行生产资源有重复,请修改!";
			objJsonResult.data = null;
			return objJsonResult;
			}

			//主表
			oCN.RunProc("insert into Sc_WorkBillSortBillMain(HInterID,HBillNo,HYear,HPeriod,HBillType," +
			"HBillSubType,HDate,HBillStatus,HMaker,HMakeDate,HICMOBillNo,HOrderType," +
			"HICMOEntrySEQ,HWorkShopID,HSourceID, HYX, HEmpID," +
			" HMaterID, HMaterName, HMaterModel, HUnitID, HBatchNo," +
			" HSeOrderBillQty, HPlanQty, HCompleteQty,HPreparatDate," +
			"HMainSourceInterID,HMainSourceEntryID)values" +
			$"({HInterID},'{HBillNo}',{DateTime.Now.Year},{DateTime.Now.Month},'{BillType}'," +
			$"'{BillType}',GETDATE(),1,'{user}','{HDate}','{HICMOBillNo}','{HOrderType}'," +
			$"{(HICMOEntrySEQ == "" ? 0.ToString() : HICMOEntrySEQ)},{(HWorkShopID == "" ? 0.ToString() : HWorkShopID)},{(HSourceID == "" ? 0.ToString() : HSourceID)}, {(HYX == "" ? 0.ToString() : HYX)}, {(HEmpID == "" ? 0.ToString() : HEmpID)}," +
			$" {(HMaterID == "" ? 0.ToString() : HMaterID)}, '{HMaterName}', '{HMaterModel}', {(HUnitID == "" ? 0.ToString() : HUnitID)}, '{HBatchNo}'," +
			$" {(HSeOrderBillQty == "" ? 0.ToString() : HSeOrderBillQty)}, {(HPlanQty == "" ? 0.ToString() : HPlanQty)}, {(HCompleteQty == "" ? 0.ToString() : HCompleteQty)},getdate()," +
			$" {HMainSourceInterID},{HMainSourceEntryID})");
			}

			int SumDay = 31; //动态两月之差 DateTime.Now.AddMonths(1).AddDays(-1).Subtract(DateTime.Now).Days;

			for (int j = 0; j < SumDay; j++)
			{
			//主表子表都有数据
			if (list[i][DateTime.Now.AddDays(j).ToString("yyyy-MM-dd")].ToString() != "")
			{
			SumCount += 1;
			////保存子表
			objJsonResult = RoutAddBillSub(HInterID == 0 ? list[i]["hmainid"].ToString() : HInterID.ToString()
			, HBillNo == "" ? list[i]["单据号"].ToString() : HBillNo
			, DateTime.Parse(DateTime.Now.AddDays(j).ToString("yyyy-MM-dd").ToString())
			, int.Parse(list[i][DateTime.Now.AddDays(j).ToString("yyyy-MM-dd")].ToString()));

			if (objJsonResult.count == 1)
			{
			TrueCount += 1;
			}
			}
			//主表有数据子表无数据
			if (j == 30 && SumCount == 0 && HInterID != 0 && HBillNo != "")
			{
			objJsonResult = RoutAddBillSub(HInterID.ToString(), HBillNo, DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd").ToString()), 0);
			}
			}
			}

			if (TrueCount != SumCount)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = objJsonResult.Message;
			objJsonResult.data = null;
			return objJsonResult;
			}

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

			public json RoutAddBillSub(string HInterID, string HBillNo, DateTime date, int HQTY)
			{
			//获取表格数据
			ds = oCN.RunProcReturn($"select * from h_v_JIT_Sc_WorkBillSortBillList where 单据号='{HBillNo}'", "h_v_JIT_Sc_WorkBillSortBillList");

			int count = 0;
			if (ds.Tables[0].Rows.Count > 0)
			{
			//判断子表是否有当日的日期以及日期所对应的数量
			for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
			{
			if (DateTime.Parse(ds.Tables[0].Rows[i]["日计划生产日期"].ToString()) == date)
			{
			oCN.RunProc($"update Sc_WorkBillSortBillSub set HMasterDate='{date}',HQty={HQTY}" +
			$" where HInterID={HInterID} and HEntryID={ds.Tables[0].Rows[i]["hsubid"].ToString()}");
			count += 1;
			}
			}
			}

			if (count != 1)
			{
			//插入子表
			oCN.RunProc("insert into Sc_WorkBillSortBillSub(HInterID,HEntryID," +
			"HMasterDate,HQty)" +
			$"values({HInterID}, {(ds.Tables[0].Rows.Count + 1)}," +
			$" '{date}', {HQTY})");
			}

			objJsonResult.code = "1";
			objJsonResult.count = 1;
			objJsonResult.Message = null;
			objJsonResult.data = null;
			return objJsonResult;
			}
			#endregion
			}
			}