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