Expense Administration System with PHP & MySQL

[ad_1]

In our earlier tutorial, you may have realized develop On-line Examination System with PHP and MySQL. On this tutorial, you’ll learn to develop Expense Administration System with PHP and MySQL.

The Expense administration techniques are internet based mostly purposes to handle their earnings and bills. The customers allowed to login system and handle their earnings and bills and look at the report of bills for the vary of time. That is an preliminary degree undertaking during which we’ve coated part like handle earnings, bills, customers and so on.

Additionally, learn:

Right here we’ll develop a expense administration system and canopy following.

The Administrator will do the next:

Handle Earnings and it’s classes.
Handle Bills and it’s classes.
View Studies
Handle Customers

So let’s begin creating expense administration system. The foremost information are:

index.php
earnings.php
expense.php
report.php
consumer.php
Consumer.php: A category comprises customers strategies.
Earnings.php: A category comprises strategies associated to earnings.
Expense.php: A category comprises strategies associated to expense.
Report.php: A category comprises strategies associated to report.

Step1: Create MySQL Database Desk

We’ll create database desk expense_users to retailer consumer login data.


CREATE TABLE `expense_users` (
  `id` int(11) UNSIGNED NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `e mail` varchar(255) DEFAULT NULL,
  `password` varchar(64) NOT NULL,
  `function` enum('admin') DEFAULT 'admin'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `expense_users`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `expense_users`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

we’ll create database desk expense_income_category to retailer earnings class particulars.


CREATE TABLE `expense_income_category` (
  `id` int(11) NOT NULL,
  `title` varchar(250) NOT NULL,
  `standing` enum('allow','disable') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `expense_income_category`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `expense_income_category`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

we’ll create database desk expense_income to retailer earnings particulars.


CREATE TABLE `expense_income` (
  `id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `date` date NOT NULL,
  `category_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `expense_income`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `expense_income`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

we’ll create database desk expense_category to retailer expense class particulars.


CREATE TABLE `expense_category` (
  `id` int(11) NOT NULL,
  `title` varchar(250) NOT NULL,
  `standing` enum('allow','disable') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `expense_category`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `expense_category`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

and we’ll create database desk expense_expense to retailer expense particulars.


CREATE TABLE `expense_expense` (
  `id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `date` date NOT NULL,
  `category_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `expense_expense`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `expense_expense`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

Step2: Handle Earnings

In earnings.php file, we’ll create HTML to handle earnings.


<div> 	
	<div class="panel-heading">
		<div class="row">
			<div class="col-md-10">
				<h3 class="panel-title"></h3>
			</div>
			<div class="col-md-2" align="proper">
				<button sort="button" id="addIncome" class="btn btn-info" title="Add Earnings"><span class="glyphicon glyphicon-plus"></span></button>
			</div>
		</div>
	</div>
	<desk id="incomeListing" class="desk table-bordered table-striped">
		<thead>
			<tr>						
				<th>Sn.</th>					
				<th>Quantity</th>					
				<th>Class</th>
				<th>Date</th>						
				<th></th>
				<th></th>					
			</tr>
		</thead>
	</desk>
</div>

we’ll make ajax request to income_action.php with motion listIncome to load incode knowledge in Datatable.


var incomeRecords = $('#incomeListing').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,		
	"bFilter": false,
	'serverMethod': 'publish',		
	"order":[],
	"ajax":{
		url:"income_action.php",
		sort:"POST",
		knowledge:{motion:'listIncome'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0, 4, 5],
			"orderable":false,
		},
	],
	"pageLength": 10
});

we’ll verify for motion listIncome in income_action.php and name methodology listIncome() from class Earnings.php to load earnings knowledge.


$earnings = new Earnings($db);
if(!empty($_POST['action']) && $_POST['action'] == 'listIncome') {
	$income->listIncome();
}

we’ll implement methodology listIncome() in school Earnings.php and return earnings knowledge as JSON knowledge to load into datatable.


public perform listIncome(){		
		
	if($_SESSION["userid"]) {
		$sqlQuery = "SELECT earnings.id, earnings.quantity, earnings.date, class.title
			FROM ".$this->incomeTable." AS earnings 
			LEFT JOIN ".$this->incomeCategoryTable." AS class ON earnings.category_id = class.id 
			WHERE earnings.user_id = '".$_SESSION["userid"]."' ";		
			
		if(!empty($_POST["search"]["value"])){
			$sqlQuery .= ' AND (earnings.id LIKE "%'.$_POST["search"]["value"].'%" ';
			$sqlQuery .= ' OR earnings.quantity LIKE "%'.$_POST["search"]["value"].'%" ';
			$sqlQuery .= ' OR earnings.date LIKE "%'.$_POST["search"]["value"].'%" ';
			$sqlQuery .= ' OR class.title LIKE "%'.$_POST["search"]["value"].'%" ';							
		}
		
		if(!empty($_POST["order"])){
			$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
		} else {
			$sqlQuery .= 'ORDER BY earnings.id ';
		}
		
		if($_POST["length"] != -1){
			$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
		}	
		
		$stmt = $this->conn->put together($sqlQuery);
		$stmt->execute();
		$end result = $stmt->get_result();	
		
		$stmtTotal = $this->conn->put together($sqlQuery);
		$stmtTotal->execute();
		$allResult = $stmtTotal->get_result();
		$allRecords = $allResult->num_rows;
		
		$displayRecords = $result->num_rows;
		$data = array();		
		$rely = 1;
		whereas ($earnings = $result->fetch_assoc()) { 				
			$rows = array();			
			$rows[] = $rely;
			$rows[] = ucfirst($earnings['amount']);
			$rows[] = $earnings['name'];	
			$rows[] = $earnings['date'];			
			$rows[] = '<button sort="button" title="replace" id="'.$earnings["id"].'" class="btn btn-warning btn-xs replace"><span class="glyphicon glyphicon-edit" title="Edit"></span></button>';
			$rows[] = '<button sort="button" title="delete" id="'.$earnings["id"].'" class="btn btn-danger btn-xs delete" ><span class="glyphicon glyphicon-remove" title="Delete"></span></button>';
			$data[] = $rows;
			$rely++;
		}
		
		$output = array(
			"draw"	=>	intval($_POST["draw"]),			
			"iTotalRecords"	=> 	$displayRecords,
			"iTotalDisplayRecords"	=>  $allRecords,
			"knowledge"	=> 	$data
		);
		
		echo json_encode($output);
	}
}

Step3: Handle Bills

We’ll create HTML in expense.php to record bills document.


<div> 	
	<div class="panel-heading">
		<div class="row">
			<div class="col-md-10">
				<h3 class="panel-title"></h3>
			</div>
			<div class="col-md-2" align="proper">
				<button sort="button" id="addExpense" class="btn btn-info" title="Add expense"><span class="glyphicon glyphicon-plus"></span></button>
			</div>
		</div>
	</div>
	<desk id="expenseListing" class="desk table-bordered table-striped">
		<thead>
			<tr>						
				<th>Sn.</th>					
				<th>Quantity</th>					
				<th>Class</th>
				<th>Date</th>						
				<th></th>
				<th></th>					
			</tr>
		</thead>
	</desk>
</div>

we’ll initialize datatables and make ajax request with motion listExpense to load expense data.


var expenseRecords = $('#expenseListing').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,		
	"bFilter": false,
	'serverMethod': 'publish',		
	"order":[],
	"ajax":{
		url:"expense_action.php",
		sort:"POST",
		knowledge:{motion:'listExpense'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0, 4, 5],
			"orderable":false,
		},
	],
	"pageLength": 10
});

we’ll verify for motion listExpense in expense_action.php and name methodology listExpense() from class Expense.php.


if(!empty($_POST['action']) && $_POST['action'] == 'listExpense') {
	$expense->listExpense();
}

we’ll implement methodology listExpense() in school Expense.php to return bills record as json knowledge.


public perform listExpense(){		
	if($_SESSION["userid"]) {
		$sqlQuery = "SELECT expense.id, expense.quantity, expense.date, class.title
			FROM ".$this->expenseTable." AS expense 
			LEFT JOIN ".$this->categoryTable." AS class ON expense.category_id = class.id 
			WHERE expense.user_id = '".$_SESSION["userid"]."' ";			
			
		if(!empty($_POST["search"]["value"])){
			$sqlQuery .= ' AND (expense.id LIKE "%'.$_POST["search"]["value"].'%" ';
			$sqlQuery .= ' OR expense.quantity LIKE "%'.$_POST["search"]["value"].'%" ';
			$sqlQuery .= ' OR expense.date LIKE "%'.$_POST["search"]["value"].'%" ';
			$sqlQuery .= ' OR class.title LIKE "%'.$_POST["search"]["value"].'%") ';							
		}
		
		if(!empty($_POST["order"])){
			$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
		} else {
			$sqlQuery .= 'ORDER BY expense.date DESC ';
		}
		
		if($_POST["length"] != -1){
			$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
		}	
		
		$stmt = $this->conn->put together($sqlQuery);
		$stmt->execute();
		$end result = $stmt->get_result();	
		
		$stmtTotal = $this->conn->put together($sqlQuery);
		$stmtTotal->execute();
		$allResult = $stmtTotal->get_result();
		$allRecords = $allResult->num_rows;
		
		$displayRecords = $result->num_rows;
		$data = array();	
		$rely = 1;
		whereas ($expense = $result->fetch_assoc()) { 				
			$rows = array();			
			$rows[] = $rely;
			$rows[] = ucfirst($expense['amount']);
			$rows[] = $expense['name'];	
			$rows[] = $expense['date'];			
			$rows[] = '<button sort="button" title="replace" id="'.$expense["id"].'" class="btn btn-warning btn-xs replace"><span class="glyphicon glyphicon-edit" title="Edit"></span></button>';
			$rows[] = '<button sort="button" title="delete" id="'.$expense["id"].'" class="btn btn-danger btn-xs delete" ><span class="glyphicon glyphicon-remove" title="Delete"></span></button>';
			$data[] = $rows;
			$rely++;
		}
		
		$output = array(
			"draw"	=>	intval($_POST["draw"]),			
			"iTotalRecords"	=> 	$displayRecords,
			"iTotalDisplayRecords"	=>  $allRecords,
			"knowledge"	=> 	$data
		);
		
		echo json_encode($output);
	}
}

Step4: Implement Report

In report.php file, we’ll create HTML to generate bills report by date vary.


<div> 	
	<div class="panel-heading">
		<div class="row">	
			<div>
				<h4>View Earnings and Expense Studies</h4>
			</div>
			<div class="col-md-2" fashion="padding-left:0px;">
				<enter sort="date" class="form-control" id="from_date" title="from_date" placeholder="From date" >
			</div>
			<div class="col-md-2" fashion="padding-left:0px;">
				<enter sort="date" class="form-control" id="to_date" title="to_date" placeholder="So far" >
			</div>
			<div class="col-md-2" fashion="padding-left:0px;">
				<button sort="submit" id="viewReport" class="btn btn-info" title="View Report"><span class="glyphicon glyphicon-search"></span></button>
			</div>
		</div>
	</div>
	<desk class="desk table-bordered table-striped" id="reportTable" fashion="show:none;">
		<thead>
			<tr>									
				<th>Expense</th>					
				<th>Date</th>
				<th>Class</th>									
			</tr>				
		</thead>
		<tbody id="listReports">
		
		</tbody>
	</desk>
	<div class="panel-heading" id="detailSection" fashion="show:none;">
		<div class="row">		
			<div fashion="padding-bottom:5px;coloration:inexperienced"><sturdy>Whole Earnings : </sturdy><span id="totalIncome"></span></div>
			<div fashion="padding-bottom:5px;coloration:crimson"><sturdy>Whole Expense : </sturdy><span id="totalExpense"></span></div>
			<div fashion="padding-bottom:5px;coloration:blue"><sturdy>Whole Saving : </sturdy><span id="totalSaving"></span></div>
		</div>
	</div>
	<div class="panel-heading" id="noRecords" fashion="show:none;">
	</div>
</div>

In report.js, we’ll get date vary knowledge to generate report and make ajax request to get knowledge and show report.


$('#viewReport').click on(perform(){
	var fromDate = $('#from_date').val();
	var toDate = $('#to_date').val();		
	var motion = 'getReports';
	$.ajax({
		url:'report_action.php',
		methodology:"POST",
		knowledge:{fromDate:fromDate, toDate:toDate, motion:motion},
		dataType:"json",
		success:perform(respData){				
			var reportHTML = '';
			var totalExpense = 0;
			$('#reportTable').cover();
			$('#noRecords').cover();
			respData.knowledge.forEach(perform(merchandise){	
				reportHTML+= '<tr>';
				reportHTML+= '<td>$'+merchandise['amount']+'</td>';
				reportHTML+= '<td>'+merchandise['date']+'</td>';
				reportHTML+= '<td>'+merchandise['category']+'</td>';	
				reportHTML+= '</tr>';
				totalExpense = totalExpense + parseInt(merchandise['amount']);
				$('#reportTable').present();
			});
			$('#listReports').html(reportHTML);
			$('#detailSection').cover();
			$('#totalIncome').textual content("");
			$('#totalExpense').textual content("");
			$('#totalSaving').textual content("");
			respData.earnings.forEach(perform(earnings){	
				$('#totalIncome').textual content("$"+earnings['total']);
				$('#totalExpense').textual content("$"+totalExpense);
				var finalTotal = earnings['total'] - totalExpense;
				$('#totalSaving').textual content("$"+finalTotal);
				$('#detailSection').present();
			});
			
			if(!totalExpense) {
				$('#noRecords').html("<sturdy>No document discovered!</sturdy>").present();
			}
		}
	});
});

we’ll verify for motion getReports in report_action and name methodology getReports() from class Report.php


if(!empty($_POST['action']) && $_POST['action'] == 'getReports') {
	$report->fromDate = $_POST['fromDate'];
	$report->toDate = $_POST['toDate'];
	$report->getReports();
}

and eventually we’ll implement methodology getReports() in school Report.php to get studies.


public perform getReports(){
	if($this->fromDate && $this->toDate && $_SESSION["userid"]) {				
			
		$sqlQuery = "SELECT expense.id, expense.quantity, expense.date, class.title AS class
			FROM ".$this->incomeTable." AS expense 
			LEFT JOIN ".$this->incomeCategoryTable." AS class ON expense.category_id = class.id 
			WHERE expense.user_id = '".$_SESSION["userid"]."' AND expense.date BETWEEN  '".$this->fromDate."' AND '".$this->toDate."'";
				
		$stmt = $this->conn->put together($sqlQuery);			
		$stmt->execute();
		$end result = $stmt->get_result();				
		$incomeRecords = array();	
		$totalIncome = 0;
		whereas ($earnings = $result->fetch_assoc()) {			
			$totalIncome+=$earnings['amount'];			
		}
		if($totalIncome) {
			$row = array();
			$row['total'] = $totalIncome;
			$incomeRecords[] = $row;
		}
		
		$sqlQuery = "SELECT expense.id, expense.quantity, expense.date, class.title AS class
			FROM ".$this->expenseTable." AS expense 
			LEFT JOIN ".$this->categoryTable." AS class ON expense.category_id = class.id 
			WHERE expense.date BETWEEN  '".$this->fromDate."' AND '".$this->toDate."'";
				
		$stmt = $this->conn->put together($sqlQuery);			
		$stmt->execute();
		$end result = $stmt->get_result();				
		$data = array();		
		whereas ($expense = $result->fetch_assoc()) { 				
			$rows = array();	
			$rows['id'] = $expense['id'];				
			$rows['amount'] = $expense['amount'];				
			$rows['date'] = $expense['date'];	
			$rows['category'] = $expense['category'];
			$data[] = $rows;
		}		
		$output = array(			
			"knowledge"	=> 	$data,
			"earnings" => $incomeRecords
		);
		echo json_encode($output);
	}
}

Now we have additionally implement add, edit and delete performance for all sections.

You might also like:

You may view the dwell demo from the Demo hyperlink and may obtain the script from the Obtain hyperlink beneath.
Demo Obtain

[ad_2]

Source_link

Step1: Create MySQL Database Desk

Step2: Handle Earnings

Step3: Handle Bills

Step4: Implement Report

Leave a Comment