package com.jy.business.contract.dao;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.jy.business.capital.model.table.CapitalAccountTable;
import com.jy.business.capital.model.table.CapitalTransactionsTable;
import com.jy.business.contract.mapper.ContractInfoMapper;
import com.jy.business.contract.model.dto.ContractInfoSelectDto;
import com.jy.business.contract.model.entity.ContractInfo;
import com.jy.business.contract.model.table.ContractDetailTable;
import com.jy.business.contract.model.table.ContractInfoTable;
import com.jy.business.contract.model.vo.ContractInfoVo;
import com.jy.business.contract.model.vo.ContractStatisticsVo;
import com.jy.business.contract.model.vo.TransactionVo;
import com.jy.business.payment.model.entity.PaymentRequestsDetail;
import com.jy.business.payment.model.table.PaymentRemitTable;
import com.jy.business.payment.model.table.PaymentRequestsDetailTable;
import com.jy.business.payment.model.table.PaymentRequestsTable;
import com.jy.framework.model.base.BaseDao;
import com.jy.framework.mybatis.join.TempTable;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class ContractInfoDao extends BaseDao<ContractInfoMapper, ContractInfo> {

    public List<ContractStatisticsVo> getStatistics(ContractInfoSelectDto dto) {
        ContractInfoTable ci = ContractInfoTable.ci;
        CapitalTransactionsTable ct = CapitalTransactionsTable.ct;
        PaymentRequestsDetailTable prd = PaymentRequestsDetailTable.prd;
        PaymentRequestsTable pr = PaymentRequestsTable.pr;
        PaymentRemitTable pre = PaymentRemitTable.payment_remit;

        TempTable t = sql().select(
                        _ifNull(_sum(ct.amount), 0).as(ContractStatisticsVo::getFundReceivedAmount),
                        ci.sellerCompany
                )
                .from(ci)
                .leftJoin(ct).on(ct.targetType.eq(70), ci.id.eq(ct.contractId))
                .groupBy(ci.sellerCompany)
                .toTable("t");

        TempTable t2 = sql().select(
                        _ifNull(_sum(prd.amount), 0).as("amount"),
                        ci.sellerCompany
                )
                .from(prd)
                .innerJoin(ci).on(ci.id.eq(prd.contractId))
                .innerJoin(pr).on(prd.paymentRequestsId.eq(pr.id))
                .innerJoin(pre).on(pre.paymentRequestsId.eq(pr.id))
                .groupBy(ci.sellerCompany)
                .toTable("t2");

        return sql(ContractStatisticsVo.class)
                .select(
                        ci.sellerCompany,
                        _count(0).as(ContractStatisticsVo::getContractQuantity),
                        _sum(ci.amount.multiply(ci.exchangeRate)).as(ContractStatisticsVo::getContractAmount),
                        t.field(ContractStatisticsVo::getFundReceivedAmount).multiply(ci.exchangeRate)
                                .as(ContractStatisticsVo::getFundReceivedAmount),
                        t.field(ContractStatisticsVo::getFundReceivedAmount).multiply(ci.exchangeRate)
                                .subtract(_ifNull(t2.field("amount"), 0)).as(ContractStatisticsVo::getProfit)
                )
                .from(ci)
                .leftJoin(t).on(ci.sellerCompany.eq(t.field(ContractInfo::getSellerCompany)))
                .leftJoin(t2).on(ci.sellerCompany.eq(t2.field(ContractInfo::getSellerCompany)))
                .where(
                        ci.contractNo.like(dto.getContractNo()),
                        ci.status.eq(dto.getStatus()),
                        ci.signingDate.between(dto.getStartDate(), dto.getEndDate())
                )
                .groupBy(ci.sellerCompany)
                .list();
    }

    /**
     * 合同列表
     */
    public List<ContractInfoVo> getList(ContractInfoSelectDto dto) {
        ContractInfoTable ci = ContractInfoTable.ci;

        return sql(ContractInfoVo.class)
                .select(
                        ci.all
                )
                .from(ci)
                .where(
                )
                .orderBy(
                        ci.id.desc()
                )
                .list();
    }

    /**
     * 合同分页
     */
    public Page<ContractInfoVo> getPage(ContractInfoSelectDto dto) {
        ContractInfoTable ci = ContractInfoTable.ci;
        ContractDetailTable cd = ContractDetailTable.cd;

        return sql(ContractInfoVo.class)
                .select(
                        ci.all.exclude(ci.amount),
                        ci.amount.multiply(ci.exchangeRate).as(ContractInfoVo::getAmount),
                        sql()
                                .select(_sum(_ifNull(cd.costPrice, 0).multiply(cd.quantity))).from(cd).where(ci.id.eq(cd.contractId))
                                .add(ci.freightPrice.multiply(ci.exchangeRate))
                                .add(ci.insurance.multiply(ci.exchangeRate))
                                .as(ContractInfoVo::getCostPrice)
                )
                .from(ci)
                .where(
                        ci.contractNo.like(dto.getContractNo()),
                        ci.status.eq(dto.getStatus()),
                        ci.signingDate.between(dto.getStartDate(), dto.getEndDate())
                )
                .orderBy(
                        ci.id.desc()
                )
                .page(dto.getPage());
    }

    /**
     * 合同明细
     */
    public ContractInfoVo getDetail(Long id) {
        ContractInfoTable ci = ContractInfoTable.ci;

        return sql(ContractInfoVo.class)
                .select(
                        ci.all
                )
                .from(ci)
                .where(
                        ci.id.eq(id)
                )
                .one();
    }

    public List<TransactionVo> getTransactionList(Long id) {
        CapitalTransactionsTable ct = CapitalTransactionsTable.ct;
        CapitalAccountTable ca = CapitalAccountTable.ca;
        PaymentRequestsDetailTable prd = PaymentRequestsDetailTable.prd;
        PaymentRequestsTable pr = PaymentRequestsTable.pr;
        PaymentRemitTable pre = PaymentRemitTable.payment_remit;
        ContractInfoTable ci = ContractInfoTable.ci;

        // 请款
        TempTable t = sql()
                .select(
                        pre.remitTime.as(TransactionVo::getTradingTime),
                        column("1").as(TransactionVo::getType),
                        prd.amount.as(TransactionVo::getTradingAmount),
                        prd.remark.as(TransactionVo::getRemark),
                        ca.depositBank.as(TransactionVo::getDepositBank),
                        ca.accountName.as(TransactionVo::getAccountName),
                        ca.account.as(TransactionVo::getAccount),
                        pr.depositBank.as(TransactionVo::getTargetDepositBank),
                        pr.accountName.as(TransactionVo::getTargetAccountName),
                        pr.account.as(TransactionVo::getTargetAccount)
                )
                .from(prd)
                .innerJoin(pr).on(prd.paymentRequestsId.eq(pr.id))
                .innerJoin(pre).on(pre.paymentRequestsId.eq(pr.id))
                .innerJoin(ca).on(pre.capitalAccountId.eq(ca.id))
                .where(
                        prd.contractId.eq(id)
                )

                .unionAll()

                // 到账认领
                .select(
                        ct.tradingTime.as(TransactionVo::getTradingTime),
                        column("2").as(TransactionVo::getType),
                        ct.amount.multiply(ci.exchangeRate).as(TransactionVo::getTradingAmount),
                        ct.remark.as(TransactionVo::getRemark),
                        ca.depositBank.as(TransactionVo::getDepositBank),
                        ca.accountName.as(TransactionVo::getAccountName),
                        ca.account.as(TransactionVo::getAccount),
                        ct.targetDepositBank.as(TransactionVo::getTargetDepositBank),
                        ct.targetAccountName.as(TransactionVo::getTargetAccountName),
                        ct.targetAccount.as(TransactionVo::getTargetAccount)
                )
                .from(ct)
                .innerJoin(ci).on(ct.contractId.eq(ci.id))
                .leftJoin(ca).on(ct.capitalAccountId.eq(ca.id))
                .where(
                        ct.contractId.eq(id),
                        ct.targetType.eq(70)
                ).toTable("t");

        return sql(TransactionVo.class)
                .select(column("*"))
                .from(t)
                .orderBy(t.field(TransactionVo::getTradingTime).desc())
                .list();
    }

    public List<PaymentRequestsDetail> getPaymentList(List<Long> idList) {
        PaymentRequestsDetailTable prd = PaymentRequestsDetailTable.prd;
        PaymentRequestsTable pr = PaymentRequestsTable.pr;
        PaymentRemitTable pre = PaymentRemitTable.payment_remit;

        return sql(PaymentRequestsDetail.class).select(
                        prd.contractId,
                        _ifNull(_sum(prd.amount), 0).as(PaymentRequestsDetail::getAmount)
                )
                .from(prd)
                .innerJoin(pr).on(prd.paymentRequestsId.eq(pr.id))
                .innerJoin(pre).on(pre.paymentRequestsId.eq(pr.id))
                .where(
                        prd.contractId.in(idList)
                )
                .groupBy(
                        prd.contractId
                )
                .list();
    }

}
