Reminder.

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
mqley
Veteran Member
Posts: 70
Joined: 2020-07-19 13:25
Contact:

Reminder.

Post by mqley » 2021-01-08 17:14

Hi, how would one go about having an email sent at a certain date/time. A bit like the reminder app on Apple but simplified ?

Thanks
Best Wishes,

Mark

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Reminder.

Post by pbottcher » 2021-01-08 18:15

Hi,

set-up a cron job (unix) or a task-cscheduler (win).
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mqley
Veteran Member
Posts: 70
Joined: 2020-07-19 13:25
Contact:

Re: Reminder.

Post by mqley » 2021-01-08 20:17

From with an Appgini app I mean - say if you have a date field and you want that as a reminder? Thanks
Best Wishes,

Mark

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Reminder.

Post by pbottcher » 2021-01-08 20:45

Hi,


appGini isself has no scheduling function. It only reacts on users events.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Reminder.

Post by pbottcher » 2021-01-08 20:47

If you want to react on a user event, let say, the user logs in, you can verify against you date field and use the build-in mail function to send a mail.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mqley
Veteran Member
Posts: 70
Joined: 2020-07-19 13:25
Contact:

Re: Reminder.

Post by mqley » 2021-01-09 01:10

Ok thanks. Would the calender mod help?
Best Wishes,

Mark

mqley
Veteran Member
Posts: 70
Joined: 2020-07-19 13:25
Contact:

Re: Reminder.

Post by mqley » 2021-01-09 09:39

I saw the paid calendar mod and wondered if that would work. It does look good that. Thinking about your answer then. If I logged in and add say 100 entries. Could I check against all the dates and send reminders if any match the date? If so do you happen to have any ref of this being done? Thanks.
Best Wishes,

Mark

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Reminder.

Post by pbottcher » 2021-01-09 12:15

The same applies for the calendar plugin. There is no scheduler function.

If you do any action within your app, you can always call a function to do what you need to do, but it is alway "user" driven and not scheduled.
For a scheduled type you need something external (cron, task scheduler, ...).
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mqley
Veteran Member
Posts: 70
Joined: 2020-07-19 13:25
Contact:

Re: Reminder.

Post by mqley » 2021-01-09 12:41

Ok thanks for the info. Much appreciated.
Best Wishes,

Mark

mqley
Veteran Member
Posts: 70
Joined: 2020-07-19 13:25
Contact:

Re: Reminder.

Post by mqley » 2021-01-10 11:29

i have this working now via a custom page. It looks through all rows and matches todays date with the reminder date.

It emails me when I refresh the page. Which I can set a cron job up to run then.

Just a thought. It runs through all rows of a table and emails me only. How would I detect for different users and email them rather than just emailing all the results to me? So each user would get all their reminders if that makes sense?

Regards,

Mark
Best Wishes,

Mark

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Reminder.

Post by jsetzer » 2021-01-10 11:52

Join your table with membership_userrecords, group by owner, join with membership_users on owner=memberID to get users' email addresses. Prepare a message per email address which contains all info about the related records.

Or

Other way round: get all users having valid email addresses. For each user fetch all records from your table, owned by that user. Prepare an email body per user and send.

I would store notified_on (datetime) in your table for avoiding duplicates. Skip records which already have been sent.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

mqley
Veteran Member
Posts: 70
Joined: 2020-07-19 13:25
Contact:

Re: Reminder.

Post by mqley » 2021-01-10 14:32

Thanks for info Jan. I'll look into this. Regards
Best Wishes,

Mark

mqley
Veteran Member
Posts: 70
Joined: 2020-07-19 13:25
Contact:

Re: Reminder.

Post by mqley » 2021-01-17 17:01

Hi, I have this working locally with a refresh of the custom page. When I run from the server a cron to run this page (daily) it comes up with ACCESS DENIED - so it looks like the cron won't run. Have you had any experience with this?

Thanks,

Mark
Best Wishes,

Mark

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Reminder.

Post by jsetzer » 2021-01-17 18:32

Did you create a custom page for your automation? You should.

Silly question: did you upload the custom page to your server? Does your CRON Job call the correct serverside custom php page?

There you are free to execute specific SQL commands even without being logged in.

When using pure SQL with functions sqlValue() and sql() the permissions will not be checked. But as soon as you start using built-in AppGini PHP functions like get_sql_from (don't know if the name is correct) there will be a permission check unless you change $skip_auth parameter.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Reminder.

Post by onoehring » 2021-01-17 18:39

Hi,

ACCESS DENIED is a bit short for us to get a hint of the source for the problem ;-)
Does your custom page need a login (of an AG user)?

If so, I suggest something different:

Create a simple page that can be called from any browser.
In that page do some access checking: Test the GET parameter X set.
You should be able to set a a cron by calling: http(s)://yourURL/yourCronPage.php?pw=yourSecretCronjobPasswort
If the parameter "pw" matches the expected parameter, the page calls the function "reminderthing".

In your AG page (where you do the reminder-check) simply call the function "reminderthing".

In theory :-) I do not see any reasons for problems.
Olaf


mqley
Veteran Member
Posts: 70
Joined: 2020-07-19 13:25
Contact:

Re: Reminder.

Post by mqley » 2021-01-17 20:40

Hi, thanks both! Yeah I have a custom page with my own code - it works when I refresh the page on the server. Emails me the details I need. The cron is correct but gets an access denied result.

The header as this:

define('PREPEND_PATH', '../');
$hooks_dir = dirname(__FILE__);
include("$hooks_dir/../lib.php");



include_once("$hooks_dir/../header.php");


Then the main SQL is this:

$cnt = 0;
$dateToday = date('Y-m-d');

$eo = null;
$rows = [];
$sql = "SELECT id,title,reminder_date,reminder_flag FROM expenseTracker WHERE reminder_flag>='1' ORDER by id";
$res = sql($sql, $eo);
if (!$eo) {
while ($row = db_fetch_assoc($res))
{
$rows[] = $row;

}


Followed by the mail function, so don't think I"m using any AppGini built in functions to cause Access Denied, but could definitely be wrong!. I"ll keep experimenting anyway. Thanks guys. Mark
Best Wishes,

Mark

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Reminder.

Post by jsetzer » 2021-01-17 21:49

Well, in your custom page you include header.php which, I guess, may fetch some user dependent settings like navbar items and current member.

Maybe you should try without that header-include.

I don't know if you have posted the complete code or fragments, only. If there is also a footer.php-include, you should remove that, too. I think there is no need for UI elements like navbar in a serverside script being called by CRON. It will not be shown to anyone, anyway.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

mqley
Veteran Member
Posts: 70
Joined: 2020-07-19 13:25
Contact:

Re: Reminder.

Post by mqley » 2021-01-21 19:56

Hi, ok I have the cron working now. To recap it does work with the header and footer in (should you need it). I'd been uploading the changes to the wrong folder which really didn't help. But a learning curve anyway I suppose. Never used cron before so pleased with that.

That brings me back to an earlier point in this thread. Regarding having all users each receive their own reminders.

It looks like I need to get:
email and memberID fields from the table: membership_users

Then find the fields - memberID in the table: membership_userrecords and the relevant tableName (in my case expenseTracker) and pkValues (pkValues seems to hold all the ids that I'll need to match the correct rows per user)

Now also if a reminder is ticked this sets a field called reminder_flag to 1. I've actually work it out so every day an email is sent the flag moves on one for a period of 7 then goes to unset (0) so you have a week of reminders before it stops sending.

So my question is how would one write the SQL (I'm very new to this and think I'll need join?) to be the most efficient to look through all users find the flag set and bring the email in so I can email them?

The working SQL for all users to my email is currently this:

SELECT id,title,reminder_date,reminder_flag FROM expenseTracker WHERE reminder_flag>='1' ORDER by id

But would like to work it so it works for each user. I have tried but gone back to the drawing board as I"m really not sure the best way forward here. Thanks.
Best Wishes,

Mark

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Reminder.

Post by onoehring » 2021-01-22 07:52

Hi,

it's probably not the answer to your different questions above.
You should have some table that holds the user for whom the reminder is set up. Let's say "tbl_customer".
In my example, the two tables have a common field ID_expenseTracker which creates the relation between tbl_customers and expenseTracker.
Maybe something like this:

Code: Select all

SELECT
	expenseTracker.id,
	expenseTracker.title,
	expenseTracker.reminder_date,
	expenseTracker.reminder_flag,
	tbl_customer.username	/* this is not really needed */      
FROM
    tbl_customer INNER JOIN
    expenseTracker ON tbl_customer.ID_expenseTracker = expenseTracker.ID_expenseTracker
WHERE   
	 expenseTracker.reminder_flag>='1' AND
	 tbl_customer.username = 'something'		/* your criteria to get data for a single app user only */
Olaf

mqley
Veteran Member
Posts: 70
Joined: 2020-07-19 13:25
Contact:

Re: Reminder.

Post by mqley » 2021-01-22 18:38

Hi Olaf, thanks for this. How would one go about this with AppGini.

"You should have some table that holds the user for whom the reminder is set up. Let's say "tbl_customer". "

I sort of get what you're saying, when a reminder is setup then it needs to update the tbl_customer (table needs to be created - appgini? )

Then when it comes to custom php it will be easier to work out rather than how I thought about it. Is that what we're saying here?

Appreciate the feedback. Thanks.
Best Wishes,

Mark

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Reminder.

Post by onoehring » 2021-01-23 09:06

Hi,
well, you want to send reminders to a specific user, when that user is "active". This means there must be a connection between a user and the reminder. In the example above it's a table tbl_customer that holds the username and your expenseTracker table that also holds a username: The record in that table belongs to a user - simple as that.
When the reminder is setup not the tbl_customer would be changed, but in your expenseTracker there would be a new entry for THAT user - with the username to create the relation between the expenseTracker record and the user (customer in the example case)

Olaf

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Reminder.

Post by pbottcher » 2021-01-23 11:40

Hi,

if you use your code

SELECT id,title,reminder_date,reminder_flag FROM expenseTracker WHERE reminder_flag>='1' ORDER by id

you get all the records that will have a reminder to be send.

If you join now your table expendeTracker with the membership_userrecords table you will also have the information who owns that record.

Try

Code: Select all

SELECT e.id,e.title,reminder_date,e.reminder_flag,m.memberID FROM expenseTracker e
LEFT JOIN membership_userrecords m ON e.id=pkValue and m.tableName='expenseTracker' 
WHERE e.reminder_flag>='1' ORDER by e.id
Now you can loop through the result to send your mails.

Also if you want to use the e-mail address from the membership_users table, join that as well, so you have the e-mail address directly in your result.

Code: Select all

SELECT e.id,e.title,reminder_date,e.reminder_flag,u.memberID,u.email  FROM expenseTracker e
LEFT JOIN membership_userrecords m ON e.id=pkValue and m.tableName='expenseTracker' 
LEFT JOIN membership_users u on m.memberID=u.memberID 
WHERE e.reminder_flag>='1' ORDER by e.id
CODE is not tested, you there might by typos.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

mqley
Veteran Member
Posts: 70
Joined: 2020-07-19 13:25
Contact:

Re: Reminder.

Post by mqley » 2021-01-24 17:33

Hi, thank you - this seems to work exactly as I wanted. Really appreciate this. I don't understand it all as yet, but will research SQLs some more.

SELECT e.id,e.title,reminder_date,e.reminder_flag,u.memberID,u.email FROM expenseTracker e
LEFT JOIN membership_userrecords m ON e.id=pkValue and m.tableName='expenseTracker'
LEFT JOIN membership_users u on m.memberID=u.memberID
WHERE e.reminder_flag>='1' ORDER by e.id

Would this work with say 1000 reminders? Or would I run into email issues. Guess thats another topic!

Thanks very much for this.

Regards,

Mark
Best Wishes,

Mark

mqley
Veteran Member
Posts: 70
Joined: 2020-07-19 13:25
Contact:

Re: Reminder.

Post by mqley » 2021-04-18 11:47

Hi again, how would I amend this to include another table to check. i.e. incomeTracker (below was created via pböttcher and it totally loses me although I have tried to get to grips with this. Any help would be appreciated. Thanks.

$sql = "SELECT e.id,e.title,reminder_date,e.reminder_flag,u.memberID,u.email FROM expenseTracker e
LEFT JOIN membership_userrecords m ON e.id=pkValue and m.tableName='expenseTracker'
LEFT JOIN membership_users u on m.memberID=u.memberID
WHERE e.reminder_flag>='1' ORDER by e.id";
Best Wishes,

Mark

Post Reply