<?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.supply.projectInformation.mapper.ProjectInformationMapper">

    <sql id="ProjectInformation">
        sf.submission_form_id as "submissionFormId",
        sf.project_definition_number as "projectDefinitionNumber",
        sf.approval_reply_no as "approvalReplyNo",
        sf.single_project_wbs_no as "singleProjectWbsNo",
        sf.engineering_no as "engineeringNo",
        sf.engineering_name as "engineeringName",
        sf.contacts as "contacts",
        sf.contact_number as "contactNumber",
        sf.actual_commencement_date as "actualCommencementDate",
        sf.actual_completion_date as "actualCompletionDate",
        sf.construction_unit as "constructionUnit",
        sf.contract_no as "contractNo",
        sf.contract_amount as "contractAmount",
        sf.settlement_discount_rate as "settlementDiscountRate",
        sf.planned_commencement_date as "plannedCommencementDate",
        sf.planned_completion_date as "plannedCompletionDate",
        sf.civil_engineering_amount_submitted_for_approval as "civilEngineeringAmountSubmittedForApproval",
        sf.install_amount_submitted_for_approval as "installAmountSubmittedForApproval",
        sf.subtotal_of_construction_cost_submitted_for_approval as "subtotalOfConstructionCostSubmittedForApproval",
        sf.amount_of_materials_provided_by_party_a_for_approval as "amountOfMaterialsProvidedByPartyAForApproval",
        sf.comments_on_submission_for_approval as "commentsOnSubmissionForApproval",
        sf.approved_amount_of_civil_engineering as "approvedAmountOfCivilEngineering",
        sf.installation_approval_amount as "installationApprovalAmount",
        sf.subtotal_of_approved_construction_cost as "subtotalOfApprovedConstructionCost",
        sf.approved_amount_of_materials_supplied_by_party_a as "approvedAmountOfMaterialsSuppliedByPartyA",
        sf.deduction_amount_of_construction_cost as "deductionAmountOfConstructionCost",
        sf.reduction_rate_of_construction_cost as "reductionRateOfConstructionCost",
        sf.credit_deduction_of_the_construction_unit as "creditDeductionOfTheConstructionUnit",
        sf.proposed_settlement as "proposedSettlement",
        sf.longitude as "longitude",
        sf.latitude as "latitude",
        sf.audit_fee as "auditFee",
        sf.audit_opinion_no as "auditOpinionNo",
        sf.audit_report_no as "auditReportNo",
        sf.audit_remarks as "auditRemarks",
        sf.firm_name as "firmName",
        sf.auditor_of_the_firm as "auditorOfTheFirm",
        sf.submission_date as "submissionDate",
        sf.audit_task_release_date as "auditTaskReleaseDate",
        sf.end_date_of_audit as "endDateOfAudit",
        sf.result_upload_date as "resultUploadDate",
        sf.approval_date as "approvalDate",
        sf.timeout as "timeout",
        sf.audit_management_level as "auditManagementLevel",
        sf.audit_method as "auditMethod",
        sf.full_time_auditor as "fullTimeAuditor",
        sf.submitter as "submitter",
        sf.submission_department as "submissionDepartment",
        sf.secondary_unit as "secondaryUnit",
        sf.primary_unit as "primaryUnit",
        sf.submission_status as "submissionStatus",
        pi.project_name as "projectName",
        pi.project_type as "projectType",
        pi.voltage_leve as "voltageLeve",
        pi.project_release_year as "projectReleaseYear",
        pi.project_budget_year as "projectBudgetYear",
        pi.total_project_investment as "totalProjectInvestment",
        sf.table_building_time as "tableBuildingTime"
    </sql>

    <select id="findList" resultType="ProjectInformation" >
        SELECT
        <include refid="ProjectInformation"/>
        ,sf.id as "id"
        from submission_form sf
        left join project_information pi on pi.project_definition_number = sf.project_definition_number
        <where>
            <if test="projectDefinitionNumber != null and projectDefinitionNumber != ''">
                AND sf.project_definition_number like concat('%',#{projectDefinitionNumber},'%')
            </if>
            <if test="submissionFormId != null and submissionFormId != ''">
                AND sf.submission_form_id like concat('%',#{submissionFormId},'%')
            </if>
            <if test="beginDate != null ">
                AND sf.table_building_time >= #{beginDate}
            </if>
            <if test="endDate != null ">
                AND sf.table_building_time &lt;= #{endDate}
            </if>
        </where>
        GROUP BY sf.submission_form_id
        <choose>
            <when test="page !=null and page.orderBy != null and page.orderBy != ''">
                ORDER BY ${page.orderBy}
            </when>
                        <otherwise>
                            ORDER BY sf.table_building_time DESC
                        </otherwise>
        </choose>
    </select>






    <!--批量新增 审定单表-->
    <insert id="insertSubmissionFormList">
        insert into submission_form (
        submission_form_id,
        project_definition_number,
        approval_reply_no,
        single_project_wbs_no,
        engineering_no,
        engineering_name,
        contacts,
        contact_number,
        actual_commencement_date,
        actual_completion_date,
        construction_unit,
        contract_no,
        contract_amount,
        settlement_discount_rate,
        planned_commencement_date,
        planned_completion_date,
        civil_engineering_amount_submitted_for_approval,
        install_amount_submitted_for_approval,
        subtotal_of_construction_cost_submitted_for_approval,
        amount_of_materials_provided_by_party_a_for_approval,
        comments_on_submission_for_approval,
        approved_amount_of_civil_engineering,
        installation_approval_amount,
        subtotal_of_approved_construction_cost,
        approved_amount_of_materials_supplied_by_party_a,
        deduction_amount_of_construction_cost,
        reduction_rate_of_construction_cost,
        credit_deduction_of_the_construction_unit,
        proposed_settlement,
        longitude,
        latitude,
        audit_fee,
        audit_opinion_no,
        audit_report_no,
        audit_remarks,
        firm_name,
        auditor_of_the_firm,
        submission_date,
        audit_task_release_date,
        end_date_of_audit,
        result_upload_date,
        approval_date,
        timeout,
        audit_management_level,
        audit_method,
        full_time_auditor,
        submitter,
        submission_department,
        secondary_unit,
        primary_unit,
        submission_status,
        table_building_time,
        id,
        create_by,
        create_date,
        update_by,
        update_date,
        remarks,
        del_flag
        )
        values
        <foreach collection="list" item="item" index="index" separator="," >
            (
            #{item.submissionFormId},
            #{item.projectDefinitionNumber},
            #{item.approvalReplyNo},
            #{item.singleProjectWbsNo},
            #{item.engineeringNo},
            #{item.engineeringName},
            #{item.contacts},
            #{item.contactNumber},
            #{item.actualCommencementDate},
            #{item.actualCompletionDate},
            #{item.constructionUnit},
            #{item.contractNo},
            #{item.contractAmount},
            #{item.settlementDiscountRate},
            #{item.plannedCommencementDate},
            #{item.plannedCompletionDate},
            #{item.civilEngineeringAmountSubmittedForApproval},
            #{item.installAmountSubmittedForApproval},
            #{item.subtotalOfConstructionCostSubmittedForApproval},
            #{item.amountOfMaterialsProvidedByPartyAForApproval},
            #{item.commentsOnSubmissionForApproval},
            #{item.approvedAmountOfCivilEngineering},
            #{item.installationApprovalAmount},
            #{item.subtotalOfApprovedConstructionCost},
            #{item.approvedAmountOfMaterialsSuppliedByPartyA},
            #{item.deductionAmountOfConstructionCost},
            #{item.reductionRateOfConstructionCost},
            #{item.creditDeductionOfTheConstructionUnit},
            #{item.proposedSettlement},
            #{item.longitude},
            #{item.latitude},
            #{item.auditFee},
            #{item.auditOpinionNo},
            #{item.auditReportNo},
            #{item.auditRemarks},
            #{item.firmName},
            #{item.auditorOfTheFirm},
            #{item.submissionDate},
            #{item.auditTaskReleaseDate},
            #{item.endDateOfAudit},
            #{item.resultUploadDate},
            #{item.approvalDate},
            #{item.timeout},
            #{item.auditManagementLevel},
            #{item.auditMethod},
            #{item.fullTimeAuditor},
            #{item.submitter},
            #{item.submissionDepartment},
            #{item.secondaryUnit},
            #{item.primaryUnit},
            #{item.submissionStatus},
            now(),
            #{item.id},
            #{item.createBy.id},
            #{item.createDate},
            #{item.updateBy.id},
            #{item.updateDate},
            #{item.remarks},
            #{item.delFlag}
            )
        </foreach>
    </insert>

    <!--批量新增 项目信息表-->
    <insert id="insertProjectInformationList">
        insert into project_information (
        project_definition_number,
        project_name,
        project_type,
        voltage_leve,
        project_release_year,
        project_budget_year,
        total_project_investment,
        id,
        create_by,
        create_date,
        update_by,
        update_date,
        remarks,
        del_flag
        )
        values
        <foreach collection="list" item="item" index="index" separator="," >
            (
            #{item.projectDefinitionNumber},
            #{item.projectName},
            #{item.projectType},
            #{item.voltageLeve},
            #{item.projectReleaseYear},
            #{item.projectBudgetYear},
            #{item.totalProjectInvestment},
            #{item.id},
            #{item.createBy.id},
            #{item.createDate},
            #{item.updateBy.id},
            #{item.updateDate},
            #{item.remarks},
            #{item.delFlag}
            )
        </foreach>
    </insert>

    <select id="getSubmissionFormIdListBysubmissionFormIdList" resultType="string">
        select submission_form_id from submission_form
        <where>
            <if test="submissionFormIdList != null and submissionFormIdList.size !=0">
                submission_form_id in
                <foreach collection="submissionFormIdList" item="submissionFormId"  separator="," open="(" close=")">
                    #{submissionFormId}
                </foreach>
            </if>
        </where>
    </select>

    <delete id="deleteAll">
        delete sf,pi from submission_form sf left join project_information pi on pi.project_definition_number = sf.project_definition_number
        <where>
            sf.submission_form_id in
            <foreach collection="codeList" item="code" separator="," open="(" close=")">
                #{code}
            </foreach>
        </where>
    </delete>

    <select id="get" resultType="com.jeeplus.modules.supply.projectInformation.entity.ProjectInformation">
        select
        <include refid="ProjectInformation"/>
        ,sf.id as "id"
        from submission_form sf
        left join project_information pi on pi.project_definition_number = sf.project_definition_number
        where sf.id = #{id}
    </select>

    <delete id="delete">
        delete sf,pi from submission_form sf left join project_information pi on pi.project_definition_number = sf.project_definition_number
        where sf.id = #{id}
    </delete>

</mapper>
