<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.tsy.data.idsse.dsds.mapper.voyage.VoyageRegMapper">
 
  <!-- 分页查询 -->
  <select id="voyageWithPage" resultType="map">
	select r.*,member_total from voyage_reg r
	left join (select voyage_id,count(1) member_total from voyage_member group by voyage_id) m 
	on r.tsy_id = m.voyage_id 
	where 1=1  
	<if test="query != null and query != ''">
      and (voyage_name like concat('%',#{query},'%')  or voyage_officer like concat('%',#{query},'%')  
       or voyage_officer like concat('%',#{query},'%') or voyage_remark like concat('%',#{query},'%')) 
    </if>
    <if test="order != null and order != ''">
		ORDER BY ${order} 
	</if>
	<if test="type != null and (type == 'DESC')">
	 	DESC
	</if>
  </select>
  
  
   <select id="searchWithPage" resultType="map">
	select v.tsy_id id,v.voyage_name name from voyage_reg v
	<!-- 如果有平台类型，需要去查找有平台类型的航次 -->
	<if test="param.platform_type != null and param.platform_type != ''">
		,(select distinct voyage_id from sample_dataset where platform_type=#{param.platform_type}) p1		
	</if>	
	<if test="param.platform_name != null and param.platform_name != ''">
		,(select distinct voyage_id from sample_dataset where platform_name=#{param.platform_name}) p2		
	</if>
	<if test="param.equipment_type != null and param.equipment_type != ''">
		,(select distinct voyage_id from sample_dataset where equipment_type=#{param.equipment_type}) p3		
	</if>
	<if test="param.data_type != null and param.data_type != ''">
		,(select distinct voyage_id from sample_dataset where sample_type=#{param.sample_type}) p4		
	</if>
	where 1=1 
	<if test="param.platform_type != null and param.platform_type != ''">
		and p1.voyage_id = v.tsy_id		 
	</if>	
	<if test="param.platform_name != null and param.platform_name != ''">
		and p2.voyage_id = v.tsy_id		 	
	</if>
	<if test="param.equipment_type != null and param.equipment_type != ''">
		and p3.voyage_id = v.tsy_id			
	</if>
	<if test="param.data_type != null and param.data_type != ''">
		and p4.voyage_id = v.tsy_id			
	</if>
	order by v.voyage_name
  </select>
  
 <select id="totalVoyage" resultType="map">
  	SELECT count(1) voyage_total,sum(DATEDIFF(voyage_end_date, VOYAGE_START_DATE)) day_total,sum(ifnull(voyage_mileage,0)) mileage_total from voyage_reg where 1=1 
  		<if test="start_date != null ">
			and voyage_start_date &gt;= #{start_date}
		</if>

		<if test="end_date != null ">
			and voyage_end_date &lt;= #{end_date}
		</if>
  </select>
  
</mapper>