<?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.sg.managementcenter.project.mapper.ProjectMapper">
    
	<sql id="projectColumns">
		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.pro_type As "type",
		a.demand_unit AS "demandUnit",
		a.bdz_name As "bdzName",
	  	a.line_name As "lineName",
	  	a.require_name AS "requireName",
	  	a.project_id AS "projectId",
	  	a.project_name AS "projectName",
	  	a.project_number AS "projectNumber",
		a.project_manage_role AS "projectManageRole",
		a.distribution_engineering AS "distributionEngineering",
		a.distribution_materials AS "distributionMaterials",
		a.distribution_system AS "distributionSystem",
		a.distribution_policy AS "distributionPolicy",
		a.management_leader AS "managementLeader",
		a.run_unit AS "runUnit",
		a.run_unit_role AS "runUnitRole",
		a.through_research_role AS "throughResearchRole",
		a.construction_unit AS "constructionUnit",
		a.construction_unit_role AS "constructionUnitRole",
		a.construction_unit_manage AS "constructionUnitManage",
		a.construction_unit_leader AS "constructionUnitLeader",
    	a.design_unit AS "design_unit",
		a.design_unit_manage AS "designUnitManage",
		a.design_unit_leader AS "designUnitLeader",
		a.clarificaiton_type AS "clarificaitonType",
		a.build_classification_resons As "bulidClassificationResons",
		a.bulbid_resons As "bulbidResons",
		a.asic_equipment AS "asicEquipment",
		a.run_the_class As "runTheClass",
		a.project_team AS "projectTeam",
		a.cost_estimate As "costEstimate",
		a.whether_rhe_upwind AS "whetherRheUpwind",
		a.sure_da_date AS "sureDaDate",
		a.text_remark AS "textRemark",
		a.power_line AS "powerLine",
    	a.power_scope AS "powerScope",
    	a.project_type AS "projectType",
        a.power_flag_package AS "powerFlagPackage",
     	a.preliminary_judgment AS "preliminaryJudgment",
    	a.flag_civil_engineering AS "flagCivilEngineering",
    	a.steel_tube_pole AS "steelTubePole",
    	a.flag_approval_planning AS "flagApprovalPlanning",
    	a.equ_create_type AS "equCreateType",
    	a.equ_create_size AS "equCreateSize",
    	a.flag_new_equ_name AS "flagNewEquName",
    	a.include_bdz_news AS "includeBdzNews",
    	a.flag_no_electrical AS "flagNoElectrical",
		a.drawings_work AS "drawingsWork",
    	a.flag_automation AS "flagAutomation",
    	a.two_and_three_remote AS "twoAndThreeRemote",
    	a.phone_style AS "phoneStyle",
    	a.stay_open_area AS "stayOpenArea",
    	a.ring_network_cabinet AS "ringNetworkCabinet",
    	a.station_dtu AS "stationDtu",
    	a.reserve_number AS "reserveNumber",
    	a.run_unit_inspection_role AS "runUnitInspectionRole",
    	a.construction_contract_unit AS "constructionContractUnit",
    	a.construction_contract_rate AS "constructionContractRate",
    	a.construction_contract_sub AS "constructionContractSub",
    	a.construction_skill_by_full AS "constructionSkillByFull",
    	a.automated_remarks AS "automatedRemarks",
    	a.skill_by_full AS "skillByFull",
		a.status As "status",
		a.acquisition_status AS "acquisitionStatus",
		a.construction_status AS "constructionStatus",
		a.whether_close_status AS "whetherCloseStatus",
		a.earlyMaterial AS "earlyMaterial",
		a.preConstruction AS "preConstruction",
		a.rezkey AS "rezkey"
	</sql>
    
	<select id="get" resultType="Project" >
		SELECT 
			<include refid="projectColumns"/>
		FROM xm_project_details a
		WHERE a.id = #{id}
	</select>
	
	<select id="findList" resultType="Project" >
		SELECT
		<include refid="projectColumns"/>
		FROM xm_project_details a
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			${dataScope}
			<if test="requireName != null and  requireName != ''">
				AND a.require_name LIKE CONCAT('%',#{requireName},'%')
			</if>
			<if test="status != null and  status != ''">
				AND a.status = #{status}
			</if>
			<if test="whetherCloseStatus != null and  whetherCloseStatus != ''">
				AND a.whether_close_status = #{whetherCloseStatus}
			</if>
			<if test="id != null and  id != ''">
				AND a.id = #{id}
			</if>
			<if test="acquisitionStatus != null and  acquisitionStatus != ''">
				AND a.acquisition_status = #{acquisitionStatus}
			</if>
			<if test="constructionUnitRole != null and  constructionUnitRole != ''">
				AND a.construction_unit_role = #{constructionUnitRole}
			</if>
			<if test="constructionStatus != null and  constructionStatus != ''">
				AND a.construction_status  = #{constructionStatus}
			</if>
			<if test="settStatus != null and settStatus != ''">
				AND a.sett_status = #{settStatus}
			</if>
			<if test="earlyMaterial != null">
				or a.earlyMaterial = #{earlyMaterial}
			</if>
			<if test="preConstruction != null">
				AND (a.acquisition_status = '2' OR a.preConstruction = #{preConstruction})
			</if>
			<if test="projectId != null and  projectId != ''">
				and a.project_id = #{projectId}
			</if>
			<if test="projectNumber != null and projectNumber != ''">
				and a.project_number = #{projectNumber}
			</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="loginNameFindStats != null and loginNameFindStats !='' ">
				<if test="loginNameFind != null and loginNameFind != '' ">
					AND a.project_manage_role = #{loginNameFind}
					or a.distribution_engineering = #{loginNameFind}
					or a.distribution_materials = #{loginNameFind}
					or a.distribution_system = #{loginNameFind}
					or a.distribution_policy = #{loginNameFind}
					or a.management_leader = #{loginNameFind}
					or a.run_unit_role = #{loginNameFind}
					or a.through_research_role = #{loginNameFind}
					or a.construction_unit_role = #{loginNameFind}
					or a.construction_unit_manage = #{loginNameFind}
					or a.construction_unit_leader = #{loginNameFind}
					or a.design_unit_manage = #{loginNameFind}
					or a.design_unit_leader = #{loginNameFind}
				</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>

	<!--<if test="projectManageRole != null and  projectManageRole != ''">-->
		<!--AND a.project_manage_role = #{projectManageRole}-->
	<!--</if>-->

	<select id="findAllList" resultType="Project" >
		SELECT
		<include refid="projectColumns"/>
		FROM xm_project_details a
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			${dataScope}
		</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 xm_project_details(
			id,
			create_by,
			create_date,
			update_by,
			update_date,
			remarks,
			del_flag,
			pro_type,
			demand_unit,
			bdz_name,
			line_name,
			require_name,
			project_id,
			project_name,
			project_number,
			project_manage_role,
			distribution_engineering,
			distribution_materials,
			distribution_system,
			distribution_policy,
			management_leader,
			run_unit,
			run_unit_role,
			through_research_role,
			construction_unit,
			construction_unit_role,
			construction_unit_manage,
			construction_unit_leader,
			design_unit,
			design_unit_manage,
			design_unit_leader,
			clarificaiton_type,
			build_classification_resons,
			bulbid_resons,
			asic_equipment,
			run_the_class,
			project_team,
			cost_estimate,
			whether_rhe_upwind,
			sure_da_date,
			text_remark,
			power_line,
			power_scope,
			project_type,
			power_flag_package,
			preliminary_judgment,
			flag_civil_engineering,
			steel_tube_pole,
			flag_approval_planning,
			equ_create_type,
			equ_create_size,
			flag_new_equ_name,
			include_bdz_news,
			flag_no_electrical,
			drawings_work,
			flag_automation,
			two_and_three_remote,
			phone_style,
			stay_open_area,
			ring_network_cabinet,
			station_dtu,
			reserve_number,
			run_unit_inspection_role,
			construction_contract_unit,
			construction_contract_rate,
			construction_contract_sub,
			construction_skill_by_full,
			automated_remarks,
			skill_by_full,
			status,
			acquisition_status,
			construction_status,
			whether_close_status,
			earlyMaterial,
			preConstruction,
			rezkey
		) VALUES (
			#{id},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{remarks},
			#{delFlag},
			#{type},
			#{demandUnit},
			#{bdzName},
			#{lineName},
    		#{requireName},
    		#{projectId},
			#{projectName},
			#{projectNumber},
			#{projectManageRole},
			#{distributionEngineering},
			#{distributionMaterials},
			#{distributionSystem},
			#{distributionPolicy},
			#{managementLeader},
			#{runUnit},
			#{runUnitRole},
			#{throughResearchRole},
			#{constructionUnit},
			#{constructionUnitRole},
			#{constructionUnitManage},
			#{constructionUnitLeader},
			#{designUnit},
			#{designUnitManage},
			#{designUnitLeader},
			#{clarificaitonType},
    		#{bulidClassificationResons},
    		#{bulbidResons},
    		#{asicEquipment},
    		#{runTheClass},
    		#{projectTeam},
    		#{costEstimate},
    		#{whetherRheUpwind},
    		#{sureDaDate},
    		#{textRemark},
    		#{powerLine},
    	 	#{powerScope},
    	 	#{projectType},
        	#{powerFlagPackage},
     	 	#{preliminaryJudgment},
    	 	#{flagCivilEngineering},
    	 	#{steelTubePole},
    	 	#{flagApprovalPlanning},
    		#{equCreateType},
    		#{equCreateSize},
    	 	#{flagNewEquName},
    	 	#{includeBdzNews},
    		#{flagNoElectrical},
			#{drawingsWork},
    	 	#{flagAutomation},
    	 	#{twoAndThreeRemote},
     		#{phoneStyle},
    	 	#{stayOpenArea},
    	 	#{ringNetworkCabinet},
    	 	#{stationDtu},
			#{reserveNumber},
			#{runUnitInspectionRole},
			#{constructionContractUnit},
			#{constructionContractRate},
			#{constructionContractSub},
			#{constructionSkillByFull},
			#{automatedRemarks},
			#{skillByFull},
    		#{status},
    		#{acquisitionStatus},
    		#{constructionStatus},
    		#{whetherCloseStatus},
    		#{earlyMaterial},
    		#{preConstruction},
    		#{rezkey}
		)
	</insert>

	<insert id="insertList">
		replace INTO xm_project_details(
		id,
		create_by,
		create_date,
		update_by,
		update_date,
		remarks,
		del_flag,
		pro_type,
		demand_unit,
		bdz_name,
		line_name,
		require_name,
		project_id,
		project_name,
		project_number,
		project_manage_role,
		distribution_engineering,
		distribution_materials,
		distribution_system,
		distribution_policy,
		management_leader,
		run_unit,
		run_unit_role,
		through_research_role,
		construction_unit,
		construction_unit_role,
		construction_unit_manage,
		construction_unit_leader,
		design_unit,
		design_unit_manage,
		design_unit_leader,
		clarificaiton_type,
		build_classification_resons,
		bulbid_resons,
		asic_equipment,
		run_the_class,
		project_team,
		cost_estimate,
		whether_rhe_upwind,
		sure_da_date,
		text_remark,
		power_line,
		power_scope,
		project_type,
		power_flag_package,
		preliminary_judgment,
		flag_civil_engineering,
		steel_tube_pole,
		flag_approval_planning,
		equ_create_type,
		equ_create_size,
		flag_new_equ_name,
		include_bdz_news,
		flag_no_electrical,
		drawings_work,
		flag_automation,
		two_and_three_remote,
		phone_style,
		stay_open_area,
		ring_network_cabinet,
		station_dtu,
		reserve_number,
		run_unit_inspection_role,
		construction_contract_unit,
		construction_contract_rate,
		construction_contract_sub,
		construction_skill_by_full,
		automated_remarks,
		skill_by_full,
		status,
		acquisition_status,
		construction_status,
		whether_close_status,
		earlyMaterial,
		preConstruction,
		rezkey
		) VALUES
		<foreach collection="list" item="item" index="index" separator="," >
			(
			#{item.id},
			#{item.createBy.id},
			#{item.createDate},
			#{item.updateBy.id},
			#{item.updateDate},
			#{item.remarks},
			#{item.delFlag},
			#{item.type},
			#{item.demandUnit},
			#{item.bdzName},
			#{item.lineName},
			#{item.requireName},
			#{item.projectId},
			#{item.projectName},
			#{item.projectNumber},
			#{item.projectManageRole},
			#{item.distributionEngineering},
			#{item.distributionMaterials},
			#{item.distributionSystem},
			#{item.distributionPolicy},
			#{item.managementLeader},
			#{item.runUnit},
			#{item.runUnitRole},
			#{item.throughResearchRole},
			#{item.constructionUnit},
			#{item.constructionUnitRole},
			#{item.constructionUnitManage},
			#{item.constructionUnitLeader},
			#{item.designUnit},
			#{item.designUnitManage},
			#{item.designUnitLeader},
			#{item.clarificaitonType},
			#{item.bulidClassificationResons},
			#{item.bulbidResons},
			#{item.asicEquipment},
			#{item.runTheClass},
			#{item.projectTeam},
			#{item.costEstimate},
			#{item.whetherRheUpwind},
			#{item.sureDaDate},
			#{item.textRemark},
			#{item.powerLine},
			#{item.powerScope},
			#{item.projectType},
			#{item.powerFlagPackage},
			#{item.preliminaryJudgment},
			#{item.flagCivilEngineering},
			#{item.steelTubePole},
			#{item.flagApprovalPlanning},
			#{item.equCreateType},
			#{item.equCreateSize},
			#{item.flagNewEquName},
			#{item.includeBdzNews},
			#{item.flagNoElectrical},
			#{item.drawingsWork},
			#{item.flagAutomation},
			#{item.twoAndThreeRemote},
			#{item.phoneStyle},
			#{item.stayOpenArea},
			#{item.ringNetworkCabinet},
			#{item.stationDtu},
			#{item.reserveNumber},
			#{item.runUnitInspectionRole},
			#{item.constructionContractUnit},
			#{item.constructionContractRate},
			#{item.constructionContractSub},
			#{item.constructionSkillByFull},
			#{item.automatedRemarks},
			#{item.skillByFull},
			#{item.status},
			#{item.acquisitionStatus},
			#{item.constructionStatus},
			#{item.whetherCloseStatus},
			#{item.earlyMaterial},
			#{item.preConstruction},
			#{item.rezkey}
		)
		</foreach>
	</insert>

	<update id="update">
		UPDATE xm_project_details SET
			update_by = #{updateBy.id},
			update_date = #{updateDate},
			remarks = #{remarks},
			pro_type=#{type},
			demand_unit=#{demandUnit},
			bdz_name=#{bdzName},
			line_name=#{lineName},
			require_name=#{requireName},
			project_id=#{projectId},
			project_name=#{projectName},
			project_number=#{projectNumber},
			project_manage_role=#{projectManageRole},
			distribution_engineering=#{distributionEngineering},
			distribution_materials=#{distributionMaterials},
			distribution_system=#{distributionSystem},
			distribution_policy=#{distributionPolicy},
			management_leader=#{managementLeader},
			run_unit=#{runUnit},
			run_unit_role=#{runUnitRole},
			through_research_role=#{throughResearchRole},
			construction_unit=#{constructionUnit},
			construction_unit_role=#{constructionUnitRole},
			construction_unit_manage=#{constructionUnitManage},
			construction_unit_leader=#{constructionUnitLeader},
			design_unit=#{designUnit},
			design_unit_manage=#{designUnitManage},
			design_unit_leader=#{designUnitLeader},
			clarificaiton_type=#{clarificaitonType},
			build_classification_resons=#{bulidClassificationResons},
			bulbid_resons=#{bulbidResons},
			asic_equipment=#{asicEquipment},
			run_the_class=#{runTheClass},
			project_team=#{projectTeam},
			cost_estimate=#{costEstimate},
			whether_rhe_upwind=#{whetherRheUpwind},
			sure_da_date=#{sureDaDate},
			text_remark=#{textRemark},
			power_line=#{powerLine},
			power_scope=#{powerScope},
			project_type=#{projectType},
			power_flag_package=#{powerFlagPackage},
			preliminary_judgment=#{preliminaryJudgment},
			flag_civil_engineering=#{flagCivilEngineering},
			steel_tube_pole=#{steelTubePole},
			flag_approval_planning=#{flagApprovalPlanning},
			equ_create_type=#{equCreateType},
			equ_create_size=#{equCreateSize},
			flag_new_equ_name=#{flagNewEquName},
			include_bdz_news=#{includeBdzNews},
			flag_no_electrical=#{flagNoElectrical},
			drawings_work=#{drawingsWork},
			flag_automation=#{flagAutomation},
			two_and_three_remote=#{twoAndThreeRemote},
			phone_style=#{phoneStyle},
			stay_open_area=#{stayOpenArea},
			ring_network_cabinet=#{ringNetworkCabinet},
			station_dtu=#{stationDtu},
			reserve_number=#{reserveNumber},
			run_unit_inspection_role=#{runUnitInspectionRole},
			construction_contract_unit=#{constructionContractUnit},
			construction_contract_rate=#{constructionContractRate},
			construction_contract_sub=#{constructionContractSub},
			construction_skill_by_full=#{constructionSkillByFull},
			automated_remarks=#{automatedRemarks},
			skill_by_full=#{skillByFull}
		WHERE id = #{id}
	</update>

	<!--物理删除-->
	<update id="delete">
		DELETE FROM xm_project_details
		WHERE id = #{id}
	</update>
	
	<!--逻辑删除-->
	<update id="deleteByLogic">
		UPDATE xm_project_details SET
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id}
	</update>
	
	
	<!-- 根据实体名称和字段名称和字段值获取唯一记录 -->
	<select id="findUniqueByProperty" resultType="Project" statementType="STATEMENT">
		select * FROM xm_project_details  where ${id} = '${value}'
	</select>

	<!--根据项目名称修改项目施工交底状态0未发起施工交底，1施工交底过程中，2完成施工交底-->
	<update id="updateStatusBy">
		update xm_project_details set status = #{status} where require_name = (select project_name from xm_construction_clarificaiton where proc_ins_id = #{procInsId});
	</update>
	<!-- 修改项目关闭状态-->
	<update id="updateStatusByClose">
		update xm_project_details set whether_close_status = #{close} where require_name = (select project_name from xm_construction_clarificaiton where proc_ins_id = #{procInsId});
	</update>

	<!--修改项目施工交底状态0未发起施工交底，1施工交底过程中，2完成施工交底-->
	<update id="updateStatus">
		update xm_project_details set status = #{status} where id = #{id};
	</update>

	<!--修改项目领料状态状态0未发起-->
	<update id="updateAcquisitionStatus">
		update xm_project_details set acquisition_status = #{acquisitionStatus} where id = #{id};
	</update>

	<!--修改提前施工状态0未提前施工，1提前施工-->
	<update id="updatePreConstruction">
		update xm_project_details set preConstruction = #{preConstruction} where id = #{id};
	</update>


	<select id="findListById" resultType="com.jeeplus.modules.sg.managementcenter.project.entity.Project">
		SELECT
		<include refid="projectColumns"/>
		FROM xm_project_details a
		where a.id	in
		<foreach collection="array" item="id" open="(" close=")" separator=",">
			#{id}
		</foreach>
	</select>

	<select id="findListProject" parameterType="java.util.List" resultType="com.jeeplus.modules.sg.managementcenter.project.entity.Project">
		select
		<include refid="projectColumns"/>
		FROM xm_project_details a
		WHERE a.del_flag = '0'
		and a.require_name in
		<if test="list != null">
			<foreach collection="list" index="index" item="item" open="("
					 separator="," close=")">
				#{item.requireName}
			</foreach>
		</if>
	</select>

	<select id="findUser"  resultType="com.jeeplus.modules.sg.managementcenter.project.entity.Project">
		select
			id
			,demand_unit
			,project_manage_role
			,distribution_engineering
			,distribution_materials
			,distribution_system
			,distribution_policy
			,management_leader
			,run_unit
			,run_unit_role
			,through_research_role
			,construction_unit
			,construction_unit_role
			,construction_unit_manage
			,construction_unit_leader
			,design_unit
			,design_unit_manage
			,design_unit_leader
		FROM xm_project_details
		WHERE require_name = (select project_name from xm_construction_clarificaiton where proc_ins_id=#{procInsId});
	</select>

	<select id="findWZUser"  resultType="com.jeeplus.modules.sg.managementcenter.project.entity.Project">
		select
			id
			,demand_unit
			,project_manage_role
			,distribution_engineering
			,distribution_materials
			,distribution_system
			,distribution_policy
			,management_leader
			,run_unit
			,run_unit_role
			,through_research_role
			,construction_unit
			,construction_unit_role
			,construction_unit_manage
			,construction_unit_leader
			,design_unit
			,design_unit_manage
			,design_unit_leader
		FROM xm_project_details
		WHERE id = (select project_id from md_acquisition_process where proc_ins_id=#{procInsId});
	</select>

	<select id="sgFindUser"  resultType="com.jeeplus.modules.sg.managementcenter.project.entity.Project">
		select
			id
			,demand_unit
			,project_manage_role
			,distribution_engineering
			,distribution_materials
			,distribution_system
			,distribution_policy
			,management_leader
			,run_unit
			,run_unit_role
			,through_research_role
			,construction_unit
			,construction_unit_role
			,construction_unit_manage
			,construction_unit_leader
			,design_unit
			,design_unit_manage
			,design_unit_leader
		FROM xm_project_details
		WHERE require_name = (select project_name from sg_construction_process where proc_ins_id=#{procInsId});
	</select>

	<select id="findUserById"  resultType="com.jeeplus.modules.sg.managementcenter.project.entity.Project">
		select
			project_name
			,demand_unit
			,project_manage_role
			,distribution_engineering
			,distribution_materials
			,distribution_system
			,distribution_policy
			,management_leader
			,run_unit
			,run_unit_role
			,through_research_role
			,construction_unit
			,construction_unit_role
			,construction_unit_manage
			,construction_unit_leader
			,design_unit
			,design_unit_manage
			,design_unit_leader
		FROM xm_project_details
		WHERE id = #{projectId}
	</select>


	<select id="findListProjectRequisitions" parameterType="java.util.List" resultType="com.jeeplus.modules.sg.managementcenter.project.entity.Project">
		select
		<include refid="projectColumns"/>
		FROM xm_project_details a
		WHERE a.del_flag = '0'
		and a.project_number in
		<if test="list != null">
			<foreach collection="list" index="index" item="item" open="("
					 separator="," close=")">
				#{item.projectDefinition}
			</foreach>
		</if>
	</select>

	<select id="findAllUser" resultType="java.lang.String">
		<!-- 查询全部用户 -->
		SELECT
			a.name AS "name"
		FROM sys_user a
		WHERE a.del_flag = '0'
	</select>
</mapper>