<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yeejoin.amos.patrol.business.dao.mapper.RouteMapper">
    <!-- 根据ID获取路线点的项信息 -->
    <select id="getInputItemById" resultType="com.yeejoin.amos.patrol.business.vo.PointInputItemVo">   
		SELECT
			ii.*, prpi.point_classify_id as 'classifyIds',prpi.id routePointItemId
		FROM
			p_input_item ii
		LEFT JOIN p_point_inputitem pii ON pii.input_item_id = ii.id
		LEFT JOIN p_route_point_item prpi ON prpi.point_input_item_id = pii.id,
		 (
			SELECT
				id AS route_point_id
			FROM
				p_route_point rp
		        <where>
		            <if test="routeId != null">route_id = #{routeId}</if>
		            <if test="pointId != null">and point_id = #{pointId}</if>
		        </where>
		) prp
		WHERE
			prp.route_point_id = prpi.route_point_id
    </select>
    
    <resultMap id="routePointInputItemMap" type="com.yeejoin.amos.patrol.dao.entity.PointInputItem">
       <result property="id" column="id" />
       <result property="pointId" column="point_id" />
       <result property="inputItemId" column="input_item_id" />
       <result property="classifyIds" column="classify_ids" />
       <result property="createDate" column="create_date" />
       <result property="isDelete" column="is_delete" />
       <result property="orderNo" column="order_no" />
    </resultMap>
    <select id="getRoutePointInputItem" resultMap="routePointInputItemMap">
	    SELECT
			*
		FROM
			p_point_inputitem
		WHERE
			id IN (
				SELECT
                    point_input_item_id
                FROM
                    p_route_point_item prpi,
                    (
                        SELECT
                            id AS route_point_id
                        FROM
                            p_route_point
                        <where>
                            <if test="routeId != null">route_id = #{routeId}</if>
                            <if test="pointId != null">and point_id = #{pointId}</if>
                        </where>
                    ) prp
                WHERE
                    prp.route_point_id = prpi.route_point_id
			)
    </select>
    
    <select id="getRouteNameByPointId" resultType="String">
	    SELECT
		    name
		FROM
		    p_route
		WHERE
		    id IN (
		        SELECT DISTINCT
		            route_id
		        FROM
		            `p_route_point`
		        <where>
                    <if test="pointId != null">point_id = #{pointId}</if>
                </where>
		    );
    </select>
    
    <!--统计路线个数-->
    <select id="countRoute" resultType="long">
        SELECT
		    COUNT(1) AS total_sum
		FROM
		    `p_route` r
		<!-- LEFT JOIN s_department d ON r.dept_id = d.id
		LEFT JOIN s_user u ON r.user_id = u.id -->
		WHERE
		    r.is_delete = 0
	    <if test="name!=null">AND r.`name` LIKE CONCAT('%', #{name}, '%')</if>
		<if test="remark!=null">AND r.remark LIKE CONCAT('%', #{remark}, '%')</if>
		<if test="orgCode!=null">AND r.org_Code = #{orgCode}</if>
		<!-- <if test="deptId!=null">AND r.dept_id = #{deptId}</if> -->
		  <choose>
			         <when	test="deptId > 0 " > AND r.dept_id = #{deptId}</when>
			          <when	test="deptId == -1 " > AND r.dept_id in(0,-1)</when>
			        </choose>
    </select>
    
    <!--返回路线信息-->
    <select id="getRouteInfo" resultType="java.util.HashMap">
		        SELECT
		            r.`name`,
		            r.id,
		            r.boss,
		            r.tel,
		            r.remark,
		            r.dept_id,
		            r.user_id
		        FROM
		            `p_route` r	
		        WHERE
		            r.is_delete = 0
		            <if test="name!=null">AND r.`name` LIKE CONCAT('%', #{name}, '%')</if>
			        <if test="remark!=null">AND r.remark LIKE CONCAT('%', #{remark}, '%')</if>
			        <if test="orgCode!=null">AND r.org_Code = #{orgCode}</if>
			        <choose>
			         <when	test="deptId > 0 " > AND r.dept_id = #{deptId}</when>
			          <when	test="deptId == -1 " > AND r.dept_id in(0,-1)</when>
			        </choose>
				ORDER BY id
        <choose>
            <when test="pageSize==-1"></when>
            <when test="pageSize!=-1">limit #{offset},#{pageSize}</when>  
        </choose>
    </select>
    <delete id="deleteReleInputItem" > 
		delete b from p_point_inputitem a,p_route_point_item b  where   a.id = b.point_input_item_id  and a.input_item_id in 
		<foreach item="Id" collection="array" index="index" open="(" separator="," close=")">
		#{Id}
	    </foreach>
	</delete>
	<select id="queryRouteListByOrgCode" resultType="Map">
		SELECT temp.* FROM (
		<if test="userId != null and userId != ''">
			SELECT

				r.id,
				r.`name`
			FROM
				p_plan ppl
			LEFT JOIN p_route r ON r.id = ppl.route_id
			WHERE
			r.is_delete = 0
			AND	find_in_set(#{userId}, ppl.user_id) > 0 and r.org_code =  #{orgCode}
			 
		</if>
		<if test="(orgCode != null and orgCode != '') or (deptId != null and deptId != '')">
			<if test="userId != null and userId != ''">
				UNION
			</if>
			SELECT
				r.id,
				r.`name`
			FROM
				p_route r
			WHERE
				r.is_delete = 0
				<if test="orgCode != null and orgCode != ''">
					 AND r.org_code = #{orgCode}
				</if>
		   		<if test="deptId != null and deptId != ''">
					AND r.dept_id = #{deptId}
				</if>
		</if>) temp
		GROUP BY temp.id
	</select>
	
	<select id="queryRoutePointsCount" resultType="Long">
		SELECT
			count(1) num
		FROM
			p_route_point rp
		LEFT JOIN p_point p ON p.id = rp.point_id
		where p.is_delete = 0
		AND rp.route_id = #{routeId}	
		AND p.org_code = #{orgCode}
	</select>
	<select id="queryRoutePoints" resultType="Map">
		SELECT
			rp.order_no orderNo,
			rp.id routePointId,
			p.id,
			p.route_id routeId,
			p.route_name routeName,
			p.name,
			p.point_no pointNo,
			p.remark,
			p.save_gps saveGps,
			p.distance,
			p.usually_shot usuallyShot,
			p.shot_min_number shotMinNumber,
			p.shot_max_number shotMaxNumber,
			p.fixed_shot fixedShot,
			p.latitude,
			p.longitude,
			p.address,
			p.offline,
			p.is_fixed,
			p.catalog_id catalogId,
			p.extend_json extendJson,
			p.is_score isScore,
			p.level,
			p.last_update_time lastUpdateTime,
			p.coordinates,
			p.charge_dept_id chargeDeptId,
			p.charge_person_id chargPersonId
		FROM
			p_route_point rp
		LEFT JOIN p_point p ON p.id = rp.point_id
		where p.is_delete = 0
		AND rp.route_id = #{routeId}
		AND p.org_code = #{orgCode}
        <if test="pointNo != null and pointNo != ''">and p.point_no like CONCAT('%', #{pointNo}, '%')</if>
        <if test="name != null  and name != ''">and p.name like CONCAT('%', #{name}, '%')</if>
		<choose>
            <when test="pageSize==-1"></when>
            <when test="pageSize!=-1">limit #{offset},#{pageSize}</when>  
        </choose>
	</select>
	
	 <resultMap id="routePointMap" type="com.yeejoin.amos.patrol.core.common.response.RoutePointRespone">
	   <result property="id" column="id" />
       <result property="pointNo" column="pointNo" />
       <result property="pointId" column="pointId" />
       <result property="pointName" column="pointName" />
       <result property="orderNo" column="orderNo" />
       <result property="routeId" column="routeId" />
		 <result property="createDate" column="createDate" />
       <collection property="pointClassifyList" ofType="com.yeejoin.amos.patrol.core.common.response.PointClassifysRespone" 
       column="{pointId=pointId}" select ="queryPointClassifyList"></collection>
       <collection property="routePointItem" ofType="com.yeejoin.amos.patrol.core.common.response.PointInputItemRespone" 
       column="{pointId=pointId,routeId=routeId,isBound = isBound}" select ="queryRoutePointItemByCondition"></collection>
    </resultMap>
	
	<select id="queryPointClassifyList" resultType="com.yeejoin.amos.patrol.core.common.response.PointClassifysRespone">
		SELECT id,name FROM p_point_classify WHERE point_id = #{pointId}
	</select>
	
	<select id="queryRoutePointsByCondition" resultMap="routePointMap">
		SELECT	
			rp.id,
			rp.order_no orderNo,
			rp.create_date createDate,
			p.id pointId,
			rp.route_id routeId,
			p.name pointName,
			p.point_no pointNo,
			'bound' as isBound,
			(SELECT GROUP_CONCAT(name SEPARATOR ';')  FROM p_point_classify WHERE point_id = p.id GROUP BY point_id) classifyName
		FROM
			p_route_point rp
		LEFT JOIN p_point p ON p.id = rp.point_id
		where p.is_delete = 0
		AND rp.route_id = #{routeId}
		AND p.org_code = #{orgCode}
		order by rp.order_no 
        <if test="pointNo != null and pointNo != ''">and p.point_no like CONCAT('%', #{pointNo}, '%')</if>
        <if test="name != null  and name != ''">and p.name like CONCAT('%', #{name}, '%')</if>
		<choose>
            <when test="pageSize==-1"></when>
            <when test="pageSize!=-1">limit #{offset},#{pageSize}</when>  
        </choose>
	</select>
	
	
	<select id="queryRoutePointItem" resultType="com.yeejoin.amos.patrol.dao.entity.RoutePointItem">
		SELECT
			rpi.*
		FROM
			p_route_point_item rpi
		LEFT JOIN p_route_point rp ON rp.id = rpi.route_point_id
		LEFT JOIN p_point_inputitem ppi ON ppi.id = rpi.point_input_item_id
		<where>
			<if test="routeId !=null and routeId !=''">
				AND rp.route_id = #{routeId}
			</if>
			<if test="pointId !=null and pointId !=''">
				AND rp.point_id = #{pointId}
			</if>
			<if test="inputItemId !=null and inputItemId !=''">
				AND ppi.input_item_id = #{inputItemId}	
			</if>
			<if test="pointClassifyId !=null and pointClassifyId !=''">
				AND rpi.point_classify_id = #{pointClassifyId}
			</if>
		</where>
	</select>
	
	<select id="getRouteInfoList" resultType="Map">
		SELECT
		    ppl.planId,		   
		    IFNULL(ppl.user_id ,'')  userId,
		     IFNULL(ppl.user_id,'') userName,
		     IFNULL(ppl.org_code,'') orgCode,
		     IFNULL(ppl.dept_id ,'')groupName,
		     pr.id routeId,
		    IFNULL( pr.`name`,'') routeName,
		     pr.tel,
		    pr.is_delete isDelete,
		     IFNULL(pr.boss,'') boss,
        pr.org_code defaultOrgCode
		FROM
		    (
		        SELECT
		            pp.route_id,
		            pp.id planId,
		            pp. NAME,
		            pp.org_code,
					pp.dept_id,
		            SUBSTRING_INDEX(
		                SUBSTRING_INDEX(
		                    pp.user_id,
		                    ',',
		                    temp.help_topic_id + 1
		                ),
		                ',',
		                - 1
		            ) user_id
		        FROM
		            p_plan pp
		        JOIN mysql.help_topic temp ON temp.help_topic_id <![CDATA[ < ]]> (
		            LENGTH(pp.user_id) - LENGTH(REPLACE(pp.user_id, ',', '')) + 1
		        )
		    ) ppl
		RIGHT JOIN p_route pr ON pr.id = ppl.route_id
		<!-- LEFT JOIN s_user u ON u.id = ppl.user_id
		LEFT JOIN s_user u1 ON u1.id = pr.boss
		LEFT JOIN s_department sd ON sd.id = u.department_id -->
		WHERE
		    pr.id = #{routeId}
    </select>
    
     
    <select id="queryRoutePointItemByCondition" resultType="com.yeejoin.amos.patrol.core.common.response.PointInputItemRespone">
		SELECT
			CONCAT(ppi.id,(case WHEN ppc.id IS NULL THEN '' ELSE CONCAT('-',ppc.id) END)) tempId,
			ppi.point_id pointId,
			ppi.order_no orderNo,
			ppi.id pointInputItemId,
			pii.`name` inputName,
			ppc.`name` classifyName,
			ppc.id pointClassifyId,
			pii.`level`,
			pii.item_type itemType,
			pii.is_must isMust,
			pii.is_score isScore,
			pii.item_no itemNo,
			temp.routePointId,
			temp.routePointItemId id,
			temp.routeId,
				CASE
						WHEN temp.routePointItemId is null THEN
							'noBound'
						ELSE
							'bound'
						END isBound
		FROM
			p_point_inputitem ppi
		LEFT JOIN p_input_item pii ON pii.id = ppi.input_item_id
		LEFT JOIN p_point_classify ppc ON FIND_IN_SET(ppc.id, ppi.classify_ids) > 0
		LEFT JOIN (
		SELECT
			prpi.id routePointItemId,
			prpi.point_classify_id pointClassifyId,
			prpi.point_input_item_id pointInputItemId,
-- 			prpi.create_date createDate,
-- 			prpi.creator_id  prpi.creator_id,
		    prp.id routePointId,
			prp.route_id routeId,
			prp.point_id pointId
		FROM
			p_route_point_item prpi
		LEFT JOIN p_route_point prp ON prp.id = prpi.route_point_id
		WHERE
			prp.route_id = #{routeId}
		) temp ON temp.pointInputItemId = ppi.id AND temp.pointId = ppi.point_id AND  IFNULL(temp.pointClassifyId,0) = IFNULL(ppc.id,0)
		
		WHERE
			ppi.point_id = #{pointId}
			<!-- <if test="level!=null and level !=''">
				and pii.`level` = #{level}
			</if> -->
		<choose>
	     <when test="level!=null and level !='' and level > 0">and pii.`level` = #{level}</when> 
	      <when test="level== -1"> and pii.`level` is null</when>
	    </choose>
			<if test="classifyId!=null and classifyId !=''">
				and ppc.id = #{classifyId}
			</if>
			<if test="inputName!=null and inputName !=''">
				and (pii.`name` like CONCAT('%', #{inputName}, '%')
				or pii.`item_no` like CONCAT('%', #{inputName}, '%'))
			</if>
			<if test="isBound!=null and isBound !=''">
				and temp.routePointItemId >0
			</if>
		ORDER BY
			pii.id
    </select>
    
    <select id="queryRoutesByOrgCode" resultType="com.yeejoin.amos.patrol.dao.entity.Route">
		SELECT * FROM p_route r where r.is_delete = 0 and r.org_code = #{orgCode}
	</select>
</mapper>