<?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.hxhq.business.mapper.GspFfjlMapper">
    <select id="queryList" resultType="com.hxhq.business.dto.gsp.GspFfjlListDto">
        select t.id,t.mc,t.bh,t.ffzytj,t.ffrq,t.ghrq,t.ckl,t.ckldw,t.rkl,t.rkldw,t.lqr1_mc,t.lqr2_mc,t.ffr1_mc,t.ffr2_mc,t.jsr1_mc,t.jsr2_mc
        ,t.syl,t.syldw,t.jlzt,t.jyzt,t.md_other,t.jyksrq,t.jyjsrq, z.mc as zcgMc,
        (select GROUP_CONCAT(s.name) from t_study s where find_in_set(s.id, t.`md_ids`)) as mdMcs
        FROM `t_gsp_ffjl` t
        left join  `t_zcg` z on z.id=t.zcg_id
        <if test="ew.sqlSegment != '' and ew.sqlSegment != null">
            <where>
                ${ew.sqlSegment}
            </where>
        </if>
    </select>
    <select id="queryInfo" resultType="com.hxhq.business.dto.gsp.GspFfjlListDto">
        select t.id,t.mc,t.bh,t.ffzytj,t.ffrq,t.ghrq,t.ckl,t.ckldw,t.rkl,t.rkldw,t.lqr1_mc,t.lqr2_mc,t.ffr1_mc,t.ffr2_mc,t.jsr1_mc,t.jsr2_mc
        ,t.syl,t.syldw,t.jlzt,t.jyzt,t.md_other,t.jyksrq,t.jyjsrq,
        (select GROUP_CONCAT(s.name) from t_study s where find_in_set(s.id, t.`md_ids`)) as mdMcs
        FROM `t_gsp_ffjl` t WHERE id = #{id}
    </select>
    <select id="getStatisticsUnit" resultType="java.util.Map">
        SELECT * FROM
            (SELECT ckldw, COUNT(*) AS ckldw_cnt FROM t_gsp_ffjl
            <if test="ew.sqlSegment != '' and ew.sqlSegment != null">
                <where>
                    ${ew.sqlSegment}
                </where>
            </if>
             GROUP BY ckldw ORDER BY ckldw_cnt DESC LIMIT 1) AS ckldw
        CROSS JOIN
            (SELECT syldw, COUNT(*) AS syldw_cnt FROM t_gsp_ffjl
                <if test="ew.sqlSegment != '' and ew.sqlSegment != null">
                    <where>
                        ${ew.sqlSegment}
                    </where>
                </if>
            GROUP BY syldw ORDER BY syldw_cnt DESC LIMIT 1)  AS syldw
        CROSS JOIN
            (SELECT rkldw, COUNT(*) AS rkldw_cnt FROM t_gsp_ffjl
                <if test="ew.sqlSegment != '' and ew.sqlSegment != null">
                    <where>
                        ${ew.sqlSegment}
                    </where>
                </if>
            GROUP BY rkldw ORDER BY rkldw_cnt DESC LIMIT 1)  AS rkldw;
    </select>

    <select id="sumRkl" resultType="java.math.BigDecimal">
        SELECT SUM(basic_rkl + 0) AS total
        FROM t_gsp_ffjl
        <if test="ew.sqlSegment != '' and ew.sqlSegment != null">
            <where>
                ${ew.sqlSegment}
            </where>
        </if>
    </select>
    <select id="sumCkl" resultType="java.math.BigDecimal">
        SELECT SUM(basic_ckl + 0) AS total
        FROM t_gsp_ffjl
        <if test="ew.sqlSegment != '' and ew.sqlSegment != null">
            <where>
                ${ew.sqlSegment}
            </where>
        </if>
    </select>
    <select id="sumSyl" resultType="java.math.BigDecimal">
        SELECT SUM(basic_syl + 0) AS total
        FROM t_gsp_ffjl
        <if test="ew.sqlSegment != '' and ew.sqlSegment != null">
            <where>
                ${ew.sqlSegment}
            </where>
        </if>
    </select>
    <select id="getStatistics" resultType="java.util.Map">
        SELECT
            IFNULL(SUM(IF(ckldw = #{ckldw}, basic_ckl + 0, 0)), 0) AS ckl,
            IFNULL(SUM(IF(rkldw = #{rkldw}, basic_rkl + 0, 0)), 0) AS rkl,
            IFNULL(SUM(IF(syldw = #{syldw}, basic_syl + 0, 0)), 0) AS syl
        FROM t_gsp_ffjl
        <if test="ew.sqlSegment != '' and ew.sqlSegment != null">
            <where>
                ${ew.sqlSegment}
            </where>
        </if>
    </select>
</mapper>