def get_customer_trend(date_start, date_end, time_frame):
"""
Return the trend of active customer number in the time range
"""
time_dict = {'date': 'date', 'ww': 'week', 'mon': 'month', 'q': 'quarter'}
if time_frame == 'date' or time_frame is None: # None is used for switch page default frame
sql = f"""
select salesdate, count(unique customerID)
from sales
where salesdate between to_date('{date_start}', 'YYYY-MM-DD') and to_date('{date_end}', 'YYYY-MM-DD')
group by salesdate
order by salesdate
"""
rows = query(sql)
df = pd.DataFrame(columns=['date', 'customer_number'])
for row in rows:
df.loc[len(df), :] = row
df['date'] = pd.to_datetime(df['date'])
else:
sql = f"""
select to_char(salesdate, '{time_frame}'), count(unique customerID)
from sales
where salesdate between to_date('{date_start}', 'YYYY-MM-DD') and to_date('{date_end}', 'YYYY-MM-DD')
and salesdate is Not null
group by to_char(salesdate, '{time_frame}')
order by to_char(salesdate, '{time_frame}')
"""
rows = query(sql)
df = pd.DataFrame(columns=[time_dict[time_frame], 'customer_number'])
for row in rows:
df.loc[len(df), :] = row
return df
def loc_tag_remove(loc_tag_id, id_tag):
sql_command = f"DELETE FROM loc_tag WHERE id = %s ;"
cursor = db.query(sql_command, (loc_tag_id,))
db.conn.commit()
try:
#delete the tag connection
sql_command = f"DELETE FROM loc_tag WHERE id = %s;"
cursor = db.query(sql_command, (loc_tag_id,))
db.conn.commit()
#check if the tag is connected to anything anymore
sql_command = f"SELECT * FROM loc_tag WHERE id_tag = %s;"
cursor = db.query(sql_command, (id_tag,))
results = cursor.fetchall()
#if not, delete it
if (len(results) is 0):
sql_command = f"DELETE FROM tags WHERE id = %s;"
cursor = db.query(sql_command, (id_tag,))
db.conn.commit()
except:
db.conn.rollback()
def get_revenue_by_time(date_start, date_end, time_frame):
"""
Return the revenue for each day within the time range.
"""
time_dict = {'date': 'date', 'ww': 'week', 'mon': 'month', 'q': 'quarter'}
if time_frame == 'date' or time_frame is None: # None is used for switch page default frame
sql = f"""
select salesdate, sum(total)
from sales
where salesdate between to_date('{date_start}', 'YYYY-MM-DD') and to_date('{date_end}', 'YYYY-MM-DD')
group by salesdate
order by salesdate
"""
rows = query(sql)
df = pd.DataFrame(columns=['date', 'revenue'])
for row in rows:
df.loc[len(df), :] = row
df['date'] = pd.to_datetime(df['date'])
else:
sql = f"""
select to_char(salesdate, '{time_frame}'), sum(total)
from sales
where salesdate between to_date('{date_start}', 'YYYY-MM-DD') and to_date('{date_end}', 'YYYY-MM-DD')
and salesdate is Not null
group by to_char(salesdate, '{time_frame}')
order by to_char(salesdate, '{time_frame}')
"""
rows = query(sql)
df = pd.DataFrame(columns=[time_dict[time_frame], 'revenue'])
for row in rows:
df.loc[len(df), :] = row
return df
def get_plays(self, now):
global PERFORM_FETCH
metadata = []
songs = []
genres = {}
artists = {}
result = db.query(models.Songs).filter_by(date=now).order_by(models.Songs.playCount.desc()).first()
if result is not None:
app.logger.warn("There is already existing data for today.")
PERFORM_FETCH = False
else:
PERFORM_FETCH = True
if PERFORM_FETCH:
# Pull Metadata
app.logger.info("Starting Metadata fetch.")
data = self.api.get_all_songs()
app.logger.info("Retrieved %d records." % len(data))
required_metadata = ['id', 'playCount', 'artist', 'album', 'title', 'trackNumber', 'genre']
for item in data:
missing_metadata = False
for field in required_metadata:
if field not in item:
missing_metadata = True
break
if missing_metadata:
continue
metadata.append((item['id'],
item['playCount'],
item['artist'],
item['album'],
item['title'],
item['trackNumber'],
item['genre']))
DatabaseInsert.song_playcount(metadata, now)
app.logger.info("Inserted %d play count records." % len(metadata))
# Retrieve Dataset
for result in db.query(models.Songs).filter_by(date=now).order_by(models.Songs.playCount.desc()).yield_per(5):
songs.append([
result.playCount,
result.artist,
result.album,
result.title,
result.trackNumber,
result.genre
])
genres[result.genre] = genres.get(result.genre, 0) + result.playCount
artists[result.artist] = artists.get(result.artist, 0) + result.playCount
# Sort Data for Pie Chart
sorted_genres = sorted(genres.items(), key=operator.itemgetter(1))
sorted_artists = sorted(artists.items(), key=operator.itemgetter(1), reverse=True)
# Close Connection (which drops the table, wipes the databse from memory)
app.logger.info("Completed data sorting.")
return songs, sorted_genres, sorted_artists
def source_process(source_id):
ws = db.query(WordStat).filter(WordStat.source_id == source_id)
ws.delete()
source = db.query(Source).filter(Source.source_id == source_id)
source = source.first()
process(source)
flash('Source "{}" successfully processed'.format(source.source_name))
return redirect('/sources/{}'.format(source_id))
def post(self):
data = request.get_json() or request.form
if data['importType'] == 'batch':
# Batch Import
return {
"reason": "Not Supported Temporarily..."
}
else:
# Single Import
courseId = data['courseId']
studentList = data['students']
studentCount = len(studentList)
for i in range(0, studentCount):
studentId = studentList[i]['id']
res = db.query(
"""
select 1 from User where school_id=%s limit 1;
""",
(studentId,)
)
if res:
isIn = db.query(
"""
select 1 from StudentCourse where student_id=%s and course_id=%s;
""",
(studentId, courseId)
)
if isIn:
studentList[i]['state'] = False
studentList[i]['reason'] = 'Already Involved.'
else:
db.insert(
"""
insert into StudentCourse (student_id, course_id, status)
values
(%s, %s, %s);
""",
(studentId, courseId, 0)
)
db.modify(
"""
update User set course_count=course_count+1 where school_id=%s;
""",
(studentId,)
)
studentList[i]['state'] = True
else:
studentList[i]['state'] = False
studentList[i]['reason'] = 'User not Found.'
resp = {
"state": "success",
"students": studentList
}
return resp
def like_user(self, liked_user):
if liked_user.id in self.liked:
action = 'unlike'
sql = "DELETE FROM `likes` WHERE user_id = %s AND liked_id = %s"
else:
action = 'like_back' if self.id in liked_user.liked else 'like'
sql = "INSERT INTO `likes` SET user_id = %s, liked_id = %s"
db.query(sql, values=(self.id, liked_user.id), commit=True)
return action
def get_sections(user_id, year, tri):
sections = []
if year != -1 and tri != -1 and not Config.DEBUG:
sections = query(DB.ELECTIVE,
'SELECT section.section_id, section.elective_id, e.name, e.desc, e.prereq, section.room_nbr, section.enrolled_count, section.section_nbr, section.max, t.usr_id, t.usr_first_name, t.usr_last_name '
'FROM elective_section section, elective e, user t '
'WHERE course_year = %s '
'AND tri = %s '
'AND e.elective_id = section.elective_id '
'AND section.enrolled_count < section.max '
'AND t.usr_id = section.teacher_id '
'AND NOT (section.section_id in (SELECT section_id FROM elective_user_xref WHERE usr_id=%s)) '
'ORDER BY e.name', [year, tri, user_id])
else:
sections = query(DB.ELECTIVE,
'SELECT section.section_id, section.elective_id, e.name, e.desc, e.prereq, section.room_nbr, section.enrolled_count, section.section_nbr, section.max, t.usr_id, t.usr_first_name, t.usr_last_name '
'FROM elective_section section, elective e, user t '
'WHERE e.elective_id = section.elective_id '
'AND t.usr_id = section.teacher_id '
'AND NOT (section.section_id in (SELECT section_id FROM elective_user_xref WHERE usr_id=%s)) '
'ORDER BY e.name', [user_id])
e_sections = []
for section in sections:
elective_id = section[1]
elective_name = section[2]
elective_desc = section[3]
elective_prereq = section[4]
section_id = section[0]
section_room_nbr = section[5]
section_enrolled = section[6]
section_nbr = section[7]
section_max = section[8]
teacher_id = section[9]
teacher_first_name = section[10]
teacher_last_name = section[11]
elective = Elective(elective_id, elective_name, elective_desc, elective_prereq)
teacher = ElectiveTeacher(teacher_id, teacher_first_name, teacher_last_name)
times = get_times(section_id)
s = ElectiveSection(section_id, elective, section_nbr, tri, year, section_enrolled, section_max,
section_room_nbr, teacher, times, False)
s.teacher = teacher
e_sections.append(s)
return e_sections
def read_users(request:Request, offset=0, limit=100):
total = db.query(models.User).count()
users = db.query(models.User).offset(offset).limit(limit).all()
for user in users:
_links = {}
_links.update({"self" : request.url_for("read_user", user_id=user.id)})
_links.update({"collection" : request.url_for("read_users")})
_links.update({"department" : request.url_for("read_department", department_id=user.department_id)})
user.__setattr__("_links", _links)
return {'data':users, 'total':total, 'offset':offset, 'limit':limit}
def read_departments(request:Request, offset=0, limit=100):
total = db.query(models.Department).count()
departments = db.query(models.Department).offset(offset).limit(limit).all()
for department in departments:
_links = {}
_links.update({"self" : request.url_for("read_department", department_id=department.id)})
_links.update({"collection" : request.url_for("read_departments")})
_links.update({"users" : request.url_for("read_department_users", department_id=department.id)})
department.__setattr__("_links", _links)
return {'data':departments, 'total':total, 'offset':offset, 'limit':limit}
def get_club_teacher(club_id):
teacher_id = query(DB.CLUBS, "SELECT advisor_id "
"FROM club "
"WHERE club_id = %s ", [club_id])
full_name = query(DB.SHARED, "SELECT usr_first_name, usr_last_name "
"FROM user "
"WHERE usr_id=%s ", [teacher_id])
return full_name[0]
def avatar(self, value):
user_dir = os.path.join(app.config['UPLOAD_FOLDER'], self.login)
absolute_dir = os.path.join(app.config['ROOT_PATH'], user_dir)
for photo in os.listdir(absolute_dir):
photo_path = os.path.join('/', user_dir, photo)
if photo_path != value and photo_path not in self.photos:
os.remove(os.path.join(absolute_dir, photo))
db.query("UPDATE users SET avatar = %s WHERE id = %s",
values=(value, self.id),
commit=True)
self._avatar = value
def online(self, value):
if value == 0:
last_login_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
sql = "UPDATE users SET online = 0, last_login = %s WHERE id = %s"
db.query(sql, values=(last_login_date, self.id), commit=True)
elif value == 1:
db.query("UPDATE users SET online = 1 WHERE id = %s",
values=(self.id, ),
commit=True)
else:
raise ValueError("Online must be equal to 0 or 1")
self._online = value
def __init__(self, user1_id, user2_id):
select_sql = "SELECT id FROM chats WHERE user1_id = %s AND user2_id = %s"
user_pair = (user1_id, user2_id)
response = db.get_row(select_sql, values=user_pair) or \
db.get_row(select_sql, values=reversed(user_pair))
if response is None:
insert_sql = "INSERT INTO chats (user1_id, user2_id) VALUE (%s, %s)"
db.query(insert_sql, values=user_pair, commit=True)
response = db.get_row(select_sql, values=user_pair)
self.id, = response
self.sender_id = user1_id
self.recipient_id = user2_id
def photos(self, values):
user_dir = os.path.join(app.config['UPLOAD_FOLDER'], self.login)
absolute_dir = os.path.join(app.config['ROOT_PATH'], user_dir)
for photo in os.listdir(absolute_dir):
photo_path = os.path.join('/', user_dir, photo)
if photo_path not in values and photo_path != self.avatar:
os.remove(os.path.join(absolute_dir, photo))
values = json.dumps(values)
db.query("UPDATE users SET photos = %s WHERE id = %s",
values=(values, self.id),
commit=True)
self._photos = values
def change(self, data, files=None):
sql, values, need_confirmation = self.get_changed_values(data)
if need_confirmation and self.email_exist(data["email"]):
raise ValueError("User with his E-mail already exists")
if len(values) > 0:
values.append(self.id)
db.query(f"UPDATE `users` SET {sql} WHERE id = %s",
values=values,
commit=True)
self.update_user_tags(data.getlist('tags'))
self.update_user_files(files)
if need_confirmation:
self.confirmed = 0
confirm_email_mail(data['email'], self.login, self.token)
flash("You will have to confirm your new E-mail!", 'success')
def query_all(self):
result = None
try:
result = db.query('SELECT id,about,image_url,addr,ST_X(the_geom) as lon,ST_Y(the_geom) as lat FROM '+ db_name + ";")
except Exception as e:
print e
return result.dictresult()
def get_clubs(usr_id):
clubs = query(DB.CLUBS,
"SELECT club_id, name, advisor_id, day, club_type_cde, room, description, max_nbr, enrollment_count"
" FROM club "
" WHERE advisor_id = %s"
" order by club_id", [usr_id])
all_clubs = []
for club in clubs:
club_id = club[0]
club_name = club[1]
club_advisor_id = club[2]
club_day = club[3]
club_type_cde = club[4]
club_room_nbr = club[5]
club_description = club[6]
club_max_nbr = club[7]
club_enrollment_count = club[8]
c = Club(club_name, club_day, club_type_cde, club_id, club_description, club_max_nbr, club_room_nbr,
club_enrollment_count, club_advisor_id)
all_clubs.append(c)
return all_clubs
def parking_get_all_of_location(location_id):
sql_command = f"SELECT * FROM parkings WHERE id_location = %s;"
cursor = db.query(sql_command, (location_id,))
result = cursor.fetchall()
return result
def location_change(location_id, name, desc_s, desc_l, rating, tts, coord, mtld, contact, webpage, timetable, fee, fee_price, child, season, icon):
sql_command = f"""UPDATE locations SET name = %s, desc_s = %s, desc_l = %s, rating = %s, tts = %s, coord = %s,
mtld = %s, contact = %s, webpage = %s, timetable = %s, fee = %s, fee_price = %s, child = %s, season = %s, icon = %s
WHERE id = %s;"""
cursor = db.query(sql_command, (name, desc_s, desc_l, rating, tts, coord, mtld, contact, webpage, timetable, fee, fee_price, child, season, icon, location_id))
db.conn.commit()
def location_get_all_where_rating(rating):
sql_command = f"SELECT id FROM locations WHERE rating = %s;"
cursor = db.query(sql_command, (rating,))
result = cursor.fetchall()
return result
def get_all_user_sessions(username):
sessions = (db.query(Session).with_entities(
Session.id, Session.start_time, Session.last_sitting_signal_time,
Session.last_posture_signal_time).filter_by(
username=username).order_by(
Session.last_sitting_signal_time.desc()).all())
return sessions
def myth_mtag_get_one_by(myth_id, mtag_id):
sql_command = f"SELECT * FROM myth_mtag WHERE id_myth = %s AND id_mtag = %s ;"
cursor = db.query(sql_command, (myth_id, mtag_id))
result = cursor.fetchone()
return result
def mtag_get_one(mtag_id):
sql_command = f"SELECT * FROM mtags WHERE id = %s;"
cursor = db.query(sql_command, (mtag_id,))
result = cursor.fetchone()
return result
def user_sql_get_one(user_id):
sql_command = f"SELECT * FROM users WHERE id = %s;"
cursor = db.query(sql_command, (user_id,))
result = cursor.fetchone()
return result
def mtag_add(myth_id, mtag_id):
sql_command = f"""INSERT INTO myth_mtag (id_mtag, id_myth)
VALUES (%s, %s);"""
cursor = db.query(sql_command, (myth_id, mtag_id))
db.conn.commit()
def location_delete_one(location_id):
try:
sql_command = f"DELETE FROM imgs WHERE id_location = %s;"
cursor = db.query(sql_command, (location_id,))
sql_command = f"DELETE FROM locations WHERE id = %s;"
cursor = db.query(sql_command, (location_id,))
db.conn.commit()
except:
db.conn.rollback()
def user_sql_get_all():
sql_command = f"SELECT id, name, username FROM users;"
cursor = db.query(sql_command, ())
results = cursor.fetchall()
return results
def location_get_numbers():
sql_command = f"SELECT COUNT(*) FROM locations;"
cursor = db.query(sql_command, ())
result = cursor.fetchone()
return result
def location_get_icon_link(location_id):
sql_command = f"SELECT icons.link FROM locations LEFT JOIN icons ON icons.id = locations.icon WHERE locations.id = %s ;"
cursor = db.query(sql_command, (location_id,))
result = cursor.fetchone()
return result
def location_get_all_with_tag_for_argus(tag_id):
sql_command = f"SELECT locations.id FROM loc_tag LEFT JOIN locations ON loc_tag.id_loc = locations.id WHERE loc_tag.id_tag = %s;"
cursor = db.query(sql_command, (tag_id,))
result = cursor.fetchall()
return result
def location_get_all_with_tag(tag_id):
sql_command = f"SELECT locations.id, locations.name, locations.desc_s, icons.link FROM loc_tag LEFT JOIN locations ON loc_tag.id_loc = locations.id LEFT JOIN icons ON icons.id = locations.icon WHERE loc_tag.id_tag = %s;"
cursor = db.query(sql_command, (tag_id,))
result = cursor.fetchall()
return result
def myths_get_all_argus():
sql_command = f"SELECT id, name, desc_l FROM myths ;"
cursor = db.query(sql_command, ())
result = cursor.fetchall()
return result
def query_search(self, lat, lon, distance_mi):
result = None
try:
print 'SELECT id,about,image_url,addr,ST_X(the_geom) as lon,ST_Y(the_geom) as lat FROM '+ db_name+ \
' WHERE ST_Distance_Sphere(the_geom, ST_MakePoint('+ lon + ', ' + lat + ')) <=' + distance_mi + ' * 1609.34;'
result = db.query('SELECT id,about,image_url,addr,ST_X(the_geom) as lon,ST_Y(the_geom) as lat FROM '+ db_name+ \
' WHERE ST_Distance_Sphere(the_geom, ST_MakePoint('+ lon + ', ' + lat + ')) <=' + distance_mi + ' * 1609.34;')
except Exception as e:
print e
return result.dictresult()
def facebook_authorized(resp):
if resp is None:
flash(u'Access denied: reason=%s error=%s' % (
request.args['error_reason'],
request.args['error_description'])
)
return redirect(url_for('sign_in'))
session['facebook_token'] = (resp['access_token'], '')
me = facebook.get('/me')
# >>> me.data
# {
# "username": "******",
# "id": "581604320",
# "email": "*****@*****.**"
# "locale": "en_US",
# "timezone": -5,
# "first_name": "Juan-Pablo",
# "last_name": "Scaletti",
# "name": "Juan-Pablo Scaletti",
# "gender": "male",
# "link": "http://www.facebook.com/jpscaletti",
# "updated_time": "2013-04-15T06:33:55+0000",
# }
user = db.query(User).filter(User.facebook_id == me.data['id']).first()
if not user: # new user!
if g.user:
user = g.user
else:
login = get_unique_login(me.data.get('username'))
user = User(login=login)
db.add(user)
user.facebook_id = me.data['id']
user.last_sign_in = datetime.utcnow()
# in any case we update the authentication token in the db
# In case the user temporarily revoked access we will have
# new tokens here.
user.facebook_token = resp['access_token']
# don't forget to commit **before** doing ``auth.login(user)`
db.commit()
auth.login(user)
next = request.args.get('next') or url_for('profile')
return redirect(next)
def twitter_authorized(resp):
if resp is None:
flash(u'You denied the request to sign in.')
return redirect(url_for('sign_in'))
session['twitter_token'] = resp['oauth_token']
# >>> resp
# {
# "oauth_token_secret": "...",
# "oauth_token": "...",
# "user_id": "11640332",
# "screen_name": "jpscaletti"
# }
user = db.query(User).filter(User.twitter_id == resp['user_id']).first()
if not user: # new user!
if g.user:
user = g.user
else:
login = get_unique_login(resp['screen_name'])
user = User(login=login)
db.add(user)
user.twitter_id = resp['user_id']
user.last_sign_in = datetime.utcnow()
# in any case we update the authentication token in the db
# In case the user temporarily revoked access we will have
# new tokens here.
user.twitter_username = resp['screen_name']
user.twitter_token = resp['oauth_token']
user.twitter_secret = resp['oauth_token_secret']
# don't forget to commit **before** doing ``auth.login(user)`
db.commit()
auth.login(user)
next = request.args.get('next') or url_for('profile')
return redirect(next)
