/// <summary> /// 根据id获取实体对象 /// </summary> /// <param name="ID">对象id</param> /// <returns></returns> public static T GetModel <T>(OQLCompareFunc <T> cp, bool IsUseCache = false) where T : EntityBase, ILongID, new() { T model = new T(); model = OQL.FromObject <T>().Select().Where(cp).END.ToObject(); return(model); }
public IUser GetUser(int id) { return(OQL.FromObject <IUser>() .Select() .Where((cmp, e) => cmp.Property(e.UserID) == id) .END .ToObject(CurrentDataBase)); }
public ContactInfo GetUser(int id) { return(OQL.FromObject <ContactInfo>() .Select() .Where((cmp, e) => cmp.Property(e.CID) == id) .END .ToObject(CurrentDataBase)); }
/// <summary> /// 根据id获取实体对象 /// </summary> /// <param name="ID">对象id</param> /// <returns></returns> public static T GetModel <T>(int ID, bool IsUseCache = false) where T : EntityBase, ILongID, new() { T model = new T(); model.ID = ID; //UpdateCacheEventSource es = new UpdateCacheEventSource(); //model.Subscribe(es); //es.RaiseEvent("GetModel"); if (RedisHelper.IsUseCache && IsUseCache && (model is ICacheEvent)) { MemoryCache mc = MemoryCache.Default; ICacheEvent im = model as ICacheEvent; string rule = im.GetCacheKeyRule(); string key = string.Format(rule, model.GetIdentityName(), model.ID); CacheItemPolicy cip = new CacheItemPolicy(); cip.AbsoluteExpiration = DateTimeOffset.Now.AddHours(6); var cache = mc.Get(key); if (cache != null) { model = cache as T; } else { model = RedisHelper.GetStringKey <T>(key); if (model == null) { model = OQL.FromObject <T>().Select().Where((cmp, m) => cmp.Comparer(m.ID, "=", ID)).END.ToObject(); if (model != null) { RedisHelper.SetStringKey <T>(key, model, DateTime.Now.AddDays(7) - DateTime.Now); } } if (model != null) { //更新本地缓存 mc.Set(key, model, cip); } } } else { model = OQL.FromObject <T>().Select().Where((cmp, m) => cmp.Comparer(m.ID, "=", ID)).END.ToObject(); } return(model); }
/// <summary> /// 根据商品名称为顺序获取所有商品基本信息 /// </summary> /// <returns></returns> public List <GoodsBaseInfoVM> GetAllGoodsBaseInfoOrderByNames() { //GoodsBaseInfo info = new GoodsBaseInfo(); //OQL q = new OQL(info); //q.Select(info.SerialNumber,info.GoodsName,info.Manufacturer,info.CanUserMonth ) // .OrderBy(info.GoodsName, "asc"); OQL q = OQL.FromObject <GoodsBaseInfo>() .Select(info => new object[] { info.SerialNumber, info.GoodsName, info.Manufacturer, info.CanUserMonth }) .OrderBy((o, info) => o.Asc(info.GoodsName)) .END; return(GetGoodsBaseInfoList(q)); }
//95行源码,一行代码调用实现带字段选取+条件判断+排序+分页功能的增强ORM框架 static void TestGOQL() { string sqlInfo = ""; //下面使用 ITable_User 或者 Table_User均可 List <ITable_User> userList = OQL.FromObject <ITable_User>() //.Select() .Select(s => new object[] { s.UID, s.Name, s.Sex }) //仅选取3个字段 .Where((cmp, user) => cmp.Property(user.UID) < 100) .OrderBy((o, user) => o.Asc(user.UID)) .Limit(5, 1) //限制5条记录每页,取第一页 .Print(out sqlInfo) .ToList(); Console.WriteLine(sqlInfo); Console.WriteLine("User List item count:{0}", userList.Count); if (userList.Count > 0) { Console.WriteLine("User Entity Type:{0}", userList[0].GetType()); } }
void Test1() { Users user = new Users() { NickName = "pdf.net", RoleID = RoleNames.Admin }; UserRoles roles = new UserRoles() { RoleName = "role1" }; //测试字段直接比较 OQL q00 = OQL.From(user) .Select() .Where(cmp => cmp.Comparer(user.AddTime, "=", user.LastLoginTime)) .END; Console.WriteLine("q00:one table and select all fields \r\n{0}", q00); Console.WriteLine(q00.PrintParameterInfo()); OQL q0 = OQL.From(user) .Select() .Where(user.NickName, user.RoleID) .OrderBy(user.ID) .END; q0.SelectStar = true; Console.WriteLine("q0:one table and select all fields \r\n{0}", q0); Console.WriteLine(q0.PrintParameterInfo()); OQL q01 = OQL.From(user) .Select() .Where(cmp => cmp.Comparer(user.ID, OQLCompare.CompareType.Equal, 0)) .OrderBy(user.ID) .END; q01.SelectStar = true; Console.WriteLine("q01:one table and select all fields \r\n{0}", q01); Console.WriteLine(q01.PrintParameterInfo()); //var userList = EntityQuery<Users>.QueryList(q0); //if (userList.Count > 0) //{ // Users u = userList[0]; // Console.WriteLine("User Type is:" + u.RoleID.ToString()); // u.RoleID = RoleNames.User; // EntityQuery<Users>.Instance.Update(u); //} OQL q = OQL.From(user) .Select(user.ID, user.UserName, user.RoleID) .END; q.Select(user.LastLoginIP).Where(user.NickName); Console.WriteLine("q1:one table and select some fields\r\n{0}", q); Console.WriteLine(q.PrintParameterInfo()); //动态指定查询的字段和比较关心、值 q = OQL.From(user).Select().Where(new QueryParameter[] { new QueryParameter("ID", "=", 1) } ).END; Console.WriteLine("q1:QueryParameter Test\r\n{0}", q); Console.WriteLine(q.PrintParameterInfo()); 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()); OQL q3 = OQL.From(user, roles) .Select(user.ID, user.UserName, roles.ID, roles.RoleName) .Where(cmp => cmp.Comparer(user.RoleID, "=", roles.ID) & cmp.EqualValue(roles.RoleName)) .OrderBy(user.ID) .END; Console.WriteLine("q3:two table query not use join\r\n{0}", q3); Console.WriteLine(q3.PrintParameterInfo()); OQL q4 = OQL.From(user).InnerJoin(roles).On(user.RoleID, roles.ID) .Select(user.RoleID).Count(user.RoleID, "roldid_count") // .Where(user.NickName) .GroupBy(user.RoleID) .END; Console.WriteLine("q4:count from two table query \r\n{0}", q4); Console.WriteLine(q4.PrintParameterInfo()); OQL q5 = OQL.From(user) .Select(user.RoleID).Count(user.RoleID, "count_rolid") .GroupBy(user.RoleID) .Having(p => p.Count(user.RoleID, OQLCompare.CompareType.GreaterThanOrEqual, 2)) .END; Console.WriteLine("q5:having Test: \r\n{0}", q5); Console.WriteLine(q5.PrintParameterInfo()); //q5 = OQL.From(user) // .Select(user.Age).Sum(user.Age,"sum_age") // .GroupBy(user.Age) // .OrderBy(user.Age) // .END; //Console.WriteLine("q5:having Test: \r\n{0}", q5); //Console.WriteLine(q5.PrintParameterInfo()); OQL q6 = OQL.From(user).Select() .Where(cmp => cmp.Comparer(user.RoleID, "is not", null) & cmp.Comparer(user.AddTime, ">=", DateTime.Now.AddDays(-1)) & cmp.Comparer(user.AddTime, "<", DateTime.Now) ) .END; q6.SelectStar = true; Console.WriteLine("q6:SQL 'IS' Test: \r\n{0}", q6); Console.WriteLine(q6.PrintParameterInfo()); OQL q7 = OQL.From(user).Select() .Where(cmp => cmp.Between(user.ID, 5, 10)) .END; q7.SelectStar = true; Console.WriteLine("q7:SQL Between Test: \r\n{0}", q7); Console.WriteLine(q7.PrintParameterInfo()); //Compare 对象使用 ComparerSqlFunction 方法,解决SQL函数操作结果的类型跟字段类型不一致的问题 //感谢网友 【有事M我】发现此问题 2014.3.11 GOQL <Users> q8 = OQL.FromObject <Users>() .Select() .Where((cmp, u) => cmp.ComparerSqlFunction(u.NickName, ">", 0, "CHARINDEX( 'xiao',{0} )")) .END; string sql; q8.Print(out sql); Console.WriteLine("q8:SQL Function Test: \r\n{0}", sql); }
/// <summary> /// 根据id获取实体对象 /// </summary> /// <param name="ID">对象id</param> /// <returns></returns> public static T GetModelB <T>(string ID, AdoHelper ado) where T : EntityBase, IStringID, new() { OQL oql = OQL.FromObject <T>().Select().Where((cmp, u) => cmp.Comparer(u.ID, "=", ID)).END.END; return(EntityQuery <T> .QueryObject(oql, ado)); }
/// <summary> /// 增 /// </summary> /// <param name="model"></param> /// <returns></returns> public JsonRsp Add(ChargeCardsModel model) { model.ChargeCardNo = "H" + model.CustomId + DateTime.Now.ToString("yyyyMMddHHmmss"); //开启事务 PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper(); int returnvalue = 0; try { db.BeginTransaction(); //客户财务信息操作 //获取当前客户财务信息 CustomFinancialModel financialModel = OQL.FromObject <CustomFinancialModel>().Select().Where((cmp, m) => cmp.Comparer(m.CustomId, "=", model.CustomId) & cmp.Comparer(m.TenantId, "=", TenantId)).END.ToObject(db); if (financialModel == null) { db.Rollback(); return(new JsonRsp { success = false, retmsg = "获取客户合同信息失败" }); } decimal amount = model.CurrentAmount; string remark = "客户充卡"; if (model.MoneyType == (int)MoneyTypeEnum.应收) { if (financialModel.MoneyTypeOneAmount < amount) { db.Rollback(); return(new JsonRsp { success = false, retmsg = "余额只有" + financialModel.MoneyTypeOneAmount + "" }); } financialModel.MoneyTypeOneAmount -= amount; CustomFinancialDetailModel financialDetail = new CustomFinancialDetailModel(); financialDetail.CustomFinancialId = financialModel.ID; financialDetail.FinanciaOpeType = (int)FinanciaOpeTypeEnum.减少; financialDetail.Remark = remark; financialDetail.MoneyType = (int)BaseEnum.MoneyTypeEnum.应收; financialDetail.CurrentAmount = amount; financialDetail.Balance = financialModel.MoneyTypeOneAmount; returnvalue += Add <CustomFinancialDetailModel>(financialDetail, db); } else if (model.MoneyType == (int)BaseEnum.MoneyTypeEnum.赠送) { if (financialModel.MoneyTypeTwoAmount < amount) { db.Rollback(); return(new JsonRsp { success = false, retmsg = "余额只有" + financialModel.MoneyTypeTwoAmount + "" }); } financialModel.MoneyTypeTwoAmount -= amount; CustomFinancialDetailModel financialDetail = new CustomFinancialDetailModel(); financialDetail.CustomFinancialId = financialModel.ID; financialDetail.FinanciaOpeType = (int)FinanciaOpeTypeEnum.减少; financialDetail.Remark = remark; financialDetail.MoneyType = (int)BaseEnum.MoneyTypeEnum.赠送; financialDetail.CurrentAmount = amount; financialDetail.Balance = financialModel.MoneyTypeTwoAmount;; returnvalue += Add <CustomFinancialDetailModel>(financialDetail, db); } else if (model.MoneyType == (int)BaseEnum.MoneyTypeEnum.置换) { if (financialModel.MoneyTypeThreeAmount < amount) { db.Rollback(); return(new JsonRsp { success = false, retmsg = "余额只有" + financialModel.MoneyTypeThreeAmount + "" }); } financialModel.MoneyTypeThreeAmount -= amount; CustomFinancialDetailModel financialDetail = new CustomFinancialDetailModel(); financialDetail.CustomFinancialId = financialModel.ID; financialDetail.FinanciaOpeType = (int)FinanciaOpeTypeEnum.减少; financialDetail.Remark = remark; financialDetail.MoneyType = (int)BaseEnum.MoneyTypeEnum.置换; financialDetail.CurrentAmount = amount; financialDetail.Balance = financialModel.MoneyTypeThreeAmount; returnvalue += Add <CustomFinancialDetailModel>(financialDetail, db); } returnvalue += Update <CustomFinancialModel>(financialModel, db); returnvalue += Add <ChargeCardsModel>(model, db); //新增客户应付 CustomAccReceiptModel customAR = new CustomAccReceiptModel(); customAR.CustomId = model.CustomId; customAR.ChargeCardNo = model.ChargeCardNo; customAR.CurrentAmount = model.MoneyType == (int)MoneyTypeEnum.应收 ? model.CurrentAmount : 0; customAR.Status = (int)ARStatusEnum.已确认; customAR.CreateId = AdminId; customAR.CreateUser = AdminName; customAR.CreateIP = Util.GetLocalIP; customAR.CreateTime = DateTime.Now; returnvalue += Add <CustomAccReceiptModel>(customAR, db); //事务提交 if (returnvalue == 4) { db.Commit(); } else { returnvalue = 0; db.Rollback(); } } catch (Exception ex) { //事务回滚 db.Rollback(); return(new JsonRsp { success = false, retmsg = ex.Message.ToString() }); } return(new JsonRsp { success = returnvalue > 0, code = returnvalue }); }
/// <summary> /// 审核 /// </summary> /// <param name="model"></param> /// <returns></returns> public JsonRsp Audit(long[] Ids, int status) { #region if (Ids == null) { return(new JsonRsp { success = false, retmsg = "请选择要操作的数据" }); } int returnvalue = 0; PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper(); try { //开始事务 db.BeginTransaction(); //更新状态 ContractModel model = new ContractModel(); OQL qList = OQL.From(model) .Select() .OrderBy(model.Sort, "asc") .END; List <ContractModel> items = qList.ToList <ContractModel>(); foreach (ContractModel item in items) { if (item.Status != 0) { return(new JsonRsp { success = false, retmsg = "只能审核待审核合同,该合同当前状态为:" + Util.getStatus(item.Status, typeof(BaseEnum.ProtocolTypeEnum)) }); } //更新合同状态 item.Status = status; returnvalue += Update <ContractModel>(item, db, false); string remark = "合同/协议号:" + item.ContractNo + "审核通过"; //获取当前客户财务信息 CustomFinancialModel financialModel = OQL.FromObject <CustomFinancialModel>().Select().Where((cmp, m) => cmp.Comparer(m.CustomId, "=", item.CustomId) & cmp.Comparer(m.TenantId, "=", item.TenantId)).END.ToObject(db); if (financialModel == null) { financialModel = new CustomFinancialModel(); //客户财务信息初始化 financialModel.CustomId = item.CustomId; financialModel.MoneyTypeOneAmount = item.MoneyTypeOneAmount; financialModel.MoneyTypeOneTotalAmount = item.MoneyTypeOneAmount; financialModel.MoneyTypeTwoAmount = item.MoneyTypeTwoAmount; financialModel.MoneyTypeTwoTotalAmount = item.MoneyTypeTwoAmount; financialModel.MoneyTypeThreeAmount = item.MoneyTypeThreeAmount; financialModel.MoneyTypeThreeTotalAmount = item.MoneyTypeThreeAmount; financialModel.Status = 1; returnvalue += Add <CustomFinancialModel>(financialModel, db); } else { //客户财务信息修改 financialModel.CustomId = item.CustomId; financialModel.MoneyTypeOneAmount += item.MoneyTypeOneAmount; financialModel.MoneyTypeOneTotalAmount += item.MoneyTypeOneAmount; financialModel.MoneyTypeTwoAmount += item.MoneyTypeTwoAmount; financialModel.MoneyTypeTwoTotalAmount += item.MoneyTypeTwoAmount; financialModel.MoneyTypeThreeAmount += item.MoneyTypeThreeAmount; financialModel.MoneyTypeThreeTotalAmount += item.MoneyTypeThreeAmount; financialModel.Status = 1; returnvalue += Update <CustomFinancialModel>(financialModel, db); } //新增客户财务信息日志 //应收明细 CustomFinancialDetailModel financialDetailOne = new CustomFinancialDetailModel(); financialDetailOne.CustomFinancialId = financialModel.ID; financialDetailOne.FinanciaOpeType = (int)FinanciaOpeTypeEnum.增加; financialDetailOne.CurrentAmount += item.MoneyTypeOneAmount; financialDetailOne.Balance += item.MoneyTypeOneAmount; financialDetailOne.Remark = remark; financialDetailOne.Status = 1; financialDetailOne.MoneyType = (int)BaseEnum.MoneyTypeEnum.应收; returnvalue += Add <CustomFinancialDetailModel>(financialDetailOne, db); //赠送明细 CustomFinancialDetailModel financialDetailTwo = new CustomFinancialDetailModel(); financialDetailTwo.CustomFinancialId = financialModel.ID; financialDetailTwo.FinanciaOpeType = (int)FinanciaOpeTypeEnum.增加; financialDetailTwo.CurrentAmount += item.MoneyTypeTwoAmount; financialDetailTwo.Balance += item.MoneyTypeTwoAmount; financialDetailTwo.Remark = remark; financialDetailTwo.Status = 1; financialDetailTwo.MoneyType = (int)BaseEnum.MoneyTypeEnum.赠送; returnvalue += Add <CustomFinancialDetailModel>(financialDetailTwo, db); //置换明细 CustomFinancialDetailModel financialDetailThree = new CustomFinancialDetailModel(); financialDetailThree.CustomFinancialId = financialModel.ID; financialDetailThree.FinanciaOpeType = (int)FinanciaOpeTypeEnum.增加; financialDetailThree.CurrentAmount += item.MoneyTypeThreeAmount; financialDetailThree.Balance += item.MoneyTypeThreeAmount; financialDetailThree.Remark = remark; financialDetailThree.Status = 1; financialDetailThree.MoneyType = (int)BaseEnum.MoneyTypeEnum.置换; returnvalue += Add <CustomFinancialDetailModel>(financialDetailThree); //事务提交 if (returnvalue == 5) { db.Commit(); } else { returnvalue = 0; db.Rollback(); } } } catch (Exception ex) { //事务回滚 db.Rollback(); return(new JsonRsp { success = false, retmsg = ex.Message.ToString() }); } return(new JsonRsp { success = returnvalue > 0, code = returnvalue }); #endregion }