PHP DBLayer::execute方法代码示例

 /**
  * Get the (external) id of the user assigned to a ticket
  * @param $ticket_id the Id of the ticket that's being queried
  * @return The (external)id of the user being assigned to the ticket
  */
 public static function getUserAssignedToTicket($ticket_id)
 {
     $dbl = new DBLayer("lib");
     $statement = $dbl->execute("SELECT ticket_user.ExternId FROM `assigned` JOIN `ticket_user` ON assigned.User = ticket_user.TUserId WHERE `Ticket` = :ticket_id", array('ticket_id' => $ticket_id));
     $user_id = $statement->fetch();
     return $user_id['ExternId'];
 }

 /**
  * return all replies on a specific ticket.
  * @param $ticket_id the id of the ticket of which we want the replies.
  * @param $view_as_admin if the browsing user is an admin/mod it should be 1, this will also show the hidden replies.
  * @return an array with ticket_reply objects (beware the author and content are objects on their own, not integers!)
  */
 public static function getRepliesOfTicket($ticket_id, $view_as_admin)
 {
     $dbl = new DBLayer("lib");
     $statement = $dbl->execute("SELECT * FROM ticket_reply INNER JOIN ticket_content INNER JOIN ticket_user ON ticket_reply.Content = ticket_content.TContentId and ticket_reply.Ticket=:id and ticket_user.TUserId = ticket_reply.Author ORDER BY ticket_reply.TReplyId ASC", array('id' => $ticket_id));
     $row = $statement->fetchAll();
     $result = array();
     foreach ($row as $tReply) {
         //only add hidden replies if the user is a mod/admin
         if (!$tReply['Hidden'] || $view_as_admin) {
             //load author
             $instanceAuthor = Ticket_User::constr_TUserId($tReply['Author']);
             $instanceAuthor->setExternId($tReply['ExternId']);
             $instanceAuthor->setPermission($tReply['Permission']);
             //load content
             $instanceContent = new Ticket_Content();
             $instanceContent->setTContentId($tReply['TContentId']);
             $instanceContent->setContent($tReply['Content']);
             //load reply and add the author and content object in it.
             $instanceReply = new self();
             $instanceReply->setTReplyId($tReply['TReplyId']);
             $instanceReply->setTimestamp($tReply['Timestamp']);
             $instanceReply->setAuthor($instanceAuthor);
             $instanceReply->setTicket($ticket_id);
             $instanceReply->setContent($instanceContent);
             $instanceReply->setHidden($tReply['Hidden']);
             $result[] = $instanceReply;
         }
     }
     return $result;
 }

 /**
  * Constructor.
  * will fetch the correct elements that match to a specific page (specified by the $_GET['pagenum'] variable). The query has to be passed as a string to the function
  * that way it will only load the specific elements that are related to the pagenumber. The $params, parameter is optional and is used to pass the parameters for the query.
  * The result class will be used to instantiate the found elements with, their set() function will be called. The class  its getters can be later used to get the info out of the object.
  * @param $query the query to be paginated
  * @param $db the db on which the query should be performed
  * @param $nrDisplayed the amount of elements that should be displayed /page
  * @param $resultClass the elements that should be returned should be of that specific class.
  * @param $params the parameters used by the query (optional)
  */
 function __construct($query, $db, $nrDisplayed, $resultClass, $params = array())
 {
     if (!isset($_GET['pagenum'])) {
         $this->current = 1;
     } else {
         $this->current = $_GET['pagenum'];
     }
     //Here we count the number of results
     $db = new DBLayer($db);
     $rows = $db->execute($query, $params)->rowCount();
     $this->amountOfRows = $rows;
     //the array hat will contain all users
     if ($rows > 0) {
         //This is the number of results displayed per page
         $page_rows = $nrDisplayed;
         //This tells us the page number of our last page
         $this->last = ceil($rows / $page_rows);
         //this makes sure the page number isn't below one, or more than our maximum pages
         if ($this->current < 1) {
             $this->current = 1;
         } else {
             if ($this->current > $this->last) {
                 $this->current = $this->last;
             }
         }
         //This sets the range to display in our query
         $max = 'limit ' . ($this->current - 1) * $page_rows . ',' . $page_rows;
         //This is your query again, the same one... the only difference is we add $max into it
         $data = $db->execute($query . " " . $max, $params);
         $this->element_array = array();
         //This is where we put the results in a resultArray to be sent to smarty
         while ($row = $data->fetch(PDO::FETCH_ASSOC)) {
             $element = new $resultClass();
             $element->set($row);
             $this->element_array[] = $element;
         }
     }
 }

/**
 * Global Hook to return global variables which contains
 * the content to use in the smarty templates extracted from
 * the database
 *
 * @return $domain_management_return_set global array returns the template data
 */
function domain_management_hook_get_db()
{
    global $domain_management_return_set;
    if (isset($_GET['ModifyDomain']) && ($_GET['ModifyDomain'] = '1' && isset($_POST['domain_name']))) {
        try {
            $dbs = new DBLayer('shard');
            $dbs->update("domain", array('domain_name' => $_POST['domain_name'], 'status' => $_POST['status'], 'patch_version' => $_POST['patch_version'], 'backup_patch_url' => $_POST['backup_patch_url'], 'patch_urls' => $_POST['patch_urls'], 'login_address' => $_POST['login_address'], 'session_manager_address' => $_POST['session_manager_address'], 'ring_db_name' => $_POST['ring_db_name'], 'web_host' => $_POST['web_host'], 'web_host_php' => $_POST['web_host_php'], 'description' => $_POST['description']), '`domain_id` = ' . $_GET['edit_domain']);
        } catch (Exception $e) {
            return null;
        }
    }
    if (isset($_GET['ModifyPermission']) && ($_GET['ModifyPermission'] = '1' && isset($_POST['user']))) {
        try {
            $dbl = new DBLayer("lib");
            $statement = $dbl->execute("SELECT * FROM `settings` WHERE `Setting` = :setting", array('setting' => 'Domain_Auto_Add'));
            $json = $statement->fetch();
            $json = json_decode($json['Value'], true);
            $json[$_GET['edit_domain']]['1'] = $_POST['user'];
            $json[$_GET['edit_domain']]['2'] = $_POST['moderator'];
            $json[$_GET['edit_domain']]['3'] = $_POST['admin'];
            $update = json_encode($json);
            $dbl->update("settings", array('Value' => $update), "`Setting` = 'Domain_Auto_Add'");
        } catch (Exception $e) {
            return null;
        }
    }
    try {
        $db = new DBLayer('shard');
        // get all domains
        $statement = $db->executeWithoutParams("SELECT * FROM domain");
        $rows = $statement->fetchAll();
        $domain_management_return_set['domains'] = $rows;
        if (isset($_GET['edit_domain'])) {
            // get permissions
            $statement = $db->executeWithoutParams("SELECT * FROM `domain` WHERE `domain_id` = '" . $_GET['edit_domain'] . "'");
            $rows = $statement->fetchAll();
            $domain_management_return_set['domains'] = $rows;
            $statement = $db->executeWithoutParams("SELECT * FROM `permission` WHERE `DomainId` = '" . $_GET['edit_domain'] . "'");
            $rows = $statement->fetchAll();
            $domain_management_return_set['permissions'] = $rows;
            // get all users
            $pagination = new Pagination(WebUsers::getAllUsersQuery(), "web", 10, "WebUsers");
            $domain_management_return_set['userlist'] = Gui_Elements::make_table($pagination->getElements(), array("getUId", "getUsername", "getEmail"), array("id", "username", "email"));
            $dbl = new DBLayer("lib");
            $statement = $dbl->execute("SELECT * FROM `settings` WHERE `Setting` = :setting", array('setting' => 'Domain_Auto_Add'));
            $json = $statement->fetch();
            $json = json_decode($json['Value'], true);
            $domain_management_return_set['Domain_Auto_Add'] = $json[$_GET['edit_domain']];
        }
        return $rows;
    } catch (Exception $e) {
        return null;
    }
}

 /**
  * return the latest reply of a ticket
  * @param $ticket_id the id of the ticket.
  * @return a ticket_reply object.
  */
 public static function getLatestReply($ticket_id)
 {
     $dbl = new DBLayer("lib");
     $statement = $dbl->execute("SELECT * FROM ticket_reply WHERE Ticket =:id ORDER BY TReplyId DESC LIMIT 1 ", array('id' => $ticket_id));
     $reply = new Ticket_Reply();
     $reply->set($statement->fetch());
     return $reply;
 }

 /**
  * creates permissions in the shard db for a user.
  * incase the shard is offline it will place it in the ams_querycache.
  * @param $pvalues with username
  */
 public static function createPermissions($pvalues)
 {
     try {
         $values = array('username' => $pvalues[0]);
         $dbs = new DBLayer("shard");
         $sth = $dbs->selectWithParameter("UId", "user", $values, "Login= :username");
         $result = $sth->fetchAll();
         $dbl = new DBLayer("lib");
         $UId = $result['0']['UId'];
         $statement = $dbl->execute("SELECT * FROM `settings` WHERE `Setting` = :setting", array('setting' => 'Domain_Auto_Add'));
         $json = $statement->fetch();
         $json = json_decode($json['Value'], true);
         $db = new DBLayer('shard');
         // get all domains
         $statement = $db->executeWithoutParams("SELECT * FROM domain");
         $rows = $statement->fetchAll();
         //error_log(print_r($rows,true));
         //error_log(print_r($result,true));
         //error_log(print_r($json,true));
         foreach ($json as $key => $value) {
             //error_log(print_r($key,true));
             //error_log(print_r($value,true));
             $ins_values = array('UId' => $UId, 'DomainId' => $key, 'AccessPrivilege' => $value['1']);
             error_log(print_r($ins_values, true));
             $dbs = new DBLayer("shard");
             $dbs->insert("permission", $ins_values);
         }
     } catch (PDOException $e) {
         //oh noooz, the shard is offline! Put it in query queue at ams_lib db!
         $dbl = new DBLayer("lib");
         $dbl->insert("ams_querycache", array("type" => "createPermissions", "query" => json_encode(array($pvalues[0])), "db" => "shard"));
     }
     return true;
 }

//require the pages that are being needed.
require '../../config.php';
require '../../../ams_lib/libinclude.php';
ini_set("display_errors", true);
error_reporting(E_ALL);
//var used to access the DB;
global $cfg;
try {
    //SETUP THE WWW DB
    $dbs = new DBLayer("shard");
    $sql = "SELECT * FROM user";
    $statement = $dbs->executeWithoutParams($sql);
    $users = $statement->fetchAll();
    foreach ($users as $user) {
        //add user to web
        $dbw = new DBLayer("web");
        if (!$dbw->execute("SELECT * FROM ams_user WHERE Login = :name", array('name' => $user['Login']))->rowCount()) {
            $query = "INSERT INTO ams_user (Login, Password, Email, Language) VALUES (:name, :pass, :mail, :lang)";
            global $DEFAULT_LANGUAGE;
            $vars = array('name' => $user['Login'], 'pass' => $user['Password'], 'mail' => $user['Email'], 'lang' => $DEFAULT_LANGUAGE);
            $id = $dbw->executeReturnId($query, $vars);
            $dbl = new DBLayer("lib");
            $query = "INSERT INTO `ticket_user` (Permission, ExternId) VALUES (1, :id)";
            $vars = array('id' => $id);
            $dbl->execute($query, $vars);
        }
    }
    print "The users were imported! ";
} catch (PDOException $e) {
    print "There was an error while creating the admin account! ";
}

             require_once $AMS_LIB . '/libinclude.php';
         } catch (Exception $e) {
             printalert("danger", "Failed to include AMS <em>libinclude.php</em>");
             $continue = false;
         }
     }
     // Create AMS Admin user
     if ($continue) {
         $hashpass = crypt($_POST["amsAdminPassword"], Users::generateSALT());
         $params = array('Login' => $_POST["amsAdminUsername"], 'Password' => $hashpass, 'Email' => "localhost@localhost");
         try {
             $user_id = WebUsers::createWebuser($params['Login'], $params['Password'], $params['Email']);
             $result = Webusers::createUser($params, $user_id);
             Users::createPermissions(array($params['Login']));
             $dbl = new DBLayer("lib");
             $dbl->execute("UPDATE ticket_user SET Permission = 3 WHERE TUserId = :user_id", array('user_id' => $user_id));
             printalert("success", "AMS Admin account <em>" . htmlentities($_POST["amsAdminUsername"]) . "</em> created");
         } catch (PDOException $e) {
             printalert("danger", "Failed to create AMS Admin account");
             $continue = false;
         }
     }
 }
 if ($roleDomain) {
     // TODO: Register the domain with the nel database etc
 }
 if ($continue && $roleService) {
     if (file_put_contents("role_service", "1")) {
         printalert("success", "Service role successfully installed");
     } else {
         printalert("danger", "Failed to flag installation success");

 /**
  * get the total number of tickets.
  */
 public static function getNrOfTickets()
 {
     $queueHandler = new Ticket_Queue_Handler();
     $queueHandler->queue->loadAllTickets();
     $query = $queueHandler->queue->getQuery();
     $params = $queueHandler->queue->getParams();
     $dbl = new DBLayer("lib");
     return $dbl->execute($query, $params)->rowCount();
 }

 /**
  * update the language value in the db.
  * update the language in the www/CMS version.
  * @param $user the username
  * @param $language the new language value.
  */
 public static function setLanguage($user, $language)
 {
     $values = array('user' => $user, 'language' => $language);
     try {
         //make connection with and put into shard db
         $dbw = new DBLayer("web");
         $dbw->execute("UPDATE ams_user SET Language = :language WHERE UId = :user ", $values);
     } catch (PDOException $e) {
         //ERROR: the web DB is offline
     }
 }

 /**
  * get list of all users that are enlisted to a support group.
  * @param $group_id the id of the group we want to query
  * @return an array of ticket_user objects that are in the support group.
  */
 public static function getAllUsersOfSupportGroup($group_id)
 {
     $dbl = new DBLayer("lib");
     $statement = $dbl->execute("SELECT * FROM `in_support_group` INNER JOIN `ticket_user` ON ticket_user.TUserId = in_support_group.User WHERE in_support_group.Group=:id", array('id' => $group_id));
     $rows = $statement->fetchAll();
     $result = array();
     foreach ($rows as $row) {
         $userInstance = new Ticket_User();
         $userInstance->setTUserId($row['TUserId']);
         $userInstance->setPermission($row['Permission']);
         $userInstance->setExternId($row['ExternId']);
         $result[] = $userInstance;
     }
     return $result;
 }

 /**
  * return all log entries related to a ticket.
  * @param $ticket_id the id of the ticket of which we want all related log entries returned.
  * @return an array of ticket_log objects, here the author is an integer.
  * @todo only use one of the 2 comparable functions in the future and make the other depricated.
  */
 public static function getAllLogs($ticket_id)
 {
     $dbl = new DBLayer("lib");
     $statement = $dbl->execute("SELECT * FROM ticket_log INNER JOIN ticket_user ON ticket_log.Author = ticket_user.TUserId and ticket_log.Ticket=:id", array('id' => $ticket_id));
     $row = $statement->fetchAll();
     $result = array();
     foreach ($row as $log) {
         $instance = new self();
         $instance->set($log);
         $result[] = $instance;
     }
     return $result;
 }

/**
* This function is beign used to change the users personal info.
* It will first check if the user who executed this function is the person of whom the information is or if it's a mod/admin. If this is not the case the page will be redirected to an error page.
* afterwards the current info will be loaded, which will be used to determine what to update. After updating the information, the settings template will be reloaded. Errors made by invalid data will be shown
* also after reloading the template.
* @author Daan Janssens, mentored by Matthew Lagoe
*/
function change_info()
{
    try {
        //if logged in
        if (WebUsers::isLoggedIn()) {
            if (isset($_POST['target_id'])) {
                // check if the user who executed this function is the person of whom the information is or if it's a mod/admin.
                if ($_POST['target_id'] == $_SESSION['id'] || Ticket_User::isMod(unserialize($_SESSION['ticket_user']))) {
                    if ($_POST['target_id'] == $_SESSION['id']) {
                        //if the info is of the executing user himself
                        $target_username = $_SESSION['user'];
                    } else {
                        //if the info is from someone else.
                        $webUser = new WebUsers($_POST['target_id']);
                        $target_username = $webUser->getUsername();
                    }
                    $webUser = new WebUsers($_POST['target_id']);
                    //use current info to check for changes
                    $current_info = $webUser->getInfo();
                    $current_info['FirstName'] = filter_var($current_info['FirstName'], FILTER_SANITIZE_STRING);
                    $current_info['LastName'] = filter_var($current_info['LastName'], FILTER_SANITIZE_STRING);
                    $current_info['Country'] = filter_var($current_info['Country'], FILTER_SANITIZE_STRING);
                    $current_info['Gender'] = filter_var($current_info['Gender'], FILTER_SANITIZE_NUMBER_INT);
                    $updated = false;
                    $values = array();
                    $values['user'] = $target_username;
                    //make the query that will update the data.
                    $query = "UPDATE ams_user SET ";
                    if ($_POST['FirstName'] != "" && $_POST['FirstName'] != $current_info['FirstName']) {
                        $query = $query . "FirstName = :fName ";
                        $updated = true;
                        $values['fName'] = filter_var($_POST['FirstName'], FILTER_SANITIZE_STRING);
                    }
                    if ($_POST['LastName'] != "" && $_POST['LastName'] != $current_info['LastName']) {
                        if ($updated) {
                            $query = $query . ", LastName = :lName ";
                        } else {
                            $query = $query . "LastName = :lName ";
                        }
                        $updated = true;
                        $values['lName'] = filter_var($_POST['LastName'], FILTER_SANITIZE_STRING);
                    }
                    if ($_POST['Country'] != "AA" && $_POST['Country'] != $current_info['Country']) {
                        if ($updated) {
                            $query = $query . ", Country = :country ";
                        } else {
                            $query = $query . "Country = :country ";
                        }
                        $updated = true;
                        $values['country'] = filter_var($_POST['Country'], FILTER_SANITIZE_STRING);
                    }
                    if ($_POST['Gender'] != $current_info['Gender']) {
                        if ($updated) {
                            $query = $query . ", Gender = :gender ";
                        } else {
                            $query = $query . "Gender = :gender ";
                        }
                        $updated = true;
                        $values['gender'] = filter_var($_POST['Gender'], FILTER_SANITIZE_NUMBER_INT);
                    }
                    //finish the query!
                    $query = $query . "WHERE Login = :user";
                    //if some field is update then:
                    if ($updated) {
                        //execute the query in the web DB.
                        $dbw = new DBLayer("web");
                        $dbw->execute($query, $values);
                    }
                    //reload the settings inc function before recalling the settings template.
                    global $SITEBASE;
                    require_once $SITEBASE . '/inc/settings.php';
                    $result = settings();
                    if ($updated) {
                        $result['info_updated'] = "OK";
                    }
                    $result['permission'] = unserialize($_SESSION['ticket_user'])->getPermission();
                    $result['username'] = $_SESSION['user'];
                    $result['no_visible_elements'] = 'FALSE';
                    $result['target_id'] = $_POST['target_id'];
                    global $INGAME_WEBPATH;
                    $result['ingame_webpath'] = $INGAME_WEBPATH;
                    helpers::loadtemplate('settings', $result);
                    throw new SystemExit();
                } else {
                    //ERROR: permission denied!
                    $_SESSION['error_code'] = "403";
                    header("Cache-Control: max-age=1");
                    header("Location: index.php?page=error");
                    throw new SystemExit();
                }
            } else {
                //ERROR: The form was not filled in correclty
                header("Cache-Control: max-age=1");
//.........这里部分代码省略.........