ECO Audit Tool

This is an example of a simple application I produced while working as a technical manager. If you came here for help producing apps to improve processes, you’ll be better served over at Automate Your Chores.

I created the ECO Audit Tool to provide audit team members a bespoke platform for conducting Energy Performance Certificate (EPC) audits on behalf of energy suppliers for the Ofgem Energy Company Obligation (ECO) scheme. Prior to the introduction of the ECO Audit Tool, the audit process involved the use of Excel and Access and emailing of evidence documents.

Some benefits we enjoyed since adopting the ECO Audit Tool include:

  • Increase of over 100% to the number of audits performed each day.
  • Faster audits led to better team morale. Less time was spent performing administrative tasks; more time was spent auditing.
  • Audit data stored in SQL database allowed for quick reporting and monitoring of all stages of the audit process

User Interface

ECO Audit Tool UI

The user interface was built using WinForms for its speedy development. As it is not customer facing the appearance of the application was not critical. Despite this, auditors have praised the ease of use and simple design. It also impressed a client during a demonstration of our audit process.

It is designed using the lowest resolution monitors available at the company. I then used Visual Studio’s anchoring feature to allow the interface to scale according to window size. I created a small class to handle the picture box scaling as this proved more reliable than using anchors alone.

Threading

In order to prevent the GUI freezing every time a sub is run, I used Visual Studio’s System.Threading. I used delegates to allow communicate with the form controls from the business logic:

Private Delegate Sub SetPictureBoxImageDelegate(image As Image)
Private Sub SetPictureBoxImage(image As Image)
If PictureBox1.InvokeRequired Then
Dim del As New SetPictureBoxImageDelegate(AddressOf SetPictureBoxImage)
Me.Invoke(del, New Object() {image})
Else
PictureBox1.Image = image
End If
End Sub

SQL

Due to the nature of ECO audits – it is possible to need multiple iterations under one audit job – data is stored in 2 tables: Audits and AuditQueue.  This keeps a clean main table while allowing visibility of full audit history by joining AuditQueue to Audits.

  1. When a new address is audited, all relevant data is inserted into the Audits table. An audit instance is then also inserted into the AuditQueue table.
  2. When evidence has been received to the ASP.Net evidence portal a boolean flag is raised to remove the job from the evidence portal drop down list and mark the job as ready to audit. This then appears in the queue of jobs visible in the bottom left of the ECO Audit Tool.
  3. When an auditor selects the job it is ‘locked’ to that user by updating AuditQueue  with the username and setting a boolean field. This prevents another user from opening the same job. Job data such as the property address is populated from the main Audits table. Any previously input data is also used to populate the form.
  4. On use of the ‘Save’ and ‘Submit’ buttons all input data is validated and updated to AuditQueue.
  5. Audits is then updated with the most recent audit status. If this status shows further information is required from the installer, the job again becomes available in the evidence portal for the installer to upload to. Otherwise the Complete boolean is set to TRUE and the audit is complete.

Example – getting list of audit jobs

'Get list of queued audits
Private Sub GetReady_Thread()
Dim SQLstring As String
Dim SQLCon As SqlConnection = New SqlConnection(ConnString)
Dim SQLCMD As New SqlCommand()
Dim SQLda As SqlDataAdapter

Try
Dim ReadyToAudit As New DataTable

SQLstring = "Select Top 20 " _
& "AuditId, a.JobNo, e.Addressline1, e.Postcode , e.Warning, e.UserUpdated, a.AuditBackground, e.RdSAPiD " _
& "From AuditQueue a " _
& "Inner Join Audits e on e.jobno = a.jobno " _
& "Where a.ReadyToAudit = 1 " _
& "AND a.auditcompleted = 0 " _
& "AND a.Locked is null " _
& "Order by AuditId Desc"
SQLda = New SqlDataAdapter(SQLstring, SQLCon)
SQLCon.Open()
SQLda.Fill(ReadyToAudit)
SQLCon.Close()
ReadyToAudit_Loaded = True
SetGetReadyGridViewSource(ReadyToAudit)
SetGetReadyGridViewEnabled(True, Color.Black)

Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

End Sub

Audit evidence

Audit evidence can originate from our cloud storage facility, from our evidence upload portal or both.

If there is no existing evidence for the job in cloud storage, the audited installers are requested to upload audit evidence to an ASP.Net evidence portal. This allows the installers to select an address that has been called for audit and upload evidence for it. Installers can only see and select their own audit jobs. This evidence is then saved to a remote server network drive.

On selection of an audit, the application iterates through the folders in each location to find every document. This is then saved to a new folder on the remote server named after the AuditID. This provides a snapshot of the evidence that was available at the time of the audit.

All files in this audit folder are then called into the evidence list box. On selection of the list item, the image appears in the picture box. On double click the file opens in its default program.

Example – iterating through evidence files


Dim Dir As String = "*************" & AssNo & "\Survey Images\" & RdSAPiD & "\"
If AssNo <> "" Then
If RdSAPiD <> 0 Then
Try
Dim folderInfo As New IO.DirectoryInfo(Dir)
Dim folderArray() As IO.DirectoryInfo = folderInfo.GetDirectories
Dim folderinDir As IO.DirectoryInfo
Dim fileArray() As IO.FileInfo = folderInfo.GetFiles()
Dim fileinFolder As IO.FileInfo

For Each fileinFolder In fileArray
My.Computer.FileSystem.CopyFile(fileinFolder.FullName, EvidenceSavePath & fileinFolder.Name)
Next

For Each folderinDir In folderArray
Dim subDir As String = folderinDir.FullName & "\"
Dim subFolder As New IO.DirectoryInfo(subDir)
Dim subArray() As IO.FileInfo = subFolder.GetFiles()
Dim subFile As IO.FileInfo
For Each subFile In subArray
My.Computer.FileSystem.CopyFile(subFile.FullName, EvidenceSavePath & subFile.Name)
Next
Next
Catch ex As Exception
End Try
End If
End If

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s