package com.rongwei.bscommon.sys.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.rongwei.bsentity.domain.EquMaintenanceReportDo;
import com.rongwei.rwadmincommon.system.domain.SysUserDo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

public interface EquMaintenanceReportDao extends BaseMapper<EquMaintenanceReportDo> {

    @Select("SELECT a.* FROM (\n" +
            "SELECT DISTINCT\n" +
            "\tu.NAME,\n" +
            "\tu.EMAIL,\n" +
            "\tr.NAME AS roleName \n" +
            "FROM\n" +
            "\tsys_user u\n" +
            "    LEFT JOIN sys_user_role ur ON u.ID = ur.USERID AND ur.DELETED = 0  \n" +
            "    LEFT JOIN sys_role r ON ur.ROLEID = r.ID AND r.DELETED = 0  \n" +
            "    LEFT JOIN sys_user_org uo ON u.ID = uo.USERID AND uo.DELETED = 0  \n" +
            "    LEFT JOIN sys_organization org ON uo.ORGID = org.ID  AND org.DELETED = 0  \n" +
            "WHERE\n" +
            "\tu.DELETED = 0 \n" +
            "\tAND ORGID = #{workshopid}\n" +
            "\tAND ( r.NAME = '车间主任' OR r.NAME = '车间维修主管') \n" +
            "\tGROUP BY\n" +
            "    u.EMAIL\n" +
            "UNION\n" +
            "SELECT DISTINCT\n" +
            "    u.NAME,\n" +
            "    u.EMAIL,\n" +
            "    r.NAME AS roleName \n" +
            "FROM\n" +
            "    sys_user u \n" +
            "    LEFT JOIN sys_user_role ur ON u.ID = ur.USERID AND ur.DELETED = 0  \n" +
            "    LEFT JOIN sys_role r ON ur.ROLEID = r.ID AND r.DELETED = 0  \n" +
            "    LEFT JOIN sys_user_org uo ON u.ID = uo.USERID AND uo.DELETED = 0  \n" +
            "    LEFT JOIN sys_organization org ON uo.ORGID = org.ID  AND org.DELETED = 0  \n" +
            "WHERE\n" +
            "    u.DELETED = 0  \n" +
            "    AND org.FULLPID LIKE CONCAT('%', (\n" +
            "        SELECT \n" +
            "            SUBSTRING_INDEX(\n" +
            "                SUBSTRING_INDEX(\n" +
            "                    (SELECT GROUP_CONCAT(FULLPID SEPARATOR ',') \n" +
            "                     FROM sys_organization o \n" +
            "                     WHERE o.ID = #{workshopid}), \n" +
            "                    ',', 2), \n" +
            "                ',', -1\n" +
            "            )\n" +
            "        ), '%')\n" +
            "    AND r.NAME = '生产计划主任'\n" +
            "\t\tGROUP BY\n" +
            "    u.EMAIL\n" +
            ") a\n" +
            "GROUP BY\n" +
            "    a.EMAIL\n")
    List<SysUserDo> selectEmailList( @Param("workshopid") String workshopid);



}
