public void LicenceQueryReturnsCorrectDatabasePrefixForar() { //Arrange LicenceQuery criteria = new LicenceQuery(); //Act var arHeaders = new string[1] { "FIRM_NO" }; //Assert foreach (string header in arHeaders) { Assert.AreEqual("ar.", criteria.getDatabasePrefix(header)); } }
public void LicenceQueryReturnsCorrectDatabasePrefixFora() { //Arrange LicenceQuery criteria = new LicenceQuery(); //Act var aHeaders = new string[3] { "DESCRIPTION", "ACTIVITY_TYPE", "SOURCE_CODE" }; //Assert foreach (string header in aHeaders) { Assert.AreEqual("a.", criteria.getDatabasePrefix(header)); } }
public void LicenceQueryReturnsCorrectDatabasePrefixForn() { //Arrange LicenceQuery criteria = new LicenceQuery(); //Act var nHeaders = new string[5] { "ID", "MAJOR_KEY", "FIRST_NAME", "MIDDLE_NAME", "LAST_NAME", }; //Assert foreach (string header in nHeaders) { Assert.AreEqual("n.", criteria.getDatabasePrefix(header)); } }
public void LicenceQueryReturnsCorrectDatabasePrefixForf() { //Arrange LicenceQuery criteria = new LicenceQuery(); //Act var fHeaders = new string[1] { "SORT_NAME" }; //Assert foreach (string header in fHeaders) { Assert.AreEqual("f.", criteria.getDatabasePrefix(header)); } }
public void LicenceQueryReturnsCorrectDatabasePrefixForGivenType() { //Arrange LicenceQuery criteria = new LicenceQuery(); //Act var nResponse = criteria.getDatabasePrefix("FIRST_NAME"); var aReponse = criteria.getDatabasePrefix("DESCRIPTION"); var fResponse = criteria.getDatabasePrefix("FIRM_NO"); var siResponse = criteria.getDatabasePrefix("FIRM_ID"); //Assert Assert.AreEqual("n.", nResponse); Assert.AreEqual("a.", aReponse); Assert.AreEqual("ar.", fResponse); Assert.AreEqual("si.", siResponse); }
/* * Inputs: criteria - The query which has the fields in LicenceQuery * Returns: A list of Licence objects * Remark: Constructs the query which is executed on the imis database and returns the results as a * list of Licence objects where each Licence is a record that was returned */ public List <Licence> Post([FromBody] LicenceQuery criteria) { var persons = new List <Licence>(); var queryBase = @"SELECT TOP (1000) n.[ID] as [IMIS_ID] ,n.[MAJOR_KEY] as [MEMBER_NO] ,n.[FUNCTIONAL_TITLE] ,n.[FIRST_NAME] ,n.[MIDDLE_NAME] ,n.[LAST_NAME] ,n.[STATUS] ,n.[ORG_CODE] ,a.[DESCRIPTION] ,a.[TRANSACTION_DATE] ,a.[EFFECTIVE_DATE] ,a.[THRU_DATE] ,a.[NOTE] ,a.[PRODUCT_CODE] ,a.[ACTIVITY_TYPE] ,a.[SOURCE_CODE] ,si.[FIRM_ID] ,ar.[FIRM_NO] ,f.[AR_MONTH] ,f.[SORT_NAME] ,f.[FINANCIAL_YEAR_END] ,f.[CA_LICENSED_END_DATE] ,f.[AROther] ,f.[FIRM_NOTES] ,pii.[StartDate] as [PII_Start_Date] ,pii.[EndDate] as [PII_End_Date] ,pii.[RecordState] as [PII_Status] ,pii.[PII_Comments] ,dpb.[StartDate] as [DPB_Start_Date] ,dpb.[EndDate] as [DPB_End_Date] ,dpb.[RecordStatus] as [DPB_Status] ,dpb.[Comments] as [DPB_COMMENTS] ,ar.[DATE_CREATED] as [AR_Start_Date] ,ar.[STATUS] as [AR_Status] FROM [imis].[dbo].[Name] as n LEFT OUTER JOIN activity as a on n.id = a.id LEFT OUTER JOIN Student_Info as si on n.ID = si.ID LEFT OUTER JOIN Firm as f on n.ID = f.ID LEFT OUTER JOIN IREG_DPB as dpb on n.ID = dpb.NameID LEFT OUTER JOIN IREG_PII as pii on n.ID = pii.NameID LEFT OUTER JOIN IREG_AnnRet as ar on n.ID = ar.ID " + criteria.queryGenerator();; using (var connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["sql"].ConnectionString)) { try { persons = connection.Query <Licence>(queryBase, criteria.ToDapperParameter()).ToList(); } catch (SqlException e) { _Logger.Error("Database Query Failed", e); switch (e.Number) { //2601 = SQL Violation in unique index case 2601: return(persons); default: return(persons); } } catch (Exception e) { _Logger.Error("Exception occurred", e); throw e; } } return(persons); }