def get(self):
args = searchParser.parse_args()
per_page = 50
offset = 0
tagId = None
if args['per_page'] is not None:
try:
per_page = limit_int(int(args['per_page']), 0, 100)
except ValueError:
pass
if args['page'] is not None:
try:
offset = limit_int(int(args['page']) * per_page, 0)
except ValueError:
pass
if args['tagId'] is not None:
try:
tagId = limit_int(int(args['tagId']), 0)
except ValueError:
pass
if tagId is not None:
SQL = "SELECT v.* FROM vehicles as v JOIN tags as t ON v.id = t.vehicle_id where t.id = %s;"
data = (tagId, )
else:
SQL = "SELECT * FROM vehicles order by id limit %s offset %s;"
data = (per_page, offset)
conn = get_db()
cur = conn.cursor()
cur.execute(SQL, data)
# row = cur.fetchone()
rows = cur.fetchall()
if rows == None:
print("There are no results for this query")
rows = []
columns = [desc[0] for desc in cur.description]
result = []
for row in rows:
row = dict(zip(columns, row))
result.append(row)
conn.commit()
cur.close()
return jsonify(result)
def get(self):
args = searchParser.parse_args()
per_page = 50;
offset = 0;
dump = False;
if args['per_page'] is not None:
try:
per_page=limit_int(int(args['per_page']), 0, 100)
except ValueError:
pass
if args['page'] is not None:
try:
offset=limit_int(int(args['page']) * per_page, 0)
except ValueError:
pass
if args['dump'] is not None:
if args['dump'] == 'true':
dump = True
print("DUMP is "+str(dump))
conn = get_db()
cur = conn.cursor()
SQL="SELECT k.\"UID\" as id, u.username, u.first_name, u.last_name, u.email, u.is_staff, u.is_active, u.date_joined, u.nickname, u.language_preference FROM {} as u LEFT JOIN {} as k ON k.user_id = u.id order by id limit %s offset %s;"
SQL = sql.SQL(SQL).format(sql.Identifier(TABLE_NAMES['users']), sql.Identifier(TABLE_NAMES['users_mapping']))
data = (per_page, offset)
# if dump is true compose all users/vehicles/tags and output them
if dump:
SQL="SELECT k.\"UID\" as id, u.id as numeric_id, u.username, u.first_name, u.last_name, u.email, u.is_staff, u.is_active, u.date_joined, u.nickname, u.language_preference FROM {} as u LEFT JOIN {} as k ON k.user_id = u.id order by id asc;"
SQL = sql.SQL(SQL).format(sql.Identifier(TABLE_NAMES['users']), sql.Identifier(TABLE_NAMES['users_mapping']))
data = None
cur.execute(SQL, data)
# row = cur.fetchone()
rows = cur.fetchall()
if rows == None:
print("There are no results for this query")
rows = []
columns = [desc[0] for desc in cur.description]
result = []
for row in rows:
row = dict(zip(columns, row))
result.append(row)
if dump:
for i in result:
i['vehicles'] = []
print(json.dumps(i))
SQL="""Select v.id as id, v.lastupdate, 1 as type, v.nickname as name, CASE WHEN vs.lost = true THEN 1 ELSE 0 END as status, v.picture_gallery_id, v.owner_id as owner,
CASE WHEN vp.position IS NOT NULL THEN
jsonb_build_object(
'type', 'Feature',
'id', vp.id,
'geometry', ST_AsGeoJSON(vp.position)::jsonb,
'properties', CASE WHEN vp.reporter_id IS NOT NULL THEN json_build_object(
'reporter_id', vp.reporter_id
) ELSE '{{}}' END
)
ELSE NULL
END as lastposition
FROM {} as v
LEFT JOIN
(
SELECT vs1.bike_id, vs1.lost
FROM vehicles_bikestatus vs1
LEFT JOIN vehicles_bikestatus vs2 ON vs1.bike_id = vs2.bike_id AND vs1.creation_date < vs2.creation_date
WHERE vs2.creation_date IS NULL
) as vs
ON vs.bike_id = v.id
LEFT JOIN
(
SELECT vp1.id, vp1.bike_id, vp1.position, vp1.reporter_id
FROM {} vp1
LEFT JOIN {} vp2 ON vp1.bike_id = vp2.bike_id AND vp1.observed_at < vp2.observed_at
WHERE vp2.observed_at IS NULL
) as vp
ON vp.bike_id = v.id
WHERE owner_id = %s order by id asc;"""
SQL = sql.SQL(SQL).format(sql.Identifier(TABLE_NAMES['vehicles']), sql.Identifier(TABLE_NAMES['vehiclemonitor_bikeobservation']), sql.Identifier(TABLE_NAMES['vehiclemonitor_bikeobservation']))
data = (i['numeric_id'],)
cur.execute(SQL, data)
vehicles = cur.fetchall()
if vehicles == None:
print("There are no results for vehicles query")
vehicles = []
v_columns = [desc[0] for desc in cur.description]
for v in vehicles:
v = dict(zip(v_columns, v))
#Fill the tags
v['tags'] = []
#print(json.dumps(v))
SQL = "SELECT epc FROM {} where bike_id = %s order by epc;"
SQL = sql.SQL(SQL).format(sql.Identifier(TABLE_NAMES['tags']))
data = (v['id'],)
cur.execute(SQL, data)
tags = cur.fetchall()
if tags == None:
print("There are no tags for this vehicles")
tags = []
t_columns = [desc[0] for desc in cur.description]
for t in tags:
t = dict(zip(t_columns, t))
print(json.dumps(t))
v['tags'].append(t)
#Fill the images
v['images'] = []
print(json.dumps(v))
SQL = "SELECT concat('https://dev.savemybike.geo-solutions.it/media/', image) url FROM public.photologue_photo pp LEFT JOIN public.photologue_gallery_photos pgp on pp.id = pgp.photo_id where pgp.gallery_id = %s"
data = (v['picture_gallery_id'],)
cur.execute(SQL, data)
images = cur.fetchall()
if images == None:
print("There are no images for this vehicles")
images = []
#t_columns = [desc[0] for desc in cur.description]
for img in images:
#t = dict(zip(t_columns, t))
print(json.dumps(img))
v['images'].append(img[0])
i['vehicles'].append(v)
del i['numeric_id']
conn.commit()
cur.close()
return jsonify(result)
def get(self, user_id):
args = searchParser.parse_args()
per_page = 50
offset = 0
tagId = None
if args['per_page'] is not None:
try:
per_page = limit_int(int(args['per_page']), 0, 100)
except ValueError:
pass
if args['page'] is not None:
try:
offset = limit_int(int(args['page']) * per_page, 0)
except ValueError:
pass
if args['tagId'] is not None:
try:
tagId = limit_int(int(args['tagId']), 0)
except ValueError:
pass
if tagId is not None:
SQL = "SELECT v.* FROM {} as v JOIN {} as t ON v.id = t.vehicle_id where t.epc = %s;"
SQL = sql.SQL(SQL).format(sql.Identifier(TABLE_NAMES['vehicles']),
sql.Identifier(TABLE_NAMES['tags']))
data = (tagId, )
else:
SQL = """Select v.id as id, v.lastupdate, 1 as type, v.nickname as name, CASE WHEN vs.lost = true THEN 1 ELSE 0 END as status, v.picture_gallery_id, k.kuid as owner,
CASE WHEN vp.position IS NOT NULL THEN
jsonb_build_object(
'type', 'Feature',
'id', vp.id,
'geometry', ST_AsGeoJSON(vp.position)::jsonb,
'properties', CASE WHEN vp.reporter_id IS NOT NULL THEN json_build_object(
'reporter_id', vp.reporter_id
) ELSE '{{}}' END
)
ELSE NULL
END as lastposition
FROM {} as v
LEFT JOIN
(
SELECT vs1.bike_id, vs1.lost
FROM vehicles_bikestatus vs1
LEFT JOIN vehicles_bikestatus vs2 ON vs1.bike_id = vs2.bike_id AND vs1.creation_date < vs2.creation_date
WHERE vs2.creation_date IS NULL
) as vs
ON vs.bike_id = v.id
LEFT JOIN
(
SELECT vp1.id, vp1.bike_id, vp1.position, vp1.reporter_id
FROM {} vp1
LEFT JOIN {} vp2 ON vp1.bike_id = vp2.bike_id AND vp1.observed_at < vp2.observed_at
WHERE vp2.observed_at IS NULL
) as vp
ON vp.bike_id = v.id
LEFT JOIN
(
SELECT \"UID\" as kuid, user_id as portal_id
FROM {} as u
) as k
ON k.portal_id = v.owner_id
WHERE k.kuid = %s order by k.kuid limit %s offset %s;"""
SQL = sql.SQL(SQL).format(
sql.Identifier(TABLE_NAMES['vehicles']),
sql.Identifier(TABLE_NAMES['vehiclemonitor_bikeobservation']),
sql.Identifier(TABLE_NAMES['vehiclemonitor_bikeobservation']),
sql.Identifier(TABLE_NAMES['users_mapping']))
data = (user_id, per_page, offset)
conn = get_db()
cur = conn.cursor()
cur.execute(SQL, data)
# row = cur.fetchone()
rows = cur.fetchall()
if rows == None:
print("There are no results for this query")
rows = []
columns = [desc[0] for desc in cur.description]
result = []
for row in rows:
row = dict(zip(columns, row))
result.append(row)
conn.commit()
cur.close()
return jsonify(result)