public void Query(NetPayQueryVM req, int pageSize, int pageIndex, string sortField, Action <NetPayQueryResultVM> callback) { NetPayQueryFilter filter = req.ConvertVM <NetPayQueryVM, NetPayQueryFilter>(); filter.PagingInfo = new PagingInfo() { PageIndex = pageIndex, PageSize = pageSize, SortBy = sortField }; string relativeUrl = "/InvoiceService/NetPay/Query"; restClient.QueryDynamicData(relativeUrl, filter, (obj, args) => { if (args.FaultsHandle()) { return; } NetPayQueryResultVM result = new NetPayQueryResultVM(); result.ResultList = DynamicConverter <NetPayVM> .ConvertToVMList(args.Result.Rows); result.TotalCount = args.Result.TotalCount; callback(result); }); }
public virtual QueryResult QueryNetPay(NetPayQueryFilter request) { int totalCount; var dataTable = ObjectFactory <INetPayQueryDA> .Instance.Query(request, out totalCount); return(new QueryResult() { Data = dataTable, TotalCount = totalCount }); }
public DataTable Query(NetPayQueryFilter query, out int totalCount) { DataTable result = null; PagingInfoEntity pagingInfo = new PagingInfoEntity(); if (query.PagingInfo != null) { MapSortField(query.PagingInfo); pagingInfo.MaximumRows = query.PagingInfo.PageSize; pagingInfo.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize; pagingInfo.SortField = query.PagingInfo.SortBy; } CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryNetPayList"); using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingInfo, "Result.SysNo DESC")) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "NetPay.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, query.SysNo); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "NetPay.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, query.Status); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "NetPay.InputTime", DbType.DateTime, "@CreateDateFrom", QueryConditionOperatorType.MoreThanOrEqual, query.CreateDateFrom); string createDateTo = string.Empty; if (query.CreateDateTo.HasValue) { createDateTo = Convert.ToDateTime(query.CreateDateTo).AddDays(1).ToString(); } sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "NetPay.InputTime", DbType.DateTime, "@CreateDateTo", QueryConditionOperatorType.LessThan, createDateTo); if (!string.IsNullOrEmpty(query.SOSysNo)) { int[] sysno = Array.ConvertAll <string, int>(query.SOSysNo.Split('.'), new Converter <string, int>((source) => { if (string.IsNullOrEmpty(source)) { return(0); } else { int ResultInt = 0; System.Int32.TryParse(source, out ResultInt); return(ResultInt); } })); sb.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "NetPay.SOSysNo", QueryConditionOperatorType.In, string.Format(@"SELECT SOSysNo FROM [IPP3].[dbo].[Finance_NetPay] WHERE MasterSoSysNo IN ({0}) UNION ALL SELECT MasterSoSysNo FROM [IPP3].[dbo].[Finance_NetPay] WHERE SOSysNo IN ({0}) UNION ALL SELECT SoSysNo FROM [IPP3].[dbo].[Finance_NetPay] WHERE SOSysNo IN ({0}) UNION ALL SELECT SoSysNo FROM [IPP3].[dbo].[Finance_NetPay] WHERE MasterSoSysNo in (SELECT MasterSoSysNo FROM [IPP3].[dbo].[Finance_NetPay] WHERE SOSysNo IN ({0}))", string.Join(",", sysno))); } if (!string.IsNullOrEmpty(query.StockID)) { sb.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format(@"Exists (SELECT SOSysNo FROM OverseaOrderManagement.dbo.V_OM_SO_ItemNoHistory SOItem WHERE WarehouseNumber={0} AND SOMaster.SysNo=SOItem.SOSysNo )", query.StockID)); } sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOCheckShipping.SOType", DbType.Int32, "@SOType", QueryConditionOperatorType.Equal, query.SOType); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "NetPay.PayTypeSysNo", DbType.Int32, "@PayType", QueryConditionOperatorType.Equal, query.PayTypeCode); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.Status", DbType.Int32, "@SOStatus", QueryConditionOperatorType.Equal, query.SOStatus); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.ShipTypeSysNo", DbType.Int32, "@ShipType", QueryConditionOperatorType.Equal, query.ShipTypeCode); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "NetPay.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, query.CompanyCode); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "NetPay.Source", DbType.Int32, "@Source", QueryConditionOperatorType.Equal, query.Source); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.DeliveryDate", DbType.DateTime, "@DeliveryDate", QueryConditionOperatorType.Equal, query.DeliveryDate); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.DeliveryTimeRange", DbType.Int32, "@DeliveryTimeRange", QueryConditionOperatorType.Equal, query.DeliveryTimeRange); if (query.SettlementStatus.HasValue) { if (GroupBuyingSettlementStatus.Null == query.SettlementStatus) { sb.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOCheckShipping.SettlementStatus", DbType.String, "@SettlementStatus", QueryConditionOperatorType.IsNull, DBNull.Value); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOCheckShipping.SOType", DbType.String, "@SOType1", QueryConditionOperatorType.Equal, 7); sb.ConditionConstructor.EndGroupCondition(); } else { string settlementStatus = string.Empty; switch (query.SettlementStatus.Value) { case GroupBuyingSettlementStatus.PlanFail: settlementStatus = "P"; break; case GroupBuyingSettlementStatus.Success: settlementStatus = "S"; break; case GroupBuyingSettlementStatus.Fail: settlementStatus = "F"; break; default: throw new InvalidOperationException(string.Format("the type is not support of {0}", query.SettlementStatus.Value.ToString())); } sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOCheckShipping.SettlementStatus", DbType.String, "@SettlementStatus", QueryConditionOperatorType.Equal, settlementStatus); } } //CashPay(现金支付)+PayPrice(手续费)+ShipPrice(运费)+PremiumAmt(保价费)+DiscountAmt(折扣)=OrderAmt(订单总金额) sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "(SOMaster.CashPay+SOMaster.PayPrice+SOMaster.ShipPrice+SOMaster.PremiumAmt+SOMaster.DiscountAmt)", DbType.Decimal, "@AmtFrom", QueryConditionOperatorType.MoreThanOrEqual, query.AmtFrom); sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "(SOMaster.CashPay+SOMaster.PayPrice+SOMaster.ShipPrice+SOMaster.PremiumAmt+SOMaster.DiscountAmt)", DbType.Decimal, "@AmtTo", QueryConditionOperatorType.LessThanOrEqual, query.AmtTo); cmd.CommandText = sb.BuildQuerySql(); cmd.CommandTimeout = 120; EnumColumnList enumColumns = new EnumColumnList(); enumColumns.Add("Status", typeof(NetPayStatus)); enumColumns.Add("Source", typeof(NetPaySource)); enumColumns.Add("SOStatus", typeof(ECCentral.BizEntity.SO.SOStatus)); enumColumns.Add("SettlementStatus", typeof(GroupBuyingSettlementStatus)); result = cmd.ExecuteDataTable(enumColumns); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(result); } }