def load_new_bus_stations_routes(self) -> Dict:
start = time.time()
try:
with transaction(self.cds_db_project.transaction_manager()) as tr:
cur = tr.cursor()
cur.execute(
'''select bsr."Num" as NUMBER_, nbs."Name" as NAME_, nbs."Latitude" as LAT_,
nbs."Longitude" as LON_,
bsr."RouteId" as ROUT_, 0 as CONTROL_,
bsr."BusStationId" as ID
from "NewRoute" r
join "NewBusStationRoute" bsr on bsr."RouteId" = r."Id"
left join "NewBusStation" nbs on bsr."BusStationId" = nbs."Id"
''')
bus_stops_data = fetch_cursor_map(cur)
end = time.time()
self.logger.info(
f"Finish fetch data. Elapsed: {end - start:.2f}")
except firebird.driver.DatabaseError as db_error:
self.logger.error(db_error)
self.try_reconnect()
return {}
result = self.convert_to_stations_dict(
self.load_new_codd_route_names(), bus_stops_data)
return result
def load_bus_stations_routes(self) -> Dict:
start = time.time()
try:
with transaction(self.cds_db_project.transaction_manager()) as tr:
cur = tr.cursor()
cur.execute(
'''select bsr.NUM as NUMBER_, bs.NAME as NAME_, bs.LAT as LAT_,
bs.LON as LON_, bsr.ROUTE_ID as ROUT_, 0 as CONTROL_, bsr.BS_ID as ID
from ROUTS r
join BS_ROUTE bsr on bsr.ROUTE_ID = r.ID_
left join BS on bsr.BS_ID = bs.ID''')
bus_stops_data = fetch_cursor_map(cur)
end = time.time()
self.logger.info(
f"Finish fetch data. Elapsed: {end - start:.2f}")
except firebird.driver.DatabaseError as db_error:
self.logger.error(db_error)
self.try_reconnect()
return {}
routes_dict = {
k: v.ID_
for k, v in self.load_codd_route_names().items()
}
result = self.convert_to_stations_dict(routes_dict, bus_stops_data)
return result
def load_bus_stops(self) -> List[BusStop]:
self.logger.debug('Execute load_bus_stops from DB')
start = time.time()
try:
with transaction(self.cds_db_project.transaction_manager()) as tr:
cur = tr.cursor()
cur.execute(
'''select distinct ID, NAME as NAME_, LAT as LAT_, LON as LON_, AZMTH
from bs
order by NAME_''')
self.logger.debug('Finish execution')
result = fetch_cursor_map(cur)
end = time.time()
self.logger.info(
f"Finish fetch data. Elapsed: {end - start:.2f}")
except firebird.driver.DatabaseError as db_error:
self.logger.error(db_error)
self.try_reconnect()
return []
return [BusStop(**x) for x in result]
def load_codd_route_names(self) -> Dict:
self.logger.debug('Execute fetch routes from DB')
start = time.time()
try:
with transaction(self.cds_db_project.transaction_manager()) as tr:
cur = tr.cursor()
cur.execute('''select ID_, NAME_, ROUTE_ACTIVE_ from ROUTS
order by NAME_''')
self.logger.debug('Finish execution')
result = fetch_cursor_map(cur)
# tr.commit()
# cur.close()
end = time.time()
self.logger.info(
f"Finish fetch data. Elapsed: {end - start:.2f}")
except firebird.driver.DatabaseError as db_error:
self.logger.error(db_error)
self.try_reconnect()
return {}
result = [CoddBus(**x) for x in result]
end = time.time()
self.logger.info(f"Finish proccess. Elapsed: {end - start:.2f}")
return {x.NAME_: x for x in result}
def load_new_codd_route_names(self):
self.logger.debug('Execute fetch routes from DB')
start = time.time()
try:
with transaction(self.cds_db_project.transaction_manager()) as tr:
cur = tr.cursor()
cur.execute(
'''select "Id" as ID_, "Name" as NAME_ from "NewRoute"
where "NewRouteStatusID" <> 3
order by NAME_''')
self.logger.debug('Finish execution')
result = fetch_cursor_map(cur)
end = time.time()
self.logger.info(
f"Finish fetch data. Elapsed: {end - start:.2f}")
except firebird.driver.DatabaseError as db_error:
self.logger.error(db_error)
self.try_reconnect()
return {}
result = [CoddBus(**x) for x in result]
end = time.time()
self.logger.info(f"Finish proccess. Elapsed: {end - start:.2f}")
return {x.NAME_: x.ID_ for x in result}
def load_all_cds_buses(self) -> List[CdsRouteBus]:
def make_names_lower(x):
return {k.lower(): v for (k, v) in x.items()}
self.logger.debug('Execute fetch all from DB')
start = time.time()
try:
with transaction(self.cds_db_project.transaction_manager()) as tr:
cur = tr.cursor()
cur.execute(
'''SELECT bs.NAME_ AS BUS_STATION_, rt.NAME_ AS ROUTE_NAME_, o.NAME_, o.OBJ_ID_, o.LAST_TIME_,
o.LAST_LON_, o.LAST_LAT_, o.LAST_SPEED_, o.LAST_STATION_TIME_, o.PROJ_ID_,
coalesce(o.lowfloor, 0) as low_floor, coalesce(o.VEHICLE_TYPE_, 0) as bus_type,
coalesce(obj_output_, 0) as obj_output,
coalesce(azmth_, 0) as azimuth,
coalesce(o."BortName", '') as bort_name
FROM OBJECTS O LEFT JOIN BUS_STATIONS bs
ON o.LAST_ROUT_ = bs.ROUT_ AND o.LAST_STATION_ = bs.NUMBER_
LEFT JOIN ROUTS rt ON o.LAST_ROUT_ = rt.ID_''')
self.logger.debug('Finish execution')
result = fetch_cursor_map(cur)
end = time.time()
self.logger.info(
f"Finish fetch data. Elapsed: {end - start:.2f}")
except firebird.driver.DatabaseError as db_error:
self.logger.error(db_error)
self.try_reconnect()
return []
obl_result = []
try:
if self.load_obl_objects:
with transaction(self.cds_db_data.transaction_manager()) as tr:
cur = tr.cursor()
cur.execute(
'''SELECT bs.NAME AS BUS_STATION_, rt.NAME_ AS ROUTE_NAME_, o.block_number as OBJ_ID_,
CAST(o.block_number as VARCHAR(10)) as NAME_, o.LAST_TIME as LAST_TIME_,
o.LON as LAST_LON_, o.LAT as LAST_LAT_, 0 as LAST_SPEED_, NULL as LAST_STATION_TIME_, NULL as PROJ_ID_,
0 as low_floor,
0 as bus_type,
0 as obj_output,
coalesce(o.azimuth, 0) as azimuth,
coalesce(o."BortName", '') as bort_name
FROM OBL_OBJECTS O LEFT JOIN BS
ON o.bs_id = bs.ID
LEFT JOIN ROUTS rt ON o.route_id = rt.ID_''')
self.logger.debug('Finish execution')
obl_result = fetch_cursor_map(cur)
end = time.time()
self.logger.info(
f"Finish fetch data. Elapsed: {end - start:.2f}")
except firebird.driver.DatabaseError as db_error:
if self.load_obl_objects:
self.load_obl_objects = False
self.logger.error(db_error)
self.try_reconnect()
result = [
CdsRouteBus(**make_names_lower(x)) for x in result + obl_result
]
result.sort(key=lambda s: s.last_time_, reverse=True)
end = time.time()
self.logger.info(f"Finish proccess. Elapsed: {end - start:.2f}")
return result