namespace VECV_WebApi.Models.Sync
{
    #region Namespaces

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

    #endregion

    #region Repository Class

    /// <summary>
    /// This class contains method related to syncing
    /// </summary>
    public class SyncRepository
    {

        #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 vecv_godata database 
        /// </summary>
        public SyncRepository(string connString)
        {
            this._connStr = connString;
        }

        #endregion

        #region API Methods

        /// <summary>
        /// To get synced open tickets details device alias wise
        /// </summary>
        /// <param name="model">open ticket sync info</param>
        /// <returns>status and synced open tickets details device alias wise</returns>
        public List<SyncOpenModel> GetSyncOpenTicketDetail(SyncOpenModel model)
        {
            List<SyncOpenModel> objListSync = new List<SyncOpenModel>();
            SyncOpenModel objModelSync = new SyncOpenModel();

            List<CustomerOpenTicketDetail> objListCustomerOpenTicketDetail = new List<CustomerOpenTicketDetail>();
            List<TicketActivityOpenModel> objListOpenTicketActivityTicket = new List<TicketActivityOpenModel>();

            try
            {

                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    DataSet dsActivity = new DataSet();

                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[2];
                    nSqlParam[0] = new NpgsqlParameter("indevice_alias", model.DeviceAlias);
                    nSqlParam[1] = new NpgsqlParameter("db_syn_last_time", model.DbSynLastTime);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_ticket_open"], nSqlParam);

                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        // get open tciket details for android syn from last syn time
                        objListCustomerOpenTicketDetail = ds.Tables[0].AsEnumerable().Select(s => new CustomerOpenTicketDetail
                        {
                            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(s.Field<DateTime?>("creation_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            LastModifiedBy = s.Field<string>("last_modified_by"),
                            LastModifiedTime = Convert.ToDateTime(s.Field<DateTime?>("last_modified_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            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<Boolean?>("isdeclined"),
                            EstimatedTimeForJobCompletion = s.Field<int?>("estimated_time_for_job_completion"),
                            TotalTicketLifecycleTimeSla = s.Field<int?>("total_ticket_lifecycle_time_sla"),
                            EstimatedTimeForJobCompletionSubmitTime = Convert.ToDateTime(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"),
                            EstimatedDistance = s.Field<string>("estimated_distance"),
                            TicketIdAlias = s.Field<string>("_ticket_id_alias"),
                            ChassisNumber = s.Field<string>("_chassis_number"),
                            OdometerReading = s.Field<double>("_odometer_reading"),

                        }).ToList();

                        string TicketId = "";
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {

                            if (i == 0)
                            {
                                TicketId = ds.Tables[0].Rows[i]["ticket_id"].ToString();
                            }
                            else
                            {
                                TicketId = TicketId + "," + ds.Tables[0].Rows[i]["ticket_id"].ToString();
                            }

                        }

                        // Get ticket activity ticket wise.
                        nSqlParam = new NpgsqlParameter[1];
                        nSqlParam[0] = new NpgsqlParameter("inticket_id", TicketId);
                        dsActivity = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_open_ticket_activity"], nSqlParam);

                        if (dsActivity.Tables[0].Rows.Count > 0)
                        {

                            objListOpenTicketActivityTicket = dsActivity.Tables[0].AsEnumerable().Select(s => new TicketActivityOpenModel
                            {
                                Id = s.Field<Int64?>("id"),
                                TicketId = s.Field<string>("ticket_id"),
                                ActivityTicketsOpenCreatedBy = s.Field<string>("created_by"),
                                ActivityTicketsOpenCreationTime = Convert.ToDateTime(s.Field<DateTime?>("creation_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                                Remarks = s.Field<string>("remarks"),
                                TicketActivityStatus = s.Field<int?>("ticket_activity_status")

                            }).ToList();


                        }

                        // add ticket list and activity in syn model.
                        objListSync.Add(new SyncOpenModel
                        {
                            TicketOpenList = objListCustomerOpenTicketDetail,
                            TicketActivityOpenList = objListOpenTicketActivityTicket,
                            DeviceAlias = model.DeviceAlias,
                            DbSynLastTime = model.DbSynLastTime,
                            Status = "1"

                        });
                        return objListSync;
                    }
                    else
                    {
                        objListSync.Add(new SyncOpenModel
                        {
                            DeviceAlias = model.DeviceAlias,
                            DbSynLastTime = model.DbSynLastTime,
                            Status = "1",
                            Message = ConfigurationManager.AppSettings["DataSetEmpty"].ToString()
                        });
                        return objListSync;
                    }
                }
                else
                {
                    objListSync.Add(new SyncOpenModel
                    {
                        DeviceAlias = model.DeviceAlias,
                        DbSynLastTime = model.DbSynLastTime,
                        Status = "0",
                        Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString()
                    });

                    return objListSync;
                }
            }
            catch (Exception Ex)
            {
                objListSync.Add(new SyncOpenModel
                {
                    DeviceAlias = model.DeviceAlias,
                    DbSynLastTime = model.DbSynLastTime,
                    Status = "0",
                    Message = Ex.Message
                });
                objLog.ErrorLogFile("GetSyncOpenTicketDetail", Ex.Message, path, errorlogtf);
                return objListSync;
            }
        }

        /// <summary>
        /// To get close tickets detail device alias wise
        /// </summary>
        /// <param name="model">close ticket sync info</param>
        /// <returns>status and close tickets detail device alias wise</returns>
        public List<SyncCloseModel> GetSyncCloseTicketDetail(SyncCloseModel model)
        {
            List<SyncCloseModel> objListSync = new List<SyncCloseModel>();
            SyncCloseModel objModelSync = new SyncCloseModel();

            List<CustomerOpenTicketDetail> objListCustomerOpenTicketDetail = new List<CustomerOpenTicketDetail>();
            List<TicketActivityCloseModel> objListOpenTicketActivityTicket = new List<TicketActivityCloseModel>();

            try
            {

                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    DataSet dsActivity = new DataSet();

                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[2];
                    nSqlParam[0] = new NpgsqlParameter("indevice_alias", model.DeviceAlias);
                    nSqlParam[1] = new NpgsqlParameter("db_syn_last_time", model.DbSynLastTime);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_ticket_closed"], nSqlParam);

                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        objListCustomerOpenTicketDetail = ds.Tables[0].AsEnumerable().Select(s => new CustomerOpenTicketDetail
                        {
                            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(s.Field<DateTime?>("creation_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            LastModifiedBy = s.Field<string>("last_modified_by"),
                            LastModifiedTime = Convert.ToDateTime(s.Field<DateTime?>("last_modified_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            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<Boolean?>("isdeclined"),
                            EstimatedTimeForJobCompletion = s.Field<int?>("estimated_time_for_job_completion"),
                            TotalTicketLifecycleTimeSla = s.Field<int?>("total_ticket_lifecycle_time_sla"),
                            EstimatedTimeForJobCompletionSubmitTime = Convert.ToDateTime(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"),
                            EstimatedDistance = s.Field<string>("estimated_distance")
                        }).ToList();

                        string TicketId = "";
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {

                            if (i == 0)
                            {
                                TicketId = ds.Tables[0].Rows[i]["ticket_id"].ToString();
                            }
                            else
                            {
                                TicketId = TicketId + "," + ds.Tables[0].Rows[i]["ticket_id"].ToString();
                            }

                        }

                        nSqlParam = new NpgsqlParameter[1];
                        nSqlParam[0] = new NpgsqlParameter("inticket_id", TicketId);
                        dsActivity = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_close_ticket_activity"], nSqlParam);

                        if (dsActivity.Tables[0].Rows.Count > 0)
                        {
                            objListOpenTicketActivityTicket = dsActivity.Tables[0].AsEnumerable().Select(s => new TicketActivityCloseModel
                            {
                                Id = s.Field<Int64?>("id"),
                                TicketId = s.Field<string>("ticket_id"),
                                ActivityTicketsOpenCreatedBy = s.Field<string>("created_by"),
                                ActivityTicketsOpenCreationTime = Convert.ToDateTime(s.Field<DateTime?>("creation_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                                Remarks = s.Field<string>("remarks"),
                                TicketActivityStatus = s.Field<int?>("ticket_activity_status")

                            }).ToList();


                        }
                        objListSync.Add(new SyncCloseModel
                        {
                            TicketCloseList = objListCustomerOpenTicketDetail,
                            TicketActivityCloseList = objListOpenTicketActivityTicket,
                            DeviceAlias = model.DeviceAlias,
                            DbSynLastTime = model.DbSynLastTime,
                            Status = "1"

                        });
                        return objListSync;
                    }
                    else
                    {
                        objListSync.Add(new SyncCloseModel
                        {
                            DeviceAlias = model.DeviceAlias,
                            DbSynLastTime = model.DbSynLastTime,
                            Status = "1",
                            Message = ConfigurationManager.AppSettings["DataSetEmpty"].ToString()
                        });
                        return objListSync;
                    }
                }
                else
                {
                    objListSync.Add(new SyncCloseModel
                    {
                        DeviceAlias = model.DeviceAlias,
                        DbSynLastTime = model.DbSynLastTime,
                        Status = "0",
                        Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString()
                    });

                    return objListSync;
                }
            }
            catch (Exception Ex)
            {
                objListSync.Add(new SyncCloseModel
                {
                    DeviceAlias = model.DeviceAlias,
                    DbSynLastTime = model.DbSynLastTime,
                    Status = "0",
                    Message = Ex.Message
                });
                objLog.ErrorLogFile("GetSyncCloseTicketDetail", Ex.Message, path, errorlogtf);
                return objListSync;
            }
        }

        /// <summary>
        /// To get Sync detail of open and close ticket
        /// </summary>
        /// <param name="model">ticket info</param>
        /// <returns>status and Sync detail of open and close ticket</returns>
        public List<SyncModel> GetSyncTicketDetail(SyncModel model)
        {
            List<SyncModel> objListSync = new List<SyncModel>();
            SyncOpenModel objModelSyncOpen = new SyncOpenModel();
            SyncCloseModel objModelSyncClose = new SyncCloseModel();

            List<CustomerOpenTicketDetail> objListCustomerOpenTicketDetail = new List<CustomerOpenTicketDetail>();
            List<TicketActivityOpenModel> objListOpenTicketActivityTicket = new List<TicketActivityOpenModel>();
            List<CustomerCloseTicketDetail> objListCustomerCloseTicketDetail = new List<CustomerCloseTicketDetail>();
            List<TicketActivityCloseModel> objListCloseTicketActivityTicket = new List<TicketActivityCloseModel>();

            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet dsTicketOpen = new DataSet();
                    DataSet dsActivityOpen = new DataSet();

                    DataSet dsTicketClose = new DataSet();
                    DataSet dsActivityClose = new DataSet();

                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[3];
                    nSqlParam[0] = new NpgsqlParameter("indevice_alias", model.DeviceAlias);
                    nSqlParam[1] = new NpgsqlParameter("inieminumber", model.Ieminumber);
                    nSqlParam[2] = new NpgsqlParameter("db_syn_last_time", model.DbSynLastTime);

                    dsTicketOpen = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_ticket_open"], nSqlParam);
                    dsTicketClose = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_ticket_closed"], nSqlParam);

                    #region open Ticket

                    if (dsTicketOpen.Tables[0].Rows.Count > 0)
                    {
                        objListCustomerOpenTicketDetail = dsTicketOpen.Tables[0].AsEnumerable().Select(s => new CustomerOpenTicketDetail
                        {

                            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(s.Field<DateTime?>("creation_time")).ToString("dd MMM yyy HH:mm:ss"),
                            LastModifiedBy = s.Field<string>("last_modified_by"),
                            LastModifiedTime = Convert.ToDateTime(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<Boolean?>("isdeclined"),
                            EstimatedTimeForJobCompletion = s.Field<int?>("estimated_time_for_job_completion"),
                            TotalTicketLifecycleTimeSla = s.Field<int?>("total_ticket_lifecycle_time_sla"),
                            EstimatedTimeForJobCompletionSubmitTime = Convert.ToDateTime(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"),
                            IsTripEnd = s.Field<bool>("_trip_end"),
                            ChassisNumber = s.Field<string>("_chassis_number"),
                            OdometerReading = s.Field<double>("_odometer_reading"),
                            VehicleType = s.Field<string>("_vehicle_type"),
                            PreviousOdometerReading = s.Field<double>("_previous_odometer_reading"),
                            //--------added on 31-12-2020 start------------------
                            VehicleWarranty = s.Field<string>("_vehicle_warranty") == null ? "" : s.Field<string>("_vehicle_warranty"),
                            VehicleAMC = s.Field<string>("_vehicle_amc") == null ? "" : s.Field<string>("_vehicle_amc"),
                            VehicleSalesDate = s.Field<string>("_vehicle_sales_date"),
                            TicketSource = s.Field<string>("_toll_free_no_source") == null ? "" : s.Field<string>("_toll_free_no_source"),
                            OwnerName = s.Field<string>("_owner_name") == null ? "" : s.Field<string>("_owner_name"),
                            CustomerId = s.Field<string>("_customer_id") == null ? "" : s.Field<string>("_customer_id"),
                            CustomerType = s.Field<string>("_customer_type") == null ? "" : s.Field<string>("_customer_type"),
                            // priya  15 oct 2022
                            IsTripStart = s.Field<Boolean?>("istripstart"),
                            IsKam = s.Field<Boolean?>("_is_kam"),
                            Product_Variant = s.Field<string>("_product_variant"),
                            Vehicle_Emission_Norms = s.Field<string>("_vehicle_emission_norms")
                            //--------added on 31-12-2020 end------------------
                        }).ToList();
                    }

                    #endregion

                    #region Close Ticket

                    if (dsTicketClose.Tables[0].Rows.Count > 0)
                    {
                        objListCustomerCloseTicketDetail = dsTicketClose.Tables[0].AsEnumerable().Select(s => new CustomerCloseTicketDetail
                        {
                            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(s.Field<DateTime?>("creation_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            LastModifiedBy = s.Field<string>("last_modified_by"),
                            LastModifiedTime = Convert.ToDateTime(s.Field<DateTime?>("last_modified_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            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<Boolean?>("isdeclined"),
                            EstimatedTimeForJobCompletion = s.Field<int?>("estimated_time_for_job_completion"),
                            TotalTicketLifecycleTimeSla = s.Field<int?>("total_ticket_lifecycle_time_sla"),
                            EstimatedTimeForJobCompletionSubmitTime = Convert.ToDateTime(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"),
                            IsTripEnd = s.Field<bool>("_trip_end"),
                            ChassisNo = s.Field<string>("_chassis_number"),


                            //OdoMeter = s.Field<double>("_odometer_reading"),   //commented existing condition on 31-12-2020
                            OdometerReading = s.Field<double>("_odometer_reading"),   //added condition on 31-12-2020

                            VehicleType = s.Field<string>("_vehicle_type"),
                            PreviousOdometerReading = s.Field<double>("_previous_odometer_reading"),
                            //--------added on 31-12-2020 start------------------
                            VehicleWarranty = s.Field<string>("_vehicle_warranty") == null ? "" : s.Field<string>("_vehicle_warranty"),
                            VehicleAMC = s.Field<string>("_vehicle_amc") == null ? "" : s.Field<string>("_vehicle_amc"),
                            VehicleSalesDate = s.Field<string>("_vehicle_sales_date"),
                            TicketSource = s.Field<string>("_toll_free_no_source") == null ? "" : s.Field<string>("_toll_free_no_source"),
                            OwnerName = s.Field<string>("_owner_name") == null ? "" : s.Field<string>("_owner_name"),
                            CustomerId = s.Field<string>("_customer_id") == null ? "" : s.Field<string>("_customer_id"),
                            CustomerType = s.Field<string>("_customer_type") == null ? "" : s.Field<string>("_customer_type"),
                            // Added by priya 21102022
                            IsTripStart = s.Field<Boolean?>("istripstart"),
                            IsKam = s.Field<Boolean?>("_is_kam"),
                            //--------added on 31-12-2020 end------------------
                        }).ToList();
                    }

                    #endregion

                    objListSync.Add(new SyncModel
                    {
                        TicketOpenList = objListCustomerOpenTicketDetail,
                        // TicketActivityOpenList = objListOpenTicketActivityTicket,
                        TicketCloseList = objListCustomerCloseTicketDetail,
                        // TicketActivityCloseList = objListCloseTicketActivityTicket,
                        DeviceAlias = model.DeviceAlias,
                        // DbSynLastTime = string.Format("{yyyy-MM-dd hh:mm:ss}", DateTime.UtcNow),
                        DbSynLastTime = DateTime.UtcNow.ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                        Status = "1"

                    });
                    return objListSync;
                }
                else
                {
                    objListSync.Add(new SyncModel
                    {
                        DeviceAlias = model.DeviceAlias,
                        DbSynLastTime = model.DbSynLastTime,
                        Status = "0",
                        Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString()
                    });

                    return objListSync;
                }
            }
            catch (Exception Ex)
            {
                objListSync.Add(new SyncModel
                {
                    DeviceAlias = model.DeviceAlias,
                    DbSynLastTime = model.DbSynLastTime,
                    Status = "0",
                    Message = Ex.Message
                });
                objLog.ErrorLogFile("GetSyncTicketDetail", Ex.Message, path, errorlogtf);
                return objListSync;
            }
        }

        /// <summary>
        /// To get Sync detail of open and close ticket for dealer
        /// </summary>
        /// <param name="model">ticket info</param>
        /// <returns>status and Sync detail of open and close ticket</returns>
        public List<SyncModel> GetSyncTicketDetail_Dealer(SyncModel model)
        {
            List<SyncModel> objListSync = new List<SyncModel>();
            SyncOpenModel objModelSyncOpen = new SyncOpenModel();
            SyncCloseModel objModelSyncClose = new SyncCloseModel();

            List<CustomerOpenTicketDetail> objListCustomerOpenTicketDetail = new List<CustomerOpenTicketDetail>();
            List<TicketActivityOpenModel> objListOpenTicketActivityTicket = new List<TicketActivityOpenModel>();
            List<CustomerCloseTicketDetail> objListCustomerCloseTicketDetail = new List<CustomerCloseTicketDetail>();
            List<TicketActivityCloseModel> objListCloseTicketActivityTicket = new List<TicketActivityCloseModel>();

            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet dsTicketOpen = new DataSet();
                    DataSet dsActivityOpen = new DataSet();

                    DataSet dsTicketClose = new DataSet();
                    DataSet dsActivityClose = new DataSet();

                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[2];
                    nSqlParam[0] = new NpgsqlParameter("indealer_code", model.DealerCode);
                    nSqlParam[1] = new NpgsqlParameter("db_syn_last_time", model.DbSynLastTime);

                    dsTicketOpen = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_ticket_open_dealer"], nSqlParam);
                    dsTicketClose = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_ticket_closed_dealer"], nSqlParam);

                    #region open Ticket

                    if (dsTicketOpen.Tables[0].Rows.Count > 0)
                    {
                        objListCustomerOpenTicketDetail = dsTicketOpen.Tables[0].AsEnumerable().Select(s => new CustomerOpenTicketDetail
                        {

                            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(s.Field<DateTime?>("creation_time")).ToString("dd MMM yyy HH:mm:ss"),
                            LastModifiedBy = s.Field<string>("last_modified_by"),
                            LastModifiedTime = Convert.ToDateTime(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<Boolean?>("isdeclined"),
                            EstimatedTimeForJobCompletion = s.Field<int?>("estimated_time_for_job_completion"),
                            TotalTicketLifecycleTimeSla = s.Field<int?>("total_ticket_lifecycle_time_sla"),
                            EstimatedTimeForJobCompletionSubmitTime = Convert.ToDateTime(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();
                    }

                    #endregion

                    #region Close Ticket

                    if (dsTicketClose.Tables[0].Rows.Count > 0)
                    {
                        objListCustomerCloseTicketDetail = dsTicketClose.Tables[0].AsEnumerable().Select(s => new CustomerCloseTicketDetail
                        {
                            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(s.Field<DateTime?>("creation_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            LastModifiedBy = s.Field<string>("last_modified_by"),
                            // LastModifiedTime = Convert.ToDateTime(s.Field<DateTime?>("last_modified_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            LastModifiedTime = Convert.ToDateTime(s.Field<DateTime?>("last_modified_time")).AddMinutes(Convert.ToInt16(ConfigurationManager.AppSettings["UtcMinute"])).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            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<Boolean?>("isdeclined"),
                            EstimatedTimeForJobCompletion = s.Field<int?>("estimated_time_for_job_completion"),
                            TotalTicketLifecycleTimeSla = s.Field<int?>("total_ticket_lifecycle_time_sla"),
                            EstimatedTimeForJobCompletionSubmitTime = Convert.ToDateTime(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();
                    }

                    #endregion

                    objListSync.Add(new SyncModel
                    {
                        TicketOpenList = objListCustomerOpenTicketDetail,
                        // TicketActivityOpenList = objListOpenTicketActivityTicket,
                        TicketCloseList = objListCustomerCloseTicketDetail,
                        // TicketActivityCloseList = objListCloseTicketActivityTicket,
                        DeviceAlias = model.DeviceAlias,
                        // DbSynLastTime = string.Format("{yyyy-MM-dd hh:mm:ss}", DateTime.UtcNow),
                        DbSynLastTime = DateTime.UtcNow.ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                        Status = "1"

                    });
                    return objListSync;
                }
                else
                {
                    objListSync.Add(new SyncModel
                    {
                        DeviceAlias = model.DeviceAlias,
                        DbSynLastTime = model.DbSynLastTime,
                        Status = "0",
                        Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString()
                    });

                    return objListSync;
                }
            }
            catch (Exception Ex)
            {
                objListSync.Add(new SyncModel
                {
                    DeviceAlias = model.DeviceAlias,
                    DbSynLastTime = model.DbSynLastTime,
                    Status = "0",
                    Message = Ex.Message
                });
                objLog.ErrorLogFile("GetSyncTicketDetail_Dealer", Ex.Message, path, errorlogtf);
                return objListSync;
            }
        }



        /// <summary>
        /// To get Sync detail of open and close ticket for KAM
        /// </summary>
        /// <param name="model">ticket info</param>
        /// <returns>status and Sync detail of open and close ticket</returns>
        public List<SyncModel> GetSyncTicketDetail_Kam(SyncModel model)
        {
            List<SyncModel> objListSync = new List<SyncModel>();
            SyncOpenModel objModelSyncOpen = new SyncOpenModel();
            SyncCloseModel objModelSyncClose = new SyncCloseModel();

            List<CustomerOpenTicketDetail> objListCustomerOpenTicketDetail = new List<CustomerOpenTicketDetail>();
            List<TicketActivityOpenModel> objListOpenTicketActivityTicket = new List<TicketActivityOpenModel>();
            List<CustomerCloseTicketDetail> objListCustomerCloseTicketDetail = new List<CustomerCloseTicketDetail>();
            List<TicketActivityCloseModel> objListCloseTicketActivityTicket = new List<TicketActivityCloseModel>();

            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet dsTicketOpen = new DataSet();
                    DataSet dsActivityOpen = new DataSet();

                    DataSet dsTicketClose = new DataSet();
                    DataSet dsActivityClose = new DataSet();

                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[2];
                    nSqlParam[0] = new NpgsqlParameter("inkam_id", model.KamUserId);
                    nSqlParam[1] = new NpgsqlParameter("db_syn_last_time", model.DbSynLastTime);

                    dsTicketOpen = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_ticket_open_kam"], nSqlParam);
                    dsTicketClose = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_ticket_closed_kam"], nSqlParam);

                    #region open Ticket

                    if (dsTicketOpen.Tables[0].Rows.Count > 0)
                    {
                        objListCustomerOpenTicketDetail = dsTicketOpen.Tables[0].AsEnumerable().Select(s => new CustomerOpenTicketDetail
                        {
                            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(s.Field<DateTime?>("creation_time")).ToString("dd MMM yyy HH:mm:ss"),
                            LastModifiedBy = s.Field<string>("last_modified_by"),
                            LastModifiedTime = Convert.ToDateTime(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<Boolean?>("isdeclined"),
                            EstimatedTimeForJobCompletion = s.Field<int?>("estimated_time_for_job_completion"),
                            TotalTicketLifecycleTimeSla = s.Field<int?>("total_ticket_lifecycle_time_sla"),
                            EstimatedTimeForJobCompletionSubmitTime = Convert.ToDateTime(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();
                    }

                    #endregion

                    #region Close Ticket

                    if (dsTicketClose.Tables[0].Rows.Count > 0)
                    {
                        objListCustomerCloseTicketDetail = dsTicketClose.Tables[0].AsEnumerable().Select(s => new CustomerCloseTicketDetail
                        {
                            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(s.Field<DateTime?>("creation_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            LastModifiedBy = s.Field<string>("last_modified_by"),
                            LastModifiedTime = Convert.ToDateTime(s.Field<DateTime?>("last_modified_time")).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            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<Boolean?>("isdeclined"),
                            EstimatedTimeForJobCompletion = s.Field<int?>("estimated_time_for_job_completion"),
                            TotalTicketLifecycleTimeSla = s.Field<int?>("total_ticket_lifecycle_time_sla"),
                            EstimatedTimeForJobCompletionSubmitTime = Convert.ToDateTime(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();
                    }

                    #endregion

                    objListSync.Add(new SyncModel
                    {
                        TicketOpenList = objListCustomerOpenTicketDetail,
                        // TicketActivityOpenList = objListOpenTicketActivityTicket,
                        TicketCloseList = objListCustomerCloseTicketDetail,
                        // TicketActivityCloseList = objListCloseTicketActivityTicket,
                        DeviceAlias = model.DeviceAlias,
                        // DbSynLastTime = string.Format("{yyyy-MM-dd hh:mm:ss}", DateTime.UtcNow),
                        DbSynLastTime = DateTime.UtcNow.ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                        Status = "1"

                    });
                    return objListSync;
                }
                else
                {
                    objListSync.Add(new SyncModel
                    {
                        DeviceAlias = model.DeviceAlias,
                        DbSynLastTime = model.DbSynLastTime,
                        Status = "0",
                        Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString()
                    });

                    return objListSync;
                }
            }
            catch (Exception Ex)
            {
                objListSync.Add(new SyncModel
                {
                    DeviceAlias = model.DeviceAlias,
                    DbSynLastTime = model.DbSynLastTime,
                    Status = "0",
                    Message = Ex.Message
                });
                objLog.ErrorLogFile("GetSyncTicketDetail_Kam", Ex.Message, path, errorlogtf);
                return objListSync;
            }
        }


        // by priya


        public TicketStatusModel GetSyncTicketDetail(string ticketid, string strTicketStatus)
        {
            TicketStatusModel objModel = new TicketStatusModel();
            objModel.Status = "1";
            DataSet ds = new DataSet();
            NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];
            nSqlParam[0] = new NpgsqlParameter("inticket_id", ticketid);
            if (strTicketStatus.ToLower() == "open")
            {
                ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_open_ticket_details_mobile"], nSqlParam);
            }
            else
            {
                ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_close_ticket_details_mobile"], nSqlParam);
            }
            if (ds.Tables[0].Rows.Count > 0)
            {

                objModel.IsTripStart = Convert.ToBoolean(ds.Tables[0].Rows[0]["is_trip_start"].ToString())
                    ;
                objModel.TicketStatus = Convert.ToInt32(ds.Tables[0].Rows[0]["_activity_ticket_status"].ToString());
                objModel.TicketIdAlias = ds.Tables[0].Rows[0]["_ticket_id_alias"].ToString();

            }
            objModel.Status = "1";

            return objModel;
        }

        public List<DTCCodeMaster> GetAllDtcDescriptionListDataSet(string[] dtccodes)
        {
            List<DTCCodeMaster> objList = new List<DTCCodeMaster>();
            //DashboardModel objModel = new DashboardModel();
            try
            {
                objAuthorization = new AuthenticationRepository();

                DataSet ds = new DataSet();
                NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];
                nSqlParam[0] = new NpgsqlParameter("indtc_code ", dtccodes);
                ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_dtcdescription_bydtccode"], nSqlParam);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    objList = ds.Tables[0].AsEnumerable().Select(s => new DTCCodeMaster
                    {
                        //   objNewDashboardModel = s.Field<Boolean?>("_customer_vehicle_is_owner"),
                        //DealerName = model.TicketNo,
                        DTCCode = s.Field<string>("_dtc_code"),
                        // Region = s.Field<string>("_region"),
                        // TicketCount = s.Field<string>("_ticket_count"),
                        Description = s.Field<string>("_description"),

                    }).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 string getChassisNumberByRegnum(string RegNum)
        {
            string strchassisNumber = "";
            try
            {
                objAuthorization = new AuthenticationRepository();

                DataSet ds = new DataSet();
                NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];
                nSqlParam[0] = new NpgsqlParameter("inRegNum", RegNum);
                ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_chassisNumber_by_REGNumber"], nSqlParam);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    strchassisNumber = Convert.ToString(ds.Tables[0].Rows[0]["_chassis_number"]);
                }
                //}
                return strchassisNumber;
            }
            catch (Exception Ex)
            {
                //  objNewDashboardModel = new CustomerVehicleModel();
                // objNewDashboardModel.Status = 0;
                // objCustomerVehicleModel.Message = ConfigurationManager.AppSettings["PwdErrorMsg"].ToString() + Ex.Message;
                objLog.ErrorLogFile("GetChassisNumber", Ex.Message, path, errorlogtf);
                return strchassisNumber;
            }
        }

        public string getChassisNumber(string ticketId)
        {
            string strchassisNumber = "";
            string STRstatus = "";
            try
            {
                objAuthorization = new AuthenticationRepository();

                DataSet ds = new DataSet();
                NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];
                nSqlParam[0] = new NpgsqlParameter("inticketId", ticketId);
                ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_chassisNumber_by_ticketid"], nSqlParam);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    strchassisNumber = Convert.ToString(ds.Tables[0].Rows[0]["_chassis_number"]);
                    STRstatus = Convert.ToString(ds.Tables[0].Rows[0]["_vehicle_status"]);
                }

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

        public string insertTechnicianNumber(string ticketId, string tech_number)
        {
            string strmessage = "";
            try
            {

                NpgsqlParameter[] nSqlParam = new NpgsqlParameter[2];
                nSqlParam[0] = new NpgsqlParameter("inticket_id", ticketId);
                nSqlParam[1] = new NpgsqlParameter("intech_number", tech_number);
                DataSet ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_insert_technician_detail"], nSqlParam);
                strmessage = "Mobile Number successfully saved";

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

            }
            return strmessage;
        }
        public CustomerOpenTicketDetail getTicketDetailLatestUpdate(SyncModel model)
        {
            string dealerid = "";
            CustomerOpenTicketDetail objModel = new CustomerOpenTicketDetail();
            try
            {
                DataSet dsOpen = new DataSet();
                DataSet dsActivityOpen = new DataSet();

                DataSet dsTicketClose = new DataSet();
                DataSet dsActivityClose = new DataSet();
                /*    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[3];
                    nSqlParam[0] = new NpgsqlParameter("indevice_alias", model.DeviceAlias);
                    nSqlParam[1] = new NpgsqlParameter("inieminumber", model.Ieminumber);
                    nSqlParam[2] = new NpgsqlParameter("db_syn_last_time", model.DbSynLastTime);*/
                NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];
                nSqlParam[0] = new NpgsqlParameter("inticketId", model.ticketId);
                dsOpen = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_ticket_open_latest_activity_by_id"], nSqlParam);
                //  dsTicketClose = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_syn_android_db_ticket_closed"], nSqlParam);
                if (dsOpen.Tables[0].Rows.Count > 0)
                {
                    //assigned_to_user_id = dsOpen.Tables[0].Rows[0]["_assigned_to_user_id"].ToString();  // getting ticket id alias
                    objModel.TicketId = dsOpen.Tables[0].Rows[0]["ticket_id"].ToString();
                    objModel.Description = dsOpen.Tables[0].Rows[0]["description"].ToString();
                    objModel.AssignedTo = dsOpen.Tables[0].Rows[0]["assigned_to"].ToString();
                    objModel.ReportedVia = dsOpen.Tables[0].Rows[0]["reported_via"].ToString();
                    objModel.TicketStatus = Convert.ToInt32(dsOpen.Tables[0].Rows[0]["ticket_status"]);
                    objModel.Priority = Convert.ToInt32(dsOpen.Tables[0].Rows[0]["priority"]);
                    objModel.CustomerScore = dsOpen.Tables[0].Rows[0]["customer_score"].ToString();
                    objModel.CreatedBy = dsOpen.Tables[0].Rows[0]["created_by"].ToString();
                    objModel.CreationTime = Convert.ToDateTime(dsOpen.Tables[0].Rows[0]["creation_time"]).ToString("dd MMM yyyy HH:mm:ss");
                    objModel.LastModifiedBy = dsOpen.Tables[0].Rows[0]["last_modified_by"].ToString();
                    objModel.LastModifiedTime = Convert.ToDateTime(dsOpen.Tables[0].Rows[0]["last_modified_time"]).ToString("dd MMM yyyy HH:mm:ss");
                    objModel.BreakdownLocation = dsOpen.Tables[0].Rows[0]["breakdown_location"].ToString();
                    objModel.BreakdownLongitude = dsOpen.Tables[0].Rows[0]["breakdown_longitude"].ToString();
                    objModel.BreakdownLattitude = dsOpen.Tables[0].Rows[0]["breakdown_lattitude"].ToString();
                    objModel.AssignedToUserId = dsOpen.Tables[0].Rows[0]["assigned_to_user_id"].ToString();
                    objModel.AssignedToUserLattitude = dsOpen.Tables[0].Rows[0]["assigned_to_user_lattitude"].ToString();
                    objModel.AssignedToUserLongitude = dsOpen.Tables[0].Rows[0]["assigned_to_user_longitude"].ToString();
                    objModel.Isdeclined = Convert.ToBoolean(dsOpen.Tables[0].Rows[0]["isdeclined"]);
                    objModel.EstimatedTimeForJobCompletion = dsOpen.Tables[0].Rows[0]["estimated_time_for_job_completion"] == DBNull.Value ? 0 : Convert.ToInt32(dsOpen.Tables[0].Rows[0]["estimated_time_for_job_completion"].ToString());
                  //  dsOpen.Tables[0].Rows[0]["estimated_time_for_job_completion"]);
                    objModel.TotalTicketLifecycleTimeSla = dsOpen.Tables[0].Rows[0]["total_ticket_lifecycle_time_sla"] == DBNull.Value ? 0 : Convert.ToInt32(dsOpen.Tables[0].Rows[0]["total_ticket_lifecycle_time_sla"].ToString());

                    //Convert.ToInt32(dsOpen.Tables[0].Rows[0]["total_ticket_lifecycle_time_sla"]);
                    objModel.EstimatedTimeForJobCompletionSubmitTime =
 dsOpen.Tables[0].Rows[0]["estimated_time_for_job_completion_submit_time"] == DBNull.Value ? string.Empty : Convert.ToDateTime(dsOpen.Tables[0].Rows[0]["estimated_time_for_job_completion_submit_time"]).ToString("dd MMM yyyy HH:mm:ss");

                    //Convert.ToDateTime(dsOpen.Tables[0].Rows[0]["estimated_time_for_job_completion_submit_time"]).ToString("dd MMM yyyy HH:mm:ss");

                    objModel.VehicleRegisterNumber = dsOpen.Tables[0].Rows[0]["vehicle_register_number"].ToString();
                    objModel.BreakdownLocationLandmark = dsOpen.Tables[0].Rows[0]["breakdown_location_landmark"].ToString();
                    objModel.RouteId = Convert.ToInt32(dsOpen.Tables[0].Rows[0]["route_id"]);
                    objModel.CustomerContactNo = dsOpen.Tables[0].Rows[0]["customer_contact_no"].ToString();
                    objModel.RepairCost = dsOpen.Tables[0].Rows[0]["repair_cost"].ToString();
                    objModel.DefaultSlaTime = Convert.ToInt32(dsOpen.Tables[0].Rows[0]["default_sla_time"]);
                    objModel.SlaMissedReason = dsOpen.Tables[0].Rows[0]["sla_missed_reason"].ToString();
                    objModel.SuggestionComment = dsOpen.Tables[0].Rows[0]["suggestion_comment"].ToString();
                    objModel.JobCompleteResponseTime = dsOpen.Tables[0].Rows[0]["default_col_1"] == DBNull.Value ? "" : dsOpen.Tables[0].Rows[0]["default_col_1"].ToString();
                    //dsOpen.Tables[0].Rows[0]["default_col_1"].ToString();
                    objModel.DefaultCol2 = dsOpen.Tables[0].Rows[0]["default_col_2"] == DBNull.Value ? "" : dsOpen.Tables[0].Rows[0]["default_col_2"].ToString();
                    //dsOpen.Tables[0].Rows[0]["default_col_2"].ToString();
                    objModel.DefaultCol3 = dsOpen.Tables[0].Rows[0]["default_col_3"] == DBNull.Value ? "" : dsOpen.Tables[0].Rows[0]["default_col_3"].ToString();
                    //dsOpen.Tables[0].Rows[0]["default_col_3"].ToString();
                    objModel.EstimatedDistance = dsOpen.Tables[0].Rows[0]["estimated_distance"].ToString();
                    objModel.Owner_Contact_no = dsOpen.Tables[0].Rows[0]["_owner_contact_no"].ToString();
                    objModel.TicketIdAlias = dsOpen.Tables[0].Rows[0]["_ticket_id_alias"].ToString();
                    objModel.IsTripEnd = Convert.ToBoolean(dsOpen.Tables[0].Rows[0]["_trip_end"]);
                    objModel.ChassisNumber = dsOpen.Tables[0].Rows[0]["_chassis_number"].ToString();
                    objModel.OdometerReading = Convert.ToDouble(dsOpen.Tables[0].Rows[0]["_odometer_reading"]);
                    objModel.VehicleType = dsOpen.Tables[0].Rows[0]["_vehicle_type"].ToString();
                    objModel.PreviousOdometerReading = Convert.ToDouble(dsOpen.Tables[0].Rows[0]["_previous_odometer_reading"].ToString());
                    //--------added on 31-12-2020 start------------------
                    objModel.VehicleWarranty = dsOpen.Tables[0].Rows[0]["_vehicle_warranty"] == DBNull.Value ? "" : dsOpen.Tables[0].Rows[0]["_vehicle_warranty"].ToString();
                    objModel.VehicleAMC = dsOpen.Tables[0].Rows[0]["_vehicle_amc"] == DBNull.Value ? "" : dsOpen.Tables[0].Rows[0]["_vehicle_amc"].ToString();
                    objModel.VehicleSalesDate = dsOpen.Tables[0].Rows[0]["_vehicle_sales_date"].ToString();
                    objModel.TicketSource = dsOpen.Tables[0].Rows[0]["_toll_free_no_source"] == DBNull.Value ? "" : dsOpen.Tables[0].Rows[0]["_toll_free_no_source"].ToString();
                    objModel.OwnerName = dsOpen.Tables[0].Rows[0]["_owner_name"] == DBNull.Value ? "" : dsOpen.Tables[0].Rows[0]["_owner_name"].ToString();
                    objModel.CustomerId = dsOpen.Tables[0].Rows[0]["_customer_id"] == DBNull.Value ? "" : dsOpen.Tables[0].Rows[0]["_customer_id"].ToString();
                    objModel.CustomerType = dsOpen.Tables[0].Rows[0]["_customer_type"] == DBNull.Value ? "" : dsOpen.Tables[0].Rows[0]["_customer_type"].ToString();
                    // priya  15 oct 2022
                    objModel.IsTripStart = Convert.ToBoolean(dsOpen.Tables[0].Rows[0]["istripstart"].ToString());
                    objModel.IsKam = Convert.ToBoolean(dsOpen.Tables[0].Rows[0]["_is_kam"].ToString());
                    objModel.Product_Variant = dsOpen.Tables[0].Rows[0]["_product_variant"].ToString();
                    objModel.Vehicle_Emission_Norms = dsOpen.Tables[0].Rows[0]["_vehicle_emission_norms"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {

                // objModel. = Ex.Message;
                objLog.ErrorLogFile("getTicketDetailLiveTabTracking", Ex.Message, path, errorlogtf);
                return objModel;
            }
        }


        #endregion
    }

    #endregion
}