= Excel Integration - Handling Server Updates =

 * 2011-07-25, AH

<<TableOfContents>>


= Introduction =

This section describes a proposal for handling updates made to plan dates in revision workflows using DrawMGT's web interface. This is to solve the problem that plan dates entered via a browser are lost when they are overwritten by the plan date calculated in the Excel spreadsheet.


= Plan Date Calculations =

In the Excel spreadsheet, the plan date for a revision's workflow step is calculated based on:

 1. The plan date field associated with the following workflow step, or the '''''T0''''' field for last workflow step
 1. The '''''delta''''' field associated with the workflow step

Normally the plan date calculations are performed in the Excel spreadsheet and the results are stored in the DrawMGT database.
Using the Excel spreadsheet, the plan dates fields are always calculated and the results are displayed in read-only columns. The plan dates can only be changed indirectly by changing the ''T0'' and ''delta'' fields. This means than the plan dates and ''delta'' fields for a series of revisions are always ''consistent'' when created/updated using the Excel spreadsheet.

The plan dates and ''delta'' information can also be viewed and updated using using DrawMGT's web interface.
When using DrawMGT's web interface it is possible to save plan dates and delta fields without regard to the calculations performed by the Excel spreadsheet. This results in inconsistent data, where plan dates do not match those that would be calculated by the Excel spreadsheet.

When workflow information is read into the Excel spreadsheet, plan dates values from the database are ignored and replaced by values calculated locally in the spreadsheet. The result is the loss of any plan date information entered via the browser that is not consistent with the ''T0'' and ''delta'' values.

The problem is compounded because the ''T0'' field, the initial value from which all the plan dates are calculated, is stored in a ''used defined'' field in the DrawMGT database and can only be entered and updated via the Excel spreadsheet. This means that all currently entered plan date values are at risk of being overwritten by the Excel spreadsheet.


= Proposed Solution =

The proposed solution has two parts:

 1. Migrating existing plan date fields and making corrections to ensure consistent ''T0'' and ''delta'' values
 1. Highlighting inconsistent plan dates in the Excel spreadsheet, giving users the chance to make corrections


== Migrating Existing Plan Dates ==

The plan date data currently stored in the production database does not have any ''T0'' or ''delta'' values associated with it. The ''T0'' and ''delta'' values are stored in user defined fields and are only available in DrawMT V11.5.

The migration of the current V11.3 database to V11.5 should attempt to set ''T0'' and ''delta'' values where possible.

Conditions for setting ''T0'' and ''delta'' values in a revision:

 1. The revision must be of one of the following types:
    * ''gut zur Ausführung''
    * ''Freigegeben''
    * ''zur Freigabe''
    * ''Vorabzug''

 1. The revisions must have one or more plan date values set

Assumptions for setting workflow plan dates, ''delta'' and ''T0'' values:

 1. Plan dates in the database take precedence over  ''delta'' values
    * ''Delta'' values can be adjusted to ensure the consistency between the plan dates
 1. Default ''delta'' values can be used to calculate the ''T0'' date and any missing plan dates
 1. Documents which have consistency errors that cannot be easily be resolved, will be placed in a list for manual checking and correction

Suggested algorithm for setting the plan ''T0'', date and effort fields (will be part of the V11.3 to V11.5 database migration):

 1. Create a temporary table to contain, in a single record, all the revision ids, revision type ids and the following workflow fields:
    * All plan date fields
    * All ''delta'' fields (effort fields)
    * ''T0'' field
    * ''T0wv'' field (?)

 1. In a revision sequence, find the first and last workflow steps that have plan dates set
    * Set default ''delta'' values and calculate plan dates ''backwards'' from the first plan date
    * Set default ''delta'' values and calculate plan dates and the ''T0'' date ''forwards'' from the last plan date
    * '''To do:''' Define an algorithm for setting the ''delta'' values and plan dates between the first and last plan dates that are already set. It must be able to handle:
      * Intermediate plan dates set
      * Intermediate ''delta'' values set

 1. Use the completed temporary table, which will now have consistent ''T0'', ''delta'' values and plan dates, to update corresponding fields in the original revisions records

=== Migration Issues ===

 1. Current only the revision type ''Vorabzug'' is defined in the V11.3 database. There are no revisions of type  ''zur Freigabe'', ''gut zur Auzführung'' or ''Freigegeben''. Need to determine if any existing revisions should have their revision types changed so that they are recognized as planning documents.

== Highlighting Inconsistent Plan Date in Excel ==

It will always be possible to save inconsistent and incomplete ''delta'' values and plan dates using a web browser. Therefore the Excel spreadsheet must be able to highlight inconsistent data for manual checking and correction.

  * '''Proposal:''' Create cell comments containing the plan dates read from the database, when they do not match those calculated by the Excel spreadsheet

Notes:

  1. The normal date calculations will always be performed
  1. The user manually enters/adjusts the ''T0'' and ''delta'' values to match the plan dates in the cell comments