#!/usr/bin/perl

#use warnings;
#use strict;
use DBI;
 
my $db2 ="isubeefdb";
my $user = "eric";
my $pass = "LushPed07";
my $host="localhost";
my $dbh2 = DBI->connect("DBI:mysql:$db2:$host", $user, $pass);
my $animal = "";
my $sire = "";
my $dam = "";
my $genotyped = "";
my $sireG = "";
my $damG = "";
my $pgs = "";
my $pgd = "";
my $mgs = "";
my $mgd = "";
my $pgsG = "";
my $pgdG = "";
my $mgsG = "";
my $mgdG = "";

print "Animal\tSire\tDam\tPaternal Grand Sire\tPaternal Grand Dam\tMaternal Grand Sire\tMaternal Grand Dam\n";
    
my $iquery1 = "select AnimalID, SireID, DamID, Genotyped from Pedigree where ISU='Y'";
my $isth1 = $dbh2->prepare($iquery1);
$isth1->execute();
$isth1->bind_columns(\$animal,\$sire,\$dam,\$genotyped);
while($isth1->fetch())
{	
	my $iquery2 = "SELECT SireID, DamID, Genotyped from Pedigree WHERE AnimalID='".$sire."'";
	my $isth2 = $dbh2->prepare($iquery2);
    $isth2->execute();
    $isth2->bind_columns(\$pgs,\$pgd,\$sireG);
	$isth2->fetch();
	
	my $iquery3 = "SELECT SireID, DamID, Genotyped from Pedigree WHERE AnimalID='".$dam."'";
	my $isth3 = $dbh2->prepare($iquery3);
    $isth3->execute();
    $isth3->bind_columns(\$mgs,\$mgd,\$damG);
	$isth3->fetch();
	
	if(!($pgs eq ""))
	{
		my $iquery4 = "SELECT Genotyped from Pedigree WHERE AnimalID='".$pgs."'";
		my $isth4 = $dbh2->prepare($iquery4);
    	$isth4->execute();
    	$isth4->bind_columns(\$pgsG);
		$isth4->fetch();
	}
	
	if(!($pgd eq ""))
	{
		my $iquery5 = "SELECT Genotyped from Pedigree WHERE AnimalID='".$pgd."'";
		my $isth5 = $dbh2->prepare($iquery5);
    	$isth5->execute();
    	$isth5->bind_columns(\$pgdG);
		$isth5->fetch();
	}
	
	if(!($mgs eq ""))
	{
		my $iquery6 = "SELECT Genotyped from Pedigree WHERE AnimalID='".$mgs."'";
		my $isth6 = $dbh2->prepare($iquery6);
    	$isth6->execute();
    	$isth6->bind_columns(\$mgsG);
		$isth6->fetch();
	}
	
	if(!($mgd eq ""))
	{
		my $iquery7 = "SELECT Genotyped from Pedigree WHERE AnimalID='".$mgd."'";
		my $isth7 = $dbh2->prepare($iquery7);
    	$isth7->execute();
    	$isth7->bind_columns(\$mgdG);
		$isth7->fetch();
	}
	
	if(!($genotyped eq "") && !($sireG eq "") && !($pgsG eq "") && !($pgdG eq "") && !($mgsG eq "") && !($mgdG eq ""))
	{
		if(!($dam eq ""))
			{print $animal."\t".$dam."\n";}
		else
			{print $animal."\tNA\n";}
	}
	elsif(!($genotyped eq "") && !($damG eq "") && !($pgsG eq "") && !($pgdG eq "") && !($mgsG eq "") && !($mgdG eq ""))
	{
		if(!($sire eq ""))
			{print $animal."\t".$sire."\n";}
		else
			{print $animal."\tNA\n";}
	}
		
	$genotyped = "";
	$sireG = "";
	$damG = "";
	$pgs = "";
	$pgd = "";
	$mgs = "";
	$mgd = "";
	$pgsG = "";
	$pgdG = "";
	$mgsG = "";
	$mgdG = "";
}