package com.sunda.spmsorder.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmsorder.entity.MaterialApplicationDtl;
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 org.apache.ibatis.annotations.Update;

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

/**
 * <p>
 * 物料申请单明细表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-05-21
 */
@Mapper
public interface MaterialApplicationDtlMapper extends BaseMapper<MaterialApplicationDtl> {

    @Select("SELECT * FROM MATERIAL_APPLICATION_DTL mad JOIN MATERIAL_APPLICATION ma ON mad.APPLICATION_UUID = ma.APPLICATION_UUID WHERE ma.SPMS_STATUS IN ('2','4')")
    List<MaterialApplicationDtl> findMatBySpmsStatus();

    //首次查询非目录申请筛选结果
    @Select("SELECT mad.APPLICATION_UUID AS \"applicationUuid\", \n"+
            "mad.APPLICATION_ITEM AS \"applicationItem\", \n"+
            "mad.MATERIAL_EN_DESC AS \"materialEnDesc\", \n"+
            "mad.SPECS AS \"specs\", \n"+

            "ma.OA_SUBMIT_DATE AS \"oaSubmitDate\",\n" +
            "ma.CREATE_TIME AS \"createTime\",\n"+
            "ma.UPDATE_TIME AS \"updateTime\",\n"+
            "mad.MATERIAL_NO AS \"materialNo\", \n"+
            "su.LAST_NAME AS \"name\",\n"+
            "ma.WERKS AS \"werks\",\n"+
            "ma.SPMS_STATUS AS \"spmsStatus\", \n"+
            "ma.CPX AS \"cpx\",\n"+
            "mad.REMARKS AS \"remarks\", \n"+
            "mad.BASIC_UNIT AS \"basicUnit\", \n"+

            "mad.SUPPLIER_MATERIAL_NO AS \"supplierMaterialNo\",\n"+
            "mad.SUPPLY_DESC AS \"supplyDesc\",\n"+
            "mad.RELATE_EQUIPMENT AS \"relateEquipment\",\n"+
            "mad.EQUIPMENT_BRAND AS \"equipmentBrand\",\n"+
            "mad.EQUIPMENT_SPEC AS \"equipmentSpec\",\n"+
            "mad.EQUIPEMNT_SUPPLIER AS \"equipmentSupplier\",\n"+

            "ma.USER_ID AS \"userId\" ,\n"+
            "mad.PICTURE AS \"picture\" ,\n"+
            "mad.MATERIAL_MODEL AS \"materialModel\",\n"+

            "mad.THIRD_PARTY_PLATFORM AS \"thirdPartyPlatform\" ,\n"+
            "mad.Link AS \"link\",\n"+
            "mad.SKU AS \"sku\",\n"+

            "ma.APPLICATION_ID AS \"applicationId\" \n"+
            "FROM MATERIAL_APPLICATION_DTL mad JOIN MATERIAL_APPLICATION ma ON mad.APPLICATION_UUID = ma.APPLICATION_UUID \n"+
            "JOIN SPMS_USER su ON ma.USER_ID = su.WORK_CODE \n"+
            "WHERE ma.SPMS_STATUS IN ('2','4')")
    List<Map<String,Object>> findMatBySpms(Page<Map<String, Object>> pages);


    //非目录申请查询页筛选结果
    @Select("<script> \n"+
            "SELECT mad.APPLICATION_UUID AS \"applicationUuid\", \n"+
            "su.LAST_NAME AS \"name\",\n"+

            "ma.OA_SUBMIT_DATE AS \"oaSubmitDate\",\n" +
            "ma.CREATE_TIME AS \"createTime\",\n"+
            "ma.UPDATE_TIME AS \"updateTime\",\n"+
            "mad.APPLICATION_ITEM AS \"applicationItem\", \n"+
            "mad.MATERIAL_EN_DESC AS \"materialEnDesc\", \n"+
            "mad.MATERIAL_NO AS \"materialNo\", \n"+
            "mad.SPECS AS \"specs\", \n"+
            "ma.CPX AS \"cpx\", \n"+
            "ma.WERKS AS \"werks\", \n"+
            "mad.REMARKS AS \"remarks\", \n"+
            "mad.BASIC_UNIT AS \"basicUnit\", \n"+
            "mad.MATERIAL_MODEL AS \"materialModel\",\n"+
            "ma.APPLICATION_ID AS \"applicationId\" ,\n"+

            "mad.SUPPLIER_MATERIAL_NO AS \"supplierMaterialNo\",\n"+
            "mad.SUPPLY_DESC AS \"supplyDesc\",\n"+
            "mad.RELATE_EQUIPMENT AS \"relateEquipment\",\n"+
            "mad.EQUIPMENT_BRAND AS \"equipmentBrand\",\n"+
            "mad.EQUIPMENT_SPEC AS \"equipmentSpec\",\n"+
            "mad.EQUIPEMNT_SUPPLIER AS \"equipmentSupplier\",\n"+

            "mad.THIRD_PARTY_PLATFORM AS \"thirdPartyPlatform\" ,\n"+
            "mad.Link AS \"link\",\n"+
            "mad.SKU AS \"sku\",\n"+

            "mad.PICTURE AS \"picture\" ,\n"+
            "ma.USER_ID AS \"userId\" \n"+
            "FROM MATERIAL_APPLICATION_DTL mad JOIN MATERIAL_APPLICATION ma ON mad.APPLICATION_UUID = ma.APPLICATION_UUID \n"+
            "JOIN SPMS_USER su ON ma.USER_ID = su.WORK_CODE\n"+
            " WHERE 1=1 AND ma.SPMS_STATUS IN ('2','4')\n" +
            "<when test='applicationUuid!=null'>AND mad.APPLICATION_UUID=#{applicationUuid}</when> \n"+
            "<when test='applicationItem!=null'>AND mad.APPLICATION_ITEM=#{applicationItem}</when> \n"+
            "<when test='cpx!=null'>AND ma.CPX=#{cpx}</when> \n"+
            "<when test='specs!=null'>AND mad.SPECS like concat(concat('%',#{specs}),'%') </when> \n"+
            "<when test='materialEnDesc!=null'>AND mad.MATERIAL_EN_DESC || mad.SPECS like concat(concat('%',#{materialEnDesc}),'%') </when> \n"+
            "<when test='applicationId!=null'>AND ma.APPLICATION_ID=#{applicationId}</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='name!=null'>AND su.LAST_NAME=#{name}</when> \n"+
            "<when test='materialNo!=null'>AND mad.MATERIAL_NO like concat(concat('%',#{materialNo}),'%')</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" +
            "</script>")
    List<Map<String,Object>> findMatBySpmsNParam(Page<Map<String, Object>> pages,
                                                 @Param("applicationId") String applicationId,
                                                 @Param("applicationItem") String applicationItem,
                                                 @Param("cpx") String cpx,
                                                 @Param("applicationUuid") String applicationUuid,
                                                 @Param("werks") String werks,
                                                 @Param("specs") String specs,
                                                 @Param("materialEnDesc") String materialEnDesc,
                                                 @Param("userId") String userId,
                                                 @Param("name") String name,
                                                 @Param("materialNo") String materialNo,
                                                 @Param("beginDate") String beginDate,
                                                 @Param("endDate") String endDate);


    @Select("<script> \n"+
            "SELECT mad.APPLICATION_UUID AS \"applicationUuid\", \n"+
            "su.LAST_NAME AS \"name\",\n"+

            "ma.OA_SUBMIT_DATE AS \"oaSubmitDate\",\n" +
            "ma.CREATE_TIME AS \"createTime\",\n"+
            "ma.UPDATE_TIME AS \"updateTime\",\n"+
            "mad.APPLICATION_ITEM AS \"applicationItem\", \n"+
            "mad.MATERIAL_EN_DESC AS \"materialEnDesc\", \n"+
            "mad.MATERIAL_NO AS \"materialNo\", \n"+
            "mad.SPECS AS \"specs\", \n"+
            "ma.CPX AS \"cpx\", \n"+
            "ma.WERKS AS \"werks\", \n"+
            "mad.REMARKS AS \"remarks\", \n"+
            "mad.BASIC_UNIT AS \"basicUnit\", \n"+
            "mad.MATERIAL_MODEL AS \"materialModel\",\n"+
            "ma.APPLICATION_ID AS \"applicationId\" ,\n"+

            "mad.SUPPLIER_MATERIAL_NO AS \"supplierMaterialNo\",\n"+
            "mad.SUPPLY_DESC AS \"supplyDesc\",\n"+
            "mad.RELATE_EQUIPMENT AS \"relateEquipment\",\n"+
            "mad.EQUIPMENT_BRAND AS \"equipmentBrand\",\n"+
            "mad.EQUIPMENT_SPEC AS \"equipmentSpec\",\n"+
            "mad.EQUIPEMNT_SUPPLIER AS \"equipmentSupplier\",\n"+

            "mad.THIRD_PARTY_PLATFORM AS \"thirdPartyPlatform\" ,\n"+
            "mad.Link AS \"link\",\n"+
            "mad.SKU AS \"sku\",\n"+

            "mad.PICTURE AS \"picture\" ,\n"+
            "ma.USER_ID AS \"userId\" \n"+
            "FROM MATERIAL_APPLICATION_DTL mad JOIN MATERIAL_APPLICATION ma ON mad.APPLICATION_UUID = ma.APPLICATION_UUID \n"+
            "JOIN SPMS_USER su ON ma.USER_ID = su.WORK_CODE\n"+
            " WHERE 1=1 AND ma.SPMS_STATUS IN ('2','4') \n"+
            "<when test='applicationUuid!=null'>AND mad.APPLICATION_UUID=#{applicationUuid}</when> \n"+
            "<when test='applicationItem!=null'>AND mad.APPLICATION_ITEM=#{applicationItem}</when> \n"+
            "<when test='cpx!=null'>AND ma.CPX=#{cpx}</when> \n"+
            "<when test='specs!=null'>AND mad.SPECS like concat(concat('%',#{specs}),'%') </when> \n"+
            "<when test='materialEnDesc!=null'>AND mad.MATERIAL_EN_DESC || mad.SPECS like concat(concat('%',#{materialEnDesc}),'%') </when> \n"+
            "<when test='applicationId!=null'>AND ma.APPLICATION_ID=#{applicationId}</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='name!=null'>AND su.LAST_NAME=#{name}</when> \n"+
            "<when test='materialNo!=null'>AND mad.MATERIAL_NO like concat(concat('%',#{materialNo}),'%')</when> \n"+
            "</script>")
    List<Map<String,Object>> findExcelMat(@Param("applicationId") String applicationId, @Param("applicationItem") String applicationItem, @Param("cpx") String cpx, @Param("applicationUuid") String applicationUuid, @Param("werks") String werks, @Param("specs") String specs, @Param("materialEnDesc") String materialEnDesc, @Param("userId") String userId, @Param("name") String name, @Param("materialNo") String materialNo);


    @Update("UPDATE MATERIAL_APPLICATION_DTL SET MATERIAL_NO=#{materialNo} WHERE MATERIAL_APPLICATION_DTL.APPLICATION_UUID=#{applicationUuid} AND MATERIAL_APPLICATION_DTL.APPLICATION_ITEM = #{applicationItem}")
    int updateMatByItem(@Param("materialNo") String materialNo, @Param("applicationUuid") String applicationUuid, @Param("applicationItem") String applicationItem);

}