2016-11-14

PHP at Bloomberg's - 3

This is not a Bloomberg Web Services tutorial. It is a post how I use PHP to connect to Bloomberg Web Services.

Part three, the PHP.  (This post.)
Part four, requesting data

In the previous post I described how i built a Data Warehouse app around a scheduled web service from Bloomberg, ‘daily currency rates’, fetch rates from Bloomberg and distribute reports via mail. The Data Warehouse is built on top of PHP, so it’s natural to build the Bloomberg web client in PHP. I do not have a ready made Soap client for the Data Warehouse, so I built one for Bloomberg.
First you must a have pem access certificate(see part one).
If we look at the integration tag language specification of the web  client:
In the job declaration the parameters needed are specified (with the exception of the passphrase of the certificate).  
  1. The link to the wsdl
  2. The responseId
  3. The certificate
You can also see the program this job invokes pgm=BloombergSoapCurrRate1.php, when this program gets control the parameters is in an array called $job. In the initialization we set up these parameters and others we also define some lambdas used in the program:

The start of  BloombergSoapCurrRate1.php, we take care of the ‘Soap parameters’ and bomb out if someone is missing. You have to replace all $log-> stuff with ‘echo’ or whatever. Now when we have the parms we can call Bloomberg:

I use PHP’s SoapClient, just add in my parameters, exception  and trace I just copied from some example. As you see I bomb out if the return status code is non zero. Now we take care of the response (which is rather complex), first I take care of the response headers:

I just create a more friendly PHP associative array of the headers, and create timestamps in clear text  of the mismash of Bloomberg timestamps in the response headers. I also create a timestamp in local time (which is CET for me) of the started timestamp, so we know when the currency rates were produced, ‘NewYork time’ is not natural for Europeans, especially when we are forced to schedule in ‘London Time’. With that done next thing take care of the currencies:

Now we are almost done, I like to save the result in two SQL tables headers and rates, so while I’m at it I create two SQL insert queries, one for the header and one for the rates:

As you see I save the sql statements in two files in the current working directory.
At last I use two helper lambda functions to create the SQL statements:

The PHP code in it’s entirety:

// OK Here we go...
//initialize set up lambdas, constants, parms etc.
$sanitize = function ($val){
// This skeleton lambda can be used for sanitizing SQL statements in lambda sqlit
   return $val;
};
$sqlit = function ($sqlarr) use($sanitize){
// This lambda creates the field and value clauses of an SQL insert from an array of assoc arrays 
  $fldstmt = ',('.implode(', ', array_keys($sqlarr[0])).')';
  foreach($sqlarr as $arr){
    $val= [];
    foreach($arr as $k => $v){
 $val[] = "'".$sanitize($v)."'";
    }
    $valstmt .= ',('.implode(', ', $val).')';
  }
  return [substr($fldstmt,1), substr($valstmt,1)];
};

$scriptName = $job['data'];
$scriptParm = $job['parm'];
$hdrtable = array_key_exists('HEADTAB',$schedule['_xlate']) ? $schedule['_xlate']['HEADTAB'] : 'HEADTAB';
$crtable = array_key_exists('RATETAB',$schedule['_xlate']) ? $schedule['_xlate']['RATETAB'] : 'RATETAB';

$parms = getParms([$job],['responseId'=>'M','wsdl'=>'M','certificate'=>'M','userid'=>'M']);
if (!$parms[0]) return FALSE;
extract($parms);

$usercred=getUserCredentials($context,'bloomberg.xml', "$userid");
var_dump($usercred);
if ($usercred == FALSE){
  $log->enter('Error',"Enter script=$scriptName Bloomberg user $userid credentials is missing"); 
  return FALSE;
}
$passphrase = $usercred['password'];

$BloombergResponse = 'BloombergResponse';
//$BloombergResponse = '/home/tooljn/dw/data/161108174334_getCurrencyRates/getRates/BloombergResponse';

$log->enter('Info',"Enter script=$scriptName responseId=$responseId,wsdl=$wsdl,certificate=$certificate");
$log->logit('Info',"Header table=$hdrtable, Rate table=$crtable");

// Rock'n'Roll
if (file_exists("$BloombergResponse")){
  $log->logit('Info',"We alreay have a response from Bloomberg($BloombergResponse)");
  if (!is_object($response = unserialize(file_get_contents("$BloombergResponse")))) {
    $log->logit('Error',"$BloombergResponse can not be unserialized"); 
    return FALSE;
  }
} else { // No response from Bloomberg
  $log->logit('Info',"Trying to contact Bloomberg web server");
  $params =['responseId' => "$responseId"];
  //$params =['responseId' => '1478085720-104624273'];
  //$Scheduledparams =['responseId' => '1478105035-72407552'];
   //$Scheduledparams =['responseId' => '1478861819-71753597'];
  try {
    $client = new SoapClient("$wsdl"
      , array('soap_version'   => SOAP_1_1
      , 'local_cert' => "$certificate"
      , 'passphrase' => "$passphrase"
      , 'cache_wsdl' => WSDL_CACHE_NONE
      , 'connection_timeout' => 15
      , 'exception' => TRUE
      , 'trace' => 1
    ));
    $response = $client->retrieveGetDataResponse($params);
  } catch (SoapFault $e){
    $log->logit('Error',"catching pretty bad errors, something went terribly wrong"); 
    $log->dumpit('Note',"Soap error",$e->getMessage());
    $log->dumpit('Note',"Soap error",$e);
    return FALSE;
  }
  $log->logit('Info',"Bloomberg responded");
  file_put_contents("$BloombergResponse",serialize($response)); // Save it for a rainy day
} // if (file_exists("$BloombergResponse"))

$stscd = $response->statusCode->code; 
if ($stscd) { 
    $log->logit('Error',"Invalid Bloomberg status code=$stscd"); 
    return FALSE;
}
$log->logit('Info',"Bloomberg status code=$stscd");
//var_dump($response);
//print "end dumping response\n";

//ok we got an answer first we deal with the headers
$rhdr = [];
$rhdr['stsCode'] = $response->statusCode->code;
$rhdr['stsDescr'] = $response->statusCode->description;
$rhdr['requestId'] = $response->requestId;
$rhdr['responseId'] = $response->responseId;
$rhdr['timestarted'] = $response->timestarted;
$rhdr['timefinished'] = $response->timefinished;

$thdr = (array) $response->headers;
$Stimestamp = substr($rhdr['timestarted'],0,19);
if (array_key_exists('time',$thdr)) $tmp_sched = $thdr['rundate'] . $thdr['time'] . '00';
else {
  $thdr['time'] = '0000'; // add a dummy entry for SQL insert
  $tmp_sched = $Stimestamp;
}
print "start time $Stimestamp $tmp_sched\n";
$Sdate = new DateTime($tmp_sched, new DateTimeZone('Europe/London'));  
$thdr['timescheduled'] = $Sdate->format('Y-m-d H:i:sP');  // Bloomberg schedule in London time!
$rhdr = array_merge($rhdr,$thdr);
print "start time 2\n";
//Bloomberg responds in 'New York time' no matter what!
// Convert started timestamp from Standard Estern Time day light Savings 'EDT' -> CET

$Sdate = new DateTime($Stimestamp, new DateTimeZone('EDT')); 
print "start time 3\n";
$SedtDtTm = $Sdate->format('Y-m-d H:i:sP') . "<br>";  // 'SET' Date time
$Sdate->setTimezone(new DateTimeZone('CET'));   
$ScetDtTmX = $Sdate->format('Y-m-d H:i:sP') . "<br>";  // 'CET' Date time
$log->logit('Info',"start time=$started SET=$SedtDtTm, CET=$ScetDtTmX");
//$rhdr['timestarted_CET'] =  $Sdate->format('Y-m-d H:i:s');
$rhdr = array_merge(['started_local' => $Sdate->format('Y-m-d H:i:s')], $rhdr);
$started = $rhdr['started_local'];

//var_dump($rhdr);
//print "end dumping headers\n";

// Now the names of fields in Bloombergs currency rate response
$fields = (array) $response->fields->field;
//var_dump($fields);
//print "end dumping fields\n";

// Massage the response into a an array of associative arrays (one per currency) 
$currrate = [];
foreach($response->instrumentDatas->instrumentData as $daId){
//print "start dumping instrumentData\n";
//  var_dump($daId);
  $dacode = $daId->code;
  $idfromto = $daId->instrument->id;
//  print "DATA CODE $dacode $idfromto\n";
  $td1 = [];
  foreach($daId->data as $da){
//  var_dump($da);
    $td1[] = $da->value;
  }
  $td2['started_local'] = $started;
  $td2['CODE'] = $dacode;
  $td2['FROM_CURRENCY'] = substr($idfromto,0,3);
  $td2['TO_CURRENCY'] = substr($idfromto,3);
  if ($td2['TO_CURRENCY'] == '') $td2['TO_CURRENCY'] = $td2['FROM_CURRENCY'];
  $td2 = array_merge($td2, array_combine($fields, $td1));
//  var_dump($td2);
  $currrate[] = $td2;
}
//print "end dumping instrumentData\n"; 
//var_dump($currrate);
//print "end dumping currencies\n";

//create SQL statements
$stmtarray = $sqlit([$rhdr]);
$hdrstmt = "INSERT INTO $hdrtable ". $stmtarray[0] . ' VALUES ' . $stmtarray[1]; 
file_put_contents("$hdrtable.sql", $hdrstmt);   
$stmtarray = $sqlit($currrate);
$crstmt = "INSERT INTO $crtable ". $stmtarray[0] . ' VALUES ' . $stmtarray[1];      
file_put_contents("$crtable.sql", $crstmt);
//var_dump($hdrstmt);
//var_dump($crstmt); 
return $_RESULT = TRUE;


No comments:

Post a Comment