package com.sunda.spmsorder.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sunda.spmsorder.entity.ShoppingScenario;
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-03-01
 */
@Mapper
@Component
public interface ShoppingScenarioMapper extends BaseMapper<ShoppingScenario> {

    @Select("<script>" +
            "SELECT SS.UUID AS \"uuid\",\n" +
            "       SS.SCENARIO_ID AS \"scenarioId\",\n" +
            "       SS.WERKS AS \"werks\",\n" +
            "       SS.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       SS.SCENARIO_NAME AS \"scenarioName\",\n" +
            "       SS.SCENARIO_DESC AS \"scenarioDesc\",\n" +
            "       SS.MACHINE_NO AS \"machineNo\",\n" +
            "       SS.MACHINE_DESC AS \"machineDesc\",\n" +
            "       SS.REMARKS AS \"remarks\",\n" +
            "       SS.IS_DELETE AS \"isDelete\",\n" +
            "       SS.CREATE_USERID AS \"createUserid\",\n" +
            "       SU.LAST_NAME AS \"createUserName\",\n" +
            "       SS.UPDATE_USERID AS \"updateUser\",\n" +
            "       SU1.LAST_NAME AS \"updateUserName\",\n" +
            "       SS.CREATE_TIME AS \"createTime\",\n" +
            "       SS.UPDATE_TIME AS \"updateTime\"\n" +
            "FROM SHOPPING_SCENARIO SS \n" +
            "    LEFT JOIN SPMS_USER SU ON SS.CREATE_USERID = SU.WORK_CODE \n" +
            "    LEFT JOIN SPMS_USER SU1 ON SS.UPDATE_USERID = SU1.WORK_CODE\n" +
            "WHERE SS.WERKS IN \n" +
            "    <foreach item='item' index='index' collection='werks' open='(' separator=',' close=')'>\n" +
            "        #{item}\n" +
            "    </foreach>" +
            "<when test='isDelete!=null'> AND SS.IS_DELETE = #{isDelete} </when>\n" +
            "    ORDER BY SS.CREATE_TIME DESC \n" +
            "</script>")
    List<Map<String, Object>> getScenarioList(@Param("werks") String [] werks, @Param("isDelete") String isDelete);

    @Select("<script>" +
            "SELECT SS.UUID AS \"uuid\",\n" +
            "       SS.SCENARIO_ID AS \"scenarioId\",\n" +
            "       SS.WERKS AS \"werks\",\n" +
            "       SS.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       SS.SCENARIO_NAME AS \"scenarioName\",\n" +
            "       SS.SCENARIO_DESC AS \"scenarioDesc\",\n" +
            "       SS.MACHINE_NO AS \"machineNo\",\n" +
            "       SS.MACHINE_DESC AS \"machineDesc\",\n" +
            "       SS.REMARKS AS \"remarks\",\n" +
            "       SS.IS_DELETE AS \"isDelete\",\n" +
            "       SS.CREATE_USERID AS \"createUserid\",\n" +
            "       SU.LAST_NAME AS \"createUserName\",\n" +
            "       SS.UPDATE_USERID AS \"updateUser\",\n" +
            "       SU1.LAST_NAME AS \"updateUserName\",\n" +
            "       SS.CREATE_TIME AS \"createTime\",\n" +
            "       SS.UPDATE_TIME AS \"updateTime\"\n" +
            "FROM SHOPPING_SCENARIO SS \n" +
            "    LEFT JOIN SPMS_USER SU ON SS.CREATE_USERID = SU.WORK_CODE \n" +
            "    LEFT JOIN SPMS_USER SU1 ON SS.UPDATE_USERID = SU1.WORK_CODE\n" +
            "WHERE 1=1 \n" +
            "<when test='isDelete!=null'> AND SS.IS_DELETE = #{isDelete} </when>\n" +
            "<when test='spmsId!=null'> AND SS.SCENARIO_ID = #{spmsId} </when>\n" +
            "<when test='workShopCode!=null'> AND SS.WORKSHOP_CODE = #{workShopCode} </when>\n" +
            "<when test='werks!=null'> AND SS.WERKS = #{werks} </when>\n" +
            "<when test='authorName!=null'> AND SU.LAST_NAME LIKE concat(concat('%',#{authorName}),'%') </when>\n" +
            "<when test='machineDesc!=null'> AND SS.MACHINE_DESC LIKE concat(concat('%',#{machineDesc}),'%') </when>\n" +
            "<when test='scenarioName!=null'> AND SS.SCENARIO_NAME LIKE concat(concat('%',#{scenarioName}),'%') </when>\n" +
            "    ORDER BY SS.WERKS,SS.WORKSHOP_CODE,SS.SCENARIO_ID \n" +
            "</script>")
    List<Map<String, Object>> queryScenarioList(
            @Param("werks") String werks,
            @Param("isDelete") String isDelete,
            @Param("authorName") String authorName,
            @Param("scenarioName") String scenarioName,
            @Param("spmsId") String spmsId,
            @Param("workShopCode") String workShopCode,
            @Param("machineDesc") String machineDesc);


    @Update("<script> UPDATE SHOPPING_SCENARIO SET IS_DELETE = 1 WHERE UUID IN \n" +
            "    <foreach item='item' index='index' collection='scenarioUuid' open='(' separator=',' close=')'>\n" +
            "        #{item}\n" +
            "    </foreach>" +
            "</script>")
    int deleteScenarios(@Param("scenarioUuid") String [] scenarioUuid);
    
    @Select("<script>" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       SI.UUID AS \"uuid\",\n" +
            "       SI.WERKS \"werks\",\n" +
            "       SI.WHS_LOCATION_CODE \"whsLocationCode\",\n" +
            "       SI.MATERIAL_NO \"materialNo\",\n" +
            "       SI.BASIC_UNIT \"basicUnit\",\n" +
            "       MS.MATERIAL_ZH_DESC \"materialZhDesc\",\n" +
            "       MS.MATERIAL_EN_DESC \"materialEnDesc\",\n" +
            "       SI.SAFETY_QTY_BASIC_UNIT \"safetyQtyBasicUnit\",\n" +
            "       SI.SPMS_STATUS \"spmsStatus\",\n" +
            "       SI.STANDARD_CONSUMPTION_LAST_YEAR \"standardConsumptionLastYear\",\n" +
            "       SI.SAFETY_INVENTORY_YEAR \"safetyInventoryYear\",\n" +
            "       SI.EMERGENCY_RESERVE_BASIC_UNIT \"emergencyReserveBasicUnit\",\n" +
            "       SI.REMARKS \"remarks\",\n" +
            "       SI.COMMENTS \"comments\",\n" +
            "       SI.DATA_VERSION \"dataVersion\",\n" +
            "       SI.CREATE_USERID \"createUserid\",\n" +
            "       SU1.LAST_NAME \"createUsername\",\n" +
            "       SI.UPDATE_USERID \"updateUserid\",\n" +
            "       SU2.LAST_NAME \"updateUsername\",\n" +
            "       SI.CREATE_TIME \"createTime\",\n" +
            "       SI.CLASSIFY \"classify\",\n" +
            "       SI.UPDATE_TIME \"updateTime\"\n" +
            "FROM SAFETY_INVENTORY SI\n" +
            "    LEFT JOIN MATERIAL_SAP MS ON SI.MATERIAL_NO = MS.MATERIAL_NO\n" +
            "    LEFT JOIN SPMS_USER SU1 ON SI.CREATE_USERID = SU1.WORK_CODE\n" +
            "    LEFT JOIN SPMS_USER SU2 ON SI.UPDATE_USERID = SU2.WORK_CODE\n" +
            "WHERE SI.SPMS_STATUS != '0' AND SI.WERKS = #{werks} \n" +
            "  <when test='materialNo!=null and materialNo != \"\"'> AND SI.MATERIAL_NO = #{materialNo}</when>" +
            "  ORDER BY SI.MATERIAL_NO " +
            "</script>")
    List<Map<String, Object>> selectSafetyInventory(@Param("werks") String werks, @Param("materialNo") String materialNo);
}