Improves database view.

1 files changed, 59 insertions, 4 deletions

diff --git a/crawler/database.go b/crawler/database.go
index 085f210..241d9f4 100644
--- a/crawler/database.go
+++ b/crawler/database.go
@@ -41,15 +41,70 @@ func (app *App) createTables() error {
 		return err
 	}
 
-	query3 := `CREATE OR REPLACE VIEW whisky_view AS
+	whisky_view := `CREATE OR REPLACE VIEW whisky_view AS
 		SELECT
-			angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price, discounted_price, angebot.base_price, image_url,
-			shop.name as shop, shop.url as shop_url, (original_price/discounted_price) AS quotient 
+			angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url,
+			shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent 
 		FROM angebot
 		JOIN shop ON angebot.shop = shop.id
 		WHERE
 			spirit_type = "Whisky" AND original_price > 1998`
-	_, err = app.DB.Exec(query3)
+	_, err = app.DB.Exec(whisky_view)
+	if err != nil {
+		return err
+	}
+
+	wodka_view := `CREATE OR REPLACE VIEW wodka_view AS
+		SELECT
+			angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url,
+			shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent 
+		FROM angebot
+		JOIN shop ON angebot.shop = shop.id
+		WHERE
+			spirit_type = "Wodka"`
+	_, err = app.DB.Exec(wodka_view)
+	if err != nil {
+		return err
+	}
+
+	gin_view := `CREATE OR REPLACE VIEW gin_view AS
+		SELECT
+			angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url,
+			shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent 
+		FROM angebot
+		JOIN shop ON angebot.shop = shop.id
+		WHERE
+			spirit_type = "gin"`
+	_, err = app.DB.Exec(gin_view)
+	if err != nil {
+		return err
+	}
+
+	rum_view := `CREATE OR REPLACE VIEW rum_view AS
+		SELECT
+			angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url,
+			shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent 
+		FROM angebot
+		JOIN shop ON angebot.shop = shop.id
+		WHERE
+			spirit_type = "rum"`
+	_, err = app.DB.Exec(rum_view)
+	if err != nil {
+		return err
+	}
+
+	misc_view := `CREATE OR REPLACE VIEW misc_view AS
+		SELECT
+			angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price, image_url,
+			shop.name as shop, shop.url as shop_url, ROUND(100-((discounted_price/original_price)*100)) AS procent 
+		FROM angebot
+		JOIN shop ON angebot.shop = shop.id
+		WHERE
+			spirit_type = "Anderes"`
+	_, err = app.DB.Exec(misc_view)
+	if err != nil {
+		return err
+	}
 
 	return err
 }