using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Web;
|
using VueWebApi.Tools;
|
|
namespace VueWebApi.DLL.DAL
|
{
|
public class PurchaseOrderDAL
|
{
|
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 PurchaseOrderSearch(string mesorderstus, string mesordercode, string customercode, string orderdateopendate, string orderdateclosedate, string sourceorder, string deptcode, string creatuser, string createopendate, string createclosedate, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (mesorderstus != "" && mesorderstus != null)
|
{
|
search += "and A.status=@mesorderstus ";
|
dynamicParams.Add("@mesorderstus", mesorderstus);
|
}
|
if (mesordercode != "" && mesordercode != null)
|
{
|
search += "and A.ordercode like '%'+@mesordercode+'%' ";
|
dynamicParams.Add("@mesordercode", mesordercode);
|
}
|
if (customercode != "" && customercode != null)
|
{
|
search += "and A.customercode=@customercode ";
|
dynamicParams.Add("@customercode", customercode);
|
}
|
if (deptcode != "" && deptcode != null)
|
{
|
search += "and A.departmentcode=@deptcode ";
|
dynamicParams.Add("@deptcode", deptcode);
|
}
|
if (sourceorder != "" && sourceorder != null)
|
{
|
search += "and A.saleorderCode like '%'+@sourceorder+'%' ";
|
dynamicParams.Add("@sourceorder", sourceorder);
|
}
|
if (orderdateopendate != "" && orderdateopendate != null)
|
{
|
search += "and A.orderdate between @orderdateopendate and @orderdateclosedate ";
|
dynamicParams.Add("@orderdateopendate", orderdateopendate + " 00:00:00");
|
dynamicParams.Add("@orderdateclosedate", orderdateclosedate + " 23:59:59");
|
}
|
if (creatuser != "" && creatuser != null)
|
{
|
search += "and A.lm_user like '%'+@creatuser+'%' ";
|
dynamicParams.Add("@creatuser", creatuser);
|
}
|
if (createopendate != "" && createopendate != null)
|
{
|
search += "and A.lm_date between @createopendate and @createclosedate ";
|
dynamicParams.Add("@createopendate", createopendate + " 00:00:00");
|
dynamicParams.Add("@createclosedate", createclosedate + " 23:59:59");
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.status,A.orderdate,A.ordercode,T.org_name as deptname,C.name as customername,A.saleorderCode,A.acceptdate,A.lm_user,A.lm_date,U.username as auditoruser,A.auditordate
|
from T_PurchaseOrder A
|
left join TOrganization T on A.departmentcode=T.org_code
|
left join TCustomer C on A.customercode=C.code
|
left join TUser U on A.auditorusercode=U.usercode
|
where 1=1 " + search;
|
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.count = total;
|
mes.data = data.ToList();
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[采购订单明细]
|
public static ToMessage PurchaseOrderSubSearch(string purodercode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//获取采购订单物料信息
|
sql = @"select M.partcode,M.partname,M.partspec,T.name as uomname,A.quantity,A.countarrivalquantity,A.countInquantity,A.countreturnquantity,
|
A.acceptdate,S.name as housename,M.is_incheck
|
from T_PurchaseOrder_sub A
|
left join TMateriel_Info M on A.materiel_code=M.partcode
|
left join TUom T on M.uom_code=T.code
|
left join T_Sec_Stck S on A.warehousecode=S.code
|
where A.purchaseorder_code=@purodercode";
|
dynamicParams.Add("@purodercode", purodercode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = data;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[采购订单删除]
|
public static ToMessage DeletePurchaseOrder(string po)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断是否生成了采购到货单
|
sql = @"select * from T_PurchaseArrivalOrder where hsourcebillno=@po";
|
dynamicParams.Add("@po", po);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前采购订单已生成到货单!";
|
mes.data = null;
|
return mes;
|
}
|
//删除采购订单主表
|
sql = @"delete T_PurchaseOrder where ordercode=@po";
|
list.Add(new { str = sql, parm = new { po = po } });
|
|
//删除采购订单子表
|
sql = @"delete T_PurchaseOrder_sub where purchaseorder_code=@po";
|
list.Add(new { str = sql, parm = new { po = po } });
|
|
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 VerifyPurchaseOrder(string po,string username)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//更新采购订单主表审核人、审核时间
|
sql = @"update T_PurchaseOrder set auditorusercode=@username,auditordate=@lm_date where ordercode=@po";
|
list.Add(new { str = sql, parm = new { username= username,po = po, lm_date = DateTime.Now.ToString() } });
|
|
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 ReversVerifyPurchaseOrder(string po, string username)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断是否生成了采购到货单
|
sql = @"select * from T_PurchaseArrivalOrder where hsourcebillno=@po";
|
dynamicParams.Add("@po", po);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前采购订单已生成到货单!";
|
mes.data = null;
|
return mes;
|
}
|
//更新采购订单主表审核人、审核时间
|
sql = @"update T_PurchaseOrder set auditorusercode=@username,auditordate=@lm_date where ordercode=@po";
|
list.Add(new { str = sql, parm = new { username="",po = po, lm_date ="" } });
|
|
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 ClosedPurchaseOrder(string po, string username)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//更新采购订单状态
|
sql = @"update T_PurchaseOrder set status='CLOSED' where ordercode=@po";
|
list.Add(new { str = sql, parm = new { po = po} });
|
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 PurchaseArrivalOrderSearch(string orderstatus, string mesordercode, string departmentcode, string customercode, string orderdateopendate, string orderdateclosedate, string sourceorder, string creatuser, string arrivalopendate, string arrivalclosedate, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (orderstatus != "" && orderstatus != null)
|
{
|
search += "and A.hbillstaus=@orderstatus ";
|
dynamicParams.Add("@orderstatus", orderstatus);
|
}
|
if (mesordercode != "" && mesordercode != null)
|
{
|
search += "and A.hbillno like '%'+@mesordercode+'%' ";
|
dynamicParams.Add("@mesordercode", mesordercode);
|
}
|
if (departmentcode != "" && departmentcode != null)
|
{
|
search += "and A.hdepartmentcode=@departmentcode ";
|
dynamicParams.Add("@departmentcode", departmentcode);
|
}
|
if (customercode != "" && customercode != null)
|
{
|
search += "and A.hcustomercode=@customercode ";
|
dynamicParams.Add("@customercode", customercode);
|
}
|
if (sourceorder != "" && sourceorder != null)
|
{
|
search += "and A.hsourcebillno like '%'+@sourceorder+'%' ";
|
dynamicParams.Add("@sourceorder", sourceorder);
|
}
|
if (orderdateopendate != "" && orderdateopendate != null)
|
{
|
search += "and A.hdate between @orderdateopendate and @orderdateclosedate ";
|
dynamicParams.Add("@orderdateopendate", orderdateopendate + " 00:00:00");
|
dynamicParams.Add("@orderdateclosedate", orderdateclosedate + " 23:59:59");
|
}
|
if (creatuser != "" && creatuser != null)
|
{
|
search += "and A.lm_user like '%'+@creatuser+'%' ";
|
dynamicParams.Add("@creatuser", creatuser);
|
}
|
if (arrivalopendate != "" && arrivalopendate != null)
|
{
|
search += "and A.hacceptdate between @arrivalopendate and @arrivalclosedate ";
|
dynamicParams.Add("@arrivalopendate", arrivalopendate + " 00:00:00");
|
dynamicParams.Add("@arrivalclosedate", arrivalclosedate + " 23:59:59");
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.hbillstaus,A.hdate,A.hbillno,A.hdepartmentcode,T.org_name as hdepartmentname,A.hcustomercode,C.name as hcustomername,
|
A.hsourcebillno,A.hacceptdate,A.lm_user as usercode,U.username
|
from T_PurchaseArrivalOrder A
|
left join TCustomer C on A.hcustomercode=C.code
|
left join TOrganization T on A.hdepartmentcode=T.org_code
|
left join TUser U on A.lm_user=U.usercode
|
where 1=1 " + search;
|
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.count = total;
|
mes.data = data.ToList();
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[采购到货单明细]
|
public static ToMessage PurchaseArrivalOrderSubSearch(string arrivelodercode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//获取采购到货单物料信息
|
sql = @"select S.hpartcode as partcode,M.partname,M.partspec,M.uom_code,T.name as uom_name,
|
S.hqty,S.hinqty,S.hreturnqty,S.hacceptdate,S.hstorehousecode,K.name as hstorehousename,S.hcheckstaus
|
from T_PurchaseArrivalOrder A
|
left join T_PurchaseArrivalOrder_sub S on A.hbillno=S.hbillno
|
left join TMateriel_Info M on S.hpartcode=M.partcode
|
left join TUom T on M.uom_code=T.code
|
left join T_Sec_Stck K on S.hstorehousecode=K.code
|
where A.hbillno=@arrivelodercode";
|
dynamicParams.Add("@arrivelodercode", arrivelodercode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = data;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[采购到货单关闭]
|
public static ToMessage ClosedPurchaseArrivalOrder(string arrivelodercode, string username)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//更新到货单状态
|
sql = @"update T_PurchaseArrivalOrder set hbillstaus='CLOSED' where hbillno=@arrivelodercode";
|
list.Add(new { str = sql, parm = new { arrivelodercode = arrivelodercode } });
|
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 PurchaseArrivalOrderSubLabDetails(string sourceorder, string arrivelodercode, string partcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//获取到货物料对应标签信息
|
sql = @"select C.hbarcode,A.hbillno,A.hsourcebillno,C.qualitystatus,C.hqty,C.hbatchno
|
from T_PurchaseArrivalOrder A
|
inner join T_PurchaseArrivalOrder_sub B on A.hbillno=B.hbillno
|
left join T_BarCodeBill C on A.hsourcebillno=C.hsourcebillno and A.hbillno=C.hbillno and B.hpartcode=C.hmaterialcode
|
where A.hsourcebillno=@hsourcebillno and A.hbillno=@hbillno and B.hpartcode=@hpartcode";
|
dynamicParams.Add("@hsourcebillno", sourceorder);
|
dynamicParams.Add("@hbillno", arrivelodercode);
|
dynamicParams.Add("@hpartcode", partcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = data;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
}
|
}
|
