/// <summary>
/// 获取管理员列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <RoleViewModel> GetPageList(int pageIndex, int pageSize)
{
JsonRsp <RoleViewModel> rsp = new JsonRsp <RoleViewModel>();
AdminRole m = new AdminRole();
OQL q = OQL.From(m)
.Select(m.ID, m.RoleName, m.Status, m.CreateTIme)
.OrderBy(m.ID, "asc")
.END;
//分页
q.Limit(pageSize, pageIndex, true);
//q.PageWithAllRecordCount = allCount;
//List<Employee> list= EntityQuery<Employee>.QueryList(q);
List <AdminRole> list = q.ToList <AdminRole>();//使用OQL扩展
rsp.data = list.ConvertAll <RoleViewModel>(o =>
{
return(new RoleViewModel()
{
ID = o.ID,
RoleName = o.RoleName,
Status = o.Status,
CreateTIme = o.CreateTIme.ToString("yyyy-MM-dd HH:mm:ss"),
});
}
);
rsp.success = true;
rsp.code = 0;
rsp.count = q.PageWithAllRecordCount;
return(rsp);
}
/// <summary>
/// 获取列表数据
/// </summary>
/// <param name="cp">where条件，没有可传人null</param>
/// <param name="orders">排序字段对象</param>
/// <param name="pageSize">每页显示记录数</param>
/// <param name="pageIndex">当前页</param>
/// <returns></returns>
public static List <T> GetList <T>(OQLCompare cp, OQLOrder orders, int pageSize, int pageIndex) where T : EntityBase, ILongID, new()
{
T m = new T();
OQL q = new OQL(m);
if ((object)cp != null)
{
if ((object)orders != null)
{
q.Select().Where(cp).OrderBy(orders);
}
else
{
q.Select().Where(cp);
}
}
else
{
if ((object)orders != null)
{
q.Select().OrderBy(orders);
}
else
{
q.Select();
}
}
q.PageWithAllRecordCount = GetRecordCounts <T>(cp);
if (pageSize > 0 && pageIndex > 0 && q.PageWithAllRecordCount > 0)
{
q.Limit(pageSize, pageIndex);
}
return(EntityQuery <T> .QueryList(q));
}
/// <summary>
/// 获取管理员列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <AdminActionViewModel> GetPageList(int pageIndex, int pageSize)
{
JsonRsp <AdminActionViewModel> rsp = new JsonRsp <AdminActionViewModel>();
AdminAction action = new AdminAction();
AdminMenu menu = new AdminMenu();
//Select 方法不指定具体要选择的实体类属性，可以推迟到EntityContainer类的MapToList 方法上指定
OQL joinQ = OQL.From(action)
.Join(menu).On(action.MenuId, menu.ID)
.Select(action.ID, menu.MenuName, action.ActionKey, action.ActionName, action.Status, action.CreateTime)
.OrderBy(action.Sort, "desc")
.END;
joinQ.Limit(pageSize, pageIndex, true);
PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
EntityContainer ec = new EntityContainer(joinQ, db);
rsp.data = (List <AdminActionViewModel>)ec.MapToList <AdminActionViewModel>(() => new AdminActionViewModel()
{
ID = action.ID,
MenuId = menu.ID,
MenuName = menu.MenuName,
ActionKey = action.ActionKey,
ActionName = action.ActionName,
Status = action.Status,
CreateTime = action.CreateTime,
});
rsp.success = true;
rsp.code = 0;
rsp.count = joinQ.PageWithAllRecordCount;
return(rsp);
}
void TestLimit()
{
Users user = new Users()
{
NickName = "pdf.net"
};
OQL q0 = OQL.From(user)
.Select()
.Where(user.NickName)
.OrderBy(user.ID, "asc")
.END;
q0.Distinct = true;
q0.Limit(10, 2);
try
{
var list = EntityQuery <Users> .QueryList(q0);
}
catch (Exception ex)
{
Console.WriteLine("查询错误：{0}", ex.Message);
}
Console.WriteLine("one table and select page number 2,page size 10: \r\n{0}", q0);
Console.WriteLine("因为OQL是抽象的SQL，而分页语法又是特定于数据库的，所以具体的分页SQL要到查询真正执行的时候才会生成。");
Console.WriteLine(q0.PrintParameterInfo());
}
static void TestDataSetAndOQL(object para)
{
string sql = "select top 10 * from Table_User";
Table_User user = new Table_User();
user.MapNewTableName("Table_User");
//user.MapNewTableName("Table_User");
OQL qt = new OQL(user);
qt.Select().Where(qt.Condition.IN(user.Name, new object[] { "a", "b", "c" })).OrderBy(user.Name);
//qt.Select().Where(cmp => cmp.Comparer(user.Name, "in", new string[] { "a", "b", "c" }));
//OQL qt = OQL.From(user).Select().END;
//qt.TopCount = 10;
qt.Limit(10, 10);
qt.PageWithAllRecordCount = 0;
AdoHelper db = MyDB.GetDBHelper();
//测试下面2种方式对连接数量的影响，经测试，发现 MyDB.Instance 跟 MyDB.GetDBHelper() 没有区别。
//List<Table_User> list = EntityQuery<Table_User>.QueryList(qt);
//DataSet ds = MyDB.Instance.ExecuteDataSet(sql);
//测试连接会话，下面db的连接会在using 结束后关闭
using (db.OpenSession())
{
List <Table_User> list = EntityQuery <Table_User> .QueryList(qt, db);
DataSet ds = db.ExecuteDataSet(sql);
}
}
void TestOqlPage()
{
UserEntity ue = new UserEntity();
OQL q = OQL.From(ue)
.Select(ue.ID, ue.Name, ue.Age)
.Where(cmp => cmp.Comparer(ue.Age, ">", 20))
.OrderBy(ue.Age)
.END;
q.Limit(2, 3, true);
Console.WriteLine("q:Page SQL is \r\n{0}", q);
Console.WriteLine(q.PrintParameterInfo());
//当前测试总记录数5，查询后，OQL会得到总记录数
AdoHelper db = MyDB.GetDBHelperByConnectionName("conn2");
var list = EntityQuery <UserEntity> .QueryList(q, db);
Users user = new Users()
{
NickName = "pdf.net", RoleID = RoleNames.Admin, Age = 20
};
UserRoles roles = new UserRoles()
{
RoleName = "role1"
};
//测试字段直接比较
OQL q00 = OQL.From(user)
.Select(user.ID, user.NickName, user.LastLoginIP)
.Where(cmp => cmp.Comparer(user.AddTime, "=", user.LastLoginTime))
.OrderBy(o => o.Desc(user.LastLoginTime))
.END;
Console.WriteLine("q00:one table and select all fields \r\n{0}", q00);
Console.WriteLine(q00.PrintParameterInfo());
string pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q00.ToString(), "", 10, 2, 999);
Console.WriteLine("Page SQL");
Console.WriteLine(pageSql);
OQL q2 = OQL.From(user)
.InnerJoin(roles).On(user.RoleID, roles.ID)
.Select(user.RoleID, roles.RoleName)
.Where(user.NickName, roles.RoleName)
.GroupBy(user.RoleID, roles.RoleName)
.OrderBy(user.ID)
.END;
Console.WriteLine("q2:two table query use join\r\n{0}", q2);
Console.WriteLine(q2.PrintParameterInfo());
pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q2.ToString(), "", 10, 2, 999);
Console.WriteLine("Page SQL");
Console.WriteLine(pageSql);
}
/// <summary>
/// 获取指定页的联系人信息
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageNumber"></param>
/// <param name="allCount"></param>
/// <returns></returns>
public List <CustomerContactInfo> GetContactInfoList(int pageSize, int pageNumber, int allCount)
{
CustomerContactInfo info = new CustomerContactInfo();
OQL q = new OQL(info);
q.Select().OrderBy(info.CustomerName, "asc");
q.Limit(pageSize, pageNumber);
q.PageWithAllRecordCount = allCount;
return(EntityQuery <CustomerContactInfo> .QueryList(q));
}
/// <summary>
/// 获取列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <ChargeCardViewModel> GetPageList(int pageIndex, int pageSize, bool limit = true)
{
JsonRsp <ChargeCardViewModel> rsp = new JsonRsp <ChargeCardViewModel>();
ChargeCardsModel c = new ChargeCardsModel();
CustomModel custom = new CustomModel();
TicketTypeModel t = new TicketTypeModel();
OQL joinQ = OQL.From(c)
.Join(custom).On(c.CustomId, custom.ID)
.LeftJoin(t).On(c.TicketTypeID, t.ID)
.Select()
.OrderBy(c.Sort, "desc")
.END;
//分页
if (limit)
{
joinQ.Limit(pageSize, pageIndex, true);
}
PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
EntityContainer ec = new EntityContainer(joinQ, db);
rsp.data = (List <ChargeCardViewModel>)ec.MapToList <ChargeCardViewModel>(() => new ChargeCardViewModel()
{
ID = c.ID,
CustomId = c.CustomId,
CustomName = custom.CustomName,
TicketTypeID = c.TicketTypeID,
TicketTypeName = t.TicketTypeName,
MoneyType = c.MoneyType,
CurrentCount = c.CurrentCount,
FaceAmount = c.FaceAmount,
CurrentAmount = c.CurrentAmount,
ExpireDate = c.ExpireDate,
TicketBatchId = c.TicketBatchId,
TicketStart = c.TicketStart,
TicketEnd = c.TicketEnd,
Consumptionlevel = c.Consumptionlevel,
IsCommonCard = c.IsCommonCard,
Sort = c.Sort,
CreateId = c.CreateId,
CreateUser = c.CreateUser,
CreateIP = c.CreateIP,
CreateTime = c.CreateTime,
UpdateBy = c.UpdateUser,
UpdateIP = c.UpdateIP,
UpdateTime = c.UpdateTime,
});
rsp.success = true;
rsp.code = 0;
rsp.count = joinQ.PageWithAllRecordCount;
return(rsp);
}
public List <GoodsBaseInfoVM> GetGoodsBaseInfoList(int pageSize, int pageNumber, int allCount)
{
GoodsBaseInfo info = new GoodsBaseInfo();
OQL q = new OQL(info);
q.Select()
.OrderBy(info.SerialNumber, "asc");
q.Limit(pageSize, pageNumber);
q.PageWithAllRecordCount = allCount;
return(GetGoodsBaseInfoList(q));
}
/// <summary>
/// 模糊查询用户，返回用户列表，使用OQLCompare 委托
/// </summary>
/// <param name="likeName">要匹配的用户名</param>
/// <returns>用户列表</returns>
public List<User> FuzzyQueryUser(string likeName)
{
User user = new User();
OQL q = OQL.From(user)
.Select()
.Where(cmp => cmp.Comparer(user.Name, "like", likeName+"%") )
.OrderBy (user.ID )
.END;
q.Limit(10,5);
List<User> users = EntityQuery<User>.QueryList(q);
return users;
}
/// <summary>
/// 获取管理员列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <TicketViewModel> GetPageList(int pageIndex, int pageSize)
{
JsonRsp <TicketViewModel> rsp = new JsonRsp <TicketViewModel>();
TicketInfo ticket = new TicketInfo();
TicketTypeModel ticketType = new TicketTypeModel();
//Select 方法不指定具体要选择的实体类属性，可以推迟到EntityContainer类的MapToList 方法上指定
OQL joinQ = OQL.From(ticket)
.Join(ticketType).On(ticket.TicketTypeId, ticketType.ID)
.Select()
.OrderBy(ticket.Sort, "desc")
.END;
joinQ.Limit(pageSize, pageIndex, true);
PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
EntityContainer ec = new EntityContainer(joinQ, db);
rsp.data = (List <TicketViewModel>)ec.MapToList <TicketViewModel>(() => new TicketViewModel()
{
ID = ticket.ID,
TicketCode = ticket.TicketCode,
TicketTypeId = ticket.TicketTypeId,
Consumptionlevel = BaseEnum.ConsumptionlevelEnum.初始化,
MoneyTyp = BaseEnum.MoneyTypeEnum.初始化,
CustomID = 0,
InitialAmount = 0,
CostAmount = 0,
Balance = 0,
Status = BaseEnum.CredentialEnum.未交付,
IsExpire = false,
IsActivated = false,
//MakeTime=null,
//ExpireDate=null,
TicketBatchNo = ticket.TicketBatchNo,
GrantBy = 0,
//GrantTime=null,
CreateBy = AdminName,
CreateIP = Util.GetLocalIP(),
CreateTime = ticket.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
Sort = ticket.Sort,
});
rsp.success = true;
rsp.code = 0;
rsp.count = joinQ.PageWithAllRecordCount;
return(rsp);
}
/// <summary>
/// 获取管理员列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <MerchantViewModel> GetPageList(int pageIndex, int pageSize)
{
JsonRsp <MerchantViewModel> rsp = new JsonRsp <MerchantViewModel>();
MerchantModel model = new MerchantModel();
MerchantTypeModel MerchantType = new MerchantTypeModel();
model.TenantId = TenantId;
//Select 方法不指定具体要选择的实体类属性，可以推迟到EntityContainer类的MapToList 方法上指定
OQL joinQ = OQL.From(model)
.Join(MerchantType).On(model.MerchantTypeId, MerchantType.ID)
.Select()
.OrderBy(model.Sort, "desc")
.END;
joinQ.Limit(pageSize, pageIndex, true);
PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
EntityContainer ec = new EntityContainer(joinQ, db);
rsp.data = (List <MerchantViewModel>)ec.MapToList <MerchantViewModel>(() => new MerchantViewModel()
{
ID = model.ID,
MerchantTypeId = model.MerchantTypeId,
MerchantTypeName = MerchantType.MerchantTypeName,
MerchantName = model.MerchantName,
LinkPhone = model.LinkPhone,
LinkName = model.LinkName,
LinkMobile = model.LinkMobile,
MerchantArea = model.MerchantArea,
MerchantAddress = model.MerchantAddress,
CreateId = model.CreateId,
CreateUser = model.CreateUser,
CreateIP = model.CreateIP,
CreateTime = model.CreateTime,
Sort = model.Sort,
Status = model.Status,
UpdateBy = model.UpdateUser,
UpdateIP = model.UpdateIP,
UpdateTime = model.UpdateTime,
});
rsp.success = true;
rsp.code = 0;
rsp.count = joinQ.PageWithAllRecordCount;
return(rsp);
}
/// <summary>
/// 获取管理员列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <ContractViewModel> GetPageList(int pageIndex, int pageSize)
{
JsonRsp <ContractViewModel> rsp = new JsonRsp <ContractViewModel>();
ContractModel contract = new ContractModel();
CustomModel custom = new CustomModel();
CustomTypeModel customType = new CustomTypeModel();
//Select 方法不指定具体要选择的实体类属性，可以推迟到EntityContainer类的MapToList 方法上指定
OQL joinQ = OQL.From(contract)
.Join(custom).On(contract.CustomId, custom.ID)
.Join(customType).On(custom.CustomTypeId, customType.ID)
.Select()
.OrderBy(contract.Sort, "desc")
.END;
joinQ.Limit(pageSize, pageIndex, true);
PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
EntityContainer ec = new EntityContainer(joinQ, db);
rsp.data = (List <ContractViewModel>)ec.MapToList <ContractViewModel>(() => new ContractViewModel()
{
ID = contract.ID,
CustomId = contract.CustomId,
CustomName = custom.CustomName,
CustomTypeName = customType.CustomTypeName,
ContractNo = contract.ContractNo,
ContractAmount = contract.ContractAmount,
Deductions = contract.Deductions,
Balance = contract.Balance,
Remark = contract.Remark,
Sort = contract.Sort,
Status = contract.Status,
CreateId = contract.CreateId,
CreateUser = contract.CreateUser,
CreateIP = contract.CreateIP,
CreateTime = contract.CreateTime,
});
rsp.success = true;
rsp.code = 0;
rsp.count = joinQ.PageWithAllRecordCount;
return(rsp);
}
void TestMapOql()
{
Users user = new Users()
{
NickName = "pdf.net", RoleID = RoleNames.Admin
};
//插入一个数据便于测试
EntityQuery <Users> .Instance.Insert(user);
OQL q = OQL.From(user).Select().Where(user.RoleID).END;
EntityContainer ec = new EntityContainer(q);
//第一种映射方式，适用于OQL单实体类查询：
var list = ec.MapToList(user, u => new
{
P1 = u.ID,
P2 = u.NickName
}
);
UserRoles role = new UserRoles();
OQL q2 = OQL.From(user)
.Join(role).On(user.RoleID, role.ID)
.Select()
.Where(cmp => cmp.Comparer(role.ID, "=", RoleNames.User))
.OrderBy(role.RoleName)
.END;
q2.Limit(10, 2);
q2.PageWithAllRecordCount = 0;
EntityContainer ec2 = new EntityContainer(q2);
//第二种映射方式，推荐用于OQL多个实体类关联查询的情况：
var list2 = ec2.MapToList(() => new {
P1 = user.ID,
P2 = user.NickName,
P3 = role.ID,
P4 = role.Description
});
Console.WriteLine("OQL TestMapOql Query:\r\n{0}\r\n", q2);
Console.WriteLine(q2.PrintParameterInfo());
}
/// <summary>
/// 获取管理员列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <ProductTypeViewModel> GetPageList(string searchTxt, int pageIndex, int pageSize)
{
JsonRsp <ProductTypeViewModel> rsp = new JsonRsp <ProductTypeViewModel>();
ProductTypeModel pt = new ProductTypeModel();
MerchantTypeModel mt = new MerchantTypeModel();
OQLCompareFunc <ProductTypeModel> cmpFun = (cmp, u) =>
{
OQLCompare cmpResult = null;
//and 条件
cmpResult = cmpResult & cmp.Comparer(pt.ProductTypeName, OQLCompare.CompareType.Like, "%" + searchTxt + "%");
//or
cmpResult = cmpResult | cmp.Comparer(pt.TenantId, OQLCompare.CompareType.Equal, TenantId);
return(cmpResult);
};
//Select 方法不指定具体要选择的实体类属性，可以推迟到EntityContainer类的MapToList 方法上指定
OQL joinQ = OQL.From(pt)
.Join(mt).On(pt.MerchantTypeId, mt.ID)
.Select(pt.ID, pt.ProductTypeName, mt.MerchantTypeName, pt.Status, pt.CreateTime)
.Where(cmpFun)
.OrderBy(pt.Sort, "desc")
.END;
joinQ.Limit(pageSize, pageIndex, true);
PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
EntityContainer ec = new EntityContainer(joinQ, db);
rsp.data = (List <ProductTypeViewModel>)ec.MapToList <ProductTypeViewModel>(() => new ProductTypeViewModel()
{
ID = pt.ID,
ProductTypeName = pt.ProductTypeName,
MerchantTypeId = mt.ID,
MerchantTypeName = mt.MerchantTypeName,
CreateId = pt.CreateId,
CreateUser = pt.CreateUser,
CreateIP = pt.CreateIP,
CreateTime = pt.CreateTime,
Status = pt.Status,
});
rsp.success = true;
rsp.code = 0;
rsp.count = joinQ.PageWithAllRecordCount;
return(rsp);
}
void TestLimit()
{
Users user = new Users()
{
NickName = "pdf.net"
};
OQL q0 = OQL.From(user)
.Select()
.Where(user.NickName)
.OrderBy(user.ID)
.END;
q0.Limit(10, 2);
Console.WriteLine("one table and select page number 2,page size 10: \r\n{0}", q0);
Console.WriteLine("因为OQL是抽象的SQL，而分页语法又是特定于数据库的，所以具体的分页SQL要到查询真正执行的时候才会生成。");
Console.WriteLine(q0.PrintParameterInfo());
}
/// <summary>
/// 获取管理员列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <CinemaViewModel> GetPageList(int pageIndex, int pageSize)
{
JsonRsp <CinemaViewModel> rsp = new JsonRsp <CinemaViewModel>();
CinemaModel m = new CinemaModel();
OQL q = OQL.From(m)
.Select()
.OrderBy(m.ID, "asc")
.END;
//分页
q.Limit(pageSize, pageIndex, true);
//q.PageWithAllRecordCount = allCount;
//List<Employee> list= EntityQuery<Employee>.QueryList(q);
List <CinemaModel> list = q.ToList <CinemaModel>();//使用OQL扩展
rsp.data = list.ConvertAll <CinemaViewModel>(o =>
{
return(new CinemaViewModel()
{
ID = o.ID,
CinemaChainId = o.CinemaChainId,
CinemaName = o.CinemaName,
LinkPhone = o.LinkPhone,
LinkName = o.LinkName,
LinkMobile = o.LinkMobile,
CinemaArea = o.CinemaArea,
CinemaAddress = o.CinemaAddress,
CreateBy = o.CreateUser,
CreateIP = o.CreateIP,
CreateTime = o.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
Sort = o.Sort,
Status = o.Status,
UpdateBy = o.UpdateUser,
UpdateIP = o.UpdateIP,
UpdateTime = o.UpdateTime == null ? "" : Convert.ToDateTime(o.UpdateTime).ToString("yyyy-MM-dd HH:mm:ss"),
});
}
);
rsp.success = true;
rsp.code = 0;
rsp.count = q.PageWithAllRecordCount;
return(rsp);
}
/// <summary>
/// 获取管理员列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <TicketBatchViewModel> GetPageList(int pageIndex, int pageSize)
{
JsonRsp <TicketBatchViewModel> rsp = new JsonRsp <TicketBatchViewModel>();
TicketBatchModel ticket = new TicketBatchModel();
TicketTypeModel ticketType = new TicketTypeModel();
//Select 方法不指定具体要选择的实体类属性，可以推迟到EntityContainer类的MapToList 方法上指定
OQL joinQ = OQL.From(ticket)
.Join(ticketType).On(ticket.TicketTypeId, ticketType.ID)
.Select()
.OrderBy(ticket.ID, "desc")
.END;
joinQ.Limit(pageSize, pageIndex, true);
PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
EntityContainer ec = new EntityContainer(joinQ, db);
rsp.data = (List <TicketBatchViewModel>)ec.MapToList <TicketBatchViewModel>(() => new TicketBatchViewModel()
{
ID = ticket.ID,
TicketTypeId = ticket.TicketTypeId,
TicketTypeIdStr = ticketType.TicketTypeName,
TicketBatchNo = ticket.TicketBatchNo,
TicketPrefix = ticket.TicketPrefix,
DataFrom = ticket.DataFrom,
Count = ticket.Count,
TicketBatchName = ticket.TicketBatchName,
CreateId = ticket.CreateId,
CreateUser = ticket.CreateUser,
CreateIP = ticket.CreateIP,
CreateTime = ticket.CreateTime,
Status = ticket.Status,
});
rsp.success = true;
rsp.code = 0;
rsp.count = joinQ.PageWithAllRecordCount;
return(rsp);
}
public static void 分页查询()
{
SODUser user = new SODUser();
OQL q = new OQL(user);
OQLCompareFunc resultFunc = cmp =>
{
OQLCompare resultCmp = cmp.Comparer(user.ID, ">", 0);
resultCmp = resultCmp & cmp.Comparer(user.LogName, "like", "%zhang%");
return(resultCmp);
};
q.Select().Where(resultFunc).OrderBy(user.ID);
//分页
q.PageEnable = true;
q.PageWithAllRecordCount = 10;
q.Limit(10, 1, true);
var outlist = EntityQuery <SODUser> .QueryList(q);
int pageCount = q.PageWithAllRecordCount;
}
/// <summary>
/// 获取管理员列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <BankAccountViewModel> GetPageList(int pageIndex, int pageSize, bool limit = true)
{
JsonRsp <BankAccountViewModel> rsp = new JsonRsp <BankAccountViewModel>();
BankAccountModel ba = new BankAccountModel();
BankModel b = new BankModel();
OQL joinQ = OQL.From(ba)
.Join(b).On(ba.BankId, b.ID)
.Select()
.OrderBy(ba.Sort, "desc")
.END;
//分页
if (limit)
{
joinQ.Limit(pageSize, pageIndex, true);
}
PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
EntityContainer ec = new EntityContainer(joinQ, db);
rsp.data = (List <BankAccountViewModel>)ec.MapToList <BankAccountViewModel>(() => new BankAccountViewModel()
{
ID = ba.ID,
BankAccountName = ba.BankAccountName,
BankName = b.BankName,
BankId = ba.BankId,
BankAccountCode = ba.BankAccountCode,
CreateId = ba.CreateId,
CreateUser = ba.CreateUser,
CreateIP = ba.CreateIP,
CreateTime = ba.CreateTime,
Sort = ba.Sort,
Status = ba.Status,
});
rsp.success = true;
rsp.code = 0;
rsp.count = joinQ.PageWithAllRecordCount;
return(rsp);
}
/// <summary>
/// 获取列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <CustomTypeViewModel> GetPageList(int pageIndex, int pageSize)
{
JsonRsp <CustomTypeViewModel> rsp = new JsonRsp <CustomTypeViewModel>();
CustomTypeModel m = new CustomTypeModel();
OQL q = OQL.From(m)
.Select()
.OrderBy(m.ID, "asc")
.END;
//分页
q.Limit(pageSize, pageIndex, true);
//q.PageWithAllRecordCount = allCount;
//List<Employee> list= EntityQuery<Employee>.QueryList(q);
List <CustomTypeModel> list = q.ToList <CustomTypeModel>();//使用OQL扩展
rsp.data = list.ConvertAll <CustomTypeViewModel>(o =>
{
return(new CustomTypeViewModel()
{
ID = o.ID,
CustomTypeName = o.CustomTypeName,
CreateId = o.CreateId,
CreateUser = o.CreateUser,
CreateIP = o.CreateIP,
CreateTime = o.CreateTime,
Sort = o.Sort,
Status = o.Status,
UpdateBy = o.UpdateUser,
UpdateIP = o.UpdateIP,
UpdateTime = o.UpdateTime,
});
}
);
rsp.success = true;
rsp.code = 0;
rsp.count = q.PageWithAllRecordCount;
return(rsp);
}
private void btnLoadData_Click(object sender, EventArgs e)
{
//List<User> list = OQL.From<User>().Select().END.ToList<User>();
//上面一行是简写的方式，下面是传统的方式，带分页，当前选定第一页
User user = new User();
OQL q = OQL.From(user)
.Select(user.UserID, user.UserName, user.UserType, user.RegisterDate, user.Expenditure)
.OrderBy(user.UserName, "asc")
.END;
q.Distinct = true;
q.Limit(10);
List <User> list = EntityQuery <User> .QueryList(q);
foreach (var item in list)
{
UserBindingList.Add(item);
}
this.dataGridView1.DataSource = UserBindingList;
//this.dataGridView1.DataSource = list;
}
/// <summary>
/// 获取管理员列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <CustomReceiptedViewModel> GetPageList(int pageIndex, int pageSize)
{
JsonRsp <CustomReceiptedViewModel> rsp = new JsonRsp <CustomReceiptedViewModel>();
CustomReceiptedModel m = new CustomReceiptedModel();
OQL q = OQL.From(m)
.Select()
.OrderBy(m.ID, "asc")
.END;
//分页
q.Limit(pageSize, pageIndex, true);
//q.PageWithAllRecordCount = allCount;
//List<Employee> list= EntityQuery<Employee>.QueryList(q);
List <CustomReceiptedModel> list = q.ToList <CustomReceiptedModel>();//使用OQL扩展
rsp.data = list.ConvertAll <CustomReceiptedViewModel>(o =>
{
return(new CustomReceiptedViewModel()
{
ID = o.ID,
CustomAccReceiptId = o.CustomAccReceiptId,
BankAccountId = o.BankAccountId,
CurrentAmount = o.CurrentAmount,
BankSerialNumber = o.BankSerialNumber,
DateOfEntry = o.DateOfEntry,
CreateId = o.CreateId,
CreateUser = o.CreateUser,
CreateIP = o.CreateIP,
CreateTime = o.CreateTime,
Status = o.Status,
});
}
);
rsp.success = true;
rsp.code = 0;
rsp.count = q.PageWithAllRecordCount;
return(rsp);
}
/// <summary>
/// 获取管理员列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <TicketTypeViewModel> GetPageList(int pageIndex, int pageSize)
{
JsonRsp <TicketTypeViewModel> rsp = new JsonRsp <TicketTypeViewModel>();
TicketTypeModel m = new TicketTypeModel();
OQL q = OQL.From(m)
.Select()
.OrderBy(m.Sort, "desc")
.END;
//分页
q.Limit(pageSize, pageIndex, true);
//q.PageWithAllRecordCount = allCount;
//List<Employee> list= EntityQuery<Employee>.QueryList(q);
List <TicketTypeModel> list = q.ToList <TicketTypeModel>();//使用OQL扩展
rsp.data = list.ConvertAll <TicketTypeViewModel>(o =>
{
return(new TicketTypeViewModel()
{
ID = o.ID,
TicketTypeName = o.TicketTypeName,
CreateBy = o.CreateUser,
CreateIP = o.CreateIP,
CreateTime = o.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
Sort = o.Sort,
Status = o.Status,
UpdateBy = o.UpdateUser,
UpdateIP = o.UpdateIP,
UpdateTime = o.UpdateTime == null ? "" : Convert.ToDateTime(o.UpdateTime).ToString("yyyy-MM-dd HH:mm:ss"),
});
}
);
rsp.success = true;
rsp.code = 0;
rsp.count = q.PageWithAllRecordCount;
return(rsp);
}
/// <summary>
/// 获取管理员列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <MenuViewModel> GetPageList(int pageIndex, int pageSize)
{
JsonRsp <MenuViewModel> rsp = new JsonRsp <MenuViewModel>();
AdminMenu m = new AdminMenu();
OQL q = OQL.From(m)
.Select()
.OrderBy(m.ID, "asc")
.END;
//分页
q.Limit(pageSize, pageIndex, true);
//q.PageWithAllRecordCount = allCount;
//List<Employee> list= EntityQuery<Employee>.QueryList(q);
List <AdminMenu> list = q.ToList <AdminMenu>();//使用OQL扩展
rsp.data = list.ConvertAll <MenuViewModel>(o =>
{
return(new MenuViewModel()
{
ID = o.ID,
MenuKey = o.MenuKey,
MenuName = o.MenuName,
MenuUrl = o.MenuUrl,
MenuType = o.MenuType,
IDPath = o.IDPath,
Remark = o.Remark,
Sort = o.Sort,
Status = o.Status,
CreateTIme = o.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
});
}
);
rsp.success = true;
rsp.code = 0;
rsp.count = q.PageWithAllRecordCount;
return(rsp);
}
/// <summary>
/// 获取列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <CustomAccReceiptViewModel> GetPageList(int pageIndex, int pageSize)
{
JsonRsp <CustomAccReceiptViewModel> rsp = new JsonRsp <CustomAccReceiptViewModel>();
CustomAccReceiptModel CustomAccReceipt = new CustomAccReceiptModel();
CustomModel custom = new CustomModel();
//Select 方法不指定具体要选择的实体类属性，可以推迟到EntityContainer类的MapToList 方法上指定
OQL joinQ = OQL.From(CustomAccReceipt)
.Join(custom).On(CustomAccReceipt.CustomId, custom.ID)
.Select()
.OrderBy(CustomAccReceipt.CreateTime, "asc")
.END;
joinQ.Limit(pageSize, pageIndex, true);
PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
EntityContainer ec = new EntityContainer(joinQ, db);
rsp.data = (List <CustomAccReceiptViewModel>)ec.MapToList <CustomAccReceiptViewModel>(() => new CustomAccReceiptViewModel()
{
ID = CustomAccReceipt.ID,
CustomId = CustomAccReceipt.CustomId,
CustomName = custom.CustomName,
ChargeCardNo = CustomAccReceipt.ChargeCardNo,
CurrentAmount = CustomAccReceipt.CurrentAmount,
Remark = CustomAccReceipt.Remark,
Status = CustomAccReceipt.Status,
CreateId = CustomAccReceipt.CreateId,
CreateUser = CustomAccReceipt.CreateUser,
CreateIP = CustomAccReceipt.CreateIP,
CreateTime = CustomAccReceipt.CreateTime,
});
rsp.success = true;
rsp.code = 0;
rsp.count = joinQ.PageWithAllRecordCount;
return(rsp);
}
/// <summary>
/// 获取管理员列表（分页）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public JsonRsp <AccountViewModel> GetPageList(int pageIndex, int pageSize)
{
JsonRsp <AccountViewModel> rsp = new JsonRsp <AccountViewModel>();
AdminAccount m = new AdminAccount();
OQL q = OQL.From(m)
.Select(m.ID, m.AccountName, m.TrueName, m.Status, m.LoginTime, m.LoginCount, m.LastLoginTime, m.CreateTIme)
.OrderBy(m.ID, "asc")
.END;
//分页
q.Limit(pageSize, pageIndex, true);
//q.PageWithAllRecordCount = allCount;
//List<Employee> list= EntityQuery<Employee>.QueryList(q);
List <AdminAccount> list = q.ToList <AdminAccount>();//使用OQL扩展
rsp.data = list.ConvertAll <AccountViewModel>(o =>
{
return(new AccountViewModel()
{
ID = o.ID,
AccountName = o.AccountName,
TrueName = o.TrueName,
Status = o.Status,
LoginTime = o.LoginTime,
LastLoginTime = o.LastLoginTime,
LoginCount = o.LoginCount,
CreateTime = o.CreateTIme,
});
}
);
rsp.success = true;
rsp.code = 0;
rsp.count = q.PageWithAllRecordCount;
return(rsp);
}
private static void TestTransaction()
{
AdoHelper db = MyDB.GetDBHelper();
EntityQuery <AuctionOperationLog> query = new EntityQuery <AuctionOperationLog>(db);
AuctionOperationLog optLog = new AuctionOperationLog();
optLog.OperaterID = 1000;
optLog.Module = "Login";
optLog.Operation = "登录成功1";
optLog.LogSource = "PC";
db.BeginTransaction();
try
{
query.Insert(optLog);
//必须设置为全部属性已经修改，否则仅会更新 Operation 字段
optLog.ResetChanges(true);
optLog.Operation = "退出登录";
query.Insert(optLog);
//optLog.Module = "Login";
//OQL q = OQL.From(optLog).Select().Where(optLog.Module).END;
OQL q = new OQL(optLog);
//q.Select().Where(q.Condition.AND(optLog.Operation, "like", "%登录%"));
q.Select().Count(optLog.OperaterID, "");//使用空字符串参数，这样统计的值将放到 OperaterID 属性中
//必须指定db参数，否则不再一个事务中，无法进行统计查询
optLog = EntityQuery <AuctionOperationLog> .QueryObject(q, db);
int allCount = optLog.OperaterID;
//optLog 已经使用过，在生成OQL的查询前，必须使用新的实体对象，
// 否则下面的查询仅会使用OperaterID 字段从而导致分页查询出错
optLog = new AuctionOperationLog();
q = new OQL(optLog);
q.Select().OrderBy(optLog.Module, "asc").OrderBy(optLog.AtDateTime, "desc");
q.Limit(10, 2);
q.PageEnable = true;
q.PageWithAllRecordCount = allCount;
//查询列表并更新到数据库
List <AuctionOperationLog> list = EntityQuery <AuctionOperationLog> .QueryList(q, db);
foreach (AuctionOperationLog logItem in list)
{
logItem.AtDateTime = DateTime.Now;
}
query.Update(list);
db.Commit();
Console.WriteLine("事务操作成功。");
}
catch (Exception ex)
{
Console.WriteLine("Error:" + ex.Message);
db.Rollback();
}
}
/// <summary>
/// 获取商品销售价格信息
/// </summary>
/// <returns></returns>
public IEnumerable <GoodsSaleInfoVM> GetGoodsSaleInfo()
{
GoodsBaseInfo bInfo = new GoodsBaseInfo();
GoodsStock stock = new GoodsStock();
/*
* Select采用指定详细实体类属性的方式：
* OQL joinQ = OQL.From(bInfo)
* .Join(stock).On(bInfo.SerialNumber, stock.SerialNumber)
* .Select(
* bInfo.GoodsName,
* bInfo.Manufacturer,
* bInfo.SerialNumber,
* stock.GoodsPrice,
* stock.MakeOnDate,
* bInfo.CanUserMonth,
* stock.Stocks,
* stock.GoodsID)
* .OrderBy(bInfo.GoodsName, "asc")
* .END;
*/
//Select 方法不指定具体要选择的实体类属性，可以推迟到EntityContainer类的MapToList 方法上指定
OQL joinQ = OQL.From(bInfo)
.Join(stock).On(bInfo.SerialNumber, stock.SerialNumber)
.Select()
.OrderBy(bInfo.SerialNumber, "asc").OrderBy(bInfo.GoodsName, "asc")
.END;
joinQ.Limit(3, 3);
PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
EntityContainer ec = new EntityContainer(joinQ, db);
/*
* 如果OQL的Select方法指定了详细的实体类属性，那么映射结果，可以采取下面的方式：
* var result = ec.Map<GoodsSaleInfoVM>(e =>
* {
* e.GoodsName = ec.GetItemValue<string>(0);
* e.Manufacturer = ec.GetItemValue<string>(1);
* e.SerialNumber = ec.GetItemValue<string>(2);
* e.GoodsPrice = ec.GetItemValue<decimal>(3);
* e.MakeOnDate = ec.GetItemValue<DateTime>(4);
* e.CanUserMonth = ec.GetItemValue<int>(5);
* e.Stocks = ec.GetItemValue<int>(6);
* e.GoodsID = ec.GetItemValue<int>(7);
* return e;
* }
* );
*/
var result = ec.MapToList <GoodsSaleInfoVM>(() => new GoodsSaleInfoVM()
{
GoodsName = bInfo.GoodsName,
Manufacturer = bInfo.Manufacturer,
SerialNumber = bInfo.SerialNumber,
GoodsPrice = stock.GoodsPrice,
MakeOnDate = stock.MakeOnDate,
CanUserMonth = bInfo.CanUserMonth,
Stocks = stock.Stocks,
GoodsID = stock.GoodsID,
ExpireDate = stock.MakeOnDate.AddMonths(bInfo.CanUserMonth)
});
return(result);
}
