<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//oos.juyouhx.com//DTD Mapper 3.0//EN" "http://oss.juyouhx.com/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.scpyun.platform.standard.jilinsscgsdp.app" name="app业务">

	<select id="queryUserInfo" resultType="map" parameterType="String">
        select 
        cast(AES_DECRYPT(FROM_BASE64 (a.cardno),'*#id#') AS CHAR) cardnoPt,
        a.* from sys_user a where a.id=#{id}
    </select>
    
    <select id="queryGardenUserInfo" resultType="com.scpyun.platform.jilinsscgsdp.bean.bo.GardenInfoBo" parameterType="String">
       SELECT
       		b.id gardenUserId,
			a.city_id cityId,
			a.street_id streetId,
			a.community_id communityId,
			b.garden_id gardenId,
			a.NAME gardenName 
		FROM
			jl_grid_garden a
			INNER JOIN jl_grid_garden_user b 
			ON a.id = b.garden_id 
		WHERE
			b.cardno = #{cardno}
			ORDER BY
		a.create_time ASC
    </select>

    <select id="queryAllOffice" parameterType="map" resultType="map">
        SELECT
            id,name
        FROM
            sys_office
        WHERE
            del_flag='0'
    </select>
    
   <select id="queryAllArea" parameterType="map" resultType="map">
        SELECT
            id,name
        FROM
            sys_area
        WHERE
            del_flag='0'
    </select>
    
    <select id="queryOneOffice" resultType="map" parameterType="String">
        SELECT
            parent_ids,type,grade
        FROM
            sys_office
        WHERE
            del_flag='0' and id=#{id}
    </select>
    
    <select id="queryUserRoleInfo" resultType="map" parameterType="String">
        select c.id,c.name from sys_user a INNER JOIN sys_user_role b on a.id=b.USER_ID
		INNER JOIN sys_role c on b.ROLE_ID=c.id where a.id=#{id}
    </select>
    
     <select id="queryHomepageStaticCount" resultType="map" parameterType="com.scpyun.platform.jilinsscgsdp.bean.bo.UserInfoBo">
     	select count(1) totalCount,ifnull(round(sum(case when a.status='1' then 1 else 0 end)/COUNT(1)*100,2),0) finishRate 
     	from jl_event a INNER JOIN (select event_id from  jl_event_flow_log b  
     	where b.office_id = #{company_id} or b.grade_user_id = #{id}  GROUP BY event_id ) b on a.id=b.event_id
     </select>
     
     <select id="queryTaskCount" resultType="map" parameterType="map">
     	select count(1) taskCount from jl_event a INNER JOIN (select event_id from  jl_event_flow_log b  
		where 
		b.status!='3'  
		<choose>
			<when test="isGrid!=null and isGrid=='1'.toString()">
				and b.grade_user_id = #{user_id}
			</when>
			<otherwise>
				and b.office_id = #{company_id} and b.grade_user_id is null
			</otherwise>
		</choose>
		GROUP BY event_id ) b  on a.id=b.event_id
     </select>
     
     
     <select id="queryAreaEventsPageList" parameterType="page" resultType="map">
     	select a.id,event_code,c.label event_category_name,city_id,area_id,DATE_FORMAT(occurrence_time,'%Y-%m-%d') occurrence_time,dispute_description,
     	case when a.status='1' then '已办结' else '处理中' end status
		from jl_event a INNER JOIN 
		(select event_id from  jl_event_flow_log b  
		<where>
			<choose>
				<when test="params.grade!=null and params.grade=='6'.toString()">
					and b.office_id = #{params.company_id}
				</when>
				<when test="params.grade!=null and params.grade=='5'.toString()">
					and b.office_id like concat(substr(#{params.company_id},1,9),'%') 
				</when>
				<when test="params.grade!=null and params.grade=='4'.toString()">
					and b.office_id like concat(substr(#{params.company_id},1,6),'%') 
				</when>
				<when test="params.grade!=null and params.grade=='3'.toString()">
					and b.office_id like concat(substr(#{params.company_id},1,4),'%') 
				</when>
				<otherwise>
					and 1=1
				</otherwise>
			</choose>
		</where>
		GROUP BY event_id ) b  on a.id=b.event_id
		INNER JOIN sys_dict c on a.event_category_id=c.value where c.type='event_category' and c.parent_id='0' order by occurrence_time desc
     </select>
     
     <select id="queryEventsUnHandlePageList" parameterType="page" resultType="map">
     	select a.id,event_code,c.label event_category_name,city_id,area_id,DATE_FORMAT(b.create_time,'%Y-%m-%d %T') create_time,dispute_description,
     	case 
     	when now()>=deal_warn_time and now() &lt; deal_end_time then 'warning' 
     	when now()>=deal_end_time then 'alarm' else 'normal' end alert_status
		from jl_event a INNER JOIN 
		(select event_id,create_time,deal_warn_time,deal_end_time from  jl_event_flow_log b 
		where b.status!='3'
		<choose>
			<when test="params.isGrid!=null and params.isGrid=='1'.toString()">
				and b.grade_user_id = #{params.user_id}
			</when>
			<otherwise>
				and b.office_id = #{params.company_id} and b.grade_user_id is null
			</otherwise>
		</choose>
		) b  on a.id=b.event_id
		INNER JOIN sys_dict c on a.event_category_id=c.value
		where c.type='event_category' and c.parent_id='0' 
		<if test="params.month!=null and params.month!= ''">
			and DATE_FORMAT(b.create_time,'%Y-%m')=#{params.month}
		</if>
		<if test="params.keywords!=null and params.keywords!= ''">
			and (a.dispute_description like concat('%',#{params.keywords},'%') or a.event_code like concat('%',#{params.keywords},'%'))
		</if>
		order by occurrence_time desc
     </select>
     
    <select id="queryEventsHandledPageList" parameterType="page" resultType="map">
     	select a.id,event_code,c.label event_category_name,city_id,area_id,DATE_FORMAT(b.complete_time,'%Y-%m-%d %T') complete_time,dispute_description
		from jl_event a INNER JOIN 
		(select event_id,max(complete_time) complete_time from  jl_event_flow_log b 
		where b.office_id = #{params.company_id} and b.status='3'
		GROUP BY event_id ) b  on a.id=b.event_id
		INNER JOIN sys_dict c on a.event_category_id=c.value 
		where c.type='event_category' and c.parent_id='0' 
		<if test="params.month!=null and params.month!= ''">
			and DATE_FORMAT(b.complete_time,'%Y-%m')=#{params.month}
		</if>
		<if test="params.keywords!=null and params.keywords!= ''">
			and (a.dispute_description like concat('%',#{params.keywords},'%') or a.event_code like concat('%',#{params.keywords},'%'))
		</if>
		order by occurrence_time desc
     </select>
     
     <select id="queryEventsCompletePageList" parameterType="page" resultType="map">
     	select a.id,event_code,c.label event_category_name,city_id,area_id,DATE_FORMAT(complete_time,'%Y-%m-%d %T') complete_time,dispute_description
		from jl_event a INNER JOIN 
		(select event_id from  jl_event_flow_log b 
		where b.office_id = #{params.company_id}
		GROUP BY event_id ) b  on a.id=b.event_id
		INNER JOIN sys_dict c on a.event_category_id=c.value 
		where c.type='event_category' and c.parent_id='0' and a.status='1' 
		<if test="params.month!=null and params.month!= ''">
			and DATE_FORMAT(a.complete_time,'%Y-%m')=#{params.month}
		</if>
		<if test="params.keywords!=null and params.keywords!= ''">
			and (a.dispute_description like concat('%',#{params.keywords},'%') or a.event_code like concat('%',#{params.keywords},'%'))
		</if>
		order by complete_time desc
     </select>


    <select id="getEventDetailById" parameterType="map" resultType="map">
        SELECT
            je.event_type,
            (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id = '0' AND je.event_category_id = `value`)event_category_id,
            (SELECT label FROM sys_dict WHERE type = 'event_category' AND parent_id != '0' AND je.event_type_id = `value`) event_type_id,
            je.is_hidden_danger,
            je.is_special_tasks,
            je.is_difficult,
            je.is_public_opinion,
            je.is_early_warning,
            je.is_point_event,
            je.involved_number,
            DATE_FORMAT(je.occurrence_time,'%Y-%m-%d %T') occurrence_time,
            je.city_id,
            (SELECT name FROM sys_area WHERE code = je.city_id) city,
            je.area_id,
            (SELECT name FROM sys_area WHERE code = je.area_id) area,
            je.street_id,
            (SELECT name FROM sys_area WHERE code = je.street_id) street,
            je.community_id,
            (SELECT name FROM sys_area WHERE code = je.community_id) community,
            je.garden_id,
            je.building_id,
            je.unit_id,
            je.floor_id,
            je.doorplate_id,
            je.event_code,
            je.event_source,
            je.address,
            je.dispute_description,
            je.reporting_name,
            je.reporting_unit,
            je.reporting_garden,
            jefl.id,
            jefl.event_id,
            jefl.flow_version,
            jefl.flow_id,
            jefl.office_id,
            jefl.status,
            jefl.log_type,
            jefl.assignment_type,
            jefl.log_result,
            jefl.resolve_name,
            jefl.resolve_unit,
            jefl.resolve_hierarchy,
            DATE_FORMAT(jefl.resolve_time,'%Y-%m-%d %T') resolve_time,
            jefl.is_satisfaction,
            jefl.resolve_process,
            jefl.is_resolve,
            jefl.warning_level,
            jefl.contacts,
            jefl.attachment,
            jefl.deal_warn_time,
            jefl.deal_end_time,
            (SELECT hierarchy FROM jl_event_flow_setting WHERE flow_id = jefl.id) hierarchySetting
        FROM
            jl_event je
        LEFT JOIN jl_event_flow_log jefl ON je.id = jefl.event_id
        WHERE
		je.del_flag = '0'
        AND jefl.del_flag = '0'
        AND je.id = #{id}
        ORDER BY jefl.create_time DESC
        LIMIT 1
    </select>
</mapper>
