alphagov/trade-tariff-backend — pull request #61 by sauliusg (2013-01-29 12:02:35+00:00)
Title: National quantities

This adds support for national quantities. Adds two new chief tables (chief_comm and chief_tbl9). COMM table is a join table between goods_nomenclatures and TBL9. TBL9 contains descriptions for national quantity units.

National quantities apply to national excise measures. Commodities that have national quantities (for checking after deploy):

```sql
mysql> SELECT DISTINCT(goods_nomenclatures.goods_nomenclature_item_id) FROM chief_comm INNER JOIN goods_nomenclatures ON chief_comm.cmdty_code = goods_nomenclatures.go
ods_nomenclature_item_id INNER JOIN measures ON goods_nomenclatures.goods_nomenclature_sid = measures.goods_nomenclature_sid WHERE uoq_code_cdu2 IS NOT NULL and uoq_co
de_cdu3 IS NOT NULL AND le_tsmp IS NULL AND goods_nomenclatures.validity_end_date IS NULL AND goods_nomenclatures.validity_start_date <= NOW() AND measures.measure_typ
e IN ('DAA','DAB','DAC','DAE','DAI','DBA','DBB','DBC','DBE','DBI','DCA','DCC','DCE','DCH','DDJ','DDA','DDB','DDC','DDD','DDE','DDF','DDG','DEA','DFA','DFB','DFC','DGC'
,'DHA','DHC','DHE','EAA','EAE','EBJ','EBA','EBB','EBE','EDA','EDB','EDE','EDJ','EEA','EEF','EFJ','EFA','EGA','EGB','EGJ','EHI','EIJ','EIA','EIB','EIC','EID','EIE','FAA
','FAE','FAI','FBC','FBG','LAA','LAE','LBJ','LBA','LBB','LBE','LDA','LEA','LEF','LFA','LGJ');
+----------------------------+
| goods_nomenclature_item_id |
+----------------------------+
| 2207200090 |
| 2205909000 |
| 2205109000 |
| 2207100090 |
| 2207100011 |
| 2207200019 |
| 2207200011 |
| 2207100019 |
| 2204299790 |
| 2204299651 |
| 2204219551 |
| 2204219751 |
| 2204219895 |
| 2204299495 |
| 2204299451 |
| 2204109300 |
| 2204219851 |
| 2206005900 |
| 2204210800 |
| 2204219695 |
| 2204219651 |
| 2206003900 |
| 2204299695 |
| 2204210900 |
| 2204109800 |
| 2204219495 |
| 2204299895 |
| 2204219451 |
| 2204299390 |
| 2204210700 |
| 2204291000 |
| 2204210600 |
| 2204109600 |
| 2204299851 |
| 2204109400 |
| 2206008900 |
| 2204299590 |
| 2204219351 |
| 2710125110 |
| 2710124990 |
| 2710124190 |
| 2710124510 |
| 2710124110 |
| 2710124590 |
| 2710125910 |
| 2710125190 |
| 2710125990 |
| 2710124910 |
| 2208909919 |
| 2208909911 |
| 2106902000 |
| 2208909110 |
| 2205101000 |
| 2205901000 |
| 2204301000 |
| 2206001000 |
| 2710129010 |
| 2710121510 |
| 2710122591 |
| 2710121110 |
| 2710127010 |
| 2710122110 |
| 3814009070 |
| 2710123110 |
+----------------------------+
64 rows in set (0.01 sec)
```

There may be fewer or more of these, I'm not checking validity dates in this query, but it is a good reference.

### There are two problems however:

* The format of national quantity unit description ir horrible, e.g.:

```ruby
[2] pry(main)> Chief::Tbl9.where(tbl_type: 'UNOQ').map(&:tbl_txt).join("|")
=> "QUINTAL|1000LB|CENTAL|OZ TROY|HECTOGRAM|POUND GROSS|50KG|SHORT STANDARD|LONG HUNDRED|100 SQUARE METRES|100 FEET|DAKAMETRE|36 GALLONS IE BULK BARREL|MILLILITRE IE CU CENTIMETRE|DEKALITRE|US GALLON|PILED CUBIC FATHOM|PILED CUBIC METRE|CUBIC DECIMETRE|MILLIMETRE|CENTIMETRE|DECIMETRE|METRE|INCH|FOOT|YARD|SQUARE DECIMETRE|SQUARE METRE|SQUARE INCH|SQUARE FOOT|LITRE|SQUARE YARD|CUBIC METRE|HECTOLITRE|CENTILITRE|CUBIC INCH|CUBIC YARD|CUBIC FOOT|PINT|GALLON|STANDARD|CARAT|GRAMME|KILOGRAMME|KG GROSS|TONNE|KILOGRAMME OF TOTAL ALCOHOL|GROSS TONNAGE|DISPLACEMENT TONNAGE|POUND|CCT CARRYING CAPACITY IN TONNE|CWT|OUNCE|100 KG|LB TROY|KILOWATT|GRAMME FISSILE ISOTOPES|DOZEN|GROSS|NUMBER OF PAIRS|NUMBER|NUMBER OF ROLLS|DOZEN PAIR|100 PACKS|HUNDRED|THOUSAND|1000 CUBIC METRES|KILOGRAMME 90 PERCENT DRY|KILOGRAMME OF K20|KILOGRAMME OF KOH|KILOGRAMME OF NAOH|KILOGRAMME OF U|KILOGRAMME OF P2O5|NUMBER OF SETS|ALCOHOL BY VOLUME|DEGREES PERCENTAGE VOLUME|NUMBER OF KITS|1000 TABLETS|KILOGRAMME OF WO3|NUMBER OF FLASKS|100 INTL UNITS|KILOGRAMME OF H2O2|GRAM FINE GOLD CONTENT|LITRE OF ALCOHOL|90% TONNE DRY|CURIE|PROOF GALLON|MILLION INTL UNITS POTENCY|HECTOMETRE|TON|% SUCROSE CONTENT/ 100 KG NET|% LACTIC DRY MATTER/ 100 KG PRODUCT|100 KG DRY PRODUCT|% ADDED SUGAR/ 100 KG|PER% OF POTENTIAL ALCOHOLIC STRENGTH PER HECTOLITRE|% LACTIC MATTER/ 100 KG PRODUCT|% ACTUAL ALCOHOLIC STRENGTH/ HECTOLITRE|STANDARD LITRE( OF HYDROCARBON OIL)|KILOGRAM DRAINED NET WEIGHT|HECTOKILOGRAM DRAINED NET WEIGHT|KILOGRAMME OF NITROGEN|1000 Litres|KILOGRAM OF METHYL AMINES|HECTOKILOGRAM NET DRY MATTER( 100 kg/ net mas)|KILOGRAM CHOLINE CHLORIDE|KILOWATT HOUR|1000 KILOWATT HOUR|KILOTONS|MICRO LITRES OR CUBIC MM|MILLIGRAMS|TONNE OF FUEL|BIO DIESEL|KILOMETERS|BIOETHANOL CONTENT|BIOETHANOL CONTENT (WEIGHT IN TONNES)"
```

Uppercase, weird spacing. I don't think we should be altering CHIEF data, but we could add 'normalizer' in the frontend.

* The import of the new table data. Tbl9 contains 37688 initial entries and 1985 entries from update files. Comm contains 127506 initial entries and 7877 entries from update files. We are including *ALL* entries from these tables since we may possibly need something else from here for further development. I don't think such amounts of data really qualifies for migration. Shall I create a new DB snapshot? Other ideas?