Retrieving and Storing Call History

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using WindowsMobile6.Phone;
using iAnywhere.Data.SQLAnywhere;

namespace CallHistoryViewer
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            SAConnection conn;
            SACommand cmd;
            SADataReader reader;
            string sqlstmt;

            WaitForm waitForm = new WaitForm();
            waitForm.Message.Text = "Loading your saved call history from the database. Please wait...";
            waitForm.Visible = true;
            waitForm.Refresh();

            System.Diagnostics.Process webService = new System.Diagnostics.Process();
            webService.StartInfo.FileName = "\\Program Files\\SQLAny10\\dbsrv10.exe";
            webService.StartInfo.Arguments = "\"\\Program Files\\SA10 Call History Viewer\\callhistory.db\" -qw -x tcpip -xs http(port=8888)";
            webService.Start();

            sqlstmt = "SELECT StartTime, EndTime, IsConnected, IsOutgoing, CallerName, CallerNumber FROM PHONE.CallHistory ORDER BY StartTime";
            conn = new SAConnection("DBF=\"\\Program Files\\SA10 Call History Viewer\\callhistory.db\";UID=phone_user;PWD=sql");
            conn.Open();
            cmd = new SACommand(sqlstmt, conn);
            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                ListViewItem newItem = new ListViewItem();

                newItem.Text = reader.GetDateTime(0).ToString("yy-MM-dd hh:mm tt");
                
                newItem.SubItems.Add(reader.GetDateTime(1).Subtract(reader.GetDateTime(0)).ToString());
                
                bool isConnected = reader.GetBoolean(2);
                bool isOutgoing = reader.GetBoolean(3);       
                string Type = "???";
                if (isOutgoing)
                    Type = "OUT";
                else if (!isOutgoing && isConnected)
                    Type = "IN";
                else
                    Type = "MISS";
                newItem.SubItems.Add(Type);

                if (!reader.IsDBNull(4))
                    newItem.SubItems.Add(reader.GetString(4));
                else
                    newItem.SubItems.Add("");
                
                if (!reader.IsDBNull(5))
                    newItem.SubItems.Add(reader.GetString(5));

                CallList.Items.Add(newItem);
            }

            reader.Close();
            conn.Close();

            waitForm.Visible = false;
        }        

        private void UpdateMenuItem_Click(object sender, EventArgs e)
        {
            SAConnection conn;
            SACommand cmd;
            string sqlstmt;

            DateTime latestEntryTime;

            WaitForm waitForm = new WaitForm();
            waitForm.Message.Text = "Updating your saved call history database from your phone's internal logs. Please wait...";
            waitForm.Visible = true;
            waitForm.Refresh();

            conn = new SAConnection("DBF=\"\\Program Files\\SA10 Call History Viewer\\callhistory.db\";UID=phone_user;PWD=sql");
            conn.Open();

            sqlstmt = "SELECT TOP 1 StartTime FROM PHONE.CallHistory ORDER BY StartTime DESC";
            cmd = new SACommand(sqlstmt, conn);
            if (cmd.ExecuteScalar() != null)
                latestEntryTime = (DateTime)cmd.ExecuteScalar();
            else
                latestEntryTime = DateTime.MinValue;
            
            CallLogEntry[] entries = CallLog.Entries;

            for (int i = 0; i < entries.Length; i++)
            {
                if (entries[i].StartTime > latestEntryTime)
                {
                    sqlstmt = "INSERT INTO PHONE.CallHistory (StartTime, EndTime, IsConnected, IsEnded, IsOutgoing, IsRoaming, CallerID, CallerName, CallerNumber)";
                    sqlstmt += " VALUES('";
                    sqlstmt += entries[i].StartTime.ToString("yyyy-MM-dd hh:mm:ss tt") + "', '";
                    sqlstmt += entries[i].EndTime.ToString("yyyy-MM-dd hh:mm:ss tt") + "', ";
                    sqlstmt += ((int)(entries[i].IsConnected ? 1 : 0)).ToString() + ", ";
                    sqlstmt += ((int)(entries[i].IsEnded ? 1 : 0)).ToString() + ", ";
                    sqlstmt += ((int)(entries[i].IsOutgoing ? 1 : 0)).ToString() + ", ";
                    sqlstmt += ((int)(entries[i].IsRoaming ? 1 : 0)).ToString() + ", ";
                    sqlstmt += (int)entries[i].CallerID + ", '";
                    sqlstmt += entries[i].CallerName + "', '";
                    sqlstmt += entries[i].CallerNumber + "')";

                    cmd = new SACommand(sqlstmt, conn);
                    cmd.ExecuteNonQuery();
                }
            }

            conn.Close();

            waitForm.Visible = false;

            CallList.Items.Clear();

            MainForm_Load(null, null);
        }

        private void LaunchMenuItem_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process ie = new System.Diagnostics.Process();
            ie.StartInfo.UseShellExecute = true;
            ie.StartInfo.FileName = "iexplore";
            ie.StartInfo.Arguments = "http://localhost:8888";
            ie.Start();
        }
    }
}