/*************************************************************************
 *
 * Functions on the main screen 
 *
 *************************************************************************/

function save_query()
{
  var query = document.querySelector('#Query').value.replace(/^\s+|\s+$/g,''), db_type = parseInt(document.querySelector('input[name=db_type]:checked').value), form;

  if (query.length > 0 && db_type >= 0 && db_type < 6)
  {
    form = document.getElementById('cr_form');
    query = db_type + '|' + query;
    $.get(form.action, {save_query:query,sid:Math.random()}, query_saved);
  }

  return false;
}

function query_saved(response)
{
  if (response == 'saved')
  {
    $('#a_save').text(saved_text);
    window.setTimeout('reset_save()', 2000);
  }

  return false;
}

function reset_save()
{
  $('#a_save').text(save_text);
}

function clear_query()
{
  $('#Query').val('').focus();
  return false;
}

function load_query()
{
  var query=$('#Query'),results=$('#results_query'),sql=$(this).text();
  query.val(sql).focus();
  results.val(make_results_query(sql));
}

function execute_query()
{
  var query,f1=$('<form action="cr_freeform.cgi" method="get" target="_blank">'),hist,div_id,i;

  query = $('#Query').val();
  if (query.replace(/^\s+|\s+$/g,'').length == 0)
  {
    alert(no_query);
    return false;
  }

  //Use a form to let the browser do the encoding properly.
  f1.append($('<input type="hidden" name="Proceed.x" value="1">'));
  f1.append($('<input type="hidden" name="query">').val(query));
  f1.append($('<input type="hidden" name="form_parm1">').val($('#form_parm1').val()));
  f1.append($('<input type="hidden" name="db_type">').val($('[name=db_type]:checked').val()));
  $('body').append(f1);
  f1.submit().remove();

  hist = $('div#history');
  div_id = parseInt(hist.attr('data-idnum'));

  for (i = 1; i < div_id; i++)
  {
    if($('#query'+i).text() == query) break;
  }

  if (i == div_id)
  {
    //Save query to the history. Use jQuery for properly escapping HTML for text (avoid HTML injection)
    hist.append($('<div class="history_entry"></div>').append($('<span class="history_query"></span>').click(load_query).attr('id', 'query'+i).text(query)).append($('<span class="remove_button">X</span>').click(function(e){e.preventDefault();$(this).parent().remove();hist.attr('data-idnum', parseInt(hist.attr('data-idnum')) - 1);}))).attr('data-idnum', parseInt(i) + 1);
  }

  return false;
}

// Show or hide links depending on the selected db_type
function query_links(db_type)
{
  var qpl = $('.query_popup_link');
  if (parseInt(db_type) > 3) qpl.hide(); else qpl.show();

  var old_db_type = document.getElementById('db_type'); // the hidden field
  var new_db_type = document.querySelector('input[name=db_type]:checked').value;

  if (old_db_type.value != new_db_type)
  {
    var query = document.getElementById('Query');
    sqlQueries[old_db_type.value] = query.value;
    query.value = sqlQueries[new_db_type];
    old_db_type.value = new_db_type;
  }

  var new_type = sqlDBTypes[new_db_type];
  var new_name = sqlDBNames[new_db_type];

  var sub_head = document.getElementById('sub_head');
  sub_head.innerHTML = new_type + '&nbsp;:&nbsp;' + new_name;
}

function set_focus() // onload function
{
  var userid = document.getElementById('userid');
  var query = document.getElementById('Query');
  
  if (userid != null)
    userid.focus();
  else
  if (query != null)
    query.focus();
}


/*************************************************************************
 *
 * Popup window for saved queries
 *
 *************************************************************************/

function show_queries()
{
  var db_types = document.getElementsByName('db_type');
  var db_type = document.querySelector('input[name=db_type]:checked').value;
  var form, url;

  form = document.getElementById('cr_form');
  url = form.action + '?show_queries=' + db_type;

  window.open(url, 'cr_query', 'width=770,height=520,scrollbars=yes,resizable=yes');
  return false;
}

function get_queries()
{
  var databases = document.getElementById('sql_databases');
  var i = databases.selectedIndex;

  if (i >= 0)
  {
    var db_type = databases.options[i].value;
    var form = window.opener.document.getElementById('cr_form');
    var url = form.action + '?get_queries=' + db_type;

    $.get(url, {}, queries_received);
  }
}

function queries_received(response)
{
  var returned_queries = response.split('\n');
  var query, i;

  var queries = document.getElementById('queries');
  queries.options.length = 0;

  for (i = 0; i < returned_queries.length; i++)
  {
    query = returned_queries[i].split('|');
    queries.options[i] = new Option(query[1], query[0], false, false);
  }
}

// move query up or down in the list
function move_query(incr)
{
  var queries = document.getElementById('queries');
  var options, temp;
  var i, j, n;

  i = queries.selectedIndex;
  if (i >= 0)
  {
    j = i + incr;
    options = queries.options;
    n = options.length;

    if ((incr < 0 && j < 0) || (incr > 0 && j >= n))
      return false;

    text = options[j].text;
    value = options[j].value;
    options[j].text = options[i].text;
    options[j].value = options[i].value;
    options[i].text = text;
    options[i].value = value;

    options[i].selected = false;
    options[j].selected = true;
  }

  return false;
}

function delete_query()
{
  var queries = document.getElementById('queries');
  var i = queries.selectedIndex;

  if (i >= 0)
    queries[i] = null;

  return false;
}

// Paste the selected query into the 'Query' textarea
function paste_query()
{
  var wo_doc = window.opener.document;
  var results_query = wo_doc.getElementById('results_query');
  var db_type = wo_doc.getElementsByName('db_type');
  var query = wo_doc.getElementById('Query');
  var queries = document.getElementById('queries');
  var i, j;

  results_query.value = "";

  if (queries.options.length > 0)
  {
    if ((i = queries.selectedIndex) >= 0)
    {
      query.value = queries.options[i].text;

      // check the appropriate db_type radio button for this query
      j = queries.options[i].value;
      db_type[j].checked = true;

      // and also set the hidden db_type field
      db_type = wo_doc.getElementById('db_type');
      db_type.value = j;

      // make a query to show the results
      results_query.value = make_results_query(query.value);

      window.close();
    }
  }
}

function make_results_query(sql_query)
{
  var sql_verb, sql_table, sql_columns, sql_values;
  var select_query, where;
  var parts, i;

  parts = sql_query.split(' ');
  sql_verb = parts[0].toLowerCase();

  switch (sql_verb)
  {
    case 'select':
      select_query = '';
      break;

    case 'update':
      sql_table = parts[1];
      break;

    case 'insert':
      sql_table = parts[2];
      break;

    case 'delete':
      sql_table = parts[2];
      break;

    default:
      select_query = '';
      break;
  }

  if (sql_verb == 'update')
  {
    // find column names in name=value pairs
    sql_columns = sql_query.match(/\w+\s*=/g);

    if (sql_columns != null)
    {
      select_query = 'SELECT ';

      for (i = 0; i < sql_columns.length; i++)
      {
        parts = sql_columns[i].split('=');
        select_query += i > 0? ', ' : '';
        select_query += parts[0].replace(/\s+/g, '');
      }
    }

    select_query += ' FROM ' + sql_table;

    // include where clause if any
    where = sql_query.match(/where.+$/i);

    if (where != null)
      select_query += ' ' + where[0];
  }
  else
  if (sql_verb == 'insert')
  {
    // find column names and values enclosed in parentheses
    sql_columns = sql_query.match(/\(.+\)\s*values/i);
    sql_values = sql_query.match(/values\s*\(.+\)/i);

    if (sql_columns != null)
    {
      sql_columns = sql_columns[0].replace(/\s*values\s*/i, '');
      sql_columns = sql_columns.replace(/[()]/g, '');
      sql_columns = sql_columns.split(',');

      if (sql_columns.length > 0)
      {
        select_query = 'SELECT ';

        for (i = 0; i < sql_columns.length; i++)
        {
          select_query += i > 0? ', ' : '';
          select_query += sql_columns[i].replace(/"/g, '');
        }
      }
      
      select_query += ' FROM ' + sql_table;

      if (sql_values != null)
      {
        sql_values = sql_values[0].replace(/\s*values\s*/i, '');
        sql_values = sql_values.replace(/[()]/g, '');
        sql_values = sql_values.split(',');
      
        if (sql_values.length > 0)
        {
          select_query += ' WHERE ' + sql_columns[0] + ' = ';
          select_query += sql_values[0];
        }
      }
    }
    else
      select_query = 'SELECT * FROM ' + sql_table;
  }
  else
  if (sql_verb == 'delete')
  {
    select_query = 'SELECT * FROM ' + sql_table;

    // include where clause if any
    where = sql_query.match(/where.+$/i);

    if (where != null)
      select_query += ' ' + where[0];
  }

  return select_query;
}

function save_changes(button)
{
  var queries = document.getElementById('queries');
  var num = queries.options.length;
  var all_values = '';
  var form, url;

  for (i = 0; i < num; i++)
  {
    all_values += '[' + queries.options[i].value;
    all_values += '|' + queries.options[i].text;
    all_values += ']';
  }

  if (all_values.length == 0)
    all_values = 'none';

  form = window.opener.document.getElementById('cr_form');
  url = form.action;
  url += '?save_queries=' + encodeURIComponent(all_values);
  url += '&sid=' + Math.random();

  $.get(url, {}, changes_saved);

  button.value = saved_text;
  return false;
}

function changes_saved(response)
{
  if (response == "deleted")
  {
    // nothing for now
  }
}

function expand(incr)
{
  var box = document.getElementById('queries');
  var width = box.style.width;

  width = width.split('p', 1);
  width = Math.max(Number(width) + incr, 600);
  box.style.width = width + 'px';
  window.resizeBy(incr + 10, 0);
}


/*************************************************************************
 *
 * Popup windows to create a new query or to edit the current query
 *
 *************************************************************************/

function select_query_popup()
{
  var query = document.getElementById('Query').value;
  var format = document.getElementById('fields_format').value;
  var db_type = document.querySelector('input[name=db_type]:checked').value;
  var form, url;

  form = document.getElementById('cr_form');
  url = form.action + '?select_query=';

  if (query.length > 0)
  {
    // check if wanting to modify an existing query or make a new one
    if (query.search(/select /i) >= 0)
      query = encodeURIComponent(query);
    else
      query = '';
  }

  url += query.length > 0? query : 'new';
  url += '&format=' + format;
  url += '&db_type=' + db_type;

  window.open(url, 'cr_query', 'width=920,height=780,scrollbars=yes,resizable=yes');
  return false;
}

function update_query_popup()
{
  var query = document.getElementById('Query').value;
  var format = document.getElementById('fields_format').value;
  var db_type = document.querySelector('input[name=db_type]:checked').value;
  var form, url;

  form = document.getElementById('cr_form');
  url = form.action + '?update_query=';

  if (query.length > 0)
  {
    // check if wanting to modify an existing query or make a new one
    if (query.search(/update /i) >= 0)
      query = encodeURIComponent(query);
    else
      query = '';
  }

  url += query.length > 0? query : 'new';
  url += '&format=' + format;
  url += '&db_type=' + db_type;

  window.open(url, 'cr_query', 'width=920,height=780,scrollbars=yes,resizable=yes');
  return false;
}

function insert_query_popup()
{
  var query = document.getElementById('Query').value;
  var format = document.getElementById('fields_format').value;
  var db_type = document.querySelector('input[name=db_type]:checked').value;
  var form, url;

  form = document.getElementById('cr_form');
  url = form.action + '?insert_query=';

  if (query.length > 0)
  {
    // check if wanting to modify an existing query or make a new one
    if (query.search(/insert /i) >= 0)
      query = encodeURIComponent(query);
    else
      query = '';
  }

  url += query.length > 0? query : 'new';
  url += '&format=' + format;
  url += '&db_type=' + db_type;

  window.open(url, 'cr_query', 'width=920,height=680,scrollbars=yes,resizable=yes');
  return false;
}

function delete_query_popup()
{
  var query = document.getElementById('Query').value;
  var format = document.getElementById('fields_format').value;
  var db_type = document.querySelector('input[name=db_type]:checked').value;
  var form, url;

  form = document.getElementById('cr_form');
  url = form.action + '?delete_query=';

  if (query.length > 0)
  {
    // check if wanting to modify an existing query or make a new one
    if (query.search(/delete /i) >= 0)
      query = encodeURIComponent(query);
    else
      query = '';
  }

  url += query.length > 0? query : 'new';
  url += '&format=' + format;
  url += '&db_type=' + db_type;

  window.open(url, 'cr_query', 'width=920,height=720,scrollbars=yes,resizable=yes');
  return false;
}

function file_name(path_name)
{
  var normalized = path_name.replace(/\\/g, '/');
  var index = normalized.lastIndexOf('/');

  if (index >= 0)
    return path_name.substr(index + 1);

  return path_name;
}

function clear_where()
{
  var where = document.getElementById('where');
  where.value = '';
  where.focus();
}

function return_query()
{
  var done;

  switch (query_type)
  {
    case 1:
      done = return_select_query();
      break;

    case 2:
      done = return_update_query();
      break;

    case 3:
      done = return_insert_query();
      break;

    case 4:
      done = return_delete_query();
      break;
  }

  if (done)
    window.close();
  return false;
}

function return_select_query()
{
  var wo_doc = window.opener.document;
  var default_table = wo_doc.getElementById('sql_table');
  var fields_format = wo_doc.getElementById('fields_format');
  var results_query = wo_doc.getElementById('results_query');
  var select_query = wo_doc.getElementById('Query');

  var sql_tables = document.getElementById('sql_tables');
  var sql_fields = document.getElementById('sql_fields');
  var order_by = document.getElementById('order_by');
  var asc_desc = document.getElementById('asc_desc');
  var where = document.getElementById('where');

  var sql_table, sql_names, sql;
  var i;

  // in case the user has forgotten to click Add...
  add_expression();

  if ((i = sql_tables.selectedIndex) >= 0)
    sql_table = sql_tables.options[i].value;
  else
    sql_table = '';

  sql_names = sql_fields.options;

  for (i = 0, sql = ''; i < sql_names.length; i++)
  {
    if (i == 0)
      sql = 'SELECT ' + sql_names[i].value;
    else
      sql += ', ' + sql_names[i].value;
  }

  if (sql_table.length > 0)
  {
    if (sql.length == 0)
      sql = 'SELECT *';

    sql += ' FROM ' + sql_table;

    if (where.value.length > 0)
      sql += ' WHERE (' + where.value + ')';

    if (order_by.value.length > 0)
    {
      sql += ' ORDER BY ' + order_by.value;
      
      if ((i = asc_desc.selectedIndex) > 0)
        sql += ' ' + asc_desc.options[i].value;
    }

    select_query.value = sql;
    results_query.value = '';

    default_table.value = sql_table;
    fields_format.value = db_fields_format;
  }
  
  return true;
}

function return_update_query()
{
  var wo_doc = window.opener.document;
  var default_table = wo_doc.getElementById('sql_table');
  var fields_format = wo_doc.getElementById('fields_format');
  var results_query = wo_doc.getElementById('results_query');
  var update_query = wo_doc.getElementById('Query');

  var update_values = document.getElementById('update_values');
  var sql_tables = document.getElementById('sql_tables');
  var sql_fields = document.getElementById('sql_fields');
  var where = document.getElementById('where');

  var sql_names, sql_values, sqlName, sqlValue;
  var sql_table, sql = '';
  var update_columns = '';
  var separator = ', ';
  var quote = '"';
  var i, j;

  if (sql_fields.options.length == 0)
  {
    alert(no_update_columns);
    return false;
  }
  
  // in case the user has forgotten to click Add...
  add_expression();

  if ((i = sql_tables.selectedIndex) >= 0)
    sql_table = sql_tables.options[i].value;
  else
    sql_table = '';

  sql_names = sql_fields.options;
  sql_values = update_values.options;

  for (i = 0; i < sql_names.length; i++)
  {
    if (sql.length > 0)
    {
      sql += separator;
      update_columns += separator;
    }

    sqlName = sql_names[i].value;
    sqlValue = '';

    for (j = 0; j < sql_values.length; j++)
    {
      if (sql_values[j].text == sqlName)
      {
        sqlValue = sql_values[j].value;
        break;
      }
    }

    sql += sqlName + '="' + sqlValue + quote;
    update_columns += sqlName;
  }

  if (sql_table.length > 0)
  {
    if (sql.length > 0)
    {
      if (where.value.length == 0)
      {
        if (confirm(no_update_where) == false)
        {
          var fields = document.getElementById('where_fields');
          fields.selectedIndex = 0;
          return false;
        }
      }

      sql = 'UPDATE ' + sql_table + ' SET ' + sql;

      if (where.value.length > 0)
        sql += ' WHERE (' + where.value + ')';

      update_query.value = sql;

      // query to return the results
      sql = 'SELECT ' + update_columns + ' FROM ' + sql_table;

      if (where.value.length > 0)
        sql += ' WHERE (' + where.value + ')';

      results_query.value = sql;

      default_table.value = sql_table;
      fields_format.value = db_fields_format;
    }
  }

  return true;
}

function return_insert_query()
{
  var wo_doc = window.opener.document;
  var default_table = wo_doc.getElementById('sql_table');
  var fields_format = wo_doc.getElementById('fields_format');
  var results_query = wo_doc.getElementById('results_query');
  var insert_query = wo_doc.getElementById('Query');

  var insert_values = document.getElementById('insert_values');
  var sql_tables = document.getElementById('sql_tables');
  var sql_fields = document.getElementById('sql_fields');
  var sql_names, sql_values, sqlName, sqlValue;
  var pages_products = 'ss_Pages ss_Products';

  var column_names = '';
  var column_values = '';
  var insert_columns = '';
  var sql_table, sql = '';
  var separator = ', ';
  var quote = '"';
  var i, j;

  if ((i = sql_tables.selectedIndex) >= 0)
    sql_table = sql_tables.options[i].value;
  else
    sql_table = '';

  sql_names = sql_fields.options;
  sql_values = insert_values.options;

  for (i = 0; i < sql_names.length; i++)
  {
    if (sql.length > 0)
    {
      sql += separator;
      insert_columns += separator;
    }

    sqlName = sql_names[i].value;
    sqlValue = '';

    for (j = 0; j < sql_values.length; j++)
    {
      if (sql_values[j].text == sqlName)
      {
        sqlValue = sql_values[j].value;
         break;
      }
    }

    if (sqlValue.length > 0)
    {
      if (column_names.length > 0)
      {
        column_names += separator;
        column_values += separator;
        insert_columns += separator;
      }

      column_names += sqlName;
      column_values += quote + sqlValue + quote;
      insert_columns += sqlName;
    }
  }

  if (pages_products.indexOf(sql_table) >= 0)
  {
    column_names += separator;
    column_values += separator;

    column_names += 'ss_Status';
    column_values += quote + '1' + quote;
  }

  if (sql_table.length > 0)
  {
    sql = 'INSERT INTO ' + sql_table + ' (' + column_names + ') ';
    sql += 'VALUES ' + '(' + column_values + ')'; 

    insert_query.value = sql;

    // query to return the results
    sql = 'SELECT ' + insert_columns + ' FROM ' + sql_table;
    sql += ' WHERE rowid = %d';    
    results_query.value = sql;

    default_table.value = sql_table;
    fields_format.value = db_fields_format;
  }

  return true;
}

function return_delete_query()
{
  var wo_doc = window.opener.document;
  var default_table = wo_doc.getElementById('sql_table');
  var fields_format = wo_doc.getElementById('fields_format');
  var results_query = wo_doc.getElementById('results_query');
  var delete_query = wo_doc.getElementById('Query');
  var sql_tables = document.getElementById('sql_tables');

  var where = document.getElementById('where');
  var sql_table, sql;
  var i;
  
  // in case the user has forgotten to click Add...
  add_expression();

  if ((i = sql_tables.selectedIndex) >= 0)
    sql_table = sql_tables.options[i].value;
  else
    sql_table = '';

  if (sql_table.length > 0)
  {
    sql = 'DELETE FROM ' + sql_table;

    if (where.value.length == 0)
    {
      if (confirm(no_delete_where) == false)
      {
        var fields = document.getElementById('where_fields');
        fields.selectedIndex = 0;
        return false;
      }
    }

    if (where.value.length > 0)
      sql += ' WHERE (' + where.value + ')';

    delete_query.value = sql;

    // query to return the results
    sql = 'SELECT * FROM ' + sql_table;

    if (where.value.length > 0)
      sql += ' WHERE (' + where.value + ')';

    results_query.value = sql;

    default_table.value = sql_table;
    fields_format.value = db_fields_format;
  }

  return true;
}

function add_fields()
{
  var db_fields = document.getElementById('db_fields');
  var sql_fields = document.getElementById('sql_fields');

  var update_value = document.getElementById('update_value');
  var update_values = document.getElementById('update_values');

  var insert_value = document.getElementById('insert_value');
  var insert_values = document.getElementById('insert_values');

  var from = db_fields.options;
  var to = sql_fields.options;
  var i, j, k;

  for (i = 0, j = to.length; i < from.length; i++)
  {
    if (from[i].selected == true)
    {
      var sql_name = from[i].value;

      if (sql_name.length > 0)
      {
        k = j;
        to[j++] = new Option('', '', false, false);
        to[k].text = sql_name;
        to[k].value = sql_name;
      }

      from[i].selected = false;
    }
  }

  if (update_values != null)
    refresh_update_values(sql_fields, update_values);

  if (insert_values != null)
    refresh_insert_values(sql_fields, insert_values);

  if (update_value != null)
    update_value.focus();

  if (insert_value != null)
    insert_value.focus();
}

function remove_fields()
{
  var sql_fields = document.getElementById('sql_fields');
  var update_values = document.getElementById('update_values');
  var insert_values = document.getElementById('insert_values');
  var fields = sql_fields.options;
  var i;

  for (i = fields.length - 1; i >= 0; i--)
  {
    if (fields[i].selected == true)
      fields[i] = null;
  }
  
  if (update_values != null)
    refresh_update_values(sql_fields, update_values);
  
  if (insert_values != null)
    refresh_insert_values(sql_fields, insert_values);
}

function refresh_update_values(sql_fields, update_values)
{
  var columns = sql_fields.options;
  var values = update_values.options;
  var column_name, value_name;
  var i, j, count;

  if (values.length > 0)
  {
    // first remove values for the update columns that have been removed
    for (i = values.length - 1, count = columns.length; i >= 0; i--)
    {
      value_name = values[i].text;

      for (j = 0; j < count; j++)
      {
        column_name = columns[j].text;
        if (column_name == value_name)
          break;
      }

      if (j == count)
      {
        if (values[i].selected)
          update_values.selectedIndex = -1;

        values[i] = null;
      }
    }
  }
  
  // now add values for update columns that have been added
  for (i = 0, count = values.length, k = count; i < columns.length; i++)
  {
    column_name = columns[i].text;

    for (j = 0; j < count; j++)
    {
      value_name = values[j].text;
      if (value_name == column_name)
        break;
    }

    if (j == count)
      values[k++] = new Option(column_name, '', false, false);
  }

  if (update_values.length > 0 && update_values.selectedIndex < 0)
    update_values.selectedIndex = 0;

  show_update_value();
}

function refresh_insert_values(sql_fields, insert_values)
{
  var columns = sql_fields.options;
  var values = insert_values.options;
  var column_name, value_name;
  var i, j, count;

  if (values.length > 0)
  {
    // first remove values for the insert columns that have been removed
    for (i = values.length - 1, count = columns.length; i >= 0; i--)
    {
      value_name = values[i].text;

      for (j = 0; j < count; j++)
      {
        column_name = columns[j].text;
        if (column_name == value_name)
          break;
      }

      if (j == count)
      {
        if (values[i].selected)
          insert_values.selectedIndex = -1;

        values[i] = null;
      }
    }
  }
  
  // now add values for insert columns that have been added
  for (i = 0, count = values.length, k = count; i < columns.length; i++)
  {
    column_name = columns[i].text;

    for (j = 0; j < count; j++)
    {
      value_name = values[j].text;
      if (value_name == column_name)
        break;
    }

    if (j == count)
      values[k++] = new Option(column_name, '', false, false);
  }

  if (insert_values.length > 0 && insert_values.selectedIndex < 0)
    insert_values.selectedIndex = 0;

  show_insert_value();
}

function set_update_value(new_value)
{
  var update_values = document.getElementById('update_values');
  var i = update_values.selectedIndex;

  if (i >= 0)
    update_values.options[i].value = new_value;
}

function show_update_value()
{
  var update_values = document.getElementById('update_values');
  var update_value = document.getElementById('update_value');
  var i = update_values.selectedIndex;

  if (i >= 0)
    update_value.value = update_values.options[i].value;
  else
    update_value.value = "";
}

function next_update_value()
{
  var update_values = document.getElementById('update_values');
  var update_value = document.getElementById('update_value');
  var i = update_values.selectedIndex;

  update_values.selectedIndex += 1;
  i = update_values.selectedIndex;

  if (i >= 0)
    update_value.value = update_values.options[i].value;
  else
    update_value.value = "";

  update_value.focus();
}

function set_insert_value(new_value)
{
  var insert_values = document.getElementById('insert_values');
  var i = insert_values.selectedIndex;

  if (i >= 0)
    insert_values.options[i].value = new_value;
}

function show_insert_value()
{
  var insert_values = document.getElementById('insert_values');
  var insert_value = document.getElementById('insert_value');
  var i = insert_values.selectedIndex;

  if (i >= 0)
    insert_value.value = insert_values.options[i].value;
  else
    insert_value.value = "";
}

function next_insert_value()
{
  var insert_values = document.getElementById('insert_values');
  var insert_value = document.getElementById('insert_value');
  var i = insert_values.selectedIndex;

  insert_values.selectedIndex += 1;
  i = insert_values.selectedIndex;

  if (i >= 0)
    insert_value.value = insert_values.options[i].value;
  else
    insert_value.value = "";

  insert_value.focus();
}

function move_field(incr)
{
  var fields = document.getElementById('sql_fields');
  var options = fields.options;
  var text, value;
  var i, j, n;

  i = fields.selectedIndex;
  if (i >= 0)
  {
    j = i + incr;
    n = options.length;

    if ((incr < 0 && j < 0) || (incr > 0 && j >= n))
      return false;

    text = options[j].text;
    value = options[j].value;
    options[j].text = options[i].text;
    options[j].value = options[i].value;
    options[i].text = text;
    options[i].value = value;

    options[i].selected = false;
    options[j].selected = true;
  }

  return false;
}

function list_tables(response)
{
  var sql_tables = document.getElementById('sql_tables');
  var tables, table, default_table;
  var name, i;

  if (response.search(/invalid/i) >= 0)
  {
    alert(response);
    return;
  }

  if (response.length == 0)
  {
    sql_tables.options[0] = new Option(no_tables, '', false, true);
    return;
  }

  tables = response.split(',');
  default_table = tables.shift();

  for (i = 0; i < tables.length; i++)
  {
    name = tables[i];

    if (name == 'Products')
      table = 'ss_Products';

    else if (name == 'Pages')
      table = 'ss_Pages';

    else
      table = name;

    sql_tables.options[i] = new Option(name, table, false, false);

    if (name == default_table)
      sql_tables.selectedIndex = i;
  }

  clear_all_fields();
  get_fields();
}

function list_fields()
{
  clear_all_fields();
  get_fields();
}

function change_format()
{
  var format_options = document.getElementById('format_options');
  var i = format_options.selectedIndex;

  db_fields_format = format_options[i].value;

  clear_db_fields();
  get_fields();
}

function get_fields()
{
  var format_options = document.getElementById('format_options');
  var sql_tables = document.getElementById('sql_tables');

  if ((i = sql_tables.selectedIndex) >= 0)
  {
    var readystate_function;
    var sql_table, form, url;
    var pages_products = 'ss_Pages ss_Products';

    form = window.opener.document.getElementById('cr_form');
    sql_table = encodeURIComponent(sql_tables.options[i].value);

    if (pages_products.indexOf(sql_table) >= 0)
    {
      readystate_function = db_fields_format == 0? list_page_product_fields :
        list_sql_field_names;

      format_options.style.display = ''
    }
    else
    {
      readystate_function = list_sql_field_names;
      format_options.style.display = 'none'
    }

    url = form.action + '?get_fields=' + sql_table;
    url += db_fields_format == 1? '&sql_names=1' : '';
    url += '&db_type=' + db_type;
    url += '&sid=' + Math.random();

    $.get(url, {}, readystate_function);
  }
}

function list_page_product_fields(response)
{
  if (response.length > 0)
  {
    var db_fields = document.getElementById('db_fields');
    var section, current_section = -1;
    var fields = response.split(/['\r\n']+/);
    var fldName, sqlName;
    var db_type = 0;
    var parts, count;
    var i, j;

    if (fields.length > 0)
    {
      parts = fields[0].split(';');
      fields.shift();

      db_type = Number(parts[0]);  // pages or products
      count = Number(parts[1]);
    }

    if (db_type > 0)
    {
      for (i = 0, j = db_type - 1; i < count; i++)
      {
        parts = fields[i].split(';');
        section = Number(parts[0]);
        fldName = parts[1];
        sqlName = parts[2];

        db_fields.options[i] = new Option(fldName, sqlName, false, false);

        if (section >= 0)
        {
          if (section != current_section)
          {
            var section_name = section_names[j][section];
            add_optgroup(db_fields, section_name, i);

            current_section = section;
          }
        }
      }
    }
  }
}

function list_sql_field_names(response)
{
  if (response.length > 0)
  {
    var db_fields = document.getElementById('db_fields');
    var columns = response.split(',');
    var column, name_only, i;

    for (i = 0; i < columns.length; i++)
    {
      column = columns[i];
      name_only = column.split(' ');

      db_fields.options[i] = new Option(column, name_only[0], false, false);
    }
  }
}

function add_where_fields()
{
  var db_fields = document.getElementById('db_fields');
  var sql_fields = document.getElementById('sql_fields');
  var to_fields = document.getElementById('where_fields');

  if (db_fields.selectedIndex >= 0 || sql_fields.selectedIndex >= 0)
  {
    var field_value = document.getElementById('field_value');
    var from_list, from_fields, sql_name;
    var i, j = 0;

    to_fields.options.length = 0;

    for (from_list = 0; from_list < 2; from_list++)
    {
      switch (from_list)
      {
        case 0:
          from_fields = db_fields.options;
          break;

        case 1:
          from_fields = sql_fields.options;
          break;
      }

      for (i = 0; i < from_fields.length; i++)
      {
        if (from_fields[i].selected)
        {
          sql_name = from_fields[i].value;
          to_fields.options[j++] = new Option(sql_name, sql_name, false, false);
          from_fields[i].selected = false;
        }
      }

      to_fields.selectedIndex = 0;
      field_value.value = '';
      field_value.focus();
    }
  }
}

function add_logical_operator(operators)
{
  var i = operators.selectedIndex;
  
  if (i > 0)
  {
    var where = document.getElementById('where');
    where.value += operators.options[i].value + ' ';
  }
}

function add_expression()
{
  var where = document.getElementById('where');
  var fields = document.getElementById('where_fields');
  var field_value = document.getElementById('field_value');
  var binary_operator = document.getElementById('binary_operator');
  var logical_operator = document.getElementById('logical_operator');

  var type = document.getElementById('type');
  var quote = type.selectedIndex == 0? '"' : '';

  var i = fields.selectedIndex;
  var j = binary_operator.selectedIndex;
  var k = logical_operator.selectedIndex;

  if (i >= 0 && field_value.value.length > 0)
  {
    var expression = fields.options[i].value;
    expression += binary_operator.options[j].value;
    expression += quote + field_value.value + quote;

    if (where.value.length > 0)
      where.value += ' ' + logical_operator.options[k].value + ' ';

    where.value += expression;
  }

  fields.selectedIndex += 1;
  field_value.value = '';
  field_value.focus();
}

function orderby_field(field)
{
  var db_fields = document.getElementById('db_fields');
  var sql_fields = document.getElementById('sql_fields');
  var from_list;

  for (from_list = 0; from_list < 2; from_list++)
  {
    switch (from_list)
    {
      case 0:
        fields = db_fields.options;
        break;

      case 1:
        fields = sql_fields.options;
        break;
    }

    for (i = 0; i < fields.length; i++)
    {
      if (fields[i].selected)
      {
        field.value = fields[i].value;
        fields[i].selected = false;
        return;
      }
    }
  }
}

function add_optgroup(db_fields, section_name, index)
{
  var options = db_fields.options;
  var optgroup = document.createElement("optgroup");

  optgroup.setAttribute("label", section_name);
  optgroup.appendChild(options[index]);
  db_fields.insertBefore(optgroup, options[index]);
}

function clear_all_fields()
{
  var sql_fields = document.getElementById('sql_fields');
  var order_by = document.getElementById('order_by');
  var asc_desc = document.getElementById('asc_desc');
  var filter = document.getElementById('where');

  if (filter != null)
    filter.value = '';

  if (order_by != null)
    order_by.value = '';
  
  if (asc_desc != null)
    asc_desc.selectedIndex = -1;

  sql_fields.options.length = 0;

  clear_db_fields();
}

function clear_db_fields()
{
  var db_fields = document.getElementById('db_fields');
  db_fields.options.length = 0;
  remove_optgroups(db_fields);
}

function remove_optgroups(db_fields)
{
  if (db_fields.hasChildNodes())
  {
    var child = db_fields.firstChild;

    while (child != null)
    {
      var sibling = child.nextSibling;
      
      if (child.nodeName == 'OPTGROUP')
        child.parentNode.removeChild(child);

      child = sibling;
    }
  }
}


/*************************************************************************
 *
 * Code no longer used but keep here for possible future use
 *
 *************************************************************************/

/*

This function removes fields from one select list and adds
them to the end of another select list.
 
function move_selected_fields(from_fields, to_fields)
{
  var from = from_fields.options;
  var to = to_fields.options;
  var i, j, k, n;

  n = from.length;
  j = to.length;

  for (i = 0; i < n; i++)
  {
    if (from[i].selected == true)
    {
      if (from[i].text.length > 0)
      {
        k = j;
        to[j++] = new Option('', '', false, false);

        to[k].text = from[i].text;
        to[k].value = from[i].value;
      }
    }
  }

  for (i = n - 1; i >= 0; i--)
  {
    if (from[i].selected == true)
    {
      if (from[i].text.length > 0)
        from[i] = null;
    }
  }
}

This code creates an optgroup at the end of a select list and
adds a field to it.

  i = fields.options.length;
  fields.options[i] = new Option(text, value, false, false);
  add_optgroup(fields, optgroup_name, i);


This code creates an optgroup at the end of a select list after which
fields removed from another select list can be added after it.

  if (removed_fields == false)
  {
    var i = db_fields.options.length;
    var j = i + 1;

    db_fields.options[i] = new Option('', '', false, false);
    db_fields.options[j] = new Option('', '', false, false);

    add_optgroup(db_fields, removed_fields_label, j);
    db_fields.options[j] = null;
    removed_fields = true;
  }

  move_selected_fields(from, to);

*/