How to Insert, Edit and Delete over three Database tables

karasu

Hi,
I have a list view which data is selected over three DB tables.
Now I have errors on editing (insert, delete) one row.
Have I mistake in my following lines ?
PHP file

date_default_timezone_set("Europe/Berlin");

include( "lib/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

Editor::inst( $db, 'rooms', 'uid' )
    ->field( 
        Field::inst( 'rooms.start_date' )
            ->set( false )
            ->validator( 'Validate::dateFormat', 'Y-m-d' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Anreise fehlt' )
            ) )
            ->getFormatter( Format::dateSqlToFormat( 'd.m.y' ) )
            ->setFormatter( Format::dateFormatToSql( 'Y-m-d' ) ),
        Field::inst( 'rooms.name' )
            ->options( 'rooms', 'name', 'rooms.name' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Apt. fehlt' )
            ) ),
        Field::inst( 'rooms.ca_dauer' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Anz.Tage fehlt' )
            ) ),
        Field::inst( 'customer.first_name' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Vorname fehlt' )
            ) ),
        Field::inst( 'customer.last_name' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Nachname fehlt' )
            ) ),
        Field::inst( 'rooms.traveler_count' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Anz.Gäste fehlt' )
            ) ),
        Field::inst( 'customer.number' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Telefon fehlt' )
            ) ),
        Field::inst( 'customer.email' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'E-Mail fehlt' )
            ) ),
        Field::inst( 'rooms.price' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Preis fehlt' )
            ) ),
        Field::inst( 'rooms.end_date' )
            ->set( false )
            ->validator( 'Validate::dateFormat', 'Y-m-d' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Abreise fehlt' )
            ) )
            ->getFormatter( Format::dateSqlToFormat( 'd.m.y' ) )
            ->setFormatter( Format::dateFormatToSql( 'Y-m-d' ) ),
        Field::inst( 'bookings.ota' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'OTA fehlt' )
            ) ),
        Field::inst( 'rooms.ca_guestmanager' )
            ->options( 'rooms', 'ca_guestmanager', 'rooms.ca_guestmanager' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'GM fehlt' )
            ) ),
        Field::inst( 'rooms.paid' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'rooms.ca_storno' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } ),
        Field::inst( 'rooms.checkin_guest_time' )
            ->validator( Validate::dateFormat(
                'H:i',
                ValidateOptions::inst()
                    ->allowEmpty( true )
            ) )
            ->getFormatter( Format::datetime(
                'H:i:s',
                'H:i'
            ) )
            ->setFormatter( Format::datetime(
                'H:i',
                'H:i:s'
            ) ), 
        Field::inst( 'rooms.ca_bemerkungen' ),      
        Field::inst( 'rooms.rooms_quantity' ),
        Field::inst( 'bookings.status' )
    )
    ->on( 'writeEdit', function ( $editor, $id, $values ) {
        $editor
            ->field( 'bookings.status' )
            ->setValue( 1 );
        $editor->db()
            ->raw()
            ->bind( ':internal_id', $id )
            ->exec( 'UPDATE bookings  
                        SET edited_date = NOW() 
                      WHERE internal_id = :internal_id' );
    } )    
    ->on( 'postEdit', function ( $editor, $id, $values, $row ) {        
        $editor->db()
            ->raw()
            ->bind( ':internal_id', $id )
            ->bind( ':status', ( $row["rooms"]["ca_storno"] == 1 ) ? "cancelled" : 'new' )
            ->exec( 'UPDATE bookings  
                        SET status = :status, 
                            edited_date = NOW() 
                      WHERE internal_id = :internal_id' );
    } )
    ->leftJoin( 'bookings', 'rooms.internal_id', '=', 'bookings.internal_id' )
    ->leftJoin( 'customer', 'customer.internal_id', '=', 'bookings.internal_id' )
    ->leftJoin( 'mitarbeiter', 'mitarbeiter.nick', '=', 'rooms.ca_guestmanager' )
    #->debug(true)
    ->process($_POST)
    ->json();

karasu

JS file

var editor; 

(function($) {
    $(document).ready(function() {
        console.log('START');
        editor = new $.fn.dataTable.Editor({
            ajax: '/php/table.bookings_test.php',
            table: '#bookings_test',
            fields: [
                {
                    label:     "Anreise:",
                    name:      "rooms.start_date",
                    type:      "datetime",
                    def:   function () { return new Date(); }
                },
                {
                    label:     "Apt.:",
                    name:      "rooms.name"
                },
                {
                    label:     "Anz.Tage.:",
                    name:      "rooms.ca_dauer"
                },
                {
                    label:     "Vorname:",
                    name:      "customer.first_name"
                },
                {
                    label:     "Nachname:",
                    name:      "customer.last_name"
                },
                {
                    label:     "Anz.Gäste:",
                    name:      "rooms.traveler_count"
                },
                {
                    label:     "Telefon:",
                    name:      "customer.number"
                },
                {
                    label:     "E-Mail:",
                    name:      "customer.email"
                },
                {
                    label:     "Preis:",
                    name:      "rooms.price"
                },
                {
                    label:     "Abreise:",
                    name:      "rooms.end_date",
                    type:      "datetime",
                    def:   function () { return new Date(); }
                },
                {
                    label:     "OTA:",
                    name:      "bookings.ota"
                },
            {
                "label": "GM:",
                "name": "rooms.ca_guestmanager",
                type: "select",
                options: {
                    "rooms.ca_guestmanager": [{
                        label: "4201",
                        value: "4201"
                    }]
                }
            }, 
            {
                label:     "Bezahlt:",
                name:      "rooms.paid",
                type:      "checkbox",
                separator: "|",
                options:   [
                    { label: '', value: 1 }
                ]
            },
            {
                label:     "Storno:",
                name:      "rooms.ca_storno",
                type:      "checkbox",
                separator: "|",
                options:   [
                    { label: '', value: 1 }
                ]
            },
            {
                label: 'Uhrzeit',
                name:  'rooms.checkin_guest_time',
                type:  'datetime',
                format: 'HH:mm',
                fieldInfo: '24 Std.',
                opts: {
                    minutesIncrement: 15,
                    secondsIncrement: 15
                }
            },
            {
                label: 'Kommentar:',
                name: 'rooms.ca_bemerkungen'
            }]
        });
        
        $('#bookings_test').on('click', 'tbody td, tbody span.dtr-data', function(e) {
            // Ignore the Responsive control and checkbox columns
            if ($(this).hasClass('control') || $(this).hasClass('select-checkbox')) {
                return;
            }
        });
        
        $('#bookings_test').on('click', 'td.guest_manager, td.bemerkung', function(e) {
            editor.inline( this, {
                buttons: { label: 'Speichern', fn: function () { this.submit(); } }
            } );
        });

        $('#bookings_test').on('click', 'tbody td.checkin_guest_time', function(e) {
            editor.inline( this, {
                buttons: { label: 'Speichern', fn: function () { this.submit(); } }
            } );
        });
        
        var table = $('#bookings_test').DataTable({
            "language": {
              "url": "/i18n/de_de.lang"
            },
            dom: "Bfrtip",
            ajax: '/php/table.bookings_test.php',
            order: [
                [0, "asc"],
                [2, "asc"],
                [1, "asc"]
            ],
            Processing: true,
            ServerSide: true,
            "lengthMenu": [[10, 20, 50, -1], [10, 20, 50, "alle"]],
            "pageLength": 20,
            columns: [
                { data: "rooms.start_date" },
                { data: "rooms.name" },
                { data: "rooms.rooms_quantity", "visible": false },
                { data: "rooms.ca_dauer" },
                { data: "customer.first_name" },
                { data: "customer.last_name" },
                { data: "rooms.traveler_count" },
                { data: "customer.number" },
                { data: "customer.email" },
                { data: "rooms.price" },
                { data: "rooms.end_date" },
                { data: "bookings.ota" },
                { 
                    data: "rooms.ca_guestmanager",
                    className: "guest_manager"
                },
                {
                    data:   "rooms.paid",
                    className: "dt-body-center"
                },
                {
                    data:   "rooms.ca_storno",
                    className: "dt-body-center"
                },
                { 
                    data: "rooms.checkin_guest_time",
                    className: "checkin_guest_time"
                },
                { 
                    data: "rooms.ca_bemerkungen",
                    className: "bemerkung"
                }
            ],
            responsive: true,
            columnDefs: [
                { type: 'date-eu', targets: 0 },
                { type: 'date-eu', targets: 10 },
                { responsivePriority: 1, targets: 0 },
                { responsivePriority: 1, targets: 1 },
                { responsivePriority: 1, targets: 3 },
                { responsivePriority: 1, targets: 9 },
                { responsivePriority: 1, targets: 12 },
                { responsivePriority: 2, targets: 14 },
                { responsivePriority: 1, targets: 15 },
                { responsivePriority: 1, targets: 16 }
            ],
            select: true,
            buttons: [
                { extend: 'create', editor: editor },
                { extend: 'edit',   editor: editor },
                { extend: 'remove', editor: editor }
            ]
        });
    });
}(jQuery));

allan

Now I have errors on editing (insert, delete) one row.

I don't quite understand what you mean - are you inserting a new row and deleting the old one in order to do an edit?

What are the errors you are getting?

Allan

karasu

Hi @allan , sorry I have explain my problem wrong.

I save the data during import into three different tables:
1. customer
2. booking
3. rooms

When viewing the records, everything is displayed correctly.
Filter, order, etc. works also correct.

Now I want to do the following:
(For all three procedures,
the values are only written to the rooms table, not in customer or bookings table)
1. Add new record
2. edit the records
3. Delete the selected records

Can you help me please ? Do I have a bug in my code?

allan

No - no bug in your code, its just that this is not something that the Editor server-side libraries currently attempt to handle. It is possible to edit across three tables by ensuring that you submit the primary key of the joined tables, delete could be done in the database with a on delete cascade, but there is no way to add new records across three tables with the existing libraries we provide. That would require some custom server-side code I'm afraid.

Allan