package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmswms.entity.WhsMove;
import com.sunda.spmswms.entity.WhsMoveDtl;

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-05-31
 */
@Mapper
public interface WhsMoveMapper extends BaseMapper<WhsMove> {

    @Select("<script>\n" +
            "SELECT ROWNUM AS \"rowId\", RE.*\n" +
            "FROM (SELECT WM.WHS_MOVE_UUID AS \"whsMoveUuid\",\n" +
            "             WM.WHS_MOVE_NO AS \"whsMoveNo\",\n" +
            "             WM.MOVE_TYPE AS \"moveType\",\n" +
            "             WM.GOODS_TYPE AS \"goodsType\",\n" +
            "             WM.WERKS_MOVE_OUT AS \"werksMoveOut\",\n" +
            "             WM.WHS_LOCATION_CODE_MOVE_OUT AS \"whsLocationCodeMoveOut\",\n" +
            "             WM.MOVE_OUT_REMARK AS \"moveOutRemark\",\n" +
            "             WM.WERKS_MOVE_IN AS \"werksMoveIn\",\n" +
            "             WM.WHS_LOCATION_CODE_MOVE_IN AS \"whsLocationCodeMoveIn\",\n" +
            "             WM.MOVE_IN_REMARK AS \"moveInRemark\",\n" +
            "             WM.CREATE_DATE AS \"createDate\",\n" +
            "             WM.POSTING_DATE AS \"postingDate\",\n" +
            "             WM.SAP_VOUCHER AS \"sapVoucher\",\n" +
            "             WM.CREATE_USERID AS \"createUserId\",\n" +
            "             U.LAST_NAME AS \"createUsername\",\n" +
            "             WM.SPMS_STATUS AS \"spmsStatus\",\n" +
            "             WM.CREATE_TIME AS \"createTime\",\n" +
            "             WM.REMARK AS \"remark\"\n" +
            "      FROM WHS_MOVE WM LEFT JOIN SPMS_USER U ON WM.CREATE_USERID = U.WORK_CODE\n" +
            "      WHERE 1=1 \n" +
            "      <when test='whsMoveNo!=null'> AND WM.WHS_MOVE_NO = #{whsMoveNo} </when> \n" +
            "      <when test='moveType!=null'> AND WM.MOVE_TYPE = #{moveType} </when> \n" +
            "      <when test='goodsType!=null'> AND WM.GOODS_TYPE = #{goodsType} </when> \n" +
            "      <when test='werksMoveOut!=null'> AND WM.WERKS_MOVE_OUT = #{werksMoveOut} </when> \n" +
            "      <when test='whsLocationCodeMoveOut!=null'> AND WM.WHS_LOCATION_CODE_MOVE_OUT = #{whsLocationCodeMoveOut} </when> \n" +
            "      <when test='werksMoveIn!=null'> AND WM.WERKS_MOVE_IN = #{werksMoveIn} </when> \n" +
            "      <when test='whsLocationCodeMoveIn!=null'> AND WM.WHS_LOCATION_CODE_MOVE_IN = #{whsLocationCodeMoveIn} </when> \n" +
            "      <when test='beginDate!=null'> AND TO_DATE(WM.CREATE_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{beginDate}, 'yyyy-MM-dd') </when> \n" +
            "      <when test='endDate!=null'> AND TO_DATE(WM.CREATE_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{endDate}, 'yyyy-MM-dd') </when> \n" +
            "      <when test='materialNo!=null'> AND WM.WHS_MOVE_UUID IN (" +
            "           SELECT WMD.WHS_MOVE_UUID FROM WHS_MOVE_DTL WMD WHERE MATERIAL_NO = #{materialNo} " +
            "       ) </when> \n" +
            "      <when test='spmsStatisList != null'> AND WM.SPMS_STATUS IN \n" +
            "           <foreach item='item' index='index' collection='spmsStatisList' open='(' separator=',' close=')'>\n" +
            "                #{item} \n" +
            "           </foreach>" +
            "      </when> ORDER BY WM.CREATE_TIME DESC\n" +
            "    ) RE \n" +
            "</script>\n")
    List<Map<String, Object>> pageWMDataMap(Page<Map<String, Object>> pages,
                                            @Param("whsMoveNo") String whsMoveNo,
                                            @Param("moveType") String moveType,
                                            @Param("goodsType") String goodsType,
                                            @Param("werksMoveOut") String werksMoveOut,
                                            @Param("whsLocationCodeMoveOut") String whsLocationCodeMoveOut,
                                            @Param("werksMoveIn") String werksMoveIn,
                                            @Param("whsLocationCodeMoveIn") String whsLocationCodeMoveIn,
                                            @Param("beginDate") String beginDate,
                                            @Param("endDate") String endDate,
                                            @Param("spmsStatisList") List<String> spmsStatisList,
                                            @Param("materialNo") String materialNo);

    @Select("SELECT DISTINCT WMD.WHS_MOVE_UUID AS \"whsMoveUuid\",\n" +
            "                WMD.AUTO_ID AS \"autoId\",\n" +
            "                WMD.ITEM_NO AS \"itemNo\",\n" +
            "                WMD.MATERIAL_NO AS \"materialNo\",\n" +
            "                SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "                SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "                SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  (CASE WHEN SAP.UNIT_SALES IS NULL THEN SAP.BASIC_UNIT ELSE SAP.UNIT_SALES END) AS\"unitSales\",\n" +
            "  (CASE WHEN SAP.NUMERATOR IS NULL THEN 1 ELSE SAP.NUMERATOR END) AS \"numerator\",\n" +
            "  (CASE WHEN SAP.DENOMINATOR IS NULL THEN 1 ELSE SAP.DENOMINATOR END) AS \"denominator\"," +
            "                WMD.BOX_NOTE AS \"boxNote\",\n" +
            "                WMD.PLANNED_OUT_QTY AS \"plannedOutQty\",\n" +
            "                WMD.PLANNED_OUT_STORAGE AS \"plannedOutStorage\",\n" +
            "                WMD.ACTUAL_OUT_QTY AS \"actualOutQty\",\n" +
            "                WMD.MOVE_OUT_STORAGE AS \"moveOutStorage\",\n" +
            "                WMD.MOVE_OUT_REMARK AS \"moveOutRemark\",\n" +
            "                WMD.ACTUAL_IN_QTY AS \"actualInQty\",\n" +
            "                WMD.MOVE_IN_STORAGE AS \"moveInStorage\",\n" +
            "                WMD.MOVE_IN_REMARK AS \"moveInRemark\",\n" +
            "                WMD.SUPPLIER_NO AS \"supplierNo\",\n" +
            "                WMD.COST_CENTER AS \"costCenter\",\n" +
            "                WMD.REMARK AS \"remark\",\n" +
            "                WMD.WAERS AS \"waers\",\n" +
            "                WMD.DMBTR AS \"dmbtr\"\n" +
            "FROM WHS_MOVE_DTL WMD LEFT JOIN MATERIAL_SAP SAP ON WMD.MATERIAL_NO = SAP.MATERIAL_NO \n" +
            "WHERE WMD.WHS_MOVE_UUID = #{whsMoveUuid} \n" +
            "ORDER BY WMD.AUTO_ID ASC ")
    List<Map<String, Object>> getWhsMoveDtls(@Param("whsMoveUuid") String whsMoveUuid);

    @Select("SELECT TO_CHAR(ROWNUM * 10) AS \"rowId\", RE.*\n" +
            "FROM (SELECT WMD.WHS_MOVE_UUID AS \"whsMoveUuid\",\n" +
            "             WMD.AUTO_ID AS \"autoId\",\n" +
            "             WMD.ITEM_NO AS \"itemNo\",\n" +
            "             WMD.BOX_NOTE AS \"boxNote\",\n" +
            "             WMD.ACTUAL_IN_QTY AS \"actualInQty\",\n" +
            "             WMD.MOVE_IN_STORAGE AS \"moveInStorage\",\n" +
            "             BND.MATERIAL_NO AS \"materialNo\",\n" +
            "             BND.BASIC_UNIT AS \"basicUnit\",\n" +
            "             BND.DOMESTIC_QTY_RECEIVED AS \"domesticQtyReceived\",\n" +
            "             WMD.COST_CENTER AS \"costCenter\",\n" +
            "             WMD.SUPPLIER_NO AS \"supplierNo\",\n" +
            "             WMD.REMARK AS \"remark\"\n" +
            "      FROM WHS_MOVE_DTL WMD\n" +
            "               LEFT JOIN SAP_BOX_NOTE_DTL BND ON WMD.BOX_NOTE = BND.BOX_NOTE\n" +
            "      WHERE BND.DOMESTIC_QTY_RECEIVED > 0 AND WMD.WHS_MOVE_UUID = #{whsMoveUuid} \n" +
            ") RE ")
    List<Map<String, Object>> getWhsMoveDtlsX(@Param("whsMoveUuid") String whsMoveUuid);

    @Update("UPDATE WHS_MOVE SET SPMS_STATUS = #{spmsStatus}, POSTING_DATE = #{postingDate}, SAP_VOUCHER = #{O_MBLNR}, " +
            "SAP_VOUCHER_YEAR = #{O_MJAHR} WHERE WHS_MOVE_UUID = #{whsMoveUuid} ")
    int updateWhsMove(@Param("whsMoveUuid") String whsMoveUuid,
                      @Param("postingDate") String postingDate,
                      @Param("spmsStatus") String spmsStatus,
                      @Param("O_MBLNR") String O_MBLNR,
                      @Param("O_MJAHR") String O_MJAHR);

    @Update("UPDATE WHS_MOVE SET SPMS_STATUS = #{spmsStatus} WHERE WHS_MOVE_UUID = #{whsMoveUuid} ")
    int updateWhsMoveStatus(@Param("whsMoveUuid") String whsMoveUuid, @Param("spmsStatus") String spmsStatus);
    
    @Update("<script>" +
    		"<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"begin\" close=\";end;\" separator=\";\"> \n" + 
    		"	update WHS_MOVE_DTL set \n" +
    		"		WAERS = #{item.waers,jdbcType=VARCHAR}, \n" +
    		"		DMBTR = #{item.dmbtr,jdbcType=VARCHAR} \n" +
    		"	where WHS_MOVE_UUID = #{item.whsMoveUuid,jdbcType=VARCHAR} \n" +
    		"		and ITEM_NO = #{item.itemNo} \n" +
    		"</foreach>" +
    		"</script>")
	int updateBatchDtl(List<WhsMoveDtl> list);
}