function GetOnlinePriceList($db)
{
    $SQL = "SELECT debtorsmaster.salestype\r\n\t\t\tFROM debtorsmaster\r\n\t\t\tWHERE debtorsmaster.debtorno = '" . GetWeberpCustomerIdFromCurrency(OPENCART_DEFAULT_CURRENCY, $db) . "'";
    $result = DB_query($SQL);
    if (DB_num_rows($result) != 0) {
        $myrow = DB_fetch_array($result);
        return array($myrow['salestype'], OPENCART_DEFAULT_CURRENCY);
    } else {
        return array(0, 0);
    }
}
function SyncPaypalPaymentInformation($TimeDifference, $ShowMessages, $LastTimeRun, $db, $db_oc, $oc_tableprefix, $EmailText = '')
{
    if ($EmailText != '') {
        $EmailText = $EmailText . "Sync OpenCart Order Information" . "\n" . PrintTimeInformation($db);
    }
    // Now deal with the Paypal payment/s of the order...
    $SQL = "SELECT \t" . $oc_tableprefix . "paypal_order.paypal_order_id,\r\n\t\t\t\t" . $oc_tableprefix . "order.order_id,\r\n\t\t\t\t" . $oc_tableprefix . "order.currency_code AS ordercurrency,\r\n\t\t\t\t" . $oc_tableprefix . "order.currency_value,\r\n\t\t\t\t" . $oc_tableprefix . "order.customer_id,\r\n\t\t\t\t" . $oc_tableprefix . "customer.email,\r\n\t\t\t\t" . $oc_tableprefix . "order.total AS ordertotal,\r\n\t\t\t\t" . $oc_tableprefix . "paypal_order.paypal_order_id,\r\n\t\t\t\t" . $oc_tableprefix . "paypal_order.capture_status,\r\n\t\t\t\t" . $oc_tableprefix . "paypal_order.currency_code AS paypalcurrency,\r\n\t\t\t\t" . $oc_tableprefix . "paypal_order.authorization_id,\r\n\t\t\t\t" . $oc_tableprefix . "paypal_order.total AS paypaltotal,\r\n\t\t\t\t" . $oc_tableprefix . "paypal_order_transaction.transaction_id,\r\n\t\t\t\t" . $oc_tableprefix . "paypal_order_transaction.created,\r\n\t\t\t\t" . $oc_tableprefix . "paypal_order_transaction.payment_status,\r\n\t\t\t\t" . $oc_tableprefix . "paypal_order_transaction.pending_reason,\r\n\t\t\t\t" . $oc_tableprefix . "paypal_order_transaction.transaction_entity,\r\n\t\t\t\t" . $oc_tableprefix . "paypal_order_transaction.amount,\r\n\t\t\t\t" . $oc_tableprefix . "paypal_order_transaction.debug_data,\r\n\t\t\t\t" . $oc_tableprefix . "paypal_order_transaction.call_data\r\n\t\tFROM " . $oc_tableprefix . "paypal_order,\r\n\t\t\t " . $oc_tableprefix . "paypal_order_transaction,\r\n\t\t\t " . $oc_tableprefix . "order,\r\n\t\t\t " . $oc_tableprefix . "customer\r\n\t\tWHERE " . $oc_tableprefix . "paypal_order.paypal_order_id = " . $oc_tableprefix . "paypal_order_transaction.paypal_order_id\r\n\t\t\t\tAND " . $oc_tableprefix . "paypal_order.order_id  = " . $oc_tableprefix . "order.order_id\r\n\t\t\t\tAND " . $oc_tableprefix . "order.customer_id  = " . $oc_tableprefix . "customer.customer_id\r\n\t\t\t\tAND ( " . $oc_tableprefix . "paypal_order.created >= '" . $LastTimeRun . "'\r\n\t\t\t\t\tOR " . $oc_tableprefix . "paypal_order.modified >= '" . $LastTimeRun . "')\r\n\t\tORDER BY " . $oc_tableprefix . "paypal_order.paypal_order_id";
    $result = DB_query_oc($SQL);
    if (DB_num_rows($result) != 0) {
        if ($ShowMessages) {
            echo '<p class="page_title_text" align="center"><strong>' . _('Paypal Payments from OpenCart') . '</strong></p>';
            echo '<div>';
            echo '<table class="selection">';
            $TableHeader = '<tr>
								<th>' . _('CustomerID') . '</th>
								<th>' . _('email') . '</th>
								<th>' . _('webERP Code') . '</th>
								<th>' . _('OrderID') . '</th>
								<th>' . _('webERP #') . '</th>
								<th>' . _('Order Total') . '</th>
								<th>' . _('Order Curr') . '</th>
								<th>' . _('Paypal Total') . '</th>
								<th>' . _('Paypal Curr') . '</th>
								<th>' . _('Paypal Trx') . '</th>
								<th>' . _('Trx Total') . '</th>
								<th>' . _('Commission') . '</th>
								<th>' . _('Date') . '</th>
								<th>' . _('Status') . '</th>
								<th>' . _('Pending reason') . '</th>
							</tr>';
            echo $TableHeader;
        }
        $DbgMsg = _('The SQL statement that failed was');
        $UpdateErrMsg = _('The SQL to update OpenCart Paypal payments in webERP failed');
        $InsertErrMsg = _('The SQL to insert OpenCart Paypal payments in webERP failed');
        $k = 0;
        //row colour counter
        $i = 0;
        while ($myrow = DB_fetch_array($result)) {
            if ($k == 1) {
                echo '<tr class="EvenTableRows">';
                $k = 0;
            } else {
                echo '<tr class="OddTableRows">';
                $k = 1;
            }
            /* FIELD MATCHING */
            $CustomerCode = GetWeberpCustomerIdFromCurrency($myrow['ordercurrency'], $db);
            $OrderNo = GetWeberpOrderNo($CustomerCode, $myrow['order_id'], $db);
            $PaymentSystem = OPENCART_DEFAULT_PAYMENT_SYSTEM;
            $CurrencyOrder = $myrow['ordercurrency'];
            $CurrencyPayment = $myrow['paypalcurrency'];
            $TotalOrder = round($myrow['ordertotal'] * $myrow['currency_value'], 2);
            // from OC default currency to order and payment currency
            $Rate = GetWeberpCurrencyRate($CurrencyOrder, $db);
            $AmountPaid = $myrow['paypaltotal'];
            $TransactionID = $myrow['transaction_id'];
            $GLAccount = GetWeberpGLAccountFromCurrency($CurrencyPayment, $db);
            $GLCommissionAccount = GetWeberpGLCommissionAccountFromCurrency($CurrencyPayment, $db);
            $PayPalResponseArray = GetPaypalReturnDataInArray($myrow['debug_data']);
            $Commission = urldecode($PayPalResponseArray['PAYMENTINFO_0_FEEAMT']);
            $WebERPDateOrder = date('Y-m-d H:i:s', strtotime($myrow['created'] . -$TimeDifference . ' hours'));
            if ($myrow['paypalcurrency'] == $myrow['ordercurrency'] and $myrow['pending_reason'] == 'None') {
                // order currency and Paypal currency are the same
                // AND has been paid OK
                $PaymentOK = true;
            } else {
                prnMsg("HORROR: Currency mess", "warn");
                $PaymentOK = false;
            }
            if ($PaymentOK) {
                $PeriodNo = GetPeriod(Date($_SESSION['DefaultDateFormat']), $db);
                InsertCustomerReceipt($CustomerCode, $AmountPaid, $CurrencyPayment, $Rate, $GLAccount, $PaymentSystem, $TransactionID, $OrderNo, $PeriodNo, $db);
                TransactionCommissionGL($CustomerCode, $GLAccount, $GLCommissionAccount, $Commission, $CurrencyPayment, $Rate, $PaymentSystem, $TransactionID, $PeriodNo, $db);
                ChangeOrderQuotationFlag($OrderNo, 0, $db);
                // it has been paid, so we consider it a firm order
            }
            if ($ShowMessages) {
                printf('<td class="number">%s</td>
						<td>%s</td>
						<td>%s</td>
						<td class="number">%s</td>
						<td class="number">%s</td>
						<td class="number">%s</td>
						<td>%s</td>
						<td class="number">%s</td>
						<td>%s</td>
						<td>%s</td>
						<td class="number">%s</td>
						<td class="number">%s</td>
						<td>%s</td>
						<td>%s</td>
						<td>%s</td>
						</tr>', $myrow['customer_id'], $myrow['email'], $CustomerCode, $myrow['order_id'], $OrderNo, $TotalOrder, $myrow['ordercurrency'], $AmountPaid, $myrow['paypalcurrency'], $TransactionID, $myrow['amount'], $Commission, $WebERPDateOrder, $myrow['payment_status'], $myrow['pending_reason']);
            }
            if ($EmailText != '') {
                $EmailText = $EmailText . $myrow['customer_id'] . " = " . $myrow['email'] . " = " . $CustomerCode . " = " . $myrow['order_id'] . " = " . $TotalOrder . " = " . $myrow['ordercurrency'] . " = " . $AmountPaid . " = " . $myrow['payment_status'] . " --> " . $Action . "\n";
            }
            $i++;
        }
        if ($ShowMessages) {
            echo '</table>
					</div>
					</form>';
        }
    }
    if ($ShowMessages) {
        prnMsg(locale_number_format($i, 0) . ' ' . _('Payments synchronized from OpenCart to webERP'), 'success');
    }
    if ($EmailText != '') {
        $EmailText = $EmailText . locale_number_format($i, 0) . ' ' . _('Payments synchronized from OpenCart to webERP') . "\n\n";
    }
    return $EmailText;
}
function SyncProductPrices($ShowMessages, $LastTimeRun, $db, $db_oc, $oc_tableprefix, $EmailText = '')
{
    if ($EmailText != '') {
        $EmailText = $EmailText . "Product Price Sync" . "\n" . PrintTimeInformation($db);
    }
    /* let's get the webERP price list and base currency for the online customer */
    list($PriceList, $Currency) = GetOnlinePriceList($db);
    /* Look for the late modifications of prices table in webERP */
    $SQL = "SELECT prices.stockid,\r\n\t\t\t\tstockmaster.discountcategory\r\n\t\t\tFROM prices, stockmaster\r\n\t\t\tWHERE prices.stockid = stockmaster.stockid\r\n\t\t\t\tAND prices.typeabbrev ='" . $PriceList . "'\r\n\t\t\t\tAND prices.currabrev ='" . $Currency . "'\r\n\t\t\t\tAND (prices.date_created >= '" . $LastTimeRun . "'\r\n\t\t\t\t\tOR prices.date_updated >= '" . $LastTimeRun . "')\r\n\t\t\tORDER BY prices.stockid";
    $result = DB_query($SQL, $db);
    if (DB_num_rows($result) != 0) {
        if ($ShowMessages) {
            echo '<p class="page_title_text" align="center"><strong>' . _('Product Prices Updates') . '</strong></p>';
            echo '<div>';
            echo '<table class="selection">';
            $TableHeader = '<tr>
								<th>' . _('StockID') . '</th>
								<th>' . _('New Price') . '</th>
								<th>' . _('Discount Category') . '</th>
								<th>' . _('Action') . '</th>
							</tr>';
            echo $TableHeader;
        }
        $DbgMsg = _('The SQL statement that failed was');
        $UpdateErrMsg = _('The SQL to update Product Prices in Opencart failed');
        $k = 0;
        //row colour counter
        $i = 0;
        while ($myrow = DB_fetch_array($result)) {
            $k = StartEvenOrOddRow($k);
            /* Field Matching */
            $Model = $myrow['stockid'];
            $CustomerCode = GetWeberpCustomerIdFromCurrency(OPENCART_DEFAULT_CURRENCY, $db);
            $Price = GetPrice($myrow['stockid'], $CustomerCode, $CustomerCode, $db);
            // Get the price without any discount from webERP
            $ManufacturerId = $myrow['manufacturers_id'];
            $DiscountCategory = $myrow['discountcategory'];
            // Let's get the OpenCart primary key for product
            $ProductId = GetOpenCartProductId($Model, $db_oc, $oc_tableprefix);
            $Action = "Update";
            $sqlUpdate = "UPDATE " . $oc_tableprefix . "product SET\r\n\t\t\t\t\t\t\tprice = '" . $Price . "'\r\n\t\t\t\t\t\tWHERE product_id = '" . $ProductId . "'";
            $resultUpdate = DB_query_oc($sqlUpdate, $UpdateErrMsg, $DbgMsg, true);
            // update discounts if needed
            MaintainOpenCartDiscountForItem($ProductId, $Price, $DiscountCategory, $PriceList, $db, $db_oc, $oc_tableprefix);
            if ($ShowMessages) {
                printf('<td>%s</td>
						<td class="number">%s</td>
						<td>%s</td>
						<td>%s</td>
						</tr>', $Model, locale_number_format($Price, 2), $DiscountCategory, $Action);
            }
            if ($EmailText != '') {
                $EmailText = $EmailText . str_pad($Model, 20, " ") . " = " . locale_number_format($Price, 2) . " = " . $DiscountCategory . " --> " . $Action . "\n";
            }
            $i++;
        }
        if ($ShowMessages) {
            echo '</table>
					</div>
					</form>';
        }
    }
    if ($ShowMessages) {
        prnMsg(locale_number_format($i, 0) . ' ' . _('Product Prices synchronized from webERP to OpenCart'), 'success');
    }
    if ($EmailText != '') {
        $EmailText = $EmailText . locale_number_format($i, 0) . ' ' . _('Product Prices synchronized from webERP to OpenCart') . "\n\n";
    }
    return $EmailText;
}