Writing current date and time to DB from a context menu item

Something like this could work (you will need to adjust it for your needs):

async function clockOn() {
    const modal = await DayPilot.Modal.confirm("Are you sure you want to clock onto this job?");

    if (!modal.canceled) {
        const {data} = await DayPilot.Http.post("clock_on.php");
        dp.message(data.message);
    }
}

async function clockOff() {
    const modal = await DayPilot.Modal.confirm("Are you sure you want to clock off this job?");

    if (!modal.canceled) {
        const response = await DayPilot.Http.post("clock_off.php");
        if (response.status === 200) {
            dp.message(response.data);
        }
    }
}

And the PHP scripts:

<?php
// Database connection...
$db = new PDO('mysql:host=localhost;dbname=yourdb;charset=utf8', 'username', 'password');

// Insert current date and time into job_start field
$sql = "UPDATE jobs SET job_start = NOW() WHERE user_id = :user_id";
$sth = $db->prepare($sql);
$sth->execute(array(':user_id' => $_SESSION['user_id']));

$response = array('message' => 'Clocked on');
echo json_encode($response);

Note that it creates a new db record and it reads a user id stored in session.

<?php
// Database connection...
$db = new PDO('mysql:host=localhost;dbname=yourdb;charset=utf8', 'username', 'password');

// Insert a new record with current date and time into job_end field
$sql = "INSERT INTO jobs (user_id, job_end) VALUES (:user_id, NOW())";
$sth = $db->prepare($sql);
$sth->execute(array(':user_id' => $_SESSION['user_id']));

$response = array('message' => 'Clocked off');
echo json_encode($response);

Tank you! I'll give it a whirl and let you know what happens.

Hi Dan

I was doing fine until this bit: "Note that it creates a new db record and it reads a user id stored in session."

I'd actually used the existing 'events' table and simply added the fields 'job_start' and 'job_end'. Each event in this table also has a resource ID that corresponds to a user, thus adding clocking on/off records to this table gives me all the information I need.

I'm guessing that all I need to do is change this line:

$sql = "UPDATE jobs SET job_start = NOW() WHERE user_id = :user_id"; $sth = $db->prepare($sql); $sth->execute(array(':user_id' => $_SESSION['user_id']));

To something like this:

$sql = "UPDATE events SET job_start = NOW() WHERE id = :id";

Which, when implemented, gives me 'message' => 'Clocked on' but nothing is written to the job_start field in events.

What am I missing?

Thank you.

Just looing at the Clocking On aspect my full clock_on.php is:

<?php
require_once '_db.php';

$sql = "UPDATE events SET job_start = NOW() WHERE id = :id";

$response = array('message' => 'Clocked on'); 
echo json_encode($response);

and the Context menu item is:

{text: "Clock On", onClick: async function clockOn() {
    const modal = await DayPilot.Modal.confirm("Are you sure you want to clock onto this job?");

    if (!modal.canceled) {
        const {data} = await DayPilot.Http.post("clock_on.php");
        dp.message(data.message);
    }
}
},

Looking back at my first question to you, a few weeks back, I'm guessing I need to do something similar to:

https://forums.daypilot.org/question/6039/links-in-right-click-menu

however I cannot work out how to work the current date/time into the mix.

Yes, you can use UPDATE to modify an existing record. Don't forget to send the event id with the request (either in the query string part of the URL or in the body as JSON) and bind the value to the query.

You don't have to send the current time from the client. It's better to generate in on the server. In the example above it's generated by the database (the "NOW()" function).

Hi Dan

Thank you very much for the reply however, I'm sorry, you've lost me. I'm fine with HTML/CSS but I "best guess" and copy my way through PHP/Javascript and my best guess is coming up short!

I need the idiots guide...

You can send the event id like this:

The event object is accessible as `args.source` in the `onClick` event handler (https://api.daypilot.org/daypilot-menu-items/). You can read the event id using the `id()` method (https://api.daypilot.org/daypilot-event-id/).

The `DayPilot.Http.post()` method sends the specified object (the second argument) in the request body in JSON format (https://api.daypilot.org/daypilot-http-post/).

{
  text: "Clock On", 
  onClick: async (args) => {
    const modal = await DayPilot.Modal.confirm("Are you sure you want to clock onto this job?");

    if (!modal.canceled) {
        const {data} = await DayPilot.Http.post("clock_on.php", {id : args.source.id());
        dp.message(data.message);
    }
  }
},

And on the server (clock_on.php) you can read and bind the id like this:

<?php
require_once '_db.php';

$json = file_get_contents('php://input');
$params = json_decode($json);

$stmt = $db->prepare("UPDATE events SET job_start = NOW() WHERE id = :id");
$stmt->bindParam(':id', $params->id);
$stmt->execute();

header('Content-Type: application/json');
$response = array('message' => 'Clocked on'); 
echo json_encode($response);

Good morning Dan :-)

Thank you very much for the clear explanation and the code. It now works perfectly and, more importantly, I now understand why it works perfectly!