<?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="org.jeecg.modules.zrzyqc.qyxx.mapper.QcQyxxMapper">
    <select id="selectqyxx" resultType="String">
        /*   select count(1) xfrwsl  from qc_qyxx where  sys_org_code like 'A02%'*/
        select count(1) xfrwsl
        from sys_depart
        where org_code like 'A04A%'
    </select>
    <select id="yxdls" resultType="String">
        select count(1) yxdls
        from qc_qyxx
        where qymc is not null
          and tyshxybm is not null
          and qylx is not null
          and sys_org_code like 'A04%'
    </select>
    <select id="sbdksAdmin" resultType="String">
        select count(1) sbdk
        from qc_ssgqzysytdqk
        where sfsb > 0
          and sys_org_code like 'A04%'
    </select>
    <select id="sbdks" resultType="String">
        select count(1) sbdk
        from qc_ssgqzysytdqk
        where sfsb > 0
          and create_by = #{username}
    </select>
    <select id="wsh" resultType="String" parameterType="String">
        select count(1) shzt
        from qc_ssgqzysytdqk
        where create_by = #{username}
          and sfsb = 1
    </select>
    <select id="tgsh" resultType="String" parameterType="String">
        select count(1) shzt
        from qc_ssgqzysytdqk
        where shzt = '通过'
          and create_by = #{username}
          and sfsb = 3
    </select>
    <select id="btgsh" resultType="String" parameterType="String">
        select count(1) shzt
        from qc_ssgqzysytdqk
        where shzt = '不通过'
          and create_by = #{username}
          and sfsb = 3
    </select>
    <select id="tgshAdmin" resultType="String">
        select count(1) shzt
        from qc_ssgqzysytdqk
        where shzt = '通过'
          and sfsb = 3
          and sys_org_code like 'A04%'
    </select>
    <select id="wshAdmin" resultType="String">
        select count(1) shzt
        from qc_ssgqzysytdqk
        where sfsb = 1
          and sys_org_code like 'A04%'
    </select>
    <select id="btgshAdmin" resultType="String">
        select count(1) shzt
        from qc_ssgqzysytdqk
        where shzt = '不通过'
          and sfsb = 4
          and sys_org_code like 'A04%'
    </select>
    <select id="dqsbs" resultType="String" parameterType="String">
        select count(1) sbdk
        from qc_ssgqzysytdqk
        where create_by = #{username}
          and sfsb = 1
    </select>
    <select id="cwth" resultType="String" parameterType="String">
        select count(1) sbdk
        from qc_ssgqzysytdqk
        where sfsb = 4
          and create_by = #{username}
    </select>
    <select id="wshsl" resultType="String" parameterType="String">
        select count(1) sbdk
        from qc_ssgqzysytdqk
        where create_by = #{username}
          and sfsb in (1)
    </select>
    <select id="wshslAdmin" resultType="String" parameterType="String">
        select count(1) sbdk
        from qc_ssgqzysytdqk
        where sfsb = 1
          and sys_org_code like 'A04%'
    </select>
    <select id="shzsl" resultType="String" parameterType="String">
        select count(1) sbdk
        from qc_ssgqzysytdqk
        where create_by = #{username}
          and sfsb in (2)
    </select>
    <select id="shzslAdmin" resultType="String" parameterType="String">
        select count(1) sbdk
        from qc_ssgqzysytdqk
        where sfsb = 2
          and sys_org_code like 'A04%'
    </select>
    <update id="upSfsb" parameterType="String">
        update qc_qyxx
        set sfsb=1
        where id = #{id}
          and sfsb = 0
    </update>
    <update id="updatePid" parameterType="String">
        UPDATE sys_depart
        SET PARENT_ID=#{PARENT_ID}
        WHERE ORG_CODE = #{ORG_CODE}
    </update>
    <select id="getPWDlog" resultType="Integer">
        select count(1)
        from SYS_log l
        where l.log_content like concat('修改密码%', #{username}, '%')
    </select>


    <!--省直单位信息-->
    <select id="selectdwxx" resultType="String">
        /*   select count(1) xfrwsl  from qc_qyxx where  sys_org_code like 'A02%'*/
        select count(1) xfrwsl
        from sys_user
        where org_code like 'A03%'
    </select>
    <select id="sydwyxdls" resultType="String">
        /* select count(DISTINCT create_by)   from qc_ssgqzysytdqk where sys_org_code like 'A03%'*/
        select count(1) yxdls
        from qc_qyxx
        where qymc is not null
          and qylx is not null
          and sys_org_code like 'A03%'
    </select>
    <select id="sydwsbdksAdmin" resultType="String">
        select count(1) sbdk
        from qc_ssgqzysytdqk
        where sfsb > 0
          and sys_org_code like 'A03%'
    </select>


    <!--饼图-->
    <select id="sydwtgshAdmin" resultType="String">
        select count(1) shzt
        from qc_ssgqzysytdqk
        where shzt = '通过'
          and sfsb = 3
          and sys_org_code like 'A03%'
    </select>
    <select id="sydwwshAdmin" resultType="String">
        select count(1) shzt
        from qc_ssgqzysytdqk
        where sfsb = 1
          and sys_org_code like 'A03%'
    </select>
    <select id="sydwbtgshAdmin" resultType="String">
        select count(1) shzt
        from qc_ssgqzysytdqk
        where shzt = '不通过'
          and sfsb = 4
          and sys_org_code like 'A03%'
    </select>
    <select id="sydwshzslAdmin" resultType="String" parameterType="String">
        select count(1) sbdk
        from qc_ssgqzysytdqk
        where sfsb = 2
          and sys_org_code like 'A03%'
    </select>


    <select id="sjyttjsz" resultType="map">
        select count(a.pzyt) as "value", b.name as "name"
        from QC_SSGQZYSYTDQK a
        LEFT JOIN SYS_CATEGORY b
        on a.pzyt = b.id
        where a.SYS_ORG_CODE like 'A03%'
        <if test="xzqh !=null">
            and xzqh like CONCAT( #{xzqh},'%')
        </if>
        GROUP BY b.name
    </select>
    <select id="dtdktjByxq" resultType="map" parameterType="String">
        select count(b.XJXZQHDM) as "value", b.XJXZQHMC as "name",b.XJXZQHDM as "xzqhdm",b.sjxzqhdm as "sjxzqhdm",
        <if test="orgCode=='A04'">
            sum(CAST(sjzytdmj_m AS DECIMAL(10,2))) as "value2"
        </if>
        <if test="orgCode=='A03'">
            sum(SJZYTDMJ*0.0015) as "value2"
        </if>
        from QC_SSGQZYSYTDQK a
        right JOIN (select * from XZQH) b
        on a.xzqh=b.XJXZQHDM
        where SYS_ORG_CODE like concat(#{orgCode}, '%') and xzqh like concat(#{xzqh}, '%') and sfsb=3
        GROUP BY b.XJXZQHDM, b.XJXZQHMC, b.sjxzqhdm ORDER BY b.XJXZQHDM DESC
    </select>

    <select id="dtdktj" resultType="map" parameterType="String">

        select count(b.sjxzqhdm) as "value", b.SJXZQHMC as "name",b.SJXZQHDM as "xzqhdm",
        <if test="orgCode=='A04'">
            sum(CAST(sjzytdmj_m AS DECIMAL(10,2))) as "value2"
        </if>
        <if test="orgCode=='A03'">
            sum(SJZYTDMJ*0.0015) as "value2"
        </if>
        from QC_SSGQZYSYTDQK a
        LEFT JOIN (select DISTINCT SJXZQHDM, SJXZQHMC from XZQH) b
        on a.xzqh like b.SJXZQHDM||'%'
        where SYS_ORG_CODE like concat(#{orgCode}, '%') and sfsb=3
        GROUP BY b.sjxzqhdm, b.SJXZQHMC ORDER BY b.sjxzqhdm DESC
        <!--        select count(SUBSTR(a.xzqh, 0, 4)) as "value", b.SJXZQHMC as "name",-->
        <!--        <if test="orgCode=='A04'">-->
        <!--            sum(SJZYTDMJ_M) as "value2"-->
        <!--        </if>-->
        <!--        <if test="orgCode=='A03'">-->
        <!--            sum(SJZYTDMJ*0.0015) as "value2"-->
        <!--        </if>-->
        <!--        from QC_SSGQZYSYTDQK a-->
        <!--        LEFT JOIN (select DISTINCT SJXZQHDM, SJXZQHMC from XZQH) b-->
        <!--        on SUBSTR(a.xzqh, 0, 4) = b.SJXZQHDM-->
        <!--        where SYS_ORG_CODE like concat(#{orgCode}, '%') and sfsb=3-->
        <!--        GROUP BY SUBSTR(a.xzqh, 0, 4), b.SJXZQHMC ORDER BY count(SUBSTR(a.xzqh, 0, 4)) DESC-->
    </select>
    <select id="sjyttjss" resultType="map">
        select count(a.sjyt) as "value", b.name as "name", b.id as "code"
        from QC_SSGQZYSYTDQK a
        LEFT JOIN SYS_CATEGORY b
        on a.sjyt = b.id
        where a.SYS_ORG_CODE like 'A04%'
        and sfsb = 3
        <if test="xzqh !=null">
            and xzqh like CONCAT( #{xzqh},'%')
        </if>
        GROUP BY b.name, b.id
    </select>
    <select id="ssdkmj" resultType="map">
        select VA as "value", NA as "name", VA2 as "value2"
        from (
                 select ROUND(sum(CAST(sjzytdmj_m AS DECIMAL(10,2))), 2) VA, b.SJXZQHMC na, b.SJXZQHDM xzqh
                 from QC_SSGQZYSYTDQK a
                          LEFT JOIN (select DISTINCT SJXZQHDM, SJXZQHMC from XZQH) b
                                    on a.xzqh like b.SJXZQHDM || '%'
                 where SYS_ORG_CODE like 'A04%'
                   and sfsb = 3
                 GROUP BY b.SJXZQHDM, b.SJXZQHMC
             ) c
                 LEFT JOIN (select ROUND(sum(CAST(sjzytdmj_m AS DECIMAL(10,2))), 2) VA2, b.SJXZQHDM as xzqh2
                            from QC_SSGQZYSYTDQK a
                                     LEFT JOIN (select DISTINCT SJXZQHDM, SJXZQHMC from XZQH) b
                                               on a.xzqh like b.SJXZQHDM || '%'
                            where TDSYXZ = '闲置低效土地'
                              and SYS_ORG_CODE like 'A04%'

                              and sfsb = 3
                            GROUP BY b.SJXZQHDM) d on c.xzqh = d.xzqh2
    </select>
    <select id="ssdkmjbyqx" resultType="map">
        select VA as "value", NA as "name", VA2 as "value2"
        from (
                 select sum(CAST(sjzytdmj_m AS DECIMAL(10,2))) VA, b.XJXZQHMC na, b.XJXZQHDM xzqh, b.SJXZQHDM
                 from QC_SSGQZYSYTDQK a
                          LEFT JOIN (select * from XZQH) b
                                    on a.xzqh = b.XJXZQHDM
                 where SYS_ORG_CODE like 'A04%'
                   and sfsb = 3
                   and xzqh like CONCAT(#{xzqh}, '%')
                 GROUP BY b.XJXZQHDM, b.XJXZQHMC, b.SJXZQHDM
             ) c
                 LEFT JOIN (select sum(CAST(sjzytdmj_m AS DECIMAL(10,2))) VA2, b.XJXZQHDM as xzqh2
                            from QC_SSGQZYSYTDQK a
                                     LEFT JOIN (select * from XZQH) b
                                               on a.xzqh = b.XJXZQHDM
                            where TDSYXZ = '闲置低效土地'
                              and SYS_ORG_CODE like 'A04%'
                              and sfsb = 3
                              and xzqh like CONCAT(#{xzqh}, '%')

                            GROUP BY b.XJXZQHDM) d on c.xzqh = d.xzqh2
    </select>
    <select id="szdkmj" resultType="map">
        select VA as "value", NA as "name", VA2 as "value2"
        from (
                 select ROUND(sum(SJZYTDMJ * 0.0015), 2) VA, b.SJXZQHMC na, b.SJXZQHDM xzqh
                 from QC_SSGQZYSYTDQK a
                          LEFT JOIN (select DISTINCT SJXZQHDM, SJXZQHMC from XZQH) b
                                    on a.xzqh like b.SJXZQHDM || '%'
                 where SYS_ORG_CODE like 'A03%'
                   and sfsb = 3
                 GROUP BY b.SJXZQHDM, b.SJXZQHMC
             ) c
                 LEFT JOIN (select ROUND(sum(SJZYTDMJ * 0.0015), 2) VA2, b.SJXZQHDM as xzqh2
                            from QC_SSGQZYSYTDQK a
                                     LEFT JOIN (select DISTINCT SJXZQHDM, SJXZQHMC from XZQH) b
                                               on a.xzqh like b.SJXZQHDM || '%'
                            where TDSYXZ = '闲置低效土地'
                              and SYS_ORG_CODE like 'A03%'
                              and sfsb = 3
                            GROUP BY b.SJXZQHDM) d on c.xzqh = d.xzqh2
    </select>
    <select id="szdkmjByqx" resultType="map">
        select VA as "value", NA as "name", VA2 as "value2"
        from (
                 select ROUND(sum(SJZYTDMJ * 0.0015), 2) VA, b.XJXZQHMC na, b.XJXZQHDM xzqh, b.SJXZQHDM
                 from QC_SSGQZYSYTDQK a
                          RIGHT JOIN (select * from XZQH) b
                                     on a.xzqh = b.XJXZQHDM
                 where SYS_ORG_CODE like 'A03%'
                   and sfsb = 3
                   and xzqh like CONCAT(#{xzqh}, '%')
                 GROUP BY b.XJXZQHDM, b.XJXZQHMC, b.SJXZQHDM
             ) c
                 LEFT JOIN (select ROUND(sum(SJZYTDMJ * 0.0015), 2) VA2, b.XJXZQHDM as xzqh2
                            from QC_SSGQZYSYTDQK a
                                     RIGHT JOIN (select * from XZQH) b
                                                on a.xzqh = b.XJXZQHDM
                            where TDSYXZ = '闲置低效土地'
                              and SYS_ORG_CODE like 'A03%'
                              and sfsb = 3
                              and xzqh like CONCAT(#{xzqh}, '%')
                            GROUP BY b.XJXZQHDM) d on c.xzqh = d.xzqh2
    </select>

    <select id="yjdwhz" resultType="map">
        select a.depart_name as "name", ROUND(sum(b.area), 2) as "value"
        from (select id, depart_name, org_code from sys_depart where org_type = '2' and org_code like 'A04%') a
        LEFT JOIN (
        select sum(CAST(sjzytdmj_m AS DECIMAL(10,2))) as area, sys_ORG_CODE as orgcode
        FROM QC_SSGQZYSYTDQK
        where sys_ORG_CODE like 'A04A%'
        <if test="xzqh !=null">
            and xzqh like CONCAT( #{xzqh},'%')
        </if>
        GROUP BY SYS_ORG_CODE) b on b.orgcode like a.org_code || '%'
        GROUP BY a.depart_name

    </select>


</mapper>
