File builder/table/SQLHTMLTableModel.class.php

  1:   2:   3:   4:   5:   6:   7:   8:   9:  10:  11:  12:  13:  14:  15:  16:  17:  18:  19:  20:  21:  22:  23:  24:  25:  26:  27:  28:  29:  30:  31:  32:  33:  34:  35:  36:  37:  38:  39:  40:  41:  42:  43:  44:  45:  46:  47:  48:  49:  50:  51:  52:  53:  54:  55:  56:  57:  58:  59:  60:  61:  62:  63:  64:  65:  66:  67:  68:  69:  70:  71:  72:  73:  74:  75:  76:  77:  78:  79:  80:  81:  82:  83:  84:  85:  86:  87:  88:  89:  90:  91:  92:  93:  94:  95:  96:  97:  98:  99: 100: 101: 102: 103: 104: 105: 106: 107: 

<?php
/**
 * @package     Builder
 * @subpackage  Table
 * @copyright   &copy; 2005-2019 PHPBoost
 * @license     https://www.gnu.org/licenses/gpl-3.0.html GNU/GPL-3.0
 * @author      Kevin MASSY <reidlos@phpboost.com>
 * @version     PHPBoost 5.2 - last update: 2015 06 10
 * @since       PHPBoost 4.1 - 2015 01 19
 * @contributor Julien BRISWALTER <j1.seth@phpboost.com>
*/

class SQLHTMLTableModel extends HTMLTableModel
{
    protected $table;
    protected $parameters;

    public function __construct($table, $id, array $columns, HTMLTableSortingRule $default_sorting_rule, $rows_per_page = self::DEFAULT_PAGINATION)
    {
        $this->table = $table;
        parent::__construct($id, $columns, $default_sorting_rule, $rows_per_page);
    }

    public function get_number_of_matching_rows()
    {
        return PersistenceContext::get_querier()->count($this->table, $this->get_filtered_clause($this->html_table->parameters->get_filters()) . $this->get_permanent_filtered_clause($this->get_permanent_filters()), $this->parameters);
    }

    public function get_sql_results($sql_join = false, $select = array('*'))
    {
        $limit = $this->html_table->get_nb_rows_per_page();
        $offset = ($this->html_table->parameters->get_page_number() - 1) * $limit;
        $sorting_rule = $this->html_table->parameters->get_sorting_rule();
        $filters = $this->html_table->parameters->get_filters();
        $permanent_filters = $this->get_permanent_filters();

        $query = 'SELECT ' . implode(', ', $select) . ' ';
        $query .= $this->get_sql_from($sql_join);
        $query .= $this->get_filtered_clause($filters);
        $query .= $this->get_permanent_filtered_clause($permanent_filters);
        $query .= $this->get_order_clause($sorting_rule);
        $query .= $limit !== HTMLTableModel::NO_PAGINATION ? ' LIMIT ' . $limit . ' OFFSET ' . $offset : '';

        return PersistenceContext::get_querier()->select($query, $this->parameters);
    }

    public function get_sql_from($sql_join)
    {
        if (!empty($sql_join))
        {
            return 'FROM ' . $this->table . ' ' . $sql_join;
        }
        return 'FROM ' . $this->table;
    }

    private function get_filtered_clause(array $filters)
    {
        $this->parameters = array();
        $clause = ' WHERE 1';
        if (!empty($filters))
        {
            $sql_filters = array();
            foreach ($filters as $filter)
            {
                $query_fragment = $filter->get_sql();
                $query_fragment->add_parameters_to_map($this->parameters);
                if ($query_fragment->get_query())
                    $sql_filters[] = $query_fragment->get_query();
            }
            $clause .= !empty($sql_filters) ? ' AND ' . implode(' AND ', $sql_filters) : '';
        }
        return $clause;
    }

    private function get_permanent_filtered_clause(array $permanent_filters)
    {
        if (!empty($permanent_filters))
        {
            return ' AND ' . implode(' AND ', $permanent_filters);
        }
    }

    private function get_order_clause(HTMLTableSortingRule $rule)
    {
        $order_clause = ' ORDER BY ';
        $order_clause .= $this->get_sort_parameter_column($rule) . ' ';
        if ($rule->get_order_way() == HTMLTableSortingRule::ASC)
        {
            $order_clause .= SQLQuerier::ORDER_BY_ASC;
        }
        else
        {
            $order_clause .= SQLQuerier::ORDER_BY_DESC;
        }
        return $order_clause;
    }

    private function get_sort_parameter_column(HTMLTableSortingRule $rule)
    {
        if ($this->is_sort_parameter_allowed($rule->get_sort_parameter()) || $rule->is_default_sorting())
        {
            return $rule->get_sort_parameter();
        }
    }
}
?>