public PageList <InvoiceModel> SearchInvoices(InvoiceSearchCondition condition)
{
var query = _shoppingContext.Invoices.AsNoTracking().AsQueryable();
if (condition.ApprovedBy > 0)
{
query = query.Where(p => p.ApprovedBy == condition.ApprovedBy);
}
if (!string.IsNullOrEmpty(condition.Code))
{
query = query.Where(p => p.Code.ToLower().Contains(condition.Code.ToLower()));
}
if (condition.DateFrom.HasValue)
{
query = query.Where(p => p.CreatedDateTime >= condition.DateFrom.Value);
}
if (condition.DateTo.HasValue)
{
var dateTo = condition.DateTo.Value.AddDays(1);
query = query.Where(p => p.CreatedDateTime < dateTo);
}
var products = query.OrderBy(o => o.CreatedDateTime).Skip(condition.PageSize * condition.PageNumber).Take(condition.PageSize).ToList();
return(new PageList <InvoiceModel>(Mapper.Map <List <InvoiceModel> >(products), query.Count()));
}
public IEnumerable <Invoice> GetInvoicesByCondition(InvoiceSearchCondition SearchCondition)
{
string sqlWhere = this.GenGetInvoicesWhere(SearchCondition);
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, sqlWhere, ParameterDirection.Input)
};
return(this.ExecuteDataTable("Proc_GetInvoicesByCondition", dbParams).ConvertToEntityCollection <Invoice>());
}
public ActionResult List(DataSourceRequest command, InvoiceSearchCondition condition)
{
condition.PageSize = command.PageSize;
condition.PageNumber = command.Page - 1;
var invoices = _invoiceService.SearchInvoices(condition);
var gridModel = new DataSourceResult()
{
Data = invoices.DataSource,
Total = invoices.TotalItems
};
return(Json(gridModel));
}
/// <summary>
/// 根据输入条件查询出库单
/// </summary>
/// <param name="condition">搜索条件</param>
/// <returns>出库单实体列表</returns>
/// <remarks>2013-06-08 周瑜 创建</remarks>
/// <remarks>2013-11-14 周唐炬 重构</remarks>
public PagedList <CBFnInvoice> Search(InvoiceSearchCondition condition)
{
PagedList <CBFnInvoice> model = null;
if (condition != null)
{
model = new PagedList <CBFnInvoice>();
var result = IInvoiceDao.Instance.Search(condition, model.PageSize);
if (result != null)
{
model.TData = result.Rows;
model.TotalItemCount = result.TotalRows;
model.CurrentPageIndex = condition.CurrentPage;
}
}
return(model);
}
/// <summary>
/// 根据输入条件查询发票列表
/// </summary>
/// <param name="condition">搜索条件</param>
/// <param name="pageSize">页大小</param>
/// <returns>发票实体列表</returns>
/// <remarks>2013-07-09 周瑜 创建</remarks>
/// <remarks>2013-11-13 周唐炬 重构</remarks>
public override Pager <SoOrder> QuickSearch(InvoiceSearchCondition condition, int pageSize)
{
//查询已审核且已付款的记录
const string sql = @"(SELECT A.SysNo
FROM SOORDER A
WHERE EXISTS (SELECT 1
FROM splitstr(@0, ',') tmp
WHERE tmp.col = A.DEFAULTWAREHOUSESYSNO)
AND A.STATUS <> @2 --不能是作废订单
AND A.PAYSTATUS = @3 --订单已支付
AND (@1 IS NULL OR A.SYSNO = @1)
)tb";
var warehouseSysNoList = string.Empty;
if (condition.WarehouseSysNoList != null && condition.WarehouseSysNoList.Any())
{
warehouseSysNoList = string.Join(",", condition.WarehouseSysNoList);
}
var paras = new object[]
{
warehouseSysNoList,
condition.OrderSysNo,
OrderStatus.销售单状态.作废.GetHashCode(),
OrderStatus.销售单支付状态.已支付.GetHashCode()
};
var dataList = Context.Select <SoOrder>("tb.*").From(sql);
var dataCount = Context.Select <int>("count(1)").From(sql);
dataList.Parameters(paras);
dataCount.Parameters(paras);
var pager = new Pager <SoOrder>
{
Rows = dataList.OrderBy(@"tb.SysNo desc").Paging(condition.CurrentPage, pageSize).QueryMany(),
TotalRows = dataCount.QuerySingle()
};
return(pager);
}
private string GenGetInvoicesWhere(InvoiceSearchCondition SearchCondition)
{
StringBuilder sb = new StringBuilder();
sb.Append(" where ProjectID=").Append(SearchCondition.ProjectID).Append(" ");
sb.Append(" and Target=").Append(SearchCondition.Target).Append(" ");
sb.Append(" and IsComplete=").Append(SearchCondition.IsComplete ? "1" : "0").Append(" ");
if (!string.IsNullOrEmpty(SearchCondition.SystemNumber))
{
IEnumerable <string> systemNumbers = Enumerable.Empty <string>();
if (SearchCondition.SystemNumber.IndexOf("\n") > 0)
{
systemNumbers = SearchCondition.SystemNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (SearchCondition.SystemNumber.IndexOf(',') > 0)
{
systemNumbers = SearchCondition.SystemNumber.Split(',').Select(s => { return(s.Trim()); });
}
if (systemNumbers != null && systemNumbers.Any())
{
sb.Append(" and SystemNumber in ( ");
foreach (string s in systemNumbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and SystemNumber like '%").Append(SearchCondition.SystemNumber.Trim()).Append("%' ");
}
}
if (!string.IsNullOrEmpty(SearchCondition.InvoiceNumber))
{
IEnumerable <string> invoiceNumbers = Enumerable.Empty <string>();
if (SearchCondition.InvoiceNumber.IndexOf("\n") > 0)
{
invoiceNumbers = SearchCondition.InvoiceNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (SearchCondition.InvoiceNumber.IndexOf(',') > 0)
{
invoiceNumbers = SearchCondition.InvoiceNumber.Split(',').Select(s => { return(s.Trim()); });
}
if (invoiceNumbers != null && invoiceNumbers.Any())
{
sb.Append(" and InvoiceNumber in ( ");
foreach (string s in invoiceNumbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and InvoiceNumber like '%" + SearchCondition.InvoiceNumber.Trim() + "%' ");
}
}
if (SearchCondition.CustomerOrShipperID.HasValue && SearchCondition.CustomerOrShipperID.Value != 0)
{
sb.Append(" and CustomerOrShipperID=").Append(SearchCondition.CustomerOrShipperID).Append(" ");
}
if (SearchCondition.ProjectUserCustomerIDs != null && SearchCondition.ProjectUserCustomerIDs.Any())
{
sb.Append(" and RelatedCustomerID in ( ");
foreach (long id in SearchCondition.ProjectUserCustomerIDs)
{
sb.Append(id).Append(",");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and RelatedCustomerID=0 ");
}
if (SearchCondition.InvoiceType != null && SearchCondition.InvoiceType.Value != 0)
{
sb.Append(" and InvoiceType=").Append(SearchCondition.InvoiceType).Append(" ");
}
if (SearchCondition.EstimateDate.HasValue)
{
sb.Append(" and EstimateDate >= '").Append(SearchCondition.EstimateDate.Value.DateTimeToString()).Append("' ");
}
if (SearchCondition.EndEstimateDate.HasValue)
{
sb.Append(" and EstimateDate <= '").Append(SearchCondition.EndEstimateDate.Value.DateTimeToString()).Append(" 23:59' ");
}
return(sb.ToString());
}
/// <summary> /// 根据输入条件查询发票列表 /// </summary> /// <param name="condition">搜索条件</param> /// <param name="pageSize">页大小</param> /// <returns>发票实体列表</returns> /// <remarks>2013-07-09 周瑜 创建</remarks> public abstract Pager <SoOrder> QuickSearch(InvoiceSearchCondition condition, int pageSize);
/// <summary> /// 根据输入条件查询发票列表 /// </summary> /// <param name="condition">搜索条件</param> /// <param name="pageSize">页大小</param> /// <returns>发票实体列表</returns> /// <remarks>2013-07-09 周瑜 创建</remarks> public abstract Pager <CBFnInvoice> Search(InvoiceSearchCondition condition, int pageSize);
/// <summary>
/// 根据输入条件查询发票
/// </summary>
/// <param name="condition">搜索条件</param>
/// <param name="pageSize">页大小</param>
/// <returns>发票实体列表</returns>
/// <remarks>2013-07-10 周瑜 创建</remarks>
/// <remarks>2013-11-13 周唐炬 重构</remarks>
public override Pager <CBFnInvoice> Search(InvoiceSearchCondition condition, int pageSize)
{
//查询已审核且已付款的记录
const string sql = @"(SELECT B.SYSNO AS OrderSysNo,
B.TransactionSysNo as TransactionSysNo,
A.SysNo,
A.InvoiceTypeSysNo,
A.InvoiceCode,
A.InvoiceNo,
A.InvoiceAmount,
A.InvoiceTitle,
A.Status,
A.LastUpdateDate
FROM SOORDER B
LEFT JOIN FNINVOICE A
ON B.TRANSACTIONSYSNO = A.TRANSACTIONSYSNO
WHERE (@0 IS NULL OR charindex(A.INVOICECODE, @0) > 0)
AND (@1 IS NULL OR charindex(A.INVOICENO, @1) > 0)
AND (@2 IS NULL OR charindex(A.INVOICETITLE, @2) > 0)
AND (@3 IS NULL OR A.STATUS = @3)
AND (@4 IS NULL OR A.INVOICETYPESYSNO = @4)
AND B.STATUS <> @5 --不能是作废订单
--AND B.PAYSTATUS = @PayStatus --订单已支付
AND B.TRANSACTIONSYSNO IS NOT NULL
AND EXISTS
(SELECT 1
FROM splitstr(@6, ',') tmp
WHERE tmp.col = B.DEFAULTWAREHOUSESYSNO)
AND (@7 IS NULL OR B.SYSNO = @7)
)tb";
var warehouseSysNoList = string.Empty;
if (condition.WarehouseSysNoList != null && condition.WarehouseSysNoList.Any())
{
warehouseSysNoList = string.Join(",", condition.WarehouseSysNoList);
}
var paras = new object[]
{
condition.InvoiceCode,
condition.InvoiceNo,
condition.InvoiceTitle,
condition.Status,
condition.InvoiceTypeSysNo,
OrderStatus.销售单状态.作废.GetHashCode(),
warehouseSysNoList,
condition.OrderSysNo
};
var dataList = Context.Select <CBFnInvoice>("tb.*").From(sql);
var dataCount = Context.Select <int>("count(1)").From(sql);
dataList.Parameters(paras);
dataCount.Parameters(paras);
var pager = new Pager <CBFnInvoice>
{
Rows = dataList.OrderBy(@"tb.OrderSysNo desc").Paging(condition.CurrentPage, pageSize).QueryMany(),
TotalRows = dataCount.QuerySingle()
};
return(pager);
}
public ActionResult List()
{
var model = new InvoiceSearchCondition();
return(View(model));
}
