Powershell - CMIS Student list in pipe

[pcstru]

Powershell command which will query a CMIS database and produce a list of students that can be shunted along the pipeline.

 

i.e.

 

Get-DBStudent -MinYear 8 -MaxYear 8 -DataSet "2012/2013" | Export-Csv c:\tmp\yr8.csv -notype

 

Will produce a list of students in Year 8 for the Dataset 2012/2013.

 

<#

.SYNOPSIS

Retrieves Some Basic Student info from CMIS Database. Why? For increased integration with Active
directory we can pipe user information from the database which is the master source of student
and staff information. 

.PARAMETER MinYear, MaxYear, DataSet

The Min YearGroup, The Max YearGroup, The CMIS 'DataSet'

.EXAMPLE

Get-DBStudent -MinYear 8 -MaxYear 8 -DataSet "2012/2013" | Export-Csv c:\tmp\year8.csv

#>

Function Get-DBStudent {
  
   [CmdletBinding()]

   Param(
   [Parameter(Mandatory=$True,ValueFromPipeline=$True,ValueFromPipelinebyPropertyName=$True)]
      [string]$MinYear,
      [string]$MaxYear,
      [string]$DataSet='2012/2013'
   )

   PROCESS {

       # You need to set these appropraitely for your circumstances. SSPI means you will need permissions to 
       # access the database.
       # $SQLServer="mssql.ourschool.org.uk"  
       # $SQLDBName="CMIS_DATA"
       $SQLServer=""  
       $SQLDBName=""
       $SQLConn=New-Object System.Data.SQLClient.SQLConnection  
       $SQLCmd=New-Object System.Data.SQLClient.SQLCommand        
       $SQLConn.ConnectionString="Server=$SQLServer;Database=$SQLDBName;Integrated Security=SSPI"
       $SQLConn.Open()  
       

       $SQLCmd.CommandText= `
                   "SELECT st.StudentId, sp.Surname, sp.Forename, st.StudentId,st.Name,st.classgroupid, " + `
                   "       sp.EMailAddr, st.CourseId, sp.LeftSchool, sp.DateLeft, sp.DateEntry " + `
                   "  FROM STUDENTS st, NSTUPERSONAL sp " + `
                   "  WHERE st.SetId =   @dataSet and " + `
                "        st.SetId = sp.SetId and " + `
                "        st.StudentId = sp.StudentId and " + `
	                "        (st.CourseYear <=   @maxyearGrp) and " + `
 	                "        (st.CourseYear >=   @MinYearGrp) "

       $SQLCmd.Connection=$SQLConn
       $SQLCmd.Parameters.AddWithValue(   @maxyearGrp",$MaxYear) | Out-Null
       $SQLCmd.Parameters.AddWithValue(   @MinYearGrp",$MinYear) | Out-Null
       $SQLCmd.Parameters.AddWithValue(   @dataSet",$DataSet) | Out-Null
       $SQLReturn=$SQLcmd.ExecuteReader()
       
       while ($SQLReturn.Read())          
       {
       
           $obj = New-Object -typename PSObject

           $obj | Add-Member –membertype NoteProperty –name ForeName –value ($SQLReturn["ForeName"]) –passthru |
                  Add-Member –membertype NoteProperty –name Surname –value ($SQLReturn["Surname"]) –passthru |
                  Add-Member –membertype NoteProperty –name StudentId –value ($SQLReturn["StudentId"]) –passthru |
                  Add-Member –membertype NoteProperty –name FullName –value ($SQLReturn["Name"]) –passthru |
                  Add-Member –membertype NoteProperty –name EMail –value ($SQLReturn["EMailAddr"]) –passthru |
                  Add-Member –membertype NoteProperty –name TutorGroup –value ($SQLReturn["Classgroupid"]) –passthru |
                  Add-Member –membertype NoteProperty –name KeyStage –value ($SQLReturn["CourseId"]) –passthru |
                  Add-Member –membertype NoteProperty –name HasLeft –value ($SQLReturn["LeftSchool"]) –passthru |
                  Add-Member –membertype NoteProperty –name DateEntry –value ($SQLReturn["DateEntry"]) –passthru |
                  Add-Member –membertype NoteProperty –name DateLeft –value ($SQLReturn["DateLeft"])

           Write-Output $obj
           
       }

   }

}

# Example Use (Uncomment to test in ISE)
# Get-DBStudent -MinYear 8 -MaxYear 8 -DataSet "2012/2013" | Export-Csv c:\tmp\yr8.csv -notype

Edited July 22, 2013 by pcstru

[pcstru]

Can't edit to update. So ..

 

Converted to a Powershell Module. Added function to calculate a sensible default dataset. Added a function to pull out staff details.

 

$DBServer="yourdbserver.yourdomain.com"
$DBName="YourDatabaseName"

function Get-DefaultDataSet ($Offset=0) {

   [int]$NowYear = (Get-Date).Year
   [int]$NowMonth = (Get-Date).Month
   $NowDay = (Get-Date).Day
   
   if( $NowMonth -lt 9 ) {
       $DefDS = [string]($NowYear-1+$offset)+"/"+[string]($NowYear+$offset)
   } else {
       $DefDS = [string]($NowYear+$offset)+"/"+[string]($NowYear+1+$offset)
   }
   
   $DefDS
           
}

$DefDataSet=Get-DefaultDataSet

<#

.SYNOPSIS

Retrieves Basic Student info from CMIS Database

.PARAMETER MinYear, MaxYear

The Min YearGroup, The Max YearGroup

.EXAMPLE

Get-DBStudent | Export-Csv c:\tmp\student.csv

#>
Function Get-DBStudent {
  
   [CmdletBinding()]

   Param(
      [Parameter(ValueFromPipeline=$True,ValueFromPipelinebyPropertyName=$True)]
      [string]$MinYear=7,
      [string]$MaxYear=15,
      [string]$DataSet=$DefDataSet
   )

   PROCESS {

       $SQLServer=$DBServer  
       $SQLDBName=$DBName
       $SQLConn=New-Object System.Data.SQLClient.SQLConnection  
       $SQLCmd=New-Object System.Data.SQLClient.SQLCommand        
       $SQLConn.ConnectionString="Server=$SQLServer;Database=$SQLDBName;Integrated Security=SSPI"
       $SQLConn.Open()  

       try {        

           $SQLCmd.CommandText= `
                       "SELECT st.StudentId, sp.Surname, sp.Forename, st.StudentId,st.Name,st.classgroupid, " + `
                       "       sp.EMailAddr, st.CourseId, sp.LeftSchool, sp.DateLeft, sp.DateEntry " + `
                       "  FROM STUDENTS st, NSTUPERSONAL sp " + `
                       "  WHERE st.SetId =   @dataSet and " + `
   	                "        st.SetId = sp.SetId and " + `
                   "        st.StudentId = sp.StudentId and " + `
	                   "        (st.CourseYear <=   @maxyearGrp) and " + `
 	                   "        (st.CourseYear >=   @MinYearGrp) "

           $SQLCmd.Connection=$SQLConn
           $SQLCmd.Parameters.AddWithValue(   @maxyearGrp",$MaxYear) | Out-Null
           $SQLCmd.Parameters.AddWithValue(   @MinYearGrp",$MinYear) | Out-Null
           $SQLCmd.Parameters.AddWithValue(   @dataSet",$DataSet) | Out-Null
           $SQLReturn=$SQLcmd.ExecuteReader()
       
           while ($SQLReturn.Read()) {
       
               $obj = New-Object -typename PSObject
   
               $obj | Add-Member –membertype NoteProperty –name ForeName –value ($SQLReturn["ForeName"]) –passthru |
                      Add-Member –membertype NoteProperty –name Surname –value ($SQLReturn["Surname"]) –passthru |
                      Add-Member –membertype NoteProperty –name StudentId –value ($SQLReturn["StudentId"]) –passthru |
                      Add-Member –membertype NoteProperty –name FullName –value ($SQLReturn["Name"]) –passthru |
                      Add-Member –membertype NoteProperty –name EMail –value ($SQLReturn["EMailAddr"]) –passthru |
                      Add-Member –membertype NoteProperty –name TutorGroup –value ($SQLReturn["Classgroupid"]) –passthru |
                      Add-Member –membertype NoteProperty –name KeyStage –value ($SQLReturn["CourseId"]) –passthru |
                      Add-Member –membertype NoteProperty –name HasLeft –value ($SQLReturn["LeftSchool"]) –passthru |
                      Add-Member –membertype NoteProperty –name DateEntry –value ($SQLReturn["DateEntry"]) –passthru |
                      Add-Member –membertype NoteProperty –name DateLeft –value ($SQLReturn["DateLeft"])

               Write-Output $obj
           
           }

       } finally {        
           # Will clean close if ctrl+C is used        
           $SQLConn.Close()  
       }
       

   }

}



Function Get-DBStaff {
  
   [CmdletBinding()]

   Param(
   [Parameter(ValueFromPipelinebyPropertyName=$True)]
      [string]$DataSet=$DefDataSet
   )

   PROCESS {

       $SQLServer=$DBServer  
       $SQLDBName=$DBName
       $SQLConn=New-Object System.Data.SQLClient.SQLConnection  
       $SQLCmd=New-Object System.Data.SQLClient.SQLCommand        
       $SQLConn.ConnectionString="Server=$SQLServer;Database=$SQLDBName;Integrated Security=SSPI"
       $SQLConn.Open()  
       try {

           $SQLCmd.CommandText= `
                       "SELECT LecturerId, Surname, ForeName, Active, LectLeft, " + `
                       "       Email, StartDate, DateLeft, LineMngr, JobTitle from Lectdets" + `
                       "  WHERE SetId =   @dataSet "

           $SQLCmd.Connection=$SQLConn
           $SQLCmd.Parameters.AddWithValue(   @dataSet",$DataSet) | Out-Null
           $SQLReturn=$SQLcmd.ExecuteReader()
       
           while ($SQLReturn.Read()) {
       
               $obj = New-Object -typename PSObject

               $obj | Add-Member –membertype NoteProperty –name LecturerId –value ($SQLReturn["LecturerId"]) –passthru |
                      Add-Member –membertype NoteProperty –name Surname –value ($SQLReturn["Surname"]) –passthru |
                      Add-Member –membertype NoteProperty –name Forename –value ($SQLReturn["Forename"]) –passthru |
                      Add-Member –membertype NoteProperty –name Active –value ($SQLReturn["Active"]) –passthru |
                      Add-Member –membertype NoteProperty –name LectLeft –value ($SQLReturn["LectLeft"]) –passthru |
                      Add-Member –membertype NoteProperty –name Email –value ($SQLReturn["Email"]) –passthru |
                      Add-Member –membertype NoteProperty –name StartDate –value ($SQLReturn["StartDate"]) –passthru |
                      Add-Member –membertype NoteProperty –name LineMngr –value ($SQLReturn["LineMngr"]) –passthru |
                      Add-Member –membertype NoteProperty –name JobTitle –value ($SQLReturn["JobTitle"]) –passthru |
                      Add-Member –membertype NoteProperty –name DateLeft –value ($SQLReturn["DateLeft"])

               Write-Output $obj
           
           }
           
       } finally {        
           # Will clean close if ctrl+C is used        
           $SQLConn.Close()  
       }

   }

}




#Get-DBStudent -MinYear 8 -MaxYear 8 -DataSet "2012/2013" | Export-Csv c:\tmp\yr8.csv -notype

Edited July 23, 2013 by pcstru