自定义sql查询
生成查询条件：
//生成查询条件
Operator operator = operatorService.selectByPrimaryKey(SessionUserUtils.getSessionUserId());
String queryCondition = operatorService.generatePurviewSql(operator, 50);
deviceInfoVo.setQueryCondition(queryCondition);
生成操作员权限 可查询sql：
/***
* 生成操作员权限 可查询sql
* @param oper 系统用户
* @param type 操作类型 10=商户查询 20 活动列表 30 系统用户 40 系统角色 50 设备列表
* 60 商品列表 70 会员列表 110 资讯中心 120 消息中心 130 日志中心 140 单机库存/总库存 150 设备分组列表
* 160 后台访问日志/后台操作日志/对账日志/定时任务执行日志/分页查询系统警报日志 170 库存操作日志
* 180 设备注册信息列表 190 历史商品 200 场景活动查看设备 210 批量发券列表 220 用户持有券
*
* @return
*/
@Override
public String generatePurviewSql(Operator oper, Integer type) {
String sql = "";
if (oper == null) {
return sql;
}
MerchantInfo merchant = merchantInfoDao.selectByPrimaryKey(oper.getSmerchantId());
if (type == 10) {//商户查询
if (oper.getImerType().intValue() == 0) {//所有商户
if (merchant.getSparentId().equals("0")) {//公司系统用户
sql = " ((shi.id='" + merchant.getId() + "') or (shi.sparent_id='" + merchant.getId() + "') or (shi.sroot_id in (select smi.id from sh_merchant_info smi where smi.sparent_id='" + merchant.getId() + "')))";
} else {//商户系统用户
sql = " ((shi.id='" + merchant.getId() + "') or (shi.sparent_id='" + merchant.getId() + "') or (shi.sroot_id='" + merchant.getId() + "'))";
}
} else if (oper.getImerType().intValue() == 1) {//所有bd对应商户
sql = " (shi.sdb_id='" + oper.getId() + "')";
} else if (oper.getImerType().intValue() == 4) {//所有bd对应商户 及 以下
sql = " ((shi.sdb_id='" + oper.getId() + "') or (shi.sparent_id in (select smi.id from sh_merchant_info smi where smi.sdb_id='" + oper.getId() + "')) or (shi.sroot_id in (select smi.id from sh_merchant_info smi where smi.sdb_id='" + oper.getId() + "')))";
} else if (oper.getImerType().intValue() == 2) {//自己商户
sql = " shi.id='" + merchant.getId() + "'";
} else if (oper.getImerType().intValue() == 3) {//指定商户
String arr[] = null;
StringBuffer smerList = new StringBuffer();
if (StringUtil.isNotBlank(oper.getSmerList())) {
arr = oper.getSmerList().split(",");
for (String str : arr) {
if (StringUtil.isNotBlank(str)) {
smerList.append("'" + str + "',");
}
}
}
if (smerList.toString().length() > 0) {
sql = " shi.id in (" + smerList.toString().substring(0, (smerList.toString().length() - 1)) + ")";
} else {
sql = " 1 = 2";
}
}
} else if (type == 20 || type == 30 || type == 40 || type == 50 || type == 60 || type == 70 || type == 80 || type == 90
|| type == 100 || type == 110 || type == 120 || type == 130 || type == 140 || type == 150 || type == 160 || type == 170
|| type == 180 || type == 190 || type == 200 || type == 210 || type == 220) {
//20 活动列表 30 系统用户 40 系统角色 50 设备列表 60 商品列表 70 会员列表/批量下发券全部用户
// 80 计划补货列表/补货列表 90 订单列表/审核订单/退款订单 100商户域名 110 资讯中心
// 120 消息中心 130 日志中心 140 单机库存/总库存 150 设备分组列表 170 库存操作日志
// 180 设备注册信息列表/设备详细信息/设备搬迁管理/设备监控数据配置信息/设备故障信息记录
// 190历史商品 200场景活动查看活动 210 批量发券列表 220 用户持有券
if (oper.getImerType().intValue() == 0) {//所有商户
if (merchant.getSparentId().equals("0")) {//公司系统用户
sql = " 1=1";
} else {//商户系统用户 本身和
sql = " A.smerchant_id in (select smi.id from sh_merchant_info smi where (smi.id ='" + merchant.getId() + "' or smi.sroot_id='" + merchant.getId() + "'))";
}
} else if (oper.getImerType().intValue() == 1) {//所有bd对应商户
sql = " A.smerchant_id in (select smi.id from sh_merchant_info smi where smi.sdb_id='" + oper.getId() + "')";
} else if (oper.getImerType().intValue() == 4) {//所有bd对应商户 及 以下
sql = " ((A.smerchant_id in (select smi.id from sh_merchant_info smi where smi.sdb_id='" + oper.getId() + "')) or (A.smerchant_id in (select sminfo.id from sh_merchant_info sminfo where sminfo.sroot_id in (select smi.id from sh_merchant_info smi where smi.sdb_id='" + oper.getId() + "'))))";
} else if (oper.getImerType().intValue() == 2) {//自己商户
sql = " A.smerchant_id='" + merchant.getId() + "'";
} else if (oper.getImerType().intValue() == 3) {//指定商户
String arr[] = null;
StringBuffer smerList = new StringBuffer();
if (StringUtil.isNotBlank(oper.getSmerList())) {
arr = oper.getSmerList().split(",");
for (String str : arr) {
smerList.append("'" + str + "',");
}
}
if (smerList.toString().length() > 0) {
sql = " A.smerchant_id in (" + smerList.toString().substring(0, (smerList.toString().length() - 1)) + ")";
} else {
sql = " 1 = 2";
}
}
if (oper.getIdevType().intValue() != 0) {
String arr[] = null;
StringBuffer actSql = new StringBuffer();
StringBuffer sdevList = new StringBuffer();
if (StringUtil.isNotBlank(oper.getSgroupDecList())) {
arr = oper.getSgroupDecList().split(",");
if (210 == type) {
for (String str : arr) {
if (StringUtil.isNotBlank(str)) {
DeviceInfo deviceInfo = deviceInfoService.selectByPrimaryKey(str);
if (null != deviceInfo) {
sdevList.append("'" + deviceInfo.getScode() + "',");
}
}
}
} else {
for (String str : arr) {
if (StringUtil.isNotBlank(str)) {
sdevList.append("'" + str + "',");
actSql.append("find_in_set('" + str + "',aur.SDEVICE_ID) and ");
}
}
}
}
if (sdevList.toString().length() > 0) {
if (type == 20) {
sql += " and A.id in (select aur.SAC_ID from AC_USE_RANGE aur where (aur.IRANGE_TYPE in (10,30) and aur.sac_id in (select acac.id from ac_activity_conf acac where acac.smerchant_id = '" + merchant.getId() + "')) or (" + actSql + " 1=1))";
} else if (type == 50 || type == 130 || type == 200) {
sql += " and A.id in (" + sdevList.toString().substring(0, (sdevList.toString().length() - 1)) + ")";
} else if (type == 60) {
sql += " and A.smerchant_id in (select sdinfo.smerchant_id from sb_device_info sdinfo where sdinfo.id in (" + sdevList.toString().substring(0, (sdevList.toString().length() - 1)) + "))";
} else if (type == 70) {
sql += " and A.sreg_device_code in (select sdinfo.scode from sb_device_info sdinfo where sdinfo.id in (" + sdevList.toString().substring(0, (sdevList.toString().length() - 1)) + "))";
} else if (type == 80 || type == 90 || type == 140 || type == 170 || type == 180 || type == 190) {
sql += " and A.sdevice_id in (" + sdevList.toString().substring(0, (sdevList.toString().length() - 1)) + ")";
} else if (type == 150) {
sql += " and SGR.SDEVICE_ID in (" + sdevList.toString().substring(0, (sdevList.toString().length() - 1)) + ")";
} else if (type == 210) {
sql += " and (A.SUSE_LIMIT_DEVICE = '' or A.SUSE_LIMIT_DEVICE in (" + sdevList.toString().substring(0, (sdevList.toString().length() - 1)) + "))";
} else if (type == 220) {
sql += " and C.sreg_device_code in (select sdinfo.scode from sb_device_info sdinfo where sdinfo.id in (" + sdevList.toString().substring(0, (sdevList.toString().length() - 1)) + "))";
}
} else {
sql += " and 1 = 2";
}
}
}
return sql;
}
xml:
<if test="queryCondition != null and queryCondition != '' ">
and ${queryCondition}
</if>
浙公网安备 33010602011771号