function load_market(mysqli $conn)
{
clear_market($conn);
//calculate elementary price of each goods
//for each places
$places = PLACES_COUNT;
for ($i = 1; $i <= $places; $i++) {
// INSERT INTO `market`(`id`, `name`, `price`, `place_id`) VALUES ([value-1],[value-2],[value-3],[value-4],[value-5])
$res_goods_here = mysqli_query($conn, "SELECT * FROM " . GOODS_TABLE . " WHERE place_id = {$i}");
//print_sql("SELECT * FROM ".GOODS_TABLE." WHERE place_id = $i");
//print_query_result("place(id = $i) goods list", $res_goods_here);
$goods_here_count = mysqli_num_rows($res_goods_here);
for ($j = 0; $j < $goods_here_count; $j++) {
$goods_here = mysqli_fetch_array($res_goods_here);
$price = round($goods_here['price']);
$name = $goods_here['name'];
$id = $goods_here['goods_id'];
$min_price = $price * $goods_here['fluctu_min'];
$max_price = $price * $goods_here['fluctu_max'];
$price = mt_rand($min_price, $max_price);
mysqli_query($conn, "INSERT INTO `market`(`id`, `name`, `price`, `place_id`, `goods_id`) VALUES ({$j} + {$i}, '{$name}', {$price}, {$i}, {$id} )");
print_var("name", $name);
print_sql("INSERT INTO `market`(`id`, `name`, `price`, `place_id`) VALUES ({$j} + {$i}, '{$name}', {$price}, {$i} )");
}
}
}
function dbQuery($sql, $parameters = array())
{
global $fullSql, $debug;
$fullSql = dbMakeQuery($sql, $parameters);
if ($debug) {
if ($GLOBALS['cli']) {
print_message("\nSQL[%y" . $fullSql . "%n] ", 'color');
} else {
print_sql($fullSql);
}
}
if ($GLOBALS['config']['profile_sql']) {
$time_start = microtime(true);
}
$result = mysql_query($fullSql);
// sets $this->result
if ($GLOBALS['config']['profile_sql']) {
$time_end = microtime(true);
#fwrite($this->logFile, date('Y-m-d H:i:s') . "\n" . $fullSql . "\n" . number_format($time_end - $time_start, 8) . " seconds\n\n");
$GLOBALS['sql_profile'][] = array('sql' => $fullSql, 'time' => number_format($time_end - $time_start, 8));
}
if ($result === false && error_reporting() & 1) {
// aye. this gets triggers on duplicate Contact insert
//trigger_error('QDB - Error in query: ' . $fullSql . ' : ' . mysql_error(), E_USER_WARNING);
}
return $result;
}
function dbQuery($sql, $parameters = array())
{
global $fullSql;
$fullSql = dbMakeQuery($sql, $parameters);
if (OBS_DEBUG) {
// Pre query debug output
if (is_cli()) {
print_message(PHP_EOL . 'SQL[%y' . $fullSql . '%n]', 'console', FALSE);
} else {
print_sql($fullSql);
}
}
if (OBS_DEBUG || $GLOBALS['config']['profile_sql']) {
$time_start = microtime(true);
}
$result = mysql_query($fullSql);
// sets $this->result
if (OBS_DEBUG || $GLOBALS['config']['profile_sql']) {
$runtime = number_format(microtime(true) - $time_start, 8);
$debug_msg .= 'RUNTIME[' . ($runtime > 0.05 ? '%r' : '%g') . $runtime . 's%n]';
if ($GLOBALS['config']['profile_sql']) {
#fwrite($this->logFile, date('Y-m-d H:i:s') . "\n" . $fullSql . "\n" . number_format($time_end - $time_start, 8) . " seconds\n\n");
$GLOBALS['sql_profile'][] = array('sql' => $fullSql, 'time' => $runtime);
}
}
if (OBS_DEBUG) {
if ($result === FALSE && error_reporting() & 1) {
// aye. this gets triggers on duplicate Contact insert
//trigger_error('QDB - Error in query: ' . $fullSql . ' : ' . mysql_error(), E_USER_WARNING);
$error_msg = 'Error in query: (' . mysql_errno() . ') ' . mysql_error();
$debug_msg .= PHP_EOL . 'ERROR[%r' . $error_msg . '%n]';
}
if (is_cli()) {
if (OBS_DEBUG > 1) {
$rows = mysql_affected_rows();
$debug_msg = 'ROWS[' . ($rows < 1 ? '%r' : '%g') . $rows . '%n]' . PHP_EOL . $debug_msg;
}
// After query debug output for cli
print_message($debug_msg, 'console', FALSE);
} else {
print_error($error_msg);
}
}
return $result;
}
$i++;
}
################################################################
# Check what we want to sort data on
################################################################
if ($_GET['sortby'] == "date") {
$sql = "SELECT `flmevent_date`,`flmevent_user`,MAX(`flmevent_feature`),count(*) FROM `flexlm_events` WHERE `flmevent_type`='OUT' GROUP BY `flmevent_date`,`flmevent_user` ORDER BY `flmevent_date`,`flmevent_user`,`flmevent_feature` DESC;";
} else {
if ($_GET['sortby'] == "user") {
$sql = "SELECT `flmevent_date`,`flmevent_user`,MAX(`flmevent_feature`),count(*) FROM `flexlm_events` WHERE `flmevent_type`='OUT' GROUP BY `flmevent_user`,`flmevent_date` ORDER BY `flmevent_user`,`flmevent_date`,`flmevent_feature` DESC;";
} else {
$sql = "SELECT `flmevent_date`,MAX(flmevent_user),`flmevent_feature`,count(*) FROM `flexlm_events` WHERE `flmevent_type`='OUT' GROUP BY `flmevent_feature`,`flmevent_date` ORDER BY `flmevent_feature`,`flmevent_date`,`flmevent_user` DESC;";
}
}
if (isset($debug) && $debug == 1) {
print_sql($sql);
}
$recordset = $db->query($sql);
if (DB::isError($recordset)) {
die($recordset->getMessage());
}
while ($row = $recordset->fetchRow()) {
$table->AddRow($row, "style=\"background: " . $features_color[$row[2]] . ";\"");
}
$table->updateColAttributes(3, "align=\"center\"");
$recordset->free();
$db->disconnect();
################################################################
# Right align the 3 column
################################################################
$table->updateColAttributes(2, "align=\"right\"");
function test_dictionary($dbType)
{
echo "<h3>{$dbType}</h3><p>";
$db = NewADOConnection($dbType, "pear");
$dict = NewDataDictionary($db);
if (!$dict) {
continue;
}
$dict->debug = 1;
$opts = array('REPLACE', 'mysql' => 'TYPE=INNODB', 'oci8' => 'TABLESPACE USERS');
if (mt_rand(1, 10000) < 5000) {
$flds = array(array('id', 'I', 'AUTO', 'KEY'), array('name' => 'firstname', 'type' => 'varchar', 'size' => 30, 'DEFAULT' => 'Joan'), array('lastname', 'varchar', 28, 'DEFAULT' => 'Chen', 'key'), array('averylonglongfieldname', 'X', 1024, 'NOTNULL', 'default' => 'test'), array('price', 'N', '7.2', 'NOTNULL', 'default' => '0.00'), array('MYDATE', 'D', 'DEFDATE'), array('TS', 'T', 'DEFTIMESTAMP'));
} else {
$flds = "\nID I AUTO KEY,\nFIRSTNAME VARCHAR(30) DEFAULT 'Joan',\nLASTNAME VARCHAR(28) DEFAULT 'Chen' key,\naverylonglongfieldname X(1024) DEFAULT 'test',\nprice N(7.2) DEFAULT '0.00',\nMYDATE D DEFDATE,\nBIGFELLOW X NOTNULL,\nTS T DEFTIMESTAMP";
}
$sqla = $dict->CreateDatabase('ADOdbLite', array('postgres' => "LOCATION='/u01/postdata'"));
print_sql($dbType, $sqla, "CreateDatabase");
$dict->SetSchema('ADOdbLite');
if (substr($dbType, 0, 8) != 'postgres') {
$sqli = $dict->CreateTableSQL('testtable', $flds, $opts);
print_sql($dbType, $sqli, "CreateTableSQL");
}
$sqli = $dict->CreateIndexSQL('idx', 'testtable', 'firstname,lastname', array('BITMAP', 'FULLTEXT', 'CLUSTERED', 'HASH'));
print_sql($dbType, $sqli, "CreateIndexSQL");
$sqli = $dict->CreateIndexSQL('idx2', 'testtable', 'price,lastname');
//,array('BITMAP','FULLTEXT','CLUSTERED'));
print_sql($dbType, $sqli, "CreateIndexSQL");
$addflds = array(array('height', 'F'), array('weight', 'F'));
$sqli = $dict->AddColumnSQL('testtable', $addflds);
print_sql($dbType, $sqli, "AddColumnSQL");
$addflds = array(array('height', 'F', 'NOTNULL'), array('weight', 'F', 'NOTNULL'));
$sqli = $dict->AlterColumnSQL('testtable', $addflds);
print_sql($dbType, $sqli, "AlterColumnSQL");
if ($dbType == 'mysql') {
$db->Connect($_POST['dbhost'], $_POST['dbusername'], $_POST['dbpassword'], $_POST['databasename']);
$dict->SetSchema('');
$sqla2 = $dict->CreateTableSQL('adoxyz', $flds, $opts);
if ($_POST['create_test']) {
$debug_query = $dict->ExecuteSQLArray($sqla2);
}
if ($sqla2) {
print_sql($dbType, $sqla2, "CreateTableSQL");
}
print_sql($dbType, $dict->ChangeTableSQL('adoxyz', 'LASTNAME varchar(32)'), "ChangeTableSQL");
}
print_sql($dbType, $dict->DropColumnSQL('table', array('my col', '`col2_with_Quotes`', 'A_col3', 'col3(10)')), "DropColumnSQL");
unset($db, $dict);
}
<?php
try {
require_once "./db.php";
$REQUEST = get_request_data();
$uid = null;
if (array_key_exists('userid', $REQUEST)) {
$uid = $REQUEST['userid'];
} elseif (isset($_COOKIE) && isset($_COOKIE['user'])) {
$uid = $_COOKIE['user']['id'];
} else {
throw new Exception('Must supply userid');
}
$dbh = open_db();
$cohortstring = '';
if (array_key_exists('cohortid', $REQUEST)) {
$cohortstring = "AND cohortid={$REQUEST["cohortid"]}";
}
print_sql($sql = "SELECT balance.*, cohort.name AS cohort_name, ufrom.name AS from_name, ufrom.nick AS from_nick, uto.name AS to_name, uto.nick AS to_nick FROM balance LEFT JOIN user AS ufrom ON userid_from=ufrom.userid LEFT JOIN user AS uto ON userid_to=uto.userid LEFT JOIN cohort USING(cohortid) WHERE amount <> 0 AND (userid_from={$uid} or userid_to={$uid}) {$cohortstring}");
if (($res = $dbh->query($sql, PDO::FETCH_ASSOC)) == false) {
throw new Exception('Error selecting from balance table:' . implode($dbh->errorInfo(), ' '));
}
$data = array();
foreach ($res as $row) {
array_push($data, $row);
}
echo json_response('success', null, $data);
} catch (Exception $e) {
echo json_response('error', $e->getMessage(), null);
}
?>
<li class="dropdown">
<a href="<?php
echo generate_url(array('page' => 'overview'));
?>
" class="dropdown-toggle" data-hover="dropdown" data-toggle="dropdown">
<i class="oicon-databases"></i> <b class="caret"></b></a>
<div class="dropdown-menu" style="padding: 10px 10px 0px 10px; width: 1150px; height: 700px; z-index: 2000; overflow: scroll;">
<table class="table table-condensed-more table-striped">
<?php
$sql_profile = array_sort($sql_profile, 'time', 'SORT_DESC');
$sql_profile = array_slice($sql_profile, 0, 15);
foreach ($sql_profile as $sql_query) {
echo '<tr><td>', $sql_query['time'], '</td><td>';
print_sql($sql_query['sql']);
echo '</td></tr>';
}
?>
</table>
</div>
</li>
<?php
}
// End profile_sql
?>
</ul>
</div>
</div>
</div>
$userid_payer = $balance['userid_to'];
$userid_payee = $balance['userid_from'];
} else {
throw new Exception("No balance to settle");
}
$amount = abs($amount);
print_sql($balance);
print_sql($sql = "INSERT INTO purchase SET description=\"{$REQUEST["desc"]}\", amount={$amount}, userid={$userid_enterer}, userid_payer={$userid_payer}, date_of=\"{$datestring}\", date_created=NOW()");
if (($nrows = $dbh->exec($sql)) != 1) {
throw new Exception("Inserted {$nrows} rows into purchase table, expected 1...");
}
$purchaseId = $dbh->lastInsertId();
print_sql($sql = "INSERT INTO iou SET purchaseid={$purchaseId}, cohortid={$cohortid}, userid_payer={$userid_payer}, userid_payee={$userid_payee}, amount={$amount}, date_updated=NOW()");
if (($nrows = $dbh->exec($sql)) != 1) {
throw new Exception("Inserted {$nrows} rows into purchasedetail table, expected 1...");
}
print_sql($sql = "UPDATE balance SET amount=0.00 WHERE userid_from={$userid1} AND userid_to={$userid2} AND cohortid={$cohortid}");
if (($nrows = $dbh->exec($sql)) != 1) {
throw new Exception("Updated {$nrows} rows in balance table, expected 1...");
}
////////////////////////////////////////////////////////////////////////////////
// commit the transaction on success
// if ($debug) echo "<p>Everything was successful -- committing the transaction!</p>";
$dbh->commit();
echo json_response('success', null, null);
} catch (Exception $e) {
////////////////////////////////////////////////////////////////////////////////
// roll back the transaction on any error
echo json_response('error', $e->getMessage(), null);
$dbh->rollBack();
}
// if ($REQUEST['whopaid'] == $iou['userid']) continue;
if ($iou['amount'] == "") {
continue;
}
print_sql($sql = "INSERT INTO iou SET purchaseid={$purchaseId}, cohortid={$REQUEST["cohortid"]}, userid_payer={$REQUEST["whopaid"]}, userid_payee={$iou["userid"]}, amount={$iou["amount"]}, date_updated=NOW()");
if (($nrows = $dbh->exec($sql)) != 1) {
throw new Exception("Inserted {$nrows} rows into purchasedetail table, expected 1...");
}
// HOWEVER, we do NOT store self-balances
if ($REQUEST['whopaid'] == $iou['userid']) {
continue;
}
if ($REQUEST['whopaid'] < $iou['userid']) {
print_sql($sql = "UPDATE balance SET amount=amount-{$iou["amount"]} WHERE userid_from={$REQUEST["whopaid"]} AND userid_to={$iou["userid"]} AND cohortid={$REQUEST["cohortid"]}");
} else {
print_sql($sql = "UPDATE balance SET amount=amount+{$iou["amount"]} WHERE userid_to={$REQUEST["whopaid"]} AND userid_from={$iou["userid"]} AND cohortid={$REQUEST["cohortid"]}");
}
if (($nrows = $dbh->exec($sql)) != 1) {
throw new Exception("Updated {$nrows} rows in balance table, expected 1...");
}
}
////////////////////////////////////////////////////////////////////////////////
// commit the transaction on success
// if ($debug) echo "<p>Everything was successful -- committing the transaction!</p>";
$dbh->commit();
echo json_response('success', null, null);
} catch (Exception $e) {
////////////////////////////////////////////////////////////////////////////////
// roll back the transaction on any error
echo json_response('error', $e->getMessage(), null);
$dbh->rollBack();
?>
<li class="dropdown">
<a href="<?php
echo generate_url(array('page' => 'overview'));
?>
" class="dropdown-toggle" data-hover="dropdown" data-toggle="dropdown">
<i class="oicon-databases"></i> <b class="caret"></b></a>
<div class="dropdown-menu" style="padding: 10px; width: 1150px;">
<table class="table table-bordered table-condensed-more table-rounded table-striped">
<?php
$sql_profile = array_sort($sql_profile, 'time', 'SORT_DESC');
$sql_profile = array_slice($sql_profile, 0, 15);
foreach ($sql_profile as $sql_query) {
echo '<tr><td>', $sql_query['time'], '</td><td>', print_sql($sql_query['sql']), '</td></tr>';
#echo '<tr><td>', $sql_query['time'], '</td><td>' ,$sql_query['sql'], '</td></tr>';
}
?>
</table>
</div>
</li>
<?php
}
// End profile_sql
?>
</ul>
</div>
</div>
</div>
<?php
try {
require "./db.php";
$REQUEST = get_request_data();
$dbh = open_db();
if (!array_key_exists('name', $REQUEST)) {
throw new Exception('Did not supply name');
}
print_sql($sql = "SELECT * FROM location WHERE name LIKE '{$REQUEST["name"]}%'");
if (($res = $dbh->query($sql, PDO::FETCH_ASSOC)) == false) {
throw new Exception("Could not select recent purchase ids");
}
$locations = $res->fetchAll();
if (!$locations) {
echo json_encode(array('status' => 'success', 'message' => 'No locations found', 'data' => null));
} else {
echo json_response('success', null, $locations);
}
} catch (Exception $e) {
echo json_response('error', $e->getMessage(), null);
}
function muffleAlerts($host)
{
require_once "DB.php";
global $dsn;
global $notify_resend;
$mysqldate = date('Y-m-d H:i:s');
$alertdate = date('Y-m-d H:i:s', strtotime("-{$notify_resend} minutes"));
$db = DB::connect($dsn, true);
if (DB::isError($db)) {
die($db->getMessage());
}
# check for records
$sql = "SELECT pkid from alert_events where type = '" . $host['type'] . "' and hostname = '" . $host['hostname'] . "' and datetime > '" . $alertdate . "' and datetime < '" . $mysqldate . "'";
$recordset = $db->query($sql);
if (DB::isError($recordset)) {
print_sql($sql);
die($recordset->getMessage());
}
if ($recordset->numRows() == 0) {
#nothing found
$sql = "INSERT into alert_events(datetime,type,hostname) values('" . $mysqldate . "','" . $host['type'] . "','" . $host['hostname'] . "')";
$recordset = $db->query($sql);
if (DB::isError($recordset)) {
print_sql($sql);
die($recordset->getMessage());
}
return 1;
} else {
# we found a match!
# muffle alerts
return 0;
}
}
throw new Exception("Could not connect to location table");
}
if (($nrows = $res->fetchColumn()) != 1) {
throw new Exception("Found {$nrows} locations with locationid {$REQUEST["locationid"]}, expecting 1...");
}
if (array_key_exists('lat', $REQUEST)) {
$valueString .= "lat={$REQUEST["lat"]} ";
}
if (array_key_exists('lon', $REQUEST)) {
$valueString .= "lon={$REQUEST["lon"]} ";
}
if (array_key_exists('addr', $REQUEST)) {
$valueString .= "addr=\"{$REQUEST["addr"]}\" ";
}
if ($valueString == "") {
throw new Exception("No values were supplied to update the database");
}
print_sql($sql = "UPDATE location SET {$valueString} WHERE locationid={$REQUEST["locationid"]}");
if (($nrows = $dbh->exec($sql)) > 1) {
throw new Exception("Updated {$nrows} locations; expected 0 or 1...");
} elseif ($nrows == 0) {
throw new Exception("Updated {$nrows} locations; the supplied data were already in the database");
}
$dbh->commit();
echo json_response('success', null, null);
} catch (Exception $e) {
if ($dbh) {
$dbh->rollBack();
}
echo json_response('error', $e->getMessage(), null);
}
function dbQuery($sql, $parameters = array())
{
global $fullSql;
$fullSql = dbMakeQuery($sql, $parameters);
if (OBS_DEBUG > 0) {
// Pre query debug output
if (is_cli()) {
$debug_sql = explode(PHP_EOL, $fullSql);
print_message(PHP_EOL . 'SQL[%y' . implode('%n' . PHP_EOL . '%y', $debug_sql) . '%n]', 'console', FALSE);
} else {
print_sql($fullSql);
}
}
if (OBS_DEBUG > 0 || $GLOBALS['config']['profile_sql']) {
$time_start = microtime(true);
}
$result = dbCallQuery($fullSql);
// sets $this->result
if (OBS_DEBUG > 0 || $GLOBALS['config']['profile_sql']) {
$runtime = number_format(microtime(true) - $time_start, 8);
$debug_msg .= 'SQL RUNTIME[' . ($runtime > 0.05 ? '%r' : '%g') . $runtime . 's%n]';
if ($GLOBALS['config']['profile_sql']) {
#fwrite($this->logFile, date('Y-m-d H:i:s') . "\n" . $fullSql . "\n" . number_format($time_end - $time_start, 8) . " seconds\n\n");
$GLOBALS['sql_profile'][] = array('sql' => $fullSql, 'time' => $runtime);
}
}
if (OBS_DEBUG > 0) {
if ($result === FALSE && error_reporting() & 1) {
$error_msg = 'Error in query: (' . dbError() . ') ' . dbErrorNo();
$debug_msg .= PHP_EOL . 'ERROR[%r' . $error_msg . '%n]';
}
if (is_cli()) {
if (OBS_DEBUG > 1) {
$rows = dbAffectedRows();
$debug_msg = 'ROWS[' . ($rows < 1 ? '%r' : '%g') . $rows . '%n]' . PHP_EOL . $debug_msg;
}
// After query debug output for cli
print_message($debug_msg, 'console', FALSE);
} else {
print_error($error_msg);
}
}
if ($result === FALSE && isset($GLOBALS['config']['db']['debug']) && $GLOBALS['config']['db']['debug']) {
logfile('db.log', 'Failed dbQuery (#' . dbErrorNo() . ' - ' . dbError() . '), Query: ' . $fullSql);
}
return $result;
}
