Excel Integration - Requirements and Use Cases

Contents

  1. Excel Integration - Requirements and Use Cases
  2. Main Menu
  3. Use Cases
    1. Use Case: Login/Logout
    2. Use Case: Refresh Attribute List
    3. Use Case: Search
    4. Use Case: Highlighting Changes Rows
    5. Use Case: Get Next Sequence Number
    6. Use Case: Check for Duplicates
    7. Use Case: Refresh
    8. Use Case: Save
    9. Use Case: Row Manipulation
      1. Duplicate Selected Rows
      2. Fill Down
      3. Copy/Cut and Past Rows
      4. Deleting Rows
    10. Use Case: Exit
  4. Revision Code and Revision Number Handling
    1. Revision Handling Use Cases
    2. Events Affecting Revision Codes
    3. Revision Coding
      1. Ausführungs Documents
      2. Grundlagen Documents
    4. Migration and New Records for Revision Numbers and Codes
      1. New Revision Code Records
      2. Revision Code Migration
  5. GUI Details
    1. Menus
    2. GA/Fregegeben Menu
    3. Vorabzug Flag
    4. Client Side Validation
    5. Nice to Have

Main Menu

List of menu groups and items:

  1. Server
    1. Login
    2. Logout
  2. Data
    1. Search
  3. Edit
    1. Get Next Sequence Number for Selected Rows
    2. Refresh Selected Rows
    3. Refresh All Rows
    4. Duplicate Selected Rows
    5. Check for Duplicates
    6. Refresh Attribute Lists
  4. Save
    1. Save Selected
    2. Save All

  5. Test (only displayed for development/debug, e.g. when the testMode global variable set)

    1. Test Cases
    2. 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

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

  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
  2. 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)
  2. 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)

  2. Document code - txtDrawingCode (not implemented)

  3. Classification fields:

    1. Bereich - cmbBereich

    2. Group - cmbGruppe -- Warning Requires filtering by contractId (not implemented)

    3. Zugriff - cmbZugriff (not implemented)

    4. Fachbereich - cmbFachBereich

    5. Kontakt - cmbKontakt

    6. Object - cmbObjekt

    7. Lage - cmbLage

    8. Dokumenttyp - cmdDokumentTyp

    9. Projektphase - cmdProjektPhase

    10. Archiv - cmbArchiv

  4. Document sequence number from/to - txtDrawingNoMin, txtDrawingNoMax

  5. Extern reference number - txtEtxRef (not implemented)

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

  2. 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).
  2. 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)
  2. 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

  2. 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
  2. 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.
  3. 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.
  4. The records' record serial numbers (updateCountDoc for Doc, and updateCountRev for RevA, RevB and RevC) are updated with the values from the server.
  5. 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.

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

  2. 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
      2. Server checks the update count supplied by the client against that in the system database
      3. 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

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

    2. 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.
      2. 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
    2. Invalid users (e.g. user's not having the rquired user roles) were selected for workflow steps
    3. Date sequence violations (complete and plan dates sequences must occur in order)
    4. Complete dates set/missing vs. selection of users
    5. Status vs. complete date and user
  2. 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

  2. Fill-down cells into blank rows (e.g. not copying the hidden keyset area)
  3. 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

  2. User selects menu item Duplicate Selected Rows

  3. 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
  2. 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
  2. Duplicate row

  3. Documents with a Vorabzug revision

  4. Documents without a Vorabzug revision

  5. Restart revision sequence
  6. Reject revision (RevC only?)
  7. Detecting invalid/incomplete revision sequences (which must be corrected via web GUI)
  8. Sub-Revision

Events Affecting Revision Codes

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)

Grundlagen Documents

Pre-condition: RevC Type = 13 (freigegeben)

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:

Revision Code Migration

Migrate existing revision numbers as follows:

Notes:

  1. Revision codes are not changed
  2. The revision numbers less than and equal to 101 are not changed

  3. Revision numbers greater than 101 are set to ( revisionNo - 'A' ) * 3 + 100

GUI Details

  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

    2. The menu should contain two entries

      • gut zur Aurführung - corresponding to revisionTypeId = 15

      • Freigegeben - corresponding to revisionTypeId = 13

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

    2. The menu value can be changed at any time. E.g. saved records can have their revisionTypeId changed at a later date

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

    2. The choices indicate whether a Vorabzug (RevA) revision can be edited/saved

    3. The flag value is stored as a user defined field named revBVorabzugFlag attached to RevB

    4. The user defined field revBVorabzugFlag has a value of one or zero when read from or written to the server

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

    6. 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
  2. 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).

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

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

  2. Ist-dates: Ist date for workflow step must be >= previous Ist-date

  3. Soll-dates: automatically calculated, validation required (Soll date columns should be read-only)

  4. No checking between Ist and Soll dates is required

  5. Wer vs. status:

    1. Offen status: Wer can be empty

    2. non-Offen status: Wer must be set

  6. Workflow status vs. Ist-date:

    1. Offen status: Ist-date must be empty

    2. non-Offen status: Ist-date must be set to a date <= today

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

    2. Note that all the status menus default to the appropriate offen status value.

  8. Data types: t0 and t0wv must be dates

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

    2. von/bis - must be dates

    3. Verteiler - must be integers. The values represent the number of copies to send

  10. Date format enforcement via data formats in layout sheet?
  11. Read-only fields:

    1. Doku.-Nr - drawing code

    2. Soll dates

    3. 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)
  2. Catch cell change events and mark rows as changed
  3. 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

ExcelIntegrationRequirements (last edited 2011-04-17 13:33:36 by alan)

Copyright 2008-2014, SoftXS GmbH, Switzerland