PHP PDOConnection::prepare方法代码示例

function update_product($prodInfo)
{
    if (!isset($prodInfo['id'])) {
        throw new Exception("Product id required.");
    }
    $dbh = new PDOConnection();
    $query = "SELECT id,code,description,price,active,last_updated FROM products WHERE id = :id";
    $sth = $dbh->prepare($query);
    $id = $prodInfo['id'];
    $sth->bindParam(':id', $id, PDO::PARAM_INT);
    if (!$sth->execute()) {
        throw new Exception($sth->errorInfo()[2]);
    }
    if (!($oldValues = $sth->fetch())) {
        throw new Exception("Product id: '" . $id . "' not found!");
    }
    $query = "UPDATE products \n        SET code = :code, \n            description = :description, \n            price = :price, \n            class = :class, \n            active = :active \n        WHERE id = :id";
    $sth = $dbh->prepare($query);
    $code = isset($prodInfo['code']) ? $prodInfo['code'] : $oldValues['code'];
    $description = isset($prodInfo['description']) ? $prodInfo['description'] : $oldValues['description'];
    $price = isset($prodInfo['price']) ? $prodInfo['price'] : $oldValues['price'];
    $class = isset($prodInfo['class']) ? $prodInfo['class'] : $oldValues['class'];
    $active = isset($prodInfo['active']) ? $prodInfo['active'] : $oldValues['active'];
    $sth->bindParam(':id', $id, PDO::PARAM_INT);
    $sth->bindParam(':code', $code);
    $sth->bindParam(':description', $description);
    $sth->bindParam(':price', $price);
    $sth->bindParam(':class', $class, PDO::PARAM_INT);
    $sth->bindParam(':active', $active, PDO::PARAM_INT);
    $sth->execute();
    return true;
}

function add_product_class($info)
{
    $dbh = new PDOConnection();
    $product_id = isset($info['product_id']) ? $info['product_id'] : '';
    $unit_id = isset($info['unit_id']) ? $info['unit_id'] : '';
    $description = isset($info['description']) ? $info['description'] : '';
    if (!$product_id) {
        $product_code = isset($info['product_code']) ? $info['product_code'] : '';
        if (!$product_code) {
            throw new Exception("Product id or code required");
        }
        $query = "SELECT id FROM products WHERE code = :code";
        $sth = $dbh->prepare($query);
        $sth->bindParam(':code', $product_code);
        if (!$sth->execute()) {
            throw new Exception($sth->errorInfo()[2]);
        }
        $product_id = $sth->fetchColumn();
    }
    if (!$unit_id) {
        $unit_code = isset($info['product_code']) ? $info['unit_code'] : '';
        if (!$unit_code) {
            throw new Exception("Unit id or code required");
        }
        $query = "SELECT id FROM units WHERE code = :code";
        $sth = $dbh->prepare($query);
        $sth->bindParam(':code', $unit_code);
        if (!$sth->execute()) {
            throw new Exception($sth->errorInfo[2]);
        }
        $unit_id = $sth->fetchColumn();
    }
    $query = "SELECT id FROM product_unit WHERE product_id = :pid AND unit_id = :uid";
    $sth = $dbh->prepare($query);
    $sth->bindParam(':pid', $product_id, PDO::PARAM_INT);
    $sth->bindParam(':uid', $unit_id, PDO::PARAM_INT);
    $sth->execute();
    if ($sth->rowCount() > 0) {
        throw new Exception("Product/unit entry already exists.");
    }
    $query = "INSERT INTO product_unit(product_id,unit_id,description) VALUES(:pid,:uid,desc)";
    $sth = $dbh->prepare($query);
    $sth->bindParam(':pid', $product_id, PDO::PARAM_INT);
    $sth->bindParam(':uid', $unit_id, PDO::PARAM_INT);
    $sth->bindParam(':description', $description, PDO::PARAM_STR);
    if (!$sth->execute()) {
        throw new Exception($sth->errorInfo()[2]);
    }
    return true;
}

function get_addresses($values = NULL)
{
    if (!(isset($values['user_id']) || isset($values['customer_id']))) {
        throw new Exception("Must provide user_id or customer_id");
    }
    $query = "SELECT a.id address_id, a.last_updated, a.name, address1, address2, city, state, zipcode, type FROM addresses a \n            LEFT JOIN user_addresses ua ON a.id = ua.address_id \n            LEFT JOIN customer_addresses ca ON a.id = ca.address_id \n        WHERE (ua.user_id = :user_id or ca.customer_id = :customer_id) ";
    $execArray['user_id'] = isset($values['user_id']) ? $values['user_id'] : -1;
    $execArray['customer_id'] = isset($values['customer_id']) ? $values['customer_id'] : -1;
    if (isset($values['address_id'])) {
        $query .= " AND a.id = :address_id ";
        $execArray['address_id'] = (int) $values['address_id'];
    }
    if (isset($values['type'])) {
        $query .= " AND type = :type ";
        $execArray['type'] = (int) $values['type'];
    }
    $dbh = new PDOConnection();
    $sth = $dbh->prepare($query);
    if (!$sth->execute($execArray)) {
        throw new Exception($sth->errorInfo()[2]);
    }
    $addressArray = array();
    foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $addressArray[] = $row;
    }
    return $addressArray;
}

function update_address($addressInfo)
{
    if (!(isset($addressInfo['address_id']) && (isset($addressInfo['customer_id']) || isset($addressInfo['user_id'])))) {
        throw new Exception("ERROR: address_id and customer_id or user_id required");
    }
    $oldInfo = get_addresses($addressInfo)[0];
    //takes customer/user id and address id
    if (empty($oldInfo)) {
        throw new Exception("Could not find address id for customer or user.");
    }
    $addressInfo = array_replace($oldInfo, $addressInfo);
    $query = "UPDATE addresses SET name = :name, address1 = :address1, address2 = :address2, city = :city, state = :state, zipcode = :zipcode, type = :type WHERE id = :address_id";
    $dbh = new PDOConnection();
    $sth = $dbh->prepare($query);
    $sth->bindParam(':name', $addressInfo['name']);
    $sth->bindParam(':address1', $addressInfo['address1']);
    $sth->bindParam(':address2', $addressInfo['address2']);
    $sth->bindParam(':city', $addressInfo['city']);
    $sth->bindParam(':state', $addressInfo['state']);
    $sth->bindParam(':zipcode', $addressInfo['zipcode']);
    $sth->bindParam(':type', $addressInfo['type'], PDO::PARAM_INT);
    $sth->bindParam(':address_id', $addressInfo['address_id'], PDO::PARAM_INT);
    if (!$sth->execute()) {
        throw new Exception("ERROR: could not update address - " . $sth->errorInfo()[2]);
    }
    return $addressInfo;
}

function update_inventory($inventoryInfo)
{
    if (!isset($inventoryInfo['inventory'])) {
        throw new Exception('Must provide \'inventory\'');
    }
    $dbh = new PDOConnection();
    $query = "INSERT INTO inventory(\n                product_id, unit_id, quantity\n            )\n            VALUES(\n                :product_id, :unit_id, :quantity\n            )\n            ON DUPLICATE KEY UPDATE\n                quantity = :quantity";
    $product_id = -1;
    $unit_id = -1;
    $qantity = -1;
    $response = '';
    $sth = $dbh->prepare($query);
    $sth->bindParam(':product_id', $product_id, PDO::PARAM_INT);
    $sth->bindParam(':unit_id', $unit_id, PDO::PARAM_INT);
    $sth->bindParam(':quantity', $quantity);
    foreach ($inventoryInfo['inventory'] as $inventory) {
        $product_id = $inventory['product_id'];
        $unit_id = $inventory['unit_id'];
        $quantity = $inventory['quantity_id'];
        if (!$sth->execute()) {
            throw new Exception($sth->errorInfo()[2]);
        }
    }
    return true;
}

function update_unit($unitInfo)
{
    if (!isset($unitInfo['id'])) {
        throw new Exception("Product id required.");
    }
    $id = $unitInfo['id'];
    $dbh = new PDOConnection();
    $oldValues = get_units(array('id' => $id))[0];
    //returns array of units
    if (empty($oldValues)) {
        throw new Exception("Product id: '" . $id . "' not found!");
    }
    $query = "UPDATE units \n        SET code = :code, \n            description = :description, \n            active = :active\n        WHERE id = :id";
    $sth = $dbh->prepare($query);
    $code = isset($unitInfo['code']) ? $unitInfo['code'] : $oldValues['code'];
    $description = isset($unitInfo['description']) ? $unitInfo['description'] : $oldValues['description'];
    $active = isset($unitInfo['active']) ? $unitInfo['active'] : $oldValues['active'];
    $sth->bindParam(':id', $id, PDO::PARAM_INT);
    $sth->bindParam(':code', $code);
    $sth->bindParam(':description', $description);
    $sth->bindParam(':active', $active, PDO::PARAM_INT);
    if (!$sth->execute()) {
        throw new Exception($sth->errorInfo()[2]);
    }
    return true;
}

function add_product_class($classArray)
{
    $dbh = new PDOConnection();
    $code = $classArray['code'];
    $description = $classArray['description'];
    $query = "SELECT code FROM product_classes where code = :code";
    $sth = $dbh->prepare($query);
    $sth->bindParam(':code', $code, PDO::PARAM_STR);
    $sth->execute();
    if ($sth->rowCount() > 0) {
        throw new Exception("Class code exists");
    }
    $query = "INSERT INTO product_classes(description,code) VALUES(:description,:code)";
    $sth = $dbh->prepare($query);
    $sth->bindParam(':code', $code, PDO::PARAM_STR);
    $sth->bindParam(':description', $description, PDO::PARAM_STR);
    return $sth->execute();
}

function verifyUserIsAdmin($username)
{
    $dbh = new PDOConnection();
    $query = 'SELECT user_id FROM admins JOIN users ON users.id = user_id WHERE username = :username';
    $sth = $dbh->prepare($query);
    $sth->bindParam(':username', $username, PDO::PARAM_STR);
    if (!$sth->execute()) {
        throw new Exception($sth->errorInfo()[2]);
    }
    $result = $sth->fetchAll();
    $retval = !empty($result);
    return $retval;
}

function get_product_classes($classFilters = NULL)
{
    $dbh = new PDOConnection();
    $query = "SELECT * FROM product_classes ";
    $classArray = array();
    $sth = $dbh->prepare($query);
    $sth->execute();
    $result = $sth->fetchAll(PDO::FETCH_ASSOC);
    foreach ($result as $row) {
        $classArray[] = $row;
    }
    return $classArray;
}

function update_user($user)
{
    if (!(isset($user['email']) || isset($user['username']) || isset($user['password']))) {
        throw new Exception("Nothing changed!");
    }
    $dbh = new PDOConnection();
    $query = "SELECT id,username,email,password,token,last_updated FROM users WHERE id = :id";
    $sth = $dbh->prepare($query);
    $id = $user['user_id'];
    $sth->bindParam(':id', $id, PDO::PARAM_INT);
    if (!$sth->execute()) {
        throw new Exception($sth->errorInfo()[2]);
    }
    if (!($oldValues = $sth->fetch())) {
        throw new Exception("User id: '" . $id . "' not found!");
    }
    // if you change username you must provide password
    if (isset($user['username']) && !isset($user['password'])) {
        throw new Exception("Must provide password to change username.");
    }
    $email = isset($user['email']) ? $user['email'] : $oldValues['email'];
    $username = isset($user['username']) ? $user['username'] : $oldValues['username'];
    $password = isset($user['password']) ? hash_password($user['password'], $username) : $oldValues['password'];
    $token = $oldValues['token'];
    $query = "UPDATE users \n        SET username = :username, email = :email, password = :password \n        WHERE id = :id";
    $sth = $dbh->prepare($query);
    $sth->bindParam(':id', $id, PDO::PARAM_INT);
    $sth->bindParam(':username', $username);
    $sth->bindParam(':email', $email);
    $sth->bindParam(':password', $password);
    if (!$sth->execute()) {
        throw new Exception($sth->errorInfo()[2]);
    }
    if (isset($user['password'])) {
        $token = GenerateToken($username, $user['password']);
        StoreToken($username, $token);
    }
    return array('id' => $id, 'email' => $email, 'username' => $username, 'token' => $token);
}

function update_product_class($classArray)
{
    $dbh = new PDOConnection();
    $query = "SELECT id,code,description,last_updated FROM product_classes WHERE id = :id";
    $sth = $dbh->prepare($query);
    $id = $classArray['id'];
    $sth->bindParam(':id', $id, PDO::PARAM_INT);
    if (!$sth->execute()) {
        throw new Exception($sth->errorInfo()[2]);
    }
    if (!($oldValues = $sth->fetch())) {
        throw new Exception("Class id: '" . $id . "' not found!");
    }
    $query = "UPDATE product_classes SET code = :code, description = :description WHERE id = :id";
    $sth = $dbh->prepare($query);
    $code = isset($classArray['code']) ? $classArray['code'] : $oldValues['code'];
    $description = isset($classArray['description']) ? $classArray['description'] : $oldValues['description'];
    $sth->bindParam(':id', $id, PDO::PARAM_INT);
    $sth->bindParam(':code', $code);
    $sth->bindParam(':description', $description);
    $sth->execute();
    return true;
}

function add_unit($unitArray)
{
    $dbh = new PDOConnection();
    $code = $unitArray['code'];
    $description = $unitArray['description'];
    $query = "SELECT id,code FROM units WHERE code = :code";
    $sth = $dbh->prepare($query);
    $sth->bindParam(':code', $code, PDO::PARAM_STR);
    if (!$sth->execute()) {
        throw new Exception($sth->errorInfo()[2]);
    }
    if ($sth->rowCount() > 0) {
        throw new Exception("Unit code exists");
    }
    $query = "INSERT INTO units(code, description) VALUES(:code, :description)";
    $sth = $dbh->prepare($query);
    $sth->bindParam(':code', $code, PDO::PARAM_STR);
    $sth->bindParam(':description', $description, PDO::PARAM_STR);
    if (!$sth->execute()) {
        throw new Exception($sth->errorInfo()[2]);
    }
    return true;
}

function get_units($filters = NULL)
{
    $dbh = new PDOConnection();
    $query = "SELECT id, code, description, active, last_updated FROM units ";
    $query .= GetOptionalParams($filters);
    $units = array();
    $sth = $dbh->prepare($query);
    if (isset($filters['id'])) {
        $sth->bindParam(':id', $filters['id'], PDO::PARAM_INT);
    } elseif (isset($filters['code'])) {
        $sth->bindParam(':code', $filters['code']);
    }
    $sth->execute();
    $result = $sth->fetchAll(PDO::FETCH_ASSOC);
    foreach ($result as $row) {
        $units[] = $row;
    }
    return $units;
}

function get_customers($values = NULL)
{
    $dbh = new PDOConnection();
    $query = "SELECT id, code, name, active, last_updated FROM customers ";
    if (isset($values['id'])) {
        $optional[] = "id = :id ";
    }
    if (isset($values['code'])) {
        $optional[] = "code = :code ";
    }
    if (isset($values['active'])) {
        $optional[] = "active = :active ";
    }
    if (!empty($optional)) {
        $query .= ' WHERE ';
        $countOpt = count($optional);
        for ($i = 0; $i < $countOpt; ++$i) {
            $query .= ($i > 0 ? ' AND ' : ' ') . $optional[$i];
        }
    }
    $sth = $dbh->prepare($query);
    if (isset($values['id'])) {
        $sth->bindParam(':id', $values['id'], PDO::PARAM_INT);
    }
    if (isset($values['code'])) {
        $sth->bindParam(':code', $values['code'], PDO::PARAM_STR);
    }
    if (isset($values['active'])) {
        $sth->bindParam(':active', $values['active'], PDO::PARAM_INT);
    }
    if (!$sth->execute()) {
        throw new Exception($sth->errorInfo()[2]);
    }
    $customerArray = array();
    foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $customerArray[] = $row;
    }
    return array('customers' => $customerArray);
}

function get_cart($cartInfo)
{
    $dbh = new PDOConnection();
    $query = "SELECT u.id user_id, u.username, u.email, h.address_id, delivery_date, delivery_method, shipping_type, comments, shipping_comments, h.last_updated \n        FROM cart_headers h \n        LEFT JOIN users u ON u.id = h.user_id \n        WHERE user_id = :user_id ";
    $user_id = $cartInfo['user_id'];
    $sth = $dbh->prepare($query);
    $sth->bindParam(':user_id', $user_id);
    if (!$sth->execute()) {
        throw new Exception('ERROR in get_cart(): ' . $sth->errorInfo()[2]);
    }
    if ($sth->rowCount() <= 0) {
        throw new Exception('No cart found for user_id: ' . $user_id);
    }
    $cartArray = $sth->fetch(PDO::FETCH_ASSOC);
    $details = get_cart_details($dbh, $user_id);
    //calculate total price
    $cartArray['total_price'] = array_sum(array_map(function ($row) {
        return $row['line_price'];
    }, $details));
    //uncomment if you want details passed in the main get_cart function
    $cartArray['lines'] = $details;
    return $cartArray;
}