Changes for automatic shipment creation

[stufisher]

To aid our automatic shipping creation process i would like to propose the following changes:

CourierTermsAccepted

shippingid int (foreign key to shipping)

We should have had this in the beginning but at the time users accepted terms before the shipment was created. Terms are now accepted afterwards allowing us to use the shippingid

Shipping

deliveryAgent_flightcode_timestamp timestamp (date flight code created, if automatic)
deliveryAgent_label text (base64 encoded pdf of airway label)

readybytime datetime (time shipment will be ready)
closetime datetime (time after which shipment cannot be picked up)
physicallocation varchar(50) (where shipment can be picked up from: i.e. Stores)

deliveryAgent_pickupconfirmation_timestamp (date picked confirmed)
deliveryAgent_pickupconfirmation varchar(10) (confirmation number of requested pickup)
deliveryAgent_readybytime datetime (confirmed readyby time)
deliveryAgent_callintime datetime (confirmed courier call in time)

Dewar

weight number (dewar weight in kg)

Self explanatory

Laboratory

postcode varchar(15)

I would like to register postcode separately from the "Address" field so it can be automatically sent to the courier

@KarlLevik can weigh in with SQL alter statements

[KarlLevik]

The ALTER statements would look something like this:

ALTER TABLE CourierTermsAccepted
  DROP FOREIGN KEY CourierTermsAccepted_ibfk_3,
  DROP shippingId;

ALTER TABLE Shipping
  DROP deliveryAgent_flightCodeTimestamp,
  DROP deliveryAgent_label,
  DROP readyByTime,
  DROP closeTime,
  DROP physicalLocation,
  DROP deliveryAgent_pickupConfirmationTimestamp,
  DROP deliveryAgent_pickupConfirmation,
  DROP deliveryAgent_readyByTime,
  DROP deliveryAgent_callinTime,
  DROP deliveryAgent_productcode,
  DROP FOREIGN KEY Shipping_ibfk_4,
  DROP deliveryAgent_flightCodePersonId;

ALTER TABLE Dewar
  DROP weight, 
  DROP deliveryAgent_barcode;

ALTER TABLE Laboratory
  DROP postcode;

ALTER TABLE CourierTermsAccepted
  ADD shippingId int(11) unsigned,
  ADD CONSTRAINT CourierTermsAccepted_ibfk_3
      FOREIGN KEY (shippingId) REFERENCES Shipping(shippingId) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE Shipping 
  ADD deliveryAgent_flightCodeTimestamp timestamp NULL DEFAULT NULL COMMENT 'Date flight code created, if automatic',
  ADD deliveryAgent_label text COMMENT 'Base64 encoded pdf of airway label',
  ADD readyByTime time COMMENT 'Time shipment will be ready',
  ADD closeTime time COMMENT 'Time after which shipment cannot be picked up',
  ADD physicalLocation varchar(50) COMMENT 'Where shipment can be picked up from: i.e. Stores',
  ADD deliveryAgent_pickupConfirmationTimestamp timestamp NULL DEFAULT NULL COMMENT 'Date picked confirmed',
  ADD deliveryAgent_pickupConfirmation varchar(10) COMMENT 'Confirmation number of requested pickup',
  ADD deliveryAgent_readyByTime time COMMENT 'Confirmed ready-by time',
  ADD deliveryAgent_callinTime time COMMENT 'Confirmed courier call-in time',
  ADD deliveryAgent_productCode varchar(10) COMMENT 'A code that identifies which shipment service was used',
  ADD deliveryAgent_flightCodePersonId int(10) unsigned COMMENT 'The person who created the AWB (for auditing)',
  ADD CONSTRAINT Shipping_ibfk_4
      FOREIGN KEY (deliveryAgent_flightCodePersonId) REFERENCES Person(personId);

ALTER TABLE Dewar
  ADD weight float COMMENT 'Dewar weight in kg',
  ADD deliveryAgent_barcode varchar(30) COMMENT 'Courier piece barcode (not the airway bill)';

ALTER TABLE Laboratory
  ADD postcode varchar(15);

[KarlLevik]

I've just edited the above comment and removed the second underscore in two of the proposed columns for the Shipping table. I think that should make it more compliant with the convention we seem to be following.

[KarlLevik]

Have updated the script again to allow for NULL + make NULL the default value in the proposed Shipping timestamp columns.

[KarlLevik]

I've again updated the script to add the columns Shipping.deliveryAgent_productcode and Dewar.deliveryAgent_barcode. These are needed to store information needed to produce a report required by DHL, though can be used more generally, of course.

[KarlLevik]

I've updated the script with an additional column and its foreign key definition: deliveryAgent_flightCodePersonId. As the column comment says, this is to record who actually created the airway bill.