Quicklinks
- Download ActiveXperts SMS Messaging Server 5.3 (14947 KB)
- Case studies - How SMS Messaging Server is used by existing customers
- Live Demo! - Play the SMS Music Trivia, based on SMS Messaging Server
- Screenshots - Product image gallery
SMS elections application software, Kosovo 2009
Authors:
- Andi Erebara of IstiTech.net,
- Augustus de Vree of Uela.it,
- Bart Jonkman of ActiveXperts Software B.V.
- 1. Background
- 2. Problem Statement
- 3. Goals of the new System
- 4. ActiveXperts SMS Messaging Server Solution
1. Background
IFES is a nonprofit democracy development organization that works to give people a voice in the way that they are governed. Formerly the International Foundation for Election Systems, IFES is the world's premiere election assistance organization, providing countries with the technical advice and tools they need to run democratic elections. IFES work is nonpartisan and also includes projects that:
- Help citizens participate in their democracies
- Increase politicians' accountability to the electorate
- Strengthen government institutions
Since its founding in 1987, IFES has worked in more than 100 countries - from developing democracies such as Liberia, to mature democracies such as the United States. Every IFES project is staffed by local personnel and our team partners with local organizations. With this homegrown approach, IFES ensures that the expertise it offers fits the needs of the country or client and that the benefit of assistance outlasts the life of the project. (ref. www.ifes.org) IFES presence in Kosovo is focused on the same principles and targets. This case study is focused on the last project that IFES implemented for the Elections held in Kosovo in November 2007.
2. Problem Statement
The project aims to get results in real time for the elections. The main challenge is the number of voting centers from which the results are planned to come and the time frame which should be as low as possible, of course keeping the information intact, without errors and logically arranged in result tables for media broadcasting.
3. Goals of the new System
Goals of the new system:
- Collect information from more than 1600 observers all over the country;
- Check the information that arrives form the observes for inconsistencies and errors;
- Put all the information on a centralize database system for reporting features;
- Communicate automatically with the observers for errors that can not be resolved by the mathematics logic;
4. ActiveXperts SMS Messaging Server Solution
In this project we will get use of the ActiveXperts Messaging Server and SMS technology to get information regarding the participation during the voting g process and the results online as they are counted on the voting centers. Using SMS is an efficient and more economic way of implementing this system rather than the GPRS because:
- there is no need to provide GPRS service to all the users;
- there is no need to provide GPRS capable devices to all the users;
- SMS service is well known and friendly to all the users so there is no need for training on how to use;
- SMS service is tested and implemented for long time by the GSM operator and there is no risk in implementing this technology;
The project intends to spread up to 1600 users to monitor the voting process and give data about its progress and by the end the same users will give results from each respective voting center. This projects aims to cover 1600 voting centers. The information given by the users will contain:
whether the Voting Center is opened or not; how the voting process is going, whether it is normal or it has some problems, or it is conflictive or it is blocked; how the participation is; Voting Center is closed; Every information after this one will be considered as information regarding results so it will be interpreted like that from the system.
All this information that the users will send will be stored on the database of the conferencing room/premises. The link used with the GSM provider was primary a SMPP link over a VPN connection with the GSM network (Figure 1):
Figure 1
As a backup or by using GSM modems, this way the GSM provider sends the messages to the GSM modems and database server stores them accordingly (Figure 2).
Figure 2
The ActiveXperts server automatically switches between the two connection in case one of them fails.
After that the messages have arrived at the server they are processed appropriately based on the structure of the message itself, based on the Mobile phone number who sent the message and also based on the time when the message is sent.
The entire process flows from the user to the database of the server. The user initiates the process with sending an SMS message based on what he sees on the Voting Center. This message is sent to the GSM provider network, which sends this message to our servers. The message then is processed accordingly to the graphics and video generating modules.
Once this graphs are generated form the values coming form the database they are ready to be processed in two different ways: one way will visualize this graphics in Video for the conference monitors and for the Medias, the other way is for processing them to the website where can be seen by every internet user.
This process will continue as soon as the last Voting Center is counted and the Video streams and Web pages will stay updated with the latest information coming form the SMS users. The entire information whether video, data or web pages will be streamed online as long as there is a request for them.
5. Source code
The source code of the system:
'// ========================================================================
'// ProcessParticipation.vbs
'// ------------------------------------------------------------------------
'// Author : Augustus De Vree
'// Company: ActiveXperts BV - Uela.it
'// Date : 2007/10/27
'// Purpose: SMS Messaging Server Trigger to handle sms participation for the
'// Parliamentary Elections Kosovo 2007
'// History: 2007/11/06: ADV: New localized error strings
'// : 2007/11/07: ADV: Added the date to logfile names
'// : 2007/11/09: ADV: Check only last 8 digits of the phone number
'// : 2007/11/12: ADV: Added switch to send email or not
'// Checking the maximum number of participants/registered users
'// : 2007/11/16: ADV: Changed various CInt's to CDbl
'// Added switch to send sms or not
'// Added switch to place error sms's in failedSms table
'// Added constant with Sms Send channel
'// ========================================================================
'// --------------------------------------------------
'// OPTION EXPLICIT
'// --------------------------------------------------
Option Explicit
'// --------------------------------------------------
'// CONSTANTS: GENERAL
'// --------------------------------------------------
CONST SEND_EMAIL = False '// 20071112: ADV: SEND EMAIL? TRUE/FALSE
CONST MAX_NUMBER_VOTES = 1400 '// 20071112: ADV: MAX NUMBER OF PARTICIPANTS/REGISTERED USERS ALLOWED
CONST DEBUGGER_ENABLED = True
CONST SMS_SEND_CHANNEL = 2002 '// 20071116: ADV: SMS SEND CHANNEL
CONST SEND_SMS = False '// 20071116: ADV: SEND SMS? TRUE/FALSE
CONST USE_FAILED_SMS_TABLE = True '// 20071116: ADV: USE FAILED SMS TABLE? TRUE/FALSE
'// 20071116: ADV: ARRAY OF ERRORS WHICH SHOULD HAVE THE STATUS IN THE FAILED SMS TABLE
'// SET TO 1: SEND EMAIL. NORMAL IS 0: TO BE CORRECTED AND PROCESSED
CONST ARR_SMS_ERROR_BOUNCE = ""
'// --------------------------------------------------
'// CONSTANTS: ERROR MESSAGES
'// --------------------------------------------------
'CONST MSG_ELECTION_TYPE_UNKNOWN = "Error [101] processing sms: Sms not starting with a zero"
'CONST MSG_PHONENUMBER_NOT_ALLOWED = "Error [111] processing sms: Phone number is not allowed to send participation"
'CONST MSG_SMS_NOT_COMPLETE = "Error [122] processing sms: Sms message not complete"
'CONST MSG_NUMBER_VOTERS_NOT_NUMERICAL = "Error [133] processing sms: The number of voters who voted is not numerical"
'CONST MSG_TOTAL_REGISTERED_VOTERS_NOT_NUMERICAL = "Error [134] processing sms: The number of registered voters is not numerical"
'CONST MSG_VOTERS_MORE_THAN_ALLOWED = "Error [135] processing sms: The number of voters is more than the number of voters allowed"
'CONST MSG_TOO_MANY_VOTES = "Error [136] processing sms: Too many participants received (>MAX_NUMBER_VOTES)"
'CONST MSG_VOTING_PLACE_UNKNOWN = "Error [141] processing sms: Voting place (VP) unknown"
'CONST MSG_PARTY_VOTES_ERROR = "Error [151] processing sms: One of the party-votes parts not correct"
CONST MSG_ELECTION_TYPE_UNKNOWN = "Gabim (101) : Sms per pjesemarrjen duhet te filloje me 0"
CONST MSG_PHONENUMBER_NOT_ALLOWED = "Gabim (111) : Ky numer telefoni nuk lejohet të dergojë mesazhe per pjesemarrjen"
CONST MSG_SMS_NOT_COMPLETE = "Gabim (122) : Mesazhi SMS jo i plote"
CONST MSG_NUMBER_VOTERS_NOT_NUMERICAL = "Gabim (133) : Numri i votueseve që kane marre pjese nuk eshte i sakte"
CONST MSG_TOTAL_REGISTERED_VOTERS_NOT_NUMERICAL = "Gabim (134) : Numri i votuesve te regjistruar në VV eshte shkruar gabim"
CONST MSG_VOTERS_MORE_THAN_ALLOWED = "Gabim (135) : Numri i pjesemarresve me i madh se numri i votuesve te regjistruar"
CONST MSG_TOO_MANY_VOTES = "Gabim (136) : Numuri i votave te derguara me i madh se numuri i votuesve te regjitsruar"
CONST MSG_VOTING_PLACE_UNKNOWN = "Gabim (141) : Vendvotimi jo i sakte"
CONST MSG_PARTY_VOTES_ERROR = "Gabim (151) : Nje nga votat e subjekteve nuk eshte shkruar saktesisht"
'// --------------------------------------------------
'// CONSTANTS: SQL SERVER
'// --------------------------------------------------
CONST SQL_SERVER = "servername"
CONST SQL_DATABASE = "database"
CONST SQL_USERNAME = "username"
CONST SQL_PASSWORD = "password"
'// --------------------------------------------------
'// CONSTANTS: ALERT EMAIL
'// --------------------------------------------------
CONST ALERT_EMAIL_RECIPIENTS = "ifeskosovo@istitech.net"
'// --------------------------------------------------
'// CONSTANTS: DEBUG
'// --------------------------------------------------
Dim DebugFile
CONST DEBUG_FILE_BASE = "C:\Program Files\ActiveXperts\SMS Messaging Server\Projects\Parliamentary Elections Kosovo\Logs\"
DebugFile = DEBUG_FILE_BASE & Year(Date()) & Right("0" & Month(Date()), 2) & Right("0" & Day(Date()), 2) & "_" & "ProcessParticipation.txt"
'// --------------------------------------------------
'// GLOBAL OBJECTS
'// --------------------------------------------------
Dim gObjMessageDB, gObjDebugger, gObjConstants
Set gObjConstants = CreateObject("AxSmsServer.Constants")
Set gObjMessageDB = CreateObject("AxSmsServer.MessageDB")
Set gObjDebugger = CreateObject("ActiveXperts.VbDebugger")
'// --------------------------------------------------
'// SET DEBUG FILE - FOR TROUBLESHOOTING PURPOSES
'// --------------------------------------------------
gObjDebugger.DebugFile = DebugFile
gObjDebugger.Enabled = DEBUGGER_ENABLED
'// ========================================================================
'// FUNCTION: PROCESSMESSAGE
'// ------------------------------------------------------------------------
'// PROCESSMESSAGE TRIGGER FUNCTION TO PROCESS INCOMING MESSAGES
'// ========================================================================
Function ProcessMessage(numMessageID)
gObjDebugger.WriteLine DateTime() & " --------------------------------------------------"
gObjDebugger.WriteLine DateTime() & " ProcessMessage: numMessageID: " & numMessageID
gObjDebugger.WriteLine DateTime() & " ProcessMessage: Enter function"
'// --------------------------------------------------
'// OPEN THE MESSAGE DATABASE
'// --------------------------------------------------
gObjMessageDB.Open
If(gObjMessageDB.LastError <> 0) Then
gObjDebugger.WriteLine DateTime() & " ProcessMessage: ERROR: Unable to open database, result: [" & gObjMessageDB.LastError & "]"
Exit Function
End If
'// --------------------------------------------------
'// RETRIEVE THE MESSAGE THAT HAS JUST BEEN RECEIVED.
'// IF IT FAILS THEN EXIT SCRIPT
'// --------------------------------------------------
Dim objMessageIn
Set objMessageIn = gObjMessageDB.FindFirstMessage("ID = " & numMessageID)
If gObjMessageDB.LastError <> 0 Then
gObjMessageDB.Close
gObjDebugger.WriteLine DateTime() & " ProcessMessage: ERROR: Unable to locate message, result: [" & gObjMessageDB.LastError & "]"
Exit Function
End If
'// --------------------------------------------------
'// SET INCOMING SMS MESSAGE STATUS TO: SUCCESS
'// --------------------------------------------------
objMessageIn.Status = gObjConstants.MESSAGESTATUS_SUCCESS
gObjMessageDB.Save objMessageIn
gObjDebugger.WriteLine DateTime() & " ProcessMessage: Incoming message saved, result: [" & gObjMessageDB.LastError & "]"
'// --------------------------------------------------
'// PROCESS VOTES
'// --------------------------------------------------
ProcessParticipation(objMessageIn)
'// --------------------------------------------------
'// CLOSE THE MESSAGE DATABASE
'// --------------------------------------------------
gObjMessageDB.Close
gObjDebugger.WriteLine DateTime() & " ProcessMessage: Exit function"
End Function
' // ========================================================================
' // FUNCTION: PROCESSPARTICIPATION
' // ------------------------------------------------------------------------
' // VALIDATES SMS AND PROCESSES PARTICIPATION
' // ========================================================================
Function ProcessParticipation(objMessageIn)
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: Enter function"
'// --------------------------------------------------
'// CONNECTION STRING
'// --------------------------------------------------
Dim strConn
strConn = "Driver={MySQL ODBC 3.51 Driver};Server=" & SQL_SERVER & ";Database=" & SQL_DATABASE & ";User=" & SQL_USERNAME & _
";Password=" & SQL_PASSWORD & ";Option=3;"
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: strConn " & strConn
'// --------------------------------------------------
'// CREATE CONNECTION OBJECT
'// --------------------------------------------------
Dim objConn, strSQL, RS
Set objConn = CreateObject("ADODB.Connection")
objConn.Open(strConn)
'// --------------------------------------------------
'// CHECK IF SENDER IS ALLOWED TO SEND
'// --------------------------------------------------
gObjDebugger.WriteLine DateTime() & " ProcessVotes: objMessageIn.Sender = " & objMessageIn.Sender
Dim senderAllowed : senderAllowed = False
'// strSQL = "SELECT COUNT(*) AS `senderAllowed` FROM `mobile_users` WHERE `mobile_phone_no` = '" & objMessageIn.Sender & "' "
'// 20071109: ADV: CHECK ONLY LAST 8 DIGITS OF THE PHONE NUMBER
strSQL = "SELECT COUNT(*) AS `senderAllowed` FROM `mobile_users` WHERE `mobile_phone_no` LIKE '%" & Right(objMessageIn.Sender, 8) & "' "
gObjDebugger.WriteLine DateTime() & " ProcessVotes: strSQL = " & Replace(strSQL, "%", "%%")
Set RS = objConn.Execute(strSQL)
senderAllowed = (CInt(RS(0)) <> 0)
RS.Close : Set RS = Nothing
gObjDebugger.WriteLine DateTime() & " ProcessVotes: senderAllowed: " & senderAllowed
If senderAllowed = False Then 'SENDER IS NOT ALLOWED
gObjDebugger.WriteLine DateTime() & " " & MSG_PHONENUMBER_NOT_ALLOWED
Call SendMessage(objConn, objMessageIn, MSG_PHONENUMBER_NOT_ALLOWED)
Exit Function
End If
'// --------------------------------------------------
'// PROCESS BODY
'// --------------------------------------------------
Dim strBody, arrBody
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: objMessageIn.Body = " & objMessageIn.Body
strBody = objMessageIn.Body
' ELIMINATE DOUBLE SPACES
If strBody <> "" Then Do While Instr(strBody, " ") <> 0 : strBody = Replace(strBody, " ", " ") : Loop
arrBody = Split(strBody, " ")
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: Sms body parts: " & UBound(arrBody)
'// --------------------------------------------------
'// CHECK ON TOTAL BODY PARTS (4 PARTS)
'// --------------------------------------------------
If UBound(arrBody) <> 3 Then 'SMS IS NOT COMPOSED OF 4 SECTIONS
gObjDebugger.WriteLine DateTime() & " " & MSG_SMS_NOT_COMPLETE
Call SendMessage(objConn, objMessageIn, MSG_SMS_NOT_COMPLETE)
Exit Function
End If
'// --------------------------------------------------
'// NAME THE SMS BODY PARTS
'// --------------------------------------------------
Dim intElectionType, strVP, intTotal, intTotalReg
intElectionType = arrBody(0)
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: intElectionType: " & intElectionType
strVP = arrBody(1)
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: strVP: " & strVP
intTotal = arrBody(2)
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: intTotal: " & intTotal
intTotalReg = arrBody(3)
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: intTotalReg: " & intTotalReg
'// --------------------------------------------------
'// CHECK IF NUMBER OF VOTERS IS NUMERICAL
'// --------------------------------------------------
If IsNumeric(intTotal) = False Then
gObjDebugger.WriteLine DateTime() & " " & MSG_NUMBER_VOTERS_NOT_NUMERICAL
Call SendMessage(objConn, objMessageIn, MSG_NUMBER_VOTERS_NOT_NUMERICAL)
Exit Function
'// --------------------------------------------------
'// 20071112: ADV: CHECK IF NUMBER OF VOTERS IS MORE THAN MAX_NUMBER_VOTES
'// 20071116: ADV: CHANGED CINT TO CDBL
'// --------------------------------------------------
ElseIf CDbl(intTotal) > MAX_NUMBER_VOTES Then
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: intTotal: " & intTotal
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: MAX_NUMBER_VOTES: " & MAX_NUMBER_VOTES
gObjDebugger.WriteLine DateTime() & " " & MSG_TOO_MANY_VOTES
Call SendMessage(objConn, objMessageIn, MSG_TOO_MANY_VOTES)
Exit Function
End If
'// --------------------------------------------------
'// CHECK IF NUMBER OF REGISTERED VOTERS IS NUMERICAL
'// --------------------------------------------------
If IsNumeric(intTotalReg) = False Then
gObjDebugger.WriteLine DateTime() & " " & MSG_TOTAL_REGISTERED_VOTERS_NOT_NUMERICAL
Call SendMessage(objConn, objMessageIn, MSG_TOTAL_REGISTERED_VOTERS_NOT_NUMERICAL)
Exit Function
'// --------------------------------------------------
'// 20071112: ADV: CHECK IF NUMBER OF REGISTERED VOTERS IS MORE THAN MAX_NUMBER_VOTES
'// 20071116: ADV: CHANGED CINT TO CDBL
'// --------------------------------------------------
ElseIf CDbl(intTotalReg) > MAX_NUMBER_VOTES Then
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: intTotalReg: " & intTotalReg
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: MAX_NUMBER_VOTES: " & MAX_NUMBER_VOTES
gObjDebugger.WriteLine DateTime() & " " & MSG_TOO_MANY_VOTES
Call SendMessage(objConn, objMessageIn, MSG_TOO_MANY_VOTES)
Exit Function
End If
'// --------------------------------------------------
'// CHECK IF VOTERS IS MORE THAN ALLOWED
'// --------------------------------------------------
If intTotal > intTotalReg Then
gObjDebugger.WriteLine DateTime() & " " & MSG_VOTERS_MORE_THAN_ALLOWED
Call SendMessage(objConn, objMessageIn, MSG_VOTERS_MORE_THAN_ALLOWED)
Exit Function
End If
'// --------------------------------------------------
'// SELECT TABLE NAME BASED ON ELECTION TYPE
'// --------------------------------------------------
Dim strTableName
Select Case intElectionType
Case 0
strTableName = "participation"
'// NOT USED: THIS IS ALREADY HANDLED AT SMS SERVER LEVEL WHICK INVOCES THE TRIGGER
Case Else
gObjDebugger.WriteLine DateTime() & " " & MSG_ELECTION_TYPE_UNKNOWN
Call SendMessage(objConn, objMessageIn, MSG_ELECTION_TYPE_UNKNOWN)
Exit Function
End Select
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: strTableName: " & strTableName
'// --------------------------------------------------
'// CHECK IF VOTING PLACE EXISTS
'// --------------------------------------------------
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: strVP = " & strVP
Dim existsVP : existsVP = False
strSQL = "SELECT COUNT(*) AS `existsVP` FROM `" & strTableName & "` WHERE `VP` = '" & strVP & "' "
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: strSQL = " & strSQL
Set RS = objConn.Execute(strSQL)
existsVP = (CInt(RS(0)) <> 0)
RS.Close : Set RS = Nothing
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: existsVP: " & existsVP
If existsVP = False Then 'VOTING PLACE NOT FOUND
gObjDebugger.WriteLine DateTime() & " " & MSG_VOTING_PLACE_UNKNOWN
Call SendMessage(objConn, objMessageIn, MSG_VOTING_PLACE_UNKNOWN)
Exit Function
End If
' '// --------------------------------------------------
' '// ZERO ALL TABLES
' '// --------------------------------------------------
' strSQL = "UPDATE `participation` SET "
' strSQL = strSQL & " `participation` = 0, "
' strSQL = strSQL & " `total_voters` = 0 "
' objConn.execute(strSQL)
'// --------------------------------------------------
'// FINALY UPDATE TABLE RECORD
'// --------------------------------------------------
strSQL = "UPDATE `" & strTableName & "` SET "
strSQL = strSQL & " `participation` = " & intTotal & ", "
strSQL = strSQL & " `total_voters` = " & intTotalReg & " "
strSQL = strSQL & " WHERE `VP` = '" & strVP & "' "
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: strSQL = " & strSQL
objConn.execute(strSQL)
'// --------------------------------------------------
'// CLOSE DATABASE CONNECTION
'// --------------------------------------------------
objConn.Close
Set objConn = Nothing
gObjDebugger.WriteLine DateTime() & " ProcessParticipation: Exit function"
End Function
'// ==================================================
'// SEND MESSAGE
'// ==================================================
Function SendMessage(objConn, objMessageIn, strMsg)
gObjDebugger.WriteLine DateTime() & " SendMessage: Enter function"
'// --------------------------------------------------
'// SEND MESSAGE
'// --------------------------------------------------
Dim objMessageOut
Set objMessageOut = gObjMessageDB.Create
If(gObjMessageDB.LastError = 0) Then
'// --------------------------------------------------
'// 20071116: ADV: INSERT INTO FAILED SMS TABLE?
'// --------------------------------------------------
gObjDebugger.WriteLine DateTime() & " SendMessage: USE_FAILED_SMS_TABLE: " & USE_FAILED_SMS_TABLE
If USE_FAILED_SMS_TABLE = True Then
Dim smsBody
smsBody = objMessageIn.Body
If smsBody <> "" Then
smsBody = Left(Replace(smsBody, "'", "''"), 255)
Else
smsBody = " "
End If
Dim sError : sError = strMsg
If sError <> "" then sError = Left(Replace(sError, "'", "''"), 255)
Dim strSQL
strSQL = "INSERT INTO `failedsms` (`Sender` , `Sms` , `Error` , `Status` , `CreationDate` , `IDSms`, `Recipient`) VALUES ("
strSQL = strSQL & " '" & objMessageIn.Sender & "', "
strSQL = strSQL & " '" & smsBody & "', "
strSQL = strSQL & " '" & sError & "', "
Dim errFound : errFound = False
Dim arrSmsError : arrSmsError = Split(ARR_SMS_ERROR_BOUNCE, "|")
Dim a : For a = 0 To UBound(arrSmsError)
If InStr(sError, "[" & arrSmsError(a) & "]") <> 0 Then errFound = True
Next
If errFound = False Then
strSQL = strSQL & " '0', "
Else
strSQL = strSQL & " '1', "
End If
strSQL = strSQL & " '" & Year(Date()) & "-" & Right("0" & Month(Date()), 2) & "-" & Right("0" & Day(Date()), 2) & _
" " & Right("0" & Hour(Now()), 2) & ":" & Right("0" & Minute(Now()), 2) & ":" & Right("0" & Second(Now()), 2) & "', "
strSQL = strSQL & " '" & objMessageIn.ID & "', "
strSQL = strSQL & " '" & objMessageIn.Recipient & "' "
strSQL = strSQL & " )"
gObjDebugger.WriteLine DateTime() & " ProcessVotes: strSQL = " & strSQL
objConn.Execute(strSQL)
End If
'// --------------------------------------------------
'// 20071116: ADV: SEND SMS?
'// --------------------------------------------------
gObjDebugger.WriteLine DateTime() & " SendMessage: SEND_SMS: " & SEND_SMS
If SEND_SMS = True Then
'// SMS MESSAGE OUT
objMessageOut.Direction = gObjConstants.MESSAGEDIRECTION_OUT
objMessageOut.Type = gObjConstants.MESSAGETYPE_SMS
objMessageOut.Status = gObjConstants.MESSAGESTATUS_PENDING
objMessageOut.ChannelID = SMS_SEND_CHANNEL
objMessageOut.Recipient = objMessageIn.Sender
objMessageOut.Body = strMsg
gObjMessageDB.Save(objMessageOut)
Set objMessageOut = Nothing
gObjDebugger.WriteLine DateTime() & " SendMessage: Outgoing Sms Message saved"
End If
'// --------------------------------------------------
'// 20071112: ADV: SEND EMAIL?
'// --------------------------------------------------
gObjDebugger.WriteLine DateTime() & " SendMessage: SEND_EMAIL: " & SEND_EMAIL
If SEND_EMAIL = True Then
'// EMAIL MESSAGE OUT
Set objMessageOut = gObjMessageDB.Create
objMessageOut.Direction = gObjConstants.MESSAGEDIRECTION_OUT
objMessageOut.Type = gObjConstants.MESSAGETYPE_EMAIL
objMessageOut.Status = gObjConstants.MESSAGESTATUS_PENDING
objMessageOut.ChannelID = 0
objMessageOut.Recipient = ALERT_EMAIL_RECIPIENTS
objMessageOut.Subject = "[SMS Server] " & strMsg
Dim strBody
strBody = "Message : " & strMsg & "." & vbCrLf
strBody = strBody & "Phone number: " & objMessageIn.Sender & vbCrLf
strBody = strBody & "Sms : " & objMessageIn.Body & vbCrLf & vbCrLf
objMessageOut.Body = strBody
gObjMessageDB.Save(objMessageOut)
Set objMessageOut = Nothing
gObjDebugger.WriteLine DateTime() & " SendMessage: Outgoing Email Message saved"
End If
Else
gObjDebugger.WriteLine DateTime() & " SendMessage: gObjMessageDB.LastError <> 0"
gObjDebugger.WriteLine DateTime() & " SendMessage: ERROR: Unable to create message"
End If
gObjDebugger.WriteLine DateTime() & " SendMessage: Exit function"
End Function
'// ==================================================
'// GET DATE & TIME (FOR DEBUGGING)
'// ==================================================
Function DateTime()
DateTime = Year(Date()) & "/" & Right("0" & Month(Date()), 2) & "/" & Right("0" & Day(Date()), 2) & " " & _
Right("0" & Hour(Time()), 2) & ":" & Right("0" & Minute(Time()), 2) & ":" & Right("0" & Second(Time()), 2)
End Function
'// ========================================================================
'// ProcessVotes.vbs
'// ------------------------------------------------------------------------
'// Author : Augustus De Vree
'// Company: ActiveXperts BV - Uela.it
'// Date : 2007/10/20
'// Purpose: SMS Messaging Server Trigger to handle sms votes for the
'// Parliamentary Elections Kosovo 2007
'// History: 2007/11/06: ADV: New localized error strings
'// : 2007/11/07: ADV: Added the date to logfile names
'// : 2007/11/09: ADV: Check only last 8 digits of the phone number
'// : 2007/11/12: ADV: Added switch to send email or not
'// Checking the maximum number of votes/total votes allowed
'// : 2007/11/16: ADV: Changed various CInt's to CDbl
'// Added switch to send sms or not
'// Added switch to place error sms's in failedSms table
'// Added constant with Sms Send channel
'// ========================================================================
'// --------------------------------------------------
'// OPTION EXPLICIT
'// --------------------------------------------------
Option Explicit
'// --------------------------------------------------
'// CONSTANTS: GENERAL
'// --------------------------------------------------
CONST SEND_EMAIL = False '// 20071112: ADV: SEND EMAIL? TRUE/FALSE
CONST MAX_NUMBER_VOTES = 1400 '// 20071112: ADV: MAX NUMBER OF PARTY VOTES/TOTAL VOTES ALLOWED
CONST DEBUGGER_ENABLED = True
CONST SMS_SEND_CHANNEL = 2002 '// 20071116: ADV: SMS SEND CHANNEL
CONST SEND_SMS = False '// 20071116: ADV: SEND SMS? TRUE/FALSE
CONST USE_FAILED_SMS_TABLE = True '// 20071116: ADV: USE FAILED SMS TABLE? TRUE/FALSE
'// 20071116: ADV: ARRAY OF ERRORS WHICH SHOULD HAVE THE STATUS IN THE FAILED SMS TABLE
'// SET TO 1: SEND EMAIL. NORMAL IS 0: TO BE CORRECTED AND PROCESSED
CONST ARR_SMS_ERROR_BOUNCE = ""
'// --------------------------------------------------
'// CONSTANTS: ERROR MESSAGES
'// --------------------------------------------------
'CONST MSG_ELECTION_TYPE_UNKNOWN = "Error [101] processing sms: Election type unknown (should be 1, 2 or 3)"
'CONST MSG_PHONENUMBER_NOT_ALLOWED = "Error [111] processing sms: Phone number is not allowed to send votes"
'CONST MSG_SMS_MISSING_PARTS = "Error [121] processing sms: Sms message not complete"
'CONST MSG_LAST_PART_NOT_NUMERIC = "Error [131] processing sms: The total of votes (last part of sms) is not numeric"
'CONST MSG_TOO_MANY_VOTES = "Error [136] processing sms: Too many votes/total of votes received (>MAX_NUMBER_VOTES)"
'CONST MSG_VOTING_PLACE_UNKNOWN = "Error [141] processing sms: Voting place (VP) unknown"
'CONST MSG_PARTY_VOTES_ERROR = "Error [151] processing sms: One of the party-votes parts not correct"
'CONST MSG_PARTY_NOT_NUMERIC = "Error [155] processing sms: The party portion of a party-votes part is not numeric"
'CONST MSG_VOTES_NOT_NUMERIC = "Error [156] processing sms: The votes portion of a party-votes part is not numeric"
'CONST MSG_PARTY_NOT_ALLOWED = "Error [160] processing sms: The party is not allowed for this election type"
CONST MSG_ELECTION_TYPE_UNKNOWN = "Gabim (101) : Lloji i zgjedhjeve i panjohur. Duhet te jete 1, 2 ose 3"
CONST MSG_PHONENUMBER_NOT_ALLOWED = "Gabim (111) : Ky numer telefoni nuk lejohet të dergojë mesazhe"
CONST MSG_SMS_MISSING_PARTS = "Gabim (121) : Mesazhi SMS jo i plote"
CONST MSG_LAST_PART_NOT_NUMERIC = "Gabim (131) : Numri total i votave (pjesa e fundit e SMS) nuk është shifer"
CONST MSG_TOO_MANY_VOTES = "Gabim (136) : Numuri i votave te derguara me i madh se numuri i votuesve te regjitsruar"
CONST MSG_VOTING_PLACE_UNKNOWN = "Gabim (141) : Vendvotimi jo i sakte"
CONST MSG_PARTY_VOTES_ERROR = "Gabim (151) : Nje nga votat e subjekteve nuk eshte shkruar saktesisht"
CONST MSG_PARTY_NOT_NUMERIC = "Gabim (155) : Kodi i subjektit politik nuk eshte shkruar saktesisht"
CONST MSG_VOTES_NOT_NUMERIC = "Gabim (156) : Votat e subjektit politik nuk jane shkruar saktesisht"
CONST MSG_PARTY_NOT_ALLOWED = "Gabim (160) : Kjo parti nuk kandidon per kete lloj votimi"
'// --------------------------------------------------
'// CONSTANTS: SQL SERVER
'// --------------------------------------------------
CONST SQL_SERVER = "servername"
CONST SQL_DATABASE = "database"
CONST SQL_USERNAME = "username"
CONST SQL_PASSWORD = "password"
'// --------------------------------------------------
'// CONSTANTS: ALERT EMAIL
'// --------------------------------------------------
CONST ALERT_EMAIL_RECIPIENTS = "ifeskosovo@istitech.net"
'// --------------------------------------------------
'// CONSTANTS: DEBUG
'// --------------------------------------------------
Dim DebugFile
CONST DEBUG_FILE_BASE = "C:\Program Files\ActiveXperts\SMS Messaging Server\Projects\Parliamentary Elections Kosovo\Logs\"
DebugFile = DEBUG_FILE_BASE & Year(Date()) & Right("0" & Month(Date()), 2) & Right("0" & Day(Date()), 2) & "_" & "ProcessVotes.txt"
'// --------------------------------------------------
'// GLOBAL OBJECTS
'// --------------------------------------------------
Dim gObjMessageDB, gObjDebugger, gObjConstants
Set gObjConstants = CreateObject("AxSmsServer.Constants")
Set gObjMessageDB = CreateObject("AxSmsServer.MessageDB")
Set gObjDebugger = CreateObject("ActiveXperts.VbDebugger")
'// --------------------------------------------------
'// SET DEBUG FILE - FOR TROUBLESHOOTING PURPOSES
'// --------------------------------------------------
gObjDebugger.DebugFile = DebugFile
gObjDebugger.Enabled = DEBUGGER_ENABLED
'// ========================================================================
'// FUNCTION: PROCESSMESSAGE
'// ------------------------------------------------------------------------
'// PROCESSMESSAGE TRIGGER FUNCTION TO PROCESS INCOMING MESSAGES
'// ========================================================================
Function ProcessMessage(numMessageID)
gObjDebugger.WriteLine DateTime() & " --------------------------------------------------"
gObjDebugger.WriteLine DateTime() & " ProcessMessage: numMessageID: " & numMessageID
gObjDebugger.WriteLine DateTime() & " ProcessMessage: Enter function"
'// --------------------------------------------------
'// OPEN THE MESSAGE DATABASE
'// --------------------------------------------------
gObjMessageDB.Open
If(gObjMessageDB.LastError <> 0) Then
gObjDebugger.WriteLine DateTime() & " ProcessMessage: ERROR: Unable to open database, result: [" & gObjMessageDB.LastError & "]"
Exit Function
End If
'// --------------------------------------------------
'// RETRIEVE THE MESSAGE THAT HAS JUST BEEN RECEIVED.
'// IF IT FAILS THEN EXIT SCRIPT
'// --------------------------------------------------
Dim objMessageIn
Set objMessageIn = gObjMessageDB.FindFirstMessage("ID = " & numMessageID)
If gObjMessageDB.LastError <> 0 Then
gObjMessageDB.Close
gObjDebugger.WriteLine DateTime() & " ProcessMessage: ERROR: Unable to locate the message, result: [" & gObjMessageDB.LastError & "]"
Exit Function
End If
'// --------------------------------------------------
'// SET INCOMING SMS MESSAGE STATUS TO: SUCCESS
'// --------------------------------------------------
objMessageIn.Status = gObjConstants.MESSAGESTATUS_SUCCESS
gObjMessageDB.Save objMessageIn
gObjDebugger.WriteLine DateTime() & " ProcessMessage: Incoming message saved, result: [" & gObjMessageDB.LastError & "]"
'// --------------------------------------------------
'// PROCESS VOTES
'// --------------------------------------------------
ProcessVotes(objMessageIn)
'// --------------------------------------------------
'// CLOSE THE MESSAGE DATABASE
'// --------------------------------------------------
gObjMessageDB.Close
gObjDebugger.WriteLine DateTime() & " ProcessMessage: Exit function"
End Function
' // ========================================================================
' // FUNCTION: PROCESSVOTES
' // ------------------------------------------------------------------------
' // VALIDATES SMS AND PROCESSES VOTES
' // ========================================================================
Function ProcessVotes(objMessageIn)
gObjDebugger.WriteLine DateTime() & " ProcessVotes: Enter function"
'// --------------------------------------------------
'// CONNECTION STRING
'// --------------------------------------------------
Dim strConn
strConn = "Driver={MySQL ODBC 3.51 Driver};Server=" & SQL_SERVER & ";Database=" & SQL_DATABASE & ";User=" & SQL_USERNAME & _
";Password=" & SQL_PASSWORD & ";Option=3;"
gObjDebugger.WriteLine DateTime() & " ProcessVotes: strConn " & strConn
'// --------------------------------------------------
'// CREATE CONNECTION OBJECT
'// --------------------------------------------------
Dim objConn, strSQL, RS
Set objConn = CreateObject("ADODB.Connection")
objConn.Open(strConn)
'// --------------------------------------------------
'// CHECK IF SENDER IS ALLOWED TO SEND
'// --------------------------------------------------
gObjDebugger.WriteLine DateTime() & " ProcessVotes: objMessageIn.Sender = " & objMessageIn.Sender
Dim senderAllowed : senderAllowed = False
'// strSQL = "SELECT COUNT(*) AS `senderAllowed` FROM `mobile_users` WHERE `mobile_phone_no` = '" & objMessageIn.Sender & "' "
'// 20071109: ADV: CHECK ONLY LAST 8 DIGITS OF THE PHONE NUMBER
strSQL = "SELECT COUNT(*) AS `senderAllowed` FROM `mobile_users` WHERE `mobile_phone_no` LIKE '%" & Right(objMessageIn.Sender, 8) & "' "
gObjDebugger.WriteLine DateTime() & " ProcessVotes: strSQL = " & Replace(strSQL, "%", "%%")
Set RS = objConn.Execute(strSQL)
senderAllowed = (CInt(RS(0)) <> 0)
RS.Close : Set RS = Nothing
gObjDebugger.WriteLine DateTime() & " ProcessVotes: senderAllowed: " & senderAllowed
If senderAllowed = False Then 'SENDER IS NOT ALLOWED
gObjDebugger.WriteLine DateTime() & " " & MSG_PHONENUMBER_NOT_ALLOWED
Call SendMessage(objConn, objMessageIn, MSG_PHONENUMBER_NOT_ALLOWED)
Exit Function
End If
'// --------------------------------------------------
'// PROCESS BODY
'// --------------------------------------------------
Dim strBody, arrBody
gObjDebugger.WriteLine DateTime() & " ProcessVotes: objMessageIn.Body = " & objMessageIn.Body
strBody = objMessageIn.Body
' ELIMINATE DOUBLE SPACES
If strBody <> "" Then Do While Instr(strBody, " ") <> 0 : strBody = Replace(strBody, " ", " ") : Loop
arrBody = Split(strBody, " ")
gObjDebugger.WriteLine DateTime() & " ProcessVotes: Sms body parts: " & UBound(arrBody)
'// --------------------------------------------------
'// CHECK ON TOTAL BODY PARTS (MINIMUM 4 PARTS, SO 3)
'// --------------------------------------------------
If UBound(arrBody) < 3 Then 'SMS IS NOT COMPOSED OF MINIMAL 4 SECTIONS
gObjDebugger.WriteLine DateTime() & " " & MSG_SMS_MISSING_PARTS
Call SendMessage(objConn, objMessageIn, MSG_SMS_MISSING_PARTS)
Exit Function
End If
'// --------------------------------------------------
'// NAME THE SMS BODY PARTS
'// --------------------------------------------------
Dim intElectionType, strVP, intTotal
intElectionType = arrBody(0)
gObjDebugger.WriteLine DateTime() & " ProcessVotes: intElectionType: " & intElectionType
strVP = arrBody(1)
gObjDebugger.WriteLine DateTime() & " ProcessVotes: strVP: " & strVP
intTotal = arrBody(UBound(arrBody))
gObjDebugger.WriteLine DateTime() & " ProcessVotes: intTotal: " & intTotal
'// --------------------------------------------------
'// CHECK IF LAST PART IS NUMERIC (TOTAL)
'// --------------------------------------------------
If IsNumeric(intTotal) = False Then 'LAST PART (TOTAL) IS NOT NUMERIC
gObjDebugger.WriteLine DateTime() & " " & MSG_LAST_PART_NOT_NUMERIC
Call SendMessage(objConn, objMessageIn, MSG_LAST_PART_NOT_NUMERIC)
Exit Function
'// --------------------------------------------------
'// 20071112: ADV: CHECK IF LAST PART IS MORE THAN MAX_NUMBER_VOTES (TOTAL)
'// 20071116: ADV: CHANGED CINT TO CDBL
'// --------------------------------------------------
ElseIf CDbl(intTotal) > MAX_NUMBER_VOTES Then
gObjDebugger.WriteLine DateTime() & " ProcessVotes: intTotal: " & intTotal
gObjDebugger.WriteLine DateTime() & " ProcessVotes: MAX_NUMBER_VOTES: " & MAX_NUMBER_VOTES
gObjDebugger.WriteLine DateTime() & " " & MSG_TOO_MANY_VOTES
Call SendMessage(objConn, objMessageIn, MSG_TOO_MANY_VOTES)
Exit Function
End If
'// --------------------------------------------------
'// SELECT TABLE NAME BASED ON ELECTION TYPE
'// --------------------------------------------------
Dim strTableName
Select Case intElectionType
Case 1
strTableName = "parliament"
Case 2
strTableName = "council"
Case 3
strTableName = "mayor"
'// NOT USED: THIS IS ALREADY HANDLED AT SMS SERVER LEVEL WHICK INVOCES THE TRIGGER
Case Else
gObjDebugger.WriteLine DateTime() & " " & MSG_ELECTION_TYPE_UNKNOWN
Call SendMessage(objConn, objMessageIn, MSG_ELECTION_TYPE_UNKNOWN)
Exit Function
End Select
gObjDebugger.WriteLine DateTime() & " ProcessVotes: strTableName: " & strTableName
'// --------------------------------------------------
'// CHECK IF VOTING PLACE EXISTS
'// --------------------------------------------------
gObjDebugger.WriteLine DateTime() & " ProcessVotes: strVP = " & strVP
Dim existsVP : existsVP = False
strSQL = "SELECT COUNT(*) AS `existsVP` FROM `" & strTableName & "` WHERE `VP` = '" & strVP & "' "
gObjDebugger.WriteLine DateTime() & " ProcessVotes: strSQL = " & strSQL
Set RS = objConn.Execute(strSQL)
existsVP = (CInt(RS(0)) <> 0)
RS.Close : Set RS = Nothing
gObjDebugger.WriteLine DateTime() & " ProcessVotes: existsVP: " & existsVP
If existsVP = False Then 'VOTING PLACE NOT FOUND
gObjDebugger.WriteLine DateTime() & " " & MSG_VOTING_PLACE_UNKNOWN
Call SendMessage(objConn, objMessageIn, MSG_VOTING_PLACE_UNKNOWN)
Exit Function
End If
'// --------------------------------------------------
'// FURTHER SPLIT PARTY-VOTES PARTS OF SMS BODY
'// --------------------------------------------------
Dim arrPartyVotes(126,1), arrParts, strSQLPartyCheck
Dim i : For i = 2 To UBound(arrBody) -1
arrParts = Split(arrBody(i), "-")
'// PARTY-VOTES PAIR SHOULD BE TWO (2)
If UBound(arrParts) <> 1 Then
gObjDebugger.WriteLine DateTime() & " " & MSG_PARTY_VOTES_ERROR
Call SendMessage(objConn, objMessageIn, MSG_PARTY_VOTES_ERROR)
Exit Function
'// PARTY-VOTES PARTY ELEMENT SHOULD BE NUMERIC
ElseIf IsNumeric(arrParts(0)) = False Then
gObjDebugger.WriteLine DateTime() & " " & MSG_PARTY_NOT_NUMERIC
Call SendMessage(objConn, objMessageIn, MSG_PARTY_NOT_NUMERIC)
Exit Function
'// PARTY-VOTES VOTES ELEMENT SHOULD BE NUMERIC
ElseIf IsNumeric(arrParts(1)) = False Then
gObjDebugger.WriteLine DateTime() & " " & MSG_VOTES_NOT_NUMERIC
Call SendMessage(objConn, objMessageIn, MSG_VOTES_NOT_NUMERIC)
Exit Function
'// 20071112: ADV: PARTY-VOTES VOTES ELEMENT SHOULD NOT BE MORE THAN MAX_NUMBER_VOTES
'// 20071116: ADV: CHANGED CINT TO CDBL
ElseIf CDbl(arrParts(1)) > MAX_NUMBER_VOTES Then
gObjDebugger.WriteLine DateTime() & " ProcessVotes: arrParts(1): " & arrParts(1)
gObjDebugger.WriteLine DateTime() & " ProcessVotes: MAX_NUMBER_VOTES: " & MAX_NUMBER_VOTES
gObjDebugger.WriteLine DateTime() & " " & MSG_TOO_MANY_VOTES
Call SendMessage(objConn, objMessageIn, MSG_TOO_MANY_VOTES)
Exit Function
End If
'// 20071109: ADV: DELETE LEFT ZERO DIGITS IN PARTY NUMBER
gObjDebugger.WriteLine DateTime() & " ProcessVotes: Party number pre processed: " & arrParts(0)
If Left(arrParts(0), 1) = "0" Then Do While Left(arrParts(0), 1) = "0" : arrParts(0) = Right(arrParts(0), Len(arrParts(0)) - 1) : Loop
gObjDebugger.WriteLine DateTime() & " ProcessVotes: Party number post processed: " & arrParts(0)
'// SAVE FOR LATER
arrPartyVotes(i, 0) = arrParts(0)
arrPartyVotes(i, 1) = arrParts(1)
'// BUILD SQL QUERY FOR ALLOWED PARTIES
If intElectionType = 1 Then '// PARLIAMENT
strSQLPartyCheck = strSQLPartyCheck & " (`partyID` = " & arrPartyVotes(i, 0) & " AND `Assembly` = 1) OR "
ElseIf intElectionType = 2 Then '// COUNCIL
'// 20071116: ADV: CHANGED CINT TO CDBL
strSQLPartyCheck = strSQLPartyCheck & " (`partyID` = " & arrPartyVotes(i, 0) & " AND `Commune" & CDbl(Left(strVP, 2)) & "` = 1) OR "
ElseIf intElectionType = 3 Then '// MAYOR
'// 20071116: ADV: CHANGED CINT TO CDBL
strSQLPartyCheck = strSQLPartyCheck & " (`partyID` = " & arrPartyVotes(i, 0) & " AND `Commune" & CDbl(Left(strVP, 2)) & "` = 1) OR "
End If
Next
If strSQLPartyCheck <> "" Then strSQLPartyCheck = Left(strSQLPartyCheck, Len(strSQLPartyCheck) - 3)
'// --------------------------------------------------
'// CHECK IF PARTIES ARE OK FOR THIS VOTING PLACE
'// --------------------------------------------------
Dim totalParties : totalParties = 0
strSQL = "SELECT COUNT(*) AS `totalParties` FROM `" & strTableName & "_parties` WHERE " & strSQLPartyCheck
gObjDebugger.WriteLine DateTime() & " ProcessVotes: strSQL = " & strSQL
Set RS = objConn.Execute(strSQL)
totalParties = CInt(RS(0))
RS.Close : Set RS = Nothing
gObjDebugger.WriteLine DateTime() & " ProcessVotes: UBound(arrBody) - 2: " & UBound(arrBody) - 2
gObjDebugger.WriteLine DateTime() & " ProcessVotes: totalParties: " & totalParties
If UBound(arrBody) - 2 <> totalParties Then
gObjDebugger.WriteLine DateTime() & " " & MSG_PARTY_NOT_ALLOWED
Call SendMessage(objConn, objMessageIn, MSG_PARTY_NOT_ALLOWED)
Exit Function
End If
' '// --------------------------------------------------
' '// ZERO ALL TABLES
' '// --------------------------------------------------
' strSQL = "UPDATE `parliament` SET "
' For i = 31 To 126
' strSQL = strSQL & " `P" & i & "` = 0, "
' Next
' strSQL = strSQL & " `Total` = 0 "
' objConn.execute(strSQL)
' strSQL = "UPDATE `council` SET "
' For i = 31 To 126
' strSQL = strSQL & " `P" & i & "` = 0, "
' Next
' strSQL = strSQL & " `Total` = 0 "
' objConn.execute(strSQL)
' strSQL = "UPDATE `mayor` SET "
' For i = 31 To 126
' strSQL = strSQL & " `P" & i & "` = 0, "
' Next
' strSQL = strSQL & " `Total` = 0 "
' objConn.execute(strSQL)
'// --------------------------------------------------
'// ZERO TABLE RECORD
'// --------------------------------------------------
strSQL = "UPDATE `" & strTableName & "` SET "
For i = 31 To 126
strSQL = strSQL & " `P" & i & "` = 0, "
Next
strSQL = strSQL & " `Total` = 0 "
strSQL = strSQL & " WHERE `VP` = '" & strVP & "' "
gObjDebugger.WriteLine DateTime() & " ProcessVotes: strSQL = " & strSQL
objConn.execute(strSQL)
'// --------------------------------------------------
'// FINALY UPDATE TABLE RECORD
'// --------------------------------------------------
strSQL = "UPDATE `" & strTableName & "` SET "
For i = 2 To UBound(arrBody) -1
strSQL = strSQL & " `P" & arrPartyVotes(i, 0) & "` = " & arrPartyVotes(i, 1) & ", "
Next
strSQL = strSQL & " `Total` = " & intTotal & " "
strSQL = strSQL & " WHERE `VP` = '" & strVP & "' "
gObjDebugger.WriteLine DateTime() & " ProcessVotes: strSQL = " & strSQL
objConn.execute(strSQL)
'// --------------------------------------------------
'// CLOSE DATABASE CONNECTION
'// --------------------------------------------------
objConn.Close
Set objConn = Nothing
gObjDebugger.WriteLine DateTime() & " ProcessVotes: Exit function"
End Function
'// ==================================================
'// SEND MESSAGE
'// ==================================================
Function SendMessage(objConn, objMessageIn, strMsg)
gObjDebugger.WriteLine DateTime() & " SendMessage: Enter function"
'// --------------------------------------------------
'// SEND MESSAGE
'// --------------------------------------------------
Dim objMessageOut
Set objMessageOut = gObjMessageDB.Create
If(gObjMessageDB.LastError = 0) Then
'// --------------------------------------------------
'// 20071116: ADV: INSERT INTO FAILED SMS TABLE?
'// --------------------------------------------------
gObjDebugger.WriteLine DateTime() & " SendMessage: USE_FAILED_SMS_TABLE: " & USE_FAILED_SMS_TABLE
If USE_FAILED_SMS_TABLE = True Then
Dim smsBody
smsBody = objMessageIn.Body
If smsBody <> "" Then
smsBody = Left(Replace(smsBody, "'", "''"), 255)
Else
smsBody = " "
End If
Dim sError : sError = strMsg
If sError <> "" then sError = Left(Replace(sError, "'", "''"), 255)
Dim strSQL
strSQL = "INSERT INTO `failedsms` (`Sender` , `Sms` , `Error` , `Status` , `CreationDate` , `IDSms`, `Recipient`) VALUES ("
strSQL = strSQL & " '" & objMessageIn.Sender & "', "
strSQL = strSQL & " '" & smsBody & "', "
strSQL = strSQL & " '" & sError & "', "
Dim errFound : errFound = False
Dim arrSmsError : arrSmsError = Split(ARR_SMS_ERROR_BOUNCE, "|")
Dim a : For a = 0 To UBound(arrSmsError)
If InStr(sError, "[" & arrSmsError(a) & "]") <> 0 Then errFound = True
Next
If errFound = False Then
strSQL = strSQL & " '0', "
Else
strSQL = strSQL & " '1', "
End If
strSQL = strSQL & " '" & Year(Date()) & "-" & Right("0" & Month(Date()), 2) & "-" & Right("0" & Day(Date()), 2) & _
" " & Right("0" & Hour(Now()), 2) & ":" & Right("0" & Minute(Now()), 2) & ":" & Right("0" & Second(Now()), 2) & "', "
strSQL = strSQL & " '" & objMessageIn.ID & "', "
strSQL = strSQL & " '" & objMessageIn.Recipient & "' "
strSQL = strSQL & " )"
gObjDebugger.WriteLine DateTime() & " ProcessVotes: strSQL = " & strSQL
objConn.Execute(strSQL)
End If
'// --------------------------------------------------
'// 20071116: ADV: SEND SMS?
'// --------------------------------------------------
gObjDebugger.WriteLine DateTime() & " SendMessage: SEND_SMS: " & SEND_SMS
If SEND_SMS = True Then
'// SMS MESSAGE OUT
objMessageOut.Direction = gObjConstants.MESSAGEDIRECTION_OUT
objMessageOut.Type = gObjConstants.MESSAGETYPE_SMS
objMessageOut.Status = gObjConstants.MESSAGESTATUS_PENDING
objMessageOut.ChannelID = SMS_SEND_CHANNEL
objMessageOut.Recipient = objMessageIn.Sender
objMessageOut.Body = strMsg
gObjMessageDB.Save(objMessageOut)
Set objMessageOut = Nothing
gObjDebugger.WriteLine DateTime() & " SendMessage: Outgoing Sms Message saved"
End If
'// --------------------------------------------------
'// 20071112: ADV: SEND EMAIL?
'// --------------------------------------------------
gObjDebugger.WriteLine DateTime() & " SendMessage: SEND_EMAIL: " & SEND_EMAIL
If SEND_EMAIL = True Then
'// EMAIL MESSAGE OUT
Set objMessageOut = gObjMessageDB.Create
objMessageOut.Direction = gObjConstants.MESSAGEDIRECTION_OUT
objMessageOut.Type = gObjConstants.MESSAGETYPE_EMAIL
objMessageOut.Status = gObjConstants.MESSAGESTATUS_PENDING
objMessageOut.ChannelID = 0
objMessageOut.Recipient = ALERT_EMAIL_RECIPIENTS
objMessageOut.Subject = "[SMS Server] " & strMsg
Dim strBody
strBody = "Message : " & strMsg & "." & vbCrLf
strBody = strBody & "Phone number: " & objMessageIn.Sender & vbCrLf
strBody = strBody & "Sms : " & objMessageIn.Body & vbCrLf & vbCrLf
objMessageOut.Body = strBody
gObjMessageDB.Save(objMessageOut)
Set objMessageOut = Nothing
gObjDebugger.WriteLine DateTime() & " SendMessage: Outgoing Email Message saved"
End If
Else
gObjDebugger.WriteLine DateTime() & " SendMessage: gObjMessageDB.LastError <> 0"
gObjDebugger.WriteLine DateTime() & " SendMessage: ERROR: Unable to create message"
End If
gObjDebugger.WriteLine DateTime() & " SendMessage: Exit function"
End Function
'// ==================================================
'// GET DATE & TIME (FOR DEBUGGING)
'// ==================================================
Function DateTime()
DateTime = Year(Date()) & "/" & Right("0" & Month(Date()), 2) & "/" & Right("0" & Day(Date()), 2) & " " & _
Right("0" & Hour(Time()), 2) & ":" & Right("0" & Minute(Time()), 2) & ":" & Right("0" & Second(Time()), 2)
End Function