package com.ruoyi.system.mapper;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ruoyi.system.domain.PosOrder;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * posorderMapper接口
 *
 * @author ruoyi
 * @date 2023-05-23
 */
public interface PosOrderMapper  extends BaseMapper<PosOrder>
{
    //按照距离查询推荐商家
    @Select("SELECT *,(st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000 ) as juli FROM pos_order WHERE state like #{state} and type=0 HAVING juli<#{juli} ORDER BY juli ASC limit #{page},20")  //查询语句
    List<PosOrder> getstate(@Param("longitude") BigDecimal longitude, @Param("latitude")BigDecimal latitude, @Param("page")Integer page, @Param("juli")Integer juli, @Param("state")String state);  //反回结果

    @Select("SELECT *,(st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000 ) as juli FROM pos_order and type=0  HAVING juli<#{juli} ORDER BY juli ASC limit #{page},20")  //查询语句
    List<PosOrder> getqisjvli(@Param("longitude") BigDecimal longitude, @Param("latitude")BigDecimal latitude, @Param("page")Integer page, @Param("juli")Integer juli);  //反回结果

    @Select("SELECT *,(st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000 ) as juli FROM pos_order WHERE state>=#{zx} and state<=#{zd} and type=0  HAVING juli<#{juli} ORDER BY juli ASC limit #{page},20")  //查询语句
    List<PosOrder> getz234(@Param("longitude") BigDecimal longitude, @Param("latitude")BigDecimal latitude, @Param("page")Integer page, @Param("juli")Integer juli, @Param("zx")Integer zx, @Param("zd")Integer zd);  //反回结果

//    @Select("SELECT *,(st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000 ) as juli FROM pos_order WHERE (state=0 and collect_payment=1 and type=0) or (state=1 and collect_payment=0 and type=0)  HAVING juli<#{juli} ORDER BY juli ASC limit #{page},20")
    @Select("SELECT *,(st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000 ) as juli FROM pos_order WHERE (state=0 and collect_payment=1 and type=0) or (state=1 and collect_payment=0 and type=0)  ORDER BY juli ASC limit #{page},20")  //查询语句
    List<PosOrder> getz02(@Param("longitude") BigDecimal longitude, @Param("latitude")BigDecimal latitude, @Param("page")Integer page, @Param("juli")Integer juli);  //反回结果
    //统计订单数量
    @Select("SELECT IFNULL(COUNT(*),0) as amount FROM pos_order")  //查询语句
    List<PosOrder> getOrderCount();

    int getOrderCountByDate(@Param("startDate") String startDate,@Param("endDate") String endDate);
    //统计订单金额
    @Select("SELECT IFNULL(SUM(amount),0) as amount FROM pos_order")  //查询语句
    List<PosOrder> getOrderSum();

    int getOrderSumByDate(@Param("startDate") String startDate,@Param("endDate") String endDate);//反回结果

    //统计订单数量
    @Select("SELECT IFNULL(COUNT(*),0) as state FROM pos_order WHERE DATE_FORMAT(cretim,'%Y-%m') = DATE_FORMAT(#{month},'%Y-%m')")  //查询语句
    Integer getMoorcoun(@Param("month") String month);  //反回结果

    //统计订单金额
    @Select("SELECT IFNULL(SUM(amount),0) as amount FROM pos_order WHERE DATE_FORMAT(cretim,'%Y-%m') = DATE_FORMAT(#{month},'%Y-%m')")  //查询语句
    Double getMoorSun(@Param("month") String month);  //反回结果

    //查询超过一定时间的订单
    @Select("SELECT * FROM pos_order WHERE state=#{state} and TIMESTAMPDIFF(MINUTE,cretim,NOW()) >= #{minute}")  //查询语句
    List<PosOrder> getOrderTim(@Param("state") Integer state,@Param("minute") Integer minute);  //反回结果

    //查询超过规定时间未点完成的订单
    @Select("SELECT * FROM pos_order WHERE state=#{state} and ((TIMESTAMPDIFF(MINUTE,cretim,NOW()) >= #{minute} and sd_time is null) or (sd_time is not null and TIMESTAMPDIFF(MINUTE,sd_time,NOW()) >= #{minute}))")  //查询语句
    List<PosOrder> getOverOrderTim(@Param("state") Integer state,@Param("minute") Integer minute);

    //查询骑手订单（带距离计算）
    @Select("<script>" +
            "SELECT *,(st_distance(point(longitude,latitude),point(#{longitude},#{latitude}))*111195/1000) as juli " +
            "FROM pos_order WHERE qs_id=#{qsId} AND is_display=1 " +
            "<if test='state != null and state != \"\"'>" +
            "<choose>" +
            "<when test='state == \"z234\"'>AND state IN (2,3,4)</when>" +
            "<when test='state == \"z23\"'>AND state IN (2,3)</when>" +
            "<when test='state == \"z34\"'>AND state IN (3,4)</when>" +
            "<when test='state == \"z6789\"'>AND state IN (6,7,8,9)</when>" +
            "<when test='state == \"z678911\"'>AND state IN (6,7,8,9,11)</when>" +
            "<otherwise>AND state=#{state}</otherwise>" +
            "</choose>" +
            "</if>" +
            "ORDER BY cretim DESC LIMIT #{page},#{size}" +
            "</script>")
    List<PosOrder> getQsOrderList(@Param("qsId") String qsId, @Param("longitude") BigDecimal longitude, @Param("latitude") BigDecimal latitude, @Param("page") Integer page, @Param("size") Integer size, @Param("state") String state);

    //统计商户当天订单量
    @Select("SELECT IFNULL(COUNT(*),0) FROM pos_order WHERE sh_id=#{shid} AND DATE_FORMAT(cretim, '%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')")  //查询语句
    int getshoredr(@Param("shid") Long shid);  //反回结果
    //统计商户全部订单量
    @Select("SELECT IFNULL(COUNT(*),0) FROM pos_order WHERE sh_id=#{shid}")  //查询语句
    int getshqbuord(@Param("shid") Long shid);  //反回结果

    //统计用户已参于活动次数
    @Select("SELECT IFNULL(COUNT(*),0)  FROM pos_order WHERE user_id = #{userId} AND activity = #{salid}")  //查询语句
    int getActivity(@Param("userId") Long userId,@Param("salid") Long salid);  //反回结果

    /**
     * 统计热销商品下单次数
     * 使用JSON函数解析food字段中的商品ID并统计出现次数
     */
    @Select("SELECT " +
            "JSON_UNQUOTE(JSON_EXTRACT(item, '$.id')) as food_id, " +
            "COUNT(*) as order_count " +
            "FROM pos_order po, " +
            "JSON_TABLE(po.food, '$[*]' COLUMNS (item JSON PATH '$')) as food_items " +
            "WHERE po.food IS NOT NULL AND po.food != '' " +
            "GROUP BY JSON_UNQUOTE(JSON_EXTRACT(item, '$.id')) " +
            "ORDER BY order_count DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getHotFoodOrderCount(@Param("limit") int limit);

    /**
     * 统计热销商品下单次数（兼容版本）
     * 使用简单的JSON_EXTRACT函数统计
     */
    @Select("SELECT " +
            "SUBSTRING_INDEX(SUBSTRING_INDEX(po.food, '\"id\":', -1), ',', 1) as food_id, " +
            "COUNT(*) as order_count " +
            "FROM pos_order po " +
            "WHERE po.food IS NOT NULL AND po.food != '' " +
            "AND po.food LIKE '%\"id\":%' " +
            "GROUP BY SUBSTRING_INDEX(SUBSTRING_INDEX(po.food, '\"id\":', -1), ',', 1) " +
            "ORDER BY order_count DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getHotFoodOrderCountSimple(@Param("limit") int limit);


    /**
     * 查询posorder
     *
     * @param id posorder主键
     * @return posorder
     */
    public PosOrder selectPosOrderById(Long id);

    /**
     * 查询posorder列表
     *
     * @param posOrder posorder
     * @return posorder集合
     */
    public List<PosOrder> selectPosOrderList(PosOrder posOrder);

    /**
     * 查询posorder列表 sh
     *
     * @param posOrder posorder
     * @return posorder集合
     */
    public List<PosOrder> selectPosOrderShList(PosOrder posOrder);

    /**
     * 新增posorder
     *
     * @param posOrder posorder
     * @return 结果
     */
    public int insertPosOrder(PosOrder posOrder);

    /**
     * 修改posorder
     *
     * @param posOrder posorder
     * @return 结果
     */
    public int updatePosOrder(PosOrder posOrder);

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

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

    /**
     * 当年的总订单数
     * @param nowYearFirstDay 今年1月1日
     * @return 结果
     */
    public int countOrderByNowYear(Date nowYearFirstDay);

    /**
     * 当年的成功订单数
     * @param nowYearFirstDay 今年1月1日
     * @return 结果
     */
    public int countOrderSuccessByNowYear(Date nowYearFirstDay);

    /**
     * 当年的失败订单数
     * @param nowYearFirstDay 今年1月1日
     * @return 结果
     */
    public int countOrderFailByNowYear(Date nowYearFirstDay);

    /**
     * 今年总成交额
     * @param nowYearFirstDay 今年1月1日
     * @return 结果
     */
    public Double totalTurnoverByNowYear(Date nowYearFirstDay);

    int markLinePaid(@Param("ddId") String ddId, @Param("paymentId") Long paymentId);

    int markLineRefunded(@Param("ddId") String ddId, @Param("paymentId") Long paymentId);
}
