def delete_day_entry(cls, dates, uid):
connection = dc.connectdb()
cursor = connection.cursor()
for date in dates:
try:
day_number = int(
datetime.datetime.strptime(date,
'%m/%d/%y').strftime('%d'))
date1 = datetime.datetime.strptime(date, '%m/%d/%y')
date2 = date1 - datetime.timedelta(days=day_number)
month, year = int(
datetime.datetime.strptime(
date, '%m/%d/%y').strftime('%m')), int(
datetime.datetime.strptime(
date, '%m/%d/%y').strftime('%y'))
month_find = MonthUsage.query.filter_by(month=month,
year=year,
uid=uid).first()
week_find = WeekUsage.query.filter_by(week_start_date=date2,
uid=uid).first()
finder = cls.query.filter_by(date=date, uid=uid).first()
w_usage = week_find.w_usage - finder.d_usage
m_usage = month_find.m_usage - finder.d_usage
sql = 'UPDATE `week_usage` SET `w_usage`=%s WHERE (`week_start_date`=%s AND `uid`=%s)'
cursor.execute(sql, (w_usage, date2, uid))
sql = 'UPDATE `month_usage` SET `m_usage`=%s WHERE (`month`=%s AND `year`=%s AND `uid`=%s)'
cursor.execute(sql, (m_usage, month, year, uid))
except AttributeError:
pass
finally:
sql = 'DELETE FROM `db`.`day_usage` WHERE (`date`=%s AND `uid`=%s)'
cursor.execute(sql, (date, uid))
connection.close()
def new_day_entry(date, d_usage, uid):
date = datetime.datetime.strptime(date,
'%Y-%m-%d').strftime('%m/%d/%y')
day = datetime.datetime.strptime(date, '%m/%d/%y').strftime('%d')
month = datetime.datetime.strptime(date, '%m/%d/%y').strftime('%m')
year = datetime.datetime.strptime(date, '%m/%d/%y').strftime('%Y')
find = pd.read_sql_table('day_usage',
db.app.config['SQLALCHEMY_DATABASE_URI'],
index_col='did')
f = len(find[(find['uid'] == uid) & (find['date'] == date)])
if f == 1:
return e.DayExist
connection = dc.connectdb()
cursor = connection.cursor()
sql = 'INSERT INTO `day_usage` (`date`, `day`, `month`, `year`, `d_usage`, `uid`) VALUES (%s, %s, %s, %s, %s, %s)'
cursor.execute(sql, (date, day, month, year, d_usage, uid))
def new_month_entry(cls, month_start_date, m_usage_input, uid):
try:
month = month_start_date.strftime('%b')
year = month_start_date.strftime('%Y')
except TypeError:
month = datetime.datetime.strptime(month_start_date,
'%Y-%m-%d').strftime('%B')
year = datetime.datetime.strptime(month_start_date,
'%Y-%m-%d').strftime('%Y')
find = len(cls.query.filter_by(date=month_start_date, uid=uid).all())
if find == 1:
return e.DayExist
if cls.isitthefirst(month_start_date.strftime('%Y-%m-%d')) is not None:
connection = dc.connectdb()
cursor = connection.cursor()
sql = 'INSERT INTO `month_usage` (`date`, `month`, `year`, `m_usage`, `uid`) ' \
'VALUES (%s, %s, %s, %s, %s)'
cursor.execute(sql,
(month_start_date, month, year, m_usage_input, uid))
connection.close()
def edit_day_entry(cls, date, usage, uid):
connection = dc.connectdb()
cursor = connection.cursor()
try:
day_number = int(
datetime.datetime.strptime(date, '%m/%d/%y').strftime('%w'))
date1 = datetime.datetime.strptime(date, '%m/%d/%y')
date2 = date1 - datetime.timedelta(days=day_number)
find = WeekUsage.query.filter_by(week_start_date=date2,
uid=uid).first()
finder = cls.query.filter_by(date=date, uid=uid).first()
w_usage = find.w_usage - finder.d_usage + usage
sql = 'UPDATE `week_usage` SET `w_usage`=%s WHERE (`week_start_date`=%s AND `uid`=%s)'
cursor.execute(sql, (w_usage, date2, uid))
except AttributeError:
pass
finally:
sql = 'UPDATE `day_usage` SET `d_usage`=%s WHERE (`date`=%s AND `uid`=%s)'
cursor.execute(sql, (usage, date, uid))
connection.close()
def new_week_entry(cls, week_start_date, w_usage_input, uid):
try:
datetime.datetime.strptime(week_start_date, '%Y-%m-%d')
except TypeError:
pass
find = len(
cls.query.filter_by(week_start_date=week_start_date,
uid=uid).all())
day = datetime.datetime.strptime(week_start_date,
'%Y-%m-%d').strftime('%d')
month = datetime.datetime.strptime(week_start_date,
'%Y-%m-%d').strftime('%m')
year = datetime.datetime.strptime(week_start_date,
'%Y-%m-%d').strftime('%Y')
if find == 1:
return e.DayExist
connection = dc.connectdb()
cursor = connection.cursor()
sql = 'INSERT INTO `week_usage` (`week_start_date`, `week_start_day`, `week_start_month`, `week_start_year`, `w_usage`, `uid`) ' \
'VALUES (%s, %s, %s, %s, %s, %s)'
cursor.execute(sql,
(week_start_date, day, month, year, w_usage_input, uid))
connection.close()
def delete_usage(cls, uid):
connection = dc.connectdb()
cursor = connection.cursor()
sql = 'DELETE FROM `db`.`month_usage` WHERE `uid`=%s'
cursor.execute(sql, uid)
connection.close()