/*
 * *********************************************************************************************************************
 *
 *          !!
 *        .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.dao.mappers

import com.persagy.server.datacenter.models.entities.dictnew.DefClass
import com.persagy.server.models.EquipDictStatistics
import com.persagy.server.models.SpaceBdFl
import com.persagy.server.models.objects.FloorCompon
import org.apache.ibatis.annotations.Mapper
import org.apache.ibatis.annotations.Select
import org.springframework.stereotype.Repository

/**
 * 自定义sql
 *
 * @author 张维新
 */
@Mapper
@Repository
interface CustomSql {
    /** */
    @Select("SELECT category,(SELECT name FROM dict.class_def WHERE id = category) as category_name ,COUNT(*) FROM dictcustom.dict_type_conversion_datacenter_log WHERE project_id = #{projectId} GROUP BY category")
    fun datacenter(projectId: String): ArrayList<EquipDictStatistics>
    /** */
    @Select("SELECT category,(SELECT name FROM dict.class_def WHERE id = category) as category_name ,COUNT(*) FROM dictcustom.dict_type_conversion_dataplatform_log WHERE project_id = #{projectId} GROUP BY category")
    fun dataplatfrom(projectId: String): ArrayList<EquipDictStatistics>
    /** */
    @Select("SELECT p.floor_id as id, COUNT(p.floor_id) FROM r_tn_in_sp_base r LEFT JOIN zone_space_base P ON r.space_id = p.id WHERE r.project_id = #{projectId} AND r.object_type = #{objectType} AND r.tenant_id = #{tenantId} GROUP BY floor_id")
    fun floorCounts(projectId: String,objectType: String,tenantId: String): ArrayList<FloorCompon>

//    /** 模型质量报告 待删除的设备 */
//    @Select("<script>SELECT e.id,e.name ,(SELECT floor_name FROM revit.model_floor WHERE id = (SELECT model_floor_id FROM revit.model_file WHERE id = e.model_id))  AS floor_name ,(SELECT name FROM revit.model_folder WHERE id = (SELECT folder_id FROM revit.model_floor WHERE id = (SELECT model_floor_id FROM revit.model_file WHERE id = e.model_id))) as building_name FROM equipment e WHERE e.project_id = #{projectId} AND e.task_state = -1 AND e.model_id IN <foreach collection='listId' item='id' open='(' separator=',' close=')'> #{id} </foreach> ORDER BY floor_name </script>")
//    fun equipCommDel(projectId: String,listId: ArrayList<String>): ArrayList<EquipmentDel>
//
//    /** 模型质量报告 待删除的设备 */
//    @Select("SELECT * FROM equipment  e WHERE e.project_id = 'Pj4201050001' AND e.model_id IN ( '0ae9a09efc7f11e985a925cd807371a9' , '28bdbfc9fc8311e985a9d551daa279f0' )")
//    fun equipCommDel2(projectId: String,listId: ArrayList<String>): ArrayList<EquipmentDel>


    //设备 统计信息点与总数据占比

    /** 动态信息点总数 */
    @Select("select count(*) from dict.property_def_control_info where length(type) = 4 and datatype in " +
            "(select datatype from dict.property_def where datatype like '%v' or datatype like '%p' or datatype = 'Inst' or datatype = 'Acc')")
    fun dynamicInfoPointCounts(): Int

    /** 项目下所有动态信息点编码 */
    @Select("select info_point_code,type from dict.property_def_control_info where type in" +
            " (select category from public.equipment where project_id= #{projectId} GROUP BY category) and datatype in " +
            "(select datatype from dict.property_def_control_info where datatype like '%v' or datatype like '%p' or datatype = 'Inst' or datatype = 'Acc')")
    fun dynamicInfoPointCodes(projectId: String): ArrayList<HashMap<String,String>>

    /** 静态信息点总数 */
    @Select("select count(*) from dict.property_def_control_info where length(type) = 4 and datatype not in " +
            "(select datatype from dict.property_def_control_info where datatype like '%v' or datatype like '%p' or datatype = 'Inst' or datatype = 'Acc')")
    fun staticInfoPointCounts(): Int

    /** 项目下所有静态信息点编码 */
    @Select("select info_point_code,type from dict.property_def_control_info where type in(select category" +
            " from public.equipment where project_id= #{projectId} GROUP BY category) and datatype not in " +
            "(select datatype from dict.property_def where datatype like '%v' or datatype like '%p' or datatype = 'Inst' or datatype = 'Acc')")
    fun staticInfoPointCodes(projectId: String): ArrayList<HashMap<String,String>>

    /** 项目下所有的设备 */
    @Select("select id,name,local_id,local_name,qr_code,cad_id,bim_id,bim_location,category,ledger_param,comm_param,tech_param," +
            "run_param,setting_param,alram,tech_param_general,run_param_general,equip_serial,family_name,family_symbol,custom_param from public.equipment where project_id=#{projectId}")
    fun projectEquipment(projectId: String):ArrayList<HashMap<String,Any>>

    /** 项目中自定义信息点总数 */
    @Select("SELECT count(*) FROM dict.data_platform_scheme_info WHERE scheme_id = " +
            "(SELECT scheme_id FROM dict.project_scheme WHERE project_id = #{projectId}) AND length(type) = 4")
    fun customInfoPointCounts(projectId: String): Int

    /** 项目中所有自定义信息点编码*/
    @Select("SELECT info_point_code,type FROM dict.data_platform_scheme_info WHERE " +
            "scheme_id = (SELECT scheme_id FROM dict.project_scheme WHERE project_id =#{projectId} ) AND length(type) = 4")
    fun projectCustomInforPoints(projectId: String): ArrayList<HashMap<String,String>>


    //设备 统计信息点与需采集数据占比

    /** 设备 数字字典信息点总数 */
    @Select("select count(*) from dict.property_def_control_info where length(type) = 4")
    fun controlInfoCounts():Int

    /** 项目下所有已采集的动态信息点编码 */
    @Select("select info_point_code,type from dict.property_def_control_info where type" +
            " in(select category from public.equipment where project_id= #{projectId} GROUP BY category) and datatype in" +
            " (select datatype from dict.property_def_control_info where datatype like '%v' or datatype like '%p' or datatype = 'Inst' or datatype = 'Acc') and visible=TRUE")
    fun gatherDynamicInfoCode(projectId: String): ArrayList<HashMap<String,String>>


    /** 项目下所有已采集静态信息点编码 */
    @Select("select info_point_code,type from dict.property_def_control_info where type in(select category" +
            " from public.equipment where project_id= #{projectId} GROUP BY category) and datatype not in " +
            "(select datatype from dict.property_def_control_info where datatype like '%v' or datatype like '%p' or datatype = 'Inst' or datatype = 'Acc') and visible=TRUE")
    fun gatherStaticInfoCode(projectId: String): ArrayList<HashMap<String,String>>

    /** 项目下所有已采集自定义信息点编码*/
    @Select("SELECT info_point_code,type FROM dict.data_platform_scheme_info WHERE " +
            "scheme_id = (SELECT scheme_id FROM dict.project_scheme WHERE project_id =#{projectId} ) AND length(type) = 4 and visible=TRUE ")
    fun gatherCustomInforPointCode(projectId: String): ArrayList<HashMap<String,String>>

    //系统 统计信息点与需采集数据占比

    /** 系统  数字字典信息点总数 */
    @Select("select count(*) from dict.property_def where length(type)=2 and type not in ('WS','WE','SP','SE','OT','FF','CF','AC')")
    fun systemControlInfoCounts():Int

    /** 系统 项目下自定义信息点总数 */
    @Select("select count(*) from dict.data_platform_scheme_info where length(type)=2 and" +
            " type not in ('WS','WE','SP','SE','OT','FF','CF','AC') and scheme_id = (SELECT scheme_id FROM dict.project_scheme WHERE project_id =#{projectId})")
    fun systemCustomInfoCounts(projectId: String): Int

    /** 系统  已采集动态信息点编码 */
    @Select("select info_point_code,type from dict.property_def where (length(type)=2 or type='GeneralSystem') and type not in" +
            " ('WS','WE','SP','SE','OT','FF','CF','AC') and datatype in (select datatype from dict.property_def where datatype" +
            " like '%v' or datatype like '%p' or datatype = 'Inst' or datatype = 'Acc') and visible=TRUE")
    fun systemDynamicInfoCode():ArrayList<HashMap<String,String>>

    /** 系统 已采集静态信息点编码 */
    @Select("select info_point_code,type from dict.property_def where (length(type)=2 or type='GeneralSystem') and type not in ('WS','WE','SP','SE','OT','FF','CF','AC') and datatype not in" +
            " (select datatype from dict.property_def where datatype like '%v' or datatype like '%p' or datatype = 'Inst' or datatype = 'Acc') and visible=TRUE")
    fun systemStaticInfoCode():ArrayList<HashMap<String,String>>

    /** 系统 已采集自定义信息点编码 */
    @Select("select info_point_code,type from dict.data_platform_scheme_info where length(type)=2 and type not in ('WS','WE','SP','SE','OT','FF','CF','AC') " +
            "and scheme_id = (SELECT scheme_id FROM dict.project_scheme WHERE project_id =#{projectId}) and visible=TRUE")
    fun systemCustomInfoCode(projectId: String):ArrayList<HashMap<String,String>>

    /** 项目下所有系统 */
    @Select("select id,name,local_id,local_name,cad_id,bim_id,setting_param,tech_param,run_param,custom_param,category from public.general_system where project_id=#{projectId}")
    fun projectSystem(projectId: String):ArrayList<HashMap<String,Any>>



    /**
     * 查询空间所在的多楼层
     */
    @Select("SELECT space_id,COUNT(space_id)  FROM r_sp_in_fl WHERE project_id = #{projectId}  GROUP BY space_id")
    fun spaceFl(projectId: String): ArrayList<SpaceBdFl>

    /**
     * 查询空间所在的多建筑
     */
    @Select("SELECT space_id,COUNT(space_id)  FROM r_sp_in_bd WHERE project_id = #{projectId}  GROUP BY space_id")
    fun spaceBd(projectId: String): ArrayList<SpaceBdFl>

    /**
     * 计算项目空间的垂直关系
     */
    @Select("select is_vertically_overlap(#{projectId})")
    fun  spVertically(projectId: String):Boolean

    /**
     *项目下所有设备类型以及名称
     */
    @Select("SELECT class_code as code ,(SELECT name FROM dict.def_class WHERE project_id = #{projectId} AND code = class_code ) FROM v_equipment WHERE project_id = #{projectId} GROUP BY class_code")
    fun  equipTypeName(projectId: String):ArrayList<DefClass>

} //