gistfile1.txt

BACKGROUND:

We're changing the offer form a bit to have a new set of fields for each line item in the offer.  Therefore we need to change the set of columns in the database table that stores these line items.  Old data will need to be modified to fit the new format.  This document outlines an algorithm for modifying the old data into the new format.


EXISTING COLUMNS

* description
* quantity
* part_price (unit price per part)
* labor_price (a total)
* part (aka "part number")
* note


NEW COLUMNS

* description (contains part number if they like)
* line_item_type (one of ['Part', 'Rate', 'Flat Rate'])
* quantity (locked at 1 for 'Flat Rate')
* unit_price
* note


DATA MIGRATION PLAN

For each existing offer_line_item, we'll run it through the following set of changes:

* old "description" and "part" are combined into new "description"
* old "note" becomes new "note" field (unchanged)

IF(part_price IS present, but labor price IS NOT present)
  * set new "line_item_type" to 'Part'
  * quantity carries over unchanged
  * old "part_price" becomes new "unit_price"

ELSE IF(labor_price IS present, but part_price IS NOT present)
  * set new "line_item_type" to 'Rate'
  * quantity carries over unchanged
  * old "labor_price" is divided by quantity, then becomes "unit_price"
      for example: 
        this old record:
          part_price: nil
          labor_price: 20
          quantity: 4
          (totaling 20)
        would become this new record:
          line_item_type: 'Rate'
          quantity: 4
          labor_price: 5
          (totaling 20)

ELSE IF(labor_price IS present, and part_price IS present)
  * old record becomes two new records as follows:
    ** new record 1
      * line_item_type: 'Part'
      * quantity: old_quantity
      * unit_price: old_part_price
    ** new record 2 
      * line_item_type: 'Rate'
      * quantity: old_quantity
      * unit_price: old_labor_price / quantity
  
  * for example:
      this old record:
        part_price: 10
        labor_price: 25
        quantity: 5
        (totaling (10*5) + 25 = 75)
      would become these two new records:
        new record 1:
          * line_item_type: 'Part'
          * quantity: 5
          * unit_price: 10
          (totaling 50)
        new record 2:
          * line_item_type: 'Rate'
          * quantity: 5
          * unit_price: 5
          (totaling 25)




Let me know if the above format for describing the changes doesn't make sense.  I can implement this in a data migration task that will run against the existing data, but I need approval on the business logic.