<?php

session_start();

if(!isset($_SESSION['user_id'])){
    header("Location: login.php");
    exit;
}

require_once "config.php";

require_once "tcpdf-main/tcpdf.php";


// FILTERS

$start=$_GET['start'] ?? '';
$end=$_GET['end'] ?? '';
$category=$_GET['category'] ?? '';
$vendor=$_GET['vendor'] ?? '';
$item=$_GET['item'] ?? '';
$payment=$_GET['payment'] ?? '';
$search=$_GET['search'] ?? '';



$where=[];
$params=[];



if($start!=""){
    $where[]="expenses.expense_date >= ?";
    $params[]=$start;
}


if($end!=""){
    $where[]="expenses.expense_date <= ?";
    $params[]=$end;
}


if($category!=""){
    $where[]="expenses.category_id=?";
    $params[]=$category;
}


if($vendor!=""){
    $where[]="expenses.vendor_id=?";
    $params[]=$vendor;
}


if($item!=""){
    $where[]="expenses.item_id=?";
    $params[]=$item;
}


if($payment!=""){
    $where[]="expenses.payment_method=?";
    $params[]=$payment;
}



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

}





$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();




// TOTALS

$total=0;

foreach($expenses as $e){

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

}


$count=count($expenses);




// CATEGORY SUMMARY

$summary=[];


foreach($expenses as $e){

$cat=$e['category_name'] ?? 'Unknown';

if(!isset($summary[$cat])){

$summary[$cat]=0;

}

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

}






// PDF SETTINGS


$pdf=new TCPDF(
'L',
'mm',
'A4',
true,
'UTF-8',
false
);



$pdf->SetCreator("Expense Management System");

$pdf->SetAuthor("Expense System");

$pdf->SetTitle("Expense Statement");



$pdf->SetMargins(
8,
10,
8
);


$pdf->SetAutoPageBreak(
TRUE,
15
);



$pdf->AddPage();





// HEADER


$html='

<table width="100%" cellpadding="5">

<tr>


<td width="70%">

<h1 style="color:#065f46">

EXPENSE STATEMENT

</h1>


<p>

Expense Management System

<br>

Generated:
'.date("d M Y H:i").'

</p>


</td>



<td width="30%" align="right">

<b>

REPORT SUMMARY

</b>

<br>

Records: '.$count.'

<br>

Total:

<br>

<b>

MWK '.number_format($total).'

</b>


</td>


</tr>


</table>


<hr>


';





// SUMMARY BY CATEGORY


$html.='

<h3 style="color:#065f46">

Category Summary

</h3>



<table border="1" cellpadding="5">


<tr bgcolor="#065f46" color="white">

<th>

Category

</th>


<th>

Amount

</th>


</tr>

';



foreach($summary as $cat=>$amount){


$html.='

<tr>

<td>

'.$cat.'

</td>


<td>

MWK '.number_format($amount).'

</td>

</tr>

';


}



$html.='

</table>


<br><br>


';






// DETAIL TABLE


$html.='


<h3 style="color:#065f46">

Expense Details

</h3>


<table border="1" cellpadding="4">


<tr bgcolor="#065f46" color="white">


<th width="8%">

Date

</th>


<th width="14%">

Category

</th>


<th width="14%">

Vendor

</th>


<th width="12%">

Item

</th>


<th width="10%">

Reg No

</th>


<th width="20%">

Description

</th>


<th width="10%">

Amount

</th>


<th width="12%">

Payment

</th>


</tr>



';



$row=0;


foreach($expenses as $e){


$bg=($row%2==0)
?'#f3f4f6'
:'#ffffff';



$html.='


<tr bgcolor="'.$bg.'">


<td>

'.$e['expense_date'].'

</td>


<td>

'.($e['category_name'] ?? '').'

</td>


<td>

'.($e['vendor_name'] ?? 'N/A').'

</td>


<td>

'.($e['item_name'] ?? 'N/A').'

</td>



<td>

'.($e['registration_no'] ?? '').'

</td>


<td>

'.htmlspecialchars($e['description']).'

</td>



<td>

MWK '.number_format($e['total']).'

</td>



<td>

'.$e['payment_method'].'

</td>



</tr>


';


$row++;

}





$html.='



<tr bgcolor="#dcfce7">


<td colspan="6" align="right">

<b>

TOTAL EXPENSE

</b>

</td>


<td colspan="2">

<b>

MWK '.number_format($total).'

</b>

</td>


</tr>



</table>



';







$pdf->writeHTML(
$html,
true,
false,
true,
false,
''
);






// FOOTER


$pdf->SetY(-12);


$pdf->SetFont(
'helvetica',
'',
8
);



$pdf->Cell(
0,
10,
'Expense Report | Page '.$pdf->getAliasNumPage().' of '.$pdf->getAliasNbPages(),
0,
0,
'C'
);





$pdf->Output(

'expense_statement_'.date('Y-m-d').'.pdf',

'I'

);


?>