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);
}
}
}
