Esempio n. 1
0
        public static OrganizationsData GetOrganizationsStructuralData()
        {
            // like above but does not return data for specific organizations
            const string cmdText =
                "SELECT ot.OrgTypeId,ot.OrgType FROM OrganizationTypes ot" +
                " ORDER BY ot.OrgTypeOrder";

            var result = new OrganizationsData();
            var cmd    = VoteDb.GetCommand(cmdText);

            using (var cn = VoteDb.GetOpenConnection())
            {
                cmd.Connection = cn;
                var           table   = new DataTable();
                DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
                adapter.Fill(table);
                result.OrgTypes = table.Rows.OfType <DataRow>().GroupBy(r => r.OrgTypeId()).Select(
                    g => new OneOrgType
                {
                    OrgTypeId = g.First().OrgTypeId(),
                    OrgType   = g.First().OrgType()
                }).ToArray();
            }

            // attach sub types
            var subTypeDictionary = OrganizationSubTypes.GetAllData().GroupBy(r => r.OrgTypeId)
                                    .ToDictionary(g => g.Key, g => g);

            foreach (var orgType in result.OrgTypes)
            {
                if (subTypeDictionary.ContainsKey(orgType.OrgTypeId))
                {
                    orgType.SubTypes = subTypeDictionary[orgType.OrgTypeId]
                                       .OrderBy(t => t.OrgSubTypeOrder).Select(t =>
                                                                               new OneOrgTypeSubType
                    {
                        OrgSubTypeId = t.OrgSubTypeId,
                        OrgSubType   = t.OrgSubType
                    }).ToArray();
                }
                else
                {
                    orgType.SubTypes = new OneOrgTypeSubType[0];
                }
            }

            // attach ideologies
            result.Ideologies = OrganizationIdeologies.GetAllData().OrderBy(r => r.IdeologyOrder)
                                .Select(r => new OneIdeology {
                IdeologyId = r.IdeologyId, Ideology = r.Ideology
            })
                                .ToArray();

            // attach email tags
            var tagDictionary = OrganizationEmailTags.GetAllData().GroupBy(r => r.OrgTypeId)
                                .ToDictionary(g => g.Key, g => g);

            foreach (var orgType in result.OrgTypes)
            {
                if (tagDictionary.ContainsKey(orgType.OrgTypeId))
                {
                    orgType.EmailTags = tagDictionary[orgType.OrgTypeId].OrderBy(t => t.EmailTagOrder)
                                        .Select(t =>
                                                new OneOrgTypeEmailTag {
                        EmailTagId = t.EmailTagId, EmailTag = t.EmailTag
                    })
                                        .ToArray();
                }
                else
                {
                    orgType.EmailTags = new OneOrgTypeEmailTag[0];
                }
            }

            return(result);
        }
Esempio n. 2
0
        public static OrganizationsData GetOrganizationsData()
        {
            const string cmdText =
                "SELECT ot.OrgTypeId,ot.OrgType,(o.OrgId IS NULL) AS IsNull,o.OrgId,o.OrgTypeId," +
                "o.OrgSubTypeId,o.IdeologyId,o.DateStamp,o.Name,o.OrgAbbreviation,o.Address1,o.Address2,o.City," +
                "o.StateCode,o.Zip,o.Url,o.LongMission,o.ShortMission,o.EmailMission" +
                " FROM OrganizationTypes ot" +
                " LEFT OUTER JOIN Organizations o ON o.OrgTypeId = ot.OrgTypeId" +
                " ORDER BY ot.OrgTypeOrder,o.Name";

            var result = new OrganizationsData();
            var cmd    = VoteDb.GetCommand(cmdText);

            using (var cn = VoteDb.GetOpenConnection())
            {
                cmd.Connection = cn;
                var           table   = new DataTable();
                DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
                adapter.Fill(table);
                result.OrgTypes = table.Rows.OfType <DataRow>().GroupBy(r => r.OrgTypeId()).Select(
                    g => new OneOrgType
                {
                    OrgTypeId     = g.First().OrgTypeId(),
                    OrgType       = g.First().OrgType(),
                    Organizations = g.Where(r => r.OrgId() != 0).Select(r => new OneOrganization
                    {
                        OrgId           = r.OrgId(),
                        OrgTypeId       = g.First().OrgTypeId(),
                        OrgSubTypeId    = r.OrgSubTypeId(),
                        IdeologyId      = r.IdeologyId(),
                        DateStamp       = r.DateStamp().AsUtc(),
                        Name            = r.Name(),
                        OrgAbbreviation = r.OrgAbbreviation(),
                        Address1        = r.Address1(),
                        Address2        = r.Address2(),
                        City            = r.City(),
                        StateCode       = r.StateCode(),
                        Zip             = r.Zip(),
                        Url             = r.Url(),
                        LongMission     = r.LongMission(),
                        ShortMission    = r.ShortMission(),
                        EmailMission    = r.EmailMission()
                    }).ToArray()
                }).ToArray();
            }

            // attach sub types
            var subTypeDictionary = OrganizationSubTypes.GetAllData().GroupBy(r => r.OrgTypeId)
                                    .ToDictionary(g => g.Key, g => g);

            foreach (var orgType in result.OrgTypes)
            {
                if (subTypeDictionary.ContainsKey(orgType.OrgTypeId))
                {
                    orgType.SubTypes = subTypeDictionary[orgType.OrgTypeId]
                                       .OrderBy(t => t.OrgSubTypeOrder).Select(t =>
                                                                               new OneOrgTypeSubType
                    {
                        OrgSubTypeId = t.OrgSubTypeId, OrgSubType = t.OrgSubType
                    }).ToArray();
                }
                else
                {
                    orgType.SubTypes = new OneOrgTypeSubType[0];
                }
            }

            // attach ideologies
            result.Ideologies = OrganizationIdeologies.GetAllData().OrderBy(r => r.IdeologyOrder)
                                .Select(r => new OneIdeology {
                IdeologyId = r.IdeologyId, Ideology = r.Ideology
            })
                                .ToArray();

            // attach email tags
            var tagDictionary = OrganizationEmailTags.GetAllData().GroupBy(r => r.OrgTypeId)
                                .ToDictionary(g => g.Key, g => g);

            foreach (var orgType in result.OrgTypes)
            {
                if (tagDictionary.ContainsKey(orgType.OrgTypeId))
                {
                    orgType.EmailTags = tagDictionary[orgType.OrgTypeId].OrderBy(t => t.EmailTagOrder)
                                        .Select(t =>
                                                new OneOrgTypeEmailTag {
                        EmailTagId = t.EmailTagId, EmailTag = t.EmailTag
                    })
                                        .ToArray();
                }
                else
                {
                    orgType.EmailTags = new OneOrgTypeEmailTag[0];
                }
            }

            // attach assigned email tags
            var assignedTagDictionary = OrganizationAssignedEmailTags.GetAllData()
                                        .GroupBy(r => r.OrgId).ToDictionary(g => g.Key, g => g);

            foreach (var orgType in result.OrgTypes)
            {
                foreach (var org in orgType.Organizations)
                {
                    if (assignedTagDictionary.ContainsKey(org.OrgId))
                    {
                        org.EmailTagIds = assignedTagDictionary[org.OrgId].Select(t => t.EmailTagId)
                                          .OrderBy(id => id).ToArray();
                    }
                    else
                    {
                        org.EmailTagIds = new int[0];
                    }
                }
            }

            // attach contacts
            var contactsDictionary = OrganizationContacts.GetAllData().GroupBy(r => r.OrgId)
                                     .ToDictionary(g => g.Key, g => g);

            foreach (var orgType in result.OrgTypes)
            {
                foreach (var org in orgType.Organizations)
                {
                    if (contactsDictionary.ContainsKey(org.OrgId))
                    {
                        org.Contacts = contactsDictionary[org.OrgId].OrderBy(c => c.ContactOrder).Select(
                            c => new OneOrganizationContact
                        {
                            ContactId = c.ContactId,
                            Contact   = c.Contact,
                            Email     = c.Email,
                            Phone     = c.Phone,
                            Title     = c.Title
                        }).ToArray();
                    }
                    else
                    {
                        org.Contacts = new OneOrganizationContact[0];
                    }
                }
            }

            // attach mission urls
            var missionUrlsDictionary = OrganizationMissionUrls.GetAllData().GroupBy(r => r.OrgId)
                                        .ToDictionary(g => g.Key, g => g);

            foreach (var orgType in result.OrgTypes)
            {
                foreach (var org in orgType.Organizations)
                {
                    if (missionUrlsDictionary.ContainsKey(org.OrgId))
                    {
                        org.MissionUrls = missionUrlsDictionary[org.OrgId].OrderBy(c => c.UrlOrder)
                                          .Select(c => new OneOrganizationMissionUrl
                        {
                            OrgMissionUrlId = c.OrgMissionUrlId, Url = c.Url
                        }).ToArray();
                    }
                    else
                    {
                        org.MissionUrls = new OneOrganizationMissionUrl[0];
                    }
                }
            }

            return(result);
        }