public function fire()
{
$db = new Db(Db::PGSQL, env('DB_DATABASE'), env('DB_USERNAME'), env('DB_PASSWORD'), env('DB_HOST', 'localhost'));
$args = $this->input->getArguments();
$email = strtolower(trim($args['email']));
$password = \Hash::make($args['password']);
$table = "`{$args['schema']}`.`{$args['table']}`";
$exists = $db->processRecords($db->query(DbExpr::create("SELECT 1 FROM {$table} WHERE `email`=``{$email}``")), $db::FETCH_VALUE);
if ($exists > 0) {
$query = "UPDATE {$table} SET `password`=``{$password}``, `role`=``{$args['role']}`` WHERE `email`=``{$email}``";
} else {
$query = "INSERT INTO {$table} (`email`, `password`, `role`) VALUES (``{$email}``,``{$password}``, ``{$args['role']}``)";
}
try {
$result = $db->exec(DbExpr::create($query));
if ($result > 0) {
$this->line($exists ? 'Admin updated' : 'Admin created');
} else {
$this->line('Fail. DB returned "0 rows updated"');
}
} catch (\Exception $exc) {
$this->line('Fail. DB Exception:');
$this->line($exc->getMessage());
$this->line($exc->getTraceAsString());
}
}
public static function getTimezonesList($asOptions = false)
{
if (self::$timeZonesList === null) {
$ds = self::_getDataSource('default');
$query = $ds->replaceQuotes(DbExpr::create('SELECT * from `pg_timezone_names` ORDER BY `utc_offset` ASC')->get());
self::$timeZonesList = Db::processRecords($ds->query($query), Db::FETCH_ALL);
}
if ($asOptions) {
if (self::$timeZonesOptions === null) {
self::$timeZonesOptions = [];
foreach (self::$timeZonesList as $tzInfo) {
$offset = preg_replace('%:\\d\\d$%', '', $tzInfo['utc_offset']);
$offsetPrefix = $offset[0] === '-' ? '' : '+';
self::$timeZonesOptions[$tzInfo['name']] = "({$offsetPrefix}{$offset}) {$tzInfo['name']}";
}
}
return self::$timeZonesOptions;
} else {
return self::$timeZonesList;
}
}
/**
* @param string $schema
* @param string $queryTpl
* @param array $tables - empty value: just execute $queryTpl
* @param null|string $testQueryTpl
* @throws \PeskyORM\Exception\DbException
*/
public function executeQueryOnSchema($schema, $queryTpl, array $tables = [], $testQueryTpl = null)
{
$this->out('DB Schema: ' . $schema);
$ds = $this->getConnection();
if (!empty($tables)) {
foreach ($tables as $tableName) {
$this->out('Update table: ' . $tableName);
$query = StringUtils::insert($queryTpl, ['table' => $tableName, 'schema' => $schema]);
if (!empty($testQueryTpl)) {
$test = StringUtils::insert($testQueryTpl, ['table' => $tableName, 'schema' => $schema]);
$stmnt = $ds->query(DbExpr::create($test));
if ($stmnt) {
$exists = Db::processRecords($stmnt, DB::FETCH_VALUE);
if (!empty($exists)) {
$this->out('- Object already exists');
} else {
$ds->exec(DbExpr::create($query));
$this->out('+ Done');
}
} else {
$this->out('- Failed to test if Object already exists');
}
} else {
$ds->exec(DbExpr::create($query));
$this->out('+ Done');
}
}
} else {
$query = $queryTpl;
if (!empty($testQueryTpl)) {
$test = StringUtils::insert($testQueryTpl, ['schema' => $schema]);
$stmnt = $ds->query(DbExpr::create($test));
if ($stmnt) {
$exists = Db::processRecords($stmnt, DB::FETCH_VALUE);
if (!empty($exists)) {
$this->out('- Object already exists');
} else {
$ds->exec(DbExpr::create($query));
$this->out('+ Done');
}
} else {
$this->out('- Failed to test if Object already exists');
}
} else {
$ds->exec(DbExpr::create($query));
$this->out('+ Done');
}
}
$ds->disconnect();
}