ticket Table (262)
This table contains the tickets (requests) of the system. Its purpose should be evident.
Fields
| Name | Description | Type | Null |
|---|---|---|---|
| id | The primary key (auto-incremented) | PK | |
| connect_id | If a ticket is connected to another ticket, this field is set to the id of the 'master' ticket. | FK ticket | |
| title | The title of the ticket. | String(255) | |
| created_at | When the ticket was created. | DateTime | |
| last_changed | The last time the ticket was modified. | DateTime | ● |
| activate | When the ticket should be activated, if it is postponed. | DateTime | ● |
| closed_at | When the ticket was closed. | DateTime | ● |
| created_by | The id of the user who created the ticket. 1 (system user) if the ticket was created externally | FK ejuser | |
| author | A string representing the author of the ticket (same as author of first message). | String(255) | ● |
| owned_by | The id of the user who owns the ticket. 1 (system user) if the ticket is unassigned. | FK ejuser | |
| category | The id of the category a ticket is in. | FK ej_category | |
| orig_category | The id of the category the ticket was posted to. | FK ej_category | ● |
| slevel | The securitylevel of the ticket. | Enum TicketSecurityLevel | ● |
| status | The status of the ticket. I.e. active/closed/postponed/deleted | Enum TicketBaseStatus | |
| ticket_status | User defined ticket status | FK ticket_status | |
| cust_id | The reference to the customer. NULL or -1 if ticket is not connected to customer. | FK person | |
| priority | The reference to the ticket_priority table. | FK ticket_priority | |
| orig_priority | The reference to the initial priority for the ticket. | FK ticket_priority | ● |
| alert_level | The alert level for the ticket. Matches the level value of the ticket_alert table. | Short | ● |
| alert_timeout | The datetime for when the ticket should jump to the next alert_level. | DateTime | ● |
| alert_stop | If the esclatation was stopped, this fields indicates how many seconds left before the next escalation level. -1 if escalation is running. | Int | |
| read_by_owner | The datetime for when the ticket last was read by the owner. | DateTime | ● |
| first_read_by_owner | The datetime for when the ticket first was read by the current owner. | DateTime | ● |
| first_read_by_user | The datetime for when the ticket first was read by a user. | DateTime | ● |
| read_by_customer | The datetime for when the ticket was read by the customer. | DateTime | ● |
| filter_id | The reference to the mailbox from which the ticket was created. NULL or -1 if the ticket was not created by a mailbox. | FK mail_in_filter | |
| display_filter | The name of the mailbox from which the ticket was created. Only for displaypurposes | String(255) | ● |
| replied_at | The datetime for when the ticket was replied to. I.e. the first external message added to the ticket. | DateTime | ● |
| time_to_reply | The time (minutes) between when the ticket was created and when it was replied to. Calculated based on priority's timeframe. | Int | ● |
| time_to_close | The time (minutes) between when the ticket was created and when it was closed. Calculated based on priority's timeframe. | Int | ● |
| real_time_to_reply | Same as time_to_reply, but not calculated based on priority. | Int | ● |
| real_time_to_close | Same as time_to_close, but not calculated based on priority. | Int | ● |
| read_status | Whether the owner has read the ticket or not (red, yellow, green). | Enum TicketReadStatus | |
| has_attachment | Boolean indicating if this ticket has one or more attachments. | Bool | ● |
| deadline | Deadline for ticket. | DateTime | ● |
| filter_address | Address of receiving filter (mail box) | String(255) | ● |
| dbi_agent_id | Integration agent (eJournal) | FK dbi_agent | |
| dbi_key | The primary key for the integrated entry in the external datasource. | String(255) | |
| dbi_last_syncronized | Last external synchronization. | DateTime | |
| dbi_last_modified | When the entry was last modified. | DateTime | |
| origin | What is the origin of this ticket | Enum TicketOrigin | ● |
| time_spent_internally | The total time (seconds) within the priority’s office hours the ticket has been in an open status (configurable), not including current state | Int | ● |
| time_spent_externally | The total time (seconds) within the priority’s office hours the ticket has been in a external waiting status (configurable), not including current state | Int | ● |
| time_spent_queue | The total time (seconds) within the priority’s office hours the ticket has been in a queue status, not including current state | Int | ● |
| real_time_spent_internally | The total time (seconds) within 24x7 the ticket has been in an open status (configurable), not including current state | Int | ● |
| real_time_spent_externally | The total time (seconds) within 24x7 the ticket has been in a external waiting status (configurable), not including current state | Int | ● |
| real_time_spent_queue | The total time (seconds) within 24x7 hours the ticket has been in a queue status, not including current state | Int | ● |
| time_spent_calculated | When the time_spent value was last calculated. | DateTime | ● |
| num_replies | The number of replies (messages) to the customer for this request. | Int | ● |
| num_messages | The total number of messages for this request. | Int | ● |
| from_address | The from-address used when this ticket got created, e.g. by email | String(4000) | ● |
| tags | Array of references to the Tags records | FKArray | ● |
| contact_id | The company of the person in the cust_id field, if that person belongs to a company | FK contact | ● |
| language | The language of the first external message | String(10) | ● |
| sentiment | The sentiment index of the last external message | Int | ● |
| sentimentConfidence | The sentiment confidence of the last external message | Int | ● |
| form_submission_id | If this ticket was created from a form submission, this field will point to that record | FK form_submission | ● |
| created_by_workflow_id | The workflow this request/ticket was created by | FK workflow | ● |
| suggestedCategory_id | Suggestion for categorization, based on the text of the message (AI) | FK ej_category | |
| origHumanCategory_id | Will contain the category id selected by the user, when having the choice of using the suggested category or manually selecting a category | FK ej_category | |
| sale_id | Reference to sale table | FK sale | ● |
| project_id | Reference to project table | FK project | ● |
| time_spent | The total time (minutes). Aggregated time spent from ticket's messages. Read-only for external use. | Int | |
| ticket_type | Tickettype of the ticket | FK ticket_type |
Communication channel leading to ticket being created
| Origin | Comment | |---|---| | 0 | We just have no idea. Maybe it was too long ago | | 1 | Email | | 2 | SMS | | 3 | Telefacsimile | | 4 | Phone call to human operator | | 5 | Facebook wall | | 6 | Tweet tweet | | 7 | Internal, by human operator | | 8 | Direct by customer through Customer Centre web pages | | 9 | Auto-generated from e-marketing link | | 10 | Automatic processes in Service |
Status of a ticket / request
This is the internal value. The user-defined ticket status is saved to ticket.ticket_status
| Status | Comment |
|---|---|
| 0 | Unknown / uninitialized |
| 1 | Request is currently active |
| 2 | Request has been closed |
| 3 | Request has been postponed |
| 4 | Deleted |
| 5 | Request has been merged with another request. See connect_id |
Indexes
| Fields | Types | Description |
|---|---|---|
| id | PK | Clustered, Unique |
| connect_id | FK | Index |
| created_at | DateTime | Index |
| created_by | FK | Index |
| owned_by | FK | Index |
| category | FK | Index |
| orig_category | FK | Index |
| status | Enum | Index |
| ticket_status | FK | Index |
| cust_id | FK | Index |
| priority | FK | Index |
| orig_priority | FK | Index |
| alert_timeout | DateTime | Index |
| filter_id | FK | Index |
| read_status | Enum | Index |
| dbi_agent_id | FK | Index |
| dbi_key | String(255) | Index |
| dbi_last_syncronized | DateTime | Index |
| dbi_last_modified | DateTime | Index |
| tags | FKArray | Full text |
| created_by_workflow_id | FK | Index |
| ticket_type | FK | Index |
Relationships
| Table | Description |
|---|---|
| chat_session | This table contains chat sessions. |
| contact | Companies and Organizations. |
| dbi_agent | DBI agent settings |
| ej_category | This table contains categories, in which tickets are categorized. The categories are organized in a hierarchial manner. |
| ej_message | This table contains the messages listed under tickets. |
| ejuser | This table contains entries for the users of the system. |
| form_submission | A form submission |
| hotlist | The table is used by the hotlist to store the relationship between tickets and users |
| mail_in_filter | This table contains entries for the mailboxes the eJournal system is fetching mail from (POP3 or IMAP). |
| notify | This table contains the pop-up messages displayed for users for various events, such as 'new ticket', etc. |
| outbox | Outgoing emails with sending status and other info |
| person | Persons |
| project | Projects |
| s_shipment_addr | Addresses that are ready to be sent in a shipment. |
| sale | Sales For every Sale record edited through the SuperOffice GUI, a copy of the current version of the record will be saved in the SaleHist table. This also applies to editing done through the SaleModel COM interface, but not to editing done through the OLE DB Provider or other channels. |
| Tags | MDO List of tags for Service entities |
| ticket | This table contains the tickets (requests) of the system. Its purpose should be evident. |
| ticket_customers | This table allows several customers to be connected to several tickets (many-to-many) |
| ticket_log | This table contains log entries for the tickets. |
| ticket_log_action | This table contains actions for the tickets. |
| ticket_log_change | This table contains log entries for the tickets. |
| ticket_priority | This table contains the ticket priorities. |
| ticket_status | This table user defined ticket status values. |
| ticket_status_history | This table contains the history of a tickets statuses. Each time a ticket changes status a copy of the previous status of the record will be saved |
| ticket_type | A ticket (request) type |
| workflow | SuperOffice specific info about a workflow |
Replication Flags
- None
Security Flags
- Sentry controls access to items in this table using user’s Role and data rights matrix.