package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sunda.spmsoversea.dto.OverseaInvCheckActionSearchDTO;
import com.sunda.spmsoversea.entity.OverseaInvCheckAction;
import com.sunda.spmsoversea.entity.OverseaInvCheckActionDtl;

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 org.springframework.stereotype.Component;

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

/**
 * <p>
 * 仓库盘盈盘亏 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-05-10
 */
@Mapper
@Component
public interface OverseaInvCheckActionMapper extends BaseMapper<OverseaInvCheckAction> {

    @Update("UPDATE OVERSEA_INV_CHECK_ACTION SET SPMS_STATUS = #{spmsStatus}, \n" +
            "SAP_VOUCHER_NUMBER = #{SAP_VOUCHER_NUMBER}, \n" +
            "SAP_VOUCHER_YEAR = #{SAP_VOUCHER_YEAR}, \n" +
            "POSTING_DATE = #{postingDate}, \n" +
            "REMARKS = #{remarks} " +
            "WHERE UUID = #{uuid} ")
    int updateInventoryCheckActionStatus(@Param("uuid") String uuid,
                                         @Param("spmsStatus") String spmsStatus,
                                         @Param("SAP_VOUCHER_NUMBER") String SAP_VOUCHER_NUMBER,
                                         @Param("SAP_VOUCHER_YEAR") String SAP_VOUCHER_YEAR,
                                         @Param("postingDate") String postingDate,
                                         @Param("remarks") String remarks);

    @Select("SELECT A.WERKS AS \"werks\",\n" +
            "       A.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       A.INVENTORY_ACTION_TYPE AS \"inventoryActionType\",\n" +
            "       AD.UUID AS \"uuid\",\n" +
            "       AD.TASK_ROW_ID AS \"taskRowId\",\n" +
            "       AD.MATERIAL_NO AS \"materialNo\",\n" +
            "       AD.INVENTORY_QUANTITY AS \"inventoryQuantity\",\n" +
            "       AD.CHECK_QUANTITY AS \"checkQuantity\",\n" +
            "       ABS(AD.DISCREPANCY) AS \"discrepancy\",\n" +
            "       AD.STORAGE_UUID AS \"storageUuid\"\n" +
            "FROM OVERSEA_INV_CHECK_ACTION_DTL AD\n" +
            "         LEFT JOIN OVERSEA_INV_CHECK_ACTION A ON AD.UUID = A.UUID\n" +
            "WHERE A.UUID = #{uuid} AND AD.DISCREPANCY != 0 ")
    List<Map<String, Object>> getInventoryCheckActionAndDtl(@Param("uuid") String uuid);


    /** 获取提交OA的单据序列号 */
    @Select("SELECT NVL(SUBSTR(MAX(ODN.OA_FIELD), -3, 3), '000') FROM OVERSEA_INV_CHECK_ACTION ODN\n" +
            "WHERE SUBSTR(ODN.SUBMIT_OA_DATE, 0, 7) = SUBSTR(#{submitOaDate}, 0, 7) ")
    String getOaOrderNo(@Param("submitOaDate") String submitOaDate);

    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (\n" +
            "    SELECT OICA.UUID AS \"uuid\",\n" +
            "           OICA.SPMS_ID AS \"spmsId\",\n" +
            "           OICA.INVENTORY_ACTION_TYPE AS \"inventoryActionType\",\n" +
            "           OICA.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "           OICA.OA_BIANHAO AS \"oaBianhao\",\n" +
            "        OICA.INVENTORY_CHECK_ID AS \"inventoryCheckId\",\n" +
            "        OICA.WERKS AS \"werks\",\n" +
            "        OICA.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "        OICA.INVENTORY_CHECK_CATEGORY AS \"inventoryCheckCategory\",\n" +
            "        OICA.INVENTORY_CHECK_MOLD AS \"inventoryCheckMold\",\n" +
            "        TO_CHAR(TO_DATE(OICA.CREATE_DATE, 'yyyymmdd'), 'yyyy-mm-dd') AS \"createDate\",\n" +
            "        OICA.INVENTORY_CHECK_RESULT AS \"inventoryCheckResult\",\n" +
            "        OICA.SPMS_STATUS AS \"spmsStatus\",\n" +
            "        OICA.REQUESTER AS \"createUserid\",\n" +
            "        SU.LAST_NAME AS \"createUsername\",\n" +
            "        OICAD.TASK_ROW_ID AS \"taskRowId\",\n" +
            "        OICAD.MATERIAL_NO AS \"materialNo\",\n" +
            "        OICAD.INVENTORY_QUANTITY AS \"inventoryQuantity\",\n" +
            "        OICAD.CHECK_QUANTITY AS \"checkQuantity\",\n" +
            "        OICAD.DISCREPANCY AS \"discrepancy\",\n" +
            "        MS.BASIC_UNIT AS \"basicUnit\",\n" +
            "        MS.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "        MS.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "        OICAD.STORAGE_UUID AS \"storageUuid\",\n" +
            "        WS.STORAGE_AREA AS \"storageArea\",\n" +
            "        WS.STORAGE_NO AS \"storageNo\"\n" +
            "    FROM OVERSEA_INV_CHECK_ACTION OICA\n" +
            "        LEFT JOIN OVERSEA_INV_CHECK_ACTION_DTL OICAD ON OICA.UUID = OICAD.UUID\n" +
            "        LEFT JOIN SPMS_USER SU ON OICA.REQUESTER = SU.WORK_CODE\n" +
            "        LEFT JOIN MATERIAL_SAP MS ON OICAD.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "        LEFT JOIN WHS_STORAGE WS ON OICAD.STORAGE_UUID = WS.UUID\n" +
            "    WHERE OICA.WERKS != 'CN01'\n" +
            "  <when test='dto.werks!=null and dto.werks != \"\"'> AND OICA.WERKS = #{dto.werks}</when>" +
            "  <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND OICA.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            "  <when test='dto.inventoryCheckCategory!=null and dto.inventoryCheckCategory != \"\"'> AND OICA.INVENTORY_CHECK_CATEGORY = #{dto.inventoryCheckCategory}</when>" +
            "  <when test='dto.inventoryCheckMold!=null and dto.inventoryCheckMold != \"\"'> AND OICA.INVENTORY_CHECK_MOLD = #{dto.inventoryCheckMold}</when>" +
            "  <when test='dto.inventoryActionType!=null and dto.inventoryActionType != \"\"'> AND OICA.INVENTORY_ACTION_TYPE = #{dto.inventoryActionType}</when>" +
            "  <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND OICA.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            "  <when test='dto.endDate!=null and dto.endDate != \"\"'> AND TO_DATE(OICA.CREATE_DATE, 'yyyymmdd') &lt;= TO_DATE(#{dto.endDate}, 'yyyy-mm-dd')</when>\n" +
            "  <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND TO_DATE(OICA.CREATE_DATE, 'yyyymmdd') &gt;= TO_DATE(#{dto.beginDate}, 'yyyy-mm-dd')</when>\n" +
            "  <when test='dto.storageArea!=null and dto.storageArea != \"\"'> AND WS.STORAGE_AREA = #{dto.storageArea}</when>" +
            "    ORDER BY TO_DATE(OICA.CREATE_DATE, 'yyyymmdd') DESC, TO_NUMBER(OICAD.TASK_ROW_ID)\n" +
            "    ) RE" +
            "</script>")
    /** 获取海外仓盘盈盘亏任务，带明细不分页报表 */
    List<Map<String, Object>> getOverseaInvCheckAction(@Param("dto") OverseaInvCheckActionSearchDTO dto);
    
    @Select("<script>" +
    		"    SELECT OICA.UUID AS \"uuid\",\n" +
            "        OICA.SPMS_ID AS \"spmsId\",\n" +
            "        OICA.INVENTORY_ACTION_TYPE AS \"inventoryActionType\",\n" +
            "        OICA.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "        OICA.OA_BIANHAO AS \"oaBianhao\",\n" +
            "        OICA.INVENTORY_CHECK_ID AS \"inventoryCheckId\",\n" +
            "        OICA.WERKS AS \"werks\",\n" +
            "        OICA.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "        OICA.INVENTORY_CHECK_CATEGORY AS \"inventoryCheckCategory\",\n" +
            "        OICA.INVENTORY_CHECK_MOLD AS \"inventoryCheckMold\",\n" +
            "        TO_CHAR(TO_DATE(OICA.CREATE_DATE, 'yyyymmdd'), 'yyyy-mm-dd') AS \"createDate\",\n" +
            "        OICA.INVENTORY_CHECK_RESULT AS \"inventoryCheckResult\",\n" +
            "        OICA.SPMS_STATUS AS \"spmsStatus\",\n" +
            "        OICA.REQUESTER AS \"createUserid\"\n" +
            "    FROM OVERSEA_INV_CHECK_ACTION OICA\n" +
            "	 WHERE OICA.SPMS_ID = #{spmsId} \n" +
            "</script>")
	Map<String, Object> selectInvCheckByNo(@Param("spmsId") String spmsId);
    
    @Select("<script>" +
    		"    SELECT OICA.UUID AS \"uuid\",\n" +
            "        OICA.TASK_ROW_ID AS \"taskRowId\",\n" +
            "        OICA.MATERIAL_NO AS \"materialNo\",\n" +
            "        OICA.BOX_NOTE AS \"boxNote\",\n" +
            "        OICA.INVENTORY_QUANTITY AS \"inventoryQuantity\",\n" +
            "        OICA.CHECK_QUANTITY AS \"checkQuantity\",\n" +
            "        OICA.DISCREPANCY AS \"discrepancy\",\n" +
            "        OICA.BASIC_UNIT AS \"basicUnit\",\n" +
            "        OICA.STORAGE_UUID AS \"storageUuid\",\n" +
            "        OICA.REMARKS AS \"remarks\",\n" +
            "        OICA.RELATE_ACCOUNT AS \"relateAccount\",\n" +
            "        OICA.VOUCHER_ITEM_TEXT AS \"voucherItemText\"\n" +
            "    FROM OVERSEA_INV_CHECK_ACTION_DTL OICA\n" +
            "	 WHERE OICA.UUID = #{uuid} \n" +
            "</script>")
	List<Map<String, Object>> selectInvCheckDltList(@Param("uuid") String uuid);
    
    @Update("<script>" +
    		"<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"begin\" close=\";end;\" separator=\";\"> \n" + 
    		"	update OVERSEA_INV_CHECK_ACTION_DTL set \n" +
    		"		WAERS = #{item.waers,jdbcType=VARCHAR}, \n" +
    		"		DMBTR = #{item.dmbtr,jdbcType=VARCHAR} \n" +
    		"	where UUID = #{item.uuid,jdbcType=VARCHAR} \n" +
    		"		and TASK_ROW_ID = #{item.taskRowId,jdbcType=VARCHAR} \n" +
    		"</foreach>" +
    		"</script>")
	int updateBatchDtl(List<OverseaInvCheckActionDtl> list);
    
    @Select("<script>" +
    		"    SELECT OICA.UUID AS \"uuid\",\n" +
            "        OICA.SPMS_ID AS \"spmsId\",\n" +
            "        OICA.INVENTORY_ACTION_TYPE AS \"inventoryActionType\",\n" +
            "        OICA.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "        OICA.OA_BIANHAO AS \"oaBianhao\",\n" +
            "        OICA.INVENTORY_CHECK_ID AS \"inventoryCheckId\",\n" +
            "        OICA.WERKS AS \"werks\",\n" +
            "        OICA.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "        OICA.INVENTORY_CHECK_CATEGORY AS \"inventoryCheckCategory\",\n" +
            "        OICA.INVENTORY_CHECK_MOLD AS \"inventoryCheckMold\",\n" +
            "        TO_CHAR(TO_DATE(OICA.CREATE_DATE, 'yyyymmdd'), 'yyyy-mm-dd') AS \"createDate\",\n" +
            "        OICA.INVENTORY_CHECK_RESULT AS \"inventoryCheckResult\",\n" +
            "        OICA.SPMS_STATUS AS \"spmsStatus\",\n" +
            "        OICA.REQUESTER AS \"createUserid\",\n" +
            "        OICA.REMARKS AS \"remarks\",\n" +
            "        SU.LAST_NAME AS \"createUserName\",\n" +
            "        SU.DEPT_NAME AS \"departName\" \n" +
            "    FROM OVERSEA_INV_CHECK_ACTION OICA\n" +
            "		LEFT JOIN SPMS_USER SU ON OICA.REQUESTER = SU.WORK_CODE \n" +
            "	 WHERE OICA.UUID = #{uuid} \n" +
            "</script>")
	Map<String, Object> queryInvCheckByUuid(@Param("uuid") String uuid);

}