namespace VECV_WebApi.Models.Dashboard
{
    #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;


  #endregion

  #region Repository Class

  /// <summary>
  /// This class contain dashbord methods 
  /// </summary>
  public class DashboardRepository
    {
        #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;

        /// <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 DashboardRepository(string connString)
        {
            this._connStr = connString;
        }

        #endregion

        #region API Methods

        /// <summary>
        /// To get tile dashboard KPIs
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>status and data</returns>
        public List<DashboardModel> GetTileDashboardDataSet(DashboardModel model)
        {
            List<DashboardModel> objList = new List<DashboardModel>();
            DashboardModel objModel = new DashboardModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[3];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinutes)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinutes)));
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_dashboard_tile_data"], nSqlParam);
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            objList = ds.Tables[0].AsEnumerable().Select(s => new DashboardModel
                            {
                                Key = s.Field<string>("_key"),
                                KeyName = s.Field<string>("_key_name") == null ? "-" : s.Field<string>("_key_name"),
                                KeyValue = s.Field<string>("_key_value") == null ? "-" : s.Field<string>("_key_value"),
                                Status = "1"
                            }).ToList();
                        }
                    }
                    else
                    {
                        objModel.Key = "Eicher";
                        objModel.Key = "Truck";
                        objModel.Key = "Bus";
                        objList.Add(objModel);

                        objModel.Key = "Volvo";
                        objModel.Key = "Car";
                        objModel.Key = "SUV";
                        objList.Add(objModel);
                    }
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                    objList.Add(objModel);
                }
                return objList;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objList.Add(objModel);
                objLog.ErrorLogFile("GetCustomerDetails", Ex.Message, path, errorlogtf);
                return objList;
            }
        }


        /// <summary>
        /// To get tile dashboard KPIs
        /// </summary>
        /// <param name="model">date range and user info</param>
        /// <returns>status and data</returns>
        /// 
        public NewDashboardModel GetTileNewDashboardDataSet(NewDashboardDataModel model)
        {
            NewDashboardModel objNewDashboardModel = new NewDashboardModel();
            

            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                   
                    // nSqlParam[8] = new NpgsqlParameter("indealer_val_str", result);
                    //nSqlParam[7] = new NpgsqlParameter("inverticle_val", model.verticleVal);
                    if (model.customerVal == null && model.fuel_type == null && model.dealerVal == null && model.emissionVal == null && model.regionVal == null && model.verticleVal == null)
                    {
                        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("incustomer_val", model.customerVal);
                        nSqlParam[4] = new NpgsqlParameter("indealer_val", model.dealerVal);
                        nSqlParam[5] = new NpgsqlParameter("inemission_val", model.emissionVal);
                        nSqlParam[6] = new NpgsqlParameter("inregion_val", model.regionVal);
                        nSqlParam[7] = new NpgsqlParameter("inverticle_val", model.verticleVal);
            // nSqlParam[8] = new NpgsqlParameter("infuel_type", model.fuel_type);
            DateTime cutoffDate = new DateTime(2025, 8, 31);

            if (model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)) <= cutoffDate)
            {
              ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_Newdashboard_tile_data"], nSqlParam);

            }
            else
            {
              ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_Newdashboard_tile_data_new"], nSqlParam);

            }
            if (ds.Tables.Count > 0)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                objNewDashboardModel.OpenTicket = ds.Tables[0].Rows[0]["open_ticket_count"].ToString();
                                // objNewDashboardModel.OpenTicket = "400";
                                objNewDashboardModel.OpenTicket24hours = ds.Tables[0].Rows[0]["open_ticket_24hours"].ToString();
                                objNewDashboardModel.AverageResolutionTime = ds.Tables[0].Rows[0]["average_resolution_time"].ToString();
                                objNewDashboardModel.AverageVanReachTime = ds.Tables[0].Rows[0]["average_van_reach_time"].ToString();
                                objNewDashboardModel.SLA1hrs = ds.Tables[0].Rows[0]["sla_1_hrs"].ToString();
        

                                // Double d = double(ds.Tables[0].Rows[0]["sla_1_hrs"].ToString());
                                // Double dc = Math.Round((Double)d, 2);
                objNewDashboardModel.SLA2hrs = ds.Tables[0].Rows[0]["sla_2_hrs"].ToString();
                                objNewDashboardModel.SLA3hrs = ds.Tables[0].Rows[0]["sla_3_hrs"].ToString();
                                objNewDashboardModel.SLAMoreThan3hrs = ds.Tables[0].Rows[0]["sla_more_than_3_hrs"].ToString();
                objNewDashboardModel.SLARes1hrs = ds.Tables[0].Rows[0]["sla_res_1_hrs"].ToString();
                objNewDashboardModel.SLARes3hrs = ds.Tables[0].Rows[0]["sla_res_3_hrs"].ToString();
                objNewDashboardModel.SLA5hrs = ds.Tables[0].Rows[0]["sla_5_hrs"].ToString();
                objNewDashboardModel.SLA7hrs = ds.Tables[0].Rows[0]["sla_7_hrs"].ToString();
                objNewDashboardModel.SLA10hrs = ds.Tables[0].Rows[0]["sla_10_hrs"].ToString();
                objNewDashboardModel.SLA12hrs = ds.Tables[0].Rows[0]["sla_12_hrs"].ToString();
                                objNewDashboardModel.SLA16hrs = ds.Tables[0].Rows[0]["sla_16_hrs"].ToString();
                                objNewDashboardModel.SLA18hrs = ds.Tables[0].Rows[0]["sla_18_hrs"].ToString();
                                objNewDashboardModel.SLAMoreThan18hrs = ds.Tables[0].Rows[0]["sla_more_than_18_hrs"].ToString();
                                objNewDashboardModel.TABsBusyCount = ds.Tables[0].Rows[0]["tabs_busy_count"].ToString();
                                objNewDashboardModel.TABsFreeCount = ds.Tables[0].Rows[0]["tabs_free_count"].ToString();
                                objNewDashboardModel.TABsOfflineMorethan7Days = ds.Tables[0].Rows[0]["tabs_free_count_7days"].ToString();
                                objNewDashboardModel.NewTicketCount = ds.Tables[0].Rows[0]["new_ticket_count"].ToString();
                                objNewDashboardModel.AssignedTicketCount = ds.Tables[0].Rows[0]["assigned_ticket_count"].ToString();
                                objNewDashboardModel.InprogressTicketCount = ds.Tables[0].Rows[0]["inprogress_ticket_count"].ToString();
                                objNewDashboardModel.ClosedTicketCount = ds.Tables[0].Rows[0]["closed_ticket_count"].ToString();
                                objNewDashboardModel.DeclinedTicketCount = ds.Tables[0].Rows[0]["declined_ticket_count"].ToString();
                                objNewDashboardModel.ActiveCount = ds.Tables[0].Rows[0]["active_count"].ToString();
                                objNewDashboardModel.BreakDownTickets = ds.Tables[0].Rows[0]["breakdown_tickets"].ToString();
                                objNewDashboardModel.CSat = ds.Tables[0].Rows[0]["csat"].ToString();
                                objNewDashboardModel.OppLostCount = ds.Tables[0].Rows[0]["Opplost_ticket_count"].ToString();
                                objNewDashboardModel.BreakDownTicketsBystatus = ds.Tables[0].Rows[0]["breakdown_tickets_by_status"].ToString();
                                objNewDashboardModel.TripStartCount = ds.Tables[0].Rows[0]["trip_start_count"].ToString();
                                objNewDashboardModel.PreclosureCount = ds.Tables[0].Rows[0]["preclosure_count"].ToString();
                            }
                        }

                    }

                    else
                    {
                        //    //NewDashboardModel objNewDashboardModel1 = new NewDashboardModel();

                        //objAuthorization = new AuthenticationRepository();
                        //if (objAuthorization.AuthenticateDevice(model.Token))
                        //{
                        DataSet ds1 = new DataSet();
                        NpgsqlParameter[] nSqlParam1 = new NpgsqlParameter[9];
                        nSqlParam1[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                        nSqlParam1[1] = new NpgsqlParameter("instart_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam1[2] = new NpgsqlParameter("inend_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam1[3] = new NpgsqlParameter("indealer_val", model.dealerVal);
                        nSqlParam1[4] = new NpgsqlParameter("inregion_val", model.regionVal);
                        nSqlParam1[5] = new NpgsqlParameter("inverticle_val", model.verticleVal);
                        nSqlParam1[6] = new NpgsqlParameter("inemission_val", model.emissionVal);
                        nSqlParam1[7] = new NpgsqlParameter("incustomer_val", model.customerVal);
                        nSqlParam1[8] = new NpgsqlParameter("infuel_type", model.fuel_type);
            //    nSqlParam1[9] = new NpgsqlParameter("in_modelno", model.modelNumber);
            DateTime cutoffDate = new DateTime(2025, 8, 31);

            if (model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)) <= cutoffDate)
            {
              ds1 = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_newdashboard_tile_data_sorting"], nSqlParam1);

            }
            else
            {
              ds1 = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_newdashboard_tile_data_sorting_new"], nSqlParam1);

            }

            // ds1.Tables[0].DefaultView.RowFilter = "ticket_activity = '7'";
            //  DataTable dt = (ds.Tables[0].DefaultView).ToTable();
            if (ds1.Tables.Count > 0)
                        {
                            if (ds1.Tables[0].Rows.Count > 0)
                            {
                                objNewDashboardModel.OpenTicket = ds1.Tables[0].Rows[0]["open_ticket_count"].ToString();
                                // objNewDashboardModel.OpenTicket = "400";
                                objNewDashboardModel.OpenTicket24hours = ds1.Tables[0].Rows[0]["open_ticket_24hours"].ToString();
                                objNewDashboardModel.AverageResolutionTime = ds1.Tables[0].Rows[0]["average_resolution_time"].ToString();
                                objNewDashboardModel.AverageVanReachTime = ds1.Tables[0].Rows[0]["average_van_reach_time"].ToString();
                                objNewDashboardModel.SLA1hrs = ds1.Tables[0].Rows[0]["sla_1_hrs"].ToString();


                                // Double d = double(ds.Tables[0].Rows[0]["sla_1_hrs"].ToString());
                                // Double dc = Math.Round((Double)d, 2);
                                objNewDashboardModel.SLA2hrs = ds1.Tables[0].Rows[0]["sla_2_hrs"].ToString();
                                objNewDashboardModel.SLA3hrs = ds1.Tables[0].Rows[0]["sla_3_hrs"].ToString();
                                objNewDashboardModel.SLAMoreThan3hrs = ds1.Tables[0].Rows[0]["sla_more_than_3_hrs"].ToString();
                objNewDashboardModel.SLARes1hrs = ds1.Tables[0].Rows[0]["sla_res_1_hrs"].ToString();
                objNewDashboardModel.SLARes3hrs = ds1.Tables[0].Rows[0]["sla_res_3_hrs"].ToString();
                objNewDashboardModel.SLA5hrs = ds1.Tables[0].Rows[0]["sla_5_hrs"].ToString();
                objNewDashboardModel.SLA7hrs = ds1.Tables[0].Rows[0]["sla_7_hrs"].ToString();
                objNewDashboardModel.SLA10hrs = ds1.Tables[0].Rows[0]["sla_10_hrs"].ToString();
                objNewDashboardModel.SLA12hrs = ds1.Tables[0].Rows[0]["sla_12_hrs"].ToString();
                objNewDashboardModel.SLA12hrs = ds1.Tables[0].Rows[0]["sla_12_hrs"].ToString();
                                objNewDashboardModel.SLA16hrs = ds1.Tables[0].Rows[0]["sla_16_hrs"].ToString();
                                objNewDashboardModel.SLA18hrs = ds1.Tables[0].Rows[0]["sla_18_hrs"].ToString();
                                objNewDashboardModel.SLAMoreThan18hrs = ds1.Tables[0].Rows[0]["sla_more_than_18_hrs"].ToString();
                                objNewDashboardModel.TABsBusyCount = ds1.Tables[0].Rows[0]["tabs_busy_count"].ToString();
                                objNewDashboardModel.TABsFreeCount = ds1.Tables[0].Rows[0]["tabs_free_count"].ToString();
                                objNewDashboardModel.TABsOfflineMorethan7Days = ds1.Tables[0].Rows[0]["tabs_free_count_7days"].ToString();
                                objNewDashboardModel.NewTicketCount = ds1.Tables[0].Rows[0]["new_ticket_count"].ToString();
                                objNewDashboardModel.AssignedTicketCount = ds1.Tables[0].Rows[0]["assigned_ticket_count"].ToString();
                                objNewDashboardModel.InprogressTicketCount = ds1.Tables[0].Rows[0]["inprogress_ticket_count"].ToString();
                                objNewDashboardModel.ClosedTicketCount = ds1.Tables[0].Rows[0]["closed_ticket_count"].ToString();
                                objNewDashboardModel.DeclinedTicketCount = ds1.Tables[0].Rows[0]["declined_ticket_count"].ToString();
                                objNewDashboardModel.ActiveCount = ds1.Tables[0].Rows[0]["active_count"].ToString();
                                objNewDashboardModel.BreakDownTickets = ds1.Tables[0].Rows[0]["breakdown_tickets"].ToString();
                                objNewDashboardModel.CSat = ds1.Tables[0].Rows[0]["csat"].ToString();
                                objNewDashboardModel.OppLostCount = ds1.Tables[0].Rows[0]["Opplost_ticket_count"].ToString();
                                objNewDashboardModel.BreakDownTicketsBystatus = ds1.Tables[0].Rows[0]["breakdown_tickets_by_status"].ToString();
                                objNewDashboardModel.TripStartCount = ds1.Tables[0].Rows[0]["trip_start_count"].ToString();
                                objNewDashboardModel.PreclosureCount = ds1.Tables[0].Rows[0]["preclosure_count"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
                // write error log into file
                //    objticketModel.Status = "0";
                //   objticketModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetTicketStatusByRegistrationNumber", Ex.Message, path, errorlogtf);
                return objNewDashboardModel;
            }
            return objNewDashboardModel;
        }

        /// <summary>
        /// To get customer details
        /// </summary>
        /// <param name="model">device and ticket info</param>
        /// <returns>status and customer details</returns>
        public List<DealerModel> GetDealerDetails(NewDashboardDataModel model)
        {
            List<DealerModel> objList = new List<DealerModel>();
            try
            {
                DealerModel objNewDashboardModel = new DealerModel();

                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    if (model.customerVal == null && model.fuel_type == null && model.dealerVal == null && model.emissionVal == null && model.regionVal == null && model.verticleVal == null)
                    {
                        NpgsqlParameter[] nSqlParam1 = new NpgsqlParameter[4];
                        nSqlParam1[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                        nSqlParam1[1] = new NpgsqlParameter("instart_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam1[2] = new NpgsqlParameter("inend_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam1[3] = new NpgsqlParameter("inverticle_val", model.verticleVal);
                      
            DateTime cutoffDate = new DateTime(2025, 8, 31);

            if (model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)) <= cutoffDate)
            {
              ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_top_dealer_detail"], nSqlParam1);

            }
            else
            {
              ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_top_dealer_detail_new"], nSqlParam1);

            }
          }
                    else
                    {
                        NpgsqlParameter[] nSqlParam1 = new NpgsqlParameter[9];
                        nSqlParam1[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                        nSqlParam1[1] = new NpgsqlParameter("instart_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam1[2] = new NpgsqlParameter("inend_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
                        nSqlParam1[3] = new NpgsqlParameter("inemission_val", model.emissionVal);
                        nSqlParam1[4] = new NpgsqlParameter("inregion_val", model.regionVal);
                        nSqlParam1[5] = new NpgsqlParameter("inverticle_val", model.verticleVal);
                        nSqlParam1[6] = new NpgsqlParameter("incustomer_val", model.customerVal);
                        nSqlParam1[7] = new NpgsqlParameter("indealer_val", model.dealerVal);
                        nSqlParam1[8] = new NpgsqlParameter("infuel_type", model.fuel_type);
          //  nSqlParam1[9] = new NpgsqlParameter("in_modelno", model.modelNumber);
            DateTime cutoffDate = new DateTime(2025, 8, 31);

            if (model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)) <= cutoffDate)
            {
              ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_top_dealer_detail_sorting"], nSqlParam1);

            }
            else
            {
              ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_top_dealer_detail_sorting"], nSqlParam1);

            }

          }
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        objList = ds.Tables[0].AsEnumerable().Select(s => new DealerModel
                        {
                         
                            DealerName = s.Field<string>("_dealer_name"),
                            Region = s.Field<string>("_region"),
                            TicketCount = s.Field<string>("_ticket_count"),
                            DealerId = s.Field<string>("_dealer_id"),

                        }).ToList();
                    }
                }
                else
                {
                    //objCustomerVehicleModel.Status = 0;
                    // objCustomerVehicleModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objList;
            }
            catch (Exception Ex)
            {
                //  objNewDashboardModel = new CustomerVehicleModel();
                // objNewDashboardModel.Status = 0;
                // objCustomerVehicleModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetCustomerDetails", Ex.Message, path, errorlogtf);
                return objList;
            }
        }

    public List<DTCModel> GetDTCDetails(NewDashboardDataModel model)
    {
      List<DTCModel> objList = new List<DTCModel>();
      try
      {
        DTCModel objNewDashboardModel = new DTCModel();

        objAuthorization = new AuthenticationRepository();
        if (objAuthorization.AuthenticateDevice(model.Token))
        {
          DataSet ds = new DataSet();
          if (model.customerVal == null && model.fuel_type == null && model.dealerVal == null && model.emissionVal == null && model.regionVal == null && model.verticleVal == null)
          {
            NpgsqlParameter[] nSqlParam1 = new NpgsqlParameter[4];
            nSqlParam1[0] = new NpgsqlParameter("inuser_id ", model.UserId);
            nSqlParam1[1] = new NpgsqlParameter("instart_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
            nSqlParam1[2] = new NpgsqlParameter("inend_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
            nSqlParam1[3] = new NpgsqlParameter("inverticle_val", model.verticleVal);
            ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_top_dtc_detail"], nSqlParam1);
          }
          else
          {
            NpgsqlParameter[] nSqlParam1 = new NpgsqlParameter[9];
            nSqlParam1[0] = new NpgsqlParameter("inuser_id ", model.UserId);
            nSqlParam1[1] = new NpgsqlParameter("instart_date", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
            nSqlParam1[2] = new NpgsqlParameter("inend_date", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)));
            nSqlParam1[3] = new NpgsqlParameter("inemission_val", model.emissionVal);
            nSqlParam1[4] = new NpgsqlParameter("inregion_val", model.regionVal);
            nSqlParam1[5] = new NpgsqlParameter("inverticle_val", model.verticleVal);
            nSqlParam1[6] = new NpgsqlParameter("incustomer_val", model.customerVal);
            nSqlParam1[7] = new NpgsqlParameter("indealer_val", model.dealerVal);
            nSqlParam1[8] = new NpgsqlParameter("infuel_type", model.fuel_type);
       //     nSqlParam1[9] = new NpgsqlParameter("in_modelno", model.modelNumber);
            ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_top_dtc_detail_sorting"], nSqlParam1);
          }
          if (ds.Tables[0].Rows.Count > 0)
          {
            objList = ds.Tables[0].AsEnumerable().Select(s => new DTCModel
            {

              DTCCode = s.Field<string>("_dtc_code"),
              DTCCount = s.Field<string>("_dtc_count"),
        

            }).ToList();
          }
        }
        else
        {
          //objCustomerVehicleModel.Status = 0;
          // objCustomerVehicleModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
        }
        return objList;
      }
      catch (Exception Ex)
      {
        //  objNewDashboardModel = new CustomerVehicleModel();
        // objNewDashboardModel.Status = 0;
        // objCustomerVehicleModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
        objLog.ErrorLogFile("GetCustomerDetails", Ex.Message, path, errorlogtf);
        return objList;
      }
    }


    /// <summary>
    /// To get customer details
    /// </summary>
    /// <param name="model">device and ticket info</param>
    /// <returns>status and customer details</returns>
    public List<GraphModel> GetGraphDetails(NewDashboardDataModel model)
        {
            List<GraphModel> objList = new List<GraphModel>();
            try
            {
                GraphModel objNewDashboardModel = new GraphModel();

                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    if (model.customerVal == null && model.fuel_type == null && model.dealerVal == null && model.emissionVal == null && model.regionVal == null && model.verticleVal == null)
                    {
                        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("incustomer_val", model.customerVal);
                        nSqlParam[4] = new NpgsqlParameter("indealer_val", model.dealerVal);
                        nSqlParam[5] = new NpgsqlParameter("inemission_val", model.emissionVal);
                        nSqlParam[6] = new NpgsqlParameter("inregion_val", model.regionVal);
                        nSqlParam[7] = new NpgsqlParameter("inverticle_val", model.verticleVal);
            DateTime cutoffDate = new DateTime(2025, 8, 31);

            if (model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)) <= cutoffDate)
            {
              ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_Graph_detail"], nSqlParam);

            }
            else
            {
              ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_Graph_detail_new"], nSqlParam);

            }
          }
                    else
                    {
                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[9];
                        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("inemission_val", model.emissionVal);
                        nSqlParam[4] = new NpgsqlParameter("inregion_val", model.regionVal);
                        nSqlParam[5] = new NpgsqlParameter("inverticle_val", model.verticleVal);
                        nSqlParam[6] = new NpgsqlParameter("incustomer_val", model.customerVal);
                        nSqlParam[7] = new NpgsqlParameter("indealer_val", model.dealerVal);
                        nSqlParam[8] = new NpgsqlParameter("infuel_type", model.fuel_type);
           // nSqlParam[9] = new NpgsqlParameter("in_modelno", model.modelNumber);
                   
            DateTime cutoffDate = new DateTime(2025, 8, 31);

            if (model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinute)) <= cutoffDate)
            {
              ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_graph_detail_Sorting"], nSqlParam);

            }
            else
            {
              ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_graph_detail_Sorting_new"], nSqlParam);

            }
          }
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        objList = ds.Tables[0].AsEnumerable().Select(s => new GraphModel
                        {
                            //   objNewDashboardModel = s.Field<Boolean?>("_customer_vehicle_is_owner"),
                            //DealerName = model.TicketNo,
                            RegionName = s.Field<string>("_region_name"),
                            count_25km = s.Field<Int32>("_count_25km"),
                            count_50km = s.Field<Int32>("_count_50km"),
                            count_100km = s.Field<Int32>("_count_100km"),
                            count_More_Than_100km = s.Field<Int32>("_count_More_Than_100km"),
                        }).ToList();
                    }
                }
                else
                {
                    //objCustomerVehicleModel.Status = 0;
                    // objCustomerVehicleModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objList;
            }
            catch (Exception Ex)
            {
                //  objNewDashboardModel = new CustomerVehicleModel();
                // objNewDashboardModel.Status = 0;
                // objCustomerVehicleModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetCustomerDetails", Ex.Message, path, errorlogtf);
                return objList;
            }
        }

        public List<DealerModel> GetAllDealerListDataSet(int[] dealers)
        {
            List<DealerModel> objList = new List<DealerModel>();
            DashboardModel objModel = new DashboardModel();
            try
            {
                objAuthorization = new AuthenticationRepository();

                DataSet ds = new DataSet();
                // NpgsqlParameter[] nSqlParam = new NpgsqlParameter[3];
                if (dealers == null)
                {
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_alldealers"]);

                }
                else
                {
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];
                    nSqlParam[0] = new NpgsqlParameter("inorganization_id ", dealers);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_alldealers_byRegion"], nSqlParam);
                }

                if (ds.Tables[0].Rows.Count > 0)
                {
                    objList = ds.Tables[0].AsEnumerable().Select(s => new DealerModel
                    {
                        //   objNewDashboardModel = s.Field<Boolean?>("_customer_vehicle_is_owner"),
                        //DealerName = model.TicketNo,
                        DealerName = s.Field<string>("_dealer_name"),
                        // Region = s.Field<string>("_region"),
                        // TicketCount = s.Field<string>("_ticket_count"),
                        DealerId = s.Field<string>("_id"),

                    }).ToList();
                }
                //}
                return objList;
            }
            catch (Exception Ex)
            {
                //  objNewDashboardModel = new CustomerVehicleModel();
                // objNewDashboardModel.Status = 0;
                // objCustomerVehicleModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetCustomerDetails", Ex.Message, path, errorlogtf);
                return objList;
            }
        }

        /// <summary>
        /// To get all State list
        /// </summary>
        /// <param name="model">state info</param>
        /// <returns>all State list</returns>
        public List<EmissionModel> GetAllEmissionList()
        {
            List<EmissionModel> objList = new List<EmissionModel>();
            EmissionModel objModel = new EmissionModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                //   if (objAuthorization.AuthenticateDevice(model.Token))
                // {
                DataSet ds = new DataSet();
                ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_allemissionnorms"]);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    objList = ds.Tables[0].AsEnumerable().Select(s => new EmissionModel
                    {
                        EmissionName = s.Field<string>("_emissionnorms"),
                        //  StateAlias = s.Field<string>("state_alias")
                    }).ToList();
                }
                //  }
                //else
                //{
                //  objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                // objList.Add(objModel);
                // }

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



        public List<RegionModel> GetAllRegionListDataSet()
        {
            List<RegionModel> objList = new List<RegionModel>();
            DashboardModel objModel = new DashboardModel();
            try
            {

                DataSet ds = new DataSet();
                //  NpgsqlParameter[] nSqlParam = new NpgsqlParameter[3];
                ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_allregions"]);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    objList = ds.Tables[0].AsEnumerable().Select(s => new RegionModel
                    {
                        //   objNewDashboardModel = s.Field<Boolean?>("_customer_vehicle_is_owner"),
                        //DealerName = model.TicketNo,
                        // RegionName = s.Field<string>("_dealer_name"),
                        RegionName = s.Field<string>("_region"),
                        //TicketCount = s.Field<string>("_ticket_count"),
                        Id = s.Field<string>("_id"),

                    }).ToList();
                }

                return objList;
            }
            catch (Exception Ex)
            {
                //  objNewDashboardModel = new CustomerVehicleModel();
                // objNewDashboardModel.Status = 0;
                // objCustomerVehicleModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetCustomerDetails", Ex.Message, path, errorlogtf);
                return objList;
            }
        }

        public List<VerticleModel> GetVerticleListDataSet()
        {
            List<VerticleModel> objList = new List<VerticleModel>();
            VerticleModel objModel = new VerticleModel();
            try
            {

                DataSet ds = new DataSet();
                //  NpgsqlParameter[] nSqlParam = new NpgsqlParameter[3];
                ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_allvertical"]);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    objList = ds.Tables[0].AsEnumerable().Select(s => new VerticleModel
                    {
                        //   objNewDashboardModel = s.Field<Boolean?>("_customer_vehicle_is_owner"),
                        //DealerName = model.TicketNo,
                        VerticleName = s.Field<string>("_vertical"),
                        //  Id = s.Field<string>("_region"),
                        //  TicketCount = s.Field<string>("_ticket_count"),
                        //  DealerId = s.Field<string>("_dealer_id"),

                    }).ToList();

                }
                return objList;
            }
            catch (Exception Ex)
            {
                //  objNewDashboardModel = new CustomerVehicleModel();
                // objNewDashboardModel.Status = 0;
                // objCustomerVehicleModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetCustomerDetails", Ex.Message, path, errorlogtf);
                return objList;
            }
        }



        public List<DashboardModel> GetTileDashboardDataSetVECV(DashboardModel model)
        {
            List<DashboardModel> dashboardDataSetVecv = new List<DashboardModel>();
            DashboardModel dashboardModel = new DashboardModel();
            try
            {
                if (model.Email == "")
                {
                    model.Email = null;
                }
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {

                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[5];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    nSqlParam[1] = new NpgsqlParameter("instart_date ", model.FromDate.AddMinutes(Convert.ToInt32(-model.UtcMinutes)));
                    nSqlParam[2] = new NpgsqlParameter("inend_date ", model.ToDate.AddMinutes(Convert.ToInt32(-model.UtcMinutes)));
                    nSqlParam[3] = new NpgsqlParameter("inemails ", model.Email);
                    nSqlParam[4] = new NpgsqlParameter("indealercodes ", model.dealerCodes);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_dashboard_tile_data_VECV_User"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        dashboardDataSetVecv = ds.Tables[0].AsEnumerable().Select(s => new DashboardModel
                        {
                            Key = s.Field<string>("_key"),
                            KeyName = s.Field<string>("_key_name") == null ? "-" : s.Field<string>("_key_name"),
                            KeyValue = s.Field<string>("_key_value") == null ? "-" : s.Field<string>("_key_value"),
                            Status = "1"
                        }).ToList();

                    }
                    else
                    {
                        dashboardModel.Key = "Eicher";
                        dashboardModel.Key = "Truck";
                        dashboardModel.Key = "Bus";
                        dashboardDataSetVecv.Add(dashboardModel);
                        dashboardModel.Key = "Volvo";
                        dashboardModel.Key = "Car";
                        dashboardModel.Key = "SUV";
                        dashboardDataSetVecv.Add(dashboardModel);
                    }
                }
                else
                {
                    dashboardModel.Status = "0";
                    dashboardModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                    dashboardDataSetVecv.Add(dashboardModel);
                }
                return dashboardDataSetVecv;
            }
            catch (Exception ex)
            {
                dashboardModel.Status = "0";
                dashboardModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + ex.Message;
                dashboardDataSetVecv.Add(dashboardModel);
                this.objLog.ErrorLogFile("GetCustomerDetails", ex.Message, this.path, this.errorlogtf);
                return dashboardDataSetVecv;
            }
        }


        public List<VECVSyncModel> GetSyncTicketDetail_VECV(VECVSyncModel model)
        {
            List<VECVSyncModel> ticketDetailVecv = new List<VECVSyncModel>();
            List<VECVCustomerOpenTicketDetail> openTicketDetailList = new List<VECVCustomerOpenTicketDetail>();
            List<TicketActivityOpenModelVECV> activityOpenModelVecvList = new List<TicketActivityOpenModelVECV>();
            List<VECVCustomerCloseTicketDetail> closeTicketDetailList = new List<VECVCustomerCloseTicketDetail>();
            List<TicketActivityCloseModelVECV> activityCloseModelVecvList = new List<TicketActivityCloseModelVECV>();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    if (model.Email == "")
                    {
                        model.Email = null;
                    }
                    if(model.DealerCode == null)
                    {
                        model.DealerCode ="";
                    }
                    DataSet ds = new DataSet();
                    DataSet ds1 = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[3];
                    nSqlParam[0] = new NpgsqlParameter("indealer_code ", model.DealerCode);
                    nSqlParam[1] = new NpgsqlParameter("db_syn_last_time ", model.DbSynLastTime);
                    nSqlParam[2] = new NpgsqlParameter("inemails ", model.Email);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_ticket_open_VECV_User"], nSqlParam);
                    ds1 = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_ticket_closed_VECV_User"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        openTicketDetailList = ds.Tables[0].AsEnumerable().Select(s => new VECVCustomerOpenTicketDetail
                        {
                            TicketId = s.Field<string>("ticket_id"),
                            Description = s.Field<string>("description"),
                            AssignedTo = s.Field<string>("assigned_to"),
                            ReportedVia = s.Field<string>("reported_via"),
                            TicketStatus = s.Field<int?>("ticket_status"),
                            Priority = s.Field<int?>("priority"),
                            CustomerScore = s.Field<string>("customer_score"),
                            CreatedBy = s.Field<string>("created_by"),
                            CreationTime = Convert.ToDateTime((object)s.Field<DateTime?>("creation_time")).ToString("dd MMM yyy HH:mm:ss"),
                            LastModifiedBy = s.Field<string>("last_modified_by"),
                            LastModifiedTime = Convert.ToDateTime((object)s.Field<DateTime?>("last_modified_time")).ToString("dd MMM yyy HH:mm:ss"),
                            BreakdownLocation = s.Field<string>("breakdown_location"),
                            BreakdownLongitude = s.Field<string>("breakdown_longitude"),
                            BreakdownLattitude = s.Field<string>("breakdown_lattitude"),
                            AssignedToUserId = s.Field<string>("assigned_to_user_id"),
                            AssignedToUserLattitude = s.Field<string>("assigned_to_user_lattitude"),
                            AssignedToUserLongitude = s.Field<string>("assigned_to_user_longitude"),
                            Isdeclined = s.Field<bool?>("isdeclined"),
                            EstimatedTimeForJobCompletion = s.Field<int?>("estimated_time_for_job_completion"),
                            TotalTicketLifecycleTimeSla = s.Field<int?>("total_ticket_lifecycle_time_sla"),
                            EstimatedTimeForJobCompletionSubmitTime = Convert.ToDateTime((object)s.Field<DateTime?>("estimated_time_for_job_completion_submit_time")).ToString("dd MMM yyy HH:mm:ss"),
                            VehicleRegisterNumber = s.Field<string>("vehicle_register_number"),
                            BreakdownLocationLandmark = s.Field<string>("breakdown_location_landmark"),
                            RouteId = s.Field<int?>("route_id"),
                            CustomerContactNo = s.Field<string>("customer_contact_no"),
                            RepairCost = s.Field<string>("repair_cost"),
                            DefaultSlaTime = s.Field<int?>("default_sla_time"),
                            SlaMissedReason = s.Field<string>("sla_missed_reason"),
                            SuggestionComment = s.Field<string>("suggestion_comment"),
                            JobCompleteResponseTime = s.Field<string>("default_col_1"),
                            DefaultCol2 = s.Field<string>("default_col_2"),
                            DefaultCol3 = s.Field<string>("default_col_3"),
                            EstimatedDistance = s.Field<string>("estimated_distance"),
                            Owner_Contact_no = s.Field<string>("_owner_contact_no"),
                            TicketIdAlias = s.Field<string>("_ticket_id_alias")
                        }).ToList();
                    }
                    if (ds1.Tables[0].Rows.Count > 0)
                    {
                        closeTicketDetailList = ds1.Tables[0].AsEnumerable().Select(s => new VECVCustomerCloseTicketDetail
                        {

                            TicketId = s.Field<string>("ticket_id"),
                            Description = s.Field<string>("description"),
                            AssignedTo = s.Field<string>("assigned_to"),
                            ReportedVia = s.Field<string>("reported_via"),
                            TicketStatus = s.Field<int?>("ticket_status"),
                            Priority = s.Field<int?>("priority"),
                            CustomerScore = s.Field<string>("customer_score"),
                            CreatedBy = s.Field<string>("created_by"),
                            CreationTime = Convert.ToDateTime((object)s.Field<DateTime?>("creation_time")).ToString("dd MMM yyy HH:mm:ss"),
                            LastModifiedBy = s.Field<string>("last_modified_by"),
                            LastModifiedTime = Convert.ToDateTime((object)s.Field<DateTime?>("last_modified_time")).ToString("dd MMM yyy HH:mm:ss"),
                            BreakdownLocation = s.Field<string>("breakdown_location"),
                            BreakdownLongitude = s.Field<string>("breakdown_longitude"),
                            BreakdownLattitude = s.Field<string>("breakdown_lattitude"),
                            AssignedToUserId = s.Field<string>("assigned_to_user_id"),
                            AssignedToUserLattitude = s.Field<string>("assigned_to_user_lattitude"),
                            AssignedToUserLongitude = s.Field<string>("assigned_to_user_longitude"),
                            Isdeclined = s.Field<bool?>("isdeclined"),
                            EstimatedTimeForJobCompletion = s.Field<int?>("estimated_time_for_job_completion"),
                            TotalTicketLifecycleTimeSla = s.Field<int?>("total_ticket_lifecycle_time_sla"),
                            EstimatedTimeForJobCompletionSubmitTime = Convert.ToDateTime((object)s.Field<DateTime?>("estimated_time_for_job_completion_submit_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            VehicleRegisterNumber = s.Field<string>("vehicle_register_number"),
                            BreakdownLocationLandmark = s.Field<string>("breakdown_location_landmark"),
                            RouteId = s.Field<int?>("route_id"),
                            CustomerContactNo = s.Field<string>("customer_contact_no"),
                            RepairCost = s.Field<string>("repair_cost"),
                            DefaultSlaTime = s.Field<int?>("default_sla_time"),
                            SlaMissedReason = s.Field<string>("sla_missed_reason"),
                            SuggestionComment = s.Field<string>("suggestion_comment"),
                            JobCompleteResponseTime = s.Field<string>("default_col_1"),
                            DefaultCol2 = s.Field<string>("default_col_2"),
                            DefaultCol3 = s.Field<string>("default_col_3"),
                            EstimatedDistance = s.Field<string>("estimated_distance"),
                            Owner_Contact_no = s.Field<string>("_owner_contact_no"),
                            TicketIdAlias = s.Field<string>("_ticket_id_alias"),

                        }).ToList();

                    }
                    
                    ticketDetailVecv.Add(new VECVSyncModel()
                    {
                        TicketOpenList = openTicketDetailList,
                        TicketCloseList = closeTicketDetailList,
                        DeviceAlias = model.DeviceAlias,
                        DbSynLastTime = DateTime.UtcNow.ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                        Status = "1"
                    });
                    return ticketDetailVecv;
                }
                ticketDetailVecv.Add(new VECVSyncModel()
                {
                    DeviceAlias = model.DeviceAlias,
                    DbSynLastTime = model.DbSynLastTime,
                    Status = "0",
                    Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString()
                });
                return ticketDetailVecv;
            }
            catch (Exception ex)
            {
                ticketDetailVecv.Add(new VECVSyncModel()
                {
                    DeviceAlias = model.DeviceAlias,
                    DbSynLastTime = model.DbSynLastTime,
                    Status = "0",
                    Message = ex.Message
                });
                this.objLog.ErrorLogFile("GetSyncTicketDetail_Dealer", ex.Message, this.path, this.errorlogtf);
                return ticketDetailVecv;
            }
        }



        public VECVTicketRequestModel GetDealerOpenTicketDetailVECV(VECVTicketRequestModel model)
        {


            VECVTicketRequestModel ticketDetailVecv = new VECVTicketRequestModel();
            List<VECVTicketOpenModel> vecvTicketOpenModelList = new List<VECVTicketOpenModel>();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    if (model.Email == "")
                    {
                        model.Email = null;
                    }
                    DataSet ds = new DataSet();
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];
                    nSqlParam[0] = new NpgsqlParameter("inuser_id ", model.UserId);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_dealer_open_ticket_details"], nSqlParam);

                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        vecvTicketOpenModelList = ds.Tables[0].AsEnumerable().Select(s => new VECVTicketOpenModel
                        {

                            Id = s.Field<string>("ticket_id"),
                            Description = s.Field<string>("description"),
                            AssignedTo = s.Field<string>("assigned_to"),
                            ReportedVia = s.Field<string>("reported_via"),
                            TicketStatus = s.Field<int?>("ticket_status"),
                            Priority = s.Field<int?>("priority"),
                            CutomerScore = s.Field<string>("customer_score"),
                            CreatedBy = s.Field<string>("created_by"),
                            //   DateTime dateTime1 = Convert.ToDateTime((object)s.Field<DateTime?>("creation_time")),
                            //  dateTime1 = dateTime1.AddMinutes((double)Convert.ToInt16((object)model.UtcMinute)),
                            // CreationTime = dateTime1.ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            //ateTime dateTime2 = Convert.ToDateTime((object)s.Field<DateTime?>("last_modified_time"));
                            //dateTime2 = dateTime2.AddMinutes((double)Convert.ToInt16((object)model.UtcMinute));
                            //LastModifiedTime = dateTime2.ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            LastModifiedBy = s.Field<string>("last_modified_by"),
                            BreakDownLocation = s.Field<string>("breakdown_location"),
                            BreackDownLongitude = s.Field<string>("breakdown_longitude"),
                            BreackDownLatitude = s.Field<string>("breakdown_lattitude"),
                            AssignedToUserId = s.Field<string>("assigned_to_user_id"),
                            AssignedToUserLatitude = s.Field<string>("assigned_to_user_lattitude"),
                            AssignedToUserLongitude = s.Field<string>("assigned_to_user_longitude"),
                            IsDeclined = s.Field<bool?>("isdeclined"),
                            EstimatedTimeForJobComplition = s.Field<int?>("estimated_time_for_job_completion"),
                            TotalTicketLifeCycleTimeSlab = s.Field<int?>("total_ticket_lifecycle_time_sla"),
                            VehicleRegistrationNo = s.Field<string>("vehicle_register_number"),
                            BreakdownLocationLandmark = s.Field<string>("breakdown_location_landmark"),
                            RouteId = s.Field<int?>("route_id"),
                            CustomeContact_no = s.Field<string>("customer_contact_no"),
                            RepairCost = s.Field<string>("repair_cost"),
                            DefaultSlaTime = s.Field<int?>("default_sla_time"),
                            SlaMissedReason = s.Field<string>("sla_missed_reason"),
                            SuggestionComment = s.Field<string>("suggestion_comment"),
                            TicketStatusAlias = s.Field<string>("ticket_status_alias"),
                            JobCompleteResponseTime = s.Field<string>("default_col_1"),
                            DefaultCol2 = s.Field<string>("default_col_2"),
                            DefaultCol3 = s.Field<string>("default_col_3"),
                            ticketid_alias = s.Field<string>("_ticket_id_alias"),

                        }).ToList();
                        ticketDetailVecv.TicketList = vecvTicketOpenModelList;
                        ticketDetailVecv.Status = "1";
                    }
                    else
                    {
                        ticketDetailVecv.Status = "0";
                        ticketDetailVecv.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                    }
                }
            }

            catch (Exception ex)
            {
                ticketDetailVecv.Status = "0";
                ticketDetailVecv.Message = ex.Message;
                this.objLog.ErrorLogFile("GetDealerOpenTicketDetail", ex.Message, this.path, this.errorlogtf);

            }
     return ticketDetailVecv;
        }


        /// <summary>
        /// To get open and 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 VECVTicketRequestModel GetOpenCloseTicketDetailVECV(VECVTicketRequestModel model)
        {
            VECVTicketRequestModel objModel = new VECVTicketRequestModel();
            List<VECVTicketOpenModel> objList = new List<VECVTicketOpenModel>();
            try
            {
                DataSet ds = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    // Get Open ticket report details.
                    if (model.TicketType.Trim().ToLower() == "open")
                    {
                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[5];
                        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("inemails", model.Email);
                        nSqlParam[4] = new NpgsqlParameter("indealercodes", model.Dealers);
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_open_tickets_user_wise_VECV_User"], 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["usp_get_closed_tickets_count_user_wise"], nSqlParam);
                    }
                    else
                    {
                        // Get closed ticket report details.
                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[7];
                        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);
                        nSqlParam[6] = new NpgsqlParameter("inemails", model.Email);
                        //  nSqlParam[7] = new NpgsqlParameter("indealercodes", model.Dealers);
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_closed_tickets_user_wise_VECV_User"], nSqlParam);
                    }
                    if (model.TicketType.Trim().ToLower() == "count")
                    {
                        objModel.TicketCount = Convert.ToInt64(ds.Tables[0].Rows[0][0]);
                    }
                    else if (model.TicketType.Trim().ToLower() == "open")
                    {
                        objList = ds.Tables[0].AsEnumerable().Select(s => new VECVTicketOpenModel
                        {
                            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<Int32?>("_age")),
                            DefaultCol2 = model.TicketType.Trim().ToLower() == "close" ? s.Field<string>("_feedbackagent") : null,
                            DefaultCol3 = model.TicketType.Trim().ToLower() == "close" ? s.Field<string>("_reasonformorethen24hours") : null,
                            VehicleRegistrationNo = s.Field<string>("_vehicle_registration_number"),
                            ticketid_alias = s.Field<string>("_ticket_id_alias"),
                            caller_language = s.Field<string>("_caller_language"),
                            gps_status = s.Field<string>("_gps_status"),
                            current_latitude = s.Field<string>("_current_latitude"),
                            current_longitude = s.Field<string>("_current_longitude"),
                            PreClosureTime = Convert.ToDateTime(s.Field<DateTime?>("_pre_closure_time")).AddMinutes(Convert.ToInt16(model.UtcMinute)).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            ChassisNo = s.Field<string>("_chassis_number"),
                            OdometerReading = s.Field<double>("_odometer_reading"),
                            Remarks = s.Field<string>("_last_remark"),
                            Description = s.Field<string>("_nature_of_problem"),
                            BreakDownLocation = s.Field<string>("csm"),
                            BreackDownLongitude = s.Field<string>("csm_contact_no"),
                            BreackDownLatitude = s.Field<string>("rsm"),
                            AssignedToUserLongitude = s.Field<string>("rsm_contact_no"),
                            AssignedToUserLatitude = s.Field<string>("asm"),
                            BreakdownLocationLandmark = s.Field<string>("asm_contact_no"),
                            Designation = s.Field<string>("_Dealer_name_1scode"),
                            VehicleType = s.Field<string>("_vehicle_status"),
                            ServiceEngineerPinPassword = s.Field<string>("_vehicle_emission_norms"),
                            ServiceEngineerAlias = s.Field<string>("_created_by"),
                            ServiceEngineerApplicationLicenseKey = s.Field<string>("_region"),
                            VanReachedPunchTime = s.Field<DateTime?>("_van_reached_punch_time") == null ? "" : Convert.ToDateTime(s.Field<DateTime?>("_van_reached_punch_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),  //added on 23-12-2020
                        }).ToList();
                    }
                    else if (model.TicketType.Trim().ToLower() == "close")
                    {
                        objList = ds.Tables[0].AsEnumerable().Select(s => new VECVTicketOpenModel
                        {
                            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"),
                            //added on 25-09-2020
                            VanLatitudeAtTripStart = s.Field<string>("_van_trip_start_latitude"),
                            VanLongitudeAtTripStart = s.Field<string>("_van_trip_start_longitude"),
                            VanLatitudeAtVanReached = s.Field<string>("_van_reached_latitude"),
                            VanLongitudeAtVanReached = s.Field<string>("_van_reached_longitude"),
                            VanLatitudeAtTripEnd = s.Field<string>("_van_trip_end_latitude"),
                            VanLongitudeAtTripEnd = s.Field<string>("_van_trip_end_longitude"),
                            BreakdownLocationLatitude = s.Field<string>("_breakdown_location_latitude"),
                            BreakdownLocationLongitude = s.Field<string>("_breakdown_location_longitude"),
                            //----------------added on 25-11-2020 (start) -------------------------------------
                            TicketClosedReason = s.Field<string>("_ticket_closed_reason"),
                            //----------------added on 25-11-2020 (end) -------------------------------------

                            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")),
                            reopen_count = Convert.ToString(s.Field<Int32?>("_reopen_count")),
                            Score = s.Field<string>("_score"),
                            ChassisNo = s.Field<string>("_chassis_number"),
                            OdometerReading = s.Field<double>("_odometer_reading"),
                            //added on 03/12/2020
                            vehicle_warranty = s.Field<string>("_vehicle_warranty"),
                            vehicle_amc = s.Field<string>("_vehicle_amc"),
                            vehicle_emission_norms = s.Field<string>("_vehicle_emission_norms"),
                            vehicle_sales_date = s.Field<string>("_vehicle_sales_date"),
                            AssignedToUserLatitude = s.Field<string>("_assigned_lat"),
                            AssignedToUserLongitude = s.Field<string>("_assigned_long"),
                            BreackDownLongitude = s.Field<string>("_estimated_van_reach_time"),
                            VanReachedPunchTime = s.Field<DateTime?>("_van_reached_punch_time") == null ? "" : Convert.ToDateTime(s.Field<DateTime?>("_van_reached_punch_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),  //added on 23-12-2020

                        }).ToList();
                    }
                    objModel.TicketList = objList;
                    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;
        }


    public List<VehicleTypeModel> GetVehicleModelFilter(string fueltype,string[] vehicleTagging)
    {
      try
      {
        DataSet ds = new DataSet();
        List<VehicleTypeModel> objList = new List<VehicleTypeModel>();
        if (fueltype == null || fueltype == "null")
        {
          fueltype = "";
        }
        //else
        //{
        //  if (fueltype == "ELECTRIC")
        //  {

        //  }
        //  else
        //  {
        //    fueltype = "Diesel,CNG";
        //  }

        //}
              NpgsqlParameter[] nSqlParam = new NpgsqlParameter[2];
        nSqlParam[0] = new NpgsqlParameter("infueltype", fueltype);
        nSqlParam[1] = new NpgsqlParameter("invehicleTagging", vehicleTagging);
        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_vehicle_model_dashboard"], nSqlParam);
      
     
        if (ds.Tables[0].Rows.Count > 0)
        {
          objList = ds.Tables[0].AsEnumerable().Select(s => new VehicleTypeModel
          {
            VehicleTypeId = s.Field<string>("_name"),
            VehicleTypeName = s.Field<string>("_name")
          }).ToList();
        }
        return objList;
      }
      catch (Exception Ex)
      {
        objLog.ErrorLogFile("GlobalRepository GetAllVehicleTypeList", Ex.Message, path, errorlogtf);
        throw Ex;
      }
    }

    #endregion
  }



  #endregion
}