$actYear = isset($_GET["actYear"]) && !empty($_GET["actYear"]) ? $_GET["actYear"] : $currYear;
$actMonth = isset($_GET["actMonth"]) && !empty($_GET["actMonth"]) ? $_GET["actMonth"] : $currMonth;
$storeCode = isset($_GET["storeCode"]) && !empty($_GET["storeCode"]) ? $_GET["storeCode"] : "no store selected";
// make connection
$conn = pg_connect("host={$host} port=5432 dbname={$dbname} user={$user} password={$pass}");
if (!$conn) {
die("Error: Could not establish a connection!");
}
$storeInit = "";
$aParams = array($storeCode);
$sql = "select store_init from mst_site where store_code = \$1 limit 1";
$res = pg_query_params($conn, $sql, $aParams);
if ($row = pg_fetch_array($res)) {
$storeInit = $row["store_init"];
}
$dataLabel = "Period: " . readMonth($actMonth) . " " . $actYear;
$expense_name = array();
$expense_pc["name"] = !empty($storeInit) ? $storeInit : $storeCode;
$aParams = array($actYear, $actMonth, $storeCode);
$sql = "select pid, pmonth, pyear, store_code, xpense_name, xpense_pc from xpense where pyear = \$1 and pmonth = \$2 and store_code = \$3 order by xpense_name";
$res = pg_query_params($conn, $sql, $aParams);
while ($row = pg_fetch_array($res)) {
$expense_name[] = $row["xpense_name"];
$expense_pc["data"][] = round($row["xpense_pc"], 2);
}
$result["data_label"] = $dataLabel;
$result["expense_name"] = $expense_name;
$result["expense_pc"] = $expense_pc;
# php document said no need this call
pg_close($conn);
#echo json_encode($result, JSON_NUMERIC_CHECK); # Available since PHP 5.3.3.
// DB table to use
#$table = 'table_name';
#$table = "select id, pyear, pmonth, store_init, account, amount from adm_budget where is_active = 1";
if ($userId == "admin") {
$table = "select z.id, z.pyear, z.pmonth, z.store_init, w.name, z.amount, case when z.is_active = 1 then 'Y' else 'N' end is_active from adm_budget z " . "inner join adm_expense_hdr w on w.id = cast(z.account as integer)";
} else {
$table = "select z.id, z.pyear, z.pmonth, z.store_init, w.name, z.amount, case when z.is_active = 1 then 'Y' else 'N' end is_active from adm_budget z " . "inner join adm_expense_hdr w on w.id = cast(z.account as integer) " . "where z.store_init in (select y.branch_code from adm_user x inner join adm_user_store y on y.id_hdr = x.id where x.user_id = '" . $userId . "')";
}
// Table's primary key
$primaryKey = 'id';
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(array('db' => 'pyear', 'dt' => 0), array('db' => 'pmonth', 'dt' => 1, 'formatter' => function ($d, $row) {
return readMonth($d);
}), array('db' => 'store_init', 'dt' => 2), array('db' => 'name', 'dt' => 3), array('db' => 'amount', 'dt' => 4, 'formatter' => function ($d, $row) {
return number_format($d, 2, ".", ",");
}), array('db' => 'is_active', 'dt' => 5));
// SQL server connection information
$sql_details = array('user' => 'xpense', 'pass' => 'xpenseteam', 'db' => 'xpense', 'host' => 'localhost');
# set additional function here
$detail = false;
$edit = true;
$delete = true;
function readMonth($v)
{
switch ($v) {
case 1:
return "January";
break;
