Thumb

Part-15: Inventory and POS batch tracking and control table create in SQL Server 2012

Part-1: Point of Sale(POS) Inventory Super Shop Management System using ASP.NET MVC Part-2: Point of Sale(POS) Setup template to your project | Super Shop Management System|ASP.NET MVC Part-3: Point of Sale(POS) Database & table analysis | Super Shop Management System|ASP.NET MVC Part-4: Point of Sale(POS) Database Create in SQL Server | Super Shop Management System|ASP.NET MVC Part-5: Point of Sale(POS) Login using AJAX ASP.NET MVC | JQUERY Part-6: Point of Sale(POS) Login and Authorization with Session variables in ASP.NET | JQUERY | AJAX Part-7: Point of Sale(POS) Convert Password to MD5 Hash string in ASP.NET | Encrypt password in ASP.NET MVC C# Part-8: Point of Sale(POS) Role based authentication and authorization in ASP.NET MVC|Redirect to not found page if not authorized|C# Part-9: Point of Sale(POS) Create user & Account management UI UX in ASP.NET MVC Part-10: Point of Sale(POS) User Creation & user registration using ASP.NET MVC | Jquery | Ajax Part-11: Point of Sale(POS) Get user list using ASP.NET MVC | Jquery | Ajax Part-12: Point of Sale(POS) Update user using ASP.NET MVC | Jquery | Ajax Part-13: Inventory and POS Login logout session using ASP.NET | Supershop management system Part-14: Inventory Category CRUD Create,Retrieve,Update,View | POS Category CRUD using ASP.NET JQuery AJAX Part-15: Inventory and POS batch tracking and control table create in SQL Server 2012 Part-16: Inventory Product CRUD List | Point of sale Products Crud using asp.net MVC Part-17: Inventory and POS Batch CRUD using asp.net MVC JQUERY AJAX | CSharp Part-18: Inventory management and stock control using asp.net mvc | Jquery AngularJs Part-19: Inventory & POS Stock edit and validation using asp.net AngularJS

10/17/2020 12:00:00 AM

Download Project

Post your any code related problem to www.abctutorial.com

Follow the previous video and articles to complete the POS tutorials

Step-1

In our previous table structure we never use Product sales price and Produce Purchase price. Also a very important part we missed which is batch. Batch is very important because like example: we purchase few mobile phone in January by 100$ per piece but in next purchase when we went to market then price has been increase it to 120$ that mean when we sell then we will consider sells price based on 100$ then when this product will be sold-out then we will consider sells price based on 120$. That’s  why we will make some change in our table and create new Batch table. Also we changed ProductQuantity table to ProductStock.

  • Please create Batch table by executing below code.
CREATE TABLE [dbo].[Batch](
	[BatchId] [int] IDENTITY(1,1) NOT NULL,
	[BatchName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED 
(
	[BatchId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
  • Change ProductQuantity table name to ProductStock.
  • Execute below code to add BatchId into ProductStock table and add new field PurchasePrice and SalesPrice.
CREATE TABLE [dbo].[ProductStock](
	[ProductQtyId] [int] IDENTITY(1,1) NOT NULL,
	[ProductId] [int] NULL,
	[Quantity] [int] NULL,
	[BatchId] [int] NULL,
	[PurchasePrice] [decimal](18, 0) NULL,
	[SalesPrice] [decimal](18, 0) NULL,
 CONSTRAINT [PK_ProductQuantity] PRIMARY KEY CLUSTERED 
(
	[ProductQtyId] 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

ALTER TABLE [dbo].[ProductStock]  WITH CHECK ADD  CONSTRAINT [FK_ProductQuantity_Product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([ProductId])
GO

ALTER TABLE [dbo].[ProductStock] CHECK CONSTRAINT [FK_ProductQuantity_Product]
GO

ALTER TABLE [dbo].[ProductStock]  WITH CHECK ADD  CONSTRAINT [FK_ProductStock_Batch] FOREIGN KEY([BatchId])
REFERENCES [dbo].[Batch] ([BatchId])
GO

ALTER TABLE [dbo].[ProductStock] CHECK CONSTRAINT [FK_ProductStock_Batch]
GO

Instead of Executing this code I recommend to see the video and follow video to complete this

  • Now run the project.
  • Then save, update, retrieve  data.

About Teacher

Reza Karim

Software Engineer

More about him