#!/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 addressline1='aaaa' and postcode='bbbb'";
$sqlupd  = "update wardres set postal=1 where wresid=wwww";

# read csv
open HFILE, $ipfile or die "failed to open $ipfile ($!)\n";
@pvlines = <HFILE>;
close HFILE;


$count  = 0;
%haddr  = ();
%hcount = ();
@klist = ();

foreach $pvline (@pvlines) {
  chomp($pvline);
  @z = split(/,/, $pvline);
  $addr1 = $z[2];
  $pcode = $z[6];
  $sql = $sqladdr;
  $sql =~ s/aaaa/$addr1/;
  $sql =~ s/bbbb/$pcode/;
  print $sql."\n";
  $sth = $dbh->prepare($sql);
  $sth->execute;
  @wresids = ();
  while (@data = $sth->fetchrow_array()) {
print "A wresid=".$data[0]."\n";
    push @wresids, $data[0];
  }
  $sth->finish();
  
  foreach $wresid (@wresids) {
print "B wresid=".$wresid."\n";
    $sql = $sqlupd;
    $sql =~ s/wwww/$wresid/;
print $sql."\n";
    $sth = $dbh->prepare($sql);
    $sth->execute;
    $sth->finish();  
  }  
}