def edit_user_active(_params):
"""edit user preferences
Keyword arguments:
_params includes:
active -- activity state of user
user_id -- id of user
"""
if _params['active'] not in ['0', '1']:
raise FailedRequest('Invalid active', 400)
get_user(_params['user_id'])
_params['active'] = int(_params['active'])
database.query(
db.music_db,
'''
UPDATE users
SET active = :active
WHERE user_id = :user_id
LIMIT 1
''',
_params
)
elastic_search.update_from_index('users', 'user', _params['user_id'],
['active'], _params)
def edit_user_picture(_params):
"""edit user picture
Keyword arguments:
_params includes:
picture -- picture of user
"""
data = get_user(_params['user_id'])
file_ext = _params['image_filename'][_params['image_filename'].rfind('.'):]
new_filename = _params['user_id'] + file_ext
path = '/'.join([
utils.hash_string(data['email']),
utils.generate_filename(new_filename)
])
_params['picture'] = '/'.join(
[app.config['CLOUDFRONT_URL'], 'users', path])
uploader = Uploader(config)
uploader.upload_image('users', path, _params['image'].read())
database.query(
db.music_db, '''
UPDATE users
SET picture = :picture
WHERE user_id = :user_id
LIMIT 1
''', _params)
elastic_search.update_from_index('users', 'user', _params['user_id'],
['picture'], _params)
def edit_user_active(_params):
"""edit user preferences
Keyword arguments:
_params includes:
active -- activity state of user
user_id -- id of user
"""
if _params['active'] not in ['0', '1']:
raise FailedRequest('Invalid active', 400)
get_user(_params['user_id'])
_params['active'] = int(_params['active'])
database.query(
db.music_db, '''
UPDATE users
SET active = :active
WHERE user_id = :user_id
LIMIT 1
''', _params)
elastic_search.update_from_index('users', 'user', _params['user_id'],
['active'], _params)
def add_user(_params):
"""add user information to database servers
Keyword arguments:
_params includes:
email -- email of user
role -- role of user
user_id -- id of user
"""
_params.update({
'active': 1,
'rank': 0,
'picture': None,
'genre': [],
'mood': [],
'instrument': [],
'skype': None,
'date_created': datetime.now(),
'date_updated': None
})
database.query(
db.music_db,
'''
INSERT INTO users
VALUES (
:user_id,
:email,
:name,
:active,
:rank,
:role,
:picture,
:skype,
:application_status,
:date_created,
:date_updated
)
''',
_params
)
database.query(
db.music_db,
'''
INSERT INTO user_preferences (
user_id
)
VALUES (
:user_id
)
''',
_params
)
elastic_search.add_to_index('users', 'user', _params, _params['user_id'],
config['ES']['user_cols'])
def add_user(_params):
"""add user information to database servers
Keyword arguments:
_params includes:
email -- email of user
role -- role of user
user_id -- id of user
"""
_params.update({
'active': 1,
'rank': 0,
'picture': None,
'genre': [],
'mood': [],
'instrument': [],
'skype': None,
'date_created': datetime.now(),
'date_updated': None
})
database.query(
db.music_db, '''
INSERT INTO users
VALUES (
:user_id,
:email,
:name,
:active,
:rank,
:role,
:picture,
:skype,
:application_status,
:date_created,
:date_updated
)
''', _params)
database.query(
db.music_db, '''
INSERT INTO user_preferences (
user_id
)
VALUES (
:user_id
)
''', _params)
elastic_search.add_to_index('users', 'user', _params, _params['user_id'],
config['ES']['user_cols'])
def apply(_params):
_params.update({
'artist_id': utils.generate_UUID(),
'application_id': 0,
'application_status': 'accepted',
'agree_on_contract': 'yes',
'picture': None,
'rating': None,
'music_provider_id': config['DEFAULT_MUSIC_PROVIDER'],
'date_applied': datetime.now(),
'date_approved': None,
'date_created': datetime.now(),
'date_updated': None
})
artist.process_artist_params(_params, True)
artist.insert_artist(_params)
database.query(
db.music_db,
'''
INSERT INTO applications
VALUES (
:application_id,
:user_id,
:contract_id,
:agree_on_contract,
:source,
:date_applied,
:date_approved,
:date_created,
:date_updated
)
''',
_params
)
database.query(
db.music_db,
'''
UPDATE users
SET skype = :skype,
application_status = :application_status
WHERE user_id = :user_id
LIMIT 1
''',
_params
)
elastic_search.update_from_index('users', 'user', _params['user_id'],
['skype', 'application_status'], _params)
def update_user(_params):
get_user(_params['user_id'])
database.query(
db.music_db, '''
UPDATE users
SET name = :name,
application_status = :application_status
WHERE user_id = :user_id
LIMIT 1
''', _params)
elastic_search.update_from_index('users', 'user', _params['user_id'],
['name', 'application_status'], _params)
def decline_invitation(_params):
data = get_user(_params['user_id'])
_params['email'] = data['email']
mp_op = '=' if _params['music_provider_id'] else 'IS'
a_op = '=' if _params['artist_id'] else 'IS'
database.query(
db.music_db, '''
DELETE FROM user_invites
WHERE email = :email
AND role = :role
AND music_provider_id %s :music_provider_id
AND artist_id %s :artist_id
''' % (mp_op, a_op), _params)
def edit_user_preference(_params):
"""edit user preferences
Keyword arguments:
_params includes:
genre -- genre preferences of user (default None)
instrument -- instrument preferences of user (default None)
mood -- mood preferences of user (default None)
"""
get_user(_params['user_id'])
for category in ['genre', 'instrument', 'mood']:
if not _params[category]:
continue
results = []
for preference in utils.split(_params[category], ','):
results.append(preference)
data = elastic_search.multi_get(category + 's', key='name',
values=results)
results = [row['name'] for row in data]
_params[category] = ','.join(results) or None
database.query(
db.music_db,
'''
UPDATE user_preferences
SET genre = :genre,
mood = :mood,
instrument = :instrument
WHERE user_id = :user_id
LIMIT 1
''',
_params
)
_params.update({
'genre': utils.split(_params['genre'], ','),
'instrument': utils.split(_params['instrument'], ','),
'mood': utils.split(_params['mood'], ',')
})
elastic_search.update_from_index('users', 'user', _params['user_id'],
['genre', 'mood', 'instrument'], _params)
def update_user(_params):
get_user(_params['user_id'])
database.query(
db.music_db,
'''
UPDATE users
SET name = :name,
application_status = :application_status
WHERE user_id = :user_id
LIMIT 1
''',
_params
)
elastic_search.update_from_index('users', 'user', _params['user_id'],
['name', 'application_status'], _params)
def apply(_params):
_params.update({
'artist_id': utils.generate_UUID(),
'application_id': 0,
'application_status': 'accepted',
'agree_on_contract': 'yes',
'picture': None,
'rating': None,
'music_provider_id': config['DEFAULT_MUSIC_PROVIDER'],
'date_applied': datetime.now(),
'date_approved': None,
'date_created': datetime.now(),
'date_updated': None
})
artist.process_artist_params(_params, True)
artist.insert_artist(_params)
database.query(
db.music_db, '''
INSERT INTO applications
VALUES (
:application_id,
:user_id,
:contract_id,
:agree_on_contract,
:source,
:date_applied,
:date_approved,
:date_created,
:date_updated
)
''', _params)
database.query(
db.music_db, '''
UPDATE users
SET skype = :skype,
application_status = :application_status
WHERE user_id = :user_id
LIMIT 1
''', _params)
elastic_search.update_from_index('users', 'user', _params['user_id'],
['skype', 'application_status'], _params)
def edit_user_preference(_params):
"""edit user preferences
Keyword arguments:
_params includes:
genre -- genre preferences of user (default None)
instrument -- instrument preferences of user (default None)
mood -- mood preferences of user (default None)
"""
get_user(_params['user_id'])
for category in ['genre', 'instrument', 'mood']:
if not _params[category]:
continue
results = []
for preference in utils.split(_params[category], ','):
results.append(preference)
data = elastic_search.multi_get(category + 's',
key='name',
values=results)
results = [row['name'] for row in data]
_params[category] = ','.join(results) or None
database.query(
db.music_db, '''
UPDATE user_preferences
SET genre = :genre,
mood = :mood,
instrument = :instrument
WHERE user_id = :user_id
LIMIT 1
''', _params)
_params.update({
'genre': utils.split(_params['genre'], ','),
'instrument': utils.split(_params['instrument'], ','),
'mood': utils.split(_params['mood'], ',')
})
elastic_search.update_from_index('users', 'user', _params['user_id'],
['genre', 'mood', 'instrument'], _params)
def decline_invitation(_params):
data = get_user(_params['user_id'])
_params['email'] = data['email']
mp_op = '=' if _params['music_provider_id'] else 'IS'
a_op = '=' if _params['artist_id'] else 'IS'
database.query(
db.music_db,
'''
DELETE FROM user_invites
WHERE email = :email
AND role = :role
AND music_provider_id %s :music_provider_id
AND artist_id %s :artist_id
''' % (mp_op, a_op),
_params
)
def delete_user(user_id):
"""delete user from elastic search index, set active to 0 in mysql
Keyword arguments:
_params includes:
user_id -- id of user
"""
get_user(user_id)
database.query(
db.music_db, '''
UPDATE users
SET active = 0
WHERE user_id = :user_id
LIMIT 1
''', {'user_id': user_id})
elastic_search.delete_from_index('users', 'user', user_id)
def load_measurement_counts(places):
if places.count > 0:
params = [place.Id for place in places]
meta = ''
for id in params: meta = meta + ',?'
SQL_MEASUREMENT_COUNT = 'SELECT Place, COUNT(Value) FROM Measurement WHERE Place IN (%s) GROUP BY Place' % meta[1:]
result = database.query(SQL_MEASUREMENT_COUNT, params)
for mc in result:
for place in places:
if place.Id == mc[0]: place.MeasurementCount = mc[1]
return True
def edit_user_picture(_params):
"""edit user picture
Keyword arguments:
_params includes:
picture -- picture of user
"""
data = get_user(_params['user_id'])
file_ext = _params['image_filename'][_params['image_filename'].rfind('.'):]
new_filename = _params['user_id'] + file_ext
path = '/'.join([
utils.hash_string(data['email']),
utils.generate_filename(new_filename)
])
_params['picture'] = '/'.join([
app.config['CLOUDFRONT_URL'],
'users',
path
])
uploader = Uploader(config)
uploader.upload_image('users', path, _params['image'].read())
database.query(
db.music_db,
'''
UPDATE users
SET picture = :picture
WHERE user_id = :user_id
LIMIT 1
''',
_params
)
elastic_search.update_from_index('users', 'user', _params['user_id'],
['picture'], _params)
def delete_user(user_id):
"""delete user from elastic search index, set active to 0 in mysql
Keyword arguments:
_params includes:
user_id -- id of user
"""
get_user(user_id)
database.query(
db.music_db,
'''
UPDATE users
SET active = 0
WHERE user_id = :user_id
LIMIT 1
''',
{'user_id': user_id}
)
elastic_search.delete_from_index('users', 'user', user_id)
def get_all(place_id = None, from_date = None, to_date = None, type_id = None):
params = ()
SQL = 'SELECT Time, Place as PlaceId, Type as TypeId, Value FROM Measurement WHERE 1=1'
if place_id:
SQL = SQL + ' AND Place = ?'
params = params + (place_id,)
if type_id:
SQL = SQL + ' AND Type = ?'
params = params + (type_id,)
if from_date > MIN_DATE:
SQL = SQL + ' AND Time >= ?'
params = params + (from_date,)
if to_date < MAX_DATE:
SQL = SQL + ' AND Time < ?'
params = params + (to_date,)
print SQL
return [Measurement(row) for row in database.query(SQL, params)]
def get_all():
if not Place.cache:
SQL_GET_ALL = "SELECT Id, Name, CoordLon, CoordLat, Country FROM Place"
Place.cache = [Place(row) for row in database.query(SQL_GET_ALL)]
return Place.cache
def add_user(_params):
data = database.query(
db.test_app_db, 'INSERT INTO users(`user_id`, `email`) VALUES(:user_id, :email)', _params)
return data
def accept_invitation(_params):
data = get_user(_params['user_id'])
_params['email'] = data['email']
mp_op = '=' if _params['music_provider_id'] else 'IS'
a_op = '=' if _params['artist_id'] else 'IS'
data = database.get(
db.music_db,
'''
SELECT *
FROM user_invites
WHERE email = :email
AND role = :role
AND music_provider_id %s :music_provider_id
AND artist_id %s :artist_id
''' % (mp_op, a_op),
_params
)
if not data:
raise FailedRequest('Invalid parameters', 400)
if _params['role'] == 'artist':
database.query(
db.music_db,
'''
UPDATE artists
SET user_id = :user_id
WHERE artist_id = :artist_id
LIMIT 1
''',
_params
)
elastic_search.update_from_index('artists', 'artist',
_params['artist_id'],
['user_id', 'email'], _params)
database.query(
db.music_db,
'''
DELETE FROM user_invites
WHERE artist_id = :artist_id
''',
_params
)
else:
database.query(
db.music_db,
'''
INSERT IGNORE INTO music_provider_owners_managers
VALUES (
:user_id,
:music_provider_id,
:role
)
''',
_params
)
data = music_provider.get_music_provider(_params['music_provider_id'])
_params['users'] = data['users']
_params['users'][_params['user_id']] = _params['role']
elastic_search.update_from_index('music_providers', 'music_provider',
_params['music_provider_id'],
['users'], _params)
database.query(
db.music_db,
'''
DELETE FROM user_invites
WHERE music_provider_id = :music_provider_id
''',
_params
)
if _params['role'] != 'artist':
_params['role'] = 'music_provider'
database.query(
db.music_db,
'''
UPDATE users
SET role = :role
WHERE user_id = :user_id
LIMIT 1
''',
_params
)
elastic_search.update_from_index('users', 'user', _params['user_id'],
['role'], _params)
_params['scopes'] = config['SCOPES'][_params['role']]
auth.remove_scopes(_params['user_id'])
auth.add_scopes(_params)
database.query(
db.music_db,
'''
DELETE FROM user_invites
WHERE email = :email
''',
_params
)
def accept_invitation(_params):
data = get_user(_params['user_id'])
_params['email'] = data['email']
mp_op = '=' if _params['music_provider_id'] else 'IS'
a_op = '=' if _params['artist_id'] else 'IS'
data = database.get(
db.music_db, '''
SELECT *
FROM user_invites
WHERE email = :email
AND role = :role
AND music_provider_id %s :music_provider_id
AND artist_id %s :artist_id
''' % (mp_op, a_op), _params)
if not data:
raise FailedRequest('Invalid parameters', 400)
if _params['role'] == 'artist':
database.query(
db.music_db, '''
UPDATE artists
SET user_id = :user_id
WHERE artist_id = :artist_id
LIMIT 1
''', _params)
elastic_search.update_from_index('artists', 'artist',
_params['artist_id'],
['user_id', 'email'], _params)
database.query(
db.music_db, '''
DELETE FROM user_invites
WHERE artist_id = :artist_id
''', _params)
else:
database.query(
db.music_db, '''
INSERT IGNORE INTO music_provider_owners_managers
VALUES (
:user_id,
:music_provider_id,
:role
)
''', _params)
data = music_provider.get_music_provider(_params['music_provider_id'])
_params['users'] = data['users']
_params['users'][_params['user_id']] = _params['role']
elastic_search.update_from_index('music_providers', 'music_provider',
_params['music_provider_id'],
['users'], _params)
database.query(
db.music_db, '''
DELETE FROM user_invites
WHERE music_provider_id = :music_provider_id
''', _params)
if _params['role'] != 'artist':
_params['role'] = 'music_provider'
database.query(
db.music_db, '''
UPDATE users
SET role = :role
WHERE user_id = :user_id
LIMIT 1
''', _params)
elastic_search.update_from_index('users', 'user', _params['user_id'],
['role'], _params)
_params['scopes'] = config['SCOPES'][_params['role']]
auth.remove_scopes(_params['user_id'])
auth.add_scopes(_params)
database.query(
db.music_db, '''
DELETE FROM user_invites
WHERE email = :email
''', _params)
def add_user(_params):
database.query(
db.app_db, 'INSERT INTO users(`user_id`, `email`) VALUES \
(:user_id, :email)', _params)
return
def get_all():
if not MeasurementType.cache:
SQL = 'SELECT Id, Name, Unit FROM MeasurementType'
MeasurementType.cache = [MeasurementType(row) for row in database.query(SQL)]
return MeasurementType.cache