<?php
    require_once('dbconnect.php');
    
    function get_product_list() {
        try {
            global $pdo;
            $sqlQuery = 'SELECT * FROM crashcourse.products ORDER BY prod_name';
            $statement = $pdo->prepare($sqlQuery);
            $statement->execute();
            $results = $statement->fetchAll();
            $statement->closeCursor();
            return $results;
        } catch (Exception $e) {
            $output = 'Could not retrieve data from server: ';
            $errorException = $e->getMessage();
            include('View/error.php');
            exit();
        }
    }
    
    function countProductsByVendor($txtVendID) {
        try {
            global $pdo;
            $sqlQuery = 'SELECT COUNT(*) FROM products WHERE vend_id = ' . $txtVendID; // Temporary Patch, cannot figure out what wrong with bind
            $stmt = $pdo->query($sqlQuery);
            //$stmt->bindValue(':vend_id', $txtVendID);
            $results = $stmt->fetchColumn();
            $stmt->closeCursor();
            return $results;
        } catch (Exception $e) {
            $output = 'Could not retrieve list of products with this vendor from server: ' . $txtVendID;
            $errorException = $e->getMessage();
            include('View/error.php');
            exit();
        }
    }
    
    function countProducts() {
        try {
            global $pdo;
            $sqlQuery = 'SELECT COUNT(*) FROM crashcourse.products';
            $statement = $pdo->prepare($sqlQuery);
            $statement->execute();
            $results = $statement->fetchColumn();
            $statement->closeCursor();
            return $results;
        } catch (Exception $e) {
            $output = 'Could get product count from server: ';
            $errorException = $e->getMessage();
            include('View/error.php');
            exit();
        }
    }
    
    function get_product($product_id) {
        try {
            global $pdo;
            $sqlQuery = 'SELECT * FROM products WHERE prod_id = :txtProdID';
            $statement = $pdo->prepare($sqlQuery);
            $statement->bindValue(':txtProdID', $product_id);
            $statement->execute();
            $results = $statement->fetch();
            $statement->closeCursor();
            return $results;
        } catch (Exception $e) {
            $output = 'Could not retrieve data from server: ';
            $errorException = $e->getMessage();
            include('View/error.php');
            exit();
        }
    }
    
    function insert_product($prod_id, $vend_id, $prod_name, $prod_price, $prod_desc) {
        try{
            global $pdo;
            $sqlQuery = "INSERT INTO products (prod_id, vend_id, prod_name, prod_price, prod_desc) 
            VALUES(:prodID, :prodVendID, :prodName, :prodPrice, :prodDesc)";
            $statement = $pdo->prepare($sqlQuery);
            $statement->bindValue(':prodID', strtoupper(addslashes($prod_id)));
            $statement->bindValue(':prodVendID', addslashes($vend_id));
            $statement->bindValue(':prodName', strtoupper(addslashes($prod_name)));
            $statement->bindValue(':prodPrice', addslashes($prod_price));
            $statement->bindValue(':prodDesc', addslashes($prod_desc));
            $statement->execute();
            $statement->closeCursor();
            header('Location: .');
            exit();
        } catch (Exception $e) {
            $output = 'Could not add product to server: ';
            $errorException = $e->getMessage();
            include('View/error.php');
            exit();
        }
    }
    
    function update_product($prod_id, $vend_id, $prod_name, $prod_price, $prod_desc) {
        try {
            global $pdo;
            $sqlQuery = 'UPDATE products SET vend_id = :prodVendID, prod_name = :prodName, '
                    . 'prod_price = :prodPrice, prod_desc = :prodDesc WHERE prod_id = :prodID';
            $stmt = $pdo->prepare($sqlQuery);
            $stmt->bindValue(':prodID', strtoupper(addslashes($prod_id)));
            $stmt->bindValue(':prodVendID', addslashes($vend_id));
            $stmt->bindValue(':prodName', strtoupper(addslashes($prod_name)));
            $stmt->bindValue(':prodPrice', addslashes($prod_price));
            $stmt->bindValue(':prodDesc', addslashes($prod_desc));
            $stmt->execute();
            $stmt->closeCursor();
            header('Location: .');
            exit();
        } catch (Exception $e) {
            $output = 'Could not update database: ';
            $errorException = $e->getMessage();
            include('error.php');
            exit();
        }
    }
    
    function delete_product($product_id) {
        try {
            global $pdo;
            $sqlQuery = 'DELETE FROM products WHERE prod_id = :txtProdID';
            $stmt = $pdo->prepare($sqlQuery);
            $stmt->bindValue(':txtProdID', $product_id);
            $stmt->execute();
            $stmt->closeCursor();
            header('Location: .');
            exit();
        } catch (Exception $e) {
            $output = 'Could not delete product from server: ';
            $errorException = $e->getMessage();
            include('error.php');
            exit();
        } 
    }
    
    function product_filter($sqlQuery) {
        try {
            global $pdo;
            $statement = $pdo->prepare($sqlQuery);
            $statement->execute();
            $results = $statement->fetchAll();
            $statement->closeCursor();
            return $results;
        } catch (Exception $e) {
            $output = 'Could not retrieve data from server: ';
            $errorException = $e->getMessage();
            include('View/error.php');
            exit();
        }
    }
?>