NOK Configuration for the Linthal 2015 Project

Notes

1. The official project name is PSW Limmern (Linthal 2015 is a project above, which include PSW Limmern)

2. NOK is being renamed as Axpo

3. Axpo Informatic will host our server. They seem to be quite independent of NOK and the Linthal project

4. This page describes a configuration method for the Linthal project, which could become a model
   • for configuring other project too.

Terminology

1. Zust. = Approval (Zustimmung)
2. Indextyp = Document code type

Contact Information

Contact Addresses

Main Office

• Axpo AG - Hydroenergie, Parkstrasse 23, CH-5401 Baden
• +41-56-200-4483

Axpo Informatik

• Axpo Informatik AG
• Bruggerstrasse 68, Postfach, CH-5401 Baden

Construction Site

• Tierfeld, Kanton Glarus

• Main Office: 3rd floor of the Bauleitung office, a 3-level white container office complex

• CH coordinates: 717 750 / 193 178 (46.879298N, 8.983394E)

Contact Names

• Name	Description	Email	Phone
  Erich Ermel	Main contact from Oct-2011	Erich.Ermel@axpo.ch	T: 056-200-3779, M: 079-915-6241
  Martin Gmür	Main contact	Martin.Gmuer@axpo.ch	T: 056-200-3872, M: 076-489-0874
  Michael Schlegel	Contact for DrawMGT operations. Leiter Planung/Dienste	Michael.Schlegel@axpo.ch	+41 56 200 44 25
  Norbet Wohlkinger	Commercial contact	Norbert.Wohlkinger@nok.ch	T: 056-933-4483, M: 079-630-3150
  Martin Hase	Leiter Baustelle	Martin.Hase@axpo.ch
  Emil Bieri	Electro-Mechanical Leiter	Emil.Bieri@axpo.ch
  Rene Käppeli	Axpo Informatik	Rene.Kaeppeli@axpo.ch	T:056-200-4538, M:079-609-6147
  Mario Robic	Linux/Network specialist	Mario.Robic@axpo.ch	T: 056-200-3367, M: 079-630-3558
  Adele Grandi	User administration	Adele.Grandi@axpo.ch	?
  Jörg Leppin	Unix/Linux Spezialist	Joerg.Leppin@axpo.ch	T +41-56-200-4569, M +41-79-403-9429
  Axpo IT Hotline	For Citrix login support	See also BUG-2703	0800-55-05-05

Documentation and Notes on System Updates for Axpo

1. AxpoTaskEmailImplementation - Notes set to Axpo describing the task email updates requested May 2011

Discussions and Input Data from NOK

2009.10.13: Email from GMM

1. Bewilligungsauflagen Erfassung DrawMGT v02.xlsx - Example spreadsheet for Bewilligungsauflagen

2. Request for management of milestone dates, that can be assigned to tasks as due dates. This includes the ability
   • change the dates for milestones and have them automatically change the due dates of the tasks.

2009.10.16: AH/GMM/etc. Meeting in Baden to discuss classification system and workflows

1. Request for feature (for document/revision security): New flag in Drawings and/or Revision record:
   • Public to Bereich
   • Public to entire system

2. Request for feature: New workflow step F Freigabe, a final approval that is performed after all the

   • other Z (Zustimmung) approvals, and before the Submit workflow step. To be called Release in English.

3. New ApprovalStatus: Nicht efforderlich (e.g. N/A - Not Required). Rules:

   • Plan date: optional

   • Complete date: must be empty

   • Initials: optional

4. Summary of German language approval steps:
   • Offen
   • Erteilt
   • Erteilt mit Kommentar
   • Abgelehnt
5. Implement new workflows:

   • DCAAAAR - Design-Check-App1-App2-App3-App4-Release

   • DCAAAARS - Design-Check-App1-App2-App3-App4-Release-Submit

   • RCAAAAR - Receive-Check-App1-App2-App3-App4-Release

   • RCAAAARS - Receive-Check-App1-App2-App3-App4-Release-Submit

6. Request for improved event history to see exactly what was changed in workflows.

7. Idea for new Comment field for each Approval and Release workflow step

   • Did not commit to implementing this feature
   • NOK agreed to use tasks/comments for recording comments associated with approvals

New configuration files sent after the meeting from GMM:

1. 2009.10.19 DrawMGT PSW Limmern Struktur v01.xls -- Master configuration file for classifications

   • Worksheet Indextyp -- describe drawing code format and file names
   • Worksheet Attribute PSW Limmern -- list of all the classification and other reference data

2. 2009.10.19 DrawMGT Indextyp-Attribute v01.xlsx -- List of document code types and their components

3. 2009.10.19 DrawMGT Doktypen-Prozesstyp v01.xlsx -- List of (project) document types and their

   • corresponding document code types and workflow types. Note that we will implement the project document type as a classification field, and separate from the documentTypeId.

4. 2009.10.19 DrawMGT Prozesstypen Tabelle v01.xlsx -- List of desired workflows

Note that that attribute lists in the file 2009.10.19 DrawMGT PSW Limmern Struktur v01.xls generally have a set of code letters. E.g. Bereich has code aa. These same codes also appear in the document code definition and the names of the .txt and .sql files (see below).

2009.10.23: Emails from GMM for Training

1. Official project name is PSW Limmern.

2. Change workflow step name E (Erhalten/Receive) to B (Bekommen).

3. The old (original) test system test is to be kept.

   • The new update of the data from the meeting or 2009.10.16 should go in the beta system.

4. OK to delay deployment of production system to the Axpo hosted server to the end of the year

2009.10.30: Email from GMM: Configuration Corrections

1. linthal-configuration v02.xls -- File returned from GMM. Only the Users worksheet appears to be updated.

2. L2015 DrawMGT Rollen v01.xlsx -- List of user roles, and DE names for the roles.

3. L2015 DrawMGT PSW Limmern Struktur v02b.xls -- Updates to contract & group codes, marked in red. (File renamed to v02b from v02)

2009.11.03: Updated NOK ToDo List

1. Update test system with latest CVS updates

2. Create new task type Bewilligungsauflagen

3. Update L2015 DrawMGT Testsystem Korrekturliste v02-AH4.xls

   1. Update with input collected during training sessions
   2. Sent to GMM
4. Check Auflagen spreadsheet: check fields can fit in the Comments table

   1. (Bewilligungsauflagen Erfassung DrawMGT v02.xlsx) -- see email from 2009.10.13 above)

5. Suggestion for allowing task subscribers to download documents attached to a task, regardless of their user roles. E.g.
   • making some a subscriber to a task, would automatically give them viewing rights to all documents attached to the task.

2009.11.24: Email from GMM and phone call AH/GMM

1. Deadline for going live: 2009.01.08 -- Axpo personnel only
2. Deadline for going live: 2009.01.15 -- All project partners
3. Final to do items:
   1. DrawMGT Handbook - translate into German
   2. Schritt-für-Schritt Dokumentation - To be implemented in wiki
   3. Set user passwords and send emails to them

   4. Implement configuration for new Bereich LUe TN done (date?)

   5. Update user roles TN done (date?)

4. Anleitung_Bestellungserfassung_EBanf v1.2.pdf -- Example Schritt-für-Schritt documetation

5. L2015 DrawMGT PSW Limmern Struktur v05.xls -- Updated Project Structure

6. L2015-DrawMGT-Rollen-v07.xls -- Updated User Roles

2010.01.15: Phone call AH/GMM

This section describes how the mapping from the NOK spreadsheet 2009.10.19 DrawMGT PSW Limmern Struktur v01.xls and the DrawMGT database tables.

• Worksheet Name	NOK Description	DrawMGT Table	Notes
  Ref-aaa	Bereich	ContractRef
  Ref-b	Projektphase	ProjectPhaseRef
  Ref-cc	Fachbereich	CategoryRef
  Ref-ddd	Objekt	LocationRef
  Ref-eee	Kontakt	WorkTypeRef	Copied also to table Companies s. below
  Ref-ff	Dokumententyp	SiteClassification1Ref	Keep separate from DocumentTypeRef
  Lage	Lage	AlignmentRef
  Taetigkeit	Tätigkeit	WorkCategoryRef
  Archiv	Archiv	SiteClassification3Ref
  Veroeffentlichung	Veröffentlichung	SiteClassification4Ref
  WorkflowSteps	-	-
  ApprovalStatusRef	-	ApprovalStatusRef	German translation of workflow steps
  Companies	-	Companies
  Groups	-	Groups
  Users	-	Users
  UserRoles	-	UserRoles

A workaround is applied to fullfill NOK requieremnt 9.1 in L2015 DrawMGT Testsystem Korrekturliste v02.xls. NOK's Kontakt information will be copied also to table Companies to allow to select the same information in feld Requestor.

NOK Document Coding System

Note: NOK calls the drawing code Indextyp

Drawing Code

• Document Code

  -

  Revision Code

  File Extension

  aaa

  -

  i

  -

  b

  -

  cc

  -

  ddd

  -

  eee

  -

  ff

  _

  ####

  -

  XXX

  -

  v

  ##

  .yyy

Component Description

• Part	Description	DrawMGT Mapping
  aaa	Bereich	contractId
  i	Intern/Extern Flag	siteClassification2Id
  b	Projektphase	projectPhaseId
  cc	Fachbereich	categoryId
  ddd	Objekt	locationId
  eee	Kontakt	workTypeId
  ff	Dokumenttyp	siteClassification1Id
  ####	Laufnummer (drawingNo)	drawingNo
  XXX	Teilnummer, 1-3 alphanumeric characters, optional	-none-
  -		-none-
  v	Constant	revisionCode (with revisionNo)
  ##	Versionsnummer (revisionNo)	revisionNo
  .yyy	File name extension	-none-

Generated SQL Configuration Files

The initial worksheet called Overview lists the mappings from the NOK attribute lists to their corresponding DrawMGT classification tables. Note that DrawMGT V11.3 has four extra classification tables SiteClassification1-4.

The generation is perform by saving each separate worksheet in the Excel file, in tab-separated format, into a file of the same in the data directory. The saving can be mad manually as described below or automatically with an OpenOffice macro export2Csv.bas in the same directory.

• E.g. Save the ContractRef worksheet as ContractRef.txt

• Save the other worksheets too

Then run a script to generate SQL files:

• generateRefData.sh

This script reads the .txt files and outputs .sql files

Then load the SQL into the database and run genMeta.pl

  cd cfg/linthal/site/data

  mysql -u... -p...
    create database linthal113dev;
    use linthal113dev;

    source ../../../../app/schema/drawmgt-schema.sql
    source ../../../../app/schema/id-table-schema.sql
    source ../../../../app/schema/data/refdata.sql

    source linthal-configuration.sql

    source ../../../../app/schema/id-table-setup.sql
    quit

  cd ../../../../app/etc
  ./genMeta.pl

1. Access via private RSA 1024 bit key that we supplied

   • Public and private key stored at: lu.softxs.ch:/root/axpo-ssh

     • id_rsa
     • id_rsa.pub -- sent to Jörg Leppin, to be installed on their server.
2. Still to be defined:
   • System hostname/ip address of CentOS system
   • User name
3. Access will be possible from the following IP addresses:

   • 81.221.23.33 (softxs.ch)

   • 80.254.184.33 (sz.softxs.ch) Joseph Kaelin's home network

4. Typical access:

   •        ssh root@lu.softxs.ch
            ssh -i /root/axpo-ssh/id_rsa user@centos.axpo.ch

Access to Axpo Internal CentOS System

1. The only network connection can be used is an incoming SSH on the Axpo system. Therefore ssh tunneling is used to allow access CVS on honir from the Axpo system. The port 2401 (pserver) will be forwarded to the ssh port on honir. The typical usage:

   honir# ssh -R 2401:localhost:22 -i ~/axpo-ssh/id_rsa root@axpo
   [root@sabdn470 ~]# su - linthal
   [linthal@sabdn470 ~]$ cd www/shtml/delta/app/etc
   [linthal@sabdn470 etc]$ cvs -q up

2. The following settings was made to allow to use CVS so simply as above:

   1. A .cvsrc file was created in the home directory of linthal on the Axpo system with the content below, to allow the access to the CVS repository through ssh on localhost

      cvs -z9 -d linthal@localhost:/home/cvsroot

   2. A section was made in /etc/ssh/ssh_config on the Axpo system (above section 'Host *'), to use port 2401 automatically on ssh connection to localhost:

      Host localhost
          Port 2401

   3. An ssh key pair was generated on the Axpo system as user linthal and the content of the public key was copied to .ssh/authorized_keys on honir to allow to use cvs without password.

      [linthal@sabdn470 etc]$ ssh-keygen -t rsa
      [linthal@sabdn470 .ssh]$ view ~/.ssh/id_rsa.pub
      
      linthal@honir (~) 499$ vi ~/.ssh/authorized_keys

Using CVS on the Axpo CentOS Internal System

1. On lu.softxs.ch Done

   • Enable redirect pages

           cd /home/linthal/www/html/beta
           mv index-DISABLED.html index.html
           mv index.php index-DISABLED.php
     
           cd /home/linthal/www/html/delta
           mv index-DISABLED.html index.html
           mv index.php index-DISABLED.php

   • Backup delta database.
   • Copy delta database backup to Axpo server.
   • Rsync delta system files from lu.softxs.ch server to Axpo server.

2. Desired end state lu.softxs.ch server

   • Attempts to access the beta/delta systems on lu.softxs.ch are automatically redirected to the Axpo server, to the main page. (the redirect pages are configured for this).

3. On Axpo server: drawmgt-pswlimmern.axpo.ch

   • Backup database

   • Setup and install new 'prod' instance (done)

     • cfg/..../InstanceSettings.php exists & is checked-into CVS, but needs checking/testing

     • Note the email test mode is set to false

   • Setup and install new 'test' instance (done)

     • cfg/..../InstanceSettings.php exists & is checked-into CVS, but needs checking/testing

     • Note the email test mode is set to true

   • Update main page and links to prod and test systems (add note to say that delta and beta systems Sind nicht mehr aktuell (or something like that).

   • Setup event daemon jobs for prod and test systems

   • Setup copy prod --> test job

4. Desired end state: Axpo server

   • Initial page http://drawmgt-pswlimmern.axpo.ch has new links to the prod and test systems, and warnings that the beta and delta systems are not to be used.

   • Data from the lu.softxs.ch delta system loaded in the prod and test systems on the Axpo server.

Deleting Users