<?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.ContractStatisticalMapper">
	 <!-- 合同录入统计管理映射 -->
	  <resultMap id="ContractStatisticalMap" type="cn.com.countrygarden.bi.main.model.dup.ContractStatistical">
	  	<result column="ContID" property="ContId"/>
		<result column="BipNum" property="BipNum"/>
		<result column="ContName" property="ContName"/>
		<result column="IsAuditing" property="IsAuditing"/>
		<result column="RegDate" property="RegDate"/>
		<result column="CommName" property="CommName"/>
		<result column="OrganCode" property="OrganCode"/>
		<result column="OrganName" property="OrganName"/>
		<result column="no_import" property="noImport"/>
	  </resultMap>
	  
  <!-- 查询所有的未录入的BIP流程编号 -->
  <select id="findBIPnumber" parameterType="cn.com.countrygarden.bi.main.model.dup.ContractStatistical" resultType="string">
  		SELECT
			tbc.BipNum	
		FROM
			tb_dup_contract AS tbc
		<where>
		<if test="BipNum !=null and BipNum != ''">AND tbc.BipNum LIKE CONCAT('%',#{BipNum},'%')</if>	
		<if test="ContName !=null and ContName != ''">AND tbc.ContName LIKE CONCAT('%',#{ContName},'%')</if>	
		<if test="IsAuditing !=null and IsAuditing != ''">AND tbc.IsAuditing LIKE CONCAT('%',#{IsAuditing},'%')</if>	
		<if test="noImport !=null and noImport != ''">AND tbc.no_import LIKE CONCAT('%',#{noImport},'%')</if>	
		<if test="RegDate !=null">AND tbc.RegDate LIKE CONCAT('%',#{RegDate},'%')</if>	
		<if test="OrganCode !=null and OrganCode !=''" >AND tbc.OrganCode LIKE CONCAT('%',#{OrganCode},'%')</if>
		<if test="CommId !=null and CommId !=0 ">AND tbc.CommID=#{CommId}</if>
		<if test="finalJudgmentDate !=null and finalJudgmentDate !='' ">AND DATE_FORMAT(tbc.FinalJudgmentDate, "%Y-%m-%d") =#{finalJudgmentDate}</if>
		<if test="businessType !=null and businessType !='' ">AND tbc.BusinessType=#{businessType}</if>
		</where>	
		order by tbc.RegDate  desc
  </select>
  
  <!-- 合同统计录入查询 -->
  <select id="contractStatisticalSelect" parameterType="cn.com.countrygarden.bi.main.model.dup.ContractStatistical" resultMap="ContractStatisticalMap">
  		SELECT
			tbc.BipNum,
			tbc.ContName,
			tbc.IsAuditing,
			tbc.ContID,
			tbc.RegDate,
			tbc.CommName,
			tbc.no_import,
			tbc.OrganCode,
			tbc.OrganName,
			tbc.FinalJudgmentDate As finalJudgmentDate,
			tbc.BusinessType As businessType
		FROM
			tb_dup_contract AS tbc
		<where>
		<if test="BipNum !=null and BipNum != ''">AND tbc.BipNum LIKE CONCAT('%',#{BipNum},'%')</if>	
		<if test="ContName !=null and ContName != ''">AND tbc.ContName LIKE CONCAT('%',#{ContName},'%')</if>	
		<if test="IsAuditing !=null and IsAuditing != ''">AND tbc.IsAuditing LIKE CONCAT('%',#{IsAuditing},'%')</if>	
		<if test="noImport !=null and noImport != ''">AND tbc.no_import LIKE CONCAT('%',#{noImport},'%')</if>	
		<if test="RegDateStart !=null and RegDateStart!=''">AND tbc.RegDate &gt;= #{RegDateStart}</if>	
		<if test="RegDateEnd !=null and RegDateEnd!=''">AND tbc.RegDate &lt;= #{RegDateEnd}</if>
		<if test="CommName !=null and CommName != ''">AND tbc.CommName LIKE CONCAT('%',#{CommName},'%')</if>
		<if test="OrganCode !=null and OrganCode !=''" >AND tbc.OrganCode LIKE CONCAT('%',#{OrganCode},'%')</if>
		<if test="CommId !=null and CommId!=0">AND tbc.CommID = #{CommId}</if>
		<if test="finalJudgmentDate !=null and finalJudgmentDate !='' ">AND DATE_FORMAT(tbc.FinalJudgmentDate, "%Y-%m-%d")=#{finalJudgmentDate}</if>
		<if test="businessType !=null and businessType !='' ">AND tbc.BusinessType=#{businessType}</if>
		</where>	
		order by tbc.FinalJudgmentDate  desc
  </select>
  
  <!-- 合同统计录入查询 -->
  <select id="contractStatisticalSelect1" parameterType="cn.com.countrygarden.bi.main.model.dup.ContractStatistical" resultMap="ContractStatisticalMap">
  		SELECT
			tbc.BipNum,
			tbc.ContName,
			tbc.IsAuditing,
			tbc.ContID,
			tbc.RegDate,
			tbc.CommName,
			tbc.no_import,
			tbc.OrganCode,
			tbc.OrganName,
			tbc.FinalJudgmentDate As finalJudgmentDate,
			tbc.BusinessType As businessType
		FROM
			tb_dup_contract AS tbc
		<where>
		<if test="BipNum !=null and BipNum != ''">AND tbc.BipNum LIKE CONCAT('%',#{BipNum},'%')</if>	
		<if test="ContName !=null and ContName != ''">AND tbc.ContName LIKE CONCAT('%',#{ContName},'%')</if>	
		<if test="IsAuditing !=null and IsAuditing != ''">AND tbc.IsAuditing LIKE CONCAT('%',#{IsAuditing},'%')</if>	
		<if test="noImport !=null and noImport != ''">AND tbc.no_import LIKE CONCAT('%',#{noImport},'%')</if>	
		<if test="RegDate !=null">AND tbc.RegDate LIKE CONCAT('%',#{RegDate},'%')</if>	
		<if test="OrganCode !=null and OrganCode !=''" >AND tbc.OrganCode LIKE CONCAT('%',#{OrganCode},'%')</if>
		<if test="CommName !=null and CommName != ''">AND tbc.CommName LIKE CONCAT('%',#{CommName},'%')</if>
		<if test="CommId !=null and CommId!=0">AND tbc.CommID = #{CommId}</if>
		<if test="finalJudgmentDate !=null and finalJudgmentDate !='' ">AND DATE_FORMAT(tbc.FinalJudgmentDate, "%Y-%m-%d") =#{finalJudgmentDate}</if>
		<if test="businessType !=null and businessType !='' ">AND tbc.BusinessType=#{businessType}</if>
		</where>	
		order by tbc.RegDate  desc
  </select>
  
  <!-- 未录入 -->
  <select id="contractStatisticalSelectNoImport" resultType="int">
  		SELECT COUNT(tbc.BipNum)  FROM tb_dup_contract AS tbc WHERE tbc.no_import ='未录入'
  </select>
  
  <!--删除已录入的信息  -->
  <delete id="deleteBIP" parameterType="String">
  	DELETE FROM tb_dup_contract WHERE tb_dup_contract.BipNum =#{BipNum}
  </delete>
  
  <!-- 插入数据 -->
  <insert id="insertdto" parameterType="cn.com.countrygarden.bi.main.model.dup.ContractStatistical">
  	INSERT INTO tb_dup_contract(
		`ContID`,
		`BipNum`,
		`ContName`,
		`IsAuditing`,
		`no_import`,
		`CommID`,
		`CommName`,
		`OrganName`,
		`OrganCode`,
		`RegDate`,
		FinalJudgmentDate,
		BusinessType
	)
	VALUES
		(
			#{ContId},
			#{BipNum},
			#{ContName},
			#{IsAuditing},
			#{noImport},
			#{CommId},
			#{CommName},
			#{OrganName},
			#{OrganCode},
			#{RegDate},	
			#{finalJudgmentDate},
			#{businessType}			
		);
  </insert>
  
  <resultMap type="cn.com.countrygarden.bi.main.model.dup.ContractTimedExecute" id="contractTimedExecuteMap">
  	<result column="start_date" property="startDate"/>
  	<result column="create_date" property="createDate"/>
  	<result column="end_date" property="endDate"/>
  </resultMap>
  
  <!--查询调度时间  -->
  <select id="findDateStart" resultMap="contractTimedExecuteMap">
   SELECT
		tb_dup_contract_timed_execute.create_date,
		tb_dup_contract_timed_execute.end_date,
		tb_dup_contract_timed_execute.start_date
	FROM
		tb_dup_contract_timed_execute
	ORDER BY
		tb_dup_contract_timed_execute.create_date DESC
	limit 0,1	
  </select>
  
  <!-- 插入调度时间 -->
  <insert id="insterContractTimedExecute" parameterType="cn.com.countrygarden.bi.main.model.dup.ContractTimedExecute">
  		INSERT INTO tb_dup_contract_timed_execute (
			`start_date`,
			`end_date`,
			`create_by`,
			`create_date`
		)
		VALUES
			(
				#{startDate},
				#{endDate},
				'sys',
				#{createDate}
			)
  </insert>
  
</mapper>