<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.changhu.mapper.ServiceProjectMapper">
    <resultMap id="ProjectManageServiceProjectListVoResultMap"
               type="com.changhu.module.miniProgram.pojo.vo.IndexServiceProjectListVo">
        <result
                column="contact_person_info"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.Fastjson2TypeHandler"
                property="contactPersonInfo"/>
        <result
                column="service_project_list"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.Fastjson2TypeHandler"
                property="serviceProjectList"/>
    </resultMap>
    <select id="getServiceProjectList"
            resultMap="ProjectManageServiceProjectListVoResultMap">
        select eu.*,
               ad1.name                      as 'provinceName',
               ad2.name                      as 'cityName',
               ad3.name                      as 'districtsName',
               ad4.name                      as 'streetName',
               JSON_ARRAYAGG(JSON_OBJECT(
                       'snowFlakeId', sp.snow_flake_id,
                       'securityUnitId',sp.security_unit_id,
                       'securityUnitName',su.name,
                       'name', sp.name,
                       'type', sp.type,
                       'idNumber', sp.id_number,
                       'serviceArea', sp.service_area,
                       'buildingTotal', sp.building_total,
                       'houseTotal', sp.house_total,
                       'staffTotal', sp.staff_total,
                       'securityUserTotal', sp.security_user_total,
                       'projectManagerMiniProgramUserInfo',JSON_OBJECT(
                                                        'name',mpu.name,
                                                        'telephone',mpu.telephone,
                                                        'idCard',mpu.id_card),
                       'remark', sp.remark)) as 'service_project_list'
        from enterprises_unit eu
                 left join service_project sp on eu.snow_flake_id = sp.enterprises_unit_id and sp.delete_flag = 0
                 left join security_unit su on sp.security_unit_id = su.snow_flake_id and su.delete_flag = 0
                 left join mini_program_user mpu on sp.project_manager_mini_program_user_id = mpu.snow_flake_id and mpu.delete_flag = 0
                 left join administrative_division ad1 on eu.province = ad1.code and ad1.delete_flag = 0
                 left join administrative_division ad2 on eu.city = ad2.code and ad2.delete_flag = 0
                 left join administrative_division ad3 on eu.districts = ad3.code and ad3.delete_flag = 0
                 left join administrative_division ad4 on eu.street = ad4.code and ad4.delete_flag = 0
        where eu.delete_flag = 0
          <choose>
              <when test="policeUnitId!=null">
                  and eu.police_unit_id = #{policeUnitId}
              </when>
              <otherwise>
                  and sp.project_manager_mini_program_user_id = #{projectManagerMiniProgramUserId}
              </otherwise>
          </choose>
        group by eu.snow_flake_id
        order by any_value(sp.create_time) desc;
    </select>
</mapper>
