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)