<?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.WcxWeeklyStatisticsMapper">
    <resultMap id="BaseMap" type="cn.com.countrygarden.bi.main.model.wcx.WcxWeeklyStatistics">
        <id column="id" property="id"/>
        <result column="week" property="week"/>
        <result column="innovation_num" property="innovationNum"/>
        <result column="innovation_num_chain" property="innovationNumChain"/>
        <result column="user_num" property="userNum"/>
        <result column="user_num_chain" property="userNumChain"/>
        <result column="new_user_num" property="newUserNum"/>
        <result column="new_user_chain" property="newUserChain"/>
        <result column="date_range" property="dateRange"/>
        <result column="wekindex" property="wekindex"/>
    </resultMap>

    <select id="innovationNumZhouTongji" resultType="cn.com.countrygarden.bi.main.model.wcx.WcxWeeklyStatistics">

SELECT t.timein AS `week`,t.cou AS innovationNum,FORMAT(CASE WHEN ISNULL((t.couben-t.coush)/t.coush*100) THEN 0 ELSE (t.couben-t.coush)/t.coush*100 END,2) AS innovationNumChain
FROM
(SELECT r1.timein,r1.cou,
CASE WHEN ISNULL(r1.cou) THEN 0 ELSE r1.cou END AS couben,CASE WHEN ISNULL(r2.cou) THEN 0 ELSE r2.cou END AS coush
FROM(SELECT (@rownum := @rownum + 1) AS rownum,
CONCAT(SUBSTRING(DATE_FORMAT(tab.create_date ,'%Y-%u'),1,4),'年第',SUBSTRING(DATE_FORMAT(tab.create_date ,'%Y-%u'),6,7),'周') AS `timein`
,COUNT(tab.innovation_id) AS cou, COUNT(DISTINCT tab.user_id) AS userNu
FROM wcx_innovation tab,(SELECT @rownum := 0) r WHERE 1 = 1 AND tab.audit_status=1

GROUP BY timein) r1
LEFT JOIN (SELECT (@indextr := @indextr + 1) AS rownum,
CONCAT(SUBSTRING(DATE_FORMAT(tab.create_date ,'%Y-%u'),1,4),'年第',SUBSTRING(DATE_FORMAT(tab.create_date ,'%Y-%u'),6,7),'周') AS `timein`
,COUNT(tab.innovation_id) AS cou, COUNT(DISTINCT tab.user_id) AS userNu
FROM wcx_innovation tab,(SELECT @indextr := 0) r WHERE 1 = 1 AND tab.audit_status=1

GROUP BY timein) r2 ON  r1.rownum = r2.rownum+1
  ) t
    </select>

    <update id="updateWeek">
         UPDATE `wcx_weekly_statistics`
         SET  `innovation_num` = #{innovationNum},`innovation_num_chain` = #{innovationNumChain}
         WHERE `week` = #{week};
    </update>

    <update id="updateWeeChain">
         UPDATE `wcx_weekly_statistics`
         SET  `user_num_chain` = #{userNumChain},`new_user_chain` = #{newUserChain}
         WHERE `week` = #{week};
    </update>

    <select id="innovationNumYueTongji" resultType="cn.com.countrygarden.bi.main.model.wcx.WcxWeeklyStatistics">
         SELECT t.timein AS `week`,t.cou AS innovationNum, FORMAT(CASE WHEN ISNULL((t.couben-t.coush)/t.coush*100) THEN 0 ELSE (t.couben-t.coush)/t.coush*100 END,2) AS innovationNumChain
FROM
(SELECT r1.timein,r1.cou,
CASE WHEN ISNULL(r1.cou) THEN 0 ELSE r1.cou END AS couben,CASE WHEN ISNULL(r2.cou) THEN 0 ELSE r2.cou END AS coush
FROM(SELECT (@rownum := @rownum + 1) AS rownum,
CONCAT(SUBSTRING(DATE_FORMAT(tab.create_date ,'%Y-%m'),1,4),'年第',SUBSTRING(DATE_FORMAT(tab.create_date ,'%Y-%m'),6,7),'月') AS `timein`
,COUNT(tab.innovation_id) AS cou, COUNT(DISTINCT tab.user_id) AS userNu
FROM wcx_innovation tab,(SELECT @rownum := 0) r WHERE 1 = 1
AND tab.audit_status=1

GROUP BY timein) r1
LEFT JOIN (SELECT (@indextr := @indextr + 1) AS rownum,
CONCAT(SUBSTRING(DATE_FORMAT(tab.create_date ,'%Y-%m'),1,4),'年第',SUBSTRING(DATE_FORMAT(tab.create_date ,'%Y-%m'),6,7),'月') AS `timein`
,COUNT(tab.innovation_id) AS cou, COUNT(DISTINCT tab.user_id) AS userNu
FROM wcx_innovation tab,(SELECT @indextr := 0) r WHERE 1 = 1
AND tab.audit_status=1

GROUP BY timein) r2 ON  r1.rownum = r2.rownum+1
  ) t
    </select>

    <select id="StatisticsNumZhouTongji" resultType="cn.com.countrygarden.bi.main.model.wcx.WcxWeeklyStatistics">
   SELECT tab.week AS `week`
,tab.user_num AS userNum,tab.new_user_num AS newUserNum
FROM wcx_weekly_statistics tab WHERE
 tab.wekindex=3
 ORDER BY tab.week
    </select>

    <select id="StatisticsNumZhouTongjican" resultType="cn.com.countrygarden.bi.main.model.wcx.WcxWeeklyStatistics">
         SELECT t.week AS `week`,t.cou,FORMAT(CASE WHEN ISNULL((t.couben-t.coush)/t.coush*100) THEN 0 ELSE (t.couben-t.coush)/t.coush*100 END,2) AS userNumChain,
t.nucou,
FORMAT(CASE WHEN ISNULL((t.nucouben-t.nucoush)/t.nucoush*100) THEN 0 ELSE (t.nucouben-t.nucoush)/t.nucoush*100 END,2) AS newUserChain
FROM
(SELECT r1.week, r1.cou,r1.nucou,
CASE WHEN ISNULL(r1.cou) THEN 0 ELSE r1.cou END AS couben,CASE WHEN ISNULL(r2.cou) THEN 0 ELSE r2.cou END AS coush,
CASE WHEN ISNULL(r1.nucou) THEN 0 ELSE r1.nucou END AS nucouben,CASE WHEN ISNULL(r2.nucou) THEN 0 ELSE r2.nucou END AS nucoush
FROM(SELECT (@rownum := @rownum + 1) AS rownum,tab.week AS `week`
,tab.user_num AS cou,tab.new_user_num AS nucou
FROM wcx_weekly_statistics tab,(SELECT @rownum := 0) r WHERE 1 = 1
AND tab.wekindex=#{wekindex}
) r1
LEFT JOIN (SELECT (@INDEX := @INDEX + 1) AS rownum,tab.week AS `week`
,tab.user_num AS cou,tab.new_user_num AS nucou
FROM wcx_weekly_statistics tab,(SELECT @INDEX := 0) r WHERE 1 = 1
AND tab.wekindex=#{wekindex}
) r2 ON  r1.rownum = r2.rownum+1
   ) t
    </select>

    <select id="innovationNumDateTongji" resultType="cn.com.countrygarden.bi.main.model.wcx.WcxWeeklyStatistics">
/*        SELECT t.timein AS `week`,t.cou AS innovationNum,
FORMAT(CASE WHEN ISNULL((t.couben-t.coush)/t.coush*100) THEN 0 ELSE (t.couben-t.coush)/t.coush*100 END,2) AS innovationNumChain
FROM
(SELECT r1.timein,r1.cou,
CASE WHEN ISNULL(r1.cou) THEN 0 ELSE r1.cou END AS couben,CASE WHEN ISNULL(r2.cou) THEN 0 ELSE r2.cou END AS coush
FROM(SELECT (@rownum := @rownum + 1) AS rownum,
DATE_FORMAT(tab.create_date ,'%Y') AS `timein`
,COUNT(tab.innovation_id) AS cou
FROM wcx_innovation tab,(SELECT @rownum := 0) r WHERE 1 = 1
AND tab.audit_status=1

GROUP BY timein) r1
LEFT JOIN (SELECT (@indextr := @indextr + 1) AS rownum,
DATE_FORMAT(tab.create_date ,'%Y') AS `timein`
,COUNT(tab.innovation_id) AS cou
FROM wcx_innovation tab,(SELECT @indextr := 0) r WHERE 1 = 1
AND tab.audit_status=1

GROUP BY timein) r2 ON  r1.rownum = r2.rownum+1
  ) t*/
  SELECT
DATE_FORMAT(tab.create_date ,'%Y') AS `week`
,COUNT(tab.innovation_id) AS innovationNum
FROM wcx_innovation tab WHERE
 tab.audit_status=1
GROUP BY `week`
    </select>

    <select id="statistime" resultMap="BaseMap">
         SELECT DISTINCT `week`,`innovation_num`,`innovation_num_chain`,`user_num`,
 `user_num_chain`,`new_user_num`,`new_user_chain`,`date_range`,`wekindex`
FROM `wcx_weekly_statistics`
WHERE wekindex=#{wekindex}
        <if test="weekList != null and weekList.size()>0">
            and `week` in
            <foreach collection="weekList" index="index" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        </if>

        <!--<if test="pageSize != '-1'">
            limit #{pageOffset}, #{pageSize}
        </if>-->
    </select>

    <select id="statistimeCount" resultType="long">
          SELECT count(DISTINCT week) FROM `wcx_weekly_statistics`
         WHERE wekindex=#{wekindex} AND `week`=#{week}
    </select>

    <select id="statistimeVO" resultType="cn.com.countrygarden.bi.main.dto.wcx.WcxWeeklyStatisticsVO">
        SELECT DISTINCT `date_range` as `week`,`innovation_num` as innovationNum,`innovation_num_chain` as innovationNumChain,`user_num` as userNum,
        `user_num_chain` as userNumChain,`new_user_num` as newUserNum,`new_user_chain` as newUserChain
        FROM `wcx_weekly_statistics`
        WHERE wekindex=#{wekindex}
        <if test="weekList != null and weekList.size()>0">
            and `week` in
            <foreach collection="weekList" index="index" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        </if>
    </select>

</mapper>
