package com.sunda.spmsorder.mapper;

import com.sunda.spmsorder.entity.SapProjectOaNumber;
import com.sunda.spmsorder.entity.vo.SapProjectOaNumberQueryVO;

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

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;

/**
 * <p>
 * 项目立项OA表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2022-10-21
 */
@Mapper
@Component
public interface SapProjectOaNumberMapper extends BaseMapper<SapProjectOaNumber> {
	
	@Select("<script>" +
			"SELECT DISTINCT RE.* FROM ( \n" +
            "SELECT  " +
            "	OW.COUNTRY AS \"country\",\n" +
            "	OW.FBK1 AS \"werks\",\n" +
            "	OW.FBK2 AS \"bukrs\",\n" +
            "	OW.PROJECTNAME AS \"projectname\",\n" +
            "	OW.PROJECTCODE AS \"projectcode\",\n" +
            "	concat(concat(OW.PROJECTCODE,'_'),OW.PROJECTNAME) AS \"projectcodename\",\n" +
            "	OW.CREATETIME AS \"createtime\"\n" +
            "FROM SAP_PROJECT_OA_NUMBER OW \n" +
            "WHERE 1=1 \n" +
            "AND (OW.FBK3 is null OR OW.FBK3 IN ('0','1'))" +
            "AND OW.FBK1 = #{record.werks} \n" +
            "<when test='record.projectname != null and record.projectname != \"\" '> AND OW.PROJECTNAME like concat(concat('%',#{record.projectname}),'%') </when> " +
            "<when test='record.projectcode != null and record.projectcode != \"\" '> AND OW.PROJECTCODE = #{record.projectcode} </when> " +
            "UNION ALL \n" +
            "SELECT  " +
            "	OW.COUNTRY AS \"country\",\n" +
            "	OW.FBK1 AS \"werks\",\n" +
            "	OW.FBK2 AS \"bukrs\",\n" +
            "	OW.PROJECTNAME AS \"projectname\",\n" +
            "	OW.PROJECTCODE AS \"projectcode\",\n" +
            "	concat(concat(OW.PROJECTCODE,'_'),OW.PROJECTNAME) AS \"projectcodename\",\n" +
            "	OW.CREATETIME AS \"createtime\"\n" +
            "FROM SAP_PROJECT_OA_NUMBER OW \n" +
            "LEFT JOIN WERKS WW ON OW.COUNTRY like concat(concat('%',WW.COUNTRY_NAME),'%') \n" +
            "WHERE 1=1 \n" +
            "AND (OW.FBK3 is null OR OW.FBK3 IN ('0','1'))" +
            "AND OW.FBK1 IS NULL \n" +
            "AND WW.WERKS = #{record.werks} \n" +
            "<when test='record.projectname != null and record.projectname != \"\" '> AND OW.PROJECTNAME like concat(concat('%',#{record.projectname}),'%') </when> " +
            "<when test='record.projectcode != null and record.projectcode != \"\" '> AND OW.PROJECTCODE = #{record.projectcode} </when> " +
            ") RE \n" +
            "</script>")
	List<Map<String, Object>> selectOaNumberList(IPage page, @Param("record") SapProjectOaNumberQueryVO record);
	
	@Delete("<script>" +
            "	DELETE FROM SAP_PROJECT_OA_NUMBER \n" +
            "</script>")
	int deleteBatch();
	
	@Insert("<script>" +
			"insert all \n" +
			"<foreach collection=\"list\" item=\"item\" index=\"index\" separator=\"\"> \n" +
			"	into SAP_PROJECT_OA_NUMBER ( \n" +
			"		COUNTRY, PROJECTNAME, PROJECTCODE, CREATETIME, CREATEUSER, LASTUPDATETIME, LASTUPDATEUSER, \n" +
			"		FBK1, FBK2, FBK3, FBK4, FBK5, FBK6, FBK7, FBK8, FBK9, FBK10 \n" +
			"	) values ( \n" +
			"		#{item.country,jdbcType=VARCHAR}, #{item.projectname,jdbcType=VARCHAR}, #{item.projectcode,jdbcType=VARCHAR}, \n" +
			"		#{item.createtime,jdbcType=TIMESTAMP}, #{item.createuser,jdbcType=VARCHAR}, #{item.lastupdatetime,jdbcType=TIMESTAMP}, \n" +
			"		#{item.lastupdateuser,jdbcType=VARCHAR}, #{item.fbk1,jdbcType=VARCHAR}, #{item.fbk2,jdbcType=VARCHAR}, \n" +
			"		#{item.fbk3,jdbcType=VARCHAR}, #{item.fbk4,jdbcType=VARCHAR}, #{item.fbk5,jdbcType=VARCHAR}, \n" +
			"		#{item.fbk6,jdbcType=VARCHAR}, #{item.fbk7,jdbcType=VARCHAR}, #{item.fbk8,jdbcType=VARCHAR}, \n" +
			"		#{item.fbk9,jdbcType=VARCHAR}, #{item.fbk10,jdbcType=VARCHAR} )\n" +
			"</foreach>" +
			"SELECT 1 FROM DUAL" +
			"</script>")
	int insertBatch(List<SapProjectOaNumber> list);

}