Excel Integration - Internal Documentation and Implementation Notes

• Back

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

Server Side

Proposal for processing valid revisions server side

• Legacy browser data is problematic for the new excel tool. The excel tool expects the following revision pattern.

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&con tractId=310&siteClassification2Id=2&allRevisions=1&revisionTypeIds=8,14,13,15&maxRecords=50. The prototype query consists of an outer query with 4 sub queries.

q1 (most constrained)

• select 
    drw.drawingId,
    group_concat(revType.code separator ', ') concatsequence
  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

The first query constrains the data by user parameters as well as a right join to RevisionTypeRef. The aggregate function group_concat is used to print the revision code sequence.

q2

q2 uses q1 and constrains the data by the axpo revision pattern.

select tblRevisionSquence.drawingId from (q1) where concatsequence in ('VA',

• 'VA, ZF', 'VA, ZF, FR', 'VA, ZF, GA', 'ZF', 'ZF, FR', 'ZF, GA'))

q3

q3 uses q2 and aggregates the data to get the leading edge. The leading edge is derived by the max function.

select drw.drawingId,rev.revisionId,max(rev.revisionNo) from Drawings drw, Revisions rev where rev.drawingId = drw.drawingId and drw.drawingId in (q2) group by drw.drawingId,rev.RevisionId

q4

q4 uses q3. q4 concatenates both revisionId and RevisionTypeCode

select drw.drawingId,rev.revisionId,revType.code,group_concat(revType.code separator ', ') concatsequence,group_concat(rev.revisionId separator ',') revsequence from Drawings drw, Revisions rev,RevisionTypeRef revType where rev.drawingId = drw.drawingId and revType.revisionTypeId = rev.revisionTypeId and drw.drawingId in (q3) group by drw.drawingId

q5

q5 uses q4 and decodes the data into the rev ABC pattern that Excel understands

select drawingId, CAST(revsequence AS CHAR CHARACTER SET utf8), CASE concatsequence when "VA" then "A" when "VA, ZF" then "AB" when "VA, ZF, FR" then "ABC" when "VA, ZF, GA" then "ABC" when "ZF" then "B" when "ZF, FR" then "BC" when "ZF, GA" then "BC" END

• FROM

(q4)

The Query (q1`+ q2 + q3 + q4 + q5)

select drawingId, CAST(revsequence AS CHAR CHARACTER SET utf8), CASE concatsequence when "VA" then "A" when "VA, ZF" then "AB" when "VA, ZF, FR" then "ABC" when "VA, ZF, GA" then "ABC" when "ZF" then "B" when "ZF, FR" then "BC" when "ZF, GA" then "BC" END

• FROM

(select drw.drawingId,rev.revisionId,revType.code,group_concat(revType.code separator ', ') concatsequence,group_concat(rev.revisionId separator ',') revsequence from Drawings drw, Revisions rev,RevisionTypeRef revType where rev.drawingId = drw.drawingId and revType.revisionTypeId = rev.revisionTypeId and drw.drawingId in ( select leadingEdgeDrawingIds.drawingId from (select drw.drawingId,rev.revisionId,max(rev.revisionNo) from Drawings drw, Revisions rev where rev.drawingId = drw.drawingId and drw.drawingId in

• (select tblRevisionSquence.drawingId from

(select drw.drawingId,group_concat(revType.code separator ', ') concatsequence 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) as tblRevisionSquence where concatsequence in ('VA',

• 'VA, ZF', 'VA, ZF, FR', 'VA, ZF, GA', 'ZF', 'ZF, FR', 'ZF, GA'))

group by drw.drawingId,rev.RevisionId) leadingEdgeDrawingIds ) group by drw.drawingId) as leadingEdge