Editing left join tables

Editing left join tables

shantiramshantiram Posts: 13Questions: 5Answers: 2

Hi,
I'm writing a small SPA using datatables and editor: https://www.yogameditation.com/karto/

Trying to create a few parent/child table pairs as described here: https://datatables.net/blog/2016-03-25

Currently facing two issues:

  1. On joined tables, edits are only registered for the "main" table i.e. if I'm getting a table "student" and joining with "enrolment" then only edits on "student" will be saved. Similar problem on create. Rows for both tables are created, but no foreign key is generated for the "enrolment" row so it never shows up in the datatable. Is there a way to set something like mysqli::$insert_id as the default for a field?

  2. Whenever a row in the parent table is selected, the ajax request for the child table is made like 2-4 times. (have attached a screenshot) Not sure why this is happening.

Happy for any help or suggestions

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    If you could post your data model and your PHP Editor source code that would be great.

  • shantiramshantiram Posts: 13Questions: 5Answers: 2

    There are three tables used in the code below: course, student, and enrolment.
    Enrolment acts as a bridge table for student and course.
    They are joined on
    student.student_id = enrolment.student_id and enrolment.course_id = course.course_id
    I haven't set up any actual foreign keys in mysql. Could that be the problem?

    new_enrolments.php

    <?php
    
    // DataTables PHP library and database connection
    include( "lib/DataTables.php" );
    
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate;
    
    
    Editor::inst( $db, 'student', 'student_id')
        ->field(
            Field::inst( 'student.first_name' ),
            Field::inst( 'student.last_name' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'student.phone' ),
            Field::inst( 'student.phone_2' ),
            Field::inst( 'student.email' ),
            Field::inst( 'student.last_updated' ),
            Field::inst( 'student.language' )
        ->getFormatter( 'Format::explode' )
        ->setFormatter( 'Format::implode' ),
            Field::inst( 'student.address' ),
            Field::inst( 'student.postcode' ),
            Field::inst( 'student.city' ),
            Field::inst( 'student.state' ),
            Field::inst( 'student.country' ),
            Field::inst( 'student.born' ),
            Field::inst( 'student.sex' ),
            Field::inst( 'student.status' ),
            Field::inst( 'student.email_language' ),
            Field::inst( 'student.newsletter' ),
            Field::inst( 'student.occupation' ),
            Field::inst( 'enrolment.student_id' ),
            Field::inst( 'enrolment.enrolment_id' ),
            Field::inst( 'enrolment.course_id' ),
            Field::inst( 'enrolment.rebate' ),
            Field::inst( 'enrolment.enroldate' ),
            Field::inst( 'enrolment.currency' ),
            Field::inst( 'enrolment.room' ),
            Field::inst( 'enrolment.beddings' ),
            Field::inst( 'enrolment.enrolment_info' )
        )
        ->leftJoin( 'enrolment', 'enrolment.student_id', '=', 'student.student_id' )
        ->leftJoin( 'course', 'course.course_id', '=', 'enrolment.course_id' )
        ->where( 'student.status', 'new')
        ->process($_POST)
        ->json();
    

    course_enrolments.php

    <?php
    
    // DataTables PHP library and database connection
    include( "lib/DataTables.php" );
    
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate;
    
    
    if ( ! isset($_POST['course_id'])) {
        echo json_encode( [ "data" => [] ] );
    }
    else {
        Editor::inst( $db, 'enrolment', 'enrolment_id')
            ->field(
                    Field::inst( 'student.first_name' ),
            Field::inst( 'student.last_name' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'student.phone' ),
            Field::inst( 'student.phone_2' ),
            Field::inst( 'student.email' ),
            Field::inst( 'student.last_updated' ),
            Field::inst( 'student.language' )
        ->getFormatter( 'Format::explode' )
        ->setFormatter( 'Format::implode' ),
            Field::inst( 'student.address' ),
            Field::inst( 'student.postcode' ),
            Field::inst( 'student.city' ),
            Field::inst( 'student.state' ),
            Field::inst( 'student.country' ),
            Field::inst( 'student.born' ),
            Field::inst( 'student.sex' ),
            Field::inst( 'student.status' ),
            Field::inst( 'student.email_language' ),
            Field::inst( 'student.occupation' ),
            Field::inst( 'student.student_info' ),
            Field::inst( 'student.level' ),
            Field::inst( 'enrolment.enrolment_id' ),
            Field::inst( 'enrolment.course_id' )
            ->options( 'course', 'course_id', 'course_name_english' )
            ->validator( 'Validate::dbValues' ),
            Field::inst( 'enrolment.rebate' ),
            Field::inst( 'enrolment.enroldate' ),
            Field::inst( 'enrolment.currency' ),
            Field::inst( 'enrolment.rebate_documentation' ),
            Field::inst( 'enrolment.room' ),
            Field::inst( 'enrolment.beddings' ),
            Field::inst( 'enrolment.extra_fee' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.extra_fee_name' ),
            Field::inst( 'enrolment.extra_rebate' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.extra_rebate_name' ),
            Field::inst( 'enrolment.health_cert' ),
            Field::inst( 'enrolment.transport' ),
            Field::inst( 'enrolment.transport_place' ),
            Field::inst( 'enrolment.transport_time' ),
            Field::inst( 'enrolment.payment_1' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.payment_1_date' ),
            Field::inst( 'enrolment.payment_1_type' ),
            Field::inst( 'enrolment.payment_2' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.payment_2_date' ),
            Field::inst( 'enrolment.payment_2_type' ),
            Field::inst( 'enrolment.payment_3' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.payment_3_date' ),
            Field::inst( 'enrolment.payment_3_type' ),
            Field::inst( 'enrolment.payment_4' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.payment_4_date' ),
            Field::inst( 'enrolment.payment_4_type' ),
            Field::inst( 'enrolment.payment_5' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.payment_5_date' ),
            Field::inst( 'enrolment.payment_5_type' ),
            Field::inst( 'enrolment.enrolment_info' ),
            Field::inst( 'enrolment.email_sent' ),
            Field::inst( 'enrolment.cancel' ),
            Field::inst( 'course.level' ),
            Field::inst( 'course.sek_full_price' ),
            Field::inst( 'course.sek_deposit' ),
            Field::inst( 'course.sek_deposit_2' ),
            Field::inst( 'course.sek_single_room' ),
            Field::inst( 'course.sek_double_room' ),
            Field::inst( 'course.sek_beddings' ),
            Field::inst( 'course.sek_rebate_price' ),
            Field::inst( 'course.eur_full_price' ),
            Field::inst( 'course.eur_deposit' ),
            Field::inst( 'course.eur_deposit_2' ),
            Field::inst( 'course.eur_single_room' ),
            Field::inst( 'course.eur_double_room' ),
            Field::inst( 'course.eur_beddings' ),
            Field::inst( 'course.eur_rebate_price' ),
            Field::inst( 'course.start_date' )
            )
            ->leftJoin( 'student', 'student.student_id', '=', 'enrolment.student_id' )
            ->leftJoin( 'course', 'course.course_id', '=', 'enrolment.course_id' )
            ->where( 'enrolment.course_id', $_POST['course_id'] )
            ->where( 'student.status', 'OK' )
            ->process($_POST)
            ->json();
    }
    
    
  • shantiramshantiram Posts: 13Questions: 5Answers: 2

    ...and the last one:
    student_enrolments.php

    <?php
    
    // DataTables PHP library and database connection
    include( "lib/DataTables.php" );
    
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate;
    
    
    if ( ! isset($_POST['student_id'])) {
        echo json_encode( [ "data" => [] ] );
    }
    else {
        Editor::inst( $db, 'enrolment', 'enrolment_id')
            ->field(
            Field::inst( 'student.first_name' ),
            Field::inst( 'student.last_name' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'student.phone' ),
            Field::inst( 'student.phone_2' ),
            Field::inst( 'student.email' ),
            Field::inst( 'student.last_updated' ),
            Field::inst( 'student.language' )
        ->getFormatter( 'Format::explode' )
        ->setFormatter( 'Format::implode' ),
            Field::inst( 'student.address' ),
            Field::inst( 'student.postcode' ),
            Field::inst( 'student.city' ),
            Field::inst( 'student.state' ),
            Field::inst( 'student.country' ),
            Field::inst( 'student.born' ),
            Field::inst( 'student.sex' ),
            Field::inst( 'student.status' ),
            Field::inst( 'student.email_language' ),
            Field::inst( 'student.occupation' ),
            Field::inst( 'student.student_info' ),
            Field::inst( 'enrolment.enrolment_id' ),
            Field::inst( 'enrolment.course_id' )
            ->options( 'course', 'course_id', 'course_name_english' )
            ->validator( 'Validate::dbValues' ),
            Field::inst( 'enrolment.student_id' )
            ->options( 'student', 'student_id' )
            ->validator( 'Validate::dbValues' ),
            Field::inst( 'enrolment.rebate' ),
            Field::inst( 'enrolment.enroldate' ),
            Field::inst( 'enrolment.currency' ),
            Field::inst( 'enrolment.rebate_documentation' ),
            Field::inst( 'enrolment.room' ),
            Field::inst( 'enrolment.beddings' ),
            Field::inst( 'enrolment.extra_fee' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.extra_fee_name' ),
            Field::inst( 'enrolment.extra_rebate' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.extra_rebate_name' ),
            Field::inst( 'enrolment.health_cert' ),
            Field::inst( 'enrolment.transport' ),
            Field::inst( 'enrolment.transport_place' ),
            Field::inst( 'enrolment.transport_time' ),
            Field::inst( 'enrolment.payment_1' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.payment_1_date' ),
            Field::inst( 'enrolment.payment_1_type' ),
            Field::inst( 'enrolment.payment_2' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.payment_2_date' ),
            Field::inst( 'enrolment.payment_2_type' ),
            Field::inst( 'enrolment.payment_3' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.payment_3_date' ),
            Field::inst( 'enrolment.payment_3_type' ),
            Field::inst( 'enrolment.payment_4' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.payment_4_date' ),
            Field::inst( 'enrolment.payment_4_type' ),
            Field::inst( 'enrolment.payment_5' )->validator( 'Validate::numeric' ),
            Field::inst( 'enrolment.payment_5_date' ),
            Field::inst( 'enrolment.payment_5_type' ),
            Field::inst( 'enrolment.enrolment_info' ),
            Field::inst( 'enrolment.email_sent' ),
            Field::inst( 'enrolment.cancel' ),
            Field::inst( 'course.sek_full_price' ),
            Field::inst( 'course.sek_deposit' ),
            Field::inst( 'course.sek_deposit_2' ),
            Field::inst( 'course.sek_single_room' ),
            Field::inst( 'course.sek_double_room' ),
            Field::inst( 'course.sek_beddings' ),
            Field::inst( 'course.sek_rebate_price' ),
            Field::inst( 'course.eur_full_price' ),
            Field::inst( 'course.eur_deposit' ),
            Field::inst( 'course.eur_deposit_2' ),
            Field::inst( 'course.eur_single_room' ),
            Field::inst( 'course.eur_double_room' ),
            Field::inst( 'course.eur_beddings' ),
            Field::inst( 'course.eur_rebate_price' ),
            Field::inst( 'course.start_date' )
            )
            ->leftJoin( 'student', 'student.student_id', '=', 'enrolment.student_id' )
            ->leftJoin( 'course', 'course.course_id', '=', 'enrolment.course_id' )
            ->where( 'student.student_id', $_POST['student_id'] )
            ->process($_POST)
            ->json();
    }
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    Let's assume you have student as the parent table. You select one student to display the student's enrolments. When you open the enrolments Editor in this case you want to assign further courses to the student. You do this with an options instance in which you display a list to select one (or more) courses to be assigned to the student. In the enrolments Editor you can also enter all the other enrolements attributes.

    Likewise you can have courses as the parent table. You select one course to display the course's students. In this case enrolments Editor is used to assign further students to the respective cours etc. etc.

    In this example I have "user" as the parent table and I have a table called "creditor_has_user" used to assign a creditor to that user and also to add an attribute which is the user's role for that creditor (but of course you can add as many attributes as you like). So you have "user" and "creditor" and "creditor_has_user" as the link between the two.

    This is the editor instance for the link table

    if ( ! isset($_POST['user']) || ! is_numeric($_POST['user']) ) {
        echo json_encode( [ "data" => [] ] );
    } else {
        if ($lang === 'de') {     
            $msg[0] = 'Feld darf nicht leer sein.';
        } else {
            $msg[0] = 'Field may not be empty.';
        }
        Editor::inst( $db, 'creditor_has_user' )
        ->field(
            Field::inst( 'creditor_has_user.user_id' )->set(Field::SET_CREATE),
            Field::inst( 'creditor_has_user.creditor_id' )
                ->options( Options::inst()
                        ->table('creditor')
                        ->value('id')
                        ->label( array('name', 'BIC') )
                        ->render( function ( $row ) {
                                    return $row['name'].' ('.$row['BIC'].')';
                                    } )
                        ->where( function($q) {
                            if (isset($_SESSION['masterDataId']) ) {
                                $q ->where( 'id', 
                                            '( SELECT DISTINCT creditor_id FROM creditor_has_user     
                                               WHERE user_id = :id AND role IN ("Administrator", "Principal" ) 
                                               ORDER BY creditor_id ASC  
                                               )', 'IN', false);
                                $q->bind( ':id', $_SESSION['masterDataId'] );
                            }
                        } )
                    ),
            Field::inst( 'creditor_has_user.role' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
            Field::inst( 'creditor.name' ),
            Field::inst( 'creditor.BIC' )
        )
        ->leftJoin( 'user', 'user.id', '=', 'creditor_has_user.user_id' )
        ->leftJoin( 'creditor', 'creditor.id', '=', 'creditor_has_user.creditor_id' )
        ->where( function ( $q ) {
            $q  ->where( 'creditor_has_user.user_id', $_POST['user'] );
            $q  ->where( 'user.type', 'C' );
            if (isset($_SESSION['masterDataId']) ) {
                $q ->where( 'creditor_has_user.creditor_id', 
                            '( SELECT DISTINCT creditor_id FROM creditor_has_user     
                               WHERE user_id = :id AND role IN ("Administrator", "Principal" )  
                               ORDER BY creditor_id ASC  
                               )', 'IN', false);
                $q->bind( ':id', $_SESSION['masterDataId'] );
            }
        } )                    
    
    
        ->on('preCreate', function ( $editor, $values ) {
            // no acces to $id inside the function - need to access global array again
            $userUser = filter_var($_POST['user']);
            $editor            
                ->field('creditor_has_user.user_id')
                ->setValue($userUser);
        })
        ->on( 'postCreate', function ( $editor, $id, $values, $row ) {
            logChange( $editor->db(), 'create', $id, $row, 'creditor_has_user' );
            //set these session variable to be able to send the pwLink
            $_SESSION['sendPwLinkId'] = filter_var($_POST['user']);
        } )
        ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
            logChange( $editor->db(), 'edit', $id, $row, 'creditor_has_user' );
        } )
        ->on( 'postRemove', function ( $editor, $id, $values ) {
            logChange( $editor->db(), 'delete', $id, $values, 'creditor_has_user' );
        } )
        ->process($_POST)
        ->json();
    

    By the way if you had a true link table consisting of only two columns (i.e. the ids of the two tables) Editor offers additional functionality. In this case you can edit both tables simultaneously and Editor will automatically maintain the link table. That does NOT work if your link table has its own id and addtional attributes (i.e. if it has more than the two keys from the tables to be linked.).

    Here is an example for this.
    I have three tables "contract", "underlying" and the connnection "derivative_has_underlying". Underlying is only a view of contract - I am linking the table with itself. "derivative_has_underlying" only has two columns: "derivative_contract_id" and "underlying_contract_id".

    In this case you can edit "contract" and - through the mjoin instance - edit "underlying" as well while the link table is updated automatically because it only has the two links and nothing else.

    If your link table is more than just the link (two foreign keys) and has its own id this is NOT possible. Your Editor instance must be for the link table in that case and you can only link to the other tables from that link table (i.e. from enrolments you can link to students and to courses). In your example you cannot edit "courses" with an Editor instance for "students" because your link table "enrolments" isn't a true link table but has its own id and contains additional attributes. That means that with your data model you need to put the link table (enrolments) "in focus" if you want to manage your enrolments. It cannot be done from the students editor directly because you aren't able to link to the courses from there. Likewise you can't do it from the courses editor because you can't link to the students from there.

    But here is the Mjoin example for a pure link table:

    ->join(
    Mjoin::inst( 'underlying' )
        ->link( 'contract.id', 'derivative_has_underlying.derivative_contract_id' )
        ->link( 'underlying.id', 'derivative_has_underlying.underlying_contract_id' )
        ->order( 'serial, instrument, type asc' )
        ->fields(
            Field::inst( 'id' )->set( false )
                ->options( Options::inst()
                    ->table( 'underlying' )
                    ->value( 'id' )
                    ->label( array('serial', 'instrument', 'type', 'number') )
                //render serial, instrument, type, number
                    ->render( function ( $row ) {               
                        return '# '.$row['serial']
                                .' / '.renderInstrument($row['instrument'])
                                .' / '.renderType($row['type'])
                                .' ('.$row['number'].')';
                    } )
                    ->order( 'serial, instrument, type asc' )
                    //where clause MUST be a closure function in Options!!!
                    ->where( function($q) {
                        $q ->where( function($r) {
                            $r ->where('govdept_id', $_SESSION['govdept_id'] );
                            $r ->where('instrument', 'Z', '<' );
                        });
                    } )
                ),
            Field::inst( 'serial' )->set( false ),
            Field::inst( 'instrument' )->set( false ),    
            Field::inst( 'type' )->set( false ),
            Field::inst( 'number' )->set( false )
        )
    )                
    
  • shantiramshantiram Posts: 13Questions: 5Answers: 2
    edited July 2017

    Ok, thanks a lot. So basically I have to either work around this limitation or structure my db differently?

    One thing that puzzles me though is that in one scenario it works.
    I have a small function that allows you to select one row from the "new enrolments" table and one row from the students table and "merge" them, i.e. update old entry in students with info from new entry in "new enrolments", and then move the enrolment from the new entry to the old entry. (This is used for students who enrol several times.)
    In the code for this (part of it below) fields from both sides of the join are edited (enrolment.student_id, student.status).
    This seems to work perfectly.

        //Transfer enrolment to old student on update
        studentsEditor.on('preSubmit', function (e, mode, action) {
    
            if (karto.tables.newEnrolments.rows({
                    selected: true
                }).any()) {
                if (courseExists) {
                    var currentStudentID = karto.tables.students.row({
                        selected: true
                    }).id();
                    newEnrolmentsEditor
                        .edit(karto.tables.newEnrolments.row({
                            selected: true
                        }).index(), false)
                        //set enrolment.student_id to student_id of selected entry in students table
                        .set('enrolment.student_id', currentStudentID)
                        //remove the the student from newEnrolmentsTable                  .set('student.status', 'Trash')
                        .submit();
    
                }
            }
    
        });
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    I can't comment on that one, I am afraid.

    But here is a link in which Allan (the author of Editor and Data Tables) commented on e.g. compound keys in Mjoins (not supported): https://datatables.net/forums/discussion/43446
    and left joins on columns that aren't the primary key (not supported):
    https://datatables.net/forums/discussion/42199/bug-editor-ignores-where-clause-in-leftjoin-update#latest

    I found a couple of cases where I could not get everything done with Editor. But I have always been able to integrate work arounds.

    Sometimes I had to insert the link table myself upon create (I use delete cascade so no worries about the deletes). Or I have really complex options that don't work with the options instance. Here is an example for both. Good luck!

    Editor::inst( $db, 'cashflow' )
    ->field(
        Field::inst( 'cashflow.id' )->set( false ),
        Field::inst( 'cashflow.contract_id' )  ->set(Field::SET_CREATE)
                                               ->setValue( $_POST['contract_id'] ),
        Field::inst( 'cashflow.id AS cashFlowElement' )->set( false )
            //$val = modified element with F or V prefix /
            //       id of the respective fixed or variable record /
            //       'fixed' or 'variable'                
            //      e.g. "FL/20/fixed" or "VX1P/25/variable"
                ->getFormatter( function($val, $data, $opts) {
                    if ( $data['cashflow.id'] > 0 ) {
                        return getFormatterCashFlowElement($data['cashflow.id']);
                    } else {
                        return '';
                    }
                }),                         
        Field::inst( 'cashflow.start_date' )
                ->getFormatter( function ( $val, $data, $opts ) {
                    return getFormatterDate($val);                 
                } )
                ->setFormatter( function ( $val, $data, $opts ) {
                    return setFormatterDate($val);
                } ),
        Field::inst( 'cashflow.end_date' )
                ->validator( function ( $val, $data, $opts ) {
                    return validatorEndDate($data['cashflow'], $val);
                } )
                ->getFormatter( function ( $val, $data, $opts ) {
                    return getFormatterDate($val);                     
                } )
                ->setFormatter( function ( $val, $data, $opts ) {
                    return setFormatterDate($val);
                } ),
        Field::inst( 'cashflow.amount_remaining' )
            ->validator( function ( $val, $data, $opts ) {
                return validatorAmount($data['cashflow'], $val);
            } )
            ->getFormatter( function($val, $data, $opts) {
                return getFormatterAmount($val);
            })
            ->setFormatter( function($val, $data, $opts) {
                $elementArray = explode('/', $data['cashFlowElement']);
                $element = substr($elementArray[0], 1);
                return setFormatterAmount($val, 'pcAmount', $element);
            }),
        Field::inst( 'variable.currency' )->set( false ),
        Field::inst( 'fixed.currency AS cashFlowCurrency' )->set( false )
            //either the variable or the fixed currency are filled!
                ->getFormatter( function($val, $data, $opts) {
                    if ($data['variable.currency'] <= '') {
                        return $val;
                    } else {
                        return $data['variable.currency'];
                    }
                }),
        Field::inst( 'cashflow.rate' )
            ->validator( function ( $val, $data, $opts ) {
                return validatorRate($data['cashflow'], $val);
            } )
            ->getFormatter( function($val, $data, $opts) {
                return getFormatterRate($val);
            })
            ->setFormatter( function($val, $data, $opts) {
                return setFormatterRate($val);
            }),
        Field::inst( 'cashflow.final' ),
        Field::inst( 'cashflow.repayment' )
            ->getFormatter( function($val, $data, $opts) {
                return getFormatterAmount($val);
            })
            ->setFormatter( function($val, $data, $opts) {
                $elementArray = explode('/', $data['cashFlowElement']);
                $element = substr($elementArray[0], 1);
                return setFormatterAmount($val, 'pcAmount', $element);
            }),            
        Field::inst( 'cashflow.real_repayment' )
            ->setFormatter( function($val, $data, $opts) {
                $elementArray = explode('/', $data['cashFlowElement']);
                $element = substr($elementArray[0], 1);
                if ( substr($element, 0, 1) === 'L' ||  //loans also with cap floor collar
                     substr($element, 0, 1) === 'W' ||  //deposits also with cap floor collar
                     substr($element, 0, 2) === 'X1'   )  {  //cross currency swaps
                    return 1;
                } else {
                    return 0;
                }
            }),                  
        Field::inst( 'cashflow.interest_fee' )
            ->getFormatter( function($val, $data, $opts) {
                return getFormatterAmount($val);
            })
            ->setFormatter( function($val, $data, $opts) {
                $elementArray = explode('/', $data['cashFlowElement']);
                $element = substr($elementArray[0], 1);
                return setFormatterAmount($val, 'pcAmount', $element, $data['cashflow']);
            }),
        Field::inst( 'cashflow.interest_fee AS cashflowTotalAmount' )->set (false)
            ->getFormatter( function($val, $data, $opts) {
                $val = $val + $data['cashflow.repayment'];
                return getFormatterAmount($val);
            }),
        Field::inst( 'cashflow.is_fee' ),
        Field::inst( 'cashflow.manual' ),
        Field::inst( 'cashflow.update_time' )->set(Field::SET_BOTH)
                                             ->setValue( mySqlTimestamp() ),
        Field::inst( 'cashflow.updater_id' )->set(Field::SET_BOTH)
                                            ->setValue( $_SESSION['id'] ),
        Field::inst( 'cashflow.creator_id' )->set(Field::SET_CREATE)
                                            ->setValue( $_SESSION['id'] )
    )
    ->leftJoin( 'fixed_has_cashflow', 'cashflow.id', '=', 'fixed_has_cashflow.cashflow_id')
    ->leftJoin( 'fixed', 'fixed_has_cashflow.fixed_id', '=', 'fixed.id')                
    ->leftJoin( 'variable_has_cashflow', 'cashflow.id', '=', 'variable_has_cashflow.cashflow_id')
    ->leftJoin( 'variable', 'variable_has_cashflow.variable_id', '=', 'variable.id')         
    ->where( 'cashflow.contract_id', $_POST['contract_id'] )
    ->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
        logChange( $editor->db(), 'create', $id, $row, 'cashflow' );
    //cashFlowElement = modified element with F or V prefix /
    //       id of the respective fixed or variable record /
    //       'fixed' or 'variable'                
    //      e.g. "FL/20/fixed" or "VX1P/25/variable"
        $elementArray = explode('/', $values['cashFlowElement']);
        $_SESSION['linkTableParentId'] = (int)$elementArray[1];
        $table = $elementArray[2] . '_has_cashflow';
        insertLinkTable( $editor->db(), $id, $table );
    } )
    ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), 'edit', $id, $row, 'cashflow' );
    } )
    ->on( 'postRemove', function ( $editor, $id, $values ) {
        logChange( $editor->db(), 'delete', $id, $values, 'cashflow' );
    } )
    ->process($_POST)
    ->json();
    
    function getFormatterCashFlowElement($CashFlowId) {
        //value to be returned = modified element with F or V prefix /
        //       id of the respective fixed or variable record /
        //       'fixed' or 'variable'                
        //      e.g. "FL/20/fixed" or "VX1P/25/variable"
        global $dbh;
        $dbh->query('SELECT CONCAT("F", fixed.element) AS `option`,  
                            fixed.id AS `id`,  
                            "fixed" AS `table`  
                       FROM fixed, fixed_has_cashflow  
                      WHERE fixed_has_cashflow.cashflow_id = :cashflow_id  
                        AND fixed_has_cashflow.fixed_id = fixed.id  
                        AND fixed.end_date IS NOT NULL  
                  UNION ALL  
                     SELECT CONCAT("V", variable.element) AS `option`,  
                            variable.id AS `id`,  
                            "variable" as `table`  
                       FROM variable, variable_has_cashflow  
                      WHERE variable_has_cashflow.cashflow_id = :cashflow_id  
                        AND variable_has_cashflow.variable_id = variable.id  
                        AND variable.end_date IS NOT NULL  
                      LIMIT 1'); 
        $dbh->bind(':cashflow_id', $CashFlowId);
        $row = $dbh->singleAssoc(); // a one-dimensional array is returned SINGLE
        (int)$rowCount = $dbh->rowCount();
        if ((bool)$rowCount ) {
            return $row["option"] . '/' . $row["id"] . '/' . $row["table"];
        } else {
            return '';
        }
    }
    
  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓
    function insertLinkTable ( $db, $id, $table ) {
        switch ($table) {
            case "proposal_has_fixed":
                $db->insert( $table, array(
                    'proposal_id'   => $_SESSION['linkTableParentId'],
                    'fixed_id'      => $id
                ) );
                break; 
            case "proposal_has_variable":
                $db->insert( $table, array(
                    'proposal_id'   => $_SESSION['linkTableParentId'],
                    'variable_id'   => $id
                ) );
                break;
            case "contract_has_fixed":
                $db->insert( $table, array(
                    'contract_id'   => $_SESSION['linkTableParentId'],
                    'fixed_id'      => $id
                ) );
                break; 
            case "contract_has_variable":
                $db->insert( $table, array(
                    'contract_id'   => $_SESSION['linkTableParentId'],
                    'variable_id'   => $id
                ) );
                break;
            case "fixed_has_cashflow":
                $db->insert( $table, array(
                    'fixed_id'      => $_SESSION['linkTableParentId'],
                    'cashflow_id'   => $id
                ) );
                break; 
            case "variable_has_cashflow":
                $db->insert( $table, array(
                    'variable_id'   => $_SESSION['linkTableParentId'],
                    'cashflow_id'   => $id
                ) );
                break;
        }
    }
    
  • naspersgaspnaspersgasp Posts: 53Questions: 14Answers: 1

    Hi,

    Good day.

    Thanks for this. Really heped me a lot! Bit more code than I wanted to write, but at least there's a workaround.

    Regards.

This discussion has been closed.