+ Post New Thread
Results 1 to 2 of 2
Coding Thread, Powershell - CMIS Student list in pipe in Coding and Web Development; Powershell command which will query a CMIS database and produce a list of students that can be shunted along the ...
  1. #1


    Join Date
    May 2009
    Posts
    2,914
    Thank Post
    259
    Thanked 770 Times in 585 Posts
    Rep Power
    270

    Powershell - CMIS Student list in pipe

    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.

    Code:
    <#
    
    .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="<your_server>"  
            $SQLDBName="<your_db>"
            $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
    Last edited by pcstru; 22nd July 2013 at 12:27 PM.

  2. #2


    Join Date
    May 2009
    Posts
    2,914
    Thank Post
    259
    Thanked 770 Times in 585 Posts
    Rep Power
    270
    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.

    Code:
    $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
    Last edited by pcstru; 23rd July 2013 at 08:55 AM.

SHARE:
+ Post New Thread

Similar Threads

  1. [ACS] CMIS Student Groups Slow?
    By pcstru in forum MIS Systems
    Replies: 1
    Last Post: 11th September 2012, 06:00 PM
  2. Student Contacts in CMIS Facility
    By mbyrew in forum MIS Systems
    Replies: 12
    Last Post: 16th September 2009, 03:32 PM
  3. Student Photos In SIMS - Your Comments Please
    By Benji1 in forum MIS Systems
    Replies: 11
    Last Post: 8th November 2007, 11:00 AM
  4. Staff Photo List in SIMS
    By markwilliamson2001 in forum MIS Systems
    Replies: 2
    Last Post: 1st October 2007, 03:52 PM
  5. Global Address list in OWA
    By adamt82 in forum Educational Software
    Replies: 0
    Last Post: 26th July 2007, 01:28 PM

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Posting Permissions

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