ViettechReport/SuperCms/Controllers/ReportController.cs

using System;
using System.Collections;
using System.Collections.Generic;
//using System.Drawing;
using System.Globalization;
using System.IO;
//using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using SuperCms.Extensions;
using SuperCms.Models;
using SuperCms.Repositories;
using Syncfusion.Drawing;
using Syncfusion.XlsIO;

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

        ISuperCmsRepo iSuperCmsRepo;

        public ReportController(ISuperCmsRepo ISuperCmsRepo)
        {
            iSuperCmsRepo = ISuperCmsRepo;
        }

        public IActionResult Index(
            String typeGet,
            String telcoID,
            String companyID,
            String startDate,
            String endDate,
            String page,
            String dateGet
        )
        {
            try
            {
                if (!CheckAuthToken())
                {
                    return Redirect("/Home/Login");
                }

                ReportViewModel model = new ReportViewModel();

                model.date = dateGet != null ? dateGet : (DateTime.Now.Day).ToString();

                model.page = page != null ? page : "1";
                model.typeGet = typeGet != null ? typeGet : UtilsController.Constant.MONEY_GET;
                model.services = new ConnConfigs();
                model.services.data = new List<ConnConfig>();

                model.telcos = new ConnTelcos();
                model.telcos.data = new List<ConnTelco>();

                model.companies = new ConnCompanies();
                model.companies.data = new List<ConnCompany>();

                model.distributedDataByServices = new List<DistributedDataByService>();

                ConnCompanies companies = HttpContext.Session.GetComplexData<ConnCompanies>(
                    "companies"
                );
                if (companies == null)
                {
                    var resultT = iSuperCmsRepo.GetConnCompany(log);

                    var propertyT = typeof(ICollection).GetProperty("Count");
                    int countT = (int)propertyT.GetValue(resultT, null);
                    if (resultT != null && countT > 0)
                    {
                        ConnCompanies data = new ConnCompanies();
                        data.data = new List<ConnCompany>();
                        foreach (IDictionary<string, object> row in (IEnumerable<object>)resultT)
                        {
                            var json = JsonConvert.SerializeObject(row);
                            ConnCompany dataGet = new ConnCompany(json.ToString());
                            data.data.Add(dataGet);
                        }
                        HttpContext.Session.SetComplexData("companies", data);
                    }
                }
                model.companies = companies;
                model.companyID = companyID != null ? companyID : companies.data[0].id;

                ConnTelcos telcos = HttpContext.Session.GetComplexData<ConnTelcos>("telcos");
                if (telcos == null)
                {
                    var resultT = iSuperCmsRepo.GetConnTelco(log);

                    var propertyT = typeof(ICollection).GetProperty("Count");
                    int countT = (int)propertyT.GetValue(resultT, null);
                    if (resultT != null && countT > 0)
                    {
                        ConnTelcos data = new ConnTelcos();
                        data.data = new List<ConnTelco>();
                        foreach (IDictionary<string, object> row in (IEnumerable<object>)resultT)
                        {
                            var json = JsonConvert.SerializeObject(row);
                            ConnTelco dataGet = new ConnTelco(json.ToString());
                            data.data.Add(dataGet);
                        }
                        telcos = new ConnTelcos();
                        telcos.data = new List<ConnTelco>();
                        telcos = data;
                        HttpContext.Session.SetComplexData("telcos", data);
                    }
                }
                model.telcos = telcos;
                model.telcoID = telcoID != null ? telcoID : model.telcos.data[0].id;

                ConnConfigs services = HttpContext.Session.GetComplexData<ConnConfigs>("services");
                if (services == null)
                {
                    var resultC = iSuperCmsRepo.GetConnConfig(log);

                    var propertyC = typeof(ICollection).GetProperty("Count");
                    int countC = (int)propertyC.GetValue(resultC, null);
                    if (resultC != null && countC > 0)
                    {
                        ConnConfigs data = new ConnConfigs();
                        data.data = new List<ConnConfig>();
                        foreach (IDictionary<string, object> row in (IEnumerable<object>)resultC)
                        {
                            var json = JsonConvert.SerializeObject(row);
                            ConnConfig dataGet = new ConnConfig(json.ToString());
                            data.data.Add(dataGet);
                        }
                        services = new ConnConfigs();
                        services.data = new List<ConnConfig>();
                        services = data;
                        HttpContext.Session.SetComplexData("services", data);
                    }
                }
                model.services = services;

                List<DistributedByTelcoAndCompany> dis = HttpContext.Session.GetComplexData<
                    List<DistributedByTelcoAndCompany>
                >("distributedByTelcoAndCompany");
                if (dis == null)
                {
                    List<DistributedByTelcoAndCompany> distributedByTelcoAndCompanyT =
                        new List<DistributedByTelcoAndCompany>();
                    dis = distributedByTelcoAndCompany(
                        model.telcos,
                        model.companies,
                        model.services,
                        0,
                        distributedByTelcoAndCompanyT
                    );
                    HttpContext.Session.SetComplexData("distributedByTelcoAndCompany", dis);
                }

                model.telco = model.telcos.data.Find(x => x.id == model.telcoID);
                model.company = model.companies.data.Find(x => x.id == model.companyID);
                DistributedByTelcoAndCompany resGet = dis.Find(x => x.telco.id == model.telcoID);
                ConnCompanies c = new ConnCompanies();
                c.data = new List<ConnCompany>();
                ConnConfigs s = new ConnConfigs();
                s.data = new List<ConnConfig>();
                model.services = resGet != null ? resGet.services : s;
                model.companies = resGet != null ? resGet.companies : c;

                // sort
                model.services.data.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));
                    }
                );

                // get data in 2 months
                String start =
                    startDate == null
                        ? getStartByMonthToString(-1)
                        : DateTime
                            .ParseExact(
                                startDate,
                                "yyyy-MM-ddTHH:mm:ss",
                                CultureInfo.InvariantCulture
                            )
                            .ToString("MM/dd/yyyy HH:mm:ss");
                String end =
                    endDate == null
                        ? getEndByDayToString()
                        : DateTime
                            .ParseExact(endDate, "yyyy-MM-ddTHH:mm", CultureInfo.InvariantCulture)
                            .ToString("MM/dd/yyyy HH:mm:ss");

                DateTime startDateTime =
                    startDate == null
                        ? getStartByMonthToDate(-1)
                        : DateTime.ParseExact(
                            startDate,
                            "yyyy-MM-ddTHH:mm:ss",
                            CultureInfo.InvariantCulture
                        );
                DateTime endDateTime =
                    endDate == null
                        ? getEndByDayToDate()
                        : DateTime.ParseExact(
                            endDate,
                            "yyyy-MM-ddTHH:mm:ss",
                            CultureInfo.InvariantCulture
                        );

                model.listDate = getRangeTime(startDateTime, endDateTime);
                model.timeNow = model.listDate[1].ToString("dd/MM");
                for (int i = 0; i < model.services.data.Count; i++)
                {
                    DistributedDataByService distributedDataByService =
                        new DistributedDataByService();
                    distributedDataByService.dataNow = new ConnDatas();
                    distributedDataByService.dataLastMonth = new ConnDatas();

                    distributedDataByService.service = model.services.data[i];
                    distributedDataByService.dataLastMonth.data = new List<ConnData>();
                    distributedDataByService.dataNow.data = new List<ConnData>();

                    // distributed by time
                    distributedDataByService.distributedByTimes = new List<DistributedByTime>();

                    var resultD = iSuperCmsRepo.GetConnDataByService(
                        log,
                        model.services.data[i].id,
                        start,
                        end
                    );
                    var propertyD = typeof(ICollection).GetProperty("Count");
                    int countD = (int)propertyD.GetValue(resultD, null);
                    if (resultD != null && countD > 0)
                    {
                        ConnDatas data = new ConnDatas();
                        data.data = new List<ConnData>();
                        foreach (IDictionary<string, object> row in (IEnumerable<object>)resultD)
                        {
                            var json = JsonConvert.SerializeObject(row);
                            ConnData dataGet = new ConnData(json.ToString());
                            data.data.Add(dataGet);
                        }
                        DateTime startLastMonth = startDateTime;
                        DateTime stopLastMonth = getEndByMonthToDate(-1);
                        List<ConnData> dataLastMonth = data.data.FindAll(
                            x =>
                                DateTime.Compare(
                                    startLastMonth,
                                    DateTime.ParseExact(
                                        x.dateGet + " 01:00:00",
                                        "dd/MM/yyyy HH:mm:ss",
                                        CultureInfo.InvariantCulture
                                    )
                                ) <= 0
                                && DateTime.Compare(
                                    DateTime.ParseExact(
                                        x.dateGet + " 01:00:00",
                                        "dd/MM/yyyy HH:mm:ss",
                                        CultureInfo.InvariantCulture
                                    ),
                                    stopLastMonth
                                ) <= 0
                        );

                        DateTime startNow = getStartByMonthToDate(0);
                        DateTime stopNow = getEndByDayToDate();

                        List<ConnData> dataNow = data.data.FindAll(
                            x =>
                                DateTime.Compare(
                                    startNow,
                                    DateTime.ParseExact(
                                        x.dateGet + " 01:00:00",
                                        "dd/MM/yyyy HH:mm:ss",
                                        CultureInfo.InvariantCulture
                                    )
                                ) < 0
                                && DateTime.Compare(
                                    DateTime.ParseExact(
                                        x.dateGet + " 01:00:00",
                                        "dd/MM/yyyy HH:mm:ss",
                                        CultureInfo.InvariantCulture
                                    ),
                                    stopNow
                                ) < 0
                        );

                        //if (dataNow.Count == 0)
                        //{
                        //    dataNow.Add(dataLastMonth[dataLastMonth.Count - 1]);
                        //}

                        distributedDataByService.dataLastMonth.data =
                            dataLastMonth != null ? dataLastMonth : new List<ConnData>();
                        distributedDataByService.dataNow.data =
                            dataNow != null ? dataNow : new List<ConnData>();

                        for (int t = 0; t < model.listDate.Count - 1; t++)
                        {
                            DistributedByTime time = new DistributedByTime();
                            time.time = model.listDate[t + 1];
                            time.connDatas = new ConnDatas();
                            time.connDatas.data = data.data.FindAll(
                                x =>
                                    DateTime.Compare(
                                        model.listDate[t + 1],
                                        DateTime.ParseExact(
                                            x.dateGet + " 01:00:00",
                                            "dd/MM/yyyy HH:mm:ss",
                                            CultureInfo.InvariantCulture
                                        )
                                    ) <= 0
                                    && DateTime.Compare(
                                        DateTime.ParseExact(
                                            x.dateGet + " 01:00:00",
                                            "dd/MM/yyyy HH:mm:ss",
                                            CultureInfo.InvariantCulture
                                        ),
                                        model.listDate[t]
                                    ) <= 0
                            );
                            distributedDataByService.distributedByTimes.Add(time);
                        }
                    }
                    model.distributedDataByServices.Add(distributedDataByService);
                }
                return View("Index", model);
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                return Redirect("/Home");
            }
        }

        public IActionResult Export(String telcoID, String companyID)
        {
            try
            {
                if (!CheckAuthToken())
                {
                    return Redirect("/Home/Login");
                }
                ReportViewModel model = new ReportViewModel();
                ConnTelcos telcos = HttpContext.Session.GetComplexData<ConnTelcos>("telcos");
                ConnCompanies companies = HttpContext.Session.GetComplexData<ConnCompanies>(
                    "companies"
                );

                model.telcos = telcos;
                model.companies = companies;
                List<DistributedByTelcoAndCompany> disByTelcoAndCompany =
                    HttpContext.Session.GetComplexData<List<DistributedByTelcoAndCompany>>(
                        "distributedByTelcoAndCompany"
                    );
                DistributedByTelcoAndCompany dis = disByTelcoAndCompany.Find(
                    x => x.telco.id == telcoID
                );

                if (dis == null)
                {
                    model.telco = disByTelcoAndCompany[0].telco;
                    model.company = disByTelcoAndCompany[0].companies.data[0];
                    model.services = disByTelcoAndCompany[0].services;
                }
                else
                {
                    model.telco = dis.telco;
                    model.companies = dis.companies;
                    model.company =
                        companyID != null
                            ? dis.companies.data.Find(x => x.id == companyID)
                            : dis.companies.data[0];
                    model.services = dis.services;
                }
                model.telcoID = model.telco.id;
                model.companyID = model.company.id;

                model.startDate = getStartByMonthToString(-1);
                model.endDate = getEndByDayToString();

                return PartialView("Export", model);
            }
            catch (Exception ex)
            {
                return Redirect("/Home");
            }
        }

        public IActionResult ExportAction(
            String telcoID,
            String companyID,
            String serviceID,
            String startMonth,
            String endMonth
        )
        {
            try
            {
                if (!CheckAuthToken())
                {
                    return Redirect("/Home/Login");
                }
                //Adding values to a worksheet range
                // get all data
                ConnTelcos telcos = HttpContext.Session.GetComplexData<ConnTelcos>("telcos");
                ConnCompanies companies = HttpContext.Session.GetComplexData<ConnCompanies>(
                    "companies"
                );
                ConnConfigs services = HttpContext.Session.GetComplexData<ConnConfigs>("services");

                //sort
                telcos.data.Sort(
                    delegate(ConnTelco x, ConnTelco y)
                    {
                        return int.Parse(x.id).CompareTo(int.Parse(y.id));
                    }
                );

                companies.data.Sort(
                    delegate(ConnCompany x, ConnCompany y)
                    {
                        return int.Parse(x.id).CompareTo(int.Parse(y.id));
                    }
                );

                DateTime startDateTime =
                    startMonth == null
                        ? getStartByMonthToDate(-1)
                        : DateTime.ParseExact(
                            convertToDateTimeServer(startMonth),
                            "MM/dd/yyyy HH:mm:ss",
                            CultureInfo.InvariantCulture
                        );
                DateTime endDateTime =
                    endMonth == null
                        ? getEndByDayToDate()
                        : DateTime.ParseExact(
                            convertToDateTimeServer(endMonth),
                            "MM/dd/yyyy HH:mm:ss",
                            CultureInfo.InvariantCulture
                        );

                List<DateTime> listMonths = getRangeMonth(startDateTime, endDateTime);
                List<DistributedByTelcoAndCompany> disByTelcoAndCompany =
                    HttpContext.Session.GetComplexData<List<DistributedByTelcoAndCompany>>(
                        "distributedByTelcoAndCompany"
                    );
                // if telcoID == null => get all telcos and all companies
                List<DistributedDataForExcelByOneMonth> disAllMonth =
                    new List<DistributedDataForExcelByOneMonth>();
                List<DistributedByTime> dataAll = new List<DistributedByTime>();

                if (telcoID == UtilsController.Constant.GET_ALL)
                {
                    for (int i = 0; i < listMonths.Count - 1; i++)
                    {
                        DateTime start = listMonths[i + 1];
                        DateTime end = listMonths[i];
                        DistributedDataForExcelByOneMonth disOneMonth =
                            new DistributedDataForExcelByOneMonth();
                        List<DateTime> listDate = getRangeTimeForExport(start, end);
                        disOneMonth.time = listDate;
                        disOneMonth.telcos = telcos;
                        disOneMonth.companies = companies;
                        disOneMonth.services = services;
                        disOneMonth.distributedByTimes = new List<DistributedByTime>();

                        String startTime = start.ToString("MM/dd/yyyy HH:mm:ss");
                        String endTime = end.ToString("MM/dd/yyyy HH:mm:ss");

                        // get data
                        for (int s = 0; s < services.data.Count; s++)
                        {
                            var resultD = iSuperCmsRepo.GetConnDataByService(
                                log,
                                services.data[s].id,
                                startTime,
                                endTime
                            );
                            var propertyD = typeof(ICollection).GetProperty("Count");
                            int countD = (int)propertyD.GetValue(resultD, null);
                            if (resultD != null && countD > 0)
                            {
                                ConnDatas data = new ConnDatas();
                                data.data = new List<ConnData>();
                                foreach (
                                    IDictionary<string, object> row in (IEnumerable<object>)resultD
                                )
                                {
                                    var json = JsonConvert.SerializeObject(row);
                                    ConnData dataGet = new ConnData(json.ToString());
                                    data.data.Add(dataGet);
                                }

                                for (int t = 0; t < listDate.Count - 1; t++)
                                {
                                    DistributedByTime time = new DistributedByTime();
                                    time.time = listDate[t + 1];
                                    time.service = services.data[s];

                                    time.connDatas = new ConnDatas();
                                    time.connDatas.data = data.data.FindAll(
                                        x =>
                                            DateTime.Compare(
                                                listDate[t + 1],
                                                DateTime.ParseExact(
                                                    x.dateGet + " 01:00:00",
                                                    "dd/MM/yyyy HH:mm:ss",
                                                    CultureInfo.InvariantCulture
                                                )
                                            ) <= 0
                                            && DateTime.Compare(
                                                DateTime.ParseExact(
                                                    x.dateGet + " 01:00:00",
                                                    "dd/MM/yyyy HH:mm:ss",
                                                    CultureInfo.InvariantCulture
                                                ),
                                                listDate[t]
                                            ) <= 0
                                    );
                                    disOneMonth.distributedByTimes.Add(time);
                                    dataAll.Add(time);
                                }
                            }
                        }
                        disAllMonth.Add(disOneMonth);
                    }
                }
                else if (
                    telcoID != UtilsController.Constant.GET_ALL
                    && companyID == UtilsController.Constant.GET_ALL
                )
                {
                    for (int i = 0; i < listMonths.Count - 1; i++)
                    {
                        DateTime start = listMonths[i + 1];
                        DateTime end = listMonths[i];

                        DistributedDataForExcelByOneMonth disOneMonth =
                            new DistributedDataForExcelByOneMonth();
                        List<DateTime> listDate = getRangeTimeForExport(start, end);
                        disOneMonth.time = listDate;

                        disOneMonth.telcos = new ConnTelcos();
                        disOneMonth.telcos.data = new List<ConnTelco>();
                        disOneMonth.telcos.data.Add(telcos.data.Find(x => x.id == telcoID));
                        disOneMonth.services = services;
                        disOneMonth.companies = companies;
                        disOneMonth.distributedByTimes = new List<DistributedByTime>();

                        String startTime = start.ToString("MM/dd/yyyy HH:mm:ss");
                        String endTime = end.ToString("MM/dd/yyyy HH:mm:ss");

                        // get all service which have telcoID and companyID equal to telcoID and all
                        List<ConnConfig> servicesFilter = services.data.FindAll(
                            x => x.telcoID == telcoID
                        );
                        disOneMonth.services = new ConnConfigs();
                        disOneMonth.services.data = servicesFilter;

                        // get data
                        for (int s = 0; s < servicesFilter.Count; s++)
                        {
                            var resultD = iSuperCmsRepo.GetConnDataByService(
                                log,
                                servicesFilter[s].id,
                                startTime,
                                endTime
                            );
                            var propertyD = typeof(ICollection).GetProperty("Count");
                            int countD = (int)propertyD.GetValue(resultD, null);
                            if (resultD != null && countD > 0)
                            {
                                ConnDatas data = new ConnDatas();
                                data.data = new List<ConnData>();
                                foreach (
                                    IDictionary<string, object> row in (IEnumerable<object>)resultD
                                )
                                {
                                    var json = JsonConvert.SerializeObject(row);
                                    ConnData dataGet = new ConnData(json.ToString());
                                    data.data.Add(dataGet);
                                }
                                for (int t = 0; t < listDate.Count - 1; t++)
                                {
                                    DistributedByTime time = new DistributedByTime();
                                    time.time = listDate[t + 1];
                                    time.service = servicesFilter[s];
                                    time.connDatas = new ConnDatas();
                                    time.connDatas.data = data.data.FindAll(
                                        x =>
                                            DateTime.Compare(
                                                listDate[t + 1],
                                                DateTime.ParseExact(
                                                    x.dateGet + " 01:00:00",
                                                    "dd/MM/yyyy HH:mm:ss",
                                                    CultureInfo.InvariantCulture
                                                )
                                            ) <= 0
                                            && DateTime.Compare(
                                                DateTime.ParseExact(
                                                    x.dateGet + " 01:00:00",
                                                    "dd/MM/yyyy HH:mm:ss",
                                                    CultureInfo.InvariantCulture
                                                ),
                                                listDate[t]
                                            ) <= 0
                                    );
                                    disOneMonth.distributedByTimes.Add(time);
                                    dataAll.Add(time);
                                }
                            }
                        }
                        disAllMonth.Add(disOneMonth);
                    }
                }
                else if (
                    telcoID != UtilsController.Constant.GET_ALL
                    && companyID != UtilsController.Constant.GET_ALL
                    && serviceID == UtilsController.Constant.GET_ALL
                )
                {
                    for (int i = 0; i < listMonths.Count - 1; i++)
                    {
                        DateTime start = listMonths[i + 1];
                        DateTime end = listMonths[i];

                        DistributedDataForExcelByOneMonth disOneMonth =
                            new DistributedDataForExcelByOneMonth();
                        List<DateTime> listDate = getRangeTimeForExport(start, end);
                        disOneMonth.time = listDate;

                        disOneMonth.telcos = new ConnTelcos();
                        disOneMonth.telcos.data = new List<ConnTelco>();
                        disOneMonth.telcos.data.Add(telcos.data.Find(x => x.id == telcoID));

                        disOneMonth.companies = new ConnCompanies();
                        disOneMonth.companies.data = new List<ConnCompany>();
                        disOneMonth.companies.data.Add(companies.data.Find(x => x.id == companyID));
                        disOneMonth.distributedByTimes = new List<DistributedByTime>();
                        //disOneMonth.services = services;
                        String startTime = start.ToString("MM/dd/yyyy HH:mm:ss");
                        String endTime = end.ToString("MM/dd/yyyy HH:mm:ss");

                        // get all service which have telcoID and companyID equal to telcoID and all
                        List<ConnConfig> servicesFilter = services.data.FindAll(
                            x => x.telcoID == telcoID && x.companyID == companyID
                        );
                        disOneMonth.services = new ConnConfigs();
                        disOneMonth.services.data = servicesFilter;
                        // get data
                        for (int s = 0; s < servicesFilter.Count; s++)
                        {
                            var resultD = iSuperCmsRepo.GetConnDataByService(
                                log,
                                servicesFilter[s].id,
                                startTime,
                                endTime
                            );
                            var propertyD = typeof(ICollection).GetProperty("Count");
                            int countD = (int)propertyD.GetValue(resultD, null);
                            if (resultD != null && countD > 0)
                            {
                                ConnDatas data = new ConnDatas();
                                data.data = new List<ConnData>();
                                foreach (
                                    IDictionary<string, object> row in (IEnumerable<object>)resultD
                                )
                                {
                                    var json = JsonConvert.SerializeObject(row);
                                    ConnData dataGet = new ConnData(json.ToString());
                                    data.data.Add(dataGet);
                                }
                                for (int t = 0; t < listDate.Count - 1; t++)
                                {
                                    DistributedByTime time = new DistributedByTime();
                                    time.time = listDate[t + 1];
                                    time.service = servicesFilter[s];
                                    time.connDatas = new ConnDatas();
                                    time.connDatas.data = data.data.FindAll(
                                        x =>
                                            DateTime.Compare(
                                                listDate[t + 1],
                                                DateTime.ParseExact(
                                                    x.dateGet + " 01:00:00",
                                                    "dd/MM/yyyy HH:mm:ss",
                                                    CultureInfo.InvariantCulture
                                                )
                                            ) <= 0
                                            && DateTime.Compare(
                                                DateTime.ParseExact(
                                                    x.dateGet + " 01:00:00",
                                                    "dd/MM/yyyy HH:mm:ss",
                                                    CultureInfo.InvariantCulture
                                                ),
                                                listDate[t]
                                            ) <= 0
                                    );
                                    disOneMonth.distributedByTimes.Add(time);
                                    dataAll.Add(time);
                                }
                            }
                        }
                        disAllMonth.Add(disOneMonth);
                    }
                }
                else
                {
                    for (int i = 0; i < listMonths.Count - 1; i++)
                    {
                        DateTime start = listMonths[i + 1];
                        DateTime end = listMonths[i];

                        DistributedDataForExcelByOneMonth disOneMonth =
                            new DistributedDataForExcelByOneMonth();
                        List<DateTime> listDate = getRangeTimeForExport(start, end);
                        disOneMonth.time = listDate;

                        disOneMonth.telcos = new ConnTelcos();
                        disOneMonth.telcos.data = new List<ConnTelco>();
                        disOneMonth.telcos.data.Add(telcos.data.Find(x => x.id == telcoID));

                        disOneMonth.companies = new ConnCompanies();
                        disOneMonth.companies.data = new List<ConnCompany>();
                        disOneMonth.companies.data.Add(companies.data.Find(x => x.id == companyID));
                        disOneMonth.distributedByTimes = new List<DistributedByTime>();

                        String startTime = start.ToString("MM/dd/yyyy HH:mm:ss");
                        String endTime = end.ToString("MM/dd/yyyy HH:mm:ss");

                        // get all service which have telcoID and companyID equal to telcoID and all
                        ConnConfig serviceFilter = services.data.Find(x => x.id == serviceID);
                        disOneMonth.services = new ConnConfigs();
                        disOneMonth.services.data = new List<ConnConfig>();
                        disOneMonth.services.data.Add(serviceFilter);
                        // get data
                        var resultD = iSuperCmsRepo.GetConnDataByService(
                            log,
                            serviceFilter.id,
                            startTime,
                            endTime
                        );
                        var propertyD = typeof(ICollection).GetProperty("Count");
                        int countD = (int)propertyD.GetValue(resultD, null);
                        if (resultD != null && countD > 0)
                        {
                            ConnDatas data = new ConnDatas();
                            data.data = new List<ConnData>();
                            foreach (
                                IDictionary<string, object> row in (IEnumerable<object>)resultD
                            )
                            {
                                var json = JsonConvert.SerializeObject(row);
                                ConnData dataGet = new ConnData(json.ToString());
                                data.data.Add(dataGet);
                            }
                            for (int t = 0; t < listDate.Count - 1; t++)
                            {
                                DistributedByTime time = new DistributedByTime();
                                time.time = listDate[t + 1];
                                time.service = serviceFilter;
                                time.connDatas = new ConnDatas();
                                time.connDatas.data = data.data.FindAll(
                                    x =>
                                        DateTime.Compare(
                                            listDate[t + 1],
                                            DateTime.ParseExact(
                                                x.dateGet + " 01:00:00",
                                                "dd/MM/yyyy HH:mm:ss",
                                                CultureInfo.InvariantCulture
                                            )
                                        ) <= 0
                                        && DateTime.Compare(
                                            DateTime.ParseExact(
                                                x.dateGet + " 01:00:00",
                                                "dd/MM/yyyy HH:mm:ss",
                                                CultureInfo.InvariantCulture
                                            ),
                                            listDate[t]
                                        ) <= 0
                                );
                                disOneMonth.distributedByTimes.Add(time);
                                dataAll.Add(time);
                            }
                        }
                        disAllMonth.Add(disOneMonth);
                    }
                }
                return Excel(endDateTime, disAllMonth, dataAll);
            }
            catch (Exception ex)
            {
                log.Error("Exception: ", ex);
                return Redirect("/Home");
            }
        }
    }
}