/// <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));
}
