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 case when A.status='NEW' then '新订单' when A.status='CHECK' then '已审核' when A.status='CLOSED' then '已关闭' end as 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.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
|
}
|
}
|
