#!/usr/bin/perl 
#query to get the difference between dates and things in the same columns... 
use strict;
use DBI;
use Time::Local;
##################### mysql definitions 
#password and access
         my $db       = "new"         ;
         my $host     = "192.168.1.20" ;
         my $user     = "user"         ;
         my $pass     = "pass"         ;  
	my  $dbh  = DBI->connect ("DBI:mysql:database=$db:host=$host",$user,$pass)or die "Can't connect to database: $DBI::errstr\n";

############################ mysql connection definitions 
#the key thing is to do a select query that results in the items being in order in the resultset...

my $sql_query ="SELECT brand, date, weight FROM test2 ORDER BY brand, date ASC";
my $sth_query = $dbh->prepare($sql_query);
$sth_query->execute();
#######################################################
my $brand="";
my $date_old=timelocal(0,0,0,10,10,1980); #just set it to something
my $date;
my $date_diff;
my $weight;
my $weight_old;
my %samples;
my %dates;
my @row;
while(@row = $sth_query->fetchrow_array()){

		$brand	  	= $row[0];
		$date		= $row[1];
		$weight 	= $row[2];
		$samples{$brand}++; #increment the number of times this brand has appeared 

if($samples{$brand} ==1){ #if it is the first time then make old = new so that no big step changes in weight etc recorded
	$date_old	= $date;
	$weight_old	= $weight;
}

#difference in two datestrings calculation - convert to seconds - I'm sure there are better ways to do this..
$date =~ m|(\d{4}).(\d{2}).(\d{2})|;
my $date_new1= timelocal(0,0,0,$3, $2-1, $1); #the '-1' cos locatime(0,0,0,dd,mm,yyyy) months count from zero
$date_old =~ m|(\d{4}).(\d{2}).(\d{2})|;
my $date_old1= timelocal(0,0,0,$3, $2-1, $1);

$date_diff = $date_new1- $date_old1; # and subtract to get difference in seconds
$date_diff = $date_diff / 60 / 60 / 24; # then divide by number of seconds in a day to get days
		# voila!
print "$brand,\t$samples{$brand}\t$date\t$date_old\t$date_diff,\t$weight,\t$weight_old,\t".($weight-$weight_old)."\n";
		$date_old	= $date;
		$weight_old	= $weight;
}
$sth_query->finish;
$dbh->disconnect;