+ Post New Thread
Results 1 to 4 of 4
MIS Systems Thread, Bulk import staff photos in Technical; Iíve been tasked with updating the staff photos in SIMS and Iíve come across an annoying problem. There doesnít seam ...
  1. #1
    MicrodigitUK's Avatar
    Join Date
    May 2007
    Location
    Wiltshire
    Posts
    332
    Thank Post
    37
    Thanked 54 Times in 50 Posts
    Rep Power
    24

    Bulk import staff photos

    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.


  2. #2
    rh91uk's Avatar
    Join Date
    Sep 2008
    Location
    UK
    Posts
    876
    Thank Post
    137
    Thanked 132 Times in 114 Posts
    Rep Power
    35
    @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!

  3. #3
    MicrodigitUK's Avatar
    Join Date
    May 2007
    Location
    Wiltshire
    Posts
    332
    Thank Post
    37
    Thanked 54 Times in 50 Posts
    Rep Power
    24
    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.

    Code:
    '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
    Last edited by MicrodigitUK; 26th November 2010 at 07:29 PM.

  4. #4

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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?

SHARE:
+ Post New Thread

Similar Threads

  1. italc bulk import?
    By AntiThesis in forum Network and Classroom Management
    Replies: 2
    Last Post: 18th November 2011, 02:13 PM
  2. [SIMS] Bulk import staff email address
    By matt40k in forum MIS Systems
    Replies: 16
    Last Post: 31st October 2010, 10:59 PM
  3. Replies: 4
    Last Post: 6th July 2010, 10:04 AM
  4. Anyone know how to bulk import into glpi
    By reggiep in forum Network and Classroom Management
    Replies: 3
    Last Post: 14th May 2010, 07:28 AM
  5. Data import (Photos) into eclipse.net from Cmis Facility
    By robk in forum Educational Software
    Replies: 0
    Last Post: 12th May 2010, 06:12 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •