import acm
import ael
import FHTI_EDD_OTC_Util
import HTI_ExcelReport2
import HTI_Util
import HTI_FeedTrade_EDD_Util
import os
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."

Client_Code = 0
Client_Name = 1
Trade_Reference = 2
Trade_Date = 3
Type = 4
Currency = 5
Amount = 6
EXT_REF = 7

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
    
def cb4(index, fieldValues):
    global ael_variables        
    if ael_variables[index][0] == 'saveToNetwork':
        disable_variables(('networkDriveLocation',), 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\\SecurityLoan_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 Security Loan 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, 'Cash Entry 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']
    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:
        genCashEntryRpt(asofdate, pf_list, acq_list, pty_list, base_ccy, currclspricemkt, histclspricemkt, title, subject, saveToFile, saveToCSV, sendEmail, emailList, fileName, fileperpty, period)
    else:
        strSql = getReportSql()
        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", ' ')
  
        strSql = "select distinct cpty.ptyid " + strSql
        
        print strSql
        
        
        rs = ael.asql(strSql)                 
        columns, buf = rs
        rptContent = []
        for table in buf:     
            for row in table:
                ptyid = row[Client_Code]
                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
                
                genCashEntryRpt(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
                    #print 'destination_fileName', destination_fileName
                    destination_fileName = destination_fileName.replace('[ptyid]', ptyid)
                    #print 'destination_fileName', destination_fileName
                    dir_path = os.path.dirname(os.path.realpath(destination_fileName))
                    #print 'dir_path', dir_path
                    if os.path.exists(dir_path) == False:
                        #print 'dir_path exist', dir_path
                        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 = """from trade t, instrument i, payment p, party cpty, party a, portfolio pf, instrument c
                where t.insaddr = i.insaddr
                and i.instype = 'Curr'
                and t.type = 'Cash Entry'
                and t.trdnbr = p.trdnbr
                and t.counterparty_ptynbr = cpty.ptynbr
                and t.acquirer_ptynbr = a.ptynbr
                and p.curr = c.insaddr
                and a.ptyid in (@accquirer_list)                    
                @counterparty_list_sql
                and t.prfnbr = pf.prfnbr
                and pf.prfid in (@portfolio_list)
                @start_date
                and t.time < '@dt'
                and t.status not in ('Void', 'Simulated')
            """
    print '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!', strSql
    return strSql
 
def genCashEntryRpt(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', 'Client Code', 'Client Name', 'Trade Reference', 'Type', 'Currency', 'Amount', 'External Reference']


    strSql = getReportSql()
    strSql = "select cpty.ptyid, cpty.fullname, t.trdnbr, t.time, p.type, c.insid, p.amount, add_info(t, 'External Reference') 'ext_ref' " + strSql
    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:
            
            client_code = row[Client_Code]
            client_name = row[Client_Name]
            trade_ref = row[Trade_Reference]
            type = row[Type]
            currency = row[Currency]            
            amt = row[Amount]
            ext_ref = row[EXT_REF]
                
            acm_trd = acm.FTrade[trade_ref]
            if acm_trd != None:
                print acm_trd
                trade_date = acm.Time.DateFromTime(acm_trd.TradeTime())
            
            rptRow = [str(trade_date), client_code, client_name, str(trade_ref), type, currency, str(amt), ext_ref]
                    
            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:
                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 = ''
    Amount = 7
        
    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
        
        currentRow[self.ROW] = currentRow[self.ROW] + 1
        HTI_ExcelReport2.CommonLayoutReport.reportHeader(self, currentRow, reportIndex, excelApp)      
        excelApp.Columns(self.Amount).NumberFormat = "#,##0.00"
        
        
    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()