Ok, let me try like this. Imagine a table with the aim to keep up with the deadlines for the contracts. It is made of three tables:
- archivio_contratti, where there's the main contract, the field for the company you signed it with, a description of what's it about, and the field for the latest deadline;
- deadlines, where for each archivio_contratti entry, you can specify a date, dropdown category, and a yes/no (is the deadline completed or not);
- documents, where for each archivio_contratti entry, you can select from the table archivio all the relevant documentation for that contract, and choose a category
They are represented in the front view like this:
form archivio_contratti:
---------------------------------------------------
archivio contratti form
---------------------------------------------------
1)..............................||2) ........................... ||
--------------------------------------------------
form footer text:
1)
Code:
{fabrik view=list id=124 archivio_contratti_scadenze___id_contratto={archivio_contratti___id} showfilters=1}
2)
Code:
{fabrik view=list id=120 archivio_contratti_documenti___id_contratto={archivio_contratti___id} showfilters=1}
all the user has to do is select the base contract in the archivio_contratti form, and the MySQL event after 5 seconds finds the rest and updates the info from archivio to archivio_contratti. For each contract, the user specifies the deadlines, and the MySQL event calculates the next deadline and places it on the archivio_contratti field for deadlines.
The archivio_contratti list has an introduction with: "{fabrik view=list" of a MySQL view containing only deadlines, supplier, date of contract, ordered by date. So I have deadlines above and list of contracts below.
MySQL event:
Code:
BEGIN
/*Scadenze*/
UPDATE archivio_contratti ac, (SELECT
id, id_contratto, data_scadenza
FROM archivio_contratti_scadenze
WHERE archivio_contratti_scadenze.id IN (SELECT archivio_contratti_scadenze.id FROM archivio_contratti_scadenze
JOIN
(SELECT archivio_contratti_scadenze.id_contratto, MIN(archivio_contratti_scadenze.data_scadenza) AS max FROM archivio_contratti_scadenze
WHERE archivio_contratti_scadenze.concluso <> 1
GROUP BY archivio_contratti_scadenze.id_contratto)
max_data_documento
ON
(max_data_documento.id_contratto = archivio_contratti_scadenze.id_contratto
AND max_data_documento.max = archivio_contratti_scadenze.data_scadenza))) AS tabella
set ac.prossima_scadenza = tabella.data_scadenza
WHERE ac.id = tabella.id_contratto;
/*Info sul contratto*/
UPDATE archivio_contratti ac, (SELECT id, fornitore, data_documento, descrizione FROM archivio) AS tabella
set ac.fornitore = tabella.fornitore, ac.data_contratto= tabella.data_documento, ac.note= tabella.descrizione
WHERE ac.contratto_base = tabella.id;
END