Average of last seven entries?

[madpad]

Hello
I have a list dailylogs with three elements - {dailylogs___weight}, {dailylogs___bp},{dailylogs___sugar}. Users enter data every day, but I would like to generate a summary report every week, say every Saturday. In that email template I send out, I want to capture the average of the last seven entries, or entries from date1 to today, whichever is easier. I want this to be stored as $weeklyavgwt $weeklyavgbp and $weeklyavgsugar.

Secondly, I am calculating a day element as follows -
$id = (int)'{dailylogs___dayid}';
$date=new DateTime();
$date->setDate(2020,8,8);
$date->modify('+'.$id. 'day');
return $date->format('D');

This was supposed to calculate and store Days (Mon, Tue etc) on the fly, and based on if today = Sat, trigger an email report. Something is going wrong and $id is returning 0 every case.

Can someone help how I can calculate these three average variables, and also set a weekly email plugin? Thanks a bunch.

 

[troester]

I don't know what your 'dayid' is. Maybe you need dayid_raw.

In the email form plugin you can do whatever you need by creating and selecting a php template.
http://fabrikar.com/forums/index.php?wiki/email-form-plugin/

If you want to send emails automatically (without user action) you can create a scheduled task using the email or php plugin
http://fabrikar.com/forums/index.php?wiki/scheduled-task-plugins/

 

[Sophist]

1. I would recommend that you store the actual date of the measurements in the dailylogs table rather than calculate it as an offset from a fixed date using the dayid, which assumes that dayid is always the offset from the start date - but this is so easy to go wrong if e.g. you miss a measurement, or make two measurements or whatever. Also whilst AUTO INCREMENT ids are guaranteed to be unique, but am not at all sure that they are guaranteed to be consecutive either.

2. If you are certain that you will have entries for all 7 previous days, from and SQL perspective you can "SELECT TOP 7 AVG(weight), AVG(bp), AVG(sugar) FROM dailylogs ORDER BY date DESC".

But it might be safer to do "SELECT AVG(weight), AVG(bp), AVG(sugar) FROM dailylogs WHERE date >= DAYADD(day, -6, enddate)".

 

[madpad]

1. I would recommend that you store the actual date of the measurements in the dailylogs table rather than calculate it as an offset from a fixed date using the dayid, which assumes that dayid is always the offset from the start date - but this is so easy to go wrong if e.g. you miss a measurement, or make two measurements or whatever. Also whilst AUTO INCREMENT ids are guaranteed to be unique, but am not at all sure that they are guaranteed to be consecutive either.

2. If you are certain that you will have entries for all 7 previous days, from and SQL perspective you can "SELECT TOP 7 AVG(weight), AVG(bp), AVG(sugar) FROM dailylogs ORDER BY date DESC".

But it might be safer to do "SELECT AVG(weight), AVG(bp), AVG(sugar) FROM dailylogs WHERE date >= DAYADD(day, -6, enddate)".

Thank you.
Where would this code "SELECT AVG(weight), AVG(bp), AVG(sugar) FROM dailylogs WHERE date >= DAYADD(day, -6, enddate)" be inserted, and how do I call out those average values in the email php plugin?

 

[Sophist]

Sorry - but it is so long since I used Fabrik that I am no longer certain how to achieve this. I guess it depends on how you are triggering the transaction, but I would probably run it as a cron transaction and run the SQL from some php code and send the email from the same php code.

 

[troester]

If you are using an email form plugin with php template the AVG code has to go into your-template.php.
See http://fabrikar.com/forums/index.php?wiki/php-common-tasks/&redirect=common-php-tasks for the exact query syntax.