package com.ff.weijinxiao.repository;


import com.ff.weijinxiao.domain.Supplier;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface SupplierRepository extends JpaRepository<Supplier, Integer>, JpaSpecificationExecutor<Supplier> {

    Supplier findByOnlyID(String onlyID);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(purchase_price*purchase_number)) THEN 0 ELSE (SUM(purchase_price*purchase_number) ) END  FROM tb_goods_batch WHERE uid = :uid AND isDelete=0", nativeQuery = true)
    Double getSupplierTotalAmount(String uid);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(realPayMoney)) THEN 0 ELSE (SUM(realPayMoney)  ) END  FROM tb_goods_batch WHERE uid = :uid AND isDelete=0", nativeQuery = true)
    Double getSupplierAlreadyPayed1(String uid);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(payAmount)) THEN 0 ELSE (SUM(payAmount)  ) END  FROM tb_paybacksupplier WHERE uid = :uid AND isDelete=0", nativeQuery = true)
    Double getSupplierAlreadyPayed2(String uid);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(discounts)) THEN 0 ELSE (SUM(discounts) ) END  FROM tb_goods_batch WHERE uid = :uid AND isDelete=0 ", nativeQuery = true)
    Double getSupplierDiscount(String uid);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(purchase_price*purchase_number)) THEN 0 ELSE (SUM(purchase_price*purchase_number) ) END  FROM tb_goods_batch WHERE uid = :uid AND isDelete=0 and supplier_id =:supplierOnlyId", nativeQuery = true)
    Double getSupplierTotalAmountBySupplierOnlyId(String uid, String supplierOnlyId);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(realPayMoney)) THEN 0 ELSE (SUM(realPayMoney)  ) END  FROM tb_goods_batch WHERE uid = :uid AND isDelete=0 and supplier_id =:supplierOnlyId", nativeQuery = true)
    Double getSupplierAlreadyPayed1BySupplierOnlyId(String uid, String supplierOnlyId);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(payAmount)) THEN 0 ELSE (SUM(payAmount)  ) END  FROM tb_paybacksupplier WHERE uid = :uid AND isDelete=0 and supplier_id =:supplierOnlyId", nativeQuery = true)
    Double getSupplierAlreadyPayed2BySupplierOnlyId(String uid, String supplierOnlyId);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(discounts)) THEN 0 ELSE (SUM(discounts) ) END  FROM tb_goods_batch WHERE uid = :uid AND isDelete=0 and supplier_id =:supplierOnlyId", nativeQuery = true)
    Double getSupplierDiscountBySupplierOnlyId(String uid, String supplierOnlyId);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(purchase_number)) THEN 0 ELSE (SUM(purchase_number) ) END  FROM tb_goods_batch WHERE uid = :uid AND isDelete=0 and supplier_id =:supplierOnlyId", nativeQuery = true)
    Double getSupplierPurchaseNumBySupplierOnlyId(String uid, String supplierOnlyId);

    @Query(value = "SELECT CASE WHEN ISNULL(SUM(stock_number)) THEN 0 ELSE (SUM(stock_number) ) END  FROM tb_goods_batch WHERE uid = :uid AND isDelete=0 and supplier_id =:supplierOnlyId", nativeQuery = true)
    Double getSupplierStockNumBySupplierOnlyId(String uid, String supplierOnlyId);


    //获取供应商累计进货金额，应付款，库存剩余
    @Query(value = "SELECT sum(gb.purchase_number*gb.purchase_price),sum(gb.purchase_number*gb.purchase_price- (CASE WHEN ISNULL(gb.realPayMoney) THEN 0 ELSE gb.realPayMoney END) - (CASE WHEN ISNULL(ps.payAmount) THEN 0 ELSE ps.payAmount END) - (CASE WHEN ISNULL(gb.discounts) THEN 0 ELSE gb.discounts END) ),sum(gb.stock_number) FROM tb_goods_batch gb LEFT JOIN tb_paybacksupplier ps ON ps.supplier_id = gb.supplier_id WHERE gb.uid = :uid and gb.supplier_id=:supplierId AND gb.isDelete=0", nativeQuery = true)
    List<Object[]> getSupplierExtInfo(String uid, String supplierId);
}
