$stat_simple .= " REPLACE(format(avgAmtArea,2), ',','') as avgAmtArea ";
$stat_simple .= ", REPLACE(format(avgAmtLand,2), ',', '') as avgAmtLand ";
break;
// all others
// all others
default:
$stat_sql .= " REPLACE(format(avg(amount/area),2), ',', '') as avgDeposit ";
$stat_sql .= ", REPLACE(format(avg(monthlyPay/area),2), ',', '') as avgRent ";
$stat_sql .= " from {$tname} where year >= ? AND year <= ?";
$stat_simple .= " REPLACE(format(avgDeposit,2), ',', '') as avgDeposit, ";
$stat_simple .= " REPLACE(format(avgRent,2), ',', '') as avgRent ";
}
$stat_simple .= " from {$tname}" . "_agg where k=? AND year >= ? AND year <= ? order by year, month";
// to append after adding more search keys
$stat_sql_append = " group by year, month order by year, month ";
processQuery($stat_sql, $stat_sql_append, $stat_simple);
/**
*/
function processQuery($sql, $sql_append, $simple)
{
$startyear = intval($_GET['startyear']);
$endyear = intval($_GET['endyear']);
if ($endyear == 0) {
$endyear = 3000;
}
$params = [&$startyear, &$endyear];
$type = "ii";
$searchKey = "";
$i = 0;
$debug = false;
$hasMonthlyType = false;
function query_engine($apptype, $GET)
{
if (!$apptype) {
exit(-1);
}
$optQuery = " ";
foreach ($GET as $key => $value) {
switch ($key) {
case 'result':
switch ($value) {
case 'done':
$optQuery .= " and b.status='처리' ";
break;
case 'ongoing':
$optQuery .= " and b.status='계류' ";
break;
case 'pass':
$optQuery .= " and (b.status_detail='의결' or b.status_detail='공포' or b.status_detail='정부이송') ";
break;
}
break;
case 'by':
if ($value === 'rep') {
$optQuery .= " and c.is_representative=1 ";
break;
}
}
}
$json = '[{}]';
// Basic information SQL
switch ($apptype) {
case 'coact':
$sql = "select name_kr, name_cn, party, district, actorid as id, count(actorid) c from CoActor c " . " inner join Actor a on a.id=actorid inner join (select distinct(billid) from CoActor " . " where actorid = ?) x on x.billid=c.billid group by actorid order by c desc limit 11;";
break;
case 'stat':
$sql = "select count(*) as c, YEAR(proposed_date) as y, MONTH(proposed_date) as m from CoActor c ";
$sql .= " INNER JOIN Bill b on b.id = c.billid INNER Join Actor a on a.id=c.actorid ";
$sql .= " where a.id = ? {$optQuery} group by YEAR(proposed_date), MONTH(proposed_date) order by YEAR(proposed_date), MONTH(proposed_date) ;";
break;
case 'list':
$sql = "select b.id, b.link_id, title, proposed_date, decision_date, status, status_detail, actor_count from Bill b ";
$sql .= "INNER JOIN CoActor c on c.billid = b.id where c.actorid=? {$optQuery} ";
$sql .= " order by proposed_date desc";
break;
case 'all':
$sql = "select a.name_kr, a.name_cn, a.party, a.id actorid, count(*) as c from CoActor c ";
$sql .= "INNER JOIN Actor a ON a.id = c.actorid group by actorid order by c desc; ";
case 'summary':
$sql = "select summary from Bill where id = ?;";
break;
case 'billactors':
$sql = "select name_kr, name_cn, party, a.id from Actor a Inner join CoActor c on a.id = c.actorid where c.billid = ? order by name_kr;";
break;
case 'allorder':
$sql = "select a.id, a.name_kr, a.name_cn, a.party, year(proposed_date) as y, month(proposed_date) as m, count(distinct b.id) as c from CoActor c inner join Actor a on a.id=c.actorid inner join Bill b on c.billid=b.id group by actorid, y, m order by a.id, y, m";
break;
// This for the circle viz
// This for the circle viz
case 'order':
$sql = "select name_kr, name_cn, party, district, a.id, count(distinct billid) as c, count(distinct billid)+50 as value from CoActor c ";
$sql .= " inner join Bill b on c.billid = b.id ";
$sql .= " inner join Actor a on a.id = c.actorid and a.name_cn<>'' AND a.party<>'' ";
$sql .= " where 1=1 {$optQuery} ";
$sql .= " group by actorid order by c desc";
break;
//"select name_kr, name_cn, party, district, id, count(distinct billid) as c, count(distinct billid)+50 as value from CoActor c inner join Bill b where c.billid = b.id inner join Actor a on a.id = c.actorid and a.name_cn<>'' AND a.party<>'' group by actorid order by c desc";
// This is for the autocomplete search
//"select name_kr, name_cn, party, district, id, count(distinct billid) as c, count(distinct billid)+50 as value from CoActor c inner join Bill b where c.billid = b.id inner join Actor a on a.id = c.actorid and a.name_cn<>'' AND a.party<>'' group by actorid order by c desc";
// This is for the autocomplete search
case 'actor':
$sql = "select CONCAT_WS('(', name_kr, CONCAT(CONCAT_WS('/', party, district),')') ) as info, id from Actor where name_cn<>'' AND party<>'' order by name_kr";
break;
default:
print $json;
exit(-1);
}
// process and print
processQuery($apptype, $sql, $GET);
}
public function calculate()
{
$transaction = array();
$fundsAvail = false;
//make sure source account has sufficient funds
switch (getAccountType($this->srcAccount['type'], true)) {
//cash accounts
case 'asset':
if ($this->srcAccount['balance'] > $this->transAmount) {
$fundsAvail = true;
}
break;
//credit accounts
//credit accounts
case 'revolving':
if ($this->srcAccount['credit_limit'] - $this->srcAccount['balance'] > $this->transAmount) {
$fundsAvail = true;
}
break;
}
if ($fundsAvail) {
//debit transaction amount from source account
$transaction['srcAccount']['balance'] = $this->srcAcount['balance'] - $this->transAmount;
//get account type
switch (getAccountType($this->transAccount['type'], true)) {
case 'expense':
//debit transaction amount from transaction account
$transaction['transAccount']['balance'] = $this->transAccount['balance'] - $this->transAmount;
//update transaction account due date
$transaction['transAccount']['due_date'] = date('Y-m-d', strtotime($this->transAccount['due_date'], getFrequencyOffest($this->transAccount['repeat'])));
break;
case 'asset':
case 'installment':
case 'credit':
case 'revolving':
//credit transaction amount to transaction account
$transaction['transAccount']['balance'] = $this->transAccount['balance'] + $this->transAmount;
break;
}
}
foreach ($transaction as $array => $account) {
$errors = array();
$query = "UPDATE accounts SET ";
$count = 0;
foreach ($account as $key => $value) {
if ($count < count($account)) {
$query .= $key . ' = ?, ';
} else {
$query .= $key . ' = ? ';
}
++$count;
}
$params = getQueryParams($account);
$query .= "WHERE ID = " . $account['ID'];
$queryData[$array]['query'] = $query;
$queryData[$array]['params'] = $params;
}
$transaction['transaction'] = array('transaction_account' => $this->transAccount['ID'], 'transaction_source' => $this->srcAccount['ID'], 'transaction_amount' => $this->transAccount, 'transaction_date' => $this->transDate, 'transaction_memo' => $this->transMemo, 'user' => $_SESSION['user_id']);
$query = 'INSERT INTO transactions ( ';
$count = 0;
foreach ($transactions as $key => $value) {
if ($count < count($transaction)) {
$params .= $key . ', ';
$vars .= '?, ';
} else {
$params .= $key;
$vars .= '?';
}
}
$query .= $params . ' ) VALUES ( ' . $vars . ' )';
$params = getQueryParams($transaction);
$queryData['transaction']['query'] = $query;
$queryData['transaction']['params'] = $params;
if ($result = processQuery($queryData, false)) {
//An errors has occured
if (isset($result['errors'])) {
$transaction['errors'] = $result['errors'];
return $transaction;
}
$_SESSION['transaction'] = $transaction;
}
return $transaction;
}
<?php
header("Access-Control-Allow-Origin: *");
//header("Accept-Encoding: gzip,deflate");
//header("Content-Encoding: gzip");
header("Content-Type: application/json; charset=UTF-8");
// Get app name
$tname = substr($_SERVER['PATH_INFO'], 1);
if (!$tname) {
exit(0);
}
// Basic information SQL
$sale_sql = "SELECT * FROM {$tname} where year >= ? AND year <= ? ";
$sale_sql_append = " order by year desc, month desc limit 500";
// process and print
processQuery($sale_sql, $sale_sql_append);
/**
* Main function
*/
function processQuery($sql, $sql_append)
{
$startyear = intval($_GET['startyear']);
$endyear = intval($_GET['endyear']);
// No end year, give it enough
if ($endyear == 0) {
$endyear = 3000;
}
// make array and type
$params = [&$startyear, &$endyear];
$type = "ii";
$debug = false;
