go/svc/invoice/model.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"strconv"

	"github.com/golang/glog"
	"github.com/golang/protobuf/proto"
	_ "github.com/mattn/go-sqlite3"
	"google.golang.org/grpc/codes"
	"google.golang.org/grpc/status"

	pb "code.hackerspace.pl/hscloud/proto/invoice"
)

type model struct {
	db *sql.DB
}

func newModel(dsn string) (*model, error) {
	db, err := sql.Open("sqlite3", dsn)
	if err != nil {
		return nil, err
	}
	return &model{
		db: db,
	}, nil
}

func (m *model) init() error {
	_, err := m.db.Exec(`
		create table invoice (
			id integer primary key not null,
			proto blob not null
		);
		create table invoice_seal (
			id integer primary key not null,
			invoice_id integer not null,
			final_uid text not null unique,
			foreign key (invoice_id) references invoice(id)
		);
		create table invoice_blob (
			id integer primary key not null,
			invoice_id integer not null,
			pdf blob not null,
			foreign key (invoice_id) references invoice(id)
		);
	`)
	return err
}

func (m *model) sealInvoice(ctx context.Context, uid string) error {
	id, err := strconv.Atoi(uid)
	if err != nil {
		return status.Error(codes.InvalidArgument, "invalid uid")
	}

	invoice, err := m.getInvoice(ctx, uid)
	if err != nil {
		return err
	}

	tx, err := m.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}

	q := `
		insert into invoice_seal (
			invoice_id, final_uid
		) values (
			?,
			( select printf("%04d", ifnull( (select final_uid as v from invoice_seal order by final_uid desc limit 1), 19000) + 1 ))
		)

	`
	res, err := tx.Exec(q, id)
	if err != nil {
		return err
	}

	lastInvoiceSealId, err := res.LastInsertId()
	if err != nil {
		return err
	}

	q = `
		select final_uid from invoice_seal where id = ?
	`

	var finalUid string
	if err := tx.QueryRow(q, lastInvoiceSealId).Scan(&finalUid); err != nil {
		return err
	}

	q = `
		insert into invoice_blob (
			invoice_id, pdf
		) values (
			?,
			?
		)
	`

	pdfBlob, err := renderInvoicePDF(invoice, finalUid, false)
	if err != nil {
		return err
	}

	if _, err := tx.Exec(q, id, pdfBlob); err != nil {
		return err
	}

	if err := tx.Commit(); err != nil {
		return err
	}

	return nil
}

func (m *model) createInvoice(ctx context.Context, i *pb.Invoice) (string, error) {
	data, err := proto.Marshal(i)
	if err != nil {
		return "", err
	}

	sql := `
		insert into invoice (
			proto
		) values (
			?
		)
	`
	res, err := m.db.Exec(sql, data)
	if err != nil {
		return "", err
	}
	id, err := res.LastInsertId()
	if err != nil {
		return "", err
	}

	glog.Infof("%+v", id)
	return fmt.Sprintf("%d", id), nil
}

func (m *model) getRendered(ctx context.Context, uid string) ([]byte, error) {
	id, err := strconv.Atoi(uid)
	if err != nil {
		return nil, status.Error(codes.InvalidArgument, "invalid uid")
	}

	q := `
		select invoice_blob.pdf from invoice_blob where invoice_blob.invoice_id = ?
	`
	res := m.db.QueryRow(q, id)

	data := []byte{}
	if err := res.Scan(&data); err != nil {
		if err == sql.ErrNoRows {
			return nil, status.Error(codes.InvalidArgument, "no such invoice")
		}
		return nil, err
	}
	return data, nil
}

func (m *model) getSealedUid(ctx context.Context, uid string) (string, error) {
	id, err := strconv.Atoi(uid)
	if err != nil {
		return "", status.Error(codes.InvalidArgument, "invalid uid")
	}

	q := `
		select invoice_seal.final_uid from invoice_seal where invoice_seal.invoice_id = ?
	`
	res := m.db.QueryRow(q, id)
	finalUid := ""
	if err := res.Scan(&finalUid); err != nil {
		if err == sql.ErrNoRows {
			return "", nil
		}
		return "", err
	}
	return finalUid, nil
}

func (m *model) getInvoice(ctx context.Context, uid string) (*pb.Invoice, error) {
	id, err := strconv.Atoi(uid)
	if err != nil {
		return nil, status.Error(codes.InvalidArgument, "invalid uid")
	}

	q := `
		select invoice.proto from invoice where invoice.id = ?
	`
	res := m.db.QueryRow(q, id)
	data := []byte{}
	if err := res.Scan(&data); err != nil {
		if err == sql.ErrNoRows {
			return nil, status.Error(codes.NotFound, "no such invoice")
		}
		return nil, err
	}

	p := &pb.Invoice{}
	if err := proto.Unmarshal(data, p); err != nil {
		return nil, err
	}
	return p, nil
}