<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//oos.juyouhx.com//DTD Mapper 3.0//EN" "http://oss.juyouhx.com/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.scpyun.platform.standard.jilinsscgsdp.SocialSecurity">

    <select id="checkHasExist" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.SocialSecurityVo" resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.SocialSecurityVo">
      select
        id,
        data_type,
        year,
        month1,
        month2,
        month3,
        month4,
        month5,
        month6,
        month7,
        month8,
        month9,
        month10,
        month11,
        month12
      from jl_social_security
      where del_flag='0'
      and create_by=#{_user.id}
      and year=#{year}
      and data_type=#{data_type}
    </select>

    <insert id="insertSocialSecurity" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.SocialSecurityVo">
      insert into jl_social_security
      <trim prefix="(" suffix=")" suffixOverrides=",">
        id,
        <if test="data_type != null and data_type != ''">data_type,</if>
        <if test="year != null and year != ''">year,</if>
        <if test="month1 != null and month1 != ''">month1,</if>
        <if test="month2 != null and month2 != ''">month2,</if>
        <if test="month3 != null and month3 != ''">month3,</if>
        <if test="month4 != null and month4 != ''">month4,</if>
        <if test="month5 != null and month5 != ''">month5,</if>
        <if test="month6 != null and month6 != ''">month6,</if>
        <if test="month7 != null and month7 != ''">month7,</if>
        <if test="month8 != null and month8 != ''">month8,</if>
        <if test="month9 != null and month9 != ''">month9,</if>
        <if test="month10 != null and month10 != ''">month10,</if>
        <if test="month11 != null and month11 != ''">month11,</if>
        <if test="month12 != null and month12 != ''">month12,</if>
        create_by,
        create_time,
        company_id
      </trim>
      <trim prefix="values (" suffix=")" suffixOverrides=",">
        uuid(),
        <if test="data_type != null and data_type != ''">#{data_type},</if>
        <if test="year != null and year != ''">#{year},</if>
        <if test="month1 != null and month1 != ''">#{month1},</if>
        <if test="month2 != null and month2 != ''">#{month2},</if>
        <if test="month3 != null and month3 != ''">#{month3},</if>
        <if test="month4 != null and month4 != ''">#{month4},</if>
        <if test="month5 != null and month5 != ''">#{month5},</if>
        <if test="month6 != null and month6 != ''">#{month6},</if>
        <if test="month7 != null and month7 != ''">#{month7},</if>
        <if test="month8 != null and month8 != ''">#{month8},</if>
        <if test="month9 != null and month9 != ''">#{month9},</if>
        <if test="month10 != null and month10 != ''">#{month10},</if>
        <if test="month11 != null and month11 != ''">#{month11},</if>
        <if test="month12 != null and month12 != ''">#{month12},</if>
        #{_user.id},
          now(),
          #{_user.company_id}
      </trim>
    </insert>

    <update id="updateSocialSecurity" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.SocialSecurityVo">
      update jl_social_security
      <set>
        <if test="data_type != null and data_type != ''">data_type = #{data_type},</if>
        <if test="year != null and year != ''">year = #{year},</if>
        <if test="month1 != null and month1 != ''">month1 = #{month1},</if>
        <if test="month2 != null and month2 != ''">month2 = #{month2},</if>
        <if test="month3 != null and month3 != ''">month3 = #{month3},</if>
        <if test="month4 != null and month4 != ''">month4 = #{month4},</if>
        <if test="month5 != null and month5 != ''">month5 = #{month5},</if>
        <if test="month6 != null and month6 != ''">month6 = #{month6},</if>
        <if test="month7 != null and month7 != ''">month7 = #{month7},</if>
        <if test="month8 != null and month8 != ''">month8 = #{month8},</if>
        <if test="month9 != null and month9 != ''">month9 = #{month9},</if>
        <if test="month10 != null and month10 != ''">month10 = #{month10},</if>
        <if test="month11 != null and month11 != ''">month11 = #{month11},</if>
        <if test="month12 != null and month12 != ''">month12 = #{month12},</if>
          <if test="del_flag != null and del_flag != ''">del_flag = #{del_flag},</if>
        update_by = #{_user.id},
        update_time = now()
      </set>
      where id = #{id}
    </update>

    <select id="selectSocialSecurityPageList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.SocialSecurityVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.SocialSecurityVo">
      select
        a.id,
        <!--a.data_type,-->
        CASE
            WHEN a.data_type = '1' THEN '刑事案件数'
            WHEN a.data_type = '2' THEN '八类暴力犯罪案件数'
            WHEN a.data_type = '3' THEN '电信网络诈骗案件数'
            WHEN a.data_type = '4' THEN '盗窃、抢劫、抢夺案件数'
            WHEN a.data_type = '5' THEN '民转刑案件数'
            WHEN a.data_type = '6' THEN '涉毒案件数'
            WHEN a.data_type = '7' THEN '治安案件数'
            WHEN a.data_type = '8' THEN '扰乱公共秩序案件数'
            WHEN a.data_type = '9' THEN '妨害社会管理秩序案件数'
            WHEN a.data_type = '10' THEN '侵犯他人人身权利、财产权利案件数'
            ELSE '未知类型'
        END as data_type,
        a.year,
        a.month1,
        a.month2,
        a.month3,
        a.month4,
        a.month5,
        a.month6,
        a.month7,
        a.month8,
        a.month9,
        a.month10,
        a.month11,
        a.month12,
      <!--case when #{params._user.id} = a.create_by then '1' else '0' end as isMine,-->
      <!--case when a.create_by = #{params._user.id} then '1' else '0' end as isMine,-->
        b.name as create_by,
        DATE_FORMAT(a.create_time,'%Y-%m-%d %T') create_time,
        a.company_id
      from jl_social_security a
        join sys_user b on a.create_by=b.id
      where a.del_flag='0'
      <if test="_user.company_grade=='3'.toString()">
        and a.create_by=#{_user.id}
      </if>
      <if test="data_type!=null and data_type!=''">
        and a.data_type=#{data_type}
      </if>
      <if test="year!=null and year!=''">
        and a.year=#{year}
      </if>
      order by a.create_time desc
    </select>

  <select id="selectAllSocialSecurityPageList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.SocialSecurityVo"
          resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.SocialSecurityVo">
      select
        <!--a.data_type,-->
    CASE
            WHEN a.data_type = '1' THEN '刑事案件数'
            WHEN a.data_type = '2' THEN '八类暴力犯罪案件数'
            WHEN a.data_type = '3' THEN '电信网络诈骗案件数'
            WHEN a.data_type = '4' THEN '盗窃、抢劫、抢夺案件数'
            WHEN a.data_type = '5' THEN '民转刑案件数'
            WHEN a.data_type = '6' THEN '涉毒案件数'
            WHEN a.data_type = '7' THEN '治安案件数'
            WHEN a.data_type = '8' THEN '扰乱公共秩序案件数'
            WHEN a.data_type = '9' THEN '妨害社会管理秩序案件数'
            WHEN a.data_type = '10' THEN '侵犯他人人身权利、财产权利案件数'
            ELSE '未知类型'
        END as data_type,
        a.year,
        sum(a.month1) month1,
        sum(a.month2) month2,
        sum(a.month3) month3,
        sum(a.month4) month4,
        sum(a.month5) month5,
        sum(a.month6) month6,
        sum(a.month7) month7,
        sum(a.month8) month8,
        sum(a.month9) month9,
        sum(a.month10) month10,
        sum(a.month11) month11,
        sum(a.month12) month12
      from jl_social_security a
        join sys_user b on a.create_by=b.id
      where a.del_flag='0'
      <if test="data_type!=null and data_type!=''">
        and a.data_type=#{data_type}
      </if>
      <if test="year!=null and year!=''">
        and a.year=#{year}
      </if>
      <if test="create_by!=null and create_by!=''">
        and a.company_id=#{create_by}
      </if>
      group by a.data_type,a.year
      order by a.create_time desc
    </select>

    <select id="selectSocialSecurityInfo" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.SocialSecurityVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.SocialSecurityVo">
      select
        a.id,
        a.data_type,
        a.year,
        a.month1,
        a.month2,
        a.month3,
        a.month4,
        a.month5,
        a.month6,
        a.month7,
        a.month8,
        a.month9,
        a.month10,
        a.month11,
        a.month12,
        b.name as create_by,
        DATE_FORMAT(a.create_time,'%Y-%m-%d %T') create_time,
        a.company_id
      from jl_social_security a
        join sys_user b on a.create_by=b.id
      where a.del_flag='0' and a.id=#{id}
    </select>

    <select id="socialSecurityStatistics" parameterType="map" resultType="map">
        <!--SELECT
          IFNULL(SUM(CASE WHEN data_type = '1' THEN month1 + month2 + month3 + month4 + month5 + month6 + month7 + month8 + month9 + month10 + month11 + month12 ELSE 0 END), 0) AS sum1,
          IFNULL(SUM(CASE WHEN data_type = '7' THEN month1 + month2 + month3 + month4 + month5 + month6 + month7 + month8 + month9 + month10 + month11 + month12 ELSE 0 END), 0) AS sum2,
          IFNULL(SUM(CASE WHEN (data_type != '1' and data_type != '7') THEN month1 + month2 + month3 + month4 + month5 + month6 + month7 + month8 + month9 + month10 + month11 + month12 ELSE 0 END), 0) AS sum3
        FROM jl_social_security a
        WHERE a.del_flag = '0' and a.year=DATE_FORMAT(CURDATE(), '%Y')
        <if test="_user.company_grade=='3'.toString()">
            and a.create_by=#{_user.id}
        </if>-->
        SELECT
          IFNULL(SUM(
            CASE WHEN data_type = '1' THEN
              IFNULL(month1,0) + IFNULL(month2,0) + IFNULL(month3,0) + IFNULL(month4,0) + IFNULL(month5,0) + IFNULL(month6,0) +
              IFNULL(month7,0) + IFNULL(month8,0) + IFNULL(month9,0) + IFNULL(month10,0) + IFNULL(month11,0) + IFNULL(month12,0)
            ELSE 0 END), 0) AS sum1,

          IFNULL(SUM(
            CASE WHEN data_type = '7' THEN
              IFNULL(month1,0) + IFNULL(month2,0) + IFNULL(month3,0) + IFNULL(month4,0) + IFNULL(month5,0) + IFNULL(month6,0) +
              IFNULL(month7,0) + IFNULL(month8,0) + IFNULL(month9,0) + IFNULL(month10,0) + IFNULL(month11,0) + IFNULL(month12,0)
            ELSE 0 END), 0) AS sum2,

          IFNULL(SUM(
            CASE WHEN data_type NOT IN ('1','7') THEN
              IFNULL(month1,0) + IFNULL(month2,0) + IFNULL(month3,0) + IFNULL(month4,0) + IFNULL(month5,0) + IFNULL(month6,0) +
              IFNULL(month7,0) + IFNULL(month8,0) + IFNULL(month9,0) + IFNULL(month10,0) + IFNULL(month11,0) + IFNULL(month12,0)
            ELSE 0 END), 0) AS sum3
        FROM jl_social_security a
        WHERE a.del_flag = '0'
      AND a.year = DATE_FORMAT(CURDATE(), '%Y')
      <if test="_user.company_grade=='3'.toString()">
        AND a.create_by = #{_user.id}
      </if>
        <if test="_user.company_grade != '2'.toString()">
            and (
            create_by=#{params._user.id}
            or create_by in (
            select id from sys_user where company_id in (
            select id from sys_office where parent_ids LIKE CONCAT('%', #{_user.company_id}, '%') and
            del_flag='0'
            ) and del_flag='0'
            )
            )
        </if>
    </select>



</mapper>
