<?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.fjhx.account.mapper.account.AccountRunningWaterMapper">
    <!--这段代码正常是不需要跨库查询的。但是一开始封装包的时候分装的不合理，pom里面包是不能互相引用的。account包继承sale包会出现依赖冲突，目前不好改动，只能跨库查询-->
    <select id="getPage" resultType="com.fjhx.account.entity.account.vo.AccountRunningWaterVo">
        SELECT
            arw.*,
            IF
                (
                        arw.type = 60,
                        ( SELECT c.`code` FROM bytesailing_jxst.sales_contract c WHERE c.id = arw.business_id ),
                        IF
                            (
                                    arw.type = 40,
                                    (
                                        SELECT
                                            GROUP_CONCAT( c.`code` )
                                        FROM
                                            tax_refund_details trd
                                                LEFT JOIN bytesailing_jxst.sales_contract c ON c.id = trd.contract_id
                                        WHERE
                                            trd.account_running_water_id = arw.id
                                    ),
                                    IF
                                        (
                                                arw.type = 30,
                                                (
                                                    SELECT
                                                        GROUP_CONCAT( clc.contract_code )
                                                    FROM
                                                        bytesailing_sale.claim cl
                                                            LEFT JOIN bytesailing_sale.claim_contract clc ON clc.claim_id = cl.id
                                                    WHERE
                                                        cl.business_id = arw.id
                                                ),
                                                (
                                                    SELECT
                                                        IF
                                                            (
                                                                    t1.type = 20,
                                                                    (
                                                                        SELECT
                                                                            GROUP_CONCAT( c.`code` )
                                                                        FROM
                                                                            bytesailing_purchase.pay pa
                                                                                LEFT JOIN bytesailing_purchase.pay_detail pad ON pad.pay_id = pa.id
                                                                        LEFT JOIN bytesailing_purchase.purchase p ON pad.purchase_id = p.id
                                                                        LEFT JOIN bytesailing_jxst.sales_contract c ON p.data_resource_id = c.id
                                                                        WHERE
                                                                        t1.business_id = pa.id
                                                            ),(
						SELECT
							GROUP_CONCAT( t4.`code` )
						FROM
							account_request_funds t2
							LEFT JOIN account_request_funds_detail t3 ON t2.id = t3.account_request_funds_id
							LEFT JOIN bytesailing_jxst.sales_contract t4 ON t3.contract_id = t4.id
						WHERE
							t1.business_id = t2.id
						)
					) contractCodes
                                                    FROM
                                                        account_payment t1
                                                    WHERE
                                                        t1.id = arw.business_id
                                                )
                                        )
                            )
                ) AS contractCodes,
            IF
                (
                        arw.type = 60,
                        ( SELECT c.`id` FROM bytesailing_jxst.sales_contract c WHERE c.id = arw.business_id ),
                        IF
                            (
                                    arw.type = 40,
                                    (
                                        SELECT
                                            GROUP_CONCAT( c.`id` )
                                        FROM
                                            tax_refund_details trd
                                                LEFT JOIN bytesailing_jxst.sales_contract c ON c.id = trd.contract_id
                                        WHERE
                                            trd.account_running_water_id = arw.id
                                    ),
                                    IF
                                        (
                                                arw.type = 30,
                                                (
                                                    SELECT
                                                        GROUP_CONCAT( clc.contract_id )
                                                    FROM
                                                        bytesailing_sale.claim cl
                                                            LEFT JOIN bytesailing_sale.claim_contract clc ON clc.claim_id = cl.id
                                                    WHERE
                                                        cl.business_id = arw.id
                                                ),
                                                (
                                                    SELECT
                                                        IF
                                                            (
                                                                    t1.type = 20,
                                                                    (
                                                                        SELECT
                                                                            GROUP_CONCAT( c.`id` )
                                                                        FROM
                                                                            bytesailing_purchase.pay pa
                                                                                LEFT JOIN bytesailing_purchase.pay_detail pad ON pad.pay_id = pa.id
                                                                        LEFT JOIN bytesailing_purchase.purchase p ON pad.purchase_id = p.id
                                                                        LEFT JOIN bytesailing_jxst.sales_contract c ON p.data_resource_id = c.id
                                                                        WHERE
                                                                        t1.business_id = pa.id
                                                            ),(
						SELECT
							GROUP_CONCAT( t4.`id` )
						FROM
							account_request_funds t2
							LEFT JOIN account_request_funds_detail t3 ON t2.id = t3.account_request_funds_id
							LEFT JOIN bytesailing_jxst.sales_contract t4 ON t3.contract_id = t4.id
						WHERE
							t1.business_id = t2.id
						)
					) contractCodes
                                                    FROM
                                                        account_payment t1
                                                    WHERE
                                                        t1.id = arw.business_id
                                                )
                                        )
                            )
                ) AS contractIds,
            am.alias accountManagementName,
            am.corporation_id
        FROM
            account_running_water arw
                LEFT JOIN account_management am ON arw.account_management_id = am.id
            ${ew.customSqlSegment}
    </select>
    <select id="getDetail" resultType="com.fjhx.account.entity.account.vo.AccountRunningWaterVo">
        select arw.id,
               arw.account_management_id,
               arw.status,
               arw.business_id,
               arw.amount,
               arw.currency,
               arw.name,
               arw.received,
               arw.opening_bank,
               arw.account_opening,
               arw.transaction_time,
               arw.remarks,
               arw.create_user,
               arw.create_time,
               arw.update_user,
               arw.update_time,
               am.name                       accountManagementName,
               am.corporation_id,
               ifnull(arw.is_transaction, 0) isTransaction,
               arw.transaction_dept_id
        from account_running_water arw
                 left join account_management am on arw.account_management_id = am.id
        where arw.id = #{id}
    </select>
    <select id="getList" resultType="com.fjhx.account.entity.account.vo.AccountRunningWaterVo">
        select arw.id,
               arw.account_management_id,
               arw.status,
               arw.business_id,
               IF(arw.status = 20, CONCAT("-", arw.amount), arw.amount) amount,
               arw.currency,
               arw.name,
               arw.received,
               arw.opening_bank,
               arw.account_opening,
               arw.transaction_time,
               arw.remarks,
               arw.create_user,
               arw.create_time,
               arw.update_user,
               arw.update_time,
               am.alias                                                 accountManagementName
        from account_running_water arw
                 left join account_management am on arw.account_management_id = am.id
            ${ew.customSqlSegment}
    </select>
    <select id="getAccountRunningWaterAmount" resultType="com.fjhx.account.entity.account.po.AccountRunningWater">
        SELECT arw.`status`,
               amount
        FROM account_running_water arw ${ew.customSqlSegment}
            LIMIT #{size}
    </select>

    <!-- 要切换到sale库才能执行 -->
    <update id="exchangeSettlementByContractId">
        update contract
        set is_exchange_settlement = #{status}
        where id = #{contractId}
    </update>

    <select id="getTotal" resultType="com.fjhx.account.entity.account.vo.AccountRunningWaterVo">
        SELECT
            arw.`status`,
            arw.currency,
            sum( ifnull(arw.amount,0) ) as amount
        FROM
            account_running_water arw
            ${ew.customSqlSegment}
    </select>

</mapper>
