function verifyPreDBState(&$errorMessage)
{
$latestVer = CRM_Utils_System::version();
$errorMessage = ts('Pre-condition failed for upgrade to %1.', array(1 => $latestVer));
// check table, if the db is 3.1
if (CRM_Core_DAO::checkTableExists('civicrm_acl_contact_cache')) {
$errorMessage = ts("Database check failed - it looks like you have already upgraded to the latest version (v%1) of the database. OR If you think this message is wrong, it is very likely that this a partially upgraded db and you will need to reload the correct db on which upgrade was never tried.", array(1 => $latestVer));
return false;
}
// check table-column, if the db is 3.1
if (CRM_Core_DAO::checkFieldExists('civicrm_custom_field', 'date_format')) {
$errorMessage = ts("Database check failed - it looks like you have already upgraded to the latest version (v%1) of the database. OR If you think this message is wrong, it is very likely that this a partially upgraded db and you will need to reload the correct db on which upgrade was never tried.", array(1 => $latestVer));
return false;
}
//check previous version table e.g 3.0.*
if (!CRM_Core_DAO::checkTableExists('civicrm_participant_status_type') || !CRM_Core_DAO::checkTableExists('civicrm_navigation')) {
$errorMessage .= ' Few important tables were found missing.';
return false;
}
// check fields which MUST be present if a proper 3.0.* db
if (!CRM_Core_DAO::checkFieldExists('civicrm_relationship_type', 'label_a_b') || !CRM_Core_DAO::checkFieldExists('civicrm_mapping_field', 'im_provider_id') || !CRM_Core_DAO::checkFieldExists('civicrm_contact', 'email_greeting_id')) {
// db looks to have stuck somewhere between 2.2 & 3.0
$errorMessage .= ' Few important fields were found missing in some of the tables.';
return false;
}
return true;
}
/**
* @param $errorMessage
*
* @return bool
*/
function verifyPreDBState(&$errorMessage)
{
$errorMessage = ts('Pre-condition failed for upgrade step %1.', array(1 => '4'));
if (CRM_Core_DAO::checkTableExists('civicrm_event_page')) {
return FALSE;
}
// check fields which MUST be present if a proper 2.2 db
if (!CRM_Core_DAO::checkFieldExists('civicrm_event', 'intro_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'footer_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'confirm_title') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'confirm_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'confirm_footer_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'is_email_confirm') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'confirm_email_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'confirm_from_name') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'confirm_from_email') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'cc_confirm') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'bcc_confirm') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'default_fee_id') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'default_discount_id') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'thankyou_title') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'thankyou_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'thankyou_footer_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'is_pay_later') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'pay_later_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'pay_later_receipt') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'is_multiple_registrations')) {
// db looks to have stuck somewhere between 2.1 & 2.2
$errorMessage .= ' Few important fields were found missing in some of the tables.';
return FALSE;
}
if ($this->checkVersion('2.1.103')) {
$this->setVersion('2.2');
} else {
return FALSE;
}
// update config defaults
$domain = new CRM_Core_DAO_Domain();
$domain->selectAdd();
$domain->selectAdd('config_backend');
$domain->find(TRUE);
if ($domain->config_backend) {
$defaults = unserialize($domain->config_backend);
// reset components
$defaults['enableComponents'] = array('CiviContribute', 'CiviPledge', 'CiviMember', 'CiviEvent', 'CiviMail');
$defaults['enableComponentIDs'] = array(1, 6, 2, 3, 4);
$defaults['moneyvalueformat'] = '%!i';
$defaults['fieldSeparator'] = ',';
$defaults['fatalErrorTemplate'] = 'CRM/common/fatal.tpl';
// serialise settings
CRM_Core_BAO_ConfigSetting::add($defaults);
}
return $this->checkVersion('2.2');
}
function verifyPreDBState(&$errorMessage)
{
$errorMessage = ts('Pre-condition failed for upgrade to 2.1.2.');
// check if the db is 2.2
if (!CRM_Core_DAO::checkTableExists('civicrm_event_page') && CRM_Core_DAO::checkTableExists('civicrm_pcp_block') && CRM_Core_DAO::checkTableExists('civicrm_pcp') && CRM_Core_DAO::checkTableExists('civicrm_contribution_soft')) {
$errorMessage = ts('Database check failed - it looks like you have already upgraded to the latest version (v2.2) of the database.');
return false;
}
// check if the db is 2.2
if (CRM_Core_DAO::checkFieldExists('civicrm_participant', 'registered_by_id') && CRM_Core_DAO::checkFieldExists('civicrm_event', 'intro_text') && CRM_Core_DAO::checkFieldExists('civicrm_event', 'is_multiple_registrations') && CRM_Core_DAO::checkFieldExists('civicrm_pcp_block', 'tellfriend_limit') && CRM_Core_DAO::checkFieldExists('civicrm_pcp_block', 'supporter_profile_id') && CRM_Core_DAO::checkFieldExists('civicrm_pcp', 'status_id') && CRM_Core_DAO::checkFieldExists('civicrm_pcp', 'goal_amount') && CRM_Core_DAO::checkFieldExists('civicrm_contribution_soft', 'pcp_display_in_roll') && CRM_Core_DAO::checkFieldExists('civicrm_contribution_soft', 'amount')) {
$errorMessage = ts('Database check failed - it looks like you have already upgraded to the latest version (v2.2) of the database.');
return false;
}
if (!CRM_Core_DAO::checkTableExists('civicrm_cache') || !CRM_Core_DAO::checkTableExists('civicrm_group_contact_cache') || !CRM_Core_DAO::checkTableExists('civicrm_menu') || !CRM_Core_DAO::checkTableExists('civicrm_discount') || !CRM_Core_DAO::checkTableExists('civicrm_pledge') || !CRM_Core_DAO::checkTableExists('civicrm_pledge_block') || !CRM_Core_DAO::checkTableExists('civicrm_pledge_payment')) {
$errorMessage .= ' Few important tables were found missing.';
return false;
}
// check fields which MUST be present if a proper 2.1 db
if (!CRM_Core_DAO::checkFieldExists('civicrm_cache', 'group_name') || !CRM_Core_DAO::checkFieldExists('civicrm_cache', 'created_date') || !CRM_Core_DAO::checkFieldExists('civicrm_cache', 'expired_date') || !CRM_Core_DAO::checkFieldExists('civicrm_discount', 'option_group_id') || !CRM_Core_DAO::checkFieldExists('civicrm_discount', 'end_date') || !CRM_Core_DAO::checkFieldExists('civicrm_group_contact_cache', 'contact_id') || !CRM_Core_DAO::checkFieldExists('civicrm_menu', 'path_arguments') || !CRM_Core_DAO::checkFieldExists('civicrm_menu', 'is_exposed') || !CRM_Core_DAO::checkFieldExists('civicrm_menu', 'page_type') || !CRM_Core_DAO::checkFieldExists('civicrm_option_value', 'component_id') || !CRM_Core_DAO::checkFieldExists('civicrm_option_group', 'id') || !CRM_Core_DAO::checkFieldExists('civicrm_option_group', 'name')) {
// db looks to have stuck somewhere between 2.0 & 2.1
$errorMessage .= ' Few important fields were found missing in some of the tables.';
return false;
}
return true;
}
function verifyPreDBState(&$errorMessage)
{
$latestVer = CRM_Utils_System::version();
$errorMessage = ts('Pre-condition failed for upgrade to %1.', array(1 => $latestVer));
// check table, if the db is 3.0
if (CRM_Core_DAO::checkTableExists('civicrm_navigation') && CRM_Core_DAO::checkTableExists('civicrm_participant_status_type')) {
$errorMessage = ts("Database check failed - it looks like you have already upgraded to the latest version (v%1) of the database. OR If you think this message is wrong, it is very likely that this a partially upgraded db and you will need to reload the correct db on which upgrade was never tried.", array(1 => $latestVer));
return false;
}
// check table-column, if the db is 3.0
if (CRM_Core_DAO::checkFieldExists('civicrm_menu', 'domain_id') && CRM_Core_DAO::checkFieldExists('civicrm_event', 'created_id') && CRM_Core_DAO::checkFieldExists('civicrm_event', 'is_template') && CRM_Core_DAO::checkFieldExists('civicrm_uf_field', 'is_reserved') && CRM_Core_DAO::checkFieldExists('civicrm_contact', 'email_greeting_id') && CRM_Core_DAO::checkFieldExists('civicrm_payment_processor_type', 'payment_type')) {
$errorMessage = ts("Database check failed - it looks like you have already upgraded to the latest version (v%1) of the database. OR If you think this message is wrong, it is very likely that this a partially upgraded db and you will need to reload the correct db on which upgrade was never tried.", array(1 => $latestVer));
return false;
}
//check previous version table e.g 2.2.*
if (!CRM_Core_DAO::checkTableExists('civicrm_cache') || !CRM_Core_DAO::checkTableExists('civicrm_pcp_block') || !CRM_Core_DAO::checkTableExists('civicrm_menu') || !CRM_Core_DAO::checkTableExists('civicrm_discount') || !CRM_Core_DAO::checkTableExists('civicrm_pcp') || !CRM_Core_DAO::checkTableExists('civicrm_pledge_block') || !CRM_Core_DAO::checkTableExists('civicrm_contribution_soft')) {
$errorMessage .= ' Few important tables were found missing.';
return false;
}
// check fields which MUST be present if a proper 2.2.* db
if (!CRM_Core_DAO::checkFieldExists('civicrm_activity', 'due_date_time') || !CRM_Core_DAO::checkFieldExists('civicrm_contact', 'greeting_type_id') || !CRM_Core_DAO::checkFieldExists('civicrm_contribution', 'check_number')) {
// db looks to have stuck somewhere between 2.1 & 2.2
$errorMessage .= ' Few important fields were found missing in some of the tables.';
return false;
}
return true;
}
function buildQuickForm()
{
require_once 'UK_Direct_Debit/Form/Main.php';
// If no civicrm_sd, then create that table
if (!CRM_Core_DAO::checkTableExists('civicrm_sd')) {
$creatSql = "CREATE TABLE `civicrm_sd` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `start_date` datetime NOT NULL,\n `frequency_unit` enum('day','week','month','year') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'month',\n `frequency_interval` int(10) unsigned DEFAULT NULL,\n\t\t\t\t\t`amount` decimal(20,2) DEFAULT NULL,\n `transaction_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `contact_id` int(10) unsigned DEFAULT NULL,\n `external_id` int(10) unsigned DEFAULT NULL,\n `membership_id` int(10) unsigned DEFAULT NULL,\n `member_count` int(10) unsigned DEFAULT NULL,\n `payment_processor_id` varchar(255) DEFAULT NULL,\n `payment_instrument_id` int(10) unsigned DEFAULT NULL,\n `cycle_day` int(10) unsigned NOT NULL DEFAULT '1',\n `contribution_status_id` int(10) DEFAULT '1',\n `is_valid` int(4) NOT NULL DEFAULT '1',\n `payerReference` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n\t `recur_id` int(10) unsigned DEFAULT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE=InnoDB AUTO_INCREMENT=350 DEFAULT CHARSET=latin1";
CRM_Core_DAO::executeQuery($creatSql);
$columnExists = CRM_Core_DAO::checkFieldExists('civicrm_contribution_recur', 'membership_id');
if (!$columnExists) {
$query = "\n ALTER TABLE civicrm_contribution_recur\n ADD membership_id int(10) unsigned AFTER contact_id,\n ADD CONSTRAINT FK_civicrm_contribution_recur_membership_id\n FOREIGN KEY(membership_id) REFERENCES civicrm_membership(id) ON DELETE CASCADE ON UPDATE RESTRICT";
CRM_Core_DAO::executeQuery($query);
}
} else {
$emptySql = "TRUNCATE TABLE `civicrm_sd`";
CRM_Core_DAO::executeQuery($emptySql);
}
$smartDebitArray = self::getSmartDebitPayments(NULL);
foreach ($smartDebitArray as $key => $smartDebitRecord) {
if ($smartDebitRecord['current_state'] == 10 || $smartDebitRecord['current_state'] == 1 || $smartDebitRecord['current_state'] == 11) {
$regularAmount = substr($smartDebitRecord['regular_amount'], 2);
// Extract the number from reference_number
$output = preg_match("/\\d+/", $smartDebitRecord['reference_number'], $results);
if ($regularAmount && $results[0]) {
list($y, $m, $d) = explode('-', $smartDebitRecord['start_date']);
$sql = "INSERT INTO `civicrm_sd`(`start_date`, `frequency_unit`, `amount`, `transaction_id`, `external_id`, `payment_processor_id`, `payment_instrument_id`, `cycle_day`, `contribution_status_id`, `frequency_interval`, `payerReference`) VALUES (%1,%2,%3,%4,%5,%6,%7,%8,%9,%10,%11)";
$params = array(1 => array($smartDebitRecord['start_date'], 'String'), 2 => array(self::translateSmartDebitFrequencyUnit($smartDebitRecord['frequency_type']), 'String'), 3 => array($regularAmount, 'Float'), 4 => array($smartDebitRecord['reference_number'], 'String'), 5 => array($results[0], 'Int'), 6 => array(self::getSmartDebitPaymentProcessorID(), 'Int'), 7 => array(5, 'Int'), 8 => array($d, 'Int'), 9 => array(5, 'Int'), 10 => array($smartDebitRecord['frequency_factor'], 'Int'), 11 => array($smartDebitRecord['payerReference'], 'String'));
CRM_Core_DAO::executeQuery($sql, $params);
}
}
}
if ($smartDebitArray) {
CRM_Core_Session::setStatus('Smart debit data retreived successfully.', 'Success', 'info');
}
}
/**
* Check whether the install has run before.
*
* Circa v4.7.betaX, we introduced a new mechanism for tracking installation
* and firing a post-install event. However, it's fairly difficult to test the
* edge-cases directly, so this canary should fire if there are any problems
* in the design/implementation of the installation-tracker.
*
* This should not exist. It should be removed in a future version.
*
* @param \Civi\Core\Event\SystemInstallEvent $event
* @throws \CRM_Core_Exception
*/
public static function check(SystemInstallEvent $event)
{
if (\CRM_Core_DAO::checkTableExists('civicrm_install_canary')) {
throw new \CRM_Core_Exception("Found installation canary. This suggests that something went wrong with tracking installation process. Please post to forum or JIRA.");
}
\Civi::log()->info('Creating canary table');
\CRM_Core_DAO::executeQuery('CREATE TABLE civicrm_install_canary (id int(10) unsigned NOT NULL)');
}
function verifyPreDBState(&$errorMessage)
{
// check if log file is writable
$config = CRM_Core_Config::singleton();
if (!is_writable($config->uploadDir . 'CiviCRM.log') && !is_writable($config->uploadDir . 'CiviCRM.log.' . md5($config->dsn . $config->userFrameworkResourceURL))) {
$errorMessage = ts('Log file CiviCRM.log is not writable. Make sure files directory is writable.', array(1 => $config->uploadDir));
return false;
}
$errorMessage = ts('Database check failed - the current database is not v2.1.');
$is21db = true;
// abort if partial upgraded db found.
if ($this->checkVersion('2.1.101') || $this->checkVersion('2.1.102') || $this->checkVersion('2.1.103')) {
$errorMessage = ts('Corrupt / Partial Upgraded database found. Looks like upgrade wizard failed to complete all the required steps to convert your database to v2.2. Please fix any errors and start the upgrade process again with a clean v2.1 database.');
return false;
}
// abort if already 2.2
if ($this->checkVersion('2.2')) {
$errorMessage = ts('Database check failed - looks like you have already upgraded to the latest version (v2.2) of the database.');
return false;
}
// check if 2.1 version
if (!$this->checkVersion('2.1.2') || !$this->checkVersion('2.1.3') || !$this->checkVersion('2.1.4')) {
$is21db = false;
}
// check if 2.1 tables exists
if (!CRM_Core_DAO::checkTableExists('civicrm_pledge') || !CRM_Core_DAO::checkTableExists('civicrm_cache') || !CRM_Core_DAO::checkTableExists('civicrm_group_contact_cache') || !CRM_Core_DAO::checkTableExists('civicrm_discount') || !CRM_Core_DAO::checkTableExists('civicrm_menu') || !CRM_Core_DAO::checkTableExists('civicrm_pledge') || !CRM_Core_DAO::checkTableExists('civicrm_pledge_block') || !CRM_Core_DAO::checkTableExists('civicrm_case_contact')) {
// db is not 2.1
$errorMessage .= ' Few 2.1 tables were found missing.';
$is21db = false;
}
// check fields which MUST be present if a proper 2.1 db
if ($is21db) {
if (!CRM_Core_DAO::checkFieldExists('civicrm_contribution_page', 'is_recur_interval') || !CRM_Core_DAO::checkFieldExists('civicrm_contribution_page', 'recur_frequency_unit') || !CRM_Core_DAO::checkFieldExists('civicrm_contribution_page', 'for_organization') || !CRM_Core_DAO::checkFieldExists('civicrm_contribution_page', 'is_for_organization') || !CRM_Core_DAO::checkFieldExists('civicrm_contribution', 'is_pay_later') || !CRM_Core_DAO::checkFieldExists('civicrm_membership', 'is_pay_later') || !CRM_Core_DAO::checkFieldExists('civicrm_membership_status', 'is_reserved') || !CRM_Core_DAO::checkFieldExists('civicrm_participant', 'is_pay_later') || !CRM_Core_DAO::checkFieldExists('civicrm_participant', 'fee_amount') || !CRM_Core_DAO::checkFieldExists('civicrm_participant', 'registered_by_id') || !CRM_Core_DAO::checkFieldExists('civicrm_participant', 'discount_id') || !CRM_Core_DAO::checkFieldExists('civicrm_contact', 'employer_id') || !CRM_Core_DAO::checkFieldExists('civicrm_domain', 'locales') || !CRM_Core_DAO::checkFieldExists('civicrm_mapping', 'mapping_type_id') || !CRM_Core_DAO::checkFieldExists('civicrm_custom_field', 'is_view') || !CRM_Core_DAO::checkFieldExists('civicrm_group', 'cache_date') || !CRM_Core_DAO::checkFieldExists('civicrm_group', 'parents') || !CRM_Core_DAO::checkFieldExists('civicrm_group', 'children') || !CRM_Core_DAO::checkFieldExists('civicrm_preferences', 'editor_id') || !CRM_Core_DAO::checkFieldExists('civicrm_uf_group', 'group_type') || !CRM_Core_DAO::checkFieldExists('civicrm_address', 'name') || !CRM_Core_DAO::checkFieldExists('civicrm_uf_match', 'language')) {
// db looks to have stuck somewhere between 2.1 & 2.2
$errorMessage .= ' Few important fields were found missing in some of the tables.';
$is21db = false;
}
}
// check if the db is 2.2
if (!CRM_Core_DAO::checkTableExists('civicrm_event_page') && CRM_Core_DAO::checkFieldExists('civicrm_participant', 'registered_by_id') && CRM_Core_DAO::checkFieldExists('civicrm_event', 'intro_text') && CRM_Core_DAO::checkFieldExists('civicrm_event', 'is_multiple_registrations') && CRM_Core_DAO::checkTableExists('civicrm_pcp_block') && CRM_Core_DAO::checkFieldExists('civicrm_pcp_block', 'tellfriend_limit') && CRM_Core_DAO::checkFieldExists('civicrm_pcp_block', 'supporter_profile_id') && CRM_Core_DAO::checkTableExists('civicrm_pcp') && CRM_Core_DAO::checkFieldExists('civicrm_pcp', 'status_id') && CRM_Core_DAO::checkFieldExists('civicrm_pcp', 'goal_amount') && CRM_Core_DAO::checkTableExists('civicrm_contribution_soft') && CRM_Core_DAO::checkFieldExists('civicrm_contribution_soft', 'pcp_display_in_roll') && CRM_Core_DAO::checkFieldExists('civicrm_contribution_soft', 'amount')) {
$errorMessage = ts('Database check failed - it looks like you have already upgraded to the latest version (v2.2) of the database.');
return false;
}
// check tables which should not exist for v2.x
if (CRM_Core_DAO::checkTableExists('civicrm_custom_option') || CRM_Core_DAO::checkTableExists('civicrm_custom_value') || CRM_Core_DAO::checkTableExists('civicrm_email_history') || CRM_Core_DAO::checkTableExists('civicrm_geo_coord') || CRM_Core_DAO::checkTableExists('civicrm_individual') || CRM_Core_DAO::checkTableExists('civicrm_location') || CRM_Core_DAO::checkTableExists('civicrm_meeting') || CRM_Core_DAO::checkTableExists('civicrm_organization') || CRM_Core_DAO::checkTableExists('civicrm_phonecall') || CRM_Core_DAO::checkTableExists('civicrm_sms_history') || CRM_Core_DAO::checkTableExists('civicrm_validation')) {
// table(s) found in the db which are no longer required
// for v2.x, though would not do any harm it's recommended
// to remove them.
CRM_Core_Session::setStatus(ts("Table(s) found in your db which are no longer required for v2.x, though would not do any harm it's recommended to remove them"));
}
// show error if any of the tables, use 'MyISAM' storage engine.
// just check the first 10 civicrm tables, rather than checking all 106!
if (CRM_Core_DAO::isDBMyISAM(10)) {
$errorMessage = ts('Your database is configured to use the MyISAM database engine. CiviCRM requires InnoDB. You will need to convert any MyISAM tables in your database to InnoDB before proceeding.');
return false;
}
return true;
}
/**
* Add column to table on install
*/
public function install()
{
if (CRM_Core_DAO::checkTableExists('civicrm_contact_segment')) {
if (!CRM_Core_DAO::checkFieldExists('civicrm_contact_segment', 'is_main')) {
CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_contact_segment\n ADD COLUMN is_main TINYINT(3) NULL DEFAULT 0 AFTER is_active");
}
} else {
throw new Exception('This extension requires table civicrm_contact_segment
(extension org.civicoop.contactsegment), could not find this table!');
}
}
/**
* Function to delete a condition with id
*
* @param int $conditionId
* @throws Exception when conditionId is empty
* @access public
* @static
*/
public static function deleteWithId($conditionId)
{
if (empty($conditionId)) {
throw new Exception('condition id can not be empty when attempting to delete a civirule condition');
}
if (!CRM_Core_DAO::checkTableExists(self::getTableName())) {
return;
}
//delete rows from rule_condition to prevent a foreign key constraint error
CRM_Core_DAO::executeQuery("DELETE FROM `civirule_rule_condition` where `condition_id` = %1", array(1 => array($conditionId, 'Integer')));
$condition = new CRM_Civirules_BAO_Condition();
$condition->id = $conditionId;
$condition->delete();
return;
}
function verifyPostDBState(&$errorMessage)
{
// check if civicrm_event_page tables droped
if (CRM_Core_DAO::checkTableExists('civicrm_event_page')) {
$errorMessage .= ' civicrm_event_page table is not droped.';
return false;
}
// check fields which MUST be present civicrm_event
if (!CRM_Core_DAO::checkFieldExists('civicrm_event', 'intro_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'footer_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'confirm_title') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'confirm_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'confirm_footer_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'is_email_confirm') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'confirm_email_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'confirm_from_name') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'confirm_from_email') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'cc_confirm') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'bcc_confirm') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'default_fee_id') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'default_discount_id') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'thankyou_title') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'thankyou_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'thankyou_footer_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'is_pay_later') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'pay_later_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'pay_later_receipt') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'is_multiple_registrations')) {
$errorMessage .= ' Few important fields were found missing in civicrm_event table.';
return false;
}
$errorMessage = ts('Post-condition failed for upgrade step %1.', array(1 => '2'));
return $this->checkVersion('2.1.102');
}
public static function rewriteContactIds()
{
$contractTable = CRM_Core_DAO::checkTableExists('civicrm_hrjobcontract');
$datesTable = CRM_Core_DAO::checkTableExists('civicrm_value_jobcontract_dates_13');
if (!$contractTable || !$datesTable) {
return false;
}
$dates = CRM_Core_DAO::executeQuery('SELECT id, contract_id FROM civicrm_value_jobcontract_dates_13 ORDER BY id ASC');
while ($dates->fetch()) {
$contract = CRM_Core_DAO::executeQuery('SELECT contact_id FROM civicrm_hrjobcontract WHERE id = %1', array(1 => array($dates->contract_id, 'Integer')));
if ($contract->fetch()) {
CRM_Core_DAO::executeQuery('UPDATE civicrm_value_jobcontract_dates_13 SET entity_id = %1 WHERE id = %2', array(1 => array($contract->contact_id, 'Integer'), 2 => array($dates->id, 'Integer')));
}
}
return true;
}
function verifyPostDBState(&$errorMessage)
{
if (!CRM_Core_DAO::checkTableExists('civicrm_cache') || !CRM_Core_DAO::checkTableExists('civicrm_discount') || !CRM_Core_DAO::checkTableExists('civicrm_group_contact_cache') || !CRM_Core_DAO::checkTableExists('civicrm_menu')) {
// db is not 2.1
$errorMessage .= ' Few 2.1 tables were found missing.';
return false;
}
// check fields which MUST be present if a proper 2.1 db
if (!CRM_Core_DAO::checkFieldExists('civicrm_cache', 'group_name') || !CRM_Core_DAO::checkFieldExists('civicrm_cache', 'created_date') || !CRM_Core_DAO::checkFieldExists('civicrm_cache', 'expired_date') || !CRM_Core_DAO::checkFieldExists('civicrm_discount', 'option_group_id') || !CRM_Core_DAO::checkFieldExists('civicrm_discount', 'end_date') || !CRM_Core_DAO::checkFieldExists('civicrm_group_contact_cache', 'contact_id') || !CRM_Core_DAO::checkFieldExists('civicrm_menu', 'path_arguments') || !CRM_Core_DAO::checkFieldExists('civicrm_menu', 'is_exposed') || !CRM_Core_DAO::checkFieldExists('civicrm_menu', 'page_type')) {
// db looks to have stuck somewhere between 2.0 & 2.1
$errorMessage .= ' Few important fields were found missing in some of the tables.';
return false;
}
$errorMessage = ts('Post-condition failed for upgrade step %1.', array(1 => '1'));
return $this->checkVersion('2.03');
}
function verifyPostDBState(&$errorMessage)
{
// check if Option Group & Option Values tables exists
if (!CRM_Core_DAO::checkTableExists('civicrm_option_group') || !CRM_Core_DAO::checkTableExists('civicrm_option_value')) {
$errorMessage .= ' option group or option value table is missing.';
return FALSE;
}
// check fields which MUST be present civicrm_option_group & civicrm_option_value
if (!CRM_Core_DAO::checkFieldExists('civicrm_option_group', 'id') || !CRM_Core_DAO::checkFieldExists('civicrm_option_group', 'name') || !CRM_Core_DAO::checkFieldExists('civicrm_option_group', 'label') || !CRM_Core_DAO::checkFieldExists('civicrm_option_group', 'description') || !CRM_Core_DAO::checkFieldExists('civicrm_option_group', 'is_reserved') || !CRM_Core_DAO::checkFieldExists('civicrm_option_group', 'is_active')) {
$errorMessage .= ' Few important fields were found missing in civicrm_option_group table.';
return FALSE;
}
if (!CRM_Core_DAO::checkFieldExists('civicrm_option_value', 'id') || !CRM_Core_DAO::checkFieldExists('civicrm_option_value', 'option_group_id') || !CRM_Core_DAO::checkFieldExists('civicrm_option_value', 'name') || !CRM_Core_DAO::checkFieldExists('civicrm_option_value', 'label') || !CRM_Core_DAO::checkFieldExists('civicrm_option_value', 'description') || !CRM_Core_DAO::checkFieldExists('civicrm_option_value', 'component_id') || !CRM_Core_DAO::checkFieldExists('civicrm_option_value', 'is_active')) {
$errorMessage .= ' Few important fields were found missing in civicrm_option_value table.';
return FALSE;
}
$errorMessage = ts('Post-condition failed for upgrade step %1.', array(1 => '1'));
return $this->checkVersion('2.02');
}
function verifyPreDBState(&$errorMessage)
{
$errorMessage = ts('Pre-condition failed for upgrade step %1.', array(1 => '2'));
if (!CRM_Core_DAO::checkTableExists('civicrm_cache') || !CRM_Core_DAO::checkTableExists('civicrm_group_contact_cache') || !CRM_Core_DAO::checkTableExists('civicrm_menu') || !CRM_Core_DAO::checkTableExists('civicrm_discount') || !CRM_Core_DAO::checkTableExists('civicrm_pledge') || !CRM_Core_DAO::checkTableExists('civicrm_pledge_block') || !CRM_Core_DAO::checkTableExists('civicrm_pledge_payment')) {
return false;
}
// check fields which MUST be present if a proper 2.1 db
if (!CRM_Core_DAO::checkFieldExists('civicrm_cache', 'group_name') || !CRM_Core_DAO::checkFieldExists('civicrm_cache', 'created_date') || !CRM_Core_DAO::checkFieldExists('civicrm_cache', 'expired_date') || !CRM_Core_DAO::checkFieldExists('civicrm_discount', 'option_group_id') || !CRM_Core_DAO::checkFieldExists('civicrm_discount', 'end_date') || !CRM_Core_DAO::checkFieldExists('civicrm_group_contact_cache', 'contact_id') || !CRM_Core_DAO::checkFieldExists('civicrm_menu', 'path_arguments') || !CRM_Core_DAO::checkFieldExists('civicrm_menu', 'is_exposed') || !CRM_Core_DAO::checkFieldExists('civicrm_menu', 'page_type') || !CRM_Core_DAO::checkFieldExists('civicrm_option_value', 'component_id') || !CRM_Core_DAO::checkFieldExists('civicrm_option_group', 'id') || !CRM_Core_DAO::checkFieldExists('civicrm_option_group', 'name')) {
// db looks to have stuck somewhere between 2.0 & 2.1
$errorMessage .= ' Few important fields were found missing in some of the tables.';
return false;
}
if ($this->checkVersion('2.03')) {
$this->setVersion($this->latestVersion);
} else {
return false;
}
return $this->checkVersion($this->latestVersion);
}
/**
* Method for upgrade 1002
* (rename events to trigger, check https://github.com/CiviCooP/org.civicoop.civirules/issues/42)
* - rename table civirule_event to civirule_trigger
* - rename columns event_id, event_params in table civirule_rule to trigger_id, trigger_params
* - remove index on event_id
* - add index on trigger_id
*/
public function upgrade_1002()
{
// rename table civirule_event to civirule_trigger
if (CRM_Core_DAO::checkTableExists("civirule_event")) {
CRM_Core_DAO::executeQuery("RENAME TABLE civirule_event TO civirule_trigger");
} else {
$this->executeSqlFile('sql/createCiviruleTrigger.sql');
$this->executeSqlFile('sql/insertCiviruleTrigger.sql');
}
// rename columns event_id and event_params in civirule_rule
if (CRM_Core_DAO::checkTableExists("civirule_rule")) {
CRM_Core_DAO::executeQuery("ALTER TABLE civirule_rule DROP FOREIGN KEY fk_rule_event;");
CRM_Core_DAO::executeQuery("ALTER TABLE civirule_rule DROP INDEX fk_rule_event_idx;");
CRM_Core_DAO::executeQuery("ALTER TABLE civirule_rule CHANGE event_id trigger_id INT UNSIGNED;");
CRM_Core_DAO::executeQuery("ALTER TABLE civirule_rule CHANGE event_params trigger_params TEXT;");
CRM_Core_DAO::executeQuery("ALTER TABLE civirule_rule ADD CONSTRAINT fk_rule_trigger FOREIGN KEY (trigger_id) REFERENCES civirule_trigger(id);");
CRM_Core_DAO::executeQuery("ALTER TABLE civirule_rule ADD INDEX fk_rule_trigger_idx (trigger_id);");
}
return true;
}
function verifyPreDBState(&$errorMessage)
{
$latestVer = CRM_Utils_System::version();
$errorMessage = ts('Pre-condition failed for upgrade to %1.', array(1 => $latestVer));
// check tables and table-columns, if the db is already 3.1
if (CRM_Core_DAO::checkTableExists('civicrm_acl_contact_cache') || CRM_Core_DAO::checkTableExists('civicrm_contact_type') || CRM_Core_DAO::checkTableExists('civicrm_dashboard') || CRM_Core_DAO::checkTableExists('civicrm_dashboard_contact') || CRM_Core_DAO::checkFieldExists('civicrm_country', 'is_province_abbreviated') || CRM_Core_DAO::checkFieldExists('civicrm_custom_field', 'date_format') || CRM_Core_DAO::checkFieldExists('civicrm_custom_field', 'time_format') || CRM_Core_DAO::checkFieldExists('civicrm_mail_settings', 'domain_id') || CRM_Core_DAO::checkFieldExists('civicrm_msg_template', 'workflow_id') || CRM_Core_DAO::checkFieldExists('civicrm_msg_template', 'is_default') || CRM_Core_DAO::checkFieldExists('civicrm_msg_template', 'is_reserved') || CRM_Core_DAO::checkFieldExists('civicrm_option_value', 'domain_id') || CRM_Core_DAO::checkFieldExists('civicrm_preferences', 'contact_autocomplete_options') || CRM_Core_DAO::checkFieldExists('civicrm_preferences_date', 'date_format') || CRM_Core_DAO::checkFieldExists('civicrm_preferences_date', 'time_format') || CRM_Core_DAO::checkFieldExists('civicrm_price_set', 'domain_id') || CRM_Core_DAO::checkFieldExists('civicrm_price_set', 'extends') || CRM_Core_DAO::checkFieldExists('civicrm_relationship_type', 'contact_sub_type_a') || CRM_Core_DAO::checkFieldExists('civicrm_relationship_type', 'contact_sub_type_b') || CRM_Core_DAO::checkFieldExists('civicrm_report_instance', 'domain_id')) {
$errorMessage = ts("Database check failed - it looks like you have already upgraded to the latest version (v%1) of the database. OR If you think this message is wrong, it is very likely that this a partially upgraded database and you will need to reload the correct database from backup on which upgrade was never tried.", array(1 => $latestVer));
return false;
}
//check previous version tables e.g 3.0.*
if (!CRM_Core_DAO::checkTableExists('civicrm_participant_status_type') || !CRM_Core_DAO::checkTableExists('civicrm_navigation')) {
$errorMessage .= ' Few important tables were found missing.';
return false;
}
// check fields which MUST be present if a proper 3.0.* db
if (!CRM_Core_DAO::checkFieldExists('civicrm_contact', 'email_greeting_id') || !CRM_Core_DAO::checkFieldExists('civicrm_contribution_page', 'created_id') || !CRM_Core_DAO::checkFieldExists('civicrm_custom_group', 'created_date') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'is_template') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'created_id') || !CRM_Core_DAO::checkFieldExists('civicrm_mailing', 'created_date') || !CRM_Core_DAO::checkFieldExists('civicrm_mapping_field', 'im_provider_id') || !CRM_Core_DAO::checkFieldExists('civicrm_membership_type', 'domain_id') || !CRM_Core_DAO::checkFieldExists('civicrm_menu', 'domain_id') || !CRM_Core_DAO::checkFieldExists('civicrm_participant', 'fee_currency') || !CRM_Core_DAO::checkFieldExists('civicrm_payment_processor', 'domain_id') || !CRM_Core_DAO::checkFieldExists('civicrm_payment_processor_type', 'payment_type') || !CRM_Core_DAO::checkFieldExists('civicrm_preferences', 'domain_id') || !CRM_Core_DAO::checkFieldExists('civicrm_preferences', 'navigation') || !CRM_Core_DAO::checkFieldExists('civicrm_relationship_type', 'label_a_b') || !CRM_Core_DAO::checkFieldExists('civicrm_report_instance', 'navigation_id') || !CRM_Core_DAO::checkFieldExists('civicrm_uf_field', 'is_reserved') || !CRM_Core_DAO::checkFieldExists('civicrm_uf_group', 'created_id') || !CRM_Core_DAO::checkFieldExists('civicrm_uf_match', 'domain_id')) {
// db looks to have stuck somewhere between 3.0 & 3.1
$errorMessage .= ' Few important fields were found missing in some of the tables.';
return false;
}
return true;
}
/**
* This function checks to see if we have the right config values
*
* @return string the error message if any
* @public
*/
function checkConfig()
{
$config = CRM_Core_Config::singleton();
$error = array();
//Create database tables if they haven't been.
if (!CRM_Core_DAO::checkTableExists('civicrm_stripe_customers')) {
CRM_Core_DAO::executeQuery("\r\n\t\tCREATE TABLE IF NOT EXISTS `civicrm_stripe_customers` (\r\n \t\t\t`email` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,\r\n \t\t\t`id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,\r\n \t\t\tUNIQUE KEY `email` (`email`)\r\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\r\n\t\t\t");
CRM_Core_DAO::executeQuery("\r\n\t\tCREATE TABLE IF NOT EXISTS `civicrm_stripe_plans` (\r\n \t\t\t`plan_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,\r\n \t\t\tUNIQUE KEY `plan_id` (`plan_id`)\r\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\r\n\t\t");
CRM_Core_DAO::executeQuery("\r\n\t\tCREATE TABLE IF NOT EXISTS `civicrm_stripe_subscriptions` (\r\n\t\t\t`customer_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,\r\n\t\t\t`invoice_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,\r\n\t\t\t`end_time` int(11) NOT NULL DEFAULT '0',\r\n\t\t\t`is_live` tinyint(4) NOT NULL COMMENT 'Whether this is a live or test transaction',\r\n\t\t\tKEY `end_time` (`end_time`)\r\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;\r\n\t\t");
CRM_Core_Error::debug('Stripe Database tables created. <br />This is the only time this message will be displayed. You do not need to take any further actions.');
}
if (empty($this->_paymentProcessor['user_name'])) {
$error[] = ts('The "Secret Key" is not set in the Stripe Payment Processor settings.');
}
if (empty($this->_paymentProcessor['password'])) {
$error[] = ts('The "Publishable Key" is not set in the Stripe Payment Processor settings.');
}
if (!empty($error)) {
return implode('<p>', $error);
} else {
return NULL;
}
}
/**
* PostNummer.Get API
*
* @param array $params
* @return array API result descriptor
* @see civicrm_api3_create_success
* @see civicrm_api3_create_error
* @throws API_Exception
*/
function civicrm_api3_postnummer_get($params)
{
if (!$params['post_code']) {
throw new API_Exception("Post Code is a mandatory param");
}
if (!CRM_Core_DAO::checkTableExists("civicrm_post_nummer")) {
throw new API_Exception("No table civicrm_post_nummer found, can not use API PostCode Get");
}
$returnValues = array();
$query = "SELECT * FROM civicrm_post_nummer WHERE post_code = %1";
$dao = CRM_Core_DAO::executeQuery($query, array(1 => array($params['post_code'], "String")));
if ($dao->fetch()) {
$returnValue['post_code'] = $dao->post_code;
$returnValue['post_city'] = $dao->post_city;
$county = CRM_Postnummer_Utils::getCountyWithCommunityNumber($dao->community_number);
if ($county != FALSE) {
$returnValue['county_id'] = $county['state_province_id'];
} else {
$returnValue['county_id'] = "";
}
$returnValues[] = $returnValue;
}
return civicrm_api3_create_success($returnValues, $params, 'PostCode', 'Get');
}
/**
* @param $formValues
*/
public function __construct(&$formValues)
{
$this->_formValues = $formValues;
$clauses = array();
$clauses[] = "contrib.total_amount != contrib_recur.amount";
$clauses[] = "contrib.is_test = 0";
$clauses[] = "(contrib.contribution_status_id = 1 OR contrib.contribution_status_id = 8)";
$value = isset($this->_formValues['start_date']) ? $this->_formValues['start_date'] : '';
if ($changeDate = CRM_Utils_Date::processDate($value)) {
$clauses[] = "contrib.receive_date >= {$changeDate}";
}
$whereClause = implode(' AND ', $clauses);
$this->tableName = 'civicrm_temp_custom_recurring_change_search';
if (CRM_Core_DAO::checkTableExists($this->tableName)) {
$query = "DROP TABLE {$this->tableName}";
CRM_Core_DAO::executeQuery($query);
}
$creatSql = "CREATE TABLE {$this->tableName}\n SELECT contrib.id as contribution_id, contact_a.id as contact_id,\n contact_a.sort_name as sort_name,\n contrib_recur.amount as new_amount,\n CONCAT(contrib_recur.frequency_interval, ' ', contrib_recur.frequency_unit) as frequency,\n contrib.receive_date as change_date,\n contrib.total_amount AS old_amount\n FROM civicrm_contact contact_a \n INNER JOIN civicrm_contribution_recur contrib_recur ON (contrib_recur.contact_id = contact_a.id AND (contrib_recur.end_date IS NULL OR contrib_recur.end_date >= NOW()) AND (contrib_recur.cancel_date IS NULL OR contrib_recur.cancel_date >= NOW()) )\n INNER JOIN civicrm_contribution contrib ON contrib.contribution_recur_id = contrib_recur.id\n WHERE {$whereClause}";
CRM_Core_DAO::executeQuery($creatSql);
// Define the columns for search result rows
$this->_columns = array(ts('Name') => 'sort_name', ts('Old Amount') => 'old_amount', ts('Changed Since') => 'change_date', ts('New Amount') => 'new_amount', ts('Frequency') => 'frequency');
// define component access permission needed
$this->_permissionedComponent = 'CiviContribute';
}
/**
* Load all explicit settings that apply to this domain or contact.
*
* @return $this
*/
public function loadValues()
{
// Note: Don't use DAO child classes. They require fields() which require
// translations -- which are keyed off settings!
$this->values = array();
$this->combined = NULL;
// Ordinarily, we just load values from `civicrm_setting`. But upgrades require care.
// In v4.0 and earlier, all values were stored in `civicrm_domain.config_backend`.
// In v4.1-v4.6, values were split between `civicrm_domain` and `civicrm_setting`.
// In v4.7+, all values are stored in `civicrm_setting`.
// Whenever a value is available in civicrm_setting, it will take precedence.
$isUpgradeMode = \CRM_Core_Config::isUpgradeMode();
if ($isUpgradeMode && empty($this->contactId) && \CRM_Core_DAO::checkFieldExists('civicrm_domain', 'config_backend', FALSE)) {
$config_backend = \CRM_Core_DAO::singleValueQuery('SELECT config_backend FROM civicrm_domain WHERE id = %1', array(1 => array($this->domainId, 'Positive')));
$oldSettings = \CRM_Upgrade_Incremental_php_FourSeven::convertBackendToSettings($this->domainId, $config_backend);
\CRM_Utils_Array::extend($this->values, $oldSettings);
}
// Normal case. Aside: Short-circuit prevents unnecessary query.
if (!$isUpgradeMode || \CRM_Core_DAO::checkTableExists('civicrm_setting')) {
$dao = \CRM_Core_DAO::executeQuery($this->createQuery()->toSQL());
while ($dao->fetch()) {
$this->values[$dao->name] = $dao->value !== NULL ? unserialize($dao->value) : NULL;
}
}
return $this;
}
function verifyPreDBState(&$errorMessage)
{
$config =& CRM_Core_Config::singleton();
// Let's first update the config defaults
require_once "CRM/Core/DAO/Domain.php";
$domain =& new CRM_Core_DAO_Domain();
$domain->selectAdd();
$domain->selectAdd('config_backend');
$domain->find(true);
if ($domain->config_backend) {
$defaults = unserialize($domain->config_backend);
// reset components
$defaults['enableComponents'] = array('CiviContribute', 'CiviPledge', 'CiviMember', 'CiviEvent', 'CiviMail');
$defaults['enableComponentIDs'] = array(1, 6, 2, 3, 4);
$defaults['moneyvalueformat'] = '%!i';
$defaults['fieldSeparator'] = ',';
$defaults['fatalErrorTemplate'] = 'CRM/common/fatal.tpl';
// update cms-version
if ($config->userFramework == 'Joomla') {
$defaults['userFrameworkVersion'] = '1.5';
if (class_exists('JVersion')) {
$version =& new JVersion();
$defaults['userFrameworkVersion'] = $version->getShortVersion();
}
} else {
if ($config->userFramework == 'Drupal') {
$defaults['userFrameworkVersion'] = '6.3';
if (defined('VERSION')) {
$defaults['userFrameworkVersion'] = VERSION;
}
}
}
// serialise settings
require_once "CRM/Core/BAO/Setting.php";
CRM_Core_BAO_Setting::add($defaults);
$config =& CRM_Core_Config::singleton();
}
// check if log file is writable
if (!is_writable($config->uploadDir . 'CiviCRM.log')) {
$errorMessage = ts('Log file CiviCRM.log is not writable. Make sure files directory is writable.', array(1 => $config->uploadDir));
return false;
}
$errorMessage = ts('Database check failed - the current database is not v2.0.');
$is20db = true;
// abort if partial upgraded db found.
if ($this->checkVersion('2.01') || $this->checkVersion('2.02') || $this->checkVersion('2.03')) {
$errorMessage = ts('Corrupt / Partial Upgraded database found. Looks like upgrade wizard failed to complete all the required steps to convert your database to v2.1. Please fix any errors and start the upgrade process again with a clean v2.0 database.');
return false;
}
// abort if already 2.1
if ($this->checkVersion('2.1')) {
$errorMessage = ts('Database check failed - looks like you have already upgraded to the latest version (v2.1) of the database.');
return false;
}
// check if 2.0 version
if (!$this->checkVersion('2.0')) {
$is20db = false;
}
// check if 2.0 tables exists
if (!CRM_Core_DAO::checkTableExists('civicrm_activity') || !CRM_Core_DAO::checkTableExists('civicrm_activity_assignment') || !CRM_Core_DAO::checkTableExists('civicrm_activity_target') || !CRM_Core_DAO::checkTableExists('civicrm_address') || !CRM_Core_DAO::checkTableExists('civicrm_address') || !CRM_Core_DAO::checkTableExists('civicrm_case') || !CRM_Core_DAO::checkTableExists('civicrm_case_activity') || !CRM_Core_DAO::checkTableExists('civicrm_component') || !CRM_Core_DAO::checkTableExists('civicrm_contribution_widget') || !CRM_Core_DAO::checkTableExists('civicrm_grant') || !CRM_Core_DAO::checkTableExists('civicrm_group_nesting') || !CRM_Core_DAO::checkTableExists('civicrm_group_organization') || !CRM_Core_DAO::checkTableExists('civicrm_loc_block') || !CRM_Core_DAO::checkTableExists('civicrm_openid') || !CRM_Core_DAO::checkTableExists('civicrm_openid_associations') || !CRM_Core_DAO::checkTableExists('civicrm_openid_nonces') || !CRM_Core_DAO::checkTableExists('civicrm_preferences_date') || !CRM_Core_DAO::checkTableExists('civicrm_tell_friend') || !CRM_Core_DAO::checkTableExists('civicrm_timezone') || !CRM_Core_DAO::checkTableExists('civicrm_worldregion')) {
// db is not 2.0
$errorMessage .= ' Few 2.0 tables were found missing.';
$is20db = false;
}
// check fields which MUST be present if a proper 2.0 db
if ($is20db) {
if (!CRM_Core_DAO::checkFieldExists('civicrm_activity', 'source_record_id') || !CRM_Core_DAO::checkFieldExists('civicrm_activity', 'activity_date_time') || !CRM_Core_DAO::checkFieldExists('civicrm_activity', 'status_id') || !CRM_Core_DAO::checkFieldExists('civicrm_contact', 'first_name') || !CRM_Core_DAO::checkFieldExists('civicrm_contact', 'last_name') || !CRM_Core_DAO::checkFieldExists('civicrm_contact', 'gender_id') || !CRM_Core_DAO::checkFieldExists('civicrm_contact', 'mail_to_household_id') || !CRM_Core_DAO::checkFieldExists('civicrm_contact', 'user_unique_id') || !CRM_Core_DAO::checkFieldExists('civicrm_contact', 'household_name') || !CRM_Core_DAO::checkFieldExists('civicrm_contact', 'organization_name') || !CRM_Core_DAO::checkFieldExists('civicrm_contribution', 'honor_type_id') || !CRM_Core_DAO::checkFieldExists('civicrm_contribution_page', 'is_pay_later') || !CRM_Core_DAO::checkFieldExists('civicrm_contribution_page', 'pay_later_text') || !CRM_Core_DAO::checkFieldExists('civicrm_country', 'region_id') || !CRM_Core_DAO::checkFieldExists('civicrm_custom_field', 'column_name') || !CRM_Core_DAO::checkFieldExists('civicrm_custom_field', 'option_group_id') || !CRM_Core_DAO::checkFieldExists('civicrm_custom_group', 'table_name') || !CRM_Core_DAO::checkFieldExists('civicrm_custom_group', 'is_multiple') || !CRM_Core_DAO::checkFieldExists('civicrm_domain', 'version') || !CRM_Core_DAO::checkFieldExists('civicrm_domain', 'loc_block_id') || !CRM_Core_DAO::checkFieldExists('civicrm_email', 'contact_id') || !CRM_Core_DAO::checkFieldExists('civicrm_email', 'location_type_id') || !CRM_Core_DAO::checkFieldExists('civicrm_email', 'is_billing') || !CRM_Core_DAO::checkFieldExists('civicrm_entity_tag', 'contact_id') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'participant_listing_id') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'loc_block_id') || !CRM_Core_DAO::checkFieldExists('civicrm_event', 'receipt_text') || !CRM_Core_DAO::checkFieldExists('civicrm_event_page', 'is_pay_later') || !CRM_Core_DAO::checkFieldExists('civicrm_event_page', 'pay_later_text') || !CRM_Core_DAO::checkFieldExists('civicrm_financial_trxn', 'contribution_id') || !CRM_Core_DAO::checkFieldExists('civicrm_im', 'contact_id') || !CRM_Core_DAO::checkFieldExists('civicrm_im', 'location_type_id') || !CRM_Core_DAO::checkFieldExists('civicrm_membership_payment', 'contribution_id') || !CRM_Core_DAO::checkFieldExists('civicrm_membership_type', 'receipt_text_signup') || !CRM_Core_DAO::checkFieldExists('civicrm_membership_type', 'receipt_text_renewal') || !CRM_Core_DAO::checkFieldExists('civicrm_option_value', 'component_id') || !CRM_Core_DAO::checkFieldExists('civicrm_participant_payment', 'contribution_id') || !CRM_Core_DAO::checkFieldExists('civicrm_payment_processor', 'url_api') || !CRM_Core_DAO::checkFieldExists('civicrm_payment_processor_type', 'url_api_default') || !CRM_Core_DAO::checkFieldExists('civicrm_phone', 'contact_id') || !CRM_Core_DAO::checkFieldExists('civicrm_phone', 'location_type_id') || !CRM_Core_DAO::checkFieldExists('civicrm_uf_match', 'uf_name')) {
// db looks to have stuck somewhere between 2.0 & 2.1
$errorMessage .= ' Few important fields were found missing in some of the tables.';
$is20db = false;
}
}
if (!$is20db) {
// check if the db is 2.1
if (CRM_Core_DAO::checkTableExists('civicrm_cache') && CRM_Core_DAO::checkFieldExists('civicrm_cache', 'group_name') && CRM_Core_DAO::checkFieldExists('civicrm_cache', 'created_date') && CRM_Core_DAO::checkFieldExists('civicrm_cache', 'expired_date') && CRM_Core_DAO::checkTableExists('civicrm_group_contact_cache') && CRM_Core_DAO::checkFieldExists('civicrm_group_contact_cache', 'contact_id') && CRM_Core_DAO::checkTableExists('civicrm_menu') && CRM_Core_DAO::checkFieldExists('civicrm_menu', 'path_arguments') && CRM_Core_DAO::checkFieldExists('civicrm_menu', 'is_exposed') && CRM_Core_DAO::checkFieldExists('civicrm_menu', 'page_type') && CRM_Core_DAO::checkTableExists('civicrm_discount') && CRM_Core_DAO::checkFieldExists('civicrm_discount', 'option_group_id') && CRM_Core_DAO::checkFieldExists('civicrm_discount', 'end_date')) {
$errorMessage = ts('Database check failed - it looks like you have already upgraded to the latest version (v2.1) of the database.');
}
return false;
}
// check tables which should not exist for v2.x
if (CRM_Core_DAO::checkTableExists('civicrm_custom_option') || CRM_Core_DAO::checkTableExists('civicrm_custom_value') || CRM_Core_DAO::checkTableExists('civicrm_email_history') || CRM_Core_DAO::checkTableExists('civicrm_geo_coord') || CRM_Core_DAO::checkTableExists('civicrm_individual') || CRM_Core_DAO::checkTableExists('civicrm_location') || CRM_Core_DAO::checkTableExists('civicrm_meeting') || CRM_Core_DAO::checkTableExists('civicrm_organization') || CRM_Core_DAO::checkTableExists('civicrm_phonecall') || CRM_Core_DAO::checkTableExists('civicrm_sms_history') || CRM_Core_DAO::checkTableExists('civicrm_validation')) {
// table(s) found in the db which are no longer required
// for v2.x, though would not do any harm it's recommended
// to remove them.
CRM_Core_Session::setStatus(ts("Table(s) found in your db which are no longer required for v2.x, though would not do any harm it's recommended to remove them"));
}
// show error if any of the tables, use 'MyISAM' storage engine.
// just check the first 10 civicrm tables, rather than checking all 106!
if (CRM_Core_DAO::isDBMyISAM(10)) {
$errorMessage = ts('Your database is configured to use the MyISAM database engine. CiviCRM requires InnoDB. You will need to convert any MyISAM tables in your database to InnoDB before proceeding.');
return false;
}
// check FK constraint names are in valid format.
if (!CRM_Core_DAO::checkFKConstraintInFormat('civicrm_contact', 'domain_id') || !CRM_Core_DAO::checkFKConstraintInFormat('civicrm_contribution_page', 'domain_id') || !CRM_Core_DAO::checkFKConstraintInFormat('civicrm_contribution_recur', 'domain_id') || !CRM_Core_DAO::checkFKConstraintInFormat('civicrm_membership_status', 'domain_id') || !CRM_Core_DAO::checkFKConstraintInFormat('civicrm_contribution', 'contact_id') || !CRM_Core_DAO::checkFKConstraintInFormat('civicrm_contribution', 'contribution_type_id')) {
$errorMessage = ts('Database consistency check failed. FK constraint names not in the required format. Please rebuild your 2.0 database to ensure schema integrity.');
return false;
}
return true;
}
/**
* Upgrade 1030 - add data for report projectintake issue 3498
*/
public function upgrade_1030()
{
$this->ctx->log->info('Applying update 1030 add data and view for projectintake');
// add columns assess_rep/cc/sc/anamon_date/customer
if (CRM_Core_DAO::checkTableExists('civicrm_pum_case_reports')) {
$columns = array('0' => array('name' => 'anamon_id'), '1' => array('name' => 'country_coordinator_id', 'type' => 'VARCHAR(45)'), '2' => array('name' => 'project_officer_id'), '3' => array('name' => 'sector_coordinator_id'));
foreach ($columns as $column) {
if (!CRM_Core_DAO::checkFieldExists('civicrm_pum_case_reports', $column['name'])) {
CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_pum_case_reports ADD COLUMN ' . $column['name'] . ' INT(11) DEFAULT NULL');
}
}
}
$this->createPumProjectIntakeView();
return true;
}
public function upgrade_1403()
{
$this->ctx->log->info('Applying update 1403');
if (CRM_Core_DAO::checkTableExists("civicrm_hrjob_hour")) {
if (!CRM_Core_DAO::checkFieldExists('civicrm_hrjob_hour', 'fte_num')) {
CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_hrjob_hour ADD COLUMN fte_num int unsigned DEFAULT 1 COMMENT "." AFTER hours_fte');
}
if (!CRM_Core_DAO::checkFieldExists('civicrm_hrjob_hour', 'fte_denom')) {
CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_hrjob_hour ADD COLUMN fte_denom int unsigned DEFAULT 1 COMMENT "." AFTER fte_num');
}
}
$result = civicrm_api3('HRJobHour', 'get');
foreach ($result['values'] as $key => $value) {
$fteFraction = CRM_HRJob_Upgrader::decToFraction($value['hours_fte']);
CRM_Core_DAO::executeQuery("update civicrm_hrjob_hour set fte_num={$fteFraction[0]} , fte_denom={$fteFraction[1]} where id = {$value['id']}");
}
return TRUE;
}
protected function checkTableExists(array $tables)
{
$result = array();
foreach ($tables as $table) {
$result[$table] = CRM_Core_DAO::checkTableExists($table);
}
return $result;
}
/**
* Conditionally fire an event during the first page run.
*
* The install system is currently implemented several times, so it's hard to add
* new installation logic. We use a poor-man's method to detect the first run.
*
* Situations to test:
* - New installation
* - Upgrade from an old version (predating first-run tracker)
* - Upgrade from an old version (with first-run tracking)
*/
public function handleFirstRun()
{
// Ordinarily, we prefetch settings en masse and find that the system is already installed.
// No extra SQL queries required.
if (Civi::settings()->get('installed')) {
return;
}
// Q: How should this behave during testing?
if (defined('CIVICRM_TEST')) {
return;
}
// If schema hasn't been loaded yet, then do nothing. Don't want to interfere
// with the existing installers. NOTE: If we change the installer pageflow,
// then we may want to modify this behavior.
if (!CRM_Core_DAO::checkTableExists('civicrm_domain')) {
return;
}
// If we're handling an upgrade, then the system has already been used, so this
// is not the first run.
if (CRM_Core_Config::isUpgradeMode()) {
return;
}
$dao = CRM_Core_DAO::executeQuery('SELECT version FROM civicrm_domain');
while ($dao->fetch()) {
if ($dao->version && version_compare($dao->version, CRM_Utils_System::version(), '<')) {
return;
}
}
// The installation flag is stored in civicrm_setting, which is domain-aware. The
// flag could have been stored under a different domain.
$dao = CRM_Core_DAO::executeQuery('
SELECT domain_id, value FROM civicrm_setting
WHERE is_domain = 1 AND name = "installed"
');
while ($dao->fetch()) {
$value = unserialize($dao->value);
if (!empty($value)) {
Civi::settings()->set('installed', 1);
return;
}
}
// OK, this looks new.
Civi::service('dispatcher')->dispatch(\Civi\Core\Event\SystemInstallEvent::EVENT_NAME, new \Civi\Core\Event\SystemInstallEvent());
Civi::settings()->set('installed', 1);
}
/**
* @param $formValues
*/
public function __construct(&$formValues)
{
$this->_formValues = $formValues;
$this->tableName = 'civicrm_temp_custom_late_recurring_search';
if (CRM_Core_DAO::checkTableExists($this->tableName)) {
$query = "DROP TABLE {$this->tableName}";
CRM_Core_DAO::executeQuery($query);
}
$creatSql = "CREATE TABLE {$this->tableName}\n SELECT \n r.id,\n r.contact_id,\n r.amount, r.frequency_unit,\n r.frequency_interval, \n r.start_date,\n r.cancel_date,\n r.end_date, \n r.next_sched_contribution_date,\n c.receive_date ,\n c.id as contribution_id,\n r.start_date as expected_date\n FROM civicrm_contribution_recur r \n LEFT JOIN civicrm_contribution c ON c.receive_date = (SELECT MAX(tmp.receive_date) FROM civicrm_contribution tmp WHERE r.id = tmp.contribution_recur_id AND tmp.is_test = 0 AND (tmp.contribution_status_id = 1 OR tmp.contribution_status_id = 8 ))\n WHERE r.cancel_date IS NULL GROUP BY r.id";
CRM_Core_DAO::executeQuery($creatSql);
$selectQuery = "SELECT receive_date, id, start_date, frequency_unit, frequency_interval, next_sched_contribution_date, expected_date, end_date FROM civicrm_temp_custom_late_recurring_search ";
$dao = CRM_Core_DAO::executeQuery($selectQuery);
while ($dao->fetch()) {
// month frquency
if (!empty($dao->receive_date) && $dao->frequency_unit == 'month') {
// For example, if the RC's start date is 2015-02-01 and the RC is monthly and the most recent payment was made on 2015-07-31,
// then the expected date of payment will be 2015-08-01,
// which is calculated by taking the start date and adding the lowest number of months
// (because it's a monthly RC) to get a date after the most recent payment.
//To fix this, please can the expected date of the next payment be calculated as the RC's start date plus the lowest multiple of the RC's interval that is after the most recent payment?
//(Unless 'next_sched_contribution_date' is set to a date after the last payment was received, in which case that should be used.)
$startDate = CRM_Utils_Date::processDate($dao->start_date);
$lastReceiveDate = CRM_Utils_Date::processDate($dao->receive_date);
$ModifiedDate[0] = date('y', strtotime(date("Y-m-d", strtotime($lastReceiveDate)) . " +" . $dao->frequency_interval . " month"));
$ModifiedDate[1] = date("m", strtotime(date("Y-m-d", strtotime($lastReceiveDate)) . " +" . $dao->frequency_interval . " month"));
$ModifiedDate[2] = date("d", strtotime($startDate));
$newDate = implode('-', $ModifiedDate);
// Calculate expected date from last payment
$expectedDate = date("Y-m-d H:i:s", strtotime($newDate));
$nextschduleDate = date("Y-m-d H:i:s", strtotime($dao->next_sched_contribution_date));
// If next scheduled date is great than last payment date, then change expected date to next scheduled date
if (!empty($nextschduleDate) && date("Y-m-d H:i:s", strtotime($lastReceiveDate)) <= $nextschduleDate) {
$expectedDate = $nextschduleDate;
}
$recurringId = $dao->id;
$updateQuery = "UPDATE `civicrm_temp_custom_late_recurring_search` SET expected_date = '{$expectedDate}' WHERE id = {$recurringId}";
CRM_Core_DAO::executeQuery($updateQuery);
//http://support.vedaconsulting.co.uk/issues/365
//The spec says that the search "will not look for missed payments after the recurring contribution's end date, if set."
if (!empty($dao->end_date) && $expectedDate > $dao->end_date) {
$recurringId = $dao->id;
$deleteQuery = "DELETE FROM `civicrm_temp_custom_late_recurring_search` WHERE id = {$recurringId}";
CRM_Core_DAO::executeQuery($deleteQuery);
}
//http://support.vedaconsulting.co.uk/issues/700
//please can you allow for a margin of error: An expected payment should count as being paid if it was received in the seven days before the expected date.
$nofoMonths = 0;
//if (!empty($this->_formValues['start_date'])) {
//$beforeDate = CRM_Utils_Date::processDate($this->_formValues['start_date']);
//$nofoMonths = (int)abs((strtotime($lastReceiveDate) - strtotime($expectedDate))/(60*60*24*30));
//}
if (date("Y-m-d", strtotime($lastReceiveDate)) <= date("Y-m-d", strtotime($expectedDate)) && date("Y-m-d", strtotime($lastReceiveDate)) >= date("Y-m-d", strtotime('-7 day' . $expectedDate))) {
$newexpectedDate = CRM_Utils_Date::processDate($expectedDate);
$ModifiedDate = array();
$ModifiedDate[0] = date('y', strtotime(date("Y-m-d", strtotime($newexpectedDate)) . " +" . $dao->frequency_interval . " month"));
$ModifiedDate[1] = date("m", strtotime(date("Y-m-d", strtotime($newexpectedDate)) . " +" . $dao->frequency_interval . " month"));
$ModifiedDate[2] = date("d", strtotime($newexpectedDate));
$newExpectedDate = implode('-', $ModifiedDate);
// Calculate expected date from last payment
$updatedExpectedDate = date("Y-m-d H:i:s", strtotime($newExpectedDate));
$recurringId = $dao->id;
$updateQuery = "UPDATE `civicrm_temp_custom_late_recurring_search` SET expected_date = '{$updatedExpectedDate}' WHERE id = {$recurringId}";
CRM_Core_DAO::executeQuery($updateQuery);
}
/*if ($nofoMonths > 0) {
if (!empty($dao->next_sched_contribution_date)) {
$newexpectedDate = CRM_Utils_Date::processDate($expectedDate);
$ModifiedDate = array();
$ModifiedDate[0] = date('y', strtotime(date("Y-m-d", strtotime($newexpectedDate)) . " +".$dao->frequency_interval." month"));
$ModifiedDate[1] = date("m",strtotime(date("Y-m-d", strtotime($newexpectedDate)) . " +".$dao->frequency_interval." month"));
$ModifiedDate[2] = date("d", strtotime($newexpectedDate));
$newExpectedDate = implode('-',$ModifiedDate);
// Calculate expected date from last payment
$updatedExpectedDate = date("Y-m-d H:i:s", strtotime($newExpectedDate));
$recurringId = $dao->id;
$updateQuery = "UPDATE `civicrm_temp_custom_late_recurring_search` SET expected_date = '{$updatedExpectedDate}' WHERE id = {$recurringId}";
CRM_Core_DAO::executeQuery($updateQuery);
}
} else {
if ((date("Y-m-d", strtotime($lastReceiveDate)) <= date("Y-m-d", strtotime($expectedDate))) &&
(date("Y-m-d", strtotime($lastReceiveDate)) >= date("Y-m-d", strtotime ( '-7 day' . $expectedDate )))
) {
$recurringId = $dao->id;
$deleteQuery = "DELETE FROM `civicrm_temp_custom_late_recurring_search` WHERE id = {$recurringId}";
CRM_Core_DAO::executeQuery($deleteQuery);
}
}*/
}
// year frequency
if (!empty($dao->receive_date) && $dao->frequency_unit == 'year') {
$lastReceiveDate = CRM_Utils_Date::processDate($dao->receive_date);
$startDate = CRM_Utils_Date::processDate($dao->start_date);
//$ModifiedDate[0] = date('y', strtotime(date('Ymd')));
$ModifiedDate[0] = date("y", strtotime(date("Y-m-d", strtotime($lastReceiveDate)) . " +" . $dao->frequency_interval . " year"));
$ModifiedDate[1] = date('m', strtotime($startDate));
$ModifiedDate[2] = date("d", strtotime($startDate));
$newDate = implode('-', $ModifiedDate);
$expectedDate = date("Y-m-d H:i:s", strtotime($newDate));
$nextschduleDate = date("Y-m-d H:i:s", strtotime($dao->next_sched_contribution_date));
// If next scheduled date is great than last payment date, then change expected date to next scheduled date
if (!empty($nextschduleDate) && date("Y-m-d H:i:s", strtotime($lastReceiveDate)) <= $nextschduleDate) {
$expectedDate = $nextschduleDate;
}
$recurringId = $dao->id;
$updateQuery = "UPDATE `civicrm_temp_custom_late_recurring_search` SET expected_date = '{$expectedDate}' WHERE id = {$recurringId}";
CRM_Core_DAO::executeQuery($updateQuery);
//http://support.vedaconsulting.co.uk/issues/365
//The spec says that the search "will not look for missed payments after the recurring contribution's end date, if set."
if (!empty($dao->end_date) && $expectedDate > $dao->end_date) {
$recurringId = $dao->id;
$deleteQuery = "DELETE FROM `civicrm_temp_custom_late_recurring_search` WHERE id = {$recurringId}";
CRM_Core_DAO::executeQuery($deleteQuery);
}
//http://support.vedaconsulting.co.uk/issues/700
//please can you allow for a margin of error: An expected payment should count as being paid if it was received in the seven days before the expected date.
if (date("Y-m-d", strtotime($lastReceiveDate)) <= date("Y-m-d", strtotime($expectedDate)) && date("Y-m-d", strtotime($lastReceiveDate)) >= date("Y-m-d", strtotime('-7 day' . $expectedDate))) {
$recurringId = $dao->id;
$deleteQuery = "DELETE FROM `civicrm_temp_custom_late_recurring_search` WHERE id = {$recurringId}";
CRM_Core_DAO::executeQuery($deleteQuery);
}
}
}
// Define the columns for search result rows
$this->_columns = array(ts('Name') => 'sort_name', ts('Last paid') => 'receive_date', ts('Expected') => 'expected_date', ts('Amount') => 'amount', ts('Frequency') => 'frequency');
// define component access permission needed
$this->_permissionedComponent = 'CiviContribute';
}
/**
* @param $rev
*/
public function upgrade_4_3_beta5($rev)
{
// CRM-12205
if (CRM_Core_DAO::checkTableExists('log_civicrm_financial_trxn') && CRM_Core_DAO::checkFieldExists('log_civicrm_financial_trxn', 'trxn_id')) {
CRM_Core_DAO::executeQuery('ALTER TABLE `log_civicrm_financial_trxn` CHANGE `trxn_id` `trxn_id` VARCHAR(255) NULL DEFAULT NULL');
}
// CRM-12142 - some sites didn't get this column added yet, and sites which installed 4.3 from scratch will already have it
// CRM-12367 - add this column to single lingual sites only
$upgrade = new CRM_Upgrade_Form();
if (!$upgrade->multilingual && !CRM_Core_DAO::checkFieldExists('civicrm_premiums', 'premiums_nothankyou_label')) {
$query = "\nALTER TABLE civicrm_premiums\nADD COLUMN premiums_nothankyou_label varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL\n COMMENT 'Label displayed for No Thank-you option in premiums block (e.g. No thank you)'\n";
CRM_Core_DAO::executeQuery($query, array(), TRUE, NULL, FALSE, FALSE);
}
$this->addTask(ts('Upgrade DB to %1: SQL', array(1 => '4.3.beta5')), 'task_4_3_x_runSql', $rev);
}
/**
* Upgrade function.
*
* @param string $rev
*/
public function upgrade_4_7_7($rev)
{
$this->addTask(ts('Upgrade DB to %1: SQL', array(1 => $rev)), 'runSql', $rev);
// https://issues.civicrm.org/jira/browse/CRM-18006
if (CRM_Core_DAO::checkTableExists('civicrm_install_canary')) {
CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_install_canary ENGINE=InnoDB');
}
}
public static function filter($params)
{
$roleContactsIds = array();
if (CRM_Core_DAO::checkTableExists('civicrm_hrjobroles') && CRM_Core_DAO::checkTableExists('civicrm_hrjobcontract')) {
$availableTags = array('department', 'level_type', 'region', 'location');
$rolesContactsWhere = array();
foreach ($availableTags as $tag) {
if (!empty($params['tags'][$tag])) {
$rolesContactsWhere[] = "r.{$tag} IN (" . implode(', ', array_map('intval', $params['tags'][$tag])) . ")";
}
}
if (!empty($rolesContactsWhere)) {
$rolesContactsWhere[] = "hrjc.deleted = 0";
$rolesContactsQuery = "SELECT c.id, c.sort_name FROM civicrm_contact c " . "INNER JOIN civicrm_hrjobcontract hrjc ON hrjc.contact_id = c.id " . "INNER JOIN civicrm_hrjobroles r ON r.job_contract_id = hrjc.id " . "WHERE " . implode(" AND ", $rolesContactsWhere);
echo 'query: ' . $rolesContactsQuery;
$rolesContactsResult = CRM_Core_DAO::executeQuery($rolesContactsQuery);
while ($rolesContactsResult->fetch()) {
echo $rolesContactsResult->id . ', ' . $rolesContactsResult->sort_name . '<br/>';
$roleContactsIds[] = $rolesContactsResult->id;
}
if (!empty($params['contact_id'])) {
if (!in_array((int) $params['contact_id'], $roleContactsIds)) {
return array();
}
$roleContactsIds = array((int) $params['contact_id']);
}
}
}
if (!empty($params['contact_id']) && empty($roleContactsIds)) {
$roleContactsIds = array((int) $params['contact_id']);
}
$getParams = array();
if (!empty($params['appraisal_cycle_id'])) {
$getParams['appraisal_cycle_id'] = (int) $params['appraisal_cycle_id'];
}
if (!empty($roleContactsIds)) {
$getParams['contact_id'] = array('IN' => $roleContactsIds);
}
if (!empty($params['status_id'])) {
$getParams['status_id'] = array('IN' => $params['status_id']);
}
$getParams['self_appraisal_due'] = array('BETWEEN' => array(empty($params['self_appraisal_due_from']) ? '0000-00-00' : $params['self_appraisal_due_from'], empty($params['self_appraisal_due_to']) ? '9999-00-00' : $params['self_appraisal_due_to']));
$getParams['manager_appraisal_due'] = array('BETWEEN' => array(empty($params['manager_appraisal_due_from']) ? '0000-00-00' : $params['manager_appraisal_due_from'], empty($params['manager_appraisal_due_to']) ? '9999-00-00' : $params['manager_appraisal_due_to']));
$getParams['grade_due'] = array('BETWEEN' => array(empty($params['grade_due_from']) ? '0000-00-00' : $params['grade_due_from'], empty($params['grade_due_to']) ? '9999-00-00' : $params['grade_due_to']));
if (!empty($params['sequential'])) {
$getParams['sequential'] = $params['sequential'];
}
if (!empty($params['options'])) {
$getParams['options'] = $params['options'];
}
$result = civicrm_api3('Appraisal', 'get', $getParams);
return $result;
}
function insertSmartDebitToTable()
{
// If no civicrm_sd, then create that table
if (!CRM_Core_DAO::checkTableExists('civicrm_sd_refresh')) {
$creatSql = "CREATE TABLE `civicrm_sd_refresh` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `first_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `last_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `email_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `address_1` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `address_2` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `address_3` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `town` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `county` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `postcode` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `first_amount` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `regular_amount` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `frequency_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `frequency_factor` int(10) unsigned DEFAULT NULL,\n `start_date` datetime NOT NULL,\n `current_state` int(10) unsigned DEFAULT NULL,\t\t\t\t\t\n `reference_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n `payerReference` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,\n PRIMARY KEY (`id`)\n ) ENGINE=InnoDB AUTO_INCREMENT=350 DEFAULT CHARSET=latin1";
CRM_Core_DAO::executeQuery($creatSql);
$alterQuery = "alter table civicrm_sd_refresh add index reference_number_idx(reference_number)";
CRM_Core_DAO::executeQuery($alterQuery);
} else {
$emptySql = "TRUNCATE TABLE `civicrm_sd_refresh`";
CRM_Core_DAO::executeQuery($emptySql);
}
$smartDebitArray = self::getSmartDebitPayments(NULL);
CRM_Core_Error::debug_var('smart debit array count', count($smartDebitArray));
foreach ($smartDebitArray as $key => $smartDebitRecord) {
$sql = "INSERT INTO `civicrm_sd_refresh`(\n `title`,\n `first_name`,\n `last_name`, \n `email_address`,\n `address_1`, \n `address_2`, \n `address_3`, \n `town`, \n `county`,\n `postcode`,\n `first_amount`,\n `regular_amount`,\n `frequency_type`,\n `frequency_factor`,\n `start_date`,\n `current_state`,\n `reference_number`,\n `payerReference`\n ) \n VALUES (%1,%2,%3,%4,%5,%6,%7,%8,%9,%10,%11,%12,%13,%14,%15,%16,%17, %18)";
$params = array(1 => array($smartDebitRecord['title'] ? $smartDebitRecord['title'] : 'NULL', 'String'), 2 => array($smartDebitRecord['first_name'] ? $smartDebitRecord['first_name'] : 'NULL', 'String'), 3 => array($smartDebitRecord['last_name'] ? $smartDebitRecord['last_name'] : 'NULL', 'String'), 4 => array($smartDebitRecord['email_address'] ? $smartDebitRecord['email_address'] : 'NULL', 'String'), 5 => array($smartDebitRecord['address_1'] ? $smartDebitRecord['address_1'] : 'NULL', 'String'), 6 => array($smartDebitRecord['address_2'] ? $smartDebitRecord['address_2'] : 'NULL', 'String'), 7 => array($smartDebitRecord['address_3'] ? $smartDebitRecord['address_3'] : 'NULL', 'String'), 8 => array($smartDebitRecord['town'] ? $smartDebitRecord['town'] : 'NULL', 'String'), 9 => array($smartDebitRecord['county'] ? $smartDebitRecord['county'] : 'NULL', 'String'), 10 => array($smartDebitRecord['postcode'] ? $smartDebitRecord['postcode'] : 'NULL', 'String'), 11 => array($smartDebitRecord['first_amount'] ? $smartDebitRecord['first_amount'] : 'NULL', 'String'), 12 => array($smartDebitRecord['regular_amount'] ? $smartDebitRecord['regular_amount'] : 'NULL', 'String'), 13 => array($smartDebitRecord['frequency_type'] ? $smartDebitRecord['frequency_type'] : 'NULL', 'String'), 14 => array($smartDebitRecord['frequency_factor'] ? $smartDebitRecord['frequency_factor'] : NULL, 'Int'), 15 => array($smartDebitRecord['start_date'] ? $smartDebitRecord['start_date'] : 'NULL', 'String'), 16 => array($smartDebitRecord['current_state'] ? $smartDebitRecord['current_state'] : 0, 'Int'), 17 => array($smartDebitRecord['reference_number'] ? $smartDebitRecord['reference_number'] : 'NULL', 'String'), 18 => array($smartDebitRecord['payerReference'] ? $smartDebitRecord['payerReference'] : 'NULL', 'String'));
// CRM_Core_Error::debug_var('$params', $params);
CRM_Core_DAO::executeQuery($sql, $params);
}
}
