Hello Jimmy,
You're already structuring it well with Employees, Courses, and a linking Records table. For allowing staff to select their manager and then limiting the manager’s view to their team’s records, there are two approaches which I can recommend:
1. Using {tablename}_init with SQL filters in your hooks
You can modify the table view by adding SQL conditions based on the logged-in user. This is useful if you want to dynamically control access based on relationships in your database (like employee-manager).
Code: Select all
// Assuming you have a managerID field in your employees table that links back to another employee.
// Inside hooks/records.php, in the init function, you could do something like (untested reference code):
function records_init(&$options, $memberInfo, &$args) {
if($memberInfo['group'] === 'Managers') {
$userID = makeSafe($memberInfo['username']);
$managerID = sqlValue("SELECT employeeID FROM employees WHERE username = '{$userID}'");
$options->QueryWhere = "`employeeID` IN (SELECT employeeID FROM employees WHERE managerID = '{$managerID}')";
}
return TRUE;
}
This one’s a bit more advanced but offers a lot of flexibility. The idea is:
- Each manager has their own user group.
- When an employee selects a manager, behind the scenes, you update the record group ownership to the manager’s group using a custom SQL Query.
- That way, the employee retains ownership of their records, but the manager (via group-based access) can also see and manage them.
- Yes, it's a little more complex at first, but it gives you very granular control. You can still apply row-level security, record auditing, etc., very effectively this way.
Course Renewal Reminders
Yes, totally doable. You can:
- Add a cron job on your server to run a PHP script daily (or however often you want).
- In that script, use AppGini’s built-in sendmail function.
- Check for courses where renewal_date is due (e.g., within the next 30 days) and send reminder emails.
Just schedule this with cron and you’re good to go.
Code: Select all
// Assuming this file is saved under the hooks folder, very simplified example, not tested
$currDir = dirname(__FILE__);
define('PREPEND_PATH', '../');
@include(__DIR__ . '/' . PREPEND_PATH . "lib.php");
$res = sql("SELECT e.email, c.name, r.renewal_date
FROM records r
JOIN employees e ON r.employeeID = e.employeeID
JOIN courses c ON r.courseID = c.courseID
WHERE DATEDIFF(r.renewal_date, CURDATE()) <= 30", $eo);
while($row = db_fetch_assoc($res)) {
sendmail([
'to' => $row['email'],
'subject' => "Course Renewal Reminder: " . $row['name'],
'message' => "Hi, your course '" . $row['name'] . "' is due for renewal on " . $row['renewal_date']
]);
}
It would be interesting to see how other members are approaching this and we both can improve.
-Saymaad