<?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.projectrecord.dao.ProjectRecordsAlterDao">

	<sql id="ProjectRecordsAlterColumns">
		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.contract_id AS "workContractInfo.id",
		a.project_id AS "projectId",
		a.project_name AS "projectName",
		a.scale_type AS "scaleType",
		a.scale_unit AS "scaleUnit",
		a.scale_quantity AS "scaleQuantity",
		a.area_id AS "area.id",
		a.project_site AS "projectSite",
		a.project_desc AS "projectDesc",
		a.remarks AS "remarks",
		a.status AS "projectStatus",
		a.process_instance_id AS "processInstanceId",
		a.company_id AS "company.id",
		a.office_id AS "office.id",
		a.alter_reason AS "alterReason",
		a.alter_before_id AS "alterBeforeRecords.id",
		a.alter_project_id AS "alterProjectId",
		area.name AS "area.name",
		a.province,
		a.city,
		a.area_name AS "areaName",
		a.project_structure AS "projectStructure",
		a.on_ground_num AS "onGroundNum",
		a.under_ground_num AS "underGroundNum",
		a.building_scale AS "buildingScale",
		a.measuring_unit AS "measuringUnit",
		a.project_use AS "projectUse",
		a.install_fees AS "installFees",
		a.building_fees AS "buildingFees",
		a.building_percent AS "buildingPercent",
		a.install_percent AS "installPercent",
		a.unit_fees AS "unitFees",
		a.building_unit_fees AS "buildingUnitFees",
		a.install_unit_fees AS "installUnitFees",
		a.total_fees AS "totalFees"
	</sql>
	
	<sql id="ProjectRecordsAlterJoins">
		LEFT JOIN sys_area area ON area.id = a.area_id
	</sql>
	
    
	<select id="get" resultType="ProjectRecordsAlter" >
		SELECT
			<include refid="ProjectRecordsAlterColumns"/>
        ,su.name AS "createBy.name"
        ,o.name AS "office.name"
		FROM project_records_alter a
		<include refid="ProjectRecordsAlterJoins"/>
        LEFT JOIN sys_user su ON su.id = a.create_by
        LEFT JOIN sys_office o ON o.id = a.office_id
		WHERE a.id = #{id}
	</select>
	
	<select id="findList" resultType="ProjectRecordsAlter" >
		SELECT DISTINCT
			<include refid="ProjectRecordsAlterColumns"/>
		,
		wci.name AS "workContractInfo.name",
		wct.id AS "workContractInfo.client.id",
		wct.name AS "workContractInfo.client.name"
		FROM project_records_alter a
		<include refid="ProjectRecordsAlterJoins"/>
		LEFT JOIN work_project_user w on a.id = w.project_id
		LEFT JOIN sys_user u on w.user_id = u.id
		LEFT JOIN work_contract_info wci on a.contract_id = wci.id
		LEFT JOIN work_client_info wct on wci.client_id = wct.id
        LEFT JOIN sys_office s ON s.id = a.company_id
        LEFT JOIN sys_office o ON o.id = a.office_id
		<where>
			<if test="id != null and id != ''">
				AND a.id = #{id}
			</if>
			<if test="projectName != null and projectName != ''">
				AND a.project_name like concat(concat('%',#{projectName}),'%')
			</if>
			<if test="workContractInfo !=null and workContractInfo.name !=null and workContractInfo.name !=''">
				AND wci.name like concat(concat('%',#{workContractInfo.name}),'%')
			</if>
			<if test="workContractInfo!=null and workContractInfo.client !=null and workContractInfo.client.name !=null and workContractInfo.client.name !=''">
				AND wct.name like concat(concat('%',#{workContractInfo.client.name}),'%')
			</if>
            <if test="leaderNameStr !=null and leaderNameStr !=''">
                AND u.name like concat(concat('%',#{leaderNameStr}),'%')
            </if>
			<if test="beginDate !=null">
				AND a.create_date >= #{beginDate}
			</if>
			<if test="endDate !=null">
				AND a.create_date &lt; #{endDate}
			</if>
            AND w.is_master = '1'
            ${sqlMap.dsf}
		</where>
            GROUP BY a.id
		<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="ProjectRecordsAlter" >
		SELECT 
			<include refid="ProjectRecordsAlterColumns"/>
		FROM project_records_alter a
		<include refid="ProjectRecordsAlterJoins"/>
		<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_records_alter(
			id,
			create_by,
			create_date,
			update_by,
			update_date,
			contract_id,
			project_id,
			project_name,
			scale_type,
			scale_unit,
			scale_quantity,
			area_id,
			project_site,
			project_desc,
			remarks,
			status,
			company_id,
		    office_id,
		    alter_reason,
		    alter_before_id,
		    alter_project_id,
		    province,
		    city,
		    area_name,
		    project_structure,
		    on_ground_num,
		    under_ground_num,
		    building_scale,
		    measuring_unit,
		    project_use,
		    install_fees,
		    building_fees,
		    building_percent,
		    install_percent,
		    unit_fees,
		    building_unit_fees,
		    install_unit_fees,
		    total_fees
		) VALUES (
			#{id},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{workContractInfo.id},
			#{projectId},
			#{projectName},
			#{scaleType},
			#{scaleUnit},
			#{scaleQuantity},
			#{area.id},
			#{projectSite},
			#{projectDesc},
			#{remarks},
			#{projectStatus},
			#{company.id},
			#{office.id},
			#{alterReason},
			#{alterBeforeRecords.id},
			#{alterProjectId},
			#{province},
			#{city},
			#{county},
			#{projectStructure},
			#{onGroundNum},
			#{underGroundNum},
			#{buildingScale},
			#{measuringUnit},
			#{projectUse},
			#{installFees},
			#{buildingFees},
			#{buildingPercent},
			#{installPercent},
			#{unitFees},
			#{buildingUnitFees},
			#{installUnitFees},
			#{totalFees}
		)
	</insert>
	
	<update id="update">
		UPDATE project_records_alter SET
			update_by = #{updateBy.id},
			update_date = #{updateDate},
			contract_id = #{workContractInfo.id},
			project_id = #{projectId},
			project_name = #{projectName},
			scale_type = #{scaleType},
			scale_unit = #{scaleUnit},
			scale_quantity = #{scaleQuantity},
			area_id = #{area.id},
			project_site = #{projectSite},
			project_desc = #{projectDesc},
			remarks = #{remarks},
			status = #{projectStatus},
			company_id = #{company.id},
			office_id = #{office.id},
			alter_reason = #{alterReason},
			alter_before_id = #{alterBeforeRecords.id},
			alter_project_id = #{alterProjectId},
			province = #{province},
		    city = #{city},
		    area_name = #{county},
		    project_structure =  #{projectStructure},
		    on_ground_num = #{onGroundNum},
		    under_ground_num = #{underGroundNum},
		    building_scale = #{buildingScale},
		    measuring_unit =  #{measuringUnit},
		    project_use =  #{projectUse},
		    install_fees =  #{installFees},
		    building_fees =  #{buildingFees},
		    building_percent =  #{buildingPercent},
		    install_percent =  #{installPercent},
		    unit_fees = #{unitFees},
		    building_unit_fees = #{buildingUnitFees},
		    install_unit_fees =  #{installUnitFees},
		    total_fees =  #{totalFees}
		WHERE id = #{id}
	</update>
	
	
	<!--物理删除-->
	<update id="delete">
		DELETE FROM project_records_alter
		WHERE id = #{id}
	</update>
	

	<!-- 根据实体名称和字段名称和字段值获取唯一记录 -->
	<select id="findUniqueByProperty" resultType="ProjectRecordsAlter" statementType="STATEMENT">
		select
			<include refid="ProjectRecordsAlterColumns"/>
		FROM project_records_alter a
		<include refid="ProjectRecordsAlterJoins"/>
		where ${propertyName} = '${value}'
	</select>

    <update id="updateProcessIdAndStatus" >
        UPDATE  project_records_alter SET
          process_instance_id = #{processInstanceId},
          status = #{projectStatus}
        WHERE id = #{id}
    </update>
	
	<update id="updateSelectiveById">
		UPDATE  project_records_alter
		<set>
			<if test="updateBy!=null and updateBy.id!=null and updateBy.id!=''">
				update_by = #{updateBy.id},
			</if>
			<if test="updateDate!=null">
				update_date = #{updateDate},
			</if>
			<if test="workContractInfo!=null and workContractInfo.id!=null and workContractInfo.id!=''">
				contract_id = #{workContractInfo.id},
			</if>
			<if test="projectId!=null and projectId!=''">
				project_id = #{projectId},
			</if>
			<if test="projectName!=null and projectName!=''">
				project_name = #{projectName},
			</if>
			<if test="scaleType!=null and scaleType!=''">
				scale_type = #{scaleType},
			</if>
			<if test="scaleUnit!=null and scaleUnit!=''">
				scale_unit = #{scaleUnit},
			</if>
			<if test="scaleQuantity !=null and scaleQuantity != ''">
				scale_quantity = #{scaleQuantity},
			</if>
			<if test="area!=null and area.id!=null and area.id!=''">
				area_id = #{area.id},
			</if>
			<if test="projectSite!=null and projectSite!=''">
				project_site = #{projectSite},
			</if>
			<if test="projectDesc!=null and projectDesc!=''">
				project_desc = #{projectDesc},
			</if>
			<if test="remarks!=null and remarks!=''">
				remarks = #{remarks},
			</if>
			<if test="projectStatus !=null and projectStatus != 0">
				status = #{projectStatus},
			</if>
			<if test="company!=null and company.id!=null and company.id!=''">
				company_id = #{company.id},
			</if>
			<if test="office!=null and office.id!=null and office.id!=''">
				office_id = #{office.id},
			</if>
			<if test="alterReason!=null and alterReason!=''">
				alter_reason = #{alterReason},
			</if>
			<if test="alterBeforeRecords!=null and alterBeforeRecords.id!=null and alterBeforeRecords.id!=''">
				alter_before_id = #{alterBeforeRecords.id},
			</if>
			<if test="alterProjectId!=null and alterProjectId!=''">
                alter_project_id = #{alterProjectId}
			</if>
		</set>
		WHERE id = #{id}
	</update>

    <select id="countAlter" resultType="int">
        SELECT count(*) from project_records_alter where project_id = #{projectId}
    </select>
</mapper>