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