' Aten Software Product Data Exporter for AspDotNetStoreFront
' 
' Copyright 2017. Aten Software LLC. All Rights Reserved.
' Author: Shailesh Humbad
' Website: https://www.atensoftware.com/p181.php

' This file is part of Aten Software Product Data Exporter for AspDotNetStoreFront.

' Aten Software Product Data Exporter for AspDotNetStoreFront is free software: 
' you can redistribute it and/or modify
' it under the terms of the GNU General Public License as published by
' the Free Software Foundation, either version 3 of the License, or
' (at your option) any later version.

' Aten Software Product Data Exporter for AspDotNetStoreFront
' is distributed in the hope that it will be useful,
' but WITHOUT ANY WARRANTY; without even the implied warranty of
' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
' GNU General Public License for more details.

' See http://www.gnu.org/licenses/ for a copy of the GNU General Public License.

Imports System.Data
Imports System.IO
Imports System.Collections
Imports System.Collections.Generic
Imports System.Web
Imports System.Web.Script.Serialization
Imports System.Text
Imports System.Text.RegularExpressions
Imports System.Security.Cryptography

Partial Class aten_exporter_for_aspdnsf
	Inherits System.Web.UI.Page

	' ******** DEFINE CONFIGURATION FILE PATH HERE
	Private Const ConfigurationFilePath As String = "aten_exporter_for_aspdnsf.config"

	' The version
	Public Const Version As String = "2017-10-22"

	' ******** OTHER SETTINGS
	' Define the line separator
	Private Const LineSeparator As String = ControlChars.Lf
	' Define the field separator
	Private Const FieldSeparator As String = ","
	' Define the enclosure character (may be empty if no enclosure Is required)
	Private Const Enclosure As String = """"
	' Define the enclosure escape character (may be empty to strip the enclosure)
	Private Const EnclosureEscape As String = """"""

	' Member variable for last index of fields
	Private FieldLastIndex As Integer = -1
	' Holds cached field names for quick lookup
	Private FieldNames() As String
	' The main response object to write data to the output stream (compressed or uncompressed)
	Private r As TextWriter
	' The AppConfig value holding the store hostname
	Private LiveServer As String
	' Whether or not the ProductStore table exists (only in multi-store)
	Private ProductStoreTableExists As Boolean
	' Whether or not the GlobalConfig table exists (only in multi-store)
	Private GlobalConfigTableExists As Boolean
	' Whether or not the ProductMarkup table exists (only in multi-store)
	Private ProductMarkupTableExists As Boolean
	' Whether or not the product filtering is in effect (only in multi-store)
	Private AllowProductFiltering As Boolean
	' The AppSettings value holding the connection string
	Private ConnectionString As String
	' A Javascript serializer instance for JSON-encoding
	Private serializer As JavaScriptSerializer
	' Database connection for JSON-encoding
	Private dbJSON As SqlClient.SqlConnection
	' The StoreID
	Private StoreID As Integer

	' On PreRenderComplete, output a CSV file containing the product data
	'  if the password is supplied, otherwise, display a form with instructions.
	' NOTE: Use PreRenderComplete instead of Page_Load event and disable session state
	'  to avoid intermittent session state ' exception: "Session state has created a 
	'  session id, but cannot save it because the response was already flushed by the application."
	Protected Sub Page_PreRenderComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRenderComplete
		' Get command from request
		Dim RequestCommand As String = Request("Command")
		If String.IsNullOrEmpty(RequestCommand) Then
			RequestCommand = ""
		End If

		' If the command is not specified display the password prompt
		If RequestCommand = "" Then
			DisplayPasswordPrompt()
		End If

		' Show the form
		If RequestCommand = "DisplayForm" Then
			' Check password
			CheckPassword()

			Try
				DisplayForm()
			Catch ex As Exception
				DisplayErrorPage("An unhandled exception occurred: " & ex.ToString())
			End Try
			Return
		End If

		' If the command is export, then run the product export
		If RequestCommand = "Export" Then
			' Check password
			CheckPassword()

			' Otherwise, try writing the product data
			Try
				dbJSON = New SqlClient.SqlConnection(GetConnectionString())

				' Connect to the database
				dbJSON.Open()

				WriteProductDataInCSVFormat()
			Catch ex As Exception
				' If there's any error, write the message in a standard format
				Response.Write("ERROR" & LineSeparator)
				Response.Write(ex.Message & LineSeparator & LineSeparator)
				Response.Write(ex.StackTrace)
				Response.End()
			Finally
				' Close database connection
				dbJSON.Close()
			End Try

			' End the response
			Response.End()
		End If

		' Display an invalid command message
		DisplayErrorPage("Invalid command specified.")
	End Sub

	Public Sub CheckPassword()
		Dim Password_SHA256 As String = GetConfigSetting("Password_SHA256").ToUpper()

		' Do not allow blank password
		If Password_SHA256 = "" Then
			DisplayErrorPage("Blank Password Detected - Please configure a SHA-256 hashed password in the config file.  This script will not function unless a password is defined.")
		End If

		' If the command is export, then check the security key
		If GenerateSHA256String(Request("Password")) <> Password_SHA256 Then
			DisplayErrorPage("Invalid Password - The specified password is invalid.")
		End If

	End Sub

	' Pass in an opened db connection. Returns true if StoreID field exists
	Public Function StoreIDFieldExists(ByRef db As SqlClient.SqlConnection) As Boolean
		Dim query As String
		Dim returnValue As Boolean

		' Check if StoreID is supported
		query = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='AppConfig' AND COLUMN_NAME='StoreID'"
		Using cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(query, db)
			' Execute the command
			returnValue = Convert.ToBoolean(cmd.ExecuteScalar())
		End Using

		Return returnValue
	End Function

	' Write radio buttons for store choices, if there is more than one
	Public Function WriteStoreChoices() As String
		Dim query As String
		Dim cmd As SqlClient.SqlCommand
		Dim content As String = ""

		Using db As New SqlClient.SqlConnection(GetConnectionString())
			' Connect to the database
			db.Open()

			' If StoreID is supported, show radio buttons
			If StoreIDFieldExists(db) Then
				content = content + "<fieldset class=""form-group""><legend>Select a store</legend>"
				' Get each StoreID
				query = "SELECT [StoreID], [ConfigValue] FROM [AppConfig] WHERE [Name] = 'LiveServer'"
				' Create a new command object
				cmd = New SqlClient.SqlCommand(query, db)
				' Print the radio buttons
				Using rdr As SqlClient.SqlDataReader = cmd.ExecuteReader
					' Check the first radio button by default
					Dim CheckedString As String = " checked=""checked"" "
					' Loop through the records
					While rdr.Read
						' Include StoreId and default in label
						Dim StoreLabel As String = rdr.GetString(1)
						StoreLabel = String.Format("{0} (StoreId={1})", StoreLabel, rdr.GetInt32(0))
						If rdr.GetInt32(0) = 0 Then
							StoreLabel = StoreLabel & " (Default)"
						End If
						' Write a checkbox for each store
						content = content + String.Format("<label for=""StoreID_{0}"" style=""display:block;""><input type=""radio"" id=""StoreID_{0}"" name=""StoreID"" value=""{0}"" {2}> {1}</label>", rdr.GetInt32(0), StoreLabel, CheckedString)
						' Reset checked string
						CheckedString = ""
					End While
					' Close the reader
					rdr.Close()
				End Using
				content = content + "<small>TIP: Make sure a LiveServer value exists for each store in the AppConfig table.</small>"
				content = content + "</fieldset>"
			End If

			' Disconnect from database
			db.Close()
		End Using

		Return content

	End Function

	' Get the connection string from the app settings
	' using the default app setting for AspDotNetStoreFront's connection string
	Private Function GetConnectionString() As String
		If ConnectionString Is Nothing Then
			' NOTE: ConfigurationManager.AppSettings does not automatically decrypt the settings, 
			'   so by default use WebConfigurationManager.ConnectionStrings instead.
			Dim connectionStringSettings As System.Configuration.ConnectionStringSettings
			Dim ConnectionStringName As String = GetConfigSetting("ConnectionStringName")
			connectionStringSettings = System.Web.Configuration.WebConfigurationManager.ConnectionStrings(ConnectionStringName)
			If connectionStringSettings Is Nothing Then
				' Try AppSettings to support v8 stores
				ConnectionString = System.Configuration.ConfigurationManager.AppSettings(ConnectionStringName)
				If ConnectionString Is Nothing Then
					Throw New Exception("A connection string named '" + ConnectionStringName + "' was not found in the app settings of the web.config.")
				End If
			Else
				ConnectionString = connectionStringSettings.ConnectionString
			End If
		End If

		Return ConnectionString
	End Function


	' Function to write the product data out
	Private Sub WriteProductDataInCSVFormat()
		' Declare variables
		Dim query As String
		Dim VariantIDList As New ArrayList()
		Dim VariantID As Integer
		Dim cmd As SqlClient.SqlCommand
		Dim ReturnValue As Object
		Dim FieldValue As String

		' Initialize serializer
		serializer = New JavaScriptSerializer()

		' Set the script timeout
		Server.ScriptTimeout = GetConfigSettingAsInteger("ServerScriptTimeout")

		' Turn off buffering to limit memory consumption
		Response.Buffer = False
		Response.BufferOutput = False
		' Set the content type and encoding (UTF-8)
		Response.ContentType = "text/plain"
		Response.ContentEncoding = System.Text.Encoding.UTF8
		' Add some custom headers
		Response.AddHeader("X-AtenSoftware-ShoppingCart", "AspDotNetStorefront")
		Response.AddHeader("X-AtenSoftware-Version", Version)

		' Get a text writer stream to gzip the output automatically
		r = GetGzipWriter()

		' If debugging, print the output of the debug query and exit
		If False Then
			DebugPrintQuery("SELECT * FROM [GlobalConfig] WHERE [Name] = 'AllowProductFiltering'")
			DebugPrintQuery("SELECT * FROM Store")
			DebugPrintQuery("SELECT COUNT(*),StoreID FROM ProductStore GROUP BY StoreID")
			' DebugPrintQuery("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='AppConfig'")

			r.Close()
			Return
		End If

		' Get the StoreID, otherwise, set it to 0 (the default store)
		StoreID = 0
		If Not String.IsNullOrEmpty(Request.QueryString("StoreID")) Then
			If Not Int32.TryParse(Request.QueryString("StoreID"), StoreID) Then
				StoreID = 0
			End If
		End If

		' Check which tables exist
		GlobalConfigTableExists = TableExists("GlobalConfig")
		ProductStoreTableExists = TableExists("ProductStore")
		ProductMarkupTableExists = TableExists("ProductMarkup")

		' Create a new Connection object
		Using db As New SqlClient.SqlConnection(GetConnectionString())
			' Connect to the database
			db.Open()

			' Query to get the AppConfig for the store host name
			query = "SELECT [ConfigValue] FROM [AppConfig] WHERE [Name] = 'LiveServer'"
			If StoreIDFieldExists(db) = True Then
				query += " AND StoreID = @StoreID"
			End If
			' Create a new command object
			cmd = New SqlClient.SqlCommand(query, db)
			cmd.Parameters.AddWithValue("@StoreID", StoreID)
			' Execute the command
			ReturnValue = cmd.ExecuteScalar()
			If Convert.ToString(ReturnValue) <> "" Then
				LiveServer = ReturnValue.ToString()
			Else
				' No LiveServer was found
				r.Write("No 'LiveServer' AppConfig value found for StoreID=" + StoreID.ToString())
				r.Close()
				Return
			End If

			' Get the AllowProductFiltering setting
			AllowProductFiltering = False
			If GlobalConfigTableExists Then
				' Query to get the AllowProductFiltering setting
				query = "SELECT ConfigValue FROM [GlobalConfig] WHERE [Name] = 'AllowProductFiltering'"
				' Create a new command object
				cmd = New SqlClient.SqlCommand(query, db)
				' Execute the command
				If cmd.ExecuteScalar().ToString() = "true" Then
					AllowProductFiltering = True
				Else
					AllowProductFiltering = False
				End If
			End If

			' Get the select VariantIDs query
			query = GetProductsQuery()
			' Create a new command object
			cmd = New SqlClient.SqlCommand(query, db)
			' Add StoreID parameter
			cmd.Parameters.AddWithValue("@StoreID", StoreID)

			' Increase command timeout to 10 minutes
			cmd.CommandTimeout = 600

			' Execute the command and get a reader object
			Using rdr As SqlClient.SqlDataReader = cmd.ExecuteReader
				' Loop through the records
				While rdr.Read
					' Add each VariantID to the list
					VariantIDList.Add(rdr.GetInt32(0))
				End While
				' Close the reader
				rdr.Close()
			End Using

			' Get the command object for the product details
			cmd = GetProductDetailsCommand(db)

			' Write a header line even if there are no rows in the data set
			' Use VariantID -1 (i.e. a non-existing product)
			cmd.Parameters("@VariantID").Value = -1
			cmd.Parameters("@StoreID").Value = StoreID
			Using rdr As SqlClient.SqlDataReader = cmd.ExecuteReader
				' Save the field count for performance
				FieldLastIndex = rdr.FieldCount - 1
				' Write header line
				WriteHeaderLine(rdr)
			End Using


			' Loop through each VariantID
			For Each VariantID In VariantIDList
				' Set the VariantID and StoreID
				cmd.Parameters("@VariantID").Value = VariantID
				cmd.Parameters("@StoreID").Value = StoreID

				' Execute the reader
				Using rdr As SqlClient.SqlDataReader = cmd.ExecuteReader

					' Loop through the rows and write them in CSV format
					' There should only be one row
					While rdr.Read
						' Write out the fields one by one
						For ColumnIndex As Integer = 0 To FieldLastIndex

							' Certain fields have a calculated value
							Select Case FieldNames(ColumnIndex)
								Case "ProductUrl"
									FieldValue = "http://" & LiveServer & "/p-" & rdr("Product_ProductID").ToString() & "-" & rdr("Product_SEName").ToString() & ".aspx"
								Case "ImageUrl"
									Dim ImageFileName As String
									' By default, if no images, then set to empty string
									FieldValue = ""
									' Return the URL of the largest image available
									ImageFileName = GetImageUrl(rdr, "large")
									If ImageFileName <> "" Then
										FieldValue = ImageFileName
									Else
										ImageFileName = GetImageUrl(rdr, "medium")
										If ImageFileName <> "" Then
											FieldValue = ImageFileName
										Else
											ImageFileName = GetImageUrl(rdr, "icon")
											If ImageFileName <> "" Then
												FieldValue = ImageFileName
											End If
										End If
									End If
								Case "IconImageUrl"
									FieldValue = GetImageUrl(rdr, "icon")
								Case "MediumImageUrl"
									FieldValue = GetImageUrl(rdr, "medium")
								Case "LargeImageUrl"
									FieldValue = GetImageUrl(rdr, "large")
								Case "AllCategoriesJSON"
									FieldValue = GetEntitiesJSON(rdr, "Category")
								Case "AllManufacturersJSON"
									FieldValue = GetEntitiesJSON(rdr, "Manufacturer")
								Case "AllSectionsJSON"
									FieldValue = GetEntitiesJSON(rdr, "Section")
								Case "AllStoresJSON"
									If ProductStoreTableExists = True Then
										FieldValue = GetEntitiesJSON(rdr, "Store")
									Else
										FieldValue = ""
									End If
								Case Else
									' Return the actual field value
									FieldValue = rdr.GetValue(ColumnIndex).ToString()
							End Select

							' Write the enclosed cell value
							' For performance, do not use WriteEnclosedCellValue here
							r.Write(Enclosure)
							If FieldValue.Length = 1 Then
								If FieldValue = Enclosure Then
									r.Write(EnclosureEscape)
								Else
									r.Write(FieldValue)
								End If
							ElseIf FieldValue.Length > 1 Then
								r.Write(FieldValue.Replace(Enclosure, EnclosureEscape))
							End If
							r.Write(Enclosure)

							' Write the separator on all except last line
							If ColumnIndex <> FieldLastIndex Then
								r.Write(FieldSeparator)
							End If
						Next

						' Write the line separator
						r.Write(LineSeparator)

						Exit While
					End While

					' Close the reader
					rdr.Close()
				End Using

			Next

			' Close the db connection
			db.Close()
		End Using

		' Close the writer
		r.Close()

	End Sub

	' Write a header line
	Private Sub WriteHeaderLine(ByRef rdr As SqlClient.SqlDataReader)
		' Create array to hold field names for quick lookup
		ReDim FieldNames(rdr.FieldCount)

		' Loop through fields and write column names
		For FieldIndex As Integer = 0 To rdr.FieldCount - 1
			' Write enclosed value
			WriteEnclosedCellValue(rdr.GetName(FieldIndex))
			' Save field name in array
			FieldNames(FieldIndex) = rdr.GetName(FieldIndex)
			' Write the field separator, except after last field
			If FieldIndex < FieldLastIndex Then
				r.Write(FieldSeparator)
			End If
		Next
		' Write line separator
		r.Write(LineSeparator)
	End Sub

	' Write enclosed and escaped cell value
	Private Sub WriteEnclosedCellValue(ByRef strToWrite As String)
		r.Write(Enclosure)
		If strToWrite.Length = 1 Then
			If strToWrite = Enclosure Then
				r.Write(EnclosureEscape)
			Else
				r.Write(strToWrite)
			End If
		ElseIf strToWrite.Length > 1 Then
			r.Write(strToWrite.Replace(Enclosure, EnclosureEscape))
		End If
		r.Write(Enclosure)
	End Sub

	' Get the image file name that exists on the server for the given size
	' Return empty string if no image is found
	Private Function GetImageUrl(ByRef rdr As SqlClient.SqlDataReader, ByVal ImageSize As String) As String
		Dim ImageBasePath, ImageFullPath, ImageFileName As String
		Dim ImageUrl As String = ""
		Dim ImageFileNames As New List(Of String)
		Dim ImageBasePaths As New List(Of String)

		' Build the folder name for the image base paths
		ImageBasePaths.Add("/images/product/" & ImageSize & "/")
		ImageBasePaths.Add("/Master/images/product/" & ImageSize & "/")

		' Calculate the image file names
		If IsDBNull(rdr("Product_ImageFilenameOverride")) = False And rdr("Product_ImageFilenameOverride").ToString().Trim() <> "" Then
			' The image file name is based on the filename override
			ImageFileNames.Add(rdr("Product_ImageFilenameOverride").ToString().Trim())
			ImageFileNames.Add(rdr("Product_ImageFilenameOverride").ToString().Trim() & ".jpg")
			ImageFileNames.Add(rdr("Product_ImageFilenameOverride").ToString().Trim() & ".gif")
			ImageFileNames.Add(rdr("Product_ImageFilenameOverride").ToString().Trim() & ".png")
		End If
		' Try image file name based on the product ID
		ImageFileNames.Add(rdr("Product_ProductID").ToString() & ".jpg")
		ImageFileNames.Add(rdr("Product_ProductID").ToString() & ".gif")
		ImageFileNames.Add(rdr("Product_ProductID").ToString() & ".png")
		' Try image file name based on SKU
		ImageFileNames.Add(rdr("Product_SKU").ToString() & ".jpg")
		ImageFileNames.Add(rdr("Product_SKU").ToString() & ".gif")
		ImageFileNames.Add(rdr("Product_SKU").ToString() & ".png")

		' Search for the first image file that exists
		For Each ImageBasePath In ImageBasePaths
			' Check each file to see if it exists
			For Each ImageFileName In ImageFileNames
				' Combine the file name with the base path
				ImageFullPath = Path.Combine(Server.MapPath(ImageBasePath), ImageFileName)
				' Check if the file exists
				If File.Exists(ImageFullPath) Then
					' Build the image URL from the file path
					ImageUrl = "http://" & LiveServer & ImageBasePath & ImageFileName
					' Return the image URL
					Return ImageUrl
				End If
			Next
		Next

		' Return the empty string if no image was found
		Return ""
	End Function

	' Store prepared commands for getting entities JSON
	Private EntityJSONCommands As Dictionary(Of String, SqlClient.SqlCommand) = New Dictionary(Of String, SqlClient.SqlCommand)()

	' Get list of entities in JSON
	Private Function GetEntitiesJSON(ByRef rdrProduct As SqlClient.SqlDataReader, ByVal EntityName As String) As String
		Dim query As String
		Dim cmd As SqlClient.SqlCommand
		Dim EntityList As New Hashtable()

		' Check if command was already prepared
		If EntityJSONCommands.ContainsKey(EntityName) Then
			cmd = EntityJSONCommands(EntityName)
		Else
			' Prepare the command object
			' Get the query explicitly without format string to avoid sql-injection vulnerabilities (for PCI-compliance)
			Select Case EntityName
				Case "Store"
					query = "SELECT Store.StoreId, Store.Name FROM ProductStore WITH (NOLOCK) INNER JOIN Store WITH (NOLOCK) ON ProductStore.StoreID = Store.StoreID WHERE ProductStore.ProductID = @ProductID"
				Case "Category"
					query = "SELECT Category.CategoryId, Category.Name FROM ProductCategory WITH (NOLOCK) INNER JOIN Category WITH (NOLOCK) ON ProductCategory.CategoryID = Category.CategoryID WHERE ProductCategory.ProductID = @ProductID"
				Case "Manufacturer"
					query = "SELECT Manufacturer.ManufacturerId, Manufacturer.Name FROM ProductManufacturer WITH (NOLOCK) INNER JOIN Manufacturer WITH (NOLOCK) ON ProductManufacturer.ManufacturerID = Manufacturer.ManufacturerID WHERE ProductManufacturer.ProductID = @ProductID"
				Case "Section"
					query = "SELECT Section.SectionId, Section.Name FROM ProductSection WITH (NOLOCK) INNER JOIN Section WITH (NOLOCK) ON ProductSection.SectionID = Section.SectionID WHERE ProductSection.ProductID = @ProductID"
				Case Else
					query = ""
			End Select

			' Create a new, prepared command object
			cmd = New SqlClient.SqlCommand(query, dbJSON)
			cmd.Parameters.Add("@ProductID", SqlDbType.Int, 4)
			cmd.Prepare()

			' Save it to the dictionary
			EntityJSONCommands(EntityName) = cmd
		End If

		' Set ProductID parameter
		cmd.Parameters("@ProductID").Value = rdrProduct("Product_ProductID")

		' Execute the command and get a reader object
		Using rdr As SqlClient.SqlDataReader = cmd.ExecuteReader
			' Loop through the records
			While rdr.Read
				EntityList.Add(rdr.GetInt32(0).ToString(), rdr.GetString(1))
			End While
			' Close the reader
			rdr.Close()
		End Using

		' Return entity list as JSON
		Return serializer.Serialize(EntityList)
	End Function

	' Helper to add column name to select statement if the column exists
	Private Sub AddColumnToSelectQuery(ByRef q As StringBuilder, ByVal TableName As String, ByVal ColumnName As String)
		Dim TableAndColumnName As String = TableName & "." & ColumnName
		Dim AliasName As String = TableName & "_" & ColumnName

		' Do nothing if the column does not exist
		If ColumnExists(TableAndColumnName) = False Then
			Return
		End If

		' Append to select statement
		q.AppendLine("," & TableAndColumnName & " AS " & AliasName)
	End Sub

	' Return the query to get all the product data for a single item
	' Do not lock any tables while selecting the item
	' Select the item by VariantID, do not perform any filtering
	Private Function GetProductDetailsCommand(ByRef db As SqlClient.SqlConnection) As SqlClient.SqlCommand
		Dim cmd As SqlClient.SqlCommand
		' Create a new string builder
		Dim q As StringBuilder = New StringBuilder()

		' Start a select query
		q.AppendLine("SELECT")

		' Always start with [Product].[ProductID]
		q.AppendLine("	 Product.ProductID AS Product_ProductID")
		For Each TableAndColumnName As String In ProductColumns.Keys
			' Skip product.productid
			If TableAndColumnName = "product.productid" Then
				Continue For
			End If
			' Add all other columns and their aliases
			q.AppendLine("," & TableAndColumnName & " AS " & ProductColumns(TableAndColumnName))
		Next

		' Select the manufacturer name
		q.AppendLine("	,ISNULL((SELECT TOP 1 Manufacturer.Name FROM ProductManufacturer WITH (NOLOCK) INNER JOIN Manufacturer WITH (NOLOCK) ON ProductManufacturer.ManufacturerID = Manufacturer.ManufacturerID WHERE ProductManufacturer.ProductID = Product.ProductID), '') AS Product_Manufacturer_Name")
		' Select the category name
		q.AppendLine("	,ISNULL((SELECT TOP 1 Category.Name FROM ProductCategory WITH (NOLOCK) INNER JOIN Category WITH (NOLOCK) ON ProductCategory.CategoryID = Category.CategoryID WHERE ProductCategory.ProductID = Product.ProductID), '') AS Product_Category_Name")
		' Select product markup
		If ProductMarkupTableExists Then
			q.AppendLine("	,(SELECT TOP 1 ProductMarkup.MarkupFixed FROM ProductMarkup WITH (NOLOCK) WHERE ProductMarkup.ProductID = Product.ProductID AND ProductMarkup.StoreID = @StoreID) AS Product_Markup_MarkupFixed")
		End If
		' Placeholders for pre-calculated values
		q.AppendLine("	,'' AS ProductUrl")
		q.AppendLine("	,'' AS ImageUrl")
		q.AppendLine("	,'' AS IconImageUrl")
		q.AppendLine("	,'' AS MediumImageUrl")
		q.AppendLine("	,'' AS LargeImageUrl")
		q.AppendLine("	,'' AS AllCategoriesJSON")
		q.AppendLine("	,'' AS AllManufacturersJSON")
		q.AppendLine("	,'' AS AllSectionsJSON")
		If ProductStoreTableExists = True Then
			q.AppendLine("	,'' AS AllStoresJSON")
		End If

		' Select from Product tables
		q.AppendLine("FROM Product WITH (NOLOCK)")
		' Join the ProductVariant table
		q.AppendLine("	INNER JOIN ProductVariant WITH (NOLOCK) ON Product.ProductID=ProductVariant.ProductID")

		' Select the specific item by its Variant ID
		q.AppendLine("	WHERE ProductVariant.VariantID = @VariantID AND @StoreID = @StoreID")

		' Create the command object
		cmd = New SqlClient.SqlCommand(q.ToString(), db)
		' Add VariantID parameter
		cmd.Parameters.Add("@VariantID", SqlDbType.Int, 4)
		' Add StoreID parameter
		cmd.Parameters.Add("@StoreID", SqlDbType.Int, 4)

		' Prepare the command for performance
		cmd.Prepare()

		Return cmd
	End Function

	' Return the query to get all the product data
	' Allow the tables to be locked normally
	' This should run in a few seconds for over 100K products
	Private Function GetProductsQuery() As String
		' Create a new string builder
		Dim q As StringBuilder = New StringBuilder()

		' Start a select query
		q.AppendLine("SELECT")

		' Only fetch the VariantID for performance reasons
		q.AppendLine(" DISTINCT ProductVariant.VariantID")

		' Select from Product tables
		q.AppendLine(" FROM Product")
		' Join the ProductVariant table
		q.AppendLine(" INNER JOIN ProductVariant ON Product.ProductID=ProductVariant.ProductID")

		' Begin where clause
		q.AppendLine(" WHERE @StoreID = @StoreID")

		' Limit to published variants
		If ColumnExists("ProductVariant.Published") And ExportOptionDisabled("IncludeUnpublishedProductVariant") Then
			q.AppendLine(" AND ProductVariant.Published = 1")
		End If
		' Limit to products that are not deleted
		If ColumnExists("ProductVariant.Deleted") Then
			q.AppendLine("	AND ProductVariant.Deleted = 0")
		End If
		' Limit to products that are not excluded from price feeds
		If ColumnExists("Product.ExcludeFromPriceFeeds") And ExportOptionDisabled("IncludeExcludedFromPriceFeeds") Then
			q.AppendLine("	AND Product.ExcludeFromPriceFeeds = 0")
		End If
		' Exclude kit products
		If ColumnExists("Product.IsAKit") And ExportOptionDisabled("IncludeIsAKit") Then
			q.AppendLine("	AND Product.IsAKit = 0")
		End If
		' Exclude pack products
		If ColumnExists("Product.IsAPack") And ExportOptionDisabled("IncludeIsAPack") Then
			q.AppendLine("	AND Product.IsAPack = 0")
		End If
		' Limit to products that are not deleted
		If ColumnExists("Product.Deleted") Then
			q.AppendLine("	AND Product.Deleted = 0")
		End If
		' Exclude products requiring registration
		If ColumnExists("Product.RequiresRegistration") And ExportOptionDisabled("IncludeRequiresRegistration") Then
			q.AppendLine("	AND Product.RequiresRegistration = 0")
		End If
		' Exclude products whose price is hidden
		If ColumnExists("Product.HidePriceUntilCart") And ExportOptionDisabled("IncludeHiddenPrice") Then
			q.AppendLine("	AND Product.HidePriceUntilCart = 0")
		End If
		' Limit to published products
		If ColumnExists("Product.Published") And ExportOptionDisabled("IncludeUnpublishedProduct") Then
			q.AppendLine("	AND Product.Published = 1")
		End If
		' Limit to non-system products
		If ColumnExists("Product.IsSystem") Then
			q.AppendLine("	AND Product.IsSystem = 0")
		End If
		' Today's date should be between the start date and end dates (if available)
		If ColumnExists("Product.AvailableStartDate") And ColumnExists("Product.AvailableStopDate") And ExportOptionDisabled("IncludeNotAvailableByDate") Then
			q.AppendLine("	AND GETDATE() BETWEEN ISNULL(Product.AvailableStartDate, '2000-01-01') AND ISNULL(Product.AvailableStopDate, '2100-12-31')")
		End If
		' The price should be non-zero
		If ColumnExists("ProductVariant.Price") And ExportOptionDisabled("IncludeZeroPrice") Then
			q.AppendLine("	AND ProductVariant.Price > 0")
		End If
		' The product must have a buy button enabled
		If ColumnExists("Product.ShowBuyButton") And ExportOptionDisabled("IncludeNoBuyButton") Then
			q.AppendLine("	AND Product.ShowBuyButton = 1")
		End If
		' Exclude products in customer levels
		If ExportOptionDisabled("IncludeCustomerLevel") Then
			q.AppendLine("	AND Product.ProductID NOT IN (SELECT ProductID FROM ProductCustomerLevel)")
		End If
		' Exclude products in affiliate program
		If ExportOptionDisabled("IncludeAffiliateProgram") Then
			q.AppendLine("	AND Product.ProductID NOT IN (SELECT ProductID FROM ProductAffiliate)")
		End If
		' Exclude products with inventory level below the minimum of 1
		If ColumnExists("Product.TrackInventoryBySizeAndColor") And ExportOptionDisabled("IncludeZeroQuantity") Then
			q.AppendLine("	AND CASE Product.TrackInventoryBySizeAndColor WHEN 1 THEN ISNULL((SELECT SUM(quan) FROM Inventory WHERE Inventory.VariantId = ProductVariant.VariantId), 0) ELSE ProductVariant.Inventory END >= 1")
		End If
		' Restrict products to the specific store if specified
		If AllowProductFiltering = True And StoreID <> -1 Then
			q.AppendLine("	AND Product.ProductID IN (SELECT ProductID FROM ProductStore WHERE StoreID = @StoreID)")
		End If

		Return q.ToString()
	End Function

	' Rrturn true if the checkbox was NOT checked for the specified export option
	Function ExportOptionDisabled(ByVal ExportOptionName As String) As Boolean
		Return String.IsNullOrEmpty(Request.QueryString("IncludeAffiliateProgram"))
	End Function

	' Dictionary containing all Product and ProductVariant column names in lower-case
	' and their computed aliases
	Private _ProductColumns As StringDictionary
	Public ReadOnly Property ProductColumns() As StringDictionary
		Get
			If _ProductColumns Is Nothing Then
				' Create a new dictionary on first call
				_ProductColumns = New StringDictionary()

				' Get the query
				Dim query As String = "SELECT TABLE_NAME + '.' + COLUMN_NAME AS TableAndColumnName," &
				" TABLE_NAME + '_' + COLUMN_NAME AS AliasName " &
				" FROM INFORMATION_SCHEMA.COLUMNS " &
				" WHERE TABLE_NAME IN ('Product','ProductVariant')"

				' Create a new command object
				Using cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(query, dbJSON)
					' Execute the command and get a reader object
					Using rdr As SqlClient.SqlDataReader = cmd.ExecuteReader
						' Loop through the records
						While rdr.Read
							_ProductColumns.Add(rdr.GetString(0).ToLower(), rdr.GetString(1))
						End While
						' Close the reader
						rdr.Close()
					End Using
				End Using
			End If

			Return _ProductColumns
		End Get
	End Property

	' Return true if the column exists in Product or ProductVariant table
	Public Function ColumnExists(ByVal TableAndColumnName As String) As Boolean
		Return ProductColumns.ContainsKey(TableAndColumnName.ToLower())
	End Function

	' Return true if table exists, otherwise false
	Public Function TableExists(ByVal TableName As String) As Boolean
		Dim ReturnValue As Boolean
		' Create a new Connection object
		Using db As New SqlClient.SqlConnection(GetConnectionString())
			' Connect to the database
			db.Open()
			' Query to check if table exists
			Dim Query As String = "SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_NAME=@TableName"
			' Create a new command object
			Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(Query, db)
			cmd.Parameters.AddWithValue("@TableName", TableName)
			' Execute the command
			If cmd.ExecuteScalar() = 1 Then
				ReturnValue = True
			Else
				ReturnValue = False
			End If
			' Close the db connection
			db.Close()
		End Using
		Return ReturnValue
	End Function

	Private Function GetGzipWriter() As TextWriter
		Dim gzip As Compression.GZipStream
		Dim deflate As Compression.DeflateStream

		' Set up Gzip buffering
		Dim r As Stream = Response.OutputStream
		Dim CompressionMethod As String = GetBrowserCompressionMethod()

		If CompressionMethod = "deflate" Then
			deflate = New Compression.DeflateStream(Response.OutputStream, Compression.CompressionMode.Compress)
			Response.AppendHeader("Content-Encoding", "deflate")
			r = deflate
		ElseIf CompressionMethod = "gzip" Then
			gzip = New Compression.GZipStream(Response.OutputStream, Compression.CompressionMode.Compress)
			Response.AppendHeader("Content-Encoding", "gzip")
			r = gzip
		End If

		' Make sure no BOM is written
		Dim Utf8 As Encoding = New UTF8Encoding(False)
		' Create a textwriter
		Dim Writer As TextWriter
		Writer = New StreamWriter(r, Utf8)

		Return Writer
	End Function

	' Gets the compression method supported by the browser
	Public Function GetBrowserCompressionMethod() As String
		Dim AcceptEncoding As String = Request.Headers("Accept-Encoding")
		' If no accept encoding is available, or 
		'  compression is disabled, just return empty string
		If String.IsNullOrEmpty(AcceptEncoding) Or GetConfigSettingAsBoolean("EnableCompression") = False Then
			Return ""
		End If
		' Return appropriate encoding type
		If AcceptEncoding.Contains("deflate") Then
			Return "deflate"
		End If
		If AcceptEncoding.Contains("gzip") Then
			Return "gzip"
		End If
		' If no valid encoding type detected, return empty string
		Return ""
	End Function

	' Debug print an arbitrary query in CSV format
	Private Sub DebugPrintQuery(ByVal query As String)
		Dim RowCount As Integer = 1
		Dim cmd As SqlClient.SqlCommand

		' Create a new Connection object
		Using db As New SqlClient.SqlConnection(GetConnectionString())
			' Connect to the database
			db.Open()

			' Create a command object
			cmd = New SqlClient.SqlCommand(query, db)

			Using rdr As SqlClient.SqlDataReader = cmd.ExecuteReader
				' Loop through the rows and write them in CSV format
				While rdr.Read
					' Write header line on first row
					If RowCount = 1 Then
						For ColumnIndex As Integer = 0 To (rdr.FieldCount - 1)

							' Write the enclosed field name
							WriteEnclosedCellValue(rdr.GetName(ColumnIndex))

							' Write the field separator on all except last cell
							If ColumnIndex <> (rdr.FieldCount - 1) Then
								r.Write(FieldSeparator)
							End If
						Next
						' Write the line separator
						r.Write(LineSeparator)
					End If

					' Increment row count
					RowCount = RowCount + 1

					' Write out the field values
					For ColumnIndex As Integer = 0 To (rdr.FieldCount - 1)

						' Write the enclosed cell value
						WriteEnclosedCellValue(rdr.GetValue(ColumnIndex).ToString())

						' Write the separator on all except last line
						If ColumnIndex <> (rdr.FieldCount - 1) Then
							r.Write(FieldSeparator)
						End If
					Next
					' Write the line separator
					r.Write(LineSeparator)

				End While

				' Close the reader
				rdr.Close()
			End Using

			' Close the db connection
			db.Close()

		End Using
	End Sub

	Public Sub DisplayErrorPage(ByVal ErrorMessage As String)
		WritePageHeader()
		Response.Write("<div class=""alert alert-danger"">")
		Response.Write(Server.HtmlEncode(ErrorMessage))
		Response.Write("</div>")
		WritePageFooter()
		Response.End()
	End Sub

	' Write common page header
	Public Sub WritePageHeader()
		' Set character set to UTF-8
		Response.AppendHeader("Content-Type", "text/html; charset=UTF-8")
		Response.Write("<!DOCTYPE html><html lang=""en""><head>")
		Response.Write("<meta charset=""utf-8"">")
		Response.Write("<meta http-equiv=""X-UA-Compatible"" content=""IE=edge"">")
		Response.Write("<meta name=""viewport"" content=""width=device-width, initial-scale=1"">")
		Response.Write("<title>Aten Software Product Data Exporter for AspDotNetStorefront</title>")
		Response.Write("<link href=""https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"" rel=""stylesheet"" integrity=""sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u"" crossorigin=""anonymous"">")
		Response.Write("</head><body><div class=""container"">")
		Response.Write("<h2><a href=""https://www.atensoftware.com/p187.php"">Aten Software Product Data Exporter for AspDotNetStorefront</a></h2>")
	End Sub

	' Write common page footer
	Public Sub WritePageFooter()
		Response.Write("<div class=""well 5well-sm"" style=""text-align:center;margin-top:1em;"">Copyright 2017 &middot;")
		Response.Write("<a href=""https://www.atensoftware.com"">Aten Software LLC</a> &middot;")
		Response.Write("Version " & Version & "</div>")
		Response.Write("</div>")
		Response.Write("<script src=""https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js""></script>")
		Response.Write("<script src=""https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"" integrity=""sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa"" crossorigin=""anonymous""></script>")
		Response.Write("</body></html>")
	End Sub

	' Display the user interface for the exporter, as a web page
	Public Sub DisplayPasswordPrompt()
		WritePageHeader()

		Response.Write("<form method=""post"" action="""" role=""form"" class=""form-inline"">")

		Response.Write("<fieldset class=""form-group""><legend>Log In</legend>")
		Response.Write("	<label for=""Password"">Password:</label>")
		Response.Write("	<input type=""password"" name=""Password"" id=""Password"" size=""20"" class=""form-control"" />")
		Response.Write("	<input type=""submit"" value=""Submit"" class=""btn btn-primary"" />")
		Response.Write("</fieldset>")

		Response.Write("<input type=""hidden"" name=""Command"" value=""DisplayForm"" />")

		Response.Write("</form>")

		Response.Write("<p style=""margin-top:1em;"">For data feed services for your AspDotNetStorefront store,")
		Response.Write("	visit <a href=""https://www.atensoftware.com/"">atensoftware.com</a></p>")

		WritePageFooter()
		Response.End()
	End Sub

	' Display the user interface for the exporter, as a web page
	Public Sub DisplayForm()
		WritePageHeader()

		Response.Write("<form method=""get"" action="""" role=""form"" name=""exportForm"">")

		Response.Write(WriteStoreChoices())

		Response.Write("<fieldset class=""form-group""><legend>Select export options</legend>")
		Response.Write("	<label for=""IncludeCustomerLevel""><input type=""checkbox"" id=""IncludeCustomerLevel"" name=""IncludeCustomerLevel"" /> Include products in customer levels</label>")
		Response.Write("	<br><label for=""IncludeAffiliateProgram""><input type=""checkbox"" id=""IncludeAffiliateProgram"" name=""IncludeAffiliateProgram"" /> Include products in affiliate programs</label>")
		Response.Write("	<br><label for=""IncludeUnpublishedProductVariant""><input type=""checkbox"" id=""IncludeUnpublishedProductVariant"" name=""IncludeUnpublishedProductVariant"" /> Include unpublished product variants</label>")
		Response.Write("	<br><label for=""IncludeExcludedFromPriceFeeds""><input type=""checkbox"" id=""IncludeExcludedFromPriceFeeds"" name=""IncludeExcludedFromPriceFeeds"" /> Include products excluded from price feeds</label>")
		Response.Write("	<br><label for=""IncludeIsAKit""><input type=""checkbox"" id=""IncludeIsAKit"" name=""IncludeIsAKit"" /> Include kit products</label>")
		Response.Write("	<br><label for=""IncludeIsAPack""><input type=""checkbox"" id=""IncludeIsAPack"" name=""IncludeIsAPack"" /> Include pack products</label>")
		Response.Write("	<br><label for=""IncludeRequiresRegistration""><input type=""checkbox"" id=""IncludeRequiresRegistration"" name=""IncludeRequiresRegistration"" /> Include products that require registration</label>")
		Response.Write("	<br><label for=""IncludeHiddenPrice""><input type=""checkbox"" id=""IncludeHiddenPrice"" name=""IncludeHiddenPrice"" /> Include products with price hidden until cart</label>")
		Response.Write("	<br><label for=""IncludeUnpublishedProduct""><input type=""checkbox"" id=""IncludeUnpublishedProduct"" name=""IncludeUnpublishedProduct"" /> Include unpublished products</label>")
		Response.Write("	<br><label for=""IncludeNotAvailableByDate""><input type=""checkbox"" id=""IncludeNotAvailableByDate"" name=""IncludeNotAvailableByDate"" /> Include not available by date</label>")
		Response.Write("	<br><label for=""IncludeZeroPrice""><input type=""checkbox"" id=""IncludeZeroPrice"" name=""IncludeZeroPrice"" /> Include products with zero price</label>")
		Response.Write("	<br><label for=""IncludeNoBuyButton""><input type=""checkbox"" id=""IncludeNoBuyButton"" name=""IncludeNoBuyButton"" /> Include products with buy button disabled</label>")
		Response.Write("	<br><label for=""IncludeZeroQuantity""><input type=""checkbox"" id=""IncludeZeroQuantity"" name=""IncludeZeroQuantity"" /> Include tracked products with zero quantity</label>")
		Response.Write("	<br><small>TIP: Leave all these options unchecked unless the export is missing products.</small>")
		Response.Write("</fieldset>")

		Response.Write("<fieldset class=""form-group""><legend>Run export</legend>")
		Response.Write("<input type=""submit"" value=""Export the Product Data in CSV format"" class=""btn btn-primary btn-block"" />")
		Response.Write("</fieldset>")

		Response.Write("<input type=""hidden"" name=""Command"" value=""Export"" />")
		Response.Write("<input type=""hidden"" name=""Password""")
		Response.Write("	value=""" & Request.Form("Password") & """  />")

		Response.Write("</form>")

		WritePageFooter()

		Response.End()

	End Sub


	Public Function GetConfigSettingAsBoolean(ByVal SettingName As String) As Boolean
		Dim SettingValue As String = GetConfigSetting(SettingName)
		Dim SettingValueAsBoolean As Boolean

		If Boolean.TryParse(SettingValue, SettingValueAsBoolean) = False Then
			DisplayErrorPage("The setting for " & SettingName & " must be set to either 0 or 1.")
		End If

		Return SettingValueAsBoolean
	End Function

	Public Function GetConfigSettingAsInteger(ByVal SettingName As String) As Integer
		Dim SettingValue As String = GetConfigSetting(SettingName)
		Dim SettingValueAsInteger As Integer

		If Integer.TryParse(SettingValue, SettingValueAsInteger) = False Then
			DisplayErrorPage("The setting for " & SettingName & " must be set to an integer.")
		End If

		Return SettingValueAsInteger
	End Function

	Public Function GetConfigSetting(ByVal SettingName As String) As String

		Dim FileContents As String = ""
		Dim SettingValue As String = ""

		Dim FileName = HttpContext.Current.Server.MapPath(ConfigurationFilePath)

		' Check if file exists
		If File.Exists(FileName) = False Then
			Response.Write("ERROR" & LineSeparator)
			Response.Write("The confiration file was not found." & LineSeparator)
			Response.Write("<br>Please ensure a configuration file exists and that the configuration file path is set correctly.")
			Response.End()
		End If

		FileContents = File.ReadAllText(FileName)

		Dim settingRegex As Regex = New Regex("<" & SettingName & ">([^<]*)")
		Dim settingMatch As Match = settingRegex.Match(FileContents)

		If settingMatch.Success Then
			Return settingMatch.Groups(1).Value
		End If

		Return ""
	End Function

	Public Function GenerateSHA256String(ByVal inputString As String) As String
		Dim sha256 As SHA256 = SHA256Managed.Create()
		Dim bytes As Byte() = Encoding.UTF8.GetBytes(inputString)
		Dim hash As Byte() = sha256.ComputeHash(bytes)
		Dim stringBuilder As New StringBuilder()

		For i As Integer = 0 To hash.Length - 1
			stringBuilder.Append(hash(i).ToString("X2"))
		Next

		Return stringBuilder.ToString()
	End Function

End Class