Thanks to Mike Caraker was good enough to share this idea and code with everyone. I think this is a really cool idea on how you can take information or issues from Vision and engage people outside of the software to correct something, in this case Mike’s code is prompting users to complete their daily timesheets when they logon to their computer, IF they haven’t done so already. Enjoy:

Sometimes it’s the little things that makes management happy.  Mine was concerned that people were not entering time on their timesheets in a timely manner (pun intended) and decided that the best way to get people to take care of this was to have their timesheet open each time they log on in the morning.  However, they only wanted it to open IF the employee had not entered any time for the previous day.  So they asked me to see if I could make this happen.  We use a vbs script for our logons so it was pretty easy to write a couple lines (below) that query our vision database to see if the logging on user had any time entered or not.  The tricky part was determining whether “yesterday” was a work day or not.  I did not go so far as to look up the holidays from Vision to skip those figuring that was going a bit overboard for this little project.  This process is a lot simpler for us since we use integrated logons.  Here’s the code I added to my vbs logon script;

 

‘section to run timesheet if no time was entered yesterday

Dim myStream, myConnection, myCommand, chkvar, strxml, wshShell, return, dayoff

Set myStream = CreateObject(“ADODB.Stream”)

Set myConnection = CreateObject(“ADODB.Connection”)

Set myCommand = CreateObject(“ADODB.Command”)

Set WshShell = WScript.CreateObject(“WScript.Shell”)

myConnection.Open “Provider=sqloledb;Server=SERVERNAME;Database=DATABASENAME;Trusted_Connection=yes;”

myCommand.ActiveConnection=myConnection

select case weekday(now())

case 1

                dayoff = 2

case 2

                dayoff = 3

case else

                dayoff = 1

end select

myCommand.CommandText=”SELECT tkDetail.RegHrs + tkDetail.OvtHrs + tkDetail.SpecialOvtHrs AS hours FROM tkDetail RIGHT OUTER JOIN SEUser ON tkDetail.Employee = SEUser.Employee WHERE (tkDetail.TransDate = CONVERT(varchar(10), DATEADD(d, -” & dayoff & “, GETDATE()), 21)) AND (SEUser.Username = upper(right(suser_name(),len(suser_name())-4))) FOR XML AUTO”

myStream.Open

myCommand.Properties(“Output Stream”) = myStream

myCommand.Properties(“xml root”) = “root”

myCommand.Execute ,,1024

myStream.Position = 0

myStream.Charset = “ISO-8859-1”

strxml = myStream.ReadText

chkvar = mid(strxml, 48, 4)

if chkvar = “root” then Return = WshShell.Run(“iexplore.exe http://visionapp/visionclient/DeltekVision.application“, 1)

myStream.close

 

Notice that you’ll have to substitute your Vision server name and databasename where the words are RED above.  You may have to tweak that command based on what version of SQL you use as well.  If you’d like some help incorporating this into your environment, just let me know.  I’ll be happy to help you as I can.  Good luck!

Leave a Reply

Your email address will not be published. Required fields are marked *