package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaOtherSearchDTO;
import com.sunda.spmsoversea.entity.OverseaOtherDtl;
import com.sunda.spmsoversea.entity.OverseaOtherTask;
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-12-27
 */
@Mapper
public interface OverseaOtherTaskMapper extends BaseMapper<OverseaOtherTask> {

    /** 获取其他出入库表头分页列表 */
    @Select("<script>" +
            "SELECT OOT.UUID AS \"uuid\",\n" +
            "       OOT.OPERATE_TITLE AS \"operateTitle\",\n" +
            "       OOT.SPMS_ID AS \"spmsId\",\n" +
            "       OOT.MOVEMENT_TYPE AS \"movementType\",\n" +
            "       OOT.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OOT.WERKS AS \"werks\",\n" +
            "       OOT.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       OOT.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OOT.CREATE_DATE AS \"createDate\",\n" +
            "       OOT.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       OOT.CREATE_USERID AS \"createUserid\",\n" +
            "       OOT.COST_CENTER AS \"costCenter\",\n" +
            "       OOT.REMARKS AS \"remarks\",\n" +
            "       OOT.COMMENTS AS \"comments\",\n" +
            "       OOT.PURCHASE_GROUP AS \"purchaseGroup\",\n" +
            "       OOT.PURCHASE_ORG AS \"purchaseOrg\",\n" +
            "       OOT.POSTING_DATE AS \"postingDate\",\n" +
            "       OOT.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       OOT.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OOT.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       OOT.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       OOT.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       OOT.DATA_VERSION AS \"dataVersion\",\n" +
            "       OOT.CREATE_TIME AS \"createTime\",\n" +
            "       OOT.UPDATE_TIME AS \"updateTime\",\n" +
            "       OOT.FACTORY_TYPE AS \"factoryType\",\n" +
            "       OOT.CANCEL_REASON AS \"cancelReason\",\n" +
            "       U.LAST_NAME AS \"username\"\n" +
            "FROM OVERSEA_OTHER_TASK OOT\n" +
            "    LEFT JOIN SPMS_USER U ON OOT.CREATE_USERID = U.WORK_CODE\n" +
            "WHERE 1=1\n" +
            "  <when test='dto.spmsId!=null and dto.spmsId != \"\"'> AND OOT.SPMS_ID = #{dto.spmsId}</when>" +
            "  <when test='dto.movementType!=null and dto.movementType != \"\"'> AND OOT.MOVEMENT_TYPE = #{dto.movementType}</when>" +
            "  <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND OOT.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            "  <when test='dto.werks!=null and dto.werks != \"\"'> AND OOT.WERKS = #{dto.werks}</when>" +
            "  <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND OOT.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            "  <when test='dto.createDateBegin!=null and dto.createDateBegin != \"\"'> AND TO_DATE(OOT.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(OOT.CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{dto.createDateEnd}, 'yyyy-MM-dd') </when>" +
            " <when test='dto.werksList != null'> AND OOT.WERKS in \n" +
            "    <foreach item='item' index='index' collection='dto.werksList' open='(' separator=',' close=')'>\n" +
            "       #{item} \n" +
            "    </foreach>\n" +
            " </when>\n" +
            "ORDER BY OOT.SPMS_STATUS ASC, OOT.UPDATE_TIME DESC" +
            "</script>")
    List<Map<String, Object>> getOtherTaskPage(IPage page, @Param("dto")OverseaOtherSearchDTO dto);

    /** 获取其他出入库表头详细信息 */
    @Select("SELECT OOT.UUID AS \"uuid\",\n" +
            "       OOT.OPERATE_TITLE AS \"operateTitle\",\n" +
            "       OOT.SPMS_ID AS \"spmsId\",\n" +
            "       OOT.MOVEMENT_TYPE AS \"movementType\",\n" +
            "       OOT.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OOT.WERKS AS \"werks\",\n" +
            "       OOT.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       OOT.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OOT.CREATE_DATE AS \"createDate\",\n" +
            "       OOT.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       OOT.CREATE_USERID AS \"createUserid\",\n" +
            "       OOT.COST_CENTER AS \"costCenter\",\n" +
            "       OOT.REMARKS AS \"remarks\",\n" +
            "       OOT.COMMENTS AS \"comments\",\n" +
            "       OOT.PURCHASE_GROUP AS \"purchaseGroup\",\n" +
            "       OOT.PURCHASE_ORG AS \"purchaseOrg\",\n" +
            "       OOT.POSTING_DATE AS \"postingDate\",\n" +
            "       OOT.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       OOT.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OOT.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       OOT.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       OOT.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       OOT.DATA_VERSION AS \"dataVersion\",\n" +
            "       OOT.CREATE_TIME AS \"createTime\",\n" +
            "       OOT.UPDATE_TIME AS \"updateTime\",\n" +
            "       OOT.CANCEL_REASON AS \"cancelReason\",\n" +
            "       OOT.OTHER_WERKS AS \"otherWerks\",\n" +
            "       OOT.OTHER_WHS_LOCATION_CODE AS \"otherWhsLocationCode\",\n" +
            "       U.LAST_NAME AS \"username\"\n" +
            "FROM OVERSEA_OTHER_TASK OOT\n" +
            "    LEFT JOIN SPMS_USER U ON OOT.CREATE_USERID = U.WORK_CODE \n" +
            "WHERE OOT.UUID = #{uuid} ")
    Map<String, Object> getOtherTaskInfo(@Param("uuid") String uuid);

    @Select("SELECT MAX(TO_NUMBER(OOD.ITEM)) FROM OVERSEA_OTHER_DTL OOD WHERE OOD.UUID = #{uuid} ")
    Integer getOtherTaskMaxItemNumber(@Param("uuid") String uuid);

    /** 报废出库551;零价格出库Z55;零价格入库Z56;收货多收Z16;串货调整309;不可抗力出库Z13;IT专用出库Z47;期初库存增加561;期初库存减少562; 提交SAP表头信息结构 */
    @Select("SELECT OOT.WERKS AS \"WERKS\",\n" +
            "       OOT.WHS_LOCATION_CODE AS \"LGORT\",\n" +
            "       REPLACE(OOT.CREATE_DATE, '-', '') AS \"BLDAT\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"BUDAT\",\n" +
            "       OOT.MOVEMENT_TYPE AS \"BWART\",\n" +
            "       OOT.SPMS_ID AS \"XBLNR\",\n" +
            //"       OOT.SPMS_ID AS \"SPMSID\",\n" +
            "       OOT.SPMS_ID ||' '|| OOT.REMARKS AS \"BKTXT\"\n" +
            "FROM OVERSEA_OTHER_TASK OOT\n" +
            "WHERE OOT.UUID = #{uuid} ")
    Map<String, Object> otherTaskHead(@Param("uuid") String uuid, @Param("postingDate") String postingDate);

    /** 报废出库551;零价格出库Z55;零价格入库Z56;收货多收Z16;串货调整309;不可抗力出库Z13;IT专用出库Z47;期初库存增加561;期初库存减少562; 提交SAP行项目信息结构 */
    /** 行项目请求SAP结构，增加两个字段 EXBWR 本币金额，AUFNR内部订单号 */
    /** 零价格出库Z55;零价格入库Z56 金额默认为空 2022-12-02 xzp add**/
    @Select("SELECT OOD.ITEM AS \"ZEILE\",\n" +
            "       OOD.MATERIAL_NO AS \"MATNR\",\n" +
            "       TO_CHAR(OOD.ACTUAL_QTY_BASIC_UNIT, 'fm9999999990.000') AS \"MENGE\",\n" +
            "       OOD.BASIC_UNIT AS \"MEINS\",\n" +
            "       OOD.SUPPLIER_NO AS \"LIFNR\",\n" +
            "       #{costCenter} AS \"KOSTL\",\n" +
            "       OOD.WERKS AS \"WERKS\",\n" +
            "       OOD.WHS_LOCATION_CODE AS \"LGORT\",\n" +
            "       OOD.REMARKS AS \"SGTXT\",\n" +
            "       OOD.MATERIAL_NO_ADJUSTED AS \"ZSHWL\",\n" +
            "       OOD.INTERNAL_ORDER_NUMBER AS \"AUFNR\",\n" +
            "       (CASE WHEN OOT.MOVEMENT_TYPE = 'Z55' OR OOT.MOVEMENT_TYPE = 'Z56' THEN '' ELSE TO_CHAR(OOD.LOCAL_CURRENCY) END) AS \"EXBWR\"\n" +
            "FROM OVERSEA_OTHER_DTL OOD\n" +
            "    LEFT JOIN OVERSEA_OTHER_TASK OOT ON OOD.UUID = OOT.UUID\n" +
            "WHERE OOD.UUID = #{uuid} \n" +
            "  AND OOD.ACTUAL_QTY_BASIC_UNIT > 0\n" +
            "ORDER BY TO_NUMBER(OOD.ITEM) ")
    List<Map<String, Object>> otherDtlItem(@Param("uuid") String uuid, @Param("costCenter") String costCenter);

    /** 其他出入库从SAP撤回凭证 */
    @Select("SELECT OOT.SAP_VOUCHER_NUMBER AS \"I_MBLNR\",\n" +
            "       OOT.SAP_VOUCHER_YEAR AS \"I_MJAHR\",\n" +
            "       REPLACE(#{cancelPostingDate}, '-', '') AS \"I_BUDAT\"\n" +
            "FROM OVERSEA_OTHER_TASK OOT\n" +
            "WHERE OOT.UUID = #{uuid} ")
    Map<String, Object> dataHeadCancel(@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_OTHER_DTL set \n" +
    		"		WAERS = #{item.waers,jdbcType=VARCHAR}, \n" +
    		"		DMBTR = #{item.dmbtr,jdbcType=VARCHAR} \n" +
    		"	where UUID = #{item.uuid,jdbcType=VARCHAR} \n" +
    		"		and ITEM = #{item.item,jdbcType=VARCHAR} \n" +
    		"</foreach>" +
    		"</script>")
	int updateBatchDtl(List<OverseaOtherDtl> list);

}