<?php
session_start();
require_once '../config.php';

if (!isset($_SESSION['user_id'])) {
    exit("Unauthorized Access");
}

$user_id = $_SESSION['user_id'];

/* GET USER COUNTRY */

$stmt = $conn->prepare("SELECT country FROM users WHERE id=? LIMIT 1");
$stmt->bind_param("i",$user_id);
$stmt->execute();
$res = $stmt->get_result();

$user = $res->fetch_assoc();
$user_country = $user['country'] ?? '';

$stmt->close();

/* FIND COUNTRY ID */

$country_id = 0;

$stmt = $conn->prepare("SELECT id FROM countries WHERE country_name LIKE CONCAT('%', ?, '%') LIMIT 1");
$stmt->bind_param("s",$user_country);
$stmt->execute();

$res = $stmt->get_result();

if($res && $res->num_rows > 0){
    $row = $res->fetch_assoc();
    $country_id = $row['id'];
}

$stmt->close();

/* GET PRODUCTS */

$stmt = $conn->prepare("
SELECT p.*, c.country_name
FROM products p
LEFT JOIN countries c ON c.id = p.country_id
WHERE p.country_id = ?
ORDER BY p.id DESC
");

$stmt->bind_param("i",$country_id);
$stmt->execute();

$result = $stmt->get_result();

/* DOWNLOAD EXCEL */

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=products_export.xls");

echo "<table border='1'>";

/* HEADER */

echo "<tr style='background:#2E75B6;color:#ffffff;font-weight:bold'>";

$headers = [
'ID',
'Product Name',
'Generic Name',
'Brand Name',
'Standards',
'Strength',
'Volume',
'Dosage Form',
'Indication',
'Primary Packing',
'Secondary Packing',
'Shelf Life',
'Manufactured By',
'Marketed By',
'Dossier Number',
'Submitted To',
'Submitted On',
'Registration Number',
'Registration Date',
'Registration Expiry',
'Renewal Start',
'Country'
];

foreach($headers as $h){
echo "<th>$h</th>";
}

echo "</tr>";

/* DATA */

while($row = $result->fetch_assoc()){

echo "<tr>";

echo "<td>".$row['id']."</td>";
echo "<td>".$row['product_name']."</td>";
echo "<td>".$row['generic_name']."</td>";
echo "<td>".$row['brand_name']."</td>";
echo "<td>".$row['standards']."</td>";
echo "<td>".$row['strength']."</td>";
echo "<td>".$row['volume']."</td>";
echo "<td>".$row['dosage_form']."</td>";
echo "<td>".$row['indication']."</td>";
echo "<td>".$row['primary_packing']."</td>";
echo "<td>".$row['secondary_packing']."</td>";
echo "<td>".$row['shelf_life']."</td>";
echo "<td>".$row['manufactured_by']."</td>";
echo "<td>".$row['marketed_by']."</td>";
echo "<td>".$row['dossier_number']."</td>";
echo "<td>".$row['submitted_to']."</td>";
echo "<td>".$row['submitted_on']."</td>";
echo "<td>".$row['registration_number']."</td>";
echo "<td>".$row['registration_date']."</td>";
echo "<td>".$row['registration_exp_date']."</td>";
echo "<td>".$row['renewal_process_start']."</td>";
echo "<td>".$row['country_name']."</td>";

echo "</tr>";

}

echo "</table>";
exit;
?>