Hjælp til paging class

/**
* DataFetcher interface.
*
* Note: errors should throw exceptions.
*/
interface DataFetcher {
    /**
    * Execute query.
    * @param integer $offset row offset (0 based)
    * @param integer $numrows number of rows
    */
    public function query($offset, $numrows);
    /**
    * Get row.
    * @return array row as associative array with field name as key and field value as value (null if no row available)
    */
    public function getRow();
    /**
    * Close connection.
    */
    public function close();
}

/**
* DataFetcher class for mysqli extension.
*
* Note: parameters is not supported due to API problems.
*/
class MysqliDataFetcher implements DataFetcher {
    private $con;
    private $stmt;
    private $rs;
    private $sql;
    /**
    * Constructor.
    * @param string $host server
    * @param string $un username
    * @param string $pw password
    * @param string $db database
    * @param string $sql base SQL statement (will be modified before execution)
    * @throws Exception
    */
    public function __construct($host, $un, $pw, $db, $sql) {
        $this->con = new mysqli($host, $un, $pw, $db); 
        if(mysqli_connect_errno()) throw new Exception(mysqli_connect_error());
        $stmt = null;
        $rs = null;
        $this->sql = $sql;
    }
    public function query($offset, $numrows) {
        if($this->rs != null) $this->rs->free();   
        if($this->stmt != null) $this->stmt->close(); 
        if($this->stmt = $this->con->prepare("$this->sql LIMIT $offset,$numrows")) {
            $this->stmt->execute();
            $this->rs = $this->stmt->get_result();
        } else {
            throw new Exception($this->con->error);
        }
    }
    public function getRow() {
        return $this->rs->fetch_array(MYSQLI_ASSOC);   
    }
    public function close() {
        if($this->rs != null) $this->rs->free();   
        if($this->stmt != null) $this->stmt->close(); 
        $this->con->close();   
    }
}

/**
* DataFetcher class for PDO.
*/
abstract class PDODataFetcher implements DataFetcher {
    private $con;
    private $stmt;
    private $sql;
    private $params;
    /**
    * Constructor.
    * @param string $constr connection string
    * @param string $un username
    * @param string $pw password
    * @param string $sql base SQL statement (will be modified before execution)
    * @param array $params parameters for SQL statement
    * @throws Exception
    */
    public function __construct($constr, $un, $pw, $sql, $params) {
        $this->con = new PDO($constr, $un, $pw);
        $this->con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = null;
        $rs = null;
        $this->sql = $sql;
        $this->params = $params;
    }
    public function query($offset, $numrows) {
        if($this->stmt != null) $this->stmt->closeCursor();
        $this->stmt = $this->con->prepare($this->convertSQL($this->sql, $offset, $numrows));
        $this->stmt->execute($this->params);
    }
    public function getRow() {
        return $this->stmt->fetch(PDO::FETCH_ASSOC);
    }
    public function close() {
        if($this->stmt != null) $this->stmt->closeCursor();
    }
    /**
    * Convert SQL for pagination.
    * @param string $sql base SQL statement
    * @param integer $offset row offset (0 based)
    * @param integer $numrows number of rows
    * @return string modified SQL statement
    */
    protected abstract function convertSQL($sql, $offset, $numrows);
}

/**
* DataFetcher class for PDO with MySQL.
*/
class PDOmysqlDataFetcher extends PDODataFetcher {
    /**
    * Constructor.
    * @param string $host server
    * @param string $un username
    * @param string $pw password
    * @param string $db database
    * @param string $sql base SQL statement (will be modified before execution)
    * @param array $params parameters for SQL statement
    * @throws Exception
    */
    public function __construct($host, $un, $pw, $db, $sql, $params) {
        parent::__construct("mysql:host=$host;dbname=$db", $un, $pw, $sql, $params);
    }
    protected function convertSQL($sql, $offset, $numrows) {
        return "$sql LIMIT $offset,$numrows";
    }
}

/**
* DataFetcher class for PDO with SQLServer.
*/
class PDOsqlsrvDataFetcher extends PDODataFetcher {
    /**
    * Constructor.
    * @param string $host server
    * @param string $un username
    * @param string $pw password
    * @param string $db database
    * @param string $sql base SQL statement (will be modified before execution)
    * @param array $params parameters for SQL statement
    * @throws Exception
    */
    public function __construct($host, $un, $pw, $db, $sql, $params) {
        parent::__construct("sqlsrv:server=$host;database=$db", $un, $pw, $sql, $params);
    }
    protected function convertSQL($sql, $offset, $numrows) {
        return "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,x.* FROM ($sql) x) y WHERE rownum BETWEEN ($offset+1) AND ($offset+$numrows)";
    }
}

/**
* Data paginator class.
*/
class DataPaginator {
    private $df;
    private $pgsiz;
    private $first;
    private $last;
    private $data;
    private $fnd1more;
    private function update() {
        $this->df->query($this->first - 1, $this->last - $this->first + 2);
        $this->data = array();
        while((count($this->data) < $this->pgsiz) && ($row = $this->df->getRow())) {
            $this->data[] = $row;
        }
        $extrarow = $this->df->getRow();
        $this->fnd1more = is_array($extrarow);
    }
    /**
    * Constructor.
    * @param object $df data fetcher
    * @param integer $pgsiz page size
    * @param integer $start start row number (1 based)
    */
    public function __construct($df, $pgsiz, $start = 1) {
        $this->df = $df;
        $this->pgsiz = $pgsiz;
        $this->first = $start;
        $this->last = $this->first + $this->pgsiz - 1;
        $this->update();
    }
    /**
    * Get data.
    * @return array indexed array where each element is a row and row is an associative array where each element is a field with field name as index and field value as value
    */
    public function getData() {
        return $this->data;
    }
    /**
    * Get first row of current page.
    * @return integer row number (1 based)
    */
    public function getFirst() {
        return $this->first;
    }
    /**
    * Get last row of current page.
    * @return integer row number (1 based)
    */
    public function getLast() {
        return min($this->last, $this->first + count($this->data) - 1);
    }
    /**
    * Get page size.
    * @return integer page size
    */
    public function getPageSize() {
        return $this->pgsiz;
    }
    /**
    * Does previous page exist.
    * @return boolean
    */
    public function hasPrev() {
        return $this->first > 1;   
    }
    /**
    * Does next page exist.
    * @return boolean
    */
    public function hasNext() {
        return $this->fnd1more;
    }
    /**
    * Go to next page.
    */
    public function next() {
        $this->first += $this->pgsiz; 
        $this->last = $this->first + $this->pgsiz - 1;
        $this->update();   
    }
    /**
    * Go to previous page.
    */
    public function prev() {
        $this->first -= $this->pgsiz;
        $this->last = $this->first + $this->pgsiz - 1;
        $this->update();
    }
}

/**
* Visual paginator class.
*/
abstract class VisualPaginator {
    private $dp;
    private $url;
    /**
    * Constructor.
    * @param object $dp data paginator
    * @param string $url URL to use for previous/next links (usually same URL as current page)
    */
    public function __construct($dp, $url) {
        $this->dp = $dp;
        $this->url = $url;
    }
    /**
    * Display paginated data.
    */
    public function display() {
        $this->displayHeader($this->dp->getFirst(), $this->dp->getLast());
        foreach($this->dp->getData() as $row) {
            $this->displayRow($row);
        }
        $this->displayFooter($this->url, $this->dp->hasPrev(), $this->dp->getFirst(), $this->dp->hasNext(), $this->dp->getLast(), $this->dp->getPageSize());
    }
    /**
    * Display header.
    * @param integer $first first row number on page (1 based)
    * @param integer $last last row number on page (1 based)
    */
    protected abstract function displayHeader($first, $last);
    /**
    * Display single row.
    * @param array $row associative array where each element is a field with field name as index and field value as value
    */
    protected abstract function displayRow($row);
    /**
    * Display footer.
    * @param string $url URL to use for previous/next links (usually same URL as current page)
    * @param boolean $hasprev does previous page exist
    * @param integer $first first row number on page (1 based)
    * @param boolean $hasnext does next page exist
    * @param integer $last last row number on page (1 based)
    * @param integer $pgsiz page size
    */
    protected abstract function displayFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz);
}

/**
* Table visual paginator class (display data in HTML table).
*/
abstract class TableVisualPaginator extends VisualPaginator {
    private $desc;
    private $colhdr;
    private function spacer($attr) {
        return (strlen($attr) > 0) ? (' ' . $attr) : '';
    }
    /**
    * Constructor.
    * @param object $dp data paginator
    * @param string $url URL to use for previous/next links (usually same URL as current page)
    * @param string $desc descriptive text used in header
    * @param array $colhdr array with column header names
    */
    public function __construct($dp, $url, $desc, $colhdr) {
        parent::__construct($dp, $url);
        $this->desc = $desc;
        $this->colhdr = $colhdr;
    }
    protected function displayHeader($first, $last) {
        echo $this->getHeader($this->desc, $first, $last);
        echo "<table" . $this->spacer($this->getTableAttr()) . ">\r\n";
        echo "<tr" . $this->spacer($this->getTrAttr()) . ">\r\n";
        foreach($this->colhdr as $ch) {
            echo "<th" . $this->spacer($this->getThAttr()) . ">$ch</th>\r\n";
        }
        echo "</tr>\r\n";
    }
    protected function displayRow($row) {
        echo "<tr" . $this->spacer($this->getTrAttr()) . ">\r\n";
        foreach($row as $f => $v) {
            echo "<td" . $this->spacer($this->getTdAttr()) . ">$v</td>\r\n";
        }
        foreach($this->getExtraCols() as $cb) {
            echo "<td" . $this->spacer($this->getTdAttr()) . ">" . call_user_func($cb, $row) . "</td>\r\n";
        }
        echo "</tr>\r\n";
    }
    protected function displayFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz) {
        echo "</table>\r\n";
        echo $this->getFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz);
    }
    /**
    * Get header string.
    * @param string $desc descriptive text
    * @param integer $first first row number on page (1 based)
    * @param integer $last last row number on page (1 based)
    * @return string header
    */
    protected abstract function getHeader($desc, $first, $last);
    /**
    * Get footer string.
    * @param string $url URL to use for previous/next links (usually same URL as current page)
    * @param boolean $hasprev does previous page exist
    * @param integer $first first row number on page (1 based)
    * @param boolean $hasnext does next page exist
    * @param integer $last last row number on page (1 based)
    * @param integer $pgsiz page size
    * @return footer
    */
    protected abstract function getFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz);
    /**
    * Get attributes on table tag.
    * @return string attributes
    */
    protected abstract function getTableAttr();
    /**
    * Get attributes on tr tag.
    * @return string attributes
    */
    protected abstract function getTrAttr();
    /**
    * Get attributes on th tag.
    * @return string attributes
    */
    protected abstract function getThAttr();
    /**
    * Get attributes on td tag.
    * @return string attributes
    */
    protected abstract function getTdAttr();
    /**
    * Get extra columns to add.
    * @return array array with column values
    */
    protected abstract function getExtraCols();
}

/**
* List visual paginator class (display data in HTML list).
*/
abstract class ListVisualPaginator extends VisualPaginator {
    private $desc;
    private function spacer($attr) {
        return (strlen($attr) > 0) ? (' ' . $attr) : '';
    }
    /**
    * Constructor.
    * @param object $dp data paginator
    * @param string $url URL to use for previous/next links (usually same URL as current page)
    * @param string $desc descriptive text used in header
    */
    public function __construct($dp, $url, $desc) {
        parent::__construct($dp, $url);
        $this->desc = $desc;
    }
    protected function displayHeader($first, $last) {
        echo $this->getHeader($this->desc, $first, $last);
        echo "<ul" . $this->spacer($this->getUlAttr()) . ">\r\n";
    }
    protected function displayRow($row) {
        echo "<li" . $this->spacer($this->getLiAttr()) . ">\r\n";
        echo $this->getBody($row);
        echo "</li>\r\n";
    }
    protected function displayFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz) {
        echo "</ul>\r\n";
        echo $this->getFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz);
    }
    /**
    * Get header string.
    * @param string $desc descriptive text
    * @param integer $first first row number on page (1 based)
    * @param integer $last last row number on page (1 based)
    * @return string header
    */
    protected abstract function getHeader($desc, $first, $last);
    /**
    * Get footer string.
    * @param string $url URL to use for previous/next links (usually same URL as current page)
    * @param boolean $hasprev does previous page exist
    * @param integer $first first row number on page (1 based)
    * @param boolean $hasnext does next page exist
    * @param integer $last last row number on page (1 based)
    * @param integer $pgsiz page size
    * @return footer
    */
    protected abstract function getFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz);
    /**
    * Get attributes on ul tag.
    * @return string attributes
    */
    protected abstract function getUlAttr();
    /**
    * Get attributes on li tag.
    * @return string attributes
    */
    protected abstract function getLiAttr();
    /**
    * Get representation of one row.
    * @param array $row associative array where each element is a field with field name as index and field value as value
    */
    protected abstract function getBody($row);
}

/**
* Standard header and footer trait.
*/
trait StandardHeaderFooter {
    /**
    * Get header string.
    * @param string $desc descriptive text
    * @param integer $first first row number on page (1 based)
    * @param integer $last last row number on page (1 based)
    * @return string header
    */
    public function getHeader($desc, $first, $last) {
        return "$desc $first - $last:\r\n";
    } 
    private function getUrl($url, $start) {
        return "$url?start=$start";
    }
    private function getLink($text, $url, $start) {
        return "<a href='" . $this->getUrl($url, $start) . "'>$text</a>";
    }
    /**
    * Get footer string.
    * @param string $url URL to use for previous/next links (usually same URL as current page)
    * @param boolean $hasprev does previous page exist
    * @param integer $first first row number on page (1 based)
    * @param boolean $hasnext does next page exist
    * @param integer $last last row number on page (1 based)
    * @param integer $pgsiz page size
    * @return footer
    */
    public function getFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz) {
        return  ($hasprev ? $this->getLink('Previous', $url, $first - $pgsiz) : '') . ' ' . ($hasnext ? $this->getLink('Next', $url, $last + 1) : '') . "\r\n";
    }
}

/**
* Simple table visual paginator class (just a plain table with no extras).
*/
class SimpleTableVisualPaginator extends TableVisualPaginator {
    use StandardHeaderFooter;
    /**
    * Constructor.
    * @param object $dp data paginator
    * @param string $url URL to use for previous/next links (usually same URL as current page)
    * @param string $desc descriptive text used in header
    * @param array $colhdr array with column header names
    */
    public function __construct($dp, $url, $desc, $colhdr) {
        parent::__construct($dp, $url, $desc, $colhdr);
    }
    protected function getTableAttr() {
        return "border='1'";   
    }
    protected function getTrAttr() {
        return '';
    }
    protected function getThAttr() {
        return '';
    }
    protected function getTdAttr() {
        return '';
    }
    protected function getExtraCols() {
        return array();
    }
}

/**
* Simple list visual paginator class (just a plain list with no extras).
*/
class SimpleListVisualPaginator extends ListVisualPaginator {
    use StandardHeaderFooter;
    public function __construct($dp, $url, $desc) {
        parent::__construct($dp, $url, $desc);
    }
    protected function getUlAttr() {
        return '';
    }
    protected function getLiAttr() {
        return '';
    }
    protected function getBody($row) {
        $res = '';
        foreach($row as $f => $v) {
            $res .= "$v\r\n";
        }
        return $res;
    }
}

define('NOFLD','');
define('NOFUNC', null);

/**
* Advanced table visual paginator class (table with classes on tags and extra columns with edit and delete links).
*/
class AdvancedTableVisualPaginator extends TableVisualPaginator {
    use StandardHeaderFooter;
    private $clz;
    private $idfld;
    private $editurl;
    private $delurl;
    private function getClassAttr($elm) {
        if(array_key_exists($elm, $this->clz)) {
            return "class='" . $this->clz[$elm] . "'"; 
        } else {
            return '';
        }
    }
    /**
    * Constructor.
    * @param object $dp data paginator
    * @param string $url URL to use for previous/next links (usually same URL as current page)
    * @param string $desc descriptive text used in header
    * @param array $colhdr array with column header names
    * @param array $clz associative array where each element is a class attribute with tag name as index and class name as value
    * @param string $idfld name of field to use as is for edit and delete
    * @param string $editurl URL to use for edit links
    * @param string $editurl URL to use for delete links
    */
    public function __construct($dp, $url, $desc, $colhdr, $clz, $idfld = NOFLD, $editurl = NOFUNC, $delurl = NOFUNC) {
        parent::__construct($dp, $url, $desc, $colhdr);
        $this->clz = $clz;
        $this->idfld = $idfld;
        $this->editurl = $editurl;
        $this->delurl = $delurl;
    }
    protected function getTableAttr() {
        $clzattr = $this->getClassAttr('table');
        if(strlen($clzattr) > 0) {
            return "border='1' $clzattr";
        } else {
            return "border='1'";
        }
    }
    protected function getTrAttr() {
        return $this->getClassAttr('tr');
    }
    protected function getThAttr() {
        return $this->getClassAttr('th');
    }
    protected function getTdAttr() {
        return $this->getClassAttr('td');
    }
    protected function getExtraCols() {
        $res = array();
        if(($this->idfld != NOFLD) && ($this->editurl != NOFUNC)) {
            $res[] = array($this, 'getEditLink');
        }
        if(($this->idfld != NOFLD) && ($this->delurl != NOFUNC)) {
            $res[] = array($this, 'getDelLink');
        }
        return $res;
    }
    protected function getEditLink($row) {
        return "<a href='$this->editurl?$this->idfld=" . $row[$this->idfld] . "'>Edit</a>";
    }
    protected function getDelLink($row) {
        return "<a href='$this->delurl?$this->idfld=" . $row[$this->idfld] . "'>Delete</a>";
    }
}

/**
* Advanced list visual paginator class (list with classes on tags and data formatted via template).
*/
class AdvancedListVisualPaginator extends ListVisualPaginator {
    use StandardHeaderFooter;
    private $clz;
    private $tmpl;
    private function getClassAttr($elm) {
        if(array_key_exists($elm, $this->clz)) {
            return "class='" . $this->clz[$elm] . "'"; 
        } else {
            return '';
        }
    }
    /**
    * Constructor.
    * @param object $dp data paginator
    * @param string $url URL to use for previous/next links (usually same URL as current page)
    * @param string $desc descriptive text used in header
    * @param array $colhdr array with column header names
    * @param array $clz associative array where each element is a class attribute with tag name as index and class name as value
    * @param strint $tmpl template for displaying a row (colon plus field name get replaced with field value)
    */
    public function __construct($dp, $url, $desc, $clz, $tmpl) {
        parent::__construct($dp, $url, $desc);
        $this->clz = $clz;
        $this->tmpl = $tmpl;
    }
    protected function getUlAttr() {
        return $this->getClassAttr('ul');
    }
    protected function getLiAttr() {
        return $this->getClassAttr('li');
    }
    protected function getBody($row) {
        $res = $this->tmpl;
        foreach($row as $f => $v) {
            $res = str_replace(':' . $f, $v, $res);
        }
        return $res;
    }
}

function test_SimpleTableVisualPaginator() {
    $df = new PDOmysqlDataFetcher('localhost', 'root', '', 'Test', 'SELECT f1,f2 FROM t1', array());
    $dp = new DataPaginator($df, 4, 5);
    $vp = new SimpleTableVisualPaginator($dp, 'list.php', 'Test data', array('Col 1', 'Col 2'));
    test_VisualPaginator($vp);
}

function test_AdvancedTableVisualPaginator() {
    $df = new PDOmysqlDataFetcher('localhost', 'root', '', 'Test', 'SELECT f1,f2 FROM t1', array());
    $dp = new DataPaginator($df, 4, 5);
    $vp = new AdvancedTableVisualPaginator($dp, 'list.php', 'Test data', array('Col 1', 'Col 2'), array('th' => 'AAA', 'td' => 'BBB'), 'f1', 'edit.php', 'del.php');
    test_VisualPaginator($vp);
}

function test_SimpleListVisualPaginator() {
    $df = new PDOmysqlDataFetcher('localhost', 'root', '', 'Test', 'SELECT f1,f2 FROM t1', array());
    $dp = new DataPaginator($df, 4, 5);
    $vp = new SimpleListVisualPaginator($dp, 'list.php', 'Test data');
    test_VisualPaginator($vp);
}

function test_AdvancedListVisualPaginator() {
    $df = new PDOmysqlDataFetcher('localhost', 'root', '', 'Test', 'SELECT f1,f2 FROM t1', array());
    $dp = new DataPaginator($df, 4, 5);
    $vp = new AdvancedListVisualPaginator($dp, 'list.php', 'Test data', array('ul' => 'AAA', 'li' => 'BBB'), "f1=:f1\r\n<br>\r\nf2=:f2\r\n");
    test_VisualPaginator($vp);
}