MET Configuration

Contents

  1. MET Configuration
  2. Updating Aspect Data
  3. Uploading files for Matrics
    1. Actualize the MDL to Metadata Upload File Converter
    2. Creating MetaData Upload File from Master Document List of Matrics
    3. Move Document Files to the Correct Place

Updating Aspect Data

  1. Use a tab separated file for the classification aspects and values as described in header of app/etc/genAspectData.pl. See 
    cfg/met/site/data/Management_Aspects_Tree.csv
    It is VERY IMPORTANT, that the code values for existing aspect values remain unchanged, otherwise the classification information will be destroyed with this procedure.
  2. Create the SQL file which creates the data 
    cd cfg/met/site/data
../../../../app/etc/genAspectData.pl Management_Aspects_Tree.csv > genAspectData.sql
mysql -uroot -psqladmin met120prod
  set names utf8;
  truncate table AspectRef;
  truncate table AspectValues;
  source genAspectData.sql;
  -- Set the AspectRef colors
  update AspectRef set displayColor='#6d7b8d' where aspectId=1;  -- code=00, Black  #000000
  update AspectRef set displayColor='#ccffcc' where aspectId=2;  -- code=01, Green  #00ff00
  update AspectRef set displayColor='#50ebec' where aspectId=3;  -- code=02, Blue   #00ffff
  update AspectRef set displayColor='#f778a1' where aspectId=4;  -- code=03, Red    #ff0000
  update AspectRef set displayColor='#f9966b' where aspectId=5;  -- code=04, Orange #ff9900
  update AspectRef set displayColor='#e6a9ec' where aspectId=6;  -- code=05, Purple #cc99ff
  update AspectRef set displayColor='#ffffaa' where aspectId=7;  -- code=06, Yellow #ffff00
  update AspectRef set displayColor='#c0c0c0' where aspectId=8;  -- code=07, Gray   #c0c0c0
  update AspectRef set displayColor='#f4f4f4' where aspectId=9;  -- code=08, White  #ffffff
  update AspectRef set displayColor='#ffddbb' where aspectId=10; -- code=09, Beige  #ffcc99
  -- Refresh Classification fields
  source correct_classif.sql;
cd ../../../../app/etc
./genMeta.pl
  3. Delete cache in your browser if the aspect data appears strange in Document/New.

Uploading files for Matrics

Actualize the MDL to Metadata Upload File Converter

The Master Document List (MDL) to Metadata Upload File (MUF) converter is: 

cfg/met/site/data/convertMetaData.pl

Suppose that the Drawings record of a new revision is already in the database. If the contractIds in the Metadata Upload File (MUF) and in the database differ, the Metadata Uploader returns an error. Therefore the correct contractId must be set in MUF for all existing documents.

Change the function getContract in the converter program. Replace the body of the switch command with the actual list. See the function header, how to select the list form the database.

Creating MetaData Upload File from Master Document List of Matrics

  1. Remove all unnecessary columns behind column 'Original File Name if Different' (Excel column AQ)
  2. Perform some check and changes to avoid unnecessary iterations
    • Change 'METS-' to 'METS' in column 'SOURCE' (Excel column Q)
    • Remove 'Rev ' in column 'Appendix Revision' (Excel column AK)
    • Check that the header is the first line
    • Change date fields into ISO format (YYYY-MM-DD)
  3. Export the MDL into a TAB separated CSV file: "cfg/met/site/data/MDLDoclist_YYMMDD.csv". Use character coding UTF-8.
  4. Remove unnecessary trailing lines
  5. Convert MDL to MUF: 
    MUF_FILE=MUF_YYMMDD
cd cfg/met/site/data
./convertMetaData.pl < MDLDoclist_YYMMDD.csv > $MUF_FILE.txt
  6. Check that at least the drawingCodes are correct: 
    awk '{ printf "%s\t%s\n", NR, $1 }' $MUF_FILE.txt
  7. Split the new upload file into separate files based on contract code ("A", "B", "C", "D"), and determine the maximum number of lines in a file. 
    awk 'BEGIN { FS="\t" } { if ( $6 == "A" || NR == 1 ) print $0 }' $MUF_FILE.txt > ${MUF_FILE}_A.txt
awk 'BEGIN { FS="\t" } { if ( $6 == "B" || NR == 1 ) print $0 }' $MUF_FILE.txt > ${MUF_FILE}_B.txt
awk 'BEGIN { FS="\t" } { if ( $6 == "C" || NR == 1 ) print $0 }' $MUF_FILE.txt > ${MUF_FILE}_C.txt
awk 'BEGIN { FS="\t" } { if ( $6 == "D" || NR == 1 ) print $0 }' $MUF_FILE.txt > ${MUF_FILE}_D.txt
wc -l ${MUF_FILE}_?.txt
  8. Split every file which have more then 100 lines into separate upload files, e.g.: 
    split100lines.sh MUF_091221_A.txt

  9. Load up all files into the DrawMGT system with the menu Documents/Upload Metadata

Move Document Files to the Correct Place

  1. Create a list of all available files. The file is a TAB separated CSV fájl, which can be loaded into the database for further processing. The file contains the directory (relative), the filename, the filename without extension and the size of the file. 
    DATADIR=/var/www/html/dev/met120prod/cfg/met/site/data   # set your site/data directory
cd /var/www/html/dev/met120prod                          # go to the directory where the files are
$DATADIR/genFileList.sh data_* > /tmp/filelist.txt       # list all directories as argument
  2. Load the file into a temp table in the database. Note that do not leave mysql until the end of the process, because temp table is used and it is lost on exit from mysql. 
    mysql -uroot -psqladmin met120prod
 set names utf8;
 create temporary table FileList ( n serial, dir char(255), fileWithExt char(128),
  fileWithoutExt char(128), size int ) collate utf8_general_ci;
 load data local infile '/tmp/filelist.txt' into table FileList(dir, fileWithExt, fileWithoutExt, size);
  3. Create a CSV file from the MDL, containing the following columns: 'Doc. No.', 'Doc. Part', 'Appendix Revision', 'Coded File Name', 'Original File Name if Different'. Remove the header line(s) also. Load this file also into a temp table in the database. 
     create temporary table MDLFileList ( docNo char(5), docPart char(5), rev char(1),
   codedFileName char(128), origFileName char(172) ) collate utf8_general_ci;

 load data local infile 'MDLFileList.csv' into table MDLFileList;

 update MDLFileList set rev = "1" where rev is null or length(rev)=0;
 update MDLFileList set docPart=null where length(docPart)=0;
  4. Filter out all revisionId from filenames in directory 'drawings'. Use an other terminal, do not exit from mysql. 
    SITE=met
INSTANCE=lu.softxs.ch-prod
cd var/${SITE}/${INSTANCE}/data/drawings
find . -type f | awk -F- '{ print $6 }' | sort -n > /tmp/revId.txt
  5. Load the revisionId list into a temp table in mysql 
     create temporary table exclRevId( revId int ) collate utf8_general_ci;

 load data local infile '/tmp/revId.txt' into table exclRevId;
  6. Create list of revisionIds and corresponding filenumber 
     create temporary table revFile( revId int, fileNo int, primary key( revId ) ) collate utf8_general_ci;

 insert ignore into revFile select r.revisionId, f.n 
  from Drawings d, Revisions r, MDLFileList m, FileList f
  where d.drawingId = r.drawingId
  and d.drawingCode like concat( "%-METS-00", coalesce(concat(m.docNo, "-", upper(m.docPart)),m.docNo))
  and r.revisionCode = concat( "Rev-", m.rev )
  and r.revisionId not in (select revId
  from exclRevId) and f.fileWithExt = m.origFileName;   -- Original filename with extension

 insert ignore into revFile select r.revisionId, f.n 
  from Drawings d, Revisions r, MDLFileList m, FileList f
  where d.drawingId = r.drawingId
  and d.drawingCode like concat( "%-METS-00", coalesce(concat(m.docNo, "-", upper(m.docPart)),m.docNo))
  and r.revisionCode = concat( "Rev-", m.rev )
  and r.revisionId not in (select revId
  from exclRevId) and f.fileWithoutExt = m.origFileName;   -- Original filename without extension

 insert ignore into revFile select r.revisionId, f.n 
  from Drawings d, Revisions r, MDLFileList m, FileList f
  where d.drawingId = r.drawingId
  and d.drawingCode like concat( "%-METS-00", coalesce(concat(m.docNo, "-", upper(m.docPart)),m.docNo))
  and r.revisionCode = concat( "Rev-", m.rev )
  and r.revisionId not in (select revId
  from exclRevId) and f.fileWithExt = m.codedFileName;   -- Coded filename with extension

 insert ignore into revFile select r.revisionId, f.n 
  from Drawings d, Revisions r, MDLFileList m, FileList f
  where d.drawingId = r.drawingId
  and d.drawingCode like concat( "%-METS-00", coalesce(concat(m.docNo, "-", upper(m.docPart)),m.docNo))
  and r.revisionCode = concat( "Rev-", m.rev )
  and r.revisionId not in (select revId
  from exclRevId) and f.fileWithoutExt = m.codedFileName;   -- Coded filename without extension
  7. Update file name, file size, file typ in table Revisions 

 update Revisions, FileList, revFile set publishFilename = fileWithExt, publishFilesize = FileList.size 
  where Revisions.revisionId = revFile.revId
  and FileList.n = revFile.fileNo;
 update Revisions, FileTypeRef set publishFileTypeId = fileTypeId
  where publishFileTypeId is null
  and publishFilename like concat( '%', FileTypeRef.fileExtension );

  1. Create a bunch of cp commands into the file /tmp/cp.sh 

     select
   -- drawingCode,
   concat(
     'cp ',
     -- From original name
       '"$SRC_DIR"/\'', FileList.dir, '/', replace(publishFileName,"'", "'\"'\"'"), '\' ',
     -- to internal filename $ROOT_DIR/contractId/{internalName}
       '"$DST_DIR"/', contractId, '/',
       17,       '-',    -- site ID (MET)
       0,  '-',          -- projectId
       contractId, '-',  -- contractId
       2,  '-',          -- objectTypeId (Revision)
       4,  '-',          -- objectSubTypeId (PublishFile)
       revisionId, '-',  -- objectId
       publishFileTypeId -- fileTypeId
   ) 'Internal Filename'
 from
   Drawings, Revisions, revFile, FileList
 where
   Drawings.drawingId = Revisions.drawingId and
   Revisions.revisionId = revFile.revId and
   revFile.fileNo = FileList.n and
   Revisions.revisionId = revFile.revId
 into outfile '/tmp/cp.sh';
  2. Set environment variables SRC_DIR and DST_DIR. Note that SRCDIR must be the same as your work directoy above in step 1 at creating the /tmp/filelist.txt. 
    cd /home/met/www/html/prod            # the project directory
 
SITE=met
INSTANCE=lu.softxs.ch-prod
 
SRC_DIR=/home/met                     # set it appropriately
DST_DIR=`pwd`/var/${SITE}/${INSTANCE}/data/drawings

. /tmp/cp.sh
Copyright 2008-2014, SoftXS GmbH, Switzerland