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 SyncRelatedItems($ShowMessages, $LastTimeRun, $db, $db_oc, $oc_tableprefix, $EmailText = '')
{
if ($EmailText != '') {
$EmailText = $EmailText . "Sync Related Items" . "\n" . PrintTimeInformation($db);
}
$SQL = "SELECT stockid,\r\n\t\t\t\trelated\r\n\t\t\tFROM relateditems\r\n\t\t\tWHERE date_created >= '" . $LastTimeRun . "'\r\n\t\t\t\tOR date_updated >= '" . $LastTimeRun . "'\r\n\t\t\tORDER BY stockid, related";
$result = DB_query($SQL, $db);
if (DB_num_rows($result) != 0) {
if ($ShowMessages) {
echo '<p class="page_title_text" align="center"><strong>' . _('Related Items') . '</strong></p>';
echo '<div>';
echo '<table class="selection">';
$TableHeader = '<tr>
<th>' . _('Item webERP') . '</th>
<th>' . _('Related webERP') . '</th>
<th>' . _('Item OC') . '</th>
<th>' . _('Related OC') . '</th>
<th>' . _('Action') . '</th>
</tr>';
echo $TableHeader;
}
$DbgMsg = _('The SQL statement that failed was');
$UpdateErrMsg = _('The SQL to update related items in Opencart failed');
$InsertErrMsg = _('The SQL to insert related items in Opencart failed');
$k = 0;
//row colour counter
$i = 0;
while ($myrow = DB_fetch_array($result)) {
$k = StartEvenOrOddRow($k);
/* FIELD MATCHING */
$ProductId = GetOpenCartProductId($myrow['stockid'], $db_oc, $oc_tableprefix);
$RelatedId = GetOpenCartProductId($myrow['related'], $db_oc, $oc_tableprefix);
if (DataExistsInOpenCart($db_oc, $oc_tableprefix . 'product_related', 'product_id', $ProductId, 'related_id', $RelatedId)) {
$Action = "Update";
} else {
$Action = "Insert";
$sqlInsert = "INSERT INTO " . $oc_tableprefix . "product_related\r\n\t\t\t\t\t\t\t\t(product_id,\r\n\t\t\t\t\t\t\t\trelated_id)\r\n\t\t\t\t\t\t\tVALUES\r\n\t\t\t\t\t\t\t\t('" . $ProductId . "',\r\n\t\t\t\t\t\t\t\t'" . $RelatedId . "'\r\n\t\t\t\t\t\t\t\t)";
$resultInsert = DB_query_oc($sqlInsert, $InsertErrMsg, $DbgMsg, true);
}
if ($ShowMessages) {
printf('<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
</tr>', $myrow['stockid'], $myrow['related'], $ProductId, $RelatedId, $Action);
}
$i++;
}
if ($ShowMessages) {
echo '</table>
</div>
</form>';
}
}
if ($ShowMessages) {
prnMsg(locale_number_format($i, 0) . ' ' . _('Pairs of related items synchronized from webERP to OpenCart'), 'success');
}
if ($EmailText != '') {
$EmailText = $EmailText . locale_number_format($i, 0) . ' ' . _('Pairs of related items synchronized from webERP to OpenCart') . "\n\n";
}
return $EmailText;
}
