<?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.workbidingevaluation.dao.WorkBidingEvaluationDao">
    
	<sql id="workBidingEvaluationColumns">
		a.id AS "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",
		a.company_id AS "companyId",
		a.office_id AS "officeId",
		a.start_time AS "startTime",
		a.start_address AS "startAddress",
		a.evaluation_time AS "evaluationTime",
		a.evaluation_address AS "evaluationAddress",
		a.judge_count AS "judgeCount",
		a.judge_leader AS "judgeLeader",
		a.judge_members AS "judgeMembers",
		a.judge_members_bak AS "judgeMembersBak",
		a.consult_type AS "consultType",
		a.other_members AS "otherMembers",
		a.work_evaluation_room_id AS "workEvaluationRoom.id",
		room.address AS "workEvaluationRoom.address",
		room.floor AS "workEvaluationRoom.floor",
		room.house_number AS "workEvaluationRoom.houseNumber",
		a.appointment_time_begin AS "appointmentTimeBegin",
		a.appointment_time_end AS "appointmentTimeEnd",
		a.work_biding_project_id AS "workBidingProjectId"
	</sql>
	
	<sql id="workBidingEvaluationJoins">
		LEFT JOIN work_evaluation_room room on room.id = a.work_evaluation_room_id
	</sql>
	
    
	<select id="get" resultType="WorkBidingEvaluation" >
		SELECT 
			<include refid="workBidingEvaluationColumns"/>
		FROM work_biding_evaluation a
		<include refid="workBidingEvaluationJoins"/>
		WHERE a.id = #{id}
	</select>

	<select id="findlistWithWorkBidingProject" resultType="WorkBidingEvaluation">
		SELECT
			a.id AS "workBidingProject.id",
			a.create_by AS "workBidingProject.createBy.id",
			a.create_date AS "workBidingProject.createDate",
			a.update_by AS "workBidingProject.updateBy.id",
			a.update_date AS "workBidingProject.updateDate",
			a.del_flag AS "workBidingProject.delFlag",
			a.remarks AS "workBidingProject.remarks",
			a.client_id AS "workBidingProject.client.id",
			a.contract_id AS "workBidingProject.contract.id",
			a.bidding_type AS "workBidingProject.biddingType",
			a.company_id AS "workBidingProject.companyId",
			a.office_id AS "workBidingProject.office.id",
			a.batch AS "workBidingProject.batch",
			a.investment_amount AS "workBidingProject.investmentAmount",
			a.project_number AS "workBidingProject.projectNumber",
			a.project_name AS "workBidingProject.projectName",
			a.control_price AS "workBidingProject.controlPrice",
			a.is_control_price AS "workBidingProject.isControlPrice",
			a.demand_office AS "workBidingProject.demandOffice.id",
			a.job_location AS "workBidingProject.area.id",
			a.supervisory_office AS "workBidingProject.supervisoryOffice",
			a.capital_attribute AS "workBidingProject.capitalAttribute",
			a.project_start_date AS "workBidingProject.projectStartDate",
			a.project_end_date AS "workBidingProject.projectEndDate",
			a.scope_of_bidding AS "workBidingProject.scopeOfBidding",
			a.bid_evaluation_method AS "workBidingProject.bidEvaluationMethod",
			a.investor_qualification AS "workBidingProject.investorQualification",
			a.host_project_qualification AS "workBidingProject.hostProjectQualification",
			a.other_requirement AS "workBidingProject.otherRequirement",
			a.project_master AS "workBidingProject.projectMaster",
			a.project_member AS "workBidingProject.projectMember",
			a.inventor_user AS "workBidingProject.inventorUser",
			a.status AS "workBidingProject.status",
			o.name AS "workBidingProject.office.name",
			ce.name AS "workBidingProject.client.name",
			workBidingEvaluation.id AS "id",
			workBidingEvaluation.create_by AS "createBy.id",
			workBidingEvaluation.create_date AS "createDate",
			workBidingEvaluation.update_by AS "updateBy.id",
			workBidingEvaluation.update_date AS "updateDate",
			workBidingEvaluation.remarks AS "remarks",
			workBidingEvaluation.del_flag AS "delFlag",
			workBidingEvaluation.company_id AS "companyId",
			workBidingEvaluation.office_id AS "officeId",
			workBidingEvaluation.start_time AS "startTime",
			workBidingEvaluation.start_address AS "startAddress",
			workBidingEvaluation.evaluation_time AS "evaluationTime",
			workBidingEvaluation.evaluation_address AS "evaluationAddress",
			workBidingEvaluation.judge_count AS "judgeCount",
			workBidingEvaluation.judge_leader AS "judgeLeader",
			workBidingEvaluation.judge_members AS "judgeMembers",
			workBidingEvaluation.judge_members_bak AS "judgeMembersBak",
			workBidingEvaluation.consult_type AS "consultType",
			workBidingEvaluation.other_members AS "otherMembers",
			workBidingEvaluation.work_evaluation_room_id AS "workEvaluationRoom.id",
			room.address AS "workEvaluationRoom.address",
			room.floor AS "workEvaluationRoom.floor",
			room.house_number AS "workEvaluationRoom.houseNumber",
			workBidingEvaluation.appointment_time_begin AS "appointmentTimeBegin",
			workBidingEvaluation.appointment_time_end AS "appointmentTimeEnd",
			workBidingEvaluation.work_biding_project_id AS "workBidingProjectId"
		FROM work_biding_project a
		JOIN sys_office o ON o.id = a.office_id
		JOIN sys_office s ON s.id = a.company_id
		LEFT JOIN  work_client_Info ce ON ce.id = a.client_id
		LEFT JOIN  work_contract_Info ca ON ca.id = a.contract_id
		LEFT JOIN  work_biding_evaluation workBidingEvaluation ON workBidingEvaluation.work_biding_project_id = a.id
		LEFT JOIN  work_evaluation_room room ON room.id = workBidingEvaluation.work_evaluation_room_id
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="companyId != null and companyId != ''">
				AND a.company_id = #{companyId}
			</if>
			<if test="workBidingProject != null and workBidingProject.projectNumber != null and workBidingProject.projectNumber != ''">
				AND a.project_number LIKE
				<if test="dbName == 'oracle'">'%'||#{workBidingProject.projectNumber}||'%'</if>
				<if test="dbName == 'mssql'">'%'+#{workBidingProject.projectNumber}+'%'</if>
				<if test="dbName == 'mysql'">concat('%',#{workBidingProject.projectNumber},'%')</if>
			</if>
			<if test="workBidingProject != null and workBidingProject.projectName != null and workBidingProject.projectName != ''">
				AND a.project_name LIKE
				<if test="dbName == 'oracle'">'%'||#{workBidingProject.projectName}||'%'</if>
				<if test="dbName == 'mssql'">'%'+#{workBidingProject.projectName}+'%'</if>
				<if test="dbName == 'mysql'">concat('%',#{workBidingProject.projectName},'%')</if>
			</if>
			<if test="workBidingProject != null and workBidingProject.client != null and workBidingProject.client.name != null and workBidingProject.client.name !=''">
				AND ce.name LIKE
				<if test="dbName == 'oracle'">'%'||#{workBidingProject.client.name}||'%'</if>
				<if test="dbName == 'mssql'">'%'+#{workBidingProject.client.name}+'%'</if>
				<if test="dbName == 'mysql'">concat('%',#{workBidingProject.client.name},'%')</if>
			</if>
			<if test="workBidingProject != null and workBidingProject.status != null and workBidingProject.status != ''">
				AND a.status = #{workBidingProject.status}
			</if>
			<choose>
				<when test="queryCon != null and queryCon != ''">
					AND (
					find_in_set(#{queryCon},a.project_master)
					or
					find_in_set(#{queryCon},a.project_member)
					OR a.create_by = #{queryCon}
					${sqlMap.dsf}
					)
				</when>
				<otherwise>
					${sqlMap.dsf}
				</otherwise>
			</choose>
		</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>

	<update id="confirmDeposit">
		UPDATE work_project_deposit a
		SET
			<if test="num !=null and num !='' and num==0">
				a.deposit_status = #{statusValue}
			</if>
			<if test="num != null and num !='' and num==1">
				a.biding_document_status = #{statusValue}
			</if>
		WHERE id = #{workProjectDepositId}
	</update>

	<update id="saveJudgeMembersBak" >
		UPDATE work_biding_evaluation w
		SET
		w.judge_members_bak = #{judgeMembersBak}
		WHERE w.id = #{id}
	</update>

	<update id="saveJudgeMembers">
		UPDATE work_biding_evaluation w
		SET
		<if test="judgeCount !=null and judgeCount !=''">
			w.judge_count = #{judgeCount},
		</if>
		w.judge_members = #{judgeMembers}
		WHERE w.id = #{id}
	</update>
	<update id="saveJudgeLeader">
		UPDATE work_biding_evaluation w
		SET w.judge_leader = #{judgeLeader}
		WHERE w.id = #{id}
	</update>
	
	<update id="updateMembersAndBak">
		UPDATE work_biding_evaluation w
		SET w.judge_members = #{judgeMembers},w.judge_members_bak = #{judgeMembersBak}
		WHERE w.id = #{id}
	</update>
	
	<select id="findList" resultType="WorkBidingEvaluation" >
		SELECT 
			<include refid="workBidingEvaluationColumns"/>
		FROM work_biding_evaluation a
		<include refid="workBidingEvaluationJoins"/>
		<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>
	
	<select id="findAllList" resultType="WorkBidingEvaluation" >
		SELECT 
			<include refid="workBidingEvaluationColumns"/>
		FROM work_biding_evaluation a
		<include refid="workBidingEvaluationJoins"/>
		<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 work_biding_evaluation(
			id,
			create_by,
			create_date,
			update_by,
			update_date,
			remarks,
			del_flag,
			company_id,
			office_id,
			start_time,
			start_address,
			evaluation_time,
			evaluation_address,
			judge_count,
			judge_leader,
			judge_members,
			judge_members_bak,
			consult_type,
			other_members,
			work_evaluation_room_id,
			appointment_time_begin,
			appointment_time_end,
			work_biding_project_id
		) VALUES (
			#{id},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{remarks},
			#{delFlag},
			#{companyId},
			#{officeId},
			#{startTime},
			#{startAddress},
			#{evaluationTime},
			#{evaluationAddress},
			#{judgeCount},
			#{judgeLeader},
			#{judgeMembers},
			#{judgeMembersBak},
			#{consultType},
			#{otherMembers},
			#{workEvaluationRoom.id},
			#{appointmentTimeBegin},
			#{appointmentTimeEnd},
			#{workBidingProjectId}
		)
	</insert>
	
	<update id="update">
		UPDATE work_biding_evaluation SET 	
			update_by = #{updateBy.id},
			update_date = #{updateDate},
			remarks = #{remarks},
			company_id = #{companyId},
			office_id = #{officeId},
			start_time = #{startTime},
			start_address = #{startAddress},
			evaluation_time = #{evaluationTime},
			evaluation_address = #{evaluationAddress},
			judge_count = #{judgeCount},
			judge_leader = #{judgeLeader},
			judge_members = #{judgeMembers},
			judge_members_bak = #{judgeMembersBak},
			consult_type = #{consultType},
			other_members = #{otherMembers},
			work_evaluation_room_id = #{workEvaluationRoom.id},
			appointment_time_begin = #{appointmentTimeBegin},
			appointment_time_end = #{appointmentTimeEnd},
			work_biding_project_id = #{workBidingProjectId}
		WHERE id = #{id}
	</update>
	
	
	<!--物理删除-->
	<update id="delete">
		DELETE FROM work_biding_evaluation
		WHERE id = #{id}
	</update>
	
	<!--逻辑删除-->
	<update id="deleteByLogic">
		UPDATE work_biding_evaluation SET 
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id}
	</update>
	
	
	<!-- 根据实体名称和字段名称和字段值获取唯一记录 -->
	<select id="findUniqueByProperty" resultType="WorkBidingEvaluation" statementType="STATEMENT">
		select * FROM work_biding_evaluation  where ${propertyName} = '${value}'
	</select>
	
</mapper>