import acm
import ael
import FHTI_EDD_OTC_Util
import HTI_ExcelReport2
import HTI_Util
import HTI_FeedTrade_EDD_Util
import os
import math
from shutil import copyfile

ttSaveToFile = "Check this to save the report instead of showing it."
ttCSV = "Check this to export the report in CSV format"
ttFileName = "File name and path of the report. YYYYMMDD in the file name will be replaced by the valuation date."
ttSendMail = "Send report as email attachment."


Trade_Date = 0
Client_Code = 1
TRS_Contracts_No = 2
BBG_Code = 3
Underlying_Type = 4
Currency = 5
Buy_Sell = 6
Exe_Qty = 7
Avg_Exe_Price = 8
Trading_Fee = 9
Levy = 10
Stamp = 11
Exe_Notional = 12

 


Commission = 13
Commission_Currency = 14
Total_Settlement = 15
Base_Currency = 16
FX_Rate = 17

Trade_No = 18 
Short_Portfolio = 19 
Buy_Back = 20 
Trd_Long_Pfr_IA = 21 
Trd_Short_Pfr_IA = 22 
TrdNo = 23 
Trading_Fee_Currency = 24 
Transaction_Levy_Currency = 25 
Stamp_Currency = 26  
Value_Day = 27 
Acquire_Day = 28 



def cb4(index, fieldValues):
    global ael_variables        
    if ael_variables[index][0] == 'saveToNetwork':
        disable_variables(('networkDriveLocation',), fieldValues[index])  
            
    return fieldValues



def getFx(dt, fm_ccy, to_ccy, currclspricemkt, histclspricemkt):
    ins_fm_ccy = ael.Instrument[fm_ccy]
    ins_to_ccy = ael.Instrument[to_ccy]
    ins_usd = ael.Instrument['USD']
    try:
        if dt == ael.date_today():
            fm_usd_rate = ins_fm_ccy.used_price(ael.date_today(), ins_usd.insid, 'Last', 0, currclspricemkt)
            to_usd_rate = ins_usd.used_price(ael.date_today(), ins_to_ccy.insid, 'Last', 0, currclspricemkt)
            fx_rate = fm_usd_rate * to_usd_rate
        else:
            fm_usd_rate = ins_fm_ccy.used_price(dt, ins_usd.insid, 'Close', 0, histclspricemkt)
            to_usd_rate = ins_usd.used_price(dt, ins_to_ccy.insid, 'Close', 0, histclspricemkt)
            fx_rate = fm_usd_rate * to_usd_rate
    except:
        fx_rate = 0.0        
        
    return fx_rate

def report_compare(x, y):
    return 1
    '''
    if x[Contract_Date] == '':
        return -1
    if y[Contract_Date] == '':
        return 1
    
    if ael.date(x[Contract_Date]).to_string('%Y%m%d') > ael.date(y[Contract_Date]).to_string('%Y%m%d'):
        return 1  
    elif ael.date(x[Contract_Date]).to_string('%Y%m%d') < ael.date(y[Contract_Date]).to_string('%Y%m%d'):
        return -1
        
    if x[Contract_No] > y[Contract_No]:
        return 1  
    elif x[Contract_No] < y[Contract_No]:
        return -1
        
    if x[Counterparty] > y[Counterparty]:
        return 1  
    elif x[Counterparty] < y[Counterparty]:
        return -1
        
    return 0
    '''

def disable_variables(variables, enable = 0):
    for i in variables:            
        for j in ael_variables:
            if i == j[0]:
                j[9] = enable

def cb(index, fieldValues):
    global ael_variables        
    if ael_variables[index][0] == 'saveToFile':        
        disable_variables(('fileName',), fieldValues[index])        
    
    return fieldValues


def cb2(index, fieldValues):
    global ael_variables        
    if ael_variables[index][0] == 'sendEmail':        
        disable_variables(('emaillist',), fieldValues[index])
        disable_variables(('subject',), fieldValues[index])
    
    return fieldValues
    
def cb3(index, fieldValues):
    global ael_variables        
    if ael_variables[index][0] == 'saveToCSV':
        disable_variables(('fileName',), fieldValues[index])  
            
    return fieldValues


ael_variables = [['asofdate', 'Date', 'string', [str(ael.date_today()), 'Today'], 'Today', 1, 0, 'Report Date', None, 1], \
                ['saveToFile', 'Save to file', 'int', [1, 0], 0, 0, 0, ttSaveToFile, cb, None], \
                ['fileName', 'File name', 'string', None, 'c:\\temp\\DeltaOnelMgt_YYYYMMDD', 0, 0, ttFileName, None, 0], \
                ['sendEmail', 'Send mail', 'int', [1, 0], 0, 0, 0, ttSendMail, cb2, None], \
                ['emaillist', 'Email', 'string', None, 'louis.ck.wong@htisec.com', 0, 0, 'Email List', None, 0], \
                ['acquirers', 'Acquirer(s)', 'string', HTI_Util.getAllAcquirers(), None, 1, 1, 'Acquirer(s)', None, 1], \
                ['counterparties', 'Counterparty(s)', 'string', HTI_Util.getAllParties(), None, 0, 1, 'Counterparty(s)', None, 1], \
                ['portfolio', 'Portfolio', 'string', HTI_Util.getAllPortfolios(), None, 1, 1, 'Portfolio', None, 1], \
                ['subject', 'Email subject', 'string', None, 'FA4 (PROD) EDD DeltaOne Management Report (TRS) asof @date', 1, 0, 'Email Subject', None, 0], \
                ['saveToCSV', 'CSV format', 'int', [1, 0], 0, 0, 0, ttCSV, None, None], \
                ['title', 'Report title', 'string', None, 'Trade Details Report as of @date', 1, 0, 'Report Title', None, 1],
                ['period', 'Report Period', 'string', ['Inception', 'MTD'], 'Inception', 1, 0, 'Report Period', None, 1],
                ['currclspricemkt', 'Current Closing Price Market', 'string', None, '', 1, 0, 'Current Closing Price Market', None, 1],
                ['histclspricemkt', 'Historical Closing Price Market', 'string', None, '', 1, 0, 'Historical Closing Price Market', None, 1],
                ['base_ccy', 'Base Ccy', 'string', None, '', 1, 0, 'Base Ccy', None, 1], \
                ['fileperpty', 'Separte File for Counterparty', 'int', [1, 0], 0, 0, 0, 'Separte File for Counterparty', None, None], \
                ['saveToNetwork', 'Copy to Network', 'int', [1, 0], 0, 0, 0, 'Copy to Network Drive', cb4, None], \
                ['networkDriveLocation', 'Network Drive Location', 'string', None, 'C:\\temp\\PositionReport', 0, 0, 'Network Drive Location', None, 0]]
        
def ael_main(dict):
    asofdate = dict['asofdate']
    if asofdate == 'Today':
        asofdate = ael.date_today()    
    asofdate = ael.date(asofdate)
    
    title = dict['title'].replace('@date', str(asofdate))
    period = dict['period']
    
    subject = dict['subject'].replace('@date', str(asofdate))
    
    saveToFile = dict['saveToFile']
    saveToCSV = dict['saveToCSV']
    fileName = dict['fileName']    
    sendEmail = dict['sendEmail']    
    emailList = dict['emaillist']    
    recipients = emailList.split(',')
    
    fileName = fileName.replace("YYYYMMDD", asofdate.to_string('%Y%m%d'))
    
    fileperpty = dict['fileperpty']    
    saveToNetwork = dict['saveToNetwork']
    
    print 'saveToNetwork', saveToNetwork
    networkDriveLocation = dict['networkDriveLocation']
    
    
    # Portfolios
    portfolios = dict['portfolio'] 
    
    portfolioList2 = []
    pf_list = ''
    portfolioList2.extend(portfolios)
    for port in portfolioList2:
        prfid = port
        pfarr = []
        pPf = ael.Portfolio[prfid]
        HTI_FeedTrade_EDD_Util.getChildPortfolio(pPf, pfarr)  
        if len(pfarr) > 0:
            for pf in pfarr:                
                if len(pf_list) != 0:          
                    pf_list = pf_list + ','                
                pf_list = pf_list + "'" + pf + "'"
        else:
            if len(pf_list) != 0:  
                pf_list = pf_list + ','                
            pf_list = pf_list + "'" + prfid + "'"   
            
            
    # Acquirers
    acq_array_list = dict['acquirers']
    
    acq_list = ''
    for acq in acq_array_list:
        if acq_list == '':
            acq_list = "'" + acq + "'"
        else:
            acq_list = acq_list + ",'" + acq + "'"
            
    
    # Counterparties
    pty_array_list = dict['counterparties']
    
    pty_list = ''
    for pty in pty_array_list:
        if pty_list == '':
            pty_list = "'" + pty + "'"
        else:
            pty_list = pty_list + ",'" + pty + "'"
    
    currclspricemkt = dict['currclspricemkt']
    histclspricemkt = dict['histclspricemkt']
    
    base_ccy = dict['base_ccy']
    
    if not fileperpty:
        genDeltaOneMgtRpt(asofdate, pf_list, acq_list, pty_list, base_ccy, currclspricemkt, histclspricemkt, title, subject, saveToFile, saveToCSV, sendEmail, emailList, fileName, fileperpty, period)
    else:
        strSql = getReportSql()
        select_fields = "select distinct cpty.ptyid 'client_code'"
        strSql = strSql.replace("@select_fields", select_fields)
        
        strSql = strSql.replace('@dt',  asofdate.add_days(1).to_string('%Y-%m-%d'))
        strSql = strSql.replace('@portfolio_list', pf_list)
        strSql = strSql.replace('@accquirer_list', acq_list)
        if pty_list != '':
            counterparty_list_sql = 'and cpty.ptyid in (@counterparty_list)'
            counterparty_list_sql = counterparty_list_sql.replace("@counterparty_list", pty_list)
            strSql = strSql.replace("@counterparty_list_sql", counterparty_list_sql)
        else:
            strSql = strSql.replace("@counterparty_list_sql", ' ')
        
        '''
        if period == 'MTD':
            fm_date = asofdate.first_day_of_month()
            print 'fm_date', fm_date, ael.date(fm_date).to_string('%Y-%m-%d')
            strSql = strSql.replace("@start_date", "and t.time >= '%s'" % (ael.date(fm_date).to_string('%Y-%m-%d')))
        else:
            strSql = strSql.replace("@start_date", ' ')
        ''' 
        
        strSql = strSql.replace("@start_date", ' ')
        
        print strSql    
        
        rs = ael.asql(strSql)                 
        columns, buf = rs
        rptContent = []
        for table in buf:     
            for row in table:
                ptyid = row[0]
                ptyfileName = fileName.replace('[ptyid]', ptyid)
                
                print 'ptyfileName', ptyfileName                
                dir_path = os.path.dirname(os.path.realpath(ptyfileName))
                if os.path.exists(dir_path) == False:
                    os.mkdir(dir_path)
                    
                exact_ptyfileName = os.path.basename(ptyfileName)                
                print 'exact_ptyfileName',exact_ptyfileName
                
                genDeltaOneMgtRpt(asofdate, pf_list, acq_list, "'"+ptyid+"'", base_ccy, currclspricemkt, histclspricemkt, title, subject, saveToFile, saveToCSV, sendEmail, emailList, ptyfileName, fileperpty, period)
                
                
                    
                #print ptyfileName, destination_fileName
                if saveToNetwork:
                    destination_fileName = networkDriveLocation + '\\' + exact_ptyfileName
                    destination_fileName = destination_fileName.replace('[ptyid]', ptyid)
                    dir_path = os.path.dirname(os.path.realpath(destination_fileName))
                    if os.path.exists(dir_path) == False:
                        os.mkdir(dir_path)
                    copyfile(ptyfileName+'.xlsx', destination_fileName+'.xlsx')
    
def monthCode(intMonth):
    switcher = {
        1: "Jan",
        2: "Feb",
        3: "Mar",
        4: "Apr",
        5: "May",
        6: "Jun",
        7: "Jul",
        8: "Aug",
        9: "Sep",
        10: "Oct",
        11: "Nov",
        12: "Dec",
    }
    return switcher.get(intMonth, "")
    
def getReportSql():
    strSql = """select t.trdnbr, add_info(t, 'Product_Strategy') 'Product_Strategy', add_info(t, 'MSS_Account') 'MSS_Account',
                add_info(t, 'Short Portfolio') 'Short_Portfolio', 
                add_info(t, 'Buy Back') 'Buy_Back', 
                add_info(t, 'Trd Long Pfr IA%') 'Trd_Long_Pfr_IA',
                add_info(t, 'Trd Short Pfr IA%') 'Trd_Short_Pfr_IA'
                into temp_trade
                from trade t, instrument i, portfolio pf, party a, party cpty
                where t.time < '@dt'
                @start_date
                and t.insaddr = i.insaddr
                and t.status not in ('Void', 'Simulated')
                and i.instype = 'TotalReturnSwap'
                and t.prfnbr = pf.prfnbr
                and pf.prfid in (@portfolio_list)
                and t.acquirer_ptynbr = a.ptynbr
                and a.ptyid in (@accquirer_list)
                and t.counterparty_ptynbr = cpty.ptynbr
                @counterparty_list_sql

                select a.insaddr, a.alias
                into temp_InstrumentAlias
                from InstrAliasType t, InstrumentAlias a
                where t.alias_type_name = 'BB_TICKER'
                and t.oid = a.type

                select p.trdnbr, i.insid 'curr_insid', p.amount
                into trading_fee_payment
                from payment p, instrument i
                where p.type = 'Trading Fee'
                    and p.curr = i.insaddr

                select p.trdnbr, i.insid 'curr_insid', p.amount
                into levy_payment
                from payment p, instrument i
                where p.type = 'Transaction Levy'
                    and p.curr = i.insaddr
                
                select p.trdnbr, i.insid 'curr_insid', p.amount
                into commission_payment
                from payment p, instrument i
                where p.type = 'Commission'
                    and p.curr = i.insaddr

                select p.trdnbr, i.insid 'curr_insid', p.amount
                into stamp_payment
                from payment p, instrument i
                where p.type = 'Stamp Duty'
                    and p.curr = i.insaddr

                @select_fields
                from temp_trade tt, trade t, instrument i, leg l, instrument ui, temp_InstrumentAlias ia, instrument c, trading_fee_payment tfee, levy_payment levy, stamp_payment stamp,
                    party cpty, commission_payment comm
                where /*tt.Product_Strategy = 'SP_TRS'
                and*/ tt.trdnbr = t.trdnbr
                and t.insaddr = i.insaddr
                and i.insaddr = l.insaddr
                and l.type = 'Total Return'
                and l.float_rate = ui.insaddr
                and i.curr = c.insaddr
                and t.counterparty_ptynbr = cpty.ptynbr
                and ui.insaddr *= ia.insaddr
                and t.trdnbr *= tfee.trdnbr
                and t.trdnbr *= levy.trdnbr
                and t.trdnbr *= stamp.trdnbr
                and t.trdnbr *= comm.trdnbr
                """
                
    return strSql
 
def genDeltaOneMgtRpt(asofdate, pf_list, acq_list, pty_list, base_ccy, currclspricemkt, histclspricemkt, title, subject, saveToFile, saveToCSV, sendEmail, emailList, fileName, fileperpty, period):
    report = ReportLayout(title)
    font = HTI_ExcelReport2.Font()
    font.bold = True    
    reportData = HTI_ExcelReport2.ReportData()
    reportData.newSheet = True
    
    #reportData.headerText = ['Trade Date', 'TRS Contracts No.', 'BBG Code', 'Underlying Instru.', 'Currency', 'Buy / Sell', 'Exe. Qty', 'Avg. Exe. Price', 'Trading Fee',
    #                        'Lev', 'Stamp', 'Exe. Notional', 'Commission', 'Commission Currency', 
    #                        'Total Settlement', 'Currency', 'FX', 'Client Code', 'Short Portfolio', 
    #                        'Buy Back', 'Trd Long Pfr IA%', 'Trd Short Pfr IA%', 'Trade No.', 'Value Day', 'Acquire Day']
    
    reportData.headerText = ['Trade Date', 'Client Code', 'TRS Contracts No.', 'BBG Code', 'Underlying Instru.', 'Currency', 'Buy / Sell', 'Exe. Qty', 'Avg. Exe. Price', 'Trading Fee',
                            'Lev', 'Stamp', 'Exe. Notional', 'Commission', 'Commission Currency', 
                            'Total Settlement', 'Currency', 'FX', 'Short Portfolio', 
                            'Buy Back', 'Trd Long Pfr IA%', 'Trd Short Pfr IA%', 'Trade No.', 'Value Day', 'Acquire Day']
    '''
    strSql = """select t.trdnbr, add_info(t, 'Product_Strategy') 'Product_Strategy', add_info(t, 'MSS_Account') 'MSS_Account',
                add_info(t, 'Short Portfolio') 'Short_Portfolio', 
                add_info(t, 'Buy Back') 'Buy_Back', 
                add_info(t, 'Trd Long Pfr IA%') 'Trd_Long_Pfr_IA',
                add_info(t, 'Trd Short Pfr IA%') 'Trd_Short_Pfr_IA'
                into temp_trade
                from trade t, instrument i, portfolio pf, party a, party cpty
                where t.time < '@dt'
                and t.insaddr = i.insaddr
                and t.status not in ('Void', 'Simulated')
                and i.instype = 'TotalReturnSwap'
                and t.prfnbr = pf.prfnbr
                and pf.prfid in (@portfolio_list)
                and t.acquirer_ptynbr = a.ptynbr
                and a.ptyid in (@accquirer_list)
                and t.counterparty_ptynbr = cpty.ptynbr
                @counterparty_list_sql

                select a.insaddr, a.alias
                into temp_InstrumentAlias
                from InstrAliasType t, InstrumentAlias a
                where t.alias_type_name = 'BB_TICKER'
                and t.oid = a.type

                select p.trdnbr, i.insid 'curr_insid', p.amount
                into trading_fee_payment
                from payment p, instrument i
                where p.type = 'Trading Fee'
                    and p.curr = i.insaddr

                select p.trdnbr, i.insid 'curr_insid', p.amount
                into levy_payment
                from payment p, instrument i
                where p.type = 'Transaction Levy'
                    and p.curr = i.insaddr
                
                select p.trdnbr, i.insid 'curr_insid', p.amount
                into commission_payment
                from payment p, instrument i
                where p.type = 'Commission'
                    and p.curr = i.insaddr

                select p.trdnbr, i.insid 'curr_insid', p.amount
                into stamp_payment
                from payment p, instrument i
                where p.type = 'Stamp Duty'
                    and p.curr = i.insaddr

                select t.time, add_info(t, 'External Reference') 'External Reference', ia.alias, ui.instype, c.insid 'ccy',
                    t.quantity >= 0 ? 'B' : 'S' 'bs', -t.quantity 'exe_qty', t.price 'avg_exe_price', tfee.amount 'tfee_amount', levy.amount 'levy_amount', stamp.amount 'stamp_amount',
                    -t.quantity * t.price 'exe_notional', comm.amount 'commission', comm.curr_insid 'comm_ccy', 
                    0.00 'ttl_settle_amt', 'HKD' 'base_ccy', 0.00 'fx_rate', cpty.ptyid 'client_code', t.trdnbr,
                    tt.Short_Portfolio, 
                    tt.Buy_Back, 
                    tt.Trd_Long_Pfr_IA,
                    tt.Trd_Short_Pfr_IA,
                    t.trdnbr, tfee.curr_insid 'tfee_ccy', levy.curr_insid 'levy_ccy', stamp.curr_insid 'stamp_ccy', t.value_day, t.acquire_day
                from temp_trade tt, trade t, instrument i, leg l, instrument ui, temp_InstrumentAlias ia, instrument c, trading_fee_payment tfee, levy_payment levy, stamp_payment stamp,
                    party cpty, commission_payment comm
                where /*tt.Product_Strategy = 'SP_TRS'
                and*/ tt.trdnbr = t.trdnbr
                and t.insaddr = i.insaddr
                and i.insaddr = l.insaddr
                and l.type = 'Total Return'
                and l.float_rate = ui.insaddr
                and i.curr = c.insaddr
                and t.counterparty_ptynbr = cpty.ptynbr
                and ui.insaddr *= ia.insaddr
                and t.trdnbr *= tfee.trdnbr
                and t.trdnbr *= levy.trdnbr
                and t.trdnbr *= stamp.trdnbr
                and t.trdnbr *= comm.trdnbr
                """
    '''
    
    strSql = getReportSql()
    select_fields = """
                    select t.time, cpty.ptyid 'client_code', add_info(t, 'External Reference') 'External Reference', ia.alias, ui.instype, c.insid 'ccy',
                    t.quantity >= 0 ? 'B' : 'S' 'bs', -t.quantity 'exe_qty', t.price 'avg_exe_price', tfee.amount 'tfee_amount', levy.amount 'levy_amount', stamp.amount 'stamp_amount',
                    -t.quantity * t.price 'exe_notional', comm.amount 'commission', comm.curr_insid 'comm_ccy', 
                    0.00 'ttl_settle_amt', 'HKD' 'base_ccy', 0.00 'fx_rate',  t.trdnbr,
                    tt.Short_Portfolio, 
                    tt.Buy_Back, 
                    tt.Trd_Long_Pfr_IA,
                    tt.Trd_Short_Pfr_IA,
                    t.trdnbr, tfee.curr_insid 'tfee_ccy', levy.curr_insid 'levy_ccy', stamp.curr_insid 'stamp_ccy', t.value_day, t.acquire_day
                    """
    strSql = strSql.replace("@select_fields", select_fields)
                
    strSql = strSql.replace('@dt',  asofdate.add_days(1).to_string('%Y-%m-%d'))
    strSql = strSql.replace('@portfolio_list', pf_list)
    strSql = strSql.replace('@accquirer_list', acq_list)
    if pty_list != '':
        counterparty_list_sql = 'and cpty.ptyid in (@counterparty_list)'
        counterparty_list_sql = counterparty_list_sql.replace("@counterparty_list", pty_list)
        strSql = strSql.replace("@counterparty_list_sql", counterparty_list_sql)
    else:
        strSql = strSql.replace("@counterparty_list_sql", ' ')
        
    if period == 'MTD':
        fm_date = asofdate.first_day_of_month()
        strSql = strSql.replace("@start_date", "and t.time >= '%s'" % (ael.date(fm_date).to_string('%Y-%m-%d')))
    else:
        strSql = strSql.replace("@start_date", ' ')
        
    print strSql    
    
 
    rs = ael.asql(strSql)                 
    columns, buf = rs
    rptContent = []
    for table in buf:     
        for row in table:
            trdnbr = row[Trade_No]
            
            print trdnbr
            ael_trd = ael.Trade[trdnbr]
            acm_trd = acm.FTrade[trdnbr]
            
            if ael_trd != None:
                ael_trd_dt = ael.date_from_time(ael_trd.time)
                trade_dt = ael_trd_dt.to_string('%d') + '-' + monthCode(int(ael_trd_dt.to_string('%m'))) + '-' + ael_trd_dt.to_string('%y')
                trs_contracts_no = row[TRS_Contracts_No]
                trs_contracts_no = row[TRS_Contracts_No]
                bbg_code = row[BBG_Code]
                underlying_type = row[Underlying_Type]
                ccy = row[Currency]
                buy_sell = row[Buy_Sell]
                if fileperpty:
                    if buy_sell == 'B':
                        buy_sell = 'S'
                    elif buy_sell == 'S':
                        buy_sell = 'B'
                exe_qty = row[Exe_Qty]
                avg_exe_price = row[Avg_Exe_Price]
                trading_fee = row[Trading_Fee]
                levy = row[Levy]
                stamp = row[Stamp]
                                          
                print avg_exe_price, trading_fee, levy, stamp

                                
                                          
                
                if row[Currency] == 'JPY':
                    exe_notional = math.trunc(row[Exe_Notional])
                else:
                    exe_notional = row[Exe_Notional]
  
                commission = row[Commission]
                #print 'louis', commission
                comm_ccy = row[Commission_Currency]
                ttl_settlement = row[Total_Settlement]
                base_ccy = row[Base_Currency]
                
                
                
                print 'second',commission, comm_ccy, ttl_settlement, base_ccy
                
                
                
                
                if acm_trd.AdditionalInfo().FX_Rate() == None:
                    fx_rate = getFx(asofdate, ccy, base_ccy, currclspricemkt, histclspricemkt)    
                else:
                    fx_rate = float(acm_trd.AdditionalInfo().FX_Rate())
       
                client_code = row[Client_Code]
                short_portfolio = row[Short_Portfolio]
                buy_back = row[Buy_Back]
                trd_long_pfr_ia = row[Trd_Long_Pfr_IA]
                if trd_long_pfr_ia == '':
                    trd_long_pfr_ia = acm_trd.Counterparty().AdditionalInfo().Long_Pfr_IA__37()
                
                trd_short_pfr_ia = row[Trd_Short_Pfr_IA]
                if trd_short_pfr_ia == '':
                    trd_short_pfr_ia = acm_trd.Counterparty().AdditionalInfo().Short_Pfr_IA__37()
                    
                trdnbr = row[TrdNo]
                
                tfee_ccy = row[Trading_Fee_Currency]
                if tfee_ccy != ccy:
                    tfee_fx = getFx(asofdate, tfee_ccy, ccy, currclspricemkt, histclspricemkt) 
                else:
                    tfee_fx = 1.0
                
                tran_levy_ccy = row[Transaction_Levy_Currency]
                if tran_levy_ccy != ccy:
                    tran_levy_fx = getFx(asofdate, ccy, base_ccy, currclspricemkt, histclspricemkt) 
                else:
                    tran_levy_fx = 1.0
                    
                stamp_ccy = row[Stamp_Currency]
                if stamp_ccy != ccy:
                    stamp_fx = getFx(asofdate, stamp_ccy, ccy, currclspricemkt, histclspricemkt) 
                else:
                    stamp_fx = 1.0
                    
                if comm_ccy != ccy:
                    comm_fx = fx_rate 
                else:
                    comm_fx = 1.0
                
                if levy == '':
                    levy_ins_ccy = 0.0
                else:
                    levy_ins_ccy = levy / tran_levy_fx
                    
                if trading_fee == '':
                    trading_fee_ins_ccy = 0.0
                    trading_fee = 0.0
                else:
                    print 'trading_fee..............................................................................', trading_fee, tfee_fx
                    trading_fee_ins_ccy = trading_fee / tfee_fx
                    
                if stamp == '':
                    stamp_ins_ccy = 0.0
                    stamp = 0.0
                else:
                    stamp_ins_ccy = stamp / stamp_fx
                    
                if commission == '':
                    commission_ins_ccy = 0.0
                    commission = 0.0
                else:
                    commission_ins_ccy = commission / comm_fx
                    
                ttl_settlement = round(exe_notional + levy_ins_ccy + trading_fee_ins_ccy + stamp_ins_ccy + commission_ins_ccy, 2)


                print 'three', ttl_settlement


                print 'row[Value_Day]', row[Value_Day]

		#value_day = row[Value_Day].to_string('%d') + '-' + monthCode(int(ael_trd_dt.to_string('%m'))) + '-' + ael_trd_dt.to_string('%y')
                value_day = row[Value_Day].to_string('%d') + '-' + monthCode(int(row[Value_Day].to_string('%m'))) + '-' + row[Value_Day].to_string('%y')
                
                #acquire_day = row[Acquire_Day].to_string('%d') + '-' + monthCode(int(ael_trd_dt.to_string('%m'))) + '-' + ael_trd_dt.to_string('%y')
                acquire_day = row[Acquire_Day].to_string('%d') + '-' + monthCode(int(row[Acquire_Day].to_string('%m'))) + '-' + row[Acquire_Day].to_string('%y')


		'''
                rptRow = [str(trade_dt), trs_contracts_no, bbg_code, underlying_type, ccy, buy_sell, str(exe_qty), str(avg_exe_price), 
                        str(trading_fee), str(levy), str(stamp), str(exe_notional), str(hti_comm_percent), str(comm_cost_percent),
                        str(comm_cost), str(comm_income_ccy), str(comm_income_base_ccy), str(ttl_settlement), base_ccy, str(fx_rate),
                        str(edd_income), str(pwm_income), str(ced_income), mss_acc_ccy, client_code, short_portfolio, buy_back, 
                        trd_long_pfr_ia, trd_short_pfr_ia, str(trdnbr)]
                '''
                
                
                
                rptRow = [str(trade_dt), client_code, trs_contracts_no, bbg_code, underlying_type, ccy, buy_sell, str(exe_qty), str(avg_exe_price), 
                        str(trading_fee), str(levy), str(stamp), str(exe_notional), str(commission), str(comm_ccy), str(ttl_settlement), base_ccy, str(fx_rate),
                        short_portfolio, buy_back, 
                        trd_long_pfr_ia, trd_short_pfr_ia, str(trdnbr), value_day, acquire_day]
                
                
        
                rptContent.append(rptRow)

    rptContent.sort(report_compare)
    
    reportData.rows = rptContent

    report.addReportData(reportData, {'SUM': [], 'COL_TEXT': [], 'CUSTOM_TEXT': {'COL': [], 'TEXT': []}})
    
    if saveToFile:
        if not saveToCSV:
            try:
                fileName = fileName + '.xlsx'
                if os.path.exists(fileName):
                    os.remove(fileName)
            except:
                pass            
            if sendEmail and len(emailList) != 0:
                report.saveNoQuit(fileName)
            else:
                print fileName
                report.save(fileName)
        else:
            fileName = fileName + '.csv'                
            if os.path.isfile(fileName):
                os.remove(fileName)                
            csvData = []
            csvData.append(reportData.headerText)
            csvData = csvData + reportData.rows                
            print fileName
            try:                        
                outPutFile = open(fileName,'wb')                     
                csvWriter = csv.writer(outPutFile, delimiter=',', quotechar='"')                     
                for row in csvData:       
                    csvWriter.writerow(row)        
                outPutFile.flush()                    
            finally:
                outPutFile.close()
    else:
        report.show()

    
class ReportLayout(HTI_ExcelReport2.CommonLayoutReport):
    title = ''
    
    Trade_Date = 1
    Client_Code = 2
    TRS_Contracts_No = 3
    BBG_Code = 4
    Underlying_Type = 5
    Currency = 6
    Buy_Sell = 7
    Exe_Qty = 8
    Avg_Exe_Price = 9
    Trading_Fee = 10
    Levy = 11
    Stamp = 12
    Exe_Notional = 13

     


    Commission = 14
    Commission_Currency = 15
    Total_Settlement = 16
    Base_Currency = 17
    FX_Rate = 18

    Trade_No = 19 
    Short_Portfolio = 20 
    Buy_Back = 21 
    Trd_Long_Pfr_IA = 22 
    Trd_Short_Pfr_IA = 23 
    TrdNo = 24 
    Trading_Fee_Currency = 25 
    Transaction_Levy_Currency = 26 
    Stamp_Currency = 27  
    Value_Day = 28 
    Acquire_Day = 29 



        
    def __init__(self, title):
        self.title = title
        HTI_ExcelReport2.CommonLayoutReport.__init__(self)
                    
    def reportHeader(self, currentRow, reportIndex, excelApp):
        # Write title        
        excelApp.Cells(currentRow[self.ROW], 1).Value = self.title
        excelApp.Cells(currentRow[self.ROW], 1).Font.Bold = True
        excelApp.Cells(currentRow[self.ROW], 1).Font.Size = 12
        
        
        print '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!',self.Avg_Exe_Price
        
        currentRow[self.ROW] = currentRow[self.ROW] + 1
        HTI_ExcelReport2.CommonLayoutReport.reportHeader(self, currentRow, reportIndex, excelApp)      
        excelApp.Columns(self.Exe_Qty).NumberFormat = "#,##0"
        excelApp.Columns(self.Avg_Exe_Price).NumberFormat = "#,##0.0000"
        excelApp.Columns(self.Trading_Fee).NumberFormat = "#,##0.00"
        excelApp.Columns(self.Levy).NumberFormat = "#,##0.00"
        excelApp.Columns(self.Stamp).NumberFormat = "#,##0.00"
        excelApp.Columns(self.Exe_Notional).NumberFormat = "#,##0.00"
        excelApp.Columns(self.Commission).NumberFormat = "#,##0.00"        
      
        excelApp.Columns(self.Total_Settlement).NumberFormat = "#,##0.00"
        excelApp.Columns(self.FX_Rate).NumberFormat = "#,##0.000000"
       
        
        
    def groupFooter(self, currentRow, reportIndex, group, excelApp):   
        HTI_ExcelReport2.CommonLayoutReport.groupFooter(self, currentRow, reportIndex, group, excelApp)
     
            
        
    def reportEnd(self, excelApp):       
        
        HTI_ExcelReport2.CommonLayoutReport.reportEnd(self, excelApp)
        
        excelApp.Columns(self.Trade_Date).Select()
        excelApp.Selection.HorizontalAlignment = HTI_ExcelReport2.ExcelConstant.xlLeft
        
        excelApp.Cells(1, 1).Select()
        
        