<?php
ob_clean();
include("../../startup.php");

echo $sql = "SELECT 
    piim.id AS mapping_id,
    piim.po_invoices_id,
    piim.purchase_order_mapping_id,
    piim.purchase_order_id,
    piim.status,
    piim.stock_ref,
    piim.supplier_ref,
    piim.description,
    piim.vat_value,
    piim.quantity,
    piim.unit_price,
    piim.total,
    piim.nominal_code,

    -- Purchase Order Details
    po.order_ref,
    po.order_status,
    po.deliver_to,
    po.order_notes,

    -- Supplier Details
    sm.id AS supplier_id,
    sm.supplier_name,
    sm.supplier_code,
    sm.delivery_lead_times,
    sm.contact_1_first_name,
    sm.contact_1_surname,
    sm.contact_1_email_address,
    sm.contact_1_phone_no,

    -- Invoice Details
    pi.invoice_number,
    pi.purchase_order_id AS invoice_purchase_order_id,
    pi.invoice_date,
	pi.due_date,
    pi.total_inc_vat,
    pi.discrepancy,
    pi.discrepancy_details

FROM po_invoices_items_mapping piim

-- First join Invoice
LEFT JOIN po_invoices pi 
    ON pi.id = piim.po_invoices_id

-- Then join Purchase Order
LEFT JOIN purchase_order po 
    ON po.id = pi.purchase_order_id

-- Then join Supplier
LEFT JOIN supplier_management sm 
    ON sm.id = po.supplier_id;";

$rs = mysqli_query($conn, $sql);

// CSV Headers - Xero Purchase Invoice format
$data  = "\"*ContactName\",\"EmailAddress\",\"POAddressLine1\",\"POAddressLine2\",\"POAddressLine3\",\"POAddressLine4\",";
$data .= "\"POCity\",\"PORegion\",\"POPostalCode\",\"POCountry\",";
$data .= "\"*InvoiceNumber\",\"*InvoiceDate\",\"*DueDate\",\"Total\",";
$data .= "\"InventoryItemCode\",\"Description\",\"*Quantity\",\"*UnitAmount\",";
$data .= "\"*AccountCode\",\"*TaxType\",\"TaxAmount\",";
$data .= "\"TrackingName1\",\"TrackingOption1\",\"TrackingName2\",\"TrackingOption2\",";
$data .= "\"Currency\"\n";

while ($row = mysqli_fetch_assoc($rs)) {
    $row = utility::validate_crypt_data($row);

    // Supplier / Contact Info
    $contact_name   = $row['supplier_name'];
    $email_address  = $row['contact_1_email_address'];

    // Supplier Address
    $po_line1       = $row['address_1'];
    $po_line2       = $row['address_2'];
    $po_line3       = $row['address_3'];
    $po_line4       = "";
    $po_city        = "";
    $po_region      = "";
    $po_postcode    = $row['supplier_postcode'];
    $po_country     = "";

    // Invoice Info
    $invoice_number = $row['invoice_number'];
    $invoice_date   = utility::getFormatedDateReturn($row['invoice_date']);
    $due_date       = utility::getFormatedDateReturn($row['due_date']);
   //$due_date       = "";

    // Line Item Info
    $description    = $row['description'];
    $quantity       = $row['quantity'];
    $unit_price     = $row['unit_price'];
    $total          = $row['total'];
    $inventory_code = "";  // InventoryItemCode

    // Supplier Codes & Tax
    $account_code   = $row['supplier_code'];
    $tax_type       = $gsi->getTaxCodeListById($row['tax_code']);
    $tax_amount     = $row['vat_value'];
    $currency       = "";

    // Empty / Static fields
    $tracking1_name = $tracking1_opt = $tracking2_name = $tracking2_opt = "";

    $data .= "\"$contact_name\",\"$email_address\",";
    $data .= "\"$po_line1\",\"$po_line2\",\"$po_line3\",\"$po_line4\",";
    $data .= "\"$po_city\",\"$po_region\",\"$po_postcode\",\"$po_country\",";
    $data .= "\"$invoice_number\",\"$invoice_date\",\"$due_date\",\"$total\",";
    $data .= "\"$inventory_code\",\"$description\",\"$quantity\",\"$unit_price\",";
    $data .= "\"$account_code\",\"$tax_type\",\"$tax_amount\",";
    $data .= "\"$tracking1_name\",\"$tracking1_opt\",\"$tracking2_name\",\"$tracking2_opt\",";
    $data .= "\"$currency\"\n";
}

// Output CSV
$file_name = "export_purchase_invoice_xero.csv";

ob_clean();
header("Content-Type: text/csv; charset=UTF-8");
header("Content-Disposition: attachment; filename=\"$file_name\"");
header("Pragma: no-cache");
header("Expires: 0");
echo $data;
exit();
?>