function analysis_list($jng_warehouses_id, $period, $price_type, $page)
{
use_class('jng_warehouses');
$class_jw = new jng_warehouses();
$wh = $class_jw->retrieveDetail($jng_warehouses_id);
$period_year = substr($period, 0, 4);
$period_month = substr($period, -2);
$period_date = "{$period_year}-{$period_month}-01";
if ($wh['warehouse_type'] == 'E') {
$filter = " WHERE mse.jng_warehouses_id={$jng_warehouses_id} AND mse.stock_period='{$period}'";
$total_query = "SELECT count(mse.elements_id) AS total FROM minierp_stock_elements mse";
$total_query .= $filter;
$total_result = tep_db_query($total_query);
$total = tep_db_fetch_array($total_result);
//PAGING QUERY & LINKS
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($total['total'], $page, '?open=analysis-stock-list&page=');
$q = "SELECT mse.*";
$q .= ", SUM(mset.stock_add) AS stock_add, SUM(mset.stock_reduce) AS stock_red";
$q .= ", CONCAT(ei.image_path, ei.image_filename) AS elements_image, e.elements_code, ed.name AS elements_name";
$q .= " FROM minierp_stock_elements mse";
$q .= " LEFT JOIN minierp_stock_elements_transaction AS mset";
$q .= " \tON mset.jng_warehouses_id=mse.jng_warehouses_id";
$q .= " \tAND mset.elements_id=mse.elements_id";
$q .= "\t\tAND YEAR(mset.transaction_time)='{$period_year}'";
$q .= "\t\tAND MONTH(mset.transaction_time)='{$period_month}'";
$q .= " LEFT JOIN elements e ON e.elements_id=mse.elements_id";
$q .= " LEFT JOIN elements_images ei ON ei.elements_id=e.elements_id AND ei.elements_sample_name='EC1'";
$q .= " LEFT JOIN elements_description ed ON ed.elements_id=e.elements_id AND ed.languages_id=2";
$q .= $filter;
$q .= " GROUP BY elements_id";
$q .= $page_query;
$r = tep_db_query($q);
} else {
$filter = " WHERE msp.jng_warehouses_id={$jng_warehouses_id} AND msp.stock_period='{$period}'";
$total_query = "SELECT count(msp.products_id) AS total FROM minierp_stock_products msp";
$total_query .= $filter;
$total_result = tep_db_query($total_query);
$total = tep_db_fetch_array($total_result);
//PAGING QUERY & LINKS
$offset = ($page - 1) * ROWPERPAGE;
$page_query = " LIMIT " . ROWPERPAGE . " OFFSET {$offset}";
$pagelink = tep_paging($total['total'], $page, '?open=analysis-stock-list&page=');
$q = "SELECT msp.*";
$q .= ", SUM(mspt.stock_add) AS stock_add, SUM(mspt.stock_reduce) AS stock_red";
$q .= ", p.products_image, p.products_model, pd.products_name";
$q .= " FROM minierp_stock_products msp";
$q .= " LEFT JOIN minierp_stock_products_transaction AS mspt";
$q .= " \tON mspt.jng_warehouses_id=msp.jng_warehouses_id";
$q .= " \tAND mspt.products_id=msp.products_id";
$q .= "\t\tAND YEAR(mspt.transaction_time)='{$period_year}'";
$q .= "\t\tAND MONTH(mspt.transaction_time)='{$period_month}'";
$q .= " LEFT JOIN products p ON p.products_id=msp.products_id";
$q .= " LEFT JOIN products_description pd ON pd.products_id=p.products_id AND pd.language_id=2";
$q .= $filter;
$q .= " GROUP BY products_id";
$q .= $page_query;
$r = tep_db_query($q);
}
$stocks = array();
$s = array();
$s['img'] = 'Product';
$s['name'] = 'Info';
$s['ev'] = 'EoP Value';
$s['sb'] = 'Inventory BoP';
$s['sa'] = 'Addition';
$s['sr'] = 'Reduction';
$s['se'] = 'Inventory EoP';
$s['sv'] = 'Inventory EoP Value';
$s['avg'] = 'Average Inventory Level';
$s['avg_v'] = 'Average Inventory Value';
$s['to'] = 'Inventory Turnover';
$s['doi'] = 'Average days of inventory';
$s['tc'] = 'Total Cost of Capital of Period';
$stocks[] = $s;
$total_days_period = date('Ym') == $period ? date('j') : date('t', strtotime($period_date));
while ($row = tep_db_fetch_array($r)) {
if ($wh['warehouse_type'] == 'E') {
$img = webImage('elements/' . $row['elements_image'], '80', '80');
$info = "<div><strong>{$row['elements_id']} / {$row['elements_code']}</strong></div>";
$info .= "<div>{$row['elements_name']}</div>";
} else {
$img = '<a href="' . $detail_link . '" title="View and Manage Product Detail">' . webImage($row['products_image'], '80', '80') . '</a>';
$detail_link = "?open=product-detail&products_id={$row['products_id']}";
$info = "<div><strong>{$row['products_id']} / {$row['products_model']}</strong></div>";
$info .= "<div>{$row['products_name']}</div>";
}
$eop_value = $row[$price_type . '_close'];
$eop_value_inv = $row['stock_close'] * $eop_value;
$avg = ($row['stock_open'] + $row['stock_close']) / 2;
$avg_value = $avg * $eop_value;
$turnover = $avg == 0 ? 0 : $row['stock_red'] / $avg;
$avg_days = $turnover == 0 ? 0 : $total_days_period / $turnover;
$cap_cost = $avg_value == 0 ? 0 : 0.07000000000000001 * $avg_days / 365 * 31 * $avg_value;
$s = array();
$s['img'] = $img;
$s['name'] = $info;
$s['ev'] = number_format($eop_value, 2);
$s['sb'] = $row['stock_open'];
$s['sa'] = $row['stock_add'];
$s['sr'] = $row['stock_red'];
$s['se'] = $row['stock_close'];
$s['sv'] = number_format($eop_value_inv, 2);
$s['avg'] = number_format($avg, 1);
$s['avg_v'] = number_format($avg_value, 2);
$s['to'] = number_format($turnover, 2);
$s['doi'] = number_format($avg_days, 1);
$s['tc'] = number_format($cap_cost, 2);
$stocks[] = $s;
}
$result = tep_draw_table('proman', $stocks);
$result .= '<div> </div>';
$result .= $pagelink;
return $result;
}
$address_postcode = tep_db_prepare_input($_POST['address_postcode']);
$address_city = tep_db_prepare_input($_POST['address_city']);
$address_country_id = tep_db_prepare_input($_POST['address_country_id']);
if ($jng_warehouses_id == '') {
$jng_warehouses_id = $class_jw->create($name, $type, $code, $is_active, $pic, $address_street, $address_postcode, $address_city, $address_country_id);
$messagebox->add('New Warehouse is added.', 'green');
} else {
$class_jw->update($jng_warehouses_id, $name, $type, $code, $is_active, $pic, $address_street, $address_postcode, $address_city, $address_country_id);
$messagebox->add('Changes is successfully saved.', 'green');
}
$wid = $jng_warehouses_id;
}
}
//START TEMPLATE
if ($wid != 'new') {
$warehouse = $class_jw->retrieveDetail($wid);
if (!is_null($warehouse)) {
$jng_warehouses_id = $warehouse['jng_warehouses_id'];
$pic = $warehouse['pic'];
$name = $warehouse['name'];
$type = $warehouse['warehouse_type'];
$code = $warehouse['warehouse_code'];
$is_active = $warehouse['is_active'];
$address_street = $warehouse['address_street'];
$address_postcode = $warehouse['address_postcode'];
$address_city = $warehouse['address_city'];
$address_country_id = $warehouse['address_country_id'];
$warehouse_buttons = '<input type="submit" class="blue" name="submit" value="Save" title="Save Changes" />';
$warehouse_buttons .= '<input type="button" class="red" name="delete" value="Delete" title="Delete Warehouse" onclick="if(confirm(\'Will delete this warehouse, are you sure?\')) warehouse_delete.submit();" />';
} else {
$messagebox->add("Warehouse ID: <strong>{$wid}</strong> is not found on database.");
