# ============================================================================================== # NAME: set_ColleagueCRI # # AUTHOR: Jonathan Gose, CWI # DATE : 9/14/2018 # # COMMENT: Query W35_DC_DATA and update imported Student CRI information # Limited use of tab to format due to the use of Here-Strings (sorry) # ============================================================================================== # ---------------------------------------------------------------------------------------------- # SCRIPT GENERAL INFORMATION # ---------------------------------------------------------------------------------------------- $workingDir = Convert-Path . $appName = "Set Dual Credit CRI" $logFile = "$workingDir\$appName.log" # ---------------------------------------------------------------------------------------------- # ---------------------------------------------------------------------------------------------- # SCRIPT PROCESS INFORMATION # ---------------------------------------------------------------------------------------------- switch ($workingDir) { "" { } "" { } "" { } "" { } "" { } } # ---------------------------------------------------------------------------------------------- # ---------------------------------------------------------------------------------------------- # BEGIN LOGGING INFORMATION # ---------------------------------------------------------------------------------------------- if ( (Test-Path $logFile) -eq $false) { New-Item -Path $logFile -ItemType File | Out-Null } else { New-Item -Path $logFile -ItemType File -Force | Out-Null } "Start Time:`t$(Get-Date -Format "yyyyMMdd hh:mm")`n" >> $logFile # ---------------------------------------------------------------------------------------------- # ============================================================================================== # * FUNCTION: edit-w35DcData # * # * Connects to Specified ServerInstance/Database, Merge ELF_TGT_RESULTS with W35_DC_DATA # * $server - ServerInstance # * $database - Database # ============================================================================================== Function edit-w35DcData([string]$server, [string]$database) { $query = @" UPDATE W35_DC_DATA SET W35_DC_DATA.DC_DATA_STUDENT_NUMBER = ELF_TGT_RESULTS.ELFTGT_TGT_ID , W35_DC_DATA.DC_DATA_RECORD_STATUS = ELF_TGT_RESULTS.ELFTGT_RECORD_STATUS FROM ELF_TGT_RESULTS INNER Join W35_DC_DATA on ELF_TGT_RESULTS.ELFTGT_BATCH = W35_DC_DATA.DC_DATA_BATCH WHERE ( W35_DC_DATA.DC_DATA_BATCH != 'NULL' ) AND ( ELFTGT_TGT_ID != 'NULL' ) AND ( ELFTGT_TGT_FILE = 'PERSON' ) AND ( ELFTGT_INTER_FILE = 'INTER.BIODEMO' ) AND ( ELFTGT_TRANSACTION = W35_DC_DATA.DC_DATA_TRANSACTION ) "@ # Clear previous errors if any $Error.Clear() # Initialize queryReponse Array $queryResponse = @() # Perform requested SQL query try { $queryResponse = Invoke-SqlCmd -ServerInstance $server -Database $database -Query $query -Credential $credential } catch { $Error } # If SQL query successful, return student CH.CORR information if ( -not $Error ) { return $queryResponse } # If SQL query not successful log error for the student if ($Error) { "Error Occurred:`t$($Error)`n" >> $logFile } } # ============================================================================================== # ============================================================================================== # * FUNCTION: read-w35DcData # * # * Connects to Specified ServerInstance/Database, reads W35_DC_DATA to evaluate student information # * $server - ServerInstance # * $database - Database # ============================================================================================== Function read-w35DcData([string]$server, [string]$database) { $query = @" SELECT DC_DATA_REFERENCE_NUMBER AS 'referenceNumber' , DC_DATA_STUDENT_NUMBER AS 'studentNumber' , DC_DATA_RECORD_STATUS AS 'recordStatus' , DC_DATA_ACCEPTED_TERMS AS 'admitted' , DC_DATA_COMMENTS AS 'term' , DC_DATA_PROCESSED AS 'comments' FROM W35_DC_DATA WHERE ( DC_DATA_BATCH != 'NULL' ) AND ( DC_DATA_STUDENT_NUMBER != 'NULL' ) AND ( DC_DATA_COMMENTS LIKE '%FA' ) OR ( DC_DATA_COMMENTS LIKE '%SP' ) OR ( DC_DATA_COMMENTS LIKE '%SU' ) ORDER BY DC_DATA_STUDENT_NUMBER ASC "@ # Clear previous errors if any $Error.Clear() # Initialize queryReponse Array $queryResponse = @() # Perform requested SQL query try { $queryResponse = Invoke-SqlCmd -ServerInstance $server -Database $database -Query $query } catch { $Error } # If SQL query successful, return student CH.CORR information if ( -not $Error ) { return $queryResponse } # If SQL query not successful log error for the student if ($Error) { "Error Occurred:`t$($Error)`n" >> $logFile } } # ============================================================================================== # ============================================================================================== # * FUNCTION: read-mailing # * # * Connects to Specified ServerInstance/Database, query Mailing table for student ID # * $server - ServerInstance # * $database - Database # * $student - Student ID # ============================================================================================== Function read-mailing([string]$server, [string]$database, [string]$student) { $query = @" SELECT MAILING_ID FROM MAILING WHERE MAILING_ID = '$student' "@ # Clear previous errors if any $Error.Clear() # Initialize queryReponse Array $queryResponse = @() # Perform requested SQL query try { $queryResponse = Invoke-SqlCmd -ServerInstance $server -Database $database -Query $query } catch { $Error } # If SQL query successful, return student CH.CORR information if ( -not $Error ) { return $queryResponse } # If SQL query not successful log error for the student if ($Error) { "Error Occurred:`t$($Error)`n" >> $logFile } } # ============================================================================================== # ============================================================================================== # * FUNCTION: read-chCORR # * # * Connects to Specified ServerInstance/Database, reads collection of query CH.CORR # * $server - ServerInstance # * $database - Database # * $student - Student ID # ============================================================================================== Function read-chCORR([string]$server, [string]$database, [string]$student) { $query = @" SELECT POS , MAILING_ID , MAILING_CORR_RECEIVED , MAILING_CORR_RECEIVED_DATE , MAILING_CORR_RECVD_STATUS , MAILING_CORR_RECVD_COMMENT , MAILING_CORR_RECVD_ASGN_DT FROM CH_CORR WHERE MAILING_ID = '$student' "@ # Clear previous errors if any $Error.Clear() # Initialize queryReponse Array $queryResponse = @() # Perform requested SQL query try { $queryResponse = Invoke-SqlCmd -ServerInstance $server -Database $database -Query $query } catch { $Error } # If SQL query successful, return student CH.CORR information if ( -not $Error ) { return $queryResponse } # If SQL query not successful log error for the student if ($Error) { "Error Occurred:`t$($Error)`n" >> $logFile } } # ============================================================================================== # ============================================================================================== # * FUNCTION: add-mailing # * # * Connects to Specified ServerInstance/Database, create student mailing record # * $server - ServerInstance # * $database - Database # * $student - Student ID # ============================================================================================== Function add-mailing([string]$server, [string]$database, [string]$student) { $query = @" INSERT INTO MAILING ( MAILING_ID , MAILING_ADD_DATE , MAILING_ADD_OPERATOR ) VALUES ( '$student' , GETDATE() , 'DTADMIN' ) "@ # Clear previous errors if any $Error.Clear() # Initialize queryReponse Array $queryResponse = @() # Perform requested SQL query try { $queryResponse = Invoke-SqlCmd -ServerInstance $server -Database $database -Query $query -Credential $credential } catch { $Error } # If SQL query successful, return student CH.CORR information if ( -not $Error ) { return $queryResponse } # If SQL query not successful log error for the student if ($Error) { "Error Occurred:`t$($Error)`n" >> $logFile } } # ============================================================================================== # ============================================================================================== # * FUNCTION: add-chCORR # * # * Connects to Specified ServerInstance/Database, inserts new communication info into CH.CORR # * $server - ServerInstance # * $database - Database # * $student - Student ID # * $count - record count - to determine insert position # * $record - record to insert # * $term - Term comment for ARCNSNT # ============================================================================================== Function add-chCORR([string]$server, [string]$database, [string]$student, [string]$count, [string]$record, [string]$term) { Switch ($record) { "ARCNSNT" { $comment = $term $query = @" INSERT INTO CH_CORR ( MAILING_ID , POS , MAILING_CORR_RECEIVED , MAILING_CORR_RECEIVED_DATE , MAILING_CORR_RECVD_STATUS , MAILING_CORR_RECVD_COMMENT , MAILING_CORR_RECVD_ASGN_DT ) VALUES ( '$student' ,'$count' ,'$record' ,GETDATE() ,'R' ,'$comment' ,GETDATE() ) "@ } "RGCDCREG" { $comment = $null $query = @" INSERT INTO CH_CORR ( MAILING_ID , POS , MAILING_CORR_RECEIVED , MAILING_CORR_RECEIVED_DATE , MAILING_CORR_RECVD_STATUS , MAILING_CORR_RECVD_COMMENT , MAILING_CORR_RECVD_ASGN_DT ) VALUES ( '$student' ,'$count' ,'$record' ,GETDATE() ,'R' ,NULL ,GETDATE() ) "@ } } # Clear previous errors if any $Error.Clear() # Initialize queryReponse Array $queryResponse = @() # Perform requested SQL query try { $queryResponse = Invoke-SqlCmd -ServerInstance $server -Database $database -Query $query -Credential $credential } catch { $Error } # If SQL query successful, return student CH.CORR information if ( -not $Error ) { return $queryResponse } # If SQL query not successful log error for the student if ($Error) { "Error Occurred:`t$($Error)`n" >> $logFile } } # ============================================================================================== # ============================================================================================== # * FUNCTION: edit-w35DcDataAcceptedTerms # * # * Connects to Specified ServerInstance/Database, updates DC_DATA_ACCEPTED_TERMS # * $server - ServerInstance # * $database - Database # * $student - Student ID # ============================================================================================== Function edit-w35DcDataAcceptedTerms([string]$server, [string]$database, [string]$accepted, [string]$student) { $query = @" UPDATE W35_DC_DATA SET DC_DATA_ACCEPTED_TERMS = '$accepted' FROM W35_DC_DATA WHERE DC_DATA_STUDENT_NUMBER = '$student' "@ # Clear previous errors if any $Error.Clear() # Initialize queryReponse Array $queryResponse = @() # Perform requested SQL query try { $queryResponse = Invoke-SqlCmd -ServerInstance $server -Database $database -Query $query -Credential $credential } catch { $Error } # If SQL query successful, return student CH.CORR information if ( -not $Error ) { return $queryResponse } # If SQL query not successful log error for the student if ($Error) { "Error Occurred:`t$($Error)`n" >> $logFile } } # ============================================================================================== # ============================================================================================== # * FUNCTION: edit-w35DcDataProcessed # * # * Connects to Specified ServerInstance/Database, inserts new communication info into CH.CORR # * $server - ServerInstance # * $database - Database # * $student - Student ID # ============================================================================================== Function edit-w35DcDataProcessed([string]$server, [string]$database, [string]$student, [string]$comment) { $query = @" UPDATE W35_DC_DATA SET DC_DATA_PROCESSED = '$comment' FROM W35_DC_DATA WHERE DC_DATA_STUDENT_NUMBER = '$student' "@ # Clear previous errors if any $Error.Clear() # Initialize queryReponse Array $queryResponse = @() # Perform requested SQL query try { $queryResponse = Invoke-SqlCmd -ServerInstance $server -Database $database -Query $query -Credential $credential } catch { $Error } # If SQL query successful, return student CH.CORR information if ( -not $Error ) { return $queryResponse } # If SQL query not successful log error for the student if ($Error) { "Error Occurred:`t$($Error)`n" >> $logFile } } # ============================================================================================== # ---------------------------------------------------------------------------------------------- # Begin Script Execution # ---------------------------------------------------------------------------------------------- # ---------------------------------------------------------------------------------------------- # Execute Query/Export # ---------------------------------------------------------------------------------------------- # Load SQL Server Module Import-Module -Name SqlServer # W35_DC_STAT_DATA Update from ELF_TGT_RESULTS edit-w35DcData $collServer $collDatabase # W35_DC_STAT_DATA Query (evaluation for mailing, ch_corr and w35_dc_data updates) $w35DcData = read-w35DcData $collServer $collDatabase | Sort-Object studentNumber -Unique # Look up student mailing and create necessary records foreach ($student in $w35DcData) { $processed = $false "Student ID:`t$($student.studentNumber)" >> $logFile # Process Student Record Status switch ($student.recordStatus) { # Ambiguous New "AN" { $processed = $true $updateMSG = "Dual submitted - DUAL term accepted in Colleague" } # New "N" { $processed = $true $updateMSG = "Dual submitted - DUAL term accepted in Colleague" } # Ambiguous Merge "AM" { $processed = $true $updateMSG = "Dual already submitted - Student already has a DUAL program in Colleague" } # Merge "M" { $processed = $true $updateMSG = "Dual already submitted - Student already has a DUAL program in Colleague" } } # Proceed if Student Record New/Merged if ($processed -eq $true) { # Check for Student Mailing ID and create if not present $mailingData = read-mailing $collServer $collDatabase $student.studentNumber if (-Not $mailingData) { add-mailing $collServer $collDatabase $student.studentNumber "$($student.studentNumber)`tAdded MAILING." >> $logFile } # Check for Student CH.CORR (ARCNSNT and $chCorrData = read-chCORR $collServer $collDatabase $student.studentNumber # If current Term already accepted in CRI skip update $accepted = $false if ($chCorrData) { foreach ($pos in $chCorrData) { if ($pos.MAILING_CORR_RECVD_COMMENT -eq $student.term) { $accepted = $true edit-w35DcDataProcessed $collServer $collDatabase $student.studentNumber $updateMSG } } } # Current term not previously accepted create records if ($accepted -ne $true) { # Existing ch_corr, count existing positions and append new CRI if ($chCorrData) { # Add Term Acceptance $rCount = $chCorrData.POS.Count $rCount++ add-chCORR $collServer $collDatabase $student.studentNumber $rCount "ARCNSNT" $student.term if ( -not $Error ) { "$($student.studentNumber)`tARCNSNT added to CH_CORR: Postion $rCount" >> $logFile } else { $Error >> $logFile } # Add Term Registration $rCount++ add-chCORR $collServer $collDatabase $student.studentNumber $rCount "RGCDCREG" $null if ( -not $Error ) { "$($student.studentNumber)`tRGCDCREG addedto CH_CORR: Position: $rCount" >> $logFile } else { $Error >> $logFile } # Update DC_DATA_PROCESSED edit-w35DcDataProcessed $collServer $collDatabase $student.studentNumber $updateMSG } # No existing ch_corr, add new CRI if (-Not $chCorrData) { $rCount = 0 $rCount++ add-chCORR $collServer $collDatabase $student.studentNumber $rCount "ARCNSNT" $student.term if ( -not $Error ) { "$($student.studentNumber)`tARCNSNT added to CH_CORR: Postion $rCount" >> $logFile } $rCount++ add-chCORR $collServer $collDatabase $student.studentNumber $rCount "RGCDCREG" $null if ( -not $Error ) { "$($student.studentNumber)`tRGCDCREG addedto CH_CORR: Position: $rCount" >> $logFile } # Update DC_DATA_PROCESSED edit-w35DcDataProcessed $collServer $collDatabase $student.studentNumber $updateMSG } } "----------------------------------------------------------------------------------------------`n" >> $logFile } # Update DC_DATA_ACCEPTED_TERM to False else { edit-w35DcDataAcceptedTerms $collServer $collDatabase "false" $student.studentNumber } } # ---------------------------------------------------------------------------------------------- "End Time:`t$(Get-Date -Format "yyyyMMdd hh:mm")" >> $logFile # ----------------------------------------------------------------------------------------------- # End Script # -----------------------------------------------------------------------------------------------