Gossamer Forum: Products: Gossamer Links: Version 1.x: Nice date format mod

I found a way to use date format like: 1-Jan-2000 instead of the SQL way.
What I did is create a upper level that converts dates in the backend mantaining
the correct date grammar SQL needs to work just unshown (sorry for this very
bad english :-) )

well, I think I've done the most important modifications but not all. Should need
to check all fuctions more more deeper.

so.. here what I did:

DBSQL.pm
-------------

Code:
sub add_record { 
    my ($self, $rec_r, $in) = @_; 
    (ref $rec_r eq 'HASH') or return $self->error ("NOTHASHREF", "add_record"); 
	$self->connect(); 

# Convert dates in sql format.....		 
        foreach my $column (@{$self->{'db_cols'}}) {             
		if ($self->{'db_type'}{$column} =~ /DATE$/i) { 
			$rec_r->{$column} = &_date_to_sql ($rec_r->{$column}); 
		} 
       	} 

...

Code:
sub modify_record { 
... 
    my ($self, $rec_r, $extra, $in) = @_; 
    (ref $rec_r eq 'HASH') or return $self->error ("NOTHASHREF", "modify_record");   
	$self->connect(); 

# Convert dates in sql format.....		 
        foreach my $column (@{$self->{'db_cols'}}) {             
		if ($self->{'db_type'}{$column} =~ /DATE$/i) { 
			$rec_r->{$column} = &_date_to_sql ($rec_r->{$column}); 
		} 
       	} 
.....

Code:
sub get_record { 
... 
    my $sth = $DBH->prepare($query) or return $self->error ('CANTPREPARE', $query, $DBI::errstr); 
    $sth->execute() or return $self->error('CANTEXECUTE', $query, $DBI::errstr); 
    $self->{'last_query'} = $query; 
	carp "DBSQL: Record $key_q retrieved." if ($DEBUG); 

	my ($rec, $doc); 
 	if (uc $format eq 'HASH') { 
		$rec = $sth->fetchrow_hashref; 

	# Convert dates in a nice format.....		 
	        foreach my $column (@{$self->{'db_cols'}}) {             
			if ($self->{'db_type'}{$column} =~ /DATE$/i) { 
				$rec->{$column} = &_sql_to_date ($rec->{$column}); 
			} 
        	} 
		return $rec; 
	       } 
	 else { 
		$rec = $sth->fetchrow_arrayref; 
	# Convert dates in a nice format.....		 
		my $i=0; 
	        foreach my $column (@{$self->{'db_cols'}}) {             
			if ($self->{'db_type'}{$column} =~ /DATE$/i) { 
				$rec->[$i] = &_sql_to_date ($rec->[$i]); 
			} 
			$i++; 
        	} 
 		return $rec; 
	       } 
}

Code:
sub query { 
.... 

# Convert dates in a nice format.....	 
	for (0 .. $#{$hits}) { 
		my $hit = ${$hits}[$_]; 
		my $i=0; 
	        foreach my $column (@{$self->{'db_cols'}}) {             
			if ($self->{'db_type'}{$column} =~ /DATE$/i) { 
				$hit->[$i] = &_sql_to_date ($hit->[$i]); 
			} 
			$i++; 
        	} 
	} 
# If we have to many hits, let's build the next toolbar, and return only the hits we want. 
    if ($tb and ($self->{'hits'} > $maxhits)) { 
		$self->{'next_url'} = $self->toolbar($nh, $maxhits); 
    } 
    else { $self->{'next_url'} = ''; } 

# Clean up! 
    $sth->finish; 
    return $hits; 
}

Code:
sub get_defaults { 
... 
			if (${$self->{'db_type'}}{$field} eq 'DATE')	     { $defaults{$field} = &_sql_to_date($self->get_date()); } 
...

and at last, at the end of the file the two most important routines.

Code:
sub _sql_to_date { 
# --------------------------------------- 
my $in = shift; 
my %months = qw!1 Gen 2 Feb 3 Mar 4 Apr 5 Mag 6 Giu 7 Lug 8 Ago 9 Set 10 Ott 11 Nov 12 Dic!; 
my ($year, $mon, $day) = split /-/, $in; 
return "$day-" . $months{int $mon} . "-$year"; 
} 

sub _date_to_sql { 
# --------------------------------------- 
my $in = shift; 
my %months = qw!Gen 01 Feb 02 Mar 03 Apr 04 Mag 05 Giu 06 Lug 07 Ago 08 Set 09 Ott 10 Nov 11 Dic 12!; 
my ($day, $mon, $year) = split /-/, $in; 
return "$year-$months{$mon}-$day"; 
}

Well.. off course this is a very very uncommented mod. Hope that should help some of you.

Cheers

lepo