<?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.finance.signatureDaily.mapper.SignatureDailyOfficeWorkMapper">

    <select id="findList" resultType="com.jeeplus.finance.signatureDaily.service.dto.SignatureDailyOfficeWorkDTO">
        select distinct
            a.id,
            a.create_by_id,
            a.create_time,
            a.signature_name,
            a.number,
            a.signature_type,
            a.status,
            a.remarks,
            a.proc_ins_id,
            a.process_definition_id,
            c.name as createName,
            d.ID_ AS task_id,
            so.name as officeName
        from cw_signature_daily_office_work a
        LEFT JOIN sys_user c ON a.create_by_id =  c.id
        left join sys_office so on so.id = c.office_id and so.del_flag = 0
        left join sys_user_manage_office sumo on sumo.office_id = c.office_id
        LEFT JOIN act_ru_task d ON a.proc_ins_id = d.PROC_INST_ID_
        ${ew.customSqlSegment}
		ORDER BY a.create_time DESC
    </select>

    <select id="findList2" resultType="com.jeeplus.finance.signatureDaily.service.dto.SignatureDailyOfficeWorkDTO">
        select distinct
            a.id,
            a.create_by_id,
            a.create_time,
            a.signature_name,
            a.number,
            a.signature_type,
            a.status,
            a.remarks,
            a.proc_ins_id,
            a.process_definition_id,
            c.name as createName,
            d.ID_ AS task_id,
            so.name as officeName
        from cw_signature_project_report_relation a
        LEFT JOIN sys_user c ON a.create_by_id =  c.id
        left join sys_office so on so.id = c.office_id and so.del_flag = 0
        left join sys_user_manage_office sumo on sumo.office_id = c.office_id
        LEFT JOIN act_ru_task d ON a.proc_ins_id = d.PROC_INST_ID_
        ${ew.customSqlSegment}
		ORDER BY a.create_time DESC
    </select>

    <select id="findById" resultType="com.jeeplus.finance.signatureDaily.service.dto.SignatureDailyOfficeWorkDTO">
        select distinct
            a.id,
            a.create_by_id,
            a.create_time,
            a.signature_name,
            a.number,
            a.signature_type,
            a.status,
            a.remarks,
            a.proc_ins_id,
            a.process_definition_id,
            a.signature_contract_id,
            a.signature_document_id,
            a.signature_url,
            a.signature_url_flag,
            a.signature_file_name,
            a.signature_upload_date,
            a.signature_upload_file_user,
            a.signature_flag,
            c.name as createName,
            d.ID_ AS task_id,
            so.name as officeName
        from cw_signature_daily_office_work a
        LEFT JOIN sys_user c ON a.create_by_id =  c.id
        left join sys_office so on so.id = c.office_id and so.del_flag = 0
        left join sys_user_manage_office sumo on sumo.office_id = c.office_id
        LEFT JOIN act_ru_task d ON a.proc_ins_id = d.PROC_INST_ID_
        where a.id = #{id} and a.del_flag = 0
    </select>

    <select id="findFiles" resultType="com.jeeplus.sys.domain.WorkAttachmentInfo">
		SELECT
			id,
			url,
			attachment_name AS `name`,
			create_by_id AS `by`,
			attachment_flag AS `attachmentFlag`,
			create_time
		FROM
			work_attachment
		WHERE
			del_flag = 0
			AND attachment_id = #{id}
			and attachment_flag = #{flag}
	</select>

    <update id="updateStatusById">
        update cw_signature_daily_office_work set status = #{status} where id = #{id}
    </update>

    <update id="updateSignatureInfo" >
        UPDATE cw_signature_daily_office_work SET
        <if test="signatureDocumentId != null and signatureDocumentId != ''">
            signature_document_id = #{signatureDocumentId}
        </if>
        <if test="signatureUrl != null">
            ,signature_url = #{signatureUrl}
        </if>
        <if test="signatureUrlFlag != null">
            ,signature_url_flag = #{signatureUrlFlag}
        </if>
        <if test="signatureContractId != null and signatureContractId != ''">
            ,signature_contract_id = #{signatureContractId}
        </if>
        <if test="signatureFlag != null">
            ,signature_flag = #{signatureFlag}
        </if>
        <if test="signatureFileName != null and signatureFileName != ''">
            ,signature_file_name = #{signatureFileName}
        </if>
        <if test="signatureUploadFileUser != null and signatureUploadFileUser.id != null and signatureUploadFileUser.id != ''">
            ,signature_upload_file_user = #{signatureUploadFileUser.id}
        </if>
        <if test="signatureUploadDate != null">
            ,signature_upload_date = #{signatureUploadDate}
        </if>
        WHERE id = #{id}
    </update>

    <select id="getByDocumentId" resultType="com.jeeplus.finance.signatureDaily.service.dto.SignatureDailyOfficeWorkDTO">
        select distinct
            a.id,
            a.create_by_id,
            a.create_time,
            a.signature_name,
            a.number,
            a.signature_type,
            a.status,
            a.remarks,
            a.proc_ins_id,
            a.process_definition_id,
            a.signature_contract_id,
            a.signature_document_id,
            a.signature_url,
            a.signature_url_flag,
            a.signature_file_name,
            a.signature_upload_date,
            a.signature_upload_file_user,
            a.signature_flag,
            c.name as createName,
            d.ID_ AS task_id,
            so.name as officeName
        from cw_signature_daily_office_work a
        LEFT JOIN sys_user c ON a.create_by_id =  c.id
        left join sys_office so on so.id = c.office_id and so.del_flag = 0
        left join sys_user_manage_office sumo on sumo.office_id = c.office_id
        LEFT JOIN act_ru_task d ON a.proc_ins_id = d.PROC_INST_ID_
        where a.signature_document_id like concat ('%',#{documentId},'%') and a.del_flag = 0
    </select>

    <select id="getByContractId" resultType="com.jeeplus.finance.signatureDaily.service.dto.SignatureDailyOfficeWorkDTO">
        select distinct
            a.id,
            a.create_by_id,
            a.create_time,
            a.signature_name,
            a.number,
            a.signature_type,
            a.status,
            a.remarks,
            a.proc_ins_id,
            a.process_definition_id,
            a.signature_contract_id,
            a.signature_document_id,
            a.signature_url,
            a.signature_url_flag,
            a.signature_file_name,
            a.signature_upload_date,
            a.signature_upload_file_user,
            a.signature_flag,
            c.name as createName,
            d.ID_ AS task_id,
            so.name as officeName
        from cw_signature_daily_office_work a
        LEFT JOIN sys_user c ON a.create_by_id =  c.id
        left join sys_office so on so.id = c.office_id and so.del_flag = 0
        left join sys_user_manage_office sumo on sumo.office_id = c.office_id
        LEFT JOIN act_ru_task d ON a.proc_ins_id = d.PROC_INST_ID_
        where a.signature_contract_id = #{contractId} and a.del_flag = 0
    </select>

    <insert id="insertProjectReportFile">
        insert into cw_signature_daily_file (
        id,
        create_by_id,
        create_time,
        update_by_id,
        update_time,
        del_flag,
        report_file_id,
        file_type,
        seal_type,
        remarks,
        file_affiliation
        )
        values
        (
        #{id},
       #{createById},
       #{createTime},
       #{updateById},
       #{updateTime},
       #{delFlag},
       #{reportFileId},
       #{fileType},
       #{sealType},
       #{remarks},
       #{fileAffiliation}
        )
    </insert>

</mapper>