package com.sunda.spmsorder.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmsorder.entity.MaterialApplication;
import com.sunda.spmsorder.entity.vo.MaterialApplicationQueryResultVo;
import com.sunda.spmsorder.entity.vo.MaterialApplicationQueryVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

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

/**
 * <p>
 * 物料申请单表表头 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-05-21
 */
@Mapper
public interface MaterialApplicationMapper extends BaseMapper<MaterialApplication> {

    @Select("<script>\n" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.*\n" +
            "FROM (\n" +
            "    SELECT MA.APPLICATION_UUID AS \"applicationUuid\",\n" +
            "             MA.APPLICATION_ID AS \"applicationId\",\n" +
            "             MA.USER_ID AS \"userId\",\n" +
            "             U.LAST_NAME AS \"username\",\n" +
            "             MA.WERKS AS \"werks\",\n" +
            "             MA.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "             MA.APPLICATION_TYPE AS \"applicationType\",\n" +
            "             MA.SPMS_STATUS AS \"spmsStatus\",\n" +
            "             MA.REMARKS AS \"remarks\",\n" +
            "             MA.APPLICATION_DATE AS \"applicationDate\",\n" +
            "             MA.CREATE_TIME AS \"createTime\",\n" +

            "             MA.OA_SUBMIT_DATE AS \"oaSubmitDate\",\n" +
            "             MA.UPDATE_TIME AS \"updateTime\",\n" +
            "             MA.PURCHASE_LOCATION AS \"purchaseLocation\",\n" +
            "             MA.CPX AS \"cpx\"\n" +
            "      FROM MATERIAL_APPLICATION MA\n" +
            "          LEFT JOIN SPMS_USER U ON MA.USER_ID = U.WORK_CODE \n" +
            "      WHERE MA.SPMS_STATUS != '0' \n" +
            "      <when test='queryVo.applicationId!=null and queryVo.applicationId != \"\"'> AND MA.APPLICATION_ID = #{queryVo.applicationId} </when> \n" +
            "      <when test='queryVo.werks!=null and queryVo.werks != \"\"'> AND MA.WERKS = #{queryVo.werks} </when> \n" +
            "      <when test='queryVo.workshopCode!=null and queryVo.workshopCode != \"\"'> AND MA.WORKSHOP_CODE = #{queryVo.workshopCode} </when> \n" +
            "      <when test='queryVo.userId!=null and queryVo.userId != \"\"'> AND MA.USER_ID = #{queryVo.userId} </when> \n" +
            "      <when test='queryVo.spmsStatus!=null and queryVo.spmsStatus != \"\"'> AND MA.SPMS_STATUS = #{queryVo.spmsStatus} </when> \n" +
            "      <when test='queryVo.beginDate!=null and queryVo.beginDate != \"\"'> AND TO_DATE(MA.APPLICATION_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{queryVo.beginDate}, 'yyyy-MM-dd') </when> \n" +
            "      <when test='queryVo.endDate!=null and queryVo.endDate != \"\"'> AND TO_DATE(MA.APPLICATION_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{queryVo.endDate}, 'yyyy-MM-dd') </when> \n" +
            ") RE \n" +
            "</script>\n")
    List<MaterialApplicationQueryResultVo> getMaterialApplicationList(@Param("queryVo") MaterialApplicationQueryVo materialApplicationQueryVo);

    @Select("<script>\n" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.*\n" +
            "FROM (\n" +
            "    SELECT MA.APPLICATION_UUID AS \"applicationUuid\",\n" +
            "             MA.APPLICATION_ID AS \"applicationId\",\n" +
            "             MA.USER_ID AS \"userId\",\n" +
            "             U.LAST_NAME AS \"username\",\n" +
            "             MA.WERKS AS \"werks\",\n" +
            "             MA.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "             MA.APPLICATION_TYPE AS \"applicationType\",\n" +
            "             MA.SPMS_STATUS AS \"spmsStatus\",\n" +
            "             MA.REMARKS AS \"remarks\",\n" +
            "             MA.APPLICATION_DATE AS \"applicationDate\",\n" +

            "             MA.OA_SUBMIT_DATE AS \"oaSubmitDate\",\n" +
            "             MA.CREATE_TIME AS \"createTime\",\n" +
            "             MA.UPDATE_TIME AS \"updateTime\",\n" +
            "             MA.PURCHASE_LOCATION AS \"purchaseLocation\",\n" +
            "             MA.CPX AS \"cpx\"\n" +
            "      FROM MATERIAL_APPLICATION MA\n" +
            "          LEFT JOIN SPMS_USER U ON MA.USER_ID = U.WORK_CODE \n" +
//            "      WHERE MA.SPMS_STATUS != '0' \n" +
            "      WHERE 1=1 \n" +
            "      <when test='queryVo.applicationId!=null and queryVo.applicationId != \"\"'> AND MA.APPLICATION_ID = #{queryVo.applicationId} </when> \n" +
            "      <when test='queryVo.werks!=null and queryVo.werks != \"\"'> AND MA.WERKS = #{queryVo.werks} </when> \n" +
            "      <when test='queryVo.workshopCode!=null and queryVo.workshopCode != \"\"'> AND MA.WORKSHOP_CODE = #{queryVo.workshopCode} </when> \n" +
            "      <when test='queryVo.userId!=null and queryVo.userId != \"\"'> AND MA.USER_ID = #{queryVo.userId} </when> \n" +
            "      <when test='queryVo.spmsStatus!=null and queryVo.spmsStatus != \"\"'> AND MA.SPMS_STATUS = #{queryVo.spmsStatus} </when> \n" +
            "      <when test='queryVo.beginDate!=null and queryVo.beginDate != \"\"'> AND TO_DATE(MA.APPLICATION_DATE, 'yyyy-MM-dd') &gt;= TO_DATE(#{queryVo.beginDate}, 'yyyy-MM-dd') </when> \n" +
            "      <when test='queryVo.endDate!=null and queryVo.endDate != \"\"'> AND TO_DATE(MA.APPLICATION_DATE, 'yyyy-MM-dd') &lt;= TO_DATE(#{queryVo.endDate}, 'yyyy-MM-dd') </when> \n" +
            ") RE \n" +
            "</script>\n")
    List<MaterialApplicationQueryResultVo> pageMaData(Page<MaterialApplicationQueryResultVo> page, @Param("queryVo") MaterialApplicationQueryVo queryVo);

    @Select(("<script>\n" +
            "SELECT ROWNUM AS \"rowId\",\n" +
            "       RE.*\n" +
            "FROM (\n" +
            "    SELECT MA.APPLICATION_UUID AS \"applicationUuid\",\n" +
            "             MA.APPLICATION_ID AS \"applicationId\",\n" +
            "             MA.USER_ID AS \"userId\",\n" +
            "             U.LAST_NAME AS \"username\",\n" +
            "             MA.WERKS AS \"werks\",\n" +
            "             MA.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "             MA.APPLICATION_TYPE AS \"applicationType\",\n" +
            "             MA.SPMS_STATUS AS \"spmsStatus\",\n" +
            "             MA.REMARKS AS \"remarks\",\n" +
            "             MA.APPLICATION_DATE AS \"applicationDate\",\n" +

            "             MA.OA_SUBMIT_DATE AS \"oaSubmitDate\",\n" +
            "             MA.CREATE_TIME AS \"createTime\",\n" +
            "             MA.UPDATE_TIME AS \"updateTime\",\n" +
            "             MA.PURCHASE_LOCATION AS \"purchaseLocation\",\n" +
            "             MA.CPX AS \"cpx\"\n" +
            "      FROM MATERIAL_APPLICATION MA\n" +
            "          LEFT JOIN SPMS_USER U ON MA.USER_ID = U.WORK_CODE \n" +
//            "      WHERE MA.SPMS_STATUS != '0' \n" +
            "      WHERE 1=1   \n" +
            "      <when test='applicationId!=null'> AND MA.APPLICATION_ID = #{applicationId} </when> \n" +
            "      <when test='spmsStatus!=null'> AND MA.SPMS_STATUS = #{spmsStatus} </when> \n" +
            "      <when test='userId!=null'> AND MA.USER_ID = #{userId} </when> \n" +
            "      <when test='werks!=null'> AND MA.WERKS = #{werks} </when> \n" +
            "      <when test='workshopCode!=null'> AND MA.WORKSHOP_CODE = #{workshopCode} </when> \n" +
            "      <when test='beginDate!=null'> AND TO_DATE(TO_CHAR(MA.CREATE_TIME, 'yyyy-MM-dd'), 'yyyy-MM-dd') &gt;= TO_DATE(#{beginDate}, 'yyyy-MM-dd') </when> \n" +
            "      <when test='endDate!=null'> AND TO_DATE(TO_CHAR(MA.CREATE_TIME, 'yyyy-MM-dd'), 'yyyy-MM-dd') &lt;= TO_DATE(#{endDate}, 'yyyy-MM-dd') </when> \n" +
            ") RE \n" +
            "</script>\n"))
    List<Map<String, Object>> pageMaDataMap(Page<Map<String, Object>> pages,
                                            @Param("applicationId") String applicationId,
                                            @Param("spmsStatus") String spmsStatus,
                                            @Param("userId") String userId,
                                            @Param("werks") String werks,
                                            @Param("workshopCode") String workshopCode,
                                            @Param("beginDate") String beginDate,
                                            @Param("endDate") String endDate);
}