/// <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();
}
}
/// <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();
}
// 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();
}
}
/// <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;
}
}