Changeset 1272

Show
Ignore:
Timestamp:
1/27/2010 5:08:02 PM (7 weeks ago)
Author:
vessper
Message:

27/01/2010 (1.14.0.1272)

- Core: Updated custom database access function
- Core: Fetching EveMails? and EveNotices? now checks for existing ones in the DB
- Core: Mailing lists now displayed as <Mailing List> in the From column

Location:
trunk
Files:
3 modified

Legend:

Unmodified
Added
Removed

  • trunk/EveHQ.Core/DataFunctions.vb

    r1233 r1272  
    464464                Try 
    465465                    conn.Open() 
    466                     If strSQL.Contains(" LIKE ") = False Then 
     466                    If strSQL.Contains(" LIKE ") = False And strSQL.Contains(" IN ") = False Then 
    467467                        strSQL = strSQL.Replace("'", "''") 
    468468                        strSQL = strSQL.Replace(ControlChars.Quote, "'") 

  • trunk/EveHQ.Core/EveMail.vb

    r1203 r1272  
    1111        ' Stage 1: Download the latest EveMail API using the standard API method 
    1212        ' Stage 2: Populate the class with our EveMail 
    13         ' Stage 3: Check the last messageID posted to our database 
     13        ' Stage 3: Check the messages which have already been posted 
    1414        ' Stage 4: Post all new messages to the database 
    1515        ' Stage 5: Get all the IDs and parse them 
    … …  
    6666        Next 
    6767 
    68         ' Stage 3: Check the last messageID posted to our database 
    69         Dim lastMessageID As Long = -1 
    70         Dim strSQL As String = "SELECT TOP 1 * FROM eveMail ORDER BY messageID DESC;" 
    71         Dim mailData As DataSet = EveHQ.Core.DataFunctions.GetCustomData(strSQL) 
    72         If mailData IsNot Nothing Then 
    73             If mailData.Tables(0).Rows.Count > 0 Then 
    74                 lastMessageID = CLng(mailData.Tables(0).Rows(0).Item("messageID")) 
     68        ' Stage 3: Check the messages which have already been posted 
     69        Dim existingMails As New ArrayList 
     70        Dim strExistingMails As New StringBuilder 
     71        If Mails.Count > 0 Then 
     72            For Each messageKey As String In Mails.Keys 
     73                strExistingMails.Append(",'" & messageKey & "'") 
     74            Next 
     75            strExistingMails.Remove(0, 1) 
     76            Dim strSQL As String = "SELECT messageKey FROM eveMail WHERE messageKey IN (" & strExistingMails.ToString & ");" 
     77            Dim mailData As DataSet = EveHQ.Core.DataFunctions.GetCustomData(strSQL) 
     78            If mailData IsNot Nothing Then 
     79                If mailData.Tables(0).Rows.Count > 0 Then 
     80                    For Each mailRow As DataRow In mailData.Tables(0).Rows 
     81                        existingMails.Add(mailRow.Item("messageKey").ToString) 
     82                    Next 
     83                End If 
    7584            End If 
    7685        End If 
    … …  
    8190        For Each mailKey As String In Mails.Keys 
    8291            Dim cMail As EveHQ.Core.EveMailMessage = Mails(mailKey) 
    83             Dim uSQL As New StringBuilder 
    84             uSQL.Append(strInsert) 
    85             uSQL.Append("(") 
    86             uSQL.Append("'" & cMail.MessageKey & "', ") 
    87             uSQL.Append(cMail.MessageID & ", ") 
    88             uSQL.Append(cMail.OriginatorID & ", ") 
    89             uSQL.Append(cMail.SenderID & ", ") 
    90             uSQL.Append("'" & Format(cMail.MessageDate, "yyyy-MM-dd HH:mm:ss") & "', ") 
    91             uSQL.Append("'" & cMail.MessageTitle.Replace("'", "''") & "', ") 
    92             uSQL.Append("'" & cMail.ToCorpAllianceIDs & "', ") 
    93             uSQL.Append("'" & cMail.ToCharacterIDs & "', ") 
    94             uSQL.Append("'" & cMail.ToListIDs & "', ") 
    95             uSQL.Append(CInt(cMail.ReadFlag) & ");") 
    96             If EveHQ.Core.DataFunctions.SetData(uSQL.ToString) = False Then 
    97                 If EveHQ.Core.HQ.dataError.Contains("Cannot insert duplicate key") = True Then 
    98                     ' Try an update 
    99                     Dim updateSQL As String = "UPDATE eveMail SET readMail=" & CInt(cMail.ReadFlag) & " WHERE messageKey='" & cMail.MessageKey & "';" 
    100                     If EveHQ.Core.DataFunctions.SetData(updateSQL) = False Then 
    101                         MessageBox.Show("There was an error updating data in the Eve Mail database table. The error was: " & ControlChars.CrLf & ControlChars.CrLf & EveHQ.Core.HQ.dataError & ControlChars.CrLf & ControlChars.CrLf & "Data: " & uSQL.ToString, "Error Writing EveMails", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 
    102                     End If 
     92            If existingMails.Contains(mailKey) = False Then 
     93                ' Add the message to the database 
     94                Dim uSQL As New StringBuilder 
     95                uSQL.Append(strInsert) 
     96                uSQL.Append("(") 
     97                uSQL.Append("'" & cMail.MessageKey & "', ") 
     98                uSQL.Append(cMail.MessageID & ", ") 
     99                uSQL.Append(cMail.OriginatorID & ", ") 
     100                uSQL.Append(cMail.SenderID & ", ") 
     101                uSQL.Append("'" & Format(cMail.MessageDate, "yyyy-MM-dd HH:mm:ss") & "', ") 
     102                uSQL.Append("'" & cMail.MessageTitle.Replace("'", "''") & "', ") 
     103                uSQL.Append("'" & cMail.ToCorpAllianceIDs & "', ") 
     104                uSQL.Append("'" & cMail.ToCharacterIDs & "', ") 
     105                uSQL.Append("'" & cMail.ToListIDs & "', ") 
     106                uSQL.Append(CInt(cMail.ReadFlag) & ");") 
     107                If EveHQ.Core.DataFunctions.SetData(uSQL.ToString) = False Then 
     108                    MessageBox.Show("There was an error writing data to the Eve Mail database table. The error was: " & ControlChars.CrLf & ControlChars.CrLf & EveHQ.Core.HQ.dataError & ControlChars.CrLf & ControlChars.CrLf & "Data: " & uSQL.ToString, "Error Writing EveMails", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 
    103109                Else 
    104                     MessageBox.Show("There was an error writing data to the Eve Mail database table. The error was: " & ControlChars.CrLf & ControlChars.CrLf & EveHQ.Core.HQ.dataError & ControlChars.CrLf & ControlChars.CrLf & "Data: " & uSQL.ToString, "Error Writing EveMails", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 
     110                    ' This may require an EveMail notification, so store it for later 
     111                    NewMails.Add(cMail) 
    105112                End If 
    106113            Else 
    107                 ' This may require an EveMail notification, so store it for later 
    108                 NewMails.Add(cMail) 
     114                ' Update the message in the database 
     115                Dim updateSQL As String = "UPDATE eveMail SET readMail=" & CInt(cMail.ReadFlag) & " WHERE messageKey='" & cMail.MessageKey & "';" 
     116                If EveHQ.Core.DataFunctions.SetData(updateSQL) = False Then 
     117                    MessageBox.Show("There was an error updating data in the Eve Mail database table. The error was: " & ControlChars.CrLf & ControlChars.CrLf & EveHQ.Core.HQ.dataError & ControlChars.CrLf & ControlChars.CrLf & "Data: " & updateSQL.ToString, "Error Writing EveMails", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 
     118                End If 
    109119            End If 
    110120        Next 
    … …  
    138148        ' Stage 1: Download the latest EveNotifications API using the standard API method 
    139149        ' Stage 2: Populate the class with our Eve Notifications 
    140         ' Stage 3: Check the last messageID posted to our database 
     150        ' Stage 3: Check the messages which have already been posted 
    141151        ' Stage 4: Post all new messages to the database 
    142152        ' Stage 5: Get all the IDs and parse them 
    … …  
    190200        Next 
    191201 
    192         ' Stage 3: Check the last messageID posted to our database 
    193         Dim lastMessageID As Long = -1 
    194         Dim strSQL As String = "SELECT TOP 1 * FROM eveNotifications ORDER BY messageID DESC;" 
    195         Dim NoticeData As DataSet = EveHQ.Core.DataFunctions.GetCustomData(strSQL) 
    196         If NoticeData IsNot Nothing Then 
    197             If NoticeData.Tables(0).Rows.Count > 0 Then 
    198                 lastMessageID = CLng(NoticeData.Tables(0).Rows(0).Item("messageID")) 
     202        ' Stage 3: Check the messages which have already been posted 
     203        Dim existingMails As New ArrayList 
     204        Dim strExistingMails As New StringBuilder 
     205        If Notices.Count > 0 Then 
     206            For Each messageKey As String In Notices.Keys 
     207                strExistingMails.Append(",'" & messageKey & "'") 
     208            Next 
     209            strExistingMails.Remove(0, 1) 
     210            Dim strSQL As String = "SELECT messageKey FROM eveNotifications WHERE messageKey IN (" & strExistingMails.ToString & ");" 
     211            Dim mailData As DataSet = EveHQ.Core.DataFunctions.GetCustomData(strSQL) 
     212            If mailData IsNot Nothing Then 
     213                If mailData.Tables(0).Rows.Count > 0 Then 
     214                    For Each mailRow As DataRow In mailData.Tables(0).Rows 
     215                        existingMails.Add(mailRow.Item("messageKey").ToString) 
     216                    Next 
     217                End If 
    199218            End If 
    200219        End If 
    … …  
    205224        For Each NoticeKey As String In Notices.Keys 
    206225            Dim cMail As EveHQ.Core.EveNotification = Notices(NoticeKey) 
    207             Dim uSQL As New StringBuilder 
    208             uSQL.Append(strInsert) 
    209             uSQL.Append("(") 
    210             uSQL.Append("'" & cMail.MessageKey & "', ") 
    211             uSQL.Append(cMail.MessageID & ", ") 
    212             uSQL.Append(cMail.OriginatorID & ", ") 
    213             uSQL.Append(cMail.SenderID & ", ") 
    214             uSQL.Append(cMail.TypeID & ", ") 
    215             uSQL.Append("'" & Format(cMail.MessageDate, "yyyy-MM-dd HH:mm:ss") & "', ") 
    216             uSQL.Append(CInt(cMail.ReadFlag) & ");") 
    217             If EveHQ.Core.DataFunctions.SetData(uSQL.ToString) = False Then 
    218                 If EveHQ.Core.HQ.dataError.Contains("Cannot insert duplicate key") = True Then 
    219                     ' Try an update 
    220                     Dim updateSQL As String = "UPDATE eveNotifications SET readMail=" & CInt(cMail.ReadFlag) & " WHERE messageKey='" & cMail.MessageKey & "';" 
    221                     If EveHQ.Core.DataFunctions.SetData(updateSQL) = False Then 
    222                         MessageBox.Show("There was an error updating data in the Eve Notifications database table. The error was: " & ControlChars.CrLf & ControlChars.CrLf & EveHQ.Core.HQ.dataError & ControlChars.CrLf & ControlChars.CrLf & "Data: " & uSQL.ToString, "Error Writing Eve Notifications", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 
    223                     End If 
     226            If existingMails.Contains(cMail.MessageKey) = False Then 
     227                ' Add the message to the database 
     228                Dim uSQL As New StringBuilder 
     229                uSQL.Append(strInsert) 
     230                uSQL.Append("(") 
     231                uSQL.Append("'" & cMail.MessageKey & "', ") 
     232                uSQL.Append(cMail.MessageID & ", ") 
     233                uSQL.Append(cMail.OriginatorID & ", ") 
     234                uSQL.Append(cMail.SenderID & ", ") 
     235                uSQL.Append(cMail.TypeID & ", ") 
     236                uSQL.Append("'" & Format(cMail.MessageDate, "yyyy-MM-dd HH:mm:ss") & "', ") 
     237                uSQL.Append(CInt(cMail.ReadFlag) & ");") 
     238                If EveHQ.Core.DataFunctions.SetData(uSQL.ToString) = False Then 
     239                    MessageBox.Show("There was an error writing data to the Eve Notifications database table. The error was: " & ControlChars.CrLf & ControlChars.CrLf & EveHQ.Core.HQ.dataError & ControlChars.CrLf & ControlChars.CrLf & "Data: " & uSQL.ToString, "Error Writing Eve Notifications", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 
    224240                Else 
    225                     MessageBox.Show("There was an error writing data to the Eve Notifications database table. The error was: " & ControlChars.CrLf & ControlChars.CrLf & EveHQ.Core.HQ.dataError & ControlChars.CrLf & ControlChars.CrLf & "Data: " & uSQL.ToString, "Error Writing Eve Notifications", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 
     241                    ' This may require an EveMail notification, so store it 
     242                    newNotifys.Add(cMail) 
    226243                End If 
    227244            Else 
    228                 ' This may require an EveMail notification, so sotre it 
    229                 newNotifys.Add(cMail) 
    230             End If 
     245                ' Update the message in the database 
     246                Dim updateSQL As String = "UPDATE eveNotifications SET readMail=" & CInt(cMail.ReadFlag) & " WHERE messageKey='" & cMail.MessageKey & "';" 
     247                If EveHQ.Core.DataFunctions.SetData(updateSQL) = False Then 
     248                    MessageBox.Show("There was an error updating data in the Eve Notifications database table. The error was: " & ControlChars.CrLf & ControlChars.CrLf & EveHQ.Core.HQ.dataError & ControlChars.CrLf & ControlChars.CrLf & "Data: " & updateSQL.ToString, "Error Writing Eve Notifications", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) 
     249                End If 
     250            End If 
     251 
    231252        Next 
    232253 

  • trunk/EveHQ/Forms/frmMail.vb

    r1259 r1272  
    176176        For Each newMail As EveHQ.Core.EveMailMessage In allMails.Values 
    177177            Dim mailItem As New ContainerListViewItem 
    178             If FinalIDs.ContainsKey(newMail.SenderID) = True Then 
    179                 mailItem.Text = FinalIDs(newMail.SenderID) 
    180             Else 
    181                 'TODO: Replace this with a routine to get the name from the API 
    182                 mailItem.Text = "Unknown" 
     178            If newMail.ToListIDs = "" Then 
     179                If FinalIDs.ContainsKey(newMail.SenderID) = True Then 
     180                    mailItem.Text = FinalIDs(newMail.SenderID) 
     181                Else 
     182                    'TODO: Replace this with a routine to get the name from the API 
     183                    mailItem.Text = "Unknown" 
     184                End If 
     185            Else 
     186                mailItem.Text = "<Mailing List>" 
    183187            End If 
    184188            clvMail.Items.Add(mailItem)