import os
import os.path
import psycopg2
import pdb
import pandas as pd


if os.name =='nt':
    root = "//WDsentinel/share/CorpCDOs"
elif os.name == 'posix':
    root = '/home/share/CorpCDOs'

conn = psycopg2.connect(database="ET",
                        user="et_user",
                        password="Serenitas1",
                        host="192.168.1.108")
cursor = conn.cursor()

def cusips_from_dealname(dealname, curr = cursor):
    curr.execute("SELECT \"Deal Cusip List\" FROM latest_clo_universe "
                 " WHERE dealname = %s", (dealname,))
    return curr.fetchone()[0]

def sanitize_float(string):
    if isinstance(string, float):
        return string
    else:
        string = string.replace(",","")
        if "(" in string:
            return - float(string[1:-1])
        else:
            return float(string)

fields = ["Price", "WAL", "Market Value", "Modified Duration"]
dealdata = {}
workdate = '2013-01-09'
for dealname in ["abcl071", "ammcclo5"]:
    tranches = os.listdir(os.path.join(root, "Scenarios", "Prices_" + workdate, dealname))
    d  = {}
    for tranche in tranches:
        data = pd.read_table(os.path.join(root, "Scenarios", "Prices_" + workdate, dealname, tranche))
        datamod = data[data.columns[2:-1]].T
        datamod.columns = data[data.columns[1]]
        for field in fields:
            datamod[field] = datamod[field].apply(sanitize_float)
        d[tranche[:-7]] = datamod[fields]
    dealdata[dealname] = pd.concat(d)

dealdata = pd.concat(dealdata)

cursor.close()
conn.close()