Line 1: Imports System
Line 2: Imports System.Web
Line 3: Imports System.Web.UI
Line 4: Imports System.Web.UI.WebControls
Line 5: Imports System.Data
Line 6: Imports System.Data.SQLClient
Line 7: Imports System.Configuration
Line 8: Imports System.Web.UI.HtmlControls
Line 9: Imports System.IO
Line 10: Imports Microsoft.VisualBasic
Line 11: Imports System.Web.Security
Line 12: Imports System.Web.Mail
Line 13: Imports System.Text
Line 14: Imports System.Security
Line 15: Imports System.Security.Principal
Line 16: Imports InterPro
Line 17:
Line 18: Public Class VisaRequest : Inherits InterPro.global
Line 19: 'Declare variables
Line 20: Private objConn As SqlConnection
Line 21:
Line 22: Public dropCountry As DropDownList
Line 23: Public txtSurname As TextBox
Line 24: Public txtGivenName As TextBox
Line 25: Public txtOtherName As TextBox
Line 26: Public dropYear As DropDownList
Line 27: Public dropMonth As DropDownList
Line 28: Public dropDay As DropDownList
Line 29: Public dropBirthCountry As DropDownList
Line 30: Public dropNationality As DropDownList
Line 31: Public radlSex As RadioButtonList
Line 32: Public txtAddress As TextBox
Line 33: Public txtCity As TextBox
Line 34: Public txtPhone As TextBox
Line 35: Public txtFax As TextBox
Line 36: Public txtMail As TextBox
Line 37: Public txtFatherName As TextBox
Line 38: Public txtMotherName As TextBox
Line 39: Public txtProfession As TextBox
Line 40: Public radlType As RadioButtonList
Line 41: Public txtAddress2 As TextBox
Line 42: Public txtPhone2 As TextBox
Line 43: Public txtFax2 As TextBox
Line 44: Public txtMail2 As TextBox
Line 45: Public txtField1 As TextBox
Line 46: Public txtField2 As TextBox
Line 47: Public txtField3 As TextBox
Line 48: Public txtField4 As TextBox
Line 49: Public txtField5 As TextBox
Line 50: Public txtField6 As TextBox
Line 51: Public txtField7 As TextBox
Line 52: Public txtField8 As TextBox
Line 53: Public txtField9 As TextBox
Line 54: Public txtField10 As TextBox
Line 55: Public lblMessage As Label
Line 56:
Line 57: Public pageHeader
Line 58: Public pageBody
Line 59: Public pageSlogen
Line 60: Public pageMetaTitle
Line 61: Public pageMetaDescription
Line 62: Public pageMetaKeywords
Line 63:
Line 64: Public Cpassword As TextBox
Line 65: Public Cusername As TextBox
Line 66: Public strBody As String
Line 67: Public dropFormType As DropDownList
Line 68: Public sMyI25 As String
Line 69: Protected phldEmbassyName As PlaceHolder
Line 70: Protected dropEmbassyName As DropDownList
Line 71: Protected lblPlace As Label
Line 72:
Line 73:
Line 74: 'Get list of countries from database
Line 75: Function getCountry
Line 76: 'Define variables
Line 77: Dim objCommand As SqlCommand = New SqlCommand("getCountry",objConn)
Line 78: objCommand.CommandType = CommandType.StoredProcedure
Line 79:
Line 80: Dim objReader As SqlDataReader = objCommand.ExecuteReader
Line 81:
Line 82: Return objReader
Line 83:
Line 84: 'Close datareader
Line 85: objReader.Close()
Line 86: End Function
Line 87:
Line 88: 'This function opens connection to database
Line 89: Sub openDatabaseConn
Line 90: objConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Line 91:
Line 92: 'Open connection to database
Line 93: objConn.Open()
Line 94: End Sub
Line 95:
Line 96:
Line 97: 'This function closes connection to database
Line 98: Sub Page_PreRender
Line 99: Try
Line 100: objConn.Close()
Line 101: Catch
Line 102: End Try
Line 103: End Sub
Line 104:
Line 105:
Line 106: 'This function redirect user to page with form details
Line 107: Sub redirectToForm(s As Object, e As EventArgs)
Line 108: If dropFormType.SelectedItem.Value = 1 Then
Line 109: Response.Redirect(String.Format("requestForm.aspx?CountryID={0}",dropEmbassyName.SelectedItem.Value))
Line 110: Else If dropFormType.SelectedItem.Value = 2 Then
Line 111: Response.Redirect(String.Format("requestFormStudent.aspx?CountryID={0}",dropEmbassyName.SelectedItem.Value))
Line 112: End If
Line 113: End Sub
Line 114:
Line 115:
Line 116: 'This function insert new request into database
Line 117: Sub addToRequest(s As Object, e As EventArgs)
Line 118:
Line 119: If Page.IsValid Then
Line 120: 'Open connection to database
Line 121: openDatabaseConn
Line 122:
Line 123: 'for output from the stored procedure
Line 124: Dim workparam As SQLParameter = Nothing
Line 125:
Line 126: Dim objCommand As SqlCommand = New SqlCommand("addToRequest",objConn)
Line 127: objCommand.CommandType = CommandType.StoredProcedure
Line 128:
Line 129:
Line 130: 'Add parameters to command object
Line 131: 'Generate random name
Line 132: Dim MyValue As Integer
Line 133: Randomize ' Initialize random-number generator.
Line 134: MyValue = CInt(Int((100000 * Rnd()) + 100000))
Line 135:
Line 136: If FileData.PostedFile.FileName.length > 0 Then
Line 137: objCommand.Parameters.Add("@Photo",SqlDbType.nvarchar).Value = MyValue & Path.GetFileName(FileData.PostedFile.FileName)
Line 138: FileData.PostedFile.SaveAs(MapPath("data/request/") & MyValue & Path.GetFileName(FileData.PostedFile.FileName))
Line 139: Else
Line 140: objCommand.Parameters.Add("@Photo",SqlDbType.nvarchar).Value = System.DBNull.Value
Line 141: End If
Line 142:
Line 143: objCommand.Parameters.Add("@Surname",SQLDBtype.nvarchar).Value = txtSurname.Text.Trim()
Line 144:
Line 145: objCommand.Parameters.Add("@GivenName",SQLDBtype.nvarchar).Value = txtGivenName.Text.Trim()
Line 146:
Line 147: If txtOtherName.Text.Trim().Length > 0 Then
Line 148: objCommand.Parameters.Add("@OtherName",SQLDBtype.nvarchar).Value = txtOtherName.Text.Trim()
Line 149: Else
Line 150: objCommand.Parameters.Add("@OtherName",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 151: End If
Line 152:
Line 153: objCommand.Parameters.Add("@Birthdate",SQLDBtype.smalldatetime).Value = dropDay.SelectedItem.Text + "." + dropMonth.SelectedItem.Text + "." + dropYear.SelectedItem.Text
Line 154:
Line 155: objCommand.Parameters.Add("@BirthCountry",SQLDBtype.nvarchar).Value = dropBirthCountry.SelectedItem.Text
Line 156:
Line 157: objCommand.Parameters.Add("@Nationality",SQLDBtype.nvarchar).Value = dropNationality.SelectedItem.Text
Line 158:
Line 159: objCommand.Parameters.Add("@Sex",SQLDBtype.nvarchar).Value = radlSex.SelectedItem.Text
Line 160:
Line 161: objCommand.Parameters.Add("@Address",SQLDBtype.nvarchar).Value = txtAddress.Text
Line 162:
Line 163: objCommand.Parameters.Add("@City",SQLDBtype.nvarchar).Value = txtCity.Text
Line 164:
Line 165: objCommand.Parameters.Add("@Country",SQLDBtype.nvarchar).Value = dropCountry.SelectedItem.Text
Line 166:
Line 167: objCommand.Parameters.Add("@Phone",SQLDBtype.nvarchar).Value = txtPhone.Text
Line 168:
Line 169: If txtFax.Text.Trim().Length > 0 Then
Line 170: objCommand.Parameters.Add("@Fax",SQLDBtype.nvarchar).Value = txtFax.Text
Line 171: Else
Line 172: objCommand.Parameters.Add("@Fax",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 173: End If
Line 174:
Line 175: If txtMail.Text.Trim().Length > 0 Then
Line 176: objCommand.Parameters.Add("@Mail",SQLDBtype.nvarchar).Value = txtMail.Text
Line 177: Else
Line 178: objCommand.Parameters.Add("@Mail",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 179: End If
Line 180:
Line 181: If txtFatherName.Text.Trim().Length > 0 Then
Line 182: objCommand.Parameters.Add("@FatherName",SQLDBtype.nvarchar).Value = txtFatherName.Text
Line 183: Else
Line 184: objCommand.Parameters.Add("@FatherName",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 185: End If
Line 186:
Line 187: If txtMotherName.Text.Trim().Length > 0 Then
Line 188: objCommand.Parameters.Add("@MotherName",SQLDBtype.nvarchar).Value = txtMotherName.Text
Line 189: Else
Line 190: objCommand.Parameters.Add("@MotherName",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 191: End If
Line 192:
Line 193: objCommand.Parameters.Add("@Profession",SQLDBtype.nvarchar).Value = txtProfession.Text
Line 194:
Line 195: objCommand.Parameters.Add("@Type",SQLDBtype.nvarchar).Value = radlType.SelectedItem.Text
Line 196:
Line 197: objCommand.Parameters.Add("@Address2",SQLDBtype.nvarchar).Value = txtAddress2.Text
Line 198:
Line 199: objCommand.Parameters.Add("@Phone2",SQLDBtype.nvarchar).Value = txtPhone2.Text
Line 200:
Line 201: If txtFax2.Text.Trim().Length > 0 Then
Line 202: objCommand.Parameters.Add("@Fax2",SQLDBtype.nvarchar).Value = txtFax2.Text
Line 203: Else
Line 204: objCommand.Parameters.Add("@Fax2",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 205: End If
Line 206:
Line 207: If txtMail2.Text.Trim().Length > 0 Then
Line 208: objCommand.Parameters.Add("@Mail2",SQLDBtype.nvarchar).Value = txtMail2.Text
Line 209: Else
Line 210: objCommand.Parameters.Add("@Mail2",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 211: End If
Line 212:
Line 213: If txtField1.Text.Trim().Length > 0 Then
Line 214: objCommand.Parameters.Add("@Field1",SQLDBtype.nvarchar).Value = txtField1.Text
Line 215: Else
Line 216: objCommand.Parameters.Add("@Field1",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 217: End If
Line 218:
Line 219: If txtField2.Text.Trim().Length > 0 Then
Line 220: objCommand.Parameters.Add("@Field2",SQLDBtype.nvarchar).Value = txtField2.Text
Line 221: Else
Line 222: objCommand.Parameters.Add("@Field2",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 223: End If
Line 224:
Line 225: If txtField3.Text.Trim().Length > 0 Then
Line 226: objCommand.Parameters.Add("@Field3",SQLDBtype.nvarchar).Value = txtField3.Text
Line 227: Else
Line 228: objCommand.Parameters.Add("@Field3",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 229: End If
Line 230:
Line 231: If txtField4.Text.Trim().Length > 0 Then
Line 232: objCommand.Parameters.Add("@Field4",SQLDBtype.nvarchar).Value = txtField4.Text
Line 233: Else
Line 234: objCommand.Parameters.Add("@Field4",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 235: End If
Line 236:
Line 237: If txtField5.Text.Trim().Length > 0 Then
Line 238: objCommand.Parameters.Add("@Field5",SQLDBtype.nvarchar).Value = txtField5.Text
Line 239: Else
Line 240: objCommand.Parameters.Add("@Field5",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 241: End If
Line 242:
Line 243: If txtField6.Text.Trim().Length > 0 Then
Line 244: objCommand.Parameters.Add("@Field6",SQLDBtype.nvarchar).Value = txtField6.Text
Line 245: Else
Line 246: objCommand.Parameters.Add("@Field6",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 247: End If
Line 248:
Line 249: If txtField7.Text.Trim().Length > 0 Then
Line 250: objCommand.Parameters.Add("@Field7",SQLDBtype.nvarchar).Value = txtField7.Text
Line 251: Else
Line 252: objCommand.Parameters.Add("@Field7",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 253: End If
Line 254:
Line 255: If txtField8.Text.Trim().Length > 0 Then
Line 256: objCommand.Parameters.Add("@Field8",SQLDBtype.nvarchar).Value = txtField8.Text
Line 257: Else
Line 258: objCommand.Parameters.Add("@Field8",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 259: End If
Line 260:
Line 261: If txtField9.Text.Trim().Length > 0 Then
Line 262: objCommand.Parameters.Add("@Field9",SQLDBtype.nvarchar).Value = txtField9.Text
Line 263: Else
Line 264: objCommand.Parameters.Add("@Field9",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 265: End If
Line 266:
Line 267: If txtField10.Text.Trim().Length > 0 Then
Line 268: objCommand.Parameters.Add("@Field10",SQLDBtype.nvarchar).Value = txtField10.Text
Line 269: Else
Line 270: objCommand.Parameters.Add("@Field10",SQLDBtype.nvarchar).Value = System.DBNull.Value
Line 271: End If
Line 272:
Line 273: objCommand.Parameters.Add("@ReqDate",SQLDBtype.smalldatetime).Value = Now()
Line 274:
Line 275: objCommand.Parameters.Add("@Status",SQLDBtype.int).Value = 0
Line 276:
Line 277: objCommand.Parameters.Add("@CounID",SQLDBtype.int).Value = Request.Params("CountryID")
Line 278:
Line 279:
Line 280: 'Establish an output parameter to return a value from the stored proc
Line 281: workparam = objCommand.Parameters.Add(New SQLParameter("@ReqID",SQLDbType.Int))
Line 282:
Line 283: 'Our parameter is for output as mentioned
Line 284: workParam.Direction = ParameterDirection.Output
Line 285:
Line 286:
Line 287: 'Execute command
Line 288: Try
Line 289: objCommand.ExecuteNonQuery()
Line 290: Session("ReqID") = objCommand.Parameters("@ReqID").Value
Line 291: 'sendRequestMail()
Line 292: Response.Redirect("requestSend.aspx?CountryID="&Request.Params("CountryID"))
Line 293: Catch
Line 294: lblMessage.Text = String.Format("<br>• {0}<br>", err.Description)
Line 295: End Try
Line 296:
Line 297: End If
Line 298: End Sub
Line 299:
Line 300:
Line 301: 'This function get description of page from database
Line 302: Sub getSitePages(pageType as integer)
Line 303:
Line 304: ' Obtain page info
Line 305:
Line 306: Dim dbCommand as SQLCommand
Line 307: Dim SQLServer as String = ConfigurationSettings.AppSettings("ConnectionString")
Line 308:
Line 309: Dim DataConn as SQLConnection
Line 310: DataConn = new SQLConnection(SQLServer)
Line 311:
Line 312: dim ObjReader as SQLDataReader
Line 313:
Line 314: 'get page details------------------------------------------------
Line 315: 'call to store procedure which will be executed in a moment
Line 316: dbCommand = New SQLCommand("getSitePages",DataConn)
Line 317:
Line 318: 'our command type is for a store procedure. Not a string SQL statement
Line 319: dbCommand.CommandType = CommandType.StoredProcedure
Line 320:
Line 321: 'variable @LNG in the stored proc
Line 322: dbCommand.Parameters.Add(New SQLParameter("@LNG", SQLDbType.nvarchar,20))
Line 323: dbCommand.Parameters("@LNG").Value = "EN"
Line 324:
Line 325: 'variable @pageType in the stored proc
Line 326: dbCommand.Parameters.Add(New SQLParameter("@pageType",SQLDbType.Int))
Line 327: dbCommand.Parameters("@pageType").Value = pageType
Line 328:
Line 329: 'open connection and execute.
Line 330: DataConn.open()
Line 331:
Line 332: ObjReader=dbCommand.ExecuteReader
Line 333:
Line 334: if ObjReader.Read() then
Line 335: pageHeader = ObjReader("pageHeader")
Line 336: pageBody = ObjReader("pageBody")
Line 337: pageSlogen = ObjReader("pageSlogen")
Line 338: pageMetaTitle = ObjReader("pageMetaTitle")
Line 339: pageMetaDescription = ObjReader("pageMetaDescription")
Line 340: pageMetaKeywords = ObjReader("pageMetaKeywords")
Line 341: End IF
Line 342:
Line 343: ObjReader.Close
Line 344:
Line 345: 'close connection
Line 346: DataConn.close
Line 347:
Line 348: End Sub
Line 349:
Line 350:
Line 351: 'This function check if this user exist in database
Line 352: Sub checkUser(s As Object, e As EventArgs)
Line 353: 'Open connection to database
Line 354: openDatabaseConn
Line 355:
Line 356: 'Define variables
Line 357: Dim objCommand As SqlCommand = New SqlCommand("checkUser",objConn)
Line 358: Dim objReader As SqlDataReader
Line 359: objCommand.CommandType = CommandType.StoredProcedure
Line 360:
Line 361: 'Add parameters to command
Line 362: objCommand.Parameters.Add("@UserName",SQLDBtype.nvarchar).Value = Cusername.Text
Line 363:
Line 364: objCommand.Parameters.Add("@UserPassword",SQLDBtype.nvarchar).Value = Cpassword.Text
Line 365:
Line 366: objReader = objCommand.ExecuteReader
Line 367:
Line 368: If objReader.Read() Then
Line 369: session("AdminID") = objReader("AdminID")
Line 370: session("AdminName") = objReader("Name")
Line 371:
Line 372: If objReader("CounID") Is System.DBNull.Value Then
Line 373: session("CounID") = 0
Line 374: Else
Line 375: session("CounID") = objReader("CounID")
Line 376: End If
Line 377:
Line 378: 'Redirect browser back to originating page and create authentication ticket
Line 379: Dim objTicket As FormsAuthenticationTicket
Line 380: Dim objCookie As HttpCookie
Line 381:
Line 382: objTicket = New FormsAuthenticationTicket(1,objReader("AdminID"),Now(),dateAdd("yyyy",1,Now()),false,objReader("Role"))
Line 383:
Line 384: objCookie = New HttpCookie(".MongolianVisa")
Line 385: objCookie.Value = FormsAuthentication.Encrypt(objTicket)
Line 386: Response.Cookies.Add(objCookie)
Line 387:
Line 388: 'If Request.Params("ReturnURL") <> Nothing Then
Line 389: 'Response.Redirect(Request.Params("ReturnURL"))
Line 390: 'Else
Line 391: If objReader("Role").ToString.ToLower = "supadmin" Then
Line 392: Response.Redirect("admin/admin.aspx")
Line 393: Else If objReader("Role").ToString.ToLower = "admin" Then
Line 394: Response.Redirect("admin/admin.aspx")
Line 395: Else If objReader("Role").ToString.ToLower = "paymentadmin" Then
Line 396: Response.Redirect("admin/payment/index.aspx")
Line 397: Else If objReader("Role").ToString.ToLower = "bordersadmin" Then
Line 398: Response.Redirect("admin/request/index.aspx")
Line 399: Else
Line 400: Response.Redirect("default.aspx")
Line 401: End If
Line 402: 'End If
Line 403: Else
Line 404: lblMessage.Text = "Wrong username or password!"
Line 405: End If
Line 406: End Sub
Line 407:
Line 408:
Line 409: 'This function get help details for getting visa of specific country
Line 410: Sub getHelpDetails(CountryID As Integer)
Line 411: ' Obtain page info
Line 412: Dim dbCommand as SQLCommand
Line 413: Dim SQLServer as String = ConfigurationSettings.AppSettings("ConnectionString")
Line 414:
Line 415: Dim DataConn as SQLConnection
Line 416: DataConn = new SQLConnection(SQLServer)
Line 417:
Line 418: dim ObjReader as SQLDataReader
Line 419:
Line 420: 'get page details------------------------------------------------
Line 421: 'call to store procedure which will be executed in a moment
Line 422: dbCommand = New SQLCommand("getHelpDetails",DataConn)
Line 423:
Line 424: 'our command type is for a store procedure. Not a string SQL statement
Line 425: dbCommand.CommandType = CommandType.StoredProcedure
Line 426:
Line 427:
Line 428: 'variable @pageType in the stored proc
Line 429: dbCommand.Parameters.Add(New SQLParameter("@CounID",SQLDbType.Int))
Line 430: dbCommand.Parameters("@CounID").Value = CountryID
Line 431:
Line 432: 'open connection and execute.
Line 433: DataConn.open()
Line 434:
Line 435: ObjReader=dbCommand.ExecuteReader
Line 436:
Line 437: if ObjReader.Read() then
Line 438: pageHeader = ObjReader("HelpHeader")
Line 439: pageBody = ObjReader("HelpBody")
Line 440: End IF
Line 441:
Line 442: ObjReader.Close
Line 443:
Line 444: 'close connection
Line 445: DataConn.close
Line 446: End Sub
Line 447:
Line 448:
Line 449: 'This function send mail with request details to user and notification mail to support staff
Line 450: Sub sendRequestMail
Line 451: 'Define variables
Line 452: Dim Mailer As MailMessage = New MailMessage()
Line 453: Dim strBody As String
Line 454:
Line 455:
Line 456: 'Get all staff from barcode
Line 457: '----------------------------------------------------
Line 458: Dim BarcodeID As String = String.Format("{0:d8}",Session("ReqID"))
Line 459: sMyI25 = I25Encode(BarcodeID)
Line 460: '/---------------------------------------------------
Line 461:
Line 462:
Line 463: 'Send mail to user
Line 464: '-----------------------------------------------------
Line 465:
Line 466: 'Get details of MessageRequest text
Line 467: Dim dbCommand as SQLCommand
Line 468: Dim SQLServer as String = ConfigurationSettings.AppSettings("ConnectionString")
Line 469:
Line 470: Dim DataConn as SQLConnection
Line 471: DataConn = new SQLConnection(SQLServer)
Line 472:
Line 473: dim ObjReader as SQLDataReader
Line 474:
Line 475: 'call to store procedure which will be executed in a moment
Line 476: dbCommand = New SQLCommand("getMessageRequest",DataConn)
Line 477:
Line 478: 'our command type is for a store procedure. Not a string SQL statement
Line 479: dbCommand.CommandType = CommandType.StoredProcedure
Line 480:
Line 481: dbCommand.Parameters.Add(New SQLParameter("@CounID",SQLDbType.Int))
Line 482: dbCommand.Parameters("@CounID").Value = Request.Params("CountryID")
Line 483:
Line 484: 'open connection and execute.
Line 485: DataConn.open()
Line 486:
Line 487: ObjReader=dbCommand.ExecuteReader
Line 488:
Line 489: if ObjReader.Read() then
Line 490: strBody = ObjReader("MessageRequest").ToString()
Line 491: End IF
Line 492:
Line 493: ObjReader.Close
Line 494:
Line 495: 'close connection
Line 496: DataConn.close
Line 497:
Line 498:
Line 499: Mailer.From = "yairp@plbtec.com"
Line 500: Mailer.To = txtMail.Text.Trim()
Line 501: Mailer.Subject = "Mongolian VISA request"
Line 502: Mailer.Body = String.Format("Your request number is {0}.<br>Barcode: <br>{2}<br>{1}",Session("ReqID"),strBody,compileBarcode)
Line 503: Mailer.BodyFormat = MailFormat.Html
Line 504: SmtpMail.SmtpServer = "mail.webhost4life.com"
Line 505: Mailer.BodyEncoding = Encoding.UTF8
Line 506: SmtpMail.Send(Mailer)
Line 507: '/----------------------------------------------------
Line 508:
Line 509:
Line 510:
Line 511: 'Send mail to support staff
Line 512: '-----------------------------------------------------
Line 513: 'Get Mail list
Line 514: Dim strMailList As String
Line 515: DataConn = new SQLConnection(SQLServer)
Line 516: Mailer = New MailMessage()
Line 517:
Line 518: 'call to store procedure which will be executed in a moment
Line 519: dbCommand = New SQLCommand("getMessageRequest",DataConn)
Line 520:
Line 521: 'our command type is for a store procedure. Not a string SQL statement
Line 522: dbCommand.CommandType = CommandType.StoredProcedure
Line 523:
Line 524: dbCommand.Parameters.Add(New SQLParameter("@CounID",SQLDbType.Int))
Line 525: dbCommand.Parameters("@CounID").Value = Request.Params("CountryID")
Line 526:
Line 527: 'open connection and execute.
Line 528: DataConn.open()
Line 529:
Line 530: ObjReader=dbCommand.ExecuteReader
Line 531:
Line 532: if ObjReader.Read() then
Line 533: strMailList = ObjReader("Mail").ToString()
Line 534: End IF
Line 535:
Line 536: ObjReader.Close
Line 537:
Line 538: 'close connection
Line 539: DataConn.close
Line 540:
Line 541: Mailer.From = "yairp@plbtec.com"
Line 542: Mailer.To = strMailList
Line 543: Mailer.Subject = "New VISA request"
Line 544: Mailer.Body = "In our system accepted new VISA request.<br>Please enter and review it."
Line 545: Mailer.BodyFormat = MailFormat.Html
Line 546: SmtpMail.SmtpServer = "mail.webhost4life.com"
Line 547: Mailer.BodyEncoding = Encoding.UTF8
Line 548: SmtpMail.Send(Mailer)
Line 549:
Line 550: '/----------------------------------------------------
Line 551: End Sub
Line 552:
Line 553:
Line 554: 'This function get details of MessageRequest field in database table
Line 555: Sub getMessageRequest
Line 556: 'Get details of MessageRequest text
Line 557: Dim dbCommand as SQLCommand
Line 558: Dim SQLServer as String = ConfigurationSettings.AppSettings("ConnectionString")
Line 559:
Line 560: Dim DataConn as SQLConnection
Line 561: DataConn = new SQLConnection(SQLServer)
Line 562:
Line 563: dim ObjReader as SQLDataReader
Line 564:
Line 565: 'call to store procedure which will be executed in a moment
Line 566: dbCommand = New SQLCommand("getMessageRequest",DataConn)
Line 567:
Line 568: 'our command type is for a store procedure. Not a string SQL statement
Line 569: dbCommand.CommandType = CommandType.StoredProcedure
Line 570:
Line 571: dbCommand.Parameters.Add(New SQLParameter("@CounID",SQLDbType.Int))
Line 572: dbCommand.Parameters("@CounID").Value = Request.Params("CountryID")
Line 573:
Line 574: 'open connection and execute.
Line 575: DataConn.open()
Line 576:
Line 577: ObjReader=dbCommand.ExecuteReader
Line 578:
Line 579: if ObjReader.Read() then
Line 580: strBody = ObjReader("MessageRequest").ToString()
Line 581: End IF
Line 582:
Line 583: ObjReader.Close
Line 584:
Line 585: 'close connection
Line 586: DataConn.close
Line 587: End Sub
Line 588:
Line 589:
Line 590: 'This function generates Barcode
Line 591: Function I25Encode(StringNumber) As String
Line 592: Dim asPattern(), sSTART, sSTOP
Line 593: Redim asPattern(10)
Line 594:
Line 595: sSTART = "NNNN" ' start and stop patterns can be found in fig. 3
Line 596: sSTOP = "WNN"
Line 597:
Line 598: asPattern(0) = "NNWWN" ' these patterns can be found in fig. 1
Line 599: asPattern(1) = "WNNNW"
Line 600: asPattern(2) = "NWNNW"
Line 601: asPattern(3) = "WWNNN"
Line 602: asPattern(4) = "NNWNW"
Line 603: asPattern(5) = "WNWNN"
Line 604: asPattern(6) = "NWWNN"
Line 605: asPattern(7) = "NNNWW"
Line 606: asPattern(8) = "WNNWN"
Line 607: asPattern(9) = "NWNWN"
Line 608:
Line 609: ' validate argument
Line 610:
Line 611: If (Len(StringNumber) Mod 2) <> 0 Then
Line 612: ' the number of characters in the argument must be odd
Line 613: I25Encode = ""
Line 614: Exit Function
Line 615: End If
Line 616:
Line 617: If Not IsNumeric(StringNumber) Then
Line 618: ' argument must be numeric
Line 619: I25Encode = ""
Line 620: Exit Function
Line 621: Else
Line 622: If (InStr(StringNumber, ".") <> 0) Or (InStr(StringNumber, ",") <> 0) Then
Line 623: ' argument is numeric but contains invalid characters to us
Line 624: I25Encode = ""
Line 625: Exit Function
Line 626: End If
Line 627: End If
Line 628:
Line 629: ' encode and interleave argument
Line 630: Dim sEncodedSTR, sUnit
Line 631: Dim iCharRead, sDigit1, sDigit2, i
Line 632: sEncodedSTR = ""
Line 633: For iCharRead = 1 To Len(StringNumber) Step 2
Line 634: sDigit1 = asPattern( Asc( Mid( StringNumber, iCharRead, 1 ) ) - 48 )
Line 635: sDigit2 = asPattern( Asc( Mid( StringNumber, iCharRead + 1, 1 ) ) - 48 )
Line 636: sUnit = ""
Line 637: For i = 1 To 5
Line 638: sUnit = sUnit & Mid( sDigit1, i, 1 ) & Mid( sDigit2, i, 1 )
Line 639: Next
Line 640: sEncodedSTR = sEncodedSTR & sUnit
Line 641: Next
Line 642: I25Encode = sSTART & sEncodedSTR & sSTOP
Line 643:
Line 644: Return I25Encode
Line 645: End Function
Line 646:
Line 647:
Line 648: 'This function compile string with images of barcode
Line 649: Function compileBarcode() As String
Line 650: Dim iPos, sGIF, bBAR
Line 651: Dim strBarCodeString As String = ""
Line 652: bBAR = True
Line 653:
Line 654: For iPos = 1 To Len(sMyI25)
Line 655: If (bBar) Then
Line 656: 'Bar
Line 657: sGIF = Mid(sMyI25, iPos, 1) & "b.gif"
Line 658: Else
Line 659: 'Space
Line 660: sGIF = Mid(sMyI25, iPos, 1) & "s.gif"
Line 661: End If
Line 662:
Line 663: strBarCodeString &= "<IMG SRC='http://www.mongolianvisa.com/images/" & sGIF & "'>"
Line 664:
Line 665: bBar = Not bBar
Line 666: Next
Line 667:
Line 668: Return strBarCodeString
Line 669: End Function
Line 670:
Line 671:
Line 672: 'This function compile barcode for RequestSend page
Line 673: Function getBarcode() As String
Line 674: Dim BarcodeID As String = String.Format("{0:d8}",Session("ReqID"))
Line 675: sMyI25 = I25Encode(BarcodeID)
Line 676:
Line 677: Return compileBarcode()
Line 678: End Function
Line 679:
Line 680:
Line 681: 'This function selects embassy`s names from database table
Line 682: Sub chooseEmbassyName(s As Object,e As EventArgs)
Line 683: 'Define command object and execute it
Line 684: Dim objCommand As SqlCommand = New SqlCommand("getEmbassyName",objConn)
Line 685: Dim objReader As SqlDataReader
Line 686: objCommand.CommandType = CommandType.StoredProcedure
Line 687:
Line 688: 'Add parameters to command object
Line 689: objCommand.Parameters.Add("@CounName",SqlDbType.nvarchar).Value = dropCountry.SelectedItem.Value
Line 690:
Line 691: 'Execute reader and bind results to dropEmbassyName control
Line 692: objReader = objCommand.ExecuteReader
Line 693: dropEmbassyName.DataSource = objReader
Line 694: dropEmbassyName.DataTextField = "EmbassyName"
Line 695: dropEmbassyName.DataValueField = "CounID"
Line 696: dropEmbassyName.DataBind
Line 697:
Line 698: If dropCountry.SelectedItem.Value = "0" Then
Line 699: phldEmbassyName.Visible = false
Line 700: Else
Line 701: phldEmbassyName.Visible = true
Line 702: End If
Line 703:
Line 704: 'Close data reader
Line 705: objReader.Close()
Line 706:
Line 707: End Sub
Line 708:
Line 709:
Line 710: 'This function get details of user defined pages
Line 711: Sub getOtherPages(pageID As Integer)
Line 712: ' Obtain page info
Line 713:
Line 714: Dim dbCommand as SQLCommand
Line 715: Dim SQLServer as String = ConfigurationSettings.AppSettings("ConnectionString")
Line 716:
Line 717: Dim DataConn as SQLConnection
Line 718: DataConn = new SQLConnection(SQLServer)
Line 719:
Line 720: dim ObjReader as SQLDataReader
Line 721:
Line 722: 'get page details------------------------------------------------
Line 723: 'call to store procedure which will be executed in a moment
Line 724: dbCommand = New SQLCommand("getOtherPages",DataConn)
Line 725:
Line 726: 'our command type is for a store procedure. Not a string SQL statement
Line 727: dbCommand.CommandType = CommandType.StoredProcedure
Line 728:
Line 729: 'variable @LNG in the stored proc
Line 730: dbCommand.Parameters.Add(New SQLParameter("@LNG", SQLDbType.nvarchar,20))
Line 731: dbCommand.Parameters("@LNG").Value = "EN"
Line 732:
Line 733: 'variable @pageType in the stored proc
Line 734: dbCommand.Parameters.Add(New SQLParameter("@pageID",SQLDbType.Int))
Line 735: dbCommand.Parameters("@pageID").Value = pageID
Line 736:
Line 737: 'open connection and execute.
Line 738: DataConn.open()
Line 739:
Line 740: ObjReader=dbCommand.ExecuteReader
Line 741:
Line 742: if ObjReader.Read() then
Line 743: pageHeader = ObjReader("pageHeader").Tostring
Line 744: pageBody = ObjReader("pageBody").Tostring
Line 745: pageSlogen = ObjReader("pageSlogen").Tostring
Line 746: pageMetaTitle = ObjReader("pageMetaTitle").Tostring
Line 747: pageMetaDescription = ObjReader("pageMetaDescription").Tostring
Line 748: pageMetaKeywords = ObjReader("pageMetaKeywords").Tostring
Line 749: End IF
Line 750:
Line 751: ObjReader.Close
Line 752:
Line 753: 'close connection
Line 754: DataConn.close
Line 755: End Sub
Line 756:
Line 757:
Line 758: 'This function get name of country and name of embassy from database table
Line 759: Sub getCountryName
Line 760: 'Define variables
Line 761: Dim objConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Line 762: Dim objCommand As SqlCommand = New SqlCommand("getCountry4",objConn)
Line 763: Dim objReader As SqlDataReader
Line 764: objCommand.CommandType = CommandType.StoredProcedure
Line 765:
Line 766: 'Add parameters to command object
Line 767: objCommand.Parameters.Add("@CounID",SQLDBType.int).Value = Request("CountryID")
Line 768:
Line 769: Try
Line 770: objConn.Open()
Line 771: objReader = objCommand.ExecuteReader(CommandBehavior.SingleRow)
Line 772:
Line 773: If objReader.Read() Then
Line 774: lblPlace.Text = objReader("CounName") & " > " & objReader("EmbassyName")
Line 775: End If
Line 776: Catch
Line 777: lblMessage.Text = String.Format("<br>• {0}<br>", err.Description)
Line 778: Finally
Line 779: objConn.Close()
Line 780: End Try
Line 781: End Sub
Line 782: End Class
|