Workflow requires manual date in addition to system status date

[GoogleCodeExporter]

Workflow transitions should have "manual date". e.g. we cannot expect that all 
the workflow transition, "send to the committee:, will be recorded the very day 
it will take place. This also applies for some transitions document types other 
than the bill, e.g. if an MP send a fax with his Question  on Friday 3rd at 5 
p.m. ... we cannot expect that the Clerk office will register it the same day, 
they will input the Question, at best, Monday the 6th. :
          + always have two dates for every work-flow transitions (even if in some transitions they will be the same):
                      # a system date that records when the action of e.g. adding a Question to the system takes place - this is for the audit log - in the examples above it will be "Friday 6th"

                      # a workflow manual date that that records the actual date when a workflow event did take place,  in the examples above it will be "Friday 3rd" (This is the manually entered date)

         + in certain transition the "workflow manual date" will not be editable, e.g. when an MP submit a Question, in other e.g. when the Clerk create a question on behalf of the MP, when the Clerk "submit" the question, "workflow date" will be editable so that, using the example above, the Clerk can set the "submit" date to Friday 3rd while the "system date" will be "Monday 6th

       + dates validation for the manually entered: 
           * it has to be =< of the parliament begin and .... I really wonder is we should prevent to put dates in the future,  I would say that
                      # for all document types expect BILL the "manual date" has to be "=< of Parliament start day and => of today (since here scheduling is handle differently)

                      # for BILL we should have the manual date has to be "=< of Parliament start day and ... nothing else, I would say, not sure ... mistakes can happen ... even in the past ... but nothing that would crash the system ... we could "require" the end date for the parliament to make it at least =< Parliament End date (i.e. in the case when entering a past parliament)

                + the "audit log list" should list both dates "system date" and "workflow date" 

Original issue reported on code.google.com by ashok.ha...@gmail.com on 13 Jul 2010 at 1:12

• Blocking: #612
• Blocked on: #612

[GoogleCodeExporter]

The necessary schema changes have been implemented in r7006: 

Schema changes needed for implementing Issue 612 and Issue 662: 
- effects the 6 *_changes tables: agenda_item_changes, attached_file_changes, 
bill_changes, motion_changes, question_changes, tabled_document_changes
- each of these 6 tables has been modified by changing the single "date" column 
to the 2 columns "date_audit" and "date_active", both of type "timestamp 
without time zone" and "not null" (note the origininal audit "date" column was 
previously nullable).

Existing databases needed to be updated.

The date_audit column is exclusively managed by the system.

The date_active column is user-modifiable, defaulting to the same value as 
date_audit, 
and is the *effective* date i.e. the date to be used for all intents and 
purposes other than for data auditing.

The UI has been updated as follows:
- all workflow views: the "date" column nows shows "date_active";
- all versions views: the "modified" column now shows "date_audit";
- all audit-log views: the "date" column now shows "date_active"; a newly added 
view column "audit date" renders the value of "date_audit";
- all timeline tabs: the "date" column now shows "date_active";

Note currently newly added date_active columns are not as yet exposed for 
editing anywhere in the UI.

To update existing bungeni db instances, execute the following SQL:

----------------------------------------

ALTER TABLE agenda_item_changes RENAME COLUMN date TO date_audit ;
ALTER TABLE agenda_item_changes ALTER COLUMN date_audit TYPE timestamp without 
time zone ;
ALTER TABLE agenda_item_changes ADD COLUMN date_active timestamp without time 
zone not null DEFAULT now() ;
UPDATE agenda_item_changes SET date_active=date_audit ;

ALTER TABLE attached_file_changes RENAME COLUMN date TO date_audit ;
ALTER TABLE attached_file_changes ALTER COLUMN date_audit TYPE timestamp 
without time zone ;
ALTER TABLE attached_file_changes ADD COLUMN date_active timestamp without time 
zone not null DEFAULT now() ;
UPDATE attached_file_changes SET date_active=date_audit ;

ALTER TABLE bill_changes RENAME COLUMN date TO date_audit ;
ALTER TABLE bill_changes ALTER COLUMN date_audit TYPE timestamp without time 
zone ;
ALTER TABLE bill_changes ADD COLUMN date_active timestamp without time zone not 
null DEFAULT now() ;
UPDATE bill_changes SET date_active=date_audit ;

ALTER TABLE motion_changes RENAME COLUMN date TO date_audit ;
ALTER TABLE motion_changes ALTER COLUMN date_audit TYPE timestamp without time 
zone ;
ALTER TABLE motion_changes ADD COLUMN date_active timestamp without time zone 
not null DEFAULT now() ;
UPDATE motion_changes SET date_active=date_audit ;

ALTER TABLE question_changes RENAME COLUMN date TO date_audit ;
ALTER TABLE question_changes ALTER COLUMN date_audit TYPE timestamp without 
time zone ;
ALTER TABLE question_changes ADD COLUMN date_active timestamp without time zone 
not null DEFAULT now() ;
UPDATE question_changes SET date_active=date_audit ;

ALTER TABLE tabled_document_changes RENAME COLUMN date TO date_audit ;
ALTER TABLE tabled_document_changes ALTER COLUMN date_audit TYPE timestamp 
without time zone ;
ALTER TABLE tabled_document_changes ADD COLUMN date_active timestamp without 
time zone not null DEFAULT now() ;
UPDATE tabled_document_changes SET date_active=date_audit ;

----------------------------------------

NOTE however that all modified date_active columns should also be set to be 
"not null", but as there is existing data the db does not allow column 
modifiers to be modified (not easily at least). In a newly created db, the 
columns will be correctly created with "not null" as modifier. 

Original comment by mario.ruggier@gmail.com on 16 Jul 2010 at 2:33

• Changed state: Started

[GoogleCodeExporter]

With regard to the NOTE above for 'date_audit' (not date_active) ... use the 
following script below to update the column nullability on a existing database 
(after running the scripts above) :

ALTER TABLE agenda_item_changes alter column date_audit set not null;
ALTER TABLE attached_file_changes ALTER COLUMN  date_audit set not null ;
ALTER TABLE bill_changes ALTER COLUMN  date_audit set not null;
ALTER TABLE motion_changes ALTER COLUMN  date_audit set not null;
ALTER TABLE question_changes ALTER COLUMN  date_audit set not null;
ALTER TABLE tabled_document_changes ALTER COLUMN  date_audit set not null;

Original comment by ashok.ha...@gmail.com on 19 Jul 2010 at 11:12

[GoogleCodeExporter]

r7046 implements the remaining bulk of this feature: 
- added an "date_active" input widget to the workflow form
- as the ore.workflow package only foresees that a COMMENT be passed to the 
transition handlers, had to bypass that altogether -- the alternative way to 
pass along this data  is via a "change_data" object attached to the request. 
- the "date_active" form field is validated that (a) it is not older than the 
date_active of the most recent transition of the same object or (b) it is not 
older than the current parliament start_date (c) it is NOT in the future. 

Remaining issues:

- the "creation" transition date_active, for any object, may not currently be 
set manually -- for this the creation form for each object would need to be 
modified to support a user-modifiable date_active input field (but this should 
probably be considered more part of Issue 612 i.e. past data entry) 

- there is NO object-specific of transition-specific variations relating to how 
the data_active field is handled i.e. if for some object/transition/user 
scenario this field may need to be NOT available (e.g. when an MP submits a 
Question) -- this is still to be done (requires that all such scenarios be 
clarified). 

Original comment by mario.ruggier@gmail.com on 21 Jul 2010 at 10:06

[GoogleCodeExporter]

Closing All these issues -- As the list is being updated to match with the 
whiteboard backlog.

Original comment by ashok.ha...@gmail.com on 7 Sep 2012 at 7:57

• Changed state: Closed