<?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="cn.com.countrygarden.bi.main.dao.wcx.WcxReportMapper">
    <resultMap id="BaseWcxReportDtoMap" type="cn.com.countrygarden.bi.main.dto.wcx.WcxReportDto">
        <result column="innovation_type" property="innovationType"/>
        <result column="baseline" property="baseline"/>
        <result column="classification" property="classification"/>
        <result column="reg_num" property="regNum"/>
        <result column="inn_num" property="innNum"/>
        <result column="thumb_num" property="thumbNum"/>
        <result column="region_name" property="regionName"/>
        <result column="project_name" property="projectName"/>
        <result column="baseline_code" property="baselineCode"/>
    </resultMap>

    <select id="selectProjectReportPageData" resultMap="BaseWcxReportDtoMap">
        SELECT
        rp.region_name,
        rp.project_name,
        tdoc3.regNum reg_num,
        inno.innNum inn_num,
        tt.thumbNum thumb_num
        FROM
        (
        SELECT
        case
        when RIGHT(max(Four_tissue), 4) = '（房产）' OR RIGHT(max(Four_tissue), 4) = '（物业）' then LEFT(max(Four_tissue),char_length(max(Four_tissue))-4)
        else max(Four_tissue)  end region_name,
        pk_org project_name
        FROM
        performance_app.bd_type
        <where>
            <if test="regionName != null and regionName !=''">
                Four_tissue like concat(#{regionName}, '%') or Four_tissue like concat(#{regionName}, '%')
            </if>
        </where>
        GROUP BY
        pk_org
        ) rp
        <!-- 项目下创意数 -->
        LEFT JOIN (
        SELECT
        count(ti.innovation_id) innNum,
        ti.project_name
        FROM
        wcx_innovation ti
        WHERE ti.create_date >= #{startDateStr} AND ti.create_date &lt;= #{endDateStr}
        and ti.audit_status=1
        GROUP BY ti.project_name
        ) inno ON inno.project_name = rp.project_name
        <!-- 项目下在职注册数 -->
        LEFT JOIN (
        SELECT
        count(tdoc.project_name) regNum, project_name
        FROM
        wcx_user u
        JOIN (
        select
        psndoc.ID employee_card_id, psndoc.PK_ORG project_name
        from performance_app.bd_psndoc psndoc
        join performance_app.hi_psnjob psnjob  on psnjob.pk_psndoc=psndoc.pk_psndoc and psnjob.recordnum ='0'
        where
        psnjob.enddate is null			-- 结束日期
        and psnjob.recordnum ='0'		-- 记录序号
        and psnjob.poststat ='Y'		-- 在岗
        and psnjob.endflag ='N'			-- 结束
        and psnjob.lastflag ='Y'		-- 最新记录
        and psnjob.indocflag = 'Y'		-- 是否转档
        ) tdoc ON tdoc.employee_card_id = u.employee_card_id
        WHERE u.`status` != '0' OR u.`status` IS NULL
        GROUP BY tdoc.project_name
        ) tdoc3 ON tdoc3.project_name = rp.project_name
        <!-- 项目下被点赞数 -->
        LEFT JOIN (
        SELECT
        ti.project_name,
        sum(tb.thumb_number) thumbNum
        FROM (SELECT * from wcx_thumb ttb WHERE (ttb.create_date >= #{startDateStr} AND ttb.create_date &lt;= #{endDateStr})) tb
        JOIN wcx_innovation ti on ti.innovation_id = tb.innovation_id
        GROUP BY ti.project_name
        ) tt ON tt.project_name = rp.project_name
        ORDER BY region_name
        <if test="pageSize != -1">
            limit #{pageOffset}, #{pageSize}
        </if>
    </select>

    <select id="selectProjectReportPageCount" resultType="long">
        SELECT
        count(rp.project_name)
        FROM
        (
        SELECT
        max(Four_tissue)  region_name,
        pk_org project_name
        FROM
        performance_app.bd_type
        <where>
            <if test="regionName != null and regionName !=''">
                Four_tissue like concat(#{regionName}, '%') or Four_tissue like concat(#{regionName}, '%')
            </if>
        </where>
        GROUP BY
        pk_org
        ) rp
    </select>

    <select id="selectRegionReportPageData" resultMap="BaseWcxReportDtoMap">
        select
        temp1.region_name,
        temp1.reg_num,
        temp2.inn_num,
        temp3.thumb_num
        from (
        SELECT
        ttp.region_name,
        SUM(ttp.reg_num) reg_num
        FROM (
        SELECT
        rp.region_name,
        rp.project_name,
        tdoc3.regNum reg_num
        FROM
        (
        SELECT
        case
        when RIGHT(max(Four_tissue), 4) = '（房产）' OR RIGHT(max(Four_tissue), 4) = '（物业）' then LEFT(max(Four_tissue),char_length(max(Four_tissue))-4)
        else max(Four_tissue)  end region_name,
        pk_org project_name
        FROM
        performance_app.bd_type
        <where>
            <if test="regionName != null and regionName !=''">
                Four_tissue like concat(#{regionName}, '%')
            </if>
        </where>
        GROUP BY
        pk_org

        ) rp
        <!-- 项目下在职的注册数 -->
        LEFT JOIN (
        SELECT
        count(tdoc.project_name) regNum, project_name
        FROM
        wcx_user u
        JOIN (
        select
        psndoc.ID employee_card_id, psndoc.PK_ORG project_name
        from performance_app.bd_psndoc psndoc
        join performance_app.hi_psnjob psnjob  on psnjob.pk_psndoc=psndoc.pk_psndoc and psnjob.recordnum ='0'
        where
        psnjob.enddate is null			-- 结束日期
        and psnjob.recordnum ='0'		-- 记录序号
        and psnjob.poststat ='Y'		-- 在岗
        and psnjob.endflag ='N'			-- 结束
        and psnjob.lastflag ='Y'		-- 最新记录
        and psnjob.indocflag = 'Y'		-- 是否转档
        ) tdoc ON tdoc.employee_card_id = u.employee_card_id
        WHERE u.`status` != '0' OR u.`status` IS NULL
        GROUP BY tdoc.project_name
        ) tdoc3 ON tdoc3.project_name = rp.project_name
        ) ttp GROUP BY region_name
        ) temp1
        <!-- 区域下创新数 -->
        left join (
        SELECT temp.region_name,count(1) inn_num
        from (
        SELECT
        case
        when RIGHT(region_name, 4) = '（房产）' OR RIGHT(region_name, 4) = '（物业）' then LEFT(region_name,char_length(region_name)-4)
        else region_name  end region_name
        FROM wcx_innovation ti
        WHERE ti.create_date >= #{startDateStr} AND ti.create_date &lt;= #{endDateStr}
        and ti.audit_status=1
        ) temp
        group by region_name
        ) temp2 on temp1.region_name = temp2.region_name
        <!-- 区域下点赞数 -->
        left join (
        select
        region_name,
        sum(thumb_number) thumb_num
        from (
        SELECT
        case
        when RIGHT(region_name, 4) = '（房产）' OR RIGHT(region_name, 4) = '（物业）' then LEFT(region_name,char_length(region_name)-4)
        else region_name  end region_name ,
        tb.thumb_number
        FROM wcx_innovation ti
        JOIN wcx_thumb tb on ti.innovation_id = tb.innovation_id
        WHERE tb.create_date >= #{startDateStr} AND tb.create_date &lt;= #{endDateStr}
        and ti.audit_status=1
        ) temp
        GROUP BY region_name
        ) temp3 on temp1.region_name = temp3.region_name
        ORDER BY thumb_num DESC
        <if test="pageSize != -1">
            limit #{pageOffset}, #{pageSize}
        </if>


    </select>

    <select id="selectRegionReportPageCount" resultType="long">
        SELECT count(ttr.region_name) from (
        SELECT
        region_name
        FROM
        (
        SELECT
        CASE
        WHEN RIGHT (max(Four_tissue), 4) = '（房产）'
        OR RIGHT (max(Four_tissue), 4) = '（物业）' THEN
        LEFT (
        max(Four_tissue),
        char_length(max(Four_tissue)) - 4
        )
        ELSE
        max(Four_tissue)
        END region_name,
        pk_org project_name
        FROM
        performance_app.bd_type
        <where>
            <if test="regionName != null and regionName !=''">
                Four_tissue like concat(#{regionName}, '%') or Four_tissue like concat(#{regionName}, '%')
            </if>
        </where>
        GROUP BY
        pk_org
        ) trp
        GROUP BY
        region_name) ttr
    </select>

    <select id="findBaselineReportPageData" resultMap="BaseWcxReportDtoMap">
        SELECT
            temrp.region_name, ti1.baseline baseline_code, ti1.innNum inn_num, tt.thumbNum thumb_num
        from(
            SELECT Four_tissue region_name  from performance_app.bd_type GROUP BY Four_tissue
            ) temrp
        join(
            SELECT
                 ti.baseline,
                 count(ti.innovation_id) innNum,
                 ti.region_name
            FROM
                wcx_innovation ti
            where ti.create_date <![CDATA[>=]]> #{startDateStr} and ti.create_date <![CDATA[<=]]> #{endDateStr}
            and ti.audit_status=1
            GROUP BY ti.region_name, ti.baseline) ti1 on ti1.region_name = temrp.region_name
        left join(
                    SELECT
                        ti.region_name,
                        sum(tb.thumb_number) thumbNum,
                        ti.baseline
                    FROM wcx_thumb tb
                    JOIN wcx_innovation ti on ti.innovation_id = tb.innovation_id and ti.audit_status=1
                    where tb.create_date <![CDATA[>=]]> #{startDateStr} and tb.create_date <![CDATA[<=]]> #{endDateStr}
                    GROUP BY ti.region_name,ti.baseline
                ) tt on tt.baseline = ti1.baseline and tt.region_name = temrp.region_name
        ORDER BY region_name
    </select>

    <select id="findBaselineReportPageCount" resultType="long">
        SELECT
        COUNT(ti1.region_name)
        from(
        SELECT Four_tissue region_name  from performance_app.bd_type GROUP BY Four_tissue
        ) temrp
        join(
        SELECT
        ti.baseline,
        count(ti.innovation_id) innNum,
        ti.region_name
        FROM
        wcx_innovation ti
        GROUP BY ti.region_name, ti.baseline) ti1 on ti1.region_name = temrp.region_name
    </select>
</mapper>