Canteen Sales and Credit Management System

Title: Canteen Sales and Credit Management System

Introduction and Description:

The project entitled Canteen Sales and Credit Management System was designed and developed using Visual Basic and MariaDB. It is a system that will run in Local Area Network or LAN. Canteen Sales and Credit Management System is a study intended for the store owners of the cafeteria inside the school canteen.

Sales and Credit Management are the two most important features that were incorporated in this project since this will help and improve the business process of the store owners especially in tracking and monitoring the credit line of their customers.

Most of the canteen store owners are still using pen and paper method in recording their sales as well as the debt/credit of their client which is very prone to errors. Inventory management is also a big problem since they are using a manual process, which is why monitoring of stocks, and items are very troublesome and the reports are not so accurate.

Sales and Credit Management is very important in businesses especially in mini stores, cafeterias and store owners inside the school canteen.  To reduce and avoid errors it is highly recommended that the Canteen Sales and Credit Management System must be implemented.

Objectives of the Study

The project aimed to automate the transactions in school canteens especially the monitoring of sales and credits. It is also aimed that the project will be designed and developed in Visual Basic and MariaDB.

Specifically, the project will:

1. Convert the manual process to automated one
2. The project can provide reliable and accurate records and reports
3. Better searching facilities for item, sales and credits
4. To provide a user-friendly environment for the end-users

System Features and Modules

Employee Module – there are two sub-modules under the employee module, first is the department list where in the different departments must be encoded and employee list where the information of employees must be encoded.

Supplier Module – list of suppliers are stored in this module.

Product Module – three sub-modules are under the product module it includes the unit of measurement, product category and the list of products.

Credit Line – credit line function is where the administrator set the limit or max amount the customer can borrow.

Receiving Module – new items are encoded in this section or module to add to the current inventory of supplies and products.

POS Module – the point of sale module is where the sales and credit transaction occurs.

Adjustments Module – adjustments modules are where records of damage items are encoded and recorded.

Payment Module – this is the section or module of the system where the user can encode and record the partial or full payment of the customers.

Inventory Report – this is the real-time inventory of items, stocks or products. It is the module that shows the current count of each item.

Collectibles Report – collectibles report will display the list of employees together with their balances.

Sales Report – sales report or income report will display the list of items and their quantity being sold as well as the total amount of sales.

Employee Accounts – employee accounts or ledger will display the payment history and the balance of each employee.

Stockin List – reports of items being encoded for additional inventory, these are the items encoded in the receiving module.

Database Schema (database design):

tbladjustment – adjustment table will store information on the items to be returned and damaged, the table includes the productid, quantity to return, date and time encoded and the user who encoded the transaction.

CREATE TABLE `tbladjustment` (
`id` int(11) NOT NULL,
`productid` int(11) NOT NULL,
`returnqty` int(11) NOT NULL,
`remarks` varchar(100) NOT NULL,
`daterec` date NOT NULL,
`timerec` time NOT NULL,
`processedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblcategory –  the category table will store information on the different categories of products such as foods, beverages, etc.

CREATE TABLE `tblcategory` (
`id` int(11) NOT NULL,
`categoryname` varchar(15) NOT NULL,
`catdesc` varchar(50) NOT NULL,
`sflag` int(5) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblcredit – credit table will store the value of the maximum amount that the customers can borrow.

CREATE TABLE `tblcredit` (
`id` int(11) NOT NULL,
`creditamount` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbldepartment – department table will store information on the different department of the school.

CREATE TABLE `tbldepartment` (
`id` int(11) NOT NULL,
`depinitial` varchar(15) NOT NULL,
`depdesc` varchar(50) NOT NULL,
`sflag` int(5) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblemployee – employee table will store information of the employees such as the id number, name, and department he/she is assigned to.

CREATE TABLE `tblemployee` (
`id` int(11) NOT NULL,
`idno` varchar(11) NOT NULL,
`lastname` varchar(25) NOT NULL,
`firstname` varchar(25) NOT NULL,
`deptid` int(11) NOT NULL,
`sflag` int(5) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblledger – the ledger table will store information on the list of credits and as well as the list of payments. Ledger also means the balance sheet of the employees.

CREATE TABLE `tblledger` (
`id` int(11) NOT NULL,
`empid` int(11) NOT NULL,
`creditamount` double NOT NULL,
`creditid` int(11) NOT NULL,
`paymentamount` double NOT NULL,
`paymentid` int(11) NOT NULL,
`balanceamount` double NOT NULL,
`daterec` date NOT NULL,
`timerec` time NOT NULL,
`processedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblmeasurement – measurement table will store information on the different unit of measurement of the items. Example (pcs, box, bottle, etc.).

CREATE TABLE `tblmeasurement` (
`id` int(11) NOT NULL,
`unitname` varchar(15) NOT NULL,
`unitdesc` varchar(30) NOT NULL,
`sflag` int(5) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblpayment – payment table will store information on the payment of customers, it includes the customerid, payment amount, date/time recorded and the user who processed the transaction.

CREATE TABLE `tblpayment` (
`id` int(11) NOT NULL,
`empid` int(11) NOT NULL,
`paymentamount` double NOT NULL,
`daterec` date NOT NULL,
`timerec` time NOT NULL,
`processedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblprivileges – privileges table will allow the administrator to select the modules that can be accessed by the users. Example, if the admin will check only the allow_add, access_product and access_supplier, this means the user with those privileges can only add record to the product module and supplier module.

CREATE TABLE `tblprivileges` (
`id` int(11) NOT NULL,
`categoryname` varchar(25) NOT NULL,
`jobdesc` varchar(200) NOT NULL,
`allow_add` int(2) NOT NULL DEFAULT '0',
`allow_update` int(2) NOT NULL DEFAULT '0',
`allow_delete` int(2) NOT NULL DEFAULT '0',
`allow_print` int(2) NOT NULL DEFAULT '0',
`allow_export` int(2) NOT NULL DEFAULT '0',
`access_user` int(2) NOT NULL DEFAULT '0',
`access_backup` int(2) NOT NULL DEFAULT '0',
`access_employee` int(2) NOT NULL DEFAULT '0',
`access_product` int(2) NOT NULL DEFAULT '0',
`access_supplier` int(2) NOT NULL DEFAULT '0',
`access_creditline` int(2) NOT NULL DEFAULT '0',
`access_stockin` int(2) NOT NULL DEFAULT '0',
`access_pos` int(2) NOT NULL DEFAULT '0',
`access_adjustment` int(2) NOT NULL DEFAULT '0',
`access_payment` int(2) NOT NULL DEFAULT '0',
`access_inventory` int(2) NOT NULL DEFAULT '0',
`access_collectible` int(2) NOT NULL DEFAULT '0',
`access_sales` int(2) NOT NULL DEFAULT '0',
`access_employeeaccounts` int(2) NOT NULL DEFAULT '0',
`access_stockinlist` int(2) NOT NULL DEFAULT '0',
`access_stockoutlist` int(2) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblproduct – product table will store information of products like the name, barcode(optional), capital price, selling price, reorder level, etc.

Note: the reorder level will be the basis of the system in order to notify the users if such items needs to be replenished.

CREATE TABLE `tblproduct` (
`id` int(11) NOT NULL,
`categoryid` int(11) NOT NULL,
`pbarcode` varchar(20) NOT NULL,
`pname` varchar(50) NOT NULL,
`pdescription` varchar(50) NOT NULL,
`capitalprice` double NOT NULL,
`sellingprice` double NOT NULL,
`initialqty` int(11) NOT NULL DEFAULT '0',
`reorderlevel` int(11) NOT NULL,
`unitmid` int(11) NOT NULL,
`supplierid` int(11) NOT NULL,
`sflag` int(5) NOT NULL DEFAULT '1',
`totalin` int(11) NOT NULL DEFAULT '0',
`tmptotalin` int(11) NOT NULL DEFAULT '0',
`totalout` int(11) DEFAULT '0',
`tmptotalout` int(11) NOT NULL DEFAULT '0',
`totaladjustment` int(11) NOT NULL DEFAULT '0',
`inventorycount` int(11) NOT NULL DEFAULT '0',
`tmpinventorycount` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblsales – sales table will store information such as employeeid, products and the payment type if it is cash or credit.

CREATE TABLE `tblsales` (
`id` int(11) NOT NULL,
`productid` int(11) NOT NULL,
`employeeid` int(11) NOT NULL,
`qty` int(11) NOT NULL,
`psubtotal` double NOT NULL,
`salesdate` date NOT NULL,
`payment` int(5) NOT NULL COMMENT '0=credit, 1=cash',
`userid` int(11) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblstockin – newly bought products and stocks are encoded and recorded in the stock in table. The table includes the productid, quantity, subtotal amount, supplier and the user who encoded the transaction.

CREATE TABLE `tblstockin` (
`id` int(11) NOT NULL,
`productid` int(11) NOT NULL,
`qty` int(11) NOT NULL,
`psubtotal` double NOT NULL,
`indate` date NOT NULL,
`supplierid` int(11) NOT NULL,
`userid` int(11) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblsupplier – supplier table will store information on the different suppliers of products and stocks. It includes the name, contact and address.

CREATE TABLE `tblsupplier` (
`id` int(11) NOT NULL,
`suppliername` varchar(50) NOT NULL,
`contact` varchar(15) NOT NULL,
`saddress` varchar(100) NOT NULL,
`sflag` int(5) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbluser – information of users that can access the system are stored in the users table.

CREATE TABLE `tbluser` (
`id` int(11) NOT NULL,
`fullname` varchar(50) NOT NULL,
`contact` varchar(15) NOT NULL,
`myusername` varchar(15) NOT NULL,
`mypassword` varchar(15) NOT NULL,
`usercategory` int(11) NOT NULL,
`sflag` int(11) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ERD/TableRelationship:

Canteen Sales and Credit Management System has a total of 14 tables and the image below shows the relationship of each table to another table(s).

Download system/project files

You may visit our facebook page for more information, inquiries and comments.

Hire our team to do the project.