function DataModelImprovements_Change_Entity($custom_group_id, $contact_type) { $tableName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $custom_group_id, 'table_name'); CRM_Core_DAO::dropTriggers($tableName); echo "- Delete custom data set with id {$custom_group_id} for organistions that are not of type '{$contact_type}'\n"; $query = "DELETE cd\n FROM {$tableName} AS cd\n JOIN civicrm_contact AS c ON c.id=cd.entity_id\n WHERE c.contact_type!='{$contact_type}'"; CRM_Core_DAO::singleValueQuery($query); echo "- Set the custom data group to only extend individuals\n"; $query = "\n UPDATE civicrm_custom_group\n SET extends = '{$contact_type}'\n WHERE id = {$custom_group_id}"; CRM_Core_DAO::singleValueQuery($query); CRM_Core_DAO::triggerRebuild($tableName); }
static function alterFieldSQL(&$params, $indexExist = FALSE) { $sql = str_repeat(' ', 8); $sql .= "ALTER TABLE {$params['table_name']}"; // lets suppress the required flag, since that can cause sql issue $params['required'] = FALSE; switch ($params['operation']) { case 'add': $separator = "\n"; $prefix = "ADD "; $sql .= self::buildFieldSQL($params, $separator, "ADD COLUMN "); $separator = ",\n"; $sql .= self::buildPrimaryKeySQL($params, $separator, "ADD PRIMARY KEY "); $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX "); $sql .= self::buildForeignKeySQL($params, $separator, "ADD ", $params['table_name']); break; case 'modify': $separator = "\n"; $prefix = "MODIFY "; $sql .= self::buildFieldSQL($params, $separator, $prefix); $separator = ",\n"; $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ", $indexExist); break; case 'delete': $sql .= " DROP COLUMN `{$params['name']}`"; if (CRM_Utils_Array::value('primary', $params)) { $sql .= ", DROP PRIMARY KEY"; } if (CRM_Utils_Array::value('fk_table_name', $params)) { $sql .= ", DROP FOREIGN KEY FK_{$params['fkName']}"; } break; } // CRM-7007: do not i18n-rewrite this query $dao = CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE); $dao->free(); $config = CRM_Core_Config::singleton(); if ($config->logging) { // logging support: if we’re adding a column (but only then!) make sure the potential relevant log table gets a column as well if ($params['operation'] == 'add') { $logging = new CRM_Logging_Schema(); $logging->fixSchemaDifferencesFor($params['table_name'], array($params['name'])); } elseif ($params['operation'] == 'delete') { // CRM-7293: if we’re dropping a column – rebuild triggers CRM_Core_DAO::triggerRebuild($params['table_name']); } } return TRUE; }
static function rebuildMenuAndCaches($triggerRebuild = FALSE, $sessionReset = FALSE) { $config = CRM_Core_Config::singleton(); $config->clearModuleList(); // also cleanup all caches $config->cleanupCaches($sessionReset || CRM_Utils_Request::retrieve('sessionReset', 'Boolean', CRM_Core_DAO::$_nullObject, FALSE, 0, 'GET')); CRM_Core_Menu::store(); // also reset navigation CRM_Core_BAO_Navigation::resetNavigation(); // also cleanup module permissions $config->cleanupPermissions(); // also rebuild word replacement cache CRM_Core_BAO_WordReplacement::rebuild(); CRM_Core_BAO_Setting::updateSettingsFromMetaData(); CRM_Core_Resources::singleton()->resetCacheCode(); // also rebuild triggers if requested explicitly if ($triggerRebuild || CRM_Utils_Request::retrieve('triggerRebuild', 'Boolean', CRM_Core_DAO::$_nullObject, FALSE, 0, 'GET')) { CRM_Core_DAO::triggerRebuild(); } CRM_Core_DAO_AllCoreTables::reinitializeCache(TRUE); CRM_Core_ManagedEntities::singleton(TRUE)->reconcile(); }
/** * Helper for testing timestamp manipulation. * * Create a contact and perform a series of steps with it; after each * step, ensure that the contact's modified_date has increased. * * @param array $callbacks * ($name => $callable). */ public function _testTimestamps($callbacks) { CRM_Core_DAO::triggerRebuild(); $contactId = $this->individualCreate(); $origTimestamps = CRM_Contact_BAO_Contact::getTimestamps($contactId); $this->assertRegexp('/^\\d\\d\\d\\d-\\d\\d-\\d\\d /', $origTimestamps['created_date']); $this->assertRegexp('/^\\d\\d\\d\\d-\\d\\d-\\d\\d /', $origTimestamps['modified_date']); $this->assertTrue($origTimestamps['created_date'] <= $origTimestamps['modified_date']); $prevTimestamps = $origTimestamps; foreach ($callbacks as $callbackName => $callback) { sleep(1); // advance clock by 1 second to ensure timestamps change $callback($contactId); $newTimestamps = CRM_Contact_BAO_Contact::getTimestamps($contactId); $this->assertRegexp('/^\\d\\d\\d\\d-\\d\\d-\\d\\d /', $newTimestamps['created_date'], "Malformed created_date (after {$callbackName})"); $this->assertRegexp('/^\\d\\d\\d\\d-\\d\\d-\\d\\d /', $newTimestamps['modified_date'], "Malformed modified_date (after {$callbackName})"); $this->assertEquals($origTimestamps['created_date'], $newTimestamps['created_date'], "Changed created_date (after {$callbackName})"); $this->assertTrue($prevTimestamps['modified_date'] < $newTimestamps['modified_date'], "Misordered modified_date (after {$callbackName})"); $prevTimestamps = $newTimestamps; } $this->contactDelete($contactId); }
/** * @param array $params * @param bool $indexExist * @param bool $triggerRebuild * * @return bool */ public static function alterFieldSQL(&$params, $indexExist = FALSE, $triggerRebuild = TRUE) { $sql = str_repeat(' ', 8); $sql .= "ALTER TABLE {$params['table_name']}"; // lets suppress the required flag, since that can cause sql issue $params['required'] = FALSE; switch ($params['operation']) { case 'add': $separator = "\n"; $prefix = "ADD "; $sql .= self::buildFieldSQL($params, $separator, "ADD COLUMN "); $separator = ",\n"; $sql .= self::buildPrimaryKeySQL($params, $separator, "ADD PRIMARY KEY "); $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX "); $sql .= self::buildForeignKeySQL($params, $separator, "ADD ", $params['table_name']); break; case 'modify': $separator = "\n"; $prefix = "MODIFY "; $sql .= self::buildFieldSQL($params, $separator, $prefix); $separator = ",\n"; $sql .= self::buildSearchIndexSQL($params, $separator, "ADD INDEX ", $indexExist); break; case 'delete': $sql .= " DROP COLUMN `{$params['name']}`"; if (!empty($params['primary'])) { $sql .= ", DROP PRIMARY KEY"; } if (!empty($params['fk_table_name'])) { $sql .= ", DROP FOREIGN KEY FK_{$params['fkName']}"; } break; } // CRM-7007: do not i18n-rewrite this query $dao = CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, FALSE, FALSE); $dao->free(); $config = CRM_Core_Config::singleton(); if ($config->logging) { // CRM-16717 not sure why this was originally limited to add. // For example custom tables can have field length changes - which need to flow through to logging. // Are there any modifies we DON'T was to call this function for (& shouldn't it be clever enough to cope?) if ($params['operation'] == 'add' || $params['operation'] == 'modify') { $logging = new CRM_Logging_Schema(); $logging->fixSchemaDifferencesFor($params['table_name'], array(trim($prefix) => array($params['name'])), FALSE); } } if ($triggerRebuild) { CRM_Core_DAO::triggerRebuild($params['table_name']); } return TRUE; }
/** * @param bool $rebuildTrigger */ public function fixSchemaDifferencesForAll($rebuildTrigger = FALSE) { $diffs = array(); foreach ($this->tables as $table) { if (empty($this->logs[$table])) { $this->createLogTableFor($table); } else { $diffs[$table] = $this->columnsWithDiffSpecs($table, "log_{$table}"); } } foreach ($diffs as $table => $cols) { $this->fixSchemaDifferencesFor($table, $cols, FALSE); } if ($rebuildTrigger) { // invoke the meta trigger creation call CRM_Core_DAO::triggerRebuild($table); } }
/** * @param bool $triggerRebuild * @param bool $sessionReset * * @throws Exception */ public static function rebuildMenuAndCaches($triggerRebuild = FALSE, $sessionReset = FALSE) { $config = CRM_Core_Config::singleton(); $config->clearModuleList(); // also cleanup all caches $config->cleanupCaches($sessionReset || CRM_Utils_Request::retrieve('sessionReset', 'Boolean', CRM_Core_DAO::$_nullObject, FALSE, 0, 'GET')); CRM_Core_Menu::store(); // also reset navigation CRM_Core_BAO_Navigation::resetNavigation(); // also cleanup module permissions $config->cleanupPermissions(); // rebuild word replacement cache - pass false to prevent operations redundant with this fn CRM_Core_BAO_WordReplacement::rebuild(FALSE); Civi::service('settings_manager')->flush(); // Clear js caches CRM_Core_Resources::singleton()->flushStrings()->resetCacheCode(); CRM_Case_XMLRepository::singleton(TRUE); // also rebuild triggers if requested explicitly if ($triggerRebuild || CRM_Utils_Request::retrieve('triggerRebuild', 'Boolean', CRM_Core_DAO::$_nullObject, FALSE, 0, 'GET')) { CRM_Core_DAO::triggerRebuild(); } CRM_Core_DAO_AllCoreTables::reinitializeCache(TRUE); CRM_Core_ManagedEntities::singleton(TRUE)->reconcile(); //CRM-16257 update Config.IDS.ini might be an old copy CRM_Core_IDS::createConfigFile(TRUE); }
/** * Delete content / rows of a custom table specific to a subtype for a given custom-group. * This function currently works for contact subtypes only and could be later improved / genralized * to work for other subtypes as well. * * @param int $gID * Custom group id. * @param array $subtypes * List of subtypes related to which entry is to be removed. * * @return void */ public static function deleteCustomRowsOfSubtype($gID, $subtypes = array()) { if (!$gID or empty($subtypes)) { return FALSE; } $tableName = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $gID, 'table_name'); // drop triggers CRM-13587 CRM_Core_DAO::dropTriggers($tableName); $subtypeClause = array(); foreach ($subtypes as $subtype) { $subtype = CRM_Utils_Type::escape($subtype, 'String'); $subtypeClause[] = "civicrm_contact.contact_sub_type LIKE '%" . CRM_Core_DAO::VALUE_SEPARATOR . $subtype . CRM_Core_DAO::VALUE_SEPARATOR . "%'"; } $subtypeClause = implode(' OR ', $subtypeClause); $query = "DELETE custom.*\nFROM {$tableName} custom\nINNER JOIN civicrm_contact ON civicrm_contact.id = custom.entity_id\nWHERE ({$subtypeClause})"; CRM_Core_DAO::singleValueQuery($query); // rebuild triggers CRM-13587 CRM_Core_DAO::triggerRebuild($tableName); }
/** * Rebuild multilingual indices, views and triggers (useful for upgrades) * * @param $locales array locales to be rebuilt * @param $version string version of schema structure to use * * @return void */ static function rebuildMultilingualSchema($locales, $version = NULL) { if ($version) { $latest = self::getLatestSchema($version); require_once "CRM/Core/I18n/SchemaStructure_{$latest}.php"; $class = "CRM_Core_I18n_SchemaStructure_{$latest}"; } else { $class = 'CRM_Core_I18n_SchemaStructure'; } $indices =& $class::indices(); $tables =& $class::tables(); $queries = array(); $dao = new CRM_Core_DAO(); // get all of the already existing indices $existing = array(); foreach (array_keys($indices) as $table) { $existing[$table] = array(); $dao->query("SHOW INDEX FROM {$table}", FALSE); while ($dao->fetch()) { if (preg_match('/_[a-z][a-z]_[A-Z][A-Z]$/', $dao->Key_name)) { $existing[$table][] = $dao->Key_name; } } } // from all of the CREATE INDEX queries fetch the ones creating missing indices foreach ($locales as $locale) { foreach (array_keys($indices) as $table) { $allQueries = self::createIndexQueries($locale, $table, $class); foreach ($allQueries as $name => $query) { if (!in_array("{$name}_{$locale}", $existing[$table])) { $queries[] = $query; } } } } // rebuild views foreach ($locales as $locale) { foreach ($tables as $table) { $queries[] = self::createViewQuery($locale, $table, $dao, $class); } } // rebuild triggers $last = array_pop($locales); foreach ($queries as $query) { $dao->query($query, FALSE); } // invoke the meta trigger creation call CRM_Core_DAO::triggerRebuild(); }
function postProcess() { $values = $this->controller->exportValues($this->_name); // save field values foreach ($this->config_fields as $key => $value) { if (array_key_exists($this->domainToString($value[0]), $values)) { CRM_Core_BAO_Setting::setItem($values[$this->domainToString($value[0])], 'SEPA Direct Debit Preferences', $this->domainToString($value[0])); } } // save general config options: // default creditor CRM_Core_BAO_Setting::setItem($values['batching_default_creditor'], 'SEPA Direct Debit Preferences', 'batching_default_creditor'); // mandate modification $allow_mandate_modification = empty($values['allow_mandate_modification']) ? '0' : '1'; CRM_Core_BAO_Setting::setItem($allow_mandate_modification, 'SEPA Direct Debit Preferences', 'allow_mandate_modification'); CRM_Core_BAO_Setting::setItem(isset($values['exclude_weekends']) ? "1" : "0", 'SEPA Direct Debit Preferences', 'exclude_weekends'); $session = CRM_Core_Session::singleton(); $session->setStatus(ts("Settings successfully saved")); CRM_Core_DAO::triggerRebuild(); $session->replaceUserContext(CRM_Utils_System::url('civicrm/admin/setting/sepa')); }
/** * Used for generating the schema and data * * Should be called whenever the extension has the chosen * fields saved or via Cron job. * * Called by the API gendata. */ function sumfields_gen_data(&$returnValues) { // generate_schema_and_data variable can be set to any of the following: // // NULL It has never been run, no need to run // scheduled:YYYY-MM-DD HH:MM:SS -> it should be run // running:YYYY-MM-DD HH:MM:SS -> it is currently running, and started at the given date/time // success:YYYY-MM-DD HH:MM:SS -> It completed successfully on the last run at the given date/time // failed:YYYY-MM-DD HH:MM:SS -> It failed on the last run at the given date/time // // We will run if we are scheduled to run OR if the fiscal year has turned // and we haven't yet run this fiscal year $status = $new_status = sumfields_get_setting('generate_schema_and_data', FALSE); $date = date('Y-m-d H:i:s'); $exception = FALSE; $status_name = NULL; $status_date = NULL; if (preg_match('/^([a-z]+):([0-9 -:]+)$/', $status, $matches)) { $status_name = $matches[1]; $status_date = $matches[2]; // Check if the fiscal year has turned over since we last ran. // (also, only attempt to do a new fiscal year run if the last run // was successful to avoid loops of failed runs). if ($status_name == 'success') { $fiscal_dates = sumfields_get_fiscal_dates(); $ts_fiscal_year_begin = strtotime($fiscal_dates['%current_fiscal_year_begin']); $ts_last_run = strtotime($status_date); if ($ts_fiscal_year_begin > $ts_last_run) { // We need to re-generate totals because the fiscal year has changed. $status_name = 'scheduled'; } } } if ($status_name == 'scheduled') { $new_status = 'running:' . $date; sumfields_save_setting('generate_schema_and_data', $new_status); // Check to see if the new_active_fields setting is set. This means we have to alter the fields // from the current setting. $new_active_fields = sumfields_get_setting('new_active_fields', NULL); if (!is_null($new_active_fields)) { if (!sumfields_alter_table()) { // If we fail to properly alter the table, bail and record that we had an error. $date = date('Y-m-d H:i:s'); $new_status = 'failed:' . $date; $exception = TRUE; } else { // Set new active fields to NULL to indicate that they no longer // need to be updated sumfields_save_setting('new_active_fields', NULL); } } if (!$exception) { if (sumfields_generate_data_based_on_current_data()) { CRM_Core_DAO::triggerRebuild(); $date = date('Y-m-d H:i:s'); $new_status = 'success:' . $date; } else { $date = date('Y-m-d H:i:s'); $new_status = 'fail:' . $date; $exception = TRUE; } } } $returnValues = array("Original Status: {$status}, New Status: {$new_status}"); sumfields_save_setting('generate_schema_and_data', $new_status); if (!$exception) { return FALSE; } return TRUE; }
/** * Add missing (potentially specified) log table columns for the given table. * * param $table string name of the relevant table * param $cols mixed array of columns to add or null (to check for the missing columns) */ function fixSchemaDifferencesFor($table, $cols = NULL) { if (empty($this->logs[$table])) { $this->createLogTableFor($table); return; } if (is_null($cols)) { $cols = array_diff($this->columnsOf($table), $this->columnsOf("log_{$table}")); } if (empty($cols)) { return; } // use the relevant lines from CREATE TABLE to add colums to the log table $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE {$table}"); $dao->fetch(); $create = explode("\n", $dao->Create_Table); foreach ($cols as $col) { $line = substr(array_pop(preg_grep("/^ `{$col}` /", $create)), 0, -1); CRM_Core_DAO::executeQuery("ALTER TABLE `{$this->db}`.log_{$table} ADD {$line}"); } // invoke the meta trigger creation call CRM_Core_DAO::triggerRebuild($table); }
static function rebuildMenuAndCaches($triggerRebuild = FALSE, $sessionReset = FALSE) { $config = CRM_Core_Config::singleton(); $config->clearModuleList(); CRM_Core_Menu::store(); // also reset navigation CRM_Core_BAO_Navigation::resetNavigation(); // also cleanup all caches $config->cleanupCaches($sessionReset || CRM_Utils_Request::retrieve('sessionReset', 'Boolean', CRM_Core_DAO::$_nullObject, FALSE, 0, 'GET')); // also rebuild triggers if requested explicitly if ($triggerRebuild || CRM_Utils_Request::retrieve('triggerRebuild', 'Boolean', CRM_Core_DAO::$_nullObject, FALSE, 0, 'GET')) { CRM_Core_DAO::triggerRebuild(); } CRM_Core_ManagedEntities::singleton(TRUE)->reconcile(); }
/** * (Queue Task Callback) */ static function task_4_2_x_runSql(CRM_Queue_TaskContext $ctx, $rev) { $upgrade = new CRM_Upgrade_Form(); $upgrade->processSQL($rev); // now rebuild all the triggers // CRM-9716 CRM_Core_DAO::triggerRebuild(); return TRUE; }
public function upgradeBundle() { //$this->ctx->log->info('Applying update 0999'); $this->executeCustomDataFile('xml/option_group_install.xml'); //$this->ctx->log->info('Applying update 1101'); $this->executeCustomDataFile('xml/1101_departments.xml'); //$this->ctx->log->info('Applying update 1105'); if (!CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'hrjc_pension_type', 'name')) { $this->executeCustomDataFile('xml/1105_pension_type.xml'); } /* DEPRECATED: //$this->ctx->log->info('Applying update 1201'); //get all fields of Custom Group "HRJobContract_Summary" $params = array( 'custom_group_id' => 'HRJobContract_Summary', ); $results = civicrm_api3('CustomField', 'get', $params); foreach ($results['values'] as $result) { $result['is_view'] = 0; // make the field editable civicrm_api3('CustomField', 'create', $result); } //disable trigger CRM_Core_DAO::triggerRebuild();*/ //$this->ctx->log->info('Applying update 1202'); //Add job import navigation menu $weight = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Navigation', 'Import Contacts', 'weight', 'name'); $contactNavId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Navigation', 'Contacts', 'id', 'name'); $importJobNavigation = new CRM_Core_DAO_Navigation(); $params = array('domain_id' => CRM_Core_Config::domainID(), 'label' => ts('Import Jobs'), 'name' => 'jobImport', 'url' => null, 'parent_id' => $contactNavId, 'weight' => $weight + 1, 'permission' => 'access HRJobs', 'separator' => 1, 'is_active' => 1); $importJobNavigation->copyValues($params); $importJobNavigation->save(); //$this->ctx->log->info('Applying update 1400'); $i = 4; $params = array('option_group_id' => 'hrjc_contract_type', 'name' => 'Employee_Permanent', 'weight' => $i, 'label' => 'Employee - Permanent', 'value' => 'Employee - Permanent'); civicrm_api3('OptionValue', 'create', $params); /* DEPRECATED: $empoption_id = civicrm_api3('OptionValue', 'getsingle', array('return' => "id",'option_group_id' => 'hrjc_contract_type', 'name' => "Employee")); civicrm_api3('OptionValue', 'create',array('id' => $empoption_id['id'],'name' => "Employee_Temporary",'label' => 'Employee - Temporary', 'value' => 'Employee - Temporary')); */ $optionGroupID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'hrjc_contract_type', 'id', 'name'); foreach (array('Intern', 'Trustee', 'Volunteer') as $opName) { $i++; CRM_Core_DAO::executeQuery("UPDATE civicrm_option_value SET weight = {$i} WHERE name = '{$opName}' and option_group_id = {$optionGroupID}"); } $optionGroupId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'hrjc_hours_type', 'id', 'name'); //change value of stored hours type CRM_Core_DAO::executeQuery("UPDATE civicrm_hrjobcontract_hour SET hours_type = CASE hours_type WHEN 'full' THEN 8 WHEN 'part' THEN 4 WHEN 'casual' THEN 0 ELSE hours_type END"); //$this->ctx->log->info('Applying update 1402'); //Upgrade for HR-394 and HR-395 $optionGroupID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'hrjc_region', 'id', 'name'); if (!$optionGroupID) { $params = array('name' => 'hrjc_region', 'title' => 'Region', 'is_active' => 1); $newRegionGroupResult = civicrm_api3('OptionGroup', 'create', $params); } // Migrate old 'hrjob_region' option values into new 'hrjc_region': if (!empty($newRegionGroupResult['id'])) { $oldRegionGroup = civicrm_api3('OptionGroup', 'get', array('sequential' => 1, 'name' => "hrjob_region")); if (!empty($oldRegionGroup['id'])) { $oldRegionsResult = civicrm_api3('OptionValue', 'get', array('sequential' => 1, 'option_group_id' => $oldRegionGroup['id'])); foreach ($oldRegionsResult['values'] as $oldRegion) { $newRegion = $oldRegion; unset($newRegion['id']); $newRegion['option_group_id'] = $newRegionGroupResult['id']; civicrm_api3('OptionValue', 'create', $newRegion); } } } $result = CRM_Core_DAO::executeQuery('SELECT * FROM civicrm_hrjobcontract_hour ORDER BY id ASC'); while ($result->fetch()) { $fteFraction = CRM_Hrjobcontract_Upgrader::decToFraction($result->hours_fte); CRM_Core_DAO::executeQuery("UPDATE civicrm_hrjobcontract_hour SET fte_num={$fteFraction[0]} , fte_denom={$fteFraction[1]} WHERE id = {$result->id}"); } //$this->ctx->log->info('Applying update 1404'); $optionGroupId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'hrjc_pay_grade', 'id', 'name'); $sql = "UPDATE civicrm_option_value SET civicrm_option_value.value = CASE civicrm_option_value.label WHEN 'Paid' THEN 1 WHEN 'Unpaid' THEN 0 END WHERE option_group_id = {$optionGroupId}"; CRM_Core_DAO::executeQuery($sql); CRM_Core_DAO::triggerRebuild(); $reportTemplateOptionGroup = CRM_Core_DAO::executeQuery("SELECT * FROM civicrm_option_group WHERE name='report_template' AND is_active = 1 LIMIT 1"); if ($reportTemplateOptionGroup->fetch()) { $hrjobcontractReportTemplateQuery = "INSERT INTO `civicrm_option_value` (`option_group_id`, `label`, `value`, `name`, `grouping`, `filter`, `is_default`, `weight`, `description`, `is_optgroup`, `is_reserved`, `is_active`, `component_id`, `domain_id`, `visibility_id`) VALUES\n (%1, 'JobContract Revision Report', 'hrjobcontract/summary', 'CRM_Hrjobcontract_Report_Form_Summary', NULL, 0, 0, 54, 'JobContract Revision Report', 0, 0, 1, NULL, NULL, NULL)"; $hrjobcontractReportTemplateParams = array(1 => array($reportTemplateOptionGroup->id, 'Integer')); CRM_Core_DAO::executeQuery($hrjobcontractReportTemplateQuery, $hrjobcontractReportTemplateParams); } CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_hrjobcontract_hour` ADD `location_type` INT(3) NULL DEFAULT NULL AFTER `id`"); CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_hrjobcontract_hour` CHANGE `location_type` `location_standard_hours` INT(3) NULL DEFAULT NULL"); CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS civicrm_hrhours_location"); CRM_Core_DAO::executeQuery("\n CREATE TABLE IF NOT EXISTS `civicrm_hrhours_location` (\n `id` int(10) unsigned NOT NULL,\n `location` varchar(63) DEFAULT NULL,\n `standard_hours` int(4) DEFAULT NULL,\n `periodicity` varchar(63) DEFAULT NULL,\n `is_active` tinyint(4) DEFAULT '1'\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1\n "); CRM_Core_DAO::executeQuery("\n INSERT INTO `civicrm_hrhours_location` (`id`, `location`, `standard_hours`, `periodicity`, `is_active`) VALUES\n (1, 'Head office', 40, 'Week', 1),\n (2, 'Other office', 8, 'Day', 1),\n (3, 'Small office', 36, 'Week', 1)\n "); $optionGroupID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'hrjc_revision_change_reason', 'id', 'name'); if (!$optionGroupID) { $params = array('name' => 'hrjc_revision_change_reason', 'title' => 'Job Contract Revision Change Reason', 'is_active' => 1, 'is_reserved' => 1); civicrm_api3('OptionGroup', 'create', $params); $optionsValue = array(1 => 'Reason 1', 2 => 'Reason 2', 3 => 'Reason 3'); foreach ($optionsValue as $key => $value) { $opValueParams = array('option_group_id' => 'hrjc_revision_change_reason', 'name' => $value, 'label' => $value, 'value' => $key); civicrm_api3('OptionValue', 'create', $opValueParams); } } CRM_Core_DAO::executeQuery("\n ALTER TABLE `civicrm_hrjobcontract_pay` ADD `annual_benefits` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `pay_is_auto_est`, ADD `annual_deductions` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `annual_benefits`\n "); CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS civicrm_hrhours_location"); CRM_Core_DAO::executeQuery("\n CREATE TABLE IF NOT EXISTS `civicrm_hrhours_location` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `location` varchar(63) DEFAULT NULL,\n `standard_hours` int(4) DEFAULT NULL,\n `periodicity` varchar(63) DEFAULT NULL,\n `is_active` tinyint(4) DEFAULT '1',\n PRIMARY KEY(id)\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1\n "); CRM_Core_DAO::executeQuery("\n INSERT INTO `civicrm_hrhours_location` (`id`, `location`, `standard_hours`, `periodicity`, `is_active`) VALUES\n (1, 'Head office', 40, 'Week', 1),\n (2, 'Other office', 8, 'Day', 1),\n (3, 'Small office', 36, 'Week', 1)\n "); // pay_cycle: $optionGroupID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'hrjc_pay_cycle', 'id', 'name'); if (!$optionGroupID) { $params = array('name' => 'hrjc_pay_cycle', 'title' => 'Job Contract Pay Cycle', 'is_active' => 1, 'is_reserved' => 1); civicrm_api3('OptionGroup', 'create', $params); $optionsValue = array(1 => 'Weekly', 2 => 'Monthly'); foreach ($optionsValue as $key => $value) { $opValueParams = array('option_group_id' => 'hrjc_pay_cycle', 'name' => $value, 'label' => $value, 'value' => $key); civicrm_api3('OptionValue', 'create', $opValueParams); } } // benefit_name: $optionGroupID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'hrjc_benefit_name', 'id', 'name'); if (!$optionGroupID) { $params = array('name' => 'hrjc_benefit_name', 'title' => 'Job Contract Benefit Name', 'is_active' => 1, 'is_reserved' => 1); civicrm_api3('OptionGroup', 'create', $params); $optionsValue = array(1 => 'Bike', 2 => 'Medical'); foreach ($optionsValue as $key => $value) { $opValueParams = array('option_group_id' => 'hrjc_benefit_name', 'name' => $value, 'label' => $value, 'value' => $key); civicrm_api3('OptionValue', 'create', $opValueParams); } } // benefit_type: $optionGroupID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'hrjc_benefit_type', 'id', 'name'); if (!$optionGroupID) { $params = array('name' => 'hrjc_benefit_type', 'title' => 'Job Contract Benefit Type', 'is_active' => 1, 'is_reserved' => 1); civicrm_api3('OptionGroup', 'create', $params); $optionsValue = array(1 => 'Fixed', 2 => '%'); foreach ($optionsValue as $key => $value) { $opValueParams = array('option_group_id' => 'hrjc_benefit_type', 'name' => $value, 'label' => $value, 'value' => $key); civicrm_api3('OptionValue', 'create', $opValueParams); } } // deduction_name: $optionGroupID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'hrjc_deduction_name', 'id', 'name'); if (!$optionGroupID) { $params = array('name' => 'hrjc_deduction_name', 'title' => 'Job Contract Deduction Name', 'is_active' => 1, 'is_reserved' => 1); civicrm_api3('OptionGroup', 'create', $params); $optionsValue = array(1 => 'Bike', 2 => 'Medical'); foreach ($optionsValue as $key => $value) { $opValueParams = array('option_group_id' => 'hrjc_deduction_name', 'name' => $value, 'label' => $value, 'value' => $key); civicrm_api3('OptionValue', 'create', $opValueParams); } } // deduction_type: $optionGroupID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'hrjc_deduction_type', 'id', 'name'); if (!$optionGroupID) { $params = array('name' => 'hrjc_deduction_type', 'title' => 'Job Contract Deduction Type', 'is_active' => 1, 'is_reserved' => 1); civicrm_api3('OptionGroup', 'create', $params); $optionsValue = array(1 => 'Fixed', 2 => '%'); foreach ($optionsValue as $key => $value) { $opValueParams = array('option_group_id' => 'hrjc_deduction_type', 'name' => $value, 'label' => $value, 'value' => $key); civicrm_api3('OptionValue', 'create', $opValueParams); } } CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_hrjobcontract_pay` ADD `pay_cycle` INT(4) DEFAULT NULL AFTER `annual_deductions`, ADD `pay_per_cycle_gross` DECIMAL(10,2) DEFAULT NULL AFTER `pay_cycle`, ADD `pay_per_cycle_net` DECIMAL(10,2) DEFAULT NULL AFTER `pay_per_cycle_gross`"); CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_hrjobcontract_revision` ADD `editor_uid` INT(10) NULL DEFAULT NULL AFTER `jobcontract_id`"); CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_hrjobcontract` ADD `deleted` INT(2) UNSIGNED NOT NULL DEFAULT '0'"); CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm_hrjobcontract_revision` ADD `deleted` INT(2) UNSIGNED NOT NULL DEFAULT '0'"); // Navigation items: CRM_Core_DAO::executeQuery("DELETE FROM `civicrm_navigation` WHERE name IN ('hoursType', 'pay_scale', 'hours_location', 'hrjc_contract_type', 'hrjc_location', 'hrjc_pay_cycle', 'hrjc_benefit_name', 'hrjc_benefit_type', 'hrjc_deduction_name', 'hrjc_deduction_type', 'hrjc_health_provider', 'hrjc_life_provider', 'hrjc_pension_type', 'hrjc_revision_change_reason')"); // Add administer options $administerNavId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_Navigation', 'Dropdown Options', 'id', 'name'); $jobContractOptionsMenuTree = array(array('label' => ts('Hours Types'), 'name' => 'hoursType', 'url' => 'civicrm/hour/editoption', 'permission' => 'administer CiviCRM', 'parent_id' => $administerNavId), array('label' => ts('Job Contract Pay Scale'), 'name' => 'pay_scale', 'url' => 'civicrm/pay_scale', 'permission' => 'administer CiviCRM', 'parent_id' => $administerNavId), array('label' => ts('Job Contract Hours/Location'), 'name' => 'hours_location', 'url' => 'civicrm/hours_location', 'permission' => 'administer CiviCRM', 'parent_id' => $administerNavId)); // hrjc_contract_type: $result = civicrm_api3('OptionGroup', 'get', array('sequential' => 1, 'name' => "hrjc_contract_type")); if (!empty($result['id'])) { $jobContractOptionsMenuTree[] = array('label' => ts('Contract Type'), 'name' => 'hrjc_contract_type', 'url' => 'civicrm/admin/options?gid=' . $result['id'], 'permission' => 'administer CiviCRM', 'parent_id' => $administerNavId); } // hrjc_location: $result = civicrm_api3('OptionGroup', 'get', array('sequential' => 1, 'name' => "hrjc_location")); if (!empty($result['id'])) { $jobContractOptionsMenuTree[] = array('label' => ts('Normal place of work'), 'name' => 'hrjc_location', 'url' => 'civicrm/admin/options?gid=' . $result['id'], 'permission' => 'administer CiviCRM', 'parent_id' => $administerNavId); // Migrating old 'hrjob_location' option values into new 'hrjc_location': $oldLocationGroup = civicrm_api3('OptionGroup', 'get', array('sequential' => 1, 'name' => "hrjob_location")); if (!empty($oldLocationGroup['id'])) { $oldLocationsResult = civicrm_api3('OptionValue', 'get', array('sequential' => 1, 'option_group_id' => $oldLocationGroup['id'])); foreach ($oldLocationsResult['values'] as $oldLocation) { $newLocationResult = civicrm_api3('OptionValue', 'get', array('sequential' => 1, 'option_group_id' => $result['id'], 'value' => $oldLocation['value'])); if (!empty($newLocationResult['id'])) { continue; } $newLocation = $oldLocation; unset($newLocation['id']); $newLocation['option_group_id'] = $result['id']; civicrm_api3('OptionValue', 'create', $newLocation); } } } // hrjc_pay_cycle: $result = civicrm_api3('OptionGroup', 'get', array('sequential' => 1, 'name' => "hrjc_pay_cycle")); if (!empty($result['id'])) { $jobContractOptionsMenuTree[] = array('label' => ts('Pay cycle'), 'name' => 'hrjc_pay_cycle', 'url' => 'civicrm/admin/options?gid=' . $result['id'], 'permission' => 'administer CiviCRM', 'parent_id' => $administerNavId); } // hrjc_benefit_name: $result = civicrm_api3('OptionGroup', 'get', array('sequential' => 1, 'name' => "hrjc_benefit_name")); if (!empty($result['id'])) { $jobContractOptionsMenuTree[] = array('label' => ts('Benefits'), 'name' => 'hrjc_benefit_name', 'url' => 'civicrm/admin/options?gid=' . $result['id'], 'permission' => 'administer CiviCRM', 'parent_id' => $administerNavId); } // hrjc_benefit_type: $result = civicrm_api3('OptionGroup', 'get', array('sequential' => 1, 'name' => "hrjc_benefit_type")); if (!empty($result['id'])) { $jobContractOptionsMenuTree[] = array('label' => ts('Benefit type'), 'name' => 'hrjc_benefit_type', 'url' => 'civicrm/admin/options?gid=' . $result['id'], 'permission' => 'administer CiviCRM', 'parent_id' => $administerNavId); } // hrjc_deduction_name: $result = civicrm_api3('OptionGroup', 'get', array('sequential' => 1, 'name' => "hrjc_deduction_name")); if (!empty($result['id'])) { $jobContractOptionsMenuTree[] = array('label' => ts('Deductions'), 'name' => 'hrjc_deduction_name', 'url' => 'civicrm/admin/options?gid=' . $result['id'], 'permission' => 'administer CiviCRM', 'parent_id' => $administerNavId); } // hrjc_deduction_type: $result = civicrm_api3('OptionGroup', 'get', array('sequential' => 1, 'name' => "hrjc_deduction_type")); if (!empty($result['id'])) { $jobContractOptionsMenuTree[] = array('label' => ts('Deduction type'), 'name' => 'hrjc_deduction_type', 'url' => 'civicrm/admin/options?gid=' . $result['id'], 'permission' => 'administer CiviCRM', 'parent_id' => $administerNavId); } // hrjc_pension_type: $result = civicrm_api3('OptionGroup', 'get', array('sequential' => 1, 'name' => "hrjc_pension_type")); if (!empty($result['id'])) { $jobContractOptionsMenuTree[] = array('label' => ts('Pension provider type'), 'name' => 'hrjc_pension_type', 'url' => 'civicrm/admin/options?gid=' . $result['id'], 'permission' => 'administer CiviCRM', 'parent_id' => $administerNavId); } // hrjc_revision_change_reason: $result = civicrm_api3('OptionGroup', 'get', array('sequential' => 1, 'name' => "hrjc_revision_change_reason")); if (!empty($result['id'])) { $jobContractOptionsMenuTree[] = array('label' => ts('Reason for change'), 'name' => 'hrjc_revision_change_reason', 'url' => 'civicrm/admin/options?gid=' . $result['id'], 'permission' => 'administer CiviCRM', 'parent_id' => $administerNavId); } foreach ($jobContractOptionsMenuTree as $key => $menuItems) { $menuItems['is_active'] = 1; CRM_Core_BAO_Navigation::add($menuItems); } CRM_Core_BAO_Navigation::resetNavigation(); // Delete old HRJob Option Groups: CRM_Core_DAO::executeQuery("DELETE FROM civicrm_option_group WHERE name IN ('hrjob_contract_type',\n 'hrjob_department',\n 'hrjob_health_provider',\n 'hrjob_hours_type',\n 'hrjob_level_type',\n 'hrjob_life_provider',\n 'hrjob_pay_grade',\n 'hrjob_pay_scale',\n 'hrjob_pension_type',\n 'hrjob_region',\n 'hrjob_location')"); $this->upgrade_1001(); $this->upgrade_1002(); $this->upgrade_1003(); $this->upgrade_1004(); $this->upgrade_1005(); $this->upgrade_1006(); $this->upgrade_1008(); $this->upgrade_1009(); }
public function upgrade_1404() { $this->ctx->log->info('Applying update 1404'); $optionGroupId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', 'hrjob_pay_grade', 'id', 'name'); $sql = "UPDATE civicrm_option_value SET civicrm_option_value.value = CASE civicrm_option_value.value WHEN 'paid' THEN 1 WHEN 'unpaid' THEN 0 ELSE NULL END WHERE option_group_id = {$optionGroupId}"; CRM_Core_DAO::executeQuery($sql); if (!CRM_Core_DAO::checkFieldExists('civicrm_hrjob_pay', 'is_paid')) { CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_hrjob_pay ADD COLUMN is_paid int unsigned DEFAULT 0 COMMENT "Paid, Unpaid, etc." AFTER pay_scale'); CRM_Core_DAO::executeQuery("UPDATE civicrm_hrjob_pay SET is_paid = CASE pay_grade WHEN 'paid' THEN 1 WHEN 'unpaid' THEN 0 ELSE NULL END"); } if (CRM_Core_DAO::checkFieldExists('civicrm_hrjob_pay', 'pay_grade')) { CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_hrjob_pay DROP COLUMN pay_grade'); } CRM_Core_DAO::triggerRebuild(); return TRUE; }