viernes, 6 de julio de 2012

RSAP, Rook and ERP

As I wrote in my blog Analytics with SAP and R (Windows version) we can use RSAP to connect to our ERP system and play with the data. This time I wanted of course, to keep exploring the capabilities of RSAP, but using something else. As everybody knows, I love micro-frameworks, so for R that not an exception...gladly, Rook came to the rescue... Rook is a simple web server that will run locally and will allow us to do some really nice things...enough talk...let's go to the source code...


Start the Rserve server
library("RSAP")
require("Rook")
setwd("C:/Blag/R_Scripts")
 
conn = RSAPConnect("sap.yml")
parms <- list('DELIMITER' = ';',
                 'FIELDS' = list(FIELDNAME = list('CARRID', 'CARRNAME')),
                 'QUERY_TABLE' = 'SCARR')
res<-RSAPInvoke(conn, "RFC_READ_TABLE", parms)
scarr<-res$DATA
flds<-sub("\\s+$", "", res$FIELDS$FIELDNAME)
scarr<-data.frame(colsplit(scarr$WA,";", names=flds))
 
parms<-list('DELIMITER' = ';',
               'FIELDS' = list(FIELDNAME = list('CITYFROM')),
               'QUERY_TABLE' = 'SPFLI')
res<-RSAPInvoke(conn, "RFC_READ_TABLE", parms)
spfli<-res$DATA
flds<-sub("\\s+$", "", res$FIELDS$FIELDNAME)
spfli<-data.frame(colsplit(spfli$WA,";", names=flds))
spfli<-unique(spfli)
 
get_data<-function(p_carrid,p_cityfrom){
  parms<-list('DELIMITER' = ';',
                 'FIELDS' = list(FIELDNAME = list('CITYTO','FLTIME')),
                 'OPTIONS' = list(TEXT = list(p_carrid, p_cityfrom)),
                 'QUERY_TABLE' = 'SPFLI')
  res<-RSAPInvoke(conn, "RFC_READ_TABLE", parms)
  RSAPClose(conn)
  spfli<-res$DATA
  flds<-sub("\\s+$", "", res$FIELDS$FIELDNAME)
  if(length(spfli$WA)>0){
  spfli<-data.frame(colsplit(spfli$WA,";", names=flds))
  return(spfli)
  }else{
   return(spfli)
  }
}
 
newapp<-function(env){
  req<-Rook::Request$new(env)
  res<-Rook::Response$new()
  res$write('<form method="POST">\n')
  res$write('<div align="center"><table><tr>') 
  res$write('<td>Select a carrier: <select name=CARRID>')
  for(i in 1:length(scarr$CARRID)) {
    res$write(sprintf('<OPTION VALUE=%s>%s</OPTION>',
                      scarr$CARRID[i],scarr$CARRNAME[i]))
  }
  res$write('</select></td><td>')
  res$write('Select a city: <select name=CITYFROM>')
  for(i in 1:length(spfli$CITYFROM)) {
    res$write(sprintf('<OPTION VALUE=%s>%s
                       </OPTION>',spfli$CITYFROM[i],spfli$CITYFROM[i]))
  }
  res$write('</select></td>')
  res$write('<td><input type="submit" name="Get Flights"></td>')
  res$write('</tr></table></div>')
  res$write('</form>')
 
  if (!is.null(req$POST())) {
    p_carrid = req$POST()[["CARRID"]]
    p_cityfrom = req$POST()[["CITYFROM"]]
    flights_from<-paste('Distance in Flights from ',p_cityfrom,sep='')
 
    p_carrid<-paste('CARRID = \'',p_carrid,'\'',sep='')
    p_cityfrom<-paste('AND CITYFROM =\'',p_cityfrom,'\'',sep='')
 
    spfli<-get_data(p_carrid,p_cityfrom)
 
    if(length(spfli$CITYTO) > 0){
    png("Flights.png",width=800,height=500)
    plot(spfli$FLTIME,type="n",axes=FALSE,ann=FALSE)
    lines(spfli$FLTIME,col="blue")
    points(spfli$FLTIME, pch=21, bg="lightcyan", cex=1.25)
    box()
    xy<-length(spfli$CITYTO)
    axis(2, col.axis="blue", las=1)
    axis(1, at=1:xy, lab=spfli$CITYTO, col.axis="purple")
    title(main=flights_from, col.main="red", font.main=4)
    dev.off()
    res$write("<div align='center'>")
    res$write(paste("<img src='", server$full_url("pic"), "/", 
                    "Flights.png'", "/>", sep = ""))
    res$write("</div>")
    }else{
      res$write("<p>No data to select...</p>")
    }
  }
  res$finish()
}
 
server = Rhttpd$new()
server$add(app = newapp, name = "Flights")
server$add(app = File$new("C:/Blag/R_Scripts"), name = "pic")
server$start()
server$browse("Flights")

This is the result...





As you can see, we're getting the data from SAP to fill both SELECT's and then call out the query. We generate a PNG graphic showing the distance from the City From to the City To and then call it from our Web Page to show it on the screen.

As you can see, RSAP give us a lot of opportunities that we can take advantage by simply putting some effort and imagination. Hope this boots your R interest -;)

Greetings,

Blag.