<?php

session_start();


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

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

}


require_once "config.php";


$id=$_GET['id'] ?? 0;


if(!$id){

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

}



// ==========================
// ITEM DETAILS
// ==========================

$stmt=$pdo->prepare("
SELECT *
FROM expense_items
WHERE id=?
");

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

$item=$stmt->fetch();


if(!$item){

    die("Item not found");

}




// ==========================
// MAIN SUMMARY
// ==========================


$stmt=$pdo->prepare("

SELECT

COUNT(id) records,

COALESCE(SUM(total),0) total,

COALESCE(AVG(total),0) average,

COALESCE(MAX(total),0) highest,

COALESCE(MIN(total),0) lowest


FROM expenses

WHERE item_id=?

");


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


$summary=$stmt->fetch();





// ==========================
// MONTH SUMMARY
// ==========================


$stmt=$pdo->prepare("

SELECT

COALESCE(SUM(total),0)

FROM expenses

WHERE item_id=?

AND MONTH(expense_date)=MONTH(CURRENT_DATE())

AND YEAR(expense_date)=YEAR(CURRENT_DATE())


");


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


$thisMonth=$stmt->fetchColumn();






// ==========================
// YEAR SUMMARY
// ==========================


$stmt=$pdo->prepare("

SELECT

COALESCE(SUM(total),0)

FROM expenses

WHERE item_id=?

AND YEAR(expense_date)=YEAR(CURRENT_DATE())


");


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


$thisYear=$stmt->fetchColumn();





// ==========================
// LAST EXPENSE DATE
// ==========================


$stmt=$pdo->prepare("

SELECT MAX(expense_date)

FROM expenses

WHERE item_id=?

");


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


$lastExpense=$stmt->fetchColumn();







// ==========================
// MONTHLY CHART
// ==========================


$stmt=$pdo->prepare("

SELECT

MONTHNAME(expense_date) month,

SUM(total) amount


FROM expenses


WHERE item_id=?


GROUP BY MONTH(expense_date),
MONTHNAME(expense_date)


ORDER BY MONTH(expense_date)


");


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


$monthly=$stmt->fetchAll();





// ==========================
// CATEGORY CHART
// ==========================


$stmt=$pdo->prepare("

SELECT

COALESCE(c.category_name,'Unknown') category,

SUM(e.total) amount


FROM expenses e


LEFT JOIN expense_categories c

ON e.category_id=c.id


WHERE e.item_id=?


GROUP BY e.category_id


");


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


$categories=$stmt->fetchAll();






// ==========================
// PAYMENT METHOD
// ==========================


$stmt=$pdo->prepare("

SELECT

payment_method,

SUM(total) amount


FROM expenses


WHERE item_id=?


GROUP BY payment_method


");


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


$payments=$stmt->fetchAll();







// ==========================
// VENDOR ANALYSIS
// ==========================


$stmt=$pdo->prepare("

SELECT


COALESCE(v.vendor_name,'Unknown') vendor,


COUNT(e.id) transactions,


SUM(e.total) amount


FROM expenses e


LEFT JOIN vendors v

ON e.vendor_id=v.id


WHERE e.item_id=?


GROUP BY v.id


ORDER BY amount DESC


");


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


$vendors=$stmt->fetchAll();







// ==========================
// EXPENSE HISTORY
// ==========================


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



$sql="

SELECT

e.*,

v.vendor_name,

c.category_name


FROM expenses e


LEFT JOIN vendors v

ON e.vendor_id=v.id


LEFT JOIN expense_categories c

ON e.category_id=c.id


WHERE e.item_id=?


";


$params=[$id];



if($search!=""){


$sql.="

AND (

v.vendor_name LIKE ?

OR e.description LIKE ?

OR c.category_name LIKE ?

)

";


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


}



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



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

$stmt->execute($params);


$expenses=$stmt->fetchAll();





?>
<!DOCTYPE html>

<html>

<head>

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


<title>
<?=htmlspecialchars($item['item_name'])?> Statement
</title>


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


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


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


<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>


<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;
align-items:center;
justify-content:space-between;
margin-bottom:25px;

}



.mobile-btn{

display:none;
font-size:25px;
cursor:pointer;

}



.toggle{

font-size:24px;
cursor:pointer;
color:#059669;

}



.btn{

background:#059669;
color:white;
padding:10px 18px;
border-radius:10px;
text-decoration:none;
display:inline-flex;
align-items:center;
gap:8px;

}




.btn.gray{

background:#64748b;

}




.header-box{

background:white;
padding:25px;
border-radius:18px;
box-shadow:0 5px 20px #0002;
margin-bottom:20px;

}



body.dark .header-box{

background:#1f2937;

}




.header-title{

display:flex;
justify-content:space-between;
align-items:center;

}




.item-info{

display:grid;

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

gap:15px;

margin-top:20px;

}



.info{

background:#ecfdf5;
padding:15px;
border-radius:12px;

}



.info strong{

display:block;

color:#065f46;

}




.cards{


display:grid;

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

gap:15px;

}




.card{


background:white;

padding:18px;

border-radius:15px;

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


}



body.dark .card{

background:#1f2937;

}



.icon{


width:45px;

height:45px;

border-radius:12px;

background:#ecfdf5;

color:#059669;

display:flex;

align-items:center;

justify-content:center;

font-size:20px;

}



.number{

font-size:22px;

font-weight:bold;

color:#059669;

margin-top:10px;

}





.chart-grid{


display:grid;

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


gap:20px;

margin-top:20px;


}




.box{

background:white;

padding:20px;

border-radius:18px;

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


}



body.dark .box{

background:#1f2937;

}





.vendor-card{


background:#ecfdf5;

padding:15px;

border-radius:12px;

margin-top:10px;


}



.progress{

height:10px;

background:#ddd;

border-radius:20px;

overflow:hidden;

margin-top:10px;

}



.progress div{

height:100%;

background:#059669;

}




@media(max-width:768px){


.content{

margin-left:0;
padding:15px;

}



.mobile-btn{

display:block;

}



.header-title{

display:block;

}



.cards{

grid-template-columns:repeat(2,1fr);

}



.chart-grid{

grid-template-columns:1fr;

}



.sidebar{

left:-260px;

}



.sidebar.active{

left:0;

}


}



</style>


</head>


<body>



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



<div class="content">



<div class="topbar">


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


<h2>

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

Item Statement

</h2>



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


</div>





<!-- ITEM HEADER -->


<div class="header-box">


<div class="header-title">


<div>


<h2>

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

<?=htmlspecialchars($item['item_name'])?>

</h2>


<p>

Expense Statement

</p>


</div>



<div>


<a href="javascript:history.back()" class="btn gray">

<i class="fa fa-arrow-left"></i>

Back

</a>


</div>



</div>




<div class="item-info">


<div class="info">

<strong>
Registration
</strong>

<?=htmlspecialchars($item['registration_no'] ?? '-')?>

</div>



<div class="info">

<strong>
Last Expense
</strong>

<?=$lastExpense ?? '-'?>

</div>



<div class="info">

<strong>
Current Year
</strong>

 <?=number_format($thisYear)?>

</div>


</div>


</div>







<!-- KPI CARDS -->


<div class="cards">



<div class="card">

<div class="icon">

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

</div>

<p>Total Spent</p>

<div class="number">

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

</div>

</div>




<div class="card">

<div class="icon">

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

</div>


<p>Transactions</p>


<div class="number">

<?=$summary['records']?>

</div>

</div>





<div class="card">

<div class="icon">

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

</div>


<p>Average</p>


<div class="number">

 <?=number_format($summary['average'])?>

</div>

</div>





<div class="card">

<div class="icon">

<i class="fa fa-arrow-up"></i>

</div>


<p>Highest</p>


<div class="number">

 <?=number_format($summary['highest'])?>

</div>

</div>





<div class="card">

<div class="icon">

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

</div>


<p>This Month</p>


<div class="number">

 <?=number_format($thisMonth)?>

</div>

</div>



</div>







<!-- CHARTS -->


<div class="chart-grid">



<div class="box">

<h3>
Monthly Spending
</h3>

<br>

<canvas id="monthlyChart"></canvas>


</div>





<div class="box">


<h3>
Category Spending
</h3>


<br>

<canvas id="categoryChart"></canvas>


</div>





<div class="box">


<h3>
Payment Methods
</h3>


<br>

<canvas id="paymentChart"></canvas>


</div>




</div>
<!-- VENDOR ANALYSIS -->

<div class="box">

<h3>

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

Vendor Analysis

</h3>


<br>


<?php 

$grandTotal=$summary['total'] ?: 1;


foreach($vendors as $v){


$percentage=($v['amount']/$grandTotal)*100;

?>



<div class="vendor-card">


<h4>

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

<?=htmlspecialchars($v['vendor'])?>

</h4>


<p>

Transactions:

<strong>
<?=$v['transactions']?>
</strong>

</p>


<p>

Amount:

<strong>

 <?=number_format($v['amount'])?>

</strong>

</p>



<div class="progress">

<div style="width:<?=$percentage?>%"></div>

</div>



<small>

<?=number_format($percentage,1)?>%

of total spending

</small>



</div>


<?php } ?>


</div>









<!-- EXPENSE HISTORY -->


<div class="box">


<div style="
display:flex;
justify-content:space-between;
align-items:center;
flex-wrap:wrap;
gap:10px;
">


<h3>

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

Expense History

</h3>



<div>


<a class="btn"

href="export_expenses_pdf.php?item=<?=$id?>"

target="_blank">

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

PDF

</a>



<a class="btn"

href="export_expenses_excel.php?item=<?=$id?>"

target="_blank">

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

Excel

</a>




<a class="btn"

target="_blank"

href="print_item_statement.php?id=<?=$id?>">

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

Print

</a>



</div>


</div>




<br>



<form>


<input 

type="hidden"

name="id"

value="<?=$id?>"



>



<input

style="
padding:12px;
width:100%;
border-radius:10px;
border:1px solid #ddd;
"

name="search"

placeholder="Search vendor, category or description..."

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

>



<br><br>


<button class="btn">

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

Search

</button>


</form>






<br>



<div style="overflow:auto">


<table>


<thead>

<tr>

<th>Date</th>

<th>Category</th>

<th>Vendor</th>

<th>Description</th>

<th>Payment</th>

<th>Amount</th>

<th>Receipt</th>

</tr>

</thead>



<tbody>



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



<tr>


<td>

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

</td>



<td>

<?=htmlspecialchars($e['category_name'] ?? 'Unknown')?>

</td>




<td>

<?=htmlspecialchars($e['vendor_name'] ?? 'Unknown')?>

</td>




<td>

<?=htmlspecialchars($e['description'])?>

</td>




<td>

<span class="badge">

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

</span>

</td>




<td>

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

</td>




<td>


<?php if(!empty($e['attachment'])){ ?>


<a target="_blank"

href="<?=$e['attachment']?>"

class="btn">

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

View

</a>



<?php }else{ ?>


-

<?php } ?>


</td>




</tr>



<?php } ?>



</tbody>


</table>


</div>


</div>







</div>








<script>


// ========================
// MOBILE MENU
// ========================


function openMenu(){

document.getElementById("sidebar")
.classList.add("active");

}



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");

}







// ========================
// CHARTS
// ========================



new Chart(

document.getElementById("monthlyChart"),

{

type:"line",

data:{


labels:

<?=json_encode(array_column($monthly,'month'))?>,


datasets:[{


label:"Expenses",


data:

<?=json_encode(array_column($monthly,'amount'))?>


}]


}


}

);







new Chart(

document.getElementById("categoryChart"),

{

type:"doughnut",

data:{


labels:

<?=json_encode(array_column($categories,'category'))?>,


datasets:[{


data:

<?=json_encode(array_column($categories,'amount'))?>


}]


}


}

);








new Chart(

document.getElementById("paymentChart"),

{

type:"pie",

data:{


labels:

<?=json_encode(array_column($payments,'payment_method'))?>,


datasets:[{


data:

<?=json_encode(array_column($payments,'amount'))?>


}]


}


}

);




</script>



</body>

</html>