Current Directory:
                \\10.5.0.10\iis\Sites\goprint2.com\www\
            
            

                Contents:
                Directories:
App_Code
App_Data
App_GlobalResources
App_LocalResources
App_WebReferences
articles
aspnet_client
Bin
common
designer
dillners
images
img
includes
kwikkopy
order_centre
printthree
RadControls
update
WebServices
Files:
about_us.aspx | Download
about_us.aspx.vb | Download
contact_us.aspx | Download
contact_us.aspx.vb | Download
create_user.aspx | Download
create_user.aspx.vb | Download
Default.aspx | Download
Default.aspx.vb | Download
discount.aspx | Download
discount.aspx.vb | Download
domain_activation.aspx | Download
domain_activation.aspx.vb | Download
downloadPD.aspx | Download
downloadPD.aspx.vb | Download
download_error.aspx | Download
download_error.aspx.vb | Download
email_policy.aspx | Download
email_policy.aspx.vb | Download
error_page.aspx | Download
error_page.aspx.vb | Download
faq.aspx | Download
faq.aspx.vb | Download
favicon.ico | Download
FileNotFound.htm | Download
generate_print_driver_step1.aspx | Download
generate_print_driver_step1.aspx.vb | Download
generate_print_driver_step2.aspx | Download
generate_print_driver_step2.aspx.vb | Download
generate_print_driver_step3.aspx | Download
generate_print_driver_step3.aspx.vb | Download
Global.asax | Download
header.fla | Download
header.swf | Download
home.aspx | Download
home.aspx.vb | Download
jsincluding.js | Download
login.aspx | Download
login.aspx.vb | Download
marcelito.txt | Download
packages.aspx | Download
packages.aspx.vb | Download
password_recovery.aspx | Download
password_recovery.aspx.vb | Download
PrintIt.master | Download
PrintIt.master.vb | Download
PrintIt.sitemap | Download
PrintItEs.sitemap | Download
PrintItFr.sitemap | Download
PrintIt_CreateUser.master | Download
PrintIt_CreateUser.master.vb | Download
PrintIt_OutSide.master | Download
PrintIt_OutSide.master.vb | Download
privacy_policy.aspx | Download
privacy_policy.aspx.vb | Download
proba.aspx | Download
proba.aspx.vb | Download
robots.txt | Download
sample.txt | Download
show_sess_par.aspx | Download
show_sess_par.aspx.vb | Download
site_map.aspx | Download
site_map.aspx.vb | Download
site_map.xml | Download
sRacadUploader.jar | Download
sRacadUploader_last.jar | Download
terms_and_conditions.aspx | Download
terms_and_conditions.aspx.vb | Download
terms_of_service.aspx | Download
terms_of_service.aspx.vb | Download
test.txt | Download
testimonials.aspx | Download
testimonials.aspx.vb | Download
TextDraw.html | Download
udraw-designer-widescreen-ui.zip | Download
web.config | Download
websites-for-printers.aspx | Download
websites-for-printers.aspx.vb | Download

            
            

                File Preview:
                Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols

<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class WebWS
    Inherits System.Web.Services.WebService

    Public _connectionString As String = ConfigurationManager.ConnectionStrings("PrintItConnectionString").ConnectionString

    '<WebMethod()> _
    'Public Function GetPressTypes() As System.Data.DataSet
    '    Dim _dataSet As New System.Data.DataSet
    '    Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
    '        Dim _command As New System.Data.SqlClient.SqlCommand()
    '        _command.CommandType = Data.CommandType.Text
    '        _command.Connection = _connection
    '        _command.CommandText = "Select PressType, PressTypeId From PI_PressTypes"
    '        Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
    '        _dataAdapter.Fill(_dataSet, "PressTypes")
    '    End Using
    '    Return _dataSet
    'End Function

    <WebMethod()> _
    Public Function GetPreferableLanguage(ByVal storeId As Integer) As String
        Dim _lang As String = ""
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Try
                Dim _command As New System.Data.SqlClient.SqlCommand()
                _command.CommandType = Data.CommandType.Text
                _command.Connection = _connection
                _command.CommandText = "SELECT Language FROM PI_PreferableLanguage WHERE StoreId=@StoreId"
                _command.Parameters.AddWithValue("@StoreId", storeId)
                _lang = _command.ExecuteScalar().ToString()
            Catch ex As Exception
                _lang = ""
            End Try
            _connection.Close()
        End Using
        Return _lang
    End Function


    Public Function GetNewStoreId() As Integer
        Dim _newStoreId = -1

        Using dbConnection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim sqlCommand As New System.Data.SqlClient.SqlCommand()
            sqlCommand.CommandText = "SELECT TOP 1 [StoreId]+1 AS NewStoreId FROM PI_Activation WHERE StoreId < 9999999 ORDER BY StoreId DESC"
            sqlCommand.Connection = dbConnection
            dbConnection.Open()
            Using dbReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.SingleResult And System.Data.CommandBehavior.CloseConnection)
                While dbReader.Read()
	            _newStoreId = dbReader("NewStoreId")
                End While
            End Using
            dbConnection.Close()
        End Using
        ' using
        Return _newStoreId
    End Function

    Public Function GetDillnerLicenses() As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * FROM VO_DilnerLicense"
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "DilnerLicense")
        End Using
        Return _dataSet
    End Function

    Public Function GetDillnerLicense(ByVal dilnerLicenseStr As String) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * FROM VO_DilnerLicense WHERE DilnerLicense=@DilnerLicense"
            _command.Parameters.AddWithValue("@DilnerLicense", dilnerLicenseStr)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "DilnerLicense")
        End Using
        Return _dataSet
    End Function

    Public Function UpdateDillnerLicense(ByVal id As Integer, ByVal dilnerLicenseStr As String, ByVal clientName As String) As Boolean

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.CommandText = "Update VO_DilnerLicense Set DilnerLicense=@DilnerLicense, ClientName=@ClientName Where id=@id"
            _command.Connection = _connection
            _command.Parameters.AddWithValue("@id", id)
            _command.Parameters.AddWithValue("@DilnerLicense", dilnerLicenseStr)
            _command.Parameters.AddWithValue("@ClientName", clientName)

            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As System.Exception
                Return False
            End Try
            _connection.Close()
        End Using

    End Function

    Public Function InsertDillnerLicense(ByVal dilnerLicenseStr As String, ByVal clientName As String) As Boolean

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.CommandText = "Insert Into VO_DilnerLicense (DilnerLicense, ClientName) Values (@DilnerLicense, @ClientName)"
            _command.Connection = _connection

            _command.Parameters.AddWithValue("@DilnerLicense", dilnerLicenseStr)
            _command.Parameters.AddWithValue("@ClientName", clientName)

            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using

    End Function

    <WebMethod()> _
    Public Function GetPressTypeByPressTypeId(ByVal pressTypeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            '_command.CommandText = "Select * From PI_PressTypes Where PressTypeId=@PressTypeId"
            Dim _ses = Session("lang")
            If Session("lang") = "en" Or Session("lang") = "" Then 'Change PressType according to current language
                _command.CommandText = "Select PressTypeId,PressType From PI_PressTypes Where PressTypeId=@PressTypeId"
            Else
                _command.CommandText = "Select PressTypeId,PressType From PI_PressTypesPerLang where Language='" & Session("lang") & "' and PressTypeId=@PressTypeId"
            End If
            _command.Parameters.AddWithValue("@PressTypeId", pressTypeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "PressTypes")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetCountries() As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select Country, CountryId From PI_Countries Order By Country"
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "Countries")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetStates(ByVal countryId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select State, StateId From PI_States Where CountryId=@CountryId Order By State"
            _command.Parameters.AddWithValue("@CountryId", countryId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "States")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetEmailSettingsByStoreId(ByVal storeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_EmailSettings Where StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "EmailSettings")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function UpdateEmailSettingsByStoreId(ByVal storeId As Integer, ByVal email As String, ByVal attachmentSize As Integer) As Boolean

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.CommandText = "Update PI_EmailSettings Set Email=@Email, AttachmentSize=@AttachmentSize Where StoreId=@StoreId"
            _command.Connection = _connection
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@Email", email)
            _command.Parameters.AddWithValue("@AttachmentSize", attachmentSize)

            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As System.Exception
                Return False
            End Try
            _connection.Close()
        End Using

    End Function

    <WebMethod()> _
    Public Function InsertEmailSettings(ByVal storeId As Integer, ByVal email As String, ByVal attachmentSize As Integer) As Boolean

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.CommandText = "Insert Into PI_EmailSettings (StoreId, Email, AttachmentSize) Values (@StoreId, @Email, @AttachmentSize)"
            _command.Connection = _connection

            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@Email", email)
            _command.Parameters.AddWithValue("@AttachmentSize", attachmentSize)

            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using

    End Function

    <WebMethod()> _
    Public Function GetFtpSettingsByStoreId(ByVal storeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_FTPSettings Where StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "FtpSettings")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function UpdateFtpSettingsByStoreId(ByVal storeId As Integer, ByVal hostName As String, ByVal port As String, ByVal userNameSet As String, ByVal passwordSet As String, ByVal path As String, ByVal isPASV As Boolean) As Boolean

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.CommandText = "Update PI_FTPSettings Set HostName=@HostName, Port=@Port, UserName=@UserName, Password=@Password, Path=@Path, IsPASV=@IsPASV Where StoreId=@StoreId"
            _command.Connection = _connection

            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@HostName", hostName)
            _command.Parameters.AddWithValue("@Port", port)
            _command.Parameters.AddWithValue("@UserName", userNameSet)
            _command.Parameters.AddWithValue("@Password", passwordSet)
            _command.Parameters.AddWithValue("@Path", path)
            _command.Parameters.AddWithValue("@IsPASV", isPASV)

            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As System.Exception
                Return False
            End Try
            _connection.Close()
        End Using

    End Function

    <WebMethod()> _
    Public Function InsertFtpSettings(ByVal storeId As Integer, ByVal hostName As String, ByVal port As String, ByVal userNameSet As String, ByVal passwordSet As String, ByVal path As String, ByVal isPASV As Boolean) As Boolean

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.CommandText = "Insert Into PI_FTPSettings (StoreId, HostName, Port, UserName, Password, Path, IsPASV) Values (@StoreId, @HostName, @Port, @UserName, @Password, @Path, @IsPASV)"
            _command.Connection = _connection

            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@HostName", hostName)
            _command.Parameters.AddWithValue("@Port", port)
            _command.Parameters.AddWithValue("@UserName", userNameSet)
            _command.Parameters.AddWithValue("@Password", passwordSet)
            _command.Parameters.AddWithValue("@Path", path)
            _command.Parameters.AddWithValue("@IsPASV", isPASV)

            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As System.Exception
                Return False
            End Try
            _connection.Close()
        End Using

    End Function

    <WebMethod()> _
    Public Function GetPrintDriverInterfaceByStoreId(ByVal storeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_PrintDriverInterface Where StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "PrintDriverInterface")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function UpdatePrintDriverInterfaceByStoreId(ByVal storeId As Integer, ByVal frameTitle As String, ByVal thankYouMessage As String, ByVal companyName As String, ByVal companyAddress As String, ByVal companyPhone As String, ByVal companyEmail As String, ByVal companyLogo As String, ByVal theme As String) As Boolean

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.CommandText = "Update PI_PrintDriverInterface Set FrameTitle=@FrameTitle, ThankYouMessage=@ThankYouMessage, CompanyName=@CompanyName, CompanyAddress=@CompanyAddress, CompanyPhone=@CompanyPhone, CompanyEmail=@CompanyEmail, CompanyLogo=@CompanyLogo, Theme=@Theme Where StoreId=@StoreId"
            _command.Connection = _connection

            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@FrameTitle", frameTitle)
            _command.Parameters.AddWithValue("@ThankYouMessage", thankYouMessage)
            _command.Parameters.AddWithValue("@CompanyName", companyName)
            _command.Parameters.AddWithValue("@CompanyAddress", companyAddress)
            _command.Parameters.AddWithValue("@CompanyPhone", companyPhone)
            _command.Parameters.AddWithValue("@CompanyEmail", companyEmail)
            _command.Parameters.AddWithValue("@CompanyLogo", companyLogo)
            _command.Parameters.AddWithValue("@Theme", theme)

            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As System.Exception
                Return False
            End Try
            _connection.Close()
        End Using

    End Function

    <WebMethod()> _
    Public Function InsertPrintDriverInterface(ByVal storeId As Integer, ByVal frameTitle As String, ByVal thankYouMessage As String, ByVal companyName As String, ByVal companyAddress As String, ByVal companyPhone As String, ByVal companyEmail As String, ByVal companyLogo As String, ByVal theme As String) As Boolean

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.CommandText = "Insert Into PI_PrintDriverInterface (StoreId, FrameTitle, ThankYouMessage, CompanyName, CompanyAddress, CompanyPhone, CompanyEmail, CompanyLogo, Theme) Values (@StoreId, @FrameTitle, @ThankYouMessage, @CompanyName, @CompanyAddress, @CompanyPhone, @CompanyEmail, @CompanyLogo, @Theme)"
            _command.Connection = _connection

            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@FrameTitle", frameTitle)
            _command.Parameters.AddWithValue("@ThankYouMessage", thankYouMessage)
            _command.Parameters.AddWithValue("@CompanyName", companyName)
            _command.Parameters.AddWithValue("@CompanyAddress", companyAddress)
            _command.Parameters.AddWithValue("@CompanyPhone", companyPhone)
            _command.Parameters.AddWithValue("@CompanyEmail", companyEmail)
            _command.Parameters.AddWithValue("@CompanyLogo", companyLogo)
            _command.Parameters.AddWithValue("@Theme", theme)

            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As System.Exception
                Return False
            End Try
            _connection.Close()
        End Using

    End Function

    <WebMethod()> _
    Public Function InsertActivation(ByVal userId As Guid, ByVal storeId As Integer, ByVal activationId As String, ByVal isActivationValid As Boolean)

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.CommandText = "Insert Into PI_Activation (UserId, ActivationId, StoreId, IsActivationValid) Values (@UserId, @ActivationId, @StoreId, @IsActivationValid)"
            _command.Connection = _connection

            _command.Parameters.AddWithValue("@UserId", userId)
            _command.Parameters.AddWithValue("@ActivationId", activationId)
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@IsActivationValid", isActivationValid)

	    _command.ExecuteNonQuery()
            'Try                
            '    Return True
            'Catch ex As Exception
            '    Return False
            'End Try
            _connection.Close()

        End Using	
    End Function

    <WebMethod()> _
    Public Function UpdateActivationByUserId(ByVal userId As Guid, ByVal activationId As String) As Boolean

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.CommandText = "Update PI_Activation Set ActivationId=@ActivationId Where UserId=@UserId"
            _command.Connection = _connection

            _command.Parameters.AddWithValue("@UserId", userId)
            _command.Parameters.AddWithValue("@ActivationId", activationId)

            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As System.Exception
                Return False
            End Try
            _connection.Close()
        End Using

    End Function

    Public Function GetStoreIdFromDomainName(ByVal _subDomain As String) As Integer
        Dim _storeId As Integer = 0
        Dim _sql As String = "SELECT * FROM PI_DomainName WHERE DomainName = @DomainName"

        Using dbConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("PrintItConnectionString").ConnectionString)
            Dim sqlCommand As New System.Data.SqlClient.SqlCommand()
            sqlCommand.CommandText = _sql
            sqlCommand.Connection = dbConnection
            sqlCommand.Parameters.AddWithValue("@DomainName", _subDomain)
            dbConnection.Open()

            Using dbReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.SingleResult And System.Data.CommandBehavior.CloseConnection)
                While dbReader.Read()
                    _storeId = Integer.Parse(dbReader("StoreId").ToString())
                End While
            End Using
            ' using
            dbConnection.Dispose()
        End Using

        Return _storeId
    End Function

    Public Function GetCustomerIdByUserId(ByVal userId As String) As String

        Dim _customerId As String = ""
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()

            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "SELECT CustomerId FROM PI_Customer_Details WHERE UserId=@UserId"
            _command.Parameters.AddWithValue("@UserId", userId)
            _customerId = _command.ExecuteScalar()
            _connection.Close()
        End Using
        Return _customerId
    End Function

    Public Function GetCustomerIdByJobId(ByVal jobId As String) As String

        Dim _jobId As String = ""
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()

            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "SELECT CustomerId FROM PI_Job_Files WHERE JobId=@JobId"
            _command.Parameters.AddWithValue("@JobId", jobId)
            _jobId = _command.ExecuteScalar()
            _connection.Close()
        End Using
        Return _jobId
    End Function

    Public Function DeleteCustomerByUserId(ByVal userId As Guid) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Delete From PI_Customer_Details Where UserId=@UserId"
            _command.Parameters.AddWithValue("@UserId", userId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function GetActivationIdByUserId(ByVal userId As Guid) As String

        Dim _activationId As String = ""
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()

            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select ActivationId From PI_Activation Where UserId=@UserId"
            _command.Parameters.AddWithValue("@UserId", userId)
            _activationId = _command.ExecuteScalar()
            _connection.Close()
        End Using
        Return _activationId

    End Function

    <WebMethod()> _
    Public Function GetUserIdByStoreId(ByVal storeId As String) As Guid

        Dim _userId As Guid
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()

            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select UserId From PI_Activation Where StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _userId = _command.ExecuteScalar()
            _connection.Close()
        End Using
        Return _userId

    End Function

    <WebMethod()> _
    Public Function GetActivationIdByStoreId(ByVal storeId As String) As String

        Dim _activationId As String = ""
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()

            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select ActivationId From PI_Activation Where StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _activationId = _command.ExecuteScalar()
            _connection.Close()
        End Using
        Return _activationId

    End Function

    <WebMethod()> _
    Public Function GetActivationDataSetByUserId(ByVal userId As Guid) As System.Data.DataSet

        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_Activation Where UserId=@UserId"
            _command.Parameters.AddWithValue("@UserId", userId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "ActivationDS")
        End Using
        Return _dataSet
    End Function


    <WebMethod()> _
    Public Function GetActivationDataSetByStoreId(ByVal storeId As String) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_Activation Where StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "ActivationDS")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Function EmailAddressCheck(ByVal emailAddress As String) As Boolean
        Dim pattern As String = "^[a-zA-Z0-9][\w\.-]*@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"
        Dim emailAddressMatch As Match = Regex.Match(emailAddress, pattern)
        If emailAddressMatch.Success Then
            EmailAddressCheck = True
        Else
            EmailAddressCheck = False
        End If
    End Function

    <WebMethod()> _
    Public Function GetAllFiles() As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_Job_Files"
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobFiles")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetJobFilesByStoreId(ByVal storeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_Job_Files Where StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobFiles")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetJobFilesByStoreIdOrderByDesc(ByVal storeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_Job_Files Where StoreId=@StoreId ORDER BY JobId DESC"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobFiles")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetJobSettingsByStoreId(ByVal storeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_JobSettings s Inner Join PI_JobSettingsPerStore ss on s.SettingId=ss.SettingId Where ss.StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobSettings")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetJobSettingsBySettingName(ByVal settingName As String) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_JobSettings Where SettingName=@SettingName"
            _command.Parameters.AddWithValue("@SettingName", settingName)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobSettings")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetJobSettingValuesByStoreIdAndSettingId(ByVal storeId As Integer, ByVal settingId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_JobSettingValues Where StoreId=@StoreId And SettingId=@SettingId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@SettingId", settingId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobSettingValues")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function InsertJobSetting(ByVal settingName As String, ByVal settingName_es As String) As Integer
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.StoredProcedure
            _command.Connection = _connection
            _command.CommandText = "InsertJobSetting"
            _command.Parameters.AddWithValue("@SettingName", settingName)
            _command.Parameters.AddWithValue("@SettingName_es", settingName_es)

            Try
                Return _command.ExecuteScalar()
                'Return True
            Catch ex As Exception
                'Return False
                Return 0
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function ChangeJobSettingName(ByVal settingId As Integer, ByVal settingName As String) As Integer
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_JobSettings Set SettingName=@SettingName Where SettingId=@SettingId"
            _command.Parameters.AddWithValue("@SettingId", settingId)
            _command.Parameters.AddWithValue("@SettingName", settingName)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function DeleteJobSetting(ByVal settingId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Delete From PI_JobSettings Where SettingId=@SettingId"
            _command.Parameters.AddWithValue("@SettingId", settingId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function DeleteJobSettingCompletely(ByVal settingId As Integer, ByVal storeId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Begin tran " & _
                                   "Delete From PI_JobSettings Where SettingId=@SettingId " & _
                                   "Delete From PI_JobSettingsPerStore Where SettingId=@SettingId And StoreId=@Storeid " & _
                                   "Delete From PI_JobSettingValues Where SettingId=@SettingId And StoreId=@Storeid " & _
                                   "Commit tran"
            _command.Parameters.AddWithValue("@SettingId", settingId)
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function DeleteJobSettingsByStoreId(ByVal storeId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Begin tran " & _
                                   "Delete From PI_JobSettingsPerStore Where StoreId=@StoreId " & _
                                   "Delete From PI_JobSettingValues Where StoreId=@StoreId " & _
                                   "Commit tran"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function InsertJobSettingValue(ByVal storeId As Integer, ByVal settingId As Integer, ByVal settingValue As String, ByVal settingValue_es As String) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Insert Into PI_JobSettingValues (StoreId, SettingId, SettingValue, SettingValue_es) Values (@StoreId, @SettingId, @SettingValue, @SettingValue_es)"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@SettingId", settingId)
            _command.Parameters.AddWithValue("@SettingValue", settingValue)
            _command.Parameters.AddWithValue("@SettingValue_es", settingValue_es)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function ChangeSettingIdInSettingsPerStoreAndSettingValues(ByVal oldSettingId As Integer, ByVal newSettingId As Integer, ByVal storeId As Integer) As Integer
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Begin tran Update PI_JobSettingsPerStore Set SettingId=@NewSettingId Where SettingId=@OldSettingId And StoreId=@StoreId Update PI_JobSettingValues Set SettingId=@NewSettingId Where SettingId=@OldSettingId And StoreId=@StoreId Commit tran"
            _command.Parameters.AddWithValue("@OldSettingId", oldSettingId)
            _command.Parameters.AddWithValue("@NewSettingId", newSettingId)
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function DeleteJobSettingValue(ByVal storeId As Integer, ByVal settingId As Integer, ByVal settingValue As String) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Delete From PI_JobSettingValues Where StoreId=@StoreId And SettingId=@SettingId And SettingValue=@SettingValue"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@SettingId", settingId)
            _command.Parameters.AddWithValue("@SettingValue", settingValue)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
        Public Function DeleteJobSettingValuesBySettingIdAndStoreId(ByVal settingId As Integer, ByVal storeId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Delete From PI_JobSettingValues Where SettingId=@SettingId And StoreId=@Storeid"
            _command.Parameters.AddWithValue("@SettingId", settingId)
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function GetJobSettingsPerStore(ByVal storeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_JobSettingsPerStore Where StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobSettings")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function InsertJobSettingsPerStore(ByVal settingId As Integer, ByVal storeId As Integer, ByVal isUsed As Boolean, ByVal isDefaultValues As Boolean) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Insert Into PI_JobSettingsPerStore (SettingId, StoreId, IsUsed, IsDefaultValues) Values (@SettingId, @StoreId, @IsUsed, @IsDefaultValues)"
            _command.Parameters.AddWithValue("@SettingId", settingId)
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@IsUsed", isUsed)
            _command.Parameters.AddWithValue("@IsDefaultValues", isDefaultValues)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function


    <WebMethod()> _
    Public Function ChangeIsDefaultValue(ByVal settingId As Integer, ByVal storeId As Integer, ByVal isDefaultValues As Boolean) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_JobSettingsPerStore Set IsDefaultValues=@IsDefaultValues Where SettingId=@SettingId And StoreId=@StoreId"
            _command.Parameters.AddWithValue("@SettingId", settingId)
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@IsDefaultValues", isDefaultValues)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function DeleteJobSettingsPerStoreBySettingIdAndStoreId(ByVal settingId As Integer, ByVal storeId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Delete From PI_JobSettingsPerStore Where SettingId=@SettingId And StoreId=@Storeid"
            _command.Parameters.AddWithValue("@SettingId", settingId)
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function GetValue(ByVal _commandString As String)
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = _commandString
            Try
                Return _command.ExecuteScalar()
            Catch ex As Exception
                Return 0
            End Try
            _connection.Close()
        End Using
    End Function

    Public Function SelectWithSQLQuery(ByVal _commandString As String) As System.Data.DataSet
        Dim _dataSet As System.Data.DataSet = New System.Data.DataSet()
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = _commandString
            Dim _dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet)
        End Using
        Return _dataSet
    End Function

    Public Function IsDomainNameAvailable(ByVal domainName As String) As Boolean
        Dim _isDomainAvailable As Boolean = True
        If (domainName.Equals("")) Then
            Return False
        End If

        Using dbConnection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim sqlCommand As New System.Data.SqlClient.SqlCommand()
            sqlCommand.CommandText = "SELECT lower(DomainName) As DomainName FROM PI_DomainName WHERE DomainName = @DomainName"
            sqlCommand.Connection = dbConnection
            sqlCommand.Parameters.AddWithValue("@DomainName", domainName)
            dbConnection.Open()

            Using dbReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.SingleResult And System.Data.CommandBehavior.CloseConnection)
                While dbReader.Read()
                    If (domainName.ToLower().Equals(dbReader("DomainName"))) Then
                        _isDomainAvailable = False
                        Exit While
                    End If
                End While
            End Using
            ' using
            dbConnection.Dispose()
        End Using
        Return _isDomainAvailable
    End Function

    Public Function GetModuleDescription(ByVal moduleId As Integer, ByVal lang As String) As String
        Dim _moduleDescription As String = ""
        Dim _sql As String = "SELECT ModuleDescription FROM PI_Modules WHERE ModuleId = @ModuleId"
        If lang.Equals("") Then lang = "en"
        If Not (lang.Equals("en")) Then
            _sql = "SELECT ModuleDescription FROM PI_ModulesPerLang WHERE ModuleId = @ModuleId AND Language = @Language"
        End If

        Using dbConnection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim sqlCommand As New System.Data.SqlClient.SqlCommand()
            sqlCommand.CommandText = _sql
            sqlCommand.Connection = dbConnection
            sqlCommand.Parameters.AddWithValue("@ModuleId", moduleId)
            sqlCommand.Parameters.AddWithValue("@Language", lang)
            dbConnection.Open()

            Using dbReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.SingleResult And System.Data.CommandBehavior.CloseConnection)
                While dbReader.Read()
                    _moduleDescription = dbReader("ModuleDescription").ToString()
                End While
            End Using
            ' using
            dbConnection.Dispose()
        End Using
        Return _moduleDescription
    End Function

    '-------------------------'
    ' Job Info Set Up methods '
    '-------------------------'

    <WebMethod()> _
    Public Function GetDefJobSettings() As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_DefJobSettings"
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobSettings")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetDefJobSettingsPerLanguage(ByVal language As String) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select sl.* From PI_DefJobSettingsPerLanguage sl Inner Join PI_Languages l on l.LanguageId=sl.LanguageId Where l.Language=@Language"
            _command.Parameters.AddWithValue("@Language", language)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobSettings")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetDefJobSettingsPerLanguageByPrimaryKey(ByVal settingId As Integer, ByVal languageId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_DefJobSettingsPerLanguage Where LanguageId=@LanguageId and DSettingId=@DSettingId"
            _command.Parameters.AddWithValue("@LanguageId", languageId)
            _command.Parameters.AddWithValue("@DSettingId", settingId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobSettings")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetDefJobSettingValuesBySettingId(ByVal settingId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_DefJobSettingValues Where DSettingId=@DSettingId"
            _command.Parameters.AddWithValue("@DSettingId", settingId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobSettings")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetDefJobSettingValuesPerLanguageByPrimaryKey(ByVal settingValueId As Integer, ByVal languageId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_DefJobSettingValuesPerLanguage Where DSettingValueId=@DSettingValueId And LanguageId=@LanguageId"
            _command.Parameters.AddWithValue("@DSettingValueId", settingValueId)
            _command.Parameters.AddWithValue("@LanguageId", languageId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobSettings")
        End Using
        Return _dataSet
    End Function

    ''' <summary>
    ''' This will get settings values per language for a particular setting id.
    ''' </summary>
    ''' <param name="settingId">SettingId to query.</param>
    ''' <param name="language">Language as ISO 2 code.</param>
    ''' <returns>DataSet Object</returns>
    ''' <remarks></remarks>
    <WebMethod()> _
    Public Function GetDefJobSettingValuesPerLanguageBySettingId(ByVal settingId As Integer, ByVal language As String) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "SELECT vl.* FROM PI_DefJobSettingValuesPerLanguage " + _
                                   "vl INNER JOIN PI_DefJobSettingValues v " + _
                                   "ON v.DSettingValueId=vl.DSettingValueId " + _
                                   "INNER JOIN PI_Languages l ON l.LanguageId=vl.LanguageId " + _
                                   "WHERE l.Language=@Language AND v.DSettingId=@DSettingId"
            _command.Parameters.AddWithValue("@DSettingId", settingId)
            _command.Parameters.AddWithValue("@Language", language)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobSettings")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function InsertStoreJobSettingValue(ByVal settingId As Integer, ByVal settingValue As String) As Integer
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.StoredProcedure
            _command.Connection = _connection
            _command.CommandText = "InsertStoreJobSettingValue"
            _command.Parameters.AddWithValue("@SettingId", settingId)
            _command.Parameters.AddWithValue("@SettingValue", settingValue)
            Try
                Return _command.ExecuteScalar()
                'Return True
            Catch ex As Exception
                'Return False
                Return 0
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
Public Function UpdateStoreJobSettingValueBySettingValueId(ByVal settingValueId As Integer, ByVal settingValue As String) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_StoreJobSettingValues Set SettingValue=@SettingValue Where SettingValueId=@SettingValueId"
            _command.Parameters.AddWithValue("@SettingValueId", settingValueId)
            _command.Parameters.AddWithValue("@SettingValue", settingValue)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function
    <WebMethod()> _
   Public Function UpdateStoreJobSettingBySettingId(ByVal settingId As Integer, ByVal setting As String) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_StoreJobSettings Set Setting=@Setting Where SettingId=@SettingId"
            _command.Parameters.AddWithValue("@SettingId", settingId)
            _command.Parameters.AddWithValue("@Setting", setting)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function GetJobIdFromDownloadId(ByVal downloadId As String) As String
        Dim _jobId As String = "00000"

        Using dbConnection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim sqlCommand As New System.Data.SqlClient.SqlCommand()
            sqlCommand.CommandText = "Select JobId from PI_Job_Files Where DownloadId=@DownloadId"
            sqlCommand.Connection = dbConnection
            sqlCommand.Parameters.AddWithValue("@DownloadId", downloadId)
            dbConnection.Open()

            Using dbReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.SingleResult And System.Data.CommandBehavior.CloseConnection)
                While dbReader.Read()
                    _jobId = dbReader("JobId").ToString()
                End While
            End Using
            ' using
            dbConnection.Dispose()
        End Using

        Return _jobId
    End Function

    <WebMethod()> _
      Public Function GetStoreJobSettingByStoreId(ByVal storeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)

            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * from PI_StoreJobSettings  Where StoreId=@StoreId  "
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "StoreSettings")
        End Using
        Return _dataSet
    End Function

    Public Function IsPackageAvailableByStoreId(ByVal StoreId As Integer, ByVal ModuleId As Integer) As Boolean
        Dim _dataSet As New System.Data.DataSet
        Dim _isPackageAvailable As Boolean = False

        Using dbConnection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim sqlCommand As New System.Data.SqlClient.SqlCommand()
            sqlCommand.CommandText = "SELECT * FROM PI_StorePackages WHERE  StoreId=@StoreId and ModuleId=@ModuleId"
            sqlCommand.Connection = dbConnection
            sqlCommand.Parameters.AddWithValue("@ModuleId", ModuleId)
            sqlCommand.Parameters.AddWithValue("@StoreId", StoreId)
            dbConnection.Open()

            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(sqlCommand)
            _dataAdapter.Fill(_dataSet, "PackageAvailable")
            If _dataSet.Tables(0).Rows.Count > 0 Then
                _isPackageAvailable = True
            End If

            ' using
            dbConnection.Close()
        End Using
        Return _isPackageAvailable
    End Function

    <WebMethod()> _
     Public Function GetStorePackagesByStoreId(ByVal storeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)

            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            Dim _selectedLanguage As String = System.Threading.Thread.CurrentThread.CurrentUICulture.Name
            If _selectedLanguage = "en" Or _selectedLanguage = "" Then 'Change PressType according to current language
                _command.CommandText = "SELECT st.StoreId, st.PackageId, pck.PackageName, pck.PackagePrice, pck.PackageDescription, pck.FreeJobDescription, " & _
                     "pck.PricePerClick, pck.MaxMonthlyFee, st.ModuleId, md.ModuleName " & _
                   " FROM [PrintIt].[dbo].[PI_StorePackages] st, PI_PackageInformation pck, PI_Modules md" & _
                 " where st.PackageId=pck.PackageId and st.ModuleId=md.ModuleId and storeId=@StoreId order by st.ModuleId asc"
            Else
                _command.CommandText = "SELECT st.StoreId, st.PackageId, pcklang.PackageName,pcklang.PackageDescription, pck.PackagePrice,pcklang.FreeJobDescription," & _
                     "pck.PricePerClick, pck.MaxMonthlyFee, st.ModuleId, md.ModuleName " & _
                   " FROM [PrintIt].[dbo].[PI_StorePackages] st, PI_PackageInformation pck, PI_ModulesPerLang md, PI_PackageInformationPerLang pcklang" & _
                 " where st.PackageId=pck.PackageId and st.PackageId=pcklang.PackageId and st.ModuleId=md.ModuleId and pcklang.Language='" & _selectedLanguage & "' and storeId=@StoreId and md.Language='" & _selectedLanguage & "' order by st.ModuleId asc"
            End If

            _command.Parameters.AddWithValue("@StoreId", storeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "StorePackages")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function InsertStorePackages(ByVal storeId As Integer, ByVal packageId As Integer, _
                                        ByVal moduleId As Integer, ByVal startPackageDate As Date, _
                                        ByVal endPackageDate As Date) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Insert Into PI_StorePackages (StoreId, PackageId, ModuleId, StartPackageDate, EndPackageDate) " + _
                                   "Values (@StoreId, @PackageId, @ModuleId, @StartPackageDate, @EndPackageDate)"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@PackageId", packageId)
            _command.Parameters.AddWithValue("@ModuleId", moduleId)
            _command.Parameters.AddWithValue("@StartPackageDate", startPackageDate)
            _command.Parameters.AddWithValue("@EndPackageDate", endPackageDate)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function DeleteStorePackagesByStoreId(ByVal storeId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "DELETE FROM PI_StorePackages Where StoreId = @StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function InsertStoreJobSetting(ByVal storeId As Integer, ByVal setting As String, ByVal isUsed As Boolean) As Integer
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.StoredProcedure
            _command.Connection = _connection
            _command.CommandText = "InsertStoreJobSetting"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@Setting", setting)
            _command.Parameters.AddWithValue("@IsUsed", isUsed)
            Try
                Return _command.ExecuteScalar()
                'Return True
            Catch ex As Exception
                'Return False
                Return 0
            End Try
            _connection.Close()
        End Using
    End Function


    <WebMethod()> _
    Public Function InsertDefJobSetting(ByVal setting As String) As Integer
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.StoredProcedure
            _command.Connection = _connection
            _command.CommandText = "InsertDefJobSetting"
            _command.Parameters.AddWithValue("@DSetting", setting)
            Try
                Return _command.ExecuteScalar()
            Catch ex As Exception
                Return 0
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function InsertDefJobSettingValue(ByVal settingId As Integer, ByVal settingValue As String) As Integer
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.StoredProcedure
            _command.Connection = _connection
            _command.CommandText = "InsertDefJobSettingValue"
            _command.Parameters.AddWithValue("@DSettingId", settingId)
            _command.Parameters.AddWithValue("@DSettingValue", settingValue)
            Try
                Return _command.ExecuteScalar()
                'Return True
            Catch ex As Exception
                'Return False
                Return 0
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function InsertDefJobSettingPerLanguage(ByVal settingId As Integer, ByVal languageId As Integer, ByVal setting As String) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Insert Into PI_DefJobSettingsPerLanguage (DSettingId, LanguageId, DSetting) Values (@DSettingId, @LanguageId, @DSetting)"
            _command.Parameters.AddWithValue("@DSettingId", settingId)
            _command.Parameters.AddWithValue("@LanguageId", languageId)
            _command.Parameters.AddWithValue("@DSetting", setting)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function InsertDefJobSettingValuePerLanguage(ByVal settingValueId As Integer, ByVal languageId As Integer, ByVal settingValue As String) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Insert Into PI_DefJobSettingValuesPerLanguage (DSettingValueId, LanguageId, DSettingValue) Values (@DSettingValueId, @LanguageId, @DSettingValue)"
            _command.Parameters.AddWithValue("@DSettingValueId", settingValueId)
            _command.Parameters.AddWithValue("@LanguageId", languageId)
            _command.Parameters.AddWithValue("@DSettingValue", settingValue)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function
    <WebMethod()> _
   Public Function GetDomainName(ByVal storeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_DomainName where StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "DomainName")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
   Public Function GetDomainNameByActivationKey(ByVal key As String) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_DomainName where ActivationKey=@ActivationKey"
            _command.Parameters.AddWithValue("@ActivationKey", key)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "DomainName")
        End Using
        Return _dataSet
    End Function

    Public Function UpdateDomainIsActive(ByVal key As String, ByVal isActive As Boolean) As Boolean
        Dim _result As Boolean = False
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_DomainName Set IsActive=@IsActive Where ActivationKey=@ActivationKey"
            _command.Parameters.AddWithValue("@ActivationKey", key)
            _command.Parameters.AddWithValue("@IsActive", isActive)
            Try
                _command.ExecuteNonQuery()
                _result = True
            Catch ex As Exception
                _result = False
            End Try
            _connection.Close()
        End Using
        Return _result
    End Function

    <WebMethod()> _
    Public Function InsertDomainName(ByVal storeId As Integer, ByVal domainName As String, ByVal defaultWebContent As Boolean, ByVal isActive As Boolean, ByVal activationKey As String, ByVal isCmsAvailable As Boolean, ByVal IsInternal As Boolean) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "INSERT INTO PI_DomainName (StoreId, DomainName, RegisterDate, DefaultWebContent, IsActive, ActivationKey, IsCmsAvailable, IsInternal) Values (@StoreId, @DomainName, @RegisterDate, @DefaultWebContent, @IsActive, @ActivationKey, @IsCmsAvailable, @IsInternal)"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@DomainName", domainName)
            _command.Parameters.AddWithValue("@RegisterDate", DateTime.Now)
            _command.Parameters.AddWithValue("@DefaultWebContent", defaultWebContent)
            _command.Parameters.AddWithValue("@IsActive", isActive)
            _command.Parameters.AddWithValue("@ActivationKey", activationKey)
            _command.Parameters.AddWithValue("@IsCmsAvailable", isCmsAvailable)
            _command.Parameters.AddWithValue("@IsInternal", IsInternal)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    Public Function UpdateDomainName(ByVal oldDomainName As String, ByVal newDomainName As String, ByVal storeId As Integer) As Boolean
        Dim _result As Boolean = False
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_DomainName Set DomainName=@NewDomainName Where DomainName=@OldDomainName And StoreId=@StoreId"
            _command.Parameters.AddWithValue("@OldDomainName", oldDomainName)
            _command.Parameters.AddWithValue("@NewDomainName", newDomainName)
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Try
                _command.ExecuteNonQuery()
                _result = True
            Catch ex As Exception
                _result = False
            End Try
            _connection.Close()
        End Using
        Return _result
    End Function


    <WebMethod()> _
    Public Function UpdateDefJobSettingBySettingId(ByVal settingId As Integer, ByVal setting As String) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_DefJobSettings Set DSetting=@DSetting Where DSettingId=@DSettingId"
            _command.Parameters.AddWithValue("@DSettingId", settingId)
            _command.Parameters.AddWithValue("@DSetting", setting)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function


    <WebMethod()> _
    Public Function UpdateDefJobSettingPerLanguageBySettingId(ByVal settingId As Integer, ByVal setting As String, ByVal languageId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_DefJobSettingsPerLanguage Set DSetting=@DSetting Where DSettingId=@DSettingId and LanguageId=@LanguageId"
            _command.Parameters.AddWithValue("@DSettingId", settingId)
            _command.Parameters.AddWithValue("@DSetting", setting)
            _command.Parameters.AddWithValue("@LanguageId", languageId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function UpdateDefJobSettingValueBySettingValueId(ByVal settingValueId As Integer, ByVal settingValue As String) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_DefJobSettingValues Set DSettingValue=@DSettingValue Where DSettingValueId=@DSettingValueId"
            _command.Parameters.AddWithValue("@DSettingValueId", settingValueId)
            _command.Parameters.AddWithValue("@DSettingValue", settingValue)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function UpdateDefJobSettingValuePerLanguageBySettingValueId(ByVal settingValueId As Integer, ByVal settingValue As String, ByVal languageId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_DefJobSettingValuesPerLanguage Set DSettingValue=@DSettingValue Where DSettingValueId=@DSettingValueId and LanguageId=@LanguageId"
            _command.Parameters.AddWithValue("@DSettingValueId", settingValueId)
            _command.Parameters.AddWithValue("@DSettingValue", settingValue)
            _command.Parameters.AddWithValue("@LanguageId", languageId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function DeleteDefJobSettingByDSettingId(ByVal settingId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Begin tran " & _
                                    "Delete From PI_DefJobSettingValuesPerLanguage Where DSettingValueId In (Select DSettingValueId From PI_DefJobSettingValues Where DSettingId=@DSettingId) " & _
                                    "Delete From PI_DefJobSettingValues Where DSettingValueId In (Select DSettingValueId From PI_DefJobSettingValues Where DSettingId=@DSettingId) " & _
                                    "Delete From PI_DefJobSettingsPerLanguage Where DSettingId=@DSettingId " & _
                                    "Delete From PI_DefJobSettings Where DSettingId=@DSettingId " & _
                                    "Commit tran"
            _command.Parameters.AddWithValue("@DSettingId", settingId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function DeleteDomainNameByStoreId(ByVal storeId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "DELETE FROM PI_DomainName Where StoreId = @StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function DeleteDefJobSettingValueByDSettingValueId(ByVal settingValueId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Begin tran " & _
                                    "Delete From PI_DefJobSettingValuesPerLanguage Where DSettingValueId=@DSettingValueId " & _
                                    "Delete From PI_DefJobSettingValues Where DSettingValueId=@DSettingValueId " & _
                                    "Commit tran"
            _command.Parameters.AddWithValue("@DSettingValueId", settingValueId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function DeleteStoreJobSetting(ByVal settingId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Delete From PI_StoreJobSettings Where SettingId=@SettingId"
            _command.Parameters.AddWithValue("@SettingId", settingId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function DeleteStoreJobSettingValue(ByVal settingId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Delete From PI_StoreJobSettingValues Where SettingId=@SettingId"
            _command.Parameters.AddWithValue("@SettingId", settingId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function DeleteStoreJobSettingValueBySettingValueId(ByVal settingValueId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Delete From PI_StoreJobSettingValues Where SettingValueId=@SettingValueId"
            _command.Parameters.AddWithValue("@SettingValueId", settingValueId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    '---------------------------'
    ' / Job Info Set Up methods '
    '---------------------------'
    <WebMethod()> _
    Public Function DeleteAllUserSettings(ByVal storeId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Begin tran " & _
                                   "Delete From PI_JobSettingsPerStore Where StoreId=@StoreId " & _
                                   "Delete From PI_JobSettingValues Where StoreId=@StoreId " & _
                                   "Delete From PI_EmailSettings Where StoreId=@StoreId " & _
                                   "Delete From PI_FtpSettings Where StoreId=@StoreId " & _
                                   "Delete From PI_PrintDriverInterface Where StoreId=@StoreId " & _
                                   "Delete From PI_MetaTags Where PageId In (Select PageId From PI_WebPages Where StoreId=@StoreId) " & _
                                   "Delete From PI_WebPages Where StoreId=@StoreId " & _
                                   "Delete From PI_DomainName Where StoreId=@StoreId " & _
                                   "Commit tran"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function GetPackageInformation() As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_PackageInformation Order By PackageId"
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "PackageInfo")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetPackageInformationByPackageId(ByVal packageId As Integer, ByVal lang As String) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            '_command.CommandText = "Select * From PI_PackageInformation Where PackageId=@PackageId"  
            If lang.Equals("en") Or lang.Equals("") Then
                _command.CommandText = "Select * From PI_PackageInformation Where PackageId=@PackageId"
            Else
                _command.CommandText = "select pl.*, p.PackagePrice," & _
                " p.PricePerClick,p.PrePaidClicks,p.MaxMonthlyFee,p.YearlyPrice,p.ModuleId " & _
                " from PI_PackageInformationPerLang pl, PI_PackageInformation p " & _
                " where pl.PackageId =p.PackageId " & _
                " and pl.Language='" & lang & "' " & _
                " and pl.PackageId=@PackageId"
            End If

            _command.Parameters.AddWithValue("@PackageId", packageId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "PackageInfo")

        End Using
        Return _dataSet
    End Function
    <WebMethod()> _
Public Function GetModuleInformationByModuleId(ByVal ModuleId As Integer, ByVal lang As String) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection

            Dim _ses = Session("lang") 'choose module tabel depend on the language
            If lang.Equals("en") Or lang.Equals("") Then
                _command.CommandText = "Select * From PI_Modules Where ModuleId =@ModuleId"
            Else
                _command.CommandText = "Select * From PI_ModulesPerLang Where ModuleId =@ModuleId" & _
                " and Language='" & lang & "'"
            End If

            _command.Parameters.AddWithValue("@ModuleId", ModuleId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "ModuleInfo")

        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetCreditCardInformationByUserId(ByVal userId As Guid) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_CreditCardInformation Where UserId=@UserId"
            _command.Parameters.AddWithValue("@UserId", userId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "CCInfo")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function UpdateCreditCardInformation(ByVal userId As Guid, ByVal cardType As String, ByVal cardNumber As String, ByVal cardHolderName As String, ByVal expMonth As String, ByVal expYear As String, ByVal cvd As String, ByVal isValid As Boolean) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_CreditCardInformation Set CardType=@CardType, CardNumber=@CardNumber, CardHolderName=@CardHolderName, ExpMonth=@ExpMonth, ExpYear=@ExpYear, CVD=@CVD, IsValid=@IsValid Where UserId=@UserId"
            _command.Parameters.AddWithValue("@UserId", userId)
            If cardType Is Nothing Then _command.Parameters.AddWithValue("@CardType", DBNull.Value) Else _command.Parameters.AddWithValue("@CardType", cardType)
            If cardNumber Is Nothing Then _command.Parameters.AddWithValue("@CardNumber", DBNull.Value) Else _command.Parameters.AddWithValue("@CardNumber", cardNumber)
            If cardHolderName Is Nothing Then _command.Parameters.AddWithValue("@CardHolderName", DBNull.Value) Else _command.Parameters.AddWithValue("@CardHolderName", cardHolderName)
            If expMonth Is Nothing Then _command.Parameters.AddWithValue("@ExpMonth", DBNull.Value) Else _command.Parameters.AddWithValue("@ExpMonth", expMonth)
            If expYear Is Nothing Then _command.Parameters.AddWithValue("@ExpYear", DBNull.Value) Else _command.Parameters.AddWithValue("@ExpYear", expYear)
            If cvd Is Nothing Then _command.Parameters.AddWithValue("@CVD", DBNull.Value) Else _command.Parameters.AddWithValue("@CVD", cvd)
            If isValid.ToString() Is Nothing Then _command.Parameters.AddWithValue("@IsValid", DBNull.Value) Else _command.Parameters.AddWithValue("@IsValid", isValid)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function UpdateCreditCardAndStartBillingDate(ByVal userId As Guid, ByVal cardType As String, ByVal cardNumber As String, ByVal cardHolderName As String, ByVal expMonth As String, ByVal expYear As String, ByVal cvd As String, ByVal startBillingDate As Date, ByVal isValid As Boolean) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_CreditCardInformation Set CardType=@CardType, CardNumber=@CardNumber, CardHolderName=@CardHolderName, ExpMonth=@ExpMonth, ExpYear=@ExpYear, CVD=@CVD, StartBillingDate=@StartBillingDate, IsValid=@IsValid Where UserId=@UserId"
            _command.Parameters.AddWithValue("@UserId", userId)
            If cardType Is Nothing Then _command.Parameters.AddWithValue("@CardType", DBNull.Value) Else _command.Parameters.AddWithValue("@CardType", cardType)
            If cardNumber Is Nothing Then _command.Parameters.AddWithValue("@CardNumber", DBNull.Value) Else _command.Parameters.AddWithValue("@CardNumber", cardNumber)
            If cardHolderName Is Nothing Then _command.Parameters.AddWithValue("@CardHolderName", DBNull.Value) Else _command.Parameters.AddWithValue("@CardHolderName", cardHolderName)
            If expMonth Is Nothing Then _command.Parameters.AddWithValue("@ExpMonth", DBNull.Value) Else _command.Parameters.AddWithValue("@ExpMonth", expMonth)
            If expYear Is Nothing Then _command.Parameters.AddWithValue("@ExpYear", DBNull.Value) Else _command.Parameters.AddWithValue("@ExpYear", expYear)
            If cvd Is Nothing Then _command.Parameters.AddWithValue("@CVD", DBNull.Value) Else _command.Parameters.AddWithValue("@CVD", cvd)
            If startBillingDate.ToString() Is Nothing Then _command.Parameters.AddWithValue("@StartBillingDate", DBNull.Value) Else _command.Parameters.AddWithValue("@StartBillingDate", startBillingDate)
            If isValid.ToString() Is Nothing Then _command.Parameters.AddWithValue("@IsValid", DBNull.Value) Else _command.Parameters.AddWithValue("@IsValid", isValid)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    '<WebMethod()> _
    'Public Function UpdateStartBillingDate(ByVal userId As Guid, ByVal startBillingDate As Date) As Boolean

    '    Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
    '        _connection.Open()
    '        Dim _command As New System.Data.SqlClient.SqlCommand()
    '        _command.CommandType = Data.CommandType.Text
    '        _command.Connection = _connection
    '        _command.CommandText = "Update PI_CreditCardInformation Set StartBillingDate=@StartBillingDate Where UserId=@UserId"
    '        _command.Parameters.AddWithValue("@UserId", userId)
    '        If startBillingDate.ToString() Is Nothing Then _command.Parameters.AddWithValue("@StartBillingDate", DBNull.Value) Else _command.Parameters.AddWithValue("@StartBillingDate", startBillingDate)
    '        Try
    '            _command.ExecuteNonQuery()
    '            Return True
    '        Catch ex As Exception
    '            Return False
    '        End Try
    '        _connection.Close()
    '    End Using
    'End Function

    <WebMethod()> _
    Public Function InsertCreditCardInformation(ByVal userId As Guid, ByVal cardType As String, ByVal cardNumber As String, ByVal cardHolderName As String, ByVal expMonth As String, ByVal expYear As String, ByVal cvd As String, ByVal isValid As Boolean) As Boolean
        Dim _dataSet As New System.Data.DataSet

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Insert Into PI_CreditCardInformation (UserId, CardType, CardNumber, CardHolderName, ExpMonth, ExpYear, CVD, IsValid) Values (@UserId, @CardType, @CardNumber, @CardHolderName, @ExpMonth, @ExpYear, @CVD, @IsValid)"
            _command.Parameters.AddWithValue("@UserId", userId)
            If cardType Is Nothing Then _command.Parameters.AddWithValue("@CardType", DBNull.Value) Else _command.Parameters.AddWithValue("@CardType", cardType)
            If cardNumber Is Nothing Then _command.Parameters.AddWithValue("@CardNumber", DBNull.Value) Else _command.Parameters.AddWithValue("@CardNumber", cardNumber)
            If cardHolderName Is Nothing Then _command.Parameters.AddWithValue("@CardHolderName", DBNull.Value) Else _command.Parameters.AddWithValue("@CardHolderName", cardHolderName)
            If expMonth Is Nothing Then _command.Parameters.AddWithValue("@ExpMonth", DBNull.Value) Else _command.Parameters.AddWithValue("@ExpMonth", expMonth)
            If expYear Is Nothing Then _command.Parameters.AddWithValue("@ExpYear", DBNull.Value) Else _command.Parameters.AddWithValue("@ExpYear", expYear)
            If cvd Is Nothing Then _command.Parameters.AddWithValue("@CVD", DBNull.Value) Else _command.Parameters.AddWithValue("@CVD", cvd)
            If isValid.ToString() Is Nothing Then _command.Parameters.AddWithValue("@IsValid", DBNull.Value) Else _command.Parameters.AddWithValue("@IsValid", isValid)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function InsertStartBillingDate(ByVal userId As Guid, ByVal startBillingDate As Date, ByVal isValid As Boolean) As Boolean
        Dim _dataSet As New System.Data.DataSet

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Insert Into PI_CreditCardInformation (UserId, StartBillingDate, IsValid) Values (@UserId, @StartBillingDate, @IsValid)"
            _command.Parameters.AddWithValue("@UserId", userId)
            _command.Parameters.AddWithValue("@StartBillingDate", startBillingDate)
            _command.Parameters.AddWithValue("@IsValid", isValid)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function GetNumberOfJobsByStoreId(ByVal storeId As Integer, ByVal startDate As DateTime, ByVal finishDate As DateTime) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            '    _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select Count(*) From PI_Job_Files Where StoreId=@StoreId And Cast(Convert(varchar, DateTime, 101) as datetime) >= Cast(Convert(varchar, @StartDate, 101) as datetime) And Cast(Convert(varchar, DateTime, 101) as datetime) <= Cast(Convert(varchar, @FinishDate, 101) as datetime)"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@StartDate", startDate)
            _command.Parameters.AddWithValue("@FinishDate", finishDate)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobsInfo")

            'Try
            '    Return _command.ExecuteNonQuery()
            'Catch ex As Exception
            '    Return 0
            'End Try
            '_connection.Close()
        End Using

        Return _dataSet

    End Function

    <WebMethod()> _
    Public Function GetInvoiceByStoreIdAndDate(ByVal storeId As Integer, ByVal startDate As DateTime, ByVal finishDate As DateTime) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_Invoices Where StoreId=@StoreId And Cast(Convert(varchar, StartDate, 101) as datetime)=Cast(Convert(varchar, @StartDate, 101) as datetime) And Cast(Convert(varchar, FinishDate, 101) as datetime)=Cast(Convert(varchar, @FinishDate, 101) as datetime)"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@StartDate", startDate)
            _command.Parameters.AddWithValue("@FinishDate", finishDate)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "InvoiceInfo")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetInvoicesByStoreId(ByVal storeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_Invoices Where StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "InvoiceInfo")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function GetInvoicePaidRefByInvoiceId(ByVal invoiceId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_Invoices Where InvoicePaidRef=@InvoiceId"
            _command.Parameters.AddWithValue("@InvoiceId", invoiceId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "InvoiceInfo")
        End Using
        Return _dataSet
    End Function


    'SELECT
    '(Select Count(*) From PI_Job_Files Where StoreId='1'And (DateTime>='12/01/2007' And DateTime <= '01/31/2008')) as TotalQuantity,
    '(Select PrePaidClicks From PI_PackageInformation Where PackageId='1') as PackageQuantity,
    '(Select PackagePrice From PI_PackageInformation Where PackageId='1') as PackagePrice,
    '((Select Count(*) From PI_Job_Files Where StoreId='1'And (DateTime>='12/01/2007' And DateTime <= '01/31/2008')) 
    '- (Select PrePaidClicks From PI_PackageInformation Where PackageId='1')) as ExtraQuantity,
    '(((Select Count(*) From PI_Job_Files Where StoreId='1'And (DateTime>='12/01/2007' And DateTime <= '01/31/2008')) 
    '- (Select PrePaidClicks From PI_PackageInformation Where PackageId='1'))
    '*(Select PricePerClick From PI_PackageInformation Where PackageId='1')) as ExtraPrice,
    ' ((Select PackagePrice From PI_PackageInformation Where PackageId='1')+
    '(((Select Count(*) From PI_Job_Files Where StoreId='1'And (DateTime>='12/01/2007' And DateTime <= '01/31/2008')) 
    '- (Select PrePaidClicks From PI_PackageInformation Where PackageId='1'))
    '*(Select PricePerClick From PI_PackageInformation Where PackageId='1')))as Subtotal,
    '((Select PackagePrice From PI_PackageInformation Where PackageId='1')+
    '((Select Count(*) From PI_Job_Files Where StoreId='1'And (DateTime>='12/01/2007' And DateTime <= '01/31/2008')) 
    '- (Select PrePaidClicks From PI_PackageInformation Where PackageId='1')))* 0.0 as Taxes,
    '( ((Select PackagePrice From PI_PackageInformation Where PackageId='1')+
    '(((Select Count(*) From PI_Job_Files Where StoreId='1'And (DateTime>='12/01/2007' And DateTime <= '01/31/2008')) 
    '- (Select PrePaidClicks From PI_PackageInformation Where PackageId='1'))
    '*(Select PricePerClick From PI_PackageInformation Where PackageId='1'))) +
    '((Select PackagePrice From PI_PackageInformation Where PackageId='1')+
    '((Select Count(*) From PI_Job_Files Where StoreId='1'And (DateTime>='12/01/2007' And DateTime <= '01/31/2008')) 
    '- (Select PrePaidClicks From PI_PackageInformation Where PackageId='1')))* 0.0) as Total


    <WebMethod()> _
    Public Function ChangeUserStatus(ByVal userId As Guid, ByVal actionDate As Date, ByVal action As String, ByVal affiliateName As String, ByVal notes As String) As Boolean
        Dim _dataSet As New System.Data.DataSet

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Insert Into PI_ChangeUserStatusHistory (UserId, ActionDate, Action, AffiliateName, Notes) Values (@UserId, @ActionDate, @Action, @AffiliateName, @Notes)"
            _command.Parameters.AddWithValue("@UserId", userId)
            _command.Parameters.AddWithValue("@ActionDate", actionDate)
            _command.Parameters.AddWithValue("@Action", action)
            _command.Parameters.AddWithValue("@AffiliateName", affiliateName)
            _command.Parameters.AddWithValue("@Notes", notes)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function InsertHistoryRecord(ByVal actionModeId As Integer, ByVal userId As Guid, ByVal actionDate As Date, ByVal action As String, ByVal affiliateName As String, ByVal notes As String, ByVal addInfo As String) As Boolean
        Dim _dataSet As New System.Data.DataSet

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Insert Into PI_AffiliateActionsHistory (ActionModeId, UserId, ActionDate, Action, AffiliateName, Notes, AddInfo) Values (@ActionModeId, @UserId, @ActionDate, @Action, @AffiliateName, @Notes, @AddInfo)"
            _command.Parameters.AddWithValue("@ActionModeId", actionModeId)
            _command.Parameters.AddWithValue("@UserId", userId)
            _command.Parameters.AddWithValue("@ActionDate", actionDate)
            _command.Parameters.AddWithValue("@Action", action)
            _command.Parameters.AddWithValue("@AffiliateName", affiliateName)
            _command.Parameters.AddWithValue("@Notes", notes)
            _command.Parameters.AddWithValue("@AddInfo", addInfo)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function GetPaymentTypeByPaymentId(ByVal paymentTypeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select PaymentType From PI_PaymentType Where PaymentTypeId=@PaymentTypeId"
            _command.Parameters.AddWithValue("@PaymentTypeId", paymentTypeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "Countries")
        End Using
        Return _dataSet
    End Function


    <WebMethod()> _
    Public Function GetFilesByJobId(ByVal jobId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_Job_Files Where JobId=@JobId"
            _command.Parameters.AddWithValue("@JobId", jobId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobFiles")
        End Using
        Return _dataSet
    End Function


    <WebMethod()> _
    Public Function GetFileByJobIdAndFileName(ByVal jobId As Integer, ByVal fileName As String) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_Job_Files Where JobId=@JobId and FileName=@FileName"
            _command.Parameters.AddWithValue("@JobId", jobId)
            _command.Parameters.AddWithValue("@FileName", fileName)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobFiles")
        End Using
        Return _dataSet
    End Function

    <WebMethod()> _
    Public Function DeleteJob(ByVal jobId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Begin tran " & _
                                   "Delete From PI_Job_Files Where JobId=@JobId " & _
                                   "Delete From PI_Job_Information Where JobId=@JobId " & _
                                   "Commit tran"
            _command.Parameters.AddWithValue("@JobId", jobId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function GetSectionsByStoreId(ByVal storeId As Integer, ByVal isActive As Boolean) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "SELECT ss.StoreId, ss.SectionId, ss.IsActive, s.SectionUrl, s.SectionName, s.SectionType FROM PI_SectionsForStores AS ss INNER JOIN PI_Sections AS s ON ss.SectionId = s.SectionId WHERE (ss.StoreId = @storeId) AND (ss.IsActive = @isActive)"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@IsActive", isActive)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "JobFiles")
        End Using
        Return _dataSet
    End Function


    <WebMethod()> _
    Public Function InsertSectionForStore(ByVal storeId As Integer, ByVal sectionId As Integer, ByVal isActive As Boolean) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "INSERT INTO PI_SectionsForStores (StoreId, SectionId, IsActive) Values (@StoreId, @SectionId, @IsActive)"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@SectionId", sectionId)
            _command.Parameters.AddWithValue("@IsActive", isActive)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function InsertHeadOfficeForStore(ByVal headOfficeId As Integer, ByVal storeId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "INSERT INTO PI_HeadOfficesAndStores (HeadOfficeId, StoreId) Values (@HeadOfficeId, @StoreId)"
            _command.Parameters.AddWithValue("@HeadOfficeId", headOfficeId)
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    <WebMethod()> _
    Public Function UpdateUserEmailByUserId(ByVal userId As Guid, ByVal email As String) As Boolean

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.CommandText = "Update aspnet_Membership Set Email=@Email, LoweredEmail=@LoweredEmail Where UserId=@UserId"
            _command.Connection = _connection
            _command.Parameters.AddWithValue("@Email", email)
            _command.Parameters.AddWithValue("@LoweredEmail", email.ToLower())
            _command.Parameters.AddWithValue("@UserId", userId)

            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As System.Exception
                Return False
            End Try
            _connection.Close()
        End Using

    End Function

    Public Function InsertStoreOrderCentreSettings(ByVal storeId As Integer, ByVal simpleLogin As Boolean, ByVal showTermsAndConditions As Boolean, ByVal showTopMenu As Boolean, ByVal backgroundColour As String, ByVal backgroundImage As String, ByVal backgroundRepeat As Boolean) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Insert Into PI_StoreOrderCentreSettings (StoreId, SimpleLogin, ShowTermsAndConditions, ShowTopMenu, BackgroundColour, BackgroundImage, BackgroundRepeat) Values (@StoreId, @SimpleLogin, @ShowTermsAndConditions, @ShowTopMenu, @BackgroundColour, @BackgroundImage, @BackgroundRepeat)"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@SimpleLogin", simpleLogin)
            _command.Parameters.AddWithValue("@ShowTermsAndConditions", showTermsAndConditions)
            _command.Parameters.AddWithValue("@ShowTopMenu", showTopMenu)
            _command.Parameters.AddWithValue("@BackgroundColour", backgroundColour)
            _command.Parameters.AddWithValue("@BackgroundImage", backgroundImage)
            _command.Parameters.AddWithValue("@BackgroundRepeat", backgroundRepeat)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    Public Function UpdateStoreOrderCentreSettings(ByVal storeId As Integer, ByVal simpleLogin As Boolean, ByVal showTermsAndConditions As Boolean) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_StoreOrderCentreSettings Set SimpleLogin=@SimpleLogin, ShowTermsAndConditions=@ShowTermsAndConditions Where StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@SimpleLogin", simpleLogin)
            _command.Parameters.AddWithValue("@ShowTermsAndConditions", showTermsAndConditions)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    Public Function UpdateStoreOrderCentreSettingsByMaster(ByVal storeId As Integer, ByVal simpleLogin As Boolean, ByVal showTermsAndConditions As Boolean, ByVal showTopMenu As Boolean, ByVal backgroundColour As String, ByVal backgroundImage As String, ByVal backgroundRepeat As Boolean) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_StoreOrderCentreSettings Set SimpleLogin=@SimpleLogin, ShowTermsAndConditions=@ShowTermsAndConditions, ShowTopMenu=@ShowTopMenu, BackgroundColour=@BackgroundColour, BackgroundImage=@BackgroundImage, BackgroundRepeat=@BackgroundRepeat Where StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@SimpleLogin", simpleLogin)
            _command.Parameters.AddWithValue("@ShowTermsAndConditions", showTermsAndConditions)
            _command.Parameters.AddWithValue("@ShowTopMenu", showTopMenu)
            _command.Parameters.AddWithValue("@BackgroundColour", backgroundColour)
            _command.Parameters.AddWithValue("@BackgroundImage", backgroundImage)
            _command.Parameters.AddWithValue("@BackgroundRepeat", backgroundRepeat)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function



    Public Function InsertUserIdAndStoreId(ByVal _userId As Guid, ByVal _storeId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Insert into PI_UsersAndStores (UserId, StoreId) Values (@UserId, @StoreId)"
            _command.Parameters.AddWithValue("@UserId", _userId)
            _command.Parameters.AddWithValue("@StoreId", _storeId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

End Class