package com.skua.modules.safe.mapper;

import java.util.List;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import com.skua.modules.safe.entity.DangerLevelManage;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

/**
 * 风险分级管控清单
 */
public interface DangerLevelManageMapper extends BaseMapper<DangerLevelManage> {

    /***
     * 根据id获取风险分级对象
     * @param id
     * @return
     *   测试的sql语句     select * from danger_level_manage where id = '4e7200f531db9f6409ecc81d24b07486';
     * 	select * from danger_inspection_record where danger_id = '4e7200f531db9f6409ecc81d24b07486';
     * 	select * from danger_inspection_info where inspection_record_id = '1898976888818851842';
     * 	select * from danger_inspection_item where inspection_info_id in ( '1898976888844017665' ,'1898976888919515137');
     */
    public  DangerLevelManage queryById(@Param("id") String id);

    //List<DangerLevelManage> queryByList(Page page, @Param("param") DangerLevelManage dangerLevelManage);
    @Select("SELECT t.*,ifnull(aaa.inspect_content,'') inspect_content, ifnull(ccc.inspection_num,0) inspection_num, ifnull(bbb.rectification_num,0) 'rectification_num' " +
            " FROM danger_level_manage t" +
            " left join (select danger_id ,GROUP_CONCAT(inspect_name SEPARATOR '@') 'inspect_content' from  danger_inspect_info group by danger_id)aaa on aaa.danger_id = t.id " +
            " left join (select eee.danger_id   ,count(eee.record_id) 'rectification_num' from ( select  DISTINCT t.id 'record_id' ,t1.danger_id  from danger_inspection_record t" +
            "  left join danger_inspection_info  t1 on t.id= t1.inspection_record_id    left join danger_inspection_item t2 on t1.id = t2.inspection_info_id  " +
            "  where  t2.status = 0  and DATE_FORMAT(t.report_date,'%Y-%m-%d')  >= #{startTime} and  DATE_FORMAT(t.report_date,'%Y-%m-%d')  <= #{endTime} "+
            "  )eee  group by eee.danger_id  " +
            " )bbb on bbb.danger_id = t.id  " +
            " left join (select danger_id, count(id) 'inspection_num' from ajh_rectification_info" +
            "  where DATE_FORMAT(rec_ord_report_date,'%Y-%m-%d')  >= #{startTime} and  DATE_FORMAT(rec_ord_report_date,'%Y-%m-%d')  <= #{endTime} "+
            "  group by danger_id)ccc  on ccc.danger_id = t.id ${ew.customSqlSegment}")
    List<DangerLevelManage> getListByWrapper(Page page,@Param("ew") QueryWrapper<DangerLevelManage> queryWrapper,@Param("startTime") String startTime,@Param("endTime") String endTime);

}