{" "} 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
`}