Untitled

using ClosedXML.Excel;
using Microsoft.Extensions.Options;
using Notenetic.Model.ViewModels;
using Notenetic.Service.Data.Model.Common;
using Notenetic.Service.Services.Contract;
using System.Collections.Generic;
using System.IO;


namespace Notenetic.Service.Services
{
    [ScopedService]
    public class ExportToExcelService : IExportToExcelService
    {
        private XLColor _headerColor = XLColor.FromArgb(0xFDEEDB);
        
        private readonly XLColor _greenColor = XLColor.Green;
        private readonly XLColor _redColor = XLColor.Red;
        private readonly XLColor _yellowColor = XLColor.Yellow;
        private readonly XLColor _greyColor = XLColor.Gray;
        private readonly IS3Service _s3Service;
        private readonly AppSettings _appsettings;

        public ExportToExcelService(IS3Service s3Service,
            IOptions<AppSettings> appsettings)
        {
            _s3Service = s3Service;
            _appsettings = appsettings.Value;
        }

        public async Task<string> ExportToPdf(List<ComplianceReportClientDetailsModel> complianceReport)
        {
            using var workbook = new XLWorkbook();
            workbook.Author = "Notenetic";
            workbook.Style.Font.FontName = "Manrope";
            var worksheet = workbook.Worksheets.Add("Compliance");
            var columnNames = AddFirstRow(worksheet, complianceReport.First());

            var index = columnNames.RowNumber();

            foreach (var rpt in complianceReport)
            {
                index++;
                AddRow(worksheet, index, rpt);
            }

            using var stream = new MemoryStream();
            workbook.SaveAs(stream, new SaveOptions { EvaluateFormulasBeforeSaving = false, GenerateCalculationChain = false, ValidatePackage = false });
            return await _s3Service.PutStreamInS3Bucket(_appsettings.S3Bucket, stream, $"{_appsettings.S3TempFilePath}/ComplianceReport_{Guid.NewGuid()}.xlsx");
        }

        public IXLRow AddFirstRow(IXLWorksheet workSheet, ComplianceReportClientDetailsModel rpt)
        {
            var startAddress = workSheet.FirstCell().Address;
            var row = workSheet.Row(startAddress.RowNumber);

            row.Cell(startAddress.ColumnNumber).Value = "Name";
            row.Cell(startAddress.ColumnNumber).Style.Fill.BackgroundColor = _headerColor;

            row.Cell(startAddress.ColumnNumber + 1).Value = "Sites";
            row.Cell(startAddress.ColumnNumber + 1).Style.Fill.BackgroundColor = _headerColor;

            row.Cell(startAddress.ColumnNumber + 2).Value = "Payer";
            row.Cell(startAddress.ColumnNumber + 2).Style.Fill.BackgroundColor = _headerColor;

            row.Cell(startAddress.ColumnNumber + 3).Value = "Status";
            row.Cell(startAddress.ColumnNumber + 3).Style.Fill.BackgroundColor = _headerColor;

            row.Cell(startAddress.ColumnNumber + 4).Value = "UpcomingDate";
            row.Cell(startAddress.ColumnNumber + 4).Style.Fill.BackgroundColor = _headerColor;

            row.Cell(startAddress.ColumnNumber + 5).Value = "NextDueDate";
            row.Cell(startAddress.ColumnNumber + 5).Style.Fill.BackgroundColor = _headerColor;

            row.Cell(startAddress.ColumnNumber + 6).Value = "CompletedDate";
            row.Cell(startAddress.ColumnNumber + 6).Style.Fill.BackgroundColor = _headerColor;

            var index = 1;
            foreach (var compliances in rpt.complianceReport)
            {
                row.Cell(startAddress.ColumnNumber + index + 6).Value = compliances.complianceName;
                row.Cell(startAddress.ColumnNumber + index + 6).Style.Fill.BackgroundColor = _headerColor;
                index++;
            }

            return row;
        }

        public IXLRow AddRow(IXLWorksheet workSheet, int rowIndex, ComplianceReportClientDetailsModel rpt)
        {
            var row = workSheet.Row(rowIndex);
            var address = workSheet.FirstCell().Address;
            row.Cell(address.ColumnNumber).Value = $"{rpt.lName}, {rpt.fName} ({rpt.Age})";
            row.Cell(address.ColumnNumber + 1).Value = rpt.siteName;
            row.Cell(address.ColumnNumber + 2).Value = rpt.payerName;
            row.Cell(address.ColumnNumber + 3).Value = rpt.status;

            DateTime? upcomingDate = null;
            DateTime? nextDueDate = null;
            DateTime? completedDate = null;
            foreach (var compliance in rpt.complianceReport)
            {
                if (compliance.isCompleted && compliance.isRecurring)
                {
                    nextDueDate = compliance.nextDueDate;
                    completedDate = compliance.completedDate;
                    break; 
                }
                else if (compliance.isCompleted)
                {
                    completedDate = DateTime.Now.Date;
                }
                else if (compliance.isRecurring)
                {
                    nextDueDate = compliance.nextDueDate;
                }
                
            }
            row.Cell(address.ColumnNumber + 4).Value = upcomingDate;
            row.Cell(address.ColumnNumber + 5).Value = nextDueDate;
            row.Cell(address.ColumnNumber + 6).Value = completedDate;

            var index = 1;
            foreach (var compliances in rpt.complianceReport)
            {
                var complianceProps = GetComplianceProps(compliances);
                row.Cell(address.ColumnNumber + index + 6).Value = complianceProps.status;
                row.Cell(address.ColumnNumber + index + 6).Style.Fill.SetBackgroundColor(complianceProps.color);
                index++;
            }

            return row;
        }



        private (string status, XLColor color) GetComplianceProps(ComplianceReportDetails complianceReport)

        {
            Console.WriteLine(complianceReport);
            if (complianceReport.isCompleted && complianceReport.isRecurring)
            {
                return ("Complete", _greenColor);
            }

            if (complianceReport.isCompleted)
            {
                return ("Complete", _greenColor);
            }

            if (!complianceReport.isApplicable)
            {
                return ("NA", _greenColor);
            }


            if (complianceReport.isRestrictive)
            {
                return ("Pending", _redColor);
            }

            return ("Pending", _yellowColor);
        }
    }
}