package cn.iocoder.yudao.module.order.dal.mysql.orderException;

import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.mybatis.core.mapper.AbstractMapper;
import cn.iocoder.yudao.framework.mybatis.core.query.LambdaQuery;
import cn.iocoder.yudao.framework.mybatis.core.vo.PageVO;
import cn.iocoder.yudao.module.order.dal.dataobject.orderException.OrderExceptionDO;
import cn.iocoder.yudao.module.order.vo.order.StatisticsOrderVO;
import cn.iocoder.yudao.module.order.vo.orderException.OrderBackVO;
import cn.iocoder.yudao.module.order.vo.orderException.OrderExceptionBackVO;
import cn.iocoder.yudao.module.order.vo.orderException.OrderExceptionExcelVO;
import cn.iocoder.yudao.module.order.vo.orderException.OrderExceptionQueryVO;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 订单异常 Mapper
 *
 * @author 系统管理员
 */
@Mapper
public interface OrderExceptionMapper extends AbstractMapper<OrderExceptionDO> {
    @Override
    default PageResult<OrderExceptionDO> selectPage(PageVO page, Object object) {
        if (object instanceof OrderExceptionQueryVO) {
            OrderExceptionQueryVO vo = (OrderExceptionQueryVO) object;
            return selectPage(page, new LambdaQuery<OrderExceptionDO>()
                    .eqIfPresent(OrderExceptionDO::getOrderId, vo.getOrderId())
                    // .eqIfPresent(OrderExceptionDO::getOrderNumber, vo.getOrderNumber())
                    .eqIfPresent(OrderExceptionDO::getOrderExceptionType, vo.getOrderExceptionType())
                    //.eqIfPresent(OrderExceptionDO::getOrderExceptionRemark, vo.getOrderExceptionRemark())
                    //.eqIfPresent(OrderExceptionDO::getOrderExceptionAmount, vo.getOrderExceptionAmount())
                    //.eqIfPresent(OrderExceptionDO::getOrderExceptionAttr, vo.getOrderExceptionAttr())
                    .eqIfPresent(OrderExceptionDO::getOrderExceptionStatus, vo.getOrderExceptionStatusList())
                    .betweenIfPresent(OrderExceptionDO::getCreateTime, vo.getBeginCreateTime(), vo.getEndCreateTime())
                    .orderByDesc(OrderExceptionDO::getId));
        }

        return null;
    }

    @Override
    default List<OrderExceptionDO> selectList(Object object) {
        if (object instanceof OrderExceptionQueryVO) {
            OrderExceptionQueryVO vo = (OrderExceptionQueryVO) object;
            return selectList(new LambdaQuery<OrderExceptionDO>()
                    .eqIfPresent(OrderExceptionDO::getOrderId, vo.getOrderId())
                    //.eqIfPresent(OrderExceptionDO::getOrderNumber, vo.getOrderNumber())
                    .eqIfPresent(OrderExceptionDO::getOrderExceptionType, vo.getOrderExceptionType())
                    .eqIfPresent(OrderExceptionDO::getOrderExceptionStatus, vo.getOrderExceptionStatusList())
                    .betweenIfPresent(OrderExceptionDO::getCreateTime, vo.getBeginCreateTime(), vo.getEndCreateTime())
                    .orderByDesc(OrderExceptionDO::getId));
        }

        return null;
    }

    IPage<OrderBackVO> selectOrderPage(IPage<OrderBackVO> page, @Param(Constants.WRAPPER) Wrapper<OrderExceptionDO> queryWrapper, @Param("exceptionList") List<String> exceptionList, @Param("startDate") String startDate, @Param("endDate") String endDate);


    List<OrderExceptionBackVO> selectOrderExceptionListByOrderId(@Param(Constants.WRAPPER) Wrapper queryWrapper);

    List<OrderExceptionExcelVO> getOrderExceptionExcelList(@Param(Constants.WRAPPER) Wrapper<OrderExceptionDO> queryWrapper, @Param("exceptionList") List<String> exceptionList, @Param("startDate") String startDate, @Param("endDate") String endDate);

    List<OrderExceptionBackVO> selectOrderExceptionListByProductId(@Param(Constants.WRAPPER) Wrapper queryWrapper);


    @Select("select count(1) from ecw_order_exception t inner join ecw_order_item item  on t.order_item_id = item.order_item_id " +
            "where t.deleted = 0 and item.deleted = 0 and t.order_id= #{orderId} and item.prod_id = #{orderItemId} " +
            "and t.order_exception_type = 'goods_add_exception' and t.order_exception_status in (0, 1) ")
    Long getUnprocessGoodsAddExceptionCounts(@Param("orderId") Long orderId, @Param("orderItemId") Long orderItemId);

    StatisticsOrderVO statisticsExceptionOrder(@Param(Constants.WRAPPER) Wrapper queryWrapper, @Param("exceptionList") List<String> exceptionList, @Param("startDate") String startDate, @Param("endDate") String endDate);


    /*@ResultType(OrderBackPageVO.class)
    @Select({
            "<script>",
            "select o.order_id, ",
            "o.package_type as advance_type, ",
            "o.package_remarks, ",
            "o.number, ",
            "o.order_no, ",
            "o.tidan_no, ",
            "o.type, ",
            "o.order_type, ",
//            "IF(o.order_type != 2 and (SELECT COUNT(1) FROM ecw_order_exception hoe WHERE hoe.order_id = o.order_id AND hoe.deleted = 0 AND hoe.order_exception_status = 0 AND hoe.order_exception_type ='order_heavy_cargo_exception') = 0, o.sum_volume, o.w_volume ) as w_volume, ",
            "IFNULL((select SUM(IF(it.charge_volume > it.min_metering_volume, it.charge_volume, it.min_metering_volume)) FROM ecw_order_item it WHERE it.order_id = o.order_id and it.deleted = 0 and it.warehouse_in_info IS NOT NULL), 0) as w_volume, ",
//            "IF(o.order_type != 3 and (SELECT COUNT(1) FROM ecw_order_exception boe WHERE boe.order_id = o.order_id AND boe.deleted = 0 AND boe.order_exception_status = 0 AND boe.order_exception_type ='order_bulky_cargo_exception') = 0, o.sum_weight, o.v_weight ) as v_weight, ",
            "IFNULL((select SUM(IF( it.charge_weight > it.min_metering_weight, it.charge_weight, it.min_metering_weight)) FROM ecw_order_item it WHERE it.order_id = o.order_id and it.deleted = 0 and it.warehouse_in_info IS NOT NULL), 0) as v_weight, ",
            "o.customs_type, ",
            "if(#{query.customerDetailId} is not null and #{query.customerDetailId} > 0, (select min(owi.`in_time`) from ecw_order_warehouse_in owi where owi.deleted = 0 and owi.order_id = o.order_id ), o.rucang_time ) as rucang_time,",
            "o.cost, ",
            "o.parent_number, ",
            "o.parent_order_id, ",
            "if(#{query.lang} = 0, de.departure ->> '$.titleZh', de.departure ->> '$.titleEn') as departure_name, ",
            "if(#{query.lang} = 0, ob.objective ->> '$.titleZh', ob.objective ->> '$.titleEn') as objective_name, ",
            "de.departure_id, ",
            "ob.objective_id, ",
            "o.adjust_to_start_warehouse_id, ",
            "(SELECT IF(0 = 0, ew_start_adjust.title_zh, ew_start_adjust.title_en) FROM ecw_warehouse ew_start_adjust WHERE ew_start_adjust.id = o.adjust_to_start_warehouse_id) AS adjust_to_start_warehouse_name,",
            "o.adjust_to_dest_warehouse_id, ",
            "(SELECT IF(0 = 0, ew_dest_adjust.title_zh, ew_dest_adjust.title_en) FROM ecw_warehouse ew_dest_adjust WHERE ew_dest_adjust.id = o.adjust_to_dest_warehouse_id) AS adjust_to_dest_warehouse_name,",
            "w.start_warehouse_name,",
            "w.dst_warehouse_name,",
            "w.start_warehouse_id,",
            "w.dst_warehouse_id,",
            "o.marks, ",
            "o.salesman_id, ",
            "o.status, ",
            "o.abnormal_state, ",
            "o.in_warehouse_state, ",
            "o.shipment_state, ",
            "o.pick_state, ",
            "o.audit_type, ",
            "o.audit_result, ",
            "o.guan_lian_order_status, ",
            "o.transport_id, ",
            "(select ifnull(sum(ccp.pick_num),0) from ecw_order_cargo_control_pick ccp where ccp.order_id = o.order_id and ccp.status in(1,2,3,4) ) as release_num, ",
            "o.is_cargo_control, ",
            "o.cargo_control_status, ",
            "o.cost ->> '$.totalNum' as total_num, ",
            "o.sum_num, ",
            "o.sum_volume, ",
            "o.sum_weight, ",
            "o.packing_list_url, ",
            "o.is_external_warehouse, ",
            "o.exception_reason, ",
            "o.is_exception, ",
            "o.create_time, ",
            "IF(o.user_id is not null AND o.user_id > 0 AND o.`status` = 0, 1 , 0)  as st, ",
            "if(#{query.userType} = 1, 1, 2) as user_type, ",
            "nor.name as consignor_name, ",
            "nor.name_en as consignor_name_en, ",
            "nor.phone as consignor_phone, ",
            "nor.country_code as consignor_country_code, ",
            "nee.name as consignee_name, ",
            "nee.name_en as consignee_name_en, ",
            "nee.phone as consignee_phone, ",
            "nee.country_code as consignee_country_code, ",
            "if(#{query.lang} = 0, channel.name_zh, channel.name_en) as channel_name, ",
            "o.update_time ",
            "from ecw_order o ",
            "left join (",
            "SELECT",
            "ewl.id AS line_id,",
            "ew_start.id AS start_warehouse_id,",
            "ew_dest.id AS dst_warehouse_id,",
            "ew_start.title_zh AS start_title_zh,",
            "IF( #{query.lang} = 0, ew_dest.title_zh, ew_dest.title_en ) AS dst_warehouse_name,",
            "IF( #{query.lang} = 0, ew_start.title_zh, ew_start.title_en ) AS start_warehouse_name",
            "FROM",
            "ecw_warehouse_line ewl",
            "LEFT JOIN ecw_warehouse ew_start ON ewl.start_warehouse_id = ew_start.id",
            "LEFT JOIN ecw_warehouse ew_dest ON ewl.dest_warehouse_id = ew_dest.id",
            ") w ON w.line_id = o.line_id",
            "left join ecw_order_departure de on de.order_id = o.order_id ",
            "left join ecw_order_objective ob on ob.order_id = o.order_id ",
            "left join ecw_order_consignor nor on nor.order_id = o.order_id ",
            "left join ecw_order_consignee nee on nee.order_id = o.order_id ",
            "left join ecw_channel channel on channel.channel_id = o.channel_id  ",
            "where o.deleted = 0 and o.in_warehouse_state != 211 and o.in_warehouse_state != 208 and o.abnormal_state!=0 ",
            "<when test = 'query.deptId != null '>",
            "AND o.dept_id = #{query.deptId} ",
            "</when>",
            "<when test = 'query.type != null and query.type != \"\" and query.type == 0 '>",
            "AND (o.type is null or o.type = '' or o.type = '0')",
            "</when>",
            "<when test = 'query.type == 1 '>",
            "AND FIND_IN_SET(1, o.type)",
            "</when>",
            "<when test = 'query.type == 2 '>",
            "AND FIND_IN_SET(2, o.type)",
            "</when>",
            "<when test = 'query.pdaStartWareIds != null and query.pdaStartWareIds != \"\" '>",
            "AND FIND_IN_SET(ew_start.`id`, #{query.pdaStartWareIds})",
            "</when>",
            "<when test = 'query.pdaDestWareIds != null and query.pdaDestWareIds != \"\" '>",
            "AND FIND_IN_SET(ew_dest.`id`, #{query.pdaDestWareIds})",
            "</when>",
            "<when test = 'query.isExternalWarehouse != null'>",
            "AND o.`is_external_warehouse` = #{query.isExternalWarehouse}",
            "</when>",
            "<when test = 'query.status != null'>",
            "AND o.`status` = #{query.status}",
            "</when>",
            "<when test = 'query.abnormalState != null'>",
            "<choose>",
            "<when test = 'query.abnormalState != 0'>",
            "<choose>",
            "<when test = 'query.abnormalState != -1'>",
            "AND o.`abnormal_state` = #{query.abnormalState}",
            "</when>",
            "<otherwise>",
            "AND o.`abnormal_state` != 0",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<otherwise>",
            "AND o.`abnormal_state` = 0",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test = 'query.inWarehouseState != null'>",
            "AND o.`in_warehouse_state` = #{query.inWarehouseState}",
            "</when>",
            "<when test = 'query.shipmentState != null'>",
            "AND o.`shipment_State` = #{query.shipmentState}",
            "</when>",
            "<when test = 'query.auditType != null'>",
            "<choose>",
            "<when test = 'query.auditType != 0'>",
            "<choose>",
            "<when test = 'query.auditType != -1'>",
            "AND o.`audit_type` = #{query.auditType}",
            "</when>",
            "<otherwise>",
            "AND o.`audit_type` != 0",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<otherwise>",
            "AND o.`audit_type` = 0",
            "</otherwise>",
            "</choose>",
            "</when>",
            "<when test = 'query.salesmanId != null '>",
            "AND o.`salesman_id` = #{query.salesmanId}",
            "</when>",
            "<when test = 'query.customsType != null'>",
            "AND o.`customs_type` = #{query.customsType}",
            "</when>",
            "<when test = 'query.productRecord != null'>",
            "AND o.`product_record` = #{query.productRecord}",
            "</when>",
            "<when test = 'query.transportId != null'>",
            "AND o.`transport_id` = #{query.transportId}",
            "</when>",
            "<when test = 'query.warehouseType != null'>",
            "AND o.`warehouse_type` = #{query.warehouseType}",
            "</when>",
            "<when test = 'query.number != null and query.number != \"\" '>",
            "AND (o.`number` like concat('%',concat(#{query.number},'%')) ",
            "or ",
            "o.order_id in ",
            "(select distinct oi.order_id from ecw_order_item oi ",
            "where ",
            "(oi.warehouse_in_info is null and oi.express_no like concat('%',concat(#{query.number},'%')))",
            " or ",
            "(oi.warehouse_in_info is not null and oi.warehouse_in_info ->> '$.expressNo' like concat('%',concat(#{query.number},'%')))))",
            "</when>",
            "<when test = 'query.marks != null and query.marks != \"\" '>",
            "AND o.`marks` like concat('%',concat(#{query.marks},'%'))",
            "</when>",
            "<when test = 'query.departureId != null '>",
            "AND de.`departure_id` = #{query.departureId}",
            "</when>",
            "<when test = 'query.objectiveId != null '>",
            "AND ob.`objective_id` = #{query.objectiveId}",
            "</when>",
            "<when test = 'query.consignorId != null '>",
            "AND nor.`customer_id` = #{query.consignorId} ",
            "</when>",
            "<when test = 'query.consignorPhone != null and query.consignorPhone != \"\"  '>",
            "AND nor.`phone` = #{query.consignorPhone} ",
            "</when>",
            "<when test = 'query.consigneeId != null '>",
            "AND nee.`customer_id` = #{query.consigneeId}",
            "</when>",
            "<when test = 'query.consigneePhone != null and query.consigneePhone != \"\"  '>",
            "AND nee.`phone` = #{query.consigneePhone} ",
            "</when>",
            "<when test = 'query.isCargoControl != null '>",
            "AND o.`is_cargo_control` = #{query.isCargoControl}",
            "</when>",
            "<when test = 'query.orderNo != null and query.orderNo != \"\" '>",
            "AND o.`order_no` like concat('%',concat(#{query.orderNo},'%'))",
            "</when>",
            "<when test = 'query.orderIdList != null and query.orderIdList.size() != 0'>",
            "AND o.`order_id` in ",
            "<foreach item='orderId' index='index' collection='query.orderIdList' open='(' separator=',' close=')'>#{orderId}</foreach> ",
            "</when>",
            "<when test = 'query.tidanNo != null and query.tidanNo != \"\" '>",
            "AND o.`tidan_no` like concat('%',concat(#{query.tidanNo},'%'))",
            "</when>",
//            "<when test = 'query.userId != null'>",
//            "AND o.`user_id` = #{query.userId}",
//            "</when>",
            "<when test = 'query.customerId != null'>",
            "AND o.`customer_id` = #{query.customerId}",
            "</when>",
            "<when test = 'query.beginRucangTime != null and query.endRucangTime != null '>",
//            "AND o.`rucang_time` between #{query.beginRucangTime} and #{query.endRucangTime}",
            "AND o.order_id in(select distinct wi.order_id from ecw_order_warehouse_in wi where wi.deleted = 0 and wi.`in_time` between #{query.beginRucangTime} and #{query.endRucangTime})",
            "</when>",
            "<when test = 'query.beginQingguanTime != null and query.endQingguanTime != null '>",
            "AND o.`qingguan_time` between #{query.beginQingguanTime} and #{query.endQingguanTime}",
            "</when>",
            "<when test = 'query.beginDaogangTime != null and query.endDaogangTime != null '>",
            "AND o.`daogang_time` between #{query.beginDaogangTime} and #{query.endDaogangTime}",
            "</when>",
            "<when test = 'query.beginPreLoadTime != null and query.endPreLoadTime != null '>",
            "AND o.`status` > 5 AND o.`pre_load_time` between #{query.beginPreLoadTime} and #{query.endPreLoadTime}",
            "</when>",
            "<when test = 'query.beginLoadTime != null and query.endLoadTime != null '>",
            "AND o.`load_time` between #{query.beginLoadTime} and #{query.endLoadTime}",
            "</when>",
            "<when test = 'query.beginUnloadTime != null and query.endUnloadTime != null '>",
            "AND o.`unload_time` between #{query.beginUnloadTime} and #{query.endUnloadTime}",
            "</when>",
            "<when test = 'query.beginTakeTime != null and query.endTakeTime != null '>",
            "AND o.`status` > 19 AND o.`take_time` between #{query.beginTakeTime} and #{query.endTakeTime}",
            "</when>",
            "<when test = 'query.beginSplitTime != null and query.endSplitTime != null '>",
            "AND o.`split_time` between #{query.beginSplitTime} and #{query.endSplitTime}",
            "</when>",
            "<when test = 'query.beginCreateTime != null and query.endCreateTime != null '>",
            "AND o.`create_time` between #{query.beginCreateTime} and #{query.endCreateTime}",
            "</when>",
            "<when test = 'query.beginPickTime != null and query.endPickTime != null '>",
            "AND o.order_id in(select distinct op.order_id from ecw_order_cargo_control_pick op where op.deleted = 0 and op.status in(1,3) and op.`create_time` between #{query.beginPickTime} and #{query.endPickTime})",
            "</when>",
            "<when test = 'query.beginPickUpTime != null and query.endPickUpTime != null '>",
            "AND o.order_no in(select distinct op.order_id from ecw_order_pickup op where op.deleted = 0 and op.`create_time` between #{query.beginPickUpTime} and #{query.endPickUpTime})",
            "</when>",
            "<when test = 'query.beginWarehouseInTime != null and query.endWarehouseInTime != null '>",
            "AND o.order_id in(select distinct wi.order_id from ecw_order_warehouse_in wi where wi.deleted = 0 and wi.`update_time` between #{query.beginWarehouseInTime} and #{query.endWarehouseInTime})",
            "</when>",
            "<when test = 'query.creator != null and query.creator != \"\" '>",
            "AND o.`creator` = #{query.creator} and (o.user_id is null or o.user_id = 0)",
            "</when>",
            "<when test = 'query.searchKey != null and query.searchKey != \"\" '>",
            "AND concat(IFNULL(o.`order_no`,''), IFNULL(o.`old_numbers`,'') ,IFNULL(o.`marks`,''),IFNULL(nee.`name`,''),IFNULL(nee.`phone`,'')) like concat('%',concat(#{query.searchKey},'%'))",
            "</when>",
            "<when test = 'query.consignorKey != null and query.consignorKey != \"\" '>",
            "AND (concat(IFNULL(nor.`name`,''),IFNULL(nor.`name_en`,''),IFNULL(nor.`phone`,'')) like concat('%',concat(#{query.consignorKey},'%'))",
            "OR nor.customer_id in (select c.id from ecw_customer c where c.number like concat('%',concat(#{query.consignorKey},'%'))))",
            "</when>",
            "<when test = 'query.consigneeKey != null and query.consigneeKey != \"\" '>",
            "AND (concat(IFNULL(nee.`name`,''),IFNULL(nee.`name_en`,''),IFNULL(nee.`phone`,'')) like concat('%',concat(#{query.consigneeKey},'%'))",
            "OR nee.customer_id in (select c.id from ecw_customer c where c.number like concat('%',concat(#{query.consigneeKey},'%'))))",
            "</when>",
            "<when test = 'query.numberKey != null and query.numberKey != \"\" '>",
            "AND concat(IFNULL(o.`order_no`,''), IFNULL(o.`old_numbers`,'') ,IFNULL(o.`marks`,''),IFNULL(o.`tidan_no`,''),IFNULL(o.`no_charge_record`,'')) like concat('%',concat(#{query.numberKey},'%'))",
            "</when>",
            "<when test = 'query.prodKey != null and query.prodKey != \"\" '>",
            "and (o.order_id in(",
            "select oi.order_id ",
            "from ecw_order_item oi ",
            "left join ecw_product p ",
            "on oi.prod_id = p.id ",
            "left join ecw_product_type pt ",
            "on pt.id = oi.prod_type ",
            "where oi.deleted = 0 and concat(IFNULL(oi.`prod_title_zh`,''),IFNULL(oi.`prod_title_en`,''),IFNULL(pt.`title_zh`,''),IFNULL(pt.`title_en`,'')) like concat('%',concat(#{query.prodKey},'%'))",
            "))",
            "</when>",
            "<when test = 'query.startWarehouseId != null and query.destWarehouseId != null '>",
            "and (o.line_id in(",
            "select whl.id ",
            "from ecw_warehouse_line whl ",
            "where whl.start_warehouse_id = #{query.startWarehouseId} and whl.dest_warehouse_id = #{query.destWarehouseId} ",
            "))",
            "</when>",
            "<when test = 'query.startWarehouseId != null and query.destWarehouseId == null '>",
            "and (o.line_id in(",
            "select whl.id ",
            "from ecw_warehouse_line whl ",
            "where whl.start_warehouse_id = #{query.startWarehouseId}  ",
            "))",
            "</when>",
            "<when test = 'query.startWarehouseId == null and query.destWarehouseId != null '>",
            "and (o.line_id in(",
            "select whl.id ",
            "from ecw_warehouse_line whl ",
            "where whl.dest_warehouse_id = #{query.destWarehouseId} ",
            "))",
            "</when>",
            "<when test = 'query.isNeat == false  '>",
            " AND o.sum_num  <![CDATA[ < ]]> o.cost->>'$.totalNum' ",
            "</when>",
            "<when test = 'query.isNeat == true  '>",
            " AND o.sum_num  = o.cost->>'$.totalNum' ",
            "</when>",
            "<when test = 'query.customerDetailId != null'>",
            "AND (o.`customer_id` = #{query.customerDetailId} or nor.`customer_id` = #{query.customerDetailId}  or nee.`customer_id` = #{query.customerDetailId})",
            "</when>",
            "<when test = 'query.userType == 2 '>",
            "HAVING st = 0 ",
            "</when>",
            "</script>"
    })
    IPage<OrderBackPageVO> orderExceptionPage(IPage<OrderBackPageVO> mpPage, @Param("query") OrderQueryVO query);*/


    List<String> getCustomerServiceById(@Param("salesmanId") Long salesmanId);

}
