Excel Integration - Internal Documentation and Implementation Notes

• Back

Definitions

• key set

• record update count

• RevA

• RevB

• RevC

• revision sequence

Revision Sequences

Legacy browser data is problematic for the new Excel tool. The Excel tool expects each document that can be managed to be one of the following revision sequences:

• RevA	RevB	RevC	Notes
  			Create new revision sequence based on last revision. Depending on the last revision code, this may force the RevC to be a GA
  VA
  VA	ZF
  VA	ZF	FR	Could be invalid if code of FR revision code is >= 'A'
  VA	ZF	GA	Could be invalid if code of GA revision code is < 'A'
  	ZF
  	ZF	FR
  	ZF	GA

Notes:

• See Specification, page ?, for a description of the revision coding rules

• The vorabzugFlag must be set/cleared depending on whether a VA revision is present in the sequence

• The revCType must be set depending on whether the RevC is a FR or GA

Client-Server Interface

The following client-server transactions exist:

1. Login/Logout
   1. Login
   2. Logout
2. Metadata
   1. Refresh Metadata
   2. Refresh User Role Metadata
3. Document/Revision
   1. Document Search
   2. Get Document Detail
   3. Get Revision Detail
4. Links
   1. Get List of Document Links
   2. Add Document Link
   3. Delete Document Link
5. Update
   1. Upsert Document and Revisions
6. Log
   1. Log Message

All transactions

Login/Logout

• to be completed

Login

Logout

Metadata

• to be completed

Refresh Metadata

Refresh User Role Metadata

Document/Revision

Document Search

Request

Response

Get Document Detail

• to be completed

Get Revision Detail

• to be completed

Links

• to be completed

Get List of Document Links

Add Document Link

Result Set Specifications =

The fo

Definition for the result set to be returned from a Excel search.

• A set of search criterion are sent to the server
• Should be able to return record for all documents matching the search criterion, including those where the revision sequence does not match that processed by Excel

Result set contents: (basically the fields in the key set)

1. Document part
   • documentId
   • document record update count
   • classification fields -- are they necessary?
   • title -- how to fill in title? From last revision?
2. Last revision part: (only needed when a blank-blank-blank sequence is returned)
   • revisionCode
   • revisionNo
3. RevA part: (could be empty)
   • revisionId
   • revisionTypeId
   • revisionCode -- not currently in the key set
   • revisionNo -- not current set by the VBA code
   • revision record update count
4. RevB part: (could be empty)
   • revisionId
   • revisionTypeId
   • revisionCode -- not currently in the key set
   • revisionNo -- not current set by the VBA code
   • revision record update count
5. RevC part: (could be empty)
   • revisionId
   • revisionTypeId
   • revisionCode -- not currently in the key set
   • revisionNo -- not current set by the VBA code
   • revision record update count

Client Site

Header Structure

Formula definitions

Example formula:

•     _^^revCT0_^^row_-_^^revCVersandDelta1_^^row

The formula is split on the '_' character, which results in the following array:

• Formula Component	Meaning	Example Value
  ^^revCT0	The column for the field revCT0	AB
  ^^row	The current row number	27
  -	Minus sign	-
  ^^revCVersandDelta1	The column for the field revCVersandDelta1	BD
  ^^row	The current row number	27

For the example values given above, the result is the following formula:

•      AB27-BD27

Forms

VBA Modules

Client-Side

Requirement Notes for VBA functions and routines:

1. Probably need to store (in the key set) the last revision code and number (at least for blank-blank-blank revision sequences), depending on the implement details. The client does noeed to know the last revision number, but may not need to store it
2. Recognise the delivery of a document with a blank-blank-blank revision sequence

3. Recognise delivery of documents with open (e.g. undefined) tail-end revisions. E.g. where the RevC or RevB and RevB are missing

4. Be able to initialise RevB user defined fields/flags, when not already present
5. Be able to initialise RevC user defined fields/flags, when not already present
6. Be able to initialise user defined fields (mainly the vorabzugFlag) when the RevA is not present in an otherwise valid revision sequence. But also be able to flag an error if the revision code/number does not conform to what is describe in the specification
   1. Routine to create a new revision sequence based on the latest revision's revision number and code
   2. Search form: Checkbox to only return valid revisions? By default returns all documents, even those that do not conform to accepted revision sequences

Server Side

Proposal for processing valid revisions server side

Using sql, we have produced a prototype query that could be used as a design spec for a new rest transaction. The tool currently uses the following rest api to return a list of drawings

• /dev/linthal115dev/documents/list?
    &maxRecords=50
    &contractId=310
    &siteClassification2Id=2
    &allRevisions=1
    &revisionTypeIds=8,14,13,15
    &maxRecords=50 

The prototype query consists of an outer query with 4 sub queries:

An improved server side solution should help performance and simplify the Excel VBA code.

SQL Proposal

• -- Create temporary table to contain the results to be returned to Excel
  
  drop table if exists t1;
  create temporary table t1 (
    drawingId int,
    allRevisionIds varchar(255),
    allRevTypeCodes varchar(255),
    revisionIds varchar(255),
    revTypeCodes varchar(255),     -- 'VA', 'VA,ZG', 'VA,ZF,GA', etc.
    seqType varchar(3)             -- 'A', 'BC', etc.
  );
  
  -- Select all documents that have revision sequences that conform to the Excel requirements
  --  and set the revisionIds and revisiontype codes
  
  insert into t1 ( drawingId, allRevisionIds, allRevTypeCodes )
  select
    drw.drawingId,
    group_concat(rev.revisionId order by rev.revisionNo separator ','), -- allRevisionIds
    group_concat(revType.code order by rev.revisionNo separator ',')    -- allRevTypeCodes
  from
    Drawings drw,
    Revisions rev,
    RevisionTypeRef revType
  where
        rev.drawingId = drw.drawingId
    and revType.revisionTypeId = rev.revisionTypeId
    and drw.contractId = 120
    and drw.siteClassification2Id = 2
  group
    by drw.drawingId
  having 
    group_concat(revType.code order by rev.revisionNo separator ',') regexp
      'VA$|VA,ZF$|VA,ZF,FR$|VA,ZF,GA$|ZF$|ZF,FR$|ZF,GA$';
  
  -- Update the sequence types
  
  update t1 set seqType =
    case
    when allRevTypeCodes regexp 'VA$'                 then 'A'
    when allRevTypeCodes regexp 'VA,ZF$'              then 'AB'
    when allRevTypeCodes regexp 'VA,ZF,FR$|VA,ZF,GA$' then 'ABC'
    when allRevTypeCodes regexp 'ZF$'                 then 'B'
    when allRevTypeCodes regexp 'ZF,FR$|ZF,GA$'       then 'BC'
    end;
  
  -- Remove trailing commas?
  
  update t1 set
    revTypeCodes = substring_index( allRevTypeCodes, ',', -length(seqType) ),
    revisionIds = substring_index( allRevisionIds, ',', -length(seqType) );
  
  -- Deliver result set to Excel
  
  select * from t1;

Proposal for new rest api /doucuments/revisionseq

Excel currently uses /documents/list to produce a search response for vba code to process. This requires some ugly vba to parse the response in reverse, attempting to identify the latest revision sequence. Tibor has produced some sql that accurately identifies the latest revisions in a revision sequence. I would propose the sql be wrapped by a rest api called /doucuments/revisionseq. The api would take the same parameters as /documents/list and return an xml structure similar to the one below.

<?xml version="1.0" encoding="UTF-8"?>
<recordSet name="revisionSequences" idFieldName="drawingId">
  <record type="" key="drawingId">
    <drawingCode>LA2-E-6-03-330-211-10_1002</drawingCode>
    <drawingId>33099</drawingId>
    <updateCountDoc>0</updateCountDoc>
    <revisionIdA>27058</revisionIdA>
    <revisionIdB/>
    <revisionIdC/>
  </record>
</recordSet>

Example

The following URL: http://.../documents/revisionseq?contractId=120&maxRecords=200&siteClassification2Id=2 gives the response:

• <?xml version='1.0' encoding='UTF-8'?>
  <recordSet name="revisionSequences" idFieldName="drawingId">
    <record type="" key="drawingId">
      <drawingCode>LA2-E-6-00-200-211-10_0001</drawingCode>
      <drawingId>1261</drawingId>
      <updateCountDoc>0</updateCountDoc>
      <revisionIdA>740</revisionIdA>
      <revisionIdB/>
      <revisionIdC/>
    </record>
    <record type="" key="drawingId">
      <drawingCode>LA2-E-6-00-200-211-10_0002</drawingCode>
      <drawingId>1264</drawingId>
      <updateCountDoc>0</updateCountDoc>
      <revisionIdA>741</revisionIdA>
      <revisionIdB/>
      <revisionIdC/>
    </record>
  .
  .
  .
    <record type="" key="drawingId">
      <drawingCode>LA2-E-6-03-300-211-10_2056</drawingCode>
      <drawingId>39143</drawingId>
      <updateCountDoc>0</updateCountDoc>
      <revisionIdA>33657</revisionIdA>
      <revisionIdB/>
      <revisionIdC/>
    </record>
  </recordSet>