/*
 * *********************************************************************************************************************
 *
 *          !!
 *        .F88X
 *        X8888Y
 *      .}888888N;
 *        i888888N;        .:!              .I$WI:
 *          R888888I      .'N88~            i8}+8Y&8"l8i$8>8W~'>W8}8]KW+8IIN"8&
 *          .R888888I    .;N8888~          .X8'  "8I.!,/8"  !%NY8`"8I8~~8>,88I
 *            +888888N;  .8888888Y                                  "&&8Y.}8,
 *            ./888888N;  .R888888Y        .'}~    .>}'.`+>  i}!    "i'  +/'  .'i~  !11,.:">,  .~]!  .i}i
 *              ~888888%:  .I888888l      .]88~`1/iY88Ii+1'.R$8$8]"888888888>  Y8$  W8E  X8E  W8888'188Il}Y88$*
 *              18888888    E8888881    .]W%8$`R8X'&8%++N8i,8N%N8+l8%`  .}8N:.R$RE%N88N%N$K$R  188,FE$8%~Y88I
 *            .E888888I  .i8888888'      .:$8I;88+`E8R:/8N,.>881.`$8E/1/]N8X.Y8N`"KF&&FK!'88*."88K./$88%RN888+~
 *            8888888I  .,N888888~        ~88i"8W,!N8*.I88.}888%F,i$88"F88"  888:E8X.>88!i88>`888*.}Fl1]*}1YKi'
 *          i888888N'      I888Y          ]88;/EX*IFKFK88X  K8R  .l8W  88Y  ~88}'88E&%8W.X8N``]88!.$8K  .:W8I
 *        .i888888N;        I8Y          .&8$  .X88!  i881.:%888>I88  ;88]  +88+.';;;;:.Y88X  18N.,88l  .+88/
 *      .:R888888I
 *      .&888888I                                          Copyright (c) 2009-2020.  博锐尚格科技股份有限公司
 *        ~8888'
 *        .!88~                                                                     All rights reserved.
 *
 * *********************************************************************************************************************
 */

package com.persagy.server.mappers

import org.apache.ibatis.annotations.Mapper
import org.apache.ibatis.annotations.Select
import org.springframework.stereotype.Repository

/**
 * 导入关系excel
 *
 * @author  庞利祥 <sybotan@126.com>
 */
@Mapper
@Repository
interface IRelationExcelExportMapper {

//    /**
//     * 建筑下的竖井
//     * @param   projectId       项目id
//     * @return  excel需要的记录
//     */
//    @Select("select m.id,m.name,m.local_id,m.local_name,'建筑' AS object_type,m.name AS building_floor," +
//            "s.id AS _id,s.name AS _name,s.local_id AS _local_id,s.local_name AS _local_name,'竖井' AS _object_type," +
//            "m.name as _building_floor from public.building as m,shaft as s,r_sh_in_bd rsb where m.id=rsb.building_id and s.id=rsb.shaft_id and rsb.project_id=#{projectId}")
//    fun sh2bd(projectId: String): List<HashMap<String, Any?>>
//
//
//    /**
//     * 竖井贯通关系
//     * @param   projectId       项目id
//     * @return  excel需要的记录
//     */
//    @Select("select m.id,m.name,m.local_id,m.local_name,'竖井' AS object_type,s.id AS _id,s.name AS _name,s.local_id AS _local_id,s.local_name AS _local_name," +
//            "'竖井' AS _object_type from public.shaft as m,public.shaft as s,r_sh_through_sh as r where m.id=r.shaft_id and s.id=r.shaft_other_id and r.project_id=#{projectId}")
//    fun sh2sh(projectId: String): List<HashMap<String, Any?>>
//
//    /**
//     * 楼层贯通关系
//     * @param   projectId       项目id
//     * @return  excel需要的记录
//     */
//    @Select("select m.id,m.name,m.local_id,m.local_name,'楼层' AS object_type,m.name AS building_floor,s.id AS _id,s.name AS _name,s.local_id AS _local_id,s.local_name AS _local_name," +
//            "'楼层' AS _object_type,s.name as _building_floor from public.floor as m,floor as s,r_fl_through_fl r where m.id=r.floor_id and s.id=r.floor_other_id and r.project_id=#{projectId}")
//    fun fl2fl(projectId: String): List<HashMap<String, Any?>>
//
//    /**
//     * 资产所在竖井
//     * @param   projectId       项目id
//     * @return  excel需要的记录
//     */
//    @Select("select m.id,m.name,m.local_id,m.local_name,'竖井' AS object_type,s.id AS _id,s.name AS _name,s.local_id AS _local_id,s.local_name AS _local_name," +
//            "'资产' AS _object_type from public.shaft as m,property as s,r_pe_in_sh as r where m.id=r.shaft_id and s.id=r.equip_id and r.project_id=#{projectId}")
//    fun pe2sh(projectId: String): List<HashMap<String, Any?>>


    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    /**
     * 建筑与设备关系
     *
     * @param   projectId       项目id
     * @param   type            关系类型
     * @return  关系记录
     */
    @Select("SELECT r.bd_id AS id, bd.name, bd.local_id, bd.local_name, '建筑' AS object_type,\n" +
            "       r.eq_id AS _id, eq.name AS _name, eq.local_id AS _local_id, eq.local_name, '设备' AS _local_name \n" +
            "FROM relationship.r_eq2bd r JOIN equipment AS eq ON r.eq_id = eq.id JOIN building AS bd ON r.bd_id = bd.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun eq2bd(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 建筑下的设备
     *
     * @param   projectId       项目id
     * @return  关系记录
     */
    @Select("SELECT m.id, m.name, m.local_id, m.local_name,'建筑' AS object_type,m.name AS building_floor ," +
            "s.id AS _id, s.name AS _name, s.local_id AS _local_id, s.local_name AS _local_name, '设备' AS _object_type," +
            "s.f_name AS _building_floor FROM public.building as m JOIN (select e.id,e.name,e.local_id,e.local_name,e.building_id," +
            "f.name as f_name from public.equipment as e join floor as f on e.floor_id=f.id) AS s ON s.building_id = m.id WHERE m.project_id =#{projectId}")
    fun eq2bdIn(projectId: String): List<HashMap<String, Any?>>

    /**
     * 设备与设备关系
     *
     * @param   projectId       项目id
     * @param   type            关系类型
     * @return  关系记录
     */
    @Select("SELECT r.id1 AS id, eq1.name, eq1.local_id, eq1.local_name, '设备' AS object_type,\n" +
            "       r.id2 AS _id, eq2.name AS _name, eq2.local_id AS _local_id, eq2.local_name AS _local_name, '设备' AS _object_type \n" +
            "FROM relationship.r_eq2eq r JOIN equipment AS eq1 ON r.id1 = eq1.id JOIN equipment AS eq2 ON r.id2 = eq2.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun eq2eq(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 楼层下的设备
     *
     * @param   projectId       项目id
     * @return  excel需要的记录
     */
    @Select("SELECT " +
            "   m.id, m.name, m.local_id, m.local_name,'楼层' AS object_type,m.name AS building_floor, " +
            "   s.id AS _id, s.name AS _name, s.local_id AS _local_id, s.local_name AS _local_name, '设备' AS _object_type,m.name AS _building_floor " +
            "FROM public.floor as m JOIN equipment AS s ON s.floor_id = m.id " +
            "WHERE m.project_id = #{projectId}")
    fun eq2flIn(projectId: String): List<HashMap<String, Any?>>

    /**
     * 楼层与设备关系
     *
     * @param   projectId       项目id
     * @param   type            关系类型
     * @return  关系记录
     */
    @Select("SELECT r.fl_id AS id, fl.name, fl.local_id, fl.local_name, '楼层' AS object_type,\n" +
            "       r.eq_id AS _id, eq.name AS _name, eq.local_id AS _local_id, eq.local_name, '设备' AS _local_name \n" +
            "FROM relationship.r_eq2bd r JOIN equipment AS eq ON r.eq_id = eq.id JOIN floor AS fl ON r.fl_id = fl.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun eq2fl(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 设备与空间关系
     *
     * @param   projectId       项目id
     * @param   type            关系类型
     * @param   zoneType        空间类型
     * @return  关系记录
     */
    @Select("SELECT r.eq_id AS id, eq.name, eq.local_id, eq.local_name, '设备' AS object_type,\n" +
            "       r.sp_id AS _id, sp.name AS _name, sp.local_id AS _local_id, sp.local_name AS _local_name, '空间' AS _object_type\n" +
            "FROM relationship.r_eq2sp r JOIN equipment AS eq ON r.eq_id = eq.id JOIN zone_space_base AS sp ON r.sp_id = sp.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type} AND r.zoneType = #{zoneType}")
    fun eq2sp(projectId: String, type: String, zoneType :String): List<HashMap<String, Any?>>

    /**
     * 设备与系统关系
     *
     * @param   projectId       项目id
     * @param   type            关系类型
     * @return  关系记录
     */
    @Select("SELECT r.eq_id AS id, eq.name, eq.local_id, eq.local_name, '设备' AS object_type,\n" +
            "       r.sy_id AS _id, sy.name AS _name, sy.local_id AS _local_id, sy.local_name AS _local_name, '系统' AS _object_type\n" +
            "FROM relationship.r_eq2sy r JOIN equipment AS eq ON r.eq_id = eq.id JOIN general_system AS sy ON r.sy_id = sy.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun eq2sy(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 楼层与楼层关系
     *
     * @param   projectId       项目id
     * @param   type            关系类型
     * @return  关系记录
     */
    @Select("SELECT r.id1 AS id, fl1.name, fl1.local_id, fl1.local_name, '楼层' AS object_type,\n" +
            "       r.id2 AS _id, fl2.name AS _name, fl2.local_id AS _local_id, fl2.local_name AS _local_name, '楼层' AS _object_type\n" +
            "FROM relationship.r_sp2sp r JOIN floor AS fl1 ON r.id1 = fl1.id JOIN floor AS fl2 ON r.id2 = fl2.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun fl2fl(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 建筑下的资产
     *
     * @param   projectId       项目id
     * @return  excel需要的记录
     */
    @Select("SELECT m.id, m.name, m.local_id, m.local_name, '建筑' AS object_type,m.name AS building_floor," +
            "s.id AS _id, s.name AS _name, s.local_id AS _local_id, s.local_name AS _local_name, '资产' AS _object_type," +
            "s.f_name AS _building_floor FROM public.building as m JOIN (select p.id,p.name,p.local_id,p.local_name,p.building_id," +
            "f.name as f_name from property as p JOIN floor as f ON p.floor_id=f.id) AS s ON s.building_id = m.id WHERE m.project_id =#{projectId}")
    fun pe2bdIn(projectId: String): List<HashMap<String, Any?>>

    /**
     * 楼层下的资产
     *
     * @param   projectId       项目id
     * @return  excel需要的记录
     */
    @Select("SELECT " +
            "   m.id, m.name, m.local_id, m.local_name,'楼层' AS object_type,m.name AS building_floor, " +
            "   s.id AS _id, s.name AS _name, s.local_id AS _local_id, s.local_name AS _local_name,'资产' AS _object_type,m.name AS _building_floor " +
            "FROM public.floor as m JOIN property AS s ON s.floor_id = m.id " +
            "WHERE m.project_id = #{projectId}")
    fun pe2flIn(projectId: String): List<HashMap<String, Any?>>


    /**
     * 资产与竖井关系
     *
     * @param   projectId       项目id
     * @param   type            关系类型
     * @return  关系记录
     */
    @Select("SELECT r.pe_id AS id, pe.name, pe.local_id, pe.local_name, '资产' AS object_type,\n" +
            "       r.sh_id AS _id, sh.name AS _name, sh.local_id AS _local_id, sh.local_name AS _local_name, '竖井' AS _object_type\n" +
            "FROM relationship.r_pe2sh r JOIN property AS pe ON r.pe_id = pe.id JOIN shaft AS sh ON r.sh_id = sh.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun pe2sh(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 资产与空间关系
     *
     * @param   projectId       项目ID
     * @param   type            关系类型
     * @return  excel需要的记录
     */
    @Select("SELECT r.pe_id AS id, pe.name, pe.local_id, pe.local_name, '资产' AS object_type,\n" +
            "       r.sp_id AS _id, sp.name AS _name, sp.local_id AS _local_id, sp.local_name AS _local_name, '空间' AS _object_type\n" +
            "FROM relationship.r_pe2sp r JOIN property AS pe ON r.pe_id = pe.id JOIN zone_space_base AS sp ON r.sp_id = sp.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun pe2sp(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 建筑与竖井关系
     *
     * @param   projectId       项目ID
     * @param   type            关系类型
     * @return  excel需要的记录
     */
    @Select("SELECT r.sh_id AS id, sh.name, sh.local_id, sh.local_name, '资产' AS object_type,\n" +
            "       r.bd_id AS _id, bd.name AS _name, bd.local_id AS _local_id, bd.local_name AS _local_name, '空间' AS _object_type\n" +
            "FROM relationship.r_sh2bd r JOIN building AS bd ON r.bd_id = bd.id JOIN shaft AS sh ON r.sh_id = sh.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun sh2bd(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 楼层与竖井关系
     *
     * @param   projectId       项目ID
     * @param   type            关系类型
     * @return  excel需要的记录
     */
    @Select("SELECT r.sh_id AS id, sh.name, sh.local_id, sh.local_name, '竖井' AS object_type,\n" +
            "       r.fl_id AS _id, fl.name AS _name, fl.local_id AS _local_id, fl.local_name AS _local_name, '楼层' AS _object_type\n" +
            "FROM relationship.r_sh2fl r JOIN floor AS fl ON r.fl_id = fl.id JOIN shaft AS sh ON r.sh_id = sh.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun sh2fl(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 楼层与竖井关系
     *
     * @param   projectId       项目ID
     * @param   type            关系类型
     * @return  excel需要的记录
     */
    @Select("SELECT r.id1 AS id, sh1.name, sh1.local_id, sh1.local_name, '竖井' AS object_type,\n" +
            "       r.id2 AS _id, sh2.name AS _name, sh2.local_id AS _local_id, sh2.local_name AS _local_name, '竖井' AS _object_type\n" +
            "FROM relationship.r_sh2sh r JOIN shaft AS sh1 ON r.id1 = sh1.id JOIN shaft AS sh2 ON r.id2 = sh2.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun sh2sh(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 竖井空间关系
     *
     * @param   projectId       项目ID
     * @param   type            关系类型
     * @return  excel需要的记录
     */
    @Select("SELECT r.sh_id AS id, sh.name, sh.local_id, sh.local_name, '竖井' AS object_type,\n" +
            "       r.sp_id AS _id, sp.name AS _name, sp.local_id AS _local_id, sp.local_name AS _local_name, '空间' AS _object_type\n" +
            "FROM relationship.r_sh2sp r JOIN zone_space_base AS sp ON r.sp_id = sp.id JOIN shaft AS sp ON r.sh_id = sh.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun sh2sp(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 建筑与空间关系
     *
     * @param   projectId       项目ID
     * @param   type            关系类型
     * @return  excel需要的记录
     */
    @Select("SELECT r.sp_id AS id, sp.name, sp.local_id, sp.local_name, '空间' AS object_type,\n" +
            "       r.bd_id AS _id, bd.name AS _name, bd.local_id AS _local_id, bd.local_name AS _local_name, '建筑' AS _object_type\n" +
            "FROM relationship.r_sp2bd r JOIN building AS bd ON r.bd_id = bd.id JOIN zone_space_base AS sh ON r.sp_id = sp.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun sp2bd(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 楼层与空间关系
     *
     * @param   projectId       项目ID
     * @param   type            关系类型
     * @return  excel需要的记录
     */
    @Select("SELECT r.sp_id AS id, sp.name, sp.local_id, sp.local_name, '空间' AS object_type,\n" +
            "       r.fl_id AS _id, fl.name AS _name, fl.local_id AS _local_id, fl.local_name AS _local_name, '楼层' AS _object_type\n" +
            "FROM relationship.r_sp2fl r JOIN floor AS fl ON r.fl_id = fl.id JOIN zone_space_base AS sp ON r.sp_id = sp.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun sp2fl(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 空间与空间关系
     *
     * @param   projectId       项目ID
     * @param   type            关系类型
     * @return  excel需要的记录
     */
    @Select("SELECT r.id1 AS id, sp1.name, sp1.local_id, sp1.local_name, '空间' AS object_type,\n" +
            "       r.id2 AS _id, sp2.name AS _name, sp2.local_id AS _local_id, sp2.local_name AS _local_name, '空间' AS _object_type\n" +
            "FROM relationship.r_sp2sp r JOIN zone_space_base AS sp1 ON r.id1 = sp1.id JOIN zone_space_base AS sp2 ON r.id2 = sp2.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun sp2sp(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 建筑与系统关系
     *
     * @param   projectId       项目ID
     * @param   type            关系类型
     * @return  excel需要的记录
     */
    @Select("SELECT r.sy_id AS id, sy.name, sy.local_id, sy.local_name, '系统' AS object_type,\n" +
            "       r.bd_id AS _id, bd.name AS _name, bd.local_id AS _local_id, bd.local_name AS _local_name, '建筑' AS _object_type\n" +
            "FROM relationship.r_sy2bd r JOIN building AS bd ON r.bd_id = bd.id JOIN general_system AS sy ON r.sy_id = sy.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun sy2bd(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 楼层与系统关系
     *
     * @param   projectId       项目ID
     * @param   type            关系类型
     * @return  excel需要的记录
     */
    @Select("SELECT r.sy_id AS id, sy.name, sy.local_id, sy.local_name, '系统' AS object_type,\n" +
            "       r.fl_id AS _id, fl.name AS _name, fl.local_id AS _local_id, fl.local_name AS _local_name, '楼层' AS _object_type\n" +
            "FROM relationship.r_sp2fl r JOIN floor AS fl ON r.fl_id = fl.id JOIN general_system AS sy ON r.sy_id = sy.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun sy2fl(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 系统与空间关系
     *
     * @param   projectId       项目ID
     * @param   type            关系类型
     * @return  excel需要的记录
     */
    @Select("SELECT r.sy_id AS id, sy.name, sy.local_id, sy.local_name, '系统' AS object_type,\n" +
            "       r.fl_id AS _id, fl.name AS _name, fl.local_id AS _local_id, fl.local_name AS _local_name, '楼层' AS _object_type\n" +
            "FROM relationship.r_sy2sh r JOIN floor AS fl ON r.fl_id = fl.id JOIN general_system AS sy ON r.sy_id = sy.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun sy2sh(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 系统与空间关系
     *
     * @param   projectId       项目ID
     * @param   type            关系类型
     * @return  excel需要的记录
     */
    @Select("SELECT r.sy_id AS id, sy.name, sy.local_id, sy.local_name, '系统' AS object_type,\n" +
            "       r.sp_id AS _id, sp.name AS _name, sp.local_id AS _local_id, sp.local_name AS _local_name, '空间' AS _object_type\n" +
            "FROM relationship.r_sy2sp r JOIN general_system AS sy ON r.sy_id = sy.id JOIN zone_space_base AS sp ON r.sp_id = sp.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun sy2sp(projectId: String, type: String): List<HashMap<String, Any?>>

    /**
     * 租户与空间关系
     *
     * @param   projectId       项目ID
     * @param   type            关系类型
     * @return  excel需要的记录
     */
    @Select("SELECT r.tn_id AS id, tn.name, tn.local_id, tn.local_name, '租户' AS object_type,\n" +
            "       r.sp_id AS _id, sp.name AS _name, sp.local_id AS _local_id, sp.local_name AS _local_name, '空间' AS _object_type\n" +
            "FROM relationship.r_tn2sp r JOIN tenant AS tn ON r.tn_id = tn.id JOIN zone_space_base AS sp ON r.sp_id = sp.id\n" +
            "WHERE r.project_id = #{projectId} AND r.type = #{type}")
    fun tn2sp(projectId: String, type: String): List<HashMap<String, Any?>>

} // Interface IRelationExcelExportMapper