Financial accounting table

josecnp1 · Post by **josecnp1** » Tue Dec 16, 2014 8:20 am

I created this function that works well.

// ListOptions Load event
function ListOptions_Load() {
$opt = &$this->ListOptions->Add("saldo");
$opt->Header = "Saldo";
}

// ListOptions Rendered event
function ListOptions_Rendered() {
$totaldebe = ew_ExecuteScalar("SELECT SUM(economia.debe) FROM economia");
$totalhaber = ew_ExecuteScalar("SELECT SUM(economia.haber) FROM economia");
$GLOBALS["saldototal"] = $totaldebe - $totalhaber;
$this->ListOptions->Items["saldo"]->Footer ="Total Saldo: " . $GLOBALS["saldototal"];
}

and having me these results. for example.

id | debe | haber | saldo
1 | 10 | 0 |
2 | 0 | 10 |
3 | 10 | 0 |
4 | 10 | 0 |
Total:30 Total:10 Total Saldo: 20

I would like to create a function that calculates the balance and type in each row.
and all balances will be updated if there is any modification of any row.

id | debe | haber | saldo
1 | 10 | 0 | 10
2 | 0 | 10 | 0
3 | 10 | 0 | 10
4 | 10 | 0 | 20
Total:30 Total:10

some member of the community has done something similar?
appreciate any idea. thank you.

mobhar · Post by **mobhar** » Tue Dec 16, 2014 9:10 am

Post your table schema (CREATE TABLE statement) including some records in it (INSERT INTO statement).

danielc · Post by **danielc** » Tue Dec 16, 2014 10:01 am

You may take a look for this topic, http://www.hkvforums.com/viewtopic.php?f=4&t=35979.

josecnp1 · Post by **josecnp1** » Tue Dec 16, 2014 8:46 pm

Hello, I found this information what I need.

I have adapted to my table and it works.

SELECT balanceCalculation.id, balanceCalculation.debe, balanceCalculation.haber, balanceCalculation.saldo FROM ( SELECT economia.id, economia.debe, economia.haber, @saldo := @saldo + economia.debe - economia.haber AS saldo FROM economia, (SELECT @saldo := 0) AS variableInit ORDER BY economia.id ASC ) AS balanceCalculation ORDER BY balanceCalculation.id DESC

but when I put in the function, all rows gives me the same value.

// ListOptions Load event
function ListOptions_Load() {
$opt = &$this->ListOptions->Add("saldo");
$opt->Header = "Saldo";  
}

// ListOptions Rendered event
function ListOptions_Rendered() {
$total = ew_ExecuteScalar("SELECT `balanceCalculation`.`id`, `balanceCalculation`.`debe`, `balanceCalculation`.`haber`, `balanceCalculation`.`saldo` FROM ( SELECT `economia`.`id`, `economia`.`debe`, `economia`.`haber`, @saldo := @saldo + `economia`.`debe` - `economia`.`haber` AS `saldo` FROM `economia`, (SELECT @saldo := 0) AS variableInit ORDER BY `economia`.`id` ASC ) AS `balanceCalculation` ORDER BY `balanceCalculation`.`id` DESC");
$this->ListOptions->Items["saldo"]->Body =$total;
}

my table schema is:

CREATE TABLE IF NOT EXISTS economia (
id int(11) NOT NULL,
dato varchar(255) DEFAULT NULL,
debe decimal(10,2) DEFAULT NULL,
haber decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

INSERT INTO economia (id, dato, debe, haber) VALUES
(28, NULL, '10.00', '0.00'),
(29, NULL, '10.00', '0.00'),
(30, NULL, '0.00', '10.00'),
(31, NULL, '0.00', '10.00'),
(32, NULL, '10.00', '0.00');

ALTER TABLE economia
ADD PRIMARY KEY (id);

josecnp1 · Post by **josecnp1** » Wed Dec 17, 2014 12:27 am

this mysql query shows all the results of balance:

SELECT (select sum(debe) - sum(haber) from economia m2 where m2.id <= m.id) as saldo FROM economia m ORDER BY m.id DESC;

but I still see the same end result in all fields

function ListOptions_Rendered() {
$total = ew_ExecuteScalar("SELECT (select sum(debe) - sum(haber) from economia m2 where m2.id <= m.id) as saldo FROM economia m ORDER BY m.id DESC;");
$this->ListOptions->Items["saldo"]->Body =$total;
}

josecnp1 · Post by **josecnp1** » Wed Dec 17, 2014 1:34 am

solved, just missing me set the condition "where"