CREATE DEFINER=`otrsuser`@`%` PROCEDURE `link_cmdb_ci_computer_to_ticket`(
	IN `in_ticket_id` INT,
	IN `in_rid_nr` VARCHAR(10),
    IN `in_user_name` VARCHAR(10)
)
BEGIN
/* Creates a link between a config item computer and a ticket.
 Function validates the input data and if the link exists
 Parameters:
 in_ticket_id: Ticket ID (not number) as string
 in_rid_nr:    ConfigItem (computer) name as string
 in_user_name: Otrs user login
 Return value: 0=success, 1=failed
 Example CALL otrs5.link_cmdb_ci_computer_to_ticket('82', 'RID422', 'otrsuser');
*/
DECLARE vconfigitem_id INT;
DECLARE vuser_id INT;
DECLARE vcount_linked_int INT;
DECLARE vcount_ticket_int INT;

SELECT configitem_id INTO vconfigitem_id FROM configitem_version WHERE name = in_rid_nr;
SELECT id INTO vuser_id FROM users WHERE login = in_user_name;

SELECT COUNT(*) INTO vcount_linked_int FROM link_relation WHERE
        source_object_id = 2        # source_object_id (2=Ticket)
    AND source_key = in_ticket_id
    AND target_object_id = 5        # target_object_id (5=ITSMConfigItem)
    AND target_key = vconfigitem_id
    AND type_id = 1;                # type_id (1=normal)
    
SELECT COUNT(*) INTO vcount_ticket_int FROM ticket WHERE id = in_ticket_id;

if vcount_linked_int = 0 and vuser_id != '' and vcount_ticket_int = 1 and vconfigitem_id != '' then
  INSERT INTO link_relation (source_object_id, source_key, target_object_id, target_key, type_id, state_id, create_time, create_by) VALUES 
  (2,               # source_object_id (2=Ticket)
   in_ticket_id,    # source_key
   5,               # target_object_id (5=ITSMConfigItem)
   vconfigitem_id,  # target_key
   1,               # type_id (1=normal)
   1,               # state_id (1=valid)
   now(),           # create_time
   vuser_id);       # create_by
   SELECT 0 as 'result';
else
   SELECT 1 as 'result';
end if;

END










CREATE DEFINER=`otrsuser`@`%` PROCEDURE `get_ticket_dynamic_field_value`(
	IN `in_ticket_id` INT,
	IN `in_dynamic_field_name` VARCHAR(20)
)
BEGIN
/* Queries the value of a tickets dynamic field
 Parameters:
 in_ticket_id: 			Ticket ID (not number) as string
 dynamic_field_name:    The name of the dynamic field
 Return value: Value of the dynamic field
 Example CALL otrs5.get_ticket_dynamic_field_value('82', 'RIDNr');
*/

if in_dynamic_field_name != '' and in_ticket_id != 0 then
  SELECT value_text from dynamic_field_value WHERE field_id=(SELECT id from dynamic_field WHERE name=in_dynamic_field_name) and object_id=in_ticket_id;
end if;


END