$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;