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;
}