ZQHApi.git - Gitblit

			@@ -1,10 +1,12 @@
			using Dapper;
			using Newtonsoft.Json.Linq;
			using System;
			using System.Collections.Generic;
			using System.Data;
			using System.Data.SqlClient;
			using System.Linq;
			using System.Web;
			using VueWebApi.Models;
			using VueWebApi.Tools;

			namespace VueWebApi.DLL.DAL
			@@ -34,7 +36,7 @@
			//查询未关闭且未全部到货的采购订单
			sql = @"select distinct A.ordercode from T_PurchaseOrder A
			inner join T_PurchaseOrder_sub B on A.ordercode=B.purchaseorder_code
			where B.countarrivalquantity<quantity" + search;
			where B.countarrivalquantity<quantity and A.status<>'CLOSED'" + search;
			dynamicParams.Add("@purchordercode", purchordercode);
			var data = DapperHelper.selectdata(sql, dynamicParams);
			if (data.Rows.Count > 0)
			@@ -69,15 +71,16 @@
			try
			{
			//根据采购订单号查询物料信息，供应商信息
			sql = @"select B.materiel_code as partcode,M.partname,M.partspec,M.is_batchno,
			B.quantity,B.customercode,C.name as customername,U.code as uomcode,U.name as uomname,
			A.ordercode,B.countarrivalquantity
			sql = @"select B.rownumber, B.materiel_code as partcode,M.partname,M.partspec,M.is_batchno,M.is_incheck,
			B.quantity,B.customercode,C.name as customername,A.departmentcode,T.org_name as departmentname,U.code as uomcode,U.name as uomname,
			A.ordercode,B.countarrivalquantity,A.acceptdate
			from T_PurchaseOrder A
			inner join T_PurchaseOrder_sub B on A.ordercode=B.purchaseorder_code
			left join TMateriel_Info M on B.materiel_code=M.partcode
			left join TUom U on M.uom_code=U.code
			left join TCustomer C on B.customercode=C.code
			where A.ordercode=@purchordercode";
			left join TOrganization T on A.departmentcode=T.org_code
			where B.quantity>B.countarrivalquantity and A.ordercode=@purchordercode";
			dynamicParams.Add("@purchordercode", purchordercode);
			var data = DapperHelper.selectdata(sql, dynamicParams);
			if (data.Rows.Count > 0)
			@@ -107,9 +110,595 @@
			#region[采购到货：生成系统条码]
			public static ToMessage MesAppPurchOrderLabelBarCode(string partcode, string arrivalqty, string onelableqty)
			{
			mes = AppLableBarCode.EncodingSeach("2060",partcode, arrivalqty, onelableqty);
			mes = AppLableBarCode.EncodingSeach("2018", partcode, arrivalqty, onelableqty);
			return mes;
			}
			#endregion

			#region[采购到货：提交]
			public static ToMessage MesAppPurchOrderSave(string purordercode, string orderstaus, string username, List<AppPurchOrderSave> json)
			{
			var sql = "";
			string djwo = "", numvalue = "";
			List<object> list = new List<object>();
			var dynamicParams = new DynamicParameters();
			try
			{
			list.Clear();
			mes = SeachEncode.EncodingSeach("2060"); //APP端采购到货生成单号
			if (mes.code == "300")
			{
			return mes;
			}
			else
			{
			List<string> wo = (List<string>)mes.data;
			djwo = wo[0].ToString(); //获取单号
			numvalue = wo[1].ToString(); //获取流水号
			}
			if (json == null \|\| json.Count <= 0)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = "物料信息不能为空!";
			mes.data = null;
			return mes;
			}
			//Lambda表达式过滤获取预计到货日期、部门、供应商取第一个
			string hacceptdate = json.Select(p => p.hacceptdate).Distinct().ToList().First().ToString(); //预计到货日期
			string dpartment = json.Select(p => p.hdepartmentcode).Distinct().ToList().First().ToString(); //部门
			string customercode = json.Select(p => p.hcustomercode).Distinct().ToList().First().ToString(); //供应商
			//写入采购到货单主表
			sql = @"insert into T_PurchaseArrivalOrder(hbillno,hdate,hacceptdate,hbillstaus,hdepartmentcode,hcustomercode,hsourcebillno,lm_user,lm_date)
			values(@hbillno,@hdate,@hacceptdate,@hbillstaus,@hdepartmentcode,@hcustomercode,@hsourcebillno,@lm_user,@lm_date)";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			hbillno = djwo,
			hdate = DateTime.Now.ToString(),
			hacceptdate = hacceptdate == "" ? null : hacceptdate,
			hbillstaus = orderstaus,
			hdepartmentcode = dpartment,
			hcustomercode = customercode,
			hsourcebillno = purordercode,
			lm_user = username,
			lm_date = DateTime.Now.ToString()
			}
			});
			//循环获取物信息
			for (int i = 0; i < json.Count; i++)
			{
			//写入采购到货单子表
			sql = @"insert into T_PurchaseArrivalOrder_sub(hbillno,rownumber,hpartcode,hplanqty,hqty,hacceptdate,hcheckstaus,hbatchno)
			values(@hbillno,@rownumber,@hpartcode,@hplanqty,@hqty,@hacceptdate,@hcheckstaus,@hbatchno)";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			hbillno = djwo,
			rownumber = json[i].rownumber,
			hpartcode = json[i].hpartcode,
			hplanqty = decimal.Parse(json[i].hplanqty),
			hqty = decimal.Parse(json[i].hqty),
			hacceptdate = hacceptdate == "" ? null : hacceptdate,
			hcheckstaus = json[i].hischeck,
			hbatchno = json[i].hbatchno
			}
			});

			//循环获取物料生成标签信息
			for (int j = 0; j < json[i].children.Count; j++)
			{
			//写入条码档案表
			sql = @"insert into T_BarCodeBill(rightcode,hbillno,hbilltype,hbarcode,qualitystatus,hbarcodestatus,hbarcodetype,hmaterialcode,hqty,hbatchno,hcustomercode,hdeptcode,hsourcebillno,hsourcebilltype,barcodeposition,lm_user,lm_date)
			values(@rightcode,@hbillno,@hbilltype,@hbarcode,@qualitystatus,@hbarcodestatus,@hbarcodetype,@hmaterialcode,@hqty,@hbatchno,@hcustomercode,@hdeptcode,@hsourcebillno,@hsourcebilltype,@barcodeposition,@lm_user,@lm_date)";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			rightcode = "2060",
			hbillno = djwo,
			hbilltype = "1103", //采购到货单类型
			hbarcode = json[i].children[j].labcode,
			qualitystatus= json[i].hischeck=="Y"? "TS" : "CS",
			hbarcodestatus = "Y",
			hbarcodetype = "P",
			hmaterialcode = json[i].children[j].partnumber,
			hqty = decimal.Parse(json[i].children[j].labqty),
			hbatchno = json[i].hbatchno,
			hcustomercode = customercode,
			hdeptcode = dpartment,
			hsourcebillno = purordercode,
			hsourcebilltype = "1102",
			barcodeposition = "2060;采购到货",
			lm_user = username,
			lm_date = DateTime.Now.ToString()
			}
			});
			}
			//更新采购订单子表：累计到货数量
			sql = @"update T_PurchaseOrder_sub set countarrivalquantity=countarrivalquantity+@hqty where purchaseorder_code=@purordercode and materiel_code=@materiel_code and rownumber=@rownumber";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			purordercode = purordercode,
			materiel_code = json[i].hpartcode,
			rownumber = json[i].rownumber,
			hqty = decimal.Parse(json[i].hqty)
			}
			});
			}
			//更新采购订单主表：订单状态
			sql = @"update T_PurchaseOrder set status=@status where ordercode=@ordercode";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			status = orderstaus,
			ordercode = purordercode
			}
			});
			bool aa = DapperHelper.DoTransaction(list);
			if (aa)
			{
			mes.code = "200";
			mes.count = 0;
			mes.Message = "采购到货成功!";
			mes.data = djwo;
			}
			else
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = "采购到货失败!";
			mes.data = null;
			}
			}
			catch (Exception e)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion


			#region[采购入库选择有到货采购订单]
			public static ToMessage MesAppPurchInStorSelectCGOrder()
			{
			string sql = "";
			var dynamicParams = new DynamicParameters();
			try
			{
			//有到货或者部分入库的采购订单
			sql = @"select distinct A.ordercode from T_PurchaseOrder A
			inner join T_PurchaseArrivalOrder B on A.ordercode=B.hsourcebillno
			where B.hbillstaus in('BINARIVE','INARIVE','BINSTOG')";
			var data = DapperHelper.selecttable(sql);
			if (data.Rows.Count > 0)
			{
			mes.code = "200";
			mes.Message = "success!";
			mes.data = data;
			}
			else
			{
			mes.code = "300";
			mes.Message = "无可执行订单!";
			mes.data = null;
			}
			}
			catch (Exception e)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion

			#region[选择采购订单带出订单下有到货的物料信息]
			public static ToMessage MesAppPurchInStorSelectCGOrderPart(string ordercode)
			{
			string sql = "";
			var dynamicParams = new DynamicParameters();
			try
			{
			//选择采购订单带出订单下有到货的物料信息
			sql = @"select A.ordercode,M.partcode,M.partname,M.partspec,A.customercode,A.departmentcode,avg(S.hplanqty) as hplanqty,sum(S.hqty) as hqty
			from T_PurchaseOrder A
			inner join T_PurchaseArrivalOrder B on A.ordercode=B.hsourcebillno
			inner join T_PurchaseArrivalOrder_sub S on B.hbillno=S.hbillno
			left join TMateriel_Info M on S.hpartcode=M.partcode
			where B.hbillstaus in('BINARIVE','INARIVE','BINSTOG') and A.ordercode=@ordercode
			group by A.ordercode,M.partcode,M.partname,M.partspec,A.customercode,A.departmentcode";
			dynamicParams.Add("@ordercode", ordercode);
			var data = DapperHelper.selectdata(sql, dynamicParams);
			if (data.Rows.Count > 0)
			{
			mes.code = "200";
			mes.Message = "success!";
			mes.data = data;
			}
			else
			{
			mes.code = "300";
			mes.Message = "当前订单无可执行物料!";
			mes.data = null;
			}
			}
			catch (Exception e)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion

			#region[采购入库扫描标签条码带出到货单信息]
			public static ToMessage MesAppPurchInStorScanLabCode(string labcode, string ordercode)
			{
			string sql = "";
			var dynamicParams = new DynamicParameters();
			try
			{
			//判断条码状态
			mes = LabCodeStatusMessage.LabCodeStatus("2060", "1103", labcode);//功能编号、单据类型编码、标签编码
			if (mes.code == "300")
			{
			return mes;
			}
			//判断扫描标签是否为当前选择采购订单下的到货单所生成标签
			sql = @"select * from T_BarCodeBill where hsourcebillno=@ordercode";
			dynamicParams.Add("@ordercode", ordercode);
			var data0 = DapperHelper.selectdata(sql, dynamicParams);
			if (data0.Rows.Count < 0)
			{
			mes.code = "300";
			mes.Message = "当前条码不是采购订单【" + ordercode + "】生成条码!";
			mes.data = null;
			return mes;
			}
			//根据标签条码查询到货生成的标签信息
			sql = @"select A.hbarcode,A.hbillno,A.hqty,A.hcustomercode,C.name as hcustomername,A.hbatchno,A.qualitystatus
			from T_BarCodeBill A
			left join TCustomer C on A.hcustomercode=C.code
			where A.hbarcode=@labcode";
			dynamicParams.Add("@labcode", labcode);
			var data = DapperHelper.selectdata(sql, dynamicParams);
			if (data.Rows.Count > 0)
			{
			mes.code = "200";
			mes.Message = "success!";
			mes.data = data;
			}
			else
			{
			mes.code = "300";
			mes.Message = "标签信息不存在!";
			mes.data = null;
			}
			}
			catch (Exception e)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion

			#region[采购入库提交]
			public static ToMessage MesAppPurchInStorSave(string ordercode, string username, string incontent)
			{
			var sql = "";
			string djwo = "", numvalue = "";
			List<object> list = new List<object>();
			List<ObjectData> obj = new List<ObjectData>();
			var dynamicParams = new DynamicParameters();
			try
			{
			string datetime = DateTime.Now.ToString(); //获取系统时间
			mes = SeachEncode.EncodingSeach("2061"); //APP端采购入库生成单号
			if (mes.code == "300")
			{
			return mes;
			}
			else
			{
			List<string> wo = (List<string>)mes.data;
			djwo = wo[0].ToString(); //获取单号
			numvalue = wo[1].ToString(); //获取流水号
			}
			//获取提交信息
			JArray arra = (JArray)Newtonsoft.Json.JsonConvert.DeserializeObject(incontent);
			list.Clear();
			//写入入库记录主表
			sql = @"insert into TK_WMS_Inwh_Main(docu_typecode,hbillno,hsoucenillno,status,hcustomer,hdempart,lm_user,lm_date)
			values(@docu_typecode,@hbillno,@hsoucenillno,@status,@hcustomer,@hdempart,@lm_user,@lm_date)";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			docu_typecode = "1201",
			hbillno = djwo,
			hsoucenillno = ordercode,
			status = "NEW",
			hcustomer = arra[0]["customercode"].ToString(),
			hdempart = arra[0]["departmentcode"].ToString(),
			lm_user = username,
			lm_date = datetime
			}
			});
			//根据源单+物料查询采购订单到货总数、入库总数
			sql = @"select sum(B.quantity) as quantity,sum(B.countarrivalquantity) as countarrivalquantity,
			sum(B.countInquantity) as countInquantity
			from T_PurchaseOrder A
			inner join T_PurchaseOrder_sub B on A.ordercode=B.purchaseorder_code
			where ordercode=@ordercode";
			dynamicParams.Add("@ordercode", ordercode);
			var data0 = DapperHelper.selectdata(sql, dynamicParams);
			decimal quantity = decimal.Parse(data0.Rows[0]["quantity"].ToString()); //采购订单累计任务数量
			decimal countarrivalquantity = decimal.Parse(data0.Rows[0]["countarrivalquantity"].ToString()); //采购订单累计到货数量
			decimal countInquantity = decimal.Parse(data0.Rows[0]["countInquantity"].ToString()); //采购订单累计入库数量

			string storg = "";//采购订单入库状态
			decimal partlabqty = 0; //当前标签累计数量

			for (int i = 1; i <= arra.Count; i++)//循环获取物料信息
			{
			string labcode = "";//标签信息
			decimal suminqty = 0; //单标签入库总数量
			string batchno = ""; //批次号

			//查询汇总标签信息
			for (int j = 0; j < arra[i]["labcont"].Count(); j++)//循环获取标签信息
			{

			//更新到货单子表入库数量
			sql = @"update T_PurchaseArrivalOrder_sub set hinqty=isnull(hinqty,0)+@inqty where hbillno=@hbillno and hpartcode=@hpartcode";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			hbillno = arra[i]["labcont"][j]["arrivelorder"].ToString(),
			hpartcode = arra[i]["partcode"].ToString(),
			hinqty = decimal.Parse(arra[i]["labcont"][j]["qty"].ToString())
			}
			});
			labcode = labcode + arra[i]["labcont"][j]["labcode"].ToString() + ",";
			suminqty = suminqty + decimal.Parse(arra[i]["labcont"][j]["qty"].ToString());
			partlabqty = partlabqty + decimal.Parse(arra[i]["labcont"][j]["qty"].ToString());
			batchno = arra[i]["labcont"][j]["batch"].ToString();
			}

			//更新采购订单子表入库数量
			sql = @"update T_PurchaseOrder_sub set countInquantity=isnull(countInquantity,0)+@countInquantity
			where purchaseorder_code=@purchaseorder_code and materiel_code=@materiel_code";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			purchaseorder_code = ordercode,
			materiel_code = arra[i]["partcode"].ToString(),
			countInquantity = partlabqty
			}
			});

			//写入采购入库单子表
			sql = @"insert into TK_WMS_Inwh_Sub(hbillno,labcode,rownumber,materiel_code,storehouse_code,location_code,qty,batchno)
			values(@hbillno,@labcode,@rownumber,@materiel_code,@storehouse_code,@location_code,@qty,@batchno)";
			labcode = string.Format("'{0}'", labcode.Substring(0, labcode.Length - 1).Replace(",", "','"));
			list.Add(new
			{
			str = sql,
			parm = new
			{
			hbillno = djwo,
			labcode = labcode,
			rownumber = i,
			materiel_code = arra[i]["partcode"].ToString(),
			storehouse_code = arra[i]["storehousecode"].ToString(),
			location_code = arra[i]["warehousecode"].ToString(),
			qty = suminqty,
			batchno = batchno
			}
			});
			//写入库存表前查询当前物料在库存表中是否存在,写入或更新库存表
			sql = @"select * from TK_WMS_Stock where materiel_code=@materiel_code";
			dynamicParams.Add("@materiel_code", arra[i]["partcode"].ToString());
			var data1 = DapperHelper.selectdata(sql, dynamicParams);
			if (data1.Rows.Count > 0)
			{
			sql = @"update TK_WMS_Stock set qty=qty+@suminqty
			where materiel_code=@materiel_code";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			materiel_code = arra[i]["partcode"].ToString(),
			suminqty = suminqty
			}
			});
			}
			else
			{
			sql = @"insert into TK_WMS_Stock(materiel_code,batchno,storehouse_code,location_code,customercode,qty,lm_user,lm_date)
			values(materiel_code,batchno,storehouse_code,location_code,customercode,qty,lm_user,lm_date)";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			materiel_code = arra[i]["partcode"].ToString(),
			batchno = batchno,
			storehouse_code= arra[i]["storehousecode"].ToString(),
			location_code= arra[i]["warehousecode"].ToString(),
			customercode= arra[i]["customercode"].ToString(),
			qty= suminqty,
			lm_user=username,
			lm_date= datetime,
			}
			});
			}
			/写入ERP采购入库单***********
			* ***********************
			* **********************
			**********************/
			}
			//更新采购定单状态(部分入库、已入库)
			if ((countInquantity + partlabqty) < countarrivalquantity) //当前采购订单累计已入库+本次总入库<当前采购订单累计到货=部分入库
			{
			storg = "BINSTOG";
			}
			else
			{
			storg = "INSTOG";
			}
			//更新采购订单主表单据状态
			sql = @"update T_PurchaseOrder set status=@storg
			where ordercode=@ordercode";
			list.Add(new
			{
			str = sql,
			parm = new
			{
			ordercode = ordercode,
			storg = storg
			}
			});

			bool aa = DapperHelper.DoTransaction(list);
			if (aa)
			{
			mes.code = "200";
			mes.count = 0;
			mes.Message = "操作成功!";
			mes.data = null;
			}
			else
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = "操作失败!";
			mes.data = null;
			}
			}
			catch (Exception e)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion


			#region[采购退货：获取采购订单列表(已到货的采购订单)]
			public static ToMessage MesAppPurchaseReturnOrderSearch(string purchordercode)
			{
			string sql = "";
			string search = "";
			var dynamicParams = new DynamicParameters();
			try
			{
			if (purchordercode != "" && purchordercode != null) //订单号不为空
			{
			search += " and A.ordercode like '%'+@purchordercode+'%' ";
			}
			//查询未关闭且有到货的采购订单
			sql = @"select distinct A.ordercode from T_PurchaseOrder A
			inner join T_PurchaseOrder_sub B on A.ordercode=B.purchaseorder_code
			where B.countarrivalquantity<=quantity and A.status<>'CLOSED'" + search;
			dynamicParams.Add("@purchordercode", purchordercode);
			var data = DapperHelper.selectdata(sql, dynamicParams);
			if (data.Rows.Count > 0)
			{
			mes.code = "200";
			mes.Message = "查询成功!";
			mes.data = data;
			}
			else
			{
			mes.code = "300";
			mes.Message = "无订单数据!";
			mes.data = null;
			}
			}
			catch (Exception e)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion

			#region[采购退货：选择采购订单获取物料信息]
			public static ToMessage MesAppPurchReturnOrderPartSearch(string purchordercode)
			{
			string sql = "";
			var dynamicParams = new DynamicParameters();
			try
			{
			//根据采购订单号查询非关闭状态下有到货且到货数量大于0的物料
			sql = @"select B.rownumber, B.materiel_code as partcode,M.partname,M.partspec,
			B.quantity,B.countarrivalquantity,U.code as uomcode,U.name as uomname
			from T_PurchaseOrder A
			inner join T_PurchaseOrder_sub B on A.ordercode=B.purchaseorder_code
			left join TMateriel_Info M on B.materiel_code=M.partcode
			left join TUom U on M.uom_code=U.code
			where B.countarrivalquantity>0 and A.status<>'CLOSED' and A.ordercode=@purchordercode";
			dynamicParams.Add("@purchordercode", purchordercode);
			var data = DapperHelper.selectdata(sql, dynamicParams);
			if (data.Rows.Count > 0)
			{
			mes.code = "200";
			mes.Message = "查询成功!";
			mes.data = data;
			}
			else
			{
			mes.code = "300";
			mes.Message = "暂无物料数据!";
			mes.data = null;
			}
			}
			catch (Exception e)
			{
			mes.code = "300";
			mes.count = 0;
			mes.Message = e.Message;
			mes.data = null;
			}
			return mes;
			}
			#endregion

			}
			}