insert multiple lines in a subform.

Questions related to customising nuBuilder Forte with JavaScript or PHP.

Re: insert multiple lines in a subform.

Unread postby kev1n » Sat Jul 17, 2021 5:44 pm

I don't see an attachment. You might have to zip it.
kev1n
nuBuilder Team
 
Posts: 1996
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: insert multiple lines in a subform.

Unread postby johan » Sun Jul 18, 2021 4:01 am

Ik attachment.
Attachments
database.zip
(957 Bytes) Downloaded 14 times
johan
 
Posts: 340
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: insert multiple lines in a subform.

Unread postby kev1n » Sun Jul 18, 2021 5:24 pm

Could you also export your form + subform using the cloner (In v 4.5., it's already included).
It makes it so much easier than if I have to create the forms again on my side.
kev1n
nuBuilder Team
 
Posts: 1996
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: insert multiple lines in a subform.

Unread postby johan » Tue Jul 20, 2021 1:51 am

Kev1n
i've dumped my database and the forms.
I've made some adjustments.
Still have to rewrite my select 'lokalen' so user can't select a room that's already in use.

Johan
Attachments
nubuilder.zip
(173.64 KiB) Downloaded 15 times
johan
 
Posts: 340
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: insert multiple lines in a subform.

Unread postby kev1n » Wed Jul 21, 2021 3:10 pm

To get you started, here's a PHP Procedure to check for the selected dates and "lokaal" the availability

Code: Select all

// Fields of Main from
$actDateStart = "#act_start#";
$actDateEnd = "#act_eind#";
$actTimeStart = "#act_uur_start#";
$actTimeEnd = "#act_uur_end#";
$actLocation = "#act_lokaal#";

// Get all dates from start to end
$dates = dateRange($actDateStart, $actDateEnd);



$msg = "";

// Loop through dates
foreach ($dates as $date) {
   
   
   // Check in reservaties if there's already a row with the same date, "lookal" etc.
   $sql = "
      SELECT * FROM
         reservaties
      WHERE
         res_datum = :act_date AND
         res_lokaal = :act_location
         -- add other criterias here         
      
   ";
   
   // sql arguments
   $arg = array(
      "act_date" => $date,
      "act_location" => $actLocation      
   );

   $r = nuRunQuery($sql, $arg);   
   if (db_num_rows($r) != 0) {
      $mgs .= "Not available on $date";
   }
   
}

if ($msg != '') {
   nuDisplayError($msg);
} else {
   nuDisplayError("Free.");
}


function dateRange( $first, $last, $step = '+1 day', $format = 'Y-m-d' ) {
    $dates = [];
    $current = strtotime( $first );
    $last = strtotime( $last );

    while( $current <= $last ) {
        $dates[] = date( $format, $current );
        $current = strtotime( $step, $current );
    }

    return $dates;
}




Create a Procedure with Code "checkAvailability".

On your form, add a button with a JS event to trigger the procedure:
Code: Select all
nuRunPHPHidden('checkAvailability',0);
kev1n
nuBuilder Team
 
Posts: 1996
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: insert multiple lines in a subform.

Unread postby johan » Thu Jul 22, 2021 3:09 am

Kev1n
Thanks for that, that works but how can insert the results of the check into my subtable?

Johan
johan
 
Posts: 340
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: insert multiple lines in a subform.

Unread postby kev1n » Thu Jul 22, 2021 3:24 am

When all dates are free, add them after the line "nuDisplayError("Free.");". Also add the other columns in addition to $date, $res_lokaal

Code: Select all
if ($msg != '') {
   nuDisplayError($msg);
} else {

   nuDisplayError("Free.");
   
   foreach ($dates as $date) {
      addReservation($date,  $res_lokaal)
   }
}


function addReservation($res_datum,  $res_lokaal) {
   $q = "
      INSERT INTO $table (res_id, $res_datum,  $res_lokaal)
      VALUES(?, ?, ?)
   ";
   $t = nuRunQuery($q, [nuID(), $res_datum,  $res_lokaal]);
}
   
kev1n
nuBuilder Team
 
Posts: 1996
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: insert multiple lines in a subform.

Unread postby johan » Thu Jul 22, 2021 4:51 pm

Kev1n

I don't know much about PHP. But is it possible to fill in the rows in the subform using this procedure?
What I would like is to copy the data from the form for each date in the array.
So date from array, $actTimeStart, $actTimeEnd, $actLocation. When $actLocation is not available $actLocation should be left empty.

Johan
johan
 
Posts: 340
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: insert multiple lines in a subform.

Unread postby kev1n » Thu Jul 22, 2021 5:00 pm

The addReservation() adds the rows to the subform (table) but the form would have to be reloaded to see them in the subform.
If this is no option for you, the rows would have to be added by JavaScript which is a bit more work, but doable. I will try something a bit later today.
kev1n
nuBuilder Team
 
Posts: 1996
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: insert multiple lines in a subform.

Unread postby johan » Thu Jul 22, 2021 5:32 pm

Kev1n
I only get the message free, but no rows in subform.

Code: Select all
// Fields of Main from
$actDateStart = "#act_start#";
$actDateEnd = "#act_eind#";
$actTimeStart = "#act_uur_start#";
$actTimeEnd = "#act_uur_end#";
$actLocation = "#act_lokaal#";

// Get all dates from start to end
$dates = dateRange($actDateStart, $actDateEnd);



$msg = "";

// Loop through dates
foreach ($dates as $date) {
   
   
   // Check in reservaties if there's already a row with the same date, "lookal" etc.
   $sql = "
      SELECT * FROM
         reservaties
      WHERE
         res_datum = :act_date AND
         res_lokaal = :act_location
         -- add other criterias here         
     
   ";
   
   // sql arguments
   $arg = array(
      "act_date" => $date,
      "act_lokaal" => $actLocation,
      "act_uur_start" => $actTimeStart,
      "act_uur_end" => $actTimeEnd
   );

   $r = nuRunQuery($sql, $arg);   
   if (db_num_rows($r) != 0) {
      $mgs .= "Not available on $date";
   }
   
}

if ($msg != '') {
   nuDisplayError($msg);
} else {
   nuDisplayError("Free.");
   
    foreach ($dates as $date){
      addReservation($date,$actTimeStart,$actDateEnd , $res_lokaal);
    }
}


function dateRange( $first, $last, $step = '+1 day', $format = 'Y-m-d' ) {
    $dates = [];
    $current = strtotime( $first );
    $last = strtotime( $last );

    while( $current <= $last ) {
        $dates[] = date( $format, $current );
        $current = strtotime( $step, $current );
    }

    return $dates;
}


function addReservation($res_datum, $actTimeStart,$actTimeEnd, $res_lokaal) {
   $q = "
      INSERT INTO $table (res_id, $res_datum,$actTimeStart, $actTimeEnd,  $res_lokaal)
      VALUES(?, ?, ?,?,?)
   ";
   $t = nuRunQuery($q, [nuID(), $res_datum,$actTimeStart,$actTimeEnd,  $res_lokaal]);
}
   
johan
 
Posts: 340
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

PreviousNext

Return to Custom Code

Who is online

Users browsing this forum: No registered users and 2 guests