How to get available Discounts or credits for a past period?

Developing for Dolibarr
1

Hello,

I would like to know how I can obtain the value of Discounts or credits available to a client within a specific time period.

I printed the query, and it was like this:

SELECT SUM(rc.amount_ttc) as amount, SUM(rc.multicurrency_amount_ttc) as multicurrency_amount 
FROM jotex_societe_remise_except as rc 
WHERE rc.entity = 1 
AND rc.discount_type = 0 
AND (rc.fk_facture IS NULL AND rc.fk_facture_line IS NULL) 
AND rc.fk_soc = 178

I wanted to add a date condition, but I found that there is only one date column, which takes its value from the server’s date and time, not the invoice date.

So even if I create an invoice dated 1-7-2024 (an old date), the system will record it in the societe_remise_except table with today’s date, and thus I won’t be able to filter.

Does anyone have an idea or suggestion to guide me?

Untitled
Untitled1867×876 131 KB

1 Like
2

Try joining the table with the invoces (llx_facture)

SELECT SUM(rc.amount_ttc) AS amount, SUM(rc.multicurrency_amount_ttc) AS multicurrency_amount 
FROM llx_societe_remise_except AS rc 
INNER JOIN llx_facture AS fa ON rc.fk_facture = fa.rowid
WHERE rc.entity = 1 
AND rc.discount_type = 0 
AND (rc.fk_facture IS NULL AND rc.fk_facture_line IS NULL) 
AND rc.fk_soc = 178
AND fa.datef<your_date_condition>