From 36610486b9dbf72e9e0c6b0b3ec75e1daa158e1e Mon Sep 17 00:00:00 2001
From: eileen <emcnaughton@wikimedia.org>
Date: Mon, 6 Mar 2017 17:23:10 +1300
Subject: [PATCH] CRM-20062 upgrade component of Kenyan provinces

---
 CRM/Upgrade/Incremental/php/FourSeven.php    | 62 ++++++++++++++++++++
 CRM/Upgrade/Incremental/sql/4.7.18.mysql.tpl | 53 +++++++++++++++++
 2 files changed, 115 insertions(+)
 create mode 100644 CRM/Upgrade/Incremental/sql/4.7.18.mysql.tpl

diff --git a/CRM/Upgrade/Incremental/php/FourSeven.php b/CRM/Upgrade/Incremental/php/FourSeven.php
index 36b4313e9c..53083e06e0 100644
--- a/CRM/Upgrade/Incremental/php/FourSeven.php
+++ b/CRM/Upgrade/Incremental/php/FourSeven.php
@@ -307,6 +307,16 @@ class CRM_Upgrade_Incremental_php_FourSeven extends CRM_Upgrade_Incremental_Base
     $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => $rev)), 'runSql', $rev);
   }
 
+  /**
+   * Upgrade function.
+   *
+   * @param string $rev
+   */
+  public function upgrade_4_7_18($rev) {
+    $this->addTask('Update Kenyan Provinces', 'updateKenyanProvinces');
+    $this->addTask(ts('Upgrade DB to %1: SQL', array(1 => $rev)), 'runSql', $rev);
+  }
+
   /*
    * Important! All upgrade functions MUST add a 'runSql' task.
    * Uncomment and use the following template for a new upgrade version
@@ -983,4 +993,56 @@ FROM `civicrm_dashboard_contact` JOIN `civicrm_contact` WHERE civicrm_dashboard_
     return TRUE;
   }
 
+  /**
+   * Update Kenyan Provinces to reflect changes per CRM-20062
+   *
+   * @param \CRM_Queue_TaskContext $ctx
+   */
+  public function updateKenyanProvinces(CRM_Queue_TaskContext $ctx) {
+    $kenyaCountryID = CRM_Core_DAO::singleValueQuery('SELECT max(id) from civicrm_country where iso_code = "KE"');
+    $oldProvinces = array(
+      'Nairobi Municipality',
+      'Coast',
+      'North-Eastern Kaskazini Mashariki',
+      'Rift Valley',
+      'Western Magharibi',
+    );
+    self::deprecateStateProvinces($kenyaCountryID, $oldProvinces);
+    return TRUE;
+  }
+
+  /**
+   * Deprecate provinces that no longer exist.
+   *
+   * @param int $countryID
+   * @param array $provinces
+   */
+  public static function deprecateStateProvinces($countryID, $provinces) {
+    foreach ($provinces as $province) {
+      $existingStateID = CRM_Core_DAO::singleValueQuery("
+        SELECT id FROM civicrm_state_province
+        WHERE country_id = %1
+        AND name = %2
+      ",
+      array(1 => array($countryID, 'Int'), 2 => array($province, 'String')));
+
+      if (!$existingStateID) {
+        continue;
+      }
+      if (!CRM_Core_DAO::singleValueQuery("
+       SELECT count(*) FROM civicrm_address
+       WHERE state_province_id = %1
+       ", array(1 => array($existingStateID, 'Int')))
+      ) {
+        CRM_Core_DAO::executeQuery("DELETE FROM civicrm_state_province WHERE id = %1", array(1 => array($existingStateID, 'Int')));
+      }
+      else {
+        $params = array('1' => array(ts("Former - $province"), 'String'));
+        CRM_Core_DAO::executeQuery("
+          UPDATE civicrm_state_province SET name = %1 WHERE id = $existingStateID
+        ", $params);
+      }
+    }
+  }
+
 }
diff --git a/CRM/Upgrade/Incremental/sql/4.7.18.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.7.18.mysql.tpl
new file mode 100644
index 0000000000..c23f15d0b5
--- /dev/null
+++ b/CRM/Upgrade/Incremental/sql/4.7.18.mysql.tpl
@@ -0,0 +1,53 @@
+{* file to handle db changes in 4.7.18 during upgrade *}
+
+-- CRM-20062 New counties of Kenya.
+SELECT @country_id := max(id) from civicrm_country where iso_code = "KE";
+INSERT IGNORE INTO civicrm_state_province (country_id, abbreviation, name) VALUES
+(@country_id, "01", "Baringo"),
+(@country_id, "02", "Bomet"),
+(@country_id, "03", "Bungoma"),
+(@country_id, "04", "Busia"),
+(@country_id, "05", "Elgeyo/Marakwet"),
+(@country_id, "06", "Embu"),
+(@country_id, "07", "Garissa"),
+(@country_id, "08", "Homa Bay"),
+(@country_id, "09", "Isiolo"),
+(@country_id, "10", "Kajiado"),
+(@country_id, "11", "Kakamega"),
+(@country_id, "12", "Kericho"),
+(@country_id, "13", "Kiambu"),
+(@country_id, "14", "Kilifi"),
+(@country_id, "15", "Kirinyaga"),
+(@country_id, "16", "Kisii"),
+(@country_id, "17", "Kisumu"),
+(@country_id, "18", "Kitui"),
+(@country_id, "19", "Kwale"),
+(@country_id, "20", "Laikipia"),
+(@country_id, "21", "Lamu"),
+(@country_id, "22", "Machakos"),
+(@country_id, "23", "Makueni"),
+(@country_id, "24", "Mandera"),
+(@country_id, "25", "Marsabit"),
+(@country_id, "26", "Meru"),
+(@country_id, "27", "Migori"),
+(@country_id, "28", "Mombasa"),
+(@country_id, "29", "Murang'a"),
+(@country_id, "30", "Nairobi City"),
+(@country_id, "31", "Nakuru"),
+(@country_id, "32", "Nandi"),
+(@country_id, "33", "Narok"),
+(@country_id, "34", "Nyamira"),
+(@country_id, "35", "Nyandarua"),
+(@country_id, "36", "Nyeri"),
+(@country_id, "37", "Samburu"),
+(@country_id, "38", "Siaya"),
+(@country_id, "39", "Taita/Taveta"),
+(@country_id, "40", "Tana River"),
+(@country_id, "41", "Tharaka-Nithi"),
+(@country_id, "42", "Trans Nzoia"),
+(@country_id, "43", "Turkana"),
+(@country_id, "44", "Uasin Gishu"),
+(@country_id, "45", "Vihiga"),
+(@country_id, "46", "Wajir"),
+(@country_id, "47", "West Pokot");
+
-- 
2.25.1