需求：新增接口 查询管理员，也就是查询users表中role_id = 0 and status = 1的数据） 原接口查询到的是所有数据，在页面做的过滤，现在是要修改这种方式，直接查询出管理员数据。 UserMapper.xml
1 2 3 4 5 6
<!--2026-6-15 查询管理员（role_id = 0 and status = 1） --> <selectid="selectAdmins"resultType="com.example.luan_recruitment_system.entity.User"> SELECT * FROM users WHERE role_id = 0 AND status = 1 ORDER BY create_time DESC </select>
UserMapper.java
1
List<User> selectAdmins();
UserServiceImpl.java
1 2 3 4
@Override public List<User> getAdmins() { return userMapper.selectAdmins(); }
UserService.java
1 2 3 4
/** * 查询管理员（role_id = 0 and status = 1） */ List<User> getAdmins();
UserController.java
1 2 3 4 5 6 7 8
privatefinal UserService userService;
@Operation(summary = "查询管理员", description = "查询users表中role_id = 0 and status = 1的数据") @GetMapping("/admins") public Result<List<User>> getAdmins() { List<User> admins = userService.getAdmins(); return Result.success(admins); }
报错： http://localhost/marketing/task-template 页面报错 permission.js:512 [Vue warn]: Error in created hook: “TypeError: Cannot read properties of undefined (reading ‘marketing’)” found in —> at src/views/marketing/template/index.vue at src/layout/components/AppMain.vue at src/layout/index.vue at src/App.vue permission.js:512 TypeError: Cannot read properties of undefined (reading ‘marketing’)
create index idx_marketing_task_template_task_type on marketing_task_template(task_type); create index idx_marketing_task_template_status on marketing_task_template(status);
-- 索引 create index idx_marketing_task_task_type on marketing_task(task_type); create index idx_marketing_task_task_status on marketing_task(task_status); create index idx_marketing_task_audit_status on marketing_task(audit_status); create index idx_marketing_task_manager_id on marketing_task(manager_id); create index idx_marketing_task_village_id on marketing_task(village_id); create index idx_marketing_task_customer_id on marketing_task(customer_id);
PRIMARY KEY (rule_id), INDEX idx_data_dimension (data_dimension), INDEX idx_status (status), INDEX idx_del_flag (del_flag), INDEX idx_bind_tag_id (bind_tag_id) ) ENGINE=INNODB AUTO_INCREMENT=100 COMMENT='客户画像规则表';
WHERE t.deleted = 0 AND t.id IN ( SELECT ft.farmer_id FROM t_farmer_tag ft WHERE ft.deleted = 0 AND ft.tag_name IN (SELECT tag_name FROM t_customer_tag WHERE tag_id = #{tagId} AND del_flag < 1) )
ALTER TABLE t_farmer_call ADDCOLUMN IF NOTEXISTS task_id BIGINTDEFAULTNULL COMMENT '关联营销任务ID' AFTER farmer_id, ADDCOLUMN IF NOTEXISTS manager_id BIGINTDEFAULTNULL COMMENT '记录人（客户经理）ID' AFTER task_id;
<!-- 按网点ID查询未完成授信的客户列表（用于整村授信任务派发） --> <select id="selectFarmerByOrgIdNotCredited" resultMap="FarmerResultMap"> <include refid="selectFarmerVo"/> WHERE t.deleted = 0 AND t.org_id = #{orgId} AND (t.credit_village_status IS NULL OR t.credit_village_status NOT IN(2, 3, 4)) </select>
<!-- 按客户经理ID查询未完成授信的客户列表（用于整村授信任务派发） --> <select id="selectFarmerByManagerIdNotCredited" resultMap="FarmerResultMap"> <include refid="selectFarmerVo"/> WHERE t.deleted = 0 AND t.manager_id = #{managerId} AND (t.credit_village_status IS NULL OR t.credit_village_status NOT IN(2, 3, 4)) </select>
<!-- 按行政村ID查询未完成授信的客户列表（用于整村授信任务派发） --> <select id="selectFarmerByVillageIdNotCredited" resultMap="FarmerResultMap"> <include refid="selectFarmerVo"/> WHERE t.deleted = 0 AND t.village_id = #{villageId} AND (t.credit_village_status IS NULL OR t.credit_village_status NOT IN(2, 3, 4)) </select>
<!-- 按标签ID查询未完成授信的客户列表（用于整村授信任务派发） --> <select id="selectFarmerByTagIdNotCredited" resultMap="FarmerResultMap"> <include refid="selectFarmerVo"/> WHERE t.deleted = 0 AND t.id IN( SELECT ft.farmer_id FROM t_farmer_tag ft WHERE ft.deleted = 0 AND ft.tag_id = #{tagId} ) AND (t.credit_village_status IS NULL OR t.credit_village_status NOT IN(2, 3, 4)) </select>
1 2 3 4 5 6 7 8 9 10 11
// 按网点ID查询未完成授信的客户列表（用于整村授信任务派发） List<TFarmer> selectFarmerByOrgIdNotCredited(@Param("orgId") Long orgId);
// 按客户经理ID查询未完成授信的客户列表（用于整村授信任务派发） List<TFarmer> selectFarmerByManagerIdNotCredited(@Param("managerId") Long managerId);
// 按行政村ID查询未完成授信的客户列表（用于整村授信任务派发） List<TFarmer> selectFarmerByVillageIdNotCredited(@Param("villageId") Long villageId);
// 按标签ID查询未完成授信的客户列表（用于整村授信任务派发） List<TFarmer> selectFarmerByTagIdNotCredited(@Param("tagId") Long tagId);
客户数 → COUNT(DISTINCT mt.customer_id) as customerCount 未电访 → SUM(CASE WHEN COALESCE(mt.real_duration, 0) = 0 AND mt.task_status != ‘2’ THEN 1 ELSE 0 END) as noPhoneCount 未走访 → SUM(CASE WHEN mt.task_status IN (‘0’, ‘1’) THEN 1 ELSE 0 END) as noVisitCount
1 2 3 4 5 6
// 我的任务角标 = 待跟进任务数 let tasksItem = null for (const menu ofthis.menuList) { tasksItem = menu.children.find((q) => q.id === 'tasks') if (tasksItem) break }
AND EXISTS (SELECT 1 FROM t_farmer tfm WHERE tfm.village_id = rga.area_id AND tfm.deleted = 0 AND tfm.manager_id = #{managerId}) AND EXISTS (SELECT 1 FROM t_farmer tfm WHERE tfm.village_id = rga.area_id AND tfm.deleted = 0 AND tfm.manager_id = #{managerId}) 删除 <if test="managerId != null">AND tf.manager_id = #{managerId}</if> GROUP BY rga.area_id, rga.area_name, COALESCE(bo.org_name, bo2.org_name) GROUP BY rga.area_id, rga.area_name, bo.org_name
only_full_group_by 报错风险 only_full_group_by 是 MySQL SQL_MODE（SQL 语法校验规则） 里的一个选项。 MySQL 5.7+、MySQL8.0 默认开启。 规则核心 在 SELECT … GROUP BY 查询中： SELECT、HAVING、ORDER BY 里出现的所有非聚合字段，必须全部写在 GROUP BY 后面。 聚合函数：SUM() COUNT() MAX() MIN() GROUP_CONCAT() 非聚合字段：普通列 mtc_f.customer_name、mt.task_name
<selectid="selectVillageCreditBoardData"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="com.ruoyi.mobile.vo.VillageCreditBoardVO$BoardData"> SELECT COUNT(DISTINCT rga.area_id) AS totalVillage, COUNT(DISTINCT tf.id) AS totalHousehold, COUNT(DISTINCT CASE WHEN vcs.credit_status = 3 THEN tf.id END) AS creditedHousehold, COALESCE(( SELECT SUM(sub_amt.total_amt) FROM ( SELECT vcs2.customer_id, SUM(vcs2.suggest_credit_amount) AS total_amt FROM village_credit_survey vcs2 INNER JOIN t_farmer tf2 ON vcs2.customer_id = tf2.id AND tf2.deleted = 0 WHERE vcs2.credit_status = 3 AND vcs2.del_flag = 0 <iftest="batchId != null"> AND vcs2.batch_id = #{batchId} </if> <iftest="periodValue != null and periodType == 'month'"> AND DATE_FORMAT(vcs2.survey_time, '%Y-%m') = #{periodValue} </if> <iftest="periodValue != null and periodType == 'quarter'"> AND YEAR(vcs2.survey_time) = SUBSTRING(#{periodValue}, 1, 4) AND QUARTER(vcs2.survey_time) = SUBSTRING(#{periodValue}, 6, 1) </if> <iftest="periodValue != null and periodType == 'year'"> AND YEAR(vcs2.survey_time) = #{periodValue} </if> <iftest="orgId != null"> AND tf2.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND tf2.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null"> AND tf2.manager_id = #{managerId} </if> GROUP BY vcs2.customer_id ) sub_amt ), 0) AS totalCreditAmount FROM village_credit_survey vcs INNER JOIN t_farmer tf ON vcs.customer_id = tf.id AND tf.deleted = 0 LEFT JOIN rural_grid_area rga ON tf.village_id = rga.area_id AND rga.area_type = 3 AND rga.del_flag = '0' WHERE vcs.del_flag = 0 <iftest="batchId != null"> AND vcs.batch_id = #{batchId} </if> <iftest="periodValue != null and periodType == 'month'"> AND DATE_FORMAT(vcs.survey_time, '%Y-%m') = #{periodValue} </if> <iftest="periodValue != null and periodType == 'quarter'"> AND YEAR(vcs.survey_time) = SUBSTRING(#{periodValue}, 1, 4) AND QUARTER(vcs.survey_time) = SUBSTRING(#{periodValue}, 6, 1) </if> <iftest="periodValue != null and periodType == 'year'"> AND YEAR(vcs.survey_time) = #{periodValue} </if> <iftest="orgId != null"> AND tf.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND tf.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null"> AND tf.manager_id = #{managerId} </if> </select>
<selectid="selectCreditRateRank"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="com.ruoyi.mobile.vo.VillageCreditBoardVO$VillageCreditRankVO"> SELECT rga.area_name AS villageName, CASE WHEN COUNT(DISTINCT tf.id) = 0 THEN 0 ELSE ROUND(COUNT(DISTINCT CASE WHEN vcs.credit_status = 3 THEN tf.id END) * 100.0 / COUNT(DISTINCT tf.id), 2) END AS creditRate FROM village_credit_survey vcs INNER JOIN t_farmer tf ON vcs.customer_id = tf.id AND tf.deleted = 0 LEFT JOIN rural_grid_area rga ON tf.village_id = rga.area_id AND rga.area_type = 3 AND rga.del_flag = '0' WHERE vcs.del_flag = 0 <iftest="batchId != null"> AND vcs.batch_id = #{batchId} </if> <iftest="orgId != null"> AND (rga.belong_org_id = #{orgId} OR tf.org_id = #{orgId}) </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND (rga.belong_org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> OR tf.org_id IN <foreachcollection="orgIds"item="orgIdItem2"open="("separator=","close=")"> #{orgIdItem2} </foreach> ) </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null"> AND EXISTS (SELECT 1 FROM t_farmer tfm WHERE tfm.village_id = rga.area_id AND tfm.deleted = 0 AND tfm.manager_id = #{managerId}) </if> GROUP BY rga.area_id, rga.area_name ORDER BY creditRate DESC LIMIT 10 </select>
<selectid="selectCreditAmountDist"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="com.ruoyi.mobile.vo.VillageCreditBoardVO$CreditAmountDistVO"> SELECT t.name AS name, COUNT(vcs.suggest_credit_amount) AS value FROM ( SELECT '5万以下' AS name, 0 AS min_val, 5 AS max_val UNION ALL SELECT '5-10万', 5, 10 UNION ALL SELECT '10-20万', 10, 20 UNION ALL SELECT '20-30万', 20, 30 UNION ALL SELECT '30万以上', 30, 999999 ) t LEFT JOIN ( SELECT vcs.suggest_credit_amount FROM village_credit_survey vcs LEFT JOIN t_farmer tf ON vcs.customer_id = tf.id WHERE vcs.credit_status = 3 AND vcs.del_flag = 0 AND tf.deleted = 0 <iftest="batchId != null"> AND vcs.batch_id = #{batchId} </if> <iftest="orgId != null"> AND tf.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND tf.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null"> AND tf.manager_id = #{managerId} </if> ) vcs ON vcs.suggest_credit_amount >= t.min_val AND vcs.suggest_credit_amount < t.max_val GROUP BY t.name, t.min_val ORDER BY t.min_val </select>
<selectid="selectCreditTrend"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="java.util.Map"> SELECT GROUP_CONCAT(t.month_label ORDER BY t.month_order) AS monthList, GROUP_CONCAT(COALESCE(stats.credit_num, 0) ORDER BY t.month_order) AS creditNumList, GROUP_CONCAT(COALESCE(stats.credit_amount, 0) ORDER BY t.month_order) AS creditAmountList FROM ( SELECT 1 AS month_order, '1月' AS month_label UNION ALL SELECT 2, '2月' UNION ALL SELECT 3, '3月' UNION ALL SELECT 4, '4月' UNION ALL SELECT 5, '5月' UNION ALL SELECT 6, '6月' UNION ALL SELECT 7, '7月' UNION ALL SELECT 8, '8月' UNION ALL SELECT 9, '9月' UNION ALL SELECT 10, '10月' UNION ALL SELECT 11, '11月' UNION ALL SELECT 12, '12月' ) t LEFT JOIN ( SELECT stats_month.period_key, COUNT(*) AS credit_num, COALESCE(SUM(stats_month.total_amt), 0) AS credit_amount FROM ( SELECT MONTH(vcs.survey_time) AS period_key, vcs.customer_id, SUM(vcs.suggest_credit_amount) AS total_amt FROM village_credit_survey vcs INNER JOIN t_farmer tf ON vcs.customer_id = tf.id AND tf.deleted = 0 WHERE vcs.credit_status = 3 AND vcs.del_flag = 0 <iftest="batchId != null"> AND vcs.batch_id = #{batchId} </if> <iftest="orgId != null"> AND tf.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND tf.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null"> AND tf.manager_id = #{managerId} </if> <iftest="periodValue != null"> AND YEAR(vcs.survey_time) = SUBSTRING(#{periodValue}, 1, 4) </if> GROUP BY MONTH(vcs.survey_time), vcs.customer_id ) stats_month GROUP BY stats_month.period_key ) stats ON t.month_order = stats.period_key </select>
<selectid="selectVillageCreditDetailList"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="com.ruoyi.mobile.vo.VillageCreditBoardVO$VillageCreditDetailVO"> SELECT bo.org_name AS orgName, rga.area_name AS villageName, COUNT(DISTINCT tf.id) AS totalHousehold, COUNT(DISTINCT vcs_survey.id) AS surveyNum, COUNT(DISTINCT CASE WHEN vcs_survey.credit_status IN (2, 3) THEN vcs_survey.id END) AS preCreditNum, COUNT(DISTINCT CASE WHEN vcs_survey.credit_status = 3 THEN tf.id END) AS creditedNum, COALESCE(SUM(CASE WHEN vcs_survey.credit_status = 3 THEN vcs_survey.suggest_credit_amount END), 0) AS creditAmount, CASE WHEN COUNT(DISTINCT tf.id) = 0 THEN 0 ELSE ROUND(COUNT(DISTINCT CASE WHEN vcs_survey.credit_status = 3 THEN tf.id END) * 100.0 / COUNT(DISTINCT tf.id), 2) END AS creditRate FROM village_credit_survey vcs_survey INNER JOIN t_farmer tf ON vcs_survey.customer_id = tf.id AND tf.deleted = 0 LEFT JOIN rural_grid_area rga ON tf.village_id = rga.area_id AND rga.area_type = 3 AND rga.del_flag = '0' LEFT JOIN rural_grid_area rga_town ON rga.parent_id = rga_town.area_id AND rga_town.del_flag = '0' LEFT JOIN bank_organization bo ON COALESCE(rga_town.belong_org_id, rga.belong_org_id) = bo.org_id AND bo.del_flag = '0' WHERE vcs_survey.del_flag = 0 <iftest="batchId != null"> AND vcs_survey.batch_id = #{batchId} </if> <iftest="orgId != null"> AND (rga.belong_org_id = #{orgId} OR tf.org_id = #{orgId}) </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND (rga.belong_org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> OR tf.org_id IN <foreachcollection="orgIds"item="orgIdItem2"open="("separator=","close=")"> #{orgIdItem2} </foreach> ) </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null"> AND EXISTS (SELECT 1 FROM t_farmer tfm WHERE tfm.village_id = rga.area_id AND tfm.deleted = 0 AND tfm.manager_id = #{managerId}) </if> GROUP BY rga.area_id, rga.area_name, bo.org_name ORDER BY creditRate DESC LIMIT #{offset}, #{limit} </select>
<selectid="selectVillageCreditDetailCount"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="Long"> SELECT COUNT(DISTINCT rga.area_id) FROM village_credit_survey vcs INNER JOIN t_farmer tf ON vcs.customer_id = tf.id AND tf.deleted = 0 LEFT JOIN rural_grid_area rga ON tf.village_id = rga.area_id AND rga.area_type = 3 AND rga.del_flag = '0' WHERE vcs.del_flag = 0 <iftest="batchId != null"> AND vcs.batch_id = #{batchId} </if> <iftest="orgId != null"> AND (rga.belong_org_id = #{orgId} OR tf.org_id = #{orgId}) </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND (rga.belong_org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> OR tf.org_id IN <foreachcollection="orgIds"item="orgIdItem2"open="("separator=","close=")"> #{orgIdItem2} </foreach> ) </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null"> AND EXISTS (SELECT 1 FROM t_farmer tfm WHERE tfm.village_id = rga.area_id AND tfm.deleted = 0 AND tfm.manager_id = #{managerId}) </if> </select>
<selectid="selectCustomerMarketingBoardData"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="com.ruoyi.mobile.vo.CustomerMarketingBoardVO$BoardData"> SELECT (SELECT COUNT(*) FROM marketing_task mt WHERE mt.del_flag = '0' <iftest="orgId != null"> AND mt.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND mt.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null">AND mt.manager_id = #{managerId}</if> ) AS totalTask, (SELECT COUNT(*) FROM marketing_task mt WHERE mt.del_flag = '0' AND mt.task_status = '2' <iftest="orgId != null"> AND mt.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND mt.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null">AND mt.manager_id = #{managerId}</if> ) AS completedTask, (SELECT COUNT(DISTINCT mt.customer_id) FROM marketing_exec_record mer LEFT JOIN marketing_task mt ON mer.task_id = mt.id AND mt.del_flag = '0' WHERE mer.del_flag = '0' <iftest="orgId != null"> AND mt.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND mt.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null">AND mt.manager_id = #{managerId}</if> ) AS totalVisit, (SELECT COUNT(*) FROM t_farmer_call tfc LEFT JOIN marketing_task mt ON tfc.task_id = mt.id AND mt.del_flag = '0' WHERE tfc.deleted = 0 <iftest="orgId != null"> AND mt.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND mt.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null">AND mt.manager_id = #{managerId}</if> ) AS totalCall </select>
<selectid="selectTaskTypeDist"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="com.ruoyi.mobile.vo.CustomerMarketingBoardVO$TaskTypeDistVO"> SELECT mtt.type_name AS name, COUNT(mt.id) AS value FROM marketing_task_type mtt LEFT JOIN marketing_task mt ON mt.type_id = mtt.id AND mt.del_flag = '0' <iftest="orgId != null"> AND mt.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND mt.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null">AND mt.manager_id = #{managerId}</if> WHERE mtt.status = 0 GROUP BY mtt.id, mtt.type_name ORDER BY value DESC </select>
<selectid="selectMarketingTrend"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="java.util.Map"> SELECT GROUP_CONCAT(t.month_label ORDER BY t.month_order) AS months, GROUP_CONCAT(COALESCE(stats.visit_num, 0) ORDER BY t.month_order) AS visit_num, GROUP_CONCAT(COALESCE(stats.call_num, 0) ORDER BY t.month_order) AS call_num FROM ( SELECT 1 AS month_order, '1月' AS month_label UNION ALL SELECT 2, '2月' UNION ALL SELECT 3, '3月' UNION ALL SELECT 4, '4月' UNION ALL SELECT 5, '5月' UNION ALL SELECT 6, '6月' UNION ALL SELECT 7, '7月' UNION ALL SELECT 8, '8月' UNION ALL SELECT 9, '9月' UNION ALL SELECT 10, '10月' UNION ALL SELECT 11, '11月' UNION ALL SELECT 12, '12月' ) t LEFT JOIN ( SELECT period_key, SUM(visit_num) AS visit_num, SUM(call_num) AS call_num FROM ( SELECT MONTH(mer.create_time) AS period_key, COUNT(DISTINCT mt.customer_id) AS visit_num, 0 AS call_num FROM marketing_exec_record mer LEFT JOIN marketing_task mt ON mer.task_id = mt.id AND mt.del_flag = '0' WHERE mer.del_flag = '0' <iftest="orgId != null"> AND mt.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND mt.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null">AND mt.manager_id = #{managerId}</if> <iftest="periodValue != null">AND YEAR(mer.create_time) = SUBSTRING(#{periodValue}, 1, 4)</if> GROUP BY MONTH(mer.create_time) UNION ALL SELECT MONTH(c.create_time) AS period_key, 0 AS visit_num, COUNT(*) AS call_num FROM t_farmer_call c LEFT JOIN marketing_task mt ON c.task_id = mt.id AND mt.del_flag = '0' WHERE c.deleted = 0 <iftest="orgId != null"> AND mt.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND mt.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null">AND mt.manager_id = #{managerId}</if> <iftest="periodValue != null">AND YEAR(c.create_time) = SUBSTRING(#{periodValue}, 1, 4)</if> GROUP BY MONTH(c.create_time) ) union_stats GROUP BY period_key ) stats ON t.month_order = stats.period_key </select>
<selectid="selectManagerListByOrg"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="com.ruoyi.mobile.vo.CustomerMarketingBoardVO$ManagerSimpleVO"> SELECT DISTINCT su.user_id AS userId, su.nick_name AS nickName FROM sys_user su INNER JOIN sys_user_role sur ON su.user_id = sur.user_id WHERE sur.role_id = 2008 AND su.del_flag = '0' <iftest="orgId != null"> AND su.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND su.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> ORDER BY su.nick_name </select>
<selectid="selectMarketingTaskDetailList"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="com.ruoyi.mobile.vo.CustomerMarketingBoardVO$MarketingTaskDetailVO"> SELECT mt.task_name AS taskName, mtt.type_name AS typeName, mt.org_name AS orgName, mt.manager_name AS managerName, COALESCE(mt.customer_name, mtc_f.customer_name) AS customerNames, (SELECT COUNT(*) FROM marketing_exec_record mer WHERE mer.del_flag = '0' AND mer.task_id = mt.id ) AS visitNum, (SELECT COUNT(*) FROM t_farmer_call tfc WHERE tfc.deleted = 0 AND tfc.task_id = mt.id ) AS callNum, CASE WHEN mt.task_status = '2' THEN 100 ELSE 0 END AS completionRate, CASE mt.task_status WHEN '0' THEN 0 WHEN '1' THEN 1 WHEN '2' THEN 2 ELSE 3 END AS status FROM marketing_task mt LEFT JOIN marketing_task_type mtt ON mt.type_id = mtt.id LEFT JOIN marketing_task_customer mtc ON mt.id = mtc.task_id AND mtc.del_flag = 0 LEFT JOIN t_farmer mtc_f ON mtc.customer_id = mtc_f.id WHERE mt.del_flag = '0' <iftest="orgId != null"> AND mt.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND mt.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null">AND mt.manager_id = #{managerId}</if> GROUP BY mt.id, mt.task_name, mtt.type_name, mt.org_name, mt.manager_name, mt.manager_id, mt.task_status ORDER BY mt.create_time DESC LIMIT #{offset}, #{limit} </select>
<selectid="selectMarketingTaskDetailCount"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="Long"> SELECT COUNT(*) FROM marketing_task mt WHERE mt.del_flag = '0' <iftest="orgId != null"> AND mt.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND mt.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null">AND mt.manager_id = #{managerId}</if> </select>
<selectid="selectManagerPerfSummary"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="com.ruoyi.mobile.vo.ManagerPerfReportVO$SummaryData"> SELECT (SELECT COUNT(DISTINCT su.user_id) FROM sys_user su INNER JOIN sys_user_role sur ON su.user_id = sur.user_id WHERE sur.role_id = 2008 AND su.del_flag = '0' <iftest="orgId != null"> AND su.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND su.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> ) AS managerCount, (SELECT COUNT(*) FROM t_farmer tf WHERE tf.deleted = 0 <iftest="orgId != null"> AND tf.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND tf.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null">AND tf.manager_id = #{managerId}</if> ) AS totalCustomer, (SELECT COUNT(*) FROM marketing_task mt WHERE mt.del_flag = '0' AND mt.task_status = '2' <iftest="orgId != null"> AND mt.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND mt.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null">AND mt.manager_id = #{managerId}</if> ) AS completedTask, COALESCE((SELECT SUM(vcs.suggest_credit_amount) FROM village_credit_survey vcs LEFT JOIN t_farmer tf ON vcs.customer_id = tf.id WHERE vcs.credit_status = 3 AND vcs.del_flag = 0 AND tf.deleted = 0 <iftest="orgId != null"> AND tf.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND tf.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null">AND tf.manager_id = #{managerId}</if> ), 0) AS totalCreditAmount </select>
<selectid="selectManagerPerfDetailList"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="com.ruoyi.mobile.vo.ManagerPerfReportVO$ManagerPerfDetailVO"> SELECT bo.org_name AS orgName, su.nick_name AS managerName, COALESCE(customer_stats.customer_count, 0) AS customerCount, COALESCE(customer_stats.valid_customer_count, 0) AS validCustomerCount, COALESCE(credit_stats.survey_count, 0) AS surveyCount, COALESCE(credit_stats.credit_count, 0) AS creditCount, COALESCE(credit_stats.credit_amount, 0) AS creditAmount, COALESCE(task_stats.task_count, 0) AS taskCount, COALESCE(task_stats.task_completed, 0) AS taskCompleted, CASE WHEN COALESCE(task_stats.task_count, 0) = 0 THEN 0 ELSE ROUND(COALESCE(task_stats.task_completed, 0) * 100.0 / task_stats.task_count, 2) END AS taskRate, COALESCE(visit_stats.visit_count, 0) AS visitCount, COALESCE(visit_stats.visit_times, 0) AS visitTimes, COALESCE(call_stats.call_count, 0) AS callCount, COALESCE(call_stats.call_total_duration, 0) AS callTotalDuration FROM sys_user su INNER JOIN sys_user_role sur ON su.user_id = sur.user_id LEFT JOIN bank_organization bo ON su.org_id = bo.org_id AND bo.del_flag = '0' LEFT JOIN ( SELECT tf.manager_id, COUNT(DISTINCT tf.id) AS customer_count, COUNT(DISTINCT CASE WHEN vcs.credit_status = 3 THEN tf.id END) AS valid_customer_count FROM t_farmer tf LEFT JOIN village_credit_survey vcs ON tf.id = vcs.customer_id AND vcs.del_flag = 0 WHERE tf.deleted = 0 GROUP BY tf.manager_id ) customer_stats ON su.user_id = customer_stats.manager_id LEFT JOIN ( SELECT vcs.survey_user_id AS manager_id, COUNT(*) AS survey_count, SUM(CASE WHEN vcs.credit_status = 3 THEN 1 ELSE 0 END) AS credit_count, COALESCE(SUM(CASE WHEN vcs.credit_status = 3 THEN vcs.suggest_credit_amount END), 0) AS credit_amount FROM village_credit_survey vcs WHERE vcs.del_flag = 0 GROUP BY vcs.survey_user_id ) credit_stats ON su.user_id = credit_stats.manager_id LEFT JOIN ( SELECT mt.manager_id, COUNT(*) AS task_count, SUM(CASE WHEN mt.task_status = '2' THEN 1 ELSE 0 END) AS task_completed FROM marketing_task mt WHERE mt.del_flag = '0' GROUP BY mt.manager_id ) task_stats ON su.user_id = task_stats.manager_id LEFT JOIN ( SELECT mt.manager_id, COUNT(DISTINCT mt.customer_id) AS visit_count, COUNT(mer.id) AS visit_times FROM marketing_exec_record mer LEFT JOIN marketing_task mt ON mer.task_id = mt.id AND mt.del_flag = '0' WHERE mer.del_flag = '0' GROUP BY mt.manager_id ) visit_stats ON su.user_id = visit_stats.manager_id LEFT JOIN ( SELECT mt.manager_id, COUNT(*) AS call_count, COALESCE(SUM(tfc.duration_min), 0) AS call_total_duration FROM t_farmer_call tfc LEFT JOIN marketing_task mt ON tfc.task_id = mt.id AND mt.del_flag = '0' WHERE tfc.deleted = 0 GROUP BY mt.manager_id ) call_stats ON su.user_id = call_stats.manager_id WHERE sur.role_id = 2008 AND su.del_flag = '0' <iftest="orgId != null"> AND su.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND su.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null"> AND su.user_id = #{managerId} </if> ORDER BY su.user_id DESC LIMIT #{offset}, #{limit} </select>
<selectid="selectManagerPerfDetailCount"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="Long"> SELECT COUNT(*) FROM sys_user su INNER JOIN sys_user_role sur ON su.user_id = sur.user_id WHERE sur.role_id = 2008 AND su.del_flag = '0' <iftest="orgId != null"> AND su.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND su.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="managerId != null">AND su.user_id = #{managerId}</if> </select>
<selectid="selectOrgSummary"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="com.ruoyi.mobile.vo.OrgPeriodSummaryReportVO$SummaryData"> SELECT (SELECT COUNT(*) FROM bank_organization bo WHERE bo.del_flag = '0' AND bo.org_type = 2 <iftest="orgId != null"> AND bo.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND bo.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> ) AS orgCount, (SELECT COUNT(DISTINCT tf.id) FROM t_farmer tf LEFT JOIN village_credit_survey vcs ON tf.id = vcs.customer_id AND vcs.del_flag = 0 WHERE tf.deleted = 0 AND vcs.credit_status = 3 <iftest="orgId != null"> AND tf.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND tf.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> ) AS totalCreditCount, COALESCE((SELECT SUM(vcs.suggest_credit_amount) FROM village_credit_survey vcs LEFT JOIN t_farmer tf ON vcs.customer_id = tf.id WHERE vcs.credit_status = 3 AND vcs.del_flag = 0 AND tf.deleted = 0 <iftest="orgId != null"> AND tf.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND tf.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> ), 0) AS totalCreditAmount, 0 AS avgCreditAmount </select>
<selectid="selectOrgSummaryDetailList"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="com.ruoyi.mobile.vo.OrgPeriodSummaryReportVO$OrgSummaryDetailVO"> SELECT bo.org_name AS orgName, COALESCE(manager_stats.manager_count, 0) AS managerCount, COALESCE(village_stats.village_count, 0) AS villageCount, COALESCE(customer_stats.total_customer, 0) AS totalCustomer, COALESCE(customer_stats.valid_customer_count, 0) AS validCustomerCount, COALESCE(credit_stats.survey_count, 0) AS surveyCount, COALESCE(credit_stats.credit_count, 0) AS creditCount, COALESCE(credit_stats.credit_amount, 0) AS creditAmount, CASE WHEN COALESCE(customer_stats.total_customer, 0) = 0 THEN 0 ELSE ROUND(COALESCE(credit_stats.credit_count, 0) * 100.0 / customer_stats.total_customer, 2) END AS creditRate, COALESCE(task_stats.task_count, 0) AS taskCount, COALESCE(task_stats.task_completed, 0) AS taskCompleted, CASE WHEN COALESCE(task_stats.task_count, 0) = 0 THEN 0 ELSE ROUND(COALESCE(task_stats.task_completed, 0) * 100.0 / task_stats.task_count, 2) END AS taskRate, COALESCE(visit_stats.visit_count, 0) AS visitCount, COALESCE(visit_stats.visit_times, 0) AS visitTimes, COALESCE(call_stats.call_count, 0) AS callCount, COALESCE(call_stats.call_total_duration, 0) AS callTotalDuration FROM bank_organization bo LEFT JOIN ( SELECT su.org_id, COUNT(*) AS manager_count FROM sys_user su INNER JOIN sys_user_role sur ON su.user_id = sur.user_id WHERE sur.role_id = 2008 AND su.del_flag = '0' GROUP BY su.org_id ) manager_stats ON bo.org_id = manager_stats.org_id LEFT JOIN ( SELECT rga.belong_org_id AS dept_id, COUNT(*) AS village_count FROM rural_grid_area rga WHERE rga.area_type = 3 AND rga.del_flag = '0' GROUP BY rga.belong_org_id ) village_stats ON bo.org_id = village_stats.dept_id LEFT JOIN ( SELECT tf.org_id AS dept_id, COUNT(DISTINCT tf.id) AS total_customer, COUNT(DISTINCT CASE WHEN vcs.credit_status = 3 THEN tf.id END) AS valid_customer_count FROM t_farmer tf LEFT JOIN village_credit_survey vcs ON tf.id = vcs.customer_id AND vcs.del_flag = 0 WHERE tf.deleted = 0 GROUP BY tf.org_id ) customer_stats ON bo.org_id = customer_stats.dept_id LEFT JOIN ( SELECT tf.org_id AS dept_id, COUNT(vcs.id) AS survey_count, SUM(CASE WHEN vcs.credit_status = 3 THEN 1 ELSE 0 END) AS credit_count, COALESCE(SUM(CASE WHEN vcs.credit_status = 3 THEN vcs.suggest_credit_amount END), 0) AS credit_amount FROM village_credit_survey vcs LEFT JOIN t_farmer tf ON vcs.customer_id = tf.id WHERE vcs.del_flag = 0 AND tf.deleted = 0 GROUP BY tf.org_id ) credit_stats ON bo.org_id = credit_stats.dept_id LEFT JOIN ( SELECT mt.org_id AS dept_id, COUNT(*) AS task_count, SUM(CASE WHEN mt.task_status = '2' THEN 1 ELSE 0 END) AS task_completed FROM marketing_task mt WHERE mt.del_flag = '0' GROUP BY mt.org_id ) task_stats ON bo.org_id = task_stats.dept_id LEFT JOIN ( SELECT mt.org_id AS dept_id, COUNT(DISTINCT mt.customer_id) AS visit_count, COUNT(mer.id) AS visit_times FROM marketing_exec_record mer LEFT JOIN marketing_task mt ON mer.task_id = mt.id AND mt.del_flag = '0' WHERE mer.del_flag = '0' GROUP BY mt.org_id ) visit_stats ON bo.org_id = visit_stats.dept_id LEFT JOIN ( SELECT mt.org_id AS dept_id, COUNT(*) AS call_count, COALESCE(SUM(tfc.duration_min), 0) AS call_total_duration FROM t_farmer_call tfc LEFT JOIN marketing_task mt ON tfc.task_id = mt.id AND mt.del_flag = '0' WHERE tfc.deleted = 0 GROUP BY mt.org_id ) call_stats ON bo.org_id = call_stats.dept_id WHERE bo.org_type = 2 AND bo.del_flag = '0' <iftest="orgId != null"> AND bo.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND bo.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="orgName != null and orgName != ''"> AND bo.org_name LIKE CONCAT('%', #{orgName}, '%') </if> ORDER BY bo.org_id LIMIT #{offset}, #{limit} </select>
<selectid="selectOrgSummaryDetailCount"parameterType="com.ruoyi.mobile.dto.ReportQueryDTO"resultType="Long"> SELECT COUNT(*) FROM bank_organization bo WHERE bo.org_type = 2 AND bo.del_flag = '0' <iftest="orgId != null"> AND bo.org_id = #{orgId} </if> <iftest="orgIds != null"> <iftest="orgIds.size() > 0"> AND bo.org_id IN <foreachcollection="orgIds"item="orgIdItem"open="("separator=","close=")"> #{orgIdItem} </foreach> </if> <iftest="orgIds.size() == 0"> AND 1 = 0 </if> </if> <iftest="orgName != null and orgName != ''">AND bo.org_name LIKE CONCAT('%', #{orgName}, '%')</if> </select> </mapper>
<select id="selectVillageCreditBoardData" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="com.ruoyi.mobile.vo.VillageCreditBoardVO$BoardData"> SELECT COUNT(DISTINCT rga.area_id) AS totalVillage, COUNT(DISTINCT tf.id) AS totalHousehold, COUNT(DISTINCT CASE WHEN vcs.credit_status = 3 THEN tf.id END) AS creditedHousehold, COALESCE(SUM(CASE WHEN vcs.credit_status = 3 THEN vcs.suggest_credit_amount END), 0) AS totalCreditAmount FROM village_credit_survey vcs INNER JOIN t_farmer tf ON vcs.customer_id = tf.id AND tf.deleted = 0 LEFT JOIN rural_grid_area rga ON tf.village_id = rga.area_id AND rga.area_type = 3 AND rga.del_flag = '0' WHERE vcs.del_flag = 0 <if test="batchId != null"> AND vcs.batch_id = #{batchId} AND EXISTS(SELECT 1 FROM credit_batch cb WHERE cb.id = #{batchId} AND FIND_IN_SET(tf.village_id, cb.village_ids) > 0) </if> <if test="periodValue != null and periodType == 'month'"> AND DATE_FORMAT(vcs.survey_time, '%Y-%m') = #{periodValue} </if> <if test="periodValue != null and periodType == 'quarter'"> AND YEAR(vcs.survey_time) = SUBSTRING(#{periodValue}, 1, 4) AND QUARTER(vcs.survey_time) = SUBSTRING(#{periodValue}, 6, 1) </if> <if test="periodValue != null and periodType == 'year'"> AND YEAR(vcs.survey_time) = #{periodValue} </if> <if test="orgId != null"> AND tf.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND tf.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null"> AND tf.manager_id = #{managerId} </if> </select>
<select id="selectCreditRateRank" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="com.ruoyi.mobile.vo.VillageCreditBoardVO$VillageCreditRankVO"> SELECT rga.area_name AS villageName, CASE WHEN COUNT(DISTINCT tf.id) = 0 THEN 0 ELSE ROUND(COUNT(DISTINCT CASE WHEN vcs.credit_status = 3 THEN tf.id END) * 100.0 / COUNT(DISTINCT tf.id), 2) END AS creditRate FROM village_credit_survey vcs INNER JOIN t_farmer tf ON vcs.customer_id = tf.id AND tf.deleted = 0 LEFT JOIN rural_grid_area rga ON tf.village_id = rga.area_id AND rga.area_type = 3 AND rga.del_flag = '0' WHERE vcs.del_flag = 0 <if test="batchId != null"> AND vcs.batch_id = #{batchId} AND EXISTS(SELECT 1 FROM credit_batch cb WHERE cb.id = #{batchId} AND FIND_IN_SET(tf.village_id, cb.village_ids) > 0) </if> <if test="orgId != null"> AND (rga.belong_org_id = #{orgId} OR tf.org_id = #{orgId}) </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND (rga.belong_org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> OR tf.org_id IN <foreach collection="orgIds" item="orgIdItem2" open="(" separator="," close=")"> #{orgIdItem2} </foreach> ) </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null"> AND EXISTS(SELECT 1 FROM t_farmer tfm WHERE tfm.village_id = rga.area_id AND tfm.deleted = 0 AND tfm.manager_id = #{managerId}) </if> GROUP BY rga.area_id, rga.area_name ORDER BY creditRate DESC LIMIT 10 </select>
<select id="selectCreditAmountDist" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="com.ruoyi.mobile.vo.VillageCreditBoardVO$CreditAmountDistVO"> SELECT t.name AS name, COUNT(vcs.suggest_credit_amount) AS value FROM( SELECT '5万以下' AS name, 0 AS min_val, 5 AS max_val UNION ALL SELECT '5-10万', 5, 10 UNION ALL SELECT '10-20万', 10, 20 UNION ALL SELECT '20-30万', 20, 30 UNION ALL SELECT '30万以上', 30, 999999 ) t LEFT JOIN( SELECT vcs.suggest_credit_amount FROM village_credit_survey vcs LEFT JOIN t_farmer tf ON vcs.customer_id = tf.id WHERE vcs.credit_status = 3 AND vcs.del_flag = 0 AND tf.deleted = 0 <if test="batchId != null"> AND vcs.batch_id = #{batchId} AND EXISTS (SELECT 1 FROM credit_batch cb WHERE cb.id = #{batchId} AND FIND_IN_SET(tf.village_id, cb.village_ids) > 0) </if> <if test="orgId != null"> AND tf.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND tf.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null"> AND tf.manager_id = #{managerId} </if> ) vcs ON vcs.suggest_credit_amount >= t.min_val AND vcs.suggest_credit_amount < t.max_val GROUP BY t.name, t.min_val ORDER BY t.min_val </select>
<select id="selectCreditTrend" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="java.util.Map"> SELECT GROUP_CONCAT(t.month_label ORDER BY t.month_order) AS monthList, GROUP_CONCAT(COALESCE(stats.credit_num, 0) ORDER BY t.month_order) AS creditNumList, GROUP_CONCAT(COALESCE(stats.credit_amount, 0) ORDER BY t.month_order) AS creditAmountList FROM( SELECT 1 AS month_order, '1月' AS month_label UNION ALL SELECT 2, '2月' UNION ALL SELECT 3, '3月' UNION ALL SELECT 4, '4月' UNION ALL SELECT 5, '5月' UNION ALL SELECT 6, '6月' UNION ALL SELECT 7, '7月' UNION ALL SELECT 8, '8月' UNION ALL SELECT 9, '9月' UNION ALL SELECT 10, '10月' UNION ALL SELECT 11, '11月' UNION ALL SELECT 12, '12月' ) t LEFT JOIN( SELECT MONTH(vcs.survey_time) AS period_key, COUNT(vcs.id) AS credit_num, COALESCE(SUM(vcs.suggest_credit_amount), 0) AS credit_amount FROM village_credit_survey vcs LEFT JOIN t_farmer tf ON vcs.customer_id = tf.id WHERE vcs.credit_status = 3 AND vcs.del_flag = 0 AND tf.deleted = 0 <if test="batchId != null"> AND vcs.batch_id = #{batchId} AND EXISTS(SELECT 1 FROM credit_batch cb WHERE cb.id = #{batchId} AND FIND_IN_SET(tf.village_id, cb.village_ids) > 0) </if> <if test="orgId != null"> AND tf.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND tf.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null"> AND tf.manager_id = #{managerId} </if> <if test="periodValue != null"> AND YEAR(vcs.survey_time) = SUBSTRING(#{periodValue}, 1, 4) </if> GROUP BY MONTH(vcs.survey_time) ) stats ON t.month_order = stats.period_key </select>
<select id="selectVillageCreditDetailList" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="com.ruoyi.mobile.vo.VillageCreditBoardVO$VillageCreditDetailVO"> SELECT bo.org_name AS orgName, rga.area_name AS villageName, COUNT(DISTINCT tf.id) AS totalHousehold, COUNT(DISTINCT vcs_survey.id) AS surveyNum, COUNT(DISTINCT CASE WHEN vcs_survey.credit_status IN(2, 3) THEN vcs_survey.id END) AS preCreditNum, COUNT(DISTINCT CASE WHEN vcs_survey.credit_status = 3 THEN vcs_survey.id END) AS creditedNum, COALESCE(SUM(CASE WHEN vcs_survey.credit_status = 3 THEN vcs_survey.suggest_credit_amount END), 0) AS creditAmount, CASE WHEN COUNT(DISTINCT tf.id) = 0 THEN 0 ELSE ROUND(COUNT(DISTINCT CASE WHEN vcs_survey.credit_status = 3 THEN tf.id END) * 100.0 / COUNT(DISTINCT tf.id), 2) END AS creditRate FROM village_credit_survey vcs_survey INNER JOIN t_farmer tf ON vcs_survey.customer_id = tf.id AND tf.deleted = 0 LEFT JOIN rural_grid_area rga ON tf.village_id = rga.area_id AND rga.area_type = 3 AND rga.del_flag = '0' LEFT JOIN rural_grid_area rga_town ON rga.parent_id = rga_town.area_id AND rga_town.del_flag = '0' LEFT JOIN bank_organization bo ON COALESCE(rga_town.belong_org_id, rga.belong_org_id) = bo.org_id AND bo.del_flag = '0' WHERE vcs_survey.del_flag = 0 <if test="batchId != null"> AND vcs_survey.batch_id = #{batchId} AND EXISTS(SELECT 1 FROM credit_batch cb WHERE cb.id = #{batchId} AND FIND_IN_SET(tf.village_id, cb.village_ids) > 0) </if> <if test="orgId != null"> AND (rga.belong_org_id = #{orgId} OR tf.org_id = #{orgId}) </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND (rga.belong_org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> OR tf.org_id IN <foreach collection="orgIds" item="orgIdItem2" open="(" separator="," close=")"> #{orgIdItem2} </foreach> ) </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null"> AND EXISTS(SELECT 1 FROM t_farmer tfm WHERE tfm.village_id = rga.area_id AND tfm.deleted = 0 AND tfm.manager_id = #{managerId}) </if> GROUP BY rga.area_id, rga.area_name, bo.org_name ORDER BY bo.org_name LIMIT #{offset}, #{limit} </select>
<select id="selectVillageCreditDetailCount" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="Long"> SELECT COUNT(DISTINCT rga.area_id) FROM village_credit_survey vcs INNER JOIN t_farmer tf ON vcs.customer_id = tf.id AND tf.deleted = 0 LEFT JOIN rural_grid_area rga ON tf.village_id = rga.area_id AND rga.area_type = 3 AND rga.del_flag = '0' WHERE vcs.del_flag = 0 <if test="batchId != null"> AND vcs.batch_id = #{batchId} AND EXISTS(SELECT 1 FROM credit_batch cb WHERE cb.id = #{batchId} AND FIND_IN_SET(tf.village_id, cb.village_ids) > 0) </if> <if test="orgId != null"> AND (rga.belong_org_id = #{orgId} OR tf.org_id = #{orgId}) </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND (rga.belong_org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> OR tf.org_id IN <foreach collection="orgIds" item="orgIdItem2" open="(" separator="," close=")"> #{orgIdItem2} </foreach> ) </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null"> AND EXISTS(SELECT 1 FROM t_farmer tfm WHERE tfm.village_id = rga.area_id AND tfm.deleted = 0 AND tfm.manager_id = #{managerId}) </if> </select>
<select id="selectCustomerMarketingBoardData" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="com.ruoyi.mobile.vo.CustomerMarketingBoardVO$BoardData"> SELECT (SELECT COUNT(*) FROM marketing_task mt WHERE mt.del_flag = '0' <if test="orgId != null"> AND mt.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND mt.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null">AND mt.manager_id = #{managerId}</if> ) AS totalTask, (SELECT COUNT(*) FROM marketing_task mt WHERE mt.del_flag = '0' AND mt.task_status = '2' <if test="orgId != null"> AND mt.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND mt.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null">AND mt.manager_id = #{managerId}</if> ) AS completedTask, (SELECT COUNT(DISTINCT mt.customer_id) FROM marketing_exec_recordmer LEFT JOIN marketing_task mt ON mer.task_id = mt.id AND mt.del_flag = '0' WHERE mer.del_flag = '0' <if test="orgId != null"> AND mt.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND mt.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null">AND mt.manager_id = #{managerId}</if> ) AS totalVisit, (SELECT COUNT(*) FROM t_farmer_call tfc LEFT JOIN marketing_task mt ON tfc.task_id = mt.id AND mt.del_flag = '0' WHERE tfc.deleted = 0 <if test="orgId != null"> AND mt.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND mt.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null">AND mt.manager_id = #{managerId}</if> ) AS totalCall </select>
<select id="selectTaskTypeDist" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="com.ruoyi.mobile.vo.CustomerMarketingBoardVO$TaskTypeDistVO"> SELECT mtt.type_name AS name, COUNT(mt.id) AS value FROM marketing_task_type mtt LEFT JOIN marketing_task mt ON mt.type_id = mtt.id AND mt.del_flag = '0' <if test="orgId != null"> AND mt.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND mt.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null">AND mt.manager_id = #{managerId}</if> WHERE mtt.status = 0 GROUP BY mtt.id, mtt.type_name ORDER BY value DESC </select>
<select id="selectMarketingTrend" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="java.util.Map"> SELECT GROUP_CONCAT(t.month_label ORDER BY t.month_order) AS months, GROUP_CONCAT(COALESCE(stats.visit_num, 0) ORDER BY t.month_order) AS visit_num, GROUP_CONCAT(COALESCE(stats.call_num, 0) ORDER BY t.month_order) AS call_num FROM( SELECT 1 AS month_order, '1月' AS month_label UNION ALL SELECT 2, '2月' UNION ALL SELECT 3, '3月' UNION ALL SELECT 4, '4月' UNION ALL SELECT 5, '5月' UNION ALL SELECT 6, '6月' UNION ALL SELECT 7, '7月' UNION ALL SELECT 8, '8月' UNION ALL SELECT 9, '9月' UNION ALL SELECT 10, '10月' UNION ALL SELECT 11, '11月' UNION ALL SELECT 12, '12月' ) t LEFT JOIN( SELECT period_key, SUM(visit_num) AS visit_num, SUM(call_num) AS call_num FROM( SELECT MONTH(mer.create_time) AS period_key, COUNT(DISTINCT mt.customer_id) AS visit_num, 0 AS call_num FROM marketing_exec_recordmer LEFT JOIN marketing_task mt ON mer.task_id = mt.id AND mt.del_flag = '0' WHERE mer.del_flag = '0' <if test="orgId != null"> AND mt.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND mt.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null">AND mt.manager_id = #{managerId}</if> <if test="periodValue != null">AND YEAR(mer.create_time) = SUBSTRING(#{periodValue}, 1, 4)</if> GROUP BY MONTH(mer.create_time) UNION ALL SELECT MONTH(c.create_time) AS period_key, 0 AS visit_num, COUNT(*) AS call_num FROM t_farmer_call c LEFT JOIN marketing_task mt ON c.task_id = mt.id AND mt.del_flag = '0' WHERE c.deleted = 0 <if test="orgId != null"> AND mt.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND mt.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null">AND mt.manager_id = #{managerId}</if> <if test="periodValue != null">AND YEAR(c.create_time) = SUBSTRING(#{periodValue}, 1, 4)</if> GROUP BY MONTH(c.create_time) ) union_stats GROUP BY period_key ) stats ON t.month_order = stats.period_key </select>
<select id="selectManagerListByOrg" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="com.ruoyi.mobile.vo.CustomerMarketingBoardVO$ManagerSimpleVO"> SELECT DISTINCT su.user_id AS userId, su.nick_name AS nickName FROM sys_user su INNER JOIN sys_user_role sur ON su.user_id = sur.user_id WHERE sur.role_id = 2008 AND su.del_flag = '0' <if test="orgId != null"> AND su.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND su.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> ORDER BY su.nick_name </select>
<select id="selectMarketingTaskDetailList" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="com.ruoyi.mobile.vo.CustomerMarketingBoardVO$MarketingTaskDetailVO"> SELECT mt.task_name AS taskName, mtt.type_name AS typeName, mt.org_name AS orgName, mt.manager_name AS managerName, GROUP_CONCAT(DISTINCT COALESCE(mt.customer_name, mtc_f.customer_name) SEPARATOR ',') AS customerNames, (SELECT COUNT(*) FROM marketing_exec_recordmer WHERE mer.del_flag = '0' AND mer.task_id = mt.id ) AS visitNum, (SELECT COUNT(*) FROM t_farmer_call tfc WHERE tfc.deleted = 0 AND tfc.task_id = mt.id ) AS callNum, CASE WHEN mt.task_status = '2' THEN 100 ELSE 0 END AS completionRate, CASE mt.task_status WHEN '0' THEN 0 WHEN '1' THEN 1 WHEN '2' THEN 2 ELSE 3 END AS status FROM marketing_task mt LEFT JOIN marketing_task_type mtt ON mt.type_id = mtt.id LEFT JOIN marketing_task_customer mtc ON mt.id = mtc.task_id AND mtc.del_flag = 0 LEFT JOIN t_farmer mtc_f ON mtc.customer_id = mtc_f.id WHERE mt.del_flag = '0' <if test="orgId != null"> AND mt.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND mt.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null">AND mt.manager_id = #{managerId}</if> GROUP BY mt.id, mt.task_name, mtt.type_name, mt.org_name, mt.manager_name, mt.manager_id, mt.task_status ORDER BY mt.org_name, mt.manager_name LIMIT #{offset}, #{limit} </select>
<select id="selectMarketingTaskDetailCount" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="Long"> SELECT COUNT(*) FROM marketing_task mt WHERE mt.del_flag = '0' <if test="orgId != null"> AND mt.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND mt.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null">AND mt.manager_id = #{managerId}</if> </select>
<select id="selectManagerPerfSummary" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="com.ruoyi.mobile.vo.ManagerPerfReportVO$SummaryData"> SELECT (SELECT COUNT(DISTINCT su.user_id) FROM sys_user su INNER JOIN sys_user_role sur ON su.user_id = sur.user_id WHERE sur.role_id = 2008 AND su.del_flag = '0' <if test="orgId != null"> AND su.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND su.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> ) AS managerCount, (SELECT COUNT(*) FROM t_farmer tf WHERE tf.deleted = 0 <if test="orgId != null"> AND tf.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND tf.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null">AND tf.manager_id = #{managerId}</if> ) AS totalCustomer, (SELECT COUNT(*) FROM marketing_task mt WHERE mt.del_flag = '0' AND mt.task_status = '2' <if test="orgId != null"> AND mt.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND mt.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null">AND mt.manager_id = #{managerId}</if> ) AS completedTask, COALESCE((SELECT SUM(vcs.suggest_credit_amount) FROM village_credit_survey vcs LEFT JOIN t_farmer tf ON vcs.customer_id = tf.id WHERE vcs.credit_status = 3 AND vcs.del_flag = 0 AND tf.deleted = 0 <if test="orgId != null"> AND tf.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND tf.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null">AND tf.manager_id = #{managerId}</if> ), 0) AS totalCreditAmount </select>
<select id="selectManagerPerfDetailList" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="com.ruoyi.mobile.vo.ManagerPerfReportVO$ManagerPerfDetailVO"> SELECT bo.org_name AS orgName, su.nick_name AS managerName, COALESCE(customer_stats.customer_count, 0) AS customerCount, COALESCE(customer_stats.valid_customer_count, 0) AS validCustomerCount, COALESCE(credit_stats.survey_count, 0) AS surveyCount, COALESCE(credit_stats.credit_count, 0) AS creditCount, COALESCE(credit_stats.credit_amount, 0) AS creditAmount, COALESCE(task_stats.task_count, 0) AS taskCount, COALESCE(task_stats.task_completed, 0) AS taskCompleted, CASE WHEN COALESCE(task_stats.task_count, 0) = 0 THEN 0 ELSE ROUND(COALESCE(task_stats.task_completed, 0) * 100.0 / task_stats.task_count, 2) END AS taskRate, COALESCE(visit_stats.visit_count, 0) AS visitCount, COALESCE(visit_stats.visit_times, 0) AS visitTimes, COALESCE(call_stats.call_count, 0) AS callCount, COALESCE(call_stats.call_total_duration, 0) AS callTotalDuration FROM sys_user su INNER JOIN sys_user_role sur ON su.user_id = sur.user_id LEFT JOIN bank_organization bo ON su.org_id = bo.org_id AND bo.del_flag = '0' LEFT JOIN( SELECT tf.manager_id, COUNT(DISTINCT tf.id) AS customer_count, COUNT(DISTINCT CASE WHEN vcs.credit_status = 3 THEN tf.id END) AS valid_customer_count FROM t_farmer tf LEFT JOIN village_credit_survey vcs ON tf.id = vcs.customer_id AND vcs.del_flag = 0 WHERE tf.deleted = 0 GROUP BY tf.manager_id ) customer_stats ON su.user_id = customer_stats.manager_id LEFT JOIN( SELECT vcs.survey_user_id AS manager_id, COUNT(*) AS survey_count, SUM(CASE WHEN vcs.credit_status = 3 THEN 1 ELSE 0 END) AS credit_count, COALESCE(SUM(CASE WHEN vcs.credit_status = 3 THEN vcs.suggest_credit_amount END), 0) AS credit_amount FROM village_credit_survey vcs WHERE vcs.del_flag = 0 GROUP BY vcs.survey_user_id ) credit_stats ON su.user_id = credit_stats.manager_id LEFT JOIN( SELECT mt.manager_id, COUNT(*) AS task_count, SUM(CASE WHEN mt.task_status = '2' THEN 1 ELSE 0 END) AS task_completed FROM marketing_task mt WHERE mt.del_flag = '0' GROUP BY mt.manager_id ) task_stats ON su.user_id = task_stats.manager_id LEFT JOIN( SELECT mt.manager_id, COUNT(DISTINCT mt.customer_id) AS visit_count, COUNT(mer.id) AS visit_times FROM marketing_exec_recordmer LEFT JOIN marketing_task mt ON mer.task_id = mt.id AND mt.del_flag = '0' WHERE mer.del_flag = '0' GROUP BY mt.manager_id ) visit_stats ON su.user_id = visit_stats.manager_id LEFT JOIN( SELECT mt.manager_id, COUNT(*) AS call_count, COALESCE(SUM(tfc.duration_min), 0) AS call_total_duration FROM t_farmer_call tfc LEFT JOIN marketing_task mt ON tfc.task_id = mt.id AND mt.del_flag = '0' WHERE tfc.deleted = 0 GROUP BY mt.manager_id ) call_stats ON su.user_id = call_stats.manager_id WHERE sur.role_id = 2008 AND su.del_flag = '0' <if test="orgId != null"> AND su.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND su.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null"> AND su.user_id = #{managerId} </if> ORDER BY bo.org_name, su.nick_name LIMIT #{offset}, #{limit} </select>
<select id="selectManagerPerfDetailCount" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="Long"> SELECT COUNT(*) FROM sys_user su INNER JOIN sys_user_role sur ON su.user_id = sur.user_id WHERE sur.role_id = 2008 AND su.del_flag = '0' <if test="orgId != null"> AND su.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND su.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="managerId != null">AND su.user_id = #{managerId}</if> </select>
<select id="selectOrgSummary" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="com.ruoyi.mobile.vo.OrgPeriodSummaryReportVO$SummaryData"> SELECT (SELECT COUNT(*) FROM bank_organization bo WHERE bo.del_flag = '0' AND bo.org_type = 2 <if test="orgId != null"> AND bo.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND bo.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> ) AS orgCount, (SELECT COUNT(DISTINCT tf.id) FROM t_farmer tf LEFT JOIN village_credit_survey vcs ON tf.id = vcs.customer_id AND vcs.del_flag = 0 WHERE tf.deleted = 0 AND vcs.credit_status = 3 <if test="orgId != null"> AND tf.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND tf.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> ) AS totalCreditCount, COALESCE((SELECT SUM(vcs.suggest_credit_amount) FROM village_credit_survey vcs LEFT JOIN t_farmer tf ON vcs.customer_id = tf.id WHERE vcs.credit_status = 3 AND vcs.del_flag = 0 AND tf.deleted = 0 <if test="orgId != null"> AND tf.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND tf.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> ), 0) AS totalCreditAmount, 0 AS avgCreditAmount </select>
<select id="selectOrgSummaryDetailList" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="com.ruoyi.mobile.vo.OrgPeriodSummaryReportVO$OrgSummaryDetailVO"> SELECT bo.org_name AS orgName, COALESCE(manager_stats.manager_count, 0) AS managerCount, COALESCE(village_stats.village_count, 0) AS villageCount, COALESCE(customer_stats.total_customer, 0) AS totalCustomer, COALESCE(customer_stats.valid_customer_count, 0) AS validCustomerCount, COALESCE(credit_stats.survey_count, 0) AS surveyCount, COALESCE(credit_stats.credit_count, 0) AS creditCount, COALESCE(credit_stats.credit_amount, 0) AS creditAmount, CASE WHEN COALESCE(customer_stats.total_customer, 0) = 0 THEN 0 ELSE ROUND(COALESCE(credit_stats.credit_count, 0) * 100.0 / customer_stats.total_customer, 2) END AS creditRate, COALESCE(task_stats.task_count, 0) AS taskCount, COALESCE(task_stats.task_completed, 0) AS taskCompleted, CASE WHEN COALESCE(task_stats.task_count, 0) = 0 THEN 0 ELSE ROUND(COALESCE(task_stats.task_completed, 0) * 100.0 / task_stats.task_count, 2) END AS taskRate, COALESCE(visit_stats.visit_count, 0) AS visitCount, COALESCE(visit_stats.visit_times, 0) AS visitTimes, COALESCE(call_stats.call_count, 0) AS callCount, COALESCE(call_stats.call_total_duration, 0) AS callTotalDuration FROM bank_organization bo LEFT JOIN( SELECT su.org_id, COUNT(*) AS manager_count FROM sys_user su INNER JOIN sys_user_role sur ON su.user_id = sur.user_id WHERE sur.role_id = 2008 AND su.del_flag = '0' GROUP BY su.org_id ) manager_stats ON bo.org_id = manager_stats.org_id LEFT JOIN( SELECT rga.belong_org_id AS dept_id, COUNT(*) AS village_count FROM rural_grid_area rga WHERE rga.area_type = 3 AND rga.del_flag = '0' GROUP BY rga.belong_org_id ) village_stats ON bo.org_id = village_stats.dept_id LEFT JOIN( SELECT tf.org_id AS dept_id, COUNT(DISTINCT tf.id) AS total_customer, COUNT(DISTINCT CASE WHEN vcs.credit_status = 3 THEN tf.id END) AS valid_customer_count FROM t_farmer tf LEFT JOIN village_credit_survey vcs ON tf.id = vcs.customer_id AND vcs.del_flag = 0 WHERE tf.deleted = 0 GROUP BY tf.org_id ) customer_stats ON bo.org_id = customer_stats.dept_id LEFT JOIN( SELECT tf.org_id AS dept_id, COUNT(vcs.id) AS survey_count, SUM(CASE WHEN vcs.credit_status = 3 THEN 1 ELSE 0 END) AS credit_count, COALESCE(SUM(CASE WHEN vcs.credit_status = 3 THEN vcs.suggest_credit_amount END), 0) AS credit_amount FROM village_credit_survey vcs LEFT JOIN t_farmer tf ON vcs.customer_id = tf.id WHERE vcs.del_flag = 0 AND tf.deleted = 0 GROUP BY tf.org_id ) credit_stats ON bo.org_id = credit_stats.dept_id LEFT JOIN( SELECT mt.org_id AS dept_id, COUNT(*) AS task_count, SUM(CASE WHEN mt.task_status = '2' THEN 1 ELSE 0 END) AS task_completed FROM marketing_task mt WHERE mt.del_flag = '0' GROUP BY mt.org_id ) task_stats ON bo.org_id = task_stats.dept_id LEFT JOIN( SELECT mt.org_id AS dept_id, COUNT(DISTINCT mt.customer_id) AS visit_count, COUNT(mer.id) AS visit_times FROM marketing_exec_recordmer LEFT JOIN marketing_task mt ON mer.task_id = mt.id AND mt.del_flag = '0' WHERE mer.del_flag = '0' GROUP BY mt.org_id ) visit_stats ON bo.org_id = visit_stats.dept_id LEFT JOIN( SELECT mt.org_id AS dept_id, COUNT(*) AS call_count, COALESCE(SUM(tfc.duration_min), 0) AS call_total_duration FROM t_farmer_call tfc LEFT JOIN marketing_task mt ON tfc.task_id = mt.id AND mt.del_flag = '0' WHERE tfc.deleted = 0 GROUP BY mt.org_id ) call_stats ON bo.org_id = call_stats.dept_id WHERE bo.org_type = 2 AND bo.del_flag = '0' <if test="orgId != null"> AND bo.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND bo.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="orgName != null and orgName != ''"> AND bo.org_name LIKE CONCAT('%', #{orgName}, '%') </if> ORDER BY bo.org_name LIMIT #{offset}, #{limit} </select>
<select id="selectOrgSummaryDetailCount" parameterType="com.ruoyi.mobile.dto.ReportQueryDTO" resultType="Long"> SELECT COUNT(*) FROM bank_organization bo WHERE bo.org_type = 2 AND bo.del_flag = '0' <if test="orgId != null"> AND bo.org_id = #{orgId} </if> <if test="orgIds != null"> <if test="orgIds.size() > 0"> AND bo.org_id IN <foreach collection="orgIds" item="orgIdItem" open="(" separator="," close=")"> #{orgIdItem} </foreach> </if> <if test="orgIds.size() == 0"> AND 1 = 0 </if> </if> <if test="orgName != null and orgName != ''">AND bo.org_name LIKE CONCAT('%', #{orgName}, '%')</if> </select> </mapper>
微信
支付宝
