package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaWhsOutQueryDTO;
import com.sunda.spmsoversea.entity.OverseaWhsOut;
import com.sunda.spmsoversea.entity.OverseaWhsOutDtl;

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 2021-09-22
 */
@Mapper
public interface OverseaWhsOutMapper extends BaseMapper<OverseaWhsOut> {

    @Select("<script>" +
            "SELECT OWO.AUTO_ID_WHS_OUT AS \"autoIdWhsOut\",\n" +
            "       OWO.UUID_WHS_OUT AS \"uuidWhsOut\",\n" +
            "       OWO.SPMS_ID AS \"spmsId\",\n" +
            "       OWO.CREATE_DATE AS \"createDate\",\n" +
            "       OWO.MOVEMENT_TYPE AS \"movementType\",\n" +
            "       OWO.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OWO.CREATE_USERID AS \"createUserid\",\n" +
            "       SU.LAST_NAME AS \"createUsername\",\n" +
            "       OWO.WERKS AS \"werks\",\n" +
            "       OWO.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       OWO.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OWO.RELATE_DOCUMENT AS \"relateDocument\",\n" +
            "       ORQ.COMMENTS AS \"overseaRequisitionSubject\",\n" +
            "       OWO.COST_CENTER AS \"costCenter\",\n" +
            "       OWO.COST_CENTER_NAME AS \"costCenterName\",\n" +
            "       OWO.DATA_VERSION AS \"dataVersion\",\n" +
            "       OWO.REMARKS AS \"remarks\",\n" +
            "       OWO.COMMENTS AS \"comments\",\n" +
            "       OWO.POSTING_DATE AS \"postingDate\",\n" +
            "       OWO.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       OWO.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OWO.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       OWO.SAP_CANCEL_VOUCHER_NUMBER AS \"sapCencelVoucherNumber\",\n" +
            "       OWO.SAP_CANCEL_VOUCHER_YEAR AS \"sapCancelVoucherYear\",\n" +
            "       OWO.CREATE_TIME AS \"createTime\",\n" +
            "       OWO.OA_NUMBER AS \"oaNumber\",\n" +
            "       OWO.CANCEL_REASON AS \"cancelReason\",\n" +
            "       OWO.UPDATE_TIME AS \"updateTime\",\n" +
            "       OWO.PDA_SN AS \"pdaSN\",\n" +
            "       OWO.PDA_TIME AS \"pdaTime\",\n" +
            "       OWO.DOCUMENT_TYPE AS \"documentType\"\n" +
            "FROM OVERSEA_WHS_OUT OWO\n" +
            "    LEFT JOIN SPMS_USER SU ON OWO.CREATE_USERID = SU.WORK_CODE " +
            "    LEFT JOIN OVERSEA_REQUISITION ORQ ON OWO.RELATE_DOCUMENT = ORQ.SPMS_ID \n" +
            "WHERE 1=1 \n" +
            " <when test='dto.uuidWhsOut!=null and dto.uuidWhsOut != \"\"'> AND OWO.UUID_WHS_OUT = #{dto.uuidWhsOut}</when>" +
            " <when test='dto.spmsId!=null and dto.spmsId != \"\"'> AND OWO.SPMS_ID = #{dto.spmsId}</when>" +
            " <when test='dto.relateDocument!=null and dto.relateDocument != \"\"'> AND OWO.RELATE_DOCUMENT = #{dto.relateDocument}</when>" +
            " <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND OWO.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            " <when test='dto.werks!=null and dto.werks != \"\"'> AND OWO.WERKS = #{dto.werks}</when>" +
            " <when test='dto.workshopCode!=null and dto.workshopCode != \"\"'> AND OWO.WORKSHOP_CODE = #{dto.workshopCode}</when>" +
            " <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND OWO.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            " <when test='dto.movementType!=null and dto.movementType != \"\"'> AND OWO.MOVEMENT_TYPE = #{dto.movementType}</when>" +
            " <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND OWO.CREATE_DATE &gt;= #{dto.beginDate}</when>" +
            " <when test='dto.endDate!=null and dto.endDate != \"\"'> AND OWO.CREATE_DATE &lt;= #{dto.endDate}</when>" +
            " <when test='dto.documentType!=null and dto.documentType != \"\"'> AND OWO.DOCUMENT_TYPE = #{dto.documentType}</when>" +
            "  <when test='dto.createUser!=null and dto.createUser != \"\"'> " +
            "     AND INSTR(OWO.CREATE_USERID || ',' || SU.LAST_NAME, #{dto.createUser}, 1, 1) > 0 " +
            "  </when>" +
            " <when test='dto.werksList != null'> AND OWO.WERKS in \n" +
            "    <foreach item='item' index='index' collection='dto.werksList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "    </foreach>\n" +
            " </when>\n" +
            "  <when test='dto.comments!=null and dto.comments != \"\"'> AND INSTR(OWO.COMMENTS, #{dto.comments}, 1, 1) > 0 </when>" +
            " ORDER BY OWO.SPMS_STATUS DESC, OWO.UPDATE_TIME DESC " +
            "</script>")
    List<Map<String, Object>> getWhsOutPage(IPage<OverseaWhsOut> page, @Param("dto") OverseaWhsOutQueryDTO dto);

    @Select("SELECT OWO.WHS_LOCATION_CODE AS \"LGORT\",\n" +
            "       OWO.WERKS AS \"WERKS\",\n" +
            "       REPLACE(OWO.CREATE_DATE, '-', '') AS \"BLDAT\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"BUDAT\",\n" +
            "       OWO.MOVEMENT_TYPE AS \"BWART\",\n" +
            "       OWO.OA_ID AS \"XBLNR\",\n" +
            "       OWO.SAP_KEY_CODE AS \"SAP_KEY_CODE\",\n" +
            "       OWO.RELATE_DOCUMENT AS \"BKTXT\",\n" +
            "       OWO.SPMS_ID AS \"FRBNR\"\n" +
            "FROM OVERSEA_WHS_OUT OWO\n" +
            "WHERE OWO.UUID_WHS_OUT = #{uuidWhsOut} ")
    Map<String, Object> dataHeadWhsOut(@Param("uuidWhsOut") String uuidWhsOut, @Param("postingDate") String postingDate);

    @Select("SELECT TO_CHAR(OWOD.ITEM) AS \"ZEILE\",\n" +
            "       OWOD.MATERIAL_NO AS \"MATNR\",\n" +
            "       TO_CHAR(OWOD.ACTUAL_QTY_BASIC_UNIT) AS \"MENGE\",\n" +
            "       OWOD.BASIC_UNIT AS \"MEINS\",\n" +
            "       '' AS \"LIFNR\",\n" +
            "       OWO.COST_CENTER AS \"KOSTL\",\n" +
            "       CONCAT(OWO.SPMS_ID, NVL(CONCAT('+', OWO.REMARKS), '')) AS \"SGTXT\",\n" +
            "       OWOD.WERKS AS \"WERKS\",\n" +
            "       substr(OWOD.PROJECT_OA_NUMBER,1,instr(OWOD.PROJECT_OA_NUMBER,'-')-1) AS \"AUFNR\",\n" +
            "       OWOD.WHS_LOCATION_CODE AS \"LGORT\"\n" +
            "FROM OVERSEA_WHS_OUT_DTL OWOD\n" +
            "    LEFT JOIN OVERSEA_WHS_OUT OWO ON OWOD.UUID_WHS_OUT = OWO.UUID_WHS_OUT\n" +
            "WHERE OWOD.UUID_WHS_OUT = #{uuidWhsOut}\n" +
            "  AND OWOD.ACTUAL_QTY_BASIC_UNIT > 0\n" +
            "ORDER BY OWOD.ITEM ASC ")
    List<Map<String, Object>> dataItemWhsOut(@Param("uuidWhsOut") String uuidWhsOut);

    @Select("SELECT OWO.SAP_VOUCHER_NUMBER AS \"I_MBLNR\",\n" +
            "       OWO.SAP_VOUCHER_YEAR AS \"I_MJAHR\",\n" +
            "       REPLACE(#{cancelPostingDate}, '-', '') AS \"I_BUDAT\"\n" +
            "FROM OVERSEA_WHS_OUT OWO\n" +
            "WHERE OWO.UUID_WHS_OUT = #{uuidWhsOut} ")
    Map<String, Object> dataHeadCancelWhsOut(@Param("uuidWhsOut") String uuidWhsOut,
                                             @Param("cancelPostingDate") String cancelPostingDate);
    
    
    @Update("<script>" +
    		"<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"begin\" close=\";end;\" separator=\";\"> \n" + 
    		"	update OVERSEA_WHS_OUT_DTL set \n" +
    		"		WAERS = #{item.waers,jdbcType=VARCHAR}, \n" +
    		"		DMBTR = #{item.dmbtr,jdbcType=VARCHAR} \n" +
    		"	where UUID_WHS_OUT = #{item.uuidWhsOut,jdbcType=VARCHAR} \n" +
    		"		and ITEM = #{item.item} \n" +
    		"</foreach>" +
    		"</script>")
	int updateBatchDtl(List<OverseaWhsOutDtl> list);
}