public ActionResult PurchaseList(ParaPrPurchaseFilter para)
{
if (Request.IsAjaxRequest())
{
para.Id = para.Id > 0 ? para.Id : 1;
int purchaseSysno = 0;
int.TryParse(para.PurchaseCode, out purchaseSysno);
para.SysNo = purchaseSysno;
var pageList = BLL.Purchase.PrPurchaseBo.Instance.QueryPrPurchase(para);
return(PartialView("_AjaxPagerPurchaseList", pageList));
}
else
{
//采购单状态下拉绑定数据
var statustList = new List <SelectListItem>()
{
new SelectListItem()
{
Text = @"全部", Value = "0", Selected = true
}
};
EnumUtil.ToListItem <PurchaseStatus.采购单状态>(ref statustList);
ViewData["Status"] = new SelectList(statustList, "Value", "Text");
}
return(View());
}
/// <summary>
/// 查询采购单
/// </summary>
/// <param name="para">查询参数</param>
/// <returns></returns>
/// <remarks>2016-6-21 杨浩 增加查询条件</remarks>
public override Pager <CBPrPurchase> QueryPrPurchase(ParaPrPurchaseFilter para)
{
var paras = new List <object>();
string whereStr = " where 1=1 ";
if (para.WarehouseSysNo > 0)
{
whereStr += " and ph.WarehouseSysNo=@" + paras.Count;
paras.Add(para.WarehouseSysNo);
}
if (!string.IsNullOrEmpty(para.PurchaseCode) && para.PurchaseCode != "")
{
whereStr += " and (ph.PurchaseCode=@" + paras.Count + " or phd.ProductName like '%" + para.PurchaseCode + "%' )";
paras.Add(para.PurchaseCode);
}
if (para.Status != 0)
{
whereStr += " and ph.Status=@" + paras.Count;
paras.Add(para.Status);
}
if (para.CreatedDate.HasValue)
{
whereStr += " and ph.CreatedDate=@" + paras.Count;
paras.Add(para.CreatedDate);
}
string sql = @"
(
select distinct ph.*,wh.BackWarehouseName,wh.WarehouseName,pmf.FName from PrPurchase as ph
inner join PrPurchaseDetails as phd on ph.SysNo=phd.PurchaseSysNo
left join WhWarehouse as wh on wh.SysNo=ph.WarehouseSysNo
left join PmManufacturer as pmf on pmf.SysNo=ph.ManufacturerSysNo " + whereStr + ") tb";
var dataList = Context.Select <CBPrPurchase>("tb.*").From(sql);
var dataCount = Context.Select <int>("count(0)").From(sql);
dataList.Parameters(paras.ToArray());
dataCount.Parameters(paras.ToArray());
var pager = new Pager <CBPrPurchase>
{
PageSize = para.PageSize,
CurrentPage = para.Id,
TotalRows = dataCount.QuerySingle(),
Rows = dataList.OrderBy("tb.sysNo desc").Paging(para.Id, para.PageSize).QueryMany()
};
return(pager);
}
public override List <CBPrPurchaseDetails> QueryPrPurchaseByOrderDetail(ParaPrPurchaseFilter para)
{
string whereStr = " where 1=1 ";
if (para.WarehouseSysNo > 0)
{
whereStr += " and ph.WarehouseSysNo=" + para.WarehouseSysNo;
}
if (!string.IsNullOrEmpty(para.PurchaseCode) && para.PurchaseCode != "")
{
whereStr += " and (ph.PurchaseCode='" + para.PurchaseCode + "' or phd.ProductName like '%" + para.PurchaseCode + "%' )";
}
if (para.Status != 0)
{
whereStr += " and ph.Status=" + para.Status;
}
if (para.CreatedDate.HasValue)
{
whereStr += " and ph.CreatedDate='" + para.CreatedDate + "'";
}
string sql = @"
(
select ph.*,wh.BackWarehouseName,wh.WarehouseName,pmf.FName ,
phd.ProductName as ProductName, phd.ErpCode as ErpCode,
phd.Quantity as ProQuantity,phd.EnterQuantity as ProEnterQuantity,
phd.Money as ProMoney , phd.TotalMoney as ProTotalMoney,
phd.Remarks as ProductRemarks , a.CategoryName as OneCategory ,b.CategoryName as SecondCategory,c.CategoryName as thridCategory,PdPrice.Price
from PrPurchase as ph
inner join PrPurchaseDetails as phd on ph.SysNo=phd.PurchaseSysNo
left join WhWarehouse as wh on wh.SysNo=ph.WarehouseSysNo
left join PmManufacturer as pmf on pmf.SysNo=ph.ManufacturerSysNo
inner join PdProduct on PdProduct.SysNo=phd.ProductSysNo
left join PdCategoryAssociation on PdCategoryAssociation.ProductSysNo=PdProduct.SysNo and
PdCategoryAssociation.IsMaster=1
left join PdCategory a on a.SysNo=PdCategoryAssociation.CategorySysNo
left join PdCategory b on b.SysNo=a.ParentSysNo
left join PdCategory c on c.SysNo=b.ParentSysNo
left join PdPrice on PdPrice.ProductSysNo=PdProduct.SysNo and PdPrice.PriceSource=10 and PdPrice.SourceSysNo=1
" + whereStr + ") tb";
sql = "select tb.* from " + sql + " order by tb.SysNo desc ";
return(Context.Sql(sql).QueryMany <CBPrPurchaseDetails>());
}
/// <summary>
/// 查询采购单
/// </summary>
/// <param name="para">查询参数</param>
/// <returns></returns>
public PagedList <CBPrPurchase> QueryPrPurchase(ParaPrPurchaseFilter para, int pageSize = 10)
{
PagedList <CBPrPurchase> model = null;
if (para != null)
{
model = new PagedList <CBPrPurchase>();
model.PageSize = pageSize;
var pager = IPrPurchaseDao.Instance.QueryPrPurchase(para);
if (null != pager)
{
model.TData = pager.Rows;
model.TotalItemCount = pager.TotalRows;
model.CurrentPageIndex = para.Id;
}
}
return(model);
}
/// <summary>
/// 分页
/// </summary>
/// <param name="para">查询参数</param>
/// <returns></returns>
/// <remarks> 2018-1-4 吴琨 创建</remarks>
public override Pager <SyKingdeeUser> GetPages(ParaPrPurchaseFilter para)
{
var paras = new List <object>();
//paras.Add(para.WarehouseSysNo);
string whereStr = " where 1=1 ";
if (!string.IsNullOrEmpty(para.KeyWord))
{
whereStr += @" and (SyUser.Account like @" + paras.Count + @"
or SyUser.UserName like @" + paras.Count + @"
or SyUser.MobilePhoneNumber like @" + paras.Count + @"
or SyKingdeeUser.KingdeeUserCode like @" + paras.Count + @"
or SyUser.SysNo like @" + paras.Count + @"
or SyUser.Account like @" + paras.Count + @"
)";
paras.Add("%" + para.KeyWord + "%");
}
string sql = @"
(
select SyKingdeeUser.*,SyUser.Account,SyUser.UserName as SyUserName from SyKingdeeUser
inner join SyUser on SyUser.SysNo=SyKingdeeUser.SyUserSysNo " + whereStr + @"
) tb";
var dataList = Context.Select <SyKingdeeUser>("tb.*").From(sql);
var dataCount = Context.Select <int>("count(0)").From(sql);
dataList.Parameters(paras.ToArray());
dataCount.Parameters(paras.ToArray());
var pager = new Pager <SyKingdeeUser>
{
PageSize = para.PageSize,
CurrentPage = para.Id,
TotalRows = dataCount.QuerySingle(),
Rows = dataList.OrderBy("tb.SysNo desc").Paging(para.Id, para.PageSize).QueryMany()
};
return(pager);
}
public void ExportPurchaseData(ParaPrPurchaseFilter para)
{
List <CBPrPurchaseDetails> TData = BLL.Purchase.PrPurchaseBo.Instance.QueryPrPurchaseByOrderDetail(para);
Hyt.BLL.Purchase.PrPurchaseBo.Instance.ExportPurchaseData(TData, "", 0);
}
/// <summary> /// 分页查询 /// </summary> /// <param name="para">查询参数</param> /// <returns></returns> /// <remarks>2016-6-21 杨浩 增加查询条件</remarks> public abstract Pager <SyKingdeeUser> GetPages(ParaPrPurchaseFilter para);
public List <CBPrPurchaseDetails> QueryPrPurchaseByOrderDetail(ParaPrPurchaseFilter para)
{
return(IPrPurchaseDao.Instance.QueryPrPurchaseByOrderDetail(para));
}
/// <summary> /// 获取采购单导出明细 /// </summary> /// <param name="para"></param> /// <returns></returns> public abstract List <CBPrPurchaseDetails> QueryPrPurchaseByOrderDetail(ParaPrPurchaseFilter para);
/// <summary> /// 查询采购单 /// </summary> /// <param name="para">查询参数</param> /// <returns></returns> public abstract Pager <CBPrPurchase> QueryPrPurchase(ParaPrPurchaseFilter para);
