"""
Author: Scott Jorgensen
Date: 29th February 2012
Purpose: Tests the loading of the CS Call account into the dev DB
"""
import os
import sys
sys.path.append(r"c:\src\trunk\src")
import datetime
from kew.pe.control import Control
from pkg_resources import resource_filename
if __name__ == "__main__":
dburl = "mysql://*****:*****@localhost/kew_pe?charset=utf8"
date = datetime.datetime.now().date()
dt = date.strftime("%Y%m%d")
base = r"S:\Kew Shared\Operations\Vollin Position & Cash Rec\Cash And Position Files"
sql = "select account.id as acc_id, account.account_category as deal_name, deal.name as deal, entity.name as company,account.bank as bank,account.acc_number,account.acc_type,account.margin,account.iban, account.currency, ab3.amount as amount, ab3.dt as bal_date, account.note, account.collateral, account.automatic from account inner join (select account_balance.amount, account_balance.account_id, account_balance.as_of_date as dt from account_balance inner join (select max(account_balance.as_of_date) as max_date, account_balance.account_id from account_balance group by account_balance.account_id) ab1 on account_balance.as_of_date = ab1.max_date and account_balance.account_id = ab1.account_id) ab3 on account.id = ab3.account_id inner join entity on account.entity_id = entity.id left join d2_e on account.entity_id = d2_e.entity_id left join deal on d2_e.deal_id = deal.id where account.live = 1"
c = Control(file_archive=None, db_url=dburl)
# c.fetch_report_data()
c.fetch_exception_data()
def run(self):
self.tmpl = resource_filename("illiquids.reports","exceptions.html")
args = {
'cols' : ['deal', 'entity', 'bank', 'account_type', 'account', 'iban', 'currency', 'balance', 'collateral', 'fx', 'bal_usd', 'subtotal', 'total', 'balance_date', 'age'],
'headers' : {
'deal' : {'name' : 'Deal',
'class':'text',
'_fmt' : fmtdata1},
'entity' : {'name' : 'Entity',
'class':'text',
'_fmt' : fmtdata1},
'bank' : {'name' : 'Bank',
'class':'text',
'_fmt' : fmtdata1},
'account' : {'name' : 'Account#',
'class':'text',
'_fmt' : fmtdata1},
'account_type' : {'name' : 'Account Type',
'class':'text',
'_fmt' : fmtdata1},
'iban' : {'name' : 'IBAN',
'class':'text',
'_fmt' : fmtdata1},
'currency' : {'name' : 'CCY',
'class':'text',
'_fmt' : fmtdata1},
'balance' : {'name' : 'Balance',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 0)},
'collateral' : {'name' : 'Collateral',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 0)},
'fx' : {'name' : 'FX Rate',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 4)},
'bal_usd' : {'name' : 'USD Equiv.',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 0, '$')},
'subtotal' : {'name' : 'Sub Total',
'class':'number',
'_fmt' : lambda a : fmtdata1(a, 0, '$')},
'total' : {'name' : 'Total',
'class':'number',
'_fmt' : lambda a : fmtdata1(a, 0, '$')},
'balance_date' : {'name' : 'Balance Date',
'class':'text',
'_fmt' : fmtdata1},
'age' : {'name' : 'Aged',
'class':'number',
'_fmt' : lambda a: fmtdata1(a, 0)},
},
'asof' : self._asof.strftime('%A, %d %B %Y') ,
'acc_info': []
}
# Note - CRM url is hard coded!
control = Control(file_archive=None,self._dburl)
aged, no_data, dropped, age_limit = control.fetch_exception_data()
args['acc_info'] = ((aged, "Aged Accounts (Balances older than %s days)" % age_limit, "No old balances to report"),
(no_data, "Accounts that have never had a balance", "All accounts have a balance."),
(dropped, "Automatic accounts not loading automatically (Balance >1 day old)", "No old balances to report")
)
self._gen_report_(args)
# Now convert it to a pdf file
pdf_output = self.outputfile.replace('.html', '.pdf')
pdfargs = ['--username','corp\\reportingdb', '--password','&AdRekAd']
htmltopdf([os.path.abspath(self.outputfile)], os.path.abspath(pdf_output), orientation='landscape', pdfargs=pdfargs)
