public function getValuation($type = "", $date = "")
{
if (isset($date) && $date != "") {
$extraWhere = 'ih.date = "' . $date . '"';
} else {
$extraWhere = 'ih.date = "' . date('Y-m-d') . '"';
}
if (isset($type) && $type != "") {
$extraWhere .= ' AND cat.id = ' . $type;
}
$extraWhere .= ' GROUP BY ih.item_id';
$table = 'inv_item_history';
$primaryKey = 'id';
$columns = array(array('db' => 'item_code', 'dt' => 0, 'field' => 'item_code', 'searchable' => 'item.item_code', 'formatter' => function ($d, $row) {
return '<a href="order/item/' . $row['item_id'] . '" style="color:blue;">' . $d . ' </a>';
}), array('db' => 'item_name', 'dt' => 1, 'field' => 'item_name', 'searchable' => 'item.item_name'), array('db' => 'size', 'dt' => 2, 'field' => 'size', 'searchable' => 'item.size'), array('db' => 'category', 'dt' => 3, 'field' => 'category', 'searchable' => 'cat.name'), array('db' => 'total_in_stock', 'dt' => 4, 'field' => 'total_in_stock', 'searchable' => 'ih.total_in_stock'), array('db' => 'cost_per_unit', 'dt' => 5, 'field' => 'cost_per_unit', 'searchable' => 'ih.cost_per_unit'), array('db' => 'id', 'dt' => 6, 'field' => 'total', 'formatter' => function ($d, $row) {
return '$' . $row['total_in_stock'] * $row['cost_per_unit'];
}));
//$joinQuery = 'SELECT item.id,item.item_code,item.total_in_stock,item.cost_per_unit, item.item_name,item.size,cat.id as cat_id,cat.name as category, ih.modified_date as date FROM inv_item as item JOIN inv_category as cat ON item.category = cat.id LEFT JOIN inv_item_history as ih ON item.item_id = ih.item_id $where $order $limit';
$joinQuery = 'SELECT ih.*, item.item_code,item.item_name, item.size, cat.name as category, cat.id FROM (SELECT * FROM `inv_item_history` ORDER BY `inv_item_history`.`time` DESC) AS ih LEFT JOIN inv_item as item ON item.id = ih.item_id LEFT JOIN inv_category as cat ON cat.id = item.category $where $order $limit';
echo json_encode(SSP2::simple($_REQUEST, $this->_sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere));
}
public function getBatch($type = "")
{
$extraWhere = isset($type) && $type != "" ? 'cat.id = ' . $type : '';
$table = 'inv_receive';
$primaryKey = 'id';
$columns = array(array('db' => 'batch_number', 'dt' => 0, 'field' => 'batch_number', 'searchable' => 'r.batch_number', 'formatter' => function ($d, $row) {
return '<a href="inventory_batch/view/' . $row['id'] . '" style="color:blue;">' . $d . ' </a>';
}), array('db' => 'date', 'dt' => 1, 'field' => 'date', 'searchable' => 'r.date'), array('db' => 'contact', 'dt' => 2, 'field' => 'contact_id', 'searchable' => 'c.name'), array('db' => 'supplier_reference', 'dt' => 3, 'field' => 'supplier_reference', 'searchable' => 'r.supplier_reference'));
$joinQuery = 'SELECT r.id, r.batch_number, r.date, c.name as contact, r.supplier_reference FROM inv_receive as r JOIN inv_contact as c ON c.id = r.contact_id $where $order $limit';
echo json_encode(SSP2::simple($_REQUEST, $this->_sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere));
}
