package IGMDB;
#
# Giovanni Bortoluzzi IGM - CNR Bologna
# Package perl per elaborazioni database
#
require 5.000;

use Time::Local;

require Exporter;
@ISA = qw(Exporter);
@EXPORT = qw (  
		 write_date time2gmt date_from_yday
 		%MYSQL_TYPES %MONTH_BY_NUM %MONTH_BY_NAME %MONTH_BY_NAME_N
		crea_dataset_prop populate_dataset_prop
	);
@EXPORT_OK = qw();

#use Carp;
#use strict;
use vars qw($VERSION);

%MONTH_BY_NUM = ('1','Jan','2','Feb', '3','Mar','4','Apr','5','May', '6','Jun',
	 	 '7','Jul', '8','Aug', '9','Sep','10','Oct','11','Nov','12','Dec',
);
%MONTH_BY_NAME = ('Jan','1','Feb','2','Mar','3','Apr','4','May','5','Jun','6',
		'Jul','7','Aug','8','Sep','9','Oct','10','Nov','11','Dec','12',
);
%MONTH_BY_NAME_N = ('JAN','1','FEB','2','MAR','3','APR','4','MAY','5','JUN','6',
		'JUL','7','AUG','8','SEP','9','OCT','10','NOV','11','DEC','12',
);

CONFIG:	{
	%mltable = qw(
		 1	31
		 3	31
		 4	30
		 5	31
		 6	30
		 7	31
		 8	31
		 9	30
		10	31
		11	30
		12	31);
}
sub days_in
{
	# Month is 1..12
	my ($year, $month) = @_;
	return $mltable{$month+0} unless $month == 2;
	return 28 unless &is_leap($year);
	return 29;
}

sub is_leap
{
	my ($year) = @_;
	return 0 unless $year % 4 == 0;
	return 1 unless $year % 100 == 0;
	return 0 unless $year % 400 == 0;
	return 1;
}
sub date_from_yday {
	my ($year,$day) =@_;
	my ($sday,$m);
	$sday = 0; $m = 1;
	while (($sday+=days_in($year,$m)) < $day) {
		$m++;	
	}
	return $day-($sday-days_in($year,$m)),$m;
	return $sday-$day,$m;

}
%MYSQL_TYPES = ( "f" => "FLOAT",
	  	"d" => "DOUBLE",
	  	"r" => "DOUBLE",
		"i" => "INT",
		"si" => "SMALLINT",
	  	"t" => "VARCHAR",
          	"c" => "CHAR",
		"d" => "DATE",
		"ti" => "TIME",				
);


sub time2gmt { my ($ss,$mm,$hh,$d,$m,$y,$off)=@_;
	my ($date,$time,$dw,$year,$day,$mo);
	if ($ss=~/\-/) {
		$off=$mm;
		($date,$time)=split(' ',$ss);
		($hh,$mm,$ss) = split(':',$time);
		($y,$m,$d)=split('-',$date);
	} elsif ($ss=~/(\D+)/) {
		$off=$mm;
		($m,$d,$y,$time) = split(' ',$ss);
		($hh,$mm,$ss) = split(':',$time);
	} 

	my $ux = timelocal($ss,$mm,$hh, $d, $m=~/(\d+)/?$m-1:$MONTH_BY_NAME{$m}-1, $y>1000?$y-1900:$y);
	$off =~ s/GMT//;;
	my ($dw,$mo,$day,$time,$year) = split (' ',localtime ($ux - ($off*3600) ) );
	$date = sprintf ("%d-%02d-%02d ", $year,$MONTH_BY_NAME{$mo},$day);
#
	return $date,$time, GMT, $ux;
}

sub write_date { my ($day,$month,$year,$hh,$mm,$ss) = @_;

	if ($day=~/(\D+)/) {		# Jul 17 1996 18:41:47
		($month,$day,$year) = split(' ',$day);
	}
	my ($date) = sprintf "%d-%02d-%02d", 
		$year<1000 ? $year+1900 : $year,
		$month=~/\D+/ ? $MONTH_BY_NAME{$month} : $month,
		$day ;
	return $date; 	

}

sub crea_dataset_prop {
	print "CREATE TABLE DATASET_PROP (\n  DATASET_ID CHAR (24) NOT NULL,\n  DATASET_NAME CHAR (24),\n  DATASET_DESC CHAR (80), \n DATASET_SYMBOL CHAR (24),\n DATASET_COLOR CHAR (24), \n LONMIN DOUBLE, LONMAX DOUBLE, LATMIN DOUBLE, LATMAX DOUBLE )\n\\g\n";
}
sub populate_dataset_prop {
	my $i;

	while (<>) {
		@D = split (/\,/,$_);
        	print "insert into DATASET_PROP ( ";
		print "DATASET_ID, DATASET_NAME, DATASET_DESC, DATASET_SYMBOL, DATASET_COLOR, ";
		print "LONMIN, LONMAX, LATMIN, LATMAX) VALUES ( ";
		for $i (0..$#D-1) {
			$D[$i]=~s/\s+//g if $i != 2;
			$QUOTE = $i<=4 ? "'" : "";
                 	print "${QUOTE}$D[$i]${QUOTE}, ";
		}
		$D[$#D] =~ s/\s+//;
		print "$D[$#D]);\n"
	}
}
1;