<?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.SellDataManagementMapper">
	
	<resultMap type="cn.com.countrygarden.bi.main.model.dup.SellDataManagement" id="SellDataManagementMap">
		<result column="id" property="id"/>
		<result column="organ_name" property="organName"/>
		<result column="organ_code" property="organCode"/>
		<result column="comm_name" property="commName"/>
		<result column="comm_id" property="commId"/>
		<result column="room" property="room"/>
		<result column="purchasing_number" property="purchasingNumber"/>
		<result column="purchasing_money" property="purchasingMoney"/>
		<result column="signature_number" property="signatureNumber"/>
		<result column="signature_money" property="signatureMoney"/>
		<result column="purchasing_date" property="purchasingDate"/>
		<result column="signature_date" property="signatureDate"/>
		<result column="verify_type" property="verifytype"/>
		<result column="create_date" property="createDate"/>
		<result column="name" property="name"/>
		<result column="purchasingNumberTotal" property="purchasingNumberTotal"/>
		<result column="purchasingMoneyTotal" property="purchasingMoneyTotal"/>
		<result column="signatureNumberTotal" property="signatureNumberTotal"/>
		<result column="signatureMoneyTotal" property="signatureMoneyTotal"/>
	</resultMap>
	
	<!-- 查询营销数据 -->
	<select id="findAllSiteResourcesControllerLike" parameterType="cn.com.countrygarden.bi.main.model.dup.SellDataManagement" resultMap="SellDataManagementMap">
	SELECT
		tsdm.id,
		tsdm.organ_name,
		tsdm.organ_code,
		tsdm.comm_id,
		tsdm.comm_name,
		tsdm.room,
		tsdm.purchasing_number,
		tsdm.purchasing_money,
		tsdm.signature_number,
		tsdm.signature_money,
		tsdm.purchasing_date,
		tsdm.signature_date,
		tsdm.verify_type,
		tsdm.create_date,
		tsdm.name
	FROM
		tb_sell_data_management AS tsdm
	<where>
		<if test="organName!=null and organName!=''"> AND tsdm.organ_name  LIKE CONCAT('%',#{organName},'%')</if>
		<if test="commName!=null and commName!=''">AND tsdm.comm_name LIKE CONCAT('%',#{commName},'%')</if>
		<if test="room!=null and room!=''">AND tsdm.room LIKE CONCAT('%',#{room},'%')</if>
		<if test="purchasingDateStart!=null and purchasingDateStart!=''">AND tsdm.purchasing_date &gt;#{purchasingDateStart}</if>
		<if test="signatureDateStart!=null and signatureDateStart!=''">AND tsdm.signature_date &gt;#{signatureDateStart}</if>
		<if test="purchasingDateEnd!=null and purchasingDateEnd!=''">AND tsdm.purchasing_date &lt;#{purchasingDateEnd}</if>
		<if test="signatureDateEnd!=null and signatureDateEnd!=''">AND tsdm.signature_date &lt;#{signatureDateEnd}</if>
		<if test="verifytype!=null and verifytype!=''">AND tsdm.verify_type =#{verifytype}</if>
	</where>
	ORDER BY  	tsdm.create_date DESC
	</select>
	
	<!-- 新增营销数据 -->
	<insert id="addSellDataManagement" parameterType="cn.com.countrygarden.bi.main.model.dup.SellDataManagement" >
	INSERT INTO `tb_sell_data_management` (
	`organ_name`,
	`organ_code`,
	`comm_id`,
	`comm_name`,
	`room`,
	`purchasing_number`,
	`purchasing_money`,
	`signature_number`,
	`signature_money`,
	`purchasing_date`,
	`signature_date`,
	`verify_type`,
	`create_date`
	)
	VALUES
	(
		#{organName},
		#{organCode},
		#{commId},
		#{commName},
		#{room},
		#{purchasingNumber},
		#{purchasingMoney},
		#{signatureNumber},
		#{signatureMoney},
		#{purchasingDate},
		#{signatureDate},
		#{verifytype},
		#{createDate}
	)
	</insert>
	
	<!-- 更新营销数据管理 -->
	<update id="updateSellDataManagement" parameterType="cn.com.countrygarden.bi.main.model.dup.SellDataManagement">
	UPDATE `tb_sell_data_management`
			<set>
			<if test="organName !=null and organName!=''">`organ_name` = #{organName},</if>
			<if test="commName !=null and commName!=''">`comm_name` = #{commName},</if>
			<if test="room !=null and room !=''">`room` = #{room},</if>
			<if test="purchasingNumber !=null and purchasingNumber !=0"> `purchasing_number` = #{purchasingNumber},</if> 
			<if test="purchasingMoney !=null and purchasingMoney !=0"> `purchasing_money` = #{purchasingMoney},</if> 
			<if test="signatureNumber !=null and signatureNumber !=0"> `signature_number` = #{signatureNumber},</if> 
			<if test="signatureMoney !=null and signatureMoney !=0"> `signature_money` = #{signatureMoney},</if>  
			<if test="purchasingDates !=null">`purchasing_date` = #{purchasingDates},</if>  
			<if test="signatureDates !=null"> `signature_date` = #{signatureDates},</if> 
			<if test="verifytype !=null"> `verify_type` = #{verifytype},</if> 
			<if test="createDate !=null"> `create_date` = #{createDate}</if> 
			<if test="name !=null and name!=''"> `name` = #{name}</if> 
			</set>
			<where>
				(`id` = #{id})
			</where>
	</update>
	
	<!--删除营销数据管理  -->
  <delete id="deleteSellDataManagement" parameterType="cn.com.countrygarden.bi.main.model.dup.SellDataManagement">
  	DELETE FROM tb_sell_data_management WHERE tb_sell_data_management.id =#{id}
  </delete>
  
  <select id="findtotalNumber" parameterType="cn.com.countrygarden.bi.main.model.dup.SellDataManagement" resultMap="SellDataManagementMap">
	  SELECT
		sum(tsdm.purchasing_number) as purchasingNumberTotal,
		sum(tsdm.purchasing_money) as purchasingMoneyTotal,
		sum(tsdm.signature_number) as signatureNumberTotal,
		sum(tsdm.signature_money)	as signatureMoneyTotal
	FROM
		tb_sell_data_management AS tsdm
	<where>
		<if test="organCode!=null and organCode!=''"> AND tsdm.organ_code=#{organCode}</if>
		<if test="commId!=null and commId!=0">AND tsdm.comm_id=#{commId}</if>
		<if test="room!=null and room!=''">AND tsdm.room LIKE CONCAT('%',#{room},'%')</if>
		<if test="purchasingDateStart!=null and purchasingDateStart!=''">AND tsdm.purchasing_date &gt;#{purchasingDateStart}</if>
		<if test="signatureDateStart!=null and signatureDateStart!=''">AND tsdm.signature_date &gt;#{signatureDateStart}</if>
		<if test="purchasingDateEnd!=null and purchasingDateEnd!=''">AND tsdm.purchasing_date &lt;#{purchasingDateEnd}</if>
		<if test="signatureDateEnd!=null and signatureDateEnd!=''">AND tsdm.signature_date &lt;#{signatureDateEnd}</if>
		<if test="verifytype!=null and verifytype!=''">AND tsdm.verify_type =#{verifytype}</if>
	</where>
  </select>
	
</mapper>