Code snippets I find useful or just interesting

VBA Code

		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
	

SQL Code

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
	

HTML code

	<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>
	

Zurmo REST code

	/*
	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";
	}