SQL server

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'tonerplus')
CREATE USER [tonerplus] FOR LOGIN [tonerplus] WITH DEFAULT_SCHEMA=[tonerplus]
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'tonerplus')
EXEC sys.sp_executesql N'CREATE SCHEMA [tonerplus] AUTHORIZATION [tonerplus]'

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetCustomerDetailsByName]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetCustomerDetailsByName]
@email_id nvarchar(50)

AS
BEGIN
select cust_id ,cust_fname from customers where cust_email= @email_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetCustPasswordByName]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetCustPasswordByName]
@email_id nvarchar(50)

AS
BEGIN

select cust_pwd from customers where cust_email=@email_id
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Shipping]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Shipping](
[Rate_id] [bigint] IDENTITY(1,1) NOT NULL,
[cart_session_id] [nvarchar](50) NULL,
[prd_id] [bigint] NULL,
[Od_id] [bigint] NULL,
[Rate_Method] [nvarchar](max) NULL,
[status] [int] NULL,
[Rate_Amount] [decimal](18, 2) NULL,
[Date] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prd_category]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[prd_category](
[cat_id] [bigint] IDENTITY(1,1) NOT NULL,
[cat_name] [varchar](30) NULL,
[parent] [bigint] NULL,
CONSTRAINT [PK_prd_category] PRIMARY KEY CLUSTERED
(
[cat_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[support_category]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[support_category](
[cat_id] [bigint] IDENTITY(1,1) NOT NULL,
[cat_name] [nvarchar](50) NULL,
[cat_order] [int] NULL,
CONSTRAINT [PK_support_category] PRIMARY KEY CLUSTERED
(
[cat_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[supp_referal]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[supp_referal](
[id] [bigint] NULL,
[datetime] [datetime] NULL,
[referal_url] [nvarchar](max) NULL,
[ipaddress] [nvarchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Order_items]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Order_items](
[Od_id] [bigint] NOT NULL,
[prd_id] [bigint] NULL,
[Od_quantity] [int] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_cms]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_cms](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[cat_id] [bigint] NULL,
[page_id] [bigint] NULL,
[path] [nvarchar](max) NULL,
[page_name] [nvarchar](50) NULL,
[content_title] [nvarchar](50) NULL,
[contents] [text] NULL,
[page_title] [nvarchar](50) NULL,
[meta_keyword] [text] NULL,
[meta_description] [nvarchar](200) NULL,
CONSTRAINT [PK_tbl_cms] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetProductId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Sp_GetProductId]

AS
BEGIN


select isnull(max(prd_id),0) from products

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllStates]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create PROCEDURE [dbo].[GetAllStates]

AS
BEGIN
Select state_id,state_name from state_master

END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_ChangeMyAccount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Created date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_ChangeMyAccount]
@Flag bit,
@Cust_id bigint,
@Cust_fname varchar(50),
@Cust_lname varchar(50),
@Cust_email nvarchar(100),
@Cust_pwd nvarchar(100),
@Cust_address nvarchar(200),
@Cust_City nvarchar(50),
@Cust_Phone varchar(50),
@Country_id bigint,
@Cust_company_name nvarchar(50),
@Provision_code nvarchar(3),
@State_id bigint,
@Cust_promo_code varchar(50) ,

@Cust_zip varchar(50),
@CreatedDate datetime,
@Mobile nvarchar(20),
@Fax nvarchar(20),
@Exists int output

AS
BEGIN
if(@Flag=1)
begin

if exists(select * from Customers where Cust_email=@Cust_email)
begin

update Customers set Cust_fname=@Cust_fname,Cust_lname=@Cust_lname,Cust_email=@Cust_email,Cust_pwd=@Cust_pwd,Cust_address=@Cust_address,Cust_City=@Cust_City,Cust_Phone=@Cust_Phone,Cust_zip=@Cust_zip ,Provision_code=@Provision_code,
State_id=@State_id,CreatedDate=@CreatedDate,Mobile=@Mobile,Fax=@Fax where Cust_id=@Cust_id
set @Exists=0
end
else
begin

set @Exists=1
end
end



END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllCustomers]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetAllCustomers]

AS
BEGIN
select Cust_id,Cust_fname,Cust_lname,Cust_email,Cust_pwd,Cust_address,Cust_City,
Cust_Phone,Cust_Zip,convert(nvarchar(10), CreatedDate,105) as CreatedDate from Customers

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertImagegallery]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertImagegallery]
@prd_id bigint,
@image_path varchar(50),
@Exists int output

AS
BEGIN
delete from image_gallery where prd_id=@prd_id
insert into Image_gallery values(@prd_id,@image_path)
set @Exists=0
return

END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllImages1]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetAllImages1]
@prd_id bigint
AS
BEGIN
select prd_id , case substring(image_path,0,5) when ''http'' then image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+image_path end
as prd_image from products where prd_id= @prd_id
--select prd_id,image_id,''../ProductImages/ThumbnailImage/''+image_path as prd_image from image_gallery where prd_id=@prd_id --

END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCMSList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [dbo].[GetCMSList]




AS
BEGIN

select id,page_id,page_name,content_title,contents,page_title,meta_keyword,meta_description
from tbl_cms


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_CMS_ById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' -- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_CMS_ById]
@page_id bigint
AS
BEGIN
delete from tbl_cms where page_id=@page_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[cms](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[Home] [nvarchar](max) NULL,
[about_us] [nvarchar](max) NULL,
[contact_us] [nvarchar](max) NULL,
CONSTRAINT [PK_cms] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Testimonials]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Testimonials](
[test_id] [bigint] IDENTITY(1,1) NOT NULL,
[author] [nvarchar](50) NULL,
[Contents] [nvarchar](max) NULL,
CONSTRAINT [PK_Testimonials] PRIMARY KEY CLUSTERED
(
[test_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[jobs]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[jobs](
[job_id] [bigint] IDENTITY(1,1) NOT NULL,
[job_title] [nvarchar](50) NULL,
[emp_status] [nvarchar](20) NULL,
[description] [text] NULL,
[contract_type] [nvarchar](20) NULL,
[job_available] [nvarchar](30) NULL,
[experience] [nvarchar](50) NULL,
[qualification] [nvarchar](50) NULL,
[workingplace] [text] NOT NULL,
[job_begin_date] [nvarchar](50) NULL,
[creation_date] [nvarchar](50) NULL,
CONSTRAINT [PK_jobs] PRIMARY KEY CLUSTERED
(
[job_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tagstrip]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[tagstrip] ( @in VARCHAR(8000) )
RETURNS VARCHAR(8000) AS BEGIN
DECLARE @i INT
WHILE 1 = 1 BEGIN
SET @i = LEN( @in )
SET @in = REPLACE( @in, SUBSTRING( @in,
CHARINDEX( ''<'', @in ), CHARINDEX( ''>'', @in ) -
CHARINDEX( ''<'', @in ) + 1 ), SPACE( 0 ) ) IF @i = LEN( @in ) BREAK END RETURN ( @in ) END ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[faq]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[faq]( [faq_id] [bigint] IDENTITY(1,1) NOT NULL, [options] [int] NULL, [faq_quest] [nvarchar](500) NULL, [faq_ans] [text] NULL, CONSTRAINT [PK_faq] PRIMARY KEY CLUSTERED ( [faq_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[search]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[search]( [sid] [bigint] IDENTITY(1,1) NOT NULL, [id] [bigint] NULL, [status] [int] NULL, [title] [nvarchar](max) NULL, [link] [nvarchar](max) NULL, [keywords] [text] NULL, [search_date] [datetime] NULL, [ip_address] [nvarchar](50) NULL, [hits] [bigint] NULL, CONSTRAINT [PK_search] PRIMARY KEY CLUSTERED ( [sid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetCustomerEmailById]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetCustomerEmailById]
@Cust_id bigint
AS
BEGIN
select Cust_id,Cust_email from Customers
where cust_id=@Cust_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[usp_GetProducts]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [tonerplus].[usp_GetProducts]

@startRowIndex int,
@maximumRows int,
@totalRows int OUTPUT

AS

DECLARE @first_id int, @startRow int

SET @startRowIndex = (@startRowIndex - 1) * @maximumRows

IF @startRowIndex = 0
SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex

SELECT @first_id = prd_id FROM Products ORDER BY prd_id

PRINT @first_id

SET ROWCOUNT @maximumRows

SELECT prd_id, prd_name FROM Products WHERE
prd_id >= @first_id
ORDER BY prd_id

SET ROWCOUNT 0

-- GEt the total rows

SELECT @totalRows = COUNT(prd_id) FROM Products
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetOrderListByStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
create PROCEDURE [dbo].[GetOrderListByStatus]
@Status int

AS
BEGIN
select O.Od_id,O.Od_shipping_fname+O.Od_shipping_lname as CustName,
sum(P.prd_price*OI.Od_quantity)+O.Os_shipping_cost+ [dbo].[GetTax]
(
O.Od_id,
sum(P.prd_price*OI.Od_quantity)
) as Total ,
O.Od_date,
[Status] = case
when O.Od_status =0 then ''Processing''
when O.Od_status =1 then ''Approved''
when O.Od_status =2 then ''Declined''
else
''Deleted''
end
from Customers C ,Orders O,Products P,Order_items OI,Shipping S
where C.Cust_id=O.Cust_id and O.Od_id=OI.Od_id and OI.prd_id=P.prd_id and P.prd_id=S.prd_id and S.Od_id=OI.Od_id and OI.prd_id=S.prd_id and S.status=1 and O.Od_status=1
group by O.Od_id,O.Od_shipping_fname,O.Od_shipping_lname,O.Od_date,O.Od_status,O.Os_shipping_cost

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Country_master]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Country_master](
[country_id] [bigint] IDENTITY(1,1) NOT NULL,
[country_name] [nvarchar](50) NULL,
CONSTRAINT [PK_Country_master] PRIMARY KEY CLUSTERED
(
[country_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_sweepstakes_products]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_sweepstakes_products](
[Sid] [bigint] IDENTITY(1,1) NOT NULL,
[prd_id] [bigint] NULL,
[status] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_SweepStakes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_SweepStakes](
[S_ID] [bigint] IDENTITY(1,1) NOT NULL,
[ProductID] [bigint] NULL,
[P_Count] [bigint] NULL,
[draw_date] [datetime] NULL,
[draw_message] [nvarchar](max) NULL,
CONSTRAINT [PK_tbl_SweepStakes] PRIMARY KEY CLUSTERED
(
[S_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prd_sweepstakes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[prd_sweepstakes](
[sid] [bigint] IDENTITY(1,1) NOT NULL,
[P_Count] [bigint] NULL,
[prd_name] [nvarchar](300) NULL,
[img_path] [nvarchar](300) NULL,
[drawdate] [datetime] NULL,
[messaage] [nvarchar](max) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertUpdateState]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertUpdateState]
@Flag bit,
@State_id bigint,
@State_name nvarchar(50),
@Country_id bigint,
@State_province nvarchar(3),
@tax_rate float,
@Exists int output

AS
BEGIN

if(@Flag=1)
begin
if exists(select * from state_master where state_name=@State_name or State_province=@State_province )
begin
set @Exists=1
end
else
begin

insert into State_master values(@State_name,@Country_id,@State_province,@tax_rate)
set @Exists=0
end
end
else
begin

if(exists(select state_name from (select * from state_master where state_id not in(@State_id)) State

where state_name=ltrim(@State_name) or State_province=ltrim(@State_province) ))
begin
set @Exists=1
end
else
begin
update state_master set state_name=@State_name,country_id=@Country_id,State_province=@State_province ,tax_rate=@tax_rate
where state_id=@State_id
set @Exists=0
end

end
return

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllSweepstakesproducts]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Sp_GetAllSweepstakesproducts]




AS
BEGIN


select sid,prd_name from prd_sweepstakes order by sid desc

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetStateListById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetStateListById]

@Stateid bigint


AS
BEGIN

select state_id,state_name,country_id,state_province ,tax_rate from State_master where state_id=@Stateid


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[tbl_referer]') AND type in (N'U'))
BEGIN
CREATE TABLE [tonerplus].[tbl_referer](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[Cust_id] [bigint] NULL,
[hear_from] [nvarchar](100) NULL,
[hear_from_value] [bigint] NULL,
[referaldate] [datetime] NULL,
CONSTRAINT [PK_tbl_referer] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[brands]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[brands](
[brand_id] [bigint] IDENTITY(1,1) NOT NULL,
[brand_name] [nvarchar](50) NULL,
CONSTRAINT [PK_brands] PRIMARY KEY CLUSTERED
(
[brand_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_StateById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_StateById]
@Stateid bigint
AS
BEGIN
delete from state_master where state_id=@Stateid

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetProductDetailsById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Sp_GetProductDetailsById]
@prd_id bigint



AS
BEGIN
select P.prd_id,P.prd_name,P.prd_details,P.prd_quantity,P.prd_price,
''ProductImages/ThumbnailImage/''+IG.image_path as image_path from products P,Image_gallery IG where P.prd_id=IG.prd_id
and P.prd_id=@prd_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetProducts]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

create PROCEDURE [dbo].[Sp_GetProducts]




AS
BEGIN
select P.prd_id,P.prd_name,
''ProductImages/ThumbnailImage/''+IG.image_path as image_path from products P,Image_gallery IG where P.prd_id=IG.prd_id





END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetIdByEmail]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[GetIdByEmail]

@Email nvarchar(100)
AS
BEGIN
select id, emailid,status from tbl_subscribers where emailid=@Email
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdatetEmailForUnsubscribe]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[UpdatetEmailForUnsubscribe]
@email nvarchar(100),
@Exists int output
AS
BEGIN

if exists(select emailid from tbl_subscribers where emailid=@email)
begin
update tbl_subscribers set status=2 where emailid=@email
--delete from tbl_subscribers where emailid=@email
set @Exists=1
end
else
begin

set @Exists=0
end
return

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[counter]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[counter](
[sid] [bigint] IDENTITY(1,1) NOT NULL,
[page_name] [nvarchar](50) NULL,
[url] [nvarchar](100) NULL,
[ip_address] [nvarchar](50) NULL,
[hits] [bigint] NULL,
[date] [datetime] NULL,
CONSTRAINT [PK_counter] PRIMARY KEY CLUSTERED
(
[sid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[allproductsnew]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[allproductsnew](
[product_id] [bigint] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](255) NULL,
[SubCategoryName] [nvarchar](255) NULL,
[BrandName] [nvarchar](255) NULL,
[ProductName] [nvarchar](255) NULL,
[ProductShortDescription] [nvarchar](255) NULL,
[Product Details] [nvarchar](255) NULL,
[Featured] [float] NULL,
[Sell Online] [float] NULL,
[Quantity] [float] NULL,
[Price] [float] NULL,
[PageTitle] [nvarchar](255) NULL,
[Meta Description] [nvarchar](255) NULL,
[Meta Keywords] [nvarchar](255) NULL,
[Product Weight] [float] NULL,
[Product Height] [float] NULL,
[Product Width] [float] NULL,
[Product Length] [float] NULL,
[Image Path] [nvarchar](255) NULL,
[Prd_features] [nvarchar](255) NULL,
[subcategroy1] [nvarchar](255) NULL,
[subcategory2] [nvarchar](255) NULL,
[part_number] [nvarchar](255) NULL,
[part_name] [nvarchar](255) NULL,
[compatible_brand] [nvarchar](255) NULL,
[compatible_models] [nvarchar](255) NULL,
[oem_cross_ref_compatibilities] [nvarchar](255) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllFAQDetailsById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[GetAllFAQDetailsById]
@FAQId bigint

AS
BEGIN
select * from faq where faq_id=@FAQId
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PackageDeliveryMst]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PackageDeliveryMst](
[PDId] [bigint] IDENTITY(1,1) NOT NULL,
[Code] [varchar](50) NULL,
[ShipmentMode] [varchar](50) NULL,
CONSTRAINT [PK_PackageDeliveryMst] PRIMARY KEY CLUSTERED
(
[PDId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_FAQById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' -- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_FAQById]
@faq_id bigint
AS
BEGIN
delete from faq where faq_id=@faq_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[Enable_Disbale_SweepStakes]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'--@status 1 - enabled
--@status 2 - Disabled
--@status 3 - cannot disable
CREATE Proc [tonerplus].[Enable_Disbale_SweepStakes]
(
@productID bigint,
@mode int,


@status int Output
)
AS
Declare @count int
IF @mode=0
begin
insert into [tbl_SweepStakes](ProductID,P_Count) values(@productID,0)
set @status=1
end
else if @mode=1
begin
Delete From [tbl_SweepStakes] where ProductID=@productID
set @status=2
end
else if @mode=2
begin
select @count=max(P_Count) From [tbl_SweepStakes] where ProductID=@productID
set @count=@count+1
Update [tbl_SweepStakes] set P_Count=@count where ProductID=@productID
set @status=0
end '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[CheckSweepStakes]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'Create Proc [tonerplus].[CheckSweepStakes]
(
@productID bigint,
@status int Output
)
AS
IF exists(select ProductID From tbl_SweepStakes Where ProductID=@productID)
begin
set @status=1
end
else
begin
set @status=0
end


'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_sweepstakes_users]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_sweepstakes_users](
[uid] [bigint] IDENTITY(1,1) NOT NULL,
[prd_id] [bigint] NULL,
[name] [nvarchar](50) NULL,
[email] [nvarchar](50) NULL,
[phone] [nvarchar](20) NULL,
[comments] [text] NULL,
[ip_address] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteNewletterDetailsByID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
create PROCEDURE [dbo].[DeleteNewletterDetailsByID]
@id bigint
AS
BEGIN

delete from tbl_subscribers where id=@id

END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[sp_PagedItems]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [tonerplus].[sp_PagedItems]
(
@Page int,
@RecsPerPage int
)
AS

-- We don''t want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON


--Create a temporary table
CREATE TABLE #TempItems
(

ID int IDENTITY,
prd_name nvarchar(50),
prd_price decimal(18,2),
prd_short_description nvarchar(250),
prd_image nvarchar(300),
url nvarchar(300)
)

--select top(1)* from products
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (prd_name ,prd_short_description ,prd_image ,
prd_price ,
url )

select substring(P.prd_name,0,24) as prd_name,
substring(P.prd_short_description,0,110) as prd_short_description ,

--(select top(1)(image_path) from image_gallery IG where IG.prd_id=P.prd_id) as prd_image
case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''ProductImages/ThumbnailImage/noimage.jpg'' else ''ProductImages/ThumbnailImage/''+P.image_path end
as prd_image

,P.prd_price
,replace(replace(replace(replace(replace(replace(lower(P.prd_name),'' '',''-''),''&'',''-''),''/'',''-''),'','',''-''),''*'',''''),''"'','''')+''-productdetail''+''-''+cast(P.prd_id as varchar)+''.aspx'' as url

from products P ,Brands B where P.brand_id=B.brand_id ORDER BY P.prd_id

--SELECT Name,Price FROM tblItem ORDER BY Price

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec -- Turn NOCOUNT back OFF SET NOCOUNT OFF ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[product_temp]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[product_temp]( [prd_name] [varchar](50) NULL, [sub_cat] [varchar](50) NULL, [category] [varchar](50) NULL, [brandname] [varchar](50) NULL, [quantity] [varchar](50) NULL, [price] [varchar](50) NULL ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sheet1$]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Sheet1$]( [PART_NUMBER] [nvarchar](255) NULL, [PART_NAME] [nvarchar](255) NULL, [PRICE] [float] NULL, [UNIT_OF_ISSUE] [nvarchar](255) NULL, [ITEMS_PER_UOI] [float] NULL, [OEM_NAME] [nvarchar](255) NULL, [OEM_PART_NO] [nvarchar](255) NULL, [DESCRIPTION] [nvarchar](255) NULL, [DAYS_ARO] [float] NULL, [LONG_DESCRIPTION] [nvarchar](255) NULL, [NSN] [nvarchar](255) NULL, [UPC] [nvarchar](255) NULL, [IMAGE_DESCRIPTION] [nvarchar](255) NULL, [IMAGE_REF] [float] NULL, [ENAC_CODE] [nvarchar](255) NULL, [UNSPSC] [nvarchar](255) NULL, [HAZMAT] [nvarchar](255) NULL, [PRICE_TBD] [nvarchar](255) NULL, [CLASS_CODE] [nvarchar](255) NULL, [SHIP_WEIGHT] [nvarchar](255) NULL, [SHIP_WEIGHT_UOM] [float] NULL, [Compatible Brand] [nvarchar](255) NULL, [Compatible models] [nvarchar](255) NULL, [OEM cross ref for Compatibles] [nvarchar](255) NULL, [SHIP_DIM_UOM] [nvarchar](255) NULL, [PROD_WEIGHT] [nvarchar](255) NULL, [PROD_WEIGHT_UOM] [nvarchar](255) NULL, [PROD_LENGTH] [nvarchar](255) NULL, [PROD_WIDTH] [nvarchar](255) NULL, [PROD_HEIGHT] [nvarchar](255) NULL, [PROD_DIM_UOM] [nvarchar](255) NULL, [FSC_CODE] [nvarchar](255) NULL, [PKG_UOS] [nvarchar](255) NULL, [PKG_IPUOS] [nvarchar](255) NULL, [PKG_PRICE] [nvarchar](255) NULL, [NSN_CERT_CODE] [nvarchar](255) NULL, [COG_CODE] [nvarchar](255) NULL, [OEM_CAGE_CODE] [nvarchar](255) NULL, [ITEM_TYPE] [nvarchar](255) NULL, [SALE_START] [nvarchar](255) NULL, [SALE_END] [nvarchar](255) NULL, [PB1_QTY] [nvarchar](255) NULL, [PB1_Z1_PRICE] [nvarchar](255) NULL, [PB1_Z1_SALE] [nvarchar](255) NULL, [PB1_Z2_PRICE] [nvarchar](255) NULL, [PB1_Z2_SALE] [nvarchar](255) NULL, [PB1_Z3_PRICE] [nvarchar](255) NULL, [PB1_Z3_SALE] [nvarchar](255) NULL, [PB1_Z4_PRICE] [nvarchar](255) NULL, [PB1_Z4_SALE] [nvarchar](255) NULL, [PB1_Z5_PRICE] [nvarchar](255) NULL, [PB1_Z5_SALE] [nvarchar](255) NULL, [PB1_Z6_PRICE] [nvarchar](255) NULL, [PB1_Z6_SALE] [nvarchar](255) NULL, [PB1_Z7_PRICE] [nvarchar](255) NULL, [PB1_Z7_SALE] [nvarchar](255) NULL, [PB1_Z8_PRICE] [nvarchar](255) NULL, [PB1_Z8_SALE] [nvarchar](255) NULL, [PB1_Z9_PRICE] [nvarchar](255) NULL, [PB1_Z9_SALE] [nvarchar](255) NULL, [PB1_Z10_PRICE] [nvarchar](255) NULL, [PB1_Z10_SALE] [nvarchar](255) NULL, [PB2_QTY] [nvarchar](255) NULL, [PB2_Z1_PRICE] [nvarchar](255) NULL, [PB2_Z1_PRICE1] [nvarchar](255) NULL, [PB2_Z2_PRICE] [nvarchar](255) NULL, [PB2_Z2_PRICE1] [nvarchar](255) NULL, [PB2_Z3_PRICE] [nvarchar](255) NULL, [PB2_Z3_SALE] [nvarchar](255) NULL, [PB2_Z4_PRICE] [nvarchar](255) NULL, [PB2_Z4_SALE] [nvarchar](255) NULL, [PB2_Z5_PRICE] [nvarchar](255) NULL, [PB2_Z5_SALE] [nvarchar](255) NULL, [PB2_Z6_PRICE] [nvarchar](255) NULL, [PB2_Z6_SALE] [nvarchar](255) NULL, [PB2_Z7_PRICE] [nvarchar](255) NULL, [PB2_Z7_SALE] [nvarchar](255) NULL, [PB2_Z8_PRICE] [nvarchar](255) NULL, [PB2_Z8_SALE] [nvarchar](255) NULL, [PB2_Z9_PRICE] [nvarchar](255) NULL, [PB2_Z9_SALE] [nvarchar](255) NULL, [PB2_Z10_PRICE] [nvarchar](255) NULL, [PB2_Z10_SALE] [nvarchar](255) NULL, [PB3_QTY] [nvarchar](255) NULL, [PB3_Z1_PRICE] [nvarchar](255) NULL, [PB3_Z1_PRICE1] [nvarchar](255) NULL, [PB3_Z2_PRICE] [nvarchar](255) NULL, [PB3_Z2_PRICE1] [nvarchar](255) NULL, [PB3_Z3_PRICE] [nvarchar](255) NULL, [PB3_Z3_SALE] [nvarchar](255) NULL, [PB3_Z4_PRICE] [nvarchar](255) NULL, [PB3_Z4_SALE] [nvarchar](255) NULL, [PB3_Z5_PRICE] [nvarchar](255) NULL, [PB3_Z5_SALE] [nvarchar](255) NULL, [PB3_Z6_PRICE] [nvarchar](255) NULL, [PB3_Z6_SALE] [nvarchar](255) NULL, [PB3_Z7_PRICE] [nvarchar](255) NULL, [PB3_Z7_SALE] [nvarchar](255) NULL, [PB3_Z8_PRICE] [nvarchar](255) NULL, [PB3_Z8_SALE] [nvarchar](255) NULL, [PB3_Z9_PRICE] [nvarchar](255) NULL, [PB3_Z9_SALE] [nvarchar](255) NULL, [PB3_Z10_PRICE] [nvarchar](255) NULL, [PB3_Z10_SALE] [nvarchar](255) NULL, [PB4_QTY] [nvarchar](255) NULL, [PB4_Z1_PRICE] [nvarchar](255) NULL, [PB4_Z1_PRICE1] [nvarchar](255) NULL, [PB4_Z2_PRICE] [nvarchar](255) NULL, [PB4_Z2_PRICE1] [nvarchar](255) NULL, [PB4_Z3_PRICE] [nvarchar](255) NULL, [PB4_Z3_SALE] [nvarchar](255) NULL, [PB4_Z4_PRICE] [nvarchar](255) NULL, [PB4_Z4_SALE] [nvarchar](255) NULL, [PB4_Z5_PRICE] [nvarchar](255) NULL, [PB4_Z5_SALE] [nvarchar](255) NULL, [PB4_Z6_PRICE] [nvarchar](255) NULL, [PB4_Z6_SALE] [nvarchar](255) NULL, [PB4_Z7_PRICE] [nvarchar](255) NULL, [PB4_Z7_SALE] [nvarchar](255) NULL, [PB4_Z8_PRICE] [nvarchar](255) NULL, [PB4_Z8_SALE] [nvarchar](255) NULL, [PB4_Z9_PRICE] [nvarchar](255) NULL, [PB4_Z9_SALE] [nvarchar](255) NULL, [PB4_Z10_PRICE] [nvarchar](255) NULL, [PB4_Z10_SALE] [nvarchar](255) NULL, [PB5_QTY] [nvarchar](255) NULL, [PB5_Z1_PRICE] [nvarchar](255) NULL, [PB5_Z1_PRICE1] [nvarchar](255) NULL, [PB5_Z2_PRICE] [nvarchar](255) NULL, [PB5_Z2_PRICE1] [nvarchar](255) NULL, [PB5_Z3_PRICE] [nvarchar](255) NULL, [PB5_Z3_SALE] [nvarchar](255) NULL, [PB5_Z4_PRICE] [nvarchar](255) NULL, [PB5_Z4_SALE] [nvarchar](255) NULL, [PB5_Z5_PRICE] [nvarchar](255) NULL, [PB5_Z5_SALE] [nvarchar](255) NULL, [PB5_Z6_PRICE] [nvarchar](255) NULL, [PB5_Z6_SALE] [nvarchar](255) NULL, [PB5_Z7_PRICE] [nvarchar](255) NULL, [PB5_Z7_SALE] [nvarchar](255) NULL, [PB5_Z8_PRICE] [nvarchar](255) NULL, [PB5_Z8_SALE] [nvarchar](255) NULL, [PB5_Z9_PRICE] [nvarchar](255) NULL, [PB5_Z9_SALE] [nvarchar](255) NULL, [PB5_Z10_PRICE] [nvarchar](255) NULL, [PB5_Z10_SALE] [nvarchar](255) NULL, [PB6_QTY] [nvarchar](255) NULL, [PB6_Z1_PRICE] [nvarchar](255) NULL, [PB6_Z1_PRICE1] [nvarchar](255) NULL, [PB6_Z2_PRICE] [nvarchar](255) NULL, [PB6_Z2_PRICE1] [nvarchar](255) NULL, [PB6_Z3_PRICE] [nvarchar](255) NULL, [PB6_Z3_SALE] [nvarchar](255) NULL, [PB6_Z4_PRICE] [nvarchar](255) NULL, [PB6_Z4_SALE] [nvarchar](255) NULL, [PB6_Z5_PRICE] [nvarchar](255) NULL, [PB6_Z5_SALE] [nvarchar](255) NULL, [PB6_Z6_PRICE] [nvarchar](255) NULL, [PB6_Z6_SALE] [nvarchar](255) NULL, [PB6_Z7_PRICE] [nvarchar](255) NULL, [PB6_Z7_SALE] [nvarchar](255) NULL, [PB6_Z8_PRICE] [nvarchar](255) NULL, [PB6_Z8_SALE] [nvarchar](255) NULL, [PB6_Z9_PRICE] [nvarchar](255) NULL, [PB6_Z9_SALE] [nvarchar](255) NULL, [PB6_Z10_PRICE] [nvarchar](255) NULL, [PB6_Z10_SALE] [nvarchar](255) NULL, [PB7_QTY] [nvarchar](255) NULL, [PB7_Z1_PRICE] [nvarchar](255) NULL, [PB7_Z1_PRICE1] [nvarchar](255) NULL, [PB7_Z2_PRICE] [nvarchar](255) NULL, [PB7_Z2_PRICE1] [nvarchar](255) NULL, [PB7_Z3_PRICE] [nvarchar](255) NULL, [PB7_Z3_SALE] [nvarchar](255) NULL, [PB7_Z4_PRICE] [nvarchar](255) NULL, [PB7_Z4_SALE] [nvarchar](255) NULL, [PB7_Z5_PRICE] [nvarchar](255) NULL, [PB7_Z5_SALE] [nvarchar](255) NULL, [PB7_Z6_PRICE] [nvarchar](255) NULL, [PB7_Z6_SALE] [nvarchar](255) NULL, [PB7_Z7_PRICE] [nvarchar](255) NULL, [PB7_Z7_SALE] [nvarchar](255) NULL, [PB7_Z8_PRICE] [nvarchar](255) NULL, [PB7_Z8_SALE] [nvarchar](255) NULL, [PB7_Z9_PRICE] [nvarchar](255) NULL, [PB7_Z9_SALE] [nvarchar](255) NULL, [PB7_Z10_PRICE] [nvarchar](255) NULL, [PB7_Z10_SALE] [nvarchar](255) NULL, [PB8_QTY] [nvarchar](255) NULL, [PB8_Z1_PRICE] [nvarchar](255) NULL, [PB8_Z1_PRICE1] [nvarchar](255) NULL, [PB8_Z2_PRICE] [nvarchar](255) NULL, [PB8_Z2_PRICE1] [nvarchar](255) NULL, [PB8_Z3_PRICE] [nvarchar](255) NULL, [PB8_Z3_SALE] [nvarchar](255) NULL, [PB8_Z4_PRICE] [nvarchar](255) NULL, [PB8_Z4_SALE] [nvarchar](255) NULL, [PB8_Z5_PRICE] [nvarchar](255) NULL, [PB8_Z5_SALE] [nvarchar](255) NULL, [PB8_Z6_PRICE] [nvarchar](255) NULL, [PB8_Z6_SALE] [nvarchar](255) NULL, [PB8_Z7_PRICE] [nvarchar](255) NULL, [PB8_Z7_SALE] [nvarchar](255) NULL, [PB8_Z8_PRICE] [nvarchar](255) NULL, [PB8_Z8_SALE] [nvarchar](255) NULL, [PB8_Z9_PRICE] [nvarchar](255) NULL, [PB8_Z9_SALE] [nvarchar](255) NULL, [PB8_Z10_PRICE] [nvarchar](255) NULL, [PB8_Z10_SALE] [nvarchar](255) NULL, [PB9_QTY] [nvarchar](255) NULL, [PB9_Z1_PRICE] [nvarchar](255) NULL, [PB9_Z1_PRICE1] [nvarchar](255) NULL, [PB9_Z2_PRICE] [nvarchar](255) NULL, [PB9_Z2_PRICE1] [nvarchar](255) NULL, [PB9_Z3_PRICE] [nvarchar](255) NULL, [PB9_Z3_SALE] [nvarchar](255) NULL, [PB9_Z4_PRICE] [nvarchar](255) NULL, [PB9_Z4_SALE] [nvarchar](255) NULL, [PB9_Z5_PRICE] [nvarchar](255) NULL, [PB9_Z5_SALE] [nvarchar](255) NULL, [PB9_Z6_PRICE] [nvarchar](255) NULL, [PB9_Z6_SALE] [nvarchar](255) NULL, [PB9_Z7_PRICE] [nvarchar](255) NULL, [PB9_Z7_SALE] [nvarchar](255) NULL, [PB9_Z8_PRICE] [nvarchar](255) NULL, [PB9_Z8_SALE] [nvarchar](255) NULL, [PB9_Z9_PRICE] [nvarchar](255) NULL, [PB9_Z9_SALE] [nvarchar](255) NULL, [PB9_Z10_PRICE] [nvarchar](255) NULL, [PB9_Z10_SALE] [nvarchar](255) NULL, [PB10_QTY] [nvarchar](255) NULL, [PB10_Z1_PRICE] [nvarchar](255) NULL, [PB10_Z1_PRICE1] [nvarchar](255) NULL, [PB10_Z2_PRICE] [nvarchar](255) NULL, [PB10_Z2_PRICE1] [nvarchar](255) NULL, [PB10_Z3_PRICE] [nvarchar](255) NULL, [PB10_Z3_SALE] [nvarchar](255) NULL, [PB10_Z4_PRICE] [nvarchar](255) NULL, [PB10_Z4_SALE] [nvarchar](255) NULL, [PB10_Z5_PRICE] [nvarchar](255) NULL, [PB10_Z5_SALE] [nvarchar](255) NULL, [PB10_Z6_PRICE] [nvarchar](255) NULL, [PB10_Z6_SALE] [nvarchar](255) NULL, [PB10_Z7_PRICE] [nvarchar](255) NULL, [PB10_Z7_SALE] [nvarchar](255) NULL, [PB10_Z8_PRICE] [nvarchar](255) NULL, [PB10_Z8_SALE] [nvarchar](255) NULL, [PB10_Z9_PRICE] [nvarchar](255) NULL, [PB10_Z9_SALE] [nvarchar](255) NULL, [PB10_Z10_PRICE] [nvarchar](255) NULL, [PB10_Z10_SALE] [nvarchar](255) NULL, [F252] [nvarchar](255) NULL, [F253] [nvarchar](255) NULL ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[sp_PagedItemsbybrand_id]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [tonerplus].[sp_PagedItemsbybrand_id] ( @Page int, @RecsPerPage int , @Brand_id bigint ) AS -- We don''t want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Create a temporary table CREATE TABLE #TempItems ( ID int IDENTITY, prd_name nvarchar(50), prd_price decimal(18,2), prd_short_description nvarchar(250), prd_image nvarchar(300), url nvarchar(300) ) --select top(1)* from products -- Insert the rows from tblItems into the temp. table INSERT INTO #TempItems (prd_name ,prd_short_description ,prd_image , prd_price , url ) select substring(P.prd_name,0,24) as prd_name, substring(P.prd_short_description,0,110) as prd_short_description , --(select top(1)(image_path) from image_gallery IG where IG.prd_id=P.prd_id) as prd_image case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''/ProductImages/ThumbnailImage/noimage.jpg'' else ''/ProductImages/ThumbnailImage/''+P.image_path end as prd_image , P.prd_price ,replace(replace(replace(replace(replace(replace(lower(P.prd_name),'' '',''-''),''&'',''-''),''/'',''-''),'','',''-''),''*'',''''),''"'','''')+''-productdetail''+''-''+cast(P.prd_id as varchar)+''.aspx'' as url from products P ,Brands B where P.brand_id=B.brand_id and P.Brand_id=@Brand_id order by P.prd_id --SELECT Name,Price FROM tblItem ORDER BY Price -- Find out the first and last record we want DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@Page - 1) * @RecsPerPage SELECT @LastRec = (@Page * @RecsPerPage + 1) -- Now, return the set of paged records, plus, an indiciation of we -- have more records or not! SELECT *, MoreRecords = ( SELECT COUNT(*) FROM #TempItems TI WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec -- Turn NOCOUNT back OFF SET NOCOUNT OFF ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[sp_PagedItemsbycat_id]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [tonerplus].[sp_PagedItemsbycat_id] ( @Page int, @RecsPerPage int , @Cat_Id bigint ) AS -- We don''t want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Create a temporary table CREATE TABLE #TempItems ( ID int IDENTITY, prd_name nvarchar(50), prd_price decimal(18,2), prd_short_description nvarchar(250), prd_image nvarchar(300), url nvarchar(300) ) --select top(1)* from products -- Insert the rows from tblItems into the temp. table INSERT INTO #TempItems (prd_name ,prd_short_description ,prd_image , prd_price , url ) select substring(P.prd_name,0,24) as prd_name, substring(P.prd_short_description,0,110) as prd_short_description , --(select top(1)(image_path) from image_gallery IG where IG.prd_id=P.prd_id) as prd_image case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''ProductImages/ThumbnailImage/noimage.jpg'' else ''ProductImages/ThumbnailImage/''+P.image_path end as prd_image ,P.prd_price,replace(replace(replace(replace(replace(lower(P.prd_name),'' '',''-''),''&'',''-''),''/'',''-''),'','',''-''),''"'','''')+''-productdetail''+''-''+cast(P.prd_id as varchar)+''.aspx'' as url from products P ,Brands B,Multiple_category MC where MC.prd_id=P.prd_id and P.brand_id=B.brand_id and MC.cat_id=@Cat_Id order by P.prd_id --SELECT Name,Price FROM tblItem ORDER BY Price -- Find out the first and last record we want DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@Page - 1) * @RecsPerPage SELECT @LastRec = (@Page * @RecsPerPage + 1) -- Now, return the set of paged records, plus, an indiciation of we -- have more records or not! SELECT *, MoreRecords = ( SELECT COUNT(*) FROM #TempItems TI WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec -- Turn NOCOUNT back OFF SET NOCOUNT OFF ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_contact]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[cms_contact]( [contact_id] [bigint] IDENTITY(1,1) NOT NULL, [contact_name] [nvarchar](50) NULL, [contact_address] [text] NULL, [email] [nvarchar](50) NULL, CONSTRAINT [PK_cms_contact] PRIMARY KEY CLUSTERED ( [contact_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[Enable_Disbale_SweepStakes1]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'--@status 1 - enabled --@status 2 - Disabled --@status 3 - cannot disable Create Proc [tonerplus].[Enable_Disbale_SweepStakes1] ( @productID bigint, @mode int, @draw_date datetime, @draw_message nvarchar(300), @status int Output ) AS Declare @count int IF @mode=0 begin insert into [tbl_SweepStakes] values(@productID,0,@draw_date,@draw_message) set @status=1 end else if @mode=1 begin Delete From [tbl_SweepStakes] where ProductID=@productID set @status=2 end else if @mode=2 begin select @count=max(P_Count) From [tbl_SweepStakes] where ProductID=@productID set @count=@count+1 Update [tbl_SweepStakes] set P_Count=@count where ProductID=@productID set @status=0 end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_newsletter]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[tbl_newsletter]( [id] [bigint] IDENTITY(1,1) NOT NULL, [title] [text] NULL, [subject] [text] NULL, [contents] [text] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertUpdateCustomers]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author:
-- Created date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertUpdateCustomers]
@Flag bit,
@Cust_id bigint,
@Cust_fname varchar(50),
@Cust_lname varchar(50),
@Cust_email nvarchar(100),
@Cust_pwd nvarchar(100),
@Cust_address nvarchar(200),
@Cust_City nvarchar(50),
@Cust_Phone varchar(50),
@Country_id bigint,
@Cust_company_name nvarchar(50),
@Provision_code nvarchar(3),
@State_id bigint,
@Cust_promo_code varchar(50) ,

@Cust_zip varchar(50),
@CreatedDate datetime,
@Mobile nvarchar(20),
@Fax nvarchar(20),
@Exists int output

AS
BEGIN
if(@Flag=1)
begin

if exists(select * from Customers where cust_fname=@Cust_fname or Cust_email=@Cust_email)
begin
set @Exists=1
end
else
begin
insert into Customers values(@Cust_fname,@Cust_lname,@Cust_email,@Cust_pwd,@Cust_address,@Cust_City,@Cust_Phone,@Country_id,
@Cust_company_name, @Provision_code,
@State_id,
@Cust_promo_code,@Cust_zip,@CreatedDate,@Mobile,@Fax)
set @Exists=0
end
end
else
begin
update Customers set Cust_fname=@Cust_fname,Cust_lname=@Cust_lname,Cust_email=@Cust_email,Cust_pwd=@Cust_pwd,Cust_address=@Cust_address,Cust_City=@Cust_City,Cust_Phone=@Cust_Phone,Cust_zip=@Cust_zip ,Provision_code=@Provision_code,
State_id=@State_id,CreatedDate=@CreatedDate,Mobile=@Mobile,Fax=@Fax where Cust_id=@Cust_id


set @Exists=0
end
return

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[admin_users]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[admin_users](
[user_id] [bigint] IDENTITY(1,1) NOT NULL,
[user_name] [varchar](30) NOT NULL,
[email_id] [nvarchar](50) NULL,
[password] [varchar](20) NOT NULL,
[first_name] [varchar](20) NOT NULL,
[last_name] [varchar](50) NULL,
[active] [bit] NULL,
CONSTRAINT [PK_admin_users] PRIMARY KEY CLUSTERED
(
[user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_subscribers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_subscribers](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[subscriber_name] [nvarchar](50) NULL,
[emailid] [nvarchar](100) NULL,
[status] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_CheckUserExists]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'


CREATE Procedure [dbo].[Sp_CheckUserExists]
(
@Cust_id bigint output,
@Password varchar(20),
@Cust_email nvarchar(100)
)
AS

BEGIN



if exists(Select cust_id from customers where Cust_pwd=@Password and Cust_email=@Cust_email)
begin
select @Cust_id=(Select cust_id from customers where Cust_pwd=@Password and Cust_email=@Cust_email)--''1'' --If Success return 1

end
else
begin
select @Cust_id=''0'' --if Invalid UserId

end
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetStateListByCountryId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetStateListByCountryId]

@country_id bigint


AS
BEGIN

select state_id,state_name,country_id,state_province from State_master where country_id=@country_id


END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Customers](
[Cust_id] [bigint] IDENTITY(1,1) NOT NULL,
[Cust_fname] [varchar](50) NULL,
[Cust_lname] [varchar](50) NULL,
[Cust_email] [nvarchar](100) NULL,
[Cust_pwd] [nvarchar](100) NULL,
[Cust_address] [nvarchar](200) NULL,
[Cust_City] [nvarchar](50) NULL,
[Cust_Phone] [varchar](50) NULL,
[Country_id] [bigint] NULL,
[Cust_company_name] [nvarchar](50) NULL,
[Provision_code] [nvarchar](3) NULL,
[State_id] [bigint] NULL,
[Cust_promo_code] [varchar](50) NULL,
[Cust_zip] [varchar](50) NULL,
[CreatedDate] [datetime] NULL,
[Mobile] [nvarchar](20) NULL,
[Fax] [nvarchar](20) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[Cust_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[multiple_category]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[multiple_category](
[cat_id] [bigint] NOT NULL,
[prd_id] [bigint] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[state_master]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[state_master](
[state_id] [bigint] IDENTITY(1,1) NOT NULL,
[state_name] [nvarchar](50) NULL,
[country_id] [bigint] NULL,
[state_province] [nvarchar](3) NULL,
[tax_rate] [decimal](18, 2) NULL,
CONSTRAINT [PK_state_master] PRIMARY KEY CLUSTERED
(
[state_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[products]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[products](
[prd_id] [bigint] IDENTITY(1,1) NOT NULL,
[part_number] [nvarchar](50) NULL,
[part_name] [nvarchar](50) NULL,
[brand_id] [bigint] NULL,
[prd_name] [varchar](300) NOT NULL,
[prd_short_description] [nvarchar](250) NULL,
[prd_details] [nvarchar](max) NULL,
[prd_features] [nvarchar](max) NULL,
[featured] [bit] NULL,
[sell_online] [bit] NULL,
[prd_quantity] [decimal](18, 2) NULL,
[image_path] [nvarchar](300) NULL,
[prd_price] [decimal](18, 2) NULL,
[title] [nvarchar](max) NULL,
[meta_desc] [nvarchar](max) NULL,
[meta_keywords] [nvarchar](max) NULL,
[prd_weight] [decimal](18, 2) NULL,
[prd_height] [decimal](18, 2) NULL,
[prd_width] [decimal](18, 2) NULL,
[length] [decimal](18, 2) NULL,
CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED
(
[prd_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Orders](
[Od_id] [bigint] IDENTITY(1,1) NOT NULL,
[Od_date] [datetime] NULL,
[Od_status] [int] NULL,
[Od_shipping_fname] [varchar](50) NULL,
[Od_shipping_lname] [varchar](50) NOT NULL,
[Od_shipping_address1] [varchar](50) NULL,
[Od_shipping_address2] [varchar](50) NOT NULL,
[Od_shipping_phone] [varchar](50) NULL,
[Od_shipping_city] [varchar](50) NULL,
[Od_shipping_state] [varchar](50) NOT NULL,
[Od_shipping_zip] [varchar](50) NOT NULL,
[Od_shipping_mobile] [nvarchar](20) NULL,
[Od_shipping_fax] [nvarchar](20) NULL,
[Os_shipping_cost] [decimal](18, 2) NOT NULL,
[Cust_id] [bigint] NOT NULL,
[OrderNo] [nvarchar](50) NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[Od_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Image_gallery]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Image_gallery](
[prd_id] [bigint] NULL,
[image_id] [bigint] IDENTITY(1,1) NOT NULL,
[image_path] [nvarchar](300) NULL,
CONSTRAINT [PK_Image_gallery] PRIMARY KEY CLUSTERED
(
[image_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[shoppingcart]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[shoppingcart](
[cart_id] [bigint] IDENTITY(1,1) NOT NULL,
[prd_id] [bigint] NULL,
[prd_quantity] [bigint] NULL,
[cart_session_id] [nvarchar](50) NULL,
[cart_date] [datetime] NULL,
[od_id] [bigint] NULL,
CONSTRAINT [PK_shoppingcart] PRIMARY KEY CLUSTERED
(
[cart_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_InsertShippingDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_InsertShippingDetails]
@cart_session_id nvarchar(50),
@Od_id bigint,
@prd_id bigint,
@Rate_Method nvarchar(MAX),
@Rate_Amount decimal(18, 2),
@status int,
@Date datetime
AS
BEGIN
if exists(select * from shipping where cart_session_id=@cart_session_id and prd_id=@prd_id and Od_id=@Od_id)
begin
--delete from shipping where cart_session_id=@cart_session_id and Od_id=@Od_id


update shipping set Rate_Method=@Rate_Method,status=@status ,
Rate_Amount=@Rate_Amount,
Date=@Date where cart_session_id=@cart_session_id and prd_id=@prd_id and Od_id=@Od_id
end
else
begin
insert into shipping values(@cart_session_id,@prd_id,@Od_id,
@Rate_Method, @status ,
@Rate_Amount,
@Date)
end

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetInvoiceById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetInvoiceById]
@SessionId nvarchar(50)



AS
BEGIN

--image changed
--old path: ''ProductImages/ThumbnailImage/''+
select SC.cart_id,SC.prd_id,SC.prd_quantity,SC.cart_date,SC.od_id, P.prd_weight,
P.prd_name,P.prd_price,isnull(SC.prd_quantity,1) as qty,
isnull(SC.prd_quantity,1) * P.prd_price as Total,
isnull(SC.prd_quantity,1) * P.prd_price +S.Rate_Amount as subtotal,
case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as img_path ,

S.Rate_Amount
from shoppingcart SC
inner join products P on SC.prd_id=P.prd_id inner join shipping S
on S.cart_session_id=SC.cart_session_id
where S.cart_session_id=@SessionId and SC.prd_id=S.prd_id
and S.status=1 and SC.Od_id=S.Od_id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductsById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetAllProductsById]
@prd_id bigint

AS
BEGIN

select P.part_number,P.part_name, P.featured,prd_name,brand_id,prd_short_description,prd_details, P.prd_features,
P.sell_online,P.prd_quantity,P.prd_price ,(select parent from prd_category where cat_id in (select cat_id from multiple_category where prd_id=@prd_id)) as parent,(select cat_id from multiple_category where prd_id=@prd_id) as subcat,
P.title,P.meta_desc,P.meta_keywords ,P.prd_weight,P.prd_height,P.prd_width,P.length
from products P
where prd_id=@prd_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductsCategoriesForlistnew]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[Sp_GetAllProductsCategoriesForlistnew]


AS
BEGIN

select PC.cat_id,PC.cat_name ,PC.parent, (select top(1)(''ProductImages/ThumbnailImage/''+image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=PC.cat_id) as Img_path
from prd_category PC --where PC.parent=0




END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductsCategoriesnew]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetAllProductsCategoriesnew]


AS
BEGIN
----image path changed
-- select top(11)* from (
--select PC.cat_id,PC.cat_name ,PC.parent, (select top(1)(image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
--inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=PC.cat_id) as Img_path
--,replace(replace(lower(PC.cat_name),'' '',''-''),''&'',''-'')+''-products''+''-''+cast(PC.cat_id as varchar)+''.aspx'' as url
--from prd_category PC
--where PC.parent>0 ) prd


--image path changed
select top(11)* from (
select PC.cat_id,PC.cat_name ,


isnull((
(select top(1)(IG.image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=PC.cat_id)),''ProductImages/noimage.jpg'') as Img_path ,



replace(replace(lower(PC.cat_name),'' '',''-''),''&'',''-'')+''-products''+''-''+cast(PC.cat_id as varchar)+''.aspx'' as url
from prd_category PC
where PC.cat_id not in (118,119,120,121,122) ) prd



END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetMultipleCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetMultipleCategories]

AS
BEGIN

select cat_id,cat_name from prd_category

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertProductCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertProductCategory]

@cat_id bigint,
@Flag bit,
@cat_name varchar(30),
@parent bigint,

@Exists int output

AS
BEGIN
declare @no int
if(@Flag=1)
begin

if exists(select * from prd_category where cat_name=@cat_name)
begin
set @Exists=1
end
else
begin

insert into prd_category values(@cat_name,@parent)
set @Exists=0
end
end
else
begin
if(exists(select cat_name from (select * from prd_category where cat_id not in(@cat_id)) Category

where cat_name=@cat_name))

begin
set @Exists=1
end
else
begin

declare @par bigint
set @par=(select parent from prd_category where cat_id=@cat_id )
if(@par=0)
begin
update prd_category set cat_name=@cat_name where cat_id=@cat_id
set @Exists=0
end
else
begin
update prd_category set cat_name=@cat_name ,parent=@parent where cat_id=@cat_id
set @Exists=0
end

end

end
return

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_DeleteProductCategoryById1]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:

-- =============================================
create PROCEDURE [dbo].[Sp_DeleteProductCategoryById1]
@cat_id bigint ,
@Exists int output
AS
BEGIN

--need to chk if any related product is connected with.
delete from prd_category where cat_id=@cat_id
set @Exists=1

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetAllProductCategories]

AS
BEGIN

select PC.cat_id,PC.cat_name from prd_category PC
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetProductCategoryById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetProductCategoryById]
@cat_id bigint
AS
BEGIN
select PC.cat_id,PC.cat_name ,PC.parent
from prd_category PC where PC.cat_id=@cat_id
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[CSVFileUploadnew]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE proc [tonerplus].[CSVFileUploadnew]




@category nvarchar(50),
@subcat nvarchar(50),
@brandname nvarchar(50),
@pdctname nvarchar(50),
@pdctdesc nvarchar(250),
@pdctdetails nvarchar(max),
@featured nvarchar(50),
@sellonline nvarchar(50),
@quantity nvarchar(50),
@price nvarchar(50),
@pagetitle nvarchar(50),
@metadescription nvarchar(max),
@metakeyword nvarchar(max),
@pdctweight nvarchar(50),
@pdctheight nvarchar(50),
@pdctwidth nvarchar(50),
@pdctlenght nvarchar(50),
@imagepath nvarchar(50),
@prd_features nvarchar(max)



as
BEGIN
declare @catid int,@subcatid int,@brandid int,@pdctid int

-----------check product----------

--if not exists (select * from products where products.prd_id in(select prd_id from multiple_category where cat_id
-- in (select cat_id from prd_category where cat_name=@subcat))
--and brand_id in (select brand_id from brands where brand_name=@brandname)
--and products.prd_name=@pdctname )
if not exists (select prd_id from products where prd_name=@pdctname)
begin
----------validate category-----------
if exists (select cat_id from prd_category where cat_name=@category)
begin
select @catid=cat_id from prd_category where cat_name=@category


---------------Validate Subcategory--------------
if exists (select cat_id from prd_category where cat_name=@subcat)
begin
select @subcatid=cat_id from prd_category where cat_name=@subcat
end
else
begin
insert into prd_category values(@subcat,@catid)
select @subcatid=max(cat_id) from prd_category
end

----------------Validate BrandName------------------
if exists(select brand_id from brands where brand_name=@brandname)
begin
select @brandid=brand_id from brands where brand_name=@brandname
end
else
begin
insert into brands values(@brandname)
select @brandid=max(brand_id) from brands
end
--------------Insert Product------------------
if exists(select products.prd_id from products ,multiple_category where products.prd_id=multiple_category.prd_id
and brand_id=@brandid and products.prd_name=@pdctname and multiple_category.cat_id=@subcatid )
begin
select ''exists''
end
else
begin
insert into products (brand_id,
prd_name,
prd_short_description,
prd_details,
featured,
sell_online,
prd_quantity,
prd_price,
title,
meta_desc,
meta_keywords,
prd_weight,
prd_height,
prd_width,
length,
prd_features)

values (@brandid,
@pdctname,
@pdctdesc,
@pdctdetails,
@featured,
@sellonline,
@quantity,
@price,
@pagetitle,
@metadescription,
@metakeyword,
@pdctweight,
@pdctheight,
@pdctwidth,
@pdctlenght,
@prd_features

)


select @pdctid=max(prd_id) from products
insert into multiple_category values(@subcatid,@pdctid)
insert into Image_gallery(prd_id,image_path) values(@pdctid,@imagepath)
select ''not exists'',max(prd_id) from products
end
end
else
begin
select ''cat not exists''
end

end
else
begin
select ''exists''

end
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductsFeatured]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetAllProductsFeatured]
@Cat_id bigint


AS
BEGIN

select top(3)* from(
select P.prd_id ,B.brand_name,substring(P.prd_name,0,24) as prd_name,substring(P.prd_short_description,0,120) as prd_short_description,P.prd_details,P.featured,
--P.image_path as prd_image
case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as prd_image


--(select top(1)(''ProductImages/ThumbnailImage/''+image_path) from image_gallery IG where IG.prd_id=P.prd_id) as prd_image
,P.prd_quantity,P.prd_price ,replace(replace(replace(replace(replace(lower(P.prd_name),'' '',''-''),''&'',''-''),''/'',''-''),'','',''-''),''"'','''')+''-productdetail''+''-''+cast(P.prd_id as varchar)+''.aspx'' as url
from products P ,Brands B,Multiple_category MC where
P.brand_id=B.brand_id and MC.prd_id=P.prd_id and MC.cat_id in(select cat_id from multiple_category where cat_id in(select cat_id from prd_category where parent=@Cat_id) ) and P.featured=1 ) featured


END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetInkAndTonerList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' -- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[GetInkAndTonerList]
@prd_id bigint
AS
BEGIN


select MC.cat_id,P.prd_id,PC.parent from prd_category PC inner join multiple_category MC on MC.cat_id=PC.cat_id inner join products P on P.prd_id =MC.prd_id where P.prd_id=@prd_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductsCategorieslistnew]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create PROCEDURE [dbo].[Sp_GetAllProductsCategorieslistnew]


AS
BEGIN
----image path changed
-- select top(11)* from (
--select PC.cat_id,PC.cat_name ,PC.parent, (select top(1)(image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
--inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=PC.cat_id) as Img_path
--,replace(replace(lower(PC.cat_name),'' '',''-''),''&'',''-'')+''-products''+''-''+cast(PC.cat_id as varchar)+''.aspx'' as url
--from prd_category PC
--where PC.parent>0 ) prd
select PC.cat_id,PC.cat_name ,PC.parent, (select top(1)(''ProductImages/ThumbnailImage/''+image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=PC.cat_id) as Img_path
from prd_category PC where PC.cat_id not in (118,119,120,121,122)



END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[GetRecentUploadedProducts]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE proc [tonerplus].[GetRecentUploadedProducts]

@start int,
@end int




as
BEGIN

delete from product_temp
select prd_name,(select cat_name from prd_category where cat_id in(select cat_id from multiple_category where prd_id=P.prd_id)) as subcat,
(select cat_name from prd_category where cat_id in (select parent from prd_category where cat_id in(select cat_id from multiple_category where prd_id=P.prd_id))) as cat_name,
(select brand_name from brands where brand_id=P.brand_id) as brand_name,
P.prd_quantity,P.prd_price
from products P
where P.prd_id between @start and @end
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[CSVFileUpload]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE proc [tonerplus].[CSVFileUpload]

@category varchar(50),
@subcat varchar(50),
@brandname nvarchar(50),
@pdctname nvarchar(300),
@pdctdesc nvarchar(250),
@pdctdetails nvarchar(max),
@featured varchar(50),
@sellonline varchar(50),
@quantity varchar(50),
@price varchar(50),
@pagetitle nvarchar(max),
@metadescription nvarchar(max),
@metakeyword nvarchar(max),
@pdctweight varchar(50),
@pdctheight varchar(50),
@pdctwidth varchar(50),
@pdctlenght varchar(50),
@imagepath nvarchar(300),
@prd_features nvarchar(max),
@part_number nvarchar(50),
@part_name nvarchar(50)




as
BEGIN

declare @keyword nvarchar(max)
declare @url nvarchar(max)
declare @prd_short nvarchar(250)
set @prd_short =(select replace(@pdctdesc,''#'','',''))


declare @page_title nvarchar(max)
set @page_title =(select replace(@pagetitle,''#'','',''))

declare @metakey nvarchar(max)
set @metakey =(select replace(@metakeyword,''#'','',''))

declare @metadesc nvarchar(max)
set @metadesc =(select replace(@metadescription,''#'','',''))
declare @prd_detail nvarchar(max)
set @prd_detail =(select replace(@pdctdetails,''#'','',''))
declare @brand nvarchar(50)
set @brand=(select replace(@brandname,''/'',''-''))
declare @prodname nvarchar(300)
set @prodname=(select replace(@pdctname,''#'','',''))
declare @catid int,@subcatid int,@brandid int,@pdctid int

-----------check product----------

--if not exists (select * from products where products.prd_id in(select prd_id from multiple_category where cat_id
-- in (select cat_id from prd_category where cat_name=@subcat))
--and brand_id in (select brand_id from brands where brand_name=@brandname)
--and products.prd_name=@pdctname )
if not exists (select prd_id from products where prd_name=@prodname)
begin
----------validate category-----------
if exists (select cat_id from prd_category where cat_name=@category)
begin
select @catid=cat_id from prd_category where cat_name=@category


---------------Validate Subcategory--------------
if exists (select cat_id from prd_category where cat_name=@subcat)
begin
select @subcatid=cat_id from prd_category where cat_name=@subcat
end
else
begin
insert into prd_category values(@subcat,@catid)
select @subcatid=max(cat_id) from prd_category
end

----------------Validate BrandName------------------
if exists(select brand_id from brands where brand_name=@brandname)
begin
select @brandid=brand_id from brands where brand_name=@brand
end
else
begin
insert into brands values(@brandname)
select @brandid=max(brand_id) from brands
end
--------------Insert Product------------------
if exists(select products.prd_id from products ,multiple_category where products.prd_id=multiple_category.prd_id
and brand_id=@brandid and products.prd_name=@prodname and multiple_category.cat_id=@subcatid )
begin
select ''exists''
end
else
begin
insert into products (part_number,part_name,brand_id,
prd_name,
prd_short_description,
prd_details,
featured,
sell_online,
prd_quantity, image_path,
prd_price,
title,
meta_desc,
meta_keywords,
prd_weight,
prd_height,
prd_width,
length,
prd_features)

values (@part_number,@part_name,@brandid,
@prodname,
@prd_short,
@prd_detail,
@featured,
@sellonline,
@quantity,
@imagepath,
@price,
@page_title,
@metadesc,
@metakeyword,
@pdctweight,
@pdctheight,
@pdctwidth,
@pdctlenght,
@prd_features

)


select @pdctid=max(prd_id) from products
set @keyword=(select @brandname+'' ''+@part_number+'' ''+part_name+'' ''+ prd_name+prd_short_description+prd_details from products where prd_id=@pdctid)
set @url=replace(replace(replace(replace(lower(@prodname),'' '',''-''),''&'',''-''),''/'',''-''),'','',''-'')+''-productdetail''+''-''+cast(@pdctid as varchar)+''.aspx''

insert into multiple_category values(@subcatid,@pdctid)
insert into Image_gallery(prd_id,image_path) values(@pdctid,@imagepath)


insert into search values(@pdctid,1,@prodname, @url,@keyword,getdate(),null,null)
select ''not exists'',max(prd_id) from products
end
end
else
begin
select ''cat not exists''
end



end
else
begin
select ''exists''

end
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_ShippingCalculationDetails_freeshipping]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_ShippingCalculationDetails_freeshipping]
@SessionId nvarchar(50)

AS
BEGIN
select P.prd_id
from products P
inner join shoppingcart SC on SC.prd_id=P.prd_id
where cart_session_id=@SessionId

and
P.prd_id in(select prd_id from multiple_category where cat_id in
(select cat_id from prd_category where parent=118)) and SC.prd_quantity*P.prd_price >50




END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetShoppingCartById_new]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetShoppingCartById_new]
@SessionId nvarchar(50)



AS
BEGIN

--select * from shoppingcart


select SC.cart_id,SC.prd_id,SC.prd_quantity,SC.cart_date,SC.od_id,P.prd_weight,P.prd_name,P.prd_price,isnull(SC.prd_quantity,1) as qty,isnull(SC.prd_quantity,1) * P.prd_price as Total ,
(select top(1)''ProductImages/ThumbnailImage/''+image_path from image_gallery where prd_id=P.prd_id) as img_path
from shoppingcart SC
inner join products P on SC.prd_id=P.prd_id
inner join multiple_category MC on MC.prd_id=P.prd_id inner join prd_category PC on PC.cat_id=MC.cat_id
where cart_session_id=@SessionId --and SC.od_id is NULL
and
P.prd_id in (select prd_id from multiple_category where cat_id in (select cat_id from prd_category where parent=2)
)


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_ShippingCalculationDetails_new]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[Sp_ShippingCalculationDetails_new]
@SessionId nvarchar(50)

AS
BEGIN
select P.prd_id,P.prd_name,P.prd_weight,P.prd_height,P.prd_width,P.length as prd_length,SC.prd_quantity,prd_price
from products P
inner join shoppingcart SC on SC.prd_id=P.prd_id
where cart_session_id=@SessionId

and
P.prd_id not in(select prd_id from multiple_category where cat_id in
(select cat_id from prd_category where parent=118) and SC.prd_quantity*P.prd_price >50 )

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetProductCategoryNameByCatId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[GetProductCategoryNameByCatId]
@Cat_id bigint
AS
BEGIN

select PC.cat_name from prd_category PC where PC.cat_id= @Cat_id
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetCategoryNamesInkAndToner]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetCategoryNamesInkAndToner]

AS
BEGIN


select cat_id,''Ink and Toner'' as cat_name,parent,replace(lower(cat_name),'' '',''-'')+''-productcategories''+''-''+cast(cat_id as varchar)+''.aspx'' as url from prd_category where cat_id=118--parent=0

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductsCategoriesnew_All]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetAllProductsCategoriesnew_All]


AS
BEGIN
----image path changed
-- select top(11)* from (
--select PC.cat_id,PC.cat_name ,PC.parent, (select top(1)(image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
--inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=PC.cat_id) as Img_path
--,replace(replace(lower(PC.cat_name),'' '',''-''),''&'',''-'')+''-products''+''-''+cast(PC.cat_id as varchar)+''.aspx'' as url
--from prd_category PC
--where PC.parent>0 ) prd


----image path changed
--
--select PC.cat_id,PC.cat_name ,
--
--
--isnull((
--(select top(1)(P.image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
--inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=PC.cat_id)),''ProductImages/noimage.jpg'') as Img_path ,
--
--
--
--replace(replace(lower(PC.cat_name),'' '',''-''),''&'',''-'')+''-products''+''-''+cast(PC.cat_id as varchar)+''.aspx'' as url
--from prd_category PC
--where PC.cat_id not in (118,119,120,121,122)






select PC.cat_id,PC.cat_name ,


isnull((

select top(1)image_path from (select (P.image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=PC.cat_id where P.image_path<>'''') ss),''ProductImages/noimage.jpg'')
as Img_path ,

replace(replace(lower(PC.cat_name),'' '',''-''),''&'',''-'')+''-products''+''-''+cast(PC.cat_id as varchar)+''.aspx'' as url
from prd_category PC
where PC.cat_id not in (118,119,120,121,122)



END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetCategoryNames]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetCategoryNames]

AS
BEGIN


select cat_id,cat_name,parent,replace(lower(cat_name),'' '',''-'')+''-productcategories''+''-''+cast(cat_id as varchar)+''.aspx'' as url from prd_category where parent=0

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductsCategorieslist]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Sp_GetAllProductsCategorieslist]


AS
BEGIN

select PC.cat_id,PC.cat_name ,PC.parent, (select top(1)(''ProductImages/ThumbnailImage/''+image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=PC.cat_id) as Img_path
from prd_category PC




END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductsCategoriesForlist]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Sp_GetAllProductsCategoriesForlist]


AS
BEGIN

select PC.cat_id,PC.cat_name ,PC.parent, (select top(1)(''ProductImages/ThumbnailImage/''+P.image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=PC.cat_id) as Img_path
from prd_category PC where PC.parent=0




END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_AdminGetAllParents]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Sp_AdminGetAllParents]


AS
BEGIN

--select PC.cat_id,PC.cat_name ,PC.parent, (select top(1)(''ProductImages/ThumbnailImage/''+image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
--inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=PC.cat_id) as Img_path
--from prd_category PC where
select PC.cat_id,PC.parent,PC.cat_name from prd_category PC where parent=0




END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_AdminGetAllSubCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Sp_AdminGetAllSubCategories]

@Cat_Id bigint
AS
BEGIN

--select PC.cat_id,PC.cat_name ,PC.parent, (select top(1)(''ProductImages/ThumbnailImage/''+image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
--inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=PC.cat_id) as Img_path
--from prd_category PC where
select PC.cat_id,PC.parent,PC.cat_name from prd_category PC where parent=@Cat_Id




END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetProductParentCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetProductParentCategory]

AS
BEGIN
select PC.cat_id,PC.cat_name
from prd_category PC where PC.parent=0
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductsCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetAllProductsCategories]


AS
BEGIN

select PC.cat_id,PC.cat_name ,PC.parent, (select top(1)(''ProductImages/ThumbnailImage/''+image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=PC.cat_id) as Img_path
,replace(replace(lower(PC.cat_name),'' '',''-''),''&'',''-'')+''-products''+''-''+cast(PC.cat_id as varchar)+''.aspx'' as url
from prd_category PC
where PC.parent>0



END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetProductSubCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetProductSubCategory]
@parent bigint
AS
BEGIN
select PC.cat_id,PC.cat_name
from prd_category PC where PC.parent=@parent
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetBrandNameForToner]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[GetBrandNameForToner]

AS
BEGIN



select distinct(b.brand_name),b.brand_id from brands b inner join products p on b.brand_id=p.brand_id
inner join multiple_category m on m.prd_id=p.prd_id inner join prd_category pc on pc.cat_id =m.cat_id
where pc.parent=118

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductsCategoriesForMenu]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Sp_GetAllProductsCategoriesForMenu]
@Cat_id bigint

AS
BEGIN
select PC.cat_id,PC.cat_name ,PC.parent,


(select top(1) image_path from ( select p.image_path from products p inner
join multiple_category mc on mc.prd_id=p.prd_id
where PC.parent=@Cat_id and p.image_path<>'''' and mc.cat_id=PC.cat_id)ss) as Img_path,


replace(replace(lower(PC.cat_name),'' '',''-''),''&'',''-'')+''-products''+''-''+cast(PC.cat_id as varchar)+''.aspx'' as url

from prd_category PC
where PC.parent=@Cat_id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCategoriesByBrand_id]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[GetCategoriesByBrand_id]
@brand_id bigint
AS
BEGIN



select distinct(pc.cat_id),upper(pc.cat_name) as cat_name from prd_category pc inner join multiple_category m on
m.cat_id=pc.cat_id

inner join products p on p.prd_id=m.prd_id where p.brand_id=@brand_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSupportCMS]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[GetSupportCMS]




AS
BEGIN

select * from support_category where cat_id not in(5)


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllSupportCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetAllSupportCategory]

AS
BEGIN

select cat_id,cat_name from support_category

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertUpdateSupportCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[InsertUpdateSupportCategory]

@Cat_id bigint,
@Flag bit,
@Cat_name nvarchar(50),

@Exists int output

AS
BEGIN
declare @no int
if(@Flag=1)
begin

if exists(select * from support_category where cat_name=@Cat_name)
begin
set @Exists=1
end
else
begin

insert into support_category values(@Cat_name,1)
set @Exists=0
end
end
else
begin

if exists(select * from support_category where cat_name=@Cat_name)

begin
update support_category set cat_name=@Cat_name where cat_id=@cat_id
set @Exists=0
end


end
return

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSupportCategoryName]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [dbo].[GetSupportCategoryName]

@Cat_id bigint


AS
BEGIN

select cat_name from support_category where cat_id=@Cat_id


END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetShippingRate]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
create PROCEDURE [dbo].[Sp_GetShippingRate]
@Session nvarchar(50)

AS
BEGIN

select Os_shipping_cost from orders O ,shoppingcart SC where O.Od_id =SC.Od_id and cart_session_id=@Session

END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_UpdateShippingCost]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_UpdateShippingCost]
@OrderId bigint,
@ShippingCost decimal(18,2)


AS
BEGIN


update orders set Os_shipping_cost =@ShippingCost where Od_id=@OrderId

END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_UpdateOrderStatus1]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
create PROCEDURE [dbo].[Sp_UpdateOrderStatus1]
@OrderStatus int,
@OrderNo nvarchar(50),
@Od_id bigint

AS
BEGIN

update orders set Od_status=@OrderStatus,OrderNo=@OrderNo where Od_id=@Od_id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_ProductById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_ProductById]
@prd_id bigint ,
@status int output
AS
BEGIN

if exists(select prd_id from order_items OI inner join orders O on OI.od_id=O.od_id where OI.prd_id =@prd_id and O.os_shipping_cost>0.00 )
begin
set @status=0
end
else
begin
delete from orders where orderno in (select od_id from order_items where prd_id=@prd_id)
delete from order_items where prd_id=@prd_id
delete from multiple_category where prd_id=@prd_id
delete from image_gallery where prd_id=@prd_id
delete from shoppingcart where prd_id=@prd_id
delete from search where id=@prd_id and status=1
delete from products where prd_id=@prd_id





set @status=1

end

end
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_UpdateShippingInformation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Created date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_UpdateShippingInformation]
@Od_date datetime,
@Session nvarchar(50),
@Od_shipping_fname varchar(50),
@Od_shipping_lname varchar(50),
@Od_shipping_address1 varchar(50),
@Od_shipping_address2 varchar(50),
@Od_shipping_phone varchar(50),
@Od_shipping_city varchar(50),
@Od_shipping_state varchar(50),
@Od_shipping_zip varchar(50),
@Od_shipping_mobile nvarchar(20),
@Od_shipping_fax nvarchar(20),
@Os_shipping_cost decimal(18, 2),
@Cust_id bigint ,
@Od_id bigint

AS
BEGIN



update orders set Od_date=@Od_date,
Od_shipping_fname=@Od_shipping_fname,
Od_shipping_lname=@Od_shipping_lname,--status
Od_shipping_address1=@Od_shipping_address1,
Od_shipping_address2=@Od_shipping_address2,
Od_shipping_phone=@Od_shipping_phone,
Od_shipping_city=@Od_shipping_city,
Od_shipping_state=@Od_shipping_state,
Od_shipping_mobile=@Od_shipping_mobile,
Od_shipping_fax=@Od_shipping_fax,
Od_shipping_zip=@Od_shipping_zip,
Os_shipping_cost=@Os_shipping_cost,
Cust_id=@Cust_id where Od_id=@Od_id

update shoppingcart set od_id=@Od_id where cart_session_id=@Session

delete from order_items where od_id=@Od_id



END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertShippingInformation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:
-- Created date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertShippingInformation]
@Od_date datetime,
@Session nvarchar(50),
@Od_shipping_fname varchar(50),
@Od_shipping_lname varchar(50),
@Od_shipping_address1 varchar(50),
@Od_shipping_address2 varchar(50),
@Od_shipping_phone varchar(50),
@Od_shipping_city varchar(50),
@Od_shipping_state varchar(50),
@Od_shipping_zip varchar(50),
@Od_shipping_mobile nvarchar(20),
@Od_shipping_fax nvarchar(20),
@Os_shipping_cost decimal(18, 2),
@Cust_id bigint ,
@Od_id bigint output

AS
BEGIN

insert into orders values(@Od_date,0,@Od_shipping_fname,@Od_shipping_lname,--status
@Od_shipping_address1,@Od_shipping_address2,@Od_shipping_phone,@Od_shipping_city,
@Od_shipping_state,@Od_shipping_zip,@Od_shipping_mobile,@Od_shipping_fax,@Os_shipping_cost,@Cust_id,null)
set @Od_id =@@identity
update shoppingcart set od_id=@Od_id where cart_session_id=@Session

return
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetTax]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION [dbo].[GetTax]
(
@Od_id bigint,
@Subtotal decimal(18,2)
)
RETURNS decimal(18,2)
AS
BEGIN
declare @Tax decimal(18,2)
declare @Amount decimal(18,2)
declare @Cstate nvarchar(50)
declare @tax_rate float


select @tax_rate = tax_rate from state_master where state_name=(select Od_shipping_state from orders where od_id=@Od_id)



set @Amount=(@Subtotal*@tax_rate)/100





RETURN @Amount

END'
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_DeleteOrderById1]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_DeleteOrderById1]
@OrderId bigint
AS
BEGIN
--delete from orders where Od_id=@OrderId
update orders set Od_status=4 where Od_id=@OrderId--delete
delete from order_items where Od_id=@OrderId

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetOrderDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetOrderDetails]

@OrderId bigint
AS
BEGIN

select O.Od_id,O.OrderNo,C.Cust_fname+'' ''+Cust_lname as CustName,C.Cust_fname as fname ,C.Cust_lname as lastname,C.Cust_address,C.Cust_City,C.Cust_Phone,CM.Country_name as Cust_country,
C.Cust_company_name,SM.state_name,C.Cust_zip, C.Cust_email,CM.Country_name as Cust_country,C.Mobile,C.Fax,
O.Od_shipping_fname+'' ''+O.Od_shipping_lname as sname,O.Od_shipping_address1 as saddress1,O.Od_shipping_address2 as saddress2,
O.Od_shipping_phone as sphone,O.Od_shipping_mobile as sMobile,O.Od_shipping_fname as sfname,O.Od_shipping_lname as slname,O.Od_shipping_fax as sFax,O.Od_shipping_city as scity,O.Os_shipping_cost,
O.Od_shipping_state as sstate,O.Od_shipping_zip as szip,
O.Od_date as sdate,[Status] = case
when O.Od_status =0 then ''Processing''
when O.Od_status =1 then ''Approved''
else
''Declined''
end
from Customers C,orders O,Country_master CM,state_master SM where
C.Cust_Id=O.Cust_Id and CM.Country_id=C.Country_id and C.state_id =SM.state_id
and O.Od_id=@OrderId

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_CustomerById1]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_CustomerById1]
@Cust_Id bigint ,
@Exists bigint output

AS
BEGIN
delete from orders where od_id in(select od_id from orders where cust_id =@Cust_Id and os_shipping_cost=0.00 )
--delete from orders where Cust_Id=@Cust_Id --deleting because order table not deleting any order,updating the status only.
if not exists (select * from orders where cust_id =@Cust_Id and od_status in (0))
begin
delete from orders where od_id in (select od_id from orders where cust_id =@Cust_Id and od_status not in (0))
delete from Customers where Cust_Id=@Cust_Id
set @Exists=1
end
else
begin
set @Exists=0

end


END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetTaxRateByOrderId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
create PROCEDURE [dbo].[Sp_GetTaxRateByOrderId]

@Od_id bigint


AS
BEGIN

select tax_rate from State_master where state_name =( select Od_shipping_state from orders where od_id=@Od_id)


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetTaxByID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE procedure [dbo].[GetTaxByID]
(
@Od_id bigint,
@Subtotal decimal(18,2) ,
@Amount decimal(18,2) output

)

AS
BEGIN
declare @Tax decimal(18,2)

declare @Cstate nvarchar(50)
declare @tax_rate float


select @tax_rate = tax_rate from state_master where state_name=(select Od_shipping_state from orders where od_id=@Od_id)

set @Amount=(@Subtotal*@tax_rate)/100





RETURN @Amount

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertHits]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[InsertHits]

@id bigint,
@datetime datetime,
@referal_url nvarchar(max),
@ipaddress nvarchar(50)



AS
BEGIN

insert into supp_referal values(@id,@datetime,@referal_url,@ipaddress)

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetHits]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[GetHits]

@Cat_id bigint


AS
BEGIN

select C.content_title,count(S.id) as hits,C.id from tbl_cms C inner join supp_referal S on C.id=S.id
group by S.id,C.content_title,C.cat_id ,C.id
having C.cat_id=@Cat_id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_InsertOrderItem]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Created date:
-- Description:
-- =============================================
Create PROCEDURE [dbo].[Sp_InsertOrderItem]
@Od_id bigint,
@prd_id bigint,
@Od_quantity int

AS
BEGIN
insert into order_items values(@Od_id,@prd_id,@Od_quantity)
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_ProductById1]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_ProductById1]
@prd_id bigint
AS
BEGIN

if exists(select prd_id from order_items where prd_id =@prd_id)
begin
print ''exist''
end
else
begin

delete from multiple_category where prd_id=@prd_id
delete from image_gallery where prd_id=@prd_id
delete from shoppingcart where prd_id=@prd_id
delete from products where prd_id=@prd_id






end

end '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertUpdatecms1]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertUpdatecms1]
@Flag bit,
@id bigint,
@Cat_id bigint,

@page_id bigint,
@page_name nvarchar(50),
@content_title nvarchar(50),
@contents text,
@page_title nvarchar(50),
@meta_keyword text,
@meta_description nvarchar(200),

@Exists int output

AS
BEGIN

declare @url nvarchar(300)
declare @keyword nvarchar(max)
declare @keyword_new nvarchar(max)
declare @pageid int
declare @Sid bigint
set @pageid=(select max(page_id)+1 from tbl_cms)
if(@Flag=1)
begin
if exists(select * from tbl_cms where page_name=@page_name )
begin
set @Exists=1
end
else
begin

insert into tbl_cms values
(@Cat_id,@pageid,null, @page_name,@content_title ,
@contents,@page_title,@meta_keyword,@meta_description)



set @pageid=@id


if(@pageid=1)
set @url=''home.aspx''
else if(@pageid=2)
set @url=''aboutus.aspx''
else if(@pageid=3)
set @url=''contactus.aspx''
else if(@pageid=4)
set @url=''customerrewards.aspx''
else if(@pageid=5)
set @url=''customerservice.aspx''

else if(@pageid=7)
set @url=''coupon.aspx''
else if(@pageid=8)
set @url=''delivery_surcharge.aspx''
else if(@pageid=9)
set @url=''email_promotions.aspx''
else if(@pageid=10)
set @url=''credit_card.aspx''
else if(@pageid=11)
set @url=''employment.aspx''
else if(@pageid=12)
set @url=''mission.aspx''
else if(@pageid=13)
set @url=''iso.aspx''
else if(@pageid=14)
set @url=''shipping_policy.aspx''
else if(@pageid=15)
set @url=''return_policy.aspx''
else if(@pageid=16)
set @url=''troubleshooting.aspx''
else if(@pageid=17)
set @url=''recycle_link.aspx''
else if(@pageid=18)
set @url=''environment.aspx''

else if(@pageid=20)
set @url=''''
else if(@pageid=21)
set @url=''sales_and_rebates.aspx''
else if(@pageid=22)
set @url=''officehours.aspx''
else if(@pageid=23)
set @url=''''
else if(@pageid=24)
set @url=''supportcenter.aspx?cid=28''
else if(@pageid=25)
set @url=''supportcenter.aspx''
else if(@pageid=26)
set @url=''supportcenter.aspx?cid=30''
else if(@pageid=27)
set @url=''supportcenter.aspx?cid=31''
else if(@pageid=28)
set @url=''supportcenter.aspx?cid=32''
else if(@pageid=29)
set @url=''supportcenter.aspx?cid=33''
else if(@pageid=30)
set @url=''supportcenter.aspx?cid=34''
else if(@pageid=31)
set @url=''supportcenter.aspx?cid=35''
else if(@pageid=32)
set @url=''supportcenter.aspx?cid=36''



else if(@pageid=33)
set @url=''supportcenter.aspx?cid=37''

else if(@pageid=34)
set @url=''supportcenter.aspx?cid=38''
else if(@pageid=35)
set @url=''supportcenter.aspx?cid=39''

else if(@pageid=36)
set @url=''supportcenter.aspx?cid=40''
else if(@pageid=37)
set @url=''supportcenter.aspx?cid=41''

else if(@pageid=38)
set @url=''supportcenter.aspx?cid=42''


else if(@pageid=39)
set @url=''supportcenter.aspx?cid=43''

else if(@pageid=40)
set @url=''supportcenter.aspx?cid=44''

else if(@pageid=41)
set @url=''supportcenter.aspx?cid=45''
else if(@pageid=42)
set @url=''supportcenter.aspx?cid=46''
else if(@pageid=43)
set @url=''supportcenter.aspx?cid=47''


set @Sid=@@identity

set @keyword=(@page_name+convert(nvarchar(max), @contents))
set @keyword_new=(select dbo.tagstrip (@keyword ))
insert into search values(1,@Sid,@page_name,@url,@keyword_new,getdate(),null,null)


set @Exists=0
end
end
else
begin


if(exists(select * from (select * from tbl_cms where page_id not in(@page_id)) cms

where page_name=@page_name))
begin
set @Exists=1
end
else
begin
set @pageid=@id
if(@pageid=1)
set @url=''home.aspx''
else if(@pageid=2)
set @url=''aboutus.aspx''
else if(@pageid=3)
set @url=''contactus.aspx''
else if(@pageid=4)
set @url=''customerrewards.aspx''
else if(@pageid=5)
set @url=''customerservice.aspx''

else if(@pageid=7)
set @url=''coupon.aspx''
else if(@pageid=8)
set @url=''delivery_surcharge.aspx''
else if(@pageid=9)
set @url=''email_promotions.aspx''
else if(@pageid=10)
set @url=''credit_card.aspx''
else if(@pageid=11)
set @url=''employment.aspx''
else if(@pageid=12)
set @url=''mission.aspx''
else if(@pageid=13)
set @url=''iso.aspx''
else if(@pageid=14)
set @url=''shipping_policy.aspx''
else if(@pageid=15)
set @url=''return_policy.aspx''
else if(@pageid=16)
set @url=''troubleshooting.aspx''
else if(@pageid=17)
set @url=''recycle_link.aspx''
else if(@pageid=18)
set @url=''environment.aspx''

else if(@pageid=20)
set @url=''''
else if(@pageid=21)
set @url=''sales_and_rebates.aspx''
else if(@pageid=22)
set @url=''officehours.aspx''
else if(@pageid=23)
set @url=''''
else if(@pageid=24)
set @url=''supportcenter.aspx?cid=28''
else if(@pageid=25)
set @url=''supportcenter.aspx''
else if(@pageid=26)
set @url=''supportcenter.aspx?cid=30''
else if(@pageid=27)
set @url=''supportcenter.aspx?cid=31''
else if(@pageid=28)
set @url=''supportcenter.aspx?cid=32''
else if(@pageid=29)
set @url=''supportcenter.aspx?cid=33''
else if(@pageid=30)
set @url=''supportcenter.aspx?cid=34''
else if(@pageid=31)
set @url=''supportcenter.aspx?cid=35''
else if(@pageid=32)
set @url=''supportcenter.aspx?cid=36''



else if(@pageid=33)
set @url=''supportcenter.aspx?cid=37''

else if(@pageid=34)
set @url=''supportcenter.aspx?cid=38''
else if(@pageid=35)
set @url=''supportcenter.aspx?cid=39''

else if(@pageid=36)
set @url=''supportcenter.aspx?cid=40''
else if(@pageid=37)
set @url=''supportcenter.aspx?cid=41''

else if(@pageid=38)
set @url=''supportcenter.aspx?cid=42''


else if(@pageid=39)
set @url=''supportcenter.aspx?cid=43''

else if(@pageid=40)
set @url=''supportcenter.aspx?cid=44''

else if(@pageid=41)
set @url=''supportcenter.aspx?cid=45''
else if(@pageid=42)
set @url=''supportcenter.aspx?cid=46''
else if(@pageid=43)
set @url=''supportcenter.aspx?cid=47''

update tbl_cms set cat_id=@Cat_id,page_id=@page_id,page_name=@page_name,content_title=@content_title ,
contents=@contents,page_title=@page_title,meta_keyword=@meta_keyword,meta_description=@meta_description
where page_id=@Id
set @keyword=(@page_name+convert(nvarchar(max), @contents))
set @keyword_new=(select dbo.tagstrip (@keyword ))
update search set keywords=@keyword_new,search_date=getdate() where id=@id and status=2
set @Exists=0
end
end





END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCMSListById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[GetCMSListById]
@page_id bigint



AS
BEGIN

select id,cat_id,page_id,page_name,content_title,contents,page_title,meta_keyword,meta_description
from tbl_cms where page_id=@page_id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSupportCMSnew]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[GetSupportCMSnew]

@cat_id bigint


AS
BEGIN

select * from tbl_cms where cat_id=@cat_id and cat_id!=5


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetIdByCategoryname]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[GetIdByCategoryname]

@cat_name nvarchar(50)


AS
BEGIN
select id from tbl_cms where content_title=@cat_name and cat_id not in(5)

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSupportsBycatId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' create PROCEDURE [dbo].[GetSupportsBycatId]

@id bigint


AS
BEGIN

select * from tbl_cms where id =@id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetDefaultSupport]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create PROCEDURE [dbo].[GetDefaultSupport]




AS
BEGIN

select * from tbl_cms where page_id =25


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_InsertMultipleCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_InsertMultipleCategories]
@prd_id bigint,
@cat_id bigint
AS
BEGIN

if not exists(select * from multiple_category where prd_id=@prd_id and cat_id=@cat_id)
begin
insert into multiple_category values(@cat_id,@prd_id)
end
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_DeleteMultipleCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
create PROCEDURE [dbo].[Sp_DeleteMultipleCategories]
@prd_id bigint

AS
BEGIN
delete from multiple_category where prd_id=@prd_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_UpdateMultipleCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_UpdateMultipleCategories]
@prd_id bigint,
@cat_id bigint
AS
BEGIN


if not exists(select prd_id from multiple_category where prd_id=@prd_id and cat_id=@cat_id)
begin
insert into multiple_category values(@cat_id,@prd_id)
end



END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetProductCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
create PROCEDURE [dbo].[Sp_GetProductCategories]
@prd_id bigint

AS
BEGIN

select cat_id,prd_id from multiple_category where prd_id=@prd_id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCountProductsByCatId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetCountProductsByCatId]

@Cat_Id bigint


AS
BEGIN

--path changed.
--old path ''ProductImages/ThumbnailImage/''+
select count(*)
from products P ,Brands B,Multiple_category MC where MC.prd_id=P.prd_id and P.brand_id=B.brand_id and MC.cat_id=@Cat_Id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllProductsByCatId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create PROCEDURE [dbo].[GetAllProductsByCatId]

@Cat_Id bigint


AS
BEGIN

select P.prd_id ,B.brand_name,P.prd_name,substring(P.prd_short_description,0,120) as prd_short_description ,P.prd_details,P.featured,
(select top(1)(''ProductImages/ThumbnailImage/''+image_path) from image_gallery IG where IG.prd_id=P.prd_id) as prd_image
,P.prd_quantity,P.prd_price
from products P ,Brands B,Multiple_category MC where MC.prd_id=P.prd_id and P.brand_id=B.brand_id and MC.cat_id=@Cat_id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllProductsByCatIdForList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[GetAllProductsByCatIdForList]

@Cat_Id bigint


AS
BEGIN

--path changed.
--old path ''ProductImages/ThumbnailImage/''+
select P.prd_id ,B.brand_name,substring(P.prd_name,0,24) as prd_name,substring(P.prd_short_description,0,110) as prd_short_description ,P.prd_details,P.featured,
--(select top(1)(image_path) from image_gallery IG where IG.prd_id=P.prd_id) as prd_image
case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as prd_image
,P.prd_quantity,P.prd_price,replace(replace(replace(replace(replace(lower(P.prd_name),'' '',''-''),''&'',''-''),''/'',''-''),'','',''-''),''"'','''')+''-productdetail''+''-''+cast(P.prd_id as varchar)+''.aspx'' as url
from products P ,Brands B,Multiple_category MC where MC.prd_id=P.prd_id and P.brand_id=B.brand_id and MC.cat_id=@Cat_Id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[GetCategoryImage]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION [tonerplus].[GetCategoryImage]
(
@cat_id bigint
)
RETURNS nvarchar(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @img_path nvarchar(100)


set @img_path= (select top(1)(''ProductImages/ThumbnailImage/''+image_path) from image_gallery IG inner join Products P on P.prd_id =IG.prd_id
inner join multiple_category MC on MC.prd_id=IG.prd_id and MC.cat_id=@cat_id)
-- Add the T-SQL statements to compute the return value here

RETURN @img_path

END
'
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllProductsByCatIdandBrandIdForList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[GetAllProductsByCatIdandBrandIdForList]

@Cat_Id bigint,
@Brand_id bigint


AS
BEGIN

--path changed.
--old path ''ProductImages/ThumbnailImage/''+
select P.prd_id ,B.brand_name,substring(P.prd_name,0,24) as prd_name,substring(P.prd_short_description,0,115) as prd_short_description ,P.prd_details,P.featured,
--(select top(1)(image_path) from image_gallery IG where IG.prd_id=P.prd_id) as prd_image
case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as prd_image
,P.prd_quantity,P.prd_price,replace(replace(replace(replace(replace(lower(P.prd_name),'' '',''-''),''&'',''-''),''/'',''-''),'','',''-''),''"'','''')+''-productdetail''+''-''+cast(P.prd_id as varchar)+''.aspx'' as url
from products P ,Brands B,Multiple_category MC where MC.prd_id=P.prd_id and P.brand_id=B.brand_id and MC.cat_id=@Cat_Id
and P.brand_id=@Brand_id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetProductDetailsByIdForDisplay]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetProductDetailsByIdForDisplay]
@prd_id bigint



AS
BEGIN

select (select brand_name from brands where brand_id=P.brand_id) as brandname,part_number,part_name,P.prd_id,P.prd_name,P.prd_details,P.prd_features,
P.prd_quantity,P.prd_price, P.image_path as image_path_thumb ,

case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''ProductImages/noimage.jpg'' else ''ProductImages/''+P.image_path end
as prd_image ,
P.title,P.meta_desc,P.meta_keywords
from products P,Image_gallery IG where P.prd_id=IG.prd_id
and P.prd_id=@prd_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetProductDetailsByIdForDisplay_sweepstakes]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetProductDetailsByIdForDisplay_sweepstakes]
@prd_id bigint



AS
BEGIN
select P.prd_id,P.prd_name,P.prd_details,P.prd_features,P.prd_quantity,P.prd_price,

case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as image_path_thumb ,

P.title,P.meta_desc,
P.meta_keywords,convert(nvarchar(12), S.draw_date) as date,S.draw_message

from products P,Image_gallery IG,tbl_sweepstakes S where P.prd_id=IG.prd_id and P.prd_id=S.productId
and P.prd_id=@prd_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertCMS]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertCMS]


@homepagetext text,
@about_us text,

@contact_us text


AS
BEGIN


if exists(select * from cms)
begin
update cms set home=@homepagetext,about_us=@about_us,contact_us=@contact_us
end




END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetCMS]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetCMS]


AS
BEGIN

Select * from cms
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_InsertTestimonials]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'--sp_helptext Sp_InsertTestimonials



-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_InsertTestimonials]

@TestId bigint,
@Author varchar(50),
@Contents nvarchar(max) ,
@Flag bit,
@Exists int output
AS
BEGIN

if(@Flag=1)
begin
if exists(select * from Testimonials where contents=@Contents)
begin
--update Testimonials set Author=@Author,contents=@Contents
set @Exists=1
end
else
begin
insert into Testimonials values(@Author,@Contents)
set @Exists=0
end

end
else
begin
update Testimonials set Author=@Author,contents=@Contents where test_id=@TestId
end

return
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_Testimonial]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_Testimonial]
@testid bigint
AS
BEGIN
delete from testimonials where test_id=@testid
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetTestimonials]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
create PROCEDURE [dbo].[Sp_GetTestimonials]

AS
BEGIN

select * from testimonials

END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetTestimonialById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetTestimonialById]
@testid bigint

AS
BEGIN

Select * from testimonials where test_id=@testid
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllJobs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'






-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetAllJobs]




AS
BEGIN

select * from jobs


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_JobById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' -- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_JobById]
@job_id bigint
AS
BEGIN
delete from jobs where job_id=@job_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetJobById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetJobById]

@job_id bigint


AS
BEGIN

select * from jobs where job_id=@job_id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertUpdateCMSnew]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertUpdateCMSnew]
@Flag bit,
@job_id bigint,
@job_title nvarchar(50),
@emp_status nvarchar(20),
@description text,
@contract_type nvarchar(20),
@job_available nvarchar(30),
@experience nvarchar(50),
@qualification nvarchar(50),
@workingplace text,
@job_begin_date nvarchar(50),
@creation_date nvarchar(50),

@Exists int output

AS
BEGIN

if(@Flag=1)
begin
if exists(select * from jobs where job_title=@job_title )
begin
set @Exists=1
end
else
begin

insert into jobs values(
@job_title,
@emp_status,
@description,
@contract_type,
@job_available,
@experience,
@qualification ,
@workingplace,
@job_begin_date ,
@creation_date )
set @Exists=0
end
end
else
begin


if(exists(select * from (select * from jobs where job_id not in(@job_id)) job

where job_title=@job_title))
begin
set @Exists=1
end
else
begin

update jobs set job_title=@job_title,
emp_status=@emp_status,
description=@description,
contract_type=@contract_type,
job_available=@job_available,
experience=@experience,
qualification=@qualification ,
workingplace=@workingplace,
job_begin_date=@job_begin_date ,
creation_date=@creation_date
where job_id=@job_id
set @Exists=0
end
end


END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertProducts]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertProducts]
@Flag bit,

@prd_id bigint,
@part_number nvarchar(50),
@partname nvarchar(50),
@brand_id bigint,
@prd_name varchar(300),
@prd_short_description nvarchar(250),
@prd_details nvarchar(max),
@prd_features nvarchar(max),
@image_path nvarchar(300),
@featured bit,
@OnlineorCall bit,
@prd_quantity decimal(18,2),
@prd_price decimal(18,2),
@title nvarchar(MAX),
@meta_desc nvarchar(MAX),
@meta_keywords nvarchar(MAX),
@Height decimal(18,2),
@Width decimal(18,2),
@Length decimal(18,2),
@Weight decimal(18,2),
@Exists int output


AS
BEGIN
declare @id bigint
declare @keyword_new nvarchar(max)
declare @keyword nvarchar(max)
declare @url nvarchar(300)
declare @brandname nvarchar(50)
if(@Flag=1)
begin

if exists(select * from products where prd_name=@prd_name)
begin
set @Exists=1
end
else
begin
insert into products values(@part_number,@partname,@brand_id,@prd_name,@prd_short_description,@prd_details,@prd_features,@featured,0,@prd_quantity,@image_path,@prd_price,@title,@meta_desc,@meta_keywords,@Weight,@Height,@Width,@Length)


set @id=@@identity
set @brandname=(select brand_name from brands where brand_id=@brand_id)
set @url=replace(replace(replace(replace(lower(@prd_name),'' '',''-''),''&'',''-''),''/'',''-''),'','',''-'')+''-productdetail''+''-''+cast(@id as varchar)+''.aspx''
set @keyword=(@brandname+'' ''+@prd_name+'' ''+@part_number+'' ''+@partname+'' ''+@prd_short_description+@prd_details)
set @keyword_new=(select dbo.tagstrip (@keyword ))

insert into search values(@id,1,@prd_name,@url,@keyword_new,getdate(),null,null)
set @Exists=0
end
end
else
begin

if(exists(select prd_name from (select * from products where prd_id not in(@prd_id)) brand

where prd_name=ltrim(@prd_name)))
begin
set @Exists=1
end
else
begin


update products set part_number=@part_number,part_name=@partname, brand_id=@brand_id,prd_name=@prd_name,prd_short_description=@prd_short_description,prd_details=@prd_details,prd_features=@prd_features,
featured=@featured,prd_quantity=@prd_quantity,--image_path= @image_path ,
prd_price=@prd_price, title=@title,meta_desc=@meta_desc,meta_keywords=@meta_keywords ,prd_weight=@Weight, prd_height=@Height,prd_width=@Width,length=@Length
where prd_id=@prd_id

set @brandname=(select brand_name from brands where brand_id=@brand_id)
set @keyword=(@brandname+'' ''+@prd_name+'' ''+@part_number+'' ''+@partname+'' ''+@prd_short_description+'' ''+@prd_details)


set @keyword_new=(select dbo.tagstrip (@keyword ))
update search set keywords=@keyword_new ,search_date=getdate() where id=@prd_id and status=1



set @Exists=0

end

end
return
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertUpdateFAQ]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertUpdateFAQ]
@Flag bit,
@faq_id bigint,
@option int,
@faq_quest nvarchar(500),
@faq_ans text,
@Exists int output

AS
BEGIN

if(@Flag=1)
begin
if exists(select * from faq where faq_quest=@faq_quest and options=@option )
begin
set @Exists=1
end
else
begin

insert into faq values(@option,@faq_quest,@faq_ans)
set @Exists=0
end
end
else
begin


if(exists(select * from (select * from faq where faq_id not in(@faq_id)) faq

where faq_quest=@faq_quest))
begin
set @Exists=1
end
else
begin

update faq set options=@option, faq_quest=@faq_quest ,faq_ans=@faq_ans
where faq_id=@faq_id
set @Exists=0
end
end

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllFAQList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[GetAllFAQList]
@option int

AS
BEGIN
select * from faq where options=@option
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetSearchResult]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetSearchResult]
@keyword nvarchar(max)


AS
BEGIN


-- select id, title,status,substring(keywords,0,200) as keywords ,link from search where keywords like ''%''+@keyword+''%''


select S.id, S.title,S.status,substring(S.keywords,0,200) as keywords ,S.link,
isnull((select case substring(image_path,0,5) when ''http'' then
image_path when '''' then ''ProductImages/noimage.jpg'' else ''ProductImages/''+image_path end
as prd_image
from products where prd_id=S.id and S.status<>2),''ProductImages/noimage.jpg'')
as prd_image,(select prd_price from products where prd_id=S.id and S.status<>2 ) as prd_price from search S where S.keywords like ''%''+@keyword+''%''



END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertUpdateCountry]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertUpdateCountry]
@Flag bit,
@Country_id bigint,
@Country_name nvarchar(50),
@Exists int output

AS
BEGIN

if(@Flag=1)
begin
if exists(select * from country_master where country_name=ltrim(@Country_name) )
begin
set @Exists=1
end
else
begin

insert into country_master values(@Country_name)
set @Exists=0
end
end
else
begin


if(exists(select country_name from (select * from country_master where country_id not in(@Country_id)) country

where country_name=ltrim(@Country_name)))
begin
set @Exists=1
end
else
begin

update country_master set country_name=@Country_name
where country_id=@Country_id
set @Exists=0
end



end
return

END


'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetCountryList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetCountryList]




AS
BEGIN

select Country_id,Country_name from Country_master


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetCountryListById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'




-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetCountryListById]

@CountryId bigint


AS
BEGIN

select Country_id,Country_name from Country_master where country_id=@CountryId


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetStateList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetStateList]



AS
BEGIN

select SM.state_id,SM.state_name,CM.Country_name,SM.state_province from State_master SM,Country_master CM
where SM.country_id=CM.country_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_CountryById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_CountryById]
@Country_id bigint
AS
BEGIN
delete from Country_master where country_id=@Country_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllCustomersById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Sp_GetAllCustomersById]
@Cust_id bigint
AS
BEGIN
select C.Cust_id,C.Cust_fname,C.Cust_lname,C.Cust_email,C.Cust_pwd,C.Cust_address,C.Cust_City, C.Cust_Phone,
C.Country_id,C.Cust_company_name,C.Provision_code,
SM.state_name,C.mobile,C.fax,C.Cust_promo_code,C.Cust_Zip,convert(nvarchar(10), C.CreatedDate,105) as CreatedDate from Customers C ,state_master SM ,country_master CM
where C.state_id=SM.state_id and CM.country_id=SM.country_id and cust_id=@Cust_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[Enable_Disbale_SweepStakesNew]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Proc [tonerplus].[Enable_Disbale_SweepStakesNew]
(
@productID bigint,
@mode int,

@drawdate datetime,
@drawmessage nvarchar(max),
@status int Output

)
AS
Declare @count int
IF @mode=0
begin
insert into [tbl_SweepStakes](ProductID,P_Count,draw_date,draw_message) values(@productID,0,@drawdate,@drawmessage)
set @status=1
end
else if @mode=1
begin
if exists(select name from tbl_sweepstakes_users where prd_id=@productID)
begin
set @status=4
end
else
begin
Delete From [tbl_SweepStakes] where ProductID=@productID
set @status=2
end

end
else if @mode=2
begin
select @count=max(P_Count) From [tbl_SweepStakes] where ProductID=@productID
set @count=@count+1
Update [tbl_SweepStakes] set P_Count=@count where ProductID=@productID
set @status=0
end '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[InsertSweepstakes]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE Proc [tonerplus].[InsertSweepstakes]
(



@sid bigint,
@prd_name nvarchar(300),
@img_path nvarchar(300),
@drawdate datetime,
@messaage nvarchar(MAX),
@mode int,
@status int Output

)
AS
Declare @count int
IF @mode=0
begin

if(exists(select prd_name from prd_sweepstakes where prd_name=@prd_name))
begin
set @status=0
end
else

begin
insert into prd_sweepstakes
values(0,@prd_name,@img_path,@drawdate,@messaage)
set @status=1
end

end
else if @mode=3
begin
update prd_sweepstakes set prd_name=@prd_name,img_path=@img_path,drawdate=@drawdate,messaage=@messaage
where sid=@sid
set @status=3
end
else if @mode=1
begin
if exists(select name from tbl_sweepstakes_users where prd_id=@sid)
begin
set @status=4
end
else
begin
--Delete From [tbl_SweepStakes] where ProductID=@productID
delete from prd_sweepstakes where sid=@sid
set @status=2
end

end
else if @mode=2
begin
select @count=max(P_Count) From prd_sweepstakes where sid=@sid
set @count=@count+1
Update prd_sweepstakes set P_Count=@count where sid=@sid
set @status=0
end '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_Sweepstakes]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_Sweepstakes]
@sid bigint ,
@status int output
AS
BEGIN



if(exists(select prd_id from tbl_sweepstakes_users where prd_id=@sid))
begin
set @status=0
end
else

begin
delete from prd_sweepstakes where sid=@sid
set @status=1
end








end '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetSweepstakesDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetSweepstakesDetails]
@prd_id bigint



AS
BEGIN

select sid as prd_id,prd_name,''ProductImages/''+img_path as image_path_thumb,drawdate,
messaage as draw_message from prd_sweepstakes where sid=@prd_id



END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertProductBrand]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertProductBrand]

@brand_id bigint,
@Flag bit,
@brand_name varchar(50),

@Exists int output

AS
BEGIN
declare @no int
if(@Flag=1)
begin

if exists(select * from brands where brand_name=@brand_name)
begin
set @Exists=1
end
else
begin

insert into brands values(@brand_name)
set @Exists=0
end
end
else
begin

if(exists(select brand_name from (select * from brands where brand_id not in(@brand_id)) brand

where brand_name=ltrim(@brand_name)))
begin
set @Exists=1
end
else
begin
update brands set brand_name=@brand_name where brand_id=@brand_id
set @Exists=0
set @Exists=0
end

end
return

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetProductBrandById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetProductBrandById]
@brand_id bigint
AS
BEGIN
select brand_id,brand_name
from brands where brand_id=@brand_id
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductBrands]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetAllProductBrands]

AS
BEGIN
select top(11)* from (
select brand_id,brand_name ,replace(replace(replace(lower(brand_name),'' '',''-''),'','',''-''),''&'',''-'')+''-brand''+''-''+cast(brand_id as varchar)+''.aspx'' as url
from brands )brands_new
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_DeleteProductBrand]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:

-- =============================================
create PROCEDURE [dbo].[Sp_DeleteProductBrand]
@brand_id bigint ,
@Exists int output
AS
BEGIN


delete from brands where brand_id=@brand_id
set @Exists=1

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductsForList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Sp_GetAllProductsForList]




AS
BEGIN

select top(11)* from (
select P.prd_id ,B.brand_name,P.prd_name,P.prd_short_description,P.prd_details,P.featured,
(select top(1)(''../ProductImages/ThumbnailImage/''+image_path) from image_gallery IG where IG.prd_id=P.prd_id) as prd_image
,P.prd_quantity,P.prd_price,replace(lower(P.prd_name),'' '',''-'')+''-product''+''-''+cast(P.prd_id as varchar)+''.aspx'' as url
from products P ,Brands B where P.brand_id=B.brand_id ) prod_list


END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllBrandsForList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[Sp_GetAllBrandsForList]




AS
BEGIN

select * from brands


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductsnew]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[Sp_GetAllProductsnew]


@startRowIndex int,
@maximumRows int

AS
BEGIN




DECLARE @first_id int, @startRow int

SET @startRowIndex = (@startRowIndex - 1) * @maximumRows

IF @startRowIndex = 0
SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex
print @startRowIndex
SELECT @first_id = prd_id FROM Products ORDER BY prd_id

PRINT @first_id



SET ROWCOUNT @maximumRows



select P.prd_id ,B.brand_name,P.prd_name,substring(P.prd_short_description,0,120) as prd_short_description ,P.prd_details,P.featured,

case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as prd_image
,P.prd_quantity,P.prd_price
from products P ,Brands B where P.brand_id=B.brand_id
and P.prd_id >= @first_id ORDER BY P.prd_id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProducts]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Sp_GetAllProducts]




AS
BEGIN

select P.prd_id ,B.brand_name,P.prd_name,substring(P.prd_short_description,0,120) as prd_short_description ,P.prd_details,P.featured,

case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as prd_image
,P.prd_quantity,P.prd_price
from products P ,Brands B where P.brand_id=B.brand_id order by prd_id desc


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductsByManufactureId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Sp_GetAllProductsByManufactureId]
@Mid bigint = 0 ,

@Featured int =2

AS
BEGIN

select P.prd_id ,P.prd_name,B.brand_name,P.prd_short_description,P.prd_details,P.featured,


case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as prd_image



,P.prd_quantity,P.prd_price
from products P ,Brands B
where (P.featured=@Featured or @Featured=2) and P.brand_id=B.brand_id order by prd_id desc
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductBrandsnew]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetAllProductBrandsnew]

AS
BEGIN

select brand_id,brand_name ,replace(replace(replace(lower(brand_name),'' '',''-''),'','',''-''),''&'',''-'')+''-brand''+''-''+cast(brand_id as varchar)+''.aspx'' as url
from brands
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tonerplus].[usp_GetProductslatest]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [tonerplus].[usp_GetProductslatest]
@startRowIndex int,
@maximumRows int,
@totalRows int OUTPUT

AS

DECLARE @first_id int, @startRow int

SET @startRowIndex = (@startRowIndex - 1) * @maximumRows

IF @startRowIndex = 0
SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex

SELECT @first_id = prd_id FROM Products ORDER BY prd_id

--PRINT @first_id

SET ROWCOUNT @maximumRows

select substring(P.prd_name,0,24) as prd_name,
substring(P.prd_short_description,0,110) as prd_short_description ,

--(select top(1)(image_path) from image_gallery IG where IG.prd_id=P.prd_id) as prd_image
case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as prd_image

,P.prd_price
,replace(replace(replace(replace(replace(replace(lower(P.prd_name),'' '',''-''),''&'',''-''),''/'',''-''),'','',''-''),''*'',''''),''"'','''')+''-productdetail''+''-''+cast(P.prd_id as varchar)+''.aspx'' as url

from products P ,Brands B where P.brand_id=B.brand_id and P.prd_id >= @first_id ORDER BY P.prd_id




SET ROWCOUNT 0

-- GEt the total rows


SELECT @totalRows = COUNT(prd_id) FROM Products
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllCountByBrandId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetAllCountByBrandId]

@Brand_id bigint


AS
BEGIN
--image path changed.
--old path: ''ProductImages/ThumbnailImage/''
select count(*)
from products P ,Brands B where P.brand_id=B.brand_id and P.Brand_id=@Brand_id


END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllProductsForListing2]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[GetAllProductsForListing2]




AS
BEGIN
--path changed :
--old path :''ProductImages/ThumbnailImage/''+
select top(10) * from (
select P.prd_id ,B.brand_name,substring(P.prd_name,0,24) as prd_name,substring(P.prd_short_description,0,110) as prd_short_description ,P.prd_details,P.featured,
--(select top(1)(image_path) from image_gallery IG where IG.prd_id=P.prd_id) as prd_image
case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as prd_image
,P.prd_quantity,P.prd_price ,replace(replace(replace(replace(replace(replace(lower(P.prd_name),'' '',''-''),''&'',''-''),''/'',''-''),'','',''-''),''*'',''''),''"'','''')+''-productdetail''+''-''+cast(P.prd_id as varchar)+''.aspx'' as url


from products P ,Brands B where P.brand_id=B.brand_id )ss


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Getbrandnamebyid]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Getbrandnamebyid]

@Brand_id bigint


AS
BEGIN
select brand_id,brand_name from brands where brand_id= @Brand_id

END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductBrandsall]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetAllProductBrandsall]

AS
BEGIN

select B.brand_id,

( select top(1) case substring(image_path,0,5) when ''http'' then image_path when '''' then ''ProductImages/noimage.jpg'' else ''ProductImages/''+image_path end


from products where brand_id=B.brand_id ) as Img_path ,B.brand_name ,replace(replace(replace(lower(B.brand_name),'' '',''-''),'','',''-''),''&'',''-'')+''-brand''+''-''+cast(B.brand_id as varchar)+''.aspx'' as url
from brands B
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllProductsByBrandId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[GetAllProductsByBrandId]

@Brand_id bigint


AS
BEGIN
--image path changed.
--old path: ''ProductImages/ThumbnailImage/''
select P.prd_id ,B.brand_name,substring(P.prd_name,0,24) as prd_name,substring(P.prd_short_description,0,110) as prd_short_description ,P.prd_details,P.featured,
--(select top(1)(image_path) from image_gallery IG where IG.prd_id=P.prd_id) as prd_image

case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as prd_image
,P.prd_quantity,P.prd_price ,replace(replace(replace(replace(replace(replace(lower(P.prd_name),'' '',''-''),''&'',''-''),''/'',''-''),'','',''-''),''*'',''''),''"'','''')+''-productdetail''+''-''+cast(P.prd_id as varchar)+''.aspx'' as url
from products P ,Brands B where P.brand_id=B.brand_id and P.Brand_id=@Brand_id


END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllProductsForListing]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[GetAllProductsForListing]




AS
BEGIN
--path changed :
--old path :''ProductImages/ThumbnailImage/''+
select P.prd_id ,B.brand_name,substring(P.prd_name,0,24) as prd_name,substring(P.prd_short_description,0,110) as prd_short_description ,P.prd_details,P.featured,
--(select top(1)(image_path) from image_gallery IG where IG.prd_id=P.prd_id) as prd_image
case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as prd_image
,P.prd_quantity,P.prd_price ,replace(replace(replace(replace(replace(replace(lower(P.prd_name),'' '',''-''),''&'',''-''),''/'',''-''),'','',''-''),''*'',''''),''"'','''')+''-productdetail''+''-''+cast(P.prd_id as varchar)+''.aspx'' as url

from products P ,Brands B where P.brand_id=B.brand_id


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductBrandsforadmin]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetAllProductBrandsforadmin]

AS
BEGIN

select brand_id,brand_name ,replace(replace(replace(lower(brand_name),'' '',''-''),'','',''-''),''&'',''-'')+''-brand''+''-''+cast(brand_id as varchar)+''.aspx'' as url
from brands
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetAllProductBrandsallnew]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' -- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetAllProductBrandsallnew]

AS
BEGIN

--select top(3)* from (
select B.brand_id,
B.brand_name ,replace(replace(replace(replace(lower(B.brand_name),'' '',''-''),'','',''-''),''&'',''-''),''/'','''')+''-brand''+''-''+cast(B.brand_id as varchar)+''.aspx'' as url
from brands B order by B.brand_name --) brandsnew order by brand_name
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllProductsByBrandIdforBrands]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[GetAllProductsByBrandIdforBrands]

@Brand_id bigint


AS
BEGIN
--image path changed.
--old path: ''ProductImages/ThumbnailImage/''
--select top(3)* from (
select P.prd_id ,B.brand_name,P.part_number as prd_name ,substring(P.prd_short_description,0,115) as prd_short_description ,P.prd_details,P.featured,

case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as prd_image
,P.prd_quantity,P.prd_price ,replace(replace(replace(replace(replace(replace(lower(P.prd_name),'' '',''-''),''&'',''-''),''/'',''-''),'','',''-''),''*'',''''),''"'','''')+''-productdetail''+''-''+cast(P.prd_id as varchar)+''.aspx'' as url

from products P ,Brands B where P.brand_id=B.brand_id and P.Brand_id=@Brand_id order by P.part_number
--)ffff order by prd_name


END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_AdminproductSearch]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Sp_AdminproductSearch]
@keyword nvarchar(300)

AS
BEGIN
select P.prd_id ,P.prd_name,B.brand_name,P.prd_short_description,P.prd_details,P.featured,
case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as prd_image
,P.prd_quantity,P.prd_price
from products P,Brands B where P.brand_id=B.brand_id and (P.prd_name+'' ''+B.brand_name+'' ''+P.prd_short_description+'' ''+P.prd_details+'' ''+P.part_number+'' ''+P.part_name) like ''%''+@keyword+''%''
END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertToHitCounter]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[InsertToHitCounter]
@page_name nvarchar(50),
@url nvarchar(100),
@ip_address nvarchar(50)


AS
BEGIN
declare @hits bigint
set @hits =0
if exists(select hits from counter where ip_address=@ip_address and url=@url )
begin

update counter set hits=(select max(hits)+1 from counter where ip_address=@ip_address and url=@url )
end
else
begin

insert into counter values(@page_name,@url,@ip_address ,1,getdate())

end
return

END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetProductUrlById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetProductUrlById]

@prd_id bigint


AS
BEGIN

select replace(replace(replace(replace(lower(prd_name),'' '',''-''),''/'',''''),''*'',''''),''"'','''')+''-productdetail''+''-''+cast(prd_id as varchar)+''.aspx'' as url
from products where prd_id=@prd_id


END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetProductCount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
create PROCEDURE [dbo].[Sp_GetProductCount]




AS
BEGIN

select count(prd_id) from products


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSweepstakesDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[GetSweepstakesDetails]
@prd_id bigint
AS
BEGIN

select uid,prd_id,name,email,phone,comments,ip_address from tbl_sweepstakes_users
where prd_id=@prd_id
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_Sweepstkakes_users]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' -- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_Sweepstkakes_users]
@uid bigint
AS
BEGIN


delete from tbl_sweepstakes_users where uid=@uid

END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_Sweepstkakes users]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_Sweepstkakes users]
@uid bigint
AS
BEGIN


delete from tbl_sweepstakes_users where uid=@uid

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertSweepstakesUser_details]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[InsertSweepstakesUser_details]


@prd_id bigint,
@name nvarchar(50),
@email nvarchar(50),
@phone nvarchar(20),
@comments text,
@ip_address nvarchar(50)
AS
BEGIN

insert into tbl_sweepstakes_users values
(@prd_id,@name,@email,@phone,@comments,@ip_address)

END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetCMS_contactById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetCMS_contactById]
@contact_id bigint

AS
BEGIN

Select * from cms_contact where contact_id=@contact_id
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertUpdateContact]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertUpdateContact]
@Flag bit,
@contact_id bigint,
@contact_name nvarchar(50),
@contact_address text,
@email nvarchar(50),
@Exists int output

AS
BEGIN

if(@Flag=1)
begin
if exists(select * from cms_contact where contact_name=@contact_name)
begin
set @Exists=1
end
else
begin

insert into cms_contact values(@contact_name,@contact_address,@email)
set @Exists=0
end
end
else
begin


if(exists(select contact_name from (select * from cms_contact where contact_id not in(@contact_id)) contact

where contact_name=ltrim(@contact_name)))
begin
set @Exists=1
end
else
begin

update cms_contact set contact_name=@contact_name ,contact_address=@contact_address ,email=@email
where contact_id=@contact_id
set @Exists=0
end



end
return

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetCMS_contact]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetCMS_contact]


AS
BEGIN

Select * from cms_contact
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_ContactUs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' -- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_ContactUs]
@contact_id bigint
AS
BEGIN
delete from cms_contact where contact_id=@contact_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertInoShoppingCart]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertInoShoppingCart]
@prd_id bigint,
@prdqty int,
@cart_session_id nvarchar(50),
@cart_date datetime


AS
BEGIN
declare @qty int

if exists(select * from shoppingcart where prd_id=@prd_id and cart_session_id=@cart_session_id )
begin
set @qty=(select prd_quantity from shoppingcart where prd_id=@prd_id and cart_session_id=@cart_session_id)
update shoppingcart set prd_quantity=@qty+@prdqty where prd_id=@prd_id and cart_session_id=@cart_session_id
end
else
begin

insert into shoppingcart values(@prd_id ,@prdqty,@cart_session_id,@cart_date,null)
end
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetShoppingCart_countById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetShoppingCart_countById]
@SessionId nvarchar(50)



AS
BEGIN



select prd_id from shoppingcart SC
where cart_session_id=@SessionId --and SC.od_id is NULL


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetShoppingCartById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetShoppingCartById]
@SessionId nvarchar(50)



AS
BEGIN

--select * from shoppingcart
--image path changed :
--old path :

select SC.cart_id,SC.prd_id,SC.prd_quantity,SC.cart_date,SC.od_id,P.prd_weight,P.prd_name,P.prd_price,isnull(SC.prd_quantity,1) as qty,isnull(SC.prd_quantity,1) * P.prd_price as Total ,
case substring(P.image_path,0,5) when ''http'' then P.image_path when '''' then ''../ProductImages/ThumbnailImage/noimage.jpg'' else ''../ProductImages/ThumbnailImage/''+P.image_path end
as img_path

--(select top(1)image_path from image_gallery where prd_id=P.prd_id) as img_path
from shoppingcart SC
inner join products P on SC.prd_id=P.prd_id
where cart_session_id=@SessionId --and SC.od_id is NULL


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_ShoppingCart]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
Create PROCEDURE [dbo].[Sp_Delete_ShoppingCart]
@prd_id bigint,
@cart_session_id nvarchar(50)
AS
BEGIN


delete from shoppingcart where prd_id=@prd_id and cart_session_id=@cart_session_id

END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_UpdateShoppingCart]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_UpdateShoppingCart]
@prd_id bigint,
@qty int,
@cart_session_id nvarchar(50)


AS
BEGIN

--insert into shoppingcart values(@prd_id ,null,@cart_session_id,@cart_date,null)
update shoppingcart set prd_quantity =@qty where cart_session_id=@cart_session_id and prd_id=@prd_id

END

'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetShoppingCartDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetShoppingCartDetails]
@SessionId nvarchar(50)



AS
BEGIN

select prd_id,isnull(prd_quantity,0) as qty from shoppingcart
where cart_session_id=@SessionId


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_ShippingCalculationDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[Sp_ShippingCalculationDetails]
@SessionId nvarchar(50)

AS
BEGIN
select P.prd_id,P.prd_name,P.prd_weight,P.prd_height,P.prd_width,P.length as prd_length,SC.prd_quantity,prd_price
from products P
inner join shoppingcart SC on SC.prd_id=P.prd_id
where cart_session_id=@SessionId



END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_ShoppingCart_logout]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
Create PROCEDURE [dbo].[Sp_Delete_ShoppingCart_logout]

@cart_session_id nvarchar(50)
AS
BEGIN


delete from shoppingcart where cart_session_id=@cart_session_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertEmailForNewsletter]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[InsertEmailForNewsletter]
@SubscriberName nvarchar(50),
@email nvarchar(100),
@Exists int output
AS
BEGIN

if exists(select emailid from tbl_subscribers where emailid=@email and status=1 )
begin

set @Exists=1
end
else
begin






if exists(select emailid from tbl_subscribers where emailid=@email and status=2 )
begin
update tbl_subscribers set subscriber_name=@SubscriberName , status=1 where emailid=@email
set @Exists=0
end
else
begin

--status=1 for subscriber
--status=2 for unsubscribe
insert into tbl_subscribers values(@SubscriberName,@email,1)
set @Exists=0
end
end
return

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetNewsletterEmailsBy_Id]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[GetNewsletterEmailsBy_Id]
@Id bigint
AS
BEGIN

select id,subscriber_name, emailid,status from tbl_subscribers where id=@Id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetNewsletterEmailsBy_Status]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[GetNewsletterEmailsBy_Status]
@status int
AS
BEGIN

select id,subscriber_name, emailid,status from tbl_subscribers where status=@status

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSubscriber_Emails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' -- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[GetSubscriber_Emails]


AS
BEGIN
select id,subscriber_name, emailid,status from tbl_subscribers where status=1
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetNewsletterContents]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' create PROCEDURE [dbo].[GetNewsletterContents]




AS
BEGIN

select title,subject,contents from tbl_newsletter

END'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertNewsletter]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' -- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertNewsletter]
@title text,
@subject text,
@contents text


AS
BEGIN


if not exists(select title from tbl_newsletter)
begin
insert into tbl_newsletter values(''newtitle'',''newsubject'',''newcontents'')

end
else
begin
update tbl_newsletter set title=@title ,subject=@subject,contents=@contents


end


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_CheckUserId]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE Procedure [dbo].[Sp_CheckUserId]
(
@Out int output,
@Password varchar(20),
@user_id bigint
)
AS

BEGIN



if exists(Select user_id from admin_users where Password=@Password and user_id=@user_id)
begin
select @Out=''1'' --If Success return 1

end
else
begin
select @Out=''0'' --if Invalid UserId

end
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetUsersDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'--sp_helptext Sp_GetUsersDetails




-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetUsersDetails]




AS
BEGIN

select user_id,user_name,password,first_name,last_name,email_id,active from admin_users


END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_insertUpdateAdminUsers]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_insertUpdateAdminUsers]
@Flag bit,
@user_id bigint,
@user_name varchar(30),
@email_id nvarchar(50),
@password varchar(20),
@first_name varchar(20),
@last_name varchar(50),
@active bit,
@Exists int output


AS
BEGIN
declare @no int
if(@Flag=1)
begin


if exists(select * from admin_users where user_name=@user_name or email_id=@email_id )
begin
set @Exists=1
end
else
begin

insert into admin_users values(@user_name,@email_id,@password ,@first_name,@last_name ,@active)
set @Exists=0
end
end
else
begin
select @no=count(*) from admin_users where email_id=@email_id or user_name=@user_name

if(@no>1)
begin
set @Exists=1
end
else
begin
update admin_users set user_name=@user_name,email_id=@email_id,first_name=@first_name,last_name=@last_name ,active=@active
where user_id=@user_id
set @Exists=0
end
end

return

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_Delete_AdminUser]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' -- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_Delete_AdminUser]
@user_id bigint
AS
BEGIN
delete from admin_users where user_id=@user_id

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_ChangePassword]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_ChangePassword]
@user_id bigint,
@password varchar(20)

AS
BEGIN

update admin_users set password=@password where user_id=@user_id
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetLoginUser]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetLoginUser]

@username varchar(30),

@password varchar(20)


AS
BEGIN
select count(*) from admin_users where user_name=@username and password=@password and active=1
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetLoginUsers]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
create PROCEDURE [dbo].[Sp_GetLoginUsers]

@username varchar(30),

@password varchar(20)


AS
BEGIN
select * from admin_users where user_name=@username and password=@password and active=1
END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetOrderProductDetails]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Sp_GetOrderProductDetails]

@OrderId bigint
AS
BEGIN

select P.prd_id,S.Rate_Method,S.Rate_Amount,P.prd_name ,OI.Od_quantity,P.prd_price as UnitPrice,[dbo].[GetTax]
(
@OrderId,
(OI.Od_quantity*P.prd_price)
) as Tax,
(OI.Od_quantity*P.prd_price)+S.Rate_Amount+[dbo].[GetTax]
(
@OrderId,
(OI.Od_quantity*P.prd_price)
) as Total
from shipping S,Products P,order_items OI
where P.prd_id=S.prd_id and OI.Od_id=S.Od_id and OI.prd_id=S.prd_id and S.od_id=@OrderId and S.status=1

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetOrderListByStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
create PROCEDURE [dbo].[Sp_GetOrderListByStatus]
@Status int

AS
BEGIN
select O.Od_id,O.Od_shipping_fname+O.Od_shipping_lname as CustName,
sum(P.prd_price*OI.Od_quantity)+O.Os_shipping_cost+ [dbo].[GetTax]
(
O.Od_id,
sum(P.prd_price*OI.Od_quantity)
) as Total ,
O.Od_date,
[Status] = case
when O.Od_status =0 then ''Processing''
when O.Od_status =1 then ''Approved''
when O.Od_status =2 then ''Declined''
else
''Deleted''
end
from Customers C ,Orders O,Products P,Order_items OI,Shipping S
where C.Cust_id=O.Cust_id and O.Od_id=OI.Od_id and OI.prd_id=P.prd_id and P.prd_id=S.prd_id and S.Od_id=OI.Od_id and OI.prd_id=S.prd_id and S.status=1 and O.Od_status=@Status
group by O.Od_id,O.Od_shipping_fname,O.Od_shipping_lname,O.Od_date,O.Od_status,O.Os_shipping_cost

END '
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sp_GetOrderList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[Sp_GetOrderList]


AS
BEGIN
select O.Od_id,O.Od_shipping_fname+O.Od_shipping_lname as CustName,
sum(P.prd_price*OI.Od_quantity)+O.Os_shipping_cost+ [dbo].[GetTax]
(
O.Od_id,
sum(P.prd_price*OI.Od_quantity)
) as Total ,
O.Od_date,
[Status] = case
when O.Od_status =0 then ''Processing''
when O.Od_status =1 then ''Approved''
when O.Od_status =2 then ''Declined''
else
''Deleted''
end
from Customers C ,Orders O,Products P,Order_items OI,Shipping S
where C.Cust_id=O.Cust_id and O.Od_id=OI.Od_id and OI.prd_id=P.prd_id and P.prd_id=S.prd_id and S.Od_id=OI.Od_id and OI.prd_id=S.prd_id and S.status=1 and O.Od_status not in(4)
group by O.Od_id,O.Od_shipping_fname,O.Od_shipping_lname,O.Od_date,O.Od_status,O.Os_shipping_cost

END '
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[tonerplus].[FK_tbl_referer_tbl_referer]') AND parent_object_id = OBJECT_ID(N'[tonerplus].[tbl_referer]'))
ALTER TABLE [tonerplus].[tbl_referer] WITH CHECK ADD CONSTRAINT [FK_tbl_referer_tbl_referer] FOREIGN KEY([id])
REFERENCES [tonerplus].[tbl_referer] ([id])
GO
ALTER TABLE [tonerplus].[tbl_referer] CHECK CONSTRAINT [FK_tbl_referer_tbl_referer]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Customers_Country_master]') AND parent_object_id = OBJECT_ID(N'[dbo].[Customers]'))
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [FK_Customers_Country_master] FOREIGN KEY([Country_id])
REFERENCES [dbo].[Country_master] ([country_id])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [FK_Customers_Country_master]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Customers_state_master]') AND parent_object_id = OBJECT_ID(N'[dbo].[Customers]'))
ALTER TABLE [dbo].[Customers] WITH CHECK ADD CONSTRAINT [FK_Customers_state_master] FOREIGN KEY([State_id])
REFERENCES [dbo].[state_master] ([state_id])
GO
ALTER TABLE [dbo].[Customers] CHECK CONSTRAINT [FK_Customers_state_master]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_multiple_category_prd_category]') AND parent_object_id = OBJECT_ID(N'[dbo].[multiple_category]'))
ALTER TABLE [dbo].[multiple_category] WITH CHECK ADD CONSTRAINT [FK_multiple_category_prd_category] FOREIGN KEY([cat_id])
REFERENCES [dbo].[prd_category] ([cat_id])
GO
ALTER TABLE [dbo].[multiple_category] CHECK CONSTRAINT [FK_multiple_category_prd_category]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_multiple_category_products]') AND parent_object_id = OBJECT_ID(N'[dbo].[multiple_category]'))
ALTER TABLE [dbo].[multiple_category] WITH CHECK ADD CONSTRAINT [FK_multiple_category_products] FOREIGN KEY([prd_id])
REFERENCES [dbo].[products] ([prd_id])
GO
ALTER TABLE [dbo].[multiple_category] CHECK CONSTRAINT [FK_multiple_category_products]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_state_master_Country_master]') AND parent_object_id = OBJECT_ID(N'[dbo].[state_master]'))
ALTER TABLE [dbo].[state_master] WITH CHECK ADD CONSTRAINT [FK_state_master_Country_master] FOREIGN KEY([country_id])
REFERENCES [dbo].[Country_master] ([country_id])
GO
ALTER TABLE [dbo].[state_master] CHECK CONSTRAINT [FK_state_master_Country_master]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_products_brands]') AND parent_object_id = OBJECT_ID(N'[dbo].[products]'))
ALTER TABLE [dbo].[products] WITH CHECK ADD CONSTRAINT [FK_products_brands] FOREIGN KEY([brand_id])
REFERENCES [dbo].[brands] ([brand_id])
GO
ALTER TABLE [dbo].[products] CHECK CONSTRAINT [FK_products_brands]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Orders_Customers]') AND parent_object_id = OBJECT_ID(N'[dbo].[Orders]'))
ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([Cust_id])
REFERENCES [dbo].[Customers] ([Cust_id])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Image_gallery_products]') AND parent_object_id = OBJECT_ID(N'[dbo].[Image_gallery]'))
ALTER TABLE [dbo].[Image_gallery] WITH CHECK ADD CONSTRAINT [FK_Image_gallery_products] FOREIGN KEY([prd_id])
REFERENCES [dbo].[products] ([prd_id])
GO
ALTER TABLE [dbo].[Image_gallery] CHECK CONSTRAINT [FK_Image_gallery_products]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_shoppingcart_products]') AND parent_object_id = OBJECT_ID(N'[dbo].[shoppingcart]'))
ALTER TABLE [dbo].[shoppingcart] WITH CHECK ADD CONSTRAINT [FK_shoppingcart_products] FOREIGN KEY([prd_id])
REFERENCES [dbo].[products] ([prd_id])
GO
ALTER TABLE [dbo].[shoppingcart] CHECK CONSTRAINT [FK_shoppingcart_products]