//Set the date range to calculate
if (isset($_GET['start']) && isset($_GET['end'])) {
//MANUAL SET DATE RANGE
$date_start = date('Y-m-d', strtotime($_GET['start']));
$date_end = date('Y-m-d', strtotime($_GET['end']));
} else {
$lookback = '60 days';
$date_end = date('Y-m-d', strtotime('yesterday'));
$date_start = date('Y-m-d', strtotime("{$date_end} -{$lookback}"));
}
$logger->write('Date range is ' . $date_start . ' until ' . $date_end);
//PROCESS JNG, use $jng_sp_id = 0
$jng_sp_id = '0';
$logger->write('Delete existing data from J&G');
ReportCOGS::clear($jng_sp_id, $date_start, $date_end);
$logger->write('Query orders from J&G and process as data');
$total_rows = ReportCOGS::queryAndAddB2COrders($jng_sp_id, $date_start, $date_end);
$logger->write('Total orders found: ' . $total_rows);
//PROCESS B2C SP
foreach ($sp_ids_included as $jng_sp_id) {
$logger->write('Delete existing data from SPID ' . $jng_sp_id);
ReportCOGS::clear($jng_sp_id, $date_start, $date_end);
$logger->write('Query orders from SPID ' . $jng_sp_id . ' and process as data');
$total_rows = ReportCOGS::queryAndAddB2COrders($jng_sp_id, $date_start, $date_end);
$logger->write('Total orders found: ' . $total_rows);
}
$logger->write('Optimize report_cogs table');
ReportCOGS::optimizeDatabaseTable();
$logger->close();
echo 'Done!';
tep_db_close();
} else {
$sp_detail = $class_sp->retrieveDetail($spid);
}
if (!is_array($products)) {
$products = explode(',', $products);
}
$ajaxResult = array();
$ajaxResult['period'] = 'p-' . $period;
$ajaxResult['period_text'] = $sp_detail['package_prefix'] . '<br />' . substr($period_start_raw, 0, 5) . ' - ' . substr($period_end_raw, 0, 5);
if ($level == 'p') {
$cogs_result = ReportCOGS::retrieveSummaryByProducts($products, $spid, $period_start, $period_end);
foreach ($products as $pid) {
$ajaxResult['row-' . $pid] = intval($cogs_result[$pid]['total_quantity']);
}
} else {
$cogs_result = ReportCOGS::retrieveSummaryByArticles($products, $spid, $period_start, $period_end);
$palist = $class_pa->retrieveAllArticlesByProducts($products);
foreach ($palist as $pid => $pdata) {
$row_content = '<table align="center" border="0"' . ' cellpadding="0" cellspacing="0">';
foreach ($pdata as $aid => $adata) {
if ($level == 'l') {
$left = $adata['length'] == 0 ? '<span class="notice">None</span>' : textLength($adata['length']);
} else {
$left = $adata['ean'];
}
$row_content .= '<tr><td>' . $left . '</td><td>=</td><td>' . intval($cogs_result[$adata['ean']]['total_quantity']) . '</td></tr>';
}
$row_content .= '</table>';
$ajaxResult['row-' . $pid] = $row_content;
}
}
$header['w100 6'] = 'Gross Profit';
$header['w100 7'] = 'Open Prepayment Orders';
$header['w100 8'] = 'Open Prepayment Value';
$table[] = $header;
$total_sales = 0;
$total_sold = 0;
$total_cogs = 0;
$total_net_sales = 0;
$total_gross_profit = 0;
$total_prepayment_count = 0;
$total_prepayment_value = 0;
for ($d = 0; $d < $weekly_days; $d++) {
$date_ts = strtotime($date_start . " -{$d} days");
$date_display = date('d-M-y', $date_ts);
$date_filter = date('Y-m-d', $date_ts);
$daily_data = ReportCOGS::retrieveSummary($jng_sp_id, $date_filter, $date_filter);
$total_sales += $daily_data['total_price'];
$total_sold += $daily_data['total_quantity'];
$total_cogs += $daily_data['total_cogs'];
$total_net_sales += $daily_data['total_net_sales'];
$total_gross_profit += $daily_data['total_gross_profit'];
if (!isset($prepayments[$date_filter])) {
$prepayment_count = 0;
$prepayment_value = 0;
} else {
$prepayment_count = $prepayments[$date_filter]['count'];
$prepayment_value = $prepayments[$date_filter]['value'];
}
$total_prepayment_count += $prepayment_count;
$total_prepayment_value += $prepayment_value;
$avpr = $daily_data['total_quantity'] == 0 ? 0 : $daily_data['total_price'] / $daily_data['total_quantity'];
if ($show_minimum_data) {
$price_uvp = 'N/A';
$price_default = 'N/A';
$price_selling_old_amazon = 'N/A';
$price_selling_amazon = 'N/A';
$mat_exp = $p->material_expense;
$cogs = 'N/A';
} else {
$price_uvp = $p->getPriceUVP();
$price_default = $p->getPriceDefault();
$price_selling_old_amazon = $p->getPriceSellingOld($sp_id_amazon);
$price_selling_amazon = $p->getPriceSelling($sp_id_amazon);
$mat_exp = $p->material_expense;
$cogs = $p->getProductCOGSValue();
$report_cogs_lastyear = ReportCOGS::retrieveSummaryByArticles($pids, '*', $last_year_start, $last_year_end);
$report_cogs_thisyear = ReportCOGS::retrieveSummaryByArticles($pids, '*', $this_year_start, $this_year_end);
}
foreach ($p->articles as $aid => $a) {
if ($p->isArticleKilled($aid)) {
continue;
}
$article_counter++;
$ean = $p->getEAN($aid);
$size = $p->getLengthOrSizeAsText($aid);
if ($show_minimum_data) {
$stock_available = 'N/A';
$booked = 'N/A';
$last_year_sold_qty = 'N/A';
$last_year_net_sales = 'N/A';
$last_year_gross_profit = 'N/A';
$this_year_sold_qty = 'N/A';
$cogs = Product::getClosestCOGSOnSpecificDate($products_id, $date);
$matexp_total = $qty * $matexp;
$cogs_total = $qty * $cogs;
if ($correction_mode) {
ReportCOGS::addToExistingData($jng_sp_id, $date, $ean, $complexity, $qty, $price_total, $matexp_total, $cogs_total);
} else {
if ($qty >= 0) {
$qty_sold = $qty;
$qty_return = 0;
$price_total_before_return = $price_total;
} else {
$qty_sold = 0;
$qty_return = abs($qty);
$price_total_before_return = 0;
}
ReportCOGS::add($jng_sp_id, $date, $ean, $products_id, $articles_id, $complexity, $qty_sold, $qty_return, $price_total, $matexp_total, $cogs_total, 0, jng_sp::CASH_DISCOUNT_B2B, $price_total_before_return);
}
}
}
}
}
}
$messagebox->add('Import of <strong>' . $sp_list[$jng_sp_id]['name'] . '</strong> customer orders "' . $file['name'] . '" Succesful!', 'green');
} else {
$messagebox->add('Import Failed!' . ' Only XML and Excel File are allowed,' . ' please check your file: ' . $file['type']);
}
} else {
$messagebox->add('Sorry, Import function for <strong>' . $sp_list[$jng_sp_id]['name'] . '</strong> is not yet implemented');
}
}
$sp_options = array();
//yesterday
$date_start = date('Y-m-d', strtotime('-2 days'));
$date_end = $date_start;
$daily_data = ReportCOGS::retrieveSummary($jng_sp_id, $date_start, $date_end);
$sales_ld = displayCurrency(CURRENCY_DEFAULT, $daily_data['total_price'], false);
$grossprofit_ld = number_format($daily_data['average_gross_profit_percent'] * 100, 1) . ' %';
//last week
$date_start = date('Y-m-d', strtotime('last sunday -6 days'));
$date_end = date('Y-m-d', strtotime('last sunday'));
$daily_data = ReportCOGS::retrieveSummary($jng_sp_id, $date_start, $date_end);
$sales_lw = displayCurrency(CURRENCY_DEFAULT, $daily_data['total_price'], false);
$grossprofit_lw = number_format($daily_data['average_gross_profit_percent'] * 100, 1) . ' %';
//last month
$date_start = date('Y-m-01', strtotime('last month'));
$date_end = date('Y-m-t', strtotime('last month'));
$daily_data = ReportCOGS::retrieveSummary($jng_sp_id, $date_start, $date_end);
$sales_lm = displayCurrency(CURRENCY_DEFAULT, $daily_data['total_price'], false);
$grossprofit_lm = number_format($daily_data['average_gross_profit_percent'] * 100, 1) . ' %';
//bestseller
$q = "SELECT COUNT(products_id) AS total FROM products WHERE active_status=1 AND stars=3";
$r = tep_db_query($q);
$row = tep_db_fetch_array($r);
$total_bestseller = $row['total'];
//Draw content
$wall = new wall('0');
$content .= '<div id="dashboard">' . '<div id="db-block-left" style="position:absolute;">' . '<div id="db-block-sales" style="margin-bottom:40px">' . '<h2>Sales Summary (' . CURRENCY_DEFAULT . ')</h2>' . '<ul class="table-list">' . '<li><span class="label">Yesterday</span><span class="value">' . $sales_ld . '</span></li>' . '<li><span class="label">Last Week</span><span class="value">' . $sales_lw . '</span></li>' . '<li><span class="label">Last Month</span><span class="value">' . $sales_lm . '</span></li>' . '</ul>' . '</div>' . '<div id="db-block-grossprofit" style="margin-bottom:40px">' . '<h2>Gross Profit Margin</h2>' . '<ul class="table-list">' . '<li><span class="label">Yesterday</span><span class="value">' . $grossprofit_ld . '</span></li>' . '<li><span class="label">Last Week</span><span class="value">' . $grossprofit_lw . '</span></li>' . '<li><span class="label">Last Month</span><span class="value">' . $grossprofit_lm . '</span></li>' . '</ul>' . '</div>' . '<div id="db-block-bestseller" style="margin-bottom:40px">' . '<h2>Bestseller Management</h2>' . '<ul class="table-list">' . '<li><span class="value" style="">' . $total_bestseller . '</span><span class="label">Total 3 stars products</span></li>' . '<li><span class="value" title="coming soon!">NA</span><span class="label">Bestseller not on stock</span></li>' . '</ul>' . '</div>' . '</div>' . '<div id="db-block-middle" style="position:absolute;left:320px;margin-bottom:20px;">' . '<h1>News</h1>' . $wall->draw(2, 'width:500px;margin-bottom:20px;', true, 'Share something new...') . '</div>' . '<div id="db-block-right" class="w500" style="position:absolute;left:870px;">' . '<h2>Recent Orders</h2>' . '<div id="recent-bought"></div>' . '</div>' . '</div>';
#dashboard
$javascript = wall::drawWallJSaction() . '
var recent_bought_firstload = true;
function compareProduct(xmlResult, txtStatus) {
if(txtStatus=="success") {
break;
case 'avg_grossprofit_pct_ge':
$filter_grossprofit_pct_ge = $fval;
break;
case 'avg_grossprofit_pct_ge':
$filter_grossprofit_pct_le = $fval;
break;
}
}
}
}
$products_total = count($products);
if ($products_total == 0) {
$search_result = '<h3 class="red">No Products/Articles Found</h3>';
} else {
$summaries = ReportCOGS::retrieveSummaryByProducts($products, $jng_sp_id, $start_date, $end_date, implode(' AND ', $filters), implode(' AND ', $havings), implode(',', $sorting));
//need to make sure that all filtered product from products analysis are included even if no report_cogs data is found for the product
//for that we will loop the products var (from pids) instead of summaries var. But we still need to maintain the order from summaries.
//so we created a new var products_sorted to manage it. First add all pids from summaries in the same order
$products_sorted = array_keys($summaries);
//then loop the products array to add pids which are not queried in summaries
foreach ($products as $pid) {
if (!in_array($pid, $products_sorted)) {
$products_sorted[] = $pid;
}
}
$header = array();
$header['img tac'] = 'Product';
$header['i w100 tac'] = 'Info';
$header['a w080 tac'] = 'Pieces Sold';
$header['b w150 tac'] = 'Net Sales';
list($year, $week) = explode('-', $week_end);
$temp_date = getStartAndEndDate($week, $year);
$report_date_end = $temp_date['end_date'];
//Validate week filter input
if (strtotime($report_date_end) < strtotime($report_date_start)) {
echo utf8_encode('Invalid week range input');
exit;
}
$week_date_start = $report_date_start;
$week_date_end = date('Y-m-d', strtotime($report_date_start . " + 6 days"));
$row = 0;
while (strtotime($week_date_end) <= strtotime($report_date_end)) {
//echo("$week_date_start | $week_date_end<br/>");
$row++;
$week_number = date('W', strtotime($week_date_start));
$prod_summary = ReportCOGS::retrieveSummaryByProducts($products_id, $spid, $week_date_start, $week_date_end);
$week_sold = isset($prod_summary[$products_id]['total_quantity_sold']) ? $prod_summary[$products_id]['total_quantity_sold'] : 0;
$row_class = $row > 0 && $row % 2 == 0 ? 'e' : 'o';
$info = '<span class="ui-icon ui-icon-info help float-right" title="' . date('d-m-Y', strtotime($week_date_start)) . ' to ' . date('d-m-Y', strtotime($week_date_end)) . '" />';
$result .= '<div class="tac ' . $row_class . '" style="margin-top:3px;">Week ' . $week_number . ": " . $week_sold . $info . '</div>';
$week_date_start = date('Y-m-d', strtotime($week_date_end . " + 1 days"));
$week_date_end = date('Y-m-d', strtotime($week_date_start . " + 6 days"));
}
} else {
$result .= " <br/><br/>{$not_available_text}";
}
$result .= ' </div>';
} else {
$result .= "Product {$products_id} not found";
}
$result .= '</div>';
