Adds detection of expired offers.

5 files changed, 171 insertions, 120 deletions

diff --git a/config.go b/config.go
index 30387a3..2706201 100644
--- a/config.go
+++ b/config.go
@@ -9,11 +9,13 @@ type Config struct {
 	DBDriver   string
 	DBDBName   string
 	DBHost     string
-	DBPort     int
+	DBPort     string
 	DBUser     string
 	DBPassword string
 	DBOptions  string
 	DBPath     string // for sqlite
+
+	Debug bool
 }
 
 // Parses the configuration and sets the configuration struct.
@@ -21,11 +23,13 @@ func (c *Config) parseConfig(configFile string) {
 
 	viper.SetDefault("DBDriver", "mysql")
 	viper.SetDefault("DBDBName", "alkobote")
-	viper.SetDefault("DBHost", "127.0.0.1")
-	viper.SetDefault("DBPort", 3306)
+	viper.SetDefault("DBHost", "localhost")
+	viper.SetDefault("DBPort", "3306")
 
 	viper.SetDefault("DBPath", "./alkobote.db")
 
+	viper.SetDefault("Debug", false)
+
 	// Name of the configuration file
 	viper.SetConfigName("config")
 
@@ -62,10 +66,11 @@ func (c *Config) parseConfig(configFile string) {
 func (c *Config) setsConfig() {
 	c.DBDriver = viper.GetString("DBDriver")
 	c.DBHost = viper.GetString("DBHost")
-	c.DBPort = viper.GetInt("DBPort")
+	c.DBPort = viper.GetString("DBPort")
 	c.DBUser = viper.GetString("DBUser")
 	c.DBPassword = viper.GetString("DBPassword")
 	c.DBDBName = viper.GetString("DBDBName")
 	c.DBOptions = viper.GetString("DBOptions")
-	c.DBOptions = viper.GetString("DBPath")
+	c.DBPath = viper.GetString("DBPath")
+	c.Debug = viper.GetBool("Debug")
 }
diff --git a/database.go b/database.go
new file mode 100644
index 0000000..d5d59de
--- /dev/null
+++ b/database.go
@@ -0,0 +1,129 @@
+package main
+
+import (
+	"database/sql"
+	"log"
+)
+
+func (app *App) createTables() error {
+	query1 := `CREATE TABLE IF NOT EXISTS angebot (
+		id INTEGER PRIMARY KEY AUTO_INCREMENT,
+		shop TEXT,
+		name TEXT,
+		url TEXT,
+		original_price INT,
+		discounted_price INT,
+		image_url TEXT,
+		spirit_type TEXT,
+		valid_until INT DEFAULT NULL,
+		created_at INT
+	)`
+	_, err := app.DB.Exec(query1)
+	if err != nil {
+		return err
+	}
+
+	query2 := `CREATE TABLE IF NOT EXISTS shop(
+		id INTEGER PRIMARY KEY AUTO_INCREMENT,
+		name text,
+		url text,
+		logo_url text,
+		shipping_costs text,
+		free_shipping text
+	)`
+	_, err = app.DB.Exec(query2)
+	return err
+}
+
+func (app *App) save_offer(W []Angebot) error {
+
+	query := `INSERT INTO angebot (shop, name, url, original_price, discounted_price, valid_until, image_url, spirit_type, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`
+
+	stmt, err := app.DB.Prepare(query)
+	if err != nil {
+		return err
+	}
+	defer stmt.Close()
+
+	for _, o := range W {
+
+		var found int
+
+		if o.Discounted_price == 0 || o.Original_price == 0 {
+			continue
+		}
+
+		err := app.DB.QueryRow("SELECT 1 FROM angebot WHERE shop = ? AND name = ? AND url = ? AND original_price = ? AND discounted_price = ? AND image_url = ? AND spirit_type = ?", o.Shop, o.Name, o.Url, o.Original_price, o.Discounted_price, o.Image_url, o.Spirit_type).Scan(&found)
+
+		/*
+		 */
+
+		if err == sql.ErrNoRows {
+
+			if 0 == o.Valid_until {
+				_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Original_price, o.Discounted_price, sql.NullInt64{}, o.Image_url, o.Spirit_type, app.Now)
+			} else {
+				_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Original_price, o.Discounted_price, o.Valid_until, o.Image_url, o.Spirit_type, app.Now)
+			}
+			if err != nil {
+				return err
+			}
+
+		}
+	}
+
+	//return app.remove_expired(W)
+	return nil
+}
+
+func (app *App) remove_expired(W []Angebot) error {
+
+	query := `SELECT id, shop, name, url, original_price, discounted_price FROM angebot WHERE created_at < ? AND valid_until IS NULL`
+
+	rows, err := app.DB.Queryx(query, app.Now)
+	if err != nil {
+		return err
+	}
+	defer rows.Close()
+
+	for rows.Next() {
+		var offer_db Angebot
+		err = rows.StructScan(&offer_db)
+
+		if err != nil {
+			return err
+		}
+
+		if !app.offer_contains(W, offer_db) {
+			expire_query := `UPDATE angebot SET valid_until = ? WHERE id = ?`
+			_, err = app.DB.Exec(expire_query, app.Now, offer_db.Id)
+			if err != nil {
+				return err
+			}
+		}
+	}
+
+	return nil
+}
+
+func (app *App) offer_contains(W []Angebot, offer_db Angebot) bool {
+	for _, v := range W {
+		if v.Shop == offer_db.Shop && v.Name == offer_db.Name && v.Url == offer_db.Url && v.Original_price == offer_db.Original_price && v.Discounted_price == offer_db.Discounted_price {
+
+			if app.Config.Debug {
+				log.Println("Contains: " + v.Name)
+				log.Println("")
+			}
+
+			return true
+
+		}
+	}
+
+	if app.Config.Debug {
+		log.Println("Contains not: " + offer_db.Name)
+		log.Println("")
+	}
+
+	return false
+}
diff --git a/db.go b/db.go
deleted file mode 100644
index 8ea7165..0000000
--- a/db.go
+++ /dev/null
@@ -1,64 +0,0 @@
-package main
-
-import (
-	//	"github.com/jmoiron/sqlx"
-	"database/sql"
-)
-
-func (app *App) createTables() error {
-	query1 := `CREATE TABLE IF NOT EXISTS angebot (
-		id INTEGER PRIMARY KEY AUTOINCREMENT,
-		shop text,
-		name text,
-		url text,
-		original_price int,
-		discounted_price int,
-		valid_until text,
-		image_url text,
-		spirit_type text,
-		created_at text
-	)`
-	_, err := app.DB.Exec(query1)
-	if err != nil {
-		return err
-	}
-
-	query2 := `CREATE TABLE IF NOT EXISTS shop(
-		id INTEGER PRIMARY KEY AUTOINCREMENT,
-		name text,
-		url text,
-		logo_url text,
-		shipping_costs text,
-		free_shipping text
-	)`
-	_, err = app.DB.Exec(query2)
-	return err
-}
-
-func (app *App) save_offer(W []Angebot) error {
-
-	query := `INSERT INTO angebot (shop, name, url, original_price, discounted_price, valid_until, image_url, spirit_type) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`
-
-	stmt, err := app.DB.Prepare(query)
-	if err != nil {
-		return err
-	}
-
-	for _, o := range W {
-
-		var found int
-
-		err := app.DB.QueryRow("SELECT 1 FROM angebot WHERE shop = ? AND name = ? AND url = ? AND original_price = ? AND discounted_price = ? AND image_url = ? AND spirit_type = ?", o.Shop, o.Name, o.Url, o.Original_price, o.Discounted_price, o.Image_url, o.Spirit_type).Scan(&found)
-
-		if err == sql.ErrNoRows {
-
-			_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Original_price, o.Discounted_price, o.Valid_until, o.Image_url, o.Spirit_type)
-			if err != nil {
-				return err
-			}
-
-		}
-	}
-
-	return nil
-}
diff --git a/main.go b/main.go
index beb3aa1..3dc3fc0 100644
--- a/main.go
+++ b/main.go
@@ -4,10 +4,11 @@ import (
 	"encoding/json"
 	"fmt"
 	"log"
+	"time"
 
 	_ "database/sql"
 	_ "github.com/go-sql-driver/mysql"
-	_ "github.com/mattn/go-sqlite3"
+	//_ "github.com/mattn/go-sqlite3"
 
 	"github.com/jmoiron/sqlx"
 )
@@ -17,9 +18,12 @@ type App struct {
 	Shops  []Shop
 	Config *Config
 	DB     *sqlx.DB
+	Now    int64
+	Debug  bool
 }
 
 type Angebot struct {
+	Id               int
 	Name             string
 	Shop             string
 	Url              string
@@ -27,7 +31,7 @@ type Angebot struct {
 	Discounted_price int
 	Image_url        string
 	Spirit_type      string
-	Valid_until      string
+	Valid_until      int
 }
 
 type Shop struct {
@@ -45,79 +49,43 @@ func main() {
 	app := App{Config: &Config{}}
 	app.Config.parseConfig("")
 
+	app.Now = time.Now().Unix()
+
 	if "sqlite3" == app.Config.DBDriver {
 		//app.DB, err = sqlx.Connect(app.Config.DBDriver, app.Config.DBPath)
-		app.DB, err = sqlx.Connect(app.Config.DBDriver, "./alkobote.db")
+		app.DB, err = sqlx.Connect(app.Config.DBDriver, app.Config.DBPath)
 	} else {
-		app.DB, err = sqlx.Connect(app.Config.DBDriver, app.Config.DBUser+":"+app.Config.DBPassword+"@"+app.Config.DBHost+"/"+app.Config.DBDBName+app.Config.DBOptions)
-	}
-	if err != nil {
-		log.Fatal(err)
-	}
-
-	err = app.createTables()
-	if err != nil {
-		log.Fatal(err)
-	}
-
-	W := ScrapeBottleWord()
-	err = app.save_offer(W)
-	if err != nil {
-		log.Fatal(err)
-	}
-	printName(W, "BottleWorld")
-
-	W = ScrapeMCWhisky()
-	err = app.save_offer(W)
-	if err != nil {
-		log.Fatal(err)
-	}
-	printName(W, "MC Whisky")
 
-	W = ScrapeRumundCo()
-	err = app.save_offer(W)
-	if err != nil {
-		log.Fatal(err)
+		if app.Config.Debug {
+			log.Println(app.Config.DBUser + ":" + app.Config.DBPassword + "@tcp(" + app.Config.DBHost + ":" + app.Config.DBPort + ")/" + app.Config.DBDBName + app.Config.DBOptions)
+		}
+		app.DB, err = sqlx.Connect(app.Config.DBDriver, app.Config.DBUser+":"+app.Config.DBPassword+"@tcp("+app.Config.DBHost+":"+app.Config.DBPort+")/"+app.Config.DBDBName+app.Config.DBOptions)
 	}
-	printName(W, "Rum und Co")
+	defer app.DB.Close()
 
-	W = ScrapeWhic()
-	err = app.save_offer(W)
 	if err != nil {
 		log.Fatal(err)
 	}
-	printName(W, "Whic")
 
-	W = ScrapeWhiskyde()
-	err = app.save_offer(W)
+	err = app.createTables()
 	if err != nil {
 		log.Fatal(err)
 	}
-	printName(W, "Whisky.de")
 
-	W = ScrapeWhiskysitenl()
-	err = app.save_offer(W)
-	if err != nil {
-		log.Fatal(err)
-	}
-	printName(W, "Whiskysite.nl")
+	W := ScrapeHTML()
 
-	W = ScrapeWhiskyworld()
 	err = app.save_offer(W)
 	if err != nil {
 		log.Fatal(err)
 	}
-	printName(W, "Whiskyworld")
-
-	W = ScrapeWhiskyzone()
-	err = app.save_offer(W)
+	err = app.remove_expired(W)
 	if err != nil {
 		log.Fatal(err)
 	}
-	printName(W, "Whiskyzone")
 }
 
 func printName(W []Angebot, name string) {
+	return
 	fmt.Println("-------------------")
 	fmt.Println("Sonderangebote von " + name)
 	fmt.Println("-------------------")
@@ -129,3 +97,16 @@ func printName(W []Angebot, name string) {
 
 	fmt.Println(string(output))
 }
+
+func ScrapeHTML() []Angebot {
+	W := ScrapeBottleWord()
+	W = append(W, ScrapeMCWhisky()...)
+	W = append(W, ScrapeRumundCo()...)
+	W = append(W, ScrapeWhic()...)
+	W = append(W, ScrapeWhiskyde()...)
+	W = append(W, ScrapeWhiskysitenl()...)
+	W = append(W, ScrapeWhiskyworld()...)
+	W = append(W, ScrapeWhiskyzone()...)
+
+	return W
+}
diff --git a/whiskyde.go b/whiskyde.go
index 62e30f1..5bf3248 100644
--- a/whiskyde.go
+++ b/whiskyde.go
@@ -43,7 +43,7 @@ func ScrapeWhiskyde() []Angebot {
 		})
 
 		e.ForEach(".article-price-prefix", func(i int, e *colly.HTMLElement) {
-			W.Valid_until = e.ChildText(".article-price-special")
+			//W.Valid_until = e.ChildText(".article-price-special")
 		})
 
 		W.Shop = "Whisky.de"