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.