using LoggingHelper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using GODATA.RoleServices;
using GODATA.AuthenticationServices;
using RestSharp;
using Newtonsoft.Json;
using Kendo.Mvc.UI;
using Kendo.Mvc.Extensions;
using GODATA.Models.Ticket;
using GODATA.Models.Report;
using System.Data;
using System.IO;
using System.Data.OleDb;
using System.Globalization;


namespace GODATA.Models.Report
{
    public class ReportRepository
    {
        #region Global Variables

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

        /// <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 log status
        /// </summary>
        string logtf = (ConfigurationManager.AppSettings["Log"]);

        /// <summary>
        /// Represent string object contain Error log status
        /// </summary>
        string errorlogtf = (ConfigurationManager.AppSettings["ErrorLog"]);


        /// <summary>
        /// getting application name from AppSetting in web.config file
        /// </summary>
        private static string _appName = ConfigurationManager.AppSettings["ApplicationName"].ToString();

        /// <summary>
        /// getting web api token from AppSetting in web.config file
        /// </summary>
        private static string _securityToken = ConfigurationManager.AppSettings["RESTfulSecurityToken"].ToString();

        /// <summary>
        /// getting user's utc minutes from session
        /// </summary>
        private static string _timeOffSetMinutes = null;

        /// <summary>
        /// getting login user's id
        /// </summary>
        private static string _LoginUserId = null;

        /// <summary>
        /// getting login user's name
        /// </summary>
        private static string _LoginUserName = null;

        /// <summary>
        /// getting time zone id from AppSetting in web.config file
        /// </summary>
        private static string _TimeZoneId = ConfigurationManager.AppSettings["TimeZoneId"].ToString();

        /// <summary>
        /// getting rest api url from AppSetting in web.config file
        /// </summary>
        private static string _RestClientUrl = ConfigurationManager.AppSettings["RestfulApiUrl"].ToString();

        /// <summary>
        /// getting Date Time format from AppSetting in web.config file
        /// </summary>
        private static string _dateTimeFormat = ConfigurationManager.AppSettings["dateTimeFormat"].ToString();
        #endregion

        #region open close ticket report

        /// <summary>
        /// Post method to get open tickets history
        /// </summary>
        /// <param name="registrationNo">vehicle registration no.</param>
        /// <param name="mobileNo">customer or owner mobile no.</param>
        /// <param name="complaintNo">ticket id</param>
        /// <returns>returns list of open tickets</returns>
        public List<ReportOpenTicketModel> GetOpenTicketsList(string userId, string startDate, string endDate, Int32 limit, Int32 offset, string ticketStatus, string query,string TicketType)
        {
            try
            {
                _LoginUserId = HttpContext.Current.Session["UserId"].ToString();
                _timeOffSetMinutes = HttpContext.Current.Session["UtcMinute"].ToString();
                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest("Api/Ticket?TicketType=" + ticketStatus, Method.POST);
                request.AddParameter("Token", _securityToken);
                request.AddParameter("FromDate", Convert.ToDateTime(startDate).ToString(_dateTimeFormat));
                request.AddParameter("ToDate", Convert.ToDateTime(endDate).ToString(_dateTimeFormat));
                request.AddParameter("Limit", limit);
                request.AddParameter("Offset", offset);
                request.AddParameter("UserId", _LoginUserId); 
                request.AddParameter("UtcMinute", _timeOffSetMinutes);
                request.AddParameter("Condition", query);
        request.AddParameter("TicketType", TicketType);
        return client.Execute<List<ReportOpenTicketModel>>(request).Data;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetOpenTicketsList", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #region REPORTS

        #region EOS Call Count

        /// <summary>
        /// To Get Eos Call Count report json form API and deserialize it into model 
        /// </summary>
        /// <param name="Token">security token</param>
        /// <param name="UtcMinutes">UTC minutes</param>
        /// <param name="UserId">user id</param>
        /// <param name="fromDate">start date</param>
        /// <param name="toDate">end date</param>
        /// <param name="state">state name</param>
        /// <param name="city">city name</param>
        /// <param name="dealerId">dealer id</param>
        /// <returns> Eos Call Count report Model</returns>
        public EosCallCount GetEosCallCount(string Token, int UtcMinutes, string UserId, DateTime fromDate, DateTime toDate, string state, string city, string dealerId)
        {
            try
            {
                EosCallCount model = new EosCallCount();

                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest(ConfigurationManager.AppSettings["eosCallCountApi"].ToString(), Method.POST);
                request.AddParameter("Token", Token);
                request.AddParameter("FromDate", fromDate.ToString(_dateTimeFormat));
                request.AddParameter("ToDate", toDate.ToString(_dateTimeFormat));
                request.AddParameter("UserId", UserId);
                request.AddParameter("state", state);
                request.AddParameter("city", city);
                request.AddParameter("DealerId", dealerId);
                request.AddParameter("UtcMinute", UtcMinutes);
                request.RequestFormat = DataFormat.Json;
                request.AddHeader("content-type", "application/json");
                var response = client.Execute(request);
                var content = response.Content;

                model = JsonConvert.DeserializeObject<EosCallCount>(content);

                return model;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetEosCallCount", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #region HD, LMD & Buses Contribution

        /// <summary>
        /// To Get Hd Lmd Bus Contribution report json form API and deserialize it into model
        /// </summary>
        /// <param name="Token">security token</param>
        /// <param name="UtcMinutes">UTC minutes</param>
        /// <param name="UserId">user id</param>
        /// <param name="fromDate">start date</param>
        /// <param name="toDate">end date</param>
        /// <param name="state">state name</param>
        /// <param name="city">city name</param>
        /// <param name="dealerId">dealer id</param>
        /// <returns>Hd Lmd Bus Contribution report model</returns>
        public HdLmdBusContribution GetHdLmdBusContribution(string Token, int UtcMinutes, string UserId, DateTime fromDate, DateTime toDate, string state, string city, string dealerId)
        {
            try
            {
                HdLmdBusContribution model = new HdLmdBusContribution();

                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest(ConfigurationManager.AppSettings["HdBusLmdContributionApi"].ToString(), Method.POST);
                request.AddParameter("Token", Token);
                request.AddParameter("FromDate", fromDate.ToString(_dateTimeFormat));
                request.AddParameter("ToDate", toDate.ToString(_dateTimeFormat));
                request.AddParameter("UserId", UserId);
                request.AddParameter("state", state);
                request.AddParameter("city", city);
                request.AddParameter("DealerId", dealerId);
                request.AddParameter("UtcMinute", UtcMinutes);
                request.RequestFormat = DataFormat.Json;
                request.AddHeader("content-type", "application/json");
                var response = client.Execute(request);
                var content = response.Content;

                model = JsonConvert.DeserializeObject<HdLmdBusContribution>(content);

                return model;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetHdLmdBusContribution", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #region Dealer Wise Call Details

        /// <summary>
        /// To Get Dealer Wise Call Details report json form API and deserialize it into model
        /// </summary>
        /// <param name="Token">security token</param>
        /// <param name="UtcMinutes">UTC minutes</param>
        /// <param name="UserId">user id</param>
        /// <param name="fromDate">start date</param>
        /// <param name="toDate">end date</param>
        /// <param name="state">state name</param>
        /// <param name="city">city name</param>
        /// <param name="dealerId">dealer id</param>
        /// <returns>Dealer Wise Call Details report model</returns>
        public DealerWiseCallDetail GetDealerWiseCallDetails(string Token, int UtcMinutes, string UserId, DateTime fromDate, DateTime toDate, string state, string city, string dealerId)
        {
            try
            {
                DealerWiseCallDetail model = new DealerWiseCallDetail();

                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest(ConfigurationManager.AppSettings["DealerWiseCallDetailsApi"].ToString(), Method.POST);
                request.AddParameter("Token", Token);
                request.AddParameter("FromDate", fromDate.ToString(_dateTimeFormat));
                request.AddParameter("ToDate", toDate.ToString(_dateTimeFormat));
                request.AddParameter("UserId", UserId);
                request.AddParameter("state", state);
                request.AddParameter("city", city);
                request.AddParameter("DealerId", dealerId);
                request.AddParameter("UtcMinute", UtcMinutes);
                request.RequestFormat = DataFormat.Json;
                request.AddHeader("content-type", "application/json");
                var response = client.Execute(request);
                var content = response.Content;

                model = JsonConvert.DeserializeObject<DealerWiseCallDetail>(content);

                return model;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetDealerWiseCallDetails", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        /// <summary>
        /// To Get Reason Call Closed Beyond 24Hrs json form API and deserialize it into model
        /// </summary>
        /// <param name="Token">security token</param>
        /// <param name="UtcMinutes">UTC minutes</param>
        /// <param name="UserId">user id</param>
        /// <param name="fromDate">start date</param>
        /// <param name="toDate">end date</param>
        /// <param name="state">state name</param>
        /// <param name="city">city name</param>
        /// <param name="dealerId">dealer id</param>
        /// <returns>Reason Call Closed Beyond 24Hrs report model</returns>
        public DealerWiseReasonCallClosedBeyond24Hrs GetReasonCallClosedBeyond24Hrs(string Token, int UtcMinutes, string UserId, DateTime fromDate, DateTime toDate, string state, string city, string dealerId)
        {
            try
            {
                DealerWiseReasonCallClosedBeyond24Hrs model = new DealerWiseReasonCallClosedBeyond24Hrs();

                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest(ConfigurationManager.AppSettings["ReasonCallClosedBeyond24HrsApi"].ToString(), Method.POST);
                request.AddParameter("Token", Token);
                request.AddParameter("FromDate", fromDate.ToString(_dateTimeFormat));
                request.AddParameter("ToDate", toDate.ToString(_dateTimeFormat));
                request.AddParameter("UserId", UserId);
                request.AddParameter("state", state);
                request.AddParameter("city", city);
                request.AddParameter("DealerId", dealerId);
                request.AddParameter("UtcMinute", UtcMinutes);
                request.RequestFormat = DataFormat.Json;
                request.AddHeader("content-type", "application/json");
                var response = client.Execute(request);
                var content = response.Content;

                model = JsonConvert.DeserializeObject<DealerWiseReasonCallClosedBeyond24Hrs>(content);

                return model;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetReasonCallClosedBeyond24Hrs", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #region Timeslot Closure

        /// <summary>
        /// To Get Timeslot Closure report json form API and deserialize it into model
        /// </summary>
        /// <param name="Token">security token</param>
        /// <param name="UtcMinutes">UTC minutes</param>
        /// <param name="UserId">user id</param>
        /// <param name="fromDate">start date</param>
        /// <param name="toDate">end date</param>
        /// <param name="state">state name</param>
        /// <param name="city">city name</param>
        /// <param name="dealerId">dealer id</param>
        /// <returns>Timeslot Closure report model</returns>
        public TimeslotClosure GetTimeslotClosure(string Token, int UtcMinutes, string UserId, DateTime fromDate, DateTime toDate, string state, string city, string dealerId)
        {
            try
            {
                TimeslotClosure model = new TimeslotClosure();

                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest(ConfigurationManager.AppSettings["TimeslotClosureApi"].ToString(), Method.POST);
                request.AddParameter("Token", Token);
                request.AddParameter("FromDate", fromDate.ToString(_dateTimeFormat));
                request.AddParameter("ToDate", toDate.ToString(_dateTimeFormat));
                request.AddParameter("UserId", UserId);
                request.AddParameter("state", state);
                request.AddParameter("city", city);
                request.AddParameter("DealerId", dealerId);
                request.AddParameter("UtcMinute", UtcMinutes);
                request.RequestFormat = DataFormat.Json;
                request.AddHeader("content-type", "application/json");
                var response = client.Execute(request);
                var content = response.Content;

                model = JsonConvert.DeserializeObject<TimeslotClosure>(content);

                return model;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetTimeslotClosure", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #region Toll Free No. and Oppotunity Loss

        /// <summary>
        /// To Get Opportunity Loss report json form API and deserialize it into model
        /// </summary>
        /// <param name="Token">security token</param>
        /// <param name="UtcMinutes">UTC minutes</param>
        /// <param name="UserId">user id</param>
        /// <param name="fromDate">start date</param>
        /// <param name="toDate">end date</param>
        /// <param name="state">state name</param>
        /// <param name="city">city name</param>
        /// <param name="dealerId">dealer id</param>
        /// <returns>Opportunity Loss report model</returns>
        public OpportunityLoss GetOpportunityLoss(string Token, int UtcMinutes, string UserId, DateTime fromDate, DateTime toDate, string state, string city, string dealerId)
        {
            try
            {
                OpportunityLoss model = new OpportunityLoss();

                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest(ConfigurationManager.AppSettings["OpportunityLossApi"].ToString(), Method.POST);
                request.AddParameter("Token", Token);
                request.AddParameter("FromDate", fromDate.ToString(_dateTimeFormat));
                request.AddParameter("ToDate", toDate.ToString(_dateTimeFormat));
                request.AddParameter("UserId", UserId);
                request.AddParameter("state", state);
                request.AddParameter("city", city);
                request.AddParameter("DealerId", dealerId);
                request.AddParameter("UtcMinute", UtcMinutes);
                request.RequestFormat = DataFormat.Json;
                request.AddHeader("content-type", "application/json");
                var response = client.Execute(request);
                var content = response.Content;

                model = JsonConvert.DeserializeObject<OpportunityLoss>(content);

                return model;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetOpportunityLoss", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        /// <summary>
        /// To Get Toll Free No report json form API and deserialize it into model
        /// </summary>
        /// <param name="Token">security token</param>
        /// <param name="UtcMinutes">UTC minutes</param>
        /// <param name="UserId">user id</param>
        /// <param name="fromDate">start date</param>
        /// <param name="toDate">end date</param>
        /// <param name="state">state name</param>
        /// <param name="city">city name</param>
        /// <param name="dealerId">dealer id</param>
        /// <returns>Toll Free No report model</returns>
        public TollFreeCall GetTollFreeNo(string Token, int UtcMinutes, string UserId, DateTime fromDate, DateTime toDate, string state, string city, string dealerId)
        {
            try
            {
                TollFreeCall model = new TollFreeCall();

                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest(ConfigurationManager.AppSettings["TollFreeNoApi"].ToString(), Method.POST);
                request.AddParameter("Token", Token);
                request.AddParameter("FromDate", fromDate.ToString(_dateTimeFormat));
                request.AddParameter("ToDate", toDate.ToString(_dateTimeFormat));
                request.AddParameter("UserId", UserId);
                request.AddParameter("state", state);
                request.AddParameter("city", city);
                request.AddParameter("DealerId", dealerId);
                request.AddParameter("UtcMinute", UtcMinutes);
                request.RequestFormat = DataFormat.Json;
                request.AddHeader("content-type", "application/json");
                var response = client.Execute(request);
                var content = response.Content;

                model = JsonConvert.DeserializeObject<TollFreeCall>(content);

                return model;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetTollFreeNo", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #region Call Feedback Rating

        /// <summary>
        /// To Get Call Feedback Rating report json form API and deserialize it into model
        /// </summary>
        /// <param name="Token">security token</param>
        /// <param name="UtcMinutes">UTC minutes</param>
        /// <param name="UserId">user id</param>
        /// <param name="fromDate">start date</param>
        /// <param name="toDate">end date</param>
        /// <param name="state">state name</param>
        /// <param name="city">city name</param>
        /// <param name="dealerId">dealer id</param>
        /// <returns>Call Feedback Rating report model</returns>
        public CallFeedbackRating GetCallFeedbackRating(string Token, int UtcMinutes, string UserId, DateTime fromDate, DateTime toDate, string state, string city, string dealerId)
        {
            try
            {
                CallFeedbackRating model = new CallFeedbackRating();

                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest(ConfigurationManager.AppSettings["CallFeedbackRatingApi"].ToString(), Method.POST);
                request.AddParameter("Token", Token);
                request.AddParameter("FromDate", fromDate.ToString(_dateTimeFormat));
                request.AddParameter("ToDate", toDate.ToString(_dateTimeFormat));
                request.AddParameter("UserId", UserId);
                request.AddParameter("state", state);
                request.AddParameter("city", city);
                request.AddParameter("DealerId", dealerId);
                request.AddParameter("UtcMinute", UtcMinutes);
                request.RequestFormat = DataFormat.Json;
                request.AddHeader("content-type", "application/json");
                var response = client.Execute(request);
                var content = response.Content;

                model = JsonConvert.DeserializeObject<CallFeedbackRating>(content);

                return model;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetCallFeedbackRating", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #region Reason for closure (>24)

        /// <summary>
        /// To Get Hd Lmd Call Closure report json form API and deserialize it into model
        /// </summary>
        /// <param name="Token">security token</param>
        /// <param name="UtcMinutes">UTC minutes</param>
        /// <param name="UserId">user id</param>
        /// <param name="fromDate">start date</param>
        /// <param name="toDate">end date</param>
        /// <param name="state">state name</param>
        /// <param name="city">city name</param>
        /// <param name="dealerId">dealer id</param>
        /// <returns>Hd Lmd Call Closure report model</returns>
        public HdLmdBusCallCLoserModel GetHdLmdCallClosure(string Token, int UtcMinutes, string UserId, DateTime fromDate, DateTime toDate, string state, string city, string dealerId)
        {
            try
            {
                HdLmdBusCallCLoserModel model = new HdLmdBusCallCLoserModel();

                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest(ConfigurationManager.AppSettings["HdLmdCallClosureApi"].ToString(), Method.POST);
                request.AddParameter("Token", Token);
                request.AddParameter("FromDate", fromDate.ToString(_dateTimeFormat));
                request.AddParameter("ToDate", toDate.ToString(_dateTimeFormat));
                request.AddParameter("UserId", UserId);
                request.AddParameter("state", state);
                request.AddParameter("city", city);
                request.AddParameter("DealerId", dealerId);
                request.AddParameter("UtcMinute", UtcMinutes);
                request.RequestFormat = DataFormat.Json;
                request.AddHeader("content-type", "application/json");
                var response = client.Execute(request);
                var content = response.Content;

                model = JsonConvert.DeserializeObject<HdLmdBusCallCLoserModel>(content);

                return model;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetHdLmdCallClosure", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        /// <summary>
        /// To Get Call Closure More Than 24 hrs report json form API and deserialize it into model
        /// </summary>
        /// <param name="Token">security token</param>
        /// <param name="UtcMinutes">UTC minutes</param>
        /// <param name="UserId">user id</param>
        /// <param name="fromDate">start date</param>
        /// <param name="toDate">end date</param>
        /// <param name="state">state name</param>
        /// <param name="city">city name</param>
        /// <param name="dealerId">dealer id</param>
        /// <returns>Call Closure More Than 24 hrs report model</returns>
        public ReasonCallClosedBeyond24Hrs GetCallClosureMoreThan24(string Token, int UtcMinutes, string UserId, DateTime fromDate, DateTime toDate, string state, string city, string dealerId)
        {
            try
            {
                ReasonCallClosedBeyond24Hrs model = new ReasonCallClosedBeyond24Hrs();

                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest(ConfigurationManager.AppSettings["CallClosureMoreThan24Api"].ToString(), Method.POST);
                request.AddParameter("Token", Token);
                request.AddParameter("FromDate", fromDate.ToString(_dateTimeFormat));
                request.AddParameter("ToDate", toDate.ToString(_dateTimeFormat));
                request.AddParameter("UserId", UserId);
                request.AddParameter("state", state);
                request.AddParameter("city", city);
                request.AddParameter("DealerId", dealerId);
                request.AddParameter("UtcMinute", UtcMinutes);
                request.RequestFormat = DataFormat.Json;
                request.AddHeader("content-type", "application/json");
                var response = client.Execute(request);
                var content = response.Content;

                model = JsonConvert.DeserializeObject<ReasonCallClosedBeyond24Hrs>(content);

                return model;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetCallClosureMoreThan24", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #region Declined Calls

        /// <summary>
        /// To Get Declined Calls report json form API and deserialize it into model
        /// </summary>
        /// <param name="Token">security token</param>
        /// <param name="UtcMinutes">UTC minutes</param>
        /// <param name="UserId">user id</param>
        /// <param name="fromDate">start date</param>
        /// <param name="toDate">end date</param>
        /// <param name="state">state name</param>
        /// <param name="city">city name</param>
        /// <param name="dealerId">dealer id</param>
        /// <returns>Declined Calls report model</returns>
        public DeclinedCalls GetDeclinedCalls(string Token, int UtcMinutes, string UserId, DateTime fromDate, DateTime toDate, string state, string city, string dealerId)
        {
            try
            {
                DeclinedCalls model = new DeclinedCalls();

                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest(ConfigurationManager.AppSettings["DeclinedCallsApi"].ToString(), Method.POST);
                request.AddParameter("Token", Token);
                request.AddParameter("FromDate", fromDate.ToString(_dateTimeFormat));
                request.AddParameter("ToDate", toDate.ToString(_dateTimeFormat));
                request.AddParameter("UserId", UserId);
                request.AddParameter("state", state);
                request.AddParameter("city", city);
                request.AddParameter("DealerId", dealerId);
                request.AddParameter("utcminute", UtcMinutes);
                request.RequestFormat = DataFormat.Json;
                request.AddHeader("content-type", "application/json");
                var response = client.Execute(request);
                var content = response.Content;

                model = JsonConvert.DeserializeObject<DeclinedCalls>(content);

                return model;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetDeclinedCalls", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #region Eicher Promise

        /// <summary>
        /// To Get Declined Calls report json form API and deserialize it into model
        /// </summary>
        /// <param name="Token">security token</param>
        /// <param name="UtcMinutes">UTC minutes</param>
        /// <param name="UserId">user id</param>
        /// <param name="fromDate">start date</param>
        /// <param name="toDate">end date</param>
        /// <param name="state">state name</param>
        /// <param name="city">city name</param>
        /// <param name="dealerId">dealer id</param>
        /// <returns>Declined Calls report model</returns>
        public EicherPromiseModel GetEicherPromiseReport(string Token, int UtcMinutes, string UserId, DateTime fromDate, DateTime toDate, string state, string city, string dealerId)
        {
            try
            {
                EicherPromiseModel model = new EicherPromiseModel();

                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest(ConfigurationManager.AppSettings["EicherPromiseApi"].ToString(), Method.POST);
                request.AddParameter("Token", Token);
                request.AddParameter("FromDate", fromDate.ToString(_dateTimeFormat));
                request.AddParameter("ToDate", toDate.ToString(_dateTimeFormat));
                request.AddParameter("UserId", UserId);
                request.AddParameter("state", state);
                request.AddParameter("city", city);
                request.AddParameter("DealerId", dealerId);
                request.AddParameter("utcminute", UtcMinutes);
                request.RequestFormat = DataFormat.Json;
                request.AddHeader("content-type", "application/json");
                var response = client.Execute(request);
                var content = response.Content;

                model = JsonConvert.DeserializeObject<EicherPromiseModel>(content);

                return model;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetEicherPromiseReport", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #region Customer Report

        /// <summary>
        /// To get Owner List for a particular state or on the basis of a searched value
        /// </summary>
        /// <param name="state">state</param>
        /// <param name="FilterVal">Filter Value</param>
        /// <param name="limit">limit of the returned objects</param>
        /// <param name="Offset">starting index from where objects are fetched</param>
        /// <returns></returns>
        public List<ManageOwner> GetOwnerList(string state, string FilterVal, int limit, int Offset)
        {
            try
            {
                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest("Api/Customer?owner=hh", Method.POST);
                request.AddParameter("Token", _securityToken);
                request.AddParameter("CustomerState", state);
                request.AddParameter("Value", FilterVal);
                request.AddParameter("Limit", limit);
                request.AddParameter("OffeSet", Offset);
                return client.Execute<List<ManageOwner>>(request).Data;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("GetOwnerList", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        /// <summary>
        /// To Get Declined Calls report json form API and deserialize it into model
        /// </summary>
        /// <param name="Token">security token</param>
        /// <param name="UtcMinutes">UTC minutes</param>
        /// <param name="UserId">user id</param>
        /// <param name="fromDate">start date</param>
        /// <param name="toDate">end date</param>
        /// <param name="state">state name</param>
        /// <param name="city">city name</param>
        /// <param name="dealerId">dealer id</param>
        /// <returns>Declined Calls report model</returns>
        public CustomerReportModel GetCustomerReport(string Token, int UtcMinutes, string UserId, DateTime fromDate, DateTime toDate, string state, string CustomerId)
        {
            try
            {
                CustomerReportModel model = new CustomerReportModel();

                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest(ConfigurationManager.AppSettings["CustomerReportApi"].ToString(), Method.POST);
                request.AddParameter("Token", Token);
                request.AddParameter("FromDate", fromDate.ToString(_dateTimeFormat));
                request.AddParameter("ToDate", toDate.ToString(_dateTimeFormat));
                request.AddParameter("UserId", UserId);
                request.AddParameter("state", state);
                request.AddParameter("CustomerId", CustomerId);
                request.AddParameter("utcminute", UtcMinutes);
                request.RequestFormat = DataFormat.Json;
                request.AddHeader("content-type", "application/json");
                var response = client.Execute(request);
                var content = response.Content;

                model = JsonConvert.DeserializeObject<CustomerReportModel>(content);

                return model;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetCustomerReport", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #region Consolidated Report

        /// <summary>
        /// Post method to get consolidated report
        /// </summary>
        /// <param name="registrationNo">vehicle registration no.</param>
        /// <param name="mobileNo">customer or owner mobile no.</param>
        /// <param name="complaintNo">ticket id</param>
        /// <returns>returns list of consolidated report</returns>
        public List<ReportOpenTicketModel> GetConsolidatedData(string userId, string startDate, string endDate, Int32 limit, Int32 offset, string queryType, string query, string organization, string vehicleTagging,string filterType,string consolidateType)
        {
            try
            {
                _LoginUserId = HttpContext.Current.Session["UserId"].ToString();
                _timeOffSetMinutes = HttpContext.Current.Session["UtcMinute"].ToString();
                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest("Api/Ticket?consolidateddata=hh&tickettype=" + queryType, Method.POST);
                request.AddParameter("Token", _securityToken);
                request.AddParameter("FromDate", Convert.ToDateTime(startDate).ToString(_dateTimeFormat));
                request.AddParameter("ToDate", Convert.ToDateTime(endDate).ToString(_dateTimeFormat));
                request.AddParameter("Limit", limit);
                request.AddParameter("Offset", offset);
                request.AddParameter("UserId", _LoginUserId);
                request.AddParameter("UtcMinute", _timeOffSetMinutes);
                request.AddParameter("Condition", query);
                request.AddParameter("VehicleType", vehicleTagging); 
                request.AddParameter("Organization", organization);        
                request.AddParameter("TicketType", filterType);
                request.AddParameter("consolidaetype", consolidateType);
        return client.Execute<List<ReportOpenTicketModel>>(request).Data;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetConsolidatedData", ex.Message, path, errorlogtf);
                throw ex;
            }
        
    
    }

        /// <summary>
        /// Method to get consolidated report (modified function) 07-10-2020
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="limit"></param>
        /// <param name="offset"></param>
        /// <param name="queryType"></param>
        /// <param name="query"></param>
        /// <param name="organization"></param>
        /// <param name="vehicleTagging"></param>
        /// <returns></returns>
        public dynamic GetConsolidatedDataForDownload(string userId, string startDate, string endDate, Int32 limit, Int32 offset, string queryType, string query, string organization, string vehicleTagging)
        {
            try
            {
                _LoginUserId = HttpContext.Current.Session["UserId"].ToString();
                _timeOffSetMinutes = HttpContext.Current.Session["UtcMinute"].ToString();
                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest("Api/Ticket?consolidateddata=hh&isCount=false&tickettype=" + queryType, Method.POST);
                request.AddParameter("Token", _securityToken);
                request.AddParameter("FromDate", Convert.ToDateTime(startDate).ToString(_dateTimeFormat));
                request.AddParameter("ToDate", Convert.ToDateTime(endDate).ToString(_dateTimeFormat));
                request.AddParameter("Limit", limit);
                request.AddParameter("Offset", offset);
                request.AddParameter("UserId", _LoginUserId);
                request.AddParameter("UtcMinute", _timeOffSetMinutes);
                request.AddParameter("Condition", query);
                request.AddParameter("VehicleType", vehicleTagging);
                request.AddParameter("Organization", organization);
                List<ReportOpenTicketModel> objT = new List<ReportOpenTicketModel>();
                var t = client.Execute<List<string>>(request).Data;
                var output = JsonConvert.DeserializeObject<dynamic>(t[0]);
                return output.ConsolidatedReportModelList;

            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetConsolidatedData", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        public ReportOpenTicketModel GetConsolidatedDataForDownloadCSV(string userId, string startDate, string endDate, Int32 limit, Int32 offset, string queryType, string query, string organization, string vehicleTagging)
        {
            try
            {
                _LoginUserId = HttpContext.Current.Session["UserId"].ToString();
                _timeOffSetMinutes = HttpContext.Current.Session["UtcMinute"].ToString();
                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest("Api/Ticket?consolidateddata=hh&isCount=false&Download=fh&tickettype=" + queryType, Method.POST);
                request.AddParameter("Token", _securityToken);
                request.AddParameter("FromDate", Convert.ToDateTime(startDate).ToString(_dateTimeFormat));
                request.AddParameter("ToDate", Convert.ToDateTime(endDate).ToString(_dateTimeFormat));
                request.AddParameter("Limit", limit);
                request.AddParameter("Offset", offset);
                request.AddParameter("UserId", _LoginUserId);
                request.AddParameter("UtcMinute", _timeOffSetMinutes);
                request.AddParameter("Condition", query);
                request.AddParameter("VehicleType", vehicleTagging);
                request.AddParameter("Organization", organization);
                return client.Execute<ReportOpenTicketModel>(request).Data;

            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetConsolidatedData", ex.Message, path, errorlogtf);
                throw ex;
            }
        }


    #endregion




    #region Consolidated Report

    /// <summary>
    /// Post method to get consolidated report
    /// </summary>
    /// <param name="registrationNo">vehicle registration no.</param>
    /// <param name="mobileNo">customer or owner mobile no.</param>
    /// <param name="complaintNo">ticket id</param>
    /// <returns>returns list of consolidated report</returns>
    public List<ReportOpenTicketModel> GetConsolidatedDataNew(string userId, string startDate, string endDate, Int32 limit, Int32 offset, string queryType, string query, string organization, string vehicleTagging, string filterType)
    {
      try
      {
        _LoginUserId = HttpContext.Current.Session["UserId"].ToString();
        _timeOffSetMinutes = HttpContext.Current.Session["UtcMinute"].ToString();
        var client = new RestSharp.RestClient(_RestClientUrl);
        var request = new RestRequest("Api/AddOnReport?consolidateddata=hh&tickettype=" + queryType, Method.POST);
        request.AddParameter("Token", _securityToken);
        request.AddParameter("FromDate", Convert.ToDateTime(startDate).ToString(_dateTimeFormat));
        request.AddParameter("ToDate", Convert.ToDateTime(endDate).ToString(_dateTimeFormat));
        request.AddParameter("Limit", limit);
        request.AddParameter("Offset", offset);
        request.AddParameter("UserId", _LoginUserId);
        request.AddParameter("UtcMinute", _timeOffSetMinutes);
        request.AddParameter("Condition", query);
        request.AddParameter("VehicleType", vehicleTagging);
        request.AddParameter("Organization", organization);
        request.AddParameter("TicketType", filterType);
        return client.Execute<List<ReportOpenTicketModel>>(request).Data;
      }
      catch (Exception ex)
      {
        objLog.ErrorLogFile("ReportRepository_GetConsolidatedData", ex.Message, path, errorlogtf);
        throw ex;
      }


    }

    
    #endregion

    #endregion

    #region KAM ticket reports

    /// <summary>
    /// Post method to get open tickets history
    /// </summary>
    /// <param name="registrationNo">vehicle registration no.</param>
    /// <param name="mobileNo">customer or owner mobile no.</param>
    /// <param name="complaintNo">ticket id</param>
    /// <returns>returns list of open tickets</returns>
    public TicketRequestModel GetKamTicketsList(string userId, string startDate, string endDate, Int32 limit, Int32 offset, string ticketStatus, string query)
        {
            try
            {
                _LoginUserId = HttpContext.Current.Session["UserId"].ToString();
                _timeOffSetMinutes = HttpContext.Current.Session["UtcMinute"].ToString();
                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest("Api/Ticket?kam=12&TicketType=" + ticketStatus, Method.POST);
                request.AddParameter("Token", _securityToken);
                request.AddParameter("FromDate", Convert.ToDateTime(startDate).ToString(_dateTimeFormat));
                request.AddParameter("ToDate", Convert.ToDateTime(endDate).ToString(_dateTimeFormat));
                request.AddParameter("Limit", limit);
                request.AddParameter("Offset", offset);
                request.AddParameter("UserId", null);
                request.AddParameter("UtcMinute", _timeOffSetMinutes);
                request.AddParameter("Condition", query);
                return client.Execute<TicketRequestModel>(request).Data;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetOpenTicketsList", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #region dummy ticket model

        /// <summary>
        /// Post method to get open tickets history
        /// </summary>
        /// <param name="registrationNo">vehicle registration no.</param>
        /// <param name="mobileNo">customer or owner mobile no.</param>
        /// <param name="complaintNo">ticket id</param>
        /// <returns>returns list of open tickets</returns>
        public TicketRequestModel GetDummyTicketsList(string userId)
        {
            try
            {
                _LoginUserId = HttpContext.Current.Session["UserId"].ToString();
                _timeOffSetMinutes = HttpContext.Current.Session["UtcMinute"].ToString();
                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest("Api/Ticket?dummyticket=1&Ticket=cce&TicketType=open", Method.POST);
                request.AddParameter("Token", _securityToken);
                request.AddParameter("UserId", _LoginUserId);
                return client.Execute<TicketRequestModel>(request).Data;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetOpenTicketsList", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion



        public List<EosOpportunityLoss> GetDataTableFromCsv(string path, bool isFirstRowHeader)
        {
            string header = isFirstRowHeader ? "Yes" : "No";
            List<EosOpportunityLoss> oEosOpportunityLoss = new List<EosOpportunityLoss>();
            string pathOnly = Path.GetDirectoryName(path);
            string fileName = Path.GetFileName(path);

            string sql = @"SELECT * FROM [" + fileName + "]";

            using (OleDbConnection connection = new OleDbConnection(
                      @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
                      ";Extended Properties=\"Text;HDR=" + header + "\""))
            using (OleDbCommand command = new OleDbCommand(sql, connection))
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
            {
                DataTable dataTable = new DataTable();
                dataTable.Locale = CultureInfo.CurrentCulture;
                adapter.Fill(dataTable);
                if (dataTable.Rows.Count > 0)
                {
                    oEosOpportunityLoss = dataTable.AsEnumerable().Select(s => new EosOpportunityLoss
                    {
                        month_name = s.Field<string>("month_name"),
                        StateId = s.Field<string>("breakdown_state_name"),
                        DealerName = s.Field<string>("dealer_id")
                    }).ToList();
                }
                return oEosOpportunityLoss;
            }
        }

        #region Driver Score Report

        #region Van score

        /// <summary>
        /// Post method to get open tickets history
        /// </summary>
        /// <returns>returns list of vans with score</returns>
        public ScoreRequestModel GetVanScoreList()
        {
            try
            {
                _LoginUserId = HttpContext.Current.Session["UserId"].ToString();
                _timeOffSetMinutes = HttpContext.Current.Session["UtcMinute"].ToString();
                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest("Api/Report?ReportType=allvanscore&CompareData=5rf3", Method.POST);
                request.AddParameter("Token", _securityToken);
                return client.Execute<ScoreRequestModel>(request).Data;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetOpenTicketsList", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        /// <summary>
        /// Post method to get open tickets history
        /// </summary>
        /// <returns>returns list of vans with score</returns>
        public ScoreRequestModel GetVansScoresCompareReport(string vans, string fromDate, string toDate)
        {
            try
            {
                _LoginUserId = HttpContext.Current.Session["UserId"].ToString();
                _timeOffSetMinutes = HttpContext.Current.Session["UtcMinute"].ToString();
                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest("Api/Report?ReportType=vanscore&CompareData=5rf3", Method.POST);
                request.AddParameter("VanId", vans);
                request.AddParameter("FromDate", Convert.ToDateTime(fromDate).ToString(_dateTimeFormat));
                request.AddParameter("ToDate", Convert.ToDateTime(toDate).ToString(_dateTimeFormat));
                request.AddParameter("Token", _securityToken);
                return client.Execute<ScoreRequestModel>(request).Data;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetOpenTicketsList", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #region dealer score


        /// <summary>
        /// Post method to get open tickets history
        /// </summary>
        /// <returns>returns list of vans with score</returns>
        public ScoreRequestModel GetDealerScoreList()
        {
            try
            {
                _LoginUserId = HttpContext.Current.Session["UserId"].ToString();
                _timeOffSetMinutes = HttpContext.Current.Session["UtcMinute"].ToString();
                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest("Api/Report?ReportType=alldealerscore&CompareData=5rf3", Method.POST);
                request.AddParameter("Token", _securityToken);
                return client.Execute<ScoreRequestModel>(request).Data;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetOpenTicketsList", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        /// <summary>
        /// Post method to get open tickets history
        /// </summary>
        /// <returns>returns list of vans with score</returns>
        public ScoreRequestModel GetDealersScoresCompareReport(string dealers, string fromDate, string toDate)
        {
            try
            {
                _LoginUserId = HttpContext.Current.Session["UserId"].ToString();
                _timeOffSetMinutes = HttpContext.Current.Session["UtcMinute"].ToString();
                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest("Api/Report?ReportType=dealerscore&CompareData=5rf3", Method.POST);
                request.AddParameter("DealerId", dealers);
                request.AddParameter("FromDate", Convert.ToDateTime(fromDate).ToString(_dateTimeFormat));
                request.AddParameter("ToDate", Convert.ToDateTime(toDate).ToString(_dateTimeFormat));
                request.AddParameter("Token", _securityToken);
                return client.Execute<ScoreRequestModel>(request).Data;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetOpenTicketsList", ex.Message, path, errorlogtf);
                throw ex;
            }
        }


        #endregion


        #region ticket score

        /// <summary>
        /// Post method to get open tickets history
        /// </summary>
        /// <param name="registrationNo">vehicle registration no.</param>
        /// <param name="mobileNo">customer or owner mobile no.</param>
        /// <param name="complaintNo">ticket id</param>
        /// <returns>returns list of open tickets</returns>
        public List<ReportOpenTicketModel> GetTicketScoreList(string userId, string startDate, string endDate, Int32 limit, Int32 offset, string ticketStatus, string query)
        {
            try
            {
                _LoginUserId = HttpContext.Current.Session["UserId"].ToString();
                _timeOffSetMinutes = HttpContext.Current.Session["UtcMinute"].ToString();
                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest("Api/Report?TicketType=" + ticketStatus, Method.POST);
                request.AddParameter("Token", _securityToken);
                request.AddParameter("FromDate", Convert.ToDateTime(startDate).ToString(_dateTimeFormat));
                request.AddParameter("ToDate", Convert.ToDateTime(endDate).ToString(_dateTimeFormat));
                request.AddParameter("Limit", limit);
                request.AddParameter("Offset", offset);
                request.AddParameter("UserId", _LoginUserId);
                request.AddParameter("UtcMinute", _timeOffSetMinutes);
                request.AddParameter("Condition", query);
                return client.Execute<List<ReportOpenTicketModel>>(request).Data;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetTicketScoreList", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        #endregion

        #endregion

        //------------------ added on 09-03-2021 start ---------------------------------

        public List<CancelledDraftTicketReportModel> GetCancelledDummyList()
        {
            try
            {
                _LoginUserId = HttpContext.Current.Session["UserId"].ToString();
                _timeOffSetMinutes = HttpContext.Current.Session["UtcMinute"].ToString();
                var client = new RestSharp.RestClient(_RestClientUrl);
                var request = new RestRequest("Api/Ticket?dummycancelledData=hh&dummycancelledData1=hh1&dummycancelledData2=hh2", Method.POST);
                request.AddParameter("Token", _securityToken);
                return client.Execute<List<CancelledDraftTicketReportModel>>(request).Data;
            }
            catch (Exception ex)
            {
                objLog.ErrorLogFile("ReportRepository_GetCancelledDummyList", ex.Message, path, errorlogtf);
                throw ex;
            }
        }

        //------------------ added on 09-03-2021 end ---------------------------------

    }
}