package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.EamFormerSkuQueryDTO;
import com.sunda.spmsoversea.dto.EamReturnSkuQueryDTO;
import com.sunda.spmsoversea.entity.EamFormerSku;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
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>
 * EAM旧件申请表 Mapper 接口
 * </p>
 *
 * @author Hzc
 * @since 2023-11-16
 */
@Mapper
public interface EamFormerSkuMapper extends BaseMapper<EamFormerSku> {

    @Select("<script>" +
            "SELECT ORQ.UUID_REQUISITION AS \"uuidRequisition\",\n" +
            "       ORQ.SPMS_ID AS \"spmsId\",\n" +
            "       ORQ.CREATE_DATE AS \"createDate\",\n" +
            "       ORQ.APPROVAL_DATE AS \"approvalDate\",\n" +
            "       ORQ.BUSINESS_TYPE AS \"businessType\",\n" +
            "       ORQ.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       ORQ.CREATE_USERID AS \"createUserid\",\n" +
            "       SU.LAST_NAME AS \"createUsername\",\n" +
            "       ORQ.WERKS AS \"werks\",\n" +
            "       ORQ.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       ORQ.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       ORQ.RELATE_DOCUMENT AS \"relateDocument\",\n" +
            "       ORQ.COST_CENTER AS \"costCenter\",\n" +
            "       ORQ.COST_CENTER_NAME AS \"costCenterName\",\n" +
            "       ORQ.DATA_VERSION AS \"dataVersion\",\n" +
            "       ORQ.REMARKS AS \"remarks\",\n" +
            "       ORQ.COMMENTS AS \"comments\",\n" +
            "       ORQ.CREATE_TIME AS \"createTime\",\n" +
            "       ORQ.OA_NUMBER AS \"oaNumber\",\n" +
            "       ORQ.UPDATE_TIME AS \"updateTime\",\n" +
            "       ORQ.REQUESTNUM AS \"requestNum\",\n" +
            "       ORQ.FLAG AS \"flag\"\n" +
            "FROM EAM_FORMER_SKU ORQ " +
            "   LEFT JOIN SPMS_USER SU ON ORQ.CREATE_USERID = SU.WORK_CODE\n" +
            "WHERE 1=1 " +
            " <when test='dto.uuidRequisition!=null and dto.uuidRequisition != \"\"'> AND ORQ.UUID_REQUISITION = #{dto.uuidRequisition}</when>" +
            " <when test='dto.spmsId!=null and dto.spmsId != \"\"'> AND ORQ.SPMS_ID = #{dto.spmsId}</when>" +
            " <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND ORQ.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            " <when test='dto.werks!=null and dto.werks != \"\"'> AND ORQ.WERKS = #{dto.werks}</when>" +
            " <when test='dto.workshopCode!=null and dto.workshopCode != \"\"'> AND ORQ.WORKSHOP_CODE = #{dto.workshopCode}</when>" +
            " <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND ORQ.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            " <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND ORQ.CREATE_DATE &gt;= #{dto.beginDate}</when>" +
            " <when test='dto.endDate!=null and dto.endDate != \"\"'> AND ORQ.CREATE_DATE &lt;= #{dto.endDate}</when>" +
            " <when test='dto.requestNum!=null and dto.requestNum != \"\"'> AND ORQ.REQUESTNUM = #{dto.requestNum}</when>" +
            " <when test='dto.flag!=null and dto.flag != \"\"'> AND ORQ.flag = #{dto.flag}</when>" +
            " <when test='dto.createUser!=null and dto.createUser != \"\"'> " +
            "   AND INSTR(ORQ.CREATE_USERID || ',' || SU.LAST_NAME, #{dto.createUser}, 1, 1) > 0 " +
            " </when>" +
            " ORDER BY ORQ.SPMS_STATUS ASC, ORQ.UPDATE_TIME DESC " +
            "</script>")
    List<Map<String, Object>> getEamFormerSkuIPage(IPage page, @Param("dto") EamFormerSkuQueryDTO dto);


    @Select("SELECT ORQ.UUID_REQUISITION AS \"uuidRequisition\",\n" +
            "       ORQ.SPMS_ID AS \"spmsId\",\n" +
            "       ORQ.CREATE_DATE AS \"createDate\",\n" +
            "       ORQ.APPROVAL_DATE AS \"approvalDate\",\n" +
            "       ORQ.BUSINESS_TYPE AS \"businessType\",\n" +
            "       ORQ.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       ORQ.CREATE_USERID AS \"createUserid\",\n" +
            "       SU.LAST_NAME AS \"createUsername\",\n" +
            "       SU.DEPT_NAME AS \"deptName\",\n" +
            "       ORQ.WERKS AS \"werks\",\n" +
            "       ORQ.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       ORQ.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       ORQ.RELATE_DOCUMENT AS \"relateDocument\",\n" +
            "       ORQ.COST_CENTER AS \"costCenter\",\n" +
            "       ORQ.COST_CENTER_NAME AS \"costCenterName\",\n" +
            "       ORQ.DATA_VERSION AS \"dataVersion\",\n" +
            "       ORQ.REMARKS AS \"remarks\",\n" +
            "       ORQ.COMMENTS AS \"comments\",\n" +
            "       ORQ.CREATE_TIME AS \"createTime\",\n" +
            "       ORQ.UPDATE_TIME AS \"updateTime\",\n" +
            "       ORQ.REQUESTNUM AS \"requestNum\",\n" +
            "       ORQ.FLAG AS \"flag\"\n" +
            "FROM EAM_FORMER_SKU ORQ " +
            "   LEFT JOIN SPMS_USER SU ON ORQ.CREATE_USERID = SU.WORK_CODE\n" +
            "WHERE 1=1 " +
            "   AND ORQ.UUID_REQUISITION = #{uuidRequisition}")
    Map<String, Object> getEamFormerSku(@Param("uuidRequisition") String uuidRequisition);
}