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
}

type invoice struct {
	ID       int64
	Number   string
	Sealed   bool
	Proto    *pb.Invoice
	TotalNet int
	Total    int
}

func (m *model) getInvoices(ctx context.Context) ([]invoice, error) {
	q := `
		select invoice_seal.final_uid, invoice.id, invoice.proto from invoice
		left join invoice_seal
		on invoice_seal.invoice_id = invoice.id
	`
	rows, err := m.db.QueryContext(ctx, q)
	if err != nil {
		return []invoice{}, err
	}
	defer rows.Close()

	res := []invoice{}
	for rows.Next() {
		i := invoice{
			Proto: &pb.Invoice{},
		}
		buf := []byte{}

		number := sql.NullString{}
		if err := rows.Scan(&number, &i.ID, &buf); err != nil {
			return []invoice{}, err
		}

		if err := proto.Unmarshal(buf, i.Proto); err != nil {
			return []invoice{}, err
		}

		if number.Valid {
			i.Sealed = true
			i.Number = number.String
		} else {
			i.Number = "proforma"
		}

		i.Total = 0
		i.TotalNet = 0
		for _, it := range i.Proto.Item {
			rowTotalNet := int(it.UnitPrice * it.Count)
			rowTotal := int(float64(rowTotalNet) * (float64(1) + float64(it.Vat)/100000))
			i.TotalNet += rowTotalNet
			i.Total += rowTotal
		}

		res = append(res, i)
	}

	return res, nil
}