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)); }