/// <summary>
/// 用户分组 查询
/// </summary>
/// <param name="where">查询条件</param>
/// <param name="orderby">排序方式</param>
/// <param name="PageSize">每页数量</param>
/// <param name="PageIndex">页码</param>
/// <param name="cmdParameters">查询条件赋值</param>
/// <returns></returns>
public static PaginatedList<UserGroup> Take(Zippy.Data.IDalProvider db, string @where, string orderby, int PageSize, int PageIndex, params System.Data.Common.DbParameter[] cmdParameters)
{
PaginatedList<UserGroup> rtn = new PaginatedList<UserGroup>();
List<UserGroup> records = db.Take<UserGroup>(where + " order by " + orderby, PageSize, PageIndex, cmdParameters);
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = db.Count<UserGroup>(where, cmdParameters);
return rtn;
}
public static PaginatedList <Culture> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <Culture> rtn = new PaginatedList <Culture>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " [TenantID]=@TenantID";
dbParams.Add(db.CreateParameter("TenantID", tenantID));
#region 开始查询
if (paras != null)
{
object qCultureType = paras["qCultureType"];
if (qCultureType != null && qCultureType.ToString() != "")
{
Int32 intqCultureType = (Int32)qCultureType;
if (intqCultureType > 0)
{
where += " and ([CultureType] & @CultureType = @CultureType)";
dbParams.Add(db.CreateParameter("CultureType", qCultureType));
}
}
object qCultureStatus = paras["qCultureStatus"];
if (qCultureStatus != null && qCultureStatus.ToString() != "")
{
Int32 intqCultureStatus = (Int32)qCultureStatus;
if (intqCultureStatus > 0)
{
where += " and [CultureStatus] = @CultureStatus";
dbParams.Add(db.CreateParameter("CultureStatus", qCultureStatus));
}
}
object qCreateDateStart = paras["qCreateDateStart"];
if (qCreateDateStart != null && qCreateDateStart.ToString() != "")
{
where += " and [CreateDate] >= @CreateDateStart";
dbParams.Add(db.CreateParameter("CreateDateStart", qCreateDateStart));
}
object qCreateDateEnd = paras["qCreateDateEnd"];
if (qCreateDateEnd != null && qCreateDateEnd.ToString() != "")
{
where += " and [CreateDate] < @CreateDateEnd";
dbParams.Add(db.CreateParameter("CreateDateEnd", ((DateTime)qCreateDateEnd).AddDays(1)));
}
}
#endregion
string orderBy = "[CreateDate] desc";
if (orderCol == 0)
{
orderBy = "[CreateDate] desc";
}
else if (orderCol == 1)
{
orderBy = "[Location]";
}
else if (orderCol == 2)
{
orderBy = "[Location] desc";
}
else if (orderCol == 3)
{
orderBy = "[CultureType]";
}
else if (orderCol == 4)
{
orderBy = "[CultureType] desc";
}
else if (orderCol == 5)
{
orderBy = "[CultureStatus]";
}
else if (orderCol == 6)
{
orderBy = "[CultureStatus] desc";
}
else if (orderCol == 7)
{
orderBy = "[CreateDate]";
}
else if (orderCol == 8)
{
orderBy = "[CreateDate] desc";
}
int RecordCount = db.Count <Culture>(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 <Culture> records = db.Take <Culture>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}
public static PaginatedList <Z01Brand> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <Z01Brand> rtn = new PaginatedList <Z01Brand>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " [TenantID]=@TenantID";
dbParams.Add(db.CreateParameter("TenantID", tenantID));
#region 开始查询
if (paras != null)
{
object qTitle = paras["qTitle"];
if (qTitle.IsNotNullOrEmpty())
{
where += " and [Title] like @Title";
dbParams.Add(db.CreateParameter("Title", "%" + qTitle + "%"));
}
}
#endregion
string orderBy = "[CreateDate] desc";
if (orderCol == 0)
{
orderBy = "[CreateDate] desc";
}
else if (orderCol == 1)
{
orderBy = "[Title]";
}
else if (orderCol == 2)
{
orderBy = "[Title] desc";
}
else if (orderCol == 3)
{
orderBy = "[ImagePath]";
}
else if (orderCol == 4)
{
orderBy = "[ImagePath] desc";
}
else if (orderCol == 5)
{
orderBy = "[CreateDate]";
}
else if (orderCol == 6)
{
orderBy = "[CreateDate] desc";
}
int RecordCount = db.Count <Z01Brand>(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 <Z01Brand> records = db.Take <Z01Brand>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}
public static PaginatedList <ExtProperty> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <ExtProperty> rtn = new PaginatedList <ExtProperty>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " [TenantID]=@TenantID";
dbParams.Add(db.CreateParameter("TenantID", tenantID));
#region 开始查询
if (paras != null)
{
object qTitle = paras["qTitle"];
if (qTitle.IsNotNullOrEmpty())
{
where += " and [Title] like @Title";
dbParams.Add(db.CreateParameter("Title", "%" + qTitle + "%"));
}
object qCreateDateStart = paras["qCreateDateStart"];
if (qCreateDateStart != null && qCreateDateStart.ToString() != "")
{
where += " and [CreateDate] >= @CreateDateStart";
dbParams.Add(db.CreateParameter("CreateDateStart", qCreateDateStart));
}
object qCreateDateEnd = paras["qCreateDateEnd"];
if (qCreateDateEnd != null && qCreateDateEnd.ToString() != "")
{
where += " and [CreateDate] < @CreateDateEnd";
dbParams.Add(db.CreateParameter("CreateDateEnd", ((DateTime)qCreateDateEnd).AddDays(1)));
}
}
#endregion
string orderBy = "[CreateDate] desc";
if (orderCol == 0)
{
orderBy = "[CreateDate] desc";
}
else if (orderCol == 1)
{
orderBy = "[PropertyID]";
}
else if (orderCol == 2)
{
orderBy = "[PropertyID] desc";
}
else if (orderCol == 3)
{
orderBy = "[Title]";
}
else if (orderCol == 4)
{
orderBy = "[Title] desc";
}
else if (orderCol == 5)
{
orderBy = "[TemplateID]";
}
else if (orderCol == 6)
{
orderBy = "[TemplateID] desc";
}
else if (orderCol == 7)
{
orderBy = "[DisplayOrder]";
}
else if (orderCol == 8)
{
orderBy = "[DisplayOrder] desc";
}
else if (orderCol == 9)
{
orderBy = "[CreateDate]";
}
else if (orderCol == 10)
{
orderBy = "[CreateDate] desc";
}
int RecordCount = db.Count <ExtProperty>(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 <ExtProperty> records = db.Take <ExtProperty>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}
public static PaginatedList <Z10Order> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <Z10Order> rtn = new PaginatedList <Z10Order>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " [TenantID]=@TenantID";
dbParams.Add(db.CreateParameter("TenantID", tenantID));
#region 开始查询
if (paras != null)
{
object qCustomerID = paras["qCustomerID"];
if (qCustomerID.IsNotNullOrEmpty())
{
where += " and [CustomerID] = @CustomerID";
dbParams.Add(db.CreateParameter("CustomerID", qCustomerID));
}
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 qOrderType = paras["qOrderType"];
if (qOrderType.IsNotNullOrEmpty())
{
where += " and ([OrderType]&@OrderType)=@OrderType";
dbParams.Add(db.CreateParameter("OrderType", qOrderType));
}
object qIsSnap = paras["qIsSnap"];
if (qIsSnap.IsNotNullOrEmpty())
{
where += " and [IsSnap] = @IsSnap";
dbParams.Add(db.CreateParameter("IsSnap", qIsSnap));
}
object qDeleteFlag = paras["qDeleteFlag"];
if (qDeleteFlag.IsNotNullOrEmpty())
{
where += " and [DeleteFlag] = @DeleteFlag";
dbParams.Add(db.CreateParameter("DeleteFlag", qDeleteFlag));
}
}
#endregion
string orderBy = "[CreateDate] desc";
if (orderCol == 0)
{
orderBy = "[CreateDate] desc";
}
else if (orderCol == 1)
{
orderBy = "[OrderID]";
}
else if (orderCol == 2)
{
orderBy = "[OrderID] desc";
}
else if (orderCol == 3)
{
orderBy = "[CustomerID]";
}
else if (orderCol == 4)
{
orderBy = "[CustomerID] desc";
}
else if (orderCol == 5)
{
orderBy = "[DateOrder]";
}
else if (orderCol == 6)
{
orderBy = "[DateOrder] desc";
}
else if (orderCol == 7)
{
orderBy = "[DateShip]";
}
else if (orderCol == 8)
{
orderBy = "[DateShip] desc";
}
else if (orderCol == 9)
{
orderBy = "[CreateDate]";
}
else if (orderCol == 10)
{
orderBy = "[CreateDate] desc";
}
int RecordCount = db.Count <Z10Order>(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 <Z10Order> records = db.Take <Z10Order>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}
public static PaginatedList <Z01CustomerInCategory> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <Z01CustomerInCategory> rtn = new PaginatedList <Z01CustomerInCategory>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " [TenantID]=@TenantID";
dbParams.Add(db.CreateParameter("TenantID", tenantID));
#region 开始查询
if (paras != null)
{
object qProductIDStart = paras["qProductIDStart"];
if (qProductIDStart != null && qProductIDStart.ToString() != "")
{
where += " and [ProductID] >= @ProductIDStart";
dbParams.Add(db.CreateParameter("ProductIDStart", qProductIDStart));
}
object qProductIDEnd = paras["qProductIDEnd"];
if (qProductIDEnd != null && qProductIDEnd.ToString() != "")
{
where += " and [ProductID] <= @ProductID";
dbParams.Add(db.CreateParameter("ProductIDEnd", qProductIDEnd));
}
object qCategoryIDStart = paras["qCategoryIDStart"];
if (qCategoryIDStart != null && qCategoryIDStart.ToString() != "")
{
where += " and [CategoryID] >= @CategoryIDStart";
dbParams.Add(db.CreateParameter("CategoryIDStart", qCategoryIDStart));
}
object qCategoryIDEnd = paras["qCategoryIDEnd"];
if (qCategoryIDEnd != null && qCategoryIDEnd.ToString() != "")
{
where += " and [CategoryID] <= @CategoryID";
dbParams.Add(db.CreateParameter("CategoryIDEnd", qCategoryIDEnd));
}
object qCreateDateStart = paras["qCreateDateStart"];
if (qCreateDateStart != null && qCreateDateStart.ToString() != "")
{
where += " and [CreateDate] >= @CreateDateStart";
dbParams.Add(db.CreateParameter("CreateDateStart", qCreateDateStart));
}
object qCreateDateEnd = paras["qCreateDateEnd"];
if (qCreateDateEnd != null && qCreateDateEnd.ToString() != "")
{
where += " and [CreateDate] < @CreateDateEnd";
dbParams.Add(db.CreateParameter("CreateDateEnd", ((DateTime)qCreateDateEnd).AddDays(1)));
}
}
#endregion
string orderBy = "[CreateDate] desc";
if (orderCol == 0)
{
orderBy = "[CreateDate] desc";
}
else if (orderCol == 1)
{
orderBy = "[CICID]";
}
else if (orderCol == 2)
{
orderBy = "[CICID] desc";
}
else if (orderCol == 3)
{
orderBy = "[ProductID]";
}
else if (orderCol == 4)
{
orderBy = "[ProductID] desc";
}
else if (orderCol == 5)
{
orderBy = "[CategoryID]";
}
else if (orderCol == 6)
{
orderBy = "[CategoryID] desc";
}
else if (orderCol == 7)
{
orderBy = "[CreateDate]";
}
else if (orderCol == 8)
{
orderBy = "[CreateDate] desc";
}
int RecordCount = db.Count <Z01CustomerInCategory>(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 <Z01CustomerInCategory> records = db.Take <Z01CustomerInCategory>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}
public static PaginatedList <Z30Communication> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <Z30Communication> rtn = new PaginatedList <Z30Communication>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " [TenantID]=@TenantID";
dbParams.Add(db.CreateParameter("TenantID", tenantID));
#region 开始查询
if (paras != null)
{
object qCreator = paras["qCreator"];
if (qCreator.IsNotNullOrEmpty())
{
where += " and [Creator] = @Creator";
dbParams.Add(db.CreateParameter("Creator", qCreator));
}
object qCustomerID = paras["qCustomerID"];
if (qCustomerID.IsNotNullOrEmpty())
{
where += " and [CustomerID] = @CustomerID";
dbParams.Add(db.CreateParameter("CustomerID", qCustomerID));
}
object qContent = paras["qContent"];
if (qContent.IsNotNullOrEmpty())
{
where += " and [Content] like @Content";
dbParams.Add(db.CreateParameter("Content", "%" + qContent + "%"));
}
object qWish = paras["qWish"];
if (qWish.IsNotNullOrEmpty())
{
where += " and [Wish] >= @Wish";
dbParams.Add(db.CreateParameter("Wish", qWish));
}
object qSuccessRatio = paras["qSuccessRatio"];
if (qSuccessRatio.IsNotNullOrEmpty())
{
where += " and [SuccessRatio] >= @SuccessRatio";
dbParams.Add(db.CreateParameter("SuccessRatio", qSuccessRatio));
}
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] < @CreateDate";
dbParams.Add(db.CreateParameter("CreateDateEnd", ((DateTime)qCreateDateEnd).AddDays(1)));
}
}
#endregion
string orderBy = "[CreateDate] desc";
if (orderCol == 0)
{
orderBy = "[CreateDate] desc";
}
else if (orderCol == 1)
{
orderBy = "[CommunicationID]";
}
else if (orderCol == 2)
{
orderBy = "[CommunicationID] desc";
}
else if (orderCol == 3)
{
orderBy = "[CustomerID]";
}
else if (orderCol == 4)
{
orderBy = "[CustomerID] desc";
}
else if (orderCol == 5)
{
orderBy = "[VisitWay]";
}
else if (orderCol == 6)
{
orderBy = "[VisitWay] desc";
}
else if (orderCol == 7)
{
orderBy = "[Wish]";
}
else if (orderCol == 8)
{
orderBy = "[Wish] desc";
}
else if (orderCol == 9)
{
orderBy = "[NextVisitDate]";
}
else if (orderCol == 10)
{
orderBy = "[NextVisitDate] desc";
}
else if (orderCol == 11)
{
orderBy = "[SuccessRatio]";
}
else if (orderCol == 12)
{
orderBy = "[SuccessRatio] desc";
}
else if (orderCol == 13)
{
orderBy = "[VisitDate]";
}
else if (orderCol == 14)
{
orderBy = "[VisitDate] desc";
}
else if (orderCol == 15)
{
orderBy = "[VisitDuration]";
}
else if (orderCol == 16)
{
orderBy = "[VisitDuration] desc";
}
else if (orderCol == 17)
{
orderBy = "[CreateDate]";
}
else if (orderCol == 18)
{
orderBy = "[CreateDate] desc";
}
int RecordCount = db.Count <Z30Communication>(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 <Z30Communication> records = db.Take <Z30Communication>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}
public static PaginatedList <User> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <User> rtn = new PaginatedList <User>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " [TenantID]=@TenantID";
dbParams.Add(db.CreateParameter("TenantID", tenantID));
#region 开始查询
if (paras != null)
{
object qUserName = paras["qUserName"];
if (qUserName.IsNotNullOrEmpty())
{
where += " and [UserName] like @UserName";
dbParams.Add(db.CreateParameter("UserName", "%" + qUserName + "%"));
}
object qEmail = paras["qEmail"];
if (qEmail.IsNotNullOrEmpty())
{
where += " and [Email] like @Email";
dbParams.Add(db.CreateParameter("Email", "%" + qEmail + "%"));
}
object qName = paras["qName"];
if (qName.IsNotNullOrEmpty())
{
where += " and [Name] like @Name";
dbParams.Add(db.CreateParameter("Name", "%" + qName + "%"));
}
object qNickname = paras["qNickname"];
if (qNickname.IsNotNullOrEmpty())
{
where += " and [Nickname] like @Nickname";
dbParams.Add(db.CreateParameter("Nickname", "%" + qNickname + "%"));
}
object qMobileID1 = paras["qMobileID1"];
if (qMobileID1.IsNotNullOrEmpty())
{
where += " and [MobileID1] like @MobileID1";
dbParams.Add(db.CreateParameter("MobileID1", "%" + qMobileID1 + "%"));
}
object qMobileID2 = paras["qMobileID2"];
if (qMobileID2.IsNotNullOrEmpty())
{
where += " and [MobileID2] like @MobileID2";
dbParams.Add(db.CreateParameter("MobileID2", "%" + qMobileID2 + "%"));
}
object qUserType = paras["qUserType"];
if (qUserType.IsNotNullOrEmpty())
{
Int32 intqUserType = (Int32)qUserType;
if (intqUserType > 0)
{
where += " and ([UserType] & @UserType = @UserType)";
dbParams.Add(db.CreateParameter("UserType", qUserType));
}
}
object qUserStatus = paras["qUserStatus"];
if (qUserStatus.IsNotNullOrEmpty())
{
Int32 intqUserStatus = (Int32)qUserStatus;
if (intqUserStatus > 0)
{
where += " and [UserStatus] = @UserStatus";
dbParams.Add(db.CreateParameter("UserStatus", qUserStatus));
}
}
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 qGroupID = paras["qGroupID"];
if (qGroupID.IsNotNullOrEmpty())
{
where += " and [UserID] in (select [UserID] from [UserGroup] where [GroupID]=@GroupID)";
dbParams.Add(db.CreateParameter("GroupID", qGroupID));
}
}
#endregion
string orderBy = "[CreateDate] desc";
if (orderCol == 0)
{
orderBy = "[CreateDate] desc";
}
else if (orderCol == 1)
{
orderBy = "[UserName]";
}
else if (orderCol == 2)
{
orderBy = "[UserName] desc";
}
else if (orderCol == 3)
{
orderBy = "[Email]";
}
else if (orderCol == 4)
{
orderBy = "[Email] desc";
}
else if (orderCol == 5)
{
orderBy = "[Nickname]";
}
else if (orderCol == 6)
{
orderBy = "[Nickname] desc";
}
int RecordCount = db.Count <User>(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 <User> records = db.Take <User>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}
public static PaginatedList <Z01UserInfo> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <Z01UserInfo> rtn = new PaginatedList <Z01UserInfo>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " [TenantID]=@TenantID";
dbParams.Add(db.CreateParameter("TenantID", tenantID));
#region 开始查询
if (paras != null)
{
object qName = paras["qName"];
if (qName.IsNotNullOrEmpty())
{
where += " and [Name] like @Name";
dbParams.Add(db.CreateParameter("Name", "%" + qName + "%"));
}
object qNickname = paras["qNickname"];
if (qNickname.IsNotNullOrEmpty())
{
where += " and [Nickname] like @Nickname";
dbParams.Add(db.CreateParameter("Nickname", "%" + qNickname + "%"));
}
object qEmail = paras["qEmail"];
if (qEmail.IsNotNullOrEmpty())
{
where += " and [Email] like @Email";
dbParams.Add(db.CreateParameter("Email", "%" + qEmail + "%"));
}
object qUserStatus = paras["qUserStatus"];
if (qUserStatus.IsNotNullOrEmpty())
{
Int32 intqUserStatus = (Int32)qUserStatus;
if (intqUserStatus > 0)
{
where += " and [UserStatus] = @UserStatus";
dbParams.Add(db.CreateParameter("UserStatus", qUserStatus));
}
}
object qCreateDateStart = paras["qCreateDateStart"];
if (qCreateDateStart != null && qCreateDateStart.ToString() != "")
{
where += " and [CreateDate] >= @CreateDateStart";
dbParams.Add(db.CreateParameter("CreateDateStart", qCreateDateStart));
}
object qCreateDateEnd = paras["qCreateDateEnd"];
if (qCreateDateEnd != null && qCreateDateEnd.ToString() != "")
{
where += " and [CreateDate] < @CreateDateEnd";
dbParams.Add(db.CreateParameter("CreateDateEnd", ((DateTime)qCreateDateEnd).AddDays(1)));
}
}
#endregion
string orderBy = "[CreateDate] desc";
if (orderCol == 0)
{
orderBy = "[CreateDate] desc";
}
else if (orderCol == 1)
{
orderBy = "[UserID]";
}
else if (orderCol == 2)
{
orderBy = "[UserID] desc";
}
else if (orderCol == 3)
{
orderBy = "[Name]";
}
else if (orderCol == 4)
{
orderBy = "[Name] desc";
}
else if (orderCol == 5)
{
orderBy = "[Nickname]";
}
else if (orderCol == 6)
{
orderBy = "[Nickname] desc";
}
else if (orderCol == 7)
{
orderBy = "[Email]";
}
else if (orderCol == 8)
{
orderBy = "[Email] desc";
}
else if (orderCol == 9)
{
orderBy = "[Tel1]";
}
else if (orderCol == 10)
{
orderBy = "[Tel1] desc";
}
else if (orderCol == 11)
{
orderBy = "[Tel2]";
}
else if (orderCol == 12)
{
orderBy = "[Tel2] desc";
}
else if (orderCol == 13)
{
orderBy = "[Avatar]";
}
else if (orderCol == 14)
{
orderBy = "[Avatar] desc";
}
else if (orderCol == 15)
{
orderBy = "[TitleID]";
}
else if (orderCol == 16)
{
orderBy = "[TitleID] desc";
}
else if (orderCol == 17)
{
orderBy = "[TenantID]";
}
else if (orderCol == 18)
{
orderBy = "[TenantID] desc";
}
else if (orderCol == 19)
{
orderBy = "[UserStatus]";
}
else if (orderCol == 20)
{
orderBy = "[UserStatus] desc";
}
else if (orderCol == 21)
{
orderBy = "[CreateDate]";
}
else if (orderCol == 22)
{
orderBy = "[CreateDate] desc";
}
int RecordCount = db.Count <Z01UserInfo>(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 <Z01UserInfo> records = db.Take <Z01UserInfo>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}
public static PaginatedList <EAP.Logic.Z10.View.V_DepotProduct> ListProduct(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <EAP.Logic.Z10.View.V_DepotProduct> rtn = new PaginatedList <EAP.Logic.Z10.View.V_DepotProduct>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " [TenantID]=@TenantID";
dbParams.Add(db.CreateParameter("TenantID", tenantID));
#region 开始查询
if (paras != null)
{
object qTitle = paras["qTitle"];
if (qTitle.IsNotNullOrEmpty())
{
where += " and ([ProductTitle] like @Title or [DepotTitle] like @Title) ";
dbParams.Add(db.CreateParameter("Title", "%" + qTitle + "%"));
}
object qDepot = paras["qDepot"];
if (qDepot.IsNotNullOrEmpty() && qDepot.ToInt64() > 0)
{
where += " and [DepotID] = @DepotID ";
dbParams.Add(db.CreateParameter("DepotID", qDepot));
}
}
#endregion
string orderBy = "[DepotProductID] desc";
if (orderCol == 0)
{
orderBy = "[DepotProductID] desc";
}
else if (orderCol == 1)
{
orderBy = "[ProductTitle]";
}
else if (orderCol == 2)
{
orderBy = "[ProductTitle] desc";
}
else if (orderCol == 3)
{
orderBy = "[DepotTitle]";
}
else if (orderCol == 4)
{
orderBy = "[DepotTitle] desc";
}
else if (orderCol == 5)
{
orderBy = "[StockSum]";
}
else if (orderCol == 6)
{
orderBy = "[StockSum] desc";
}
int RecordCount = db.Count <EAP.Logic.Z10.View.V_DepotProduct>(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.Z10.View.V_DepotProduct> records = db.Take <EAP.Logic.Z10.View.V_DepotProduct>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}
public static PaginatedList <Z01Product> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <Z01Product> rtn = new PaginatedList <Z01Product>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " [TenantID]=@TenantID";
dbParams.Add(db.CreateParameter("TenantID", tenantID));
#region 开始查询
if (paras != null)
{
object qTitle = paras["qTitle"];
if (qTitle.IsNotNullOrEmpty())
{
where += " and [Title] like @Title";
dbParams.Add(db.CreateParameter("Title", "%" + qTitle + "%"));
}
object qBrief = paras["qBrief"];
if (qBrief.IsNotNullOrEmpty())
{
where += " and [Brief] like @Brief";
dbParams.Add(db.CreateParameter("Brief", "%" + qBrief + "%"));
}
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 qModel1 = paras["qModel1"];
if (qModel1.IsNotNullOrEmpty())
{
where += " and [Model1] like @Model1";
dbParams.Add(db.CreateParameter("Model1", "%" + qModel1 + "%"));
}
object qModel2 = paras["qModel2"];
if (qModel2.IsNotNullOrEmpty())
{
where += " and [Model2] = @Model2";
dbParams.Add(db.CreateParameter("Model2", qModel2));
}
var qBrandID = paras["qBrandID"].ToInt64(0);
if (qBrandID > 0)
{
where += " and [BrandID] = @BrandID";
dbParams.Add(db.CreateParameter("BrandID", qBrandID));
}
var qStatus = paras["qStatus"].ToInt32(0);
if (qStatus > 0)
{
where += " and [ProductStatus] = @ProductStatus";
dbParams.Add(db.CreateParameter("ProductStatus", qStatus));
}
var qCateID = paras["qCateID"].ToInt64(0);
if (qCateID > 0)
{
where += " and [ProductID] in (select [ProductID] from Z01ProductInCategory where [CategoryID]=@CategoryID)";
dbParams.Add(db.CreateParameter("CategoryID", qCateID));
}
}
#endregion
string orderBy = "[ViewCount] desc, ProductID";
if (orderCol == 0)
{
orderBy = "[ViewCount] desc, ProductID";
}
else if (orderCol == 1)
{
orderBy = "[Title]";
}
else if (orderCol == 2)
{
orderBy = "[Title] desc";
}
else if (orderCol == 3)
{
orderBy = "[UnitID]";
}
else if (orderCol == 4)
{
orderBy = "[UnitID] desc";
}
else if (orderCol == 5)
{
orderBy = "[PriceList]";
}
else if (orderCol == 6)
{
orderBy = "[PriceList] desc";
}
else if (orderCol == 7)
{
orderBy = "[ProductStock]";
}
else if (orderCol == 8)
{
orderBy = "[ProductStock] desc";
}
else if (orderCol == 9)
{
orderBy = "[PriceSelling]";
}
else if (orderCol == 10)
{
orderBy = "[PriceSelling] desc";
}
else if (orderCol == 11)
{
orderBy = "[ImagePath]";
}
else if (orderCol == 12)
{
orderBy = "[ImagePath] desc";
}
else if (orderCol == 13)
{
orderBy = "[CreateDate]";
}
else if (orderCol == 14)
{
orderBy = "[CreateDate] desc";
}
int RecordCount = db.Count <Z01Product>(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 <Z01Product> records = db.Take <Z01Product>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}
public static PaginatedList <Z01Customer> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <Z01Customer> rtn = new PaginatedList <Z01Customer>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " [TenantID]=@TenantID";
dbParams.Add(db.CreateParameter("TenantID", tenantID));
#region 开始查询
if (paras != null)
{
object qTitle = paras["qTitle"];
if (qTitle.IsNotNullOrEmpty())
{
where += " and ( [Title] like @Title or [Tel1] like @Title or [Tel2] like @Title)";
dbParams.Add(db.CreateParameter("Title", "%" + qTitle + "%"));
}
object qEmail = paras["qEmail"];
if (qEmail.IsNotNullOrEmpty())
{
where += " and [Email] like @Email";
dbParams.Add(db.CreateParameter("Email", "%" + qEmail + "%"));
}
object qCustomerType = paras["qCustomerType"];
if (qCustomerType.IsNotNullOrEmpty())
{
where += " and ([CustomerType] & @CustomerType) = @CustomerType";
dbParams.Add(db.CreateParameter("CustomerType", qCustomerType));
}
object qCateID = paras["qCateID"];
if (qCateID.IsNotNullOrEmpty())
{
where += " and [CustomerID] in (select [CustomerID] from Z01CustomerInCategory where [CategoryID]=@CategoryID)";
dbParams.Add(db.CreateParameter("CategoryID", qCateID));
}
object qPrincipal = paras["qPrincipal"];
if (qPrincipal.IsNotNullOrEmpty())
{
where += " and [Principal]=@Principal";
dbParams.Add(db.CreateParameter("Principal", qPrincipal));
}
object qSiteStatus = paras["qSiteStatus"];
if (qSiteStatus.IsNotNullOrEmpty())
{
where += " and [CustomerStatus]=@CustomerStatus";
dbParams.Add(db.CreateParameter("CustomerStatus", qSiteStatus));
}
object qSuccessRatio = paras["qSuccessRatio"];
if (qSuccessRatio.IsNotNullOrEmpty())
{
where += " and [CustomerID] in (select CustomerID from Z30Communication where SuccessRatio>=@SuccessRatio)";
dbParams.Add(db.CreateParameter("SuccessRatio", qSuccessRatio));
}
}
#endregion
string orderBy = "[ManageHot] desc, CustomerID desc";
if (orderCol == 0)
{
orderBy = "[ManageHot] desc, CustomerID desc";
}
else if (orderCol == 1)
{
orderBy = "[Title]";
}
else if (orderCol == 2)
{
orderBy = "[Title] desc";
}
else if (orderCol == 3)
{
orderBy = "[Tel1]";
}
else if (orderCol == 4)
{
orderBy = "[Tel1] desc";
}
else if (orderCol == 5)
{
orderBy = "[Tel2]";
}
else if (orderCol == 6)
{
orderBy = "[Tel2] desc";
}
else if (orderCol == 7)
{
orderBy = "[Email]";
}
else if (orderCol == 8)
{
orderBy = "[Email] desc";
}
else if (orderCol == 11)
{
orderBy = "[CreateDate]";
}
else if (orderCol == 12)
{
orderBy = "[CreateDate] desc";
}
int RecordCount = db.Count <Z01Customer>(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 <Z01Customer> records = db.Take <Z01Customer>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}
public static PaginatedList <Z01CustomerPerson> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <Z01CustomerPerson> rtn = new PaginatedList <Z01CustomerPerson>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " [TenantID]=@TenantID";
dbParams.Add(db.CreateParameter("TenantID", tenantID));
#region 开始查询
if (paras != null)
{
object qName = paras["qName"];
if (qName.IsNotNullOrEmpty())
{
where += " and [Name] like @Name";
dbParams.Add(db.CreateParameter("Name", "%" + qName + "%"));
}
object qNickname = paras["qNickname"];
if (qNickname.IsNotNullOrEmpty())
{
where += " and [Nickname] like @Nickname";
dbParams.Add(db.CreateParameter("Nickname", "%" + qNickname + "%"));
}
object qCustomerID = paras["qCustomerID"];
if (qCustomerID.IsNotNullOrEmpty())
{
where += " and [CustomerID] = @CustomerID";
dbParams.Add(db.CreateParameter("CustomerID", qCustomerID));
}
}
#endregion
string orderBy = "[CreateDate] desc";
if (orderCol == 0)
{
orderBy = "[CreateDate] desc";
}
else if (orderCol == 1)
{
orderBy = "[Name]";
}
else if (orderCol == 2)
{
orderBy = "[Name] desc";
}
else if (orderCol == 3)
{
orderBy = "[Nickname]";
}
else if (orderCol == 4)
{
orderBy = "[Nickname] desc";
}
else if (orderCol == 5)
{
orderBy = "[Email]";
}
else if (orderCol == 6)
{
orderBy = "[Email] desc";
}
else if (orderCol == 7)
{
orderBy = "[Tel1]";
}
else if (orderCol == 8)
{
orderBy = "[Tel1] desc";
}
else if (orderCol == 9)
{
orderBy = "[Tel2]";
}
else if (orderCol == 10)
{
orderBy = "[Tel2] desc";
}
int RecordCount = db.Count <Z01CustomerPerson>(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 <Z01CustomerPerson> records = db.Take <Z01CustomerPerson>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}
public static PaginatedList <Permission> Query(Zippy.Data.IDalProvider db, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <Permission> rtn = new PaginatedList <Permission>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " 1=1";
#region 开始查询
if (paras != null)
{
object qTitle = paras["qTitle"];
if (qTitle.IsNotNullOrEmpty())
{
where += " and [Title] like @Title";
dbParams.Add(db.CreateParameter("Title", "%" + qTitle + "%"));
}
object qUrl = paras["qUrl"];
if (qUrl.IsNotNullOrEmpty())
{
where += " and [Url] like @Url";
dbParams.Add(db.CreateParameter("Url", "%" + qUrl + "%"));
}
object qFlag = paras["qFlag"];
if (qFlag.IsNotNullOrEmpty())
{
where += " and [Flag] like @Flag";
dbParams.Add(db.CreateParameter("Flag", "%" + qFlag + "%"));
}
object qParentID = paras["qParentID"];
if (qParentID.IsNotNullOrEmpty())
{
where += " and [ParentID] = @ParentID";
dbParams.Add(db.CreateParameter("ParentID", qParentID));
}
}
#endregion
string orderBy = " [DisplayOrder] asc";
int RecordCount = db.Count <Permission>(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 <Permission> records = db.Take <Permission>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
//重组 records
List <Permission> newRecords = new List <Permission>();
IEnumerable <Permission> rootRecords = records.Where(s => (s.ParentID ?? 0) == 0).OrderBy(s => s.DisplayOrder);
foreach (Permission per in rootRecords)
{
newRecords.Add(per);
newRecords.AddRange(records.Where(s => s.ParentID == per.PermissionID).OrderBy(s => s.DisplayOrder));
}
rtn.AddRange(newRecords);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}
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);
}
public static PaginatedList <Application> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int?orderCol)
{
PaginatedList <Application> rtn = new PaginatedList <Application>();
List <System.Data.Common.DbParameter> dbParams = new List <System.Data.Common.DbParameter>();
string where = " [TenantID]=@TenantID";
dbParams.Add(db.CreateParameter("TenantID", tenantID));
#region 开始查询
if (paras != null)
{
object qTitle = paras["qTitle"];
if (qTitle != null)
{
where += " and [Title] like @Title";
dbParams.Add(db.CreateParameter("Title", "%" + qTitle + "%"));
}
object qApplicationType = paras["qApplicationType"];
if (qApplicationType != null && qApplicationType.ToString() != "")
{
Int32 intqApplicationType = (Int32)qApplicationType;
if (intqApplicationType > 0)
{
where += " and ([ApplicationType] & @ApplicationType = @ApplicationType)";
dbParams.Add(db.CreateParameter("ApplicationType", qApplicationType));
}
}
object qApplicationStatus = paras["qApplicationStatus"];
if (qApplicationStatus != null && qApplicationStatus.ToString() != "")
{
Int32 intqApplicationStatus = (Int32)qApplicationStatus;
if (intqApplicationStatus > 0)
{
where += " and [ApplicationStatus] = @ApplicationStatus";
dbParams.Add(db.CreateParameter("ApplicationStatus", qApplicationStatus));
}
}
object qCreateDateStart = paras["qCreateDateStart"];
if (qCreateDateStart != null && qCreateDateStart.ToString() != "")
{
where += " and [CreateDate] >= @CreateDateStart";
dbParams.Add(db.CreateParameter("CreateDateStart", qCreateDateStart));
}
object qCreateDateEnd = paras["qCreateDateEnd"];
if (qCreateDateEnd != null && qCreateDateEnd.ToString() != "")
{
where += " and [CreateDate] < @CreateDateEnd";
dbParams.Add(db.CreateParameter("CreateDateEnd", ((DateTime)qCreateDateEnd).AddDays(1)));
}
}
#endregion
string orderBy = "[CreateDate] desc";
if (orderCol == 0)
{
orderBy = "[CreateDate] desc";
}
else if (orderCol == 1)
{
orderBy = "[ApplicationID]";
}
else if (orderCol == 2)
{
orderBy = "[ApplicationID] desc";
}
else if (orderCol == 3)
{
orderBy = "[Title]";
}
else if (orderCol == 4)
{
orderBy = "[Title] desc";
}
else if (orderCol == 5)
{
orderBy = "[ClientApiStartServeice]";
}
else if (orderCol == 6)
{
orderBy = "[ClientApiStartServeice] desc";
}
else if (orderCol == 7)
{
orderBy = "[ClientApiStopService]";
}
else if (orderCol == 8)
{
orderBy = "[ClientApiStopService] desc";
}
else if (orderCol == 9)
{
orderBy = "[ClientApiDeleteService]";
}
else if (orderCol == 10)
{
orderBy = "[ClientApiDeleteService] desc";
}
else if (orderCol == 11)
{
orderBy = "[ClientApiPauseService]";
}
else if (orderCol == 12)
{
orderBy = "[ClientApiPauseService] desc";
}
else if (orderCol == 13)
{
orderBy = "[ClientApiGetMenu]";
}
else if (orderCol == 14)
{
orderBy = "[ClientApiGetMenu] desc";
}
else if (orderCol == 15)
{
orderBy = "[ApplicationType]";
}
else if (orderCol == 16)
{
orderBy = "[ApplicationType] desc";
}
else if (orderCol == 17)
{
orderBy = "[ApplicationStatus]";
}
else if (orderCol == 18)
{
orderBy = "[ApplicationStatus] desc";
}
else if (orderCol == 19)
{
orderBy = "[CreateDate]";
}
else if (orderCol == 20)
{
orderBy = "[CreateDate] desc";
}
int RecordCount = db.Count <Application>(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 <Application> records = db.Take <Application>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return(rtn);
}