<?php
// Checked for PHP5.6 / 8.x compatibility
include("../../operainfo/getq3.php");

$hostcompanyid = safetext2(isset($_GET['hostcompanyid']) ? $_GET['hostcompanyid'] : '');

// Initialise to prevent undefined warnings
$wip                       = '';
$Iaccount                  = array();
$companyidoperaaccountis   = array();
$companyoperafromid        = array();
$compname                  = array();
$compAddress1              = array();
$compAddress2              = array();
$compAddress3              = array();
$compCity                  = array();
$compPostCode              = array();
$compCountry               = array();
$Opera_Region              = array();
$vatexempt                 = array();
$XeroInvoiceCompanyName    = array();
$XeroInvoiceContactEmail   = array();
$email                     = array();
$tax                       = array();
$currency                  = array();
$customerref               = array();
$ordercompanyid            = array();
$invoicecompanyid          = array();
$nominalcode               = array();
$stkdesc                   = array();
$companyoperatoid          = array();
$ioperaaddress             = '';
$operaaddress              = '';

// Lets tidy up the credits file to insert pricelist data
$d = "TRUNCATE XERO_SalesInvoiceTemplate";
pdoquery($d);

$s = "SELECT
    viamedquote_orders.id as orderid,
    viamedquote_orders_details.pricelist as pricelist
FROM
    viamedquote_orders_credit_details,
    viamedquote_orders,
    viamedquote_orders_details
WHERE
    viamedquote_orders_credit_details.pricelist = '' AND
    viamedquote_orders_credit_details.orderid = viamedquote_orders.id AND
    viamedquote_orders.id = viamedquote_orders_details.orderid AND
    viamedquote_orders.hostcompany = ?
GROUP BY
    viamedquote_orders_credit_details.orderid, viamedquote_orders_details.pricelist";
$stmt = pdoquery($s, array($hostcompanyid));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    $u = "UPDATE viamedquote_orders_credit_details
          SET pricelist = ?
          WHERE viamedquote_orders_credit_details.orderid = ?";
    pdoquery($u, array($row['pricelist'], $row['orderid']));
    //print "$u<br>";
}

$s = "SELECT
    viamedquote_orders.id as orderid,
    viamedquote_orders.hostcompany as hostcompany
FROM
    viamedquote_orders_credits,
    viamedquote_orders
WHERE
    viamedquote_orders_credits.hostcompanyid = '0' AND
    viamedquote_orders_credits.orderid = viamedquote_orders.id AND
    viamedquote_orders.hostcompany = ?
GROUP BY
    viamedquote_orders_credits.orderid, viamedquote_orders.hostcompany";
$stmt = pdoquery($s, array($hostcompanyid));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    $u = "UPDATE viamedquote_orders_credits
          SET hostcompanyid = ?
          WHERE viamedquote_orders_credits.orderid = ?";
    pdoquery($u, array($row['hostcompany'], $row['orderid']));
    //print "$u<br>";
}
// End of data fixer

// LETS look at preferred addresses
$s = "SELECT ID, KEY5, COMPANY, ADDRESS1, ADDRESS2, ADDRESS3, CITY, ZIP, COUNTRY,
             Opera_Region, vat_exempt, XeroInvoiceCompanyName, XeroInvoiceContactEmail,
             Opera_Stat_account
      FROM viamedquoteaddress
      WHERE hideme = '' OR hideme = 'InvoiceOnly'";
$stmt = pdoquery($s);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    $companyid    = $row['ID'];
    $operaaddress = str_replace(",", " ", isset($row['KEY5']) ? $row['KEY5'] : '');

    $companyidoperaaccountis[$companyid] = $operaaddress;
    $companyoperafromid[$operaaddress]   = $companyid;
    $compname[$companyid]      = str_replace(",", " ", isset($row['COMPANY'])  ? $row['COMPANY']  : '');
    $compAddress1[$companyid]  = str_replace(",", " ", isset($row['ADDRESS1']) ? $row['ADDRESS1'] : '');
    $compAddress2[$companyid]  = str_replace(",", " ", isset($row['ADDRESS2']) ? $row['ADDRESS2'] : '');
    $compAddress3[$companyid]  = str_replace(",", " ", isset($row['ADDRESS3']) ? $row['ADDRESS3'] : '');
    $compCity[$companyid]      = str_replace(",", " ", isset($row['CITY'])     ? $row['CITY']     : '');
    $compPostCode[$companyid]  = str_replace(",", " ", isset($row['ZIP'])      ? $row['ZIP']      : '');
    $compCountry[$companyid]   = str_replace(",", " ", isset($row['COUNTRY'])  ? $row['COUNTRY']  : '');
    $Opera_Region[$companyid]  = str_replace(",", " ", isset($row['Opera_Region']) ? $row['Opera_Region'] : '');
    $vatexempt[$companyid]     = str_replace(",", " ", isset($row['vat_exempt'])   ? $row['vat_exempt']   : '');
    $XeroInvoiceCompanyName[$companyid]  = str_replace(",", " ", isset($row['XeroInvoiceCompanyName'])  ? $row['XeroInvoiceCompanyName']  : '');
    $XeroInvoiceContactEmail[$companyid] = str_replace(",", " ", isset($row['XeroInvoiceContactEmail']) ? $row['XeroInvoiceContactEmail'] : '');
    if (isset($row['Opera_Stat_account']) && $row['Opera_Stat_account'] != '') {
        $Iaccount[$operaaddress] = $row['Opera_Stat_account'];
    }
}

$s = "SELECT COMPANY_ID, KEY5, INETADDR
      FROM viamedquotecontact
      WHERE accountstatements <> ''";
$stmt = pdoquery($s);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    $companyid     = $row['COMPANY_ID'];
    $operaaddress  = isset($row['KEY5']) ? $row['KEY5'] : '';
    $ioperaaddress = isset($Iaccount[$operaaddress]) ? $Iaccount[$operaaddress] : '';
    $email[$operaaddress]  = $row['INETADDR'];
    $email[$ioperaaddress] = $row['INETADDR'];
    $email[$companyid]     = $row['INETADDR'];
}

// ALL SALES THAT HAVE A VALUE in the Vat_Value field
$tax[1] = "20% (VAT on sales)";   // normal sale to the uk

// Region == EX sales or UK Sales and Zero VAT
$tax[2] = "Exempt Sales";          // Normal sales to UK but VAT Exempt or outside Europe

// Region == EC Sales
$tax[3] = "EC Sales Zero VAT";     // ALL EC sales
$tax[3] = "Zero Rated Sales";      // BOOKS - NOT VIAMED REQUIRED

$tax[4] = "Zero Rated Sales";      // BOOKS - NOT VIAMED REQUIRED

$s = "SELECT pricelist, currency
      FROM global_companys_bankaccounts
      WHERE companyid = ?";
$stmt = pdoquery($s, array($hostcompanyid));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    $pricelist = $row['pricelist'];
    $currency[$pricelist] = $row['currency'];
}

// Pull invoice header data for invoices not yet committed to Xero
$s = "SELECT
    viamedquote_orders_invoices.orderid,
    viamedquote_orders.company_id,
    viamedquote_orders.customer_ref,
    viamedquote_orders.invoice_company_id
FROM
    viamedquote_orders,
    viamedquote_orders_invoices
WHERE
    viamedquote_orders_invoices.orderid = viamedquote_orders.id AND
    viamedquote_orders_invoices.xero_commitedon = '0' AND
    viamedquote_orders_invoices.invoicecommitedon > '0' AND
    viamedquote_orders_invoices.hostcompanyid = ?";
$stmt = pdoquery($s, array($hostcompanyid));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    $orderid = $row['orderid'];
    $customerref[$orderid]      = $row['customer_ref'];
    $ordercompanyid[$orderid]   = $row['company_id'];
    $invoicecompanyid[$orderid] = $row['invoice_company_id'];
}

// Pull credit header data for credits not yet committed to Xero
$s = "SELECT
    viamedquote_orders_credits.orderid,
    viamedquote_orders.company_id,
    viamedquote_orders.customer_ref,
    viamedquote_orders.invoice_company_id
FROM
    viamedquote_orders,
    viamedquote_orders_credits
WHERE
    viamedquote_orders_credits.orderid = viamedquote_orders.id AND
    viamedquote_orders_credits.xero_commitedon = '0' AND
    viamedquote_orders_credits.committedon > '0' AND
    viamedquote_orders_credits.hostcompanyid = ?";
$stmt = pdoquery($s, array($hostcompanyid));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$num  = count($rows);
foreach ($rows as $row) {
    $orderid = $row['orderid'];
    $customerref[$orderid]      = $row['customer_ref'];
    $ordercompanyid[$orderid]   = $row['company_id'];
    $invoicecompanyid[$orderid] = $row['invoice_company_id'];
}

$wip .= "$num Invoice to import<br>";

// Stocklist tariffs (nominal codes / descriptions)
$s = "SELECT stock_ref, nominalcode, label_description
      FROM stocklist_tarriffs
      WHERE hostcompany_id = ?";
$stmt = pdoquery($s, array($hostcompanyid));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    $stkref = $row['stock_ref'];
    $nominalcode[$stkref] = $row['nominalcode'];
    $stkdesc[$stkref]     = str_replace(",", "", isset($row['label_description']) ? $row['label_description'] : '');
}


//** Normal Order Details Go In Here **
$s = "SELECT
    viamedquote_orders_invoices.orderid,
    viamedquote_orders_invoices.goodstotal as invoicetotal,
    viamedquote_orders_invoices.vattotal as vattotal,
    viamedquote_orders_invoices.invoicecommitedon,
    viamedquote_orders_invoices_details.pricelist,
    viamedquote_orders_invoices_details.invoiceref,
    viamedquote_orders_invoices_details.stockref,
    viamedquote_orders_invoices_details.goodstotal,
    count(viamedquote_orders_invoices_details.stockref) as qty
FROM
    viamedquote_orders_invoices_details,
    viamedquote_orders_invoices
WHERE
    viamedquote_orders_invoices.orderid = viamedquote_orders_invoices_details.orderid AND
    viamedquote_orders_invoices.deliveryid = viamedquote_orders_invoices_details.deliveryid AND
    viamedquote_orders_invoices.xero_commitedon = '0' AND
    viamedquote_orders_invoices.invoicecommitedon > '0' AND
    viamedquote_orders_invoices.hostcompanyid = ? AND
    viamedquote_orders_invoices_details.linetotal > '-0.01' AND
    viamedquote_orders_invoices_details.deliveryid > '0'
GROUP BY
    viamedquote_orders_invoices.orderid,
    viamedquote_orders_invoices_details.invoiceref,
    viamedquote_orders_invoices_details.goodstotal,
    viamedquote_orders_invoices_details.vattotal,
    viamedquote_orders_invoices_details.stockref
ORDER BY
    viamedquote_orders_invoices.orderid,
    viamedquote_orders_invoices_details.paperworkorder";
$stmt = pdoquery($s, array($hostcompanyid));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$num  = count($rows);
$wip .= "$num Invoice Details to import<br>";

foreach ($rows as $row) {
    $orderid     = $row['orderid'];
    $stkref      = $row['stockref'];
    $Invoicedate = date("d/m/Y", $row['invoicecommitedon']);
    $duedate     = date("d/m/Y", ($row['invoicecommitedon'] + (86400 * 30)));
    $pricelist   = $row['pricelist'];
    $goodstotal  = $row['goodstotal'];
    $invoicetotal = $row['invoicetotal'];
    $qty         = $row['qty'];
    $companyid   = isset($ordercompanyid[$orderid]) ? $ordercompanyid[$orderid] : 0;
    $XeroCompanyName = isset($XeroInvoiceCompanyName[$companyid]) ? $XeroInvoiceCompanyName[$companyid] : '';
    $invcompanyid = isset($invoicecompanyid[$orderid]) ? $invoicecompanyid[$orderid] : 0;

    if (!isset($nominalcode[$stkref]) || $nominalcode[$stkref] == '') {
        $nominalcode[$stkref] = '1001';
    }

    // Try to find appropriate person for email contact
    $useemail = isset($XeroInvoiceContactEmail[$companyid]) ? $XeroInvoiceContactEmail[$companyid] : '';

    $invcompanyid = isset($companyoperatoid[$ioperaaddress]) ? $companyoperatoid[$ioperaaddress] : 0;
    if (!isset($compname[$invcompanyid]) || $compname[$invcompanyid] == '') {
        $invcompanyid = isset($companyoperatoid[$operaaddress]) ? $companyoperatoid[$operaaddress] : 0;
    }
    if (!isset($compname[$invcompanyid]) || $compname[$invcompanyid] == '') {
        $invcompanyid = isset($companyoperatoid[$operaaddress]) ? $companyoperatoid[$operaaddress] : 0;
    }
    if (!isset($compname[$invcompanyid]) || $compname[$invcompanyid] == '') {
        $invcompanyid = isset($invoicecompanyid[$orderid]) ? $invoicecompanyid[$orderid] : 0;
    }
    if (!isset($compname[$invcompanyid]) || $compname[$invcompanyid] == '') {
        $invcompanyid = $companyid;
    }

    // options for VATCODE 1-4
    $VATCODE = 0;
    $regCid  = isset($Opera_Region[$companyid]) ? $Opera_Region[$companyid] : '';
    $vexCid  = isset($vatexempt[$companyid])    ? $vatexempt[$companyid]    : '';

    if ($regCid == 'UK  UK sales' || $regCid == 'UKA UK Automotive') {
        $VATCODE = 1;
        if ((isset($row['vattotal']) ? $row['vattotal'] : 0) + 0 == 0 && $invoicetotal > 0) {
            $VATCODE = 4;
        }
    }

    if ($regCid == 'ECX EC Export Sale' && $vexCid == 1) {
        $VATCODE = 3;
        $VATCODE = 4;
    }

    if ($regCid == 'ECX EC Export Sale' && $vexCid != 1) {
        $VATCODE = 1;
    }

    if ($regCid == 'EX  Export sales') {
        $VATCODE = 4;
    }

    if ($qty == 0) {
        $qty = 1;
    }

    $taxType  = isset($tax[$VATCODE]) ? $tax[$VATCODE] : '';
    $curr     = isset($currency[$pricelist]) ? $currency[$pricelist] : '';
    $stkDescV = isset($stkdesc[$stkref]) ? $stkdesc[$stkref] : '';
    $nomCode  = isset($nominalcode[$stkref]) ? $nominalcode[$stkref] : '';
    $a1       = isset($compAddress1[$invcompanyid]) ? $compAddress1[$invcompanyid] : '';
    $a2       = isset($compAddress2[$invcompanyid]) ? $compAddress2[$invcompanyid] : '';
    $a3       = isset($compAddress3[$invcompanyid]) ? $compAddress3[$invcompanyid] : '';
    $cty      = isset($compCity[$invcompanyid])     ? $compCity[$invcompanyid]     : '';
    $pcd      = isset($compPostCode[$invcompanyid]) ? $compPostCode[$invcompanyid] : '';
    $cou      = isset($compCountry[$companyid])     ? $compCountry[$companyid]     : '';
    $cust     = isset($customerref[$orderid])       ? $customerref[$orderid]       : '';

    // The Core Line
    $i = "INSERT INTO XERO_SalesInvoiceTemplate
        (ContactName, EmailAddress, InvoiceNumber, Reference, InvoiceDate,
         DueDate, Total, InventoryItemCode, Description, Quantity,
         UnitAmount, Currency, TaxType, AccountCode, TaxAmount,
         POAddressLine1, POAddressLine2, POAddressLine3, POAddressLine4, POCity,
         POPostalCode, POCountry)
         VALUES
         (?, ?, ?, ?, ?,
          ?, '', ?, ?, ?, ?,
          ?, ?, ?, '',
          ?, ?, ?, '', ?,
          ?, ?)";
    pdoquery($i, array(
        $XeroCompanyName, $useemail, $row['invoiceref'], $cust, $Invoicedate,
        $duedate, $stkref, $stkDescV, $qty, $goodstotal,
        $curr, $taxType, $nomCode,
        $a1, $a2, $a3, $cty,
        $pcd, $cou
    ));
    //print "$i<br>";
}
//** End of normal orders entry **


//** Start of normal Credits entry **
$s = "SELECT
    viamedquote_orders_credits.orderid,
    viamedquote_orders_credits.invoicetotal as invoicetotal,
    viamedquote_orders_credits.committedon,
    viamedquote_orders_credit_details.pricelist,
    viamedquote_orders_credits.creditreference,
    viamedquote_orders_credit_details.stockref,
    viamedquote_orders_credit_details.goodstotal,
    count(viamedquote_orders_credit_details.stockref) as qty
FROM
    viamedquote_orders_credit_details,
    viamedquote_orders_credits
WHERE
    viamedquote_orders_credits.orderid = viamedquote_orders_credit_details.orderid AND
    viamedquote_orders_credits.deliveryid = viamedquote_orders_credit_details.deliveryid AND
    viamedquote_orders_credits.xero_commitedon = '0' AND
    viamedquote_orders_credits.committedon > '0' AND
    viamedquote_orders_credits.hostcompanyid = ?
GROUP BY
    viamedquote_orders_credits.orderid,
    viamedquote_orders_credits.creditreference,
    viamedquote_orders_credit_details.goodstotal,
    viamedquote_orders_credit_details.vattotal,
    viamedquote_orders_credit_details.stockref
ORDER BY
    viamedquote_orders_credits.orderid,
    viamedquote_orders_credit_details.paperworkorder";
$stmt = pdoquery($s, array($hostcompanyid));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$num  = count($rows);
$wip .= "$num Credit Details to import<br>";

foreach ($rows as $row) {
    $orderid       = $row['orderid'];
    $stkref        = $row['stockref'];
    $Invoicedate   = date("d/m/Y", $row['committedon']);
    $duedate       = date("d/m/Y", ($row['committedon'] + (86400 * 30)));
    $pricelist     = $row['pricelist'];
    $goodstotal    = -$row['goodstotal'];
    $invoicetotal  = -$row['invoicetotal'];
    $qty           = $row['qty'];
    $companyid     = isset($ordercompanyid[$orderid]) ? $ordercompanyid[$orderid] : 0;
    $XeroCompanyName = isset($XeroInvoiceCompanyName[$companyid]) ? $XeroInvoiceCompanyName[$companyid] : '';
    $invcompanyid  = isset($invoicecompanyid[$orderid]) ? $invoicecompanyid[$orderid] : 0;

    if (!isset($nominalcode[$stkref]) || $nominalcode[$stkref] == '') {
        $nominalcode[$stkref] = '1001';
    }

    $useemail = isset($XeroInvoiceContactEmail[$companyid]) ? $XeroInvoiceContactEmail[$companyid] : '';

    $invcompanyid = isset($companyoperatoid[$ioperaaddress]) ? $companyoperatoid[$ioperaaddress] : 0;
    if (!isset($compname[$invcompanyid]) || $compname[$invcompanyid] == '') {
        $invcompanyid = isset($companyoperatoid[$operaaddress]) ? $companyoperatoid[$operaaddress] : 0;
    }
    if (!isset($compname[$invcompanyid]) || $compname[$invcompanyid] == '') {
        $invcompanyid = isset($companyoperatoid[$operaaddress]) ? $companyoperatoid[$operaaddress] : 0;
    }
    if (!isset($compname[$invcompanyid]) || $compname[$invcompanyid] == '') {
        $invcompanyid = isset($invoicecompanyid[$orderid]) ? $invoicecompanyid[$orderid] : 0;
    }
    if (!isset($compname[$invcompanyid]) || $compname[$invcompanyid] == '') {
        $invcompanyid = $companyid;
    }
    if (!isset($compname[$invcompanyid]) || $compname[$invcompanyid] == '') {
        $invcompanyid = isset($ordercompanyid[$orderid]) ? $ordercompanyid[$orderid] : 0;
    }

    // options for VATCODE 1-4
    $VATCODE = 0;
    $regCid  = isset($Opera_Region[$companyid]) ? $Opera_Region[$companyid] : '';
    $vexCid  = isset($vatexempt[$companyid])    ? $vatexempt[$companyid]    : '';

    if ($regCid == 'UK  UK sales' || $regCid == 'UKA UK Automotive') {
        $VATCODE = 1;
        if ((isset($row['vattotal']) ? $row['vattotal'] : 0) + 0 == 0 && $invoicetotal > 0) {
            $VATCODE = 4;
        }
    }

    if ($regCid == 'ECX EC Export Sale' && $vexCid == 1) {
        $VATCODE = 3;
        $VATCODE = 4;
    }

    if ($regCid == 'ECX EC Export Sale' && $vexCid != 1) {
        $VATCODE = 1;
    }

    if ($regCid == 'EX  Export sales') {
        $VATCODE = 4;
    }

    if ($qty == 0) {
        $qty = 1;
    }

    $taxType  = isset($tax[$VATCODE]) ? $tax[$VATCODE] : '';
    $curr     = isset($currency[$pricelist]) ? $currency[$pricelist] : '';
    $stkDescV = isset($stkdesc[$stkref]) ? $stkdesc[$stkref] : '';
    $nomCode  = isset($nominalcode[$stkref]) ? $nominalcode[$stkref] : '';
    $a1       = isset($compAddress1[$invcompanyid]) ? $compAddress1[$invcompanyid] : '';
    $a2       = isset($compAddress2[$invcompanyid]) ? $compAddress2[$invcompanyid] : '';
    $a3       = isset($compAddress3[$invcompanyid]) ? $compAddress3[$invcompanyid] : '';
    $cty      = isset($compCity[$invcompanyid])     ? $compCity[$invcompanyid]     : '';
    $pcd      = isset($compPostCode[$invcompanyid]) ? $compPostCode[$invcompanyid] : '';
    $cou      = isset($compCountry[$companyid])     ? $compCountry[$companyid]     : '';
    $cust     = isset($customerref[$orderid])       ? $customerref[$orderid]       : '';

    // The Core Line
    $i = "INSERT INTO XERO_SalesInvoiceTemplate
        (ContactName, EmailAddress, InvoiceNumber, Reference, InvoiceDate,
         DueDate, Total, InventoryItemCode, Description, Quantity,
         UnitAmount, Currency, TaxType, AccountCode, TaxAmount,
         POAddressLine1, POAddressLine2, POAddressLine3, POAddressLine4, POCity,
         POPostalCode, POCountry)
         VALUES
         (?, ?, ?, ?, ?,
          ?, '', ?, ?, ?, ?,
          ?, ?, ?, '',
          ?, ?, ?, '', ?,
          ?, ?)";
    pdoquery($i, array(
        $XeroCompanyName, $useemail, $row['creditreference'], $cust, $Invoicedate,
        $duedate, $stkref, $stkDescV, $qty, $goodstotal,
        $curr, $taxType, $nomCode,
        $a1, $a2, $a3, $cty,
        $pcd, $cou
    ));
    //print "$i<br>";
}
//** End of normal credits entry **


$u = "UPDATE XERO_SalesInvoiceTemplate SET Quantity = '1' WHERE Quantity = '0' OR Quantity = ''";
pdoquery($u);

$u = "UPDATE XERO_SalesInvoiceTemplate SET UnitAmount = '0' WHERE UnitAmount = ''";
pdoquery($u);

print "$wip";
?>