/// <summary>
/// 从数据库中载入订单
/// </summary>
/// <param name="orderID"></param>
/// <param name="tenantID"></param>
/// <param name="db"></param>
/// <returns></returns>
public static Order LoadFromDB(long orderID, Guid tenantID, Zippy.Data.IDalProvider db)
{
Order order = new Order();
order.Z10Order = db.FindUnique<Z10Order>("OrderID=@OrderID and TenantID=@TenantID",
db.CreateParameter("OrderID", orderID),
db.CreateParameter("TenantID", tenantID));
order.Items = db.Take<Z10OrderItem>("OrderID=@OrderID and TenantID=@TenantID",
db.CreateParameter("OrderID", orderID),
db.CreateParameter("TenantID", tenantID));
return order;
}
public static Z01Beetle.Entity.Z01Customer GetCustomer(this Z10Order order, Zippy.Data.IDalProvider db)
{
Z01Beetle.Entity.Z01Customer rtn = db.FindUnique<Z01Beetle.Entity.Z01Customer>("CustomerID=@CustomerID", db.CreateParameter("CustomerID", order.CustomerID));
if (rtn == null)
rtn = new Z01Beetle.Entity.Z01Customer();
return rtn;
}
public static List<Z10Cabbage.Entity.Z10Depot> GetDepots(Guid tenantID, Zippy.Data.IDalProvider db)
{
if (System.Web.HttpContext.Current.Cache["Depots"].IsNotNullOrEmpty())
return System.Web.HttpContext.Current.Cache["Depots"] as List<Z10Cabbage.Entity.Z10Depot>;
List<Z10Cabbage.Entity.Z10Depot> xdepots = db.Take<Z10Cabbage.Entity.Z10Depot>("TenantID=@TenantID order by DisplayOrder", db.CreateParameter("TenantID", tenantID));
System.Web.HttpContext.Current.Cache["Depots"] = xdepots;
return xdepots;
}
/// <summary>
/// 获取角色
/// </summary>
/// <param name="db"></param>
/// <param name="tenantID"></param>
/// <returns></returns>
public static List<Role> GetRoles(Zippy.Data.IDalProvider db, Guid tenantID)
{
//if (System.Web.HttpContext.Current.Cache["Roles_" + tenantID].IsNotNullOrEmpty())
// return System.Web.HttpContext.Current.Cache["Roles_" + tenantID] as List<Role>;
List<Role> xroles = db.Take<Role>("TenantID=@TenantID order by DisplayOrder", db.CreateParameter("TenantID", tenantID));
System.Web.HttpContext.Current.Cache["Roles_" + tenantID] = xroles;
return xroles;
}
/// <summary>
/// 合同条款下拉列表
/// </summary>
/// <param name="tenantID"></param>
/// <param name="db"></param>
/// <returns></returns>
public static string PaperTemplateSelectOptions(Guid tenantID, Zippy.Data.IDalProvider db)
{
StringBuilder sb = new StringBuilder();
List<Z01PaperTemplate> xobjs = db.Take<Z01PaperTemplate>("TenantID=@TenantID order by DisplayOrder", db.CreateParameter("TenantID", tenantID));
foreach (var xobj in xobjs)
{
sb.AppendLine("<option value='" + xobj.TemplateID + "'>" + xobj.Title + "</option>");
}
return sb.ToString();
}
/// <summary>
/// 表示 [父分类] 被选实体集合的 option html
/// </summary>
public static string GetTitleIDEntitiesHtmlOption(Zippy.Data.IDalProvider db, Guid tenantID, System.Int64? selectedValue)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
List<Z01Title> objs = db.Take<Z01Title>("TenantID=@TenantID", db.CreateParameter("TenantID", tenantID));
foreach (var obj in objs)
{
if (selectedValue == obj.TitleID)
sb.AppendLine("<option value='" + obj.TitleID + "' selected='selected'>" + obj.Title + "</option>");
else
sb.AppendLine("<option value='" + obj.TitleID + "'>" + obj.Title + "</option>");
}
return sb.ToString();
}
/// <summary>
/// 表示 [父分类] 被选实体集合的 option html
/// </summary>
public static string GetParentIDEntitiesHtmlOption(Zippy.Data.IDalProvider db, Guid tenantID, long? selectedValue, long removeID)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
List<Z01CustomerCategory> objs = db.Take<Z01CustomerCategory>("TenantID=@TenantID", db.CreateParameter("TenantID", tenantID));
List<Z01CustomerCategory> newObjs = new List<Z01CustomerCategory>();
ReGroup(objs, newObjs, 0, "├", removeID);
foreach (var obj in newObjs)
{
if (selectedValue == obj.CategoryID)
sb.AppendLine("<option value='" + obj.CategoryID + "' selected='selected'>" + obj.Title + "</option>");
else
sb.AppendLine("<option value='" + obj.CategoryID + "'>" + obj.Title + "</option>");
}
return sb.ToString();
}
/// <summary>
/// j将当前用户的url都显示出来
/// </summary>
/// <param name="user"></param>
/// <param name="tenantID"></param>
/// <param name="db"></param>
/// <returns></returns>
public static string ToJsonMenu(Zippy.SaaS.Entity.User user, Guid? tenantID, Zippy.Data.IDalProvider db)
{
System.Text.StringBuilder menus = new StringBuilder();
menus.AppendLine("{");
List<Permission> xobjs = GetPermissions(db, tenantID);
List<EAP.Logic.Bus.View.V_UserPermission> mePers = null;
if (!user.UserType.BitIs(Zippy.SaaS.UserTypes.SystemAdministrator))
{
mePers = db.Take<EAP.Logic.Bus.View.V_UserPermission>("UserID=@UserID", db.CreateParameter("UserID", user.UserID));
}
var rootRecords = xobjs.Where(s => (s.ParentID ?? 0) == 0 && s.PermissionStatus == 1).OrderBy(s => s.DisplayOrder);
foreach (Permission per in rootRecords)
{
if (mePers == null || (mePers != null && mePers.Exists(s => s.PermissionID == per.PermissionID)))
{
if (per.PermissionStatus == 1)
menus.AppendLine("'" + per.PermissionID + "' : [['" + per.PermissionID + "','" + per.Title + "','" + per.Icon + "'],{");
var subRecords = xobjs.Where(s => s.ParentID == per.PermissionID).OrderBy(s => s.DisplayOrder);
foreach (Permission subper in subRecords)
{
if (mePers == null || (mePers != null && mePers.Exists(s => s.PermissionID == subper.PermissionID)))
{
if (subper.PermissionStatus == 1)
menus.AppendLine("'" + subper.PermissionID + "' : ['" + subper.Title + "','" + subper.Url + "','" + subper.Icon + "'],");
}
}
//if (menus.ToString().EndsWith(",")) menus.Remove(menus.Length - 1, 1);
menus.AppendLine("}],");
}
}
//if (menus.ToString().EndsWith(",\n")) menus.Remove(menus.Length - 1, 1);
menus.AppendLine("}");
return menus.ToString().Replace(",\r\n}", "\r\n}");
}
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 List<Permission> Query(Zippy.Data.IDalProvider db, long parentID)
{
return db.Take<Permission>(" [ParentID] = @ParentID order by DisplayOrder", db.CreateParameter("ParentID", parentID));
}
/// <summary>
/// 表示 [父权限] 对应的实体
/// </summary>
public static Permission GetParentIDEntity(Zippy.Data.IDalProvider db, Permission entity)
{
return db.FindUnique<Permission>("PermissionID=@PermissionID", db.CreateParameter("PermissionID", entity.ParentID));
}
/// <summary>
/// 获取 [银行 的 银行] 的 [财务流水] 集合
/// </summary>
public static List<Z01FinancialFlow> GetBankID_Z01FinancialFlows(Zippy.Data.IDalProvider db, Z01Bank entity)
{
if (entity.BankID.HasValue)
return db.Take<Z01FinancialFlow>("BankID=@BankID", db.CreateParameter("BankID", entity.BankID));
return new List<Z01FinancialFlow>();
}
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;
}
/// <summary>
/// 获取 [行业 的 父行业] 的 [行业] 集合
/// </summary>
public static List<Industry> GetParentID_Industrys(Zippy.Data.IDalProvider db, Industry entity)
{
if (entity.IndustryID.HasValue)
return db.Take<Industry>("ParentID=@ParentID", db.CreateParameter("ParentID", entity.IndustryID));
return new List<Industry>();
}
/// <summary>
/// 表示 [父行业] 对应的实体
/// </summary>
public static Industry GetParentIDEntity(Zippy.Data.IDalProvider db, Industry entity)
{
return db.FindUnique<Industry>("IndustryID=@IndustryID", db.CreateParameter("IndustryID", entity.ParentID));
}
public static PaginatedList<Z01CustomerCategory> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int? orderCol)
{
PaginatedList<Z01CustomerCategory> rtn = new PaginatedList<Z01CustomerCategory>();
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 = "[Code]";
}
else if (orderCol == 4)
{
orderBy = "[Code] desc";
}
else if (orderCol == 5)
{
orderBy = "[ParentID]";
}
else if (orderCol == 6)
{
orderBy = "[ParentID] desc";
}
int RecordCount = db.Count<Z01CustomerCategory>(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<Z01CustomerCategory> records = db.Take<Z01CustomerCategory>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
//递归重组
List<Z01CustomerCategory> newRecords = new List<Z01CustomerCategory>();
ReGroup(records, newRecords, 0, "├", 0);
rtn.AddRange(newRecords);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return rtn;
}
public static Z10Cabbage.Entity.Z10OrderItem GetTop1OrderItem(this Z10Order order, Zippy.Data.IDalProvider db)
{
return db.FindUnique<Z10Cabbage.Entity.Z10OrderItem>("OrderID=@OrderID", db.CreateParameter("OrderID", order.OrderID));
}
/// <summary>
/// 移除角色
/// </summary>
/// <param name="userid"></param>
/// <param name="roleid"></param>
/// <param name="tenantid"></param>
/// <param name="creator"></param>
/// <param name="db"></param>
public static void RemoveRole(Guid userid, Guid roleid, Guid? tenantid, Zippy.Data.IDalProvider db)
{
db.Delete<UserRole>("UserID=@UserID and RoleID=@RoleID and TenantID=@TenantID",
db.CreateParameter("UserID", userid),
db.CreateParameter("RoleID", roleid),
db.CreateParameter("TenantID", tenantid));
}
/// <summary>
/// 获取 [计量单位 的 标准计量单位] 的 [产品] 集合
/// </summary>
public static List<Z01Product> GetUnitID_Z01Products(Zippy.Data.IDalProvider db, Z01Unit entity)
{
if (entity.UnitID.HasValue)
return db.Take<Z01Product>("UnitID=@UnitID", db.CreateParameter("UnitID", entity.UnitID));
return new List<Z01Product>();
}
/// <summary>
/// 设置角色
/// </summary>
/// <param name="userid"></param>
/// <param name="roleid"></param>
/// <param name="db"></param>
public static void SetRole(Guid userid, Guid roleid, Guid? tenantid, Guid? creator, Zippy.Data.IDalProvider db)
{
if (!db.Exists<UserRole>("UserID=@UserID and RoleID=@RoleID", db.CreateParameter("UserID", userid), db.CreateParameter("RoleID", roleid)))
{
UserRole ur = new UserRole();
ur.RoleID = roleid;
ur.UserID = userid;
ur.TenantID = tenantid;
ur.Creator = creator;
db.Insert(ur);
}
}
public static PaginatedList<Z01Bank> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int? orderCol)
{
PaginatedList<Z01Bank> rtn = new PaginatedList<Z01Bank>();
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 qAccount = paras["qAccount"];
if (qAccount.IsNotNullOrEmpty())
{
where += " and [Account] like @Account";
dbParams.Add(db.CreateParameter("Account", "%" + qAccount + "%"));
}
}
#endregion
string orderBy = "[DisplayOrder]";
if (orderCol == 0)
{
orderBy = "[DisplayOrder] desc";
}
else if (orderCol == 1)
{
orderBy = "[Title]";
}
else if (orderCol == 2)
{
orderBy = "[Title] desc";
}
else if (orderCol == 3)
{
orderBy = "[Brief]";
}
else if (orderCol == 4)
{
orderBy = "[Brief] desc";
}
else if (orderCol == 5)
{
orderBy = "[Account]";
}
else if (orderCol == 6)
{
orderBy = "[Account] desc";
}
else if (orderCol == 7)
{
orderBy = "[Contact]";
}
else if (orderCol == 8)
{
orderBy = "[Contact] desc";
}
else if (orderCol == 9)
{
orderBy = "[Tel]";
}
else if (orderCol == 10)
{
orderBy = "[Tel] desc";
}
else if (orderCol == 11)
{
orderBy = "[Fax]";
}
else if (orderCol == 12)
{
orderBy = "[Fax] desc";
}
else if (orderCol == 13)
{
orderBy = "[CreateDate]";
}
else if (orderCol == 14)
{
orderBy = "[CreateDate] desc";
}
int RecordCount = db.Count<Z01Bank>(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<Z01Bank> records = db.Take<Z01Bank>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return rtn;
}
/// <summary>
/// 表示 [分组] 对应的实体
/// </summary>
public static Group GetGroupIDEntity(Zippy.Data.IDalProvider db, User entity)
{
return db.FindUnique<Group>("GroupID=@GroupID", db.CreateParameter("GroupID", entity.GroupID));
}
/// <summary>
/// 获取 [权限表 的 父权限] 的 [权限表] 集合
/// </summary>
public static List<Permission> GetParentID_Permissions(Zippy.Data.IDalProvider db, Permission entity)
{
if (entity.PermissionID.HasValue)
return db.Take<Permission>("ParentID=@ParentID", db.CreateParameter("ParentID", entity.PermissionID));
return new List<Permission>();
}
/// <summary>
/// 获取 [用户 的 用户] 的 [用户角色] 集合
/// </summary>
public static List<V_UserRole> GetUserID_UserRoles(Zippy.Data.IDalProvider db, User entity)
{
if (entity.UserID.HasValue)
return db.Take<V_UserRole>("UserID=@UserID", db.CreateParameter("UserID", entity.UserID));
return new List<V_UserRole>();
}
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<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;
}
/// <summary>
/// 表示 [头衔/职务] 对应的实体
/// </summary>
public static Z01Title GetTitleIDEntity(Zippy.Data.IDalProvider db, Z01UserInfo entity)
{
return db.FindUnique<Z01Title>("TitleID=@TitleID", db.CreateParameter("TitleID", entity.TitleID));
}
/// <summary>
/// 表示 [租户编号] 对应的实体
/// </summary>
public static Tenant GetTenantIDEntity(Zippy.Data.IDalProvider db, TenantIdentity entity)
{
return db.FindUnique<Tenant>("TenantID=@TenantID", db.CreateParameter("TenantID", entity.TenantID));
}
public static PaginatedList<DataSource> Query(Zippy.Data.IDalProvider db, Guid tenantID, int PageSize, int PageIndex, Hashtable paras, int? orderCol)
{
PaginatedList<DataSource> rtn = new PaginatedList<DataSource>();
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 qDataSourceType = paras["qDataSourceType"];
if (qDataSourceType != null && qDataSourceType.ToString()!="")
{
Int32 intqDataSourceType = (Int32)qDataSourceType;
if (intqDataSourceType > 0)
{
where += " and ([DataSourceType] & @DataSourceType = @DataSourceType)";
dbParams.Add(db.CreateParameter("DataSourceType", qDataSourceType));
}
}
object qTitle = paras["qTitle"];
if (qTitle != null)
{
where += " and [Title] like @Title";
dbParams.Add(db.CreateParameter("Title", "%" + qTitle + "%"));
}
object qDataSourceStatus = paras["qDataSourceStatus"];
if (qDataSourceStatus != null && qDataSourceStatus.ToString()!="" )
{
Int32 intqDataSourceStatus = (Int32)qDataSourceStatus;
if (intqDataSourceStatus > 0)
{
where += " and [DataSourceStatus] = @DataSourceStatus";
dbParams.Add(db.CreateParameter("DataSourceStatus", qDataSourceStatus));
}
}
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 = "[DataSourceID]";
}
else if (orderCol == 2)
{
orderBy = "[DataSourceID] desc";
}
else if (orderCol == 3)
{
orderBy = "[DataSourceType]";
}
else if (orderCol == 4)
{
orderBy = "[DataSourceType] desc";
}
else if (orderCol == 5)
{
orderBy = "[Title]";
}
else if (orderCol == 6)
{
orderBy = "[Title] desc";
}
else if (orderCol == 7)
{
orderBy = "[DataSourceStatus]";
}
else if (orderCol == 8)
{
orderBy = "[DataSourceStatus] desc";
}
else if (orderCol == 9)
{
orderBy = "[CreateDate]";
}
else if (orderCol == 10)
{
orderBy = "[CreateDate] desc";
}
int RecordCount = db.Count<DataSource>(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<DataSource> records = db.Take<DataSource>(where + " order by " + orderBy, PageSize, PageIndex, dbParams.ToArray());
rtn.AddRange(records);
rtn.PageIndex = PageIndex;
rtn.PageSize = PageSize;
rtn.TotalCount = RecordCount;
return rtn;
}
public static EAP.Bus.Entity.Currency GetCurrency(this Z10Order order, Zippy.Data.IDalProvider db)
{
return db.FindUnique<EAP.Bus.Entity.Currency>("ID=@ID", db.CreateParameter("ID",order.Currency));
}