[sims] Bulk import staff photos

MicrodigitUK · November 13, 2010

I’ve been tasked with updating the staff photos in SIMS and I’ve come across an annoying problem.

There doesn’t seam to be a way to bulk import like you can for the students and individually loading 300 photos one by one isn’t an option.

I’ve got all of the photos in a directory with filenames based on the internal Sims database ID for the relevant member of staff.

As bulk import for staff isn’t built into SIMS has anyone written a vbscript to load the photos (in the non-supported way) directly into the photos table in the MSSQL database?

Or can some body help me write one as my scripting is a bit more like just copy and pasting.

rh91uk · November 14, 2010

@MicrodigitUK - I haven't tried it myself. Howabout trying to ask your LEA for the Business Objects documentation as that seems to be the only way!

MicrodigitUK · November 26, 2010

Well I’ve managed to hack a VBscript together that dose the job. Below I have posted the code for reference if anyone has the same problem.

I in no way encourage direct access to the Capita SIMS MSSQL database and this was only done as a last resort. Doing so to the live SIMS database could brake Capita’s terms and conditions. But backup copies of the MSSQL database are not included in this, so I recommend you only work on a backup SIMS MSSQL database.

By using this script you take full responsibility for any copyright or data protection issues. I do not provide any guarantees, warranty or support with this script. Use of the script is entirely at your own risk.

'Photos must be less than 10kb and must be JEPG files
'+------------------------------------------------------------------------+
'| Connection Setting for SIMS SQL Server                                 |
'+------------------------------------------------------------------------+
const SIMS_USER = "sa"
const SIMS_PASS = "*********"
const SIMS_SERVER = "10.0.0.15\sims2008"
const SIMS_DB = "sims"
'
Const ForReading = 1 
InDir = "H:\photos\SIMSstaffPhotoImport\"

' Change this to the SIMS Blank No Photo Silhouette PhotoID
Const NoPhotoSilhouette = 1697
'
'

'+------------------------------------------------------------------------+
'| Globals                                                                |
'+------------------------------------------------------------------------+

Dim SIMS_Connection
Dim objSIMSConnection 'SIMS database connection
Dim objSIMSRecordSet  'SIMS database connection
Dim strSQL	      	  'SQL Query
Const adCmdText = 1
Const adOpenDynamic = 2
Const adLockOptimistic = 3
Const adOpenKeyset = 1

'
'+------------------------------------------------------------------------+
'| Create Objects                                                         |
'+------------------------------------------------------------------------+
Set objSIMSConnection = CreateObject("ADODB.Connection")
Set objSIMSRecordset = CreateObject("ADODB.Recordset")
'
'+------------------------------------------------------------------------+
'| Set SQL Query Strings                                                       |
'+------------------------------------------------------------------------+
'strSQL = "SELECT * FROM dbo.vbs_adsync"
SIMS_Connection  = "DRIVER={SQL Server};SERVER=" &  SIMS_SERVER &  ";UID=" & SIMS_USER & ";PWD=" &  SIMS_PASS & ";" & "DATABASE=" & SIMS_DB &";"
'
'+------------------------------------------------------------------------+
'| Get Information from SIMS SQL Server                                	  |
'+------------------------------------------------------------------------+

objSIMSConnection.Open SIMS_Connection
'
'
'
'
Set fso = CreateObject("Scripting.FileSystemObject")  
For Each tFile In fso.GetFolder(InDir).Files 
  tName = tFile.Name
  tPhoto_Date = format(now(), "yyyy-mm-dd") & " " & format(time(), "hh:mm") & ":00"
  ' Get file extention from file name
  tExt = Right(tName, (Len(tName)-(InStrRev(tName,"."))))
  ' Check if file is a JPEG
  If tExt = "JPG" or tExt = "jpg"or tExt = "JPEG" or tExt = "jpeg" Then
  	'Gets the persons Name from the file by stripping the extention. 
  	tNameStrip = Left(tName, InStrRev(tName,".")-1)
  	If (Not(IsNumeric(tNameStrip))) then
	MsgBox "Can't find person in SIMS for " & tNameStrip
Else

  		objSIMSRecordset.Open "SELECT [sims_person].[photo_id] FROM [sims].[sims].[sims_person] WHERE [sims_person].[person_id] = " & tNameStrip, objSIMSConnection

  		If objSIMSRecordset.EOF then
		MsgBox "Can't find person in SIMS for " & tNameStrip
  		Else
		' If No Photo/No Photo Silhouette in SIMS add Photo or if there is a photo update it
		If IsNull(objSIMSRecordset.Fields(0)) or objSIMSRecordset.Fields(0) = NoPhotoSilhouette Then
			'No Photo in sims
			objSIMSRecordset.Close
			'Select no records from the photos table
			objSIMSRecordset.Open "SELECT [photo_id],[photo],[photo_date],[photo_status],[bmp_name] FROM [sims].[sims].[sims_photo] Where 1=0", objSIMSConnection, adOpenKeyset, adLockOptimistic, adCmdText
			'AddNew - new row To the recordset
			objSIMSRecordset.AddNew
			objSIMSRecordset("photo") = ReadByteArray(tFile.Path)
			objSIMSRecordset("photo_date") = tPhoto_Date
			objSIMSRecordset("photo_status") = "U"
			objSIMSRecordset("bmp_name") = tName
			'Store data To database
			objSIMSRecordset.Update
			'Get an ID of currently added row.
			AddPhotoDataRow = objSIMSRecordset("photo_id")
			objSIMSRecordset.Close
			'Link Photo to person
			objSIMSRecordset.Open "SELECT [person_id],[photo_id] FROM [sims].[sims].[sims_person] WHERE [sims_person].[person_id] = " & tNameStrip, objSIMSConnection, adOpenKeyset, adLockOptimistic, adCmdText
			objSIMSRecordset("photo_id") = AddPhotoDataRow
			'Store data To database
			objSIMSRecordset.Update
		Else
			tPhoto_ID = objSIMSRecordset.Fields(0)
			objSIMSRecordset.Close
			'Update Photo
			objSIMSRecordset.Open "SELECT [photo_id],[photo],[photo_date],[photo_status],[bmp_name] FROM [sims].[sims].[sims_photo] WHERE [sims_photo].[photo_id] = " & tPhoto_ID, objSIMSConnection, adOpenKeyset, adLockOptimistic, adCmdText
 		   		objSIMSRecordset("photo") = ReadByteArray(tFile.Path)
			objSIMSRecordset("photo_date") = tPhoto_Date
			objSIMSRecordset("photo_status") = "U"
			objSIMSRecordset("bmp_name") = tName
			'Store data To database
			objSIMSRecordset.Update
		End If
  		End If
  		objSIMSRecordset.Close
  	End If
  Else
  	'MsgBox "not a JPEG file"
  End if
Next

Function Format(vExpression, sFormat) 

set fmt = CreateObject("MSSTDFMT.StdDataFormat") 
fmt.Format = sFormat 

set rs = CreateObject("ADODB.Recordset") 
rs.Fields.Append "fldExpression", 12 ' adVariant 

rs.Open 
rs.AddNew 

set rs("fldExpression").DataFormat = fmt 
rs("fldExpression").Value = vExpression 

Format = rs("fldExpression").Value 

rs.close: Set rs = Nothing: Set fmt = Nothing 

End Function

'Stolen from http://www.ericphelps.com/q193998/index.htm 
Function ReadByteArray(strFileName) 
  Const adTypeBinary = 1 
  Dim bin 
  Set bin = CreateObject("ADODB.Stream") 
  bin.Type = adTypeBinary 
  bin.Open 
  bin.LoadFromFile strFileName 
  ReadByteArray = bin.Read 
End Function

Edited November 26, 2010 by MicrodigitUK

vikpaw · November 28, 2010

Nice. Wish i had the time to try it.

So how are you getting around the "Capita wont support you if you hack the db" issue?

MicrodigitUK · January 9, 2020

10 years on and I find my self at a school where I needed to bulk inport the staf photos once again. Problem was my old script was for Windows XP.

So just updated the broken code so it now works on Windows 10

'Photos must be less than 10kb and must be JEPG files
'+------------------------------------------------------------------------+
'| Connection Setting for SIMS SQL Server                                 |
'+------------------------------------------------------------------------+
const SIMS_USER = "sa"
const SIMS_PASS = "******"
const SIMS_SERVER = "SCHOOL-SQL-01\SIMS2016"
const SIMS_DB = "SIMS"
'
Const ForReading = 1 
InDir = "S:\Photographs\StaffPicsJPG-Resize\"

' Change this to the SIMS Blank No Photo Silhouette PhotoID
Const NoPhotoSilhouette = 0
'
'

'+------------------------------------------------------------------------+
'| Globals                                                                |
'+------------------------------------------------------------------------+

Dim SIMS_Connection
Dim objSIMSConnection 'SIMS database connection
Dim objSIMSRecordSet  'SIMS database connection
Dim strSQL	      	  'SQL Query
Const adCmdText = 1
Const adOpenDynamic = 2
Const adLockOptimistic = 3
Const adOpenKeyset = 1

'
'+------------------------------------------------------------------------+
'| Create Objects                                                         |
'+------------------------------------------------------------------------+
Set objSIMSConnection = CreateObject("ADODB.Connection")
Set objSIMSRecordset = CreateObject("ADODB.Recordset")
'
'+------------------------------------------------------------------------+
'| Set SQL Query Strings                                                       |
'+------------------------------------------------------------------------+
'strSQL = "SELECT * FROM dbo.vbs_adsync"
SIMS_Connection  = "DRIVER={SQL Server};SERVER=" &  SIMS_SERVER &  ";UID=" & SIMS_USER & ";PWD=" &  SIMS_PASS & ";" & "DATABASE=" & SIMS_DB &";"
'
'+------------------------------------------------------------------------+
'| Get Information from SIMS SQL Server                                	  |
'+------------------------------------------------------------------------+

objSIMSConnection.Open SIMS_Connection
'
'
'
'
Set fso = CreateObject("Scripting.FileSystemObject")  
For Each tFile In fso.GetFolder(InDir).Files 
  tName = tFile.Name
  tPhoto_Date = format(now(), "yyyy-MM-dd") & " " & format(time(), "hh:mm") & ":00"
  ' Get file extention from file name
  tExt = Right(tName, (Len(tName)-(InStrRev(tName,"."))))
  ' Check if file is a JPEG
  If tExt = "JPG" or tExt = "jpg"or tExt = "JPEG" or tExt = "jpeg" Then
  	'Gets the persons Name from the file by stripping the extention. 
  	tNameStrip = Left(tName, InStrRev(tName,".")-1)
  	If (Not(IsNumeric(tNameStrip))) then
	MsgBox "Can't find person in SIMS for " & tNameStrip
Else

  		objSIMSRecordset.Open "SELECT [sims_person].[photo_id] FROM [sims].[sims].[sims_person] WHERE [sims_person].[person_id] = " & tNameStrip, objSIMSConnection

  		If objSIMSRecordset.EOF then
		MsgBox "Can't find person in SIMS for " & tNameStrip
  		Else
		' If No Photo/No Photo Silhouette in SIMS add Photo or if there is a photo update it
		If IsNull(objSIMSRecordset.Fields(0)) or objSIMSRecordset.Fields(0) = NoPhotoSilhouette Then
			'No Photo in sims
			objSIMSRecordset.Close
			'Select no records from the photos table
			objSIMSRecordset.Open "SELECT [photo_id],[photo],[photo_date],[photo_status],[bmp_name] FROM [sims].[sims].[sims_photo] Where 1=0", objSIMSConnection, adOpenKeyset, adLockOptimistic, adCmdText
			'AddNew - new row To the recordset
			objSIMSRecordset.AddNew
			objSIMSRecordset("photo") = ReadByteArray(tFile.Path)
			objSIMSRecordset("photo_date") = tPhoto_Date
			objSIMSRecordset("photo_status") = "U"
			objSIMSRecordset("bmp_name") = tName
			'Store data To database
			objSIMSRecordset.Update
			'Get an ID of currently added row.
			AddPhotoDataRow = objSIMSRecordset("photo_id")
			objSIMSRecordset.Close
			'Link Photo to person
			objSIMSRecordset.Open "SELECT [person_id],[photo_id] FROM [sims].[sims].[sims_person] WHERE [sims_person].[person_id] = " & tNameStrip, objSIMSConnection, adOpenKeyset, adLockOptimistic, adCmdText
			objSIMSRecordset("photo_id") = AddPhotoDataRow
			'Store data To database
			objSIMSRecordset.Update
		Else
			tPhoto_ID = objSIMSRecordset.Fields(0)
			objSIMSRecordset.Close
			'Update Photo
			objSIMSRecordset.Open "SELECT [photo_id],[photo],[photo_date],[photo_status],[bmp_name] FROM [sims].[sims].[sims_photo] WHERE [sims_photo].[photo_id] = " & tPhoto_ID, objSIMSConnection, adOpenKeyset, adLockOptimistic, adCmdText
 		   		objSIMSRecordset("photo") = ReadByteArray(tFile.Path)
			objSIMSRecordset("photo_date") = tPhoto_Date
			objSIMSRecordset("photo_status") = "U"
			objSIMSRecordset("bmp_name") = tName
			'Store data To database
			objSIMSRecordset.Update
		End If
  		End If
  		objSIMSRecordset.Close
  	End If
  Else
  	'MsgBox "not a JPEG file"
  End if
Next

Function Format(vExpression, sFormat)

Dim sb
Set sb = createobject("System.Text.StringBuilder")

If isDate(vExpression) Then
	Call sb.AppendFormat ("{0:" & sFormat & "}", cdate(vExpression))
	Format = sb.ToString()
Else
	Call sb.AppendFormat ("{0:" & sFormat & "}", vExpression)
	Format = sb.ToString()
End If

End Function

'Stolen from http://www.ericphelps.com/q193998/index.htm 
Function ReadByteArray(strFileName) 
  Const adTypeBinary = 1 
  Dim bin 
  Set bin = CreateObject("ADODB.Stream") 
  bin.Type = adTypeBinary 
  bin.Open 
  bin.LoadFromFile strFileName 
  ReadByteArray = bin.Read 
End Function

[sims] Bulk import staff photos

Recommended Posts

MicrodigitUK

rh91uk

MicrodigitUK

vikpaw

MicrodigitUK

Create an account or sign in to comment

Create an account

Sign in

Recent Threads

Sage 200 budget holders - test something for me?

Bromcom Support

My Fluke LinkRunner has a fault...

Home

Forums

What's New