{" "} Networking Assignment Coding Sample Assignment
Complete Script For Data Base Creating
{`
USE [master]
GO
/****** Object: Database [savorfood] Script Date: 14/11/2018 2:21:08 PM ******/
CREATE DATABASE [savorfood]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'savorfood', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\savorfood.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'savorfood_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\savorfood_log.ldf' , SIZE = 6272KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [savorfood] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [savorfood].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [savorfood] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [savorfood] SET ANSI_NULLS OFF
GO
ALTER DATABASE [savorfood] SET ANSI_PADDING OFF
GO
ALTER DATABASE [savorfood] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [savorfood] SET ARITHABORT OFF
GO
ALTER DATABASE [savorfood] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [savorfood] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [savorfood] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [savorfood] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [savorfood] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [savorfood] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [savorfood] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [savorfood] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [savorfood] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [savorfood] SET DISABLE_BROKER
GO
ALTER DATABASE [savorfood] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [savorfood] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [savorfood] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [savorfood] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [savorfood] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [savorfood] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [savorfood] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [savorfood] SET RECOVERY FULL
GO
ALTER DATABASE [savorfood] SET MULTI_USER
GO
ALTER DATABASE [savorfood] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [savorfood] SET DB_CHAINING OFF
GO
ALTER DATABASE [savorfood] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [savorfood] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
ALTER DATABASE [savorfood] SET DELAYED_DURABILITY = DISABLED
GO
EXEC sys.sp_db_vardecimal_storage_format N'savorfood', N'ON'
GO
USE [savorfood]
GO
/****** Object: Table [dbo].[BridgeTableINDish] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BridgeTableINDish](
[DishID] [int] NOT NULL,
[IngradentID] [int] NOT NULL,
[Quntity] [int] NULL,
[potsizeid] [int] NOT NULL,
CONSTRAINT [PK_BridgeTableINDish] PRIMARY KEY CLUSTERED
(
[DishID] ASC,
[IngradentID] ASC,
[potsizeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DishsList] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DishsList](
[DishID] [int] IDENTITY(1,1) NOT NULL,
[DishName] [varchar](80) NULL,
[DishImage] [nchar](50) NULL,
CONSTRAINT [PK_Dishs] PRIMARY KEY CLUSTERED
(
[DishID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Expenses] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Expenses](
[ExpensesID] [int] IDENTITY(1,1) NOT NULL,
[ExpensesName] [varchar](50) NULL,
[ExpensesDescription] [nchar](50) NULL,
[ExpensesDate] [datetime] NULL,
[ExpensesType] [varchar](10) NULL,
CONSTRAINT [PK_ExpensesBeryani] PRIMARY KEY CLUSTERED
(
[ExpensesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[IngradientsList] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IngradientsList](
[IngradientID] [int] IDENTITY(1,1) NOT NULL,
[IngradientName] [varchar](100) NULL,
[IngradientPrice] [int] NULL,
[QuntityInStock] [int] NULL,
CONSTRAINT [PK_Ingradents] PRIMARY KEY CLUSTERED
(
[IngradientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Invoice] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Invoice](
[InvoiceId] [int] IDENTITY(1,1) NOT NULL,
[DishName] [nchar](100) NULL,
[PlatePrice] [int] NULL,
[NumberOfPlate] [int] NULL,
[TotalPrice] [int] NULL,
[InvoiceDate] [nvarchar](20) NULL,
CONSTRAINT [PK_InvoiceBeryani] PRIMARY KEY CLUSTERED
(
[InvoiceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[pot] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[pot](
[potId] [int] IDENTITY(1,1) NOT NULL,
[Capacity] [int] NULL,
[DishID] [int] NULL,
[NumberOfDeigh] [int] NULL,
[NumberOfSinglePlate] [int] NULL,
[CostOfpot] [int] NULL,
[CostOfSinglePlate] [int] NULL,
[potDate] [nvarchar](10) NULL,
[CostOfdoublePlate1] [int] NULL,
[NumberOfdoublePlates] [int] NULL,
[TotalIngredient] [int] NULL,
[potStatus] [int] NULL,
CONSTRAINT [PK_Daigh] PRIMARY KEY CLUSTERED
(
[potId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[potsize] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[potsize](
[potsizeid] [int] IDENTITY(1,1) NOT NULL,
[potsize] [nchar](5) NULL,
[SinglePlate] [int] NULL,
[DoublePalte] [int] NULL,
CONSTRAINT [PK_DeighSize1] PRIMARY KEY CLUSTERED
(
[potsizeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Stock] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Stock](
[IngradentID] [int] NULL,
[Quntity] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Stores] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Stores](
[StoreID] [int] IDENTITY(1,1) NOT NULL,
[Survey_untyped] [xml] NULL,
[Survey_typed] [xml] NULL,
CONSTRAINT [PK_Stores] PRIMARY KEY CLUSTERED
(
[StoreID] 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]
GO
/****** Object: Table [dbo].[tblEmployeeAudit] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblEmployeeAudit](
[Id] [int] IDENTITY(1,1) NOT NULL,
[AuditData] [nvarchar](1000) NULL,
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]
GO
/****** Object: Table [dbo].[Users1_login_table] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users1_login_table](
[U_id] [int] IDENTITY(1,1) NOT NULL,
[Username] [nvarchar](100) NOT NULL,
[Password] [nvarchar](100) NOT NULL,
[Email] [nvarchar](100) NULL,
[Utype] [nvarchar](10) NOT NULL,
CONSTRAINT [PK_Users1_login_table] PRIMARY KEY CLUSTERED
(
[U_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [Unique_username] UNIQUE NONCLUSTERED
(
[Email] ASC,
[Username] ASC,
[Password] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ__Users1_l__536C85E42610A626] UNIQUE NONCLUSTERED
(
[Username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: View [dbo].[gatpot] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [dbo].[gatpot]
as
Select Ing.IngradientName,tbdis.Quntity,dgsi.SinglePlate,dgsi.DoublePalte,Ing.IngradientPrice,Ing.IngradientID
FROM DishsList as dis
inner JOIN dbo.BridgeTableINDish as tbdis ON dis.DishID = tbdis.DishID
inner JOIN IngradientsList as Ing ON tbdis.IngradentID =Ing.IngradientID
inner JOIN dbo.potsize as dgsi ON tbdis.potsizeid = dgsi.potsizeid
GO
/****** Object: View [dbo].[gatpotbydishid] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [dbo].[gatpotbydishid]
as
Select Dg.potId,Dg.Capacity,Dg.potDate,Dg.NumberOfSinglePlate,Dg.NumberOfdoublePlates,
Dg.CostOfSinglePlate,Dg.CostOfdoublePlate1, dis.DishName,Dg.CostOfpot,dis.DishImage
FROM pot as Dg
inner JOIN DishsList as dis ON Dg.DishID =dis.DishID
where
(Dg.potStatus =1)
GO
ALTER TABLE [dbo].[BridgeTableINDish] WITH CHECK ADD CONSTRAINT [FK_BridgeTableINDish_DeighSize11] FOREIGN KEY([potsizeid])
REFERENCES [dbo].[potsize] ([potsizeid])
GO
ALTER TABLE [dbo].[BridgeTableINDish] CHECK CONSTRAINT [FK_BridgeTableINDish_DeighSize11]
GO
ALTER TABLE [dbo].[BridgeTableINDish] WITH CHECK ADD CONSTRAINT [FK_BridgeTableINDish_DishsList1] FOREIGN KEY([DishID])
REFERENCES [dbo].[DishsList] ([DishID])
GO
ALTER TABLE [dbo].[BridgeTableINDish] CHECK CONSTRAINT [FK_BridgeTableINDish_DishsList1]
GO
ALTER TABLE [dbo].[BridgeTableINDish] WITH CHECK ADD CONSTRAINT [FK_BridgeTableINDish_IngradientsList1] FOREIGN KEY([potsizeid])
REFERENCES [dbo].[IngradientsList] ([IngradientID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[BridgeTableINDish] CHECK CONSTRAINT [FK_BridgeTableINDish_IngradientsList1]
GO
ALTER TABLE [dbo].[pot] WITH CHECK ADD CONSTRAINT [FK_Daigh_DishsList] FOREIGN KEY([DishID])
REFERENCES [dbo].[DishsList] ([DishID])
GO
ALTER TABLE [dbo].[pot] CHECK CONSTRAINT [FK_Daigh_DishsList]
GO
ALTER TABLE [dbo].[Stock] WITH CHECK ADD CONSTRAINT [FK_Stock_IngradentsList] FOREIGN KEY([IngradentID])
REFERENCES [dbo].[IngradientsList] ([IngradientID])
GO
ALTER TABLE [dbo].[Stock] CHECK CONSTRAINT [FK_Stock_IngradentsList]
GO
/****** Object: StoredProcedure [dbo].[InvoiceAdddaig] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvoiceAdddaig]
@Action VARCHAR(10)
,@Quntity INT = NULL
,@IngradientID INT = NULL
,@DeighCapacity INT = NULL
AS
BEGIN
IF (@Action='update')
Begin
declare @CurrentQty AS INT;
SELECT @CurrentQty = QuntityInStock
FROM IngradientsList
WHERE IngradientID =@IngradientID
UPDATE IngradientsList
SET QuntityInStock = (@CurrentQty - @Quntity)
WHERE IngradientID = @IngradientID
END
end
GO
/****** Object: StoredProcedure [dbo].[Sp_Dish] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Sp_Dish]
@Action VARCHAR(10)
,@DishID INT = NULL
,@DishName VARCHAR(50) = NULL
,@DishImage nvarchar(50)= null
AS
BEGIN
SET NOCOUNT ON;
--SELECT
IF @Action = 'SELECT'
begin
SELECT [DishID]
,[DishName],DishImage
FROM [dbo].[DishsList]
end
--INSERT
IF @Action = 'INSERT'
BEGIN
INSERT INTO DishsList(DishName,DishImage)
VALUES (@DishName,@DishImage)
END
--UPDATE
IF @Action = 'UPDATE'
BEGIN
UPDATE DishsList
SET DishName = @DishName
WHERE DishID = @DishID
END
IF @Action = 'Count'
BEGIN
SELECT COUNT (DISTINCT[DishID])as TotalDish
FROM [dbo].[DishsList]
END
--DELETE
IF @Action = 'DELETE'
BEGIN
DELETE FROM DishsList
WHERE DishID = @DishID
END
IF @Action = 'Search'
BEGIN
select DishID, DishName,DishImage from DishsList
where DishName like @DishName +'%'
end
END
GO
/****** Object: StoredProcedure [dbo].[Sp_Ingradient] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Sp_Ingradient]
@Action VARCHAR(10)
,@IngradientID INT = NULL
,@IngradientName VARCHAR(100) = NULL
,@IngradientPrice INT = NULL
,@QuntityInStock nvarchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
--SELECT
IF @Action = 'SELECT'
begin
SELECT [IngradientID]
,[IngradientName],IngradientPrice,QuntityInStock
FROM [dbo].[IngradientsList]
end
IF @Action = 'SELECT2'
begin
SELECT [IngradientID]
,[IngradientName]
FROM [dbo].[IngradientsList]
end
--INSERT
IF @Action = 'INSERT'
BEGIN
INSERT INTO IngradientsList(IngradientName,IngradientPrice,QuntityInStock)
VALUES (@IngradientName,@IngradientPrice,@QuntityInStock)
END
--UPDATE
IF @Action = 'UPDATE'
BEGIN
UPDATE IngradientsList
SET
IngradientName = @IngradientName
,IngradientPrice=@IngradientPrice
,QuntityInStock=@QuntityInStock
WHERE IngradientID = @IngradientID
END
IF @Action = 'Count'
BEGIN
SELECT COUNT (DISTINCT[IngradientID])as TotalIngradient
FROM IngradientsList
END
--DELETE
IF @Action = 'DELETE'
BEGIN
DELETE FROM IngradientsList
WHERE IngradientID = @IngradientID
END
IF @Action = 'Search'
BEGIN
select IngradientID, IngradientName,IngradientPrice,QuntityInStock from IngradientsList
where IngradientName like @IngradientName +'%'
end
IF @Action = 'Add'
BEGIN
declare @CurrentQty AS INT;
SELECT @CurrentQty = QuntityInStock
FROM IngradientsList
WHERE IngradientID =@IngradientID
UPDATE IngradientsList
SET QuntityInStock = (@CurrentQty + @QuntityInStock)
WHERE IngradientID = @IngradientID
end
END
GO
/****** Object: StoredProcedure [dbo].[Sp_InvoiceSelect] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Sp_InvoiceSelect]
@Action VARCHAR(10)
,@InvoiceId INT = NULL
,@DishName NCHAR(100) = NULL
,@PlatePrice INT = NULL
,@TotalPrice INT = NULL
,@InvoiceDate date =null
,@potId INT = NULL
,@fromTotal INT= null,
@toTotal Int = null
,@fromDate nvarchar (50) = null,
@toDate nvarchar (50) = null
,@fromPrice nvarchar (50) = null,
@toPrice nvarchar (50) = null
,@fromQuntity nvarchar (50) = null
,@toQuntity nvarchar (50) = null
AS
BEGIN
SET NOCOUNT ON;
IF (@Action='All')
begin
Select InvoiceId, DishName,PlatePrice,NumberOfPlate,TotalPrice,InvoiceDate
FROM Invoice
order by InvoiceDate DESC
end
if (@Action='Ind')
begin
Select InvoiceId,DishName,PlatePrice,NumberOfPlate,TotalPrice,InvoiceDate
FROM Invoice
where
(DishName = @DishName OR @DishName IS NULL)AND
(PlatePrice between @fromPrice and @toPrice OR (@fromPrice IS NULL and @toPrice IS NULL)) And
(NumberOfPlate between @fromQuntity and @toQuntity OR (@fromQuntity IS NULL and @toQuntity IS NULL)) And
(TotalPrice between @fromTotal and @toTotal OR (@fromTotal IS NULL and @toTotal IS NULL)) And
(InvoiceDate between @fromDate and @toDate OR (@fromDate IS NULL and @toDate IS NULL))
ORDER BY InvoiceDate DESC
end
end
GO
/****** Object: StoredProcedure [dbo].[Sp_pot] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[Sp_pot]
@Action VARCHAR(10)
,@potId INT = NULL
,@potCapacity NCHAR(50) = NULL
,@DishID INT = NULL
,@DishName NCHAR(50) = NULL,
@Quntity int = NULL
,@IngradientID int = null
,@potsizeid int = null
AS
BEGIN
SET NOCOUNT ON;
IF (@Action='All')
begin
Select Dg.potId,Dg.Capacity,Dg.potDate,Dg.NumberOfSinglePlate,Dg.NumberOfdoublePlates,
Dg.CostOfSinglePlate,Dg.CostOfdoublePlate1, dis.DishName,Dg.CostOfpot,dis.DishImage
FROM pot as Dg
inner JOIN DishsList as dis ON Dg.DishID =dis.DishID
where
(Dg.potStatus =1)
order by Dg.potDate
end
IF (@Action='bydishname')
begin
Select Ing.IngradientName,tbdis.Quntity,dgsi.SinglePlate,dgsi.DoublePalte,Ing.IngradientPrice,Ing.IngradientID
FROM DishsList as dis
inner JOIN dbo.BridgeTableINDish as tbdis ON dis.DishID = tbdis.DishID
inner JOIN IngradientsList as Ing ON tbdis.IngradentID =Ing.IngradientID
inner JOIN dbo.potsize as dgsi ON tbdis.potsizeid = dgsi.potsizeid
where
(dis.DishID = @DishID )AND (dgsi.potsizeid =@potsizeid)
order by Ing.IngradientName
end
IF (@Action='Update')
begin
UPDATE BridgeTableINDish SET
Quntity=@Quntity
WHERE (IngradentID = @IngradientID and DishID = @DishID )and (potsizeid =@potsizeid)
end
IF @Action = 'INSERT'
BEGIN
Insert into BridgeTableINDish values(@DishID,@IngradientID,@Quntity,@potsizeid)
END
IF @Action = 'Count'
BEGIN
SELECT COUNT (DISTINCT [potId])as TotalInpot
FROM pot where potStatus =1
END
end
GO
/****** Object: StoredProcedure [dbo].[spAuthenticateUserBeryani] Script Date: 14/11/2018 2:21:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create Procedure [dbo].[spAuthenticateUserBeryani] @Password nvarchar(50),@Username nvarchar(50)
AS
BEGIN
SELECT U_id, Username,Utype
FROM Users1_login_table
WHERE (Username =@Username AND Password =@Password )
End
GO
USE [master]
GO
ALTER DATABASE [savorfood] SET READ_WRITE
GO
`}
