The SISfm Local API provides developers with a range of methods that can be used to develop custom application code within the SISfm web application, for example:
Provides methods for accessing SISfm databases.
To use these methods, import the System.Data.Common namespace.
Errors are written to the HTTP response according to setting DbExcludeErrorDetails:
DbConnect(db As String) As DbConnection
Create a connection specified by the connection string in the setting named db. If db is "DbConnectionIFM" and this setting is undefined, setting DbConnectionAFM is used.
Use DbConnection Close() to close the connection after use.
DbExecute(cn As DbConnection, sql As String) As DbDataReader
Return a recordset from executing SQL SELECT statement sql.
Use DbDataReader Close() to close the recordset after use.
DbExecuteScalar(cn As DbConnection, sql As String) As Object
Return a single value from executing SQL SELECT statement sql, .
DbExecuteNonQuery(cn As DbConnection, sql As String) As Integer
Return the number of rows affected by executing SQL statement sql other than SELECT (e.g. INSERT, UPDATE, DELETE).
DbSqlGetInsertIdentity(cn As DbConnection, table As String, field As String) As String
Return the value of AUTOINCREMENT field for a previous SQL INSERT.INTO table.
DbDataSet(cn As DbConnection, sql As String) As DataSet
Return a System.Data.DataSet created by executing SQL SELECT statement sql.
DbText(text As String) As String
Return text with single-quotes doubled.
DbValue(text As String) As String
Return NULL if text is an empty string, otherwise 'text'.
DbStr(rs As DbDataReader, column As Object) As String
Return the value of column (index or name) in the current record, converted to a String.
DbBool(rs As DbDataReader, column As Object) As Boolean
Return True if the value of Boolean column is True.
DbTableExists(cn As DbConnection, table As String) As Boolean
Return True if table table exists.
DbColumnExists(cn As DbConnection, table As String, column As String, Optional ByRef cache As SortedList = Nothing) As Boolean
Return True if table table column column exists. The value of column may be a comma-separated list. Performance may be improved using cache, a list of previous tests.
DbIsIFM() As Boolean
Return True if setting "DbConnectionIFM" is defined.
DbName(name As String) As String
Return name replacing sisfm by afm, for interoperability between SISfm and Archibus databases. Use for tables sisfm_docs sisfm_docvers sisfm_dwgs sisfm_flds sisfm_groupsforroles sisfm_tbls sisfm_users and fields sisfm_flds.sisfm_size sisfm_flds.sisfm_type.
DbGetType(cn As DbConnection) As Integer
Return the database type (SQL Server, Oracle, MS Access). See constants DBTYPE_*.
DbSqlOpConcat(cn As DbConnection) As String
Return the database string concatenation operator.
DbSqlFunUpper(cn As DbConnection, expression As String) As String
Return the database upper-case function with expression as argument.
DbSqlFunStr(cn As DbConnection, expression As String) As String
Return the database left-trim and string conversion functions with expression as argument.
DbSqlFunInteger(cn As DbConnection, expression As String) As String
Return the database integer conversion function with expression as argument.
DbSqlFunSubstring(cn As DbConnection, expression As String, start As Integer, length As Integer) As String
Return the database substring function with expression, start and length as arguments.
DbSqlFunIIF(cn As DbConnection, condition As String, resultIfTrue As String, resultIfFalse As String) As String
Return the database IIF function or CASE clause with condition, resultIfTrue and resultIfFalse as arguments.
DbDateConstant(cn As DbConnection, value As DateTime, includeTime As Boolean) As String
Return a database date value for value, optionally including the time.
DbUpdateLogging(cn As DbConnection)
Specify database update logging, before using DbExecuteNonQuery with an INSERT, UPDATE, DELETE statement.
The SISfm database schema is defined by tables sisfm_tbls and sisfm_flds.
See constants SISFM_DATA_TYPE_* and SISFM_FIELD_TYPE_*.
DbGetTableIsView(cn As DbConnection, table As String) As Boolean
Return True if a table is a database view.
DbGetTableTitle(cn As DbConnection, table As String) As String
Return the title of a table.
DbGetFieldTitle(cn As DbConnection, table As String, field As String) As String
Return the title of a field of a table.
DbFormatFieldTitle(ml_heading As String) As String
Return field title ml_heading as a single-line string.
DbGetDescField(cn As DbConnection, table As String, defaultField As String) As String
Return the name of the field in table that has the Desc field type (sisfm_flds.sisfm_type=2055), or defaultField if none.
DbGetEnumListFromRefTable(cn As DbConnection, table As String, column As String, ref_table As String) As String
Return the values of reference table ref_table of column of table in sisfm_flds.enum_list format.
DbFldsGet(cn As DbConnection, table As String, fields As ArrayList, ByRef numAutoIncrement As Integer, ByRef pkColName As ArrayList, ByRef pkIsAutoIncrement As Boolean, ByRef pkIsInteger As Boolean) As SortedList
Return a list of schema attributes of fields of table. All fields are returned if fields is Nothing.
Field attributes are a SortedList with keys "ml_heading" "primary_key" "allow_null" "data_type" "sisfm_size" "sisfm_type" "decimals" "min_val" "max_val" "enum_list" "ref_table" "dflt_val" "dep_cols"
Also returned are:
DbFldGetAttribute(dbFlds As SortedList, field As String, attribute As String) As Object
Return the value of attribute for field in schema attributes dbFlds.
DbFldSetAttribute(dbFlds As SortedList, field As String, attribute As String, value As Object)
Set the value of attribute for field in schema attributes dbFlds.
DbGetDataType(fieldType As String) As Integer
Return the "data_type" for the "sisfm_type" fieldType
DbGetDataTypeDesc(dataType As String) As String
Return the description of "data_type" dataType
DbFldsValidate(cn As System.Data.Common.DbConnection, dbFlds As SortedList, type As String, table As String, fields As ArrayList, values As ArrayList) As String
Validates values of fields of table according to its schema attributes dbFlds and a type whose value may be:
Return zero or more of the following HTML <br/> separated messages:
DbDocIsPhoto(ext As String) As Boolean
Return True if lower-cased ext is one of .jpeg, .jpg, .gif, .png.
DbDocLocked(cn As DbConnection, pkey As String) As Boolean
Return True and issue the following script alert message if sisfm_docs.locked is True where table_name is 'docs_assigned' and field_name is 'doc' and pkey_value is 'pkey':
DbDocUpload(files() As HttpPostedFile, name As String, description As String, expiry As String, tableName As String, idName As ArrayList, idValue As ArrayList, Optional pkey As String = "")
Uploads posted files with their name, description and expiry date for item specified by value idValue of PK field idName of table tableName, or otherwise issues one of the following script alert messages:
Calls DbDocAdd passing pkey to it.
DbDocAdd(cn As DbConnection, idName As ArrayList, idValue As ArrayList, name As String, description As String, expiry As String, doc As String, ms As MemoryStream, Optional pkey As String = "")
Add document with content ms as for DbDocUpload.
Photo EXIF orientation is replaced by content rotation. The values of name description doc are truncated if they would exceed field widths. If adding a new photo version, its thumbnail is deleted. A photo thumbnail is inserted if one does not exist.
Calls DbDocInsert passing pkey to it.
DbDocInsert(cn As DbConnection, idName As ArrayList, idValue As ArrayList, name As String, description As String, expiry As String, doc As String, ms As MemoryStream, Optional pkey As String = "") As Integer
Add document with content ms as for DbDocAdd.
If pkey is specified, it is inserted as a new version of an existing document with that docs_assigned.pkey_value.
DbDocGetThumbnail(ms As MemoryStream, ext As String) As MemoryStream
Return the content of a photo thumbnail for a photo whose content is ms and type is ext (one of .jpeg, .jpg, .gif, .png).
(other location code methods not requiring database access are elsewhere)
DbGetLocCode(cn As DbConnection, blId As String, flId As String, rmId As String) As String
Return the location code for building blId floor flId room rmId (refer setting LocCodeFormat).
DbGetBuildingId(cn As DbConnection, siteId As String, buildNo As String, Optional DT As String = "") As String
Return the Building Code bl.bl_id for Building Number buildNo (bl.build_no) on site siteId, optionally for facility type DT.
DbRoomClasses(cn As DbConnection, oper As String, className As String) As String
Return a SQL WHERE clause, preceded by oper ("AND" or "OR") to filter rows in table rm according to the settings RoomClassclassName where className is one of "Occupant" "Session" "Meeting" "General".
DbGetRoomCapacity(cn As DbConnection, locCode As String) As Double
Return capacity of room locCode according to settings RoomCapacityField, PersonModule, PersonArchibusMode, PersonRoomZoneAutomatic.
DbGetAmenityTypes(cn As DbConnection, featureTable As String, featureField As String) As ArrayList
Return list of amenity types for feature field featureField of table featureTable.
DbGetAmenity(cn As DbConnection, featureTable As String, locCode As String, assetId As String, featureField As String, ByRef amenityValue As ArrayList, ByRef amenityQuantity As ArrayList)
Return amenityValue and amenityQuantity lists for a site, building, floor, room (locCode) or asset (assetId).
See Access Security.
Methods for multiple items are given an ArrayList of their identifiers and return it with inaccessible items removed.
DbSecurityGetFunctionAccess(ByRef functions As ArrayList, Optional accessIfMissing As Boolean = True)
DbSecurityFunctionAccessible(functionName As String, Optional accessIfMissing As Boolean = True) As Boolean
Return accessible functions (table sisfm_functions), or True if functionName is accessible. If accessIfMissing is True, a function not in the table is accessible.
DbSecurityGetFieldAccess(table As String, ByRef fields As ArrayList)
DbSecurityGetFieldEditAccess(table As String, ByRef fields As ArrayList)
Return accessible, or editable, fields of table.
DbSecurityGetLayerAccess(locCode As String, ByRef layers As ArrayList)
DbSecurityGetLayerEditAccess(locCode As String, ByRef layers As ArrayList)
Return accessible layers of site or floor plan locCode.
DbSecurityGetReportAccess(ByRef paths As ArrayList)
Return accessible reports, specified by their report definition files paths omitting file extensions.
DbSecurityGetLinkAccess(linkTable As String, ByRef links As ArrayList)
Return accessible links in table linkTable (refer settings SiteLinkTable etc).
DbSecurityGetSiteAccess(ByRef siteId As ArrayList)
Return accessible sites (table site).
DbSecurityGetBuildingAccess(ByRef blId As ArrayList, Optional DT As String = "")
DbSecurityGetBuildingAccessByLocCode(locCode As String, Optional DT As String = "") As Boolean
DbSecurityGetBuildingQuickAccess(cn As DbConnection, ByRef blId As ArrayList, Optional DT As String = "")
Return accessible buildings (table bl or DT_bl), or True if building locCode is accessible.
DbSecurityGetFloorAccess(blId As String, ByRef flId As ArrayList, Optional DT As String = "")
DbSecurityGetFloorAccessByLocCode(locCode As String, Optional DT As String = "") As Boolean
Return accessible floors (table fl or DT_fl), or True if floor locCode is accessible.
DbSecurityGetRoomAccess(ByRef locCode As ArrayList, Optional DT As String = "")
DbSecurityGetRoomAccessByLocCode(locCode As String, Optional DT As String = "") As Boolean
DbSecurityGetRoomQuickAccess(cn As DbConnection, ByRef locCode As ArrayList, Optional DT As String = "")
Return accessible rooms (table rm or DT_rm), or True if room locCode is accessible.
DbSecurityGetAssetAccess(assetTable As String, assets As ArrayList)
DbSecurityGetAssetAccessById(assetTable As String, assetId As String) As Boolean
Return accessible assets in table assetTable, or True if assetId is accessible.
DbSecurityGetPersonAccess(ByRef emId As ArrayList)
Return accessible people (table em).
DbSecurityGetRoomBookingAccess(ByRef locCodeBookable As ArrayList)
Return bookable rooms (table rm) by their location codes locCodeBookable.
DbSecurityGetPersonAdminAccessByAdminCode(adminCode As String) As Boolean
Return True if adminCode is accessible (table sisfm_personadmin) where adminCode is up to three bu_id, dv_id and dp_id values separated by the "|" character.
DbSecurityGetRoomBookingAdminAccess(ByRef locCodeBookAdmin As ArrayList)
DbSecurityGetRoomBookingAdminAccessByLocCode(locCode As String) As Boolean
Return True if bookings can be notified and cancelled for room locCode (table sisfm_roombookadmin).
DbSecurityGetWorkFlowActionAccess(workFlowId As String, status As String, actionName As String) As Boolean
Return True if action actionName for work request status status of work flow workFlowId is accessible (table work_flow_actions).
Provides methods for calling the REST API of another web application.
To use these methods, set the following session variables, where authSettingsBase is an arbitrary identification of the other web application, and is passed to these methods:
ApiGet(authSettingsBase As String, endpoint As String, formData As NameValueCollection) As XmlDocument
Get data from endpoint according to formData values as parameters.
ApiPostForm(authSettingsBase As String, endpoint As String, formData As NameValueCollection) As XmlDocument
Post formData values to endpoint.
ApiPostFile(authSettingsBase As String, endpoint As String, file As FileInfo) As XmlDocument
Post the file file to endpoint.