84 lines
2.4 KiB
Go
84 lines
2.4 KiB
Go
package database
|
|
|
|
import (
|
|
"context"
|
|
"fmt"
|
|
|
|
"Engine/internal/model"
|
|
)
|
|
|
|
type ActualsRepo struct {
|
|
db *DB
|
|
}
|
|
|
|
func NewActualsRepo(db *DB) *ActualsRepo {
|
|
return &ActualsRepo{db: db}
|
|
}
|
|
|
|
func (r *ActualsRepo) Ingest(ctx context.Context, req model.IngestActualsRequest) (*model.Actual, error) {
|
|
var deptID, glID int
|
|
if err := r.db.QueryRowContext(ctx, `SELECT id FROM departments WHERE code=?`, req.DeptCode).Scan(&deptID); err != nil {
|
|
return nil, fmt.Errorf("department %q not found: %w", req.DeptCode, err)
|
|
}
|
|
if err := r.db.QueryRowContext(ctx, `SELECT id FROM gl_accounts WHERE code=?`, req.GLCode).Scan(&glID); err != nil {
|
|
return nil, fmt.Errorf("GL account %q not found: %w", req.GLCode, err)
|
|
}
|
|
|
|
_, err := r.db.ExecContext(ctx, `
|
|
INSERT INTO actuals (fiscal_year, fiscal_period, department_id, gl_account_id, amount, currency, source)
|
|
VALUES (?,?,?,?,?,?,?)
|
|
ON CONFLICT (fiscal_year, fiscal_period, department_id, gl_account_id)
|
|
DO UPDATE SET amount=excluded.amount, source=excluded.source`,
|
|
req.FiscalYear, req.FiscalPeriod, deptID, glID,
|
|
req.Amount, req.Currency, req.Source,
|
|
)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("upsert actual: %w", err)
|
|
}
|
|
|
|
a := &model.Actual{}
|
|
err = r.db.QueryRowContext(ctx, `
|
|
SELECT id, fiscal_year, fiscal_period, department_id, gl_account_id,
|
|
amount, currency, source, ingested_at
|
|
FROM actuals
|
|
WHERE fiscal_year=? AND fiscal_period=? AND department_id=? AND gl_account_id=?`,
|
|
req.FiscalYear, req.FiscalPeriod, deptID, glID,
|
|
).Scan(
|
|
&a.ID, &a.FiscalYear, &a.FiscalPeriod,
|
|
&a.DepartmentID, &a.GLAccountID,
|
|
&a.Amount, &a.Currency, &a.Source, &a.IngestedAt,
|
|
)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("fetch ingested actual: %w", err)
|
|
}
|
|
return a, nil
|
|
}
|
|
|
|
func (r *ActualsRepo) ListByPeriod(ctx context.Context, fiscalYear, fiscalPeriod int, deptCode string) (map[string]float64, error) {
|
|
rows, err := r.db.QueryContext(ctx, `
|
|
SELECT g.code, a.amount
|
|
FROM actuals a
|
|
JOIN departments d ON d.id = a.department_id
|
|
JOIN gl_accounts g ON g.id = a.gl_account_id
|
|
WHERE a.fiscal_year = ?
|
|
AND a.fiscal_period = ?
|
|
AND (? = '' OR d.code = ?)`,
|
|
fiscalYear, fiscalPeriod, deptCode, deptCode,
|
|
)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("list actuals: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
result := make(map[string]float64)
|
|
for rows.Next() {
|
|
var code string
|
|
var amount float64
|
|
if err := rows.Scan(&code, &amount); err != nil {
|
|
return nil, err
|
|
}
|
|
result[code] += amount
|
|
}
|
|
return result, rows.Err()
|
|
}
|