public void BenevolenceRequestDateKeyJoinsCorrectly()
{
var expectedRecordCount = 15;
var year = 2015;
var rockContext = new RockContext();
var benevolenceRequestService = new BenevolenceRequestService(rockContext);
var minDateValue = TestDataHelper.GetAnalyticsSourceMinDateForYear(rockContext, year);
var maxDateValue = TestDataHelper.GetAnalyticsSourceMaxDateForYear(rockContext, year);
for (var i = 0; i < 15; i++)
{
var benevolenceRequest = BuildBenevolenceRequest(rockContext,
TestDataHelper.GetRandomDateInRange(minDateValue, maxDateValue));
benevolenceRequestService.Add(benevolenceRequest);
}
rockContext.SaveChanges();
var benevolenceRequests = benevolenceRequestService.
Queryable("AnalyticsSourceDate").
Where(i => i.ForeignKey == benevolenceRequestForeignKey).
Where(i => i.RequestSourceDate.CalendarYear == year);
Assert.AreEqual(expectedRecordCount, benevolenceRequests.Count());
}
/// <summary>
/// Creates a Linq Expression that can be applied to an IQueryable to filter the result set.
/// </summary>
/// <param name="entityType">The type of entity in the result set.</param>
/// <param name="serviceInstance">A service instance that can be queried to obtain the result set.</param>
/// <param name="parameterExpression">The input parameter that will be injected into the filter expression.</param>
/// <param name="selection">A formatted string representing the filter settings.</param>
/// <returns>
/// A Linq Expression that can be used to filter an IQueryable.
/// </returns>
/// <exception cref="System.Exception">Filter issue(s): + errorMessages.AsDelimited( ; )</exception>
public override Expression GetExpression(Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection)
{
var settings = new FilterSettings(selection);
var context = (RockContext)serviceInstance.Context;
// Get the Benevolence Request Data View.
var dataView = DataComponentSettingsHelper.GetDataViewForFilterComponent(settings.DataViewGuid, context);
// Evaluate the Data View that defines the Person's Benevolence Request.
var benevolenceRequestService = new BenevolenceRequestService(context);
var benevolenceRequestQuery = benevolenceRequestService.Queryable();
if (dataView != null)
{
benevolenceRequestQuery = DataComponentSettingsHelper.FilterByDataView(benevolenceRequestQuery, dataView, benevolenceRequestService);
}
var benevolenceRequestPersonsKey = benevolenceRequestQuery.Select(a => a.RequestedByPersonAliasId);
// Get all of the Person corresponding to the qualifying Benevolence Requests.
var qry = new PersonService(context).Queryable()
.Where(g => g.Aliases.Any(k => benevolenceRequestPersonsKey.Contains(k.Id)));
// Retrieve the Filter Expression.
var extractedFilterExpression = FilterExpressionExtractor.Extract <Model.Person>(qry, parameterExpression, "g");
return(extractedFilterExpression);
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
phSummary.Controls.Clear();
rFilter.Visible = true;
gList.Visible = true;
bool canAddEditDelete = IsUserAuthorized(Authorization.EDIT);
gList.Actions.ShowAdd = canAddEditDelete;
gList.IsDeleteEnabled = canAddEditDelete;
var rockContext = new RockContext();
var benevolenceRequestService = new BenevolenceRequestService(rockContext);
var benevolenceRequests = benevolenceRequestService
.Queryable("BenevolenceResults,RequestedByPersonAlias,RequestedByPersonAlias.Person,CaseWorkerPersonAlias,CaseWorkerPersonAlias.Person").AsNoTracking();
var hideGridColumns = GetAttributeValue(AttributeKey.HideColumnsAttributeKey)?.Split(',')?.Select(v => v.ToUpper());
var benevolenceTypeFilter = GetAttributeValue(AttributeKey.FilterBenevolenceTypesAttributeKey)
?.Split(',')
?.Where(v => v.IsNotNullOrWhiteSpace())
?.Select(v => new Guid(v));
// Filter by Start Date
DateTime?startDate = drpDate.LowerValue;
if (startDate != null)
{
benevolenceRequests = benevolenceRequests.Where(b => b.RequestDateTime >= startDate);
}
// Filter by End Date
DateTime?endDate = drpDate.UpperValue;
if (endDate != null)
{
benevolenceRequests = benevolenceRequests.Where(b => b.RequestDateTime <= endDate);
}
// Filter by Campus
if (cpCampus.SelectedCampusId.HasValue)
{
benevolenceRequests = benevolenceRequests.Where(b => b.CampusId == cpCampus.SelectedCampusId);
}
if (TargetPerson != null)
{
// show benevolence request for the target person and also for their family members
var qryFamilyMembers = TargetPerson.GetFamilyMembers(true, rockContext);
benevolenceRequests = benevolenceRequests.Where(a => a.RequestedByPersonAliasId.HasValue && qryFamilyMembers.Any(b => b.PersonId == a.RequestedByPersonAlias.PersonId));
}
else
{
// Filter by First Name
string firstName = tbFirstName.Text;
if (!string.IsNullOrWhiteSpace(firstName))
{
benevolenceRequests = benevolenceRequests.Where(b => b.FirstName.StartsWith(firstName));
}
// Filter by Last Name
string lastName = tbLastName.Text;
if (!string.IsNullOrWhiteSpace(lastName))
{
benevolenceRequests = benevolenceRequests.Where(b => b.LastName.StartsWith(lastName));
}
}
// Filter by Government Id
string governmentId = tbGovernmentId.Text;
if (!string.IsNullOrWhiteSpace(governmentId))
{
benevolenceRequests = benevolenceRequests.Where(b => b.GovernmentId.StartsWith(governmentId));
}
// Filter by Case Worker
int?caseWorkerPersonAliasId = ddlCaseWorker.SelectedItem.Value.AsIntegerOrNull();
if (caseWorkerPersonAliasId != null)
{
benevolenceRequests = benevolenceRequests.Where(b => b.CaseWorkerPersonAliasId == caseWorkerPersonAliasId);
}
// Filter by Result
int?resultTypeValueId = dvpResult.SelectedItem.Value.AsIntegerOrNull();
if (resultTypeValueId != null)
{
benevolenceRequests = benevolenceRequests.Where(b => b.BenevolenceResults.Where(r => r.ResultTypeValueId == resultTypeValueId).Count() > 0);
}
// Filter by Request Status
int?requestStatusValueId = dvpStatus.SelectedItem.Value.AsIntegerOrNull();
if (requestStatusValueId != null)
{
benevolenceRequests = benevolenceRequests.Where(b => b.RequestStatusValueId == requestStatusValueId);
}
// Filter by Benevolence Types
var benevolenceTypeIds = cblBenevolenceType.SelectedValues.Where(v => v.ToIntSafe() > 0).Select(v => v.ToIntSafe());
if (benevolenceTypeIds?.Count() > 0)
{
benevolenceRequests = benevolenceRequests.Where(b => benevolenceTypeIds.Contains(b.BenevolenceTypeId));
}
if (benevolenceTypeFilter?.Count() > 0)
{
benevolenceRequests = benevolenceRequests.Where(b => benevolenceTypeFilter.Contains(b.BenevolenceType.Guid));
}
SortProperty sortProperty = gList.SortProperty;
if (sortProperty != null)
{
if (sortProperty.Property == "TotalAmount")
{
if (sortProperty.Direction == SortDirection.Descending)
{
benevolenceRequests = benevolenceRequests.OrderByDescending(a => a.BenevolenceResults.Sum(b => b.Amount));
}
else
{
benevolenceRequests = benevolenceRequests.OrderBy(a => a.BenevolenceResults.Sum(b => b.Amount));
}
}
}
else
{
benevolenceRequests = benevolenceRequests.OrderByDescending(a => a.RequestDateTime).ThenByDescending(a => a.Id);
}
// Filter query by any configured attribute filters
if (AvailableAttributes != null && AvailableAttributes.Any())
{
foreach (var attribute in AvailableAttributes)
{
var filterControl = phAttributeFilters.FindControl("filter_" + attribute.Id.ToString());
benevolenceRequests = attribute.FieldType.Field.ApplyAttributeQueryFilter(benevolenceRequests, filterControl, attribute, benevolenceRequestService, Rock.Reporting.FilterMode.SimpleFilter);
}
}
var personContext = this.ContextEntity <Person>();
if (personContext != null)
{
benevolenceRequests = benevolenceRequests.Where(a => a.RequestedByPersonAlias.PersonId == personContext.Id);
}
if (sortProperty != null)
{
gList.DataSource = benevolenceRequests.Sort(sortProperty).ToList();
}
else
{
gList.DataSource = benevolenceRequests.OrderByDescending(p => p.CreatedDateTime).ThenByDescending(p => p.Id).ToList();
}
// Hide the campus column if the campus filter is not visible.
gList.ColumnsOfType <RockBoundField>().First(c => c.DataField == "Campus.Name").Visible = cpCampus.Visible;
gList.EntityTypeId = EntityTypeCache.Get <BenevolenceRequest>().Id;
gList.DataBind();
// Hide columns and specific fields if the hide column attributes are set on the block.
if (hideGridColumns?.Count() > 0)
{
foreach (DataControlField controlField in gList.Columns)
{
controlField.Visible = !hideGridColumns.Contains(controlField.HeaderText.ToUpper());
}
pnlSummary.Visible = !hideGridColumns.Contains("TOTAL RESULTS");
}
// Hide the campus column if the campus filter is not visible.
gList.ColumnsOfType <RockBoundField>().First(c => c.DataField == "Campus.Name").Visible = cpCampus.Visible;
// Builds the Totals section
var definedTypeCache = DefinedTypeCache.Get(new Guid(Rock.SystemGuid.DefinedType.BENEVOLENCE_RESULT_TYPE));
Dictionary <string, decimal> resultTotals = new Dictionary <string, decimal>();
decimal grandTotal = 0;
foreach (BenevolenceRequest request in benevolenceRequests)
{
foreach (BenevolenceResult result in request.BenevolenceResults)
{
if (result.Amount != null)
{
if (resultTotals.ContainsKey(result.ResultTypeValue.Value))
{
resultTotals[result.ResultTypeValue.Value] += result.Amount.Value;
}
else
{
resultTotals.Add(result.ResultTypeValue.Value, result.Amount.Value);
}
grandTotal += result.Amount.Value;
}
}
}
foreach (KeyValuePair <string, decimal> keyValuePair in resultTotals)
{
phSummary.Controls.Add(new LiteralControl(string.Format("<div class='row'><div class='col-xs-8'>{0}: </div><div class='col-xs-4 text-right'>{1}</div></div>", keyValuePair.Key, keyValuePair.Value.FormatAsCurrency())));
}
phSummary.Controls.Add(new LiteralControl(string.Format("<div class='row'><div class='col-xs-8'><b>Total: </div><div class='col-xs-4 text-right'>{0}</b></div></div>", grandTotal.FormatAsCurrency())));
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
phSummary.Controls.Clear();
rFilter.Visible = true;
gList.Visible = true;
RockContext rockContext = new RockContext();
BenevolenceRequestService benevolenceRequestService = new BenevolenceRequestService(rockContext);
var qry = benevolenceRequestService.Queryable("BenevolenceResults,RequestedByPersonAlias,RequestedByPersonAlias.Person,CaseWorkerPersonAlias,CaseWorkerPersonAlias.Person").AsNoTracking();
// Filter by Start Date
DateTime?startDate = drpDate.LowerValue;
if (startDate != null)
{
qry = qry.Where(b => b.RequestDateTime >= startDate);
}
// Filter by End Date
DateTime?endDate = drpDate.UpperValue;
if (endDate != null)
{
qry = qry.Where(b => b.RequestDateTime <= endDate);
}
// Filter by Campus
if (cpCampus.SelectedCampusId.HasValue)
{
qry = qry.Where(b => b.CampusId == cpCampus.SelectedCampusId);
}
if (TargetPerson != null)
{
// show benevolence request for the target person and also for their family members
var qryFamilyMembers = TargetPerson.GetFamilyMembers(true, rockContext);
qry = qry.Where(a => a.RequestedByPersonAliasId.HasValue && qryFamilyMembers.Any(b => b.PersonId == a.RequestedByPersonAlias.PersonId));
}
else
{
// Filter by First Name
string firstName = tbFirstName.Text;
if (!string.IsNullOrWhiteSpace(firstName))
{
qry = qry.Where(b => b.FirstName.StartsWith(firstName));
}
// Filter by Last Name
string lastName = tbLastName.Text;
if (!string.IsNullOrWhiteSpace(lastName))
{
qry = qry.Where(b => b.LastName.StartsWith(lastName));
}
}
// Filter by Government Id
string governmentId = tbGovernmentId.Text;
if (!string.IsNullOrWhiteSpace(governmentId))
{
qry = qry.Where(b => b.GovernmentId.StartsWith(governmentId));
}
// Filter by Case Worker
int?caseWorkerPersonAliasId = ddlCaseWorker.SelectedItem.Value.AsIntegerOrNull();
if (caseWorkerPersonAliasId != null)
{
qry = qry.Where(b => b.CaseWorkerPersonAliasId == caseWorkerPersonAliasId);
}
// Filter by Result
int?resultTypeValueId = dvpResult.SelectedItem.Value.AsIntegerOrNull();
if (resultTypeValueId != null)
{
qry = qry.Where(b => b.BenevolenceResults.Where(r => r.ResultTypeValueId == resultTypeValueId).Count() > 0);
}
// Filter by Request Status
int?requestStatusValueId = dvpStatus.SelectedItem.Value.AsIntegerOrNull();
if (requestStatusValueId != null)
{
qry = qry.Where(b => b.RequestStatusValueId == requestStatusValueId);
}
SortProperty sortProperty = gList.SortProperty;
if (sortProperty != null)
{
if (sortProperty.Property == "TotalAmount")
{
if (sortProperty.Direction == SortDirection.Descending)
{
qry = qry.OrderByDescending(a => a.BenevolenceResults.Sum(b => b.Amount));
}
else
{
qry = qry.OrderBy(a => a.BenevolenceResults.Sum(b => b.Amount));
}
}
else
{
qry = qry.Sort(sortProperty);
}
}
else
{
qry = qry.OrderByDescending(a => a.RequestDateTime).ThenByDescending(a => a.Id);
}
// Filter query by any configured attribute filters
if (AvailableAttributes != null && AvailableAttributes.Any())
{
foreach (var attribute in AvailableAttributes)
{
var filterControl = phAttributeFilters.FindControl("filter_" + attribute.Id.ToString());
qry = attribute.FieldType.Field.ApplyAttributeQueryFilter(qry, filterControl, attribute, benevolenceRequestService, Rock.Reporting.FilterMode.SimpleFilter);
}
}
var list = qry.ToList();
gList.DataSource = list;
gList.DataBind();
// Builds the Totals section
var definedTypeCache = DefinedTypeCache.Get(new Guid(Rock.SystemGuid.DefinedType.BENEVOLENCE_RESULT_TYPE));
Dictionary <string, decimal> resultTotals = new Dictionary <string, decimal>();
decimal grandTotal = 0;
foreach (BenevolenceRequest request in list)
{
foreach (BenevolenceResult result in request.BenevolenceResults)
{
if (result.Amount != null)
{
if (resultTotals.ContainsKey(result.ResultTypeValue.Value))
{
resultTotals[result.ResultTypeValue.Value] += result.Amount.Value;
}
else
{
resultTotals.Add(result.ResultTypeValue.Value, result.Amount.Value);
}
grandTotal += result.Amount.Value;
}
}
}
foreach (KeyValuePair <string, decimal> keyValuePair in resultTotals)
{
phSummary.Controls.Add(new LiteralControl(string.Format("<div class='row'><div class='col-xs-8'>{0}: </div><div class='col-xs-4 text-right'>{1}{2:#,##0.00}</div></div>", keyValuePair.Key, GlobalAttributesCache.Value("CurrencySymbol"), keyValuePair.Value)));
}
phSummary.Controls.Add(new LiteralControl(string.Format("<div class='row'><div class='col-xs-8'><b>Total: </div><div class='col-xs-4 text-right'>{0}{1:#,##0.00}</b></div></div>", GlobalAttributesCache.Value("CurrencySymbol"), grandTotal)));
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
phSummary.Controls.Clear();
rFilter.Visible = true;
gList.Visible = true;
RockContext rockContext = new RockContext();
BenevolenceRequestService benevolenceRequestService = new BenevolenceRequestService( rockContext );
var qry = benevolenceRequestService.Queryable( "BenevolenceResults,RequestedByPersonAlias,RequestedByPersonAlias.Person,CaseWorkerPersonAlias,CaseWorkerPersonAlias.Person" );
// Filter by Start Date
DateTime? startDate = drpDate.LowerValue;
if ( startDate != null )
{
qry = qry.Where( b => b.RequestDateTime >= startDate );
}
// Filter by End Date
DateTime? endDate = drpDate.UpperValue;
if ( endDate != null )
{
qry = qry.Where( b => b.RequestDateTime <= endDate );
}
if ( TargetPerson != null )
{
// show benevolence request for the target person and also for their family members
var qryFamilyMembers = TargetPerson.GetFamilyMembers( true, rockContext );
qry = qry.Where( a => a.RequestedByPersonAliasId.HasValue && qryFamilyMembers.Any( b => b.PersonId == a.RequestedByPersonAlias.PersonId ) );
}
else
{
// Filter by First Name
string firstName = tbFirstName.Text;
if ( !string.IsNullOrWhiteSpace( firstName ) )
{
qry = qry.Where( b => b.FirstName.StartsWith( firstName ) );
}
// Filter by Last Name
string lastName = tbLastName.Text;
if ( !string.IsNullOrWhiteSpace( lastName ) )
{
qry = qry.Where( b => b.LastName.StartsWith( lastName ) );
}
}
// Filter by Government Id
string governmentId = tbGovernmentId.Text;
if ( !string.IsNullOrWhiteSpace( governmentId ) )
{
qry = qry.Where( b => b.GovernmentId.StartsWith( governmentId ) );
}
// Filter by Case Worker
int? caseWorkerPersonAliasId = ddlCaseWorker.SelectedItem.Value.AsIntegerOrNull();
if ( caseWorkerPersonAliasId != null )
{
qry = qry.Where( b => b.CaseWorkerPersonAliasId == caseWorkerPersonAliasId );
}
// Filter by Result
int? resultTypeValueId = ddlResult.SelectedItem.Value.AsIntegerOrNull();
if ( resultTypeValueId != null )
{
qry = qry.Where( b => b.BenevolenceResults.Where( r => r.ResultTypeValueId == resultTypeValueId ).Count() > 0 );
}
// Filter by Request Status
int? requestStatusValueId = ddlStatus.SelectedItem.Value.AsIntegerOrNull();
if ( requestStatusValueId != null )
{
qry = qry.Where( b => b.RequestStatusValueId == requestStatusValueId );
}
SortProperty sortProperty = gList.SortProperty;
if ( sortProperty != null )
{
if ( sortProperty.Property == "TotalAmount" )
{
if ( sortProperty.Direction == SortDirection.Descending )
{
qry = qry.OrderByDescending( a => a.BenevolenceResults.Sum( b => b.Amount ) );
}
else
{
qry = qry.OrderBy( a => a.BenevolenceResults.Sum( b => b.Amount ) );
}
}
else
{
qry = qry.Sort( sortProperty );
}
}
else
{
qry = qry.OrderByDescending( a => a.RequestDateTime ).ThenByDescending( a => a.Id );
}
gList.DataSource = qry.ToList();
gList.DataBind();
// Builds the Totals section
var definedTypeCache = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.BENEVOLENCE_RESULT_TYPE ) );
Dictionary<string, decimal> resultTotals = new Dictionary<string, decimal>();
decimal grandTotal = 0;
foreach ( BenevolenceRequest request in qry.ToList() )
{
foreach ( BenevolenceResult result in request.BenevolenceResults )
{
if ( result.Amount != null )
{
if ( resultTotals.ContainsKey( result.ResultTypeValue.Value ) )
{
resultTotals[result.ResultTypeValue.Value] += result.Amount.Value;
}
else
{
resultTotals.Add( result.ResultTypeValue.Value, result.Amount.Value );
}
grandTotal += result.Amount.Value;
}
}
}
foreach ( KeyValuePair<string, decimal> keyValuePair in resultTotals )
{
phSummary.Controls.Add( new LiteralControl( string.Format( "<div class='row'><div class='col-xs-8'>{0}: </div><div class='col-xs-4 text-right'>{1}{2:#,##0.00}</div></div>", keyValuePair.Key, GlobalAttributesCache.Value( "CurrencySymbol" ), keyValuePair.Value ) ) );
}
phSummary.Controls.Add( new LiteralControl( string.Format( "<div class='row'><div class='col-xs-8'><b>Total: </div><div class='col-xs-4 text-right'>{0}{1:#,##0.00}</b></div></div>", GlobalAttributesCache.Value( "CurrencySymbol" ), grandTotal ) ) );
}
/// <summary>
/// Loads the Benevolence Requests data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadBenevolenceRequest(CSVInstance csvData)
{
var lookupContext = new RockContext();
var benevolenceRequestService = new BenevolenceRequestService(lookupContext);
var importedBenevolenceRequests = benevolenceRequestService.Queryable().Count(p => p.ForeignKey != null);
var requestStatusDTGuid = Rock.SystemGuid.DefinedType.BENEVOLENCE_REQUEST_STATUS.AsGuid();
var requestStatusPendingDVId = DefinedValueCache.Get(new Guid(Rock.SystemGuid.DefinedValue.BENEVOLENCE_PENDING), lookupContext).Id;
var homePhoneTypeDVId = DefinedValueCache.Get(new Guid(Rock.SystemGuid.DefinedValue.PERSON_PHONE_TYPE_HOME), lookupContext).Id;
var mobilePhoneTypeDVId = DefinedValueCache.Get(new Guid(Rock.SystemGuid.DefinedValue.PERSON_PHONE_TYPE_MOBILE), lookupContext).Id;
var workPhoneTypeDVId = DefinedValueCache.Get(new Guid(Rock.SystemGuid.DefinedValue.PERSON_PHONE_TYPE_WORK), lookupContext).Id;
var benevolenceRequestList = new List <BenevolenceRequest>();
var completedItems = 0;
var addedItems = 0;
ReportProgress(0, string.Format("Verifying benevolence request import ({0:N0} already imported).", importedBenevolenceRequests));
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
var benevolenceRequestText = row[BenevolenceRequestText];
var benevolenceRequestDate = row[BenevolenceRequestDate];
var benevolenceRequestId = row[BenevolenceRequestId];
var benevolenceRequestFirstName = row[BenevolenceRequestFirstName];
var benevolenceRequestLastName = row[BenevolenceRequestLastName];
var benevolenceRequestEmail = row[BenevolenceRequestEmail];
var benevolenceRequestCreatedById = row[BenevolenceRequestCreatedById];
var benevolenceRequestCreatedDate = row[BenevolenceRequestCreatedDate];
var benevolenceRequestRequestedById = row[BenevolenceRequestRequestedById];
var benevolenceRequestCaseWorkerId = row[BenevolenceRequestCaseWorkerId];
var benevolenceRequestCellPhone = row[BenevolenceRequestCellPhone];
var benevolenceRequestHomePhone = row[BenevolenceRequestHomePhone];
var benevolenceRequestWorkPhone = row[BenevolenceRequestWorkPhone];
var benevolenceRequestGovernmentId = row[BenevolenceRequestGovernmentId];
var benevolenceRequestProvidedNextSteps = row[BenevolenceRequestProvidedNextSteps];
var benevolenceRequestStatus = row[BenevolenceRequestStatus];
var benevolenceRequestResultSummary = row[BenevolenceRequestResultSummary];
var benevolenceRequestAddress = row[BenevolenceRequestAddress];
var benevolenceRequestAddress2 = row[BenevolenceRequestAddress2];
var benevolenceRequestCity = row[BenevolenceRequestCity];
var benevolenceRequestState = row[BenevolenceRequestState];
var benevolenceRequestZip = row[BenevolenceRequestZip];
var benevolenceRequestCountry = row[BenevolenceRequestCountry];
//
// Verify we have the minimum required information for a valid BenevolenceRequest in the csv file.
//
if (string.IsNullOrWhiteSpace(benevolenceRequestText) ||
(string.IsNullOrWhiteSpace(benevolenceRequestRequestedById) && (string.IsNullOrWhiteSpace(benevolenceRequestFirstName) || string.IsNullOrWhiteSpace(benevolenceRequestLastName))))
{
ReportProgress(0, $"Benevolence Request {benevolenceRequestId} is missing information. See exception log for details.");
LogException("InvalidBenevolenceRequest", string.Format("RequestId: {0} - BenevolenceRequestText and either BenevolenceRequestRequestedById or both BenevolenceRequestFirstName and BenevolenceRequestLastName are required. Benevolence Request {0} was not imported.", benevolenceRequestId));
completedItems++;
continue;
}
//
// Check that this Benevolence Request doesn't already exist.
//
var exists = false;
if (importedBenevolenceRequests > 0)
{
exists = benevolenceRequestService.Queryable().AsNoTracking().Any(r => r.ForeignKey == benevolenceRequestId);
}
if (!exists)
{
var email = string.Empty;
if (benevolenceRequestEmail.IsEmail())
{
email = benevolenceRequestEmail;
}
int?requestedByAliasId = null;
var requestedByPersonKeys = GetPersonKeys(benevolenceRequestRequestedById);
if (requestedByPersonKeys != null)
{
requestedByAliasId = requestedByPersonKeys.PersonAliasId;
}
int?createdByAliasId = null;
var createdByPersonKeys = GetPersonKeys(benevolenceRequestCreatedById);
if (createdByPersonKeys != null)
{
createdByAliasId = createdByPersonKeys.PersonAliasId;
}
int?caseWorkerAliasId = null;
var caseWorkerPersonKeys = GetPersonKeys(benevolenceRequestCaseWorkerId);
if (caseWorkerPersonKeys != null)
{
caseWorkerAliasId = caseWorkerPersonKeys.PersonAliasId;
}
var requestDate = ( DateTime )ParseDateOrDefault(benevolenceRequestDate, Bulldozer.BulldozerComponent.ImportDateTime);
var dateCreated = ( DateTime )ParseDateOrDefault(benevolenceRequestCreatedDate, Bulldozer.BulldozerComponent.ImportDateTime);
var benevolenceRequest = new BenevolenceRequest
{
RequestedByPersonAliasId = requestedByAliasId,
FirstName = benevolenceRequestFirstName,
LastName = benevolenceRequestLastName,
Email = email,
RequestText = benevolenceRequestText,
RequestDateTime = requestDate,
CreatedDateTime = dateCreated,
CreatedByPersonAliasId = createdByAliasId,
ResultSummary = benevolenceRequestResultSummary,
CaseWorkerPersonAliasId = caseWorkerAliasId,
ForeignKey = benevolenceRequestId,
ForeignId = benevolenceRequestId.AsType <int?>()
};
// Handle request Status
if (!string.IsNullOrWhiteSpace(benevolenceRequestStatus))
{
var statusDV = FindDefinedValueByTypeAndName(lookupContext, requestStatusDTGuid, benevolenceRequestStatus);
if (statusDV == null)
{
statusDV = AddDefinedValue(new RockContext(), requestStatusDTGuid.ToString(), benevolenceRequestStatus);
}
benevolenceRequest.RequestStatusValueId = statusDV.Id;
}
else
{
// set default status to pending
benevolenceRequest.RequestStatusValueId = requestStatusPendingDVId;
}
// Check for requester person and use its info instead
if (requestedByAliasId.HasValue && requestedByAliasId.Value > 0)
{
Person requester = null;
var requesterPersonAlias = new PersonAliasService(lookupContext).Queryable()
.AsNoTracking()
.FirstOrDefault(pa => pa.Id == requestedByAliasId.Value);
if (requesterPersonAlias != null && requesterPersonAlias.PersonId > 0)
{
requester = requesterPersonAlias.Person;
}
if (requester != null)
{
if (!string.IsNullOrWhiteSpace(requester.NickName))
{
benevolenceRequest.FirstName = requester.NickName;
}
else if (!string.IsNullOrWhiteSpace(requester.FirstName))
{
benevolenceRequest.FirstName = requester.FirstName;
}
if (!string.IsNullOrWhiteSpace(requester.LastName))
{
benevolenceRequest.LastName = requester.LastName;
}
if (!string.IsNullOrWhiteSpace(requester.Email))
{
benevolenceRequest.Email = requester.Email;
}
if (requester.PrimaryCampusId.HasValue)
{
benevolenceRequest.CampusId = requester.PrimaryCampusId;
}
if (requester.PhoneNumbers.Any(n => n.NumberTypeValueId.Value == homePhoneTypeDVId))
{
benevolenceRequest.HomePhoneNumber = requester.PhoneNumbers.FirstOrDefault(n => n.NumberTypeValueId.Value == homePhoneTypeDVId).NumberFormatted;
}
if (requester.PhoneNumbers.Any(n => n.NumberTypeValueId.Value == mobilePhoneTypeDVId))
{
benevolenceRequest.CellPhoneNumber = requester.PhoneNumbers.FirstOrDefault(n => n.NumberTypeValueId.Value == mobilePhoneTypeDVId).NumberFormatted;
}
if (requester.PhoneNumbers.Any(n => n.NumberTypeValueId.Value == workPhoneTypeDVId))
{
benevolenceRequest.WorkPhoneNumber = requester.PhoneNumbers.FirstOrDefault(n => n.NumberTypeValueId.Value == workPhoneTypeDVId).NumberFormatted;
}
var requesterAddressLocation = requester.GetHomeLocation();
if (requesterAddressLocation != null)
{
benevolenceRequest.LocationId = requesterAddressLocation.Id;
}
}
else
{
benevolenceRequestRequestedById = null;
}
}
if (string.IsNullOrWhiteSpace(benevolenceRequestRequestedById))
{
// Handle Address
var requestAddress = new LocationService(lookupContext).Get(benevolenceRequestAddress.Left(100), benevolenceRequestAddress2.Left(100), benevolenceRequestCity, benevolenceRequestState, benevolenceRequestZip, benevolenceRequestCountry, verifyLocation: false);
if (requestAddress != null)
{
benevolenceRequest.LocationId = requestAddress.Id;
}
}
benevolenceRequestList.Add(benevolenceRequest);
addedItems++;
}
completedItems++;
if (completedItems % (ReportingNumber * 10) < 1)
{
ReportProgress(0, string.Format("{0:N0} benevolence requests processed.", completedItems));
}
if (completedItems % ReportingNumber < 1)
{
SaveBenevolenceRequests(benevolenceRequestList);
ReportPartialProgress();
benevolenceRequestList.Clear();
}
}
if (benevolenceRequestList.Any())
{
SaveBenevolenceRequests(benevolenceRequestList);
}
ReportProgress(100, string.Format("Finished benevolence request import: {0:N0} benevolence requests processed, {1:N0} imported.", completedItems, addedItems));
return(completedItems);
}
/// <summary>
/// Loads the BenevolenceResult data.
/// </summary>
/// <param name="csvData">The CSV data.</param>
private int LoadBenevolenceResult(CSVInstance csvData)
{
var lookupContext = new RockContext();
var benevolenceResultService = new BenevolenceResultService(lookupContext);
var benevolenceRequestService = new BenevolenceRequestService(lookupContext);
var resultTypeDTGuid = Rock.SystemGuid.DefinedType.BENEVOLENCE_RESULT_TYPE.AsGuid();
var benevolenceResultList = new List <BenevolenceResult>();
var importedRequestIds = new List <int>();
var completed = 0;
var importedCount = 0;
var alreadyImportedCount = benevolenceResultService.Queryable().AsNoTracking().Count(i => i.ForeignKey != null);
ReportProgress(0, $"Starting Benevolence Result import ({alreadyImportedCount:N0} already exist).");
string[] row;
// Uses a look-ahead enumerator: this call will move to the next record immediately
while ((row = csvData.Database.FirstOrDefault()) != null)
{
var benevolenceResultRequestId = row[BenevolenceResultRequestId];
var benevolenceResultType = row[BenevolenceResultType];
var benevolenceResultId = row[BenevolenceResultId];
var benevolenceResultAmount = row[BenevolenceResultAmount];
var benevolenceResultSummary = row[BenevolenceResultSummary];
var benevolenceResultCreatedById = row[BenevolenceResultCreatedById];
var benevolenceResultCreatedDate = row[BenevolenceResultCreatedDate];
//
// Verify the Benevolence Result has a Result Type provided in the csv file.
//
if (string.IsNullOrWhiteSpace(benevolenceResultType))
{
ReportProgress(0, $"Benevolence Result {benevolenceResultId} has no BenevolenceResultType value provided. Skipping Benevolence Result {benevolenceResultId}.");
LogException("InvalidBenevolenceResult", string.Format("ResultId: {0} - Missing BenevolenceResultType value. Benevolence Result {0} was not imported.", benevolenceResultId));
completed++;
continue;
}
BenevolenceRequest benevolenceRequest = null;
if (benevolenceRequestService.Queryable().AsNoTracking().Any(r => r.ForeignKey == benevolenceResultRequestId))
{
benevolenceRequest = benevolenceRequestService.Queryable().AsNoTracking().FirstOrDefault(r => r.ForeignKey == benevolenceResultRequestId);
}
//
// Verify the Benevolence Request exists.
//
if (benevolenceRequest == null || benevolenceRequest.Id < 1)
{
ReportProgress(0, $"Benevolence Request {benevolenceResultRequestId} not found. Skipping Benevolence Result {benevolenceResultId}.");
LogException("InvalidBenevolenceResult", string.Format("ResultId: {0} - BenevolenceResultRequestId {1} does not exist in imported Benevolence Requests. Benevolence Result {0} was not imported.", benevolenceResultId, benevolenceResultRequestId));
completed++;
continue;
}
//
// Check that this Benevolence Result doesn't already exist.
//
var exists = false;
if (alreadyImportedCount > 0)
{
exists = benevolenceResultService.Queryable().AsNoTracking().Any(r => r.ForeignKey == benevolenceResultId);
}
if (!exists)
{
// Handle Result Type
var resultTypeDV = FindDefinedValueByTypeAndName(lookupContext, resultTypeDTGuid, benevolenceResultType);
if (resultTypeDV == null)
{
resultTypeDV = AddDefinedValue(new RockContext(), resultTypeDTGuid.ToString(), benevolenceResultType);
}
// Format created date
var resultCreatedDate = ( DateTime )ParseDateOrDefault(benevolenceResultCreatedDate, Bulldozer.BulldozerComponent.ImportDateTime);
// Handle created by
int?createdByAliasId = null;
var createdByPersonKeys = GetPersonKeys(benevolenceResultCreatedById);
if (createdByPersonKeys != null)
{
createdByAliasId = createdByPersonKeys.PersonAliasId;
}
//
// Create and populate the new Benevolence Result.
//
var benevolenceResult = new BenevolenceResult
{
BenevolenceRequestId = benevolenceRequest.Id,
ResultSummary = benevolenceResultSummary,
ResultTypeValueId = resultTypeDV.Id,
Amount = benevolenceResultAmount.AsType <decimal?>(),
ForeignKey = benevolenceResultId,
ForeignId = benevolenceResultId.AsType <int?>(),
CreatedDateTime = resultCreatedDate,
CreatedByPersonAliasId = createdByAliasId,
};
benevolenceResultList.Add(benevolenceResult);
importedCount++;
}
//
// Notify user of our status.
//
completed++;
if (completed % (ReportingNumber * 10) < 1)
{
ReportProgress(0, $"{completed:N0} Benevolence Request records processed, {importedCount:N0} imported.");
}
if (completed % ReportingNumber < 1)
{
SaveBenevolenceResults(benevolenceResultList);
ReportPartialProgress();
benevolenceResultList.Clear();
// Clear out variables
benevolenceRequestService = new BenevolenceRequestService(lookupContext);
}
}
if (benevolenceResultList.Any())
{
SaveBenevolenceResults(benevolenceResultList);
}
ReportProgress(0, $"Finished Benevolence Result import: {importedCount:N0} records added.");
return(completed);
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
phSummary.Controls.Clear();
rFilter.Visible = true;
gList.Visible = true;
RockContext rockContext = new RockContext();
BenevolenceRequestService benevolenceRequestService = new BenevolenceRequestService(rockContext);
var qry = benevolenceRequestService.Queryable("BenevolenceResults,BenevolenceResults.ResultTypeValue,RequestedByPersonAlias,RequestedByPersonAlias.Person,CaseWorkerPersonAlias,CaseWorkerPersonAlias.Person");
//Filter by Start Date
DateTime?startDate = drpDate.LowerValue;
if (startDate != null)
{
qry = qry.Where(b => b.RequestDateTime >= startDate);
}
//Filter by End Date
DateTime?endDate = drpDate.UpperValue;
if (endDate != null)
{
qry = qry.Where(b => b.RequestDateTime <= endDate);
}
// Filter by First Name
string firstName = tbFirstName.Text;
if (!string.IsNullOrWhiteSpace(firstName))
{
qry = qry.Where(b => b.FirstName.StartsWith(firstName));
}
// Filter by Last Name
string lastName = tbLastName.Text;
if (!string.IsNullOrWhiteSpace(lastName))
{
qry = qry.Where(b => b.LastName.StartsWith(lastName));
}
// Filter by Government Id
string governmentId = tbGovernmentId.Text;
if (!string.IsNullOrWhiteSpace(governmentId))
{
qry = qry.Where(b => b.GovernmentId.StartsWith(governmentId));
}
//Filter by Case Worker
int?caseWorkerPersonAliasId = ddlCaseWorker.SelectedItem.Value.AsIntegerOrNull();
if (caseWorkerPersonAliasId != null)
{
qry = qry.Where(b => b.CaseWorkerPersonAliasId == caseWorkerPersonAliasId);
}
//Filter by Result
int?resultTypeValueId = ddlResult.SelectedItem.Value.AsIntegerOrNull();
if (resultTypeValueId != null)
{
qry = qry.Where(b => b.BenevolenceResults.Where(r => r.ResultTypeValueId == resultTypeValueId).Count() > 0);
}
//Filter by Request Status
int?requestStatusValueId = ddlStatus.SelectedItem.Value.AsIntegerOrNull();
if (requestStatusValueId != null)
{
qry = qry.Where(b => b.RequestStatusValueId == requestStatusValueId);
}
gList.DataSource = qry.ToList();
gList.DataBind();
//Builds the Totals section
var definedTypeCache = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.BENEVOLENCE_RESULT_TYPE));
Dictionary <string, decimal> resultTotals = new Dictionary <string, decimal>();
decimal grandTotal = 0;
foreach (BenevolenceRequest request in qry.ToList())
{
foreach (BenevolenceResult result in request.BenevolenceResults)
{
if (result.Amount != null)
{
if (resultTotals.ContainsKey(result.ResultTypeValue.Value))
{
resultTotals[result.ResultTypeValue.Value] += result.Amount.Value;
}
else
{
resultTotals.Add(result.ResultTypeValue.Value, result.Amount.Value);
}
grandTotal += result.Amount.Value;
}
}
}
foreach (KeyValuePair <string, decimal> keyValuePair in resultTotals)
{
phSummary.Controls.Add(new LiteralControl(String.Format("<div class='row'><div class='col-xs-8'>{0}: </div><div class='col-xs-4 text-right'>{1}{2:0.00}</div></div>", keyValuePair.Key, GlobalAttributesCache.Value("CurrencySymbol"), keyValuePair.Value)));
}
phSummary.Controls.Add(new LiteralControl(String.Format("<div class='row'><div class='col-xs-8'><b>Grand Total: </div><div class='col-xs-4 text-right'>{0}{1:0.00}</b></div></div>", GlobalAttributesCache.Value("CurrencySymbol"), grandTotal)));
}
/// <summary>
/// Maps the specified folder.
/// </summary>
/// <param name="folder">The folder.</param>
/// <param name="requestDocumentType">The benevolence request document file type.</param>
public int Map(ZipArchive folder, BinaryFileType requestDocumentType)
{
var lookupContext = new RockContext();
var emptyJsonObject = "{}";
var newFileList = new Dictionary <KeyValuePair <int, int>, Rock.Model.BinaryFile>();
var benevolenceRequestService = new BenevolenceRequestService(lookupContext);
var importedRequests = benevolenceRequestService
.Queryable().AsNoTracking().Where(t => t.ForeignId != null)
.ToDictionary(t => ( int )t.ForeignId, t => t.Id);
var importedRequestDocuments = new BenevolenceRequestDocumentService(lookupContext)
.Queryable().AsNoTracking().Where(t => t.ForeignId != null)
.ToDictionary(t => ( int )t.ForeignId, t => t.Id);
var storageProvider = requestDocumentType.StorageEntityTypeId == DatabaseProvider.EntityType.Id
? ( ProviderComponent )DatabaseProvider
: ( ProviderComponent )FileSystemProvider;
var completedItems = 0;
var totalEntries = folder.Entries.Count;
var percentage = (totalEntries - 1) / 100 + 1;
ReportProgress(0, string.Format("Verifying benevolence request documents import ({0:N0} found.", totalEntries));
foreach (var file in folder.Entries)
{
var fileExtension = Path.GetExtension(file.Name);
if (FileTypeBlackList.Contains(fileExtension))
{
LogException("Binary File Import", string.Format("{0} filetype not allowed ({1})", fileExtension, file.Name));
continue;
}
var nameWithoutExtension = file.Name.ReplaceLastOccurrence(fileExtension, string.Empty);
var parsedFileName = nameWithoutExtension.Split('_').ToList();
//
// Benevolence Request docs should follow this pattern:
// 0. Request ForeignId
// 1. FileName
// 2. Doc Id
var foreignBenevolenceRequestId = parsedFileName[0].AsType <int?>();
// Make sure the Benevolence Request exists
if (foreignBenevolenceRequestId != null && importedRequests.ContainsKey(( int )foreignBenevolenceRequestId))
{
var benevolenceRequest = benevolenceRequestService.Queryable().AsNoTracking().FirstOrDefault(r => r.ForeignId.HasValue && r.ForeignId == foreignBenevolenceRequestId);
var documentForeignId = -1;
var fileName = string.Empty;
if (parsedFileName.Count() >= 3)
{
documentForeignId = parsedFileName.LastOrDefault().AsInteger();
// If document foreignId is provided, make sure it doesn't already exist
if (documentForeignId > 0 && importedRequestDocuments.ContainsKey(documentForeignId))
{
continue;
}
// Extract filename
parsedFileName.RemoveAt(parsedFileName.Count() - 1); // Remove Doc Id from end
parsedFileName.RemoveAt(0); // Remove Request ForeignId from beginning
fileName = string.Join("_", parsedFileName);
}
else
{
var filename = file.Name.ReplaceLastOccurrence(fileExtension, string.Empty);
}
// Create the binary file
var rockFile = new Rock.Model.BinaryFile
{
IsSystem = false,
IsTemporary = false,
MimeType = GetMIMEType(file.Name),
BinaryFileTypeId = requestDocumentType.Id,
FileName = fileName,
CreatedDateTime = file.LastWriteTime.DateTime,
ModifiedDateTime = file.LastWriteTime.DateTime,
CreatedByPersonAliasId = ImportPersonAliasId
};
rockFile.SetStorageEntityTypeId(requestDocumentType.StorageEntityTypeId);
rockFile.StorageEntitySettings = emptyJsonObject;
if (requestDocumentType.AttributeValues.Any())
{
rockFile.StorageEntitySettings = requestDocumentType.AttributeValues
.ToDictionary(a => a.Key, v => v.Value.Value).ToJson();
}
// use base stream instead of file stream to keep the byte[]
// NOTE: if byte[] converts to a string it will corrupt the stream
using (var fileContent = new StreamReader(file.Open()))
{
rockFile.ContentStream = new MemoryStream(fileContent.BaseStream.ReadBytesToEnd());
}
// add this document file to the Rock transaction
newFileList.Add(new KeyValuePair <int, int>(importedRequests[( int )foreignBenevolenceRequestId], documentForeignId), rockFile);
completedItems++;
if (completedItems % percentage < 1)
{
var percentComplete = completedItems / percentage;
ReportProgress(percentComplete, string.Format("{0:N0} benevolence document files imported ({1}% complete).", completedItems, percentComplete));
}
if (completedItems % ReportingNumber < 1)
{
SaveFiles(newFileList, storageProvider);
// Reset list
newFileList.Clear();
ReportPartialProgress();
}
}
}
if (newFileList.Any())
{
SaveFiles(newFileList, storageProvider);
}
ReportProgress(100, string.Format("Finished document import: {0:N0} benevolence documents imported.", completedItems));
return(completedItems);
}