GST Billing System with PHP & MySQL

In our previous tutorial, we have explained how to develop a Restaurant Management System with PHP and MySQL. In this tutorial, we will explain how to develop a GST Billing System with PHP and MySQL.

GST Billing or Invoice system is an online application to use in shops, factories to generate order invoices. It allows to add items or product details, create users, manage taxes and invoices. The invoice system provides instant calculations of items, quantity, taxes and totals.

Also, read:

• Visitor Management System with PHP & MySQL

So if you’re a PHP programmer and looking for solution to develop your own GST Billing or Invoice system, then you’re here at the right place. In this tutorial, we will cover tutorial to develop billing system with PHP and MySQL.

Here in this tutorial, we will develop an example of online billing system and cover following.

The Administrator will do the following:

• Manage Items / Products.
• Manage Taxes.
• Manage Billing / Invoices.
• Manage Users.

The normal users will do the following:

• Manage Billing / Invoices.

So let’s implement online billing system. The major files are:

• items.php
• taxes.php
• invoices.php
• users.php
• User.php: User class to hold methods related to user.
• Item.php: Item class to hold methods related to food items.
• Tax.php: Tax class to hold methods related to taxes.
• Invoice.php: Invoice class to hold methods related to manage invoices.

Step1: Create MySQL Database Table

We will create MySQL database tables. So we will create billing_user table to store user login details.

CREATE TABLE `billing_user` (
  `id` int(11) UNSIGNED NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `gender` enum('Male','Female') NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(64) NOT NULL,
  `mobile` varchar(12) NOT NULL,
  `address` text NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `role` enum('admin','user') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

we will create table billing_items to store irems/product details.

CREATE TABLE `billing_items` (
  `id` int(11) NOT NULL,
  `name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `category_id` int(11) NOT NULL,
  `status` enum('Enable','Disable') COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

we will create table billing_tax to store taxes details CGST, SGST etc.

CREATE TABLE `billing_tax` (
  `id` int(11) NOT NULL,
  `tax_name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `percentage` decimal(4,2) NOT NULL,
  `status` enum('Enable','Disable') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Enable'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

we will create table billing_order to store order or invoice details.

CREATE TABLE `billing_order` (
  `id` int(11) NOT NULL,
  `gross_amount` decimal(12,2) NOT NULL,
  `tax_amount` decimal(12,2) NOT NULL,
  `net_amount` decimal(12,2) NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` int(11) NOT NULL,
  `status` enum('In Process','Paid') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'In Process'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

and we will create table billing_order_item to store order items /invoice items details.

CREATE TABLE `billing_order_item` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `quantity` int(4) NOT NULL,
  `rate` decimal(12,2) NOT NULL,
  `amount` decimal(12,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Step2: Manage Items / Products

In this section, we will manage items to display items listing. We will implement functionality to add new items with edit and delete. We will create a method listItems() in class Item.php to return list of items as JSON data to load in jQuery datatables.

public function listItems(){			
		
	$sqlQuery = "
		SELECT item.id, item.name AS item_name, item.price, item.category_id, item.status
		FROM ".$this->itemTable." item ";
					
	if(!empty($_POST["order"])){
		$sqlQuery .= ' ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
	} else {
		$sqlQuery .= ' ORDER BY item.id ASC ';
	}
	
	if($_POST["length"] != -1){
		$sqlQuery .= ' LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
	}
	
	$stmt = $this->conn->prepare($sqlQuery);
	$stmt->execute();
	$result = $stmt->get_result();	
	
	$stmtTotal = $this->conn->prepare($sqlQuery);
	$stmtTotal->execute();
	$allResult = $stmtTotal->get_result();
	$allRecords = $allResult->num_rows;
	
	$displayRecords = $result->num_rows;
	$records = array();	

	while ($items = $result->fetch_assoc()) { 				
		$rows = array();			
		$rows[] = $items['id'];			
		$rows[] = $items['item_name'];
		$rows[] = "$".$items['price'];			
		$rows[] = $items['status'];
		$rows[] = '<button type="button" name="update" id="'.$items["id"].'" class="btn btn-warning btn-xs update"><span class="glyphicon glyphicon-edit" title="Edit"></span></button>';			
		$rows[] = '<button type="button" name="delete" id="'.$items["id"].'" class="btn btn-danger btn-xs delete" ><span class="glyphicon glyphicon-remove" title="Delete"></span></button>';
		$records[] = $rows;
	}
	
	$output = array(
		"draw"	=>	intval($_POST["draw"]),			
		"iTotalRecords"	=> 	$displayRecords,
		"iTotalDisplayRecords"	=>  $allRecords,
		"data"	=> 	$records
	);
	
	echo json_encode($output);
}

We will implement method insert() to insert items.

public function insert(){
		
	if($this->itemName && $_SESSION["userid"]) {

		$stmt = $this->conn->prepare("
			INSERT INTO ".$this->itemTable."(`name`, `price`, `status`)
			VALUES(?, ?, ?)");
	
		$this->itemName = htmlspecialchars(strip_tags($this->itemName));
		$this->price = htmlspecialchars(strip_tags($this->price));			
		$this->status = htmlspecialchars(strip_tags($this->status));
		
		$stmt->bind_param("sss", $this->itemName, $this->price, $this->status);
		
		if($stmt->execute()){
			return true;
		}		
	}
}

We will also implement method update() to update items.

public function update(){
		
	if($this->id && $this->itemName && $_SESSION["userid"]) {
		
		$stmt = $this->conn->prepare("
		UPDATE ".$this->itemTable." 
		SET name = ?, price = ?, status = ?
		WHERE id = ?");
 
		$this->itemName = htmlspecialchars(strip_tags($this->itemName));
		$this->price = htmlspecialchars(strip_tags($this->price));			
		$this->status = htmlspecialchars(strip_tags($this->status));
							
		$stmt->bind_param("sssi", $this->itemName, $this->price, $this->status, $this->id);
		
		if($stmt->execute()){				
			return true;
		}			
	}	
}	

Step3: Manage Invoices

In this section, we will implement functionality to manage orders or invoices. We will create method listOrder() in class Invoices.php to implement functionality to list invoices.

public function listOrder(){			
		
	$sqlQuery = "
		SELECT orders.id, orders.gross_amount, orders.tax_amount, orders.net_amount, orders.created, orders.created_by, orders.status
		FROM ".$this->orderTable." orders ";
					
	if(!empty($_POST["order"])){
		$sqlQuery .= ' ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
	} else {
		$sqlQuery .= ' ORDER BY orders.id DESC ';
	}
	
	if($_POST["length"] != -1){
		$sqlQuery .= ' LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
	}
	
	$stmt = $this->conn->prepare($sqlQuery);
	$stmt->execute();
	$result = $stmt->get_result();	
	
	$stmtTotal = $this->conn->prepare($sqlQuery);
	$stmtTotal->execute();
	$allResult = $stmtTotal->get_result();
	$allRecords = $allResult->num_rows;
	
	$displayRecords = $result->num_rows;
	$records = array();	

	while ($order = $result->fetch_assoc()) { 				
		$rows = array();			
		$rows[] = $order['id'];				
		$rows[] = "$".$order['gross_amount'];
		$rows[] = "$".$order['tax_amount'];
		$rows[] = "$".$order['net_amount'];
		$rows[] = $order['created'];
		$rows[] = $order['created_by'];
		$rows[] = $order['status'];
		$rows[] = '<button type="button" name="update" id="'.$order["id"].'" class="btn btn-warning btn-xs update"><span class="glyphicon glyphicon-edit" title="Edit"></span></button>';			
		$rows[] = '<button type="button" name="delete" id="'.$order["id"].'" class="btn btn-danger btn-xs delete" ><span class="glyphicon glyphicon-remove" title="Delete"></span></button>';
		$records[] = $rows;
	}
	
	$output = array(
		"draw"	=>	intval($_POST["draw"]),			
		"iTotalRecords"	=> 	$displayRecords,
		"iTotalDisplayRecords"	=>  $allRecords,
		"data"	=> 	$records
	);
	
	echo json_encode($output);
}

we will implement method insert() in class Invoices.php to save new invoices with details into table billing_order and billing_order_item.

public function insert(){
		
	if($_SESSION["userid"] && $this->subTotal) {
		$stmt = $this->conn->prepare("
			INSERT INTO ".$this->orderTable."(`gross_amount`, `tax_amount`, `net_amount`, `created_by`, `status`)
			VALUES(?, ?, ?, ?, ?)");		
		
		$this->subTotal = htmlspecialchars(strip_tags($this->subTotal));
		$this->taxAmount = htmlspecialchars(strip_tags($this->taxAmount));
		$this->totalAftertax = htmlspecialchars(strip_tags($this->totalAftertax));
		$this->status = htmlspecialchars(strip_tags($this->status));

		$stmt->bind_param("sssss", $this->subTotal, $this->taxAmount, $this->totalAftertax, $_SESSION["role"], $this->status);
		
		if($stmt->execute()){				
			$lastInsertId = $this->conn->insert_id;				
			$stmt1 = $this->conn->prepare("
				INSERT INTO ".$this->orderItemTable."(`order_id`, `item_id`, `quantity`, `rate`, `amount`)
				VALUES(?,?,?,?,?)");					
			foreach($this->items as $key => $value) {					
				$stmt1->bind_param("iiiss", $lastInsertId, $this->items[$key], $this->quantity[$key], $this->price[$key], $this->total[$key]);
				$stmt1->execute();
			}
			return true;
		}		
	}
}

we will implement method update() to update the invoices with details into table billing_order and billing_order_item.

public function update(){
		
	if($this->id && $_SESSION["userid"] && $this->subTotal) {
		
		$stmt = $this->conn->prepare("
		UPDATE ".$this->orderTable." 
		SET gross_amount = ?, tax_amount = ?, net_amount = ?, status = ?
		WHERE id = ?");
 
		$this->subTotal = htmlspecialchars(strip_tags($this->subTotal));
		$this->taxAmount = htmlspecialchars(strip_tags($this->taxAmount));
		$this->totalAftertax = htmlspecialchars(strip_tags($this->totalAftertax));
		$this->status = htmlspecialchars(strip_tags($this->status));

		$stmt->bind_param("ssssi", $this->subTotal, $this->taxAmount, $this->totalAftertax, $this->status, $this->id);			
		if($stmt->execute()){
			$this->deleteOrderItems($this->id);				
			$stmt1 = $this->conn->prepare("
				INSERT INTO ".$this->orderItemTable."(`order_id`, `item_id`, `quantity`, `rate`, `amount`)
				VALUES(?,?,?,?,?)");					
			foreach($this->items as $key => $value) {					
				$stmt1->bind_param("iiiss", $this->id, $this->items[$key], $this->quantity[$key], $this->price[$key], $this->total[$key]);
				$stmt1->execute();
			}				
			return true;
		}			
	}	
}	

We have also implemented functionality to manage taxes, users in live demo with add, edit and delete functionality.

You may also like:

• Follow and Unfollow System with PHP & MySQL
• Student Attendance System with PHP & MySQL
• Like and Dislike System with Ajax, PHP & MySQL
• Build Star Rating System with jQuery, PHP & MySQL
• User Registration and Login System with PHP & MySQL
• Build Contact Form with PHP and Ajax
• To-do List Application with Ajax, PHP & MySQL
• Build Event Calendar with jQuery , PHP & MySQL

You can view the live demo from the Demo link and can download the source from the Download link below.
Demo Download