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