/**
* Main query generator
*
* @param string $sql String of SQL code to execute
* @param array $paramArray Array of parameters to bind, bind types as
* first element of the array. Can send array of arrays, in which case
* it will be treated as a transaction.
* @param string $db Optional, send if a different database is to be used
* for just this query.
* @return boolean|\DBResult Returns false if query fails, otherwise result
* object
*/
public static function query($sql, $paramArray = NULL, $db = NULL, $auto_clean = NULL, $index = NULL)
{
// For function flexibility can send dbname as 2nd parameter
$log = false;
if ($log) {
Timer::start();
}
if ($paramArray && !is_array($paramArray)) {
$db = $paramArray;
$paramArray = NULL;
}
// Get database handler
$dbh = self::getDBh($db);
// If query fails to initialise then print error message
if (!$dbh || !($stmt = $dbh->prepare($sql))) {
echo "<p>DB failure on database " . self::getDB() . " with query {$sql}";
if ($paramArray) {
"and parameters - ";
print_r($paramArray);
}
echo "</p>";
echo "<p>With error message - ";
echo $dbh->error;
echo "</p>";
echo "<pre>";
var_dump(debug_backtrace());
var_dump($db);
echo "</pre>";
exit;
}
$affRows = 0;
// Bind parameters if supplied
if ($paramArray) {
// Allows an array of parameters to be applied to the same prepared
// statement
// Turns off autocommit for 10x speed!
if (is_array($paramArray[0])) {
self::begin();
foreach ($paramArray as $pA) {
self::bindParam($stmt, $pA);
$affRows += $stmt->affected_rows;
}
self::commit();
// Single set of parameters only
} else {
self::bindParam($stmt, $paramArray);
$affRows += $stmt->affected_rows;
}
// No parameters, just execute the sql
} else {
$stmt->execute();
$affRows += $stmt->affected_rows;
}
$meta = $stmt->result_metadata();
$result = new DBResult();
// If no metadata then INSERT, UPDATE etc. so return id data
if (!$meta) {
$result->affected_rows = $affRows;
// Insert ID of last inserted if multiple done
$result->insert_id = $stmt->insert_id;
// If metadata then retrieve and store results
} else {
$stmt->store_result();
// If no results from the SELECT return false
$result->num_rows = $stmt->num_rows;
$params = array();
$row = array();
$result->rows = array();
// Get field names from meta
while ($field = $meta->fetch_field()) {
$params[] =& $row[$field->name];
}
$meta->close();
call_user_func_array(array($stmt, 'bind_result'), $params);
// Create result object as $result->rows[]->fieldnames
while ($stmt->fetch()) {
$rowobj = new StdClass();
foreach ($row as $key => $value) {
if ($auto_clean) {
$value = HTML::clean($value);
}
$rowobj->{$key} = $value;
}
if ($index) {
$result->rows[$rowobj->{$index}] = $rowobj;
} else {
$result->rows[] = $rowobj;
}
}
$stmt->free_result();
}
$stmt->close();
if ($log) {
$time = Timer::result();
if (self::getDB() != 'dbusers') {
$logsql = "INSERT INTO dbLog ( `sql`, params, `time`, `dbfn` ) VALUES ( ?, ?, ?, 'DB' )";
$pA = array($sql, serialize($paramArray), $time);
DBP::query($logsql, $pA, 'ecrf');
}
Timer::stop();
}
return $result;
}
/**
* @Route("/wotr/ajax/units/{id}")
* @ParamConverter("post", class="WotRBundle:Game", options={"repository_method" = "getCurrentGame"})
* @param Game $game
* @param Request $request
* @return JsonResponse|Response
*/
public function ajaxUnitsAction(Game $game, Request $request)
{
if ($request->isXMLHttpRequest()) {
$units = $game->getUnits();
$outputArr = array();
/** @var Unit $u */
foreach ($units as $u) {
if ($u->getLocation()) {
$outputArr[$u->getId()] = array('unitName' => $u->getName(), 'loc' => $u->getLocation()->getId(), 'sideId' => $u->getSide()->getId());
}
}
$response = new JsonResponse();
$response->setData(array('data' => $outputArr));
return $response;
}
return new Response('This is not ajax!', 400);
$unitTable = 'units';
$request = new Request();
switch ($request->getProperty('request')) {
case 'units':
$sql = "SELECT {$unitTable}.id, loc, unit.name as unitName, unitType.name as type, " . "nation.side_id as sideId, nation.name as nation FROM {$unitTable} " . "LEFT JOIN unit ON unit_id = unit.id " . "LEFT JOIN nation ON nation_id = nation.id " . "LEFT JOIN unitType on unitType_id = unitType.id " . "WHERE loc != 0 " . "ORDER BY {$unitTable}.loc";
$unitsQ = DBP::query($sql);
$outputArr = array();
foreach ($unitsQ->rows as $unit) {
$outputArr[$unit->id] = array('unitName' => $unit->unitName, 'loc' => $unit->loc, 'type' => $unit->type, 'nation' => $unit->nation, 'sideId' => $unit->sideId);
}
echo json_encode($outputArr);
break;
case 'recruitable':
$sql = "SELECT {$unitTable}.id, IF( ISNULL(unit.name),CONCAT(nation.name, ' ', unitType.name, ' (', COUNT({$unitTable}.id), ' remaining)'),unit.name) AS unitName " . "FROM {$unitTable} " . "LEFT JOIN unit ON {$unitTable}.unit_id = unit.id " . "LEFT JOIN nation ON nation.id = nation_id " . "LEFT JOIN unitType ON unitType.id = unitType_id " . "WHERE loc = 0 AND ( side_id = 2 || casualty != 1 ) AND unit.unitType_id <= 4 " . "GROUP BY CONCAT(nation.name, ' ', unitType.name) " . "UNION ALL " . "SELECT {$unitTable}.id, unit.name AS unitName FROM {$unitTable} " . "LEFT JOIN unit ON {$unitTable}.unit_id = unit.id " . "LEFT JOIN nation ON nation.id = nation_id " . "LEFT JOIN unitType ON unitType.id = unitType_id " . "WHERE loc = 0 AND casualty != 1 AND unit.unitType_id >= 5 " . "ORDER BY id";
$units = DBP::query($sql, NULL, NULL, 'keyPair');
echo json_encode($units->rows);
break;
case 'recruit':
$unitID = $request->getProperty('unit');
$regionID = $request->getProperty('region');
if ($regionID && $unitID) {
$sql = "UPDATE {$unitTable} SET loc = ? WHERE id = ?";
$pA = array($regionID, $unitID);
DBP::query($sql, $pA);
echo "Unit recruited.";
} else {
echo "Please select a unit to recruit.";
}
break;
case 'move':
$unitIDs = $request->getProperty('selectedUnits');
$dest = $request->getProperty('dest');
if (is_array($unitIDs)) {
$qMarks = str_repeat('?,', count($unitIDs) - 1) . '?';
$sql = "UPDATE {$unitTable} SET loc = ? WHERE id IN ({$qMarks})";
array_unshift($unitIDs, $dest);
DBP::query($sql, $unitIDs);
echo "Units moved";
} else {
echo "Please select units to move";
}
break;
case 'remove':
$unitIDs = $request->getProperty('selectedUnits');
$casualty = $request->getProperty('casualty');
if (is_array($unitIDs)) {
$qMarks = str_repeat('?,', count($unitIDs) - 1) . '?';
$sql = "UPDATE {$unitTable} SET loc = 0, casualty = ? WHERE id IN ({$qMarks})";
array_unshift($unitIDs, $casualty);
DBP::query($sql, $unitIDs);
echo "Units removed";
} else {
echo "Please select units to remove";
}
break;
case 'reduce':
$unitIDs = $request->getProperty('selectedUnits');
$regionID = $request->getProperty('region');
$return = '';
foreach ($unitIDs as $unitID) {
$sql = "SELECT nation_id FROM {$unitTable} " . "LEFT JOIN unit ON {$unitTable}.unit_id = unit.id " . "WHERE {$unitTable}.id = ?";
$result = DBP::query($sql, array($unitID));
$nation = $result->nation_id;
$sql = "SELECT {$unitTable}.id, casualty FROM {$unitTable} " . "LEFT JOIN unit ON {$unitTable}.unit_id = unit.id " . "WHERE nation_id = ? AND loc = 0 AND unitType_id = 1 " . "ORDER BY casualty DESC";
$regulars = DBP::query($sql, array($nation));
if ($regulars->getRows()) {
$sql = "UPDATE {$unitTable} SET loc = 0, casualty = 1 WHERE id = ?";
$pA = array($unitID);
DBP::query($sql, $pA);
$sql = "UPDATE {$unitTable} SET loc = ?, casualty = 0 WHERE id = ?";
$pA = array($regionID, $regulars->id);
DBP::query($sql, $pA);
$return = 'Elite reduced';
} else {
$return = 'No regular to reduce elite';
}
}
echo $return;
}
}
