public static function delete_role($id) { $query = "\n SELECT\n r.id AS role_id,\n COUNT(u.id) AS user_count\n FROM roles AS r\n LEFT JOIN users AS u ON (u.role_id = r.id)\n WHERE (r.id = {$id})\n GROUP BY r.id"; $roles = new Roles(); $res = new Phalcon\Mvc\Model\Resultset\Simple(null, $roles, $roles->getReadConnection()->query($query)); $uc = $res->toArray(); if (!$uc) { return -1; } if ($uc[0]['user_count'] > 0) { return $uc[0]['user_count']; } $query = "\n DELETE FROM role_right AS rl_rt\n WHERE rl_rt.role_id IN (\n SELECT rls.id\n FROM roles AS rls\n WHERE (rls.id = " . $uc[0]['role_id'] . ")\n ); "; $res = new Phalcon\Mvc\Model\Resultset\Simple(null, $roles, $roles->getReadConnection()->query($query)); $query = "DELETE FROM roles WHERE (id = " . $uc[0]['role_id'] . ");"; $res = new Phalcon\Mvc\Model\Resultset\Simple(null, $roles, $roles->getReadConnection()->query($query)); return $uc[0]['user_count']; }
public static function addStation($data) { /* full_name_value VARCHAR(255), full_name_en_value VARCHAR(255), station_code_value INTEGER, full_address_value VARCHAR(255), full_address_en_value VARCHAR(255), longitude_value REAL, width_value REAL, country_id_value INTEGER, station_status_id_value INTEGER, ttl_persons_cache_value INTEGER, branch_office_value INTEGER DEFAULT NULL, airport_value VARCHAR(255) DEFAULT NULL */ $stations = new Stations(); $branch_office = null; $airport = null; if (isset($data['branch_office'])) { $branch_office = ", " . $data['branch_office'] . " "; } if (isset($data['airport'])) { $airport = ", '" . pg_escape_string($data['airport']) . "' "; } $query = "SELECT stations_list_add(" . "'" . pg_escape_string($data['name']) . "', " . "'" . pg_escape_string($data['name_en']) . "', " . $data['code'] . ", " . "'" . pg_escape_string($data['address']) . "', " . "'" . pg_escape_string($data['address_en']) . "', " . $data['l'] . ", " . $data['w'] . ", " . $data['country_id'] . ", " . $data['station_status_id'] . ", " . $data['ttl_persons_cache'] . " " . $branch_office . $airport . ");"; $res = new Phalcon\Mvc\Model\Resultset\Simple(null, $stations, $stations->getReadConnection()->query($query)); if ($res->toArray()[0]['stations_list_add'] == -1) { return array('class' => 'alert-warning', 'text' => "<p>Запись <b>" . $data['name'] . " не</b> была <b>добавлена</b>, т. к. запись с такими данными уже <b>существует</b> в базе.<br> " . "Следующие значения не должны повторяться: " . "<ul>" . "<li>Название: <b>" . $data['name'] . "</b>;</li>" . "<li>Назв. (en): <b>" . $data['name_en'] . "</b>;</li>" . "<li>Адрес: <b>" . $data['address'] . "</b>;</li>" . "<li>Адр. (en): <b>" . $data['address_en'] . "</b>.</li>" . "</ul></p>"); } return array('class' => 'alert-success', 'text' => "<p>Добавление <b>" . $data['name'] . "</b> произошло успешно.</p>"); }
public static function addUser($data) { /* station_id_value INTEGER, role_id_value INTEGER, language_id_value INTEGER, currency_id_value INTEGER, unit_id1_value INTEGER, unit_id2_value INTEGER, unit_id3_value INTEGER, first_name_value VARCHAR(255), last_name_value VARCHAR(255), e_mail_value VARCHAR(255), password_value VARCHAR(255), blocked_value BOOLEAN DEFAULT FALSE, middle_name_value VARCHAR(255) DEFAULT NULL, description_value VARCHAR(255) DEFAULT NULL */ $blocked = "FALSE"; if (!empty($data['blocked'])) { $blocked = "TRUE"; } $user = new Users(); $query = "SELECT users_add(" . $data['station_id'] . ", " . $data['role_id'] . ", " . $data['language_id'] . ", " . $data['currency_id'] . ", " . $data['unit_id1'] . ", " . $data['unit_id2'] . ", " . $data['unit_id3'] . ", " . "'" . pg_escape_string($data['first_name']) . "', " . "'" . pg_escape_string($data['last_name']) . "', " . "'" . pg_escape_string($data['e_mail']) . "', " . "'" . pg_escape_string($data['password']) . "', " . $blocked . ", " . "'" . pg_escape_string($data['middle_name']) . "', " . "'" . pg_escape_string($data['description']) . "' " . ");"; $res = new Phalcon\Mvc\Model\Resultset\Simple(null, $user, $user->getReadConnection()->query($query)); if ($res->toArray()[0]['users_add'] == -1) { return array('class' => 'alert-warning', 'text' => "<p>Профиль <b>" . $data['first_name'] . " " . $data['last_name'] . " не</b> был <b>добавлен</b>, т. к. запись с таким e-mail уже <b>существует</b> в базе.<br> " . "Следующие значения не должны повторяться: " . "<ul>" . "<li>e-mail: <b>" . $data['e_mail'] . "</b>;</li>" . "</ul></p>"); } return array('class' => 'alert-success', 'text' => "<p>Добавление <b>" . $data['name'] . "</b> произошло успешно.</p>"); }
public static function addPersonNew($full_name, $full_address, $country_id, $status, $code, $code_prefix, $phone, $statin_id) { /* SELECT person_add( recipient_full_name_value, recipient_full_address_value, recipient_country_id_value, 2::smallint, recipient_code_value, recipient_code_prefix_value, recipient_phone_value ) INTO person_id; PERFORM persons_cache_add( person_id, station_id_value, (SELECT CURRENT_TIMESTAMP)::timestamp ); /**/ $references = new References(); $query = "SELECT * FROM person_add_new(" . "'" . pg_escape_string($full_name) . "', " . "'" . pg_escape_string($full_address) . "', " . $country_id . ", " . $status . "::smallint, " . "'" . pg_escape_string($code) . "', " . $code_prefix . "::smallint, " . "'" . pg_escape_string($phone) . "' " . ");"; $res = new Phalcon\Mvc\Model\Resultset\Simple(null, $references, $references->getReadConnection()->query($query)); $tmp = $res->toArray(); if ($tmp[0][0]['person_added']) { } else { $codes = ""; foreach ($tmp as $item) { if ($codes == "") { $codes .= $item['person_code']; } else { $codes .= ", " . $item['person_code']; } } return array('class' => 'alert-warning', 'text' => "<p>Новая запись <b>не</b> была <b>добавлена</b>, т. к. такая запись уже <b>существует</b> в базе и имеет <b>код (ы): {$codes}</b>.</p>"); } return $query; if ($res->toArray()[0]['languages_list_add'] == -1) { return array('class' => 'alert-warning', 'text' => "<p>Запись <b>{$name} не</b> была <b>добавлена</b>, т. к. запись с таким названием уже <b>существует</b> в базе.</p>"); } return array('class' => 'alert-success', 'text' => "<p>Добавление <b>{$name}</b> произошло успешно.</p>"); }
/** * 执行原生的查询sql * @params string $sql 查询的sql * @params array $params 查询的参数 * @params bool $read 是否是读实例 * @return false or array 执行失败返回false, 成功返回数组 */ public function execSelect($sql, $params = null) { try { $connect = $this->getReadConnection(); $result = $connect->query($sql, $params); if (false === $result) { LoggerUtil::error($connect->getErrorInfo()); return false; } $result = new \Phalcon\Mvc\Model\Resultset\Simple(null, $this, $result); return $result->toArray(); } catch (\Exception $e) { LoggerUtil::error($e->getMessage()); return false; } }
public static function setStatus($package_id_value, $status_id_value, $stored_value, $user_id_value, $user_courier_id_value) { $query = "SELECT package_set_status("; $query .= $package_id_value . ","; $query .= $status_id_value . ","; $query .= $stored_value . ","; $query .= $user_id_value . ","; $query .= $user_courier_id_value; $query .= ")"; //return $query; $package = new Package(); $res = new Phalcon\Mvc\Model\Resultset\Simple(null, $package, $package->getReadConnection()->query($query)); return $res->toArray(); }