MES-WEB-API.git

			@@ -10,6 +10,10 @@
			using WebAPI.Models;
			using System.Text.RegularExpressions;
			using SyntacticSugar.constant;
			using System.Web;
			using WebAPI.Controllers.SCGL.日计划管理;
			using System.IO;
			using SyntacticSugar.constant;
			namespace WebAPI.Controllers
			{
			public class Gy_SupplierController : ApiController
			@@ -254,19 +258,8 @@
			{
			try
			{
			string sql = string.Format(@"select s.HItemID,s.HUSEORGID,o.Hname 使用组织,s.HCREATEORGID ,d.Hname 创建组织,s.HNumber 编码,s.HName 名称,s.HHelpCode 助记码,s.HShortName 简称,s.HAddress 地址,s.HLinkMan 联系人,s.HLinkPhone 联系电话
			,s.HMobilePhone 手机,s.HFax 传真,s.HPostalCode 邮编,s.HEmail 电子邮件,s.HCorMan 法人代表,s.HBank 开户银行,s.HBankAccount 银行账号,s.HPSStyleID 采购方式ID,f.HName 采购方式名称,
			s.HTaxNum 税务登记号,s.HEnglishName 英文名称,s.HEngLishAddress 英文地址,s.HCountry 国家,s.HStopflag 禁用标志
			,s.HRemark 备注,s.HAreaID 地区ID,a.HName 地区名称,s.HTaxRate 增值税率,s.HCurID 默认货币ID,r.HName 默认货币,
			s.HSSID 结算方式ID,e.HName 结算方式,s.HMaterClass 供货材料类别 from Gy_Supplier s
			left join Gy_AreaSet a on s.HAreaID=a.HItemID
			left join Gy_Currency r on s.HCurID=r.HItemID
			left join Gy_SettleStyle e on s.HSSID=e.HItemID
			left join Xt_ORGANIZATIONS o on s.HUSEORGID=o.HItemID
			left join Xt_ORGANIZATIONS d on s.HCREATEORGID=d.HItemID
			left join Gy_PoStockStyle f on s.HPSStyleID=f.HItemID
			where s.HItemID='" + HInterID + "'");
			ds = oCN.RunProcReturn(sql, "Gy_Supplier");
			string sql = string.Format(@"select * from h_v_Gy_SupplierEdit where HItemID='" + HInterID + "'");
			ds = oCN.RunProcReturn(sql, "h_v_Gy_SupplierEdit");
			DataSet cs = oCN.RunProcReturn("select * from Gy_Supplier_PayPlan where HSupID = '"+ HInterID + "'", "Gy_Supplier_PayPlan");
			List<Object> list = new List<object>();
			list.Add(cs);
			@@ -348,6 +341,7 @@
			string HRemark = list[0].HRemark;
			string HMaterClass = list[0].HMaterClass;
			long HPSStyleID = list[0].HPSStyleID;
			long HSupTypeID = list[0].HSupTypeID;

			if (!DBUtility.ClsPub.AllowNumber(HNumber))
			{
			@@ -366,13 +360,13 @@
			",HShortName,HAddress,HLinkMan,HLinkPhone,HMobilePhone" +
			",HFax,HPostalCode,HEmail,HBank,HBankAccount" +
			",HTaxNum,HTaxRate,HCountry,HCorMan,HEnglishName" +
			",HEnglishAddress,HCurID,HSSID,HAreaID,HMaterClass,HUSEORGID,HCREATEORGID,HPSStyleID) " +
			",HEnglishAddress,HCurID,HSSID,HAreaID,HMaterClass,HUSEORGID,HCREATEORGID,HPSStyleID,HSupTypeID) " +
			" values('" + HNumber + "','" + HName + "','" + HHelpCode + "','" + HShortNumber + "','" + HParentID + "'" +
			"," + HLevel + ",1,'" + HStopflag + "','" + HRemark + "'" +
			",'" + HShortName + "','" + HAddress + "','" + HLinkMan + "','" + HLinkPhone + "','" + HMobilePhone + "'" +
			",'" + HFax + "','" + HPostalCode + "','" + HEmail + "','" + HBank + "','" + HBankAccount + "'" +
			",'" + HTaxNum + "','" + HTaxRate + "','" + HCountry + "','" + HCorMan + "','" + HEnglishName + "'" +
			",'" + HEnglishAddress + "'," + HCurID + "," + HSSID + "," + HAreaID +" ,'" + HMaterClass + " ','" + HUseOrgID + "' ,'" + HCreateOrgID + "' ,'" + HPSStyleID +
			",'" + HEnglishAddress + "'," + HCurID + "," + HSSID + "," + HAreaID +" ,'" + HMaterClass + " ','" + HUseOrgID + "' ,'" + HCreateOrgID + "' ,'" + HPSStyleID+ "' ,'" + HSupTypeID +
			"') ");
			//修改上级为非末级代码
			oCN.RunProc("Update Gy_Supplier set HEndflag=0 where HItemID=" + HParentID, ref DBUtility.ClsPub.sExeReturnInfo);
			@@ -528,6 +522,7 @@
			string HRemark = list[0].HRemark;
			string HMaterClass = list[0].HMaterClass;
			long HPSStyleID = list[0].HPSStyleID;
			long HSupTypeID = list[0].HSupTypeID;

			//判断权限
			if (!ClsPub.Security_Log(msg6, 1, false, msg5))
			@@ -581,6 +576,7 @@
			",HSSID=" + HSSID.ToString() +
			",HMaterClass='" + HMaterClass +
			"',HPSStyleID='"+ HPSStyleID.ToString() +
			"',HSupTypeID='" + HSupTypeID.ToString() +
			"',HUSEORGID='" + HUseOrgID.ToString() +
			"',HCREATEORGID='" + HCreateOrgID.ToString() +
			"',HAreaID=" + HAreaID.ToString() + "Where HItemID=" + HItemID, ref DBUtility.ClsPub.sExeReturnInfo);
			@@ -1175,5 +1171,457 @@
			}

			#endregion

			#region 供应商文件导入保存
			#region 供应商文件上传
			[Route("Gy_Supplier/Gy_Supplier_Excel")]
			[HttpPost]
			public object Gy_Supplier_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("HCurID", typeof(Int32));//币别ID
			tb2.Columns.Add("HSSID", typeof(Int32));//结算方式ID
			tb2.Columns.Add("HAreaID", 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 (!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 HCurNumber = "";
			string HCurName = "";
			string HSSNumber = "";
			string HSSName = "";
			string HAreaNumber = "";
			string HAreaName = "";

			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());
			HCurNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["币别代码"].ToString());
			HCurName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["币别"].ToString());
			HSSNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["结算方式代码"].ToString());
			HSSName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["结算方式"].ToString());
			HAreaNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["地区代码"].ToString());
			HAreaName = 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();
			}

			if (HCurNumber != "")
			{
			//查询币别
			ds = oCN.RunProcReturn("select * from Gy_Currency where HNumber='" + HCurNumber + "' and Hname='" + HCurName + "'", "Gy_Currency");

			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]["HCurID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
			}
			}
			else
			{
			tb2.Rows[i]["HCurID"] = 0;
			}

			if (HSSNumber != "")
			{
			//查询结算方式
			ds = oCN.RunProcReturn("select * from Gy_SettleStyle where HNumber='" + HSSNumber + "' and Hname='" + HSSName + "'", "Gy_SettleStyle");

			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]["HSSID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
			}
			}
			else
			{
			tb2.Rows[i]["HSSID"] = 0;
			}
			if (HAreaNumber != "")
			{
			//查询地区
			ds = oCN.RunProcReturn("select * from Gy_AreaSet where HNumber='" + HAreaNumber + "' and Hname='" + HAreaName + "'", "Gy_AreaSet");

			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]["HAreaID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
			}
			}
			else
			{
			tb2.Rows[i]["HAreaID"] = 0;
			}

			//供应商名称
			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;
			}
			}
			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_Supplier/Gy_Supplier_btnSave")]
			[HttpPost]
			public object Gy_Supplier_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_Supplier_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 HCurID = item["HCurID"].ToString();//币别ID
			string HSSID = item["HSSID"].ToString();//结算方式ID
			string HAreaID = item["HAreaID"].ToString();//地区ID
			string HName = item["供应商名称"].ToString();
			string HNumber = item["供应商代码"].ToString();
			string HRemark = item["备注"].ToString();
			string HHelpCode = item["助记码"].ToString();
			string HShortNumber;
			HShortNumber = DBUtility.ClsPub.GetShortNumber(HNumber);//短代码
			if (HShortNumber.Trim() == "")
			{
			objJsonResult.code = CodeConstant.FAIL;
			objJsonResult.count = CountConstant.FAIL;
			objJsonResult.Message = "保存失败！短代码为空！";
			objJsonResult.data = 1;
			return objJsonResult;
			}
			int HLevel = DBUtility.ClsPub.GetLevel(HNumber); //等级
			int HParentID = 0;//父id
			int HStopflag = 0;//禁用标记
			string HShortName = item["简称"].ToString();
			string HAddress = item["地址"].ToString();
			string HLinkMan = "";//联系人
			string HLinkPhone = "";//联系电话
			string HMobilePhone = "";//手机
			string HFax = "";//传真
			string HPostalCode = item["邮编"].ToString();
			string HEmail = item["电子邮箱"].ToString();
			string HBank = item["银行"].ToString();
			string HBankAccount = item["银行账户"].ToString();
			string HTaxNum = item["税务登记号"].ToString();
			string HCountry = item["国家"].ToString();
			string HCorMan = item["法人代表"].ToString();
			string HEnglishName = item["英文名称"].ToString();
			string HEnglishAddress = item["英文地址"].ToString();
			int HTaxRate = 13;//增值税率
			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_Supplier where HNumber='" + HNumber + "'", "Gy_Supplier");

			if (ds.Tables[0].Rows.Count == 0)
			{
			oCN.RunProc("Insert Into Gy_Supplier " +
			"(HNumber,HName,HHelpCode,HShortNumber,HParentID" +
			",HLevel,HEndFlag,HStopflag,HRemark" +
			",HShortName,HAddress,HLinkMan,HLinkPhone,HMobilePhone" +
			",HFax,HPostalCode,HEmail,HBank,HBankAccount" +
			",HTaxNum,HTaxRate,HCountry,HCorMan,HEnglishName" +
			",HEnglishAddress,HCurID,HSSID,HAreaID,HMaterClass,HUSEORGID,HCREATEORGID,HPSStyleID,HSupTypeID) " +
			" values('" + HNumber + "','" + HName + "','" + HHelpCode + "','" + HShortNumber + "','" + HParentID + "'" +
			"," + HLevel + ",1,'" + HStopflag + "','" + HRemark + "'" +
			",'" + HShortName + "','" + HAddress + "','" + HLinkMan + "','" + HLinkPhone + "','" + HMobilePhone + "'" +
			",'" + HFax + "','" + HPostalCode + "','" + HEmail + "','" + HBank + "','" + HBankAccount + "'" +
			",'" + HTaxNum + "','" + HTaxRate + "','" + HCountry + "','" + HCorMan + "','" + HEnglishName + "'" +
			",'" + HEnglishAddress + "'," + HCurID + "," + HSSID + "," + HAreaID + " ,'" + 0 + " ','" + HOrgID + "' ,'" + HOrgID + "' ,'" + 0 + "' ,'" + 0 +
			"') ");
			}
			else
			{
			//主表
			oCN.RunProc("Update Gy_Supplier set " +
			"HName='" + HName + "'" +
			",HShortNumber='" + HShortNumber + "'" +
			",HParentID=" + HParentID.ToString() +
			",HLevel=" + HLevel.ToString() +
			",HStopflag ='" + HStopflag + "'" +
			",HRemark= '" + HRemark + "'" +
			",HHelpCode='" + HHelpCode + "'" +
			",HShortName='" + HShortName + "'" +
			",HAddress='" + HAddress + "'" +
			",HLinkMan='" + HLinkMan + "'" +
			",HLinkPhone='" + HLinkPhone + "'" +
			",HMobilePhone='" + HMobilePhone + "'" +
			",HFax='" + HFax + "'" +
			",HPostalCode='" + HPostalCode + "'" +
			",HEmail='" + HEmail + "'" +
			",HBank='" + HBank + "'" +
			",HBankAccount='" + HBankAccount + "'" +
			",HTaxNum='" + HTaxNum + "'" +
			",HTaxRate=" + HTaxRate.ToString() +
			",HCountry='" + HCountry + "'" +
			",HCorMan='" + HCorMan + "'" +
			",HEnglishName='" + HEnglishName + "'" +
			",HEnglishAddress='" + HEnglishAddress + "'" +
			",HCurID=" + HCurID.ToString() +
			",HSSID=" + HSSID.ToString() +
			",HAreaID=" + HAreaID.ToString() + "Where HNumber='" + HNumber + "'", ref DBUtility.ClsPub.sExeReturnInfo);
			}

			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

			}
			}