""" 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)