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
2. Logout
3. Refresh Metadata
4. Refresh User Role Metadata
5. Document Search
6. Get Document Detail
7. Get Revision Detail
8. Get List of Document Links
9. Add Document Link
10. Delete Document Link
11. Upsert Document and Revisions
12. Log

Notes:

1. Transactions are always initiated by the Excel client
2. Requests are HTTP requests, consisting of a URL and CGI encoded variables
3. Responses are delivered as HTTP responses and contain data encoded in XML
4. 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

• to be completed

Response

• to be completed

Login

• to be completed

Logout

• to be completed

Refresh Metadata

• to be completed

Refresh User Role Metadata

• to be completed

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/revisionseq/list?
    &maxRecords=50
    &contractId=310
    &siteClassification2Id=2
    &allRevisions=1
    &revisionTypeIds=8,14,13,15
    &maxRecords=50 

new rest api /doucuments/revisionseq (replacement for /documents/list?)

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>

Modifications to rest transaction /documents/revisionseq? sql

Currently the server returns data with matching revision types as per our standard case. However, to make the search consistent with the browser search, we should extend the data, with a union to the remaining data, that does not match our vanilla case. The additional data would be handled by the aforementioned changes to the Excel Client.

Modifications to rest transaction /documents/revisionseq? response

• 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.

&revisionTypeIds=8,14,13,15

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

The proposed XML would look like this

• <?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>
      <revisionLast key="revisionId">
        <revisionId>234</revisionId>
        <revisionCode>v00</revisionCode>
        <revisionNo>100</revisionNo>
        <title0>blah</title0>
        <title1>more blah</title1>
        <title2>more blah</title2>
        <title3>more blah</title3>
        <scale>100:1</scale>
        <size>blah</size>
      </revisionLast>
      <revisionIdA key="revisionId">
       <revisionId>740</revisionId>
       <revisionTypeId></revisionTypeId>
       <revisionCode></revisionCode>
       <revisionNo></revisionNo>
       <updateCountRev></updateCountRev>
       <displayWorkflowTypeId><displayWorkflowTypeId>
      </revisionIdA>
      <revisionIdB key="revisionId">
       <revisionId>740</revisionId>
       <revisionTypeId></revisionTypeId>
       <revisionCode></revisionCode>
       <revisionNo></revisionNo>
       <updateCountRev></updateCountRev>
       <displayWorkflowTypeId><displayWorkflowTypeId>
      </revisionIdB>
      <revisionIdC key="revisionId">
       <revisionId>740</revisionId>
       <revisionTypeId></revisionTypeId>
       <revisionCode></revisionCode>
       <revisionNo></revisionNo>
       <updateCountRev></updateCountRev>
       <displayWorkflowTypeId><displayWorkflowTypeId>
      </revisionIdC>    
    </record>
   
  </recordSet>

Get Document Detail

• to be completed

Get Revision Detail

• to be completed

Get List of Document Links

• to be completed

Add Document Link

• to be completed

Delete Document Link

• to be completed

Upsert Document and Revisions

• to be completed

Log

• to be completed

---

Server-Side Implementation

1. The server-side implementation consists of a series of REST transactions

2. You can find the actual code that implements each transaction by looking up the request URLs in the RestMap.

Document Search

Proposal for processing valid revisions server side

• to be completed

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.

Example SQL

• -- 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;
  
  -- Not sure what this does
  
  update t1 set
    revTypeCodes = substring_index( allRevTypeCodes, ',', -length(seqType) ),
    revisionIds = substring_index( allRevisionIds, ',', -length(seqType) );
  
  -- Deliver result set to Excel
  
  select * from t1;

---

Client-Side Implementation

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

Handling of non standard data in the Excel Client

Non standard data....

• Requirement	Parameters	Solution
  To save data revtypes must be a sequence	revCode, revNo
  To save data worktypes have to match expected values	displayworkflowTypeId
  Vorabzug flag and revCType handling could require post processing depending on revision sequence
  One or two revs missing

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