/// <summary>
/// Loads the database for this instance.
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public override bool LoadSchema(string fileName)
{
if (DataNodes == null)
{
DataNodes = new List <DataNode>();
}
var folderItem = new DataNode();
var previewFolder = new ZipArchive(new FileStream(fileName, FileMode.Open));
folderItem.Name = Path.GetFileNameWithoutExtension(fileName);
folderItem.Path = fileName;
foreach (var document in previewFolder.Entries.Take(50))
{
if (document != null)
{
var entryItem = new DataNode();
entryItem.Name = document.FullName;
string content = new StreamReader(document.Open()).ReadToEnd();
entryItem.Value = Encoding.UTF8.GetBytes(content) ?? null;
entryItem.NodeType = typeof(byte[]);
entryItem.Parent.Add(folderItem);
folderItem.Children.Add(entryItem);
}
}
previewFolder.Dispose();
DataNodes.Add(folderItem);
return(DataNodes.Count > 0 ? true : false);
}
/// <summary>
/// Loads the database for this instance.
/// may be called multiple times, if uploading multiple CSV files.
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public override bool LoadSchema(string fileName)
{
//enforce that the filename must be a known configuration.
if (!FileIsKnown(fileName))
{
return(false);
}
using (var dbPreview = new CsvReader(new StreamReader(fileName), true))
{
if (CsvDataToImport == null)
{
CsvDataToImport = new List <CSVInstance>();
DataNodes = new List <DataNode>();
}
//a local tableNode object, which will track this one of multiple CSV files that may be imported
var tableNodes = new List <DataNode>();
CsvDataToImport.Add(new CSVInstance(fileName)
{
TableNodes = tableNodes, RecordType = GetRecordTypeFromFilename(fileName)
});
var currentIndex = 0;
var tableItem = new DataNode
{
Name = Path.GetFileNameWithoutExtension(fileName)
};
var firstRow = dbPreview.ElementAtOrDefault(0);
if (firstRow != null)
{
foreach (var columnName in dbPreview.GetFieldHeaders())
{
var childItem = new DataNode
{
Name = columnName,
NodeType = typeof(string),
Value = firstRow[currentIndex] ?? string.Empty
};
childItem.Parent.Add(tableItem);
tableItem.Children.Add(childItem);
currentIndex++;
}
tableNodes.Add(tableItem);
DataNodes.Add(tableItem); //this is to maintain compatibility with the base Excavator object.
}
return(tableNodes.Count() > 0 ? true : false);
}
}
/// <summary>
/// Transforms the data from the dataset.
/// </summary>
public override int TransformData(Dictionary <string, string> settings)
{
var importUser = settings["ImportUser"];
int totalCount = 0;
ReportProgress(0, "Starting health checks...");
var rockContext = new RockContext();
var personService = new PersonService(rockContext);
var importPerson = personService.GetByFullName(importUser, allowFirstNameOnly: true).FirstOrDefault();
if (importPerson == null)
{
importPerson = personService.Queryable().AsNoTracking().FirstOrDefault();
}
ImportPersonAliasId = importPerson.PrimaryAliasId;
ReportProgress(0, "Checking for existing attributes...");
LoadRockData(rockContext);
// only import things that the user checked
foreach (var selectedFile in DataNodes.Where(n => n.Checked != false))
{
var selectedFileType = FileTypes.FirstOrDefault(t => selectedFile.Name.RemoveWhitespace().StartsWith(t.Name.RemoveWhitespace(), StringComparison.InvariantCultureIgnoreCase));
if (selectedFileType == null)
{
selectedFileType = FileTypes.FirstOrDefault(f => f.Name == "Default");
}
var archiveFolder = new ZipArchive(new FileStream(selectedFile.Path, FileMode.Open));
IBinaryFile worker = IMapAdapterFactory.GetAdapter(selectedFile.Name);
if (worker != null && selectedFileType != null)
{
ReportProgress(0, string.Format("Starting {0} file import", selectedFileType.Name));
var selectedProvider = StorageProviders.FirstOrDefault(p => selectedFileType.StorageEntityTypeId == p.EntityType.Id);
worker.Map(archiveFolder, selectedFileType, selectedProvider);
totalCount += archiveFolder.Entries.Count;
}
else
{
LogException("Binary File", string.Format("Unknown File: {0} does not start with the name of a known data map.", selectedFile.Name));
}
}
// Report the final imported count
ReportProgress(100, string.Format("Completed import: {0:N0} records imported.", totalCount));
return(totalCount);
}
public void AddDataNode(int dataNodeIndex, int replica)
{
if (dataNodeIndex < 0)
{
throw new ArgumentException("data node index is null");
}
if (replica == RouteResultsetNode.DefaultReplicaIndex || replica < 0)
{
replica = -1;
}
if (DataNodes == null)
{
DataNodes = new List <Pair <int, int> >();
}
DataNodes.Add(new Pair <int, int>(dataNodeIndex, replica));
}
/// <summary>
/// Transforms the data from the dataset.
/// </summary>
/// <param name="settings">The settings.</param>
/// <returns></returns>
public override int TransformData(Dictionary <string, string> settings)
{
var importUser = settings["ImportUser"];
var totalCount = 0;
ReportProgress(0, "Starting health checks...");
var rockContext = new RockContext();
var personService = new PersonService(rockContext);
var importPerson = personService.GetByFullName(importUser, allowFirstNameOnly: true).FirstOrDefault();
if (importPerson == null)
{
importPerson = personService.Queryable().AsNoTracking().FirstOrDefault();
}
ImportPersonAliasId = importPerson.PrimaryAliasId;
ReportProgress(0, "Checking for existing attributes...");
LoadRockData(rockContext);
// only import things that the user checked
foreach (var selectedFile in DataNodes.Where(n => n.Checked != false))
{
var specificFileType = FileTypes.FirstOrDefault(t => selectedFile.Name.RemoveWhitespace().StartsWith(t.Name.RemoveWhitespace()));
var archiveFolder = new ZipArchive(new FileStream(selectedFile.Path, FileMode.Open));
var worker = IMapAdapterFactory.GetAdapter(selectedFile.Name.RemoveWhitespace());
if (worker != null)
{
worker.ProgressUpdated += this.RenderProgress;
ReportProgress(0, $"Starting {specificFileType} import...");
totalCount += worker.Map(archiveFolder, specificFileType);
ReportProgress(0, $"Finished {selectedFile.Name} import.");
}
else
{
LogException("Binary File", string.Format("Unknown File: {0} does not start with the name of a known data map.", selectedFile.Name));
}
}
// Report the final imported count
ReportProgress(100, string.Format("Completed import: {0:N0} records imported.", totalCount));
return(totalCount);
}
/// <summary>
/// Transforms the data from the dataset.
/// </summary>
/// <returns></returns>
public override int TransformData(Dictionary <string, string> settings)
{
var importUser = settings["ImportUser"];
ReportProgress(0, "Starting health checks...");
var rockContext = new RockContext();
var personService = new PersonService(rockContext);
var importPerson = personService.GetByFullName(importUser, allowFirstNameOnly: true).FirstOrDefault();
if (importPerson == null)
{
importPerson = personService.Queryable().AsNoTracking().FirstOrDefault();
}
ImportPersonAliasId = importPerson.PrimaryAliasId;
var tableList = DataNodes.Where(n => n.Checked != false).ToList();
ReportProgress(0, "Checking for existing attributes...");
LoadExistingRockData();
ReportProgress(0, "Checking for existing people...");
bool isValidImport = ImportedPeople.Any() || tableList.Any(n => n.Name.Equals("Individual_Household"));
var tableDependencies = new List <string>();
tableDependencies.Add("Batch"); // needed to attribute contributions properly
tableDependencies.Add("Users"); // needed for notes, user logins
tableDependencies.Add("Company"); // needed to attribute any business items
tableDependencies.Add("Individual_Household"); // needed for just about everything
if (isValidImport)
{
ReportProgress(0, "Checking for table dependencies...");
// Order tables so non-dependents are imported first
if (tableList.Any(n => tableDependencies.Contains(n.Name)))
{
tableList = tableList.OrderByDescending(n => tableDependencies.IndexOf(n.Name)).ToList();
}
ReportProgress(0, "Starting data import...");
var scanner = new DataScanner(Database);
foreach (var table in tableList)
{
switch (table.Name)
{
case "Account":
MapBankAccount(scanner.ScanTable(table.Name).AsQueryable());
break;
case "Batch":
MapBatch(scanner.ScanTable(table.Name).AsQueryable());
break;
case "Communication":
MapCommunication(scanner.ScanTable(table.Name).AsQueryable());
break;
case "Company":
MapCompany(scanner.ScanTable(table.Name).AsQueryable());
break;
case "Contribution":
MapContribution(scanner.ScanTable(table.Name).AsQueryable());
break;
case "Household_Address":
MapFamilyAddress(scanner.ScanTable(table.Name).AsQueryable());
break;
case "Individual_Household":
MapPerson(scanner.ScanTable(table.Name).AsQueryable());
break;
case "Notes":
MapNotes(scanner.ScanTable(table.Name).AsQueryable());
break;
case "Pledge":
MapPledge(scanner.ScanTable(table.Name).AsQueryable());
break;
case "Users":
MapUsers(scanner.ScanTable(table.Name).AsQueryable());
break;
default:
break;
}
}
ReportProgress(100, "Import completed. ");
}
else
{
ReportProgress(0, "No imported people exist. Please include the Individual_Household table during the import.");
}
return(100); // return total number of rows imported?
}
/// <summary>
/// Transforms the data from the dataset.
/// </summary>
public override int TransformData(Dictionary <string, string> settings)
{
var importUser = settings["ImportUser"];
// Report progress to the main thread so it can update the UI
ReportProgress(0, "Starting import...");
// Instantiate the object model service
var rockContext = new RockContext();
// Connects to the source database (already loaded in memory by the UI)
var scanner = new DataScanner(Database);
// List of tables the user would like to import
var tableList = DataNodes.Where(n => n.Checked != false).Select(n => n.Name).ToList();
// Supplies a lazy-loaded database queryable
var tableData = scanner.ScanTable("TableName").AsQueryable();
// Hold a count of how many records have been imported
int completed = 0;
// Pick a method to save data to Rock: #1 (simple) or #2 (fast)
// Option #1. Standard way to put data in Rock
foreach (var dataRow in tableData)
{
// Get a value from the row. This has to be a nullable type.
string columnValue = dataRow["ColumnName"] as string;
// Create a Rock model and assign data to it
Person person = new Person();
person.LastName = columnValue;
rockContext.WrapTransaction(() =>
{
// If it's a new model, add it to the database first
rockContext.People.Add(person);
// Save the data to the database
rockContext.SaveChanges(DisableAuditing);
});
completed++;
}
// end option #1
// Option #2. More efficient way to import large data sets
var newPersonList = new List <Person>();
foreach (var dataRow in tableData)
{
// Get a value from the row. This has to be a nullable type.
string columnValue = dataRow["ColumnName"] as string;
// Create a Rock model and assign data to it
Person person = new Person();
newPersonList.Add(new Person());
completed++;
// Save 100 people at a time
if (completed % ReportingNumber < 1)
{
SaveModel(newPersonList);
}
}
// Outside foreach, save any that haven't been saved yet
if (newPersonList.Any())
{
SaveModel(newPersonList);
}
// end option #2
// Report the final imported count
ReportProgress(100, string.Format("Completed import: {0:N0} records imported.", completed));
return(completed);
}
/// <summary>
/// Transforms the data from the dataset.
/// </summary>
/// <param name="settings">todo: describe settings parameter on TransformData</param>
/// <returns></returns>
public override int TransformData(Dictionary <string, string> settings)
{
var importUser = settings["ImportUser"];
ReportProgress(0, "Starting health checks...");
var scanner = new DataScanner(Database);
var rockContext = new RockContext();
var personService = new PersonService(rockContext);
var importPerson = personService.GetByFullName(importUser, allowFirstNameOnly: true).FirstOrDefault();
if (importPerson == null)
{
importPerson = personService.Queryable().AsNoTracking().FirstOrDefault();
}
ImportPersonAliasId = importPerson.PrimaryAliasId;
var tableList = DataNodes.Where(n => n.Checked != false).ToList();
ReportProgress(0, "Checking for existing attributes...");
LoadGlobalObjects(scanner);
ReportProgress(0, "Checking for existing people...");
var isValidImport = ImportedPeople.Any() || tableList.Any(n => n.Name.Equals("Individual_Household"));
var tableDependencies = new List <string>();
tableDependencies.Add("ContactFormData"); // needed for individual contact notes
tableDependencies.Add("Groups"); // needed for home group structure
tableDependencies.Add("RLC"); // needed for bottom-level group and location structure
tableDependencies.Add("Activity_Group"); // needed for mid-level group structure
tableDependencies.Add("ActivityMinistry"); // needed for top-level group structure
tableDependencies.Add("Batch"); // needed to attribute contributions properly
tableDependencies.Add("Users"); // needed for notes, user logins
tableDependencies.Add("Company"); // needed to attribute any business items
tableDependencies.Add("Individual_Household"); // needed for just about everything
if (isValidImport)
{
ReportProgress(0, "Checking for table dependencies...");
// Order tables so dependencies are imported first
if (tableList.Any(n => tableDependencies.Contains(n.Name)))
{
tableList = tableList.OrderByDescending(n => tableDependencies.IndexOf(n.Name)).ToList();
}
// get list of objects to grab their rowcounts
var objectNameIds = Database.Dmvs.Objects.Where(o => !o.IsMSShipped).ToDictionary(t => t.Name, t => t.ObjectID);
ReportProgress(0, "Starting data import...");
foreach (var table in tableList)
{
var totalRows = Database.Dmvs.Partitions.FirstOrDefault(p => p.ObjectID == objectNameIds[table.Name]).Rows;
switch (table.Name)
{
case "Account":
MapBankAccount(scanner.ScanTable(table.Name).AsQueryable(), totalRows);
break;
case "Batch":
MapBatch(scanner.ScanTable(table.Name).AsQueryable(), totalRows);
break;
case "Communication":
MapCommunication(scanner.ScanTable(table.Name).AsQueryable(), totalRows);
break;
case "Company":
MapCompany(scanner.ScanTable(table.Name).AsQueryable(), totalRows);
break;
case "ContactFormData":
MapContactFormData(scanner.ScanTable(table.Name).AsQueryable(), totalRows);
break;
case "Contribution":
MapContribution(scanner.ScanTable(table.Name).AsQueryable(), totalRows);
break;
case "Household_Address":
MapFamilyAddress(scanner.ScanTable(table.Name).AsQueryable(), totalRows);
break;
case "IndividualContactNotes":
MapIndividualContactNotes(scanner.ScanTable(table.Name).AsQueryable(), totalRows);
break;
case "Individual_Household":
MapPerson(scanner.ScanTable(table.Name).AsQueryable(), totalRows);
break;
case "Notes":
MapNotes(scanner.ScanTable(table.Name).AsQueryable(), totalRows);
break;
case "Pledge":
MapPledge(scanner.ScanTable(table.Name).AsQueryable(), totalRows);
break;
case "Users":
MapUsers(scanner.ScanTable(table.Name).AsQueryable(), totalRows);
break;
default:
break;
}
}
ReportProgress(100, "Import completed. ");
}
else
{
ReportProgress(0, "No imported people exist. Please include the Individual_Household table during the import.");
}
return(100); // return total number of rows imported?
}
/// <summary>
/// Loads Rock data that's used globally by the transform
/// </summary>
/// <param name="scanner">The scanner.</param>
private void LoadGlobalObjects(DataScanner scanner)
{
var lookupContext = new RockContext();
var attributeValueService = new AttributeValueService(lookupContext);
var attributeService = new AttributeService(lookupContext);
var visitInfoCategoryId = new CategoryService(lookupContext).GetByEntityTypeId(AttributeEntityTypeId)
.Where(c => c.Name == "Visit Information").Select(c => c.Id).FirstOrDefault();
// Look up and create attributes for F1 unique identifiers if they don't exist
var attributeKey = "F1HouseholdId";
var personAttributes = attributeService.GetByEntityTypeId(PersonEntityTypeId).AsNoTracking().ToList();
HouseholdIdAttribute = personAttributes.FirstOrDefault(a => a.Key.Equals(attributeKey, StringComparison.OrdinalIgnoreCase));
if (HouseholdIdAttribute == null)
{
HouseholdIdAttribute = AddEntityAttribute(lookupContext, PersonEntityTypeId, string.Empty, string.Empty, string.Format("{0} imported {1}", attributeKey, ImportDateTime),
"Visit Information", "F1 Household Id", attributeKey, IntegerFieldTypeId
);
}
attributeKey = "F1IndividualId";
IndividualIdAttribute = personAttributes.FirstOrDefault(a => a.Key.Equals(attributeKey, StringComparison.OrdinalIgnoreCase));
if (IndividualIdAttribute == null)
{
IndividualIdAttribute = AddEntityAttribute(lookupContext, PersonEntityTypeId, string.Empty, string.Empty, string.Format("{0} imported {1}", attributeKey, ImportDateTime),
"Visit Information", "F1 Individual Id", attributeKey, IntegerFieldTypeId
);
}
attributeKey = "SecondaryEmail";
SecondaryEmailAttribute = personAttributes.FirstOrDefault(a => a.Key.Equals(attributeKey, StringComparison.OrdinalIgnoreCase));
if (SecondaryEmailAttribute == null)
{
SecondaryEmailAttribute = AddEntityAttribute(lookupContext, PersonEntityTypeId, string.Empty, string.Empty, string.Format("{0} imported {1}", attributeKey, ImportDateTime),
"Visit Information", "Secondary Email", attributeKey, TextFieldTypeId
);
}
attributeKey = "InFellowshipLogin";
InFellowshipLoginAttribute = personAttributes.FirstOrDefault(a => a.Key.Equals(attributeKey, StringComparison.OrdinalIgnoreCase));
if (InFellowshipLoginAttribute == null)
{
InFellowshipLoginAttribute = AddEntityAttribute(lookupContext, PersonEntityTypeId, string.Empty, string.Empty, string.Format("{0} imported {1}", attributeKey, ImportDateTime),
"Visit Information", "InFellowship Login", attributeKey, TextFieldTypeId
);
}
var aliasIdList = new PersonAliasService(lookupContext).Queryable().AsNoTracking()
.Select(pa => new
{
PersonAliasId = pa.Id,
PersonId = pa.PersonId,
Gender = pa.Person.Gender,
ForeignId = pa.ForeignId,
FamilyRole = pa.Person.ReviewReasonNote
}).ToList();
var householdIdList = attributeValueService.GetByAttributeId(HouseholdIdAttribute.Id).AsNoTracking()
.Select(av => new
{
PersonId = ( int )av.EntityId,
HouseholdId = av.Value
}).ToList();
ImportedPeople = householdIdList.GroupJoin(aliasIdList,
household => household.PersonId,
aliases => aliases.PersonId,
(household, aliases) => new PersonKeys
{
PersonAliasId = aliases.Select(a => a.PersonAliasId).FirstOrDefault(),
PersonId = household.PersonId,
PersonForeignId = aliases.Select(a => a.ForeignId).FirstOrDefault(),
GroupForeignId = household.HouseholdId.AsType <int?>(),
PersonGender = aliases.Select(a => a.Gender).FirstOrDefault(),
FamilyRoleId = aliases.Select(a => a.FamilyRole.ConvertToEnum <FamilyRole>(0)).FirstOrDefault()
}
).ToList();
ImportedGroupTypes = new GroupTypeService(lookupContext).Queryable().AsNoTracking()
.Where(t => t.Id != FamilyGroupTypeId && t.ForeignKey != null)
.ToList();
ImportedGroups = new GroupService(lookupContext).Queryable().AsNoTracking()
.Where(g => g.GroupTypeId != FamilyGroupTypeId && g.ForeignKey != null)
.ToList();
ImportedSchedules = new ScheduleService(lookupContext).Queryable().AsNoTracking()
.Where(s => s.ForeignKey != null)
.ToList();
ImportedCheckinActivityGroups = new List <Group>();
// this is a lookup hack for when clients don't want to import groups
if (!ImportedGroups.Any() && !DataNodes.Where(n => n.Checked == true).Any(n => n.Name.Equals("ActivityMinistry")))
{
ImportedGroups.AddRange(
scanner.ScanTable("ActivityMinistry")
.Select(s => new Group
{
ForeignId = s["Activity_ID"] as int?,
Name = s["Activity_Name"] as string
}).ToList()
);
ImportedGroups.AddRange(
scanner.ScanTable("RLC")
.Select(s => new Group
{
ForeignId = s["RLC_ID"] as int?,
Name = s["Room_Desc"] as string
}).ToList()
);
}
ImportedBatches = new FinancialBatchService(lookupContext).Queryable().AsNoTracking()
.Where(b => b.ForeignId.HasValue)
.ToDictionary(t => ( int )t.ForeignId, t => ( int? )t.Id);
ServingTeamsParentGroup = lookupContext.Groups.AsNoTracking().AsQueryable().FirstOrDefault(g => g.Guid.ToString() == "31730962-4C7B-425B-BD73-4185331F37EF");
// get the portal users for lookups on notes
var userIdList = scanner.ScanTable("Users")
.Select(s => new
{
UserId = s["UserID"] as int?,
ForeignId = s["LinkedIndividualID"] as int?
}).ToList();
PortalUsers = userIdList.Join(aliasIdList,
users => users.ForeignId,
aliases => aliases.ForeignId,
(users, aliases) => new
{
UserId = users.UserId,
PersonAliasId = aliases.PersonAliasId
}).ToDictionary(t => ( int )t.UserId, t => t.PersonAliasId);
}
