SQL statement in Lotus Script.

Tuesday, March 17, 2009

Kerana nak tulis script nih la..I have to stay back until 9pm and have to let go my plan to Tupai2. Tp rasa lega sangat2..at least I am able to finish my work before the deadline. Today is the implementation day, pagi2 dah buat final testing..ada kena tambah sikit & terus masuk production. Harap2 system stable & x der complain from user..

This codes connected to SQL server using ODBC.

(1)Agent call from Button.
Verify Bypass Ticket) | lscrptVerifyBypassTicket



Option Public

Use "Common Functions"


Sub Initialize


Dim db_PNAB As NotesDatabase
Dim strCurrentServerName As String
Dim strPNABServer As String
Dim strPNABPath As String
Dim strName As String
Dim strEmailAddress As String
Dim strCurrentUserName As String
Dim strCurrentUserRemedyLogin As String
Dim strDataSourceName As String
Dim strODBCUserName As String
Dim strODBCPassword As String
Dim uidoc As NotesUIDocument
Dim doc As NotesDocument
Dim strErrorMsg As String
Dim strTicketHeader As String
Dim varNameList As Variant
Dim varEmailList As Variant

Const intTicketHeaderLength = 4

Dim session As New NotesSession
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim ws As New NotesUIWorkspace

Set uidoc = ws.CurrentDocument
Set doc = uidoc.Document

strCurrentServerName = fncGetCurrentServerName
strDataSourceName = fncGetProfileValue (strCurrentServerName , "ODBCDataSource" )

strODBCUserName = fncGetProfileValue ( strCurrentServerName , "RemedyUserID" )
strODBCPassword = fncGetProfileValue (strCurrentServerName , "RemedyUserPassword" )

Set qry.Connection = con
Set result.Query = qry
con.SilentMode = True
status = con.ConnectTo ( strDataSourceName , strODBCUserName , strODBCPassword )

If Not con.IsConnected Then
Messagebox "ODBC Connection to " + strDataSourceName + " failed !" , 0 + 16 , "ERROR"
Exit Sub
End If


strPNABServer = fncGetProfileValue ( strCurrentServerName , "PNABServer" )
strPNABPath = fncGetProfileValue ( strCurrentServerName , "PNABPath" )
Set db_PNAB = New NotesDatabase ( strPNABServer , strPNABPath )
strName = session.UserName


'function call "fncGetCorrectEmailAddr" to check and retrieve correct email address due to removing country code in email address

tempEmailAddr$ = fncGetInternetEmailAddress ( db_PNAB, strName )
strEmailAddress = fncGetCorrectEmailAddr ( qry, result, tempEmailAddr$ )
strCurrentUserName = strName
strCurrentUserRemedyLogin = Lcase ( fncGetLoginNameOfEmail ( qry , result , strEmailAddress) )
strTicketHeader = Lcase ( Left( doc.BypassReasonText (0) , intTicketHeaderLength ) )


If Lcase ( strTicketHeader ) = "pnkl" Or Lcase ( strTicketHeader ) = "pnch" Then '|||||||||||||||||||||| If ticket is a Problem Ticket. PNKL / PNCH

doc.TicketType = "1"

qry.SQL = "SELECT ""Problem #"" FROM ""SCB-ProblemManagement"" WHERE ( ""Problem #"" = '" + Ucase ( doc.BypassReasonText (0) )+ "') "

result.execute


If Not ( result.IsResultSetAvailable ) Then
strErrorMsg = "Problem Ticket Number [" + doc.BypassReasonText (0) + "] does not exist in Remedy !"

doc.ErrorMsg = strErrorMsg
Call uidoc.GoToField ("RequestNo")
Goto ENDNOW
Else
doc.TicketVerify = "1"
Goto ENDNOW
End If


Elseif Lcase ( strTicketHeader ) = "cmkl" Or Lcase ( strTicketHeader ) = "cmch" Then '|||||||||||||||||||||| If ticket is a Problem Ticket. CMKL / CMCH

doc.TicketType = "2"

qry.SQL = "SELECT ""Change #"" FROM ""SCB-ChangeManagement"" WHERE ( ""Change #"" = '" + Ucase ( doc.BypassReasonText (0) )+ "') "

result.execute

If Not ( result.IsResultSetAvailable ) Then
strErrorMsg = "Change Ticket Number [" + doc.BypassReasonText (0) + "] does not exist in Remedy !"

doc.ErrorMsg = strErrorMsg
Goto ENDNOW
Else
doc.TicketVerify = "1"
Goto ENDNOW
End If
Else '|||||||||||||||||||||| If ticket is invalid i.e. not PNKL/PNCH/CMKL/CMCH

strErrorMsg = "Ticket Number [" + doc.BypassReasonText (0) + "] does not exist in Remedy. Please enter the full Problem / Change ticket number."

doc.ErrorMsg = strErrorMsg
Goto ENDNOW
End If


ENDNOW :
con.Disconnect
Call uidoc.refresh


End Sub


(2) Bypass Verification button


Sub Click(Source As Button)


Dim ws As New NotesUIWorkspace
Dim session As New NotesSession
Dim uidoc As NotesUIDocument
Dim doc As NotesDocument

Set uidoc = ws.CurrentDocument
Set doc = uidoc.Document


If doc.BypassReason (0) = "" Then
Messagebox "Please select a reason for bypassing the verification process", 0 + 16 + 256 + 0 , "WARNING ! "

Exit Sub
End If

If doc.BypassReason (0) = "BAU" Then


If doc.Requestor (0) = "" Then
doc.Requestor = session.CommonUserName
doc.Receipient = session.CommonUserName
End If

Elseif doc.BypassReason (0) = "Requester is manually validated" Then


If doc.RequestNo (0) = "" Then
Messagebox "Please enter a valid Request No" , 0 + 16 , "ERROR"
uidoc.GoToField ("RequestNo")
Exit Sub

Elseif doc.Requestor (0) = "" Then
Messagebox "Please enter a valid Requester Name" , 0 + 16 , "ERROR"
uidoc.GoToField ("Requestor")
Exit Sub

Elseif doc.Receipient (0) = "" Then
Messagebox "Please enter a valid Receipient Name" , 0 + 16 , "ERROR"

uidoc.GoToField ("Receipient")
Exit Sub
Elseif doc.Reason (0) = "" Then
Messagebox "Please enter a Reason for ID withdrawal" , 0 + 16 , "ERROR"

uidoc.GoToField ("Reason")
Exit Sub
Elseif doc.Time (0) = "" Then

Messagebox "Please enter a Length of Time requested" , 0 + 16 , "ERROR"

uidoc.GoToField ("Time")
Exit Sub
End If

If doc.BypassReasonText (0) = "" Then

Messagebox "Please enter the reason why the requester was not automatically verified", 0 + 16 + 256 + 0 , "ERROR ! "

uidoc.GoToField ("BypassReasonText")
Exit Sub
End If


If doc.BypassApprovedBy (0) = "" Then
Messagebox "Please enter approver of this bypass operation", 0 + 16 + 256 + 0 , "ERROR ! "

uidoc.GoToField ("BypassApprovedBy")
Exit Sub
End If



Dim s As New NotesSession
Set db = s.CurrentDatabase
Set agent = db.GetAgent("lscrptVerifyBypassTicket")
If agent.Run = 0 Then
Print "Agent ran",, "Success"
Else
Print "Agent did not run",, "Failure"
End If
End If


If doc.TicketVerify (0) <> "1" Then
Messagebox doc.ErrorMsg(0) , 0 + 16 , "ERROR"
Messagebox "REMEDY Ticket cannot be verified. Password cannot be released.", 0+64, "Error"
Else
intReply = Messagebox ( "You are about to bypass the verification process of releasing this password." + Chr (10) + "Are you sure you want to continue ?", 4 + 32 + 256 + 0 , "WARNING ! " )


If intReply = 6 Then
If doc.Receipient (0) = "" Then doc.Receipient = doc.Requestor (0)
doc.Bypassed = "1"
doc.BypassedBy = session.CommonUserName
doc.TicketVerify = "1"
doc.Form = "log"
Else
doc.TicketVerify = "0"
End If
End If

Call uidoc.Refresh

End Sub

0 comments: