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
|
{
|
public class AppPurchManagementDAL
|
{
|
public static DataTable dt; //定义全局变量dt
|
public static bool res; //定义全局变量dt
|
public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
|
public static string strProcName = ""; //定义全局sql变量
|
public static List<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
|
public static SqlParameter[] parameters; //定义全局SqlParameter参数数组
|
|
#region[采购到货：获取采购订单列表]
|
public static ToMessage MesAppPurchOrderSearch(string purchordercode)
|
{
|
string sql = "";
|
string search = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (purchordercode != "" && purchordercode != null) //订单号不为空
|
{
|
search += " and A.ordercode like '%'+@purchordercode+'%' ";
|
//dynamicParams.Add("@purchordercode", purchordercode);
|
}
|
//查询未关闭且未全部到货的采购订单
|
sql = @"select distinct A.orderid,A.ordercode from T_PurchaseOrder A
|
inner join T_PurchaseOrder_sub B on A.ordercode=B.purchaseorder_code
|
where (isnull(B.quantity,0)-isnull(B.countarrivalquantity,0)+isnull(B.countreturnquantity,0))>0 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 MesAppPurchOrderPartSearch(string purchordercode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//根据采购订单号查询物料信息，供应商信息
|
sql = @"select B.rownumber,B.sbid, 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
|
left join TOrganization T on A.departmentcode=T.org_code
|
where (isnull(B.quantity,0)-isnull(B.countarrivalquantity,0)+isnull(B.countreturnquantity,0))>0 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
|
|
#region[采购到货：生成系统条码]
|
public static ToMessage MesAppPurchOrderLabelBarCode(string partcode, string arrivalqty, string onelableqty)
|
{
|
mes = AppLableBarCode.EncodingSeach("2018", partcode, arrivalqty, onelableqty);
|
return mes;
|
}
|
#endregion
|
|
#region[采购到货：提交]
|
public static ToMessage MesAppPurchOrderSave(string purordercode, 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 = "NEW",
|
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)
|
}
|
});
|
}
|
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 purchordercode)
|
{
|
string sql = "";
|
string search = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (purchordercode != "" && purchordercode != null) //订单号不为空
|
{
|
search += " and AA.ordercode like '%'+@purchordercode+'%' ";
|
}
|
//有到货或者部分入库的采购订单
|
sql = @"select distinct AA.ordercode from(
|
select A.ordercode,B.hbillstaus,S.hpartcode,MAX(S.hplanqty) as hplanqty,SUM(S.hqty) as hqty,SUM(S.hinqty) as hinqty,SUM(S.hreturnqty) as hreturnqty
|
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
|
group by A.ordercode,B.hbillstaus,S.hpartcode
|
) as AA where (isnull(AA.hplanqty,0)-isnull(AA.hqty,0)+isnull(AA.hreturnqty,0))>0 and AA.hbillstaus='NEW' " + search;
|
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='NEW' 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 sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断条码状态
|
mes = LabCodeStatusMessage.LabCodeStatus("2060", "1103", labcode);//功能编号(采购到货)、单据类型编码(采购到货单)、标签编码
|
if (mes.code == "300")
|
{
|
return mes;
|
}
|
//判断标签状态(是否已入库(不能再入))
|
sql = @"select * from TK_WMS_Inwh_Sub where labcode like '%'+@labcode+'%'";
|
dynamicParams.Add("@labcode", "labcode");
|
dynamicParams.Add("@labcode", labcode);
|
var dt = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前条码已入库!";
|
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,hsoucenillnoid,hsoucenillno,status,hcustomer,hdempart,lm_user,lm_date)
|
values(@docu_typecode,@hbillno,@hsoucenillnoid,@hsoucenillno,@status,@hcustomer,@hdempart,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
docu_typecode = "1201",
|
hbillno = djwo,
|
hsoucenillnoid= arra[0]["hsoucenillnoid"].ToString(),
|
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_BarCodeBill set storehouse_code=@storehouse_code,location_code=@location_code where hsourcebillno=@hsourcebillno and hbarcode=@hbarcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
storehouse_code = arra[i]["storehousecode"].ToString(),
|
location_code = arra[i]["warehousecode"].ToString(),
|
hsourcebillno = ordercode,
|
hbarcode = arra[i]["labcont"][j]["labcode"].ToString()
|
}
|
});
|
//更新到货单子表入库数量
|
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())
|
}
|
});
|
|
//写入库存表
|
sql = @"insert into TK_WMS_Stock(labcode,materiel_code,batchno,storehouse_code,location_code,customercode,qty,lm_user,lm_date)
|
values(@labcode,materiel_code,batchno,storehouse_code,location_code,customercode,qty,lm_user,lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
labcode = arra[i]["labcont"][j]["labcode"].ToString(),
|
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,
|
}
|
});
|
|
|
//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,rownumber,materiel_code,storehouse_code,location_code,qty,batchno)
|
values(@hbillno,@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
|
}
|
});
|
|
/*写入ERP采购入库单************
|
* ***********************
|
* **********************
|
**********************/
|
}
|
|
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.id,A.ordercode from T_PurchaseOrder A
|
inner join T_PurchaseOrder_sub B on A.ordercode=B.purchaseorder_code
|
where (isnull(B.countarrivalquantity,0)-isnull(B.countreturnquantity,0))>0 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
|
|
#region[采购退货单：扫描标签]
|
public static ToMessage MesAppPurchReturnInStorScanLabCode(string ordercode, string partcode, string labcode)
|
{
|
string sql = "";
|
string search = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断条码状态
|
mes = LabCodeStatusMessage.LabCodeStatus("2060", "1103", labcode);//功能编号(采购到货)、单据类型编码(采购到货单)、标签编码
|
if (mes.code == "300")
|
{
|
return mes;
|
}
|
//判断标签状态(是否已入库(不能退货))
|
sql = @"select * from TK_WMS_Inwh_Sub where labcode like '%'+@labcode+'%'";
|
dynamicParams.Add("@labcode", "labcode");
|
dynamicParams.Add("@labcode", labcode);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前条码已入库!";
|
mes.data = null;
|
return mes;
|
}
|
//根据采购订单号、物料编码、标签条码查找标签信息
|
sql = @"select A.hbarcode,A.hcustomercode,C.name as hcustomercode,
|
A.hbatchno,A.qualitystatus,A.location_code,L.name as location_name,A.hqty
|
from T_BarCodeBill A
|
left join TCustomer C on A.hcustomercode=C.code
|
left join T_Sec_Loca L on A.location_code=L.code
|
where A.rightcode=@rightcode and A.hbilltype=@hbilltype and A.hbarcodestatus='Y'
|
and A.hsourcebillno=@hsourcebillno and A.hmaterialcode=@hmaterialcode and A.hbarcode=@labcode";
|
dynamicParams.Add("@rightcode", "2060");
|
dynamicParams.Add("@hbilltype", "1103");
|
dynamicParams.Add("@hsourcebillno", ordercode);
|
dynamicParams.Add("@hmaterialcode", partcode);
|
dynamicParams.Add("@labcode", labcode);
|
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 MesAppPurchReturnInStorSave(string orderid, 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("2074"); //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 T_PurchaseReturnOrder(orderdate,ordercode,status,departmentcode,customercode,saleorderid,saleordercode,lm_user,lm_date)
|
values(@orderdate,@ordercode,@status,@departmentcode,@customercode,@saleorderid,@saleordercode,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
orderdate = datetime,
|
ordercode = djwo,
|
status = "NEW",
|
departmentcode = arra[0]["departmentcode"].ToString(),
|
customercode = arra[0]["customercode"].ToString(),
|
saleorderid = orderid,
|
saleordercode = ordercode,
|
lm_user = username,
|
lm_date = datetime
|
}
|
});
|
//根据源单+物料查询采购订单到货总数、入库总数
|
sql = @"select sum(B.quantity) as quantity,sum(B.countarrivalquantity) as countarrivalquantity,
|
sum(B.countInquantity) as countInquantity,sum(B.countreturnquantity) as countreturnquantity
|
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()); //采购订单累计入库数量
|
decimal countreturnquantity = decimal.Parse(data0.Rows[0]["countreturnquantity"].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_BarCodeBill set hbarcodestatus=@hbarcodestatus where hsourcebillno=@hsourcebillno and hbarcode=@hbarcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
hbarcodestatus = "N",
|
hsourcebillno = ordercode,
|
hbarcode = arra[i]["labcont"][j]["labcode"].ToString()
|
}
|
});
|
//更新到货单子表退货数量
|
sql = @"update T_PurchaseArrivalOrder_sub set hreturnqty=isnull(hreturnqty,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())
|
}
|
});
|
|
//更新库存表
|
sql = @"update TK_WMS_Stock set qty=qty-@inqty
|
where materiel_code=@materiel_code and labcode=@labcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
labcode = arra[i]["labcont"][j]["labcode"].ToString(),
|
materiel_code = arra[i]["partcode"].ToString(),
|
inqty = 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 countreturnquantity=isnull(countreturnquantity,0)+@countreturnquantity
|
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(),
|
countreturnquantity = partlabqty
|
}
|
});
|
|
//写入采购退货单子表
|
sql = @"insert into T_PurchaseReturnOrder_sub(purchaseorder_code,labcode,rownumber,materiel_code,customercode,quantity,batchno)
|
values(@purchaseorder_code,@labcode,@rownumber,@materiel_code,@customercode,@quantity,@batchno)";
|
labcode = string.Format("'{0}'", labcode.Substring(0, labcode.Length - 1).Replace(",", "','"));
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
purchaseorder_code = djwo,
|
labcode = labcode,
|
rownumber = i,
|
materiel_code = arra[i]["partcode"].ToString(),
|
customercode = arra[0]["customercode"].ToString(),
|
quantity = suminqty,
|
batchno = batchno
|
}
|
});
|
/*写入ERP采购退货单************
|
* ***********************
|
* **********************
|
**********************/
|
}
|
|
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
|
|
}
|
}
|
