private static void ModifyReportParams(SqlParameterCollection paramCol, ProviderDetailCategories categories)
{
paramCol.AddWithValue("@affiliations", categories.Affiliations);
paramCol.AddWithValue("@certifications", categories.Certifications);
paramCol.AddWithValue("@expertise", categories.Expertise);
paramCol.AddWithValue("@languages", categories.Languages);
paramCol.AddWithValue("@licenses", categories.Licenses);
paramCol.AddWithValue("@practices", categories.Practices);
paramCol.AddWithValue("@professionalDetails", categories.Professional);
paramCol.AddWithValue("@specializations", categories.Specializations);
}
public FileStreamResult ReportSelectAll(ProviderDetailCategories categories)
{
BaseResponse response;
try
{
IUserAuthData user = _auth.GetCurrentUser();
MemoryStream stream = null;
if (user.Roles.Contains("Office Manager") || (user.Roles.Contains("SysAdmin")))
{
if (user.Roles.Contains("SysAdmin"))
{
stream = _service.ReportSelectAll(categories, 0);
}
else
{
stream = _service.ReportSelectAll(categories, user.Id);
}
}
if (stream == null)
{
response = new ErrorResponse("App resource not found.");
}
else
{
stream.Position = 0;
var contentType = "application/octet-stream";
var fileName = "ProviderReport.xlsx";
return(File(stream, contentType, fileName));
}
}
catch (Exception ex)
{
base.Logger.LogError(ex.ToString());
response = new ErrorResponse(ex.Message);
}
return(null);
}
public ActionResult <ItemResponse <Paged <ProviderReport> > > SearchProviderReportList(int pageIndex, int pageSize, string query, ProviderDetailCategories categories)
{
int code = 200;
BaseResponse response;
try
{
IUserAuthData user = _auth.GetCurrentUser();
Paged <ProviderReport> pagedItems = null;
if (user.Roles.Contains("SysAdmin") || user.Roles.Contains("Office Manager"))
{
if (user.Roles.Contains("SysAdmin"))
{
pagedItems = _service.SearchReportSelectAllPaged(pageIndex, pageSize, categories, query);
}
else
{
pagedItems = _service.SearchReportSelectPaged(pageIndex, pageSize, categories, query, user.Id);
}
}
if (pagedItems == null)
{
code = 404;
response = new ErrorResponse("App resource not found.");
}
else
{
response = new ItemResponse <Paged <ProviderReport> >()
{
Item = pagedItems
};
}
}
catch (Exception ex)
{
code = 500;
response = new ErrorResponse(ex.Message);
}
return(StatusCode(code, response));
}
public ActionResult <ItemResponse <Paged <ProviderReport> > > ReportSelectPaged(int pageIndex, int pageSize, ProviderDetailCategories categories)
{
int code = 200;
BaseResponse response = null;
try
{
IUserAuthData user = _auth.GetCurrentUser();
if (user.Roles.Contains("SysAdmin") || user.Roles.Contains("Office Manager"))
{
Paged <ProviderReport> pagedItems = null;
if (user.Roles.Contains("SysAdmin"))
{
pagedItems = _service.ReportSelectAllPaged(pageIndex, pageSize, categories);
}
else
{
pagedItems = _service.ReportSelectPaged(pageIndex, pageSize, user.Id, categories);
}
if (pagedItems == null)
{
code = 404;
response = new ErrorResponse("App resource not found.");
}
else
{
response = new ItemResponse <Paged <ProviderReport> > {
Item = pagedItems
};
}
}
else
{
code = 500;
response = new ErrorResponse("You do not meet the requirements to access this resource.");
}
}
catch (Exception ex)
{
base.Logger.LogError(ex.ToString());
code = 500;
response = new ErrorResponse(ex.Message);
}
return(StatusCode(code, response));
}
public ActionResult <ItemsResponse <List <ProviderReport> > > ReportSelectAllPdf(ProviderDetailCategories categories)
{
int code = 200;
BaseResponse response = null;
try
{
IUserAuthData user = _auth.GetCurrentUser();
List <ProviderReport> list = null;
if (user.Roles.Contains("Office Manager") || (user.Roles.Contains("SysAdmin")))
{
if (user.Roles.Contains("SysAdmin"))
{
list = _service.ReportSelectAllPdf(categories, 0);
}
else
{
list = _service.ReportSelectAllPdf(categories, user.Id);
}
if (list == null)
{
code = 404;
response = new ErrorResponse("App resource not found.");
}
else
{
response = new ItemsResponse <ProviderReport>()
{
Items = list
};
}
}
}
catch (Exception ex)
{
base.Logger.LogError(ex.ToString());
code = 500;
response = new ErrorResponse(ex.Message);
}
return(StatusCode(code, response));
}
public MemoryStream ReportSelectAll(ProviderDetailCategories categories, int userId)
{
string procName;
if (userId == 0)
{
procName = "[dbo].[ProvidersReport_SelectAll_Details]";
}
else
{
procName = "[dbo].[ProvidersReport_SelectAll_DetailsV2]";
}
List <ProviderReport> list = null;
_data.ExecuteCmd(procName, paramCol =>
{
if (userId == 0)
{
ModifyReportParams(paramCol, categories);
}
else
{
ModifyReportParamsV2(paramCol, categories, userId);
}
}, (reader, set) =>
{
ProviderReport provider = HydrateProvidersReport(reader, out int lastIndex);
if (list == null)
{
list = new List <ProviderReport>();
}
list.Add(provider);
});
//this dict will keep track of the longest count across each category
Dictionary <string, int> countDict = new Dictionary <string, int>();
bool catIsSelected;
//filter categories by truthy, and add an entries to the dictionary "countDict"
PropertyInfo[] truthyCategories = categories.GetType()
.GetProperties()
.Where(prop =>
{
bool.TryParse(prop.GetValue(categories).ToString(), out catIsSelected);
return(catIsSelected);
}).ToArray();
foreach (PropertyInfo propertyInfo in truthyCategories)
{
countDict.Add(propertyInfo.Name, 0);
}
//get all properties of type List that are truthy within categories, and store them in arrayProperties for future access
Type type = typeof(ProviderReport);
PropertyInfo[] allProperties = type.GetProperties();
PropertyInfo[] arrayProperties = allProperties
.Where(property => property.PropertyType.IsGenericType &&
countDict.ContainsKey(property.Name)).ToArray();
//iterate over each provider and update counts in dict
foreach (ProviderReport providerReport in list)
{
foreach (PropertyInfo property in arrayProperties)
{
//nullcheck current prop
ICollection propCollection = GetPropValue <ICollection>(providerReport, property.Name);
if (propCollection != null)
{
//compare this particular instanced count to the count in the dict
if (countDict[property.Name] < propCollection.Count)
{
countDict[property.Name] = propCollection.Count;
}
}
}
}
var stream = new MemoryStream();
using (var package = new ExcelPackage(stream))
{
#region concatenated details worksheet
//create new worksheet with base info
ExcelWorksheet concatenated = package.Workbook.Worksheets.Add("Concatenated Details");
ModifyBaseExcelHeaders(concatenated, out int concatenatedHeaderIndex);
if (categories.Professional)
{
concatenated.Cells[1, concatenatedHeaderIndex++].Value = "NPI";
concatenated.Cells[1, concatenatedHeaderIndex++].Value = "Genders Accepted";
}
//sort arrayProperties to ensure consistent excel column ordering between worksheets
PropertyInfo[] sortedArrayProperties = arrayProperties.OrderBy(p => p.Name).ToArray();
//add cell to worksheet with value of property name
for (int i = 0; i < sortedArrayProperties.Length; i++)
{
concatenated.Cells[1, concatenatedHeaderIndex++].Value = sortedArrayProperties[i].Name;
}
//call this before filling concatenated w/s
using (var r = concatenated.Cells[1, 1, 1, concatenatedHeaderIndex - 1])
{
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
//fill w/s with data
int concatenatedRowIndex = 2;
foreach (ProviderReport providerReport in list)
{
PopulateBaseExcelCells(concatenated, providerReport, concatenatedRowIndex, out int concatenatedColIndex);
if (categories.Professional)
{
concatenated.Cells[concatenatedRowIndex, concatenatedColIndex].Value
= providerReport.Professional?.NPI;
concatenatedColIndex++;
if (providerReport.Professional?.GendersAccepted != null)
{
concatenated.Cells[concatenatedRowIndex, concatenatedColIndex].Value
= providerReport.Professional.GendersAccepted;
}
concatenatedColIndex++;
}
for (int i = 0; i < sortedArrayProperties.Length; i++)
{
ICollection propCollection = GetPropValue <ICollection>(providerReport, sortedArrayProperties[i].Name);
if (propCollection != null)
{
string concatProp = "";
foreach (var prop in propCollection)
{
if (concatProp != "")
{
concatProp += $"; ";
}
string propName = "";
if (sortedArrayProperties[i].Name == "Licenses")
{
propName = GetPropValue <string>(prop, "State");
}
else
{
propName = GetPropValue <string>(prop, "Name");
}
concatProp += $"{propName}";
}
concatenated.Cells[concatenatedRowIndex, concatenatedColIndex++].Value
= concatProp;
}
else
{
concatenatedColIndex++;
}
}
concatenatedRowIndex++;
}
concatenated.Cells.AutoFitColumns(0, 30);
#endregion
#region expanded details worksheet
ExcelWorksheet expanded = package.Workbook.Worksheets.Add("Expanded Details");
ModifyBaseExcelHeaders(expanded, out int expandedHeaderIndex);
if (categories.Professional)
{
expanded.Cells[1, expandedHeaderIndex++].Value = "NPI";
expanded.Column(expandedHeaderIndex).Style.Numberformat.Format = "#";
expanded.Cells[1, expandedHeaderIndex++].Value = "Genders Accepted";
}
for (int i = 0; i < sortedArrayProperties.Length; i++)
{
for (int j = 0; j < countDict[sortedArrayProperties[i].Name]; j++)
{
string truncatedName = sortedArrayProperties[i].Name.EndsWith("s")
? sortedArrayProperties[i].Name.Substring(0, sortedArrayProperties[i].Name.Length - 1)
: sortedArrayProperties[i].Name;
expanded.Cells[1, expandedHeaderIndex++].Value = $"{truncatedName}{j + 1}";
}
}
using (var r = expanded.Cells[1, 1, 1, expandedHeaderIndex - 1])
{
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
//fill w/s with data
int expandedRowIndex = 2;
foreach (ProviderReport providerReport in list)
{
PopulateBaseExcelCells(expanded, providerReport, expandedRowIndex, out int expandedColIndex);
if (categories.Professional)
{
expanded.Cells[concatenatedRowIndex, expandedColIndex].Value
= providerReport.Professional?.NPI;
expandedColIndex++;
if (providerReport.Professional?.GendersAccepted != null)
{
expanded.Cells[concatenatedRowIndex, expandedColIndex].Value
= providerReport.Professional.GendersAccepted;
}
expandedColIndex++;
}
for (int i = 0; i < sortedArrayProperties.Length; i++)
{
ICollection propCollection = GetPropValue <ICollection>(providerReport, sortedArrayProperties[i].Name);
int dictCount = countDict[sortedArrayProperties[i].Name];
if (propCollection != null)
{
foreach (var prop in propCollection)
{
string propName = "";
if (sortedArrayProperties[i].Name == "Licenses")
{
propName = GetPropValue <string>(prop, "State");
}
else
{
propName = GetPropValue <string>(prop, "Name");
}
expanded.Cells[expandedRowIndex, expandedColIndex++].Value = propName;
}
int propCount = propCollection.Count;
if (propCount < dictCount)
{
expandedColIndex += dictCount - propCount;
}
}
else
{
expandedColIndex += dictCount;
}
}
expandedRowIndex++;
}
expanded.Cells.AutoFitColumns(0, 40);
#endregion
#region individual detail worksheets
Dictionary <string, ExcelWorksheet> worksheets = new Dictionary <string, ExcelWorksheet>();
//creates appropriate worksheet detail names
foreach (PropertyInfo propertyInfo in truthyCategories)
{
string friendlyWsName = propertyInfo.Name.EndsWith("s")
? propertyInfo.Name.Substring(0, propertyInfo.Name.Length - 1)
: propertyInfo.Name;
worksheets.Add(propertyInfo.Name, package.Workbook.Worksheets.Add(friendlyWsName + " Details"));
}
//populate all of the worksheets with shared headers
foreach (var kvp in worksheets)
{
//ExcelWorksheet sharedWs = GetPropValue<ExcelWorksheet>(worksheets, key);
ExcelWorksheet sharedWs = kvp.Value;
int sharedHeaderIndex = 1;
sharedWs.Cells[1, sharedHeaderIndex++].Value = "ID";
sharedWs.Cells[1, sharedHeaderIndex++].Value = "First Name";
sharedWs.Cells[1, sharedHeaderIndex++].Value = "Last Name";
if (kvp.Key == "Professional")
{
sharedWs.Cells[1, sharedHeaderIndex++].Value = "NPI";
sharedWs.Cells[1, sharedHeaderIndex++].Value = "Genders Accepted";
}
else
{
string truncatedName = kvp.Key.EndsWith("s")
? kvp.Key.Substring(0, kvp.Key.Length - 1)
: kvp.Key;
sharedWs.Cells[1, sharedHeaderIndex++].Value = $"{truncatedName}";
}
using (var r = sharedWs.Cells[1, 1, 1, sharedHeaderIndex - 1])
{
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
//add data to w/s
int sharedWsRowIndex = 2;
foreach (ProviderReport providerReport in list)
{
if (kvp.Key == "Professional")
{
PopulateSharedBaseExcelCells(sharedWs, providerReport, sharedWsRowIndex, out int sharedColIndex);
sharedWs.Cells[sharedWsRowIndex, sharedColIndex].Value = providerReport.Professional?.NPI;
sharedColIndex++;
if (providerReport.Professional?.GendersAccepted != null)
{
sharedWs.Cells[sharedWsRowIndex, sharedColIndex].Value = providerReport.Professional.GendersAccepted;
}
sharedColIndex++;
sharedWsRowIndex++;
}
else
{
IList prop = GetPropValue <IList>(providerReport, kvp.Key);
if (prop != null)
{
for (int i = 0; i < prop.Count; i++)
{
PopulateSharedBaseExcelCells(sharedWs, providerReport, sharedWsRowIndex, out int sharedColIndex);
if (kvp.Key == "Licenses")
{
sharedWs.Cells[sharedWsRowIndex, sharedColIndex++].Value = GetPropValue <string>(prop[i], "State");
}
else
{
sharedWs.Cells[sharedWsRowIndex, sharedColIndex++].Value = GetPropValue <string>(prop[i], "Name");
if (kvp.Key == "Specializations")
{
bool isPrimarySpec = GetStructValue <bool>(prop[i], "IsPrimary");
if (isPrimarySpec)
{
sharedWs.Cells[sharedWsRowIndex, sharedColIndex - 1].Style.Font.Bold = true;
}
}
}
sharedWsRowIndex++;
}
}
}
}
sharedWs.Cells.AutoFitColumns(0);
}
#endregion
// set some document properties
package.Workbook.Properties.Title = "Providers Report";
package.Workbook.Properties.Author = "Scrubs Data";
// save new workbook in the output directory
package.Save();
}
return(stream);
}
