namespace VECV_WebApi.Models.BoatAPIRepository
{

  #region Namespaces

  using DBHelper;
  using LoggingHelper;
  using Newtonsoft.Json;
  using Newtonsoft.Json.Linq;
  using Npgsql;
  using RestSharp;
  using System;
  using System.Collections.Generic;
  using System.Configuration;
  using System.Data;
  using System.Data.Entity.Infrastructure;
  using System.Diagnostics;
  using System.Globalization;
  using System.IO;
  using System.Linq;
  using System.Net;
  using System.Net.Http;
  using System.Net.Http.Headers;
  using System.Net.Mail;
  using System.Net.Security;
  using System.Security.Cryptography;
  using System.Security.Cryptography.X509Certificates;
  using System.Text;
  using System.Threading.Tasks;
  using System.Web;
  using System.Web.Script.Serialization;
  using System.Xml;
  using System.Xml.Linq;
  using VECV_WebApi.Common;
  using VECV_WebApi.Models.Authorization;
  using VECV_WebApi.Models.BoatAPIModel;
  using VECV_WebApi.Models.Customer;
  using VECV_WebApi.Models.EmailServices;
  using VECV_WebApi.Models.Notification;
  using VECV_WebApi.Models.ServiceEngineer;
  using VECV_WebApi.Models.Ticket;
  using VECV_WebApi.Models.Tracking;
  using VECV_WebApi.Models.Util;
  using VECV_WebApi.Models.Vehicle;

  #endregion

  #region Repository Class

  /// <summary>
  /// This class contain method for ticket manipulation
  /// </summary>
  public class BoatAPIRepository
  {  
    #region Global Variables

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

    PushData UdanPushData = new PushData();

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

    /// <summary>
    /// making data log file path available to this class
    /// </summary>
    string logtf = (ConfigurationManager.AppSettings["Log"]);

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

    #endregion

    #region API Methods

    public TicketDetailsResponseModel GetTicketDetails(TicketDetailsRequestModel model)
    {
      TicketDetailsResponseModel objBoatAPIModel = new TicketDetailsResponseModel();
      try
      {
        DataSet ds = new DataSet();
        if (!string.IsNullOrEmpty(model.TicketId))
        {
          NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];
          nSqlParam[0] = new NpgsqlParameter("inticket_id", model.TicketId);         
           ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_boat_ticket_details_ticket_id"], nSqlParam);
        }
        else if (!string.IsNullOrEmpty(model.Reg_No))
        {
          NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];         
          nSqlParam[0] = new NpgsqlParameter("inregNo", model.Reg_No);
          ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_boat_ticket_details_reg_no"], nSqlParam);
        }
        if (ds.Tables[0].Rows.Count > 0)
        {
          objBoatAPIModel.TicketId = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_ticket_id_alias")).FirstOrDefault();
          objBoatAPIModel.Ticket_Status = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_ticket_status_alias")).FirstOrDefault();
          objBoatAPIModel.Actual_status = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_ticket_status")).FirstOrDefault();
          objBoatAPIModel.dealer_code = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_dealer_code")).FirstOrDefault();  //added on 15-01-2021
          objBoatAPIModel.dealer_name = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_dealer_name")).FirstOrDefault();  //added on 15-01-2021
          objBoatAPIModel.Technician_name = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_tech_name")).FirstOrDefault();
          objBoatAPIModel.Reg_No = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_reg_no")).FirstOrDefault();  //added on 15-01-2021
          objBoatAPIModel.OTP = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_otp")).FirstOrDefault();  //added on 15-01-2021
          objBoatAPIModel.Fuel_Type = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_fuel_type")).FirstOrDefault();  //added on 15-01-2021
          objBoatAPIModel.Assigned_To = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_assigned_to")).FirstOrDefault();  //added on 15-01-2021
          objBoatAPIModel.Driver_No = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_driver_no")).FirstOrDefault();  //added on 15-01-2021

        }
      }
      catch (Exception Ex)
      {
        objLog.ErrorLogFile("GetTicketDetails", Ex.Message, path, errorlogtf);
      }
      return objBoatAPIModel;
    }

    public string InsertBoatComment(updateRemarksRequestModel model)
    {
      string strmessage = "";
      try
      {
        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[2];
        nSqlParam[0] = new NpgsqlParameter("inticket_id", model.TicketId);
        nSqlParam[1] = new NpgsqlParameter("inremarks", model.SuggestionComment);
        DataSet ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_boat_api_remarks"], nSqlParam);

        strmessage = "success";//ds.Tables[0].Rows[0]["_ticket_id"].ToString();  // getting ticket id alias
                               // errorlogtf = (ConfigurationManager.AppSettings["ErrorLog"]) + ticketIdAlias;

      }
      catch (Exception Ex)
      {
        objLog.ErrorLogFile("InsertCogentComment", Ex.Message, path, errorlogtf);
      }
      return strmessage;
    }


    public List<VanListResponseModel> GetVanListBoatAPI(VanListRequestModel model)
    {
      List<VanListResponseModel> objListModel = new List<VanListResponseModel>();
      VanListResponseModel objModel = new VanListResponseModel();

      try
      {
        objAuthorization = new AuthenticationRepository();


        if (model.Fuel_type == null)
        {

        }
        else
        {
          if (model.Fuel_type.ToLower() == "electric")
          {

          }
          else
          {
            model.Fuel_type = "diesel,cng";
          }
        }
        DataSet ds = new DataSet();
          DataSet dsTracking = new DataSet();
        //NpgsqlParameter[] nSqlParam = new NpgsqlParameter[3];
        //nSqlParam[0] = new NpgsqlParameter("inuser_id", model.UserId);
        //nSqlParam[1] = new NpgsqlParameter("indealer_1sCode", DealerSCode);
        //nSqlParam[2] = new NpgsqlParameter("infuel_type", model.Fuel_type.ToLower());
        string deviceAlias = "";
        NpgsqlParameter[] nSqlParam1 = new NpgsqlParameter[2];
        nSqlParam1[0] = new NpgsqlParameter("indealer_1sCode", model.DealersCode.Trim());
        nSqlParam1[1] = new NpgsqlParameter("infuel_type", model.Fuel_type.ToLower());
        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_van_by_dealer"], nSqlParam1);
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
          if (ds.Tables[0].Rows[i]["_type"].ToString().Trim().ToLower() == "van")
          {
            if (i == 0)
            {
              deviceAlias = ds.Tables[0].Rows[i]["device_alias"].ToString();
            }
            else
            {
              deviceAlias = deviceAlias + "," + ds.Tables[0].Rows[i]["device_alias"].ToString();
            }
          }
        }

          //Get device tracking log
          NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];
        
        nSqlParam[0] = new NpgsqlParameter("indevice_alias", deviceAlias);
        dsTracking = NpgSqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["Vecv_Tracking"].ToString(), CommandType.StoredProcedure, ConfigurationManager.AppSettings["vecv_log_sp_get_current_log_devices_user_wise"], nSqlParam);

        List<TrackingModel> listTracking = new List<TrackingModel>();
        listTracking = dsTracking.Tables[0].AsEnumerable().Select(s => new TrackingModel
        {

          LogId = s.Field<Int32>("_log_id"),
          DeviceAlias = s.Field<string>("_device_alias"),
          Latitude = s.Field<string>("_latitude"),
          Longitude = s.Field<string>("_longitude"),
        //  LogTime = s.Field<DateTime?>("_log_time") != null ? Convert.ToDateTime(s.Field<DateTime?>("_log_time")).AddMinutes(model.UtcMinute).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]) : Convert.ToDateTime(s.Field<DateTime?>("_log_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
          IsConnected = s.Field<string>("_device_status"),
          Xcordinate = s.Field<string>("_x_coordinate"),
          Ycordinate = s.Field<string>("_y_coordinate"),
          Zcordinate = s.Field<string>("_z_coordinate"),

        //  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"]),
          DeviceStatus = s.Field<string>("_device_status")
        }).ToList();

        if (ds.Tables[0].Rows.Count > 0)
        {
          for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
          {
            objModel = new VanListResponseModel();
           
             
                objModel.DealerVanId = ds.Tables[0].Rows[i]["dealer_van_id"].ToString();
                objModel.DealerVanName = ds.Tables[0].Rows[i]["dealer_van_name"].ToString();
            objModel.Licnsce_key = ds.Tables[0].Rows[i]["dealer_van_city"].ToString();
            objModel.Tech_name = ds.Tables[0].Rows[i]["dealer_van_lattitude"].ToString();
            objModel.Van_reg_no = ds.Tables[0].Rows[i]["dealer_van_longitude"].ToString();
            ///   objModel.latitude = ds.Tables[0].Rows[i]["dealer_van_lattitude"].ToString();
            //  objModel.Longitude = ds.Tables[0].Rows[i]["dealer_van_longitude"].ToString();
            //    objModel.DealerVanCity = ds.Tables[0].Rows[i]["dealer_van_city"].ToString();
            objModel.DealerVanState = ds.Tables[0].Rows[i]["dealer_van_state"].ToString();
                objModel.DeviceAlias = ds.Tables[0].Rows[i]["device_alias"].ToString();
                objModel.NoOfAssignedTicket = ds.Tables[0].Rows[i]["no_of_assigned_ticket"].ToString();
                //objModel.t = ds.Tables[0].Rows[i]["_type"].ToString();
               // objModel.RemainingTime = ds.Tables[0].Rows[i]["remaining_time"].ToString();
                objModel.DealerSCode = ds.Tables[0].Rows[i]["dealer_id"].ToString();
                objModel.DealerOrVanContactNo = ds.Tables[0].Rows[i]["contact_number"].ToString();
                objModel.Fuel_type = ds.Tables[0].Rows[i]["fueltype"].ToString();
                VanDealerListStateWise objVanDealerListStateWise = new VanDealerListStateWise();
                objVanDealerListStateWise = GetVanlatlngbydevicealiasBoat(objModel.DeviceAlias);
                objModel.latitude = objVanDealerListStateWise.DealerVanLattitude;
                objModel.Longitude = objVanDealerListStateWise.DealerVanLongitude;


            objListModel.Add(objModel);

          }

          }
          return objListModel;
      }
      catch (Exception Ex)
      {
        
        objLog.ErrorLogFile("GetVanDealerListStateWise", Ex.Message, path, errorlogtf);
        throw Ex;
      }
    }
    public VanDealerListStateWise GetVanlatlngbydevicealiasBoat(string deviceAlias)
    {
      VanDealerListStateWise objVanDealerListStateWise = new VanDealerListStateWise();
      DataSet dsTracking = new DataSet();
      try
      {
        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[5];

        nSqlParam = new NpgsqlParameter[1];
        nSqlParam[0] = new NpgsqlParameter("indevice_alias", deviceAlias);
        dsTracking = NpgSqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["Vecv_Tracking"].ToString(), CommandType.StoredProcedure, ConfigurationManager.AppSettings["vecv_log_sp_get_current_log_devices_user_wise"], nSqlParam);
        TrackingModel objlistTracking = new TrackingModel();
        List<TrackingModel> listTracking = new List<TrackingModel>();
        listTracking = dsTracking.Tables[0].AsEnumerable().Select(s => new TrackingModel
        {

          LogId = s.Field<Int32>("_log_id"),
          DeviceAlias = s.Field<string>("_device_alias"),
          Latitude = s.Field<string>("_latitude"),
          Longitude = s.Field<string>("_longitude"),
          // LogTime = s.Field<DateTime?>("_log_time") != null ? Convert.ToDateTime(s.Field<DateTime?>("_log_time")).AddMinutes(model.UtcMinute).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]) : Convert.ToDateTime(s.Field<DateTime?>("_log_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
          IsConnected = s.Field<string>("_device_status"),
          Xcordinate = s.Field<string>("_x_coordinate"),
          Ycordinate = s.Field<string>("_y_coordinate"),
          Zcordinate = s.Field<string>("_z_coordinate"),

          //  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"]),
          DeviceStatus = s.Field<string>("_device_status")
        }).ToList();
        objlistTracking = listTracking.FirstOrDefault();
        objVanDealerListStateWise.DealerVanLattitude = objlistTracking.Latitude;
        objVanDealerListStateWise.DealerVanLongitude = objlistTracking.Longitude;
        //  objVanDealerListStateWise.DealerVanLattitude = objlistTracking.Latitude;
      }
      catch (Exception Ex)
      {
        objLog.ErrorLogFile("GetVanlatlngbydevicealias", Ex.Message, path, errorlogtf);
      }
      return objVanDealerListStateWise;
    }



    public InsertOpenTicket UpdateOpenTicketaBoat(InsertOpenTicket model)
    {
      InsertOpenTicket objModel = new InsertOpenTicket();
      GlobalRepository objGlobalRepository = new GlobalRepository(_connStr);
      SendNotificationModel objGcmModel = new SendNotificationModel();
      try
      {
        objModel.Status = "1";
        DataSet ds2 = new DataSet();
        DataSet dsGcm = new DataSet();
        int UtcMinute = 330;
        objAuthorization = new AuthenticationRepository();
           
            string strTicketStatus = "";
            DataSet dsOpen = new DataSet();
            NpgsqlParameter[] nSqlParamopen = new NpgsqlParameter[1];
            nSqlParamopen[0] = new NpgsqlParameter("inticketid ", model.TicketId);
            dsOpen = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_open_ticket_Cogent_API_By_ticketId"], nSqlParamopen);
            if (dsOpen.Tables.Count > 0)
            {
              if (dsOpen.Tables[0].Rows.Count > 0)
              {
                strTicketStatus = dsOpen.Tables[0].Rows[0]["_ticket_status"].ToString();

              }
            }
            // get latest ticket activity for the ticket.
            NpgsqlParameter[] nSqlParam0 = new NpgsqlParameter[1];
            nSqlParam0[0] = new NpgsqlParameter("inticket_id", model.TicketId);
            DataSet ds_latest_ticket_activity = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_ticket_activity"], nSqlParam0);
            if (model.TicketStatus == 4)
            {
              string[] CSMContactNo = model.preclosure_reason.ToString().Split(',');
              int cout = CSMContactNo.Length;
              if (cout == 2)
              {
                {
                  model.preclosure_reason = CSMContactNo[0];
                  model._24HrsReason = CSMContactNo[1];
                }
              }
              else
              {
                model.preclosure_reason = CSMContactNo[0];
              }

            }
            // updating ticket status and creating activity by trigger
            NpgsqlParameter[] nSqlParam = new NpgsqlParameter[39];
            nSqlParam[0] = new NpgsqlParameter("inticket_id", model.TicketId);
            nSqlParam[1] = new NpgsqlParameter("inassigned_to", model.AssignedTo);
            nSqlParam[2] = new NpgsqlParameter("inreported_via", model.ReportedVia);
            nSqlParam[3] = new NpgsqlParameter("inpriority", model.Priority);
            nSqlParam[4] = new NpgsqlParameter("incustomer_score", model.CustomerScore);
            nSqlParam[5] = new NpgsqlParameter("inbreakdown_location", model.BreakdownLocation);
            nSqlParam[6] = new NpgsqlParameter("inlast_modified_by", model.LastModifiedBy);
            //  nSqlParam[7] = new NpgsqlParameter("inlast_modified_time", model.LastModifiedTime);
            nSqlParam[7] = new NpgsqlParameter("inlast_modified_time", DateTime.Now.AddMinutes(-UtcMinute));
            nSqlParam[8] = new NpgsqlParameter("inticket_status", model.TicketStatus);
            nSqlParam[9] = new NpgsqlParameter("indescription", model.Description);
            nSqlParam[10] = new NpgsqlParameter("inbreakdown_longitude", model.BreakdownLongitude);
            nSqlParam[11] = new NpgsqlParameter("inbreakdown_lattitude", model.BreakdownLattitude);
            nSqlParam[12] = new NpgsqlParameter("inassigned_to_user_id", model.AssignedToUserId);
            nSqlParam[13] = new NpgsqlParameter("inassigned_to_user_lattitude", model.AssignedToUserLattitude);
            nSqlParam[14] = new NpgsqlParameter("inassigned_to_user_longitude", model.AssignedToUserLongitude);
            nSqlParam[15] = new NpgsqlParameter("inestimated_time_for_job_completion", model.EstimatedTimeForJobCompletion);
            nSqlParam[16] = new NpgsqlParameter("inestimated_time_for_job_completion_submit_time", model.EstimatedTimeForJobCompletionSubmitTime);
            nSqlParam[17] = new NpgsqlParameter("inbreakdown_location_landmark", model.BreakdownLocationLandmark);
            nSqlParam[18] = new NpgsqlParameter("inroute_id", model.RouteId);
            nSqlParam[19] = new NpgsqlParameter("inrepair_cost", model.RepairCost);
            nSqlParam[20] = new NpgsqlParameter("indefault_sla_time", model.DefaultSlaTime);
            nSqlParam[21] = new NpgsqlParameter("insla_missed_reason", model.SlaMissedReason);
            nSqlParam[22] = new NpgsqlParameter("insuggestion_comment", model.SuggestionComment);
            nSqlParam[23] = new NpgsqlParameter("indefault_col_1", model.JobCompleteResponseTime);
            nSqlParam[24] = new NpgsqlParameter("indefault_col_2", model.DefaultCol2);
            nSqlParam[25] = new NpgsqlParameter("indefault_col_3", model.DefaultCol3);
            nSqlParam[26] = new NpgsqlParameter("inopportunity_loss", model.OpportunityLost);
            nSqlParam[27] = new NpgsqlParameter("inreason_for_more_then_24hours", model._24HrsReason);
            nSqlParam[28] = new NpgsqlParameter("inestimated_distance", null);
            nSqlParam[29] = new NpgsqlParameter("inother_remarks", model.OtherRemarks);
            nSqlParam[30] = new NpgsqlParameter("inchassis_number", model.ChassisNo);
            nSqlParam[31] = new NpgsqlParameter("inodometer_reading", model.OdometerReading);
            nSqlParam[32] = new NpgsqlParameter("invehicle_type", model.VehicleType);
            nSqlParam[33] = new NpgsqlParameter("inprevious_odometer_reading", model.PreviousOdometerReading);
            //added on 23-09-2020
            nSqlParam[34] = new NpgsqlParameter("invan_latitude", model.VanLatitude);
            nSqlParam[35] = new NpgsqlParameter("invan_longitude", model.VanLongitude);
            nSqlParam[36] = new NpgsqlParameter("inmobile_request", model.IsMobileRequest);
            nSqlParam[37] = new NpgsqlParameter("in_estimated_van_reach_time", model.estimatedVAnReachTime);
            nSqlParam[38] = new NpgsqlParameter("inpreclosure_reason", model.preclosure_reason);


            ds2 = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_update_open_ticket_status"], nSqlParam);
           #region Sending SMS ( When ticket was assigned to Van and is in Pre-Closure )
            // For the first time message sending when ticket status is PreClosure first time

            if (model.TicketStatus == 4)
            {
              DataSet ds1 = new DataSet();
              DataSet ds = new DataSet();
              DataSet ds3 = new DataSet();

              // get contact details for sending sms.
              NpgsqlParameter[] nSqlParam1 = new NpgsqlParameter[2];
              nSqlParam1[0] = new NpgsqlParameter("inticket_id", model.TicketId);
              nSqlParam1[1] = new NpgsqlParameter("invehicle_reg", null);
              ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_contact_details_for_sms_api"], nSqlParam1);

              string ticketIdAlias = ds.Tables[0].Rows[0]["_ticket_id_alias"].ToString();

              // get sms content details.
              nSqlParam = new NpgsqlParameter[3];
              nSqlParam[0] = new NpgsqlParameter("inregistration_number", null);
              nSqlParam[1] = new NpgsqlParameter("inmobile_number", null);
              nSqlParam[2] = new NpgsqlParameter("inticket_number", model.TicketId);
              ds1 = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_customer_open_ticket_details"], nSqlParam);

              nSqlParam = new NpgsqlParameter[1];
              nSqlParam[0] = new NpgsqlParameter("inticket_number", model.TicketId);
              ds2 = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_customer_open_ticket_full_details_ticket_wise"], nSqlParam);


              //get sending sms api url.
              string url = System.Web.Configuration.WebConfigurationManager.AppSettings["SmsUrl"].ToString();

              // get msg format                        
              string msgVanTicketPreCloserForCustomer = System.Web.Configuration.WebConfigurationManager.AppSettings["msgVanTicketPreCloserForCustomer"].ToString();
              string msgVanTicketPreCloserForOwner = System.Web.Configuration.WebConfigurationManager.AppSettings["msgVanTicketPreCloserForOwner"].ToString();
              string msgVanTicketPreCloserForWM = System.Web.Configuration.WebConfigurationManager.AppSettings["msgVanTicketPreCloserForWM"].ToString();
              string msgVanTicketPreCloserForCSM = System.Web.Configuration.WebConfigurationManager.AppSettings["msgVanTicketPreCloserForCSM"].ToString();
              if (ds.Tables.Count > 0)
              {
                if (ds.Tables[0].Rows.Count > 0)
                {
                  //string[] id = model.TicketId.Split('-');

                  msgVanTicketPreCloserForCustomer = string.Format(msgVanTicketPreCloserForCustomer,
                                                          ticketIdAlias,    // Ticket Id
                                                          ds.Tables[0].Rows[0]["vehicle_registration_number"].ToString(), // Vehicle Registration Number
                                                          ds.Tables[0].Rows[0]["dealer_dealer_name"].ToString() + " (" + ds2.Tables[0].Rows[0]["dealer_city"].ToString() + ") "// Dealer Name, Dealer City
                                                          );



                  msgVanTicketPreCloserForWM = string.Format(msgVanTicketPreCloserForWM,
                                                          ticketIdAlias,    // Ticket Id
                                                          ds.Tables[0].Rows[0]["dealer_or_service_engg"].ToString() + " " + ds.Tables[0].Rows[0]["service_engineer_contact_number"].ToString(),   // Service_Engineer_Name Service_Engineer_Contact
                                                          ds.Tables[0].Rows[0]["customer_mobile_number_1"].ToString(),   // owner number
                                                          ds.Tables[0].Rows[0]["customer_contact_no"].ToString()   // driver number
                                                          );

                  msgVanTicketPreCloserForCSM = string.Format(msgVanTicketPreCloserForCSM,
                                                          ticketIdAlias,    // Ticket ID
                                                          ds.Tables[0].Rows[0]["dealer_dealer_name"].ToString(),  // Dealer Name, Dealer City
                                                          ds.Tables[0].Rows[0]["customer_mobile_number_1"].ToString(),   // owner number
                                                          ds.Tables[0].Rows[0]["customer_contact_no"].ToString()   // driver number
                                                          );


                  string CustomerSms = string.Format(url, ds.Tables[0].Rows[0]["customer_contact_no"].ToString(), msgVanTicketPreCloserForCustomer);
                  string DealerSms = string.Format(url, ds.Tables[0].Rows[0]["_dealer_contact_number1"].ToString(), msgVanTicketPreCloserForWM);
                  //string OwnerSms = string.Format(url, ds.Tables[0].Rows[0]["customer_mobile_number_1"].ToString(), msgVanTicketPreCloserForOwner);
                  string OwnerSms = string.Format(url, ds.Tables[0].Rows[0]["customer_mobile_number_1"].ToString(), msgVanTicketPreCloserForCustomer);

                  // get all contact no split in array.
                  string[] CSMContactNo = ds.Tables[0].Rows[0]["csm_contact_no"].ToString().Split(',');

                  for (int i = 0; i < CSMContactNo.Count(); i++)
                  {
                    string CSMsms = string.Format(url, CSMContactNo[i], msgVanTicketPreCloserForCSM);
                    objGlobalRepository.SendMessage(CSMsms);
                  }
                  //   objGlobalRepository.SendMessage(CustomerSms);    //commented on 18-12-2020(stop sending message to customer on preclosure)
                  //  objGlobalRepository.SendMessage(OwnerSms); 
                  string[] WMContactNo = ds.Tables[0].Rows[0]["WM_contact_no"].ToString().Split(',');

                  for (int i = 0; i < WMContactNo.Count(); i++)
                  {
                    string WMsms = string.Format(url, WMContactNo[i], msgVanTicketPreCloserForWM);
                    objGlobalRepository.SendMessage(WMsms);
                  }

                  string[] SiteIchargeContactNo = ds.Tables[0].Rows[0]["site_incharge_contact"].ToString().Split(',');

                  for (int i = 0; i < SiteIchargeContactNo.Count(); i++)
                  {

                    string WMsms = string.Format(url, SiteIchargeContactNo[i], msgVanTicketPreCloserForWM);
                    objGlobalRepository.SendMessage(WMsms);

                  }

                  string typePreclosure = "";
                  if (model.TicketStatus.ToString() == strTicketStatus)
                  {

                  }
                  else
                  {
                    if (model.preclosure_reason == "Closure Request")
                    {
                      typePreclosure = "closure";
                    }
                    if (model.preclosure_reason == "Reassignment Request")
                    {
                      typePreclosure = "reassign";
                    }
               TicketRepository objticketRepository = new TicketRepository(_connStr);
                objticketRepository.InsertTicketDetailOnPreclousreInVECV(model, ticketIdAlias, typePreclosure,model.SuggestionComment);
                    //objGlobalRepository.SendMessage(OwnerSms);
                  }
                }
                //objGlobalRepository.SendMessage(OwnerSms);
              }
              #region Sending sms to KAM when ticket is Assigned to Van and in Preclosure

              // checking whether this ticket creation belongs to KAM user or not
              NpgsqlParameter[] nSqlParam01 = new NpgsqlParameter[1];
              nSqlParam01[0] = new NpgsqlParameter("inticket_id", model.TicketId);
              DataSet ds_kam_ticket_details = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_check_kam_ticket_or_not"], nSqlParam01);

              //=============================================================================================
              string msgKamPreClosureTicket = System.Web.Configuration.WebConfigurationManager.AppSettings["msgKamPreClosureTicket"].ToString();
              // set content in msg
              msgKamPreClosureTicket = string.Format(msgKamPreClosureTicket,
                                                    ticketIdAlias,    // ticket id
                                                    ds.Tables[0].Rows[0]["vehicle_registration_number"].ToString(),  // vehicle reg no.
                                                    ds.Tables[0].Rows[0]["dealer_dealer_name"].ToString() + "( " + ds2.Tables[0].Rows[0]["dealer_city"].ToString() + " )" // Dealer Name, Dealer City
                                                    );

              string KamSms = string.Format(url, ds.Tables[0].Rows[0]["customer_mobile_number_1"].ToString(), msgKamPreClosureTicket);
              objGlobalRepository.SendMessage(KamSms);
              //=============================================================================================
              if (ds_kam_ticket_details.Tables.Count > 0)
              {

                // if this ticket belongs to KAM
                if (ds_kam_ticket_details.Tables[0].Rows.Count > 0)
                {
                  //-----------Sending GCM push to all KAM users-------------------------
                  NpgsqlParameter[] nSqlParam11 = new NpgsqlParameter[1];
                  nSqlParam11[0] = new NpgsqlParameter("inkam_id", ds_kam_ticket_details.Tables[0].Rows[0]["_kam_id"].ToString());
                  DataSet dsGcm1 = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_kam_user_devices_details_open_tickets"], nSqlParam11);
                  if (dsGcm1.Tables.Count > 0)
                  {

                    if (dsGcm1.Tables[0].Rows.Count > 0)
                    {
                      foreach (DataRow dr in dsGcm1.Tables[0].Rows)
                      {

                        string notificationMessageFormat = ConfigurationManager.AppSettings["NotificationMessagePreClosureKamTicket"].ToString();
                        string notificationMessage = string.Format(notificationMessageFormat, ticketIdAlias, ConfigurationManager.AppSettings["TollFreeNo"].ToString());

                        SendNotificationModel objGcmModel1 = new SendNotificationModel();
                        objGcmModel1.DeviceGcmId = dr["_gcm_id"].ToString();
                        objGcmModel1.TicketId = ds_kam_ticket_details.Tables[0].Rows[0]["_kam_id"].ToString();
                        objGcmModel1.NotificationCode = ConfigurationManager.AppSettings["MessagePreClosureKamTicket"].ToString();
                        objGcmModel1.NotificationMessage = notificationMessage;
                        if (Convert.ToInt32(dr["_push_notification_bit"]) == 0)
                        {
                          objGcmModel1 = objGlobalRepository.GcmSendNotificationForKam(objGcmModel1);
                          objModel.Status = objGcmModel1.Status;
                        }
                        else
                        {
                          objGcmModel1 = objGlobalRepository.FcmSendNotificationForKam(objGcmModel1);
                          objModel.Status = objGcmModel1.Status;
                        }
                        //objModel.SenderId = objGcmModel.SenderId;
                      }
                    }
                  }
                  //----------------------------------------------------------------------
                }
              }
              #endregion

            }

            #endregion

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


    public VanDetailsResponseModel Getvandetailsbyfilter(VanDetailsRequestModel model)
    {
      VanDetailsResponseModel objticketModel = new VanDetailsResponseModel();
      try
      {
        DataSet ds = new DataSet();

        if (!string.IsNullOrEmpty(model.Reg_no))
        {
          
          NpgsqlParameter[] nSqlParam1 = new NpgsqlParameter[1];
        
          nSqlParam1[0] = new NpgsqlParameter("invehicle_reg", model.Reg_no);
          ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_van_details_reg_no"], nSqlParam1);

        }
        else
        {
            NpgsqlParameter[] nSqlParam1 = new NpgsqlParameter[1];

          nSqlParam1[0] = new NpgsqlParameter("inliscence_key", model.Liscensce_key);
            ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_van_details_liscensce_key"], nSqlParam1);
          }
          if (ds.Tables[0].Rows[0]["_device_alias"].ToString().Trim() != null && ds.Tables[0].Rows[0]["_device_alias"].ToString().Trim() != "")
        {

          //added on 19-11-2020 start
          
          objticketModel.devcie_alias = ds.Tables[0].Rows[0]["_device_alias"].ToString();
          objticketModel.Reg_no = ds.Tables[0].Rows[0]["_reg_no"].ToString();
          objticketModel.Liscensce_key = ds.Tables[0].Rows[0]["_liscense_key"].ToString();
          VanDealerListStateWise objVanDealerListStateWise = new VanDealerListStateWise();
          objVanDealerListStateWise = GetVanlatlngbydevicealiasBoat(objticketModel.devcie_alias);
          objticketModel.Latitude = objVanDealerListStateWise.DealerVanLattitude;
          objticketModel.Longitude = objVanDealerListStateWise.DealerVanLongitude;

        }
        return objticketModel;
      }
      catch (Exception Ex)
      {
        // write error log into file
        // objticketModel.Status = "0";
        //   objticketModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;

        objLog.ErrorLogFile("Getvandetails", Ex.Message, path, errorlogtf);
        return objticketModel;
      }
    }

    #endregion
  }
  #endregion
}