/** * Execute the script * * @param void * @return boolean */ function execute() { // --------------------------------------------------- // Check MySQL version // --------------------------------------------------- $mysql_version = mysql_get_server_info($this->database_connection); if ($mysql_version && version_compare($mysql_version, '4.1', '>=')) { $constants['DB_CHARSET'] = 'utf8'; @mysql_query("SET NAMES 'utf8'", $this->database_connection); tpl_assign('default_collation', $default_collation = 'collate utf8_unicode_ci'); tpl_assign('default_charset', $default_charset = 'DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'); } else { tpl_assign('default_collation', $default_collation = ''); tpl_assign('default_charset', $default_charset = ''); } // if $installed_version = installed_version(); $t_prefix = TABLE_PREFIX; if (version_compare($installed_version, '1.7.5') <= 0 && TABLE_PREFIX != "fo_") { $t_prefix = "fo_"; } tpl_assign('table_prefix', $t_prefix); if (defined('DB_ENGINE')) { tpl_assign('engine', DB_ENGINE); } else { tpl_assign('engine', 'InnoDB'); } // --------------------------------------------------- // Execute migration // --------------------------------------------------- $additional_upgrade_steps = array(); // RUN QUERIES $total_queries = 0; $executed_queries = 0; $upgrade_script = ""; // upgrading from version 1.x if (version_compare($installed_version, '2.0.0.0-beta') < 0) { ini_set('memory_limit', '1024M'); @set_time_limit(0); $upgrade_script .= tpl_fetch(get_template_path('db_migration/2_0_asado')); if ($this->executeMultipleQueries($upgrade_script, $total_queries, $executed_queries, $this->database_connection)) { $this->printMessage("Database schema transformations executed (total queries: {$total_queries})"); } else { $this->printMessage('Failed to execute DB schema transformations. MySQL said: ' . mysql_error(), true); return false; } $_SESSION['from_feng1'] = true; $upgrade_script = ""; @unlink(ROOT . '/cache/autoloader.php'); include ROOT . '/environment/classes/AutoLoader.class.php'; include ROOT . '/environment/constants.php'; if (!($callbacks = spl_autoload_functions())) { $callbacks = array(); } foreach ($callbacks as $callback) { spl_autoload_unregister($callback); } spl_autoload_register('feng_upg_autoload'); foreach ($callbacks as $callback) { spl_autoload_register($callback); } @(include ROOT . '/cache/autoloader.php'); define('DONT_LOG', true); define('FORCED_TABLE_PREFIX', 'fo_'); if (!defined('FILE_STORAGE_FILE_SYSTEM')) { define('FILE_STORAGE_FILE_SYSTEM', 'fs'); } if (!defined('FILE_STORAGE_MYSQL')) { define('FILE_STORAGE_MYSQL', 'mysql'); } if (!defined('MAX_SEARCHABLE_FILE_SIZE')) { define('MAX_SEARCHABLE_FILE_SIZE', 1048576); } try { DB::connect(DB_ADAPTER, array('host' => DB_HOST, 'user' => DB_USER, 'pass' => DB_PASS, 'name' => DB_NAME, 'persist' => DB_PERSIST)); if (defined('DB_CHARSET') && trim(DB_CHARSET)) { DB::execute("SET NAMES ?", DB_CHARSET); } } catch (Exception $e) { $this->printMessage("Error connecting to database: " . $e->getMessage() . "\n" . $e->getTraceAsString()); } try { $db_result = DB::execute("SELECT value FROM " . $t_prefix . "config_options WHERE name = 'file_storage_adapter'"); $db_result_row = $db_result->fetchRow(); if ($db_result_row['value'] == FILE_STORAGE_FILE_SYSTEM) { if (!defined('FILES_DIR')) { define('FILES_DIR', ROOT . '/upload'); } FileRepository::setBackend(new FileRepository_Backend_FileSystem(FILES_DIR, TABLE_PREFIX)); } else { FileRepository::setBackend(new FileRepository_Backend_DB(TABLE_PREFIX)); } PublicFiles::setRepositoryPath(ROOT . '/public/files'); if (!defined('PUBLIC_FOLDER')) { define('PUBLIC_FOLDER', 'public'); } if (trim(PUBLIC_FOLDER) == '') { PublicFiles::setRepositoryUrl(with_slash(ROOT_URL) . 'files'); } else { PublicFiles::setRepositoryUrl(with_slash(ROOT_URL) . 'public/files'); } $member_parents = array(); $members = Members::findAll(); foreach ($members as $member) { $member_parents[$member->getId()] = $member->getAllParentMembersInHierarchy(false, false); } $object_members = DB::executeAll('SELECT * FROM ' . $t_prefix . 'object_members WHERE is_optimization=0 and not exists (SELECT x.object_id FROM ' . $t_prefix . 'object_members x where x.object_id=fo_object_members.object_id and x.is_optimization=1)'); foreach ($object_members as $om) { $parents = isset($member_parents[$om['member_id']]) ? $member_parents[$om['member_id']] : array(); if (count($parents) > 0) { $sql_values = ""; foreach ($parents as $p) { $sql_values .= ($sql_values == "" ? "" : ",") . "(" . $om['object_id'] . "," . $p->getId() . ",1)"; } $sql = "INSERT INTO " . $t_prefix . "object_members (object_id, member_id, is_optimization) VALUES {$sql_values} ON DUPLICATE KEY UPDATE is_optimization=1;"; DB::execute($sql); } } $this->printMessage("Finished generating Object Members"); foreach ($members as $m) { if ($m->getParentMember() instanceof Member && $m->getDimensionId() != $m->getParentMember()->getDimensionId()) { $m->setDimensionId($m->getParentMember()->getDimensionId()); $m->save(); } } $app_move_logs = ApplicationLogs::findAll(array("conditions" => "action = 'move'")); foreach ($app_move_logs as &$app_log) { /* @var $app_log ApplicationLog */ $exp_log_data = explode(";", $app_log->getLogData()); if (count($exp_log_data) > 1) { $old_to = array_var($exp_log_data, 1); $old_from = array_var($exp_log_data, 0); } else { $old_to = array_var($exp_log_data, 0); $old_from = ""; } $to_id = str_replace("to:", "", $old_to); $new_to_id = Members::instance()->findOne(array("id" => true, "conditions" => "ws_id = '{$to_id}'")); if (count($new_to_id) > 0) { $new_to_id = $new_to_id[0]; } $new_from_ids = ""; $from_ids = str_replace("from:", "", $old_from); if ($from_ids != "") { $new_from_ids_array = Members::instance()->findAll(array("id" => true, "conditions" => "ws_id IN ({$from_ids})")); $new_from_ids = implode(",", $new_from_ids_array); } if ($new_to_id) { if ($new_from_ids) { $log_data = "from:{$new_from_ids};to:{$new_to_id}"; } else { $log_data = "to:{$new_to_id}"; } $app_log->setLogData($log_data); $app_log->save(); } } } catch (Exception $e) { die("\nError occurred:\n-----------------\n" . $e->getMessage() . "\n" . $e->getTraceAsString()); } //tpl_assign('install_inv_dw', true); $additional_upgrade_steps[] = array('url' => 'complete_migration.php?out=file', 'name' => 'Fill searchable objects and sharing table', 'filename' => dirname(__FILE__) . "/../complete_migration.php"); } else { // upgrading from a pre-release of this version (beta, rc, etc) if (version_compare($installed_version, '2.0.0.4') <= 0) { if (!$this->checkTableExists($t_prefix . 'role_object_type_permissions', $this->database_connection)) { $upgrade_script .= "\r\n\t\t\t\t\t\tCREATE TABLE `" . $t_prefix . "role_object_type_permissions` (\r\n\t\t\t\t\t\t `role_id` INTEGER UNSIGNED NOT NULL,\r\n\t\t\t\t\t\t `object_type_id` INTEGER UNSIGNED NOT NULL,\r\n\t\t\t\t\t\t `can_delete` BOOLEAN NOT NULL,\r\n\t\t\t\t\t\t `can_write` BOOLEAN NOT NULL,\r\n\t\t\t\t\t\t PRIMARY KEY (`role_id`, `object_type_id`)\r\n\t\t\t\t\t\t) ENGINE = InnoDB;\r\n\t\t\t\t\t\tINSERT INTO " . $t_prefix . "role_object_type_permissions (role_id, object_type_id, can_delete, can_write)\r\n\t\t\t\t\t\t SELECT p.id, o.id, 1, 1\r\n\t\t\t\t\t\t FROM `" . $t_prefix . "object_types` o JOIN `" . $t_prefix . "permission_groups` p\r\n\t\t\t\t\t\t WHERE o.`name` IN ('message','weblink','file','task','milestone','event','contact','mail','timeslot','report','comment')\r\n\t\t\t\t\t\t AND p.`name` IN ('Super Administrator','Administrator','Manager','Executive');\r\n\t\t\t\t\t\tINSERT INTO " . $t_prefix . "role_object_type_permissions (role_id, object_type_id, can_delete, can_write)\r\n\t\t\t\t\t\t SELECT p.id, o.id, 0, 1\r\n\t\t\t\t\t\t FROM `" . $t_prefix . "object_types` o JOIN `" . $t_prefix . "permission_groups` p\r\n\t\t\t\t\t\t WHERE o.`name` IN ('message','weblink','file','task','milestone','event','contact','timeslot','report','comment')\r\n\t\t\t\t\t\t AND p.`name` IN ('Collaborator Customer');\r\n\t\t\t\t\t\tINSERT INTO " . $t_prefix . "role_object_type_permissions (role_id, object_type_id, can_delete, can_write)\r\n\t\t\t\t\t\t SELECT p.id, o.id, 0, 1\r\n\t\t\t\t\t\t FROM `" . $t_prefix . "object_types` o JOIN `" . $t_prefix . "permission_groups` p\r\n\t\t\t\t\t\t WHERE o.`name` IN ('message','weblink','file','task','milestone','event','timeslot','comment')\r\n\t\t\t\t\t\t AND p.`name` IN ('Internal Collaborator','External Collaborator');\r\n\t\t\t\t\t\tINSERT INTO " . $t_prefix . "role_object_type_permissions (role_id, object_type_id, can_delete, can_write)\r\n\t\t\t\t\t\t SELECT p.id, o.id, 0, 0\r\n\t\t\t\t\t\t FROM `" . $t_prefix . "object_types` o JOIN `" . $t_prefix . "permission_groups` p\r\n\t\t\t\t\t\t WHERE o.`name` IN ('message','weblink','file','event','comment')\r\n\t\t\t\t\t\t AND p.`name` IN ('Guest Customer');\r\n\t\t\t\t\t\tINSERT INTO " . $t_prefix . "role_object_type_permissions (role_id, object_type_id, can_delete, can_write)\r\n\t\t\t\t\t\t SELECT p.id, o.id, 0, 0\r\n\t\t\t\t\t\t FROM `" . $t_prefix . "object_types` o JOIN `" . $t_prefix . "permission_groups` p\r\n\t\t\t\t\t\t WHERE o.`name` IN ('message','weblink','event','comment')\r\n\t\t\t\t\t\t AND p.`name` IN ('Guest');\r\n\t\t\t\t\t\tINSERT INTO " . $t_prefix . "role_object_type_permissions (role_id, object_type_id, can_delete, can_write)\r\n\t\t\t\t\t\t SELECT p.id, o.id, 0, 0\r\n\t\t\t\t\t\t FROM `" . $t_prefix . "object_types` o JOIN `" . $t_prefix . "permission_groups` p\r\n\t\t\t\t\t\t WHERE o.`name` IN ('message','weblink','file','task','milestone','event','contact','timeslot','report','comment')\r\n\t\t\t\t\t\t AND p.`name` IN ('Non-Exec Director');\r\n\t\t\t\t\t\tUPDATE " . $t_prefix . "role_object_type_permissions SET can_write = 1 WHERE object_type_id = (SELECT id FROM " . $t_prefix . "object_types WHERE name='comment');\r\n\t\t\t\t\t"; } if (!$this->checkTableExists($t_prefix . 'widgets', $this->database_connection)) { $upgrade_script .= "\r\n\t\t\t\t\t\tCREATE TABLE `" . $t_prefix . "widgets` (\r\n\t\t\t\t\t\t `name` varchar(64) NOT NULL,\r\n\t\t\t\t\t\t `title` varchar(255) NOT NULL,\r\n\t\t\t\t\t\t `plugin_id` int(10) unsigned NOT NULL,\r\n\t\t\t\t\t\t `path` varchar(512) NOT NULL,\r\n\t\t\t\t\t\t `default_options` text NOT NULL,\r\n\t\t\t\t\t\t `default_section` varchar(64) NOT NULL,\r\n\t\t\t\t\t\t `default_order` int(10) NOT NULL,\r\n\t\t\t\t\t\t PRIMARY KEY (`name`)\r\n\t\t\t\t\t\t) ENGINE = InnoDB;\r\n\t\t\t\t\t"; } if ($this->executeMultipleQueries($upgrade_script, $total_queries, $executed_queries, $this->database_connection)) { $this->printMessage("Database schema transformations executed (total queries: {$total_queries})"); } else { $this->printMessage('Failed to execute DB schema transformations. MySQL said: ' . mysql_error(), true); return false; } } if (version_compare($installed_version, '2.0.0.5') <= 0) { if (!$this->checkColumnExists($t_prefix . 'contacts', 'default_billing_id', $this->database_connection)) { $upgrade_script = "\r\n\t\t\t\t\t\tALTER TABLE `" . $t_prefix . "contacts` ADD COLUMN `default_billing_id` INTEGER NOT NULL DEFAULT 0;\r\n\t\t\t\t\t\tALTER TABLE `" . $t_prefix . "project_tasks`\r\n\t\t\t\t\t\t ADD COLUMN `use_due_time` BOOLEAN DEFAULT 0,\r\n\t\t\t\t\t\t ADD COLUMN `use_start_time` BOOLEAN DEFAULT 0;\r\n\t\t\t\t\t\tUPDATE " . $t_prefix . "project_tasks t SET\r\n\t\t\t\t\t\t t.due_date = ADDTIME(t.due_date, CONCAT(SUBSTRING_INDEX((SELECT c.timezone FROM " . $t_prefix . "contacts c WHERE c.object_id=(SELECT o.updated_by_id FROM " . $t_prefix . "objects o WHERE o.id=t.object_id)), '.', 1), ':', SUBSTRING_INDEX(abs((SELECT c.timezone FROM " . $t_prefix . "contacts c WHERE c.object_id=(SELECT o.updated_by_id FROM " . $t_prefix . "objects o WHERE o.id=t.object_id)) % 1)*60, '.', 1)))\r\n\t\t\t\t\t\t WHERE t.due_date > 0;\r\n\t\t\t\t\t\tUPDATE " . $t_prefix . "project_tasks t SET\r\n\t\t\t\t\t\t t.start_date = ADDTIME(t.start_date, CONCAT(SUBSTRING_INDEX((SELECT c.timezone FROM " . $t_prefix . "contacts c WHERE c.object_id=(SELECT o.updated_by_id FROM " . $t_prefix . "objects o WHERE o.id=t.object_id)), '.', 1), ':', SUBSTRING_INDEX(abs((SELECT c.timezone FROM " . $t_prefix . "contacts c WHERE c.object_id=(SELECT o.updated_by_id FROM " . $t_prefix . "objects o WHERE o.id=t.object_id)) % 1)*60, '.', 1)))\r\n\t\t\t\t\t\t WHERE t.start_date > 0;\r\n\t\t\t\t\t\tINSERT INTO `" . $t_prefix . "contact_config_options` (`category_name`, `name`, `default_value`, `config_handler_class`, `is_system`, `option_order`, `dev_comment`) VALUES\r\n\t\t\t\t\t\t ('general', 'work_day_end_time', '18:00', 'TimeConfigHandler', 0, 410, 'Work day end time');\t\t\t\t\t\t\r\n\t\t\t\t\t"; } if ($this->executeMultipleQueries($upgrade_script, $total_queries, $executed_queries, $this->database_connection)) { $this->printMessage("Database schema transformations executed (total queries: {$total_queries})"); } else { $this->printMessage('Failed to execute DB schema transformations. MySQL said: ' . mysql_error(), true); return false; } } if (version_compare($installed_version, '2.0.0.6') <= 0) { //WS Widgets $upgrade_script = "\r\n\t\t\t\t\tUPDATE `" . $t_prefix . "contact_config_options` SET `default_value` = '15' WHERE `" . $t_prefix . "contact_config_options`.`name` = 'noOfTasks' LIMIT 1 ;\r\n\t\t\t\t\tUPDATE " . $t_prefix . "widgets SET default_section = 'none' WHERE name = 'people' AND NOT EXISTS (SELECT id from " . $t_prefix . "plugins WHERE name = 'crpm');\r\n\t\t\t\t\tUPDATE " . $t_prefix . "dimensions SET options = '{\"defaultAjax\":{\"controller\":\"dashboard\", \"action\": \"main_dashboard\"}, \"quickAdd\":true,\"showInPaths\":true}' \r\n\t\t\t\t\t\tWHERE code='workspaces';\r\n\t\t\t\t\tUPDATE `" . $t_prefix . "tab_panels` SET default_action = 'main_dashboard', initial_action = 'main_dashboard'\r\n\t\t\t\t\t\tWHERE id = 'overview-panel' ;\r\n\t\t\t\t\tUPDATE " . $t_prefix . "object_types SET type = 'dimension_object', handler_class='Workspaces', table_name = 'workpaces' WHERE name = 'workspace' ;\r\n\t\t\t\t\tUPDATE " . $t_prefix . "dimension_object_types SET OPTIONS = '{\"defaultAjax\":{\"controller\":\"dashboard\", \"action\": \"main_dashboard\"}}' \r\n\t\t\t\t\t\tWHERE dimension_id = (SELECT id FROM " . $t_prefix . "dimensions WHERE code = 'workspaces');\r\n\t\t\t\t\tCREATE TABLE IF NOT EXISTS `" . $t_prefix . "contact_widgets` (\r\n\t\t\t\t\t `widget_name` varchar(40) NOT NULL,\r\n\t\t\t\t\t `contact_id` int(11) NOT NULL,\r\n\t\t\t\t\t `section` varchar(40) NOT NULL,\r\n\t\t\t\t\t `order` int(11) NOT NULL,\r\n\t\t\t\t\t `options` varchar(255) NOT NULL,\r\n\t\t\t\t\t PRIMARY KEY (`widget_name`,`contact_id`) USING BTREE\r\n\t\t\t\t\t) ENGINE=InnoDB;\r\n\t\t\t\t\tINSERT INTO " . $t_prefix . "widgets(name, title, plugin_id, default_section,default_order) \r\n\t\t\t\t\t VALUES ('messages','notes',0,'none',1000)\r\n\t\t\t\t\t ON DUPLICATE KEY update name = name;\r\n\t\t\t\t\tINSERT INTO " . $t_prefix . "dimension_object_type_contents (dimension_id, dimension_object_type_id, content_object_type_id, is_required, is_multiple)\r\n\t\t\t\t\t SELECT d.id, ot.id, (SELECT tmp.id FROM " . $t_prefix . "object_types tmp WHERE tmp.name='contact'), 0, 1\r\n\t\t\t\t\t FROM " . $t_prefix . "dimensions d JOIN " . $t_prefix . "object_types ot\r\n\t\t\t\t\t WHERE d.code = 'customer_project' AND ot.name IN ('customer', 'project', 'folder', 'customer_folder', 'project_folder')\r\n\t\t\t\t\tON DUPLICATE KEY UPDATE dimension_id=dimension_id;\r\n\t\t\t\t\tUPDATE " . $t_prefix . "dimension_object_type_contents SET is_multiple = 1 WHERE content_object_type_id = (SELECT id FROM " . $t_prefix . "object_types WHERE name='mail');\r\n\t\t\t\t"; if (@mysql_fetch_row(@mysql_query("SELECT id from " . $t_prefix . "plugins WHERE name = 'workspaces'"))) { $upgrade_script .= "INSERT INTO " . $t_prefix . "widgets(name, title, plugin_id, default_section,default_order) \r\n\t\t\t\t\t\tVALUES ('ws_description', 'workspace description',(SELECT id from " . $t_prefix . "plugins WHERE name = 'workspaces'), 'left',-100)\r\n\t\t\t\t\t\tON DUPLICATE KEY update name = name ;"; } if ($this->executeMultipleQueries($upgrade_script, $total_queries, $executed_queries, $this->database_connection)) { $this->printMessage("Database schema transformations executed (total queries: {$total_queries})"); } else { $this->printMessage('Failed to execute DB schema transformations. MySQL said: ' . mysql_error(), true); return false; } if ($obj = @mysql_fetch_object(@mysql_query("SELECT id FROM " . $t_prefix . "object_types WHERE name = 'workspace' "))) { $wsTypeId = $obj->id; $res = @mysql_query("SELECT * FROM " . $t_prefix . "members WHERE dimension_id = (SELECT id FROM " . $t_prefix . "dimensions WHERE code='workspaces')"); while ($m = @mysql_fetch_object($res)) { @mysql_query("INSERT INTO " . $t_prefix . "objects (object_type_id, name) VALUES ({$wsTypeId}, '" . $m->name . "' )"); if ($id = @mysql_insert_id()) { @mysql_query("INSERT INTO " . $t_prefix . "workspaces (object_id) VALUES ({$id})"); @mysql_query("UPDATE " . $t_prefix . "members SET object_id={$id} WHERE id = {$m->id} "); } } } } if (version_compare($installed_version, '2.0.0.7') <= 0) { $upgrade_script = ""; if (!$this->checkTableExists($t_prefix . 'mail_spam_filters', $this->database_connection)) { $upgrade_script .= "\r\n CREATE TABLE IF NOT EXISTS `" . $t_prefix . "mail_spam_filters` (\r\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\r\n `account_id` int(10) unsigned NOT NULL,\r\n `text_type` enum('email_address','subject') COLLATE utf8_unicode_ci NOT NULL,\r\n `text` text COLLATE utf8_unicode_ci NOT NULL,\r\n `spam_state` enum('no spam','spam') COLLATE utf8_unicode_ci NOT NULL,\r\n PRIMARY KEY (`id`)\r\n ) ENGINE=InnoDB;\r\n "; } $upgrade_script .= "INSERT INTO `" . $t_prefix . "config_options` (`category_name`, `name`, `value`, `config_handler_class`, `is_system`, `option_order`, `dev_comment`) \r\n\t\t\t\t\tVALUES ('general', 'untitled_notes', '0', 'BoolConfigHandler', '0', '0', NULL) ON DUPLICATE KEY UPDATE name=name;"; if ($this->executeMultipleQueries($upgrade_script, $total_queries, $executed_queries, $this->database_connection)) { $this->printMessage("Database schema transformations executed (total queries: {$total_queries})"); } else { $this->printMessage('Failed to execute DB schema transformations. MySQL said: ' . mysql_error(), true); return false; } } if (version_compare($installed_version, '2.0.0.8') < 0) { $upgrade_script = ""; if (!$this->checkTableExists($t_prefix . 'external_calendar_users', $this->database_connection)) { $upgrade_script .= "\r\n CREATE TABLE IF NOT EXISTS `" . $t_prefix . "external_calendar_users` (\r\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\r\n `contact_id` int(10) unsigned NOT NULL,\r\n `auth_user` varchar(100) COLLATE utf8_unicode_ci NOT NULL,\r\n `auth_pass` varchar(100) COLLATE utf8_unicode_ci NOT NULL,\r\n `type` text COLLATE utf8_unicode_ci NOT NULL,\r\n `sync` TINYINT( 1 ) NULL DEFAULT '0',\r\n PRIMARY KEY (`id`)\r\n ) ENGINE = InnoDB;\r\n\t\t\t\t\t"; } if (!$this->checkTableExists($t_prefix . 'external_calendars', $this->database_connection)) { $upgrade_script .= "\r\n CREATE TABLE IF NOT EXISTS `" . $t_prefix . "external_calendars` (\r\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\r\n `ext_cal_user_id` int(10) unsigned NOT NULL,\r\n `calendar_user` varchar(255) COLLATE utf8_unicode_ci NOT NULL,\r\n `calendar_visibility` varchar(255) COLLATE utf8_unicode_ci NOT NULL,\r\n `calendar_name` text COLLATE utf8_unicode_ci NOT NULL,\r\n `calendar_feng` TINYINT( 1 ) NOT NULL DEFAULT '0',\r\n PRIMARY KEY (`id`)\r\n ) ENGINE = InnoDB;\r\n\t\t\t\t\t"; } if (!$this->checkColumnExists($t_prefix . 'project_events', 'ext_cal_id', $this->database_connection)) { $upgrade_script .= "\r\n\t\t\t\t\t\tALTER TABLE `" . $t_prefix . "project_events` ADD `ext_cal_id` INT(10) UNSIGNED NOT NULL;\r\n\t\t\t\t\t"; } $upgrade_script .= "\r\n\t\t\t\t\tALTER TABLE `" . $t_prefix . "project_events` CHANGE `special_id` `special_id` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;\r\n\t\t\t\t\tUPDATE `" . $t_prefix . "file_types` SET `is_searchable` = '1' WHERE `extension` = 'docx';\r\n\t\t\t\t\tUPDATE `" . $t_prefix . "file_types` SET `is_searchable` = '1' WHERE `extension` = 'pdf';\r\n\t\t\t\t\tINSERT INTO `" . $t_prefix . "config_options` (`category_name`, `name`, `value`, `config_handler_class`, `is_system`, `option_order`, `dev_comment`)\r\n\t\t\t\t\t\tVALUES ('general', 'repeating_task', '0', 'BoolConfigHandler', '0', '0', '')\r\n\t\t\t\t\tON DUPLICATE KEY UPDATE name=name;\r\n\t\t\t\t\tINSERT INTO `" . $t_prefix . "contact_config_options` (`category_name`, `name`, `default_value`, `config_handler_class`, `is_system`, `option_order`, `dev_comment`)\r\n\t\t\t\t\t\tVALUES ('calendar panel', 'calendar task filter', 'pending', 'StringConfigHandler', '1', '0', NULL),\r\n\t\t\t\t\t\t\t('task panel', 'close timeslot open', '1', 'BoolConfigHandler', '0', '0', NULL),\r\n\t\t\t\t\t\t\t('calendar panel', 'reminders_events', 'reminder_email,1,60', 'StringConfigHandler', '0', '0', NULL)\r\n\t\t\t\t\tON DUPLICATE KEY UPDATE name=name;\r\n\t\t\t\t\tINSERT INTO `" . $t_prefix . "cron_events` (`name`, `recursive`, `delay`, `is_system`, `enabled`, `date`)\r\n\t\t\t\t\t\tVALUES ('import_google_calendar', '1', '10', '0', '0', '0000-00-00 00:00:00'),\r\n\t\t\t\t\t\t\t('export_google_calendar', '1', '10', '0', '0', '0000-00-00 00:00:00')\r\n\t\t\t\t\tON DUPLICATE KEY UPDATE name=name;\r\n\t\t\t\t\t"; $upgrade_script .= "\r\n\t\t\t\t\tDELETE FROM `" . $t_prefix . "config_options` WHERE `name`='use_time_in_task_dates' AND NOT EXISTS (SELECT id FROM `" . $t_prefix . "plugins` WHERE `name`='crpm' AND is_activated=1);\r\n\t\t\t\t\tINSERT INTO " . $t_prefix . "contact_config_options (category_name, name, default_value, config_handler_class, is_system, option_order) VALUES\r\n\t\t\t\t\t\t('general','show_object_direct_url',0,'BoolConfigHandler',0,0),\r\n\t\t\t\t\t\t('general','drag_drop_prompt','prompt','DragDropPromptConfigHandler',0,0)\r\n\t\t\t\t\t ON DUPLICATE KEY UPDATE name = name;\r\n\t\t\t\t"; $upgrade_script .= "\r\n\t\t\t\t\tINSERT INTO `" . $t_prefix . "tab_panels` (`id`,`title`,`icon_cls`,`refresh_on_context_change`,`default_controller`,`default_action`,`initial_controller`,`initial_action`,`enabled`,`type`,`ordering`,`plugin_id`,`object_type_id`) VALUES \r\n\t\t\t\t\t('contacts-panel','contacts','ico-contacts',1,'contact','init','','',0,'system',7,0,16) ON DUPLICATE KEY UPDATE title=title;\r\n\t\t\t\t"; if ($this->executeMultipleQueries($upgrade_script, $total_queries, $executed_queries, $this->database_connection)) { $this->printMessage("Database schema transformations executed (total queries: {$total_queries})"); } else { $this->printMessage('Failed to execute DB schema transformations. MySQL said: ' . mysql_error(), true); return false; } } if (version_compare($installed_version, '2.0.1') < 0) { $upgrade_script = ""; $upgrade_script .= "INSERT INTO `" . $t_prefix . "config_options` (`category_name`, `name`, `value`, `config_handler_class`, `is_system`, `option_order`, `dev_comment`)\r\n\t\t\t\t\tVALUES ('general', 'working_days', '1,2,3,4,5,6,7', 'StringConfigHandler', '0', '0', NULL);\r\n\t\t\t\t\tALTER TABLE `" . $t_prefix . "project_tasks` ADD `original_task_id` INT( 10 ) UNSIGNED NULL DEFAULT '0';\r\n\t\t\t\t\tALTER TABLE `" . $t_prefix . "project_tasks` ADD `type_content` ENUM( 'text', 'html' ) NOT NULL DEFAULT 'text';\r\n\t\t\t\t\tALTER TABLE `" . $t_prefix . "project_events` ADD `original_event_id` INT( 10 ) UNSIGNED NULL DEFAULT '0';\r\n\t\t\t\t\tALTER TABLE `" . $t_prefix . "project_messages` ADD `type_content` ENUM( 'text', 'html' ) NOT NULL DEFAULT 'text';\r\n\t\t\t\t"; $upgrade_script .= "INSERT INTO `" . $t_prefix . "config_options` (`category_name`, `name`, `value`, `config_handler_class`, `is_system`, `option_order`, `dev_comment`)\r\n\t\t\t\t\tVALUES ('general', 'wysiwyg_tasks', '0', 'BoolConfigHandler', '0', '0', NULL),\r\n\t\t\t\t\t('general', 'wysiwyg_messages', '0', 'BoolConfigHandler', '0', '0', NULL),\r\n\t\t\t\t\t('task panel', 'tasksShowTimeEstimates', '1', 'BoolConfigHandler', '1', '0', NULL)\r\n\t\t\t\tON DUPLICATE KEY UPDATE name=name;\r\n\t\t\t\t"; $upgrade_script .= "UPDATE `" . $t_prefix . "widgets` SET plugin_id = (SELECT id FROM `" . $t_prefix . "plugins` WHERE name='workspaces') WHERE name='workspaces';\r\n\t\t\t\t"; // clean old users dimension $upgrade_script .= "DELETE FROM `" . $t_prefix . "object_members` WHERE member_id IN (SELECT `id` FROM `" . $t_prefix . "members` WHERE `dimension_id` IN (SELECT `id` FROM `" . $t_prefix . "dimensions` WHERE `code`='feng_users'));\r\n\t\t\t\t\tDELETE FROM `" . $t_prefix . "contact_dimension_permissions` WHERE dimension_id IN (SELECT `id` FROM `" . $t_prefix . "dimensions` WHERE `code`='feng_users');\r\n\t\t\t\t\tDELETE FROM `" . $t_prefix . "members` WHERE dimension_id IN (SELECT `id` FROM `" . $t_prefix . "dimensions` WHERE `code`='feng_users');\r\n\t\t\t\t\tDELETE FROM `" . $t_prefix . "dimension_object_type_contents` WHERE dimension_id IN (SELECT `id` FROM `" . $t_prefix . "dimensions` WHERE `code`='feng_users');\r\n\t\t\t\t\tDELETE FROM `" . $t_prefix . "dimension_object_type_hierarchies` WHERE dimension_id IN (SELECT `id` FROM `" . $t_prefix . "dimensions` WHERE `code`='feng_users');\r\n\t\t\t\t\tDELETE FROM `" . $t_prefix . "dimension_object_types` WHERE dimension_id IN (SELECT `id` FROM `" . $t_prefix . "dimensions` WHERE `code`='feng_users');\r\n\t\t\t\t\tDELETE FROM `" . $t_prefix . "dimensions` WHERE code='feng_users';\r\n\t\t\t\t\tDELETE FROM `" . $t_prefix . "object_types` WHERE name='user';\r\n\t\t\t\t\tUPDATE " . $t_prefix . "contacts c SET c.personal_member_id = 0 WHERE c.user_type>0 AND NOT (SELECT count(m2.id) FROM " . $t_prefix . "members m2 WHERE m2.object_id=c.personal_member_id)=0;\r\n\t\t\t\t"; if ($this->executeMultipleQueries($upgrade_script, $total_queries, $executed_queries, $this->database_connection)) { $this->printMessage("Database schema transformations executed (total queries: {$total_queries})"); } else { $this->printMessage('Failed to execute DB schema transformations. MySQL said: ' . mysql_error(), true); return false; } } // Plugin Version Support $upgrade_script = ''; if (!$this->checkColumnExists($t_prefix . "plugins", 'version', $this->database_connection)) { $upgrade_script = 'ALTER TABLE ' . $t_prefix . 'plugins ADD COLUMN `version` INTEGER NOT NULL DEFAULT 1 AFTER `name` '; if ($this->executeMultipleQueries($upgrade_script, $total_queries, $executed_queries, $this->database_connection)) { $this->printMessage("Database schema transformations executed (total queries: {$total_queries})"); } else { $this->printMessage('Failed to execute DB schema transformations. MySQL said: ' . mysql_error(), true); return false; } } } $this->printMessage('Feng Office has been upgraded. You are now running Feng Office ' . $this->getVersionTo() . ' Enjoy!'); tpl_assign('additional_steps', $additional_upgrade_steps); }
static function getLastActivities() { $members = active_context_members(false); // Context Members Ids $options = explode(",", user_config_option("filters_dashboard", null, null, true)); $extra_conditions = "action <> 'login' AND action <> 'logout' AND action <> 'subscribe' AND created_by_id > '0'"; if ($options[1] == 0) { //do not show timeslots $extra_conditions .= "AND action <> 'open' AND action <> 'close' AND ((action <> 'add' OR action <> 'edit' OR action <> 'delete') AND object_name NOT LIKE 'Time%')"; } // task assignment conditions if (!SystemPermissions::userHasSystemPermission(logged_user(), 'can_see_assigned_to_other_tasks')) { $extra_conditions .= " AND IF((SELECT o.object_type_id FROM " . TABLE_PREFIX . "objects o WHERE o.id=rel_object_id)=(SELECT ot.id FROM " . TABLE_PREFIX . "object_types ot WHERE ot.name='task'),\r\n\t\t\t\t(SELECT t.assigned_to_contact_id FROM " . TABLE_PREFIX . "project_tasks t WHERE t.object_id=rel_object_id) = " . logged_user()->getId() . ",\r\n\t\t\t\ttrue)"; } //do not display template tasks logs $extra_conditions .= " AND IF((SELECT o.object_type_id FROM " . TABLE_PREFIX . "objects o WHERE o.id=rel_object_id)=(SELECT ot.id FROM " . TABLE_PREFIX . "object_types ot WHERE ot.name='template_task'), false, true)"; // if logged user is guest dont show other users logs if (logged_user()->isGuest()) { $extra_conditions .= " AND `created_by_id`=" . logged_user()->getId(); } $members_sql = ""; $is_member_child = ""; if (count($members) > 0) { $members_sql = "(EXISTS(\r\n\t\t\t\tSELECT om.object_id FROM " . TABLE_PREFIX . "object_members om\r\n\t\t\t\tWHERE om.member_id IN (" . implode(',', $members) . ") AND rel_object_id = om.object_id\r\n\t\t\t\tGROUP BY object_id\r\n\t\t\t\tHAVING count(member_id) = " . count($members) . "\r\n\t\t\t))"; $is_member_child = "AND mem.parent_member_id IN (" . implode(',', $members) . ")"; } //permissions $logged_user_pgs = implode(',', logged_user()->getPermissionGroupIds()); $permissions_condition = "al.rel_object_id IN (\r\n\t\tSELECT sh.object_id FROM " . TABLE_PREFIX . "sharing_table sh\r\n\t\tWHERE al.rel_object_id = sh.object_id AND sh.object_id > 0\r\n\t\tAND sh.group_id IN ({$logged_user_pgs})\r\n\t\t)"; $sql = "SELECT al.id FROM " . TABLE_PREFIX . "application_logs al \r\n\t\t\t\tWHERE {$permissions_condition} AND {$extra_conditions}"; if ($members_sql != "") { $sql .= " AND {$members_sql}"; //do not display users logs $sql .= " AND NOT EXISTS(SELECT con.object_id FROM " . TABLE_PREFIX . "contacts con WHERE con.object_id=rel_object_id AND user_type > 0)"; } $sql .= " ORDER BY created_on DESC LIMIT 100"; $id_rows = array_flat(DB::executeAll($sql)); // if logged user is guest dont show other users logs $user_condition = ""; if (logged_user()->isGuest()) { $user_condition .= " AND `created_by_id`=" . logged_user()->getId(); } $member_logs_sql = "SELECT al.id FROM " . TABLE_PREFIX . "application_logs al\r\n\t\t\t\t\t\t\t\t\tINNER JOIN " . TABLE_PREFIX . "members mem ON mem.id=al.member_id \r\n\t\t\t\t\t\t\t\t\t\tINNER JOIN " . TABLE_PREFIX . "contact_member_cache cmcache ON cmcache.member_id=mem.id AND cmcache.contact_id = " . logged_user()->getId() . "\r\n\t\t\t\t\t\t\t\t\t\t\tWHERE al.member_id>0\r\n\t\t\t\t\t\t\t\t\t\t\t{$user_condition}\r\n\t\t\t\t\t\t\t\t\t\t\t{$is_member_child}\r\n\t\t\t\t\t\t\tORDER BY created_on DESC LIMIT 100"; $m_id_rows = array_flat(DB::executeAll($member_logs_sql)); $id_rows = array_filter(array_merge($id_rows, $m_id_rows)); $logs = array(); if (count($id_rows) > 0) { $logs = ApplicationLogs::findAll(array("condition" => "id IN (" . implode(',', $id_rows) . ")", "order" => "created_on DESC")); } return $logs; }
static function getLastActivities() { $members = active_context_members(false); // Context Members Ids $options = explode(",",user_config_option("filters_dashboard",null,null,true)); $extra_conditions = "action <> 'login' AND action <> 'logout' AND action <> 'subscribe' "; if($options[1] == 0){//do not show timeslots $extra_conditions .= "AND action <> 'open' AND action <> 'close' AND ((action <> 'add' OR action <> 'edit' OR action <> 'delete') AND object_name NOT LIKE 'Time%')"; } // task assignment conditions if (!SystemPermissions::userHasSystemPermission(logged_user(), 'can_see_assigned_to_other_tasks')) { $extra_conditions .= " AND IF((SELECT o.object_type_id FROM ".TABLE_PREFIX."objects o WHERE o.id=rel_object_id)=(SELECT ot.id FROM ".TABLE_PREFIX."object_types ot WHERE ot.name='task'), (SELECT t.assigned_to_contact_id FROM ".TABLE_PREFIX."project_tasks t WHERE t.object_id=rel_object_id) = ".logged_user()->getId().", true)"; } $members_sql = ""; if(count($members) > 0){ $object_ids_rows = DB::executeAll("SELECT object_id FROM " . TABLE_PREFIX . "object_members om WHERE member_id IN (" . implode ( ',', $members ) . ") GROUP BY object_id HAVING count(member_id) = ".count($members).""); $object_ids = implode(',', array_flat($object_ids_rows)); if ($object_ids == "") $object_ids = "0"; $members_sql = "rel_object_id IN ($object_ids)"; } $permissions_sql = "AND rel_object_id IN ( SELECT object_id FROM ".TABLE_PREFIX."sharing_table WHERE group_id IN (SELECT permission_group_id FROM ".TABLE_PREFIX."contact_permission_groups WHERE contact_id = ".logged_user()->getId().") )"; $condition = ($members_sql != "" ? $members_sql . " AND " : "") . $extra_conditions . $permissions_sql; return ApplicationLogs::findAll(array( "condition" => $condition, "order" => "created_on DESC", "limit" => "100" )); }
function send_reports() { $company = Companies::findById(1); $lTime = time() + 60 * 60 * $company->getTimezone(); $dayOfWeek = date("l", $lTime); $footer = '<a href="' . externalUrl(ROOT_URL) . '">' . externalUrl(ROOT_URL) . "</a>"; $people = Reminders::findAll(array('conditions' => 'reports_enabled = 1 and report_day = "' . $dayOfWeek . '"')); if (is_array($people) && count($people)) { foreach ($people as $person) { tpl_assign('settings', $person); $user = Users::findById($person->getUserId()); tpl_assign('user', $user); $offset = 60 * 60 * $user->getTimezone(); tpl_assign('offset', $offset); $allProjects = $user->getProjects(); $emailBody = ''; $recipient = Notifier::prepareEmailAddress($user->getEmail(), $user->getDisplayName()); foreach ($allProjects as $project) { if ($project->isActive() || $project->getCompletedOn()->getLeftInDays() > -7) { tpl_assign('project', $project); $condition = 'project_id = ' . $project->getId(); $condition .= " and is_private = 0 and is_silent = 0"; if (!$person->getReportsIncludeEveryone()) { $condition .= ' and taken_by_id = ' . $user->getId(); } $logs = array(); if ($person->getReportsIncludeActivity()) { $condition .= " and created_on > Interval -7 day + now()"; $logs = ApplicationLogs::findAll(array('conditions' => $condition)); } tpl_assign('logs', $logs); $taskLists = $project->getAllTaskLists(); $emailTaskLists = array(); if (is_array($taskLists) && count($taskLists)) { foreach ($taskLists as $taskList) { $condition = 'task_list_id = ' . $taskList->getId(); if (!$person->getReportsIncludeEveryone()) { $condition .= " and assigned_to_user_id = " . $user->getId(); } $condition .= " and completed_on > Interval -7 day + now()"; $tasks = ProjectTasks::findAll(array('conditions' => $condition)); if (is_array($tasks) && count($tasks)) { $emailTaskLists[] = $taskList; } } } if (count($emailTaskLists) || count($logs)) { tpl_assign('taskLists', $emailTaskLists); $emailBody .= tpl_fetch(get_template_path('report_per_project', 'reminders')); if ($person->getReportsSummarizedBy()) { try { Notifier::sendEmail($recipient, $recipient, "[ProjectPier] - Project Report - " . $project->getObjectName(), $emailBody . $footer, 'text/html'); // send $emailBody = ''; } catch (Exception $e) { echo $e; } } } } } if (strlen($emailBody) && !$person->getReportsSummarizedBy()) { $time = time() + 60 * 60 * $user->getTimezone(); try { Notifier::sendEmail($recipient, $recipient, "[ProjectPier] - Activity Report - " . gmdate('Y/m/d', $time), $emailBody . $footer, 'text/html'); // send $emailBody = ''; } catch (Exception $e) { echo $e; } } } } }