public override Task <IEnumerable <Models.Examination> > Handle(FinanceQuery param)
{
if (param == null)
{
throw new ArgumentNullException(nameof(param));
}
var queryExpression = _examinationQueryBuilder.GetFinancePredicate(param);
Expression <Func <Models.Examination, dynamic> > select = examination => new
{
id = examination.ExaminationId,
site_location_id = examination.SiteLocationId,
trust_location_id = examination.TrustLocationId,
region_location_id = examination.RegionLocationId,
national_location_id = examination.NationalLocationId,
medical_team = examination.MedicalTeam,
CreatedAt = examination.CreatedAt,
case_completed = examination.CaseCompleted,
nhs_number = examination.NhsNumber,
waive_fee = examination.CaseOutcome.WaiveFee,
case_outcome = examination.CaseOutcome
};
return(GetItemsAsync(queryExpression, select));
}
/*
* Inputs: criteria - The query which has the fields in FinanceQuery
* Returns: A list of Finance objects
* Remark: Constructs the query which is executed on the imis database and returns the results as a
* list of Finance objects where each Finance is a record that was returned
*/
public List <Finance> Post([FromBody] FinanceQuery criteria)
{
var persons = new List <Finance>();
var queryBase = @"SELECT TOP (2000) n.[ID] as [IMIS_ID]
,n.[MAJOR_KEY] as [MEMBER_NO]
,si.[STUDENT_NO]
,i.[INVOICE_DATE]
,i.[REFERENCE_NUM]
,i.[DESCRIPTION] AS [INVOICE_DESCRIPTION]
,i.[CHARGES]
,i.[CREDITS]
,i.[BALANCE]
,i.[Note]
,t.[TRANSACTION_DATE] AS [TRANS_TRANSACTION_DATE]
,t.[TRANSACTION_TYPE]
,t.[DESCRIPTION] AS [TRANSACTION_DESCRIPTION]
,t.[AMOUNT] AS [TRANSACTION_AMOUNT]
,f.[MAIN_FIRM_NO]
FROM [imis].[dbo].[Name] as n
LEFT OUTER JOIN Student_Info as si
on n.ID = si.ID
JOIN Firm as f
on n.ID = f.ID
LEFT OUTER JOIN Orders as o
on n.CO_ID = o.CO_ID
LEFT OUTER JOIN Trans as t
on t.TRANS_NUMBER = o.ORDER_NUMBER
LEFT OUTER JOIN Invoice as i
on t.TRANS_NUMBER = i.REFERENCE_NUM " + criteria.queryGenerator() + " ORDER BY TRANSACTION_DATE DESC";
using (var connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["sql"].ConnectionString))
{
try
{
persons = connection.Query <Finance>(queryBase, criteria.ToDapperParameter()).ToList();
}
catch (SqlException e)
{
_Logger.Error("Database Query Failed", e);
//2601 = SQL Violation in unique index
switch (e.Number)
{
case 2601: return(persons);
default: throw e;
}
}
catch (Exception e)
{
_Logger.Error("Exception occurred", e);
throw e;
}
}
return(persons);
}
public Expression <Func <Models.Examination, bool> > GetFinancePredicate(FinanceQuery queryObject)
{
var permissedLocationFilter = GetPermissedLocationPredicate(queryObject.PermissedLocation);
var locationFilter = GetLocationPredicate(queryObject.LocationId);
var dateFromQuery = GetCaseCreatedFromQuery(queryObject.DateFrom);
var dateToQuery = GetCaseCreatedToQuery(queryObject.DateTo);
Expression <Func <Models.Examination, bool> > voidCasesQuery = examination => !examination.IsVoid;
return(dateFromQuery.And(dateToQuery).And(permissedLocationFilter).And(locationFilter).And(voidCasesQuery));
}
public void FinanceQueryReturnsCorrectDatabasePrefixForGivenType()
{
//Arrange
FinanceQuery criteria = new FinanceQuery();
//Act
var nResponse = criteria.getDatabasePrefix("ID");
var ecResponse = criteria.getDatabasePrefix("STUDENT_NO");
//Assert
Assert.AreEqual("n.", nResponse);
Assert.AreEqual("si.", ecResponse);
}
public void FinanceQueryReturnsCorrectDatabasePrefixFori()
{
//Arrange
FinanceQuery criteria = new FinanceQuery();
//Act
var iHeaders = new string[1] {
"REFERENCE_NUM"
};
foreach (string header in iHeaders)
{
Assert.AreEqual("i.", criteria.getDatabasePrefix(header));
}
}
public void FinanceQueryReturnsCorrectDatabasePrefixForf()
{
//Arrange
FinanceQuery criteria = new FinanceQuery();
//Act
var fHeaders = new string[1] {
"MAIN_FIRM_NO"
};
//Assert
foreach (string header in fHeaders)
{
Assert.AreEqual("f.", criteria.getDatabasePrefix(header));
}
}
public void FinanceQueryReturnsCorrectDatabasePrefixForsi()
{
//Arrange
FinanceQuery criteria = new FinanceQuery();
//Act
var siHeaders = new string[1] {
"STUDENT_NO"
};
//Assert
foreach (string header in siHeaders)
{
Assert.AreEqual("si.", criteria.getDatabasePrefix(header));
}
}
public void FinanceQueryReturnsCorrectDatabasePrefixForn()
{
//Arrange
FinanceQuery criteria = new FinanceQuery();
//Act
var nHeaders = new string[2] {
"ID", "MAJOR_KEY"
};
//Assert
foreach (string header in nHeaders)
{
Assert.AreEqual("n.", criteria.getDatabasePrefix(header));
}
}
public virtual async Task DateRangeReturnsCorrectCount()
{
//Arrange
var dateFrom = new DateTime(2010, 2, 1);
var dateTo = new DateTime(2010, 2, 27);
var locationId = "location1";
var permissedLocation = new string[] { "location1" };
var financeQuery = new FinanceQuery(dateFrom, dateTo, locationId, permissedLocation);
//Act
var results = await Service.Handle(financeQuery);
//Assert
results.Should().NotBeNull();
Assert.Equal(1, results.Count());
}
