package com.ff.weijinxiao.repository;


import com.ff.weijinxiao.domain.UserInfo;
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 UserInfoRepository extends JpaRepository<UserInfo, Integer>, JpaSpecificationExecutor<UserInfo> {
    UserInfo findByUid(String uid);

    UserInfo findByPhone(String phone);

    @Query(value = "select min(sp.name),max(gb.stock_number),sum(NUMBER) as selled_num, SUM(og.sales_volume) as selled_amount,SUM(profit) AS total_profit from tb_order_good og LEFT JOIN tb_supplier sp ON sp.onlyID = og.supplier_name LEFT JOIN tb_goods_batch gb ON gb.onlyID = og.goods_batch_id where og.uid = :uid and og.isDelete = '0' and og.supplier_name is not null and og.submit_date>=:beginTime and og.submit_date <= :endTime GROUP BY supplier_name ORDER BY total_profit desc", nativeQuery = true)
    List<Object[]> getSupplierReport(String uid, String beginTime, String endTime);

    @Query(value = "select  min(sp.name),sum(NUMBER) as selled_num, SUM(og.sales_volume) as selled_amount,SUM(profit) AS total_profit from tb_order_good og LEFT JOIN tb_goods sp ON sp.onlyID = og.goods_id   where og.uid = :uid and og.isDelete = '0' and og.submit_date>=:beginTime and og.submit_date <= :endTime GROUP BY goods_id ORDER BY total_profit desc", nativeQuery = true)
    List<Object[]> getProductReport(String uid, String beginTime, String endTime);

    @Query(value = "select  min(sp.name),sum(NUMBER) as selled_num, SUM(og.sales_volume) as selled_amount,SUM(profit) AS total_profit from tb_order_good og LEFT JOIN tb_custom sp ON sp.onlyID = og.customId   where og.uid = :uid and og.isDelete = '0' and og.submit_date>=:beginTime and og.submit_date <= :endTime GROUP BY customId ORDER BY total_profit desc", nativeQuery = true)
    List<Object[]> getCustomerReport(String uid, String beginTime, String endTime);


    //进货统计
    @Query(value = "SELECT gb.submit_date,gb.purchase_number,gb.purchase_price,gb.sell_price,gb.stock_number, s.name as supplier_name,g.name,gb.image,gb.explains FROM tb_goods_batch gb LEFT JOIN tb_supplier s ON s.onlyID=gb.supplier_id  left join tb_goods g ON g.onlyID=gb.goods_id WHERE gb.uid=:uid AND gb.submit_date >= :beginTime and gb.submit_date <= :endTime order by gb.submit_date desc ", nativeQuery = true)
    List<Object[]> getBusinessReport1(String uid, String beginTime, String endTime);

    @Query(value = "SELECT gb.submit_date,gb.purchase_number,gb.purchase_price,gb.sell_price,gb.stock_number, s.name as supplier_name,g.name,gb.image,gb.explains FROM tb_goods_batch gb LEFT JOIN tb_supplier s ON s.onlyID=gb.supplier_id  left join tb_goods g ON g.onlyID=gb.goods_id WHERE gb.uid=:uid AND gb.submit_date >= :beginTime and gb.submit_date <= :endTime and g.name like CONCAT('%',:goodsName,'%') order by gb.submit_date desc", nativeQuery = true)
    List<Object[]> getBusinessReport1WithName(String uid, String goodsName, String beginTime, String endTime);


    //出货统计
    @Query(value = "SELECT og.submit_date,gb.purchase_number,gb.purchase_price,gb.sell_price,gb.stock_number, s.name as supplier_name,g.name,gb.image,gb.explains,og.number,og.sales_volume,o.is_send,o.is_receivables FROM tb_order_good og LEFT JOIN tb_goods_batch gb ON gb.onlyID=og.goods_batch_id LEFT JOIN tb_supplier s ON s.onlyID=gb.supplier_id  left join tb_goods g ON g.onlyID=gb.goods_id LEFT JOIN tb_order o ON o.onlyID=og.order_id WHERE og.uid=:uid AND og.submit_date >= :beginTime and og.submit_date <= :endTime order by og.submit_date desc  ", nativeQuery = true)
    List<Object[]> getBusinessReport2(String uid, String beginTime, String endTime);

    @Query(value = "SELECT og.submit_date,gb.purchase_number,gb.purchase_price,gb.sell_price,gb.stock_number, s.name as supplier_name,g.name,gb.image,gb.explains,og.number,og.sales_volume,o.is_send,o.is_receivables FROM tb_order_good og LEFT JOIN tb_goods_batch gb ON gb.onlyID=og.goods_batch_id LEFT JOIN tb_supplier s ON s.onlyID=gb.supplier_id  left join tb_goods g ON g.onlyID=gb.goods_id LEFT JOIN tb_order o ON o.onlyID=og.order_id WHERE og.uid=:uid AND og.submit_date >= :beginTime and og.submit_date <= :endTime and g.name like CONCAT('%',:goodsName,'%') order by og.submit_date desc  ", nativeQuery = true)
    List<Object[]> getBusinessReport2WithName(String uid, String goodsName, String beginTime, String endTime);

    //收款统计
    @Query(value = "SELECT sum(realReceive) as amount,payWay FROM tb_order WHERE uid=:uid AND submit_date >= :beginTime and submit_date <= :endTime GROUP BY payWay ORDER BY amount desc", nativeQuery = true)
    List<Object[]> getBusinessReport3(String uid, String beginTime, String endTime);

}
