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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
|
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,
base_price 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
}
whisky_view := `CREATE OR REPLACE VIEW whisky_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 = "Whisky" AND original_price > 1998`
_, 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
}
func (app *App) save_offer(W []Angebot) error {
query := `INSERT INTO angebot (shop, name, url, abv, volume, original_price, discounted_price, base_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 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.Base_price, 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.Base_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.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
}
|
