<%doc>

 Generic Database Operations Section.

</%doc>

<%args>
$sql    =>         undef
$action =>         undef
$sid    =>         undef
$table  =>         undef
$sql_name =>       undef
$submit =>         undef
$forget =>         undef
$forget_name   =>  undef
$saved_queries =>  undef
</%args>

<%attr>
title   => 'Direct SQL Commands' 
section => 'Generic'
</%attr>

<%init>
my $result;
my $sql_err;
my %objindex;
my $session;
my @orderbrief;
my @headers;
my $headers;
my $bool_unique_query = 1;

if ( $action eq "DO_SQL" && $submit ){
    eval {
	$result = Netdot::Model->raw_sql($sql);
    };
    if ( my $e = $@ ){
	$sql_err = $e;
    }
}
if ( $action eq "DO_SQL" && $forget && $forget_name && $forget_name ne ""){
    my $forget_query = SavedQueries->search(name=>$forget_name)->first;
    if ( defined ($forget_query) ) {
	$forget_query->delete;
    }
}
if ( $action eq "SAVE_QUERY" ){
   if($sql_name && $sql){
      SavedQueries->insert({name=>$sql_name, querytext=>$sql});
   }
}

my @saved_queries = SavedQueries->retrieve_all();
@saved_queries = sort { $a->name cmp $b->name } @saved_queries;
foreach (@saved_queries){
    if(lc($_->querytext) eq lc($sql)){
        $bool_unique_query = 0;
        last;
    }
}
</%init>

<div class="container">
    <div class="containerhead">
        Direct SQL Commands
    </div>

    <div class="containerbody">

    <p><b>Be Careful!</b></p>

    <form name = "sql_form" action="<% $r->dir_config('NetdotPath') %>generic/" method="POST">
      <input type="hidden" name="action" value="DO_SQL">
      <input type="hidden" name="forget_name" id="forget_name" value="">
%     if ( $sql ) {
%       $sql =~ s/</&lt;/g;
%	$sql =~ s/>/&gt;/g;
%     }
      SQL query: <br/><textarea type="text" cols = '80' rows='5' name="sql"><% $sql | h %></textarea><br/>
      <select name="saved_queries" id="saved_queries" onChange="document.forms['sql_form']['sql'].value=saved_queries.value; document.getElementById('forget_name').value=saved_queries.options[document.getElementById('saved_queries').selectedIndex].text">
        <option value="">Saved Queries</option>
%           foreach (@saved_queries){
%               my $qt = $_->querytext;
%               my $qn = $_->name;
%               $qt =~ s/"/&quot;/g;
%               $qn =~ s/</&lt;/g;
%               $qn =~ s/>/&gt;/g;
                <option value="<% $qt %>"><% $qn %></option>
%           }                
      </select><br>
      <input name="submit" value="Go" type="submit"> &nbsp; or &nbsp;
      <input name="forget" value="Forget This Query" type="submit">
    </form>
%   if($action eq "DO_SQL" && $submit && (! $sql_err) && $bool_unique_query){
        <form name = "save_query" method="POST">
            <input type="hidden" name="action" value="SAVE_QUERY">
%     if ( $sql ) {
%       $sql =~ s/"/&quot;/g;
%     }
            <input type="hidden" name="sql" value="<% $sql %>">
            Save this query?  Name: <input type="text" name="sql_name" value=""> <input name="submit" type="submit" value="Save Query">
        </form>
%   }
    </div> <!-- close containerbody -->
</div> <!-- close container -->
% my @rows;
% if ( $action eq "DO_SQL" && $submit ){
    <div class="container">
        <div class="containerhead">Query Results:</div>
        <div class="containerbody">
<%perl>
    if ( defined $result && scalar $result->{rows} ){
        @headers = $result->{headers};
        @rows    = $result->{rows};      
	print scalar @{$rows[0]} . " rows<br>";
	$m->comp('data_table.mhtml', field_headers=>@headers, data=>@rows);
    
    }elsif ( $sql_err ){
        print $sql_err, "<br>";
    }

if ( @headers && @rows ) {
    # Create new session and get a new window for csv output
    $session = $ui->mk_session();
    $headers = $result->{headers};
    @orderbrief = @$headers if $headers;
    $session->{objindex}    = \%objindex;
    $session->{orderbrief}  = \@orderbrief;
    $session->{page}        = $result->{rows};
}
</%perl>

<div class="containerheadright">
    <a href="#" onClick="opentextwindow('','csv2','sid=<% $session->{_session_id} %>')">[csv]</a>
</div>
        <br>
       </div> <!-- close containerbody -->
    </div> <!-- close container -->
% }


