#=====================================================================
# 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.$form->{vc}_id = vc.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 
				       ON (a.$form->{vc}_id = vc.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 employee 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, vc.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
				 WHERE s.trans_id = a.id
				       AND s.spoolfile IS NOT NULL
				       AND s.formname = ?
				       AND a.$form->{vc}_id = vc.id|;

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

				if ($form->{$form->{vc}} ne "") {
					$item = $form->like(
						lc $form->{$form->{vc}});
					$query .= " AND lower(vc.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) = @_;

	# 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;