# ==================================================================
# DBMan SQL - enhanced database management system
#
#   Website  : http://gossamer-threads.com/
#   Support  : http://gossamer-threads.com/scripts/support/
#   Revision : $Id: Home.pm,v 1.18 2002/08/09 21:47:54 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::Home;
# ==================================================================

use strict;

$Dbsql::Home::COMPILE{qsearch_form} = <<'END_OF_SUB';
sub qsearch_form {
#---------------------------------------------------------------------
# Search form for a query
#
    my ($self,$msg) = @_;
    return $self->home($self->_language('PER_VIEW')) unless ( $self->{user}->{view_p} );

    $msg ||= $self->_language('SRC_MESSAGE');
    return ('qsearch_form.html',{
                    header  => $self->_language('HEA_SEARCH'),
                    msg     => $msg,
    });
}
END_OF_SUB

$Dbsql::Home::COMPILE{qsearch_results} = <<'END_OF_SUB';
sub qsearch_results {
#---------------------------------------------------------------------
# Search results
#
    my $self = shift;
    return $self->home($self->_language( 'PER_VIEW' )) unless ( $self->{user}->{view_p} );

    my $rs = $self->{sql}->table('Dbsql_Queries')->get({ QName => $self->{cgi}->{q} });
    ( $rs ) or return $self->qsearch_form("Query $self->{cgi}->{q} does not exist.");
    
    my $nh     = $self->{cgi}->{nh} || 1;
    my $mh     = $self->{cgi}->{mh} || $self->{glb_cfg}->{'build_links_per_page'} || 25;
    my $start  = ( $nh == 1 ) ? 0 : ( $nh - 1 ) * $mh;
    my $limit  = "LIMIT ".$start.",".$mh;

    my @cols   = @{eval $rs->{QFields}};
    my @fields = ( !$self->{cgi}->{d} and $rs->{QGroup} ) ? @{eval $rs->{QHeaders}} : @{eval $rs->{QFields}};
    my $tables = "'".join ("','", @{eval $rs->{QTables}})."'";

# create conditions
    my $cond = new GT::SQL::Condition;    
    my ($empty, $key);

    if ( !$self->{cgi}->{keyword} ) {
        $cond->{bool} = 'OR' if ( $self->{cgi}->{ma} );
        foreach ( @cols ) {
            if ( $self->{cgi}->{$_} ) {
                my $val = $self->{cgi}->{$_};
                next if ( !$val );
                my $opt = $self->{cgi}->{"$_-opt"};
                $key   = $val;
                $empty = 1 if ( $val );                
                next       if ( $val eq '*' );

                ( lc $self->{cgi}->{"$_-opt"} eq 'like' ) ? $cond->add($_, 'like', "$val%")
                                                          : $cond->add($_, $self->{cgi}->{"$_-opt"}, "$val");
            }
            else {
                if ( $self->{cgi}->{"$_-gt"} or $self->{cgi}->{"$_-lt"} ) { # greater than and less than
                    $cond->add($_, '>', $self->{cgi}->{"$_-gt"}) if ( $self->{cgi}->{"$_-gt"} );
                    $cond->add($_, '<', $self->{cgi}->{"$_-lt"}) if ( $self->{cgi}->{"$_-lt"} ); 
                    $empty = 1;
                }
                elsif ( $self->{cgi}->{"$_-ge"} or $self->{cgi}->{"$_-le"} ) { # greater than or equal and less than or equal
                    $cond->add($_, '>=', $self->{cgi}->{"$_-ge"}) if ( $self->{cgi}->{"$_-ge"} );
                    $cond->add($_, '<=', $self->{cgi}->{"$_-le"}) if ( $self->{cgi}->{"$_-le"} ); 
                    $empty = 1;
                }                
            }
        }
    }
    elsif ( $self->{cgi}->{keyword} ne '*' ) { #search for keyword
        $cond->{bool} = 'OR';        
        foreach ( @cols ) { $cond->add($_, 'like', "%$self->{cgi}->{keyword}%"); }
    }
    $empty = 1 if ( $self->{cgi}->{keyword} );

    ( $empty ) or return $self->qsearch_form($self->_language('SRC_FAILURE'));

# Check if users can view only their own record
    my $auth = $self->{sql}->table('Dbsql')->get({ Tablename => $rs->{QKey} });
    if ( $auth->{'auth_view_own'} and $auth->{auth_user_field} and !$self->{user}->{admin_p} ) {
        $cond->add("$rs->{QKey}.$auth->{'auth_user_field'}", '=', $self->{user}->{'Username'});
    }
    my $db = $self->{sql}->table(eval $tables);
    my $sb = $self->{cgi}->{sb};
    my $so = $self->{cgi}->{so};
    my $hits;

# create alias for each fields
    foreach ( @fields ) { $_ = "$_ as \"$_\""; }

# Group data
    if ( $rs->{QGroup} and !$self->{cgi}->{d} ) {
        $db->select_options("GROUP BY $rs->{QGroup}");
        my $sth = $db->select($cond);
        $hits   = $sth->rows if ( $sth );
       
        $db->select_options("GROUP BY $rs->{QGroup}", $limit);
        push @fields, "COUNT(*) as 'rows'";
    }
    else {
        my $sth = $db->select($cond);
        $hits = $sth->rows if ( $sth );
        ( $self->{cgi}->{sb} ) ? $db->select_options("ORDER BY $self->{cgi}->{sb} $so", $limit) : $db->select_options($limit);
    }
    my $sth = $db->select($cond, \@fields);

# Return if we haven't found anything.
    return $self->qsearch_form($self->_language('SRC_NOTFOUND')) if ( $hits == 0 );

# Build speed bar
    my $speedbar = '';
    if ( $hits > $mh ) {
        my $url    = GT::CGI->url( remove_empty => 1 );
        my $disp   = $self->{sql}->html($db, $self->{cgi});
        $speedbar .= $disp->toolbar($self->{cgi}->{nh} || 1, $self->{cgi}->{mh} || 25, $hits, $url);
    }

# Return results
    my ( @output );
    while ( my $rs = $sth->fetchrow_hashref ) { push @output, $rs; }    
    my $tmp_name = ( $self->{cgi}->{d} ) ? 'qsearch_results_detail.html' : 'qsearch_results.html';
    return ( $tmp_name, {
                header   => $self->_language('HEA_SRC_RESULT'),
                results  => \@output,
                speedbar => $speedbar,
                group    => $rs->{QGroup},
                msg      => ($self->{cgi}->{d})? $self->_language('REL_RESULTS', $key, $hits) : $self->_language('SRC_RESULT', $hits),
                hits     => $hits
    });
}
END_OF_SUB

sub sadd_record {
#---------------------------------------------------------------------
# create add form
#
    my $self = shift;
    return $self->home($self->_language('PER_ADD')) unless ( $self->{user}->{add_p} );

# Turn arrays into delimited fields
    $self->format_insert_cgi;

    if ( $self->{cfg}->{'auth_user_field'} ) { # check if it has user field
        if ( $self->{user}->{admin_p} ) {
            my $prop = $self->{sql}->table('Dbsql')->get($self->{cgi}->{sdb});    
            if ( $prop->{'auth_user_field'} ) { #Update user field
                $self->{cgi}->{$prop->{'auth_user_field'}} = $self->{user}->{'Username'};
            }
        } 
        else {        
            my $pk = $self->{subfrm}->{pk};
            my $fk = $self->{subfrm}->{fk};
            my $rs = $self->{db}->get({ $pk => $self->{cgi}->{$fk}, $self->{cfg}->{'auth_user_field'} => $self->{user}->{'Username'} });

            ( $rs ) or return $self->add_form($self->_language('REL_ADD_OWN', $self->{cgi}->{$fk}));

            my $prop = $self->{sql}->table('Dbsql')->get($self->{cgi}->{sdb});    
            if ( $prop->{'auth_user_field'} ) { #Update user field
                $self->{cgi}->{$prop->{'auth_user_field'}} = $self->{user}->{'Username'};
            }
        }            
    }
    
#Check foreign key
    my $rs = $self->{sql}->table($self->{subfrm}->{Tablename})->get({ $self->{subfrm}->{pk} => $self->{cgi}->{$self->{subfrm}->{fk}} });
    ( $rs ) or return $self->add_form($self->_language('REL_ERROR_FK', $self->{subfrm}->{Tablename}));

# Setup the language for GT::SQL.    
    local $GT::SQL::ERRORS->{ILLEGALVAL} = $self->_language('ADD_ILLEGALVAL');
    local $GT::SQL::ERRORS->{UNIQUE}     = $self->_language('ADD_UNIQUE');
    local $GT::SQL::ERRORS->{NOTNULL}    = $self->_language('ADD_NOTNULL');

    if ( defined( my $ret = $self->{subfrm}->{db}->add($self->{cgi}) ) ) {        
        $self->auth_logging('add record ') if ( $self->{cfg}->{log_file} );
        $self->{cgi}->{sfk} = $self->{cgi}->{$self->{subfrm}->{fk}};
        return $self->ssearch_results($self->_language('ADD_SUB_SUCCESSFULLY'));
    }
    else {
        local $^W;
        my $error = $GT::SQL::error;
        $error =~ s/\n/<br>\n<li>/g;
        $self->add_form("<font color=red><ul><li>$error</ul></font>");
    }          
}

sub ssearch_results {
#----------------------------------------------------------------------
# search results page
#
    my ($self, $msg) = @_;
    return $self->home($self->_language('PER_VIEW')) unless( $self->{user}->{view_p} );

    my $db  = $self->{cgi}->{db};
    my $sdb = $self->{cgi}->{sdb};    
    my $val = $self->{subfrm}->{value} || $self->{cgi}->{$self->{subfrm}->{fk}};

    my $nh    = $self->{cgi}->{nh} || 1;
    my $mh    = $self->{cgi}->{mh} || $self->{glb_cfg}->{'build_links_per_page'} || 25;
    my $start = ( $nh == 1 ) ? 0 : ( $nh - 1 ) * $mh;
    my $limit = "LIMIT ".$start.",".$mh;

    my $cond = new GT::SQL::Condition;
    $cond->add("$sdb.$self->{subfrm}->{fk}", '=', $val);

# Check if users can view only their own record        
    if ( $self->{cfg}->{'auth_view_own'} and $self->{cfg}->{'auth_user_field'} and !$self->{user}->{admin_p} ) {
        $cond->add("$db.$self->{cfg}->{'auth_user_field'}", '=', $self->{user}->{'Username'});
    }

    my $tab = $self->{sql}->table($db, $sdb);
    $tab->select_options($limit);

    my $sth  = $tab->select($cond, ["$sdb.*"]);
    my $hits = $tab->hits();

# Return if we haven't found anything.
    if ( $hits == 0 ) {
        return ('search_results.html', {
                   header  => $self->_language('HEA_SRC_RESULT'),
                   msg     => $self->_language('REL_NORESULTS', $val)
        });
    }

# Build speed bar   
    my $speedbar = '';
    if ( $hits > ( $self->{cgi}->{mh} || 25 ) ) {
        my $name   = GT::CGI->url(remove_empty => 1);
        $speedbar .= $self->{subfrm}->{disp}->toolbar($self->{cgi}->{nh} || 1, $self->{cgi}->{mh} || 25, $hits, $name);
    }

    my @output;
    while ( my $rs = $sth->fetchrow_hashref ) { push @output, $rs; }

    return ('search_results.html', {
                   header  => $self->_language('HEA_SRC_RESULT'),
                   results => \@output,
                   speedbar=> $speedbar,
                   msg     => $msg || $self->_language('REL_RESULTS', $val, $hits),
                   hits    => $hits
    });
}

sub smodify_search_results {
#----------------------------------------------------------------------
# show the detail listings 
#
    my ($self,$msg) = @_;
    return $self->home($self->_language('PER_MOD')) unless( $self->{user}->{modify_p} );

    my $db  = $self->{cgi}->{db};
    my $sdb = $self->{cgi}->{sdb};
    my $val = $self->{subfrm}->{value} || $self->{cgi}->{$self->{subfrm}->{fk}};

    my $nh    = $self->{cgi}->{nh} || 1;
    my $mh    = $self->{cgi}->{mh} || $self->{glb_cfg}->{'build_links_per_page'} || 25;
    my $start = ( $nh == 1 ) ? 0 : ( $nh - 1 ) * $mh;
    my $limit = "LIMIT ".$start.",".$mh;

    my $cond = new GT::SQL::Condition;
    $cond->add("$sdb.$self->{subfrm}->{fk}", '=', $val);

# Check if users can modify only their own record        
    if ( $self->{cfg}->{'auth_modify_own'} and $self->{cfg}->{'auth_user_field'} and !$self->{user}->{admin_p} ) {
        $cond->add("$db.$self->{cfg}->{'auth_user_field'}", '=', $self->{user}->{'Username'});
    }
    
    my $tab  = $self->{sql}->table($db, $sdb);
    $tab->select_options($limit);

    my $sth  = $tab->select($cond, ["$sdb.*"]);
    my $hits = $tab->hits();

# Build speed bar
    my $speedbar = '';
    if ( $hits > ( $self->{cgi}->{mh} || 25 ) ) {
        my $name = GT::CGI->url(remove_empty => 1);
        $speedbar .= $self->{subfrm}->{disp}->toolbar( $self->{cgi}->{nh} || 1, $self->{cgi}->{mh} || 25, $hits, $name);
    }

# Return results
    my (@output);
    my @pk = $self->{subfrm}->{db}->pk;
    my $i = 1;
    while ( my $row = $sth->fetchrow_hashref ) {
        my $hidden;
        foreach my $key ( @pk ) {
            if ( $self->{subfrm}->{db}->can ('_complete_name') ) {
                my $new = {};
                for ( keys %{$row} ) { $new->{$self->{subfrm}->{db}->_complete_name ($_)} = $row->{$_}; }
                $row = $new;
            }
            my $val = $row->{$key};
            $hidden .= qq~<input type=hidden name="$i-$key" value="$val">~;
        }
        $row->{hidden} = $hidden;
        push @output, $row;  
        $i++;
    }
    return ('modify_search_results.html',{
                    header  => $self->_language('HEA_SRC_RESULT'),
                    results => \@output,
                    speedbar=> $speedbar,
                    msg     => $msg || $self->_language('REL_RESULTS', $val, $hits),
                    hits    => $hits
    });
    
}

sub smodify_form {
#----------------------------------------------------------------------
# Modify a record
#
    my ($self,$msg) = @_;
    return $self->home($self->_language('PER_MOD')) unless ( $self->{user}->{modify_p} );

    $msg ||= $self->_language('MOD_MESSAGE');
    my $values;
    my $mod = $self->{cgi}->{modify};
    ( $self->{cgi}->{modify} == 0 and $self->{cgi}->{do} ne 'modify_record' ) and return $self->smodify_search_results($self->_language('REL_SELECT'));

    if ( $self->{cgi}->{modify} == 0 ) {
        $values = $self->{cgi};
    }
    else {
        my $lookup = {};
        my $pk     = $self->{subfrm}->{db}->pk;
        foreach ( @$pk ) { $lookup->{$_} = $self->{cgi}->{"$mod-$_"}; }
        $values = $self->{subfrm}->{db}->get($lookup, 'HASH');
    }
    
    my $enctype = $self->{subfrm}->{db}->_file_cols() ? 'enctype="multipart/form-data"' : '';
    return ('modify_form.html', {
                    header      => $self->_language('HEA_MODIFY'),
                    msg         => $msg, %$values,
                    values      => $values,
                    enctype     => $enctype,
    });        
}

sub smodify_record {
#-----------------------------------------------------------------------
# Update a record 
#
    my $self = shift;    
    return $self->home($self->_language('PER_MOD')) unless ( $self->{user}->{modify_p} );

# Format arrays for insertion
    $self->format_insert_cgi;

# Check if users can delete only their own records
    if ( $self->{cfg}->{'auth_modify_own'} and $self->{cfg}->{'auth_user_field'} and !$self->{user}->{admin_p} ) {
        my $pk = $self->{subfrm}->{pk};
        my $fk = $self->{subfrm}->{fk};        
                
        my $id = $self->{subfrm}->{db}->get($self->{cgi}->{@{$self->{subfrm}->{db}->pk}[0]});
        ( $id ) or return $self->modify_search_form($self->_language('SRC_NOTFOUND'));                
        
        my $result = $self->{db}->get({ $pk => $id->{$fk} });       
        ( $result ) or return $self->modify_search_form($self->_language('SRC_NOTFOUND'));

        my $userid = $result->{$self->{cfg}->{'auth_user_field'}};        
        ( $userid eq $self->{user}->{'Username'} ) or return $self->smodify_search_results($self->_language('ERR_MODIFY_OWN')) ;
    }

#Check foreign key
    my $rs = $self->{sql}->table($self->{subfrm}->{Tablename})->get({ $self->{subfrm}->{pk} => $self->{cgi}->{$self->{subfrm}->{fk}} });
    ( $rs ) or return $self->smodify_form($self->_language('REL_ERROR_FK', $self->{subfrm}->{Tablename}));

# Setup the language for GT::SQL.    
    local $GT::SQL::ERRORS->{ILLEGALVAL} = $self->_language('ADD_ILLEGALVAL');
    local $GT::SQL::ERRORS->{UNIQUE}     = $self->_language('ADD_UNIQUE');
    local $GT::SQL::ERRORS->{NOTNULL}    = $self->_language('ADD_NOTNULL');

    if ( $self->{subfrm}->{db}->modify($self->{cgi}) ) {    
        $self->auth_logging('modify record ') if ( $self->{cfg}->{log_file} );
        return $self->smodify_search_results($self->_language('REL_MOD_SUCCESSFULLY'));
    }
    else {
        local $^W;
        my $error = $GT::SQL::error;
        $error    =~ s/\n/<br>\n<li>/g;
        return $self->smodify_form ("<ul><li><font color=red><b>$error</font></b></ul>");
    }
}

sub sdelete_search_results {
#-----------------------------------------------------------------------
#
    my ($self,$msg) = @_;
    return $self->home($self->_language('PER_DEL')) unless ( $self->{user}->{delete_p} );

    my $db  = $self->{cgi}->{db};
    my $sdb = $self->{cgi}->{sdb};    
    my $val = $self->{subfrm}->{value} || $self->{cgi}->{$self->{subfrm}->{fk}};

    my $nh    = $self->{cgi}->{nh} || 1;
    my $mh    = $self->{cgi}->{mh} || $self->{glb_cfg}->{'build_links_per_page'} || 25;
    my $start = ( $nh == 1 ) ? 0 : ( $nh - 1 ) * $mh;
    my $limit = "LIMIT ".$start.",".$mh;

    my $cond = new GT::SQL::Condition;
    $cond->add("$sdb.$self->{subfrm}->{fk}", '=', $val);

# Check if users can modify only their own record        
    if ( $self->{cfg}->{'auth_modify_own'} and $self->{cfg}->{'auth_user_field'} and !$self->{user}->{admin_p} ) {
        $cond->add("$db.$self->{cfg}->{'auth_user_field'}", '=', $self->{user}->{'Username'});
    }
    my $tab = $self->{sql}->table($db, $sdb);
    $tab->select_options($limit);
    my $sth  = $tab->select($cond, ["$sdb.*"]);
    my $hits = $tab->hits;

# Build speed bar
    my $speedbar = '';
    if ( $hits > ($self->{cgi}->{mh} || 25) ) {
        my $name   = GT::CGI->url(remove_empty => 1);
        $speedbar .= $self->{disp}->toolbar($self->{cgi}->{nh} || 1, $self->{cgi}->{mh} || 25, $hits, $name);
    }

# Return results
    my (@output);
    my @pk = $self->{subfrm}->{db}->pk;
    my $i  = 1;
    while ( my $row = $sth->fetchrow_hashref ) {
        my $hidden;
        foreach my $key ( @pk ) {
            if ( $self->{subfrm}->{db}->can('_complete_name') ) {
                my $new = {};
                for ( keys %{$row} ) {
                    $new->{$self->{db}->_complete_name ($_)} = $row->{$_};
                }
                $row = $new;
            }
            my $val  = $row->{$key};
            $hidden .= qq~<input type=hidden name="$i-$key" value="$val">~;
        }
        $row->{hidden} = $hidden;
        push @output, $row;  
        $i++;
    }

    return ('delete_search_results.html', {
                    header  => $self->_language('HEA_SRC_RESULT'),
                    results => \@output,
                    speedbar=> $speedbar,
                    msg     => $msg || $self->_language('REL_RESULTS', $val, $hits),
                    hits    => $hits
    });
}

sub sdelete_records {
#-----------------------------------------------------------------------
# Delete records
#
    my $self = shift;
    
    return $self->home($self->_language('PER_DEL')) unless ($self->{user}->{delete_p});

#------------demo code----------------
# demo 
#    return $self->delete_search_form('<font color=red>Delete records has been disabled in the demo !</font>');

# Need to know the names of the columns for this Table.
    my @columns = keys %{$self->{subfrm}->{db}->cols};

# Need to know the number of records modified
    my $rec_modified = 0;

# For through the record numbers. These are the values of the  check boxes
    foreach my $rec_num (@{$self->{cgi}->{delete}})  {
        my $change = {};
        foreach my $column (@columns) {
            $change->{$column} = $self->{cgi}->{"$rec_num-$column"} if exists $self->{cgi}->{"$rec_num-$column"};
        }
# Check for delete own record
        if ( $self->{cfg}->{'auth_modify_own'} and $self->{cfg}->{'auth_user_field'} and !$self->{user}->{admin_p} ) {
            my $pk  = $self->{subfrm}->{pk};
            my $fk  = $self->{subfrm}->{fk};
            
            my $id  = $self->{subfrm}->{db}->get($change);
            next if ( !$id );          

            my $rec = $self->{db}->get({ $pk => $id->{$fk} });
            next if ( $rec->{$self->{cfg}->{'auth_user_field'}} ne $self->{user}->{'Username'} );
        }
        next unless (keys %$change);
        my $ret = $self->{subfrm}->{db}->delete($change);
        $self->auth_logging('delete record ', $change) if ( $self->{cfg}->{log_file} );
        if ( defined $ret and ( $ret != 0 ) ) {
            $rec_modified++;
        }
    }
# Return the results page with the proper arguments depending on if we got an error or not.
    return $self->sdelete_search_results($self->_language('DEL_SUCCESSFULLY', $rec_modified));
}

sub check_relation {
#-----------------------------------------------------------------------
# check the relation between two tables
#
    my $self = shift;
    my $pk = $self->{db}->pk; 
    ($#$pk == 0) or return $self->error($self->_language('REL_ERROR_PK', $self->{cgi}->{db}));

    my $db  = $self->{cgi}->{db};
    my $sdb = $self->{cgi}->{sdb};    
    ( ref $sdb eq 'ARRAY' ) and return $self->error($self->_language('REL_ERROR_DB'));
    
    my $rs  = $self->{sql}->table('Dbsql_Relationships')->get({ Tablename => $db, Relatedname => $sdb });
    ( $rs ) or return $self->error($self->_language('REL_ERROR', $db, $sdb));

    $rs->{db}   = $self->{sql}->table($self->{cgi}->{sdb});
    $rs->{disp} = $self->{sql}->html($rs->{db}, $self->{cgi});
    $rs->{value}= $self->{cgi}->{sfk};
   
    if ( !$rs->{value} and defined $self->{cgi}->{modify} ) {
        my $mod    = $self->{cgi}->{modify};
        my $lookup = {};

        foreach ( @$pk ) { $lookup->{$_} = $self->{cgi}->{"$mod-$_"}; }
        my $val = $self->{db}->get($lookup, 'HASH');        
        $rs->{value} = $val->{$rs->{pk}};
    } 
    $self->{cgi}->{sfk} = $rs->{value};
    $self->{subfrm}     = $rs;
}

sub check_query {
#----------------------------------------------------------------------------
# Check query
#
    my $self = shift;
    my $qname = $self->{cgi}->{q};
    my $rs    = $self->{sql}->table('Dbsql_Queries')->get({ QName => $qname });
    ($rs) or return $self->error($self->_language('QUE_ERROR', $qname));
    
    my $auth = $self->{sql}->table('Dbsql')->get({ Tablename => $rs->{QKey} });
}
1;