EOS/Controllers/AnalysisController.cs

using ExportToExcel;
using GODATA.Models.Report;
using GODATA.Models.Ticket;
using LoggingHelper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.UI;

namespace GODATA.Controllers
{
    [GODATA.MvcApplication.SessionExpire]
    public class AnalysisController : Controller
    {
        /// <summary>
        /// Global variable for Analysis Repository.
        /// </summary>
        private AnalysisRepository objAnalysisRepository = null;

        #region Global Variables

        /// <summary>
        /// Represent object of LoggingUtility class
        /// </summary>
        LoggingUtility objLog = new LoggingUtility();

        /// <summary>
        /// Global variable for Ticket administration CCE portal repository.
        /// </summary>
        private TicketAdministrationRepository objTicketAdministrationRepository = null;

        /// <summary>
        /// Absolute Path on server in which excel files are saved
        /// </summary>
        private static string _excelExportPathOnServer = ConfigurationManager.AppSettings["excelExportPathOnServer"].ToString();

        /// <summary>
        /// URI to Path to Excel Export location.
        /// </summary>
        private static string _exportLocation = ConfigurationManager.AppSettings["excelExportPath"].ToString();


        /// <summary>
        /// Represent string object contain log file path
        /// </summary>
        //string path = "~/Log/";
        string path = System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["PathLog"]);
        /// <summary>
        /// Represent string object contain Error log status
        /// </summary>
        string errorlogtf = (ConfigurationManager.AppSettings["ErrorLog"]);
        /// <summary>
        /// Represent string object contain log status
        /// </summary>
        string logtf = (ConfigurationManager.AppSettings["Log"]);
        #endregion

        /// <summary>
        /// The Geo Analysis View
        /// </summary>
        /// <returns>Returns view of Geo analysis</returns>
        public ActionResult Index()
        {
            return View();
        }

        /// <summary>
        /// Get Analysis Report Data based Year, Open Ticket, Close Ticket, Dealer,EOS Van
        /// </summary>
        /// <param name="objAnalysis">Contain Month, IsDealer,IsVan, IsOpenTicket and IsCloseTicket </param>
        /// <returns>return JSON Result view Of Analysis Model Object</returns>
        [HttpGet]
        //[OutputCache(Duration = 120, VaryByParam = "None", Location = OutputCacheLocation.Client)]
        public JsonResult Analysis_GetAnalysisRprtDetl(Analysis objAnalysis)
        {
            Analysis objAnalysisNew = new Analysis();
            objLog.AddLogFile("Analysis_GetAnalysisRprtDetl", DateTime.Now.ToString(ConfigurationManager.AppSettings["dateTimeFormat"]), path, logtf);
            try
            {
                objAnalysisRepository = new AnalysisRepository();
                //Set First and Last Date of Year If No value comes from Javascript else get value that comes From js object
                if (objAnalysis.Month == "" || objAnalysis.Month == null)
                {
                    objAnalysis.FromDate = "01-Jan-" + objAnalysis.Year;
                    objAnalysis.ToDate = "31-Dec-" + objAnalysis.Year;
                }
                else
                {
                    objAnalysis.FromDate = "01-" + objAnalysis.Month + "-" + objAnalysis.Year;
                    objAnalysis.ToDate = DateTime.DaysInMonth(Convert.ToInt32(objAnalysis.Year), DateTime.ParseExact(objAnalysis.Month, "MMM", CultureInfo.InvariantCulture).Month).ToString() + "-" + objAnalysis.Month + "-" + objAnalysis.Year;
                }
                //Get Analysis Report Data From Repository based Year, Open Ticket, Close Ticket, Dealer,EOS Van
                objAnalysisNew = objAnalysisRepository.GetAllRecords(objAnalysis);

                //Get Distinct Result Of EOS Van Detail
                List<GeoAnalysisTicketModel> oEosVanList = objAnalysisNew.GeoAnalysisModelReport.lsEOSVAN.GroupBy(x => x.Id).Select(y => y.First()).Distinct().ToList();
                oEosVanList.ForEach(i => i.lstType = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["DealerDetl"]));
                objAnalysisNew.GeoAnalysisModelReport.lsEOSVAN.Clear();
                oEosVanList.Where(i => i.DefaultLongitude == "0" || i.DefaultLongitude == null).ToList().ForEach(i => i.DefaultLongitude = System.Configuration.ConfigurationManager.AppSettings["DefaultLongitude"]);
                oEosVanList.Where(i => i.DefaultLattitude == "0" || i.DefaultLattitude == null).ToList().ForEach(i => i.DefaultLattitude = System.Configuration.ConfigurationManager.AppSettings["DefaultLatitude"]);
                objAnalysisNew.GeoAnalysisModelReport.lsEOSVAN = oEosVanList;

                //Set EOS Van lstype so that we can identify in Javascript the Result is for EOS Van 
                objAnalysisNew.GeoAnalysisModelReport.lsEOSVAN.ForEach(i => i.lstType = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["EOSVan"]));

                //Get Distinct Result Of Dealer Detail
                List<GeoAnalysisTicketModel> oDealerList = objAnalysisNew.GeoAnalysisModelReport.lsDealerDetl.GroupBy(x => x.Id).Select(y => y.First()).Distinct().ToList();
                //Set Dealer lstype so that we can identify in Javascript the Result is for Dealer
                oDealerList.Where(i => i.DefaultLongitude == "0" || i.DefaultLongitude == null).ToList().ForEach(i => i.DefaultLongitude = System.Configuration.ConfigurationManager.AppSettings["DefaultLongitude"]);
                oDealerList.Where(i => i.DefaultLattitude == "0" || i.DefaultLattitude == null).ToList().ForEach(i => i.DefaultLattitude = System.Configuration.ConfigurationManager.AppSettings["DefaultLatitude"]);
                oDealerList.ForEach(i => i.lstType = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["DealerDetl"]));
                objAnalysisNew.GeoAnalysisModelReport.lsDealerDetl.Clear();
                objAnalysisNew.GeoAnalysisModelReport.lsDealerDetl = oDealerList;

                //Set Open Ticket lstype so that we can identify in Javascript the Result is for Open Ticket
                objAnalysisNew.GeoAnalysisModelReport.lsOpenTicket.ForEach(i => i.lstType = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["OpenTicket"]));
                //Set Close Ticket lstype so that we can identify in Javascript the Result is for Close Ticket
                objAnalysisNew.GeoAnalysisModelReport.lsCloseTicket.ForEach(i => i.lstType = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["CloseTicket"]));
                //Set Opportunity Lost oppurLost so that we can identify in Javascript the Result is for Opportunity Lost
                objAnalysisNew.GeoAnalysisModelReport.lsCloseTicket.Where(i => i.TicketStatusAlias == "Opportunity Lost").ToList().ForEach(i => i.oppurLost = true);

                string _LoginUserId = Session["UserId"].ToString();
                string _timeOffSetMinutes = Session["UtcMinute"].ToString();
                // Set View Bag Data that is used in Javascript
                ViewBag.SecurityToken = ConfigurationManager.AppSettings["RESTfulSecurityToken"].ToString();
                ViewBag.UtcMinutes = _timeOffSetMinutes;
                ViewBag.Userid = _LoginUserId;

                var jsonResult = Json(objAnalysisNew, JsonRequestBehavior.AllowGet);

                jsonResult.MaxJsonLength = int.MaxValue;

                return jsonResult;

                //return Json(objAnalysisNew, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("Analysis_GetAnalysisRprtDetl", ex.Message, path, errorlogtf);
                objLog.AddLogFile(DateTime.Now.ToString(ConfigurationManager.AppSettings["dateTimeFormat"]), path, logtf);
                throw ex;
            }

        }

        /// <summary>
        /// Get Analysis Report Data SLA Met ,SLA Not Met, State and Duration
        /// </summary>
        /// <param name="objAnalysis">Contain State, IsSLANotMet, IsSLAMet, FromDate and ToDate</param>
        /// <returns>return JSON Result view Of Analysis Model Object</returns>
        [HttpGet]
        public JsonResult Analysis_GetPositioningRprtDetl(Analysis objAnalysis)
        {
            Analysis objAnalysisNew = new Analysis();
            objLog.AddLogFile("Analysis_GetAnalysisRprtDetl", DateTime.Now.ToString(ConfigurationManager.AppSettings["dateTimeFormat"]), path, logtf);
            try
            {
                objAnalysisRepository = new AnalysisRepository();
                //It Means That if the Todate != -12  we have to substract the months that comes from javascript else Fromdate = 01-Jan-1947 And Substract 12 Months from Today 
                if (objAnalysis.ToDate != "-12")
                {
                    objAnalysis.FromDate = "01-" + DateTime.Now.AddMonths(Convert.ToInt32(objAnalysis.FromDate)).ToString("MMM") + "-" + DateTime.Now.AddMonths(Convert.ToInt32(objAnalysis.FromDate)).ToString("yyyy");
                    objAnalysis.ToDate = DateTime.DaysInMonth(DateTime.Now.AddMonths(Convert.ToInt32(objAnalysis.ToDate)).Year, DateTime.Now.AddMonths(Convert.ToInt32(objAnalysis.ToDate)).Month).ToString() + "-" + DateTime.Now.AddMonths(Convert.ToInt32(objAnalysis.ToDate)).ToString("MMM") + "-" + DateTime.Now.AddMonths(Convert.ToInt32(objAnalysis.ToDate)).ToString("yyyy");
                }
                else
                {
                    objAnalysis.ToDate = DateTime.Now.AddMonths(Convert.ToInt32(objAnalysis.ToDate)).ToString("dd") + "-" + DateTime.Now.AddMonths(Convert.ToInt32(objAnalysis.ToDate)).ToString("MMM") + "-" + DateTime.Now.AddMonths(Convert.ToInt32(objAnalysis.ToDate)).ToString("yyyy");
                }
                //Get Analysis Report Data From Repository based Year, Open Ticket, Close Ticket, Dealer,EOS Van
                objAnalysisNew = objAnalysisRepository.GetAllRecords(objAnalysis);

                //if sla not met is not checked then we get only results of sla met and vice versa
                if (objAnalysis.IsSLANotMet == false)
                {
                    objAnalysisNew.GeoAnalysisModelReport.lsOpenTicket = objAnalysisNew.GeoAnalysisModelReport.lsOpenTicket.Where(p => p.SlaMet == "True").ToList();
                    objAnalysisNew.GeoAnalysisModelReport.lsCloseTicket = objAnalysisNew.GeoAnalysisModelReport.lsCloseTicket.Where(p => p.SlaMet == "True").ToList();
                }
                else if (objAnalysis.IsSLAMet == false)
                {
                    objAnalysisNew.GeoAnalysisModelReport.lsOpenTicket = objAnalysisNew.GeoAnalysisModelReport.lsOpenTicket.Where(p => p.SlaMet == "False").ToList();
                    objAnalysisNew.GeoAnalysisModelReport.lsCloseTicket = objAnalysisNew.GeoAnalysisModelReport.lsCloseTicket.Where(p => p.SlaMet == "False").ToList();
                }
                //Set SLA met and sla not met value in open and close ticket list so we can identify in javascript
                objAnalysisNew.GeoAnalysisModelReport.lsOpenTicket.Where(p => p.SlaMet == "True").ToList().ForEach(i => i.lstType = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["SLAMet"]));
                objAnalysisNew.GeoAnalysisModelReport.lsOpenTicket.Where(p => p.SlaMet == "False").ToList().ForEach(i => i.lstType = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["SLANotMet"]));

                objAnalysisNew.GeoAnalysisModelReport.lsCloseTicket.Where(p => p.SlaMet == "True").ToList().ForEach(i => i.lstType = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["SLAMet"]));
                objAnalysisNew.GeoAnalysisModelReport.lsCloseTicket.Where(p => p.SlaMet == "False").ToList().ForEach(i => i.lstType = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["SLANotMet"]));

                //get total SLA met and SLA Not Met Count so that we show it on Client Side
                objAnalysisNew.SLAMetCount = objAnalysisNew.GeoAnalysisModelReport.lsOpenTicket.Where(p => p.SlaMet == "True").Count() + objAnalysisNew.GeoAnalysisModelReport.lsCloseTicket.Where(p => p.SlaMet == "True").Count();
                objAnalysisNew.SLANotMetCount = objAnalysisNew.GeoAnalysisModelReport.lsOpenTicket.Where(p => p.SlaMet == "False").Count() + objAnalysisNew.GeoAnalysisModelReport.lsCloseTicket.Where(p => p.SlaMet == "False").Count();

                string _LoginUserId = Session["UserId"].ToString();

                string _timeOffSetMinutes = Session["UtcMinute"].ToString();
                // Set View Bag Data that is used in Javascript
                ViewBag.SecurityToken = ConfigurationManager.AppSettings["RESTfulSecurityToken"].ToString();
                ViewBag.UtcMinutes = _timeOffSetMinutes;
                ViewBag.Userid = _LoginUserId;
                return Json(objAnalysisNew, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("Analysis_GetAnalysisRprtDetl", ex.Message, path, errorlogtf);
                objLog.AddLogFile(DateTime.Now.ToString(ConfigurationManager.AppSettings["dateTimeFormat"]), path, logtf);
                throw ex;
            }
        }


        #region Route wise geo analysis report

        /// <summary>
        /// The Geo Analysis View
        /// </summary>
        /// <returns>Returns view of Geo analysis</returns>
        public ActionResult Analysis_RouteWiseBreakdownReport_Index()
        {
            objTicketAdministrationRepository = new TicketAdministrationRepository();
            //Get route List
            List<RouteModel> oRouteList = objTicketAdministrationRepository.GetRoutesList();

            //Bind route list to route drop down list
            List<SelectListItem> oRouteNameList = new List<SelectListItem>();

            foreach (RouteModel items in oRouteList)
            {
                oRouteNameList.Add(new SelectListItem { Text = items.Name, Value = items.Id.ToString() });
            }
            return View(oRouteNameList.OrderBy(o => o.Text));
        }

        /// <summary>
        /// Get Analysis Report Data based Year, Open Ticket, Close Ticket, Dealer,EOS Van
        /// </summary>
        /// <param name="objAnalysis">Contain Month, IsDealer,IsVan, IsOpenTicket and IsCloseTicket </param>
        /// <returns>return JSON Result view Of Analysis Model Object</returns>
        [HttpGet]
        //[OutputCache(Duration = 120, VaryByParam = "None", Location = OutputCacheLocation.Client)]
        public JsonResult Analysis_RouteWiseBreakdownReport_GetReport(Analysis objAnalysis)
        {
            Analysis objAnalysisNew = new Analysis();
            Analysis objReportKpiAnalysisData = new Analysis();
            objLog.AddLogFile("Analysis_RouteWiseBreakdownReport_GetReport", DateTime.Now.ToString(ConfigurationManager.AppSettings["dateTimeFormat"]), path, logtf);
            try
            {
                objAnalysisRepository = new AnalysisRepository();

                //Get Analysis Report Data From Repository based Year, Open Ticket, Close Ticket, Dealer,EOS Van
                objAnalysisNew = objAnalysisRepository.GetAllRouteWiseBreakdownRecords(objAnalysis);


                //Set Close Ticket lstype so that we can identify in Javascript the Result is for Close Ticket
                objAnalysisNew.GeoAnalysisModelReport.lsCloseTicket.ForEach(i => i.lstType = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["CloseTicket"]));
                //Set Opportunity Lost oppurLost so that we can identify in Javascript the Result is for Opportunity Lost
                objAnalysisNew.GeoAnalysisModelReport.lsCloseTicket.Where(i => i.TicketStatusAlias == "Opportunity Lost").ToList().ForEach(i => i.oppurLost = true);

                //Get kpi's data of analysis report
                objReportKpiAnalysisData = objAnalysisRepository.GetRouteWiseBreakdownKpiData(objAnalysis);

                string _LoginUserId = Session["UserId"].ToString();
                string _timeOffSetMinutes = Session["UtcMinute"].ToString();
                // Set View Bag Data that is used in Javascript
                ViewBag.SecurityToken = ConfigurationManager.AppSettings["RESTfulSecurityToken"].ToString();
                ViewBag.UtcMinutes = _timeOffSetMinutes;
                ViewBag.Userid = _LoginUserId;

                var jsonResult = Json(new { data = objAnalysisNew, kpiData = objReportKpiAnalysisData }, JsonRequestBehavior.AllowGet);

                jsonResult.MaxJsonLength = int.MaxValue;

                return jsonResult;

                //return Json(objAnalysisNew, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("Analysis_RouteWiseBreakdownReport_GetReport", ex.Message, path, errorlogtf);
                objLog.AddLogFile(DateTime.Now.ToString(ConfigurationManager.AppSettings["dateTimeFormat"]), path, logtf);
                throw ex;
            }

        }


        /// <summary>
        /// Export to excel consolidated Report
        /// </summary>
        /// <param name="objAnalysis">Analysis Model</param>
        /// <returns>Returns excel file</returns>
        [HttpPost]
        public string Analysis_RouteWiseBreakdownReport_ExportToExcel(Analysis objAnalysis)
        {
            Analysis objAnalysisNew = new Analysis();
            objLog.AddLogFile("Analysis_RouteWiseBreakdownReport_ExportToExcel", DateTime.Now.ToString(ConfigurationManager.AppSettings["dateTimeFormat"]), path, logtf);
            try
            {
                string fileName = null;
                objAnalysisRepository = new AnalysisRepository();

                //Get Analysis Report Data From Repository based Year, Open Ticket, Close Ticket, Dealer,EOS Van
                objAnalysisNew = objAnalysisRepository.GetAllRouteWiseBreakdownRecords(objAnalysis);

                DataTable ConsolidatedDataTable = objAnalysisNew.GeoAnalysisModelReport.lsCloseTicket.ToDataTable();
                Int32 ConsolidatedDataTableColumnCount = ConsolidatedDataTable.Columns.Count - 1;
                for (var count = ConsolidatedDataTableColumnCount; count >= 12; count--)
                {
                    ConsolidatedDataTable.Columns.RemoveAt(count);
                }

                // getting required dataset
                DataSet ds = new DataSet();
                ds.Tables.Add(ConsolidatedDataTable);

                //Give column names 
                ds.Tables[0].Columns[0].ColumnName = "Ticket Id";
                ds.Tables[0].Columns[1].ColumnName = "Assigned To";
                ds.Tables[0].Columns[2].ColumnName = "Creation Time";
                ds.Tables[0].Columns[3].ColumnName = "Route";
                ds.Tables[0].Columns[4].ColumnName = "VASTeam Member / Van Reg No.";
                ds.Tables[0].Columns[5].ColumnName = "State";
                ds.Tables[0].Columns[6].ColumnName = "City";
                ds.Tables[0].Columns[7].ColumnName = "Customer Name";
                ds.Tables[0].Columns[8].ColumnName = "Customer Contact No.";
                ds.Tables[0].Columns[9].ColumnName = "Dealer Name";
                ds.Tables[0].Columns[10].ColumnName = "Dealer Contact No.";
                ds.Tables[0].Columns[11].ColumnName = "Vehicle Model";

                fileName = "RouteWiseConsolidatedReport_" + DateTime.Now.ToString("ddMMMyyyyhhmmss") + ".xlsx";
                string SaveCsvAs = Server.MapPath(_excelExportPathOnServer + fileName);

                bool isCreated = CreateExcelFile.CreateExcelDocument(ds, SaveCsvAs);
                if (isCreated == true) { return _exportLocation + fileName; }
                else { return "error"; }
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("Analysis_RouteWiseBreakdownReport_ExportToExcel", ex.Message, path, errorlogtf);
                throw ex;
            }
        }


        #endregion


    }




}