Scalar functions

Scalar function	Scheme	Description
DefaultSlaId	dbo	Returns the default SLA for on-duty requesters.
fAddressString	dbo	Formats the address properties from tAddress to one string.
fDate	dbo	Returns date without time only.
fDateHoursMinutes	dbo	Cut-off of seconds from date and time records.
fDeadline	dbo	Calculates deadline in the service operating hours.
fHtmlString	dbo	Encodes a string as valid HTML, including ends of lines.
fIsAfterDeadline	dbo	Checks whether the actual moment is after the deadline in the service operating hours.
fLastLevelDomain	dbo	Returns the last level of the domain.
fnCheckRelCycles	dbo	Checks the circular task link between tickets.
fnCheckRelCyclesLicHist	dbo	Function for reviewing a circular task.
fnCheckRelCyclesNode	dbo	Function for reviewing a circular task.
fnCheckRelDuplicity	dbo	Checks the link between tickets for duplicity.
fnCheckRelOneToMany	dbo	Checks whether the ticket has maximum one link of the 1:N type.
fnCreateColumnName	dbo	Transforms the string to usable string for database column name.
fnCutSymbolsFromQuery	dbo	Returns a string without unnecessary symbols.
fnFileStringInfoCsvSignature	dbo	CSV file signature from FileStringInfo part.
fnFindAssetUserFromPerson	dbo	According to PersonId of the user in Administration, NodeId finds the user in the objects tree in AM.
fnFloatHourToTimeString	dbo	Converts the value of hours from a floating point number into a text string in the h:mm format.
fnFloatToCharExactLength	dbo	The function returns a number (float) as a string with the set minimum length of the integer part and the maximum length of the fractional part, which is guaranteed by completing with zeroes, or rounding where applicable. The maximum length is 50, which is an appropriately selected constant.
fnGetBackupKindName	dbo	Suffix of properties with original data in which the conversion to another data type failed.
fnGetDateDiffInOpeningHours	dbo	Custom difference in hours between two dates in given operating hours.
fnGetNextSectionId	dbo
fnGetNextTicketId	dbo	Function returns id of newly created ticket.
fnGetSlaByImpactUrgency	dbo	Finds best SLA for a given requester and service based on impact and urgency items.
fnGetUnreadCount	dbo	Number of unread events of the person on ticket.
fnHdSectionWithoutLast	dbo	The function returns the full service name (path) excluding the name of the service
fnLastLocalMidnightUtc	dbo	The calculation of the last local midnight according to UTC time and the time zone.
fnLocalTime	dbo	Converts UTC data to the specific time zone.
fnLocalTimeString	dbo	Converts UTC data to the specific time zone and returns as a string formated in specified language.
fnNextLocalMidnightUtc	dbo	The calculation of the following local midnight according to UTC time and the time zone.
fnPersonLoginWithoutDomain	dbo	Username of a person without a domain.
fnReportedTime	dbo	The calculation of time reported on the selected day and for the selected employee.
fnStartWorkTime	dbo	The calculation of the time at which the selected employee began working on the selected day.
fnTimeZoneOffset	dbo	Returns the deviation in hours and minutes of the time zone from UTC at a given time.
fnTodayHour	dbo	Converts a floating point number into the item type date and time where date is today's date and time is transferred from the parameter.
fnUtcTime	dbo	UTC time calculated from the local time and time zones.
fnWorkTimeAtDay	dbo	The calculation of the worked on the selected day for the selected employee.
fnWorkTimeToday	dbo	The calculation of the time at which the selected employee began working on the selected day. The calculation does not consider the end of the day, only up to "now"
fRelatedCompany	dbo	Ticket - "Related Organizations" item
fStripDomain	dbo	Username of a user without a domain.
fSuggestResolutionDate	dbo	Default due date by SLA.
GetProperty	dbo	Returns 1 value from the tProperty table based on the set code.

dbo.DefaultSlaId

Returns the default SLA for on-duty requesters.

Parameters

Name	Type	Description
@iHdSectionId	int
@iPersonId	int

dbo.fAddressString

Formats the address properties from tAddress to one string.

Parameters

Name	Type	Description
@addressId	int

dbo.fDate

Returns date without time only.

Parameters

Name	Type	Description
@datetime	datetime

dbo.fDateHoursMinutes

Cut-off of seconds from date and time records.

Parameters

Name	Type	Description
@d	datetime

dbo.fDeadline

Calculates deadline in the service operating hours.

Parameters

Name	Type	Description
@liOpeningHours	int
@dFrom	datetime
@nHours	float

dbo.fHtmlString

Encodes a string as valid HTML, including ends of lines.

Parameters

Name	Type	Description
@s	nvarchar(4000)

dbo.fIsAfterDeadline

Checks whether the actual moment is after the deadline in the service operating hours.

Parameters

Name	Type	Description
@liOpeningHours	int
@dFrom	datetime
@nHours	float

dbo.fLastLevelDomain

Returns the last level of the domain.

Parameters

Name	Type	Description
@LoginWithDomain	nvarchar(255)

dbo.fnCheckRelCycles

Checks the circular task link between tickets.

Parameters

Name	Type	Description
@trId	int
@ticketBegin	int
@ticketEnd	int
@relTypeId	int

dbo.fnCheckRelCyclesLicHist

Function for reviewing a circular task.

Parameters

Name	Type	Description
@nrId	int
@beginLicHistId	int
@endLicHistId	int
@relTypeId	int

dbo.fnCheckRelCyclesNode

Function for reviewing a circular task.

Parameters

Name	Type	Description
@nrId	int
@beginNodeId	int
@endNodeId	int
@relTypeId	int

dbo.fnCheckRelDuplicity

Checks the link between tickets for duplicity.

Parameters

Name	Type	Description
@trId	int
@ticketBegin	int
@ticketEnd	int
@relTypeId	int

dbo.fnCheckRelOneToMany

Checks whether the ticket has maximum one link of the 1:N type.

Parameters

Name	Type	Description
@trId	int
@ticketBegin	int
@ticketEnd	int
@relTypeId	int

dbo.fnCreateColumnName

Transforms the string to usable string for database column name.

Parameters

Name	Type	Description
@str	nvarchar(255)	Any string to the transgform.

dbo.fnCutSymbolsFromQuery

Returns a string without unnecessary symbols.

Parameters

Name	Type	Description
@sample	nvarchar(max)

dbo.fnFileStringInfoCsvSignature

CSV file signature from FileStringInfo part.

Parameters

Name	Type	Description
@id	int

dbo.fnFindAssetUserFromPerson

According to PersonId of the user in Administration, NodeId finds the user in the objects tree in AM.

Parameters

Name	Type	Description
@personId	int

dbo.fnFloatHourToTimeString

Converts the value of hours from a floating point number into a text string in the h:mm format.

Parameters

Name	Type	Description
@hours	float

dbo.fnFloatToCharExactLength

The function returns a number (float) as a string with the set minimum length of the integer part and the maximum length of the fractional part, which is guaranteed by completing with zeroes, or rounding where applicable. The maximum length is 50, which is an appropriately selected constant.

Parameters

Name	Type	Description
@value	float
@intLen	int
@decLen	int

dbo.fnGetBackupKindName

Suffix of properties with original data in which the conversion to another data type failed.

Parameters

Name	Type	Description
@intKindId	int

dbo.fnGetDateDiffInOpeningHours

Custom difference in hours between two dates in given operating hours.

Parameters

Name	Type	Description
@from	datetime	Date from (UTC)
@to	datetime	Date until (UTC)
@openingHoursId	int	Identifier for operating hours

dbo.fnGetNextSectionId

Parameters

dbo.fnGetNextTicketId

Function returns id of newly created ticket.

Parameters

dbo.fnGetSlaByImpactUrgency

Finds best SLA for a given requester and service based on impact and urgency items.

Parameters

Name	Type	Description
@impactColumnValueId	int	Impact ID, value iColumnValueId from table tColumnValue.
@urgencyColumnValueId	int	Urgency ID, value iColumnValue from table tColumnValue
@hdSectionId	int	Service ID
@requesterPersonId	int	Requester ID

dbo.fnGetUnreadCount

Number of unread events of the person on ticket.

Parameters

Name	Type	Description
@PersonId	int
@TicketId	int

dbo.fnHdSectionWithoutLast

The function returns the full service name (path) excluding the name of the service

Parameters

Name	Type	Description
@SectionId	int

dbo.fnLastLocalMidnightUtc

The calculation of the last local midnight according to UTC time and the time zone.

Parameters

Name	Type	Description
@utc	datetime
@tzuid	nvarchar(128)

dbo.fnLocalTime

Converts UTC data to the specific time zone.

Parameters

Name	Type	Description
@utc	datetime
@tzuid	nvarchar(128)

dbo.fnLocalTimeString

Converts UTC data to the specific time zone and returns as a string formated in specified language.

Parameters

Name	Type	Description
@utc	datetime
@tzuid	nvarchar(128)
@localeId	int

dbo.fnNextLocalMidnightUtc

The calculation of the following local midnight according to UTC time and the time zone.

Parameters

Name	Type	Description
@utc	datetime
@tzuid	nvarchar(128)

dbo.fnPersonLoginWithoutDomain

Username of a person without a domain.

Parameters

Name	Type	Description
@sPersonLogin	nvarchar(255)

dbo.fnReportedTime

The calculation of time reported on the selected day and for the selected employee.

Parameters

Name	Type	Description
@utc	datetime
@tzuid	nvarchar(128)
@personId	int

dbo.fnStartWorkTime

The calculation of the time at which the selected employee began working on the selected day.

Parameters

Name	Type	Description
@utc	datetime
@tzuid	nvarchar(128)
@personId	int

dbo.fnTimeZoneOffset

Returns the deviation in hours and minutes of the time zone from UTC at a given time.

Parameters

Name	Type	Description
@utc	datetime	Time in UTC.
@tzuid	nvarchar(128)	Time zone identifier, see dbo.TimeZone.uid.

dbo.fnTodayHour

Converts a floating point number into the item type date and time where date is today's date and time is transferred from the parameter.

Parameters

Name	Type	Description
@hour	float

dbo.fnUtcTime

UTC time calculated from the local time and time zones.

Parameters

Name	Type	Description
@local	datetime
@tzuid	nvarchar(128)

dbo.fnWorkTimeAtDay

The calculation of the worked on the selected day for the selected employee.

Parameters

Name	Type	Description
@utc	datetime
@tzuid	nvarchar(128)
@personId	int

dbo.fnWorkTimeToday

The calculation of the time at which the selected employee began working on the selected day. The calculation does not consider the end of the day, only up to "now"

Parameters

Name	Type	Description
@utc	datetime
@tzuid	nvarchar(128)
@personId	int

dbo.fRelatedCompany

Ticket - "Related Organizations" item

Parameters

Name	Type	Description
@iHdTicketId	int

dbo.fStripDomain

Username of a user without a domain.

Parameters

Name	Type	Description
@LoginWithDomain	nvarchar(255)

dbo.fSuggestResolutionDate

Default due date by SLA.

Parameters

Name	Type	Description
@Ticketid	int

dbo.GetProperty

Returns 1 value from the tProperty table based on the set code.

Parameters

Name	Type	Description
@key	nvarchar(255)