package com.persagy.server.mappers

import com.persagy.server.datacenter.models.entities.task.CategoryStatistics
import com.persagy.server.datacenter.models.entities.task.EquipmentTaskTwo
import com.persagy.server.datacenter.models.response.StatisticsCountResponse
import org.apache.ibatis.annotations.Mapper
import org.apache.ibatis.annotations.Select
import org.springframework.stereotype.Repository

/**
 * 统计
 *
 * @author 邓婷婷
 */
@Mapper
@Repository
interface StatisticsCountMapper {

    /**
     * 竖井-楼层  不能同步到物理世界的关系数量
     */
    @Select(" select COALESCE(sum(num),0) from " +
            "(select count(distinct shaft_id) num from r_sh_in_fl  where project_id = #{projectId} group by shaft_id HAVING count(floor_id)>1) a")
    fun sh2flCount(projectId: String): Long

    /**
     * 竖井-建筑  不能同步到物理世界的关系数量
     */
    @Select(" select COALESCE(sum(num),0) from " +
            "(select count(distinct shaft_id) num from r_sh_in_bd  where project_id = #{projectId} and shaft_id not in " +
            "(select shaft_id from r_sh_in_fl  where project_id = #{projectId} group by shaft_id HAVING count(floor_id)>1) group by shaft_id HAVING count(building_id)>1) a")
    fun sh2bdCount(projectId: String): Long


    /**
     * 系统-楼层  不能同步到物理世界的关系数量
     */
    @Select(" select COALESCE(sum(num),0) from " +
            "(select count(distinct sys_id) num from r_sy_in_fl  where project_id = #{projectId} group by sys_id HAVING count(floor_id)>1) a ")
    fun sy2flCount(projectId: String): Long

    /**
     * 系统-建筑  不能同步到物理世界的关系数量
     */
    @Select(" select COALESCE(sum(num),0) from " +
            "(select count(distinct sys_id) num from r_sy_in_bd  where project_id = #{projectId} and sys_id not in " +
            "(select sys_id from r_sy_in_fl  where project_id = #{projectId} group by sys_id HAVING count(floor_id)>1) group by sys_id HAVING count(building_id)>1) a")
    fun sy2bdCount(projectId: String): Long


    /**
     * 岗位和关联的资产信息点值不一致
     */
    @Select(" select count(p.id) from " +
            "property p,equipment e where " +
            "p.equip_id = e.id  and p.project_id = #{projectId} and " +
            "(COALESCE(p.name,'') != COALESCE(e.name,'')  or COALESCE(p.local_id,'') != COALESCE(e.local_id,'')  or COALESCE(p.local_name,'') != COALESCE(e.local_name,'') or " +
            "COALESCE(p.qr_code,'') != COALESCE(e.qr_code,'') or COALESCE(p.cad_id,'') != COALESCE(e.cad_id,'') or COALESCE(p.bim_id,'') != COALESCE(e.bim_id,'') or " +
            "COALESCE(p.bim_location,'') != COALESCE(e.bim_location,'') or COALESCE(p.building_id,'') != COALESCE(e.building_id,'') or " +
            "COALESCE(p.floor_id,'') != COALESCE(e.floor_id,'') or COALESCE(p.ledger_param ::text,'') != COALESCE(e.ledger_param ::text,'') or COALESCE(p.comm_param ::text,'') != COALESCE(e.comm_param ::text,'') or " +
            "COALESCE(p.tech_param ::text,'') != COALESCE(e.tech_param ::text,'') or COALESCE(p.run_param ::text,'') != COALESCE(e.run_param ::text,'') or COALESCE(p.setting_param ::text,'') != COALESCE(e.setting_param ::text,'') or " +
            "COALESCE(p.alram ::text,'') != COALESCE(e.alram ::text,'') or COALESCE(p.custom_param ::text,'') != COALESCE(e.custom_param ::text,'') or COALESCE(p.dp_manufacturer_id,'') != COALESCE(e.dp_manufacturer_id,'') or " +
            "COALESCE(p.dp_supplier_id,'') != COALESCE(e.dp_supplier_id,'') or COALESCE(p.dp_maintainer_id,'') != COALESCE(e.dp_maintainer_id,'') or COALESCE(p.dp_insurer_id,'') != COALESCE(e.dp_insurer_id,'') or " +
            "COALESCE(p.temp_sensor ::text,'') != COALESCE(e.temp_sensor ::text,'') or COALESCE(p.tech_param_general ::text,'') != COALESCE(e.tech_param_general ::text,'') or " +
            "COALESCE(p.run_param_general ::text,'') != COALESCE(e.run_param_general ::text,'') or COALESCE(p.speed_sensor ::text,'') != COALESCE(e.speed_sensor ::text,'') or " +
            "COALESCE(p.sound_sensor ::text,'') != COALESCE(e.sound_sensor ::text,'') or COALESCE(p.humidity_sensor ::text,'') != COALESCE(e.humidity_sensor ::text,'') or " +
            "COALESCE(p.press_transd ::text,'') != COALESCE(e.press_transd ::text,'') or COALESCE(p.heat_sensor ::text,'') != COALESCE(e.heat_sensor ::text,'') or COALESCE(p.flow_sensor ::text,'') != COALESCE(e.flow_sensor ::text,'') or " +
            "COALESCE(p.electricity_sensor ::text,'') != COALESCE(e.electricity_sensor ::text,'') or COALESCE(p.displ_sensor ::text,'') != COALESCE(e.displ_sensor ::text,'') or " +
            "COALESCE(p.concent_sensor ::text,'') != COALESCE(e.concent_sensor ::text,'') or COALESCE(p.image_sensor ::text,'') != COALESCE(e.image_sensor ::text,'') or " +
            "COALESCE(p.feedback_param ::text,'') != COALESCE(e.feedback_param ::text,'') or COALESCE(p.man_flow_sensor ::text,'') != COALESCE(e.man_flow_sensor ::text,'') or " +
            "COALESCE(p.dp_specification_id,'') != COALESCE(e.dp_specification_id,'') or COALESCE(p.dp_brand_id,'') != COALESCE(e.dp_brand_id,'') or COALESCE(p.equip_serial,0) != COALESCE(e.equip_serial,0))")
    fun eq2peCount(projectId: String): Long


    /**
     * 岗位和关联的资产信息点值不一致数据
     */
    @Select("select p.name,p.local_id,p.local_name,p.qr_code,p.cad_id,p.bim_id,p.bim_location,p.ledger_param,p.comm_param,p.tech_param,p.run_param," +
            "p.setting_param,p.alram,p.custom_param,p.dp_manufacturer_id,p.dp_supplier_id,p.dp_maintainer_id,p.dp_insurer_id,p.temp_sensor,p.tech_param_general," +
            "p.run_param_general,p.speed_sensor,p.sound_sensor,p.humidity_sensor,p.press_transd,p.heat_sensor,p.flow_sensor,p.electricity_sensor,p.displ_sensor," +
            "p.concent_sensor,p.image_sensor,p.feedback_param,p.man_flow_sensor,p.dp_specification_id,p.dp_brand_id,p.equip_serial,e.name as _name," +
            "e.local_id as _local_id,e.local_name as _local_name,e.qr_code as _qr_code,e.cad_id as _cad_id,e.bim_id as _bim_id,e.bim_location as _bim_location," +
            "e.ledger_param as _ledger_param,e.comm_param as _comm_param,e.tech_param as _tech_param,e.run_param as _run_param,e.setting_param as _setting_param," +
            "e.alram as _alram,e.custom_param as _custom_param,e.dp_manufacturer_id as _dp_manufactuer_id,e.dp_supplier_id as _dp_supplier_id,e.dp_maintainer_id as _dp_maintainer_id," +
            "e.dp_insurer_id as _dp_insurer_id,e.temp_sensor as _temp_sensor,e.tech_param_general as _tech_param_general,e.run_param_general as _run_param_general," +
            "e.speed_sensor as _speed_sensor,e.sound_sensor as _sound_sensor,e.humidity_sensor as _humidity_sensor,e.press_transd as _press_transd,e.heat_sensor as _heat_sensor," +
            "e.flow_sensor as _flow_sensor,e.electricity_sensor as _electricity_sensor,e.displ_sensor as _displ_sensor,e.concent_sensor as _concent_sensor," +
            "e.image_sensor as _image_sensor,e.feedback_param as _feedback_param,e.man_flow_sensor as _man_flow_sensor,e.dp_specification_id as _dp_specification_id," +
            "e.dp_brand_id as _dp_brand_id,e.equip_serial as _equip_serial from property p,equipment e where p.equip_id = e.id  and p.project_id =#{projectId}")
    fun eq2peDifferent(projectId: String): ArrayList<HashMap<String,Any>>




    /**
     * 设备-未关联资产的设备
     */
    @Select("select count(id) from equipment where id not in (select equip_id from property where equip_id is not null and project_id = #{projectId}) and project_id = #{projectId}")
    fun uneq2peCount(projectId: String): Long


    /**
     * 业务空间-各分区占比
     */
    @Select("select object_type as name,count(id) as count from zone_space_base where object_type is not null and project_id = #{projectId} group by object_type")
    fun spObjectTypeCount(projectId: String): ArrayList<StatisticsCountResponse>

    /**
     * 业务空间-默认分区功能类型占比
     */
    @Select("select func_type as name , count(id) as count from zone_space_base where func_type is not null and project_id = #{projectId}  AND object_type = 'GeneralZone' group by func_type")
    fun generalSpFuncTypeCount(projectId: String): ArrayList<StatisticsCountResponse>

    /**
     * 租户-关联业务空间完成度
     */
    @Select("select count(distinct tn_id) from relationship.r_tn2sp where project_id = #{projectId} and type = 'tn2sp'")
    fun tenant2SpCount(projectId: String): Long

    /**
     * 竖井-关联业务空间完成度
     */
    @Select("select count(distinct sh_id) from relationship.r_sh2sp where project_id = #{projectId} and type = 'sh2sp'")
    fun shaft2SpCount(projectId: String): Long

    /**
     * 竖井-按功能类型占比
     */
    @Select("select structure_info#>>'{ShaftFuncType}' as name ,count(id) as count  from shaft where  project_id = #{projectId} AND NOT structure_info#>>'{ShaftFuncType}' is null GROUP BY structure_info#>>'{ShaftFuncType}'")
    fun shaftFuncTypeCount(projectId: String): ArrayList<StatisticsCountResponse>

    /**
     * 统计设备任务下的部件类型以及数量
     */
    @Select("SELECT category as code,(SELECT name FROM dict.class_def WHERE id = category) as name, COUNT(category) FROM scantask.equip_scan_task WHERE task_parent_id = #{id} AND parent_id = #{equipId} GROUP BY category")
    fun categoryStatistics(id: String,equipId: String): ArrayList<CategoryStatistics>

    /**
     * 统计模型设备部件类型以及数量
     */
    @Select("SELECT category as code,(SELECT name FROM dict.class_def WHERE id = category) as name, COUNT(category) FROM scantask.model_scan_task WHERE task_parent_id = #{id} AND parent_id = #{equipId} GROUP BY category")
    fun categoryStatisticsModel(id: String,equipId: String): ArrayList<CategoryStatistics>

    /**
     * 查询 当前模型文件下指定元空间内的设备
     */
    @Select("SELECT id,name,local_id,local_name,cad_id,bim_id,category FROM equipment e WHERE project_id = #{projectId} AND bim_location  IS NOT null  AND model_id = #{modelId} AND is_point_in_polygon_v2_str(split_part(e.bim_location, E',', 1) , split_part(e.bim_location, E',', 2) , (SELECT outline FROM zone_ispace WHERE project_id= #{projectId} AND bim_id = #{bimId})) AND (e.task_state IS null OR e.task_state = 0 OR task_state = -1) ORDER BY create_time , e.id")
    fun equipInISpace(projectId: String,modelId: String,bimId: String): ArrayList<EquipmentTaskTwo>

    /**
     * 查询 当前模型文件下指定元空间内的设备
     */
    @Select("SELECT id,name,local_id,local_name,cad_id,bim_id,category FROM equipment e WHERE project_id = #{projectId} AND bim_location  IS NOT null AND category = #{category} AND model_id = #{modelId} AND is_point_in_polygon_v2_str(split_part(e.bim_location, E',', 1) , split_part(e.bim_location, E',', 2) , (SELECT outline FROM zone_ispace WHERE project_id= #{projectId} AND bim_id = #{bimId})) AND (e.task_state IS null OR e.task_state = 0 OR task_state = -1) ORDER BY create_time , e.id")
    fun equipInISpaceTwo(projectId: String,modelId: String,bimId: String,category: String): ArrayList<EquipmentTaskTwo>


} // Interface StatisticsCountMapper