
CREATE PROCEDURE dbo.bvc_GiftCertificate_ByOrderID_s 

@ID int

AS

	SELECT CertificateID,Amount,OrderNumber,AmountUsed FROM bvc_GiftCertificate WHERE OrderNumber=@ID

GO

CREATE PROCEDURE [dbo].bvc_FraudCheckDomain_i

@DomainName as nvarchar(255)

AS
	BEGIN
		DELETE FROM bvc_FraudCheckDomain WHERE DomainName=@DomainName
		INSERT INTO bvc_FraudCheckDomain (DomainName) VALUES (@DomainName)
	END
	
	RETURN 
GO


CREATE PROCEDURE [dbo].bvc_FraudCheckIP_d

@IPAddress as nvarchar(15)

AS
	BEGIN
		DELETE FROM bvc_FraudCheckIP WHERE IPAddress=@IPAddress
	END
	
	RETURN 

GO


ALTER PROCEDURE dbo.bvc_Orders_ByAffiliate_s
@Records int = 0,
@AffiliateID int,
@StartDate datetime,
@EndDate datetime

AS
	IF @Records > 0 
		SET ROWCOUNT @RECORDS
	ELSE
		SET ROWCOUNT 0
	
BEGIN
	SELECT [ID],SubTotal,
	TimeOfOrder,
	UserName,CompletedOrderID 
	FROM bvc_Order
	WHERE  AffiliateID=@AffiliateID
    AND TimeOfOrder >= @StartDate 
    AND TimeofOrder <= @EndDate 
    AND (StatusCode=2 OR StatusCode=3)
    AND PaymentStatus=30    
END

SET ROWCOUNT 0
	
	RETURN
GO

ALTER PROCEDURE dbo.bvc_Orders_ByDate_s

@StartDate datetime,
@EndDate datetime

AS
	SELECT [ID],GrandTotal,TimeOfOrder,
	UserName,
	SubTotal,
	TaxTotal,
	ShippingTotal,
	OrderDiscountsTotal,
	ShippingDiscountsTotal,
	HandlingFee,
	GiftWrapCharge,
	CompletedOrderID	 
	FROM bvc_Order 
	WHERE TimeOfOrder >= @StartDate 
	AND TimeOfOrder <= @EndDate 
	AND (StatusCode=3 OR StatusCode=2)
	AND (PaymentStatus=30)
	ORDER BY [ID] DESC
	
	
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Manufacturer_i
@DisplayName nvarchar(255),
@EmailAddress nvarchar(255) = '',
@Address ntext

AS
	INSERT INTO
	bvc_Manufacturer
	(DisplayName,EmailAddress,Address) 
	VALUES
	(@DisplayName,@EmailAddress,@Address)
	
	 SELECT SCOPE_IDENTITY()  as NewID
	 
	RETURN
GO


CREATE PROCEDURE [dbo].bvc_FraudCheckEmail_i

@EmailAddress as nvarchar(255)

AS
	BEGIN
		DELETE FROM bvc_FraudCheckEmail WHERE EmailAddress=@EmailAddress
		INSERT INTO bvc_FraudCheckEmail (EmailAddress) VALUES (@EmailAddress)
	END
	
	RETURN 
GO

CREATE PROCEDURE [dbo].bvc_FraudCheck_s

@IPAddress nvarchar(15),
@DomainName nvarchar(255),
@EmailAddress nvarchar(255)

AS
	BEGIN
		IF @IPAddress = ''			
			SELECT IPAddress FROM bvc_FraudCheckIP ORDER BY IPAddress			
		ELSE			
			SELECT IPAddress FROM bvc_FraudCheckIP WHERE IPAddress=@IPAddress			
			
		
		IF @DomainName = ''
			SELECT DomainName FROM bvc_FraudCheckDomain ORDER By DomainName
		ELSE
			SELECT DomainName FROM bvc_FraudCheckDomain WHERE DomainName=@DomainName
			
		IF @EmailAddress = ''
			SELECT EmailAddress FROM bvc_FraudCheckEmail ORDER By EmailAddress
		ELSE
			SELECT EmailAddress FROM bvc_FraudCheckEmail WHERE EmailAddress=@EmailAddress
			
	END
	
	RETURN 

GO

CREATE PROCEDURE [dbo].bvc_FraudCheckDomain_d

@DomainName as nvarchar(255)

AS
	BEGIN
		DELETE FROM bvc_FraudCheckDomain WHERE DomainName=@DomainName
	END
	
	RETURN 

GO


ALTER PROCEDURE dbo.bvc_Vendor_i
@DisplayName nvarchar(255),
@EmailAddress nvarchar(255) = '',
@Address ntext

AS
	INSERT INTO
	bvc_Vendor
	(DisplayName,EmailAddress,Address) 
	VALUES
	(@DisplayName,@EmailAddress,@Address)
	
	 SELECT SCOPE_IDENTITY()  as NewID
	 
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Vendor_u
@ID int,
@DisplayName nvarchar(255),
@EmailAddress nvarchar(255),
@Address ntext

AS
	UPDATE
		bvc_Vendor
	SET 
	DisplayName=@DisplayName,
	EmailAddress=@EmailAddress,
	Address=@Address
	WHERE
	[ID]=@ID
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Manufacturers_s
AS
	SELECT [ID],DisplayName,EmailAddress,Address FROM bvc_Manufacturer
	ORDER BY DisplayName
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Vendors_s
AS
	SELECT [ID],DisplayName,EmailAddress,Address FROM bvc_Vendor
	ORDER BY DisplayName
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Vendors_Alphabetical_s

@StartCharacter nvarchar(1) = 'A',
@EndCharacter nvarchar(20) = 'ZZZZZZZZZZZ'

AS

SELECT [ID],DisplayName,EmailAddress,Address FROM bvc_Vendor WHERE
	[DisplayName] >= @StartCharacter
	AND
	[DisplayName] < @EndCharacter
	ORDER BY DisplayName
	            
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Vendor_s
@ID int
AS
	SELECT [ID],DisplayName,EmailAddress,Address FROM bvc_Vendor
	  WHERE  [ID]=@ID
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Manufacturers_AlphabeticalCount_s

AS

SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'A' AND [DisplayName] < 'B'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'B' AND [DisplayName] < 'C'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'C' AND [DisplayName] < 'D'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'D' AND [DisplayName] < 'E'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'E' AND [DisplayName] < 'F'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'F' AND [DisplayName] < 'G'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'G' AND [DisplayName] < 'H'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'H' AND [DisplayName] < 'I'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'I' AND [DisplayName] < 'J'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'J' AND [DisplayName] < 'K'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'K' AND [DisplayName] < 'L'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'L' AND [DisplayName] < 'M'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'M' AND [DisplayName] < 'N'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'N' AND [DisplayName] < 'O'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'O' AND [DisplayName] < 'P'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'P' AND [DisplayName] < 'Q'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'Q' AND [DisplayName] < 'R'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'R' AND [DisplayName] < 'S'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'S' AND [DisplayName] < 'T'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'T' AND [DisplayName] < 'U'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'U' AND [DisplayName] < 'V'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'V' AND [DisplayName] < 'W'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'W' AND [DisplayName] < 'X'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'X' AND [DisplayName] < 'Y'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'Y' AND [DisplayName] < 'Z'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] >= 'Z'
SELECT COUNT([ID]) as UserCount FROM bvc_Manufacturer WHERE [DisplayName] < 'A'
GO

CREATE PROCEDURE [dbo].bvc_FraudCheckIP_i

@IPAddress as nvarchar(15)

AS
	BEGIN
		DELETE FROM bvc_FraudCheckIP WHERE IPAddress=@IPAddress
		INSERT INTO bvc_FraudCheckIP (IPAddress) VALUES (@IPAddress)
	END
	
	RETURN 

GO


ALTER PROCEDURE dbo.bvc_Manufacturers_Alphabetical_s

@StartCharacter nvarchar(1) = 'A',
@EndCharacter nvarchar(20) = 'ZZZZZZZZZZZ'

AS

SELECT [ID],DisplayName,EmailAddress,Address FROM bvc_Manufacturer WHERE
	[DisplayName] >= @StartCharacter
	AND
	[DisplayName] < @EndCharacter
ORDER BY DisplayName
	            
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Manufacturer_u
@ID int,
@DisplayName nvarchar(255),
@EmailAddress nvarchar(255),
@Address ntext

AS
	UPDATE
		bvc_Manufacturer
	SET 
	DisplayName=@DisplayName,
	EmailAddress=@EmailAddress,
	Address=@Address
	WHERE
	[ID]=@ID
	RETURN
GO

CREATE PROCEDURE [dbo].bvc_FraudCheckEmail_d

@EmailAddress as nvarchar(255)

AS
	BEGIN
		DELETE FROM bvc_FraudCheckEmail WHERE EmailAddress=@EmailAddress
	END
	
	RETURN 
GO


ALTER PROCEDURE dbo.bvc_Vendors_AlphabeticalCount_s

AS

SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'A' AND [DisplayName] < 'B'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'B' AND [DisplayName] < 'C'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'C' AND [DisplayName] < 'D'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'D' AND [DisplayName] < 'E'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'E' AND [DisplayName] < 'F'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'F' AND [DisplayName] < 'G'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'G' AND [DisplayName] < 'H'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'H' AND [DisplayName] < 'I'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'I' AND [DisplayName] < 'J'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'J' AND [DisplayName] < 'K'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'K' AND [DisplayName] < 'L'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'L' AND [DisplayName] < 'M'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'M' AND [DisplayName] < 'N'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'N' AND [DisplayName] < 'O'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'O' AND [DisplayName] < 'P'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'P' AND [DisplayName] < 'Q'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'Q' AND [DisplayName] < 'R'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'R' AND [DisplayName] < 'S'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'S' AND [DisplayName] < 'T'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'T' AND [DisplayName] < 'U'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'U' AND [DisplayName] < 'V'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'V' AND [DisplayName] < 'W'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'W' AND [DisplayName] < 'X'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'X' AND [DisplayName] < 'Y'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'Y' AND [DisplayName] < 'Z'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] >= 'Z'
SELECT COUNT([ID]) as UserCount FROM bvc_Vendor WHERE [DisplayName] < 'A'

GO


ALTER PROCEDURE dbo.bvc_Manufacturer_s
@ID int
AS
	SELECT [ID],DisplayName,EmailAddress,Address FROM bvc_Manufacturer
	  WHERE  [ID]=@ID
	RETURN
GO


ALTER PROCEDURE dbo.bvc_OrderPayment_i 

@AuditDate datetime,
@PaymentType int,
@Amount float,
@CheckNumber nvarchar(50),
@GiftCertificateNumber nvarchar(50),
@CreditCardNumber nvarchar(50),
@CreditCardType int,
@CreditCardExpMonth int,
@CreditCardExpYear int,
@CreditCardHolder nvarchar(100),
@TransactionReferenceNumber nvarchar(200),
@TransactionResponseCode nvarchar(200),
@Note ntext,
@PaymentMethod int,
@OrderID int,
@PurchaseOrderNumber nvarchar(100),
@AuthorizationOnly int = 0

AS

	INSERT INTO bvc_OrderPayment
	(
	AuditDate,
	PaymentType,
	Amount,
	CheckNumber,
	GiftCertificateNumber,
	CreditCardNumber,
	CreditCardType,
	CreditCardExpMonth,
	CreditCardExpYear,
	CreditCardHolder,
	TransactionReferenceNumber,
	TransactionResponseCode,
	Note,
	PaymentMethod,
	OrderID,
	PurchaseOrderNumber,
	AuthorizationOnly
	)
	VALUES
	(
	@AuditDate,
	@PaymentType,
	@Amount,
	@CheckNumber,
	@GiftCertificateNumber,
	@CreditCardNumber,
	@CreditCardType,
	@CreditCardExpMonth,
	@CreditCardExpYear,
	@CreditCardHolder,
	@TransactionReferenceNumber,
	@TransactionResponseCode,
	@Note,
	@PaymentMethod,
	@OrderID,
	@PurchaseOrderNumber,
	@AuthorizationOnly
	)
	
	 SELECT SCOPE_IDENTITY()  as NewID
GO



ALTER PROCEDURE dbo.bvc_OrderItem_i

	@OrderID int, 
	@ProductID nvarchar(50),
	@Qty int, 
	@DisplayName nvarchar(255), 
	@OriginalBasePrice float,
	@SitePrice float,
	@AdjustedSitePrice float,
	@SiteCost float,
	@TaxExempt int,
	@TaxClass int,
	@NonShipping int,
	@ShipSeparately int,
	@DropShipMode int,
	@DropShipAddressID int,
	@LineTotal float, 
	@LineTaxTotal float,
	@PackageID int,		
	@StatusCode int,
	@Weight float,
	@Length float,
	@Width float,
	@Height float,
	@MinimumQty int,
	@DisplayDescription ntext,
	@ImageURL ntext,
	@ExtraInformation ntext,
	@GiftWrap int,
	@GiftWrapMessage nvarchar(255),
	@GiftWrapAllowed int,
	@InventoryKey nvarchar(255),
	@ShippingCost float = 0,
	@QtyShipped int,
	@QtyReturned int,
	@ParentID int = 0
	
AS
	INSERT INTO bvc_OrderItem
	(
	OrderID,
	ProductID,
	Qty,
	DisplayName,
	OriginalBasePrice,
	SitePrice,
	AdjustedSitePrice,
	SiteCost,
	TaxExempt,
	TaxClass,
	NonShipping,
	ShipSeparately,
	DropShipMode,
	DropShipAddressID,
	LineTotal,
	LineTaxTotal,
	PackageID,
	StatusCode,
	Weight,
	Length,
	Width,
	Height,
	MinimumQty,
	DisplayDescription,
	ImageURL,
	ExtraInformation,
	GiftWrap,
	GiftWrapMessage,
	GiftWrapAllowed,
	InventoryKey,
	ShippingCost,
	QtyShipped,
	QtyReturned,
	ParentID
	)	
	VALUES
	(
	@OrderID,
	@ProductID,
	@Qty,
	@DisplayName,
	@OriginalBasePrice,
	@SitePrice,
	@AdjustedSitePrice,
	@SiteCost,
	@TaxExempt,
	@TaxClass,
	@NonShipping,
	@ShipSeparately,
	@DropShipMode,
	@DropShipAddressID,
	@LineTotal,
	@LineTaxTotal,
	@PackageID,
	@StatusCode,
	@Weight,
	@Length,
	@Width,
	@Height,
	@MinimumQty,
	@DisplayDescription,
	@ImageURL,
	@ExtraInformation,
	@GiftWrap,
	@GiftWrapMessage,
	@GiftWrapAllowed,
	@InventoryKey,
	@ShippingCost,
	@QtyShipped,
	@QtyReturned,
	@ParentID
	)
	
	 SELECT SCOPE_IDENTITY()  as NewID
	
	RETURN
GO




 



ALTER PROCEDURE dbo.bvc_UserAccount_i

@Username nvarchar(100),
@Password nvarchar(50),
@FirstName nvarchar(50),
@LastName nvarchar(100),
@Salt nvarchar(50),
@TaxExempt int,
@Email nvarchar(100),
@CreationDate datetime,
@LastLoginDate datetime,
@PasswordHint nvarchar(100),
@Comment ntext,
@PricingLevel int = 0,
@AddressBook ntext

AS

	INSERT INTO bvc_User
	([UserName],[Password],[FirstName],[LastName],Salt,
	TaxExempt,Email,CreationDate,LastLoginDate,PasswordHint,Comment,PricingLevel,AddressBook) 
	VALUES(@UserName,@Password,@FirstName,@LastName,@Salt,
	@TaxExempt,@Email,@CreationDate,
	@LastLoginDate,@PasswordHint,@Comment,@PricingLevel,@AddressBook)
			
	SELECT SCOPE_IDENTITY()  as NewID

	RETURN
GO


ALTER PROCEDURE dbo.bvc_Products_ByKeyword_s 

@MaxResults int = 1000,
@Keyword nvarchar(256),
@SortType int = 9999,
@ShowInactive as integer = 0

AS

	SET @Keyword = '%' + @Keyword + '%'

	BEGIN
	SET ROWCOUNT @MaxResults

	
	IF @SortType = 2
		BEGIN

			IF @ShowInactive = 1
				BEGIN
					SELECT 
					 [ID],ProductName, SitePrice,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
					FROM bvc_Product 
					WHERE (ParentID=-1) 
					AND ((ProductName LIKE @Keyword) OR 
					          (MetaDescription LIKE @Keyword) OR 
					          (MetaKeywords LIKE @Keyword) OR
					          (ShortDescription LIKE @Keyword) OR
					          (LongDescription LIKE @Keyword) OR
					          ([ID] LIKE @Keyword)) 
					AND (status > -1) 
					ORDER BY SitePrice ASC
				END
			ELSE
				BEGIN
					SELECT 
					 [ID],ProductName, SitePrice,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
					FROM bvc_Product 
					WHERE (ParentID=-1) 
					AND (InventoryStatus > 0) 
					AND ((ProductName LIKE @Keyword) OR 
					          (MetaDescription LIKE @Keyword) OR 
					          (MetaKeywords LIKE @Keyword) OR
					          (ShortDescription LIKE @Keyword) OR
					          (LongDescription LIKE @Keyword) OR
					          ([ID] LIKE @Keyword)) 
					AND (status > 0) 
					ORDER BY SitePrice ASC
				END
		END


	ELSE
		BEGIN


			IF @ShowInactive = 1
				BEGIN
					SELECT 
					 [ID],ProductName, SitePrice,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
					FROM bvc_Product 
					WHERE (ParentID=-1) 
					AND ((ProductName LIKE @Keyword) OR 
					          (MetaDescription LIKE @Keyword) OR 
					          (MetaKeywords LIKE @Keyword) OR
					          (ShortDescription LIKE @Keyword) OR
					          (LongDescription LIKE @Keyword) OR
					          ([ID] LIKE @Keyword)) 
					AND (status > -1) 
					ORDER BY ProductName
				END
			ELSE
				BEGIN
					SELECT 
					 [ID],ProductName, SitePrice,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
					FROM bvc_Product 
					WHERE (ParentID=-1) 
					AND (InventoryStatus > 0) 
					AND ((ProductName LIKE @Keyword) OR 
					          (MetaDescription LIKE @Keyword) OR 
					          (MetaKeywords LIKE @Keyword) OR
					          (ShortDescription LIKE @Keyword) OR
					          (LongDescription LIKE @Keyword) OR
					          ([ID] LIKE @Keyword)) 
					AND (status > 0) 
					ORDER BY ProductName
				END

			
		END

	SET ROWCOUNT 0
	END
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO


ALTER  PROCEDURE [dbo].[bvc_UserAccount_SearchByUsername_s] 

@Keyword nvarchar(255),
@MaxResults integer = 0

AS
	BEGIN
	SET ROWCOUNT @MaxResults
	
	SELECT 
	[ID],[UserName],[Password],[Firstname],[LastName],Salt,
	TaxExempt,Email,CreationDate,LastLoginDate,PasswordHint,Comment,PricingLevel,AddressBook
	FROM bvc_User WHERE UserName LIKE '%' + @Keyword + '%'
	ORDER BY Username

	SET ROWCOUNT 0
	END

	RETURN

GO


 


ALTER PROCEDURE dbo.bvc_Discount_s

@ID int

AS
	SELECT [ID],StartDate,EndDate,ApplyTo,AmountType,Amount,
	QualifyQty,QualifyTotal,RequiresCoupon,CouponCode, MaxUsesPerCustomer,ExclusiveOffer,ShipMethodID
	
	FROM bvc_Discount WHERE [ID]=@ID
	
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Affiliate_u

@ID int,
@DisplayName nvarchar(255),
@Address ntext,
@CommissionAmount float,
@CommissionType int,
@ReferralDays int,
@TaxID nvarchar(100),
@DriversLicenseNumber nvarchar(100),
@WebSiteURL nvarchar(1000),
@StyleSheet nvarchar(1000)

AS
	Update 
	bvc_Affiliate 
	SET 
	DisplayName=@DisplayName,
	Address=@Address,
	CommissionAmount=@CommissionAmount,
	CommissionType=@CommissionType,
	ReferralDays=@ReferralDays,
	TaxID=@TaxID,
	DriversLicenseNumber=@DriversLicenseNumber,
	WebSiteURL=@WebSiteURL,
	StyleSheet=@StyleSheet
	WHERE
	[ID]=@id
	RETURN
GO




 


ALTER PROCEDURE dbo.bvc_UserAccount_ByName_s

@UserName nvarchar(100)

AS
	SELECT 
	[ID],[UserName],[Password],[Firstname],[LastName],Salt,
	TaxExempt,Email,CreationDate,LastLoginDate,PasswordHint,Comment,PricingLevel,AddressBook
	FROM bvc_User WHERE UserName=@UserName
	
	RETURN
GO




 


ALTER PROCEDURE dbo.bvc_Product_s

@ID nvarchar(50)

AS
	SELECT
	
	 [ID],
	ProductTypeID,
	ProductName,
	ListPrice,
	SitePrice,
	SiteCost,
	MetaKeywords,
	MetaDescription,
	MetaTitle,
	TaxExempt,
	TaxClass,
	NonShipping,
	ShipSeparately,
	DropShipMode,

	ShippingWeight,
	ShippingLength,
	ShippingWidth,
	ShippingHeight,
	Status,
	ImageFileSmall,
	ImageFileMedium,
	CreationDate,
	MinimumQty,
	ParentID,
	VariantDisplayMode,
	InventoryAvailableQty,
	InventoryLowStockNotice,
	InventoryStatus,
	InventoryNotAvailableStatus,
	InventoryNotAvailableMessage,
	ShortDescription,
	LongDescription,
	ManufacturerID,
	VendorID,
	GiftWrapAllowed,
	ExtraShipFee

	FROM bvc_Product
	WHERE [ID]=@ID
	
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Order_i

@AffiliateID int,
@BillingAddress ntext,
@CompletedOrderID nvarchar(50),
@Downloadkey nvarchar(50),
@GrandTotal float,
@HandlingFee float,
@OrderDiscountsTotal float,
@PaymentStatus int,
@Salt nvarchar(50),
@ShippingDiscountsTotal float,
@ShippingTotal float,
@StatusCode int,
@SubTotal float,
@TaxTotal float,
@TimeOfOrder datetime,
@UserID int,
@UserName nvarchar(100),
@GiftWrapCharge float,
@Instructions ntext,
@LastUpdated datetime,
@ShippingStatus int

AS
	INSERT INTO bvc_Order 
	(CompletedOrderID,
	UserID,
	UserName,
	BillingAddress,
	SubTotal,
	TaxTotal,
	ShippingTotal,
	OrderDiscountsTotal,
	ShippingDiscountsTotal,
	GrandTotal,
	TimeOfOrder, 
	StatusCode,
	PaymentStatus,
	AffiliateID,
	HandlingFee,
	DownloadKey,
	LastUpdated,
	Salt,
	GiftWrapCharge,
	Instructions,
	ShippingStatus
	)
	 VALUES
	(@CompletedOrderID,
	 @UserID,
	@UserName,
	@BillingAddress,
	@SubTotal,
	@TaxTotal,
	@ShippingTotal,
	@OrderDiscountsTotal,
	@ShippingDiscountsTotal,
	@GrandTotal,
	@TimeOfOrder,
	@StatusCode,
	@PaymentStatus,
	@AffiliateID,
	@HandlingFee,
	@Downloadkey,
	@LastUpdated,
	@Salt,
	@GiftWrapCharge,
	@Instructions,
	@ShippingStatus
	) 
	 
	 SELECT SCOPE_IDENTITY()  as NewID 
	 
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Products_ByManufacturer_s

@ID int,
@SortType int = 9999,
@Admin int = 0


AS
	IF @Admin=1
		BEGIN
			SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
				FROM bvc_Product 
				WHERE ManufacturerID = @ID		
		END
	ELSE
		BEGIN
		IF @SortType = 2
			BEGIN
				SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
				FROM bvc_Product 
				WHERE ManufacturerID = @ID
				AND ParentID=-1
				AND InventoryStatus >0
				AND Status > 0
				ORDER BY SitePrice ASC
			END				
		ELSE
			BEGIN	
				SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
				FROM bvc_Product 
				WHERE ManufacturerID=@ID 
				AND ParentID=-1
				AND InventoryStatus >0
				AND Status > 0
				ORDER BY ProductName		
			END					

		END

	RETURN
GO


ALTER PROCEDURE dbo.bvc_Category_GetProducts_s
@ID int,
@SortType int = 9999
AS

	IF @SortType = 2
		BEGIN
			SELECT [ID],ProductName,imagefileSmall,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount 
			FROM bvc_Product 
			WHERE ParentID=-1 
			AND InventoryStatus>0 
			AND Status > 0
			AND  ( [ID]  IN (SELECT ProductID FROM bvc_ProductXCategory 
			WHERE  CategoryID=@ID)) 
			ORDER BY SitePrice ASC
		END
	ELSE
		BEGIN
			SELECT [ID],ProductName,imagefileSmall,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount 
			FROM bvc_Product 
			WHERE ParentID=-1 
			AND InventoryStatus>0 
			AND Status > 0
			AND  ( [ID]  IN (SELECT ProductID FROM bvc_ProductXCategory 
			WHERE  CategoryID=@ID)) 
			ORDER BY ProductName
		END
	
	RETURN
GO


ALTER PROCEDURE dbo.bvc_OrderItem_s

@ID int

AS
	SELECT
	[ID],
	OrderID,
	ProductID,
	Qty,
	DisplayName,
	OriginalBasePrice,
	SitePrice,
	AdjustedSitePrice,
	SiteCost,
	TaxExempt,
	TaxClass,
	NonShipping,
	ShipSeparately,
	DropShipMode,
	DropShipAddressID,
	LineTotal,
	LineTaxTotal,
	PackageID,
	StatusCode,
	Weight,
	Length,
	Width,
	Height,
	MinimumQty,
	DisplayDescription,
	(SELECT [Name] FROM bvc_OrderItemStatus WHERE [ID]=StatusCode) as StatusName,
	ImageURL,
	ExtraInformation,
	GiftWrap,
	GiftWrapMessage,
	GiftWrapAllowed,
	InventoryKey,
	ShippingCost,
	QtyShipped,
	QtyReturned,
	ParentID
	
	FROM 
	bvc_OrderItem
	WHERE bvc_OrderItem.ID=@ID
	
	RETURN
GO


ALTER PROCEDURE dbo.bvc_ProductInventory_u 

@ProductID nvarchar(50),
@InventoryKey nvarchar(255),
@Qty int,
@SKU nvarchar(50) = @ProductID

AS
	BEGIN
		UPDATE bvc_ProductInventory
		SET
		Qty=@Qty,
		SKU=@SKU
		WHERE
		ProductID=@ProductID AND InventoryKey=@InventoryKey					
	END
GO


ALTER PROCEDURE  dbo.bvc_Products_Latest_s

@Qty int,
@SortType int = 9999

AS
	IF @SortType = 1
		BEGIN
			SET ROWCOUNT @Qty
			SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall ,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
			FROM bvc_Product 
			WHERE  ParentID=-1
			AND InventoryStatus>0 AND Status > 0
			ORDER BY Productname
			SET ROWCOUNT 0		
		END	
	ELSE
		BEGIN
			IF @SortType = 2 
				BEGIN
					SET ROWCOUNT @Qty
					SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall ,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
					FROM bvc_Product 
					WHERE  ParentID=-1
					AND InventoryStatus>0 AND Status > 0
					ORDER BY SitePrice ASC
					SET ROWCOUNT 0		
				END	

			ELSE
				BEGIN
					SET ROWCOUNT @Qty
					SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall ,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
					FROM bvc_Product 
					WHERE  ParentID=-1
					AND InventoryStatus>0 AND Status > 0
					ORDER BY CreationDate DESC,[ID] DESC
					SET ROWCOUNT 0		
				END	
		END			
	
	RETURN
GO




 


ALTER PROCEDURE dbo.bvc_Order_s

@ID int

AS

	BEGIN


	/* Select Order Info */	
	SELECT 
	bvc_Order.[ID],
	AffiliateID ,
	BillingAddress,
	CompletedOrderID ,
	Downloadkey,
	GrandTotal ,
	HandlingFee,
	OrderDiscountsTotal ,
	PaymentStatus ,
	Salt,
	ShippingDiscountsTotal ,
	ShippingTotal ,
	StatusCode ,
	SubTotal ,
	TaxTotal ,
	TimeOfOrder ,
	UserID,
	UserName,LastUpdated,
	GiftWrapCharge,Instructions,
	ShippingStatus
	 FROM 
	  bvc_Order 
	    WHERE 
	     bvc_Order.[ID]=@ID
	     
	
	/* Select Items for Order */
	SELECT
	[ID],
	OrderID,
	ProductID,
	Qty,
	DisplayName,
	OriginalBasePrice,
	SitePrice,
	AdjustedSitePrice,
	SiteCost,
	TaxExempt,
	TaxClass,
	NonShipping,
	ShipSeparately,
	DropShipMode,
	DropShipAddressID,
	LineTotal,
	LineTaxTotal,
	PackageID,
	StatusCode,
	Weight,
	Length,
	Width,
	Height,
	MinimumQty,
	DisplayDescription,
	(SELECT [Name] FROM bvc_OrderItemStatus WHERE [ID]=StatusCode) as StatusName,
	ImageURL,
	ExtraInformation,
	GiftWrap,
	GiftWrapMessage,
	GiftWrapAllowed,
	ShippingCost,
	QtyShipped,
	QtyReturned,
	ParentID
	FROM 
	bvc_OrderItem
	WHERE bvc_OrderItem.OrderID=@ID


	/* Select Coupons */
	SELECT CouponCode,OrderID FROM bvc_OrderCoupon WHERE OrderID=@ID

	/* Select Notes */
	SELECT [ID],OrderID,AuditDate,Note FROM bvc_OrderNote WHERE OrderID=@ID
	ORDER By AuditDate

	/* Select Payment Records */
	SELECT
	[ID],
	AuditDate,
	PaymentType,
	Amount,
	CheckNumber,
	GiftCertificateNumber,
	CreditCardNumber,
	CreditCardType,
	CreditCardExpMonth,
	CreditCardExpYear,
	CreditCardHolder,
	TransactionReferenceNumber,
	TransactionResponseCode,
	Note,
	PaymentMethod,
	OrderID,
	PurchaseOrderNumber,
	AuthorizationOnly
	FROM bvc_OrderPayment
	WHERE OrderID=@ID
	ORDER By AuditDate


	END    
            
	RETURN
GO

CREATE PROCEDURE [dbo].bvc_Order_Status_u
@ID int,
@StatusCode int

AS
 
 UPDATE bvc_Order SET 

	StatusCode=@StatusCode

  WHERE [ID]=@ID
            
	RETURN

GO

SET ANSI_NULLS OFF
GO


ALTER  PROCEDURE [dbo].[bvc_UserAccount_SearchByEmail_s] 

@Keyword nvarchar(255),
@MaxResults integer = 0

AS
	BEGIN
	SET ROWCOUNT @MaxResults

	SELECT
	[ID],[UserName],[Password],[Firstname],[LastName],Salt,
	TaxExempt,Email,CreationDate,LastLoginDate,PasswordHint,Comment,PricingLevel,AddressBook
	FROM bvc_User WHERE Email LIKE '%' + @Keyword + '%'
	ORDER BY Email

	SET ROWCOUNT 0
	END

	RETURN

GO

SET ANSI_NULLS ON
GO


ALTER PROCEDURE  dbo.bvc_Products_LowStock_s

AS
	BEGIN

	SELECT [ID],ProductName,InventoryAvailableQty
	FROM bvc_Product 
	WHERE InventoryAvailableQty < InventoryLowStockNotice
	AND InventoryNotAvailableStatus <> 1
	AND [ID] NOT IN (SELECT ProductID FROM bvc_ProductInventory)
	ORDER BY [ID]
		

	SELECT [ID],ProductName,InventoryKey,Qty,SKU FROM bvc_Product bvcp JOIN
	bvc_ProductInventory bvcpi ON bvcp.ID = bvcpi.ProductID
	WHERE
		bvcp.InventoryNotAvailableStatus <> 1
		AND bvcpi.Qty < bvcp.InventoryLowStockNotice
	END				
	
	RETURN

GO

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].bvc_OrderManager2_OnHold_s

@StartDate as DateTime,
@EndDate as DateTime

AS	
	
	SELECT [ID],GrandTotal,TimeOfOrder,UserName,completedOrderID,BillingAddress,StatusCode,PaymentStatus,ShippingStatus
			FROM  bvc_Order 			
			WHERE TimeOfOrder >= @StartDate 
			AND TimeOfOrder <= @EndDate 			
			AND StatusCode =200 
			ORDER BY CompletedOrderID DESC
	
	RETURN
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.bvc_Category_GetProducts_IgnoreInventory_s
@ID int
AS
	SELECT 
	[ID],
	ProductName,ImageFileSmall,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
	FROM bvc_Product 
	WHERE ParentID=-1 
	AND  ( [ID]  IN 
		(SELECT ProductID FROM bvc_ProductXCategory 
		WHERE  CategoryID=@ID)) ORDER BY ProductName
		
	RETURN
GO

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].bvc_OrderManager2_Completed_s

@StartDate as DateTime,
@EndDate as DateTime

AS	
	
	SELECT [ID],GrandTotal,TimeOfOrder,UserName,completedOrderID,BillingAddress,StatusCode,PaymentStatus,ShippingStatus
			FROM  bvc_Order 			
			WHERE TimeOfOrder >= @StartDate 
			AND TimeOfOrder <= @EndDate 			
			AND StatusCode = 3
			ORDER BY CompletedOrderID DESC
	
	RETURN
GO

CREATE PROCEDURE [dbo].bvc_OrderManager2_All_s

@StartDate as DateTime,
@EndDate as DateTime

AS	
	
	SELECT [ID],GrandTotal,TimeOfOrder,UserName,completedOrderID,BillingAddress,StatusCode,PaymentStatus,ShippingStatus
			FROM  bvc_Order 			
			WHERE TimeOfOrder >= @StartDate 
			AND TimeOfOrder <= @EndDate 			
			AND StatusCode <> 1 AND StatusCode <> 999 AND StatusCode <> -1
			ORDER BY CompletedOrderID DESC
	
	RETURN
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE  dbo.bvc_Product_All_s


AS
	
			SELECT
			 [ID],
			ProductTypeID,
			ProductName,
			ListPrice,
			SitePrice,
			SiteCost,
			MetaKeywords,
			MetaDescription,
			MetaTitle,
			TaxExempt,
			TaxClass,
			NonShipping,
			ShipSeparately,
			DropShipMode,
			ShippingWeight,
			ShippingLength,
			ShippingWidth,
			ShippingHeight,
			Status,
			ImageFileSmall,
			ImageFileMedium,
			CreationDate,
			MinimumQty,
			ParentID,
			VariantDisplayMode,
			InventoryAvailableQty,
			InventoryLowStockNotice,
			InventoryStatus,
			InventoryNotAvailableStatus,
			InventoryNotAvailableMessage,
			ShortDescription,
			LongDescription,
			ManufacturerID,
			VendorID,
			GiftWrapAllowed,
			ExtraShipFee
			FROM bvc_Product
			ORDER BY ProductName 
		

	
	RETURN
GO




ALTER PROCEDURE dbo.bvc_Orders_PowerSearch_s

@Keyword nvarchar(255),
@Status int,
@PaymentStatus int,
@StartDate datetime,
@EndDate datetime

AS

IF @Status <> 0
BEGIN
	IF @PaymentStatus <> 0 
		BEGIN
			SELECT [ID],GrandTotal,TimeOfOrder,UserName,completedOrderID,BillingAddress,ShippingStatus
			FROM  bvc_Order 			
			WHERE TimeOfOrder >= @StartDate 
			AND TimeOfOrder <= @EndDate 
			AND (
			(CompletedOrderID LIKE '%' + @Keyword + '%') OR
			(Username LIKE '%' + @Keyword + '%') OR
			(BillingAddress LIKE '%' + @Keyword + '%')		
			)
			AND StatusCode=@Status AND PaymentStatus=@PaymentStatus
			ORDER BY CompletedOrderID DESC
		END

	ELSE
		BEGIN
			SELECT [ID],GrandTotal,TimeOfOrder,UserName,completedOrderID,BillingAddress,ShippingStatus
			FROM bvc_Order 
			WHERE TimeOfOrder >= @StartDate 
			AND TimeOfOrder <= @EndDate 
			AND (
			(CompletedOrderID LIKE '%' + @Keyword + '%') OR
			(Username LIKE '%' + @Keyword + '%') OR
			(BillingAddress LIKE '%' + @Keyword + '%')
			)
			AND StatusCode = @Status
			ORDER BY CompletedOrderID DESC
		END
END	
ELSE
BEGIN         
	IF @PaymentStatus <> 0
		BEGIN
			SELECT [ID],GrandTotal,TimeOfOrder,UserName,completedOrderID,BillingAddress,ShippingStatus
			FROM bvc_Order 
			WHERE TimeOfOrder >= @StartDate 
			AND TimeOfOrder <= @EndDate 
			AND (
			(CompletedOrderID LIKE '%' + @Keyword + '%') OR
			(Username LIKE '%' + @Keyword + '%') OR
			(BillingAddress LIKE '%' + @Keyword + '%')
			)
			AND PaymentStatus=@PaymentStatus
			ORDER BY CompletedOrderID DESC
		END

	ELSE
		BEGIN
			SELECT [ID],GrandTotal,TimeOfOrder,UserName,completedOrderID,BillingAddress,ShippingStatus
			FROM bvc_Order 
			WHERE TimeOfOrder >= @StartDate 
			AND TimeOfOrder <= @EndDate 
			AND (
			(CompletedOrderID LIKE '%' + @Keyword + '%') OR
			(Username LIKE '%' + @Keyword + '%') OR
			(BillingAddress LIKE '%' + @Keyword + '%')
			)
			ORDER BY CompletedOrderID DESC
		END

	
          
END
	RETURN
GO




 


ALTER PROCEDURE dbo.bvc_Discount_All_s
AS
	SELECT [ID],[StartDate],[EndDate],ApplyTo,
	AmountType,Amount,QualifyQty,QualifyTotal,
	RequiresCoupon,CouponCode,MaxUsesPerCustomer,ExclusiveOffer,ShipMethodID
	FROM bvc_Discount ORDER BY StartDate DESC
	
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Products_ByVendor_s

@ID int,
@SortType int = 9999,
@Admin int =0

AS
	IF @Admin=1
		BEGIN
			SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
			FROM bvc_Product 			
			WHERE VendorID=@ID
		END
	ELSE
		BEGIN
			IF @SortType = 2
				BEGIN
					SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
					FROM bvc_Product 			
					WHERE VendorID=@ID
					AND ParentID=-1
					AND InventoryStatus > 0
					AND Status > 0
					ORDER BY SitePrice ASC		
				END				
			ELSE
				BEGIN
					SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
					FROM bvc_Product 		
					WHERE VendorID=@ID
					AND ParentID=-1
					AND InventoryStatus > 0
					AND Status > 0	
					ORDER BY ProductName		
				END				
	
		END
	RETURN
GO

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].bvc_OrderManager2_Unpaid_s

@StartDate as DateTime,
@EndDate as DateTime

AS	
	
	SELECT [ID],GrandTotal,TimeOfOrder,UserName,completedOrderID,BillingAddress,StatusCode,PaymentStatus,ShippingStatus
			FROM  bvc_Order 			
			WHERE TimeOfOrder >= @StartDate 
			AND TimeOfOrder <= @EndDate 			
			AND StatusCode <> 1 AND StatusCode <> 999 AND StatusCode <> -1
			AND PaymentStatus <> 30
			ORDER BY CompletedOrderID DESC
	
	RETURN
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE  [dbo].bvc_Order_ShippingStatus_u

@ID int,
@ShippingStatus int

AS
 
 UPDATE bvc_Order SET 

ShippingStatus=@ShippingStatus 

  WHERE [ID]=@ID
            
	RETURN
GO




 


ALTER PROCEDURE dbo.bvc_UserAccount_d

@ID int

AS
	DELETE FROM bvc_WishList WHERE UserID=@ID

	DELETE FROM bvc_SecurityToken WHERE UserID=@ID

	DELETE FROM bvc_UserXRole WHERE UserID=@ID
	
	DELETE FROM bvc_User WHERE [ID]=@ID
	
	RETURN
GO

SET ANSI_NULLS OFF
GO


ALTER  PROCEDURE [dbo].[bvc_UserAccount_SearchByFirstName_s] 

@Keyword nvarchar(255),
@MaxResults integer = 0

AS
	BEGIN
	SET ROWCOUNT @MaxResults

	SELECT 
	[ID],[UserName],[Password],[Firstname],[LastName],Salt,
	TaxExempt,Email,CreationDate,LastLoginDate,PasswordHint,Comment,PricingLevel,AddressBook
	FROM bvc_User WHERE FirstName LIKE  @Keyword + '%'
	ORDER BY FirstName

	SET ROWCOUNT 0
	END

	RETURN

GO

SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.bvc_ProductChoices_Item_s 

@ID int

AS
	SELECT [ID],ChoiceID,DisplayText,DisplayValue,SortOrder,Selected,ImageURL,PriceAdjustment,CostAdjustment,ShippingCostAdjustment,WeightAdjustment,NullItem
	FROM bvc_ProductChoices_Item
	WHERE [ID] = @ID

GO


ALTER PROCEDURE dbo.bvc_Vendor_d

@ID int

AS
	UPDATE bvc_Product SET VendorID=0 WHERE VendorID=@ID

	DELETE FROM bvc_Vendor WHERE [ID]=@ID
	
	RETURN
GO


ALTER PROCEDURE dbo.bvc_ProductChoices_Item_ForChoice_s 

@ID int

AS
	SELECT [ID],ChoiceID,DisplayText,DisplayValue,SortOrder,Selected,ImageURL,PriceAdjustment,CostAdjustment,ShippingCostAdjustment,WeightAdjustment,NullItem
	FROM bvc_ProductChoices_Item
	WHERE ChoiceID=@ID
	ORDER BY SortOrder


GO


ALTER PROCEDURE dbo.bvc_Products_ByTypeIgnoreInventory_s

@ID int,
@SortType int = 9999

AS
	IF @SortType = 2
		BEGIN
			SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
			FROM bvc_Product 
			WHERE ProductTypeID=@ID AND ParentID=-1 
			ORDER BY SitePrice ASC		
		END	
	ELSE
		BEGIN
			SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
			FROM bvc_Product 
			WHERE ProductTypeID=@ID AND ParentID=-1 
			ORDER BY ProductName		
		END	
			
	
	RETURN
GO

SET ANSI_NULLS OFF
GO


ALTER  PROCEDURE [dbo].[bvc_UserAccount_SearchByLastName_s] 

@Keyword nvarchar(255),
@MaxResults integer = 0

AS
	BEGIN
	SET ROWCOUNT @MaxResults

	SELECT 
	[ID],[UserName],[Password],[Firstname],[LastName],Salt,
	TaxExempt,Email,CreationDate,LastLoginDate,PasswordHint,Comment,PricingLevel,AddressBook
	FROM bvc_User WHERE LastName LIKE  @Keyword + '%'
	ORDER BY LastName

	SET ROWCOUNT 0
	END

	RETURN

GO

SET ANSI_NULLS ON
GO


ALTER PROCEDURE dbo.bvc_OrderItem_u

@ID int,
@OrderID int, 
	@ProductID nvarchar(50),  
	@Qty int, 
	@DisplayName nvarchar(255), 
	@OriginalBasePrice float,
	@SitePrice float,
	@AdjustedSitePrice float,
	@SiteCost float,
	@TaxExempt int,
	@TaxClass int,
	@NonShipping int,
	@ShipSeparately int,
	@DropShipMode int,
	@DropShipAddressID int,
	@LineTotal float, 
	@LineTaxTotal float,
	@PackageID int,		
	@StatusCode int,
	@Weight float,
	@Length float,
	@Width float,
	@Height float,
	@MinimumQty int,
	@DisplayDescription ntext,
	@ImageURL ntext,
	@ExtraInformation ntext,
	@GiftWrap int,
	@GiftWrapMessage nvarchar(255),
	@GiftWrapAllowed int,
	@InventoryKey nvarchar(255),
	@ShippingCost float = 0,
	@QtyShipped int,
	@QtyReturned int,
	@ParentID int = 0

AS
	UPDATE bvc_OrderItem
	SET 
	
	OrderID = @OrderID ,
	ProductID = @ProductID ,
	Qty = @Qty ,
	DisplayName = @DisplayName ,
	OriginalBasePrice = @OriginalBasePrice,
	SitePrice = @SitePrice ,
	AdjustedSitePrice=@AdjustedSitePrice,
	SiteCost = @SiteCost ,
	TaxExempt = @TaxExempt ,
	TaxClass = @TaxClass ,
	NonShipping = @NonShipping ,
	ShipSeparately = @ShipSeparately ,
	DropShipMode = @DropShipMode ,
	DropShipAddressID = @DropShipAddressID ,
	LineTotal = @LineTotal ,
	LineTaxTotal = @LineTaxTotal ,
	PackageID = @PackageID ,
	StatusCode = @StatusCode ,
	Weight = @Weight ,
	Length = @Length ,
	Width = @Width ,
	Height = @Height ,
	MinimumQty = @MinimumQty ,
	DisplayDescription = @DisplayDescription,
	ImageURL=@ImageURL,
	ExtraInformation=@ExtraInformation,
	GiftWrap=@GiftWrap,
	GiftWrapMessage=@GiftWrapMessage,
	GiftWrapAllowed = @GiftWrapAllowed,
	InventoryKey=@InventoryKey,
	ShippingCost=@ShippingCost,
	QtyShipped=@QtyShipped,
	QtyReturned=@QtyReturned,
	ParentID=@ParentID

	WHERE 
	[ID]=@ID
		
	RETURN
GO




 


ALTER PROCEDURE dbo.bvc_Discount_i

@StartDate datetime,
@EndDate datetime,
@ApplyTo int,
@AmountType int,
@Amount float,
@QualifyQty int,
@QualifyTotal float,
@RequiresCoupon int,
@CouponCode nvarchar(50),
@MaxUsesPerCustomer int,
@ExclusiveOffer int,
@ShipMethodID int = 0

AS
	INSERT INTO 
	bvc_Discount (
	StartDate,EndDate,ApplyTo,
	AmountType,Amount,QualifyQty,QualifyTotal,
	RequiresCoupon,CouponCode,MaxUsesPerCustomer,ExclusiveOffer,ShipMethodID)
	
	 VALUES
	 (@StartDate,@EndDate,@ApplyTo,
	 @AmountType,@Amount,@QualifyQty,
	 @QualifyTotal,@RequiresCoupon,@CouponCode,@MaxUsesPerCustomer,@ExclusiveOffer,@ShipMethodID)
	 
	  SELECT SCOPE_IDENTITY()  as NewID
	
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Manufacturer_d

@ID int

AS
	UPDATE bvc_Product SET ManufacturerID=0 WHERE ManufacturerID=@ID

	DELETE FROM bvc_Manufacturer WHERE [ID]=@ID
	
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Package_i

@SourceAddress ntext,
@DestinationAddress ntext,
@DeclaredValue float,
@Length float,
@Width float,
@Height float,
@Weight float,
@ShippingType int,
@ShippingServiceCode nvarchar(50),
@ShippingMethodName nvarchar(100),
@ShippingCost float,
@TrackingNumber nvarchar(100),
@ShipDate datetime,
@EstimatedDeliveryDate datetime,
@ForSingleItem int

AS
	INSERT INTO bvc_Package
	(
SourceAddress,
DestinationAddress,
DeclaredValue,
Length,
Width,
Height,
Weight,
ShippingType,
ShippingServiceCode,
ShippingMethodName,
ShippingCost,
TrackingNumber,
ShipDate,
EstimatedDeliveryDate,
ForSingleItem	
	) 
	VALUES
	(
@SourceAddress,
@DestinationAddress,
@DeclaredValue,
@Length,
@Width,
@Height,
@Weight,
@ShippingType,
@ShippingServiceCode,
@ShippingMethodName,
@ShippingCost,
@TrackingNumber,
@ShipDate,
@EstimatedDeliveryDate,
@ForSingleItem
	) 
	
	SELECT SCOPE_IDENTITY()  as NewID
	
	RETURN
GO





ALTER PROCEDURE dbo.bvc_Package_s

@ID int

AS
	SELECT [ID], SourceAddress,
DestinationAddress,
DeclaredValue,
Length,
Width,
Height,
Weight,
ShippingType,
ShippingServiceCode,
ShippingMethodName,
ShippingCost,
TrackingNumber,
ShipDate,
EstimatedDeliveryDate,
ForSingleItem	

	FROM bvc_Package WHERE [ID]=@ID
	
	RETURN
GO


ALTER PROCEDURE dbo.bvc_OrderPayment_s 

@ID int

AS

	SELECT
	[ID],
	AuditDate,
	PaymentType,
	Amount,
	CheckNumber,
	GiftCertificateNumber,
	CreditCardNumber,
	CreditCardType,
	CreditCardExpMonth,
	CreditCardExpYear,
	CreditCardHolder,
	TransactionReferenceNumber,
	TransactionResponseCode,
	Note,
	PaymentMethod,
	OrderID,
	PurchaseOrderNumber,
	AuthorizationOnly
	FROM bvc_OrderPayment
	WHERE [ID]=@ID
GO


ALTER PROCEDURE dbo.bvc_Package_d
@ID as int

AS

DELETE FROM bvc_PackageXOrder WHERE PackageID=@ID

DELETE FROM bvc_Package WHERE [ID]=@ID

	RETURN
GO

CREATE PROCEDURE [dbo].bvc_Order_PaymentStatus_u

@ID int,
@PaymentStatus int

AS
 
 UPDATE bvc_Order SET 

PaymentStatus=@PaymentStatus 

  WHERE [ID]=@ID
            
	RETURN
GO


ALTER PROCEDURE dbo.bvc_ProductInventory_ForProduct_s 

@ID as nvarchar(50)

AS

	SELECT ProductID,InventoryKey,Qty,SKU FROM bvc_ProductInventory WHERE ProductID=@ID

GO



ALTER  PROCEDURE dbo.bvc_UserAccount_ByEmail_s

@Email nvarchar(100)

AS
		SELECT 
		[ID],[UserName],[Password],[Firstname],[LastName],Salt,
		TaxExempt,Email,CreationDate,LastLoginDate,PasswordHint,Comment,PricingLevel,AddressBook
		FROM bvc_User WHERE Email=@Email
	RETURN

GO


ALTER PROCEDURE dbo.bvc_ProductInventory_i 

@ProductID nvarchar(50),
@InventoryKey nvarchar(255),
@Qty int,
@SKU nvarchar(50) = @ProductID

AS
	BEGIN
		DELETE FROM bvc_ProductInventory WHERE ProductID=@ProductID AND InventoryKey=@InventoryKey
		
		INSERT INTO bvc_ProductInventory (ProductID,InventoryKey,Qty,SKU)
		VALUES
		(@ProductID,@InventoryKey,@Qty,@SKU)
			
	END
GO

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].bvc_OrderManager2_Unshipped_s

@StartDate as DateTime,
@EndDate as DateTime

AS	
	
	SELECT [ID],GrandTotal,TimeOfOrder,UserName,completedOrderID,BillingAddress,StatusCode,PaymentStatus,ShippingStatus
			FROM  bvc_Order 			
			WHERE TimeOfOrder >= @StartDate 
			AND TimeOfOrder <= @EndDate 	
			
			AND StatusCode <> 1 AND StatusCode <> 999 AND StatusCode <> -1
			AND PaymentStatus = 30			
			AND ShippingStatus <> 3				
			ORDER BY CompletedOrderID DESC
	
	RETURN
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].bvc_Product_InventorySet_u

@ProductID nvarchar(255),
@Qty int

AS

	UPDATE bvc_Product
	SET
	InventoryAvailableQty = @Qty
	WHERE [ID]=@ProductID
GO


ALTER PROCEDURE dbo.bvc_ProductChoices_Item_i 

@ChoiceID int,
@DisplayText nvarchar(255),
@DisplayValue nvarchar(255),
@SortOrder int,
@Selected int,
@ImageURL nvarchar(255),
@PriceAdjustment float,
@CostAdjustment float,
@ShippingCostAdjustment float = 0,
@WeightAdjustment float = 0,
@NullItem int = 0

AS
	BEGIN

		IF @Selected = 1 
			BEGIN
			UPDATE bvc_ProductChoices_Item SET Selected=0 WHERE ChoiceID=@ChoiceID
			END

		IF (SELECT Count([ID]) FROM bvc_ProductChoices_Item WHERE ChoiceID=@ChoiceID) > 0
			BEGIN
				SET @SortOrder = (SELECT 
				Max(SortOrder) 
				FROM bvc_ProductChoices_Item
				WHERE ChoiceID=@ChoiceID)+1			
			END
		ELSE
			BEGIN
			SET @SortOrder = 1
			END


	INSERT INTO bvc_ProductChoices_Item
	(
	ChoiceID,DisplayText,DisplayValue,SortOrder,Selected,ImageURL,PriceAdjustment,CostAdjustment,ShippingCostAdjustment,WeightAdjustment,NullItem
	)
	VALUES
	(
	@ChoiceID,@DisplayText,@DisplayValue,@SortOrder,@Selected,@ImageURL,@PriceAdjustment,@CostAdjustment,@ShippingCostAdjustment,@WeightAdjustment,@NullItem
	)

	 SELECT SCOPE_IDENTITY()  as NewID

	END
GO


ALTER PROCEDURE dbo.bvc_OrderPayment_u 

@ID int,
@AuditDate datetime,
@PaymentType int,
@Amount float,
@CheckNumber nvarchar(50),
@GiftCertificateNumber nvarchar(50),
@CreditCardNumber nvarchar(50),
@CreditCardType int,
@CreditCardExpMonth int,
@CreditCardExpYear int,
@CreditCardHolder nvarchar(100),
@TransactionReferenceNumber nvarchar(200),
@TransactionResponseCode nvarchar(200),
@Note ntext,
@PaymentMethod int,
@OrderID int,
@PurchaseOrderNumber nvarchar(100),
@AuthorizationOnly int = 0

AS
	UPDATE  bvc_OrderPayment
	SET
	AuditDate=@AuditDate,
	PaymentType=@PaymentType,
	Amount=@Amount,
	CheckNumber=@CheckNumber,
	GiftCertificateNumber=@GiftCertificateNumber,
	CreditCardNumber=@CreditCardNumber,
	CreditCardType=@CreditCardType,
	CreditCardExpMonth=@CreditCardExpMonth,
	CreditCardExpYear=@CreditCardExpYear,
	CreditCardHolder=@CreditCardHolder,
	TransactionReferenceNumber=@TransactionReferenceNumber,
	TransactionResponseCode=@TransactionResponseCode,
	Note=@Note,
	PaymentMethod=@PaymentMethod,
	OrderID=@OrderID,
	AuthorizationOnly=@AuthorizationOnly
	WHERE [ID]=@ID
GO




 


ALTER PROCEDURE dbo.bvc_Product_i

@ID nvarchar(50),
@ProductTypeID int,
@ProductName nvarchar(512),
@ListPrice  float,
@SitePrice  float,
@SiteCost float,
@MetaKeywords  nvarchar(255),
@MetaDescription  nvarchar(255),
@MetaTitle  nvarchar(512),
@TaxExempt  int,
@TaxClass  int,
@NonShipping  int,
@ShipSeparately  int,
@DropShipMode  int,
@ShippingWeight  float,
@ShippingLength float,
@ShippingWidth float,
@ShippingHeight float,
@Status int,
@ImageFileSmall nvarchar(1000),
@ImageFileMedium nvarchar(1000),
@MinimumQty int,
@ParentID int = -1,
@VariantDisplayMode int,
@InventoryAvailableQty int,
@InventoryLowStockNotice int,
@InventoryStatus int,
@InventoryNotAvailableStatus int,
@InventoryNotAvailableMessage nvarchar(1000),
@ShortDescription nvarchar(255),
@LongDescription ntext,
@ManufacturerID int,
@VendorID int,
@GiftWrapAllowed int,
@ExtraShipFee decimal

AS
	INSERT INTO bvc_Product
	(
	[ID],
	ProductTypeID,
	ProductName,
	ListPrice,
	SitePrice,
	SiteCost,
	MetaKeywords,
	MetaDescription,
	MetaTitle,
	TaxExempt,
	TaxClass,
	NonShipping,
	ShipSeparately,
	DropShipMode,

	ShippingWeight,
	ShippingLength,
	ShippingWidth,
	ShippingHeight,
	Status,
	ImageFileSmall,
	ImageFileMedium,
	CreationDate,
	MinimumQty,
	ParentID,
	VariantDisplayMode,
	InventoryAvailableQty,
	InventoryLowStockNotice,
	InventoryStatus,
	InventoryNotAvailableStatus,
	InventoryNotAvailableMessage,
	ShortDescription,
	LongDescription,
	ManufacturerID,
	VendorID,
	GiftWrapAllowed,
	ExtraShipFee
	) 
	VALUES 
	(
	@ID,
	@ProductTypeID,
	@ProductName,
	@ListPrice,
	@SitePrice,
	@SiteCost,
	@MetaKeywords,
	@MetaDescription,
	@MetaTitle,
	@TaxExempt,
	@TaxClass,
	@NonShipping,
	@ShipSeparately,
	@DropShipMode,

	@ShippingWeight,
	@ShippingLength,
	@ShippingWidth,
	@ShippingHeight,
	@Status,
	@ImageFileSmall,
	@ImageFileMedium,
GetDate(),
	@MinimumQty,
	@ParentID,
	@VariantDisplayMode,
	@InventoryAvailableQty,
	@InventoryLowStockNotice,
	@InventoryStatus,
	@InventoryNotAvailableStatus,
	@InventoryNotAvailableMessage,
	@ShortDescription,
	@LongDescription,
	@ManufacturerID,
	@VendorID,
	@GiftWrapAllowed,
	@ExtraShipFee
	)
	
		
	RETURN
GO

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].bvc_OrderManager2_Problem_s

@StartDate as DateTime,
@EndDate as DateTime

AS	
	
	SELECT [ID],GrandTotal,TimeOfOrder,UserName,completedOrderID,BillingAddress,StatusCode,PaymentStatus,ShippingStatus
			FROM  bvc_Order 			
			WHERE TimeOfOrder >= @StartDate 
			AND TimeOfOrder <= @EndDate 			
			AND StatusCode =100 
			ORDER BY CompletedOrderID DESC
	
	RETURN
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.bvc_Affiliate_s

@ID int

AS
	SELECT [ID], DisplayName,Address,CommissionAmount,CommissionType,ReferralDays, TaxID, DriversLicenseNumber,WebSiteURL,StyleSheet
	FROM bvc_Affiliate WHERE  [ID]=@ID order by DisplayName
	RETURN
GO




 






 


ALTER PROCEDURE dbo.bvc_Product_u

@ID nvarchar(50),
@ProductName nvarchar(512),
@ListPrice  float,
@SitePrice  float,
@SiteCost float,
@MetaKeywords  nvarchar(255),
@MetaDescription  nvarchar(255),
@MetaTitle  nvarchar(512),
@TaxExempt  int,
@TaxClass  int,
@NonShipping  int,
@ShipSeparately  int,
@DropShipMode  int,

@ShippingWeight  float,
@ShippingLength float,
@ShippingWidth float,
@ShippingHeight float,
@Status int,
@ImageFileSmall nvarchar(1000),
@ImageFileMedium nvarchar(1000),
@MinimumQty int,
@ParentID int = -1,
@VariantDisplayMode int,
@InventoryAvailableQty int,
@InventoryLowStockNotice int,
@inventoryStatus int,
@InventoryNotAvailableStatus int,
@InventoryNotAvailableMessage nvarchar(1000),
@ShortDescription nvarchar(255),
@LongDescription ntext,
@ManufacturerID int,
@VendorID int,
@GiftWrapAllowed int,
@ExtraShipFee decimal
	
AS
	UPDATE bvc_Product
	SET
		
	ProductName=@ProductName,
	ListPrice=@ListPrice,
	SitePrice=@SitePrice,
	SiteCost=@SiteCost,
	MetaKeywords=@MetaKeywords,
	MetaDescription=@MetaDescription,
	MetaTitle=@MetaTitle,
	TaxExempt=@TaxExempt,
	TaxClass=@TaxClass,
	NonShipping=@NonShipping,
	ShipSeparately=@ShipSeparately,
	DropShipMode=@DropShipMode,

	ShippingWeight=@ShippingWeight,
	ShippingLength=@ShippingLength,
	ShippingWidth=@ShippingWidth,
	ShippingHeight=@ShippingHeight,
	Status=@Status,
	ImageFileSmall=@ImageFileSmall,
	ImageFileMedium=@ImageFileMedium,
	MinimumQty=@MinimumQty,
	ParentID=@ParentID,
	VariantDisplayMode=@VariantDisplayMode,
	InventoryAvailableQty=@InventoryAvailableQty,
	InventoryLowStockNotice=@InventoryLowStockNotice,
	InventoryStatus=@InventoryStatus,
	InventoryNotAvailableStatus=@InventoryNotAvailableStatus,
	InventoryNotAvailableMessage=@InventoryNotAvailableMessage,
	ShortDescription=@ShortDescription,
	LongDescription=@LongDescription,
	ManufacturerID=@ManufacturerID,
	VendorID=@VendorID,
	GiftWrapAllowed=@GiftWrapAllowed,
	ExtraShipFee=@ExtraShipFee

	WHERE [ID]=@ID
	
	RETURN
GO


ALTER PROCEDURE  dbo.bvc_Products_ByCreationDate_s

@StartDate datetime

AS
	BEGIN

	SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
	FROM bvc_Product 
	WHERE CreationDate >= @StartDate AND ParentID=-1
	AND InventoryStatus>0 AND Status > 0
	ORDER BY CreationDate DESC,ID DESC
		
	END				
	
	RETURN
GO

SET QUOTED_IDENTIFIER OFF
GO


ALTER PROCEDURE dbo.bvc_Order_Unpaid_s

AS


	SELECT bvc_Order.[ID],GrandTotal,TimeOfOrder,UserName,CompletedOrderID, 
	ShippingStatus FROM bvc_Order	
	WHERE PaymentStatus < 30 AND
	bvc_Order.StatusCode > 1
	ORDER BY CompletedOrderID DESC
         
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Orders_ByTypeAndDate_s

@StatusCode int,
@StartDate datetime,
@EndDate datetime

AS

if @StatusCode <> 0

	SELECT [ID],GrandTotal,TimeOfOrder,UserName,CompletedOrderID, ShippingStatus
	FROM bvc_Order	
	WHERE StatusCode=@StatusCode 
	AND TimeOfOrder >= @StartDate 
	AND TimeOfOrder <= @EndDate 
	ORDER BY CompletedOrderID DESC

ELSE
         
	SELECT bvc_Order.[ID],GrandTotal,TimeOfOrder,UserName,completedOrderID, 
	ShippingStatus
	FROM bvc_Order 
	WHERE TimeOfOrder >= @StartDate 
	AND TimeOfOrder <= @EndDate 
	ORDER BY CompletedOrderID DESC
          
	RETURN
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.bvc_Affiliate_i

@DisplayName nvarchar(255),
@Address ntext,
@CommissionAmount float,
@CommissionType int,
@ReferralDays int,
@TaxID nvarchar(100),
@DriversLicenseNumber nvarchar(100),
@WebSiteURL nvarchar(1000),
@StyleSheet nvarchar(1000)

AS
	Insert Into 
	bvc_Affiliate 
	(DisplayName,Address,CommissionAmount,CommissionType,ReferralDays,TaxID,DriversLicenseNumber,WebSiteURL,StyleSheet)
	VALUES
	(@DisplayName,@Address, @CommissionAmount,@CommissionType,@ReferralDays,@TaxID,@DriversLicenseNumber,@WebSiteURL,@StyleSheet)
	
	 SELECT SCOPE_IDENTITY()  as NewID
	 
	RETURN
GO


ALTER PROCEDURE dbo.bvc_Products_ByType_s

@ID int,
@SortType int = 9999,
@Admin int = 0

AS
	IF @Admin = 1
		BEGIN
			SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall ,ManufacturerID,ShortDescription,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
			FROM bvc_Product 
			WHERE ProductTypeID=@ID
			ORDER BY ProductName					
		END
	ELSE
		BEGIN
			IF @SortType = 2
				BEGIN
					SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall ,ManufacturerID,ShortDescription,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
					FROM bvc_Product 
					WHERE ProductTypeID=@ID AND ParentID=-1 AND InventoryStatus > 0 AND Status > 0
					ORDER BY SitePrice ASC		
				END	
			ELSE
				BEGIN
					SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall ,ManufacturerID,ShortDescription,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount
					FROM bvc_Product 
					WHERE ProductTypeID=@ID AND ParentID=-1 AND InventoryStatus > 0 AND Status > 0
					ORDER BY ProductName		
				END					
		END
	RETURN
GO



SET QUOTED_IDENTIFIER OFF
GO



ALTER PROCEDURE dbo.bvc_Order_Unshipped_s

AS

	SELECT bvc_Order.[ID],GrandTotal,TimeOfOrder,UserName,CompletedOrderID,ShippingStatus
	FROM bvc_Order
	WHERE ShippingStatus <> 3 AND StatusCode > 1
	ORDER BY CompletedOrderID DESC
         
	RETURN
GO


SET QUOTED_IDENTIFIER ON
GO




ALTER PROCEDURE dbo.bvc_Order_u
@ID int,
@AffiliateID int,
@BillingAddress ntext,
@CompletedOrderID nvarchar(50),
@Downloadkey nvarchar(50),
@GrandTotal float,
@HandlingFee float,
@OrderDiscountsTotal float,
@PaymentStatus int,
@Salt nvarchar(50),
@ShippingDiscountsTotal float,
@ShippingTotal float,
@StatusCode int,
@SubTotal float,
@TaxTotal float,
@TimeOfOrder datetime,
@UserID int,
@UserName nvarchar(100),
@GiftWrapCharge float,
@Instructions ntext,
@LastUpdated datetime,
@ShippingStatus int

AS
 
 UPDATE bvc_Order SET 

AffiliateID=@AffiliateID,
BillingAddress=@BillingAddress ,
CompletedOrderID=@CompletedOrderID ,
Downloadkey=@Downloadkey ,
GrandTotal=@GrandTotal ,
HandlingFee=@HandlingFee ,
OrderDiscountsTotal=@OrderDiscountsTotal ,
PaymentStatus=@PaymentStatus ,
Salt=@Salt,
ShippingDiscountsTotal=@ShippingDiscountsTotal ,
ShippingTotal=@ShippingTotal ,
StatusCode=@StatusCode ,
SubTotal=@SubTotal ,
TaxTotal=@TaxTotal,
TimeOfOrder=@TimeOfOrder,
UserID=@UserID,
UserName=@UserName,
LastUpdated=@LastUpdated,
GiftWrapCharge=@GiftWrapCharge,
Instructions = @Instructions,
ShippingStatus=@ShippingStatus

  WHERE [ID]=@ID
            
	RETURN
GO




ALTER PROCEDURE dbo.bvc_ProductChoices_Item_u 

@ID int,
@ChoiceID int,
@DisplayText nvarchar(255),
@DisplayValue nvarchar(255),
@SortOrder int,
@Selected int,
@ImageURL nvarchar(255),
@PriceAdjustment float,
@CostAdjustment float,
@ShippingCostAdjustment float = 0,
@WeightAdjustment float = 0,
@NullItem int = 0

AS
	IF @Selected = 1 
			BEGIN
			UPDATE bvc_ProductChoices_Item SET Selected=0 WHERE ChoiceID=@ChoiceID
			END


	UPDATE bvc_ProductChoices_Item
	SET
	ChoiceID=@ChoiceID,
	DisplayText=@DisplayText,
	DisplayValue=@DisplayValue,
	SortOrder=@SortOrder,
	Selected=@Selected,
	ImageURL=@ImageURL,
	PriceAdjustment=@PriceAdjustment,
	CostAdjustment=@CostAdjustment,
	ShippingCostAdjustment=@ShippingCostAdjustment,
	WeightAdjustment=@WeightAdjustment,
	NullItem=@NullItem
	WHERE [ID]=@ID
GO








ALTER PROCEDURE dbo.bvc_UserAccounts_Alphabetical_s

@StartCharacter nvarchar(1) = 'A',
@EndCharacter nvarchar(20) = 'ZZZZZZZZZZZ'

AS

SELECT [ID],[UserName],[Password],[FirstName],[LastName],Salt,
	TaxExempt,Email,CreationDate,LastLoginDate,PasswordHint,Comment,PricingLevel,AddressBook
	FROM bvc_User WHERE
	[LastName] >= @StartCharacter
	AND
	[LastName] < @EndCharacter
	ORDER BY Lastname,FirstName,UserName
	            
	RETURN
GO






 


ALTER PROCEDURE dbo.bvc_Discount_u

@StartDate datetime,
@EndDate datetime,
@ApplyTo int,
@AmountType int,
@Amount float,
@QualifyQty int,
@QualifyTotal float,
@RequiresCoupon int,
@CouponCode nvarchar(50),
@ID int,
@MaxUsesPerCustomer int,
@ExclusiveOffer int,
@ShipMethodID int = 0
	
AS
	UPDATE bvc_Discount
	SET 
	StartDate=@StartDate,
	EndDate=@EndDate,
	ApplyTo=@ApplyTo,
	AmountType=@AmountType,
	Amount=@Amount,
	QualifyQty=@QualifyQty,
	QualifyTotal=@QualifyTotal,
	RequiresCoupon=@RequiresCoupon,
	CouponCode=@CouponCode,
	MaxUsesPerCustomer=@MaxUsesPerCustomer,
	ExclusiveOffer=@ExclusiveOffer,
	ShipMethodID=@ShipMethodID
	
	 WHERE [ID]=@ID
	
	RETURN
GO




ALTER PROCEDURE dbo.bvc_Affiliate_all_s

AS
	SELECT [ID], DisplayName,Address,CommissionAmount,CommissionType,ReferralDays,TaxID,DriversLicenseNumber,WebSiteURL,StyleSheet
	 FROM bvc_Affiliate  order by DisplayName
	
	RETURN
GO






 


ALTER PROCEDURE dbo.bvc_UserAccount_ByID_s

@ID int

AS
	SELECT [ID],[UserName],[Password],[Firstname],[LastName],Salt,
	TaxExempt,Email,CreationDate,LastLoginDate,PasswordHint,Comment,PricingLevel,AddressBook
	FROM bvc_User Where [ID]=@ID
	
	RETURN
GO




ALTER PROCEDURE dbo.bvc_Package_u

@ID int,
@SourceAddress ntext,
@DestinationAddress ntext,
@DeclaredValue float,
@Length float,
@Width float,
@Height float,
@Weight float,
@ShippingType int,
@ShippingServiceCode nvarchar(50),
@ShippingMethodName nvarchar(100),
@ShippingCost float,
@TrackingNumber nvarchar(100),
@ShipDate datetime,
@EstimatedDeliveryDate datetime,
@ForSingleItem int

AS
	UPDATE bvc_Package
	SET

SourceAddress=@SourceAddress,
DestinationAddress=@DestinationAddress,
DeclaredValue=@DeclaredValue,
Length=@Length,
Width=@Width,
Height=@Height,
Weight=@Weight,
ShippingType=@ShippingType,
ShippingServiceCode=@ShippingServiceCode,
ShippingMethodName=@ShippingMethodName,
ShippingCost=@ShippingCost,
TrackingNumber=@TrackingNumber,
ShipDate=@ShipDate,
EstimatedDeliveryDate=@EstimatedDeliveryDate,
ForSingleItem=@ForSingleItem

	
	WHERE [ID]=@ID
	
	RETURN
GO




ALTER PROCEDURE dbo.bvc_ProductChoices_Item_ForProduct_s 

@ID nvarchar(50)

AS
	SELECT [ID],ChoiceID,DisplayText,DisplayValue,SortOrder,Selected,ImageURL,PriceAdjustment,CostAdjustment,ShippingCostAdjustment,WeightAdjustment,NullItem
	FROM bvc_ProductChoices_Item
	WHERE ChoiceID IN 
		(
		SELECT ProductChoiceID FROM bvc_ProductXProductChoices WHERE ProductID=@ID
		)
	ORDER BY [ChoiceID],SortOrder
GO






 


ALTER PROCEDURE dbo.bvc_Affiliate_d
@ID int

AS
	DELETE FROM bvc_Referral WHERE affid=@ID

	DELETE FROM bvc_Affiliate WHERE [ID]=@ID
	
	RETURN
GO




ALTER PROCEDURE dbo.bvc_Products_ByPrice_s

@LowPrice as nvarchar(50) = '0',
@HighPrice as nvarchar(50) = '9999',
@SortType int = 9999

AS
	DECLARE @q nvarchar(1000)

	SET @q = 'SELECT [ID],ProductName,ListPrice,SitePrice,ImageFileSmall,ShortDescription,ManufacturerID,InventoryAvailableQty,InventoryStatus, (Select Count(ProductID) from bvc_ProductXProductChoices WHERE ProductID=[ID]) As ChoiceCount FROM bvc_Product '
	set @q = @q + ' WHERE [SitePrice] >= ' + @LowPrice
	SET @q = @q + ' AND [SitePrice] < ' + @HighPrice
	SET @q = @q +  ' AND (status > 0) '	
	SET @q = @q + ' AND ParentID=-1 '
	SET @q = @q + ' AND InventoryStatus>0 '


	IF @SortType = 2 
		SET @q = @q + ' ORDER BY SitePrice ASC'
	ELSE
		SET @q = @q + ' ORDER BY ProductName'
		
	BEGIN
		EXECUTE(@q)
	END				
	
	RETURN
GO





ALTER PROCEDURE dbo.bvc_UserAccounts_s

@Sort nvarchar(100) = ''

AS
	DECLARE @q nvarchar(300)
SET @q = 'SELECT [ID],[UserName],[Password],[Firstname],[LastName],Salt,TaxExempt,Email,CreationDate,LastLoginDate,PasswordHint,Comment,PricingLevel,AddressBook FROM bvc_User '

	IF @Sort <> ''
		BEGIN
			SET @q = @q + 'ORDER BY ' + @Sort	
			EXECUTE(@q)
			
		END
	ELSE
		BEGIN
			SET @q = @q + 'ORDER BY [LastName],[FirstName], [UserName]'
			EXECUTE(@q)
		END			
            
	RETURN
GO
