<select id="queryReportAssociatedAcp" resultMap="AssociatedInfo">
 
    SELECT
    r.requisition_number AS business_code,
    r.id AS header_id,
    r.document_type_id AS reportTypeId,
    r.applicant_id as employeeId
    FROM
    fec_expense.exp_report_payment_schedule b,fec_expense.exp_report_header r
    WHERE
    b.exp_report_header_id=r.id and r.`status`=1004
    and
    b.frozen_flag ="Y"
    and r.applicant_id=#{applicationId}
    <if test="reportNumber!=null and reportNumber != ''">
      AND r.requisition_number LIKE concat(
      '%',
      concat(#{reportNumber,jdbcType=VARCHAR}, '%'))
    </if>
    <if test="documentTypeId !=null and reportNumber != ''">
      AND r.document_type_id = #{documentTypeId}
    </if>
    <if test="formTypes != null and formTypes.size > 0">
      AND r.document_type_id IN
      <foreach collection="formTypes" item="formId" open="(" separator="," close=")">
        #{formId}
      </foreach>
    </if>
    AND (
    b.amount - ( SELECT
    COALESCE(sum( c.write_off_amount ), 0) AS write_off_amount
    FROM
    csh_write_off c
    WHERE
    c.document_header_id = b.exp_report_header_id
    AND c.document_line_id = b.id
    AND ( c.STATUS = 'Y' OR ( c.STATUS = 'P' AND c.operation_type = 'WRITE_OFF' ) )
    ) - (
    SELECT
    COALESCE(sum( a.amount ), 0) AS commit_amount
    FROM
    csh_data_relation_acp a
    WHERE
    a.report_head_id = b.exp_report_header_id
    AND a.report_line_id = b.id
    AND a.document_type = 'ACP_REQUISITION'
    ) > 0
    )
    GROUP BY
    r.requisition_number,
    b.exp_report_header_id,
    r.document_type_id,
    b.applicant_id
    ORDER BY
    r.requisition_number
 
  </select>

<resultMap id="AssociatedInfo" type="com.hand.hcf.app.payment.web.dto.CashDataPublicReportHeaderDTO">
    <result column="header_id" property="reportHeadId"/>
    <result column="business_code" property="reportNumber"/>
    <result column="form_name" property="reportTypeName"/>
    <collection property="lineList" column="{headerId=header_id}"
          ofType="ArrayList" select="getPaymentInfo"/>
  </resultMap>
  <select id="getPaymentInfo" resultType="com.hand.hcf.app.payment.web.dto.CashDataPublicReportLineDTO">
    SELECT
	temp.id scheduleLineId,
	"" AS cshTransactionId,
	temp.amount,
	temp.associated_amount associatedAmount,
	(
	temp.amount - temp.associated_amount - (
SELECT COALESCE
	( sum( c.write_off_amount ), 0 ) AS write_off_amount
FROM
	csh_write_off c
WHERE
	c.document_header_id = temp.exp_report_header_id
	AND c.document_line_id = temp.id
	AND c.document_type = "PUBLIC_REPORT"
	AND ( c.STATUS = 'Y' OR ( c.STATUS = 'P' AND c.operation_type = 'WRITE_OFF' ) )
	)
	) AS availableAmount,
	temp.exp_report_header_id expReportHeaderId,
	0 AS scheduleLineNumber,
	temp.company_id companyId,
	temp.currency_code currency,
	temp.description description,
	temp.exchange_rate exchangeRate,
	temp.payment_schedule_date schedulePaymentDate,
	temp.payment_method paymentMethod,
	temp.payment_type,
	temp.prop_flag prop_flag,
	temp.csh_transaction_class_id cshTransactionClassId,
	( SELECT ctc.description FROM csh_transaction_class ctc WHERE ctc.id = temp.csh_transaction_class_id ) AS cshTransactionClassName,
	temp.cash_flow_item_id cashFlowItemId,
	temp.payee_category payeeCategory,
	temp.payee_id payeeId,
	temp.account_number accountNumber,
	temp.account_name accountName,
	temp.bank_code bankCode,
	temp.bank_name bankName,
	temp.bank_code bankLocationCode,
	temp.bank_name bankLocationName,
	"" provinceCode,
	"" provinceName,
	""cityCode,
	"" cityName,
	 (select c.type_code from csh_transaction_class c where c.id=temp.csh_transaction_class_id ) cshTransactionTypeCode,
	""contractHeaderId
FROM
	(
SELECT
  b.*,
	(
SELECT COALESCE
	( sum( a.amount ), 0 ) AS associated_amount
FROM
	csh_data_relation_acp a
WHERE
	a.report_head_id = b.exp_report_header_id
	AND a.report_line_id = b.id
	AND a.document_type = 'ACP_REQUISITION'
	) AS associated_amount
FROM
	fec_expense.exp_report_payment_schedule b ,fec_expense.exp_report_header r
WHERE
	b.frozen_flag = "Y"
	and b.exp_report_header_id=r.id
	AND b.exp_report_header_id = #{headerId}
	) temp
 
  </select>

package com.hand.hcf.app.payment.web.dto;
 
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import lombok.Data;
 
import java.time.ZonedDateTime;
import java.util.List;
 
@Data
public class CashDataPublicReportHeaderDTO {
 
  @JsonSerialize(using = ToStringSerializer.class)
  private Long reportHeadId;//報賬單頭ID
 
  private String reportNumber;//報賬單編號
 
  private String reportTypeName;//報賬單類型
  @JsonSerialize(using = ToStringSerializer.class)
  private Long reportTypeId;// 報賬單類型ID
 
  private List<CashDataPublicReportLineDTO> lineList;//報賬單計劃付款行
 
  @JsonSerialize(using = ToStringSerializer.class)
  private Long employeeId;//員工ID
  private String employeeName;//員工名稱
 
  private ZonedDateTime requisitionDate; // 申請日期
}