<!-- Collection of code snippets by Arne Vajhøj -->
<!-- (from articles on eksperten.dk / vajhoej.dk written sometime between 2004 and now) -->
<?php
namespace Order\Backend;

use PDO;

require 'dbconfig.php';

class Persister {
    private $con;
    public function __construct() {
        $this->con = new PDO(CONSTR, UN, PW);
        $this->con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    }
    function GetNextCustomerId() {
        $stmt = $this->con->prepare('SELECT MAX(id)+1 AS nextid FROM customers');
        $stmt->execute(array());
        if($row = $stmt->fetch()) {
            return $row['nextid'];
        } else {
            return 1;
        }
    }
    function GetNextOrderId() {
        $stmt = $this->con->prepare('SELECT MAX(id)+1 AS nextid FROM orders');
        $stmt->execute(array());
        if($row = $stmt->fetch()) {
            return $row['nextid'];
        } else {
            return 1;
        }
    }
    function GetNextOrderLineId() {
        $stmt = $this->con->prepare('SELECT MAX(id)+1 AS nextid FROM orderlines');
        $stmt->execute(array());
        if($row = $stmt->fetch()) {
            return $row['nextid'];
        } else {
            return 1;
        }
    }
    function GetCustomers() {
        $result = array();
        $stmt = $this->con->prepare('SELECT id,name,address,phone,email FROM customers');
        $stmt->execute(array());
        while($row = $stmt->fetch()) {
            $result[] = new Customer($row['id'], $row['name'], $row['address'], $row['phone'], $row['email']);
        }
        return $result;
    }
    function GetProducts() {
        $result = array();
        $stmt = $this->con->prepare('SELECT id,name,description,price FROM products');
        $stmt->execute(array());
        while($row = $stmt->fetch()) {
            $result[] = new Product($row['id'], $row['name'], $row['description'], $row['price']);
        }
        return $result;
    }
    function SaveCustomer($customer) {
        $stmt = $this->con->prepare('INSERT INTO customers VALUES(:id,:name,:address,:phone,:email)');
        $stmt->execute(array(':id' => $customer->id, ':name' => $customer->name, ':address' => $customer->address, ':phone' => $customer->phone, ':email' => $customer->email));
    }
    function SaveOrder($order) {
        $ostmt = $this->con->prepare('INSERT INTO orders VALUES(:id,:customerid,:orderdate,:status)');
        $ostmt->execute(array(':id' => $order->id, ':customerid' => $order->customer->id, ':orderdate' => $order->orderdate, ':status' => $order->status));
        $olstmt = $this->con->prepare('INSERT INTO orderlines VALUES(:id,:orderid,:productid,:quantity)');
        foreach($order->lines as $ol) {
            $olstmt->execute(array(':id' => $ol->id,':orderid' => $order->id, ':productid' => $ol->product->id, ':quantity' => $ol->quantity));
        }
    }
    function LookupOrder($orderid) {
        $stmt = $this->con->prepare('SELECT orders.id AS oid,orders.orderdate AS odate,orders.status AS ostat,' .
                                    '       customers.id AS cid,customers.name AS cname,customers.address AS caddr,customers.phone AS cphone,customers.email AS cemail,' .
                                    '       orderlines.id AS olid,orderlines.quantity AS olq,' .
                                    '       products.id AS pid,products.name AS pname,products.description AS pdescr,products.price AS pprice ' .
                                    'FROM ((orders JOIN customers ON orders.customerid = customers.id) ' .
                                    '     JOIN orderlines ON orders.id = orderlines.orderid) ' .
                                    '     JOIN products ON orderlines.productid = products.id ' .
                                    'WHERE orders.id = :orderid');
        $stmt->execute(array(':orderid' => $orderid));
        $first = true;
        $order = null;
        while($row = $stmt->fetch()) {
            if($first) {
                $customer = new Customer($row['cid'], $row['cname'], $row['caddr'], $row['cphone'], $row['cemail']);
                $order = new Order($row['oid'], $customer, $row['odate'], $row['ostat']);
                $first = false;
            }
            $product = new Product($row['pid'], $row['pname'], $row['pdescr'], $row['pprice']); 
            $orderline = new OrderLine($row['olid'], $product, $row['olq']);
            $order->lines[] = $orderline;
        }
        return $order;
    }
    function GetSalesStats() {
        $res = array();
        $stmt = $this->con->prepare('SELECT customers.name AS name,COUNT(DISTINCT orders.id) AS norders,SUM(orderlines.quantity*products.price) AS totsale ' .
                                    'FROM ((customers LEFT JOIN orders ON customers.id = orders.customerid) ' .
                                    '     JOIN orderlines ON orders.id = orderlines.orderid) ' .
                                    '     JOIN products ON orderlines.productid = products.id ' .
                                    'GROUP BY customers.name');
        $stmt->execute(array());
        while($row = $stmt->fetch()) {
            $res[] = new Stats($row['name'], $row['norders'], $row['totsale']); 
        }
        return $res;
    }
}

?>