Example #1
0
        public static Dictionary <string, string> GetLocalElections(string stateElectionKey,
                                                                    string countyCode, IEnumerable <string> localKeys = null, int commandTimeout = -1)
        {
            if (localKeys == null)
            {
                localKeys =
                    LocalDistricts.GetLocalKeysForCounty(Elections.GetStateCodeFromKey(stateElectionKey),
                                                         countyCode);
            }
            var localKeysClause = localKeys.SqlIn("r.LocalKey");
            var altElectionKey  = Elections.GetElectionKeyToInclude(stateElectionKey);
            var cmdText         = "SELECT r.LocalKey,r.ElectionKey AS ElectionKey FROM Referendums r" +
                                  $" WHERE r.ElectionKeyState IN (@ElectionKeyState,@AltElectionKey) AND {localKeysClause}" +
                                  " GROUP BY r.LocalKey";
            var cmd = VoteDb.GetCommand(cmdText, commandTimeout);

            VoteDb.AddCommandParameter(cmd, "ElectionKeyState", stateElectionKey);
            VoteDb.AddCommandParameter(cmd, "AltElectionKey", altElectionKey);
            VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
            using (var cn = VoteDb.GetOpenConnection())
            {
                cmd.Connection = cn;
                var           table   = new DataTable();
                DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
                adapter.Fill(table);
                return(table.Rows.OfType <DataRow>()
                       .ToDictionary(row => row.LocalKey(), row => row.ElectionKey()));
            }
        }
Example #2
0
        public static Dictionary <string, string> GetLocalElections(string stateElectionKey,
                                                                    string countyCode, IEnumerable <string> localKeys = null, bool allowEmptyOffices = false,
                                                                    int commandTimeout = -1)
        {
            if (localKeys == null)
            {
                localKeys =
                    LocalDistricts.GetLocalKeysForCounty(Elections.GetStateCodeFromKey(stateElectionKey),
                                                         countyCode);
            }
            var localKeysClause = localKeys.SqlIn("eo.LocalKey");
            var altElectionKey  = Elections.GetElectionKeyToInclude(stateElectionKey);
            var cmdText         =
                "SELECT eo.LocalKey,eo.ElectionKey FROM ElectionsOffices eo" +
                (allowEmptyOffices
          ? Empty
          : " INNER JOIN ElectionsPoliticians ep ON ep.ElectionKey=eo.ElectionKey") +
                $" WHERE eo.ElectionKeyState IN (@ElectionKeyState,@AltElectionKey) AND {localKeysClause}" +
                " GROUP BY eo.LocalKey";
            var cmd = VoteDb.GetCommand(cmdText, commandTimeout);

            VoteDb.AddCommandParameter(cmd, "ElectionKeyState", stateElectionKey);
            VoteDb.AddCommandParameter(cmd, "AltElectionKey", altElectionKey);
            VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
            using (var cn = VoteDb.GetOpenConnection())
            {
                cmd.Connection = cn;
                var           table   = new DataTable();
                DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
                adapter.Fill(table);
                return(table.Rows.OfType <DataRow>()
                       .ToDictionary(row => row.LocalKey(), row => row.ElectionKey()));
            }
        }
Example #3
0
        public static Dictionary <string, bool> HasCountyOrLocalOfficesByCounty(
            string stateCode, int commandTimeout = -1)
        {
            using (var cn = VoteDb.GetOpenConnection())
            {
                // first create a dictionary of all counties in the state
                var dictionary = CountyCache.GetCountiesByState(stateCode)
                                 .ToDictionary(c => c, c => false);

                // the first query just gets the county offices
                const string cmdText =
                    "SELECT CountyCode,COUNT(*) AS Count FROM Offices" +
                    " WHERE StateCode=@StateCode AND CountyCode<>''" +
                    " GROUP BY CountyCode";
                var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
                VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
                cmd.Connection = cn;
                var           table   = new DataTable();
                DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
                adapter.Fill(table);
                // counties tagged for deletion won't be in the table
                foreach (var c in table.Rows.OfType <DataRow>())
                {
                    if (dictionary.ContainsKey(c.CountyCode()) && c.Count() > 0)
                    {
                        dictionary[c.CountyCode()] = true;
                    }
                }

                // we only have to query locals for counties that are still false
                foreach (var c in dictionary.Where(kvp => !kvp.Value).Select(kvp => kvp.Key).ToList())
                {
                    // we need to do a pre-query to get all the locals in the selected county
                    var localKeysClause = LocalDistricts.GetLocalKeysForCounty(stateCode, c)
                                          .SqlIn("LocalKey");
                    var cmdText2 = "SELECT COUNT(*) AS Count FROM Offices" +
                                   $" WHERE StateCode=@StateCode AND {localKeysClause}";
                    var cmd2 = VoteDb.GetCommand(cmdText2, commandTimeout);
                    VoteDb.AddCommandParameter(cmd2, "StateCode", stateCode);
                    cmd2.Connection = cn;
                    if (Convert.ToInt32(cmd2.ExecuteScalar()) > 0)
                    {
                        dictionary[c] = true;
                    }
                }

                // only return the trues
                return(dictionary.Where(kvp => kvp.Value)
                       .ToDictionary(kvp => kvp.Key, kvp => kvp.Value));
            }
        }
Example #4
0
        public static Dictionary <string, string> GetLocalNamesWithOffices(
            string stateCode, string countyCode, int commandTimeout = -1)
        {
            // we need to do a pre-query to get all the locals in the selected county
            var localKeysClause =
                LocalDistricts.GetLocalKeysForCounty(stateCode, countyCode).SqlIn("o.LocalKey");
            var cmdText = "SELECT o.LocalKey,l.LocalDistrict FROM Offices o" +
                          " INNER JOIN LocalDistricts l ON l.StateCode = o.StateCode" +
                          "  AND l.localKey = o.LocalKey" +
                          $" WHERE o.StateCode=@StateCode AND {localKeysClause}" + " GROUP BY o.LocalKey" +
                          " ORDER BY l.LocalDistrict";
            var cmd = VoteDb.GetCommand(cmdText, commandTimeout);

            VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
            using (var cn = VoteDb.GetOpenConnection())
            {
                cmd.Connection = cn;
                var           table   = new DataTable();
                DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
                adapter.Fill(table);
                return(table.Rows.OfType <DataRow>()
                       .ToDictionary(row => row.LocalKey(), row => row.LocalDistrict()));
            }
        }