SAINT - raw SQL
Note
To use SAINT, a separate license should be obtained. It also requires that the administrator has defined one or more SAINT statuses and generated SAINT status values on the database.
When SAINT is enabled, whenever a contact or project is created, a set of rows are added to the countervalue table. There is always a row in the countervalue table corresponding to follow-up, document, or sale. The respective counter rows in the countervalue table will be updated whenever a follow-up, document, or sale is created.
SAINT counters
When a sale is created, it is saved with sale status = 1 (open). This updates the CounterValue for the contact_id in the sale with saleStatus = 1 and saleStatus = 4 (all).
Sales are grouped into amount classes, depending on the value (in the base currency) of the sale. Assume the sale falls into amount-class 2. We would then have to update the four counter values that correspond to this query:
SELECT * FROM countervalue WHERE project_id = 56 AND sale_status in (1,4) AND amountclassid in (2,0)
| CounterValue_id | contact_id | person_id | project_id | sale_status | amountClassId | ... |
|---|---|---|---|---|---|---|
| 40265 | 0 | 0 | 56 | 1 | 2 | |
| 40268 | 0 | 0 | 56 | 1 | 0 | |
| 40280 | 0 | 0 | 56 | 4 | 2 | |
| 40283 | 0 | 0 | 56 | 4 | 0 |
The counter values themselves are stored in:
- totalReg
- totalRegInPeriod
- notCompleted
- notCompletedInPeriod
- ...
Here is a larger extraction for the same project, but now also with sale_status 2 (sold) and amountclass 1 (small)
SELECT * FROM countervalue where project_id =47 and sale_status in (1,2,4) and amountclassid in (2,1,0)
SAINT values
Values are much easier than counters. They are simple binary values - either on or off.
To find all the SAINT values for a particular contact or project, simply search the StatusValue table:
Select * from statusvalue where contact_id =89
| StatusValue_id | StatusDef_id | contact_id | person_id | project_id | isSignalled | needsUpdate | registered |
|---|---|---|---|---|---|---|---|
| 3694 | 1 | 89 | 0 | 0 | 1 | 1 | 2021-11-05 15:54:10 |
| 3693 | 7 | 89 | 0 | 0 | 1 | 1 | 2021-11-05 15:54:10 |
The isSignalled field determines whether to display an image in the background of the contact card or not. The image that is shown on the contact card is determined by the status definition - and the blob linked to this record.
According to the StatusDef_id in the statusvalue table, the statusdef table is referred and the background image in the contact card varies accordingly.
Select * from statusdef where statusdef_id = 7
| StatusDef_id | isVisual | needsUpdate | rank | deleted | ownerTable | lastGenerated | ... |
|---|---|---|---|---|---|---|---|
| 7 | 1 | 0 | 1 | 0 | 5 | 2021-11-05 15:54:10 |
isVisual indicates that a picture is used to mark that the status is active. The actual picture is found via the blob link table.