Differences

This shows you the differences between two versions of the page.

--- en:software:dashboard:dashboard_views [2016/01/11 15:50]
amanda.zens
+++ en:software:dashboard:dashboard_views [2021/07/01 09:52] (current)
@@ Line 1: / Line 1: @@
+====== TIM Datenbase Views ======
-====== T!M Database Views ======
+Before the dashboard can function properly, the appropriate views must be created. The most recent views can be found on the download server **[[http://download.tim-solutions.de|http://download.tim-solutions.de]]** and must be implemented correctly. As soon as the views have been created, the [[:en:software:dashboard:dashboard_tablestructure|TIM Tabellen structure]] may be used for analyses with the [[:en:software:dashboard|TIM Dashboard]].
-In order to effectively work with the Dashboard, appropriate views have to be generated. These views allow for the information from T!M to be used in a quick and easy way.
+<note important> If the views are used outside of the dashboard, no result will be returned because te views are coupled with the TIM-authentification.</note>
-The corresponding sections can be added to the MySQL Client by using copy-paste functions (Crtl + Enter). The script file may also be downloaded and then executed via the MySQL Server.
-<file sql tim_views.sql>
-CREATE DEFINER=`root`@`localhost` FUNCTION `currentClient`() RETURNS INT(11)
-    NO SQL
-    DETERMINISTIC
-RETURN @currentClient;
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_activity` AS SELECT
-`ni`.`ID_` AS `id`,
-`ni`.`NAME_` AS `name`,
-`ni`.`client_ID_` AS `clientId`,
-`ni`.`CREATION_TIME_` AS `creationTime`,
-`ni`.`creationUser_ID_` AS `creationUserId`,
-`ni`.`processInstance_ID_` AS `instanceId`,
-`ni`.`realStart` AS `start`,
-`ni`.`realEnd` AS `end`,
-`ni`.`loopCount` AS `loopCount`,
-`ni`.`duration` AS `duration`,
-`ni`.`escalationTime` AS `escalationTime`,
-`ni`.`estimatedEnd` AS `estimatedEnd`,
-`ni`.`desiredStart` AS `milestone`,
-`ni`.`desiredStartTime` AS `milestoneDate`,
-`ni`.`inTime` AS `inTime`,
-`ni`.`puffer` AS `timeBuffer`,
-`ni`.`remainingTime` AS `remainingTime`,
-`ni`.`calFAZ` AS `calculatedEST`,
-`ni`.`calFEZ` AS `calculatedEFT`,
-`ni`.`calSAZ` AS `calculatedLST`,
-`ni`.`calSEZ` AS `calculatedLFT`,
-`ni`.`numFAZ` AS `valueEST`,
-`ni`.`numFEZ` AS `valueEFT`,
-`ni`.`numSAZ` AS `valueLST`,
-`ni`.`numSEZ` AS `valueLFT` ,
-`node`.`CLASS_` AS `type`
-FROM ((`LOOM_NODEINSTANCE` `ni` JOIN `LOOM_NODE` `node` ON (`ni`.`node_ID_` = `node`.`ID_`))) WHERE (`ni`.`client_ID_` = `currentClient`());
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_definition` AS SELECT
-`LOOM_PROCESSDEFINITION`.`ID_` AS `id`,
-`LOOM_PROCESSDEFINITION`.`NAME_` AS `name`,
-`LOOM_PROCESSDEFINITION`.`CLIENT_` AS `clientId`,
-`LOOM_PROCESSDEFINITION`.`CREATION_TIME_` AS `creationTime`,
-`LOOM_PROCESSDEFINITION`.`CREATION_USER_` AS `creationUserId`,
-`LOOM_PROCESSDEFINITION`.`DESCRIPTION_` AS `description`,
-`LOOM_PROCESSDEFINITION`.`VERSION_` AS `version`,
-`LOOM_PROCESSDEFINITION`.`ESCALATIONSTATUS_` AS `escalationStatus`,
-`LOOM_PROCESSDEFINITION`.`OWNER_EXPRESSION_` AS `owner`,
-`LOOM_PROCESSDEFINITION`.`STARTER_EXPRESSION_` AS `starter`,
-`LOOM_PROCESSDEFINITION`.`DEPLOYER_EXPRESSION_` AS `deployer` ,
-`LOOM_PROCESSDEFINITION`.`ARCHIV_` AS `archived`
-FROM `LOOM_PROCESSDEFINITION` WHERE (`LOOM_PROCESSDEFINITION`.`CLIENT_` = `currentClient`());
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_effort` AS SELECT
-`eff`.`ID_` AS `id`,
-`eff`.`NAME_` AS `name`,
-`eff`.`client_ID_` AS `clientId`,
-`eff`.`CREATION_TIME_` AS `creationTime`,
-`eff`.`creationUser_ID_` AS `creationUserId`,
-`eff`.`CLASS` AS `effortType`,
-`eff`.`effort` AS `value`,
-`eff`.`description` AS `description`,
-`eff`.`costCenter_ID_` AS `costCenterId`,
-`ti`.`PROCINST_` AS `instanceId`,
-`eff`.`parentFolder_ID_` AS `parentFolderId`
-FROM (`LOOM_EFFORT` `eff` JOIN `LOOM_TASKINSTANCE` `ti` ON((`eff`.`parentFolder_ID_` = `ti`.`PARENT_FOLDER_`)))
-WHERE (`eff`.`client_ID_` = `currentClient`());
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_identity` AS SELECT
-`LOOM_IDENTITY`.`ID_` AS `id`,
-`LOOM_IDENTITY`.`NAME_` AS `name`,
-concat(`LOOM_IDENTITY`.`namelast`,' ',`LOOM_IDENTITY`.`namefirst`,' (',`LOOM_IDENTITY`.`NAME_`,')') AS `displayName`,
-`LOOM_IDENTITY`.`client_ID_` AS `clientId`,
-`LOOM_IDENTITY`.`CREATION_TIME_` AS `creationTime`,
-`LOOM_IDENTITY`.`creationUser_ID_` AS `creationUserId`,
-`LOOM_IDENTITY`.`CLASS` AS `identityType`,
-`LOOM_IDENTITY`.`email` AS `email`,
-`LOOM_IDENTITY`.`namefirst` AS `firstname`,
-`LOOM_IDENTITY`.`namelast` AS `lastname` ,
-`LOOM_IDENTITY`.`ARCHIV_` AS `archived` ,
-`LOOM_IDENTITY`.`blocked` AS `blocked` ,
-`LOOM_IDENTITY`.`parent_ID_` AS `parentID`,
-`LOOM_IDENTITY`.`user_ID_` AS `userID`,
-`LOOM_IDENTITY`.`userProfile_ID_` AS `userProfileId`
-FROM `LOOM_IDENTITY` WHERE (`LOOM_IDENTITY`.`client_ID_` = `currentClient`());
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_instance` AS SELECT
-`pi`.`ID_` AS `id`,
-`pi`.`NAME_` AS `name`,
-`pd`.`NAME_` AS `definitionName`,
-`pi`.`CLIENT_` AS `clientId`,
-`pi`.`CREATION_TIME_` AS `creationTime`,
-`pi`.`key_` AS `key`,
-`pi`.`CREATION_USER_` AS `creationUserId`,
-`pi`.`PROCESSDEFINITION_` AS `definitionId`,
-`pi`.`DESCRIPTION_` AS `instanceDescription`,
-`pi`.`CREATION_GROUP_` AS `creationGroup`,
-`pi`.`END_` AS `end`,
-`pi`.`ARCHIV_` AS `archiv`,
-`pi`.`ARCHIVATIONUSER_ID_` AS `archivationUserId`,
-`pi`.`ROOTTOKEN_` AS `rootToken`,
-`pi`.`SUPERPROCESSTOKEN_` AS `parentProcessToken`,
-`pi`.`NEXT_ESCALATIONTIME_` AS `nextEscalationTime`,
-`pi`.`processVariableIndex_ID_` AS `processVariableIndexId`,
-`pi`.`inTime` AS `inTime`,
-`pvi`.`field1` AS `index1`,
-`pvi`.`value1` AS `value1`,
-`pvi`.`field2` AS `index2`,
-`pvi`.`value2` AS `value2`,
-`pvi`.`field3` AS `index3`,
-`pvi`.`value3` AS `value3`,
-`pvi`.`field4` AS `index4`,
-`pvi`.`value4` AS `value4`,
-`pvi`.`field5` AS `index5`,
-`pvi`.`value5` AS `value5`,
-`pvi`.`field6` AS `index6`,
-`pvi`.`value6` AS `value6`,
-`pvi`.`field7` AS `index7`,
-`pvi`.`value7` AS `value7`,
-`pvi`.`field8` AS `index8`,
-`pvi`.`value8` AS `value8`,
-`pvi`.`field9` AS `index9`,
-`pvi`.`value9` AS `value9`,
-`pvi`.`field10` AS `index10`,
-`pvi`.`value10` AS `value10`
-FROM (((`LOOM_PROCESSINSTANCE` `pi` LEFT JOIN `LOOM_PROCESSVARIABLEINDEX` `pvi` ON (`pi`.`processVariableIndex_ID_` = `pvi`.`ID_`))) JOIN `LOOM_PROCESSDEFINITION` `pd` ON((`pi`.`PROCESSDEFINITION_` = `pd`.`ID_`)))
-WHERE (`pi`.`CLIENT_` = `currentClient`());
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_swimlane` AS SELECT
-`LOOM_SWIMLANEINSTANCE`.`ID_` AS `id`,
-`LOOM_SWIMLANEINSTANCE`.`NAME_` AS `name`,
-`LOOM_SWIMLANEINSTANCE`.`CLIENT_` AS `clientId`,
-`LOOM_SWIMLANEINSTANCE`.`PROCINST_` AS `instanceId`,
-`LOOM_SWIMLANEINSTANCE`.`ACTOR_` AS `actor`,
-`LOOM_SWIMLANEINSTANCE`.`POOLEDACTOR_` AS `pooledActor`
-FROM `LOOM_SWIMLANEINSTANCE` WHERE (`LOOM_SWIMLANEINSTANCE`.`CLIENT_` = `currentClient`());
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_task` AS SELECT
-`ti`.`ID_` AS `id`,
-`ti`.`NAME_` AS `name`,
-`ti`.`CLIENT_` AS `clientId`,
-`ti`.`CREATION_TIME_` AS `creationTime`,
-`ti`.`CREATION_USER_` AS `creationUserId`,
-`ti`.`PROCINST_` AS `instanceId`,
-`ti`.`DESCRIPTION_` AS `description`,
-`ti`.`START_` AS `start`,
-`ti`.`END_` AS `end`,
-`ti`.`ISOPEN_` AS `isOpen`,
-`ti`.`SWIMLANINSTANCE_` AS `swimlaneId`,
-`ti`.`ACTOR_` AS `actor`,
-`ti`.`POOLEDACTOR_` AS `pooledActor`,
-`ti`.`NODEINSTANCE_` AS `activity`,
-`ta`.`ISADHOC_` AS `isAdhoc`,
-`ti`.`PARENT_FOLDER_` AS `parentFolderId`,
-`ti`.`archiv_` AS `archiv`
-FROM (`LOOM_TASKINSTANCE` `ti` JOIN `LOOM_TASK` `ta` ON((`ti`.`TASK_` = `ta`.`ID_`))) WHERE (`ti`.`CLIENT_` = `currentClient`());
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_token` AS SELECT
-`tok`.`ID_` AS `id`,
-`tok`.`NAME_` AS `name`,
-`pi`.`CLIENT_` AS `clientId`,
-`tok`.`NODEINSTANCE_` AS `nodeInstance`,
-`tok`.`PROCESSINSTANCE_` AS `instanceId`,
-`tok`.`PARENT_` AS `parent`
-FROM (`LOOM_TOKEN` `tok` JOIN `LOOM_PROCESSINSTANCE` `pi` ON((`tok`.`PROCESSINSTANCE_` = `pi`.`ID_`))) WHERE (`pi`.`CLIENT_` = `currentClient`());
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_variable` AS SELECT
-`vi`.`ID_` AS `id`,
-`vi`.`NAME_` AS `name`,
-`pi`.`CLIENT_` AS `clientId`,
-`vi`.`PROCESSINSTANCE_` AS `instanceId`,
-`vi`.`Stringvalue_` AS `stringvalue`,
-`vi`.`label_` AS `label`
-FROM (`LOOM_VARIABLEINSTANCE` `vi` JOIN `LOOM_PROCESSINSTANCE` `pi` ON((`vi`.`PROCESSINSTANCE_` = `pi`.`ID_`))) WHERE (`pi`.`CLIENT_` = `currentClient`());
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_role` AS SELECT
-`LOOM_IDENTITY_ID_` AS `identityId`,
-`ROLE_ID_` AS `roleId`
-FROM `MN_IDENTITY_ROLE`;
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_systemconfiguration` AS SELECT
-`LOOM_SYSTEMCONFIGURATION`.`ID_` AS `id`,
-`LOOM_SYSTEMCONFIGURATION`.`NAME_` AS `name`,
-`LOOM_SYSTEMCONFIGURATION`.`CREATION_TIME_` AS `creationTime`,
-`LOOM_SYSTEMCONFIGURATION`.`LAST_MODIFICATION_TIME_` AS `lastModificationTime`,
-`LOOM_SYSTEMCONFIGURATION`.`ignoreLDAPAuthentification` AS `ignoreLDAPAuthentification`,
-`LOOM_SYSTEMCONFIGURATION`.`notifyAsignee` AS `notifyAsignee`,
-`LOOM_SYSTEMCONFIGURATION`.`notifyByMail` AS `notifyByMail`,
-`LOOM_SYSTEMCONFIGURATION`.`costCenter` AS `costCenter`,
-`LOOM_SYSTEMCONFIGURATION`.`department` AS `department`,
-`LOOM_SYSTEMCONFIGURATION`.`personnelNumber` AS `personnelNumber`,
-`LOOM_SYSTEMCONFIGURATION`.`phoneNumber` AS `phoneNumber`,
-`LOOM_SYSTEMCONFIGURATION`.`tableRowCount` AS `tableRowCount`,
-`LOOM_SYSTEMCONFIGURATION`.`timezoneOffset` AS `timezoneOffset`,
-`LOOM_SYSTEMCONFIGURATION`.`client_ID_` AS `client_ID_`,
-`LOOM_SYSTEMCONFIGURATION`.`lastModificationUser_ID_` AS `lastModificationUserId`,
-`LOOM_SYSTEMCONFIGURATION`.`parentFolder_ID_` AS `parentFolderId`,
-`LOOM_SYSTEMCONFIGURATION`.`defaultRepresentative_ID_` AS `defaultRepresentativeId`,
-`LOOM_SYSTEMCONFIGURATION`.`departmentChief_ID_` AS `departmentChiefId`,
-`LOOM_SYSTEMCONFIGURATION`.`supervisor_ID_` AS `supervisorId`,
-`LOOM_SYSTEMCONFIGURATION`.`companyId` AS `companyId`,
-`LOOM_SYSTEMCONFIGURATION`.`companyName` AS `companyName`,
-`LOOM_SYSTEMCONFIGURATION`.`country` AS `country`
-FROM `LOOM_SYSTEMCONFIGURATION` WHERE (`LOOM_SYSTEMCONFIGURATION`.`client_ID_` = `currentClient`());
-CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `view_costcenter` AS
-SELECT
-`psp`.`ID_` AS `id`,
-`psp`.`client_ID_` AS `clientId`,
-`psp`.`CREATION_TIME_` AS `creationTime`,
-`psp`.`creationUser_ID_` AS `creationUserId`,
-`psp`.`identifier` AS `name`,
-`psp`.`description` AS `description`,
-`psp`.`hourly` AS `hourly`,
-`psp`.`currency` AS `currency`
-FROM
-(`LOOM_COSTCENTER` `psp`)
-WHERE
-(`psp`.`client_ID_` = CURRENTCLIENT());
-</file>
-As soon these views have been created they can be used by the described [[dashboard_tablestructure|T!M table structure]] and analyzed using the [[en:software:dashboard|Dashboard]].

TIM Wiki / NEW TIM 6 Documentation

User Tools

Site Tools

Differences

Page Tools