Kimai2 – SQL

1. Sample Query
2. Create Stored Procedure
3. Call the procedure
4. Do we have stored procedures?

Sample Query

SELECT
    u.username as Username,
    c.name as Customer,
    DATE_FORMAT(t.start_time,'%Y-%m') as Date,
    FORMAT(SUM(t.duration) / 3600, 0) as Duration
FROM
    kimai2_timesheet t
    LEFT JOIN kimai2_projects p ON t.project_id = p.id
    LEFT JOIN kimai2_customers c ON p.customer_id = c.id
    LEFT JOIN kimai2_users u ON t.user = u.id
WHERE 
        u.username = 'USERNAME'
    AND
        c.name = 'CUSTOMERNAME'
GROUP BY
    t.user, u.username, u.alias, c.id, c.name, DATE_FORMAT(t.start_time,'%Y-%m')
ORDER BY 
    t.user,DATE_FORMAT(t.start_time,'%Y-%m')
;

Create Stored Procedure

DELIMITER //
CREATE PROCEDURE sum_weeks (IN user CHAR(20), IN customer CHAR (20) )
BEGIN 
    SELECT
        u.username as Username,
        c.name as Customer,
        DATE_FORMAT(t.start_time,'%Y-%m') as Date,
        FORMAT(SUM(t.duration) / 3600, 0) as Duration
    FROM
        kimai2_timesheet t
        LEFT JOIN kimai2_projects p ON t.project_id = p.id
        LEFT JOIN kimai2_customers c ON p.customer_id = c.id
        LEFT JOIN kimai2_users u ON t.user = u.id
    WHERE 
            u.username = user
        AND 
            c.name = customer 
    GROUP BY
        t.user, u.username, u.alias, c.id, c.name, DATE_FORMAT(t.start_time,'%Y-%m')
    ORDER BY 
        t.user,DATE_FORMAT(t.start_time,'%Y-%m')
    ;
END//
DELIMITER ; 

Call the procedure

mysql> CALL sum_weeks('USER','CUSTOMER');
+----------+----------+---------+----------+
| Username | Customer | Date    | Duration |
+----------+----------+---------+----------+
| USER     | CUSTOMER | 2014-01 | 10       |
| USER     | CUSTOMER | 2014-02 | 2        |
+----------+----------+---------+----------+
2 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Do we have stored procedures?

SELECT db,name FROM mysql.proc WHERE db = ‘kimai’;
+——-+———–+
| db | name |
+——-+———–+
| kimai | sum_weeks |
+——-+———–+
1 row in set (0.00 sec)

SHOW PROCEDURE STATUS WHERE Db = ‘kimai’;
+——-+———–+———–+—————-+———————+———————+—————+———+———————-+———————-+——————–+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+——-+———–+———–+—————-+———————+———————+—————+———+———————-+———————-+——————–+
| kimai | sum_weeks | PROCEDURE | root@localhost | 2024-06-07 08:41:52 | 2024-06-07 08:41:52 | DEFINER | | latin1 | latin1_swedish_ci | latin1_swedish_ci |
+——-+———–+———–+—————-+———————+———————+—————+———+———————-+———————-+——————–+
1 row in set (0.00 sec)

Monthly reporting

DROP PROCEDURE IF EXISTS month_report;
DELIMITER //

CREATE PROCEDURE month_report (IN user CHAR(20), IN customer CHAR (20) )
BEGIN
    IF customer IS NULL THEN 
        SELECT
            DATE_FORMAT(t.start_time,'%Y-%m-%d') AS datum, 
            p.name AS konto, 
            a.name AS unterkonto,   
            t.duration / 3600 as Duration,
            t.duration / 3600 as Duration,
            t.description AS Comment
        FROM
            kimai2_timesheet t
            LEFT JOIN kimai2_projects p on t.project_id = p.id
            LEFT JOIN kimai2_customers c on p.customer_id = c.id
            LEFT JOIN kimai2_users u on t.user = u.id
            LEFT JOIN kimai2_activities a on t.activity_id = a.id
        WHERE
            u.username = user
        AND
            DATE_FORMAT(t.start_time,'%Y-%m') = DATE_FORMAT(CURRENT_DATE(),'%Y-%m') 
 
        ORDER BY
             DATE_FORMAT(t.start_time,'%Y-%m-%d'), t.user, p.name
        ;

    ELSE 

      SELECT
            DATE_FORMAT(t.start_time,'%Y-%m-%d') AS datum, 
            p.name AS konto, 
            a.name AS unterkonto,   
            t.duration / 3600 as Duration,
            t.duration / 3600 as Duration,
            t.description AS Comment
        FROM
            kimai2_timesheet t
            LEFT JOIN kimai2_projects p on t.project_id = p.id
            LEFT JOIN kimai2_customers c on p.customer_id = c.id
            LEFT JOIN kimai2_users u on t.user = u.id
            LEFT JOIN kimai2_activities a on t.activity_id = a.id
        WHERE
            u.username = user
        AND
                c.name = customer 
        AND
            DATE_FORMAT(t.start_time,'%Y-%m') = DATE_FORMAT(CURRENT_DATE(),'%Y-%m') 
 
        ORDER BY
             DATE_FORMAT(t.start_time,'%Y-%m-%d'), t.user, p.name
        ;
    END IF;

END//
DELIMITER ;