My largest MySQL SELECT statement.

Dec 31
2009
Comments Off
?View Code MYSQL
 
SELECT id, name, fullname, birth1, death1, deathplace, is_divorced, 
wedding1, ann_name, MONTH( birth1 ) AS bday_month, 
DAYOFMONTH( birth1 ) AS bday_day, YEAR( birth1 ) AS event_year, 
DATE_FORMAT( birth1, '%m %d' ) AS event_date, 
DATE_FORMAT( birth1, '%Y-%m-%d' ) AS event_date_comp, 
DATE_FORMAT( birth1, '%b %d' ) AS event_date_display
FROM armer_individuals
WHERE (
MONTH( birth1 ) > '$c_month'
)
OR (
(
MONTH( birth1 ) = '$c_month'
)
AND (
DAYOFMONTH( birth1 ) > '$c_day'
)
)
ORDER BY event_date
)
UNION (
 
SELECT id, name, fullname, birth1, death1, deathplace, is_divorced, 
wedding1, ann_name, MONTH( wedding1 ) AS ann_month, 
DAYOFMONTH( wedding1 ) AS ann_day, YEAR( wedding1 ) AS ann_year, 
DATE_FORMAT( wedding1, '%m %d' ) AS event_date, 
DATE_FORMAT( wedding1, '%Y-%m-%d' ) AS event_date_comp, 
DATE_FORMAT( wedding1, '%b %d' ) AS event_date_display
FROM armer_individuals
WHERE (
(
MONTH( wedding1 ) > '$c_month'
)
OR (
(
MONTH( wedding1 ) = '$c_month'
)
AND (
DAYOFMONTH( wedding1 ) > '$c_day'
)
)
)
AND (
is_divorced <> 'yes'
)
AND (
YEAR( death1 ) = '0'
)
ORDER BY event_date
)
ORDER BY event_date
LIMIT 5 "

—————————————————–

And here is the result, happen to have no anniversaries in the nest 5 events.

Share
Written by Ross in: Tech | Tags: ,

Users.vbs v0.4

Jun 15
2009
Leave a
Comment

With help from a real programmer here is the much improved version…

*UPDATE* …added user input text boxes, no hand editing script variables

?View Code VB
 
' User account and folder creation script with individual user permissions added.
' ------------ Created by Ross Armer 6/15/2009-------------

 
Dim Message1, Message2, Message3, Message4
Dim Title, Text1, Text2
Dim homepath, teacher, profilePath, scriptPath
'------------------------user input boxes-----------------------
Message1 = "Teacher initial"           
Title = "Bulk student creation script - by Ross Armer"
Text1 = "User input canceled"
Text2 = "You entered:" & vbCrLf
teacher = InputBox(Message1, Title, "", 100, 100)
'-----
Message2 = "Students home path (***WITH trailing slash*** in UNC format)"           
Title = "Bulk student creation script - by Ross Armer"
Text1 = "User input canceled"
Text2 = "You entered:" & vbCrLf
homepath = InputBox(Message2, Title, "\\", 100, 100)
'--------
Message3 = "Students profile path (no trailing slash required)"           
Title = "Bulk student creation script - by Ross Armer"
Text1 = "User input canceled"
Text2 = "You entered:" & vbCrLf
profilePath = InputBox(Message3, Title, "\\", 100, 100)
'--------
Message4 = "Students logon script"           
Title = "Bulk student creation script - by Ross Armer"
Text1 = "User input canceled"
Text2 = "You entered:" & vbCrLf
scriptPath = InputBox(Message4, Title, "", 100, 100)
'----------------------------------------------------------------

Sub createUser(i)
 
 
	Set objRootDSE = GetObject("LDAP://rootDSE")
	Set objContainer = GetObject("LDAP://cn=Users," & objRootDSE.Get("defaultNamingContext"))
	Set objLeaf = objContainer.Create("User", "cn=" & teacher & i)
    objLeaf.Put "sAMAccountName", teacher & i
    objLeaf.Put "givenName", teacher & i
    objLeaf.Put "displayName", teacher & i
    objLeaf.Put "profilePath", profilePath
    objLeaf.Put "scriptPath", scriptPath
    objLeaf.Put "HomeDrive", "J:"
    objLeaf.Put "HomeDirectory", homepath & teacher & i
    objLeaf.SetInfo
 
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.CreateFolder(homepath & teacher & i)
    Set oShell = wscript.CreateObject("Wscript.Shell")
    oShell.Run "%COMSPEC% /c Echo Y| cacls " & homepath & teacher & i & " /t /e /g Administrators:F "& teacher & i & ":F", 2, True
 
End Sub
'----------------------------------------------------------------
For i = 100 To 130
	createUser i  
Next
For i = 200 To 230
    createUser i
Next
For i = 300 To 330
    createUser i
Next
For i = 400 To 430
    createUser i
Next
For i = 500 To 530
    createUser i
Next
For i = 600 To 630
    createUser i
Next
For i = 700 To 730
    createUser i
Next
'-----------------------------------------------------------------
WScript.Echo "All 7 hours created."
Share
Written by Ross in: Tech, Work | Tags: , ,

VBscript fun!

Jun 14
2009
Leave a
Comment

I’m not a programmer, but I can hack a mean script together!  The following creates 7 classes of 30 students each in Active Directory and also creates their home folder and assigns the correct permissions to that folder. Beats having to do it one at a time when reloading a school server!


I’ll be looking to clean it up with the use of functions or whatever you VBscript people call it.


' User account and folder creation script with individual user permissions added.
' ------------ Created by Ross Armer 6/14/2009-------------

' !!!!! BUG - Do not forget to change the homePath in the oShell.Run lines !!!!

Dim teacher,homepath,profilePath,scriptPath

' *** Provide the following variables for your network ***
teacher="a"
homepath="\\server\users$\"
profilePath="\\server\profiles$"
scriptPath="students.bat"
' ********************************************************

Set objRootDSE = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://cn=Users," & _
objRootDSE.Get("defaultNamingContext"))

For i = 100 To 130
Set objLeaf = objContainer.Create("User", "cn=" & teacher & i)
objLeaf.Put "sAMAccountName", teacher & i
objLeaf.Put "givenName", teacher & i
objLeaf.Put "displayName", teacher & i
objLeaf.Put "profilePath", profilePath
objLeaf.Put "scriptPath", scriptPath
objLeaf.Put "HomeDrive", "J:"
objLeaf.Put "HomeDirectory", homepath & teacher & i
objLeaf.SetInfo
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.CreateFolder(homepath & teacher & i)
Set oShell = wscript.CreateObject("Wscript.Shell")
oShell.Run "%COMSPEC% /c Echo Y| cacls \\server\users$\"& teacher & i & " /t /e /g Administrators:F "& teacher & i & ":F", 2, True
Next
For i = 200 To 230
Set objLeaf = objContainer.Create("User", "cn=" & teacher & i)
objLeaf.Put "sAMAccountName", teacher & i
objLeaf.Put "givenName", teacher & i
objLeaf.Put "displayName", teacher & i
objLeaf.Put "profilePath", profilePath
objLeaf.Put "scriptPath", scriptPath
objLeaf.Put "HomeDrive", "J:"
objLeaf.Put "HomeDirectory", homepath & teacher & i
objLeaf.SetInfo
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.CreateFolder(homepath & teacher & i)
Set oShell = wscript.CreateObject("Wscript.Shell")
oShell.Run "%COMSPEC% /c Echo Y| cacls \\server\users$\"& teacher & i & " /t /e /g Administrators:F "& teacher & i & ":F", 2, True
Next
For i = 300 To 330
Set objLeaf = objContainer.Create("User", "cn=" & teacher & i)
objLeaf.Put "sAMAccountName", teacher & i
objLeaf.Put "givenName", teacher & i
objLeaf.Put "displayName", teacher & i
objLeaf.Put "profilePath", profilePath
objLeaf.Put "scriptPath", scriptPath
objLeaf.Put "HomeDrive", "J:"
objLeaf.Put "HomeDirectory", homepath & teacher & i
objLeaf.SetInfo
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.CreateFolder(homepath & teacher & i)
Set oShell = wscript.CreateObject("Wscript.Shell")
oShell.Run "%COMSPEC% /c Echo Y| cacls \\server\users$\"& teacher & i & " /t /e /g Administrators:F "& teacher & i & ":F", 2, True
Next
For i = 400 To 430
Set objLeaf = objContainer.Create("User", "cn=" & teacher & i)
objLeaf.Put "sAMAccountName", teacher & i
objLeaf.Put "givenName", teacher & i
objLeaf.Put "displayName", teacher & i
objLeaf.Put "profilePath", profilePath
objLeaf.Put "scriptPath", scriptPath
objLeaf.Put "HomeDrive", "J:"
objLeaf.Put "HomeDirectory", homepath & teacher & i
objLeaf.SetInfo
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.CreateFolder(homepath & teacher & i)
Set oShell = wscript.CreateObject("Wscript.Shell")
oShell.Run "%COMSPEC% /c Echo Y| cacls \\server\users$\"& teacher & i & " /t /e /g Administrators:F "& teacher & i & ":F", 2, True
Next
For i = 500 To 530
Set objLeaf = objContainer.Create("User", "cn=" & teacher & i)
objLeaf.Put "sAMAccountName", teacher & i
objLeaf.Put "givenName", teacher & i
objLeaf.Put "displayName", teacher & i
objLeaf.Put "profilePath", profilePath
objLeaf.Put "scriptPath", scriptPath
objLeaf.Put "HomeDrive", "J:"
objLeaf.Put "HomeDirectory", homepath & teacher & i
objLeaf.SetInfo
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.CreateFolder(homepath & teacher & i)
Set oShell = wscript.CreateObject("Wscript.Shell")
oShell.Run "%COMSPEC% /c Echo Y| cacls \\server\users$\"& teacher & i & " /t /e /g Administrators:F "& teacher & i & ":F", 2, True
Next
For i = 600 To 630
Set objLeaf = objContainer.Create("User", "cn=" & teacher & i)
objLeaf.Put "sAMAccountName", teacher & i
objLeaf.Put "givenName", teacher & i
objLeaf.Put "displayName", teacher & i
objLeaf.Put "profilePath", profilePath
objLeaf.Put "scriptPath", scriptPath
objLeaf.Put "HomeDrive", "J:"
objLeaf.Put "HomeDirectory", homepath & teacher & i
objLeaf.SetInfo
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.CreateFolder(homepath & teacher & i)
Set oShell = wscript.CreateObject("Wscript.Shell")
oShell.Run "%COMSPEC% /c Echo Y| cacls \\server\users$\"& teacher & i & " /t /e /g Administrators:F "& teacher & i & ":F", 2, True
Next
For i = 700 To 730
Set objLeaf = objContainer.Create("User", "cn=" & teacher & i)
objLeaf.Put "sAMAccountName", teacher & i
objLeaf.Put "givenName", teacher & i
objLeaf.Put "displayName", teacher & i
objLeaf.Put "profilePath", profilePath
objLeaf.Put "scriptPath", scriptPath
objLeaf.Put "HomeDrive", "J:"
objLeaf.Put "HomeDirectory", homepath & teacher & i
objLeaf.SetInfo
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.CreateFolder(homepath & teacher & i)
Set oShell = wscript.CreateObject("Wscript.Shell")
oShell.Run "%COMSPEC% /c Echo Y| cacls \\server\users$\"& teacher & i & " /t /e /g Administrators:F "& teacher & i & ":F", 2, True
Next

WScript.Echo "All 7 hours created."

Share
Written by Ross in: Tech, Work | Tags: , ,
Get Adobe Flash playerPlugin by wpburn.com wordpress themes

Videos, Slideshows and Podcasts by Cincopa Wordpress Plugin