<?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="com.jeeplus.modules.project.dao.ProjectDao">
    
	<sql id="projectColumns">
		a.id AS "id",
		a.status AS "status",
		a.project_number AS "projectNumber",
		a.project_name AS "projectName",
		a.tenderee_type AS "tendereeType",
		a.batch AS "batch",
		a.investment_amounts AS "investmentAmounts",
		a.entrust_company AS "entrustCompany.id",
		a.contract_info AS "contractInfo.id",
		a.project_general AS "projectGeneral.id",
		a.work_client_linkman AS "workClientLinkman.id",
		a.project_evaluation_record AS "projectEvaluationRecord.id",
		a.project_group_info AS "workprojectgroup.id",
		a.create_by AS "createBy.id",
		a.create_date AS "createDate",
		a.update_by AS "updateBy.id",
		a.update_date AS "updateDate",
		a.remarks AS "remarks",
		a.del_flag AS "delFlag"
	</sql>
	
	<sql id="projectJoins">
	</sql>

	<!--只是备注该表与其他表的关联关系 -->
	<sql id="projectJoins2">
		LEFT JOIN work_client_linkman entrustCompany ON entrustCompany.id = a.entrust_company
		LEFT JOIN work_contract_info contractInfo ON contractInfo.id = a.contract_info
		LEFT JOIN project_general projectGeneral ON projectGeneral.id = a.project_general
		LEFT JOIN project_evaluation_record projectEvaluationRecord ON projectEvaluationRecord.id = a.project_evaluation_record
		LEFT JOIN project_group_info workprojectgroup ON workprojectgroup.id = a.project_group_info
	</sql>
	
    
	<select id="get" resultType="Project" >
		SELECT 
			<include refid="projectColumns"/>
		FROM project a
		<include refid="projectJoins"/>
		WHERE a.id = #{id}
	</select>
	
	<select id="findList" resultType="Project" >
		SELECT 
			<include refid="projectColumns"/>,
			clientInfo.name AS "contractInfo.client.name",
			workprojectgroup.leader AS "workprojectgroup.leader",
			contractInfo.name AS "contractInfo.name",
			contractInfo.contract_num AS "contractInfo.contractNum"
		FROM project a
		LEFT JOIN work_contract_info contractInfo ON contractInfo.id = a.contract_info
		LEFT JOIN work_client_info   clientInfo ON clientInfo.id = contractInfo.client_id
		LEFT JOIN project_group_info workprojectgroup ON workprojectgroup.id = a.project_group_info

		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="status != null and status != ''">
				AND a.status = #{status}
			</if>
			<if test="projectNumber != null and projectNumber != ''">
				AND a.project_number LIKE
				<if test="dbName == 'oracle'">'%'||#{projectNumber}||'%'</if>
				<if test="dbName == 'mssql'">'%'+#{projectNumber}+'%'</if>
				<if test="dbName == 'mysql'">concat('%',#{projectNumber},'%')</if>
			</if>
			<if test="projectName != null and projectName != ''">
				AND a.project_name LIKE
				<if test="dbName == 'oracle'">'%'||#{projectName}||'%'</if>
				<if test="dbName == 'mssql'">'%'+#{projectName}+'%'</if>
				<if test="dbName == 'mysql'">concat('%',#{projectName},'%')</if>
			</if>
			<if test="tendereeType != null and tendereeType != ''">
				AND a.tenderee_type = #{tendereeType}
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	
	<select id="findAllList" resultType="Project" >
		SELECT 
			<include refid="projectColumns"/>
		FROM project a
		<include refid="projectJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
		</where>		
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	
	<insert id="insert">
		INSERT INTO project(
			id,
			status,
			project_number,
			project_name,
			tenderee_type,
			batch,
			investment_amounts,
			entrust_company,
			contract_info,
			project_general,
			work_client_linkman,
			project_evaluation_record,
			project_group_info,
			create_by,
			create_date,
			update_by,
			update_date,
			remarks,
			del_flag
		) VALUES (
			#{id},
			#{status},
			#{projectNumber},
			#{projectName},
			#{tendereeType},
			#{batch},
			#{investmentAmounts},
			#{entrustCompany},
			#{contractInfo},
			#{projectGeneral},
			#{workClientLinkman},
			#{projectEvaluationRecord},
			#{workprojectgroup},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{remarks},
			#{delFlag}
		)
	</insert>
	
	<update id="update">
		UPDATE project SET 	
			status = #{status},
			project_number = #{projectNumber},
			project_name = #{projectName},
			tenderee_type = #{tendereeType},
			batch = #{batch},
			investment_amounts = #{investmentAmounts},
			entrust_company = #{entrustCompany},
			contract_info = #{contractInfo},
			project_general = #{projectGeneral},
			work_client_linkman = #{workClientLinkman},
			project_evaluation_record = #{projectEvaluationRecord},
			project_group_info = #{workprojectgroup},
			update_by = #{updateBy.id},
			update_date = #{updateDate},
			remarks = #{remarks}
		WHERE id = #{id}
	</update>
	
	
	<!--物理删除-->
	<update id="delete">
		DELETE FROM project
		WHERE id = #{id}
	</update>
	
	<!--逻辑删除-->
	<update id="deleteByLogic">
		UPDATE project SET 
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id}
	</update>
	
	
	<!-- 根据实体名称和字段名称和字段值获取唯一记录 -->
	<select id="findUniqueByProperty" resultType="Project" statementType="STATEMENT">
		select * FROM project  where ${propertyName} = '${value}'
	</select>
	
	<select id="findListByentrustCompany" resultType="WorkClientLinkman">
		SELECT 
			*
		FROM work_client_linkman a
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="id != null and id != ''">
				AND a.id LIKE 
				<if test="dbName == 'oracle'">#{id}||'%'</if>
				<if test="dbName == 'mssql'">#{id}+'%'</if>
				<if test="dbName == 'mysql'">concat(#{id},'%')</if>
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	<select id="findListBycontractInfo" resultType="WorkContractInfo">
		SELECT 
			*
		FROM work_contract_info a
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="name != null and name != ''">
				AND a.name LIKE
				<if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
				<if test="dbName == 'mssql'">'%'+#{name}+'%'</if>
				<if test="dbName == 'mysql'">concat('%',#{name},'%')</if>
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	<select id="findListByprojectGeneral" resultType="Projectgeneral">
		SELECT 
			*
		FROM project_general a
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="id != null and id != ''">
				AND a.id LIKE 
				<if test="dbName == 'oracle'">#{id}||'%'</if>
				<if test="dbName == 'mssql'">#{id}+'%'</if>
				<if test="dbName == 'mysql'">concat(#{id},'%')</if>
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	<select id="findListByworkClientLinkman" resultType="WorkClientLinkman">
		SELECT 
			*
		FROM work_client_linkman a
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="id != null and id != ''">
				AND a.id LIKE 
				<if test="dbName == 'oracle'">#{id}||'%'</if>
				<if test="dbName == 'mssql'">#{id}+'%'</if>
				<if test="dbName == 'mysql'">concat(#{id},'%')</if>
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	<select id="findListByprojectEvaluationRecord" resultType="ProjectEvaluationRecord">
		SELECT 
			*
		FROM project_evaluation_record a
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="id != null and id != ''">
				AND a.id LIKE 
				<if test="dbName == 'oracle'">#{id}||'%'</if>
				<if test="dbName == 'mssql'">#{id}+'%'</if>
				<if test="dbName == 'mysql'">concat(#{id},'%')</if>
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	<select id="findListByworkprojectgroup" resultType="Workprojectgroup">
		SELECT 
			*
		FROM project_group_info a
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="id != null and id != ''">
				AND a.id LIKE 
				<if test="dbName == 'oracle'">#{id}||'%'</if>
				<if test="dbName == 'mssql'">#{id}+'%'</if>
				<if test="dbName == 'mysql'">concat(#{id},'%')</if>
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
</mapper>