private IQueryable <NoteType> GetUnorderedNoteTypes(int?entityTypeId, RockContext rockContext = null)
{
rockContext = rockContext ?? new RockContext();
var queryable = new NoteTypeService(rockContext).Queryable();
if (entityTypeId.HasValue)
{
queryable = queryable.Where(t => t.EntityTypeId == entityTypeId.Value);
}
return(queryable);
}
/// <summary>
/// Gets the note type query.
/// </summary>
/// <param name="entityTypeId">The entity type identifier.</param>
/// <param name="rockContext">The rock context.</param>
/// <returns></returns>
private IQueryable <NoteType> GetNoteTypeQuery(int?entityTypeId, RockContext rockContext)
{
var noteTypeQuery = new NoteTypeService(rockContext).Queryable();
if (entityTypeId.HasValue)
{
noteTypeQuery = noteTypeQuery.Where(t => t.EntityTypeId == entityTypeId.Value);
}
var sortProperty = gNoteTypes.SortProperty;
if (gNoteTypes.AllowSorting && sortProperty != null)
{
noteTypeQuery = noteTypeQuery.Sort(sortProperty);
}
else
{
noteTypeQuery = noteTypeQuery.OrderBy(a => a.EntityType.Name).ThenBy(a => a.Order).ThenBy(a => a.Name);
}
return(noteTypeQuery);
}
/// <summary>
/// Maps the notes.
/// </summary>
/// <param name="tableData">The table data.</param>
public void MapNotes( IQueryable<Row> tableData )
{
var lookupContext = new RockContext();
var categoryService = new CategoryService( lookupContext );
var personService = new PersonService( lookupContext );
var noteTypes = new NoteTypeService( lookupContext ).Queryable().AsNoTracking().ToList();
var personalNoteType = noteTypes.FirstOrDefault( nt => nt.Guid == new Guid( Rock.SystemGuid.NoteType.PERSON_TIMELINE_NOTE ) );
var importedUsers = new UserLoginService( lookupContext ).Queryable().AsNoTracking()
.Where( u => u.ForeignId != null )
.ToDictionary( t => t.ForeignId, t => t.PersonId );
var noteList = new List<Note>();
int completed = 0;
int totalRows = tableData.Count();
int percentage = ( totalRows - 1 ) / 100 + 1;
ReportProgress( 0, string.Format( "Verifying note import ({0:N0} found).", totalRows ) );
foreach ( var row in tableData.Where( r => r != null ) )
{
string text = row["Note_Text"] as string;
int? individualId = row["Individual_ID"] as int?;
int? householdId = row["Household_ID"] as int?;
var noteTypeActive = row["NoteTypeActive"] as Boolean?;
bool noteArchived = false;
if ( row.Columns.FirstOrDefault( v => v.Name.Equals( "IsInactive" ) ) != null )
{
/* =====================================================================
* the NoteArchived column *should* work, but OrcaMDF won't read it...
* instead check for a manually added column: IsInactive int null
* var noteActive = row["NoteArchived"] as Boolean?;
* if ( noteActive == null ) throw new NullReferenceException();
/* ===================================================================== */
var rowInactiveValue = row["IsInactive"] as int?;
noteArchived = rowInactiveValue.Equals( 1 );
}
var personKeys = GetPersonKeys( individualId, householdId );
if ( personKeys != null && !string.IsNullOrWhiteSpace( text ) && noteTypeActive == true && !noteArchived )
{
DateTime? dateCreated = row["NoteCreated"] as DateTime?;
string noteType = row["Note_Type_Name"] as string;
var note = new Note();
note.CreatedDateTime = dateCreated;
note.EntityId = personKeys.PersonId;
// These replace methods don't like being chained together
text = Regex.Replace( text, @"\t|\ ", " " );
text = text.Replace( "-", "-" );
text = text.Replace( "<", "<" );
text = text.Replace( ">", ">" );
text = text.Replace( "&", "&" );
text = text.Replace( """, @"""" );
text = text.Replace( "
", string.Empty );
note.Text = text.Trim();
int? userId = row["NoteCreatedByUserID"] as int?;
if ( userId != null && importedUsers.ContainsKey( userId ) )
{
var userKeys = ImportedPeople.FirstOrDefault( p => p.PersonId == (int)importedUsers[userId] );
if ( userKeys != null )
{
note.CreatedByPersonAliasId = userKeys.PersonAliasId;
}
}
int? matchingNoteTypeId = null;
if ( !noteType.StartsWith( "General", StringComparison.InvariantCultureIgnoreCase ) )
{
matchingNoteTypeId = noteTypes.Where( nt => nt.Name == noteType ).Select( i => (int?)i.Id ).FirstOrDefault();
}
else
{
matchingNoteTypeId = personalNoteType.Id;
}
if ( matchingNoteTypeId != null )
{
note.NoteTypeId = (int)matchingNoteTypeId;
}
else
{
// create the note type
var newNoteType = new NoteType();
newNoteType.EntityTypeId = personalNoteType.EntityTypeId;
newNoteType.EntityTypeQualifierColumn = string.Empty;
newNoteType.EntityTypeQualifierValue = string.Empty;
newNoteType.UserSelectable = true;
newNoteType.IsSystem = false;
newNoteType.Name = noteType;
newNoteType.Order = 0;
lookupContext.NoteTypes.Add( newNoteType );
lookupContext.SaveChanges( DisableAuditing );
noteTypes.Add( newNoteType );
note.NoteTypeId = newNoteType.Id;
}
noteList.Add( note );
completed++;
if ( completed % percentage < 1 )
{
int percentComplete = completed / percentage;
ReportProgress( percentComplete, string.Format( "{0:N0} notes imported ({1}% complete).", completed, percentComplete ) );
}
else if ( completed % ReportingNumber < 1 )
{
SaveNotes( noteList );
ReportPartialProgress();
noteList.Clear();
}
}
}
if ( noteList.Any() )
{
SaveNotes( noteList );
}
ReportProgress( 100, string.Format( "Finished note import: {0:N0} notes imported.", completed ) );
}
/// <summary>
/// Maps the notes.
/// </summary>
/// <param name="tableData">The table data.</param>
private void MapNotes(IQueryable <Row> tableData)
{
var lookupContext = new RockContext();
var categoryService = new CategoryService(lookupContext);
var personService = new PersonService(lookupContext);
var noteTypes = new NoteTypeService(lookupContext).Queryable().ToList();
int noteTimelineTypeId = noteTypes.Where(nt => nt.Guid == new Guid("7E53487C-D650-4D85-97E2-350EB8332763"))
.Select(nt => nt.Id).FirstOrDefault();
var importedUsers = new UserLoginService(lookupContext).Queryable()
.Where(u => u.ForeignId != null)
.Select(u => new { UserId = u.ForeignId, PersonId = u.PersonId })
.ToDictionary(t => t.UserId.AsType <int?>(), t => t.PersonId);
var noteList = new List <Note>();
int completed = 0;
int totalRows = tableData.Count();
int percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying note import ({0:N0} found).", totalRows));
foreach (var row in tableData)
{
string text = row["Note_Text"] as string;
int? individualId = row["Individual_ID"] as int?;
int? householdId = row["Household_ID"] as int?;
int? personId = GetPersonAliasId(individualId, householdId);
if (personId != null && !string.IsNullOrWhiteSpace(text))
{
int?userId = row["NoteCreatedByUserID"] as int?;
if (userId != null && importedUsers.ContainsKey(userId))
{
DateTime?dateCreated = row["NoteCreated"] as DateTime?;
string noteType = row["Note_Type_Name"] as string;
var note = new Note();
note.CreatedByPersonAliasId = (int)importedUsers[userId];
note.CreatedDateTime = dateCreated;
note.EntityId = personId;
note.Text = text;
if (!string.IsNullOrWhiteSpace(noteType))
{
int?noteTypeId = noteTypes.Where(nt => nt.Name == noteType).Select(i => (int?)i.Id).FirstOrDefault();
note.NoteTypeId = noteTypeId ?? noteTimelineTypeId;
}
else
{
note.NoteTypeId = noteTimelineTypeId;
}
/*var rockContext = new RockContext();
* rockContext.WrapTransaction( () =>
* {
* rockContext.Configuration.AutoDetectChangesEnabled = false;
* rockContext.Notes.Add( note );
* rockContext.SaveChanges( DisableAudit );
* } );*/
noteList.Add(note);
completed++;
if (completed % percentage < 1)
{
int percentComplete = completed / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} notes imported ({1}% complete).", completed, percentComplete));
}
else if (completed % ReportingNumber < 1)
{
SaveNotes(noteList);
ReportPartialProgress();
noteList.Clear();
}
}
}
}
if (noteList.Any())
{
SaveNotes(noteList);
}
ReportProgress(100, string.Format("Finished note import: {0:N0} notes imported.", completed));
}
/// <summary>
/// Maps the notes.
/// </summary>
/// <param name="tableData">The table data.</param>
private void MapNotes( IQueryable<Row> tableData )
{
var lookupContext = new RockContext();
var categoryService = new CategoryService( lookupContext );
var personService = new PersonService( lookupContext );
var noteTypes = new NoteTypeService( lookupContext ).Queryable().ToList();
int noteTimelineTypeId = noteTypes.Where( nt => nt.Guid == new Guid( "7E53487C-D650-4D85-97E2-350EB8332763" ) )
.Select( nt => nt.Id ).FirstOrDefault();
var importedUsers = new UserLoginService( lookupContext ).Queryable()
.Where( u => u.ForeignId != null )
.Select( u => new { UserId = u.ForeignId, PersonId = u.PersonId } )
.ToDictionary( t => t.UserId.AsType<int?>(), t => t.PersonId );
var noteList = new List<Note>();
int completed = 0;
int totalRows = tableData.Count();
int percentage = ( totalRows - 1 ) / 100 + 1;
ReportProgress( 0, string.Format( "Verifying note import ({0:N0} found).", totalRows ) );
foreach ( var row in tableData )
{
string text = row["Note_Text"] as string;
int? individualId = row["Individual_ID"] as int?;
int? householdId = row["Household_ID"] as int?;
int? personId = GetPersonAliasId( individualId, householdId );
if ( personId != null && !string.IsNullOrWhiteSpace( text ) )
{
int? userId = row["NoteCreatedByUserID"] as int?;
if ( userId != null && importedUsers.ContainsKey( userId ) )
{
DateTime? dateCreated = row["NoteCreated"] as DateTime?;
string noteType = row["Note_Type_Name"] as string;
var note = new Note();
note.CreatedByPersonAliasId = (int)importedUsers[userId];
note.CreatedDateTime = dateCreated;
note.EntityId = personId;
note.Text = text;
if ( !string.IsNullOrWhiteSpace( noteType ) )
{
int? noteTypeId = noteTypes.Where( nt => nt.Name == noteType ).Select( i => (int?)i.Id ).FirstOrDefault();
note.NoteTypeId = noteTypeId ?? noteTimelineTypeId;
}
else
{
note.NoteTypeId = noteTimelineTypeId;
}
/*var rockContext = new RockContext();
rockContext.WrapTransaction( () =>
{
rockContext.Configuration.AutoDetectChangesEnabled = false;
rockContext.Notes.Add( note );
rockContext.SaveChanges( DisableAudit );
} );*/
noteList.Add( note );
completed++;
if ( completed % percentage < 1 )
{
int percentComplete = completed / percentage;
ReportProgress( percentComplete, string.Format( "{0:N0} notes imported ({1}% complete).", completed, percentComplete ) );
}
else if ( completed % ReportingNumber < 1 )
{
SaveNotes( noteList );
ReportPartialProgress();
noteList.Clear();
}
}
}
}
if ( noteList.Any() )
{
SaveNotes( noteList );
}
ReportProgress( 100, string.Format( "Finished note import: {0:N0} notes imported.", completed ) );
}
/// <summary>
/// Maps the individual contact notes where IndividualId isn't null and Contact Note isn't null
/// </summary>
/// <param name="tableData">The table data.</param>
private void MapIndividualContactNotes( IQueryable<Row> tableData )
{
var lookupContext = new RockContext();
var categoryService = new CategoryService( lookupContext ); // not sure this is being used.
var personService = new PersonService( lookupContext );
var noteTypes = new NoteTypeService( lookupContext ).Queryable().ToList();
int noteTimelineTypeId = noteTypes.Where( nt => nt.Guid == new Guid( "7E53487C-D650-4D85-97E2-350EB8332763" ) )
.Select( nt => nt.Id ).FirstOrDefault();
var importedUsers = new UserLoginService( lookupContext ).Queryable()
.Where( u => u.ForeignId != null )
.Select( u => new { UserId = u.ForeignId, PersonId = u.PersonId } )
.ToDictionary( t => t.UserId.AsType<int?>(), t => t.PersonId );
var noteList = new List<Note>();
int completed = 0;
int totalRows = tableData.Count();
int percentage = ( totalRows - 1 ) / 100 + 1;
ReportProgress( 0, string.Format( "Verifying individual contact note import ({0:N0} found).", totalRows ) );
foreach ( var row in tableData )
{
string text = row["IndividualContactNote"] as string;
string confidentialNote = row["ConfidentialNote"] as string; //Check if they want me to convert confidential notes.
int? individualId = row["IndividualID"] as int?;
//int? householdId = row["Household_ID"] as int?;
int? personId = GetPersonAliasId( individualId, null );
if ( personId != null && !string.IsNullOrWhiteSpace( text ) ) //As long as individual Id is not null and Note isn't null or empty string
{
int? userId = row["UserID"] as int?;
if ( userId != null && importedUsers.ContainsKey( userId ) ) //As long as UserId is not null
{
DateTime? dateCreated = row["IndividualContactDatetime"] as DateTime?;
string noteType = row["ContactMethodName"] as string;
int? individualContactId = row["IndividualContactID"] as int?;
var note = new Note();
note.CreatedByPersonAliasId = (int)importedUsers[userId];
note.CreatedDateTime = dateCreated;
note.EntityId = personId;
note.Text = text;
note.ForeignId = individualContactId.ToString(); //will use this for checking existing notes.
if ( !string.IsNullOrWhiteSpace( noteType ) )
{
int? noteTypeId = noteTypes.Where( nt => nt.Name == noteType ).Select( i => (int?)i.Id ).FirstOrDefault();
note.NoteTypeId = noteTypeId ?? noteTimelineTypeId; //since Contact Type in F1 is not the same as the DT in Rock, it will use the default type.
}
else
{
note.NoteTypeId = noteTimelineTypeId;
}
//noteList.Add( note );
//saving individually because it keeps giving me "Can't save with duplicate GUID", even though I'm not specifying the GUID.
var rockContext = new RockContext();
rockContext.WrapTransaction( () =>
{
rockContext.Configuration.AutoDetectChangesEnabled = false;
rockContext.Notes.Add( note );
rockContext.SaveChanges( DisableAudit );
} );
completed++;
if ( completed % percentage < 1 )
{
int percentComplete = completed / percentage;
ReportProgress( percentComplete, string.Format( "{0:N0} individual contact notes imported ({1}% complete).", completed, percentComplete ) );
}
else if ( completed % ReportingNumber < 1 )
{
ReportPartialProgress();
}
}
}
}
if ( noteList.Any() )
{
var rockContext = new RockContext();
rockContext.WrapTransaction( () =>
{
rockContext.Configuration.AutoDetectChangesEnabled = false;
rockContext.Notes.AddRange( noteList );
rockContext.SaveChanges( DisableAudit );
} );
}
ReportProgress( 100, string.Format( "Finished note import: {0:N0} notes imported.", completed ) );
}
/// <summary>
/// Maps the notes.
/// </summary>
/// <param name="tableData">The table data.</param>
public void MapNotes(IQueryable <Row> tableData)
{
var lookupContext = new RockContext();
var categoryService = new CategoryService(lookupContext);
var personService = new PersonService(lookupContext);
var noteTypes = new NoteTypeService(lookupContext).Queryable().AsNoTracking().ToList();
var personalNoteType = noteTypes.FirstOrDefault(nt => nt.Guid == new Guid(Rock.SystemGuid.NoteType.PERSON_TIMELINE_NOTE));
var importedUsers = new UserLoginService(lookupContext).Queryable().AsNoTracking()
.Where(u => u.ForeignId != null)
.ToDictionary(t => t.ForeignId, t => t.PersonId);
var noteList = new List <Note>();
int completed = 0;
int totalRows = tableData.Count();
int percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying note import ({0:N0} found).", totalRows));
foreach (var row in tableData.Where(r => r != null))
{
string text = row["Note_Text"] as string;
int? individualId = row["Individual_ID"] as int?;
int? householdId = row["Household_ID"] as int?;
var noteTypeActive = row["NoteTypeActive"] as Boolean?;
bool noteArchived = false;
if (row.Columns.FirstOrDefault(v => v.Name.Equals("IsInactive")) != null)
{
/* =====================================================================
* the NoteArchived column *should* work, but OrcaMDF won't read it...
* instead check for a manually added column: IsInactive int null
* var noteActive = row["NoteArchived"] as Boolean?;
* if ( noteActive == null ) throw new NullReferenceException();
* /* ===================================================================== */
var rowInactiveValue = row["IsInactive"] as int?;
noteArchived = rowInactiveValue.Equals(1);
}
var personKeys = GetPersonKeys(individualId, householdId);
if (personKeys != null && !string.IsNullOrWhiteSpace(text) && noteTypeActive == true && !noteArchived)
{
DateTime?dateCreated = row["NoteCreated"] as DateTime?;
string noteType = row["Note_Type_Name"] as string;
var note = new Note();
note.CreatedDateTime = dateCreated;
note.EntityId = personKeys.PersonId;
// These replace methods don't like being chained together
text = Regex.Replace(text, @"\t|\ ", " ");
text = text.Replace("-", "-");
text = text.Replace("<", "<");
text = text.Replace(">", ">");
text = text.Replace("&", "&");
text = text.Replace(""", @"""");
text = text.Replace("
", string.Empty);
note.Text = text.Trim();
int?userId = row["NoteCreatedByUserID"] as int?;
if (userId != null && importedUsers.ContainsKey(userId))
{
var userKeys = ImportedPeople.FirstOrDefault(p => p.PersonId == (int)importedUsers[userId]);
if (userKeys != null)
{
note.CreatedByPersonAliasId = userKeys.PersonAliasId;
}
}
int?matchingNoteTypeId = null;
if (!noteType.StartsWith("General", StringComparison.InvariantCultureIgnoreCase))
{
matchingNoteTypeId = noteTypes.Where(nt => nt.Name == noteType).Select(i => (int?)i.Id).FirstOrDefault();
}
else
{
matchingNoteTypeId = personalNoteType.Id;
}
if (matchingNoteTypeId != null)
{
note.NoteTypeId = (int)matchingNoteTypeId;
}
else
{
// create the note type
var newNoteType = new NoteType();
newNoteType.EntityTypeId = personalNoteType.EntityTypeId;
newNoteType.EntityTypeQualifierColumn = string.Empty;
newNoteType.EntityTypeQualifierValue = string.Empty;
newNoteType.UserSelectable = true;
newNoteType.IsSystem = false;
newNoteType.Name = noteType;
newNoteType.Order = 0;
lookupContext.NoteTypes.Add(newNoteType);
lookupContext.SaveChanges(DisableAuditing);
noteTypes.Add(newNoteType);
note.NoteTypeId = newNoteType.Id;
}
noteList.Add(note);
completed++;
if (completed % percentage < 1)
{
int percentComplete = completed / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} notes imported ({1}% complete).", completed, percentComplete));
}
else if (completed % ReportingNumber < 1)
{
SaveNotes(noteList);
ReportPartialProgress();
noteList.Clear();
}
}
}
if (noteList.Any())
{
SaveNotes(noteList);
}
ReportProgress(100, string.Format("Finished note import: {0:N0} notes imported.", completed));
}
/// <summary>
/// Maps the individual contact notes where IndividualId isn't null and Contact Note isn't null
/// </summary>
/// <param name="tableData">The table data.</param>
private void MapIndividualContactNotes(IQueryable <Row> tableData)
{
var lookupContext = new RockContext();
var categoryService = new CategoryService(lookupContext); // not sure this is being used.
var personService = new PersonService(lookupContext);
var noteTypes = new NoteTypeService(lookupContext).Queryable().ToList();
int noteTimelineTypeId = noteTypes.Where(nt => nt.Guid == new Guid("7E53487C-D650-4D85-97E2-350EB8332763"))
.Select(nt => nt.Id).FirstOrDefault();
var importedUsers = new UserLoginService(lookupContext).Queryable()
.Where(u => u.ForeignId != null)
.Select(u => new { UserId = u.ForeignId, PersonId = u.PersonId })
.ToDictionary(t => t.UserId.AsType <int?>(), t => t.PersonId);
var noteList = new List <Note>();
int completed = 0;
int totalRows = tableData.Count();
int percentage = (totalRows - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying individual contact note import ({0:N0} found).", totalRows));
foreach (var row in tableData)
{
string text = row["IndividualContactNote"] as string;
string confidentialNote = row["ConfidentialNote"] as string; //Check if they want me to convert confidential notes.
int? individualId = row["IndividualID"] as int?;
//int? householdId = row["Household_ID"] as int?;
int?personId = GetPersonAliasId(individualId, null);
if (personId != null && !string.IsNullOrWhiteSpace(text)) //As long as individual Id is not null and Note isn't null or empty string
{
int?userId = row["UserID"] as int?;
if (userId != null && importedUsers.ContainsKey(userId)) //As long as UserId is not null
{
DateTime?dateCreated = row["IndividualContactDatetime"] as DateTime?;
string noteType = row["ContactMethodName"] as string;
int? individualContactId = row["IndividualContactID"] as int?;
var note = new Note();
note.CreatedByPersonAliasId = (int)importedUsers[userId];
note.CreatedDateTime = dateCreated;
note.EntityId = personId;
note.Text = text;
note.ForeignId = individualContactId.ToString(); //will use this for checking existing notes.
if (!string.IsNullOrWhiteSpace(noteType))
{
int?noteTypeId = noteTypes.Where(nt => nt.Name == noteType).Select(i => (int?)i.Id).FirstOrDefault();
note.NoteTypeId = noteTypeId ?? noteTimelineTypeId; //since Contact Type in F1 is not the same as the DT in Rock, it will use the default type.
}
else
{
note.NoteTypeId = noteTimelineTypeId;
}
//noteList.Add( note );
//saving individually because it keeps giving me "Can't save with duplicate GUID", even though I'm not specifying the GUID.
var rockContext = new RockContext();
rockContext.WrapTransaction(() =>
{
rockContext.Configuration.AutoDetectChangesEnabled = false;
rockContext.Notes.Add(note);
rockContext.SaveChanges(DisableAudit);
});
completed++;
if (completed % percentage < 1)
{
int percentComplete = completed / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} individual contact notes imported ({1}% complete).", completed, percentComplete));
}
else if (completed % ReportingNumber < 1)
{
ReportPartialProgress();
}
}
}
}
if (noteList.Any())
{
var rockContext = new RockContext();
rockContext.WrapTransaction(() =>
{
rockContext.Configuration.AutoDetectChangesEnabled = false;
rockContext.Notes.AddRange(noteList);
rockContext.SaveChanges(DisableAudit);
});
}
ReportProgress(100, string.Format("Finished note import: {0:N0} notes imported.", completed));
}
private IQueryable<NoteType> GetUnorderedNoteTypes( int? entityTypeId, RockContext rockContext = null )
{
rockContext = rockContext ?? new RockContext();
var queryable = new NoteTypeService( rockContext ).Queryable();
if ( entityTypeId.HasValue )
{
queryable = queryable.Where( t => t.EntityTypeId == entityTypeId.Value );
}
return queryable;
}
