Query data -> loop over the array, conditionals to calculate some results and return to the screen

i am duplicating a small middleware project we did that takes a date range, returns the billable time spent, and returns an array of ClientID->TicketID->(Hours spent, After Hours Spent, Ticket Subject).

It pulls data from mysql. Then foreach’s over that data and checks the start time and end time, calculates the hours to 2 decimal places for each work log entered. Then pushes this into a new array with Customer, invoice_id, ticket subject, hours, and after hours calculations. It returns this array where it is parsed into html using a foreach by client name and then a sub foreach for each ticket subject. While it’s being parsed into HTML (it’s a printable report) it’s also updating the invoice line items for each customer.

So far in wappler i have a page that excutes the first query and displays in a dynamic table. Now I need to loop over this data and perform the time calculations and then return that to the screen. How do you do that in wappler?

I’ve searched through the docs and all i can find is how to sort a dynamic table or filter it. I need to use logic and calculate the time.

Firstly you need to stop thinking how you used to code things as the techniques in wappler are both different and easier.
Based on the limited information i would suggest you do all of this at server level and simply Output the results. So after your query i suggest you select a repeat (under core actions) and repeat on the query results, perform the calculations then output the results to your app connect page. also there are several posts about calling an API action from another server. All server action output is in JSON format by default, you don’t specify what server model you have but most languages have some for of JSON -> Array conversion if needed.

Do you perform the calculations in a server flow? That’s what I can’t find. I’m assuming I’ll have to use the while loop as it seems to be the only function for looping.

Trying to create the logic in the server flow, I’m assuming do while “id” will cause a loop through all returned results.

So let’s say I have WorkTime.start column in the returned results. Can i simply set a condition to Worktime.start < 17:30 and assume wappler will know to just do a comparison on time? Does it need to be converted?

what format is “Work time”, string? if so then yes it will need to be converted, i would suggest you work in minutes. There is also a “repeat” in addition to “while”

PHP uses a string when pulling dates from SQL. In vanilla php you then have to convert to a dateTime object or the older date function to calculate and keep in date formats.

So in php to create the interval for a work log you would first convert the “start” column and “end” column to dateTime object variables. From Mysql they would look like this and are stored in the array as strings:
‘2022-04-02 13:00:00’ and ‘2022-04-02 13:45:00’.

Then you create DateTime objects so you can manipulate the data as a date and time.
$start = new DateTime($work['start']);
$end = new DateTime($work['end']);
$interval = (($end->getTimestamp() - $start->getTimestamp())/60)/60

Those three lines of code are what i’m currently trying to accomplish and learn how wappler handles that. I’m in my server action, inside a while $id, and trying to set the interval variable. I’ve got a set value then add column, because i assume I’ll have to create a column and store the data there as the “set value” option doesn’t seem to create an object i can reference when tying a table cell to a value from the server action.

I’m fine to convert my date timestamps to minutes but you still need to perform math to find the number of minutes between the two columns and then convert to a decimal, in my example this would be .75 of hour.

Finally made some progress. And it’s awesome we can get this to display on screen.

How can we evaluate this calculated value? For instance in this example we’ve been coverting, they bill 1/2hr increments. So 1.12 would get changed to 1.5, however 1.05 is converted to just 1hr or 60 minutes.

In normal PHP we use explode on the decimal and then evaluate each side of the float based on the array that’s created. [0] being left of the decimal and [1] being to the right.

To get the screen shot below we added a cell when creating the bootstrap run table called interval. Then we’re calculating against the start cell minutes until to the end and dividing by 60.

Is there a place we can do this calulcation outside the table and it would be added to the query result, so we’re not calculating in the cell?

image

Not sure I’m following, but it sounds like you want a variable element inside your table repeat where you can calculate things and store the value for elsewhere.

Yes, I do need a variable to store an amount. I figured out how to create that interval value, screen shot, but i need to perform some more calculation on it.

I need to round each interval based on rules they (client) use to calculate time. The 0.3166666 in the screen shot needs to be modified too 1hr. However 1.36666 would need to modified to 1.5 hours. Then I need to summarize by each client. The client in the screen shot above would have a total of 2 hours because of how they calculate time for work performed.

This data is coming from a ticketing system where each task on a ticket is rounded according to how they bill, then summed up by ticket. So say ticket 1 has two work logs of .31666 minutes and .533333 minutes. This would be rounded to .5 and .5 then added together. Ticket 1 would have a total time of 1 hour, which is what i need to store.

Right now I can’t figured out how to round the interval values according to the rules they need.
Here is what it looks like in the php we wrote to do this:

if (is_float($worked['hrs'])){
     //split on decimal
     $split = explode(".", $worked['hrs']);

    //now check integer before decimal
     if ($split[0] < 1) {

       $hours = 0;
     } else {
       $hours = $split[0];
     }


     //now check number after split by making it a float again.
      if (isset($split[1])){
       if ('.'.$split[1] <= .5 && '.'.$split[1] > .01) {
         //$minutes = .5;
         if ($hours < 1)
          $minutes += 1;

         else
          $minutes += .5;

       } elseif ('.'.$split[1] > .5){
           $minutes = 0;
           $hours = $hours + 1;

       } else {
         $minutes = 0;

       }
     } else {
        $minutes = 0;
     }
    $hours = $hours + $minutes;

As you have working function, i would create a custom extension to utilize it and apply when needed on the server. If that isn’t realistic, maybe create a JavaScript version of this and then create a custom formatter so you can use it on the client side. I’m all in with node, so maybe php folks will be able to help better!

PHP explode is the same as the Wappler split function, most functions in wappler have their equivalent JavaScript name rather than the native server language name

This is a small project we completed a while back in vanilla php, so that working function is from that project.

I’m trying to find out the capabilities in wappler by emulating this project. It’s a fairly simple middleware worker.
It gathers up time worked from a ticket system via sql. iterates over that and compiles a new multi-dimensional array by ticket and then calculates total time on that ticket per the customer’s rules on hourly billed work.

What I’m struggling with is where to do this in wappler. I believe server side actions is where I need to be but it’s struggle bus to understand how to create the logic necessary. Can’t find much in the documentation.

Server Side Actions are indeed the place to do any sort of exotic calculation - you can think of it as a Controller (in Laravel terms)

As a starting example, I started making the first portion (more or less) in a Library action. You can think of a Library action as a re-usable function. I believe this is only available on NodeJS, I would suggest to use NodeJS tbh. You can do it directly on the Server Action, in that case you wouldn’t have the $_PARAM.working_hrs, but probably just a Set Value working_hrs

I skipped the is_float Condition as I don’t know the exact logic/behaviour behind it

I would then call this Library action (function) inside the Server Action (controller)

The Exec Action step accepts input arguments (just like a function) - in this case, working_hrs

1 Like

is float tells you if it’s a decimal or not.

Maybe you can move the first Set Value hours = 0 outside of the Condition to make sure such variable is always present

Do you need help with anything else? I haven’t read the whole topic

Array manipulation (editing) is something not quite implemented, so people have used workarounds, you can find more information in this topic:

Took me a minute to get back to this…

Your link is exactly what i’m trying to do and unfortunate it’s not in wappler currently. That’s a big functionality miss on the dev team.