Help and advice on a new app

[jimitodd]

Hi All

I'm building a training records DB.
I have an employees table, firstname lastname, email, username, userid.
And a courses table, name, date renewal date, courseid.
a records table relating pulling the info together.

it's in the early stages yet so could all change.

I would like a member of staff to choose who their manager is from the table, and then when the manager signs on they can only see their staffs records.

I would also like an email to go out when a course is due to be renewed, course renewal length varies.

any help, advice would be greatly appreciated.

Thanks jimi

[saymaad]

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;
}

2. Using Group Ownership (more flexible long term, preferred if you don't have a few 100 managers)
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