<?php
namespace App\ExportData\Output; class DbHandler { static private $instance = NULL; private $dbConn = NULL; private $transaction = NULL; private $host = NULL; private $username = NULL; private $password = NULL; private $database = NULL; static public function getInstance() { if (self::$instance == NULL) { self::$instance = new self(); } return self::$instance; } public function setDatabaseCredentials($host, $username, $password, $database) { $this->host = $host; $this->username = $username; $this->password = $password; $this->database = $database; } private function connect() { $this->dbConn = ADONewConnection('mysqli'); $this->dbConn->NConnect($this->host, $this->username, $this->password, $this->database); if ($this->dbConn->ErrorNo()) { $msgs = new \App\MessagesCollector(); throw new \App\MessagesException($msgs->addError(sprintf("Cannot connect to database. Error message: %s", $this->dbConn->ErrorMsg()))); } $this->dbConn->setFetchMode(ADODB_FETCH_ASSOC); } private function execute($sql, Array $values=[]) { if (!$this->dbConn) { $this->connect(); } return $this->dbConn->Execute($sql, $values); } public function insert($dbTable, Array $saveData) { $insertSQL = "INSERT INTO `{$dbTable}` SET "; $index = 0; $values = []; foreach ($saveData as $field => $value) { $index++; if ($index != 1) { $insertSQL .= ", "; } $insertSQL .= "`{$field}`=?"; $values[] = $value; } return $this->execute($insertSQL, $values); } public function update($dbTable, $primaryKeyName, $primaryKeyValue, Array $saveData) { $updateSQL = "UPDATE `{$dbTable}` SET "; $index = 0; $values = []; foreach($saveData as $field => $value) { $index++; if ($index!=1) { $updateSQL .= ", "; } $updateSQL .= "`{$field}`=?"; $values[] = $value; } $updateSQL .= " WHERE `{$primaryKeyName}`=?"; $values[] = $primaryKeyValue; return $this->execute($updateSQL, $values); } public function delete($dbTable, $primaryKeyName, $primaryKeyValue) { $values = []; $deleteSQL = "DELETE FROM `{$dbTable}` WHERE `{$primaryKeyName}`=?"; $values[] = $primaryKeyValue; return $this->execute($deleteSQL, $values); } public function startTransaction() { if ($this->transaction) { throw new \Exception("Starting transaction failed because another has already been started"); } if (!$this->execute("START TRANSACTION")) { throw new \DBException('`START TRANSACTION` failed.'); } $this->transaction = true; } public function commitTransaction() { if (!$this->transaction) { throw new \DBException('You cannot COMMIT because no transaction has been started.'); } if (!$this->execute("COMMIT")) { throw new \DBException('`COMMIT` failed.'); } $this->transaction = false; } public function rollbackTransaction() { if (!$this->transaction) { throw new \DBException('You cannot ROLLBACK because no transaction has been started.'); } if (!$this->execute("ROLLBACK")) { throw new \DBException('`ROLLBACK` failed.'); } $this->transaction = false; } public function close() { if ($this->dbConn) { $this->dbConn->Close(); unset($this->dbConn); } } public function getRow($tableName, $primaryKeyName, $primaryKeyValue) { if (!$this->dbConn) { $this->connect(); } $sql = "SELECT * from `{$tableName}` WHERE `{$primaryKeyName}`=?"; $result = $this->dbConn->GetRow($sql, (Array)$primaryKeyValue); if ($this->dbConn->ErrorNo()) { throw new \DBException(sprintf('Cannot select information from table `%s` with primary key name `%s` and primary key value `%s`. Error message: %s', $tableName, $primaryKeyName, $primaryKeyValue, $this->dbConn->ErrorMsg())); } return $result; } public function getErrorMessage() { return $this->dbConn->ErrorMsg(); } }