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 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 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 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 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 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 cfy_platform_type_revenue():
result = tq.query('PLATFORM_TO_TQTYPE_TOTAL')
rows = result.rows
data = dict(timestamp=result.timestamp,
sankey=[(row.ATTR1, row.ATTR2, float(row.TOTAL))
for row in rows])
return data
def overview():
fy = int(
request_arg('fy', TLMA.cfy, type_func=int, test_func=tests.is_year))
updates = [('PLEDGE_CREATED_FY', fy), ('BASE_QUERY', '')]
return dict(data=Tq.query(('CTE', 'CTE_PLEDGE'),
cached_timeout=30,
updates=updates),
thisfy=fy)
def fys_summary():
ltd = request_arg('ltd',
0,
type_func=int,
test_func=lambda x: tests.is_int(x) and
(int(x) == 0 or int(x) == -1))
params = (TLMA.cfy, ltd)
return Tq.query('FY_SUMMARY', *params, cached_timeout=30)
def journey_cureone_acquisiton_donors():
campaign_code = request_arg('camapgincode',
'%19AC.Cure%One%Acquisition%',
test_func=lambda x: tests.is_valid_string(
x, nosql=True, max_length=30))
updates = [('CAMPAIGN_CODE', campaign_code, '\'')]
return Tq.query('JOURNEY_CUREONE_ACQUISITON_DONORS',
cached_timeout=10,
updates=updates)
def source_code1_summary():
s1 = request_arg('s1',
'',
test_func=lambda x: tests.is_valid_string(
x, nosql=True, max_length=30))
updates = [('SOURCECODE1', s1, '\'')]
return Tq.query('_SINGLE_SOURCECODE_SUMMAY',
cached_timeout=30,
updates=updates)
def delinquency():
data = {
'general_warning_days': 90,
'dd_warning_days': 20,
'cc_warning_days': 5
}
updates = [('BASE_QUERY', '')]
data['data'] = Tq.query(('CTE', 'PLEDGE_DELINQUENCY'),
cached_timeout=10,
updates=updates)
return data
def segments():
data = Tq.query('STATS_SEGMENT', cached_timeout=120)
return dict(title='Segments', data=data)
def contact_created_fy():
return Tq.query('STATS_CONTACTS_CREATED_FY', cached_timeout=60)
def marketing_cycle():
updates = (('BASE_QUERY', ''), )
return Tq.query(('CTE', 'CTE_MARKETING_CYCLE'),
cached_timeout=10,
updates=updates)
def contacts_snapshot():
data = Tq.query('STATS_CONTACTS', cached_timeout=10)
return dict(title='Contacts', data=data)
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 sourcecode1_active():
return {'data': Tq.query('SOURCECODE_ACTIVE', cached_timeout=10)}
def pledge_types():
updates = [('BASE_QUERY_TABLE', 'cte_pledge_types')]
return Tq.query('CTE', cached_timeout=30, updates=updates)
def pending_split():
result = tq.query('PENDING_SPLIT')
data = dict(timestamp=result.timestamp, data=result.rows)
return data
def date_of_payments_fy():
data = Tq.query('LIST_FY_DATEOFPAYMENT', cached_timeout=600)
return data.rows
def pending_total():
result = tq.query('PENDING_TOTAL')
data = dict(total=result.rows[0].TOTAL, creators=result.rows[0].CREATEORS)
return data
def christmas_appeal_donors():
return Tq.query('JOURNEY_CHRISTMAS_APPEAL_DONORS', cached_timeout=20)
def approved_split():
result = tq.query('APPROVED_SPLIT')
data = dict(timestamp=result.timestamp, data=result.rows)
return data
def pledge_headers():
fy = request_arg('fy', TLMA.cfy, type_func=int, test_func=tests.is_year)
updates = (('PLEDGE_CREATED_FY', fy), ('BASE_QUERY', ''))
return Tq.query(('CTE', 'CTE_PLEDGE'), cached_timeout=30, updates=updates)
