1 files changed, 6 insertions, 47 deletions
diff --git a/R/serenitasdb.R b/R/serenitasdb.R
index 16b3d764..807bb57f 100644
--- a/R/serenitasdb.R
+++ b/R/serenitasdb.R
@@ -79,53 +79,12 @@ indexsplit <- function(indexname){
 
 get.tranchequotes <- function(indexname, tenor='5yr', date=Sys.Date()){
     ## first try the easy way:
-    sqlstr <- paste("select * from tranche_quotes",
-                    "where index=$1 and series=$2 and quotedate::date=$3",
-                    "and tenor = $4 order by attach asc")
-    temp <- indexsplit(indexname)
-    indextype <- temp$indextype
-    series <- temp$series
-    r <- dbGetQuery(serenitasdb, sqlstr,
-                    params = list(indextype, series, date, tenor))
-    ## check if set is unique and complete
-    ## hy9 and hy10 tranche is gone
-    if(tolower(indexname) %in% c("hy9", "hy10")){
-        lower.attach <- 10
-    }else{
-        lower.attach <- 0
-    }
-    if(length(unique(r$attach))==length(r$attach) && all(c(r$attach, 100)==c(lower.attach, r$detach))){
-        return(r)
-    }
-    ##else we work harder
-    ##we get the list of distinct quotes
-    sqlstr <- paste("select distinct quotesource, quotedate from tranche_quotes",
-                    "where index=$1 and series=$2 and quotedate::date=$3",
-                    "and tenor = $4 order by quotedate desc")
-    distinct.quotes <- dbGetQuery(serenitasdb, sqlstr,
-                                  params = list(indextype, series, date, tenor))
-    flag <- FALSE
-    ##we loop through the disctinct quotes until we find a complete set
-    if(nrow(distinct.quotes)==0){
-        return(NULL)
-    }
-    for(i in 1:nrow(distinct.quotes)){
-        sqlstr <- str_c("select * from tranche_quotes where index=$1 ",
-                        "and series=$2 and tenor = $3 and quotedate=$4 ",
-                        if(indextype == 'HY' && series>=15) "and detach-attach!=5 " else NULL,
-                        "and quotesource=$5 order by attach asc")
-        r <- dbGetQuery(serenitasdb, sqlstr,
-                        params = list(indextype, series, tenor,
-                            distinct.quotes$quotedate[i],
-                            distinct.quotes$quotesource[i]))
-        if(all(c(r$attach, 100)==c(lower.attach, r$detach))){#set is complete
-            flag <- TRUE
-            break
-        }
-    }
-    if(!flag){
-        return(NULL)
-    }
+    sqlstr <- "SELECT * from get_tranche_quotes($1, $2, $3, $4)"
+    r <- with(indexsplit(indexname),
+              suppressWarnings(
+                  dbGetQuery(serenitasdb, sqlstr, params = list(indextype, series, tenor, date))
+              )
+              )
     return( r )
 }