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