Example #1
0
        /// <summary>
        /// Exports the individuals.
        /// </summary>
        /// <param name="modifiedSince">The modified since.</param>
        /// <param name="peoplePerPage">The people per page.</param>
        public override void ExportIndividuals(DateTime modifiedSince, int peoplePerPage = 500)
        {
            try
            {
                // write out the person attributes
                var personAttributes = WritePersonAttributes();

                var dtAddress             = GetTableData(SQL_ADDRESSES);
                var dtCommunications      = GetTableData(SQL_COMMUNICATIONS);
                var dtAttributeValues     = GetTableData(SQL_ATTRIBUTEVALUES);
                var dtRequirementValues   = GetTableData(SQL_REQUIREMENTVALUES);
                var dtCommunicationValues = GetTableData(SQL_COMMUNCATION_ATTRIBUTE_VALUES);
                var dtPhoneNumbers        = GetTableData(SQL_PHONE_NUMBERS);

                // export people
                using (var dtHoh = GetTableData(SQL_HEAD_OF_HOUSEHOLD))
                    using (var dtPeople = GetTableData(SQL_PEOPLE))
                    {
                        var headOfHouseHolds = GetHeadOfHouseholdMap(dtHoh);

                        foreach (DataRow row in dtPeople.Rows)
                        {
                            var importPerson = F1Person.Translate(row, dtCommunications, headOfHouseHolds, dtRequirementValues, dtCommunicationValues);

                            if (importPerson != null)
                            {
                                ImportPackage.WriteToPackage(importPerson);
                            }
                        }

                        // export people addresses
                        foreach (DataRow row in dtAddress.Rows)
                        {
                            var importAddress = F1PersonAddress.Translate(row, dtPeople);

                            if (importAddress != null)
                            {
                                ImportPackage.WriteToPackage(importAddress);
                            }
                        }

                        // export Attribute Values
                        foreach (DataRow row in dtAttributeValues.Rows)
                        {
                            var importAttributes = F1PersonAttributeValue.Translate(row);

                            if (importAttributes != null)
                            {
                                foreach (PersonAttributeValue value in importAttributes)
                                {
                                    ImportPackage.WriteToPackage(value);
                                }
                            }
                        }

                        // export Phone Numbers
                        foreach (DataRow row in dtPhoneNumbers.Rows)
                        {
                            var importNumber = F1PersonPhone.Translate(row);
                            if (importNumber != null)
                            {
                                ImportPackage.WriteToPackage(importNumber);
                            }
                        }
                    }
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message;
            }
        }
        /// <summary>
        /// Internal method for ExportIndividuals().
        /// </summary>
        /// <param name="modifiedSince">The modified since.</param>
        /// <param name="peoplePerPage">The people per page.</param>
        private void ExportIndividuals_Internal(DateTime modifiedSince, int peoplePerPage = 500)
        {
            // write out the person attributes
            WritePersonAttributes();

            // export people (and their addresses).
            using (var dtPeople = GetTableData(SqlQueries.PEOPLE, true))
            {
                // export people
                using (var dtHoh = GetTableData(SqlQueries.HEAD_OF_HOUSEHOLD, true))
                    using (var dtCommunications = GetTableData(SqlQueries.COMMUNICATIONS))
                        using (var dtCommunicationValues = GetTableData(SqlQueries.COMMUNCATION_ATTRIBUTE_VALUES))
                            using (var dtRequirementValues = GetTableData(SqlQueries.REQUIREMENTVALUES))
                            {
                                // Make Requirement Names match case, as they may be in mixed cases in the F1 database.
                                foreach (DataRow row in dtRequirementValues.Rows)
                                {
                                    string requirementName = row["requirement_name"].ToString();
                                    if (_RequirementNames.ContainsKey(requirementName.ToLower()))
                                    {
                                        if (_RequirementNames[requirementName.ToLower()] != requirementName)
                                        {
                                            row["requirement_name"] = _RequirementNames[requirementName.ToLower()];
                                        }
                                    }
                                }

                                var headOfHouseHolds = GetHeadOfHouseholdMap(dtHoh);

                                //Split communications into basic elements to make subsequent queries faster.
                                var individualEmailRows = dtCommunications.Select("individual_id IS NOT NULL AND communication_type = 'Email'");
                                var dtCommunications_IndividualEmails = individualEmailRows.CopyToDataTable_Safe(dtCommunications);

                                var infellowshipLoginRows = dtCommunications.Select("individual_id IS NOT NULL AND communication_type = 'Infellowship Login'");
                                var dtCommunications_InfellowshipLogins = infellowshipLoginRows.CopyToDataTable_Safe(dtCommunications);

                                var householdEmails = dtCommunications.Select("individual_id IS NULL AND household_id IS NOT NULL AND communication_type = 'Email'");
                                var dtCommunications_HouseholdEmails = householdEmails.CopyToDataTable_Safe(dtCommunications);

                                foreach (DataRow row in dtPeople.Rows)
                                {
                                    var importPerson = F1Person.Translate(row, dtCommunications_IndividualEmails, dtCommunications_InfellowshipLogins, dtCommunications_HouseholdEmails, headOfHouseHolds, dtRequirementValues, dtCommunicationValues);

                                    if (importPerson != null)
                                    {
                                        ImportPackage.WriteToPackage(importPerson);
                                    }
                                }

                                // Cleanup - Remember not to Clear() any cached tables.
                                dtCommunications.Clear();
                                dtCommunicationValues.Clear();
                                dtRequirementValues.Clear();
                                GC.Collect();
                            }

                // export people addresses
                using (var dtAddress = GetTableData(SqlQueries.ADDRESSES))
                {
                    foreach (DataRow row in dtAddress.Rows)
                    {
                        var importAddress = F1PersonAddress.Translate(row, dtPeople);

                        if (importAddress != null)
                        {
                            ImportPackage.WriteToPackage(importAddress);
                        }
                    }

                    // Cleanup - Remember not to Clear() any cached tables.
                    dtAddress.Clear();
                    GC.Collect();
                }

                // export Phone Numbers
                using (var dtPhoneNumbers = GetTableData(SqlQueries.PHONE_NUMBERS))
                {
                    foreach (DataRow row in dtPhoneNumbers.Rows)
                    {
                        //Household phone numbers may be assigned to multiple person records (i.e., "Head" and "Spouse").
                        var personIds = F1PersonPhone.GetPhonePersonIds(row, dtPeople);
                        foreach (int personId in personIds)
                        {
                            var importNumber = F1PersonPhone.Translate(row, personId);
                            if (importNumber != null)
                            {
                                ImportPackage.WriteToPackage(importNumber);
                            }
                        }
                    }

                    // Cleanup - Remember not to Clear() any cached tables.
                    dtPhoneNumbers.Clear();
                    GC.Collect();
                }
            }

            // export Attribute Values
            using (var dtAttributeValues = GetTableData(SqlQueries.ATTRIBUTEVALUES))
            {
                foreach (DataRow row in dtAttributeValues.Rows)
                {
                    var importAttributes = F1PersonAttributeValue.Translate(row);

                    if (importAttributes != null)
                    {
                        foreach (PersonAttributeValue value in importAttributes)
                        {
                            ImportPackage.WriteToPackage(value);
                        }
                    }
                }

                // Cleanup - Remember not to Clear() any cached tables.
                dtAttributeValues.Clear();
                GC.Collect();
            }
        }
Example #3
0
        /// <summary>
        /// Internal method for ExportIndividuals().
        /// </summary>
        /// <param name="modifiedSince">The modified since.</param>
        /// <param name="peoplePerPage">The people per page.</param>
        private void ExportIndividuals_Internal(DateTime modifiedSince, int peoplePerPage = 500)
        {
            // write out the person attributes
            WritePersonAttributes();

            // export people (and their addresses).
            var      dtPeople        = _db.Table("Individual_Household").Data;
            DataView dvCommunication = _db.Table("Communication").Data.DefaultView;

            dvCommunication.Sort = "LastUpdatedDate"; // Communications must be sorted by LastUpdatedDate to ensure we get the right values.
            var dtCommunications = dvCommunication.ToTable();

            // export people

            var dtOtherCommunications = dtCommunications
                                        .Select("Communication_Type NOT IN ('Mobile', 'Email') AND Communication_Type NOT LIKE '%Phone%' AND Individual_ID IS NOT NULL");

            var dtCommunicationAttributeValueFilter =
                from a in dtOtherCommunications
                group a by new
            {
                CommunicationType = a.Field <string>("communication_type"),
                IndividualId      = a.Field <int>("individual_id")
            }
            into g
            select(from t2 in g select t2.Field <int>("Communication_Id")).Max();

            var dtCommunicationAttributeValues = (from values in dtOtherCommunications
                                                  join filter in dtCommunicationAttributeValueFilter
                                                  on values.Field <int>("Communication_Id") equals filter
                                                  select values).ToList().CopyToDataTable_Safe(dtCommunications);



            var dtRawRequirementValues   = _db.Table("Requirement").Data.AsEnumerable();
            var dtRequirementValueFilter =
                from a in dtRawRequirementValues
                group a by new
            {
                RequirementName = a.Field <string>("requirement_name"),
                IndividualId    = a.Field <int>("individual_id")
            }
            into g
            select(from t2 in g select t2.Field <int>("Individual_Requirement_ID")).Max();

            //new {
            //    CommunicationId = ( from t2 in g
            //                        select t2.Field<int>( "Communication_Id" ) ).Max()
            //};

            var dtRequirementValues = (from values in dtRawRequirementValues
                                       join filter in dtRequirementValueFilter
                                       on values.Field <int>("Individual_Requirement_ID") equals filter
                                       select values).ToList().CopyToDataTable_Safe(_db.Table("Requirement").Data);

            // Make Requirement Names match case, as they may be in mixed cases in the F1 database.
            foreach (DataRow row in dtRequirementValues.Rows)
            {
                string requirementName = row["requirement_name"].ToString();
                if (_RequirementNames.ContainsKey(requirementName))
                {
                    if (_RequirementNames[requirementName] != requirementName)
                    {
                        row["requirement_name"] = _RequirementNames[requirementName];
                    }
                }
            }

            var headOfHouseHolds = GetHeadOfHouseholdMap(dtPeople);

            //Split communications into basic elements to make subsequent queries faster.
            var individualEmailRows = dtCommunications.Select("individual_id IS NOT NULL AND communication_type = 'Email'");
            var dtCommunications_IndividualEmails = individualEmailRows.CopyToDataTable_Safe(dtCommunications);

            var infellowshipLoginRows = dtCommunications.Select("individual_id IS NOT NULL AND communication_type = 'Infellowship Login'");
            var dtCommunications_InfellowshipLogins = infellowshipLoginRows.CopyToDataTable_Safe(dtCommunications);

            var householdEmails = dtCommunications.Select("individual_id IS NULL AND household_id IS NOT NULL AND communication_type = 'Email'");
            var dtCommunications_HouseholdEmails = householdEmails.CopyToDataTable_Safe(dtCommunications);

            foreach (DataRow row in dtPeople.Rows)
            {
                var importPerson = F1Person.Translate(row, dtCommunications_IndividualEmails, dtCommunications_InfellowshipLogins, dtCommunications_HouseholdEmails, headOfHouseHolds, dtRequirementValues, dtCommunicationAttributeValues);

                if (importPerson != null)
                {
                    ImportPackage.WriteToPackage(importPerson);
                }
            }

            // export people addresses
            var dtAddress = _db.Table("Household_Address").Data;

            foreach (DataRow row in dtAddress.Rows)
            {
                var importAddress = F1PersonAddress.Translate(row, dtPeople);

                if (importAddress != null)
                {
                    ImportPackage.WriteToPackage(importAddress);
                }
            }

            // export people attributes
            var individualAttributes =
                from a in _db.Table("Attribute").Data.AsEnumerable()
                group a by new
            {
                IndividualId  = a.Field <int>("Individual_Id"),
                AttributeId   = a.Field <int>("Attribute_Id"),
                AttributeName = a.Field <string>("Attribute_Name")
            }
            into g
                select new
            {
                g.Key.IndividualId,
                g.Key.AttributeId,
                g.Key.AttributeName,
                IndividualAttributeId = (from t2 in g
                                         select t2.Field <int>("Individual_attribute_Id"))
                                        .Max()
            };

            var dtAttributeValues = (from table1 in _db.Table("Attribute").Data.AsEnumerable()
                                     join table2 in individualAttributes
                                     on table1.Field <int>("Individual_attribute_Id") equals table2.IndividualAttributeId
                                     select table1).ToList().CopyToDataTable_Safe(_db.Table("Attribute").Data);

            foreach (DataRow row in dtAttributeValues.Rows)
            {
                var importAttributes = F1PersonAttributeValue.Translate(row);

                if (importAttributes != null)
                {
                    foreach (PersonAttributeValue value in importAttributes)
                    {
                        ImportPackage.WriteToPackage(value);
                    }
                }
            }

            // export Phone Numbers
            var phoneNumbers = dtCommunications.Select("(individual_id IS NOT NULL OR household_id IS NOT NULL) AND (communication_type = 'Mobile' OR communication_type like '%Phone%')");

            foreach (DataRow row in phoneNumbers)
            {
                //Household phone numbers may be assigned to multiple person records (i.e., "Head" and "Spouse").
                var personIds = F1PersonPhone.GetPhonePersonIds(row, dtPeople);
                foreach (int personId in personIds)
                {
                    var importNumber = F1PersonPhone.Translate(row, personId);
                    if (importNumber != null)
                    {
                        ImportPackage.WriteToPackage(importNumber);
                    }
                }
            }

            // Cleanup.
            phoneNumbers = null;
            dtCommunications.Clear();
            dtAttributeValues.Clear();
            GC.Collect();
        }
Example #4
0
        // Please review GetTableData_CallOrder.txt before modifying or copying any methods which use the GetTableData() method.

        /// <summary>
        /// Internal method for ExportIndividuals().
        /// </summary>
        /// <param name="modifiedSince">The modified since.</param>
        /// <param name="peoplePerPage">The people per page.</param>
        private void ExportIndividuals_Internal(DateTime modifiedSince, int peoplePerPage = 500)
        {
            // write out the person attributes
            WritePersonAttributes();

            // export people (and their addresses).
            using (var dtPeople = GetTableData(SqlQueries.PEOPLE, true))
            {
                // export people
                using (var dtHoh = GetTableData(SqlQueries.HEAD_OF_HOUSEHOLD, true))
                    using (var dtCommunications = GetTableData(SqlQueries.COMMUNICATIONS))
                        using (var dtCommunicationValues = GetTableData(SqlQueries.COMMUNCATION_ATTRIBUTE_VALUES))
                            using (var dtRequirementValues = GetTableData(SqlQueries.REQUIREMENTVALUES))
                            {
                                // Make Requirement Names match case, as they may be in mixed cases in the F1 database.
                                foreach (DataRow row in dtRequirementValues.Rows)
                                {
                                    string requirementName = row["requirement_name"].ToString();
                                    if (_RequirementNames.ContainsKey(requirementName.ToLower()))
                                    {
                                        if (_RequirementNames[requirementName.ToLower()] != requirementName)
                                        {
                                            row["requirement_name"] = _RequirementNames[requirementName.ToLower()];
                                        }
                                    }
                                }

                                var headOfHouseHolds = GetHeadOfHouseholdMap(dtHoh);
                                foreach (DataRow row in dtPeople.Rows)
                                {
                                    var importPerson = F1Person.Translate(row, dtCommunications, headOfHouseHolds, dtRequirementValues, dtCommunicationValues);

                                    if (importPerson != null)
                                    {
                                        ImportPackage.WriteToPackage(importPerson);
                                    }
                                }

                                // Cleanup - Remember not to Clear() any cached tables.
                                dtCommunications.Clear();
                                dtCommunicationValues.Clear();
                                dtRequirementValues.Clear();
                                GC.Collect();
                            }

                // export people addresses
                using (var dtAddress = GetTableData(SqlQueries.ADDRESSES))
                {
                    foreach (DataRow row in dtAddress.Rows)
                    {
                        var importAddress = F1PersonAddress.Translate(row, dtPeople);

                        if (importAddress != null)
                        {
                            ImportPackage.WriteToPackage(importAddress);
                        }
                    }

                    // Cleanup - Remember not to Clear() any cached tables.
                    dtAddress.Clear();
                    GC.Collect();
                }
            }

            // export Attribute Values
            using (var dtAttributeValues = GetTableData(SqlQueries.ATTRIBUTEVALUES))
            {
                foreach (DataRow row in dtAttributeValues.Rows)
                {
                    var importAttributes = F1PersonAttributeValue.Translate(row);

                    if (importAttributes != null)
                    {
                        foreach (PersonAttributeValue value in importAttributes)
                        {
                            ImportPackage.WriteToPackage(value);
                        }
                    }
                }

                // Cleanup - Remember not to Clear() any cached tables.
                dtAttributeValues.Clear();
                GC.Collect();
            }

            // export Phone Numbers
            using (var dtPhoneNumbers = GetTableData(SqlQueries.PHONE_NUMBERS))
            {
                foreach (DataRow row in dtPhoneNumbers.Rows)
                {
                    var importNumber = F1PersonPhone.Translate(row);
                    if (importNumber != null)
                    {
                        ImportPackage.WriteToPackage(importNumber);
                    }
                }

                // Cleanup - Remember not to Clear() any cached tables.
                dtPhoneNumbers.Clear();
                GC.Collect();
            }
        }
Example #5
0
        /// <summary>
        /// Exports the individuals.
        /// </summary>
        /// <param name="modifiedSince">The modified since.</param>
        /// <param name="peoplePerPage">The people per page.</param>
        public override void ExportIndividuals(DateTime modifiedSince, int peoplePerPage = 500)
        {
            TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;

            HashSet <int> personIds = new HashSet <int>();

            try
            {
                // write out the person attributes
                var personAttributes = WritePersonAttributes();

                var dtAddress             = GetTableData(SQL_ADDRESSES);
                var dtCommunications      = GetTableData(SQL_COMMUNICATIONS);
                var dtAttributeValues     = GetTableData(SQL_ATTRIBUTEVALUES);
                var dtRequirementValues   = GetTableData(SQL_REQUIREMENTVALUES);
                var dtCommunicationValues = GetTableData(SQL_COMMUNCATION_ATTRIBUTE_VALUES);
                var dtPhoneNumbers        = GetTableData(SQL_PHONE_NUMBERS);


                // export people
                using (var dtPeople = GetTableData(SQL_PEOPLE))
                {
                    var headOfHouseHolds = dtPeople.Select("household_position = 'Head' ");
                    foreach (DataRow row in dtPeople.Rows)
                    {
                        var importPerson = F1Person.Translate(row, dtCommunications, headOfHouseHolds, dtRequirementValues, dtCommunicationValues);

                        if (importPerson != null)
                        {
                            ImportPackage.WriteToPackage(importPerson);
                        }
                    }

                    // export people addresses
                    foreach (DataRow row in dtAddress.Rows)
                    {
                        var importAddress = F1PersonAddress.Translate(row, dtPeople);

                        if (importAddress != null)
                        {
                            ImportPackage.WriteToPackage(importAddress);
                        }
                    }

                    // export Attribute Values
                    foreach (DataRow row in dtAttributeValues.Rows)
                    {
                        var importAttributes = F1PersonAttributeValue.Translate(row);

                        if (importAttributes != null)
                        {
                            foreach (PersonAttributeValue value in importAttributes)
                            {
                                ImportPackage.WriteToPackage(value);
                            }
                        }
                    }

                    // export Phone Numbers
                    foreach (DataRow row in dtPhoneNumbers.Rows)
                    {
                        var importNumber = F1PersonPhone.Translate(row);
                        if (importNumber != null)
                        {
                            ImportPackage.WriteToPackage(importNumber);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message;
            }
        }