Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- software:dashboard:dashboard_views [2014/05/27 10:31]
stefan.barth
+++ software:dashboard:dashboard_views [2021/07/01 09:52] (aktuell)
@@ Zeile 1: / Zeile 1: @@
-====== T!M Datenbank Views ======
+====== TIM Datenbank Views ======
-Um mit dem Dashboard arbeiten zu können, müssen zuerst die entsprechenden Views angelegt werden, die die T!M Daten aufbereiten und einen einfacheren und schlankeren Zugriff gewähren.
+Um mit dem Dashboard arbeiten zu können, müssen zuerst die entsprechenden Views angelegt werden. Die aktuellen Views finden können auf dem Download-Server **http://download.tim-solutions.de** entnommen werden und müssen entsprechend eingespielt werden. Sobald die Views angelegt sind, können diese mit Hilfe der beschriebenen [[dashboard_tablestructure|TIM Tabellenstruktur]] für Auswertungen mit dem [[software:dashboard|TIM Dashboard]] verwendet werden.
-Die einzelnen Abschnitte können entweder einfach in den entsprechenden MySQL Client kopiert und ausgeführt werden (Strg + Enter). Alternativ, kann das Script herunter geladen werden und auf dem MySQL Server ausgeführt werden.
-<file sql tim_views.sql>
-CREATE DEFINER=`root`@`localhost` FUNCTION `currentClient`() RETURNS INT(11)
-    NO SQL
-    DETERMINISTIC
-RETURN @currentClient;
+<note important>Sollten die Views ausserhalb vom Dashboard genutzt werden, wird kein Ergebnis zurück gegeben, da die Views mit der Authentifizierung von TIM gekoppelt ist.</note>
-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`.`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`
-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`.`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` 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`
-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`
-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`;
-</file>
-Sobald die Views angelegt sind, können diese mit Hilfe der beschriebenen [[dashboard_tablestructure|T!M Tabellenstruktur]] für Auswertungen mit dem [[software:dashboard|T!M Dashboard]] verwendet werden.

TIM Wiki / NEW TIM 6 Documentation

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Unterschiede

Seiten-Werkzeuge