using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Mail;
                        
using System.Security.Cryptography;
using System.Text;
using Newtonsoft.Json;

namespace Hero
{
    
    public class Service1 : IService1
    {
        
        public string EmployeeDetails(string employeeCode, DateTime dateTimeClient, string token)
        {
            if (employeeCode == null || employeeCode == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                string msg = "", str_employee_name = "", str_email = "", str_phone = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                    if (arr_token[0].Equals(employeeCode))
                   
                    {

                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("EmployeeDetails", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Code", employeeCode);
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    str_employee_name = reader.GetString(1);
                                    str_email = reader.GetString(2);
                                    str_phone = reader.GetString(3).ToString();
                                }
                                msg = str_employee_name.Trim() + "," + str_email.Trim() + "," + str_phone.Trim();
                            }
                            else
                            {
                                msg = "fail";
                            }
                            reader.Close();
                            sqlConnection.Close();
                        }
                    }
                    else
                    {
                        msg = "fail";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }

        public string ValidateEmployee(string employeeCode, string employee_otp, DateTime dateTimeClient, string token)
        {
            if (employeeCode == null || employeeCode == "" || employee_otp == null || employee_otp == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                int result = 0;
                try
                {
                    try
                    {
                        str_token = Decrypt(token).Trim();
                        arr_token = str_token.Split(',');
                    }
                    catch (Exception ex) { return "fail"; }
                    if (arr_token[0].Equals(employeeCode) && arr_token[1].Equals(employee_otp))
                    {
                        DateTime dateTime = DateTime.Now;
                        int ard_id = 0;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("EmpId", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Code", employeeCode);
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    ard_id = reader.GetInt32(0);
                                }
                                reader.Close();
                                sqlCommand = new SqlCommand("VerifyEmployeeOtp", sqlConnection);
                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                sqlCommand.Parameters.AddWithValue("@Id", ard_id);
                                string str_password = "";
                                using (SqlDataReader reader2 = sqlCommand.ExecuteReader())
                                {
                                    if (reader2.HasRows)
                                    {
                                        while (reader2.Read())
                                        {
                                            str_password = reader2.GetString(0);
                                        }

                                        reader2.Close();
                                        if (employee_otp.Trim().Equals(str_password.Trim()))
                                        {
                                            sqlCommand = new SqlCommand("EmployeeOtpConsumed", sqlConnection);
                                            sqlCommand.CommandType = CommandType.StoredProcedure;
                                            sqlCommand.Parameters.AddWithValue("@Id", ard_id);
                                            result = sqlCommand.ExecuteNonQuery();
                                            if (result > 0)
                                            {
                                                msg = "success";
                                            }
                                            else
                                            {
                                                msg = "fail";
                                            }
                                        }
                                        else
                                        {
                                            msg = "fail";
                                        }
                                    }
                                    else
                                    {
                                        msg = "fail";
                                        reader2.Close();
                                    }
                                    sqlConnection.Close();
                                }
                            }
                            else
                            {
                                msg = "fail";
                                reader.Close();
                            }
                        }
                    }
                    else
                    {
                        msg = "fail";
                    }
                }
                catch (Exception ex) { msg = ex.ToString(); }

                return msg;
            }

        }


        public string ValidateEmployeeOtp(string employeeCode, string employee_otp, DateTime dateTimeClient, string token)
        {
            if (employeeCode == null || employeeCode == "" || employee_otp == null || employee_otp == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                int result = 0;
                try
                {
                    try
                    {
                        str_token = Decrypt(token).Trim();
                        arr_token = str_token.Split(',');
                    }
                    catch (Exception ex) { return "fail"; }
                    if (arr_token[0].Equals(employeeCode) && arr_token[1].Equals(employee_otp))
                    {
                        DateTime dateTime = DateTime.Now;
                        int ard_id = 0;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("EmpId", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Code", employeeCode);
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    ard_id = reader.GetInt32(0);
                                }
                                reader.Close();
                                sqlCommand = new SqlCommand("VerifyEmployeeOtp", sqlConnection);
                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                sqlCommand.Parameters.AddWithValue("@Id", ard_id);
                                string str_password = "";
                                using (SqlDataReader reader2 = sqlCommand.ExecuteReader())
                                {
                                    if (reader2.HasRows)
                                    {
                                        while (reader2.Read())
                                        {
                                            str_password = reader2.GetString(0);
                                        }

                                        reader2.Close();
                                        if (employee_otp.Trim().Equals(str_password.Trim()))
                                        {
                                            sqlCommand = new SqlCommand("EmployeeOtpInactive", sqlConnection);
                                            sqlCommand.CommandType = CommandType.StoredProcedure;
                                            sqlCommand.Parameters.AddWithValue("@Id", ard_id);
                                            result = sqlCommand.ExecuteNonQuery();
                                            if (result > 0)
                                            {
                                                msg = "success";
                                            }
                                            else
                                            {
                                                msg = "fail";
                                            }
                                        }
                                        else
                                        {
                                            msg = "fail";
                                        }
                                    }
                                    else
                                    {
                                        msg = "fail";
                                        reader2.Close();
                                    }
                                    sqlConnection.Close();
                                }
                            }
                            else
                            {
                                msg = "fail";
                                reader.Close();
                            }
                        }
                    }
                    else
                    {
                        msg = "fail";
                    }
                }
                catch (Exception ex) { msg = ex.ToString(); }

                return msg;
            }

        }

        public string GetEmployeeOtp(string employeeCode, DateTime dateTimeClient, string token)
        {
            if (employeeCode == null || employeeCode == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                int i_is_locked = 0, i_otp_count = 0;
                DateTime dateTimeLock = DateTime.Now;
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(employeeCode))
                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("EmployeeDetails", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Code", employeeCode);
                        string str_password = "", str_email = "", str_phone = "", str_url = "", str_text = "";
                        int result = 0, ardId = 0;
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    ardId = reader.GetInt32(0);
                                    str_email = reader.GetString(2);
                                    str_phone = reader.GetString(3);
                                }
                                reader.Close();
                                sqlCommand = new SqlCommand("EmployeeOtpLock", sqlConnection);
                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                sqlCommand.Parameters.AddWithValue("@Id", ardId);
                                using (SqlDataReader reader4 = sqlCommand.ExecuteReader())
                                {
                                    if (reader4.HasRows)
                                    {
                                        while (reader4.Read())
                                        {
                                            i_is_locked = reader4.GetInt32(0);
                                            dateTimeLock = reader4.GetDateTime(1);
                                        }
                                        reader4.Close();
                                        dateTime = DateTime.Now;
                                        TimeSpan span = dateTime.Subtract(dateTimeLock);
                                        double minutes = (int)Math.Round(span.TotalMinutes);
                                        if (i_is_locked == 1 && minutes < double.Parse(System.Configuration.ConfigurationManager.AppSettings["str_lock_timeOut_in_min"].ToString()))
                                        {
                                            return "otp_lock," + (double.Parse(System.Configuration.ConfigurationManager.AppSettings["str_lock_timeOut_in_min"].ToString())-minutes).ToString();
                                        }
                                        else
                                        {
                                            span = dateTime.Subtract(dateTimeClient);
                                            TimeSpan t1 = new TimeSpan(0, Int32.Parse(System.Configuration.ConfigurationManager.AppSettings["str_lock_time_in_min"].ToString()), 0);
                                            DateTime d2 = dateTimeClient.Subtract(t1);
                                            sqlCommand = new SqlCommand("EmployeeOtpCount", sqlConnection);
                                            sqlCommand.CommandType = CommandType.StoredProcedure;
                                            sqlCommand.Parameters.AddWithValue("@Id", ardId);
                                            sqlCommand.Parameters.AddWithValue("@dateTime", d2);
                                            using (SqlDataReader reader5 = sqlCommand.ExecuteReader())
                                            {
                                                if (reader5.HasRows)
                                                {
                                                    while (reader5.Read())
                                                    {
                                                        i_otp_count = reader5.GetInt32(0);
                                                    }
                                                    reader5.Close();
                                                    if (i_otp_count >= 5)
                                                    {
                                                        sqlCommand = new SqlCommand("UpdateEmployeeOtpLock", sqlConnection);
                                                        sqlCommand.CommandType = CommandType.StoredProcedure;
                                                        sqlCommand.Parameters.AddWithValue("@Id", ardId);
                                                        result = sqlCommand.ExecuteNonQuery();
                                                        sqlConnection.Close();
                                                        return "otp_lock";
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                                str_password = RandomString(6).Trim();
                                str_text = "The One Time Password(OTP) for your VCI installer is {" + str_password.Trim() +"}";
                                Console.WriteLine(str_text + "\n");
                                try
                                {
                                    sendmail(str_email, str_password);
                                }
                                catch (Exception ex)
                                {
                                  
                                }
                                str_url = "https://haapi.myvfirst.com/psms/servlet/psms.Eservice2?data=%3C?xml%20version=%221.0%22%20encoding=%22ISO-8859-1%22?%3E%3C!DOCTYPE%20MESSAGE%20SYSTEM%20%22http://127.0.0.1/psms/dtd/messagev12.dtd%22%20%3E%3CMESSAGE%20VER=%221.2%22%3E%3CUSER%20USERNAME=%22heromtxmlint%22%20PASSWORD=%22jdfbts(4%22/%3E%3CSMS%20UDH=%220%22%20CODING=%221%22%20TEXT=%22" + str_text +"%22%20PROPERTY=%220%22%20ID=%222%22%3E%3CADDRESS%20FROM=%22HMCLTD%22%20TO=%22"+ str_phone + "%22%20SEQ=%222%22%20TAG=%22some%20clientside%20random%20data%22%20/%3E%3C/SMS%3E%3C/MESSAGE%3E&action=send";

                                try
                                {
                                    //string str_response = Get(str_url);
                                    string str_response = GetOtpMobile(str_phone, str_password);
                                    if (!str_response.Contains("OTP has been sent to your mobile number"))
                                    {
                                        return "Authorization failed";
                                    }
                                }
                                catch (Exception ex) { return "Failed to process password"; }
                                sqlCommand = new SqlCommand("EmployeeId", sqlConnection);
                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                sqlCommand.Parameters.AddWithValue("@Id", ardId);
                                using (SqlDataReader reader2 = sqlCommand.ExecuteReader())
                                {
                                    if (reader2.HasRows)
                                    {
                                        reader2.Close();
                                        sqlCommand = new SqlCommand("UpdateEmployeeOtp", sqlConnection);
                                        sqlCommand.CommandType = CommandType.StoredProcedure;
                                        sqlCommand.Parameters.AddWithValue("@Otp", str_password);
                                        sqlCommand.Parameters.AddWithValue("@Id", ardId);
                                        result = sqlCommand.ExecuteNonQuery();
                                        if (result > 0)
                                        {
                                            msg = "success";
                                        }
                                        else
                                        {
                                            msg = "fail";
                                        }
                                        sqlConnection.Close();
                                    }
                                    else
                                    {
                                        reader2.Close();
                                        sqlCommand = new SqlCommand("InsertEmployeeOtp", sqlConnection);
                                        sqlCommand.CommandType = CommandType.StoredProcedure;
                                        sqlCommand.Parameters.AddWithValue("@Otp", str_password);
                                        sqlCommand.Parameters.AddWithValue("@Id", ardId);
                                        result = sqlCommand.ExecuteNonQuery();
                                        if (result > 0)
                                        {
                                            msg = "success";
                                        }
                                        else
                                        {
                                            msg = "fail";
                                        }
                                        sqlConnection.Close();
                                    }
                                }
                            }
                            else
                            {
                                msg = "fail";
                                reader.Close();
                            }

                        }
                    }
                    else
                    {
                        msg = "fail";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }

        public string ForgotEmployeeOtp(string employeeCode, DateTime dateTimeClient, string token)
        {
            if (employeeCode == null || employeeCode == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                }
                catch (Exception ex) { return "fail_token"; }
                if (arr_token[0].Equals(employeeCode))
                {
                    try
                    {
                        DateTime dateTime = DateTime.Now;
                        string str_password = "", str_email = "", str_phone = "", str_url = "", str_text = "";
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("EmployeeDetails", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Code", employeeCode);
                        int result = 0, ardId = 0;
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    ardId = reader.GetInt32(0);
                                    str_email = reader.GetString(2);
                                    str_phone = reader.GetString(3);
                                }
                                reader.Close();
                                sqlCommand = new SqlCommand("VerifyEmployeeOtp", sqlConnection);
                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                sqlCommand.Parameters.AddWithValue("@Id", ardId);
                                using (SqlDataReader reader2 = sqlCommand.ExecuteReader())
                                {

                                    if (reader2.HasRows)
                                    {
                                        while (reader2.Read())
                                        {
                                            str_password = reader2.GetString(0);
                                        }
                                        reader2.Close();
                                        sqlConnection.Close();
                                        str_text = "The One Time Password(OTP) for your VCI installer is {" + str_password.Trim() +"}";
                                        str_url = "https://haapi.myvfirst.com/psms/servlet/psms.Eservice2?data=%3C?xml%20version=%221.0%22%20encoding=%22ISO-8859-1%22?%3E%3C!DOCTYPE%20MESSAGE%20SYSTEM%20%22http://127.0.0.1/psms/dtd/messagev12.dtd%22%20%3E%3CMESSAGE%20VER=%221.2%22%3E%3CUSER%20USERNAME=%22heromtxmlint%22%20PASSWORD=%22jdfbts(4%22/%3E%3CSMS%20UDH=%220%22%20CODING=%221%22%20TEXT=%22" + str_text + "%22%20PROPERTY=%220%22%20ID=%222%22%3E%3CADDRESS%20FROM=%22HMCLTD%22%20TO=%22" + str_phone + "%22%20SEQ=%222%22%20TAG=%22some%20clientside%20random%20data%22%20/%3E%3C/SMS%3E%3C/MESSAGE%3E&action=send";

                                        try
                                        {
                                            string str_response = GetOtpMobile(str_phone, str_password);
                                            if (!str_response.Contains("OTP has been sent to your mobile number"))
                                            {
                                                return "Authorization failed";
                                            }
                                            else
                                            {
                                                msg = "success";
                                            }
                                        }
                                        catch (Exception ex) { return "Failed to process password"; }

                                    }
                                    else
                                    {
                                        reader2.Close();
                                        sqlConnection.Close();
                                        msg = "Failed to process password";

                                    }
                                }
                            }
                            else
                            {
                                reader.Close();
                                sqlConnection.Close();
                                msg = "fail_no_data";
                            }
                        }
                        

                    }
                    catch (Exception ex) { return ex.ToString(); }
                }
                else
                {
                    return "Authorization failed";
                }
                return msg;
            }

        }

        public string DealerDetails(string dealerCode, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (dealerCode == null || dealerCode == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_dealer_name = "", str_email = "", str_phone = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {

                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("DealerDetails", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Code", dealerCode);
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    str_dealer_name = reader.GetString(1);
                                    str_email = reader.GetString(2);
                                    str_phone = reader.GetString(3);
                                }
                                msg = str_dealer_name.Trim() + "," + str_email.Trim() + "," + str_phone.Trim();
                            }
                            else
                            {
                                msg = "fail";
                            }
                            reader.Close();
                            sqlConnection.Close();
                        }
                    }
                    else
                    {
                        msg = "fail_licence";
                    }
                }
                catch (Exception ex) { return "fail_ex"; }
                return msg;
            }

        }

        public string UpdateToolData(string dealerCode, string systemId, string deviceId, string fwid, string Dataid, string Picid, string UserType,DateTime dateTimeClient, string token)
        {
            if (dealerCode == null || dealerCode == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || fwid == null || fwid == "" || Dataid == null || Dataid == "" || Picid == null || Picid == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                    int result = 0;
                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("InserttoolUpdate", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@DealerID", dealerCode);
                        sqlCommand.Parameters.AddWithValue("@Type", UserType);
                        sqlCommand.Parameters.AddWithValue("@fwver", fwid);
                        sqlCommand.Parameters.AddWithValue("@dataver", Dataid);
                        sqlCommand.Parameters.AddWithValue("@picver", Picid);
                        result = sqlCommand.ExecuteNonQuery();
                        sqlConnection.Close();
                        if (result > 0)
                        {
                            msg = "success";
                        }
                        else
                        {
                            msg = "fail";
                        }

                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }

        public string UpdateSoftwareData(string dealerCode, string systemId, string deviceId, string Ver, string UserType, DateTime dateTimeClient, string token)
        {
            if (dealerCode == null || dealerCode == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || Ver == null || Ver == "" || dateTimeClient == null || token == null || token == "" || UserType == null || UserType == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                    int result = 0;
                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("InsertSftUpdate", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@DealerID", dealerCode);
                        sqlCommand.Parameters.AddWithValue("@ver", Ver);
                        sqlCommand.Parameters.AddWithValue("@Type", UserType);
                        sqlCommand.Parameters.AddWithValue("@SystemId", systemId);
                        sqlCommand.Parameters.AddWithValue("@MotherBoard", deviceId);
                        result = sqlCommand.ExecuteNonQuery();
                        sqlConnection.Close();
                        if (result > 0)
                        {
                            msg = "success";
                        }
                        else
                        {
                            msg = "fail";
                        }

                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }

        public string ValidateDealer(string dealerCode, string dealer_otp, string ipAddress, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (dealerCode == null || dealerCode == "" || dealer_otp == null || dealer_otp == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        DateTime dateTime = DateTime.Now;
                        String ard_id = "";
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("ArdId", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Code", dealerCode);
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    ard_id = reader.GetString(0);
                                }
                                reader.Close();
                                sqlCommand = new SqlCommand("VerifyOtp", sqlConnection);
                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ard_id));
                                sqlCommand.Parameters.AddWithValue("@sId", systemId);
                                sqlCommand.Parameters.AddWithValue("@dId", deviceId);
                                string str_code = "", str_password = "";
                                int result = 0;
                                using (SqlDataReader reader2 = sqlCommand.ExecuteReader())
                                {
                                    if (reader2.HasRows)
                                    {
                                        while (reader2.Read())
                                        {
                                            str_password = reader2.GetString(0);
                                        }

                                        reader2.Close();
                                        if (dealer_otp.Trim().Equals(str_password.Trim()))
                                        {
                                            sqlCommand = new SqlCommand("Ledger", sqlConnection);
                                            sqlCommand.CommandType = CommandType.StoredProcedure;
                                            sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ard_id));
                                            sqlCommand.Parameters.AddWithValue("@Ip", ipAddress);
                                            sqlCommand.Parameters.AddWithValue("@Sid", systemId);
                                            result = sqlCommand.ExecuteNonQuery();
                                            if (result > 0)
                                            {
                                                msg = "success";
                                            }
                                            else
                                            {
                                                msg = "fail";
                                            }
                                        }
                                        else
                                        {
                                            msg = "fail";
                                        }
                                    }
                                    else
                                    {
                                        msg = "fail";
                                        reader2.Close();
                                    }
                                    sqlConnection.Close();
                                }
                            }
                            else
                            {
                                msg = "fail";
                                reader.Close();
                            }
                        }
                    }
                    else
                    {
                        msg = "fail";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }

        public string ValidateDealerOtp(string dealerCode, string dealer_otp, string ipAddress, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (dealerCode == null || dealerCode == "" || dealer_otp == null || dealer_otp == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        DateTime dateTime = DateTime.Now;
                        string ard_id = "";
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("ArdId", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Code", dealerCode);
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    ard_id = reader.GetString(0);
                                }
                                reader.Close();
                                sqlCommand = new SqlCommand("VerifyOtp", sqlConnection);
                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                sqlCommand.Parameters.AddWithValue("@Id",RemoveSpecialChars(ard_id));
                                sqlCommand.Parameters.AddWithValue("@sId", systemId);
                                sqlCommand.Parameters.AddWithValue("@dId", deviceId);
                                string str_code = "", str_password = "";
                                int result = 0;
                                using (SqlDataReader reader2 = sqlCommand.ExecuteReader())
                                {
                                    if (reader2.HasRows)
                                    {
                                        while (reader2.Read())
                                        {
                                            str_password = reader2.GetString(0);
                                        }

                                        reader2.Close();
                                        if (dealer_otp.Trim().Equals(str_password.Trim()))
                                        {
                                            sqlCommand = new SqlCommand("LedgerAndInactive", sqlConnection);
                                            sqlCommand.CommandType = CommandType.StoredProcedure;
                                            sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ard_id));
                                            sqlCommand.Parameters.AddWithValue("@Ip", ipAddress);
                                            sqlCommand.Parameters.AddWithValue("@Sid", systemId);
                                            result = sqlCommand.ExecuteNonQuery();
                                            if (result > 0)
                                            {
                                                msg = "success";
                                            }
                                            else
                                            {
                                                msg = "fail";
                                            }
                                        }
                                        else
                                        {
                                            msg = "fail";
                                        }
                                    }
                                    else
                                    {
                                        msg = "fail";
                                        reader2.Close();
                                    }
                                    sqlConnection.Close();
                                }
                            }
                            else
                            {
                                msg = "fail";
                                reader.Close();
                            }
                        }
                    }
                    else
                    {
                        msg = "fail";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }
        public string RemoveSpecialChars(string str)
        {

            string[] chars = new string[] { ",", ".", "/", "!", "@", "#", "$", "%", "^", "&", "*", "'", "\"", ";", "_", "(", ")", ":", "|", "[", "]" };

            for (int i = 0; i < chars.Length; i++)
            {
                if (str.Contains(chars[i]))
                {
                    str = str.Replace(chars[i], "");
                }
            }
            return str;
        }
        public string GetOtpAws(string dealerCode, string systemId, string deviceId, DateTime dateTimeClient, string token,string deltype)
        {
            if (dealerCode == null || dealerCode == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "" || deltype ==null || deltype=="")
            {
                return "fail";
            }
            else
            {
                string msg = "", str_token = "";
                int i_is_locked = 0,i_otp_count=0;
                DateTime dateTimeLock= DateTime.Now;
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("DealerDetails", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Code", dealerCode);
                        string str_password = "", str_email = "", str_phone = "", str_url = "", str_text = "",ardId="";
                        int result = 0;
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    ardId = reader.GetString(0);
                                    str_email = reader.GetString(2);
                                    str_phone = reader.GetString(3);
                                }
                                reader.Close();
                                sqlCommand = new SqlCommand("DealerOtpLock", sqlConnection);
                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ardId));
                                sqlCommand.Parameters.AddWithValue("@sId", systemId);
                                sqlCommand.Parameters.AddWithValue("@dId", deviceId);
                                using (SqlDataReader reader4 = sqlCommand.ExecuteReader())
                                {
                                    if (reader4.HasRows)
                                    {
                                        while (reader4.Read())
                                        {
                                            i_is_locked = reader4.GetInt32(0);
                                            dateTimeLock = reader4.GetDateTime(1);
                                        }
                                        reader4.Close();
                                        dateTime = DateTime.Now;
                                        TimeSpan span = dateTime.Subtract(dateTimeLock);
                                        double minutes = (int)Math.Round(span.TotalMinutes);
                                        if (i_is_locked == 1 && minutes < double.Parse(System.Configuration.ConfigurationManager.AppSettings["str_lock_timeOut_in_min"].ToString()))
                                        {
                                            return "otp_lock,"+(double.Parse(System.Configuration.ConfigurationManager.AppSettings["str_lock_timeOut_in_min"].ToString())-minutes).ToString();
                                        }
                                        else
                                        {
                                            span = dateTime.Subtract(dateTimeClient);
                                            TimeSpan t1 = new TimeSpan(0, Int32.Parse(System.Configuration.ConfigurationManager.AppSettings["str_lock_time_in_min"].ToString()), 0);
                                            DateTime d2 = dateTimeClient.Subtract(t1);
                                            sqlCommand = new SqlCommand("DealerOtpCount", sqlConnection);
                                            sqlCommand.CommandType = CommandType.StoredProcedure;
                                            sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ardId));
                                            sqlCommand.Parameters.AddWithValue("@sId", systemId);
                                            sqlCommand.Parameters.AddWithValue("@dId", deviceId);
                                            sqlCommand.Parameters.AddWithValue("@dateTime", d2);
                                            using (SqlDataReader reader5 = sqlCommand.ExecuteReader())
                                            {
                                                if (reader5.HasRows)
                                                {
                                                    while (reader5.Read())
                                                    {
                                                        i_otp_count= reader5.GetInt32(0);
                                                    }
                                                    reader5.Close();
                                                    if(i_otp_count>=5)
                                                    {
                                                        
                                                        sqlCommand = new SqlCommand("UpdateDealerOtpLock", sqlConnection);
                                                        sqlCommand.CommandType = CommandType.StoredProcedure;
                                                        sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ardId));
                                                        sqlCommand.Parameters.AddWithValue("@Sid", systemId);
                                                        sqlCommand.Parameters.AddWithValue("@Did", deviceId);
                                                        result = sqlCommand.ExecuteNonQuery();
                                                        sqlConnection.Close();
                                                        return "otp_lock";
                                                    }
                                                }
                                            }
                                                }
                                    }
                                }
                                str_password = RandomString(6).Trim();
                                str_text = "The One Time Password(OTP) for your VCI installer is {" + str_password.Trim() +"}";

                                Console.WriteLine(str_text + "\n");
                                try
                                {
                                    sendmail(str_email, str_password);
                                }
                                catch (Exception ex)
                                {
                                
                                }
                                str_url = "https://haapi.myvfirst.com/psms/servlet/psms.Eservice2?data=%3C?xml%20version=%221.0%22%20encoding=%22ISO-8859-1%22?%3E%3C!DOCTYPE%20MESSAGE%20SYSTEM%20%22http://127.0.0.1/psms/dtd/messagev12.dtd%22%20%3E%3CMESSAGE%20VER=%221.2%22%3E%3CUSER%20USERNAME=%22heromtxmlint%22%20PASSWORD=%22jdfbts(4%22/%3E%3CSMS%20UDH=%220%22%20CODING=%221%22%20TEXT=%22" + str_text +"%22%20PROPERTY=%220%22%20ID=%222%22%3E%3CADDRESS%20FROM=%22HMCLTD%22%20TO=%22"+ str_phone + "%22%20SEQ=%222%22%20TAG=%22some%20clientside%20random%20data%22%20/%3E%3C/SMS%3E%3C/MESSAGE%3E&action=send";
                                  
                                try
                                {
                                    string str_response = GetOtpMobile(str_phone, str_password);
                                    //if (!str_response.Contains("OTP has been sent to your mobile number"))
                                    //{
                                    //    return "Authorization failed";
                                    //}
                                    //else
                                    //{
                                    //    msg = "success";
                                    //}
                                }
                                catch (Exception ex) { return "Failed to process password"; }
                                sqlCommand = new SqlCommand("DealerId", sqlConnection);
                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ardId));
                                sqlCommand.Parameters.AddWithValue("@sId", systemId);
                                sqlCommand.Parameters.AddWithValue("@dId", deviceId);
                                using (SqlDataReader reader2 = sqlCommand.ExecuteReader())
                                {
                                    if (reader2.HasRows)
                                    {
                                        reader2.Close();
                                        
                                        sqlCommand = new SqlCommand("UpdateOtP", sqlConnection);
                                        sqlCommand.CommandType = CommandType.StoredProcedure;
                                        sqlCommand.Parameters.AddWithValue("@Otp", str_password);
                                        sqlCommand.Parameters.AddWithValue("@Sid", systemId);
                                        sqlCommand.Parameters.AddWithValue("@Did", deviceId);
                                        sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ardId));
                                        sqlCommand.Parameters.AddWithValue("@del_type", deltype);
                                        result = sqlCommand.ExecuteNonQuery();
                                        sqlConnection.Close();
                                        if (result > 0)
                                        {
                                            msg = "success";
                                        }
                                        else
                                        {
                                            msg = "fail";
                                        }
                                        
                                    }
                                    else
                                    {
                                        reader2.Close();
                                        sqlCommand = new SqlCommand("InsertDealDatawithAws", sqlConnection);
                                        sqlCommand.CommandType = CommandType.StoredProcedure;
                                        sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ardId));
                                        sqlCommand.Parameters.AddWithValue("@Sid", systemId);
                                        sqlCommand.Parameters.AddWithValue("@Did", deviceId);
                                        sqlCommand.Parameters.AddWithValue("@Otp", str_password);
                                        sqlCommand.Parameters.AddWithValue("@IsActive", 1);
                                        sqlCommand.Parameters.AddWithValue("@del_type", deltype);
                                        
                                        result = sqlCommand.ExecuteNonQuery();
                                        sqlConnection.Close();
                                        if (result > 0)
                                        {
                                            msg = "success";
                                        }
                                        else
                                        {
                                            msg = "fail";
                                        }
                                       
                                    }
                                }
                            }
                            else
                            {
                                msg = "fail";
                                reader.Close();
                            }

                        }
                    }
                    else
                    {
                        msg = "fail";
                    }
                }
                catch (Exception ex) { return ex.ToString(); }
                return msg;
            }

        }
        public string GetOtp(string dealerCode, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (dealerCode == null || dealerCode == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                string msg = "", str_token = "";
                int i_is_locked = 0, i_otp_count = 0;
                DateTime dateTimeLock = DateTime.Now;
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("DealerDetails", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Code", dealerCode);
                        string str_password = "", str_email = "", str_phone = "", str_url = "", str_text = "", ardId = "";
                        int result = 0;
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    ardId = reader.GetString(0);
                                    str_email = reader.GetString(2);
                                    str_phone = reader.GetString(3);
                                }
                                reader.Close();
                                sqlCommand = new SqlCommand("DealerOtpLock", sqlConnection);
                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ardId));
                                sqlCommand.Parameters.AddWithValue("@sId", systemId);
                                sqlCommand.Parameters.AddWithValue("@dId", deviceId);
                                using (SqlDataReader reader4 = sqlCommand.ExecuteReader())
                                {
                                    if (reader4.HasRows)
                                    {
                                        while (reader4.Read())
                                        {
                                            i_is_locked = reader4.GetInt32(0);
                                            dateTimeLock = reader4.GetDateTime(1);
                                        }
                                        reader4.Close();
                                        dateTime = DateTime.Now;
                                        TimeSpan span = dateTime.Subtract(dateTimeLock);
                                        double minutes = (int)Math.Round(span.TotalMinutes);
                                        if (i_is_locked == 1 && minutes < double.Parse(System.Configuration.ConfigurationManager.AppSettings["str_lock_timeOut_in_min"].ToString()))
                                        {
                                            return "otp_lock," + (double.Parse(System.Configuration.ConfigurationManager.AppSettings["str_lock_timeOut_in_min"].ToString()) - minutes).ToString();
                                        }
                                        else
                                        {
                                            span = dateTime.Subtract(dateTimeClient);
                                            TimeSpan t1 = new TimeSpan(0, Int32.Parse(System.Configuration.ConfigurationManager.AppSettings["str_lock_time_in_min"].ToString()), 0);
                                            DateTime d2 = dateTimeClient.Subtract(t1);
                                            sqlCommand = new SqlCommand("DealerOtpCount", sqlConnection);
                                            sqlCommand.CommandType = CommandType.StoredProcedure;
                                            sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ardId));
                                            sqlCommand.Parameters.AddWithValue("@sId", systemId);
                                            sqlCommand.Parameters.AddWithValue("@dId", deviceId);
                                            sqlCommand.Parameters.AddWithValue("@dateTime", d2);
                                            using (SqlDataReader reader5 = sqlCommand.ExecuteReader())
                                            {
                                                if (reader5.HasRows)
                                                {
                                                    while (reader5.Read())
                                                    {
                                                        i_otp_count = reader5.GetInt32(0);
                                                    }
                                                    reader5.Close();
                                                    if (i_otp_count >= 5)
                                                    {
                                                        
                                                        sqlCommand = new SqlCommand("UpdateDealerOtpLock", sqlConnection);
                                                        sqlCommand.CommandType = CommandType.StoredProcedure;
                                                        sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ardId));
                                                        sqlCommand.Parameters.AddWithValue("@Sid", systemId);
                                                        sqlCommand.Parameters.AddWithValue("@Did", deviceId);
                                                        result = sqlCommand.ExecuteNonQuery();
                                                        sqlConnection.Close();
                                                        return "otp_lock";
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                                
                                str_password = RandomString(6).Trim();
                                
                                str_text = "The One Time Password(OTP) for your VCI installer is {" + str_password.Trim() + "}";

                                Console.WriteLine(str_text + "\n");
                                try
                                {
                                    sendmail(str_email, str_password);
                                }
                                catch (Exception ex)
                                {
                                    
                                }

                                str_url = "https://haapi.myvfirst.com/psms/servlet/psms.Eservice2?data=%3C?xml%20version=%221.0%22%20encoding=%22ISO-8859-1%22?%3E%3C!DOCTYPE%20MESSAGE%20SYSTEM%20%22http://127.0.0.1/psms/dtd/messagev12.dtd%22%20%3E%3CMESSAGE%20VER=%221.2%22%3E%3CUSER%20USERNAME=%22heromtxmlint%22%20PASSWORD=%22jdfbts(4%22/%3E%3CSMS%20UDH=%220%22%20CODING=%221%22%20TEXT=%22" + str_text + "%22%20PROPERTY=%220%22%20ID=%222%22%3E%3CADDRESS%20FROM=%22HMCLTD%22%20TO=%22" + str_phone + "%22%20SEQ=%222%22%20TAG=%22some%20clientside%20random%20data%22%20/%3E%3C/SMS%3E%3C/MESSAGE%3E&action=send";
                                
                                try
                                {
                                    string str_response = GetOtpMobile(str_phone, str_password);
                                    //if (!str_response.Contains("OTP has been sent to your mobile number"))
                                    //{
                                    //    return "Authorization failed";
                                    //}
                                    //else
                                    //{
                                    //    msg = "success";
                                    //}
                                }
                                catch (Exception ex) { return "Failed to process password"; }
                                
                                sqlCommand = new SqlCommand("DealerId", sqlConnection);
                                sqlCommand.CommandType = CommandType.StoredProcedure;
                                sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ardId));
                                sqlCommand.Parameters.AddWithValue("@sId", systemId);
                                sqlCommand.Parameters.AddWithValue("@dId", deviceId);
                                using (SqlDataReader reader2 = sqlCommand.ExecuteReader())
                                {
                                    if (reader2.HasRows)
                                    {
                                        reader2.Close();
                                        
                                        sqlCommand = new SqlCommand("UpdateOtP", sqlConnection);
                                        sqlCommand.CommandType = CommandType.StoredProcedure;
                                        sqlCommand.Parameters.AddWithValue("@Otp", str_password);
                                        sqlCommand.Parameters.AddWithValue("@Sid", systemId);
                                        sqlCommand.Parameters.AddWithValue("@Did", deviceId);
                                        sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ardId));
                                        sqlCommand.Parameters.AddWithValue("@del_type","");
                                        result = sqlCommand.ExecuteNonQuery();
                                        sqlConnection.Close();
                                        if (result > 0)
                                        {
                                            msg = "success";
                                        }
                                        else
                                        {
                                            msg = "fail";
                                        }
                                        
                                    }
                                    else
                                    {
                                        reader2.Close();
                                        

                                        sqlCommand = new SqlCommand("InsertDealerdata", sqlConnection);
                                        sqlCommand.CommandType = CommandType.StoredProcedure;
                                        sqlCommand.Parameters.AddWithValue("@Id", RemoveSpecialChars(ardId));
                                        sqlCommand.Parameters.AddWithValue("@Sid", systemId);
                                        sqlCommand.Parameters.AddWithValue("@Did", deviceId);
                                        sqlCommand.Parameters.AddWithValue("@Otp", str_password);
                                        sqlCommand.Parameters.AddWithValue("@IsActive", 1);
                                        result = sqlCommand.ExecuteNonQuery();
                                        sqlConnection.Close();
                                        if (result > 0)
                                        {
                                            msg = "success";
                                        }
                                        else
                                        {
                                            msg = "fail";
                                        }
                                        

                                    }
                                }
                            }
                            else
                            {
                                msg = "fail";
                                reader.Close();
                            }

                        }
                    }
                    else
                    {
                        msg = "fail";
                    }
                }
                catch (Exception ex) { return ex.ToString(); }
                return msg;
            }

        }


        public string ForgotOtp(string dealerCode, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (dealerCode == null || dealerCode == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Now;
                            
                            SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                            SqlCommand sqlCommand = new SqlCommand("DealerDetails", sqlConnection);
                            sqlCommand.CommandType = CommandType.StoredProcedure;
                            sqlConnection.Open();
                            sqlCommand.Parameters.AddWithValue("@Code", dealerCode);
                            string str_password = "", str_email = "", str_phone = "", str_url = "", str_text = "";
                            int result = 0, ardId = 0;
                            using (SqlDataReader reader = sqlCommand.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        ardId = reader.GetInt32(0);
                                        str_email = reader.GetString(2);
                                        str_phone = reader.GetString(3);
                                    }
                                    reader.Close();
                                    sqlCommand = new SqlCommand("VerifyOtp", sqlConnection);
                                    sqlCommand.CommandType = CommandType.StoredProcedure;
                                    sqlCommand.Parameters.AddWithValue("@Id", ardId);
                                    sqlCommand.Parameters.AddWithValue("@sId", systemId);
                                    sqlCommand.Parameters.AddWithValue("@dId", deviceId);
                                    using (SqlDataReader reader2 = sqlCommand.ExecuteReader())
                                    {

                                        if (reader2.HasRows)
                                        {
                                            while (reader2.Read())
                                            {
                                                str_password = reader2.GetString(0);
                                            }
                                            reader2.Close();
                                            str_text = "The One Time Password(OTP) for your VCI installer is {" + str_password.Trim() +"}";
                                            str_url = "https://haapi.myvfirst.com/psms/servlet/psms.Eservice2?data=%3C?xml%20version=%221.0%22%20encoding=%22ISO-8859-1%22?%3E%3C!DOCTYPE%20MESSAGE%20SYSTEM%20%22http://127.0.0.1/psms/dtd/messagev12.dtd%22%20%3E%3CMESSAGE%20VER=%221.2%22%3E%3CUSER%20USERNAME=%22heromtxmlint%22%20PASSWORD=%22jdfbts(4%22/%3E%3CSMS%20UDH=%220%22%20CODING=%221%22%20TEXT=%22" + str_text + "%22%20PROPERTY=%220%22%20ID=%222%22%3E%3CADDRESS%20FROM=%22HMCLTD%22%20TO=%22" + str_phone + "%22%20SEQ=%222%22%20TAG=%22some%20clientside%20random%20data%22%20/%3E%3C/SMS%3E%3C/MESSAGE%3E&action=send";
                                            try
                                            {
                                                string str_response = GetOtpMobile(str_phone,str_password);
                                                if (!str_response.Contains("OTP has been sent to your mobile number"))
                                                {
                                                    return "Authorization failed";
                                                }
                                                else
                                                {
                                                    msg = "success";
                                                }
                                            }
                                            catch (Exception ex) { return "Failed to process password"; }

                                        }
                                        else
                                        {
                                            reader2.Close();

                                            msg = "Failed to process password";

                                           

                                        }
                                    }
                                }
                                else
                                {
                                    msg = "fail";
                                    reader.Close();
                                }

                            }
                        }
                        catch (Exception ex) { return ex.ToString(); }
                    }
                    else
                    {
                        return "Authorization failed";
                    }

                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }

        public string ValidateHdd(string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("ValidateHdd", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Sid", systemId);
                        sqlCommand.Parameters.AddWithValue("@Did", deviceId);
                        long dealerId = 0;
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    dealerId = reader.GetInt64(0);
                                }
                                reader.Close();
                                msg = "success";
                            }
                            else
                            {
                                msg = "fail";
                            }


                        }
                    }
                    else
                    {
                        msg = "fail";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }


        public string GetVersionDetails(string type, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (type == null || type == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Now;
                            TimeSpan span = dateTimeClient.Subtract(dateTime);
                            double minutes = (int)Math.Round(span.TotalMinutes);
                            if (minutes > 15)
                            {
                                return "fail_token";
                            }
                            SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                            sqlConnection.Open();
                            using (var sqlQuery = new SqlCommand("SELECT version FROM vci_firmware WHERE type = @Type order by version desc", sqlConnection))
                            {
                                sqlQuery.Parameters.AddWithValue("@Type", type.Trim());
                                using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                    if (sqlQueryResult != null)
                                    {
                                        sqlQueryResult.Read();
                                        float resultsoutput = sqlQueryResult.GetFloat(0);
                                        sqlConnection.Close();
                                        msg = resultsoutput.ToString().Trim();
                                    }
                                    else
                                    {
                                        msg = "NO DATA";
                                        sqlConnection.Close();
                                    }
                            }
                        }
                        catch (Exception ex) { msg = "NO DATA"; }
                    }
                    else
                    {
                        msg = "NO DATA";
                    }

                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }

        public string GetExportVersionDetails(string type, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (type == null || type == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Now;
                            TimeSpan span = dateTimeClient.Subtract(dateTime);
                            double minutes = (int)Math.Round(span.TotalMinutes);
                            if (minutes > 15)
                            {
                                return "fail_token";
                            }
                            SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                            sqlConnection.Open();
                            using (var sqlQuery = new SqlCommand("SELECT version FROM Expvci_firmware WHERE type = @Type order by version desc", sqlConnection))
                            {
                                sqlQuery.Parameters.AddWithValue("@Type", type.Trim());
                                using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                    if (sqlQueryResult != null)
                                    {
                                        sqlQueryResult.Read();
                                        float resultsoutput = sqlQueryResult.GetFloat(0);
                                        sqlConnection.Close();
                                        msg = resultsoutput.ToString().Trim();
                                    }
                                    else
                                    {
                                        msg = "NO DATA";
                                        sqlConnection.Close();
                                    }
                            }
                        }
                        catch (Exception ex) { msg = "NO DATA"; }
                    }
                    else
                    {
                        msg = "NO DATA";
                    }

                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }

        public string GetEolVersionDetails(string type, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (type == null || type == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Now;
                            TimeSpan span = dateTimeClient.Subtract(dateTime);
                            double minutes = (int)Math.Round(span.TotalMinutes);
                            if (minutes > 15)
                            {
                                return "fail_token";
                            }
                            SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                            sqlConnection.Open();
                            using (var sqlQuery = new SqlCommand("SELECT version FROM Eolvci_firmware WHERE type = @Type order by version desc", sqlConnection))
                            {
                                sqlQuery.Parameters.AddWithValue("@Type", type.Trim());
                                using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                    if (sqlQueryResult != null)
                                    {
                                        sqlQueryResult.Read();
                                        float resultsoutput = sqlQueryResult.GetFloat(0);
                                        sqlConnection.Close();
                                        msg = resultsoutput.ToString().Trim();
                                    }
                                    else
                                    {
                                        msg = "NO DATA";
                                        sqlConnection.Close();
                                    }
                            }
                        }
                        catch (Exception ex) { msg = "NO DATA"; }
                    }
                    else
                    {
                        msg = "NO DATA";
                    }

                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }



        public byte[] GetBinFile(string version, string type, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            byte[] byte_arr;
            string str_token = "";
            string[] arr_token;
            if (version == null || version == "" || type == null || type == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                byte_arr = new byte[1];
                return byte_arr;
            }
            else
            {
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                }
                catch (Exception ex)
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
                if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                {
                    try
                    {

                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        sqlConnection.Open();
                        using (var sqlQuery = new SqlCommand("SELECT binfile FROM vci_firmware WHERE version = @Version and type = @Type", sqlConnection))
                        {
                            sqlQuery.Parameters.AddWithValue("@Version", version.Trim());
                            sqlQuery.Parameters.AddWithValue("@Type", type.Trim());
                            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                if (sqlQueryResult != null)
                                {
                                    sqlQueryResult.Read();
                                    byte_arr = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                                    sqlQueryResult.GetBytes(0, 0, byte_arr, 0, byte_arr.Length);
                                    sqlConnection.Close();
                                    return byte_arr;
                                }
                                else
                                {
                                    sqlConnection.Close();
                                    byte_arr = new byte[1];
                                    return byte_arr;
                                }
                        }

                    }
                    catch (Exception ex)
                    {
                        byte_arr = new byte[1];
                        return byte_arr;
                    }
                }
                else
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
            }
        }

        public byte[] GetExpBinFile(string version, string type, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            byte[] byte_arr;
            string str_token = "";
            string[] arr_token;
            if (version == null || version == "" || type == null || type == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                byte_arr = new byte[1];
                return byte_arr;
            }
            else
            {
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                }
                catch (Exception ex)
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
                if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                {
                    try
                    {

                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        sqlConnection.Open();
                        using (var sqlQuery = new SqlCommand("SELECT binfile FROM Expvci_firmware WHERE version = @Version and type = @Type", sqlConnection))
                        {
                            sqlQuery.Parameters.AddWithValue("@Version", version.Trim());
                            sqlQuery.Parameters.AddWithValue("@Type", type.Trim());
                            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                if (sqlQueryResult != null)
                                {
                                    sqlQueryResult.Read();
                                    byte_arr = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                                    sqlQueryResult.GetBytes(0, 0, byte_arr, 0, byte_arr.Length);
                                    sqlConnection.Close();
                                    return byte_arr;
                                }
                                else
                                {
                                    sqlConnection.Close();
                                    byte_arr = new byte[1];
                                    return byte_arr;
                                }
                        }

                    }
                    catch (Exception ex)
                    {
                        byte_arr = new byte[1];
                        return byte_arr;
                    }
                }
                else
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
            }
        }

        public byte[] GetEolBinFile(string version, string type, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            byte[] byte_arr;
            string str_token = "";
            string[] arr_token;
            if (version == null || version == "" || type == null || type == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                byte_arr = new byte[1];
                return byte_arr;
            }
            else
            {
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                }
                catch (Exception ex)
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
                if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                {
                    try
                    {

                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        sqlConnection.Open();
                        using (var sqlQuery = new SqlCommand("SELECT binfile FROM Eolvci_firmware WHERE version = @Version and type = @Type", sqlConnection))
                        {
                            sqlQuery.Parameters.AddWithValue("@Version", version.Trim());
                            sqlQuery.Parameters.AddWithValue("@Type", type.Trim());
                            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                if (sqlQueryResult != null)
                                {
                                    sqlQueryResult.Read();
                                    byte_arr = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                                    sqlQueryResult.GetBytes(0, 0, byte_arr, 0, byte_arr.Length);
                                    sqlConnection.Close();
                                    return byte_arr;
                                }
                                else
                                {
                                    sqlConnection.Close();
                                    byte_arr = new byte[1];
                                    return byte_arr;
                                }
                        }

                    }
                    catch (Exception ex)
                    {
                        byte_arr = new byte[1];
                        return byte_arr;
                    }
                }
                else
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
            }
        }

        public string GetDatasetVersionOnlineDetails(string ecu_type, string model, string variant, string calid, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (ecu_type == null || ecu_type == "" || model == null || model == "" || calid == null || calid == ""|| variant == null || variant == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                List<string> File_data = new List<string>(20);
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Now;
                            TimeSpan span = dateTimeClient.Subtract(dateTime);
                            double minutes = (int)Math.Round(span.TotalMinutes);
                            if (minutes > 15)
                            {
                                return "fail_token";
                            }

                            SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                            SqlCommand sqlCommand = new SqlCommand("GETDATSETVERONLINE", sqlConnection);
                            sqlCommand.CommandType = CommandType.StoredProcedure;
                            sqlConnection.Open();
                            sqlCommand.Parameters.AddWithValue("@ecutype", ecu_type);
                            sqlCommand.Parameters.AddWithValue("@model", model);
                            sqlCommand.Parameters.AddWithValue("@variant", variant);
                            sqlCommand.Parameters.AddWithValue("@CALID", calid);
                            using (SqlDataReader reader = sqlCommand.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        msg = reader.GetString(0);
                                        
                                        msg += "," + reader.GetString(1);
                                        msg += "," + reader.GetDateTime(2).ToString("MM/dd/yyyy");
                                        msg += "," + reader.GetString(3);
                                    }
                                    reader.Close();

                                }
                                else
                                {
                                    msg = "fail";
                                }


                            }
                        }
                        catch (Exception ex) { msg = "NO DATA"; }
                    }
                    else
                    {
                        msg = "NO DATA";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }


        }


        public string GetBinFileSize(string ecu_type, string model, string variant, string dsname, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (ecu_type == null || ecu_type == "" || model == null || model == "" || variant == null || variant == "" || dsname == null || dsname == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                List<string> File_data = new List<string>(20);
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Now;
                            TimeSpan span = dateTimeClient.Subtract(dateTime);
                            double minutes = (int)Math.Round(span.TotalMinutes);
                            if (minutes > 15)
                            {
                                return "fail_token";
                            }

                            SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                            SqlCommand sqlCommand = new SqlCommand("GetFileSize", sqlConnection);
                            sqlCommand.CommandType = CommandType.StoredProcedure;
                            sqlConnection.Open();
                            sqlCommand.Parameters.AddWithValue("@DST", "DST");
                            sqlCommand.Parameters.AddWithValue("@ecu_type", ecu_type);
                            sqlCommand.Parameters.AddWithValue("@model", model);
                            sqlCommand.Parameters.AddWithValue("@variant", variant);
                            sqlCommand.Parameters.AddWithValue("@filename", dsname);
                            
                            using (SqlDataReader reader = sqlCommand.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        msg = reader.GetString(0);
                                    }
                                    reader.Close();

                                }
                                else
                                {
                                    msg = "fail";
                                }


                            }
                        }
                        catch (Exception ex) { msg = "NO DATA"; }
                    }
                    else
                    {
                        msg = "NO DATA";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;

            }
        }
        public string GetDatasetVersionDetails(string ecu_type, string model, string variant, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (ecu_type == null || ecu_type == "" || model == null || model == "" || variant == null || variant == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                List<string> File_data = new List<string>(20);
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Now;
                            TimeSpan span = dateTimeClient.Subtract(dateTime);
                            double minutes = (int)Math.Round(span.TotalMinutes);
                            if (minutes > 15)
                            {
                                return "fail_token";
                            }

                            SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                            SqlCommand sqlCommand = new SqlCommand("GETDATSETVER", sqlConnection);
                            sqlCommand.CommandType = CommandType.StoredProcedure;
                            sqlConnection.Open();
                            sqlCommand.Parameters.AddWithValue("@ecu_type", ecu_type);
                            sqlCommand.Parameters.AddWithValue("@model",model);
                            sqlCommand.Parameters.AddWithValue("@variant", variant);
                            sqlCommand.Parameters.AddWithValue("@DST", "DST");
                            using (SqlDataReader reader = sqlCommand.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        msg = reader.GetString(0);
                                    }
                                    reader.Close();
                                    
                                }
                                else
                                {
                                    msg = "fail";
                                }


                            }
                        }
                        catch (Exception ex) { msg = "NO DATA"; }
                    }
                    else
                    {
                        msg = "NO DATA";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }

        public byte[] GetDatasetFile(string ecu_type, string model, string variant,string filedata, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            byte[] byte_arr;
            string str_token = "";
            string[] arr_token;
            if (ecu_type == null || ecu_type == "" || model == null || model == "" || variant == null || variant == "" || filedata == null || filedata == ""|| systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                byte_arr = new byte[1];
                return byte_arr;
            }
            else
            {

                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                }
                catch (Exception ex)
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
                if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                {
                    try
                    {
                        DateTime dateTime = DateTime.Now;
                        TimeSpan span = dateTimeClient.Subtract(dateTime);
                        double minutes = (int)Math.Round(span.TotalMinutes);
                        if (minutes > 15)
                        {
                            byte_arr = new byte[1];
                            return byte_arr;
                        }
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        sqlConnection.Open();
                        using (var sqlQuery = new SqlCommand("SELECT dataset FROM ecu_dataset WHERE ecu_type = @EcuType and model = @Model and variant=@Variant and dataset_name = @FileData and dataset_type='DST' order by id desc", sqlConnection))
                        {
                            sqlQuery.Parameters.AddWithValue("@EcuType", ecu_type.Trim());
                            sqlQuery.Parameters.AddWithValue("@Model", model.Trim());
                            sqlQuery.Parameters.AddWithValue("@Variant", variant.Trim());
                            sqlQuery.Parameters.AddWithValue("@FileData", filedata.Trim());
                            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                if (sqlQueryResult != null)
                                {
                                    sqlQueryResult.Read();
                                    byte_arr = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                                    sqlQueryResult.GetBytes(0, 0, byte_arr, 0, byte_arr.Length);
                                    sqlConnection.Close();
                                    return byte_arr;
                                }
                                else
                                {
                                    sqlConnection.Close();
                                    byte_arr = new byte[1];
                                    return byte_arr;
                                }
                        }

                    }
                    catch (Exception ex)
                    {
                        byte_arr = new byte[1];
                        return byte_arr;
                    }
                }
                else
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
            }


        }
        public byte[] GetDatasetCksFile(string ecu_type, string model, string variant,string cksfile, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            byte[] byte_arr;
            string str_token = "";
            string[] arr_token;
            if (ecu_type == null || ecu_type == "" || model == null || model == "" || variant == null || cksfile == ""|| cksfile == null || variant == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                byte_arr = new byte[1];
                return byte_arr;
            }
            else
            {

                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                }
                catch (Exception ex)
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
                if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                {
                    try
                    {
                        DateTime dateTime = DateTime.Now;
                        TimeSpan span = dateTimeClient.Subtract(dateTime);
                        double minutes = (int)Math.Round(span.TotalMinutes);
                        if (minutes > 15)
                        {
                            byte_arr = new byte[1];
                            return byte_arr;
                        }
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        sqlConnection.Open();
                        using (var sqlQuery = new SqlCommand("SELECT dataset FROM ecu_dataset WHERE ecu_type = @EcuType and model = @Model and variant=@Variant and dataset_name=@Cksfildata and dataset_type='CKS' order by id desc", sqlConnection))
                        {
                            sqlQuery.Parameters.AddWithValue("@EcuType", ecu_type.Trim());
                            sqlQuery.Parameters.AddWithValue("@Model", model.Trim());
                            sqlQuery.Parameters.AddWithValue("@Variant", variant.Trim());
                            sqlQuery.Parameters.AddWithValue("@Cksfildata", cksfile.Trim());
                            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                if (sqlQueryResult != null)
                                {
                                    sqlQueryResult.Read();
                                    byte_arr = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                                    sqlQueryResult.GetBytes(0, 0, byte_arr, 0, byte_arr.Length);
                                    sqlConnection.Close();
                                    return byte_arr;
                                }
                                else
                                {
                                    sqlConnection.Close();
                                    byte_arr = new byte[1];
                                    return byte_arr;
                                }
                        }

                    }
                    catch (Exception ex)
                    {
                        byte_arr = new byte[1];
                        return byte_arr;
                    }
                }
                else
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
            }


        }

        public string GetDatasetFileVersionDetails(string ecu_type, string model, string variant, string vin_from, string vin_to, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (ecu_type == null || ecu_type == "" || model == null || model == "" || variant == null || variant == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Now;
                            TimeSpan span = dateTimeClient.Subtract(dateTime);
                            double minutes = (int)Math.Round(span.TotalMinutes);
                            if (minutes > 15)
                            {
                                return "fail_token";
                            }
                            SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                            sqlConnection.Open();
                            using (var sqlQuery = new SqlCommand("SELECT ecu_name,dataset_name,release_date FROM ecu_dataset WHERE ecu_type = @EcuType and model= @Model and variant=@Variant and Vin_From=@VinFrom and Vin_To=@VinTo order by id desc", sqlConnection))
                            {
                                sqlQuery.Parameters.AddWithValue("@EcuType", ecu_type.Trim());
                                sqlQuery.Parameters.AddWithValue("@Model", model.Trim());
                                sqlQuery.Parameters.AddWithValue("@Variant", variant.Trim());
                                sqlQuery.Parameters.AddWithValue("@VinFrom", vin_from.Trim());
                                sqlQuery.Parameters.AddWithValue("@VinTo", vin_to.Trim());
                                using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                    if (sqlQueryResult != null)
                                    {
                                        sqlQueryResult.Read();
                                        msg = sqlQueryResult.GetString(0);
                                        msg += "," + sqlQueryResult.GetString(1);
                                        msg += "," + sqlQueryResult.GetDateTime(2).ToString("MM/dd/yyyy");
                                        sqlConnection.Close();
                                    }
                                    else
                                    {
                                        msg = "NO DATA";
                                        sqlConnection.Close();
                                    }
                            }
                        }
                        catch (Exception ex) { msg = "NO DATA"; }
                    }
                    else
                    {
                        msg = "NO DATA";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }

        public byte[] GetDatasetFileData(string ecu_type, string model, string variant, string vin_from, string vin_to, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            byte[] byte_arr;
            string str_token = "";
            string[] arr_token;
            if (ecu_type == null || ecu_type == "" || model == null || model == "" || variant == null || variant == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                byte_arr = new byte[1];
                return byte_arr;
            }
            else
            {

                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                }
                catch (Exception ex)
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
                if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                {
                    try
                    {
                        DateTime dateTime = DateTime.Now;
                        TimeSpan span = dateTimeClient.Subtract(dateTime);
                        double minutes = (int)Math.Round(span.TotalMinutes);
                        if (minutes > 15)
                        {
                            byte_arr = new byte[1];
                            return byte_arr;
                        }
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        sqlConnection.Open();
                        using (var sqlQuery = new SqlCommand("SELECT dataset FROM ecu_dataset WHERE ecu_type = @EcuType and model = @Model and variant=@Variant and Vin_From=@VinFrom and Vin_To=@VinTo order by id desc", sqlConnection))
                        {
                            sqlQuery.Parameters.AddWithValue("@EcuType", ecu_type.Trim());
                            sqlQuery.Parameters.AddWithValue("@Model", model.Trim());
                            sqlQuery.Parameters.AddWithValue("@Variant", variant.Trim());
                            sqlQuery.Parameters.AddWithValue("@VinFrom", vin_from.Trim());
                            sqlQuery.Parameters.AddWithValue("@VinTo", vin_to.Trim());
                            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                if (sqlQueryResult != null)
                                {
                                    sqlQueryResult.Read();
                                    byte_arr = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                                    sqlQueryResult.GetBytes(0, 0, byte_arr, 0, byte_arr.Length);
                                    sqlConnection.Close();
                                    return byte_arr;
                                }
                                else
                                {
                                    sqlConnection.Close();
                                    byte_arr = new byte[1];
                                    return byte_arr;
                                }
                        }

                    }
                    catch (Exception ex)
                    {
                        byte_arr = new byte[1];
                        return byte_arr;
                    }
                }
                else
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
            }


        }

        public byte[] GetTcuDataset(string Calid, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            byte[] byte_arr;
            string str_token = "";
            string[] arr_token;
            if (Calid == null || Calid == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                byte_arr = new byte[1];
                return byte_arr;
            }
            else
            {

                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                }
                catch (Exception ex)
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
                if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                {
                    try
                    {
                        DateTime dateTime = DateTime.Now;
                        TimeSpan span = dateTimeClient.Subtract(dateTime);
                        double minutes = (int)Math.Round(span.TotalMinutes);
                        if (minutes > 15)
                        {
                            byte_arr = new byte[1];
                            return byte_arr;
                        }
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        sqlConnection.Open();
                        using (var sqlQuery = new SqlCommand("SELECT dataset FROM VINTCUVDS WHERE dataset_name = @Calid  order by id desc", sqlConnection))
                        {
                            sqlQuery.Parameters.AddWithValue("@Calid", Calid.Trim());

                            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                if (sqlQueryResult != null)
                                {
                                    sqlQueryResult.Read();
                                    byte_arr = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                                    sqlQueryResult.GetBytes(0, 0, byte_arr, 0, byte_arr.Length);
                                    sqlConnection.Close();
                                    return byte_arr;
                                }
                                else
                                {
                                    sqlConnection.Close();
                                    byte_arr = new byte[1];
                                    return byte_arr;
                                }
                        }

                    }
                    catch (Exception ex)
                    {
                        byte_arr = new byte[1];
                        return byte_arr;
                    }
                }
                else
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
            }


        }

        public byte[] GetEngDataset(string vinvds, string filename, string fileType,  string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            byte[] byte_arr;
            string str_token = "";
            string[] arr_token;
            if (vinvds == null || vinvds == "" || filename == null || filename == "" || fileType==null|| fileType==""|| systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                byte_arr = new byte[1];
                return byte_arr;
            }
            else
            {

                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                }
                catch (Exception ex)
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
                if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                {
                    try
                    {
                        DateTime dateTime = DateTime.Now;
                        TimeSpan span = dateTimeClient.Subtract(dateTime);
                        double minutes = (int)Math.Round(span.TotalMinutes);
                        if (minutes > 15)
                        {
                            byte_arr = new byte[1];
                            return byte_arr;
                        }
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        sqlConnection.Open();
                        using (var sqlQuery = new SqlCommand("SELECT file_content FROM Eol_ecu_dataset WHERE file_name = @filename and Type=@type and VINVDS = @VDS  order by id desc", sqlConnection))
                        {
                            sqlQuery.Parameters.AddWithValue("@filename", filename.Trim());
                            sqlQuery.Parameters.AddWithValue("@type", fileType.Trim());
                            sqlQuery.Parameters.AddWithValue("@VDS", vinvds.Trim());

                            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                if (sqlQueryResult != null)
                                {
                                    sqlQueryResult.Read();
                                    byte_arr = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                                    sqlQueryResult.GetBytes(0, 0, byte_arr, 0, byte_arr.Length);
                                    sqlConnection.Close();
                                    return byte_arr;
                                }
                                else
                                {
                                    sqlConnection.Close();
                                    byte_arr = new byte[1];
                                    return byte_arr;
                                }
                        }

                    }
                    catch (Exception ex)
                    {
                        byte_arr = new byte[1];
                        return byte_arr;
                    }
                }
                else
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
            }


        }

        public string GETTCUDATASETFILE(string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                List<string> File_data = new List<string>(20);
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Now;
                            TimeSpan span = dateTimeClient.Subtract(dateTime);
                            double minutes = (int)Math.Round(span.TotalMinutes);
                            if (minutes > 15)
                            {
                                return "fail_token";
                            }

                            SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                            SqlCommand sqlCommand = new SqlCommand("GETEOLTCUFILE", sqlConnection);
                            sqlCommand.CommandType = CommandType.StoredProcedure;
                            sqlConnection.Open();

                            using (SqlDataReader reader = sqlCommand.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        msg = reader.GetString(0);
                                    }
                                    reader.Close();

                                }
                                else
                                {
                                    msg = "NO DATA";
                                }


                            }
                        }
                        catch (Exception ex) { msg = "NO DATA"; }
                    }
                    else
                    {
                        msg = "NO DATA";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }


        public string GETEOLENGFILE(string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                List<string> File_data = new List<string>(20);
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Now;
                            TimeSpan span = dateTimeClient.Subtract(dateTime);
                            double minutes = (int)Math.Round(span.TotalMinutes);
                            if (minutes > 15)
                            {
                                return "fail_token";
                            }

                            SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                            SqlCommand sqlCommand = new SqlCommand("GETEOLENGFILE", sqlConnection);
                            sqlCommand.CommandType = CommandType.StoredProcedure;
                            sqlConnection.Open();

                            using (SqlDataReader reader = sqlCommand.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        msg = reader.GetString(0);
                                    }
                                    reader.Close();

                                }
                                else
                                {
                                    msg = "NO DATA";
                                }


                            }
                        }
                        catch (Exception ex) { msg = "NO DATA"; }
                    }
                    else
                    {
                        msg = "NO DATA";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }







        public string GetEmployeeDetails(string str_code, string systemId, string deviceId, string token)
        {
            if (str_code == null || str_code == "" || token == null || token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {

                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("GetEmployeedata", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Code", str_code);
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                msg = "success";
                                while (reader.Read())
                                {
                                    msg += "," + reader.GetString(0);
                                    msg += "," + reader.GetString(1);
                                    msg += "," + reader.GetString(2);
                                    msg += "," + reader.GetString(3);
                                    msg += "," + reader.GetString(4);
                                    msg += "," + reader.GetString(5);
                                    msg += "," + reader.GetString(6);
                                }


                            }
                            else
                            {
                                msg = "no_data";

                            }
                            reader.Close();
                        }
                    }
                    else
                    {
                        msg = "fail_token";
                    }
                }
                catch (Exception ex)
                {
                    return "fail_ex";
                }
                return msg;
            }
        }

        public byte[] GetApplicationBinFile(string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            byte[] byte_arr;
            string str_token = "";
            string[] arr_token;
            if (systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                byte_arr = new byte[1];
                return byte_arr;
            }
            else
            {
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                }
                catch (Exception ex)
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
                if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                {
                    try
                    {

                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        sqlConnection.Open();
                        using (var sqlQuery = new SqlCommand("SELECT TOP 1  exe FROM application ORDER BY id DESC", sqlConnection))
                        {
                            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                if (sqlQueryResult != null)
                                {
                                    sqlQueryResult.Read();
                                    byte_arr = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                                    sqlQueryResult.GetBytes(0, 0, byte_arr, 0, byte_arr.Length);
                                    sqlConnection.Close();
                                    return byte_arr;
                                }
                                else
                                {
                                    sqlConnection.Close();
                                    byte_arr = new byte[1];
                                    return byte_arr;
                                }
                        }

                    }
                    catch (Exception ex)
                    {
                        byte_arr = new byte[1];
                        return byte_arr;
                    }
                }
                else
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
            }
        }

        public byte[] GetExportApplicationBinFile(string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            byte[] byte_arr;
            string str_token = "";
            string[] arr_token;
            if (systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                byte_arr = new byte[1];
                return byte_arr;
            }
            else
            {
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                }
                catch (Exception ex)
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
                if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                {
                    try
                    {

                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        sqlConnection.Open();
                        using (var sqlQuery = new SqlCommand("SELECT TOP 1  exe FROM Exp_application ORDER BY id DESC", sqlConnection))
                        {
                            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                if (sqlQueryResult != null)
                                {
                                    sqlQueryResult.Read();
                                    byte_arr = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                                    sqlQueryResult.GetBytes(0, 0, byte_arr, 0, byte_arr.Length);
                                    sqlConnection.Close();
                                    return byte_arr;
                                }
                                else
                                {
                                    sqlConnection.Close();
                                    byte_arr = new byte[1];
                                    return byte_arr;
                                }
                        }

                    }
                    catch (Exception ex)
                    {
                        byte_arr = new byte[1];
                        return byte_arr;
                    }
                }
                else
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
            }
        }

        public byte[] GetEolApplicationBinFile(string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            byte[] byte_arr;
            string str_token = "";
            string[] arr_token;
            if (systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                byte_arr = new byte[1];
                return byte_arr;
            }
            else
            {
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                }
                catch (Exception ex)
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
                if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                {
                    try
                    {

                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        sqlConnection.Open();
                        using (var sqlQuery = new SqlCommand("SELECT TOP 1  exe FROM Eol_application ORDER BY id DESC", sqlConnection))
                        {
                            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                if (sqlQueryResult != null)
                                {
                                    sqlQueryResult.Read();
                                    byte_arr = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                                    sqlQueryResult.GetBytes(0, 0, byte_arr, 0, byte_arr.Length);
                                    sqlConnection.Close();
                                    return byte_arr;
                                }
                                else
                                {
                                    sqlConnection.Close();
                                    byte_arr = new byte[1];
                                    return byte_arr;
                                }
                        }

                    }
                    catch (Exception ex)
                    {
                        byte_arr = new byte[1];
                        return byte_arr;
                    }
                }
                else
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
            }
        }



        public string CheckApplicationUpdate(string version,string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            string msg = "",str_token = "";
            string[] arr_token;
            if (version == null || version == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail_null";
            }
            else
            {
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {


                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("CheckApplicationUpdate", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    msg =reader.GetString(0);
                                }
                                string[] str_server = msg.Split('.');
                                string[] str_app = version.Split('.');
                                try
                                {
                                    if (Int32.Parse(str_server[0]) > Int32.Parse(str_app[0]))
                                    {
                                        return "update";
                                    }
                                    else if (Int32.Parse(str_server[0]) == Int32.Parse(str_app[0]))
                                    {
                                        if (Int32.Parse(str_server[1]) > Int32.Parse(str_app[1]))
                                        {
                                            return "update";
                                        }
                                        else if (Int32.Parse(str_server[1]) == Int32.Parse(str_app[1]))
                                        {
                                            if (Int32.Parse(str_server[2]) > Int32.Parse(str_app[2]))
                                            {
                                                return "update";
                                            }
                                            else if (Int32.Parse(str_server[2]) == Int32.Parse(str_app[2]))
                                            {
                                                if (Int32.Parse(str_server[3]) > Int32.Parse(str_app[3]))
                                                {
                                                    return "update";
                                                }
                                                else
                                                {
                                                    return "no_data";
                                                }
                                            }
                                            else
                                            {
                                                return "no_data";
                                            }

                                        }
                                        else
                                        {
                                            return "no_data";
                                        }

                                    }
                                    else
                                    {
                                        return "no_data";
                                    }
                                }
                                catch(Exception ex)
                                {
                                    return "no_data";
                                }
                                

                            }
                            else
                            {
                                return "no_data";

                            }
                            reader.Close();
                        }


                    }
                    else
                    {
                        return "fail_token";
                    }
                }
                catch (Exception ex)
                {
                    return "fail_ex";
                }
            }
        }

        public string CheckExportApplicationUpdate(string version, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            string msg = "", str_token = "";
            string[] arr_token;
            if (version == null || version == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail_null";
            }
            else
            {
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {


                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("CheckExportApplicationUpdate", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    msg = reader.GetString(0);
                                }
                                string[] str_server = msg.Split('.');
                                string[] str_app = version.Split('.');
                                try
                                {
                                    if (Int32.Parse(str_server[0]) > Int32.Parse(str_app[0]))
                                    {
                                        return "update";
                                    }
                                    else if (Int32.Parse(str_server[0]) == Int32.Parse(str_app[0]))
                                    {
                                        if (Int32.Parse(str_server[1]) > Int32.Parse(str_app[1]))
                                        {
                                            return "update";
                                        }
                                        else if (Int32.Parse(str_server[1]) == Int32.Parse(str_app[1]))
                                        {
                                            if (Int32.Parse(str_server[2]) > Int32.Parse(str_app[2]))
                                            {
                                                return "update";
                                            }
                                            else if (Int32.Parse(str_server[2]) == Int32.Parse(str_app[2]))
                                            {
                                                if (Int32.Parse(str_server[3]) > Int32.Parse(str_app[3]))
                                                {
                                                    return "update";
                                                }
                                                else
                                                {
                                                    return "no_data";
                                                }
                                            }
                                            else
                                            {
                                                return "no_data";
                                            }

                                        }
                                        else
                                        {
                                            return "no_data";
                                        }

                                    }
                                    else
                                    {
                                        return "no_data";
                                    }
                                }
                                catch (Exception ex)
                                {
                                    return "no_data";
                                }


                            }
                            else
                            {
                                return "no_data";

                            }
                            reader.Close();
                        }


                    }
                    else
                    {
                        return "fail_token";
                    }
                }
                catch (Exception ex)
                {
                    return "fail_ex";
                }
            }
        }

        public string CheckEolApplicationUpdate(string version, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            string msg = "", str_token = "";
            string[] arr_token;
            if (version == null || version == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail_null";
            }
            else
            {
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {


                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("CheckEolApplicationUpdate", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    msg = reader.GetString(0);
                                }
                                string[] str_server = msg.Split('.');
                                string[] str_app = version.Split('.');
                                try
                                {
                                    if (Int32.Parse(str_server[0]) > Int32.Parse(str_app[0]))
                                    {
                                        return "update";
                                    }
                                    else if (Int32.Parse(str_server[0]) == Int32.Parse(str_app[0]))
                                    {
                                        if (Int32.Parse(str_server[1]) > Int32.Parse(str_app[1]))
                                        {
                                            return "update";
                                        }
                                        else if (Int32.Parse(str_server[1]) == Int32.Parse(str_app[1]))
                                        {
                                            if (Int32.Parse(str_server[2]) > Int32.Parse(str_app[2]))
                                            {
                                                return "update";
                                            }
                                            else if (Int32.Parse(str_server[2]) == Int32.Parse(str_app[2]))
                                            {
                                                if (Int32.Parse(str_server[3]) > Int32.Parse(str_app[3]))
                                                {
                                                    return "update";
                                                }
                                                else
                                                {
                                                    return "no_data";
                                                }
                                            }
                                            else
                                            {
                                                return "no_data";
                                            }

                                        }
                                        else
                                        {
                                            return "no_data";
                                        }

                                    }
                                    else
                                    {
                                        return "no_data";
                                    }
                                }
                                catch (Exception ex)
                                {
                                    return "no_data";
                                }


                            }
                            else
                            {
                                return "no_data";

                            }
                            reader.Close();
                        }


                    }
                    else
                    {
                        return "fail_token";
                    }
                }
                catch (Exception ex)
                {
                    return "fail_ex";
                }
            }
        }


        private static Random random = new Random();
        public static string RandomString(int length)
        {
            const string chars = "ABCDEFGHJKMNPQRSTUVWXYZ123456789";
            return new string(Enumerable.Repeat(chars, length)
              .Select(s => s[random.Next(s.Length)]).ToArray());
        }


        public string GetOtpMobile(string mobile_no, string password)
        {
            string message = "";
            try
            {
                //string OTP = RandomString(6).Trim();
                string AppName = ConfigurationManager.AppSettings["SMSAppName"];
                string Priority = ConfigurationManager.AppSettings["SMSPriority"];
                string SMSType = ConfigurationManager.AppSettings["SMSType"];
                string AccountType = ConfigurationManager.AppSettings["SMSAccountType"];
                long DLTCTId = Convert.ToInt64(ConfigurationManager.AppSettings["SMSDLTCTId"]);

                var smsParameters = new SMSParameters
                {
                    AppName = AppName,
                    MobileNo = mobile_no,
                    Priority = Priority,
                    SmsType = SMSType,
                    AccountType = AccountType,
                    DltCtId = DLTCTId,
                    Message = $"The One Time Password(OTP) for your VCI Installer is {password} HMCL"
                };

                var requestWrapper = new
                {
                    data = smsParameters
                };

                // Configure TLS & cert validation BEFORE making request
                ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
                ServicePointManager.ServerCertificateValidationCallback = delegate { return true; };

                using (HttpClient client = new HttpClient())
                {
                    string SubscriptionKey = ConfigurationManager.AppSettings["SubscriptionKey"];
                    string SubscriptionKeyValue = ConfigurationManager.AppSettings["SubscriptionKeyValue"];
                    string MediaType = ConfigurationManager.AppSettings["MediaType"];
                    string APIUri = ConfigurationManager.AppSettings["APIUri"];

                    string serializeObject = JsonConvert.SerializeObject(requestWrapper);

                    HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Post, APIUri);
                    request.Headers.Add(SubscriptionKey, SubscriptionKeyValue);
                    request.Content = new StringContent(serializeObject, Encoding.UTF8, MediaType);

                    var clientResponse = client.SendAsync(request).Result;
                    var responseBody = clientResponse.Content.ReadAsStringAsync().Result;

                    if (clientResponse.IsSuccessStatusCode)
                    {
                        message = "OTP has been sent to your mobile number";
                    }
                    else
                    {
                        message = $"OTP not sent. Status: {clientResponse.StatusCode}, Reason: {clientResponse.ReasonPhrase}, Body: {responseBody}";
                    }
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine("Exception:" + ex.Message);
                message = "Error: " + ex.Message;
            }
            return message;
        }

        private class SMSParameters
        {
            [JsonProperty("appName")]
            public string AppName { get; set; }

            [JsonProperty("mobileNo")]
            public string MobileNo { get; set; }

            [JsonProperty("priority")]
            public string Priority { get; set; }

            [JsonProperty("smsType")]
            public string SmsType { get; set; }

            [JsonProperty("accountType")]
            public string AccountType { get; set; }

            [JsonProperty("dltCtId")]
            public long DltCtId { get; set; }

            [JsonProperty("message")]
            public string Message { get; set; }
        }

        public string sendmail(string email, string password)
        {
            SmtpClient smtpClient = new SmtpClient("smtp-relay.gmail.com", 25);
            smtpClient.EnableSsl = true;
            using (MailMessage message = new MailMessage())
            {
                message.From = new MailAddress("vcisupport@heromotocorp.com");
                message.Subject = "Hero VCI Installer OTP";
                message.Body = "Dear Sir/Madam,\nThe One Time Password(OTP) for your VCI software login is < " + password.Trim() + ">.\n This OTP is valid for 30 minutes or 1 successful attempt whichever is earlier.\n Please do not share this One Time Password with anyone.\nThis is system generated mail. Please do not reply on it. For any support please contact Hero VCI Support team @ 18002084494.\n With Regards\n Hero MotoCorp Ltd";
                message.IsBodyHtml = true;
                message.To.Add(email);
                try
                {
                    smtpClient.Send(message);
                    return "success";
                }
                catch (Exception ex)
                {
                    return ex.ToString();
                }
            }
        }

        public string Get(string uri)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(uri);
            request.AutomaticDecompression = DecompressionMethods.GZip | DecompressionMethods.Deflate;
            using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
            using (Stream stream = response.GetResponseStream())
            using (StreamReader reader = new StreamReader(stream))
            {
                return reader.ReadToEnd();
            }
        }


        public static string Decrypt(string input)
        {
            var privateKey = "<RSAKeyValue><Modulus>21wEnTU+mcD2w0Lfo1Gv4rtcSWsQJQTNa6gio05AOkV/Er9w3Y13Ddo5wGtjJ19402S71HUeN0vbKILLJdRSES5MHSdJPSVrOqdrll/vLXxDxWs/U0UT1c8u6k/Ogx9hTtZxYwoeYqdhDblof3E75d9n2F0Zvf6iTb4cI7j6fMs=</Modulus><Exponent>AQAB</Exponent><P>/aULPE6jd5IkwtWXmReyMUhmI/nfwfkQSyl7tsg2PKdpcxk4mpPZUdEQhHQLvE84w2DhTyYkPHCtq/mMKE3MHw==</P><Q>3WV46X9Arg2l9cxb67KVlNVXyCqc/w+LWt/tbhLJvV2xCF/0rWKPsBJ9MC6cquaqNPxWWEav8RAVbmmGrJt51Q==</Q><DP>8TuZFgBMpBoQcGUoS2goB4st6aVq1FcG0hVgHhUI0GMAfYFNPmbDV3cY2IBt8Oj/uYJYhyhlaj5YTqmGTYbATQ==</DP><DQ>FIoVbZQgrAUYIHWVEYi/187zFd7eMct/Yi7kGBImJStMATrluDAspGkStCWe4zwDDmdam1XzfKnBUzz3AYxrAQ==</DQ><InverseQ>QPU3Tmt8nznSgYZ+5jUo9E0SfjiTu435ihANiHqqjasaUNvOHKumqzuBZ8NRtkUhS6dsOEb8A2ODvy7KswUxyA==</InverseQ><D>cgoRoAUpSVfHMdYXW9nA3dfX75dIamZnwPtFHq80ttagbIe4ToYYCcyUz5NElhiNQSESgS5uCgNWqWXt5PnPu4XmCXx6utco1UVH8HGLahzbAnSy6Cj3iUIQ7Gj+9gQ7PkC434HTtHazmxVgIR5l56ZjoQ8yGNCPZnsdYEmhJWk=</D></RSAKeyValue>";

            var testData = Encoding.UTF8.GetBytes(input);

            using (var rsa = new RSACryptoServiceProvider(1024))
            {
                try
                {
                    var base64Encrypted = input;

                                   
                    rsa.FromXmlString(privateKey);

                    var resultBytes = Convert.FromBase64String(base64Encrypted);
                    var decryptedBytes = rsa.Decrypt(resultBytes, true);
                    var decryptedData = Encoding.UTF8.GetString(decryptedBytes);
                    return decryptedData.ToString();
                }
                finally
                {
                    rsa.PersistKeyInCsp = false;
                }
            }
        }



        public string SendOtp()
        {
           
           
            try
            {
                SmtpClient smtpClient = new SmtpClient("smtp-relay.gmail.com", 25);
                smtpClient.EnableSsl = true;
                using (MailMessage message = new MailMessage())
                {
                    message.From = new MailAddress("vcisupport@heromotocorp.com");
                    message.Subject = "Hero VCI Installer OTP";
                    message.Body = "Dear Sir/Madam, &#10; " +
                        "The One Time Password(OTP) for your VCI software login is < " + "7854" + ">. This OTP is valid for 30 minutes or 1 successful attempt whichever is earlier. &#10; Please do not share this One Time Password with anyone.\nThis is system generated mail. Please do not reply on it.. &#10; For any support please contact Hero VCI Support team @ 18002084494.\n With Regards\n Hero MotoCorp Ltd";
                    message.IsBodyHtml = true;
                    message.To.Add("babith13@gmail.com");
                    
                    try
                    {
                        smtpClient.Send(message);
                        return "success";
                    }
                    catch (Exception ex)
                    {
                        return ex.ToString();
                    }
                }
            }
            catch (Exception ex) { return ex.ToString(); }
            
        }
        
        public string GETEOLDTC(string variant, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if ( variant == null || variant == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                List<string> File_data = new List<string>(20);
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Now;
                            TimeSpan span = dateTimeClient.Subtract(dateTime);
                            double minutes = (int)Math.Round(span.TotalMinutes);
                            if (minutes > 15)
                            {
                                return "fail_token";
                            }

                            SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                            SqlCommand sqlCommand = new SqlCommand("GETEOLDTC", sqlConnection);
                            sqlCommand.CommandType = CommandType.StoredProcedure;
                            sqlConnection.Open();
                           
                            sqlCommand.Parameters.AddWithValue("@variant", variant);
                            
                            using (SqlDataReader reader = sqlCommand.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        msg = reader.GetString(0);
                                    }
                                    reader.Close();

                                }
                                else
                                {
                                    msg = "fail";
                                }


                            }
                        }
                        catch (Exception ex) { msg = "NO DATA"; }
                    }
                    else
                    {
                        msg = "NO DATA";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }
        
        public string GETEOLVRIANT( string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                List<string> File_data = new List<string>(20);
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Now;
                            TimeSpan span = dateTimeClient.Subtract(dateTime);
                            double minutes = (int)Math.Round(span.TotalMinutes);
                            if (minutes > 15)
                            {
                                return "fail_token";
                            }

                            SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                            SqlCommand sqlCommand = new SqlCommand("GETEOLVARIANT", sqlConnection);
                            sqlCommand.CommandType = CommandType.StoredProcedure;
                            sqlConnection.Open();

                            

                            using (SqlDataReader reader = sqlCommand.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        msg = reader.GetString(0);
                                    }
                                    reader.Close();

                                }
                                else
                                {
                                    msg = "fail";
                                }


                            }
                        }
                        catch (Exception ex) { msg = "NO DATA"; }
                    }
                    else
                    {
                        msg = "NO DATA";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }       
        public string GETEOLSOC(string variant, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (variant == null || variant == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }
            else
            {
                List<string> File_data = new List<string>(20);
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        try
                        {
                            DateTime dateTime = DateTime.Now;
                            TimeSpan span = dateTimeClient.Subtract(dateTime);
                            double minutes = (int)Math.Round(span.TotalMinutes);
                            if (minutes > 15)
                            {
                                return "fail_token";
                            }

                            SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                            SqlCommand sqlCommand = new SqlCommand("GETEOLSOC", sqlConnection);
                            sqlCommand.CommandType = CommandType.StoredProcedure;
                            sqlConnection.Open();

                            sqlCommand.Parameters.AddWithValue("@variant", variant);

                            using (SqlDataReader reader = sqlCommand.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        msg = reader.GetString(0);
                                    }
                                    reader.Close();

                                }
                                else
                                {
                                    msg = "fail";
                                }


                            }
                        }
                        catch (Exception ex) { msg = "NO DATA"; }
                    }
                    else
                    {
                        msg = "NO DATA";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }

        public string EolValdation(string user,string Password, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (user == null || user == "" || Password == null || Password == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                return "fail";
            }

            else
            {
                string msg = "", Role = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');


                     if (arr_token[0].Contains(user))                    
                     {
                        DateTime dateTime = DateTime.Now;
                        
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("EolUserDetails", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Username", user);
                        sqlCommand.Parameters.AddWithValue("@password", Password);
                        
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    Role = reader.GetString(0);
                                    
                                }
                                msg = "success,"+Role.Trim();
                            }
                            else
                            {
                                msg = "fail";
                            }
                            reader.Close();
                            sqlConnection.Close();
                        }
                    }
                    else
                    {
                        msg = "fail";
                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }

        }

        public string DELIUPRMFRZNCS(string SRNumber, string SystemId, string DeviceId, DateTime DateTimeClient, string Token)
        {
            if (SRNumber == null || SRNumber == ""  || SystemId == null || SystemId == "" || DeviceId == null || DeviceId == "" || DateTimeClient == null || Token == null || Token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(Token).Trim();
                    arr_token = str_token.Split(',');
                    int result = 0;
                    //  if (arr_token[0].Equals(IEMINO) && arr_token[1].Equals(MACID))
                    if (arr_token[0].Equals(SystemId) && arr_token[1].Equals(DeviceId))

                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("DelVehIuprmFrzNcs", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@SRNumber", SRNumber);
                        result = sqlCommand.ExecuteNonQuery();
                        sqlConnection.Close();
                        if (result > 0)
                        {
                            msg = "success";
                        }
                        else
                        {
                            msg = "No Data";
                        }

                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }
        }

        public string DELIUPRMFRZMM(string SRNumber, string SystemId, string DeviceId, DateTime DateTimeClient, string Token)
        {
            if (SRNumber == null || SRNumber == "" || SystemId == null || SystemId == "" || DeviceId == null || DeviceId == "" || DateTimeClient == null || Token == null || Token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(Token).Trim();
                    arr_token = str_token.Split(',');
                    int result = 0;
                   
                    if (arr_token[0].Equals(SystemId) && arr_token[1].Equals(DeviceId))

                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("DelVehIuprmFrzMM", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@SRNumber", SRNumber);
                        result = sqlCommand.ExecuteNonQuery();
                        sqlConnection.Close();
                        if (result > 0)
                        {
                            msg = "success";
                        }
                        else
                        {
                            msg = "No Data";
                        }

                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }
        }

        public string DELIUPRMFRZKEYHIN(string SRNumber, string SystemId, string DeviceId, DateTime DateTimeClient, string Token)
        {
            if (SRNumber == null || SRNumber == "" || SystemId == null || SystemId == "" || DeviceId == null || DeviceId == "" || DateTimeClient == null || Token == null || Token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(Token).Trim();
                    arr_token = str_token.Split(',');
                    int result = 0;
                    
                    if (arr_token[0].Equals(SystemId) && arr_token[1].Equals(DeviceId))

                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("DelVehIuprmFrzKehin", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@SRNumber", SRNumber);
                        result = sqlCommand.ExecuteNonQuery();
                        sqlConnection.Close();
                        if (result > 0)
                        {
                            msg = "success";
                        }
                        else
                        {
                            msg = "No Data";
                        }

                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }
        }
        

        public string INSERTIUPRMDTC(string Calid, string Cvn, string Vin, string No_Of_Err, string DTC, string Dev_Type, string IUPRMFlag, string SRNumber, string NatureOfJob, string StatusMessage, string CurrentKM, string VehicleSaleDate, string StatusCode, string SRId, string ECUNumber, string JobCardNum, string ManufactureYear, string TCUNumber, string LevelOfService, string ManufactureMonth, string VehicleModel,string DelCode, string DelLoc,string SystemId, string DeviceId, DateTime DateTimeClient, string Token)
        {
            if (Calid == null || Calid == "" || Cvn == null || Cvn == "" || Vin == null || Vin == "" || SystemId == null || SystemId == "" || DeviceId == null || DeviceId == "" || DateTimeClient == null || Token == null || Token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(Token).Trim();
                    arr_token = str_token.Split(',');
                    int result = 0;
                    
                    if (arr_token[0].Equals(SystemId) && arr_token[1].Equals(DeviceId))

                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("InsertVehIuprmDtc", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Calid", Calid);
                        sqlCommand.Parameters.AddWithValue("@Cvn", Cvn);
                        sqlCommand.Parameters.AddWithValue("@Vin", Vin);
                        sqlCommand.Parameters.AddWithValue("@No_Of_Errors", No_Of_Err);
                        sqlCommand.Parameters.AddWithValue("@DTC", DTC);
                        sqlCommand.Parameters.AddWithValue("@Dev_Type", Dev_Type);
                        sqlCommand.Parameters.AddWithValue("@IUPRMFlag", IUPRMFlag);
                        sqlCommand.Parameters.AddWithValue("@SRNumber", SRNumber);
                        sqlCommand.Parameters.AddWithValue("@NatureOfJob", NatureOfJob);
                        sqlCommand.Parameters.AddWithValue("@StatusMessage", StatusMessage);
                        sqlCommand.Parameters.AddWithValue("@CurrentKM", CurrentKM);
                        sqlCommand.Parameters.AddWithValue("@VehSaleDate", VehicleSaleDate);
                        sqlCommand.Parameters.AddWithValue("@StatusCode", StatusCode);
                        sqlCommand.Parameters.AddWithValue("@SRId", SRId);
                        sqlCommand.Parameters.AddWithValue("@ECUNumber", ECUNumber);
                        sqlCommand.Parameters.AddWithValue("@JobCardNum", JobCardNum);
                        sqlCommand.Parameters.AddWithValue("@ManufactureYear", ManufactureYear);
                        sqlCommand.Parameters.AddWithValue("@TCUNumber", TCUNumber);
                        sqlCommand.Parameters.AddWithValue("@LvlofService", LevelOfService);
                        sqlCommand.Parameters.AddWithValue("@ManufactureMonth", ManufactureMonth);
                        sqlCommand.Parameters.AddWithValue("@VehModel", VehicleModel);
                        sqlCommand.Parameters.AddWithValue("@Del_id", DelCode);
                        sqlCommand.Parameters.AddWithValue("@Del_Loc", DelLoc);




                        result = sqlCommand.ExecuteNonQuery();
                        sqlConnection.Close();
                        if (result > 0)
                        {
                            msg = "success";
                        }
                        else
                        {
                            msg = "fail";
                        }

                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }
        }
        
        public string GetDel_Loc(string DelCode, string SystemId, string DeviceId, DateTime DateTimeClient, string Token)
        {
            if (DelCode == null || DelCode == "" || SystemId == null || SystemId == "" || DeviceId == null || DeviceId == "" || DateTimeClient == null || Token == null || Token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(Token).Trim();
                    arr_token = str_token.Split(',');
                    int result = 0;
                    
                    if (arr_token[0].Equals(SystemId) && arr_token[1].Equals(DeviceId))

                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("Get_DelLoc", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@DST",DelCode);
                        result = sqlCommand.ExecuteNonQuery();
                        sqlConnection.Close();
                        if (result > 0)
                        {
                            msg = "success";
                        }
                        else
                        {
                            msg = "fail";
                        }

                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }
        }



        public string INSERTVEHIUPRMDATAJSON(string Calid, string Cvn, string Vin, string Ignition_Counter, string Denominator_Gen, string Denominator_Evap, string Numerator_Evap, string Denominator_O2Sensor, string Numerator_O2Sensor, string Denominator_Catalyst, string Numerator_Catalyst, string Denominator_O2Sensor_Secondary, string Numerator_O2Sensor_Secondary, string MIL_Status, string Distance_Travelled_Since_MIL, string Dev_Type, string IUPRMFlag, string SRNumber, string NatureOfJob, string StatusMessage, string CurrentKM, string VehicleSaleDate, string StatusCode, string SRId, string ECUNumber, string JobCardNum, string ManufactureYear, string TCUNumber, string LevelOfService, string ManufactureMonth, string VehicleModel, string DelCode, string DelLoc, string Jsonstring, string RawCanData, string SystemId, string DeviceId, DateTime DateTimeClient, string Token)
        {
            if (Calid == null || Calid == "" || Cvn == null || Cvn == "" || Vin == null || Vin == "" || SystemId == null || SystemId == "" || DeviceId == null || DeviceId == "" || DateTimeClient == null || Token == null || Token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(Token).Trim();
                    arr_token = str_token.Split(',');
                    int result = 0;

                    if (arr_token[0].Equals(SystemId) && arr_token[1].Equals(DeviceId))

                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("InsertVehIuprmDataJson", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Calid", Calid);
                        sqlCommand.Parameters.AddWithValue("@Cvn", Cvn);
                        sqlCommand.Parameters.AddWithValue("@Vin", Vin);
                        sqlCommand.Parameters.AddWithValue("@Ignition_Counter", Ignition_Counter);
                        sqlCommand.Parameters.AddWithValue("@Denominator_Gen", Denominator_Gen);
                        sqlCommand.Parameters.AddWithValue("@Denominator_Evap", Denominator_Evap);
                        sqlCommand.Parameters.AddWithValue("@Numerator_Evap", Numerator_Evap);
                        sqlCommand.Parameters.AddWithValue("@Denominator_O2Sensor", Denominator_O2Sensor);
                        sqlCommand.Parameters.AddWithValue("@Numerator_O2Sensor", Numerator_O2Sensor);
                        sqlCommand.Parameters.AddWithValue("@Denominator_Catalyst", Denominator_Catalyst);
                        sqlCommand.Parameters.AddWithValue("@Numerator_Catalyst", Numerator_Catalyst);
                        sqlCommand.Parameters.AddWithValue("@Denominator_O2Sensor_Secondary", Denominator_O2Sensor_Secondary);
                        sqlCommand.Parameters.AddWithValue("@Numerator_O2Sensor_Secondary", Numerator_O2Sensor_Secondary);
                        sqlCommand.Parameters.AddWithValue("@MIL_Status", MIL_Status);
                        sqlCommand.Parameters.AddWithValue("@Distance_Travelled_Since_MIL", Distance_Travelled_Since_MIL);
                        sqlCommand.Parameters.AddWithValue("@Dev_Type", Dev_Type);
                        sqlCommand.Parameters.AddWithValue("@IUPRMFlag", IUPRMFlag);
                        sqlCommand.Parameters.AddWithValue("@SRNumber", SRNumber);
                        sqlCommand.Parameters.AddWithValue("@NatureOfJob", NatureOfJob);
                        sqlCommand.Parameters.AddWithValue("@StatusMessage", StatusMessage);
                        sqlCommand.Parameters.AddWithValue("@CurrentKM", CurrentKM);
                        sqlCommand.Parameters.AddWithValue("@VehSaleDate", VehicleSaleDate);
                        sqlCommand.Parameters.AddWithValue("@StatusCode", StatusCode);
                        sqlCommand.Parameters.AddWithValue("@SRId", SRId);
                        sqlCommand.Parameters.AddWithValue("@ECUNumber", ECUNumber);
                        sqlCommand.Parameters.AddWithValue("@JobCardNum", JobCardNum);
                        sqlCommand.Parameters.AddWithValue("@ManufactureYear", ManufactureYear);
                        sqlCommand.Parameters.AddWithValue("@TCUNumber", TCUNumber);
                        sqlCommand.Parameters.AddWithValue("@LvlofService", LevelOfService);
                        sqlCommand.Parameters.AddWithValue("@ManufactureMonth", ManufactureMonth);
                        sqlCommand.Parameters.AddWithValue("@VehModel", VehicleModel);
                        sqlCommand.Parameters.AddWithValue("@Del_id", DelCode);
                        sqlCommand.Parameters.AddWithValue("@Del_Loc", DelLoc);
                        sqlCommand.Parameters.AddWithValue("@Json", Jsonstring);
                        sqlCommand.Parameters.AddWithValue("@RawCanData", RawCanData);




                        result = sqlCommand.ExecuteNonQuery();
                        sqlConnection.Close();
                        if (result > 0)
                        {
                            msg = "success";
                        }
                        else
                        {
                            msg = "fail";
                        }

                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }
        }


        public string INSERTVEHIUPRMDATA(string Calid, string Cvn, string Vin, string Ignition_Counter, string Denominator_Gen, string Denominator_Evap, string Numerator_Evap, string Denominator_O2Sensor, string Numerator_O2Sensor, string Denominator_Catalyst, string Numerator_Catalyst, string Denominator_O2Sensor_Secondary, string Numerator_O2Sensor_Secondary, string MIL_Status, string Distance_Travelled_Since_MIL, string Dev_Type, string IUPRMFlag, string SRNumber, string NatureOfJob, string StatusMessage, string CurrentKM, string VehicleSaleDate, string StatusCode, string SRId, string ECUNumber, string JobCardNum, string ManufactureYear, string TCUNumber, string LevelOfService, string ManufactureMonth, string VehicleModel, string DelCode, string DelLoc, string SystemId, string DeviceId, DateTime DateTimeClient, string Token)
        {
            if (Calid == null || Calid == "" || Cvn == null || Cvn == "" || Vin == null || Vin == "" || SystemId == null || SystemId == "" || DeviceId == null || DeviceId == "" || DateTimeClient == null || Token == null || Token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(Token).Trim();
                    arr_token = str_token.Split(',');
                    int result = 0;
                    
                    if (arr_token[0].Equals(SystemId) && arr_token[1].Equals(DeviceId))

                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("InsertVehIuprmData", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Calid", Calid);
                        sqlCommand.Parameters.AddWithValue("@Cvn", Cvn);
                        sqlCommand.Parameters.AddWithValue("@Vin", Vin);
                        sqlCommand.Parameters.AddWithValue("@Ignition_Counter", Ignition_Counter);
                        sqlCommand.Parameters.AddWithValue("@Denominator_Gen", Denominator_Gen);
                        sqlCommand.Parameters.AddWithValue("@Denominator_Evap", Denominator_Evap);
                        sqlCommand.Parameters.AddWithValue("@Numerator_Evap", Numerator_Evap);
                        sqlCommand.Parameters.AddWithValue("@Denominator_O2Sensor", Denominator_O2Sensor);
                        sqlCommand.Parameters.AddWithValue("@Numerator_O2Sensor", Numerator_O2Sensor);
                        sqlCommand.Parameters.AddWithValue("@Denominator_Catalyst", Denominator_Catalyst);
                        sqlCommand.Parameters.AddWithValue("@Numerator_Catalyst", Numerator_Catalyst);
                        sqlCommand.Parameters.AddWithValue("@Denominator_O2Sensor_Secondary", Denominator_O2Sensor_Secondary);
                        sqlCommand.Parameters.AddWithValue("@Numerator_O2Sensor_Secondary", Numerator_O2Sensor_Secondary);
                        sqlCommand.Parameters.AddWithValue("@MIL_Status", MIL_Status);
                        sqlCommand.Parameters.AddWithValue("@Distance_Travelled_Since_MIL", Distance_Travelled_Since_MIL);
                        sqlCommand.Parameters.AddWithValue("@Dev_Type", Dev_Type);
                        sqlCommand.Parameters.AddWithValue("@IUPRMFlag", IUPRMFlag);
                        sqlCommand.Parameters.AddWithValue("@SRNumber", SRNumber);
                        sqlCommand.Parameters.AddWithValue("@NatureOfJob", NatureOfJob);
                        sqlCommand.Parameters.AddWithValue("@StatusMessage", StatusMessage);
                        sqlCommand.Parameters.AddWithValue("@CurrentKM", CurrentKM);
                        sqlCommand.Parameters.AddWithValue("@VehSaleDate", VehicleSaleDate);
                        sqlCommand.Parameters.AddWithValue("@StatusCode", StatusCode);
                        sqlCommand.Parameters.AddWithValue("@SRId", SRId);
                        sqlCommand.Parameters.AddWithValue("@ECUNumber", ECUNumber);
                        sqlCommand.Parameters.AddWithValue("@JobCardNum", JobCardNum);
                        sqlCommand.Parameters.AddWithValue("@ManufactureYear", ManufactureYear);
                        sqlCommand.Parameters.AddWithValue("@TCUNumber", TCUNumber);
                        sqlCommand.Parameters.AddWithValue("@LvlofService", LevelOfService);
                        sqlCommand.Parameters.AddWithValue("@ManufactureMonth", ManufactureMonth);
                        sqlCommand.Parameters.AddWithValue("@VehModel", VehicleModel);
                        sqlCommand.Parameters.AddWithValue("@Del_id", DelCode);
                        sqlCommand.Parameters.AddWithValue("@Del_Loc", DelLoc);



                        result = sqlCommand.ExecuteNonQuery();
                        sqlConnection.Close();
                        if (result > 0)
                        {
                            msg = "success";
                        }
                        else
                        {
                            msg = "fail";
                        }

                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }
        }

        

        public string INSERTVEHIUPRMKEHIN(string Calid, string Cvn, string Vin, string DTC, string ECU, string Short_Term_Fuel_Trim_1, string Manifold_Absolute_Pressure, string Engine_speed, string Vehicle_Speed, string Ignition_Timing, string Intake_Air_Temperature, string Absolute_throttle_position, string Relative_throttle_position, string Engine_Oil_Temperature, string Dev_Type, string SRNumber, string CurrentKM, string StatusCode, string SRId, string ECUNumber, string JobCardNum, string ManufactureYear, string TCUNumber, string ManufactureMonth, string VehicleModel, string DelCode, string DelLoc, string SystemId, string DeviceId, DateTime DateTimeClient, string Token)
        {
            if (Calid == null || Calid == "" || Cvn == null || Cvn == "" || Vin == null || Vin == "" || SystemId == null || SystemId == "" || DeviceId == null || DeviceId == "" || DateTimeClient == null || Token == null || Token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(Token).Trim();
                    arr_token = str_token.Split(',');
                    int result = 0;
                   
                    if (arr_token[0].Equals(SystemId) && arr_token[1].Equals(DeviceId))

                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("InsertVehIuprmKeyhin", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Calid", Calid);
                        sqlCommand.Parameters.AddWithValue("@Cvn", Cvn);
                        sqlCommand.Parameters.AddWithValue("@Vin", Vin);
                        sqlCommand.Parameters.AddWithValue("@DTC", DTC);
                        sqlCommand.Parameters.AddWithValue("@ECU", ECU);

                        sqlCommand.Parameters.AddWithValue("@Short_Term_Fuel_Trim_1", Short_Term_Fuel_Trim_1);
                        sqlCommand.Parameters.AddWithValue("@Manifold_Absolute_Pressure", Manifold_Absolute_Pressure);
                        sqlCommand.Parameters.AddWithValue("@Engine_speed", Engine_speed);
                        sqlCommand.Parameters.AddWithValue("@Vehicle_Speed", Vehicle_Speed);
                        sqlCommand.Parameters.AddWithValue("@Ignition_Timing", Ignition_Timing);
                        sqlCommand.Parameters.AddWithValue("@Intake_Air_Temperature", Intake_Air_Temperature);
                        sqlCommand.Parameters.AddWithValue("@Absolute_throttle_position", Absolute_throttle_position);
                        sqlCommand.Parameters.AddWithValue("@Relative_throttle_position", Relative_throttle_position);
                        sqlCommand.Parameters.AddWithValue("@Engine_Oil_Temperature", Engine_Oil_Temperature);
                        sqlCommand.Parameters.AddWithValue("@Dev_Type", Dev_Type);

                        sqlCommand.Parameters.AddWithValue("@SRNumber", SRNumber);
                        sqlCommand.Parameters.AddWithValue("@CurrentKM", CurrentKM);
                        sqlCommand.Parameters.AddWithValue("@StatusCode", StatusCode);
                        sqlCommand.Parameters.AddWithValue("@SRId", SRId);

                        sqlCommand.Parameters.AddWithValue("@ECUNumber", ECUNumber);
                        sqlCommand.Parameters.AddWithValue("@JobCardNum", JobCardNum);
                        sqlCommand.Parameters.AddWithValue("@ManufactureYear", ManufactureYear);
                        sqlCommand.Parameters.AddWithValue("@TCUNumber", TCUNumber);
                        sqlCommand.Parameters.AddWithValue("@ManufactureMonth", ManufactureMonth);
                        sqlCommand.Parameters.AddWithValue("@VehicleModel", VehicleModel);
                        sqlCommand.Parameters.AddWithValue("@Del_id", DelCode);
                        sqlCommand.Parameters.AddWithValue("@Del_Loc", DelLoc);




                        result = sqlCommand.ExecuteNonQuery();
                        sqlConnection.Close();
                        if (result > 0)
                        {
                            msg = "success";
                        }
                        else
                        {
                            msg = "fail";
                        }

                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }
        }

        

        public string INSERTVEHIUPRMMM(string Calid, string Cvn, string Vin, string DTC, string ECU, string Oxygen_Sensor1_Mixture_Control_Status, string Calculated_load_value_CLV, string Engine_Temperature, string Manifold_Pressure, string Engine_Speed, string Vehicle_Speed, string Applied_Spark_Advance, string Air_Temperature, string Throttle_position_calculated, string Slider_Sensor_Position, string Injection_Time, string Final_fueling_qty, string Oxygen_Sensor1_Control_Gain_Value, string ECU_Battery_voltage, string Oxygen_Sensor1_Voltage, string Duty_Cycle_Canister, string Atmospheric_Pressure, string Gear_Inserted_Neutral, string Crankshaft_Flywheel_State, string Side_Stand_State, string Clutch_State, string Run_Stop_Switch_State, string Spark_Ignition_Enable, string Selfadapt_strategy_calculation_state, string Engine_State, string Engine_Mode, string Slider_Status, string Selfadaptive_Fuel_Correction, string Distance_Travelled_Since_MIL_ON, string Time_Since_MIL_ON, string Dev_Type, string SRNumber, string CurrentKM, string StatusCode, string SRId, string ECUNumber, string JobCardNum, string ManufactureYear, string TCUNumber, string ManufactureMonth, string VehicleModel,string DelCode, string DelLoc, string SystemId, string DeviceId, DateTime DateTimeClient, string Token)
        {
            if (Calid == null || Calid == "" || Cvn == null || Cvn == "" || Vin == null || Vin == "" || SystemId == null || SystemId == "" || DeviceId == null || DeviceId == "" || DateTimeClient == null || Token == null || Token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(Token).Trim();
                    arr_token = str_token.Split(',');
                    int result = 0;
                    
                    if (arr_token[0].Equals(SystemId) && arr_token[1].Equals(DeviceId))

                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("InsertVehIuprmMm", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Calid", Calid);
                        sqlCommand.Parameters.AddWithValue("@Cvn", Cvn);
                        sqlCommand.Parameters.AddWithValue("@Vin", Vin);
                        sqlCommand.Parameters.AddWithValue("@DTC", DTC);
                        sqlCommand.Parameters.AddWithValue("@ECU", ECU);
                        sqlCommand.Parameters.AddWithValue("@Oxygen_Sensor1_Mixture_Control_Status", Oxygen_Sensor1_Mixture_Control_Status);
                        sqlCommand.Parameters.AddWithValue("@Calculated_load_value_CLV", Calculated_load_value_CLV);
                        sqlCommand.Parameters.AddWithValue("@Engine_Temperature", Engine_Temperature);
                        sqlCommand.Parameters.AddWithValue("@Manifold_Pressure", Manifold_Pressure);
                        sqlCommand.Parameters.AddWithValue("@Engine_Speed", Engine_Speed);
                        sqlCommand.Parameters.AddWithValue("@Vehicle_Speed", Vehicle_Speed);
                        sqlCommand.Parameters.AddWithValue("@Applied_Spark_Advance", Applied_Spark_Advance);
                        sqlCommand.Parameters.AddWithValue("@Air_Temperature", Air_Temperature);
                        sqlCommand.Parameters.AddWithValue("@Throttle_position_calculated", Throttle_position_calculated);
                        sqlCommand.Parameters.AddWithValue("@Slider_Sensor_Position", Slider_Sensor_Position);

                        sqlCommand.Parameters.AddWithValue("@Injection_Time", Injection_Time);
                        sqlCommand.Parameters.AddWithValue("@Final_fueling_qty", Final_fueling_qty);
                        sqlCommand.Parameters.AddWithValue("@Oxygen_Sensor1_Control_Gain_Value", Oxygen_Sensor1_Control_Gain_Value);
                        sqlCommand.Parameters.AddWithValue("@ECU_Battery_voltage", ECU_Battery_voltage);
                        sqlCommand.Parameters.AddWithValue("@Oxygen_Sensor1_Voltage", Oxygen_Sensor1_Voltage);
                        sqlCommand.Parameters.AddWithValue("@Duty_Cycle_Canister", Duty_Cycle_Canister);
                        sqlCommand.Parameters.AddWithValue("@Atmospheric_Pressure", Atmospheric_Pressure);
                        sqlCommand.Parameters.AddWithValue("@Gear_Inserted_Neutral", Gear_Inserted_Neutral);
                        sqlCommand.Parameters.AddWithValue("@Crankshaft_Flywheel_State", Crankshaft_Flywheel_State);
                        sqlCommand.Parameters.AddWithValue("@Side_Stand_State", Side_Stand_State);

                        sqlCommand.Parameters.AddWithValue("@Clutch_State", Clutch_State);
                        sqlCommand.Parameters.AddWithValue("@Run_Stop_Switch_State", Run_Stop_Switch_State);
                        sqlCommand.Parameters.AddWithValue("@Spark_Ignition_Enable", Spark_Ignition_Enable);
                        sqlCommand.Parameters.AddWithValue("@Selfadapt_strategy_calculation_state", Selfadapt_strategy_calculation_state);
                        sqlCommand.Parameters.AddWithValue("@Engine_State", Engine_State);

                        sqlCommand.Parameters.AddWithValue("@Engine_Mode", Engine_Mode);
                        sqlCommand.Parameters.AddWithValue("@Slider_Status", Slider_Status);
                        sqlCommand.Parameters.AddWithValue("@Selfadaptive_Fuel_Correction", Selfadaptive_Fuel_Correction);
                        sqlCommand.Parameters.AddWithValue("@Distance_Travelled_Since_MIL_ON", Distance_Travelled_Since_MIL_ON);

                        sqlCommand.Parameters.AddWithValue("@Time_Since_MIL_ON", Time_Since_MIL_ON);

                        sqlCommand.Parameters.AddWithValue("@Dev_Type", Dev_Type);

                        sqlCommand.Parameters.AddWithValue("@SRNumber", SRNumber);
                        sqlCommand.Parameters.AddWithValue("@CurrentKM", CurrentKM);
                        sqlCommand.Parameters.AddWithValue("@StatusCode", StatusCode);
                        sqlCommand.Parameters.AddWithValue("@SRId", SRId);

                        sqlCommand.Parameters.AddWithValue("@ECUNumber", ECUNumber);
                        sqlCommand.Parameters.AddWithValue("@JobCardNum", JobCardNum);
                        sqlCommand.Parameters.AddWithValue("@ManufactureYear", ManufactureYear);
                        sqlCommand.Parameters.AddWithValue("@TCUNumber", TCUNumber);
                        sqlCommand.Parameters.AddWithValue("@ManufactureMonth", ManufactureMonth);
                        sqlCommand.Parameters.AddWithValue("@VehicleModel", VehicleModel);
                        sqlCommand.Parameters.AddWithValue("@Del_id", DelCode);
                        sqlCommand.Parameters.AddWithValue("@Del_Loc", DelLoc);




                        result = sqlCommand.ExecuteNonQuery();
                        sqlConnection.Close();
                        if (result > 0)
                        {
                            msg = "success";
                        }
                        else
                        {
                            msg = "fail";
                        }

                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }
        }

        public string INSERTVEHIUPRMNCS(string Calid, string Cvn, string Vin, string DTC, string ECU, string LS_Exbhk_0_State, string LS_Exbhk_1_State, string Calculated_Load_Value, string Coolant_Temperature, string Lambda_control_injection_time_correction_Cyl_Bank_1, string Relative_additive_adaptation_factor_exhaust_Bank_1, string Lambda_control_injection_time_correction_Cyl_Bank_2, string Relative_additive_adaptation_factor_exhaust_Bank_Bank_2, string Intake_manifold_Air_Pressure, string Engine_Speed, string Vehicle_Speed, string Ignition_Time_Advance, string Intake_Air_Temperature, string Mass_Air_Flow, string Absolute_Throttle_position, string Dev_Type, string SRNumber, string CurrentKM, string StatusCode, string SRId, string ECUNumber, string JobCardNum, string ManufactureYear, string TCUNumber, string ManufactureMonth, string VehicleModel, string DelCode, string DelLoc, string SystemId, string DeviceId, DateTime DateTimeClient, string Token)
        {
            if (Calid == null || Calid == "" || Cvn == null || Cvn == "" || Vin == null || Vin == "" || SystemId == null || SystemId == "" || DeviceId == null || DeviceId == "" || DateTimeClient == null || Token == null || Token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {
                    str_token = Decrypt(Token).Trim();
                    arr_token = str_token.Split(',');
                    int result = 0;
                    
                    if (arr_token[0].Equals(SystemId) && arr_token[1].Equals(DeviceId))

                    {
                        DateTime dateTime = DateTime.Now;
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("InsertVehIuprmNcs", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Calid", Calid);
                        sqlCommand.Parameters.AddWithValue("@Cvn", Cvn);
                        sqlCommand.Parameters.AddWithValue("@Vin", Vin);
                        sqlCommand.Parameters.AddWithValue("@DTC", DTC);
                        sqlCommand.Parameters.AddWithValue("@ECU", ECU);
                        sqlCommand.Parameters.AddWithValue("@LS_Exbhk_0_State", LS_Exbhk_0_State);
                        sqlCommand.Parameters.AddWithValue("@LS_Exbhk_1_State", LS_Exbhk_1_State);
                        sqlCommand.Parameters.AddWithValue("@Calculated_Load_Value", Calculated_Load_Value);
                        sqlCommand.Parameters.AddWithValue("@Coolant_Temperature", Coolant_Temperature);
                        sqlCommand.Parameters.AddWithValue("@Lambda_control_injection_time_correction_Cyl_Bank_1", Lambda_control_injection_time_correction_Cyl_Bank_1);
                        sqlCommand.Parameters.AddWithValue("@Relative_additive_adaptation_factor_exhaust_Bank_1", Relative_additive_adaptation_factor_exhaust_Bank_1);
                        sqlCommand.Parameters.AddWithValue("@Lambda_control_injection_time_correction_Cyl_Bank_2", Lambda_control_injection_time_correction_Cyl_Bank_2);
                        sqlCommand.Parameters.AddWithValue("@Relative_additive_adaptation_factor_exhaust_Bank_Bank_2", Relative_additive_adaptation_factor_exhaust_Bank_Bank_2);
                        sqlCommand.Parameters.AddWithValue("@Intake_manifold_Air_Pressure", Intake_manifold_Air_Pressure);
                        sqlCommand.Parameters.AddWithValue("@Engine_Speed", Engine_Speed);

                        sqlCommand.Parameters.AddWithValue("@Vehicle_Speed", Vehicle_Speed);
                        sqlCommand.Parameters.AddWithValue("@Ignition_Time_Advance", Ignition_Time_Advance);
                        sqlCommand.Parameters.AddWithValue("@Intake_Air_Temperature", Intake_Air_Temperature);
                        sqlCommand.Parameters.AddWithValue("@Mass_Air_Flow", Mass_Air_Flow);
                        sqlCommand.Parameters.AddWithValue("@Absolute_Throttle_position", Absolute_Throttle_position);
                        sqlCommand.Parameters.AddWithValue("@Dev_Type", Dev_Type);

                        sqlCommand.Parameters.AddWithValue("@SRNumber", SRNumber);
                        sqlCommand.Parameters.AddWithValue("@CurrentKM", CurrentKM);
                        sqlCommand.Parameters.AddWithValue("@StatusCode", StatusCode);
                        sqlCommand.Parameters.AddWithValue("@SRId", SRId);

                        sqlCommand.Parameters.AddWithValue("@ECUNumber", ECUNumber);
                        sqlCommand.Parameters.AddWithValue("@JobCardNum", JobCardNum);
                        sqlCommand.Parameters.AddWithValue("@ManufactureYear", ManufactureYear);
                        sqlCommand.Parameters.AddWithValue("@TCUNumber", TCUNumber);
                        sqlCommand.Parameters.AddWithValue("@ManufactureMonth", ManufactureMonth);
                        sqlCommand.Parameters.AddWithValue("@VehicleModel", VehicleModel);
                        sqlCommand.Parameters.AddWithValue("@Del_id", DelCode);
                        sqlCommand.Parameters.AddWithValue("@Del_Loc", DelLoc);



                        result = sqlCommand.ExecuteNonQuery();
                        sqlConnection.Close();
                        if (result > 0)
                        {
                            msg = "success";
                        }
                        else
                        {
                            msg = "fail";
                        }

                    }
                }
                catch (Exception ex) { return "fail"; }
                return msg;
            }
        }
        public string GetEolFiSysEmpDetails(string Emp_Code, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            if (Emp_Code == null || Emp_Code == "" || token == null || token == "")
            {
                return "fail_null";
            }
            else
            {
                string msg = "", str_token = "";
                string[] arr_token;
                try
                {

                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                    {
                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        SqlCommand sqlCommand = new SqlCommand("GetEolFiSysEmpDetails", sqlConnection);
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlConnection.Open();
                        sqlCommand.Parameters.AddWithValue("@Code", Emp_Code);
                        using (SqlDataReader reader = sqlCommand.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                msg = "success";
                                


                            }
                            else
                            {
                                msg = "no_data";

                            }
                            reader.Close();
                        }
                    }
                    else
                    {
                        msg = "fail_token";
                    }
                }
                catch (Exception ex)
                {
                    return "fail_ex";
                }
                return msg;
            }
        }

        public byte[] GetDll(string File_Name, string systemId, string deviceId, DateTime dateTimeClient, string token)
        {
            byte[] byte_arr;
            string str_token = "";
            string[] arr_token;
            if (File_Name == null || File_Name == "" || systemId == null || systemId == "" || deviceId == null || deviceId == "" || dateTimeClient == null || token == null || token == "")
            {
                byte_arr = new byte[1];
                return byte_arr;
            }
            else
            {
                try
                {
                    str_token = Decrypt(token).Trim();
                    arr_token = str_token.Split(',');
                }
                catch (Exception ex)
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
                if (arr_token[0].Equals(systemId) && arr_token[1].Equals(deviceId))
                {
                    try
                    {

                        SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString());
                        sqlConnection.Open();
                        using (var sqlQuery = new SqlCommand("SELECT DLL_DATA FROM DLLFILE_DATA WHERE DLL_NAME = @DllRestSharp", sqlConnection))
                        {
                            sqlQuery.Parameters.AddWithValue("@DllRestSharp", File_Name.Trim());
                           
                            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                                if (sqlQueryResult != null)
                                {
                                    sqlQueryResult.Read();
                                    byte_arr = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];
                                    sqlQueryResult.GetBytes(0, 0, byte_arr, 0, byte_arr.Length);
                                    sqlConnection.Close();
                                    return byte_arr;
                                }
                                else
                                {
                                    sqlConnection.Close();
                                    byte_arr = new byte[1];
                                    return byte_arr;
                                }
                        }
                    }
                    catch (Exception ex)
                    {
                        byte_arr = new byte[1];
                        return byte_arr;
                    }
                }
                else
                {
                    byte_arr = new byte[1];
                    return byte_arr;
                }
            }
        }

        //Services Required for HERO NextGen Software
        public string GetFileVersionFromServer(string SystemId, string DeviceId, DateTime DateTimeClient, string Token)
        {
            string msg = "";
            try
            {
                if (SystemId == null || SystemId == "" || DeviceId == null || DeviceId == "" || DateTimeClient == null || Token == null || Token == "")
                {
                    msg = "fail_null";
                }
                else
                {
                    string[] arr_token;
                    string str_token = Decrypt(Token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(SystemId) && arr_token[1].Equals(DeviceId))
                    {
                        using (SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString()))
                        {
                            sqlConnection.Open();
                            SqlCommand sqlCommand = new SqlCommand("GetHeroEolFirmwareVersion", sqlConnection);
                            sqlCommand.CommandType = CommandType.StoredProcedure;
                            sqlCommand.Parameters.AddWithValue("@type", "data");
                            using (SqlDataReader reader = sqlCommand.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        msg = reader.GetString(0);
                                    }
                                }
                                else
                                {
                                    msg = "fail";
                                }
                                reader.Close();
                                
                            }
                        }
                    }
                    else
                    {
                        msg = "fail_token";
                    }   
                }
                
            }
            catch(Exception ex)
            {
                msg = ex.Message + ex.ToString();
                //msg = "fail_ex";
            }
            return msg;
        }
        public byte[] GetNextGenBinFile(string SystemId, string DeviceId, DateTime DateTimeClient, string Token, string Version)
        {
            byte[] binaryData = null;
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString()))
                {
                    sqlConnection.Open(); 
                    using (SqlCommand command = new SqlCommand("SELECT binfile,filename FROM Hero_Eol_Cdc_Vci_Firmware WHERE type = @Type AND version = @Version order by version desc", sqlConnection))
                    {
                        command.Parameters.AddWithValue("@Version", Version);
                        command.Parameters.AddWithValue("@type", "data");
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read() && !reader.IsDBNull(0))
                            {
                                binaryData = (byte[])reader[0];
                                //file_name = (string)reader[1];
                            }
                            else
                            {
                                binaryData = new byte[1];
                            }
                        }
                    }
                }
            }
            catch
            {
                binaryData = new byte[1];
            }
            return binaryData;
        }

        public string GetGen2FileVersionFromServer(string SystemId, string DeviceId, DateTime DateTimeClient, string Token)
        {
            string msg = "";
            try
            {
                if (SystemId == null || SystemId == "" || DeviceId == null || DeviceId == "" || DateTimeClient == null || Token == null || Token == "")
                {
                    msg = "fail_null";
                }
                else
                {
                    string[] arr_token;
                    string str_token = Decrypt(Token).Trim();
                    arr_token = str_token.Split(',');

                    if (arr_token[0].Equals(SystemId) && arr_token[1].Equals(DeviceId))
                    {
                        using (SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString()))
                        {
                            sqlConnection.Open();
                            SqlCommand sqlCommand = new SqlCommand("GetHeroGen2EolFirmwareVersion", sqlConnection);
                            sqlCommand.CommandType = CommandType.StoredProcedure;
                            sqlCommand.Parameters.AddWithValue("@type", "data");
                            using (SqlDataReader reader = sqlCommand.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        msg = reader.GetString(0);
                                    }
                                }
                                else
                                {
                                    msg = "fail";
                                }
                                reader.Close();
                            }
                        }
                    }
                    else
                    {
                        msg = "fail_token";
                    }
                }

            }
            catch (Exception ex)
            {
                msg = ex.Message + ex.ToString();                
            }
            return msg;
        }
        public byte[] GetGen2BinFile(string SystemId, string DeviceId, DateTime DateTimeClient, string Token, string Version)
        {
            byte[] binaryData = null;
            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["str_url"].ToString()))
                {
                    sqlConnection.Open();
                    using (SqlCommand command = new SqlCommand("SELECT binfile,filename FROM Hero_Gen2_Eol_Cdc_Vci_Firmware WHERE type = @Type AND version = @Version order by version desc", sqlConnection))
                    {
                        command.Parameters.AddWithValue("@Version", Version);
                        command.Parameters.AddWithValue("@type", "data");
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read() && !reader.IsDBNull(0))
                            {
                                binaryData = (byte[])reader[0];
                                //file_name = (string)reader[1];
                            }
                            else
                            {
                                binaryData = new byte[1];
                            }
                        }
                    }
                }
            }
            catch
            {
                binaryData = new byte[1];
            }
            return binaryData;
        }
    }
}
