Applicazione software SMS elezioni Kosovo 2007

1. Background

IFES è una organizzazione senza scopo di lucro col fine di attuare uno sviluppo della democrazia e che lavora per dare alla gente una voce nel modo in cui essi sono disciplinati. Definita in passato Fondazione Internazionale per i Sistemi Elettorali, IFES è la prima organizzazione al mondo per l’assistenza alle elezioni e si occupa di fornire ai paesi la consulenza tecnica e gli strumenti di cui hanno bisogno per svolgere le elezioni democratiche. Il lavoro di IFES è non di parte e comprende anche progetti per:

Dalla sua fondazione nel 1987, IFES ha lavorato in oltre 100 paesi - dalle democrazie in via di sviluppo come la Liberia, alle democrazie mature come gli Stati Uniti. Ogni progetto IFES è gestito da personale locale e da nostri partner del team che collaborano con le organizzazioni locali. Con questo approccio “homegrown”, IFES assicura che l'esperienza offerta si adatti alle esigenze del Paese e che il vantaggio dell’assistenza faccia perdurare la vita del progetto. La presenza di IFES in Kosovo è focalizzata sugli stessi principi e gli obiettivi. Questo caso di studio si concentra sul progetto IFES implementato per le elezioni in Kosovo nel novembre 2007.

2. Individuazione del problema

Il progetto mira a ottenere risultati in tempo reale per le elezioni. La sfida principale è il numero di centri di voto, da cui provengono i risultati, e l’arco di tempo che dovrebbe essere il più basso possibile, naturalmente mantenendo intatte le informazioni, senza errori e logicamente organizzati in tabelle dei risultati per permettere la diffusione ai media.

3. Obiettivi del nuovo sistema

Obiettivi del nuovo sistema:

4. Soluzione ActiveXperts SMS Messaging Server

In questo progetto si sfrutta l'uso dell’Activexperts Messaging Server e la sua tecnologia SMS per ottenere informazioni riguardanti la partecipazione durante le operazioni di voto ed i risultati online così da essere computati ad ogni centro di voto. L’utilizzo di SMS è il modo più efficiente ed economico per implementare questo sistema piuttosto che il GPRS in quanto:

Il progetto si propone di diffondere fino a 1600 utenti per monitorare il processo di voto e fornire dati circa il suo progresso e, alla fine, gli stessi utenti daranno i risultati di ogni centro di voto corrispondente. Questo progetto mira a coprire 1.600 centri di votazione. Le informazioni fornite dagli utenti conterranno:

se il centro di voto è aperto o no, come sta andando il processo di voto, se è normale o ha qualche problema, o è conflittuale o è bloccato, com’è la partecipazione; il centro di voto è chiuso; successivamente ogni informazioni sarà considerata come informazione riguardanti i risultati in modo che il sistema li interpreti come tali.

Tutte queste informazioni che gli utenti invieranno saranno conservati nella banca dati della sala conferenze / locali. Il collegamento utilizzato con il provider GSM è stato prima un collegamento SMPP tramite una connessione VPN con la rete GSM (Figura 1).

elezioni-kosovo-2007

Figure 1

Come backup o tramite modem GSM, in questo modo il provider GSM invia i messaggi al modem GSM e ai database dei server in modo appropriato (Figura 2).

elezioni-kosovo-2007

Figure 2

Il server ActiveXperts passa automaticamente attraverso due connessioni in caso uno di loro non riuscisse.

Dopo che i messaggi sono arrivati al server vengono trasformati opportunamente in base alla struttura del messaggio stesso, in base al numero di cellulare che ha inviato il messaggio e anche in base al momento in cui il messaggio viene inviato.

L'intero processo dei flussi passa dall'utente al database del server. L'utente avvia il processo con l'invio di un messaggio SMS in base a ciò che vede presso il Centro di voto. Questo messaggio viene inviato al fornitore di rete GSM, che invia questo messaggio ai nostri server. Il messaggio viene poi elaborate per generare grafici e moduli di generazione di video.

Una volta che questa grafici sono generati forma con i valori provenienti dai database in modo che siano pronti per essere trasformati in due modi diversi: un modo visualizzerà i grafici in video per i monitor per la conferenza e per i Media, l'altro modo è per la loro elaborazione per il sito dove possono essere visti da ogni utente Internet.

Questo processo continuerà fino a che l’ultimo Centro di voto verrà conteggiato ed i flussi video e pagine web saranno aggiornati con le ultime informazioni provenienti dagli utenti SMS. Le intere informazioni video, dati o pagine web saranno trasmesso in streaming online fino a quando non ci sarà una richiesta per loro.

5. Codice sorgente

Codice sorgente del sistema:

'// ========================================================================
'// 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