= Excel Integration - Internal Documentation and Implementation Notes =

 * [[ExcelIntegration|Back]]

<<TableOfContents>>

= 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''' || '''RevA Display WorkflowTypeID''' ||'''RevB''' || '''RevB Display WorkflowTypeID''' || '''RevC''' || '''RevC Display WorkflowType ID''' || '''Type''' || '''Notes''' ||
  || VA || 105 ||ZF ||106 ||FR ||107 || Standard || Could be invalid if code of FR revision code is >= 'A' ||
  || VA || 105 ||ZF ||106 ||GA ||107 ||Standard || Could be invalid if code of GA revision code is < 'A' ||
  || VA || 105 ||   || ||   || ||Partially Standard || ||
  || VA || 105 ||ZF ||106 ||   || ||Partially Standard || ||
  ||    || ||ZF ||106||   || ||Partially Standard || ||
  ||    || ||ZF ||106 ||FR ||107||Partially Standard || ||
  ||    || ||ZF ||106 ||GA ||107 ||Partially Standard || ||
  ||    || ||  ||  ||  ||  ||Non Standard ||Create new revision sequence based on last revision. Depending on the last revision code, this may force the RevC to be a GA ||
  || XX || ||   || ||   ||  || Non Standard || Document linked to unknown revisionTypeId ||

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
 1. Logout
 1. Refresh Metadata
 1. Refresh User Role Metadata
 1. Document Search
 1. Get Document Detail
 1. Get Revision Detail
 1. Get List of Document Links
 1. Add Document Link
 1. Delete Document Link
 1. Upsert Document and Revisions
 1. Log

Notes:

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


=== Change request (2012-01-25) to rest transaction /documents/revisionseq? sql ===

Currently the server returns data with matching revision types as per our standard data and partially standard data. Non standard data can be considered as data that does not match the pattern described in the revision section at the beginning of this page.

 * The reqular expression constraint should be additionally constrained by the workflowTypes specified in the revision section.  
 * To make the search consistent with the browser search, we should modify the sql to return the non standard data as well.

=== Change request (2012-01-25) to rest transaction /documents/revisionseq? xml response  ===

  * The rest transaction should be extended to include a new element called '''revisionLast'''. 
  * The existing reva,revb and revc elements should be retained. However, they need some additional child elements specified below.  

The parameter '''revisionTypeIds''' should be removed.

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?
  1. Last revision part: (only needed when a blank-blank-blank sequence is returned)
     * revisionCode
     * revisionNo
     * title0
     * title1
     * title2
     * title3
     * scale
     * size      
  1. 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
     * displayWorkflowTypeId
  1. 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
     * displayWorkflowTypeId
  1. 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
     * displayWorkflowTypeId

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
 1. You can find the actual code that implements each transaction by looking up the request URLs in the Rest``Map.


== 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
 1. Recognise the delivery of a document with a blank-blank-blank revision sequence
 1. Recognise delivery of documents with ''open'' (e.g. undefined) tail-end revisions. E.g. where the RevC or RevB and RevB are missing

 1. Be able to initialise RevB user defined fields/flags, when not already present
 1. Be able to initialise RevC user defined fields/flags, when not already present 
 1. 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

  1. 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 and partially standard data in the Excel Client ===

 * Non standard data can be considered as data that does not match the pattern described in the revision section at the beginning of this page.

 * Partially standard data can be considered as a non complete revision sequence, with one or more of the expected revisionTypes.
 
 * Standard data contains all three expected revisionTypes. 

  || '''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'''
 1. '''Layout Sheet'''
 1. '''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''