using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc;
using ODCWeb.DataAccess.Data;
using ODCWeb.DataAccess.Repository.IRepository;
using ODCWeb.Models;
using Syncfusion.Drawing;
using Syncfusion.EJ2.Charts;
using Syncfusion.EJ2.Notifications;
using Syncfusion.EJ2.Spreadsheet;
using Syncfusion.XlsIO;
using Syncfusion.XlsIO.Implementation;
using System.Drawing;
using System.Threading.Tasks;

namespace ODCWeb.Areas.Staff.Controllers
{
    [Area("Staff")]

    public class ExcelController : Controller
    {
        private readonly ApplicationDbContext _db;
        private readonly IUnitOfWork _unitOfWork;
        private readonly UserManager<ApplicationUser> userManager;

        public ExcelController(ApplicationDbContext applicationDbContext, UserManager<ApplicationUser> userManager, IUnitOfWork unitOfWork)
        {
            _db = applicationDbContext;
            _unitOfWork = unitOfWork;
            this.userManager = userManager;
        }
        [Authorize(Roles = "Admin,Employee,Customer")]
       
        public IActionResult Index()
        {
            return View();
        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        public IActionResult DemoDocument()
        {
            /*try catch
            {
            must use in the end
            }*/
            //////////////////////////////
            using(ExcelEngine engine = new ExcelEngine()) 
            {
                IApplication application = engine.Excel;
                application.DefaultVersion = ExcelVersion.Xlsx;

                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];


                /*FileStream imageStream = new FileStream(location + "/wwwroot/template_deskapp/src/images/Odlan_Logo.png", FileMode.Open, FileAccess.Read);
                IPictureShape shape = worksheet.Pictures.AddPicture(1, 1, imageStream, 20, 20);
*/
                worksheet.IsGridLinesVisible = false;

                worksheet.Range["A4"].Text = "New testing";
               

                worksheet.Range["A3:A5"].CellStyle.Font.Bold = true;

                worksheet.Range["D1:E1"].CellStyle.Font.Size = 35;

                worksheet.Range["A7"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;

                IHyperLink hyperLink = worksheet.HyperLinks.Add(worksheet.Range["A13"]);
                hyperLink.Type = ExcelHyperLinkType.Url;
                hyperLink.Address = "www.facebook.com";
                hyperLink.ScreenTip = "Go to facebook";

                worksheet.Range["A15:B15"].Merge();
                worksheet.Range["A16:B16"].Merge();
                worksheet.Range["A17:B17"].Merge();
                worksheet.Range["A18:B18"].Merge();

                worksheet.Range[15,1].Number = 34;
                worksheet.Range["A16"].Text = "New testing";

                worksheet.Range["A15"].NumberFormat = "$.00";

                application.EnableIncrementalFormula = true;

                worksheet.Range["A18"].Formula = "=A15+A15";

                worksheet.Range["A18"].RowHeight = 50;

                //////////////saving
                ///

                MemoryStream memoryStream = new MemoryStream();
                workbook.SaveAs(memoryStream);

                memoryStream.Position = 0;

                FileStreamResult fileStreamResult = new FileStreamResult(memoryStream, "application/Excel");
                fileStreamResult.FileDownloadName = "tester.xlsx";


                return fileStreamResult;


            }
            //////////////////////////////
        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        [HttpGet]
        public async Task<IActionResult> ProjectReplicaExcel(int? id)
        {
            ODCProject project = await _unitOfWork.ODCProject.GetAsync(u => u.Id == id);
            using (ExcelEngine engine = new ExcelEngine())
            {
                IApplication application = engine.Excel;
                application.DefaultVersion = ExcelVersion.Xlsx;

                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];

                workbook.CalculationOptions.CalculationMode = ExcelCalculationMode.Automatic;
                
                //counters
                int floor_r = 10;
                int action_r = 15;
                int task_r = 18;

                //constants
                int floor_c = 2;
                int action_c = 2;
                int task_c = 2;
                int prop_c = 4; //till 14 (counters)
                int mw_c = 16;
                int lr_c = 17;
                int lc_c = 18;
                int mc_c = 19;
                int tot_c = 20;
                int mat_c = 22; //till 34 (counters)

                worksheet.Range[2, 2].Text = project.Name;
                worksheet.Range[2, 2].CellStyle.Font.Bold = true;
                worksheet.Range[2, 2].CellStyle.Font.Size = 26;

                worksheet.Range[2, 2, 2, 3].Merge();
                worksheet.Range[2, 2].RowHeight = 70;
                worksheet.Range[3, 2, 3, 3].Merge();
                worksheet.Range[3, 3].RowHeight = 30;

                worksheet.Range[task_r, 4,task_r,20].ColumnWidth = 15;
                worksheet.Range[task_r, 22,task_r,34].ColumnWidth = 30;
                worksheet.Range[1, 1, 1000, 1000].WrapText = true;
                application.EnableIncrementalFormula = true;
                
                List<int> f_rows = new List<int>();
                int temp4 = 2;

                IEnumerable<Floor> floors = await _unitOfWork.Floor.WhereAsync(u => u.ODCProjectId == id);
                if (floors.Any())
                {
                    List<int> a_rows = new List<int>();
                    foreach (var floor in floors)
                    {
                        worksheet.Range[floor_r-2, 1, floor_r+3, 34].CellStyle.FillBackgroundRGB = Syncfusion.Drawing.Color.FloralWhite;
                        int temp3 = floor_r;
                        worksheet.IsGridLinesVisible = false;
                        worksheet.Range[floor_r, floor_c].ColumnWidth = 30;
                        worksheet.Range[floor_r,floor_c].Text = floor.Name;
                        worksheet.Range[floor_r, floor_c].CellStyle.Font.Bold = true;
                        worksheet.Range[floor_r, floor_c].CellStyle.Font.Size = 20;
                        worksheet.Range[floor_r,floor_c,1000,1000].WrapText = true;

                        a_rows.Clear();

                        IEnumerable<FloorAction> actions = await _unitOfWork.Action.WhereAsync(u => u.FloorId == floor.FloorId);
                        if (actions.Any())
                        {
                            List<int> rows = new List<int>();   //for adding all total costs
                            foreach (var action in actions)
                            {

                                int temp0 = action_r;
                                worksheet.Range[action_r, action_c].Text = action.Name;
                                worksheet.Range[action_r, action_c].CellStyle.Font.Bold = true;
                                worksheet.Range[action_r, action_c].CellStyle.Font.Size = 14;

                                rows.Clear();

                                IEnumerable<ActionTask> tasks = await _unitOfWork.Task.WhereAsync(u => u.ActionId == action.ActionId);
                                if (tasks.Any())
                                {
                                    int temp1 = task_r;
                                    foreach (var task in tasks)
                                    {
                                        rows.Add(task_r + 1);

                                        worksheet.Range[task_r, task_c].Text = task.Name;
                                        worksheet.Range[task_r, task_c].CellStyle.Font.Size = 11;

                                        //////////////properties/////////////
                                        IEnumerable<Property> properties = await _unitOfWork.Property.WhereAsync(u => u.TaskId == task.TaskId);
                                        if (properties.Any())
                                        {
                                            foreach (var property in properties)
                                            {
                                                worksheet.Range[task_r, prop_c].Text = property.Name;
                                                worksheet.Range[task_r + 1, prop_c].Number = (double)property.Value;
                                                ///////////////////change color of visible properties////////////
                                                if (property.Visible == true)
                                                {
                                                    worksheet.Range[task_r + 1, prop_c].CellStyle.FillBackgroundRGB = Syncfusion.Drawing.Color.LightPink;
                                                }
                                                /////////////////////////////////////////////////////////////////
                                                prop_c = prop_c + 1;
                                            }
                                            prop_c = 4;
                                            foreach (var prop in properties)
                                            {
                                                /////////////////get formulas of properties/////////////////////
                                                string dbFormula = "";
                                                ActionTask f_task = await _unitOfWork.Task.GetAsync(u => u.TaskId == prop.TaskId);
                                                if (f_task != null)
                                                {
                                                    string last_c = f_task.Name[f_task.Name.Length - 1].ToString();
                                                    string taskName = "";
                                                    if (int.TryParse(last_c, out _) == true)
                                                    {
                                                        taskName = f_task.Name.Substring(0, f_task.Name.Length - 2);
                                                    }
                                                    else
                                                    {
                                                        taskName = f_task.Name;
                                                    }
                                                    DataTask f_task_t =await _unitOfWork.DataTask.GetAsync(u => u.Name == taskName);
                                                    if (f_task_t != null)
                                                    {
                                                        IEnumerable<TemplateProperty> t_properties = await _unitOfWork.TemplateProperty.WhereAsync(u => u.DataTaskId == f_task_t.DataTaskId);
                                                        if (t_properties.Any())
                                                        {
                                                            List<TemplateProperty> t_properties_l = new List<TemplateProperty>();
                                                            foreach (var nh in t_properties)
                                                            {
                                                                t_properties_l.Add(nh);
                                                            }
                                                            /*  TemplateProperty t_property =  t_properties.FirstOrDefault(u => u.Name == prop.Name);*/
                                                            TemplateProperty t_property = await FindAsync2(prop.Name, t_properties_l);
                                                            if (t_property != null)
                                                            {
                                                                TemplateFormulaP t_formula_p =await _unitOfWork.TemplateFormulaP.GetAsync(u => u.TemplatePId == t_property.TemplatePId);
                                                                if (t_formula_p != null)
                                                                {
                                                                    if (t_formula_p.Formula != "" && t_formula_p.Formula != null)
                                                                    {
                                                                        dbFormula = t_formula_p.Formula.ToString();
                                                                        string tempFormula = dbFormula;
                                                                        if (dbFormula != "" && dbFormula != null)
                                                                        {
                                                                            //////////////////all working////////////////////
                                                                            List<string> propertyIds = new List<string>();
                                                                            foreach (var t_item in t_properties)
                                                                            {
                                                                                if (t_item.Name != t_property.Name)
                                                                                {
                                                                                    propertyIds.Add(t_item.TemplatePId.ToString());
                                                                                }
                                                                            }
                                                                            if (propertyIds.Any())
                                                                            {
                                                                                int difference = 0;
                                                                                foreach (var tid in propertyIds)
                                                                                {
                                                                                    if (dbFormula.Contains(tid))
                                                                                    {
                                                                                        int splace = dbFormula.IndexOf(tid) - difference;
                                                                                        int elength = tempFormula.Length - splace;
                                                                                        if (splace > -1 && elength > 0)
                                                                                        {
                                                                                            TemplateProperty name1 =await  _unitOfWork.TemplateProperty.GetAsync(u => u.TemplatePId.ToString() == tid);
                                                                                            string name = name1.Name;
                                                                                            IEnumerable<Property> properties1 = await _unitOfWork.Property.WhereAsync(u => u.TaskId == prop.TaskId);
                                                                                            
                                                                                            List<Property> propertiess = new List<Property>();
                                                                                            foreach(var bn in properties1)
                                                                                            {
                                                                                                propertiess.Add(bn);
                                                                                            }
/*                                                                                            Property t = propertiess.Find(u => u.Name == name);*/
                                                                                            Property t = await FindAsync(name, propertiess);
                                                                                            string value = "";
                                                                                            if (t != null)
                                                                                            {
                                                                                                value = t.Name.ToString();
                                                                                            }
                                                                                            else
                                                                                            {
                                                                                                value = "0";
                                                                                            }

                                                                                            string to_be_replaced = tempFormula.Substring(splace, elength);
                                                                                            tempFormula = tempFormula.Substring(0, splace) + to_be_replaced.Replace(tid, value);
                                                                                            difference = difference + tid.Length - value.Length;
                                                                                        }

                                                                                    }
                                                                                }
                                                                                /////////////////getting cell addresses and replaces with names in formula////////
                                                                                for (int i = prop_c;i<15;i++)
                                                                                {
                                                                                    string p_name = worksheet.Range[task_r,i].Value.ToString();
                                                                                    string p_v_address = worksheet.Range[task_r + 1, i].Address.ToString();
                                                                                    if (p_name != null && p_name != "")
                                                                                    {
                                                                                        if (tempFormula.Contains(p_name))
                                                                                        {
                                                                                            tempFormula = tempFormula.Replace(p_name, p_v_address);
                                                                                        }
                                                                                    }
                                                                                }
                                                                                for(int c=prop_c;c<15;c++)
                                                                                {
                                                                                    if (worksheet.Range[task_r,c].Text == prop.Name)
                                                                                    {
                                                                                        try
                                                                                        {
                                                                                            worksheet.Range[task_r + 1, c].Formula = tempFormula;
                                                                                            break;
                                                                                        }
                                                                                        catch (Exception e)
                                                                                        {
                                                                                            ///do nothing
                                                                                        }
                                                                                    }
                                                                                }
                                                                                //////////////////////////////////////////////////////////////////////////////////
                                                                            }
                                                                            /////////////////////////////////////////////////
                                                                        }
                                                                    }
                                                                    
                                                                }
                                                            }
                                                        }
                                                    }


                                                    ////////////////////////////////////////////////////////////////

                                                }
                                            }
                                        }
                                        //////////////materials//////////////
                                        IEnumerable<Material> materials = await _unitOfWork.Material.WhereAsync(u => u.TaskId == task.TaskId);
                                        int temp2 = 34;
                                        if (materials.Any())
                                        {
                                            foreach (var material in materials)
                                            {
                                                worksheet.Range[task_r, mat_c].Text = material.Name;
                                                worksheet.Range[task_r + 1, mat_c].Number = (double)material.Qty;
                                                worksheet.Range[task_r + 2, mat_c].Number = (double)material.Unit_Cost;
                                                worksheet.Range[task_r + 3, mat_c].Formula = worksheet.Range[task_r + 1, mat_c].Address + "*" + worksheet.Range[task_r + 2, mat_c].Address;
                                                ////////////////////formnulas in quantites of materials////
                                                ActionTask fm_task =await _unitOfWork.Task.GetAsync(u => u.TaskId == material.TaskId);
                                                if (fm_task != null)
                                                {
                                                    string last_c = fm_task.Name[fm_task.Name.Length - 1].ToString();
                                                    string taskNameM = "";
                                                    if (int.TryParse(last_c, out _) == true)
                                                    {
                                                        taskNameM = fm_task.Name.Substring(0, fm_task.Name.Length - 2);
                                                    }
                                                    else
                                                    {
                                                        taskNameM = fm_task.Name;
                                                    }
                                                    if (taskNameM != "" && taskNameM != null)
                                                    {
                                                        DataTask fmd_task = await _unitOfWork.DataTask.GetAsync(u => u.Name == taskNameM);
                                                        IEnumerable<TemplateMaterial> t_materials = await _unitOfWork.TemplateMaterial.WhereAsync(u => u.DataTaskId == fmd_task.DataTaskId);
                                                        if (t_materials != null)
                                                        {
                                                            List<TemplateMaterial> t_material_l = new List<TemplateMaterial>();
                                                            foreach (var gh in t_materials)
                                                            {
                                                                t_material_l.Add(gh);
                                                            }
                                                            TemplateMaterial t_material =await FindAsync3(material.Name, t_material_l);
                                                            if (t_material != null)
                                                            {
                                                                string tempFormulaM = "";
                                                                string dbFormulaM = "";
                                                                TemplateFormula m_formula = await _unitOfWork.TemplateFormula.GetAsync(u => u.TemplateMId == t_material.TemplateMId);
                                                                if (m_formula != null)
                                                                {
                                                                    if (m_formula.Formula != null && m_formula.Formula != "")
                                                                    {
                                                                        dbFormulaM = m_formula.Formula;
                                                                        tempFormulaM = dbFormulaM;
                                                                        IEnumerable<TemplateProperty> tm_properties =await _unitOfWork.TemplateProperty.WhereAsync(u => u.DataTaskId == fmd_task.DataTaskId);
                                                                        List<string> mpropertyIds = new List<string>();
                                                                        if (tm_properties != null)
                                                                        {
                                                                            foreach (var tm_property in tm_properties)
                                                                            {
                                                                                mpropertyIds.Add(tm_property.TemplatePId.ToString());
                                                                            }
                                                                            if (mpropertyIds != null)
                                                                            {
                                                                                int differencem = 0;
                                                                                foreach (var mtid in mpropertyIds)
                                                                                {
                                                                                    if (dbFormulaM.Contains(mtid))
                                                                                    {
                                                                                        int splace = dbFormulaM.IndexOf(mtid) - differencem;
                                                                                        int elength = tempFormulaM.Length - splace;
                                                                                        if (splace > -1 && elength > 0)
                                                                                        {
                                                                                            TemplateProperty name2 =await _unitOfWork.TemplateProperty.GetAsync(u => u.TemplatePId.ToString() == mtid);
                                                                                            string name = name2.Name;
                                                                                            IEnumerable<Property> properties3 =await _unitOfWork.Property.WhereAsync(u => u.TaskId == material.TaskId);
                                                                                            List<Property> propertiesss = new List<Property>();
                                                                                            foreach (var nb in properties3)
                                                                                            {
                                                                                                propertiesss.Add(nb);
                                                                                            }
/*                                                                                            Property t = propertiesss.Find(u => u.Name == name);*/
                                                                                            Property t = await FindAsync(name, propertiesss);
                                                                                            string value = "";
                                                                                            if (t != null)
                                                                                            {
                                                                                                value = t.Name.ToString();
                                                                                            }
                                                                                            else
                                                                                            {
                                                                                                value = "0";
                                                                                            }

                                                                                            string to_be_replaced = tempFormulaM.Substring(splace, elength);
                                                                                            tempFormulaM = tempFormulaM.Substring(0, splace) + to_be_replaced.Replace(mtid, value);
                                                                                            differencem = differencem + mtid.Length - value.Length;
                                                                                        }

                                                                                    }
                                                                                }
                                                                                /////////////////getting cell addresses and replaces with names in formula////////
                                                                                for (int i = prop_c; i < 15; i++)
                                                                                {
                                                                                    string p_name = worksheet.Range[task_r, i].Value.ToString();
                                                                                    string p_v_address = worksheet.Range[task_r + 1, i].Address.ToString();
                                                                                    if (p_name != null && p_name != "")
                                                                                    {
                                                                                        if (tempFormulaM.Contains(p_name))
                                                                                        {
                                                                                            tempFormulaM = tempFormulaM.Replace(p_name, p_v_address);
                                                                                        }
                                                                                    }
                                                                                }
                                                                                for (int c = mat_c; c < 34; c++)
                                                                                {
                                                                                    if (worksheet.Range[task_r, c].Text == material.Name)
                                                                                    {
                                                                                        try
                                                                                        {
                                                                                            worksheet.Range[task_r + 1, c].Formula = tempFormulaM;
                                                                                            break;
                                                                                        }
                                                                                        catch (Exception e)
                                                                                        {
                                                                                            ///do nothing
                                                                                        }
                                                                                    }
                                                                                }
                                                                            }
                                                                        }
                                                                    }
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                                
                                                ///////////////////////////////////////////////////////////////////////
                                                mat_c = mat_c + 1;
                                            }
                                            temp2 = mat_c;
                                            mat_c = 22;
                                        }
                                        //////////////calculations///////////
                                        worksheet.Range[task_r, mw_c].Text = "Measured Work";
                                        worksheet.Range[task_r + 1, mw_c].Number = (double)task.Measured_Work;
                                        worksheet.Range[task_r, lr_c].Text = "Labor Rate";
                                        worksheet.Range[task_r + 1, lr_c].Number = (double)task.Labor_Rate;
                                        worksheet.Range[task_r, lc_c].Text = "Labor Cost";
                                        worksheet.Range[task_r + 1, lc_c].Formula = worksheet.Range[task_r + 1, mw_c].Address + "*" + worksheet.Range[task_r + 1, lr_c].Address;

                                        worksheet.Range[task_r, mc_c].Text = "Material Cost";
                                        worksheet.Range[task_r +1, mc_c].Formula = "=Sum("+ worksheet.Range[task_r + 3, mat_c].Address + ":" + worksheet.Range[task_r + 3, temp2].Address + ")";
                                        worksheet.Range[task_r, tot_c].Text = "Total Cost";
                                        worksheet.Range[task_r, tot_c].CellStyle.Font.Bold = true;
                                        worksheet.Range[task_r + 1, tot_c].CellStyle.Font.Bold = true;
                                        worksheet.Range[task_r + 1, tot_c].CellStyle.Font.Size = 14;
                                        worksheet.Range[task_r, tot_c].CellStyle.Font.Size = 14;
                                        worksheet.Range[task_r + 1, tot_c].Formula = "=" + worksheet.Range[task_r + 1, lc_c].Address + "+" + worksheet.Range[task_r + 1, mc_c].Address;

                                        worksheet.Range[task_r + 1, lr_c, task_r + 1, tot_c].CellStyle.NumberFormat = "\\$#,##0.0";
                                        worksheet.Range[task_r + 1, lr_c, task_r + 1, tot_c].CellStyle.FillBackgroundRGB = Syncfusion.Drawing.Color.LightGray;
                                        worksheet.Range[task_r, lr_c, task_r, tot_c].CellStyle.FillBackgroundRGB = Syncfusion.Drawing.Color.DarkGray;
                                      
                                        worksheet.Range[task_r, lr_c, task_r, tot_c].CellStyle.Font.Color = ExcelKnownColors.White;
                                        /////////////////////////////////////
                                        worksheet.Range[task_r, prop_c - 2, task_r + 3, 34].BorderAround(ExcelLineStyle.Thin);
                                        worksheet.Range[task_r, prop_c - 2, task_r + 3, 34].BorderInside(ExcelLineStyle.Thin);

                                        task_r = task_r + 6;
                                        action_r = action_r + 6;
                                        floor_r = floor_r + 6;
                                    }
                                    worksheet.Range[temp1, prop_c - 2,task_r,34].BorderAround(ExcelLineStyle.Thick);

                                }
                                
                                /////////calculations of action/////////////
                                if (rows.Any())
                                {
                                    string lab_cost = "=";
                                    string mat_cost = "=";
                               
                                    foreach (var item in rows)
                                    {
                                        if (lab_cost == "=")
                                        {
                                            lab_cost =  worksheet.Range[item, lc_c].Address;
                                            mat_cost =  worksheet.Range[item, mc_c].Address;
                                        }
                                        else
                                        {
                                            lab_cost = lab_cost + "+" + worksheet.Range[item, lc_c].Address;
                                            mat_cost = mat_cost + "+" + worksheet.Range[item, mc_c].Address;
                                        }
                                    }
                                    worksheet.Range[temp0 + 1, 5].Formula = "=iferror(" + lab_cost + ",0)";
                                    worksheet.Range[temp0, 5].Text = "Labor Cost";

                                    worksheet.Range[temp0 + 1, 6].Formula = "=iferror(" + mat_cost + ",0)";
                                    worksheet.Range[temp0, 6].Text = "Material Cost";

                                    worksheet.Range[temp0 + 1, 7].Formula = worksheet.Range[temp0 + 1, 5].Address + "+" + worksheet.Range[temp0 + 1, 6].Address ;
                                    worksheet.Range[temp0, 7].Text = "Total Action Cost";

                                    worksheet.Range[temp0, 5, temp0 + 1, 7].CellStyle.Font.Bold = true;
                                    worksheet.Range[temp0, 5, temp0 + 1, 7].CellStyle.Font.Size = 13;
                                    worksheet.Range[temp0, 5, temp0 + 1, 7].BorderInside(ExcelLineStyle.Thin);
                                    worksheet.Range[temp0, 5, temp0 + 1, 7].BorderAround(ExcelLineStyle.Thick);
                                    worksheet.Range[temp0, 5, temp0 , 7].CellStyle.FillBackgroundRGB = Syncfusion.Drawing.Color.Gold;
                                    
                                    a_rows.Add(temp0 + 1);
                                }
/*                                worksheet.Range[temp0 - 1, 1, action_r + 6, 34].CellStyle.FillBackgroundRGB = Syncfusion.Drawing.Color.PapayaWhip;*/
                                worksheet.Range[temp0 + 1, 5, temp0 + 1, 7].CellStyle.FillBackgroundRGB = Syncfusion.Drawing.Color.Yellow;
                                worksheet.Range[temp0 + 1, 5, temp0 + 1, 7].CellStyle.NumberFormat = "\\$#,##0.0";

                                ////////////////////////////////////////////
                                task_r = task_r + 6;
                                action_r = action_r + 6; ///extra space
                                floor_r = floor_c + 6;
                            }
                        }
                        /////////////////calculation of floor////////////
                        if (a_rows.Any())
                        {
                            string f_lab_cost = "=";
                            string f_mat_cost = "=";

                            foreach (var item in a_rows)
                            {
                                if (f_lab_cost == "=")
                                {
                                    f_lab_cost =  worksheet.Range[item, 5].Address;
                                    f_mat_cost =  worksheet.Range[item, 6].Address;
                                }
                                else
                                {
                                    f_lab_cost = f_lab_cost + "+" + worksheet.Range[item, 5].Address;
                                    f_mat_cost = f_mat_cost + "+" + worksheet.Range[item, 6].Address;
                                }
                            }
                            worksheet.Range[temp3 + 1, 5].Formula = "=iferror(" + f_lab_cost + ",0)";
                            worksheet.Range[temp3, 5].Text = "Total Labor Cost";

                            worksheet.Range[temp3 + 1, 6].Formula = "=iferror(" + f_mat_cost + ",0)";
                            worksheet.Range[temp3, 6].Text = "Total Material Cost";

                            worksheet.Range[temp3 + 1, 7].Formula = worksheet.Range[temp3 + 1, 5].Address + "+" + worksheet.Range[temp3 + 1, 6].Address;
                            worksheet.Range[temp3, 7].Text = "Total Floor Cost";

                            worksheet.Range[temp3, 5, temp3 + 1, 7].CellStyle.Font.Bold = true;
                            worksheet.Range[temp3, 5, temp3 + 1, 7].CellStyle.Font.Size = 13;
                            worksheet.Range[temp3, 5, temp3 + 1, 7].BorderInside(ExcelLineStyle.Thin);
                            worksheet.Range[temp3, 5, temp3 + 1, 7].BorderAround(ExcelLineStyle.Thick);
                            worksheet.Range[temp3, 5, temp3, 7].CellStyle.FillBackgroundRGB = Syncfusion.Drawing.Color.DarkMagenta;
                            worksheet.Range[temp3, 5, temp3, 7].CellStyle.Font.Color = ExcelKnownColors.White;
                            worksheet.Range[temp3 + 1, 5, temp3 + 1, 7].CellStyle.FillBackgroundRGB = Syncfusion.Drawing.Color.DeepPink;
                            worksheet.Range[temp3 + 1, 5, temp3 + 1, 7].CellStyle.NumberFormat = "\\$#,##0.0";

                            f_rows.Add(temp3+1);
                        }
                        /////////////////////////////////////////////////
                    }
                }
                ////////////////project calculation////////////
                if (f_rows.Any())
                {
                    string p_lab_cost = "=";
                    string p_mat_cost = "=";

                    foreach (var item in f_rows)
                    {
                        if (p_lab_cost == "=")
                        {
                            p_lab_cost = worksheet.Range[item, 5].Address;
                            p_mat_cost = worksheet.Range[item, 6].Address;
                        }
                        else
                        {
                            p_lab_cost = p_lab_cost + "+" + worksheet.Range[item, 5].Address;
                            p_mat_cost = p_mat_cost + "+" + worksheet.Range[item, 6].Address;
                        }
                    }
                    worksheet.Range[temp4 + 1, 5].Formula = "=iferror(" + p_lab_cost + ",0)";
                    worksheet.Range[temp4, 5].Text = "Total Labor Cost";

                    worksheet.Range[temp4 + 1, 6].Formula = "iferror(" + p_mat_cost + ",0)";
                    worksheet.Range[temp4, 6].Text = "Total Material Cost";

                    worksheet.Range[temp4 + 1, 7].Formula = worksheet.Range[temp4 + 1, 5].Address + "+" + worksheet.Range[temp4 + 1, 6].Address;
                    worksheet.Range[temp4, 7].Text = "Total Project Cost";

                    worksheet.Range[temp4, 5, temp4 + 1, 7].CellStyle.Font.Bold = true;
                    worksheet.Range[temp4, 5, temp4 + 1, 7].CellStyle.Font.Size = 15;
                    worksheet.Range[temp4, 5, temp4 + 1, 7].BorderInside(ExcelLineStyle.Thin);
                    worksheet.Range[temp4, 5, temp4 + 1, 7].BorderAround(ExcelLineStyle.Thick);

                    worksheet.Range[1, 1, 7, 34].CellStyle.FillBackgroundRGB = Syncfusion.Drawing.Color.LightSkyBlue;
                    worksheet.Range[temp4, 5, temp4, 7].CellStyle.FillBackgroundRGB = Syncfusion.Drawing.Color.Blue;
                    worksheet.Range[temp4, 5, temp4, 7].CellStyle.Font.Color = ExcelKnownColors.White;
                    worksheet.Range[temp4 + 1, 5, temp4 + 1, 7].CellStyle.FillBackgroundRGB = Syncfusion.Drawing.Color.Aqua;
                    worksheet.Range[temp4 + 1, 5, temp4 + 1, 7].CellStyle.NumberFormat = "\\$#,##0";

                }
                ///////////////////////////////////////////////
                //////////////saving

                string dateToday = DateTime.Today.ToString("d");

                worksheet.Range[2, 9, 2, 12].Merge();
                worksheet.Range[3, 9, 3, 12].Merge();
                worksheet.Range[2, 9].Text = "Data updated till";
                worksheet.Range[3, 9].Text = DateTime.Now.ToString("f");

                worksheet.Range[2, 5].AutofitColumns();;
                worksheet.Range[2, 6].AutofitColumns();
                worksheet.Range[2, 7].AutofitColumns();

                MemoryStream memoryStream = new MemoryStream();
                workbook.SaveAs(memoryStream);

                memoryStream.Position = 0;

                FileStreamResult fileStreamResult = new FileStreamResult(memoryStream, "application/Excel");
                fileStreamResult.FileDownloadName = project.Name + "  " + dateToday + ".xlsx";


                return fileStreamResult;
            }
            

        }
        /*public async Task<int> FindAsync(string text, string searchString)
        {
            return await Task.Run(() => text.IndexOf(searchString));
        }*/
        public async Task<Property> FindAsync(string name, List<Property> propertiesss)
        {
            return await Task.Run(() => propertiesss.Find(u => u.Name == name));
        }
        public async Task<TemplateProperty> FindAsync2(string name, List<TemplateProperty> propertiesss)
        {
            return await Task.Run(() => propertiesss.Find(u => u.Name == name));
        }
        public async Task<TemplateMaterial> FindAsync3(string name, List<TemplateMaterial> materialsssss)
        {
            return await Task.Run(() => materialsssss.Find(u => u.Name == name));
        }
    }
}