<?php

session_start();


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

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

}


require_once "config.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_date,


expense_categories.category_name,


vendors.vendor_name,


expense_items.item_name,


expense_items.registration_no,


expenses.description,


expenses.total,


expenses.payment_method,


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


$data=$stmt->fetchAll();





// EXCEL DOWNLOAD HEADER


header("Content-Type: text/csv; charset=utf-8");

header(
"Content-Disposition: attachment; filename=expenses_report_".date('Y-m-d').".csv"
);




// OPEN OUTPUT


$output=fopen("php://output","w");





// TITLE


fputcsv($output,[

"EXPENSE REPORT"

]);



fputcsv($output,[

"Generated Date",

date("Y-m-d H:i:s")

]);



fputcsv($output,[]);





// HEADINGS


fputcsv($output,[

"Date",

"Category",

"Vendor",

"Expense Item",

"Registration No",

"Description",

"Amount",

"Payment Method",

"Created By"

]);





$total=0;



// DATA


foreach($data as $row){


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



fputcsv($output,[


$row['expense_date'],


$row['category_name'],


$row['vendor_name'] ?? 'N/A',


$row['item_name'] ?? 'N/A',


$row['registration_no'] ?? '',


$row['description'],


$row['total'],


$row['payment_method'],


$row['fullname']


]);


}






// TOTAL ROW


fputcsv($output,[]);



fputcsv($output,[

"",

"",

"",

"",

"",

"TOTAL EXPENSE",

$total

]);





fclose($output);


exit;

?>