[FEATURE] - fix list-trial-balance and export-trial-balance API

[arealclimber]

Summary

[功能] - 修正清單試算表和匯出試算表 API

• ref
  • [FEATURE] - 計算試算表的正確金額 #3768

Tasks

wiki

• list trial balance GET /company/:companyId/trial_balance
• expor trial balance POST /company/:companyId/trial_balance/export

list trial balance bugs

debug 的標的

http://localhost:3000/api/v2/company/10000007/trial_balance?sortOption=BeginningCreditAmount:desc&startDate=1&endDate=1791524471

1. 借貸沒有金額相同
2. 損益表科目的期初餘額為 0 (損益表科目的 begin credit/debit amount 為 0 ，不需開始日期以前的 line item)；期末餘額為開始日期到結束日期的加總
3. 父層沒有等於子層的加總

export trial balance bugs

1. 少了科目編號
2. 借貸反邊了，正確應該如圖二

Dependencies

No response

Other Dependencies

No response

Additional Notes

No response

[arealclimber]

bug record

trial balance API

request

http://localhost:3000/api/v2/company/10000007/trial_balance?sortOption=BeginningCreditAmount:desc&startDate=1&endDate=1791524471

response

{
    "powerby": "iSunFA v0.8.5+217",
    "success": true,
    "code": "200ISF0001",
    "message": "List successfully",
    "payload": {
        "currencyAlias": "TWD",
        "items": {
            "data": [
                {
                    "id": 1601,
                    "no": "1101",
                    "accountingTitle": "庫存現金",
                    "beginningCreditAmount": 0,
                    "beginningDebitAmount": 0,
                    "midtermCreditAmount": 10220,
                    "midtermDebitAmount": 2011676,
                    "endingCreditAmount": 10220,
                    "endingDebitAmount": 2011676,
                    "createAt": 0,
                    "updateAt": 0,
                    "subAccounts": [
                        {
                            "id": 10001469,
                            "no": "1101-3",
                            "accountingTitle": "庫存現金-銀行存款_玉山",
                            "beginningCreditAmount": 0,
                            "beginningDebitAmount": 0,
                            "midtermCreditAmount": 110,
                            "midtermDebitAmount": 1000110,
                            "endingCreditAmount": 110,
                            "endingDebitAmount": 1000110,
                            "createAt": 0,
                            "updateAt": 0,
                            "subAccounts": []
                        }
                    ]
                },
                {
                    "id": 1602,
                    "no": "1102",
                    "accountingTitle": "零用金／週轉金",
                    "beginningCreditAmount": 0,
                    "beginningDebitAmount": 0,
                    "midtermCreditAmount": 0,
                    "midtermDebitAmount": 100,
                    "endingCreditAmount": 0,
                    "endingDebitAmount": 100,
                    "createAt": 0,
                    "updateAt": 0,
                    "subAccounts": []
                },
                {
                    "id": 1603,
                    "no": "1103",
                    "accountingTitle": "銀行存款",
                    "beginningCreditAmount": 0,
                    "beginningDebitAmount": 0,
                    "midtermCreditAmount": 10100,
                    "midtermDebitAmount": 20000,
                    "endingCreditAmount": 10100,
                    "endingDebitAmount": 20000,
                    "createAt": 0,
                    "updateAt": 0,
                    "subAccounts": []
                },
                {
                    "id": 1604,
                    "no": "1105",
                    "accountingTitle": "在途現金",
                    "beginningCreditAmount": 0,
                    "beginningDebitAmount": 0,
                    "midtermCreditAmount": 0,
                    "midtermDebitAmount": 1230,
                    "endingCreditAmount": 0,
                    "endingDebitAmount": 1230,
                    "createAt": 0,
                    "updateAt": 0,
                    "subAccounts": []
                },
                {
                    "id": 1605,
                    "no": "1107",
                    "accountingTitle": "約當現金",
                    "beginningCreditAmount": 0,
                    "beginningDebitAmount": 0,
                    "midtermCreditAmount": 0,
                    "midtermDebitAmount": 100,
                    "endingCreditAmount": 0,
                    "endingDebitAmount": 100,
                    "createAt": 0,
                    "updateAt": 0,
                    "subAccounts": []
                },
                {
                    "id": 1606,
                    "no": "1111",
                    "accountingTitle": "指定透過損益按公允價值衡量之金融資產－流動",
                    "beginningCreditAmount": 0,
                    "beginningDebitAmount": 0,
                    "midtermCreditAmount": 2463,
                    "midtermDebitAmount": 0,
                    "endingCreditAmount": 2463,
                    "endingDebitAmount": 0,
                    "createAt": 0,
                    "updateAt": 0,
                    "subAccounts": []
                },
                {
                    "id": 1607,
                    "no": "1112",
                    "accountingTitle": "指定透過損益按公允價值衡量之金融資產評價調整－流動",
                    "beginningCreditAmount": 0,
                    "beginningDebitAmount": 0,
                    "midtermCreditAmount": 120000,
                    "midtermDebitAmount": 0,
                    "endingCreditAmount": 120000,
                    "endingDebitAmount": 0,
                    "createAt": 0,
                    "updateAt": 0,
                    "subAccounts": [
                        {
                            "id": 10001484,
                            "no": "1112-1",
                            "accountingTitle": "指定透過損益按公允價值衡量之金融資產評價調整－流動-庫存現金-銀行存款_123",
                            "beginningCreditAmount": 0,
                            "beginningDebitAmount": 0,
                            "midtermCreditAmount": 0,
                            "midtermDebitAmount": 0,
                            "endingCreditAmount": 0,
                            "endingDebitAmount": 0,
                            "createAt": 0,
                            "updateAt": 0,
                            "subAccounts": []
                        }
                    ]
                },
                {
                    "id": 1608,
                    "no": "1113",
                    "accountingTitle": "強制透過損益按公允價值衡量之金融資產－流動",
                    "beginningCreditAmount": 0,
                    "beginningDebitAmount": 0,
                    "midtermCreditAmount": 0,
                    "midtermDebitAmount": 10000,
                    "endingCreditAmount": 0,
                    "endingDebitAmount": 10000,
                    "createAt": 0,
                    "updateAt": 0,
                    "subAccounts": []
                },
                {
                    "id": 1609,
                    "no": "1114",
                    "accountingTitle": "強制透過損益按公允價值衡量之金融資產評價調整－流動",
                    "beginningCreditAmount": 0,
                    "beginningDebitAmount": 0,
                    "midtermCreditAmount": 100,
                    "midtermDebitAmount": 0,
                    "endingCreditAmount": 100,
                    "endingDebitAmount": 0,
                    "createAt": 0,
                    "updateAt": 0,
                    "subAccounts": []
                },
                {
                    "id": 1618,
                    "no": "1151",
                    "accountingTitle": "應收票據",
                    "beginningCreditAmount": 0,
                    "beginningDebitAmount": 0,
                    "midtermCreditAmount": 0,
                    "midtermDebitAmount": 120000,
                    "endingCreditAmount": 0,
                    "endingDebitAmount": 120000,
                    "createAt": 0,
                    "updateAt": 0,
                    "subAccounts": []
                }
            ],
            "page": 1,
            "totalPages": 3,
            "totalCount": 28,
            "pageSize": 10,
            "hasNextPage": true,
            "hasPreviousPage": false,
            "sort": [
                {
                    "sortBy": "BeginningCreditAmount",
                    "sortOrder": "desc"
                }
            ]
        },
        "total": {
            "beginningCreditAmount": 0,
            "beginningDebitAmount": 0,
            "midtermCreditAmount": 1313406,
            "midtermDebitAmount": 2313406,
            "endingCreditAmount": 1313406,
            "endingDebitAmount": 2313406,
            "createAt": 0,
            "updateAt": 0
        }
    }
}

[arealclimber]

在 accountBook.toJSON() 出現重複的會計科目導致重複加總進而造成 trial balance 借貸金額不一致

排查建立 account tree 時確認沒有重複的會計科目，可能是 account table 的 parent id 有重複？ 需找其他原因造成 accountBook.toJSON() 重複

=> 先用 list ledger API 回傳的資料去算 29 個有值的會計科目在 list trial balance 的答案之後請教 Anna 幫忙驗算，然後再去比對 accountBook.json() 產出的數字，建立哪個地方可能寫錯的假設，再去一一排查

http://localhost:3000/api/v2/company/10000007/trial_balance?startDate=1704038400&endDate=1735142399&page=1&pageSize=99999

• ledger response data

ledger_1735123245937.csv

[arealclimber]

endingAccountBook_17_40.json
endingAccountBookJSON_17_40.json

[arealclimber]

took 6 hrs
remaining 6 hrs

[arealclimber]

took 6 hrs

• 找不到現在 list trial balance 加總是在哪個環節篩掉自訂子科目的，先擱著，預計之後刪掉重寫

remaining n hrs