+++ /dev/null
---
--- PostgreSQL database dump
---
-
-SET statement_timeout = 0;
-SET client_encoding = 'UTF8';
-SET standard_conforming_strings = on;
-SET check_function_bodies = false;
-SET client_min_messages = warning;
-
-SET search_path = fixcountry, pg_catalog;
-
---
--- Data for Name: pkgmetasql; Type: TABLE DATA; Schema: fixcountry; Owner: admin
---
-
-COPY pkgmetasql (metasql_id, metasql_group, metasql_name, metasql_notes, metasql_query, metasql_lastuser, metasql_lastupdate, metasql_grade) FROM stdin;
-449 fixcountry strictcountrycheck select details about countries used throughout the database that do not match country.country_name a copy of crm_strictcountrycheck to avoid problems later if someone disables or removes the fixCountry package -- Group: fixcountry\n-- Name: strictcountrycheck\n-- Notes: select details about countries used throughout the database\n-- that do not match country.country_name\n-- a copy of crm_strictcountrycheck to avoid problems later if\n-- someone disables or removes the fixCountry package\n<? if exists("count") ?>\nSELECT SUM(counter) AS counter FROM (\n<? endif ?>\n SELECT <? if exists("count") ?> COUNT(*) AS counter\n <? else ?> addr_id, 'Address' AS type, addr_number AS number, addr_country AS country, '' AS detail\n <? endif ?>\n FROM addr\n WHERE addr_country != ''\n AND addr_country NOT IN (SELECT country_name\n FROM country\n WHERE country_name IS NOT NULL)\n UNION ALL\n SELECT <? if exists("count") ?> COUNT(*)\n <? else ?> cmhead_id, 'Credit Memo', cmhead_number, cmhead_billtocountry, 'Bill-To'\n <? endif ?>\n FROM cmhead\n WHERE cmhead_billtocountry != ''\n AND cmhead_billtocountry NOT IN (SELECT country_name\n FROM country\n WHERE country_name IS NOT NULL)\n <? if not exists("showAll") ?> AND (NOT cmhead_posted) <? endif ?>\n UNION ALL\n SELECT <? if exists("count") ?> COUNT(*)\n <? else ?> cmhead_id, 'Credit Memo', cmhead_number, cmhead_billtocountry, 'Ship-To'\n <? endif ?>\n FROM cmhead\n WHERE cmhead_shipto_country != ''\n AND cmhead_shipto_country NOT IN (SELECT country_name\n FROM country\n WHERE country_name IS NOT NULL)\n <? if not exists("showAll") ?> AND (NOT cmhead_posted) <? endif ?>\n UNION ALL\n SELECT <? if exists("count") ?> COUNT(*)\n <? else ?> invchead_id, 'Invoice', invchead_invcnumber, invchead_billto_country, 'Bill-To'\n <? endif ?>\n FROM invchead\n WHERE invchead_billto_country != ''\n AND invchead_billto_country NOT IN (SELECT country_name\n FROM country\n WHERE country_name IS NOT NULL)\n <? if not exists("showAll") ?> AND (NOT invchead_posted) <? endif ?>\n UNION ALL\n SELECT <? if exists("count") ?> COUNT(*)\n <? else ?> invchead_id, 'Invoice', invchead_invcnumber, invchead_shipto_country, 'Ship-To'\n <? endif ?>\n FROM invchead\n WHERE invchead_shipto_country != ''\n AND invchead_shipto_country NOT IN (SELECT country_name\n FROM country\n WHERE country_name IS NOT NULL)\n <? if not exists("showAll") ?> AND (NOT invchead_posted) <? endif ?>\n UNION ALL\n SELECT <? if exists("count") ?> COUNT(*)\n <? else ?> quhead_id, 'Quote', CAST(quhead_number AS TEXT), quhead_billtocountry, 'Bill-To'\n <? endif ?>\n FROM quhead\n WHERE quhead_billtocountry != ''\n AND quhead_billtocountry NOT IN (SELECT country_name\n FROM country\n WHERE country_name IS NOT NULL)\n <? if not exists("showAll") ?> AND (quhead_expire>=CURRENT_DATE) <? endif ?>\n UNION ALL\n SELECT <? if exists("count") ?> COUNT(*)\n <? else ?> quhead_id, 'Quote', CAST(quhead_number AS TEXT), quhead_shiptocountry, 'Ship-To'\n <? endif ?>\n FROM quhead\n WHERE quhead_shiptocountry != ''\n AND quhead_shiptocountry NOT IN (SELECT country_name\n FROM country\n WHERE country_name IS NOT NULL)\n <? if not exists("showAll") ?> AND (quhead_expire>=CURRENT_DATE) <? endif ?>\n UNION ALL\n SELECT <? if exists("count") ?> COUNT(*)\n <? else ?> cohead_id, 'Sales Order', cohead_number, cohead_billtocountry, 'Bill-To'\n <? endif ?>\n FROM cohead\n WHERE cohead_billtocountry != ''\n AND cohead_billtocountry NOT IN (SELECT country_name\n FROM country\n WHERE country_name IS NOT NULL)\n <? if not exists("showAll") ?>\n AND cohead_id IN (SELECT coitem_cohead_id\n FROM coitem\n WHERE COALESCE(coitem_status, 'O')='O')\n <? endif ?>\n UNION ALL\n SELECT <? if exists("count") ?> COUNT(*)\n <? else ?> cohead_id, 'Sales Order', cohead_number, cohead_shiptocountry, 'Ship-To'\n <? endif ?>\n FROM cohead\n WHERE cohead_shiptocountry != ''\n AND cohead_shiptocountry NOT IN (SELECT country_name\n FROM country\n WHERE country_name IS NOT NULL)\n <? if not exists("showAll") ?>\n AND cohead_id IN (SELECT coitem_cohead_id\n FROM coitem\n WHERE COALESCE(coitem_status, 'O')='O')\n <? endif ?>\n\n<? if exists("count") ?>\n) AS dummy;\n<? endif ?>\n \N \N 0
-\.
-
-
---
--- PostgreSQL database dump complete
---
-
+++ /dev/null
---
--- PostgreSQL database dump
---
-
-SET statement_timeout = 0;
-SET client_encoding = 'UTF8';
-SET standard_conforming_strings = on;
-SET check_function_bodies = false;
-SET client_min_messages = warning;
-
-SET search_path = fixcountry, pg_catalog;
-
---
--- Data for Name: pkgscript; Type: TABLE DATA; Schema: fixcountry; Owner: admin
---
-
-COPY pkgscript (script_id, script_name, script_order, script_enabled, script_source, script_notes) FROM stdin;
-124 initMenu 0 t function sFixCountries()\n{\n try {\n var newdlg = toolbox.openWindow("fixCountries", 0, Qt.Window, Qt.NonModal);\n } catch(e) { print("sFixCountries exception: " + e); }\n}\n\nvar fixCountriesAction =\n toolbox.menuAddAction(mainwindow.findChild("menu.crm.utilities"),\n qsTr("Fix Countries before setting Strict Countries option"),\n privileges.value("MaintainAddresses") &&\n privileges.value("MaintainCreditMemos") &&\n privileges.value("MaintainMiscInvoices")&&\n privileges.value("MaintainSalesOrders") &&\n privileges.value("MaintainQuotes"));\n\n// Connect the trigger to the action\nfixCountriesAction.triggered.connect(sFixCountries);\nprint("initMenu loaded");\n fixCountry package
-125 fixCountries 0 t var _addCountry = mywindow.findChild("_addCountry");\nvar _edit = mywindow.findChild("_edit");\nvar _fixAbbr = mywindow.findChild("_fixAbbr");\nvar _fixBulk = mywindow.findChild("_fixBulk");\nvar _fixCase = mywindow.findChild("_fixCase");\nvar _list = mywindow.findChild("_list");\nvar _query = mywindow.findChild("_query");\nvar _showAll = mywindow.findChild("_showAll");\n\n_list.addColumn("Record Type", -1, Qt.AlignLeft, true, "type");\n_list.addColumn("Number", -1, Qt.AlignLeft, true, "number");\n_list.addColumn("Country", -1, Qt.AlignLeft, true, "country");\n_list.addColumn("Details", -1, Qt.AlignLeft, true, "detail");\n\nfunction sAddCountry()\n{\n try {\n var params = new Object;;\n params.mode = "new";\n\n var newdlg = toolbox.openWindow("country", mywindow, Qt.Dialog, Qt.WindowModal);\n toolbox.lastWindow().set(params);\n newdlg.exec();\n } catch (e) { print("sAddCountry exception: " + e); }\n}\n\nfunction openEditorWindow(pwindow, pidname, pparent)\n{\n try {\n var params = new Object;\n params[pidname] = _list.id();\n if (pidname == 'quhead_id')\n params.mode = "editQuote";\n else\n params.mode = "edit";\n\n var newdlg = toolbox.openWindow(pwindow, pparent, Qt.Dialog, Qt.WindowModal);\n toolbox.lastWindow().set(params);\n newdlg.exec();\n } catch(e) { print("openEditorWindow exception (" + e.lineNumber + "): " + e); }\n}\n\nfunction sEdit()\n{\n try {\n var currentItem = _list.currentItem();\n\n switch (currentItem.text(0))\n {\n case 'Address':\n openEditorWindow("address", "addr_id", mywindow);\n break;\n\n case 'Credit Memo':\n openEditorWindow("creditMemo", "cmhead_id", 0);\n break;\n\n case 'Invoice':\n openEditorWindow("invoice", "invchead_id", mywindow);\n break;\n\n case 'Quote':\n openEditorWindow("salesOrder", "quhead_id", 0);\n break;\n\n case 'Sales Order':\n openEditorWindow("salesOrder", "sohead_id", 0);\n break;\n }\n sFillList();\n } catch (e) { print("sEdit() exception: " + e); }\n}\n\nfunction sFixBulk()\n{\n print("sFixBulk() entered");\n try {\n var params = new Object;\n if (_showAll.checked)\n params.showAll = true;\n\n var newdlg = toolbox.openWindow("bulkFixCountry", mywindow,\n Qt.WindowModal, Qt.Dialog);\n toolbox.lastWindow().set(params);\n\n sFillList();\n } catch (e) { print("sFixBulk() exception: " + e); }\n}\n\nfunction sFixCase()\n{\n try {\n if (toolbox.messageBox("question", mywindow, "Are you sure?",\n "<p>Are you sure that you want to change all countries that "\n + "appear to be correct except for upper- and lower-case "\n + "differences in a batch?<p>(This change will obey the "\n + "status of the 'Show closed records' check box.)",\n 0x00004000, 0x00010000) == 0x00010000)\n return;\n\n var params = new Object;\n if (_showAll.checked)\n params.showAll = true;\n\n var qry = toolbox.executeQuery(\n "UPDATE addr SET addr_country = country_name"\n + " FROM country "\n + " WHERE addr_country != ''"\n + " AND UPPER(addr_country) = UPPER(country_name)"\n + " AND addr_country != country_name;", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Address: " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE cmhead SET cmhead_billtocountry = country_name"\n + " FROM country "\n + " WHERE cmhead_billtocountry != ''"\n + " AND UPPER(cmhead_billtocountry) = UPPER(country_name)"\n + " AND cmhead_billtocountry != country_name"\n + ' <? if not exists("showAll") ?>'\n + " AND NOT cmhead_posted"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Credit Memo (Bill-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE cmhead SET cmhead_shipto_country = country_name"\n + " FROM country "\n + " WHERE cmhead_shipto_country != ''"\n + " AND UPPER(cmhead_shipto_country) = UPPER(country_name)"\n + " AND cmhead_shipto_country != country_name"\n + ' <? if not exists("showAll") ?>'\n + " AND NOT cmhead_posted"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Credit Memo (Ship-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE invchead SET invchead_billto_country = country_name"\n + " FROM country "\n + " WHERE invchead_billto_country != ''"\n + " AND UPPER(invchead_billto_country) = UPPER(country_name)"\n + " AND invchead_billto_country != country_name"\n + ' <? if not exists("showAll") ?>'\n + " AND NOT invchead_posted"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Invoice (Bill-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE invchead SET invchead_shipto_country = country_name"\n + " FROM country "\n + " WHERE invchead_shipto_country != ''"\n + " AND UPPER(invchead_shipto_country) = UPPER(country_name)"\n + " AND invchead_shipto_country != country_name"\n + ' <? if not exists("showAll") ?>'\n + " AND NOT invchead_posted"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Invoice (Ship-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE quhead SET quhead_billtocountry = country_name"\n + " FROM country"\n + " WHERE quhead_billtocountry != ''"\n + " AND UPPER(quhead_billtocountry) = UPPER(country_name)"\n + " AND quhead_billtocountry != country_name"\n + ' <? if not exists("showAll") ?>'\n + " AND quhead_expire >= CURRENT_DATE"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Quote (Bill-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE quhead SET quhead_shiptocountry = country_name"\n + " FROM country"\n + " WHERE quhead_shiptocountry != ''"\n + " AND UPPER(quhead_shiptocountry) = UPPER(country_name)"\n + " AND quhead_shiptocountry != country_name"\n + ' <? if not exists("showAll") ?>'\n + " AND quhead_expire >= CURRENT_DATE"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Quote (Ship-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE cohead SET cohead_billtocountry = country_name"\n + ' FROM country <? if not exists("showAll") ?>, coitem<? endif ?>'\n + " WHERE cohead_billtocountry != ''"\n + " AND UPPER(cohead_billtocountry) = UPPER(country_name)"\n + " AND cohead_billtocountry != country_name"\n + ' <? if not exists("showAll") ?>'\n + " AND cohead_id=coitem_cohead_id AND COALESCE(coitem_status, 'O')='O'"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Sales Order (Bill-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE cohead SET cohead_shiptocountry = country_name"\n + ' FROM country <? if not exists("showAll") ?>, coitem<? endif ?>'\n + " WHERE cohead_shiptocountry != ''"\n + " AND UPPER(cohead_shiptocountry) = UPPER(country_name)"\n + " AND cohead_shiptocountry != country_name"\n + ' <? if not exists("showAll") ?>'\n + " AND cohead_id=coitem_cohead_id AND COALESCE(coitem_status, 'O')='O'"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Sales Order (Ship-To): " + qry.lastError().text);\n\n } catch (e) { toolbox.messageBox("critical", mywindow, "sFixCase() exception", e); }\n\n sFillList();\n}\n\nfunction sFixAbbr()\n{\n try {\n if (toolbox.messageBox("question", mywindow, "Are you sure?",\n "<p>Are you sure that you want to replace all country "\n + "abbreviations to the corresponding full country names?"\n + "<p>(This change will obey the "\n + "status of the 'Show closed records' check box.)",\n 0x00004000, 0x00010000) == 0x00010000)\n return;\n\n var params = new Object;\n if (_showAll.checked)\n params.showAll = true;\n\n var qry = toolbox.executeQuery(\n "UPDATE addr SET addr_country = country_name"\n + " FROM country "\n + " WHERE addr_country != ''"\n + " AND UPPER(addr_country) = UPPER(country_abbr);", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Address: " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE cmhead SET cmhead_billtocountry = country_name"\n + " FROM country "\n + " WHERE cmhead_billtocountry != ''"\n + " AND UPPER(cmhead_billtocountry) = UPPER(country_abbr)"\n + ' <? if not exists("showAll") ?>'\n + " AND NOT cmhead_posted"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Credit Memo (Bill-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE cmhead SET cmhead_shipto_country = country_name"\n + " FROM country "\n + " WHERE cmhead_shipto_country != ''"\n + " AND UPPER(cmhead_shipto_country) = UPPER(country_abbr)"\n + ' <? if not exists("showAll") ?>'\n + " AND NOT cmhead_posted"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Credit Memo (Ship-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE invchead SET invchead_billto_country = country_name"\n + " FROM country "\n + " WHERE invchead_billto_country != ''"\n + " AND UPPER(invchead_billto_country) = UPPER(country_abbr)"\n + ' <? if not exists("showAll") ?>'\n + " AND NOT invchead_posted"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Invoice (Bill-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE invchead SET invchead_shipto_country = country_name"\n + " FROM country "\n + " WHERE invchead_shipto_country != ''"\n + " AND UPPER(invchead_shipto_country) = UPPER(country_abbr)"\n + ' <? if not exists("showAll") ?>'\n + " AND NOT invchead_posted"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Invoice (Ship-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE quhead SET quhead_billtocountry = country_name"\n + " FROM country"\n + " WHERE quhead_billtocountry != ''"\n + " AND UPPER(quhead_billtocountry) = UPPER(country_abbr)"\n + ' <? if not exists("showAll") ?>'\n + " AND quhead_expire >= CURRENT_DATE"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Quote (Bill-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE quhead SET quhead_shiptocountry = country_name"\n + " FROM country"\n + " WHERE quhead_shiptocountry != ''"\n + " AND UPPER(quhead_shiptocountry) = UPPER(country_abbr)"\n + ' <? if not exists("showAll") ?>'\n + " AND quhead_expire >= CURRENT_DATE"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Quote (Ship-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE cohead SET cohead_billtocountry = country_name"\n + ' FROM country <? if not exists("showAll") ?>, coitem<? endif ?>'\n + " WHERE cohead_billtocountry != ''"\n + " AND UPPER(cohead_billtocountry) = UPPER(country_abbr)"\n + ' <? if not exists("showAll") ?>'\n + " AND cohead_id=coitem_cohead_id AND COALESCE(coitem_status, 'O')='O'"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Sales Order (Bill-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n "UPDATE cohead SET cohead_shiptocountry = country_name"\n + ' FROM country <? if not exists("showAll") ?>, coitem<? endif ?>'\n + " WHERE cohead_shiptocountry != ''"\n + " AND UPPER(cohead_shiptocountry) = UPPER(country_abbr)"\n + ' <? if not exists("showAll") ?>'\n + " AND cohead_id=coitem_cohead_id AND COALESCE(coitem_status, 'O')='O'"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Sales Order (Ship-To): " + qry.lastError().text);\n\n } catch (e)\n {\n toolbox.messageBox("critical", mywindow, "sFixAbbr() exception", e);\n }\n sFillList();\n}\n\nfunction sFillList()\n{\n try {\n var params = new Object;\n if (_showAll.checked)\n params.showAll = true;\n\n var qry = toolbox.executeDbQuery('fixcountry', 'strictcountrycheck', params);\n _list.populate(qry);\n if (qry.lastError().type != 0)\n {\n toolbox.messageBox("critical", mywindow, "Database Error", qry.lastError().text);\n return;\n }\n } catch (e) { print("sFillList exception: " + e); }\n}\n\n_addCountry.clicked.connect(sAddCountry);\n_edit.clicked.connect(sEdit);\n_fixAbbr.clicked.connect(sFixAbbr);\n_fixBulk.clicked.connect(sFixBulk);\n_fixCase.clicked.connect(sFixCase);\n_query.clicked.connect(sFillList);\n_showAll.toggled.connect(sFillList);\n\nsFillList();\n\nif (toolbox.messageBox("question", mywindow, "Backed Up?",\n "<p>Have you backed up your database? You really should "\n + "back up your database before running this utility.",\n 0x00004000, 0x00010000) == 0x00010000)\n mywindow.close()\n\n fixCountry package
-126 bulkFixCountry 0 t var _buttons = mywindow.findChild("_buttons");\nvar _oldCountry = mywindow.findChild("_oldCountry");\nvar _newCountry = mywindow.findChild("_newCountry");\nvar _params;\n\nfunction set(params)\n{\n print("bulkFixCountry::set() entered");\n _params = params;\n var qry = toolbox.executeQuery(\n "SELECT MIN(id), country, country AS code "\n + " FROM ("\n + " SELECT addr_id AS id, addr_country AS country"\n + " FROM addr"\n + " WHERE addr_country != ''"\n + " AND addr_country NOT IN (SELECT country_name"\n + " FROM country"\n + " WHERE country_name IS NOT NULL)"\n + " UNION ALL"\n + " SELECT cmhead_id, cmhead_billtocountry"\n + " FROM cmhead"\n + " WHERE cmhead_billtocountry != ''"\n + " AND cmhead_billtocountry NOT IN (SELECT country_name"\n + " FROM country"\n + " WHERE country_name IS NOT NULL)"\n + ' <? if not exists("showAll") ?> AND (NOT cmhead_posted) <? endif ?>'\n + " UNION ALL"\n + " SELECT cmhead_id, cmhead_shipto_country"\n + " FROM cmhead"\n + " WHERE cmhead_shipto_country != ''"\n + " AND cmhead_shipto_country NOT IN (SELECT country_name"\n + " FROM country"\n + " WHERE country_name IS NOT NULL)"\n + ' <? if not exists("showAll") ?> AND (NOT cmhead_posted) <? endif ?>'\n + " UNION ALL"\n + " SELECT invchead_id, invchead_billto_country"\n + " FROM invchead"\n + " WHERE invchead_billto_country != ''"\n + " AND invchead_billto_country NOT IN (SELECT country_name"\n + " FROM country"\n + " WHERE country_name IS NOT NULL)"\n + ' <? if not exists("showAll") ?> AND (NOT invchead_posted) <? endif ?>'\n + " UNION ALL"\n + " SELECT invchead_id, invchead_shipto_country"\n + " FROM invchead"\n + " WHERE invchead_shipto_country != ''"\n + " AND invchead_shipto_country NOT IN (SELECT country_name"\n + " FROM country"\n + " WHERE country_name IS NOT NULL)"\n + ' <? if not exists("showAll") ?> AND (NOT invchead_posted) <? endif ?>'\n + " UNION ALL"\n + " SELECT quhead_id, quhead_billtocountry"\n + " FROM quhead"\n + " WHERE quhead_billtocountry != ''"\n + " AND quhead_billtocountry NOT IN (SELECT country_name"\n + " FROM country"\n + " WHERE country_name IS NOT NULL)"\n + ' <? if not exists("showAll") ?> AND (quhead_expire>=CURRENT_DATE) <? endif ?>'\n + " UNION ALL"\n + " SELECT quhead_id, quhead_shiptocountry"\n + " FROM quhead"\n + " WHERE quhead_shiptocountry != ''"\n + " AND quhead_shiptocountry NOT IN (SELECT country_name"\n + " FROM country"\n + " WHERE country_name IS NOT NULL)"\n + ' <? if not exists("showAll") ?> AND (quhead_expire>=CURRENT_DATE) <? endif ?>'\n + " UNION ALL"\n + " SELECT cohead_id, cohead_billtocountry"\n + " FROM cohead"\n + " WHERE cohead_billtocountry != ''"\n + " AND cohead_billtocountry NOT IN (SELECT country_name"\n + " FROM country"\n + " WHERE country_name IS NOT NULL)"\n + ' <? if not exists("showAll") ?>'\n + " AND cohead_id IN (SELECT coitem_cohead_id"\n + " FROM coitem"\n + " WHERE COALESCE(coitem_status, 'O')='O')"\n + ' <? endif ?>'\n + " UNION ALL"\n + " SELECT cohead_id, cohead_shiptocountry"\n + " FROM cohead"\n + " WHERE cohead_shiptocountry != ''"\n + " AND cohead_shiptocountry NOT IN (SELECT country_name"\n + " FROM country"\n + " WHERE country_name IS NOT NULL)"\n + ' <? if not exists("showAll") ?>'\n + " AND cohead_id IN (SELECT coitem_cohead_id"\n + " FROM coitem"\n + " WHERE COALESCE(coitem_status, 'O')='O')"\n + ' <? endif ?>'\n + ") AS dummy "\n + "GROUP BY country "\n + "ORDER BY country;",\n\n params);\n _oldCountry.populate(qry);\n if (qry.lastError().type != 0)\n toolbox.messageBox("warning", mywindow, qsTr("Database Error"),\n qry.lastError().text);\n}\n\nfunction sSave()\n{\n print("bulkFixCountry::sSave() entered");\n try {\n var params = _params;\n params.oldCountry = _oldCountry.currentText;\n params.newCountry = _newCountry.currentText;\n\n var qry = toolbox.executeQuery(\n 'UPDATE addr SET addr_country = <? value("newCountry") ?>'\n + ' WHERE addr_country = <? value("oldCountry") ?>;', params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Address: " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n 'UPDATE cmhead SET cmhead_billtocountry = <? value("newCountry") ?>'\n + ' WHERE cmhead_billtocountry = <? value("oldCountry") ?>'\n + ' <? if not exists("showAll") ?>'\n + " AND NOT cmhead_posted"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Credit Memo (Bill-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n 'UPDATE cmhead SET cmhead_shipto_country = <? value("newCountry") ?>'\n + ' WHERE cmhead_shipto_country = <? value("oldCountry") ?>'\n + ' <? if not exists("showAll") ?>'\n + " AND NOT cmhead_posted"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Credit Memo (Ship-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n 'UPDATE invchead SET invchead_billto_country = <? value("newCountry") ?>'\n + ' WHERE invchead_billto_country = <? value("oldCountry") ?>'\n + ' <? if not exists("showAll") ?>'\n + " AND NOT invchead_posted"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Invoice (Bill-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n 'UPDATE invchead SET invchead_shipto_country = <? value("newCountry") ?>'\n + ' WHERE invchead_shipto_country = <? value("oldCountry") ?>'\n + ' <? if not exists("showAll") ?>'\n + " AND NOT invchead_posted"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Invoice (Ship-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n 'UPDATE quhead SET quhead_billtocountry = <? value("newCountry") ?>'\n + ' WHERE quhead_billtocountry = <? value("oldCountry") ?>'\n + ' <? if not exists("showAll") ?>'\n + " AND quhead_expire >= CURRENT_DATE"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Quote (Bill-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n 'UPDATE quhead SET quhead_shiptocountry = <? value("newCountry") ?>'\n + ' WHERE quhead_shiptocountry = <? value("oldCountry") ?>'\n + ' <? if not exists("showAll") ?>'\n + " AND quhead_expire >= CURRENT_DATE"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Quote (Ship-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n 'UPDATE cohead SET cohead_billtocountry = <? value("newCountry") ?>'\n + ' WHERE cohead_billtocountry = <? value("oldCountry") ?>'\n + ' <? if not exists("showAll") ?>'\n + " AND cohead_id IN (SELECT coitem_cohead_id"\n + " FROM coitem"\n + " WHERE COALESCE(coitem_status, 'O')='O')"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Sales Order (Bill-To): " + qry.lastError().text);\n\n qry = toolbox.executeQuery(\n 'UPDATE cohead SET cohead_shiptocountry = <? value("newCountry") ?>'\n + ' WHERE cohead_shiptocountry = <? value("oldCountry") ?>'\n + ' <? if not exists("showAll") ?>'\n + " AND cohead_id IN (SELECT coitem_cohead_id"\n + " FROM coitem"\n + " WHERE COALESCE(coitem_status, 'O')='O')"\n + " <? endif ?>", params);\n if (qry.lastError().type != 0)\n throw new Error("Error updating Sales Order (Ship-To): " + qry.lastError().text);\n\n }\n catch (e)\n {\n toolbox.messageBox("critical", mywindow,\n "bulkFixCountry::sSave() exception", e);\n\n }\n mywindow.close();\n}\n\n_buttons.accepted.connect(sSave);\n_buttons.rejected.connect(mywindow, "close()");\n fixCountry package
-\.
-
-
---
--- PostgreSQL database dump complete
---
-
+++ /dev/null
---
--- PostgreSQL database dump
---
-
-SET statement_timeout = 0;
-SET client_encoding = 'UTF8';
-SET standard_conforming_strings = on;
-SET check_function_bodies = false;
-SET client_min_messages = warning;
-
-SET search_path = fixcountry, pg_catalog;
-
---
--- Data for Name: pkguiform; Type: TABLE DATA; Schema: fixcountry; Owner: admin
---
-
-COPY pkguiform (uiform_id, uiform_name, uiform_order, uiform_enabled, uiform_source, uiform_notes) FROM stdin;
-58 fixCountries 0 t <?xml version="1.0" encoding="UTF-8"?>\n<ui version="4.0">\n <class>fixCountries</class>\n <widget class="QWidget" name="fixCountries">\n <property name="geometry">\n <rect>\n <x>0</x>\n <y>0</y>\n <width>800</width>\n <height>600</height>\n </rect>\n </property>\n <property name="maximumSize">\n <size>\n <width>1000</width>\n <height>16777215</height>\n </size>\n </property>\n <property name="windowTitle">\n <string>fixCountries</string>\n </property>\n <layout class="QGridLayout" name="gridLayout">\n <item row="1" column="1">\n <layout class="QVBoxLayout" name="verticalLayout">\n <item>\n <widget class="QPushButton" name="_query">\n <property name="text">\n <string>Query</string>\n </property>\n </widget>\n </item>\n <item>\n <spacer name="verticalSpacer">\n <property name="orientation">\n <enum>Qt::Vertical</enum>\n </property>\n <property name="sizeType">\n <enum>QSizePolicy::Fixed</enum>\n </property>\n <property name="sizeHint" stdset="0">\n <size>\n <width>20</width>\n <height>15</height>\n </size>\n </property>\n </spacer>\n </item>\n <item>\n <widget class="QPushButton" name="_fixCase">\n <property name="text">\n <string>Fix Case Differences</string>\n </property>\n </widget>\n </item>\n <item>\n <widget class="QPushButton" name="_fixAbbr">\n <property name="text">\n <string>Replace Abbreviations</string>\n </property>\n </widget>\n </item>\n <item>\n <widget class="QPushButton" name="_fixBulk">\n <property name="text">\n <string>Bulk Replace</string>\n </property>\n </widget>\n </item>\n <item>\n <spacer name="verticalSpacer_3">\n <property name="orientation">\n <enum>Qt::Vertical</enum>\n </property>\n <property name="sizeType">\n <enum>QSizePolicy::Fixed</enum>\n </property>\n <property name="sizeHint" stdset="0">\n <size>\n <width>20</width>\n <height>15</height>\n </size>\n </property>\n </spacer>\n </item>\n <item>\n <widget class="QPushButton" name="_edit">\n <property name="enabled">\n <bool>false</bool>\n </property>\n <property name="text">\n <string>Edit Selection</string>\n </property>\n </widget>\n </item>\n <item>\n <widget class="QPushButton" name="_addCountry">\n <property name="text">\n <string>Add Country</string>\n </property>\n </widget>\n </item>\n <item>\n <spacer name="verticalSpacer_2">\n <property name="orientation">\n <enum>Qt::Vertical</enum>\n </property>\n <property name="sizeHint" stdset="0">\n <size>\n <width>20</width>\n <height>40</height>\n </size>\n </property>\n </spacer>\n </item>\n </layout>\n </item>\n <item row="1" column="0">\n <widget class="XTreeWidget" name="_list">\n <property name="sizePolicy">\n <sizepolicy hsizetype="Expanding" vsizetype="Expanding">\n <horstretch>0</horstretch>\n <verstretch>3</verstretch>\n </sizepolicy>\n </property>\n </widget>\n </item>\n <item row="2" column="0">\n <widget class="XCheckBox" name="_showAll">\n <property name="text">\n <string>Show closed records</string>\n </property>\n <property name="forgetful">\n <bool>true</bool>\n </property>\n </widget>\n </item>\n <item row="0" column="0" colspan="2">\n <widget class="QLabel" name="label">\n <property name="text">\n <string><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN" "http://www.w3.org/TR/REC-html40/strict.dtd">\n<html><head><meta name="qrichtext" content="1" /><style type="text/css">\np, li { white-space: pre-wrap; }\n</style></head><body style=" font-family:'Lucida Grande'; font-size:13pt; font-weight:400; font-style:normal;">\n<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;">You must fix the countries in the following records before turning on Strict Country Checking (System &gt; Configure Modules &gt; CRM...).</p>\n<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"></p>\n<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;">Here is a short description of your options (assume the <span style=" font-style:italic;">country</span> table has a country named 'Homeland' with the abbreviation 'HL'):</p>\n<ul style="-qt-list-indent: 1;"><li style=" margin-top:12px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-weight:600;">Query</span> will requery the database for records with invalid countries.</li>\n<li style=" font-weight:600;" style=" margin-top:1px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;">Fix Case Differences<span style=" font-weight:400;"> will change all occurrences of 'HOMELAND' or 'HomeLand' to 'Homeland'.</span></li>\n<li style=" margin-top:1px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-weight:600;">Replace Abbreviations</span> will change all occurrences of 'HL' to 'Homeland'.</li>\n<li style=" margin-top:1px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-weight:600;">Bulk Replace</span> will replace all occurrences of an invalid country that you choose with a valid country that you choose.</li>\n<li style=" margin-top:1px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-weight:600;">Edit Selection</span> will open the selected record for you to fix. If a changed address remains on the list, delete duplicates with CRM &gt; Address &gt; List....</li>\n<li style=" margin-top:1px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-weight:600;">Add Country</span> is for the rare case where you must add a new country. Try not to use this.</li>\n<li style=" margin-top:1px; margin-bottom:12px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;"><span style=" font-weight:600;">Show closed records</span> will show historical data, such as closed sales orders and posted credit memos &amp; invoices. These will continue to print properly if you don't fix them, but you will have to change the country if you edit and save them with Strict Country Checking turned on.</li></ul></body></html></string>\n </property>\n <property name="textFormat">\n <enum>Qt::RichText</enum>\n </property>\n <property name="wordWrap">\n <bool>true</bool>\n </property>\n </widget>\n </item>\n </layout>\n </widget>\n <customwidgets>\n <customwidget>\n <class>XCheckBox</class>\n <extends>QCheckBox</extends>\n <header>xcheckbox.h</header>\n </customwidget>\n <customwidget>\n <class>XTreeWidget</class>\n <extends>QTreeWidget</extends>\n <header>xtreewidget.h</header>\n </customwidget>\n </customwidgets>\n <tabstops>\n <tabstop>_list</tabstop>\n <tabstop>_showAll</tabstop>\n <tabstop>_query</tabstop>\n <tabstop>_fixCase</tabstop>\n <tabstop>_fixAbbr</tabstop>\n <tabstop>_edit</tabstop>\n <tabstop>_addCountry</tabstop>\n </tabstops>\n <resources/>\n <connections>\n <connection>\n <sender>_list</sender>\n <signal>valid(bool)</signal>\n <receiver>_edit</receiver>\n <slot>setEnabled(bool)</slot>\n <hints>\n <hint type="sourcelabel">\n <x>551</x>\n <y>512</y>\n </hint>\n <hint type="destinationlabel">\n <x>616</x>\n <y>503</y>\n </hint>\n </hints>\n </connection>\n </connections>\n</ui>\n fixCountry package
-59 bulkFixCountry 0 t <?xml version="1.0" encoding="UTF-8"?>\n<ui version="4.0">\n <class>bulkFixCountry</class>\n <widget class="QWidget" name="bulkFixCountry">\n <property name="geometry">\n <rect>\n <x>0</x>\n <y>0</y>\n <width>427</width>\n <height>162</height>\n </rect>\n </property>\n <property name="windowTitle">\n <string>Bulk Fix Invalid Country</string>\n </property>\n <layout class="QHBoxLayout" name="horizontalLayout">\n <item>\n <layout class="QFormLayout" name="formLayout">\n <item row="0" column="0">\n <widget class="QLabel" name="_oldCountryLit">\n <property name="text">\n <string>Replace all instances of this:</string>\n </property>\n <property name="buddy">\n <cstring>_oldCountry</cstring>\n </property>\n </widget>\n </item>\n <item row="1" column="0">\n <widget class="QLabel" name="_newCountryLit">\n <property name="text">\n <string>with this country:</string>\n </property>\n <property name="buddy">\n <cstring>_newCountry</cstring>\n </property>\n </widget>\n </item>\n <item row="1" column="1">\n <widget class="XComboBox" name="_newCountry">\n <property name="allowNull">\n <bool>true</bool>\n </property>\n <property name="type">\n <enum>XComboBox::Countries</enum>\n </property>\n </widget>\n </item>\n <item row="0" column="1">\n <widget class="XComboBox" name="_oldCountry"/>\n </item>\n </layout>\n </item>\n <item>\n <widget class="QDialogButtonBox" name="_buttons">\n <property name="orientation">\n <enum>Qt::Vertical</enum>\n </property>\n <property name="standardButtons">\n <set>QDialogButtonBox::Cancel|QDialogButtonBox::SaveAll</set>\n </property>\n <property name="centerButtons">\n <bool>false</bool>\n </property>\n </widget>\n </item>\n </layout>\n </widget>\n <customwidgets>\n <customwidget>\n <class>XComboBox</class>\n <extends>QComboBox</extends>\n <header>xcombobox.h</header>\n </customwidget>\n </customwidgets>\n <resources/>\n <connections/>\n</ui>\n fixCountry package
-\.
-
-
---
--- PostgreSQL database dump complete
---
-
projects / Pman.Xtuple / commitdiff