package com.jojubanking.boot.module.system.dal.mysql.orderSum;

import com.jojubanking.boot.framework.mybatis.core.mapper.BaseMapperX;
import com.jojubanking.boot.module.system.controller.admin.order.vo.OrderSumRespVO;
import com.jojubanking.boot.module.system.dal.dataobject.order.OrderDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface OrderSumMapper extends BaseMapperX<OrderDO> {


    @Select("<script>" +
            "SELECT itemname, batchno, school_name as schoolName, class_name as className, sex, count(*) as patCount, SUM(itemprice) as priceSum FROM xytj_order" +
            "<where>"+
            "<if test='itemname != null and itemname != \"\"'> and itemname = #{itemname}</if>" +
            "<if test='batchno != null and batchno != \"\"'> and batchno = #{batchno}</if>" +
            "<if test='schoolName != null and schoolName != \"\"'> and schoolName = #{schoolName}</if>" +
            "<if test='className != null and className != \"\"'> and className = #{className}</if>" +
            "<if test='sex != null'> and sex = #{sex}</if>" +
            "<if test='dateRangeStart != null and dateRangeStart != \"\"'> and pr.create_time &gt;= #{dateRangeStart}</if>" + // 大于>
            "<if test='dateRangeEnd != null and dateRangeEnd != \"\"'> and pr.create_time &lt;= #{dateRangeEnd}</if>" +  // 小于<
            " and paystatus = '1'" +
            " and deleted = false" +
            "</where>"+
            " GROUP BY itemname" +
            "</script>")
    List<OrderSumRespVO> getSumList(String batchno, String schoolName, String className,
                                    String itemname, Integer sex, String dateRangeStart, String dateRangeEnd);


//    @Select("select *, COALESCE(count(*), 0) as tlsum " +
//            "from users_use_count " +
//            " WHERE create_time BETWEEN #{startDate} AND #{endDate} " +
//            "GROUP BY interface_identification")
//    List<UseCountToDO> selectCountToSection(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

//    @Select("<script>" +
//            "SELECT pr.*,lr.scxh,lr.bzxh,lr.wpmc,lr.gg,lr.djp,lr.dyph,lr.bb,lr.bc,lr.jz from package_record pr LEFT JOIN line_record lr ON pr.line_id = lr.id" +
//            "<where>"+
//            "<if test='ph != null and ph != \"\"'> and pr.ph = #{ph}</if>" +
//            "<if test='scxh != null and scxh != \"\"'> and lr.scxh = #{scxh}</if>" +
//            "<if test='bb != null and bb != \"\"'> and lr.bb = #{bb}</if>" +
//            "<if test='djp != null and djp != \"\"'> and lr.djp = #{djp}</if>" +
//            "<if test='xcpd02dxbzdhStatus != null'> and pr.xcpd02dxbzdh_status = #{xcpd02dxbzdhStatus}</if>" +
//            "<if test='bzjlhStatus != null'> and pr.bzjlh_status = #{bzjlhStatus}</if>" +
//            "<if test='dateRangeStart != null and dateRangeStart != \"\"'> and pr.create_time &gt;= #{dateRangeStart}</if>" + // 大于>
//            "<if test='dateRangeEnd != null and dateRangeEnd != \"\"'> and pr.create_time &lt;= #{dateRangeEnd}</if>" +  // 小于<
//            "</where>"+
//            "limit #{offset},#{limit}" +
//            "</script>")
//    List<PackageRecordVo> getList(long offset, long limit,
//                                  String ph, String scxh, String bb, String djp, Integer xcpd02dxbzdhStatus, Integer bzjlhStatus, String dateRangeStart, String dateRangeEnd);


}
