#!/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}. " $@
\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". $lex{'Edit Translation'}; print "\n"; print "$chartype\n\n"; print "[ ". $lex{Main}. " |\n"; print "". $lex{Eoy}. " ]\n"; print "

". $lex{'Edit Translation'}. "

\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}
\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 "
\n"; print "\n"; print "\n"; print "\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//>/g; print "\n"; } # print form footer. print "\n"; print "
"; print "
$phrase"; print ""; print "
"; print "
\n"; print "\n"; exit; } #---------------- sub showStartPage { #---------------- print "
\n"; print "\n"; print "\n"; print "\n"; print "\n"; print "
". $lex{Search}; print " "; print "\n"; print $lex{'Blank=All'}. "
"; print "
\n"; exit; } #--------------- sub updateRecords { #--------------- #foreach my $key ( sort keys %arr) { print "K:$key V:$arr{$key}
\n"; } print "
\n"; print '\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 = "   "; } print "\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 "\n"; } } print "
'. $lex{Translation}. "
". $lex{Update}. "$phrase:"; print " $temptrans > $arr{$id}
". $lex{Add}. "$phrase: $arr{$id}
\n"; print "

[ ". $lex{Eoy}. " ]\n"; print "

\n"; exit; } # End of writeRecords