Auf Wiedersehen

2012-03-24 I wrote my first post in this blog. I started to write due to inspiration of my colleague Petr Hutar, in one of the first meetings with him he said, “I have lot’s of other interests beside computers, I just forgot what they are”. At that time Petr put words on who I felt I was, so I stole with pride and made the quote mine.
Now Petr is leaving the company to pursue a somewhat different IT career from administrative IT systems and IT architecture to  robotics and factory automation. As Petr is a extremely creative person with a technical degree in robotics, I’m sure we will see great things from Petr in future.
You may call Petr an IT- nerd, but more than that he is also a well respected eloquent gentleman that can see and present the big picture, not only the miniscule details of program algorithms. My best wishes Petr, I hope we have a beer together in the not too distant future.


Happy New Year

Last task this work year. Upload currency rates for year end closing.
View from the office 08:20 in the morning. If you look closely to the right you can see reflections of me drinking coffee.

The dollar was sinking which worried the financial guys. I suspect it is the recent quibble between US and Russia that makes the market a bit apprehensive.
All the same - Happy New Year to all of you.


SharePoint 5000 limit trick

Another trick to circumvent the dreaded 5000 items limit in Sharepoint.

I found this trick how to create a very complex view filter (combining two or even more boolean expressions in the view filter):
If you use two or more columns in the filter expression, the determining index or indexes should use an AND operator. For example, if you want to return Dogs from a large list of animals. You have an unindexed column called Species where you have Dog as a value. If you just query for Species = Dog, your query will be throttled. However, if you have an indexed column called Class, your query becomes Class = Mammals AND Species = Dog. You could also search for cats and dogs with the query Class = Mammals AND (Species = DOG OR Species = Cats). The second query selects all Mammals, and then filters to Dogs and Cats.”

This is an example of a view filter definition:

I have not figured out where I type or 'click in' the parentheses, but once I done that, I have great hope this Microsoft trick may work.
If you know how you create this 'dog or cat' filter in sharePoint please drop me a mail.

After rereading the post my eyes fell on the impossible filter query "Class = Mammals AND (Species = DOG OR Species = Cats)", the latter paranterized part filtering on species where DOG is upper case singular, while the feline Cats is plural lowercase with a leading uppercase 'C'. And missing quotes suround Mammals, DOG & Cats.
Do anyone beleive the author have tested this or even have had a SharePoint list to test the filter on?


Cash free

Andreas sent me this link https://youtu.be/NrmMk1Myrxc really cool. It reminded me I am cash free, it is probably about two year or so since I used cash in Sweden. I use a credit card and Swish for money transfer and the Inet bank for bills. Very convenient. I’m looking forward to the day when we can do away with the cashier counter, this post is unfortunately in swedish only but says a lot about at least my frustration of standing in line waiting to pay.     
Last time I used cash was probably in Antwerp buying bollekes and frietjes. Best beer, best french fries in the world.


Blue & Lonesome

A new Stones album doesn’t come often these days and this new one is a cover album, I expected a lot, these guys have been around for a while and they did some stunning covers some 50 years ago.  After listen to the songs once I think the album is a bit "pale", the only really good track was Howlin’ Wolf’s “Just like I treat you”.  “Just  your fool” and “I can’t quit you baby” are alright, the rest of the songs I don’t remember.
In school I once used the word “quit” in an english essay, my english teacher told me there is no such word but I had heard “I can’t quit you baby” so I knew. However my teacher was not impressed by my reference to a rhythm and blues song. I was as usually given a bad mark in english, "quit" probably didn't matter much. I didn’t write better english then and I wasn’t good teacher’s pet material either, so I expected a bad english mark.


A Bash snippet

We have a problem with Cron we cannot debug, very seldom but it happens Cron goes wild and incorrectly submits jobs. We have not been able to debug and fix the problem. We only schedule shell scripts (Bash) in Cron so I wrote a snippet to add at the beginning of such shell scripts to prevent execution daytime:
This snippet is far from perfect but it should capture most of these "spurious" submits, until we find the cause, if the submit is trapped by the snippet we will have some more info about the incident and hopefully catch the bug(ger).


PHP at Bloombergs - 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.)

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;

$usercred=getUserCredentials($context,'bloomberg.xml', "$userid");
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");
//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'];

//print "end dumping headers\n";

// Now the names of fields in Bloombergs currency rate response
$fields = (array) $response->fields->field;
//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"; 
//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);
return $_RESULT = TRUE;