def get_credit_classcards_used_classes_summary(date):
"""
:param date: datetime.date
:return:
"""
from general_helpers import max_string_length
from openstudio.os_reports import Reports
reports = Reports()
total = 0
count = db.school_classcards.id.count()
rows = reports.classes_attendance_classcards_quickstats_summary(date, date)
header = THEAD(
TR(
TH(T("Card")),
TH(T("Classes taken")),
TH(T("Class amount")),
TH(T("Total")),
))
table = TABLE(header, _class='table table-striped table-hover')
for row in rows:
classes_taken = row[count]
if not row.school_classcards.Unlimited:
class_price = row.school_classcards.Price / row.school_classcards.Classes
else:
class_price = row.school_classcards.QuickStatsAmount or 0
row_total = class_price * classes_taken
table.append(
TR(TD(max_string_length(row.school_classcards.Name, 46)),
TD(classes_taken), TD(represent_decimal_as_amount(class_price)),
TD(represent_decimal_as_amount(row_total))))
total += row_total
# cards sold footer
table.append(
TFOOT(
TR(TH(), TH(), TH(T("Total")),
TH(represent_decimal_as_amount(total)))))
box = DIV(
DIV(H3(T("Classes taken using cards"), _class='box-title'),
DIV(A(I(_class='fa fa-minus'),
_href='#',
_class='btn btn-box-tool',
_title=T("Collapse"),
**{'_data-widget': 'collapse'}),
_class='box-tools pull-right'),
_class='box-header'),
DIV(table, _class='box-body no-padding'),
_class='box box-danger',
)
return dict(box=box, total=total)
def get_debit_subscriptions(date):
"""
:param date: datetime.date
:return:
"""
from general_helpers import max_string_length
from openstudio.os_reports import Reports
reports = Reports()
total = 0
rows = reports.subscriptions_sold_on_date_summary_rows(date)
header = THEAD(
TR(
TH(T("Subscription")),
TH(T("# Sold")),
TH(T("Price")),
TH(T("Total")),
))
table = TABLE(header, _class='table table-striped table-hover')
for row in rows:
subscriptions_sold = row.school_subscriptions.CountSold or 0
row_total = (row.invoices_items.TotalPriceVAT
or 0) * subscriptions_sold
table.append(
TR(
TD(max_string_length(row.school_subscriptions.Name, 40)),
TD(subscriptions_sold),
TD(
represent_decimal_as_amount(
row.invoices_items.TotalPriceVAT)),
TD(represent_decimal_as_amount(row_total))))
total += row_total
# cards sold footer
table.append(
TFOOT(
TR(TH(), TH(), TH(T("Total")),
TH(represent_decimal_as_amount(total)))))
box = DIV(
DIV(H3(T("Subscriptions"), _class='box-title'),
DIV(A(I(_class='fa fa-minus'),
_href='#',
_class='btn btn-box-tool',
_title=T("Collapse"),
**{'_data-widget': 'collapse'}),
_class='box-tools pull-right'),
_class='box-header'),
DIV(table, _class='box-body no-padding'),
_class='box box-success',
)
return dict(box=box, total=total)
def get_credit_mollie_dropin_used_classes_summary(date):
"""
:param date: datetime.date
:return:
"""
from general_helpers import max_string_length
from openstudio.os_reports import Reports
reports = Reports()
total = 0
count = db.invoices_payments.Amount.count()
rows = reports.get_day_mollie_dropin_classes_taken_summary_day(date)
header = THEAD(
TR(
TH(T("Classes taken")),
TH(T("Class amount")),
TH(T("Total")),
))
table = TABLE(header, _class='table table-striped table-hover')
for row in rows:
classes_taken = row[count]
row_total = row.invoices_payments.Amount * classes_taken
table.append(
TR(TD(classes_taken),
TD(represent_decimal_as_amount(row.invoices_payments.Amount)),
TD(represent_decimal_as_amount(row_total))))
total += row_total
# cards sold footer
table.append(
TFOOT(TR(TH(), TH(T("Total")),
TH(represent_decimal_as_amount(total)))))
box = DIV(
DIV(H3(T("Drop-in classes taken (bought with Mollie)"),
_class='box-title'),
DIV(A(I(_class='fa fa-minus'),
_href='#',
_class='btn btn-box-tool',
_title=T("Collapse"),
**{'_data-widget': 'collapse'}),
_class='box-tools pull-right'),
_class='box-header'),
DIV(table, _class='box-body no-padding'),
_class='box box-danger',
)
return dict(box=box, total=total)
def get_debit_memberships(date):
"""
:param date: datetime.date
:return:
"""
from openstudio.os_reports import Reports
reports = Reports()
total = 0
count = db.school_memberships.id.count()
rows = reports.memberships_sold_summary_rows(date, date)
header = THEAD(
TR(
TH(T("Membership")),
TH(T("# Sold")),
TH(T("Price")),
TH(T("Total")),
))
table = TABLE(header, _class='table table-striped table-hover')
for row in rows:
cards_sold = row[count]
row_total = row.school_memberships.Price * cards_sold
table.append(
TR(TD(row.school_memberships.Name), TD(cards_sold),
TD(represent_decimal_as_amount(row.school_memberships.Price)),
TD(represent_decimal_as_amount(row_total))))
total += row_total
# cards sold footer
table.append(
TFOOT(
TR(TH(), TH(), TH(T("Total")),
TH(represent_decimal_as_amount(total)))))
box = DIV(
DIV(H3(T("Memberships"), _class='box-title'),
DIV(A(I(_class='fa fa-minus'),
_href='#',
_class='btn btn-box-tool',
_title=T("Collapse"),
**{'_data-widget': 'collapse'}),
_class='box-tools pull-right'),
_class='box-header'),
DIV(table, _class='box-body no-padding'),
_class='box box-success',
)
return dict(box=box, total=total)
def export_summary():
"""
Export summary of tax rates
:return: Excel worksheet
"""
from openstudio.os_reports import Reports
reports = Reports()
# create filestream
stream = io.BytesIO()
wb = openpyxl.workbook.Workbook(write_only=True)
# Create worksheet
ws = wb.create_sheet(title="Tax rate summary ")
data = reports.get_tax_summary_rows(
session.reports_tax_summary_index_date_from,
session.reports_tax_summary_index_date_until
)
rows = data['rows']
sum_subtotal = data['sum_subtotal']
sum_vat = data['sum_vat']
sum_total = data['sum_total']
ws.append([
"Date from",
"Date until",
"Tax rate",
"Revenue",
"VAT"
])
for i, row in enumerate(rows):
repr_row = list(rows[i:i + 1].render())[0]
ws.append([
session.reports_tax_summary_index_date_from,
session.reports_tax_summary_index_date_until,
repr_row.invoices_items.tax_rates_id or "Not specified",
row[sum_total],
row[sum_vat]
])
fname = T("TaxSummary") + '.xlsx'
wb.save(stream)
response.headers['Content-Type'] = 'application/vnd.ms-excel'
response.headers['Content-disposition'] = 'attachment; filename=' + fname
return stream.getvalue()
def get_debit_sales_summary_custom(date):
"""
:param date: datetime.date
:return:
"""
from general_helpers import max_string_length
from openstudio.os_reports import Reports
reports = Reports()
total = 0
rows = reports.shop_sales_custom(date, date)
header = THEAD(TR(
TH(T("Item")),
TH(T("Description")),
TH(T("Total")),
))
table = TABLE(header, _class='table table-striped table-hover')
for row in rows:
table.append(
TR(
TD(row.ProductName),
TD(max_string_length(row.Description, 40),
_title=row.Description),
TD(represent_decimal_as_amount(row.TotalPriceVAT)),
))
total += row.TotalPriceVAT
# cards sold footer
table.append(
TFOOT(TR(TH(T("Total")), TH(),
TH(represent_decimal_as_amount(total)))))
box = DIV(
DIV(H3(T("Shop sales - custom items"), _class='box-title'),
DIV(A(I(_class='fa fa-minus'),
_href='#',
_class='btn btn-box-tool',
_title=T("Collapse"),
**{'_data-widget': 'collapse'}),
_class='box-tools pull-right'),
_class='box-header'),
DIV(table, _class='box-body no-padding'),
_class='box box-success',
)
return dict(box=box, total=total)
def get_class_revenue():
"""
:return:
"""
from openstudio.os_reports import Reports
set_headers()
clsID = request.vars['clsID']
date_received = request.vars['date']
date = datestr_to_python("%Y-%m-%d", date_received)
reports = Reports()
return dict(revenue=reports.get_class_revenue_summary(clsID, date))
def get_debit_sales_summary(date):
"""
:param date: datetime.date
:return:
"""
from general_helpers import max_string_length
from openstudio.os_reports import Reports
reports = Reports()
total = 0
records = reports.shop_sales_summary(date, date)
header = THEAD(TR(
TH(T("G/L Account")),
TH(T("Total")),
))
table = TABLE(header, _class='table table-striped table-hover')
for record in records:
table.append(
TR(
TD(record[1]),
TD(represent_float_as_amount(record[0])),
))
total += record[0]
# cards sold footer
table.append(
TFOOT(TR(TH(T("Total")), TH(represent_float_as_amount(total)))))
box = DIV(
DIV(H3(T("Shop sales by G/L Account"), _class='box-title'),
DIV(A(I(_class='fa fa-minus'),
_href='#',
_class='btn btn-box-tool',
_title=T("Collapse"),
**{'_data-widget': 'collapse'}),
_class='box-tools pull-right'),
_class='box-header'),
DIV(table, _class='box-body no-padding'),
_class='box box-success',
)
return dict(box=box, total=total)
def get_credit_shop_sales_not_paid_with_cash(date):
"""
:param date: datetime.date
:return:
"""
from general_helpers import max_string_length
from openstudio.os_reports import Reports
reports = Reports()
total = 0
# count = db.school_subscriptions.id.count()
amount = reports.shop_sales_not_paid_with_cash_summary(date, date)
total += amount
header = THEAD(TR(
TH(T("Sales")),
TH(T("Amount")),
))
table = TABLE(header, _class='table table-striped table-hover')
table.append(
TR(
TD(T("Paid not using cash")),
TD(represent_decimal_as_amount(amount)),
))
box = DIV(
DIV(H3(T("Non cash sales payments"), _class='box-title'),
DIV(A(I(_class='fa fa-minus'),
_href='#',
_class='btn btn-box-tool',
_title=T("Collapse"),
**{'_data-widget': 'collapse'}),
_class='box-tools pull-right'),
_class='box-header'),
DIV(table, _class='box-body no-padding'),
_class='box box-danger',
)
return dict(box=box, total=total)
def export_details():
"""
Export summary of tax rates
:return: Excel worksheet
"""
from openstudio.os_reports import Reports
reports = Reports()
tID = request.vars['tID']
tax_rate = db.tax_rates(tID)
name = "Not specified"
if tax_rate:
name = tax_rate.Name
# create filestream
stream = io.BytesIO()
wb = openpyxl.workbook.Workbook(write_only=True)
# write the sheet for all mail addresses
ws = wb.create_sheet(title="Tax rate details")
ws.append([
name,
'from:',
session.reports_tax_summary_index_date_from,
'until:',
session.reports_tax_summary_index_date_until
])
reports = Reports()
rows = reports.get_tax_summary_detail_rows(
tID,
session.reports_tax_summary_index_date_from,
session.reports_tax_summary_index_date_until
)
ws.append([
"Invoice ID",
"Customer ID",
"Customer",
"Product name",
"Description",
"Quantity",
"Item price",
"Sub total",
"Tax rate",
"VAT",
"Total",
])
for i, row in enumerate(rows):
repr_row = list(rows[i:i + 1].render())[0]
ws.append([
row.invoices.InvoiceID,
row.auth_user.id,
row.auth_user.display_name,
row.invoices_items.ProductName,
row.invoices_items.Description,
row.invoices_items.Quantity,
row.invoices_items.Price,
row.invoices_items.TotalPrice,
repr_row.invoices_items.tax_rates_id,
row.invoices_items.VAT,
row.invoices_items.TotalPriceVAT,
])
fname = T("TaxRateDetails") + '.xlsx'
wb.save(stream)
response.headers['Content-Type'] = 'application/vnd.ms-excel'
response.headers['Content-disposition'] = 'attachment; filename=' + fname
return stream.getvalue()
def details():
"""
Details page to show full list of invoice items for tax rate in period
:return:
"""
from general_helpers import max_string_length
from openstudio.os_reports import Reports
response.title = T("Reports")
tID = request.vars['tID']
response.subtitle = details_subtitle(tID)
response.view = 'reports_tax_summary/index.html'
reports = Reports()
rows = reports.get_tax_summary_detail_rows(
tID,
session.reports_tax_summary_index_date_from,
session.reports_tax_summary_index_date_until
)
# Counters
subtotal = 0
vat = 0
total = 0
# populate rows
header = THEAD(TR(
TH(T("Invoice ID")),
TH(T("Customer")),
TH(T("Product name")),
TH(T("Description")),
TH(T("Quantity")),
TH(T("Item price")),
TH(T("Sub total")),
TH(T("Tax rate")),
TH(T("VAT")),
TH(T("Total")),
))
table = TABLE(header, _class='table table-striped table-hover table-condensed small_font')
for i, row in enumerate(rows):
repr_row = list(rows[i:i + 1].render())[0]
subtotal += row.invoices_items.TotalPrice or 0
vat += row.invoices_items.VAT or 0
total += row.invoices_items.TotalPriceVAT or 0
table.append(TR(
TD(A(row.invoices.InvoiceID,
_href=URL('invoices', 'edit', vars={'iID': row.invoices.id}))),
TD(A(max_string_length(row.auth_user.display_name, 20),
_href=URL('customers', 'edit', args=[row.auth_user.id]))),
TD(max_string_length(row.invoices_items.ProductName, 20)),
TD(max_string_length(row.invoices_items.Description, 40)),
TD(row.invoices_items.Quantity),
TD(repr_row.invoices_items.Price),
TD(repr_row.invoices_items.TotalPrice),
TD(repr_row.invoices_items.tax_rates_id),
TD(repr_row.invoices_items.VAT),
TD(repr_row.invoices_items.TotalPriceVAT),
))
totals = DIV(
DIV(LABEL(T("Sub total")), BR(),
represent_float_as_amount(subtotal),
_class='col-md-2'),
DIV(LABEL(T("VAT")), BR(),
represent_float_as_amount(vat),
_class='col-md-2'),
DIV(LABEL(T("Total")), BR(),
represent_float_as_amount(total),
_class='col-md-2'),
_class='row'
)
back = os_gui.get_button(
'back',
URL('index')
)
return dict(
form = totals,
content = table,
back = back,
export = details_get_export(tID)
)
def index():
"""
Main page for reports tax summary controller
"""
response.title = T("Reports")
response.subtitle = T("Tax summary")
from openstudio.os_reports import Reports
index_process_request_vars()
reports = Reports()
data = reports.get_tax_summary_rows(
session.reports_tax_summary_index_date_from,
session.reports_tax_summary_index_date_until
)
rows = data['rows']
sum_subtotal = data['sum_subtotal']
sum_vat = data['sum_vat']
sum_total = data['sum_total']
header = THEAD(TR(
TH("Tax rate"),
TH(SPAN("Revenue", _class='pull-right')),
TH(SPAN("Taxes", _class='pull-right')),
TH()
))
content = TABLE(
header,
_class='table table-striped table-hover'
)
for i, row in enumerate(rows):
repr_row = list(rows[i:i + 1].render())[0]
vars = {}
if row.invoices_items.tax_rates_id:
vars = {'tID': row.invoices_items.tax_rates_id}
details = os_gui.get_button(
'noicon',
URL('details', vars=vars),
title=T("Details"),
_class='pull-right'
)
content.append(TR(
TD(repr_row.invoices_items.tax_rates_id or "Not specified"),
TD(SPAN(represent_float_as_amount(row[sum_total]), _class='pull-right')),
TD(SPAN(represent_float_as_amount(row[sum_vat]), _class='pull-right')),
TD(details)
))
result = index_get_form(
session.reports_tax_summary_index_date_from,
session.reports_tax_summary_index_date_until,
)
form = result['form']
show_current_month = A(
T("Current month"),
_href=URL('index_show_current_month'),
_class='btn btn-default pull-right'
)
header_tools = SPAN(
show_current_month
)
export = index_get_export()
return dict(
form=result['form_display'],
content=content,
submit=result['submit'],
header_tools=header_tools,
export=export,
)
def get_debit_classes(date, list_type='balance'):
"""
return a box and total of class profit or class revenue
:param list_type: one of 'revenue' or 'teacher_payments'
:param date: datetime.date
:return:
"""
from general_helpers import max_string_length
from openstudio.os_reports import Reports
reports = Reports()
revenue = reports.get_classes_revenue_summary_day(
session.finance_cashbook_date)
if list_type == 'balance':
total = revenue['balance']
box_title = T("Class balance")
elif list_type == 'teacher_payments':
total = revenue['teacher_payments']
box_title = T("Teacher payments")
header = THEAD(
TR(
TH(T("Time")),
TH(T("Location")),
TH(T("Classtype")),
TH(T("Attendance")),
TH(T("Amount")),
))
table = TABLE(header, _class='table table-striped table-hover')
for cls in revenue['data']:
if list_type == 'balance':
amount = represent_decimal_as_amount(cls['Balance'])
elif list_type == 'teacher_payments':
amount = A(represent_decimal_as_amount(cls['TeacherPayment']),
_href=URL("classes",
"revenue",
vars={
"clsID": cls["ClassesID"],
"date": date.strftime(DATE_FORMAT)
}),
_target="_blank")
teachers = cls['Teachers']
if not 'teacher' in teachers:
teacher = T("Teacher not found")
else:
sub = T(" (sub)") if teachers['teacher_sub'] else ""
teacher = teachers['teacher'].display_name + sub
tr = TR(
TD(cls['Starttime']), TD(max_string_length(cls['Location'], 18)),
TD(max_string_length(cls['ClassType'], 18),
BR(),
SPAN(max_string_length(teacher, 28),
_class="text-muted text_small"),
_title=teacher), TD(cls['CountAttendance']), TD(amount))
table.append(tr)
# Footer total
table.append(
TFOOT(
TR(TH(), TH(), TH(), TH(T('Total')),
TH(represent_decimal_as_amount(total)))))
box = DIV(
DIV(H3(box_title, _class='box-title'),
DIV(A(I(_class='fa fa-minus'),
_href='#',
_class='btn btn-box-tool',
_title=T("Collapse"),
**{'_data-widget': 'collapse'}),
_class='box-tools pull-right'),
_class='box-header'),
DIV(table, _class='box-body no-padding'),
_class='box box-success',
)
return dict(box=box, total=total)
def get_debit_classes(date, list_type='balance'):
"""
return a box and total of class profit or class revenue
:param list_type: one of 'revenue' or 'teacher_payments'
:param date: datetime.date
:return:
"""
from general_helpers import max_string_length
from openstudio.os_reports import Reports
reports = Reports()
revenue = reports.get_classes_revenue_summary_day(
session.finance_cashbook_date)
if list_type == 'balance':
total = revenue['revenue_total']
box_title = T("Class balance")
elif list_type == 'teacher_payments':
total = revenue['teacher_payments']
box_title = T("Teacher payments")
header = THEAD(
TR(
TH(T("Time")),
TH(T("Location")),
TH(T("Classtype")),
TH(T("Amount")),
))
table = TABLE(header, _class='table table-striped table-hover')
for cls in revenue['data']:
if list_type == 'balance':
amount = cls['Balance']
elif list_type == 'teacher_payments':
amount = cls['TeacherPayment']
tr = TR(TD(cls['Starttime']),
TD(max_string_length(cls['Location'], 18)),
TD(max_string_length(cls['ClassType'], 18)),
TD(represent_float_as_amount(amount)))
table.append(tr)
# Footer total
table.append(
TFOOT(
TR(TH(), TH(), TH(T('Total')),
TH(represent_float_as_amount(total)))))
box = DIV(
DIV(H3(box_title, _class='box-title'),
DIV(A(I(_class='fa fa-minus'),
_href='#',
_class='btn btn-box-tool',
_title=T("Collapse"),
**{'_data-widget': 'collapse'}),
_class='box-tools pull-right'),
_class='box-header'),
DIV(table, _class='box-body no-padding'),
_class='box box-success',
)
return dict(box=box, total=total)
def get_credit_shop_sales_not_paid_with_cash(date):
"""
:param date: datetime.date
:return:
"""
from general_helpers import max_string_length
from openstudio.os_reports import Reports
reports = Reports()
total = 0
# non cash payments from receipts
rows = reports.shop_sales_not_paid_with_cash_summary(date, date)
sum = db.receipts_amounts.TotalPriceVAT.sum()
# online mollie payments from invoice_payments
amount_paid_using_mollie = reports.shop_sales_mollie_summary(date, date)
header = THEAD(TR(
TH(T("Payment method")),
TH(T("Amount")),
))
table = TABLE(header, _class='table table-striped table-hover')
table.append(
TR(
TD(T("Mollie (online shop)")),
TD(represent_decimal_as_amount(amount_paid_using_mollie)),
))
for row in rows:
amount = row[sum]
total += amount
table.append(
TR(
TD(row.payment_methods.Name),
TD(represent_decimal_as_amount(amount)),
))
table.append(
TFOOT(TR(
TH(T("Total")),
TH(represent_decimal_as_amount(total)),
)))
box = DIV(
DIV(H3(T("Non cash sales payments"), _class='box-title'),
DIV(A(I(_class='fa fa-minus'),
_href='#',
_class='btn btn-box-tool',
_title=T("Collapse"),
**{'_data-widget': 'collapse'}),
_class='box-tools pull-right'),
_class='box-header'),
DIV(table, _class='box-body no-padding'),
_class='box box-danger',
)
return dict(box=box, total=total)
