Excel Integration - Testing Notes

• Back

Test against ...

• requirements given in DrawMGT-AxpoExcelIntegration-15.pdf (Chapter 2)

• client-side implementation in DrawMGT-AxpoExcelIntegration-15.pdf (Chapter 4)

• use cases given in DrawMGT-AxpoExcelIntegration-15.pdf (Appendix A)

• Test notes at ExcelIntegrationToDoList#Testing

All tests against DrawMGT-AxpoExcelIntegration-15.pdf are performed with ExcelIntegrationAxpoDemoNotes in mind!

Every tested feature is protocolled below.

  Vorabzug Flag handling / RevC Revision Type menu handling
  ---------------------------------------------------------
  
  onChange RecC Revision type behavior (CD):
  - gut zur Ausführung: sets to keyRefCTypeId = 15 -- OK
  - Freigegeben: sets to keyRefCTypeId = 13 -- OK

  onChange Vorabzug behavior (AP):
  - ja:
    - Vorabzug fields are locked -- FAILED
    - after save:
      - revBVorabzugFlag => 1 -- OK
      - menu static: -- FAILED
      - Vorabzug fields are locked -- FAILED
      
  - nein:
    - Vorabzug fields are unlocked -- OK
    - after save:
      - menu static: -- FAILED
      - revBVorabzugFlag => 0 -- OK
      - Vorabzug fields are locked -- FAILED
      
  COMMENT: When Vorabzug fields are 'locked', it's still possible to
  enter data and select from select menues.
  
  Create New Document
  -------------------
  
  'Create New Document' inserts after selected row:
  - selected row = none: alert appears "Unable to create document in
    header area" -- OK
  - selected row = contains document: new document is placed after last
    document row -- OK
  
  Duplicate Selected
  ------------------
  
  - when blank row selected -- OK
  - when non-blank row selected -- OK
  
  
  Check for Duplicates
  --------------------
  
  Correctly highlights found duplicates -- OK
  
  
  Delta calculations
  ------------------
  
  The rules are ...
  
  Soll TGZA (CM) = T0 (CS) - ∆1 (CR)
  Soll TF (BX) = Soll TGZA (CM) - ∆2 (CL)
  PrüfSoll (BQ) = Soll TF (BX) - ∆3 (BW)
  Soll TZF (BK) = PrüfSoll (BQ) - ∆4 (BO)
  BrSoll (BD) = Soll TZF (BK) - ∆5 (BJ)
  StnSoll (AX) = BrSoll (BD) - ∆6 (BC)
  Soll TV (AS) = StnSoll (AX) - ∆7 (AV)

  ... or the other way around ...

  Soll TV (AS) + ∆7 (AV) = StnSoll (AX)
  StnSoll (AX) + ∆6 (BC) = BrSoll (BD)
  BrSoll (BD) + ∆5 (BJ) = Soll TZF (BK)
  Soll TZF (BK) + ∆4 (BO) = PrüfSoll (BQ)
  PrüfSoll (BQ) + ∆3 (BW) = Soll TF (BX)
  Soll TF (BX) + ∆2 (CL) = Soll TGZA (CM)
  Soll TGZA (CM) + ∆1 (CR) = T0 (CS)

  ... simplyfied ...

  Soll TV (AS) + ∆7 (AV) + ∆6 (BC)
  + ∆5 (BJ) + ∆4 (BO) + ∆3 (BW) + ∆2 (CL)
  + ∆1 (CR) = T0 (CS)
  
  Therefore ...
  
  If I set ∆0 to a date in the future, and enter expression
  "=AS23+(AV23+BC23+BJ23+BO23+BW23+CL23+CR23)" in an Excel cell [adding
  the sum of deltas except ∆0 to Soll TV (AS23)], and change random
  deltas except for ∆0, the result always equals to ∆0 (CS23) -- OK

  Also, I added cells right below Soll date cells to proof, that ...
  
  Soll TV (AS) + ∆7 (AV) = StnSoll (AX)
  [...]
  
  ... and changed random deltas again, and compared these cells with all
  the soll dates. There are no differences -- OK
  
  
  Umlauts
  -------

  Umlaut characters created from the Web GUI in text fields (e.g. titles
  and other text fields), and then displayed in the Web GUI. -- OK
  
  Umlaut characters created in Excel in text fields, and then displayed
  in Excel. -- OK
  
  New document created in Excel with umlauts in titles (1-4), and then
  saved to server. Display in web-interface -- OK
  
  New document in web-interface (title "Excel-Test-Umlauts-From-Server
  (äöü)") with all required versions for VA, ZF, FR created and saved;
  Afterwards, "Search Documents" in Excel with Rapport "Bau", Bereich
  "LC1" and Titel "Excel-Test-Umlaut": -- OK
  
  HTTP response 200 Response text:
  
  <?xml version='1.0' encoding='UTF-8'?> <record id='26696'
  type='Revisions' key='revisionId'> [...]
  <title0>Excel-Test-Umlauts-From-Server (äöü)</title0> [...] </record>
  
  Update existing but changed document with updated titles (v2/ZF
  revision only) in Excel with "Refresh Selected": -- OK

  Search Documents
  ----------------
  
  Dok. Nr.:
  - with valid revision code: -- OK
  - with invalid revision code (e.g. 'blabla', '1df3'): -- FAILED
  
  Bereich: 
  - with valid selection: -- OK
  - with invalid data (e.g. 'blabla', '1df3'): -- FAILED
  
  QUESTION: Can I test other 'Bereiche' than 'LC1'?
  
  Zugriff, Projektphase, Fachbereich, Objekt, Kontakt, Dokumenttyp,
  Gruppe, Archiv, Lage:
  - with valid selection: -- OK
  - with invalid data (e.g. 'blabla', '1df3'): -- FAILED

  COMMENT: Entering invalid data also leads to funny results being
  placed in the worksheet.

  AMENDMENT: In selects, it should not be possible to enter custom data.

  Laufnummer (von - bis):
  - with valid data: -- OK
  - with invalid data (e.g. 'abcde', '1df3'): -- OK

  Place results in new worksheet -- OK

  Titel -- OK
  
  Ext. Ref.:
  - with valid value: -- OK
  - with invalid value (e.g. 'abcde', '1df3'): -- FAILED
  
  COMMENT: If a document has 'Ext. Ref.' = 'abcde', and I search for
  'Ext. Ref.' = 'abcde', the search returns the desired result.
  
  QUESTION: What is a valid value for 'Ext. Ref.'?
  
  Versionsdatum (von - bis) -- ???
  QUESTION: How can I test this?
  
  'Search' button: -- OK
  
  AMENDMENT: If nothing is entered, and 'Search' is clicked, a confusing
  alert message appears: 'Eingabe Fehler / Test Case: Feld
  erforderlich'.
  
  
  Basic Tests
  -----------
  
  1. Check reading and updating of all fields:
  --------------------------------------------
  
    From Excel to server:
    - Bereich (static) -- ???
    - Gruppe (ignored?) -- FAILED
    - Archiv -- OK
    - Berechtigung Server (Z) -- OK 
    - Klassifizierung Projektphase (Ph) -- OK
    - Klassifizierung Fachbereich (FB) -- OK
    - Objekt (O) -- OK
    - Kontakt (K) -- OK
    - Dokumenttyp (DT) -- OK
      QUESTION: Dokumenttyp 03.1/03.2 etc that exist in Excel are not
      existing in my system yet.
    - Laufnummer -- OK
    - Dokumenttitel 1 -- OK
    - Dokumenttitel 2 -- OK
    - Dokumenttitel 3 -- OK
    - Dokumenttitel 4 -- OK
    - Massstab -- FAILED
    - Format -- FAILED
    - Version (static) -- OK

    Vorabzug:
    - Erstellen Soll (static) -- OK
    - Erstellen Ist -- OK
    - Erstellen Wer -- OK
    - Stellungnahme erstellt Status -- OK
    - Stellungnahme erstellt Soll (static) -- OK
    - Stellungnahme erstellt Ist -- OK
    - Stellungnahme erstellt Wer -- OK
    - bereinigt Status -- OK
    - bereinigt Soll -- OK
    - bereinigt Ist -- OK
    - bereinigt Wer -- OK
    
    zur Freigabe:
    - Erstellen Soll (static) -- OK
    - Erstellen Ist -- OK
    - Erstellen Wer -- OK
    - Prüfbericht erstellt Status -- OK
    - Prüfbericht erstellt Soll (static) -- OK
    - Prüfbericht erstellt Ist -- OK
    - Prüfbericht erstellt Wer -- OK
    - Freigabe Status -- OK
    - Freigabe Soll (static) -- OK
    - Freigabe Ist -- OK
    - Freigabe Wer -- OK
    
    Gut zur Ausführung:
    - versendet an BL Ist -- OK
    - versendet an BL Wer -- OK
    - erhalten von BL Status -- OK
    - erhalten von BL Ist -- OK
    - erhalten von BL Wer -- OK
    - übergeben an UN Status -- OK
    - übergeben an UN Soll (static) -- OK
    - übergeben an UN Ist -- OK
    - übergeben an UN Wer -- OK
    
{{{#!wiki comment
      
    From server to Excel via "Refresh selected":
    - After document (VA, ZF, FR) completed inside Excel and refreshed
      via "Refresh selected":
      receiving repeated (3 times) message getDocumentAndResponse:
      Internal error: Invalid XML response. HTTP response 200 Response
      text: [...]
    
    - Bereich
    - Gruppe
    - Archiv
    - Berechtigung Server (Z)
    - Klassifizierung Projektphase (Ph)
    - Klassifizierung Fachbereich (FB)
    - Objekt (O)
    - Kontakt (K) 
    - Dokumenttyp (DT)
    - Laufnummer ?
    - Dokumenttitel 1
    - Dokumenttitel 2
    - Dokumenttitel 3
    - Dokumenttitel 4
    - Massstab
    - Format
    - Version

    Vorabzug:
    - Erstellen Soll
    - Erstellen Ist
    - Erstellen Wer
    - Stellungnahme erstellt Status
    - Stellungnahme erstellt Soll
    - Stellungnahme erstellt Ist
    - Stellungnahme erstellt Wer
    - bereinigt Status
    - bereinigt Soll
    - bereinigt Ist
    - bereinigt Wer
    
    zur Freigabe:
    - Erstellen Soll
    - Erstellen Ist
    - Erstellen Wer
    - Prüfbericht erstellt Status
    - Prüfbericht erstellt Soll
    - Prüfbericht erstellt Ist
    - Prüfbericht erstellt Wer
    - Freigabe Status
    - Freigabe Soll
    - Freigabe Ist
    - Freigabe Wer
    
    Gut zur Ausführung:
    - versendet an BL Soll
    - versendet an BL Ist
    - versendet an BL Wer
    - erhalten von BL Status
    - erhalten von BL Soll
    - erhalten von BL Ist
    - erhalten von BL Wer
    - übergeben an UN Status
    - übergeben an UN Soll
    - übergeben an UN Ist
    - übergeben an UN Wer

***

High-Priority Items

• Internals

1. CRITICAL Implement cell locking helper functions that make cells read-only/non-read-only - Implementation initial implementation that just sets field's
   • background color to indicate that the field is locked. -- OK
2. TEST Fix handling/display of progress messages: - Convert display of Record count to Revision count in search --
   • (check lower right msg in Excel -- not critical now)
   - All (most?) menu items should clear display area when operation
   • completes -- OK
3. Fix checkDuplicates to check both the drawingCode and drawingId fields -- OK
   • - Set cell note and highlighting when duplicates found -- OK
4. Review/improve cell note set/clear functions: isCellDifference and setCellDifferenceComment -- FAILED
   • - Fix bug when comparing date fields -- FAILED - Do not set cell comments in key set area -- (to be tested) (check title, dates for changes after 'Refresh Selected') make sure, that hidden red fields NEVER have cell comment Note: Date fields should only have a comment after a 'Refresh Selected', if an update on the server side changed dates. If T0 Arbeits-BP (CS) is set, the calculated SOLL dates are not saved on the server -- FAILED If changed in web-interface and 'Refresh Selected' in Excel: - VA Erstellen: Soll, Ist, Wer
     • - Ist -- OK - Wer -- OK
     - VA Stellungnahme
     • - Ist -- OK - Wer -- OK
     - VA Bereinigung
     • - Ist -- OK - Wer -- OK
   Search

1. Implement checking for duplicate worksheet names -- (to be tested)
   • MEANING: mk new worksheet, name it bau23, then search and place result in new worksheet, should give bau24 - Make sure that system doesn't crash if it tries to create a
     • worksheet name that already exists --
   Edit

1. CRITICAL (AH) Implement Vorabzug ja/nein flag - Lock and grey-out all Vorabzug fields if Vorabzug flag set to 0 --
   • test later
   - Disable plan date formulas when Vorabzug set to false -- test later
2. TEST GA/Freigegeben menu: - Sets the RevC revisionTypeId -- OK

   • (CA-CF select -> show; should give you RevC value)

   - Menu with two choices: freigegeben (13) and gut zur Ausführung (15)
   • -- OK
   COMMENT: revisionTypeId changes in cell CE, when switching from 'freigegeben' to 'gut zur Ausführung' and vice-versa, but changes are not saved on the server after 'Save Selected'. -- FAILED Validation

1. TEST Client-side field validation - Implement Ist field validation.-- OK - Formulas generated from Perl script. -- (to be tested)
   • - See axpo-integration/data/genValidation.pl -- (to be tested) MEANING: Test Analog workflow wizard validation (except Soll)!
   Save/Update

1. TEST Save handling of Vorabzug menu and GA/Freigegeben menus - Vorabzug flag -- ?
   • (AP, if already "ja", it should be locked. Check specs)
   - GA/Freigegeben menu -- ?
   • (After it was set, it should be locked.) NOT YET IMPLEMENTED: Basically, after save, both menues are to be locked. MEANING: Both menues determine document codes. Check specs 5.2.1 Axpo Revision Sequence, 5.2.2 Sub-Revision Coding System, and test against them.
2. TEST Implement field locking after save. Fields: - Bereich menu -- ? - Vorabzug flag -- ? - GA/Freigegeben menu (RevC type) -- ?
   • MEANING: Same as above.
3. Generate client-side error on update/insert when drawing code not complete -- (to be tested) MEANING: Test new document with deliberately missing some settings for document code and "Save Selected" to receive expected error!
4. Implement checking of record serial numbers on update -- (to be tested) MEANING: i.e. If you change title, B4 should increment value on row
5. updateCount mismatch: update doc in web-interface, then try to save same document in excel, should give error
6. Save all/selected should set record update counts -- (to be tested) - Test that a second save results in state with correct record update
   • counts -- (to be tested)
   MEANING: Same as above.
7. Duplicate row detection before: - search / test cases -- ? - save all / save selected -- ? - refresh all / refresh selected -- ? MEANING: Check specs for it and test against. Attributes

1. Get feedback from JS re: recent changes to m_attributeRowCount global variable -- ? - Make sure that handling of m_attributeRowCount global variable is OK
   • -- ?
   MEANING: Test 'Refresh Attributes' get all metadata from server.

Medium-Priority Items

1. TEST Performance: - Disable screen update during record list processing -- ? - Investigate to see if bubble sort is really needed. Debug prints? --
   • ?
   MEANING: create many records. User Search and measure how long it takes.
2. Implement new columns to display record status and DrawMGT id values (design needed) -- (to be tested) MEANING: Cell R, if gelb = change, pink = error, white = ok
3. Design for record status fields - May not be needed if the above highlighting is implemented -- ? MEANING: Same as above.

Excel Start-Up

- User can start Excel with an empty planning spreadsheet or a workbook

• containing already-defined document and revision metadata -- OK

- To access metadata in the DrawMGT server, the user must login, using a

• custom Excel menu item, using their normal DrawMGT login name and password. -- OK

- After logging in, spreadsheet data can be downloaded and saved in the

• DrawMGT server -- OK

- If the user does not login, he is still able to work off-line, e.g.

• perform operations that do not require access to the DrawMGT server -- OK

Downloading Metadata

• - The user can download metadata from the DrawMGT server using a
  • search operation -- OK
  - It is possible to search by: document title, code and classification
  • attributes -- OK
  - In the search screen, the drop down menus of classification
  • attributes can be shortened by eliminating attributes not relevant to the planning process. -- (ignore for now)
  - Search results are processed as followed:
  • - Search results are merged with an existing worksheet. Existing
    • records in the worksheet are updated with the latest server data, new records are added to the worksheet -- OK
  - The DrawMGT server checks the user’s roles and only downloads
  • metadata that the user has rights to view -- FAILED COMMENT: If a user has Dok-beschr.Viewer role only and it is searched for a document that is not completed yet, the document gets still placed in Excel. AMENDMENT: Even if Dok-beschr.Viewer allows to see completed documents, cells still can be changed for a completed document. In my personal opinion, it would be less confusing to have the cells locked in that case. QUESTION: If a user has Dok-beschr.Viewer, shouldn't he be allowed to have a new document being placed in the worksheet after he clicked 'Create New Document' ? ANSWER: User should not be able to do that.
  - A warning is given if a search will return a large number of
  • records, and allow the user to cancel the search -- OK

Creating New Metadata

• - New metadata can be created in the planning workbook using Excel’s
  • normal copy/paste and fill operations -- OK
  - Metadata for new documents and revisions can be saved to the DrawMGT
  • server -- OK
  - The Excel planning spreadsheet cannot be used to delete document and
  • revision records from the DrawMGT server -- OK

Synchronizing Spreadsheet Metadata

• - The document rows in the planning worksheet can be synchronized with
  • latest metadata from DrawMGT server -- OK
  - The planning spreadsheet is able to detect conflicting updates and,
  • the event of a conflict, reports the before/after field values in the worksheet to the user for possible manual inspection and correction -- AMENDMENT: The error message 'Sie sind für diese Transaktion nicht authorisiert.' shown inside an XML wrapper
  - Unsaved data, including new records that have never been saved, are
  • highlighted to alert the user -- (to be tested)

Document and Revision Workflows

• - Management of workflow steps associated with the Vorabzug, zur
  • Freigabe and Gut zur Ausführung / Freigegeben workflows. Includes person responsible, planned/actual complete dates and status (for workflow steps with a status field): - Erstellen --OK - Stellungnahme and Prüfberichte -- OK - Freigabe -- OK
  - Display of appropriate workflow step names in the DrawMGT
  • web-interface (e.g. ‘Bereinigung’ instead of ‘Freigabe’ for Vorabzug revisions) -- (to be tested)
  - Calculate revision workflow step plan-dates using standard date
  • calculation formulas -- (to be tested)
  - Override the standard workflow date formulas on an individual
  • document and revision basis -- (to be tested) MEANING: If I change any date delta, is the complete chain re-calculated and saved correctly. Also test against diagram I received from Alan.
  - The DrawMGT server performs validation before saving data
  • - The user’s roles are checked and enforced -- (to be tested) - Data values are validated -- OK

Reports

• - Reports Dokumentlieferungplan ('Create Report 02F1') and
  • Resourcenplan ('Create Report 02F1') can be generated from the planning worksheet -- (to be tested)

Saving Metadata

• - The user can save one or more selected rows -- (to be tested) - The save operation checks each record for conflicting updates on the
  • DrawMGT server -- (to be tested)
  - All records where a conflict (or error) is detected are highlighted.
  • Both the local and server field values are available, so that the user can manually correct the record. -- (to be tested)
  - All records that can be saved without conflicts are saved and
  • de-highlighted -- (to be tested)

Exiting Excel

• - If there are any unsaved records and the user attempts to exit
  • Excel, a warning is displayed and the user is given the option to abort the exit -- (to be tested)

Document and Revision Process Operations

• - Ability to create sub-revisions in order to capture the history of
  • rejected and internally reviewed revisions. -- (to be tested)
  - Ability to link documents to other documents, including:
  • - Linking a document to a document, without specifying which
    • revision -- (to be tested)
    - Linking a document to a specific revision or the latest revision
    • -- (to be tested)
  - New document types:
  • - Prüfbericht -- OK - Stellungnahme -- OK
  - Ability to set a ‘revision date’ to all revisions of a document,
  • independent of the dates associated with the revision’s workflow -- (to be tested)
  - Removal of planned titles from document records (requires that every
  • document have revision defined, which will contain the title) -- (to be tested)
  - Ability to assign a serial number and prefix (e.g. ‘PBR-LA2-0001’)
  • to a document as a classification attribute. The serial numbers should assigned sequentially and be unique for a given prefix. -- OK

Login, Start-Up and Exit

• - User can provide 'Benutzername', 'Passwort', select from a list of
  • servers, and log-in -- OK Start-Up Procedure

• If the user successfully logs into the DrawMGT server then a procedure is initiated which downloads the following into the spreadsheet:
• The latest VBA code for the spreadsheet -- (to be tested)
• Attribute lists -- (to be tested)
• User lists -- FAILED
  • COMMENT: When I set user 'Hans Muster' on loki test-server, and 'Refresh Selected' in Excel, the user displayed is 'Albert Franz'.
• User role lists -- (to be tested)

Create New Document

• - click menu ribbon 'Create New Document', cells are filled with
  • template data -- OK
  AMENDMENTS: - Dokumenttitel 2 should be 'Titel-2' - it should not be possible to enter chars in Lauf-/ Teilnummer cells

Refresh Selected

• After change in web-interface - update document code -- OK - update title -- FAILED - update VA Erstellen (Ist, Wer) -- OK - update VA Stellungnahme (Ist, Wer, Status)
  • Ist -- OK Wer -- FAILED (is 'Abert Franz' should be 'Muster Hans') COMMENT: 'Muster Hans' is not found in worksheet 'Users'. Therefore, I guess the misassignment happens because the users list is updated from a source other than loki? Status -- OK
  - update VA Bereinigung (Ist, Wer, Status) -- OK - update ZF Erstellen (Ist, Wer) -- OK - update ZF Prüfbericht (Ist, Wer, Status) -- OK - update ZF Freigabe (Ist, Wer, Status) -- OK - update GA Versand (Ist, Wer) -- OK - update GA erhalten von BL (Ist, Wer, Status) -- OK - update GA übergeben an UN (Ist, Wer, Status) -- OK

Save Selected

• - update document code -- OK - update title (with Umlauts) -- OK - update VA Erstellen Soll -- OK - update VA Stellungnahme Status -- OK - update VA Stellungnahme Dauer -- OK - update VA Stellungnahme Soll -- OK - update VA Bereinigung Status -- OK - update VA Bereinigung Dauer -- OK - update VA Bereinigung Soll -- OK - update ZF Erstellen Dauer -- OK - update ZF Erstellen Soll -- OK - update ZF Prüfbericht Status -- OK - update ZF Prüfbericht Dauer -- OK - update ZF Prüfbericht Soll -- OK - update ZF Freigabe Status -- OK - update ZF Freigabe Dauer -- OK - update ZF Freigabe Soll -- OK - update GA erhalten von BL Status -- OK - update GA erhalten von BL Dauer -- OK - update GA übergeben an UN Status -- OK - update GA übergeben an UN Dauer -- OK - update GA übergeben an UN Soll -- OK

Check for Duplicates

• -- OK

Duplicate Selected

• -- OK

Get Next Sequence

• -- OK

}}}