package com.fs.store.mapper;

import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONObject;
import com.fs.store.domain.FsStorePayment;
import com.fs.store.param.FsStorePaymentParam;
import com.fs.store.param.FsStoreStatisticsParam;
import com.fs.store.vo.FsStorePaymentStatisticsVO;
import com.fs.store.vo.FsStorePaymentVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * 支付明细Mapper接口
 *
 * @author fs
 * @date 2022-06-20
 */
public interface FsStorePaymentMapper
{
    /**
     * 查询支付明细
     *
     * @param paymentId 支付明细ID
     * @return 支付明细
     */
    public FsStorePayment selectFsStorePaymentById(Long paymentId);

    /**
     * 查询支付明细列表
     *
     * @param fsStorePayment 支付明细
     * @return 支付明细集合
     */
    public List<FsStorePayment> selectFsStorePaymentList(FsStorePayment fsStorePayment);

    /**
     * 新增支付明细
     *
     * @param fsStorePayment 支付明细
     * @return 结果
     */
    public int insertFsStorePayment(FsStorePayment fsStorePayment);

    /**
     * 修改支付明细
     *
     * @param fsStorePayment 支付明细
     * @return 结果
     */
    public int updateFsStorePayment(FsStorePayment fsStorePayment);

    /**
     * 删除支付明细
     *
     * @param paymentId 支付明细ID
     * @return 结果
     */
    public int deleteFsStorePaymentById(Long paymentId);

    /**
     * 批量删除支付明细
     *
     * @param paymentIds 需要删除的数据ID
     * @return 结果
     */
    public int deleteFsStorePaymentByIds(Long[] paymentIds);

    @Select("select * from fs_store_payment where pay_code=#{payCode}")
    FsStorePayment selectFsStorePaymentByCode(String payCode);

    @Select({"<script> " +
            "select p.*,o.order_code,c.company_name,d.dept_name,u.phone as user_phone,cu.nick_name as company_user_nick_name,cu.user_name as company_user_name  from fs_store_payment p left join company_user cu on p.company_user_id=cu.user_id left join fs_user u on u.user_id=p.user_id left join company_dept d on d.dept_id=p.dept_id left join company c on c.company_id=p.company_id left join fs_store_order o on o.id=p.order_id   " +
            "where 1=1 " +
            "<if test = 'maps.payCode != null and  maps.payCode !=\"\"    '> " +
            "and p.pay_code like CONCAT('%',#{maps.payCode},'%') " +
            "</if>" +
            "<if test = 'maps.orderCode != null and  maps.orderCode !=\"\"    '> " +
            "and o.order_code like CONCAT('%',#{maps.orderCode},'%') " +
            "</if>" +
            "<if test = 'maps.tradeNo != null and  maps.tradeNo !=\"\"    '> " +
            "and p.trade_no like CONCAT('%',#{maps.tradeNo},'%') " +
            "</if>" +
            "<if test = 'maps.bankSerialNo != null and  maps.bankSerialNo !=\"\"    '> " +
            "and p.bank_serial_no like CONCAT('%',#{maps.bankSerialNo},'%') " +
            "</if>" +
            "<if test = 'maps.mobile != null and  maps.mobile !=\"\"     '> " +
            "and u.phone like CONCAT('%',#{maps.mobile},'%') " +
            "</if>" +
            "<if test = 'maps.status != null    '> " +
            "and p.status =#{maps.status} " +
            "</if>" +
            "<if test = 'maps.businessType != null    '> " +
            "and p.business_type =#{maps.businessType} " +
            "</if>" +
            "<if test = 'maps.companyId != null    '> " +
            "and p.company_id =#{maps.companyId} " +
            "</if>" +
//            "<if test = 'maps.createTime != null    '> " +
//            "and DATE_FORMAT(p.create_time,'%Y-%m-%d') = DATE_FORMAT(#{maps.createTime},'%Y-%m-%d')  " +
//            "</if>" +
            "<if test = 'maps.beginTime != null and maps.beginTime != \"\"   '> " +
            " AND date_format(p.pay_time,'%y%m%d') &gt;= date_format(#{maps.beginTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.endTime != null and maps.endTime != \"\"   '> " +
            " AND date_format(p.pay_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d') " +
            "</if>" +

            "<if test = 'maps.refundBeginTime != null  and maps.refundBeginTime != \"\"    '> " +
            "and DATE_FORMAT(p.refund_time,'%y%m%d') &gt;= date_format(#{maps.refundBeginTime},'%y%m%d')  " +
            "</if>" +
            "<if test = 'maps.refundEndTime != null and maps.refundEndTime != \"\"   '> " +
            " AND date_format(p.refund_time,'%y%m%d') &lt;= date_format(#{maps.refundEndTime},'%y%m%d') " +
            "</if>" +


            "<if test = 'maps.bankTransactionId != null and  maps.bankTransactionId !=\"\"     '> " +
            "and p.bank_transaction_id like CONCAT('%',#{maps.bankTransactionId},'%') " +
            "</if>" +

            "<if test = 'maps.companyUserNickName != null and  maps.companyUserNickName !=\"\"     '> " +
            "and cu.nick_name like CONCAT('%',#{maps.companyUserNickName},'%') " +
            "</if>" +
            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(p.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(p.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +

//            "<if test = 'maps.refundTime != null    '> " +
//            "and DATE_FORMAT(p.refund_time,'%Y-%m-%d') = DATE_FORMAT(#{maps.refundTime},'%Y-%m-%d')  " +
//            "</if>" +

            " order by p.payment_id desc "+
            "</script>"})
    List<FsStorePaymentVO> selectFsStorePaymentListQuery(@Param("maps") FsStorePaymentParam fsStorePayment);


    @Select({"<script> " +
            "select p.*,o.order_code,fsp.title, c.company_name,u.phone as user_phone,cu.nick_name as company_user_nick_name,cu.user_name as company_user_name " +
            ",(select group_concat(dd.dept_name separator  '>') from company_dept dd where p.dept_id=dd.dept_id or find_in_set(dd.dept_id,d.ancestors)) as dept_name "+
            " from fs_store_payment p left join company_user cu on p.company_user_id=cu.user_id left join fs_user u on u.user_id=p.user_id left join company_dept d on d.dept_id=p.dept_id left join company c on c.company_id=p.company_id left join fs_store_order o on o.id=p.order_id left join fs_store_product_package fsp on o.package_id = fsp.package_id " +
            "where 1=1 " +
            "<if test = 'maps.payCode != null and  maps.payCode !=\"\"    '> " +
            "and p.pay_code like CONCAT('%',#{maps.payCode},'%') " +
            "</if>" +
            "<if test = 'maps.tradeNo != null and  maps.tradeNo !=\"\"    '> " +
            "and p.trade_no like CONCAT('%',#{maps.tradeNo},'%') " +
            "</if>" +
            "<if test = 'maps.orderCode != null and  maps.orderCode !=\"\"    '> " +
            "and o.order_code like CONCAT('%',#{maps.orderCode},'%') " +
            "</if>" +
            "<if test = 'maps.mobile != null and  maps.mobile !=\"\"     '> " +
            "and u.phone like CONCAT('%',#{maps.mobile},'%') " +
            "</if>" +
            "<if test = 'maps.businessType != null    '> " +
            "and p.business_type =#{maps.businessType} " +
            "</if>" +
            "<if test = 'maps.userId != null    '> " +
            "and p.user_id =#{maps.userId} " +
            "</if>" +
            "<if test = 'maps.status != null    '> " +
            "and p.status =#{maps.status} " +
            "</if>" +
            "<if test = 'maps.companyId != null    '> " +
            "and p.company_id =#{maps.companyId} " +
            "</if>" +
            "<if test = 'maps.createTime != null    '> " +
            "and DATE_FORMAT(p.create_time,'%Y-%m-%d') = DATE_FORMAT(#{maps.createTime},'%Y-%m-%d')  " +
            "</if>" +
            "<if test = 'maps.beginTime != null and maps.beginTime != \"\"   '> " +
            " AND date_format(p.pay_time,'%y%m%d') &gt;= date_format(#{maps.beginTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.endTime != null and maps.endTime != \"\"   '> " +
            " AND date_format(p.pay_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.deptId != null    '> " +
            "  AND (p.dept_id = #{maps.deptId} OR p.dept_id IN ( SELECT t.dept_id FROM company_dept t WHERE find_in_set(#{maps.deptId}, ancestors) )) " +
            "</if>" +
            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(p.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(p.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +
            " ${maps.params.dataScope} "+
            " order by p.payment_id desc limit 30000  "+
            "</script>"})
    List<FsStorePaymentVO> exportFsStorePaymentList(@Param("maps") FsStorePaymentParam fsStorePayment);

    @Select({"<script> " +
            "select p.*,c.company_name,d.dept_name,u.phone as user_phone,cu.nick_name as company_user_nick_name,cu.user_name as company_user_name  from fs_store_payment p left join company_user cu on p.company_user_id=cu.user_id left join fs_user u on u.user_id=p.user_id left join company_dept d on d.dept_id=p.dept_id left join company c on c.company_id=p.company_id  " +
            "where 1=1 " +
            "<if test = 'maps.payCode != null and  maps.payCode !=\"\"    '> " +
            "and p.pay_code like CONCAT('%',#{maps.payCode},'%') " +
            "</if>" +
            "<if test = 'maps.mobile != null and  maps.mobile !=\"\"     '> " +
            "and u.phone like CONCAT('%',#{maps.mobile},'%') " +
            "</if>" +
            "<if test = 'maps.bankTransactionId != null and  maps.bankTransactionId !=\"\"     '> " +
            "and p.bank_transaction_id like CONCAT('%',#{maps.bankTransactionId},'%') " +
            "</if>" +
            "<if test = 'maps.bankSerialNo != null and  maps.bankSerialNo !=\"\"     '> " +
            "and p.bank_serial_no like CONCAT('%',#{maps.bankSerialNo},'%') " +
            "</if>" +
            "<if test = 'maps.businessType != null    '> " +
            "and p.business_type =#{maps.businessType} " +
            "</if>" +
            "<if test = 'maps.userId != null    '> " +
            "and p.user_id =#{maps.userId} " +
            "</if>" +
            "<if test = 'maps.status != null    '> " +
            "and p.status =#{maps.status} " +
            "</if>" +
            "<if test = 'maps.companyId != null    '> " +
            "and p.company_id =#{maps.companyId} " +
            "</if>" +
            "<if test = 'maps.companyUserNickName != null and  maps.companyUserNickName !=\"\"     '> " +
            "and cu.nick_name like CONCAT('%',#{maps.companyUserNickName},'%') " +
            "</if>" +
            "<if test = 'maps.createTime != null    '> " +
            "and DATE_FORMAT(p.create_time,'%Y-%m-%d') = DATE_FORMAT(#{maps.createTime},'%Y-%m-%d')  " +
            "</if>" +

            "<if test = 'maps.beginTime != null and maps.beginTime != \"\"   '> " +
            " AND date_format(p.pay_time,'%y%m%d') &gt;= date_format(#{maps.beginTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.endTime != null and maps.endTime != \"\"   '> " +
            " AND date_format(p.pay_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d') " +
            "</if>" +

            "<if test = 'maps.refundBeginTime != null  and maps.refundBeginTime != \"\"    '> " +
            "and DATE_FORMAT(p.refund_time,'%y%m%d') &gt;= date_format(#{maps.refundBeginTime},'%y%m%d')  " +
            "</if>" +
            "<if test = 'maps.refundEndTime != null and maps.refundEndTime != \"\"   '> " +
            " AND date_format(p.refund_time,'%y%m%d') &lt;= date_format(#{maps.refundEndTime},'%y%m%d') " +
            "</if>" +

            "<if test = 'maps.deptId != null    '> " +
            "  AND (p.dept_id = #{maps.deptId} OR p.dept_id IN ( SELECT t.dept_id FROM company_dept t WHERE find_in_set(#{maps.deptId}, ancestors) )) " +
            "</if>" +
             " ${maps.params.dataScope} "+
            " order by p.payment_id desc "+
            "</script>"})
    List<FsStorePaymentVO> selectFsStorePaymentListQueryVO(@Param("maps") FsStorePaymentParam fsStorePayment);

    @Select({"<script> " +
            "select p.*,c.company_name,d.dept_name,u.phone as user_phone,cu.nick_name as company_user_nick_name,cu.user_name as company_user_name  from fs_store_payment p left join company_user cu on p.company_user_id=cu.user_id left join fs_user u on u.user_id=p.user_id left join company_dept d on d.dept_id=p.dept_id left join company c on c.company_id=p.company_id  " +
            "where order_id  is null  " +
            "<if test = 'maps.payCode != null and  maps.payCode !=\"\"    '> " +
            "and p.pay_code like CONCAT('%',#{maps.payCode},'%') " +
            "</if>" +
            "<if test = 'maps.mobile != null and  maps.mobile !=\"\"     '> " +
            "and u.phone like CONCAT('%',#{maps.mobile},'%') " +
            "</if>" +
            "<if test = 'maps.businessType != null    '> " +
            "and p.business_type =#{maps.businessType} " +
            "</if>" +
            "<if test = 'maps.status != null    '> " +
            "and p.status =#{maps.status} " +
            "</if>" +
            "<if test = 'maps.companyUserId != null    '> " +
            "and p.company_user_id =#{maps.companyUserId} " +
            "</if>" +
            "<if test = 'maps.companyId != null    '> " +
            "and p.company_id =#{maps.companyId} " +
            "</if>" +
            "<if test = 'maps.createTime != null    '> " +
            "and DATE_FORMAT(p.create_time,'%Y-%m-%d') = DATE_FORMAT(#{maps.createTime},'%Y-%m-%d')  " +
            "</if>" +
            " order by p.payment_id desc "+
            "</script>"})
    List<FsStorePaymentVO> selectFsMyStorePaymentListQueryVO(@Param("maps") FsStorePaymentParam fsStorePayment);

    @Select("select * from fs_store_payment where business_type=2 and order_id=#{orderId} and status=1   ")
    List<FsStorePayment> selectFsStorePaymentByOrderId(Long orderId);

    @Select({"<script> " +
            "select count(1) from fs_store_payment  " +
            "where business_type=1 and status=1 " +
            "<if test = 'type != null and  type ==1  '> " +
            "and  DATE_FORMAT(create_time, '%Y-%m-%d')  = DATE_FORMAT(NOW(), '%Y-%m-%d') " +
            "</if>" +
            "<if test = 'companyId != null  '> " +
            "and  company_id=#{companyId} " +
            "</if>" +
            "</script>"})
    Long selectFsStorePaymentCount(@Param("type") int type,@Param("companyId") Long companyId);

    List<JSONObject> selectFsStorePaymentCounts(Map<String, Object> toMap);

    @Select({"<script> " +
            "select u.nick_name, count(p.payment_id) as order_count,sum(IFNULL(p.pay_money,0)) as pay_money  from  fs_store_payment p left join company_user u on p.company_user_id=u.user_id  " +
            "where p.status = 1 " +
            "<if test = 'maps.type != null and maps.type ==1 '> " +
            "and TO_DAYS(p.create_time) = TO_DAYS(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==2 '> " +
            "and TO_DAYS(NOW()) - TO_DAYS(p.create_time) &lt;= 1 " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==3 '> " +
            "and YEARWEEK(DATE_FORMAT(p.create_time,'%Y-%m-%d')) = YEARWEEK(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==4 '> " +
            "and YEARWEEK(DATE_FORMAT(p.create_time,'%Y-%m-%d')) = YEARWEEK(NOW())-1 " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==5 '> " +
            "and DATE_FORMAT(p.create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==6 '> " +
            "and PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(p.create_time,'%Y%m')) = 1 " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==7 '> " +
            "and QUARTER(p.create_time) = QUARTER(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==8 '> " +
            "and QUARTER(p.create_time) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==9 '> " +
            "and YEAR(p.create_time)=YEAR(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==10 '> " +
            "and  YEAR(p.create_time) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))" +
            "</if>" +
            "<if test = 'maps.startTime != null and maps.startTime != \"\" '> " +
            "AND date_format(p.create_time,'%y%m%d') &gt;= date_format(#{maps.startTime},'%y%m%d')"+
            "</if>" +
            "<if test = 'maps.endTime != null and maps.endTime != \"\" '> " +
            "AND date_format(p.create_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d')"+
            "</if>" +
            "and  p.`company_user_id` IN " +
            "<foreach  item='item' index='index' collection='maps.users' open='(' separator=',' close=')'> #{item}    </foreach>"+
            " group by p.company_user_id "+
            "</script>"})
    List<FsStorePaymentStatisticsVO> selectFsStorePaymentStatisticsList(@Param("maps") FsStoreStatisticsParam param);


    @Select("select * from fs_store_payment where business_type=2 and order_id=#{orderId} limit 1   ")
    FsStorePayment selectFsStorePaymentByStoreOrderId(Long orderId);

    @Select("select trade_no from fs_store_payment where date(create_time) = '2023-09-21' and status = 1 ")
    List<String> selectDay();

    @Update("update fs_store_payment set bank_transaction_id = #{bankTransactionId},bank_serial_no = #{bankSerialNo} where trade_no =#{tradeNo} ")
    int updateBankId(FsStorePayment fsStorePayment);

    @Select("select payment_id from fs_store_payment where payment_id in(396696, 368921, 404456, 396332, 370749, 311165, 359523, 388503, 370293, 359431, 373766, 368781, 393965, 396849, 332723, 437913, 397537, 440316, 442001 ) ")
    List<Long> selectPaymentIds();
    @Select("select trade_no from fs_store_payment where `status`=0 and date(create_time)=CURDATE()  AND create_time < NOW() - INTERVAL 5 MINUTE AND create_time > NOW() - INTERVAL 60 MINUTE ;")
    List<String> selectPayStatusIds();

    @Select("select * from fs_store_payment where business_type=2 and order_id=#{orderId}    ")
    List<FsStorePayment> selectFsStorePaymentByOrder(Long orderId);

    @Select("select * from fs_store_payment where trade_no=#{tradeNo}")
    FsStorePayment selectFsStorePaymentByTradeNo(String tradeNo);
}
