function manageLogisticList($rowperpage, $page)
{
$result = '';
$result .= '<div>';
$result .= '<input type="button" id="newlogpack" value="Create New" title="Create New Logistic Package" />';
$result .= '<a href="?open=packages-logistic-detail&hidemenu=true" class="view_webpage"></a>';
$result .= '</div>';
$result .= '<div> </div>';
$count_logistic_query = tep_db_query("SELECT COUNT(logistic_id) AS total FROM minierp_logistic");
$count_logistic = tep_db_fetch_array($count_logistic_query);
$offset = ($page - 1) * $rowperpage;
$paging_filter = "LIMIT {$rowperpage} OFFSET {$offset}";
$lq = "SELECT ml.*, SUM(products_total_count) AS total_qty";
$lq .= " FROM minierp_logistic ml";
$lq .= " LEFT JOIN minierp_logistic_contents mlc ON mlc.logistic_id=ml.logistic_id";
$lq .= " GROUP BY logistic_id";
$lq .= " ORDER BY logistic_date DESC";
$lq .= " {$paging_filter}";
$logistic_query = tep_db_query($lq);
$logistic = array();
while ($row = tep_db_fetch_array($logistic_query)) {
$logistic[] = $row;
}
if (count($logistic) > 0) {
$pagelink = tep_paging($count_logistic['total'], $page, '?open=packages-logistic-list&page=');
$log_table = array();
$lt = array();
$lt['pac_code'] = 'Shipper Code';
$lt['long_id'] = 'Invoice No';
$lt['date'] = 'Sent Date';
$lt['qty'] = 'Qty';
$lt['countpro'] = 'Weight';
$lt['price'] = 'Price';
$lt['awb_no'] = 'AWB No';
$log_table[] = $lt;
foreach ($logistic as $l) {
$log_date = strtotime($l['logistic_date']);
$sent_date = $l['sent_date'] == '' ? ' ' : date('d-M-Y', strtotime($l['sent_date']));
$lt = array();
$lt['pac_code'] = '<a href="?open=packages-logistic-detail&id=' . $l['logistic_id'] . '&hidemenu=true" class="view_webpage" title="View Logistic Detail">' . $l['logistic_code'] . '</a>';
$lt['long_id'] = $l['ptb_no'] != '' ? date('ym', $log_date) . '-' . $l['ptb_no'] : ' ';
$lt['date'] = date('j-M-y', $log_date);
$lt['qty'] = $l['total_qty'];
$lt['countpro'] = $l['logistic_weight'] . ' Kg';
$lt['price'] = displayCurrency($l['currency'], $l['logistic_price']);
$lt['awb_no'] = $this->awbCodeLink($l['awb_no']);
$log_table[] = $lt;
}
$result .= tep_draw_table('packages', $log_table);
$result .= $pagelink;
} else {
$result .= '<h3 class="red">No Logistic Packages is found in database</h3>';
}
return $result;
}
function managePackageList($rowperpage, $page)
{
$count_packages_query = tep_db_query("SELECT COUNT(packages_id) AS total FROM minierp_packages");
$count_packages = tep_db_fetch_array($count_packages_query);
$offset = ($page - 1) * $rowperpage;
$paging_filter = "LIMIT {$rowperpage} OFFSET {$offset}";
$packages_query = tep_db_query("SELECT * FROM minierp_packages {$paging_filter}");
$packages = array();
while ($row = tep_db_fetch_array($packages_query)) {
$packages[] = $row;
}
$pagelink = tep_paging($count_packages['total'], $page, '?open=sp-packages&page=');
$package_table = array();
$pt = array();
$pt['pac_code'] = 'Package Code';
$pt['awb_no'] = 'AWB No';
$pt['sen_date'] = 'Sent Date';
$pt['rec_date'] = 'Received Date';
$package_table[] = $pt;
if (count($packages) > 0) {
foreach ($packages as $p) {
$sent_date = $p['sent_date'] == '' ? ' ' : date('d-M-Y', strtotime($p['sent_date']));
$received_date = $p['received_date'] == '' ? ' ' : date('d-M-Y', strtotime($p['received_date']));
$pt = array();
$pt['pac_code'] = '<a href="?open=sp-package&id=' . $p['packages_id'] . '" title="View Package Detail"s>' . $p['packages_code'] . '</a>';
$pt['awb_no'] = $p['awb_no'];
$pt['sen_date'] = $sent_date;
$pt['rec_date'] = $received_date;
$package_table[] = $pt;
}
} else {
$pt = array();
$pt['pac_code'] = ' ';
$pt['awb_no'] = ' ';
$pt['sen_date'] = ' ';
$pt['rec_date'] = ' ';
$package_table[] = $pt;
}
$result = tep_draw_table('packages', $package_table);
$result .= $pagelink;
return $result;
}
<?php
$page = 1;
if (isset($_GET['page'])) {
$page = $_GET['page'];
}
$groups_total_query = tep_db_query("SELECT count(id) AS total FROM minierp_groups" . $filter_query);
$groups_total = tep_db_fetch_array($groups_total_query);
if (isset($_POST['me_action'])) {
if ($_POST['me_action'] == 'LOADGROUPS') {
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($groups_total['total'], $page, '?open=setting-me-groups&page=');
$groups_query = "SELECT * FROM minierp_groups mg";
$groups_query .= " ORDER BY mg.groupname ASC";
$groups_query .= $page_query;
$groups_result = tep_db_query($groups_query);
$groups = array();
$g = array();
$g['id'] = 'ID';
$g['name'] = 'Name';
$g['desc'] = 'Info';
$groups[] = $g;
if (tep_db_num_rows($groups_result) > 0) {
while ($row = tep_db_fetch_array($groups_result)) {
$detail = '?open=setting-me-group&id=' . $row['id'] . '&hidemenu=true';
$g = array();
$g['id'] = '<a href="' . $detail . '" class="view_webpage" title="Manage group detail">' . $row['id'] . '</a>';
$g['name'] = '<a href="' . $detail . '" class="view_webpage" title="Manage group detail">' . $row['groupname'] . '</a>';
$g['desc'] = $row['description'];
$groups[] = $g;
function invoiceOverview($rowperpage, $page, $status, $session_userinfo = '', $sort = 'asc')
{
use_class('payone_invoice');
use_class('jng_sp_customers');
use_class('minierp_customers');
$result = '';
$invoices = array();
$paging_filter = '';
$pagelink = '';
$count_trx = $this->invoiceOverviewCounter($status);
if ($count_trx >= 500) {
//DRAW WITH PAGING WHENEVER TOTAL ROWS >= 500
$offset = ($page - 1) * $rowperpage;
$paging_filter = "LIMIT {$rowperpage} OFFSET {$offset}";
$pagelink = tep_paging($count_trx, $page, '', '', '', $rowperpage);
}
/*##OLD QUERY
$q = "SELECT c.`jng_sp_customers_id`,c.`customer_id`, c.`customer_firstname`, c.`customer_lastname`, jo.`customer_billing_email` AS customers_email, payi.*";
$q .= ", CASE payi.invoice_order_type";
$q .= " WHEN 'S' THEN jo.order_id";
$q .= " WHEN 'J' THEN o.orders_no";
$q .= " ELSE NULL END AS order_no";
$q .= ", CASE payi.invoice_order_type";
$q .= " WHEN 'S' THEN sp.package_prefix";
$q .= " WHEN 'J' THEN 'J&G'";
$q .= " ELSE NULL END AS sp_code";
$q .= " FROM payone_invoice payi";
$q .= " LEFT JOIN jng_sp_orders jo ON jo.jng_sp_orders_id=payi.invoice_order_id";
$q .= " LEFT JOIN jng_sp sp ON sp.jng_sp_id=jo.jng_sp_id";
$q .= " LEFT JOIN orders o ON o.orders_id=payi.invoice_order_id";
$q .= " LEFT JOIN jng_sp_customers c ON c.customer_id=jo.customer_billing_id";
$q .= " WHERE payi.invoice_complete_status='$status'";
$q .= " ORDER BY payi.invoice_no ASC, payi.payone_saldo $sort";
*
*/
$q = "SELECT datas.* FROM";
$q .= " (";
$q .= " SELECT payi.*, jc.jng_sp_customers_id, jc.`customer_firstname` , jc.`customer_lastname` , ";
$q .= " jo.`customer_billing_email` AS customers_email, jo.order_id AS order_no, jo.payment_method, ";
$q .= " sp.package_prefix AS sp_code";
$q .= " FROM payone_invoice payi";
$q .= " INNER JOIN jng_sp_orders jo ON jo.jng_sp_orders_id = payi.invoice_order_id";
$q .= " INNER JOIN jng_sp_customers jc ON jc.jng_sp_id = jo.jng_sp_id AND jc.customer_id = jo.customer_billing_id";
$q .= " INNER JOIN jng_sp sp ON sp.jng_sp_id = jo.jng_sp_id";
$q .= " WHERE payi.invoice_complete_status = '{$status}'";
$q .= " AND invoice_order_type = 'S'";
$q .= " UNION ALL";
$q .= " SELECT payi.*, c.`customers_id` AS jng_sp_customers_id, c.`customers_firstname` AS customer_firstname, c.`customers_lastname` AS customer_lastname, ";
$q .= " o.`customers_email_address` AS customers_email, o.orders_no AS order_no, o.payment_method, ";
$q .= " 'J&G' AS sp_code ";
$q .= " FROM payone_invoice payi";
$q .= " INNER JOIN orders o ON o.orders_id = payi.invoice_order_id";
$q .= " INNER JOIN customers c ON c.customers_id = o.customers_id";
$q .= " WHERE payi.invoice_complete_status = '{$status}'";
$q .= " AND invoice_order_type = 'J'";
$q .= " ) datas";
$q .= " ORDER BY invoice_no ASC, payone_saldo {$sort}";
$q .= " {$paging_filter}";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$invoices[] = $row;
}
if (count($invoices) == 0) {
$result .= '<h3>No Invoices in this status</h3>';
} else {
$refund_status = array('F', 'D');
$still_pay_status = array('A', 'U');
$need_bank_account_status = array('O');
$super_admin = array(1, 23);
$table = array();
$t = array();
$t['d'] = 'Date';
$t['id1'] = 'Invoice';
$t['c'] = 'Customer<br/>Name';
$t['sp'] = '<span title="Ihr Aktenzeichen (Atriga Invoice) / Händler-Kundennummer (Payone)">Customer<br />ID</span>';
$t['s'] = 'Source';
$t['o'] = 'Payment<br/>Method';
$t['id2'] = 'Order';
$t['id3'] = 'Payone<br/>TXID';
$t['p'] = 'Amount<br />Total';
$t['p1'] = 'Amount<br/>Returns';
$t['p2'] = 'Amount<br/>Paid';
// if(in_array($status, $refund_status)) $t['p3'] = 'Amount<br />Refund';
$t['p3'] = 'Amount<br/>Refund';
$t['p4'] = 'Amount<br/>Fines';
$sort = $sort == 'asc' ? 'desc' : 'asc';
$t['b'] = '<a href="?open=payone-overview&sort=' . $sort . '" title="Sort Saldo ' . ucfirst($sort) . '" style="color:white;text-decoration:underline;"> Saldo</a>';
if (in_array($session_userinfo['id'], $super_admin)) {
$t['b1'] = 'Payone<br/>Saldo';
}
if (in_array($status, $still_pay_status)) {
$t['l'] = 'Action';
}
$table[] = $t;
$gta = 0;
$gtac = 0;
$gtap = 0;
$gtar = 0;
$gtaf = 0;
$gts = 0;
$gts1 = 0;
$gta_y = 0;
$gtac_y = 0;
$gtap_y = 0;
$gtar_y = 0;
$gtaf_y = 0;
$gts_y = 0;
$gts1_y = 0;
$gta_r = 0;
$gtac_r = 0;
$gtap_r = 0;
$gtar_r = 0;
$gtaf_r = 0;
$gts_r = 0;
$gts1_r = 0;
foreach ($invoices as $i) {
$currency = 'EUR';
$c_attr = '';
$c_email = '';
$customer_bankdata = null;
$balance = payone_invoice::calcBalance($i);
if (in_array($status, $need_bank_account_status) && $balance < PAYONE_MIN_NON_RETURNED_OVERPAYMENT) {
$customer_bankdata = $i['invoice_order_type'] == 'S' ? jng_sp_customers::getBankAccountData($i['jng_sp_customers_id']) : minierp_customers::getBankAccountData($i['invoice_order_id']);
if (is_null($customer_bankdata)) {
$c_attr = 'style="color:red" title="this customer (' . $i['jng_sp_customers_id'] . ') have no Bank Account Data"';
$c_email = '<a href="mailto:' . $i['customers_email'] . '" title="send mail to this customer">' . $i['customers_email'] . '</a>';
} else {
$c_attr = 'title="' . $i['jng_sp_customers_id'] . '/' . $i['customers_email'] . '"';
}
}
$order_no = $i['order_no'];
if ($order_no == '') {
$order_no = 'J&G ID: ' . $i['invoice_order_id'];
}
$t = array();
$t['d'] = date('d-M-y', strtotime($i['invoice_date']));
$t['id1'] = '<a href="print/delivery-document.php?id=' . $i['invoice_order_id'] . '" target="_blank" title="Download Invoice PDF">' . $i['invoice_no'] . '</a>';
$t['c'] = '<span ' . $c_attr . '>' . ucfirst($i['customer_firstname']) . ' ' . ucfirst($i['customer_lastname']) . '</span><br/>' . $c_email;
$t['sp'] = $i['jng_sp_customers_id'];
$t['s'] = $i['sp_code'];
$t['o'] = $i['payment_method'] != '' ? strtoupper($i['payment_method']) : '-';
$machnung_color = '';
$machnung_style = '';
if (in_array($status, $still_pay_status)) {
$machnung_color = $this->getMachnungColor($i['invoice_id']);
if ($machnung_color != '') {
$machnung_style = 'style="color:' . $machnung_color . '"';
}
}
if ($i['invoice_order_type'] == 'S') {
$order_link = '<a href="?open=sp-order&id=' . $i['invoice_order_id'] . '" target="_blank" title="View Order Detail" ' . $machnung_style . '>' . $order_no . '</a>';
} elseif ($i['invoice_order_type'] == 'J') {
$order_link = '<a href="?open=order&id=' . $i['invoice_order_id'] . '" target="_blank" title="View Order Detail" ' . $machnung_style . '>' . $order_no . '</a>';
} else {
$order_link = $order_no;
}
$t['id2'] = $order_link;
$t['id3'] = $i['payone_txid'];
$t['p'] = displayCurrency($currency, $i['invoice_amount']);
$t['p1'] = displayCurrency($currency, $i['invoice_amount_canceled']);
$t['p2'] = displayCurrency($currency, $i['invoice_amount_paid']);
$amount_refund = abs($i['invoice_amount_overpayment'] + $i['invoice_amount_overpayment_canceled']);
$t['p3'] = displayCurrency($currency, $amount_refund);
$amount_fines = $i['invoice_amount_fines'] - $i['invoice_amount_fines_canceled'];
$t['p4'] = displayCurrency($currency, $amount_fines);
$t['b'] = displayCurrency($currency, $balance);
if (in_array($session_userinfo['id'], $super_admin)) {
$t['b1'] = $balance != $i['payone_saldo'] ? '<span style="color:red">' . displayCurrency($currency, $i['payone_saldo']) . '</span>' : displayCurrency($currency, $i['payone_saldo']);
}
if (in_array($status, $still_pay_status)) {
$t['l'] = '<input type="hidden" class="txid" value="' . $i['payone_txid'] . '" /><input id="' . $status . $i['payone_txid'] . '" type="button" title="Set as Paid \'Collected By Atriga\'" value="P" name="10" class="actbtn">';
}
$table[] = $t;
/* SINCE WE USE PAGING, SO THESE VALUES WILL RECALCULATED OUTSIDE (TO GET ALL DATA, NOT ONLY WHAT SHOWN ON PAGE)
if($machnung_color=='yellow'){
$gta_y += $i['invoice_amount'];
$gtac_y += $i['invoice_amount_canceled'];
$gtap_y += $i['invoice_amount_paid'];
$gtar_y += $amount_refund;
$gtaf_y += $amount_fines;
$gts_y += $balance;
$gts1_y += $i['payone_saldo'];
}elseif($machnung_color=='red'){
$gta_r += $i['invoice_amount'];
$gtac_r += $i['invoice_amount_canceled'];
$gtap_r += $i['invoice_amount_paid'];
$gtar_r += $amount_refund;
$gtaf_r += $amount_fines;
$gts_r += $balance;
$gts1_r += $i['payone_saldo'];
}else{
$gta += $i['invoice_amount'];
$gtac += $i['invoice_amount_canceled'];
$gtap += $i['invoice_amount_paid'];
$gtar += $amount_refund;
$gtaf += $amount_fines;
$gts += $balance;
$gts1 += $i['payone_saldo'];
}
*/
}
//AMOUNT SUMMARY
$q = "SELECT invoice_id, payone_saldo, invoice_amount, invoice_amount_canceled, invoice_amount_paid,";
$q .= " ABS(invoice_amount_overpayment+invoice_amount_overpayment_canceled) amount_refund,";
$q .= " (invoice_amount_fines-invoice_amount_fines_canceled) AS amount_fines,";
$q .= payone_invoice::calcBalance(null, true) . " balance";
$q .= " FROM payone_invoice";
$q .= " WHERE invoice_complete_status = '{$status}'";
$dbq = tep_db_query($q);
while ($r = tep_db_fetch_array($dbq)) {
$machnung_color = '';
if (in_array($status, $still_pay_status)) {
$machnung_color = $this->getMachnungColor($r['invoice_id']);
}
if ($machnung_color == 'yellow') {
$gta_y += $r['invoice_amount'];
$gtac_y += $r['invoice_amount_canceled'];
$gtap_y += $r['invoice_amount_paid'];
$gtar_y += $r['amount_refund'];
$gtaf_y += $r['amount_fines'];
$gts_y += $r['balance'];
$gts1_y += $r['payone_saldo'];
} elseif ($machnung_color == 'red') {
$gta_r += $r['invoice_amount'];
$gtac_r += $r['invoice_amount_canceled'];
$gtap_r += $r['invoice_amount_paid'];
$gtar_r += $r['amount_refund'];
$gtaf_r += $r['amount_fines'];
$gts_r += $r['balance'];
$gts1_r += $r['payone_saldo'];
} else {
$gta += $r['invoice_amount'];
$gtac += $r['invoice_amount_canceled'];
$gtap += $r['invoice_amount_paid'];
$gtar += $r['amount_refund'];
$gtaf += $r['amount_fines'];
$gts += $r['balance'];
$gts1 += $r['payone_saldo'];
}
}
$result .= '<table><tr>';
$result .= '<td><h2 style="color:blue;">Amount Summary</h2>';
$result .= '<table>';
$result .= '<tr><td>Amount Total</td><td> : </td><td>' . displayCurrency($currency, $gta) . '</td></tr>';
$result .= '<tr><td>Amount Returns</td><td> : </td><td>' . displayCurrency($currency, $gtac) . '</td></tr>';
$result .= '<tr><td>Amount Paid</td><td> : </td><td>' . displayCurrency($currency, $gtap) . '</td></tr>';
$result .= '<tr><td>Amount Refund</td><td> : </td><td>' . displayCurrency($currency, $gtar) . '</td></tr>';
$result .= '<tr><td>Amount Fines</td><td> : </td><td>' . displayCurrency($currency, $gtaf) . '</td></tr>';
$result .= '<tr><td>Saldo</td><td> : </td><td>' . displayCurrency($currency, $gts) . '</td></tr>';
if (in_array($session_userinfo['id'], $super_admin)) {
$result .= '<tr><td>Payone Saldo</td><td> : </td><td>' . displayCurrency($currency, $gts1) . '</td></tr>';
}
$result .= '</table></td>';
if ($gta_y > 0) {
$result .= '<td><h2 style="color:yellow;">Amount Summary</h2>';
$result .= '<table>';
$result .= '<tr><td>Amount Total</td><td> : </td><td>' . displayCurrency($currency, $gta_y) . '</td></tr>';
$result .= '<tr><td>Amount Returns</td><td> : </td><td>' . displayCurrency($currency, $gtac_y) . '</td></tr>';
$result .= '<tr><td>Amount Paid</td><td> : </td><td>' . displayCurrency($currency, $gtap_y) . '</td></tr>';
$result .= '<tr><td>Amount Refund</td><td> : </td><td>' . displayCurrency($currency, $gtar_y) . '</td></tr>';
$result .= '<tr><td>Amount Fines</td><td> : </td><td>' . displayCurrency($currency, $gtaf_y) . '</td></tr>';
$result .= '<tr><td>Saldo</td><td> : </td><td>' . displayCurrency($currency, $gts_y) . '</td></tr>';
if (in_array($session_userinfo['id'], $super_admin)) {
$result .= '<tr><td>Payone Saldo</td><td> : </td><td>' . displayCurrency($currency, $gts1_y) . '</td></tr>';
}
$result .= '</table></td>';
}
if ($gta_r > 0) {
$result .= '<td><h2 style="color:red;">Amount Summary</h2>';
$result .= '<table>';
$result .= '<tr><td>Amount Total</td><td> : </td><td>' . displayCurrency($currency, $gta_r) . '</td></tr>';
$result .= '<tr><td>Amount Returns</td><td> : </td><td>' . displayCurrency($currency, $gtac_r) . '</td></tr>';
$result .= '<tr><td>Amount Paid</td><td> : </td><td>' . displayCurrency($currency, $gtap_r) . '</td></tr>';
$result .= '<tr><td>Amount Refund</td><td> : </td><td>' . displayCurrency($currency, $gtar_r) . '</td></tr>';
$result .= '<tr><td>Amount Fines</td><td> : </td><td>' . displayCurrency($currency, $gtaf_r) . '</td></tr>';
$result .= '<tr><td>Saldo</td><td> : </td><td>' . displayCurrency($currency, $gts_r) . '</td></tr>';
if (in_array($session_userinfo['id'], $super_admin)) {
$result .= '<tr><td>Payone Saldo</td><td> : </td><td>' . displayCurrency($currency, $gts1_r) . '</td></tr>';
}
$result .= '</table></td>';
}
$result .= '</tr></table><br/>';
$result .= '<table style="background:#cfcfcf;padding:10px;" class="ui-corner-all">';
if (in_array($session_userinfo['id'], $super_admin)) {
$result .= '<tr><td><span class="red">*txid or invoice_id</span><br/><input type="text" class="txt-check-balance" title="input txid or invoice_id to check current balance"/></td><td> </td><td><input type="button" class="btn-check-balance button" value="Check Current Balance" style="margin-top:10px;" /></td></tr>';
$result .= '<tr><td colspan="3"><div style="border-bottom:dashed 1px #efefef;width:100%;"></div></td></tr>';
}
$result .= '<tr><td><strong>Customer ID</strong></td><td>:</td><td>- equal to <strong>Ihr Aktenzeichen</strong> in Atriga Invoice</td></tr>';
$result .= '<tr><td> </td><td> </td><td>- equal to <strong>Händler-Kundennummer</strong> in Payone Online Payment Website</td></tr>';
$result .= '<tr><td colspan="3"><div style="border-bottom:dashed 1px #efefef;width:100%;"></div></td></tr>';
$result .= '<tr><td><strong>Amount Fines</strong></td><td>:</td><td>Fines issued by payone periodically after deadline of payment is reached ( 1st = 1 EUR; 2nd = 3 EUR; 3rd = 3 EUR => TOTAL <b>7 EUR</b> )</td></tr>';
$result .= '<tr><td colspan="3"><div style="border-bottom:dashed 1px #efefef;width:100%;"></div></td></tr>';
$result .= '<tr><td><strong>Payone Reminder</strong></td><td>:</td><td>Payment Reminder issued by Payone after deadline of payment is reached</td></tr>';
$result .= '<tr><td> </td><td> </td><td>- <strong>Level 1 Reminder</strong>, issued a week after deadline of payment is reached</td></tr>';
$result .= '<tr><td> </td><td> </td><td>- <strong>Level 2 Reminder</strong>, issued 2 week after deadline of payment is reached</td></tr>';
$result .= '<tr><td> </td><td> </td><td>- <strong>Level 3 Reminder</strong>, issued 3 week after deadline of payment is reached</td></tr>';
$result .= '<tr><td> </td><td> </td><td>- <strong>Level 5 Reminder</strong>, issued 4 week after deadline of payment is reached</td></tr>';
$result .= '<tr><td style="color:blue">blue Order</td><td>:</td><td>No Reminder Issued</td></tr>';
$result .= '<tr><td style="color:yellow">yellow Order</td><td>:</td><td>1st and 2nd Level Reminder issued</td></tr>';
$result .= '<tr><td style="color:red">red Order</td><td>:</td><td>a week after 3rd Level Reminder / Level 5 Reminder issued</td></tr>';
$result .= '</table><br/>';
$result .= tep_draw_table('spo sticky-tablehead', $table);
$result .= $pagelink;
}
return $result;
}
$customers = $class_jcust->retrieveList($jng_sp_id, $filter, $sort, $limit, $offset);
$content = '';
$content .= '<div style="margin-bottom:10px;">';
$content .= '<form name="customers" action="?open=sp-customers" method="post">';
$content .= 'Search for <input type="text" class="iwbutton input" name="keyword" value="' . $keywords . '" />';
$content .= ' <input type="submit" name="submit" value="Go" />';
$content .= '</form></div>';
if (count($customers) == 0) {
$content .= '<div class="red>No customer found</div>';
} else {
$ctable = array();
$ct = array();
$ct['long_id'] = 'SP ID';
$ct['sort'] = 'Title';
$ct['name'] = 'Name';
$ct['email'] = 'Email';
$ct['date'] = 'Added on';
$ctable[] = $ct;
foreach ($customers as $c) {
$ct = array();
$ct['long_id'] = $c['customer_id'];
$ct['sort'] = $c['customer_title'];
$ct['name'] = '<a href="?open=sp-customer&id=' . $c['jng_sp_customers_id'] . '" title="View customer detail">' . $c['customer_firstname'] . ' ' . $c['customer_lastname'] . '</a>';
$ct['email'] = $c['customer_email'];
$ct['date'] = date('d-M-y', strtotime($c['date_added']));
$ctable[] = $ct;
}
$content .= tep_draw_table('', $ctable);
$content .= tep_paging($cust_count, $page, '?open=sp-customers&page=');
}
$title = "{$sp_title} Customers ({$cust_count})";
# Author : Dewa #
# Created : 05 February 2010, 02:25 PM #
#########################################
$title = 'Customer Testimonial';
$page = !isset($_GET['page']) ? 1 : $_GET['page'];
$offset = $page == 0 ? 0 : ($page - 1) * ROWPERPAGE;
$testimonials = array();
$t = array();
$t['id'] = 'ID';
$t['name'] = 'Full Name';
$t['testimony'] = 'Testimonial';
$t['date_add'] = 'Created';
use_class("testimony_view");
$o_t = new testimony_view($offset, ROWPERPAGE);
$total = $o_t->getTotal();
$testimonials = $o_t->getView($t);
$pagelink = tep_paging($total, $page, '?open=customers-testimonials&page=');
$content = '<div>';
$content .= '<a href="?open=customers-testimonial&&hidemenu=true" class="view_webpage"></a>';
$content .= '<input type="button" name="create" value="Add New Testimonial" onclick="$(this).siblings(\'a\').click();" />';
$content .= '</div>';
$content .= '<div> </div>';
$content .= tep_draw_table('customers', $testimonials);
$content .= $pagelink;
/*
$javascript = '
$("#fancy_close").click(function() {
location.reload();
});';
*
*/
$fg_total_query .= " LEFT JOIN products_description pd ON pd.products_id=p.products_id AND pd.language_id=2";
$fg_total_query .= " LEFT JOIN products_stock ps ON ps.jng_warehouses_id={$fg_whid} AND ps.products_id=p.products_id AND ps.articles_id=0";
$fg_total_query .= $filter_query;
} else {
$fg_total_query .= " FROM products_stock ps";
$fg_total_query .= " LEFT JOIN products p ON p.products_id=ps.products_id";
$fg_total_query .= " LEFT JOIN products_description pd ON pd.products_id=ps.products_id AND pd.language_id=2";
$fg_total_query .= $filter_query;
$fg_total_query .= " AND ps.jng_warehouses_id={$fg_whid}";
}
$fg_total_result = tep_db_query($fg_total_query);
$fg_total = tep_db_fetch_array($fg_total_result);
$total_products = $fg_total['total'];
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($total_products, $page, '?open=products-stock-list&page=');
} else {
$page_query = "";
$pagelink = "";
}
$q = "SELECT p.products_id, p.products_model, p.products_image, p.stars, p.products_brand_id, pd.products_name, pnc.products_length";
$q .= ", ps.articles_id, IFNULL(ps.stock, 0) AS stock, b.bins_id, b.code AS bin_code";
foreach ($sort_options as $col => $so) {
$q .= ", pl.{$col}";
}
if ($view_all == '1') {
$q .= " FROM products p";
$q .= " LEFT JOIN products_non_configurator pnc ON pnc.products_id=p.products_id";
$q .= " LEFT JOIN products_log pl ON pl.products_id=p.products_id";
$q .= " LEFT JOIN products_description pd ON pd.products_id=p.products_id AND pd.language_id=2";
$q .= " LEFT JOIN products_stock ps ON ps.jng_warehouses_id={$fg_whid} AND ps.products_id=p.products_id AND ps.articles_id=0";
header("Cache-Control: cache, must-revalidate");
header("Pragma: public");
echo tep_create_csv($r);
exit;
} else {
//NORMAL PAGE
$page = 1;
if (isset($_GET['page'])) {
$page = $_GET['page'];
}
$bp_total_query = tep_db_query("SELECT count(c.customers_id) AS total FROM customers c" . $filter_query);
$bp_total = tep_db_fetch_array($bp_total_query);
//PAGING QUERY & LINKS
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($bp_total['total'], $page, '?open=business-partner&page=');
$bp_query = "SELECT c.customers_id, c.customers_email_address, c.customers_status, c.customers_firstname, c.customers_lastname";
$bp_query .= ", bp.*";
$bp_query .= " FROM customers c";
$bp_query .= " LEFT JOIN business_partner bp ON bp.customers_id=c.customers_id";
$bp_query .= $filter_query;
$bp_query .= " ORDER BY c.customers_id";
$bp_query .= $page_query;
$bp_result = tep_db_query($bp_query);
$bp_data = array();
while ($row = tep_db_fetch_array($bp_result)) {
$bp_data[] = $row;
}
$bp[0]['sel'] = ' ';
$bp[0]['company'] = 'Company';
$bp[0]['name'] = 'Name';
function retrieveOverview($page = 1, $filterby = "", $sortby = "")
{
$row_per_page = ROWPERPAGE;
$offset = ($page - 1) * $row_per_page;
$limit_query = "LIMIT {$row_per_page} OFFSET {$offset}";
$filter_query = $filterby == "" ? "" : "WHERE {$filterby}";
$sort_query = $sortby == "" ? "" : "ORDER BY {$sortby}";
$q_total = tep_db_query("SELECT COUNT(log_id) AS total FROM payone_log {$filter_query}");
$r_total = tep_db_fetch_array($q_total);
$total_rows = $r_total['total'];
$q = tep_db_query("SELECT * FROM payone_log {$filter_query} {$sort_query} {$limit_query}");
$logs = array();
while ($row = tep_db_fetch_array($q)) {
$logs[] = $row;
}
$result = '';
if (count($logs) == 0) {
$result .= 'No Log Data Found';
} else {
$table = array();
$t = array();
$t['id'] = 'ID';
$t['e'] = 'TXID';
$t['a'] = 'Time';
$t['id1'] = 'Process';
$t['id2'] = 'Method';
$t['id3'] = 'Mode';
$t['s'] = 'Status';
$table[] = $t;
foreach ($logs as $l) {
$log_time = date('d.m.y H:i', strtotime($l['log_process_time']));
$log_process = '<span class="' . ($l['log_process'] == 'S' ? 'green">Submit' : 'blue">Receive') . '</span>';
$request = unserialize($l['log_process_data_submit']);
$response = strlen($l['log_process_data_result']) <= 4 ? $l['log_process_data_result'] : unserialize($l['log_process_data_result']);
if ($response == "TSOK") {
$response = array("status" => "TSOK");
}
$mode = '<span class="' . (strtolower($request['mode']) == 'live' ? 'bold' : '') . '">' . $request['mode'] . '</span>';
$status = '<img src="/images/icon-' . ($response['status'] == "TSOK" || $response['status'] == 'APPROVED' || $response['status'] == 'REDIRECT' ? 'yes' : 'no') . '.png" title="' . $response['status'] . '" />';
$t['id'] = $l['log_id'];
$t['e'] = isset($request['txid']) && $request['txid'] != '' ? $request['txid'] : '-';
$t['a'] = $log_time;
$t['id1'] = $log_process;
$t['id2'] = $l['log_process_method'];
$t['id3'] = $mode;
$t['s'] = $status;
$table[] = $t;
}
$result .= tep_draw_table('spo', $table);
$result .= tep_paging($total_rows, $page, '?open=payone-logger&page=');
}
return $result;
}
$fq .= " OR cp.customers_nickname LIKE '%{$k}%')";
$filters[] = $fq;
}
$filter_query .= " WHERE " . implode(' OR ', $filters);
}
}
//PROCESS PAGING
$pagelink = '';
$page_query = '';
if ($paging) {
$customers_total_query = tep_db_query("SELECT count(c.customers_id) AS total FROM customers c" . $filter_query);
$customers_total = tep_db_fetch_array($customers_total_query);
//PAGING QUERY & LINKS
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($customers_total['total'], $page, '?open=customer-list&page=');
}
//START TEMPLATE
$customers_query = "SELECT c.customers_id, CONCAT(c.customers_firstname,' ',c.customers_lastname) AS customers_name";
$customers_query .= ", c.customers_email_address";
$customers_query .= ", cp.customers_nickname, ci.customers_info_date_of_last_logon AS last_logon";
$customers_query .= ", ci.customers_info_date_account_created AS account_created";
$customers_query .= ", dr.dti_referrers_code";
$customers_query .= " FROM customers c";
$customers_query .= " LEFT JOIN customers_profile cp ON cp.customers_id=c.customers_id";
$customers_query .= " LEFT JOIN customers_info ci ON ci.customers_info_id=c.customers_id";
$customers_query .= " LEFT JOIN dti_referrers dr ON dr.dti_referrers_id=ci.dti_referrers_id";
$customers_query .= $filter_query;
$customers_query .= " ORDER BY account_created DESC";
$customers_query .= $page_query;
$customers_result = tep_db_query($customers_query);
break;
}
if (isset($_POST['orderby'])) {
$_SESSION['packages_orderby'] = tep_db_prepare_input($_POST['orderby']);
} else {
if (!isset($_SESSION['packages_orderby'])) {
$_SESSION['packages_orderby'] = '1';
}
}
$orderby = $_SESSION['packages_orderby'];
$packages_total_query = tep_db_query("SELECT count(packages_id) AS total FROM minierp_packages" . $filter_query);
$packages_total = tep_db_fetch_array($packages_total_query);
//PAGING QUERY & LINKS
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($packages_total['total'], $page, '?open=packages&page=');
$filterarray = array('Not Received Packages', 'Received Packages');
foreach ($logistic_partners_option as $l) {
$filterarray[] = 'LP: ' . $l;
}
$filteroption = '<div class="filteroption"><form name="packages" action="?open=packages" method="post"> Filter By ';
$filteroption .= '<select name="filterby">';
$filteroption .= '<option value="">None</option>';
foreach ($filterarray as $key => $value) {
$filtervalue = $key + 1;
if ($filterby == $filtervalue) {
$selected = ' selected="selected"';
} else {
$selected = '';
}
$filteroption .= '<option value="' . $filtervalue . '"' . $selected . '>' . $value . '</option>';
$brand_combo .= '<option value="N" ' . $sel . '>No Logo</option>';
$sel = $current_brand == '0' ? $selected : '';
$brand_combo .= '<option value="0" ' . $sel . '>JULIE & GRACE</option>';
foreach ($brands as $b) {
$sel = $current_brand == $b['products_brand_id'] ? $selected : '';
$brand_combo .= '<option value="' . $b['products_brand_id'] . '" ' . $sel . '>' . $b['brand_name'] . '</option>';
}
$brand_combo .= '</select>';
return $brand_combo;
}
$sort_query = " jc.total_sold DESC";
$limit = 80;
$offset = ($page - 1) * $limit;
$catalog_total = $class_jc->countTotalCatalog($jng_sp_id, $filter_query, $join_query);
$catalog = $class_jc->retrieveCatalog($jng_sp_id, $filter_query, $sort_query, $limit, $offset, $join_query);
$pagelink = tep_paging($catalog_total, $page, '?open=sp-products-links-popup&products_id=' . $products_id . '&type=' . $type . '&jng_sp_id=' . $jng_sp_id . '&hidemenu=true&page=', '', '', $limit);
$content = '';
$content .= '<div style="float:left;width:120px;text-align:center;">';
$content .= '<div>' . webImage($product_data['p']['products_image'], '120', '120') . '</div>';
if (!is_null($product['brand'])) {
$content .= '<div>' . webImage($product['brand']['brand_image'], '100', '') . '</div>';
}
$content .= '</div>';
$content .= '<div style="margin-left:140px;">';
$content .= '<h2>' . $product_data['p']['products_id'] . ' / ' . $product_data['p']['products_model'] . '</h2>';
$content .= '<h3>' . displayCurrency('EUR', $product['price']) . '</h3>';
$content .= '<div style="margin-top:20px;"><form name="filtering" action="?open=sp-products-links-popup&products_id=' . $products_id . '&type=' . $type . '&jng_sp_id=' . $jng_sp_id . '&hidemenu=true" method="post">';
$content .= '<table class="form" border="0" cellpadding="0" cellspacing="0">';
$content .= '<tr><td colspan="2"><strong>Filter By</strong></td></tr>';
$content .= '<tr><td>Nav ID</td><td>' . ComboBoxneckermannNavIDs('*', 'nav-filter', $navfiltered, '', 'onchange="filtering.submit();"') . '</td></tr>';
$content .= '<tr><td>Logo</td><td>' . drawBrandCombo(true, 'brand-filter', $brandfiltered, 'onchange="filtering.submit();"') . '</td></tr>';
if (isset($join_pc) && $join_pc === true) {
$cat_total_query .= " {$join_pc_query}";
}
foreach ($jng_sp_active as $temp_sp) {
$jng_sp_id = $temp_sp['jng_sp_id'];
$alias = "jc{$jng_sp_id}";
$cat_total_query .= " LEFT JOIN jng_sp_catalog {$alias}";
$cat_total_query .= " ON {$alias}.jng_sp_id={$jng_sp_id} AND {$alias}.products_id=p.products_id";
}
$cat_total_query .= $filter_query;
$cat_total_result = tep_db_query($cat_total_query);
$cat_total = tep_db_fetch_array($cat_total_result);
//PAGING QUERY & LINKS
$offset = ($page - 1) * $rowperpage;
$page_query = " LIMIT " . $rowperpage . " OFFSET {$offset}";
$pagelink = tep_paging($cat_total['total'], $page, '?open=sp-products-activation&page=');
$q = "SELECT p.products_id, p.products_status, p.products_model, p.products_image, pd.products_name";
$q .= ",p.products_price AS jng_price, p.products_price_old AS jng_price_old";
foreach ($jng_sp_active as $temp_sp) {
$jng_sp_id = $temp_sp['jng_sp_id'];
$alias = "jc{$jng_sp_id}";
$q .= ", {$alias}.active_status AS " . $alias . "_active_status";
$q .= ", {$alias}.article_number AS " . $alias . "_article_number";
$q .= ", {$alias}.price AS " . $alias . "_price";
$q .= ", {$alias}.price_old AS " . $alias . "_price_old";
}
$q .= " FROM products p";
$q .= " LEFT JOIN products_log pl ON pl.products_id=p.products_id";
$q .= " LEFT JOIN products_description pd ON pd.products_id=p.products_id AND pd.language_id=2";
if (isset($join_pc) && $join_pc === true) {
$q .= " {$join_pc_query}";
$products_filter = " AND p.products_status='1'";
} else {
$products_filter = "";
}
$products_query = tep_db_query("SELECT p.products_id, p.products_model, p.products_image, pd.products_name FROM products_to_categories ptc LEFT JOIN products p ON p.products_id=ptc.products_id LEFT JOIN products_description pd ON pd.products_id=ptc.products_id AND pd.language_id= {$lid} WHERE ptc.categories_id={$category} {$products_filter} ORDER BY p.products_model, p.products_date_added ASC");
$products = array();
while ($pro_row = tep_db_fetch_array($products_query)) {
$products[] = $pro_row;
}
$total_products = count($products);
$productsperpage = 100;
$counter_start = ($current_page - 1) * $productsperpage + 1;
$counter_end = $current_page * $productsperpage;
$total_list = $total_categories + $total_products;
$total_page = $total_list > $productsperpage ? ceil($total_list / $productsperpage) : 1;
$pagelinks = tep_paging($total_list, $current_page, $linker . '&page=', '', 'font-weight:bold;', $productsperpage);
/*
$pagelinks = '';
if($total_page>1) {
$pl = $linker.'&page=';
$pagelinks .= '<div style="margin-bottom:10px;font-weight:bold;">Page ';
for($p=1;$p<=$total_page;$p++) $pagelinks .= ($p==$current_page) ? " $p" : ' <a href="'.$pl.$p.'">'.$p.'</a>';
$pagelinks .= '</div>';
}
*/
$tables = array();
$tables[0] = array();
$counter = 0;
$categories_maxpage = ceil($total_categories / $productsperpage);
if ($current_page <= $categories_maxpage) {
foreach ($categories as $c) {
<?php
//GET PAGE
$page = 1;
if (isset($_GET['page'])) {
$page = $_GET['page'];
}
$jng_sp_id = '1';
$filter_query = " WHERE jc.active_status='1'";
$cat_total_query = tep_db_query("SELECT count(jc.jng_sp_catalog_id) AS total FROM jng_sp_catalog jc" . $filter_query);
$cat_total = tep_db_fetch_array($cat_total_query);
//PAGING QUERY & LINKS
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($cat_total['total'], $page, '?open=sp-catalog&page=');
$cat_query = "SELECT jc.jng_sp_catalog_id, jc.products_id, jc.article_number, SUM(js.stock) AS stock";
$cat_query .= ", p.products_image";
$cat_query .= " FROM jng_sp_catalog jc";
$cat_query .= " LEFT JOIN products p ON p.products_id=jc.products_id";
$cat_query .= " LEFT JOIN jng_sp_stocks js ON js.jng_sp_catalog_id=jc.jng_sp_catalog_id";
$cat_query .= $filter_query;
$cat_query .= " GROUP BY jc.jng_sp_catalog_id";
$cat_query .= " ORDER BY jc.last_modified DESC";
$cat_query .= $page_query;
$cat_result = tep_db_query($cat_query);
$cat = array();
while ($row = tep_db_fetch_array($cat_result)) {
$cat[] = $row;
}
$content = '';
if (count($cat) > 0) {
$button_set_2 = 'style="display:block;"';
} else {
$button_set_1 = 'style="display:block;"';
$button_set_2 = 'style="display:none;"';
}
$cat_total_query = "SELECT count(jc.jng_sp_catalog_id) AS total FROM jng_sp_catalog jc";
if (strpos($filter_query, 'p.') !== false) {
$cat_total_query .= " LEFT JOIN products p ON p.products_id=jc.products_id";
}
$cat_total_query .= $filter_query;
$cat_total_result = tep_db_query($cat_total_query);
$cat_total = tep_db_fetch_array($cat_total_result);
//PAGING QUERY & LINKS
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($cat_total['total'], $page, '?open=analysis-vc&page=');
$brands = $class_pb->retrieveList();
function drawBrandCombo($with_show_all, $name, $current_brand, $params = '')
{
global $brands;
$brand_combo = '<select name="' . $name . '" ' . $params . '>';
$selected = 'selected="selected"';
if ($with_show_all) {
$brand_combo .= '<option value="" ' . $sel . '>Please select a logo option...</option>';
}
$sel = $current_brand == 'N' ? $selected : '';
$brand_combo .= '<option value="N" ' . $sel . '>No Logo</option>';
$sel = $current_brand == '0' ? $selected : '';
$brand_combo .= '<option value="0" ' . $sel . '>JULIE & GRACE</option>';
foreach ($brands as $b) {
$sel = $current_brand == $b['products_brand_id'] ? $selected : '';
} else {
if (!isset($_SESSION['orders_orderby'])) {
$_SESSION['orders_orderby'] = '1';
}
}
$orderby = $_SESSION['orders_orderby'];
//PROCESS PAGING
$pagelink = '';
$page_query = '';
$orders_total_query = tep_db_query("SELECT count(o.orders_id) AS total FROM orders o" . $filter_query);
$orders_total = tep_db_fetch_array($orders_total_query);
if ($paging) {
//PAGING QUERY & LINKS
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($orders_total['total'], $page, '?open=orders&page=');
} else {
if ($orders_total['total'] > $custorders_max && $showall == false) {
$showall_link = true;
}
}
//FIELDS TO GET
$orders_fields = "o.*";
$orders_fields .= ",os.orders_status_name";
//$orders_fields .= ",MAX(mp.packages_code) AS packages_code,MAX(mp.packages_id) AS packages_id";
$orders_fields .= ",ot.value";
$orders_fields .= ",dr.dti_referrers_code";
//$orders_fields .= ",pc.visible_status";
$orders_fields .= ",csl.list_type";
//QUERY ORDERS
$orders_query = "SELECT ";
} else {
$lastrun_exist = false;
$lastrun_opt = '';
}
if ($lastrun_exist) {
$lastrun = 'This report is queried <strong>' . $lastruninterval . '</strong> ago.';
$filter_query = " WHERE esr.jng_warehouses_id={$whid} AND esr.elements_stock_lastrun_id={$lastrunid} AND esr.elements_id!=700";
if ($filter_query_add != '') {
$filter_query .= " AND {$filter_query_add}";
}
//PAGING QUERY & LINKS
$report_total_query = tep_db_query("SELECT count(esr.elements_id) AS total FROM elements_stock_report esr LEFT JOIN elements e ON e.elements_id=esr.elements_id" . $filter_query);
$report_total = tep_db_fetch_array($report_total_query);
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($report_total['total'], $page, '?open=elements-stock&page=');
$report_query = "SELECT esr.*, e.elements_code, e.is_finalized, CONCAT('elements/', ei.image_path, ei.image_filename) AS image";
$report_query .= ", (es.pieces_per_qty_type * es.stock) AS current_stock, es.qty_type, es.stock_ratio AS current_stock_ratio, es.resourcing_day AS current_resourcing_day";
$report_query .= " FROM elements_stock_report esr";
$report_query .= " LEFT JOIN elements e ON e.elements_id=esr.elements_id";
$report_query .= " LEFT JOIN elements_images ei ON ei.elements_id=esr.elements_id AND ei.elements_sample_name='EC1'";
$report_query .= " LEFT JOIN elements_stock es ON es.jng_warehouses_id=esr.jng_warehouses_id AND es.elements_id=esr.elements_id";
$report_query .= $filter_query;
$report_query .= " ORDER BY esr.{$sort_table} {$sort_mode}";
//$report_query .= "ORDER BY ";
$report_query .= $page_query;
$report_result = tep_db_query($report_query);
$report = array();
$r = array();
$r['e'] = 'Element';
$r['v'] = 'Virtual Stock';
function analysis_list($jng_warehouses_id, $period, $price_type, $page)
{
use_class('jng_warehouses');
$class_jw = new jng_warehouses();
$wh = $class_jw->retrieveDetail($jng_warehouses_id);
$period_year = substr($period, 0, 4);
$period_month = substr($period, -2);
$period_date = "{$period_year}-{$period_month}-01";
if ($wh['warehouse_type'] == 'E') {
$filter = " WHERE mse.jng_warehouses_id={$jng_warehouses_id} AND mse.stock_period='{$period}'";
$total_query = "SELECT count(mse.elements_id) AS total FROM minierp_stock_elements mse";
$total_query .= $filter;
$total_result = tep_db_query($total_query);
$total = tep_db_fetch_array($total_result);
//PAGING QUERY & LINKS
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($total['total'], $page, '?open=analysis-stock-list&page=');
$q = "SELECT mse.*";
$q .= ", SUM(mset.stock_add) AS stock_add, SUM(mset.stock_reduce) AS stock_red";
$q .= ", CONCAT(ei.image_path, ei.image_filename) AS elements_image, e.elements_code, ed.name AS elements_name";
$q .= " FROM minierp_stock_elements mse";
$q .= " LEFT JOIN minierp_stock_elements_transaction AS mset";
$q .= " \tON mset.jng_warehouses_id=mse.jng_warehouses_id";
$q .= " \tAND mset.elements_id=mse.elements_id";
$q .= "\t\tAND YEAR(mset.transaction_time)='{$period_year}'";
$q .= "\t\tAND MONTH(mset.transaction_time)='{$period_month}'";
$q .= " LEFT JOIN elements e ON e.elements_id=mse.elements_id";
$q .= " LEFT JOIN elements_images ei ON ei.elements_id=e.elements_id AND ei.elements_sample_name='EC1'";
$q .= " LEFT JOIN elements_description ed ON ed.elements_id=e.elements_id AND ed.languages_id=2";
$q .= $filter;
$q .= " GROUP BY elements_id";
$q .= $page_query;
$r = tep_db_query($q);
} else {
$filter = " WHERE msp.jng_warehouses_id={$jng_warehouses_id} AND msp.stock_period='{$period}'";
$total_query = "SELECT count(msp.products_id) AS total FROM minierp_stock_products msp";
$total_query .= $filter;
$total_result = tep_db_query($total_query);
$total = tep_db_fetch_array($total_result);
//PAGING QUERY & LINKS
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($total['total'], $page, '?open=analysis-stock-list&page=');
$q = "SELECT msp.*";
$q .= ", SUM(mspt.stock_add) AS stock_add, SUM(mspt.stock_reduce) AS stock_red";
$q .= ", p.products_image, p.products_model, pd.products_name";
$q .= " FROM minierp_stock_products msp";
$q .= " LEFT JOIN minierp_stock_products_transaction AS mspt";
$q .= " \tON mspt.jng_warehouses_id=msp.jng_warehouses_id";
$q .= " \tAND mspt.products_id=msp.products_id";
$q .= "\t\tAND YEAR(mspt.transaction_time)='{$period_year}'";
$q .= "\t\tAND MONTH(mspt.transaction_time)='{$period_month}'";
$q .= " LEFT JOIN products p ON p.products_id=msp.products_id";
$q .= " LEFT JOIN products_description pd ON pd.products_id=p.products_id AND pd.language_id=2";
$q .= $filter;
$q .= " GROUP BY products_id";
$q .= $page_query;
$r = tep_db_query($q);
}
$stocks = array();
$s = array();
$s['img'] = 'Product';
$s['name'] = 'Info';
$s['ev'] = 'EoP Value';
$s['sb'] = 'Inventory BoP';
$s['sa'] = 'Addition';
$s['sr'] = 'Reduction';
$s['se'] = 'Inventory EoP';
$s['sv'] = 'Inventory EoP Value';
$s['avg'] = 'Average Inventory Level';
$s['avg_v'] = 'Average Inventory Value';
$s['to'] = 'Inventory Turnover';
$s['doi'] = 'Average days of inventory';
$s['tc'] = 'Total Cost of Capital of Period';
$stocks[] = $s;
$total_days_period = date('Ym') == $period ? date('j') : date('t', strtotime($period_date));
while ($row = tep_db_fetch_array($r)) {
if ($wh['warehouse_type'] == 'E') {
$img = webImage('elements/' . $row['elements_image'], '80', '80');
$info = "<div><strong>{$row['elements_id']} / {$row['elements_code']}</strong></div>";
$info .= "<div>{$row['elements_name']}</div>";
} else {
$img = '<a href="' . $detail_link . '" title="View and Manage Product Detail">' . webImage($row['products_image'], '80', '80') . '</a>';
$detail_link = "?open=product-detail&products_id={$row['products_id']}";
$info = "<div><strong>{$row['products_id']} / {$row['products_model']}</strong></div>";
$info .= "<div>{$row['products_name']}</div>";
}
$eop_value = $row[$price_type . '_close'];
$eop_value_inv = $row['stock_close'] * $eop_value;
$avg = ($row['stock_open'] + $row['stock_close']) / 2;
$avg_value = $avg * $eop_value;
$turnover = $avg == 0 ? 0 : $row['stock_red'] / $avg;
$avg_days = $turnover == 0 ? 0 : $total_days_period / $turnover;
$cap_cost = $avg_value == 0 ? 0 : 0.07000000000000001 * $avg_days / 365 * 31 * $avg_value;
$s = array();
$s['img'] = $img;
$s['name'] = $info;
$s['ev'] = number_format($eop_value, 2);
$s['sb'] = $row['stock_open'];
$s['sa'] = $row['stock_add'];
$s['sr'] = $row['stock_red'];
$s['se'] = $row['stock_close'];
$s['sv'] = number_format($eop_value_inv, 2);
$s['avg'] = number_format($avg, 1);
$s['avg_v'] = number_format($avg_value, 2);
$s['to'] = number_format($turnover, 2);
$s['doi'] = number_format($avg_days, 1);
$s['tc'] = number_format($cap_cost, 2);
$stocks[] = $s;
}
$result = tep_draw_table('proman', $stocks);
$result .= '<div> </div>';
$result .= $pagelink;
return $result;
}
$button_set_2 = 'style="display:block;"';
} else {
$button_set_1 = 'style="display:block;"';
$button_set_2 = 'style="display:none;"';
}
$cat_total_query = "SELECT count(jc.jng_sp_catalog_id) AS total FROM jng_sp_catalog jc";
if (strpos($filter_query, 'p.') !== false) {
$cat_total_query .= " LEFT JOIN products p ON p.products_id=jc.products_id";
}
$cat_total_query .= $filter_query;
$cat_total_result = tep_db_query($cat_total_query);
$cat_total = tep_db_fetch_array($cat_total_result);
//PAGING QUERY & LINKS
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($cat_total['total'], $page, '?open=sp-products-links&page=');
$brands = $class_pb->retrieveList();
$cat_query = "SELECT jc.jng_sp_catalog_id, jc.products_id, jc.article_number, jc.name, jc.price, jc.price_old";
$cat_query .= ", p.products_image, p.products_brand_id, p.products_price";
$cat_query .= " FROM jng_sp_catalog jc";
$cat_query .= " LEFT JOIN products p ON p.products_id=jc.products_id";
$cat_query .= $filter_query;
if ($sort_table == 'jc.date_added' && $sort_mode == 'DESC') {
$sort_table = "jc.status_price ASC, {$sort_table}";
}
$cat_query .= " ORDER BY {$sort_table} {$sort_mode}";
$cat_query .= $page_query;
$cat_result = tep_db_query($cat_query);
$cat = array();
$c = array();
$c['img'] = 'Product';
if (isset($_POST['keywords']) && $_POST['keywords'] != '') {
$keywords = tep_db_prepare_input(trim($_POST['keywords']));
$page_query = '';
$pagelink = '';
} else {
$keywords = '';
$page = 1;
if (isset($_GET['page'])) {
$page = $_GET['page'];
}
$vouchers_total_query = tep_db_query("SELECT count(coupon_id) AS total FROM coupons" . $filter_query);
$vouchers_total = tep_db_fetch_array($vouchers_total_query);
//PAGING QUERY & LINKS
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($vouchers_total['total'], $page, '?open=setting-vouchers&page=');
}
$vouchers_query = "SELECT c.coupon_id, c.coupon_type, c.coupon_code, c.coupon_amount, c.free_shipping";
$vouchers_query .= ", c.uses_per_coupon, c.uses_per_user, c.coupon_active, c.coupon_expire_date";
$vouchers_query .= ", COUNT(DISTINCT crt.unique_id) AS redeemed, COUNT(DISTINCT IF(crt.order_id=0, NULL, crt.order_id)) AS ordered";
$vouchers_query .= " FROM coupons c";
$vouchers_query .= " LEFT JOIN coupon_redeem_track crt ON crt.coupon_id=c.coupon_id";
if ($keywords != '') {
$vouchers_query .= " WHERE c.coupon_code LIKE '%{$keywords}%'";
}
$vouchers_query .= " GROUP BY c.coupon_id";
$vouchers_query .= " ORDER BY date_created DESC";
$vouchers_query .= $page_query;
$vouchers_result = tep_db_query($vouchers_query);
$vouchers_count = tep_db_num_rows($vouchers_result);
if ($vouchers_count == 1) {
}
$filter_id = $_SESSION['invoices_filterby'];
//if($filter_id!='') $filter_query = " WHERE invoices_status = " . $filter_id;
if(isset($_POST['invoiceby'])) {
$_SESSION['invoices_invoiceby'] = tep_db_prepare_input($_POST['invoiceby']);
} else {
if(!isset($_SESSION['invoices_invoiceby'])) $_SESSION['invoices_invoiceby'] = '1';
}
$invoiceby = $_SESSION['invoices_invoiceby'];
$invoices_total_query = tep_db_query("SELECT count(invoices_id) AS total FROM minierp_invoices" . $filter_query);
$invoices_total = tep_db_fetch_array($invoices_total_query);
//PAGING QUERY & LINKS
$offset = ($page-1) * ROWPERPAGE;
$page_query = " LIMIT ".ROWPERPAGE." OFFSET $offset";
$pagelink = tep_paging($invoices_total['total'], $page, '?open=invoices&page=');
/*
$filter_query = tep_db_query("SELECT invoices_status_id as id, invoices_status_name as name FROM minierp_invoices_status WHERE language_id=".LANGUAGEID);
$filteroption .= '<select name="filterby">';
$filteroption .= '<option value="">None</option>';
while($row=tep_db_fetch_array($filter_query)) {
if($filter_id==$row['id']) $selected = 'selected="selected"';
else $selected = '';
$filteroption .= '<option value="'.$row['id'].'" '.$selected.'>'.$row['name'].'</option>';
}
$filteroption .= '</select> ';
*/
$filteroption = '<div class="filteroption"><form name="invoices" action="?open=invoices" method="post">';
$invoicebyarray = array('Invoice Date Newer First', 'Invoice Date Older First', 'Payment Method', 'Customer Name');
$filteroption .= ' Sort by <select name="invoiceby" onchange="invoices.submit();">';
