#!/opt/SAM/perl/bin/perl


package Recipe;

use Text::Wrap;
use DBI;
use strict;

# +-----------------------+
# | Tables_in_RECIPE      |
# +-----------------------+
# | ingredient            | 
# | recipe                | 
# | recipe_ingredient_map | 
# | recipe_part           | 
# | recipe_part_map       | 
# +-----------------------+

# ingredient
# +---------------+--------------+------+-----+---------+----------------+
# | Field         | Type         | Null | Key | Default | Extra          |
# +---------------+--------------+------+-----+---------+----------------+
# | ingredient_id | int(11)      | NO   | PRI | NULL    | auto_increment | 
# | name          | varchar(20)  | NO   | MUL |         |                | 
# | description   | varchar(250) | YES  |     | NULL    |                | 
# +---------------+--------------+------+-----+---------+----------------+

# recipe
# +-------------+--------------+------+-----+---------+----------------+
# | Field       | Type         | Null | Key | Default | Extra          |
# +-------------+--------------+------+-----+---------+----------------+
# | recipe_id   | int(11)      | NO   | PRI | NULL    | auto_increment | 
# | name        | varchar(20)  | NO   | MUL |         |                | 
# | description | varchar(250) | YES  |     | NULL    |                | 
# | notes       | mediumtext   | YES  |     | NULL    |                | 
# +-------------+--------------+------+-----+---------+----------------+

# recipe_ingredient_map
# +----------------+-------------+------+-----+---------+-------+
# | Field          | Type        | Null | Key | Default | Extra |
# +----------------+-------------+------+-----+---------+-------+
# | recipe_part_id | int(11)     | NO   | PRI |         |       | 
# | ingredient_id  | int(11)     | NO   | PRI |         |       | 
# | amount         | varchar(20) | NO   |     |         |       | 
# +----------------+-------------+------+-----+---------+-------+

# recipe_part
# +----------------+-------------+------+-----+---------+----------------+
# | Field          | Type        | Null | Key | Default | Extra          |
# +----------------+-------------+------+-----+---------+----------------+
# | recipe_part_id | int(11)     | NO   | PRI | NULL    | auto_increment | 
# | name           | varchar(20) | NO   | MUL |         |                | 
# | directions     | mediumtext  | YES  |     | NULL    |                | 
# +----------------+-------------+------+-----+---------+----------------+

#recipe_part_map
# +----------------+---------+------+-----+---------+-------+
# | Field          | Type    | Null | Key | Default | Extra |
# +----------------+---------+------+-----+---------+-------+
# | recipe_id      | int(11) | NO   | PRI |         |       | 
# | recipe_part_id | int(11) | NO   | PRI |         |       | 
# +----------------+---------+------+-----+---------+-------+


sub new {
    my $proto = shift;
    my $class = ref($proto) || $proto;
    my $self = { 
			'dbi' => undef,
			'errstr'=> undef,
			'debug'=> undef,
		};
    bless ($self,$class);
    return $self;
	
}


sub errstr {
	return $_[0]->{'errstr'};
}

#
# Function connects to a database
#
sub connect {
	my ($self) = shift;

	#
	# if we are connected then 
	# we need to disconnect
	#
	$self->disconnect() if $self->{'CONNECTION'};

	eval {
		my $c = DBI->connect("dbi:mysql:RECIPE", "root", undef,
			{ RaiseError => 0, PrintError => 0}) or die "$DBI::errstr";
		$self->{'dbi'} = $c;
	};

	if($@) {
		$self->{'errstr'} = $@;
		return undef;
	}

	return $self;
}

sub disconnect {
	my $self = shift;
	return unless defined $self->{'dbi'};
	$self->{'dbi'}->disconnect();
	$self->{'dbi'} = undef;
	return;
}

sub prepare {
	my($self, $sql) = @_;
	return undef unless $sql;
	my $cursor = undef;

	# Not connected?  Let's do it!
	if(!defined $self->{'dbi'}) {
		$self->connect() or return undef;
	}

	eval {
		$cursor = $self->{'dbi'}->prepare($sql) or die $self->{'dbi'}->errstr();
	};
	if($@) {
		$self->{'errstr'} = $@;
	}

	return $cursor;
}

sub get_DBI {
	my $self = shift;
	if(not defined $self->{'dbi'}) {
		$self->connect() or return undef;
	}

	return $self->{'dbi'};
}

sub get_recipe_text {
	my $self = shift;
	my $recipe_id = shift;
	my $data = undef;

	eval {
		my ($name, $notes) = undef;

		my $sth = $self->prepare("select name,notes from recipe where recipe_id = ? limit 1;")
			or die $self->errstr(); 
		$sth->execute($recipe_id) or die $sth->errstr();
		$sth->bind_columns(\$name, \$notes) or die $sth->errstr();
		while($sth->fetch()) {}
		$sth->finish();

		$data .= sprintf "%s\n", $name;

		my $parts = $self->prepare(
			"select * from recipe_part ".
			"inner join recipe_part_map on recipe_part.recipe_part_id = recipe_part_map.recipe_part_id ".
			"where recipe_part_map.recipe_id = ?;"
		) or die $self->errstr();

		$parts->execute($recipe_id) or die $parts->errstr();
		while(my @ps = $parts->fetchrow_array()) {
			$data .= sprintf " %-10.10s\n", $ps[1];
			my $ingredients = $self->prepare(
				"select ingredient.name, recipe_ingredient_map.amount from ingredient ".
				"inner join recipe_ingredient_map on ingredient.ingredient_id = recipe_ingredient_map.ingredient_id ".
				"where recipe_ingredient_map.recipe_part_id = ?;"
			) or die $self->errstr();
			$ingredients->execute($ps[0]) or die $self->errstr();
			while(my @is = $ingredients->fetchrow_array()) {
				$data .= sprintf "  %-20.20s %-30.30s\n", $is[1], $is[0];
			}
			$ingredients->finish();

			my $saved_wrap = $Text::Wrap::columns;
			$Text::Wrap::columns = 60;
			$data .= sprintf "\n".Text::Wrap::wrap("  ","  ", $ps[2])."\n\n";
			$Text::Wrap::columns = $saved_wrap;
		}
		$parts->finish();

		my $saved_wrap = $Text::Wrap::columns;
		$Text::Wrap::columns = 60;
		$data .= sprintf "\n".Text::Wrap::wrap(" "," ", $notes)."\n";
		$Text::Wrap::columns = $saved_wrap;

	};
	if($@) {
		$self->{'errstr'} = $@;
	}

	return $data;
}

sub debug {
	my $self = shift;
	my $val = shift;

	$self->{'debug'} = ($val ? 1:0);
	return 1;
}

sub db_to_hash {
	my $self = shift;
	my $x = 0;
	my %hash = ();

	eval {
		my $sth = $self->prepare("show tables;") 
			or die $self->errstr();
		$sth->execute() or die $sth->errstr();
		while(my @c = $sth->fetchrow_array()) {
			my $t = $self->prepare("select * from $c[0];")
				or die $self->errstr();
			$t->execute() or die $t->errstr();
			$x = 0;

			# Get column headings
			my $headings = $self->prepare("describe $c[0];")
				or die $self->errstr();
			$headings->execute() or die $headings->errstr();
			my @h = ();
			while(my @c1 = $headings->fetchrow_array()) {push @h, $c1[0];}

			while(my @c1 = $t->fetchrow_array()) {
				for(my $xx = 0; $xx <= $#c1; $xx++)  {
					$hash{$c[0]}{sprintf("%010d", $x)}{$h[$xx]} = $c1[$xx];
				}
				$x++;
			}
			$t->finish();
		}
		$sth->finish();
	};
	if($@) {
		$self->{'errstr'} = $@;
		return undef;
	}

	return %hash;

}





return 1;
# vi: set ts=2 sw=2: #