package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaWhsMoveSearchDTO;
import com.sunda.spmsoversea.entity.OverseaWhsMove;
import com.sunda.spmsoversea.entity.OverseaWhsMoveDtl;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

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

/**
 * <p>
 * 海外仓调拨业务 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2022-01-20
 */
@Mapper
public interface OverseaWhsMoveMapper extends BaseMapper<OverseaWhsMove> {

    @Select("<script>" +
            "SELECT OWM.UUID AS \"uuid\",\n" +
            "       OWM.WHS_MOVE_NO AS \"whsMoveNo\",\n" +
            "       OWM.SUBJECT AS \"subject\",\n" +
            "       OWM.CREATE_DATE AS \"createDate\",\n" +
            "       OWM.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OWM.MOVEMENT_TYPE AS \"movementType\",\n" +
            "       OWM.WERKS_MOVE_OUT AS \"werksMoveOut\",\n" +
            "       OWM.WHS_LOCATION_CODE_MOVE_OUT AS \"whsLocationCodeMoveOut\",\n" +
            "       OWM.REMARK_MOVE_OUT AS \"remarkMoveOut\",\n" +
            "       OWM.WERKS_MOVE_IN AS \"werksMoveIn\",\n" +
            "       OWM.WHS_LOCATION_CODE_MOVE_IN AS \"whsLocationCodeMoveIn\",\n" +
            "       OWM.REMARK_MOVE_IN AS \"remarkMoveIn\",\n" +
            "       OWM.RELATE_DOCUMENT AS \"relateDocument\",\n" +
            "       OWM.POSTING_DATE AS \"postingDate\",\n" +
            "       OWM.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       OWM.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OWM.CREATE_USERID AS \"createUserid\",\n" +
            "       SU.LAST_NAME AS \"createUsername\",\n" +
            "       OWM.DATA_VERSION AS \"dataVersion\"\n" +
            "FROM OVERSEA_WHS_MOVE OWM\n" +
            "    LEFT JOIN SPMS_USER SU ON OWM.CREATE_USERID = SU.WORK_CODE\n" +
            "WHERE OWM.SPMS_STATUS != '0'\n" +
            "  <when test='dto.whsMoveNo!=null and dto.whsMoveNo != \"\"'> AND OWM.WHS_MOVE_NO = #{dto.whsMoveNo}</when>" +
            "  <when test='dto.subject!=null and dto.subject != \"\"'> AND INSTR(OWM.SUBJECT, #{dto.subject}, 1, 1) > 0 </when>" +
            "  <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND OWM.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            "  <when test='dto.werksMoveOut!=null and dto.werksMoveOut != \"\"'> AND OWM.WERKS_MOVE_OUT = #{dto.werksMoveOut}</when>" +
            "  <when test='dto.whsLocationCodeMoveOut!=null and dto.whsLocationCodeMoveOut != \"\"'> AND OWM.WHS_LOCATION_CODE_MOVE_OUT = #{dto.whsLocationCodeMoveOut}</when>" +
            "  <when test='dto.werksMoveIn!=null and dto.werksMoveIn != \"\"'> AND OWM.WERKS_MOVE_IN = #{dto.werksMoveIn}</when>" +
            "  <when test='dto.whsLocationCodeMoveIn!=null and dto.whsLocationCodeMoveIn != \"\"'> AND OWM.WHS_LOCATION_CODE_MOVE_IN = #{dto.whsLocationCodeMoveIn}</when>" +
            "  <when test='dto.createDateBegin!=null and dto.createDateBegin != \"\"'> AND TO_DATE(OWM.CREATE_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{dto.createDateBegin}, 'yyyy-MM-dd') </when>" +
            "  <when test='dto.createDateEnd!=null and dto.createDateEnd != \"\"'> AND TO_DATE(OWM.CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.createDateEnd}, 'yyyy-MM-dd') </when>" +
            "  <when test='dto.createUser!=null and dto.createUser != \"\"'> " +
            "     AND INSTR(OWM.CREATE_USERID || ',' || SU.LAST_NAME, #{dto.createUser}, 1, 1) > 0 " +
            "  </when>" +
            "ORDER BY OWM.SPMS_STATUS, OWM.CREATE_DATE DESC" +
            "</script>")
    List<Map<String, Object>> getOverseaWhsMovePage(IPage iPage, @Param("dto") OverseaWhsMoveSearchDTO dto);

    @Select("SELECT OWM.UUID AS \"uuid\",\n" +
            "       OWM.WHS_MOVE_NO AS \"whsMoveNo\",\n" +
            "       OWM.SUBJECT AS \"subject\",\n" +
            "       OWM.CREATE_DATE AS \"createDate\",\n" +
            "       OWM.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OWM.MOVEMENT_TYPE AS \"movementType\",\n" +
            "       OWM.WERKS_MOVE_OUT AS \"werksMoveOut\",\n" +
            "       OWM.WHS_LOCATION_CODE_MOVE_OUT AS \"whsLocationCodeMoveOut\",\n" +
            "       OWM.REMARK_MOVE_OUT AS \"remarkMoveOut\",\n" +
            "       OWM.WERKS_MOVE_IN AS \"werksMoveIn\",\n" +
            "       OWM.WHS_LOCATION_CODE_MOVE_IN AS \"whsLocationCodeMoveIn\",\n" +
            "       OWM.REMARK_MOVE_IN AS \"remarkMoveIn\",\n" +
            "       OWM.RELATE_DOCUMENT AS \"relateDocument\",\n" +
            "       OWM.POSTING_DATE AS \"postingDate\",\n" +
            "       OWM.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       OWM.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OWM.CREATE_USERID AS \"createUserid\",\n" +
            "       SU.LAST_NAME AS \"createUsername\",\n" +
            "       OWM.DATA_VERSION AS \"dataVersion\"\n" +
            "FROM OVERSEA_WHS_MOVE OWM\n" +
            "    LEFT JOIN SPMS_USER SU ON OWM.CREATE_USERID = SU.WORK_CODE\n" +
            "WHERE OWM.SPMS_STATUS != '0' AND OWM.UUID = #{uuid}")
    Map<String, Object> getOverseaWhsMoveByUuid(@Param("uuid") String uuid);

    @Select("SELECT OWM.WERKS_MOVE_OUT AS \"WERKS\",\n" +
            "       OWM.WHS_LOCATION_CODE_MOVE_OUT AS \"LGORT\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"BUDAT\",\n" +
            "       REPLACE(OWM.CREATE_DATE, '-', '') AS \"BLDAT\",\n" +
            "       OWM.MOVEMENT_TYPE AS \"BWART\",\n" +
            "       OWM.WHS_MOVE_NO AS \"XBLNR\",\n" +
            //"       OWM.WHS_MOVE_NO AS \"SPMSID\",\n" +
            "       OWM.WHS_MOVE_NO || OWM.REMARK_MOVE_OUT AS \"BKTXT\"\n" +
            "FROM OVERSEA_WHS_MOVE OWM\n" +
            "WHERE OWM.UUID = #{uuid} ")
    Map<String, Object> submitSapHead(@Param("uuid") String uuid, @Param("postingDate") String postingDate);

    @Select("SELECT OWMD.ITEM_NO AS \"ZEILE\",\n" +
            "       OWMD.MATERIAL_NO AS \"MATNR\",\n" +
            "       TO_CHAR(OWMD.ACTUAL_QTY_MOVE_OUT)  AS \"MENGE\",\n" +
            "       MS.BASIC_UNIT  AS \"MEINS\",\n" +
            "       '' AS \"LIFNR\",\n" +
            "       '' AS \"KOSTL\",\n" +
            "       OWM.WERKS_MOVE_IN AS \"WERKS\",\n" +
            "       OWM.WHS_LOCATION_CODE_MOVE_IN AS \"LGORT\",\n" +
            "       OWM.REMARK_MOVE_OUT AS \"SGTXT\"\n" +
            "FROM OVERSEA_WHS_MOVE_DTL OWMD\n" +
            "    LEFT JOIN OVERSEA_WHS_MOVE OWM ON OWMD.UUID = OWM.UUID\n" +
            "    LEFT JOIN MATERIAL_SAP MS ON OWMD.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "WHERE OWMD.UUID = #{uuid} AND OWMD.ACTUAL_QTY_MOVE_OUT > 0 ORDER BY TO_NUMBER(OWMD.ITEM_NO) ")
    List<Map<String, Object>> submitSapItem(@Param("uuid") String uuid);

    @Select("SELECT OWM.SAP_VOUCHER_NUMBER AS \"I_MBLNR\",\n" +
            "       OWM.SAP_VOUCHER_YEAR AS \"I_MJAHR\",\n" +
            "       REPLACE(#{cancelPostingDate}, '-', '') AS \"I_BUDAT\"\n" +
            "FROM OVERSEA_WHS_MOVE OWM\n" +
            "WHERE OWM.UUID = #{uuid} ")
    Map<String, Object> dataHeadCancelAnomaly(@Param("uuid") String uuid, @Param("cancelPostingDate") String cancelPostingDate);
    
    @Update("<script>" +
    		"<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"begin\" close=\";end;\" separator=\";\"> \n" + 
    		"	update OVERSEA_WHS_MOVE_DTL set \n" +
    		"		WAERS = #{item.waers,jdbcType=VARCHAR}, \n" +
    		"		DMBTR = #{item.dmbtr,jdbcType=VARCHAR} \n" +
    		"	where UUID = #{item.uuid,jdbcType=VARCHAR} \n" +
    		"		and ITEM_NO = #{item.itemNo,jdbcType=VARCHAR} \n" +
    		"</foreach>" +
    		"</script>")
	int updateBatchDtl(List<OverseaWhsMoveDtl> list);
}