insert multiple lines in a subform.

[kev1n]

I don't see an attachment. You might have to zip it.

[johan]

Ik attachment.

[kev1n]

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.

[johan]

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

[kev1n]

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);

[johan]

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

Johan

[kev1n]

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]);
}
	

[johan]

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

[kev1n]

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.

[johan]

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]);
}