<?php

session_start();


if(!isset($_SESSION['user_id'])){

    header("Location: login.php");
    exit;

}


require_once "config.php";



// =======================
// FILTER VALUES
// =======================


$start=$_GET['start'] ?? '';

$end=$_GET['end'] ?? '';

$category=$_GET['category'] ?? '';

$vendor=$_GET['vendor'] ?? '';

$item=$_GET['item'] ?? '';

$user=$_GET['user'] ?? '';

$payment=$_GET['payment'] ?? '';

$search=$_GET['search'] ?? '';

$min=$_GET['min'] ?? '';

$max=$_GET['max'] ?? '';





$where=[];

$params=[];



// DATE FILTER

if($start!=""){

    $where[]="expenses.expense_date >= ?";

    $params[]=$start;

}



if($end!=""){

    $where[]="expenses.expense_date <= ?";

    $params[]=$end;

}




// CATEGORY FILTER

if($category!=""){

    $where[]="expenses.category_id=?";

    $params[]=$category;

}




// VENDOR FILTER

if($vendor!=""){

    $where[]="expenses.vendor_id=?";

    $params[]=$vendor;

}




// EXPENSE ITEM FILTER

if($item!=""){

    $where[]="expenses.item_id=?";

    $params[]=$item;

}





// CREATED BY FILTER

if($user!=""){

    $where[]="expenses.created_by=?";

    $params[]=$user;

}





// PAYMENT FILTER

if($payment!=""){

    $where[]="expenses.payment_method=?";

    $params[]=$payment;

}





// AMOUNT FILTER

if($min!=""){

    $where[]="expenses.total >= ?";

    $params[]=$min;

}



if($max!=""){

    $where[]="expenses.total <= ?";

    $params[]=$max;

}





// SEARCH

if($search!=""){


$where[]="

(
expenses.description LIKE ?

OR vendors.vendor_name LIKE ?

OR expense_categories.category_name LIKE ?

OR expense_items.item_name LIKE ?

OR expense_items.registration_no LIKE ?

)

";


$params[]="%$search%";

$params[]="%$search%";

$params[]="%$search%";

$params[]="%$search%";

$params[]="%$search%";


}






// =======================
// MAIN REPORT QUERY
// =======================


$sql="

SELECT


expenses.*,


expense_categories.category_name,


vendors.vendor_name,


expense_items.item_name,


expense_items.registration_no,


users.fullname



FROM expenses



LEFT JOIN expense_categories

ON expenses.category_id=expense_categories.id




LEFT JOIN vendors

ON expenses.vendor_id=vendors.id




LEFT JOIN expense_items

ON expenses.item_id=expense_items.id




LEFT JOIN users

ON expenses.created_by=users.id



";





if($where){

    $sql.=" WHERE ".implode(" AND ",$where);

}




$sql.=" ORDER BY expenses.expense_date DESC";





$stmt=$pdo->prepare($sql);

$stmt->execute($params);


$expenses=$stmt->fetchAll();







// =======================
// TOTAL REPORT AMOUNT
// =======================


$total=0;


foreach($expenses as $e){

    $total += $e['total'];

}





// =======================
// SUMMARY
// =======================


// Number of records

$records=count($expenses);



// Average expense

$average=0;


if($records>0){

    $average=$total/$records;

}






// =======================
// DROPDOWNS
// =======================



$categories=$pdo->query("

SELECT *

FROM expense_categories

WHERE status=1

ORDER BY category_name

")->fetchAll();






$vendors=$pdo->query("

SELECT *

FROM vendors

WHERE status=1

ORDER BY vendor_name

")->fetchAll();






$items=$pdo->query("

SELECT *

FROM expense_items

WHERE status=1

ORDER BY item_name

")->fetchAll();







$users=$pdo->query("

SELECT *

FROM users

WHERE status=1

ORDER BY fullname

")->fetchAll();






// =======================
// STATEMENTS
// =======================



$vendorStatement=[];


if($vendor!=""){


$stmt=$pdo->prepare("

SELECT

vendors.vendor_name,

SUM(expenses.total) total


FROM expenses


LEFT JOIN vendors

ON expenses.vendor_id=vendors.id


WHERE expenses.vendor_id=?


GROUP BY expenses.vendor_id


");


$stmt->execute([$vendor]);


$vendorStatement=$stmt->fetch();

}




$categoryStatement=[];


if($category!=""){


$stmt=$pdo->prepare("

SELECT

expense_categories.category_name,

SUM(expenses.total) total


FROM expenses


LEFT JOIN expense_categories

ON expenses.category_id=expense_categories.id


WHERE expenses.category_id=?


GROUP BY expenses.category_id


");


$stmt->execute([$category]);


$categoryStatement=$stmt->fetch();


}






$itemStatement=[];


if($item!=""){


$stmt=$pdo->prepare("

SELECT


expense_items.item_name,


expense_items.registration_no,


SUM(expenses.total) total



FROM expenses



LEFT JOIN expense_items

ON expenses.item_id=expense_items.id



WHERE expenses.item_id=?



GROUP BY expenses.item_id


");


$stmt->execute([$item]);


$itemStatement=$stmt->fetch();


}



?>
<!DOCTYPE html>

<html>

<head>

<meta name="viewport" content="width=device-width,initial-scale=1">


<title>
Expense Report
</title>


<link href="https://fonts.googleapis.com/css2?family=Poppins:wght@400;500;600&display=swap" rel="stylesheet">


<link rel="stylesheet" href="assets/css/sidebar.css">


<link rel="stylesheet"
href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.0/css/all.min.css">



<style>


*{

margin:0;
padding:0;
box-sizing:border-box;
font-family:Poppins;

}



body{

background:#f4f7f9;
transition:.3s;

}



body.dark{

background:#111827;
color:white;

}




.content{

margin-left:260px;

padding:25px;

}




.topbar{

display:flex;

justify-content:space-between;

align-items:center;

margin-bottom:20px;

}




.mobile-btn{

display:none;

font-size:25px;

cursor:pointer;

color:#065f46;

}




.toggle{

font-size:25px;

cursor:pointer;

color:#059669;

}




.cards{

display:grid;

grid-template-columns:
repeat(auto-fit,minmax(180px,1fr));

gap:15px;

margin-bottom:20px;

}

.statement-link{

color:#065f46;

font-weight:600;

text-decoration:none;

display:inline-flex;

align-items:center;

gap:8px;

transition:.3s;

}


.statement-link:hover{

color:#059669;

transform:translateX(3px);

}



.statement-link i{

background:#ecfdf5;

width:28px;

height:28px;

border-radius:8px;

display:flex;

align-items:center;

justify-content:center;

color:#059669;

}





body.dark .statement-link{

color:#10b981;

}





small{

color:#64748b;

font-size:11px;

}






.card{


background:white;

padding:20px;

border-radius:15px;

box-shadow:0 5px 20px #0002;

}



body.dark .card{

background:#1f2937;

}





.icon-box{

width:45px;

height:45px;

border-radius:12px;

background:#ecfdf5;

color:#059669;

display:flex;

align-items:center;

justify-content:center;

font-size:20px;

margin-bottom:10px;

}



.number{

font-size:22px;

font-weight:bold;

color:#059669;

}





.filter-box{


background:white;

padding:20px;

border-radius:15px;

margin-bottom:20px;

box-shadow:0 5px 20px #0001;

}



body.dark .filter-box{

background:#1f2937;

}





.filter{


display:grid;

grid-template-columns:

repeat(auto-fit,minmax(180px,1fr));

gap:12px;


}



input,
select{


width:100%;

padding:12px;

border-radius:10px;

border:1px solid #ddd;

outline:none;

}




button,
.btn{


background:#059669;

color:white;

padding:12px 18px;

border:0;

border-radius:10px;

text-decoration:none;

cursor:pointer;

text-align:center;

}



.print{

background:#2563eb;

}




.statement{


display:grid;

grid-template-columns:

repeat(auto-fit,minmax(250px,1fr));

gap:15px;

margin-bottom:20px;


}




.statement-card{


background:#ecfdf5;

padding:20px;

border-radius:15px;

border-left:5px solid #059669;

}




.statement-card h3{

color:#065f46;

}



body.dark .statement-card{

background:#374151;

}




.table-box{


background:white;

padding:20px;

border-radius:15px;

overflow:auto;

}



body.dark .table-box{

background:#1f2937;

}




table{

width:100%;

border-collapse:collapse;

}



th{

background:#065f46;

color:white;

padding:12px;

}



td{

padding:12px;

border-bottom:1px solid #eee;

}





.badge{


background:#dcfce7;

color:#166534;

padding:5px 12px;

border-radius:20px;

font-size:12px;

}





.overlay{


display:none;

position:fixed;

inset:0;

background:#0008;

z-index:999;


}



.overlay.show{

display:block;

}







@media(max-width:768px){



.content{

margin-left:0;

padding:15px;

}





.mobile-btn{

display:block;

}



.sidebar{

left:-260px;

}



.sidebar.active{

left:0;

}






table,
thead,
tbody,
th,
td,
tr{

display:block;

}




thead{

display:none;

}




tbody tr{


background:white;

margin-bottom:15px;

padding:15px;

border-radius:15px;

box-shadow:0 5px 20px #0002;


}





body.dark tbody tr{

background:#1f2937;

}



tbody td{


display:flex;

justify-content:space-between;

border:0;

}



tbody td:before{


font-weight:bold;

color:#065f46;

}



tbody td:nth-child(1):before{

content:"Date";

}


tbody td:nth-child(2):before{

content:"Category";

}



tbody td:nth-child(3):before{

content:"Item";

}



tbody td:nth-child(4):before{

content:"Vendor";

}



tbody td:nth-child(5):before{

content:"Description";

}



tbody td:nth-child(6):before{

content:"Amount";

}



tbody td:nth-child(7):before{

content:"Payment";

}


}





</style>


</head>


<body>


<?php include "sidebar.php"; ?>



<div class="overlay"
id="overlay"
onclick="closeMenu()"></div>



<div class="content">



<div class="topbar">


<i class="fa fa-bars mobile-btn"
onclick="openMenu()"></i>



<h2>

<i class="fa fa-chart-line"></i>

Expense Statement

</h2>



<i class="fa fa-moon toggle"
onclick="theme()"></i>



</div>





<div class="cards">



<div class="card">

<div class="icon-box">

<i class="fa fa-money-bill"></i>

</div>


<p>Total Expenses</p>

<div class="number">

 <?=number_format($total)?>

</div>

</div>





<div class="card">


<div class="icon-box">

<i class="fa fa-file"></i>

</div>


<p>Records</p>


<div class="number">

<?=$records?>

</div>


</div>





<div class="card">

<div class="icon-box">

<i class="fa fa-calculator"></i>

</div>


<p>Average Expense</p>


<div class="number">

 <?=number_format($average)?>

</div>


</div>



</div>





<div class="filter-box">


<h3>

<i class="fa fa-filter"></i>

Report Filters

</h3>


<br>


<form class="filter">


<input type="date"
name="start"
value="<?=$start?>">



<input type="date"
name="end"
value="<?=$end?>">



<select name="category">


<option value="">
All Categories
</option>


<?php foreach($categories as $c){ ?>

<option value="<?=$c['id']?>">

<?=$c['category_name']?>

</option>

<?php } ?>


</select>





<select name="vendor">


<option value="">
All Vendors
</option>


<?php foreach($vendors as $v){ ?>


<option value="<?=$v['id']?>">

<?=$v['vendor_name']?>

</option>


<?php } ?>


</select>





<select name="item">


<option value="">
All Expense Items
</option>


<?php foreach($items as $i){ ?>


<option value="<?=$i['id']?>">

<?=$i['item_name']?>


<?=($i['registration_no'])?'('.$i['registration_no'].')':''?>

</option>


<?php } ?>


</select>


<select name="user">


<option value="">
All Users
</option>


<?php foreach($users as $u){ ?>


<option value="<?=$u['id']?>">

<?=$u['fullname']?>

</option>


<?php } ?>


</select>





<select name="payment">


<option value="">
All Payments
</option>


<option value="Cash">
Cash
</option>


<option value="Bank">
Bank
</option>


<option value="Mobile Money">
Mobile Money
</option>


<option value="Card">
Card
</option>


</select>





<input type="number"
name="min"
placeholder="Minimum Amount"
value="<?=$min?>">



<input type="number"
name="max"
placeholder="Maximum Amount"
value="<?=$max?>">





<input type="text"

name="search"

placeholder="Search expense..."

value="<?=htmlspecialchars($search)?>">






<button>

<i class="fa fa-search"></i>

Generate

</button>





<a href="javascript:window.print()"

class="btn print">

<i class="fa fa-print"></i>

Print

</a>




</form>



</div>







<!-- STATEMENTS -->


<div class="statement">



<?php if($vendorStatement){ ?>


<div class="statement-card">


<h3>

<i class="fa fa-building"></i>

Vendor Statement

</h3>


<br>


<strong>

<?=$vendorStatement['vendor_name']?>

</strong>


<p>

Total Paid:

</p>


<h2>

 <?=number_format($vendorStatement['total'])?>

</h2>


</div>


<?php } ?>







<?php if($categoryStatement){ ?>


<div class="statement-card">


<h3>

<i class="fa fa-folder"></i>

Category Statement

</h3>


<br>


<strong>

<?=$categoryStatement['category_name']?>

</strong>


<p>

Total Spent:

</p>


<h2>

 <?=number_format($categoryStatement['total'])?>

</h2>


</div>


<?php } ?>








<?php if($itemStatement){ ?>


<div class="statement-card">


<h3>

<i class="fa fa-car"></i>

Expense Item Statement

</h3>


<br>


<strong>


<?=$itemStatement['item_name']?>


<?php if($itemStatement['registration_no']){ ?>


<br>

<?=$itemStatement['registration_no']?>


<?php } ?>


</strong>


<p>

Total Spent:

</p>


<h2>

<?=number_format($itemStatement['total'])?>

</h2>



</div>


<?php } ?>



</div>







<a class="btn"
href="export_expenses_pdf.php?<?=http_build_query($_GET)?>">
<i class="fa fa-file-pdf"></i>
PDF
</a>


<a class="btn"
href="export_expenses_excel.php?<?=http_build_query($_GET)?>">
<i class="fa fa-file-excel"></i>
Excel
</a>
<div class="table-box">



<table>



<thead>


<tr>


<th>Date</th>

<th>Category</th>

<th>Item</th>

<th>Vendor</th>

<th>Description</th>

<th>Amount</th>

<th>Payment</th>


</tr>


</thead>






<tbody>


<?php foreach($expenses as $e){ ?>



<tr>


<td>

<?=date("d M Y", strtotime($e['expense_date']))?>

</td>




<td>

<a class="statement-link"
href="category_statement.php?id=<?=$e['category_id']?>">

<i class="fa fa-folder-open"></i>

<?=$e['category_name']?>

</a>

</td>





<td>

<?php if($e['item_id']){ ?>


<a class="statement-link"
href="item_statement.php?id=<?=$e['item_id']?>">


<i class="fa fa-car"></i>


<?=$e['item_name']?>


<?php if($e['registration_no']){ ?>

<br>

<small>

<?=$e['registration_no']?>

</small>

<?php } ?>


</a>


<?php }else{ ?>


<span>
<i class="fa fa-minus"></i>
No Item
</span>


<?php } ?>


</td>





<td>

<?=$e['vendor_name'] ?? 'Unknown'?>

</td>







<td>

<?=$e['description']?>

</td>







<td>


<strong>

 <?=number_format($e['total'])?>

</strong>


</td>








<td>


<span class="badge">


<?=$e['payment_method']?>


</span>


</td>




</tr>




<?php } ?>



</tbody>


</table>



</div>






</div>







<script>



function openMenu(){


document.getElementById("sidebar")

.classList.add("active");



document.getElementById("overlay")

.classList.add("show");


}







function closeMenu(){


document.getElementById("sidebar")

.classList.remove("active");



document.getElementById("overlay")

.classList.remove("show");


}







function theme(){


document.body.classList.toggle("dark");



localStorage.setItem(

"dark",

document.body.classList.contains("dark")

);


}







if(localStorage.getItem("dark")=="true"){


document.body.classList.add("dark");


}





</script>






</body>

</html>