/**
* Function to get relation query for particular module with function name
* @param <record> $recordId
* @param <String> $functionName
* @param Vtiger_Module_Model $relatedModule
* @return <String>
*/
public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false)
{
if ($functionName === 'get_activities') {
$focus = CRMEntity::getInstance($this->getName());
$focus->id = $recordId;
$entityIds = $focus->getRelatedContactsIds();
$entityIds[] = $recordId;
$entityIds = implode(',', $entityIds);
$userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
$query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, \n\t\t\t\t\t\tvtiger_activity.status AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.link IN (" . $entityIds . ')';
$time = vtlib_purify($_REQUEST['time']);
if ($time == 'current') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('current');
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))";
}
if ($time == 'history') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('history');
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))";
}
$relatedModuleName = $relatedModule->getName();
$query .= $this->getSpecificRelationQuery($relatedModuleName);
$instance = CRMEntity::getInstance($relatedModuleName);
$securityParameter = $instance->getUserAccessConditionsQuerySR($relatedModuleName, false, $recordId);
if ($securityParameter != '') {
$query .= $securityParameter;
}
// There could be more than one contact for an activity.
$query .= ' GROUP BY vtiger_activity.activityid';
} elseif ($functionName === 'get_mails' && $relatedModule->getName() == 'OSSMailView') {
$query = OSSMailView_Record_Model::getMailsQuery($recordId, $relatedModule->getName());
} else {
$query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel);
}
return $query;
}
/**
* Function to get relation query for particular module with function name
* @param <record> $recordId
* @param <String> $functionName
* @param Vtiger_Module_Model $relatedModule
* @return <String>
*/
public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false)
{
if ($functionName === 'get_activities') {
$userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
$query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility,\n\t\t\t\t\t\tCASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.process = " . $recordId;
$time = vtlib_purify($_REQUEST['time']);
if ($time == 'current') {
$query .= " AND ((vtiger_activity.activitytype='Task' and vtiger_activity.status not in ('Completed','Deferred'))\n\t\t\t\tOR (vtiger_activity.activitytype not in ('Emails','Task') and vtiger_activity.eventstatus not in ('','Held')))";
}
if ($time == 'history') {
$query .= " AND ((vtiger_activity.activitytype='Task' and vtiger_activity.status in ('Completed','Deferred'))\n\t\t\t\tOR (vtiger_activity.activitytype not in ('Emails','Task') and vtiger_activity.eventstatus in ('','Held')))";
}
$relatedModuleName = $relatedModule->getName();
$query .= $this->getSpecificRelationQuery($relatedModuleName);
$instance = CRMEntity::getInstance($relatedModuleName);
$securityParameter = $instance->getUserAccessConditionsQuerySR($relatedModuleName);
if ($securityParameter != '') {
$sql .= $securityParameter;
}
} elseif ($functionName === 'get_mails' && $relatedModule->getName() == 'OSSMailView') {
$query = OSSMailView_Record_Model::getMailsQuery($recordId, $relatedModule->getName());
} else {
$query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel);
}
return $query;
}
/**
* Function to get relation query for particular module with function name
* @param <record> $recordId
* @param <String> $functionName
* @param Vtiger_Module_Model $relatedModule
* @return <String>
*/
public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false)
{
if ($functionName === 'get_mails' && $relatedModule->getName() == 'OSSMailView') {
$query = OSSMailView_Record_Model::getMailsQuery($recordId, $relatedModule->getName());
} else {
$query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel);
}
return $query;
}
/**
* Function to get relation query for particular module with function name
* @param <record> $recordId
* @param <String> $functionName
* @param Vtiger_Module_Model $relatedModule
* @return <String>
*/
public function getRelationQuery($recordId, $functionName, $relatedModule)
{
if ($functionName === 'get_activities') {
$userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
$query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, vtiger_seactivityrel.crmid AS parent_id,\n\t\t\t\t\t\tCASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.activitytype = 'Task'\n\t\t\t\t\t\t\t\tAND vtiger_seactivityrel.crmid = " . $recordId;
$relatedModuleName = $relatedModule->getName();
$query .= $this->getSpecificRelationQuery($relatedModuleName);
$nonAdminQuery = $this->getNonAdminAccessControlQueryForRelation($relatedModuleName);
if ($nonAdminQuery) {
$query = appendFromClauseToQuery($query, $nonAdminQuery);
}
} else {
$query = parent::getRelationQuery($recordId, $functionName, $relatedModule);
}
return $query;
}
/**
* Function to get relation query for particular module with function name
* @param <record> $recordId
* @param <String> $functionName
* @param Vtiger_Module_Model $relatedModule
* @return <String>
*/
public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false)
{
if ($functionName === 'get_activities') {
$userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
$query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN $userNameSql ELSE vtiger_groups.groupname END AS user_name,
vtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,
vtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility,
CASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status
FROM vtiger_activity
INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid
LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid
WHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.process = " . $recordId;
$time = vtlib_purify($_REQUEST['time']);
if ($time == 'current') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('current');
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))";
}
if ($time == 'history') {
$stateActivityLabels = Calendar_Module_Model::getComponentActivityStateLabel('history');
$query .= " AND (vtiger_activity.activitytype NOT IN ('Emails') AND vtiger_activity.status IN ('" . implode("','", $stateActivityLabels) . "'))";
}
$relatedModuleName = $relatedModule->getName();
$query .= $this->getSpecificRelationQuery($relatedModuleName);
$instance = CRMEntity::getInstance($relatedModuleName);
$securityParameter = $instance->getUserAccessConditionsQuerySR($relatedModuleName, false, $recordId);
if ($securityParameter != '')
$query .= $securityParameter;
} elseif ($functionName === 'get_mails' && $relatedModule->getName() == 'OSSMailView') {
$query = OSSMailView_Record_Model::getMailsQuery($recordId, $relatedModule->getName());
} else {
$query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel);
}
return $query;
}
/**
* Function to get relation query for particular module with function name
* @param <record> $recordId
* @param <String> $functionName
* @param Vtiger_Module_Model $relatedModule
* @return <String>
*/
public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false)
{
if ($functionName === 'get_activities') {
$focus = CRMEntity::getInstance($this->getName());
$focus->id = $recordId;
$entityIds = $focus->getRelatedContactsIds();
$entityIds[] = $recordId;
$entityIds = implode(',', $entityIds);
$userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
$query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, \n\t\t\t\t\t\tCASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.link IN (" . $entityIds . ')';
$time = vtlib_purify($_REQUEST['time']);
if ($time == 'current') {
$query .= " AND ((vtiger_activity.activitytype='Task' and vtiger_activity.status not in ('Completed','Deferred'))\n\t\t\t\tOR (vtiger_activity.activitytype not in ('Emails','Task') and vtiger_activity.eventstatus not in ('','Held')))";
}
if ($time == 'history') {
$query .= " AND ((vtiger_activity.activitytype='Task' and vtiger_activity.status in ('Completed','Deferred'))\n\t\t\t\tOR (vtiger_activity.activitytype not in ('Emails','Task') and vtiger_activity.eventstatus in ('','Held')))";
}
$relatedModuleName = $relatedModule->getName();
$query .= $this->getSpecificRelationQuery($relatedModuleName);
$instance = CRMEntity::getInstance($relatedModuleName);
$securityParameter = $instance->getUserAccessConditionsQuerySR($relatedModuleName);
if ($securityParameter != '') {
$sql .= $securityParameter;
}
// There could be more than one contact for an activity.
$query .= ' GROUP BY vtiger_activity.activityid';
} elseif ($functionName === 'get_dependents_list' && $relatedModule->getName() == 'OutsourcedProducts') {
$query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel);
$query .= " OR potential IN (SELECT potentialid FROM vtiger_potential INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_potential.potentialid WHERE vtiger_crmentity.deleted = 0 AND related_to = '{$recordId}')";
} elseif ($functionName === 'get_mails' && $relatedModule->getName() == 'OSSMailView') {
$query = OSSMailView_Record_Model::getMailsQuery($recordId, $relatedModule->getName());
} else {
$query = parent::getRelationQuery($recordId, $functionName, $relatedModule, $relationModel);
}
return $query;
}
/**
* Function to get relation query for particular module with function name
* @param <record> $recordId
* @param <String> $functionName
* @param Vtiger_Module_Model $relatedModule
* @return <String>
*/
public function getRelationQuery($recordId, $functionName, $relatedModule)
{
if ($functionName === 'get_activities') {
$focus = CRMEntity::getInstance($this->getName());
$focus->id = $recordId;
$entityIds = $focus->getRelatedContactsIds();
$entityIds = implode(',', $entityIds);
$userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
$query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, vtiger_seactivityrel.crmid AS parent_id,\n\t\t\t\t\t\tCASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.activitytype <> 'Emails'\n\t\t\t\t\t\t\t\tAND (vtiger_seactivityrel.crmid = " . $recordId;
if ($entityIds) {
$query .= " OR vtiger_cntactivityrel.contactid IN (" . $entityIds . "))";
} else {
$query .= ")";
}
$relatedModuleName = $relatedModule->getName();
$query .= $this->getSpecificRelationQuery($relatedModuleName);
$nonAdminQuery = $this->getNonAdminAccessControlQueryForRelation($relatedModuleName);
if ($nonAdminQuery) {
$query = appendFromClauseToQuery($query, $nonAdminQuery);
}
// There could be more than one contact for an activity.
$query .= ' GROUP BY vtiger_activity.activityid';
} else {
$query = parent::getRelationQuery($recordId, $functionName, $relatedModule);
}
return $query;
}
