public static PaginatedList <EAP.Logic.Z01.View.V_FinancialFlow> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol, out decimal sumAll) { PaginatedList <EAP.Logic.Z01.View.V_FinancialFlow> rtn = new PaginatedList <EAP.Logic.Z01.View.V_FinancialFlow>(); List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>(); string where = " [TenantID]=@TenantID"; dbParams.Add(db.CreateParameter("TenantID", tenantID)); object qCurrency = null; #region 开始查询 if (paras != null) { object qBankID = paras["qBankID"]; if (qBankID.IsNotNullOrEmpty()) { where += " and [BankID] = @BankID"; dbParams.Add(db.CreateParameter("BankID", qBankID)); } object qCategoryID = paras["qCategoryID"]; if (qCategoryID.IsNotNullOrEmpty()) { where += " and [CategoryID] = @CategoryID"; dbParams.Add(db.CreateParameter("CategoryID", qCategoryID)); } qCurrency = paras["qCurrency"]; if (qCurrency.IsNotNullOrEmpty()) { where += " and [Currency] = @Currency"; dbParams.Add(db.CreateParameter("Currency", qCurrency)); } object qFlowType = paras["qFlowType"]; if (qFlowType.IsNotNullOrEmpty()) { Int32 intqFlowType = (Int32)qFlowType; if (intqFlowType > 0) { where += " and ([FlowType] & @FlowType = @FlowType)"; dbParams.Add(db.CreateParameter("FlowType", qFlowType)); } } object qFlowStatus = paras["qFlowStatus"]; if (qFlowStatus.IsNotNullOrEmpty()) { Int32 intqFlowStatus = (Int32)qFlowStatus; if (intqFlowStatus > 0) { where += " and [FlowStatus] = @FlowStatus"; dbParams.Add(db.CreateParameter("FlowStatus", qFlowStatus)); } } object qCreateDateStart = paras["qCreateDateStart"]; if (qCreateDateStart.IsNotNullOrEmpty()) { where += " and [CreateDate] >= @CreateDateStart"; dbParams.Add(db.CreateParameter("CreateDateStart", qCreateDateStart)); } object qCreateDateEnd = paras["qCreateDateEnd"]; if (qCreateDateEnd.IsNotNullOrEmpty()) { where += " and [CreateDate] < @CreateDateEnd"; dbParams.Add(db.CreateParameter("CreateDateEnd", ((DateTime)qCreateDateEnd).AddDays(1))); } object qInOut = paras["qInOut"]; if (qInOut.IsNotNullOrEmpty()) { if (qInOut.Equals("In")) { where += " and [Amount]>=0"; } else if (qInOut.Equals("Out")) { where += " and [Amount]<0"; } } } #endregion string orderBy = "[CreateDate] desc"; if (orderCol == 0) { orderBy = "[CreateDate] desc"; } else if (orderCol == 1) { orderBy = "[FlowID]"; } else if (orderCol == 2) { orderBy = "[FlowID] desc"; } else if (orderCol == 3) { orderBy = "[BankID]"; } else if (orderCol == 4) { orderBy = "[BankID] desc"; } else if (orderCol == 5) { orderBy = "[CategoryID]"; } else if (orderCol == 6) { orderBy = "[CategoryID] desc"; } else if (orderCol == 7) { orderBy = "[OrderID]"; } else if (orderCol == 8) { orderBy = "[OrderID] desc"; } else if (orderCol == 9) { orderBy = "[FlowType]"; } else if (orderCol == 10) { orderBy = "[FlowType] desc"; } else if (orderCol == 11) { orderBy = "[FlowStatus]"; } else if (orderCol == 12) { orderBy = "[FlowStatus] desc"; } else if (orderCol == 13) { orderBy = "[CreateDate]"; } else if (orderCol == 14) { orderBy = "[CreateDate] desc"; } int RecordCount = db.Count <EAP.Logic.Z01.View.V_FinancialFlow>(where, dbParams.ToArray()); int PageCount = 0; if (RecordCount % PageSize == 0) { PageCount = RecordCount / PageSize; } else { PageCount = RecordCount / PageSize + 1; } if (PageIndex > PageCount) { PageIndex = PageCount; } if (PageIndex < 1) { PageIndex = 1; } List <EAP.Logic.Z01.View.V_FinancialFlow> records = db.Take <EAP.Logic.Z01.View.V_FinancialFlow>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray()); rtn.AddRange(records); rtn.PageIndex = PageIndex; rtn.PageSize = PageSize; rtn.TotalCount = RecordCount; if (qCurrency.IsNotNullOrEmpty()) { string sqlSum = "select sum(Amount) as sAmount from V_FinancialFlow where " + where; object oSum = db.ExecuteScalar(sqlSum, dbParams.ToArray()); sumAll = oSum.ToDecimal(); } else { sumAll = 0; } return(rtn); }