CREATE VIEW "Current Business Rules" AS SELECT * FROM "Business Rules" where "ruleset ID" = (select max("ruleset ID") from "Business Rules" where "date" = (SELECT MAX( "date" ) FROM "Business Rules" WHERE "date" < NOW( ) ) );
--select statement was: SELECT "Business Rules"."date", "Business Rules"."retail member markup", "Business Rules"."retail community markup", "Business Rules"."bulk member markup", "Business Rules"."bulk community markup", "Business Rules"."default retail price calculation method", "Business Rules"."enable override set by operator", "Business Rules"."override price usage policy", "Business Rules"."ruleset ID" FROM "Business Rules" WHERE "date" IN ( SELECT MAX( "date" ) FROM "Business Rules" WHERE "date" < NOW( ) );
CREATE VIEW "Current Retail Override Prices" ("retail unit ID","override price") AS SELECT "RSU"."retail unit ID", "RPO"."override price" FROM "Retail Sales Units" AS "RSU", "Retail Price Overrides" AS "RPO" WHERE "RSU"."retail unit ID" = "RPO"."retail unit ID" AND "RPO"."override date" = ( SELECT MAX( "RPO2"."override date" ) FROM "Retail Price Overrides" AS "RPO2", "Current Business Rules" AS "CBR" WHERE "RSU"."retail unit ID" = "RPO2"."retail unit ID" AND ( ( "CBR"."override price usage policy" = 2 AND ( "RPO2"."approval status" = 2 OR "RPO2"."approval status" = 0 ) ) OR ( "CBR"."override price usage policy" = 1 AND ( "RPO2"."approval status" = 2 ) ) ) );
CREATE VIEW "Retail Sales Unit Masses" ("retail unit ID","grams") AS SELECT RSUkg."retail unit ID", RSUkg."retail size" * 1000 as "grams" from "Retail Sales Units" RSUkg WHERE RSUkg."retail unit" = 'kg'
UNION ALL
SELECT RSUg."retail unit ID", RSUg."retail size" as "grams" from "Retail Sales Units" RSUg WHERE RSUg."retail unit" = 'g'
UNION ALL
SELECT RSUlb."retail unit ID", RSUlb."retail size" * 453.59237 as "grams" from "Retail Sales Units" RSUlb WHERE RSUlb."retail unit" = 'lb';
CREATE VIEW "Retail Sales Unit Units" ("retail unit ID","units") AS SELECT "RSUunits"."retail unit ID", "RSUunits"."retail size" AS "units" FROM "Retail Sales Units" AS "RSUunits" WHERE "RSUunits"."retail unit" = 'units';
CREATE VIEW "Current Bulk Purchasesn" ("purchase ID","product ID","purchase price","purchase size","purchase unit","purchase date") AS SELECT "bp"."purchase ID", "bp"."product ID", "bp"."purchase price", "bp"."purchase size", "bp"."purchase unit", "bp"."purchase date" FROM "Bulk Purchases" AS "bp" WHERE "bp"."purchase ID" = ( SELECT MAX( "bp1"."purchase ID" ) FROM "Bulk Purchases" AS "bp1" WHERE "bp1"."product ID" = "bp"."product ID" AND "bp1"."purchase date" = ( SELECT MAX( "bp2"."purchase date" ) FROM "Bulk Purchases" AS "bp2" WHERE "bp2"."product ID" = "bp"."product ID" AND "bp2"."purchase date" < NOW( ) ) );
CREATE VIEW "Calculated Retail Prices" ("purchase ID","grams") AS SELECT BPkg."purchase ID", BPkg."purchase size" * 1000 as "grams" from "Current Bulk Purchasesn" BPkg WHERE BPkg."purchase unit" = 'kg'
UNION ALL
SELECT BPg."purchase ID", BPg."purchase size" as "grams" from "Current Bulk Purchasesn" BPg WHERE BPg."purchase unit" = 'g'
UNION ALL
SELECT BPlb."purchase ID", BPlb."purchase size" * 453.59237 as "grams" from "Current Bulk Purchasesn" BPlb WHERE BPlb."purchase unit" = 'lb';
CREATE VIEW "Current Bulk Purchases" ("purchase ID","product ID","purchase price","purchase size","purchase unit","purchase date") AS SELECT "bp"."purchase ID", "bp"."product ID", "bp"."purchase price", "bp"."purchase size", "bp"."purchase unit", "bp"."purchase date" FROM "Bulk Purchases" AS "bp" WHERE "bp"."purchase ID" = (SELECT MAX("bp1"."purchase ID") FROM "Bulk Purchases" AS "bp1" WHERE "bp1"."product ID" = "bp"."product ID" AND "bp1"."purchase date" = (SELECT MAX( "bp2"."purchase date" ) FROM "Bulk Purchases" AS "bp2" WHERE "bp2"."product ID" = "bp"."product ID" AND "bp2"."purchase date" < NOW( ) ) );
CREATE VIEW "Current Bulk Purchase Masses" ("purchase ID","grams") AS SELECT BPkg."purchase ID", BPkg."purchase size" * 1000 as "grams" from "Current Bulk Purchases" BPkg WHERE BPkg."purchase unit" = 'kg'
UNION ALL
SELECT BPg."purchase ID", BPg."purchase size" as "grams" from "Current Bulk Purchases" BPg WHERE BPg."purchase unit" = 'g'
UNION ALL
SELECT BPlb."purchase ID", BPlb."purchase size" * 453.59237 as "grams" from "Current Bulk Purchases" BPlb WHERE BPlb."purchase unit" = 'lb';
CREATE VIEW "Current Bulk Purchase Units" ("purchase ID","units") AS SELECT "BP"."purchase ID", "BP"."purchase size" AS "units" FROM "Current Bulk Purchases" AS "BP" WHERE "BP"."purchase unit" = 'units';
CREATE VIEW "Retail Sales Units Current Price Calculation Method" ("retail unit ID","method") AS select RSU."retail unit ID", RSU."price calculation method" as "method"
from "Retail Sales Units" as RSU
where RSU."price calculation method" <> 0
UNION ALL -- the above is for units not using the default method
SELECT RSU."retail unit ID", CBR."default retail price calculation method" as "method"
from "Current Business Rules" as CBR, "Retail Sales Units" as RSU
where RSU."price calculation method" = 0;
CREATE VIEW "Retail Sales Units Per Current Bulk Purchase" ("retail unit ID","retail units per bulk purchase") AS SELECT "RSU"."retail unit ID", "CBPM"."grams" / "RSUM"."grams" AS "retail units per bulk purchase"
FROM "Retail Sales Unit Masses" AS "RSUM", "Retail Sales Units" AS "RSU", "Current Bulk Purchases" AS "CBP", "Current Bulk Purchase Masses" AS "CBPM", "Current Business Rules" as CBR, "Retail Sales Units Current Price Calculation Method" as RSUCPCM
WHERE RSUCPCM."method" = 1 AND RSUCPCM."retail unit ID" = RSU."retail unit ID" AND "RSUM"."retail unit ID" = "RSU"."retail unit ID" AND "RSU"."product ID" = "CBP"."product ID" AND "CBP"."purchase ID" = "CBPM"."purchase ID"
UNION ALL  -- the above select uses the BY_SIZE method, the next calculates complete retail units per bulk purchase ------
SELECT "RSU"."retail unit ID", truncate("CBPM"."grams" / "RSUM"."grams", 0) AS "retail units per bulk purchase"
FROM "Retail Sales Unit Masses" AS "RSUM", "Retail Sales Units" AS "RSU", "Current Bulk Purchases" AS "CBP", "Current Bulk Purchase Masses" AS "CBPM", "Current Business Rules" as CBR, "Retail Sales Units Current Price Calculation Method" as RSUCPCM
WHERE RSUCPCM."method" = 2 AND RSUCPCM."retail unit ID" = RSU."retail unit ID" AND "RSUM"."retail unit ID" = "RSU"."retail unit ID" AND "RSU"."product ID" = "CBP"."product ID" AND "CBP"."purchase ID" = "CBPM"."purchase ID"
UNION ALL -- ----the above selects are for units measured by weight/mass; below are for units sold by the unit ----------
SELECT "RSU"."retail unit ID", "CBPU"."units" / "RSUU"."units" AS "retail units per bulk purchase"
FROM "Retail Sales Unit Units" AS "RSUU", "Retail Sales Units" AS "RSU", "Current Bulk Purchases" AS "CBP", "Current Bulk Purchase Units" AS "CBPU", "Current Business Rules" as CBR, "Retail Sales Units Current Price Calculation Method" as RSUCPCM
WHERE RSUCPCM."method" = 1 AND RSUCPCM."retail unit ID" = RSU."retail unit ID" AND "RSUU"."retail unit ID" = "RSU"."retail unit ID" AND "RSU"."product ID" = "CBP"."product ID" AND "CBP"."purchase ID" = "CBPU"."purchase ID"
UNION ALL  -- the above select uses the BY_SIZE method, the next calculates complete retail units per bulk purchase ------
SELECT "RSU"."retail unit ID", truncate("CBPU"."units" / "RSUU"."units", 0) AS "retail units per bulk purchase"
FROM "Retail Sales Unit Units" AS "RSUU", "Retail Sales Units" AS "RSU", "Current Bulk Purchases" AS "CBP", "Current Bulk Purchase Units" AS "CBPU", "Current Business Rules" as CBR, "Retail Sales Units Current Price Calculation Method" as RSUCPCM
WHERE RSUCPCM."method" = 2 AND RSUCPCM."retail unit ID" = RSU."retail unit ID" AND "RSUU"."retail unit ID" = "RSU"."retail unit ID" AND "RSU"."product ID" = "CBP"."product ID" AND "CBP"."purchase ID" = "CBPU"."purchase ID";
CREATE VIEW "Current Retail Sales Unit Prices" ("retail unit ID","price") AS -- Current Retail Sales Unit Prices
---- uses override prices if they exist for the item,
---- otherwise calculates the price based on the bulk purchase price
---- and the number of units per bulk item
select RSU."retail unit ID",
  CBP."purchase price" / RSUPCBP."retail units per bulk purchase" as "price"
from "Current Bulk Purchases" as CBP,
  "Retail Sales Units Per Current Bulk Purchase" as RSUPCBP,
  "Retail Sales Units" as RSU
where NOT EXISTS (
  select CROP."override price" as "price"
  from "Current Retail Override Prices" as CROP
  where RSU."retail unit ID" = CROP."retail unit ID"
) and CBP."product ID" = RSU."product ID" and RSU."retail unit ID" = RSUPCBP."retail unit ID"
UNION ALL -- below we select the override prices
select CROP."retail unit ID", CROP."override price" as "price"
from "Current Retail Override Prices" as CROP;
