insert multiple lines in a subform.

[kev1n]

Try with this updated code:

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, $actTimeEnd , $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 reservaties (res_id, res_datum, actTimeStart, actTimeEnd,  res_lokaal)
      VALUES(?,?,?,?,?)
   ";
   $t = nuRunQuery($q, [nuID(), $res_datum, $actTimeStart, $actTimeEnd,  $res_lokaal]);
}

[johan]

Kev1n

Now I get an insert in my table reservatie.
I'm missing the foreignKey (id of form) and my dates are 1921-07-22

Johan

[kev1n]

I added the FK in the function addReservation().
Place the code in the AS (After Save) event instead of executing a Procedure, otherwise it won't work since the parent records needs to be saved first.
I also added a convertDate() function to convert the date of format dd.mm.yy to yyyy-mm-dd

Code: Select all

// Fields of Main from
$actDateStart = convertDate("#act_start#");
$actDateEnd = convertDate("#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, $actTimeEnd , $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 reservaties (res_id, res_act_id, res_datum, actTimeStart, actTimeEnd,  res_lokaal)
      VALUES(?,?,?,?,?)
   ";
   $t = nuRunQuery($q, [nuID(), "#RECORD_ID#", $res_datum, $actTimeStart, $actTimeEnd,  $res_lokaal]);
}

// Convert a date of format dd.mm.yy --> yyyy-mm-dd
function convertDate($d) {
   $date = str_replace('.', '-', $d);
   return date('Y-m-d', strtotime($date));
}

[johan]

Ok this works if there is no date in the array where the room is busy.

[kev1n]

Ok this works if there is no date in the array where the room is busy.

Can you give some more details on what works and what does not work?

[johan]

Kev1n
In after save my rows are inserted when saving.
I have to check my SQL because the result is always free, even with duplicate rows.
With procedure same result if I run procedure on saved form. Maybe an option to hide subform on a new form and renew page at the end of the procedure?
Johan

What I miss is the part where occupied dates are inserted without room or room 0

[kev1n]

Did you add the other criteria in this SQL?

Code: Select all

   
   // 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 criteria here         
     
   ";
   

Here's the updated SQL that inserts the rows in the subform. I think you just need to update the SQL above in order to detect occupied locations (or are they rooms?)

Code: Select all

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

// 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, $actTimeEnd , $actLocation, $actParticipants);
    }
}


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, $actLocation, $actParticipants) {
   $q = "
      INSERT INTO reservaties (res_id, res_act_id, res_datum, res_uur_start, res_uur_eind, res_lokaal, res_deelnemers)
      VALUES(?,?,?,?,?,?,?)
   ";
   $t = nuRunQuery($q, [nuID(), "#RECORD_ID#", $res_datum, $actTimeStart, $actTimeEnd,  $actLocation, $actParticipants]);
}

// Convert a date of format dd.mm.yy --> yyyy-mm-dd
function convertDate($d) {
   $date = str_replace('.', '-', $d);
   return date('Y-m-d', strtotime($date));
}

Important: You will need the updated nuformclass.js that fixes a year bug when using a two-digit year.

[johan]

Kev1n

This is the code that I use in procedure.

• The SQL part gives no result, even with "select * from reservaties" the result is always empty. Can I use nuDebug to see the result of the query?
• Now he inserts every row 2 times.

Code: Select all

// Fields of Main from
$actDateStart = convertDate("#act_start#");
$actDateEnd = convertDate("#act_eind#");
$actTimeStart = "#act_uur_start#";
$actTimeEnd = "#act_uur_eind#";
$actLocation = "#act_lokaal#";
$actID = "#act_id#";

// 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_start AND
       res_lokaal = :act_lokaal AND
       res_uur_start = :act_uur_start AND
        res_uur_eind = :act_uur_eind
         -- add other criterias here         
     
   ";
   
   // sql arguments
   $arg = array(
      "act_date" => $date,
      "act_lokaal" => $actLocation,
      "act_uur_start" => $actTimeStart,
      "act_uur_eind" => $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($actID, $date, $actTimeStart, $actTimeEnd , $actLocation);
    }
}


function dateRange( $first, $last, $step = "+#act_ritme#"    , $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($actID,$res_datum, $actTimeStart,$actTimeEnd, $actLocation) {
   $q = "
      INSERT INTO reservaties (res_id, res_act_id, res_datum, res_uur_start, res_uur_eind,  res_lokaal)
      VALUES(?,?,?,?,?,?)
   ";
   $t = nuRunQuery($q, [nuID(), $actID, $res_datum, $actTimeStart, $actTimeEnd,  $actLocation]);
}

// Convert a date of format dd.mm.yy --> yyyy-mm-dd
function convertDate($d) {
   $date = str_replace('.', '-', $d);
   return date('Y-m-d', strtotime($date));
}

[kev1n]

:act_start is supposed to be :act_date, see my previous post.

[johan]

Kev1n
Same result.
When I replace the query with query below, I still get free. If I run that query in MariaDB I get 19 rows.

Code: Select all

    SELECT * FROM
         reservaties
    WHERE
      res_datum = '2021-07-19'