Shortcut Menu

Skip

Main Navigation

Choose your language

You are here:

ActiveXperts.com > SMS Messaging Server > Case Studies > Kosovo Elections

ActiveXperts SMS Messaging Server Send, receive and automate SMS messages

Quicklinks


SMS elections application software, Kosovo 2009

Authors:

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:

  1. Collect information from more than 1600 observers all over the country;
  2. Check the information that arrives form the observes for inconsistencies and errors;
  3. Put all the information on a centralize database system for reporting features;
  4. 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):

Kosovo

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).

Kosovo

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