/// <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>
/// 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>();
// if empty, build head of household lookups
if (!familyMembers.Any())
{
familyMembers = GetFamilyMembers();
}
// write out the person attributes
var personAttributes = WritePersonAttributes();
int currentPage = 1;
int loopCounter = 0;
bool moreIndividualsExist = true;
try
{
while (moreIndividualsExist)
{
_client.Authenticator = OAuth1Authenticator.ForProtectedResource(ApiConsumerKey, ApiConsumerSecret, OAuthToken, OAuthSecret);
_request = new RestRequest(API_INDIVIDUALS, Method.GET);
_request.AddQueryParameter("lastUpdatedDate", modifiedSince.ToShortDateString());
_request.AddQueryParameter("recordsPerPage", peoplePerPage.ToString());
_request.AddQueryParameter("page", currentPage.ToString());
_request.AddQueryParameter("include", "addresses,attributes,communications,requirements");
_request.AddHeader("content-type", "application/vnd.fellowshiponeapi.com.people.people.v2+xml");
var response = _client.Execute(_request);
ApiCounter++;
XDocument xdoc = XDocument.Parse(response.Content);
if (F1Api.DumpResponseToXmlFile)
{
xdoc.Save(Path.Combine(ImportPackage.PackageDirectory, $"API_INDIVIDUALS_ResponseLog_{loopCounter}.xml"));
}
var records = xdoc.Element("results");
if (records != null)
{
var returnCount = records.Attribute("count")?.Value.AsIntegerOrNull();
var additionalPages = records.Attribute("additionalPages").Value.AsInteger();
if (returnCount.HasValue)
{
foreach (var personNode in records.Elements())
{
if (personNode.Attribute("id") != null && personNode.Attribute("id").Value.AsIntegerOrNull().HasValue)
{
// If a person is updated during an export, the person could be returned
// twice by the API.
if (!personIds.Contains(personNode.Attribute("id").Value.AsInteger()))
{
var importPerson = F1Person.Translate(personNode, familyMembers, personAttributes, textInfo);
if (importPerson != null)
{
ImportPackage.WriteToPackage(importPerson);
}
// save person image
var personId = personNode.Attribute("id").Value;
var imageURI = personNode.Attribute("imageURI")?.Value;
if (imageURI.IsNotNullOrWhitespace())
{
// building the URI manually since the imageURI doesn't return a valid image
_request = new RestRequest(API_INDIVIDUAL + "/" + personId + "/Images", Method.GET);
var image = _client.DownloadData(_request);
ApiCounter++;
var path = Path.Combine(ImportPackage.ImageDirectory, "Person_" + personId + ".jpg");
File.WriteAllBytes(path, image);
}
personIds.Add(personNode.Attribute("id").Value.AsInteger());
}
}
}
if (additionalPages <= 0 && returnCount <= 0)
{
moreIndividualsExist = false;
}
else
{
currentPage++;
}
}
}
else
{
moreIndividualsExist = false;
}
// developer safety blanket (prevents eating all the api calls for the day)
if (loopCounter > loopThreshold)
{
break;
}
loopCounter++;
}
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
}
}
// 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>
/// 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();
}
/// <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;
}
}