library(RPostgreSQL)
options(stringsAsFactors=FALSE)
drv <- dbDriver("PostgreSQL")
dbCon <- dbConnect(drv, dbname="ET", user="et_user", password="Serenitas;1", host="192.168.1.108")
prices <- dbGetQuery(dbCon, "select * from latest_markit_prices")
collat <- dbGetQuery(dbCon, "select * from et_collateral")
userLoanxid <- dbGetQuery(dbCon, "select * from loanx_user_mapping")
userCUSIP <- dbGetQuery(dbCon, "select * from cusip_user_mapping")
bbgmap <- read.csv(file="Z:/CorpCDOs/data/lx-bbgmap.csv", header = TRUE,colClasses = "character")
RMBSmap <- read.csv(file="Z:/CorpCDOs/data/MTGECusips.csv", header = FALSE,colClasses = "character")

prices$mid <-(prices$bid + prices$offer)/2
prices$spread <- prices$spread/100

from <- c(' LLC.?$', ', INC.?$', ' Inc.?$',' GMBH.?$', ' SA$', ' LTD$', ' PLC$',
          ' CORP$', ' S A.?$', ' S.A.?$', ' BV$', ' AB$', ' LP$', ' N.V.?$',
          ' AG$', ' CO.?$', ' HLDG.?$', ' HLDGS?$', ' S P A$', ' S.P.A.?$', ' A/S$', ' Holdings$'
          ' B.V.$', '(', ')')
prices$scrubedissuer <- prices$issuer
collat$scrubedissuer <- collat$issuername
for (i in 1:length(from)){
    prices$scrubedissuer <- gsub(from[i], '', prices$scrubedissuer, ignore.case = TRUE)
    collat$scrubedissuer <- gsub(from[i], '', collat$scrubedissuer, ignore.case = TRUE)
}

#collat1 <- collat
collat1 <- collat[collat$dealname == "abrlf",]
#collat1$src[!is.na(collat1$price)] <- "IDMatch"
#collat1$src[collat1$cusip %in% RMBSmap$V1] <- "RMBS"
#collat1$iscdo[collat1$cusip %in% CLOmap$cusip] <- TRUE

for (i in 1:length(collat1$issuername)){
    #Match CUSIP vs. LoanxID
    matched0 <- grep(collat1$cusip[i], bbgmap$cusip, ignore.case=TRUE)
    if (is.na(collat1$loanxid[i])&!is.na(collat1$cusip[i])&length(matched0)==1) {
        collat1$loanxid[i] <- bbgmap$loanxid[matched0]
        matched0a <- grep(collat1$loanxid[i], prices$loanxid, ignore.case=TRUE)
        if(length(matched0a)==1) collat1$price[i] <- prices$mid[matched0a]
        collat1$src[i]<- "CUSIPMapped"
    }
    if (is.na(collat1$loanxid[i])){
        collat1$et_loanxid[i] <- userLoanxid$loanxid[collat1$scrubedissuer[i] == userLoanxid$issuername & collat1$maturity[i] == userLoanxid$maturity & collat1$spread[i] == userLoanxid$spread]
        collat1$src[i]<- "UserMapped"
    }
    if (is.na(collat1$loanxid[i])&is.na(collat1$et_loanxid[i])) {
        #matchN1 <- grep(collat1$scrubedissuer[i], prices$scrubedissuer, ignore.case=TRUE)
        #matchN1 <- prices$scrubedissuer %in% matchN1
        maxchange <- as.integer(nchar(collat1$scrubedissuer[i])*.25)#25% of string length
        z <- data.frame(x=as.numeric(adist(collat1$scrubedissuer[i], prices$scrubedissuer, ignore.case = TRUE)),y=prices$scrubedissuer)
        z <- z[order(z$x),]
        z <- z[z$x<=maxchange,]$y
        matchN1 <- prices$scrubedissuer %in% z

        matchS1 <- prices$spread == collat1$spread[i]  #Match Spread
        matchS2 <- abs(prices$spread - collat1$spread[i]) <=.5 #Match Spread+/-50bps
        matchS3 <- abs(prices$spread - collat1$spread[i]) <=1 #Match Spread+/-100bps

        matchD1 <- abs(prices$maturity - collat1$maturity[i]) <=2  #Match Maturity 2 day diff
        matchD2 <- abs(prices$maturity - collat1$maturity[i]) <=30 #Match Maturity <=30 days diff
        matchD3 <- abs(prices$maturity - collat1$maturity[i]) <=200 #Match Maturity <=200 days diff

        CompMatchV1NA <- matchN1 & matchS1 & matchD1
        CompMatchV2NA <- matchN1 & matchS2 & matchD1
        CompMatchV3NA <- matchN1 & matchS2 & matchD2
        CompMatchV4NA <- matchN1 & matchS3 & matchD1
        CompMatchV5NA <- matchN1 & matchS1 & matchD3
        CompMatchV6NA <- matchN1 & matchS1 & matchD3

        CompMatchV1NA[is.na(CompMatchV1NA)] <- FALSE
        CompMatchV2NA[is.na(CompMatchV2NA)] <- FALSE
        CompMatchV3NA[is.na(CompMatchV3NA)] <- FALSE
        CompMatchV4NA[is.na(CompMatchV4NA)] <- FALSE
        CompMatchV5NA[is.na(CompMatchV5NA)] <- FALSE

        CompMatchV1 <- CompMatchV1NA
        CompMatchV2 <- CompMatchV2NA
        CompMatchV3 <- CompMatchV3NA
        CompMatchV4 <- CompMatchV4NA
        CompMatchV5 <- CompMatchV5NA

        updatetable <- FALSE

            #Matches Found, Randomly picks the first one
        if (any(CompMatchV1)){
            collat1$et_loanxid[i] <- prices$loanxid[which(CompMatchV1)][1]
            updatetable <- TRUE
            collat1$price[i] <- prices$mid[which(CompMatchV1)][1]
            collat1$src[i]<- "MatchedNameSpreadDate"
        } else if (any(CompMatchV2)) {
            collat1$et_loanxid[i] <- prices$loanxid[which(CompMatchV2)][1]
            updatetable <- TRUE
            collat1$price[i] <- prices$mid[which(CompMatchV2)][1]
            collat1$src[i]<- "MatchedNameSpread+-50bpsDate"
        } else if (any(CompMatchV3)) {
            collat1$et_loanxid[i] <- prices$loanxid[which(CompMatchV3)][1]
            updatetable <- TRUE
            collat1$price[i] <- prices$mid[which(CompMatchV3)][1]
            collat1$src[i]<- "MatchedNameSpread+-50bpsDate+-30Days"
        } else if (any(CompMatchV4)) {
            collat1$et_loanxid[i] <- prices$loanxid[which(CompMatchV4)]
            updatetable <- TRUE
            collat1$price[i] <- prices$mid[which(CompMatchV4)][1]
            collat1$src[i]<- "MatchedNameSpread+-100bpsDate"
        } else if (any(CompMatchV5)) {
            collat1$et_loanxid[i] <- prices$loanxid[which(CompMatchV5)]
            updatetable <- TRUE
            collat1$price[i] <- prices$mid[which(CompMatchV5)][1]
            collat1$src[i]<- "MatchedNameSpreadDate+-200Days"
        }
        if (updatetable == true) {
            str <- paste0("a.dealname = '", collat1$dealname[i], "' and a.name = '", collat1$name[i], "' and a.maturity = '", collat1$maturity[i], "'")
            dbGetQuery(dbCon, paste0("UPDATE et_collateral a SET et_loanxid = '", collat1$et_loanxid[i],"' WHERE ",str))
        }
    }
}

#collat1$loanxid[collat1$cusip[i] %in% bbgmap$cusip]
#rm(collat)
#gc()



#write.csv(collat, file="Z:/edwin/collat.csv")
write.csv(collat1, file="Z:/edwin/collat1.csv")
write.csv(prices, file="Z:/edwin/prices.csv")

#scrubed <- prices[,c("scrubedissuer", "issuer")]
#scrubed1 <- collat[,c("scrubedissuer", "issuername")]
#write.csv(scrubed, file="Z:/edwin/scrubedissusername.csv")
#write.table(scrubed,"clipboard", sep = "\t")

#pD1 <- merge(collat, prices, by.x=c("loanxid"), by.y=c("loanxid"))
#pD1$src <- "LXID"
pD2 <- merge(pD1, prices, by.x=c("scrubedissuer.x", "spread.x","maturity.x"),
             by.y=c("scrubedissuer", "spread","maturity"))
pD2$src <- "ExactMatch"

pD3[1:50,c("issuername", "loanxid.y", "loanxid.x")]
## pD2 <- pD1[c("issuername","loanxid","mid")]