Differences between revisions 8 and 9

Excel Integration - Internal Documentation and Implementation Notes

Back

Contents

Excel Integration - Internal Documentation and Implementation Notes
Server Side
1. Proposal for processing valid revisions server side

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.

'VA'

'VA, ZF'

'VA, ZF, FR'

'VA, ZF, GA'

'ZF'

'ZF, FR'

'ZF, GA'

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.

Qry1 (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.

Qry2

Qry2 uses Qry1 and constrains the data by the axpo revision pattern.

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

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

Deletions are marked like this.	Additions are marked like this.
Line 54:	Line 54:
Using sql, we have produced a prototype query that could be used as a design spec for a new rest transaction.	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. === Qry1 (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. === Qry2 === Qry2 uses Qry1 and constrains the data by the axpo revision pattern. select tblRevisionSquence.drawingId from (Qry1) where concatsequence in ('VA', 'VA, ZF', 'VA, ZF, FR', 'VA, ZF, GA', 'ZF', 'ZF, FR', 'ZF, GA')) === Qry3 ===