blob: fcf4aba6184a46fe6123559c21d2f1391e172374 [file] [log] [blame]
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +02001package main
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "strconv"
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +02008 "time"
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +02009
10 "github.com/golang/glog"
11 "github.com/golang/protobuf/proto"
12 _ "github.com/mattn/go-sqlite3"
13 "google.golang.org/grpc/codes"
14 "google.golang.org/grpc/status"
15
Sergiusz Bazanski1446e982019-07-21 16:04:23 +020016 pb "code.hackerspace.pl/hscloud/bgpwtf/invoice/proto"
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +020017)
18
19type model struct {
20 db *sql.DB
21}
22
23func newModel(dsn string) (*model, error) {
24 db, err := sql.Open("sqlite3", dsn)
25 if err != nil {
26 return nil, err
27 }
28 return &model{
29 db: db,
30 }, nil
31}
32
33func (m *model) init() error {
34 _, err := m.db.Exec(`
35 create table invoice (
36 id integer primary key not null,
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +020037 created_time integer not null,
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +020038 proto blob not null
39 );
40 create table invoice_seal (
41 id integer primary key not null,
42 invoice_id integer not null,
43 final_uid text not null unique,
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +020044 sealed_time integer not null,
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +020045 foreign key (invoice_id) references invoice(id)
46 );
47 create table invoice_blob (
48 id integer primary key not null,
49 invoice_id integer not null,
50 pdf blob not null,
51 foreign key (invoice_id) references invoice(id)
52 );
53 `)
54 return err
55}
56
Sergiusz Bazanskia818ef22019-06-07 10:37:22 +020057func (m *model) sealInvoice(ctx context.Context, uid, language string, useProformaTime bool) error {
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +020058 id, err := strconv.Atoi(uid)
59 if err != nil {
60 return status.Error(codes.InvalidArgument, "invalid uid")
61 }
62
63 invoice, err := m.getInvoice(ctx, uid)
64 if err != nil {
65 return err
66 }
67
68 tx, err := m.db.BeginTx(ctx, nil)
69 if err != nil {
70 return err
71 }
72
73 q := `
74 insert into invoice_seal (
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +020075 invoice_id, final_uid, sealed_time
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +020076 ) values (
77 ?,
Sergiusz Bazanski572d7662020-01-14 14:03:38 +010078 ( select printf("%04d", ifnull( (select final_uid as v from invoice_seal order by final_uid desc limit 1), 20000) + 1 )),
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +020079 ?
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +020080 )
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +020081 `
Sergiusz Bazanski572d7662020-01-14 14:03:38 +010082
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +020083 sealTime := time.Now()
Sergiusz Bazanskia818ef22019-06-07 10:37:22 +020084 if useProformaTime {
85 sealTime = time.Unix(0, invoice.Date)
86 }
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +020087 res, err := tx.Exec(q, id, sealTime.UnixNano())
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +020088 if err != nil {
89 return err
90 }
91
92 lastInvoiceSealId, err := res.LastInsertId()
93 if err != nil {
94 return err
95 }
96
97 q = `
98 select final_uid from invoice_seal where id = ?
99 `
100
101 var finalUid string
102 if err := tx.QueryRow(q, lastInvoiceSealId).Scan(&finalUid); err != nil {
103 return err
104 }
105
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200106 invoice.State = pb.Invoice_STATE_SEALED
Sergiusz Bazanskia818ef22019-06-07 10:37:22 +0200107 // TODO(q3k): this should be configurable.
Sergiusz Bazanskibc27e642019-06-20 16:11:07 +0200108 invoice.FinalUid = fmt.Sprintf("FV/%s", finalUid)
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200109 invoice.Date = sealTime.UnixNano()
110 calculateInvoiceData(invoice)
111
Sergiusz Bazanskia818ef22019-06-07 10:37:22 +0200112 pdfBlob, err := renderInvoicePDF(invoice, language)
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200113 if err != nil {
114 return err
115 }
116
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +0200117 q = `
118 insert into invoice_blob (
119 invoice_id, pdf
120 ) values (
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200121 ?, ?
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +0200122 )
123 `
124
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +0200125 if _, err := tx.Exec(q, id, pdfBlob); err != nil {
126 return err
127 }
128
129 if err := tx.Commit(); err != nil {
130 return err
131 }
132
133 return nil
134}
135
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200136func (m *model) createInvoice(ctx context.Context, id *pb.InvoiceData) (string, error) {
137 data, err := proto.Marshal(id)
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +0200138 if err != nil {
139 return "", err
140 }
141
142 sql := `
143 insert into invoice (
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200144 proto, created_time
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +0200145 ) values (
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200146 ?, ?
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +0200147 )
148 `
Sergiusz Bazanskia818ef22019-06-07 10:37:22 +0200149
150 t := time.Now()
151 if id.Date != 0 {
152 t = time.Unix(0, id.Date)
153 }
154
155 res, err := m.db.Exec(sql, data, t.UnixNano())
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +0200156 if err != nil {
157 return "", err
158 }
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200159 uid, err := res.LastInsertId()
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +0200160 if err != nil {
161 return "", err
162 }
163
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200164 glog.Infof("%+v", uid)
165 return fmt.Sprintf("%d", uid), nil
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +0200166}
167
168func (m *model) getRendered(ctx context.Context, uid string) ([]byte, error) {
169 id, err := strconv.Atoi(uid)
170 if err != nil {
171 return nil, status.Error(codes.InvalidArgument, "invalid uid")
172 }
173
174 q := `
175 select invoice_blob.pdf from invoice_blob where invoice_blob.invoice_id = ?
176 `
177 res := m.db.QueryRow(q, id)
178
179 data := []byte{}
180 if err := res.Scan(&data); err != nil {
181 if err == sql.ErrNoRows {
182 return nil, status.Error(codes.InvalidArgument, "no such invoice")
183 }
184 return nil, err
185 }
186 return data, nil
187}
188
189func (m *model) getSealedUid(ctx context.Context, uid string) (string, error) {
190 id, err := strconv.Atoi(uid)
191 if err != nil {
192 return "", status.Error(codes.InvalidArgument, "invalid uid")
193 }
194
195 q := `
196 select invoice_seal.final_uid from invoice_seal where invoice_seal.invoice_id = ?
197 `
198 res := m.db.QueryRow(q, id)
199 finalUid := ""
200 if err := res.Scan(&finalUid); err != nil {
201 if err == sql.ErrNoRows {
202 return "", nil
203 }
204 return "", err
205 }
206 return finalUid, nil
207}
208
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200209type sqlInvoiceSealRow struct {
210 proto []byte
211 createdTime int64
212 sealedTime sql.NullInt64
213 finalUid sql.NullString
214 uid int64
215}
216
217func (s *sqlInvoiceSealRow) Proto() (*pb.Invoice, error) {
218 data := &pb.InvoiceData{}
219 if err := proto.Unmarshal(s.proto, data); err != nil {
220 return nil, err
221 }
222
223 p := &pb.Invoice{
224 Uid: fmt.Sprintf("%d", s.uid),
225 Data: data,
226 }
227 if s.finalUid.Valid {
228 p.State = pb.Invoice_STATE_SEALED
Sergiusz Bazanskibc27e642019-06-20 16:11:07 +0200229 p.FinalUid = fmt.Sprintf("FV/%s", s.finalUid.String)
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200230 p.Date = s.sealedTime.Int64
231 } else {
232 p.State = pb.Invoice_STATE_PROFORMA
233 p.FinalUid = fmt.Sprintf("PROFORMA/%d", s.uid)
234 p.Date = s.createdTime
235 }
236 calculateInvoiceData(p)
237 return p, nil
238}
239
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +0200240func (m *model) getInvoice(ctx context.Context, uid string) (*pb.Invoice, error) {
241 id, err := strconv.Atoi(uid)
242 if err != nil {
243 return nil, status.Error(codes.InvalidArgument, "invalid uid")
244 }
245
246 q := `
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200247 select
248 invoice.id, invoice.proto, invoice.created_time, invoice_seal.sealed_time, invoice_seal.final_uid
249 from invoice
250 left join invoice_seal
251 on invoice_seal.invoice_id = invoice.id
252 where invoice.id = ?
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +0200253 `
254 res := m.db.QueryRow(q, id)
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200255 row := sqlInvoiceSealRow{}
256 if err := res.Scan(&row.uid, &row.proto, &row.createdTime, &row.sealedTime, &row.finalUid); err != nil {
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +0200257 if err == sql.ErrNoRows {
258 return nil, status.Error(codes.NotFound, "no such invoice")
259 }
260 return nil, err
261 }
262
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200263 return row.Proto()
Sergiusz Bazanskifb18c992019-05-01 12:27:03 +0200264}
Sergiusz Bazanski57ef6b02019-05-01 14:08:29 +0200265
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200266func (m *model) getInvoices(ctx context.Context) ([]*pb.Invoice, error) {
Sergiusz Bazanski57ef6b02019-05-01 14:08:29 +0200267 q := `
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200268 select
269 invoice.id, invoice.proto, invoice.created_time, invoice_seal.sealed_time, invoice_seal.final_uid
270 from invoice
Sergiusz Bazanski57ef6b02019-05-01 14:08:29 +0200271 left join invoice_seal
272 on invoice_seal.invoice_id = invoice.id
273 `
274 rows, err := m.db.QueryContext(ctx, q)
275 if err != nil {
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200276 return nil, err
Sergiusz Bazanski57ef6b02019-05-01 14:08:29 +0200277 }
278 defer rows.Close()
279
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200280 res := []*pb.Invoice{}
281
Sergiusz Bazanski57ef6b02019-05-01 14:08:29 +0200282 for rows.Next() {
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200283 row := sqlInvoiceSealRow{}
284 if err := rows.Scan(&row.uid, &row.proto, &row.createdTime, &row.sealedTime, &row.finalUid); err != nil {
285 return nil, err
Sergiusz Bazanski57ef6b02019-05-01 14:08:29 +0200286 }
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200287 p, err := row.Proto()
288 if err != nil {
289 return nil, err
Sergiusz Bazanski57ef6b02019-05-01 14:08:29 +0200290 }
Sergiusz Bazanski3976e3c2019-05-01 15:27:49 +0200291 res = append(res, p)
Sergiusz Bazanski57ef6b02019-05-01 14:08:29 +0200292 }
293
294 return res, nil
295}