Skip to forum content
FrontAccounting forum
It's much more fun, when you can discuss your problems with others...
You are not logged in. Please login or register.
Active topics Unanswered topics
Search options
Hi all again,
Unfortunately i couldn't solve this problem yet. I modified sql to find invoices, payments and credit notes which allocated in year to delete.
$sql = "SELECT trans_no, type
FROM ".TB_PREF."debtor_trans
WHERE tran_date <= '$to'
AND (round((ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount), 3) = round((SELECT SUM(amt) FROM ".TB_PREF."cust_allocations WHERE date_alloc <= '$to' AND trans_no = trans_no_to AND type = trans_type_to), 3)
OR round((ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount), 3) = round((SELECT SUM(amt) FROM ".TB_PREF."cust_allocations WHERE date_alloc <= '$to' AND trans_no = trans_no_from AND type = trans_type_from), 3))";
But it's not enough to find right transactions to delete. Still I have to separate some invoices and payment.
So I have an invoice (1800$) invoiced in 2015 and i have a payment (800$) paid in 2015. This payment is fully allocated to this invoice but remaining balance of this invoice (1000$) paid in 2016.
This sql find 800$ payment to delete because this payment is fully allocated in 2015 but invoice is still alive because it's not fully allocated in 2015. I have to keep this payment to find right balance at the end of 2015.
How can i do this? Hope i could explain the problem which i couldn't solve.
Thanks in advance.
This fix only delete allocation. Payment must be deleted too.
$sql = "SELECT trans_no, type FROM ".TB_PREF."debtor_trans WHERE tran_date <= '$to' AND
(ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) =
(SELECT SUM(amt) FROM ".TB_PREF."cust_allocations WHERE date_alloc <= '$to' AND trans_no = trans_no_to AND
type = trans_type_to)";
This sql can not find the payment to delete.
After deletion year 2014 customer has records in debtor_trans table. For example;
debtor_trans
trans_no: 15496
type: 12
tran_date: 2014-09-29
ov_amount: 785
alloc: 785
cust_allocations
amt: 335
date_alloc: 2014-09-29
trans_no_from: 15496
trans_type_from: 12
trans_no_to: 14931
trans_type_to: 10
amt: 450
date_alloc: 2014-09-29
trans_no_from: 15496
trans_type_from: 12
trans_no_to: 15472
trans_type_to: 10
But 15472, 14931 transactions do not exist in debtor_trans. Why this payment didn't deleted? whereas it payed in 2014 and allocated to invoices in 2014.
By the way invoices are still alive which are processed in 2014 and allocated in 2015. This part is working correct i think but payments part is not.
I have compatibility problem. I 'm using 2.3.26 but commited file compatible with 2.4.x.
2.3.26 neither has add_journal method nor 0_journal table.
I removed this part of code and tried to delete year but unfortunately customer balance calculated wrong.
I'm investigating why balance is wrong. I'll inform you more if i find something.
@joe: Thanks for solution. I have been working on it for a few days. Your solution is ver simple and i 'll try asap. Will it work for payed before and invoiced next year transactions?
Yes you're right. Thanks for the idea 
I'll inform you when finished.
One more thing to do. The allocation of old entries will be deleted so i have to make new allocation for opening balance and unallocated payments.
I'll try your advice. If i can create journal entry for opening balance to every customer (or supplier) who has balance at end of year, i think problem is solved. For without timeout limitation, cli script is best in my opinion. i will need it once a year anyway.
@Braath Waate
Thank you for timeout solution. CLI script exactly same as handle_delete method in fiscalyears.php. I'am using delete_this_fiscalyear method in fiscalyears_db.inc (I wrote wrong in first message)
@kvvaradha
Thank you for your answer. It's hard to determine this kind of invoices. Below you can see total invoices i have year by year. I have to write another script for this therefore study db structure and relation of records more.
2014 - 21511
2015 - 40067
2016 - 64865
2017 - 98377
2018 - 114123
2019 - 152206
Hello,
i am using frontaccounting since 2014 and i did not delete any records. Now i have some problems. Because i have too many records in db. For example gl_trans table has 5.5millions of records. Now i decided to delete fiscal years but it cannot possible to delete from frontaccounting user interface becouse of timeout limitations. I wrote cli script with using delete_this_fiscalyear method in mainteince_db.inc. I can close a fiscal year and then i can delete this fiscal year.
After deletion, customer blances has been changed. I investigated what is the problem. I found something but i don't know how to solve this problem exactly.
If i delete 2014 records and customer has invoice in this year but it has paid in 2015, total customer balance is changing. As if the customer paid more than its debt but the payment belongs to invoice in 2014. I hope i could explain clearly.
Now i'm using 2.3.26 version and I can not upgrade to 2.4.x becacuse i have lot of records. Upgrade process takes very very long time like infinite.
I have to delete previous fiscal years but i cant.
I am waiting your reply.
Thanks in advance.
Posts found: 10
