Code snippets I find useful or just interesting
Attribute VB_Name = "Module1"
Sub Main()
'Chart Example Code
Dim cObj As ChartObject
Dim scatterGraph As Chart
Dim tag
Dim rDate
Dim eDate
'clear the graphs
Application.DisplayAlerts = False 'turn warnings off otherwise the user is prompted every time
If Worksheets("sheet1").ChartObjects.Count > 0 Then
Worksheets("sheet1").ChartObjects.Delete
End If
Application.DisplayAlerts = True 'turn warnings back on
Set scatterGraph = Charts.Add() 'create a new graph object
scatterGraph.Name = "Scatter Graph" 'name the graph
scatterGraph.ChartTitle.Text = "Scatter Graph for XXX: " & tag & " on " & rDate 'set the title up
scatterGraph.ChartType = xlXYScatterLines 'set the chart type up
'clear any existing graph series data... seems to be needed otherwise excel creates data automatically
Dim i
Dim x
For i = scatterGraph.SeriesCollection.Count To 1 Step -1
scatterGraph.SeriesCollection(i).Delete
Next i
scatterGraph.SeriesCollection.Add _
Source:=Worksheets("sheet1").Range("I6:I" & LastRow) 'setup the Y axis using data
scatterGraph.SeriesCollection(1).Name = "Average KWh" 'name the data series for the legend
scatterGraph.SeriesCollection(1).XValues = Sheet1.Range("H6:H" & LastRow) 'setup the X axis series
scatterGraph.Axes(xlCategory).MajorUnit = 0.04167 'setup x axis formats, in this case hourly divisions
scatterGraph.Axes(xlCategory).MinorUnit = 0.04167
scatterGraph.Axes(xlCategory).MaximumScale = DateValue(rDate & " 23:59:00") 'set the minimum point
scatterGraph.Axes(xlCategory).MinimumScale = DateValue(rDate & " 00:01:00") 'set the maximum point
'scatterGraph.Axes(xlCategory).TickLabels.Orientation = xlTickLabelOrientationUpward
scatterGraph.Axes(xlCategory).TickLabels.Orientation = 41 'angle the x axis legend
scatterGraph.Axes(xlCategory).CrossesAt = 0 ' force the axis to cross at Y=0
Set scatterGraph = scatterGraph.Location(Where:=xlLocationAsObject, Name:="Sheet1") 'define a location to draw the graph
scatterGraph.Parent.Left = 605 'left position of graph
scatterGraph.Parent.Top = 90 'top
scatterGraph.Parent.Width = 800 'how wide
scatterGraph.Parent.Height = 400 'how high
Sheet1.Range("B2").Activate 'reset active range to unhighlight the graph data
End Sub
Attribute VB_Name = "Module1" Sub Main() Dim tag Dim rDate Dim eDate 'Range example code Sheet1.Range("Tag_Number").Activate 'activate a specific named cell tag = Sheet1.Range("Tag_Number").Value 'get the value from the cell If IsEmpty(tag) Then 'Check it's contents MsgBox "No tag was entered please enter a valid tag" Exit Sub End If rDate = Format(Sheet1.Range("Report_Date").Value, "yyyy-mm-dd") 'Format a cell If rDate = "" Then MsgBox "No date was entered please enter a valid date" Exit Sub End If eDate = Format(Sheet1.Range("Report_Date").Value + 1, "yyyy-mm-dd") 'clear a range Sheet1.Range("A4").Activate Range(Selection, Selection.End(xlDown)).Select 'go to the bottom Range(Selection, Selection.End(xlToRight)).Select 'go to the right Selection.Clear 'clear the contents of the data Sheet1.Range("A4").Select 'setup and format headings 'A1 = Record id Sheet1.Range("A4").Activate Sheet1.Range("A4").Value = "Record ID" Sheet1.Range("A4").Font.Bold = True 'B1 = tag id Sheet1.Range("B4").Value = "XXX ID" Sheet1.Range("B4").Font.Bold = True 'C1 = pulse time Sheet1.Range("C4").Value = "Pulse Time" Sheet1.Range("C4").Font.Bold = True 'D1 = meter reading Sheet1.Range("D4").Value = "Reading" Sheet1.Range("D4").Font.Bold = True 'E1 = update_time Sheet1.Range("E4").Value = "Update Time" Sheet1.Range("E4").Font.Bold = True 'F1 = interval Sheet1.Range("F4").Value = "Interval" Sheet1.Range("F4").Font.Bold = True 'G1 = KWh Sheet1.Range("G4").Value = "KWh" Sheet1.Range("G4").Font.Bold = True 'H1 = interval mid point Sheet1.Range("H4").Value = "Interval Mid Point" Sheet1.Range("H4").Font.Bold = True 'I1 = average KWh Sheet1.Range("I4").Value = "Average KWh" Sheet1.Range("I4").Font.Bold = True 'now work with the data Sheet1.Range("A4").Activate Range(Selection, Selection.End(xlDown)).Select 'go to the bottom Range(Selection, Selection.End(xlToRight)).Select 'go to the right Selection.Name = "TagData" 'calculate the interval Dim LastRow Sheet1.Range("F6").Select Sheet1.Range("F6").Activate 'activate the starting cell Sheet1.Range("F6").Value = "=C6-C5" 'formula Sheet1.Range("G6").Activate Sheet1.Range("G6").Value = "=(D6-D5)/1000" Sheet1.Range("H6").Activate Sheet1.Range("H6").Value = "=AVERAGE(C6,C5)" Sheet1.Range("H6").NumberFormat = "dd-mmm-yyyy hh:mm" Sheet1.Range("I6").Activate Sheet1.Range("I6").Value = "=G6/(F6/(1/24))" Sheet1.Range("E6").Activate Sheet1.Range("E6").End(xlDown).Select LastRow = ActiveCell.Row 'fill down example Sheet1.Range("F6:I" & LastRow).FillDown Sheet1.Range("H6").Select 'select a single cell. Sheet1.Range("H6").Activate 'activate the starting cell Sheet1.Range("B2").Activate 'reset active range End Sub
Attribute VB_Name = "Module1" Sub Main() Dim conn As ADODB.Connection 'connection object Dim rs As ADODB.Recordset 'record set object Dim cs As String 'connection string Dim cmd As ADODB.Command 'command string Dim cObj As ChartObject Dim scatterGraph As Chart Dim tag Dim rDate Dim eDate 'ADO Example Code 'connect and get data Set conn = New ADODB.Connection 'Create a connection object conn.Open Sheet1.Range("ODBCSource").Value, Sheet1.Range("username").Value, Sheet1.Range("password").Value 'open the connection Set cmd = New ADODB.Command 'Create a command object cmd.ActiveConnection = conn 'set the active connection 'setup the command, quick and dirty... cmd.CommandText = "select something from somewhere" Set rs = New ADODB.Recordset 'create a record set object Set rs = cmd.Execute(, , adCmdText) 'execute the command If rs.EOF Then 'no records returned, stop processing MsgBox "No Records were found for you criteria", vbOKOnly Exit Sub End If Sheet1.Range("A5").CopyFromRecordset rs 'return the record set rs.Close 'close the record set conn.Close 'close the connection End Sub
New or Old Join Syntax?
I learned SQL many years ago on Oracle 5 running on a DEC VAX. Muscle memory is highly ingrained after such a long period of time and I write SQL almost automatically from a syntatic perspective.
The move to ANSI-92 SQL saw the introduction of new join syntax that is supposed to be clearer to understand. I still don't like it, and I don't believe it makes it clearer to understand however the battle has been lost, most new DB platforms don't support (*) or (+) syntax. There may still be a lot of old style joins found here though - be warned!
/* Mike Howard 27-11-2014 Calculates the rolling consumption of data. see if we can identify tag reads where the increment is zero, 2 or more times in a row. */ with result_set as ( select tag_id, rec_id, pulse_time, update_time, meter_reading, meter_reading-lag(meter_reading) OVER client_window as net_read, pulse_time-lag(pulse_time) OVER client_window as net_time, meter_reading - lag(meter_reading,2) OVER client_window as comparison_reading --compares the reading with the 2nd last reading from meters.tag_data --where pulse_time > '2014-03-31' --where tag_id = XXXX --if a specific tag is being viewed window client_window as (partition by tag_id order by pulse_time) ) select * from result_set where comparison_reading = 0 --if you only want the 0 reads and date_part('hour',pulse_time) < 9
/* Mike Howard 26-11-2014 Calculates the rolling consumption of data and converts to an average calculation Uses complex functions that may not be transportable. */ with result_set as ( select tag_id, rec_id, pulse_time, update_time, date_part('year',pulse_time) as year, date_part('month',pulse_time) as month, date_part('day',pulse_time) as day, date_part('hour',pulse_time) as pulse_hour, meter_reading, meter_reading-lag(meter_reading) OVER client_window as net_read, pulse_time-lag(pulse_time) OVER client_window as net_time, (meter_reading-lag(meter_reading) OVER client_window) / extract( epoch from (pulse_time-lag(pulse_time) OVER client_window)/3600)/1000 as KWh from meters.tag_data where tag_id = XXXX --if a specific tag is being viewed window client_window as (partition by tag_id order by pulse_time) ) select * from result_set where pulse_time > '2014-06-26 23:00' --where net_read = 0 --if you only want the 0 reads
<div class="row">
<div class="medium-12 column">
<div style="background-color: #f2f2f2">Some Text: <strong>?some function? </strong> Some More text.</div>
</div>
</div>
/*
zurmoRESTImport.php
Author: Mike Howard
Date: 2015-12-12
Version:
Description:
Takes a csv file and imports into Zurmo as a contact record
*/
//variables
require 'config.php'; //for code resuse of logins etc
$filename = $argv[1]; //change to the appropriate file name
$username= $argv[2]; //change to variable
$password= $argv[3]; //change to variable
$doFormsUrlLink=$zurmoRoot . '/a php program.php?CID='; //base url, update later with a variable
$contactStage='Customer'; //the default contact stage
$contactType='Residential'; //make contact Residential contacts
$contactOwner='owner'; //the default contact owner
//add other variables here
//function from the 'net - thanks to the original author who I now can't find..
function csv_explode($str, $delim = ',', $qual = "\"") {
$len = strlen($str);
$inside = false;
$word = '';
for ($i = 0; $i < $len; ++$i) {
if ($str[$i]==$delim && !$inside) {
$out[] = $word;
$word = '';
} else if ($inside && $str[$i]==$qual && ($i < $len && $str[$i+1]==$qual)) {
$word .= $qual;
++$i;
} else if ($str[$i] == $qual) {
$inside = !$inside;
} else {
$word .= $str[$i];
}
}
$out[] = $word;
return $out;
}
//include standard Rest Class & Login Function
class ApiRestHelper {
public static function createApiCall($url, $method, $headers, $data = array()) {
//debug print turn off in production
print $url . "\n";
if ($method == 'PUT')
{
$headers[] = 'X-HTTP-Method-Override: PUT';
}
$handle = curl_init();
curl_setopt($handle, CURLOPT_URL, $url);
curl_setopt($handle, CURLOPT_HTTPHEADER, $headers);
curl_setopt($handle, CURLOPT_RETURNTRANSFER, true);
curl_setopt($handle, CURLOPT_SSL_VERIFYHOST, false);
curl_setopt($handle, CURLOPT_SSL_VERIFYPEER, false);
// may be needed to ignore the proxy which drops long http requests
curl_setopt($handle, CURLOPT_PROXY, false);
switch($method)
{
case 'GET':
break;
case 'POST':
curl_setopt($handle, CURLOPT_POST, true);
curl_setopt($handle, CURLOPT_POSTFIELDS, http_build_query($data));
break;
case 'PUT':
curl_setopt($handle, CURLOPT_CUSTOMREQUEST, 'PUT');
curl_setopt($handle, CURLOPT_POSTFIELDS, http_build_query($data));
break;
case 'DELETE':
curl_setopt($handle, CURLOPT_CUSTOMREQUEST, 'DELETE');
break;
}
$response = curl_exec($handle);
return $response;
}
public function login($username, $password, $zurmoRoot) {
$headers = array(
'Accept: application/json',
'ZURMO_AUTH_USERNAME: ' . $username,
'ZURMO_AUTH_PASSWORD: ' . $password,
'ZURMO_API_REQUEST_TYPE: REST',
);
$response = ApiRestHelper::createApiCall($zurmoRoot . '/app/index.php/zurmo/api/login', 'POST', $headers);
print_r($response . "\n");
$response = json_decode($response, true);
if ($response['status'] == 'SUCCESS') {
return $response['data'];
}
else {
print "Login has failed\n";
return false;
}
}
}
//Check parameters
if ($argv[1] == "-h" || $argv[1] == "--help") {
print "The program parameters are filename, user name, password\n";
exit;
}
if (!$filename) {
print "Error, You must enter a filename as the first parameter\n";
exit;
}
if (!$username) {
print "Error, You must enter a Zurmo User as the second parameter\n";
exit;
}
if (!$password) {
print "Error, You must enter the password for the Zurmo user as the third parameter\n";
exit;
}
//login to Zurmo
$authenticationData = ApiRestHelper::login($username,$password,$zurmoRoot);
if (!$authenticationData) {
//we haven't logged in, stop!
print "Login Failed, verify your username and password\n";
exit;
}
//get session values returned from login
$headers1 = array(
'Accept: application/json',
'ZURMO_SESSION_ID: ' . $authenticationData['sessionId'],
'ZURMO_TOKEN: ' . $authenticationData['token'],
'ZURMO_API_REQUEST_TYPE: REST',
);
//open the interface file
$handle = fopen($filename, "r");
if ($handle) {
while (($line = fgets($handle)) !== false) {
// process the line read
list(
$RowID,
$firstName,
$lastName,
$Address1,
$suburb,
$phoneNo,
$description
) = csv_explode($line); //expand to include all the field names.
//debug print, turn off in production
//print(strlen($line)."\n");
print($line . "\n");
// skip first line which is header
if ($RowID == "Row ID") {
continue;
}
// build the data
$data = Array
(
'firstName' => $firstName,
'lastName' => $lastName,
'mobilePhone' => $phoneNo,
'description' => $description . "\n Contact Imported from Historical Data \n",
'primaryAddress' => Array
(
'street1' => $Address1,
'city' => $suburb,
'state' => $defaultState,
'country' => 'Australia',
),
'state'=> Array
(
'id' => 6, //Hard Coded, need a better way to do this
),
'owner' => Array
(
'id'=>11, //Hard Coded user, need a better way to do this
),
);
//Add any Custom Fields to the array here
//debug print - turn off in production
print_r($data);
print "\n";
//save the record
$response = ApiRestHelper::createApiCall($zurmoRoot .'/app/index.php/contacts/contact/api/create/', 'POST', $headers1, array('data' => $data));
$response = json_decode($response, true);
print_r($response);
//Check what happened
if ($response['status'] == 'SUCCESS') {
$contact = $response['data'];
print "Contact saved successfully\n";
}
else {
// Error
$errors = $response['errors'];
print "There was an error saving the contact\n";
print_r($errors);
print "\n";
}
}
//close the file
print "closing the file \n";
fclose($handle);
}
else {
// error opening the file.
print "Could not open file " . $filename . " to import contacts\n";
}
//now logout
$response = ApiRestHelper::createApiCall($zurmoRoot . '/app/index.php/zurmo/api/logout', 'GET', $headers1);
$response = json_decode($response, true);
//debug, turn off in production
print_r($response);
print("\n");
if ($response['status'] == 'SUCCESS') {
//We're logged out
print "Logged Out\n";
}
else {
print "Logout failed \n";
}