test.sql

DROP DATABASE tFood;
CREATE DATABASE IF NOT EXISTS tFood;
USE tFood;

CREATE TABLE IF NOT EXISTS weight(
  weight varchar(5),
  gal int DEFAULT 0,
  floz int DEFAULT 0,
  quarts int DEFAULT 0,
  cups int DEFAULT 0,
  lbs int DEFAULT 0,
  oz int DEFAULT 0,
  g int DEFAULT 0,
  PRIMARY KEY(weight)
);

CREATE TABLE IF NOT EXISTS tRM(
  snid int(7) NOT NULL AUTO_INCREMENT,
  category ENUM("Food", "Liquid", "Packaging") NOT NULL,
  -- categoryID int,
  name varchar(30) NOT NULL,
  measurement varchar(5),
  weight decimal(5, 2),
  pcsPerCs decimal(7,2) DEFAULT 0,
  PRIMARY KEY(snid),
  CONSTRAINT FK_weight FOREIGN KEY(measurement) REFERENCES weight(weight)
);

CREATE TABLE IF NOT EXISTS tIn(
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  snid int(7) NOT NULL,
  debit decimal(7,2) NOT NULL DEFAULT 0,
  pcsIn int(4) NOT NULL DEFAULT 0,
  CONSTRAINT FK_tIn FOREIGN KEY(snid) REFERENCES tRM(snid)
);

CREATE TABLE IF NOT EXISTS tOut(
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  snid int(7) NOT NULL,
  units int(3) DEFAULT 0,
  waste int(3) DEFAULT 0,
  rnd int(3) DEFAULT 0,
  CONSTRAINT FK_tOut FOREIGN KEY(snid) REFERENCES tRM(snid)
);

INSERT INTO weight(weight, lbs, oz, g) VALUES("lbs", 1, 16, 454);
INSERT INTO weight(weight, lbs, oz, g) VALUES("oz", 0, 1, 28.35);
INSERT INTO weight(weight, lbs, oz, g) VALUES("g", 0, 0, 1);
INSERT INTO weight(weight, gal, floz, quarts, cups, oz, g) VALUES("gal", 1, 128, 4, 16, 8, 3632);

INSERT INTO tRM(category, name, measurement, weight, pcsPerCs) VALUES("Food", "Allspice", "lbs", 5, 1);
INSERT INTO tRM(category, name, measurement, weight, pcsPerCs) VALUES("Food", "Thyme", "oz", 20, 1);
INSERT INTO tRM(category, name, measurement, weight, pcsPerCs) VALUES("Liquid", "Cooking Wine", "gal", 1, 3);
INSERT INTO tRM(category, name, measurement, weight, pcsPerCs) VALUES("Packaging", "Vinegar Bottle", "oz", 16, 12);

insert into tIn(snid, debit, pcsIn) values(1, 55, 2);
insert into tIn(snid, debit, pcsIn) values(1, 26.75, 1);
insert into tIn(snid, debit, pcsIn) values(2, 311.88, 12);
insert into tIn(snid, debit, pcsIn) values(2, 623.76, 24);
insert into tIn(snid, debit, pcsIn) values(3, 32.99, 3);
insert into tIn(snid, debit, pcsIn) values(4, 95.88, 12);

insert into tOut(snid, units, waste, rnd) values(1, 2000, 200, 70);
insert into tOut(snid, units, waste, rnd) values(2, 1, 0, 0);
insert into tOut(snid, units, waste, rnd) values(3, 1, 0, 0);
insert into tOut(snid, units, waste, rnd) values(4, 1, 0, 0);

create view qvt1 as (
  select
  tRM.snid,
  SUM(tIn.debit) as debit,
  SUM(tIn.pcsIn) as pcsIn,
  ROUND(SUM(tIn.debit) / SUM(tIn.pcsIn), 2) as costPerPc,
  tRM.weight * weight.g * SUM(tIn.pcsIn) as totalG,
  tRM.weight * weight.g as gramsPerPc,
  ROUND(SUM(tIn.debit) / (tRM.weight * weight.g * SUM(tIn.pcsIn)), 3) as costPerG
  from tRM, tIn, weight
  where tRM.snid = tIn.snid and tRM.measurement = weight.weight
  group by tRM.snid
);

create view qvt2 as (
  select
    tRM.snid,
    qvt1.costPerG * SUM(tOut.units + tOut.waste + tOut.rnd) as credit,
    SUM(tOut.units + tOut.waste + tOut.rnd) as usedG
  from tRM, tOut, qvt1
  where tRM.snid = tOut.snid and tRM.snid = qvt1.snid
  group by tRM.snid
);

create view vt as (
  select
  tRM.snid,
  tRM.category,
  tRM.name,
  tRM.measurement,
  tRM.weight,
  tRM.pcsPerCs,
  qvt1.debit,
  qvt2.credit,
  qvt1.pcsIn,
  ROUND(SUM(qvt2.usedG / qvt1.gramsPerPc), 3) as pcsOut,
  qvt1.costPerPc,
  qvt1.gramsPerPc,
  qvt1.totalG,
  qvt2.usedG,
  qvt1.totalG - qvt2.usedG as gramsOnHand,
  qvt1.costPerG
  from tRM, qvt1, qvt2, weight
  where tRM.snid = qvt1.snid and tRM.snid = qvt2.snid and tRM.measurement = weight.weight
  group by tRM.snid
);

-- Raw Materials #131001
CREATE TABLE IF NOT EXISTS rawMaterials(
  snid int(7) NOT NULL AUTO_INCREMENT,
  category ENUM("Canned Food", "Coffee", "Dry Goods", "Dry Herbs", "Fridge:Cheese", "Fridge:Milks", "Fridge:MISC", "Frozen Foods", "Grains & Flours", "Juices", "Labor & Shipping", "Nuts & Seeds", "Oil & Vinegar", "Packaging", "Produce", "Roots", "Sustainable", "Sweeteners") NOT NULL,
  -- categoryID int,
  name varchar(30) NOT NULL,
  lbs decimal(7,2) DEFAULT 0,
  oz decimal(7,2) DEFAULT 0,
  g decimal(7,2) DEFAULT 0,
  inchesPerUnit int(2) DEFAULT 0, -- ozPerUnit
  inchesPerPc int(2) DEFAULT 0, -- lbsPerPc
  pcsPerCs decimal(7,2) DEFAULT 0,
  link varchar(255),
  PRIMARY KEY(snid)
  -- CONSTRAINT FK_Category FOREIGN KEY (categoryID) REFERENCES categories(snid)
);

CREATE TABLE IF NOT EXISTS rmIn(
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  snid int(7) NOT NULL,
  debit decimal(7,2) NOT NULL DEFAULT 0,
  pcsIn int(4) NOT NULL DEFAULT 0,
  invoiceID varchar(50) NOT NULL DEFAULT 0,
  purchaseDate datetime DEFAULT '1970-02-17',
  CONSTRAINT FK_rmIn FOREIGN KEY(snid) REFERENCES rawMaterials(snid)
);

CREATE TABLE IF NOT EXISTS rmOut(
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  snid int(7) NOT NULL,
  units int(3) DEFAULT 0,
  waste int(3) DEFAULT 0,
  rnd int(3) DEFAULT 0,
  CONSTRAINT FK_rmOut FOREIGN KEY(snid) REFERENCES rawMaterials(snid)
);

INSERT INTO rawMaterials(category, name, lbs, oz, g, inchesPerUnit, inchesPerPc,
 pcsPerCs) VALUES('Dry Goods', 'Allspice', 1, 0, 0, 0, 0, 1);
INSERT INTO rmIn(snid, debit, pcsIn, invoiceID, purchaseDate) VALUES(1, 0, 0, "xxxx", "1970-02-17");
INSERT INTO rmIn(snid, debit, pcsIn, invoiceID, purchaseDate) VALUES(1, 26.75, 1, "xxxx", "2026-1-03");
INSERT INTO rmOut(snid, units, waste, rnd) VALUES(1, 0, 0, 0);
INSERT INTO rmOut(snid, units, waste, rnd) VALUES(1, 1, 1, 1);

select 
  rawMaterials.lbs * 16 AS ozPerPc, 
  rawMaterials.lbs * 453.6 as gPerPc, 
  ROUND(SUM(rawMaterials.lbs * 453.6) / 2, 2) as portionPerPc
INTO @ozPerPc, @gPerPc, @portionPerPc
from rawMaterials;

CREATE VIEW IF NOT EXISTS qrm1 AS
SELECT
  rawMaterials.snid AS snid,
  SUM(rmIn.debit) AS debit,
  SUM(rmIn.pcsIn) AS pcsIn,
  ROUND(SUM(rmIn.debit) / SUM(rmIn.pcsIn), 2) AS costPerPc,
  ROUND(rawMaterials.inchesPerPc / rawMaterials.inchesPerUnit, 0) AS unitsPerPc,
  ROUND((rawMaterials.inchesPerPc / rawMaterials.inchesPerUnit) * SUM(rmIn.pcsIn), 0) AS unitsIn,
  ROUND((rawMaterials.inchesPerPc / rawMaterials.inchesPerUnit) * rawMaterials.pcsPerCs, 0) AS unitsPerCs,
  ROUND(SUM(rmIn.debit) / ((rawMaterials.inchesPerPc / rawMaterials.inchesPerUnit) * SUM(rmIn.pcsIn)), 2) AS costPerUnit
FROM rawMaterials, rmIn
WHERE rawMaterials.snid = rmIn.snid
GROUP BY rawMaterials.snid
;

CREATE VIEW IF NOT EXISTS qrm2 AS
SELECT
  rawMaterials.snid,
  SUM(rmOut.units) AS units,
  SUM(rmOut.waste) AS waste,
  SUM(rmOut.rnd) AS rnd,
  SUM(rmOut.units) + SUM(rmOut.waste) + SUM(rmOut.rnd) AS unitsOut
FROM rawMaterials, rmOut
WHERE rawMaterials.snid = rmOut.snid
GROUP BY rawMaterials.snid
;

CREATE VIEW IF NOT EXISTS vRawMaterials AS
SELECT
  CONCAT(rawMaterials.snid, " ", rawMaterials.category, " ", rawMaterials.name) AS description,
  qrm1.debit as debit,
  qrm1.costPerUnit * qrm2.unitsOut AS credit,
  qrm1.costPerUnit * qrm2.units AS uDR,
  qrm1.costPerUnit * qrm2.waste AS wDR,
  qrm1.costPerUnit * qrm2.rnd AS rndDR,
  qrm1.costPerPc AS costPerPc,
  qrm1.costPerUnit AS costPerUnit,
  rawMaterials.inchesPerUnit AS inchesPerUnit,
  rawMaterials.inchesPerPc AS inchesPerPc,
  rawMaterials.pcsPerCs AS pcsPerCs,
  qrm1.unitsPerPc AS unitsPerPc,
  qrm1.unitsPerCs AS unitsPerCs,
  qrm1.pcsIn AS pcsIn,
  qrm1.unitsIn AS unitsIn,
  qrm2.unitsOut AS unitsOut,
  qrm1.unitsIn - qrm2.unitsOut AS unitsOH,
  qrm2.units AS units,
  qrm2.waste AS waste,
  qrm2.rnd AS rnd
FROM rawMaterials, qrm1, qrm2
WHERE rawMaterials.snid = qrm1.snid AND qrm1.snid = qrm2.snid
GROUP BY rawMaterials.snid
;

SELECT "DONE!" AS "";