- qq|SELECT SUM(ac.amount) | .
- qq|FROM acc_trans ac | .
- qq|JOIN chart c ON (ac.chart_id = c.id) | .
- $dpt_join .
- qq|WHERE c.accno = ? | .
- qq|AND ac.transdate < ? | .
- $dpt_where .
- $project;
- @values = ($form->{accno}, conv_date($form->{fromdate}),
- @department_values, @project_values);
-
- if ($form->{project_id}) {
- $query .=
- qq|UNION | .
-
- qq|SELECT SUM(ac.qty * ac.sellprice) | .
- qq|FROM invoice ac | .
- qq|JOIN ar a ON (ac.trans_id = a.id) | .
- qq|JOIN parts p ON (ac.parts_id = p.id) | .
- qq|JOIN chart c ON (p.income_accno_id = c.id) | .
- $dpt_join .
- qq|WHERE c.accno = ? | .
- qq| AND a.transdate < ? | .
- qq| AND c.category = 'I' | .
- $dpt_where .
- $project .
-
- qq|UNION | .
-
- qq|SELECT SUM(ac.qty * ac.sellprice) | .
- qq|FROM invoice ac | .
- qq|JOIN ap a ON (ac.trans_id = a.id) | .
- qq|JOIN parts p ON (ac.parts_id = p.id) | .
- qq|JOIN chart c ON (p.expense_accno_id = c.id) | .
- $dpt_join .
- qq|WHERE c.accno = ? | .
- qq| AND a.transdate < ? | .
- qq| AND c.category = 'E' | .
- $dpt_where .
- $project;
-
- push(@values,
- $form->{accno}, conv_date($form->{transdate}),
- @department_values, @project_values,
- $form->{accno}, conv_date($form->{transdate}),
- @department_values, @project_values);
- }
-
- ($form->{balance}) = selectrow_query($form, $dbh, $query, @values);
+ 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});