/**
* Función para ejecutar consultar de tipo INSERT, UPDATE, DELETE
* @param array $parametros
* @param string $sql
*/
protected function ejecutar($parametros, $sql = null)
{
if ($sql == null) {
$sql = $this->sql;
}
$sentencia = $this->db->prepare($sql);
if (!$sentencia->execute($parametros)) {
self::enviarError($this->db->errorInfo());
}
}
public function evolve($buildingId)
{
$buildingCost = $this->db->prepare("SELECT\n lvl.food,\n lvl.gold\n FROM\n userbuildings ub\n INNER JOIN\n buildinglvldefs lvl ON ub.level_id + 1 = lvl.id\n WHERE\n ub.id = ?");
$buildingCost->execute([$buildingId]);
$buildingCost = $buildingCost->fetch();
if ($this->user->getGold() < $buildingCost['gold'] || $this->user->getFood() < $buildingCost['food']) {
throw new \Exception("Not enough resources.");
}
if (!$this->lvlCompare($buildingId)) {
throw new \Exception('Max level reached.');
}
$this->user->setGold($this->user->getGold() - $buildingCost['gold']);
$this->user->setFood($this->user->getFood() - $buildingCost['food']);
$update = $this->db->prepare("UPDATE\n userbuildings\n SET\n level_id = level_id + 1\n WHERE\n user_id = ?\n AND\n id = ?");
$update->execute([$_SESSION["user_id"], $buildingId]);
}
/**
* @todo: store user token from request globally in config object, use username / token to find user and so on
* @todo: logic must be in Usermodel, move later
* @returns models\User
*/
public function getMe()
{
$oDb = Db::getInstance();
$oUser = new models\User();
$aUser = $oDb->getArray(Db::prepare("SELECT * FROM %s;", [$oUser->getTableName()]));
return array_shift($aUser);
}
function tambahKategori($nmkategori)
{
$db = new Db();
$stmt = $db->prepare("INSERT INTO kategori (nmkategori) VALUES (?)");
$stmt->bindParam(1, $nmkategori);
$stmt->execute();
echo "Data Kategori berhasil di simpan";
}
public static function get_static_modifiers($user_id)
{
$sql = "SELECT * FROM modifiers WHERE user_id = :user_id";
$req = Db::prepare($sql);
$req->bindParam(':user_id', $user_id, PDO::PARAM_INT);
$req->execute();
return $res = $req->fetch(PDO::FETCH_ASSOC);
}
function tambahKategori($cat_name)
{
$db = new Db();
$stmt = $db->prepare("INSERT INTO part_cat (cat_name) VALUES (?)");
$stmt->bindParam(1, $cat_name);
$stmt->execute();
echo "Data Kategori berhasil di simpan";
}
function tampilLaptop($brand, $model, $thn_keluar)
{
$db = new Db();
$stmt = $db->prepare("SELECT * FROM laptop");
$stmt->bindParam(1, $brand);
$stmt->bindParam(2, $model);
$stmt->bindParam(3, $thn_keluar);
$stmt->execute();
}
function tampilLaptop($brand, $model, $release)
{
$db = new Db();
$stmt = $db->prepare("SELECT * FROM laptops");
$stmt->bindParam(1, $brand);
$stmt->bindParam(2, $model);
$stmt->bindParam(3, $release);
$stmt->execute();
echo "";
}
function tambahProduk($brand, $model, $release_year)
{
$db = new Db();
$stmt = $db->prepare("INSERT INTO komputer (brand, model, release_year) VALUES (?, ?, ?)");
$stmt->bindParam(1, $brand);
$stmt->bindParam(2, $model);
$stmt->bindParam(3, $release_year);
$stmt->execute();
echo "Sukses";
}
function tambahLaptop($brand, $model, $thn_keluar)
{
$db = new Db();
$stmt = $db->prepare("INSERT INTO laptop (brand, model, thn_keluar) VALUES (?, ?, ?)");
$stmt->bindParam(1, $brand);
$stmt->bindParam(2, $model);
$stmt->bindParam(3, $thn_keluar);
$stmt->execute();
echo "Data Laptop berhasi di simpan";
}
function tambahSukuCadang($id_laptop, $id_cat, $part_number, $description)
{
$db = new Db();
$stmt = $db->prepare("INSERT INTO sukucadang (id_laptop,id_cat,part_number,description) VALUES (?, ?, ?, ?)");
$stmt->bindParam(1, $id_laptop);
$stmt->bindParam(2, $id_cat);
$stmt->bindParam(3, $part_number);
$stmt->bindParam(4, $description);
$stmt->execute();
echo "Data Suku Cadang berhasil di simpan";
}
function tambahSukuCadang($laptop_id, $kategori_id, $no_sukucadang, $keterangan)
{
$db = new Db();
$stmt = $db->prepare("INSERT INTO sukucadang (laptop_id,kategori_id,no_sukucadang,keterangan) VALUES (?, ?, ?, ?)");
$stmt->bindParam(1, $laptop_id);
$stmt->bindParam(2, $kategori_id);
$stmt->bindParam(3, $no_sukucadang);
$stmt->bindParam(4, $keterangan);
$stmt->execute();
echo "Data Suku Cadang berhasil di simpan";
}
public function add()
{
$fields = $this->format_request();
$sql = "INSERT troops SET {$fields['sql_params']}\n ON DUPLICATE KEY UPDATE quantity = quantity + :quantity";
$req = Db::prepare($sql);
foreach ($fields['bind_params'] as $field) {
$req->bindParam($field['name'], $field['value'], $field['type']);
}
if ($req->execute()) {
return Db::getLastInsertId();
}
return false;
}
public function testInsertWithPreparedStatement()
{
$this->checkIfFooIsEmpty();
$o_stmt = $this->db->prepare("INSERT INTO foo (id, comment) VALUES (?, ?)");
$o_stmt->execute(array(1, 'bar'));
$qr_select = $this->db->query("SELECT * FROM foo");
$this->assertInternalType('object', $qr_select);
$this->assertTrue($qr_select->nextRow());
$this->assertEquals(1, $qr_select->get('id'));
$this->assertEquals('bar', $qr_select->get('comment'));
$this->db->query("DELETE FROM foo");
$this->checkIfFooIsEmpty();
}
public function destroy($id)
{
//remove session record from the database and return result
$res = $this->db->prepare("DELETE FROM {$this->table} WHERE id = :session_id");
$res->execute(array(':session_id' => $id, ':data' => $data));
if ($res->rowCount()) {
return true;
}
}
/**
* /**
* récupère toutes les flottes en cours d'attaque de l'utilisateur
* @param $user_id int
* @param $arrived bool
* @return array
*/
public static function get_attacking_troops($user_id, $arrived = false)
{
$where = $arrived ? 'AND arrival_time <= NOW()' : 'AND arrival_time > NOW()';
$sql = "SELECT m.arrival_time, m.id, u.pseudo FROM combats m\n JOIN users u on u.id = m.target_id\n WHERE attacker_id = :user_id {$where}";
$req = Db::prepare($sql);
$req->bindParam(':user_id', $user_id, PDO::PARAM_INT);
$req->execute();
if ($req->rowCount() > 0) {
return $req->fetchAll(PDO::FETCH_OBJ);
}
return [];
}
/**
*
*/
public function getRecentChangesAsRawData($pn_table_num, $pn_num_seconds = 604800, $pn_limit = 0)
{
// 604800 = number of seconds in one week
$o_db = new Db();
$qs_log = $o_db->prepare("\n\t\t\tSELECT DISTINCT\n\t\t\t\twcl.log_id, wcl.log_datetime log_datetime, wcl.user_id, wcl.changetype, wcl.logged_table_num, wcl.logged_row_id,\n\t\t\t\twclsnap.snapshot, wcl.unit_id, wu.email, wu.fname, wu.lname, wcls.subject_table_num, wcls.subject_row_id\n\t\t\tFROM " . $this->ops_change_log_database . ".ca_change_log wcl\n\t\t\tINNER JOIN " . $this->ops_change_log_database . ".ca_change_log_snapshots AS wclsnap ON wclsnap.log_id = wcl.log_id\n\t\t\tLEFT JOIN " . $this->ops_change_log_database . ".ca_change_log_subjects AS wcls ON wcl.log_id = wcls.log_id\n\t\t\tLEFT JOIN ca_users AS wu ON wcl.user_id = wu.user_id\n\t\t\tWHERE\n\t\t\t\t(\n\t\t\t\t\t((wcl.logged_table_num = ?) AND (wcls.subject_table_num IS NULL))\n\t\t\t\t\tOR\n\t\t\t\t\t(wcls.subject_table_num = ?)\n\t\t\t\t)\n\t\t\t\tAND (wcl.log_datetime > ?)\n\t\t\tORDER BY wcl.log_datetime DESC\n\t\t");
if ($pn_limit > 0) {
$qs_log->setLimit($pn_limit);
}
if ($qr_res = $qs_log->execute($pn_table_num, $pn_table_num, time() - $pn_num_seconds)) {
$va_log = array();
while ($qr_res->nextRow()) {
$va_log[] = $qr_res->getRow();
$va_log[sizeof($va_log) - 1]['snapshot'] = caUnserializeForDatabase($va_log[sizeof($va_log) - 1]['snapshot']);
}
return array_reverse($va_log);
}
return array();
}
/**
* déduit le temps écoulé des éléments en construction
* @param $time_diff int
*/
public function update_queue($time_diff)
{
$item = $this->get_first_item_from_queue();
if (!empty($item)) {
$time_left = $item['time_left'] - $time_diff;
if ($time_left > 0) {
$sql = "UPDATE queue SET time_left = time_left - :time_diff WHERE id = {$item['id']}";
$req = Db::prepare($sql);
$req->bindParam(':time_diff', $time_diff, PDO::PARAM_INT);
$req->execute();
} else {
// ajout de la flotte au stock et mise à jour du score
$fleet = new Army($this->user_id);
$fleet->add_troop($item['unit_id'], $item['quantity'], -1, true);
// effacement de l'élement de la file d'attente
$this->remove_item_from_queue($item['id']);
// on met à jour l'élément suivant de la file d'attente
$this->update_queue(abs($time_left));
}
}
}
public function editUser(User $user)
{
$result = $this->db->prepare("UPDATE users SET username = ?, password = ? WHERE id = ?");
$result->execute([$user->getUser(), $user->getPass(), $user->getId()]);
return $result->rowCount() > 0;
}
public function delete($uid)
{
$req = Db::prepare("DELETE FROM {$this->table} WHERE uid=:uid ");
if ($req) {
$req->execute(array(':uid' => $uid));
return $req->rowCount();
}
throw new Exception(__CLASS__ . '::' . __METHOD__ . '()');
}
/** Met à jour une valeur de la table
* @param $row_name
* @param $value
* @return mixed la nouvelle valeur mise à jour si la modification à fonctionné
* @throws Exception
*/
public function update_value($row_name, $value)
{
foreach ($this->def as $row) {
if ($row['name'] == $row_name) {
$this->{$row_name} = $value;
$sql = "UPDATE {$this->table} SET {$row_name} = :value WHERE id = {$this->id};";
$req = Db::prepare($sql);
$req->bindParam(':value', $value, $row['type']);
$req->execute();
return $this->{$row_name};
}
}
throw new Exception("le champ {$row_name} est introuvable dans {getClass({$this})}");
}
public static function get_id_from_pseudo($pseudo)
{
$sql = "SELECT id FROM users WHERE pseudo = :pseudo";
$req = Db::prepare($sql);
$req->bindParam(':pseudo', $pseudo, PDO::PARAM_STR);
$req->execute();
return $req->fetchColumn();
}
