summaryrefslogtreecommitdiff
path: root/LedgerSMB/CA.pm
diff options
context:
space:
mode:
Diffstat (limited to 'LedgerSMB/CA.pm')
-rw-r--r--LedgerSMB/CA.pm596
1 files changed, 299 insertions, 297 deletions
diff --git a/LedgerSMB/CA.pm b/LedgerSMB/CA.pm
index 2c4ff105..b182e6d0 100644
--- a/LedgerSMB/CA.pm
+++ b/LedgerSMB/CA.pm
@@ -1,8 +1,8 @@
#=====================================================================
-# LedgerSMB
+# LedgerSMB
# Small Medium Business Accounting software
# http://www.ledgersmb.org/
-#
+#
# Copyright (C) 2006
# This work contains copyrighted information from a number of sources all used
@@ -32,199 +32,202 @@
#
#======================================================================
-
package CA;
-
sub all_accounts {
- my ($self, $myconfig, $form) = @_;
+ my ( $self, $myconfig, $form ) = @_;
- my $amount = ();
- # connect to database
- my $dbh = $form->{dbh};
+ my $amount = ();
- my $query = qq|
+ # connect to database
+ my $dbh = $form->{dbh};
+
+ my $query = qq|
SELECT accno, SUM(acc_trans.amount) AS amount
FROM chart, acc_trans
WHERE chart.id = acc_trans.chart_id
GROUP BY accno|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
+ $amount{ $ref->{accno} } = $ref->{amount};
+ }
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $amount{$ref->{accno}} = $ref->{amount}
- }
-
- $sth->finish;
+ $sth->finish;
- $query = qq|
+ $query = qq|
SELECT accno, description
FROM gifi|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- my $gifi = ();
+ my $gifi = ();
- while (my ($accno, $description) = $sth->fetchrow_array) {
- $gifi{$accno} = $description;
- }
+ while ( my ( $accno, $description ) = $sth->fetchrow_array ) {
+ $gifi{$accno} = $description;
+ }
- $sth->finish;
+ $sth->finish;
- $query = qq|
+ $query = qq|
SELECT c.id, c.accno, c.description, c.charttype,
c.gifi_accno, c.category, c.link
FROM chart c
ORDER BY accno|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
- $ca->{amount} = $amount{$ca->{accno}};
- $ca->{gifi_description} = $gifi{$ca->{gifi_accno}};
+ while ( my $ca = $sth->fetchrow_hashref(NAME_lc) ) {
+ $ca->{amount} = $amount{ $ca->{accno} };
+ $ca->{gifi_description} = $gifi{ $ca->{gifi_accno} };
- if ($ca->{amount} < 0) {
- $ca->{debit} = $ca->{amount} * -1;
- } else {
- $ca->{credit} = $ca->{amount};
- }
+ if ( $ca->{amount} < 0 ) {
+ $ca->{debit} = $ca->{amount} * -1;
+ }
+ else {
+ $ca->{credit} = $ca->{amount};
+ }
- push @{ $form->{CA} }, $ca;
- }
+ push @{ $form->{CA} }, $ca;
+ }
- $sth->finish;
- $dbh->commit;
+ $sth->finish;
+ $dbh->commit;
}
-
sub all_transactions {
- my ($self, $myconfig, $form) = @_;
+ my ( $self, $myconfig, $form ) = @_;
- # connect to database
- my $dbh = $form->{dbh};
+ # connect to database
+ my $dbh = $form->{dbh};
- # get chart_id
- my $query = qq|
+ # get chart_id
+ my $query = qq|
SELECT id
FROM chart
WHERE accno = ?|;
- my $accno = $form->{accno};
+ my $accno = $form->{accno};
- if ($form->{accounttype} eq 'gifi') {
- $query = qq|
+ if ( $form->{accounttype} eq 'gifi' ) {
+ $query = qq|
SELECT id
FROM chart
WHERE gifi_accno = ?|;
- $accno = $form->{gifi_accno};
- }
-
- my $sth = $dbh->prepare($query);
- $sth->execute($accno) || $form->dberror($query);
-
- my @id = ();
-
- while (my ($id) = $sth->fetchrow_array) {
- push @id, $id;
- }
-
- $sth->finish;
-
- my $fromdate_where;
- my $todate_where;
-
- ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
-
- my $fdate;
- if ($form->{fromdate}) {
- $fromdate_where = qq| AND ac.transdate >= ? |;
- $fdate = $form->{fromdate};
- }
- my $tdate;
- if ($form->{todate}) {
- $todate_where .= qq| AND ac.transdate <= ? |;
- $tdate = $form->{todate};
- }
-
-
- my $false = 'FALSE';
-
- # Oracle workaround, use ordinal positions
- my %ordinal = ( transdate => 4,
- reference => 2,
- description => 3 );
-
- my @a = qw(transdate reference description);
- my $sortorder = $form->sort_order(\@a, \%ordinal);
-
- my $null;
- my $department_id;
- my $dpt_where;
- my $dpt_join;
- my $union;
-
- ($null, $department_id) = split /--/, $form->{department};
-
- my $d_id;
- if ($department_id) {
- $dpt_join = qq| JOIN department t ON (t.id = a.department_id) |;
- $dpt_where = qq| AND t.id = ? |;
- $d_id = $department_id;
- }
-
-
- my $project;
- my $project_id;
- my $p_id;
- if ($form->{projectnumber}) {
- ($null, $project_id) = split /--/, $form->{projectnumber};
- $project = qq| AND ac.project_id = ? |;
- $p_id = $project_id;
- }
-
- @queryargs = ();
-
- if ($form->{accno} || $form->{gifi_accno}) {
- # get category for account
- $query = qq|
+ $accno = $form->{gifi_accno};
+ }
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute($accno) || $form->dberror($query);
+
+ my @id = ();
+
+ while ( my ($id) = $sth->fetchrow_array ) {
+ push @id, $id;
+ }
+
+ $sth->finish;
+
+ my $fromdate_where;
+ my $todate_where;
+
+ ( $form->{fromdate}, $form->{todate} ) =
+ $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
+ if $form->{year} && $form->{month};
+
+ my $fdate;
+ if ( $form->{fromdate} ) {
+ $fromdate_where = qq| AND ac.transdate >= ? |;
+ $fdate = $form->{fromdate};
+ }
+ my $tdate;
+ if ( $form->{todate} ) {
+ $todate_where .= qq| AND ac.transdate <= ? |;
+ $tdate = $form->{todate};
+ }
+
+ my $false = 'FALSE';
+
+ # Oracle workaround, use ordinal positions
+ my %ordinal = (
+ transdate => 4,
+ reference => 2,
+ description => 3
+ );
+
+ my @a = qw(transdate reference description);
+ my $sortorder = $form->sort_order( \@a, \%ordinal );
+
+ my $null;
+ my $department_id;
+ my $dpt_where;
+ my $dpt_join;
+ my $union;
+
+ ( $null, $department_id ) = split /--/, $form->{department};
+
+ my $d_id;
+ if ($department_id) {
+ $dpt_join = qq| JOIN department t ON (t.id = a.department_id) |;
+ $dpt_where = qq| AND t.id = ? |;
+ $d_id = $department_id;
+ }
+
+ my $project;
+ my $project_id;
+ my $p_id;
+ if ( $form->{projectnumber} ) {
+ ( $null, $project_id ) = split /--/, $form->{projectnumber};
+ $project = qq| AND ac.project_id = ? |;
+ $p_id = $project_id;
+ }
+
+ @queryargs = ();
+
+ if ( $form->{accno} || $form->{gifi_accno} ) {
+
+ # get category for account
+ $query = qq|
SELECT description, category, link, contra
FROM chart
WHERE accno = ?|;
- $accno = $form->{accno};
- if ($form->{accounttype} eq 'gifi') {
- $query = qq|
+ $accno = $form->{accno};
+ if ( $form->{accounttype} eq 'gifi' ) {
+ $query = qq|
SELECT description, category, link, contra
FROM chart
WHERE gifi_accno = ?
AND charttype = 'A'|;
- $accno = $form->{gifi_accno};
- }
+ $accno = $form->{gifi_accno};
+ }
- $sth = $dbh->prepare($query);
- $sth->execute($accno);
- ($form->{description}, $form->{category}, $form->{link},
- $form->{contra})
- = $sth->fetchrow_array;
+ $sth = $dbh->prepare($query);
+ $sth->execute($accno);
+ (
+ $form->{description}, $form->{category},
+ $form->{link}, $form->{contra}
+ ) = $sth->fetchrow_array;
- if ($form->{fromdate}) {
+ if ( $form->{fromdate} ) {
- if ($department_id) {
+ if ($department_id) {
- # get beginning balance
- $query = "";
- $union = "";
+ # get beginning balance
+ $query = "";
+ $union = "";
- for (qw(ar ap gl)) {
+ for (qw(ar ap gl)) {
- if ($form->{accounttype} eq 'gifi') {
- $query = qq|
+ if ( $form->{accounttype} eq 'gifi' ) {
+ $query = qq|
$union
SELECT SUM(ac.amount)
FROM acc_trans ac
@@ -244,16 +247,15 @@ sub all_transactions {
= ?
$project |;
- push @queryargs,
- $form->{gifi_accno},
- $form->{fromdate},
- $form->{department_id};
- if ($p_id){
- push @queryargs, $p_id;
- }
- } else {
+ push @queryargs, $form->{gifi_accno}, $form->{fromdate},
+ $form->{department_id};
+ if ($p_id) {
+ push @queryargs, $p_id;
+ }
+ }
+ else {
- $query .= qq|
+ $query .= qq|
$union
SELECT SUM(ac.amount)
FROM acc_trans ac
@@ -270,21 +272,21 @@ sub all_transactions {
a.department_id
= ?
$project |;
- push @queryargs, $form->{accno},
- $form->{fromdate},
- $department_id;
- if ($p_id){
- push @queryargs, $p_id;
- }
- }
+ push @queryargs, $form->{accno}, $form->{fromdate},
+ $department_id;
+ if ($p_id) {
+ push @queryargs, $p_id;
+ }
+ }
- $union = qq| UNION ALL |;
- }
+ $union = qq| UNION ALL |;
+ }
- } else {
+ }
+ else {
- if ($form->{accounttype} eq 'gifi') {
- $query = qq|
+ if ( $form->{accounttype} eq 'gifi' ) {
+ $query = qq|
SELECT SUM(ac.amount)
FROM acc_trans ac
JOIN chart c ON
@@ -292,13 +294,13 @@ sub all_transactions {
WHERE c.gifi_accno = ?
AND ac.transdate < ?
$project |;
- @queryargs = ($form->{gifi_accno},
- $form->{fromdate});
- if ($p_id){
- push @query_ags, $p_id;
- }
- } else {
- $query = qq|
+ @queryargs = ( $form->{gifi_accno}, $form->{fromdate} );
+ if ($p_id) {
+ push @query_ags, $p_id;
+ }
+ }
+ else {
+ $query = qq|
SELECT SUM(ac.amount)
FROM acc_trans ac
JOIN chart c
@@ -306,29 +308,28 @@ sub all_transactions {
WHERE c.accno = ?
AND ac.transdate < ?
$project |;
- @queryargs = ($form->{accno},
- $form->{fromdate});
- if ($p_id){
- push @queryargs, $p_id;
- }
- }
- }
-
- $sth = $dbh->prepare($query);
- $sth->execute(@queryargs);
- ($form->{balance}) = $sth->fetchrow_array;
- $sth->finish;
- @queryargs = ();
- }
- }
-
- $query = "";
- $union = "";
-
- foreach my $id (@id) {
-
- # get all transactions
- $query .= qq|
+ @queryargs = ( $form->{accno}, $form->{fromdate} );
+ if ($p_id) {
+ push @queryargs, $p_id;
+ }
+ }
+ }
+
+ $sth = $dbh->prepare($query);
+ $sth->execute(@queryargs);
+ ( $form->{balance} ) = $sth->fetchrow_array;
+ $sth->finish;
+ @queryargs = ();
+ }
+ }
+
+ $query = "";
+ $union = "";
+
+ foreach my $id (@id) {
+
+ # get all transactions
+ $query .= qq|
$union
SELECT a.id, a.reference, a.description, ac.transdate,
$false AS invoice, ac.amount, 'gl' as module,
@@ -341,23 +342,23 @@ sub all_transactions {
$todate_where
$dpt_where
$project|;
- if ($d_id){
- push @queryargs, $d_id;
- }
- push @queryargs, $id;
- if ($fdate){
- push @queryargs, $fdate;
- }
- if ($tdate){
- push @queryargs, $tdate;
- }
- if ($d_id){
- push @queryargs, $d_id;
- }
- if ($p_id){
- push @queryargs, $p_id;
- }
- $query .= qq|
+ if ($d_id) {
+ push @queryargs, $d_id;
+ }
+ push @queryargs, $id;
+ if ($fdate) {
+ push @queryargs, $fdate;
+ }
+ if ($tdate) {
+ push @queryargs, $tdate;
+ }
+ if ($d_id) {
+ push @queryargs, $d_id;
+ }
+ if ($p_id) {
+ push @queryargs, $p_id;
+ }
+ $query .= qq|
UNION ALL
@@ -374,24 +375,24 @@ sub all_transactions {
$dpt_where
$project|;
- if ($d_id){
- push @queryargs, $d_id;
- }
- push @queryargs, $id;
- if ($fdate){
- push @queryargs, $fdate;
- }
- if ($tdate){
- push @queryargs, $tdate;
- }
- if ($d_id){
- push @queryargs, $d_id;
- }
- if ($p_id){
- push @queryargs, $p_id;
- }
-
- $query .= qq|
+ if ($d_id) {
+ push @queryargs, $d_id;
+ }
+ push @queryargs, $id;
+ if ($fdate) {
+ push @queryargs, $fdate;
+ }
+ if ($tdate) {
+ push @queryargs, $tdate;
+ }
+ if ($d_id) {
+ push @queryargs, $d_id;
+ }
+ if ($p_id) {
+ push @queryargs, $p_id;
+ }
+
+ $query .= qq|
UNION ALL
SELECT a.id, a.invnumber, v.name, ac.transdate,
@@ -407,112 +408,113 @@ sub all_transactions {
$dpt_where
$project |;
- if ($d_id){
- push @queryargs, $d_id;
- }
- push @queryargs, $id;
- if ($fdate){
- push @queryargs, $fdate;
- }
- if ($tdate){
- push @queryargs, $tdate;
- }
- if ($d_id){
- push @queryargs, $d_id;
- }
- if ($p_id){
- push @queryargs, $p_id;
- }
- $union = qq| UNION ALL |;
- }
-
- $query .= qq| ORDER BY $sortorder |;
-
- $sth = $dbh->prepare($query);
- $sth->execute(@queryargs) || $form->dberror($query);
-
- $query = qq|SELECT c.id, c.accno
+ if ($d_id) {
+ push @queryargs, $d_id;
+ }
+ push @queryargs, $id;
+ if ($fdate) {
+ push @queryargs, $fdate;
+ }
+ if ($tdate) {
+ push @queryargs, $tdate;
+ }
+ if ($d_id) {
+ push @queryargs, $d_id;
+ }
+ if ($p_id) {
+ push @queryargs, $p_id;
+ }
+ $union = qq| UNION ALL |;
+ }
+
+ $query .= qq| ORDER BY $sortorder |;
+
+ $sth = $dbh->prepare($query);
+ $sth->execute(@queryargs) || $form->dberror($query);
+
+ $query = qq|SELECT c.id, c.accno
FROM chart c
JOIN acc_trans ac ON (ac.chart_id = c.id)
WHERE ac.amount >= 0
AND (c.link = 'AR' OR c.link = 'AP')
AND ac.trans_id = ?|;
- my $dr = $dbh->prepare($query) || $form->dberror($query);
+ my $dr = $dbh->prepare($query) || $form->dberror($query);
- $query = qq|SELECT c.id, c.accno
+ $query = qq|SELECT c.id, c.accno
FROM chart c
JOIN acc_trans ac ON (ac.chart_id = c.id)
WHERE ac.amount < 0
AND (c.link = 'AR' OR c.link = 'AP')
AND ac.trans_id = ?|;
- my $cr = $dbh->prepare($query) || $form->dberror($query);
+ my $cr = $dbh->prepare($query) || $form->dberror($query);
- my $accno;
- my $chart_id;
- my %accno;
+ my $accno;
+ my $chart_id;
+ my %accno;
- while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
+ while ( my $ca = $sth->fetchrow_hashref(NAME_lc) ) {
- # gl
- if ($ca->{module} eq "gl") {
- $ca->{module} = "gl";
- }
+ # gl
+ if ( $ca->{module} eq "gl" ) {
+ $ca->{module} = "gl";
+ }
- # ap
- if ($ca->{module} eq "ap") {
- $ca->{module} = ($ca->{invoice}) ? 'ir' : 'ap';
- $ca->{module} = 'ps' if $ca->{till};
- }
+ # ap
+ if ( $ca->{module} eq "ap" ) {
+ $ca->{module} = ( $ca->{invoice} ) ? 'ir' : 'ap';
+ $ca->{module} = 'ps' if $ca->{till};
+ }
- # ar
- if ($ca->{module} eq "ar") {
- $ca->{module} = ($ca->{invoice}) ? 'is' : 'ar';
- $ca->{module} = 'ps' if $ca->{till};
- }
+ # ar
+ if ( $ca->{module} eq "ar" ) {
+ $ca->{module} = ( $ca->{invoice} ) ? 'is' : 'ar';
+ $ca->{module} = 'ps' if $ca->{till};
+ }
- if ($ca->{amount}) {
- %accno = ();
+ if ( $ca->{amount} ) {
+ %accno = ();
- if ($ca->{amount} < 0) {
- $ca->{debit} = $ca->{amount} * -1;
- $ca->{credit} = 0;
- $dr->execute($ca->{id});
- $ca->{accno} = ();
+ if ( $ca->{amount} < 0 ) {
+ $ca->{debit} = $ca->{amount} * -1;
+ $ca->{credit} = 0;
+ $dr->execute( $ca->{id} );
+ $ca->{accno} = ();
- while (($chart_id, $accno) = $dr->fetchrow_array) {
- $accno{$accno} = 1 if $chart_id ne $ca->{chart_id};
- }
+ while ( ( $chart_id, $accno ) = $dr->fetchrow_array ) {
+ $accno{$accno} = 1 if $chart_id ne $ca->{chart_id};
+ }
- $dr->finish;
+ $dr->finish;
- for (sort keys %accno) { push @{ $ca->{accno} }, "$_ " }
+ for ( sort keys %accno ) { push @{ $ca->{accno} }, "$_ " }
- } else {
+ }
+ else {
- $ca->{credit} = $ca->{amount};
- $ca->{debit} = 0;
+ $ca->{credit} = $ca->{amount};
+ $ca->{debit} = 0;
- $cr->execute($ca->{id});
- $ca->{accno} = ();
+ $cr->execute( $ca->{id} );
+ $ca->{accno} = ();
- while (($chart_id, $accno) = $cr->fetchrow_array) {
- $accno{$accno} = 1 if $chart_id ne $ca->{chart_id};
- }
+ while ( ( $chart_id, $accno ) = $cr->fetchrow_array ) {
+ $accno{$accno} = 1 if $chart_id ne $ca->{chart_id};
+ }
- $cr->finish;
+ $cr->finish;
- for (keys %accno) { push @{ $ca->{accno} }, "$_ " }
+ for ( keys %accno ) { push @{ $ca->{accno} }, "$_ " }
- }
+ }
- push @{ $form->{CA} }, $ca;
- }
- }
+ push @{ $form->{CA} }, $ca;
+ }
+ }
- $sth->finish;
- $dbh->commit;
+ $sth->finish;
+ $dbh->commit;
}
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-27 17:44:31 +0000