#=====================================================================
# 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->dbconnect_noauto($myconfig);

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

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

    my $query = qq|DELETE FROM gl WHERE id = $form->{id}|;
    $dbh->do($query) || $form->dberror($query);

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

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

    $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->dbconnect_noauto($myconfig);

    my $query;
    my $sth;

    if ($form->{id}) {

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

        if ($form->{id}) {
            # delete individual transactions
            $query = qq|DELETE FROM acc_trans 
                         WHERE trans_id = $form->{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 = '$form->{login}'))|;

        $dbh->do($query) || $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->do($query) || $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';

            for (qw(fx_transaction cleared)) { $form->{"${_}_$i"} *= 1 }


            $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, 
                                               transdate, source, project_id, 
                                               fx_transaction, memo, cleared)
                        VALUES ($form->{id}, (SELECT id
                                                FROM chart
                                               WHERE accno = '$accno'),
                                $amount, '$form->{transdate}', |.
                                $dbh->quote($form->{"source_$i"}) .qq|,
                                $project_id, '$form->{"fx_transaction_$i"}', |.
                                $dbh->quote($form->{"memo_$i"}).qq|,
                                '$form->{"cleared_$i"}')|;

            $dbh->do($query) || $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->dbconnect($myconfig);
    my $query;
    my $sth;
    my $var;
    my $null;

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

    if ($form->{reference} ne "") {
        $var = $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};
        $glwhere .= " AND g.department_id = $var";
        $arwhere .= " AND a.department_id = $var";
        $apwhere .= " AND a.department_id = $var";
    }

    if ($form->{source} ne "") {
        $var = $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 >= '$form->{datefrom}'";
        $arwhere .= " AND ac.transdate >= '$form->{datefrom}'";
        $apwhere .= " AND ac.transdate >= '$form->{datefrom}'";
    }

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

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

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

    if ($form->{description}) {

        $var = $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 = $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'";