path: root/LedgerSMB/CP.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) 2003
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
#
#======================================================================
#
# This file has undergone whitespace cleanup.
#
#======================================================================
#
# Check and receipt printing payment module backend routines
# Number to text conversion routines are in
# locale/{countrycode}/Num2text
#
#======================================================================

package CP;
use LedgerSMB::Sysconfig;



sub new {

    my ($type, $countrycode) = @_;

    $self = {};

    use LedgerSMB::Num2text;
    use LedgerSMB::Locale;
    $self->{'locale'} = LedgerSMB::Locale->get_handle($countrycode);

    bless $self, $type;

}


sub paymentaccounts {

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

    my $dbh = $form->{dbh};

    my $query = qq|SELECT accno, description, link
                     FROM chart
                    WHERE link LIKE ?
                 ORDER BY accno|;

    my $sth = $dbh->prepare($query);
    $sth->execute("%$form->{ARAP}%") || $form->dberror($query);

    $form->{PR}{$form->{ARAP}} = ();
    $form->{PR}{"$form->{ARAP}_paid"} = ();

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

        foreach my $item (split /:/, $ref->{link}) {

            if ($item eq $form->{ARAP}) {
                push @{ $form->{PR}{$form->{ARAP}} }, $ref;
            }

            if ($item eq "$form->{ARAP}_paid") {
                push @{ $form->{PR}{"$form->{ARAP}_paid"} }, $ref;
            }
        }
    }

    $sth->finish;

    # get currencies and closedto
    $query = qq|
        SELECT value, (SELECT value FROM defaults
                        WHERE setting_key = 'closedto'), 
               current_date
          FROM defaults
         WHERE setting_key = 'curr'|;

    ($form->{currencies}, $form->{closedto}, $form->{datepaid}) = $dbh->selectrow_array($query);

    if ($form->{payment} eq 'payments') {
        # get language codes
        $query = qq|SELECT *
                      FROM language
                  ORDER BY 2|;

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

        $form->{all_language} = ();

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

        $sth->finish;

        $form->all_departments($myconfig, $dbh, $form->{vc});
    }

    $dbh->commit;

}


sub get_openvc {

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

    my $dbh = $form->{dbh};

    my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap';
    my $query = qq|SELECT count(*)
                     FROM $form->{vc} ct, $arap a
                    WHERE a.$form->{vc}_id = ct.id
                      AND a.amount != a.paid|;

    my ($count) = $dbh->selectrow_array($query);

    my $sth;
    my $ref;
    my $i = 0;

    my $where = qq|WHERE a.$form->{vc}_id = ct.id
                     AND a.amount != a.paid|;

    if ($form->{$form->{vc}}) {
        my $var = $dbh->quote($form->like(lc $form->{$form->{vc}}));
        $where .= " AND lower(name) LIKE $var";
    }

    # build selection list
    $query = qq|SELECT DISTINCT ct.*
                  FROM $form->{vc} ct, $arap a
                $where
              ORDER BY name|;

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

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

    $sth->finish;

    $form->all_departments($myconfig, $dbh, $form->{vc});

    # get language codes
    $query = qq|SELECT *
                  FROM language
              ORDER BY 2|;

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

    $form->{all_language} = ();

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

    $sth->finish;

    # get currency for first name
    if (@{ $form->{name_list} }) {

        # Chris T:  I don't like this but it seems safe injection-wise
        # Leaving it so we can change it when we go to a new system
        $query = qq|SELECT curr 
                      FROM $form->{vc}
                     WHERE id = $form->{name_list}->[0]->{id}|;

        ($form->{currency}) = $dbh->selectrow_array($query);
        $form->{currency} ||= $form->{defaultcurrency};
    }

    $dbh->commit;

    $i;
}


sub get_openinvoices {

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

    my $null;
    my $department_id;

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

    $vc_id = $dbh->quote($form->{"$form->{vc}_id"});
    my $where = qq|WHERE a.$form->{vc}_id = $vc_id
                     AND a.amount != a.paid|;

    $curr = $dbh->quote($form->{currency});
    $where .= qq| AND a.curr = $curr| if $form->{currency};

    my $sortorder = "transdate, invnumber";

    my ($buysell);

    if ($form->{vc} eq 'customer') {
        $buysell = "buy";
    } else {
        $buysell = "sell";
    }

    if ($form->{payment} eq 'payments') {

        $where = qq|WHERE a.amount != a.paid|;
        $where .= qq| AND a.curr = $curr| if $form->{currency};

        if ($form->{duedatefrom}) {
            $where .= qq| AND a.duedate >= 
                |.$dbh->quote($form->{duedatefrom});
        }

        if ($form->{duedateto}) {
            $where .= qq| AND a.duedate <= |.
                $dbh->quote($form->{duedateto});
        }

        $sortorder = "name, transdate";
    }


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

    if ($department_id) {
        $where .= qq| AND a.department_id = $department_id|;
    }

    my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid,
                          a.curr, c.name, a.$form->{vc}_id, c.language_code
                     FROM $form->{arap} a
                     JOIN $form->{vc} c ON (c.id = a.$form->{vc}_id)
                   $where
                 ORDER BY $sortorder|;

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

    $query = qq|SELECT s.spoolfile
                  FROM status s
                 WHERE s.formname = '$form->{formname}'
                   AND s.trans_id = ?|;

    my $vth = $dbh->prepare($query);

    my $spoolfile;

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

        # if this is a foreign currency transaction get exchangerate
        $ref->{exchangerate} = 
            $form->get_exchangerate($dbh, 
                $ref->{curr}, 
                $ref->{transdate}, 
                $buysell) 
                    if ($form->{currency} 
                        ne $form->{defaultcurrency});

        $vth->execute($ref->{id});
        $ref->{queue} = "";
    
        while (($spoolfile) = $vth->fetchrow_array) {
            $ref->{queued} .= "$form->{formname} $spoolfile ";
        }

        $vth->finish;
        $ref->{queued} =~ s/ +$//g;

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

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

}



sub post_payment {

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

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

    my $sth;

    my ($paymentaccno) = split /--/, $form->{account};

    # if currency ne defaultcurrency update exchangerate
    if ($form->{currency} ne $form->{defaultcurrency}) {

        $form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate});

        if ($form->{vc} eq 'customer') {
            $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate}, 0);
        } else {
            $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, 0, $form->{exchangerate});
        }

    } else {
        $form->{exchangerate} = 1;
    }

    my $query = qq|
        SELECT (SELECT value FROM defaults 
                 WHERE setting_key='fxgain_accno_id'), 
               (SELECT value FROM defaults
                 WHERE setting_key='fxloss_accno_id')|;

    my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);

    my ($buysell);

    if ($form->{vc} eq 'customer') {
        $buysell = "buy";
    } else {
        $buysell = "sell";
    }

    my $ml;
    my $where;

    if ($form->{ARAP} eq 'AR') {

        $ml = 1;
        $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |;

    } else {

        $ml = -1;
        $where = qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |;

    }

    my $paymentamount = $form->parse_amount($myconfig, $form->{amount});

    # query to retrieve paid amount
    $query = qq|SELECT paid 
                  FROM $form->{arap}
                 WHERE id = ?
            FOR UPDATE|;

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

    my %audittrail;

    # go through line by line
    for my $i (1 .. $form->{rowcount}) {

        $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
        $form->{"due_$i"} = $form->parse_amount($myconfig, $form->{"due_$i"});

        if ($form->{"checked_$i"} && $form->{"paid_$i"}) {

            $paymentamount -= $form->{"paid_$i"};

            # get exchangerate for original 
            $query = qq|
                SELECT $buysell
                  FROM exchangerate e
                  JOIN $form->{arap} a 
                       ON (a.transdate = e.transdate)
                 WHERE e.curr = ?
                       AND a.id = ?|;

            my $sth = $dbh->prepare($query);
            $sth->execute($form->{currency}, $form->{"id_$i"});
            my ($exchangerate) = $sth->fetchrow_array();

            $exchangerate = 1 unless $exchangerate;

            $query = qq|
                SELECT c.id
                  FROM chart c
                  JOIN acc_trans a ON (a.chart_id = c.id)
                 WHERE $where
                       AND a.trans_id = ?|;

            my $sth = $dbh->prepare($query);
            $sth->execute($form->{"id_$i"});
            my ($id) = $sth->fetchrow_array;

            $amount = 
                $form->round_amount(
                    $form->{"paid_$i"} * $exchangerate, 2);

            # add AR/AP
            $query = qq|
                INSERT INTO acc_trans 
                            (trans_id, chart_id, transdate, 
                            amount)
                     VALUES (?, ?, ?, ?)|;
            $sth = $dbh->prepare($query);
            $sth->execute($form->{"id_$i"}, $id, 
                $form->{date_paid}, $amount * $ml) 
                    || $form->dberror($query, 'CP.pm', 427);

            # add payment
            $query = qq|
                INSERT INTO acc_trans 
                            (trans_id, chart_id, transdate,
                             amount, source, memo)
                     VALUES (?, (SELECT id 
                                   FROM chart
                                  WHERE accno = ?),
                        ?, ?, ?, ?)|;
            $sth = $dbh->prepare($query);
            $sth->execute(
                $form->{"id_$i"}, $paymentaccno, 
                $form->{datepaid}, $form->{"paid_$i"} * $ml *-1,
                $form->{source}, $form->{memo})
                    || $form->dberror(
                        $query, 'CP.pm', 444);

            # add exchangerate difference if currency ne defaultcurrency
            $amount = $form->round_amount(
                $form->{"paid_$i"} * 
                    ($form->{exchangerate} - 1), 
                2);

            if ($amount) {
                # exchangerate difference
                $query = qq|
                    INSERT INTO acc_trans 
                                (trans_id, chart_id, 
                                transdate, amount, cleared,
                                fx_transaction, source)
                         VALUES (?, (SELECT id 
                                       FROM chart
                                      WHERE accno = ?),
                                 ?, ?, '0', '1', ?)|;
                $sth = $dbh->prepare($query);
                $sth->execute(
                    $form->{"id_$i"}, $paymentaccno,
                    $form->{datepaid}, $amount * $ml * -1, 
                    $form->{source})
                        || $form->dberror(
                            $query, 'CP.pm', 470);
                            
                # gain/loss
                $amount = 
                    ($form->round_amount(
                        $form->{"paid_$i"} * 
                            $exchangerate,
                        2) - 
                    $form->round_amount(
                        $form->{"paid_$i"} * 
                            $form->{exchangerate},
                        2)) 
                    * $ml * -1;

                if ($amount) {

                    my $accno_id = 
                        ($amount > 0) 
                        ? $fxgain_accno_id 
                        : $fxloss_accno_id;

                    $query = qq|
                        INSERT INTO acc_trans 
                                    (trans_id, 
                                    chart_id, 
                                    transdate,
                                    amount, cleared, 
                                    fx_transaction)
                        VALUES (?, ?, ?, ?, '0', '1')|;
                    $sth = $dbh->prepare($query);
                    $sth->execute(
                        $form->{"id_$i"}, $accno_id,
                        $form->{datepaid}, $amount)
                            || $form->dberror(
                                $query,
                                'CP.pm',
                                506);
                }
            }

            $form->{"paid_$i"} = 
                $form->round_amount(
                    $form->{"paid_$i"} * $exchangerate, 2);

            $pth->execute($form->{"id_$i"}) || $form->dberror;
            ($amount) = $pth->fetchrow_array;
            $pth->finish;

            $amount += $form->{"paid_$i"};

            # update AR/AP transaction
            $query = qq|
                UPDATE $form->{arap} 
                   SET paid = ?,
                       datepaid = ?
                 WHERE id = ?|;

            $sth = $dbh->prepare($query);
            $sth->execute(
                $amount, $form->{datepaid}, $form->{"id_$i"})
                    || $form->dberror($query, 'CP.pm', 
                        530);

            %audittrail = ( 
                tablename  => $form->{arap},
                reference  => $form->{source},
                formname   => $form->{formname},
                action     => 'posted',
                id         => $form->{"id_$i"} );

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

        }
    }


    # record a AR/AP with a payment
    if ($form->round_amount($paymentamount, 2)) {
        $form->{invnumber} = "";
        OP::overpayment("", $myconfig, $form, $dbh, $paymentamount, $ml, 1);
    }

    my $rc = $dbh->commit;

    $rc;

}


sub post_payments {

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

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

    my $sth;

    my ($paymentaccno) = split /--/, $form->{account};

    # if currency ne defaultcurrency update exchangerate
    if ($form->{currency} ne $form->{defaultcurrency}) {
        $form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate});

        if ($form->{vc} eq 'customer') {
            $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, $form->{exchangerate}, 0);
        } else {
            $form->update_exchangerate($dbh, $form->{currency}, $form->{datepaid}, 0, $form->{exchangerate});
        }

    } else {
        $form->{exchangerate} = 1;
    }

    my $query = qq|
        SELECT (SELECT value FROM defaults 
                 WHERE setting_key='fxgain_accno_id'), 
               (SELECT value FROM defaults
                 WHERE setting_key='fxloss_accno_id')|;

    my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);

    my ($buysell);

    if ($form->{vc} eq 'customer') {
        $buysell = "buy";
    } else {
        $buysell = "sell";
    }

    my $ml;
    my $where;

    if ($form->{ARAP} eq 'AR') {

        $ml = 1;
        $where = qq| (c.link = 'AR' OR c.link LIKE 'AR:%') |;

    } else {

        $ml = -1;
        $where = qq| (c.link = 'AP' OR c.link LIKE '%:AP' OR c.link LIKE '%:AP:%') |;

    }

    # get AR/AP account
    $query = qq|SELECT c.accno 
                  FROM chart c
                  JOIN acc_trans ac ON (ac.chart_id = c.id)
                 WHERE trans_id = ?
                   AND $where|;

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

    # query to retrieve paid amount
    $query = qq|SELECT paid 
                  FROM $form->{arap}
                 WHERE id = ?
            FOR UPDATE|;

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

    my %audittrail;

    my $overpayment = 0;
    my $accno_id;

    # go through line by line
    for my $i (1 .. $form->{rowcount}) {

        $ath->execute($form->{"id_$i"});
        ($form->{$form->{ARAP}}) = $ath->fetchrow_array;
        $ath->finish;

        $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
        $form->{"due_$i"} = $form->parse_amount($myconfig, $form->{"due_$i"});

        if ($form->{"$form->{vc}_id_$i"} ne $sameid) {
            # record a AR/AP with a payment
            if ($overpayment > 0 && $form->{$form->{ARAP}}) {
                $form->{invnumber} = "";
                OP::overpayment("", $myconfig, $form, $dbh, $overpayment, $ml, 1);
            }

            $overpayment = 0;
            $form->{"$form->{vc}_id"} = $form->{"$form->{vc}_id_$i"};
            for (qw(source memo)) { $form->{$_} = $form->{"${_}_$i"} }
        }

        if ($form->{"checked_$i"} && $form->{"paid_$i"}) {

            $overpayment += ($form->{"paid_$i"} - $form->{"due_$i"});

            # get exchangerate for original 
            $query = qq|
                SELECT $buysell
                  FROM exchangerate e
                  JOIN $form->{arap} a 
                       ON (a.transdate = e.transdate)
                 WHERE e.curr = ?
                       AND a.id = ?|;

            $sth = $sbh->prepare($query);
            $sth->execute($form->{currency}, $form->{"id_$i"})
                || $form->dberror($query, 'CP.pm', 671);
            my ($exchangerate) = $sth->fetchrow_array;

            $exchangerate ||= 1;

            $query = qq|
                SELECT c.id
                  FROM chart c
                  JOIN acc_trans a ON (a.chart_id = c.id)
                 WHERE $where
                       AND a.trans_id = $form->{"id_$i"}|;

            $sth = $dbh->prepare($query);
            $sth->execute($form->{"id_$i"});

            $paid = ($form->{"paid_$i"} > $form->{"due_$i"}) ? $form->{"due_$i"} : $form->{"paid_$i"};
            $amount = $form->round_amount($paid * $exchangerate, 2);

            # add AR/AP
            $query = qq|
                INSERT INTO acc_trans 
                            (trans_id, chart_id, transdate, 
                            amount)
                     VALUES (?, ?, ?, ?)|;

            $sth = $dbh->prepare($query);
            $sth->execute(
                $form->{"id_$i"}, $id, $form->{datepaid},
                $amount * $ml)
                    || $form->dberror($query, 'CP.pm', 
                        701);

            $query = qq|SELECT id
                          FROM chart
                         WHERE accno = ?|;

            $sth = $dbh->prepare($query);
            $sth->execute($paymentaccno);
            ($accno_id) = $sth->fetchrow_array;

            # add payment
            $query = qq|
                INSERT INTO acc_trans 
                            (trans_id, chart_id, transdate,
                            amount, source, memo)
                    VALUES (?, ?, ?, ?, ?, ?)|;

            $sth = $dbh->prepare($query);
            $sth->execute(
                $form->{"id_$i"}, $accno_id, $form->{datepaid},
                $paid * $ml * -1, $form->{source}, 
                $form->{memo})
                    || $form->dberror($query, 'CP.pm', 
                        723);

            # add exchangerate difference if currency ne defaultcurrency
            $amount = 
                $form->round_amount(
                    $paid * ($form->{exchangerate} - 1) 
                        * $ml * -1, 
                    2);

            if ($amount) {
                # exchangerate difference
                $query = qq|
                    INSERT INTO acc_trans 
                                (trans_id, chart_id, 
                                transdate,
                                amount, source)
                          VALUES (?, ?, ?, ?, ?)|;

                
                $sth = $dbh->prepare($query);
                $sth->execute(
                    $form->{"id_$i"}, $accno_id, 
                    $form->{datepaid}, $amount, 
                    $form->{source})
                        || $form->dberror(
                            $query, 'CP.pm', 748);

                # gain/loss
                $amount = ($form->round_amount($paid * $exchangerate,2) - $form->round_amount($paid * $form->{exchangerate},2)) * $ml * -1;

                if ($amount) {
                    $accno_id = 
                        ($amount > 0) 
                        ? $fxgain_accno_id 
                        : $fxloss_accno_id;

                    $query = qq|
                        INSERT INTO acc_trans 
                                    (trans_id, 
                                    chart_id, 
                                    transdate,
                                    amount, 
                                    fx_transaction)
                            VALUES (?, ?, ?, ?, '1')|;

                    $sth = $dbh->prepare($query);
                    $sth->execute(
                        $form->{"id_$i"}, $accno_id,
                        $form->{datepaid}, $amount)
                            || $form->dberror(
                                $query, 
                                'CP.pm', 775);
                }
            }

            $paid = $form->round_amount($paid * $exchangerate, 2);

            $pth->execute($form->{"id_$i"}) || $form->dberror;
            ($amount) = $pth->fetchrow_array;
            $pth->finish;

            $amount += $paid;

            # update AR/AP transaction
            $query = qq|
                UPDATE $form->{arap} 
                   SET paid = $amount,
                       datepaid = '$form->{datepaid}'
                 WHERE id = $form->{"id_$i"}|;

            $sth = $dbh->prepare($query);
            $sth->execute(
                $amount, $form->{datepaid}, $form->{"id_$i"})
                    || $form->dberror($query, 'CP.pm', 
                        796);

            %audittrail = ( tablename  => $form->{arap},
                            reference  => $form->{source},
                            formname   => $form->{formname},
                            action     => 'posted',
                            id         => $form->{"id_$i"} );

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

        }

        $sameid = $form->{"$form->{vc}_id_$i"};

    }

    # record a AR/AP with a payment
    if ($overpayment > 0 && $form->{$form->{ARAP}}) {
        $form->{invnumber} = "";
        OP::overpayment("", $myconfig, $form, $dbh, $overpayment, $ml, 1);
    }

    my $rc = $dbh->commit;

    $rc;

}


1;