package cn.ysk.cashier.repository.shop;

import cn.ysk.cashier.pojo.shop.TbShopPurveyorTransact;
import cn.ysk.cashier.vo.PurveyorTransactVO;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.Map;

/**
* @website https://eladmin.vip
* @author lyf
* @date 2024-01-23
**/
public interface TbShopPurveyorTransactRepository extends JpaRepository<TbShopPurveyorTransact, Integer>, JpaSpecificationExecutor<TbShopPurveyorTransact> {

    @Query(value = "SELECT " +
            "IFNULL(sum( b.wait_amount ), 0 ) AS waitAmount," +
            "IFNULL(count( b.id ), 0 ) AS waitCount," +
            "IFNULL(SUM(b.paid_amount),0) AS paidAmount," +
            "IFNULL(SUM(b.total_amount),0) AS totalAmount" +
            " FROM " +
            " tb_shop_purveyor_transact b" +
            " WHERE" +
            " b.purveyor_id = :purveyorId" +
            " AND (:type IS NULL OR b.type = :type)",nativeQuery = true)
    Map<String,Object> findBySum(@Param("purveyorId") String purveyorId,@Param("type")String type);

    @Query("SELECT new cn.ysk.cashier.vo.PurveyorTransactVO(" +
            "b.id," +
            "b.purveyorName," +
            "b.purveyorTelephone," +
            "b.address," +
            "b.remark," +
            "b.lastTransactAt," +
            "COALESCE(SUM(CASE WHEN a.status=0 THEN a.waitAmount ELSE 0 END), 0)," +
            "COUNT(CASE WHEN a.status = 0 THEN a.id ELSE NULL END)) " +
            "from " +
            "TbShopPurveyor b " +
            "LEFT JOIN TbShopPurveyorTransact a ON a.purveyorId = b.id " +
            "WHERE " +
            "1=1 " +
            "AND (:type IS NULL OR  a.type = :type )" +
            "AND (:shopId IS NULL OR  b.shopId = :shopId) " +
            "AND (:name IS NULL OR b.purveyorName LIKE %:name%) " +
            "GROUP BY b.id " +
            "order by b.lastTransactAt desc,a.purveyorId")
    Page<PurveyorTransactVO> queryTransactDate(String type, String shopId, String name,  Pageable page);

    @Query(value = "SELECT IFNULL(count(b.id),0) AS waitNumber FROM tb_shop_purveyor_transact b WHERE" +
            " b.purveyor_id = :purveyorId  AND (:type IS NULL OR b.type = :type) AND b.`status`= 0",nativeQuery = true)
    Map<String,Object> findByStatusSum(@Param("purveyorId") String purveyorId,@Param("type")String type);
}