<?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="cn.com.ty.lift.business.contract.dao.mapper.ContractsMapper">
    <resultMap id="BaseResultMap" type="cn.com.ty.lift.business.contract.dao.entity.Contracts">
        <id column="id" property="id" jdbcType="BIGINT"/>

        <!--这个地方要关联查询-->
        <result column="project_id" property="projectId" jdbcType="BIGINT"/>

        <result column="mt_company_id" property="mtCompanyId" jdbcType="BIGINT"/>

        <!--合同自定义id-->
        <result column="code" property="code" jdbcType="VARCHAR"/>

        <result column="source" property="source" jdbcType="INTEGER"/>
        <result column="pay_mode" property="payMode" jdbcType="VARCHAR"/>
        <result column="lift_num" property="liftNum" jdbcType="INTEGER"/>

        <result column="moneys" property="moneys" jdbcType="DECIMAL"/>

        <!--应收金额-->
        <result column="plan_money_total" property="planMoneyTotal" jdbcType="DECIMAL"/>
        <!--实收-->
        <result column="work_money_total" property="workMoneyTotal" jdbcType="DECIMAL"/>
        <!--开票金额-->
        <result column="invoice_money_total" property="invoiceMoneyTotal" jdbcType="DECIMAL"/>

        <result column="type" property="type" jdbcType="INTEGER"/>
        <result column="status" property="status" jdbcType="INTEGER"/>
        <result column="given_date" property="givenDate" jdbcType="TIMESTAMP"/>
        <result column="given_user" property="givenUser" jdbcType="VARCHAR"/>
        <result column="star_date" property="starDate" jdbcType="TIMESTAMP"/>
        <result column="end_date" property="endDate" jdbcType="TIMESTAMP"/>
        <result column="create_date" property="createDate" jdbcType="TIMESTAMP"/>
        <result column="next_id" property="nextId" jdbcType="BIGINT"/>
        <result column="accessory_url" property="accessoryUrl" jdbcType="LONGVARCHAR"/>
        <result column="end_reason" property="endReason" jdbcType="LONGVARCHAR"/>

        <!--新增开始-->
        <result column="parts_rules" property="partsRules" jdbcType="LONGVARCHAR"/>
        <!--新增结束-->

        <result column="remarks" property="remarks" jdbcType="LONGVARCHAR"/>
        <result column="previous_id" property="previousId" jdbcType="BIGINT"/>
    </resultMap>

    <sql id="Base_Column_List">
		id, project_id, mt_company_id, code, source, pay_mode, lift_num, moneys, type, status,
		given_date, given_user, star_date, end_date, create_date, next_id, previous_id, accessory_url, end_reason, remarks,
		parts_rules
	</sql>

    <!-- 根据项目、合同号、合同类型、合同状态查询合同管理列表-->
    <select id="findByCondition"
            resultType="cn.com.ty.lift.business.contract.dao.entity.model.response.ContractResponse"
            parameterType="cn.com.ty.lift.business.contract.dao.entity.model.request.ContractRequest">
        SELECT
        c.id AS id,
        c.code AS contractCode,
        c.next_id AS nextId,
        c.source AS contractSource,
        c.type AS contractType,
        c.star_date AS starDate,
        c.end_date AS endDate,
        c.lift_num AS liftNum,
        c.moneys AS moneys,
        c.given_date AS givenDate,
        c.given_user AS givenUser,
        c.accessory_url AS accessoryUrl,
        c.remarks AS remarks,
        c.parts_rules AS partsRules,
        c.status AS contractStatus,
        p.project_name AS projectName,
        c.is_check AS isCheck
        FROM contracts c
        LEFT JOIN project p ON c.project_id = p.id
        WHERE c.mt_company_id = #{request.mtCompanyId,jdbcType=BIGINT}

        <if test="request.contractCode!=null and request.contractCode!=''">
            AND c.code LIKE CONCAT('%',#{request.contractCode,jdbcType=VARCHAR},'%')
        </if>
        <if test="request.contractType!=null and request.contractType!=''">
            AND c.type = #{request.contractType,jdbcType=INTEGER}
        </if>
        <if test="request.contractStatus!=null and request.contractStatus!=''">
            AND c.status = #{request.contractStatus,jdbcType=INTEGER}
        </if>
        <if test="request.projectName!=null and request.projectName!=''">
            AND p.project_name LIKE CONCAT('%',#{request.projectName,jdbcType=VARCHAR},'%')
        </if>
        <!--新增时间搜索-->
        <!--开始时间-->
        <if test="request.starDate!=null and request.starDate!=''">
            AND c.star_date >=#{request.starDate}
        </if>

        <!--结束时间-->
        <if test="request.endDate!=null and request.endDate!=''">
            AND c.end_date >=#{request.endDate}
        </if>

    </select>

    <select id="findAllContracts"
            parameterType="cn.com.ty.lift.business.contract.dao.entity.model.request.ContractRequest"
            resultType="cn.com.ty.lift.business.contract.dao.entity.model.response.ContractExportDto">
           select
              <!--合同编号-->
               c.code as contractCode,

               <!--客户名称-->
               (select attestation_name from mt_company_attestation where mt_company_attestation.mt_company_id=c.mt_company_id) as memberName,

               <!--收款类型-->
               case when pay.type = 1 then '维保费' else '大修费' end as payType,

               <!--收款编号-->
               pay.code as paycode,

               <!--项目名称-->
               p.project_name as projectName,

               <!--项目内容-->
               CONCAT(c.lift_num,'台',case when pay.periods = '' or pay.periods is null then '1' else  pay.periods end,'期',p.start_date,'至',p.end_date) as projectContent,

               <!--本期应收-->
               pay.plan_money -pay.work_money as nowMoney,

               <!--合同金额-->
               c.moneys as moneys,

               <!--应收日期-->
               pay.plan_date as planDate,

              <!--期数-->
               pay.periods as periods,

              <!--收款责任人-->
               pay.collection_man as collectionMan,

              <!--区域-->
              (select area_name from region where region.id=p.region_id) as regionName,

              <!--帐龄-->
               pay.work_date - pay.plan_date as paymentDays,

              <!--开票金额-->
               pay.amount_invoice as amountInvoice,

              <!--开票时间-->
              pay.billing_date as billingDate,

              <!--发票类型-->
              pay.invoice_type as invoiceType,

              <!--到账日期-->
              pay.work_date as workDate,

             <!--实收金额-->
              pay.work_money as workMoney
             <!--表关联-->
            from project p
            LEFT JOIN
             contracts c on c.project_id=p.id
            left join payment pay on pay.contracts_id=c.id
            <!--筛选条件-->
        WHERE c.mt_company_id = #{request.mtCompanyId,jdbcType=BIGINT}
        <if test="request.contractCode!=null and request.contractCode!=''">
            AND c.code LIKE CONCAT('%',#{request.contractCode,jdbcType=VARCHAR},'%')
        </if>
        <if test="request.contractType!=null and request.contractType!=''">
            AND c.type = #{request.contractType,jdbcType=INTEGER}
        </if>
        <if test="request.contractStatus!=null and request.contractStatus!=''">
            AND c.status = #{request.contractStatus,jdbcType=INTEGER}
        </if>
        <if test="request.projectName!=null and request.projectName!=''">
            AND p.project_name LIKE CONCAT('%',#{request.projectName,jdbcType=VARCHAR},'%')
        </if>
        <!--新增时间搜索-->
        <!--开始时间-->
        <if test="request.starDate!=null and request.starDate!=''">
            AND c.star_date >=#{request.starDate}
        </if>
        <!--结束时间-->
        <if test="request.endDate!=null and request.endDate!=''">
            AND c.end_date >=#{request.endDate}
        </if>
</select>


    <select id="findAllErContracts"
            parameterType="cn.com.ty.lift.business.contract.dao.entity.model.request.ContractRequest"
            resultType="cn.com.ty.lift.business.contract.dao.entity.model.response.ContractExportDto">
     select
          <!--合同编号-->
          c.code as contractCode,
          <!--客户名称-->
          (select attestation_name from mt_company_attestation where mt_company_attestation.mt_company_id=c.mt_company_id) as memberName,
          <!--收款类型 默认T修理-->
          CONCAT('T修理')as payType,
          <!--收款编号 无-->
          <!--项目名称-->
          p.project_name as projectName,
          <!--项目内容-->
          CONCAT(p.project_name,emr.caller_fault_description) as projectContent,
          <!--本期应收-->
          erc.cost_money -erc.pay_money as nowMoney,
          <!--合同金额-->
          c.moneys as moneys,
          <!--应收日期-->
          erc.cost_date as planDate,
          <!--期数-->
          <!--收款责任人-->
          erc.collection_man as collectionMan,
          <!--区域-->
          (select area_name from region where region.id=p.region_id) as regionName,
          <!--帐龄-->
          erc.pay_date - erc.cost_date as paymentDays,
          <!--开票金额-->
          erc.invoice_money as amountInvoice,
          <!--开票时间-->
          erc.invoice_date as billingDate,
          <!--发票类型-->
          erc.invoice_type as invoiceType,
          <!--到账时间-->
          erc.pay_date as workDate,
          <!--到账金额-->
          erc.pay_money as workMoney
          <!--表关联-->
     from er_record_cost erc
     LEFT JOIN emergency_repair emr on erc.er_record_id=emr.id
     LEFT JOIN project p on emr.project_id=p.id
     left join contracts c on c.project_id=p.id
        <!--筛选条件-->
        WHERE c.mt_company_id = #{request.mtCompanyId,jdbcType=BIGINT}
        <if test="request.contractCode!=null and request.contractCode!=''">
            AND c.code LIKE CONCAT('%',#{request.contractCode,jdbcType=VARCHAR},'%')
        </if>
        <if test="request.contractType!=null and request.contractType!=''">
            AND c.type = #{request.contractType,jdbcType=INTEGER}
        </if>
        <if test="request.contractStatus!=null and request.contractStatus!=''">
            AND c.status = #{request.contractStatus,jdbcType=INTEGER}
        </if>
        <if test="request.projectName!=null and request.projectName!=''">
            AND p.project_name LIKE CONCAT('%',#{request.projectName,jdbcType=VARCHAR},'%')
        </if>
        <!--新增时间搜索-->
        <!--开始时间-->
        <if test="request.starDate!=null and request.starDate!=''">
            AND c.star_date >=#{request.starDate}
        </if>
        <!--结束时间-->
        <if test="request.endDate!=null and request.endDate!=''">
            AND c.end_date >=#{request.endDate}
        </if>
</select>
</mapper>
