<?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.project.ProjectSignItemMapper">
  <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.service.project.impl.vo.ProjectSignItemVO">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="project_sign_id" jdbcType="INTEGER" property="projectSignId" />
    <result column="business_type" jdbcType="INTEGER" property="businessType" />
    <result column="business_type_name" jdbcType="VARCHAR" property="businessTypeName" />
    <result column="rates_area" jdbcType="DECIMAL" property="ratesArea" />
    <result column="price" jdbcType="DECIMAL" property="price" />
    <result column="month_number" jdbcType="INTEGER" property="monthNumber" />
    <result column="count_proportion" jdbcType="DECIMAL" property="countProportion" />
    <result column="month_income" jdbcType="DECIMAL" property="monthIncome" />
    <result column="year_income" jdbcType="DECIMAL" property="yearIncome" />
    <result column="remark" jdbcType="VARCHAR" property="remark" />
     
    <result column="organ_name" jdbcType="VARCHAR" property="organName" />
      <result column="organ_id" jdbcType="VARCHAR" property="organId" />
    <result column="proposal_organ_name" jdbcType="VARCHAR" property="proposalOrganName" />
     <result column="proposal_organ_id" jdbcType="VARCHAR" property="proposalOrganId" />
    <result column="project_name" jdbcType="VARCHAR" property="projectName" />
    <result column="project_nature" jdbcType="INTEGER" property="projectNature" />
       <result column="project_nature_id" jdbcType="INTEGER" property="projectNatureId" />
    <result column="is_new" jdbcType="INTEGER" property="isNew" />
    <result column="corp_type" jdbcType="INTEGER" property="corpType" />
     <result column="corp_type_id" jdbcType="INTEGER" property="corpTypeId" />
    <result column="contract_area" jdbcType="DECIMAL" property="contractArea" />
    <result column="sing_time" jdbcType="VARCHAR" property="signTime" />
    <result column="end_time" jdbcType="VARCHAR" property="endTime" />
    <result column="in_month" jdbcType="VARCHAR" property="inMonth" />
     <result column="region" jdbcType="INTEGER" property="region" />
     <result column="province" jdbcType="VARCHAR" property="province" />

  </resultMap>
 	<select id="search" resultMap="BaseResultMap">
 		SELECT 
			psi.id,
			psi.project_sign_id,
			psi.business_type,
			psi.business_type_name business_type_name,
			psi.rates_area,
			psi.price,
			psi.month_number,
			psi.count_proportion,
			psi.month_income,
			psi.year_income,
			psi.remark,
			ps.organ_name organ_name,
			
			org.`name` proposal_organ_name,
			org.organ_code proposal_organ_id,
			ps.project_name,
			ps.project_nature_name project_nature,
			ps.is_new,
			ps.corp_type_name corp_type,
			ps.contract_area,
			ps.sing_time_str sing_time,
			ps.end_time_str end_time,
			ps.in_month,
			ps.region,
			ps.province
		FROM 
			project_sign_item psi
		LEFT JOIN project_sign ps ON ps.id = psi.project_sign_id

		LEFT JOIN organ org ON org.organ_code = ps.proposal_organ_id
		
		<where> psi.is_delete = 0
				<if test="organName != null and organName != ''" >
	    			AND ps.organ_name  LIKE CONCAT('%',#{organName},'%') 
	    		</if>
	    		<if test="projectName != null and projectName != ''" >
	    			AND ps.project_name  LIKE CONCAT('%',#{projectName},'%') 
	    		</if>
	    		<if test="signTimeStr != null " >
	    			AND ps.sign_time &gt; #{signTimeStr} 
	    		</if>
	    		<if test="signTimeEnd != null" >
	    			AND ps.sign_time &lt; #{signTimeEnd}
    		</if>
    		</where>
    			ORDER BY id DESC
 	</select>
 	
 	<!-- LEFT JOIN organ o ON o.organ_code = ps.organ_id   o.organ_code organ_id,  -->
 	
 	<select id="findByYear" resultMap="BaseResultMap">
 		SELECT
			psi.id,
			psi.project_sign_id,
			psi.business_type,
			psi.rates_area,
			psi.price,
			psi.month_number,
			psi.count_proportion,
			psi.month_income,
			psi.year_income,
			psi.remark,	
			ps.project_name,	
			ps.is_new,
			ps.contract_area,
			ps.sign_time,
			ps.end_time,
			ps.in_month,
			ps.region
		FROM
			project_sign_item psi
		INNER JOIN
			project_sign ps ON psi.project_sign_id = ps.id AND ps.in_month  LIKE CONCAT(#{year},'%')  
			<if test="organId != null" >
				AND ps.organ_id=#{organId}	
			</if>
 	</select>
</mapper>