Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
tutorial:adm:vema_cloud [2019/08/21 13:51]
kotynekv Add the common part
tutorial:adm:vema_cloud [2022/04/05 14:53] (current)
stekld
Line 2: Line 2:
  
 You may have VEMA installed locally or in the cloud. The basic principals of integration of VEMA and VEMA Cloud with CzechIdM are very similar or the same. In both cases we will use an external MS-SQL database "Interchange" to store the data from VEMA which will be read by CzechIdM. We will also need a server with the VEMA thick client installed. You may have VEMA installed locally or in the cloud. The basic principals of integration of VEMA and VEMA Cloud with CzechIdM are very similar or the same. In both cases we will use an external MS-SQL database "Interchange" to store the data from VEMA which will be read by CzechIdM. We will also need a server with the VEMA thick client installed.
 +
 +  * VEMA PAM - work and payroll data(Prace a mzdy)
 +  * VEMA PER - personel/HR data(Personalistika)
  
 ===== VEMA cloud ===== ===== VEMA cloud =====
 +
   - Prepare the Windows server with VEMA thick client   - Prepare the Windows server with VEMA thick client
-    * It's necessary to install **v4data** package. Start VEMA Administrator, go to the "Products" tab, invoke context menu by clicking the right mouse button and select "Install". Then choose the lastest version of in the dialog. +      * It's necessary to install **v4data**  package. Start VEMA Administrator, go to the "Products" tab, invoke context menu by clicking the right mouse button and select "Install". Then choose the lastest version of in the dialog.
  
   - Prepare MS-SQL database for the "Interchange".   - Prepare MS-SQL database for the "Interchange".
   - Install the scripts:   - Install the scripts:
-    - Create an user in VEMA with a permission to switch to the admin role and with access to the personal data (recommended role name = "IDM export"). Emit a certificate for this user and install it to the server with the VEMA client. You can check the installation correctness with running following command:<code cmd>C:\Program Files (x86)\Vema\Vema.exe" /app=DBV HR PAM /server=your_id.cloud.vema.cz /username=your_username /world=your_ID</code> +      - Create an user in VEMA with a permission to switch to the admin role and with access to the personal data (recommended role name = "IDM export"). Emit a certificate for this user and install it to the server with the VEMA client. You can check the installation correctness with running following command: 
-    - Create a database user in MS-SQL database "Interchange" with permission to read&write to the database and schema. It needs to be a local user, domain authentication doesn't work within the scripts. + 
-    - Create the MS-SQL "Interchange" database structure. Using following script is recommended to get the default structure.<code sql>+<code cmd> 
 +C:\Program Files (x86)\Vema\Vema.exe" /app=DBV HR PAM /server=your_id.cloud.vema.cz /username=your_username /world=your_ID 
 + 
 + 
 +</code> 
 + 
 +  - Create a database user in MS-SQL database "Interchange" with permission to read&write to the database and schema. It needs to be a local user, domain authentication doesn't work within the scripts. 
 +  - Create the MS-SQL "Interchange" database structure. Using following script is recommended to get the default structure. 
 + 
 +<code>
 -- create table skript pro vytvoreni tabulek v MS SQL pro export z Vema -- create table skript pro vytvoreni tabulek v MS SQL pro export z Vema
 -- if 'nchar' doesn't work, change it for 'char' and vice-versa - the reason is that the Python module from Vema can't compare varchar -> comparison always ends up with false so all records are marked as "changed" -- if 'nchar' doesn't work, change it for 'char' and vice-versa - the reason is that the Python module from Vema can't compare varchar -> comparison always ends up with false so all records are marked as "changed"
Line 17: Line 30:
 CREATE TABLE "dbo"."tpzr" CREATE TABLE "dbo"."tpzr"
 ( (
-   pzar int PRIMARY KEY NOT NULL, +   pzar INT PRIMARY KEY NOT NULL, 
-   pzart nchar(255)+   pzart NCHAR(255)
 ); );
 CREATE UNIQUE INDEX PK_TPZR ON "dbo"."tpzr"(pzar); CREATE UNIQUE INDEX PK_TPZR ON "dbo"."tpzr"(pzar);
Line 24: Line 37:
 CREATE TABLE "dbo"."todp" CREATE TABLE "dbo"."todp"
 ( (
-   povfc int PRIMARY KEY NOT NULL, +   povfc INT PRIMARY KEY NOT NULL, 
-   povfct nchar(255)+   povfct NCHAR(255)
 ); );
 CREATE UNIQUE INDEX PK_TODP ON "dbo"."todp"(povfc); CREATE UNIQUE INDEX PK_TODP ON "dbo"."todp"(povfc);
Line 31: Line 44:
 CREATE TABLE "dbo"."tkat" CREATE TABLE "dbo"."tkat"
 ( (
-   kat int PRIMARY KEY NOT NULL, +   kat INT PRIMARY KEY NOT NULL, 
-   katt nchar(255)+   katt NCHAR(255)
 ); );
 CREATE UNIQUE INDEX PK_TKAT ON "dbo"."tkat"(kat); CREATE UNIQUE INDEX PK_TKAT ON "dbo"."tkat"(kat);
Line 38: Line 51:
 CREATE TABLE "dbo"."todz" CREATE TABLE "dbo"."todz"
 ( (
-   zames int PRIMARY KEY NOT NULL, +   zames INT PRIMARY KEY NOT NULL, 
-   czisco nchar(10), +   czisco NCHAR(10), 
-   kateg int+   kateg INT
-   skjkz int+   skjkz INT
-   povfc int+   povfc INT
-   funkc int+   funkc INT
-   povolani nchar(255), +   povolani NCHAR(255), 
-   pocrd int+   pocrd INT
-   platnost int+   platnost INT
-   zamest nchar(255), +   zamest NCHAR(255), 
-   zamest1 nchar(255), +   zamest1 NCHAR(255), 
-   zamest2 nchar(255)+   zamest2 NCHAR(255)
 ) )
 ; ;
Line 55: Line 68:
 CREATE TABLE "dbo"."tros" CREATE TABLE "dbo"."tros"
 ( (
-   ros int PRIMARY KEY NOT NULL, +   ros INT PRIMARY KEY NOT NULL, 
-   rost nchar(255) +   rost NCHAR(255) 
-)+);
  
- +CREATE TABLE "dbo"."osobyt" ( 
-CREATE TABLE "dbo"."osobyx" +    oscis int NOT NULL, 
-+    jmenozd varchar(250) COLLATE Czech_CI_AS NULL, 
-   oscis int PRIMARY KEY NOT NULL, +    prijmzd varchar(250) COLLATE Czech_CI_AS NULL, 
-   jmenozd nchar(250) DEFAULT (NULL)+    tituly varchar(250) COLLATE Czech_CI_AS NULL, 
-   prijmzd nchar(250) DEFAULT (NULL)+    rosu int NULL, 
-   tituly nchar(250) DEFAULT (NULL)+    full_update_timestamp datetime NOT NULL, 
-   upljmeno nchar(250) DEFAULT (NULL)+    greatest_timestamp datetime NOT NULL, 
-   full_update_timestamp datetime DEFAULT (getdate()) NOT NULL, +    rocis varchar(250COLLATE Czech_CI_AS NULL
-   greatest_timestamp datetime DEFAULT (getdate()) NOT NULL, +
-   rocis nchar(15)+
-   cpoj nchar(15), +
-   ICL nchar(6), +
-   druhlp int, +
-   datlp datetime, +
-   mistolp nchar(255), +
-   vysllp nchar(10), +
-   kopllp datetime, +
-   rosu int+
 ); );
  
 CREATE TABLE "dbo"."pzarazx" CREATE TABLE "dbo"."pzarazx"
 ( (
-   oscis int NOT NULL, +   oscis INT NOT NULL, 
-   cicin int NOT NULL, +   cicin INT NOT NULL, 
-   povfc int DEFAULT (NULL), +   povfc INT DEFAULT (NULL), 
-   kateg int DEFAULT (NULL), +   kateg INT DEFAULT (NULL), 
-   pracvz int DEFAULT (NULL), +   pracvz INT DEFAULT (NULL), 
-   pzar int DEFAULT (NULL), +   pzar INT DEFAULT (NULL), 
-   utvar nchar(6) DEFAULT (NULL),+   utvar NCHAR(6) DEFAULT (NULL),
    zauc datetime NOT NULL,    zauc datetime NOT NULL,
-   zapl datetime default (NULL), +   zapl datetime DEFAULT (NULL), 
-   ppvdr int default (NULL), +   ppvdr INT DEFAULT (NULL), 
-   povfct1 int DEFAULT (NULL), +   povfct1 INT DEFAULT (NULL), 
-   zames int DEFAULT (NULL),+   zames INT DEFAULT (NULL),
    full_update_timestamp datetime DEFAULT (getdate()) NOT NULL,    full_update_timestamp datetime DEFAULT (getdate()) NOT NULL,
    CONSTRAINT PK__pzarazx__D560F4C128ED12D1 PRIMARY KEY (oscis,cicin,zauc)    CONSTRAINT PK__pzarazx__D560F4C128ED12D1 PRIMARY KEY (oscis,cicin,zauc)
Line 100: Line 103:
 CREATE TABLE "dbo"."smlouvax" CREATE TABLE "dbo"."smlouvax"
 ( (
-   oscis int NOT NULL, +   oscis INT NOT NULL, 
-   cicin int NOT NULL,+   cicin INT NOT NULL,
    ppvza datetime NOT NULL,    ppvza datetime NOT NULL,
    ppvko datetime DEFAULT (NULL),    ppvko datetime DEFAULT (NULL),
Line 110: Line 113:
 CREATE TABLE "dbo"."vynetix" CREATE TABLE "dbo"."vynetix"
 ( (
-   oscis int NOT NULL,+   oscis INT NOT NULL,
    vynz datetime NOT NULL,    vynz datetime NOT NULL,
    vynk datetime DEFAULT (NULL),    vynk datetime DEFAULT (NULL),
-   vynd int NOT NULL,+   vynd INT NOT NULL,
    full_update_timestamp datetime DEFAULT (getdate()) NOT NULL,    full_update_timestamp datetime DEFAULT (getdate()) NOT NULL,
    CONSTRAINT PK__vynetix__8758F81519AACF41 PRIMARY KEY (oscis,vynz,vynd)    CONSTRAINT PK__vynetix__8758F81519AACF41 PRIMARY KEY (oscis,vynz,vynd)
 ); );
  
-create table tutv ( +CREATE TABLE tutv ( 
- utvar nchar(50) not NULL, +    utvar NCHAR(50) NOT NULL, 
- utvart nchar(255) default NULL, +    utvart NCHAR(255) DEFAULT NULL, 
- zapl datetime default NULL, +    zapl datetime DEFAULT NULL, 
- kopl datetime default NULL, +    kopl datetime DEFAULT NULL, 
- zkratka nchar(20) default NULL, +    zkratka NCHAR(20) DEFAULT NULL, 
- constraint utvar_pkey primary key (utvar)+    CONSTRAINT utvar_pkey PRIMARY KEY (utvar)
 ); );
  
-create table czisco ( +CREATE TABLE czisco ( 
- czisco nchar(10) not NULL, +    czisco NCHAR(10) NOT NULL, 
- praczar nchar(255) not NULL, +    praczar NCHAR(255) NOT NULL, 
- praczarzk nchar(10) not NULL, +    praczarzk NCHAR(10) NOT NULL, 
- constraint czisco_pkey primary key (czisco)+    CONSTRAINT czisco_pkey PRIMARY KEY (czisco)
 ); );
 +
 </code> </code>
-    - Install test scripts to a folder and change the log file path according to your environment. Default is ''C:\VemaV4\idmlogs\export_vymenik_log.txt'' + 
-      - test3.py:<code python>+  - Install test scripts to a folder and change the log file path according to your environment. Default is ''C:\VemaV4\idmlogs\export_vymenik_log.txt'' 
 +      - test3.py: 
 + 
 +<code python>
 # -*- coding: utf-8 -*- # -*- coding: utf-8 -*-
  
Line 162: Line 169:
  
 main = MyApp main = MyApp
 +
 +
 </code> </code>
-      - test6.py:<code python>+ 
 +  - test6.py: 
 + 
 +<code python>
 # -*- coding: utf-8 -*- # -*- coding: utf-8 -*-
  
Line 182: Line 194:
     conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=127.0.0.1;DATABASE=001;UID=vema;PWD=********')     conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=127.0.0.1;DATABASE=001;UID=vema;PWD=********')
         cursor = conn.cursor()         cursor = conn.cursor()
-        cursor.execute('INSERT INTO osobyt (oscis) VALUES (123)' +        cursor.execute('INSERT INTO osobyt (oscis) VALUES (123)')
     conn.commit()     conn.commit()
-        LogInfo(u'connectDB: COMMIT'  +        LogInfo(u'connectDB: COMMIT')
     conn.close()     conn.close()
  
Line 190: Line 202:
  
 main = MyApp main = MyApp
 +
 +
 </code> </code>
-    - Run the test scripts <code cmd>C:\Program Files (x86)\Vema\Vema.exe" /app=pyscript test6.py /database=DB_NAME /server=cloud.vema.cz /world=your_ID /databasePER=DB_PER_NAME /serverPER=your_ID.cloud.vema.cz /application=pam /roleName="IDM export" /username=login</code><code cmd>C:\Program Files (x86)\Vema\Vema.exe" /app=pyscript test3.py /database=DB_NAME /server=cloud.vema.cz /world=your_ID /databasePER=DB_PER_NAME /serverPER=your_ID.cloud.vema.cz /application=pam /roleName="IDM export" /username=login</code> + 
-      - the path to the ''Vema.exe'' might differ according to a certain installation +  - Run the test scripts 
-    - Check the results + 
- test3.py - tests the communication with VEMA. Note: You might need to change the name of data file you want to read from. The default is "osobyt" in database PAM. In other cases it might be "osobyx" in database PER. +<code cmd> 
- test6.py - tests ability to write into the "Interchange" database. Fill the following values (SERVER=...,DATABASE=...,UID=....,PWD=....), you might also need to change the table from "osobyt" to "osobyx"+C:\Program Files (x86)\Vema\Vema.exe" /app=pyscript test6.py /database=DB_NAME /server=cloud.vema.cz /world=your_ID /databasePER=DB_PER_NAME /serverPER=your_ID.cloud.vema.cz /application=pam /roleName="IDM export" /username=login 
- Expected results: + 
- No errors while running the scripts + 
- test3.py: Writest a list of family names from VEMA's data file to the log file ''C:\\VemaV4\\idmlogs\\export_vymenik_log.txt'' +</code> 
- test6.py: Inserts a record into the table osobyt in the "Interchange" database. The record will have value of "oscis" 123 and a info message is written into the log file ''C:\\VemaV4\\idmlogs\\export_vymenik_log.txt''+ 
 +<code cmd> 
 +C:\Program Files (x86)\Vema\Vema.exe" /app=pyscript test3.py /database=DB_NAME /server=cloud.vema.cz /world=your_ID /databasePER=DB_PER_NAME /serverPER=your_ID.cloud.vema.cz /application=pam /roleName="IDM export" /username=login 
 + 
 + 
 +</code> 
 + 
 +  - the path to the ''Vema.exe''  might differ according to a certain installation 
 +  - Check the results 
 + 
 +  * test3.py - tests the communication with VEMA. Note: You might need to change the name of data file you want to read from. The default is "osobyt" in database PAM. In other cases it might be "osobyx" in database PER. 
 +  test6.py - tests ability to write into the "Interchange" database. Fill the following values (SERVER=,DATABASE=,UID=.,PWD=.), you might also need to change the table from "osobyt" to "osobyx"
 +  * **Expected results:** 
 +      No errors while running the scripts 
 +      test3.py: Writest a list of family names from VEMA's data file to the log file ''C:\\VemaV4\\idmlogs\\export_vymenik_log.txt'' 
 +      test6.py: Inserts a record into the table osobyt in the "Interchange" database. The record will have value of "oscis" 123 and a info message is written into the log file ''C:\\VemaV4\\idmlogs\\export_vymenik_log.txt''
  
 ===== Local VEMA installation ===== ===== Local VEMA installation =====
Line 211: Line 240:
 The script must be run automatically e.g. once a day. The script must be run automatically e.g. once a day.
  
-In IDM you will create a System with the Database Table Connector or Scripted SQL Connector according to the certain use-case. The first case is covered tutorials [[tutorial:adm:systems_db_contracts|Systems - DB: Users' contracts synchronization]] and [[tutorial:adm:connect_a_db_system|Systems - DB: Source of identities]].+In IDM you will create a System with the Database Table Connector or Scripted SQL Connector according to the certain use-case. The first case is covered tutorials [[.:systems_db_contracts|]] and [[.:connect_a_db_system|]]. 
  
  • by kotynekv