def post(self, vehicle_id, tag_epc, user_id=None):
content = request.json
print(content)
_id = content.get('epc', 1)
conn = get_db()
cur = conn.cursor()
SQL = "INSERT INTO {} (epc, bike_id, creation_date) SELECT %s as epc, v.id as bike_id, NOW() FROM {} as v WHERE v.id = %s RETURNING epc;"
SQL = sql.SQL(SQL).format(sql.Identifier(TABLE_NAMES['tags']), sql.Identifier(TABLE_NAMES['vehicles']))
data = (_id, vehicle_id )
id_of_new_row = None
error_message = ""
try:
cur.execute(SQL, data)
except Exception as e:
print(e)
if hasattr(e, 'diag') and hasattr(e.diag, 'message_detail') :
error_message = e.diag.message_detail
else :
error_message = "Database error"
conn.rollback()
else:
conn.commit()
id_of_new_row = cur.fetchone()[0]
cur.close()
# TODO : 409 Conflict if tagId already exists
if id_of_new_row is None : return {"Error" : error_message}, 404
return id_of_new_row, 201
def get(self, vehicle_id, user_id=None):
# args = searchParser.parse_args()
conn = get_db()
cur = conn.cursor()
SQL = "SELECT epc FROM {} t left join {} v on t.bike_id = v.id where v.id = %s order by epc limit 50;"
SQL = sql.SQL(SQL).format(sql.Identifier(TABLE_NAMES['tags']), sql.Identifier(TABLE_NAMES['vehicles']))
data = (vehicle_id,) # keep the comma to make it a tuple
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 delete(self, tag_epc, user_id=None):
content = request.json
print(content)
_id = content.get('epc', 1)
conn = get_db()
cur = conn.cursor()
SQL = "DELETE FROM {} WHERE epc = %s RETURNING epc;"
SQL = sql.SQL(SQL).format(sql.Identifier(TABLE_NAMES['tags']))
data = (tag_epc, )
try:
cur.execute(SQL, data)
except Exception as e:
print(e)
conn.rollback()
else:
conn.commit()
cur.close()
return {"Result": "Deleted"}, 204
def post(self):
content = request.json
print(content)
name = content.get('name', None)
status = content.get('status', 0)
lastposition = content.get('lastposition', None)
image = content.get('image', None)
owner = content.get('owner', None)
conn = get_db()
cur = conn.cursor()
SQL = "INSERT INTO {} ( nickname, picture_gallery_id, owner_id) VALUES (%s, %s, %s) RETURNING id;"
SQL = sql.SQL(SQL).format(sql.Identifier(TABLE_NAMES['vehicles']))
data = (name, image, owner)
cur.execute(SQL, data)
id_of_new_row = cur.fetchone()[0]
# TODO: insert lastposition in the datapoints table
conn.commit()
cur.close()
return id_of_new_row, 201
def delete(self, vehicle_id, user_id=None):
conn = get_db()
cur = conn.cursor()
SQL = "DELETE FROM {} WHERE id = %s;"
SQL = sql.SQL(SQL).format(sql.Identifier(TABLE_NAMES['vehicles']))
data = (vehicle_id, )
cur.execute(SQL, data)
conn.commit()
cur.close()
return '', 204
def get(self, user_id):
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 where k.\"UID\" = %s limit 1;"
SQL = sql.SQL(SQL).format(sql.Identifier(TABLE_NAMES['users']), sql.Identifier(TABLE_NAMES['users_mapping']))
data = (user_id,) # keep the comma to make it a tuple
cur.execute(SQL, data)
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)
def post(self, vehicle_id, user_id=None):
content = request.json #: :type content: dict
print(' -- content -- ')
print(content)
if content is None: return None, 304
name = content.get('name', None)
status = content.get('status', 0)
lastposition = content.get('lastposition', None)
image = content.get('image', None)
owner = content.get('owner', None) #: :type owner: tuple
conn = get_db()
cur = conn.cursor()
SQL = "Select id from {} where id::text = %s"
SQL = sql.SQL(SQL).format(sql.Identifier(TABLE_NAMES['vehicles']))
data = (vehicle_id, )
cur.execute(SQL, data)
query_results = cur.fetchone()
if query_results is None:
return {"Error": "Cannot find vehicle"}, 500
vehicle_uuid = query_results[0]
# update the position
if lastposition is not None:
# parse GeoJSON
# position = json.loads(lastposition)
# print(json.dumps(lastposition))
print(' -- lastposition -- ')
print(lastposition)
if lastposition['type'] == 'Feature':
print(' -- geometry -- ')
print(lastposition['geometry'])
print(' -- coordinates -- ')
print(lastposition['geometry']['coordinates'])
lon = lastposition['geometry']['coordinates'][0]
lat = lastposition['geometry']['coordinates'][1]
try:
reporter_id = lastposition['properties']['reporter_id']
except:
#TODO use the id of the actual user using this API
reporter_id = 1
try:
reporter_name = lastposition['properties']['reporter_name']
except:
#TODO use the id of the actual user using this API
reporter_name = 1
try:
reporter_type = lastposition['properties']['reporter_type']
except:
#TODO use the id of the actual user using this API
reporter_type = 1
SQL = "INSERT INTO {} (bike_id, position, reporter_id, reporter_name, reporter_type, created_at, observed_at, details, address) VALUES ( %s, ST_SetSRID(ST_Point(%s, %s), 4326), %s, %s, %s, now(), now(), '', '') returning id;"
SQL = sql.SQL(SQL).format(
sql.Identifier(
TABLE_NAMES['vehiclemonitor_bikeobservation']))
data = (vehicle_uuid, lon, lat, reporter_id, reporter_name,
reporter_type)
cur.execute(SQL, data)
id_of_new_row = cur.fetchone()[0]
print('new datapoint row: %s' % (id_of_new_row, ))
else:
return {
'Error':
"Please provide 'lastposition' as a valid GeoJSON point"
}, 500
inputslist = []
SQL = "UPDATE {} SET lastupdate = now()"
if 'name' in content:
SQL += ', nickname = %s'
inputslist.append(name)
if 'status' in content:
# TODO INSERT THE NEW STATUS TO THE DATABASE
None
if 'image' in content:
SQL += ', picture_gallery_id = %s'
inputslist.append(image)
if 'owner' in content:
SQL += ', owner_id = %s'
inputslist.append(owner)
SQL += " where id = %s RETURNING id;"
SQL = sql.SQL(SQL).format(sql.Identifier(TABLE_NAMES['vehicles']))
inputslist.append(vehicle_id)
data = tuple(inputslist)
cur.execute(SQL, data)
id_of_new_row = cur.fetchone()[0]
conn.commit()
cur.close()
return id_of_new_row, 201
def get(self, vehicle_id, user_id=None):
conn = get_db()
cur = conn.cursor()
#SQL = "SELECT v.*, ST_AsGeoJSON(d.the_geom) as last_position FROM vehicles v LEFT JOIN datapoints d on d.vehicle_id = v._id where v.id = %s order by d.timestamp desc limit 1;"
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 v.id = %s;"""
SQL = sql.SQL(SQL).format(
sql.Identifier(TABLE_NAMES['vehicles']),
sql.Identifier(TABLE_NAMES['vehiclemonitor_bikeobservation']),
sql.Identifier(TABLE_NAMES['vehiclemonitor_bikeobservation']))
data = (vehicle_id,
) # using vehicle id , not uuid (uuid will be used in V2.0)
try:
cur.execute(SQL, data)
except Exception as error:
print(error)
return jsonify([])
rows = cur.fetchall()
print(rows)
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))
print(json.dumps(row))
result.append(row)
conn.commit()
cur.close()
return jsonify(result)