// Code generated by sqlc. DO NOT EDIT. // versions: // sqlc v1.27.0 // source: accounting.sql package db import ( "context" "github.com/jackc/pgx/v5/pgtype" "github.com/shopspring/decimal" ) const acctCountAdvances = `-- name: AcctCountAdvances :one SELECT count(*) FROM advances adv LEFT JOIN customers c ON c.id = adv.customer_id LEFT JOIN suppliers s ON s.id = adv.supplier_id WHERE ($1::text IS NULL OR adv.advance_type = $1) AND ($2::text IS NULL OR adv.status = $2) AND ($3::text IS NULL OR adv.advance_number ILIKE '%' || $3 || '%' OR c.name ILIKE '%' || $3 || '%' OR s.name ILIKE '%' || $3 || '%') ` type AcctCountAdvancesParams struct { Type pgtype.Text `json:"type"` Status pgtype.Text `json:"status"` Search pgtype.Text `json:"search"` } // ---------- advances ---------- func (q *Queries) AcctCountAdvances(ctx context.Context, arg AcctCountAdvancesParams) (int64, error) { row := q.db.QueryRow(ctx, acctCountAdvances, arg.Type, arg.Status, arg.Search) var count int64 err := row.Scan(&count) return count, err } const acctCountJournals = `-- name: AcctCountJournals :one SELECT count(*) FROM journal_entries e WHERE ($1::text IS NULL OR e.journal_type = $1) AND ($2::text[] IS NULL OR e.journal_type = ANY($2::text[])) AND ($3::text IS NULL OR e.status = $3) AND ($4::bigint IS NULL OR e.store_id = $4) AND ($5::bigint[] IS NULL OR (e.store_id IS NOT NULL AND e.store_id = ANY($5))) AND ($6::date IS NULL OR e.entry_date >= $6) AND ($7::date IS NULL OR e.entry_date <= $7) AND ($8::text IS NULL OR e.journal_number ILIKE '%' || $8 || '%' OR e.reference ILIKE '%' || $8 || '%' OR e.description ILIKE '%' || $8 || '%') ` type AcctCountJournalsParams struct { JournalType pgtype.Text `json:"journal_type"` AllowedJournalTypes []string `json:"allowed_journal_types"` Status pgtype.Text `json:"status"` StoreID pgtype.Int8 `json:"store_id"` AllowedStoreIds []int64 `json:"allowed_store_ids"` DateFrom pgtype.Date `json:"date_from"` DateTo pgtype.Date `json:"date_to"` Search pgtype.Text `json:"search"` } // ---------- journal entries ---------- func (q *Queries) AcctCountJournals(ctx context.Context, arg AcctCountJournalsParams) (int64, error) { row := q.db.QueryRow(ctx, acctCountJournals, arg.JournalType, arg.AllowedJournalTypes, arg.Status, arg.StoreID, arg.AllowedStoreIds, arg.DateFrom, arg.DateTo, arg.Search, ) var count int64 err := row.Scan(&count) return count, err } const acctCreateAccount = `-- name: AcctCreateAccount :one INSERT INTO accounts (code, name, type, parent_id, normal_balance, is_postable, is_placeholder, is_active) VALUES ($1, $2, $3, $8, $4, $5, $6, $7) RETURNING id, code, name, type, parent_id, normal_balance, is_postable, is_placeholder, is_active, created_at, updated_at ` type AcctCreateAccountParams struct { Code string `json:"code"` Name string `json:"name"` Type string `json:"type"` NormalBalance string `json:"normal_balance"` IsPostable bool `json:"is_postable"` IsPlaceholder bool `json:"is_placeholder"` IsActive bool `json:"is_active"` ParentID pgtype.Int8 `json:"parent_id"` } func (q *Queries) AcctCreateAccount(ctx context.Context, arg AcctCreateAccountParams) (Account, error) { row := q.db.QueryRow(ctx, acctCreateAccount, arg.Code, arg.Name, arg.Type, arg.NormalBalance, arg.IsPostable, arg.IsPlaceholder, arg.IsActive, arg.ParentID, ) var i Account err := row.Scan( &i.ID, &i.Code, &i.Name, &i.Type, &i.ParentID, &i.NormalBalance, &i.IsPostable, &i.IsPlaceholder, &i.IsActive, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctCreateAdvance = `-- name: AcctCreateAdvance :one INSERT INTO advances (advance_number, advance_type, customer_id, supplier_id, amount, allocated_amount, refunded_amount, status, advance_date, notes, journal_entry_id, created_by) VALUES ($1, $2, $8, $9, $3, $4, $5, $6, $7, $10, $11, $12) RETURNING id, advance_number, advance_type, customer_id, supplier_id, amount, allocated_amount, refunded_amount, status, advance_date, notes, journal_entry_id, created_by, created_at, updated_at ` type AcctCreateAdvanceParams struct { AdvanceNumber string `json:"advance_number"` AdvanceType string `json:"advance_type"` Amount decimal.Decimal `json:"amount"` AllocatedAmount decimal.Decimal `json:"allocated_amount"` RefundedAmount decimal.Decimal `json:"refunded_amount"` Status string `json:"status"` AdvanceDate pgtype.Date `json:"advance_date"` CustomerID pgtype.Int8 `json:"customer_id"` SupplierID pgtype.Int8 `json:"supplier_id"` Notes pgtype.Text `json:"notes"` JournalEntryID pgtype.Int8 `json:"journal_entry_id"` CreatedBy pgtype.Int8 `json:"created_by"` } func (q *Queries) AcctCreateAdvance(ctx context.Context, arg AcctCreateAdvanceParams) (Advance, error) { row := q.db.QueryRow(ctx, acctCreateAdvance, arg.AdvanceNumber, arg.AdvanceType, arg.Amount, arg.AllocatedAmount, arg.RefundedAmount, arg.Status, arg.AdvanceDate, arg.CustomerID, arg.SupplierID, arg.Notes, arg.JournalEntryID, arg.CreatedBy, ) var i Advance err := row.Scan( &i.ID, &i.AdvanceNumber, &i.AdvanceType, &i.CustomerID, &i.SupplierID, &i.Amount, &i.AllocatedAmount, &i.RefundedAmount, &i.Status, &i.AdvanceDate, &i.Notes, &i.JournalEntryID, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctCreateAdvanceTxn = `-- name: AcctCreateAdvanceTxn :one INSERT INTO advance_transactions (advance_id, txn_type, amount, reference, invoice_id, purchase_order_id, journal_entry_id, notes, created_by) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING id, advance_id, txn_type, amount, reference, invoice_id, purchase_order_id, journal_entry_id, notes, created_by, created_at ` type AcctCreateAdvanceTxnParams struct { AdvanceID int64 `json:"advance_id"` TxnType string `json:"txn_type"` Amount decimal.Decimal `json:"amount"` Reference pgtype.Text `json:"reference"` InvoiceID pgtype.Int8 `json:"invoice_id"` PurchaseOrderID pgtype.Int8 `json:"purchase_order_id"` JournalEntryID pgtype.Int8 `json:"journal_entry_id"` Notes pgtype.Text `json:"notes"` CreatedBy pgtype.Int8 `json:"created_by"` } func (q *Queries) AcctCreateAdvanceTxn(ctx context.Context, arg AcctCreateAdvanceTxnParams) (AdvanceTransaction, error) { row := q.db.QueryRow(ctx, acctCreateAdvanceTxn, arg.AdvanceID, arg.TxnType, arg.Amount, arg.Reference, arg.InvoiceID, arg.PurchaseOrderID, arg.JournalEntryID, arg.Notes, arg.CreatedBy, ) var i AdvanceTransaction err := row.Scan( &i.ID, &i.AdvanceID, &i.TxnType, &i.Amount, &i.Reference, &i.InvoiceID, &i.PurchaseOrderID, &i.JournalEntryID, &i.Notes, &i.CreatedBy, &i.CreatedAt, ) return i, err } const acctCreateDepartment = `-- name: AcctCreateDepartment :one INSERT INTO accounting_departments (code, name, is_active) VALUES ($1, $2, $3) RETURNING id, code, name, is_active, created_at, updated_at ` type AcctCreateDepartmentParams struct { Code string `json:"code"` Name string `json:"name"` IsActive pgtype.Bool `json:"is_active"` } func (q *Queries) AcctCreateDepartment(ctx context.Context, arg AcctCreateDepartmentParams) (AccountingDepartment, error) { row := q.db.QueryRow(ctx, acctCreateDepartment, arg.Code, arg.Name, arg.IsActive) var i AccountingDepartment err := row.Scan( &i.ID, &i.Code, &i.Name, &i.IsActive, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctCreateJournal = `-- name: AcctCreateJournal :one INSERT INTO journal_entries (journal_number, journal_type, entry_date, reference, description, status, store_id, total_debit, total_credit, created_by, posted_by, posted_at, reversal_of, reversal_entry_id, source_type, source_id) VALUES ($1, $2, $3, $7, $8, $4, $9, $5, $6, $10, $11, $12, $13, $14, $15, $16) RETURNING id, journal_number, journal_type, entry_date, reference, description, status, store_id, total_debit, total_credit, created_by, posted_by, posted_at, reversed_at, reversal_of, reversal_entry_id, source_type, source_id, created_at, updated_at ` type AcctCreateJournalParams struct { JournalNumber string `json:"journal_number"` JournalType string `json:"journal_type"` EntryDate pgtype.Date `json:"entry_date"` Status string `json:"status"` TotalDebit decimal.Decimal `json:"total_debit"` TotalCredit decimal.Decimal `json:"total_credit"` Reference pgtype.Text `json:"reference"` Description pgtype.Text `json:"description"` StoreID pgtype.Int8 `json:"store_id"` CreatedBy pgtype.Int8 `json:"created_by"` PostedBy pgtype.Int8 `json:"posted_by"` PostedAt pgtype.Timestamptz `json:"posted_at"` ReversalOf pgtype.Int8 `json:"reversal_of"` ReversalEntryID pgtype.Int8 `json:"reversal_entry_id"` SourceType pgtype.Text `json:"source_type"` SourceID pgtype.Int8 `json:"source_id"` } func (q *Queries) AcctCreateJournal(ctx context.Context, arg AcctCreateJournalParams) (JournalEntry, error) { row := q.db.QueryRow(ctx, acctCreateJournal, arg.JournalNumber, arg.JournalType, arg.EntryDate, arg.Status, arg.TotalDebit, arg.TotalCredit, arg.Reference, arg.Description, arg.StoreID, arg.CreatedBy, arg.PostedBy, arg.PostedAt, arg.ReversalOf, arg.ReversalEntryID, arg.SourceType, arg.SourceID, ) var i JournalEntry err := row.Scan( &i.ID, &i.JournalNumber, &i.JournalType, &i.EntryDate, &i.Reference, &i.Description, &i.Status, &i.StoreID, &i.TotalDebit, &i.TotalCredit, &i.CreatedBy, &i.PostedBy, &i.PostedAt, &i.ReversedAt, &i.ReversalOf, &i.ReversalEntryID, &i.SourceType, &i.SourceID, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctCreateJournalLine = `-- name: AcctCreateJournalLine :one INSERT INTO journal_lines (journal_entry_id, account_id, department_id, debit, credit, description) VALUES ($1, $2, $5, $3, $4, $6) RETURNING id ` type AcctCreateJournalLineParams struct { JournalEntryID int64 `json:"journal_entry_id"` AccountID int64 `json:"account_id"` Debit decimal.Decimal `json:"debit"` Credit decimal.Decimal `json:"credit"` DepartmentID pgtype.Int8 `json:"department_id"` Description pgtype.Text `json:"description"` } func (q *Queries) AcctCreateJournalLine(ctx context.Context, arg AcctCreateJournalLineParams) (int64, error) { row := q.db.QueryRow(ctx, acctCreateJournalLine, arg.JournalEntryID, arg.AccountID, arg.Debit, arg.Credit, arg.DepartmentID, arg.Description, ) var id int64 err := row.Scan(&id) return id, err } const acctDeleteJournal = `-- name: AcctDeleteJournal :execrows DELETE FROM journal_entries WHERE id = $1 ` func (q *Queries) AcctDeleteJournal(ctx context.Context, id int64) (int64, error) { result, err := q.db.Exec(ctx, acctDeleteJournal, id) if err != nil { return 0, err } return result.RowsAffected(), nil } const acctDeleteJournalLines = `-- name: AcctDeleteJournalLines :exec DELETE FROM journal_lines WHERE journal_entry_id = $1 ` func (q *Queries) AcctDeleteJournalLines(ctx context.Context, journalEntryID int64) error { _, err := q.db.Exec(ctx, acctDeleteJournalLines, journalEntryID) return err } const acctGetAccount = `-- name: AcctGetAccount :one SELECT id, code, name, type, parent_id, normal_balance, is_postable, is_placeholder, is_active, created_at, updated_at FROM accounts WHERE id = $1 ` func (q *Queries) AcctGetAccount(ctx context.Context, id int64) (Account, error) { row := q.db.QueryRow(ctx, acctGetAccount, id) var i Account err := row.Scan( &i.ID, &i.Code, &i.Name, &i.Type, &i.ParentID, &i.NormalBalance, &i.IsPostable, &i.IsPlaceholder, &i.IsActive, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctGetAccountByCode = `-- name: AcctGetAccountByCode :one SELECT id, code, name, type, parent_id, normal_balance, is_postable, is_placeholder, is_active, created_at, updated_at FROM accounts WHERE code = $1 ` func (q *Queries) AcctGetAccountByCode(ctx context.Context, code string) (Account, error) { row := q.db.QueryRow(ctx, acctGetAccountByCode, code) var i Account err := row.Scan( &i.ID, &i.Code, &i.Name, &i.Type, &i.ParentID, &i.NormalBalance, &i.IsPostable, &i.IsPlaceholder, &i.IsActive, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctGetAdvance = `-- name: AcctGetAdvance :one SELECT adv.id, adv.advance_number, adv.advance_type, adv.customer_id, adv.supplier_id, adv.amount, adv.allocated_amount, adv.refunded_amount, adv.status, adv.advance_date, adv.notes, adv.journal_entry_id, adv.created_by, adv.created_at, adv.updated_at, COALESCE(c.name, '') AS customer_name, COALESCE(s.name, '') AS supplier_name FROM advances adv LEFT JOIN customers c ON c.id = adv.customer_id LEFT JOIN suppliers s ON s.id = adv.supplier_id WHERE adv.id = $1 ` type AcctGetAdvanceRow struct { ID int64 `json:"id"` AdvanceNumber string `json:"advance_number"` AdvanceType string `json:"advance_type"` CustomerID pgtype.Int8 `json:"customer_id"` SupplierID pgtype.Int8 `json:"supplier_id"` Amount decimal.Decimal `json:"amount"` AllocatedAmount decimal.Decimal `json:"allocated_amount"` RefundedAmount decimal.Decimal `json:"refunded_amount"` Status string `json:"status"` AdvanceDate pgtype.Date `json:"advance_date"` Notes pgtype.Text `json:"notes"` JournalEntryID pgtype.Int8 `json:"journal_entry_id"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` CustomerName string `json:"customer_name"` SupplierName string `json:"supplier_name"` } func (q *Queries) AcctGetAdvance(ctx context.Context, id int64) (AcctGetAdvanceRow, error) { row := q.db.QueryRow(ctx, acctGetAdvance, id) var i AcctGetAdvanceRow err := row.Scan( &i.ID, &i.AdvanceNumber, &i.AdvanceType, &i.CustomerID, &i.SupplierID, &i.Amount, &i.AllocatedAmount, &i.RefundedAmount, &i.Status, &i.AdvanceDate, &i.Notes, &i.JournalEntryID, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, &i.CustomerName, &i.SupplierName, ) return i, err } const acctGetAdvanceForUpdate = `-- name: AcctGetAdvanceForUpdate :one SELECT id, advance_number, advance_type, customer_id, supplier_id, amount, allocated_amount, refunded_amount, status, advance_date, notes, journal_entry_id, created_by, created_at, updated_at FROM advances WHERE id = $1 FOR UPDATE ` func (q *Queries) AcctGetAdvanceForUpdate(ctx context.Context, id int64) (Advance, error) { row := q.db.QueryRow(ctx, acctGetAdvanceForUpdate, id) var i Advance err := row.Scan( &i.ID, &i.AdvanceNumber, &i.AdvanceType, &i.CustomerID, &i.SupplierID, &i.Amount, &i.AllocatedAmount, &i.RefundedAmount, &i.Status, &i.AdvanceDate, &i.Notes, &i.JournalEntryID, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctGetDepartment = `-- name: AcctGetDepartment :one SELECT id, code, name, is_active, created_at, updated_at FROM accounting_departments WHERE id = $1 ` func (q *Queries) AcctGetDepartment(ctx context.Context, id int64) (AccountingDepartment, error) { row := q.db.QueryRow(ctx, acctGetDepartment, id) var i AccountingDepartment err := row.Scan( &i.ID, &i.Code, &i.Name, &i.IsActive, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctGetDepartmentByCode = `-- name: AcctGetDepartmentByCode :one SELECT id, code, name, is_active, created_at, updated_at FROM accounting_departments WHERE code = $1 ` func (q *Queries) AcctGetDepartmentByCode(ctx context.Context, code string) (AccountingDepartment, error) { row := q.db.QueryRow(ctx, acctGetDepartmentByCode, code) var i AccountingDepartment err := row.Scan( &i.ID, &i.Code, &i.Name, &i.IsActive, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctGetJournal = `-- name: AcctGetJournal :one SELECT id, journal_number, journal_type, entry_date, reference, description, status, store_id, total_debit, total_credit, created_by, posted_by, posted_at, reversed_at, reversal_of, reversal_entry_id, source_type, source_id, created_at, updated_at FROM journal_entries WHERE id = $1 ` func (q *Queries) AcctGetJournal(ctx context.Context, id int64) (JournalEntry, error) { row := q.db.QueryRow(ctx, acctGetJournal, id) var i JournalEntry err := row.Scan( &i.ID, &i.JournalNumber, &i.JournalType, &i.EntryDate, &i.Reference, &i.Description, &i.Status, &i.StoreID, &i.TotalDebit, &i.TotalCredit, &i.CreatedBy, &i.PostedBy, &i.PostedAt, &i.ReversedAt, &i.ReversalOf, &i.ReversalEntryID, &i.SourceType, &i.SourceID, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctGetJournalForUpdate = `-- name: AcctGetJournalForUpdate :one SELECT id, journal_number, journal_type, entry_date, reference, description, status, store_id, total_debit, total_credit, created_by, posted_by, posted_at, reversed_at, reversal_of, reversal_entry_id, source_type, source_id, created_at, updated_at FROM journal_entries WHERE id = $1 FOR UPDATE ` func (q *Queries) AcctGetJournalForUpdate(ctx context.Context, id int64) (JournalEntry, error) { row := q.db.QueryRow(ctx, acctGetJournalForUpdate, id) var i JournalEntry err := row.Scan( &i.ID, &i.JournalNumber, &i.JournalType, &i.EntryDate, &i.Reference, &i.Description, &i.Status, &i.StoreID, &i.TotalDebit, &i.TotalCredit, &i.CreatedBy, &i.PostedBy, &i.PostedAt, &i.ReversedAt, &i.ReversalOf, &i.ReversalEntryID, &i.SourceType, &i.SourceID, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctJournalBySource = `-- name: AcctJournalBySource :one SELECT id, journal_number, journal_type, entry_date, reference, description, status, store_id, total_debit, total_credit, created_by, posted_by, posted_at, reversed_at, reversal_of, reversal_entry_id, source_type, source_id, created_at, updated_at FROM journal_entries WHERE source_type = $1 AND source_id = $2 ORDER BY id DESC LIMIT 1 ` type AcctJournalBySourceParams struct { SourceType pgtype.Text `json:"source_type"` SourceID pgtype.Int8 `json:"source_id"` } // Drill-down: find the auto-drafted journal for one upstream document // (GR accrual, advances). Latest wins; drafts and posted both match. func (q *Queries) AcctJournalBySource(ctx context.Context, arg AcctJournalBySourceParams) (JournalEntry, error) { row := q.db.QueryRow(ctx, acctJournalBySource, arg.SourceType, arg.SourceID) var i JournalEntry err := row.Scan( &i.ID, &i.JournalNumber, &i.JournalType, &i.EntryDate, &i.Reference, &i.Description, &i.Status, &i.StoreID, &i.TotalDebit, &i.TotalCredit, &i.CreatedBy, &i.PostedBy, &i.PostedAt, &i.ReversedAt, &i.ReversalOf, &i.ReversalEntryID, &i.SourceType, &i.SourceID, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctLedgerLines = `-- name: AcctLedgerLines :many SELECT l.id, e.id AS entry_id, e.status AS entry_status, e.entry_date, e.journal_number, e.journal_type, e.reference, e.description AS entry_description, l.description AS line_description, l.debit, l.credit, a.id AS account_id, a.code AS account_code, a.name AS account_name, a.type AS account_type, a.normal_balance FROM journal_lines l JOIN journal_entries e ON e.id = l.journal_entry_id JOIN accounts a ON a.id = l.account_id WHERE ($1::text IS NULL OR e.status = $1) AND ($1::text IS NOT NULL OR e.status IN ('posted','reversed')) AND ($2::text IS NULL OR e.journal_type = $2) AND ($3::bigint IS NULL OR e.store_id = $3) AND ($4::bigint[] IS NULL OR (e.store_id IS NOT NULL AND e.store_id = ANY($4))) AND ($5::date IS NULL OR e.entry_date >= $5) AND ($6::date IS NULL OR e.entry_date <= $6) AND ($7::text IS NULL OR a.code = $7) AND ($8::text IS NULL OR a.type = $8) AND ($9::text IS NULL OR e.journal_number ILIKE '%' || $9 || '%' OR e.reference ILIKE '%' || $9 || '%' OR l.description ILIKE '%' || $9 || '%' OR e.description ILIKE '%' || $9 || '%') ORDER BY a.code, e.entry_date, e.id, l.id ` type AcctLedgerLinesParams struct { Status pgtype.Text `json:"status"` JournalType pgtype.Text `json:"journal_type"` StoreID pgtype.Int8 `json:"store_id"` AllowedStoreIds []int64 `json:"allowed_store_ids"` DateFrom pgtype.Date `json:"date_from"` DateTo pgtype.Date `json:"date_to"` AccountCode pgtype.Text `json:"account_code"` AccountType pgtype.Text `json:"account_type"` Search pgtype.Text `json:"search"` } type AcctLedgerLinesRow struct { ID int64 `json:"id"` EntryID int64 `json:"entry_id"` EntryStatus string `json:"entry_status"` EntryDate pgtype.Date `json:"entry_date"` JournalNumber string `json:"journal_number"` JournalType string `json:"journal_type"` Reference pgtype.Text `json:"reference"` EntryDescription pgtype.Text `json:"entry_description"` LineDescription pgtype.Text `json:"line_description"` Debit decimal.Decimal `json:"debit"` Credit decimal.Decimal `json:"credit"` AccountID int64 `json:"account_id"` AccountCode string `json:"account_code"` AccountName string `json:"account_name"` AccountType string `json:"account_type"` NormalBalance string `json:"normal_balance"` } // ---------- general ledger ---------- // All journal entries (draft, posted, reversed) flow into the ledger with their respective status. func (q *Queries) AcctLedgerLines(ctx context.Context, arg AcctLedgerLinesParams) ([]AcctLedgerLinesRow, error) { rows, err := q.db.Query(ctx, acctLedgerLines, arg.Status, arg.JournalType, arg.StoreID, arg.AllowedStoreIds, arg.DateFrom, arg.DateTo, arg.AccountCode, arg.AccountType, arg.Search, ) if err != nil { return nil, err } defer rows.Close() var items []AcctLedgerLinesRow for rows.Next() { var i AcctLedgerLinesRow if err := rows.Scan( &i.ID, &i.EntryID, &i.EntryStatus, &i.EntryDate, &i.JournalNumber, &i.JournalType, &i.Reference, &i.EntryDescription, &i.LineDescription, &i.Debit, &i.Credit, &i.AccountID, &i.AccountCode, &i.AccountName, &i.AccountType, &i.NormalBalance, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const acctLedgerOpening = `-- name: AcctLedgerOpening :many SELECT a.id AS account_id, a.code AS account_code, a.name AS account_name, a.type AS account_type, a.normal_balance, SUM(CASE WHEN a.normal_balance = 'debit' THEN l.debit - l.credit ELSE l.credit - l.debit END)::numeric AS opening_balance FROM journal_lines l JOIN journal_entries e ON e.id = l.journal_entry_id JOIN accounts a ON a.id = l.account_id WHERE ($1::text IS NULL OR e.status = $1) AND ($1::text IS NOT NULL OR e.status IN ('posted','reversed')) AND e.entry_date < $2::date AND ($3::text IS NULL OR e.journal_type = $3) AND ($4::bigint IS NULL OR e.store_id = $4) AND ($5::bigint[] IS NULL OR (e.store_id IS NOT NULL AND e.store_id = ANY($5))) AND ($6::text IS NULL OR a.code = $6) AND ($7::text IS NULL OR a.type = $7) AND ($8::text IS NULL OR e.journal_number ILIKE '%' || $8 || '%' OR e.reference ILIKE '%' || $8 || '%' OR l.description ILIKE '%' || $8 || '%' OR e.description ILIKE '%' || $8 || '%') GROUP BY a.id, a.code, a.name, a.type, a.normal_balance ` type AcctLedgerOpeningParams struct { Status pgtype.Text `json:"status"` DateFrom pgtype.Date `json:"date_from"` JournalType pgtype.Text `json:"journal_type"` StoreID pgtype.Int8 `json:"store_id"` AllowedStoreIds []int64 `json:"allowed_store_ids"` AccountCode pgtype.Text `json:"account_code"` AccountType pgtype.Text `json:"account_type"` Search pgtype.Text `json:"search"` } type AcctLedgerOpeningRow struct { AccountID int64 `json:"account_id"` AccountCode string `json:"account_code"` AccountName string `json:"account_name"` AccountType string `json:"account_type"` NormalBalance string `json:"normal_balance"` OpeningBalance decimal.Decimal `json:"opening_balance"` } // Opening balance per account: signed movement (per normal balance) of all // ledger entries strictly before date_from. Same non-date filters apply. func (q *Queries) AcctLedgerOpening(ctx context.Context, arg AcctLedgerOpeningParams) ([]AcctLedgerOpeningRow, error) { rows, err := q.db.Query(ctx, acctLedgerOpening, arg.Status, arg.DateFrom, arg.JournalType, arg.StoreID, arg.AllowedStoreIds, arg.AccountCode, arg.AccountType, arg.Search, ) if err != nil { return nil, err } defer rows.Close() var items []AcctLedgerOpeningRow for rows.Next() { var i AcctLedgerOpeningRow if err := rows.Scan( &i.AccountID, &i.AccountCode, &i.AccountName, &i.AccountType, &i.NormalBalance, &i.OpeningBalance, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const acctListAccounts = `-- name: AcctListAccounts :many SELECT id, code, name, type, parent_id, normal_balance, is_postable, is_placeholder, is_active, created_at, updated_at FROM accounts WHERE ($1::bool IS NULL OR is_active = $1::bool) AND ($2::text IS NULL OR code ILIKE '%' || $2 || '%' OR name ILIKE '%' || $2 || '%') ORDER BY code ` type AcctListAccountsParams struct { Active pgtype.Bool `json:"active"` Search pgtype.Text `json:"search"` } // Accounting module queries: COA (accounts), departments, journals, // general ledger, advances, trial balance. All query names carry the Acct // prefix to stay unique across db/query. // ---------- chart of accounts ---------- func (q *Queries) AcctListAccounts(ctx context.Context, arg AcctListAccountsParams) ([]Account, error) { rows, err := q.db.Query(ctx, acctListAccounts, arg.Active, arg.Search) if err != nil { return nil, err } defer rows.Close() var items []Account for rows.Next() { var i Account if err := rows.Scan( &i.ID, &i.Code, &i.Name, &i.Type, &i.ParentID, &i.NormalBalance, &i.IsPostable, &i.IsPlaceholder, &i.IsActive, &i.CreatedAt, &i.UpdatedAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const acctListAdvanceTxns = `-- name: AcctListAdvanceTxns :many SELECT id, advance_id, txn_type, amount, reference, invoice_id, purchase_order_id, journal_entry_id, notes, created_by, created_at FROM advance_transactions WHERE advance_id = $1 ORDER BY id ` func (q *Queries) AcctListAdvanceTxns(ctx context.Context, advanceID int64) ([]AdvanceTransaction, error) { rows, err := q.db.Query(ctx, acctListAdvanceTxns, advanceID) if err != nil { return nil, err } defer rows.Close() var items []AdvanceTransaction for rows.Next() { var i AdvanceTransaction if err := rows.Scan( &i.ID, &i.AdvanceID, &i.TxnType, &i.Amount, &i.Reference, &i.InvoiceID, &i.PurchaseOrderID, &i.JournalEntryID, &i.Notes, &i.CreatedBy, &i.CreatedAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const acctListAdvances = `-- name: AcctListAdvances :many SELECT adv.id, adv.advance_number, adv.advance_type, adv.customer_id, adv.supplier_id, adv.amount, adv.allocated_amount, adv.refunded_amount, adv.status, adv.advance_date, adv.notes, adv.journal_entry_id, adv.created_by, adv.created_at, adv.updated_at, COALESCE(c.name, '') AS customer_name, COALESCE(s.name, '') AS supplier_name FROM advances adv LEFT JOIN customers c ON c.id = adv.customer_id LEFT JOIN suppliers s ON s.id = adv.supplier_id WHERE ($3::text IS NULL OR adv.advance_type = $3) AND ($4::text IS NULL OR adv.status = $4) AND ($5::text IS NULL OR adv.advance_number ILIKE '%' || $5 || '%' OR c.name ILIKE '%' || $5 || '%' OR s.name ILIKE '%' || $5 || '%') ORDER BY adv.id DESC LIMIT $1 OFFSET $2 ` type AcctListAdvancesParams struct { Limit int32 `json:"limit"` Offset int32 `json:"offset"` Type pgtype.Text `json:"type"` Status pgtype.Text `json:"status"` Search pgtype.Text `json:"search"` } type AcctListAdvancesRow struct { ID int64 `json:"id"` AdvanceNumber string `json:"advance_number"` AdvanceType string `json:"advance_type"` CustomerID pgtype.Int8 `json:"customer_id"` SupplierID pgtype.Int8 `json:"supplier_id"` Amount decimal.Decimal `json:"amount"` AllocatedAmount decimal.Decimal `json:"allocated_amount"` RefundedAmount decimal.Decimal `json:"refunded_amount"` Status string `json:"status"` AdvanceDate pgtype.Date `json:"advance_date"` Notes pgtype.Text `json:"notes"` JournalEntryID pgtype.Int8 `json:"journal_entry_id"` CreatedBy pgtype.Int8 `json:"created_by"` CreatedAt pgtype.Timestamptz `json:"created_at"` UpdatedAt pgtype.Timestamptz `json:"updated_at"` CustomerName string `json:"customer_name"` SupplierName string `json:"supplier_name"` } func (q *Queries) AcctListAdvances(ctx context.Context, arg AcctListAdvancesParams) ([]AcctListAdvancesRow, error) { rows, err := q.db.Query(ctx, acctListAdvances, arg.Limit, arg.Offset, arg.Type, arg.Status, arg.Search, ) if err != nil { return nil, err } defer rows.Close() var items []AcctListAdvancesRow for rows.Next() { var i AcctListAdvancesRow if err := rows.Scan( &i.ID, &i.AdvanceNumber, &i.AdvanceType, &i.CustomerID, &i.SupplierID, &i.Amount, &i.AllocatedAmount, &i.RefundedAmount, &i.Status, &i.AdvanceDate, &i.Notes, &i.JournalEntryID, &i.CreatedBy, &i.CreatedAt, &i.UpdatedAt, &i.CustomerName, &i.SupplierName, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const acctListDepartments = `-- name: AcctListDepartments :many SELECT id, code, name, is_active, created_at, updated_at FROM accounting_departments WHERE ($1::bool IS NULL OR is_active = $1::bool) AND ($2::text IS NULL OR code ILIKE '%' || $2 || '%' OR name ILIKE '%' || $2 || '%') ORDER BY code ` type AcctListDepartmentsParams struct { Active pgtype.Bool `json:"active"` Search pgtype.Text `json:"search"` } // ---------- departments ---------- func (q *Queries) AcctListDepartments(ctx context.Context, arg AcctListDepartmentsParams) ([]AccountingDepartment, error) { rows, err := q.db.Query(ctx, acctListDepartments, arg.Active, arg.Search) if err != nil { return nil, err } defer rows.Close() var items []AccountingDepartment for rows.Next() { var i AccountingDepartment if err := rows.Scan( &i.ID, &i.Code, &i.Name, &i.IsActive, &i.CreatedAt, &i.UpdatedAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const acctListJournalLines = `-- name: AcctListJournalLines :many SELECT l.id, l.journal_entry_id, l.account_id, l.department_id, l.debit, l.credit, l.description, a.code AS account_code, a.name AS account_name, d.code AS department_code FROM journal_lines l JOIN accounts a ON a.id = l.account_id LEFT JOIN accounting_departments d ON d.id = l.department_id WHERE l.journal_entry_id = $1 ORDER BY l.id ` type AcctListJournalLinesRow struct { ID int64 `json:"id"` JournalEntryID int64 `json:"journal_entry_id"` AccountID int64 `json:"account_id"` DepartmentID pgtype.Int8 `json:"department_id"` Debit decimal.Decimal `json:"debit"` Credit decimal.Decimal `json:"credit"` Description pgtype.Text `json:"description"` AccountCode string `json:"account_code"` AccountName string `json:"account_name"` DepartmentCode pgtype.Text `json:"department_code"` } func (q *Queries) AcctListJournalLines(ctx context.Context, journalEntryID int64) ([]AcctListJournalLinesRow, error) { rows, err := q.db.Query(ctx, acctListJournalLines, journalEntryID) if err != nil { return nil, err } defer rows.Close() var items []AcctListJournalLinesRow for rows.Next() { var i AcctListJournalLinesRow if err := rows.Scan( &i.ID, &i.JournalEntryID, &i.AccountID, &i.DepartmentID, &i.Debit, &i.Credit, &i.Description, &i.AccountCode, &i.AccountName, &i.DepartmentCode, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const acctListJournalLinesByEntries = `-- name: AcctListJournalLinesByEntries :many SELECT l.id, l.journal_entry_id, l.account_id, l.department_id, l.debit, l.credit, l.description, a.code AS account_code, a.name AS account_name, d.code AS department_code FROM journal_lines l JOIN accounts a ON a.id = l.account_id LEFT JOIN accounting_departments d ON d.id = l.department_id WHERE l.journal_entry_id = ANY($1::bigint[]) ORDER BY l.id ` type AcctListJournalLinesByEntriesRow struct { ID int64 `json:"id"` JournalEntryID int64 `json:"journal_entry_id"` AccountID int64 `json:"account_id"` DepartmentID pgtype.Int8 `json:"department_id"` Debit decimal.Decimal `json:"debit"` Credit decimal.Decimal `json:"credit"` Description pgtype.Text `json:"description"` AccountCode string `json:"account_code"` AccountName string `json:"account_name"` DepartmentCode pgtype.Text `json:"department_code"` } func (q *Queries) AcctListJournalLinesByEntries(ctx context.Context, dollar_1 []int64) ([]AcctListJournalLinesByEntriesRow, error) { rows, err := q.db.Query(ctx, acctListJournalLinesByEntries, dollar_1) if err != nil { return nil, err } defer rows.Close() var items []AcctListJournalLinesByEntriesRow for rows.Next() { var i AcctListJournalLinesByEntriesRow if err := rows.Scan( &i.ID, &i.JournalEntryID, &i.AccountID, &i.DepartmentID, &i.Debit, &i.Credit, &i.Description, &i.AccountCode, &i.AccountName, &i.DepartmentCode, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const acctListJournals = `-- name: AcctListJournals :many SELECT e.id, e.journal_number, e.journal_type, e.entry_date, e.reference, e.description, e.status, e.store_id, e.total_debit, e.total_credit, e.created_by, e.posted_by, e.posted_at, e.reversed_at, e.reversal_of, e.reversal_entry_id, e.source_type, e.source_id, e.created_at, e.updated_at FROM journal_entries e WHERE ($3::text IS NULL OR e.journal_type = $3) AND ($4::text[] IS NULL OR e.journal_type = ANY($4::text[])) AND ($5::text IS NULL OR e.status = $5) AND ($6::bigint IS NULL OR e.store_id = $6) AND ($7::bigint[] IS NULL OR (e.store_id IS NOT NULL AND e.store_id = ANY($7))) AND ($8::date IS NULL OR e.entry_date >= $8) AND ($9::date IS NULL OR e.entry_date <= $9) AND ($10::text IS NULL OR e.journal_number ILIKE '%' || $10 || '%' OR e.reference ILIKE '%' || $10 || '%' OR e.description ILIKE '%' || $10 || '%') ORDER BY e.entry_date DESC, e.id DESC LIMIT $1 OFFSET $2 ` type AcctListJournalsParams struct { Limit int32 `json:"limit"` Offset int32 `json:"offset"` JournalType pgtype.Text `json:"journal_type"` AllowedJournalTypes []string `json:"allowed_journal_types"` Status pgtype.Text `json:"status"` StoreID pgtype.Int8 `json:"store_id"` AllowedStoreIds []int64 `json:"allowed_store_ids"` DateFrom pgtype.Date `json:"date_from"` DateTo pgtype.Date `json:"date_to"` Search pgtype.Text `json:"search"` } func (q *Queries) AcctListJournals(ctx context.Context, arg AcctListJournalsParams) ([]JournalEntry, error) { rows, err := q.db.Query(ctx, acctListJournals, arg.Limit, arg.Offset, arg.JournalType, arg.AllowedJournalTypes, arg.Status, arg.StoreID, arg.AllowedStoreIds, arg.DateFrom, arg.DateTo, arg.Search, ) if err != nil { return nil, err } defer rows.Close() var items []JournalEntry for rows.Next() { var i JournalEntry if err := rows.Scan( &i.ID, &i.JournalNumber, &i.JournalType, &i.EntryDate, &i.Reference, &i.Description, &i.Status, &i.StoreID, &i.TotalDebit, &i.TotalCredit, &i.CreatedBy, &i.PostedBy, &i.PostedAt, &i.ReversedAt, &i.ReversalOf, &i.ReversalEntryID, &i.SourceType, &i.SourceID, &i.CreatedAt, &i.UpdatedAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const acctSetAdvanceAmounts = `-- name: AcctSetAdvanceAmounts :execrows UPDATE advances SET allocated_amount = $2, refunded_amount = $3, status = $4, updated_at = now() WHERE id = $1 ` type AcctSetAdvanceAmountsParams struct { ID int64 `json:"id"` AllocatedAmount decimal.Decimal `json:"allocated_amount"` RefundedAmount decimal.Decimal `json:"refunded_amount"` Status string `json:"status"` } func (q *Queries) AcctSetAdvanceAmounts(ctx context.Context, arg AcctSetAdvanceAmountsParams) (int64, error) { result, err := q.db.Exec(ctx, acctSetAdvanceAmounts, arg.ID, arg.AllocatedAmount, arg.RefundedAmount, arg.Status, ) if err != nil { return 0, err } return result.RowsAffected(), nil } const acctSetJournalPosted = `-- name: AcctSetJournalPosted :execrows UPDATE journal_entries SET status = 'posted', posted_by = $2, posted_at = now(), updated_at = now() WHERE id = $1 AND status = 'draft' ` type AcctSetJournalPostedParams struct { ID int64 `json:"id"` PostedBy pgtype.Int8 `json:"posted_by"` } func (q *Queries) AcctSetJournalPosted(ctx context.Context, arg AcctSetJournalPostedParams) (int64, error) { result, err := q.db.Exec(ctx, acctSetJournalPosted, arg.ID, arg.PostedBy) if err != nil { return 0, err } return result.RowsAffected(), nil } const acctSetJournalReversed = `-- name: AcctSetJournalReversed :execrows UPDATE journal_entries SET status = 'reversed', reversed_at = now(), reversal_entry_id = $2, updated_at = now() WHERE id = $1 ` type AcctSetJournalReversedParams struct { ID int64 `json:"id"` ReversalEntryID pgtype.Int8 `json:"reversal_entry_id"` } func (q *Queries) AcctSetJournalReversed(ctx context.Context, arg AcctSetJournalReversedParams) (int64, error) { result, err := q.db.Exec(ctx, acctSetJournalReversed, arg.ID, arg.ReversalEntryID) if err != nil { return 0, err } return result.RowsAffected(), nil } const acctSetJournalVoided = `-- name: AcctSetJournalVoided :execrows UPDATE journal_entries SET status = 'voided', updated_at = now() WHERE id = $1 AND status = 'posted' ` func (q *Queries) AcctSetJournalVoided(ctx context.Context, id int64) (int64, error) { result, err := q.db.Exec(ctx, acctSetJournalVoided, id) if err != nil { return 0, err } return result.RowsAffected(), nil } const acctTrialBalance = `-- name: AcctTrialBalance :many SELECT a.id, a.code, a.name, a.type, a.normal_balance, SUM(l.debit)::numeric AS total_debit, SUM(l.credit)::numeric AS total_credit FROM journal_lines l JOIN journal_entries e ON e.id = l.journal_entry_id JOIN accounts a ON a.id = l.account_id WHERE e.status IN ('posted','reversed') AND ($1::date IS NULL OR e.entry_date >= $1) AND ($2::date IS NULL OR e.entry_date <= $2) GROUP BY a.id, a.code, a.name, a.type, a.normal_balance HAVING SUM(l.debit) <> 0 OR SUM(l.credit) <> 0 ORDER BY a.code ` type AcctTrialBalanceParams struct { DateFrom pgtype.Date `json:"date_from"` DateTo pgtype.Date `json:"date_to"` } type AcctTrialBalanceRow struct { ID int64 `json:"id"` Code string `json:"code"` Name string `json:"name"` Type string `json:"type"` NormalBalance string `json:"normal_balance"` TotalDebit decimal.Decimal `json:"total_debit"` TotalCredit decimal.Decimal `json:"total_credit"` } // ---------- trial balance ---------- func (q *Queries) AcctTrialBalance(ctx context.Context, arg AcctTrialBalanceParams) ([]AcctTrialBalanceRow, error) { rows, err := q.db.Query(ctx, acctTrialBalance, arg.DateFrom, arg.DateTo) if err != nil { return nil, err } defer rows.Close() var items []AcctTrialBalanceRow for rows.Next() { var i AcctTrialBalanceRow if err := rows.Scan( &i.ID, &i.Code, &i.Name, &i.Type, &i.NormalBalance, &i.TotalDebit, &i.TotalCredit, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const acctUpdateAccount = `-- name: AcctUpdateAccount :one UPDATE accounts SET name = $2, type = $3, normal_balance = $4, is_postable = $5, is_placeholder = $6, is_active = $7, updated_at = now() WHERE id = $1 RETURNING id, code, name, type, parent_id, normal_balance, is_postable, is_placeholder, is_active, created_at, updated_at ` type AcctUpdateAccountParams struct { ID int64 `json:"id"` Name string `json:"name"` Type string `json:"type"` NormalBalance string `json:"normal_balance"` IsPostable bool `json:"is_postable"` IsPlaceholder bool `json:"is_placeholder"` IsActive bool `json:"is_active"` } func (q *Queries) AcctUpdateAccount(ctx context.Context, arg AcctUpdateAccountParams) (Account, error) { row := q.db.QueryRow(ctx, acctUpdateAccount, arg.ID, arg.Name, arg.Type, arg.NormalBalance, arg.IsPostable, arg.IsPlaceholder, arg.IsActive, ) var i Account err := row.Scan( &i.ID, &i.Code, &i.Name, &i.Type, &i.ParentID, &i.NormalBalance, &i.IsPostable, &i.IsPlaceholder, &i.IsActive, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctUpdateDepartment = `-- name: AcctUpdateDepartment :one UPDATE accounting_departments SET name = $2, is_active = $3, updated_at = now() WHERE id = $1 RETURNING id, code, name, is_active, created_at, updated_at ` type AcctUpdateDepartmentParams struct { ID int64 `json:"id"` Name string `json:"name"` IsActive bool `json:"is_active"` } func (q *Queries) AcctUpdateDepartment(ctx context.Context, arg AcctUpdateDepartmentParams) (AccountingDepartment, error) { row := q.db.QueryRow(ctx, acctUpdateDepartment, arg.ID, arg.Name, arg.IsActive) var i AccountingDepartment err := row.Scan( &i.ID, &i.Code, &i.Name, &i.IsActive, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const acctUpdateJournal = `-- name: AcctUpdateJournal :execrows UPDATE journal_entries SET journal_type = $2, entry_date = $3, reference = $6, description = $7, store_id = $8, total_debit = $4, total_credit = $5, updated_at = now() WHERE id = $1 ` type AcctUpdateJournalParams struct { ID int64 `json:"id"` JournalType string `json:"journal_type"` EntryDate pgtype.Date `json:"entry_date"` TotalDebit decimal.Decimal `json:"total_debit"` TotalCredit decimal.Decimal `json:"total_credit"` Reference pgtype.Text `json:"reference"` Description pgtype.Text `json:"description"` StoreID pgtype.Int8 `json:"store_id"` } func (q *Queries) AcctUpdateJournal(ctx context.Context, arg AcctUpdateJournalParams) (int64, error) { result, err := q.db.Exec(ctx, acctUpdateJournal, arg.ID, arg.JournalType, arg.EntryDate, arg.TotalDebit, arg.TotalCredit, arg.Reference, arg.Description, arg.StoreID, ) if err != nil { return 0, err } return result.RowsAffected(), nil }