book of account CCv5

admin_wiky 17 Aug, 2014
Hi all,

I have a specific example.
I need to store an book of account to table.
it is easy, every day I have to store some values to columns (some are as income and some as expense) and one column will be a saldo.
of course I have to count with saldo of previous day (add up income to saldo and substract an expense from saldo of previous day) - is is easy too.
but, I if I notice that I have a mistake 5 days ago and I repair the record of this day, them the saldo will not correspond with reality of next days (because previous day was repaired).
How to re-count the saldo of next days?
thx..
GreyHead 18 Aug, 2014
Hi homeopat,

First I'd recommend that you don't so book-keeping in this way! Look at using a proper accounting system (I like Xero).

Second, don't keep a calculated balance in the table - you can never be sure that it is up to date. Record the individual transactions and calculate the balance when you need it.

If you *must* do it this way then add a MySQL query to your forms so that **every** time a record is changed, all the 'saldo' calculations are re-done.

Bob FCMA MCT
admin_wiky 18 Aug, 2014
Hi Bob,
thx for reply.

it will not be a full accounting system, so no need to use a more sophisticated solution, but thanks for the tip.
we only want to record daily turnover (cash, credit card payments, payments to the bank, other expenses) and of course also need to record the final balance due to the previous days. Do you understand me?
example:
Day 1: I received 100 and so balance is 100
Day 2: I received 500 and went out 200 so 100 + 500 - 200 = 400 balance
and so on.
It would be complicated to calculate the balance of the previous income (eg. 3 months back) and the expenditure of everyday writing than if I found the balance of the previous day from the table and he attributed the current income and expenditure.

But I understand you how do you mean it.

thx
GreyHead 19 Aug, 2014
Hi homepat,

It would be complicated to calculate the balance of the previous income (eg. 3 months back) and the expenditure of everyday writing than if I found the balance of the previous day from the table and he attributed the current income and expenditure.

True - but you have absolutely no way of knowing if the previous days' balance is correct. Don't do it like this :-(

Bob
admin_wiky 20 Aug, 2014
I figure out counting of saldo.
php functions:

model.column: if (!isset($saldo)) {global $saldo,$plus,$minus};$plus="some columns ...";$minus="some columns ...";$saldo=$saldo+$plus-$minus;return $saldo;

Thats works ... If I use ascending order. But I need descending order and then saldo isnt descending but ascending.
any idea? I need show the newest data at top, so on the top have to be actually saldo.
thx
GreyHead 21 Aug, 2014
Hi homeopat,

You might be able to get the final saldo from a DB query and then work your code backwards to calculate the earlier saldos??

Bob
admin_wiky 21 Aug, 2014
yes, but how backwards? I am not sure, how to do it in table display type, because table is generated from top to down (forwards)
I think that I cannot use a display type:Table but custom code in this case?

thank you
GreyHead 21 Aug, 2014
Hi Homeopat,

If you get the final saldo from a DB query then you can work out the previous ones by deducting income and adding expenses from each following row.

Or, as you suggest you could use PHP to load the data and process is before displaying.

Bob
admin_wiky 21 Aug, 2014
I understand, but Can I do this by table display mode .. put this code to php functions section?
GreyHead 22 Aug, 2014
Hi Homeopat,

I think so but I don't know much about how CCv5 works yet.

Bob
Max_admin 22 Aug, 2014
Hi,

but Can I do this by table display mode .. put this code to php functions section?



I think so yes, did you try it ? the code must be in one line.

The way CCv5 works is that, you define some fields (or aliases) to be listed under the "Columns list" box, then define the outputs of these fields in the other boxes config, then you can use your field (alias) in the custom mode to display the output, or just use table mode to have them automatically formatted for you!

Regards,
Max
Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.
admin_wiky 25 Aug, 2014
Hi Max,

I have In column list:
zakladni.saldo: Saldo

in php functions
zakladni.saldo: if (!isset($saldo)) {global $saldo;global $prijmy; global $vydaje;} $prijmy=$row['zakladni']['hotovost_kc']+$row['zakladni']['hotovost_euro']*$row['zakladni']['kurz_euro']+$row['zakladni']['platebni_karty']+$row['zakladni']['poukazy_p'];$vydaje=$row['zakladni']['poukazy_v']+$row['zakladni']['drobna_vydani']+$row['zakladni']['mzdy']+$row['zakladni']['postovne']+$row['zakladni']['cestovne']+$row['zakladni']['odvod_kb'];$saldo=$saldo+$prijmy-$vydaje;return $saldo;


this works fine, when ascending order. I am not sure how php functions work, I think that zakladni.saldo row in php functions is called for every record of the table separately. So I am not sure how to get final saldo at first place and then this saldo print on page by decreasing of saldo of the day from final saldo.
maybe picture help to understand ... I need saldo order same way as data in table
[attachment=0]ScreenShot601.jpg[/attachment]

thx
Max_admin 21 Sep, 2014
Hi,

Did you manage to solve this ? I just can't understand what you are trying to do here, the php function will do some processing on the cell value, if you try to do ordering then use the order box!

Regards,
Max
Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.
admin_wiky 22 Sep, 2014
Hi Max,

No, I didnt.
If u take a look at picture, data are ordered by date DESC (I am using order box) and I have problem with column saldo. this values are counted with php functions.
saldo = saldo of previous day + incomes - expenses

problem is in order of saldo values (take a look at picture. value 12295 should be for date 20.7.2014. not 15.7.2014.
So I dont know how change order in same direction like a date
Max_admin 23 Sep, 2014
And how do you calculate the previous day saldo ? the functions are processed per row, and since you have them ordered by date DESC, then you will always have the next day processed first.
Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.
admin_wiky 23 Sep, 2014
code of saldo is

    global $saldo;
    $saldo = $saldo  + $incomes - $expenses;

and it works... but if I change date order from asc to desc, then order of date is changed, but order of saldo didnt
is there some way how set it up?
GreyHead 23 Sep, 2014
Hi homeopat,

Then you will need to change the logic so that you can still start at the top of the list and work down - this will presumably require an extra database query to get the saldo at the beginning of the page.

Bob
admin_wiky 24 Sep, 2014
Hi Bob,
thats funny, I know, but how?🙂 I dont know, how set up it in CC🙂
GreyHead 24 Sep, 2014
1 Likes
Hi homeopat,

I don't know how to do it either but I think it's probably can be done with some thought.

Bob
Max_admin 28 Sep, 2014

this will presumably require an extra database query to get the saldo at the beginning of the page.



I think that you can loop and modify the data in the $rows variable in the header section, but only the $rows of the current page are accessible, so if you need rows data from other pages then you will need a new query!

Regards,
Max
Max, ChronoForms developer
ChronoMyAdmin: Database administration within Joomla, no phpMyAdmin needed.
ChronoMails simplifies Joomla email: newsletters, logging, and custom templates.
This topic is locked and no more replies can be posted.