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