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
|
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,
original_price INT,
discounted_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)
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.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
}
|
