public int GetTotalRowsAuthorizations(Connections connections, AuthorizationSearch authorizationSearch) { QueryWarehouseAuthorizations _query = new QueryWarehouseAuthorizations(); int totalRows = 0; string connectionString = $"Server={connections.ServerName};Database={connections.DatabaseName};User Id={connections.DatabaseUsername};Password={connections.DatabasePassword};"; string queryString = _query.CrateQueryStringCountRowsAuthorization(authorizationSearch); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); command = _query.EmbedParameters(command, authorizationSearch); 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 IActionResult GetSearchAuthorizations([FromBody] JObject data) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } Connections connections = data["connection"].ToObject <Connections>(); AuthorizationSearch authorizationSearch = data["authorizationSearch"].ToObject <AuthorizationSearch>(); Paging paging = data["paging"].ToObject <Paging>(); var authorizationResults = _service.GetAuthorizationResults(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 CrateQueryStringAuthorization(AuthorizationSearch authorizationSearch, Paging paging) { string selectJoin = "(SELECT C.entFirstName, C.entMiddleInitial, C.entLastName, A.jurisdictionID, A.agencyID, A.adTypeID, A.clientOtherID, A. authServiceID, A.authEventID, A.authRefNo, A.authFormat, A.authMaximum, A.authDateFrom, A.authDateTo, A.diag10Code, A.authShared, A.authVoided, A.rowupdated, A.rowcreated, A.IsProcessed, A.ftpFileName " + "FROM dbo.DataImport_Authorization as A LEFT JOIN dbo.DataImport_Client as C on " + "(A.jurisdictionID = C.jurisdictionID and A.clientOtherID = C.clientOtherID and A.adTypeID = C.adTypeID)) as R "; string selectString = "SELECT * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) ) AS RowNum, jurisdictionID, agencyID, ftpFileName, entFirstName, entMiddleInitial, entLastName, adTypeID, clientOtherID, authServiceID, authEventID, authRefNo, authFormat, authMaximum, authDateFrom, authDateTo, diag10Code, authShared, authVoided, rowupdated, rowcreated, IsProcessed FROM " + selectJoin; string filterString = "WHERE "; bool isNoFilter = true; if (authorizationSearch.Jurisdiction != "") { isNoFilter = false; filterString += "jurisdictionID = @jurisdictionID "; } if (authorizationSearch.AgencyID != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "agencyID LIKE @agencyID "; } if (authorizationSearch.FirstName != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "entFirstName like @entFirstName "; } if (authorizationSearch.LastName != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "entLastName like @entLastName "; } if (authorizationSearch.MemberID != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "clientOtherID like @clientOtherID "; } if (authorizationSearch.AdmissionType != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "adTypeID like @adTypeID "; } if (authorizationSearch.Service != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "authServiceID like @authServiceID "; } if (authorizationSearch.AuthRefNo != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "authRefNo like @authRefNo "; } if (authorizationSearch.FTPFileName != "") { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "ftpFileName like @ftpFileName "; } if (authorizationSearch.AuthBegin != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "CONVERT(DATE, authDateFrom) >= @authDateFrom "; } if (authorizationSearch.UpdatedFrom != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "CONVERT(DATE, rowUpdated) >= @updatedFrom "; } if (authorizationSearch.AuthEnd != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "CONVERT(DATE, authDateTo) <= @authDateTo "; } if (authorizationSearch.UpdatedTo != null) { if (!isNoFilter) { filterString += "AND "; } isNoFilter = false; filterString += "CONVERT(DATE, rowUpdated) <= @updatedTo "; } int indexRowStart = paging.PageSize * (paging.PageNumber - 1) + 1; int indexRowEnd = indexRowStart + paging.PageSize - 1; string pagingString = $"WHERE RowNum >= ${indexRowStart} AND RowNum <= ${indexRowEnd}"; string queryString = isNoFilter ? selectString + ") as PAGING " + pagingString : selectString + filterString + ") as PAGING " + pagingString; return(queryString); }
public SqlCommand EmbedParameters(SqlCommand command, AuthorizationSearch authorizationSearch) { if (authorizationSearch.Jurisdiction != "") { command.Parameters.AddWithValue("@jurisdictionID", authorizationSearch.Jurisdiction); } if (authorizationSearch.AgencyID != "") { command.Parameters.AddWithValue("@agencyID", authorizationSearch.AgencyID + "%"); } if (authorizationSearch.FirstName != "") { command.Parameters.AddWithValue("@entFirstName", authorizationSearch.FirstName + "%"); } if (authorizationSearch.LastName != "") { command.Parameters.AddWithValue("@entLastName", authorizationSearch.LastName + "%"); } if (authorizationSearch.MemberID != "") { command.Parameters.AddWithValue("@clientOtherID", authorizationSearch.MemberID + "%"); } if (authorizationSearch.AdmissionType != "") { command.Parameters.AddWithValue("@adTypeID", authorizationSearch.AdmissionType + "%"); } if (authorizationSearch.Service != "") { command.Parameters.AddWithValue("@authServiceID", authorizationSearch.Service + "%"); } if (authorizationSearch.AuthRefNo != "") { command.Parameters.AddWithValue("@authRefNo", authorizationSearch.AuthRefNo + "%"); } if (authorizationSearch.FTPFileName != "") { command.Parameters.AddWithValue("@ftpFileName", authorizationSearch.FTPFileName + "%"); } if (authorizationSearch.AuthBegin != null) { command.Parameters.Add("@authDateFrom", SqlDbType.Date).Value = ((DateTime)(authorizationSearch.AuthBegin)).Date; } if (authorizationSearch.UpdatedFrom != null) { command.Parameters.Add("@updatedFrom", SqlDbType.Date).Value = ((DateTime)(authorizationSearch.UpdatedFrom)).Date; } if (authorizationSearch.AuthEnd != null) { command.Parameters.Add("@authDateTo", SqlDbType.Date).Value = ((DateTime)(authorizationSearch.AuthEnd)).Date; } if (authorizationSearch.UpdatedTo != null) { command.Parameters.Add("@updatedTo", SqlDbType.Date).Value = ((DateTime)(authorizationSearch.UpdatedTo)).Date; } return(command); }
public List <AuthorizationResult> GetAuthorizationResults(Connections connections, AuthorizationSearch authorizationSearch, Paging paging) { QueryWarehouseAuthorizations _query = new QueryWarehouseAuthorizations(); string connectionString = $"Server={connections.ServerName};Database={connections.DatabaseName};User Id={connections.DatabaseUsername};Password={connections.DatabasePassword};"; string queryString = _query.CrateQueryStringAuthorization(authorizationSearch, paging); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); command = _query.EmbedParameters(command, authorizationSearch); try { List <AuthorizationResult> results = new List <AuthorizationResult>(); connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { AuthorizationResult authorizationResult = new AuthorizationResult(); authorizationResult.SetValuesFromReader(reader); results.Add(authorizationResult); } reader.Close(); return(results); } catch (Exception ex) { connection.Close(); Console.WriteLine(ex.ToString()); return(null); } } }