#!/usr/bin/perl
#  Copyright 2001-2009 Leslie Richardson

#  This file is part of Open Admin for Schools.

#  Open Admin for Schools is free software; you can redistribute it 
#  and/or modify it under the terms of the GNU General Public License
#  as published by the Free Software Foundation; either version 2 of 
#  the License, or (at your option) any later version.

# March 21/09 Only use the phrase table for origins (since it's what
# we need), and don't worry about case duplicates in this table. There
# will always be some since fieldnames are not capitalized but we
# normally want their names to have initial caps in the descriptions.

my $fixCase = 1; # allow case duplicates, if database returns them.

my %lex = ('Main' => 'Main',
	   'Error' => 'Error',
	   'Edit Translation' => 'Edit Translation',
	   'Eoy' => 'Eoy',
	   'Search' => 'Search',
	   'Original Text' => 'Original Text',
	   'Translated Text' => 'Translated Text',
	   'Both' => 'Both',
	   'Continue' => 'Continue',
	   'Start of Phrase' => 'Start of Phrase',
	   'Everywhere in Phrase' => 'Everywhere in Phrase',
	   'Translation' => 'Translation',
	   'Blank=All' => 'Blank=All',
	   'Removed Phrases' => 'Removed Phrases',
	   'Delete' => 'Delete',
	   'Phrase' => 'Phrase',
	   'Translation' => 'Translation',
	   'Deleted' => 'Deleted',
	   'Count' => 'Count',
	   'Update' => 'Update',
	   'Add' => 'Add',

	   );

my $self = 'xlatTransEdit.pl';
my $maxfieldlength = 70; # max size for text input fields; doesn't limit input.

use DBI;
use CGI;

eval require "../../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. " $@<br>\n";
    die $lex{Error}. " $@\n";
}

my $q = new CGI;
print $q->header( -charset, $charset); 
my %arr = $q->Vars;

my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);


# Print Page Header
print "$doctype\n<html><head><title>". $lex{'Edit Translation'};
print "</title><link rel=\"stylesheet\" href=\"$css\" type=\"text/css\">\n";
print "$chartype\n</head><body>\n";

print "[ <a href=\"$homepage\">". $lex{Main}. "</a> |\n";
print "<a href=\"$eoypage\">". $lex{Eoy}. "</a> ]\n";
print "<center><h1>". $lex{'Edit Translation'}. "</h1></center>\n";

if ( not $arr{page} ) {
    showStartPage();
} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    showChoices();
} elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    updateRecords();
} 
 

#----------------
sub showChoices {
#----------------

    # foreach my $key (keys %arr ) { print "K:$key V:$arr{$key}<br>\n"; }
    # Passed: sposition, sphrase and sgroup

    # Create select based on group, as well as phrase location
    my ( $select, $phrase );
    if ( $arr{sphrase} ) {
	if ( $arr{sposition} eq $lex{'Start of Phrase'} ) {
	    $phrase = $dbh->quote( $arr{sphrase}. q{%} );
	} else {
	    $phrase = $dbh->quote( q{%}. $arr{sphrase}. q{%} ); # do both ends with %
	}
    } 

    my %phrases = ();
    my %trans = ();

    # Search xlat_phrase table (phrase table)
    if ( $phrase ) { $select .= " where phrase like $phrase"; }

    my $sth = $dbh->prepare("select id, phrase from xlat_phrase $select order by phrase");
    $sth->execute;
    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }

    while ( my ( $id, $phrase) = $sth->fetchrow ) {
	$phrases{$phrase} = $id; 
    }

    # We should now have the %phrases hash fully populated ( from phrase table )


    # print form header.
    print "<center><table cellpadding=\"3\" cellspacing=\"0\" border=\"0\">\n";
    print "<form action=\"$self\" method=\"post\">\n";
    print "<input type=\"hidden\" name=\"page\" value=\"2\">\n";
    print "<tr><td colspan=\"2\" align=\"center\">";
    print "<input type=\"submit\" value=\"". $lex{Continue}. "\"></td></tr>\n";

    my $sth = $dbh->prepare("select translation, phrase from xlat_lang where phrase = ?");

    foreach my $phrase ( sort {lc $a cmp lc $b } keys %phrases ) {

	# read value(s) from translation table
	my $translation;
	$sth->execute( $phrase );
	while ( my ( $trans, $testphrase ) = $sth->fetchrow ) {
	    if ( $testphrase eq $phrase ) { 
		$translation = $trans;
		last; 
	    }
	}
	my $id = $phrases{$phrase};

	# filter tag identifiers
	$phrase =~ s/</&lt;/g;
	$phrase =~ s/>/&gt;/g;

	print "<tr><td align=\"right\">$phrase</td><td align=\"left\">";
	print "<input type=\"text\" name=\"$id\" value=\"$translation\" size=\"$maxfieldlength\">";
	print "</td></tr>\n";

    }


    # print form footer.
    print "<tr><td colspan=\"2\" align=\"center\">";
    print "<input type=\"submit\" value=\"". $lex{Continue}. "\"></td></tr>\n";
    print "</form></table>\n";

    print "</body></html>\n";

    exit;

}


#----------------
sub showStartPage {
#----------------

    print "<center><table cellpadding=\"3\" cellspacing=\"0\" border=\"0\">\n";
    print "<form action=\"$self\" method=\"post\">\n";
    print "<input type=\"hidden\" name=\"page\" value=\"1\">\n";

    print "<tr><td align=\"right\" colspan=\"2\">". $lex{Search};
    print " <input type=\"text\" name=\"sphrase\" size=\"10\"></td><td>";
    print "<select name=\"sposition\"><option>". $lex{'Start of Phrase'}. "</option>";
    print "<option>". $lex{'Everywhere in Phrase'}. "</option></select>\n";
    print $lex{'Blank=All'}. "</td></tr>\n";

    print "<tr><td colspan=\"2\" align=\"right\">";
    print "<input type=\"submit\" value=\"". $lex{Continue}. "\"></td></tr>\n";
    print "</form></table></body></html>\n";

    exit;

}


#---------------
sub updateRecords {
#---------------
    
    #foreach my $key ( sort keys %arr) { print "K:$key V:$arr{$key}<br>\n"; }

    print "<center><table cellpadding=\"3\" cellspacing=\"0\" border=\"1\">\n";
    print '<tr><th colspan=\"2\">'. $lex{Translation}. "</th></tr>\n";

    my $sth = $dbh->prepare("select phrase from xlat_phrase where id = ?");
    my $sth1 = $dbh->prepare("select id, phrase, translation from xlat_lang where phrase = ?");

    my $sth2 = $dbh->prepare("update xlat_lang set translation = ? where id = ?");
NEXTID:
    foreach my $id ( sort keys %arr ) {  # $key contains record id in phrase table

	# Read phrase
	$sth->execute( $id );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my $phrase = $sth->fetchrow;

	# Search for matching phrase record to update
	$sth1->execute( $phrase );
	if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	my $updateflag = 0;
	while ( my ( $tempid, $tempphrase, $temptrans ) = $sth1->fetchrow ) {
	    if ( $tempphrase eq $phrase ) { # we have a match
		$updateflag = 1;
		if ( $arr{$id} eq $temptrans ) { #skip, since no change
		    next NEXTID;
		} else {
		    $sth2->execute( $arr{$id}, $tempid );
		    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
		    if ( not $temptrans ) { $temptrans = "&nbsp;&nbsp;&nbsp;"; }
		    print "<tr><td>". $lex{Update}. "</td><td><b>$phrase:</b>";
		    print " $temptrans &gt; $arr{$id}</td></tr>\n";
		}
	    }
	}
	    
	if ( not $updateflag and $arr{$id} ) { # add a new record in translation table.
		    
	    my $sth3 = $dbh->prepare("insert into xlat_lang ( phrase, translation ) 
              values( ?, ?)");
	    $sth3->execute( $phrase, $arr{$id} );
	    if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; }
	    print "<tr><td>". $lex{Add}. "</td><td><b>$phrase:</b> $arr{$id}</td></tr>\n";
	}
    }

    print "</table>\n";
    print "<p>[ <a href=\"$eoypage\">". $lex{Eoy}. "</a> ]\n";
    print "</p></center></body></html>\n";

    exit;

} # End of writeRecords
