def GetBestSellerInMonth():
result = db.session.query(
func.count(Orders.employee_id).label("order_amount"),
Employees,
func.sum(Orders.total).label("total_revenue"),
) \
.filter(func.MONTH(models.Orders.order_date) == date.today().month and func.YEAR(models.Orders.order_date) == date.today().year) \
.group_by(Orders.employee_id)\
.join(Employees, Employees.employee_id == Orders.employee_id, isouter=True) \
.order_by(desc("order_amount"))\
.all()
return result
def get_transaction_by_year(year):
select_st = select([
Transaction_tbl.c.business, Transaction_tbl.c.date,
Transaction_tbl.c.sector, Transaction_tbl.c.amount,
Transaction_tbl.c.credit_card
]).where(func.YEAR(Transaction_tbl.c.date) == year)
conn = engine.connect()
# rs = conn.execute(select_st)
rs = pd.read_sql(select_st, conn)
conn.close()
return rs
def get_transaction_by_card_and_date(card_num, month, year):
select_st = select([
Transaction_tbl.c.business, Transaction_tbl.c.date,
Transaction_tbl.c.sector, Transaction_tbl.c.amount,
Transaction_tbl.c.credit_card
]).where((Transaction_tbl.c.credit_card == card_num)
& (func.MONTH(Transaction_tbl.c.date) == month)
& (func.YEAR(Transaction_tbl.c.date) == year))
conn = engine.connect()
# rs = conn.execute(select_st)
rs = pd.read_sql(select_st, conn)
conn.close()
return rs
def get_schedule(year) -> None:
'''Вернет список списков.
Состоящий из id дежурных, ФИО, цвета с фильтром по году и месяцу'''
duty_list = list()
for key, value in months.items():
schedule = db.session.query(Users.id, Users.surname, Users.name, \
Users.patronymic, Users.user_color, \
DutyDates.date).\
filter(Users.id==DutyDates.id_user).\
filter(func.YEAR(DutyDates.date) == year). \
filter(func.MONTH(DutyDates.date) == key).all()
for el in schedule:
el = [el[0], \
el[1].capitalize() + ' '+ el[2][0].upper() + '.' + el[3][0].upper() + '.', \
'#' + el[4], el[5].year, el[5].month, el[5].day]
el_data = duty_list.append(el)
return duty_list
def get_history_entries(oai_src_id, oai_date, method="harvested"):
if method == "inserted":
column = OaiHARVESTLOG.date_inserted
else:
column = OaiHARVESTLOG.date_harvested
res = db.query(OaiHARVESTLOG.date_harvested, OaiHARVESTLOG.date_inserted,
OaiHARVESTLOG.id_oaiHARVEST, OaiHARVESTLOG.oai_id, OaiHARVESTLOG.id_bibrec,
OaiHARVESTLOG.inserted_to_db, OaiHARVESTLOG.bibupload_task_id) \
.filter(OaiHARVESTLOG.id_oaiHARVEST == oai_src_id) \
.filter(func.MONTH(column) == oai_date.month) \
.filter(func.YEAR(column) == oai_date.year) \
.order_by(column).all()
result = []
for entry in res:
result.append(
HistoryEntry(entry[0], entry[1], int(entry[2]), str(entry[3]),
int(entry[4]), str(entry[5]), int(entry[6])))
return result
def get_month_logs_size(oai_src_id, oai_date, method="harvested"):
"""
Function which returns number of inserts which took place in given month (splited into days)
@param oai_src_id: harvesting source identifier
@return: Dictionary of harvesting statistics - keys describe days. values - numbers of inserted recordds
"""
if method == "inserted":
column = OaiHARVESTLOG.date_inserted
else:
column = OaiHARVESTLOG.date_harvested
res = db.session.query(func.DAY(column), func.count(func.DAY(column)))\
.filter(OaiHARVESTLOG.id_oaiHARVEST == oai_src_id) \
.filter(func.MONTH(column) == oai_date.month) \
.filter(func.YEAR(column) == oai_date.year) \
.group_by(func.DAY(column)).all()
result = {}
for entry in res:
if int(entry[0]) != 0:
result[int(entry[0])] = int(entry[1])
return result
def get_test_dates(test_id):
from sqlalchemy import func
if not Corporate.can_access_test(CookieHelper.get_corporate_id(),
test_id):
return Error("No access", 403)()
test_attempts = (
TestAttempt.query
.options(load_only(TestAttempt.date))
.filter(TestAttempt.test_id == test_id)
.filter(TestAttempt.is_complete == True)
.filter(
TestAttempt.user_id.in_(
db.session.query(
CorporateApplicants.user_id)
.filter(
CorporateApplicants.corporate_id == CookieHelper.get_corporate_id())
)
)
.group_by(
func.YEAR(func.CONVERT_TZ(TestAttempt.date,
"+00:00",
"-05:30")),
func.MONTH(func.CONVERT_TZ(TestAttempt.date,
"+00:00",
"-05:30")),
func.DAY(func.CONVERT_TZ(TestAttempt.date,
"+00:00",
"-05:30"))
)
.all())
# print(test_attempts
return test_attempts
def get_day_logs_size(oai_src_id, oai_date, method="harvested"):
"""
Function which returns number of inserts which took place in given day
@param oai_src_id: harvesting source identifier
@return: Number of inserts during the given day
"""
#sql_column = "date_harvested"
#if method == "inserted":
# sql_column = "date_inserted"
#
if method == "inserted":
column = OaiHARVESTLOG.date_inserted
else:
column = OaiHARVESTLOG.date_harvested
res = db.session.query(func.count(column)).filter(OaiHARVESTLOG.id_oaiHARVEST == oai_src_id)\
.filter(func.MONTH(column) == oai_date.month)\
.filter(func.YEAR(column) == oai_date.year)\
.filter(func.DAY(column) == oai_date.day).one()
if res:
return int(res[0])
return 0
def get_history_entries_for_day(oai_src_id,
oai_date,
limit=-1,
start=0,
method="harvested"):
"""
Returns harvesting history entries for a given day
@param oai_src_id: harvesting source identifier
@param oai_date: Date designing the deserved day
@param limit: How many records (at most) do we want to get
@param start: From which index do we want to start ?
@param method: method of getting data (two possible values "harvested" and "inserted")
Describes if the harvesting or inserting data should be used
"""
if method == "inserted":
column = OaiHARVESTLOG.date_inserted
else:
column = OaiHARVESTLOG.date_harvested
res = db.query(OaiHARVESTLOG.date_harvested, OaiHARVESTLOG.date_inserted,
OaiHARVESTLOG.id_oaiHARVEST, OaiHARVESTLOG.oai_id, OaiHARVESTLOG.id_bibrec,
OaiHARVESTLOG.inserted_to_db, OaiHARVESTLOG.bibupload_task_id) \
.filter(OaiHARVESTLOG.id_oaiHARVEST == oai_src_id) \
.filter(func.MONTH(column) == oai_date.month) \
.filter(func.YEAR(column) == oai_date.year) \
.filter(func.DAY(column) == oai_date.day) \
.order_by(column)
if limit > 0:
res = res.all()[start:start + limit]
else:
res = res.all()
result = []
for entry in res:
result.append(
HistoryEntry(entry[0], entry[1], int(entry[2]), str(entry[3]),
int(entry[4]), str(entry[5]), int(entry[6])))
return result
def GetBorrowTicketsInSpecificDay(datetime=datetime.utcnow().date()):
borrow_tickets_in_day = models.Borrowtickets.query.filter(
func.MONTH(models.Borrowtickets.borrow_date) == datetime.month,
func.YEAR(models.Borrowtickets.borrow_date) == datetime.year,
func.DAY(models.Borrowtickets.borrow_date) == datetime.day).all()
return borrow_tickets_in_day
def GetTotalRevenueOfSpecificMonth(month, year):
total_revenue_in_specific_month = db.session.query(func.sum(Orders.total)) \
.filter(func.MONTH(models.Orders.order_date) == month, func.YEAR(models.Orders.order_date) == year) \
.first()
return total_revenue_in_specific_month[
0] if total_revenue_in_specific_month[0] else 0.0
def cleanup_db():
engine = create_engine(config['Celery'].get('database_uri', raw=True))
Session = sessionmaker(bind=engine)
session = Session()
# filenaming
month, year = session.query(
func.MONTH(func.min(Smappee.utc_timestamp)).label('first_month'),
func.YEAR(func.min(Smappee.utc_timestamp)).label('first_year')
).one()
cwd = os.getcwd()
query = session.query(Smappee) \
.filter(func.MONTH(Smappee.utc_timestamp) == month) \
.filter(func.YEAR(Smappee.utc_timestamp) == year)
chunk_size = config['Celery'].get('chunk_size')
columns_to_fetch = [c.name for c in Smappee.__table__.columns]
total_rows = query.count()
logger.debug('{} rows found - will generate {} files with {chunk} records and 1 file with {} records',
total_rows, *divmod(total_rows, chunk_size), chunk=chunk_size)
def _get_fields(row):
return tuple(map(lambda column: getattr(row, column), columns_to_fetch))
def _get_chucks(q):
"""
Return the query in batches
:param q: SQLAlchemy Query
:return: list of records
"""
e = iter(q.yield_per(chunk_size))
while True:
result = []
for i in range(chunk_size):
try:
row = next(e)
except StopIteration:
yield result
return
result.append(_get_fields(row))
yield result
logger.debug('Starting fetching data')
for idx, chunk in enumerate(_get_chucks(query)):
logger.debug('Start chunk {}', idx)
logger.debug('{} rows to store', len(chunk))
df = pd.DataFrame(chunk, columns=columns_to_fetch)
filename = os.path.join(cwd, 'cold_backup/{}-{}-{}.gzip'.format(month, year, idx))
logger.debug('{} write file', filename)
df.to_parquet(filename, compression='gzip')
# delete the last month
query.delete(synchronize_session=False)
logger.debug('End of data storage')