<?php
/*
  Released under the GNU General Public License
*/
  // Set the level of error reporting
  error_reporting(E_ALL ^ E_NOTICE);
  
  require('includes/classes/tableProcessor.php');
  require('includes/functions/database.php');
  require('includes/functions/general.php');
  require('includes/languages/language_list.php');
  // Load JsHttpRequest backend.
  require_once "includes/classes/JsHttpRequest.php";
  // Create main library object. You MUST specify page encoding!
  $JsHttpRequest =& new JsHttpRequest("windows-1251");
  // Store resulting data in $_RESULT array (will appear in req.responseJs).
  
  if (!isset($_POST['language_code'])) $_POST['language_code'] = 'en';
  require('includes/languages/'. $languages_list[$_POST['language_code']]['directory'] . '/db_access.php');
  
  // check to see if the DB access variables are available
  if (!isset($_POST['cre_existing_server']) || !isset($_POST['cre_existing_username']) || !isset($_POST['cre_existing_password']) || !isset($_POST['cre_existing_database']) || 
      !isset($_POST['DB_SERVER']) || !isset($_POST['DB_SERVER_USERNAME']) || !isset($_POST['DB_SERVER_PASSWORD']) || !isset($_POST['DB_DATABASE'])) {
    $GLOBALS['_RESULT'] = array('code' => 'error',
                                'action' => $_POST['function_call'],
                                'msg' => DB_ERROR_00
                               );
    return;
  }
  // connect to the database
  $db_link_old = '';
  $db_link_new = '';
  
  $error_msg = '<font color="#000000"><b>%s - %s<br><br>%s<br><br><small><font color="#ff0000">[OSC STOP]</font></small><br><br></b></font>';
  
  // take action based on the function being called
  switch ($_POST['function_call']) {
    case '_copy':
      osc_db_connect($_POST['DB_SERVER'], $_POST['DB_SERVER_USERNAME'], $_POST['DB_SERVER_PASSWORD'], 'db_link_new');
      osc_db_connect($_POST['cre_existing_server'], $_POST['cre_existing_username'], $_POST['cre_existing_password'], 'db_link_old');
      
      $new_tables = array();
      $sql = "SHOW TABLES FROM " . $_POST['DB_DATABASE'];
      $tables_query = mysql_query($sql, $db_link_new);
      if ($tables_query === false) {
        $GLOBALS['_RESULT'] = array('code' => 'error',
                                    'action' => $_POST['function_call'],
                                    'msg' => sprintf($error_msg, mysql_errno($db_link_new), mysql_error($db_link_new), $sql));
        return;
      }
      while ($row = mysql_fetch_row($tables_query)) {
        $new_tables[] = $row[0];
      }
      mysql_free_result($tables_query);
      
      // find out which tables are to be moved over
      $table_names = array();
      $sql = "SHOW TABLES FROM " . $_POST['cre_existing_database'];
      $tables_query = mysql_query($sql, $db_link_old);
      if ($tables_query === false) {
        $GLOBALS['_RESULT'] = array('code' => 'error',
                                    'action' => $_POST['function_call'],
                                    'msg' => sprintf($error_msg, mysql_errno($db_link_old), mysql_error($db_link_old), $sql));
        return;
      }
      while ($row = mysql_fetch_row($tables_query)) {
        $table_names[] = $row[0];
      }
      mysql_free_result($tables_query);
      
      // Set the database that is in use
      if (mysql_select_db($_POST['cre_existing_database'], $db_link_old) === false) {
        $GLOBALS['_RESULT'] = array('code' => 'error',
                                    'action' => $_POST['function_call'],
                                    'msg' => sprintf($error_msg, mysql_errno($db_link_old), mysql_error($db_link_old), $sql));
        return;
      }
      
      // process the tables
      $exclude_tables = array('configuration', 'configuration_group', 'orders_pay_methods', 'orders_ship_methods');
      foreach ($table_names as $table) {
        if (in_array($table, $exclude_tables)) continue;
        
        // drop tables in new db
        if (in_array($table, $new_tables)) {
          $sql = "DROP TABLE " . $_POST['DB_DATABASE'] . "." . $table;
          if (mysql_query($sql, $db_link_new) === false) {
            $GLOBALS['_RESULT'] = array('code' => 'error',
                                        'action' => $_POST['function_call'],
                                        'msg' => sprintf($error_msg, mysql_errno($db_link_new), mysql_error($db_link_new), $sql));
            return;
          }
        }
        
        // get the status information for the table
        $sql = "SHOW TABLE STATUS LIKE '$table'";
        $status_query = mysql_query($sql, $db_link_old);
        if ($status_query === false) {
          $GLOBALS['_RESULT'] = array('code' => 'error',
                                      'action' => $_POST['function_call'],
                                      'msg' => sprintf($error_msg, mysql_errno($db_link_old), mysql_error($db_link_old), $sql));
          return;
        }
        $status = mysql_fetch_assoc($status_query);
        
        // start building the output
        $str = 'CREATE TABLE ' . $_POST['DB_DATABASE'] . "." . $table . ' (' . "\n";
    
        // loop thru the columns and output them
        $sql = "SHOW COLUMNS FROM " . $table;
        $table_query = mysql_query($sql, $db_link_old);
        if ($table_query === false) {
          $GLOBALS['_RESULT'] = array('code' => 'error',
                                      'action' => $_POST['function_call'],
                                      'msg' => sprintf($error_msg, mysql_errno($db_link_old), mysql_error($db_link_old), $sql));
          return;
        }
        while ($row = mysql_fetch_assoc($table_query)) {
          $nullValue = '';
          if ($row['Null'] == 'NO') $nullValue = ' NOT NULL';
          // because of PHP NULL handling, our normal test  $row['Default'] == 'NULL' does not work
          if ($row['Null'] == 'YES' && !is_string($row['Default']) && is_null($row['Default'])) {
            $defaultValue = " DEFAULT NULL";
          } else {
            if (substr($row['Type'], 0, 3) == 'int' ||
                substr($row['Type'], 0, 7) == 'tinyint' ||
                substr($row['Type'], 0, 8) == 'smallint' ||
                substr($row['Type'], 0, 6) == 'bigint' ||
                substr($row['Type'], 0, 9) == 'mediumint' ) {
              if ($row['Default'] == '') {
                $defaultValue = '';
              } else {
                $defaultValue = " DEFAULT " . $row['Default'];
              }
            } elseif ($row['Type'] == 'timestamp' && $row['Default'] == 'CURRENT_TIMESTAMP') {
              $defaultValue = " DEFAULT CURRENT_TIMESTAMP";
            } else {
              if ($row['Default'] == '') {
                $defaultValue = " DEFAULT ''";
              } else {
                $defaultValue = " DEFAULT '" . $row['Default'] . "'";
              }
            }
          }
          $extraValue = '';
          if ($row['Extra'] != '') $extraValue =  ' ' . $row['Extra'];
          $str .= '  ' . $row['Field'] . ' ' . $row['Type'] . $nullValue . $defaultValue . $extraValue . ',' . "\n";
        }
            
        // loop thru the indexes and output them
        $tableUniqueIndexes = array();
        $tableNonUniqueIndexes = array();
        $primaryKey = '';
        $sql = "SHOW INDEX FROM " . $table;
        $table_query = mysql_query($sql, $db_link_old);
        if ($table_query === false) {
          $GLOBALS['_RESULT'] = array('code' => 'error',
                                      'action' => $_POST['function_call'],
                                      'msg' => sprintf($error_msg, mysql_errno($db_link_old), mysql_error($db_link_old), $sql));
          return;
        }
        while ($row = mysql_fetch_assoc($table_query)) {
          if ( $row['Key_name'] == 'PRIMARY' ) {
            $primaryKey .= $row['Column_name'] . ',';
          } elseif ( $row['Non_unique'] == 1 ) {
            if ( isset($tableNonUniqueIndexes[$row['Key_name']]) ) {
              $tableNonUniqueIndexes[$row['Key_name']] .= $row['Column_name'] . ',';
            } else {
              $tableNonUniqueIndexes[$row['Key_name']] = $row['Column_name'] . ',';
            }
          } else {
            if ( isset($tableUniqueIndexes[$row['Key_name']]) ) {
              $tableUniqueIndexes[$row['Key_name']] .= $row['Column_name'] . ',';
            } else {
              $tableUniqueIndexes[$row['Key_name']] = $row['Column_name'] . ',';
            }
          }
        }
        
        // if there is no key of any kind, the str needs to be modified
        if ($primaryKey == '' && count($tableUniqueIndexes) == 0 && count($tableNonUniqueIndexes) == 0 ) {
          $str = substr($str, 0 ,-2);  // remove the new line and the comma
          $str .= "\n";  // add the new line back
        }
    
        if ($primaryKey != '') {
          if ( count($tableUniqueIndexes) > 0 || count($tableNonUniqueIndexes) > 0 ) {
            $str .= '  PRIMARY KEY (' . substr($primaryKey, 0, -1) . '),' . "\n";
          } else {
            $str .= '  PRIMARY KEY (' . substr($primaryKey, 0, -1) . ')' . "\n";
          }
        }
        $i = 1;
        $last = count($tableUniqueIndexes);
        foreach ($tableUniqueIndexes as $key => $value) {
          if ($i < $last) {
            $str .= '  UNIQUE KEY ' . $key . ' (' . substr($value, 0 ,-1) . '),' . "\n";
          } else {
            if (count($tableNonUniqueIndexes) > 0) {
              $str .= '  UNIQUE KEY ' . $key . ' (' . substr($value, 0 ,-1) . '),' . "\n";
            } else {
              $str .= '  UNIQUE KEY ' . $key . ' (' . substr($value, 0 ,-1) . ')' . "\n";
            }
          }
          ++$i;
        }
        $i = 1;
        $last = count($tableNonUniqueIndexes);
        foreach ($tableNonUniqueIndexes as $key => $value) {
          if ($i < $last) {
            $str .= '  KEY ' . $key . ' (' . substr($value, 0 ,-1) . '),' . "\n";
          } else {
            $str .= '  KEY ' . $key . ' (' . substr($value, 0 ,-1) . ')' . "\n";
          }
          ++$i;
        }
    
        // finish up building the output
        $auto = '';
        if ($status['Auto_increment'] != '') $auto = ' AUTO_INCREMENT=' . $status['Auto_increment'];
        $str .= ') ENGINE=' . $status['Engine'] . ' DEFAULT COLLATE=' . $status['Collation'] . $auto . ';' . "\n";
        
    
        $sql = $str;
        if (mysql_query($sql, $db_link_new) === false) {
          $GLOBALS['_RESULT'] = array('code' => 'error',
                                      'action' => $_POST['function_call'],
                                      'msg' => sprintf($error_msg, mysql_errno($db_link_new), mysql_error($db_link_new), $sql));
          return;
        }
        
        // proceed with moving any data over
        $sql = "SELECT * FROM " . $table;
        $data_query = mysql_query($sql, $db_link_old);
        if ($data_query === false) {
          $GLOBALS['_RESULT'] = array('code' => 'error',
                                      'action' => $_POST['function_call'],
                                      'msg' => sprintf($error_msg, mysql_errno($db_link_old), mysql_error($db_link_old), $sql));
          return;
        }
        while ($row = mysql_fetch_assoc($data_query)) {
          $sql = "INSERT INTO " . $_POST['DB_DATABASE'] . "." . $table . " SET ";
          foreach ($row as $column => $value) {
            $sql .= $column . "=" . "'" . addslashes($value) . "', ";
          }
          $sql = substr($sql, 0, -2);
          if (mysql_query($sql, $db_link_new) === false) {
            $GLOBALS['_RESULT'] = array('code' => 'error',
                                        'action' => $_POST['function_call'],
                                        'msg' => sprintf($error_msg, mysql_errno($db_link_new), mysql_error($db_link_new), $sql));
            return;
          }
        }
      }
      
      // special logic is needded to move the orders_pay_methods and orders_ship_methods tables
      // becuase of the change in the primary key.  This affect 6.15 not 6.2, but works for both.
      // After the restructure, the new tables will be created, so then a more standard copy can occur.
      
      $GLOBALS['_RESULT'] = array('code' => 'success',
                                  'action' => $_POST['function_call'],
                                  'msg' => ''
                                 );
      return;
      break;
      
    case '_restruct':
      $tblProcessor = new tableProcessor();
      
      // call the method to encode the current table
      $currTable = $tblProcessor->getTableStructure($_POST['DB_SERVER'], $_POST['DB_SERVER_USERNAME'], $_POST['DB_SERVER_PASSWORD'], $_POST['DB_DATABASE']);
      
      // call the method to encode the reference table
      $refTable = file_get_contents('sql/reference-CRELoaded_Standard_6_3.xml');
      
      // now what is needed to conver the current table intot he same struture and the reference table
      $diffTable = $tblProcessor->diffTableStructure($currTable, $refTable, true);
      
      unset($currTable);
      unset($refTable);
      
      $action_results = $tblProcessor->applyTableChanges($diffTable, $_POST['DB_SERVER'], $_POST['DB_SERVER_USERNAME'], $_POST['DB_SERVER_PASSWORD'], $_POST['DB_DATABASE']);
      foreach ($action_results as $result) {
        if ($result['success'] == 'FALSE') {
          $GLOBALS['_RESULT'] = array('code' => 'error',
                                      'action' => $_POST['function_call'],
                                      'msg' => $result['sql'] . '<br>' . $result['msg']
                                     );
          return;
        }
      }
      
      $GLOBALS['_RESULT'] = array('code' => 'success',
                                  'action' => $_POST['function_call'],
                                  'msg' => ''
                                 );
      return;
      break;
      
    case '_cust':
      osc_db_connect($_POST['DB_SERVER'], $_POST['DB_SERVER_USERNAME'], $_POST['DB_SERVER_PASSWORD'], 'db_link_new');
      osc_db_connect($_POST['cre_existing_server'], $_POST['cre_existing_username'], $_POST['cre_existing_password'], 'db_link_old');
      
      // find out which tables are to be moved over
      $sql = "SELECT customers_id, customers_default_address_id FROM " . $_POST['DB_DATABASE'] . ".customers";
      $loop_query = mysql_query($sql, $db_link_new);
      if ($loop_query === false) {
            $GLOBALS['_RESULT'] = array('code' => 'error',
                                    'action' => $_POST['function_call'],
                                    'msg' => sprintf($error_msg, mysql_errno($db_link_new), mysql_error($db_link_new), $sql));
            return;
          }
      while ($new = osc_db_fetch_array($loop_query)) {
        // read in the existing table data
        $sql = "SELECT customers_email_address, customers_telephone, customers_fax FROM " . $_POST['cre_existing_database'] . ".customers WHERE customers_id = " . $new['customers_id'];
        $old_query = mysql_query($sql, $db_link_old);
        if ($old_query === false) {
            $GLOBALS['_RESULT'] = array('code' => 'error',
                                    'action' => $_POST['function_call'],
                                    'msg' => sprintf($error_msg, mysql_errno($db_link_old), mysql_error($db_link_old), $sql));
            return;
          }
        $old = osc_db_fetch_array($old_query);
        $sql = "UPDATE " . $_POST['DB_DATABASE'] . ".address_book SET entry_telephone = '" . $old['customers_telephone'] . "', entry_fax = '" . $old['customers_telephone'] . "', entry_email_address = '" . $old['customers_email_address'] . "' WHERE customers_id = " . $new['customers_id'];
        if (mysql_query($sql, $db_link_new) === false) {
            $GLOBALS['_RESULT'] = array('code' => 'error',
                                    'action' => $_POST['function_call'],
                                    'msg' => sprintf($error_msg, mysql_errno($db_link_new), mysql_error($db_link_new), $sql));
            return;
          }
      }
      
      $GLOBALS['_RESULT'] = array('code' => 'success',
                                  'action' => $_POST['function_call'],
                                  'msg' => ''
                                 );
      return;
      break;
      
    case '_config':
      // load in the standard configuration data
      osc_db_connect($_POST['DB_SERVER'], $_POST['DB_SERVER_USERNAME'], $_POST['DB_SERVER_PASSWORD']);
      osc_db_install($_POST['DB_DATABASE'], 'sql/creloaded_configdata.sql');
      osc_db_close();
      
      // now process the keys from the existing database
      osc_db_connect($_POST['DB_SERVER'], $_POST['DB_SERVER_USERNAME'], $_POST['DB_SERVER_PASSWORD'], 'db_link_new');
      osc_db_connect($_POST['cre_existing_server'], $_POST['cre_existing_username'], $_POST['cre_existing_password'], 'db_link_old');
      
      // include the list of configuration keys that have been removed from the system
      require 'sql/reference-CRELoaded_Standard_Removed_Keys.php';
      // need to capiture which keys have been processed, so they are not reporcessed
      $processed_configuration_keys = array();
      
      /*
      // remove all the module_% from the new table
      $sql = "DELETE FROM " . $_POST['DB_DATABASE'] . ".configuration WHERE configuration_key LIKE 'module\_%'";
      if (mysql_query($sql, $db_link_new) === false) {
        $GLOBALS['_RESULT'] = array('code' => 'error',
                                    'action' => $_POST['function_call'],
                                    'msg' => sprintf($error_msg, mysql_errno($db_link_new), mysql_error($db_link_new), $sql));
        return;
      }
      */
      
      // Process all the pre-loaded configuration kyes, get their existing values
      $sql = "SELECT configuration_key FROM " . $_POST['DB_DATABASE'] . ".configuration ";
      $loop_query = mysql_query($sql, $db_link_new);
      if ($loop_query === false) {
        $GLOBALS['_RESULT'] = array('code' => 'error',
                                    'action' => $_POST['function_call'],
                                    'msg' => sprintf($error_msg, mysql_errno($db_link_new), mysql_error($db_link_new), $sql));
        return;
      }
      while ($new = osc_db_fetch_array($loop_query)) {
        $processed_configuration_keys[] = $new['configuration_key'];  // record this key as processed
        // read in the existing table data
        $sql = "SELECT configuration_value FROM " . $_POST['cre_existing_database'] . ".configuration WHERE configuration_key = '" . $new['configuration_key'] . "'";
        $old_query = mysql_query($sql, $db_link_old);
        if ($old_query === false) {
          $GLOBALS['_RESULT'] = array('code' => 'error',
                                    'action' => $_POST['function_call'],
                                    'msg' => sprintf($error_msg, mysql_errno($db_link_old), mysql_error($db_link_old), $sql));
          return;
        }
        if (osc_db_num_rows($old_query) > 0) {
          $old = osc_db_fetch_array($old_query);
          $sql = "UPDATE " . $_POST['DB_DATABASE'] . ".configuration SET configuration_value = '" . addslashes($old['configuration_value']) . "' WHERE configuration_key = '" . $new['configuration_key'] . "'";
          if (!mysql_query($sql, $db_link_new)) {
            $GLOBALS['_RESULT'] = array('code' => 'error',
                                        'action' => $_POST['function_call'],
                                        'msg' => sprintf($error_msg, mysql_errno($db_link_new), mysql_error($db_link_new), $sql));
            return;
          }
        }
      }
      
      // copy all the remaining exiting configuration keys over
      $sql = "SELECT * FROM " . $_POST['cre_existing_database'] . ".configuration ";
      $loop_query = mysql_query($sql, $db_link_old);
      if ($loop_query === false) {
        $GLOBALS['_RESULT'] = array('code' => 'error',
                                    'action' => $_POST['function_call'],
                                    'msg' => sprintf($error_msg, mysql_errno($db_link_old), mysql_error($db_link_old), $sql));
        return;
      }
      while ($old = mysql_fetch_assoc($loop_query)) {
        if (in_array($old['configuration_key'], $processed_configuration_keys)) continue;
        if (in_array($old['configuration_key'], $deleted_configuration_keys)) continue;
        // add it tot he new configuration table
        $sql = "INSERT INTO " . $_POST['DB_DATABASE'] . ".configuration SET ";
        foreach ($old as $column => $value) {
          if ($column == 'configuration_id') continue;
          $sql .= $column . " = " . "'" . addslashes($value) . "', ";
        }
        $sql = substr($sql, 0, -2);
        if (mysql_query($sql, $db_link_new) === false) {
          $GLOBALS['_RESULT'] = array('code' => 'error',
                                      'action' => $_POST['function_call'],
                                      'msg' => sprintf($error_msg, mysql_errno($db_link_new), mysql_error($db_link_new), $sql));
          return;
        }
      }
      
      
      /*
      // now copy all the module_% setting over
      $sql = "SELECT * FROM " . $_POST['cre_existing_database'] . ".configuration WHERE configuration_key LIKE 'module\_%'";
      $loop_query = mysql_query($sql, $db_link_old);
      if (!$loop_query) {
        $GLOBALS['_RESULT'] = array('code' => 'error',
                                    'action' => $_POST['function_call'],
                                    'msg' => sprintf($error_msg, mysql_errno($db_link_old), mysql_error($db_link_old), $sql));
        return;
      }
      while ($old = mysql_fetch_assoc($loop_query)) {
        // add to the existing table data
        $sql = "INSERT INTO " . $_POST['DB_DATABASE'] . ".configuration SET ";
        foreach ($old as $column => $value) {
          if ($column == 'configuration_id') continue;
              $sql .= $column . "=" . "'" . addslashes($value) . "', ";
            }
            $sql .= 'configuration_id=0';
        if (!mysql_query($sql, $db_link_new)) {
          $GLOBALS['_RESULT'] = array('code' => 'error',
                                      'action' => $_POST['function_call'],
                                      'msg' => sprintf($error_msg, mysql_errno($db_link_new), mysql_error($db_link_new), $sql));
          return;
        }
      }
      
      // the orders_pay_methods is being added here
      $sql = "SELECT * FROM " . $_POST['cre_existing_database'] . ".orders_pay_methods";
      $data_query = mysql_query($sql, $db_link_old);
      if ($data_query === false) {
        $GLOBALS['_RESULT'] = array('code' => 'error',
                                    'action' => $_POST['function_call'],
                                    'msg' => sprintf($error_msg, mysql_errno($db_link_old), mysql_error($db_link_old), $sql));
        return;
      }
      while ($row = mysql_fetch_assoc($data_query)) {
        $sql = "INSERT INTO " . $_POST['DB_DATABASE'] . ".orders_pay_methods SET ";
        foreach ($row as $column => $value) {
          $sql .= $column . "=" . "'" . addslashes($value) . "', ";
        }
        $sql = substr($sql, 0, -2);
        if (mysql_query($sql, $db_link_new) === false) {
          $GLOBALS['_RESULT'] = array('code' => 'error',
                                      'action' => $_POST['function_call'],
                                      'msg' => sprintf($error_msg, mysql_errno($db_link_new), mysql_error($db_link_new), $sql));
          return;
        }
      }
      
      // the orders_ship_methods is being added here
      $sql = "SELECT * FROM " . $_POST['cre_existing_database'] . ".orders_ship_methods";
      $data_query = mysql_query($sql, $db_link_old);
      if ($data_query === false) {
        $GLOBALS['_RESULT'] = array('code' => 'error',
                                    'action' => $_POST['function_call'],
                                    'msg' => sprintf($error_msg, mysql_errno($db_link_old), mysql_error($db_link_old), $sql));
        return;
      }
      while ($row = mysql_fetch_assoc($data_query)) {
        $sql = "INSERT INTO " . $_POST['DB_DATABASE'] . ".orders_ship_methods SET ";
        foreach ($row as $column => $value) {
          $sql .= $column . "=" . "'" . addslashes($value) . "', ";
        }
        $sql = substr($sql, 0, -2);
        if (mysql_query($sql, $db_link_new) === false) {
          $GLOBALS['_RESULT'] = array('code' => 'error',
                                      'action' => $_POST['function_call'],
                                      'msg' => sprintf($error_msg, mysql_errno($db_link_new), mysql_error($db_link_new), $sql));
          return;
        }
      }
      */
      
      $GLOBALS['_RESULT'] = array('code' => 'success',
                                  'action' => $_POST['function_call'],
                                  'msg' => ''
                                 );
      return;
      break;
      
    default:
      $GLOBALS['_RESULT'] = array('code' => 'error',
                                  'action' => $_POST['function_call'],
                                  'msg' => DB_ERROR_01
                                 );
      return;
  }

  $GLOBALS['_RESULT'] = array('result' => 'error',
                              'msg' => 'We should not be here.'
                             );
?>
