#!/usr/local/bin/perl 
#
#  Create XML to send all customer details for a country
#
#  Daryl Greensill
#

  use Mail::Sendmail;
  use lib "/home/intranet/cgi-bin";
  use lib "/home/intranet/intra/supplies/auto";
  use lib "/home/intranet/intra/supplies/auto/scripts";
  use LWP::UserAgent;

  require "cgi.ph" ;
  require "stdfunc.ph" ;
  require "sql.ph" ; 
  require "DataTidy.pl" ;

# The list of email addresses that will be sent to if an error occurs during transmission                                                                       
  $failmail = 'chris.mcpartland@gis.dhl.com';          
                                                                                
# If the transmission is successfull then these addresses will be emailed with the number of orders transmitted                                                 
  $successmail = 'chris.mcpartland@gis.dhl.com';

# The filename to be created
  $file = "/home/intranet/intra/supplies/admin/test.xml" ;

# Add the standard xml head line to the output file.
  $prnt = "<?xml version=\"1.0\" encoding=\"iso-8859-1\" ?>\n" ;  

# Add the line saying that this is awb personalisation
  $prnt .= "<awb_pers>\n" ;  

  $conn = db_connect();  
  $rp = $conn->do("SET LOCK MODE TO WAIT 180") ;

# select the details of customers that are to be sent
  $sql = "SELECT DISTINCT custid, custnumber,custcountry,monthlyvolume " .
         "FROM awb_customer " .
         "WHERE custcountry='AL'";
  print $sql . "\n";
  @data = sql_data($conn, $sql);
  $count1 = $#data ;

# Loop through all of these customers
 for (my $i = 0; $i < @data; $i++)
  {

  	$customer = $data[$i]{0} ;

  	# The language that this is to be sent in.
  	# This is western by default.
  	$language = $data[$i]{6} ;

  print $i . "start\n\n";
  print $#data . "\n\n";

  	      # Add the top level customer tag
  $prnt1 = "<customer id=\"$i$data[$i]{0}\" account=\"$data[$i]{1}\" country=\"$data[$i]{2}\" volume=\"$data[$i]{3}\" lang=\"$language\">\n" ;

              print $i . "-" ;

  	      # Select the rest of the customers details
  	      $sql = "SELECT DISTINCT addressid, shiporrec, servicearea, contactname, " .
  	             "companyname, address1, address2, address3, country, postcode, " .
  	             "phone,senderref " .
  	             "FROM awb_address " .
  	             "WHERE " .
  	             "custid = $customer ";
  	      @data1 = sql_data($conn, $sql);
  	      
  print $i . "start\n\n";
  print $#data . "\n\n";
              # Add these addresses to the datafile
  	      for $j ( 0..$#data1 )
  	      {
                for $k (0..12)
                {
                  $data1[$j]{$k} = DataTidy($data1[$j]{$k});
                }
  	      	$cval = $data1[$j]{0}.$data1[$j]{1} ;
  	      	$prn = "<contact>$data1[$j]{3}</contact>\n" ;
  	      	$cval .= $data1[$j]{3} ;
  	      	$prn .= "<company>$data1[$j]{4}</company>\n" ;
  	      	$cval .= $data1[$j]{4} ;
  	      	$prn .= "<address position=\"1\">$data1[$j]{5}</address>\n" ;
  	      	$cval .= "1" . $data1[$j]{5} ;
  	      	$prn .= "<address position=\"2\">$data1[$j]{6}</address>\n" ;
  	      	$cval .= "2" . $data1[$j]{6} ;
  	      	$prn .= "<address position=\"3\">$data1[$j]{7}</address>\n" ;
  	      	$cval .= "3" . $data1[$j]{7} ;
  	      	$prn .= "<postcode>$data1[$j]{9}</postcode>\n" ;
  	      	$cval .= $data1[$j]{9} ;
  	      	if ($data1[$j]{1} eq "r") {
  	      		$prn .= "<country>$data1[$j]{8}</country>\n" ;
  	      		$cval .= $data1[$j]{8} ;
  	      	}
  	      	$prn .= "<sva_code>$data1[$j]{2}</sva_code>\n" ;
  	      	$cval .= $data1[$j]{2} ;
  	      	$prn .= "<phone>$data1[$j]{10}</phone>\n" ;
  	      	$cval .= $data1[$j]{10} ;
  	      	if ($data1[$j]{1} eq "s") {
  	      		$prn .= "<sendref>$data1[$j]{11}</sendref>\n" ;
  	      		$cval .= $data1[$j]{11} ;
  	      	}
  	      	
                # calculate the checksum and the line containing it.
  	      	$checksum = checksum($cval) ;
 	      	$prn = "<cdetail id=\"$data1[$j]{0}\" type=\"$data1[$j]{1}\" checksum=\"$checksum\">\n" . $prn . "</cdetail>\n" ;

                # replace all &s with &amp;
                #$prn =~ s/\&/\&amp;/sg;
 	      	$prnt1 .= $prn ;
  	      $prnt1 .= "</customer>\n" ;
        $prnt .= $prnt1 ;
  	      }
  	     
  	      
              # end of customer tags
  	      $prnt1 .= "</customer>\n" ;
        $prnt .= $prnt1 ;
  }
## End of each customer loop ##






# end of personalisation tag
 $prnt .= "</awb_pers>\n" ;  
 
 $xmlText = $prnt ;
 #$xmlText =~ s/</&lt;/g ;
 #$xmlText =~ s/>/&gt;/g ; 
 #$xmlText =~ s/\n/\n<br>/g ;
 
# Write the data to the datafile
 open(xmlfile, ">$file");
 print xmlfile "$prnt" ;
 close xmlfile ;
# print to standard out (log file)
 #print $prnt ;
  
 #print "$xmlText" ;

#   $ordercount = $count2 + 1;
    $ordercount = $i + 1;                                                  
  print "\nSending mail to mcp confirming how many customers ($ordercount) have been transmitted.\n";                                                         
  print statfile "\nSending mail to mcp confirming how many customers ($ordercount) have been transmitted.\n";                                                
  close statfile ;                                                              
  %mail = ( To      => "$successmail",                                          
            From    => 'chris.mcpartland@gis.dhl.com',                                      
            Subject => 'DHL customer transmission to McP',                     
            Message => "$ordercount $description customers have been transmitted to Me by Me.",                                          
            smtp    => [ qw( gateway.dhl.com ) ],);                                                                                                        
                                                                                
  sendmail(%mail) or die $Mail::Sendmail::error;                                
  print "mail sent.\n";