/// <summary> /// 获取素材分页列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public PagerList <dynamic> GetMaterialMstrPageList(CmsMaterialMstrQuery query) { var perssion = _permissionHelper.GetCondition(AbpSession.USR_TYPE, AbpSession.USR_SCOPE, "a.CREATE_ORG_NO", AbpSession.ORG_NO, AbpSession.BG_NO); return(_sqlQuery.Select(@" a.MATERIAL_ID, a.MATERIAL_COVER_URL, a.MATERIAL_TITLE, a.CREATE_DATE, a.UPDATE_DATE, a.MATERIAL_STATUS, a.MATERIAL_AUTHOR, b.MATERIAL_TYPE_NAME, c.BU_NAME") .Filter("b.MATERIAL_ATTR", query.MATERIAL_ATTR) .Filter("a.MATERIAL_TYPE_ID", query.MATERIAL_TYPE_ID) .Filter("a.MATERIAL_TITLE", query.MATERIAL_TITLE, Operator.Contains) .Filter("a.UDF2", query.UDF2) .Filter("a.DEL_FLAG", 1) .In("a.MATERIAL_ID", query.MATERIAL_IDS) //.Filter("b.DEL_FLAG", 1) .Filter("c.DEL_FLAG", 1) .And(perssion) .OrderBy("a.UPDATE_DATE desc") .GetPageList <dynamic>(@"CMS_MATERIAL_MSTR a LEFT JOIN CMS_MATERIAL_TYPE b ON a.MATERIAL_TYPE_ID = b.MATERIAL_TYPE_ID LEFT JOIN MDM_BU_MSTR c ON a.CREATE_ORG_NO=c.BU_NO", Context.Database.GetDbConnection(), query)); }
/// <summary> /// 获取用户角色范围 /// </summary> /// <param name="userId"></param> /// <returns></returns> public async Task <List <string> > GetUserRoleScope(decimal userId) { return(_sqlQuery.Select(@" wm_concat (DISTINCT nvl (ROLE_SCOPE, 'MD')) ROLE_SCOPE") .And("role_id in(select role_id from sys_usr_auth auth where usr_id=" + userId + ")") .Filter("del_flag", 1) .GetList <string>(@"sys_role_mstr", Context.Database.GetDbConnection())); }
public dynamic GetGoodClassList() { //string where = _permissionHelper.GetCondition(AbpSession.USR_TYPE, AbpSession.USR_SCOPE, "CREATE_ORG_NO", AbpSession.ORG_NO, AbpSession.BG_NO); return(_sqlQuery.Select("*") .Filter("DEL_FLAG", 1) .Filter("BG_NO", AbpSession.BG_NO) .OrderBy("UPDATE_DATE DESC") //.And(where) .GetList <dynamic>("mdm_goods_class", Context.Database.GetDbConnection())); }
/// <summary> /// 获取上架商品 /// </summary> /// <param name="model"></param> /// <returns></returns> public PagerList <MdmGoodsList> GetPutAwayGoodsList(GoodsInfoInputModel model) { return(_sqlQuery.Select(@"GL_ID as Id, GL_NO, GL_NAME, GL_PRINT_NAME, GL_TYPE, GL_LARGECLASS, GL_INCLASS, GL_SMALLCLASS, GL_SUBCLASS, GL_LEVEL, CAR_BRAND_ID, CAR_BRAND_DESC, CAR_CLASS_ID, CAR_CLASS_DESC, CAR_TYPE_ID, CAR_TYPE_DESC, GL_UNIT, MNEMONIC_CODE, GL_SPEC, GL_MODEL, GL_MATERIAL, GL_SHELFLIFE, MADE_IN, GL_STATUS, BU_NO, GL_RMK, GL_PROPERTY, COMMENT_NUM, HIT_NUM, PROMOTION_INFO, GL_DESC, GL_SPEC_DESC, GL_PACKAGE_DESC, GL_DESC_M, GL_SPEC_DESC_M, GL_PACKAGE_DESC_M, GL_WARRANTY_DESC, GL_PUR_ATTR, GL_QA, GL_BRAND, GL_FUNC, IS_COMBO, GOODS_ATTR, LGS_SP, LGS_SP_NO, GL_SDATE, GL_EDATE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, CREATE_PSN, CREATE_DATE, UPDATE_PSN, UPDATE_DATE, CREATE_ORG_NO, DEL_FLAG, PROMOTION_ATTR, BG_NO, MEMBER_PRICE, MENBER_POINTS, ENABLE_MP, GOODS_SALES, IS_ERPGOODS") .Filter("del_flag", 1) .Filter("GL_STATUS", 1) .Filter("IS_ERPGOODS", 1) .Filter("BU_NO", AbpSession.ORG_NO) .Contains("GL_NAME", model.goodsName) .OrderBy("CREATE_DATE desc") .GetPageList <MdmGoodsList>("mdm_goods_list", Context.Database.GetDbConnection(), model.iDisplayStart, model.iDisplayLength)); }
/// <summary> /// 获取素材类型分页 /// </summary> /// <param name="query"></param> /// <returns></returns> public PagerList <dynamic> GetMaterialTypePageList(CmsMaterialTypeQuery query) { return(_sqlQuery.Select(@" MATERIAL_TYPE_ID, MATERIAL_TYPE_NAME, CREATE_DATE") .Filter("MATERIAL_ATTR", query.MATERIAL_ATTR) .Filter("MATERIAL_TYPE_ID", query.MATERIAL_TYPE_ID) .Filter("DEL_FLAG", 1) .Filter("BG_NO", AbpSession.BG_NO) .OrderBy("CREATE_DATE desc") .GetPageList <dynamic>("CMS_MATERIAL_TYPE", Context.Database.GetDbConnection(), query)); }
/// <summary> /// 获取预约配置分页数据 /// </summary> /// <param name="query"></param> /// <returns></returns> public dynamic GetAptConfigPageList(CrmAptConfigMstrQuery query) { //string where = _permissionHelper.GetCondition(AbpSession.USR_TYPE, AbpSession.USR_SCOPE, "BU_NO", AbpSession.ORG_NO, AbpSession.BG_NO); return(_sqlQuery.Select(@"APT_CONFIG_ID, APT_CONFIG_SDATE, APT_CONFIG_EDATE,APT_TYPE,case APT_TYPE when 1 then '预约试驾' when 2 then '养修预约' else '' end as APT_TYPENAME, APT_CONFIG_JSON, APT_MIN_DISCOUNT, UPDATE_DATE, DIS_TYPE,case DIS_TYPE when 1 then '折扣' when 2 then '金额' else '' end as DIS_TYPENAME, APT_LIMIT, IS_DEFAULT") .Filter("DEL_FLAG", 1) .Filter("DIS_TYPE", query.DIS_TYPE) .Filter("APT_TYPE", query.APT_TYPE) .Filter("BU_NO", AbpSession.ORG_NO) //.And(where) .And("APT_TYPE!='0'") .OrderBy("UPDATE_DATE DESC") .GetPageList <dynamic>(" CRM_APT_CONFIG_MSTR", Context.Database.GetDbConnection(), query)); }
/// <summary> /// 获取秒杀商品分页数据 /// </summary> /// <param name="query"></param> /// <returns></returns> public dynamic GetMsGoodsPageList(MdmGoodsMstrQuery query) { string where = _permissionHelper.GetCondition(AbpSession.USR_TYPE, AbpSession.USR_SCOPE, "good.CREATE_ORG_NO", AbpSession.ORG_NO, AbpSession.BG_NO); return(_sqlQuery.Select(@"good.goods_id,good.GOODS_RMK ,good.goods_name,gl.UDF2 Pic,gl.GL_STATUS,good.sp_sdate,good.sp_edate,good.sp_qty,good.SP_PRICE,gs.PL_SELL_PRICE,gs.PL_PROMO_PRICE") .Filter("good.sp_flag", 1) .Filter("gl.GL_STATUS", 1) .Contains("good.GOODS_RMK", query.GOODS_RMK) .And(where) .OrderBy("good.CREATE_DATE DESC") .GetPageList <dynamic>(@"mdm_goods_mstr good left join mdm_goods_list gl on good.gl_id = gl.gl_id left join MDM_GOODS_SPL gs on gs.pl_goods_no = good.goods_no", Context.Database.GetDbConnection(), query)); }
/// <summary> /// 获取机构列表 /// </summary> /// <returns></returns> public List <dynamic> GetMdmBuMstrList(MdmBuMstrQuery query) { var perssion = _permissionHelper.GetCondition(AbpSession.USR_TYPE, AbpSession.USR_SCOPE, "BU_NO", AbpSession.ORG_NO, AbpSession.BG_NO); perssion = query.BU_TYPE != 0 ? string.IsNullOrEmpty(perssion) ? "BU_TYPE=" + query.BU_TYPE + "" : perssion + " and BU_TYPE=" + query.BU_TYPE + "" : perssion; return(_sqlQuery.Select(@"BU_NO,BU_NAME") .Filter("PARENT_BU_NO", query.PARENT_BU_NO) .Filter("BU_STATUS", "1") .Filter("DEL_FLAG", "1") .And(perssion) .OrderBy("BU_TYPE") .GetList <dynamic>("MDM_BU_MSTR", Context.Database.GetDbConnection())); }
/// <summary> /// 获取公众号分页列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public PagerList <dynamic> GetWctPaMstrPageList(WctPaMstrQuery query) { var perssion = _permissionHelper.GetCondition(AbpSession.USR_TYPE, AbpSession.USR_SCOPE, "WCT_PA_MSTR.PA_ID_NO", AbpSession.ORG_NO, AbpSession.BG_NO); return(_sqlQuery.Select(@" WCT_PA_MSTR.PA_ID, WCT_PA_MSTR.PA_NAME, WCT_PA_MSTR.PA_ORIGINAL_ID, WCT_PA_MSTR.CREATE_DATE, SYS_USR_MSTR.USR_NAME, BU.BU_NAME, PABU.BU_NAME BG_NAME, PA_APPID, CASE PA_TYPE_ID WHEN 1 THEN '服务号' WHEN 2 THEN '订阅号' WHEN 3 THEN '企业号' WHEN 4 THEN '小程序' END PA_TYPE_ID") .Filter("WCT_PA_MSTR.PA_TYPE_ID", query.PA_TYPE_ID) .Filter("WCT_PA_MSTR.PA_NAME", query.PA_NAME, Operator.Contains) .Filter("WCT_PA_MSTR.PA_ORIGINAL_ID", query.PA_ORIGINAL_ID) .Filter("WCT_PA_MSTR.DEL_FLAG", "1") .And(perssion) .OrderBy("WCT_PA_MSTR.UPDATE_DATE desc") .GetPageList <dynamic>(@"WCT_PA_MSTR LEFT JOIN SYS_USR_MSTR ON WCT_PA_MSTR.CREATE_PSN = SYS_USR_MSTR.USR_ID LEFT JOIN MDM_BU_MSTR BU ON WCT_PA_MSTR.PA_ID_NO=BU.BU_NO LEFT JOIN MDM_BU_MSTR PABU ON PABU.BU_NO=BU.PARENT_BU_NO", Context.Database.GetDbConnection(), query)); }
/// <summary> /// 获取评论集合 /// </summary> /// <param name="query"></param> /// <returns></returns> public List <GetCommentModel> GetCommentList(WctCommentMstrQuery query) { return(_sqlQuery.Select(@" a.COMMENT_ID, a.MATERIAL_ID, a.COMMENT_OPENID, a.MAIN_COMMENT_ID, a.USER_ID, a.COMMENT_CONTENT, a.COMMENT_DATE, a.COMMENT_PARENTID, a.SUPPORT_COUNT, b.UDF3 NICK_NAME, b.WX_AVATAR_URL, c.COMMENT_RULE") .Filter("a.BU_NO", query.BU_NO) .Filter("a.MATERIAL_ID", query.MATERIAL_ID) .Filter("c.CREATE_PSN", query.CREATE_PSN) .Filter("a.DEL_FLAG", 1) .Filter("c.DEL_FLAG", 1) .OrderBy("a.COMMENT_DATE desc") .GetList <GetCommentModel>(@"WCT_COMMENT_MSTR a left join SYS_USR_WCT b on a.COMMENT_OPENID=b.OPEN_ID left join CMS_MATERIAL_MSTR c on a.MATERIAL_ID=c.MATERIAL_ID", Context.Database.GetDbConnection())); }
/// <summary> /// 获取回复信息分页列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public PagerList <dynamic> GetReplyPageList(WctReplyMstrQuery query) { return(_sqlQuery.Select(@" REPLY_ID, REPLY_KEYWORD, REPLY_TEXT, REPLY_STATUS, CASE REPLY_TYPE WHEN 1 THEN '关注' WHEN 2 THEN '默认' WHEN 3 THEN '关键词' WHEN 4 THEN '新用户关注' END REPLY_TYPE, CASE REPLY_CONTENT_TYPE WHEN 0 THEN '文本' WHEN 1 THEN '图文(链接)' WHEN 2 THEN '图文(详情)' WHEN 3 THEN '图文(模块)' END REPLY_CONTENT_TYPE") .Filter("REPLY_KEYWORD", query.REPLY_KEYWORD, Operator.Contains) .Filter("CREATE_ORG_NO", AbpSession.ORG_NO) .Filter("DEL_FLAG", "1") .And(query.REPLY_TYPE == null ? "" : "REPLY_TYPE=" + query.REPLY_TYPE + "") .OrderBy("UPDATE_DATE desc") .GetPageList <dynamic>(@"WCT_REPLY_MSTR", Context.Database.GetDbConnection(), query)); }
/// <summary> /// 获取预约管理分页数据 /// </summary> /// <param name="query"></param> /// <returns></returns> public PagerList <dynamic> GetCrmAptMstrPageList(CrmAptMstrQuery query) { //string where = _permissionHelper.GetCondition(AbpSession.USR_TYPE, "CREATE_ORG_NO", AbpSession.ORG_NO, AbpSession.BG_NO); string where = ""; if (!string.IsNullOrEmpty(query.START_DATE)) { where += "to_char(apt.APT_DATE,'yyyy-MM-dd')>='" + query.START_DATE + "'"; } if (!string.IsNullOrEmpty(query.END_DATE)) { where += string.IsNullOrEmpty(where) ? " to_char(apt.APT_DATE,'yyyy-MM-dd')<='" + query.END_DATE + "'" : " and to_char(apt.APT_DATE,'yyyy-MM-dd')<='" + query.END_DATE + "'"; } return(_sqlQuery.Select(@"apt.APT_NO,apt.APT_CLASS,apt.SERVICE_DESK,apt.APT_CHANNEL,apt.CUS_NO,apt.UDF3,apt.UDF4,apt.UDF5,apt.UDF6,apt.CUS_NAME,apt.CUS_PHONE_NO,apt.CAR_ID,apt.VIN,apt.APT_DATE,apt.APT_TIMESPAN, apt.APT_STATUS, bu.BU_NAME, BU.PARENT_BU_NAME, wct.UDF3 NICK_NAME") .Filter("apt.del_flag", 1) .Contains("apt.APT_NO", query.APT_NO) .Contains("apt.CUS_NAME", query.CUS_NAME) .Contains("apt.CUS_PHONE_NO", query.CUS_PHONE_NO) .Filter("apt.APT_CLASS", query.APT_CLASS) .Filter("apt.APT_STATUS", query.APT_STATUS) //.Filter("to_char(apt.APT_DATE,'yyyy-MM-dd')>=", query.START_DATE) //.Filter("to_char(apt.APT_DATE,'yyyy-MM-dd')<=", query.END_DATE) .Filter("bu.PARENT_BU_NO", query.AREA_NO) .Filter("bu.BU_NO", query.BU_NO) .And(where) .OrderBy("apt.CREATE_DATE DESC") .GetPageList <dynamic>("CRM_APT_MSTR apt left join mdm_bu_mstr bu on apt.APT_BU_NO = bu.bu_no left join sys_usr_wct wct on apt.OPENID = wct.OPEN_ID", Context.Database.GetDbConnection(), query)); }
/// <summary> /// 分页获取粉丝信息 /// </summary> /// <param name="query"></param> /// <returns></returns> public PagerList <dynamic> GetWctInfoPageList(SysUsrWctQuery query) { var perssion = _permissionHelper.GetCondition(AbpSession.USR_TYPE, AbpSession.USR_SCOPE, "wct.bu_no", AbpSession.ORG_NO, AbpSession.BG_NO); var joinSql = query.FOLLOW_STATUS == null ? "" : "wct.follow_status=" + query.FOLLOW_STATUS + ""; return(_sqlQuery.Select(@" wct.Wct_Id, wct.bu_no, wct.usr_id, wct.open_id, wct.cus_svc_code, wct.usr_source, wct.reg_date, wct.referee_usr_id, wct.create_date, wct.update_date, usr.usr_name, usr.usr_real_name, usr.usr_mobile, wct.UDF1 unfollow_date, wct.UDF2 sex, wct.UDF3 nickname, ( CASE wct.follow_status WHEN 1 THEN '已关注' ELSE '取消关注' END ) AS follow_status, ( CASE wct.UDF4 WHEN '1' THEN '是' ELSE '否' END ) AS IsBlack, wct.WX_AVATAR_URL, wct.tag_name, bu.BU_NAME, bu.PARENT_BU_NAME" ) .Filter("wct.UDF3", query.nickName, Operator.Contains) .Filter("usr.usr_real_name", query.realName, Operator.Contains) .Filter("bu.PARENT_BU_NO", query.parentorgNo) .Filter("bu.BU_NO", query.orgNo) .Filter("wct.TAG_NAME", query.TAG_NAME) .Filter("usr.usr_mobile", query.mobile) .Filter("wct.reg_date", query.BEGIN_REG_DATE, Operator.GreaterEqual) .Filter("wct.reg_date", query.END_REG_DATE, Operator.LessEqual) .Filter("wct.del_flag", "1") .And(perssion) .And(joinSql) .OrderBy("wct.create_date,wct.wct_id desc") .GetPageList <dynamic>(@"sys_usr_wct wct left join sys_usr_mstr usr on wct.usr_id=usr.usr_id left join mdm_bu_mstr bu on wct.bu_no=bu.bu_no", Context.Database.GetDbConnection(), query)); }
/// <summary> /// 获取省市地列表 /// </summary> /// <returns></returns> public List <dynamic> GetMdmBasRegionList(MdmBasRegionQuery query) { return(_sqlQuery.Select(@"REGION_NO,REGION_NAME") .Filter("PARENT_REGION_NO", query.PARENT_REGION_NO) .Filter("REGION_LEVEL", query.REGION_LEVEL) .Filter("DEL_FLAG", "1") .OrderBy("REGION_NO") .GetList <dynamic>("MDM_BAS_REGION", Context.Database.GetDbConnection())); }
/// <summary> /// 获取标签分页列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public PagerList <dynamic> GetTagMstrPageList(TagMstrQuery query) { return(_sqlQuery.Select(@" TAG_MSTR_CODE, TAG_NAME, TAG_MSTR_DESC, CREATE_DATE, CASE TAG_STATUS WHEN 1 THEN '是' ELSE '否' END TAG_STATUS") .Filter("TAG_NAME", query.TAG_NAME, Operator.Contains) .Filter("CREATE_ORG_NO", AbpSession.ORG_NO) .Filter("DEL_FLAG", 1) .OrderBy("UPDATE_DATE desc") .GetPageList <dynamic>(@"TAG_MSTR", Context.Database.GetDbConnection(), query)); }
/// <summary> /// 验证该属性是否存在 /// </summary> /// <param name="parentId"></param> /// <param name="value"></param> /// <returns></returns> public bool ValidateProperty(string parentId, string value) { var list = _sqlQuery.Select("*") .Filter("del_flag", 1) .Filter("PROPERTY_PARENTID", parentId) .Filter("PROPERTY_DEFAULT_VALUE", value) .Filter("CREATE_ORG_NO", AbpSession.ORG_NO) .GetList <dynamic>("MDM_GOODS_PROPERTY_MSTR", Context.Database.GetDbConnection()); return(list?.Count > 0); }
/// <summary> /// 获取微信菜单列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public dynamic GetWxMenuList(WctMenuMstrQuery query) { return(_sqlQuery.Select(@"*") .Filter("MENU_ID_NO", AbpSession.ORG_NO) .Filter("MENU_LEVEL", query.MENU_LEVEL) .Filter("MENU_PARENTID", query.MENU_PARENTID) .Filter("DEL_FLAG", 1) .Filter("MENU_STATUS", 1) .OrderBy("MENU_DISPLAYINDEX") .GetList <dynamic>(@"WCT_MENU_MSTR", Context.Database.GetDbConnection())); }
/// <summary> /// 获取用户菜单权限 /// </summary> /// <param name="userId"></param> /// <returns></returns> public List <SysUsrMstrNavTreeModel> GetSysUsrMstrNavTree(decimal userId) { return(_sqlQuery.Select(@" nav.NAV_NO, nav.NAV_NAME, nav.NAV_URL, nav.NAV_IMG_URL, nav.NAV_PARENT_NO, nav.NAV_SORT_NO, nav.NAV_EXPAND") .Filter("auth.USR_ID", userId) .Filter("role.DEL_FLAG", "1") .Filter("role.ROLE_STATUS", "1") .Filter("nav.NAV_STATUS", "1") .Filter("nav.DEL_FLAG", "1") .OrderBy("nav.NAV_NO,nav.NAV_SORT_NO") .GetList <SysUsrMstrNavTreeModel>(@"sys_usr_auth auth INNER JOIN SYS_ROLE_MSTR role on auth.ROLE_ID = role.ROLE_ID INNER JOIN SYS_ROLE_MENU_PERMISSION per on auth.ROLE_ID = per.ROLE_ID INNER JOIN SYS_NAV_TREE nav on per.PERMISSION = nav.NAV_NO" , Context.Database.GetDbConnection())); }
/// <summary> /// 获取职务分页列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public PagerList <dynamic> GetMdmDutyPageList(MdmDutyMstrQuery query) { return(_sqlQuery.Select(@" DUTY_ID, DUTY_NO, DUTY_NAME, ( CASE DUTY_STATUS WHEN 1 THEN '启用' ELSE '禁用' END ) AS DUTY_STATUS" ) .Filter("DUTY_NAME", query.DUTY_NAME, Operator.Contains) .Filter("DUTY_NO", query.DUTY_NO, Operator.Contains) .Filter("BG_NO", AbpSession.BG_NO) .Filter("DEL_FLAG", "1") .OrderBy("CREATE_DATE desc") .GetPageList <dynamic>(@"MDM_DUTY_MSTR", Context.Database.GetDbConnection(), query)); }
public async Task TestToStringAsync() { string id = Id.ObjectId(); string name = Id.Guid().Substring(0, 10); var customer = new Customer(id) { Name = name }; await _customerRepository.AddAsync(customer); await _unitOfWork.CommitAsync(); var result = await _query .Select <Customer>(t => t.Name) .From <Customer>("c") .Where <Customer>(t => t.Id == id) .ToStringAsync(); _output.WriteLine(_query.GetDebugSql()); Assert.Equal(name, result); }
/// <summary> /// 获取主应用列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public List <WctAppItemModel> GetAppItemList() { return(_sqlQuery.Select(@" MSTR_ID appId, ITEM_NAME itemName, WCT_APP_URL itemUrl, UDF1 moduleId, UDF2 module_icon") .Filter("BU_NO", AbpSession.ORG_NO) .Filter("DEL_FLAG", 1) .OrderBy("ITEM_SORT") .GetList <WctAppItemModel>(@"WCT_APP_ITEM", Context.Database.GetDbConnection())); }
public PagerList <dynamic> GetCrmQpaperMstrPageList(CrmQpaperMstrQuery query) { string where = _permissionHelper.GetCondition(AbpSession.USR_TYPE, AbpSession.USR_SCOPE, "CREATE_ORG_NO", AbpSession.ORG_NO, AbpSession.BG_NO); return(_sqlQuery.Select(@"PAPER_ID, PAPER_NAME, PAPER_TYPE, INCLUDE_QUESTION_IDS, PAPER_SDATE, PAPER_EDATE, PAPER_DESC, PAPER_STATUS") .Filter("DEL_FLAG", 1) .Contains("PAPER_NAME", query.PAPER_NAME) .Filter("PAPER_TYPE", query.PAPER_TYPE) .Filter("PAPER_STATUS", query.PAPER_STATUS) .And(where) .OrderBy("CREATE_DATE desc") .GetPageList <dynamic>(" CRM_QPAPER_MSTR", Context.Database.GetDbConnection(), query)); }
public PagerList <dynamic> GetCrmQpaperQuPageList(CrmQpaperQuQuery query) { string where = _permissionHelper.GetCondition(AbpSession.USR_TYPE, AbpSession.USR_SCOPE, "CREATE_ORG_NO", AbpSession.ORG_NO, AbpSession.BG_NO); return(_sqlQuery.Select(@"QU_ID, QU_SN, QU_TYPE, QU_NAME, QU_ENABLED, QU_ANSWER, PAPER_TYPE") .Filter("DEL_FLAG", 1) .Filter("QU_SN", query.QU_SN) .Contains("QU_NAME", query.QU_NAME) .Filter("PAPER_TYPE", query.PAPER_TYPE) .Filter("QU_TYPE", query.QU_TYPE) .Filter("QU_ENABLED", query.QU_ENABLED) .OrderBy("CREATE_DATE desc") .GetPageList <dynamic>(" CRM_QPAPER_QU", Context.Database.GetDbConnection(), query)); }
/// <summary> /// 获取系统模块分页列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public PagerList <dynamic> GetSysModulePageList(WctSysmoduleMstrQuery query) { var sql = ""; if (!string.IsNullOrEmpty(query.SYSM_JSON_VALUE)) { sql = "to_char(SYSM_JSON_VALUE) like '%" + query.SYSM_JSON_VALUE + "%'"; } return(_sqlQuery.Select(@" SYSM_ID, SYSM_KEY, SYSM_TITLE, to_char(SYSM_JSON_VALUE), UPDATE_DATE") .Filter("SYSM_KEY", query.SYSM_KEY) .Filter("SYSM_TITLE", query.SYSM_TITLE) .Filter("BG_NO", AbpSession.BG_NO) .Filter("DEL_FLAG", "1") .And("SYSM_CODE is null") .And(sql) .OrderBy("UPDATE_DATE desc") .GetPageList <dynamic>(@"WCT_SYSMODULE_MSTR", Context.Database.GetDbConnection(), query)); }
public PagerList <dynamic> GetHelpInfoPageList(WctHelpTelMstrQuery query) { string where = _permissionHelper.GetCondition(AbpSession.USR_TYPE, AbpSession.USR_SCOPE, "CREATE_ORG_NO", AbpSession.ORG_NO, AbpSession.BG_NO); var list = _sqlQuery.Select(@"TEL_ID, TEL_NAME, TEL_NO,TEL_ID_NO, CREATE_ORG_NO, CREATE_PSN, CREATE_DATE, UPDATE_PSN, UPDATE_DATE, BG_NO, case TEL_TYPE when 'insurance' then '保险' when 'help' then '救援' else '' end as TEL_TYPE") .Filter("DEL_FLAG", 1) .Filter("CREATE_ORG_NO", query.CREATE_ORG_NO) .Contains("TEL_NO", query.TEL_NO) .And(where) .OrderBy("CREATE_DATE DESC") .GetPageList <dynamic>(" WCT_HELP_TEL_MSTR", Context.Database.GetDbConnection(), query); return(list); }
/// <summary> /// 获取金融政策分页数据 /// </summary> /// <param name="query"></param> /// <returns></returns> public dynamic GetFinancePolicyList(CarFinancePolicyQuery query) { string where = _permissionHelper.GetCondition(AbpSession.USR_TYPE, AbpSession.USR_SCOPE, "CREATE_ORG_NO", AbpSession.ORG_NO, AbpSession.BG_NO); return(_sqlQuery.Select("*") .Filter("DEL_FLAG", 1) .Filter("BIZ_TYPE", query.BIZ_TYPE) .Filter("BRAND_CODE", query.BRAND_CODE) .Filter("CLASS_CODE", query.CLASS_CODE) .Filter("TYPE_CODE", query.TYPE_CODE) .Filter("SUBTYPE_CODE", query.SUBTYPE_CODE) .Contains("TAG_IDS", query.TAG_IDS) .And(where) .OrderBy("CREATE_DATE desc") .GetPageList <dynamic>("CAR_FINANCE_POLICY", Context.Database.GetDbConnection(), query)); }
/// <summary> /// 获取客户反馈分页数据 /// </summary> /// <param name="query"></param> /// <returns></returns> public PagerList <dynamic> GetCrmCasePageList(CrmCaseMstrQuery query) { string where = _permissionHelper.GetCondition(AbpSession.USR_TYPE, AbpSession.USR_SCOPE, "cm.CREATE_ORG_NO", AbpSession.ORG_NO, AbpSession.BG_NO); if (!string.IsNullOrEmpty(query.START_DATE)) { where += string.IsNullOrEmpty(where) ? "to_char(apt.APT_DATE,'yyyy-MM-dd')>='" + query.START_DATE + "'" : "and to_char(apt.APT_DATE,'yyyy-MM-dd')>='" + query.START_DATE + "'"; } if (!string.IsNullOrEmpty(query.END_DATE)) { where += string.IsNullOrEmpty(where) ? "to_char(apt.APT_DATE,'yyyy-MM-dd')<='" + query.END_DATE + "'" : "and to_char(apt.APT_DATE,'yyyy-MM-dd')<='" + query.END_DATE + "'"; } if (query.CASE_TYPE > 0) { where += string.IsNullOrEmpty(where) ? "cm.CASE_TYPE='" + query.CASE_TYPE + "'" : " and cm.CASE_TYPE='" + query.CASE_TYPE + "'"; } return(_sqlQuery.Select(@"cm.CASE_NO, cm.UDF1, cm.CASE_DATE, cm.CASE_CLASS, cm.CASE_TYPE, CASE WHEN cm.CASE_TYPE = 10 THEN '表扬员工' WHEN cm.CASE_TYPE = 11 THEN '表扬店铺' WHEN cm.CASE_TYPE = 20 THEN '投诉员工' WHEN cm.CASE_TYPE = 21 THEN '投诉店铺' END CASE_TYPE_TEXT, cm.CASE_CONTENT, bu.bu_name, bu.parent_bu_name, ( SELECT wct.udf3 FROM sys_usr_wct wct WHERE wct.bu_no = cm.CREATE_ORG_NO AND wct.open_id = cm.REF_CASE_NO AND rownum = 1 ) WXName, (SELECT USR_JOB FROM SYS_USR_MSTR WHERE USR_ID=TO_NUMBER(REGEXP_REPLACE(cm.RESPONSIBLE_PSN,'[^0-9]','')))UsrJob") .Filter("cm.DEL_FLAG", 1) .And("cm.CASE_TYPE>0") //.Filter("cm.CASE_TYPE", query.CASE_TYPE) .Contains("cm.UDF1", query.UDF1) .And(where) //.Filter("to_char(cm.CASE_DATE,'yyyy-MM-dd')>=", query.START_DATE) //.Filter("to_char(cm.CASE_DATE,'yyyy-MM-dd')<=", query.END_DATE) .Filter("bu.PARENT_BU_NO", query.AREA_NO) .Filter("bu.BU_NO", query.BU_NO) .OrderBy("cm.CASE_DATE desc") .GetPageList <dynamic>(@"CRM_CASE_MSTR cm LEFT JOIN mdm_bu_mstr bu ON cm.CREATE_ORG_NO = bu.bu_no", Context.Database.GetDbConnection(), query)); }
/// <summary> /// 获取主应用列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public List <WctAppInfoModel> GetAppMstrList() { return(_sqlQuery.Select(@" APP_ID key, APP_SORT sort, APP_NAME appName, WCT_MODULE_ID moduleId, WCT_APP_URL appUrl, WCT_MODULE_TYPE moduleType, UDF2 moduleKey, UDF3 module_icon") .Filter("BU_NO", AbpSession.ORG_NO) .Filter("DEL_FLAG", 1) .OrderBy("APP_SORT") .GetList <WctAppInfoModel>(@"WCT_APP_MSTR", Context.Database.GetDbConnection())); }
public async Task TestToAsync() { string id = Id.ObjectId(); string name = Id.Guid().Substring(0, 10); var customer = new Customer(id) { Name = name }; await _customerRepository.AddAsync(customer); await _unitOfWork.CommitAsync(); var result = await _query.Select <Customer>(t => new object[] { t.Name }) .From <Customer>("Customers") .Where("CustomerId", id) .ToAsync <Customer>(); Assert.Equal(name, result.Name); }
public PagerList <dynamic> GetCrmEvaPageList(CrmEvaMstrQuery query) { string where = _permissionHelper.GetCondition(AbpSession.USR_TYPE, AbpSession.USR_SCOPE, "mstr.CREATE_ORG_NO", AbpSession.ORG_NO, AbpSession.BG_NO); if (!string.IsNullOrEmpty(query.START_DATE)) { if (!string.IsNullOrEmpty(where)) { where += " and to_char(mstr.EVA_DATE,'yyyy-MM-dd')>='" + query.START_DATE + "'"; } else { where += " to_char(mstr.EVA_DATE,'yyyy-MM-dd')>='" + query.START_DATE + "'"; } } if (!string.IsNullOrEmpty(query.END_DATE)) { where += " and to_char(mstr.EVA_DATE,'yyyy-MM-dd')<='" + query.END_DATE + "'"; } return(_sqlQuery.Select(@"mstr.EVA_ID, mstr.EVA_TYPE, mstr.EVA_DATE, mstr.EVA_OBJ_TYPE, mstr.EVA_CONTENT, mstr.EVA_OBJ_NAME, mstr.EVA_TOTAL_VALUE, mstr.CREATE_ORG_NO, mstr.EVA_REF_NO1, mstr.EVA_REF_NO, mstr.UDF4, bu.bu_name, bu.parent_bu_name, (SELECT USR_JOB FROM SYS_USR_MSTR WHERE USR_ID = TO_NUMBER(REGEXP_REPLACE(mstr.EVA_OBJ_NO, '[^0-9]', '')))UsrJob") .Filter("mstr.DEL_FLAG", 1) .NotEqual("mstr.EVA_TYPE", "聊天信息") .And(where) .Contains("mstr.EVA_OBJ_TYPE", query.EVA_OBJ_TYPE) .Contains("mstr.EVA_OBJ_NAME", query.EVA_OBJ_NAME) .Filter("bu.PARENT_BU_NO", query.AREA_NO) .Filter("bu.BU_NO", query.BU_NO) .In("EVA_TYPE", query.EVA_TYPES) .OrderBy("mstr.EVA_DATE desc") .GetPageList <dynamic>(@" CRM_EVA_MSTR mstr LEFT JOIN mdm_bu_mstr bu ON mstr.CREATE_ORG_NO = bu.bu_no", Context.Database.GetDbConnection(), query)); }