package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sunda.spmswms.entity.ShelfOnTask;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

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

/**
 * <p>
 * 上架任务表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-03-19
 */
@Mapper
public interface ShelfOnTaskMapper extends BaseMapper<ShelfOnTask> {

    @Select("SELECT DISTINCT DN.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "                DND.WERKS AS \"werks\",\n" +
            "                DND.WHS_LOCATION_CODE AS \"whsLocationCode\"\n" +
            "FROM SAP_DELIVERY_NOTE DN\n" +
            "    LEFT JOIN SAP_DELIVERY_NOTE_DTL DND ON DN.DELIVERY_NOTE_SAP = DND.DELIVERY_NOTE_SAP\n" +
            "WHERE DN.DELIVERY_NOTE_SAP = #{deliveryNoteSap}")
    Map<String, Object> getSapDeliveryNote(String deliveryNoteSap);

    @Select("<script>" +
            "SELECT SO.TASK_ID AS \"taskId\",\n" +
            "       SO.UUID AS \"uuid\"," +
            "       SO.WERKS AS \"werks\"," +
            "       SO.WHS_LOCATION_CODE AS \"whsLocationCode\"," +
            "       SO.CREATE_USERID AS \"createUserId\"," +
            "       SO.CREATE_DATE AS \"createDate\"," +
            "       SO.TASK_STATUS AS \"taskStatus\"," +
            "       SO.CREATE_TIME AS \"createTime\"," +
            "       SO.UPDATE_USERNAME AS \"updateUsername\"," +
            "       SO.UPDATE_TIME AS \"updateTime\"," +
            "       SO.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\"," +
            "       SO.TASK_TYPE AS \"taskType\"," +
            "       SO.PDA_SN AS \"pdaSN\"," +
            "       SO.RELATE_DOC_NUMBER AS \"relateDocNumber\"," +
            "       WO.WHS_OUT_NO AS \"whsOutNo\"" +
            "FROM SHELF_ON_TASK SO " +
            "       LEFT JOIN WHS_OUT WO ON SO.RELATE_DOC_NUMBER = WO.UUID " +
            "WHERE SO.TASK_STATUS IN \n" +
            "   <foreach item='item' index='index' collection='taskStatusList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "   </foreach>" +
            "   and SO.WHS_LOCATION_CODE IN \n" +
            "   <foreach item='item' index='index' collection='whsLocationCodeList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "   </foreach>" +
            "   <if test='taskId!=null'>and SO.TASK_ID = #{taskId} </if>" +
            "   <if test='createUserId!=null'>and SO.CREATE_USERID = #{createUserId} </if>" +
            "   <if test='werks!=null'>and SO.WERKS = #{werks} </if>" +
            "   <if test='updateUsername!=null'>and SO.UPDATE_USERNAME = #{updateUsername} </if>" +
            "   <if test='pdaSN!=null'>and SO.PDA_SN = #{pdaSN} </if>" +
            "   <if test='taskType!=null'>and SO.TASK_TYPE = #{taskType} </if>" +
            "   <if test='deliveryNoteSap!=null'>and SO.DELIVERY_NOTE_SAP = #{deliveryNoteSap} </if>" +
            "   ORDER BY SO.CREATE_TIME DESC\n" +
            "   </script>")
    List<Map<String, Object>> getShelfOnTask(
            @Param("taskId") String taskId,
            @Param("createUserId") String createUserId,
            @Param("werks") String werks,
            @Param("whsLocationCodeList") List<String> whsLocationCodeList,
            @Param("taskStatusList") List<String> taskStatusList,
            @Param("updateUsername") String updateUsername,
            @Param("deliveryNoteSap") String deliveryNoteSap,
            @Param("pdaSN") String pdaSN,
            @Param("taskType") String taskType
    );

    @Select("SELECT SO.UUID AS \"uuid\",\n" +
            "       SO.TASK_ROW_ID AS \"taskRowId\",\n" +
            "       SO.MATERIAL_NO AS \"materialNo\",\n" +
            "       SO.BOX_NOTE AS \"boxNote\",\n" +
            "       SO.QUANTITY AS \"quantity\",\n" +
            "       SO.CURRENT_STORAGE AS \"currentStorage\",\n" +
            "       SO.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       SO.TARGET_STORAGE AS \"targetStorage\",\n" +
            "       WS.STORAGE_NO AS \"storageNo\",\n" +
            "       SO.ACTUAL_OPERATE_QTY AS \"actualOperateQty\",\n" +
            "       SO.UNPLANNED AS \"unplanned\",\n" +
            "       WS2.UUID AS \"recommendStorageUuid\",\n" +
            "       WS2.STORAGE_NO AS \"recommendStorageNo\"\n" +
            "FROM SHELF_ON_TASK_DTL SO\n" +
            "    LEFT JOIN WHS_STORAGE_INVENTORY WSI ON SO.BOX_NOTE = WSI.BOX_NOTE\n" +
            "    LEFT JOIN WHS_STORAGE WS ON SO.TARGET_STORAGE = WS.UUID\n" +
            "    LEFT JOIN WHS_STORAGE WS2 ON WSI.UUID = WS2.UUID " +
            "WHERE SO.UUID = #{uuid} \n")
    List<Map<String, Object>> getShelfOnTaskDtlByUuid(@Param("uuid") String uuid);

    @Select("SELECT ROWNUM * 10 AS \"rowId\", RE.*\n" +
            "FROM (SELECT DISTINCT SOD.UUID AS \"uuid\",\n" +
            "                      SOD.TASK_ROW_ID AS \"taskRowId\",\n" +
            "                      SOD.BOX_NOTE AS \"boxNote\",\n" +
            "                      SOD.QUANTITY AS \"quantity\",\n" +
            "                      SOD.CURRENT_STORAGE AS \"currentStorage\",\n" +
            "                      SOD.TARGET_STORAGE AS \"targetStorage\",\n" +
            "                      WS.STORAGE_NO AS \"storageNo\",\n" +
            "                      SOD.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "                      SOD.MATERIAL_NO AS \"materialNo\",\n" +
            "                      SOD.ACTUAL_OPERATE_QTY AS \"actualOperateQty\",\n" +
            "                      SOD.UNPLANNED AS \"unplanned\"\n" +
            "      FROM SHELF_ON_TASK_DTL SOD\n" +
            "               LEFT JOIN WHS_STORAGE WS ON SOD.TARGET_STORAGE = WS.UUID\n" +
            "      WHERE SOD.UUID = #{uuid}) RE ")
    List<Map<String, Object>> getShelfOnTaskDtlAndStorage(@Param("uuid") String uuid);

    @Select("<script>" +
            "SELECT TASK_ID AS \"taskId\",\n" +
            "       UUID AS \"uuid\"," +
            "       WERKS AS \"werks\"," +
            "       WHS_LOCATION_CODE AS \"whsLocationCode\"," +
            "       CREATE_USERID AS \"createUserId\"," +
            "       CREATE_DATE AS \"createDate\"," +
            "       TASK_STATUS AS \"taskStatus\"," +
            "       CREATE_TIME AS \"createTime\"," +
            "       UPDATE_USERNAME AS \"updateUsername\"," +
            "       UPDATE_TIME AS \"updateTime\"," +
            "       DELIVERY_NOTE_SAP AS \"deliveryNoteSap\"," +
            "       TASK_TYPE AS \"taskType\"," +
            "       PDA_SN AS \"pdaSN\"," +
            "       RELATE_DOC_NUMBER AS \"relateDocNumber\" " +
            "FROM SHELF_ON_TASK WHERE TASK_STATUS IN \n" +
            "   <foreach item='item' index='index' collection='taskStatusList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "   </foreach>" +
            "   AND WERKS = #{werks}" +
            "   AND WHS_LOCATION_CODE = #{whsLocationCode}" +
            "   <if test='taskType!=null'>AND TASK_TYPE = #{taskType} </if>" +
            "   <if test='deliveryNoteSap!=null'>AND DELIVERY_NOTE_SAP = #{deliveryNoteSap} </if>" +
            "   <if test='relateDocNumber!=null'>and RELATE_DOC_NUMBER = #{relateDocNumber} </if>" +
            "   <if test='beginDate!=null'> AND TO_DATE(CREATE_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{beginDate}, 'yyyy-MM-dd') </if> \n" +
            "   <if test='endDate!=null'> AND TO_DATE(CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{endDate}, 'yyyy-MM-dd') </if> \n" +
            "   ORDER BY CREATE_TIME DESC\n" +
            "   </script>")
    List<Map<String, Object>> queryShelfOnTasks(
            @Param("werks") String werks,
            @Param("whsLocationCode") String whsLocationCode,
            @Param("taskType") String taskType,
            @Param("taskStatusList") List<String> taskStatusList,
            @Param("deliveryNoteSap") String deliveryNoteSap,
            @Param("relateDocNumber") String relateDocNumber,
            @Param("beginDate") String beginDate,
            @Param("endDate") String endDate
    );

    @Select("SELECT RE.*\n" +
            "FROM (\n" +
            "         SELECT SOT.UUID AS \"uuid\",\n" +
            "                SOT.TASK_ID AS \"taskId\",\n" +
            "                SOT.WERKS AS \"werks\",\n" +
            "                SOT.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "                SOT.CREATE_USERID AS \"createUserId\",\n" +
            "                SOT.CREATE_DATE AS \"createDate\",\n" +
            "                SOT.TASK_STATUS AS \"taskStatus\",\n" +
            "                SOT.CREATE_TIME AS \"createTime\",\n" +
            "                SOT.UPDATE_USERNAME AS \"updateUsername\",\n" +
            "                SOT.UPDATE_TIME AS \"updateTime\",\n" +
            "                SOT.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "                SOT.PDA_SN AS \"pdaSN\",\n" +
            "                SOT.TASK_TYPE AS \"taskType\",\n" +
            "                SOT.RELATE_DOC_NUMBER AS \"relateDocNumber\"\n" +
            "         FROM SHELF_ON_TASK SOT\n" +
            "         WHERE SOT.TASK_TYPE = #{taskType} AND SOT.RELATE_DOC_NUMBER = #{relateDocNumber} \n" +
            "         ORDER BY SOT.CREATE_TIME DESC\n" +
            "         ) RE\n" +
            "WHERE ROWNUM = 1 \n")
    ShelfOnTask getByRelateDocNumber(@Param("relateDocNumber") String relateDocNumber, @Param("taskType") String taskType);
}