Differences between revisions 17 and 18
Deletions are marked like this. Additions are marked like this.
Line 60: Line 60:
=== q1 (q1 == query 1 == most constrained) === === SQL proposal ===
Line 62: Line 62:
  {{{
select 		drop table if exists t1;
create temporary table t1 (
  drawingId int,
  allRevisionIds varchar(255),
  allRevTypeCodes varchar(255),
  revisionIds varchar(255),
  revTypeCodes varchar(255),
  seqType varchar(3)
);

insert into t1 ( drawingId, allRevisionIds, allRevTypeCodes )
select
Line 65: Line 75:
  group_concat(revType.code order by rev.revisionNo separator ', ') concatsequence
from 
  Drawings drw,  		  group_concat(rev.revisionId order by rev.revisionNo separator ','),
 group_concat(revType.code order by rev.revisionNo separator ',')
from
  Drawings drw,
Line 69: Line 80:
  RevisionTypeRef revType   
WHERE 		  RevisionTypeRef revType
where
Line 74: Line 85:
  and drw.siteClassification2Id = 2 
group  		  and drw.siteClassification2Id = 2
group
Line 77: Line 88:
  }}} 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$';
Line 79: Line 91:
q1 constrains the data by rest parameters and a join to RevisionTypeRef. The aggregate function group_concat is used to print the revision code sequence. 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;
Line 81: Line 100:
update t1 set
  revTypeCodes = substring_index( allRevTypeCodes, ',', -length(seqType) ),
  revisionIds = substring_index( allRevisionIds, ',', -length(seqType) );
Line 82: Line 104:
=== 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 order by rev.revisionNo separator ', ') concatsequence,group_concat(rev.revisionId order by rev.revisionNo 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 complete 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 order by rev.revisionNo separator ', ') concatsequence,group_concat(rev.revisionId order by rev.revisionNo 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 order by rev.revisionNo 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
}}}
 		select * from t1;
Line 194: Line 108:
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 /revisionSequences (working title, open to suggestion). The api would take the same parameters as /documents/list and return an xml structure similar to the one below. 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/revisionSequences (working title, open to suggestion). The api would take the same parameters as /documents/list and return an xml structure similar to the one below.
Line 198: Line 112:
<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 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>

Excel Integration - Internal Documentation and Implementation Notes

Contents

  1. Excel Integration - Internal Documentation and Implementation Notes
    1. Client Site
    2. Header Structure
      1. Formula definitions
    3. Forms
    4. VBA Modules
  2. Server Side
    1. Proposal for processing valid revisions server side
      1. SQL proposal
    2. Proposal for new rest api /revisionSequences

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

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

A server side solution should help performance and reduce hard to maintain excel vba code.

SQL proposal

drop table if exists t1; create temporary table t1 (

  • drawingId int, allRevisionIds varchar(255), allRevTypeCodes varchar(255), revisionIds varchar(255), revTypeCodes varchar(255), seqType varchar(3)

);

insert into t1 ( drawingId, allRevisionIds, allRevTypeCodes ) select

  • drw.drawingId, group_concat(rev.revisionId order by rev.revisionNo separator ','), group_concat(revType.code order by rev.revisionNo separator ',')

from

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 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;

update t1 set

  • revTypeCodes = substring_index( allRevTypeCodes, ',', -length(seqType) ), revisionIds = substring_index( allRevisionIds, ',', -length(seqType) );

select * from t1;

Proposal for new rest api /revisionSequences

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/revisionSequences (working title, open to suggestion). 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>

ExcelIntegrationInternals (last edited 2012-01-25 15:55:11 by sleightholm)

Copyright 2008-2014, SoftXS GmbH, Switzerland