def get_next_user_trip_id(self, user_id, date_range_start, date_range_end):
qstr = """SELECT max(id) as max_id
FROM trips_alts
WHERE user_id = {0} AND (user_id, id, plan_id) NOT IN
(SELECT user_id, id, plan_id FROM trips_alts
WHERE user_id = {0}
AND start_time >= '{1}' AND end_time <= '{2}');
""".format(user_id,
CommonHelper.DateTime_to_Text(date_range_start),
CommonHelper.DateTime_to_Text(date_range_end))
log(["get_next_user_trip_id():: qstr:", qstr])
log("")
res, maxid_rows = self.db_command.ExecuteSQL(qstr)
if maxid_rows.rowcount > 0:
for maxid_row in maxid_rows:
if maxid_row['max_id'] == None:
return 0
else:
return maxid_row['max_id']
return 0
def delete_trip_plan(self, trip, mode, numItineraries, maxWalkDistance):
qstr = """DELETE FROM trip_plans
WHERE start_time = '{0}' AND
origin = '{1}' AND destination = '{2}' AND
mode = '{3}' AND max_walk_distance = {4} AND no_of_itins = {5};
""".format(
CommonHelper.DateTime_to_Text(trip.starttime),
CommonHelper.pointRow_to_postgisPoint(trip.origin),
CommonHelper.pointRow_to_postgisPoint(trip.destination), mode,
maxWalkDistance, numItineraries)
res, db_res = self.db_command.ExecuteSQL(qstr,
LOG_IMPORTANT_CUSTOM=False)
return res, self._get_delete_macthed_count(res, db_res)
def store_trip_plan(self, trip, mode, numItineraries, maxWalkDistance,
plan):
qstr = """INSERT INTO trip_plans (start_time, origin, destination, mode, max_walk_distance, no_of_itins, plan)
VALUES ('{0}',
ST_GeomFromText('{1}'), ST_GeomFromText('{2}'),
'{3}',{4},{5},
'{6}' ); """.format(
CommonHelper.DateTime_to_Text(trip.starttime),
CommonHelper.pointRow_to_postgisPoint(trip.origin),
CommonHelper.pointRow_to_postgisPoint(trip.destination), mode,
maxWalkDistance, numItineraries, CommonHelper.json_to_sqlstr(plan))
res, db_res = self.db_command.ExecuteSQL(qstr,
LOG_IMPORTANT_CUSTOM=False)
return res
def load_trip_plan(self, trip, mode, numItineraries, maxWalkDistance):
qstr = """SELECT start_time,
ST_AsText(origin) as origin, ST_AsText(destination) as destination,
mode, max_walk_distance, no_of_itins,
plan
FROM trip_plans
WHERE start_time = '{0}' AND
origin = '{1}' AND destination = '{2}' AND
mode = '{3}' AND max_walk_distance = {4} AND no_of_itins = {5};
""".format(
CommonHelper.DateTime_to_Text(trip.starttime),
CommonHelper.pointRow_to_postgisPoint(trip.origin),
CommonHelper.pointRow_to_postgisPoint(trip.destination), mode,
maxWalkDistance, numItineraries)
res, plan_rows = self.db_command.ExecuteSQL(qstr,
LOG_IMPORTANT_CUSTOM=False)
return res, plan_rows