/**
* 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;
}
exit;
}
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);
/**
* 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 mailshare
* @param int $mailshare_id
* @access protected
*/
protected function mailshareDetails($mailshare_id)
{
$obm_q = new DB_OBM();
$db_type = $obm_q->type;
$multidomain = sql_multidomain('mailshare');
$id = sql_parse_id($mailshare_id, true);
$query = "SELECT\n mailshare_id as id,\n mailshare_name as login,\n 'mailshare' as entity,\n domain_name as realm,\n ms.host_ip as host\n FROM MailShare\n LEFT JOIN Domain on mailshare_domain_id=domain_id\n LEFT JOIN Host ms on mailshare_mail_server_id=ms.host_id\n WHERE mailshare_id {$id} \n {$multidomain}";
display_debug_msg($query, $GLOBALS['cdg_sql'], 'Backup::mailshareDetails()');
$obm_q->query($query);
if (!$obm_q->next_record()) {
throw new Exception($GLOBALS['l_err_reference']);
}
$this->details = $obm_q->Record;
$this->details['login'] = strtolower($this->details['login']);
}
/**
* 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;
}
static function resourceExist($id) {
$db = new DB_OBM;
$query = 'SELECT resource_id From Resource WHERE resource_id = '.$id;
$db->query($query);
return $db->next_record();
}
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');
}
}
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;
}
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');
}
