-
- # get beginning balance
- $query = qq|SELECT SUM(ac.amount)
- FROM acc_trans ac
- JOIN chart c ON (ac.chart_id = c.id)
- $dpt_join
- WHERE c.accno = '$form->{accno}'
- AND ac.transdate < '$form->{fromdate}'
- $dpt_where
- $project
- |;
-
- if ($form->{project_id}) {
-
- $query .= qq|
-
- UNION
-
- SELECT SUM(ac.qty * ac.sellprice)
- FROM invoice ac
- JOIN ar a ON (ac.trans_id = a.id)
- JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c ON (p.income_accno_id = c.id)
- $dpt_join
- WHERE c.accno = '$form->{accno}'
- AND a.transdate < '$form->{fromdate}'
- AND c.category = 'I'
- $dpt_where
- $project
-
- UNION
-
- SELECT SUM(ac.qty * ac.sellprice)
- FROM invoice ac
- JOIN ap a ON (ac.trans_id = a.id)
- JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c ON (p.expense_accno_id = c.id)
- $dpt_join
- WHERE c.accno = '$form->{accno}'
- AND a.transdate < '$form->{fromdate}'
- AND c.category = 'E'
- $dpt_where
- $project
- |;
-
- }
-
- if ($form->{accounttype} eq 'gifi') {
- $query = qq|SELECT SUM(ac.amount)
- FROM acc_trans ac
- JOIN chart c ON (ac.chart_id = c.id)
- $dpt_join
- WHERE c.gifi_accno = '$form->{gifi_accno}'
- AND ac.transdate < '$form->{fromdate}'
- $dpt_where
- $project
- |;
-
- if ($form->{project_id}) {
-
- $query .= qq|
-
- UNION
-
- SELECT SUM(ac.qty * ac.sellprice)
- FROM invoice ac
- JOIN ar a ON (ac.trans_id = a.id)
- JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c ON (p.income_accno_id = c.id)
- $dpt_join
- WHERE c.gifi_accno = '$form->{gifi_accno}'
- AND a.transdate < '$form->{fromdate}'
- AND c.category = 'I'
- $dpt_where
- $project
-
- UNION
-
- SELECT SUM(ac.qty * ac.sellprice)
- FROM invoice ac
- JOIN ap a ON (ac.trans_id = a.id)
- JOIN parts p ON (ac.parts_id = p.id)
- JOIN chart c ON (p.expense_accno_id = c.id)
- $dpt_join
- WHERE c.gifi_accno = '$form->{gifi_accno}'
- AND a.transdate < '$form->{fromdate}'
- AND c.category = 'E'
- $dpt_where
- $project
- |;
-
- }
- }
-
- $sth = $dbh->prepare($query);
-
- $sth->execute || $form->dberror($query);
- ($form->{balance}) = $sth->fetchrow_array;
- $sth->finish;
+ # get beginning balances
+ $query =
+ qq|SELECT SUM(ac.amount) AS amount
+ FROM acc_trans ac
+ JOIN chart c ON (ac.chart_id = c.id)
+ WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction
+ $project
+ AND c.accno = ?|;
+
+ ($form->{beginning_balance}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
+
+ # get last transaction date
+ my $todate = ($form->{todate}) ? " AND ac.transdate <= '$form->{todate}' " : "";
+ $query = qq|SELECT max(ac.transdate) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ?|;
+ ($form->{last_transaction}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
+
+ # get old saldo
+ $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
+ ($form->{saldo_old}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
+
+ #get old balance
+ $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
+ ($form->{old_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
+
+ $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
+ ($form->{old_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
+
+ # get current saldo
+ $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
+ $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
+ ($form->{saldo_new}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
+
+ #get current balance
+ $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
+ $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
+ ($form->{current_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
+
+ $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
+ $query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) >= (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
+ ($form->{current_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});