/*
 * Diagnostic flags
 * 
 * Undefine one or more of the following to get additional diagnostic
 * information about the database file and process.
 */
//#define LIST_TABLES
//#define LIST_GROUPS
//#define GROUP_CONTENT
//#define LIST_LABELS
//#define SHOW_TIMING
//#define VERBOSE
 
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using log4net;

namespace RmSplit
{
    public partial class RmSplitForm : Form
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(RmSplitForm));

        SQLiteConnection con = null;
        string filename;
        HashSet<int> idSet = new HashSet<int>();
        HashSet<int> addSet = new HashSet<int>();
        int rootPerson = -1;
        Stopwatch timer = new Stopwatch();
        Stopwatch processTimer = new Stopwatch();
        SortedDictionary<String, int> nameList = new SortedDictionary<string, int>();
        Dictionary<int, String> primaryNameList = new Dictionary<int, String>();

        public RmSplitForm()
        {
            log.Info("Initializing Form");
            InitializeComponent();
            log.Info("Initializing SQLiteConnection");
            con = new SQLiteConnection();
            paramInit();
        }

        /// <summary>
        /// read in the names from the database file
        /// </summary>
        private void loadNames()
        {
            nameList.Clear();
            primaryNameList.Clear();
            try
            {
                openDb(filename);
            }
            catch (Exception)
            {
                MessageBox.Show("Unable to open the default database file.  Please select a new file and try loading it again.",
                    "Open Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            IDbCommand cmd = con.CreateCommand();
            Stopwatch timer = new Stopwatch();
            timer.Start();
            cmd.CommandText = "SELECT OwnerID, Surname, Given, IsPrimary, BirthYear, DeathYear FROM NameTable";
            using (IDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string surname = reader.GetString(1);
                    string givenName = reader.GetString(2);
                    bool primary = (reader.GetInt32(3) == 1);
                    int birth = reader.GetInt32(4);
                    int death = reader.GetInt32(5);
                    string name;
                    if (birth > 0 && death > 0)
                        name = string.Format("{0} {1} ({2}-{3}) [{4}]", givenName, surname, birth, death, id).Trim();
                    else if (birth > 0)
                        name = string.Format("{0} {1} ({2}-) [{3}]", givenName, surname, birth, id).Trim();
                    else if (death > 0)
                        name = string.Format("{0} {1} (-{2}) [{3}]", givenName, surname, death, id).Trim();
                    else
                        name = string.Format("{0} {1} [{2}]", givenName, surname, id).Trim();
                    if (!nameList.ContainsKey(name))
                    {
                        if(name.Length>0)
                            nameList.Add(name, id);
                    }
                    if (!primaryNameList.ContainsKey(id))
                    {
                        primaryNameList.Add(id, name);
                    }
                }
            }
            timer.Stop();
            statusBox.Text += String.Format("It took {0} seconds to create nameList with {1} elements and primaryNameList with {2} elements.\n",
                timer.Elapsed.TotalSeconds, nameList.Count, primaryNameList.Count);
            closeDb();
        }

        private void paramInit()
        {
            SQLiteFunction.RegisterFunction(typeof(RmNocase));  // Registers RMNOCASE collation
            filename = RmSplit.Properties.Settings.Default.RmFile;
            if (filename.Length > 0)
            {
                fileLabel.Text = System.IO.Path.GetFileName(filename);
            }
            generationsBox.Value = RmSplit.Properties.Settings.Default.Generations;
            leavesBox.Value = RmSplit.Properties.Settings.Default.Leaves;
            spouseBox.Checked = RmSplit.Properties.Settings.Default.IncludeSpouses;
            groupBox.Text = RmSplit.Properties.Settings.Default.Group;
            int rootPerson = RmSplit.Properties.Settings.Default.RootPerson;
            if(filename.Length>0 && rootPerson>0)
                setRootPerson(rootPerson);
        }

        /// <summary>
        /// Set the Root Person for the split. If rin is invalid, prompt for root person
        /// </summary>
        /// <param name="rin"></param>
        private void setRootPerson(int rin)
        {
            rootPerson = rin;
            if (primaryNameList.Count == 0 && filename.Length > 0)
            {
                loadNames();
            }
            if(primaryNameList.Count>0){
                try
                {
                    rootLabel.Text = primaryNameList[rin];
                } catch (Exception)
                {
                    MessageBox.Show("Specified root person not found in list of RIN's in database.  You probably " +
                        "used one database to select the root person and another for the current list. " +
                        "Please reset either the database or root person and try again.", "RIN Not Found",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            else
            {
                MessageBox.Show("Can't set root person.  Either the filename has not been set, " +
                    "the file name is invalid, or there are no people in the file.", "No Root Person Set",
                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void ReadFile()
        {
            if (filename.Length>0)
            {
                openDb(filename);
#if LIST_TABLES
                listDbTables();
#endif
#if LIST_GROUPS
                listGroups();
#endif
#if GROUP_CONTENT
                groupContent();
#endif
#if LIST_LABELS
                listLabels();
#endif
                traceTree();
                closeDb();
            }
        }

        private void openDb(string dbFile){
                string cs = string.Format("uri=file:{0}", dbFile);
                con.ConnectionString = cs;
                con.Open();
        }

        private void closeDb()
        {
            con.Close();
        }

        private void RmSplitForm_Load(object sender, EventArgs e)
        {
        }

        /// <summary>
        /// List all of the data tables
        /// </summary>
        private void listDbTables()
        {
            IDbCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
            IDataReader reader = cmd.ExecuteReader();
            statusBox.Text += "TABLES\n";
            while (reader.Read())
            {
                string name = reader.GetString(0);
                statusBox.Text += String.Format("\t{0}\n", name);
            }
        }

        /// <summary>
        /// List all of hte groups
        /// </summary>
        private void listGroups()
        {
            IDbCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM GroupTable";
            IDataReader reader = cmd.ExecuteReader();
            DataTable schema = reader.GetSchemaTable();
            statusBox.Text += "\nGroupTable Structure\n";
            foreach (DataRow row in schema.Rows)
            {
                string line = String.Format("\t{0}: {1}\n", (string)row["ColumnName"], row["DataType"]);
                statusBox.Text += line;
            }
        }

        /// <summary>
        /// print the content of the Group table
        /// </summary>
        private void groupContent()
        {
            IDbCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM GroupTable";
            IDataReader reader = cmd.ExecuteReader();
            statusBox.Text += "\nGroupTable Content\n";
            while (reader.Read())
            {
                int recordId = reader.GetInt32(reader.GetOrdinal("RecID"));
                int groupId = reader.GetInt32(reader.GetOrdinal("GroupID"));
                int startId = reader.GetInt32(reader.GetOrdinal("StartID"));
                int endId = reader.GetInt32(reader.GetOrdinal("EndID"));
                statusBox.Text += String.Format("\t{0}\t{1}\t{2}\t{3}\n", recordId,
                    groupId, startId, endId);
            }
        }

        /// <summary>
        /// print the labels in the label table
        /// </summary>
        private void listLabels()
        {
            IDbCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT * FROM LabelTable";
            IDataReader reader = cmd.ExecuteReader();
            statusBox.Text += "\nLabelTable Content\n";
            while (reader.Read())
            {
                int labelId = reader.GetInt32(reader.GetOrdinal("LabelID"));
                int labelType = reader.GetInt32(reader.GetOrdinal("LabelType"));
                int labelValue = reader.GetInt32(reader.GetOrdinal("LabelValue"));
                string labelName = reader.GetString(reader.GetOrdinal("LabelName"));
                /* This traps a problem I don't expect to have anymore with the
                 * current code which puts in a zero length string instead
                 * of a DBNull
                 */
                int elnum = reader.GetOrdinal("Description");
                object descObj = reader.GetValue(elnum);
                string description = "";
                if(descObj.GetType() != typeof(DBNull))
                    description = reader.GetString(elnum);
                statusBox.Text += String.Format("\t{0}\t{1}\t{2}\t{3}\t{4}\n",
                    labelId, labelType, labelValue, labelName, description);
            }
        }

        /// <summary>
        /// Print a full (or currently partial) tree for root person
        /// </summary>
        private void traceTree()
        {
            IDbCommand cmd = con.CreateCommand();
            // Get family (link?) information
            cmd.CommandText = String.Format("SELECT * FROM PersonTable WHERE PersonID={0}", rootPerson);
            using (IDataReader reader = cmd.ExecuteReader())
            {
#if VERBOSE
                statusBox.Text += String.Format("\nPersonTable Content for Person {0}\n", rootPerson);
#endif
                reader.Read();
#if VERBOSE
                string uniqueId = reader.GetString(reader.GetOrdinal("UniqueID"));
                int sex = reader.GetInt32(reader.GetOrdinal("Sex")); // 0 is mail
                float editDate = reader.GetFloat(reader.GetOrdinal("EditDate"));
#endif
                int parentId = reader.GetInt32(reader.GetOrdinal("ParentID"));
#if VERBOSE
                int spouseId = reader.GetInt32(reader.GetOrdinal("SpouseID"));
                statusBox.Text += String.Format("\tuniqueId: {0}\n\tsex:{1} edit date:{2} parentId:{3} spouseId:{4}\n",
                    uniqueId, sex, editDate, parentId, spouseId);
                // Get name information
                statusBox.Text += "\nName Query\n";
                printName(1, "", rootPerson);
#endif
                // Print related people at primary level
                printParents(parentId);
            }
            printSpouse(rootPerson);
            printSiblings(rootPerson);
        }

        /// <summary>
        /// print the name of a person's spouse(s)
        /// </summary>
        /// <param name="personId">id for person whose spouse is desired</param>
        private void printSpouse(int personId){ 
#if VERBOSE
            statusBox.Text += "\nSpouse(s)\n";
            IDbCommand cmd = con.CreateCommand();
            cmd.CommandText = String.Format("SELECT MotherID FROM FamilyTable WHERE FatherID={0}", personId);
            using (IDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    printName(1, "", reader.GetInt32(0));
                }
            }
            cmd.CommandText = String.Format("SELECT FatherID FROM FamilyTable WHERE MotherID={0}", personId);
            using (IDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    printName(1, "", reader.GetInt32(0));
                }
            }
#endif
        }

        /// <summary>
        /// print the full siblings of this person
        /// </summary>
        /// <param name="parentId">ID person</param>
        private void printSiblings(int personId)
        {
#if VERBOSE
            statusBox.Text += "\nSiblings\n";
            IDbCommand cmd = con.CreateCommand();
            cmd.CommandText = String.Format("SELECT FamilyID FROM ChildTable WHERE ChildID={0}", personId);
            long familyId = -1;
            try
            {
                object o = cmd.ExecuteScalar();
                familyId = (long)cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                MessageBox.Show("Something's wrong here: "+e.Message+"  SQL='"+cmd.CommandText+"'.",
                    "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            cmd.CommandText = String.Format("SELECT ChildID FROM ChildTable WHERE FamilyID={0} AND ChildID!={1}",
                familyId, personId);
            IDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                int siblingId = reader.GetInt32(0);
                printName(1, "", siblingId);
            }
#endif
        }

        /// <summary>
        /// Print parents of a family
        /// </summary>
        /// <param name="familyId">ID number for family</param>
        private void printParents(int familyId)
        {
#if VERBOSE
            IDbCommand cmd = con.CreateCommand();
            cmd.CommandText = String.Format("SELECT * FROM FamilyTable WHERE FamilyID={0}", familyId);
            IDataReader reader = cmd.ExecuteReader();
            statusBox.Text += "\nParents\n";
            if (reader.Read())
            {
                int fatherId = reader.GetInt32(reader.GetOrdinal("FatherID"));
                int motherId = reader.GetInt32(reader.GetOrdinal("MotherID"));
                printName(1, "father", fatherId);
                printName(1, "mother", motherId);
            }
            else
            {
                statusBox.Text += "\tNo parents found\n";
            }
            if (reader.Read())
            {
                statusBox.Text += "\tMultiple parents found\n";
            }
#endif
        }

        /// <summary>
        /// print a person's name in the status box
        /// </summary>
        /// <param name="position">number of tabs before printing</param>
        /// <param name="label">label to print</param>
        /// <param name="id">id of person to print</param>
        private void printName(int position, string label, int id)
        {
            IDbCommand cmd = con.CreateCommand();
            cmd.CommandText = String.Format("SELECT * FROM NameTable WHERE OwnerID={0} AND IsPrimary=1", id);
            IDataReader reader = cmd.ExecuteReader();
            string name = "<No Name>\n";
            if (reader.Read())
            {
                string givenName = reader.GetString(reader.GetOrdinal("Given"));
                string surname = reader.GetString(reader.GetOrdinal("Surname"));
                name = String.Format("{0} {1}\n", givenName, surname);
            }
            for (int i = 0; i < position; i++)
                statusBox.Text += "\t";
            if (label.Length > 0)
                statusBox.Text += label + ": ";
            statusBox.Text += name;
        }

        private void fileButton_Click(object sender, EventArgs e)
        {
            if (openRmFileDialog.ShowDialog() == DialogResult.OK)
            {
                filename = openRmFileDialog.FileName;
                fileLabel.Text = System.IO.Path.GetFileName(filename);
            }
        }

        private void RmSplitForm_FormClosing(object sender, FormClosingEventArgs e)
        {
            RmSplit.Properties.Settings.Default.RmFile = filename;
            RmSplit.Properties.Settings.Default.Generations = (int)generationsBox.Value;
            RmSplit.Properties.Settings.Default.Leaves = (int)leavesBox.Value;
            RmSplit.Properties.Settings.Default.IncludeSpouses = spouseBox.Checked;
            RmSplit.Properties.Settings.Default.Group = groupBox.Text;
            RmSplit.Properties.Settings.Default.RootPerson = rootPerson;
            // Storing logging setting is taken care of in menu handler
            RmSplit.Properties.Settings.Default.Save();
        }

        private void processButton_Click(object sender, EventArgs e)
        {
            if (filename.Length > 0)
            {
                ReadFile();
                // Add group could take a while, use a background worker
                fileButton.Enabled = false;
                processButton.Enabled = false;
                generationsBox.Enabled = false;
                leavesBox.Enabled = false;
                spouseBox.Enabled = false;
                groupBox.Enabled = false;
                processTimer.Start();
                treeBackgroundWorker.RunWorkerAsync();
            }
        }

        /// <summary>
        /// Add a Group to the Group Table
        /// </summary>
        private void addGroup()
        {
            if (filename.Length > 0)
            {
                openDb(filename);
                createIdList();
                createGroup();
                closeDb();
            }            
        }

        /// <summary>
        /// Create a list of ID's to include in group
        /// </summary>
        private void createIdList()
        {
            idSet.Clear();
            int generation = 1;
            treeBackgroundWorker.ReportProgress(generation, "\nCreating Group Tree\n");
            idSet.Add(rootPerson);
            treeBackgroundWorker.ReportProgress(1, ".");
            addDirectParents(rootPerson, generation + 1);
            treeBackgroundWorker.ReportProgress(idSet.Count, ".");
            if (spouseBox.Checked)
            {
                addSet.Clear();
                foreach (int id in idSet)
                {
                    addSpouse(id);
                }
                idSet.UnionWith(addSet);
                treeBackgroundWorker.ReportProgress(idSet.Count, ".");
            }
            /*
             * This process below is slow and redundant, but effective.  By incorporating the
             * current generation in each subsequent check for children and spouses, I'm checking
             * each person more than once.  If people have on the average of 2-3 children with spouses
             * and the trees are full out to the selected leave level, the tree grows geometrically
             * and this isn't too big of a deal.  If I have a lot of empty leaves or many families
             * without children, this could add a lot of redundancy.  It could be removed by having
             * each leaf level only check the addset from the previous leaf.
             * 
             * I tried streamlining this and it didn't make much difference in time and resulted in
             * fewer entries (not sure what was missing).  So I decided to keep it the older, simpler
             * way.
             */
            for (int i = 0; i < leavesBox.Value; i++)
            {
                addSet.Clear();
                foreach (int id in idSet)
                {
                    addChildren(id);
                    treeBackgroundWorker.ReportProgress(idSet.Count + addSet.Count, ".");
                }
                idSet.UnionWith(addSet);
                treeBackgroundWorker.ReportProgress(idSet.Count, ".");
                addSet.Clear();
                foreach (int id in idSet)
                {
                    addSpouse(id);
                    treeBackgroundWorker.ReportProgress(idSet.Count + addSet.Count, ".");
                }
                idSet.UnionWith(addSet);
                treeBackgroundWorker.ReportProgress(idSet.Count, ".");
                addSet.Clear();
                foreach (int id in idSet)
                {
                    addLeafParents(id);
                    treeBackgroundWorker.ReportProgress(idSet.Count + addSet.Count, ".");
                }
                idSet.UnionWith(addSet);
                treeBackgroundWorker.ReportProgress(idSet.Count, ".");
            }
        }

        /// <summary>
        /// Add parents of this direct line person recursively until parents are
        /// blank or until generation limit is reached
        /// </summary>
        /// <param name="child">id number of person whose parents are being added</param>
        /// <param name="generation">current generation number</param>
        private void addDirectParents(int child, int generation)
        {
            treeBackgroundWorker.ReportProgress(generation, ".");
            if (generation > generationsBox.Value) return;
            IDbCommand cmd = con.CreateCommand();
#if SHOW_TIMING
            if (generation == 2)
            {
                timer.Start();
            }
#endif
            cmd.CommandText = String.Format("SELECT ParentID FROM PersonTable WHERE PersonID={0}", child);
            object ofamily = cmd.ExecuteScalar();
            if (ofamily.GetType() != typeof(long)) return; // no parents
            long family = (long)ofamily;
            cmd.CommandText = String.Format("SELECT * FROM FamilyTable WHERE FamilyID={0}", family);
            using (IDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    int fatherId = reader.GetInt32(reader.GetOrdinal("FatherID"));
                    if (fatherId > 0)
                    {
                        idSet.Add(fatherId);
                        addDirectParents(fatherId, generation + 1);
                    }
                    int motherId = reader.GetInt32(reader.GetOrdinal("MotherID"));
                    if (motherId > 0)
                    {
                        idSet.Add(motherId);
                        addDirectParents(motherId, generation + 1);
                    }
                }
            }
#if SHOW_TIMING
            if (generation == 2)
            {
                timer.Stop();
                treeBackgroundWorker.ReportProgress(20,
                    String.Format("\n\tcreating list took {0} s\n", timer.Elapsed.TotalSeconds));
            }
#endif
        }

        /// <summary>
        /// Add parents of this leaf person
        /// </summary>
        /// <param name="child">id number of person whose parents are being added</param>
        private void addLeafParents(int child)
        {
            IDbCommand cmd = con.CreateCommand();
            cmd.CommandText = String.Format("SELECT ParentID FROM PersonTable WHERE PersonID={0}", child);
            object ofamily = cmd.ExecuteScalar();
            if (ofamily.GetType() != typeof(long)) return; // no parents
            long family = (long)ofamily;
            cmd.CommandText = String.Format("SELECT * FROM FamilyTable WHERE FamilyID={0}", family);
            using (IDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    int fatherId = reader.GetInt32(reader.GetOrdinal("FatherID"));
                    if (fatherId > 0)
                    {
                        addSet.Add(fatherId);
                    }
                    int motherId = reader.GetInt32(reader.GetOrdinal("MotherID"));
                    if (motherId > 0)
                    {
                        addSet.Add(motherId);
                    }
                }
            }
        }

        /// <summary>
        /// Add children of this person to the addSet
        /// </summary>
        /// <param name="child">id number of person whose children are being added</param>
        private void addChildren(int parent)
        {
            IDbCommand cmd = con.CreateCommand();
            cmd.CommandText = String.Format("SELECT PersonID FROM PersonTable, FamilyTable " +
                " WHERE ParentID = FamilyID AND (FatherID={0} OR MotherID={0})", parent);
            using (IDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    int childId = reader.GetInt32(0);
                    addSet.Add(childId);
                }
            }
        }

        /// <summary>
        /// Add spouses of this person
        /// blank or until leaf limit is reached.
        /// </summary>
        /// <param name="person">id number of person whose spouses being added</param>
        /// <param name="leaf">current leaf number</param>
        private void addSpouse(int person)
        {
            IDbCommand cmd = con.CreateCommand();
            cmd.CommandText = String.Format("SELECT FatherID, MotherID FROM FamilyTable "+
                "WHERE FatherID={0} OR MotherID={0}", person);
            using (IDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    int fatherId = reader.GetInt32(reader.GetOrdinal("FatherID"));
                    int motherId = reader.GetInt32(reader.GetOrdinal("MotherID"));
                    if(fatherId==person){
                        // This person was the father, add a mother
                        if(motherId>0)
                            addSet.Add(motherId);
                    } else {
                        // This person was the mother, add a father
                        if(fatherId>0)
                            addSet.Add(fatherId);
                    }
                }
            }
        }

        /// <summary>
        /// Generate a new group with the members of idList in it
        /// </summary>
        private void createGroup()
        {
            treeBackgroundWorker.ReportProgress(50, "\nCreating group in RM database.\n");
#if SHOW_TIMING
            timer.Restart();
#endif
            if (idSet.Count == 0)
            {
                treeBackgroundWorker.ReportProgress(50, "Empty tree, doing nothing\n");
                return;
            }
            else
            {
                treeBackgroundWorker.ReportProgress(51, String.Format("There are {0} entries in the tree\n",
                    idSet.Count));
            }
            // Convert to sorted list
            List<int> idList = idSet.ToList();
            idList.Sort();
            // Delete existing group elements
            treeBackgroundWorker.ReportProgress(60, "Removing current group\n");
            IDbCommand cmd = con.CreateCommand();
            if (groupBox.Text.Length == 0)
                groupBox.Text = "AncestorTree";
            cmd.CommandText = String.Format("SELECT LabelValue FROM LabelTable WHERE LabelName='{0}' AND LabelType=0",
                CleanField(groupBox.Text));
            object o = cmd.ExecuteScalar();
            long labelValue = -1;
            if (o != null)
            {
                labelValue = (long)o;
                cmd.CommandText = String.Format("DELETE FROM GroupTable WHERE GroupID={0}", (long)o);
                cmd.ExecuteNonQuery();
            } else {
                cmd.CommandText = "SELECT max(LabelValue) FROM LabelTable";
                object mo = cmd.ExecuteScalar();
                labelValue = 1;
                if(mo!=null)
                    labelValue = (long)mo+1;
                cmd.CommandText = String.Format(
                    "INSERT INTO LabelTable (LabelType, LabelValue, LabelName, Description) VALUES(0,{0},'{1}','')",
                    labelValue, CleanField(groupBox.Text));
                cmd.ExecuteNonQuery();
            }
#if SHOW_TIMING
            timer.Stop();
            treeBackgroundWorker.ReportProgress(65, String.Format("\tgroup created in {0} s\n",
                timer.Elapsed.TotalSeconds));
            // Add new elements to GroupTable
            timer.Restart();
#endif
            treeBackgroundWorker.ReportProgress(70, "Adding new group\n");
            // Do this in a transaction to increase speed
            cmd.CommandText = "BEGIN";
            cmd.ExecuteNonQuery();
            int last = -10;
            int first = -10;
            foreach (int pid in idList)
            {
                if (last < 0)
                {
                    // first element in list
                    last = pid;
                    first = pid;
                }
                else if (pid > last + 1)
                {
                    // I skipped at least one element
                    cmd.CommandText = String.Format("INSERT INTO GroupTable (GroupID, StartID, EndID) VALUES({0},{1},{2})",
                        labelValue, first, last);
                    cmd.ExecuteNonQuery();
                    last = pid;
                    first = pid;
                }
                else
                {
                    last = pid;
                }
            }
            if (first > 0)
            {
                cmd.CommandText = String.Format("INSERT INTO GroupTable (GroupID, StartID, EndID) VALUES({0},{1},{2})",
                    labelValue, first, last);
                cmd.ExecuteNonQuery();
            }
            // End of transaction
            cmd.CommandText = "COMMIT";
            cmd.ExecuteNonQuery();
#if SHOW_TIMING
            timer.Stop();
            treeBackgroundWorker.ReportProgress(75, String.Format("\tgroup filled in {0} s\n",
                timer.Elapsed.TotalSeconds));
#endif
        }

        private void treeBackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            addGroup();
        }

        private void treeBackgroundWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            string msg = (string)e.UserState;
            if (msg == ".")
            {
                TimeSpan ts = processTimer.Elapsed;
                timeLabel.Text = String.Format("{0:d2}:{1:d2}", ts.Minutes, ts.Seconds);
                nameLabel.Text = String.Format("{0} names", e.ProgressPercentage);
            }
            else
            {
                statusBox.Text += msg;
            }
        }

        private void treeBackgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            statusBox.Text += "Finished\n";
            fileButton.Enabled = true;
            processButton.Enabled = true;
            generationsBox.Enabled = true;
            leavesBox.Enabled = true;
            spouseBox.Enabled = true;
            groupBox.Enabled = true;
            processTimer.Stop();
        }

        private void rootButton_Click(object sender, EventArgs e)
        {
            if (filename.Length == 0)
            {
                MessageBox.Show("You can't set the root person until you select a database file. " +
                    "Please make this selection and then try again.", "No Database File",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (nameList.Count == 0)
                loadNames();
            if (nameList.Count > 0)
            {
                SelectPersonForm perForm = new SelectPersonForm(nameList);
                if (perForm.ShowDialog(this) == DialogResult.OK)
                {
                    rootPerson = perForm.RIN;
                    if(rootPerson>0)
                        setRootPerson(rootPerson);
                }
            }
            else
            {
                MessageBox.Show("There are no names to select from.  Probably this is because you " +
                    "didn't supply the name of a database file or the file is empty.", "No Names",
                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void aboutToolStripMenuItem_Click(object sender, EventArgs e)
        {
            new AboutRMSplit().ShowDialog(this);
        }

        private void applicationToolStripMenuItem_Click(object sender, EventArgs e)
        {
            new RMSplitHelp().Show();
        }

        /// <summary>
        /// Clean fields for an SQL SELECT or INSERT statement that might contain
        /// embedded ' % or _ characters.
        /// </summary>
        /// <param name="str">"fixed" string with these characters removed</param>
        /// <returns></returns>
        private String CleanField(String str)
        {
            StringBuilder rslt = new StringBuilder(str);
            rslt.Replace("'", "''");
            rslt.Replace("%", "\\%");
            rslt.Replace("_", "\\_");
            return rslt.ToString();
        }

        private void displayToolStripMenuItem_Click(object sender, EventArgs e)
        {
        }

    }
}
