1#@!#!123s

��JFIF�� 1#@!#!123s
: C: / Inetpub / vhosts / neyoclubricant.com / httpdocs / Admin / Database /
Filename : Procedure.sql
back
/*
	Date : 29-08-2020
*/

use db_neyoc_oil


---------------------------------------
------------ Category -----------------
---------------------------------------
alter proc ins_product_sp
(
	@type_id int,
	@name nvarchar(200),
	@code varchar(200),
	@photo nvarchar(max),
	@desc nvarchar(max),
	@create_by int
)
as
begin
	insert into tbl_product
	values
	(
		@type_id,
		@name,
		@code,
		@photo,
		@desc,
		1,
		1,
		@create_by,
		dbo.get_date(),
		null,
		null,
		null,
		null
	)

	select 'ok'
end


---------------------------------------
------------ Category -----------------
---------------------------------------
alter proc upd_product_sp
(
	@pro_id int,
	@type_id int,
	@name nvarchar(200),
	@code varchar(200),
	@photo nvarchar(max),
	@desc nvarchar(max),
	@modify_by int
)
as
begin
	update tbl_product
	set	
		type_id=@type_id,
		[name]=@name,
		code=@code,
		photo=@photo,
		[desc]=@desc,
		modify_by=@modify_by,
		modify_date=dbo.get_date()
	where
		pro_id=@pro_id


	select 'ok'
end



---------------------------------------
------------ Category -----------------
---------------------------------------
create proc dlt_product_sp
(
	@pro_id int,
	@delete_by int
)
as
begin
	update tbl_product
	set
		status=0,
		delete_by=@delete_by,
		delete_date=dbo.get_date()
	where
		pro_id=@pro_id

	select 'ok'
end




---------------------------------------
------------ Category -----------------
---------------------------------------
alter proc sel_product_sp
(
	@pro_id int
)
as
begin
	select
		type_id,
		name,
		code,
		photo,
		[desc],
		active,
		photo
	from
		tbl_product
	where
		pro_id=@pro_id

		
end



---------------------------------------
------------ Category -----------------
---------------------------------------
alter proc dis_product_sp
as
begin
	select
		pro_id,
		name,
		code,
		photo,
		[desc],
		active
	from
		tbl_product
	where
		status=1

end


---------------------------------------
------------ Category -----------------
---------------------------------------
alter proc dis_product_by_type_sp
(
	@type_id int
)
as
begin
	select
		pro_id,
		name,
		code,
		photo,
		[desc],
		active
	from
		tbl_product
	where
		status=1
		and type_id=@type_id

end

---------------------------------------
------------ Category -----------------
---------------------------------------
create proc dis_battery_detail_for_website_sp
(
	@pro_id int
)
as
begin
	select
		name,
		photo,
		[desc],
		photo
	from
		tbl_battery
	where
		pro_id=@pro_id

	select
		top 5
		p.pro_id,
		p.name,
		p.photo,
		p.[desc],
		p.photo
	from
		tbl_battery as p
	where
		status=1
end

----------------------------------------------
-------------- Insert Admin-------------------
----------------------------------------------
create proc ins_admin_sp
(
	@name nvarchar(100),
	@email nvarchar(100),
	@mobile_no nvarchar(20),
	@password nvarchar(500),
	@create_by int
)
as
begin
	if Not Exists(select * from tbl_admin where (email=@email or mobile_no=@mobile_no) and status=1)
	begin
		insert into 
			tbl_admin
		values(
			@name,
			@email,
			@mobile_no,
			@password,
			1,
			'',
			1,
			@create_by,
			dbo.get_date(),
			null,
			null,
			null,
			null
		)
		select 'ok'
	end
end



----------------------------------------------
-------------- Update Admin-------------------
----------------------------------------------
create proc upd_admin_sp
(
	@admin_id int,
	@name nvarchar(100),
	@email nvarchar(100),
	@mobile_no nvarchar(20),
	@password nvarchar(500),
	@modify_by int
)
as
begin
	if Not Exists(select * from tbl_admin where (email=@email or mobile_no=@mobile_no) and admin_id!=@admin_id and status=1)
	begin
		update
			tbl_admin
		set
			name=@name,
			email=@email,
			mobile_no=@mobile_no,
			password=@password,
			modify_by=@modify_by,
			modify_date=dbo.get_date()
		where
			admin_id=@admin_id
		select 'ok'
	end
end


----------------------------------------------
-------------- Delete Admin--------------------
----------------------------------------------
create proc dlt_admin_sp
(
	@admin_id int,
	@delete_by int
)
as
begin
	update
		tbl_admin
	set 
		status=0,
		delete_by=@delete_by,
		delete_date=dbo.get_date()
	where
		admin_id=@admin_id
	select 'ok'
end


----------------------------------------------
-------------- Select Admin--------------------
----------------------------------------------
create proc sel_admin_sp
(
	@admin_id int
)
as
begin
	select
		admin_id,
		name,
		email,
		mobile_no,
		password,
		temp_status,
		profile_pic
	from
		tbl_admin
	where
		admin_id=@admin_id
end

----------------------------------------------
------------- Display Admin-------------------
----------------------------------------------
create proc dis_admin_sp
as
begin
	select
		admin_id,
		a.name,
		email,
		mobile_no,
		password,
		case profile_pic
			when '' then 'user.png'
			else profile_pic end as profile_pic
	from
		tbl_admin as a
	where
		a.status=1
end


----------------------------------------------
------------- Admin Login --------------------
----------------------------------------------
create proc admin_login_sp
(
	@mobile_no nvarchar(50),
	@password nvarchar(500)
)
as
begin
	select
		admin_id,
		name,
		email,
		mobile_no,
		[password],
		temp_status,
		case profile_pic
			when '' then ''
			else profile_pic end as profile_pic
	from
		tbl_admin
	where
		status=1
		and (mobile_no=@mobile_no or email=@mobile_no)
		and password=@password
		
	--select 
	--	r.rights_id,
	--	r.page_title,
	--	r.allow, 
	--	r.[insert],
	--	r.[update],
	--	r.[delete],
	--	r.[display]
	--from 
	--	tbl_rights as r
	--	join tbl_admin as a
	--on
	--	r.role_id=a.role_id
	--where
	--	mobile_no=@mobile_no
	--	and password=@password
	--	and a.[status]=1
end


---------------------------------------------------
-------------- Change Admin Password --------------
---------------------------------------------------
create proc change_admin_password_sp
(
	@admin_id int,
	@old_password varchar(50),
	@new_password varchar(50)
)
as
begin
	if(exists(select * from tbl_admin where admin_id=@admin_id and password=@old_password and status=1))
	begin
		update tbl_admin
		set
			password=@new_password
		where
			admin_id=@admin_id
			and password=@old_password 
			and status=1
		select 'ok'
	end
end



---------------------------------------
------------ Category -----------------
---------------------------------------
alter proc ins_oil_type_sp
(
	@name nvarchar(200),
	@photo nvarchar(max),
	@create_by int
)
as
begin
	insert into tbl_oil_type
	values
	(
		@name,
		@photo,
		1,
		1,
		@create_by,
		dbo.get_date(),
		null,
		null,
		null,
		null
	)

	select 'ok'
end



---------------------------------------
------------ Category -----------------
---------------------------------------
create proc upd_oil_type_sp
(
	@type_id int,
	@name nvarchar(200),
	@photo nvarchar(max),
	@modify_by int
)
as
begin
	update tbl_oil_type
	set
		name=@name,
		photo=@photo,
		modify_by=@modify_by,
		modify_date=dbo.get_date()
	where
		type_id=@type_id

	select 'ok'
end


---------------------------------------
------------ Category -----------------
---------------------------------------
create proc dlt_oil_type_sp
(
	@type_id int,
	@delete_by int
)
as
begin
	update tbl_oil_type
	set
		status=1,
		delete_by=@delete_by,
		delete_date=dbo.get_date()
	where
		type_id=@type_id

	select 'ok'
end


---------------------------------------
------------ Category -----------------
---------------------------------------
create proc sel_oil_type_sp
(
	@type_id int
)
as
begin
	select
		type_id,
		name,
		photo,
		active
	from
		tbl_oil_type
	where
		type_id=@type_id

end


---------------------------------------
------------ Category -----------------
---------------------------------------
create proc dis_oil_type_sp
as
begin
	select
		type_id,
		name,
		photo,
		active
	from
		tbl_oil_type
	where
		status=1

end

---------------------------------------
------------ Category -----------------
---------------------------------------
alter proc dis_web_dash_sp
as
begin
	select 
		type_id,
		name,
		photo
	from 
		tbl_oil_type
	where
		status=1
		and active=1

	select
		[image]
	from
		tbl_slider
	where
		status=1
end

---------------------------------------
------------ Category -----------------
---------------------------------------
create proc ins_oil_inq_sp
(
	@pro_id int,
	@name nvarchar(500),
	@mobile_no varchar(20)
)
as
begin
	insert into tbl_oil_inq
	values
	(
		@pro_id,
		@name,
		@mobile_no,
		1,
		null,
		dbo.get_date(),
		null,
		null,
		null,
		null
	)

	select 'ok'
end


---------------------------------------
------------ Category -----------------
---------------------------------------
alter proc dis_oil_inq_sp
as
begin
	select
		p.pro_id,
		p.name as pro_name,
		p.code,
		i.name,
		i.mobile_no,
		format(i.create_date,'dd MMM, yyyy') as create_date
	from
		tbl_oil_inq as i
		join tbl_product as p
	on
		i.pro_id=p.pro_id
	where
		i.status=1
	order by
		i.inq_id desc
end


----------------------------------------------
-------------- Insert Slidet -----------------
----------------------------------------------
create proc ins_slider_sp
(
	@image nvarchar(100),
	@create_by int
)
as
begin
	 insert into tbl_slider
	 values(
		@image,
		1,
		@create_by,
		dbo.get_date(),
		null,
		null,
		null,
		null
	 )
	 select 'Ok'
end

----------------------------------------------
-------------- Delete Slider----------------
----------------------------------------------
create proc dlt_slider_sp
(
	@s_id int,
	@delete_by int
)
as
begin
	update
		tbl_slider
	set 
		status=0,
		delete_by=@delete_by,
		delete_date=dbo.get_date()
	where
		s_id=@s_id
		
	select [image] from tbl_slider where s_id=@s_id
end

----------------------------------------------
-------------- Display Slider ---------------
----------------------------------------------
create proc dis_slider_sp
as
begin
	select
		s_id,
		image
	from
		tbl_slider
	where
		status=1
end



---------------------------------------
------------ Category -----------------
---------------------------------------
create proc ins_inq_sp
(
	@name nvarchar(max),
	@email varchar(1000),
	@mobile varchar(max),
	@msg nvarchar(max)
)
as
begin
	insert into tbl_inq
	values
	(
		@name,
		@email,
		@mobile,
		@msg,
		1,
		null,
		dbo.get_date(),
		null,
		null,
		null,
		null
	)

	select 'ok'
end


---------------------------------------
------------ Category -----------------
---------------------------------------
alter proc dis_inq_sp
as
begin
	select
		id,
		[name],
		email,
		mobile,
		msg,
		format(create_date,'dd MMM, yyyy') as create_date
	from
		tbl_inq
	where
		status=1
	order by
		id desc
end


---------------------------------------
------------ Category -----------------
---------------------------------------
create proc ins_battery_sp
(
	@name nvarchar(200),
	@photo nvarchar(max),
	@desc nvarchar(max),
	@create_by int
)
as
begin
	insert into tbl_battery
	values
	(
		@name,
		@photo,
		@desc,
		1,
		1,
		@create_by,
		dbo.get_date(),
		null,
		null,
		null,
		null
	)

	select 'ok'
end


---------------------------------------
------------ Category -----------------
---------------------------------------
alter proc upd_battery_sp
(
	@pro_id int,
	@name nvarchar(200),
	@photo nvarchar(max),
	@desc nvarchar(max),
	@modify_by int
)
as
begin
	update tbl_battery
	set	
		[name]=@name,
		photo=@photo,
		[desc]=@desc,
		modify_by=@modify_by,
		modify_date=dbo.get_date()
	where
		pro_id=@pro_id


	select 'ok'
end



---------------------------------------
------------ Category -----------------
---------------------------------------
create proc dlt_battery_sp
(
	@pro_id int,
	@delete_by int
)
as
begin
	update tbl_battery
	set
		status=0,
		delete_by=@delete_by,
		delete_date=dbo.get_date()
	where
		pro_id=@pro_id

	select 'ok'
end




---------------------------------------
------------ Category -----------------
---------------------------------------
alter proc sel_battery_sp
(
	@pro_id int
)
as
begin
	select
		name,
		photo,
		[desc],
		active,
		photo
	from
		tbl_battery
	where
		pro_id=@pro_id

		
end



---------------------------------------
------------ Category -----------------
---------------------------------------
create proc dis_battery_sp
as
begin
	select
		pro_id,
		name,
		photo,
		[desc],
		active
	from
		tbl_battery
	where
		status=1

end