From 7c1029bbcb0f020fa859ecc978ed8bffd8d9b6c1 Mon Sep 17 00:00:00 2001
From: bradymiller <bradymiller>
Date: Wed, 14 Apr 2010 06:35:31 +0000
Subject: [PATCH] Added support for language translation oustomization and
 logging

---
 interface/language/lang_constant.php   |   5 ++
 interface/language/lang_definition.php |  85 +++++++++++++++----
 interface/language/lang_language.php   |   8 +-
 interface/language/lang_manage.php     | 151 +++++++++++++++++++++++++++++++++
 interface/language/language.inc.php    |  62 +++++++++++++-
 interface/language/language.php        |  67 ++++++++-------
 6 files changed, 327 insertions(+), 51 deletions(-)
 create mode 100644 interface/language/lang_manage.php

diff --git a/interface/language/lang_constant.php b/interface/language/lang_constant.php
index f67936a30..7754d9fd2 100644
--- a/interface/language/lang_constant.php
+++ b/interface/language/lang_constant.php
@@ -17,8 +17,13 @@ if ($_POST['add']){
 	if ($err=='y'){
 		$val_constant=strip_escape_custom($_POST['constant_name']);
 	} else {
+	        //insert into the main table
 		$sql="INSERT INTO lang_constants SET constant_name='".formData('constant_name')."'"; 
 		SqlStatement ($sql);
+	    
+                //insert into the log table - to allow persistant customizations
+	      	insert_language_log('','',formData('constant_name'),'');
+	    
 		echo xl('Constant','','',' ') . strip_escape_custom($_POST['constant_name']) . xl('added','',' ','<br>');
 	}
 	
diff --git a/interface/language/lang_definition.php b/interface/language/lang_definition.php
index e1b8268a4..0942b825d 100644
--- a/interface/language/lang_definition.php
+++ b/interface/language/lang_definition.php
@@ -1,9 +1,35 @@
-<?php
-require_once("language.inc.php");
+
+  <form name='filterform' id='filterform' method='get' action='language.php' onsubmit="return top.restoreSession()">
+  <span class='text'>&nbsp;&nbsp;&nbsp;<?php xl('Filter for Constants','e','',':'); ?>
+  <input type='text' name='form_filter' size='8' value='' />
+  <?php xl('(% matches any string, _ matches any character)','e'); ?>
+  </form>
+  </span>
+
+  <div class="text">
+  <br>
+
+  <?php
+  /* menu */
+  $sql="SELECT * FROM lang_languages ORDER BY lang_id";
+  $res=SqlStatement($sql);
+  $string='|';
+  while ($row=SqlFetchArray($res)){
+    $string .= "| <a href='' onclick='return editLang(" . $row['lang_id'] . ")'>" . xl($row['lang_description']) . "</a> |";
+  }
+  $string.='|';
+  echo (xl('Edit definitions').": $string <br><br>");
 
 if ($_POST['load']) {
+  // set up the mysql collation string to ensure case is sensitive in the mysql queries
+  if (!$disable_utf8_flag) {
+    $case_sensitive_collation = "COLLATE utf8_bin";
+  }
+  else {
+    $case_sensitive_collation = "COLLATE latin_bin";
+  }
+
 	// query for entering new definitions it picks the cons_id because is existant.
-	$sql = "INSERT INTO lang_definitions (`cons_id`,`lang_id`,`definition`) VALUES  ";
   if (!empty($_POST['cons_id'])) {
     foreach ($_POST['cons_id'] as $key => $value) {
       $value = formDataCore($value,true);
@@ -11,29 +37,54 @@ if ($_POST['load']) {
       // do not create new blank definitions
       if ($value == "") continue;
 	
-      $sql .= " ('$key', ";
+      // insert into the main language tables
+      $sql = "INSERT INTO lang_definitions (`cons_id`,`lang_id`,`definition`) VALUES ";
+      $sql .= "('$key',";
       $sql .= "'" . formData('lang_id') . "',";
-      $sql .= "'" . $value . "'),";
+      $sql .= "'" . $value . "')";
+      SqlStatement($sql);
+		
+      // insert each entry into the log table - to allow persistant customizations
+      $sql = "SELECT lang_description, lang_code FROM lang_languages WHERE lang_id = '".formData('lang_id')."' LIMIT 1";
+      $res = SqlStatement($sql);
+      $row_l = SqlFetchArray($res);
+      $sql = "SELECT constant_name FROM lang_constants WHERE cons_id = '".$key."' LIMIT 1";
+      $res = SqlStatement($sql);
+      $row_c = SqlFetchArray($res);
+      insert_language_log(add_escape_custom($row_l['lang_description']), add_escape_custom($row_l['lang_code']), add_escape_custom($row_c['constant_name']), $value);
+	  
       $go = 'yes';
-    }
+    }  
   }
-	if ($go=='yes') {
-		$sql=substr($sql,0,-1);
-		if (SqlStatement($sql)) {
-			xl ("New Definition set added",'e');
-		}
-	}
+    
   // query for updating preexistant definitions uses def_id because there is no def yet.
   // echo ('<pre>');	print_r($_POST['def_id']);	echo ('</pre>');
   if (!empty($_POST['def_id'])) {
     foreach ($_POST['def_id'] as $key => $value) {
       $value = formDataCore($value,true);
-      $sql = "UPDATE `lang_definitions` SET `definition` = '$value' WHERE `def_id`='$key' LIMIT 1";
-      SqlStatement($sql);
-      $goMod = 'yes';
+	
+      // only continue if the definition is new
+      $sql = "SELECT * FROM lang_definitions WHERE def_id = '".$key."' AND definition = '".$value."' ".$case_sensitive_collation;
+      $res_test = SqlStatement($sql);
+      if (!SqlFetchArray($res_test)) {	
+        // insert into the main language tables
+        $sql = "UPDATE `lang_definitions` SET `definition` = '$value' WHERE `def_id`='$key' LIMIT 1";
+        SqlStatement($sql);
+	
+        // insert each entry into the log table - to allow persistant customizations	
+        $sql = "SELECT ll.lang_description, ll.lang_code, lc.constant_name ";
+        $sql .= "FROM lang_definitions AS ld, lang_languages AS ll, lang_constants AS lc ";
+        $sql .= "WHERE ld.def_id = '".$key."' ";
+        $sql .= "AND ll.lang_id = ld.lang_id AND lc.cons_id = ld.cons_id LIMIT 1";
+        $res = SqlStatement($sql);
+        $row = SqlFetchArray($res);
+	insert_language_log(add_escape_custom($row['lang_description']), add_escape_custom($row['lang_code']), add_escape_custom($row['constant_name']), $value);
+	  
+	$go = 'yes';
+      }
     }
-    if ($goMod=='yes') xl("New Definition set added",'e');
   }
+  if ($go=='yes') xl("New Definition set added",'e');
 }
 
 if ($_GET['edit'] != ''){
@@ -99,5 +150,5 @@ if ($_GET['edit'] != ''){
 	echo ('<tr><td colspan=3><INPUT TYPE="submit" name="load" Value="' . xl('Load Definitions') . '"></td></tr>');
 	echo ('</FORM></table>');
 }
-
+    
 ?>
diff --git a/interface/language/lang_language.php b/interface/language/lang_language.php
index 0689d3a9a..8bf006b15 100644
--- a/interface/language/lang_language.php
+++ b/interface/language/lang_language.php
@@ -1,5 +1,4 @@
 <?php
-require_once("language.inc.php");
 
 if ($_POST['add']){
 	//validate	
@@ -19,9 +18,14 @@ if ($_POST['add']){
 		$val_lang_code=strip_escape_custom($_POST['lang_code']);
 		$val_lang_name=strip_escape_custom($_POST['lang_name']);
 	} else {
+	        //insert into the main table
 		$sql="INSERT INTO lang_languages SET lang_code='".formData('lang_code')."', lang_description='".formData('lang_name')."'";
 		SqlStatement ($sql);
-		xl('Language definition added','e','','<br>');
+	        
+		//insert into the log table - to allow persistant customizations
+		insert_language_log(formData('lang_name'),formData('lang_code'),'','');
+	    
+	        xl('Language definition added','e','','<br>');
 	}
 }
 
diff --git a/interface/language/lang_manage.php b/interface/language/lang_manage.php
new file mode 100644
index 000000000..ebbb80ab1
--- /dev/null
+++ b/interface/language/lang_manage.php
@@ -0,0 +1,151 @@
+<?php
+if ($_POST['check'] || $_POST['synchronize']){
+  // set up flag if only checking for changes (ie not performing synchronization)
+  $checkOnly = 0;
+  if ($_POST['check']) {
+    $checkOnly = 1;
+  }
+  
+  // set up the mysql collation string to ensure case is sensitive in the mysql queries
+  if (!$disable_utf8_flag) {
+    $case_sensitive_collation = "COLLATE utf8_bin";   
+  }
+  else {
+    $case_sensitive_collation = "COLLATE latin_bin";
+  }
+  $difference = 0; //flag
+    
+  //  
+  // collect and display(synchronize) new custom languages
+  //
+  $sql = "SELECT lang_description FROM lang_languages";
+  $res = SqlStatement($sql);
+  $row_main = array();
+  while ($row=SqlFetchArray($res)){
+    $row_main[] = $row['lang_description'];
+  }
+  $sql = "SELECT lang_description FROM lang_custom";
+  $res = SqlStatement($sql);
+  $row_custom = array();
+  while ($row=SqlFetchArray($res)){
+    $row_custom[] = $row['lang_description'];
+  }
+  $custom_languages = array_diff(array_unique($row_custom),array_unique($row_main));
+  foreach ($custom_languages as $var) {
+    if ($var=='') continue;
+    echo xl('Following is a new custom language:')." ".$var."<BR>";   
+    if (!$checkOnly) {
+      // add the new language (first collect the language code)
+      $sql = "SELECT lang_code FROM lang_custom WHERE constant_name='' AND lang_description='".add_escape_custom($var)."' ".$case_sensitive_collation." LIMIT 1";
+      $res = SqlStatement($sql);
+      $row = SqlFetchArray($res);
+      $sql="INSERT INTO lang_languages SET lang_code='".add_escape_custom($row['lang_code'])."', lang_description='".add_escape_custom($var)."'";
+      SqlStatement($sql);
+      echo xl('Synchronized new custom language:')." ".$var."<BR><BR>";
+    }
+    $difference = 1;
+  }
+    
+  //  
+  // collect and display(synchronize) new custom constants
+  //  
+  $sql = "SELECT constant_name FROM lang_constants";
+  $res = SqlStatement($sql);
+  $row_main = array();
+  while ($row=SqlFetchArray($res)){
+    $row_main[] = $row['constant_name'];
+  }
+  $sql = "SELECT constant_name FROM lang_custom";
+  $res = SqlStatement($sql);
+  $row_custom = array();
+  while ($row=SqlFetchArray($res)){
+    $row_custom[] = $row['constant_name'];
+  }
+  $custom_constants = array_diff(array_unique($row_custom),array_unique($row_main));
+  foreach ($custom_constants as $var) {
+    if ($var=='') continue;
+    echo xl('Following is a new custom constant:')." ".$var."<BR>";
+    if (!$checkOnly) {
+      // add the new constant
+      $sql="INSERT INTO lang_constants SET constant_name='".add_escape_custom($var)."'";
+      SqlStatement($sql);
+      echo xl('Synchronized new custom constant:')." ".$var."<BR><BR>";
+    }
+    $difference = 1;
+  }
+    
+  //  
+  // collect and display(synchronize) custom definitions
+  //
+  $sql = "SELECT lang_description, lang_code, constant_name, definition FROM lang_custom WHERE lang_description != '' AND constant_name != ''";
+  $res = SqlStatement($sql);
+  while ($row=SqlFetchArray($res)){
+      
+    // collect language id
+    $sql = "SELECT lang_id FROM lang_languages WHERE lang_description='".add_escape_custom($row['lang_description'])."' ".$case_sensitive_collation." LIMIT 1";
+    $res2 = SqlStatement($sql);
+    $row2 = SqlFetchArray($res2);
+    $language_id=$row2['lang_id'];
+      
+    // collect constant id
+    $sql = "SELECT cons_id FROM lang_constants WHERE constant_name='".add_escape_custom($row['constant_name'])."' ".$case_sensitive_collation." LIMIT 1";
+    $res2 = SqlStatement($sql);
+    $row2 = SqlFetchArray($res2);
+    $constant_id=$row2['cons_id'];
+      
+    // collect definition id (if it exists)
+    $sql = "SELECT def_id FROM lang_definitions WHERE cons_id='".add_escape_custom($constant_id)."' AND lang_id='".add_escape_custom($language_id)."' LIMIT 1";
+    $res2 = SqlStatement($sql);
+    $row2 = SqlFetchArray($res2);
+    $def_id=$row2['def_id'];
+    
+    if ($def_id) {
+      //definition exist, so check to see if different
+      $sql = "SELECT * FROM lang_definitions WHERE def_id = '".add_escape_custom($def_id)."' AND definition = '".add_escape_custom($row['definition'])."' ".$case_sensitive_collation;
+      $res_test = SqlStatement($sql);
+      if (SqlFetchArray($res_test)) {
+	//definition not different
+        continue;
+      }
+      else {
+        //definition is different
+        echo xl('Following is a new definition (Language, Constant, Definition):')." ".$row['lang_description']." ".$row['constant_name']." ".$row['definition']."<BR>";
+        if (!$checkOnly) {
+          //add new definition
+          $sql = "UPDATE `lang_definitions` SET `definition` = '".add_escape_custom($row['definition'])."' WHERE `def_id`='".add_escape_custom($def_id)."' LIMIT 1";
+          SqlStatement($sql);
+          echo xl('Synchronized new definition (Language, Constant, Definition):')." ".$row['lang_description']." ".$row['constant_name']." ".$row['definition']."<BR><BR>";
+        }
+	$difference = 1;
+      }
+    }
+    else {
+      echo xl('Following is a new definition (Language, Constant, Definition):')." ".$row['lang_description']." ".$row['constant_name']." ".$row['definition']."<BR>";
+      if (!$checkOnly) {
+        //add new definition
+        $sql = "INSERT INTO lang_definitions (cons_id,lang_id,definition) VALUES ('".add_escape_custom($constant_id)."','".add_escape_custom($language_id)."','".add_escape_custom($row['definition'])."')";
+        SqlStatement($sql);
+        echo xl('Synchronized new definition (Language, Constant, Definition):')." ".$row['lang_description']." ".$row['constant_name']." ".$row['definition']."<BR><BR>";
+      }
+      $difference = 1;
+    } 
+  }
+  if (!$difference) {
+    echo xl('The translation tables are synchronized.');
+  }
+}
+?>
+
+<TABLE>
+<FORM name="manage_form" METHOD=POST ACTION="?m=manage" onsubmit="return top.restoreSession()">
+  <TR>
+    <TD><INPUT TYPE="submit" name="check" value="<?php xl('Check','e'); ?>"></TD>
+    <TD> <?php xl('(Check for Differences of Translations with Custom Language Table)','e'); ?></TD>  
+  </TR>
+  <TR></TR>
+  <TR>
+    <TD><INPUT TYPE="submit" name="synchronize" value="<?php xl('Synchronize','e'); ?>"></TD>
+    <TD> <?php xl('(Synchronize Translations with Custom Language Table)','e'); ?></TD>
+  </TR>
+</FORM>
+</TABLE>	
diff --git a/interface/language/language.inc.php b/interface/language/language.inc.php
index e29a4167f..df9c39bce 100644
--- a/interface/language/language.inc.php
+++ b/interface/language/language.inc.php
@@ -10,12 +10,68 @@ if (!$thisauth) {
   exit();
  }
 
-
-
 function check_pattern ($data,$pat) {
 	if (ereg ($pat, $data)) { return TRUE ; } else { RETURN FALSE; }
 }
 
-//$pat="^(19[0-9]{2}|20[0-1]{1}[0-9]{1})-(0[1-9]|1[0-2])-(0[1-9]{1}|1[0-9]{1}|2[0-9]{1}|3[0-1]{1})$";
+// Function to insert/modify items in the language log table, lang_custom
+//  NOTE THAT ALL PARAMETERS SHOULD ALREADY BE ESCAPED TO PREPARE FOR MYSQL QUERIES
+//
+function insert_language_log($lang_desc,$lang_code,$cons_name,$def) {
+  global $enable_custom_language_logging;
+  if (!$enable_custom_language_logging) return;
+    
+  // set up the mysql collation string to ensure case is sensitive in the mysql queries
+  if (!$disable_utf8_flag) {
+    $case_sensitive_collation = "COLLATE utf8_bin";
+  }
+  else {
+    $case_sensitive_collation = "COLLATE latin_bin";
+  }
+    
+    
+  if ($cons_name == '') {
+    // NEW LANGUAGE
+    // (ensure not a repeat log entry)
+    $sql = "SELECT * FROM lang_custom WHERE constant_name='' AND lang_description='".$lang_desc."' ".$case_sensitive_collation;
+    $res_test = SqlStatement($sql);
+    if (!SqlFetchArray($res_test)) {
+      $sql="INSERT INTO lang_custom SET lang_code='".$lang_code."', lang_description='".$lang_desc."'";
+      SqlStatement ($sql);
+    }      
+  }
+  elseif ($lang_desc == '') {
+    // NEW CONSTANT
+    // (ensure not a repeat entry)
+    $sql = "SELECT * FROM lang_custom WHERE lang_description='' AND constant_name='".$cons_name."' ".$case_sensitive_collation;
+    $res_test = SqlStatement($sql);
+    if (!SqlFetchArray($res_test)) {
+      $sql="INSERT INTO lang_custom SET constant_name='".$cons_name."'";
+      SqlStatement ($sql);
+    }      
+  }
+  else {
+    // FULL ENTRY
+    // (ensure not a repeat log entry)
+    $sql = "SELECT * FROM lang_custom WHERE lang_description='".$lang_desc."' AND constant_name='".$cons_name."' AND definition='".$def."' ".$case_sensitive_collation;
+    $res_test = SqlStatement($sql);
+    if (!SqlFetchArray($res_test)) {
+      // either modify already existing log entry or create a new one
+      $sql = "SELECT * FROM lang_custom WHERE lang_description='".$lang_desc."' AND constant_name='".$cons_name."' ".$case_sensitive_collation;
+      $res_test2 = SqlStatement($sql);
+      if (SqlFetchArray($res_test2)) {
+        // modify existing log entry(s)
+        $sql = "UPDATE lang_custom SET definition = '".$def."' WHERE lang_description='".$lang_desc."' AND constant_name='".$cons_name."' ".$case_sensitive_collation;
+        SqlStatement($sql);
+      }
+      else {
+        // create new log entry
+        $sql = "INSERT INTO lang_custom (lang_description,lang_code,constant_name,definition) VALUES ";
+        $sql .= "('".$lang_desc."','".$lang_code."','".$cons_name."','".$def."')";
+        SqlStatement($sql);
+      }
+    }
+  }
+}
 
 ?>
diff --git a/interface/language/language.php b/interface/language/language.php
index b57793777..f07190947 100644
--- a/interface/language/language.php
+++ b/interface/language/language.php
@@ -7,6 +7,20 @@ include_once("../../library/acl.inc");
 require_once("language.inc.php");
 require_once("$srcdir/formdata.inc.php");
 
+// Setting to enable custom logging of language translations
+// (Note that the below mysql table is required for this function)
+$enable_custom_language_logging=false;
+/* Note that the table mysql lang_custom is required for this function,
+which can ce accomplished with following script in mysql:
+DROP TABLE IF EXISTS lang_custom;
+CREATE TABLE lang_custom (
+  lang_description varchar(100) NOT NULL default '',
+  lang_code char(2) NOT NULL default '',
+  constant_name varchar(255) NOT NULL default '',
+  definition mediumtext NOT NULL default ''
+) ENGINE=MyISAM ;
+*/
+
 //START OUT OUR PAGE....
 ?>
 <html>
@@ -18,7 +32,7 @@ require_once("$srcdir/formdata.inc.php");
 
 <script language='JavaScript'>
 function editLang(lang_id) {
- var filter = document.forms[0].form_filter.value;
+ var filter = document.forms[1].form_filter.value;
  top.restoreSession();
  window.location = '?m=definition&edit=' + lang_id + '&filter=' + encodeURIComponent(filter);
  return false;
@@ -26,46 +40,41 @@ function editLang(lang_id) {
 </script>
 </head>
 
-<body class="body_top">
-<form name='filterform' id='filterform' method='get' action='language.php'>
+<body class="body_top">	
+<form name='translation' id='translation' method='get' action='language.php' onsubmit="return top.restoreSession()">
 <input type='hidden' name='m' value='<?php echo strip_escape_custom($_GET['m']); ?>' />
 <input type='hidden' name='edit' value='<?php echo strip_escape_custom($_GET['edit']); ?>' />
 <span class="title"><?php  xl('Multi Language Tool','e') ?></span>
-<span class='text'>&nbsp;&nbsp;&nbsp;<?php xl('Filter for Constants','e','',':'); ?>
-<input type='text' name='form_filter' size='8' value='' />
-<?php xl('(% matches any string, _ matches any character)','e'); ?>
+<table>
+ <tr>
+  <td class="small" colspan='4'>
+   <?php if ($enable_custom_language_logging) { ?>
+   <a href="?m=manage" onclick="top.restoreSession()"><?php xl('Manage Translations','e'); ?></a> |
+   <?php  } ?>
+   <a href="?m=language" onclick="top.restoreSession()"><?php xl('Add Language','e'); ?></a> | 
+   <a href="?m=constant" onclick="top.restoreSession()"><?php xl('Add Constant','e'); ?></a> |
+   <a href="?m=definition" onclick="top.restoreSession()"><?php xl('Edit Definitions','e'); ?></a>
+  </td>
+ </tr>
+</table>
 </form>
-</span>
-
-<div class="text">
-<br>
-
+	
 <?php
-/* menu */
-$sql="SELECT * FROM lang_languages ORDER BY lang_id";
-$res=SqlStatement($sql);
-$string='|';
-while ($row=SqlFetchArray($res)){
-  $string .= "| <a href='' onclick='return editLang(" . $row['lang_id'] . ")'>" . xl($row['lang_description']) . "</a> |";
-}
-$string.='|';
-
-echo ("<a href=\"?m=language&language=add\">".xl('Add Language')."</a> or ");
-echo ("<a href=\"?m=constant&constant=add\">".xl('Add Constant')."</a> or ");
-echo (xl('Edit definitions').": $string <br><br>");
-
 switch ($_GET['m']):
 	case 'definition':
 		include_once('lang_definition.php');
-	break;
+	        break;
 	case 'constant':
 		include_once('lang_constant.php');
-	break;
-		case 'language':
+	        break;
+	case 'language':
 		include_once('lang_language.php');
-	break;
+	        break;
+        case 'manage':
+                include_once('lang_manage.php');
+                break;
 endswitch;
 ?>
 
 <BR><A HREF="lang.info.html" TARGET="_blank"><?php xl('Info','e'); ?></A>
-</div>
+</body>
-- 
2.11.4.GIT