budget-go/database/model/transaction.go

package model

import (
	"crypto/md5"
	"encoding/csv"
	"encoding/hex"
	"errors"
	"io/ioutil"
	"regexp"
	"strconv"
	"strings"
	"time"

	"gitnet.fr/deblan/budget/database/manager"
	"golang.org/x/text/encoding/charmap"
	"golang.org/x/text/transform"
)

type Transaction struct {
	ID              uint         `gorm:"primaryKey" json:"id"`
	ShortLabel      string       `gorm:"not null" json:"short_label"`
	Label           string       `gorm:"not null" json:"label"`
	Reference       string       `gorm:"not null" json:"reference"`
	Information     string       `json:"information"`
	OperationType   string       `gorm:"not null" json:"operation_type"`
	AccountedAt     time.Time    `json:"accounted_at"`
	BankCategory    string       `json:"bank_category"`
	BankSubCategory string       `json:"bank_sub_category"`
	Debit           float64      `json:"debit"`
	Credit          float64      `json:"credit"`
	Date            time.Time    `json:"date"`
	BankAccountID   int          `gorm:"not null" json:"-"`
	BankAccount     *BankAccount `json:"bank_account"`
	CategoryID      *int         `json:"-"`
	Category        *Category    `json:"category"`
	CreatedAt       time.Time    `json:"created_at"`
	UpdatedAt       time.Time    `json:"updated_at"`
}

func NewTransaction(label string) *Transaction {
	transaction := Transaction{}

	return &transaction
}

func (t *Transaction) MatchRule(rule CategoryRule) (bool, int) {
	match := true
	counter := 0

	if rule.Contain != nil {
		v := strings.ToLower(*rule.Contain)
		match = strings.Contains(strings.ToLower(t.Label), v) || strings.Contains(strings.ToLower(t.ShortLabel), v) || strings.Contains(strings.ToLower(t.Reference), v)

		if match {
			counter += 8
		}
	}

	if rule.Match != nil {
		res, _ := regexp.MatchString(*rule.Match, t.Label)

		match = match && res

		if match {
			counter += 8
		}
	} else {
		match = match && true
	}

	if rule.BankCategory != nil {
		v := strings.ToLower(*rule.BankCategory)
		match = match && (strings.Contains(strings.ToLower(t.BankCategory), v) || strings.Contains(strings.ToLower(t.BankSubCategory), v))

		if match {
			counter += 6
		}
	} else {
		match = match && true
	}

	if rule.Amount != nil {
		match = match && t.Debit == *rule.Amount

		if match {
			counter += 4
		}
	} else {
		match = match && true
	}

	if rule.DateFrom != nil {
		match = match && t.Date.After(*rule.DateFrom)

		if match {
			counter += 2
		}
	} else {
		match = match && true
	}

	if rule.DateTo != nil {
		match = match && t.Date.Before(*rule.DateTo)

		if match {
			counter += 2
		}
	} else {
		match = match && true
	}

	return match, counter
}

func (t *Transaction) MatchCategory(category Category) (bool, int) {
	maxCounter := 0
	match := false

	for _, rule := range category.Rules {
		m, c := t.MatchRule(rule)

		if m {
			match = true

			if c > maxCounter {
				maxCounter = c
			}
		}
	}

	return match, maxCounter
}

func ImportCaisseEpargneTransactions(content string, bankAccountID int) ([]Transaction, error) {
	lines := strings.Split(content, "\n")
	db := manager.Get().Db
	datas := []Transaction{}

	for key, line := range lines {
		if key == 0 {
			continue
		}

		line = strings.TrimSpace(line)

		if line == "" {
			continue
		}

		fields := strings.Split(line, ";")

		if len(fields) != 13 {
			return datas, errors.New("Invalid format")
		}

		ref := fields[3]

		if ref == "" {
			hash := md5.New()
			hash.Write([]byte(line))
			hashInBytes := hash.Sum(nil)
			ref = hex.EncodeToString(hashInBytes)
		}

		var count int64

		db.Model(Transaction{}).Where(Transaction{Reference: ref}).Count(&count)

		if count > 0 {
			continue
		}

		transaction := Transaction{
			ShortLabel:      toUTF8(fields[1]),
			Label:           toUTF8(fields[2]),
			Reference:       ref,
			Information:     fields[4],
			OperationType:   fields[5],
			AccountedAt:     toDate(fields[0], "02/01/2006"),
			BankCategory:    fields[6],
			BankSubCategory: fields[7],
			Debit:           -toFloat(fields[8]),
			Credit:          toFloat(fields[9]),
			BankAccountID:   bankAccountID,
			Date:            toDate(fields[10], "02/01/2006"),
		}

		db.Model(Transaction{}).Save(&transaction)

		datas = append(datas, transaction)
	}

	return datas, nil
}

func ImportRevolutTransactions(content string, bankAccountID int) ([]Transaction, error) {
	db := manager.Get().Db
	datas := []Transaction{}

	r := csv.NewReader(strings.NewReader(content))
	records, err := r.ReadAll()

	if err != nil {
		return nil, err
	}

	for key, fields := range records {
		if key == 0 {
			continue
		}

		if len(fields) != 10 {
			return datas, errors.New("Invalid format")
		}

		hash := md5.New()
		hash.Write([]byte(strings.Join(fields, ",")))
		hashInBytes := hash.Sum(nil)
		ref := hex.EncodeToString(hashInBytes)

		if fields[8] != "COMPLETED" {
			continue
		}

		var count int64

		db.Model(Transaction{}).Where(Transaction{Reference: ref}).Count(&count)

		if count > 0 {
			continue
		}

		amount := toFloat(fields[5])
		var debit float64
		var credit float64

		if amount < 0 {
			debit = -amount
			credit = 0
		} else {
			debit = 0
			credit = amount
		}

		date := toDate(fields[2], "2006-01-02 15:04:05")

		transaction := Transaction{
			ShortLabel:      fields[4],
			Label:           fields[4],
			Reference:       ref,
			Information:     "",
			OperationType:   fields[0],
			AccountedAt:     date,
			BankCategory:    "",
			BankSubCategory: "",
			Debit:           debit,
			Credit:          credit,
			BankAccountID:   bankAccountID,
			Date:            date,
		}

		db.Model(Transaction{}).Save(&transaction)

		datas = append(datas, transaction)
	}

	return datas, nil
}

func ImportTransactions(content string, bankAccountID int, format string) ([]Transaction, error) {
	var datas []Transaction
	var err error

	if format == "revolut" {
		datas, err = ImportRevolutTransactions(content, bankAccountID)
	} else {
		datas, err = ImportCaisseEpargneTransactions(content, bankAccountID)
	}

	UpdateTransactionsCategories()
	return datas, err
}

func UpdateTransactionsCategories() []Transaction {
	var categories []Category
	var transactions []Transaction
	var datas []Transaction

	manager.Get().Db.Model(Category{}).Preload("Rules").Find(&categories)
	manager.Get().Db.Model(Transaction{}).Find(&transactions)

	for _, transaction := range transactions {
		transaction.CategoryID = nil
		maxCounter := -1

		for _, category := range categories {
			match, counter := transaction.MatchCategory(category)

			if match && counter > maxCounter {
				maxCounter = counter

				var id int
				id = int(category.ID)
				transaction.CategoryID = &id
			}
		}

		manager.Get().Db.Model(Transaction{}).Where("id = ?", transaction.ID).Save(&transaction)
		datas = append(datas, transaction)
	}

	return datas
}

func toFloat(value string) float64 {
	value = strings.ReplaceAll(value, ",", ".")
	v, _ := strconv.ParseFloat(value, 64)

	return v
}

func toDate(value, format string) time.Time {
	v, _ := time.Parse(format, value)

	return v
}

func toUTF8(input string) string {
	reader := transform.NewReader(strings.NewReader(input), charmap.ISO8859_1.NewDecoder())
	decoded, _ := ioutil.ReadAll(reader)

	return string(decoded)
}