#!/usr/bin/perl
use strict;
use DBI;
use CGI qw(:standard);

my $dblogon = "logon";
my $dbpass = "password";
my $dbsid = "sid";
my $dbhost = "server";
my $printer = "printername";
$ENV{"ORACLE_HOME"}="/oracle/software/8.1.5";

#Print out the html header
print header;

#Open up the Printer File Handle
open (LPR, "|lpr -P$printer") or die("Can't open printing pipe");

#Open up the database.
my $dbh=DBI->connect( "dbi:Oracle:host=$dbhost;sid=$dbsid", "$dblogon", "$dbpass") or die("FATAL ERROR: Can't connect to $dbsid on $dbhost with $dblogon , $dbpass\n$DBI::errstr\n");
my ($pst,$pst2);

#Define the Variables and formats that we're going to use
my ($name1,$address1,$address2,$city,$state,$zipcode,$branch);
format label =
@<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$name1
Branch : @<
$branch
@<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$address1
@<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$address2
@<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<, @< @<<<<<<<<<<
$city,$state,$zipcode
.


my $oldhandle = select LPR;
$~ = "label";
select ($oldhandle);

#prepare the statements that we're going to use.
$pst = $dbh->prepare("select BRANCHNAME,BRANADD1,BRANADD2,BRANCITY,BRANSTATE,BRANZIP,BRANCHCODE from bds.branch");
$pst2 = $dbh->prepare("select BRANCH from bds.rep where termdate is NULL and BRANCH= ?");

$pst -> execute();
my $cnt=0;

print "Now Printing Label for:<br>";

my $brancha;

while(($name1,$address1,$address2,$city,$state,$zipcode,$brancha)= $pst->fetchrow_array){
	$pst2->bind_param( 1, $brancha);
	$pst2 -> execute();
	if($branch = $pst2 -> fetchrow_array){ #Test for Active Brokers
		print $branch."<br>";
		print LPR "\n";
		write (LPR);
		print LPR "\n\n";
		$cnt++;
		}
	else {
		print "skipping branch: ".$brancha." (no active brokers).<br>";
		}
}

#print out a status sheet.
$name1="Label Run Finsihed Successfully";
$branch="--";
$address1=$cnt." Labels Ran.";
$address2="-------------------------------------------------------------------------";
$city=$state=$zipcode="-------------------------------------------------------------";
print LPR "\n";
write (LPR);
print LPR "\n\n";
print "Label Run Successful.<br>".$cnt." Labels Ran.<br>";
print end_html;