def get(self, id=None, start_date=None, end_date=None):
if id:
return runSQL('''
SELECT {fields}, users.name || ' ' || users.surname as user_name, places.name as place_name, item_type.name as itemtype_name
FROM items, users, places, item_type
WHERE items.user_id = users.id
AND items.place_id = places.id
AND items.itemtype_id = item_type.id
AND items.id={id}
ORDER BY start_date, place_id;
'''.format(id=id, fields=app.config['SQL_DEFAULT_FIELDS'])
), 200 # HTTP status code 200 OK
elif start_date:
return runSQL('''
SELECT {fields}, users.name || ' ' || users.surname as user_name, places.name as place_name, item_type.name as itemtype_name
FROM items, users, places, item_type
WHERE items.user_id = users.id
AND items.place_id = places.id
AND items.itemtype_id = item_type.id
AND date(start_date) >= '{start_date}'
AND date(end_date) <= '{end_date}'
ORDER BY start_date, place_id;
'''.format(start_date=start_date,
end_date=end_date or start_date,
fields=app.config['SQL_DEFAULT_FIELDS'])), 200
else:
return runSQL('''
SELECT {fields}, users.name || ' ' || users.surname as user_name, places.name as place_name, item_type.name as itemtype_name
FROM items, users, places, item_type
WHERE items.user_id = users.id
AND items.place_id = places.id
AND items.itemtype_id = item_type.id
ORDER BY start_date, place_id;
'''.format(fields=app.config['SQL_DEFAULT_FIELDS'])), 200
def get(self, place_id):
# if not gc_synced():
# gc_sync_db()
# ongoing event
a = runSQL('''
SELECT {fields}
FROM items
WHERE place_id={place_id}
AND itemtype_id=1
AND datetime(start_date) <= datetime('now')
AND datetime('now') <= datetime(end_date)
ORDER BY id_remote DESC, start_date
LIMIT 1;
'''.format(place_id=place_id, fields=app.config['SQL_DEFAULT_FIELDS']))
# upcoming event
b = runSQL('''
SELECT {fields}
FROM items
WHERE place_id={place_id}
AND itemtype_id=1
AND datetime(start_date) > datetime('now')
AND date(start_date) = date('now')
ORDER BY id_remote DESC, start_date
LIMIT 1;
'''.format(place_id=place_id, fields=app.config['SQL_DEFAULT_FIELDS']))
c = []
#if a or b:
c.append(a)
c.append(b)
return c, 200
def get(self, place_id=None):
if place_id:
return runSQL('''
SELECT *
FROM places
WHERE id={};
'''.format(place_id)), 200 # HTTP status code 200 OK
else:
return runSQL('''
SELECT *
FROM places;
'''), 200
def post(self, id=None):
item = item_post_parser.parse_args()
start_date = item['start_date'].strftime('%Y-%m-%d %H:%M:%S')
end_date = item['end_date'].strftime('%Y-%m-%d %H:%M:%S')
# items overlaping check
overlap = runSQL('''
SELECT * FROM items
WHERE place_id = {place_id}
AND (((start_date <= '{start_date}' AND '{start_date}' < end_date) OR (start_date < '{end_date}' AND '{end_date}' <= end_date))
OR ('{start_date}' < start_date AND end_date < '{end_date}'));
'''.format(start_date=start_date,
end_date=end_date,
place_id=item['place_id']))
if overlap and item['item_type'] == 1:
return {
'message': 'Insert failed. Item overlaps existing items'
}, 409 # Conflict
# proceed with insert
item_id = runSQL('''
INSERT INTO items (name, description, start_date, end_date, user_id, place_id, itemtype_id)
VALUES ('{name}','{description}','{start_date}','{end_date}', {user_id}, {place_id}, {item_type});
'''.format(name=item['name'],
description=item['description'],
start_date=start_date,
end_date=end_date,
user_id=item['user_id'],
place_id=item['place_id'],
item_type=item['item_type']))
# return runSQL('''
# SELECT {fields}, users.name || ' ' || users.surname as user_name, places.name as place_name, item_type.name as itemtype_name
# FROM items, users, places, item_type
# WHERE items.user_id = users.id
# AND items.place_id = places.id
# AND items.itemtype_id = item_type.id
# AND items.id = {item_id};
# '''.format(fields=app.config['SQL_DEFAULT_FIELDS'], item_id=item_id)), 201 # Created
return {
'message': 'item id={id} has been created'.format(id=item_id)
}, 201 # Created
def get(self):
return runSQL('''
SELECT {fields}, users.name || ' ' || users.surname as user_name, places.name as place_name, item_type.name as itemtype_name
FROM items, users, places, item_type
WHERE items.user_id = users.id
AND items.place_id = places.id
AND items.itemtype_id = item_type.id
AND start_date <= datetime('now')
AND datetime('now') <= end_date
ORDER BY start_date, place_id;
'''.format(fields=app.config['SQL_DEFAULT_FIELDS'])
), 200 # HTTP status code 200 OK
def delete(self, id=None):
if id:
if runSQL('''
DELETE FROM items where id = {id};
'''.format(id=id)):
return {
'message': 'item id={} has been deleted'.format(id)
}, 200
else:
return {'message': 'item id={} not found'.format(id)}, 404
else:
return {'message': 'fake delete all items'}, 200
def get(self, place_id, start_datetime=None, end_datetime=None):
if end_datetime:
#gc_sync_db(place_id, start_date, end_date)
return runSQL('''
SELECT {fields}, users.name || ' ' || users.surname as user_name, places.name as place_name, item_type.name as itemtype_name
FROM items, users, places, item_type
WHERE items.user_id = users.id
AND items.place_id = places.id
AND items.itemtype_id = item_type.id
AND place_id = {place_id}
AND ((datetime(start_date) < datetime('{start_date}') AND datetime('{start_date}') < datetime(end_date))
OR (datetime('{start_date}') <= datetime(start_date) AND datetime(end_date) <= datetime('{end_date}'))
OR (datetime(start_date) < datetime('{end_date}') AND datetime('{end_date}') < datetime(end_date)))
ORDER BY start_date;
'''.format(place_id=place_id, start_date=start_datetime, end_date=end_datetime, fields=app.config['SQL_DEFAULT_FIELDS'])), 200
elif start_datetime:
#gc_sync_db(place_id, start_date)
return runSQL('''
SELECT {fields}, users.name || ' ' || users.surname as user_name, places.name as place_name, item_type.name as itemtype_name
FROM items, users, places, item_type
WHERE items.user_id = users.id
AND items.place_id = places.id
AND items.itemtype_id = item_type.id
AND place_id = {place_id}
AND ((datetime(start_date) < datetime('{start_date}') AND datetime('{start_date}') < datetime(end_date))
OR (datetime('{start_date}') <= datetime(start_date) AND datetime(end_date) <= datetime(datetime('{start_date}'),'+24 hours'))
OR (datetime(start_date) < datetime(datetime('{start_date}'),'+24 hours') AND datetime(datetime('{start_date}'),'+24 hours') < datetime(end_date)))
ORDER BY start_date;
'''.format(place_id=place_id, start_date=start_datetime, fields=app.config['SQL_DEFAULT_FIELDS'])), 200
else:
return runSQL('''
SELECT {fields}, users.name || ' ' || users.surname as user_name, places.name as place_name, item_type.name as itemtype_name
FROM items, users, places, item_type
WHERE items.user_id = users.id
AND items.place_id = places.id
AND items.itemtype_id = item_type.id
AND place_id = {place_id}
ORDER BY start_date;
'''.format(place_id=place_id, fields=app.config['SQL_DEFAULT_FIELDS'])), 200 # HTTP status code 200 OK
def get(self, place_id=None):
if place_id:
room = runSQL('''
SELECT *
FROM places
WHERE ID = {};
'''.format(place_id))
if room: # get events from Google Calendar
events = gc_today_events(room['id_remote'])
else:
events = {}
if events is not None:
return events, 200
else:
return "Google Calendar API error", 403 #403 FORBIDDEN
def gc_sync_db(place_id=None, start_date=None, end_date=None):
if place_id:
places = []
places.append(runSQL('SELECT * FROM places WHERE id={};'.format(place_id)))
else:
places = runSQL('SELECT * FROM places;')
# if dates empty set time range [utc.now; utc.now+24H]
if start_date:
start_datetime = datetime.datetime.strptime(start_date, '%Y-%m-%dT%H:%M:%S')
else:
start_datetime = datetime.datetime.utcnow()
if end_date:
end_datetime = datetime.datetime.strptime(end_date, '%Y-%m-%dT%H:%M:%S')
else:
end_datetime = start_datetime + datetime.timedelta(hours=24)
for place in places:
events = gc_get_events(place['id_remote'], start_datetime, end_datetime)
if not events:
break
# reset update flag for google calendars related items in time range [start_datetime;end_datetime]
runSQL('''
UPDATE items
SET updated = 0
WHERE id_remote
AND place_id = {place_id}
AND ((start_date < '{start_datetime}' AND '{start_datetime}' < end_date)
OR ('{start_datetime}' <= start_date AND end_date <= '{end_datetime}')
OR (start_date < '{end_datetime}' AND '{end_datetime}' < end_date));
'''.format(place_id=place['id'], start_datetime=start_datetime.strftime('%Y-%m-%d %H:%M:%S'),
end_datetime=end_datetime.strftime('%Y-%m-%d %H:%M:%S')))
for event in events:
# try update existed event otherwise insert
if runSQL('''
UPDATE items
SET name = '{name}',
description = '{desc}',
start_date = '{start_date}',
end_date = '{end_date}',
user_id = 1, updated = 1
WHERE place_id = {place_id}
AND id_remote = '{id_remote}';
'''.format(place_id=place['id'], name=event.get('summary','N/A').strip(), desc=event.get('description', 'N/A').strip(),
start_date=datetime.datetime.strptime(event['start']['dateTime'], '%Y-%m-%dT%H:%M:%SZ').strftime('%Y-%m-%d %H:%M:%S'),
end_date=datetime.datetime.strptime(event['end']['dateTime'], '%Y-%m-%dT%H:%M:%SZ').strftime('%Y-%m-%d %H:%M:%S'),
id_remote=event['id'])):
pass
else:
runSQL('''
INSERT INTO items (name, description, start_date, end_date, user_id, place_id, itemtype_id, id_remote, updated)
VALUES ('{name}','{desc}','{start_date}','{end_date}', 1, {place_id}, 1, '{id_remote}', 1);
'''.format(place_id=place['id'], name=event.get('summary','N/A').strip(), desc=event.get('description','N/A').strip(),
start_date=datetime.datetime.strptime(event['start']['dateTime'], '%Y-%m-%dT%H:%M:%SZ').strftime('%Y-%m-%d %H:%M:%S'),
end_date=datetime.datetime.strptime(event['end']['dateTime'], '%Y-%m-%dT%H:%M:%SZ').strftime('%Y-%m-%d %H:%M:%S'),
id_remote=event['id']))
# remove not updated items
runSQL('''
DELETE FROM items where not updated;
''')