public static function retrieveDailyAverageSoldExcludingPromotionOrdersQuery($date_start, $total_days_to_lookback, $pid_aid_filter = '')
{
$sp_list = SalesPartner::getOldSalesPartnerFunction()->retrieveList('use_in_forecast = 1');
$sp_filter = implode(',', array_keys($sp_list));
//WE MUST REDUCE 1 DAY BECAUSE "date - X days" when both days are
//considered will give (X+1) total days instead.
$total_days_to_lookback_calc = $total_days_to_lookback - 1;
//start and end is reversed because we're looking backward
$date_start_query = date('Y-m-d', strtotime($date_start));
$date_end_query = date('Y-m-d', strtotime("{$date_start_query} -{$total_days_to_lookback_calc} days"));
$q = "SELECT products_id, articles_id, SUM(total_sold) AS total_sold FROM (";
//SP Query
$q .= " SELECT joi.products_id, joi.products_articles_id AS articles_id, SUM(joi.order_quantity) AS total_sold";
$q .= " FROM jng_sp_orders jo";
$q .= " INNER JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_id=jo.jng_sp_orders_id AND joi.status NOT IN (10,12)";
$q .= " WHERE jo.jng_sp_id IN ({$sp_filter})";
$q .= " AND joi.shipping_window_open<='{$date_start_query}' AND joi.shipping_window_open>='{$date_end_query}'";
if ($pid_aid_filter != '') {
$q .= " AND {$pid_aid_filter}";
}
$q .= " AND NOT " . isPromotionalOrderQuery('SP');
$q .= " GROUP BY products_id, articles_id";
$q .= " UNION ALL ";
//JG Query
$date_start_query .= ' 23:59:59';
$date_end_query .= ' 00:00:00';
$q .= " SELECT op.products_id, op.products_articles_id AS articles_id, SUM(op.products_quantity) AS total_sold";
$q .= " FROM orders o";
$q .= " INNER JOIN orders_products op ON op.orders_id=o.orders_id AND op.status NOT IN (10,12)";
$q .= " WHERE o.date_purchased<='{$date_start_query}' AND o.date_purchased>='{$date_end_query}'";
if ($pid_aid_filter != '') {
$q .= " AND {$pid_aid_filter}";
}
$q .= " AND NOT " . isPromotionalOrderQuery('JG');
$q .= " GROUP BY products_id, articles_id";
$q .= ") temp_table";
$q .= " GROUP BY products_id, articles_id";
$q .= " ORDER BY products_id, articles_id";
$r = tep_db_query($q);
$result = array();
while ($row = tep_db_fetch_array($r)) {
$row['daily_average'] = round($row['total_sold'] / $total_days_to_lookback, 2);
$result[] = $row;
}
return $result;
}
$products_ean[] = $ean_code;
}
}
}
} else {
$result['status'] = 'ERROR';
$result['message'] = $statusName[$item['status']];
}
}
} else {
$result['status'] = 'ERROR';
$result['message'] = 'Order not found, please rescan!';
}
} elseif ($type_length == 5 || $type == 'AM' || $type == 'BV' || $type == 'OT') {
if ($type_length == 5) {
$obj_sp = SalesPartner::loadObjectFromSPCode($type);
$jng_sp_id = $obj_sp->id;
} else {
$jng_sp_id_translation = array('AM' => '5,9', 'BV' => '7', 'OT' => '11');
$jng_sp_id = $jng_sp_id_translation[$type];
}
$q = "SELECT joi.products_ean, joi.order_quantity FROM jng_sp_orders jo";
$q .= " INNER JOIN jng_sp_orders_items joi ON joi.jng_sp_orders_id=jo.jng_sp_orders_id AND joi.status={$inhh_status} AND print_count=1";
$q .= " WHERE jo.jng_sp_id IN ({$jng_sp_id}) AND jo.order_id='{$item_id}'";
$q .= " ORDER BY joi.products_ean";
$r = tep_db_query($q);
if (tep_db_num_rows($r) > 0) {
$products_found = true;
while ($i = tep_db_fetch_array($r)) {
$qty = intval($i['order_quantity']);
for ($x = 1; $x <= $qty; $x++) {
if (isset($_POST['me_action'])) {
if ($_POST['me_action'] == 'SETSPUPLOADSTATUS') {
if ($_POST['submit'] == 'Reupload') {
$upload_sp_id = $_POST['upload_sp_id'];
header("Location: ?open=sp-catalog-generator&jng_sp_id={$upload_sp_id}&reupload=true");
exit;
} else {
$upload_id = tep_db_prepare_input($_POST['upload_id']);
$status = $_POST['submit'] == 'Success' ? 'S' : 'F';
$class_ju->updateStatus($upload_id, $status, $session_userinfo['username']);
header("Location: ?open=sp-catalog");
exit;
}
} elseif ($_POST['me_action'] == 'COUNTCATSTATUS') {
$category_top_id = $_POST['top_cat_id'];
$sp = new SalesPartner($jng_sp_id);
$status_arr = array('all', 'new', 'update', 'deactivate');
$total_catalog_status = $sp->getCatalogTotalCount($status_arr, $category_top_id);
$catalog_status_combo_box = '<select id="status-catalog" style="width:180px;"><option value="0">Choose Catalog Status..</option>' . loadComboListFromArray($total_catalog_status, null, null, false) . '</select>';
echo $catalog_status_combo_box;
exit;
}
}
//START TEMPLATE
//CHECK IF UPLOAD IS ALREADY MADE
$status = $class_ju->retrieveNotSetStatus($jng_sp_id, 'CAT,PRI');
if ($status !== false) {
// Instead shows the confirmation of Success/Fail for last generated upload file, just set the uploaded file into Success 'S'
$class_ju->updateStatus($status['jng_sp_upload_id'], 'S', $session_userinfo['username']);
header("Location: ?open=sp-catalog");
} else {
<?php
use_class('SalesPartner');
use_class('jng_sp');
$class_sp = new jng_sp();
//Redirect to SP List if no ID is to open
if (isset($_GET['id']) && $_GET['id'] != '') {
$salespartner_id = tep_db_prepare_input($_GET['id']);
} else {
header('Location: ?open=sp-list');
exit;
}
$obj_sp = new SalesPartner($salespartner_id);
//SUBMIT ACTION
if (isset($_POST['me_action'])) {
if ($_POST['me_action'] == 'AMVDSAVECONFIG') {
$config = array();
$config['stock-status-ds'] = '+' . intval($_POST['ssds']) . ' days';
$config['stock-status-okfg'] = '+' . intval($_POST['ssokfg']) . ' days';
$config['stock-status-ns'] = '+' . intval($_POST['ssns']) . ' days';
save_config('amazon-vd-settings', $config);
} elseif ($_POST['me_action'] == 'SALESPARTNERDELETE') {
/*
$exist_query = tep_db_query("SELECT jng_sp_id FROM jng_sp WHERE jng_sp_id=$salespartner_id");
if(tep_db_num_rows($exist_query)>0) {
$check1_query = tep_db_query("SELECT * FROM minierp_groups_credentials WHERE salespartners_id=$salespartner_id");
$check1 = (tep_db_num_rows($check1_query)==0);
if($check1) {
tep_db_query("DELETE FROM jng_sp WHERE jng_sp_id=$salespartner_id");
$messagebox->add("Module ID: <strong>$salespartner_id</strong> is successfully deleted.");
$salespartner_id = 'new';
$g['w080 3'] = 'Code';
$g['w080 11'] = 'Type';
$g['w200 4'] = 'Website';
$g['w080 5'] = 'Tradebyte';
$g['w080 6'] = 'Active';
$g['w080 7'] = 'Use for<br />Forecast';
$g['w080 8'] = 'Payone';
$g['w080 9'] = 'Easylog';
$g['w080 10'] = 'Amazon<br />Logistic';
//$g['date'] = 'Default<br />Delivery<br />Time';
$g['w300 tac 11'] = 'Set Parameters / Mapping';
$salespartners[] = $g;
$counter = 0;
foreach ($sp_list as $row) {
$counter++;
$obj_sp = new SalesPartner($row['jng_sp_id']);
$detail = '?open=sp-detail&id=' . $row['jng_sp_id'] . '&hidemenu=true';
$deldays = intval($row['delivery_time']) . ' days';
$tb = $row['tb_channel'] == '' ? '-' : $row['tb_channel'];
$g = array();
//$g['id'] = '<a href="'.$detail.'" class="view_webpage" title="Manage sales partner detail">'.$row['jng_sp_id'].'</a>';
$g['tac 1'] = $counter;
$g['2'] = '<div style="float:right;margin-right:10px;">ID = ' . $row['jng_sp_id'] . '</div><a href="' . $detail . '" class="view_webpage" title="Manage sales partner detail">' . $row['name'] . '</a>';
if ($row['active_status'] == '1') {
$info = array();
//$info[] = '<a class="view_webpage" href="?open=analysis-vc-params&jng_sp_id='.$row['jng_sp_id'].'&hidemenu=true">VC</a>';
$info[] = '<a class="view_webpage" href="?open=sp-detail-cat&jng_sp_id=' . $row['jng_sp_id'] . '&hidemenu=true">Brand</a>';
$info[] = '<a class="view_webpage" href="?open=sp-detail-cat&jng_sp_id=' . $row['jng_sp_id'] . '&hidemenu=true#ui-tabs-2">Nav (SubCat)</a>';
$info[] = '<a class="view_webpage" href="?open=sp-detail-cat&jng_sp_id=' . $row['jng_sp_id'] . '&hidemenu=true#ui-tabs-3">Nav (Cat)</a>';
$info[] = '<a class="view_webpage" href="?open=sp-detail-cat&jng_sp_id=' . $row['jng_sp_id'] . '&hidemenu=true#ui-tabs-4">Color</a>';
$info[] = '<a class="view_webpage" href="?open=sp-detail-cat&jng_sp_id=' . $row['jng_sp_id'] . '&hidemenu=true#ui-tabs-5">Material</a>';
$obj_ws->setCellValueExplicit('C' . $row, $an_code, PHPExcel_Cell_DataType::TYPE_STRING);
$obj_ws->setCellValue('D' . $row, $an_size);
$obj_ws->setCellValue('E' . $row, $sp_commission);
$obj_ws->setCellValue('F' . $row, $neworder_price);
$obj_ws->setCellValue('G' . $row, $neworder_qty);
$obj_ws->setCellValue('H' . $row, $cancelorder_price);
$obj_ws->setCellValue('I' . $row, $cancelorder_qty);
$obj_ws->setCellValue('J' . $row, $sentorder_price);
$obj_ws->setCellValue('K' . $row, $sentorder_qty);
$obj_ws->setCellValue('L' . $row, $receiveorder_price);
$obj_ws->setCellValue('M' . $row, $receiveorder_qty);
$obj_ws->setCellValue('N' . $row, $return_price);
$obj_ws->setCellValue('O' . $row, $return_qty);
}
$jng_sp_id = 2;
$sp_otto = new SalesPartner($jng_sp_id);
$sp_external_id = $sp_otto->getExternalID();
$sp_commission = 0.19;
$year = tep_db_prepare_input($_GET['year']);
$month = tep_db_prepare_input($_GET['month']);
//Master file is protected and can not be updated by PHPExcel (halted)
//$template_file = DIR_FS_SUPPORTS . 'Kooperationspartner-Abrechnung_Muster.xls';
$template_file = DIR_FS_SUPPORTS . 'Kooperationspartner-Abrechnung_Kopie.xls';
$obj_excel_reader = new PHPExcel_Reader_Excel5();
if ($obj_excel_reader->canRead($template_file)) {
//LOAD TEMPLATE FILE
$logger->write('Load template file: ' . $template_file);
$obj_excel = $obj_excel_reader->load($template_file);
//Fill in general data
$obj_excel->setActiveSheetIndex(0);
$obj_ws = $obj_excel->getActiveSheet();
header("Location: ?open=sp-catalog{$hidemenu}");
exit;
}
if (!$is_directdownload) {
echo '<html>';
echo '<body>';
echo '<h1>CAT XML Generator</h1>';
echo '<div>';
}
$bgst = Product::getOldProductFunction()->retrieveBestGoodSettings();
$hide_extreme_flag = $bgst['hidenostock_xtreme'] == '1';
$hide_hard_flag = $bgst['hidenostock_hard'] == '1';
$hide_nostock_flag = $bgst['hidenostock_somesp'] == '1';
$products_stock = Product::getOldProductFunction()->stockRetrieveAll(WAREHOUSE_ID_PRODUCTS_HAMBURG);
if ($is_use_new_function) {
$sp = new SalesPartner($jng_sp_id);
$products = $sp->getProductsCatalog($catalog_status, $category_top_id);
/* For Direct Testing (Don't forget also comment ...$sp->getProductsCatalog... above)
$products_id = '53727,53773,54197,54864,61608,61759,62014';
$products = $sp->getProductsCatalogByProductsID($catalog_status, $category_top_id, $products_id);
*/
} else {
$products = $class_jc->retrieveCatalogForUpload($jng_sp_id);
//$products = $class_jc->retrieveCatalogForUpload($jng_sp_id,false,"jc.products_id IN (74458,74459,74468,74469,74470,74482,74484,73637,73664,73672,73694,73699,73700,73701,73705,73710,73713,73720,73733,73735,73737,73826,74142,74155,74156,74159,74165,74171,74177,74183,74187,74190,74574,74683)");
}
$total_products_exported = count($products);
if ($total_products_exported == 0) {
$content = 'There is no active product to be exported';
} else {
$error = false;
//EAN IS NOW A REQUIREMENT FOR ALL SP, SO MUST CHECK FOR ALL FIRST
use_class('element');
use_class('ProductAttribute');
use_class('jng_sp_orders');
use_class('elements_stock');
use_class('elements_order');
$class_s = new styles();
$class_jo = new jng_sp_orders();
$class_es = new elements_stock();
//$class_pb = new products_brand();
$class_pm = new products_minierp();
//$class_jc = new jng_sp_catalog();
//$class_do = new depot_orders();
//$class_pmat = new products_materials();
//$class_pa = new products_articles();
//$class_e = new element();
$sp = new SalesPartner('1004');
$sp->getProductsCatalog('all', '1');
/*
$prev_month_day_start = date('Y-m-01 00:00:00', strtotime(date('Y-m')." -2 month"));
$prev_month_day_end = date('Y-m-t 23:59:59', strtotime($prev_month_day_start));
echo $prev_month_day_start;
echo $prev_month_day_end;
*/
//$pid_array = array('74487','51215','74668');
/*
$pq = "SELECT pd.products_id, pd2.diamond_total_weight FROM products_description pd";
$pq .= " LEFT JOIN jng_sp_catalog jc ON jc.products_id = pd.products_id";
$pq .= " LEFT JOIN products_description2 pd2 ON pd2.products_id = pd.products_id";
$pq .= " LEFT JOIN products p ON p.products_id = pd.products_id";
//$pq .= " WHERE jc.jng_sp_id = 5 AND pd.language_id = 2 AND p.active_status = 1 AND jc.active_status = 1";
$pq .= " WHERE pd.language_id = 2 AND p.active_status = 1 AND jc.active_status = 1";
