path: root/LedgerSMB/GL.pm

#=====================================================================
# 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
# with permission.
#
# This file contains source code included with or based on SQL-Ledger which
# is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
# under the GNU General Public License version 2 or, at your option, any later
# version.  For a full list including contact information of contributors,
# maintainers, and copyright holders, see the CONTRIBUTORS file.
#
# Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
# Copyright (C) 2000
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
#======================================================================
#
# This file has undergone whitespace cleanup.
#
#======================================================================
#
# General ledger backend code
#
#======================================================================

package GL;


sub delete_transaction {

    my ($self, $myconfig, $form) = @_;

    # connect to database
    my $dbh = $form->{dbh};

    my %audittrail = ( 
        tablename  => 'gl',
        reference  => $form->{reference},
        formname   => 'transaction',
        action     => 'deleted',
        id         => $form->{id} );

    $form->audittrail($dbh, "", \%audittrail);
    my $id = $dbh->quote($form->{id});
    my $query = qq|DELETE FROM gl WHERE id = $id|;
    $dbh->do($query) || $form->dberror($query);

    $query = qq|DELETE FROM acc_trans WHERE trans_id = $id|;
    $dbh->do($query) || $form->dberror($query);

    # commit and redirect
    my $rc = $dbh->commit;

    $rc;
}


sub post_transaction {

    my ($self, $myconfig, $form) = @_;

    my $null;
    my $project_id;
    my $department_id;
    my $i;

    # connect to database, turn off AutoCommit
    my $dbh = $form->{dbh};

    my $query;
    my $sth;

    my $id = $dbh->quote($form->{id});
    if ($form->{id}) {

        $query = qq|SELECT id FROM gl WHERE id = $id|;
        ($form->{id}) = $dbh->selectrow_array($query);

        if ($form->{id}) {
            # delete individual transactions
            $query = qq|
                DELETE FROM acc_trans WHERE trans_id = $id|;

            $dbh->do($query) || $form->dberror($query);
        }
    }

    if (! $form->{id}) {

        my $uid = localtime;
        $uid .= "$$";

        $query = qq|
        INSERT INTO gl (reference, employee_id)
             VALUES ('$uid', (SELECT id FROM employee
                               WHERE login = ?))|;

        $sth = $dbh->prepare($query);
        $sth->execute($form->{login}) || $form->dberror($query);

        $query = qq|
            SELECT id 
              FROM gl
             WHERE reference = '$uid'|;
    
        ($form->{id}) = $dbh->selectrow_array($query);
    }

    ($null, $department_id) = split /--/, $form->{department};
    $department_id *= 1;

    $form->{reference} = $form->update_defaults(
        $myconfig, 'glnumber', $dbh) 
            unless $form->{reference};
    $form->{reference} ||= $form->{id};

    $query = qq|
        UPDATE gl 
           SET reference = |.$dbh->quote($form->{reference}).qq|,
              description = |.$dbh->quote($form->{description}).qq|,
              notes = |.$dbh->quote($form->{notes}).qq|,
              transdate = '$form->{transdate}',
              department_id = $department_id
        WHERE id = $form->{id}|;

    $dbh->prepare($query);
    $sth->execute($form->{transdate}, $department_id, $form->{id}) 
        || $form->dberror($query);

    my $amount = 0;
    my $posted = 0;
    my $debit;
    my $credit;

    # insert acc_trans transactions
    for $i (1 .. $form->{rowcount}) {

        $debit = $form->parse_amount($myconfig, $form->{"debit_$i"});
        $credit = $form->parse_amount($myconfig, $form->{"credit_$i"});

        # extract accno
        ($accno) = split(/--/, $form->{"accno_$i"});

        if ($credit) {
            $amount = $credit;
            $posted = 0;
        }

        if ($debit) {
            $amount = $debit * -1;
            $posted = 0;
        }

        # add the record
        if (! $posted) {

            ($null, $project_id) = split /--/, 
                $form->{"projectnumber_$i"};
            $project_id ||= 'NULL';


            $query = qq|
                INSERT INTO acc_trans 
                            (trans_id, chart_id, amount, 
                            transdate, source, project_id, 
                            fx_transaction, memo, cleared)
                    VALUES  (?, (SELECT id
                                   FROM chart
                                  WHERE accno = ?),
                           ?, ?, ?, ?, ?, ?, ?)|;

            $sth = $dbh->prepare($query);
            $sth->execute(
                $form->{id}, $accno, $amount, 
                $form->{transdate}, $form->{"source_$i"},
                $project_id, $form->{"fx_transaction_$i"},
                $form->{"memo_$i"}, $form->{"cleared_$i"}
                ) || $form->dberror($query);

            $posted = 1;
        }
    }

    my %audittrail = ( 
        tablename  => 'gl',
        reference  => $form->{reference},
        formname   => 'transaction',
        action     => 'posted',
        id         => $form->{id} );

    $form->audittrail($dbh, "", \%audittrail);

    $form->save_recurring($dbh, $myconfig);

    # commit and redirect
    my $rc = $dbh->commit;
    $dbh->disconnect;

    $rc;
}



sub all_transactions {

    my ($self, $myconfig, $form) = @_;

    # connect to database
    my $dbh = $form->{dbh};
    my $query;
    my $sth;
    my $var;
    my $null;

    my ($glwhere, $arwhere, $apwhere) = ("1 = 1", "1 = 1", "1 = 1");

    if ($form->{reference} ne "") {
        $var = $dbh->quote($form->like(lc $form->{reference}));
        $glwhere .= " AND lower(g.reference) LIKE $var";
        $arwhere .= " AND lower(a.invnumber) LIKE $var";
        $apwhere .= " AND lower(a.invnumber) LIKE $var";
    }

    if ($form->{department} ne "") {
        ($null, $var) = split /--/, $form->{department};
        $var = $dbh->quote($var);
        $glwhere .= " AND g.department_id = $var";
        $arwhere .= " AND a.department_id = $var";
        $apwhere .= " AND a.department_id = $var";
    }

    if ($form->{source} ne "") {
        $var = $dbh->quote($form->like(lc $form->{source}));
        $glwhere .= " AND lower(ac.source) LIKE $var";
        $arwhere .= " AND lower(ac.source) LIKE $var";
        $apwhere .= " AND lower(ac.source) LIKE $var";
    }

    if ($form->{memo} ne "") {
        $var = $form->like(lc $form->{memo});
        $glwhere .= " AND lower(ac.memo) LIKE $var";
        $arwhere .= " AND lower(ac.memo) LIKE $var";
        $apwhere .= " AND lower(ac.memo) LIKE $var";
    }

    ($form->{datefrom}, $form->{dateto}) = $form->from_to(
            $form->{year}, $form->{month}, $form->{interval}) 
                if $form->{year} && $form->{month};

    if ($form->{datefrom}) {
        $glwhere .= " AND ac.transdate >= ".
            $dbh->quote($form->{datefrom});
        $arwhere .= " AND ac.transdate >= ".
            $dbh->quote($form->{datefrom});
        $apwhere .= " AND ac.transdate >= ".
            $dbh->quote($form->{datefrom});
    }

    if ($form->{dateto}) {
        $glwhere .= " AND ac.transdate <= ".
            $dbh->quote($form->{dateto});
        $arwhere .= " AND ac.transdate <= ".
            $dbh->quote($form->{dateto});
        $apwhere .= " AND ac.transdate <= ".
            $dbh->quote($form->{dateto});
    }

    if ($form->{amountfrom}) {
        $glwhere .= " AND abs(ac.amount) >= ".
            $dbh->quote($form->{amountfrom});
        $arwhere .= " AND abs(ac.amount) >= ".
            $dbh->quote($form->{amountfrom});
        $apwhere .= " AND abs(ac.amount) >= ".
            $dbh->quote($form->{amountfrom});
    }

    if ($form->{amountto}) {
        $glwhere .= " AND abs(ac.amount) <= ".
            $dbh->quote($form->{amountto});
        $arwhere .= " AND abs(ac.amount) <= ".
            $dbh->quote($form->{amountto});
        $apwhere .= " AND abs(ac.amount) <= ".
            $dbh->quote($form->{amountto});
    }

    if ($form->{description}) {

        $var = $dbh->quote($form->like(lc $form->{description}));
        $glwhere .= " AND lower(g.description) LIKE $var";
        $arwhere .= " AND (lower(ct.name) LIKE $var
                       OR lower(ac.memo) LIKE $var
                       OR a.id IN (SELECT DISTINCT trans_id
                     FROM invoice
                    WHERE lower(description) LIKE 'var))";

        $apwhere .= " AND (lower(ct.name) LIKE $var
                       OR lower(ac.memo) LIKE $var
                       OR a.id IN (SELECT DISTINCT trans_id
                     FROM invoice
                    WHERE lower(description) LIKE $var))";
    }

    if ($form->{notes}) {
        $var = $dbh->quote($form->like(lc $form->{notes}));
        $glwhere .= " AND lower(g.notes) LIKE $var";
        $arwhere .= " AND lower(a.notes) LIKE $var";
        $apwhere .= " AND lower(a.notes) LIKE $var";
    }

    if ($form->{accno}) {
        $var = $dbh->quote($form->{accno});
        $glwhere .= " AND c.accno = $var";
        $arwhere .= " AND c.accno = $var";
        $apwhere .= " AND c.accno = $var";
    }

    if ($form->{gifi_accno}) {
        $var = $dbh->quote($form->{gifiaccno});
        $glwhere .= " AND c.gifi_accno = $var";
        $arwhere .= " AND c.gifi_accno = $var";
        $apwhere .= " AND c.gifi_accno = $var";
    }

    if ($form->{category} ne 'X') {
        $var = $dbh->quote($form->{gifiaccno});
        $glwhere .= " AND c.category = $var";
        $arwhere .= " AND c.category = $var";
        $apwhere .= " AND c.category = $var";
    }

    if ($form->{accno}) {
        my $accno = $dbh->quote($form->{accno});

        # get category for account
        $query = qq|SELECT category, link, contra, description
                      FROM chart
                     WHERE accno = $accno|;

        ($form->{category}, $form->{link}, $form->{contra}, 
         $form->{account_description}) = $dbh->selectrow_array($query); 

        if ($form->{datefrom}) {

            $query = qq|
                SELECT SUM(ac.amount)
                  FROM acc_trans ac
                  JOIN chart c ON (ac.chart_id = c.id)
                 WHERE c.accno = $accno
                       AND ac.transdate < date |.
                       $dbh->quote($form->{datefrom});

            ($form->{balance}) = $dbh->selectrow_array($query);
        }
    }

    if ($form->{gifi_accno}) {
        my $gifi = $dbh->quote($form->{gifi_accno});

        # get category for account
        $query = qq|SELECT c.category, c.link, c.contra, g.description
                      FROM chart c
                 LEFT JOIN gifi g ON (g.accno = c.gifi_accno)
                     WHERE c.gifi_accno = $gifi|;

        ($form->{category}, $form->{link}, $form->{contra}, 
         $form->{gifi_account_description}) = $dbh->selectrow_array(
            $query); 

        if ($form->{datefrom}) {

            $query = qq|
                SELECT SUM(ac.amount)
                  FROM acc_trans ac
                  JOIN chart c ON (ac.chart_id = c.id)
                 WHERE c.gifi_accno = $gifi
                       AND ac.transdate < date |.
                       $dbh->quote($form->{datefrom});

            ($form->{balance}) = $dbh->selectrow_array($query);
        }
    }

    my $false = 'FALSE';

    my %ordinal = ( 
        id => 1,
        reference => 4,
        description => 5,
        transdate => 6,
        source => 7,
        accno => 9,
        department => 15,
        memo => 16 );

    my @a = (id, transdate, reference, source, description, accno);
    my $sortorder = $form->sort_order(\@a, \%ordinal);

    my $query = qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference,
                          g.description, ac.transdate, ac.source,
                          ac.amount, c.accno, c.gifi_accno, g.notes, c.link,
                          '' AS till, ac.cleared, d.description AS department,
                          ac.memo
                     FROM gl AS g
                     JOIN acc_trans ac ON (g.id = ac.trans_id)
                     JOIN chart c ON (ac.chart_id = c.id)
                LEFT JOIN department d ON (d.id = g.department_id)
                    WHERE $glwhere

                    UNION ALL

                   SELECT a.id, 'ar' AS type, a.invoice, a.invnumber,
                          ct.name, ac.transdate, ac.source,
                          ac.amount, c.accno, c.gifi_accno, a.notes, c.link,
                          a.till, ac.cleared, d.description AS department,
                          ac.memo
                     FROM ar a
                     JOIN acc_trans ac ON (a.id = ac.trans_id)
                     JOIN chart c ON (ac.chart_id = c.id)
                     JOIN customer ct ON (a.customer_id = ct.id)
                LEFT JOIN department d ON (d.id = a.department_id)
                    WHERE $arwhere

                UNION ALL

                   SELECT a.id, 'ap' AS type, a.invoice, a.invnumber,
                          ct.name, ac.transdate, ac.source,
                          ac.amount, c.accno, c.gifi_accno, a.notes, c.link,
                          a.till, ac.cleared, d.description AS department,
                          ac.memo
                     FROM ap a
                     JOIN acc_trans ac ON (a.id = ac.trans_id)
                     JOIN chart c ON (ac.chart_id = c.id)
                     JOIN vendor ct ON (a.vendor_id = ct.id)
                LEFT JOIN department d ON (d.id = a.department_id)
                    WHERE $apwhere
                 ORDER BY $sortorder|;

    my $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {

        # gl
        if ($ref->{type} eq "gl") {
            $ref->{module} = "gl";
        }

        # ap
        if ($ref->{type} eq "ap") {

            if ($ref->{invoice}) {
                $ref->{module} = "ir";
            } else {
                $ref->{module} = "ap";
            }
        }

        # ar
        if ($ref->{type} eq "ar") {

            if ($ref->{invoice}) {
                $ref->{module} = ($ref->{till}) ? "ps" : "is";
            } else {
                $ref->{module} = "ar";
            }
        }

        if ($ref->{amount} < 0) {
            $ref->{debit} = $ref->{amount} * -1;
            $ref->{credit} = 0;
        } else {
            $ref->{credit} = $ref->{amount};
            $ref->{debit} = 0;
        }

        push @{ $form->{GL} }, $ref;
    }

    $sth->finish;
    $dbh->commit;
}


sub transaction {

    my ($self, $myconfig, $form) = @_;

    my ($query, $sth, $ref);

    # connect to database
    my $dbh = $form->{dbh};

    if ($form->{id}) {

        $query = "SELECT setting_key, value
                    FROM defaults
                    WHERE setting_key IN 
                        ('closedto', 'revtrans')";

        $sth = $dbh->prepare($query);
        $sth->execute || $form->dberror($query);

        my $results = $sth->fetchall_hashref('setting_key');
        $form->{closedto} = $results->{'closedto'}->{'value'};
        $form->{revtrans} = $results->{'revtrans'}->{'value'};
        $sth->finish;

        $query = qq|SELECT g.*, d.description AS department
                      FROM gl g
                 LEFT JOIN department d ON (d.id = g.department_id)  
                     WHERE g.id = ?|;

        $sth = $dbh->prepare($query);
        $sth->execute($form->{id}) || $form->dberror($query);

        $ref = $sth->fetchrow_hashref(NAME_lc);
        for (keys %$ref) { $form->{$_} = $ref->{$_} }
        $sth->finish;

        # retrieve individual rows
        $query = qq|SELECT ac.*, c.accno, c.description, p.projectnumber
                      FROM acc_trans ac
                      JOIN chart c ON (ac.chart_id = c.id)
                 LEFT JOIN project p ON (p.id = ac.project_id)
                     WHERE ac.trans_id = ?
                  ORDER BY accno|;

        $sth = $dbh->prepare($query);
        $sth->execute($form->{id}) || $form->dberror($query);

        while ($ref = $sth->fetchrow_hashref(NAME_lc)) {

            if ($ref->{fx_transaction}) {
                $form->{transfer} = 1;
            }
            push @{ $form->{GL} }, $ref;
        }

        # get recurring transaction
        $form->get_recurring($dbh);

    } else {

        $query = "SELECT current_date AS transdate, setting_key, value
                    FROM defaults
                    WHERE setting_key IN 
                        ('closedto', 'revtrans')";
        
        $sth = $dbh->prepare($query);
        $sth->execute || $form->dberror($query);

        my $results = $sth->fetchall_hashref('setting_key');
        $form->{closedto} = $results->{'closedto'}->{'value'};
        $form->{revtrans} = $results->{'revtrans'}->{'value'};
        $form->{transdate} = $results->{'revtrans'}->{'transdate'};
    }

    $sth->finish;

    # get chart of accounts
    $query = qq|SELECT accno,description
                  FROM chart
                 WHERE charttype = 'A'
              ORDER BY accno|;

    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
        push @{ $form->{all_accno} }, $ref;
    }

    $sth->finish;

    # get departments
    $form->all_departments($myconfig, $dbh);

    # get projects
    $form->all_projects($myconfig, $dbh, $form->{transdate});

    $dbh->commit;

}

1;