package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmswms.entity.WhsOperateLog;
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-04-14
 */
@Mapper
public interface WhsOperateLogMapper extends BaseMapper<WhsOperateLog> {

    @Select("SELECT WOL.LOG_ID AS \"logId\",\n" +
            "       (CASE WOL.DOCUMENT_TYPE\n" +
            "           WHEN '1' THEN '1-自管仓仓库库存日志'\n" +
            "           WHEN '2' THEN '2-自管仓储位库存日志'\n" +
            "           WHEN '3' THEN '3-海外仓仓库库存日志'\n" +
            "           ELSE '4-海外仓储位库存日志' END) AS \"documentType\",\n" +
            "       WOL.UUID_STORAGE AS \"uuidStorage\",\n" +
            "       S.WERKS AS \"WERKS\",\n" +
            "       S.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       S.STORAGE_NO AS \"storageNo\",\n" +
            "       WOL.MATERIAL_NO AS \"materialNo\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       WOL.BOX_NOTE AS \"boxNote\",\n" +
            "       WOL.QUANTITY AS \"quantity\",\n" +
            "       (CASE WOL.OPERATION_TYPE\n" +
            "           WHEN '1' THEN '1-入库'\n" +
            "           WHEN '2' THEN '2-上架'\n" +
            "           WHEN '3' THEN '3-下架'\n" +
            "           ELSE '4-出库' END) AS \"operationType\",\n" +
            "       WOL.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       WOL.RELATE_DOCUMENT_NUMBER AS \"relateDocumentNumber\",\n" +
            "       WOL.COMMENTS AS \"comments\",\n" +
            "       WOL.USER_ID AS \"userId\",\n" +
            "       U.LAST_NAME AS \"userName\",\n" +
            "       WOL.CREATE_TIME AS \"createTime\",\n" +
            "       WOL.REMARK AS \"remarks\",\n" +
            "       WOL.LOG_AUTO_ID AS \"logAutoId\"\n" +
            "FROM WHS_OPERATE_LOG WOL\n" +
            "    LEFT JOIN WHS_STORAGE S ON WOL.UUID_STORAGE = S.UUID\n" +
            "    LEFT JOIN SPMS_USER U ON WOL.USER_ID = U.WORK_CODE\n" +
            "    LEFT JOIN MATERIAL_SAP MS on WOL.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "WHERE WOL.UUID_STORAGE = #{uuid} ORDER BY WOL.LOG_AUTO_ID DESC")
    List<WhsOperateLog> getWhsOperateLog(@Param("uuid") String uuid);

    @Select("<script> \n" +
            "SELECT WOL.LOG_ID AS \"logId\",\n" +
            "       WOL.DOCUMENT_TYPE AS \"documentType\",\n" +
            "       WOL.UUID_STORAGE AS \"uuidStorage\",\n" +
            "       WOL.WERKS AS \"werks\",\n" +
            "       WOL.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       S.STORAGE_NO AS \"storageNo\",\n" +
            "       WOL.MATERIAL_NO AS \"materialNo\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       WOL.BOX_NOTE AS \"boxNote\",\n" +
            "       WOL.QUANTITY AS \"quantity\",\n" +
            "       WOL.OPERATION_TYPE AS \"operationType\",\n" +
            "       WOL.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       WOL.RELATE_DOCUMENT_NUMBER AS \"relateDocumentNumber\",\n" +
            "       WOL.COMMENTS AS \"comments\",\n" +
            "       WOL.USER_ID AS \"userId\",\n" +
            "       U.LAST_NAME AS \"userName\",\n" +
            "       WOL.CREATE_TIME AS \"createTime\",\n" +
            "       WOL.REMARK AS \"remarks\",\n" +
            "       WOL.LOG_AUTO_ID AS \"logAutoId\",\n" +
            "       WOUT.WHS_OUT_NO AS \"whsOutNo\",\n"+
            "       WOUT.PACKAGE_LIST AS \"packageList\",\n"+
            "       (CASE WHEN MS.UNIT_SALES IS NULL THEN MS.BASIC_UNIT ELSE MS.UNIT_SALES END) AS\"unitSales\",\n" +
            "       (CASE WHEN MS.NUMERATOR IS NULL THEN 1 ELSE MS.NUMERATOR END) AS \"numerator\",\n" +
            "       (CASE WHEN MS.DENOMINATOR IS NULL THEN 1 ELSE MS.DENOMINATOR END) AS \"denominator\",\n" +
            "                MS.NET_WEIGHT AS \"netWeight\",\n" +
            "                MS.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "                MS.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "                MS.VOLUME_QTY AS \"volumeQty\",\n" +
            "                MS.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "                SBN.BOX_GROSS_WEIGHT AS \"boxGrossWeight\",\n" +
            "                SBN.BOX_MATERIAL_NAME AS \"boxMaterialName\",\n" +
            "                SBN.BOX_VOLUME AS \"boxVolume\",\n" +
            "                SBN.BOX_LENGTH AS \"boxLength\",\n" +
            "                SBN.BOX_WIDTH AS \"boxWidth\",\n" +
            "                SBN.BOX_HEIGHT AS \"boxHeight\",\n" +
            "                SBN.BOX_COLOR AS \"boxColor\"\n" +
            "FROM WHS_OPERATE_LOG WOL\n" +
            "    LEFT JOIN WHS_STORAGE S ON WOL.UUID_STORAGE = S.UUID\n" +
            "    LEFT JOIN SPMS_USER U ON WOL.USER_ID = U.WORK_CODE\n" +
            "    LEFT JOIN MATERIAL_SAP MS on WOL.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "    LEFT JOIN SAP_BOX_NOTE SBN on WOL.BOX_NOTE = SBN.BOX_NOTE\n" +
            "    LEFT JOIN WHS_OUT WOUT ON WOUT.UUID=WOL.RELATE_DOCUMENT_NUMBER \n" +
            "WHERE 1=1\n" +
            "     <when test='werks != null'> AND WOL.WERKS = #{werks}  </when>\n" +
            "     <when test='whsLocationCode != null'> AND WOL.WHS_LOCATION_CODE = #{whsLocationCode}  </when>\n" +
            "     <when test='documentType != null'> AND WOL.DOCUMENT_TYPE = #{documentType}  </when>\n" +
            "     <when test='materialNo != null'> AND WOL.MATERIAL_NO = #{materialNo}  </when>\n" +
            "     <when test='boxNote != null'> AND WOL.BOX_NOTE = #{boxNote}  </when>\n" +
            "     <when test='operationType != null'> AND WOL.OPERATION_TYPE = #{operationType}  </when>\n" +
            "     <when test='userId != null'> AND WOL.USER_ID = #{userId}  </when>\n" +
            "     <when test='storageNo != null'> AND S.STORAGE_NO = #{storageNo}  </when>\n" +
            "     <when test='queryBox != null'> AND WOL.BOX_NOTE IS NOT NULL  </when>\n" +
            "     <when test='queryMaterial != null'> AND WOL.MATERIAL_NO IS NOT NULL  </when>\n" +
            "     <when test='startDate != null'> AND WOL.CREATE_TIME BETWEEN TO_DATE(#{startDate}, 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE(#{endDate}, 'yyyy-mm-dd hh24:mi:ss') </when>\n" +
            "ORDER BY WOL.LOG_AUTO_ID DESC \n" +
            "</script>")
    List<Map<String,Object>> getWhsDeskAccount(@Param("werks") String werks,
                                               @Param("whsLocationCode") String whsLocationCode,
                                               @Param("storageNo") String storageNo,
                                               @Param("materialNo") String materialNo,
                                               @Param("boxNote") String boxNote,
                                               @Param("documentType") String documentType,
                                               @Param("operationType") String operationType,
                                               @Param("startDate") String startDate,
                                               @Param("endDate") String endDate,
                                               @Param("userId") String userId,
                                               @Param("queryBox") String queryBox,
                                               @Param("queryMaterial") String queryMaterial);


    @Select("<script> \n" +
            "SELECT WOL.LOG_ID AS \"logId\",\n" +
            "       WOL.DOCUMENT_TYPE AS \"documentType\",\n" +
            "       WOL.UUID_STORAGE AS \"uuidStorage\",\n" +
            "       WOL.WERKS AS \"werks\",\n" +
            "       WOL.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       S.STORAGE_NO AS \"storageNo\",\n" +
            "       WOL.MATERIAL_NO AS \"materialNo\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       WOL.BOX_NOTE AS \"boxNote\",\n" +
            "       WOL.QUANTITY AS \"quantity\",\n" +
            "       WOL.OPERATION_TYPE AS \"operationType\",\n" +
            "       WOL.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
            "       WOL.RELATE_DOCUMENT_NUMBER AS \"relateDocumentNumber\",\n" +
            "       WOL.COMMENTS AS \"comments\",\n" +
            "       WOL.USER_ID AS \"userId\",\n" +
            "       U.LAST_NAME AS \"userName\",\n" +
            "       WOL.CREATE_TIME AS \"createTime\",\n" +
            "       WOL.REMARK AS \"remarks\",\n" +
            "       WOL.LOG_AUTO_ID AS \"logAutoId\",\n" +
            "       WOUT.WHS_OUT_NO AS \"whsOutNo\",\n"+
            "       WOUT.PACKAGE_LIST AS \"packageList\",\n"+
            "       (CASE WHEN MS.UNIT_SALES IS NULL THEN MS.BASIC_UNIT ELSE MS.UNIT_SALES END) AS\"unitSales\",\n" +
            "       (CASE WHEN MS.NUMERATOR IS NULL THEN 1 ELSE MS.NUMERATOR END) AS \"numerator\",\n" +
            "       (CASE WHEN MS.DENOMINATOR IS NULL THEN 1 ELSE MS.DENOMINATOR END) AS \"denominator\",\n" +
            "                MS.NET_WEIGHT AS \"netWeight\",\n" +
            "                MS.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "                MS.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "                MS.VOLUME_QTY AS \"volumeQty\",\n" +
            "                MS.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "                SBN.BOX_GROSS_WEIGHT AS \"boxGrossWeight\",\n" +
            "                SBN.BOX_MATERIAL_NAME AS \"boxMaterialName\",\n" +
            "                SBN.BOX_VOLUME AS \"boxVolume\",\n" +
            "                SBN.BOX_LENGTH AS \"boxLength\",\n" +
            "                SBN.BOX_WIDTH AS \"boxWidth\",\n" +
            "                SBN.BOX_HEIGHT AS \"boxHeight\",\n" +
            "                SBN.BOX_COLOR AS \"boxColor\"\n" +
            "FROM WHS_OPERATE_LOG WOL\n" +
            "    LEFT JOIN WHS_STORAGE S ON WOL.UUID_STORAGE = S.UUID\n" +
            "    LEFT JOIN SPMS_USER U ON WOL.USER_ID = U.WORK_CODE\n" +
            "    LEFT JOIN MATERIAL_SAP MS on WOL.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "    LEFT JOIN SAP_BOX_NOTE SBN on WOL.BOX_NOTE = SBN.BOX_NOTE\n" +
            "    LEFT JOIN WHS_OUT WOUT ON WOUT.UUID=WOL.RELATE_DOCUMENT_NUMBER \n" +
            "WHERE 1=1\n" +
            "     <when test='werks != null'> AND WOL.WERKS = #{werks}  </when>\n" +
            "     <when test='whsLocationCode != null'> AND WOL.WHS_LOCATION_CODE = #{whsLocationCode}  </when>\n" +
            "     <when test='documentType != null'> AND WOL.DOCUMENT_TYPE = #{documentType}  </when>\n" +
            "     <when test='materialNo != null'> AND WOL.MATERIAL_NO = #{materialNo}  </when>\n" +
            "     <when test='boxNote != null'> AND WOL.BOX_NOTE = #{boxNote}  </when>\n" +
            "     <when test='operationType != null'> AND WOL.OPERATION_TYPE = #{operationType}  </when>\n" +
            "     <when test='userId != null'> AND WOL.USER_ID = #{userId}  </when>\n" +
            "     <when test='storageNo != null'> AND S.STORAGE_NO = #{storageNo}  </when>\n" +
            "     <when test='queryBox != null'> AND WOL.BOX_NOTE IS NOT NULL  </when>\n" +
            "     <when test='queryMaterial != null'> AND WOL.MATERIAL_NO IS NOT NULL  </when>\n" +
            "     <when test='startDate != null'> AND WOL.CREATE_TIME BETWEEN TO_DATE(#{startDate}, 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE(#{endDate}, 'yyyy-mm-dd hh24:mi:ss') </when>\n" +
            "ORDER BY WOL.LOG_AUTO_ID DESC \n" +
            "</script>")
    List<Map<String,Object>> getWhsDeskAccountPage(Page<Map<String, Object>> pages, @Param("werks") String werks,
                                                   @Param("whsLocationCode") String whsLocationCode,
                                                   @Param("storageNo") String storageNo,
                                                   @Param("materialNo") String materialNo,
                                                   @Param("boxNote") String boxNote,
                                                   @Param("documentType") String documentType,
                                                   @Param("operationType") String operationType,
                                                   @Param("startDate") String startDate,
                                                   @Param("endDate") String endDate,
                                                   @Param("userId") String userId,
                                                   @Param("queryBox") String queryBox,
                                                   @Param("queryMaterial") String queryMaterial);
    
    @Select("<script> \n" +
    		"SELECT DD.* FROM ( \n" +
            "SELECT WOL.LOG_ID AS \"logId\",\n" +
            "       U.LAST_NAME AS \"userName\",\n" +
            "       TO_CHAR(WOL.CREATE_TIME, 'yyyy-MM-dd') AS \"createTime\",\n" +
            "       WOL.RELATE_DOCUMENT_NUMBER AS \"relateDocumentNumber\",\n" +
            "       WOL.DOCUMENT_TYPE AS \"documentType\",\n" +
            "       WOL.REMARK AS \"remarks\",\n" +
            "       '' AS \"overseaWhsInNo\",\n" +
            "       WOUT.WHS_OUT_NO AS \"whsOutNo\",\n"+
            "       OTHER.SPMS_ID AS \"overseaOtherTaskNo\",\n" +
    		"       WOL.DELIVERY_NOTE_SAP AS \"deliveryNoteSap\",\n" +
    		"       MOVE.WHS_MOVE_NO AS \"overseaWhsMoveNo\",\n" +
    		"       WOL.BOX_NOTE AS \"boxNote\",\n" +
    		"       WOUT.CABINET_NUMBER AS \"cabinetNumber\",\n"+
    		"       WOUT.PACKAGE_LIST AS \"packageList\",\n"+
    		"       '' AS \"requisitionNo\",\n" +
    		"       U.LAST_NAME AS \"lastName\",\n" +
            "       '' AS \"costCenter\",\n" +
    		"       WOL.OPERATION_TYPE AS \"operationType\",\n" +
    		"       WOL.COMMENTS AS \"comments\",\n" +
    		"       WOL.MATERIAL_NO AS \"materialNo\",\n" +
    		"       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
    		"       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
    		"       (CASE WHEN MS.UNIT_SALES IS NULL THEN MS.BASIC_UNIT ELSE MS.UNIT_SALES END) AS\"unitSales\",\n" +
    		"       WOL.QUANTITY AS \"quantity\",\n" +
    		"       WOL.WERKS AS \"werks\",\n" +
    		"       WOL.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
    		"       S.STORAGE_NO AS \"storageNo\",\n" +
    		"       WOL.MOVEMENT_TYPE AS \"movementType\",\n" +
    		"       WOL.USER_ID AS \"userId\",\n" +
    		"       WOL.LOG_AUTO_ID AS \"logAutoId\",\n" +
    		"       (CASE WHEN MS.NUMERATOR IS NULL THEN 1 ELSE MS.NUMERATOR END) AS \"numerator\",\n" +
    		"       (CASE WHEN MS.DENOMINATOR IS NULL THEN 1 ELSE MS.DENOMINATOR END) AS \"denominator\",\n" +
    		"       WOL.UUID_STORAGE AS \"uuidStorage\"\n" +
            "FROM WHS_OPERATE_LOG WOL\n" +
            "    LEFT JOIN WHS_STORAGE S ON WOL.UUID_STORAGE = S.UUID\n" +
            "    LEFT JOIN SPMS_USER U ON WOL.USER_ID = U.WORK_CODE\n" +
            "    LEFT JOIN MATERIAL_SAP MS on WOL.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "    LEFT JOIN SAP_BOX_NOTE SBN on WOL.BOX_NOTE = SBN.BOX_NOTE\n" +
            "    LEFT JOIN WHS_OUT WOUT ON WOUT.UUID = WOL.RELATE_DOCUMENT_NUMBER \n" +
            "    LEFT JOIN WHS_OTHER_TASK OTHER ON OTHER.UUID = WOL.RELATE_DOCUMENT_NUMBER \n" +
            "    LEFT JOIN WHS_MOVE MOVE ON MOVE.WHS_MOVE_UUID = WOL.RELATE_DOCUMENT_NUMBER \n" +
            "WHERE TO_CHAR(WOL.CREATE_TIME, 'yyyy-MM-dd') = #{nowDate}\n" +
            "	AND S.WERKS = 'CN01' \n" +
            "UNION ALL \n" +
            "SELECT WOL.LOG_ID AS \"logId\",\n" +
            "       U.LAST_NAME AS \"userName\",\n" +
            "       TO_CHAR(WOL.CREATE_TIME, 'yyyy-MM-dd') AS \"createTime\",\n" +
            "       WOL.RELATE_DOCUMENT_NUMBER AS \"relateDocumentNumber\",\n" +
            "       WOL.DOCUMENT_TYPE AS \"documentType\",\n" +
            "       WOL.REMARK AS \"remarks\",\n" +
            "       OWI.WHS_IN_NO AS \"overseaWhsInNo\",\n" +
            "       OWO.SPMS_ID AS \"whsOutNo\",\n"+
            "       OOT.SPMS_ID AS \"overseaOtherTaskNo\",\n" +
    		"       DN.SAP_DELIVERY_NOTE AS \"deliveryNoteSap\",\n" +
    		"       OWM.WHS_MOVE_NO AS \"overseaWhsMoveNo\",\n" +
    		"       WOL.BOX_NOTE AS \"boxNote\",\n" +
    		"       OWI.CABINET_NUMBER AS \"cabinetNumber\",\n"+
    		"       OWI.SAP_PACKAGE_LIST AS \"packageList\",\n"+
    		"       ORE.SPMS_ID AS \"requisitionNo\",\n" +
    		"       U.LAST_NAME AS \"lastName\",\n" +
            "       OWO.COST_CENTER AS \"costCenter\",\n" +
    		"       WOL.OPERATION_TYPE AS \"operationType\",\n" +
    		"       WOL.COMMENTS AS \"comments\",\n" +
    		"       WOL.MATERIAL_NO AS \"materialNo\",\n" +
    		"       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
    		"       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
    		"       (CASE WHEN MS.UNIT_SALES IS NULL THEN MS.BASIC_UNIT ELSE MS.UNIT_SALES END) AS\"unitSales\",\n" +
    		"       WOL.QUANTITY AS \"quantity\",\n" +
    		"       WOL.WERKS AS \"werks\",\n" +
    		"       WOL.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
    		"       S.STORAGE_NO AS \"storageNo\",\n" +
    		"       WOL.MOVEMENT_TYPE AS \"movementType\",\n" +
    		"       WOL.USER_ID AS \"userId\",\n" +
    		"       WOL.LOG_AUTO_ID AS \"logAutoId\",\n" +
    		"       (CASE WHEN MS.NUMERATOR IS NULL THEN 1 ELSE MS.NUMERATOR END) AS \"numerator\",\n" +
    		"       (CASE WHEN MS.DENOMINATOR IS NULL THEN 1 ELSE MS.DENOMINATOR END) AS \"denominator\",\n" +
    		"       WOL.UUID_STORAGE AS \"uuidStorage\"\n" +
            "FROM WHS_OPERATE_LOG WOL\n" +
            "    LEFT JOIN WHS_STORAGE S ON WOL.UUID_STORAGE = S.UUID\n" +
            "    LEFT JOIN SPMS_USER U ON WOL.USER_ID = U.WORK_CODE\n" +
            "    LEFT JOIN MATERIAL_SAP MS ON WOL.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "    LEFT JOIN OVERSEA_DN DN ON WOL.RELATE_DOCUMENT_NUMBER = DN.SAP_DELIVERY_NOTE\n" +
            "    LEFT JOIN OVERSEA_WHS_IN OWI ON WOL.RELATE_DOCUMENT_NUMBER = OWI.UUID_WHS_IN\n" +
            "    LEFT JOIN OVERSEA_WHS_OUT OWO ON WOL.RELATE_DOCUMENT_NUMBER = OWO.UUID_WHS_OUT\n" +
            "    LEFT JOIN OVERSEA_OTHER_TASK OOT ON WOL.RELATE_DOCUMENT_NUMBER = OOT.UUID\n" +
            "    LEFT JOIN OVERSEA_WHS_MOVE OWM ON WOL.RELATE_DOCUMENT_NUMBER = OWM.UUID\n" +
            "    LEFT JOIN OVERSEA_REQUISITION ORE ON OWO.RELATE_DOCUMENT = ORE.SPMS_ID\n" +
            "WHERE TO_CHAR(WOL.CREATE_TIME, 'yyyy-MM-dd') = #{nowDate}\n" +
            "	AND S.WERKS != 'CN01' \n" +
            ") DD \n" +
            "ORDER BY DD.logAutoId" +
            "</script>")
	List<Map<String,Object>> selectLogByNowDate(@Param("nowDate") String nowDate);



}