LedgerSMB/BP.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.
#
#======================================================================
#
# Batch printing module backend routines
#
#======================================================================

package BP;
use LedgerSMB::Sysconfig;

sub get_vc {

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

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

    my %arap = (
        invoice           => ['ar'],
        packing_list      => [ 'oe', 'ar' ],
        sales_order       => ['oe'],
        work_order        => ['oe'],
        pick_list         => [ 'oe', 'ar' ],
        purchase_order    => ['oe'],
        bin_list          => ['oe'],
        sales_quotation   => ['oe'],
        request_quotation => ['oe'],
        timecard          => ['jcitems'],
        check             => ['ap'],
    );

    my $query = "";
    my $sth;
    my $n;
    my $count;
    my $item;
    my $sth;

    $item = $form->{dbh}->quote($item);
    foreach $item ( @{ $arap{ $form->{type} } } ) {
        $query = qq|
            SELECT count(*)
              FROM (SELECT DISTINCT vc.id
                  FROM $form->{vc} vc, $item a, status s
                 WHERE a.entity_id = vc.entity_id
                           AND s.trans_id = a.id
                           AND s.formname = ?
                           AND s.spoolfile IS NOT NULL) AS total|;

        $sth = $dbh->prepare($query);
        $sth->execute( $form->{type} );
        ($n) = $sth->fetchrow_array;
        $count += $n;
    }

    # build selection list
    my $union = "";
    $query = "";
    my @queryargs = ();

    if ( $count < $myconfig->{vclimit} ) {

        foreach $item ( @{ $arap{ $form->{type} } } ) {
            $query .= qq| 
                $union
                SELECT DISTINCT vc.id, vc.name
                  FROM $item a
                  JOIN $form->{vc} vc 
                       USING (entity_id)
                  JOIN status s ON (s.trans_id = a.id)
                 WHERE s.formname = ?
                       AND s.spoolfile IS NOT NULL|;
            $union = "UNION";
            push @queryargs, $form->{type};
        }

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

        while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
            push @{ $form->{"all_$form->{vc}"} }, $ref;
        }

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

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

}

sub get_spoolfiles {

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

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

    my $query;
    my $invnumber = "invnumber";
    my $item;

    my %arap = (
        invoice           => ['ar'],
        packing_list      => [ 'oe', 'ar' ],
        sales_order       => ['oe'],
        work_order        => ['oe'],
        pick_list         => [ 'oe', 'ar' ],
        purchase_order    => ['oe'],
        bin_list          => ['oe'],
        sales_quotation   => ['oe'],
        request_quotation => ['oe'],
        timecard          => ['jc'],
        check             => ['ap'],
    );

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

    my @queryargs;
    if ( $form->{type} eq 'timecard' ) {
        my $dateformat = $myconfig->{dateformat};
        $dateformat =~ s/yy/yyyy/;
        $dateformat =~ s/yyyyyy/yyyy/;

        $invnumber = 'id';

        $query = qq|
            SELECT j.id, e.name, j.id AS invnumber,
                   to_char(j.checkedin, ?) AS transdate,
                   '' AS ordnumber, '' AS quonumber, '0' AS invoice,
                   '$arap{$form->{type}}[0]' AS module, s.spoolfile
              FROM jcitems j
              JOIN employees e ON (e.id = j.employee_id)
              JOIN status s ON (s.trans_id = j.id)
             WHERE s.formname = ?
                   AND s.spoolfile IS NOT NULL|;
        @queryargs = ( $dateformat, $form->{type} );

        if ( $form->{"$form->{vc}_id"} ) {
            $query .= qq| AND j.$form->{vc}_id = ?|;
            push( @queryargs, $form->{"$form->{vc}_id"} );
        }
        else {

            if ( $form->{ $form->{vc} } ) {
                $item = $form->like( lc $form->{ $form->{vc} } );
                $query .= " AND lower(e.name) LIKE ?";
                push( @queryargs, $item );
            }
        }

        if ( $form->{transdatefrom} ) {
            $query .= " AND j.checkedin >= ?";
            push( @queryargs, $form->{transdatefrom} );
        }
        if ( $form->{transdateto} ) {
            $query .= " AND j.checkedin <= ?";
            push( @queryargs, $form->{transdateto} );
        }
    }
    else {
        @queryargs = ();

        foreach $item ( @{ $arap{ $form->{type} } } ) {

            $invoice   = "a.invoice";
            $invnumber = "invnumber";

            if ( $item eq 'oe' ) {
                $invnumber = "ordnumber";
                $invoice   = "'0'";
            }

            $query .= qq| 
                $union
                SELECT a.id, c.legal_name AS name, a.$invnumber AS invnumber, a.transdate,
                       a.ordnumber, a.quonumber, $invoice AS invoice,
                       '$item' AS module, s.spoolfile
                  FROM $item a, $form->{vc} vc, status s, company c
                 WHERE s.trans_id = a.id
                       AND s.spoolfile IS NOT NULL
                       AND s.formname = ?
                       AND c.entity_id = vc.entity_id
                       AND a.entity_id = vc.entity_id|;

            push( @queryargs, $form->{type} );
            if ( $form->{"$form->{vc}_id"} ) {
                $query .= qq| AND a.entity_id = $form->{"entity_id"}|;
            }
            else {

                if ( $form->{ $form->{vc} } ne "" ) {
                    $item = $form->like( lc $form->{ $form->{vc} } );
                    $query .= " AND lower(c.legal_name) LIKE ?";
                    push @queryargs, $item;
                }
            }

            if ( $form->{invnumber} ne "" ) {
                $item = $form->like( lc $form->{invnumber} );
                $query .= " AND lower(a.invnumber) LIKE ?";
                push @queryargs, $item;
            }

            if ( $form->{ordnumber} ne "" ) {
                $item = $form->like( lc $form->{ordnumber} );
                $query .= " AND lower(a.ordnumber) LIKE ?";
                push @queryargs, $item;
            }

            if ( $form->{quonumber} ne "" ) {
                $item = $form->like( lc $form->{quonumber} );
                $query .= " AND lower(a.quonumber) LIKE ?";
                push @queryargs, $item;
            }

            if ( $form->{transdatefrom} ) {
                $query .= " AND a.transdate >= ?";
                push @queryargs, $form->{transdatefrom};
            }
            if ( $form->{transdateto} ) {
                $query .= " AND a.transdate <= ?";
                push @queryargs, $form->{transdateto};
            }
            $union = "UNION";

        }
    }

    my %ordinal = (
        'name'      => 2,
        'invnumber' => 3,
        'transdate' => 4,
        'ordnumber' => 5,
        'quonumber' => 6,
    );

    my @a = ();
    push @a, ( "transdate", "$invnumber", "name" );
    my $sortorder = $form->sort_order( \@a, \%ordinal );
    $query .= " ORDER by $sortorder";

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

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

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

}

sub delete_spool {

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

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

    my $query;
    my %audittrail;

    $query = qq|
        UPDATE status 
           SET spoolfile = NULL
         WHERE spoolfile = ?|;

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

    foreach my $i ( 1 .. $form->{rowcount} ) {

        if ( $form->{"checked_$i"} ) {
            $sth->execute( $form->{"spoolfile_$i"} ) || $form->dberror($query);
            $sth->finish;

            %audittrail = (
                tablename => $form->{module},
                reference => $form->{"reference_$i"},
                formname  => $form->{type},
                action    => 'dequeued',
                id        => $form->{"id_$i"}
            );

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

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

    if ($rc) {
        foreach my $i ( 1 .. $form->{rowcount} ) {
            $_ = qq|${LedgerSMB::Sysconfig::spool}/$form->{"spoolfile_$i"}|;
            if ( $form->{"checked_$i"} ) {
                unlink;
            }
        }
    }

    $rc;
}

sub print_spool {

    my ( $self, $myconfig, $form ) = @_;
    ##SC: XXX May need to be changed after hooking up printing to templates

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

    my %audittrail;

    my $query = qq|UPDATE status 
                      SET printed = '1'
                    WHERE spoolfile = ?|;

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

    foreach my $i ( 1 .. $form->{rowcount} ) {

        if ( $form->{"checked_$i"} ) {
            open( OUT, '>', $form->{OUT} ) or $form->error("$form->{OUT} : $!");
            binmode(OUT);

            $spoolfile = qq|$spool/$form->{"spoolfile_$i"}|;

            # send file to printer
            open( IN, '<', $spoolfile ) or $form->error("$spoolfile : $!");
            binmode(IN);

            while (<IN>) {
                print OUT $_;
            }

            close(IN);
            close(OUT);

            $sth->execute( $form->{"spoolfile_$i"} ) || $form->dberror($query);
            $sth->finish;

            %audittrail = (
                tablename => $form->{module},
                reference => $form->{"reference_$i"},
                formname  => $form->{type},
                action    => 'printed',
                id        => $form->{"id_$i"}
            );

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

            $dbh->commit;
        }
    }

}

1;