This article is the continuation of the previous article, Food Court Project Database and Stored Procedure Before continuing this article, I strongly recommend reading the previous article because I started this article with how to create a database, table, and stored procedure, which is the starting point of this article located in the previous article.
Stock Data Table:
The purpose of the Stock Data table is to maintain the stock available for day-to-day business, this table helps to prevent the shortage of food items. The main purpose of this table is to maintain the total quantity and price for each stock item.
Create Table StockData(
stockid int primary key identity(1,1),
fooditemtypeno int references fooditemtypemaster(fooditemtypeno),
fooditemno int references fooditems(fooditemno),
qty int,
price money
)
Correction: stockid column data type is int, not nvarchar(10). Please noted this change
StoredProcedure: Insert Stock Data and Update Fooditem
In this stored procedure, we update the stock information into the stock data table and also, parallelly, update another table, i.e., the fooditem table, because whenever we update the number of particular food items first, it will be updated in the stock data table, and parallelly, we check the food item to see if there is any stock left or not; if not, we update the new quantity directly, or else we will add the left-over quantity with the new quantity, as you can see in the below-stored procedure script code.
USE [FoodCourtDB]
GO
Create procedure [dbo].[proc_updateStock_and_FoodItems](
@fitemTypeNo varchar(20),
@fitemNo varchar(10),
@qty int,
@price money)
as begin
Insert into StockData(fooditemtypeno, fooditemno, qty, price) values(@fitemTypeNo, @fitemNo, @qty, @price)
declare totalqty int;
set totalqty = select foodquantity from fooditems where fooditemno = @fitemNo
update fooditems set fquantity=totalqty + @qty where fitemno=@fitemNo
end
BillTransaction Table
The purpose of the Bill Transaction table is to store day-to-day transaction details, which means when users come to the food court if they order any food items quantity and total price for the order will stored in this bill transaction table directly. In simple words can say that order details will stored in this table.
Create Table BillTransaction(
BID int primary key identity(1,1),
BMID int references BillMaster(BMID),
fitemno int references fooditems(fooditemno),
qty int,
amount money
)
Correction: BID, BMID and fitemno column data type is int, not nvarchar(10). Please noted this change
StoredProcedure: Insert Bill Transaction and BillMaster
In the billing process, the proc_BillTransaction stored procedure manages the insertion of billing transactions into the BillTransaction table while dynamically updating the BillMaster table.
USE [FoodCourtDB]
GO
Create procedure [dbo].[proc_BillTransaction](
@bmid int,
@fitemno int,
@qty int,
@amount money,
@trnno int)
as begin
if(@trnno=1)
Insert into BillMaster(BMID) values(@bmid)
Insert into BillTransaction values(@bid,@bmid,@fitemno,@qty,@amount)
end
Bill Master Table:
This table is used to store data for the overall bill amount per order transaction based on current data and employee ID.
Create Table BillMaster(
BMID int primary key identity(1,1),
totalSales int,
totalamt money,
date date,
empid int references employee(empid)
)
Correction: BMID and empid column data type is int, not varchar(10) and nvarchar(50). Please noted this change
StoredProcedure: Update Bill Master Table
proc_GenerateBillMaster is crucial for updating the BillMaster table, capturing total sales, total amount, date, and employee ID at the time of bill generation.
USE [FoodCourtDB]
GO
Create procedure [dbo].[proc_GenerateBillMaster](@bmid varchar(10),
@totalSales int,
@totalamount money,
@date date,
@empid nvarchar(50))
as begin
update BillMaster set totalSales=@totalSales, totalamt=@totalamount, date=@date, empid=@empid where BMID=@bmid
end
Download Source Code
To get more information regarding blood donar project source code like Business Object, Database & Stored Procedure, Database Logic and User Interface.
0 Comments