package create_http_xml;
#
#######################################################################
# This software has been created by Genome Research Limited (GRL).    # 
# GRL hereby grants permission to use, copy, modify and distribute    # 
# this software and its documentation for non-commercial purposes     # 
# without fee at the user's own risk on the basis set out below.      #
# GRL neither undertakes nor accepts any duty whether contractual or  # 
# otherwise in connection with the software, its use or the use of    # 
# any derivative, and makes no representations or warranties, express #
# or implied, concerning the software, its suitability, fitness for   #
# a particular purpose or non-infringement.                           #
# In no event shall the authors of the software or GRL be responsible # 
# or liable for any loss or damage whatsoever arising in any way      # 
# directly or indirectly out of the use of this software or its       # 
# derivatives, even if advised of the possibility of such damage.     #
# Our software can be freely distributed under the conditions set out # 
# above, and must contain this copyright notice.                      #
#######################################################################
#
####################################################
# Generates the External Oligo order for the new   #
# http system.                                     #
#                                                  #
# Author : Karen Ambrose                           #
#                                                  #
####################################################

#We need to use some things
use strict;

use vars qw(@ISA @EXPORT);
use Exporter;
@ISA = ('Exporter');
@EXPORT = qw(&primer_order_xml);
require Exporter;

use Checkbarcode;
use WrapDBI;
use SDTable;
use Mail::Mailer;
use ftp_order_handling;
#require "$ENV{'HOME'}/badger-bin/finch/ftp_order_handling.pm";

##only run this module for real orders not reorders.
sub primer_order_xml {

    my ($dbh, $db, $who, $session_id, $lps, $reorder)=@_;
 
    print "Within create_http_xml.pm module.\n";

#check put in place to check wether the $dbh is present 
    if (!$dbh){
        my $dbh = WrapDBI->connect("$db", {RaiseError => 1});    
        $dbh->{autocommit} = 0;
    }
    
    my $prog;
    if ($reorder eq 'reorder') {
        $prog = "REORDER"; 
    } else {
        $prog = "External Primer Order-HTTPS";
    }
    
###email address for reorders
###for testing only
#    my @list = ("kva\@sanger.ac.uk");
#    my $all_recipients=\@list;
#    my $from_ftp = "ftp-primer\@sanger.ac.uk";
#    my $recipient_list = join ',',@$all_recipients;

#real recipients
    my @list = ("sblackwe\@europe.sial.com","PTiku\@europe.sial.com","sanger\@europe.sial.com","team58g\@sanger.ac.uk","kva\@sanger.ac.uk");
    my $all_recipients=\@list;
    my $from_ftp = "ftp-primer\@sanger.ac.uk";

    my $recipient_list = join ',',@$all_recipients;

#any account details 
    my $account_num = "5541111002"; #account number from SIGMA

#place all the lpid's in an array
#push it onto an array for the loadingplate ID to be processed.
    my @lpid;
    push @lpid, $lps;
    
#may need a check to validate that the loadingplate is true
    my $stat1 = "select id_loadingplate
from loadingplate
where id_loadingplate=?";
    my $sth1 = $dbh->prepare($stat1) || die "ERROR:$DBI::errstr";    

#need to check wether the projects on the plate
#contain priority projects and if so the plates
#will be flagged up as priority 1 (high), if not
#they will be just be set to 3 (normal).
    my $stat1a = "select distinct d.projectname, NVL(e.project_type,'0') 
from loadingsample a, request_map b, finish_request c, finish_batch d, project e
where a.id_loadingsample = b.id_loadingsample
and b.id_frequest = c.id_frequest
and c.id_fbatch = d.id_fbatch
and d.projectname = e.projectname
and a.id_loadingplate = ?";

    my $stat1b = "select projectname 
from project_finishingproblem 
where projectname = ? 
and priority = 1
and resolved_date is null";
    my $sth1b = $dbh->prepare($stat1b) || die "ERROR:$DBI::errstr";

#Obtain purchase order number for this month.
#my $stat_pon = "select ID_PURCHASEORDER from purchaseorderdict where to_char(assign_date, 'MON-YY')=(select to_char(sysdate, 'MON-YY') from dual)";
    my $stat_pon = "select ID_PURCHASEORDER from purchaseorderdict where iscurrent=1";
    my $sth_pon = $dbh->prepare($stat_pon) || die "ERROR:$DBI::errstr";
    
#Obtain the date the order was made, so that the info can be added to
#the XML tag.
    my $stat_date = "select to_char(sysdate, 'DD/MM/YYYY') from dual";
    my $sth_dt = $dbh->prepare($stat_date) || die "ERROR:$DBI::errstr";
    
#obtain the teamname, mapplate_id, and fin ID for the information
#to be placed next to the barcode label.
    my $label_info = "select distinct a.team, c.id_mapplate
from loadingplate a, loadingsample b, request_map c
where a.id_loadingplate = b.id_loadingplate
and b.id_loadingsample=c.id_loadingsample
and a.id_loadingplate = ?";
    my $sth_label = $dbh->prepare($label_info) || die "ERROR:$DBI::errstr";

##BLOCKED OUT UNTIL THE FULL SWITCH TO THE HTTP SYSTEM IS ACTIONED - KVA 30-03-06 
##Generate the primerplate ID.
#    my $stat2 = "select seq_primerplate.NEXTVAL from dual";
#    my $sth2 = $dbh->prepare($stat2) || die "ERROR:$DBI::errstr";
#obtain the primerplate_id for a specified loadingplate
    my $stat2 = "select id_primerplate from loadingplate where id_loadingplate = ?";
    my $sth2 = $dbh->prepare($stat2) || die "ERROR:$DBI::errstr";

#need to insert the data into the primerplate table using the seq.nextval
#generated from the seq.primerplate entity.
    my $stat3 = "insert into primer_plate (ID_PRIMERPLATE,SUPPLIED_BY,FORMAT,ID_PURCHASEORDER) values (?, ?, ?, ?)";
    my $sth3 = $dbh->prepare($stat3) || die "ERROR:$DBI::errstr";
    
#also need to add an 'ordered' status to the primerplate_status table.
#if the plate is a reorder, a 'reordered' status is given.
    my $stat4 = "insert into primerplate_status (ID_PRIMERPLATE,STATUS,STATUSDATE,ISCURRENT,SESSIONID,PROGRAM,OPERATOR) values (?, ?, sysdate, 1, ?, ?, ?)";
    my $sth4 = $dbh->prepare($stat4) || die "ERROR:$DBI::errstr";

#update the existing loadingplate entry in the 
#loadingplate table with the primerplate ID.
    my $stat_lppp = "update loadingplate set id_primerplate = ? where id_loadingplate = ?";
    my $sth_lppp = $dbh->prepare($stat_lppp) || die "ERROR:$DBI::errstr";  
 
#obtain the total number of distinct primers in the order.
    my $stat_pcount = "select count(distinct d.id_primer)
from request_map b, finish_request c, primer d, loadingsample e, loadingplate f
where b.id_frequest = c.id_frequest
and c.id_primer = d.id_primer
and b.id_loadingsample = e.id_loadingsample
and e.id_loadingplate = f.id_loadingplate
and f.id_loadingplate = ?";
    my $sth_pcount = $dbh->prepare($stat_pcount) || die "ERROR:$DBI::errstr";
    
#Obtains the primer order information
#Doesn't need a prepare statement, as is using a selectall_arrayref
#which uses the $dbh.
    my $stat5 = "select a.id_map, a.maprow, a.mapcol, d.id_primer, d.primername, d.primerseq, d.projectname
from rowcol_map a, request_map b, finish_request c, primer d, loadingsample e, loadingplate f
where b.id_map = a.id_map
and b.id_frequest = c.id_frequest
and c.id_primer = d.id_primer
and b.id_loadingsample = e.id_loadingsample
and e.id_loadingplate = f.id_loadingplate
and f.id_loadingplate = ?
order by a.id_map";
    
#while this is fetching the data back, need to populate the primer_map table
    my $stat6 = "insert into primer_map (ID_PRIMER,ID_MAP,ID_PRIMERPLATE) values (?,?,?)";
    my $sth6 = $dbh->prepare($stat6) || die "ERROR:$DBI::errstr";
    
    
#generate this information for this session.
#Obtain purchase order number.
    $sth_pon->execute || die "Problems obtaining purchase order number - ERROR:$DBI::errstr"; 
    my $ponumber=$sth_pon->fetchrow();

#Obtain orderdate.
    $sth_dt->execute || die "Problems obtaining order date - ERROR:$DBI::errstr";
    my $orderdate=$sth_dt->fetchrow();
 
#Check for the original loadingplate ID
#if this is a reorder.
    my $stat7 = "select distinct ls.id_loadingplate from loadingplate_status ls, loadingplate lp where lp.id_loadingplate = ls.id_loadingplate and ls.program != 'REORDER' and lp.id_primerplate in (select id_primerplate from loadingplate where id_loadingplate = ?)";
    my $sth7 = $dbh->prepare($stat7) || die "ERROR:$DBI::errstr";
    
#Obtain the prefix for a specified team
    my $stat_ptpd = "select prefix from purchase_team_prefix_dict where team = ?";
    my $sth_ptpd = $dbh->prepare($stat_ptpd) || die "ERROR:$DBI::errstr";

    foreach my $lpid (@lpid) {

#validate the loadingplate ID
        $sth1->bind_param(1, $lpid);
        $sth1->execute || die "Problems validating lp ID - ERROR:$DBI::errstr\n";
        my $validlp=$sth1->fetchrow();
        
        #number of primers in order.
        my $pcount;        
        if ($validlp){
            $sth_pcount->bind_param(1, $lpid);
            $sth_pcount->execute || die "Problems obtaining primer order total - ERROR:$DBI::errstr";
            $pcount =$sth_pcount->fetchrow();
            
            if ($pcount == 0) {
                print "No primers found, no external order generated.\n";
                next;
            }
        }
        
        if (! $validlp){
            print "This ID $lpid isn't a valid loadingplate ID\n";
            die;
            
        }else{
            
#get project type information on the relevant
#projects linked to a plate.
            my $PRIORITY;
            my $proj_type_ref = $dbh->selectall_arrayref($stat1a,{},$lpid) || die "Problems extracting project_type information - ERROR:$DBI::errstr\n";
            foreach my $ptr (@$proj_type_ref){
                
                if (@{$ptr}[1]=~ /2|5|8/){
                    $PRIORITY='1';
                    
                } else {
                    
                    $sth1b->execute(@{$ptr}[0]) || die "Problems checking priority - ERROR:$DBI::errstr\n";
                    my $priority_proj = $sth1b->fetchrow();
                    if ($priority_proj){
                        $PRIORITY='1';
                    } 
                }
                
            }
            
            if (!$PRIORITY){
                $PRIORITY='3'; 
            }
            
            #If the plate is a reorder set to 1 (high).
            if ($reorder eq 'reorder') {
                $PRIORITY='1'; 
            }
            
#generate barcode 12 digit number to become the 
#filename and thus be incorporated on the barcode
#of the ordered primer plate
            my ($lpbarcode, $lphuman) = Calculatebarcode('LP',$lpid);
            #print "$lpbarcode $lphuman\n";
            
#obtain the teamname, mapplateid, and fin ID for barcode label info
            $sth_label->bind_param(1, $lpid);
            $sth_label->execute || die "Problems obtaining label info - ERROR:$DBI::errstr\n";
            my ($lpteam, $id_mapplate)=$sth_label->fetchrow();

#obtain the prefix for a specified team (purchasing info)
            $sth_ptpd->bind_param(1, $lpteam);
            $sth_ptpd->execute || die "Problems obtaining team prefix info - ERROR:$DBI::errstr\n";
            my ($prefix)=$sth_ptpd->fetchrow();

            my $pp_id; 
#Get the existing primerplate ID if it is a reorder.           
            if ($reorder eq 'reorder') {
                my $stat_reopp = "select id_primerplate from loadingplate where id_loadingplate = ?";
                my $sth_reopp = $dbh ->prepare($stat_reopp);
                $sth_reopp->execute($lpid) || die "Problems obtaining primerplate information for the reorder info - ERROR:$DBI::errstr\n";
                $pp_id=$sth_reopp->fetchrow();
                $sth_reopp->finish();   
                print ">>primerplate id : $pp_id<<\n";
            }
            
            
#Generate the primerplate ID.            
            unless ($reorder eq 'reorder') {
                $sth2->execute($lpid) || die "Problems generating pp ID - ERROR:$DBI::errstr\n";
                $pp_id=$sth2->fetchrow();
                #print ">>primerplate id : $pp_id<<\n"; 
            }
            
#Obtain the primerplate barcode ID.
            my ($ppbarcode, $pphuman) = Calculatebarcode('PP', $pp_id);
            #print "primerplate: $ppbarcode $pphuman\n"; 
     
##BLOCKED OUT UNTIL THE FULL SWITCH TO THE HTTP SYSTEM IS ACTIONED - KVA 30-03-06      
##insert the data into the primerplate table.
#            unless ($reorder eq 'reorder') {
#                $sth3->bind_param(1, $pp_id);
#                $sth3->bind_param(2, '66');  #Sigma organisation ID
#                $sth3->bind_param(3, '96');  #plate format
#                $sth3->bind_param(4, $ponumber);  #purchase order number
#                $sth3->execute || die "Problems inserting data into primerplate table - ERROR:$DBI::errstr\n";
#                
#                # dn1
#                # Output the primer_plate made
#                print "Primer plate entry: $pp_id\n";
#            }

#if the primerplate is to be reordered again, 
#render the previous status no current and 
#create a new ordered status.
            if ($reorder eq 'reorder') {
                my $stat_updpp = "update primerplate_status set iscurrent = 0 where id_primerplate = ? and iscurrent = 1";
                my $sth_updpp = $dbh ->prepare($stat_updpp);
                $sth_updpp->execute($pp_id) || die "Problems updating current status entries for primerplate to non-current - ERROR:$DBI::errstr\n";
                $sth_updpp->finish();  
            }

##BLOCKED OUT UNTIL THE FULL SWITCH TO THE HTTP SYSTEM IS ACTIONED - KVA 30-03-06             
##add a status of 'ordered' to primerplate_status table.
#            $sth4->bind_param(1, $pp_id);
#            if ($reorder eq 'reorder'){
#		$sth4->bind_param(2, '4');
#	    } else {
#		$sth4->bind_param(2, '1');
#	    }
#            $sth4->bind_param(3, $session_id);
#            $sth4->bind_param(4, $prog);
#            $sth4->bind_param(5, $who);
#            $sth4->execute || die "Problems adding status to primerplate_status table - ERROR:$DBI::errstr";
            

##BLOCKED OUT UNTIL THE FULL SWITCH TO THE HTTP SYSTEM IS ACTIONED - KVA 30-03-06             
##update the existing loadingplate entry in the 
##loadingplate table with the primerplate ID.
#            unless ($reorder eq 'reorder') {
#                $sth_lppp->bind_param(1, $pp_id); #primerplate ID
#                $sth_lppp->bind_param(2, $lpid);  #loadingplate ID  
#                $sth_lppp->execute || die "Problems inserting primerplate ID into loadingplate table - ERROR:$DBI::errstr\n";
#            }
            
#Get the primer map order.
            my $order_info;

            unless ($reorder eq 'reorder') {                       
                $order_info = $dbh->selectall_arrayref($stat5,{},$lpid) || die print "Cannot execute statement to extract primer map order: $DBI::errstr";
                
            }
            
#give some idea as to where the order was sent            
            if ($reorder eq 'reorder') {
                print "\tXML header output reoorder is going to be sent to $recipient_list\n";
            }

#array to hold the contents of the file
            my @output_file;
         
            my @clist = localtime(time);

            if (length(@clist[1])==1){
                @clist[1]='0'.@clist[1];
            }

            if (length(@clist[0])==1){
                @clist[0]='0'.@clist[0];
            }
            @clist[5] = (@clist[5]+1900);
            @clist[4] = (@clist[4]+1);

            if (length(@clist[3])==1){
                @clist[3]='0'.@clist[3];
            }

            if (length(@clist[4])==1){
                @clist[4]='0'.@clist[4];
            }

            my $c_date = @clist[5].'-'.@clist[4].'-'.@clist[3].'T'.@clist[2].':'.@clist[1].':'.@clist[0];
#            print "time list - @clist $c_date\n";
            
            my $time = @clist[5].@clist[4].@clist[3].@clist[2].@clist[1].@clist[0];
            my $random_num = $ponumber.'.'.$pp_id.'.'.$time;
            print "Random number $random_num \n";

            my $from = "team58g\@sanger.ac.uk";
            my $to = "sysadmin\@europe.sial.com";
            my $sender = "ftp-primer\@sanger.ac.uk";
            my $secret = "lemmings jumping into the river";

#start of the XML format of the file
            push @output_file, "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
            push @output_file, "<!DOCTYPE Sanger SYSTEM \"sigma_primer_order.dtd\">";
            push @output_file, "<Sanger payloadID=\"$random_num\@sanger.ac.uk\" xml:lang=\"en-US\" timestamp=\"$c_date\">";
            push @output_file, "<Header>";

            push @output_file, "<From>";
            push @output_file, "<Credential domain=\"custom\">";
            push @output_file, "<Identity>$sender</Identity>";
            push @output_file, "</Credential>";
            push @output_file, "</From>";
            
            push @output_file, "<To>";
            push @output_file, "<Credential domain=\"custom\">";
            push @output_file, "<Identity>$to</Identity>";
            push @output_file, "</Credential>";
            push @output_file, "</To>";
            
            push @output_file, "<Sender>";
            push @output_file, "<Credential domain=\"custom\">";
            push @output_file, "<Identity>$from</Identity>";
            push @output_file, "<SharedSecret>$secret</SharedSecret>";
            push @output_file, "</Credential>";
            push @output_file, "</Sender>";

            push @output_file, "</Header>";

#Request part of the xml order file
            push @output_file, "<Request>";
            push @output_file, "<OrderRequest ID=\"$prefix$ponumber\">";
            push @output_file, "<OligoOrder ID=\"Root\">";
            push @output_file, "<OligoHeader>";

            
#obtained from SIGMA and hardcoded in
            push @output_file, "<AccountNumber>$account_num</AccountNumber>";
            
#may need to make a file or database table to house the 12 purchase 
#order numbers, so that it takes a new number each month.
            push @output_file, "<PONumber>$prefix$ponumber</PONumber>";

####KVA_ADD - NEED TO ENTER INFORMATION FOR PRIORITY TAG <PRIORITY></PRIORITY>
##ANYTHING WITH A REPOSITORYTYPEDICT OF 2/5/8 NEED TO BE TAGGED WITH A
##PRIORITY OF 1 (HIGH). Also projects that are in the PROJECT_FINISHINGPROBLEM
##table with a priority of one are also tagged as HIGH priority.
##Normal orders will be tagged with 3 (NORMAL),
##REORDERS WILL BE TAGGED WITH 1 (HIGH).
            push @output_file, "<Priority>$PRIORITY</Priority>";

#get the human readable and 12 digit barcode number for both
#the loadingplate and the primerplate information.          
            push @output_file, "<StockPlateNumber>$ppbarcode</StockPlateNumber>";
            push @output_file, "<StockPlateID>$pphuman</StockPlateID>";
            push @output_file, "<WorkingPlateNumber set=\"1\">$lpbarcode</WorkingPlateNumber>";
            push @output_file, "<WorkingPlateID set=\"1\">$lphuman</WorkingPlateID>";
            
            push @output_file, "<OrderDate>$orderdate</OrderDate>";
            push @output_file, "<PersonName>sm2</PersonName>";

#info for the barcode label
            push @output_file, "<Comment1 ID=\"Team\">$lpteam</Comment1>";
            push @output_file, "<Comment2 ID=\"MPID\">$id_mapplate</Comment2>";
            push @output_file, "<Comment3 ID=\"LPID\">$lphuman</Comment3>";
            
#number of unique oligo's ordered
            push @output_file, "<OligoCount>$pcount</OligoCount>";
            push @output_file, "</OligoHeader>";
            
            unless ($reorder eq 'reorder') {
                push @output_file, "<OligoDetail ID=\"$prefix$ponumber\">";   
                
                
                my $up=1;
                my %OHASH;
                my %SHASH;
                my %HNAMES;
                my $COUNT=1;
                
#this is done to ensure that the primers still
#remain in the order of the map. If change the 
#sql to not order by id_map, then this can no 
#longer be relied upon.
                foreach my $get_name (@{$order_info}){
                    
                    #ensures that primer 1 for one project
                    #dosen't get mixed up with primer 1 from 
                    #another project for plates with different
                    #projects on them
                    my $unique_primerid = @{$get_name}[6]."_".@{$get_name}[4];
                    
                    $OHASH{$unique_primerid}=$COUNT;                     #order the primers in a hash
                    $SHASH{$unique_primerid}=@{$get_name}[5];            #place sequences into a hash
                    push (@{$HNAMES{$unique_primerid}},@{$get_name}[0]); #associated id_maps
                    $COUNT++;
                }

##BLOCKED OUT UNTIL THE FULL SWITCH TO THE HTTP SYSTEM IS ACTIONED - KVA 30-03-06                 
#                foreach my $in (@{$order_info}){
#                    
##populate the primer_map table.
#                    $sth6->bind_param(1, @{$in}[3]); #primer id
#                    $sth6->bind_param(2, @{$in}[0]); #id_map
#                    $sth6->bind_param(3, $pp_id);    #primerplate id (defined earlier)
#                    $sth6->execute || die "Problems populating primer_map table - ERROR:$DBI::errstr";
#                    
#                }
                
                
                foreach my $k (sort { $OHASH{$a} <=> $OHASH{$b} } keys %OHASH) {
                    
                    push @output_file, "<OligoLine ID=\"$up\">";
                    push @output_file, "<OligoName>$k</OligoName>";
                    push @output_file, "<OligoSequence>$SHASH{$k}</OligoSequence>";
                    push @output_file, "<OligoScale>0.025</OligoScale>";
                    push @output_file, "<OligoPurification>DST</OligoPurification>";
                    push @output_file, "<OligoNotes>[ALIQUOT] 2PMOL Dried Sanger</OligoNotes>"; 
                    
                    my $totalwells = scalar (@{$HNAMES{$k}});
                    push @output_file, "<OligoWells>$totalwells</OligoWells>";
                    push @output_file, "</OligoLine>"; 
                    
                    #count for the oligoline ID
                    $up++;
                }
                
#End of the XML order format.
                push @output_file, "</OligoDetail>"; 
                
            } #end of exclusion from reorder file
            
            push @output_file, "</OligoOrder>";        
            push @output_file, "</OrderRequest>";
            push @output_file, "</Request>";
            push @output_file, "</Sanger>";
            
  
            if  ($reorder eq 'reorder') {

#get the original lp information.
#and generate a table of summary information
#to be placed at the bottom of the mail message.
                $sth7->bind_param(1, $lpid);
                $sth7->execute || die "Problems getting the original lp ID from which this reorder loadingplate has been duplicated - ERROR:$DBI::errstr\n";
                my $original_lp=$sth7->fetchrow();
                my ($orig_lpbarcode, $orig_lphuman) = Calculatebarcode('LP',$original_lp);

                my @r_cs;
                my $table1 = SDTable->new('format' => 'gaudy');
                $table1->set('title'=> "Summary of reorder information from plate number $ppbarcode", 'titles' => ["Original LP ID","Reorder LP ID","Reorder LoadingPlateNumber","Reorder Map ID","Team"]);
                
                my @cs=("$orig_lphuman","$lphuman","$lpbarcode","$id_mapplate","$lpteam");
                push (@r_cs, \@cs);
                
                foreach my $rc (@r_cs){
                    $table1->add(@$rc);
                }
                
#generate an email for the reorder
                foreach my $email (@$all_recipients){
                    my $SUBJECT = "Reorder from primerplate ".$pp_id;
                    my ($mailer);
                    $mailer = new Mail::Mailer 'smtp', Server => "localhost";
                    $mailer->open({
                        From     =>  $from_ftp,
                        To       =>  $email,
                        Subject  =>  $SUBJECT,
                    }) or die "Cannot open: $!\n";
                    
                    print $mailer "The xml header order below, details the REORDER of a new loadingplate from the existing stockplate (primerplate) $pp_id. Could you please acknowledge receipt of this reorder with a return email. Please get in contact with dn1g if the information below isn't clear.\n"; 
                    print $mailer "Purchase order number :- $prefix$ponumber\n\n";
                    print $mailer "REF :- Replacement (PRIORITY)\n\n";
                    foreach (@output_file){
                        print $mailer "$_\n";
                    }
                    print $mailer "\n\n";
                    print $mailer $table1->format();
                    print $mailer "\n\n";
                    print $mailer "Regards dn1g\n";
                    $mailer->close();
                }
                
            } else {

                #add in the link to the FTP_ORDER_HANDLING Module.
                #which will handle placeing ftp files in the 
                #required directory.
                &ftp_order_handling::ftp_order($lpid,\@output_file,'https');

            }            
            
        }
        
    }

#end the statement handles
    $sth1->finish;
    $sth2->finish;
    $sth_lppp->finish;
    $sth3->finish;
    $sth4->finish;
    $sth_pcount->finish;
    $sth6->finish;
    $sth7->finish;
    $sth_dt->finish;
    $sth_label->finish;
    
    print "\t$prog - population finished\n\n";
}

1;
