var _addCountry = mywindow.findChild("_addCountry");
var _edit       = mywindow.findChild("_edit");
var _fixAbbr    = mywindow.findChild("_fixAbbr");
var _fixBulk    = mywindow.findChild("_fixBulk");
var _fixCase    = mywindow.findChild("_fixCase");
var _list       = mywindow.findChild("_list");
var _query      = mywindow.findChild("_query");
var _showAll    = mywindow.findChild("_showAll");

_list.addColumn("Record Type", -1, Qt.AlignLeft, true, "type");
_list.addColumn("Number",      -1, Qt.AlignLeft, true, "number");
_list.addColumn("Country",     -1, Qt.AlignLeft, true, "country");
_list.addColumn("Details",     -1, Qt.AlignLeft, true, "detail");

function sAddCountry()
{
  try {
  var params = new Object;;
  params.mode = "new";

  var newdlg = toolbox.openWindow("country", mywindow, Qt.Dialog, Qt.WindowModal);
  toolbox.lastWindow().set(params);
  newdlg.exec();
  } catch (e) { print("sAddCountry exception: " + e); }
}

function openEditorWindow(pwindow, pidname, pparent)
{
  try {
  var params = new Object;
  params[pidname] = _list.id();
  if (pidname == 'quhead_id')
    params.mode = "editQuote";
  else
    params.mode = "edit";

  var newdlg = toolbox.openWindow(pwindow, pparent, Qt.Dialog, Qt.WindowModal);
  toolbox.lastWindow().set(params);
  newdlg.exec();
  } catch(e) { print("openEditorWindow exception (" + e.lineNumber + "): " + e); }
}

function sEdit()
{
  try {
  var currentItem = _list.currentItem();

  switch (currentItem.text(0))
  {
    case 'Address':
      openEditorWindow("address", "addr_id", mywindow);
      break;

    case 'Credit Memo':
      openEditorWindow("creditMemo", "cmhead_id", 0);
      break;

    case 'Invoice':
      openEditorWindow("invoice", "invchead_id", mywindow);
      break;

    case 'Quote':
      openEditorWindow("salesOrder", "quhead_id", 0);
      break;

    case 'Sales Order':
      openEditorWindow("salesOrder", "sohead_id", 0);
      break;
  }
  sFillList();
  } catch (e) { print("sEdit() exception: " + e); }
}

function sFixBulk()
{
  print("sFixBulk() entered");
  try {
    var params = new Object;
    if (_showAll.checked)
      params.showAll = true;

    var newdlg = toolbox.openWindow("bulkFixCountry", mywindow,
                                    Qt.WindowModal, Qt.Dialog);
    toolbox.lastWindow().set(params);

    sFillList();
  } catch (e) { print("sFixBulk() exception: " + e); }
}

function sFixCase()
{
  try {
  if (QMessageBox.question(mywindow, qsTr("Are you sure?"),
                           qsTr("<p>Are you sure that you want to change all "
                              + "countries that appear to be correct except "
                              + "for upper- and lower-case differences in a "
                              + "batch?<p>(This change will obey the status "
                              + "of the 'Show closed records' check box."),
                           QMessageBox.Yes, QMessageBox.No) == QMessageBox.No)
    return;

  var params = new Object;
  if (_showAll.checked)
    params.showAll = true;

  var qry = toolbox.executeQuery(
      "UPDATE addr SET addr_country = country_name"
    + "  FROM country "
    + " WHERE addr_country != ''"
    + "   AND UPPER(addr_country) = UPPER(country_name)"
    + "   AND addr_country != country_name;", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Address: " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE cmhead SET cmhead_billtocountry = country_name"
    + "  FROM country "
    + " WHERE cmhead_billtocountry != ''"
    + "   AND UPPER(cmhead_billtocountry) = UPPER(country_name)"
    + "   AND cmhead_billtocountry != country_name"
    + '  <? if not exists("showAll") ?>'
    + "   AND NOT cmhead_posted"
    + "  <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Credit Memo (Bill-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE cmhead SET cmhead_shipto_country = country_name"
    + "  FROM country "
    + " WHERE cmhead_shipto_country != ''"
    + "   AND UPPER(cmhead_shipto_country) = UPPER(country_name)"
    + "   AND cmhead_shipto_country != country_name"
    + '  <? if not exists("showAll") ?>'
    + "   AND NOT cmhead_posted"
    + "  <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Credit Memo (Ship-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE invchead SET invchead_billto_country = country_name"
    + "  FROM country "
    + " WHERE invchead_billto_country != ''"
    + "   AND UPPER(invchead_billto_country) = UPPER(country_name)"
    + "   AND invchead_billto_country != country_name"
    + '  <? if not exists("showAll") ?>'
    + "   AND NOT invchead_posted"
    + "  <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Invoice (Bill-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE invchead SET invchead_shipto_country = country_name"
    + "  FROM country "
    + " WHERE invchead_shipto_country != ''"
    + "   AND UPPER(invchead_shipto_country) = UPPER(country_name)"
    + "   AND invchead_shipto_country != country_name"
    + '  <? if not exists("showAll") ?>'
    + "   AND NOT invchead_posted"
    + "  <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Invoice (Ship-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE quhead SET quhead_billtocountry = country_name"
    + "  FROM country"
    + " WHERE quhead_billtocountry != ''"
    + "   AND UPPER(quhead_billtocountry) = UPPER(country_name)"
    + "   AND quhead_billtocountry != country_name"
    + '  <? if not exists("showAll") ?>'
    + "   AND quhead_expire >= CURRENT_DATE"
    + "  <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Quote (Bill-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE quhead SET quhead_shiptocountry = country_name"
    + "  FROM country"
    + " WHERE quhead_shiptocountry != ''"
    + "   AND UPPER(quhead_shiptocountry) = UPPER(country_name)"
    + "   AND quhead_shiptocountry != country_name"
    + '  <? if not exists("showAll") ?>'
    + "   AND quhead_expire >= CURRENT_DATE"
    + "  <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Quote (Ship-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE cohead SET cohead_billtocountry = country_name"
    + '  FROM country <? if not exists("showAll") ?>, coitem<? endif ?>'
    + " WHERE cohead_billtocountry != ''"
    + "   AND UPPER(cohead_billtocountry) = UPPER(country_name)"
    + "   AND cohead_billtocountry != country_name"
    + '   <? if not exists("showAll") ?>'
    + "   AND cohead_id=coitem_cohead_id AND COALESCE(coitem_status, 'O')='O'"
    + "   <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Sales Order (Bill-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE cohead SET cohead_shiptocountry = country_name"
    + '  FROM country <? if not exists("showAll") ?>, coitem<? endif ?>'
    + " WHERE cohead_shiptocountry != ''"
    + "   AND UPPER(cohead_shiptocountry) = UPPER(country_name)"
    + "   AND cohead_shiptocountry != country_name"
    + '   <? if not exists("showAll") ?>'
    + "   AND cohead_id=coitem_cohead_id AND COALESCE(coitem_status, 'O')='O'"
    + "   <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Sales Order (Ship-To): " + qry.lastError().text);

  } catch (e) { QMessageBox.critical(mywindow, "sFixCase() exception", e); }

  sFillList();
}

function sFixAbbr()
{
  try {
  if (QMessageBox.question(mywindow, qsTr("Are you sure?"),
                           qsTr("<p>Are you sure that you want to replace all "
                              + "country abbreviations to the corresponding "
                              + "full country names?<p>(This change will obey "
                              + "the status of the 'Show closed records' "
                              + "check box."),
                       QMessageBox.Yes, QMessageBox.No) == QMessageBox.No)
    return;

  var params = new Object;
  if (_showAll.checked)
    params.showAll = true;

  var qry = toolbox.executeQuery(
      "UPDATE addr SET addr_country = country_name"
    + "  FROM country "
    + " WHERE addr_country != ''"
    + "   AND UPPER(addr_country) = UPPER(country_abbr);", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Address: " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE cmhead SET cmhead_billtocountry = country_name"
    + "  FROM country "
    + " WHERE cmhead_billtocountry != ''"
    + "   AND UPPER(cmhead_billtocountry) = UPPER(country_abbr)"
    + '  <? if not exists("showAll") ?>'
    + "   AND NOT cmhead_posted"
    + "  <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Credit Memo (Bill-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE cmhead SET cmhead_shipto_country = country_name"
    + "  FROM country "
    + " WHERE cmhead_shipto_country != ''"
    + "   AND UPPER(cmhead_shipto_country) = UPPER(country_abbr)"
    + '  <? if not exists("showAll") ?>'
    + "   AND NOT cmhead_posted"
    + "  <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Credit Memo (Ship-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE invchead SET invchead_billto_country = country_name"
    + "  FROM country "
    + " WHERE invchead_billto_country != ''"
    + "   AND UPPER(invchead_billto_country) = UPPER(country_abbr)"
    + '  <? if not exists("showAll") ?>'
    + "   AND NOT invchead_posted"
    + "  <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Invoice (Bill-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE invchead SET invchead_shipto_country = country_name"
    + "  FROM country "
    + " WHERE invchead_shipto_country != ''"
    + "   AND UPPER(invchead_shipto_country) = UPPER(country_abbr)"
    + '  <? if not exists("showAll") ?>'
    + "   AND NOT invchead_posted"
    + "  <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Invoice (Ship-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE quhead SET quhead_billtocountry = country_name"
    + "  FROM country"
    + " WHERE quhead_billtocountry != ''"
    + "   AND UPPER(quhead_billtocountry) = UPPER(country_abbr)"
    + '  <? if not exists("showAll") ?>'
    + "   AND quhead_expire >= CURRENT_DATE"
    + "  <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Quote (Bill-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE quhead SET quhead_shiptocountry = country_name"
    + "  FROM country"
    + " WHERE quhead_shiptocountry != ''"
    + "   AND UPPER(quhead_shiptocountry) = UPPER(country_abbr)"
    + '  <? if not exists("showAll") ?>'
    + "   AND quhead_expire >= CURRENT_DATE"
    + "  <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Quote (Ship-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE cohead SET cohead_billtocountry = country_name"
    + '  FROM country <? if not exists("showAll") ?>, coitem<? endif ?>'
    + " WHERE cohead_billtocountry != ''"
    + "   AND UPPER(cohead_billtocountry) = UPPER(country_abbr)"
    + '   <? if not exists("showAll") ?>'
    + "   AND cohead_id=coitem_cohead_id AND COALESCE(coitem_status, 'O')='O'"
    + "   <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Sales Order (Bill-To): " + qry.lastError().text);

  qry = toolbox.executeQuery(
      "UPDATE cohead SET cohead_shiptocountry = country_name"
    + '  FROM country <? if not exists("showAll") ?>, coitem<? endif ?>'
    + " WHERE cohead_shiptocountry != ''"
    + "   AND UPPER(cohead_shiptocountry) = UPPER(country_abbr)"
    + '   <? if not exists("showAll") ?>'
    + "   AND cohead_id=coitem_cohead_id AND COALESCE(coitem_status, 'O')='O'"
    + "   <? endif ?>", params);
  if (qry.lastError().type != 0)
    throw new Error("Error updating Sales Order (Ship-To): " + qry.lastError().text);

  } catch (e)
  {
    QMessageBox.critical(mywindow, "sFixAbbr() exception", e);
  }
  sFillList();
}

function sFillList()
{
  try {
  var params = new Object;
  if (_showAll.checked)
    params.showAll = true;

  var qry = toolbox.executeDbQuery('fixcountry', 'strictcountrycheck', params);
  _list.populate(qry);
  if (qry.lastError().type != 0)
  {
    QMessageBox.critical(mywindow, qsTr("Database Error"),
                         qry.lastError().text);
    return;
  }
  } catch (e) { print("sFillList exception: " + e); }
}

_addCountry.clicked.connect(sAddCountry);
_edit.clicked.connect(sEdit);
_fixAbbr.clicked.connect(sFixAbbr);
_fixBulk.clicked.connect(sFixBulk);
_fixCase.clicked.connect(sFixCase);
_query.clicked.connect(sFillList);
_showAll.toggled.connect(sFillList);

sFillList();

if (QMessageBox.question(mywindow, qsTr("Backed Up?"),
                         qsTr("<p>Have you backed up your database? "
                            + "You really should back up your database "
                            + "before running this utility."),
                         QMessageBox.Yes, QMessageBox.No) == QMessageBox.No)
  mywindow.close()

