Title: Jewelry Sales Accounting and Appraisal System
Description:
The project entitled Jewelry Sales Accounting and Appraisal System is a web based project designed and developed using PHP, MySQL and Bootstrap. This project is intended to replace the manual process of sales, accounting and appraisal for Jewelry shops. The said project is an example of ecommerce platform that is specifically intended to jewelry items and the likes, it also includes the basic features of an ecommerce such as cataloguing of items, the virtual caddy or the shopping cart module, the checkout feature that will allow the customers to input the billing address and as well as the payment option. Back office system is also incorporated in this project which means that inventory and sales monitoring can be generated anytime, anywhere.
Advantages of the project:
- Reduction of inventory errors
- Better inventory management and improved item visibility
- Generates reports easily
- Efficient, simple and user friendly
- Centralized records
System Modules
The system has three user types or categories with specific roles and privileges ; administrator, staff and the customers
Administrator account has a full access to the system with the following modules and system features:
- Item/jewelry encoding module
- Sales and Accounting module
- Report Generation module
Staff account is limited and can only access the encoding portion of the system
Customers cannot access the back office module of the project, they can only access the frontend part of the project in which they can login to their account to view the list of transactions such as sales and appraised items.
Database Schema with description of table:
tblusers (id, username, password, completename, contact, address, usertype)
sql:
CREATE TABLE `inet_jewelrydb`.`tblusers` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `username` VARCHAR(15) NOT NULL , `password` VARCHAR(25) NOT NULL , `completename` VARCHAR(100) NOT NULL , `contact` VARCHAR(11) NOT NULL , `address` VARCHAR(150) NOT NULL , `usertype` INT(2) NOT NULL DEFAULT '0' , PRIMARY KEY (`id`)) ENGINE = InnoDB;
Users table will store the basic information of users that can access the back-end part of the project, this information includes the username, password and the usertype. The back-end can be accessed by administrator and staff.
tblcustomers(id, username, password, completename, contact, address, billingaddress, email, city, province, zipcode)
sql:
CREATE TABLE `inet_jewelrydb`.`tblcustomers` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `username` VARCHAR(15) NOT NULL , `password` VARCHAR(25) NOT NULL , `completename` VARCHAR(50) NOT NULL , `contact` VARCHAR(11) NOT NULL , `address` VARCHAR(100) NOT NULL , `billingaddress` VARCHAR(100) NOT NULL , `email` VARCHAR(50) NOT NULL , `city` VARCHAR(50) NOT NULL , `province` VARCHAR(50) NOT NULL , `zipcode` VARCHAR(6) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
Customers table will store information of the customers including complete name, contact, billing address, email, username and password.
tbljewelryitems (id, itemname, amount, description, qty, categoryid)
sql:
CREATE TABLE `inet_jewelrydb`.`tbljewelryitems` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `itemname` VARCHAR(30) NOT NULL , `amount` DOUBLE NOT NULL , `description` VARCHAR(100) NOT NULL , `qty` INT NOT NULL , `categoryid` INT(5) NOT NULL , PRIMARY KEY (`id`), INDEX (`categoryid`)) ENGINE = InnoDB;
This is the table that will hold the records of each jewelry that includes the name, amount, detailed description, quantity available and the category it belong.
tblcategories (id, categoryname)
sql:
CREATE TABLE `inet_jewelrydb`.`tblcategories` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `categoryname` VARCHAR(30) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
Categories table will store the category name of the jewelries.
tblsales (id, customerid, itemid, qty, subtotal, transactiondate, status, userid)
sql:
CREATE TABLE `inet_jewelrydb`.`tblsales` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `customerid` INT(11) NOT NULL , `itemid` INT(11) NOT NULL , `qty` INT(11) NOT NULL , `subtotal` DOUBLE NOT NULL , `transactiondate` DATE NOT NULL , `status` VARCHAR(15) NOT NULL , `userid` INT(11) NOT NULL , PRIMARY KEY (`id`), INDEX (`customerid`), INDEX (`itemid`), INDEX (`userid`)) ENGINE = InnoDB;
Sales table will store the records of the item sold and the information of the customers; it also includes the quantity subtotal and the date of transaction. The transactions will be reviewed by the user before updating the status of the transaction. This table is also connected to users table which means that the records are being monitored.
ERD/TableRelationship:
