Differences between revisions 23 and 24

Excel Integration - To Do List

Back

Contents

Excel Integration - To Do List
Priority/Critical Items to Complete ASAP
Critical Items
Attribute Lists
Login/Logout
Search
Data Display
Data Editing
Updating and Inserting Records
Internals and Refactoring
Reports
Compatibility
Internationalization
Testing
Internal Documentation
User Documentation

Priority/Critical Items to Complete ASAP

Merge code
Next Goal: Complete all major functions
Complete all critical design items

Critical Items

Complete search into existing worksheet (JS)
Complere insert/update (AH)
Implement remaining planning list columns: (JS)
- Delta menu
- Vorabzug flag
- GA/Freigegeben flag
Design remaining open items: (Design needed)
- Assigning revisionNo and revisionCode to new records
- Restart revision sequence
Implement: Restart revision sequence

Attribute Lists

Make sure Refresh Attributes still works
Shorten attribute lists (Design needed: AH/TN)
- Via server-side config?

Implement login timout (low-priority)
Implement code that reacts correctly to auto-logout (session-timeout)
- Implement a heartbeat function?
Check user roles and disallow login is certain roles not present (low priority)
Login in a specific contract? (low)

Search

Search request includes max records and allow for cancel/continue (AH)
Save server URL in planning worksheet and implement checks to make sure that future searches and updates and done on the same server (Design needed)
Searches should add/update existing worksheet (JS)
Improve new worksheet naming
- Worksheet count should exclude the number of hidden worksheets
Implement group menu with dependency on bereich menu (Design needed) (AH?)
Test search fields:
- document code
- title
- revision date from/to
- external reference number
Implement named baskets (Design needed) (AH/TN)
- User can select a named basket download its contents
Display progress messages in lower message area

Data Display

Implement group menu (JS)
- Including link to contract menu
Implement delta menu (JS)
- Column BK Art (Umfang, Inhalt)
Implement Vorabzug ja/nein flag (JS)
Implement GA/Freigegeben menu: (JS)
- Sets the RevC revision type
- Menu with to choices: freigegeben (13), gut zur Ausführung (15)
Implement document links (AH/JS/TN) (Design needed)
- Handling Grundlagen document links
- Handling PRF/STN document links
Performance:
- Disable screen update during record list processing
- Measure performance with large data sets
- Profile code to see what is being called for each document/revision
- Investigate to see if bubble sort is really needed
Fix bug: cell notes for record differences on date fields

Data Editing

Check for duplicates (JS)
Implement duplicate selected rows (JS)
Implement get next sequence number (AH?/JS/TN)
Highlighting of changed/new/unsaved records (JS)
Restart revision sequence (AH) (Design needed)
Setting the revisionNo and revisionCode on new revisions (AH) (Design needed)
Test contract and group menu changes
Client-side validation:
- All dates are in DD.MM.YY format
- Ist-dates: Ist date for workflow step must be >= previous Ist-date
- Soll-dates: automatically calculated, no checking needed
- No checking between Ist and Soll dates is required
- Wer vs. status:
  - Offen status: Wer can be empty or set to a user
  - non-Offen: Wer must be set to a user
- Workflow status vs. Ist-date:
  - Offen status: Ist-date must be empty
  - non-Offen: Ist-date must be set to a date <= today
- Workflow steps must be completed in order
  - 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-open value.
  - Note that all the status menus default to the appropriate offen status value.
- Data types: t0 and t0wv must be dates
- Data types: Resource fields must be numbers
  - Resourcenplannung Planer & PL - can be integers or floating point values
  - von/bis - must be dates
  - Verteiler - must be integers
- Date format enforcement via data formats in layout sheet?
- Read-only fields:
  - Doku.-Nr - drawing code
  - Soll dates
  - Ver. - revision code (Design needed)
Test copy-paste operations
Trapping Exit and asking user if unsaved records should be changed

Updating and Inserting Records

Complete implementation of combined revision save/update (AH)
Duplicate row detection before save
Handle setting of revisionNo and revisionCode for new records
Implement checking of record serial numbers on update
Handle id for updated records
- Values returned in XML from server
Improve reporting of insert/update errors (report server-side error text)
Generate errors on update/insert when drawing code not complete

= Reject Revisions and Sub-Revisions (Design needed)

Reject revision (Design needed)
Sub-revision processing (Design needed)

Internals and Refactoring

Review TODOs in code
named ranges
- Automated management
- Function for repair/reset of named ranges in case of corruption
- Clone worksheet
- Document named ranges and their boundaries
Document and revision (write) record structures
Improved worksheet lookup/access functions (getCellValue/Address)
Improved row selection for printing to worksheet
Refactoring define names (to improve clarity and remove near-aliases)
Identify magic numbers and convert to Consts
Improve 'regularity' of function naming

Reports

Implement Bau report (Design needed)
Implement Ausrustung report

Compatibility

Setup Excel 2007 test/development environment
Implement Excel 2003 menu

Internationalization

Convert all user display text to Const strings
Translate all messages Consts into DE
Implement tag and lookup for form text (Design needed)

= Final Cleanup and Packaging

Hide internal worksheets
Nice if client-side and datatype checking and validation works off-line
Hiding of internal worksheets (header structure, etc.)
Hiding of system columns (key set columns) in lists
Get code signed with a Axpo certificate?
Assign password to code

Testing

Write a test-plan wiki page
Make sure ZK/TN/DD have suitable test environments
Check reading and updating of all fields
Stress/performance testing
- Searching for long lists
- Bad input data
Character sets: test with Umlauts
- Test Umlauts in UTF-8 set to server
- Test Umlauts in ISO-8859-1 set from server
Compatibility testing
Search tests
Display tests
List menu tests
- Status menus
- User menus
- Flag menus
Editing tests
- Delta date calculations
- Drawing code menus
Insert tests
Update tests
Client-side validation tests
Menu item tests
- Check for duplicates
- Duplicate selected
Performance tests
HTTPS test
HTTP/HTTPS proxy test

Internal Documentation

List of modules
List of public subs/functions
Convert notes/DesignNotes into Wiki
Revision sequence
Named ranges
URLS for REST transactions
XML structures
- Returned records
- Insert/update records

User Documentation

Define outline/structure for user documentation
Setup wiki pages with

-Deletions are marked like this.
+Additions are marked like this.
 Line 9:
-'''Next Goal: prepare for weekend session 18-19 Mar 2011'''



== JS Tasks ==



  1. Complete '''user id menus'''

     * Reset user id menus on contractId change



  1. Complete '''contract menu''', hidden contractId column and display menu

     * onChange should change the contractId filter on ''group menu'' (see below) 



  1. Complete '''group menu''': (Col Q.) Probably including hidden groupId column 

     * Filter based on contractId (in attribute list)

     * Re-filter and display menu when contract changed (see above) 



  1. Complete '''classification menus''' for drawing codes components. Cols T to Y. (This may already be complete)

    * Only need display and the code values to support the drawing code formula

    * These classification menus are '''not''' needed to prepare classification id values for inserts/update. The actual id values will be inferred directly from the drawing code on the server.



  1. Complete '''drawing code formula''' Col S, based on Cols T to AA. Notes:

   * ''Laufnr'' (col Z) is optional and if present is 4-digit integer with leading zeros

   * ''Teilnr'' (col AA) is optional and if present is a string with a preceeding '''-''' (dash) character.



  1. Implement '''workflow step status id''' menus 

    * Filter '''prozessStatus''' attribute list based on the mapKeyId (last column in header)

    * Handle new attribute list field '''approvalStatusListId''', which is the filter value corresponding to the ''mapKeyId''



  1. Complete '''main menu''' setup

    * See definition in the [[ExcelIntegrationRequirements#MainMenu|Excel Integration Requirements Page]]. This defines the groups and menu items

    * Create menu entries and ''mn_'' functions that call functions in the Lib modules

    * Select icons as appropriate



  1. Complete '''date calculation formulas'''

    * Test case 7119 has realistic values for ''t0. t0wv & delta1-->delta7'', which should allow calculation of all ''Soll'' dates.

    * Make sure the formulas are inserted into the planning sheet and actualy perform calculations.

    * '''Test notes 2011-03-18''': Formulas with the ''Bau'' header always display the value '''1'''

      * Suspect a ''header field name alias'' problem. We have header column names of ''formulaFlag'' and ''formula''. I suspect that the ''rngFormula'' is getting an incorrect range. One invertigation with debugger showed that the ''vout'' variable never contained the source formula text from the header structure's formula column. It appears that ''getCellAddress'' is returning an incorrect range.

      * Many fields have a have formula defined, but have the field type ''direct'', which means the formulas are never processed. Ideally the formula result is saved in the database, but a calculated value is presented in the Excel list.

      * The header structure field ''formulaFlag'' is never used!

 

  1. --(Fix '''RevA, RevB, RevC''' processing)--

    

  1. Set '''display field types/validation''': e.g. set cell validation for ''dates, ints, floats, menus,'' etc. as defined in the header



  1. Complete '''Archiv menu''' Col R. Note that the column header is currently incorrect.



    * Implement the classification menu and make sure ''siteClassification3Id'' is available for writeback/update (via getForeignKey?)



  1. '''Milestone:''' All fields available for update/insert (using neu ''Bau'' header structure)

 

  1. '''Milestone:''' Demonstrate that date calculation formulas work for calculating the date sequences





== AH Tasks ==



 1. --(Implement global login flag)-- '''Check logout function works'''



 1. '''Update''' Complete write-back of all revision fields

    * Check handling of update count flag



 1. '''Insert''' Get insert code working

    * Possibly implement 

    * Deal with setting of revision code (calculate based on revisionNo?)



 1.  Implement '''selected & all drivers''' for menu functions



 1.  Design/implement server selection (Probably in a global variable, selected by a menu item?) -- '''low priority'''





 * --(Make sure bau and old bau can be used without restarting)--

 * --(Complete ''Requirements and Use Cases'' Wiki pages)--

 * --(Implement REST transaction for downloading workFlowStatus.)--

    * --(Added new attributes list: '''prozessStatus''', with key: ''displayWorkFlowStepId'' and fields: ''code, name, approvalStatusListId'')--

 

 * --(Create an SQL script in the migration that creates a large number of  ''Vorabzug, zur Freigabe & gut zur Ausführung'' revisions, to be used for testing with multiple rows)--

 * --(Fix the duplicated Group column in the layout, the second columns should probably be ''Archiv'', also fixed 2nd line of header, where the group cells had been corrupted, and column widths for some of the wider columns.)--

 * --(Re-order the fields in the search form to confirm to the order in the DrawMGT web GUI)--

 * --(Fix writing of title fields - should write to all revisions)--

 * --(Cleanup VBA code formatting)--

 * --(Cleanup layout sheet in in Lib, change yellow cells to appropriate gray color)--

 * --(Implement display of user defined fields after search)--

   * --(Fix paths in header structure)--

   * --(Add case for handling user defined fields)--





= Main Design Issues (HIGH, DESIGN, AH/JS) =



  1. Able to manage multiple planning worksheets in a single workbook

  1. General design of ribbon/menu

  1. How does user create new rows?

  1. How does user copy rows?

  1. Catching exit when rows unsaved

  1. Restart revision sequence

  1. Can files be transfered between servers? (difficult: id value problems)

  1. Operations performed on all rows vs. selected rows





= Login/Logout (AH) =



  1. --(Implement login flag '''(HIGH)''')--

  1. --(Implement logout function  '''(HIGH)''' )--

  1. Implement server select menu and default server  '''(MEDIUM)''' 

  1. Design for login timeout from server '''(HIGH, DESIGN)'''

  1. --(All dispatches should check for login flag '''(HIGH)''')--

  1. Check user roles and disallow login is certain roles not present '''(LOW)'''

  1. Login in a specific contract? '''(LOW)'''
+ * Merge code

 * '''Next Goal: Complete all major functions'''

 * Complete all critical design items





= Critical Items =



 1. Complete search into existing worksheet '''(JS)'''

 1. Complere insert/update '''(AH)'''

 1. Implement remaining planning list columns: '''(JS)'''

    * ''Delta'' menu

    * ''Vorabzug'' flag

    * ''GA/Freigegeben'' flag

 1. Design remaining open items: (Design needed)

    * Assigning ''revisionNo'' and ''revisionCode'' to new records

    * ''Restart revision sequence''

    *

 1. Implement: ''Restart revision sequence''
-Line 117:
+Line 31:
-. GUI for refreshing the attribute lists '''(HIGH, JS)''' 

    - Any problems if user roles change? May need to refresh user menus

  1. Need REST transaction for display status menu texts '''(HIGH, AH/TN)''' 

  1. GUI side collection of display status lists '''(HIGH, JS)''' 

  1. Shortening menus to restrict them to planning relevant items '''(LOW, WHO?)''' 





= Searching =



  1. Order of menu items in search window '''(MEDIUM, AH)''' '''- Done by TN, to test'''

  1. Handling large result sets (can wait?) 

     - GUI defines a pre-defined max limit

  1. --(Search by document title '''(MEDIUM, AH)''')--

  1. --(Search by document code '''(MEDIUM, AH)''')--

  1. --(Search by external key '''(MEDIUM, AH)''')--

  1. --(Search by from/to revision date '''(MEDIUM, AH)''')--



= Display Data From Server =



  1. Merge search results with existing rows '''(HIGH, JS)''' 

  1. See UserStatusMenusNotes:

     - User menus based on roleId coded in header structure

     - Status menus based on approvalStatusListId coded in header structure

  1. Delta menu '''(HIGH)'''

  1. Vorabzug flag menu (je/nein flag) '''(HIGH, JS)'''

  1. Formulas '''(HIGH, JS/AH)'''

  1. Data types and format '''(HIGH, JS)'''

     - Dates

     - Numbers

     - Implement rules

  1. Client-side cross-field validation '''(HIGH, DESIGN, AH/JS)'''

     - Define rules

     - Complete date and status

     - Sequence of complete dates

     - sequence of plan dates

  1. Document links '''(MEDIUM, WHO?)'''

     - Grundlagen links: to a specific document

     - STN (Stellungnahme) & PRF (Pruefberichte) links: to a specific serial index prefix & number

     - How to handle link deletion when links changed (what to delete?)

  1. Highlighting changed but unsaved rows '''(HIGH, JS)''' 

  1. Highlighting non-saved rows '''(HIGH, JS)'''



= Refresh Rows =



  1. Refresh all vs. refresh selected '''(HIGH, DESIGN?, JS)'''

  1. Comments with cell differences '''(HUGH, JS)'''



= Editing List Data =



  1. Row handling and creating new rows: '''(HIGH, DESIGN?, AH/JS)'''

     - How to create new rows: via menu or copy paste?

       - Insert at end of list

       - Insert at current position

     - Default delta values for new rows?

     - Users should be able to sort/rearrange rows

     - Is copy & past possible?

     - Can fill down be used? Axpo would really link it

       - What happens in menu columns?

  1. Deleting rows should be possible

  1. Locking non-editable columns '''(HIGH, JS)'''

  1. Get next sequence number '''(HIGH)'''

     - Client-side processing '''(AH)'''

     - Server-side transaction '''(JS)'''

  1. Managing doc-doc links '''(MEDIUM)'''

     - Client-side processing '''(AH)'''

     - Server-side transaction '''(JS)'''
+. Make sure ''Refresh Attributes'' still works

 1. Shorten attribute lists (Design needed: AH/TN)

    * Via server-side config?





= Login/Logout =



 1. Implement login timout ''(low-priority)''

 1. Implement code that reacts correctly to auto-logout (session-timeout)

    * Implement a heartbeat function?

 1. Check user roles and disallow login is certain roles not present '''(low priority)'''

 1. Login in a specific contract? '''(low)'''





= Search =



 1. Search request includes max records and allow for cancel/continue '''(AH)'''

 1. Save server URL in planning worksheet and implement checks to make sure that future searches and updates and done on the same server (Design needed)

 1. Searches should add/update existing worksheet '''(JS)'''

 1. Improve new worksheet naming

    * Worksheet count should exclude the number of hidden worksheets

 1. Implement group menu with dependency on bereich menu (Design needed) '''(AH?)'''

 1. Test search fields:

    * document code

    * title

    * revision date from/to

    * external reference number

 1. Implement named baskets (Design needed) '''(AH/TN)'''

    * User can select a named basket download its contents

 1. Display progress messages in lower message area





= Data Display =



 1. Implement group menu '''(JS)'''

    * Including link to contract menu

 1. Implement delta menu  '''(JS)'''

    * Column '''BK''' ''Art (Umfang, Inhalt)''

 1. Implement Vorabzug ja/nein flag '''(JS)'''

 1. Implement GA/Freigegeben menu:  '''(JS)'''

    * Sets the RevC revision type

    * Menu with to choices: freigegeben (13), gut zur Ausführung (15)

 1. Implement document links '''(AH/JS/TN)''' (Design needed)

    * Handling Grundlagen document links

    * Handling PRF/STN document links

 1. Performance:

    * Disable screen update during record list processing

    * Measure performance with large data sets

    * Profile code to see what is being called for each document/revision

    * Investigate to see if bubble sort is really needed

 1. Fix bug: cell notes for record differences on date fields





= Data Editing =



 1. Check for duplicates '''(JS)'''

 1. Implement duplicate selected rows '''(JS)'''

 1. Implement get next sequence number '''(AH?/JS/TN)'''

 1. Highlighting of changed/new/unsaved records '''(JS)'''

 1. Restart revision sequence '''(AH)''' (Design needed)

 1. Setting the revisionNo and revisionCode on new revisions '''(AH)''' (Design needed)

 1. Test contract and group menu changes

 1. Client-side validation:

    * All dates are in ''DD.MM.YY'' format

    * ''Ist''-dates: ''Ist'' date for workflow step must be >= previous ''Ist''-date

    * ''Soll''-dates: automatically calculated, no checking needed

    * No checking between ''Ist'' and ''Soll'' dates is required

    * ''Wer'' vs. status:

      * ''Offen'' status: ''Wer'' can be empty or set to a user

      * non-''Offen'': ''Wer'' must be set to a user

    * Workflow status vs. ''Ist''-date:

      * ''Offen'' status: ''Ist''-date must be empty

      * non-''Offen'': ''Ist''-date must be set to a date <= today

    * Workflow steps must be completed in order

      * 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-open value.

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

    * Data types: ''t0'' and ''t0wv'' must be dates

    * Data types: Resource fields must be numbers

      * ''Resourcenplannung Planer & PL'' - can be integers or floating point values

      * ''von/bis'' - must be dates

      * ''Verteiler'' - must be integers

    * Date format enforcement via data formats in layout sheet?

    * Read-only fields:

      * ''Doku.-Nr'' - drawing code

      * ''Soll'' dates

      * ''Ver.'' - revision code (Design needed)

 1. Test copy-paste operations

 1. Trapping Exit and asking user if unsaved records should be changed
-Line 187:
+Line 123:
-. Save all vs. save selected  '''(HIGH, JS)'''

  1. Check for conflicts before saving (update count)

     - Is this done by server already?

     - Client must not save conflicts (for whole row? or Rev by Rev?)

  1. Can same doc/rev record structure be used for both insert & update?

  1. Review field mappings for doc/rev record structures '''(HIGH, AH)'''

  1. Duplicate detection '''(HIGH, DESIGN?, AH/JS)'''

     - Partially implemented

  1. Reporting server side validation errors '''(HIGH, AH)'''

  1. Setting revision dates '''(HIGH, AH)'''



= Sub-Revisions (LOW) =



  1. Start new revision sequence



= Error Handling (MEDIUM,DESIGN?) =



  1. Better reporting of details of server errors



= Internals =



  1. Named ranges '''(HIGH, DESIGN?, JS)'''

     - Automated management

     - Function for repair/reset of named ranges in case of corruption

     - Clone worksheet

     - Document named ranges and their boundaries

  1. Document and revision (write) record structures
+. Complete implementation of combined revision save/update ''(AH)''

 1. Duplicate row detection before save

 1. Handle setting of ''revisionNo'' and ''revisionCode'' for new records

 1. Implement checking of record serial numbers on update

 1. Handle id for updated records

    * Values returned in XML from server

 1. Improve reporting of insert/update errors (report server-side error text)

 1. Generate errors on update/insert when drawing code not complete





= Reject Revisions and Sub-Revisions (Design needed)



 1. Reject revision (Design needed)

 1. Sub-revision processing (Design needed)





= Internals and Refactoring =



 1. Review ''TODO''s in code

 1. named ranges

    * Automated management

    * Function for repair/reset of named ranges in case of corruption

    * Clone worksheet

    * Document named ranges and their boundaries

 1. Document and revision (write) record structures

 1. Improved worksheet lookup/access functions (getCellValue/Address)

 1. Improved row selection for printing to worksheet

 1. Refactoring define names (to improve clarity and remove near-aliases)

 1. Identify magic numbers and convert to Consts

 1. Improve 'regularity' of function naming
-Line 217:
+Line 157:
-. Implement Bau Report '''(MEDIUM, JS)'''



= Compatibility (MEDIUM) =



  1. Check with Excel 2003 - E.g. non-Ribbon version



= Internationalization (LOW, AH) =



  1. Convert all user display text to Consts

  1. And hard-code in German



= Final Cleanup and Packaging (HIGH, DESIGN, JS) =



  1. File packaging

     - Document use cases

     - Requirement that a single file can be sent to another user

     - Nice if client-side and datatype checking and validation works off-line

  1. Hiding of internal worksheets (header structure, etc.)

  1. Hiding of system columns (key set columns) in lists
+. Implement Bau report (Design needed)

 1. Implement Ausrustung report





= Compatibility =



 1. Setup Excel 2007 test/development environment

 1. Implement Excel 2003 menu





= Internationalization =



 1. Convert all user display text to Const strings

 1. Translate all messages Consts into DE

 1. Implement tag and lookup for form text (Design needed)





= Final Cleanup and Packaging



 1. Hide internal worksheets

 1. Nice if client-side and datatype checking and validation works off-line

 1. Hiding of internal worksheets (header structure, etc.)

 1. Hiding of system columns (key set columns) in lists

 1. Get code signed with a Axpo certificate?

 1. Assign password to code
-Line 239:
+Line 186:
-. Test HTTPS operation

  1. Test HTTP/HTTPS proxy operation

  1. Check reading and updating of all fields

  1. Stress testing   '''(HIGH)'''  

     - Searching for long lists

     - Bad input data

  1. Character sets: test with Umlauts  '''(HIGH)'''  

     - Test Umlauts in UTF-8 set to server

     - Test Umlauts in ISO-8859-1 set from server

  1. Compatibility testing



= Refactoring (MEDIUM/LOW) =



  1. Message catalog

  1. TODO's in code



= Internal Documentation (LOW) =



  1. Convert notes/DesignNotes into Wiki

  1. Revision sequence

  1. Named ranges

  1. URLS for REST transactions

  1. XML structures

     - Returned records

     - Insert/update records





= User Documentation (LOW) =



  1. To do in Wiki
+. Write a test-plan wiki page

 1. Make sure ZK/TN/DD have suitable test environments

 1. Check reading and updating of all fields

 1. Stress/performance testing

    * Searching for long lists

    * Bad input data

 1. Character sets: test with Umlauts

    * Test Umlauts in UTF-8 set to server

    * Test Umlauts in ISO-8859-1 set from server

 1. Compatibility testing

 1. Search tests

 1. Display tests

 1. List menu tests

    * Status menus

    * User menus

    * Flag menus

 1. Editing tests

    * Delta date calculations

    * Drawing code menus

 1. Insert tests

 1. Update tests

 1. Client-side validation tests

 1. Menu item tests

    * Check for duplicates

    * Duplicate selected

 1. Performance tests

 1. HTTPS test

 1. HTTP/HTTPS proxy test





= Internal Documentation =



 1. List of modules

 1. List of public subs/functions

 1. Convert notes/DesignNotes into Wiki

 1. Revision sequence

 1. Named ranges

 1. URLS for REST transactions

 1. XML structures

    * Returned records

    * Insert/update records



= User Documentation =



 1. Define outline/structure for user documentation

 1. Setup wiki pages with