1 <?xml version="1.0" encoding="UTF-8" ?>  2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  3 <mapper namespace="VisitStatisticsDao">  4     <resultMap id="visitStatisticsResult" type="com.qunar.hotel.crm.dto.VisitStatisticsResult"/>  5   6   7     <sql id="timeRange">  8         <![CDATA[  9          visit.visit_date  >= #{dateFrom} ]]> 10         and 11         <![CDATA[ visit.visit_date  <= #{dateTo} ]]> 12     </sql> 13  14     <sql id="validState"> 15         and 16         visit.valid = 1 17     </sql> 18  19     <sql id="conditionOfSort"> 20         <choose> 21             <when test="orderBy == ‘byDoorVisited‘"> 22                 and visit.visit_type = 2 23             </when> 24             <when test="orderBy == ‘KPVisited‘"> 25                 and visit.is_active = 0 26             </when> 27             <when test="orderBy == ‘byDoorKPVisited‘"> 28                 and visit.is_active = 0 and visit.visit_type =2 29             </when> 30             <when test="orderBy == ‘revisited‘"> 31                 and visit.revisit_valid = 1 32             </when> 33         </choose> 34     </sql> 35  36     <sql id="statisticAttribute"> 37         total_count.visit_count as totalVisited, 38         by_door_count.visit_count as byDoorVisited, 39         kp_count.visit_count as KPVisited, 40         by_door_kp_count.visit_count as byDoorKPVisited, 41         by_valid_revisit_count.visit_count as revisited 42     </sql> 43  44     <sql id="order"> 45         <choose> 46             <when test="orderBy == ‘totalVisited‘"> 47                 totalVisited 48             </when> 49             <when test="orderBy == ‘byDoorVisited‘"> 50                 byDoorVisited 51             </when> 52             <when test="orderBy == ‘KPVisited‘"> 53                 KPVisited 54             </when> 55             <when test="orderBy == ‘byDoorKPVisited‘"> 56                 byDoorKPVisited 57             </when> 58             <when test="orderBy == ‘revisited‘"> 59                 revisited 60             </when> 61             <otherwise> 62                 totalVisited 63             </otherwise> 64         </choose> 65         <choose> 66             <when test="orderType == ‘asc‘"> 67                 asc 68             </when> 69             <otherwise> 70                 desc 71             </otherwise> 72         </choose> 73     </sql> 74  75     <sql id="parent"> 76         (select 77         `id`, 78         `name`, 79         `tree_code`, 80         `org_type` 81         from qadmin_org org 82         where org_type = #{dimension} 83         and org.tree_code like concat((select tree_code from qadmin_org where id = #{groupId}), ‘%‘) 84         ) 85         parent 86     </sql> 87  88     <sql id="orgVisitRecord"> 89         qadmin_org org 90         left join 91         qadmin_user user 92         on org.id = user.org_id 93         inner join 94         crm_visit_record visit 95         on user.user_id = visit.user_id 96         where 97         <include refid="timeRange"/> 98         <include refid="validState"/> 99     </sql>100 101     <sql id="bdTotalCount">102         (select103         count(visit.id) visit_count,104         user.user_id user_id,105         user.realname real_name106         from107         qadmin_org org108         inner join109         qadmin_user user on user.org_id = org.id110         left join111         (select id, user_id from112         crm_visit_record visit113         where114         <include refid="timeRange"/>115         <include refid="validState"/>116         )117         visit118         on visit.user_id = user.user_id119         where120         org.tree_code like concat((select tree_code from qadmin_org where id=#{groupId}), ‘%‘)121         group by122         user.user_id123         )124     </sql>125 126     <sql id="bdByDoorCount">127         (select128         count(visit.id) visit_count,129         user.user_id user_id,130         user.realname real_name131         from132         qadmin_org org133         inner join134         qadmin_user user on user.org_id = org.id135         left join136         (select id, user_id from137         crm_visit_record visit138         where139         <include refid="timeRange"/>140         and141         visit_type = 2142         <include refid="validState"/>143         )144         visit on visit.user_id = user.user_id145         where146         org.tree_code like concat((select tree_code from qadmin_org where id=#{groupId}), ‘%‘)147         group by148         user.user_id149         )150     </sql>151 152     <sql id="bdKPCount">153         (select154         count(visit.id) visit_count,155         user.user_id user_id,156         user.realname real_name157         from158         qadmin_org org159         inner join160         qadmin_user user on user.org_id = org.id161         left join162         (select id, user_id from163         crm_visit_record visit164         where165         <include refid="timeRange"/>166         and167         is_active = 0168         <include refid="validState"/>169         )170         visit on visit.user_id = user.user_id171         where172         org.tree_code like concat((select tree_code from qadmin_org where id=#{groupId}), ‘%‘)173         group by174         user.user_id175         )176     </sql>177 178     <sql id="bdKPByDoorCount">179         (select180         count(visit.id) visit_count,181         user.user_id user_id,182         user.realname real_name183         from184         qadmin_org org185         inner join186         qadmin_user user on user.org_id = org.id187         left join188         (select id, user_id from189         crm_visit_record visit190         where191         <include refid="timeRange"/>192         and193         is_active = 0194         and195         visit_type = 2196         <include refid="validState"/>197         )198         visit on visit.user_id = user.user_id199         where200         org.tree_code like concat((select tree_code from qadmin_org where id=#{groupId}), ‘%‘)201         group by202         user.user_id203         )204     </sql>205 206     <sql id="RevisitValidCount">207         (208         select209         count(visit.id) visit_count,210         user.user_id user_id,211         user.realname real_name212         from213         qadmin_org org214         inner join215         qadmin_user user on user.org_id = org.id216         left join217         (select id, user_id from crm_visit_record visit218         where219         <include refid="timeRange"/>220         <include refid="validState"/>221         and222         revisit_valid = 1223         )224         visit on visit.user_id = user.user_id225         where226         org.tree_code like concat((select tree_code from qadmin_org where id=#{groupId}), ‘%‘)227         group by228         user.user_id229         )230     </sql>231 232     <sql id="queryBDVisitedCountInMultiGroupTotal">233         (234         select235         org.id as ‘id‘,236         org.user_id as ‘user_id‘,237         org.sub_realname as ‘name‘,238         visit.visit_count as ‘visit_count‘239         from240         (241         select242         sub.id as ‘id‘,243         sub.user_id as ‘user_id‘,244         sub.realname as ‘sub_realname‘245         from246         (select id, name, tree_code from qadmin_org)247         parent248         left join249         (select250         org.name,251         org.tree_code,252         user.id as ‘id‘,253         user.user_id as ‘user_id‘,254         user.realname255         from256         qadmin_org257         org258         inner join259         qadmin_user user260         on user.org_id = org.id261         )262         sub263         on sub.tree_code like concat(parent.tree_code, ‘%‘)264         where265         parent.id in266         <foreach collection="groupIds" index="index" item="item" open="(" separator="," close=")">267             #{item}268         </foreach>269         )270         org271         left join272         (273         select274         count(visit.id) as ‘visit_count‘,275         user.user_id as ‘user_id‘276         from277         crm_visit_record visit278         left join279         qadmin_user user280         on281         visit.user_id = user.user_id282         where283         <include refid="timeRange"/>284         <include refid="validState"/>285         group by286         user.user_id287         )288         visit289         on org.user_id = visit.user_id290         )291     </sql>292 293     <sql id="queryBDVisitedCountInMultiGroupByDoor">294         (295         select296         org.id as ‘id‘,297         org.user_id as ‘user_id‘,298         org.sub_realname as ‘name‘,299         visit.visit_count as ‘visit_count‘300         from301         (302         select303         sub.id as ‘id‘,304         sub.user_id as ‘user_id‘,305         sub.realname as ‘sub_realname‘306         from307         (select id, name, tree_code from qadmin_org)308         parent309         left join310         (select311         org.name,312         org.tree_code,313         user.id as ‘id‘,314         user.user_id as ‘user_id‘,315         user.realname316         from317         qadmin_org318         org319         inner join320         qadmin_user user321         on user.org_id = org.id322         )323         sub324         on sub.tree_code like concat(parent.tree_code, ‘%‘)325         where326         parent.id in327         <foreach collection="groupIds" index="index" item="item" open="(" separator="," close=")">328             #{item}329         </foreach>330         )331         org332         left join333         (334         select335         count(visit.id) as ‘visit_count‘,336         user.user_id as ‘user_id‘337         from338         crm_visit_record visit339         left join340         qadmin_user user341         on342         visit.user_id = user.user_id343         where344         <include refid="timeRange"/>345         and346         visit.visit_type = 2347         <include refid="validState"/>348         group by349         user.user_id350         )351         visit352         on org.user_id = visit.user_id353         )354     </sql>355 356     <sql id="queryBDVisitedCountInMultiGroupByKPVisit">357         (358         select359         org.id as ‘id‘,360         org.user_id as ‘user_id‘,361         org.sub_realname as ‘name‘,362         visit.visit_count as ‘visit_count‘363         from364         (365         select366         sub.id as ‘id‘,367         sub.user_id as ‘user_id‘,368         sub.realname as ‘sub_realname‘369         from370         (select id, name, tree_code from qadmin_org)371         parent372         left join373         (374         select375         org.name,376         org.tree_code,377         user.id as ‘id‘,378         user.user_id as ‘user_id‘,379         user.realname380         from381         qadmin_org382         org383         inner join384         qadmin_user385         user386         on user.org_id = org.id387         )388         sub389         on sub.tree_code like concat(parent.tree_code, ‘%‘)390         where391         parent.id in392         <foreach collection="groupIds" index="index" item="item" open="(" separator="," close=")">393             #{item}394         </foreach>395         )396         org397         left join398         (399         select400         count(visit.id) as ‘visit_count‘,401         user.user_id as ‘user_id‘402         from403         crm_visit_record visit404         left join405         qadmin_user user406         on407         visit.user_id = user.user_id408         where409         <include refid="timeRange"/>410         and411         visit.is_active = 0412         <include refid="validState"/>413         group by414         user.user_id415         )416         visit417         on org.user_id = visit.user_id418         )419     </sql>420 421     <sql id="queryBDVisitedCountInMultiGroupByKPDoorVisit">422         (423         select424         org.id as ‘id‘,425         org.user_id as ‘user_id‘,426         org.sub_realname as ‘name‘,427         visit.visit_count as ‘visit_count‘428         from429         (430         select431         sub.id as ‘id‘,432         sub.user_id as ‘user_id‘,433         sub.realname as ‘sub_realname‘434         from435         (select id, name, tree_code from qadmin_org)436         parent437         left join438         (439         select440         org.name,441         org.tree_code,442         user.id as ‘id‘,443         user.user_id as ‘user_id‘,444         user.realname445         from446         qadmin_org447         org448         inner join449         qadmin_user450         user451         on user.org_id = org.id452         )453         sub454         on sub.tree_code like concat(parent.tree_code, ‘%‘)455         where456         parent.id in457         <foreach collection="groupIds" index="index" item="item" open="(" separator="," close=")">458             #{item}459         </foreach>460         )461         org462         left join463         (464         select465         count(visit.id) as ‘visit_count‘,466         user.user_id as ‘user_id‘467         from468         crm_visit_record visit469         left join470         qadmin_user user471         on472         visit.user_id = user.user_id473         where474         <include refid="timeRange"/>475         and476         visit.is_active = 0477         and478         visit.visit_type = 2479         <include refid="validState"/>480         group by481         user.user_id482         )483         visit484         on org.user_id = visit.user_id485         )486     </sql>487 488     <sql id="queryBDVisitedCountInMultiGroupByValidRevisit">489         (490         select491         org.id as ‘id‘,492         org.user_id as ‘user_id‘,493         org.sub_realname as ‘name‘,494         visit.visit_count as ‘visit_count‘495         from496         (497         select498         sub.id as ‘id‘,499         sub.user_id as ‘user_id‘,500         sub.realname as ‘sub_realname‘501         from502         (select id, name, tree_code from qadmin_org)503         parent504         left join505         (506         select507         org.name,508         org.tree_code,509         user.id as ‘id‘,510         user.user_id as ‘user_id‘,511         user.realname512         from513         qadmin_org514         org515         inner join516         qadmin_user517         user518         on user.org_id = org.id519         )520         sub521         on sub.tree_code like concat(parent.tree_code, ‘%‘)522         where523         parent.id in524         <foreach collection="groupIds" index="index" item="item" open="(" separator="," close=")">525             #{item}526         </foreach>527         )528         org529         left join530         (531         select532         count(visit.id) as ‘visit_count‘,533         user.user_id as ‘user_id‘534         from535         crm_visit_record visit536         left join537         qadmin_user user538         on539         visit.user_id = user.user_id540         where541         <include refid="timeRange"/>542         and543         visit.revisit_valid = 1544         <include refid="validState"/>545         group by546         user.user_id547         )548         visit549         on org.user_id = visit.user_id550         )551     </sql>552 553     <select id="querySortedBDVisitStatistics"  parameterType="com.qunar.hotel.crm.model.condition.VisitStatisticsCondition" resultType="string">554         select user_id from555         (556         select557         visit.user_id as ‘user_id‘,558         visit.visit_count as ‘visit_count‘559         from560         (561             select562             sub.user_id as ‘user_id‘563             from564                 (select id, tree_code from qadmin_org)565             parent566             left join567             (568                 select569                 org.tree_code as ‘tree_code‘,570                 user.user_id as ‘user_id‘571                 from572                 qadmin_org573                 org574                 inner join575                 qadmin_user user576                 on user.org_id = org.id577             )578             sub579             on sub.tree_code like concat(parent.tree_code, ‘%‘)580             where581             parent.id in582             <foreach collection="groupIds" index="index" item="item" open="(" separator="," close=")">583                 #{item}584             </foreach>585         )586         user587         left join588         (589             select590             count(visit.id) as ‘visit_count‘,591             user_id592             from  crm_visit_record visit593             where594             <include refid="timeRange"/>595             <include refid="validState"/>596             <include refid="conditionOfSort"/>597             group by user_id598         )599         visit600         on user.user_id = visit.user_id601         order by visit_count602         <choose>603             <when test="orderType == ‘asc‘">604                 asc605             </when>606             <otherwise>607                 desc608             </otherwise>609         </choose>610         )temp611 612     </select>613 614     <select id="queryBDVisitedCount" resultMap="visitStatisticsResult"615             parameterType="com.qunar.hotel.crm.model.condition.VisitStatisticsCondition"616             fetchSize="100" resultSetType="FORWARD_ONLY">617         select618         total_count.user_id as groupId,619         total_count.real_name as name,620         <include refid="statisticAttribute"/>621         from622 623         <include refid="bdTotalCount"/>624         total_count625         left join626         <include refid="bdByDoorCount"/>627         by_door_count628         on total_count.user_id = by_door_count.user_id629         left join630         <include refid="bdKPCount"/>631         kp_count632         on total_count.user_id = kp_count.user_id633         left join634         <include refid="bdKPByDoorCount"/>635         by_door_kp_count636         on total_count.user_id = by_door_kp_count.user_id637         left join638         <include refid="RevisitValidCount"/>639         by_valid_revisit_count640         on total_count.user_id = by_valid_revisit_count.user_id641         order by642         <include refid="order"/>643     </select>644 645     <select id="queryOrgVisitedCount" resultMap="visitStatisticsResult"646             parameterType="com.qunar.hotel.crm.model.condition.VisitStatisticsCondition"647             fetchSize="100" resultSetType="FORWARD_ONLY">648         select649         total_count.parent_id as ‘groupId‘,650         total_count.parent_name as ‘name‘,651         <include refid="statisticAttribute"/>652         from653         (654         select655         parent.id as ‘parent_id‘,656         parent.name as ‘parent_name‘,657         count(sub.visit_id) as ‘visit_count‘658         from659         <include refid="parent"/>660         left join661         (select662         org.tree_code as ‘tree_code‘,663         visit.id as ‘visit_id‘664         from665         <include refid="orgVisitRecord"/>666         )667         sub668         on sub.tree_code like concat(parent.tree_code, ‘%‘)669         group by parent.id)670         total_count671         left join672         (673         select674         parent.id as ‘parent_id‘,675         parent.name as ‘parent_name‘,676         count(sub.visit_id) as ‘visit_count‘677         from678         <include refid="parent"/>679         left join680         (select681         org.tree_code as ‘tree_code‘,682         visit.id as ‘visit_id‘683         from684         <include refid="orgVisitRecord"/>685         and visit.visit_type = 2686         )687         sub688         on sub.tree_code like concat(parent.tree_code, ‘%‘)689         group by parent.id)690         by_door_count691         on total_count.parent_id = by_door_count.parent_id692         left join693         (694         select695         parent.id as ‘parent_id‘,696         parent.name as ‘parent_name‘,697         count(sub.visit_id) as ‘visit_count‘698         from699         <include refid="parent"/>700         left join701         (select702         org.tree_code as ‘tree_code‘,703         visit.id as ‘visit_id‘704         from705         <include refid="orgVisitRecord"/>706         and visit.is_active = 0707         )708         sub709         on sub.tree_code like concat(parent.tree_code, ‘%‘)710         group by parent.id)711         kp_count712         on total_count.parent_id = kp_count.parent_id713         left join714         (715         select716         parent.id as ‘parent_id‘,717         parent.name as ‘parent_name‘,718         count(sub.visit_id) as ‘visit_count‘719         from720         <include refid="parent"/>721         left join722         (select723         org.tree_code as ‘tree_code‘,724         visit.id as ‘visit_id‘725         from726         <include refid="orgVisitRecord"/>727         and visit.is_active = 0728         and visit.visit_type = 2729         )730         sub731         on sub.tree_code like concat(parent.tree_code, ‘%‘)732         group by parent.id)733         kp_by_door_count734         on total_count.parent_id = kp_by_door_count.parent_id735         left join736         (737         select738         parent.id as ‘parent_id‘,739         parent.name as ‘parent_name‘,740         count(sub.visit_id) as ‘visit_count‘741         from742         <include refid="parent"/>743         left join744         (745         select746         org.tree_code as ‘tree_code‘,747         visit.id as ‘visit_id‘748         from749         <include refid="orgVisitRecord"/>750         and visit.revisit_valid = 1751         )752         sub753         on sub.tree_code like concat(parent.tree_code, ‘%‘)754         group by parent.id755         )756         by_valid_revisit_count757         on total_count.parent_id = by_valid_revisit_count.parent_id758         order by759         <include refid="order"/>760     </select>761 762     <select id="querySingleBDVisitedCount" resultMap="visitStatisticsResult"763             parameterType="com.qunar.hotel.crm.model.condition.VisitStatisticsCondition">764         select765         total_count.user_id as groupId,766         total_count.name as name,767         <include refid="statisticAttribute"/>768         from769         (770         select771         count(visit.id) as visit_count,772         user.user_id as user_id,773         user.realname as name774         from775         qadmin_user user776         left join777         crm_visit_record visit778         on user.user_id = visit.user_id779         where780         user.user_id = #{groupId}781         and782         <include refid="timeRange"/>783         <include refid="validState"/>784         )785         total_count786         left join787         (788         select789         count(visit.id) as visit_count,790         user.user_id as user_id,791         user.realname as name792         from793         qadmin_user user794         left join795         crm_visit_record visit796         on user.user_id = visit.user_id797         where798         user.user_id = #{groupId}799         and800         <include refid="timeRange"/>801         <include refid="validState"/>802         and803         visit.visit_type = 2804         )805         by_door_count806         on total_count.user_id = by_door_count.user_id807         left join808         (809         select810         count(visit.id) as visit_count,811         user.user_id as user_id,812         user.realname as name813         from814         qadmin_user user815         left join816         crm_visit_record visit817         on user.user_id = visit.user_id818         where819         user.user_id = #{groupId}820         and821         <include refid="timeRange"/>822         <include refid="validState"/>823         and824         visit.is_active = 0825         )826         kp_count827         on total_count.user_id = kp_count.user_id828         left join829         (830         select831         count(visit.id) as visit_count,832         user.user_id as user_id,833         user.realname as name834         from835         qadmin_user user836         left join837         crm_visit_record visit838         on user.user_id = visit.user_id839         where840         user.user_id = #{groupId}841         and842         <include refid="timeRange"/>843         <include refid="validState"/>844         and845         visit.is_active = 0846         and847         visit.visit_type = 2848         )849         by_door_kp_count850         on total_count.user_id = by_door_kp_count.user_id851         left join852         (853         select854         count(visit.id) as visit_count,855         user.user_id as user_id,856         user.realname as name857         from858         qadmin_user user859         left join860         crm_visit_record visit861         on user.user_id = visit.user_id862         where863         user.user_id = #{groupId}864         and865         <include refid="timeRange"/>866         <include refid="validState"/>867         and868         visit.revisit_valid = 1869 870         )871         by_valid_revisit_count872         on total_count.user_id = by_valid_revisit_count.user_id873 874     </select>875 876     <select id="queryBDVisitedCountInMultiGroup" resultMap="visitStatisticsResult"877             parameterType="com.qunar.hotel.crm.model.condition.VisitStatisticsCondition"878             fetchSize="100" resultSetType="FORWARD_ONLY">879         select880         total_count.user_id as ‘groupId‘,881         total_count.name as ‘name‘,882         <include refid="statisticAttribute"/>883         from884         <include refid="queryBDVisitedCountInMultiGroupTotal"/>885         total_count886         left join887         <include refid="queryBDVisitedCountInMultiGroupByDoor"/>888         by_door_count889         on890         total_count.user_id = by_door_count.user_id891         left join892         <include refid="queryBDVisitedCountInMultiGroupByKPVisit"/>893         kp_count894         on total_count.user_id = kp_count.user_id895         left join896         <include refid="queryBDVisitedCountInMultiGroupByKPDoorVisit"/>897         by_door_kp_count898         on total_count.user_id = by_door_kp_count.user_id899         left join900         <include refid="queryBDVisitedCountInMultiGroupByValidRevisit"/>901         by_valid_revisit_count902         on total_count.user_id = by_valid_revisit_count.user_id903         group by total_count.user_id904         order by905         <include refid="order"/>906     </select>907 908     <select id="queryBDVisitedRecordSize" resultType="int"909             parameterType="com.qunar.hotel.crm.model.condition.VisitStatisticsCondition">910         select count(distinct sub.user_id) from911         (select id, tree_code from qadmin_org)912         parent913         left join914         (select915         org.tree_code as ‘tree_code‘,916         `user`.id as ‘user_id‘917         from918         qadmin_org org left join919         qadmin_user `user`920         on `user`.org_id = org.id)921         sub922         on sub.tree_code like concat(parent.tree_code, ‘%‘)923         where924         parent.id = #{groupId};925     </select>926 927     <select id="queryOrgVisitedRecordSize" resultType="int"928             parameterType="com.qunar.hotel.crm.model.condition.VisitStatisticsCondition">929         select count(sub.id) from930         (select931         name,932         id,933         org_type,934         tree_code935         from qadmin_org where id = #{groupId})936         parent937         left join938         (select939         name,940         id,941         org_type,942         tree_code943         from qadmin_org where org_type = #{dimension})944         sub945         on sub.tree_code like concat(parent.tree_code, ‘%‘);946     </select>947 948     <select id="queryBDVisitedInMultiGroupRecordSize" resultType="int"949             parameterType="com.qunar.hotel.crm.model.condition.VisitStatisticsCondition">950         select951         count(sub.id)952         from953         (select id, name, tree_code from qadmin_org)954         parent955         left join956         (select957         org.name,958         org.tree_code,959         user.id,960         user.realname961         from962         qadmin_org org inner join963         qadmin_user user964         on user.org_id = org.id)965         sub966         on sub.tree_code like concat(parent.tree_code, ‘%‘)967         where968         parent.id in969         <foreach collection="groupIds" index="index" item="item" open="(" separator="," close=")">970             #{item}971         </foreach>972     </select>973 974 </mapper>

 1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="com.qunar.scm.visit.dao.VisitStatisticsDao"> 4     <resultMap id="visitStatisticsResult" type="com.qunar.scm.visit.model.vo.VisitStatisticsResult"/> 5  6     <sql id="order"> 7         <choose> 8             <when test="orderBy == ‘totalVisited‘"> 9                 totalVisited10             </when>11             <when test="orderBy == ‘byDoorVisited‘">12                 byDoorVisited13             </when>14             <when test="orderBy == ‘KPVisited‘">15                 KPVisited16             </when>17             <when test="orderBy == ‘byDoorKPVisited‘">18                 byDoorKPVisited19             </when>20             <when test="orderBy == ‘revisited‘">21                 revisited22             </when>23             <otherwise>24                 totalVisited25             </otherwise>26         </choose>27         <choose>28             <when test="orderType == ‘asc‘">29                 asc30             </when>31             <otherwise>32                 desc33             </otherwise>34         </choose>35     </sql>36 37     <sql id="where">38         <where>39             visit.visit_date  >= #{dateFrom} and #{dateTo} >= visit.visit_date40             and visit.valid = 141             <if test="userId != null and userId != ‘‘ ">42                 and user_id = #{userId}43             </if>44             <if test="orgTreeCodes != null and orgTreeCodes.size >0 ">45                 AND46                 <foreach collection="orgTreeCodes" item="treeCode" open="(" close=")" separator="or">47                     org_tree_code like ‘${treeCode}%‘48                 </foreach>49             </if>50         </where>51     </sql>52 53     <sql id="column">54         user_id as ‘groupId‘,55         count(1) as totalVisited,56         sum(case when visit.visit_type =2 then 1 else 0 end) as byDoorVisited,57         sum(case when visit.is_active = 0 then 1 else 0 end) as KPVisited,58         sum(case when visit.is_active = 0 and visit.visit_type =2 then 1 else 0 end) as byDoorKPVisited,59         sum(case when visit.revisit_valid = 1  then 1 else 0 end) as revisited,60         sum(case when (visit.visit_purpose_type = ‘1‘ or visit.visit_purpose_type=‘1#2‘) then 1 else 0 end) as visitforSignCount,61         sum(case when (visit.visit_purpose_type = ‘2‘ or visit.visit_purpose_type=‘1#2‘) then 1 else 0 end) as visitforMantainceCount62     </sql>63 64 65     <select id="queryBDVisitedCountInMultiGroupWithRowHandler" resultMap="visitStatisticsResult"66             parameterType="com.qunar.scm.visit.model.condition.VisitStatisticsCondition"67             fetchSize="100" resultSetType="FORWARD_ONLY">68         select69         <include refid="column"/>70         from crm_visit_record visit71         <include refid="where"/>72         group by user_id73         order by74         <include refid="order"/>75     </select>76 77 78 79     <select id="queryBDVisitedCountInMultiGroup" resultMap="visitStatisticsResult"80             parameterType="com.qunar.scm.visit.model.condition.VisitStatisticsCondition">81         select82         <include refid="column"/>83         from crm_visit_record visit84         <include refid="where"/>85         group by user_id86         order by87         <include refid="order"/>88     </select>89 90     <select id="queryBDVisitedInMultiGroupRecordSize" resultType="int"91             parameterType="com.qunar.scm.visit.model.condition.VisitStatisticsCondition">92         select93         count(distinct user_id)94         from crm_visit_record visit95         <include refid="where"/>96     </select>97 98 </mapper>