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 » Thu Jul 22, 2021 5:45 pm

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]);
}
kev1n
nuBuilder Team
 
Posts: 1868
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 6:45 pm

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
johan
 
Posts: 337
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: insert multiple lines in a subform.

Unread postby kev1n » Thu Jul 22, 2021 7:03 pm

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));
}
kev1n
nuBuilder Team
 
Posts: 1868
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 7:32 pm

Ok this works if there is no date in the array where the room is busy.
johan
 
Posts: 337
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: insert multiple lines in a subform.

Unread postby kev1n » Fri Jul 23, 2021 12:28 am

johan wrote: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?
kev1n
nuBuilder Team
 
Posts: 1868
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: insert multiple lines in a subform.

Unread postby johan » Fri Jul 23, 2021 3:17 am

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
johan
 
Posts: 337
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: insert multiple lines in a subform.

Unread postby kev1n » Fri Jul 23, 2021 3:53 am

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.
kev1n
nuBuilder Team
 
Posts: 1868
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: insert multiple lines in a subform.

Unread postby johan » Fri Jul 23, 2021 4:57 pm

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));
}
johan
 
Posts: 337
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: insert multiple lines in a subform.

Unread postby kev1n » Fri Jul 23, 2021 7:13 pm

:act_start is supposed to be :act_date, see my previous post.
kev1n
nuBuilder Team
 
Posts: 1868
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: insert multiple lines in a subform.

Unread postby johan » Fri Jul 23, 2021 9:04 pm

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'   
johan
 
Posts: 337
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 8 guests