def comparative():
fy = request_arg('fy', TLMA.cfy, type_func=int, test_func=tests.is_year)
# Prepare for SQL parameters
d1, d2 = TLMA.fy_range(fy)
progress = (datetime.date.today() - d1).days / (d2 - d1).days
# Run Query By including 2 financial years
d1 = TLMA.fy_range(fy - 1)[0]
params = Tq.format_date((d1, d2))
# updates = [('PAYMENT_DATE1', d1, '\''), ('PAYMENT_DATE2', d2, '\'')]
data = Tq.query('PRIVENUE__BASE', *params, cached_timeout=180)
# Get Budget
budget = TLMA.budget()
return dict(cfy=fy, data=data, progress=progress, budget=budget)
def single_campaign():
fy = request_arg('fy', TLMA.cfy, type_func=int, test_func=tests.is_year)
# Default to 10 minutes cache or else if the FY in URL is not CFY, then cache it for a longer period of time
timeout = 600 if fy == TLMA.cfy else 2000
# Prepare for SQL parameters
d1, d2 = TLMA.fy_range(fy)
params = Tq.format_date((d1, d2))
campaign_codes = Tq.query('LIST_FY_CAMPAIGNCODES',
*params,
cached_timeout=timeout)
# Default Values
data = None
# Update campaign_code if found argument from URL query string called 'campaign_code'
campaign_code = request_arg('campaign_code',
None,
test_func=lambda x: tests.is_valid_string(
x, nosql=True, max_length=50))
if campaign_code is not None:
update = [('CAMPAIGN_CODE', str(campaign_code), '\'')]
data = Tq.query('PRIVENUE_SINGLE_CAMPAIGN',
cached_timeout=200,
updates=update)
return dict(thisfy=fy,
fys=date_of_payments_fy(),
data=data,
thiscampaign=campaign_code,
campaigncodes=campaign_codes)
def merch_activities():
fy = request_arg('fy', TLMA.cfy, type_func=int, test_func=tests.is_year)
d1, d2 = Tq.format_date(TLMA.fy_range(fy))
updates = [('BASE_QUERY', ''), ('PAYMENT_DATE1', d1, '\''),
('PAYMENT_DATE2', d2, '\'')]
return Tq.query(('CTE', 'CTE_MERCH_ACTIVITY_SUMMARY'),
cached_timeout=60,
updates=updates)
def new_customer_list():
fy = request_arg('fy', TLMA.cfy, type_func=int, test_func=tests.is_year)
d1, d2 = TLMA.fy_range(fy)
# params = (Tq.format_date(d1), Tq.format_date(d2))
updates = [('DATE_START', Tq.format_date(d1), '\''),
('DATE_END', Tq.format_date(d2), '\'')]
return Tq.query('JOURNEY_NEW_MERCHANDISE_CUSTOMER',
cached_timeout=10,
updates=updates)
def payments():
fy = request_arg('fy', TLMA.cfy, type_func=int, test_func=tests.is_year)
d1, d2 = Tq.format_date(TLMA.fy_range(fy))
# d1 = request_arg('fy', Tq.format_date(TLMA.fy_range(TLMA.cfy)[0]), lambda x: tests.is_valid_string(x, max_length=10))
# d2 = request_arg('fy', Tq.format_date(TLMA.fy_range(TLMA.cfy)[1]), lambda x: tests.is_valid_string(x, max_length=10))
updates = [('BASE_QUERY', ''), ('PAYMENT_DATE1', d1, '\''),
('PAYMENT_DATE2', d2, '\'')]
return Tq.query(('CTE', 'CTE_PAYMENTS'),
cached_timeout=60,
updates=updates)
def pledge_income_fy(fy=TLMA.cfy):
fy = request_arg('fy', TLMA.cfy, int, tests.is_year)
d1, d2 = Tq.format_date(TLMA.fy_range(fy))
update = [('DOP_START', d1, '\''), ('DOP_END', d2, '\''),
('BASE_QUERY', '')]
data = Tq.query(('CTE', 'PLEDGE_INCOME'),
updates=update,
cached_timeout=25)
fys = pledge_dop_fys()
return dict(data=data, this_fy=fy, fys=fys)
def fishing_pool_sankey():
fy = request_arg('fy', TLMA.cfy, type_func=int, test_func=tests.is_year)
d1, d2 = TLMA.fy_range(fy)
updates = (('BASE_QUERY', ''),
('FISHINGPOOL_DATE1',
Tq.format_date(d1 - datetime.timedelta(days=1)),
'\''), ('FISHINGPOOL_DATE2',
Tq.format_date(datetime.date.today()), '\''))
return Tq.query(('CTE', 'CTE_FISHING_POOL_SANKEY'),
cached_timeout=30,
updates=updates)
def get_first_date_source1_by_contacts_since():
sn = request_arg(
'sn', '', test_func=lambda x: tests.is_valid_string(x, max_length=7))
since = request_arg(
'since',
Tq.format_date(TLMA.fy_range(TLMA.cfy)[0]),
test_func=lambda x: tests.is_valid_string(x, max_length=10))
return Tq.query('CONTACTS_FIRSTDAY_SOURCECODES_SINCE',
sn,
since,
cached_timeout=120)
def pledges(fy=TLMA.cfy):
d1, d2 = TLMA.fy_range(fy)
d2 = d2 + datetime.timedelta(days=1)
d1, d2 = Tq.format_date((d1, d2))
update = [('CREATED_START', d1, '\''), ('CREATED_END', d2, '\''),
('BASE_QUERY', '')]
data = Tq.query(('CTE', 'PLEDGE_DETAIL'),
updates=update,
cached_timeout=15)
fys = pledge_created_fys()
return dict(data=data, this_fy=fy, fys=fys)
def revenue_streams():
fy = request_arg('fy', TLMA.cfy, type_func=int, test_func=tests.is_year)
# Default to 3 minutes cache or else if the FY in URL is not CFY, then cache it for a longer period of time
timeout = 180 if fy == TLMA.cfy else 2000
# Prepare for SQL parameters
d1, d2 = TLMA.fy_range(fy)
params = Tq.format_date((d1, d2))
# data = Tq.query(['PRIVENUE_REVENUE_STREAMS__BASE', 'PRIVENUE_REVENUE_STREAMS_UNION'], *params, cached_timeout=timeout)
data = Tq.query('PRIVENUE_REVENUE_STREAMS__BASE',
*params,
cached_timeout=timeout)
return dict(data=data, thisfy=fy, fys=date_of_payments_fy())
def merchandise_new():
fy = request_arg('fy', TLMA.cfy, type_func=int, test_func=tests.is_year)
d1, d2 = TLMA.fy_range(fy)
# params = (Tq.format_date(d1), Tq.format_date(d2))
updates = [('DATE_START', Tq.format_date(d1), '\''),
('DATE_END', Tq.format_date(d2), '\'')]
data = Tq.query('JOURNEY_NEW_MERCHANDISE_CUSTOMER',
cached_timeout=30,
updates=updates)
return dict(title='Merchandise New Customer Journey',
data=data,
dates=(d1, d2),
thisfy=fy,
cost=TLMA.acquisition_cost)
def financial_year_month(value):
return TLMA.fy_mth(value)
def filter_financial_year(value):
return "{:d}".format(TLMA.fy(value)) if value else None
def budget():
return ApiResult(TLMA.budget())
def sourcecode1_created():
# Prepare for SQL parameters
d1, d2 = TLMA.ccy_date(TLMA.fy12m, 1), TLMA.cfy_end_date
params = Tq.format_date((d1, d2))
data = Tq.query('SOURCECODE_CREATED', *params, cached_timeout=10)
return dict(data=data, d1=d1, d2=d2)
def inject_fy_month_list(): from app.database.tlma import TLMA return dict(fymths=TLMA.get_fy_month_list())