public int GetTotalRowsAuthorizations(Connections connections, HHAXAuthorizationsSearch authorizationsSearch) { QueryHHAXAuthorizations _query = new QueryHHAXAuthorizations(); int totalRows = 0; string connectionString = $"Server={connections.ServerName};Database={connections.DatabaseName};User Id={connections.DatabaseUsername};Password={connections.DatabasePassword};"; string queryString = _query.CrateQueryStringCountRowsAuthorizations(authorizationsSearch); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection) { CommandTimeout = 100 }; command = _query.EmbedParameters(command, authorizationsSearch); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { totalRows = Convert.ToInt32(reader[0]); } reader.Close(); return(totalRows); } catch (Exception ex) { connection.Close(); Console.WriteLine(ex.ToString()); return(0); } } }
public SqlCommand EmbedParameters(SqlCommand command, HHAXAuthorizationsSearch authorizationsSearch) { if (authorizationsSearch.AgencyID != null) { command.Parameters.AddWithValue("@agencyID", authorizationsSearch.AgencyID); } if (authorizationsSearch.TransID != "" && authorizationsSearch.TransID != null) { command.Parameters.AddWithValue("@transID", authorizationsSearch.TransID + "%"); } if (authorizationsSearch.TransStatus != "" && authorizationsSearch.TransStatus != null) { command.Parameters.AddWithValue("@transStatus", authorizationsSearch.TransStatus); } if (authorizationsSearch.Service != "" && authorizationsSearch.Service != null) { command.Parameters.AddWithValue("@service", authorizationsSearch.Service + "%"); } if (authorizationsSearch.AdmissionID != "" && authorizationsSearch.AdmissionID != null) { command.Parameters.AddWithValue("@admissionID", authorizationsSearch.AdmissionID + "%"); } if (authorizationsSearch.AuthRefNo != "" && authorizationsSearch.AuthRefNo != null) { command.Parameters.AddWithValue("@authRefNo", authorizationsSearch.AuthRefNo + "%"); } if (authorizationsSearch.AuthID != "" && authorizationsSearch.AuthID != null) { command.Parameters.AddWithValue("@authID", authorizationsSearch.AuthID + "%"); } if (authorizationsSearch.AuthDateBegin != null) { command.Parameters.Add("@begin", SqlDbType.Date).Value = ((DateTime)(authorizationsSearch.AuthDateBegin)).Date; } if (authorizationsSearch.AuthDateEnd != null) { command.Parameters.Add("@end", SqlDbType.Date).Value = ((DateTime)(authorizationsSearch.AuthDateEnd)).Date; } if (authorizationsSearch.ModifiedDateFrom != null) { command.Parameters.Add("@from", SqlDbType.Date).Value = ((DateTime)(authorizationsSearch.ModifiedDateFrom)).Date; } if (authorizationsSearch.ModifiedDateTo != null) { command.Parameters.Add("@to", SqlDbType.Date).Value = ((DateTime)(authorizationsSearch.ModifiedDateTo)).Date; } return(command); }
public string CrateQueryStringCountRowsAuthorizations(HHAXAuthorizationsSearch authorizationsSearch) { string selectString = "SELECT E.HCO_ERR_DESC, A.TRANS_ID, T.TRANSACTION_STATUS_ID, S.STATUS_DESC, A.AGENCY_ID, P.FIRST_NAME, P.MIDDLE_NAME, P.LAST_NAME, P.PATIENT_ID, A.SERVICE_TYPE, A.BILLING_SERVICE_CODE, A.ADMISSION_ID, A.AUTHORIZATION_NUMBER, P.MR_NUMBER, A.AUTHORIZATION_ID, A.FROM_DATE, A.TO_DATE, A.MODIFIED_DATE, A.INVALID_DATA FROM PATIENT_AUTHORIZATIONS as A " + "LEFT JOIN (SELECT DISTINCT FIRST_NAME, MIDDLE_NAME, LAST_NAME, AGENCY_ID, PATIENT_ID, MR_NUMBER FROM PATIENT_DEMOG) as P " + "ON A.PATIENT_ID = P.PATIENT_ID AND A.AGENCY_ID = P.AGENCY_ID " + "LEFT JOIN TRANSACTION_FILE_RECS T ON A.TRANS_ID = T.TRANS_ID " + "LEFT JOIN TRANS_STATUSES S ON T.TRANSACTION_STATUS_ID = S.STATUS_ID " + "LEFT JOIN TRANSACTION_ERRORS E ON A.TRANS_ID = E.TRANS_ID "; string filterString = CreateFilterString(authorizationsSearch, out bool isNoFilter); string queryString = isNoFilter ? selectString : selectString + filterString; queryString = AddFilterPatients(queryString, authorizationsSearch); string countString = "SELECT COUNT(*) FROM (" + queryString + ") as CO"; return(countString); }
public IActionResult GetAuthorizations([FromBody] JObject data) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } Connections connections = data["connection"].ToObject <Connections>(); HHAXAuthorizationsSearch authorizationSearch = data["authorizationSearch"].ToObject <HHAXAuthorizationsSearch>(); Paging paging = data["paging"].ToObject <Paging>(); var authorizationResults = _service.GetHHAXAuthorizationResults(connections, authorizationSearch, paging); int totalRows = _service.GetTotalRowsAuthorizations(connections, authorizationSearch); if (authorizationResults != null) { return(Ok(new { authorizationResults, totalRows })); } return(BadRequest(new { message = "Error get results!" })); }
public string AddFilterPatients(string queryString, HHAXAuthorizationsSearch authorizationsSearch) { if (authorizationsSearch.FirstName != "" && authorizationsSearch.FirstName != null) { string firstName = "'" + authorizationsSearch.FirstName + "%'"; queryString = queryString.Replace("@firstName", firstName); } if (authorizationsSearch.LastName != "" && authorizationsSearch.LastName != null) { string lastName = "'" + authorizationsSearch.LastName + "%'"; queryString = queryString.Replace("@lastName", lastName); } if (authorizationsSearch.MrNumber != "" && authorizationsSearch.MrNumber != null) { string mrNumber = "'" + authorizationsSearch.MrNumber + "%'"; queryString = queryString.Replace("@mrNumber", mrNumber); } return(queryString); }
public List <HHAXAuthorizationsResult> GetHHAXAuthorizationResults(Connections connections, HHAXAuthorizationsSearch authorizationsSearch, Paging paging) { QueryHHAXAuthorizations _query = new QueryHHAXAuthorizations(); string connectionString = $"Server={connections.ServerName};Database={connections.DatabaseName};User Id={connections.DatabaseUsername};Password={connections.DatabasePassword};"; string queryString = _query.CreateQueryStringAuthorizations(authorizationsSearch, paging); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection) { CommandTimeout = 100 }; command = _query.EmbedParameters(command, authorizationsSearch); try { List <HHAXAuthorizationsResult> results = new List <HHAXAuthorizationsResult>(); connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { HHAXAuthorizationsResult authorizationResult = new HHAXAuthorizationsResult(); authorizationResult.SetValuesFromReader(reader); results.Add(authorizationResult); } reader.Close(); connection.Close(); return(results); } catch (Exception ex) { Console.WriteLine(ex.ToString()); return(null); } finally { connection.Close(); } } }
public string CreateFilterString(HHAXAuthorizationsSearch authorizationsSearch, out bool isNoFilter) { isNoFilter = true; string filterString = "WHERE "; if (authorizationsSearch.AgencyID != null) { isNoFilter = false; filterString += "A.AGENCY_ID = @agencyID "; } if (authorizationsSearch.TransID != "" && authorizationsSearch.TransID != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "A.TRANS_ID like CONVERT(varchar(10), @transID)"; } if (authorizationsSearch.TransStatus != "" && authorizationsSearch.TransStatus != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "TRANSACTION_STATUS_ID = CONVERT(varchar(10), @transStatus)"; } if (authorizationsSearch.FirstName != "" && authorizationsSearch.FirstName != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "FIRST_NAME like @firstName "; } if (authorizationsSearch.LastName != "" && authorizationsSearch.LastName != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "LAST_NAME like @lastName "; } if (authorizationsSearch.Service != "" && authorizationsSearch.Service != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "SERVICE_TYPE like @service "; } if (authorizationsSearch.AdmissionID != "" && authorizationsSearch.AdmissionID != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "ADMISSION_ID like @admissionID "; } if (authorizationsSearch.AuthRefNo != "" && authorizationsSearch.AuthRefNo != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "AUTHORIZATION_NUMBER like @authRefNo "; } if (authorizationsSearch.MrNumber != "" && authorizationsSearch.MrNumber != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "MR_NUMBER like @mrNumber "; } if (authorizationsSearch.AuthID != "" && authorizationsSearch.AuthID != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "CONVERT(VARCHAR(50), AUTHORIZATION_ID) like @authID "; } if (authorizationsSearch.AuthDateBegin != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "CONVERT(DATE, FROM_DATE) >= @begin "; } if (authorizationsSearch.AuthDateEnd != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "CONVERT(DATE, TO_DATE) <= @end "; } if (authorizationsSearch.ModifiedDateFrom != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "CONVERT(DATE, MODIFIED_DATE) >= @from "; } if (authorizationsSearch.ModifiedDateTo != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "CONVERT(DATE, MODIFIED_DATE) <= @to "; } return(filterString); }