Admin | SmarterDataCollection

Just Code : Get the cell letter of the active cell in Excel

September 20, 2019 by Admin

Here is the code on how to do this in Unicom VBA. Needed to do this today , so thought it was a good one for the just code section

Set oCel = oDataMapHelp.cells[iRow][iCol]
                  oArray = split(oCel.Address,"$")
                  sColLeter = oArray[1]

Here is some code to tabulate questions in PHP

September 20, 2019August 14, 2019 by Admin

<?php
// url to test : http://[server]/[VirtualDirectory]/TOM.php?q=d1&m=&f=d1.containsany({japan,china})

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

if (isset($_GET["q"]) && $_GET["q"] != ""){
// d1*d3 or d1]d3 or d1+d3
$q = $_GET["q"]; 
$q = str_replace("]","+",$q); 
echo $q;
} else {
die("No Question Specified"); 
}

if (isset($_GET["m"]) && $_GET["m"] != ""){
$m = "json"; 
} else {
$m="html";
}

if (isset($_GET["f"]) && $_GET["f"] != ""){
$filter =$_GET["f"]; 
} else {
$filter="";
}

//phpinfo();
echo 'Start';
// Create the Tables object and open the DataSet
//Set TableDoc = CreateObject("TOM.Document")
$oTableDoc = new COM("TOM.Document") or die("Unable to instantiate TOM.Document"); 

$sProject = "CSUITE_2019";
$oMDD = "D:\\FMRoot\\Master\\CSUITE_2019\\CSUITE_2019.mdd";


$oData = "Provider=SQLOLEDB.1;Data Source=IBMIBV001;User ID=;Password=;Initial Catalog=" . $sProject . ";Integrated Security=SSPI;Persist Security Info=true";

$oTableDoc->DataSet->Load($oMDD, "", $oData, "mrRdbDsc2", "", "{..}");
$oTableDoc->DataSet->View = 1 ;

// Set the properties of the table document
//$oTableDoc->Language = "ENA";
//$oTableDoc->Context = "Question";
//$oTableDoc->LabelType = "Label";


$oTableDoc->Default->Clear();
$oTableDoc->Default->CellItems->AddNew(0);
//$oTableDoc->Default->CellItems->AddNew(1); // Percents
$oTableDoc->ProfileDefault->Clear();

//Define the tables
$oTable = $oTableDoc->Tables->AddNew("Table1",$q, "Table1");
if ( $filter != "" ) {
echo $filter;
$oTable->Filters->AddNew("Filter1", $filter);
}
//Table.Filters.AddNew("Filter1", "d1.ContainsAny({China})")

//Populate the tables
$oTableDoc->Populate();
$oTableXML = $oTableDoc->GetTablesXml("Table1", 1);

$xml=simplexml_load_string($oTableXML) or die("Error: Cannot create object");
$json = json_encode($xml); // convert the XML string to JSON

if ( $m == "json" ) {
echo $json;
} else {
$oHtmlRender = new COM("ExportHtml.Renderer") or die("Unable to instantiate TOM.Document"); 
//$oHtmlRender->OutputBodyOnly = 1;
echo $oHtmlRender->Render($oTableXML);
}

// Render as an HTML table
//$oHtmlRender = new COM("ExportHtml.Renderer") or die("Unable to instantiate TOM.Document"); 
//$oHtmlRender->OutputBodyOnly = 1;
//echo $oHtmlRender->Render($oTableXML);

echo 'Done';

?>

PHP to List mdd labels

August 7, 2019 by Admin

Here is some code to list the variables in an MDD

<?php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

//phpinfo();
echo 'Start';
// Create the MDM object and open the Short Drinks .mdd file in read-write mode

$oMDM = new COM("MDM.Document") or die("Unable to instantiate MDM.Document"); 
$oMDM->Open("C:\\inetpub\\wwwroot\\IBV_USERS.mdd", '', 2);
foreach ($oMDM->Variables as $oVar) {
echo $oVar->Label ."<br/>";
}


echo 'Done';

?>

I have been meaning to post something like this code for a while. Here is an example of calling one of the IBM watson API’s from a MRS script. Its just code and i think easy to understand. The full API i am playing with can be found here. https://cloud.ibm.com/apidocs/language-translator#translate

Dim sUrl
Dim http
Dim apiHash

sUrl = "https://gateway-syd.watsonplatform.net/language-translator/api/v3/translate?version=2018-05-01"
set oHTTP = createObject("Microsoft.XMLHTTP")

oHTTP.open("GET", sUrl, false)
'The string after "Basic" is the base64 encoded of apikey:[YourAPIKEY]
'I.e.: apihash = Base64Encode("apikey:[YourAPIKEY]")

apiHash = "[YOURHASH]"
oHTTP.setRequestHeader("Authorization", "Basic " + apiHash)
oHTTP.setRequestHeader("content-type", "application/json")
oHTTP.send("{""text"":[""Hello""],""model_id"":""en-es""}")

If oHTTP.status = 200 Then
    debug.Log("RESPONSE : " + oHTTP.responseText)
else
    debug.Log("ERRCODE : " + ctext(oHTTP.status))
    debug.Log("RESPONSE : " + oHTTP.responseText)
End If

Create a nested folder structure with the file system object.

June 27, 2019 by Admin

We needed to make some nested folder structures for a project we are running with mrs scripts and i thought i would post some code to do that. I am sure there are many ways to do this , but this is what we did in just a few lines

Dim sPath , oItem, sCurrentPath,oFSO


Set oFSO = CreateObject("Scripting.FileSystemObject")

For each oItem in split("C:\Temp\Doug\Doug1\Doug2\","\")

if ( find(oItem,":") > -1 ) then 
      sCurrentPath = oItem + "\"
else
      sCurrentPath = replace(sCurrentPath + "\" + oItem,"\\","\")
End If

If oFSO.FolderExists(sCurrentPath) = False Then
      debug.Log(sCurrentPath)
      oFSO.CreateFolder(sCurrentPath)
End If


Next

UNICOM® Systems Releases UNICOM® Intelligence – Quantum 6

January 6, 2019 by Admin

More info here https://www.unicomsi.com/unicom-systems-releases-unicom-intelligence-quantum-6-wm-web.html

Just Code : Delete all files with specific month

December 4, 2018 by Admin

I needed a quick bit of code today so that i could clean up my hard disk. This script will delete all the files that match a specific month number. One day it may save you a few minutes.

LoopFolders("D:\Servers\116\SQLBackups",10)


Sub LoopFolders(sFullPath,iMonth)

 Dim oFolder,oSubFolder,oFSO,oFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FolderExists(sFullPath) Then

 Set oFolder = oFSO.GetFolder(sFullPath)
For Each oFile In oFolder.Files

	if ( Month(oFile.DateCreated) = iMonth ) Then

 		debug.Log(oFolder.Path + "\" + oFile.Name +":" +  ctext(oFile.DateCreated))

 		oFSO.DeleteFile(oFolder.Path + "\" + oFile.Name)

 	End If

Next
For each oSubFolder in oFolder.SubFolders

 	debug.Log("“Folder:"+ oSubFolder.Name)

 	LoopFolders(oSubFolder.Path,iMonth)

Next
End If

Set oFSO = null Set oFolder = null Set oFile = null End Sub

Testing a carousel feature in WP

September 5, 2018September 5, 2018 by Admin

Here are some images/links to IBM Thought Leadership that the team i work for has helped produce. In most cases Unicom intelligence, SPSS Statistics and Modeler have been used to provide analytical insight into the data collected to help drive and validate the content in these documents. If you work in the Government or Education sectors, then these are for you. Happy Clicking (If you want more info then please feel free to drop me a email at dporton@au1.ibm.com )

PHP 7.2.2 and Unicom

May 31, 2018 by Admin

I am currently looking into writing a PHP interface that schedules Unicom tasks and i thought i would make a few post’s as i go along. Obviously, the first thing you have to do is to install PHP and set it up for COM use. Setting PHP up for COM is easy , just make sure you have this in your PHP.ini file. ( Search for [COM] )

; allow Distributed-COM calls
; http://php.net/com.allow-dcom
extension=php_com_dotnet.dll
com.allow_dcom = true

The way to check that com is enabled it so run a PHP file that has this in it

<?php
phpinfo(); 
?>

And when you run the file , make sure you see this

next we need to see if com is activated and works so i tried this on Excel. I have excel 2013 my machine

<?php 
     ini_set('display_errors', 1);
     ini_set('display_startup_errors', 1);
     error_reporting(E_ALL); 
     $xl = new COM("Excel.Application"); 
?>

and it produced this ,

Fatal error: Uncaught com_exception: 
Failed to create COM object `Excel.Application': 
Access is denied. in C:\inetpub\wwwroot\LoopMDD.php:9 Stack trace: #0 C:\inetpub\wwwroot\LoopMDD.php(9): 
com->com('Excel.Applicati...') #1 {main} thrown in C:\inetpub\wwwroot\LoopMDD.php on line 9

and i am ok with that as it shows the COM statement worked. If you want to take the excel thing further then you can take a look at this post and it will show you how to get access to XL this way. So whats next .. Well one of the first things i need to do is open an MDD so here is the PHP code to do that


<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

	echo 'Start';
  	$oMDM = new COM("MDM.Document") or die("Unable to instantiate MDM.Document"); 
	$oMDM->Open("C:\\inetpub\\wwwroot\\USERS.mdd", '', 2);
	foreach ($oMDM->Variables as $oVar) {
		echo $oVar->Label ."<br/>";
	}
	echo 'Done';

?>

and so when i ran the PHP file i got the labels. So PHP COM , 64bit is enabled and working , now onto the next bit, watch this space.

Send SMTP Email via Gmail

April 20, 2018 by Admin

Just helped someone get this sorted and thought i woulds share. The trick was to allow Less Secure apps in your account. Here is the link

https://myaccount.google.com/lesssecureapps?pli=1

Turn it on and then just use the normal CDO code.

Dim oEmail, sEmailFrom, sEmailTo, sEmailSubject, sEmailBody,sBCC

' Send email

sEmailTo = "admin@SmarterDimensions.com"
sEmailFrom = "FromAddress@SmarterDimensions.com"
sEmailSubject = "Email Subject"
sEmailBody = "Email Body this is bold"
sBCC = "SmarterDimensions@live.com.au"


Set oEmail      = CreateObject("CDO.Message")

With oEmail
 With .Configuration.Fields
  .Item["http://schemas.microsoft.com/cdo/configuration/smtpusessl"] = True
  .Item["http://schemas.microsoft.com/cdo/configuration/smtpserver"] = "smtp.gmail.com"
  .Item["http://schemas.microsoft.com/cdo/configuration/sendusing"] = 2
  .Item["http://schemas.microsoft.com/cdo/configuration/sendusername"] = "douglas.porton@gmail.com"
  .Item["http://schemas.microsoft.com/cdo/configuration/sendpassword"] = "xxxxxxxxxxx"	
  .Item["http://schemas.microsoft.com/cdo/configuration/smtpserverport"] = 465
  .Item["http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"] = 60
  .Item["http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"] = 1	
  .Update()		
 End With
End With	

    
oEmail.From     = sEmailFrom
oEmail.To       = sEmailTo
oEmail.BCC      = sBCC
oEmail.Subject  = sEmailSubject
oEmail.HTMLBody = sEmailBody

oEmail.Send()

set oEmail = Null

Just Code : Get the cell letter of the active cell in Excel

Like this:

Here is some code to tabulate questions in PHP

Like this:

PHP to List mdd labels

Like this:

IBM Watson API Test

Like this:

Create a nested folder structure with the file system object.

Like this:

UNICOM® Systems Releases UNICOM® Intelligence – Quantum 6

Like this:

Just Code : Delete all files with specific month

Like this:

Testing a carousel feature in WP

Like this:

PHP 7.2.2 and Unicom

Like this:

Send SMTP Email via Gmail

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: