Excel Integration - Internal Documentation and Implementation Notes

• Back

Definitions

• header structure

• key set

• layout sheet

• record header

• 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

Notes:

1. All transactions are initiated by the Excel client
2. All transaction requests are HTTP requests, consisting of a URL and CGI encoded variables
3. All transaction responses are delivered as HTTP responses and contain data encoded in XML
4. The character set:
   1. Requests: UTF-8
   2. Responses: UTF-8

The semantics, requests and responses associated with these transactions are described in the following sections.

Error Messages

Response

• to be completed

Login/Logout

• to be completed

Login

Logout

Metadata

• to be completed

Refresh Metadata

Refresh User Role Metadata

Document/Revision

Proposal for new rest api /doucuments/revisionseq

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>

Document Search

Request

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

The following is an example query:

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

Response

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

Excel currently uses the request /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>

Get Document Detail

• to be completed

Get Revision Detail

• to be completed

Links

• to be completed

Get List of Document Links

Add Document Link

Delete Document Link

Update

• to be completed

Upsert Document and Revisions

Log

• to be completed

Log Message

Server-Side Implementation

The server-side implementation consists of a series of transactions

Document Search

Proposal for processing valid revisions server side

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;

Client Site

Requirements Notes

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

Excel Worksheets

• to be completed

The Excel client is mainly data-driven and relies on a number of worksheets for defining the processing of incoming and outgoing data. The primary controlling worksheets are:

1. Header Structure

2. Layout Sheet

3. Record Headers

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

Layout Sheet

• to be completed

Record Headers

• to be completed

Forms

• to be completed

VBA Modules

• to be completed