[Enhancement] credit report and top up all -button

[ikke-t]

[ ]: I understand that the best way to get this enhancement is to create it myself and propose a Pull Request.

Is your feature request related to a problem? Please describe.**

We would like to cover expenses of the house utilities consumed. For that I'd like to give everyone credit, and few times a year check balances and reset the credit saldo.

Describe the solution you'd like**

I'd like to have a report of credits grouped by organization. Grouping could be selectable menu. I also would like to set everyone's credit back to given number.

Describe alternatives you've considered**

I would do sql query and ask database to do the grouping and math, it likely can do that. Second sql would go and set all credits to given numer. Output could be csv file which opens to sheet programs.

Additional context**

I would perhaps include this among the other reports.

[ikke-t]

I am no SQL guru, so I asked cursor to craft me such queries. Both of the ways give some numbers, but need to verify after having some real usage on credits. But for reference:

Prompt

See librebooking database schema from database_schema directory. I use version 4.0.2. I set up each user credit saldo 1000 to begin with. Organization might have several users. Create SQL query based on the db schema which summarizes how much credit each organization users have consumed.

Answers

answer 1

Here’s an SQL query for LibreBooking 4.0.2 that summarizes credit consumption per organization:

-- Credit consumption summary per organization
-- Assumes each user starts with 1000 credits; consumption = deductions in credit_log
-- (original_credit_count > credit_count means credits were consumed)

SELECT
    COALESCE(NULLIF(TRIM(u.organization), ''), '(No organization)') AS organization,
    COUNT(DISTINCT u.user_id) AS user_count,
    COALESCE(SUM(user_consumed.credits_consumed), 0) AS total_credits_consumed
FROM `users` u
LEFT JOIN (
    SELECT
        cl.user_id,
        SUM(cl.original_credit_count - cl.credit_count) AS credits_consumed
    FROM `credit_log` cl
    WHERE cl.original_credit_count > cl.credit_count
    GROUP BY cl.user_id
) user_consumed ON u.user_id = user_consumed.user_id
GROUP BY COALESCE(NULLIF(TRIM(u.organization), ''), '(No organization)')
ORDER BY total_credits_consumed DESC;

How it works:

credit_log:

original_credit_count = balance before transaction
credit_count = balance after transaction
Consumption: rows where original_credit_count > credit_count (credits deducted for reservations).
Consumption per user = SUM(original_credit_count - credit_count) over those rows.

users: organization is used to group results. Empty or NULL organization is shown as (No organization).

LEFT JOIN: organizations with no consumption (no credit_log rows) still appear with total_credits_consumed = 0.

Alternative using current balance (if all users start at exactly 1000):

If every user truly begins with 1000 credits and you do not need to handle purchases or refunds from credit_log:

SELECT
    COALESCE(NULLIF(TRIM(u.organization), ''), '(No organization)') AS organization,
    COUNT(*) AS user_count,
    SUM(1000 - COALESCE(u.credit_count, 0)) AS total_credits_consumed
FROM `users` u
GROUP BY COALESCE(NULLIF(TRIM(u.organization), ''), '(No organization)')
ORDER BY total_credits_consumed DESC;

This uses: consumed = 1000 - current_balance per user, which is simpler but assumes no credit purchases or refunds. The first query is correct for any credits added or refunded.

result

The second method is more aligned to what I need. The result looks like this:

+-------------------+------------+------------------------+
| organization      | user_count | total_credits_consumed |
+-------------------+------------+------------------------+
| B32               |          1 |                 125.00 |
| (No organization) |          1 |                  38.00 |
| B 43              |          1 |                   0.00 |
+-------------------+------------+------------------------+

Like said, I only have one admin, and two users from whom the one has only tried it. Need more data.

[ikke-t]

of course it would be easier if credit could go the other way. Like increase the credit by each use, and then occationally we would just zero it after payment.

[ikke-t]

For reference, I also tried this via API:

curl -d '{"username": "foo","password": "bar"}' -X POST -H "Content-Type: application/json" ${URL}/Web/Services/index.php/Authentication/Authenticate

curl -X GET -H "X-Booked-SessionToken: $TKN" \
 -H "X-Booked-UserId: $USER" \
 ${URL}/Web/Services/index.php/Users/ | \
 jq '.users.[] | select(.currentCredits != "1000.00") | "Org: \(.organization // "" | select(. != "") // "-"), Name: \(.firstName) \(.lastName), Saldo: \(.currentCredits)"'