<?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.yeejoin.amos.boot.module.jcs.api.mapper.PowerTransferMapper">
    <select id="getPowerTransferList"
            resultType="com.yeejoin.amos.boot.module.jcs.api.dto.PowerTransferCompanyResourcesDto">
        select sequence_nbr, company_name, type, resources_num
        from (
                 select ptcr.sequence_nbr, ptc.company_name, ptcr.type, ptcr.resources_num, ptcr.rec_date
                 from jc_power_transfer_company ptc
                          left join jc_power_transfer pt on ptc.power_transfer_id = pt.sequence_nbr
                          right join jc_power_transfer_company_resources ptcr
                                     on ptcr.power_transfer_company_id = ptc.sequence_nbr
                 where pt.alert_called_id = #{alertCalledId}
                   and ptc.is_distribution_agencies = 0
                 union all
                 select ptc.sequence_nbr, ptc.company_name, "" type, "" resources_num, ptc.rec_date
                 from jc_power_transfer_company ptc
                          left join jc_power_transfer pt on ptc.power_transfer_id = pt.sequence_nbr
                 where pt.alert_called_id = #{alertCalledId}
                   and ptc.is_distribution_agencies = 1
             ) jcpt
        order by rec_date desc
    </select>

    <select id="getLastPowerTransferCompany"
            resultType="com.yeejoin.amos.boot.module.jcs.api.dto.PowerTransferCompanyDto">
        select company_name, type, count(resources_num) resourcesCount, rec_date
        from (select ptcr.sequence_nbr, ptc.company_name, ptcr.type, ptcr.resources_num, ptcr.rec_date
              from jc_power_transfer_company ptc
                       left join jc_power_transfer pt on ptc.power_transfer_id = pt.sequence_nbr
                       right join jc_power_transfer_company_resources ptcr
                                  on ptcr.power_transfer_company_id = ptc.sequence_nbr
              where pt.alert_called_id = #{alertCalledId}
                and ptc.is_distribution_agencies = 0) r
        group by company_name, type, rec_date
        having rec_date = (select rec_date from jc_power_transfer_company_resources order by rec_date desc limit 1)
    </select>
    <select id="queryTransferCarIdsByAlertCalledId" resultType="java.lang.String">
        SELECT ptcr.resources_id carId
        FROM `jc_power_transfer` pt
                 LEFT JOIN jc_power_transfer_company ptc ON ptc.power_transfer_id = pt.sequence_nbr
                 RIGHT JOIN jc_power_transfer_company_resources ptcr ON ptcr.power_transfer_company_id = ptc.sequence_nbr
        WHERE pt.alert_called_id = #{alertCalledId}
    </select>

    <select id="getPowerTransferInfoCount" resultType="java.util.Map">
        select count(distinct pt.sequence_nbr) transfer_count, count(distinct ptcr.sequence_nbr) car_count, 0 end_count,
        0
        no_return_car_count
        from jc_power_transfer pt
        left join jc_power_transfer_company ptc on pt.sequence_nbr = ptc.power_transfer_id
        left join jc_power_transfer_company_resources ptcr on ptc.sequence_nbr = ptcr.power_transfer_company_id
        <where>
            <if test="beginDate != null and beginDate != ''">
                AND pt.rec_date &gt;= #{beginDate}
            </if>

            <if test="endDate != null and endDate != ''">
                AND pt.rec_date &lt;= #{endDate}
            </if>
        </where>
        order by pt.rec_date asc
    </select>
    <select id="getPowerTransferInfo" resultType="com.yeejoin.amos.boot.module.jcs.api.dto.PowerTransferDto">
        select sequence_nbr, rescue_grid
        from jc_power_transfer pt
        <where>
            <if test="beginDate != null and beginDate != ''">
                AND pt.rec_date &gt;= #{beginDate}
            </if>

            <if test="endDate != null and endDate != ''">
                AND pt.rec_date &lt;= #{endDate}
            </if>
        </where>
        order by pt.rec_date asc
    </select>
    <select id="getPowerTransferCompanyInfo"
            resultType="com.yeejoin.amos.boot.module.jcs.api.dto.PowerTransferCompanyDto">
        select ptc.sequence_nbr, ptc.company_name, ptc.is_distribution_agencies, ptc.power_transfer_id
        from jc_power_transfer pt
        left join jc_power_transfer_company ptc on pt.sequence_nbr = ptc.power_transfer_id
        <where>
            <if test="beginDate != null and beginDate != ''">
                AND pt.rec_date &gt;= #{beginDate}
            </if>

            <if test="endDate != null and endDate != ''">
                AND pt.rec_date &lt;= #{endDate}
            </if>
        </where>
        order by pt.rec_date asc
    </select>
    <select id="getPowerTransferCompanyResourcesInfo"
            resultType="com.yeejoin.amos.boot.module.jcs.api.dto.PowerTransferCompanyResourcesDto">
        select ptcr.power_transfer_company_id, ptcr.type, ptcr.resources_num, ptcr.resources_id
        from jc_power_transfer pt
        left join jc_power_transfer_company ptc on pt.sequence_nbr = ptc.power_transfer_id
        left join jc_power_transfer_company_resources ptcr on ptc.sequence_nbr = ptcr.power_transfer_company_id
        where ptc.is_distribution_agencies = 0
        <if test="beginDate != null and beginDate != ''">
            AND pt.rec_date &gt;= #{beginDate}
        </if>

        <if test="endDate != null and endDate != ''">
            AND pt.rec_date &lt;= #{endDate}
        </if>
        order by pt.rec_date asc
    </select>


    <select id="getCarNum" resultType="Map">
     SELECT
	COUNT( DISTINCT b.company_id ) companyNum,
	COUNT( DISTINCT c.resources_num ) carNum
    FROM
	jc_power_transfer a
	LEFT JOIN jc_power_transfer_company b ON a.sequence_nbr = b.power_transfer_id
	LEFT JOIN jc_power_transfer_company_resources c ON c.power_transfer_company_id = b.sequence_nbr
    WHERE
	a.alert_called_id = ${id}
    </select>


    <select id="getCompanyNum" resultType="Map">
      select COUNT(*) companyNum from
    (SELECT DISTINCT
        b.company_id
    FROM
        jc_power_transfer a
        LEFT JOIN jc_power_transfer_company b ON a.sequence_nbr = b.power_transfer_id
        LEFT JOIN jc_power_transfer_company_resources c ON c.power_transfer_company_id = b.sequence_nbr
    WHERE
        a.alert_called_id  = ${id} UNION ALL
    SELECT DISTINCT
        b.company_id
    FROM
        jc_alert_submitted a
        LEFT JOIN jc_alert_submitted_object b ON a.sequence_nbr = b.alert_submitted_id
    WHERE
        a.alert_called_id = ${id}) m
        </select>

        <select id="getPowerDataList" resultType="com.yeejoin.amos.boot.module.jcs.api.dto.PowerData">
      SELECT DISTINCT
        b.company_name companyName,
        c.resources_name carName,
        c.resources_num carNum

    FROM
        jc_power_transfer a
        LEFT JOIN jc_power_transfer_company b ON a.sequence_nbr = b.power_transfer_id
        LEFT JOIN jc_power_transfer_company_resources c ON c.power_transfer_company_id = b.sequence_nbr
    WHERE
        a.alert_called_id = ${id}
    </select>







    <select id="getPowerCompanyCountDtocount" resultType="com.yeejoin.amos.boot.module.jcs.api.dto.PowerCompanyCountDto">
           SELECT
        b.company_id companyId,
        b.company_name companyName,
        COUNT( c.resources_num ) carNum
    FROM
        jc_power_transfer a
        LEFT JOIN jc_power_transfer_company b ON a.sequence_nbr = b.power_transfer_id
        LEFT JOIN jc_power_transfer_company_resources c ON c.power_transfer_company_id = b.sequence_nbr
    WHERE
        a.alert_called_id = ${id}
    GROUP BY
        b.company_id,
        b.company_name
    </select>

    <select id="getPowerCarCountDtocount" resultType="com.yeejoin.amos.boot.module.jcs.api.dto.PowerCarCountDto">
         SELECT * from  (	  SELECT
        COUNT( c.resources_num ) carNum,
        c.resources_name carName,
        GROUP_CONCAT( c.resources_num ) plateNumber
    FROM
        jc_power_transfer a
        LEFT JOIN jc_power_transfer_company b ON a.sequence_nbr = b.power_transfer_id
        LEFT JOIN jc_power_transfer_company_resources c ON c.power_transfer_company_id = b.sequence_nbr
    WHERE
        a.alert_called_id = ${alertCalledId}
        AND b.company_id = ${companyId}
    GROUP BY
        c.resources_name ) m where m.carNum>0
    </select>
    <select id="getPowerTransferTeamResource"
            resultType="com.yeejoin.amos.boot.module.jcs.api.dto.PowerTransferResourceDto">
        SELECT distinct
            company.company_id sequenceNbr,
            company.company_name NAME,
            'company' type,
            team.address,
            team.contact_user chargePerson,
            team.contact_phone tel,
            team.img
        FROM
            jc_power_transfer_company company
                LEFT JOIN jc_power_transfer transfer ON transfer.sequence_nbr = company.power_transfer_id
                LEFT JOIN cb_fire_team team ON team.sequence_nbr = company.company_id
        WHERE
            transfer.alert_called_id = #{alertCalledId}
            AND is_distribution_agencies = 0
    </select>
    <select id="getPowerTransferTeamResourceCount"
            resultType="com.yeejoin.amos.boot.module.jcs.api.dto.ResourceStatisticsDto">
        SELECT
            "team" type,
            count( DISTINCT company.company_id ) allCount
        FROM
            jc_power_transfer_company company
                LEFT JOIN jc_power_transfer transfer ON transfer.sequence_nbr = company.power_transfer_id
        WHERE
            transfer.alert_called_id = #{alertCalledId}
          AND is_distribution_agencies = 0 UNION ALL
        SELECT
            "car" type,
            count( DISTINCT car.resources_id ) allCount
        FROM
            jc_power_transfer_company_resources car
                LEFT JOIN jc_power_transfer_company company ON company.sequence_nbr = car.power_transfer_company_id
                LEFT JOIN jc_power_transfer transfer ON transfer.sequence_nbr = company.power_transfer_id
        WHERE
            transfer.alert_called_id = #{alertCalledId}
          AND is_distribution_agencies = 0
        UNION ALL
        SELECT
            "person" type,
            0 allCount
    </select>
    <select id="getPowerTransferCarResource"
            resultType="com.yeejoin.amos.boot.module.jcs.api.dto.PowerTransferResourceDto">
        SELECT DISTINCT
            car.resources_id sequenceNbr,
            car.resources_name,
            'car' type
        FROM
            jc_power_transfer_company_resources car
                LEFT JOIN jc_power_transfer_company company ON company.sequence_nbr = car.power_transfer_company_id
                LEFT JOIN jc_power_transfer transfer ON transfer.sequence_nbr = company.power_transfer_id
        WHERE
            transfer.alert_called_id = #{alertCalledId}
          AND is_distribution_agencies = 0
    </select>

</mapper>
