package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.WhsOperateLogSearchDTO;
import com.sunda.spmsoversea.entity.WhsOperateLog;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

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

/**
 * <p>
 * 库存操作日志表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-09-10
 */
@Mapper
@Repository("overseaWhsOperateLogMapper")
public interface WhsOperateLogMapper extends BaseMapper<WhsOperateLog> {

    @Select("<script>" +
            "SELECT WOL.LOG_ID AS \"logId\",\n" +
            "       WOL.DOCUMENT_TYPE AS \"documentType\",\n" +
            "       WOL.UUID_STORAGE AS \"uuidStorage\",\n" +
            "       WS.STORAGE_AREA AS \"storageArea\",\n" +
            "       WS.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" +
            "       MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "       MS.UNIT_SALES AS \"unitSales\",\n" +
            "       WOL.QUANTITY AS \"quantity\",\n" +
            "       WOL.OPERATION_TYPE AS \"operateType\",\n" +
            "       WOL.RELATE_DOCUMENT_NUMBER AS \"relateDocumentNumber\",\n" +
            "       WOL.COMMENTS AS \"comments\",\n" +
            "       WOL.USER_ID AS \"userId\",\n" +
            "       SU.LAST_NAME AS \"username\",\n" +
            "       WOL.CREATE_TIME AS \"createTime\",\n" +
            "       WOL.REMARK AS \"remark\",\n" +
            "       WOL.LOG_AUTO_ID AS \"logAutoId\",\n" +
            "       WOL.WERKS AS \"werks\",\n" +
            "       ORD.LICENSE_NO AS \"licenseNo\",\n" +
            "       WOL.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
//            "       DN.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +、
            "       COALESCE(DN.SAP_DELIVERY_NOTE, OD.SAP_DELIVERY_NOTE) AS \"sapDeliveryNote\",\n" +
            "       OWI.WHS_IN_NO AS \"overseaWhsInNo\",\n" +
            "       OWO.SPMS_ID AS \"overseaWhsOutNo\",\n" +
            "       OOT.SPMS_ID AS \"overseaOtherTaskNo\",\n" +
            "       OWM.WHS_MOVE_NO AS \"overseaWhsMoveNo\",\n" +
            "       (SELECT OWID.BOX_NOTE FROM OVERSEA_WHS_IN_DTL OWID WHERE OWID.UUID_WHS_IN = WOL.RELATE_DOCUMENT_NUMBER AND WOL.MATERIAL_NO = OWID.MATERIAL_NO AND <![CDATA[ ROWNUM <= 1 ]]>) AS \"boxNote\",\n" +
            "       OWI.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       OWI.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       SU.LAST_NAME AS \"lastName\",\n" +
            "       OWO.COST_CENTER AS \"costCenter\",\n" +
            "       WOL.MOVEMENT_TYPE AS \"movementType\",\n" +
            "       ORE.SPMS_ID AS \"requisitionNo\",\n" +
            "       ORE.REMARKS AS \"remarks\",\n" +
            "       (CASE WHEN DN.SAP_DELIVERY_NOTE IS NOT NULL THEN DN.POSTING_DATE WHEN OWI.WHS_IN_NO IS NOT NULL THEN OWI.POSTING_DATE \n" +
            "			WHEN OWO.SPMS_ID IS NOT NULL THEN OWO.POSTING_DATE WHEN OOT.SPMS_ID IS NOT NULL THEN OOT.POSTING_DATE WHEN OWM.WHS_MOVE_NO IS NOT NULL THEN OWM.POSTING_DATE ELSE NULL END) AS \"postingDate\"\n" +
            "FROM WHS_OPERATE_LOG WOL\n" +
            "    LEFT JOIN WHS_STORAGE WS ON WOL.UUID_STORAGE = WS.UUID\n" +
            "    LEFT JOIN SPMS_USER SU ON WOL.USER_ID = SU.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_DELIVERY_NOTE OD ON WOL.RELATE_DOCUMENT_NUMBER = OD.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" +
            "    LEFT JOIN OVERSEA_WHS_OUT_DTL ORD ON OWO.UUID_WHS_OUT = ORD.UUID_WHS_OUT\n" +
            "    AND  WOL.ITEM_NUMBER = ORD.ITEM\n" +
            "WHERE WOL.DOCUMENT_TYPE = '4' AND WS.WERKS != 'CN01'\n" +
            "  <when test='dto.werks!=null and dto.werks != \"\"'> AND WOL.WERKS = #{dto.werks}</when>" +
            "  <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND WOL.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            "  <when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WS.STORAGE_AREA = #{dto.storageArea}</when>" +
            "  <when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WS.STORAGE_NO = #{dto.storageNo}</when>" +
            "  <when test='dto.movementType!=null and dto.movementType != \"\"'> AND WOL.MOVEMENT_TYPE = #{dto.movementType}</when>" +
            "  <when test='dto.endDate!=null and dto.endDate != \"\"'> AND TO_DATE(TO_CHAR(WOL.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-mm-dd hh24:mi:ss')</when>\n" +
            "  <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND TO_DATE(TO_CHAR(WOL.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-mm-dd hh24:mi:ss')</when>\n" +
            "  <when test='dto.postBeginDate!=null and dto.postBeginDate != \"\"'> AND (DN.POSTING_DATE &gt;= #{dto.beginDate} OR OWI.POSTING_DATE &gt;= #{dto.beginDate} OR OWO.POSTING_DATE &gt;= #{dto.beginDate} OR OOT.POSTING_DATE &gt;= #{dto.beginDate} OR OWM.POSTING_DATE &gt;= #{dto.beginDate})</when>\n" +
            "  <when test='dto.postEndDate!=null and dto.postEndDate != \"\"'> AND (DN.POSTING_DATE &lt;= #{dto.postEndDate} OR OWI.POSTING_DATE &lt;= #{dto.postEndDate} OR OWO.POSTING_DATE &lt;= #{dto.postEndDate} OR OOT.POSTING_DATE &lt;= #{dto.postEndDate} OR OWM.POSTING_DATE &lt;= #{dto.postEndDate})</when>\n" +
            "  <when test='dto.createUser!=null and dto.createUser != \"\"'> " +
            "     AND INSTR(WOL.USER_ID || ',' || SU.LAST_NAME, #{dto.createUser}, 1, 1) > 0 " +
            "  </when>" +
            "  <when test='dto.searchInfos!=null ' > " +
            "       <foreach item='item' index='index' collection='dto.searchInfos' open='' separator='' close=''>\n" +
            "           AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{item} , 1, 1) > 0" +
            "       </foreach>" +
            //"     AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{dto.searchInfo}, 1, 1) > 0" +
            "  </when>" +
            "ORDER BY WOL.LOG_AUTO_ID DESC " +
            "</script>")
    List<Map<String, Object>> getWhsOperateLog(IPage iPage, @Param("dto") WhsOperateLogSearchDTO dto);

    @Select("<script>" +
            "SELECT WOL.LOG_ID AS \"logId\",\n" +
            "       WOL.DOCUMENT_TYPE AS \"documentType\",\n" +
            "       WOL.UUID_STORAGE AS \"uuidStorage\",\n" +
            "       WS.STORAGE_AREA AS \"storageArea\",\n" +
            "       WS.STORAGE_NO AS \"storageNo\",\n" +
            "       WOL.MATERIAL_NO AS \"materialNo\",\n" +
            "       ORD.LICENSE_NO AS \"licenseNo\",\n" +
            "       MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "       MS.UNIT_SALES AS \"unitSales\",\n" +
            "       WOL.QUANTITY AS \"quantity\",\n" +
            "       WOL.OPERATION_TYPE AS \"operateType\",\n" +
            "       WOL.RELATE_DOCUMENT_NUMBER AS \"relateDocumentNumber\",\n" +
            "       WOL.COMMENTS AS \"comments\",\n" +
            "       WOL.USER_ID AS \"userId\",\n" +
            "       SU.LAST_NAME AS \"username\",\n" +
            "       WOL.CREATE_TIME AS \"createTime\",\n" +
            "       WOL.REMARK AS \"remark\",\n" +
            "       WOL.LOG_AUTO_ID AS \"logAutoId\",\n" +
            "       WOL.WERKS AS \"werks\",\n" +
            "       WOL.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       WOL.MOVEMENT_TYPE AS \"movementType\",\n" +
//            "       DN.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       COALESCE(DN.SAP_DELIVERY_NOTE, OD.SAP_DELIVERY_NOTE) AS \"sapDeliveryNote\",\n" +
            "       OWI.WHS_IN_NO AS \"overseaWhsInNo\",\n" +
            "       OWO.SPMS_ID AS \"overseaWhsOutNo\",\n" +
            "       OWO.COST_CENTER AS \"costCenter\",\n" +
            "       OOT.SPMS_ID AS \"overseaOtherTaskNo\",\n" +
            "       ORE.SPMS_ID AS \"requisitionNo\",\n" +
            "       OWM.WHS_MOVE_NO AS \"overseaWhsMoveNo\",\n" +
            "       ORE.REMARKS AS \"remarks\",\n" +
            "       (CASE WHEN DN.SAP_DELIVERY_NOTE IS NOT NULL THEN DN.POSTING_DATE WHEN OWI.WHS_IN_NO IS NOT NULL THEN OWI.POSTING_DATE \n" +
            "			WHEN OWO.SPMS_ID IS NOT NULL THEN OWO.POSTING_DATE WHEN OOT.SPMS_ID IS NOT NULL THEN OOT.POSTING_DATE WHEN OWM.WHS_MOVE_NO IS NOT NULL THEN OWM.POSTING_DATE ELSE NULL END) AS \"postingDate\"\n" +
            "FROM WHS_OPERATE_LOG WOL\n" +
            "    LEFT JOIN WHS_STORAGE WS ON WOL.UUID_STORAGE = WS.UUID\n" +
            "    LEFT JOIN SPMS_USER SU ON WOL.USER_ID = SU.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_DELIVERY_NOTE OD ON WOL.RELATE_DOCUMENT_NUMBER = OD.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" +
            "    LEFT JOIN OVERSEA_WHS_OUT_DTL ORD ON OWO.UUID_WHS_OUT = ORD.UUID_WHS_OUT\n" +
            "    AND  WOL.ITEM_NUMBER = ORD.ITEM\n" +
            "WHERE WOL.DOCUMENT_TYPE = '4' AND WS.WERKS != 'CN01'\n" +
            "  <when test='dto.werks!=null and dto.werks != \"\"'> AND WOL.WERKS = #{dto.werks}</when>" +
            "  <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND WOL.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            "  <when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WS.STORAGE_AREA = #{dto.storageArea}</when>" +
            "  <when test='dto.storageNo!=null and dto.storageNo != \"\"'> AND WS.STORAGE_NO = #{dto.storageNo}</when>" +
            "  <when test='dto.movementType!=null and dto.movementType != \"\"'> AND WOL.MOVEMENT_TYPE = #{dto.movementType}</when>" +
            "  <when test='dto.endDate!=null and dto.endDate != \"\"'> AND TO_DATE(TO_CHAR(WOL.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-mm-dd hh24:mi:ss')</when>\n" +
            "  <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND TO_DATE(TO_CHAR(WOL.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-mm-dd hh24:mi:ss')</when>\n" +
            "  <when test='dto.postBeginDate!=null and dto.postBeginDate != \"\"'> AND (DN.POSTING_DATE &gt;= #{dto.beginDate} OR OWI.POSTING_DATE &gt;= #{dto.beginDate} OR OWO.POSTING_DATE &gt;= #{dto.beginDate} OR OOT.POSTING_DATE &gt;= #{dto.beginDate} OR OWM.POSTING_DATE &gt;= #{dto.beginDate})</when>\n" +
            "  <when test='dto.postEndDate!=null and dto.postEndDate != \"\"'> AND (DN.POSTING_DATE &lt;= #{dto.postEndDate} OR OWI.POSTING_DATE &lt;= #{dto.postEndDate} OR OWO.POSTING_DATE &lt;= #{dto.postEndDate} OR OOT.POSTING_DATE &lt;= #{dto.postEndDate} OR OWM.POSTING_DATE &lt;= #{dto.postEndDate})</when>\n" +
            "  <when test='dto.createUser!=null and dto.createUser != \"\"'> " +
            "     AND INSTR(WOL.USER_ID || ',' || SU.LAST_NAME, #{dto.createUser}, 1, 1) > 0 " +
            "  </when>" +
            "  <when test='dto.searchInfo!=null and dto.searchInfo != \"\"'> " +
            "     AND INSTR(MS.MATERIAL_NO || ',' || MS.MATERIAL_ZH_DESC || ',' || MS.MATERIAL_EN_DESC, #{dto.searchInfo}, 1, 1) > 0" +
            "  </when>" +
            "ORDER BY WOL.LOG_AUTO_ID DESC " +
            "</script>")
    List<Map<String, Object>> getWhsOperateLogList(@Param("dto") WhsOperateLogSearchDTO dto);

    @Select("SELECT RE.WERKS AS \"werks\",\n" +
            "       RE.MATERIAL_NO AS \"materialNo\",\n" +
            "       NVL(RE2.QUANTITY, 0) AS \"totalQuantity\"\n" +
            "FROM (\n" +
            "    SELECT WOL.WERKS, WOL.MATERIAL_NO\n" +
            "    FROM WHS_OPERATE_LOG WOL WHERE WERKS != 'CN01' AND REMARK = '库内移位'\n" +
            "    GROUP BY WOL.WERKS, WOL.MATERIAL_NO ORDER BY WOL.WERKS, WOL.MATERIAL_NO\n" +
            "    ) RE LEFT JOIN (\n" +
            "        SELECT WS.WERKS, WS.WHS_LOCATION_CODE, WSI.MATERIAL_NO, SUM(WSI.QUANTITY) AS \"QUANTITY\"\n" +
            "        FROM WHS_STORAGE_INVENTORY WSI\n" +
            "             LEFT JOIN WHS_STORAGE WS on WSI.UUID = WS.UUID\n" +
            "        WHERE WERKS != 'CN01' GROUP BY WS.WERKS, WS.WHS_LOCATION_CODE, WSI.MATERIAL_NO\n" +
            "        ) RE2 ON RE.WERKS = RE2.WERKS AND RE.MATERIAL_NO = RE2.MATERIAL_NO AND RE2.QUANTITY > 0 \n" +
            "WHERE 1=1 ORDER BY RE.WERKS, RE.MATERIAL_NO")
    List<Map<String, Object>> getWhsOperateLogs();

    @Select("SELECT WOL.LOG_ID AS \"logId\", \n" +
            "       WOL.DOCUMENT_TYPE AS \"documentType\", \n" +
            "       WOL.OPERATION_TYPE AS \"operationType\", \n" +
            "       WOL.QUANTITY  AS \"quantity\"\n" +
            "FROM WHS_OPERATE_LOG WOL\n" +
            "WHERE WOL.WERKS = #{werks} AND WOL.MATERIAL_NO = #{materialNo}\n" +
            "ORDER BY LOG_AUTO_ID ASC ")
    List<Map<String, Object>> getWOL(@Param("werks") String werks, @Param("materialNo") String materialNo);
}