foodie-server/ruoyi-system/src/main/java/com/ruoyi/system/mapper/UserBillingMapper.java

package com.ruoyi.system.mapper;

import java.util.List;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ruoyi.system.domain.UserBilling;
import com.ruoyi.system.domain.vo.BillDTO;
import com.ruoyi.system.domain.vo.ShBillingDto;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * BillingMapper接口
 *
 * @author ruoyi
 * @date 2023-09-06
 */
public interface UserBillingMapper  extends BaseMapper<UserBilling>
{

    //用户余额查询
    @Select("SELECT IFNULL(SUM(amount),0) FROM user_billing WHERE state=0 and type<>2 and user_id = #{userid}")  //查询语句
    List<UserBilling> getamsum(@Param("userid") Long userid);  //反回结果

    @Select("SELECT IFNULL(SUM(amount),0) FROM user_billing WHERE state=0 and type=#{type} and user_id=#{userid}")  //查询语句
    int gettypesum(@Param("userid") Long userid,@Param("type") Long type);  //反回结果

    @Select("SELECT * FROM user_billing WHERE type=1 and state=3 and TIMESTAMPDIFF(MINUTE,cretim,NOW()) >= #{minute}")  //查询语句
    List<UserBilling> getbillingTim(@Param("minute") Integer minute);  //反回结果

    //统计商户全部收入
    @Select("SELECT IFNULL(SUM(amount),0) FROM user_billing WHERE user_id=#{shid} and type=0 and state=0")  //查询语句
    int getshAllIncomeSum(@Param("shid") Long shid);  //反回结果

    //统计商户当天收入
    @Select("SELECT IFNULL(SUM(amount),0) FROM user_billing WHERE user_id=#{shid} and type=0 and state=0 and DATE_FORMAT(cretim, '%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')")  //查询语句
    int getshDayIncomeSum(@Param("shid") Long shid);  //反回结果

    //统计商户已提现
    @Select("SELECT IFNULL(SUM(amount),0) FROM user_billing WHERE user_id=#{shid} and type=2 and state=0")  //查询语句
    int getshtxsum(@Param("shid") Long shid);

    //统计商户全部收入(qb)
    @Select("SELECT IFNULL(SUM(amount),0) FROM user_billing WHERE user_id=#{shid} and type=0 and state=0")
    int getshqbsum(@Param("shid") Long shid);

    //统计商户当天收入(dt)
    @Select("SELECT IFNULL(SUM(amount),0) FROM user_billing WHERE user_id=#{shid} and type=0 and state=0 and DATE_FORMAT(cretim, '%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')")
    int getshdtsum(@Param("shid") Long shid);  //反回结果

    //商家月账单统计
    @Select("SELECT IFNULL(COUNT(1),0) AS sul,IFNULL(SUM(amount),0) AS shouru ,IFNULL(SUM(divvy),0) AS commission,IFNULL(SUM(tax),0) AS tax,IFNULL(SUM(behalf_amount),0) AS behalfAmount FROM user_billing WHERE type=0 and user_id=#{shid} and DATE_FORMAT(cretim, '%Y-%m') = #{riqi} and state=0")  //查询语句
    BillDTO getmatsum(@Param("shid") Long shid, @Param("riqi") String riqi);  //反回结果
    //商户查询周月账单商品总金额，商家优惠总金额
    @Select("SELECT IFNULL(SUM(o.food_amount),0) AS foodAmount,IFNULL(SUM(o.md_discount_amount),0)+IFNULL(SUM(o.md_sales_reduction),0) as shYh  FROM user_billing b left join pos_order o on b.dd_id=o.dd_id WHERE b.type=0 and b.user_id=#{shid} and DATE_FORMAT(b.cretim, '%Y-%m') = #{riqi} and b.state=0")  //查询语句
    ShBillingDto getshMonthsum(@Param("shid") Long shid, @Param("riqi") String riqi);
    //商家月账单明细
    @Select("SELECT * FROM user_billing WHERE type=0 and user_id=#{shid} and DATE_FORMAT(cretim, '%Y-%m') = #{riqi}  ORDER BY id desc limit #{page},#{size}")  //查询语句
    List<UserBilling> getmatlist(@Param("shid") Long shid, @Param("riqi") String riqi,@Param("page") Long page,@Param("size") Long size);  //反回结果

    //商家周账单统计
    @Select("SELECT IFNULL(COUNT(1),0) AS sul,IFNULL(SUM(amount),0) AS shouru ,IFNULL(SUM(divvy),0) AS commission,IFNULL(SUM(tax),0) AS tax,IFNULL(SUM(behalf_amount),0) AS behalfAmount FROM user_billing WHERE type=0 and user_id=#{shid} and  WEEK(cretim) = WEEK(#{riqi}) and  YEAR(cretim) = YEAR(#{riqi}) and state=0")  //查询语句
    BillDTO getmeeksum(@Param("shid") Long shid, @Param("riqi") String riqi);  //反回结果
    //商户查询周账单商品总金额，商家优惠总金额
    @Select("SELECT IFNULL(SUM(o.food_amount),0) AS foodAmount,IFNULL(SUM(o.md_discount_amount),0)+IFNULL(SUM(o.md_sales_reduction),0) as shYh  FROM user_billing b left join pos_order o on b.dd_id=o.dd_id WHERE b.type=0 and b.user_id=#{shid} and  WEEK(b.cretim) = WEEK(#{riqi}) and  YEAR(b.cretim) = YEAR(#{riqi}) and b.state=0")  //查询语句
    ShBillingDto getShMeeksum(@Param("shid") Long shid, @Param("riqi") String riqi);
    //商家周账单明细
    @Select("SELECT * FROM user_billing WHERE type=0 and user_id=#{shid} and WEEK(cretim) = WEEK(#{riqi}) and  YEAR(cretim) = YEAR(#{riqi}) ORDER BY id desc limit #{page},#{size}")  //查询语句
    List<UserBilling> getmeeklist(@Param("shid") Long shid, @Param("riqi") String riqi,@Param("page") Long page,@Param("size") Long size);  //反回结果
    //商家日账单统计
    @Select("SELECT IFNULL(COUNT(1),0) AS sul,IFNULL(SUM(amount),0) AS shouru,IFNULL(SUM(divvy),0) AS commission,IFNULL(SUM(tax),0) AS tax,IFNULL(SUM(behalf_amount),0) AS behalfAmount FROM user_billing WHERE type=0 and user_id=#{shid} and date_format (cretim,'%Y-%m-%d %H:%i:%s') >= date_format (CONCAT(#{riqi}, ' 00:00:00'),'%Y-%m-%d %H:%i:%s') and date_format (cretim,'%Y-%m-%d %H:%i:%s') <= date_format (CONCAT(#{riqi}, ' 23:59:59'),'%Y-%m-%d %H:%i:%s') and state=0")  //查询语句
    BillDTO getdaysum(@Param("shid") Long shid, @Param("riqi") String riqi);  //反回结果

    //商户查询日账单商品总金额，商家优惠总金额
    @Select("SELECT IFNULL(SUM(o.food_amount),0) AS foodAmount,IFNULL(SUM(o.md_discount_amount),0)+IFNULL(SUM(o.md_sales_reduction),0) as shYh  FROM user_billing b left join pos_order o on b.dd_id=o.dd_id WHERE b.type=0 and b.user_id=#{shid} and date_format (b.cretim,'%Y-%m-%d %H:%i:%s') >= date_format (CONCAT(#{riqi}, ' 00:00:00'),'%Y-%m-%d %H:%i:%s') and date_format (b.cretim,'%Y-%m-%d %H:%i:%s') <= date_format (CONCAT(#{riqi}, ' 23:59:59'),'%Y-%m-%d %H:%i:%s')  ")
    ShBillingDto getShDaysum(@Param("shid") Long shid, @Param("riqi") String riqi);
    //商家日账单明细
    @Select("SELECT * FROM user_billing WHERE type=0 and user_id=#{shid} and date_format (cretim,'%Y-%m-%d %H:%i:%s') >= date_format (CONCAT(#{riqi}, ' 00:00:00'),'%Y-%m-%d %H:%i:%s') and date_format (cretim,'%Y-%m-%d %H:%i:%s') <= date_format (CONCAT(#{riqi}, ' 23:59:59'),'%Y-%m-%d %H:%i:%s') ORDER BY id desc limit #{page},#{size}")  //查询语句
    List<UserBilling> getdaylist(@Param("shid") Long shid, @Param("riqi") String riqi,@Param("page") Long page,@Param("size") Long size);  //反回结果
    /**
     * 查询Billing
     *
     * @param id Billing主键
     * @return Billing
     */
    public UserBilling selectUserBillingById(Long id);

    /**
     * 查询Billing列表,不包含提现记录
     *
     * @param userBilling Billing
     * @return Billing集合
     */
    public List<UserBilling> selectUserBillingList(UserBilling userBilling);

    /**
     * 查询提现列表
     *
     * @param userBilling Billing
     * @return Billing集合
     */
    public List<UserBilling> selectUserWithdrawList(UserBilling userBilling);

    /**
     * 新增Billing
     *
     * @param userBilling Billing
     * @return 结果
     */
    public int insertUserBilling(UserBilling userBilling);

    /**
     * 修改Billing
     *
     * @param userBilling Billing
     * @return 结果
     */
    public int updateUserBilling(UserBilling userBilling);

    /**
     * 删除Billing
     *
     * @param id Billing主键
     * @return 结果
     */
    public int deleteUserBillingById(Long id);

    /**
     * 批量删除Billing
     *
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteUserBillingByIds(Long[] ids);

    /**
     * 获取用户收入
     * @param userid
     * @return
     */
    @Select("SELECT IFNULL(SUM(amount),0) FROM user_billing WHERE user_id=#{userid} and type=0 and state=0")  //查询语句
    public double getUserIncomeSum(Long userid);

    /**
     * 获取用户提现金额(包括未处理完成)
     * @param userid
     * @return
     */
    @Select("SELECT IFNULL(SUM(amount),0) FROM user_billing WHERE user_id= #{userid} and type=2 and state>=0")
    public double getUserWithdrawSum(Long userid);
}