}
$ypos += 55;
$pdf->setXY($cellstart, $ypos);
$cellsizecollweek = $cellsize + $margin;
$firstorder = false;
}
$no = 1;
}
}
if ($_POST['keep_file']) {
$path_location = $_POST['path_location'];
if (empty($path_location)) {
$filepath = 'sales-report-weekly-products/';
$log_date = strtotime($filter_date);
$yeardir = $filepath . date('y/', $log_date);
if (!file_exists(DIR_FS_ARCHIVES . $yeardir) || !is_dir(DIR_FS_ARCHIVES . $yeardir)) {
mkdir(DIR_FS_ARCHIVES . $yeardir);
}
$monthdir = $yeardir . date('ym/', $log_date);
if (!file_exists(DIR_FS_ARCHIVES . $monthdir) || !is_dir(DIR_FS_ARCHIVES . $monthdir)) {
mkdir(DIR_FS_ARCHIVES . $monthdir);
}
$path_location = DIR_FS_ARCHIVES . $monthdir;
}
$pdf->Output($path_location . $filename, 'F');
echo utf8_encode($filename);
} else {
$pdf->Output($filename, 'D');
}
$logger->close();
tep_db_close();
public static function queryDepotSummary($includes_refill = false, $save_to_config = true)
{
//TODO: Please consider to also includes warehouse id as filter when we want to add new depot
set_time_limit(0);
self::loadDIOHsettings();
$depot_summary = load_config('depot-summary');
$totalstars = 3;
$goodstock_tolerance = 0;
use_class('logger');
$logger = new logger('classes', 'product');
$logger->write('QUERY DEPOT SUMMARY!');
$logger->write('Previous Summary Result:');
foreach ($depot_summary as $key => $value) {
$logger->write("{$key} = {$value}");
}
$refill_data = array();
if ($includes_refill) {
use_class('depot_orders');
$q = "SELECT products_id, articles_id, SUM(quantity) AS refill_qty";
$q .= " FROM depot_orders";
$q .= " WHERE trans_type IN (" . depot_orders::FILTER_TRANS_TYPE_ALLREFILL . ")";
$q .= " AND status NOT IN ( " . depot_orders::FILTER_STATUS_CLOSE . ")";
$q .= " GROUP BY products_id, articles_id";
$r = tep_db_query($q);
while ($row = tep_db_fetch_array($r)) {
$refill_data["{$row['products_id']}-{$row['articles_id']}"] = $row['refill_qty'];
}
}
$q = "SELECT ps.products_id, ps.articles_id, p.stars, (ps.stock-ps.booking_active) AS stock_available, p.material_expenses";
$q .= " FROM products p";
$q .= " LEFT JOIN products_stock ps ON ps.products_id=p.products_id";
$q .= " WHERE p.products_status='1' OR ps.stock>0";
$q .= " ORDER BY products_id, articles_id";
$r = tep_db_query($q);
$product = null;
$overstock = array();
$shortages = array();
$goodstock = array();
for ($s = 0; $s <= $totalstars; $s++) {
$overstock[$s]['products'] = array();
$overstock[$s]['articles'] = 0;
$overstock[$s]['quantity'] = 0;
$overstock[$s]['value'] = 0;
$shortages[$s]['products'] = array();
$shortages[$s]['articles'] = 0;
$shortages[$s]['quantity'] = 0;
$shortages[$s]['value'] = 0;
$goodstock[$s]['products'] = array();
$goodstock[$s]['articles'] = 0;
$goodstock[$s]['quantity'] = 0;
$goodstock[$s]['value'] = 0;
}
$rowcounter = 0;
while ($row = tep_db_fetch_array($r)) {
$rowcounter++;
$stock_refill = $includes_refill && isset($refill_data["{$row['products_id']}-{$row['articles_id']}"]) ? $refill_data["{$row['products_id']}-{$row['articles_id']}"] : 0;
$stock_available = ($row['stock_available'] < 0 ? 0 : $row['stock_available']) + $stock_refill;
$material_expenses = isset($row['material_expenses']) && $row['material_expenses'] > 0 ? self::getMaterialExpenseInDefaultCurrency($row['material_expenses']) : 0;
if (product::$diohStopLevel[$row['stars']] == 0) {
//NO TARGET, NO NEED TO COUNT
if ($stock_available == 0) {
$goodstock[$row['stars']]['products'][] = $row['products_id'];
$goodstock[$row['stars']]['articles']++;
} else {
$overstock[$row['stars']]['products'][] = $row['products_id'];
$overstock[$row['stars']]['articles']++;
$overstock[$row['stars']]['quantity'] += $stock_available;
$overstock[$row['stars']]['value'] += $stock_available * $material_expenses;
}
} else {
if (is_null($product) || is_object($product) && $product->id != $row['products_id']) {
$product = new product($row['products_id']);
}
$stock_target = $product->getDIOHstockTarget($row['articles_id']);
$stock_diff = $stock_available - $stock_target;
if ($stock_diff > $goodstock_tolerance) {
//OVERSTOCK
$overstock_quantity = $stock_diff - $goodstock_tolerance;
$overstock[$product->stars]['products'][] = $product->id;
$overstock[$product->stars]['articles']++;
$overstock[$product->stars]['quantity'] += $overstock_quantity;
$goodstock[$product->stars]['quantity'] += $stock_available - $overstock_quantity;
$overstock[$product->stars]['value'] += $overstock_quantity * $material_expenses;
$goodstock[$product->stars]['value'] += ($stock_available - $overstock_quantity) * $material_expenses;
} elseif ($stock_diff < -1 * $goodstock_tolerance) {
//SHORTAGE
$shortages_quantity = abs($stock_diff) - $goodstock_tolerance;
$shortages[$product->stars]['products'][] = $product->id;
$shortages[$product->stars]['articles']++;
$shortages[$product->stars]['quantity'] += $shortages_quantity;
$shortages[$product->stars]['quantity'] += $stock_available;
$shortages[$product->stars]['value'] += $shortages_quantity * $material_expenses;
$shortages[$product->stars]['value'] += $stock_available * $material_expenses;
} else {
$goodstock[$product->stars]['products'][] = $product->id;
$goodstock[$product->stars]['articles']++;
$goodstock[$product->stars]['quantity'] += $stock_available;
$goodstock[$product->stars]['value'] += $stock_available * $material_expenses;
}
}
// echo '. ';
}
$logger->write('Total rows from query = ' . $rowcounter);
for ($s = 0; $s <= $totalstars; $s++) {
$overstock[$s]['products'] = count(array_unique($overstock[$s]['products']));
$shortages[$s]['products'] = count(array_unique($shortages[$s]['products']));
$goodstock[$s]['products'] = count(array_unique($goodstock[$s]['products']));
}
// echo '<pre>';
// var_dump($goodstock);
// var_dump($overstock);
// var_dump($shortages);
// echo '</pre>';
$depot_summary['lastrun'] = time();
$depot_summary['diohTarget-0'] = self::$diohStopLevel[0];
$depot_summary['diohTarget-1'] = self::$diohStopLevel[1];
$depot_summary['diohTarget-2'] = self::$diohStopLevel[2];
$depot_summary['diohTarget-3'] = self::$diohStopLevel[3];
$depot_summary['overstock-3-products'] = $overstock[3]['products'];
$depot_summary['overstock-3-articles'] = $overstock[3]['articles'];
$depot_summary['overstock-3-quantity'] = $overstock[3]['quantity'];
$depot_summary['overstock-3-value'] = $overstock[3]['value'];
$depot_summary['overstock-2-products'] = $overstock[2]['products'];
$depot_summary['overstock-2-articles'] = $overstock[2]['articles'];
$depot_summary['overstock-2-quantity'] = $overstock[2]['quantity'];
$depot_summary['overstock-2-value'] = $overstock[2]['value'];
$depot_summary['overstock-1-products'] = $overstock[1]['products'];
$depot_summary['overstock-1-articles'] = $overstock[1]['articles'];
$depot_summary['overstock-1-quantity'] = $overstock[1]['quantity'];
$depot_summary['overstock-1-value'] = $overstock[1]['value'];
$depot_summary['overstock-0-products'] = $overstock[0]['products'];
$depot_summary['overstock-0-articles'] = $overstock[0]['articles'];
$depot_summary['overstock-0-quantity'] = $overstock[0]['quantity'];
$depot_summary['overstock-0-value'] = $overstock[0]['value'];
$depot_summary['goodstock-3-products'] = $goodstock[3]['products'];
$depot_summary['goodstock-3-articles'] = $goodstock[3]['articles'];
$depot_summary['goodstock-3-quantity'] = $goodstock[3]['quantity'];
$depot_summary['goodstock-3-value'] = $goodstock[3]['value'];
$depot_summary['goodstock-2-products'] = $goodstock[2]['products'];
$depot_summary['goodstock-2-articles'] = $goodstock[2]['articles'];
$depot_summary['goodstock-2-quantity'] = $goodstock[2]['quantity'];
$depot_summary['goodstock-2-value'] = $goodstock[2]['value'];
$depot_summary['goodstock-1-products'] = $goodstock[1]['products'];
$depot_summary['goodstock-1-articles'] = $goodstock[1]['articles'];
$depot_summary['goodstock-1-quantity'] = $goodstock[1]['quantity'];
$depot_summary['goodstock-1-value'] = $goodstock[1]['value'];
$depot_summary['goodstock-0-products'] = $goodstock[0]['products'];
$depot_summary['goodstock-0-articles'] = $goodstock[0]['articles'];
$depot_summary['goodstock-0-quantity'] = $goodstock[0]['quantity'];
$depot_summary['goodstock-0-value'] = $goodstock[0]['value'];
$depot_summary['shortages-3-products'] = $shortages[3]['products'];
$depot_summary['shortages-3-articles'] = $shortages[3]['articles'];
$depot_summary['shortages-3-quantity'] = $shortages[3]['quantity'];
$depot_summary['shortages-2-products'] = $shortages[2]['products'];
$depot_summary['shortages-2-articles'] = $shortages[2]['articles'];
$depot_summary['shortages-2-quantity'] = $shortages[2]['quantity'];
$depot_summary['shortages-2-value'] = $shortages[2]['value'];
$depot_summary['shortages-1-products'] = $shortages[1]['products'];
$depot_summary['shortages-1-articles'] = $shortages[1]['articles'];
$depot_summary['shortages-1-quantity'] = $shortages[1]['quantity'];
$depot_summary['shortages-1-value'] = $shortages[1]['value'];
$depot_summary['shortages-0-products'] = $shortages[0]['products'];
$depot_summary['shortages-0-articles'] = $shortages[0]['articles'];
$depot_summary['shortages-0-quantity'] = $shortages[0]['quantity'];
$depot_summary['shortages-0-value'] = $shortages[0]['value'];
if ($save_to_config) {
save_config('depot-summary', $depot_summary);
}
$logger->write('New value successfully saved');
$logger->close();
// echo 'Done!';
return $depot_summary;
}
function constructQuery($jng_sp_id, $filters = array())
{
$res_filter = $this->constructFilters($filters, $jng_sp_id);
$filter = $res_filter['filter'];
$filter_having = $res_filter['filter_having'];
$filter_join = $res_filter['filter_join'];
$field_used_in_filter = $res_filter['field_used_in_filter'];
$is_all_sp = $jng_sp_id == 'allsp' ? true : false;
$query = "SELECT e.*";
$query .= ", el.used_in_products, el.weekly_sold, el.weekly_sold_products";
$query .= ", ec.name AS cat_name, pm.material_name AS mat_name, cpd.name AS color_name, ei.name AS size_name, eh.name AS holesize_name";
$query .= ", es.name AS shape_name, es.shape_image, eu.name AS surface_name, eu.surface_image, et.name AS cut_name, et.cuts_image AS cut_image";
$query .= ", ehe.name AS height_name, ew.name AS width_name, eth.name AS thickness_name";
$query .= " FROM element e";
$query .= " LEFT JOIN elements_log el ON el.elements_id=e.elements_id";
$query .= " LEFT JOIN elements_log_static els ON els.elements_id=e.elements_id";
$query .= " LEFT JOIN elements_to_elements_suppliers etes ON etes.elements_id = e.elements_id AND is_default = 1";
$query .= " LEFT JOIN element_category ec ON ec.element_category_id=e.attribute_category_id";
$query .= " LEFT JOIN products_materials pm ON pm.products_materials_id=e.attribute_material_id";
$query .= " LEFT JOIN color_pattern cp ON cp.color_pattern_id=e.attribute_color_id";
$query .= " LEFT JOIN color_pattern_description cpd ON cpd.color_pattern_id=e.attribute_color_id AND languages_id=1";
$query .= " LEFT JOIN element_size ei ON ei.element_size_id=e.attribute_size_id";
$query .= " LEFT JOIN element_holesize eh ON eh.element_holesize_id=e.attribute_holesize_id";
$query .= " LEFT JOIN element_shape es ON es.element_shape_id=e.attribute_shape_id";
$query .= " LEFT JOIN element_surface eu ON eu.element_surface_id=e.attribute_surface_id";
$query .= " LEFT JOIN element_cut et ON et.element_cut_id=e.attribute_cut_id";
$query .= " LEFT JOIN element_height ehe ON ehe.element_height_id=e.attribute_height_id";
$query .= " LEFT JOIN element_width ew ON ew.element_width_id=e.attribute_width_id";
$query .= " LEFT JOIN element_thickness eth ON eth.element_thickness_id=e.attribute_thickness_id";
if (in_array("elstock", $field_used_in_filter)) {
$query .= " LEFT JOIN elements_stock est ON est.elements_id = e.elements_id AND est.jng_warehouses_id = " . WAREHOUSE_ID_ELEMENTS;
}
if (in_array("isnoproducts", $field_used_in_filter)) {
$query .= " LEFT JOIN (SELECT DISTINCT(elements_id) FROM products_use_elements pue";
$query .= " INNER JOIN products p ON p.products_id = pue.products_id AND p.products_status = 1) pue ON pue.elements_id = e.elements_id";
$query .= " LEFT JOIN (SELECT DISTINCT(paue.elements_id) FROM products_articles_use_elements paue";
$query .= " INNER JOIN products_use_elements pue ON pue.products_use_elements_id = paue.products_use_elements_id";
$query .= " INNER JOIN products p ON p.products_id = pue.products_id AND p.products_status = 1";
$query .= " AND paue.elements_id > 0) paue ON paue.elements_id = e.elements_id";
}
foreach ($filter_join as $fjk => $fjv) {
$query .= $fjv;
}
$query .= " WHERE e.is_finalized='1'";
if ($filter != '') {
$query .= " AND {$filter}";
}
if ($filter_having != '') {
$query .= " GROUP BY e.elements_id HAVING " . $filter_having;
}
$query .= " ORDER BY e.elements_id";
//save query to a log file
$logger = new logger('classes', 'elements_search_query_executed');
$logger->write($query);
$logger->close();
return $query;
}
public function delete($username)
{
if ($this->status == 1) {
$q = tep_db_query("DELETE FROM jng_sp_preorders_items WHERE jng_sp_preorders_id={$this->id}");
$q = tep_db_query("DELETE FROM jng_sp_preorders WHERE jng_sp_preorders_id={$this->id}");
use_class('logger');
$logger = new logger('classes', 'jng_sp_preorders', false);
$logger->write("SP Preorder ID {$this->id} ({$this->name}) is deleted by {$username}");
$logger->close();
$this->id = null;
$this->setDetail();
$result = true;
} else {
$result = false;
}
return $result;
}
function deleteDetail($color_pattern_id)
{
$logger = new logger('class', 'ColorPattern');
//prepare parameter data
$color_pattern_id = tep_db_prepare_input($color_pattern_id);
$allow_delete = true;
$error_message = array();
//delete data
if ($allow_delete) {
$logger->write("DELETING Color Pattern");
$query = "DELETE FROM color_pattern_has_color WHERE color_pattern_id = {$color_pattern_id}";
$result_cphc = tep_db_query($query);
if ($result_cphc) {
$query = "DELETE FROM color_pattern_description WHERE color_pattern_id = {$color_pattern_id}";
$result_cpd = tep_db_query($query);
if ($result_cpd) {
$query = "DELETE FROM color_pattern WHERE color_pattern_id = {$color_pattern_id}";
$result_cp = tep_db_query($query);
if (!$result_cp) {
$logger->write("Failed to delete from table color_pattern");
}
} else {
$logger->write("Failed to delete from table color_pattern_description");
}
} else {
$logger->write("Failed to delete from table color_pattern_has_color");
}
} else {
$result_cphc = false;
$result_cpd = false;
$result_cp = false;
}
//handle process result report
$processResult = array('error_message' => $error_message, 'result_cp' => $result_cp, 'result_cpd' => $result_cpd, 'result_cphc' => $result_cphc);
$processResult = $this->parseReturn($processResult);
$logger->close();
return $processResult;
}
function updatePaymentStatus($orders_id, $new_status, $update_by = '')
{
use_class('logger');
$logger = new logger('classes', 'orders');
$logger->write("ORDER ID: {$orders_id}");
$order = $this->retrieveDetail($orders_id);
$sda = array('paid_status' => $new_status);
tep_db_perform('orders', $sda, 'update', "orders_id={$orders_id}");
$timestamp = date('Y-m-d H:i:s');
$logger->write("UPDATE payment status: {$new_status}");
$log_sda = array();
$log_sda['orders_id'] = $order['orders_id'];
$log_sda['payment_method'] = $order['payment_method'];
$log_sda['payment_status'] = $new_status;
$log_sda['update_time'] = $timestamp;
$log_sda['update_by'] = $update_by;
$log_sda['type'] = 'JG';
tep_db_perform('log_orders_payment_status', $log_sda);
//set leadtime start date when payment is done
if ($new_status == '1') {
$this->leadtimeSetStart($timestamp, '', $orders_id);
$logger->write("SET LEADTIME {$timestamp}");
}
$logger->close();
}
function tep_db_query($query)
{
global $session_userinfo, $db;
$query_bgn = list($old_usec, $old_sec) = explode(' ', microtime());
$result = mysql_query($query, $db) or tep_db_error($query, mysql_errno(), mysql_error());
$query_end = list($new_usec, $new_sec) = explode(' ', microtime());
$runtime = (double) $new_usec + (double) $new_sec - ((double) $old_usec + (double) $old_sec);
if ($runtime > 1) {
//when query is too slow, log it to to a file
$username = isset($session_userinfo) ? $session_userinfo['username'] : '******';
use_class('logger');
$logger = new logger('general', 'query_slow', false);
$logger->write('User: '******'REQUEST_URI']);
if (isset($_POST) && count($_POST) > 0) {
$logger->write('--- POST vars sent ---');
foreach ($_POST as $pk => $pv) {
$logger->write("{$pk} = {$pv}");
}
$logger->write('--- POST vars end ---');
}
$logger->write('Total Execution Time: ' . $runtime . ' seconds');
$logger->write($query);
$logger->close();
}
return $result;
}
/**
* Run a query to retrieve products to be refilled (article level) for zalando based on the current settings
* @param Boolean $create_depot_refill Sent false for grabbing only the data and true to create the depot orders and also log the process as cron daily-zalando-reorder
* @return Array of Array containing data of each article
*/
function queryZalandoNeedRefill($create_depot_refill = false)
{
if ($create_depot_refill) {
use_class('logger');
$logger = new logger('cron', 'daily-zalando-reorder');
}
$sp_specsets = load_config('sp-specific-settings');
$articles = array();
//RUNNING RULE 1
$q = "SELECT ps.products_id, ps.articles_id, jc.active_age, (ps.stock-ps.booking_active) AS stock_available, p.products_image, p.stars, p.family_id";
$q .= ", IFNULL(pa.products_ean, p.products_ean) AS products_ean, IFNULL(pa.length, pnc.products_length) AS article_length";
$q .= " FROM jng_sp_catalog jc";
$q .= " LEFT JOIN products p ON p.products_id=jc.products_id";
$q .= " LEFT JOIN products_non_configurator pnc ON pnc.products_id=jc.products_id";
$q .= " LEFT JOIN products_stock ps ON ps.jng_warehouses_id=" . WAREHOUSE_ID_PRODUCTS_HAMBURG . " AND ps.products_id=jc.products_id";
$q .= " LEFT JOIN products_articles pa ON pa.products_id=ps.products_id AND pa.products_articles_id=ps.articles_id";
$q .= " WHERE jc.jng_sp_id=8 AND jc.active_status='1' AND jc.active_age<=" . $sp_specsets['zade-reorder-productsage'];
$q .= " AND p.stars>=" . $sp_specsets['zade-reorder-starsmin'] . " AND p.stars<=" . $sp_specsets['zade-reorder-starsmax'];
$q .= " AND p.is_wholesale != 1";
//exclude wholesale products
$q .= " AND (ps.articles_id=0 OR pa.active_status=1)";
//filter for default length and active article only
$q .= " HAVING stock_available<=" . $sp_specsets['zade-reorder-minstock'];
$q .= " ORDER BY p.stars DESC, ps.products_id, ps.articles_id";
$r = tep_db_query($q);
$total_articles_nostock_rule1 = tep_db_num_rows($r);
if ($total_articles_nostock_rule1 > 0) {
while ($row = tep_db_fetch_array($r)) {
$row['current_refill'] = $this->refillOpenOrdersQuantity(SEGMENT_ID_HAMBURG, $row['products_id'], $row['articles_id']);
$row['zalando_needed'] = $sp_specsets['zade-reorder-minstock'] + 1 - ($row['stock_available'] + $row['current_refill']);
$logdata = $row['products_id'] . '-' . $row['articles_id'] . ' => MinStockNeedded:' . $sp_specsets['zade-reorder-minstock'] . ' | StockAvailable:' . $row['stock_available'] . ' | CurrentRefill:' . $row['current_refill'] . ' | ';
$articles[] = $row;
if ($row['zalando_needed'] > 0) {
//Need Zalando Auto Refill, create one
$logdata .= 'Refill Created: ';
if ($create_depot_refill) {
$do_list = $this->newOrderAutoRefill(SEGMENT_ID_HAMBURG, $row['products_id'], $row['articles_id'], $row['products_ean'], $row['zalando_needed'], self::AUTO_REFILL_ID_ZALANDO);
$logdata .= implode(',', $do_list);
}
} else {
$logdata .= 'No Refill Created';
}
if ($create_depot_refill) {
$logger->write($logdata);
}
}
$total_refill_qty = count($do_list);
if ($create_depot_refill) {
$logger->write("Total Refill qty created: {$total_refill_qty}");
}
}
//RUNNING RULE 2, IF THE SETTINGS FOR BOTH RULE STAY THE SAME, WE MIGHT WANT TO MERGE THEM INTO A FUNCTION
$q = "SELECT ps.products_id, ps.articles_id, jc.active_age, (ps.stock-ps.booking_active) AS stock_available, p.products_image, p.stars, p.family_id";
$q .= ", IFNULL(pa.products_ean, p.products_ean) AS products_ean, IFNULL(pa.length, pnc.products_length) AS article_length";
$q .= " FROM jng_sp_catalog jc";
$q .= " LEFT JOIN products p ON p.products_id=jc.products_id";
$q .= " LEFT JOIN products_non_configurator pnc ON pnc.products_id=jc.products_id";
$q .= " LEFT JOIN products_stock ps ON ps.jng_warehouses_id=" . WAREHOUSE_ID_PRODUCTS_HAMBURG . " AND ps.products_id=jc.products_id";
$q .= " LEFT JOIN products_articles pa ON pa.products_id=ps.products_id AND pa.products_articles_id=ps.articles_id";
$q .= " WHERE jc.jng_sp_id=8 AND jc.active_status='1' AND jc.active_age<=" . $sp_specsets['zade-reorder-rule2-productsage'];
$q .= " AND p.stars>=" . $sp_specsets['zade-reorder-rule2-starsmin'] . " AND p.stars<=" . $sp_specsets['zade-reorder-rule2-starsmax'];
$q .= " AND p.is_wholesale != 1";
//exclude wholesale products
$q .= " AND (ps.articles_id=0 OR pa.active_status=1)";
//filter for default length and active article only
$q .= " HAVING stock_available<=" . $sp_specsets['zade-reorder-rule2-minstock'];
$q .= " ORDER BY p.stars DESC, ps.products_id, ps.articles_id";
$r = tep_db_query($q);
$total_articles_nostock_rule2 = tep_db_num_rows($r);
if ($total_articles_nostock_rule2 > 0) {
while ($row = tep_db_fetch_array($r)) {
$row['current_refill'] = $this->refillOpenOrdersQuantity(SEGMENT_ID_HAMBURG, $row['products_id'], $row['articles_id']);
$row['zalando_needed'] = $sp_specsets['zade-reorder-rule2-minstock'] + 1 - ($row['stock_available'] + $row['current_refill']);
$logdata = $row['products_id'] . '-' . $row['articles_id'] . ' => MinStockNeedded:' . $sp_specsets['zade-reorder-rule2-minstock'] . ' | StockAvailable:' . $row['stock_available'] . ' | CurrentRefill:' . $row['current_refill'] . ' | ';
$articles[] = $row;
if ($row['zalando_needed'] > 0) {
//Need Zalando Auto Refill, create one
$logdata .= 'Refill Created: ';
if ($create_depot_refill) {
$do_list = $this->newOrderAutoRefill(SEGMENT_ID_HAMBURG, $row['products_id'], $row['articles_id'], $row['products_ean'], $row['zalando_needed'], self::AUTO_REFILL_ID_ZALANDO);
$logdata .= implode(',', $do_list);
}
} else {
$logdata .= 'No Refill Created';
}
if ($create_depot_refill) {
$logger->write($logdata);
}
}
$total_refill_qty = count($do_list);
if ($create_depot_refill) {
$logger->write("Total Refill qty created: {$total_refill_qty}");
}
}
if ($create_depot_refill) {
$logger->write('Total no stock articles with Rule 1: ' . $total_articles_nostock_rule1);
$logger->write('Total no stock articles with Rule 2: ' . $total_articles_nostock_rule2);
$logger->close();
}
return $articles;
}
/**
* Construct SQL "Complete" syntax from $filters and $sorts that used and submitted
* @param int $jng_sp_id
* @param array $filters
* @param array $sorts
* @return string
*/
private function constructQuery($jng_sp_id, $filters = array(), $sorts = array())
{
$res_filter = $this->constructFilters($filters, $jng_sp_id);
$filter = utf8_decode($res_filter['filter']);
//decode if theres any umlaut chars
$filter_subquery = $res_filter['filter_subquery'];
$filter_having = $res_filter['filter_having'];
$filter_join = $res_filter['filter_join'];
$field_used_in_filter = $res_filter['field_used_in_filter'];
$res_sorts = $this->constructSorts($sorts);
$is_all_sp = $jng_sp_id == 'allsp' ? true : false;
$query = "SELECT brand_name, stars, hh_stock, hh_stock_value" . ", sales_monthly_1, sales_monthly_2, sales_monthly_3" . ", sold_monthly_1, sold_monthly_2, sold_monthly_3" . ", price_uvp, price_default";
if (in_array('iskilled', $field_used_in_filter)) {
$query .= ", removed_date";
}
if ($jng_sp_id > 0 || $is_all_sp) {
$query .= ", products_id, material_expenses";
//$query .= ", price, price_old, active_age";
if ($is_all_sp) {
$query .= ", (((MAX(price)/{$this->net_pricer})-material_expenses)/(MAX(price)/{$this->net_pricer}))*100 AS margin";
$query .= ", MAX(price) AS products_price, SUM(total_sales) AS total_sales";
$query .= ", IFNULL(MAX(active_age), DATEDIFF(NOW(), products_date_added)) AS products_age";
$query .= ", DATEDIFF(NOW(), products_date_added) AS jng_products_age";
$query .= ", AVG(sales_weekly) AS sales_weekly, AVG(sales_monthly) AS sales_monthly";
//$query .= ", AVG(IFNULL(vc_now,0)) AS vc_now";
//$query .= ", AVG(IFNULL(vc_now,0))*total_sold AS total_vc";
//$query .= ", SUM(vc_total) AS vc_total";
$query .= ", SUM(total_sold) AS total_sold, SUM(total_returned) AS total_returned, ((SUM(total_returned)/SUM(total_sold)) * 100) AS returned_rate";
} else {
$query .= ", ((((price/{$this->net_pricer})-material_expenses)/(price/{$this->net_pricer}))*100) AS margin";
$query .= ", price AS products_price, total_sales AS total_sales";
$query .= ", IFNULL(active_age, DATEDIFF(NOW(), products_date_added)) AS products_age";
$query .= ", DATEDIFF(NOW(), products_date_added) AS jng_products_age";
$query .= ", sales_weekly, sales_monthly";
//$query .= ", IFNULL(vc_now,0) AS vc_now";
//$query .= ", IFNULL(vc_now,0)*total_sold AS total_vc";
//$query .= ", vc_total";
$query .= ", total_sold, total_returned, ((total_returned/total_sold) * 100) AS returned_rate";
}
$query .= " FROM (";
$query .= " SELECT p.products_id, jc.price, jc.total_sales, jc.active_age";
$query .= ", jc.sales_weekly, jc.sales_monthly, jc.total_sold, jc.total_returned";
} elseif ($jng_sp_id == '0') {
$query .= ", products_id, material_expenses";
$query .= ", ((((products_price/{$this->net_pricer})-material_expenses)/(products_price/{$this->net_pricer}))*100) AS margin";
$query .= " ,products_price, 0 AS total_sales";
$query .= " ,DATEDIFF(NOW(), products_date_added) AS products_age";
$query .= " ,0 AS sales_weekly, 0 AS sales_monthly";
//$query .= " ,0 AS vc_now";
//$query .= " , 0 AS total_vc";
//$query .= " , 0 AS vc_total";
$query .= " ,0 AS total_sold, 0 AS total_returned, 0 AS returned_rate";
$query .= " FROM (";
$query .= " SELECT p.products_id, p.products_price";
}
$query .= ", p.material_expenses, p.products_date_added, p.stars , pb.brand_name";
$query .= ", p.products_price_uvp AS price_uvp, p.products_price_default AS price_default";
$query .= ", pl.sales_monthly_1, pl.sales_monthly_2, pl.sales_monthly_3, pl.sold_monthly_1, pl.sold_monthly_2, pl.sold_monthly_3";
$query .= ", pst.stock AS hh_stock, (pst.stock * p.material_expenses) AS hh_stock_value";
if (in_array('iskilled', $field_used_in_filter)) {
$query .= ", pr.removed_date";
}
$query .= " FROM products p";
if ($jng_sp_id > 0 || $is_all_sp) {
$filter_spid = $is_all_sp ? "" : " jc.jng_sp_id='{$jng_sp_id}' AND ";
$query .= $is_all_sp || in_array('isspstep0notadded', $field_used_in_filter) ? " LEFT " : " INNER ";
$query .= " JOIN jng_sp_catalog jc ON {$filter_spid} jc.products_id=p.products_id";
}
if (in_array('proddescriptionimpstat', $field_used_in_filter)) {
$language_filter = '';
} else {
$language_filter = 'AND pd.language_id = 2';
}
// $query .= " LEFT JOIN products_description pd ON pd.products_id = p.products_id AND pd.language_id = 2";
$query .= " LEFT JOIN products_description pd ON pd.products_id = p.products_id {$language_filter}";
$query .= " LEFT JOIN products_description2 pd2 ON pd2.products_id = p.products_id";
$query .= " LEFT JOIN products_brand pb ON pb.products_brand_id = p.products_brand_id";
$query .= " LEFT JOIN products_log pl ON pl.products_id = p.products_id";
$query .= " LEFT JOIN products_log_static pls ON pls.products_id=p.products_id";
if (in_array('datekeep', $field_used_in_filter) || in_array('datekeepexclude', $field_used_in_filter)) {
$query .= " LEFT JOIN products_keep_flag pkf ON pkf.products_id=p.products_id";
}
//if(in_array('isbestsell', $field_used_in_filter)) $query.= " LEFT JOIN products_best_good pbg ON pbg.products_id = p.products_id";
if (in_array('prodmat', $field_used_in_filter)) {
//$query .= " LEFT JOIN products_to_products_materials ptpm ON ptpm.products_id = p.products_id";
$query .= " INNER JOIN (";
$query .= " SELECT ptpm.products_id, CONCAT(',', GROUP_CONCAT(ptpm.products_materials_id), ',') AS products_materials_id";
$query .= " FROM products_to_products_materials ptpm";
$query .= " GROUP BY ptpm.products_id";
$query .= " HAVING " . implode(" AND ", $filter_subquery['prodmat']);
$query .= ") subquery_materials ON subquery_materials.products_id = p.products_id";
}
if (in_array('prodcat', $field_used_in_filter)) {
$query .= " LEFT JOIN products_to_categories ptc ON ptc.products_id = p.products_id";
}
if (in_array('proddeflength', $field_used_in_filter)) {
$query .= " LEFT JOIN products_non_configurator pnc ON pnc.products_id = p.products_id";
}
if (in_array('elused', $field_used_in_filter)) {
//$query .= " LEFT JOIN products_use_elements pue ON pue.products_id = p.products_id";
//$query .= " LEFT JOIN products_articles_use_elements paue ON paue.products_use_elements_id = pue.products_use_elements_id";
$query .= " INNER JOIN (";
$query .= " SELECT pue.products_id, CONCAT(',', GROUP_CONCAT(pue.elements_id),',',IFNULL(GROUP_CONCAT(paue.elements_id),0), ',') AS elements_id";
$query .= " FROM products_use_elements pue";
$query .= " LEFT JOIN products_articles_use_elements paue ON paue.products_use_elements_id=pue.products_use_elements_id";
$query .= " GROUP BY pue.products_id";
$query .= " HAVING " . implode(" AND ", $filter_subquery['elused']);
$query .= ") subquery_elements ON subquery_elements.products_id = p.products_id";
}
if (in_array('elfinishing', $field_used_in_filter)) {
$query .= " INNER JOIN (";
$query .= " SELECT products_id, fg+fh+fb+fo+fr AS elements_finishing_code FROM";
$query .= " (SELECT p.products_id";
$query .= " ,IF(SUM(finishing_goldplate)>0,1,0) fg";
$query .= " ,IF(SUM(finishing_hammer)>0,11,0) fh";
$query .= " ,IF(SUM(finishing_brush)>0,111,0) fb";
$query .= " ,IF(SUM(finishing_oxid)>0,1111,0) fo";
$query .= " ,IF(SUM(finishing_rosegoldplate)>0,11111,0) fr";
$query .= " FROM products p";
$query .= " INNER JOIN products_use_elements pue ON pue.products_id = p.products_id";
$query .= " GROUP BY pue.products_id) dt";
$query .= " HAVING " . implode(" AND ", $filter_subquery['elfinishing']);
$query .= ") subquery_finishing ON subquery_finishing.products_id = p.products_id";
}
if (in_array('prodean', $field_used_in_filter)) {
$query .= " LEFT JOIN products_articles pa ON pa.products_id = p.products_id";
}
if (in_array('prodstyles', $field_used_in_filter)) {
//$query .= " LEFT JOIN styles_has_products shp ON shp.products_id = p.products_id";
$query .= " INNER JOIN (";
$query .= " SELECT shp.products_id, CONCAT(',', GROUP_CONCAT(shp.styles_id), ',') AS styles_id";
$query .= " FROM styles_has_products shp";
$query .= " GROUP BY shp.products_id";
$query .= " HAVING " . implode(" AND ", $filter_subquery['prodstyles']);
$query .= ") subquery_styles ON subquery_styles.products_id = p.products_id";
}
if (in_array('prodcolor', $field_used_in_filter)) {
$query .= " LEFT JOIN products_to_color_pattern ptcp ON ptcp.products_id = p.products_id";
}
if (in_array('iskilled', $field_used_in_filter) || in_array('datekilled', $field_used_in_filter)) {
$query .= " LEFT JOIN products_removed pr ON pr.products_id = p.products_id";
}
//if(in_array('havenoclearimage', $field_used_in_filter)||in_array('haveclearimage', $field_used_in_filter)) $query .= " LEFT JOIN products_clear_images pci ON pci.products_id = p.products_id";
if (in_array('prodclearimage', $field_used_in_filter)) {
$query .= " LEFT JOIN products_clear_images pci ON pci.products_id = p.products_id";
}
if (in_array('watchlistof', $field_used_in_filter)) {
//$query .= " LEFT JOIN minierp_users_watch_products muwp ON muwp.products_id = p.products_id";
$query .= " INNER JOIN (";
$query .= " SELECT muwp.products_id, CONCAT(',', GROUP_CONCAT(muwp.user_id), ',') AS user_id";
$query .= " FROM minierp_users_watch_products muwp";
$query .= " GROUP BY muwp.products_id";
$query .= " HAVING " . implode(" AND ", $filter_subquery['watchlistof']);
$query .= ") subquery_watchlist ON subquery_watchlist.products_id = p.products_id";
}
$query .= " LEFT JOIN products_stock_temp pst ON pst.products_id=p.products_id";
//$query .= " JOIN (";
//$query .= " SELECT products_id, SUM(stock) AS hh_stock";
//$query .= " FROM products_stock";
//$query .= " WHERE jng_warehouses_id=".WAREHOUSE_ID_PRODUCTS_HAMBURG;
//$query .= " GROUP BY products_id";
//if(in_array('hhstock', $field_used_in_filter)) $query .= " HAVING ".implode(" AND ", $filter_subquery['hhstock']);
//$query .= ") subquery_hhstock ON subquery_hhstock.products_id=p.products_id";
foreach ($filter_join as $fjv) {
$query .= $fjv;
}
$filter_active_status = in_array('iskilled', $field_used_in_filter) ? "1=1" : "p.active_status='1'";
$query .= " WHERE {$filter_active_status}";
if ($filter != '') {
$query .= $filter;
}
$query .= ") dt ";
$query .= " GROUP BY products_id";
if ($filter_having != '') {
$query .= " HAVING " . $filter_having;
}
$query .= " ORDER BY ";
if ($res_sorts != '' && !is_null($res_sorts)) {
$query .= $res_sorts;
} else {
if (in_array('iskilled', $field_used_in_filter)) {
$query .= " removed_date DESC";
} else {
$query .= " products_id";
}
}
//save query to a log file
$logger = new logger('classes', 'products_search_query_executed');
$logger->write($query);
$logger->close();
return $query;
}
/**
* Delete booking order, the history will be logged by logger object
* @param String $username
* @return Int ID of the deleted booking
*/
public function delete($username)
{
$q = tep_db_query("DELETE FROM products_stock_booking_items WHERE booking_id={$this->id}");
$q = tep_db_query("DELETE FROM products_stock_booking WHERE booking_id={$this->id}");
if ($this->status == '0') {
$this->recalcActiveBooking();
}
use_class('logger');
$logger = new logger('classes', 'stock_booking', false);
$logger->write("Booking ID {$this->id} ({$this->name}) is deleted by {$username}");
$logger->close();
$this->id = null;
$this->setDetail();
return $this->id;
}
/**
* Send file to target folder from opened ftp connection
* @param String $filename
* @param String $target_filename
* @return Boolean $result
*/
public function send($filename, $target_filename)
{
$result = false;
$failure1 = false;
$failure2 = false;
$logger = new logger('classes', 'tradebyte_send');
if ($this->ftps_login && (file_exists($filename) && !is_dir($filename))) {
$target_filename_upload = "{$target_filename}.upload";
//MAKE STREAMING FILE AS .upload SO IT WON'T DIRECTLY PROCESSED BY TRADEBYTE
//$status = ftp_put($this->conn_id, $target_filename_upload, $filename, FTP_ASCII);
$local_filesize = filesize($filename);
$res_target = @fopen("ssh2.sftp://{$this->sftp}/{$target_filename_upload}", 'w');
if (!$res_target) {
throw new Exception("Could not create file {$target_filename_upload} on TB server");
$logger->write("Could not create file {$target_filename_upload} on TB server");
$failure1 = true;
}
$res_local = @fopen($filename, 'r');
if (!$res_local) {
throw new Exception("Could not read file {$filename} from local source");
$logger->write("Could not read file {$filename} from local source");
$failure2 = true;
}
if (!$failure1 && !$failure2) {
$written_bytes = stream_copy_to_stream($res_local, $res_target);
if ($written_bytes >= $local_filesize) {
//RENAME xxx.xml.upload TO xxx.xml FOR ALLOWING TRADEBYTE TO PROCESS THE FINISHED UPLOAD XML FILE
//ftp_rename($this->conn_id, $target_filename_upload, $target_filename);
ssh2_sftp_rename($this->sftp, $target_filename_upload, $target_filename);
$result = true;
$logger->write("Success send {$filename} to Tradebyte");
}
}
fclose($res_target);
fclose($res_local);
}
$logger->close();
return $result;
}
public static function queryDepotSummary()
{
set_time_limit(0);
self::loadDIOHsettings();
$depot_summary = load_config('depot-summary');
$totalstars = 3;
$goodstock_tolerance = 0;
use_class('logger');
$logger = new logger('classes', 'product');
$logger->write('QUERY DEPOT SUMMARY!');
$logger->write('Previous Summary Result:');
foreach ($depot_summary as $key => $value) {
$logger->write("{$key} = {$value}");
}
$q = "SELECT ps.products_id, ps.articles_id, p.stars, (ps.stock-ps.booking_active) AS stock_available";
$q .= " FROM products p";
$q .= " LEFT JOIN products_stock ps ON ps.products_id=p.products_id";
$q .= " WHERE p.products_status='1' OR ps.stock>0";
$q .= " ORDER BY products_id, articles_id";
$r = tep_db_query($q);
$product = null;
$overstock = array();
$shortages = array();
$goodstock = array();
for ($s = 0; $s <= $totalstars; $s++) {
$overstock[$s]['products'] = array();
$overstock[$s]['articles'] = 0;
$overstock[$s]['quantity'] = 0;
$shortages[$s]['products'] = array();
$shortages[$s]['articles'] = 0;
$shortages[$s]['quantity'] = 0;
$goodstock[$s]['products'] = array();
$goodstock[$s]['articles'] = 0;
$goodstock[$s]['quantity'] = 0;
}
$rowcounter = 0;
while ($row = tep_db_fetch_array($r)) {
$rowcounter++;
$stockAvailable = $row['stock_available'] < 0 ? 0 : $row['stock_available'];
if (product::$diohStopLevel[$row['stars']] == 0) {
//NO TARGET, NO NEED TO COUNT
if ($stockAvailable == 0) {
$goodstock[$row['stars']]['products'][] = $row['products_id'];
$goodstock[$row['stars']]['articles']++;
} else {
$overstock[$row['stars']]['products'][] = $row['products_id'];
$overstock[$row['stars']]['articles']++;
$overstock[$row['stars']]['quantity'] += $stockAvailable;
}
} else {
if (is_null($product) || is_object($product) && $product->id != $row['products_id']) {
$product = new product($row['products_id']);
}
$stockTarget = $product->getDIOHstockTarget($row['articles_id']);
$stockDiff = $stockAvailable - $stockTarget;
if ($stockDiff > $goodstock_tolerance) {
//OVERSTOCK
$overstock_quantity = $stockDiff - $goodstock_tolerance;
$overstock[$product->stars]['products'][] = $product->id;
$overstock[$product->stars]['articles']++;
$overstock[$product->stars]['quantity'] += $overstock_quantity;
$goodstock[$product->stars]['quantity'] += $stockAvailable - $overstock_quantity;
} elseif ($stockDiff < -1 * $goodstock_tolerance) {
//SHORTAGE
$shortages_quantity = abs($stockDiff) - $goodstock_tolerance;
$shortages[$product->stars]['products'][] = $product->id;
$shortages[$product->stars]['articles']++;
$shortages[$product->stars]['quantity'] += $shortages_quantity;
$shortages[$product->stars]['quantity'] += $stockAvailable;
} else {
$goodstock[$product->stars]['products'][] = $product->id;
$goodstock[$product->stars]['articles']++;
$goodstock[$product->stars]['quantity'] += $stockAvailable;
}
}
// echo '. ';
}
$logger->write('Total rows from query = ' . $rowcounter);
for ($s = 0; $s <= $totalstars; $s++) {
$overstock[$s]['products'] = count(array_unique($overstock[$s]['products']));
$shortages[$s]['products'] = count(array_unique($shortages[$s]['products']));
$goodstock[$s]['products'] = count(array_unique($goodstock[$s]['products']));
}
// echo '<pre>';
// var_dump($goodstock);
// var_dump($overstock);
// var_dump($shortages);
// echo '</pre>';
$depot_summary['lastrun'] = time();
$depot_summary['diohTarget-0'] = self::$diohStopLevel[0];
$depot_summary['diohTarget-1'] = self::$diohStopLevel[1];
$depot_summary['diohTarget-2'] = self::$diohStopLevel[2];
$depot_summary['diohTarget-3'] = self::$diohStopLevel[3];
$depot_summary['overstock-3-products'] = $overstock[3]['products'];
$depot_summary['overstock-3-articles'] = $overstock[3]['articles'];
$depot_summary['overstock-3-quantity'] = $overstock[3]['quantity'];
$depot_summary['overstock-2-products'] = $overstock[2]['products'];
$depot_summary['overstock-2-articles'] = $overstock[2]['articles'];
$depot_summary['overstock-2-quantity'] = $overstock[2]['quantity'];
$depot_summary['overstock-1-products'] = $overstock[1]['products'];
$depot_summary['overstock-1-articles'] = $overstock[1]['articles'];
$depot_summary['overstock-1-quantity'] = $overstock[1]['quantity'];
$depot_summary['overstock-0-products'] = $overstock[0]['products'];
$depot_summary['overstock-0-articles'] = $overstock[0]['articles'];
$depot_summary['overstock-0-quantity'] = $overstock[0]['quantity'];
$depot_summary['goodstock-3-products'] = $goodstock[3]['products'];
$depot_summary['goodstock-3-articles'] = $goodstock[3]['articles'];
$depot_summary['goodstock-3-quantity'] = $goodstock[3]['quantity'];
$depot_summary['goodstock-2-products'] = $goodstock[2]['products'];
$depot_summary['goodstock-2-articles'] = $goodstock[2]['articles'];
$depot_summary['goodstock-2-quantity'] = $goodstock[2]['quantity'];
$depot_summary['goodstock-1-products'] = $goodstock[1]['products'];
$depot_summary['goodstock-1-articles'] = $goodstock[1]['articles'];
$depot_summary['goodstock-1-quantity'] = $goodstock[1]['quantity'];
$depot_summary['goodstock-0-products'] = $goodstock[0]['products'];
$depot_summary['goodstock-0-articles'] = $goodstock[0]['articles'];
$depot_summary['goodstock-0-quantity'] = $goodstock[0]['quantity'];
$depot_summary['shortages-3-products'] = $shortages[3]['products'];
$depot_summary['shortages-3-articles'] = $shortages[3]['articles'];
$depot_summary['shortages-3-quantity'] = $shortages[3]['quantity'];
$depot_summary['shortages-2-products'] = $shortages[2]['products'];
$depot_summary['shortages-2-articles'] = $shortages[2]['articles'];
$depot_summary['shortages-2-quantity'] = $shortages[2]['quantity'];
$depot_summary['shortages-1-products'] = $shortages[1]['products'];
$depot_summary['shortages-1-articles'] = $shortages[1]['articles'];
$depot_summary['shortages-1-quantity'] = $shortages[1]['quantity'];
$depot_summary['shortages-0-products'] = $shortages[0]['products'];
$depot_summary['shortages-0-articles'] = $shortages[0]['articles'];
$depot_summary['shortages-0-quantity'] = $shortages[0]['quantity'];
save_config('depot-summary', $depot_summary);
$logger->write('New value successfully saved');
$logger->close();
// echo 'Done!';
return $depot_summary;
}
