library(RODBC)
conn <- odbcConnect("MLP-PROD")
deltas.hist <- sqlQuery(conn,"select * from ET_CusipDeltasHist where IndexType='LCDX' and ReinvSpreadScenario='MID'")
deltas.live <- sqlQuery(conn,"select * from ET_CusipDeltas where IndexType='LCDX' and ReinvSpreadScenario='MID'")
deltas <- rbind(deltas.live,deltas.hist)
deltas$PriceDate <- as.Date(deltas$PriceDate,format="%m/%d/%Y")
deltas <- deltas[order(deltas$PriceDate,decreasing=T),]
cusiplist <- unique(deltas$Cusip)
deltas <- deltas[match(cusiplist,deltas$Cusip),]

walduration <- sqlQuery(conn,"select * from ET_CusipSumProduct_WALDuration")
prices_lcdx <- sqlQuery(conn,"select * from ET_CusipSumProduct_AWPrice where IndexType='LCDX'")
prices_t1 <- sqlQuery(conn,"select * from ET_CusipSumProduct_AWPrice where IndexType='T1'")
prices_t2 <- sqlQuery(conn,"select * from ET_CusipSumProduct_AWPrice where IndexType='T2'")
prices_hy <- sqlQuery(conn,"select * from ET_CusipSumProduct_AWPrice where IndexType='HY' and IndexSeries='10' and IndexTenor='7y'")
odbcClose(conn)
selectlatest <- function(data){
  data$PriceDate <- as.Date(data$PriceDate,format="%m/%d/%Y")
  data <- data[order(data$PriceDate,data$UpdateDate,decreasing=T),]
  data_mid <- data[data$ReinvSpreadScenario=="MID",]
  data_mid <- data_mid[match(unique(data_mid$Cusip),data_mid$Cusip,),]
  data_high <- data[data$ReinvSpreadScenario=="HIGH",]
  data_high <- data_high[match(unique(data_high$Cusip),data_high$Cusip,),]
  data_low <- data[data$ReinvSpreadScenario=="LOW",]
  data_low <- data_low[match(unique(data_low$Cusip),data_low$Cusip,),]
  data_noreinv <- data[data$ReinvSpreadScenario=="NOREINV",]
  data_noreinv <- data_noreinv[match(unique(data_noreinv$Cusip),data_noreinv$Cusip,),]
  data <- rbind(data_mid,data_noreinv,data_low,data_high)
  data[order(data$Cusip),]
}

prices_t1 <- selectlatest(prices_t1)
prices_t2 <- selectlatest(prices_t2)
prices_hy <- selectlatest(prices_hy)
prices_lcdx <- selectlatest(prices_lcdx)
walduration <- selectlatest(walduration)
prices_lcdx <- prices_lcdx[prices_lcdx$Cusip%in%prices_t1$Cusip,]
prices_hy <- prices_hy[prices_hy$Cusip%in%prices_t1$Cusip,]
deltas <- deltas[deltas$Cusip%in%prices_t1$Cusip,]
walduration <- walduration[walduration$Cusip%in%prices_t1$Cusip,]
deltas <- deltas[pmatch(prices_t1$Cusip,deltas$Cusip,dup=T),]
data <- cbind(prices_lcdx[,c("Cusip","ReinvSpreadScenario","AWJuniorWgts_Price","AWSeniorWgts_Price")],prices_t1[,"AWJuniorWgts_Price"],prices_t2[,"AWJuniorWgts_Price"],prices_hy[,c("AWJuniorWgts_Price","AWSeniorWgts_Price")],walduration[,c("AW_WAL","AW_Duration","PriceDate")],deltas[,c("FullTranche","Tranche1","Tranche2","Tranche3","Tranche4")])
colnames(data)<-c("Cusip","ReinvScen","LCDX12 5y Jr","LCDX12 5y Sr","T1","T2","HY10 7y Jr","HY10 7y Sr","Wal","Duration","Price Date","LCDX index","LCDX 0-8","LCDX 8-15","LCDX 15-30","LCDX 30-100")
write.table(data,file="W:/CorpCDOs/latestprices_Prod.txt",row.names=F,col.names=T,sep=",")