About CRUD Operations

<?php
namespace crud;

use common\Utils;
use phpformbuilder\database\Mysql;
use phpformbuilder\database\Pagination;
use secure\Secure;

class Actor extends Elements
{

    // item name passed in url
    public $item;

    // item name dispolayed
    public $item_label;

    // associative array : field => field displayed name
    public $fields;

    // external relations
    public $external_tables_count = 1;
    public $external_fields_count;
    public $external_rows_count;
    public $external_fields = array();

    // primary key passed to create|edit|delete
    public $primary_key; // primary key fieldname
    public $primary_key_alias; // primary key alias for query

    // CREATE rights
    public $can_create = false;

    // authorized primary keys for restricted updates
    public $authorized_update_pk = array();

    public $pk = array(); // primary key values for each row
    public $actor_id = array();
    public $first_name = array();
    public $last_name = array();
    public $last_update = array();

    public $export_data_button;
    public $filtered_columns = '';
    public $filters_form;
    public $join_query       = '';
    public $pagination_html;
    public $records_count;
    public $select_number_per_page;
    public $sorting;
    public $url;

    public function __construct($element)
    {
        $this->table             = $element->table;
        $this->item              = $element->item;
        $this->item_label        = $element->item_label;
        $this->primary_key       = $element->primary_key;
        $this->primary_key_alias = $element->primary_key;
        $this->select_data       = $element->select_data;
        $this->fields            = $element->fields;

        $table = $this->table;

        if (file_exists(ADMIN_DIR . 'crud-data/' . $this->item . '-filter-data.json')) {
            $json = file_get_contents(ADMIN_DIR . 'crud-data/' . $this->item . '-filter-data.json');
            $filters_array = json_decode($json, true);
        }
        $this->url        = $_SERVER['REQUEST_URI'];
        $qry_start        = 'SELECT `actor`.`actor_id`, `actor`.`first_name`, `actor`.`last_name`, `actor`.`last_update` FROM actor';

        // restricted rights query
        $qry_restriction = '';
        if (Secure::canReadRestricted($table)) {
            $qry_restriction = Secure::getRestrictionQuery($table);
        }

        // filters
        $filters           = new ElementsFilters($table, $filters_array, $this->join_query);
        $filters_where_qry = $filters->returnWhereQry();
        if (!empty($qry_restriction)) {
            $filters_where_qry = str_replace('WHERE', 'AND', $filters_where_qry);
        }

        if (isset($_SESSION['filtered_columns']) && is_array($_SESSION['filtered_columns'])) {
            $this->filtered_columns = implode(', ', $_SESSION['filtered_columns']);
        }
        $this->filters_form = $filters->returnForm($this->url);

        $active_filters_join_queries = array();

        // Get join queries from active filters
        $active_filters_join_queries = $filters->buildElementJoinQuery();

        // sorting query
        $this->sorting = ElementsUtilities::getSorting($table, $this->primary_key, 'ASC');
        $qry_sorting   = " ORDER BY" . $this->sorting;

        $db = new Pagination();
        $pagination_url = $_SERVER['REQUEST_URI'];
        if (isset($_POST['npp']) && is_numeric($_POST['npp'])) {
            $_SESSION['npp'] = $_POST['npp'];
        } elseif (!isset($_SESSION['npp'])) {
            $_SESSION['npp'] = 20;
        }

        // echo $qry_start . $qry_restriction . $active_filters_join_queries . $filters_where_qry . $qry_sorting;
        $this->pagination_html = $db->pagine($qry_start . $qry_restriction . $active_filters_join_queries . $filters_where_qry . $qry_sorting, $_SESSION['npp'], 'p', $pagination_url, 5, true, '/', '');

        $this->records_count = $db->rowCount();
        $primary_key = $this->primary_key_alias;
        if (!empty($this->records_count)) {
            while (!$db->endOfSeek()) {
                $row = $db->row();
                $this->pk[] = $row->$primary_key;
                $this->actor_id[]= $row->actor_id;
                $this->first_name[]= $row->first_name;
                $this->last_name[]= $row->last_name;
                $this->last_update[]= $row->last_update;
            }
        }

        // CREATE/DELETE rights
        if (Secure::canCreate($table) || Secure::canCreateRestricted($table) === true) {
            $this->can_create = true;
        }

        // restricted UPDATE rights
        if (Secure::canUpdateRestricted($table) === true) {
            $transition = 'WHERE';
            if (!empty($filters_where_qry)) {
                $transition = 'AND';
            }

            // get authorized update primary keys
            $qry_start .= Secure::getRestrictionQuery($table, $transition);
            $db = new Pagination();
            $pagination_html = $db->pagine($qry_start . $filters_where_qry . $qry_sorting, $_SESSION['npp'], 'p', $pagination_url, 5, true, '/', '');
            $records_count = $db->rowCount();
            $primary_key = $this->primary_key_alias;
            if (!empty($records_count)) {
                while (!$db->endOfSeek()) {
                    $row = $db->row();
                    $this->authorized_update_pk[] = $row->$primary_key;
                }
            }
        } elseif (Secure::canUpdate($table) === true || Secure::canUpdateRestricted($table) === true) {
            // user can update ALL records
            $this->authorized_update_pk = $this->pk;
        }

        /* external relations */

        for ($i=0; $i < count($this->pk); $i++) {
            $this->external_rows_count[$i] = array();
            $this->external_fields[$i] = array();
            $db = new Mysql();
                    // actor => film_actor => film
                $qry = 'SELECT film.film_id, film.title, film.description, film.release_year FROM actor INNER JOIN `film_actor`
            ON `film_actor`.`actor_id`=`actor`.`actor_id` INNER JOIN `film`
            ON `film_actor`.`film_id`=`film`.`film_id` WHERE actor.' . $this->primary_key . ' = ' . $this->pk[$i];
            $db->query($qry);
            $this->external_rows_count[$i][] = $db->rowCount();
            $ext_fields = array(
                'table'       => 'film',
                'table_label' => 'film',
                'uniqid'      => 'f-' . uniqid(),
                'fields'      => array(
                        'title' => array(),
                        'description' => array(),
                        'release_year' => array()
                    )
            );
            if (!empty($this->external_rows_count[$i])) {
                while (! $db->endOfSeek()) {
                    $row = $db->row();
                    $ext_fields['fields']['title'][]  = $row->title;
                    $ext_fields['fields']['description'][]  = $row->description;
                    $ext_fields['fields']['release_year'][]  = $row->release_year;
                    $ext_fields['fields']['action'][]  = '<div class="btn-group"><a href="film_actor/edit/' . $row->film_id . '" class="btn btn-xs btn-warning legitRipple" title="View" data-popup="tooltip" data-delay="500"><span class="fas fa-pencil-alt"></span></a><a href="film_actor/delete/' . $row->film_id . '" class="btn btn-xs btn-danger legitRipple" title="Delete" data-popup="tooltip" data-delay="500"><span class="fas fa-times-circle"></span></a></div>';
                } // end while
            } // end if
            $this->external_fields[$i][] = $ext_fields;
        } // end for
        $this->external_fields_count = count($this->external_fields);

        // Export data button
        $this->export_data_button = ElementsUtilities::exportDataButtons($table, 'SELECT * FROM ' . $table . ' ORDER BY ' . $this->primary_key . ' ASC', 'excel, csv');

        // number/page
        $numbers_array = array(5, 10, 20, 50, 100, 200, 10000);
        $this->select_number_per_page = ElementsUtilities::selectNumberPerPage($numbers_array, $_SESSION['npp'], $this->url);
    }
}
                    

    <div class="card {{ constant('DEFAULT_CARD_CLASS') }} mr-4">
        <div class="card-header d-lg-flex flex-wrap justify-content-between {{ constant('DEFAULT_CARD_HEADING_CLASS') }}">
            {% if object.records_count > 0 %}

            <div class="d-flex ml-auto order-lg-2">
                {{ object.select_number_per_page|raw }}
            </div>

            <hr class="w-100 d-lg-none">

            {% endif %}
            <div class="d-flex order-lg-0 mb-3 mb-sm-0">
                {% if object.can_create == true %}
                <a href="{{ constant('ADMIN_URL') }}{{ object.item }}/create" class="btn btn-sm mr-1 btn-primary d-flex align-items-center legitRipple"><i class="{{ constant('ICON_PLUS') }} position-left"></i>{{ constant('ADD_NEW') }}</a>
                {% endif %}
                {% if object.records_count > 0 %}
                {{ object.export_data_button|raw }}
                {% endif %}
            </div>

            <div class="order-lg-1 mx-lg-auto">
                <form name="rp-search-form" id="rp-search-form" action="" class="form-inline justify-content-center">
                    <div class="form-group">
                        <div class="input-group">
                            <div id="rp-search-field" class="dropdown input-group-prepend">
                                <a class="dropdown-toggle pl-4 pr-3 rounded-left border-left border-top border-bottom" id="search-dropdown-link" data-toggle="dropdown" aria-haspopup="true"
                                        aria-expanded="false"></a>
                                <div class="dropdown-menu" aria-labelledby="search-dropdown-link">
                                    {% for field_name, field_display_name in object.fields %}
                                    {% set active = '' %}
                                    {% if field_name == attribute(session.rp_search_field, object.table) %}
                                    {% set active = ' active' %}
                                    {% endif %}
                                    <a class="dropdown-item{{ active }}" href="#" data-value="{{ field_name }}">{{ field_display_name }}</a>
                                    {% endfor %}
                                </div>
                            </div>
                            {% set search_value = '' %}
                            {% if attribute(session.rp_search_string, object.table) is defined %}
                            {% set search_value = attribute(session.rp_search_string, object.table) %}
                            {% endif %}
                            <input id="rp-search" name="rp-search" type="text" value="{{ search_value }}" placeholder="{{ constant('SEARCH') }}" class="form-control flex-grow-1">
                            <div class="input-group-append">
                                <button id="rp-search-submit" class="btn btn-secondary ladda-button" data-style="zoom-in" type="submit"><span class="ladda-label"><i class="{{ constant('ICON_SEARCH') }}"></i></span></button>
                            </div>
                        </div>
                    </div>
                </form>
            </div>

        </div>

        {# Partial block list - rendered alone on the research results #}
        {% block object_list %}

        <div id="{{ object.item }}-list">

        {% if object.records_count > 0 %}

            <div class="table-responsive">
                <table class="table table-striped table-condensed table-data">
                    <thead>
                        <tr class="{{ constant('DEFAULT_TABLE_HEADING_BACKGROUND') }}">
                            {% if constant('ADMIN_ACTION_BUTTONS_POSITION') == 'left' %}
                            <th>{{ constant('ACTION_CONST') }}</th>
                            {% endif %}

                            <th class="sorting">{{ object.fields.actor_id }}<a href="#" class="sorting-up" data-field="actor_id" data-direction="ASC"><i class="{{ constant('ICON_ARROW_UP') }}"></i></a><a href="#" class="sorting-down" data-field="actor_id" data-direction="DESC"><i class="{{ constant('ICON_ARROW_DOWN') }}"></i></a></th>
                            <th class="sorting">{{ object.fields.first_name }}<a href="#" class="sorting-up" data-field="first_name" data-direction="ASC"><i class="{{ constant('ICON_ARROW_UP') }}"></i></a><a href="#" class="sorting-down" data-field="first_name" data-direction="DESC"><i class="{{ constant('ICON_ARROW_DOWN') }}"></i></a></th>
                            <th class="sorting">{{ object.fields.last_name }}<a href="#" class="sorting-up" data-field="last_name" data-direction="ASC"><i class="{{ constant('ICON_ARROW_UP') }}"></i></a><a href="#" class="sorting-down" data-field="last_name" data-direction="DESC"><i class="{{ constant('ICON_ARROW_DOWN') }}"></i></a></th>
                            <th class="sorting">{{ object.fields.last_update }}<a href="#" class="sorting-up" data-field="last_update" data-direction="ASC"><i class="{{ constant('ICON_ARROW_UP') }}"></i></a><a href="#" class="sorting-down" data-field="last_update" data-direction="DESC"><i class="{{ constant('ICON_ARROW_DOWN') }}"></i></a></th>
                            <th>film</th>
                            <th>{{ constant('DISPLAY') }}</th>
                        {% if constant('ADMIN_ACTION_BUTTONS_POSITION') == 'right' %}
                            <th>{{ constant('ACTION_CONST') }}</th>
                        {% endif %}
                        </tr>
                    </thead>
                    <tbody>
                    {% for i in range(0, object.records_count - 1) %}
                        <tr>
                            {% if constant('ADMIN_ACTION_BUTTONS_POSITION') == 'left' %}
                            <td class="has-btn-group no-ellipsis">
                                <div class="btn-group">
                                    {% if object.pk[loop.index0] in object.authorized_update_pk %}
                                    <a href="{{ constant('ADMIN_URL') }}{{ object.item }}/edit/{{ object.pk[loop.index0] }}" class="btn btn-sm btn-warning legitRipple" data-tooltip="{{ constant('EDIT') }}" data-delay="500"><span class="{{ constant('ICON_EDIT') }} icon-md"></span></a>
                                    {% endif %}
                                    {% if object.can_create == true %}
                                    <a href="{{ constant('ADMIN_URL') }}{{ object.item }}/delete/{{ object.pk[loop.index0] }}" class="btn btn-sm btn-danger legitRipple" data-tooltip="{{ constant('DELETE_CONST') }}" data-delay="500"><span class="{{ constant('ICON_DELETE') }} icon-md"></span></a>
                                    {% endif %}
                                </div>
                            </td>
                            {% endif %}
                        <td>{{ object.actor_id[ loop.index0 ] }}</td>
                        <td>{{ object.first_name[ loop.index0 ] }}</td>
                        <td>
                        {% if object.pk[loop.index0] in object.authorized_update_pk %}
                           <span class="jedit-text tip" data-field="last_name" data-delay="500" title="{{ constant('CLICK_TO_EDIT') }}" id="actor-last_name-actor_id-{{ object.pk[ loop.index0 ] }}">{{ object.last_name[ loop.index0 ] }}</span>
                        {% else %}
                            {{ object.last_name[ loop.index0 ] }}
                        {% endif %}
                </td>
                        <td>{{ toDate(object.last_update[ loop.index0 ], 'dd MMMM yyyy H:m a')|raw }}</td>
                            {% if object.external_tables_count > 0 %}
                            {% for j in range(0, object.external_tables_count - 1) %}
                            <td class="no-ellipsis">
                                {% if object.external_rows_count[i][j] > 0 %}
                                <h6 class="card-title text-center text-nowrap mb-2"><span class="badge bg-gray-300 position-left">{{ object.external_rows_count[i][j] }}</span><a class="dropdown-toggle" data-toggle="collapse" href="#{{ object.external_fields[i][j]['uniqid'] }}" role="button" aria-expanded="false" aria-controls="{{ object.external_fields[i][j]['uniqid'] }}"><small class="text-muted nowrap">{{ constant('SHOW') }} / {{ constant('HIDE') }}</small></a></h6>
                                <div class="collapse" id="{{ object.external_fields[i][j]['uniqid'] }}">
                                {{ object.external_add_btn[i][j]|raw }}
                                    <table class="table table-striped table-condensed">
                                        <thead class=" {{ constant('DEFAULT_TABLE_HEADING_BACKGROUND') }}">
                                            <tr>
                                                {% for field, value in object.external_fields[i][j].fieldnames %}
                                                <th>{{ value }}</th>
                                                {% endfor %}
                                            </tr>
                                        </thead>
                                        <tbody>

                                            {# Loop records #}

                                            {% for k in range(0, object.external_rows_count[i][j] - 1) %}
                                            <tr>

                                                {# Loop fields #}

                                                {% for field, value in object.external_fields[i][j].fields %}
                                                <td>{{ object.external_fields[i][j].fields[field][k]|raw }}</td>
                                                {% endfor %}
                                            </tr>
                                            {% endfor %}
                                        </tbody>
                                    </table>
                                </div>
                                {% else %}
                                {{ object.external_add_btn[i][j]|raw }}
                                {% endif %}
                            </td>
                            {% endfor %}
                            {% endif %}
                            <td><a href="{{ constant('BASE_URL') }}" data-delay="500" data-tooltip="{{ constant('OPEN_URL') }}" target="_blank"><span class="{{ constant('ICON_NEW_TAB') }} text-center"></span></a></td>
                            {% if constant('ADMIN_ACTION_BUTTONS_POSITION') == 'right' %}
                            <td class="has-btn-group no-ellipsis">
                                <div class="btn-group">
                                    {% if object.pk[loop.index0] in object.authorized_update_pk %}
                                    <a href="{{ constant('ADMIN_URL') }}{{ object.item }}/edit/{{ object.pk[loop.index0] }}" class="btn btn-sm btn-warning legitRipple" data-tooltip="{{ constant('EDIT') }}" data-delay="500"><span class="{{ constant('ICON_EDIT') }} icon-md"></span></a>
                                    {% endif %}
                                    {% if object.can_create == true %}
                                    <a href="{{ constant('ADMIN_URL') }}{{ object.item }}/delete/{{ object.pk[loop.index0] }}" class="btn btn-sm btn-danger legitRipple" data-tooltip="{{ constant('DELETE_CONST') }}" data-delay="500"><span class="{{ constant('ICON_DELETE') }} icon-md"></span></a>
                                {% endif %}
                                </div>
                            </td>
                            {% endif %}
                        </tr>
                        {% endfor %}
                    </tbody>
                </table>
            </div> <!-- END table-responsive -->

            {% else %}
            <div class="card-body">
                <p class="text-semibold">
                    {{ alert(constant('NO_RECORD_FOUND'), 'alert-info has-icon')|raw }}
                </p>
            </div>
            {% endif %}

            <div class="card-footer  {{ constant('DEFAULT_CARD_FOOTER_CLASS') }} p-4 mt-5">
                {{ object.pagination_html|raw }}
            </div>
        </div> <!-- END {{ object.item }}-list -->

        {% endblock object_list %}
        {# END Partial block - rendered alone on the research results #}

    </div> <!-- END card -->

                    

<?php
use phpformbuilder\Form;
use phpformbuilder\Validator\Validator;
use phpformbuilder\database\Mysql;
use common\Utils;
use secure\Secure;

include_once ADMIN_DIR . 'secure/class/secure/Secure.php';

/* =============================================
    validation if posted
============================================= */

if ($_SERVER["REQUEST_METHOD"] == "POST" && Form::testToken('form-edit-actor') === true) {
    include_once CLASS_DIR . 'phpformbuilder/Validator/Validator.php';
    include_once CLASS_DIR . 'phpformbuilder/Validator/Exception.php';
    $validator = new Validator($_POST);
    $validator->required()->validate('actor_id');
    $validator->integer()->validate('actor_id');
    $validator->min(0)->validate('actor_id');
    $validator->max(65535)->validate('actor_id');
    $validator->required()->validate('first_name');
    $validator->maxLength(45)->validate('first_name');
    $validator->required()->validate('last_name');
    $validator->maxLength(45)->validate('last_name');
    $validator->required()->validate('last_update');
    $validator->date()->validate('last_update');

    // check for errors
    if ($validator->hasErrors()) {
        $_SESSION['errors']['form-edit-actor'] = $validator->getAllErrors();
    } else {
        require_once CLASS_DIR . 'phpformbuilder/database/db-connect.php';
        require_once CLASS_DIR . 'phpformbuilder/database/Mysql.php';
        $db = new Mysql();
        $update['first_name'] = Mysql::SQLValue($_POST['first_name'], Mysql::SQLVALUE_TEXT);
        $update['last_name'] = Mysql::SQLValue($_POST['last_name'], Mysql::SQLVALUE_TEXT);
        $update['last_update'] = Mysql::SQLValue($_POST['last_update'] . ' ' . $_POST['last_update-time'], Mysql::SQLVALUE_DATETIME);
        $filter["actor_id"] = Mysql::SQLValue($_POST['actor_id']);
        $db->throwExceptions = true;
        try {
                // begin transaction
                $db->transactionBegin();

                // update actor
            if (DEMO !== true && !$db->updateRows('actor', $update, $filter)) {
                $error = $db->error();
                $db->transactionRollback();
                throw new \Exception($error);
            } else {
                // ALL OK
                $db->transactionEnd();
                $_SESSION['msg'] = Utils::alert(UPDATE_SUCCESS_MESSAGE, 'alert-success has-icon');

                // reset form values
                Form::clear('form-edit-actor');

                // redirect to list page
                $page_link = '';
                if (isset($_SESSION['previous_page_number'])) {
                    $page_link = '/p' . $_SESSION['previous_page_number'];
                }
                header('Location:/admin/actor' . $page_link);

                // if we don't exit here, $_SESSION['msg'] will be unset
                exit();
            }
        } catch (\Exception $e) {
            $msg_content = DB_ERROR;
            if (ENVIRONMENT == 'development') {
                $msg_content .= '<br>' . $e->getMessage() . '<br>' . $db->getLastSql();
            }
            $_SESSION['msg'] = Utils::alert($msg_content, 'alert-danger has-icon');
        }
    } // END else
} // END if POST
$actor_id = $pk;
if (!isset($_SESSION['errors']['form-edit-actor']) || empty($_SESSION['errors']['form-edit-actor'])) { // If no error registered
    $qry = "SELECT * FROM `actor`";

    $transition = 'WHERE';

    // if restricted rights
    if (Secure::canUpdateRestricted('actor')) {
        $qry .= Secure::getRestrictionQuery('actor');
        $transition = 'AND';
    }
    $qry .= ' ' . $transition . " actor.actor_id = '$actor_id'";

    $db = new Mysql();
    $db->query($qry);
    if ($db->rowCount() < 1) {
        if (DEBUG === true) {
            exit($db->getLastSql() . ' : No Record Found');
        } else {
            Secure::logout();
        }
    }
    $row = $db->row();
    $_SESSION['form-edit-actor']['actor_id'] = $row->actor_id;
    $_SESSION['form-edit-actor']['first_name'] = $row->first_name;
    $_SESSION['form-edit-actor']['last_name'] = $row->last_name;
    $_SESSION['form-edit-actor']['last_update'] = $row->last_update;
}
$form = new Form('form-edit-actor', 'horizontal', 'novalidate', 'bs4');
$form->setAction('/admin/actor/edit/' . $actor_id);
$form->startFieldset();
$form->setCols(2, 10);
$form->addInput('hidden', 'actor_id', '');
$form->groupInputs('first_name', 'last_name');
$form->setCols(2, 4);
$form->addInput('text', 'first_name', '', 'First Name', 'required');
$form->addInput('text', 'last_name', '', 'Last Name', 'required');
$form->groupInputs('last_update', 'last_update-time');
$form->setCols(2, 10);
$form->addPlugin('pickadate', '#last_update', 'custom-date', array('%format%' => 'dd mmmm yyyy')); // date field
$form->addPlugin('pickadate', '#last_update-time', 'custom-time', array('%interval%' => 15, '%format%' => 'H:i a')); // time field

$form->setCols(2, 6);
$form->addInput('text', 'last_update', '', 'Last Update', 'required');
$form->setCols(0, 4);
$form->addInput('text', 'last_update-time', '', '', 'required, placeholder=Heure');
$form->setCols(2, 10);
$form->addBtn('button', 'cancel', 0, '<i class="' . ICON_BACK . ' position-left"></i>' . CANCEL, 'class=btn btn-warning legitRipple, onclick=history.go(-1)', 'btn-group');
$form->addBtn('submit', 'submit-btn', 1, SUBMIT . '<i class="' . ICON_CHECKMARK . ' position-right"></i>', 'class=btn btn-success legitRipple', 'btn-group');
$form->setCols(0, 12);
$form->centerButtons(true);
$form->printBtnGroup('btn-group');
$form->endFieldset();
$form->addPlugin('nice-check', 'form', 'default', array('%skin%' => 'green'));