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;
}
