The Sales and Inventory Management System for a Departmental Store has two main components:
1. Admin
2. Salesman
It is a console based application with the MySQL database which has 6 tables as follows:
login, items, pending_items, items_bought, inventory_report, sales_report.
After login as Admin, the operations that are available to the admin are
1. Manage Orders : When the stock of any item becomes very low, the admin can order items to
the supplier so that it is available in sufficient quantity.
1.1. Place New Order : The admin can order items by giving its ID, name and quantity.
1.2. View Pending Orders : The admin can view the list of items placed before and yet to be
delivered.
1.2.1. Change Delivery Status : Once the items are delivered, the admin can mark that
particular order as Done by giving its ID.
1.3. Cancel Orders : The admin can cancel the order which was placed and not yet delivered.
2. Add New Items : When the admin wants to add a new item to the inventory, he can add it
by mentioning the details of the item item ID, item name and item initial price.
3. Change Price of Item : The admin can change the price of an existing item in the inventory
by specifying the item ID and the new price.
4. View Inventory : The admin can view the contents of the inventory.
4.1. Generate Inventory Report : The admin can generate a full report of the inventory
turnover for a specific period by giving the start and end date.
After login as Salesman, the operations that are available to salesman are
1. View Inventory : The salesman can view the contents of the inventory.
1.1. Generate Inventory Report : The salesman can generate a full report of the
inventory turnover for a specific period by giving start and end date.
2. Generate Bills : The Salesman can generate a bill for each Customer by entering the name
of the customer and then the ID, name, quantity of each item bought. The bill along with the
total amount to be paid by the customer is displayed.
3. Update Inventory : The Salesman can update the items in the Inventory.
3.1. Add Items : When the admin makes an order and the items are delivered, they
are added to the inventory by the salesman by using the Item ID from the
pending_items table.
3.2. Delete Items : The quantity of the items purchased by the customer are deleted
from the inventory to get the updated quantities by using the Item ID from the
items_bought table.
3.2.1. Send Less Stock Alert : The salesman display all the items when the
quantity of that item in the inventory is below a threshold value.
4. Generate Sales Report : The Salesman can generate the sales report, which has all the
transactions done by the customers for a specific period by entering the start and end date.
The tables created and used in MySQL Databases are:
1. Login Table: This table stores the username, name, password and the type of user whether admin or salesman. The passwords are stored in an excrypted format. The details of the user during signup is stored over here.
2. Items Table: The item ID, name, quantity and price of the item present in the inventory is stored over here.
3. Pending Items Table: The orders of the list of items placed before and yet to be delivered are stored here.
4. Inventory Report Table: The transactions of the items(addition of a new item, adding items in stock and selling of items) that have taken place in the store is stored here to generate the inventory report.
5.Items Bought Table: The details of the items bought by the customer are stored over here which is later used to generate the bill.
6. Sales Report Table: Sales which is the total amount of the items bought by each customer are stored here.
