#!/usr/local/bin/perl

#
#  Produce Yahrzeit List
#  

require "getopts.pl";
require "fmtsub";

while (<DATA>) {
	push (@tmpl1,$_);
} 

use DBI;

#
#   Connect to database
#
$driver = "mysql";
$dsn = "DBI:$driver:database=yidata;host=192.168.64.2;port=3306";
$dbh = DBI->connect($dsn, "yiadmin", "yipass");
$drh = DBI->install_driver("mysql");




while (<>) {
	last if /^###/;
	if (/:end:/) {
		if($have_data) { 
			&fmt("",\@mydata);
			$yy=0;
			($yd,$ym)=split(/[ \t]+/,$key{"yahrhebdate"},2);
			if($ym =~ /\s*(\d+)$/) { $yy =$1; }
			$ym =~ s/\s*(\d+)$//;
			$tmo=$mo=0;
			$t=$key{relation};
			$relationname = $key{relationname};
			$relation = $key{relation};
			$membername = $key{membername};
			$hebd = $key{yahrhebdate};

			$relationname =~ s/(["'])/\\\1/g;

#
#  check if address on file
#

      @names=split(/[\s]/, $membername);
			$last= pop(@names);

			$sth = $dbh->prepare( $sqlstmt = "SELECT serial from identity 
						 where name_last like \'$last%\'" );
			$serial= $rows=0;
			if ($sth->execute ) {
				while (my $ref = $sth->fetchrow_arrayref) {
					$rows++;
					$serial =$$ref[0];
				}
				$serial = 0 if ($rows != 1 ) ;
			}

#
#  add to database
#
			$sth = $dbh->prepare( $sqlstmt = "INSERT INTO yz 
				  (serial, 
					 y_notify_name, 
					 y_rel, 
					 y_rel_name, 
					 y_heb_dy, 
					 y_dt_mo, 
					 y_dt_yr) 
				VALUES 
				  ($serial, 
					 \'$membername\', 
					 \'$relation\', 
					 \'$relationname\', 
					 $yd, 
					 \'$ym\', 
					 $yy)");
			$sqlstmt =~ s/	/ /g;
			$sth->execute;
			}

			$have_data=0;
			@mydata = ();
			next;

		} elsif (/:begin:/) { next; 
		} else {
			push (@mydata,$_);
			$have_data=1;
		}
}