<?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.AlertSubmittedMapper">

    <select id="listSchedulingByParam" resultType="com.yeejoin.amos.boot.module.jcs.api.dto.AlertSubmittedExtDto">
        select
        jas.sequence_nbr alertSubmittedId,
        jas.alert_called_id,
        jas.business_type,
        jas.business_type_code,
        jas.scheduling_type,
        jas.scheduling_type_code,
        jas.submission_time,
        jas.submission_content,
        jas.submission_method,
        jas.submission_method_code,
        jas.update_time,
        jaso.sequence_nbr,
        jaso.company_name,
        jaso.user_name,
        jaso.user_phone
        from jc_alert_submitted jas
        left join jc_alert_submitted_object jaso on jas.sequence_nbr = jaso.alert_submitted_id
        where 1=1
        <if test="alertSubmittedDto.alertCalledId != null and alertSubmittedDto.alertCalledId != ''">
            and jas.alert_called_id = #{alertSubmittedDto.alertCalledId}
        </if>
        <if test="alertSubmittedDto.submissionMethodCode != null and alertSubmittedDto.submissionMethodCode != ''">
            and jas.submission_method_code = #{alertSubmittedDto.submissionMethodCode}
        </if>
        <if test="alertSubmittedDto.businessTypeCode != null and alertSubmittedDto.businessTypeCode != ''">
            and jas.business_type_code = #{alertSubmittedDto.businessTypeCode}
        </if>
        order by jas.submission_time desc
    </select>
    <select id="listReportingByParam" resultType="com.yeejoin.amos.boot.module.jcs.api.dto.AlertSubmittedExtDto">
        select
        jas.sequence_nbr alertSubmittedId,
        jas.alert_called_id,
        jas.business_type,
        jas.business_type_code,
        jas.scheduling_type,
        jas.scheduling_type_code,
        jas.submission_time,
        jas.submission_content,
        jas.submission_method,
        jas.submission_method_code,
        jas.update_time,
        jaso.sequence_nbr,
        group_concat(distinct jaso.company_name Separator '、') companyName,
        group_concat(distinct jaso.user_name Separator '、') userName
        from jc_alert_submitted jas
        left join jc_alert_submitted_object jaso on jas.sequence_nbr = jaso.alert_submitted_id
        where 1=1
        <if test="alertSubmittedDto.alertCalledId != null and alertSubmittedDto.alertCalledId != ''">
            and jas.alert_called_id = #{alertSubmittedDto.alertCalledId}
        </if>
        <if test="alertSubmittedDto.submissionMethodCode != null and alertSubmittedDto.submissionMethodCode != ''">
            and jas.submission_method_code = #{alertSubmittedDto.submissionMethodCode}
        </if>
        <if test="alertSubmittedDto.businessTypeCode != null and alertSubmittedDto.businessTypeCode != ''">
            and jas.business_type_code = #{alertSubmittedDto.businessTypeCode}
        </if>
        group by jas.sequence_nbr, jas.submission_time
        order by jas.submission_time desc
    </select>

    <select id="getSchedulingContent" resultType="com.yeejoin.amos.boot.module.jcs.api.dto.AlertSubmittedSMSDto">
        SELECT
            s.sequence_nbr,
            s.submission_time,
            o.user_phone,
            o.company_name,
            o.user_name,
            s.sender,
            s.submission_content,
            t.content submission_template
        FROM
            jc_alert_submitted_object o
                LEFT JOIN jc_alert_submitted s ON o.alert_submitted_id = s.sequence_nbr
                LEFT JOIN jc_template t ON s.business_type_code = t.type_code
                AND t.format = 1
        WHERE
            o.sequence_nbr = ${id}
    </select>




    <select id="listAlertSubmittedZHDto" resultType="com.yeejoin.amos.boot.module.jcs.api.dto.AlertSubmittedZHDto">
        SELECT
        g.business_type businessType,
        g.sequence_nbr sequenceNbr,
        g.submission_time submissionTime,
        g.submission_content submissionContent,
        g.company_name companyName,
        group_concat( DISTINCT g.user_name ) AS userName
        FROM
        (
        SELECT
        a.sequence_nbr,
        a.business_type,
        a.submission_time,
        a.submission_content,
        b.company_name,
        b.user_name
        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}
        ) g
        GROUP BY
        g.business_type,
        g.sequence_nbr,
        g.submission_time,
        g.submission_content,
        g.company_name;
    </select>



    <select id="getUseNum" resultType="Map">
       SELECT
	COUNT( DISTINCT b.rec_user_id ) useNum
    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}
    </select>






</mapper>
