= Excel Integration - Requirements and Use Cases =

 * [[ExcelIntegration|Back]]

<<TableOfContents>>


= Main Menu =

List of menu groups and items:

  1. Server
     1. Login
     1. Logout

  1. Data
     1. Search

  1. Edit
     1. Get Next Sequence Number for Selected Rows
     1. Refresh Selected Rows
     1. Refresh All Rows
     1. Duplicate Selected Rows
     1. Check for Duplicates
     1. Refresh Attribute Lists

  1. Save
     1. Save Selected
     1. Save All

  1. Test (only displayed for development/debug, e.g. when the ''testMode'' global variable set)
     1. Test Cases
     1. Clone header? (and possible other utilities)


= Use Cases =


== Use Case: Login/Logout ==

 1. '''Login''' - user supplies user ID & password, client sets (or clears on failure) global login flag
 1. '''Logout''' - global login flag is cleared

All action routines check the global login flag and display a ''not logged in message'' and exit if the flag is not set.

If the login is successful, then the attribute lists are refreshed (see below).

'''Nice to have:''' Filter the list of available contracts to those where the user has the ''drawing update role'' (roleId=2).

Note that the session may be timed-out by the server and the client must be able to react to not being logged in, even if the global login flag is set.

All server REST transactions return HTTP_RESPONSE_NOT_AUTHORIZED (401) if the user is not (or no longer) logged in. Each action routine on the client must be able to detect and report this.


== Use Case: Refresh Attribute List ==

 1. '''Refresh Attribute List''' - The hidden worksheets containing DrawMGT reference data (attributes) are reloaded.

'''Issue:''' Will menus for existing data in worksheets need to be re-created? '''Yes'''

'''See:''' modMain getAllMetadata


== Use Case: Search ==

 1. '''Search''' - User enters search criterion.

Two cases are allowed (selected via a radio button in the search form):

 1. Data is added to an existing list
 1. Data is added to a new list

When searching into an existing list, the data is added (to the end of the list) depending on whether it already exists in the list.

 1. Exists in list: A refresh operation is performed (see below)
 1. Does not exist in list: The data is added to the bottom of the list

The following search criterion are provided:

  1. Title - Soearch form field name: ''txtTitel'' '''(not implemented)'''
  1. Document code - ''txtDrawingCode'' '''(not implemented)'''
  1. Classification fields:
     1. Bereich - ''cmbBereich''
     1. Group - ''cmbGruppe''  -- '''Warning''' Requires filtering by ''contractId'' '''(not implemented)'''
     1. Zugriff - ''cmbZugriff'' '''(not implemented)'''
     1. Fachbereich - ''cmbFachBereich''
     1. Kontakt - ''cmbKontakt''
     1. Object - ''cmbObjekt''
     1. Lage - ''cmbLage''
     1. Dokumenttyp - ''cmdDokumentTyp''
     1. Projektphase - ''cmdProjektPhase''
     1. Archiv - ''cmbArchiv''
  1. Document sequence number from/to - ''txtDrawingNoMin, txtDrawingNoMax''
  1. Extern reference number - ''txtEtxRef'' '''(not implemented)'''
  1. Revision date from/to - ''txtRevisionDateMin, txtRevisionDateMax'' '''(not implemented)'''

The following additional fields are in the search form:

  1. Radio buttons for selecting load in ''new'' or ''existing list''. Default is ''new''
  1. Menu to select the spreadsheet type, e.g. ''Bau'', ''Ausrüstung'', etc. Only enabled when the radio button is set to ''new''.

== Use Case: Highlighting Changes Rows ==

''WAIT'' This is difficult to do

When the user changes a cell in a row the row is highlighted (suggest highlighting the drawing code cell). When the row is successfully saved the highlighting is removed.


== Use Case: Get Next Sequence Number ==

  1. For each selected row the drawing code (or partial drawing code, less the sequence number part) is sent to the server , which returns the next available sequence number, which is then placed in the sequence number column (which doe to the drawing code Excel formula, is added to the drawing code).

  1. Validation errors may occur:
     1. The user does not have the access rights for the given contract

== Use Case: Check for Duplicates ==

Operates on '''all''' rows in the list.

This is also called prior to operations such as save and refresh.

Duplicate rows are detected by scanning all rows in a worksheet, and checking for duplicate values of the following columns:

  1. Drawing id (a hidden column in the keyset area)
  1. Drawing code

As soon as any duplicate is found, then a pop-up window appears indicating where the duplicate (the cell coordinates) is, and the operation is stopped. If called by another operation, then that operation is cancelled.

'''See:''' ''modFunctions identifyDuplicateRows''


== Use Case: Refresh ==

  1. '''Refresh Selected''' - All selected rows in the worksheet are refreshed
  1. '''Refresh All''' - All rows in the worksheet are refreshed

The refresh algorithm for a single row:

  1. Prior to the refresh, a check for duplicate rows will be performed (see above), and the refresh operation is cancelled if duplicate rows are detected
  1. The record contents (for Doc, RevA, RevB and RevC records) are requested from the server, and the displayed values (in the list worksheet) are checked for differences.
  1. If a difference is detected then a cell comment is created containing the old value from the row value in the worksheet, and the cell value is replaced with the new value from the server.
  1. The records' record serial numbers (updateCountDoc for Doc, and updateCountRev for RevA, RevB and RevC) are updated with the values from the server.
  1. Rows that have never been saved (e.g. for which the keyset is empty) are highlighted. E.g. the drawing code cell is highlighted.

== Use Case: Save ==

 1. '''Save Selected''' - All selected rows in the worksheet are saved.
 1. '''Save All''' - All rows in the worksheet are saved

The save algorithm:

  1. Prior to the save, a check for duplicate rows will be performed (see above), and the save operation is cancelled if duplicate rows are detected
  1. If the record ids in the (hidden) keyset area are not set, then the save operation is an ''insert'' rather than an ''update'', and the key set columns area are filled with the appropriate values from the server
    1. '''Update'''
       1. The client prepares Doc, RevA (if required), RevB and RevC XML documents and posts them to the server
       1. Server checks the update count supplied by the client against that in the system database
       1. If the record update count matches, the server saves the record, and returns the new record count.
          1. For each posting, a server side validation check is also performed. It it fails, then HTTP_RESPONSE_BAD_REQUEST is returned along with an error message
       1. If the update count doesn't match: The server does '''not''' save the record and returns the record contents. The client creates a cell comment on the drawing code, indicating an ''update conflict'' occurred, the user must refresh the row.
    1. '''Insert'''
       1. The client prepares a Doc and RevB XML document and posts it to the server, followed by posting RevA (if required) and RevC.
       1. For each posting, a server side validation check is also performed. It it fails, then HTTP_RESPONSE_BAD_REQUEST is returned along with an error message

'''TODO''' Automatically set the revision codes (?)

'''See:''' ''modUpdateServer'' ''postUpdate'' and ''getDocumentToSave''

Error conditions:

  1. Validation errors:
     1. Invalid classification id values
     1. Invalid users (e.g. user's not having the rquired user roles) were selected for workflow steps
     1. Date sequence violations (complete and plan dates sequences must occur in order)
     1. Complete dates set/missing vs. selection of users
     1. Status vs. complete date and user
  1. When a validation error occurs the validation error message is displayed in a cell comment attached to the drawing code or revision code, or actual cell that caused the error(?).


== Use Case: Row Manipulation ==

Blank rows can be inserted into a spread sheet. Blank rows are ignored by the client software.

If the user manages to copy or fill-down fields in the keyset area then those rows will be marked as duplicates.

The following ways of create new rows is supported:

  1. Using the ''Duplicate Selected Rows'' menu item
  1. Fill-down cells into blank rows (e.g. not copying the hidden keyset area)
  1. Copy-paste of cells into blank rows (e.g. not copying the hidden keyset area)

Copying and pasting entire rows, including hidden fields, will cause duplicate records to be created.


=== Duplicate Selected Rows ===

  1. User selects rows to duplicate. Any cell or an entire row can be selected
  1. User selects menu item ''Duplicate Selected Rows''
  1. The selected rows are duplicated at the end of the worksheet, the rows have an empty keyset area and the drawing code cells are highlighted (marking them as unsaved)

=== Fill Down ===

  1. User selects all visible cells and fills down into empty lines
  1. Newly filled rows have an empty keyset area and will be considered by the client as new records


=== Copy/Cut and Past Rows  ===

If rows are pasted multiple times, or the duplicate existing rows in a worksheet then the row will be marked as a duplicate the next time a refresh or save operation is preformed.


=== Deleting Rows ===

Rows can be deleted from a list worksheet. This has no affect on the data in the server.


== Use Case: Exit ==

When the user attempts to exit Excel, the client catches the event and asks the user to confirm/cancel if there are any unseved rows.

'''TODO:''' How to detect unsaved rows?


= Revision Code and Revision Number Handling =

== Revision Handling Use Cases ==

 1. New row
 1. Duplicate row
 1. Documents with a ''Vorabzug'' revision
 1. Documents without a ''Vorabzug'' revision
 1. Restart revision sequence
 1. Reject revision (RevC only?)
 1. Detecting invalid/incomplete revision sequences (which must be corrected via web GUI)
 1. Sub-Revision

== Events Affecting Revision Codes ==

  || '''Event'''               || '''Start revisionNo'''  || '''Vorabzug Flag'''               || '''RevC Type Menu'''  ||
  || Read record               || set from server data    || set from server data              || set from server data  ||
  || Change ''Vorabzug'' flag  || ''n/a''                 || set from menu                     || ''n/a''               ||
  || Change RevC type          || ''n/a''                 || ''n/a''                           || set from menu         ||
  || Save record               || ''n/a''                 || lock menu (make it ready-only)    || ''n/a''               ||
  || New row                   || set to ''nothing''      || set to default value              || set to default value  ||
  || Duplicate row             || set to ''nothing''      || set from source row, unlock menu  || set from source row   ||
  || Restart revision sequence || set to Rev revisionNo   || set from source row, unlock menu  || set from source row   ||


For each of these events the revisionNo and revisionCode fields for each revision must be determined according the the algorithm described in the next sections.

== Revision Coding ==

=== Ausführungs Documents ===

'''Pre-condition:''' RevC Type = 15 (''gut zur Ausführung'')

 ||<|2> '''Revision Type''' ||<-2> '''!VorabzugFlag = 0'''     ||<-2> '''!VorabzugFlag = 1'''||
 ||                        '''revisionCode''' || '''revisionNo'''  || '''revisionCode''' || '''revisionNo''' ||
 ||<-5> '''Series 1'''                                                                       ||
 || VA - ''Vorabzug''           || V01        || 1             || ''n/a''    || ''n/a''      ||
 || ZF - ''zur freigabe''       || V02        || 2             ||  V01       || 1            ||
 || GA - ''gut zur Ausführung'' || V``_``_    || 100           ||  V``_``_   || 100          ||
 ||<-5> '''Series 2'''                                                                       ||
 || VA - ''Vorabzug''           || V_A01      || 101           || ''n/a''    || ''n/a''      ||
 || ZF - ''zur freigabe''       || V_A02      || 102           ||  V_A01     || 101          ||
 || GA                          || V_A        || 103           ||  V_A       || 103          ||
 ||<-5> '''Series 3'''                                                                       ||
 || VA - ''Vorabzug''           || V_B01      || 104           || ''n/a''    || ''n/a''      ||
 || ZF - ''zur freigabe''       || V_B02      || 105           ||  V_B01     || 104          ||
 || GA - ''gut zur Ausführung'' || V_B        || 106           ||  V_B       || 106          ||

=== Grundlagen Documents ===

'''Pre-condition:''' RevC Type = 13 (''freigegeben'')

 ||<|2> '''Revision Type''' ||<-2> '''!VorabzugFlag = 0'''     ||<-2> '''!VorabzugFlag = 1'''||
 ||                       '''revisionCode''' || '''revisionNo'''  || '''revisionCode''' || '''revisionNo''' ||
 ||<-5> '''Series 1'''                                                              ||
 || VA - ''Vorabzug''           || V01        || 1             || ''n/a''    || ''n/a''      ||
 || ZF - ''zur freigabe''       || V02        || 2             ||  V01       || 1            ||
 || GA - ''freigegeben''        || V03        || 3             ||  V02       || 2            ||
 ||<-5> '''Series 2'''                                                                       ||
 || VA - ''Vorabzug''           || V04        || 4             || ''n/a''    || ''n/a''      ||
 || ZF - ''zur freigabe''       || V05        || 5             ||  V03       || 3            ||
 || GA - ''freigegeben''        || V06        || 6             ||  V04       || 4            ||
 ||<-5> '''Series 3'''                                                                       ||
 || VA - ''Vorabzug''           || V07        || 7             || ''n/a''    || ''n/a''      ||
 || ZF - ''zur freigabe''       || V08        || 8             ||  V05       || 5            ||
 || GA - ''freigegeben''        || V09        || 9             ||  V06       || 6            ||


== Migration and New Records for Revision Numbers and Codes ==

The revision codes table needs to be migrated to make room for additional revisions.

New revision codes affect inly records in  revisionNoMapId = 4

=== New Revision Code Records ===

Create new revision codes as follows:

  ||  '''revisionCode''' || '''revisionNo'''   ||
  || v_A01               || 101                ||
  || v_A02               || 102                ||
  || v_B01               || 104                ||
  || v_B02               || 105                ||
  || ...                 || ...                ||
  || v_Y01               || 173                ||
  || v_Y02               || 174                ||
  || v_Z01               || 176                ||
  || v_Z02               || 177                ||

=== Revision Code Migration ===

Migrate existing revision numbers as follows:

  ||<-2> '''Existing Values'''              ||<-2> '''New Values'''                   ||
  || '''revisionNo''' || '''revisionCode''' || '''revisionNo''' || '''revisionCode''' ||
  || 1                || 01                 || 1                || 01                 ||
  || 2                || 02                 || 2                || 02                 ||
  || ...              || ...                || ...              || ...                ||
  || 99               || 99                 || 99               || 99                 ||
  || 100              || V``_``_            || 100              || v``_``_            ||
  || 101              || v_A                || 103              || v_A                ||
  || 102              || v_B                || 106              || v_B                ||
  || 103              || v_C                || 109              || v_C                ||
  || ...              || ...                || ...              || ...                ||
  || 126              || v_Z                || 178              || v_Z                ||

Notes:

 1. Revision codes are not changed
 1. The revision numbers less than and equal to 101 are not changed
 1. Revision numbers greater than 101 are set to ( ''revisionNo'' - 'A' ) * 3 + 100


= GUI Details =


== Menus ==

 1. User menus (for design, check approve menus)
    1. Display ''lastName firstName - company'' in menus

== GA/Fregegeben Menu ==

The ''GA/Fregegeben'' menu controls the ''revisionTypeId'' associated with the ''Ausführung'' (RevC) revision.

 1. Requirements
    1. The menu is located in the ''5. Ausfuehrung Typ'' (CA) column
    1. The menu should contain two entries
       * ''gut zur Aurführung'' - corresponding to ''revisionTypeId'' = 15
       * ''Freigegeben'' - corresponding to ''revisionTypeId'' = 13

 1. Behavior
    1. The menu only controls the setting of the RevC ''revisionTypeId'' field, and causes no changes to the behavior of any other spreadsheet columns or internal fields
    1. The menu value can be changed at any time. E.g. saved records can have their ''revisionTypeId'' changed at a later date

 1. Notes
    1. The implementation might make use of a hidden column containing the ''revisionTypeId'' value corresponding to the menu setting


== Vorabzug Flag ==

The ''Vorabzug'' flag controls whether a ''Vorabzug'' revision is created:

 1. Requirements
  1. Control of the flag is implemented as a menu in the ''Vorabzug ja/nein'' column (col AN) with two choices: ''Ja'' and ''Nein''
  1. The choices indicate whether a ''Vorabzug'' (RevA) revision can be edited/saved
  1. The flag value is stored as a user defined field named ''revBVorabzugFlag'' attached to RevB
  1. The user defined field ''revBVorabzugFlag'' has a value of one or zero when read from or written to the server
  1. One a record is saved, the ''Vorabzug ja/nein'' menu is frozen and cannot be changed by the user. E.g. the cell with the menu must be set to read-only
  1. When the flag is read from the database the one/zero value is used to set the menu value, and the cell with the menu must be set
 to read-only

 1. Behavior

  1. When the flag is set to '''one''' then, a ''Vorabzug'' (RevA) revision '''is''' created and saved, and all the columns associated with the ''3. Version Vorabzug'' should be activited (as currently occurs).

  1. When the flag is set to '''zero''' then, a ''Vorabzug'' (RevA) revision '''is not''' created and saved, and all the columns associated with the ''3. Version Vorabzug'' should all be cleared, assigned a light gray background and set to read-only, including the ''Vorabzug ja/nein'' menu column

 1. Notes

  1. The implementation might make use of a hidden column containing the one/zero value corresponding to the ''ja/nein'' menu setting

== Client Side Validation ==

  1. All dates columns should be in ''DD.MM.YY'' format
  1. ''Ist''-dates: ''Ist'' date for workflow step must be >= previous ''Ist''-date
  1. ''Soll''-dates: automatically calculated, validation required (''Soll'' date columns should be read-only)
  1. No checking between ''Ist'' and ''Soll'' dates is required
  1. ''Wer'' vs. status:
     1. ''Offen'' status: ''Wer'' can be empty
     1. non-''Offen'' status: ''Wer'' must be set
  1. Workflow status vs. ''Ist''-date:
     1. ''Offen'' status: ''Ist''-date must be empty
     1. non-''Offen'' status: ''Ist''-date must be set to a date <= today
  1. Workflow steps must be completed in order
     1. If a reject occurs in a workflow step, then no further steps can be completed. E.g. the ''Ist''-dates for subsequent workflow steps cannot be set, nor can their status be set to a non-''offen'' value.
     1. Note that all the status menus default to the appropriate ''offen'' status value.
  1. Data types: ''t0'' and ''t0wv'' must be dates
  1. Data types: Resource fields:
     1. ''Resourcenplannung Planer & PL'' - can be integers or floating point values. The values represent the number of hours/days of effort planned
     1. ''von/bis'' - must be dates
     1. ''Verteiler'' - must be integers. The values represent the number of copies to send
  1. Date format enforcement via data formats in layout sheet?
  1. Read-only fields:
     1. ''Doku.-Nr'' - drawing code
     1. ''Soll'' dates
     1. ''Ver.'' - revision code (Design needed)

== Nice to Have ==

Nice to have, but not essential for the first release.

  1. Change cursor to an hourglass during long operations (e.g. attribute refresh, refresh, search, save)
  1. Catch cell change events and mark rows as changed
  1. Catch the Excel exit event and warn the user if there are unsaved rows

Questions and Issues:

  1. There is no columns for a serial number in the layout, and this is not in the specification