namespace VECV_WebApi.Models.Customer
{
    #region Namespaces

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

    #endregion

    #region Repository Class

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

        #endregion

        #region API Methods

        /// <summary>
        /// To Insert new customer 
        /// </summary>
        /// <param name="model">customer ,owner and vehicle details</param>
        /// <returns>contain status 0 or 1 if there is an error then return 0 other wise 1</returns>
        public CustomerModel InsertNewCustomerDetail(NewCustomerModel model)
        {
            CustomerModel objCustomer = new CustomerModel();
            try
            {
                objCustomer.Status = "1";
                DataSet ds = new DataSet();
                VehicleRepository objVehicleRepository = new VehicleRepository(_connStr);
                VehicleModel objVehicleModel = new VehicleModel();
                CustomerModel ownerModel = new CustomerModel();
                objAuthorization = new AuthenticationRepository();

                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    model.VehicleModel.Token = model.Token;
                    model.CustomerModel.Token = model.Token;
                    model.OwnerModel.Token = model.Token;
                    model.VehicleModel.KamUser = model.CustomerModel.KamUser;
                    model.OwnerModel.CustomerId = model.CustomerModel.CustomerId;
                    model.OwnerModel.CustomerType = model.CustomerModel.CustomerType;

                    objVehicleModel = objVehicleRepository.GetVehicleDetail(model.VehicleModel);
                    ownerModel = GetOwnerDetail(model.OwnerModel);

                    if (objVehicleModel.Id == null)
                    {
                        objVehicleModel = objVehicleRepository.InsertVehicleDetail(model.VehicleModel);
                    }
                    if ((ownerModel.VehicleNumberPlate != null) && (objVehicleModel.VehicleNumberPlate != model.VehicleModel.VehicleNumberPlate))
                    {
                        objVehicleModel = objVehicleRepository.UpdateVehicleDetail(model.VehicleModel);
                    }

                    if (ownerModel.CustomerId == null)
                    {
                        model.OwnerModel.VehicleRegistrationNumber = model.VehicleModel.RegistrationNo;
                        model.OwnerModel.InIsOwner = true;
                        InsertCustomer(model.OwnerModel);
                    }
                    else
                    {
                        model.CustomerModel.OwnerId = ownerModel.CustomerId;
                    }

                    if (model.CustomerModel.CustomerMobileNumber1 != null && model.CustomerModel.CustomerMobileNumber1 != "")
                    {
                        model.CustomerModel.VehicleRegistrationNumber = model.VehicleModel.RegistrationNo;
                        model.CustomerModel.InIsOwner = false;
                        objCustomer = InsertCustomer(model.CustomerModel);
                    }
                }
                else
                {
                    objCustomer.Status = "0";
                    objCustomer.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objCustomer;
            }
            catch (Exception Ex)
            {
                objCustomer.Status = "0";
                objCustomer.Message = Ex.Message;
                objLog.ErrorLogFile("InsertNewCustomerDetail", Ex.Message, path, errorlogtf);

                return objCustomer;
            }
        }

        /// <summary>
        /// To Insert new customer 
        /// </summary>
        /// <param name="model">customer ,owner and vehicle details</param>
        /// <returns>contain status 0 or 1 if there is an error then return 0 other wise 1</returns>
        public CustomerModel UpdateCustomerDetail(NewCustomerModel model)
        {
            CustomerModel objCustomer = new CustomerModel();
            try
            {
                objCustomer.Status = "1";
                DataSet ds = new DataSet();
                //VehicleRepository objVehicleRepository = new VehicleRepository(_connStr);
                //VehicleModel objVehicleModel = new VehicleModel();
                //CustomerModel ownerModel = new CustomerModel();
                objAuthorization = new AuthenticationRepository();

                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[8];   //modify the parameter count on 01-03-2021
                    nSqlParam[0] = new NpgsqlParameter("incustomer_id", model.CustomerModel.CustomerId);
                    nSqlParam[1] = new NpgsqlParameter("inid", model.CustomerModel.Id);
                    nSqlParam[2] = new NpgsqlParameter("incustomer_type", model.CustomerModel.CustomerType);
                    nSqlParam[3] = new NpgsqlParameter("incustomer_mobile", model.CustomerModel.CustomerMobileNumber1);
                    nSqlParam[4] = new NpgsqlParameter("incustomer_chassis", model.CustomerModel.VehicleRegistrationNumber); 
                    nSqlParam[5] = new NpgsqlParameter("incustomer_registrationNumber", model.CustomerModel.RegistrationNumber);
                    //----------- added on 01-03-2021 start ---------------------------
                    nSqlParam[6] = new NpgsqlParameter("incustomer_vehicleModelNumber", model.CustomerModel.VehicleModelNumber);
                    nSqlParam[7] = new NpgsqlParameter("incustomer_vehicleType", model.CustomerModel.VehicleType);
                    //----------- added on 01-03-2021 start ---------------------------

                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_update_customer_detail"], nSqlParam);
                    objCustomer.Status = "1";
                }
                return objCustomer;
            }
            catch (Exception Ex)
            {
                objCustomer.Status = "0";
                objCustomer.Message = Ex.Message;
                objLog.ErrorLogFile("InsertNewCustomerDetail", Ex.Message, path, errorlogtf);

                return objCustomer;
            }
        }

        /// <summary>
        /// To get owner detail.  
        /// </summary>
        /// <param name="model">Owner id detail</param>
        /// <returns>Owner details</returns>
        public CustomerModel GetOwnerDetail(CustomerModel model)
        {
            CustomerModel objModel = new CustomerModel();
            try
            {
                DataSet ds = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[3];
                    nSqlParam[0] = new NpgsqlParameter("inid", model.CustomerId);
                    nSqlParam[1] = new NpgsqlParameter("inregistration_number", model.VehicleRegistrationNumber);
                    nSqlParam[2] = new NpgsqlParameter("inmobile_number", model.CustomerMobileNumber1);

                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_vehicle_owner_detail"], nSqlParam);
                    model.Status = "1";
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        if (ds.Tables[0].Rows[0]["_customer_id"].ToString().Trim() != null)
                        {
                            objModel.CustomerId = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_id")).FirstOrDefault();
                            objModel.CustomerCustomerName = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_customer_name")).FirstOrDefault();
                            objModel.CustomerAlias = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_alias")).FirstOrDefault();
                            objModel.CustomerMobileNumber1 = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_mobile_number_1")).FirstOrDefault();
                            objModel.CustomerMobileNumber2 = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_mobile_number_2")).FirstOrDefault();
                            objModel.CustomerMobileNumber3 = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_mobile_number_3")).FirstOrDefault();
                            objModel.CustomerAddress = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_address")).FirstOrDefault();
                            objModel.CustomerEmailId = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_email_id")).FirstOrDefault();
                            objModel.CustomerCity = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_city")).FirstOrDefault();
                            objModel.CustomerState = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_state")).FirstOrDefault();
                            objModel.CustomerVehicleCustomerId = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_vehicle_customer_id")).FirstOrDefault();
                            objModel.CustomerVehicleVehicleId = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_vehicle_vehicle_id")).FirstOrDefault();
                            objModel.CustomerVehicleIsOwner = ds.Tables[0].AsEnumerable().Select(s => s.Field<Boolean?>("_customer_vehicle_is_owner")).FirstOrDefault();
                            objModel.VehicleId = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_vehicle_id")).FirstOrDefault();
                            objModel.VehicleRegistrationNumber = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_vehicle_registration_number")).FirstOrDefault();
                            objModel.VehicleNumberPlate = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("vehicle_number_plate")).FirstOrDefault();
                            objModel.VehicleModelNumber = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_vehicle_model_number")).FirstOrDefault();
                            objModel.CustomerVehicleModelId = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_vehicle_model_id")).FirstOrDefault();
                            objModel.CustomerVehicleModelName = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_vehicle_model_name")).FirstOrDefault();
                            objModel.CustomerVehicleModelTagging = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_vehicle_model_tagging")).FirstOrDefault();
                            objModel.VehicleType = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_vehicle_vehicle_type")).FirstOrDefault();
                            objModel.IsKamUser = ds.Tables[0].AsEnumerable().Select(s => s.Field<bool>("_is_kam")).FirstOrDefault();
                            objModel.LicenseKey = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_application_license_key")).FirstOrDefault();
                            objModel.CustomerType = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_type")).FirstOrDefault();  //added on 15-01-2021
                            objModel.Id = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("_customer_id_dbm")).FirstOrDefault();  //added on 15-01-2021

                           

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

        /// <summary>
        /// To insert customer detail.  
        /// </summary>
        /// <param name="model">customer details</param>
        /// <returns>status</returns>
        public CustomerModel InsertCustomer(CustomerModel model)
        {
            CustomerModel objModel = new CustomerModel();
            try
            {
                objModel.Status = "1";
                DataSet ds = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[15];
                    nSqlParam[0] = new NpgsqlParameter("inregistration_number", model.VehicleRegistrationNumber);
                    nSqlParam[1] = new NpgsqlParameter("incustomer_name", model.CustomerCustomerName);
                    nSqlParam[2] = new NpgsqlParameter("inalias", model.CustomerAlias);
                    nSqlParam[3] = new NpgsqlParameter("inmobile_number_1", model.CustomerMobileNumber1);
                    nSqlParam[4] = new NpgsqlParameter("inmobile_number_2", model.CustomerMobileNumber2);
                    nSqlParam[5] = new NpgsqlParameter("inmobile_number_3", model.CustomerMobileNumber3);
                    nSqlParam[6] = new NpgsqlParameter("inaddress", model.CustomerAddress);
                    nSqlParam[7] = new NpgsqlParameter("inemail_id ", model.CustomerEmailId);
                    nSqlParam[8] = new NpgsqlParameter("incity", model.CustomerCity);
                    nSqlParam[9] = new NpgsqlParameter("instate", model.CustomerState);
                    nSqlParam[10] = new NpgsqlParameter("inis_owner", model.InIsOwner);
                    nSqlParam[11] = new NpgsqlParameter("inowner_id", model.OwnerId);
                    nSqlParam[12] = new NpgsqlParameter("ininfo_modified_or_not", model.thirdPartyInfoupdatedornot);
                    nSqlParam[13] = new NpgsqlParameter("incustomer_id", model.CustomerId);
                    nSqlParam[14] = new NpgsqlParameter("incustomer_type", model.CustomerType);

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

                    // For Telematics Use
                    // NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_sync_customer_details"]);
                    // NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_sync_customer_vehicle_details"]);

                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        objModel.CustomerId = ds.Tables[0].AsEnumerable().Select(s => s.Field<string>("sp_insert_customer_or_owner_for_exist_vehicle")).FirstOrDefault();
                    }
                }
                else
                {
                    model.Status = "0";
                    model.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                model.Status = "0";
                model.Message = Ex.Message;
                objLog.ErrorLogFile("InsertCustomer", Ex.Message, path, errorlogtf);
                return objModel;
            }
        }

        /// <summary>
        /// To get owner or customer complete detail
        /// </summary>
        /// <param name="model">Customer/Owner Id information</param>
        /// <returns>status and data</returns>
        public List<CustomerModel> GetOwnerCompleteDetails(CustomerModel model)
        {
            CustomerModel objModel = new CustomerModel();
            List<CustomerModel> objList = new List<CustomerModel>();

            try
            {
                objModel.Status = "1";
                DataSet ds = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    if (model.CustomerId == null)
                    {
                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[4];
                        nSqlParam[0] = new NpgsqlParameter("instate", model.CustomerState);
                        nSqlParam[1] = new NpgsqlParameter("invalue", model.Value);
                        nSqlParam[2] = new NpgsqlParameter("inlimit", model.Limit);
                        nSqlParam[3] = new NpgsqlParameter("inoffset", model.OffeSet);
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_vehicle_owner_list"], nSqlParam);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            objList = ds.Tables[0].AsEnumerable().Select(s => new CustomerModel
                            {
                                CustomerId = s.Field<string>("customer_id"),
                                CustomerCustomerName = s.Field<string>("customer_customer_name"),
                                CustomerMobileNumber1 = s.Field<string>("customer_mobile_number_1"),
                                CustomerCity = s.Field<string>("customer_city"),
                                CustomerState = s.Field<string>("customer_state"),
                                VehicleRegistrationNumber = s.Field<string>("vehicle_registration_number"),
                                IsKamUser = s.Field<bool>("_is_kam"),
                                LicenseKey = s.Field<string>("_application_license_key"),
                            }).ToList();
                        }
                    }
                    else
                    {
                        NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];
                        nSqlParam[0] = new NpgsqlParameter("inid", model.CustomerId);
                        ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_vehicle_owner_list"], nSqlParam);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            objList = ds.Tables[0].AsEnumerable().Select(s => new CustomerModel
                            {
                                CustomerId = s.Field<string>("customer_id"),
                                CustomerCustomerName = s.Field<string>("customer_customer_name"),
                                CustomerAlias = s.Field<string>("customer_alias"),
                                CustomerMobileNumber1 = s.Field<string>("customer_mobile_number_1"),
                                CustomerMobileNumber2 = s.Field<string>("customer_mobile_number_2"),
                                CustomerMobileNumber3 = s.Field<string>("customer_mobile_number_3"),
                                CustomerAddress = s.Field<string>("customer_address"),
                                CustomerEmailId = s.Field<string>("customer_email_id"),
                                CustomerCity = s.Field<string>("customer_city"),
                                CustomerState = s.Field<string>("customer_state"),
                                //CustomerVehicleCustomerId = s.Field<string>("_customer_vehicle_customer_id"),
                                // CustomerVehicleVehicleId = s.Field<string>("_customer_vehicle_vehicle_id"),
                                CustomerVehicleIsOwner = s.Field<Boolean?>("customer_vehicle_is_owner"),
                                VehicleId = s.Field<string>("vehicle_id"),
                                VehicleRegistrationNumber = s.Field<string>("vehicle_registration_number"),
                                VehicleNumberPlate = s.Field<string>("_vehicle_number_plate"),
                                VehicleModelNumber = s.Field<string>("vehicle_model_number"),
                                CustomerVehicleModelId = s.Field<string>("customer_vehicle_model_id"),
                                CustomerVehicleModelName = s.Field<string>("customer_vehicle_model_name"),
                                CustomerVehicleModelTagging = s.Field<string>("customer_vehicle_model_tagging"),
                                VehicleType = s.Field<string>("vehicle_vehicle_type"),
                                VehicleInstallationDate = Convert.ToDateTime(s.Field<DateTime?>("_installation_date")).AddMinutes(Convert.ToInt16(model.UtcMinute)).ToString(ConfigurationManager.AppSettings["DateFormat"]),
                                IsKamUser = s.Field<bool>("_is_kam"),
                                LicenseKey = s.Field<string>("_application_license_key"),
                            }).ToList();
                        }
                    }
                }
                else
                {
                    model.Status = "0";
                    model.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                    objList.Add(model);
                }
                return objList;
            }
            catch (Exception Ex)
            {
                model.Status = "0";
                model.Message = Ex.Message;
                objLog.ErrorLogFile("GetOwnerCompleteDetails", Ex.Message, path, errorlogtf);
                objList.Add(model);
                return objList;

            }
        }


        /// <summary>
        /// To get owner or customer complete detail
        /// </summary>
        /// <param name="model">Customer/Owner Id information</param>
        /// <returns>status and data</returns>
        public List<VehicleRegistrationInfo> GetVehicleRegNoListByCustomerId(CustomerModel model)
        {
            List<VehicleRegistrationInfo> oListVehicleRegNos = new List<VehicleRegistrationInfo>();
            try
            {
                DataSet ds = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {

                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[1];
                    nSqlParam[0] = new NpgsqlParameter("instate", model.CustomerId);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_kam_vehicle_list"], nSqlParam);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        oListVehicleRegNos = ds.Tables[0].AsEnumerable().Select(s => new VehicleRegistrationInfo
                        {
                            VehicleId = s.Field<string>("vehicle_id"),
                            VehicleRegNo = s.Field<string>("_registration_number")
                        }).ToList();
                    }
                }
                return oListVehicleRegNos;
            }
            catch (Exception Ex)
            {
                objLog.ErrorLogFile("GetVehicleRegNoListByCustomerId", Ex.Message, path, errorlogtf);
                throw Ex;
            }
        }




        /// <summary>
        /// To check customer or vehicle exist or not
        /// </summary>
        /// <param name="model">customer iformation</param>
        /// <returns>status and data</returns>
        public CustomerModel CheckOwnerDetailExist(CustomerModel model)
        {
            CustomerModel objModel = new CustomerModel();
            try
            {
                objModel.Status = "1";
                DataSet ds = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[2];
                    nSqlParam[0] = new NpgsqlParameter("invalue", model.CustomerId);
                    nSqlParam[1] = new NpgsqlParameter("invalue", model.Type);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_check_vehicle_reg_and_chasis_no_and_owner_mobno_is_exists"], nSqlParam);
                    objModel.Type = ds.Tables[0].Rows[0][0].ToString();
                }
                else
                {
                    objModel.Status = "0";
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                objModel.Status = "0";
                objModel.Message = Ex.Message;
                objLog.ErrorLogFile("GetOwnerCompleteDetails", Ex.Message, path, errorlogtf);
                return objModel;
            }

        }

        /// <summary>
        /// To insert new owner with multiple customer or vehicle
        /// </summary>
        /// <param name="model">customer/vehicle details with owner info</param>
        /// <returns>Status 1 if record inserted else 0 </returns>
        public CustomerModel InsertOwnerCustomerVehicle(NewCustomerModel model)
        {
            CustomerModel objModel = new CustomerModel();
            try
            {
                objModel.Status = "1";
                DataSet ds = new DataSet();
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    #region Insert or update owner

                    NpgsqlParameter[] nSqlParam = new NpgsqlParameter[21];
                    nSqlParam[0] = new NpgsqlParameter("incust_id", model.OwnerModel.CustomerId);
                    nSqlParam[1] = new NpgsqlParameter("incustomer_name", model.OwnerModel.CustomerCustomerName);
                    nSqlParam[2] = new NpgsqlParameter("inalias", model.OwnerModel.CustomerAlias);
                    nSqlParam[3] = new NpgsqlParameter("inmobile_number_1", model.OwnerModel.CustomerMobileNumber1);
                    nSqlParam[4] = new NpgsqlParameter("inmobile_number_2", model.OwnerModel.CustomerMobileNumber2);
                    nSqlParam[5] = new NpgsqlParameter("inmobile_number_3", model.OwnerModel.CustomerMobileNumber3);
                    nSqlParam[6] = new NpgsqlParameter("inaddress", model.OwnerModel.CustomerAddress);
                    nSqlParam[7] = new NpgsqlParameter("inemail_id ", model.OwnerModel.CustomerEmailId);
                    nSqlParam[8] = new NpgsqlParameter("incity", model.OwnerModel.CustomerCity);
                    nSqlParam[9] = new NpgsqlParameter("instate", model.OwnerModel.CustomerState);
                    nSqlParam[10] = new NpgsqlParameter("inis_owner", model.OwnerModel.InIsOwner);
                    nSqlParam[11] = new NpgsqlParameter("inisdeleted", model.OwnerModel.IsDeleted);
                    nSqlParam[12] = new NpgsqlParameter("invehicle_id", model.OwnerModel.VehicleId);
                    nSqlParam[13] = new NpgsqlParameter("inregistration_number", model.OwnerModel.VehicleRegistrationNumber);
                    nSqlParam[14] = new NpgsqlParameter("invehicle_number_plate", model.OwnerModel.VehicleNumberPlate);
                    nSqlParam[15] = new NpgsqlParameter("inmodel_number", model.OwnerModel.VehicleModelNumber);
                    nSqlParam[16] = new NpgsqlParameter("invehicle_type", model.OwnerModel.VehicleType);
                    nSqlParam[17] = new NpgsqlParameter("inowner_id", model.OwnerModel.OwnerId);
                    nSqlParam[18] = new NpgsqlParameter("ininstallation_date", model.OwnerModel.InstallationDate != null ? Convert.ToDateTime(model.OwnerModel.InstallationDate).AddMinutes(-model.OwnerModel.UtcMinute) : model.OwnerModel.InstallationDate);
                    nSqlParam[19] = new NpgsqlParameter("invehicle_is_deleted", model.OwnerModel.IsDealetedVehicle);
                    nSqlParam[20] = new NpgsqlParameter("inis_kam", model.OwnerModel.IsKamUser);
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_insert_or_update_customer_or_owner_or_vehicle"], nSqlParam);


                    // For Telematics Use
                    //  NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_sync_customer_details"]);
                    //  NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_sync_vehicle_details"]);
                    //  NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_sync_customer_vehicle_details"]);

                    #endregion

                    if (model.OwnerModel.CustomerId == null)
                    {
                        model.OwnerModel.CustomerId = ds.Tables[0].Rows[0][0].ToString();
                    }
                    objModel.CustomerId = model.OwnerModel.CustomerId;

                    #region Insert Customer
                    if (model.CustomerList != null)
                    {
                        for (int i = 0; i < model.CustomerList.Count; i++)
                        {
                            nSqlParam = new NpgsqlParameter[21];
                            nSqlParam[0] = new NpgsqlParameter("incust_id", model.CustomerList[i].CustomerId);
                            nSqlParam[1] = new NpgsqlParameter("incustomer_name", model.CustomerList[i].CustomerCustomerName);
                            nSqlParam[2] = new NpgsqlParameter("inalias", model.CustomerList[i].CustomerAlias);
                            nSqlParam[3] = new NpgsqlParameter("inmobile_number_1", model.CustomerList[i].CustomerMobileNumber1);
                            nSqlParam[4] = new NpgsqlParameter("inmobile_number_2", model.CustomerList[i].CustomerMobileNumber2);
                            nSqlParam[5] = new NpgsqlParameter("inmobile_number_3", model.CustomerList[i].CustomerMobileNumber3);
                            nSqlParam[6] = new NpgsqlParameter("inaddress", model.CustomerList[i].CustomerAddress);
                            nSqlParam[7] = new NpgsqlParameter("inemail_id ", model.CustomerList[i].CustomerEmailId);
                            nSqlParam[8] = new NpgsqlParameter("incity", model.CustomerList[i].CustomerCity);
                            nSqlParam[9] = new NpgsqlParameter("instate", model.CustomerList[i].CustomerState);
                            nSqlParam[10] = new NpgsqlParameter("inis_owner", model.CustomerList[i].InIsOwner);
                            nSqlParam[11] = new NpgsqlParameter("inisdeleted", model.CustomerList[i].IsDeleted);
                            nSqlParam[12] = new NpgsqlParameter("invehicle_id", model.CustomerList[i].VehicleId);
                            nSqlParam[13] = new NpgsqlParameter("inregistration_number", model.CustomerList[i].VehicleRegistrationNumber);
                            nSqlParam[14] = new NpgsqlParameter("invehicle_number_plate", model.CustomerList[i].VehicleNumberPlate);
                            nSqlParam[15] = new NpgsqlParameter("inmodel_number", model.CustomerList[i].VehicleModelNumber);
                            nSqlParam[16] = new NpgsqlParameter("invehicle_type", model.CustomerList[i].VehicleType);
                            nSqlParam[17] = new NpgsqlParameter("inowner_id", model.OwnerModel.CustomerId);
                            nSqlParam[18] = new NpgsqlParameter("ininstallation_date", model.CustomerList[i].InstallationDate != null ? Convert.ToDateTime(model.CustomerList[i].InstallationDate).AddMinutes(-model.OwnerModel.UtcMinute) : model.CustomerList[i].InstallationDate);
                            nSqlParam[19] = new NpgsqlParameter("invehicle_is_deleted", model.CustomerList[i].IsDealetedVehicle);
                            nSqlParam[20] = new NpgsqlParameter("inis_kam", model.CustomerList[i].IsKamUser);

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

                            // For Telematics Use
                            //  NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_sync_customer_details"]);
                            //  NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_sync_vehicle_details"]);
                            //  NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_sync_customer_vehicle_details"]);
                        }
                    }
                    #endregion
                }
                else
                {
                    model.Status = "0";
                    model.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objModel;
            }
            catch (Exception Ex)
            {
                model.Status = "0";
                model.Message = Ex.Message;
                objLog.ErrorLogFile("InsertOwnerCustomerVehicle", Ex.Message, path, errorlogtf);
                return objModel;
            }
        }



        /// <summary>
        /// To get all State list
        /// </summary>
        /// <param name="model">state info</param>
        /// <returns>all State list</returns>
        public List<GetDummyTicket> GetAllDummyTicketsByMobileList(GetDummyTicket model)
        {
            List<GetDummyTicket> objList = new List<GetDummyTicket>();
            GetDummyTicket objModel = new GetDummyTicket();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_dummy_tickets_by_mobile"]);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        objList = ds.Tables[0].AsEnumerable().Select(s => new GetDummyTicket
                        {
                            DummyTicketId = s.Field<int>("_dummy_id"),
                            Description = s.Field<string>("_description"),
                            ReportedVia = s.Field<string>("_reported_via"),
                            BreakdownLocation = s.Field<string>("_breakdown_location"),
                            VehicleRegistrationNumber = s.Field<string>("_vehicle_registration_number"),
                            ChassisNo = s.Field<string>("_chassis_number"),
                            CustomerContactNo = s.Field<string>("_customer_contact_no"),
                            Comments = s.Field<string>("customer_comments"),
                            KamUser=s.Field<bool?>("_kam_user"),
                            DriverName = s.Field<string>("_DriverName"),
                            CreationTime = Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).AddMinutes(Convert.ToInt16(model.UtcMinute)).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            //CreationTime = Convert.ToString(s.Field<DateTime>("_creation_time")),     
                            TollFreeeNoSource = s.Field<string>("_toll_free_no_source")

                        }).ToList();
                    }
                }
                else
                {
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                    objList.Add(objModel);
                }

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

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

        public List<CancelledDraftTicketModel> GetAllCancelledDummyTicketsByMobileList(CancelledDraftTicketModel model)
        {
            List<CancelledDraftTicketModel> objList = new List<CancelledDraftTicketModel>();
            CancelledDraftTicketModel objModel = new CancelledDraftTicketModel();
            try
            {
                objAuthorization = new AuthenticationRepository();
                if (objAuthorization.AuthenticateDevice(model.Token))
                {
                    DataSet ds = new DataSet();
                    ds = NpgSqlHelper.ExecuteDataset(_connStr, CommandType.StoredProcedure, ConfigurationManager.AppSettings["usp_get_cancelled_dummy_tickets_by_mobile"]);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        objList = ds.Tables[0].AsEnumerable().Select(s => new CancelledDraftTicketModel
                        {
                            DraftTicketId = s.Field<int>("_dummy_id"),
                            VehicleRegistrationNumber = s.Field<string>("_vehicle_registration_number"),
                            ChassisNumber = s.Field<string>("_chassis_number"),
                            Comments = s.Field<string>("_customer_comments"),
                            ContactNumber = s.Field<string>("_contact_number"),
                            DriverName = s.Field<string>("_driver_name"),
                            KamUser = s.Field<bool?>("_kam_user"),
                            CreationTime = Convert.ToDateTime(s.Field<DateTime?>("_creation_time")).AddMinutes(Convert.ToInt16(model.UtcMinute)).ToString(ConfigurationManager.AppSettings["DateTimeFormat"]),
                            TollFreeNoSource = s.Field<string>("_toll_free_no_source"),
                            TicketCancelReason = s.Field<string>("_ticket_cancel_reason"),
                            TicketCancelRemark = s.Field<string>("_ticket_cancel_remark")

                        }).ToList();
                    }
                }
                else
                {
                    objModel.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                    objList.Add(objModel);
                }

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


        public CustomerModel InsertNewCustomerDetailDBMByPass(string Token,string REgno,string VehicleNumberPlate,string mobileno,string modelnumber,string KamUser,string VehicleType,string customername)
        {
            CustomerModel objCustomer = new CustomerModel();
            try
            {
                objCustomer.Status = "1";
                DataSet ds = new DataSet();
                VehicleRepository objVehicleRepository = new VehicleRepository(_connStr);
                VehicleModel objVehicleModel = new VehicleModel();
                CustomerModel ownerModel = new CustomerModel();
                objAuthorization = new AuthenticationRepository();

                if (objAuthorization.AuthenticateDevice(Token))
                {
                    VehicleModel objVehicleModelCust = new VehicleModel();
                    objVehicleModelCust.Token = Token;
                    objVehicleModelCust.RegistrationNo = REgno;
                    objVehicleModelCust.VehicleNumberPlate = REgno;
                    objVehicleModelCust.ModelNumber = modelnumber;
                    objVehicleModelCust.VehicleType = VehicleType;
                    objVehicleModelCust.KamUser = KamUser;
                    objVehicleModelCust.Id = "";
                    objVehicleModel = objVehicleRepository.GetVehicleDetail(objVehicleModelCust);
                    CustomerModel objownerModel = new CustomerModel();
                    ownerModel.CustomerId = "";
                    ownerModel.Token = Token;
                    ownerModel.VehicleRegistrationNumber= REgno;
                    ownerModel.CustomerMobileNumber1 = mobileno;
                    ownerModel.CustomerType = KamUser;
                    ownerModel.CustomerCustomerName = customername;
                    ownerModel.CustomerAlias = customername;
       
                    objownerModel = GetOwnerDetail(objownerModel);
            
                    if (objVehicleModel.Id == null)
                    {
                        objVehicleModelCust.Token = Token;
                        objVehicleModel = objVehicleRepository.InsertVehicleDetail(objVehicleModelCust);
                    }
                    if ((ownerModel.VehicleNumberPlate != null) && (objVehicleModel.VehicleNumberPlate != VehicleNumberPlate))
                    {
                        objVehicleModelCust.Token = Token;
                        objVehicleModel = objVehicleRepository.UpdateVehicleDetail(objVehicleModelCust);
                    }

                    if (ownerModel.CustomerId == null)
                    {
                        ownerModel.Token = Token;
                        ownerModel.VehicleRegistrationNumber = REgno;
                        ownerModel.InIsOwner = true;
                        InsertCustomer(ownerModel);
                    }
                    else
                    {
                        objownerModel.OwnerId = ownerModel.CustomerId;
                    }

                    if (mobileno != null && mobileno != "")
                    {
                        objownerModel.VehicleRegistrationNumber = REgno;
                        objownerModel.InIsOwner = false;
                        objCustomer = InsertCustomer(ownerModel);
                    }
                }
                else
                {
                    objCustomer.Status = "0";
                    objCustomer.Message = ConfigurationManager.AppSettings["DeviceConfigurationTokenMessage"].ToString();
                }
                return objCustomer;
            }
            catch (Exception Ex)
            {
                objCustomer.Status = "0";
                objCustomer.Message = Ex.Message;
                objLog.ErrorLogFile("InsertNewCustomerDetail", Ex.Message, path, errorlogtf);

                return objCustomer;
            }
        }
        //------------------ added on 09-03-2021 end -----------------------------------------


        #endregion
    }

    #endregion
}