#!/usr/bin/perl -W

=pod

=head1 DOCUMENTATION for parts_list_maker.pl

=cut

# $Id: parts_list_maker.pl,v 1.4 2002/10/21 08:25:24 bprew Exp $
# $Log: parts_list_maker.pl,v $
# Revision 1.4  2002/10/21 08:25:24  bprew
# After uploading and running on westhost's site.  Made necessary changes.
# Also, need to do some re-structuring of some function, parse_input and
# update_price don't marry well together.  And execute_mysql_query has a pretty
# big kuldge, but I'm still working on it.  Also, need to convert functions to
# pod so I can export documentation.
#
# Revision 1.3  2002/10/21 02:02:44  bprew
# Finished testing, works as planned.
#
# Revision 1.2  2002/10/19 23:46:49  bprew
# Reworked most functions to be cleaner and quicker.  Added support for DBI
# instead of the out-dated mysql module.  Should be easier to maintain and debug
# as necessary.  Currently untested, but I need to get this file off my hard
# drive and up to the server.  Compiles, but's that it.
#
# Revision 1.1.1.1  2001/10/05 23:24:41  bprew
# inital version uploaded
#
# Revision 1.7  2001/08/05 04:47:51  bprew
# Fixed algorithm for update_price.  it wasn't calculating the intersection and
# the difference properly.
#
# Revision 1.6  2001/07/27 21:45:53  bprew
# Initial Beta release to Able computers.  Currently, everything appears to be
# working, but the price algorithm needs some more help.  Still working on that
# one.
#
# Revision 1.5  2001/07/03 00:26:54  bprew
# Finished update_database function.  Data is now properly parsed and
# entered into the database.  From here I will need to write PHP front-end
#
# Revision 1.4  2001/06/10 10:26:23  bprew
# Finished read_from_database function.  Wrote psuedo-code for
# update_price.  From here, the next step is to write the php code
# and talk to Nick about possibly buying/leasing the program.
#
# Revision 1.3  2001/05/30 09:34:25  bprew
# Wrote update module, which updates price using eval.  This allows the user to
# specify how they want to update the price.  Also, using MySQL allows me to use
# PHP as an interface
#
# Revision 1.2  2001/05/29 10:05:16  bprew
# Added parse_input function.  Still need to add the rest of the code that
# interacts with the database.  Thinking about using our own database
# instead of relying on MYSQL, especially since I'm not doing anything
# fancy... I'll think about it
#

use strict;
use Getopt::Std;
#use Mysql;
# the new Mysql
use DBI;

my %options;
my $version = " \$Id: parts_list_maker.pl,v 1.4 2002/10/21 08:25:24 bprew Exp $= ";

getopts( "u:p:h", \%options );

if(defined($options{h})) {
	usage();
}

my $parts_algo = defined( $options{ "p" } ) ? $options{ "p" } : 0;

if( defined( $options{ "u" } ) ) {
	my %items = parse_input($options{u}, $parts_algo);
	update_database( \%items );
} else {
	usage();
}

##################
##################END OF MAIN########################################
##################

=pod

=over 2

=item usage()

prints system usage if proper flags are not specified or -h 
is specified on the command line.

=back

=cut

sub usage
{
	print <<OUT;
	This is the help information.
	-u <input file> update database with information in <input file>.
		<input file> must be in .csv format.

	-p <price algorithm> updates prices from wholesale using 
		<price algorithm>.  Note, price algorithm can be any sort of
		perl code, but the price must be referenced as price.
		ex. price = price + 10 #raises all prices by 10 dollars
		ex. if( price > 100 ){ price + 10;}else{ price + 5; } 
		a little more complicated, but raises prices over 100 by 10
		dollars and all other prices by 5 dollars.
OUT
	exit(0);
}

=pod

=over 4

=item LIST parse_input EXPR, EXPR

Parses .csv file specified on the command line as -u <input file>.
Also takes in the price_algorithm and returns the parsed information as
a hash of a list.

=back

=cut

sub parse_input
{
	my $filename = shift;
	my $parts_algo = shift;
	my @lines;
	my %items;

	open( CSV, $filename ) or
		die( "ERROR: Could not open file: $filename\n $!" );
	@lines = <CSV>;
	close($filename);

	foreach my $line (@lines) {
		my @dirty_values = parse_csv( $line );
		my @values = ();
		#default category value
		my $category = "None";

		#only grab values that have data in them
		@values = grep(/\w/, @dirty_values) if scalar @dirty_values > 0;
		if( scalar( @values ) == 0 ) {
			# blank line
			print "Blank Line\n" . join('', @values);
		} elsif( scalar( @values ) == 1 ) {
			#this means we're looking at a heading name
			$category = $values[0];
		} elsif(defined($values[0])) {
			#now we add our information to a hash
			#need to store desc, category and price.


			my $price = $values[2];
			$price =~ s/\W//g;
			
			$price = update_price($price, $parts_algo);
			my @list = ($category, $values[1], $price);
			$items{$values[0]} = [$category, $values[1], $price];
		} else {
			print "\nNull value in item number, discarding\n";
			print "VALUES are:" . join( ',', @values ) . "\n";
 		}
	}

	return %items;
}

=pod

=over 4

=item LIST copy_database_into_hash

reads from the database and stores all the information in a hash.
Used to hide some of the implementation of the database from myself, as 
I am the only coder.  Returns a hash ordered by part number.  Each part
number contains a description, a category and a price, in that order in
a list.

=back

=cut

sub copy_database_into_hash
{
	my $error;
	my %parts;

	#write the query to get the list of parts
	my $query ="SELECT item_number FROM parts";
	my $sth;

	my $arr_ref = execute_mysql_query($query);

	#put the parts into our hash
	foreach my $ref (@$arr_ref) {
		$parts{@$ref[0]} = 1;
	}

	return %parts;

}

=pod

=over 4

=item EXPR update_price EXPR, EXPR

I figured this little thing needed it's own function, considering 
I've never used eval before.  I should be able to update the price 
without too much trouble.  Updates the $price using $price_algorithm.
Returns the updated price.

=back

=cut

sub update_price
{
	# get reference to hash and algorithm for updating parts.
	my ($price, $price_algo) = @_;

	#TODO lookup return values for eval
	eval $price_algo or
		die( __LINE__ . "Err: $! \n$@\n");

	return $price;
}


=pod

=over 4

=item update_database EXPR

This function will add the information from the hash passed in 
to the database.  Initially it will obtain a list of all the part 
numbers in the database currently.  If a part number is in the database
currently that is not in the hash passed it, I will assume that this 
means that part is no longer available and it will remove it from the 
database. If the part number exists, it will change the price and 
reset the new_arrival flag..  If the part number does not exist, it 
will add it and tag it as a new arrival.

=back

=cut

sub update_database
{
	my $hash_ref = shift;

	#populate the hash with info from the database.
	my %current_parts = copy_database_into_hash();

	# find keys that exist in both hashes, keys that only exist in the
	# current_parts hash and keys that only exist in the new_keys hash.

	my @both;
	my @new;
	my @old;
	foreach (keys %$hash_ref) {
		push(@both, $_) if exists $current_parts{$_};
		push(@new, $_) unless exists $current_parts{$_};
		# and delete from the current_parts to get what's left
		# This is safe since both @new and @both exist in %$hash_ref
		delete($current_parts{$_}) if exists $current_parts{$_};
	}

	@old = keys(%current_parts);

	delete_database_parts(\@old);
	update_database_parts(\@both, $hash_ref);
	add_database_parts(\@new, $hash_ref);
}

=pod

=over 4

=item delete_database_parts EXPR

Forms a mysql query from $list_ref and passes the queries and arguments
off to the execute_mysql_query.  It doesn't require the statement 
handler to be returned, so it doesn't send it.

=back

=cut

sub delete_database_parts
{
	my $parts_id_ref = shift;

	#form queries
	foreach (@$parts_id_ref) {
		my $query = "DELETE FROM parts WHERE item_number=?";

		#execute SQL queries
		execute_mysql_query($query,[$_] );
	}
}

=pod

=over 4

=item update_database_parts EXPR, EXPR

Forms a mysql query using the id in $parts_id_ref and the data located 
in $hash_ref.  This also does not require a statement handler, so it 
doesn't send it.

=back

=cut

sub update_database_parts
{
	my ($parts_id_ref, $hash_ref) = @_;

	#form queries
	foreach(@$parts_id_ref) {
		my $query = "UPDATE parts SET description=?, category=?," .
			 " retail_price=? WHERE item_number=?";

		my @args = (@{$$hash_ref{$_}}[0],
				@{$$hash_ref{$_}}[1],
				@{$$hash_ref{$_}}[2],
				$_);
	
		#execute SQL queries
		execute_mysql_query($query, \@args);
	}
}

=pod

=over 4

=item add_database_parts EXPR, EXPR

Forms a mysql query to add parts to our parts database.  Uses the 
information in $hash_ref as the data for the query.  Uses $parts_id_ref
as the id for the database.

=back

=cut

sub add_database_parts
{
	my ($parts_id_ref, $hash_ref) = @_;

	#form queries
	foreach(@$parts_id_ref) {
		my $query = "INSERT INTO parts SET description=?, category=?," .
			 " retail_price=?, item_number=?";

		my @args = (@{$$hash_ref{$_}}[0],
				@{$$hash_ref{$_}}[1],
				@{$$hash_ref{$_}}[2],
				$_);

		#execute SQL queries
		execute_mysql_query($query, \@args);
	}
}

=pod 

=over 4

=item execute_mysql_query EXPR, EXPR, EXPR

execute_mysql_query($query, \@args, $sth_ref) Executes a mysql_query 
$query using @args as its arguments.  If $sth_ref is specified, it will
return a reference to the statement handle so the caller can access the
data via a hash or other means.

=back

=cut

sub execute_mysql_query
{
	my $query = shift;
	my $args = shift;
	my $return_data;
	
	my $db = "*******";
	my $host = "localhost";
	my $user = "********";
	my $pass = "*******";
	my $dbs = "DBI:mysql:database=$db;host=$host";
	my $dbh = DBI->connect($dbs, $user, $pass);

	my $sth = $dbh->prepare($query);
	
	print(__LINE__ . "Err: ". $dbh->errstr . "\n") unless $sth;

	if(!$sth->execute(@$args)) {
		print(__LINE__ . "Err: " . $sth->errstr . "\n");
	}

	# bad kludge, I haven't figured out how to handle this, but 
	# if the query doesn't return anything, this function fails;
	if($query =~ /SELECT/i) {
		$return_data = $sth->fetchall_arrayref();
	}

	$dbh->disconnect();

	return $return_data;
}
	
=pod

=over 4

=item LIST parse_csv EXPR

Splits the $line into seperate @values using .csv format (values 
seperated by comma, values with ',' in them are enclosed in quotes, 
values with quotes in them are double-quoted)

=back

=cut

sub parse_csv
{
	my $text = shift;

	#we can do this the easy way...
	if( $text !~ /\",/ && $text !~ /,\"/ )
	{
		return( split( ( /,/, $text ) ) );
	}
	#or the hard way....
	my @words = split( //, $text );

	my $in_quote = 0;
	my $in_comma = 0;
	my $single_value;
	my @lines;
	for( my $i = 0; $i < scalar( @words ); $i++ )
	{
		if( $i == 0 )
		{
			if( $words[$i] eq '"' )
			{
				$in_quote = 1;
				$i++;
			}
			else
			{
				$in_comma = 1;
			}
		}
		if( $words[$i] eq '"' )
		{
			# if we in_comma, it means the start of a quoted word, 
			# if not, it means we're looking at either the end of 
			# a word, or an escape character.
			if( $in_comma == 1 )
			{
				$in_comma = 0;
				$in_quote = 1;
			}
			elsif( $words[$i + 1] eq '"' )
			{
				# if the next char is a ", it's an escape 
				# character if not, it's the end of the quote.
				$single_value .= $words[$i];
				$i++;
			}
			else
			{
				push( @lines, $single_value );
				undef( $single_value );
				$in_quote = 0;
			}
		}
		elsif( ($in_quote == 0) && ($words[$i] eq ',') )
		{
			if( $in_comma == 0 )
			{
				$in_comma = 1;
			}
			else
			{
				# if we are not in a quote, it means the 
				# beginning and the end of a comma word.
				push( @lines, $single_value );
				undef( $single_value );
			}
		}
		else
		{
			$single_value .= $words[$i];
		}
	}
	push( @lines, $single_value );

	return @lines;
}