/**
* standard constructor
* @access public
**/
public function __construct($user = '')
{
global $cdg_sql;
if (empty($user)) {
$user = '******';
}
$obm_q = new DB_OBM();
$query = "SELECT usersystem_password as password FROM UserSystem WHERE usersystem_login='******'";
display_debug_msg($query, $cdg_sql, "SatelliteCredentials::__construct()");
$obm_q->query($query);
if (!$obm_q->next_record()) {
throw new Exception("Can't find {$user} system user");
}
//else
parent::__construct($user, $obm_q->f('password'));
}
public static function search($searchPattern=null) {
if($searchPattern !== null) {
$query = " AND AddressBook.id= #INT($searchPattern)";
}
$db = new DB_OBM;
$addressBooks = array();
$db->xquery('
SELECT
AddressBook.id,
AddressBook.owner,
AddressBook.name,
AddressBook.is_default,
AddressBook.syncable,
1 as entityright_access,
1 as entityright_read,
1 as entityright_write,
1 as entityright_admin,
SyncedAddressbook.user_id as synced
FROM AddressBook
LEFT JOIN SyncedAddressbook ON SyncedAddressbook.addressbook_id = AddressBook.id AND SyncedAddressbook.user_id = '.$GLOBALS['obm']['uid'].'
WHERE AddressBook.owner = '.$GLOBALS['obm']['uid'].' '.$query.' ORDER BY AddressBook.is_default DESC, AddressBook.name');
while($db->next_record()) {
$addressBooks[$db->f('id')] = new OBM_AddressBook($db->f('id'), $db->f('name'), $db->f('is_default'), $db->f('owner'), $db->f('syncable'), $db->f('synced'), $db->f('entityright_access'),
$db->f('entityright_read'), $db->f('entityright_write'),$db->f('entityright_admin'));
}
$columns = array('addressbookentity_addressbook_id', 'entityright_access', 'entityright_read', 'entityright_write', 'entityright_admin');
$db->xquery('
SELECT
AddressBook.id,
AddressBook.owner,
AddressBook.name,
AddressBook.is_default,
AddressBook.syncable,
Rights.entityright_access,
Rights.entityright_read,
Rights.entityright_write,
Rights.entityright_admin,
SyncedAddressbook.user_id as synced
FROM AddressBook
INNER JOIN ('.OBM_Acl::getAclSubselect($columns, 'addressbook', null, $GLOBALS['obm']['uid']).') AS Rights ON AddressBook.id = Rights.addressbookentity_addressbook_id
LEFT JOIN SyncedAddressbook ON SyncedAddressbook.addressbook_id = AddressBook.id AND SyncedAddressbook.user_id = '.$GLOBALS['obm']['uid'].'
WHERE 1=1 '.$query.' AND AddressBook.domain_id = '.$GLOBALS['obm']['domain_id'].' ORDER BY AddressBook.name');
while($db->next_record()) {
if($addressBooks[$db->f('id')]) {
if($db->f('entityright_access') == 1) $addressBooks[$db->f('id')]->access = 1;
if($db->f('entityright_read') == 1) $addressBooks[$db->f('id')]->read = 1;
if($db->f('entityright_write') == 1) $addressBooks[$db->f('id')]->write = 1;
if($db->f('entityright_admin') == 1) $addressBooks[$db->f('id')]->admin = 1;
} else {
$addressBooks[$db->f('id')] = new OBM_AddressBook($db->f('id'), $db->f('name'), $db->f('is_default'), $db->f('owner'), $db->f('syncable'), $db->f('synced'), $db->f('entityright_access'),
$db->f('entityright_read'), $db->f('entityright_write'),$db->f('entityright_admin'));
}
}
return new OBM_AddressBookArray($addressBooks);
}
function getEventAttendees($eventId){
$db = new DB_OBM;
$query = "SELECT userobm.*
FROM event
INNER JOIN eventlink ON event.event_id = eventlink.eventlink_event_id
INNER JOIN userentity ON eventlink.eventlink_entity_id = userentity.userentity_entity_id
INNER JOIN userobm ON userentity.userentity_user_id = userobm.userobm_id
WHERE event_id = '".$eventId."'";
$db->query($query);
if($db->nf() == 0)
return null;
$attendees = array();
while($db->next_record()){
$attendees[$db->f("userobm_id")] = $this->db->Record;
}
return $attendees;
}
if (is_numeric($options['u'])) {
$userobm = "WHERE userobm_id = {$options['u']}";
}
$query = "SELECT userobm_id, userobm_login, id, name from UserObm INNER JOIN AddressBook ON owner = userobm_id {$userobm} GROUP BY userobm_id";
$obm_q = new DB_OBM();
$obm_q->query($query);
$obm_q2 = new DB_OBM();
while ($obm_q->next_record()) {
$query = "SELECT \n contact_id, contact_lastname, contact_firstname, \n e1.email_address as contact_email,\n e2.email_address as contact_email2,\n WorkPhone.phone_number as contact_phone,\n MobilePhone.phone_number as contact_mobilephone,\n HomePhone.phone_number as contact_homephone,\n contact_archive,\n OtherPhone.phone_number as contact_otherphone\n FROM Contact \n INNER JOIN ContactEntity ON contactentity_contact_id = contact_id\n LEFT JOIN Email e1 ON e1.email_entity_id = contactentity_entity_id AND e1.email_label = 'INTERNET;X-OBM-Ref1'\n LEFT JOIN Email e2 ON e2.email_entity_id = contactentity_entity_id AND e2.email_label = 'INTERNET;X-OBM-Ref1'\n LEFT JOIN Phone as WorkPhone ON WorkPhone.phone_entity_id = contactentity_entity_id AND WorkPhone.phone_label = 'WORK;VOICE;X-OBM-Ref1'\n LEFT JOIN Phone as MobilePhone ON MobilePhone.phone_entity_id = contactentity_entity_id AND MobilePhone.phone_label = 'CELL;VOICE;X-OBM-Ref1'\n LEFT JOIN Phone as HomePhone ON HomePhone.phone_entity_id = contactentity_entity_id AND HomePhone.phone_label = 'HOME;VOICE;X-OBM-Ref1'\n LEFT JOIN Phone as OtherPhone ON OtherPhone.phone_entity_id = contactentity_entity_id AND OtherPhone.phone_label = 'OTHER;VOICE;X-OBM-Ref1'\n WHERE contact_addressbook_id = " . $obm_q->f('id') . " \n ORDER BY contact_id";
$obm_q2->query($query);
echo "===================================================\n";
echo "For user " . $obm_q->f('userobm_login') . " (" . $obm_q->f('userobm_id') . "), Addressbook " . $obm_q->f('name') . " (" . $obm_q->f('id') . ") : " . $obm_q2->nf() . " contacts\n";
$contacts = array();
while ($obm_q2->next_record()) {
$datas = array_map('clean_values', $obm_q2->Record);
$contacts[$obm_q2->f('contact_id')] = $datas;
}
$duples = array_duples($contacts);
echo count($duples) . " contacts to delete\n";
if (count($duples) > 0 && !$dry) {
$query = "INSERT INTO DeletedContact SELECT contact_id, contact_usercreate, NOW() , 'kill' FROM Contact WHERE contact_id IN (" . implode(',', $duples) . ")";
$obm_q2->query($query);
}
}
if (!$dry) {
$query = "DELETE FROM Entity WHERE entity_id IN (SELECT contactentity_entity_id FROM ContactEntity INNER JOIN DeletedContact ON deletedcontact_contact_id = contactentity_contact_id)";
$obm_q->query($query);
$query = "DELETE FROM Contact WHERE contact_id IN (SELECT deletedcontact_contact_id FROM DeletedContact)";
$obm_q->query($query);
}
function clean_values($item)
/**
* getVacationToInsert
*
* @param mixed $end_time
* @access public
* @return void
*/
function getVacationToRemove($date) {
$vacation = array();
$obm_q = new DB_OBM;
$db_type = $obm_q->type;
$vacation_dateend = sql_date_format($db_type,"userobm_vacation_dateend");
$obm_q = new DB_OBM;
$query = "SELECT userobm_id, userobm_login, userobm_domain_id, userobm_vacation_enable FROM UserObm
WHERE userobm_mail_perms = 1 AND userobm_vacation_dateend <= '$date' AND userobm_vacation_dateend IS NOT NULL";
$this->logger->core($query);
$obm_q->query($query);
$this->logger->info($obm_q->nf()." vacations to disable");
while($obm_q->next_record()) {
$vacation[$obm_q->f('userobm_id')] = array("login" => $obm_q->f('userobm_login'), "domain" => $obm_q->f('userobm_domain_id'), "enable" => $obm_q->f('userobm_vacation_enable'));
}
$this->logger->info('List of vacation to disable : '.implode(',', array_keys($vacation)));
return $vacation;
}
function get_document_mime_type($id) {
global $cdg_sql;
$query = "SELECT documentmimetype_extension
FROM Document
JOIN DocumentMimeType ON document_mimetype_id = documentmimetype_id
WHERE document_id = '$id'";
display_debug_msg($query, $cdg_sql);
$obm_q = new DB_OBM;
$obm_q->query($query);
if ($obm_q->num_rows() == 1){
$obm_q->next_record();
return $obm_q->f("documentmimetype_extension");
}
else
return "";
}
public function getValue($entity_id) {
$id = of_entity_get($this->entity, $entity_id);
$db = new DB_OBM();
$query = "SELECT * FROM field WHERE entity_id = '$id'";
$db->query($query);
$ret = array();
while($db->next_record()) {
$f = $db->f('field');
if ($this->fields[$f]['type'] == "boolean") {
$ret[$f] = $db->f('value') == 1 ? $GLOBALS['l_yes']:$GLOBALS['l_no'];
} else {
$ret[$f] = $db->f('value');
}
}
return $ret;
}
/**
* get the details for entity of type user
* @param int $user_id
* @access protected
*/
protected function userDetails($user_id)
{
$obm_q = new DB_OBM();
$db_type = $obm_q->type;
$multidomain = sql_multidomain('userobm');
$id = sql_parse_id($user_id, true);
// Comment for doing a backup user even if mailbox is disable
// userobm_mail_perms as mail_enabled,
$query = "SELECT\n userobm_id as id,\n userobm_login as login,\n 'user' as entity,\n domain_name as realm,\n ms.host_ip as host\n FROM UserObm\n LEFT JOIN Domain on userobm_domain_id=domain_id\n LEFT JOIN Host ms on userobm_mail_server_id=ms.host_id\n WHERE userobm_id {$id}\n {$multidomain}";
display_debug_msg($query, $GLOBALS['cdg_sql'], 'Backup::userDetails()');
$obm_q->query($query);
if (!$obm_q->next_record()) {
throw new Exception($GLOBALS['l_err_reference']);
}
$host = $obm_q->f('host');
if (empty($host)) {
throw new Exception($GLOBALS['l_err_host']);
}
// Comment for doing a backup user even if mailbox is disable
// $mail_enabled = $obm_q->f('mail_enabled');
// if (empty($mail_enabled)) {
// throw new Exception($GLOBALS['l_err_backup_no_mail']);
// }
$this->details = $obm_q->Record;
}
function get_domainproperty_list() {
global $cdg_sql;
$d = array();
$query = "SELECT * FROM DomainProperty";
$obm_q = new DB_OBM;
$obm_q->query($query);
while ($obm_q->next_record()) {
$key = $obm_q->f("domainproperty_key");
$type = $obm_q->f("domainproperty_type");
$default = $obm_q->f("domainproperty_default");
$d[$key] = array ('type' => $type, 'default' => $default);
}
return $d;
}
/**
* Delete a contact
*
* @param Integer $c_id contact_id
* @return Boolean
*/
function DeleteContact($c_id) {
global $cdg_sql, $c_use_connectors;
$sql_id = sql_parse_id($c_id, true);
$this->logger->debug("Delete contact $c_id");
$obm_q = new DB_OBM;
$query = "SELECT contact_company_id, contact_birthday_id, contact_usercreate, contact_addressbook_id FROM Contact
WHERE contact_id $sql_id";
$this->logger->core($query);
$obm_q->query($query);
$obm_q->next_record();
$comp_id = $obm_q->f('contact_company_id');
$birthday_id = $obm_q->f('contact_birthday_id');
$uid = $obm_q->f('contact_usercreate');
$ad = $obm_q->f('contact_addressbook_id');
// Hook : Pre
if (function_exists('hook_pre_run_query_contact_delete')) {
if (! hook_pre_run_query_contact_delete($c_id)) {
return false;
}
}
run_query_global_delete_document_links($c_id, 'contact');
$ret = of_userdata_query_delete('contact', $c_id);
// BEGIN birthday support
$query = "DELETE FROM Event WHERE event_id = '$birthday_id'";
$this->logger->core($query);
$obm_q->query($query);
// END birthday support
of_entity_delete('contact', $c_id);
$query = "DELETE FROM Contact WHERE contact_id $sql_id";
$this->logger->core($query);
$obm_q->query($query);
// If connectors in use
if ($c_use_connectors) {
$query = "INSERT INTO
DeletedContact (deletedcontact_contact_id, deletedcontact_addressbook_id, deletedcontact_timestamp, deletedcontact_origin)
VALUES ($c_id, $ad, NOW(), '$GLOBALS[c_origin_cron]')";
$this->logger->core($query);
$obm_q->query($query);
}
// After contact deletion to get correct number
run_query_global_company_contact_number_update($comp_id);
// Delete index
OBM_IndexingService::delete('contact', $c_id);
}
/**
* Get recipients email addresses
*
* $recipients : Id Recipients array
* $setting : recipient setting to check (set_mail, set_mail_participant)
* $force : Mail should be forced (all user even with no set_mail)
* Returns: DB with email recipients, or false if no valid recipients
*/
protected function getRecipients($recipients, $setting='set_mail', $force=false) {
global $cdg_sql;
if (($setting == 'set_mail') || ($setting == 'set_mail_participation')) {
$set_mail = $setting;
} else {
$set_mail = 'set_mail';
}
if (!$force) {
$mail_filter = "
AND (up1.userobmpref_value = 'yes' OR
(up2.userobmpref_value = 'yes' AND up1.userobmpref_value IS NULL))";
$join = "LEFT JOIN UserObmPref as up1 ON up1.userobmpref_user_id=userobm_id
AND up1.userobmpref_option = '$set_mail'
LEFT JOIN UserObmPref as up2 on up2.userobmpref_user_id IS NULL
AND up2.userobmpref_option = '$set_mail'";
}
$coma = '';
foreach($recipients as $recipient) {
if ($recipient) {
$user_list .= $coma.$recipient;
$coma = ',';
}
}
if ($user_list != '') {
$user_in = "userobm_id IN ($user_list) AND";
} else {
return false;
}
$query = "SELECT
userobm_email, userobm_lastname, userobm_firstname, userobm_commonname, domain_name
FROM UserObm
INNER JOIN Domain on userobm_domain_id = domain_id
$join
WHERE $user_in
userobm_email != ''
$mail_filter";
display_debug_msg($query, $cdg_sql, 'run_query_get_recipients()');
$db = new DB_OBM;
$db->query($query);
$recipients = array();
while ($db->next_record()) {
$email = $this->getEntityEmail($db->f('userobm_email'), $db->f('domain_name'));
$displayname = $db->f('userobm_commonname');
if (!$displayname) $displayname = sprintf($GLOBALS['l_displayname_template'], $db->f('userobm_firstname'), $db->f('userobm_lastname'));
if (isset($email) && $email != "") {
$recipients[] = array($email, $displayname);
}
}
return $recipients;
}
function setCampaignsStatus ($campaign_ids, $status) {
$res = array();
$obm_q = new DB_OBM;
if ($status != 'error_mail_format') {
$query = "UPDATE Campaign
SET campaign_status = " . $this->campaign_status_enum[$status] . "
WHERE campaign_id IN (" . join(',', $campaign_ids) . ");";
$obm_q->query($query);
} else {
$query = "SELECT campaign_email FROM Campaign WHERE campaign_id IN (" . join(',', $campaign_ids) . ");";
$obm_q->query($query);
while ($obm_q->next_record()) {
$document_id = $obm_q->f('campaign_email');
run_query_global_delete_document($document_id);
}
$query = "UPDATE Campaign
SET campaign_status = " . $this->campaign_status_enum[$status] . ",
campaign_email = NULL
WHERE campaign_id IN (" . join(',', $campaign_ids) . ");";
$obm_q->query($query);
}
return $res;
}
function get_list_list($list_ids='') {
$l = array();
$where = '';
if (trim($list_ids) != '') {
$where = "WHERE list_id IN ($list_ids)";
}
$query = "SELECT *
FROM List
$where
ORDER BY list_id";
$l_q = new DB_OBM;
$l_q->query($query);
while ($l_q->next_record()) {
$id = $l_q->f('list_id');
$l[$id]['id'] = $id;
$l[$id]['list_id'] = $id;
$l[$id]['privacy'] = $l_q->f('list_privacy');
$l[$id]['name'] = $l_q->f('list_name');
$l[$id]['subject'] = $l_q->f('list_subject');
$l[$id]['email'] = $l_q->f('list_email');
$l[$id]['mode'] = $l_q->f('list_mode');
$l[$id]['mailing_ok'] = $l_q->f('list_mailing_ok');
$l[$id]['contact_archive'] = $l_q->f('list_contact_archive');
$l[$id]['info_pub'] = $l_q->f('list_info_publication');
$l[$id]['static_nb'] = $l_q->f('list_static_nb');
$l[$id]['query_nb'] = $l_q->f('list_query_nb');
$l[$id]['query'] = $l_q->f('list_query');
$l[$id]['structure'] = $l_q->f('list_structure');
}
return $l;
}
/**
* getAlertDelta
*
* @param mixed $id
* @access public
* @return void
*/
function getAlertDelta($id, $user_id) {
$query = "SELECT eventalert_duration FROM EventAlert WHERE eventalert_event_id = '$id' AND eventalert_user_id = $user_id";
$obm_q = new DB_OBM;
$this->logger->core($query);
$obm_q->query($query);
$obm_q->next_record();
$this->logger->debug("Reminder delta for event ".$id." is ".$obm_q->f('eventalert_duration')." seconds");
return $obm_q->f('eventalert_duration');
}
/**
* Load user pattern attributes from the database
* @access protected
**/
protected function loadAttributes()
{
global $cdg_sql;
$obm_q = new DB_OBM();
$id = sql_parse_id($this->id, true);
$query = "SELECT attribute, value\n FROM userpattern_property\n WHERE userpattern_id {$id}";
display_debug_msg($query, $cdg_sql, "UserPattern::loadAttributes()");
$obm_q->query($query);
$this->attributes = array();
while ($obm_q->next_record()) {
$this->attributes[$obm_q->f('attribute')] = $obm_q->f('value');
}
}
/**
* build and return a Group instance from a SQL query response row
*
* @param DB_OBM $obm_q
* @access private
* @return User
*/
private static function build_user_entity($obm_q) {
$record = new User;
$record->id = $obm_q->f('userobm_id');
$record->domain_id = $obm_q->f('userobm_domain_id');
$record->domain_name = $obm_q->f('userobm_domain_name');
$record->timecreate = $obm_q->f('userobm_timecreate');
$record->timeupdate = $obm_q->f('userobm_timeupdate');
$record->usercreate_id = $obm_q->f('userobm_usercreate_id');
$record->usercreate_login = $obm_q->f('userobm_usercreate_login');
$record->userupdate_id = $obm_q->f('userobm_userupdate_id');
$record->userupdate_login = $obm_q->f('userobm_userupdate_login');
$record->local = $obm_q->f('userobm_local');
$record->ext_id = $obm_q->f('userobm_ext_id');
$record->system = $obm_q->f('userobm_system');
$record->archive = $obm_q->f('userobm_archive');
$record->status = $obm_q->f('userobm_status');
$record->timelastaccess = $obm_q->f('userobm_timelastaccess');
$record->login = $obm_q->f('userobm_login');
$record->nb_login_failed = $obm_q->f('userobm_nb_login_failed');
$record->password_type = $obm_q->f('userobm_password_type');
$record->password_dateexp = $obm_q->f('userobm_password_dateexp');
$record->account_dateexp = $obm_q->f('userobm_account_dateexp');
$record->perms = $obm_q->f('userobm_perms');
$record->delegation_target = $obm_q->f('userobm_delegation_target');
$record->delegation = $obm_q->f('userobm_delegation');
$record->calendar_version = $obm_q->f('userobm_calendar_version');
$record->uid = $obm_q->f('userobm_uid');
$record->gid = $obm_q->f('userobm_gid');
$record->datebegin = $obm_q->f('userobm_datebegin');
$record->hidden = $obm_q->f('userobm_hidden');
$record->kind = $obm_q->f('userobm_kind');
$record->lastname = $obm_q->f('userobm_lastname');
$record->firstname = $obm_q->f('userobm_firstname');
$record->title = $obm_q->f('userobm_title');
$record->sound = $obm_q->f('userobm_sound');
$record->company = $obm_q->f('userobm_company');
$record->direction = $obm_q->f('userobm_direction');
$record->service = $obm_q->f('userobm_service');
$record->address1 = $obm_q->f('userobm_address1');
$record->address2 = $obm_q->f('userobm_address2');
$record->address3 = $obm_q->f('userobm_address3');
$record->zipcode = $obm_q->f('userobm_zipcode');
$record->town = $obm_q->f('userobm_town');
$record->expresspostal = $obm_q->f('userobm_expresspostal');
$record->country_iso3166 = $obm_q->f('userobm_country_iso3166');
$record->phone = $obm_q->f('userobm_phone');
$record->phone2 = $obm_q->f('userobm_phone2');
$record->mobile = $obm_q->f('userobm_mobile');
$record->fax = $obm_q->f('userobm_fax');
$record->fax2 = $obm_q->f('userobm_fax2');
$record->web_perms = $obm_q->f('userobm_web_perms');
$record->web_list = $obm_q->f('userobm_web_list');
$record->web_all = $obm_q->f('userobm_web_all');
$record->mail_perms = $obm_q->f('userobm_mail_perms');
$record->mail_ext_perms = $obm_q->f('userobm_mail_ext_perms');
$record->email = $obm_q->f('userobm_email');
$record->mail_server_id = $obm_q->f('userobm_mail_server_id');
$record->mail_server_name = $obm_q->f('userobm_mail_server_name');
$record->mail_quota = $obm_q->f('userobm_mail_quota');
$record->mail_quota_use = $obm_q->f('userobm_mail_quota_use');
$record->mail_login_date = $obm_q->f('userobm_mail_login_date');
$record->nomade_perms = $obm_q->f('userobm_nomade_perms');
$record->nomade_enable = $obm_q->f('userobm_nomade_enable');
$record->nomade_local_copy = $obm_q->f('userobm_nomade_local_copy');
$record->email_nomade = $obm_q->f('userobm_email_nomade');
$record->vacation_enable = $obm_q->f('userobm_vacation_enable');
$record->vacation_datebegin = $obm_q->f('userobm_vacation_datebegin');
$record->vacation_dateend = $obm_q->f('userobm_vacation_dateend');
$record->vacation_message = $obm_q->f('userobm_vacation_message');
$record->samba_perms = $obm_q->f('userobm_samba_perms');
$record->samba_home = $obm_q->f('userobm_samba_home');
$record->samba_home_drive = $obm_q->f('userobm_samba_home_drive');
$record->samba_logon_script = $obm_q->f('userobm_samba_logon_script');
$record->host_id = $obm_q->f('userobm_host_id');
$record->description = $obm_q->f('userobm_description');
$record->location = $obm_q->f('userobm_location');
$record->education = $obm_q->f('userobm_education');
$record->has_photo = $obm_q->f('userobm_has_photo');
$record->groupware_usage = $obm_q->f('groupware_usage');
return $record;
}
protected function getDomains()
{
$domains = array();
$obm_q = new DB_OBM();
$query = "SELECT domain_id FROM Domain WHERE domain_global=0";
$this->logger->core($query);
$obm_q->query($query);
while ($obm_q->next_record()) {
$domains[] = $obm_q->f('domain_id');
}
return $domains;
}
public static function getCountryIso3166($name) {
$obm_q = new DB_OBM;
$query = "SELECT country_iso3166 FROM Country WHERE country_name #LIKE '".$name."' LIMIT 1";
$obm_q->xquery($query);
$obm_q->next_record();
return $obm_q->f('country_iso3166');
}