#!/usr/bin/perl
##############################################################################
#
# 	Script to make address labels from VAN and to personalise a letter template
#   Groups by people in a property so only one letter and address label per property
#   
##############################################################################

require 5.002;

use DBI;

$ipfile = 'postal_voters.csv';

# connect to database
$dbh = DBI->connect('dbi:mysql:JMBLOG','root','pelorus')  or die "Connection Error: $DBI::errstr\n";
$sqladdr = "select * from wardres where postal=0 order by addressline1, postcode";
$sqlins = "insert into wardres3 (namelist, addressline1, addressline2, place, town, postcode) values ('nnnn', 'aaaa', 'bbbb', 'pppp', 'tttt', 'cccc')";

$sth = $dbh->prepare($sqladdr);
$sth->execute;
$lastaddr1 = "";
$lastaddr2 = "";
$lastplace = "";
$lasttown = "";
$lastpcode = "";
$namelist = "";
@sqls = ();
while (@data = $sth->fetchrow_array()) {
  $addr1 = $data[3];
  $addr2 = $data[4];
  $place = $data[5];
  $town  = $data[6];
  $pcode = $data[7];
  $name  = $data[1].' '.$data[2];
  
  if (($addr1 eq $lastaddr1) && ($pcode eq $lastpcode)) {
    $namelist .= ', '.$name;  
  }
  else {
    if ($namelist ne "") {
      $namelist =~ s/  / /g;
      $namelist =~ s/^(.*),(.*?)$/$1 and$2/;
      $namelist =~ s/^ //;
      
      $sql = $sqlins;
      $sql =~ s/nnnn/$namelist/;
      $sql =~ s/aaaa/$lastaddr1/;
      $sql =~ s/bbbb/$lastaddr2/;
      $sql =~ s/pppp/$lastplace/;
      $sql =~ s/tttt/$lasttown/;
      $sql =~ s/cccc/$lastpcode/;
      
      push @sqls, $sql;
      print $namelist."\n";
    }
    $namelist = $name
  }
  
  $lastaddr1 = $addr1;
  $lastaddr2 = $addr2;
  $lastplace = $place;
  $lasttown  = $town;
  $lastpcode = $pcode;
}
$sth->finish();

foreach $sql (@sqls) {
  print $sql."\n";
  $sth = $dbh->prepare($sql);
  $sth->execute;
  $sth->finish();
}