New Idea, Editor calls an Editor. seeking best-practices advice

rpmccormick · September 21

I have lots of tables. Often when you update one table, you need to update another (or even the same, different field).

Here is an example of such an "automation"...

if DTEditor changed Housekeeping to Clean, also set Overview to Available (but only if it was previously Not Ready):

      //Now see if we need to adjust any other status
      //  Check if RoomStatus_Housekeeping was updated
      $Editor->on('process', function ($action, $id, $data, $response) 
       {if ($action === Editor::ACTION_EDIT && isset($data['RoomStatus_Housekeeping'])) 
         { $hid = $data['Hotel_ID'];
          $grid = $data['GuestRoom_ID'];
          $stat = $data['RoomStatus_Housekeeping'];
          $stid = $id;

          // Update Overview status based on Housekeeping status
          if ($stat == 'Clean') 
           {$sql = "UPDATE Hotel_GuestRoom_Status 
                    SET RoomStatus_Overview='Available'
                    WHERE Hotel_ID=:hotel 
                      AND RoomStatus_ID!=:stid
                      AND GuestRoom_ID=:room
                      AND RoomStatus_Overview='Not Ready'
                      AND RoomStatus_Maintenance IN ('No Issues', 'None', 'NONE')
                  ;";
           }
          else

Now I want to do the following:

If a line-item price is updated, update charge total
if a charge is updated, update roomstay total
if a roomstay is updated, update booking total

Therefore... if a line-item is updated, it will cascade and update the charge, roomstay, and booking.

QUESTION: Rather than having a change to the line-item update 3 tables, my idea is to have it call a different DTEditor php to edit the Charge... and it will call a different DTEditor php to update the RoomStay, which will call the Booking. Is this possible / advisable? ...or do you think I should just update my 3 tables, 2 tables, 1 table in each editor?

If it is a good idea, I wouldn't mind being spoon fed how to have an editor call another php and pass it variables (along with what variables to pass to the 2nd editor to get it to think it was a standard datatable edit and process my update).

Would I have to / want to use the PHP output buffer to have the php call another php?

Thanks for any input on this idea.

rpmccormick · September 21

PS: Happy to take advice on the code-snippet I provided as well... I'm sure there is probably an easier way I could be updating a different field of the same table using your DB connection instead of executing my own SQL.

EDIT: Only problem is I immediately update a different table right after that, so it I already need my own SQL for that, it is no big deal to do it the way I am... but, if I could chain a bunch of editors together for automations, I would not need my own SQL at all.

allan · September 21

and it will call a different DTEditor php to update the RoomStay, which will call the Booking. Is this possible / advisable?

Yes it is possible. You just need to craft the data that is passed into Editor->process() to make it do the action you want. The data format is here.

Is it advisable? I've only done it once before myself and it worked fine, but it very much depends on what you feel happiest maintaining. I don't know that it really gains you anything over an SQL update - I don't think the code would be any shorter for example - perhaps more modular since you could do a class or file per table, but I'd say it is personal preference. Normally I just do what you've done and do a specific action in SQL.

Allan

rpmccormick · September 22

Ok cool, I'll just do it the straightforward way, with some minimal code duplication. I gotta stop trying to be so tricky

@Allan if you see this and have time, you may want to check out my HD-PMS again. I tried to upgrade things but ran into too many issues and gave up. BUT, I wanted to get rid of the + and - so I went to hacking your responsive css file.

Not only did I do some cool things with the control icon, I also added some animations that I just love!

(mid slide-down, arrow is mid-rotation)

(end of loading, shows left arrow only for 0.5s, then right-reveal for 0.5s)

Here is the css

/* Custom Edited with new icons instead of + / - and animations */
  @keyframes open-right {  0% {clip-path: inset(0 calc(100% - 24px) 0 0);}
                          75% {clip-path: inset(0 calc(100% - 24px) 0 0);}
                         100% {clip-path: inset(0 0 0 0);  }
                        }
  @keyframes open-down  {  0% {clip-path: inset(100% 0 0 0); transform: translateY(-100%);}
                          90% {clip-path: inset(0    0 0 0); transform: translateY(  3px);}
                         100% {clip-path: inset(0    0 0 0); transform: translateY(  0  );}
                        }
  @keyframes rotate-cw  {from {transform: rotate( 0deg);}
                           to {transform: rotate(90deg);}
                        }

table.dataTable.dtr-inline.collapsed > tbody > tr[role="row"] > td:first-child:before,
table.dataTable.dtr-inline.collapsed > tbody > tr[role="row"] > th:first-child:before {
  /*top: 9px;  
  *** Other than top, below is unchanged ***/
  top: calc(50% - 1px);
  left: 4px;
  height: 14px;
  width: 14px;
  display: block;
  position: absolute;
  box-sizing: content-box;
  text-align: center;
  text-indent: 0 !important;
  line-height: 14px;
  /*color: white;
  background-color: #31b131;
  font-family: 'Courier New', Courier, monospace;
  border: 2px solid white;
  border-radius: 14px;
  box-shadow: 0 0 3px #444;
  *** Below were added ***/
  color: #999;
  transform: translateY(-50%);
  content: '❯';
  /*content was +/-, but ascii is better: ⦈ ⦘ » ❭ ⧽ ᚜ ⦒ ❯ ❱ 》 〉 ⧐ ⎬ ⁅  ➺ ᐅ ▷ ➽ ➲ ➫ ➪ ➩ ► ➤ */
 }
table.dataTable.dtr-inline.collapsed > tbody > tr.parent > td:first-child:before,
table.dataTable.dtr-inline.collapsed > tbody > tr.parent > th:first-child:before {
  /*background-color: #d33333;
  content: '▼';  
  *** Animate existing content! ***/
  margin-top:-7px;
  animation: rotate-cw 0.5s forwards;
}

table.dataTable > tbody            {             background-color: #eef;                             }
table.dataTable > tbody > tr       {                    animation: open-right 0.8s ease-out forwards;}
table.dataTable > tbody > tr.child {padding: 0.5em 1em; animation: open-down  0.6s ease-out forwards;}
/* End Custom Edits */

Note: I already use #eef for empty tables all over the place like this:

$('#tblRoomNotes .dataTables_empty').text('No Related Notes').css('background-color', '#eef');

...so having the white/blue background with the all-white arrows for a bit, before swiping open the rows, looks really cool (and brings attention to the arrows). And if the table is empty, you don't even notice the swipe because it is the same background.

rpmccormick · September 22

allan · September 26

Sorry for the delayed reply. I've just had a look and yeah, that looks really awesome. Nice one!

Allan

rpmccormick · September 29

Another bonus to the animation, is that if you re-sort a paged table, the animations only occur on rows that were not previously viewable. It is a cool feature to see that extra info. I did have an issue though with a drop-down button in a table cell dropping behind the next row. I could only fix it by removing the animation... so thats what I do automatically after the animation is over:

                  rowCallback: function (row, data) {setTimeout(function(){endAnimation(row);}, 1500);}; //pass in row for targeted!

  //Remove animations after they are complete, or DropDown button with pop-under everything and be hidden.
  function endAnimation(row)  //Pass in row for targeted?
   {if (row) {                 $(row).css({'animation':'none', 'clip-path':'none'});}
    else     {$('table > tbody > tr').css({'animation':'none', 'clip-path':'none'});}
   }
  //Usage: setTimeout(endAnimation, 1500);   (or endAnimatio({tr-target}); )

Also, with help from Cody AI, I finally completed what this OP was all about, and I will share my solution here for anyone trying to do the same (make other table edits, when editor is used to edit specific fields). I opted not to call sub-editors, but I did learn how to use DT's db connection...

<?php //Load DataTables, plus $DateFormat and $Now variables.
      include HOST_FRAMEWORK.'/api/dt/incGlobal.php';
      use DataTables\Editor,
          DataTables\Editor\Field,
          DataTables\Editor\Format,
          DataTables\Editor\Validate;

      //Main Table
      $tablename = 'Hotel_Charge_LineItems';
      $tablecode = 'Charge_LineItem_';
      $Editor = Editor::inst($db, $tablename, $tablecode.'ID')
         ->field(Field::inst($tablecode.'ID')       ->set(false),
                 Field::inst($tablecode.'Created')  ->set(false) ->getFormatter('GMT2CST'),
                 Field::inst($tablecode.'Modified') ->set(false) ->getFormatter('GMT2CST'),
                 Field::inst($tablecode.'Number')      ->validator('Validate::notEmpty'),
                 Field::inst($tablecode.'Name')        ->validator('Validate::notEmpty'),
                 Field::inst($tablecode.'Description') ->validator('Validate::notEmpty'),
                 Field::inst($tablecode.'Details')     ->validator('Validate::notEmpty'),
                 Field::inst($tablecode.'UnitCost')    ->validator('Validate::notEmpty'),
                 Field::inst($tablecode.'UnitType')    ->validator('Validate::notEmpty'),
                 Field::inst($tablecode.'QTY')         ->validator('Validate::notEmpty'),
                 Field::inst('Charge_ID'),
                 Field::inst('Ref_ID'),
                 Field::inst('Hotel_ID')            ->set(Field::SET_CREATE)
                );

      //If new row, set hotel id from SESSION hotel-id
      $Editor->on('preCreate', function ($ed, $vals) 
       {$ed->field('Hotel_ID')->setValue($_SESSION['hotel-id']);});

      //Save old values to compare later 
      $Editor->on('preEdit', function ($editor, $id, $values)
       {if ((isset($values['Charge_LineItem_UnitCost']))
         || (isset($values['Charge_LineItem_QTY'     ])))
         {$HotelID = $_SESSION['hotel-id'] ?? 0;
          $line = $editor->db()->query('select')
                                 ->get('Charge_LineItem_UnitCost')
                                 ->get('Charge_LineItem_QTY'     )
                               ->table('Hotel_Charge_LineItems'  )
                               ->where('Hotel_ID'          , $HotelID)
                               ->where('Charge_LineItem_ID',      $id)
                               ->exec()->fetch();
          if ($line) 
           {$GLOBALS['oldUnitCost'] = $line['Charge_LineItem_UnitCost'];
            $GLOBALS['oldQTY'     ] = $line['Charge_LineItem_QTY'     ];
       } } }     );

      //Update Other Tables
      $Editor->on('postEdit', function ($editor, $id, &$values, &$row) 
       {if ((isset($GLOBALS['oldUnitCost']) && ($GLOBALS['oldUnitCost'] != $row['Charge_LineItem_UnitCost']))
         || (isset($GLOBALS['oldQTY'     ]) && ($GLOBALS['oldQTY'     ] != $row['Charge_LineItem_QTY'     ])))
         {
        try {$subtotal = 0;
             $taxtotal = 0;
                $total = 0;
              $HotelID =       $_SESSION['hotel-id'] ?? 0;
                $RefID = $row[             'Ref_ID'] ?? 0;
             $ChargeID = $row[          'Charge_ID'] ?? 0;
             // Get All Charge Line Items
             $line = $editor->db()->query('select')
                                    ->get('Charge_LineItem_Name')
                                    ->get('Charge_LineItem_UnitCost')
                                    ->get('Charge_LineItem_QTY'     )
                                  ->table('Hotel_Charge_LineItems'  )
                                  ->where('Hotel_ID' ,  $HotelID)
                                  ->where('Charge_ID', $ChargeID)
                                  ->exec()->fetchAll();
             // Calculate Totals
             foreach ($line as $item) 
              {$amount = $item['Charge_LineItem_UnitCost'] * $item['Charge_LineItem_QTY'];
               if ($item['Charge_LineItem_Name'] == ' * ') {$taxtotal += $amount;}
               else                                        {$subtotal += $amount;}
              }
             // Check if the charge was tax-exempt and set total accordingly (also capture Booking_ID)
             $ogCharge = $editor->db()->query('select')
                                        ->get('HotelReservation_ID'   )
                                        ->get(          'Charge_Type' )
                                        ->get('Charge_AmountBeforeTax')
                                        ->get('Charge_AmountAfterTax' )
                                      ->table('Hotel_Charges'         )
                                      ->where('Hotel_ID' ,  $HotelID)
                                      ->where('Charge_ID', $ChargeID)
                                      ->exec()->fetch();
             $BookID     = $ogCharge['HotelReservation_ID'] ?? 0;
             $ChargeType = $ogCharge[        'Charge_Type'] ??'';

             if ($ogCharge['Charge_AmountBeforeTax'] == $ogCharge['Charge_AmountAfterTax'])
              {$total = $subtotal;} else {$total = $subtotal+$taxtotal;} 
             // Update the Hotel_Charges (using DataTables db)
             $editor->db()->query('update')
                            ->set('Charge_AmountBeforeTax', $subtotal)
                            ->set('Charge_AmountOfTax'    , $taxtotal)
                            ->set('Charge_AmountAfterTax' ,    $total)
                          ->table('Hotel_Charges')
                          ->where('Hotel_ID' ,  $HotelID)
                          ->where('Charge_ID', $ChargeID)
                           ->exec();
             // Calculate Totals of all Charges for the HotelReservation
             $totals = $editor->db()->query('select')
                                        ->get('SUM(Charge_AmountBeforeTax) as TotalBeforeTax')
                                        ->get(    'SUM(Charge_AmountOfTax) as TotalOfTax'    )
                                        ->get( 'SUM(Charge_AmountAfterTax) as TotalAfterTax' )
                                      ->table('Hotel_Charges')
                                      ->where(           'Hotel_ID', $HotelID)
                                      ->where('HotelReservation_ID',  $BookID)
                                      ->exec()->fetch();
             $totalBeforeTax = $totals['TotalBeforeTax'] ?? 0;
             $totalOfTax     = $totals['TotalOfTax'    ] ?? 0;
             $totalAfterTax  = $totals['TotalAfterTax' ] ?? 0;
             // Update HotelReservation
             $editor->db()->query('update')
                            ->set('HotelReservation_Total_AmountBeforeTax', $totalBeforeTax)
                            ->set('HotelReservation_Total_AmountOfTax'    , $totalOfTax    )
                            ->set('HotelReservation_Total_AmountAfterTax' , $totalAfterTax )
                          ->table('Hotel_Reservations')
                          ->where(           'Hotel_ID', $HotelID)
                          ->where('HotelReservation_ID',  $BookID)
                           ->exec();
             // Update RoomStay if applicable
             if (($ChargeType == 'RoomStay') && ($RefID > 0)) 
              {$StayID = $RefID;
               // Update Hotel_RoomStays
               $editor->db()->query('update')
                              ->set('RoomStay_Total_AmountBeforeTax', $subtotal)
                              ->set('RoomStay_Total_AmountOfTax'    , $taxtotal)
                              ->set('RoomStay_Total_AmountAfterTax' ,    $total)
                            ->table('Hotel_RoomStays')
                            ->where(   'Hotel_ID', $HotelID)
                            ->where('RoomStay_ID',  $StayID)
                             ->exec();

            } }
        catch (\Exception $e) {}
          }
       }         );

      // Run it
      $Editor->transaction(false)->process($_POST)->json();

Very nice system Allan. Now I can do tons of "automation updates" on all my editors.

rpmccormick · September 29

Here is another example:

      $Editor = Editor::inst($db, $tablename, $tablecode.'ID')
         ->field(Field::inst($tablecode.'ID')       ->set(false),
                 Field::inst($tablecode.'Created')  ->set(false) ->getFormatter('GMT2CST'),
                 Field::inst($tablecode.'Modified') ->set(false) ->getFormatter('GMT2CST'),
                 Field::inst($tablecode.'TimeSpan_Start')        ->getFormatter('GMT2CST'),
                 Field::inst($tablecode.'TimeSpan_End')          ->getFormatter('GMT2CST'),
                 Field::inst($tablecode.'AvgPerNight')           ,
                 Field::inst($tablecode.'Total_AmountBeforeTax') ,
                 Field::inst($tablecode.'Total_AmountAfterTax')  ,
                 Field::inst($tablecode.'Total_AmountOfTax')     ,
                 Field::inst($tablecode.'Total_CurrencyCode')    ,
                 Field::inst($tablecode.'NumAdult')              ,
                 Field::inst($tablecode.'NumChild')              ,
                 Field::inst($tablecode.'NumPet')                ,
                 Field::inst($tablecode.'PetType')               ,
                 Field::inst($tablecode.'NumCar')                ,
                 Field::inst('RoomType_ID')                      ->set(false),
                 Field::inst('HotelReservation_ID')              ->set(false),
                 Field::inst('Hotel_ID')            ->set(Field::SET_CREATE)
                );
       
      //If new row, set hotel id from SESSION hotel-id
      $Editor->on('preCreate', function ($ed, $vals) 
       {$ed->field('Hotel_ID')->setValue($_SESSION['hotel-id']);});


      //Save old values to compare later 
      $Editor->on('preEdit', function ($editor, $id, $values)
       {if ((isset($values['RoomStay_Total_AmountBeforeTax']))
         || (isset($values['RoomStay_Total_AmountAfterTax' ]))
         || (isset($values['RoomStay_Total_AmountOfTax'    ]))
         || (isset($values['RoomStay_NumAdult']))
         || (isset($values['RoomStay_NumChild']))
         || (isset($values['RoomStay_NumPet'  ]))
         || (isset($values['RoomStay_Status'])))
         {$HotelID  = $_SESSION['hotel-id'] ?? 0;
          $stay = $editor->db()->query('select')
                                 ->get('RoomStay_Total_AmountBeforeTax')
                                 ->get('RoomStay_Total_AmountAfterTax' )
                                 ->get('RoomStay_Total_AmountOfTax'    )
                                 ->get('RoomStay_Status'               )
                               ->table('Hotel_RoomStays'               )
                               ->where('Hotel_ID'  , $HotelID)
                               ->where('RoomStay_ID', $id    )
                               ->exec()->fetch();
          if ($stay) 
           {$GLOBALS['oldAmountBeforeTax'] = $stay['RoomStay_Total_AmountBeforeTax'];
            $GLOBALS['oldAmountAfterTax' ] = $stay['RoomStay_Total_AmountAfterTax' ];
            $GLOBALS['oldAmountOfTax'    ] = $stay['RoomStay_Total_AmountOfTax'    ];
            $GLOBALS['oldNumAdult'       ] = $stay['RoomStay_NumAdult'];
            $GLOBALS['oldNumChild'       ] = $stay['RoomStay_NumChild'];
            $GLOBALS['oldNumPet'         ] = $stay['RoomStay_NumPet'  ];
            $GLOBALS['oldStatus'         ] = $stay['RoomStay_Status'];
       } } }     );

      $Editor->on('postEdit', function ($editor, $id, &$values, &$row) 
       {try {$HotelID =       $_SESSION['hotel-id'] ?? 0;
             $BookID  = $row['HotelReservation_ID'] ?? 0;
             $StayID  = $id;
             // Check if RoomStay_Status has changed
             if (isset($GLOBALS['oldStatus']) && ($GLOBALS['oldStatus'] != $row['RoomStay_Status'])) 
              {// Check if the new RoomStay_Status is 'X', and set HotelReservation_Status
               if (in_array($values['RoomStay_Status'], ['Checked-Out', 'Past']))
                {$editor->db()->query('update')
                                ->set('HotelReservation_Status', 'Complete')
                              ->table('Hotel_Reservations')
                              ->where('HotelReservation_ID', $BookID)
                               ->exec();
                }
               else if (in_array($values['RoomStay_Status'], ['Checked-In', 'Staying', 'Leaving']))
                {$editor->db()->query('update')
                                ->set('HotelReservation_Status', 'Active')
                              ->table('Hotel_Reservations')
                              ->where('HotelReservation_ID', $BookID)
                               ->exec();
                }
               else if ($values['RoomStay_Status'] == 'Abandoned') 
                {$editor->db()->query('update')
                                ->set('HotelReservation_Status', 'Canceled')
                              ->table('Hotel_Reservations')
                              ->where('HotelReservation_ID', $BookID)
                               ->exec();
              } } 

             //Update PartySize
             if ((isset($GLOBALS['oldNumAdult']) && ($GLOBALS['oldNumAdult'] != $row['RoomStay_NumAdult']))
              || (isset($GLOBALS['oldNumChild']) && ($GLOBALS['oldNumChild'] != $row['RoomStay_NumChild']))
              || (isset($GLOBALS['oldNumPet'  ]) && ($GLOBALS['oldNumPet'  ] != $row['RoomStay_NumPet'  ])))
              {$newPartySize = $row['RoomStay_NumAdult'] + $row['RoomStay_NumChild'] + $row['RoomStay_NumPet'];
               $editor->db()->query('update')
                              ->set('HotelReservation_PartySize', $newPartySize)
                            ->table('Hotel_Reservations')
                            ->where(           'Hotel_ID', $HotelID)
                            ->where('HotelReservation_ID',  $BookID)
                            ->where('HotelReservation_PartySize', $newPartySize, '<')
                             ->exec();
              }

             //Update Money Changes
             if ((isset($GLOBALS['oldAmountBeforeTax']) && ($GLOBALS['oldAmountBeforeTax'] != $row['RoomStay_Total_AmountBeforeTax']))
              || (isset($GLOBALS['oldAmountAfterTax' ]) && ($GLOBALS['oldAmountAfterTax' ] != $row['RoomStay_Total_AmountAfterTax' ]))
              || (isset($GLOBALS['oldAmountOfTax'    ]) && ($GLOBALS['oldAmountOfTax'    ] != $row['RoomStay_Total_AmountOfTax'    ])))
              {// Find and update the corresponding Charge
               $charge = $editor->db()->query('select')
                                         ->get('Charge_ID')
                                       ->table('Hotel_Charges')
                                       ->where(           'Hotel_ID',  $HotelID )
                                       ->where('HotelReservation_ID',   $BookID )
                                       ->where(        'Charge_Type', 'RoomStay')
                                       ->where(             'Ref_ID',   $StayID )
                                       ->exec()->fetch();
               if ($charge) 
                {$ChargeID = $charge['Charge_ID'];
                 // Update the Charge
                 $editor->db()->query('update')
                                ->set('Charge_AmountBeforeTax', $row['RoomStay_Total_AmountBeforeTax'])
                                ->set('Charge_AmountOfTax'    , $row['RoomStay_Total_AmountOfTax'    ])
                                ->set('Charge_AmountAfterTax' , $row['RoomStay_Total_AmountAfterTax' ])
                              ->table('Hotel_Charges')
                              ->where('Charge_ID', $ChargeID)
                               ->exec();
                 // Calculate Totals of all Charges for the HotelReservation
                 $totals = $editor->db()->query('select')
                                           ->get('SUM(Charge_AmountBeforeTax) as TotalBeforeTax')
                                           ->get(    'SUM(Charge_AmountOfTax) as TotalOfTax'    )
                                           ->get( 'SUM(Charge_AmountAfterTax) as TotalAfterTax' )
                                         ->table('Hotel_Charges')
                                         ->where(           'Hotel_ID', $HotelID)
                                         ->where('HotelReservation_ID',  $BookID)
                                         ->exec()->fetch();
                 $totalBeforeTax = $totals['TotalBeforeTax'] ?? 0;
                 $totalOfTax     = $totals['TotalOfTax'    ] ?? 0;
                 $totalAfterTax  = $totals['TotalAfterTax' ] ?? 0;
                 // Update HotelReservation
                 $editor->db()->query('update')
                                ->set('HotelReservation_Total_AmountBeforeTax', $totalBeforeTax)
                                ->set('HotelReservation_Total_AmountOfTax'    , $totalOfTax    )
                                ->set('HotelReservation_Total_AmountAfterTax' , $totalAfterTax )
                              ->table('Hotel_Reservations')
                              ->where(           'Hotel_ID', $HotelID)
                              ->where('HotelReservation_ID',  $BookID)
                               ->exec();
            } } }
        catch (\Exception $e) {}
       }         );

      // Run it
      $Editor->transaction(false)->process($_POST)->json();

/* END...  do not add close php tag! */

I hope someone someday may find this post useful. I learn by example, and these show off a lot of DT power.

allan · September 30

Epic - nice one

Thanks for introducing me to Cody AI as well - I'll enjoy having a play with that later on.

Allan

rpmccormick · October 1

I keep it simple:

1) Go here: https://sourcegraph.com/cody/chat
2) Chat, and paste over some code.
3) Get help with all your syntax and repetitive task woes.
4) When switching topics, reload the page (and Cody's memory)
5) When not switching but it slowed to a crawl, ask it to summarize everything you did, then copy that, reload, and paste it back to "new Cody". Then continue.

Works a treat

New Idea, Editor calls an Editor. seeking best-practices advice

New Idea, Editor calls an Editor. seeking best-practices advice

Answers

Howdy, Stranger!

Categories

DataTables

New Idea, Editor calls an Editor. seeking best-practices advice

New Idea, Editor calls an Editor. seeking best-practices advice

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables