<?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.marketing.CommReportWeeklyMapper">
  <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.marketing.CommReportWeekly">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="comm_id" jdbcType="INTEGER" property="commId" />
    <result column="woy" jdbcType="INTEGER" property="woy" />
    <result column="year" jdbcType="INTEGER" property="year" />
    <result column="gmt_create" jdbcType="TIMESTAMP" property="gmtCreate" />
    <result column="create_by" jdbcType="VARCHAR" property="createBy" />
    <result column="gmt_update" jdbcType="TIMESTAMP" property="gmtUpdate" />
    <result column="modified_by" jdbcType="VARCHAR" property="modifiedBy" />
    <result column="start_date" jdbcType="DATE" property="startDate" />
    <result column="end_date" jdbcType="DATE" property="endDate" />
    <result column="content" jdbcType="LONGVARCHAR" property="content" />
    <result column="next_week_plan" jdbcType="LONGVARCHAR" property="nextWeekPlan" />
    <result column="assistance_item" jdbcType="LONGVARCHAR" property="assistanceItem" />
  </resultMap>

    <insert id="insertOrUpdate" parameterType="Map">
        INSERT INTO comm_report_weekly
        (comm_id, woy, year, content, next_week_plan, assistance_item, create_by, gmt_create, start_date, end_date)
        VALUES
        (
        #{commId, jdbcType = INTEGER},
        #{weekOfYear, jdbcType = INTEGER},
        #{year, jdbcType = INTEGER},
        #{workContent, jdbcType = VARCHAR},
        #{workPlan, jdbcType = VARCHAR},
        #{currentAssistanceIssues, jdbcType = VARCHAR},
        #{userCode, jdbcType = VARCHAR},
        #{date, jdbcType = TIMESTAMP},
        #{startDate, jdbcType = DATE},
        #{endDate, jdbcType = DATE}
        )
        ON DUPLICATE KEY UPDATE
        content = #{workContent, jdbcType = VARCHAR},
        next_week_plan = #{workPlan, jdbcType = VARCHAR},
        assistance_item = #{currentAssistanceIssues, jdbcType = VARCHAR},
        gmt_update = #{date, jdbcType = TIMESTAMP},
        modified_by = #{userCode, jdbcType = VARCHAR},
        start_date = #{startDate, jdbcType = DATE},
        end_date = #{endDate, jdbcType = DATE}
    </insert>

    <select id="search" parameterType="Map" resultMap="BaseResultMap">
        SELECT id, comm_id, woy, year, content, next_week_plan, assistance_item, start_date, end_date
        FROM comm_report_weekly AS r
        <where>
            <if test="commId > 0">
                AND r.comm_id = #{commId, jdbcType = INTEGER}
            </if>
            <if test="weekOfYear > 0">
                AND r.woy = #{weekOfYear, jdbcType = INTEGER}
            </if>
            <if test="year > 0">
                AND r.year = #{year, jdbcType = INTEGER}
            </if>
        </where>
        ORDER BY r.id DESC
        <if test="limit > 0">
            limit #{limit, jdbcType = INTEGER}
        </if>
    </select>

    <select id="getCurrentReport" parameterType="Map" resultMap="BaseResultMap">
        SELECT id, comm_id, woy, content, next_week_plan, assistance_item, start_date, end_date
        FROM comm_report_weekly AS r
        <where>
            <if test="commId > 0">
                AND r.comm_id = #{commId, jdbcType = INTEGER}
            </if>
            <if test="weekOfYear > 0">
                AND r.woy = #{weekOfYear, jdbcType = INTEGER}
            </if>
            <if test="year > 0">
                AND r.year = #{year, jdbcType = INTEGER}
            </if>
        </where>
        ORDER BY r.woy DESC
        limit 1
    </select>


  <resultMap id="SummaryResultMap" type="cn.com.countrygarden.bi.main.model.marketing.CommReportWeeklySummary">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="comm_id" jdbcType="INTEGER" property="commId" />
    <result column="start_date" jdbcType="DATE" property="startDate" />
    <result column="end_date" jdbcType="DATE" property="endDate" />
    <result column="content" jdbcType="LONGVARCHAR" property="weekWorkContent" />
    <result column="next_week_plan" jdbcType="LONGVARCHAR" property="nextWeekPlan" />
    <result column="assistance_item" jdbcType="LONGVARCHAR" property="assistantContent" />
    <result column="proposal_organ_id" jdbcType="VARCHAR" property="organId" />
    <result column="name" jdbcType="VARCHAR" property="projectName" />
    <result column="comm_num" jdbcType="VARCHAR" property="serialNumber"/>
    <result column="project_type_name" jdbcType="VARCHAR" property="projectType" />
    <result column="corp_type_name" jdbcType="VARCHAR" property="cooperationTypes" />
    <result column="contractor_name" jdbcType="VARCHAR" property="contact" />
    <result column="area" jdbcType="DECIMAL" property="planningArea" />
    <result column="status" jdbcType="VARCHAR" property="state" />
    <result column="history" jdbcType="INTEGER" property="historyCount" />
  </resultMap>

  <select id="fincSummaryByWeek" resultMap="SummaryResultMap">
    <![CDATA[
      SELECT w.id,
        w.comm_id,
        w.start_date,
        w.end_date,
        w.content,
        w.next_week_plan,
        w.assistance_item,
        c.proposal_organ_id,
        c.contractor_name,
        c.name,
        c.comm_num,
        p.name AS  project_type_name,
        co.name AS corp_type_name,
        n.name as project_nature_name,
        c.area,
        c.status,
        IFNULL(h.history,0) AS history
      FROM comm AS c
      LEFT JOIN  comm_report_weekly AS w ON c.id = w.comm_id AND w.start_date = #{startDate} AND w.end_date = #{endDate}
      LEFT JOIN project_type AS p ON p.code = c.project_type AND p.is_delete = 0
      LEFT JOIN cooperation_type AS co ON co.code = c.corp_type AND co.is_delete = 0
      LEFT JOIN project_nature AS n ON n.code = c.project_nature AND n.is_delete=0
      LEFT JOIN (
        SELECT count(*) as history,comm_id FROM comm_operation_history AS h
        WHERE h.name <> '前介'
        AND h.gmt_create >= #{startDate} AND h.gmt_create <= #{endDate}
        group by comm_id
      ) AS h ON c.id = h.comm_id
      WHERE c.is_delete=0
    ]]>
    <if test="searchKey !=null and searchKey != ''">
      AND (c.name like concat(concat('%',#{searchKey}),'%') OR c.comm_num LIKE concat(concat('%',#{searchKey}),'%') )
    </if>
    <if test="organId!=null and organId!=''">
      AND c.proposal_organ_id = #{organId}
    </if>
    <if test="contractorName!=null and contractorName!=''">
      AND c.contractor_name like concat(concat('%',#{contractorName}),'%')
    </if>
    <choose>
        <when test="state!=null and state!=''">
            <![CDATA[
              AND c.status = #{state}
            ]]>
        </when>
        <otherwise>
            <![CDATA[
              AND (c.status='prophase' OR c.status='candidate' OR c.status='passed')
            ]]>
        </otherwise>
    </choose>
    <![CDATA[
      ORDER BY c.id DESC
    ]]>
  </select>
</mapper>