namespace VECV_WebApi.Models.Reports
{
    #region Namespaces

    using DBHelper;
    using LoggingHelper;
    using Npgsql;
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using VECV_WebApi.Models.Authorization;
    using VECV_WebApi.Models.Tracking;
    using VECV_WebApi.Models.ServiceEngineer;

    #endregion

    #region Repository Class

    /// <summary>
    /// This clas contain reports releted method
    /// </summary>
    public class ReportsRepository
    {
        #region Global Variables

        /// <summary>
        /// making object of LoggingUtility class available to this class
        /// </summary>
        LoggingUtility objLog = new LoggingUtility();

        /// <summary>
        /// making the data-log file path available to this class
        /// </summary>
        string path = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["PathLog"]);

        /// <summary>
        /// making error log file path available to this class
        /// </summary>
        string errorlogtf = (ConfigurationManager.AppSettings["ErrorLog"]);

        /// <summary>
        /// making the Database connection string available to this class
        /// </summary>
        private string _connStr = ConfigurationManager.ConnectionStrings["Vecv_GoData"].ToString();

        /// <summary>
        /// making Authentication Repository object available to this class
        /// </summary>
        AuthenticationRepository objAuthorization;

        #endregion

        #region Contructors

        /// <summary>
        /// Default constructor intialize connection string of tracking database 
        /// </summary>
        public ReportsRepository(string connString)
        {
            this._connStr = connString;
        }

        #endregion

        #region API Methods

        /// <summary>
        /// To get EOS call count report data
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>status and EOS call count report data</returns>
        public EosallCountRequestModel GetEosCallCountDetail(EosallCountRequestModel model)
        {
            List<EosCallCountModel> objList = new List<EosCallCountModel>();
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[8];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate);
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate);
                    nSqlParam[3] = new NpgsqlParameter("instate ", model.State);
                    nSqlParam[4] = new NpgsqlParameter("incity ", model.City);
                    nSqlParam[5] = new NpgsqlParameter("indealer_id ", model.DealerId);
                    nSqlParam[6] = new NpgsqlParameter("inlimit ", model.Limit);
                    nSqlParam[7] = new NpgsqlParameter("inoff_set ", model.Offset);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_eos_call_count"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        objList = ds.Tables[0].AsEnumerable().Select(s => new EosCallCountModel
                        {
                            MonthName = s.Field<string>("_month_name").Trim(),
                            NoOfCalls = s.Field<Int64?>("total_record_count"),
                            Top23Reach2Hrs = s.Field<Int64?>("top_23_toatal_record_reach_within_2hrs"),
                            NonTop23Reach2Hrs = s.Field<Int64?>("non_top_23_toatal_record_reach_within_2hrs"),
                            ArtTop23 = s.Field<decimal?>("top_23_avg_time"),
                            ArtNonTop23 = s.Field<decimal?>("non_top_23_avg_time"),
                            Top23CallsClosed24Hrs = s.Field<Int64?>("top_23_closer_less_then_24hrs"),
                            NonTop23CallsClosed24Hrs = s.Field<Int64?>("non_top_23_closer_less_then_24hrs")
                        }).ToList();

                    }
                    objModel.Status = "1";
                    objModel.EosList = objList;
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetEosCallCountDetail", Ex.Message, path, errorlogtf);
                return objModel;
            }
        }

        /// <summary>
        /// To get hd lmd bus contribution report data
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>hd lmd bus contribution report data</returns>
        public EosallCountRequestModel GetHdLmdBusContributionDetail(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[8];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("instate ", model.State);
                    nSqlParam[4] = new NpgsqlParameter("incity ", model.City);
                    nSqlParam[5] = new NpgsqlParameter("indealer_id ", model.DealerId);
                    nSqlParam[6] = new NpgsqlParameter("inlimit ", model.Limit);
                    nSqlParam[7] = new NpgsqlParameter("inoff_set ", model.Offset);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_hd_lmd_and_bus_contribution"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        //Get Hd,LMD,BUS call contribution list.
                        objModel.HdLmdBusContributionList = ds.Tables[0].AsEnumerable().Select(s => new HdLmdBusContribution
                        {
                            MonthName = s.Field<string>("_month_name").Trim(),
                            BusCalls = s.Field<Int64?>("_bus_ticket_count"),
                            BusPer = s.Field<decimal?>("_bus_percent"),
                            HDCalls = s.Field<Int64?>("_hd_ticket_count"),
                            HdPer = s.Field<decimal?>("_hd_percent"),
                            LMDCalls = s.Field<Int64?>("_lmd_ticket_count"),
                            LmdPer = s.Field<decimal?>("_lmd_percent"),
                            TotalCalls = s.Field<Int64?>("total_record_count"),
                            BusPer_ = s.Field<Int64?>("_bus_ticket_count") > 0 ? s.Field<decimal?>("_bus_percent").ToString() : "-",
                            HdPer_ = s.Field<Int64?>("_hd_ticket_count") > 0 ? s.Field<decimal?>("_hd_percent").ToString() : "-",
                            LmdPer_ = s.Field<Int64?>("_lmd_ticket_count") > 0 ? s.Field<decimal?>("_lmd_percent").ToString() : "-"
                        }).ToList();

                        //Get HD,LMD,BUS Call Closed List.
                        objModel.HdLmdBusclosedList = ds.Tables[0].AsEnumerable().Select(s => new HdLmdBusCallClosedPercentage
                        {
                            MonthName = s.Field<string>("_month_name").Trim(),
                            BusBeyond24Hrs = s.Field<Int64?>("_bus_closer_greater_then_24hrs_count"),
                            BusTotal = s.Field<Int64?>("_bus_closer_greater_then_24hrs_count") //== null 
                                // ? 0 : s.Field<Int64?>("_bus_closer_greater_then_24hrs_count")
                                      + s.Field<Int64?>("_bus_closer_less_then_24hrs_count"),//== null ? 0 : s.Field<Int64?>("_bus_closer_less_then_24hrs_count"),
                            BusWithIn24Hrs = s.Field<Int64?>("_bus_closer_less_then_24hrs_count"),
                            BusWithIn24HrsPer = s.Field<decimal?>("_bus_percent_closer_less_then_24hrs"),
                            HdBeyond24Hrs = s.Field<Int64?>("_hd_closer_greater_then_24hrs_count"),
                            HdTotal = s.Field<Int64?>("_hd_closer_greater_then_24hrs_count")// == null
                                //? 0 : s.Field<Int64?>("_hd_closer_greater_then_24hrs_count")
                                      + s.Field<Int64?>("_hd_closer_less_then_24hrs_count"),//== null ? 0 : s.Field<Int64?>("_hd_closer_less_then_24hrs_count"),
                            HdWithIn24Hrs = s.Field<Int64?>("_hd_closer_less_then_24hrs_count"),
                            HdWithIn24HrsPer = s.Field<decimal?>("_hd_percent_closer_less_then_24hrs"),
                            LmdBeyond24Hrs = s.Field<Int64?>("_lmd_closer_greater_then_24hrs_count"),
                            LmdTotal = s.Field<Int64?>("_lmd_closer_greater_then_24hrs_count") //== null
                                //? 0 : s.Field<Int64?>("_lmd_closer_greater_then_24hrs_count")
                                    + s.Field<Int64?>("_lmd_closer_less_then_24hrs_count"),//== null ? 0 : s.Field<Int64?>("_lmd_closer_less_then_24hrs_count"),
                            LmdWithIn24Hrs = s.Field<Int64?>("_lmd_closer_less_then_24hrs_count"),
                            LmdWithIn24HrsPer = s.Field<decimal?>("_lmd_percent_closer_less_then_24hrs"),

                            BusWithIn24HrsPer_ = s.Field<Int64?>("_bus_closer_less_then_24hrs_count") > 0 ? s.Field<decimal?>("_bus_percent_closer_less_then_24hrs").ToString() : "-",
                            HdWithIn24HrsPer_ = s.Field<Int64?>("_hd_closer_less_then_24hrs_count") > 0 ? s.Field<decimal?>("_hd_percent_closer_less_then_24hrs").ToString() : "-",
                            LmdWithIn24HrsPer_ = s.Field<Int64?>("_lmd_closer_less_then_24hrs_count") > 0 ? s.Field<decimal?>("_lmd_percent_closer_less_then_24hrs").ToString() : "-"

                        }).ToList();

                    }
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetHdLmdBusContributionDetail", Ex.Message, path, errorlogtf);
                return objModel;
            }
        }

        /// <summary>
        /// To get Dealer wise call detail report data
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>Dealer wise call detail report data</returns>
        public EosallCountRequestModel GetDealerWiseCallDetail(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[8];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("instate ", model.State);
                    nSqlParam[4] = new NpgsqlParameter("incity_name", model.City);
                    nSqlParam[5] = new NpgsqlParameter("indealer_id ", model.DealerId);
                    nSqlParam[6] = new NpgsqlParameter("inlimit ", model.Limit);
                    nSqlParam[7] = new NpgsqlParameter("inoff_set ", model.Offset);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_dealer_wise_call_details"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        //Get Dealer Wise call Details list.
                        objModel.DealerWiseCallDetailList = ds.Tables[0].AsEnumerable().Select(s => new DealerWiseCallDetail
                        {
                            BusCall = s.Field<Int64>("_bus_ticket_count"),
                            BusCallWithIn24Hrs = s.Field<Int64?>("_bus_closer_less_then_24hrs_count"),
                            BusCallWithIn24HrsPer = s.Field<decimal?>("_bus_percent_closer_less_then_24hrs"),
                            DealerName = s.Field<string>("dealer_dealer_name").Trim(),
                            HdCall = s.Field<Int64?>("_hd_ticket_count"),
                            HdCallWithIn24Hrs = s.Field<Int64?>("_hd_closer_less_then_24hrs_count"),
                            HdCallWithIn24HrsPer = s.Field<decimal?>("_hd_percent_closer_less_then_24hrs"),
                            LmdCall = s.Field<Int64?>("_lmd_ticket_count"),
                            LmdCallWithIn24Hrs = s.Field<Int64?>("_lmd_closer_less_then_24hrs_count"),
                            LmdCallWithIn24HrsPer = s.Field<decimal?>("_lmd_percent_closer_less_then_24hrs"),
                            TotalCall = s.Field<Int64?>("total_record_count"),
                            TotalCallWithIn24Hrs = s.Field<Int64?>("_total_closer_less_then_24hrs"),
                            TotalCallWithIn24HrsPer = s.Field<decimal?>("_total_percent_closer_less_then_24hrs"),

                            BusCallWithIn24HrsPer_ = s.Field<Int64?>("_bus_closer_less_then_24hrs_count") > 0 ? s.Field<decimal?>("_bus_percent_closer_less_then_24hrs").ToString() : "-",
                            HdCallWithIn24HrsPer_ = s.Field<Int64?>("_hd_closer_less_then_24hrs_count") > 0 ? s.Field<decimal?>("_hd_percent_closer_less_then_24hrs").ToString() : "-",
                            LmdCallWithIn24HrsPer_ = s.Field<Int64?>("_lmd_closer_less_then_24hrs_count") > 0 ? s.Field<decimal?>("_lmd_percent_closer_less_then_24hrs").ToString() : "-",
                            TotalCallWithIn24HrsPer_ = s.Field<Int64?>("_total_closer_less_then_24hrs") > 0 ? s.Field<decimal?>("_total_percent_closer_less_then_24hrs").ToString() : "-",
                        }).ToList();
                    }
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;

            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetDealerWiseCallDetail", Ex.Message, path, errorlogtf);
                return objModel;

            }
        }

        /// <summary>
        /// To get Time Slot Clouser detail report data
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>Time Slot Clouser detail report data</returns>
        public EosallCountRequestModel GetTimeSlotClouserDetail(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[8];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("instate ", model.State);
                    nSqlParam[4] = new NpgsqlParameter("incity ", model.City);
                    nSqlParam[5] = new NpgsqlParameter("indealer_id ", model.DealerId);
                    nSqlParam[6] = new NpgsqlParameter("inlimit ", model.Limit);
                    nSqlParam[7] = new NpgsqlParameter("inoff_set ", model.Offset);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_time_slot_wise"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        objModel.TimeSlotClouserDetailList = ds.Tables[0].AsEnumerable().Select(s => new TimeSlotClouser
                        {
                            TimeSlot = s.Field<string>("_time_slot_wise").Trim(),
                            Less24Hrs = s.Field<Int64?>("_less_then_24hrs"),
                            Less24HrsPer = s.Field<decimal?>("_less_then_24hrs_percent"),
                            Calls24HrsTo48Hrs = s.Field<Int64?>("_between_24_to_48hrs"),
                            Calls24HrsTo48HrsPer = s.Field<decimal?>("_between_24_to_48hrs_percent"),
                            Calls48HrsTo72Hrs = s.Field<Int64?>("_between_48_to_72hrs"),
                            Calls48HrsTo72HrsPer = s.Field<decimal?>("_between_48_to_72hrs_percent"),
                            Calls72HrsTo96Hrs = s.Field<Int64?>("_between_72_to_96hrs"),
                            Calls72HrsTo96HrsPer = s.Field<decimal?>("_between_72_to_96hrs_percent"),
                            Greater96Hrs = s.Field<Int64?>("_greater_then_96hrs"),
                            Greater96HrsPer = s.Field<decimal?>("_greater_then_96hrs_percent"),
                            GrandTotal = s.Field<Int64?>("_record_count1"),
                        }).ToList();
                    }
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetTimeSlotClouserDetail", Ex.Message, path, errorlogtf);
                return objModel;

            }
        }

        /// <summary>
        /// To get Reason for Call closed > 24hrs report data
        /// </summary>
        /// <param name="model">date rabge and user info</param>
        /// <returns>status and Reason for Call closed > 24hrs report data</returns>
        public EosallCountRequestModel GetReasonCallCloseBeyond24HrsDetail(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[8];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("instate ", model.State);
                    nSqlParam[4] = new NpgsqlParameter("incity ", model.City);
                    nSqlParam[5] = new NpgsqlParameter("indealer_id ", model.DealerId);
                    nSqlParam[6] = new NpgsqlParameter("inlimit ", model.Limit);
                    nSqlParam[7] = new NpgsqlParameter("inoff_set ", model.Offset);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_greater_then_24hrs_closer_reason"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        //Get Reason Call Closed Beyond 24Hrs List
                        objModel.ReasonCallClosedBeyond24HrsList = ds.Tables[0].AsEnumerable().Select(s => new ReasonCallClosedBeyond24Hrs
                        {
                            DealerName = s.Field<string>("dealer_dealer_name").Trim(),
                            FIP = s.Field<Int64?>("FIP"),
                            GearBoxProblem = s.Field<Int64?>("Gear Box Problem"),
                            Major = s.Field<Int64?>("Major"),
                            MajorDiffrentialProblem = s.Field<Int64?>("Major Differential Problem"),
                            MajorEngineProblem = s.Field<Int64?>("Major Engine Problem"),
                            Other = s.Field<Int64?>("Other"),
                            PaymentApproaval = s.Field<Int64?>("Payment Approval"),
                            SparePartsNotAvailable = s.Field<Int64?>("Spare Parts Not Available"),
                            TechnicianNotAvailable = s.Field<Int64?>("Technician Not Available"),
                            Total = s.Field<Int64?>("FIP") + s.Field<Int64?>("Gear Box Problem") + s.Field<Int64?>("Major") + s.Field<Int64?>("Major Differential Problem") +
                                         s.Field<Int64?>("Major Engine Problem") + s.Field<Int64?>("Payment Approval") + s.Field<Int64?>("Spare Parts Not Available") + s.Field<Int64?>("Technician Not Available") + s.Field<Int64?>("Other")

                        }).ToList();
                    }
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetReasonCallCloseBeyond24HrsDetail", Ex.Message, path, errorlogtf);
                return objModel;

            }
        }

        /// <summary>
        /// To get Opportunity loss report
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>Opportunity loss report</returns>
        public EosallCountRequestModel GetOpportunityLossReport(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[8];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("instate ", model.State);
                    nSqlParam[4] = new NpgsqlParameter("incity ", model.City);
                    nSqlParam[5] = new NpgsqlParameter("indealer_id ", model.DealerId);
                    nSqlParam[6] = new NpgsqlParameter("inlimit ", model.Limit);
                    nSqlParam[7] = new NpgsqlParameter("inoff_set ", model.Offset);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_opportunity_loss_reason"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        //Get Eos Opportunity Loss
                        objModel.EosOpportunityLoss = ds.Tables[0].AsEnumerable().Select(s => new EosOpportunityLoss
                        {
                            DealerTakingTooMuchTime = s.Field<Int64?>("dealer_taking_too_much_time"),
                            EosChargesHigh = s.Field<Int64?>("eos_charges_is_high"),
                            GrandTotal = s.Field<Int64?>("_total_count"),
                            LocalTechCome = s.Field<Int64?>("local_tech_came_himself_and_repair_the_vehicle"),
                            Other = s.Field<Int64?>("other"),
                            Region = s.Field<string>("region").Trim(),

                        }).ToList();
                    }
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetOpportunityLossReport", Ex.Message, path, errorlogtf);
                return objModel;
            }
        }

        /// <summary>
        /// To get Toll Free no Source report
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>status and Toll Free no Source report</returns>
        public EosallCountRequestModel GetTollFreeNoSourceReport(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[8];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("instate ", model.State);
                    nSqlParam[4] = new NpgsqlParameter("incity ", model.City);
                    nSqlParam[5] = new NpgsqlParameter("indealer_id ", model.DealerId);
                    nSqlParam[6] = new NpgsqlParameter("inlimit ", model.Limit);
                    nSqlParam[7] = new NpgsqlParameter("inoff_set ", model.Offset);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_toll_free_no_source"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        // Get Eos Toll Free Call Source report.
                        objModel.EosTollFreeCallSource = ds.Tables[0].AsEnumerable().Select(s => new EosTollFreeCallSource
                        {
                            AlreadyAware = s.Field<Int64?>("already_aware"),
                            CoDriver = s.Field<Int64?>("co_driver"),
                            Dealer = s.Field<Int64?>("_dealer"),
                            GrandTotal = s.Field<Int64?>("_total_count"),
                            Internet = s.Field<Int64?>("_internet"),
                            Maanger = s.Field<Int64?>("_manage"),
                            MonthName = s.Field<string>("month").Trim(),
                            Other = s.Field<Int64?>("_other"),
                            Owner = s.Field<Int64?>("_owner"),
                            ServiceBook = s.Field<Int64?>("service_book"),
                            SMS = s.Field<Int64?>("_sms"),
                            VehicleSticker = s.Field<Int64?>("vehicle_sticker"),
                        }).ToList();
                    }
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetTollFreeNoSourceReport", Ex.Message, path, errorlogtf);
                return objModel;
            }
        }

        /// <summary>
        /// To get LMD & HD - Call Closure % report
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>status and LMD & HD - Call Closure % report</returns>
        public EosallCountRequestModel GetHdLmdBusCallCloserReport(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[8];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("instate ", model.State);
                    nSqlParam[4] = new NpgsqlParameter("incity ", model.City);
                    nSqlParam[5] = new NpgsqlParameter("indealer_id ", model.DealerId);
                    nSqlParam[6] = new NpgsqlParameter("inlimit ", model.Limit);
                    nSqlParam[7] = new NpgsqlParameter("inoff_set ", model.Offset);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_hd_lmd_bus_call_closer"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        //Get Hd Lmd Bus Call CLoser details
                        objModel.HdLmdBusCallCLoser = ds.Tables[0].AsEnumerable().Select(s => new HdLmdBusCallCLoser
                        {
                            CallCountBeyond24Hrs = s.Field<Int64?>("_closer_greater_then_24hrs_count"),
                            CallCountLess24Hrs = s.Field<Int64?>("_closer_less_then_24hrs_count"),
                            CallPercentageBeyond24Hrs_ = s.Field<decimal?>("_closer_greater_then_24hrs_count_bus_percent") > 0 ? s.Field<decimal?>("_closer_greater_then_24hrs_count_bus_percent").ToString() : "-",
                            CallPercentageBeyond24Hrs = s.Field<decimal?>("_closer_greater_then_24hrs_count_bus_percent"),
                            CallPercentageLess24Hrs = s.Field<decimal?>("_closer_less_then_24hrs_count_precent"),
                            CallPercentageLess24Hrs_ = s.Field<decimal?>("_closer_less_then_24hrs_count_precent") > 0 ? s.Field<decimal?>("_closer_less_then_24hrs_count_precent").ToString() : "-",
                            Product = s.Field<string>("_vehicle_type").Trim(),
                            TotalCallCount = s.Field<Int64?>("_total_count")
                        }).ToList();
                    }
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetHdLmdBusCallCloserReport", Ex.Message, path, errorlogtf);
                return objModel;
            }
        }

        /// <summary>
        /// To get Reason for Call closed beyond 24hrs report
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>status and Reason for Call closed beyond 24hrs report</returns>
        public EosallCountRequestModel GetReasonWiseCallClosedBeyond24Hrs(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[8];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("instate ", model.State);
                    nSqlParam[4] = new NpgsqlParameter("incity ", model.City);
                    nSqlParam[5] = new NpgsqlParameter("indealer_id ", model.DealerId);
                    nSqlParam[6] = new NpgsqlParameter("inlimit ", model.Limit);
                    nSqlParam[7] = new NpgsqlParameter("inoff_set ", model.Offset);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_reason_call_closed_beyond_24hrs"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        // get Reason Wise Call Closed Beyond 24 Hrs.
                        objModel.ReasonWiseCallClosedBeyond24Hrs = ds.Tables[0].AsEnumerable().Select(s => new ReasonWiseCallClosedBeyond24Hrs
                        {
                            Buses = s.Field<Int64?>("bus"),
                            HD = s.Field<Int64?>("hd"),
                            LMD = s.Field<Int64?>("lmd"),
                            Reason = s.Field<string>("reason_name").Trim(),

                        }).ToList();
                    }
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetReasonWiseCallClosedBeyond24Hrs", Ex.Message, path, errorlogtf);
                return objModel;
            }
        }

        /// <summary>
        /// To get Eos call Count details.
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>status and Eos call Count details</returns>
        public EosallCountRequestModel GetEosFeedBackCallFeedBackParameter(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet dsCurrent = new DataSet();
                    DataSet dsPreviouse = new DataSet();
                    DataSet dsCurrentEos = new DataSet();
                    DataSet dsPreviouseEos = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[8];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("instate ", model.State);
                    nSqlParam[4] = new NpgsqlParameter("incity ", model.City);
                    nSqlParam[5] = new NpgsqlParameter("indealer_id ", model.DealerId);
                    nSqlParam[6] = new NpgsqlParameter("inlimit ", model.Limit);
                    nSqlParam[7] = new NpgsqlParameter("inoff_set ", model.Offset);
                    dsCurrent = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_call_feedback_rating"], nSqlParam);
                    dsCurrentEos = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_call_feedback_rating_percentage"], nSqlParam);

                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)).AddYears(-1));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)).AddYears(-1));
                    dsPreviouse = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_call_feedback_rating"], nSqlParam);
                    dsPreviouseEos = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_call_feedback_rating_percentage"], nSqlParam);

                    if (dsCurrent.Tables[0].Rows.Count > 0)
                    {
                        //get Current Year Eos Feedback details
                        objModel.CurrentYearEosFeedback = dsCurrent.Tables[0].AsEnumerable().Select(s => new EosCallFeedBackParameter
                        {

                            Category = "EOS",
                            EasyGettingCall = s.Field<decimal?>("_feedback_ease_of_getting_call"),
                            EosCharges = s.Field<decimal?>("_feedback_eos_charges"),
                            MonthName = s.Field<string>("_month_name").Trim(),
                            OverAllExperience = s.Field<decimal?>("_feedback_over_all_experience"),
                            RepairTime = s.Field<decimal?>("_feedback_total_repair_time"),
                            ResponseOfCall = s.Field<decimal?>("_feedback_response_of_call_center"),
                            TimelyUpdation = s.Field<decimal?>("_feedback_timely_updation_by_dealer")
                        }).ToList();

                    }

                    //get Previous Year Eos Feedback details
                    if (dsPreviouse.Tables[0].Rows.Count > 0)
                    {
                        objModel.PreviousYearEosFeedback = dsPreviouse.Tables[0].AsEnumerable().Select(s => new EosCallFeedBackParameter
                        {
                            Category = "EOS",
                            EasyGettingCall = s.Field<decimal?>("_feedback_ease_of_getting_call"),
                            EosCharges = s.Field<decimal?>("_feedback_eos_charges"),
                            MonthName = s.Field<string>("_month_name").Trim(),
                            OverAllExperience = s.Field<decimal?>("_feedback_over_all_experience"),
                            RepairTime = s.Field<decimal?>("_feedback_total_repair_time"),
                            ResponseOfCall = s.Field<decimal?>("_feedback_response_of_call_center"),
                            TimelyUpdation = s.Field<decimal?>("_feedback_timely_updation_by_dealer")
                        }).ToList();

                    }
                    //get Current Year Eos Feedback Rating details
                    if (dsCurrentEos.Tables[0].Rows.Count > 0)
                    {
                        objModel.CurrentYearEosFeedbackRating = dsCurrentEos.Tables[0].AsEnumerable().Select(s => new EosCallFeedBackRating
                        {
                            MonthName = s.Field<string>("_month_name").Trim(),
                            Eos = s.Field<decimal?>("month_feedback_percentage")
                        }).ToList();
                    }
                    //get Previous Year Eos Feedback Rating details
                    if (dsPreviouseEos.Tables[0].Rows.Count > 0)
                    {
                        objModel.PreviousYearEosFeedbackRating = dsPreviouseEos.Tables[0].AsEnumerable().Select(s => new EosCallFeedBackRating
                        {
                            MonthName = s.Field<string>("_month_name").Trim(),
                            Eos = s.Field<decimal?>("month_feedback_percentage")
                        }).ToList();
                    }
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetReasonWiseCallClosedBeyond24Hrs", Ex.Message, path, errorlogtf);
                return objModel;
            }
        }

        /// <summary>
        /// To get data for Geo Analysis Report.
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>status and data for Geo Analysis Report</returns>
        public EosallCountRequestModel GeoAnalysis(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            ServiceEngineerRepository objDealerVan = new ServiceEngineerRepository(_connStr);
            VanModel objModelVan = new VanModel();
            Analysis objAnalysis = new Analysis();

            List<GeoAnalysisTicketModel> objAnalysisModel = new List<GeoAnalysisTicketModel>();
            try
            {
                DataSet dsOpenTickets = new DataSet();
                DataSet dsClosedTickets = new DataSet();
                DataSet dsDealerVan = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    objModelVan.UserId = (Int64)model.UserId;
                    // Get Dealer Details for geo analysis report.
                    if (model.IsDealer.Trim().ToUpper() == "TRUE")
                    {
                        dsDealerVan = objDealerVan.GetVanDetailUserIdWise(objModelVan);

                        objAnalysisModel = dsDealerVan.Tables[0].AsEnumerable().Select(s => new GeoAnalysisTicketModel
                        {
                            Id = s.Field<string>("dealer_id"),
                            DealerName = s.Field<string>("dealer_dealer_name"),
                            City = s.Field<string>("dealer_city"),
                            State = s.Field<string>("dealer_state"),
                            DefaultLattitude = s.Field<string>("dealer_dealer_default_lattitude"),
                            DefaultLongitude = s.Field<string>("dealer_dealer_default_longitude"),
                            OrganizationName = s.Field<string>("_organization_name"),
                            DealerContact = s.Field<string>("dealer_contact_number1")
                        }).ToList();
                        objAnalysisModel.RemoveAll(item => String.IsNullOrEmpty(item.DealerName));

                        objAnalysis.lsDealerDetl = objAnalysisModel;
                    }
                    // Get VAN Details for geo analysis report.
                    if (model.IsVan.Trim().ToUpper() == "TRUE")
                    {
                        dsDealerVan = objDealerVan.GetVanDetailUserIdWise(objModelVan);
                        objAnalysisModel = dsDealerVan.Tables[0].AsEnumerable().Select(s => new GeoAnalysisTicketModel
                        {
                            Id = s.Field<string>("van_id"),
                            DefaultLattitude = s.Field<string>("van_van_default_lattitude"),
                            DefaultLongitude = s.Field<string>("van_van_default_longitude"),
                            City = s.Field<string>("van_city"),
                            State = s.Field<string>("van_state"),
                            VanVanModelNo = s.Field<string>("van_van_model"),
                            VanRegistrationNumber = s.Field<string>("van_registration_number"),
                            VanVanRegisteredAs = s.Field<string>("van_van_registered_as"),
                            OrganizationName = s.Field<string>("_organization_name"),
                            ServiceEnggName = s.Field<string>("_service_engineer_name"),
                            ServiceEnggContactNumber = s.Field<string>("service_engineer_contact_number"),
                            DealerName = s.Field<string>("dealer_dealer_name"),
                            DealerContact = s.Field<string>("dealer_contact_number1")

                        }).ToList();
                        objAnalysisModel.RemoveAll(item => String.IsNullOrEmpty(item.ServiceEnggName));
                        objAnalysis.lsEOSVAN = objAnalysisModel;
                    }

                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[4];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("instate", model.State);

                    // Get OpenTicket Details for geo analysis report.
                    if (model.IsOpenTicket.Trim().ToUpper() == "TRUE")
                    {
                        dsOpenTickets = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_geo_open_tickets"], nSqlParam);
                        objAnalysisModel = dsOpenTickets.Tables[0].AsEnumerable().Select(s => new GeoAnalysisTicketModel
                        {
                            Id = s.Field<string>("_ticket_id"),
                            CustomerName = s.Field<string>("_customer_name"),
                            CustomerContactNumber = s.Field<string>("_customer_contact_no"),
                            CreationTime = s.Field<DateTime>("_creation_time") != null ? Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).AddMinutes(model.UtcMinute).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]) : Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            DealerName = s.Field<string>("_dealer_name"),
                            AssignTo = s.Field<string>("_assigned_to"),
                            SlaMet = s.Field<Boolean>("sla_met").ToString(),
                            ServiceEnggName = s.Field<string>("_van_dealer_name"),
                            DealerContact = s.Field<string>("_dealer_contact_number1"),
                            TicketStatusAlias = s.Field<string>("_ticket_status_alias"),
                            VehicleModelTagging = s.Field<string>("_customer_vehicle_model_tagging"),
                            DefaultLattitude = s.Field<string>("_breakdown_lattitude"),
                            DefaultLongitude = s.Field<string>("_breakdown_longitude"),
                            State = s.Field<string>("_state_name"),
                            TicketIdAlias = s.Field<string>("_ticket_id_alias")
                        }).ToList();

                        objAnalysis.lsOpenTicket = objAnalysisModel;
                    }

                    // Get Closed Tickets Details for geo analysis report.
                    if (model.IsClosedTickets.Trim().ToUpper() == "TRUE")
                    {
                        dsClosedTickets = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_geo_closed_tickets"], nSqlParam);

                        objAnalysisModel = dsClosedTickets.Tables[0].AsEnumerable().Select(s => new GeoAnalysisTicketModel
                        {
                            Id = s.Field<string>("_ticket_id"),
                            CustomerName = s.Field<string>("_customer_name"),
                            CustomerContactNumber = s.Field<string>("_customer_contact_no"),
                            CreationTime = s.Field<DateTime>("_creation_time") != null ? Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).AddMinutes(model.UtcMinute).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]) : Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            DealerName = s.Field<string>("_dealer_name"),
                            AssignTo = s.Field<string>("_assigned_to"),
                            SlaMet = s.Field<Boolean>("sla_met").ToString(),
                            ServiceEnggName = s.Field<string>("_van_dealer_name"),
                            DealerContact = s.Field<string>("_dealer_contact_number1"),
                            TicketStatusAlias = s.Field<string>("_ticket_status_alias"),
                            VehicleModelTagging = s.Field<string>("_customer_vehicle_model_tagging"),
                            DefaultLattitude = s.Field<string>("_breakdown_lattitude"),
                            DefaultLongitude = s.Field<string>("_breakdown_longitude"),
                            State = s.Field<string>("_state_name"),
                            TicketIdAlias = s.Field<string>("_ticket_id_alias")
                        }).ToList();

                        objAnalysis.lsCloseTicket = objAnalysisModel;
                    }
                    objModel.GeoAnalysisModelReport = objAnalysis;
                    objModel.Status = "1";
                }
                else
                {

                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                    objModel.Status = "0";
                }
            }
            catch (Exception Ex)
            {
                objLog.ErrorLogFile("GetOpenCloseTicketDetail", Ex.Message, path, errorlogtf);
                objModel.Message = Ex.Message;
                objModel.Status = "0";
            }
            return objModel;
        }





        /// <summary>
        /// To get data for Geo Analysis Report Route Wise.
        /// </summary>
        /// <param name="model">date range and Route Id</param>
        /// <returns>status and data for Geo Analysis Report</returns>
        public EosallCountRequestModel GeoRouteAnalysis(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            ServiceEngineerRepository objDealerVan = new ServiceEngineerRepository(_connStr);
            VanModel objModelVan = new VanModel();
            Analysis objAnalysis = new Analysis();

            List<GeoAnalysisTicketModel> objAnalysisModel = new List<GeoAnalysisTicketModel>();
            try
            {
                DataSet dsClosedTickets = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {

                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[4];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("inroute_id", model.RouteId);


                    // Get Closed Tickets Details for geo analysis report.

                    dsClosedTickets = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_geo_closed_tickets_by_route_wise"], nSqlParam);

                    objAnalysisModel = dsClosedTickets.Tables[0].AsEnumerable().Select(s => new GeoAnalysisTicketModel
                    {

                        TicketIdAlias = s.Field<string>("_ticket_id_alias"),
                        AssignTo = s.Field<string>("_assigned_to"),
                        CreationTime = s.Field<DateTime>("_creation_time") != null ? Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).AddMinutes(model.UtcMinute).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]) : Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                        Route = s.Field<string>("_route_alias"),
                        VanRegistrationNumber = s.Field<string>("_van_dealer_name"),
                        State = s.Field<string>("_state_name"),
                        City = s.Field<string>("_city_name"),
                        CustomerName = s.Field<string>("_customer_name"),
                        CustomerContactNumber = s.Field<string>("_customer_contact_no"),
                        DealerName = s.Field<string>("_dealer_name"),
                        DealerContact = s.Field<string>("_dealer_contact_number1"),
                        VehicleModelTagging = s.Field<string>("_customer_vehicle_model_tagging"),
                        SlaMet = s.Field<Boolean>("sla_met").ToString(),
                        ServiceEnggName = s.Field<string>("_van_dealer_name"),
                        TicketStatusAlias = s.Field<string>("_ticket_status_alias"),
                        DefaultLattitude = s.Field<string>("_breakdown_lattitude"),
                        DefaultLongitude = s.Field<string>("_breakdown_longitude"),
                        Id = s.Field<string>("_ticket_id"),
                    }).ToList();

                    objAnalysis.lsCloseTicket = objAnalysisModel;

                    objModel.GeoAnalysisModelReport = objAnalysis;
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                    objModel.Status = "0";
                }
            }
            catch (Exception Ex)
            {
                objLog.ErrorLogFile("GetCloseTicketDetailByRouteWise", Ex.Message, path, errorlogtf);
                objModel.Message = Ex.Message;
                objModel.Status = "0";
            }
            return objModel;
        }


        /// <summary>
        /// To get data for Average Response Time / Average Closure Time For Geo Analysis Report.
        /// </summary>
        /// <param name="model">date range and Route Id</param>
        /// <returns>status and data for Geo Analysis Report</returns>
        public EosallCountRequestModel CountGeoRouteAnalysis(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            ServiceEngineerRepository objDealerVan = new ServiceEngineerRepository(_connStr);
            VanModel objModelVan = new VanModel();
            Analysis objAnalysis = new Analysis();

            List<GeoAnalysisTicketModel> objAnalysisModel = new List<GeoAnalysisTicketModel>();
            try
            {
                DataSet dsClosedTickets = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {

                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[4];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("inroute_id", model.RouteId);


                    // Get Closed Tickets Details for geo analysis report.

                    dsClosedTickets = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_geo_analysis_kpi_count"], nSqlParam);

                    objAnalysisModel = dsClosedTickets.Tables[0].AsEnumerable().Select(s => new GeoAnalysisTicketModel
                    {
                        Key = s.Field<string>("_key"),
                        KeyName = s.Field<string>("_key_name"),
                        KeyValue = s.Field<string>("_key_value"),
                    }).ToList();

                    objAnalysis.lsCloseTicket = objAnalysisModel;

                    objModel.GeoAnalysisModelReport = objAnalysis;
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                    objModel.Status = "0";
                }
            }
            catch (Exception Ex)
            {
                objLog.ErrorLogFile("CountGeoRouteAnalysis", Ex.Message, path, errorlogtf);
                objModel.Message = Ex.Message;
                objModel.Status = "0";
            }
            return objModel;
        }






        /// <summary>
        /// To get dealer wise decline report.
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>status and dealer wise decline report</returns>
        public EosallCountRequestModel GetDeclinedReport(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[8];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("instate ", model.State);
                    nSqlParam[4] = new NpgsqlParameter("incity ", model.City);
                    nSqlParam[5] = new NpgsqlParameter("indealer_id ", model.DealerId);
                    nSqlParam[6] = new NpgsqlParameter("inlimit ", model.Limit);
                    nSqlParam[7] = new NpgsqlParameter("inoff_set ", model.Offset);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_dealer_wise_declined_ticket"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        objModel.DeclinedReportsDealerWise = ds.Tables[0].AsEnumerable().Select(s => new DeclinedReports
                        {
                            DealerName = s.Field<string>("_dealer_name"),
                            TicketId = s.Field<string>("_tickets_closed_id"),
                            CreationDate = s.Field<DateTime>("_ticket_creation_date") != null ? Convert.ToDateTime(s.Field<DateTime?>("_ticket_creation_date")).AddMinutes(model.UtcMinute).ToString(ConfigurationManager.AppSettings["DateFormat"]) : Convert.ToDateTime(s.Field<DateTime?>("_ticket_creation_date")).ToString(ConfigurationManager.AppSettings["DateFormat"]),
                            CreationTime = s.Field<DateTime>("_ticket_creation_date") != null ? Convert.ToDateTime(s.Field<DateTime?>("_ticket_creation_date")).AddMinutes(model.UtcMinute).ToString(ConfigurationManager.AppSettings["TimeFormat"]) : Convert.ToDateTime(s.Field<DateTime?>("_ticket_creation_date")).ToString(ConfigurationManager.AppSettings["TimeFormat"]),
                            VehicleRegNo = s.Field<string>("_vehicle_registration_number"),
                            DealerId = s.Field<string>("_dealer_dealer_id"),
                            ServiceEngg = s.Field<string>("_dealer_van_name"),
                            VehicleModelTag = s.Field<string>("_vehicle_model_tagging"),
                            DeclinedReason = s.Field<string>("_remarks"),
                            VanRegNo = s.Field<string>("_registration_number"),
                            Organization = s.Field<string>("_organization_organization_region"),
                            TicketIdAlias = s.Field<string>("_ticket_id_alias")
                        }).ToList();
                    }
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetReasonWiseCallClosedBeyond24Hrs", Ex.Message, path, errorlogtf);
                return objModel;
            }
        }

        /// <summary>
        /// To get eicher promise report.
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>status and eicher promise report</returns>
        public EosallCountRequestModel GetPromiseReport(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[8];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("instate ", model.State);
                    nSqlParam[4] = new NpgsqlParameter("incity ", model.City);
                    nSqlParam[5] = new NpgsqlParameter("indealer_id ", model.DealerId);
                    nSqlParam[6] = new NpgsqlParameter("inlimit ", model.Limit);
                    nSqlParam[7] = new NpgsqlParameter("inoff_set ", model.Offset);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_eicher_promise"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        //Get eicher promise report.
                        objModel.PromiseReportsList = ds.Tables[0].AsEnumerable().Select(s => new PromiseReports
                        {
                            DealerName = s.Field<string>("_dealer_name"),
                            TicketId = s.Field<string>("_tickets_closed_id"),
                            CreationDate = s.Field<DateTime>("_creation_time") != null ? Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).AddMinutes(model.UtcMinute).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]) : Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            ClosedTime = s.Field<DateTime>("_last_modified_time") != null ? Convert.ToDateTime(s.Field<DateTime?>("_last_modified_time")).AddMinutes(model.UtcMinute).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]) : Convert.ToDateTime(s.Field<DateTime?>("_last_modified_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            VehicleRegNo = s.Field<string>("_vehicle_registration_number"),
                            ServiceEngg = s.Field<string>("dealer_dealer_name"),
                            VehicleModelTag = s.Field<string>("customer_vehicle_model_tagging"),
                            DeclinedReason = s.Field<string>("_reason_for_more_then_24hours"),
                            VanRegNo = s.Field<string>("van_registration_number"),
                            Organization = s.Field<string>("_organization_region"),
                            Description = s.Field<string>("_description"),
                            TicketIdAlias = s.Field<string>("_ticket_id_alias")
                        }).ToList();
                    }
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("PromiseReport", Ex.Message, path, errorlogtf);
                return objModel;
            }
        }

        /// <summary>
        /// To get Customer report.
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>status and Customer report</returns>
        public EosallCountRequestModel GetCustomerReport(EosallCountRequestModel model)
        {
            EosallCountRequestModel objModel = new EosallCountRequestModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[6];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[3] = new NpgsqlParameter("instate ", model.State);
                    nSqlParam[4] = new NpgsqlParameter("incustomer_id ", model.CustomerId);
                    nSqlParam[5] = new NpgsqlParameter("incondition ", model.Condition);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_report_eicher_customer"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        objModel.CustomerReportsList = ds.Tables[0].AsEnumerable().Select(s => new CustomerReports
                        {
                            // get customer report.   
                            DealerName = s.Field<string>("dealer"),
                            TicketId = s.Field<string>("_tickets_closed_id"),
                            CreationDate = s.Field<DateTime>("_creation_time") != null ? Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).AddMinutes(model.UtcMinute).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]) : Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            VehicleRegNo = s.Field<string>("_vehicle_reg_number"),
                            ServiceEngg = s.Field<string>("eos_member"),
                            VehicleModelTag = s.Field<string>("_model_tagging"),
                            Organization = s.Field<string>("_organization_region"),
                            Description = s.Field<string>("_description"),
                            BreakdownLocation = s.Field<string>("_breakdown_location"),
                            City = s.Field<string>("_breakdown_city_name"),
                            State = s.Field<string>("_breakdown_state_name"),
                            TicketLifeCycleTime = s.Field<int?>("ticket_lifecycle_time"),
                            DefaultSlaTime = s.Field<int?>("_default_sla_time"),
                            DealerContact = s.Field<string>("_dealer_contact"),
                            CustomerName = s.Field<string>("_owner_name"),
                            CustomerContact = s.Field<string>("_owner_contact"),
                            DriverName = s.Field<string>("_customer_name"),
                            ModelNo = s.Field<string>("vehicle_model_number"),
                            ResponseSlaTime = s.Field<int?>("_response"),
                            TicketIdAlias = s.Field<string>("_ticket_id_alias")
                        }).ToList();
                    }
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetCustomerReport", Ex.Message, path, errorlogtf);
                return objModel;
            }
        }

        /// <summary>
        /// To get close ticket detail user id wise
        /// </summary>
        /// <param name="model">ticket info</param>
        /// <returns>status and open and close ticket detail user id wise</returns>
        public TicketRequestModel GetCloseTicketDetail(TicketRequestModel model)
        {
            TicketRequestModel objModel = new TicketRequestModel();
            List<TicketListForScore> objList = new List<TicketListForScore>();
            try
            {
                DataSet ds = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    // Get Open ticket report details.
                    if (model.TicketType.Trim().ToLower() == "close")
                    {
                        // Get closed ticket report details.
                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[6];
                        nSqlParam[0] = new NpgsqlParameter("inuser_id", model.UserId);
                        nSqlParam[1] = new NpgsqlParameter("instart_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam[2] = new NpgsqlParameter("inend_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam[3] = new NpgsqlParameter("inlimit", model.Limit);
                        nSqlParam[4] = new NpgsqlParameter("inoff_set", model.OffSet);
                        nSqlParam[5] = new NpgsqlParameter("incondition", model.Condition);
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_closed_tickets_user_wise"], nSqlParam);
                    }
                    if (model.TicketType.Trim().ToLower() == "close")
                    {
                        objList = ds.Tables[0].AsEnumerable().Select(s => new TicketListForScore
                        {
                            Id = s.Field<string>("_ticket_id"),
                            DefaultSlaTime = s.Field<int?>("_default_sla_time"),
                            CustomeContact_no = s.Field<string>("_customer_contact_no"),
                            TotalTicketLifeCycleTimeSlab = s.Field<int?>("_total_ticket_lifecycle_time_sla") == null || s.Field<int?>("_total_ticket_lifecycle_time_sla") <= 0 ? 0 : s.Field<int?>("_total_ticket_lifecycle_time_sla"),
                            ServiceEngineerName = s.Field<string>("_van_dealer_name"),
                            DealerDealerName = s.Field<string>("_dealer_name"),
                            ServiceEngineerContactNumber = s.Field<string>("_dealer_contact_number1"),
                            TicketStatusAlias = s.Field<string>("_ticket_status_alias"),
                            VehicleTagging = s.Field<string>("_customer_vehicle_model_tagging"),
                            CustomerName = s.Field<string>("_customer_name"),
                            CreationTime = Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).AddMinutes(Convert.ToInt16(model.UtcMinute)).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            LastModifiedTime = Convert.ToDateTime(s.Field<DateTime?>("_lastmodifytime")).AddMinutes(Convert.ToInt16(model.UtcMinute)).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            AssignedTo = s.Field<string>("_assignedto"),
                            EstimatedTimeForJobComplition = s.Field<int?>("_estimatedtimeforjobcompletion"),
                            Age = Convert.ToDouble(s.Field<string>("_age")),
                            DefaultCol2 = model.TicketType.Trim().ToLower() == "close" ? s.Field<string>("_feedbackagent") : null,
                            DefaultCol3 = model.TicketType.Trim().ToLower() == "close" ? s.Field<string>("_reasonformorethen24hours") : null,
                            CallStatus = s.Field<string>("_call_status"),
                            VehicleRegistrationNo = s.Field<string>("_vehicle_registration_number"),
                            ticketid_alias = s.Field<string>("_ticket_id_alias"),
                            caller_language = s.Field<string>("_caller_language"),
                            feedback_call_status = s.Field<string>("_feedback_status"),
                            total_count = Convert.ToString(s.Field<Int32?>("_feedback_count")),
                            Score = s.Field<string>("_score")
                        }).ToList();
                    }
                    objModel.TicketScoreList = objList;
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                    objModel.Status = "0";
                }
            }
            catch (Exception Ex)
            {
                objLog.ErrorLogFile("GetCloseTicketDetail", Ex.Message, path, errorlogtf);
                objModel.Message = Ex.Message;
                objModel.Status = "0";
            }
            return objModel;
        }




        /// <summary>
        /// To get close ticket detail user id wise
        /// </summary>
        /// <param name="model">ticket info</param>
        /// <returns>status and open and close ticket detail user id wise</returns>
        public TicketRequestModel GetCloseTicketScoresDetail(TicketRequestModel model)
        {
            TicketRequestModel objModel = new TicketRequestModel();
            List<TicketListForScore> objList = new List<TicketListForScore>();
            try
            {
                DataSet ds = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    // Get Open ticket report details.
                    if (model.TicketType.Trim().ToLower() == "close")
                    {
                        // Get closed ticket report details.
                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[6];
                        nSqlParam[0] = new NpgsqlParameter("inuser_id", model.UserId);
                        nSqlParam[1] = new NpgsqlParameter("instart_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam[2] = new NpgsqlParameter("inend_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam[3] = new NpgsqlParameter("inlimit", model.Limit);
                        nSqlParam[4] = new NpgsqlParameter("inoff_set", model.OffSet);
                        nSqlParam[5] = new NpgsqlParameter("incondition", model.Condition);
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["uanalytics_data_sp_get_closed_tickets_user_wise"], nSqlParam);
                    }
                    else if (model.TicketType.Trim().ToLower() == "count")
                    {
                        // Get closed ticket record count.
                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[6];
                        nSqlParam[0] = new NpgsqlParameter("inuser_id", model.UserId);
                        nSqlParam[1] = new NpgsqlParameter("instart_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam[2] = new NpgsqlParameter("inend_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam[3] = new NpgsqlParameter("inlimit", model.Limit);
                        nSqlParam[4] = new NpgsqlParameter("inoff_set", model.OffSet);
                        nSqlParam[5] = new NpgsqlParameter("incondition", model.Condition);
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["uanalytics_data_sp_get_closed_tickets_count_user_wise"], nSqlParam);
                    }
                    if (model.TicketType.Trim().ToLower() == "close")
                    {
                        objList = ds.Tables[0].AsEnumerable().Select(s => new TicketListForScore
                        {
                            Id = s.Field<string>("_ticket_id"),
                            DefaultSlaTime = s.Field<int?>("_default_sla_time"),
                            CustomeContact_no = s.Field<string>("_customer_contact_no"),
                            TotalTicketLifeCycleTimeSlab = s.Field<int?>("_total_ticket_lifecycle_time_sla") == null || s.Field<int?>("_total_ticket_lifecycle_time_sla") <= 0 ? 0 : s.Field<int?>("_total_ticket_lifecycle_time_sla"),
                            ServiceEngineerName = s.Field<string>("_van_dealer_name"),
                            DealerDealerName = s.Field<string>("_dealer_name"),
                            ServiceEngineerContactNumber = s.Field<string>("_dealer_contact_number1"),
                            TicketStatusAlias = s.Field<string>("_ticket_status_alias"),
                            VehicleTagging = s.Field<string>("_customer_vehicle_model_tagging"),
                            CustomerName = s.Field<string>("_customer_name"),
                            CreationTime = Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).AddMinutes(Convert.ToInt16(model.UtcMinute)).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            LastModifiedTime = Convert.ToDateTime(s.Field<DateTime?>("_lastmodifytime")).AddMinutes(Convert.ToInt16(model.UtcMinute)).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            AssignedTo = s.Field<string>("_assignedto"),
                            EstimatedTimeForJobComplition = s.Field<int?>("_estimatedtimeforjobcompletion"),
                            Age = Convert.ToDouble(s.Field<string>("_age")),
                            DefaultCol2 = model.TicketType.Trim().ToLower() == "close" ? s.Field<string>("_feedbackagent") : null,
                            DefaultCol3 = model.TicketType.Trim().ToLower() == "close" ? s.Field<string>("_reasonformorethen24hours") : null,
                            CallStatus = s.Field<string>("_call_status"),
                            VehicleRegistrationNo = s.Field<string>("_vehicle_registration_number"),
                            ticketid_alias = s.Field<string>("_ticket_id_alias"),
                            caller_language = s.Field<string>("_caller_language"),
                            feedback_call_status = s.Field<string>("_feedback_status"),
                            total_count = Convert.ToString(s.Field<Int32?>("_feedback_count")),
                            Score = s.Field<string>("_score")
                        }).ToList();
                    }
                    if (model.TicketType.Trim().ToLower() == "count")
                    {
                        objModel.TicketCount = Convert.ToInt64(ds.Tables[0].Rows[0][0]);
                    }
                    objModel.TicketScoreList = objList;
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                    objModel.Status = "0";
                }
            }
            catch (Exception Ex)
            {
                objLog.ErrorLogFile("GetCloseTicketDetail", Ex.Message, path, errorlogtf);
                objModel.Message = Ex.Message;
                objModel.Status = "0";
            }
            return objModel;
        }

        /// <summary>
        /// To get close ticket detail user id wise
        /// </summary>
        /// <param name="model">ticket info</param>
        /// <returns>status and open and close ticket detail user id wise</returns>
        public TicketRequestModel GetCloseTicketList(TicketRequestModel model)
        {
            TicketRequestModel objModel = new TicketRequestModel();
            List<EosScoreCardModel> objList = new List<EosScoreCardModel>();
            try
            {
                DataSet ds = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    // Get closed ticket report details.
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[3];
                    nSqlParam[0] = new NpgsqlParameter("inticket_alias", model.TicketAlias);
                    nSqlParam[1] = new NpgsqlParameter("infrom_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    nSqlParam[2] = new NpgsqlParameter("into_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["uanalytics_data_sp_get_close_tickets_by_ticket_id"], nSqlParam);

                    objList = ds.Tables[0].AsEnumerable().Select(s => new EosScoreCardModel
                    {
                        TicketId = s.Field<string>("_id"),
                        TicketAlias = s.Field<string>("_ticketid_alias"),
                        Description = s.Field<string>("_description"),
                        AssignedTo = s.Field<string>("_assigned_to"),
                        CreationTime = Convert.ToDateTime(s.Field<string>("_creation_time")).AddMinutes(Convert.ToInt16(model.UtcMinute)).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                        BreakdownLocation = s.Field<string>("_breakdown_location"),
                        AssignedToUserId = s.Field<string>("_assigned_to_user_id"),
                        RegistrationNumber = s.Field<string>("_vehicle_registration_number"),
                        BreakdownLocationLandmark = s.Field<string>("_breakdown_location_landmark"),
                        CustomerContactNumber = s.Field<string>("_customer_contact_no")
                    }).ToList();
                }
                else
                {
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                    objModel.Status = "0";
                }
                objModel.ClosedTicketList = objList;
                objModel.Status = "1";

            }
            catch (Exception Ex)
            {
                objLog.ErrorLogFile("GetCloseTicketList", Ex.Message, path, errorlogtf);
                objModel.Message = Ex.Message;
                objModel.Status = "0";
            }
            return objModel;
        }

        /// <summary>
        /// To get close ticket detail user id wise
        /// </summary>
        /// <param name="model">ticket info</param>
        /// <returns>status and open and close ticket detail user id wise</returns>
        public ScoreRequestModel GetScoreCardReport(ScoreRequestModel model)
        {
            ScoreRequestModel objModel = new ScoreRequestModel();
            List<EosScoreCardModel> objList = new List<EosScoreCardModel>();
            try
            {
                DataSet ds = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    // Get Open ticket report details.
                    if (model.ReportType.Trim().ToLower() == "dealerscore")
                    {
                        // Get closed ticket report details.
                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[3];
                        nSqlParam[0] = new NpgsqlParameter("indealer_id", model.DealerId);
                        nSqlParam[1] = new NpgsqlParameter("infrom_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam[2] = new NpgsqlParameter("into_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["uanalytics_data_sp_get_dealers_current_score_by_dealer_wise"], nSqlParam);
                    }
                    else if (model.ReportType.Trim().ToLower() == "vanscore")
                    {
                        // Get closed ticket report details.
                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[3];
                        nSqlParam[0] = new NpgsqlParameter("invan_id", model.VanId);
                        nSqlParam[1] = new NpgsqlParameter("infrom_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam[2] = new NpgsqlParameter("into_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["uanalytics_data_sp_get_vans_current_score_by_van_wise"], nSqlParam);
                    }

                    else if (model.ReportType.Trim().ToLower() == "allvanscore")
                    {
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["uanalytics_data_sp_get_vans_list_with_current_score"]);
                    }
                    else if (model.ReportType.Trim().ToLower() == "alldealerscore")
                    {
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["uanalytics_data_sp_get_dealers_list_with_current_score"]);
                    }

                    if (model.ReportType.Trim().ToLower() == "dealerscore")
                    {
                        List<List<string>> score = new List<List<string>>();
                        List<string> date = new List<string>();
                        objList = ds.Tables[0].AsEnumerable().Select(s => new EosScoreCardModel
                        {
                            DealerId = s.Field<string>("_dealer_id"),
                            OrganizationId = s.Field<string>("_organization_id"),
                            OrganizationName=s.Field<string>("_organization_name"),
                            DealerName = s.Field<string>("_dealer_name"),
                            DealerCity = s.Field<string>("_dealer_city"),
                            DealerState = s.Field<string>("_dealer_state"),
                            DealerContactNumber = s.Field<string>("_contact_number"),
                            Score = s.Field<string>("_score"),
                            RecordDate = s.Field<string>("_record_date"),
                        }).ToList();

                        List<EosScoreCardModel> ListEosScoreCardDateModel = objList.GroupBy(p => p.RecordDate).Select(p => p.First()).ToList();

                        List<EosScoreCardModel> ListEosScoreCardDealerModel = objList.GroupBy(p => p.DealerId).Select(p => p.First()).ToList();
                        foreach (EosScoreCardModel objScoreCardDealerModel in ListEosScoreCardDealerModel)
                        {
                            List<string> tempscore = new List<string>();

                            foreach (EosScoreCardModel objScoreCardDateModel in ListEosScoreCardDateModel)
                            {
                                EosScoreCardModel EosScoreCardModelresult = objList.Where(p => p.DealerId == objScoreCardDealerModel.DealerId && p.RecordDate == objScoreCardDateModel.RecordDate).SingleOrDefault();
                                if (EosScoreCardModelresult == null)
                                    tempscore.Add(null);
                                else
                                    tempscore.Add(EosScoreCardModelresult.Score);
                                if (date.Count > 0)
                                {
                                    if (!date.Contains(objScoreCardDateModel.RecordDate))
                                    {
                                        date.Add(objScoreCardDateModel.RecordDate);
                                    }
                                }
                                else
                                {
                                    date.Add(objScoreCardDateModel.RecordDate);
                                }
                            }
                            score.Add(tempscore);
                        }
                        objModel.score = score;
                        objModel.dates = date;
                        objModel.EosScoreCardModel = ListEosScoreCardDealerModel;
                    }                        

                    else if (model.ReportType.Trim().ToLower() == "alldealerscore")
                    {
                        objList = ds.Tables[0].AsEnumerable().Select(s => new EosScoreCardModel
                        {
                            DealerId = s.Field<string>("_dealer_id"),
                            OrganizationId = s.Field<string>("_organization_id"),
                            OrganizationName = s.Field<string>("_organization_name"),
                            DealerName = s.Field<string>("_dealer_name"),
                            DealerCity = s.Field<string>("_dealer_city"),
                            DealerState = s.Field<string>("_dealer_state"),
                            DealerContactNumber = s.Field<string>("_contact_number"),
                            Score = s.Field<string>("_score"),
                            RecordDate = s.Field<string>("_record_date"),
                        }).ToList();
                    }
                    else if (model.ReportType.Trim().ToLower() == "vanscore")
                    {
                        List<List<string>> score = new List<List<string>>();
                        List<string> date = new List<string>();
                        objList = ds.Tables[0].AsEnumerable().Select(s => new EosScoreCardModel
                        {
                            VanId = s.Field<string>("_van_id"),
                            DealerId = s.Field<string>("_dealer_id"),
                            OrganizationId = s.Field<string>("_organization_id"),
                            OrganizationName = s.Field<string>("_organization_name"),
                            DealerName = s.Field<string>("_dealer_name"),
                            VanAlias = s.Field<string>("_van_alias"),
                            VanCity = s.Field<string>("_van_city"),
                            VanState = s.Field<string>("_van_state"),
                            VanModel = s.Field<string>("_van_model"),
                            RegistrationNumber = s.Field<string>("_registration_number"),
                            VanRegisteredAs = s.Field<string>("_van_registered_as"),
                            Score = s.Field<string>("_score"),
                            RecordDate = s.Field<string>("_record_date"),
                            ServiceEngineerName = s.Field<string>("_service_engineer_name"),
                            ServiceEngineerAlias = s.Field<string>("_service_engineer_alias"),
                            ServiceEngineerContactNumber = s.Field<string>("_service_engineer_contact"),
                        }).ToList();

                        List<EosScoreCardModel> ListEosScoreCardDateModel = objList.GroupBy(p => p.RecordDate).Select(p => p.First()).ToList();

                        List<EosScoreCardModel> ListEosScoreCardVanModel = objList.GroupBy(p => p.VanId).Select(p => p.First()).ToList();
                        foreach (EosScoreCardModel objScoreCardVanModel in ListEosScoreCardVanModel)
                        {
                            List<string> tempscore = new List<string>();

                            foreach (EosScoreCardModel objScoreCardDateModel in ListEosScoreCardDateModel)
                            {
                                EosScoreCardModel EosScoreCardModelresult = objList.Where(p => p.VanId == objScoreCardVanModel.VanId && p.RecordDate == objScoreCardDateModel.RecordDate).SingleOrDefault();
                                if (EosScoreCardModelresult == null)
                                    tempscore.Add(null);
                                else
                                    tempscore.Add(EosScoreCardModelresult.Score);
                                if (date.Count > 0)
                                {
                                    if (!date.Contains(objScoreCardDateModel.RecordDate))
                                    {
                                        date.Add(objScoreCardDateModel.RecordDate);
                                    }
                                }
                                else
                                {
                                    date.Add(objScoreCardDateModel.RecordDate);
                                }
                            }
                            score.Add(tempscore);
                        }
                        objModel.score = score;
                        objModel.dates = date;
                        objModel.EosScoreCardModel = ListEosScoreCardVanModel;
                    }


                    else if (model.ReportType.Trim().ToLower() == "allvanscore")
                    {
                        objList = ds.Tables[0].AsEnumerable().Select(s => new EosScoreCardModel
                        {
                            VanId = s.Field<string>("_van_id"),
                            DealerId = s.Field<string>("_dealer_id"),
                            OrganizationId = s.Field<string>("_organization_id"),
                            OrganizationName = s.Field<string>("_organization_name"),
                            DealerName = s.Field<string>("_dealer_name"),
                            VanAlias = s.Field<string>("_van_alias"),
                            VanCity = s.Field<string>("_van_city"),
                            VanState = s.Field<string>("_van_state"),
                            VanModel = s.Field<string>("_van_model"),
                            RegistrationNumber = s.Field<string>("_registration_number"),
                            VanRegisteredAs = s.Field<string>("_van_registered_as"),
                            Score = s.Field<string>("_score"),
                            RecordDate = s.Field<string>("_record_date"),
                            ServiceEngineerName = s.Field<string>("_service_engineer_name"),
                            ServiceEngineerAlias = s.Field<string>("_service_engineer_alias"),
                            ServiceEngineerContactNumber = s.Field<string>("_service_engineer_contact"),
                        }).ToList();
                    }
                    objModel.ClosedTicketList = objList;
                    objModel.Status = "1";
                }
                else
                {
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                    objModel.Status = "0";
                }
            }
            catch (Exception Ex)
            {
                objLog.ErrorLogFile("GetScoreCardReport", Ex.Message, path, errorlogtf);
                objModel.Message = Ex.Message;
                objModel.Status = "0";
            }
            return objModel;
        }

        #endregion
    }

    #endregion
}