Public Class SmsDaemon

    Dim ConnString As String = "Provider=SQLNCLI ;Server=*******;Database=********;Uid=**********;Pwd=***********;"

    Dim cn As New OleDbConnection(ConnString)      'Dichiaro la nuova connessione
    Dim post As New SmsDriverLib



    'query per capire se ci sono entry in coda_sms
    Dim sqlCHK As String
    Dim sqlUPD As String
    Dim sqlSEL As String = "select * from coda_sms order by id asc"
    Dim sqlINS As String
    Dim sqlDEL As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        
        post.URL = "http://p********************.asp"
        post.HOST = "p*************"

    End Sub



    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Timer1.Enabled = False

        If Not (cn.State) Then
            cn.Open()

            Dim cmdSEL As New OleDbCommand(sqlSEL, cn)
            Dim dr As OleDbDataReader = cmdSEL.ExecuteReader

            While dr.Read()

                Dim id = dr("id")
                Dim username As String = dr("username")
                Dim denominazione As String
                Dim numero As String = dr("numero")
                Dim messaggio As String = dr("messaggio")

                Dim scalo


                If IsDBNull(dr("denominazione")) Then
                    scalo = 1
                    denominazione = ""
                Else
                    scalo = 1
                    denominazione = dr("denominazione")
                End If



                Dim cnCHK As New OleDbConnection(ConnString)
                cnCHK.Open()
                'controllo che ci siano crediti
                sqlCHK = "select credito,username_sms,password_sms from login where username='" & username & "'"

                Dim cmdCHK As New OleDbCommand(sqlCHK, cnCHK)
                Dim dr_chk As OleDbDataReader = cmdCHK.ExecuteReader
                dr_chk.Read()
                Dim credito = dr_chk(0)

                'per gestione sending sms personalizzata
                Dim usernameSMS = dr_chk(1)
                Dim passwordSMS = dr_chk(2)

                dr_chk.Close()
                cnCHK.Close()


                If credito > 0 Then
                    post.smsUSER = usernameSMS
                    post.smsPASSWORD = passwordSMS

                    'mando il messaggio
                    post.Add_Gsm(numero)
                    post.Send_Sms(2, denominazione, messaggio, "0", "file.sms", "")

                    Dim cnQUERY As New OleDbConnection(ConnString)
                    'diminuisco il contatore di messaggi mandati.
                    cnQUERY.Open()
                    sqlUPD = "update LOGIN set credito=(credito-" & scalo & ") where username='" & username & "'"
                    Dim cmdUPD As New OleDbCommand(sqlUPD, cnQUERY)
                    cmdUPD.ExecuteNonQuery()

                    'inserimento in tabella di archivio
                    sqlINS = "insert into archivio_sms (username,denominazione,numero,messaggio,data) values ('" & username & "','" & denominazione & "','" & numero & "','" & messaggio & "',CAST('" & Replace(Now(), ".", ":") & "' as datetime))"
                    Dim cmdINS As New OleDbCommand(sqlINS, cnQUERY)
                    cmdINS.ExecuteNonQuery()

                    'delete dalla tabella coda_sms
                    sqlDEL = "delete from coda_sms where id=" & id
                    Dim cmdDEL As New OleDbCommand(sqlDEL, cnQUERY)
                    cmdDEL.ExecuteNonQuery()

                    cnQUERY.Close()


                    'Dim debub = post.HTML
                    'MsgBox(debub)
                End If

            End While
            dr.Close()
            cn.Close()
        End If
        Timer1.Enabled = True

    End Sub

    
End Class