#! /bin/bash
cd /var/www/html/mrp
PGPASSFILE=".pgpassSteve"
export PGPASSFILE
TMPFILE=`mktemp`
DUMPFILE="${TMPFILE}"
SQLFILE="${TMPFILE}.sql"
pg_dump -U admin -h localhost -p 5432 --data-only --column-inserts --table=item --table=itemsrc --table=vendinfo --table=bomitem --table=itemsite --file=${TMPFILE} pelican
echo "BEGIN TRANSACTION;" > $SQLFILE
sed -e 's/^SET/-- SET/' -e 's/ false,/ 0,/g' -e 's/ false)/ 0)/' -e 's/ true,/ 1,/g' -e 's/ true)/ 1)/' < $TMPFILE >> $SQLFILE
echo "COMMIT TRANSACTION;" >> $SQLFILE
sqlite3 data/xtuple.db <<EOF
drop table if exists bomitem;
CREATE TABLE bomitem (
    bomitem_id integer NOT NULL,
    bomitem_parent_item_id integer NOT NULL,
    bomitem_seqnumber integer,
    bomitem_item_id integer NOT NULL,
    bomitem_qtyper real NOT NULL,
    bomitem_scrap real NOT NULL,
    bomitem_status character(1),
    bomitem_effective date NOT NULL,
    bomitem_expires date NOT NULL,
    bomitem_createwo boolean NOT NULL,
    bomitem_issuemethod character(1) NOT NULL,
    bomitem_schedatwooper boolean NOT NULL,
    bomitem_ecn text,
    bomitem_moddate date,
    bomitem_subtype character(1) NOT NULL,
    bomitem_uom_id integer NOT NULL,
    bomitem_rev_id integer DEFAULT (-1),
    bomitem_booitem_seq_id integer DEFAULT (-1),
    bomitem_char_id integer,
    bomitem_value text,
    bomitem_notes text,
    bomitem_ref text
);
drop table if exists itemsite;
CREATE TABLE itemsite (
    itemsite_id integer NOT NULL,
    itemsite_item_id integer NOT NULL,
    itemsite_warehous_id integer,
    itemsite_qtyonhand REAL NOT NULL,
    itemsite_reorderlevel REAL NOT NULL,
    itemsite_ordertoqty REAL NOT NULL,
    itemsite_cyclecountfreq integer NOT NULL,
    itemsite_datelastcount date,
    itemsite_datelastused date,
    itemsite_loccntrl boolean NOT NULL,
    itemsite_safetystock REAL NOT NULL,
    itemsite_minordqty REAL NOT NULL,
    itemsite_multordqty REAL NOT NULL,
    itemsite_leadtime integer NOT NULL,
    itemsite_abcclass character(1),
    itemsite_issuemethod character(1),
    itemsite_controlmethod character(1),
    itemsite_active boolean NOT NULL,
    itemsite_plancode_id integer NOT NULL,
    itemsite_costcat_id integer NOT NULL,
    itemsite_eventfence integer NOT NULL,
    itemsite_sold boolean NOT NULL,
    itemsite_stocked boolean NOT NULL,
    itemsite_freeze boolean NOT NULL,
    itemsite_location_id integer NOT NULL,
    itemsite_useparams boolean NOT NULL,
    itemsite_useparamsmanual boolean NOT NULL,
    itemsite_soldranking integer DEFAULT 1,
    itemsite_createpr boolean,
    itemsite_location text,
    itemsite_location_comments text,
    itemsite_notes text,
    itemsite_perishable boolean NOT NULL,
    itemsite_nnqoh REAL DEFAULT 0 NOT NULL,
    itemsite_autoabcclass boolean NOT NULL,
    itemsite_ordergroup integer DEFAULT 0 NOT NULL,
    itemsite_disallowblankwip boolean NOT NULL,
    itemsite_maxordqty REAL DEFAULT 0.0 NOT NULL,
    itemsite_mps_timefence integer DEFAULT 0 NOT NULL,
    itemsite_createwo boolean NOT NULL,
    itemsite_warrpurc boolean NOT NULL,
    itemsite_autoreg boolean,
    itemsite_costmethod character(1) NOT NULL,
    itemsite_value REAL NOT NULL,
    itemsite_ordergroup_first boolean NOT NULL,
    itemsite_supply_itemsite_id integer,
    itemsite_planning_type character(1) NOT NULL,
    itemsite_wosupply boolean NOT NULL,
    itemsite_posupply boolean NOT NULL
);
drop table if exists itemsrc;
CREATE TABLE itemsrc (
    itemsrc_id integer NOT NULL,
    itemsrc_item_id integer NOT NULL,
    itemsrc_vend_id integer NOT NULL,
    itemsrc_vend_item_number text,
    itemsrc_vend_item_descrip text,
    itemsrc_comments text,
    itemsrc_vend_uom text NOT NULL,
    itemsrc_invvendoruomratio REAL NOT NULL,
    itemsrc_minordqty REAL NOT NULL,
    itemsrc_multordqty REAL NOT NULL,
    itemsrc_leadtime integer NOT NULL,
    itemsrc_ranking integer NOT NULL,
    itemsrc_active boolean NOT NULL,
    itemsrc_manuf_name text NOT NULL,
    itemsrc_manuf_item_number text NOT NULL,
    itemsrc_manuf_item_descrip text
);
drop table if exists vendinfo;
CREATE TABLE vendinfo (
    vend_id integer NOT NULL,
    vend_name text,
    vend_lastpurchdate date,
    vend_active boolean,
    vend_po boolean,
    vend_comments text,
    vend_pocomments text,
    vend_number text,
    vend_1099 boolean,
    vend_exported boolean,
    vend_fobsource character(1),
    vend_fob text,
    vend_terms_id integer,
    vend_shipvia text,
    vend_vendtype_id integer,
    vend_qualified boolean,
    vend_ediemail text,
    vend_ediemailbody text,
    vend_edisubject text,
    vend_edifilename text,
    vend_accntnum text,
    vend_emailpodelivery boolean,
    vend_restrictpurch boolean,
    vend_edicc text,
    vend_curr_id integer,
    vend_cntct1_id integer,
    vend_cntct2_id integer,
    vend_addr_id integer,
    vend_match boolean NOT NULL,
    vend_taxauth_id integer,
    vend_ach_enabled boolean NOT NULL,
    vend_ach_accnttype text,
    vend_ach_use_vendinfo boolean NOT NULL,
    vend_ach_indiv_number text NOT NULL,
    vend_ach_indiv_name text NOT NULL,
    vend_ediemailhtml boolean NOT NULL,
    vend_ach_routingnumber blob NOT NULL,
    vend_ach_accntnumber blob NOT NULL,
    vend_taxzone_id integer
);
drop table if exists item;
CREATE TABLE item (
    item_id integer NOT NULL,
    item_number text NOT NULL,
    item_descrip1 text NOT NULL,
    item_descrip2 text NOT NULL,
    item_classcode_id integer NOT NULL,
    item_picklist boolean NOT NULL,
    item_comments text,
    item_sold boolean NOT NULL,
    item_fractional boolean NOT NULL,
    item_active boolean NOT NULL,
    item_type character(1) NOT NULL,
    item_prodweight REAL NOT NULL,
    item_packweight REAL NOT NULL,
    item_prodcat_id integer NOT NULL,
    item_exclusive boolean NOT NULL,
    item_listprice REAL NOT NULL,
    item_config boolean,
    item_extdescrip text,
    item_upccode text,
    item_maxcost REAL NOT NULL,
    item_inv_uom_id integer NOT NULL,
    item_price_uom_id integer NOT NULL,
    item_warrdays integer DEFAULT 0,
    item_freightclass_id integer,
    item_tax_recoverable boolean NOT NULL
);
EOF
sqlite3 data/xtuple.db < $SQLFILE
sqlite3 data/xtuple.db <<EOF
update item set item_type='M', item_classcode_id=35 where item_number='31-0015';
delete from item where item_id=10493;
INSERT INTO item(item_id,item_number,item_descrip1,item_descrip2,item_classcode_id,item_picklist,item_comments,item_sold,item_fractional,item_active,item_type,item_prodweight,item_packweight,item_prodcat_id,item_exclusive,item_listprice,item_config,item_extdescrip,item_upccode,item_maxcost,item_inv_uom_id,item_price_uom_id,item_warrdays,item_freightclass_id,item_tax_recoverable) VALUES(10493,'31-0015','Vanity Panel, 6" x 5.25", 1/8" WHITE Acrylic Opaque, Protective Masking Removed','',33,1,'',1,0,1,'P',0.0,0.0,28,0,9.9900000000000002131,0,'','VANITY1',0.0,4,4,0,NULL,0);
delete from bomitem where bomitem_id=20000;
INSERT INTO bomitem(bomitem_id,bomitem_parent_item_id,bomitem_seqnumber,bomitem_item_id,bomitem_qtyper,bomitem_scrap,bomitem_status,bomitem_effective,bomitem_expires,bomitem_createwo,bomitem_issuemethod,bomitem_schedatwooper,bomitem_ecn,bomitem_moddate,bomitem_subtype,bomitem_uom_id,bomitem_rev_id,bomitem_booitem_seq_id,bomitem_char_id,bomitem_value,bomitem_notes,bomitem_ref) VALUES(20000,493,1,10493,1.0,0.0,NULL,'1970-01-01','2100-01-01',0,'M',0,'','2020-02-21','I',4,-1,-1,NULL,NULL,'','');
EOF