Imports System.Web Imports System.Web.Services Imports System.Web.Services.Protocols _ _ _ Public Class WebWS Inherits System.Web.Services.WebService Public _connectionString As String = ConfigurationManager.ConnectionStrings("PrintItConnectionString").ConnectionString ' _ '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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 ' '-------------------------' _ 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 _ 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 _ 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 _ 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 _ 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 ''' ''' This will get settings values per language for a particular setting id. ''' ''' SettingId to query. ''' Language as ISO 2 code. ''' DataSet Object ''' _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 ' '---------------------------' _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 ' _ '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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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 _ 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