search

ledgersmb / LedgerSMB

Double-entry accounting & ERP for the web
https://ledgersmb.org
Other
415 stars 150 forks source link

Export Chart of Accounts #7853

Open howardlowndes opened 9 months ago

howardlowndes commented 9 months ago

Version

1.11.6

What browsers are you seeing the problem on?

This problem isn't browser related

What happened?

Under General Ledger there is an option Import Chart. Selecting this presents:

The following fields are expected (in this order): accno description charttype category contra tax link heading gifi_accno

The understanding here is that the format should be CSV with items in the "link" field being NewLine separated. This assumption is derived from studying the CSV output from General Ledger->Chart of Accounts (selection attached):

howard@raspi-office:~ $ head -n20 Downloads/Chart_of_Accounts.csv "Account Number","Description","GIFI","Debits","Credits","Dropdowns","Delete" 1,"ASSETS",,0.00,0.00,, 1.10,"HUME BANK",,0.00,0.00,, 1.10.10,"Business Transaction",,0.00,0.00,"AR_paid AP_paid","[Delete]" 1.10.20,"Community Link",,0.00,0.00,,"[Delete]" 1.10.30,"Term Deposit",,0.00,0.00,,"[Delete]" 1.20,"NON-BANK",,0.00,0.00,, 1.20.10,"Merchant Card",,0.00,0.00,"AR_paid","[Delete]" 1.20.20,"Petty Cash",,0.00,0.00,"AP_paid","[Delete]" 1.20.30,"Cash in Hand",,0.00,0.00,"AR_paid","[Delete]" 1.20.40,"GST Input (Collectable)",,0.00,0.00,"AP_tax IC_taxpart IC_taxservice","[Delete]" 1.30,"ACCOUNTS RECEIVABLE",,0.00,0.00,, 1.30.10,"Sundry Debtors",,0.00,0.00,"AR","[Delete]" 1.30.20,"Debtor Payments",,0.00,0.00,"AR_paid","[Delete]" 1.30.30,"Provision for Doubtful Debts",,0.00,0.00,"AR_overpayment","[Delete]" 1.40,"PLANT & EQUIPMENT",,0.00,0.00,, 1.40.10,"Plant & Equipment @ Valuation",,0.00,0.00,"Fixed_Asset","[Delete]" howard@raspi-office:~ $

It makes sense to this user, who is trying to develop a CoA, that this output format could be developed into a Export Chart option with obvious benefits to the SMB user.

What should have happened?

Similar remarks might be made about the Import options under Goods & Services being developed from the Search function there, though this might be more complex.

ehuelsmann commented 9 months ago

I think the query you're looking for is:

select accno, description, 'H' as charttype, null as category, null as contra, null as tax, null as link, (select accno from account_heading h2 where h2.id = h.parent_id) as heading, null as gifi_accno
from account_heading h
union all
select accno, description, 'A', category, contra, tax, (select string_agg(description, ':') from account_link where account_link.account_id = a.id) as link, (select accno from account_heading h where h.id = a.heading), gifi_accno
from account a
ehuelsmann commented 9 months ago

My idea is not to allow exporting of the CoA the way you describe, but to export it in a format that can be imported during company creation/setup so they can be exported and donated to the project, similar to the ones you can find here: https://github.com/ledgersmb/LedgerSMB/tree/master/locale/coa

howardlowndes commented 9 months ago

I agree that the XML route is better for the project. I was wondering whether there was a "quick and dirty" method - probably not a good idea in light of the tight intergration of the database.

howardlowndes commented 8 months ago

Thanks for the SQL, it works a treat, I just added an 'order by accno' and derived the attached. I'm still not happy with the links that I have defined, nor am I too certain about some of the esoterics, such as tax, depreciation, contra. BTW, how is the Negative balance heading supposed to be used when creating an account entry?

On Sun, 24 Dec 2023 at 10:15, Erik Huelsmann @.***> wrote:

My idea is not to allow exporting of the CoA the way you describe, but to export it in a format that can be imported during company creation/setup so they can be exported and donated to the project, similar to the ones you can find here: https://github.com/ledgersmb/LedgerSMB/tree/master/locale/coa

— Reply to this email directly, view it on GitHub https://github.com/ledgersmb/LedgerSMB/issues/7853#issuecomment-1868387356, or unsubscribe https://github.com/notifications/unsubscribe-auth/AMS2F7W54E3GYFD7L63VCLTYK5Q2HAVCNFSM6AAAAABAZA62NCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNRYGM4DOMZVGY . You are receiving this because you authored the thread.Message ID: @.***>

-- Howard. -- When you want a computer system that works, just choose Linux; When you want a computer system that works, just, choose Microsoft.

accno     |          description           | charttype | category | contra | tax |              link               |  heading  | gifi_accno

--------------+--------------------------------+-----------+----------+--------+-----+---------------------------------+-----------+------------ 0 | BALANCE SHEET | H | | | | | | 0.1 | ASSETS | H | | | | | 0 | 0.1.10 | HUME BANK | H | | | | | 0.1 | 0.1.10.10 | Business Transaction | A | A | f | f | AR_paid:AP_paid | 0.1.10 | 0.1.10.20 | Community Link | A | A | f | f | | 0.1.10 | 0.1.10.30 | Term Deposit | A | A | f | f | | 0.1.10 | 0.1.20 | NON-BANK | H | | | | | 0.1 | 0.1.20.10 | Merchant Card | A | A | f | f | AR_paid | 0.1.20 | 0.1.20.20 | Petty Cash | A | A | f | f | AP_paid | 0.1.20 | 0.1.20.30 | Cash in Hand | A | A | f | f | AR_paid | 0.1.20 | 0.1.20.40 | GST Input (Collectable) | A | A | f | t | AP_tax:IC_taxpart:IC_taxservice | 0.1.20 | 0.1.30 | ACCOUNTS RECEIVABLE | H | | | | | 0.1 | 0.1.30.10 | Sundry Debtors | A | A | f | f | AR | 0.1.30 | 0.1.30.90 | Provision for Doubtful Debtors | A | A | t | f | AR_overpayment | 0.1.30 | 0.1.40 | PLANT & EQUIPMENT | H | | | | | 0.1 | 0.1.40.10 | Plant & Equipment @ Valuation | A | A | f | f | Fixed_Asset | 0.1.40 | 0.1.40.20 | Plant & Equipment Depreciation | A | A | t | f | Asset_Dep:asset_expense | 0.1.40 | 0.1.50 | OFFICE EQUIPMENT | H | | | | | 0.1 | 0.1.50.10 | Office Equipment @ Valuation | A | A | f | f | Fixed_Asset | 0.1.50 | 0.1.50.20 | Office Equipment Depreciation | A | A | t | f | Asset_Dep:asset_expense | 0.1.50 | 0.1.60 | INVENTORY | H | | | | | 0.1 | 0.1.60.10 | Resale Items | A | A | f | f | IC | 0.1.60 | 0.2 | LIABILITIES | H | | | | | 0 | 0.2.20 | GOODS & SERVICES TAX | H | | | | | 0.2 | 0.2.20.40 | GST Output (Remitable) | A | L | f | t | AR_tax:IC_taxpart:IC_taxservice | 0.2.20 | 0.2.30 | ACCOUNTS PAYABLE | H | | | | | 0.2 | 0.2.30.10 | Sundry Creditors | A | L | f | f | AP | 0.2.30 | 0.3 | EQUITY | H | | | | | 0 | 0.3.10 | Opening Equity | A | Q | f | f | | 0.3 | 0.3.20 | Retained Profit or Loss | A | Q | f | f | | 0.3 | 1 | TRADING STATEMENT | H | | | | | | 1.4 | INCOME | H | | | | | 1 | 1.4.10 | INTEREST | H | | | | | 1.4 | 1.4.10.10 | Business Transaction | A | I | f | f | AR_amount | 1.4.10 | 1.4.10.20 | Community Link | A | I | f | f | AR_amount | 1.4.10 | 1.4.10.30 | Term Deposit | A | I | f | f | AR_amount | 1.4.10 | 1.4.20 | MEMBERSHIPS | H | | | | | 1.4 | 1.4.20.10 | Memberships | A | I | f | f | AR_amount:IC_income | 1.4.20 | 1.4.30 | DONATIONS | H | | | | | 1.4 | 1.4.30.10 | Donations - Coffee Box | A | I | f | f | AR_amount:IC_income | 1.4.30 | 1.4.30.20 | Donations - Services Rendered | A | I | f | f | AR_amount:IC_income | 1.4.30 | 1.4.30.30 | Donations - Unsolicited | A | I | f | f | AR_amount:IC_income | 1.4.30 | 1.4.40 | INVENTORY SALES | H | | | | | 1.4 | 1.4.40.10 | Inventory Sales | A | I | f | f | AR_amount:IC_sale | 1.4.40 | 1.5 | EXPENSES | H | | | | | 1 | 1.5.10 | BANK FEES | H | | | | | 1.5 | 1.5.10.10 | Business Transaction | A | E | f | f | AP_amount | 1.5.10 | 1.5.10.20 | Community Link | A | E | f | f | AP_amount | 1.5.10 | 1.5.10.30 | Term Deposit | A | E | f | f | AP_amount | 1.5.10 | 1.5.20 | NON-BANK FEES | H | | | | | 1.5 | 1.5.20.10 | Merchant Card | A | E | f | f | AP_amount | 1.5.20 | 1.5.30 | DEPRECIATION | H | | | | | 1.5 | 1.5.30.40 | Plant & Equipment Depreciation | A | E | f | f | asset_expense | 1.5.30 | 1.5.30.50 | Office Equipment Depreciation | A | E | f | f | asset_expense | 1.5.30 | 1.5.40 | CONSUMABLES | H | | | | | 1.5 | 1.5.40.10 | PLANT & EQUIPMENT CONSUMABLES | H | | | | | 1.5.40 | 1.5.40.10.10 | Fuel | A | E | f | f | AP_amount | 1.5.40.10 | 1.5.40.10.20 | Adhesives | A | E | f | f | AP_amount | 1.5.40.10 | 1.5.40.10.30 | Abrasives | A | E | f | f | AP_amount | 1.5.40.10 | 1.5.40.10.50 | Plant & Equipment Sundries | A | E | f | f | AP_amount | 1.5.40.10 | 1.5.40.20 | OFFICE EQUIPMENT CONSUMABLES | H | | | | | 1.5.40 | 1.5.40.20.10 | Stationary | A | E | f | f | AP_amount | 1.5.40.20 | 1.5.40.20.50 | Office Sundries | A | E | f | f | AP_amount | 1.5.40.20 | 1.5.50 | MEMBER SERVICES | H | | | | | 1.5 | 1.5.50.10 | Member Insurance | A | E | f | f | AP_amount | 1.5.50 | 1.5.60 | INVENTORY | H | | | | | 1.5 | 1.5.60.10 | Cost of Goods Sold | A | E | f | f | IC_cogs | 1.5.60 | 1.5.60.20 | Cost of Service Delivery | A | E | f | f | IC_expense | 1.5.60 | 1.5.70 | UTILITIES | H | | | | | 1.5 | 1.5.70.10 | Water & Sewerage | A | E | f | f | AP_amount | 1.5.70 | 1.5.70.20 | Electricity | A | E | f | f | AP_amount | 1.5.70 | 1.5.70.30 | Gas | A | E | f | f | AP_amount | 1.5.70 | 1.5.70.40 | Internet | A | E | f | f | AP_amount | 1.5.70 | 1.5.70.50 | Waste Disposal | A | E | f | f | AP_amount | 1.5.70 | 1.5.80 | PREMISES | H | | | | | 1.5 | 1.5.80.10 | Premises Insurance | A | E | f | f | AP_amount | 1.5.80 | 1.5.80.20 | Rent | A | E | f | f | AP_amount | 1.5.80 | 1.5.80.30 | Rates | A | E | f | f | AP_amount | 1.5.80 | 1.5.80.40 | Security | A | E | f | f | AP_amount | 1.5.80 | 1.5.90 | BAD DEBTS | H | | | | | 1.5 | 1.5.90.10 | Provision for Doubtful Debtors | A | E | f | f | AR_overpayment | 1.5.90 | (81 rows)

ehuelsmann commented 8 months ago

The "Negative balance heading" setting is described here: https://book.ledgersmb.org/dev/full-book/#Ch18.S3.SS3 ; so are Tax and Contra (scroll a bit down on that page in the same section).

Not sure what you'd like to know about depreciation, but there is some documentation in the subsection 'Fixed assets' in the section linked in the previous paragraph (just befor the section "Special accounts").