ViettechReport/SuperCms/Controllers/BaseController.cs

using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Security.Cryptography;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using SuperCms.Database;
using SuperCms.Models;
using Syncfusion.Drawing;
using Syncfusion.XlsIO;

namespace SuperCms.Controllers
{
    public class BaseController : Controller
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(typeof(Program));

        protected string convertToDateTimeServer(String date)
        {
            // date: 
            DateTime oDateFrom = DateTime.Parse(date);
            string hour = oDateFrom.Hour < 10 ? "0" + oDateFrom.Hour : oDateFrom.Hour.ToString();
            string minute = oDateFrom.Minute < 10 ? "0" + oDateFrom.Minute : oDateFrom.Minute.ToString();
            string second = oDateFrom.Second < 10 ? "0" + oDateFrom.Second : oDateFrom.Second.ToString();
            string month = oDateFrom.Month < 10 ? "0" + oDateFrom.Month : oDateFrom.Month.ToString();
            string day = oDateFrom.Day < 10 ? "0" + oDateFrom.Day : oDateFrom.Day.ToString();
            string fromCheck = month + "/" + day + "/" + oDateFrom.Year + " " + hour + ":" + minute + ":" + second;
            return fromCheck; //yyyy-MM-dd HH24:mm:ss
        }

        protected string getStartByMonthToString(int monthLeft)
        {
            DateTime now = DateTime.Now;
            DateTime time = new DateTime(now.Year, now.Month, 1, 0, 0, 0);
            DateTime timeB = time.AddMonths(monthLeft);
            return timeB.ToString("MM/dd/yyyy HH:mm:ss");
        }

        protected DateTime getStartByMonthToDate(int monthLeft)
        {
            DateTime now = DateTime.Now;
            DateTime time = new DateTime(now.Year, now.Month, 1, 0, 0, 0);
            DateTime timeB = time.AddMonths(monthLeft);
            return timeB;
        }

        protected DateTime getEndByMonthToDate(int monthLeft)
        {
            DateTime now = DateTime.Now;
            DateTime time = new DateTime(now.Year, now.Month, 1, 23, 59, 59);
            DateTime timeB = time.AddMonths(monthLeft + 1).AddDays(-1);
            return timeB;
        }

        protected DateTime getStartByMonthToDate(DateTime now, int monthLeft)
        {
            DateTime time = new DateTime(now.Year, now.Month, 1, 0, 0, 0);
            DateTime timeB = time.AddMonths(monthLeft);
            return timeB;
        }

        protected DateTime getEndByMonthToDate(DateTime now, int monthLeft)
        {
            DateTime time = new DateTime(now.Year, now.Month, 1, 23, 59, 59);
            DateTime timeB = time.AddMonths(monthLeft + 1).AddDays(-1);
            return timeB;
        }

        protected string getStartByDayToString()
        {
            DateTime now = DateTime.Now;
            DateTime date = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0);
            //int dayOfWeek = (int)now.DayOfWeek;
            return date.AddDays(-(7)).ToString("MM/dd/yyyy HH:mm:ss");
        }

        protected String getEndByDayToString()
        {
            // mm/dd/yyyy hh:MM:ss PT
            //DateTime now = DateTime.Now;

            // convert to dd/mm/yyyy
            //return DateTime.Now.ToString("dd/MM/yyyy");

            DateTime now = DateTime.Now;
            DateTime date = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
            return date.AddDays(0).ToString("MM/dd/yyyy HH:mm:ss");
        }

        protected DateTime getStartByDayToDate()
        {
            DateTime now = DateTime.Now;
            DateTime date = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0);
            //int dayOfWeek = (int)now.DayOfWeek;
            return date.AddDays(-(7));
        }

        protected DateTime getEndByDayToDate()
        {
            // mm/dd/yyyy hh:MM:ss PT
            //DateTime now = DateTime.Now;

            // convert to dd/mm/yyyy
            //return DateTime.Now.ToString("dd/MM/yyyy");

            DateTime now = DateTime.Now;
            DateTime date = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59);
            return date.AddDays(0);
        }

        protected List<DateTime> getRangeTime(DateTime start, DateTime end)
        {
            TimeSpan difference = end - start;

            List<DateTime> days = new List<DateTime>();
            //days.Add(DateTime.Now);

            for (int i = difference.Days; i >= 0; i--)
            {
                DateTime dateG = start.AddDays(i);
                DateTime date = new DateTime(dateG.Year, dateG.Month, dateG.Day, 0, 0, 0);
                days.Add(date);
            }
            return days;
        }

        protected List<DateTime> getRangeTimeForExport(DateTime start, DateTime end)
        {
            TimeSpan difference = end - start;

            List<DateTime> days = new List<DateTime>();
            days.Add(end);

            for (int i = difference.Days; i >= 0; i--)
            {
                DateTime dateG = start.AddDays(i);
                DateTime date = new DateTime(dateG.Year, dateG.Month, dateG.Day, 0, 0, 0);
                days.Add(date);
            }
            return days;
        }

        protected List<DateTime> getRangeMonth(DateTime start, DateTime end)
        {
            DateTime startD = new DateTime(start.Year, start.Month, 1);
            DateTime endD = new DateTime(end.Year, end.Month, 1);
            int diffMonths = (endD.Month + endD.Year * 12) - (startD.Month + startD.Year * 12);

            List<DateTime> days = new List<DateTime>();
            days.Add(end);
            for (int i = diffMonths; i >= 0; i--)
            {
                DateTime dateG = start.AddMonths(i);
                DateTime date;
                if (i == 0)
                {
                    date = new DateTime(dateG.Year, dateG.Month, dateG.Day, 0, 0, 0);
                }
                else
                {
                    date = new DateTime(dateG.Year, dateG.Month, 1, 0, 0, 0);
                }
                days.Add(date);
            }
            return days;
        }

        protected List<DistributedByTelcoAndCompany> distributedByTelcoAndCompany(ConnTelcos telcos, ConnCompanies companies, ConnConfigs services, int count, List<DistributedByTelcoAndCompany> result)
        {
            if (count >= services.data.Count)
            {
                return result;
            }
            else
            {
                ConnConfig service = services.data[count];
                if (service.companyID == null || service.telcoID == null)
                {
                    return distributedByTelcoAndCompany(telcos, companies, services, count + 1, result);
                }
                else
                {
                    log.Info(service.telcoID + " " + service.serviceName);
                    List<DistributedByTelcoAndCompany> resultt = result;
                    DistributedByTelcoAndCompany dis = result.Find(x => x.telco.id == service.telcoID);
                    if (dis == null)
                    {
                        // add telco to result
                        DistributedByTelcoAndCompany newEle = new DistributedByTelcoAndCompany();
                        newEle.telco = telcos.data.Find(x => x.id == service.telcoID);
                        // add company
                        ConnCompany company = companies.data.Find(x => x.id == service.companyID);
                        newEle.companies = new ConnCompanies();
                        newEle.companies.data = new List<ConnCompany>();
                        newEle.companies.data.Add(company);

                        // add service
                        newEle.services = new ConnConfigs();
                        newEle.services.data = new List<ConnConfig>();
                        newEle.services.data.Add(service);

                        result.Add(newEle);
                        return distributedByTelcoAndCompany(telcos, companies, services, count + 1, result);
                    }
                    else
                    {
                        // telco existed, get telco
                        DistributedByTelcoAndCompany disres = result.Find(x => x.telco.id == service.telcoID);
                        ConnTelco telco = disres.telco;

                        // check company existed ???
                        ConnCompany com = disres.companies.data.Find(x => x.id == service.companyID);
                        if (com == null)
                        {
                            // add company
                            ConnCompany company = companies.data.Find(x => x.id == service.companyID);
                            result.Where(S => S.telco.id == service.telcoID).Select(S => { S.companies.data.Add(company); return S; }).ToList();
                        }

                        // check service existed ???
                        ConnConfig ser = disres.services.data.Find(x => x.id == service.id);
                        if (ser == null)
                        {
                            // add company
                            result.Where(S => S.telco.id == service.telcoID).Select(S => { S.services.data.Add(service); return S; }).ToList();
                        }
                        return distributedByTelcoAndCompany(telcos, companies, services, count + 1, result);
                    }
                }
            }
        }

        protected IActionResult ToExcelStock(List<DateTime> listdate, Dictionary<ConnCkNganh, Dictionary<DateTime, ConnCkData>> distributionData)
        {
            try
            {
                using (ExcelEngine excelEngine = new ExcelEngine())
                {
                    IApplication application = excelEngine.Excel;
                    application.DefaultVersion = ExcelVersion.Excel2013;
                    IWorkbook workbook = application.Workbooks.Create(1);

                    IWorksheet worksheet = workbook.Worksheets[0];
                    List<RowAttributed> rowAttributedList = new List<RowAttributed>();

                    int rowCount = 1;

                    // for header
                    worksheet.Range["A1"].Text = "NO";
                    worksheet.Range["B1"].Text = "NGÀNH";
                    worksheet.Range["C1"].Text = "CHỈ SỐ";

                    for (int d = 0; d < listdate.Count; d++)
                    {

                        worksheet.Range[UtilsController.GetExcelColumnName(4 + d * 2) + rowCount.ToString()].Text = listdate[d].ToString("dd/MM");
                        worksheet.Range[UtilsController.GetExcelColumnName(5 + d * 2) + rowCount.ToString()].Text = "%";
                    }

                    rowCount++;


                    List<double> listTongGiaTri = new List<double>();
                    List<double> listTongKhoiLuong = new List<double>();
                    List<double> listTongThayDoi = new List<double>();
                    List<double> listTongMua = new List<double>();
                    List<double> listTongBan = new List<double>();

                    for (int d = 0; d < listdate.Count; d++)
                    {
                        // tong sum
                        double tongGiaTri = 0.0;
                        double tongThayDoi = 0.0;
                        double tongKhoiLuong = 0.0;
                        double tongMua = 0.0;
                        double tongBan = 0.0;

                        for (int k = 0; k < distributionData.Count; k++)
                        {
                            Dictionary<DateTime, ConnCkData> dataDateList = distributionData.ElementAt(k).Value;
                            ConnCkData ckData = dataDateList.ElementAt(d).Value;
                            if (ckData != null)
                            {
                                tongBan += Decimal.ToDouble(ckData.TnnBan ?? 0);
                                tongMua += Decimal.ToDouble(ckData.TnnMua ?? 0);
                                tongKhoiLuong += Decimal.ToDouble(ckData.KhoiLuong ?? 0);
                                //tongThayDoi += Double.Parse(ckData.ThayDoi);
                                tongGiaTri += Decimal.ToDouble(ckData.GiaTri ?? 0);
                            }
                        }
                        listTongGiaTri.Add(tongGiaTri);
                        listTongKhoiLuong.Add(tongKhoiLuong);
                        listTongMua.Add(tongMua);
                        listTongBan.Add(tongBan);
                        //listTongThayDoi.Add(tongThayDoi);
                    }

                    for (int i = 0; i < distributionData.Count; i++)
                    {
                        ConnCkNganh nganh = distributionData.ElementAt(i).Key;

                        worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = (i + 1).ToString();
                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null));

                        worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = nganh.Name.ToString();
                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(2), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null));


                        Dictionary<DateTime, ConnCkData> dataCkList = distributionData.ElementAt(i).Value;

                        // chi so tang giam
                        worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Tăng / Giảm";
                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(3), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null));


                        for (int j = 0; j < dataCkList.Count; j++)
                        {
                            ConnCkData dataCk = dataCkList.ElementAt(j).Value;
                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(j * 2 + 4), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null));
                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(j * 2 + 5), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null));

                            if (dataCk != null)
                            {
                                worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.TangGiam.ToString();
                            }
                        }
                        rowCount++;

                        // chi so tang giam
                        worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Thay đổi";

                        for (int j = 0; j < dataCkList.Count; j++)
                        {
                            ConnCkData dataCk = dataCkList.ElementAt(j).Value;
                            if (dataCk != null)
                            {
                                worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.ThayDoi.ToString();
                            }
                        }
                        rowCount++;

                        // chi so tang giam
                        worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Khối lượng";
                        for (int j = 0; j < dataCkList.Count; j++)
                        {
                            ConnCkData dataCk = dataCkList.ElementAt(j).Value;
                            if (dataCk != null)
                            {
                                double percent = Math.Round(Decimal.ToDouble(dataCk.KhoiLuong ?? 0) * 100 / listTongKhoiLuong[j], 2);
                                worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.KhoiLuong.ToString();
                                worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 5) + rowCount.ToString()].Text = percent.ToString() + "%";
                            }
                        }
                        rowCount++;

                        // chi so tang giam
                        worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Giá trị";
                        for (int j = 0; j < dataCkList.Count; j++)
                        {
                            ConnCkData dataCk = dataCkList.ElementAt(j).Value;
                            if (dataCk != null)
                            {
                                worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.GiaTri.ToString();
                                double percent = Math.Round(Decimal.ToDouble(dataCk.GiaTri ?? 0) * 100 / listTongGiaTri[j], 2);
                                worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.GiaTri.ToString();
                                worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 5) + rowCount.ToString()].Text = percent.ToString() + "%";
                            }
                        }
                        rowCount++;

                        // chi so tang giam
                        worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Mua";
                        for (int j = 0; j < dataCkList.Count; j++)
                        {
                            ConnCkData dataCk = dataCkList.ElementAt(j).Value;
                            if (dataCk != null)
                            {
                                double percent = Math.Round(Decimal.ToDouble(dataCk.TnnMua ?? 0) * 100 / listTongMua[j], 2);
                                worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.TnnMua.ToString();
                                worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 5) + rowCount.ToString()].Text = percent.ToString() + "%";
                            }
                        }
                        rowCount++;

                        // chi so tang giam
                        worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Bán";
                        for (int j = 0; j < dataCkList.Count; j++)
                        {
                            ConnCkData dataCk = dataCkList.ElementAt(j).Value;
                            if (dataCk != null)
                            {
                                double percent = Math.Round(Decimal.ToDouble(dataCk.TnnBan ?? 0) * 100 / listTongBan[j], 2);
                                worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.TnnBan.ToString();
                                worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 5) + rowCount.ToString()].Text = percent.ToString() + "%";
                            }
                        }
                        rowCount++;
                    }


                    // TONG
                    worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = (distributionData.Count).ToString();
                    rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null));

                    worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = "TỔNG".ToString();
                    rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(2), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null));

                    // chi so tang giam
                    worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Khối lượng";
                    rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(3), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null));

                    for (int j = 0; j < listTongKhoiLuong.Count; j++)
                    {
                        worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = listTongKhoiLuong[j].ToString();
                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(j * 2 + 4), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null));
                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(j * 2 + 5), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null));

                    }
                    rowCount++;

                    // chi so tang giam
                    worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Giá trị";
                    for (int j = 0; j < listTongGiaTri.Count; j++)
                    {
                        worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = listTongGiaTri[j].ToString();
                    }
                    rowCount++;

                    // chi so tang giam
                    worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Mua";
                    for (int j = 0; j < listTongMua.Count; j++)
                    {
                        worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = listTongMua[j].ToString();
                    }
                    rowCount++;

                    // chi so tang giam
                    worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Bán";
                    for (int j = 0; j < listTongBan.Count; j++)
                    {
                        worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = listTongBan[j].ToString();
                    }
                    rowCount++;

                    //Formatting
                    //Global styles should be used when the same style needs to be applied to more than one cell. This usage of a global style reduces memory usage.
                    //Add custom colors to the palette
                    workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33));

                    //Defining header style
                    IStyle headerStyle = workbook.Styles.Add("HeaderStyle" + 0.ToString());
                    headerStyle.BeginUpdate();
                    headerStyle.Color = Color.FromArgb(82, 190, 128);
                    headerStyle.Font.Bold = true;
                    headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
                    headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
                    headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
                    headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
                    headerStyle.EndUpdate();

                    //Apply Header style
                    worksheet.Rows[0].CellStyle = headerStyle;

                    for (int g = 0; g < rowAttributedList.Count; g++)
                    {
                        worksheet.Range[rowAttributedList[g].character + rowAttributedList[g].number].CellStyle = setStyleRowExcel(workbook, 0, rowAttributedList[g]);
                    }

                    //Auto-fit the columns
                    worksheet.UsedRange.AutofitColumns();


                    //Saving the workbook as stream
                    //FileStream stream = new FileStream("GlobalStyles.xlsx", FileMode.Create, FileAccess.ReadWrite);
                    //workbook.SaveAs(stream);
                    //stream.Dispose();
                    using (var stream = new MemoryStream())
                    {
                        workbook.SaveAs(stream);
                        var content = stream.ToArray();
                        return File(
                            content,
                            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                            "stock_" + DateTime.Now.ToString("dd_MM") + ".xlsx");
                    }
                }

            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                return Redirect("/Home");
            }
        }

        protected IActionResult Excel(DateTime endDateTime, List<DistributedDataForExcelByOneMonth> dataList, List<DistributedByTime> dataAll)
        {
            try
            {
                using (ExcelEngine excelEngine = new ExcelEngine())
                {
                    IApplication application = excelEngine.Excel;
                    application.DefaultVersion = ExcelVersion.Excel2013;
                    IWorkbook workbook = application.Workbooks.Create(dataList.Count);


                    for (int i = 0; i < dataList.Count; i++)
                    {
                        int rowCount = 1;
                        List<DistributedByTelcoAndCompany> distributedByTelcoAndCompanyT = new List<DistributedByTelcoAndCompany>();
                        List<DistributedByTelcoAndCompany> disByTelcoAndCompany = distributedByTelcoAndCompany(dataList[i].telcos, dataList[i].companies, dataList[i].services, 0, distributedByTelcoAndCompanyT);
                        IWorksheet worksheet = workbook.Worksheets[i];

                        List<RowAttributed> rowAttributedList = new List<RowAttributed>();

                        // for header
                        worksheet.Range["A1"].Text = "NO";
                        worksheet.Range["B1"].Text = "VAS CODE";
                        worksheet.Range["C1"].Text = "Acc now";
                        worksheet.Range["D1"].Text = "Acc last month";
                        worksheet.Range["E1"].Text = "+/-";
                        worksheet.Range["F1"].Text = "%";
                        rowCount++;

                        disByTelcoAndCompany.Sort(delegate (DistributedByTelcoAndCompany x, DistributedByTelcoAndCompany y)
                        {
                            if (x.telco.sequence == null && y.telco.sequence == null) return 0;
                            else if (x.telco.sequence == null) return -1;
                            else if (y.telco.sequence == null) return 1;
                            else return int.Parse(x.telco.sequence).CompareTo(int.Parse(y.telco.sequence));
                        });

                        // FOR MONEY TABLES
                        for (int k = 0; k < disByTelcoAndCompany.Count; k++)
                        {
                            if (k != 0)
                            {
                                rowCount++;
                            }
                            // show telco name
                            worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = disByTelcoAndCompany[k].telco.telcoName;
                            worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString() + ":" + UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Merge();
                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, 1));
                            rowCount++;


                            disByTelcoAndCompany[k].companies.data.Sort(delegate (ConnCompany x, ConnCompany y)
                            {
                                if (x.sequence == null && y.sequence == null) return 0;
                                else if (x.sequence == null) return -1;
                                else if (y.sequence == null) return 1;
                                else return int.Parse(x.sequence).CompareTo(int.Parse(y.sequence));
                            });


                            for (int c = 0; c < disByTelcoAndCompany[k].companies.data.Count; c++)
                            {
                                // show company name
                                ConnCompany company = disByTelcoAndCompany[k].companies.data[c];
                                worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = company.name;
                                worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString() + ":" + UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Merge();
                                rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, null, Color.FromArgb(236, 112, 99), 1, 1, 1, 1, 1));
                                rowCount++;

                                // show all services which is belong this this company
                                List<ConnConfig> serviceBelong = disByTelcoAndCompany[k].services.data.FindAll(x => x.companyID == company.id);

                                serviceBelong.Sort(delegate (ConnConfig x, ConnConfig y)
                                {
                                    if (x.sequence == null && y.sequence == null) return 0;
                                    else if (x.sequence == null) return -1;
                                    else if (y.sequence == null) return 1;
                                    else return int.Parse(x.sequence).CompareTo(int.Parse(y.sequence));
                                });

                                for (int s = 0; s < serviceBelong.Count; s++)
                                {
                                    ConnConfig service = serviceBelong[s];
                                    //worksheet.Range[GetExcelColumnName(1) + rowCount.ToString()].Text = service.serviceName;
                                    //serviceRowList.Add(rowCount);
                                    //rowCount++;


                                    // get the data of this service
                                    List<DistributedByTime> dataService = dataList[i].distributedByTimes.FindAll(x => x.service.id == service.id);
                                    DateTime now = DateTime.Now.AddDays(-1);

                                    // get date now
                                    DistributedByTime dataNow = dataService.Find(x => x.time.ToString("dd/MM/yyyy") == endDateTime.ToString("dd/MM/yyyy"));
                                    if (dataService.Count > 0)
                                    {
                                        // check
                                        if (dataService[0].time.Month == now.Month)
                                        {
                                            for (int d = 1; d < dataList[i].time.Count; d++)
                                            {
                                                worksheet.Range[UtilsController.GetExcelColumnName(d + 6) + "1"].Text = dataList[i].time[d].ToString("dd/MM");
                                            }
                                        }
                                        else
                                        {
                                            for (int d = 2; d < dataList[i].time.Count; d++)
                                            {
                                                worksheet.Range[UtilsController.GetExcelColumnName(d + 5) + "1"].Text = dataList[i].time[d].ToString("dd/MM");
                                            }
                                        }
                                    }
                                    // get all data in the last month
                                    // get month now

                                    DateTime startLastMonth = getStartByMonthToDate(dataList[i].time[dataList[i].time.Count - 1], -1);
                                    DateTime stopLastMonth = getEndByMonthToDate(dataList[i].time[dataList[i].time.Count - 1], -1);

                                    worksheet.Name = getStartByMonthToDate(dataList[i].time[dataList[i].time.Count - 1], 0).ToString("MM yyyy");

                                    List<DistributedByTime> dataLastMonth = dataAll.FindAll(x =>
                                        DateTime.Compare(startLastMonth, x.time) <= 0 && DateTime.Compare(x.time, stopLastMonth) <= 0 && x.service.id == service.id);

                                    //DateTime startNow = getStartByMonthToDate(0);
                                    //DateTime stopNow = getEndByDayToDate();
                                    //List<DistributedByTime> dataThisMonth = dataService.FindAll(x =>
                                    //    DateTime.Compare(startNow, x.time) <= 0 && DateTime.Compare(x.time, stopNow) <= 0);

                                    // remove the first element

                                    List<DistributedByTime> dataThisMonth = new List<DistributedByTime>();
                                    for (int h = dataNow != null && dataNow.connDatas != null ? 1 : 0; h < dataService.Count; h++)
                                    {
                                        dataThisMonth.Add(dataService[h]);
                                    }

                                    double newSub = 0, newSubBefore = 0;
                                    double deactive = 0, deactiveBefore = 0;
                                    double revenue = 0, revenueBefore = 0;
                                    double expense = 0, expenseBefore = 0;
                                    double totalSub = 0, totalSubBefore = 0;
                                    double benefit = 0, benefitBefore = 0;
                                    double benefitP = 0, revenueP = 0, expenseP = 0;
                                    double benefitD = 0, revenueD = 0, expenseD = 0;

                                    int dayMonthNow = dataThisMonth.Count;

                                    //for (int y = 0; y < dataLastMonth.Count; y++)
                                    //{
                                    //    if (dataLastMonth[y].connDatas.data.Count > 0)
                                    //    {
                                    //        for (int q = 0; q < dayMonthNow; q++)
                                    //        {
                                    //            newSubBefore += int.Parse(dataLastMonth[q].connDatas.data[0].registerNew);
                                    //            deactiveBefore += int.Parse(dataLastMonth[q].connDatas.data[0].deactive);
                                    //            revenueBefore += int.Parse(dataLastMonth[q].connDatas.data[0].revenue);
                                    //            expenseBefore += int.Parse(dataLastMonth[q].connDatas.data[0].expense);
                                    //            totalSubBefore += int.Parse(dataLastMonth[q].connDatas.data[0].totalSub);
                                    //        }
                                    //    }
                                    //}

                                    if (dataLastMonth.Count > 0 && dayMonthNow <= dataLastMonth.Count - 1)
                                    {
                                        for (int q = 0; q < dayMonthNow; q++)
                                        {
                                            if (dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data.Count > 0)
                                            {
                                                if (dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].registerNew != null)
                                                {
                                                    newSubBefore += double.Parse(dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].registerNew);
                                                }
                                                if (dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].deactive != null)
                                                {
                                                    deactiveBefore += double.Parse(dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].deactive);
                                                }
                                                if (dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].revenue != null)
                                                {
                                                    revenueBefore += double.Parse(dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].revenue);
                                                }
                                                if (dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].expense != null)
                                                {
                                                    expenseBefore += double.Parse(dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].expense);
                                                }
                                                if (dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].totalSub != null)
                                                {
                                                    totalSubBefore += double.Parse(dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].totalSub);
                                                }
                                            }
                                        }
                                    }

                                    benefitBefore = revenueBefore - Math.Abs(expenseBefore);

                                    for (int y = 0; y < dataThisMonth.Count; y++)
                                    {
                                        if (dataThisMonth[y].connDatas.data.Count > 0)
                                        {
                                            if (dataThisMonth[y].connDatas.data[0].registerNew != null)
                                            {
                                                newSub += double.Parse(dataThisMonth[y].connDatas.data[0].registerNew);
                                            }
                                            if (dataThisMonth[y].connDatas.data[0].deactive != null)
                                            {
                                                deactive += double.Parse(dataThisMonth[y].connDatas.data[0].deactive);
                                            }
                                            if (dataThisMonth[y].connDatas.data[0].revenue != null)
                                            {
                                                revenue += double.Parse(dataThisMonth[y].connDatas.data[0].revenue);
                                            }
                                            if (dataThisMonth[y].connDatas.data[0].expense != null)
                                            {
                                                expense += double.Parse(dataThisMonth[y].connDatas.data[0].expense);
                                            }
                                            if (dataThisMonth[y].connDatas.data[0].totalSub != null)
                                            {
                                                totalSub += double.Parse(dataThisMonth[y].connDatas.data[0].totalSub);
                                            }
                                        }
                                    }
                                    benefit = revenue - Math.Abs(expense);

                                    revenueD = revenue - revenueBefore;
                                    expenseD = Math.Abs(expense) - Math.Abs(expenseBefore);
                                    benefitD = benefit - benefitBefore;

                                    revenueP = revenueBefore != 0 ? Math.Round(revenueD * 100 / revenueBefore, 2) : 100;
                                    expenseP = expenseBefore != 0 ? Math.Round(expenseD * 100 / expenseBefore, 2) : 100;
                                    benefitP = benefitBefore != 0 ? Math.Round(benefitD * 100 / benefitBefore, 2) : 100;

                                    // show
                                    worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = service.serviceName + " NET";
                                    RowAttributed r3 = new RowAttributed(UtilsController.GetExcelColumnName(2), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), Color.FromArgb(215, 189, 226), 1, 1, 1, 1, null);
                                    rowAttributedList = rowHandler(rowAttributedList, r3);

                                    worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefit).ToString();
                                    rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(3), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null));

                                    worksheet.Range[UtilsController.GetExcelColumnName(4) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefitBefore).ToString();
                                    rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(4), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null));

                                    if (benefitD < 0)
                                    {
                                        worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = "(" + string.Format("{0:#,0}", Math.Abs(benefitD)).ToString() + ")";
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                        worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefitP).ToString() + "%";
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));
                                    }
                                    else
                                    {
                                        worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefitD).ToString();
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                        worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefitP).ToString() + "%";
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                    }

                                    if (dataNow != null && dataNow.connDatas != null && dataNow.connDatas.data.Count > 0)
                                    {
                                        double benefitN = 0;
                                        if (dataNow.connDatas.data[0].revenue != null && dataNow.connDatas.data[0].expense != null)
                                        {
                                            benefitN = double.Parse(dataNow.connDatas.data[0].revenue) - Math.Abs(double.Parse(dataNow.connDatas.data[0].expense));

                                        }
                                        worksheet.Range[UtilsController.GetExcelColumnName(7) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefitN).ToString();
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(7), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null));

                                    }
                                    for (int l = 0; l < dataThisMonth.Count; l++)
                                    {
                                        if (dataThisMonth[l].connDatas.data.Count > 0)
                                        {
                                            double benefitT = 0;
                                            if (dataThisMonth[l].connDatas.data[0].revenue != null && dataThisMonth[l].connDatas.data[0].expense != null)
                                            {
                                                benefitT = double.Parse(dataThisMonth[l].connDatas.data[0].revenue) - Math.Abs(double.Parse(dataThisMonth[l].connDatas.data[0].expense));
                                            }
                                            worksheet.Range[UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefitT).ToString();
                                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null));

                                        }
                                    }
                                    rowCount++;

                                    if (service.state == UtilsController.Constant.ALL_REVEUNUE)
                                    {
                                        worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = service.serviceName;
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(2), rowCount.ToString(), true, null, Color.FromArgb(215, 189, 226), 1, 1, 1, 1, null));

                                        worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = string.Format("{0:#,0}", revenue).ToString();
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(3), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                        worksheet.Range[UtilsController.GetExcelColumnName(4) + rowCount.ToString()].Text = string.Format("{0:#,0}", revenueBefore).ToString();
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(4), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));


                                        if (revenueD < 0)
                                        {
                                            worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = "(" + string.Format("{0:#,0}", Math.Abs(revenueD)).ToString() + ")";
                                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                            worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", revenueP).ToString() + "%";
                                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));
                                        }
                                        else
                                        {
                                            worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = string.Format("{0:#,0}", revenueD).ToString();
                                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                            worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", revenueP).ToString() + "%";
                                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));
                                        }

                                        if (dataNow != null && dataNow.connDatas != null && dataNow.connDatas.data.Count > 0)
                                        {
                                            worksheet.Range[UtilsController.GetExcelColumnName(7) + rowCount.ToString()].Text = string.Format("{0:#,0}", double.Parse(dataNow.connDatas.data[0].revenue)).ToString();
                                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(7), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                        }

                                        for (int l = 0; l < dataThisMonth.Count; l++)
                                        {
                                            if (dataThisMonth[l].connDatas.data.Count > 0)
                                            {
                                                worksheet.Range[UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l) + rowCount.ToString()].Text = string.Format("{0:#,0}", double.Parse(dataThisMonth[l].connDatas.data[0].revenue)).ToString();
                                                rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                            }
                                        }
                                        rowCount++;

                                        worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = service.serviceName + " COST";
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(2), rowCount.ToString(), true, null, Color.FromArgb(215, 189, 226), 1, 1, 1, 1, null));

                                        worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = string.Format("{0:#,0}", Math.Abs(expense)).ToString();
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(3), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                        worksheet.Range[UtilsController.GetExcelColumnName(4) + rowCount.ToString()].Text = string.Format("{0:#,0}", Math.Abs(expenseBefore)).ToString();
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(4), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                        if (expenseD < 0)
                                        {
                                            worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = "(" + string.Format("{0:#,0}", Math.Abs(expenseD)).ToString() + ")";
                                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                            worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", expenseP).ToString() + "%";
                                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                        }
                                        else
                                        {
                                            worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = string.Format("{0:#,0}", Math.Abs(expenseD)).ToString();
                                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                            worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", expenseP).ToString() + "%";
                                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                        }

                                        if (dataNow != null && dataNow.connDatas != null && dataNow.connDatas.data.Count > 0)
                                        {
                                            String expenseCheck1 = dataNow.connDatas.data[0].expense != null ? dataNow.connDatas.data[0].expense : "0";
                                            worksheet.Range[UtilsController.GetExcelColumnName(7) + rowCount.ToString()].Text = string.Format("{0:#,0}", Math.Abs(double.Parse(expenseCheck1))).ToString();
                                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(7), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                        }
                                        for (int l = 0; l < dataThisMonth.Count; l++)
                                        {
                                            if (dataThisMonth[l].connDatas.data.Count > 0)
                                            {
                                                String expenseCheck = dataThisMonth[l].connDatas.data[0].expense != null ? dataThisMonth[l].connDatas.data[0].expense : "0";
                                                worksheet.Range[UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l) + rowCount.ToString()].Text = string.Format("{0:#,0}", Math.Abs(double.Parse(expenseCheck))).ToString();
                                                rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                            }
                                        }
                                        rowCount++;
                                    }
                                }
                                worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = "TOTAL";
                                rowCount++;
                            }
                        }


                        // FOR SUBSCRIBERS TABLES
                        rowCount += 10;
                        worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = "TOTAL SUBSCRIBERS COMPARE LAST MONTH";
                        worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString() + ":" + UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Merge();
                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, Color.FromArgb(0, 0, 0), Color.FromArgb(255, 174, 33), 1, 1, 1, 1, 1));
                        rowCount++;

                        // for header
                        worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = "NO";
                        worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = "VAS CODE";
                        worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Acc now";
                        worksheet.Range[UtilsController.GetExcelColumnName(4) + rowCount.ToString()].Text = "Acc last month";
                        worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = "+/-";
                        worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = "%";

                        //for (int d = 1; d < dataList[i].time.Count; d++)
                        //{
                        //    worksheet.Range[GetExcelColumnName(d + 6) + "1"].Text = dataList[i].time[d].ToString("dd/MM");
                        //}

                        //rowCount++;


                        for (int k = 0; k < disByTelcoAndCompany.Count; k++)
                        {
                            if (k != 0)
                            {
                                rowCount++;
                            }
                            // show telco name
                            worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = disByTelcoAndCompany[k].telco.telcoName;
                            worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString() + ":" + UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Merge();
                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, 1));
                            rowCount++;


                            disByTelcoAndCompany[k].companies.data.Sort(delegate (ConnCompany x, ConnCompany y)
                            {
                                if (x.sequence == null && y.sequence == null) return 0;
                                else if (x.sequence == null) return -1;
                                else if (y.sequence == null) return 1;
                                else return int.Parse(x.sequence).CompareTo(int.Parse(y.sequence));
                            });

                            for (int c = 0; c < disByTelcoAndCompany[k].companies.data.Count; c++)
                            {
                                // show company name
                                ConnCompany company = disByTelcoAndCompany[k].companies.data[c];
                                worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = company.name;
                                worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString() + ":" + UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Merge();
                                rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, null, Color.FromArgb(236, 112, 99), 1, 1, 1, 1, 1));
                                rowCount++;

                                // show all services which is belong this this company
                                List<ConnConfig> serviceBelong = disByTelcoAndCompany[k].services.data.FindAll(x => x.companyID == company.id);

                                serviceBelong.Sort(delegate (ConnConfig x, ConnConfig y)
                                {
                                    if (x.sequence == null && y.sequence == null) return 0;
                                    else if (x.sequence == null) return -1;
                                    else if (y.sequence == null) return 1;
                                    else return int.Parse(x.sequence).CompareTo(int.Parse(y.sequence));
                                });

                                for (int s = 0; s < serviceBelong.Count; s++)
                                {
                                    ConnConfig service = serviceBelong[s];
                                    //worksheet.Range[GetExcelColumnName(1) + rowCount.ToString()].Text = service.serviceName;
                                    //serviceRowList.Add(rowCount);
                                    //rowCount++;


                                    // get the data of this service
                                    List<DistributedByTime> dataService = dataList[i].distributedByTimes.FindAll(x => x.service.id == service.id);
                                    DateTime now = DateTime.Now.AddDays(-1);

                                    // get date now
                                    DistributedByTime dataNow = dataService.Find(x => x.time.ToString("dd/MM/yyyy") == now.ToString("dd/MM/yyyy"));
                                    // get all data in the last month
                                    // get month now

                                    DateTime startLastMonth = getStartByMonthToDate(dataList[i].time[dataList[i].time.Count - 1], -1);
                                    DateTime stopLastMonth = getEndByMonthToDate(dataList[i].time[dataList[i].time.Count - 1], -1);

                                    worksheet.Name = getStartByMonthToDate(dataList[i].time[dataList[i].time.Count - 1], 0).ToString("MM yyyy");

                                    List<DistributedByTime> dataLastMonth = dataAll.FindAll(x =>
                                        DateTime.Compare(startLastMonth, x.time) <= 0 && DateTime.Compare(x.time, stopLastMonth) <= 0 && x.service.id == service.id);

                                    //DateTime startNow = getStartByMonthToDate(0);
                                    //DateTime stopNow = getEndByDayToDate();
                                    //List<DistributedByTime> dataThisMonth = dataService.FindAll(x =>
                                    //    DateTime.Compare(startNow, x.time) <= 0 && DateTime.Compare(x.time, stopNow) <= 0);

                                    //List<DistributedByTime> dataThisMonth = dataService;
                                    List<DistributedByTime> dataThisMonth = new List<DistributedByTime>();
                                    for (int h = dataNow != null && dataNow.connDatas != null ? 1 : 0; h < dataService.Count; h++)
                                    {
                                        dataThisMonth.Add(dataService[h]);
                                    }

                                    double totalSub = 0, totalSubBefore = 0;
                                    double totalSubD = 0, totalSubP = 0, totalSubN = 0;


                                    if (dataLastMonth.Count > 0 && dataLastMonth[0].connDatas.data.Count > 0)
                                    {
                                        totalSubBefore = int.Parse(dataLastMonth[0].connDatas.data[0].totalSub);
                                    }

                                    if (dataThisMonth.Count > 0 && dataThisMonth[0].connDatas.data.Count > 0)
                                    {
                                        totalSub = int.Parse(dataThisMonth[0].connDatas.data[0].totalSub);
                                    }

                                    totalSubD = totalSub - totalSubBefore;
                                    totalSubP = totalSubBefore != 0 ? Math.Round(totalSubD * 100 / totalSubBefore, 2) : 100;

                                    // show
                                    worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = service.serviceName;
                                    RowAttributed r3 = new RowAttributed(UtilsController.GetExcelColumnName(2), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), Color.FromArgb(215, 189, 226), 1, 1, 1, 1, null);
                                    rowAttributedList = rowHandler(rowAttributedList, r3);

                                    worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = string.Format("{0:#,0}", totalSub).ToString();
                                    rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(3), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null));

                                    worksheet.Range[UtilsController.GetExcelColumnName(4) + rowCount.ToString()].Text = string.Format("{0:#,0}", totalSubBefore).ToString();
                                    rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(4), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null));

                                    if (totalSubD < 0)
                                    {
                                        worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = "(" + string.Format("{0:#,0}", Math.Abs(totalSubD)).ToString() + ")";
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                        worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", totalSubP).ToString() + "%";
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));
                                    }
                                    else
                                    {
                                        worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = string.Format("{0:#,0}", totalSubD).ToString();
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                        worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", totalSubP).ToString() + "%";
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                    }

                                    if (dataNow != null && dataNow.connDatas.data.Count > 0)
                                    {
                                        worksheet.Range[UtilsController.GetExcelColumnName(7) + rowCount.ToString()].Text = string.Format("{0:#,0}", double.Parse(dataNow.connDatas.data[0].totalSub)).ToString();
                                        rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(7), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null));

                                    }
                                    for (int l = 0; l < dataThisMonth.Count; l++)
                                    {
                                        if (dataThisMonth[l].connDatas.data.Count > 0)
                                        {
                                            worksheet.Range[UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l) + rowCount.ToString()].Text = string.Format("{0:#,0}", double.Parse(dataThisMonth[l].connDatas.data[0].totalSub)).ToString();
                                            rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null));
                                        }
                                    }
                                    rowCount++;
                                }
                                worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = "TOTAL";
                                rowCount++;
                            }
                        }


                        //Formatting
                        //Global styles should be used when the same style needs to be applied to more than one cell. This usage of a global style reduces memory usage.
                        //Add custom colors to the palette
                        workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33));

                        //Defining header style
                        IStyle headerStyle = workbook.Styles.Add("HeaderStyle" + i.ToString());
                        headerStyle.BeginUpdate();
                        headerStyle.Color = Color.FromArgb(82, 190, 128);
                        headerStyle.Font.Bold = true;
                        headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
                        headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
                        headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
                        headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
                        headerStyle.EndUpdate();

                        //Apply Header style
                        worksheet.Rows[0].CellStyle = headerStyle;

                        for (int g = 0; g < rowAttributedList.Count; g++)
                        {
                            worksheet.Range[rowAttributedList[g].character + rowAttributedList[g].number].CellStyle = setStyleRowExcel(workbook, i, rowAttributedList[g]);
                        }

                        //Auto-fit the columns
                        worksheet.UsedRange.AutofitColumns();
                    }

                    //Saving the workbook as stream
                    //FileStream stream = new FileStream("GlobalStyles.xlsx", FileMode.Create, FileAccess.ReadWrite);
                    //workbook.SaveAs(stream);
                    //stream.Dispose();
                    using (var stream = new MemoryStream())
                    {
                        workbook.SaveAs(stream);
                        var content = stream.ToArray();
                        return File(
                            content,
                            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                            "report_" + DateTime.Now.ToString("dd_MM") + ".xlsx");
                    }
                }
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                return Redirect("/Home");
            }
        }



        private IStyle setStyleRowExcel(IWorkbook workbook, int worksheet, RowAttributed row)
        {
            IStyle bodyStyle = workbook.Styles.Add(row.character + row.number + "Style" + worksheet.ToString());
            bodyStyle.BeginUpdate();
            if (row.color != null)
            {
                bodyStyle.Font.RGBColor = (Color)row.color;
            }
            if (row.bold != null)
            {
                bodyStyle.Font.Bold = row.bold;
            }
            if (row.background != null)
            {
                bodyStyle.Color = (Color)row.background;
            }
            if (row.thinLeft != null)
            {
                bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
            }
            if (row.thinRight != null)
            {
                bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
            }
            if (row.thinTop != null)
            {
                bodyStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
            }
            if (row.thinBottom != null)
            {
                bodyStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
            }
            if (row.center != null)
            {
                bodyStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
            }
            else
            {
                bodyStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
            }
            bodyStyle.EndUpdate();
            return bodyStyle;
        }

        private List<RowAttributed> rowHandler(List<RowAttributed> rowList, RowAttributed row)
        {

            RowAttributed getRow = rowList.Find(x => x.character == row.character && x.number == row.number);
            if (getRow != null)
            {
                rowList.Where(S => S.character == row.character && S.number == row.number).Select(S =>
                {
                    S.color = row.color != null ? row.color : S.color;
                    S.bold = row.bold != null ? row.bold : S.bold;
                    S.background = row.background != null ? row.background : S.background;
                    S.thinLeft = row.thinLeft != null ? row.thinLeft : S.thinLeft;
                    S.thinRight = row.thinRight != null ? row.thinRight : S.thinRight;
                    S.thinTop = row.thinTop != null ? row.thinTop : S.thinTop;
                    S.thinBottom = row.thinBottom != null ? row.thinBottom : S.thinBottom;
                    return S;
                }).ToList();
            }
            else
            {
                rowList.Add(row);
            }
            return rowList;
        }

        protected void CreateAuthToken()
        {
            // create session authen
            // Create the random value we will use to secure the session.
            string authId = GenerateAuthId();

            // Store the value in both our Session and a Cookie.
            HttpContext.Session.SetString("AuthorizationCookieId", authId);
            string sessionValue = HttpContext.Session.GetString("AuthorizationCookieId");
            //CookieOptions option = new CookieOptions
            //{
            //    Expires = DateTime.Now.AddMinutes(1)
            //};
            //Response.Cookies.Append("Key Name", "Value", option);
            CookieOptions options = new CookieOptions()
            {
                //Path = "/",
                //HttpOnly = true,
                //Secure = false,
                //SameSite = SameSiteMode.None
                Expires = DateTime.Now.AddMinutes(60)
            };
            HttpContext.Response.Cookies.Append("AuthorizationCookie", authId, options);
            string cookieValue = HttpContext.Request.Cookies["AuthorizationCookie"];
        }
        protected bool CheckAuthToken()
        {
            string cookieValue = HttpContext.Request.Cookies["AuthorizationCookie"];
            string sessionValue = HttpContext.Session.GetString("AuthorizationCookieId");

            if (cookieValue == null || sessionValue == null || cookieValue != sessionValue)
            {
                // Invalidate the session and log out the current user.
                return false;
            }
            else
            {
                return true;
            }
        }

        protected bool ClearCache()
        {
            HttpContext.Session.Clear();
            foreach (var cookieKey in HttpContext.Request.Cookies.Keys)
            {
                HttpContext.Response.Cookies.Delete(cookieKey);
            }
            return true;
        }

        private string GenerateAuthId()
        {
            using (RandomNumberGenerator rng = new RNGCryptoServiceProvider())
            {
                byte[] tokenData = new byte[32];
                rng.GetBytes(tokenData);
                return Convert.ToBase64String(tokenData);
            }
        }

    }
}