#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
package fw::sql;
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

@ISA = qw(Exporter);

@EXPORT= qw(

sql_insert
sql_update
sql_exec
sql_get
sql_read
);

#==============================================================================================================================================
# INSERTH_DB
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# make an insert on a table 
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# INPUT PARAMETERS 
#  0 : connection handle
#  1 : SQL table to update
#  2 : hash ref to the data
# OUTPUT PARAMETERS
#  0 : the newly inserted row primary key value
#==============================================================================================================================================
sub sql_insert
{
	 my $dbh = $_[0];
	 my $table = $_[1];
	 my %row = %{$_[2]};
	 my @columns = keys(%row);
	 my ($cols,$vals,$stmt,$rc);

#    return 0 if ($#columns == -1);

	 foreach $v (@columns)
	 {
	    $cols.="$v,";
	    $vals.="'$row{$v}',";
	 }
	 chop($cols);
	 chop($vals);

	 $stmt = "INSERT into $table ($cols) VALUES ($vals)";
	 $stmt =~ s/\'CURRENT_DATE\'/CURRENT_DATE/g;
	 $stmt =~ s/\'NOW\(\)\'/NOW\(\)/g;
   #see();
 #  print "\nSQL : [$stmt]";

# log_to("inserth_db : [$stmt]");
	 $rc = $dbh->do($stmt) || suicide("$DBI::errstr [$stmt]\n","0x00001");

	 return $dbh->{mysql_insertid};
}
#==============================================================================================================================================
# UPDATEH_DB
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# make an update on a table row given an id
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# INPUT PARAMETERS 
#  0 : connection handle
#  1 : SQL table to update
#  2 : hash ref to the data
#  3 : name of the key field
#  4 : value of the key field
# OUTPUT PARAMETERS
#  none
#==============================================================================================================================================
sub sql_update
{
	 my $dbh = $_[0];
	 my $table = $_[1];
	 my %row = %{$_[2]};
	 my $key = $_[3];
	 my $value = $_[4];

	 my @columns = keys(%row);
	 my ($upd,$stmt,$rc);

   return if ($#columns == -1);
   
    
	 foreach $v (@columns) {
	    $upd.="$v = '$row{$v}',";
	 }
	 chop($upd);

	 $stmt = "UPDATE $table SET $upd WHERE $key = '$value'";
	 $stmt =~ s/\'CURRENT_DATE\'/CURRENT_DATE/g;
	 $stmt =~ s/\'NOW\(\)\'/NOW\(\)/g;

	 
	 $dbh->do($stmt) || suicide("$DBI::errstr [$stmt]\n","0x00002");
}

#==============================================================================================================================================
# EXECSTMT
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# execute a SQL statement
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# INPUT PARAMETERS 
#  0 : connection handle
#  1 : SQL statement to execute
# OUTPUT PARAMETERS
#  none
#==============================================================================================================================================
sub sql_exec
{
	 my $dbh = $_[0];
	 my $stmt = $_[1];

	 $dbh->do($stmt) || suicide ("$DBI::errstr [$stmt]\n","0x00003");
} 

#*******************************************************************************
#GET TABLE
#*****************************************************************************
sub sql_get
{
    my $dbh_dbf     = $_[0];
    my $table_name=$_[1];
    my $selector=$_[2] || "*";
    my $where=$_[3] || "1";
    if($where ne "")  {     $where="where $where";       }
    my $ordby=$_[4] || "";
    if($ordby ne "")  {     $ordby="order by $ordby";       }
    my $groupby=$_[5] || "";
    if($groupby ne "")  {     $groupby="group by $groupby";       }
    my $limit          = $_[6];
    if($limit ne "")    {      $limit="limit $limit";       }
    
    my $debug          = $_[7] || 0;
    
    my @table =();
    
 
    
     
  	my $stmt = "SELECT $selector FROM $table_name $where $groupby $ordby $limit";        

  	if(0 ||$debug)
  	{
  	   see();
        print "<br /><br />[".$stmt."]<br /><br />";
   	}
   	
#   	exit;
  	my $cursor = $dbh_dbf->prepare($stmt) || die("CANNOT PREPARE $stmt");
  	$cursor->execute || suicide($stmt);
#   	if (!defined $rc) 
#   	{
#   		  see();
#   		  print "[$stmt]";
#   	    exit;   
#   	}
  	 while ($ref_rec = $cursor->fetchrow_hashref()) 
  	 {
  	    
#        my %rec = %{$ref_rec}; 
#         see(\%rec);
  		  push @table,\%{$ref_rec};
  	 }
  	 $cursor->finish;
  	 return @table;
}
#*******************************************************************************
#LIRE TABLE
#*****************************************************************************
sub sql_read
{
  my $dbh_dbf     = $_[0];
  my $table       = $_[1] || "";
  my $id          = $_[2] || 0;
  my $debug       = $_[3] || 0;
  my %ligne=();
  
	my $stmt = "select * FROM $table where id='$id'";
  
  if($debug>0)
  {
    see();
    print "<br /><br />".$stmt;
  }
  if($id ne "")
  { 
   #print "\n$stmt";print "\n".join('/',caller);
  
      my $cursor = $dbh_dbf->prepare($stmt);
    	my $rc = $cursor->execute;
    	
    	if (!defined $rc) 
    	{
    		  see();
    		  print "[$stmt]";
    	    exit;   
    	}
    	 while ($ref_rec = $cursor->fetchrow_hashref()) 
    	 {
    	    %ligne = %{$ref_rec};
    	 } 
    	 $cursor->finish;
    	 return %ligne;
    }
    else
    {
        see();
        print "id non fourni";
        return "id non fourni";
    }
}