1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
package main
import (
"database/sql"
"log"
)
func (app *App) createTables() error {
query1 := `CREATE TABLE IF NOT EXISTS shop(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name varchar(255) UNIQUE,
url varchar(255) UNIQUE,
logo_url text,
shipping_costs text,
free_shipping text
)`
_, err := app.DB.Exec(query1)
if err != nil {
return err
}
query2 := `CREATE TABLE IF NOT EXISTS angebot (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
shop int,
name TEXT,
url TEXT,
short_url TEXT,
abv FLOAT(100,0),
volume FLOAT,
original_price INT,
discounted_price INT,
price_per_litre INT,
image_url TEXT,
spirit_type TEXT,
valid_until INT DEFAULT NULL,
created_at INT,
FOREIGN KEY(shop) REFERENCES shop(id)
)`
_, err = app.DB.Exec(query2)
if err != nil {
return err
}
query3 := `CREATE OR REPLACE VIEW whisky_view AS
SELECT
angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url,original_price, discounted_price, angebot.price_per_litre, image_url,
shop.name as shop, shop.url as shop_url, (original_price/discounted_price) AS quotient
FROM angebot
JOIN shop ON angebot.shop = shop.id
WHERE
spirit_type = "Whisky" AND original_price > 1998`
_, err = app.DB.Exec(query3)
return err
}
func (app *App) save_offer(W []Angebot) error {
query := `INSERT INTO angebot (shop, name, url, abv, volume, original_price, discounted_price, price_per_litre, 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 spirit_type = ?", o.Shop, o.Name, o.Url, o.Original_price, o.Discounted_price, o.Spirit_type).Scan(&found)
/*
*/
if err == sql.ErrNoRows {
if 0 == o.Valid_until {
_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Abv, o.Volume, o.Original_price, o.Discounted_price, o.Price_per_litre, sql.NullInt64{}, o.Image_url, o.Spirit_type, app.Now)
} else {
_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Abv, o.Volume, o.Original_price, o.Discounted_price, o.Price_per_litre, 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.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
}
|
