Exchange the history of the stages of matches of the Enamour Pipeline:
To organize the process, two tables are automatically created:
- CRM_STAGE_HISTORY_%ENV%
- CRM_STAGE_LIST_%ENV%
%ENV% = PROD|TEST|DEV
The data relevant for work will be stored in tables ending with PROD.
Agents are used to perform data exchange with tables. This allows you to move the execution of scripts from the client session to the server session (analog of queues). And also, it guarantees the repetition of actions in case of errors that often appear due to loss of connection with the MSSQL server.
CRM_STAGE_LIST_PROD: The list table will store the list of stages (statuses). If the stages settings are changed (adding, renaming, reordering, deleting), the following actions will be performed:
- Event interception (can be disabled in the admin panel)
- Create an agent
- Agent execution
- Writing or changing data in a table
The initial unloading of data can be performed with the CLI command: php /home/bitrix/www/local/cli -c=Bulk -m=mssqlStatusUpdate
CRM_STAGE_LIST_PROD COLS::
- ID int identity primary key,
- ACTIVE In case of removal from Bitrix24, it takes the value N
- DATE_INSERT Date of entry in the table,
- DATE_UPDATE Last update date,
- ENTITY_ID Always the same, left for compatibility with the entire system,
- STATUS_ID The system identifier of the stage in Bitrix24, need to match it
- EXTERNAL_ID ID entries in Bitrix24,
- NAME Name given by the user,
- NAME_INIT Name given by Bitrix24,
- SORT Sequence number in the sequence of stages in Bitrix24,
- SYSTEM The stage was created by the system,
- SEMANTICS Left for unification,
- CATEGORY_ID Category, #3 corresponds to Enamour Pipeline
CRM_STAGE_HISTORY_PROD::
Working with this table consists of two parts:
- [UPLOADER] step by step uploading of all data
- [ACTUALIZER] control of changes in the stages of matches and uploading changes to the table
[UPLOADER] The initial unloading of all data can be performed by the CLI-method: php /home/bitrix/www/local/cli -c=Bulk -m=mssqlHistoryUpdate
After starting the method, the Bitrix24 agent is created. Every 2 minutes 50 items will be written to the table. A step-by-step verification of the record is carried out. This slows things down, but since there are relatively few records, it guarantees data delivery. Including when the connection to the server is lost.
[ACTUALIZER] After uploading all the data, the stage change control mode in Bitrix24 is activated. To avoid delays in work and error handling uploading data is also shifted to Bitrix24 agents. This mode can be disabled in the admin panel.
CRM_STAGE_HISTORY_PROD COLS::
- ID int identity primary key,
- EXTERNAL_ID ID entries in Bitrix24 (b_crm_deal_history),
- TYPE_ID all matches (deals) have ID 2,
- OWNER_ID match ID (deal),
- DATE_INSERT Date and time of writing to the current table,
- CREATED_TIME Date and time of stage change in Bitrix24,
- START_DATE Bitrix24 system field (just in case),
- END_DATE Bitrix24 system field (just in case),
- NAME Study Name,
- RESPONSIBLE_ID user in Bitrix24 who changed the stage,
- CATEGORY_ID Category, #3 corresponds to Enamour Pipeline,
- STAGE_SEMANTIC_ID Bitrix24 system field (just in case),
- STAGE_ID Stage ID (matching with STATUS ID in list table),
- IS_LOST Bitrix24 system field (just in case)
Indexes are automatically created during matching to speed things up.
Used files:
- local/modules/dev.tools/admin/settings/02.enamour.php
- local/lib/CLI/Bulk.php
- local/modules/dev.tools/events/events.php
- local/modules/dev.tools/lib/Handlers.php
- local/lib/ExternalTables/DB.php
- local/lib/ExternalTables/MSSQL.php
- local/lib/ExternalTables/Enamour/DealStageHistory.php
- local/lib/ExternalTables/Enamour/StagesList.php
- local/lib/Exchange/DealStageHistory.php
- local/lib/Exchange/StagesList.php
Automatic addition of new columns is supported, but this is not always painless with tablesthat already have data. Probably the best option would be to add the field manually. After that don't forget to add this field to getMap methods.
gitlab tag 1.1.5: https://gitlab.vidaselect.com/yuriyurev/bitrix.vidaselect.com/-/tags/v.1.1.5