Securing Higher Grades Costing Your Pocket? Book Your Course help Tutor at The Lowest Price Now!

Get Instant Course Help

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
Course Help Features
Course Help Services
QR Code Course Help
elearningfeeds