<?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.dup.BuoyDataMapper">
  <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.dup.BuoyData">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="organ_code" jdbcType="VARCHAR" property="organCode" />
    <result column="organ_name" jdbcType="VARCHAR" property="organName" />
    <result column="comm_id" jdbcType="VARCHAR" property="commId" />
    <result column="comm_name" jdbcType="VARCHAR" property="commName" />
    <result column="visitor_num" jdbcType="BIGINT" property="visitorNum" />
    <result column="recognition_percentage" jdbcType="DOUBLE" property="recognitionPercentage" />
    <result column="tradition_visitors" jdbcType="BIGINT" property="traditionVisitors" />
    <result column="appointment_visitors" jdbcType="BIGINT" property="appointmentVisitors" />
    <result column="appointment_by_device" jdbcType="BIGINT" property="appointmentByDevice" />
    <result column="other_visitors" jdbcType="BIGINT" property="otherVisitors" />
    <result column="visit_customer_num" jdbcType="BIGINT" property="visitCustomerNum" />
    <result column="visit_satisfaction" jdbcType="DOUBLE" property="visitSatisfaction" />
    <result column="status" jdbcType="VARCHAR" property="status" />
    <result column="begin_date" jdbcType="DATE" property="beginDate" />
    <result column="end_date" jdbcType="DATE" property="endDate" />
    <result column="created_date" jdbcType="TIMESTAMP" property="createdDate" javaType="java.util.Date" />
    <result column="created_by" jdbcType="VARCHAR" property="createdBy" />
    <result column="updated_date" jdbcType="TIMESTAMP" property="updatedDate" javaType="java.util.Date" />
    <result column="updated_by" jdbcType="VARCHAR" property="updatedBy" />
  </resultMap>

  <resultMap id="QueryResultmap" type="cn.com.countrygarden.bi.main.model.dup.BuoyQueryData">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="manager_num" jdbcType="BIGINT" property="managerNum" />
    <result column="phone_percentage" jdbcType="DOUBLE" property="phonePercentage" />
    <result column="manager_recognition_percentage" jdbcType="DOUBLE" property="managerRecognitionPercentage" />

    <result column="wechat_percentage" jdbcType="DOUBLE" property="wechatPercentage" />
    <result column="wechat_cover_percentage" jdbcType="DOUBLE" property="wechatCoverPercentage" />

    <result column="satisfaction" jdbcType="DOUBLE" property="satisfaction" />
    <result column="received_quick_events" jdbcType="BIGINT" property="receivedQuickEvents" />
    <result column="received_repair_events" jdbcType="BIGINT" property="receivedRepairEvents" />
    <result column="received_complaint_events" jdbcType="BIGINT" property="receivedComplaintEvents" />
    <result column="solved_quick_events" jdbcType="BIGINT" property="solvedQuickEvents" />
    <result column="solved_repair_events" jdbcType="BIGINT" property="solvedRepairEvents" />
    <result column="solved_complaint_events" jdbcType="BIGINT" property="solvedComplaintEvents" />
    <result column="request_received" jdbcType="BIGINT" property="requestReceived" />
    <result column="request_solved" jdbcType="BIGINT" property="requestSolved" />
    <result column="wechat_praise" jdbcType="BIGINT" property="wechatPraise" />
    <result column="letter_praise" jdbcType="BIGINT" property="letterPraise" />
    <result column="banner_num" jdbcType="BIGINT" property="bannerNum" />
    <result column="other_praise" jdbcType="BIGINT" property="otherPraise" />
    <result column="wechat_subscription" jdbcType="BIGINT" property="wechatSubscription" />


    <result column="organ_code" jdbcType="VARCHAR" property="organCode" />
    <result column="organ_name" jdbcType="VARCHAR" property="organName" />
    <result column="comm_id" jdbcType="VARCHAR" property="commId" />
    <result column="comm_name" jdbcType="VARCHAR" property="commName" />
    <result column="visitor_num" jdbcType="BIGINT" property="visitorNum" />
    <result column="recognition_percentage" jdbcType="DOUBLE" property="recognitionPercentage" />
    <result column="tradition_visitors" jdbcType="BIGINT" property="traditionVisitors" />
    <result column="appointment_visitors" jdbcType="BIGINT" property="appointmentVisitors" />
    <result column="appointment_by_device" jdbcType="BIGINT" property="appointmentByDevice" />
    <result column="other_visitors" jdbcType="BIGINT" property="otherVisitors" />
    <result column="visit_customer_num" jdbcType="BIGINT" property="visitCustomerNum" />
    <result column="visit_satisfaction" jdbcType="DOUBLE" property="visitSatisfaction" />
    <result column="status" jdbcType="VARCHAR" property="status" />
    <result column="begin_date" jdbcType="DATE" property="beginDate" />
    <result column="end_date" jdbcType="DATE" property="endDate" />
    <result column="created_date" jdbcType="TIMESTAMP" property="createdDate" javaType="java.util.Date"/>
    <result column="created_by" jdbcType="VARCHAR" property="createdBy" />
    <result column="updated_date" jdbcType="TIMESTAMP" property="updatedDate" javaType="java.util.Date"/>
    <result column="updated_by" jdbcType="VARCHAR" property="updatedBy" />
  </resultMap>



  <sql id="organCodeWhereCase">
    <if test="organCode != null and organCode != ''">
      AND organ_code = #{organCode}
    </if>
    <if test="commId != null and commId != ''">
      AND comm_id = #{commId}
    </if>
    <choose>
      <when test="organCodes != null and commIds != null">
        AND ( organ_code IN
        <foreach item="item" index="index" collection="organCodes" open="(" separator="," close=")">
          #{item}
        </foreach>
        OR comm_id IN
        <foreach item="item" index="index" collection="commIds" open="(" separator="," close=")">
          #{item}
        </foreach>
        )
      </when>
      <when test="organCodes != null and commIds == null">
        AND organ_code IN
        <foreach item="item" index="index" collection="organCodes" open="(" separator="," close=")">
          #{item}
        </foreach>
      </when>
      <when test="organCodes == null and commIds != null">
        AND comm_id IN
        <foreach item="item" index="index" collection="commIds" open="(" separator="," close=")">
          #{item}
        </foreach>
      </when>
    </choose>
  </sql>

  <sql id="summaryQueryByWeek">
    <![CDATA[
      SELECT COUNT(DISTINCT organ_code) AS organ_name,
      COUNT(comm_id) AS comm_name,
      IFNULL(SUM(visitor_num),0) AS visitor_num,
      IFNULL(SUM(recognition_percentage),0) AS recognition_percentage,
      IFNULL(SUM(tradition_visitors),0) AS tradition_visitors,
      IFNULL(SUM(appointment_visitors),0) AS appointment_visitors,
      IFNULL(SUM(appointment_by_device),0) AS appointment_by_device,
      IFNULL(SUM(other_visitors),0) AS other_visitors,
      IFNULL(SUM(visit_customer_num),0) AS visit_customer_num,
      IFNULL(SUM(visit_satisfaction),0) AS visit_satisfaction,
      begin_date,
      end_date
    ]]>
    <![CDATA[
      FROM fenghuang_buoy_data
      WHERE begin_date >= #{beginDate} AND begin_date < #{endDate}
        AND end_date > #{beginDate} AND end_date <= #{endDate}
      AND status<>'unfill'
    ]]>
    <include refid="organCodeWhereCase"></include>

    <![CDATA[
      GROUP BY begin_date, end_date
    ]]>
  </sql>

  <sql id="sumManagerDataByWeek">
    <![CDATA[
        SELECT COUNT(manager_name) AS manager_num,
        IFNULL(SUM(phone_percentage),0) as phone_percentage,
        IFNULL(SUM(wechat_percentage),0) as wechat_percentage,
        IFNULL(SUM(wechat_cover_percentage),0) as wechat_cover_percentage,
        IFNULL(SUM(recognition_percentage),0) as manager_recognition_percentage,
        IFNULL(SUM(satisfaction),0) as satisfaction,
        IFNULL(SUM(request_received),0) as request_received,
        IFNULL(SUM(request_solved),0) as request_solved,
        IFNULL(SUM(wechat_praise),0) as wechat_praise,
        IFNULL(SUM(letter_praise),0) as letter_praise,
        IFNULL(SUM(banner_num),0) as banner_num,
        IFNULL(SUM(other_praise),0) as other_praise,
        IFNULL(SUM(wechat_subscription),0) as wechat_subscription,
        IFNULL(SUM(received_quick_events),0) AS received_quick_events,
        IFNULL(SUM(received_repair_events),0) AS received_repair_events,
        IFNULL(SUM(received_complaint_events),0) AS received_complaint_events,
        IFNULL(SUM(solved_quick_events),0) AS solved_quick_events,
        IFNULL(SUM(solved_repair_events),0) AS solved_repair_events,
        IFNULL(SUM(solved_complaint_events),0) AS solved_complaint_events,
        begin_date,
        end_date
        FROM fenghuang_manager_data
        WHERE begin_date >= #{beginDate} AND begin_date < #{endDate}
        AND end_date > #{beginDate} AND end_date <= #{endDate}
        AND status<>'unfill'
    ]]>
    <include refid="organCodeWhereCase"></include>
    <![CDATA[
      GROUP BY begin_date, end_date
    ]]>
  </sql>

  <sql id="sumManagerData">
    <![CDATA[
      SELECT
        COUNT(manager_name) AS manager_num,
        IFNULL(SUM(phone_percentage),0) as phone_percentage,
        IFNULL(SUM(wechat_percentage),0) as wechat_percentage,
        IFNULL(SUM(wechat_cover_percentage),0) as wechat_cover_percentage,
        IFNULL(SUM(recognition_percentage),0) as manager_recognition_percentage,
        IFNULL(SUM(satisfaction),0) as satisfaction,
        IFNULL(SUM(request_received),0) as request_received,
        IFNULL(SUM(request_solved),0) as request_solved,
        IFNULL(SUM(wechat_praise),0) as wechat_praise,
        IFNULL(SUM(letter_praise),0) as letter_praise,
        IFNULL(SUM(banner_num),0) as banner_num,
        IFNULL(SUM(other_praise),0) as other_praise,
        IFNULL(SUM(wechat_subscription),0) as wechat_subscription,
        IFNULL(SUM(received_quick_events),0) AS received_quick_events,
        IFNULL(SUM(received_repair_events),0) AS received_repair_events,
        IFNULL(SUM(received_complaint_events),0) AS received_complaint_events,
        IFNULL(SUM(solved_quick_events),0) AS solved_quick_events,
        IFNULL(SUM(solved_repair_events),0) AS solved_repair_events,
        IFNULL(SUM(solved_complaint_events),0) AS solved_complaint_events,
        organ_code,
        comm_id,
        begin_date,
        end_date
      FROM fenghuang_manager_data
      WHERE begin_date >= #{beginDate} AND begin_date < #{endDate}
      AND end_date > #{beginDate} AND end_date <= #{endDate}
      AND status<>'unfill'
    ]]>
    <include refid="organCodeWhereCase"></include>
    <![CDATA[
      GROUP BY organ_code,comm_id,begin_date,end_date
    ]]>
  </sql>

  <sql id="grouldByDate">
    <![CDATA[
        SELECT COUNT(*) AS num,
        begin_date,
        end_date
        FROM fenghuang_buoy_data
        WHERE begin_date >= #{beginDate} AND begin_date < #{endDate}
        AND end_date > #{beginDate} AND end_date <= #{endDate}
        GROUP BY begin_date, end_date
      ]]>
  </sql>
  <select id="queryByDateRang" resultMap="QueryResultmap">
    <![CDATA[
      SELECT b.*,
        IFNULL(m.manager_num,0) AS manager_num,
        IFNULL(m.phone_percentage,0) AS phone_percentage,
        IFNULL(m.manager_recognition_percentage,0) AS manager_recognition_percentage,
        IFNULL(m.wechat_percentage,0) AS wechat_percentage,
        IFNULL(m.wechat_cover_percentage,0) AS wechat_cover_percentage,
        IFNULL(m.satisfaction,0) AS satisfaction,
        IFNULL(m.received_quick_events,0) AS received_quick_events,
        IFNULL(m.received_repair_events,0) AS received_repair_events,
        IFNULL(m.received_complaint_events,0) AS received_complaint_events,
        IFNULL(m.solved_quick_events,0) AS solved_quick_events,
        IFNULL(m.solved_repair_events,0) AS solved_repair_events,
        IFNULL(m.solved_complaint_events,0) AS solved_complaint_events,
        IFNULL(m.request_received,0) AS request_received,
        IFNULL(m.request_solved,0) AS request_solved,
        IFNULL(m.wechat_praise,0) AS wechat_praise,
        IFNULL(m.letter_praise,0) AS letter_praise,
        IFNULL(m.banner_num,0) AS banner_num,
        IFNULL(m.other_praise,0) AS other_praise,
        IFNULL(m.wechat_subscription,0) AS wechat_subscription
      FROM (
        SELECT * from fenghuang_buoy_data
        WHERE begin_date >= #{beginDate} AND begin_date < #{endDate}
        AND end_date > #{beginDate} AND end_date <= #{endDate}
    ]]>
    <if test="status != null and status != ''">
      AND status = #{status}
    </if>
    <include refid="organCodeWhereCase"></include>
    <![CDATA[
      ) AS b
      LEFT JOIN (
    ]]>
    <include refid="sumManagerData"></include>
    <![CDATA[
      ) AS m ON m.organ_code = b.organ_code
        AND m.comm_id = b.comm_id
        AND m.begin_date = b.begin_date
        AND m.end_date = b.end_date
     ORDER BY b.status ASC, b.organ_code ASC ,b.comm_id ASC ,b.begin_date
    ]]>

  </select>

  <select id="summaryByWeek" resultMap="QueryResultmap">
    <![CDATA[
      SELECT IFNULL(b.organ_name,0) AS organ_name,
        IFNULL(b.comm_name,0) AS comm_name,
        IFNULL(b.visitor_num,0) AS visitor_num,
        IFNULL(b.recognition_percentage,0) AS recognition_percentage,
        IFNULL(b.tradition_visitors,0) AS tradition_visitors,
        IFNULL(b.appointment_visitors,0) AS appointment_visitors,
        IFNULL(b.appointment_by_device,0) AS appointment_by_device,
        IFNULL(b.other_visitors,0) AS other_visitors,
        IFNULL(b.visit_customer_num,0) AS visit_customer_num,
        IFNULL(b.visit_satisfaction,0) AS visit_satisfaction,
        d.begin_date,
        d.end_date,
        IFNULL(m.manager_recognition_percentage,0) AS manager_recognition_percentage,
        IFNULL(m.manager_num,0) AS manager_num,
        IFNULL(m.phone_percentage,0) AS phone_percentage,
        IFNULL(m.wechat_percentage,0) AS wechat_percentage,
        IFNULL(m.wechat_cover_percentage,0) AS wechat_cover_percentage,
        IFNULL(m.satisfaction, 0) AS satisfaction,
        IFNULL(m.received_quick_events, 0) AS received_quick_events,
        IFNULL(m.received_repair_events,0) AS received_repair_events,
        IFNULL(m.received_complaint_events,0) AS received_complaint_events,
        IFNULL(m.solved_quick_events,0) AS solved_quick_events,
        IFNULL(m.solved_repair_events,0) AS solved_repair_events,
        IFNULL(m.solved_complaint_events,0) AS solved_complaint_events,
        IFNULL(m.request_received,0) AS request_received,
        IFNULL(m.request_solved,0) AS request_solved,
        IFNULL(m.wechat_praise,0) AS wechat_praise,
        IFNULL(m.letter_praise,0) AS letter_praise,
        IFNULL(m.banner_num,0) AS banner_num,
        IFNULL(m.other_praise,0) AS other_praise,
        IFNULL(m.wechat_subscription,0) AS wechat_subscription
      FROM (
    ]]>
    <include refid="grouldByDate"></include>
    <![CDATA[
      ) AS d
      LEFT JOIN (
    ]]>
    <include refid="summaryQueryByWeek"></include>
    <![CDATA[
      ) AS b on b.begin_date = d.begin_date AND b.end_date = d.end_date
      LEFT JOIN (
    ]]>
    <include refid="sumManagerDataByWeek"></include>
    <![CDATA[
      ) AS m ON b.begin_date = m.begin_date AND b.end_date = m.end_date
      ORDER BY d.begin_date ASC
    ]]>
  </select>

</mapper>