package com.bailuntec.balancesheet.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.Date;


@Repository
public interface DcOtherCostMapper {
    /**
     * 生成昨天 其他应收的 一级明细数据
     * @param lastDay
     * @param twoDaysBefore
     */
    @Insert("insert into dc_other_cost(day,type,company_value,company_name,borrower_or_payer,start_amount,debit_amount,credit_amount,end_amount) " +
            "select #{lastDay},1,a.company_value,a.company_name,a.bank_company,ifnull(b.end_amount,0) startAmount,sum(ifnull(c.amount_rmb,0)) debitAmount," +
            "sum(ifnull(d.amount_rmb,0)) creditAmount,ifnull(b.end_amount,0) + sum(ifnull(c.amount_rmb,0)) - sum(ifnull(d.amount_rmb,0)) endAmount " +
            "from (select distinct company_value,company_name,bank_company from dc_base_cost where cost_form = 3 and day <= #{lastDay}) a " +
            "left join (select company_value,borrower_or_payer,end_amount from dc_other_cost where day = #{twoDaysBefore} and type = 1) b " +
            "on a.company_value = b.company_value and a.bank_company = b.borrower_or_payer " +
            "left join (select company_value,bank_company,amount_rmb from dc_base_cost where day = #{lastDay} and cost_form = 3 and is_lend = 1 and cost_status = 4) c " +
            "on a.company_value = c.company_value and a.bank_company = c.bank_company " +
            "left join (select company_value,bank_company,amount_rmb from dc_base_cost where day = #{lastDay} and cost_form = 3 and is_lend = 2 and cost_status = 4) d " +
            "on a.company_value = d.company_value and a.bank_company = d.bank_company " +
            "where a.company_value is not null " +
            "group by a.company_value,a.bank_company")
    void insertOneDayOtherReceive(@Param("lastDay") Date lastDay, @Param("twoDaysBefore") Date twoDaysBefore);

    /**
     * 生成昨天 其他应付的 一级明细数据
     * @param lastDay
     * @param twoDaysBefore
     */
    @Insert("insert into dc_other_cost(day,type,company_value,company_name,borrower_or_payer,start_amount,debit_amount,credit_amount,end_amount) " +
            "select #{lastDay},2,a.company_value,a.company_name,a.bank_company,ifnull(b.end_amount,0) startAmount,ifnull(sum(c.amount_rmb),0) debitAmount," +
            "ifnull(sum(d.amount_rmb),0) creditAmount,ifnull(b.end_amount,0) - ifnull(sum(c.amount_rmb),0) + ifnull(sum(d.amount_rmb),0) endAmount " +
            "from (select distinct company_value,company_name,bank_company from dc_base_cost where day <= #{lastDay}  and (cost_form = 1 or cost_form = 2)) a " +
            "left join (select company_value,borrower_or_payer,end_amount from dc_other_cost where day = #{twoDaysBefore} and type = 2) b " +
            "on a.company_value = b.company_value and a.bank_company = b.borrower_or_payer " +
            "left join (select company_value,bank_company,amount_rmb from dc_base_cost where cost_form = 1 and cost_status = 4 and day = #{lastDay}) c " +
            "on a.company_value = c.company_value and a.bank_company = c.bank_company " +
            "left join (select company_value,bank_company,amount_rmb from dc_base_cost where cost_form = 1 and cost_status = 2 and day = #{lastDay} " +
            "union select company_value,bank_company,amount_rmb from dc_base_cost where cost_form = 2 and cost_status = 4 and day = #{lastDay}) d " +
            "on a.company_value = d.company_value and a.bank_company = d.bank_company " +
            "where a.company_value is not null " +
            "group by a.company_value,a.bank_company")
    void insertOneDayOtherPay(@Param("lastDay") Date lastDay, @Param("twoDaysBefore") Date twoDaysBefore);

    /**
     * 其他应收款
     * @return
     */
    @Select("select sum(end_amount) from dc_other_cost where day = #{lastDay} and type = 1")
    BigDecimal getOtherAccountsReceivableSum(@Param("lastDay") Date lastDay);

    /**
     * 其他应付款
     * @return
     */
    @Select("select sum(end_amount) from dc_other_cost where day = #{lastDay} and type = 2")
    BigDecimal getOtherAccountsPayableSum(@Param("lastDay") Date lastDay);

}
