Line 1: Imports Microsoft.VisualBasic
Line 2: Imports System.Data
Line 3: Imports System.Data.SqlClient
Line 4: Imports System.IO
Line 5: Imports dsFTETableAdapters
Line 6: Imports dsTuitionFeesTableAdapters
Line 7: Imports dsSURDSTableAdapters
Line 8:
Line 9: Public Class clsProcessSURDSFile
Line 10: Inherits System.Web.UI.Page
Line 11: Public aFieldLength As New ArrayList
Line 12: Public aFieldNames As New ArrayList
Line 13: Public aFieldDisplayNames As New ArrayList
Line 14: Public aFieldType As New ArrayList
Line 15: Public isError As String
Line 16:
Line 17: 'Public Const kstrRootPath As String = "C:\BigBird\DOTNET\DHE_DB\Import\Files\"
Line 18: Public Const kstrRootPath As String = "C:\SURDSDATA\"
Line 19:
Line 20: Public Const kstrEmailSignOff As String = "Maggie.Yang@dhe.state.co.us"
Line 21:
Line 22: Public FileRowCount, FileErrorCount, FileWarningCount As Integer
Line 23:
Line 24: '======================================================
Line 25: '======================================================
Line 26: '======================================================
Line 27: '======================================================
Line 28:
Line 29: Sub LoadFieldDefinitions(ByVal strType As String)
Line 30:
Line 31: isError = ""
Line 32:
Line 33: Try
Line 34: ' Load SURDS field list for specified file from table
Line 35: Dim taFields As New ImportFileFieldTableAdapter
Line 36: Dim dtData As DataTable
Line 37: dtData = taFields.GetData(strType)
Line 38: If dtData.Rows.Count = 0 Then
Line 39: isError = "File Type not valid: " & strType
Line 40: End If
Line 41: For x = 0 To dtData.Rows.Count - 1
Line 42: Call AddFieldDefinition(dtData.Rows(x).Item("fieldname").ToString, _
Line 43: dtData.Rows(x).Item("max_length").ToString, _
Line 44: dtData.Rows(x).Item("system_type_id").ToString, _
Line 45: dtData.Rows(x).Item("displayname").ToString)
Line 46:
Line 47: Next
Line 48: Catch ex As Exception
Line 49: isError = ex.Message
Line 50: End Try
Line 51:
Line 52: End Sub
Line 53:
Line 54: Sub AddFieldDefinition(ByVal strName As String, ByVal strLength As String, ByVal strType As String, ByVal strDisplay As String)
Line 55:
Line 56: aFieldNames.Add(strName)
Line 57: aFieldLength.Add(strLength)
Line 58: aFieldType.Add(strType)
Line 59: aFieldDisplayNames.Add(strDisplay)
Line 60:
Line 61: End Sub
Line 62:
Line 63: Sub ClearData(ByVal strType As String, ByVal strInst As String, _
Line 64: ByVal strTerm As String, ByVal strYear As String)
Line 65:
Line 66: 'Try
Line 67:
Line 68: 'Select Case UCase(strType)
Line 69: ' Case "1"
Line 70: 'Dim taENR As New StudentEnrollmentTableAdapter
Line 71: 'taENR.DeleteStudentEnrollment(strType, strInst, strTerm, strYear)
Line 72: ' Case "2"
Line 73: 'Dim taUAF As New UAFTableAdapter
Line 74: 'taUAF.DeleteUAF(strType, strInst, strTerm, strYear)
Line 75: ' Case "3"
Line 76: 'Dim taDEG As New DegreeTableAdapter
Line 77: 'taDEG.DeleteDeg(strType, strInst, strTerm, strYear)
Line 78: ' Case "4"
Line 79: 'Dim taFA As New FinancialAidTableAdapter
Line 80: 'taFA.DeleteFA(strType, strInst, strTerm, strYear)
Line 81: ' Case "5"
Line 82: 'Dim taStCrs As New StudentCourseTableAdapter
Line 83: 'taStCrs.DeleteStudentCourse(strType, strInst, strTerm, strYear)
Line 84: ' Case "6"
Line 85: 'Dim taED As New EducatorTableAdapter
Line 86: 'taED.DeleteEducator(strType, strInst, strTerm, strYear)
Line 87: ' Case "7"
Line 88: 'Dim taRem As New RemedialCourseTableAdapter
Line 89: 'taRem.DeleteRemedial(strType, strInst, strTerm, strYear)
Line 90: ' Case "A"
Line 91: 'Dim taVer As New VerificationTableAdapter
Line 92: 'taVer.DeleteVerification(strType, strInst, strTerm, strYear)
Line 93: ' Case "B"
Line 94: 'Dim taVer As New VerificationQueryTableAdapter
Line 95: 'taVer.DeleteVerificationQuery(strType, strInst, strTerm, strYear)
Line 96: 'End Select
Line 97: '
Line 98: 'Catch ex As Exception
Line 99: ' isError = ex.Message
Line 100: 'End Try
Line 101:
Line 102: Try
Line 103:
Line 104: Dim taRpt As New FileReportingTableAdapter
Line 105: Dim strResult As String = ""
Line 106:
Line 107: ' Only run reports if 30 or fewer errors
Line 108: If FileErrorCount > 30 Then
Line 109: Exit Sub
Line 110: End If
Line 111:
Line 112: 'Set up Connection object and Connection String for a SQL Client
Line 113: Dim SQLCon As New SqlClient.SqlConnection
Line 114: Dim SQLCmd As New SqlClient.SqlCommand
Line 115: SQLCon.ConnectionString = ConfigurationManager.ConnectionStrings.Item("SURDSConn").ConnectionString
Line 116: SQLCon.Open()
Line 117:
Line 118: 'Calling Stored Procedures with Exec command
Line 119:
Line 120: Select Case UCase(strType)
Line 121: Case "1"
Line 122: SQLCmd.CommandText = "proc_StudentEnrollment_Delete"
Line 123: Case "2"
Line 124: SQLCmd.CommandText = "proc_UAF_Delete"
Line 125: Case "3"
Line 126: SQLCmd.CommandText = "proc_Degree_Delete"
Line 127: Case "4"
Line 128: SQLCmd.CommandText = "proc_FinancialAid_Delete"
Line 129: Case "5"
Line 130: SQLCmd.CommandText = "proc_StudentCourse_Delete"
Line 131: Case "6"
Line 132: SQLCmd.CommandText = "proc_TeacherEd_Delete"
Line 133: Case "7"
Line 134: SQLCmd.CommandText = "proc_RemedialCourse_Delete"
Line 135: Case "A"
Line 136: SQLCmd.CommandText = "proc_Verification_Delete"
Line 137: Case "B"
Line 138: SQLCmd.CommandText = "proc_VerificationQuery_Delete"
Line 139: End Select
Line 140:
Line 141: SQLCmd.CommandType = CommandType.StoredProcedure 'Setup Command Type
Line 142: SQLCmd.Connection = SQLCon 'Active Connection
Line 143:
Line 144: SQLCmd.CommandTimeout = 0
Line 145:
Line 146: SQLCmd.Parameters.AddWithValue("SubmitRecordType", strType)
Line 147: SQLCmd.Parameters.AddWithValue("SubmitInst", strInst)
Line 148: SQLCmd.Parameters.AddWithValue("SubmitTerm", strTerm)
Line 149: SQLCmd.Parameters.AddWithValue("SubmitYear", strYear)
Line 150:
Line 151: strResult = SQLCmd.ExecuteScalar()
Line 152:
Line 153: 'Select Case UCase(strType)
Line 154: 'Case "1"
Line 155: ' taRpt.FileReport_STUENR(strInst, strTerm, strYear)
Line 156: 'Case "2"
Line 157: ' taRpt.FileReport_UAF(strInst, strTerm, strYear)
Line 158: 'Case "3"
Line 159: ' taRpt.FileReport_DEG(strInst, strTerm, strYear)
Line 160: 'Case "4"
Line 161: ' taRpt.FileReport_FA(strInst, strTerm, strYear)
Line 162: 'Case "5"
Line 163: ' taRpt.FileReport_STUCOURSE(strInst, strTerm, strYear)
Line 164: 'Case "6"
Line 165: ' taRpt.FileReport_ED(strInst, strTerm, strYear)
Line 166: 'Case "7"
Line 167: ' taRpt.FileReport_REM(strInst, strTerm, strYear)
Line 168: ' Case "A"
Line 169: ' taRpt.FileReport_VER(strInst, strTerm, strYear)
Line 170: ' Case "B"
Line 171: ' taRpt.FileReport_VERQ(strInst, strTerm, strYear)
Line 172: 'End Select
Line 173:
Line 174: Catch ex As Exception
Line 175: isError = ex.Message
Line 176: Dim taValErroLog As New ValErrorLogTableAdapter
Line 177: taValErroLog.InsertData(strType, strInst, strTerm, strYear, 9, Left(ex.Message, 100))
Line 178: End Try
Line 179:
Line 180: End Sub
Line 181:
Line 182: Function InsertProcedure(ByVal strType As String) As String
Line 183: InsertProcedure = "Invalid"
Line 184: Select Case UCase(strType)
Line 185: Case "1"
Line 186: InsertProcedure = "proc_StudentEnrollment_Insert"
Line 187: Case "2"
Line 188: InsertProcedure = "proc_UAF_Insert"
Line 189: Case "3"
Line 190: InsertProcedure = "proc_Degree_Insert"
Line 191: Case "4"
Line 192: InsertProcedure = "proc_FinancialAid_Insert"
Line 193: Case "5"
Line 194: InsertProcedure = "proc_StudentCourse_Insert"
Line 195: Case "6"
Line 196: InsertProcedure = "proc_TeacherEd_Insert"
Line 197: Case "7"
Line 198: InsertProcedure = "proc_RemedialCourse_Insert"
Line 199: Case "A"
Line 200: InsertProcedure = "proc_Verification_Insert"
Line 201: Case "B"
Line 202: InsertProcedure = "proc_VerificationQuery_Insert"
Line 203: End Select
Line 204:
Line 205: End Function
Line 206:
Line 207: Function ValidateData(ByVal strType As String, ByVal strInst As String, _
Line 208: ByVal strTerm As String, ByVal strYear As String) As String
Line 209:
Line 210: Try
Line 211:
Line 212: Dim taValidate As New ValidationTableAdapter
Line 213: Dim strResult As String = ""
Line 214:
Line 215: 'Set up Connection object and Connection String for a SQL Client
Line 216: Dim SQLCon As New SqlClient.SqlConnection
Line 217: Dim SQLCmd As New SqlClient.SqlCommand
Line 218: SQLCon.ConnectionString = ConfigurationManager.ConnectionStrings.Item("SURDSConn").ConnectionString
Line 219: SQLCon.Open()
Line 220:
Line 221: 'Calling Stored Procedures with Exec command
Line 222:
Line 223: Select Case UCase(strType)
Line 224: Case "1"
Line 225: SQLCmd.CommandText = "proc_VALIDATE_FILE_STUDENTENROLLMENT"
Line 226: Case "2"
Line 227: SQLCmd.CommandText = "proc_VALIDATE_FILE_UNDERGRADAPPLICANT"
Line 228: Case "3"
Line 229: SQLCmd.CommandText = "proc_VALIDATE_FILE_DEGREE"
Line 230: Case "4"
Line 231: SQLCmd.CommandText = "proc_VALIDATE_FILE_FINANCIALAID"
Line 232: Case "5"
Line 233: SQLCmd.CommandText = "proc_VALIDATE_FILE_STUDENTCOURSE"
Line 234: Case "6"
Line 235: SQLCmd.CommandText = "proc_VALIDATE_FILE_TEACHERED"
Line 236: Case "7"
Line 237: SQLCmd.CommandText = "proc_VALIDATE_FILE_REMEDIALCOURSE"
Line 238: Case "A"
Line 239: SQLCmd.CommandText = "proc_VALIDATE_FILE_VERIFICATION"
Line 240: Case "B"
Line 241: SQLCmd.CommandText = "proc_VALIDATE_FILE_VERIFICATIONQUERY"
Line 242: End Select
Line 243:
Line 244: SQLCmd.CommandType = CommandType.StoredProcedure 'Setup Command Type
Line 245: SQLCmd.Connection = SQLCon 'Active Connection
Line 246:
Line 247: SQLCmd.CommandTimeout = 0
Line 248:
Line 249: SQLCmd.Parameters.AddWithValue("InstitutionCode", strInst)
Line 250: SQLCmd.Parameters.AddWithValue("ReportTerm", strTerm)
Line 251: SQLCmd.Parameters.AddWithValue("ReportYear", strYear)
Line 252:
Line 253: strResult = SQLCmd.ExecuteScalar()
Line 254:
Line 255:
Line 256: 'Select Case UCase(strType)
Line 257: ' Case "1"
Line 258: 'strResult = taValidate.Validate_STUENR(strInst, strTerm, strYear)
Line 259: ' Case "2"
Line 260: 'strResult = taValidate.Validate_UAF(strInst, strTerm, strYear)
Line 261: ' Case "3"
Line 262: 'strResult = taValidate.Validate_DEG(strInst, strTerm, strYear)
Line 263: ' Case "4"
Line 264: 'strResult = taValidate.Validate_FA(strInst, strTerm, strYear)
Line 265: ' Case "5"
Line 266: 'strResult = taValidate.Validate_STUCOURSE(strInst, strTerm, strYear)
Line 267: ' Case "6"
Line 268: 'strResult = taValidate.Validate_ED(strInst, strTerm, strYear)
Line 269: ' Case "7"
Line 270: 'strResult = taValidate.Validate_REM(strInst, strTerm, strYear)
Line 271: ' Case "A"
Line 272: 'strResult = taValidate.Validate_VER(strInst, strTerm, strYear)
Line 273: ' Case "B"
Line 274: 'strResult = taValidate.Validate_VERQ(strInst, strTerm, strYear)
Line 275: 'End Select
Line 276:
Line 277: 'Dim dtData As New DataTable
Line 278: 'dtData = taValidate.GetData(strType, strInst, strTerm, strYear)
Line 279:
Line 280: 'Call LoadStatistics(strType, strInst, strTerm, strYear)
Line 281:
Line 282: ValidateData = strResult
Line 283:
Line 284: Catch ex As Exception
Line 285: isError = ex.Message
Line 286: ValidateData = isError
Line 287: Dim taValErroLog As New ValErrorLogTableAdapter
Line 288: taValErroLog.InsertData(strType, strInst, strTerm, strYear, 9, Left(ex.Message, 100))
Line 289: End Try
Line 290:
Line 291: End Function
Line 292:
Line 293: Sub LoadStatistics(ByVal strType As String, ByVal strInst As String, _
Line 294: ByVal strTerm As String, ByVal strYear As String)
Line 295: Dim taFile As New ImportFileTableAdapter
Line 296: Dim dtData As New DataTable
Line 297: dtData = taFile.GetCurrentByPrimary(strType, strInst, strTerm, strYear)
Line 298: If dtData.Rows.Count > 0 Then
Line 299: FileRowCount = dtData.Rows(0).Item("RecordCount").ToString
Line 300: FileErrorCount = dtData.Rows(0).Item("ErrorCount").ToString
Line 301: FileWarningCount = dtData.Rows(0).Item("WarningCount").ToString
Line 302: End If
Line 303: End Sub
Line 304:
Line 305: Sub FileReporting(ByVal strType As String, ByVal strInst As String, _
Line 306: ByVal strTerm As String, ByVal strYear As String)
Line 307:
Line 308: Try
Line 309:
Line 310: Dim taRpt As New FileReportingTableAdapter
Line 311: Dim strResult As String = ""
Line 312:
Line 313: ' Only run reports if 30 or fewer errors
Line 314: ' 8/21/13 - added exception for FA(4) within FileReporting sub
Line 315: If FileErrorCount > 30 And strType <> "4" Then
Line 316: Exit Sub
Line 317: End If
Line 318:
Line 319: 'Set up Connection object and Connection String for a SQL Client
Line 320: Dim SQLCon As New SqlClient.SqlConnection
Line 321: Dim SQLCmd As New SqlClient.SqlCommand
Line 322: SQLCon.ConnectionString = ConfigurationManager.ConnectionStrings.Item("SURDSConn").ConnectionString
Line 323: SQLCon.Open()
Line 324:
Line 325: 'Calling Stored Procedures with Exec command
Line 326:
Line 327: Select Case UCase(strType)
Line 328: Case "1"
Line 329: SQLCmd.CommandText = "proc_Reports_StudentEnrollmentReportDetail_Insert"
Line 330: Case "2"
Line 331: SQLCmd.CommandText = "proc_Reports_UAFReportDetail_Insert"
Line 332: Case "3"
Line 333: SQLCmd.CommandText = "proc_Reports_DegreeReportDetail_Insert"
Line 334: Case "4"
Line 335: SQLCmd.CommandText = "proc_Reports_FinancialAidReportDetail_Insert"
Line 336: Case "5"
Line 337: SQLCmd.CommandText = "proc_Reports_StudentCourseReportDetail_Insert"
Line 338: Case "6"
Line 339: SQLCmd.CommandText = "proc_Reports_TeacherEdReportDetail_Insert"
Line 340: Case "7"
Line 341: SQLCmd.CommandText = "proc_Reports_RemedialReportDetail_Insert"
Line 342: Case "A"
Line 343: SQLCmd.CommandText = "proc_Reports_VerificationReportDetail_Insert"
Line 344: Case "B"
Line 345: SQLCmd.CommandText = "proc_Reports_VerificationQueryReportDetail_Insert"
Line 346: End Select
Line 347:
Line 348: SQLCmd.CommandType = CommandType.StoredProcedure 'Setup Command Type
Line 349: SQLCmd.Connection = SQLCon 'Active Connection
Line 350:
Line 351: SQLCmd.CommandTimeout = 0
Line 352:
Line 353: SQLCmd.Parameters.AddWithValue("InstitutionCode", strInst)
Line 354: SQLCmd.Parameters.AddWithValue("ReportTerm", strTerm)
Line 355: SQLCmd.Parameters.AddWithValue("ReportYear", strYear)
Line 356:
Line 357: strResult = SQLCmd.ExecuteScalar()
Line 358:
Line 359: 'Select Case UCase(strType)
Line 360: 'Case "1"
Line 361: ' taRpt.FileReport_STUENR(strInst, strTerm, strYear)
Line 362: 'Case "2"
Line 363: ' taRpt.FileReport_UAF(strInst, strTerm, strYear)
Line 364: 'Case "3"
Line 365: ' taRpt.FileReport_DEG(strInst, strTerm, strYear)
Line 366: 'Case "4"
Line 367: ' taRpt.FileReport_FA(strInst, strTerm, strYear)
Line 368: 'Case "5"
Line 369: ' taRpt.FileReport_STUCOURSE(strInst, strTerm, strYear)
Line 370: 'Case "6"
Line 371: ' taRpt.FileReport_ED(strInst, strTerm, strYear)
Line 372: 'Case "7"
Line 373: ' taRpt.FileReport_REM(strInst, strTerm, strYear)
Line 374: ' Case "A"
Line 375: ' taRpt.FileReport_VER(strInst, strTerm, strYear)
Line 376: ' Case "B"
Line 377: ' taRpt.FileReport_VERQ(strInst, strTerm, strYear)
Line 378: 'End Select
Line 379:
Line 380: Catch ex As Exception
Line 381: isError = ex.Message
Line 382: Dim taValErroLog As New ValErrorLogTableAdapter
Line 383: taValErroLog.InsertData(strType, strInst, strTerm, strYear, 9, Left(ex.Message, 100))
Line 384: End Try
Line 385:
Line 386: End Sub
Line 387:
Line 388: Function FilesInProgress() As Integer
Line 389: Try
Line 390: FilesInProgress = 0
Line 391: Dim tafiles As New ImportFileTableAdapter
Line 392: Dim dttable As New DataTable
Line 393: dttable = tafiles.GetNumInProgress
Line 394: FilesInProgress = dttable.Rows(0).Item("NumInProgress").ToString
Line 395:
Line 396: Catch ex As Exception
Line 397:
Line 398: End Try
Line 399:
Line 400: End Function
Line 401: End Class
Line 402:
|