Cancel Entity Documentation
rns 13/06/2002

The cancel_entity feature allows barcoded items to have their status changed to cancel. For each type of item it will follow a defined set of rules to decide if the item may be cancelled and perform any other other updates as a result.

The program is designed to work on barcoded items in any tracking type database instance. It should be easy to add new item types without changing the core code. It should also be simple to add new database instances. The information stored to facilitate this is as follows: (item type equates to barcode prefix)

%alias - relates database names (sql_engine) to the three letter code in barcode_prefix.instance. e.g. CAS/tracking, SNP/snp. Simply change contents of this hash to point to dev versions of databases.
%db - DB instance each item type is stored in. The barcode_prefix table in the tracking database is queried to find the instance each id_prefix (and hence item type) is found in - results stored in %db. DB name is the three letter code in the instance column of barcode prefix (e.g SNP, CAS).
%STATEMENTS - relates each database code to a sql_engine statements structure containing prepared sql for that database.

Program structure

The body of the program is based around loops in check_entry() and update_db() subroutines. These are generic and should work for any given entity type and database if the hashes listed above and sql libraries are set up correctly.

check_entry()
Verifies entered barcodes, gets details of the item and displays them in grid. For each entity type and sql query, get_XX_details must be written and placed in a sql library cancel_entity_dbcode.sql_lib. If hashes listed above are set up correctly this allows:

    $sql = "cancel_entity_".$db{$type}.".sql_lib::get_".$type."_details";
    $res = $STATEMENTS{$db{$type}}->get($sql, [$id]);

Details of each are drawn in the grid. For defined form of results from get_XX_details see below. Pressing update button attempts to cancel all items currently listed in the grid...

update_db()
Loops over all entered items, tests to see if they can be cancelled then cancels them. As with check_entry() the loop is generic, requiring sql and library names to be in specified form. Calls ok_to_cancel() for each item to check if cancelling the item is allowed. If returned 'Yes' then updates database and calls cleanup() to carry out any DB tasks necessary as a result of cancelling the item. If returns 'Yes' then commits transaction. For each item:

• calls ok_to_cancel() - This subroutine contains logic defining if an item can be cancelled, typically means checking the item's status or looking for any active child items. If child items exist details can be displayed in a popup window. For example:

      grid_results(\$dna);
      $update = child_details($name, 'ok', $DETAILS, %dw_pos);
  

  This brings up a window listing all results in $dna. child_window() has the option of informing that child items exist prohiting cancelling ('ok') or warning the user and offerring a choice ('yesno')
• update database - Done by running update_XX_status and insert_XX_status where XX is a barcode prefix.
• calls cleanup() - performs any additional database changes required as a result of cancel.
• if update successful commit transaction.

The main task is to add if blocks to two subroutines and write some sql. The core code should not need to be changed, minimising risk of the program breaking.

1. Write sql and place in cancel_entity_dbcode.sql_lib. Need get_XX_details, update_XX_status, insert_XX_status where XX is the barcode prefix of the entity type and dbcode is instance column of barcode_prefix. See below for examples of format.
2. Add a new option for the entity type in ok_to_cancel() - define here the logic for being allowed to cancel an item, e.g. does it have child entities, the right status, etc. Any required sql queries should be called from here. Return 'Yes' if ok to cancel, 'No' otherwise.
3. Any additional updates required in the database when an entity is cancelled should be placed in cleanup(). e.g. changing the status of a parent entity. Return 'Yes' if operations were successful. If nothing to do then add nothing to this subroutine.
4. Add the barcode prefix to allowed @cancel_types for the relevant $caller program (add new $caller option if necessary)
5. Ensure that barcode prefix is related to a statusdict table name in SNP_util::%STATUSDICTS and that a select statement for that table exists in SNP_util.sql_lib.

1. Add the database name and and identifying code to %alias [e.g. NEW/new_database]
2. Create new XXX_STATEMENTS structure and load relevent sql_libs to it [e.g. $NEW_STATEMENTS]
3. Put this statements structure in %STATEMENTS with db code as key [e.g. NEW/$NEW_STATEMENTS]
4. Create a file cancel_entity_NEW.sql_lib to hold sql queries (get_XX_details, insert_XX_status, update_XX_status for each entity type in that DB)
5. In update_db() get a session for the db and store in %session [NEW/new_session]

>get_BB_details<
select BECKMAN_BOX.ID_BECKMANBOX, BECKMAN_BOX.TEAM, BBSTATUSDICT.DESCRIPTION, BB_STATUS.STATUSDATE
from BECKMAN_BOX, BB_STATUS, BBSTATUSDICT
where BECKMAN_BOX.ID_BECKMANBOX = ?
and   BECKMAN_BOX.ID_BECKMANBOX = BB_STATUS.ID_BECKMANBOX
and   BB_STATUS.ISCURRENT = 1
and   BB_STATUS.STATUS = BBSTATUSDICT.ID_DICT



>update_BB_status<
update BB_STATUS
set ISCURRENT = ?
where ID_BECKMANBOX = ?
and   ISCURRENT = 1


>insert_BB_status<
insert into BB_STATUS
(ID_BECKMANBOX, STATUS, ISCURRENT, OPERATOR, SESSIONID, PROGRAM, STATUSDATE, REMARK)
values (?,?,?,?,?,?, sysdate, ?)