<select id="getNightOptimizationOwnerServiceListByPasser" resultMap="OwnerResultMap" parameterType="">
SELECT
union_owner.*
FROM
(SELECT
vehicle.vehicle_id,
vehicle.vehicle_type,
sys.user_mobile,
sys.user_nickname,
car.service_id,
car.user_id,
car.pin_service_code,
car.pin_leave_words,
car.pin_start_point,
car.pin_end_point,
car.pin_start_latitude,
car.pin_start_longitude,
car.pin_end_latitude,
car.pin_end_longitude,
car.pin_total_account,
car.pin_service_account,
car.pin_single_lose_account AS pin_lose_account,
car.owner_has_seats,
car.leave_off_time,
car.pin_total_distance,
car.pin_service_kinds,
car.pin_create_time
FROM
yivi_pin_car_service car
INNER JOIN yivi_vehicle_info vehicle ON car.user_id = vehicle.user_id
AND vehicle.vehicle_identity_status = 1
INNER JOIN yivi_sys_user sys ON sys.user_id = car.user_id
AND sys.user_vehicle_identity = 1
WHERE
car.pin_publish_role = 1
AND car.service_enable = 1
AND car.service_status = 1
AND car.pin_is_visible = 1
AND car.pin_time_out = 0
<if test="pinServiceKinds != null and pinServiceKinds != '' ">AND car.pin_service_kinds = #{pinServiceKinds}
</if>
<if test="pinServiceKinds != null and pinServiceKinds != 3 and startLatitude != null and startLongitude != null and endLatitude != null and endLongitude != null"> <!--长途或者旅游 方圆50公里-->
AND ROUND(
6378.138 * 2 * asin(
sqrt(
pow(
sin(
(
car.pin_start_latitude * pi() / 180 - #{startLatitude} * pi() / 180
) / 2
),
2
) + cos(car.pin_start_latitude * pi() / 180) * cos(#{startLatitude} * pi() / 180) * pow(
sin(
(
car.pin_start_longitude * pi() / 180 - #{startLongitude} * pi() / 180
) / 2
),
2
)
)
)
,
2
) <= 100
AND ROUND (
6378.138 * 2 * asin(
sqrt(
pow(
sin(
(
car.pin_end_latitude * pi() / 180 - #{endLatitude} * pi() / 180
) / 2
),
2
) + cos(car.pin_end_latitude * pi() / 180) * cos(#{endLatitude} * pi() / 180) * pow(
sin(
(
car.pin_end_longitude * pi() / 180 - #{endLongitude} * pi() / 180
) / 2
),
2
)
)
)
,
2
) <= 100
</if>
<if test="pinServiceKinds == 3 ">
<if test="startLatitude != null and startLongitude != null">
AND ROUND(
6378.138 * 2 * asin(
sqrt(
pow
(
sin(
(
car.pin_start_latitude * pi() / 180 - #{startLatitude} * pi() / 180
) / 2
),
2
) + cos(car.pin_start_latitude * pi() / 180) * cos(#{startLatitude} * pi() / 180) * pow(
sin(
(
car.pin_start_longitude * pi() / 180 - #{startLongitude} * pi() / 180
) / 2
),
2
)
)
)
,
2
) <= 40
</if>
<if test="endLatitude != null and endLongitude != null ">
AND ROUND(
6378.138 * 2 * asin(
sqrt(
pow(
sin(
(
car.pin_end_latitude * pi() / 180 - #{endLatitude} * pi() / 180
) / 2
),
2
) + cos(car.pin_end_latitude * pi() / 180) * cos(#{endLatitude} * pi() / 180) * pow(
sin(
(
car.pin_end_longitude * pi() / 180 - #{endLongitude} * pi() / 180
) / 2
),
2
)
)
)
,
2
) <= 40
</if>
</if>
<if test="leaveOffTime != null ">
and date_format(car.leave_off_time,'%Y-%m-%d')= date_format(#{leaveOffTime},'%Y-%m-%d')
</if>
<if test="leftLeaveOffTime!= null ">
and UNIX_TIMESTAMP(car. leave_off_time) >= UNIX_TIMESTAMP(date_format(#{leftLeaveOffTime},'%Y-%m-%d %H:%i'))
</if>
<if test="rightLeaveOffTime!= null ">
and UNIX_TIMESTAMP(car. leave_off_time) <= UNIX_TIMESTAMP(date_format(#{rightLeaveOffTime},'%Y-%m-%d %H:%i'))
</if>
<if test="vehicleType != null and vehicleType != 0">
and vehicle.vehicle_type = #{vehicleType}
</if>
<if test="pinServiceKinds != null and pinServiceKinds == 1">
UNION
SELECT
vehicle.vehicle_id,
vehicle.vehicle_type,
sys.user_mobile,
sys.user_nickname,
car.service_id,
car.user_id,
car.pin_service_code,
car.pin_leave_words,
car.pin_start_point,
car.pin_end_point,
car.pin_start_latitude,
car.pin_start_longitude,
car.pin_end_latitude,
car.pin_end_longitude,
car.pin_total_account,
car.pin_service_account,
car.pin_single_lose_account AS pin_lose_account,
car.owner_has_seats,
car.leave_off_time,
car.pin_total_distance,
car.pin_service_kinds,
car.pin_create_time
FROM
yivi_pin_car_service car
INNER JOIN yivi_vehicle_info vehicle ON car.user_id = vehicle.user_id
AND vehicle.vehicle_identity_status = 1
INNER JOIN yivi_sys_user sys ON sys.user_id = car.user_id
AND sys.user_vehicle_identity = 1
WHERE
car.pin_publish_role = 1
AND car.service_enable = 1
AND car.service_status = 1
AND car.pin_is_visible = 1
AND car.pin_time_out = 0
AND car.pin_service_kinds = 2
AND car.pin_total_distance > 100
</if>
<if test="pinServiceKinds != null and pinServiceKinds == 3">
UNION
SELECT
vehicle.vehicle_id,
vehicle.vehicle_type,
sys.user_mobile,
sys.user_nickname,
car.service_id,
car.user_id,
car.pin_service_code,
car.pin_leave_words,
car.pin_start_point,
car.pin_end_point,
car.pin_start_latitude,
car.pin_start_longitude,
car.pin_end_latitude,
car.pin_end_longitude,
car.pin_total_account,
car.pin_service_account,
car.pin_single_lose_account AS pin_lose_account,
car.owner_has_seats,
car.leave_off_time,
car.pin_total_distance,
car.pin_service_kinds,
car.pin_create_time
FROM
yivi_pin_car_service car
INNER JOIN yivi_vehicle_info vehicle ON car.user_id = vehicle.user_id
AND vehicle.vehicle_identity_status = 1
INNER JOIN yivi_sys_user sys ON sys.user_id = car.user_id
AND sys.user_vehicle_identity = 1
WHERE
car.pin_publish_role = 1
AND car.service_enable = 1
AND car.service_status = 1
AND car.pin_is_visible = 1
AND car.pin_time_out = 0
AND car.pin_service_kinds = 2
AND car.pin_total_distance < 100
</if>
) union_owner
ORDER BY union_owner.leave_off_time ASC <!--以出发时间为准，降序-->
LIMIT ${pageNo},
${pageSize}
</select>