# ==================================================================
# DBMan SQL - enhanced database management system
#
#   Website  : http://gossamer-threads.com/
#   Support  : http://gossamer-threads.com/scripts/support/
#   Revision : $Id: HTML.pm,v 1.13 2002/05/16 23:53:26 bao Exp $
# 
# Copyright (c) 2001 Gossamer Threads Inc.  All Rights Reserved.
# Redistribution in part or in whole strictly prohibited. Please
# see LICENSE file for full details.
# ==================================================================

package Dbsql::Relation::HTML;

    use strict;

sub generate_add_mul_success {
#----------------------------------------------------------------------
#
    my $tags    = GT::Template->tags;
    my $home    = $tags->{home};
    my $font    = '<font face="Verdana, Arial, Helvetica, sans-serif" size="2">';
    my $header  =  "<table cellpadding=1 cellspacing =1 border=0 width=100%>";
    my @rl_cols = $home->{subfrm}->{db}->ordered_columns;       
    my @pr_cols = $home->{db}->ordered_columns; 
    foreach (@pr_cols) {
        $header .= "<tr><td width=20%>$font$_:</td><td width=80%>$font$tags->{$_}</td></tr>";
    }
    $header .= '</table>';
    my $output  = "$header<BR><table cellpadding=1 cellspacing =1 border=1 width=100%>";
    my $details = $tags->{details};
    my $count   = 0;
    my $title   = '<tr>';     
    my $body;    

    foreach ( @$details ) {
        $count++;
        $body .= '<tr>';
        foreach my $k ( @rl_cols ) {
            if ( $count == 1 ) { # create the title
                $title .= "<td>$font$k</td>";
            }
            $body .= "<td>$font$_->{$k}</td>";
        }
        $body .= '</tr>';
    }
    $title .= '</tr>';
    return "$output$title$body</table>";
}

sub generate_qsearch_form {
#----------------------------------------------------------------------
#
    my $tags  = GT::Template->tags;
    my $home  = $tags->{home};
    my $qname = $tags->{home}->{cgi}->{q};
    my $rs    = $home->{sql}->table('Dbsql_Queries')->get({ QName => $qname });
    ( $rs ) or return;
    
    if ( $rs->{QTables} and $rs->{QFields} ) {
        my $output = '<table bgcolor="#ffffff" border="0" cellpadding="2" cellspacing="2" width="500" valign="top" align=center>';
        my $font   = '<font face="Tahoma,Arial,Helvetica" size=2>';
        my (@cols, $hidden);
        foreach my $f ( @{eval $rs->{QFields}} ) { push @cols, { name => $f}; }

        foreach ( @{eval $rs->{QTables}} ) {
            my $cols = $home->{sql}->table($_)->cols;
            foreach my $f ( @cols ) {
                next if ( $f->{type} );
                if ( $f->{name} =~ m/$_\./ ) {
                    my ($fn) = $f->{name} =~ /\.([^.]+)$/;
                    foreach my $k ( keys % {$cols->{$fn}} ) { $f->{$k} = $cols->{$fn}->{$k}; }
                    $f->{form_display} = $cols->{$fn}->{form_display} || $f->{name};
                }
            }
        }
            
        foreach ( @cols ) {
            next if (!$_->{type});
            my $form_type = lc $_->{form_type};
            if ( $form_type eq 'hidden' ) {
                $hidden .= "<input type='hidden' name='$_->{name}' value='' size=$_->{form_size}>";
                next;
            }                    

            $output .= "<tr><td with=30%>$font$_->{form_display}</font></td>";
            if ( $_->{form_names} and $_->{form_values} ) {
                my ($form_names,$form_values); 
                $form_names  = $_->{form_names};
                $form_values = $_->{form_values};
                if ( $form_type eq 'checkbox' or $form_type eq 'radio' ) {
                    $output .= '<td width=50%>';
                    foreach my $i (0..$#$form_names) { $output .= "<input name='$_->{name}' value='@$form_names[$i]' type=$form_type>@$form_values[$i]"; }
                    $output .= '</td>';
                }
                elsif ( $form_type eq 'select' or $form_type eq 'multiple' ) {
                    $output .= "<td width=50%><select name='$_->{name}'".(($form_type eq 'multiple') ? 'multiple' : '').">";
                    foreach my $i ( 0..$#$form_names ) { $output .= "<option value='@$form_names[$i]'>@$form_values[$i]</option>"; }
                    $output .= '</select></td>';
                }                
            }
            elsif ( $form_type eq 'textarea' ) {
                $output .= "<td width=50%><textarea name='$_->{name}' value='' cols=$_->{form_size} rows=2></textarea></td>";
            }                    
            else {
                $output .= "<td width=50%><input name='$_->{name}' value='' size=$_->{form_size} type='text'></td>";
            }
            my $selected = ( lc $_->{type} eq 'date' or lc $_->{type} eq 'int' ) ? 'selected' : '';            
            $output .= qq!
                <td width=20% align=right><select  name="$_->{name}-opt">
                    <option value="LIKE">Like
                    <option $selected value="=">Exact Match
                    <option value="<>">Not Equal
                </select></td></tr>
            !;
        } 
        $output .= "</table>$hidden";
        $tags->{fields} = \@cols;
        return $output;
    }
}

sub generate_qsearch_options {
#----------------------------------------------------------------------
#   Generate Search Options
#
    my $tags = GT::Template->tags;
    my $home = $tags->{home};  
 
# Then set the rest of the form options.
    my $ma  = exists $home->{cgi}->{ma}      ? 'CHECKED' : '';
    my $mh  = exists $home->{cgi}->{mh}      ? $home->{cgi}->{mh} : 25;
    my $kw  = exists $home->{cgi}->{keyword} ? $home->{cgi}->{keyword} : '';
    my $idx = exists $home->{cgi}->{indexed} ? $home->{cgi}->{indexed} : '';
    my $fm  = exists $home->{cgi}->{fm}      ? $home->{cgi}->{fm} : 0;
    return { mh => $mh, ma => $ma, kw => $kw, idx => $idx, fm => $fm };
}

sub generate_submenu {
#----------------------------------------------------------------------
# 
    my $tags = GT::Template->tags;
    my $sfk;
    if ( !$sfk ) {
        my $rs = $tags->{home}->{sql}->table('Dbsql_Relationships')->get({ Tablename => $tags->{db}, Relatedname => $tags->{sdb} });
        ( $rs ) or return;
        $sfk = $tags->{$rs->{pk}};
    }
    my $output = qq!
        <BR><table bgcolor="#ffffff" border="1" cellpadding="0" cellspacing="0" width="500" valign="top" align=center>
                                             <tr height=30>
                                                            <td align=center><font face='Tahoma,Arial,Helvetica' size='2'><b>subfrm of $tags->{db}:</b>
                                                                           <a href="db.cgi?db=$tags->{db}&do=modify_form&sdo=sadd&sdb=$tags->{sdb}&sfk=$sfk">Add</a> |
                                                                           <a href="db.cgi?db=$tags->{db}&do=modify_form&sdo=sedit&sdb=$tags->{sdb}&sfk=$sfk">Edit</a> |
                                                                           <a href="db.cgi?db=$tags->{db}&do=modify_form&sdo=sdelete&sdb=$tags->{sdb}&sfk=$sfk">Delete</a>
                                                            </td>
                                             </tr>
                              </table>
    !;
}

sub generate_header {
#----------------------------------------------------------------------
    my $tags         = GT::Template->tags;
    my $sform        = $tags->{home}->{subfrm};
    my @ordered_cols = $sform->{db}->ordered_columns;       
    my $cols         = $sform->{db}->cols;
    my $output ;
    foreach ( @ordered_cols ) {
        my $title = $cols->{$_}->{form_display} || $_;
        $output .= "<td><b><font face='Tahoma,Arial,Helvetica' size=2>$title</font></td>";
    }
    return $output;
}

sub generate_search_results {
#----------------------------------------------------------------------
#
    my $tags = GT::Template->tags;
    my $sfk  = $tags->{sfk};
    ($sfk) or return;

    my $record = $tags->{results}[$tags->{row_num}-1];
    my $sform  = $tags->{home}->{subfrm};
    my $rs     = $tags->{home}->{sql}->table('Dbsql_Relationships')->get({ Tablename => $tags->{db}, Relatedname => $tags->{sdb} });
    ($rs) or return;
    $sfk = $tags->{$rs->{pk}};

    foreach my $k ( keys % $record ) {
        my $rl = $tags->{home}->{sql}->table('Dbsql_Relationships')->get({ Relatedname => $tags->{sdb}, fk => $k });
        if ($rl->{Tablename} and $rl->{oselect} and $rl->{fn} and $rl->{fn} ne $k) {
            my $info = $tags->{home}->{sql}->table($rl->{Tablename})->get({ $rl->{pk} => $record->{$rl->{fk}} });
            $record->{"$rl->{Tablename}.$k"} = $info->{$rl->{fn}};
        }
    }
    if ( $tags->{home}->{cgi}->{hz} ) {
        my $output;
        my $cols = $sform->{db}->cols;
        my @ordered_cols = $sform->{db}->ordered_columns;        
        foreach ( @ordered_cols ) {
            my $title = $cols->{$_}->{form_display} || $_;
            $output .= ( lc $cols->{$_}->{form_type} eq 'file' and $record->{$_} ) ? "<td><font face='Tahoma,Arial,Helvetica' size=2><a href='db.cgi?db=$sform->{Relatedname}&do=view_file&cn=$_&id=$record->{@{$sform->{db}->pk}[0]}' target='_blank'>$record->{$_}</a></font></td>"
                                                                                   : "<td><font face='Tahoma,Arial,Helvetica' size=2>$record->{$_}</font></td>";
        }
        return $output;
    }
    else {
        my $output .=$sform->{disp}->display({ mode => 'search_results', values => $record });        
        return $output;
    }
}

sub generate_qsearch_results_header {
#----------------------------------------------------------------------
#
    my $tags    = GT::Template->tags;
    my $home    = $tags->{home};
    my $headers = $tags->{info}->{headers};    
    my $cols    = $tags->{info}->{cols};
    my $font    = '<font face="Tahoma,Arial,Helvetica" size="2">';
    my $output .= '<tr bgcolor="#000099">';
    foreach ( @$headers ) {
        my ($fn) = $cols->{$_}[0] =~ /\.([^.]+)$/;
        $fn ||= $cols->{$_}[0];
        $output .= "<td><font color='#FFFFFF'><b>$font $fn</font></b></font></td>";
    }
    $output .= "<td align=center><font color='#FFFFFF'>$font<b>Details</font></b></font></td>" if ($tags->{group});
    $output .= '</tr>';
    return $output;
}

sub generate_qsearch_results {
#----------------------------------------------------------------------
#
    my $tags    = GT::Template->tags;
    my $home    = $tags->{home};
    my $headers = $tags->{info}->{headers};    
    my $details = $tags->{info}->{details};
    my $cols    = $tags->{info}->{cols};
    my $record  = $tags->{results}[$tags->{row_num}-1];
    my $font    = '<font face="Tahoma,Arial,Helvetica" size="2">';    

    my ($output, $url, $link);

# create detail URL
    if ( $tags->{group} ) {
        $url = 'db.cgi?';
        my $opts = ['db', 'do', 'sdb', 'q'];
        foreach ( @$opts ) { 
            $url .= "$_=$home->{cgi}->{$_}&" if ( $home->{cgi}->{$_} ); 
        }
        $url .= "d=1&$tags->{group}=$record->{$tags->{group}}";
    }
    $link .= "<a href='$url'>".(($record->{rows}) ? "$record->{rows} " : '')."details</a>";
    if ($home->{cgi}->{fm}) { # vertical format
        $output = '<table width="100%" border="0" cellspacing="2" cellpadding="2" align="center">';
        foreach ( @$headers ) {
            ( $cols->{$_}[0] ) or next;
            my ($fn) = $cols->{$_}[0] =~ /\.([^.]+)$/;
            $fn ||= $cols->{$_}[0];
            $output .= qq!
                <tr> 
                  <td width="20%">$font $fn:</font></td>
                  <td width="80%">$font $record->{$_}</font></td>
                </tr>
            !;
        }
        $output .= "<tr><td colspan=2 align=right>$font $link</td></tr>" if ($tags->{group});
        $output .= '</table>';
    }
    else {
        $output = ( $tags->{even} ) ? '<tr bgcolor="#EAEAEA">' : '<tr bgcolor="#FFFFFF">';
        foreach ( @$headers ) {
            ( $cols->{$_}[0] ) or next;
            $output .= "<td nowrap>$font$record->{$_}</font></td>"
        }
        $output .= "<td align=center>$font $link</td>" if ( $tags->{group} );
        $output .= "</tr>"
    }    
    return $output;
}

sub generate_qresults_header {
#----------------------------------------------------------------------
#
    my $tags    = GT::Template->tags;
    my $home    = $tags->{home};
    my $headers = $tags->{info}->{headers};    
    my $details = $tags->{info}->{details};
    my $cols    = $tags->{info}->{cols};
    my $record  = $tags->{results}[$tags->{row_num}-1];
    my $output  = '<table width="100%" border="0" cellspacing="1" cellpadding="2" align="center" bgcolor="#FFFFFF">';
    my $font    = '<font face="Tahoma,Arial,Helvetica" size="2">';
    my ($i, $owner, $url);    
    $owner = $record->{$tags->{info}->{user_field}} if ( !$tags->{home}->{cgi}->{ow} );
    $url = 'db.cgi?';
    if ( $owner and $owner ne 'default' ) {
        my $opts = ['db', 'sdb', 'q'];
        foreach ( @$opts ) { 
            $url .= "$_=$home->{cgi}->{$_}&" if ( $home->{cgi}->{$_} ); 
        }
        $url .= "do=owner&oid=$owner";
    }
    foreach ( @$headers ) {
        ( $cols->{$_}[0] ) or next;
        my ($fn) = $cols->{$_}[0] =~ /\.([^.]+)$/;
        $fn ||= $cols->{$_}[0];
        if ( $i == $#$headers and $owner and $owner ne 'default' ) {
            $output .= qq!
                <tr> 
                  <td width="20%">$font $fn:</font></td>
                  <td width="70%">$font $record->{$_}</font></td>
                  <td width="10%">$font <a href="$url">Owner</a></font></td>
                </tr>
            !;
        }
        else {
            $output .= qq!
                <tr> 
                  <td width="20%">$font $fn:</font></td>
                  <td width="80%" colspan=2>$font $record->{$_}</font></td>
                </tr>
            !;
        }
        $i++;
    }
    $output .= '</table><table width="100%" border="0" cellspacing="1" cellpadding="1"><tr bgcolor="#000099">';
    foreach ( @$details ) {
        my ($fn) = $cols->{$_}[0] =~ /\.([^.]+)$/;
        $fn ||= $cols->{$_}[0];
        $output .= "<td><font color='#FFFFFF'><b>$font $fn</font></b></font></td>";
    }
    $output .= '</tr>';
    return $output;
}

sub generate_qresults_detail {
#----------------------------------------------------------------------
#
    my $tags    = GT::Template->tags;
    my $home    = $tags->{home};
    my $details = $tags->{info}->{details};
    my $cols    = $tags->{info}->{cols};
    my $record  = $tags->{results}[$tags->{row_num}-1];
    my $font    = '<font face="Tahoma,Arial,Helvetica" size="2">';
    my $output  = ( $tags->{even} ) ? '<tr bgcolor="#EAEAEA">' : '<tr bgcolor="#FFFFFF">';
    foreach ( @$details ) { 
        if ( $cols->{$_}[1] and $record->{$_} ) { #file field type
            my ($tn, $fn) = split(/\./, $_);
            my $pk = @{$home->{sql}->table($tn)->pk}[0];
            my $id;
            foreach my $k ( keys % $record ) {
                if ( $k =~ m/\.$pk/ ) {
                    $id = $record->{$k};
                    last;
                }
            }           
            $output .= ($id) ? "<td nowrap>$font <a href='db.cgi?db=$tn&do=view_file&cn=$fn&id=$id' target=_blank>$record->{$_}</a></font></td>"
                             : "<td nowrap>$font $record->{$_}</font></td>";
        }
        else {
            $output .= "<td nowrap>$font $record->{$_}</font></td>"; 
        }
    }
    return "$output</tr>";
}

sub generate_fields_info {
#--------------------------------------------------------------------------
#
    my $tags = GT::Template->tags;
    my $home = $tags->{home};
    my $qname = $tags->{home}->{cgi}->{q};
    my $rs    = $home->{sql}->table('Dbsql_Queries')->get({ QName => $qname });
    ( $rs ) or return;
    
    if ( $rs->{QTables} and $rs->{QFields} ) {
        my $auth = $home->{sql}->table('Dbsql')->get({ Tablename => $rs->{QKey} });
        my ($hsh,$user_field);            
        $user_field = "$rs->{QKey}.$auth->{auth_user_field}" if ( $auth->{auth_user_field} );
        my @cols = @{eval $rs->{QFields}};        
        foreach ( @{eval $rs->{QTables}} ) {
            my $c = $home->{sql}->table($_)->cols;
            foreach my $f ( @cols ) {
                 ( $f =~ m/$_\./ ) or next;                 

                 my ($fn) = $f =~ /\.([^.]+)$/;
                 #( lc $c->{$fn}->{form_type} eq 'hidden' ) and next;
                 my $info  = [$c->{$fn}->{form_display} || $f];
                 @$info[1] = ( lc $c->{$fn}->{form_type} eq 'file' ) ? 1 : 0;
                 exists $hsh->{$f} or $hsh->{$f} = $info;
            }
        }          
        $tags->{info} = { cols       => $hsh,
                          headers    => ($rs->{QGroup}) ? \@{eval $rs->{QHeaders}} : \@{eval $rs->{QFields}},
                          details    => ($rs->{QGroup}) ? \@{eval $rs->{QDetails}} : \@{eval $rs->{QFields}},
                          user_field => $user_field,
        };
    }
}

sub generate_modify_form {
#--------------------------------------------------------------------
#   Generate HTML form to modify a record
#
    my $tags = GT::Template->tags;
    my $home   = $tags->{home};
    my $values = $tags->{values};
    create_listings($home,1);
    return $home->{subfrm}->{disp}->form( { mode => 'modify_form', values => $values, view_key => 1, extra_table=>0, file_field => 1, file_delete => 1  });
}

sub generate_add_form {
#--------------------------------------------------------------------
#   Generate HTML for add form
#
    my $tags = GT::Template->tags;
    my $home = $tags->{home};
    my $hk   = [$home->{subfrm}->{db}->ai];
    create_listings($home);
    return $home->{subfrm}->{disp}->form( { mode => 'add_form', values => {$home->{subfrm}->{fk} => $tags->{sfk}}, defaults => 1, hide => $hk, hide_timestamp => 1, search_opts => 0 , extra_table=>0, file_field => 1 });
}

sub generate_full_down {
#---------------------------------------------------------------------
# Generate a full down 
# $table is primary table, $related is related table
#
    my ($table, $related, $title) = @_;
    my $tags = GT::Template->tags;
    my $home = $tags->{home};
    my $pk   = $home->{sql}->table($table)->pk;
    ($#$pk ==0) or return "<font face=arial size=2>Table <b>'$table'</b> requires single primary key.</font>"; 

    my $rs = $home->{sql}->table('Dbsql_Relationships')->get ({ Tablename => $table, Relatedname => $related });
    ($rs) or return "<font face=arial size=2>Table <b>'$table'</b> doesn't relate to <b>'$related'</b></font>";

    my $fdesc = $rs->{fn} || $rs->{pk};    
    my $db    = $home->{sql}->table($rs->{Tablename});
    $db->select_options("Order by $fdesc");
    
# Get their own listings
    my $auth = $home->{sql}->table('Dbsql')->get({ Tablename => $rs->{Tablename} });
    my ($sth,$output,$selected);
    if ($auth->{auth_user_field}) {
        my $cond = new GT::SQL::Condition;
        $cond->add($auth->{'auth_user_field'},'=',$home->{user}->{'Username'});
        $sth = $db->select($cond,[$rs->{pk},$fdesc]);
    }
    else {
        $sth = $db->select([$rs->{pk},$fdesc]);
    }
    $output   = ($title) ? "<select name='$title.$rs->{fk}' class=button><option value=''>-----</option>"
                         : "<select name='$rs->{fk}' class=button><option value=''>-----</option>";
    $selected = $home->{cgi}->{$rs->{fk}};
    while (my $r = $sth->fetchrow_hashref) {
        $output .= ($r->{$rs->{pk}} eq $selected) ? "<option value='$r->{$rs->{pk}}' selected>$r->{$fdesc}</option>"
                                                  : "<option value='$r->{$rs->{pk}}'>$r->{$fdesc}</option>";
    }
    return "$output</select>";
}

sub generate_description {
#-------------------------------------------------------------------
# Get description field from primary table
# $table : primary table, $fid: pk; $fdesc: description field
#
    my ($table, $fid, $fdesc) = @_;
    ($table and $fid and $fdesc) or return;
    my $tags   = GT::Template->tags;
    my $home   = $tags->{home};
    my $record = $tags->{results}[$tags->{row_num}-1];
    my $rs     = $home->{sql}->table($table)->get($record->{$fid},[$fdesc]);
    return $rs->{$fdesc};
}

sub create_listings {
#-------------------------------------------------------------------
# Create a drop down for table that's related to another table
#
    my ($home, $type) = @_;
    my $relation = $home->{sql}->table('Dbsql_Relationships')->select ({ Relatedname => $home->{cgi}->{sdb}, oselect => '1' });
    my $pk = $home->{subfrm}->{db}->pk;
    while ( my $rs = $relation->fetchrow_hashref ) {
        my $obj = $home->{subfrm}->{db}->cols->{$rs->{fk}};
        if ( defined $home->{subfrm}->{db}->cols->{$rs->{fk}} and uc($obj->{'form_type'}) ne 'HIDDEN' and check_pk($pk, $rs->{fk},$type) ) {
            my $table = $home->{sql}->table($rs->{Tablename});
            my $fdesc = $rs->{fn} || $rs->{pk};
            $table->select_options("ORDER BY $fdesc");
            my $sth;

# Get their own listings
            my $auth = $home->{sql}->table('Dbsql')->get({ Tablename => $rs->{Tablename} });
            if ( $auth->{auth_user_field} and !$home->{user}->{admin_p} ) {
                my $cond = new GT::SQL::Condition;
                $cond->add($auth->{'auth_user_field'},'=',$home->{user}->{'Username'});
                $sth = $table->select($cond,[$rs->{pk},$fdesc]);
            }
            else {
                $sth = $table->select([$rs->{pk},$fdesc]);
            }
            my (@values, @names);
            while ( my ($id, $desc) = $sth->fetchrow_array ) {
                push @values,$desc;
                push @names,$id;
            }            
            $obj->{'form_type'}   = 'select';
            $obj->{'form_values'} = \@values;    
            $obj->{'form_names'}  = \@names;
            $obj->{'form_size'}   = 0; 
        }
    }
}

sub check_pk {
#-------------------------------------------------------------------
    my ($pk, $fk, $type) = @_;
    ($type) or return 1; # the action is add form
    if (ref $pk eq 'ARRAY' ) {
        foreach (@$pk) { return if ($_ eq $fk); }
        return 1;
    }
    else {
        return 1 if ($pk eq $fk);
    }    
}

1;