<?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.OpenApiMapper">
    <select id="getEnterprisesUnit" resultType="com.changhu.common.pojo.vo.SelectNodeVo">
        select
        eu.snow_flake_id as 'value',
        eu.name as 'label'
        from enterprises_unit eu
        left join police_unit pu on eu.police_unit_id = pu.snow_flake_id
        where eu.delete_flag = 0
        <choose>
            <when test="level==1">
                and eu.province = #{code}
            </when>
            <when test="level==2">
                and eu.city = #{code}
            </when>
            <when test="level==3">
                and eu.districts = #{code}
            </when>
            <when test="level==4">
                and eu.street = #{code}
            </when>
            <when test="level==5">
                and pu.code = #{code}
            </when>
            <otherwise>and eu.snow_flake_id = -1</otherwise>
        </choose>
        order by eu.create_time desc
    </select>
    <resultMap id="ServiceProjectDTOResultMap" type="com.changhu.pojo.dto.ServiceProjectDTO">
        <result
                column="projectManagerInfo"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.Fastjson2TypeHandler"
                property="projectManagerInfo"/>
        <result
                column="securityUnitInfo"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.Fastjson2TypeHandler"
                property="securityUnitInfo" />
        <result
                column="securityUserList"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.Fastjson2TypeHandler"
                property="securityUserList" />
    </resultMap>
    <select id="getServiceProjectByEnterprisesUnitId" resultMap="ServiceProjectDTOResultMap">
        SELECT sp.*,
               IF(mpu.snow_flake_id IS NOT NULL,
                  json_object('name', mpu.NAME, 'sex', mpu.sex, 'telephone', mpu.telephone, 'idCard', mpu.id_card),
                  NULL)                                                        AS 'projectManagerInfo',
               json_object(
                       'name', su1.name,
                       'socialCode', su1.social_code,
                       'businessLicense', su1.business_license,
                       'legalPersonInfo', su1.legal_person_info,
                       'address', su1.address,
                       'nature', su1.nature
               )                                                               as 'securityUnitInfo',
               if(COUNT(su.snow_flake_id) > 0, JSON_ARRAYAGG(json_object(
                       'snowFlakeId', su.snow_flake_id,
                       'photo', su.photo,
                       'name', su.name,
                       'telephone', su.telephone,
                       'workPost', su.work_post,
                       'sex', su.sex,
                       'nativePlace', su.native_place,
                       'idCard', su.id_card,
                       'dateOfBirth', su.date_of_birth,
                       'securityNumber', su.security_number,
                       'homeAddress', su.home_address,
                       'remark', su.remark,
                       'educationLevel', su.education_level,
                       'createTime', su.create_time
                                                             )), JSON_ARRAY()) as 'securityUserList'
        FROM service_project sp
                 left join security_unit su1 on sp.security_unit_id = su1.snow_flake_id
                 LEFT JOIN mini_program_user mpu ON sp.project_manager_mini_program_user_id = mpu.snow_flake_id AND
                                                    mpu.identity = 'project_manager'
                 left join security_user su on su.service_project_id = sp.snow_flake_id and su.delete_flag = 0
        WHERE sp.delete_flag = 0
          AND sp.enterprises_unit_id = #{enterprisesUnitId}
        group by sp.snow_flake_id
        ORDER BY sp.create_time DESC
    </select>
    <resultMap id="SecurityUnitUseStatisticsDTOResultMap" type="com.changhu.pojo.dto.SecurityUnitUseStatisticsDTO">
        <result
                column="serviceProjectList"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.Fastjson2TypeHandler"
                property="serviceProjectList"/>
    </resultMap>
    <select id="securityUnitUseStatistics" resultMap="SecurityUnitUseStatisticsDTOResultMap">
        WITH security_user_counts AS (
        SELECT
        service_project_id,
        COUNT(1) AS securityUserTotal,
        SUM(IF(security_number != '', 1, 0)) AS haveCardSecurityUserCount
        FROM
        security_user
        WHERE delete_flag = 0
        GROUP BY service_project_id )
        select
        eu.snow_flake_id as 'enterprisesUnitId',
        eu.name as 'enterprisesUnitName',
        eu.province,
        eu.city,
        eu.districts,
        eu.street,
        ad1.name as 'provinceName',
        ad2.name as 'cityName',
        ad3.name as 'districtsName',
        ad4.name as 'streetName',
        eu.address as 'address',
        pu.snow_flake_id as 'policeUnitId',
        pu.code as 'policeUnitCode',
        pu.name as 'policeUnitName',
        json_arrayagg(
        json_object(
        'snowFlakeId', sp.snow_flake_id,
        'name', sp.name,
        'type', sp.type,
        'twoType', sp.two_type,
        'outsourceName', sp.outsource_name,
        'isFiling', sp.is_filing,
        'idNumber', sp.id_number,
        'securityUserTotal', suc.securityUserTotal,
        'haveCardSecurityUserCount', suc.haveCardSecurityUserCount,
        'securityUnitId',su.snow_flake_id,
        'securityUnitName',su.name,
        'securityUnitLegalPersonInfo',su.legal_person_info,
        'serviceProjectManagerName',mpu.name,
        'serviceProjectManagerTelephone',mpu.telephone
        )) as 'serviceProjectList'
        from
        police_unit pu
        join enterprises_unit eu on pu.snow_flake_id = eu.police_unit_id and eu.delete_flag = 0
        join service_project sp on eu.snow_flake_id = sp.enterprises_unit_id and sp.delete_flag = 0
        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
        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
        left JOIN security_user_counts suc ON sp.snow_flake_id = suc.service_project_id
        where
        pu.delete_flag = 0
        <choose>
            <when test="level==1">
                and eu.province = #{code}
            </when>
            <when test="level==2">
                and eu.city = #{code}
            </when>
            <when test="level==3">
                and eu.districts = #{code}
            </when>
            <when test="level==4">
                and eu.street = #{code}
            </when>
            <when test="level==5">
                and pu.code = #{code}
            </when>
            <otherwise>and eu.snow_flake_id = -1</otherwise>
        </choose>
        group by eu.snow_flake_id
        order by eu.create_time desc
    </select>
    <select id="securityUserRoster"
            resultType="com.changhu.pojo.dto.SecurityUserRosterDTO">
        select su.snow_flake_id,
               su.name,
               su.sex,
               su.photo,
               TIMESTAMPDIFF(YEAR, su.date_of_birth, CURDATE()) AS 'age',
               su.id_card,
               su.telephone,
               su.education_level,
               eu.name                                          as 'enterprisesUnitName',
               suu.name                                         as 'securityUnitName',
               su.security_number
        from   enterprises_unit eu
                LEFT JOIN service_project sp ON sp.enterprises_unit_id = eu.snow_flake_id AND sp.delete_flag = 0
                JOIN security_user su ON su.service_project_id = sp.snow_flake_id AND su.delete_flag = 0
                LEFT JOIN security_unit suu ON su.security_unit_id = suu.snow_flake_id AND suu.delete_flag = 0
        where eu.delete_flag = 0
          <choose>
              <when test="type==@com.changhu.pojo.params.EnterprisesUnitOrServiceProjectType@ENTERPRISES_UNIT">
                  and eu.snow_flake_id = #{id}
              </when>
            <when test="type==@com.changhu.pojo.params.EnterprisesUnitOrServiceProjectType@SERVICE_PROJECT">
                and sp.snow_flake_id = #{id}
            </when>
            <otherwise>
                and eu.snow_flake_id = -1
            </otherwise>
          </choose>
        order by su.create_time desc
    </select>
    <select id="unitSecurityUserRoster" resultType="com.changhu.pojo.dto.SecurityUserRosterDTO">
        select su.snow_flake_id,
        su.name,
        su.sex,
        su.photo,
        TIMESTAMPDIFF(YEAR, su.date_of_birth, CURDATE()) AS 'age',
        su.id_card,
        su.telephone,
        su.education_level,
        eu.name as 'enterprisesUnitName',
        suu.name as 'securityUnitName',
        su.security_number
        from enterprises_unit eu
        LEFT join police_unit pu on eu.police_unit_id = pu.snow_flake_id and pu.delete_flag = 0
        LEFT JOIN service_project sp ON sp.enterprises_unit_id = eu.snow_flake_id AND sp.delete_flag = 0
        JOIN security_user su ON su.service_project_id = sp.snow_flake_id AND su.delete_flag = 0
        LEFT JOIN security_unit suu ON su.security_unit_id = suu.snow_flake_id AND suu.delete_flag = 0
        where eu.delete_flag = 0
        <choose>
            <when test="level==1">
                and eu.province = #{code}
            </when>
            <when test="level==2">
                and eu.city = #{code}
            </when>
            <when test="level==3">
                and eu.districts = #{code}
            </when>
            <when test="level==4">
                and eu.street = #{code}
            </when>
            <when test="level==5">
                and pu.code = #{code}
            </when>
            <otherwise>and eu.snow_flake_id = -1</otherwise>
        </choose>
        order by su.create_time desc
    </select>
</mapper>