#! /bin/bash
sqlite3 /var/www/html/mrp/data/xtuple.db >/tmp/unmatchedQuantites.csv <<EOF
DROP TABLE if exists xtupleTotals;
CREATE TABLE xtupleTotals (partno VARCHAR, description VARCHAR, total REAL);
INSERT into xtupleTotals select item_number as partno, item_descrip1 as description, itemsite_qtyonhand as total from item, itemsite where item_id=itemsite_item_id;
create index if not exists xtidx on xtupleTotals (partno);
DROP TABLE if exists locationTotals;
CREATE TABLE locationTotals (partno VARCHAR, total REAL);
INSERT into locationTotals select partno, sum(quantity) as total from locations group by partno order by partno;
create index if not exists ltidx on locationTotals (partno);
.mode csv
select locationTotals.partno as partno, description, locationTotals.total as binQuantity, xtupleTotals.total xtupleQuantity from locationTotals, xtupleTotals where locationTotals.partno = xtupleTotals.partno and locationTotals.total != xtupleTotals.total order by partno;
EOF