Imports System Imports System.Web Imports System.Collections Imports System.Web.Services Imports System.Web.Services.Protocols Imports System.Data Imports System.Data.SqlClient Imports System.Web.Script.Serialization Imports System.Web.Script.Services Imports System.ComponentModel Imports Newtonsoft Imports Newtonsoft.Json Imports System.Net Imports System.IO Public Class JSON_DATA_SERVICE Inherits System.Web.Services.WebService Private DocumentFilePath As String = "~/documents/" Public Function Pernod_MASTER_SERVICE_spSel(ByVal obj As String) As String Dim sResponse As String = "" If Session("Key") <> "d68d82b2-b7c0-4b28-806f-1458501bd827" Then sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, "Invalid key"), Newtonsoft.Json.Formatting.Indented) Return sResponse End If Try Dim ValPairs As Dictionary(Of String, String) = JsonConvert.DeserializeObject(Of Dictionary(Of String, String))(obj) Using ds As New SqlDataSource(ConfigurationManager.ConnectionStrings("Pernod_ConnectionString").ConnectionString, "") ds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure For Each kvp As KeyValuePair(Of String, String) In JsonConvert.DeserializeObject(Of Dictionary(Of String, String))(obj) If kvp.Key = "spAlias" Then ds.SelectCommand = kvp.Value ElseIf kvp.Key.Contains("SESSION_") Then ds.SelectParameters.Add(kvp.Key.Replace("SESSION_", ""), Session(kvp.Value).ToString) ElseIf kvp.Value.StartsWith("SESSION@") Then ds.SelectParameters.Add(kvp.Key, Session(kvp.Value.Replace("SESSION@", "")).ToString) Else ds.SelectParameters.Add(kvp.Key, kvp.Value) End If Next Dim dv As DataView = ds.Select(New DataSourceSelectArguments) If Not dv Is Nothing Then Dim dt As DataTable = dv.Table If Not dt Is Nothing Then sResponse = Json.JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.Indented) End If End If End Using Catch ex As Exception sResponse = "" End Try Return sResponse End Function Public Function Pernod_MASTER_SERVICE_spUpd(ByVal obj As String) As String Dim sResponse As String = "" If Session("Key") <> "d68d82b2-b7c0-4b28-806f-1458501bd827" Then sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, "Invalid key"), Newtonsoft.Json.Formatting.Indented) Return sResponse End If Try Dim ValPairs As Dictionary(Of String, String) = JsonConvert.DeserializeObject(Of Dictionary(Of String, String))(obj) Using ds As New SqlDataSource(ConfigurationManager.ConnectionStrings("Pernod_ConnectionString").ConnectionString, "") ds.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure For Each kvp As KeyValuePair(Of String, String) In JsonConvert.DeserializeObject(Of Dictionary(Of String, String))(obj) If kvp.Key = "spAlias" Then ds.UpdateCommand = kvp.Value ElseIf kvp.Key.Contains("SESSION_") Then ds.UpdateParameters.Add(kvp.Key.Replace("SESSION_", ""), Session(kvp.Value).ToString) ElseIf kvp.Value.StartsWith("SESSION@") Then ds.SelectParameters.Add(kvp.Key, Session(kvp.Value.Replace("SESSION@", "")).ToString) Else ds.UpdateParameters.Add(kvp.Key, kvp.Value) End If ds.UpdateParameters.Item(ds.UpdateParameters.Count - 1).ConvertEmptyStringToNull = False Next Dim dv As DataView = ds.Select(New DataSourceSelectArguments) If Not dv Is Nothing Then Dim dt As DataTable = dv.Table If Not dt Is Nothing Then sResponse = Json.JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.Indented) End If End If If Not IsNothing(Session("USER_PK")) Then ds.UpdateParameters.Add("ACTION_USER_FK", Session("USER_PK")) End If ds.Update() sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(True, "Saved Successfully!"), Newtonsoft.Json.Formatting.Indented) End Using Catch ex As Exception sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, "An error has occurred: " & ex.Message), Newtonsoft.Json.Formatting.Indented) End Try Return sResponse End Function Public Function Pernod_MASTER_SERVICE(ByVal obj As String) As String Dim sResponse As String = "" If Session("Key") <> "d68d82b2-b7c0-4b28-806f-1458501bd827" Then sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, "Invalid key"), Newtonsoft.Json.Formatting.Indented) Return sResponse End If Dim dt As New DataTable Dim affectedRows As Integer = 0 Try Dim ValPairs As Dictionary(Of String, String) = JsonConvert.DeserializeObject(Of Dictionary(Of String, String))(obj) Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("Pernod_ConnectionString").ConnectionString) Using cmd As SqlCommand = New SqlCommand("", con) cmd.CommandType = CommandType.StoredProcedure For Each kvp As KeyValuePair(Of String, String) In JsonConvert.DeserializeObject(Of Dictionary(Of String, String))(obj) If kvp.Key = "spAlias" Then cmd.CommandText = kvp.Value ElseIf kvp.Key.Contains("SESSION_") Then cmd.Parameters.AddWithValue(kvp.Key.Replace("SESSION_", ""), Session(kvp.Value).ToString) ElseIf kvp.Value.StartsWith("SESSION@") Then cmd.Parameters.AddWithValue(kvp.Key, Session(kvp.Value.Replace("SESSION@", "")).ToString) ElseIf kvp.Value.StartsWith("IMAGE@") Then Dim imgFilename As String = Server.MapPath(kvp.Value.Replace("IMAGE@", "")) If IO.File.Exists(imgFilename) Then Dim imgBytes As Byte() = IO.File.ReadAllBytes(imgFilename) cmd.Parameters.AddWithValue(kvp.Key, imgBytes) End If Else cmd.Parameters.AddWithValue(kvp.Key, kvp.Value) End If Next If con.State = ConnectionState.Closed Then con.Open() Using da As SqlDataAdapter = New SqlDataAdapter(cmd) affectedRows = da.Fill(dt) End Using ' Handle email sending for specific stored procedures If cmd.CommandText = "USERS_spFORGOT" AndAlso affectedRows > 0 AndAlso dt.Rows.Count > 0 Then If Not dt.Columns(0).ColumnName = "ERROR" Then Try ' Send password email with credentials Dim userEmail As String = dt.Rows(0)("UserEmail").ToString() Dim userName As String = dt.Rows(0)("ContactFirstName").ToString() Dim password As String = dt.Rows(0)("Password").ToString() Dim websiteUrl As String = functions.GetAppSettings("WEBSITE") Dim loginUrl As String = websiteUrl & "/login.aspx" If EmailService.SendPasswordResetEmail(userEmail, userName, password, loginUrl) Then sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(True, "Password email has been sent."), Newtonsoft.Json.Formatting.Indented) Else sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, "Failed to send password email. Please try again later."), Newtonsoft.Json.Formatting.Indented) End If Catch ex As Exception sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, "An error occurred while sending the email: " & ex.Message), Newtonsoft.Json.Formatting.Indented) End Try Return sResponse End If ElseIf cmd.CommandText = "USERS_spREGISTER" AndAlso affectedRows > 0 AndAlso dt.Rows.Count > 0 Then If Not dt.Columns(0).ColumnName = "ERROR" Then Try ' Send activation email Dim userEmail As String = dt.Rows(0)("UserEmail").ToString() Dim firstName As String = dt.Rows(0)("FirstName").ToString() Dim lastName As String = dt.Rows(0)("LastName").ToString() Dim activationUrl As String = dt.Rows(0)("ActivationUrl").ToString() If EmailService.SendAccountActivationEmail(userEmail, activationUrl, firstName, lastName) Then sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(True, "Registration successful. Please check your email to activate your account."), Newtonsoft.Json.Formatting.Indented) Else sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, "Registration successful but failed to send activation email. Please contact support."), Newtonsoft.Json.Formatting.Indented) End If Catch ex As Exception sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, "An error occurred while sending the email: " & ex.Message), Newtonsoft.Json.Formatting.Indented) End Try Return sResponse End If ElseIf cmd.CommandText = "SHOPPING_CART_SpSend" AndAlso affectedRows > 0 AndAlso dt.Rows.Count > 0 Then If Not dt.Columns(0).ColumnName = "ERROR" Then Try ' Get order details Dim orderId As Integer = Integer.Parse(dt.Rows(0)("Orderid").ToString()) ' Get order header information Using con2 As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("Pernod_ConnectionString").ConnectionString) Using cmd2 As SqlCommand = New SqlCommand("SELECT H.systemordnumber, H.clientOrderNO, H.Orderdate, C.statusDescription, U.UserEmail, U.ContactFirstName " & "FROM ORDER_HEADER H " & "INNER JOIN USERS U ON U.userid = H.userid " & "INNER JOIN STATUS_CODES_HEADER C ON C.HeadStatusNo = H.status " & "WHERE H.OrderId = @OrderId", con2) cmd2.Parameters.AddWithValue("@OrderId", orderId) If con2.State = ConnectionState.Closed Then con2.Open() Using reader As SqlDataReader = cmd2.ExecuteReader() If reader.Read() Then ' Get order details Using con3 As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("Pernod_ConnectionString").ConnectionString) Using cmd3 As SqlCommand = New SqlCommand("SELECT D.product, ISNULL(D.DSVcode, D.IHScode) AS StockCode, D.Quantity, P.Price, D.Quantity * D.Price AS Total " & "FROM ORDER_DETAIL D " & "INNER JOIN PRODUCTS P ON P.DSVCode = D.DSVcode AND P.active = 1 " & "WHERE D.OrderId = @OrderId", con3) cmd3.Parameters.AddWithValue("@OrderId", orderId) If con3.State = ConnectionState.Closed Then con3.Open() Using reader2 As SqlDataReader = cmd3.ExecuteReader() Dim orderDetails As New StringBuilder() While reader2.Read() orderDetails.AppendLine("") orderDetails.AppendLine("" & reader2("product").ToString() & "") orderDetails.AppendLine("" & reader2("StockCode").ToString() & "") orderDetails.AppendLine("" & reader2("Quantity").ToString() & "") orderDetails.AppendLine("" & reader2("Price").ToString() & "") orderDetails.AppendLine("" & reader2("Total").ToString() & "") orderDetails.AppendLine("") End While ' Send order confirmation email If EmailService.SendOrderConfirmationEmail(reader("UserEmail").ToString(), reader("ContactFirstName").ToString(), reader("systemordnumber").ToString(), reader("clientOrderNO").ToString(), reader("Orderdate").ToString(), reader("statusDescription").ToString(), orderDetails.ToString()) Then sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(True, "Order placed successfully. Confirmation email has been sent."), Newtonsoft.Json.Formatting.Indented) Else ErrorLogger.Log("Order", "OrderSend", "OrderId " & orderId.ToString() & ": confirmation email send returned False", Nothing, reader("UserEmail").ToString()) sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, "Order placed successfully but failed to send confirmation email. Please contact support."), Newtonsoft.Json.Formatting.Indented) End If End Using End Using End Using End If End Using End Using End Using Catch ex As Exception Dim orderIdForLog As String = "" If Not IsNothing(dt) AndAlso dt.Rows.Count > 0 Then If dt.Columns.Contains("Orderid") Then orderIdForLog = dt.Rows(0)("Orderid").ToString() End If End If ErrorLogger.Log("Order", "OrderSend", "Exception while processing order send. OrderId " & orderIdForLog, ex, Nothing) sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, "An error occurred while processing the order: " & ex.Message), Newtonsoft.Json.Formatting.Indented) End Try Return sResponse End If End If End Using End Using If affectedRows > 0 Then If Not IsNothing(dt) AndAlso dt.Rows.Count > 0 AndAlso dt.Columns(0).ColumnName = "ERROR" Then sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, dt.Rows(0)("ERROR").ToString), Newtonsoft.Json.Formatting.Indented) Else sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(True, affectedRows, dt), Newtonsoft.Json.Formatting.Indented) End If Else sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, "An unknown error occurred"), Newtonsoft.Json.Formatting.Indented) End If Catch ex As Exception sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, "An error has occurred: " & ex.Message), Newtonsoft.Json.Formatting.Indented) End Try Return sResponse End Function Public Function Pernod_MASTER_SERVICE_varUPDATE(ByVal obj As String) As String Dim sResponse As String = "" If Session("Key") <> "d68d82b2-b7c0-4b28-806f-1458501bd827" Then sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(False, "Invalid key"), Newtonsoft.Json.Formatting.Indented) Return sResponse End If Try Dim ValPairs As Dictionary(Of String, String) = JsonConvert.DeserializeObject(Of Dictionary(Of String, String))(obj) For Each kvp As KeyValuePair(Of String, String) In JsonConvert.DeserializeObject(Of Dictionary(Of String, String))(obj) If kvp.Key <> "Key" AndAlso kvp.Key <> "USER_PK" AndAlso kvp.Key <> "USER_TYPE_FK" Then Session(kvp.Key) = kvp.Value End If Next Catch ex As Exception sResponse = "" End Try If sResponse = "" Then sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(True, ""), Newtonsoft.Json.Formatting.Indented) Return sResponse End Function Public Function CREATE_XML_FILE_spSel(ByVal ORDERID As String) As String Dim sResponse As String = "" Try Using ds As New SqlDataSource(ConfigurationManager.ConnectionStrings("Pernod_ConnectionString").ConnectionString, "CREATE_XML_FILE_spSel") ds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure ds.SelectParameters.Add("ORDERID", ORDERID) Dim dv As DataView = ds.Select(New DataSourceSelectArguments) If Not dv Is Nothing Then Dim dt As DataTable = dv.Table If Not dt Is Nothing Then Dim sFileName As String = "D:\XML_FILES\OUTBOX\" & Trim(dt(0)("File_Name").ToString) Dim sResult = New StringBuilder() sResult.Append("" & Environment.NewLine) sResult.Append(dt(0)("File_Body").ToString) Dim objWriter = New IO.StreamWriter(sFileName, False) objWriter.Write(sResult.ToString()) objWriter.Close() End If End If End Using Catch ex As Exception sResponse = "" End Try If sResponse = "" Then sResponse = Json.JsonConvert.SerializeObject(functions.JSON_write_boolean_response(True, ""), Newtonsoft.Json.Formatting.Indented) Return sResponse End Function End Class