public IHttpActionResult GetAllGroupedSales(FilterSales filter)
{
string[] allowedRoles = { "RDSLS" };
string[] superRoles = { "RDSLSALL" };
bool hasSuperRight = HasRight(superRoles);
bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" });
if (HasRight(allowedRoles) || hasSuperRight)
{
var repository = new RepositorySales();
if (filter.ServiceId.HasValue)
{
this.ServiceId = filter.ServiceId.Value;
this.IsFinanceFile = true;
}
if (!File.Exists(this.MapperFilePath))
{
this.IsFinanceFile = false;
}
var response = repository.GetAllGroupedSales(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsSalesManager || IsRep, IsSalesDirector, displayPatientName, mapperFilePath: this.MapperFilePath);
return(Ok <DataResponse <EntityList <GroupedSales> > >(response));
}
else
{
return(Ok <DataResponse>(null));
}
}
public IHttpActionResult GetByFilter(FilterSales filter)
{
string[] allowedRoles = { "RDSLS" };
string[] superRoles = { "RDSLSALL" };
bool hasSuperRight = HasSuperRight(superRoles);
bool displayPatientName = HasSuperRight(new string[] { "VWSLSPTNT" });
if (HasSuperRight(allowedRoles) || hasSuperRight)
{
var repository = new RepositorySales();
var response = repository.GetAllList(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsSalesManager || IsRep, IsSalesDirector, displayPatientName);
return(Ok <DataResponse <EntityList <EntitySales> > >(response));
}
else
{
return(Ok <DataResponse>(null));
}
}
public IHttpActionResult ExportGroupedSalesToExcel(FilterSales filter)
{
var response = new DataResponse <string>();
try
{
#region Sales Default Export
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport");
NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper();
string[] headers = new string[] { filter.GroupBy == 1 ? "Practice Name" : filter.GroupBy == 2 ? "Rep Name" : "Sales Team", "Sales", "Last Activity On" };
var headerRow = sheet.CreateRow(0);
//create header
for (int i = 0; i < headers.Length; i++)
{
sheet.DefaultColumnWidth = 20;
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFColor colorToFill = new XSSFColor(Color.Aqua);
style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua);
headerRow.RowStyle = style;
var cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
}
string[] allowedRoles = { "RDSLS" };
string[] superRoles = { "RDSLSALL" };
bool hasSuperRight = HasRight(superRoles);
bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" });
if (HasRight(allowedRoles) || hasSuperRight)
{
var repository = new RepositorySales();
var dataResponse = repository.GetAllGroupedSales(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath);
List <GroupedSales> salesList = dataResponse.Model.List.ToList();
int recordCount = dataResponse.Model.Pager.TotalCount;
//fill content
var rowIndex = 0;
for (int i = 0; i < recordCount; i++)
{
rowIndex++;
var row = sheet.CreateRow(rowIndex);
var objSalesItem = salesList[i];
if (objSalesItem == null)
{
continue;
}
var font = workbook.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var cell1 = row.CreateCell(0);
cell1.CellStyle = workbook.CreateCellStyle();
cell1.CellStyle.SetFont(font);
cell1.SetCellValue(string.IsNullOrEmpty(objSalesItem.KeyName) ? "Missing Information" : objSalesItem.KeyName);
var cell2 = row.CreateCell(1);
cell2.CellStyle = workbook.CreateCellStyle();
cell2.CellStyle.SetFont(font);
cell2.SetCellValue(objSalesItem.Count);
var cell3 = row.CreateCell(2);
cell3.CellStyle = workbook.CreateCellStyle();
cell3.CellStyle.SetFont(font);
cell3.SetCellValue(Convert.ToString(objSalesItem.LastActivityOn));
}
string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports");
string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory));
if (!Directory.Exists(fileUri))
{
Directory.CreateDirectory(fileUri);
}
string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now),
extension = "xlsx";
string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension));
int count = 1;
isExist:
if (File.Exists(filePath))
{
fileName = string.Format("{0}{1}{2}", fileName, count, extension);
filePath = Path.Combine(fileUri, fileName);
count++;
goto isExist;
}
using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension));
}
return(Ok <DataResponse>(response));
#endregion
}
catch (Exception ex)
{
ex.Log();
}
response.Message = "Internal Server Error";
return(Ok <DataResponse>(response));
}
public IHttpActionResult ExportAllToExcel(FilterSales filter)
{
var response = new DataResponse <string>();
try
{
if (filter.GroupBy <= 0)
{
#region Sales Default Export
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport");
NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper();
string[] columnname = filter.DynamicFilters.Where(a => a.IsVisible == true).Select(a => a.ColumnName).ToArray();
string[] headers, columns = null;
headers = columns = columnname;
columns = columns.Select(x => x.Replace("SalesTeam", "RepGroup")).ToArray();
columns = columns.Select(x => x.Replace("CollectedDate", "CollectionDate")).ToArray();
//byte[] rgb = new byte[3] { 22, 183, 223 };
//XSSFCellStyle HeaderCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//HeaderCellStyle.SetFillBackgroundColor(new XSSFColor(rgb));
var headerRow = sheet.CreateRow(0);
//headerRow.RowStyle.FillBackgroundColor= (short)ColorTranslator.ToWin32(Color.Aqua);
//create header
for (int i = 0; i < headers.Length; i++)
{
sheet.DefaultColumnWidth = 20;
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFColor colorToFill = new XSSFColor(Color.Aqua);
style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua);
headerRow.RowStyle = style;
var cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
}
string[] allowedRoles = { "RDSLS" };
string[] superRoles = { "RDSLSALL" };
bool hasSuperRight = HasRight(superRoles);
bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" });
if (HasRight(allowedRoles) || hasSuperRight)
{
var repository = new RepositorySales();
var dataResponse = repository.GetAllList(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath);
List <EntitySales> salesList = dataResponse.Model.List.ToList();
int recordCount = dataResponse.Model.Pager.TotalCount;
//fill content
var rowIndex = 0;
for (int i = 0; i < recordCount; i++)
{
rowIndex++;
var row = sheet.CreateRow(rowIndex);
for (int j = 0; j < columns.Length; j++)
{
var font = workbook.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var cell = row.CreateCell(j);
cell.CellStyle = workbook.CreateCellStyle();
cell.CellStyle.SetFont(font);
string value = null;
var objSalesItem = salesList[i];
var objStaticItem = objSalesItem.GetType().GetTypeInfo().GetProperty(columns[j]);
if (objStaticItem != null)
{
var property = salesList[i].GetType().GetRuntimeProperties().FirstOrDefault(p => string.Equals(p.Name, objStaticItem.Name, StringComparison.OrdinalIgnoreCase));
if (property != null)
{
value = Convert.ToString(property.GetValue(salesList[i], null));
}
}
else
{
var objDynamicItem = objSalesItem.ReportColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]);
if (objDynamicItem != null)
{
value = objDynamicItem.Value;
}
}
cell.SetCellValue(value);
}
bool isFinanceDataCount = salesList[i].FinanceDataRecordCount > 1;
if (isFinanceDataCount)
{
for (int k = 0; k < salesList[i].FinanceDataRecordCount; k++)
{
rowIndex++;
var financeRow = sheet.CreateRow(rowIndex);
for (int j = 0; j < columns.Length; j++)
{
var cell = financeRow.CreateCell(j);
string value = null;
var objSalesItem = salesList[i].FinanceDataList.ToList()[k];
var objStaticItem = objSalesItem.GetType().GetProperty(columns[j]);
if (objStaticItem != null)
{
value = Convert.ToString(objStaticItem.GetValue(objSalesItem, null));
}
else
{
var objDynamicItem = objSalesItem.FinanceColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]);
if (objDynamicItem != null)
{
value = objDynamicItem.Value;
}
}
cell.SetCellValue(value);
}
}
}
}
string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports");
string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory));
if (!Directory.Exists(fileUri))
{
Directory.CreateDirectory(fileUri);
}
string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now),
extension = "xlsx";
string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension));
int count = 1;
isExist:
if (File.Exists(filePath))
{
fileName = string.Format("{0}{1}{2}", fileName, count, extension);
filePath = Path.Combine(fileUri, fileName);
count++;
goto isExist;
}
using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension));
}
return(Ok <DataResponse>(response));
#endregion
}
#region Sales Grouped Report
return(ExportGroupedSalesToExcel(filter));
#endregion
}
catch (Exception ex)
{
ex.Log();
}
response.Message = "Internal Server Error";
return(Ok <DataResponse>(response));
}
