ModComment Roll Up not retrieving Comments from all related Contacts in Opportunity

In an Opportunity there are two ways a Contact can be related to it.

1. A single Contact is related by the Contact Name reference field
2. Multiple Contacts can also be related under the Contacts "tab" of the Opportunity using a many-to-many related list where the relationship is stored in (I think) vtiger_contpotentialrel

Any Contacts related to an Opportunity in 2. are not being retrieved in the getCommentEnabledRelatedEntityIds() method of the parent's Record Model.

Following this through a bit more, it seems to me that the getRelatedModuleRecordIds method (in the Vtiger_Module_model) will never retrieve all the correct IDs for Contacts in an Opportunity. In the Potential class file, the related_module_table_index only contains the 1:n Contact relationship for the contactid field in the Opportunity; and, looking the code in getRelatedModuleRecordIds, I don't believe you could have two rows of data in the related_module_table_index for the same module? So it can never retrieve Contact IDs from the vtiger_contpotentialrel n:n relationship.

Of course for the main related list in Detail View it is using the get_contacts() method of the Potential class which generates the necessary SQL to retrieve related Contacts from both the 1:n and the n:n relationships.

I think the best place to fix this would actually be in the Record Model of the Potential, and create a specific version of the getCommentEnabledRelatedEntityIds method which then includes any related Contacts IDs from vtiger_contpotentialrel by way of a custom query/function?

I did a quick test to see if I could use vtws_retrieve_related to get all the Contacts from an Opportunity... Nope. It only returns the Contact linked by the contactid field. It does not any which are linked by vtiger_contpotentialrel.

@lord_alan Thanks! for the notice and analysis done on the issue. Yes! even i made an analysis on this. where relationfieldid between Contacts to Potentials retrieved from getRelatedModuleRecordIds() of Vtiger/Module.php is pointing to 0(zero). So the generated query failed to fetch all related recordids. We will support for an api which will fetch records from vtiger_contpotentialrel.

@uma.s Thanks Uma. I look forward to seeing your patch. I didn;t deal with the api side of things, but I amde a patch which seemed to work locally for the Opportunity roll by adding the following code to the Record Model of the Potential...

    /**
     * Extend parent method to include Contact IDs from vtiger_contpotentialrel
     *
    **/
	function getCommentEnabledRelatedEntityIds($modulename, $recordId) {
		$user = Users_Record_Model::getCurrentUserModel();
		$relatedModuleRecordIds = array();
		$restrictedFieldnames = array('modifiedby', 'created_user_id', 'assigned_user_id');
		$recordModel = Vtiger_Record_Model::getInstanceById($recordId, $modulename);
		$moduleInstance = Vtiger_Module_Model::getInstance($modulename);
		$referenceFieldsModels = $moduleInstance->getFieldsByType('reference');
		$userPrevilegesModel = Users_Privileges_Model::getInstanceById($user->id);
		$directrelatedModuleRecordIds = array();

		foreach ($referenceFieldsModels as $referenceFieldsModel) {
			$relmoduleFieldname = $referenceFieldsModel->get('name');
			$relModuleFieldValue = $recordModel->get($relmoduleFieldname);

			if (!empty($relModuleFieldValue) && !in_array($relmoduleFieldname, $restrictedFieldnames) && isRecordExists($relModuleFieldValue)) {
				$relModuleRecordModel = Vtiger_Record_Model::getInstanceById($relModuleFieldValue);
				$relmodule = $relModuleRecordModel->getModuleName();

				$relatedmoduleModel = Vtiger_Module_Model::getInstance($relmodule);
				$isCommentEnabled = $relatedmoduleModel->isCommentEnabled();

				if ($isCommentEnabled) {
					$tabid = getTabid($relmodule);
					$modulePermission = $userPrevilegesModel->hasModulePermission($tabid);
					$hasDetailViewPermission = Users_Privileges_Model::isPermitted($relmodule, 'DetailView', $relModuleFieldValue);

					if ($modulePermission && $hasDetailViewPermission)
						$directrelatedModuleRecordIds[] = $relModuleFieldValue;
				}
			}
		}

		$moduleModel = Vtiger_Module_Model::getInstance($modulename);
		$relatedModuleModels = Vtiger_Relation_Model::getAllRelations($moduleModel, false);
		$commentEnabledModules = array();

        $contactComments = false; // Test if we have Comment enabled Contacts module
		foreach ($relatedModuleModels as $relatedModuleModel) {
			$relatedModuleName = $relatedModuleModel->get('relatedModuleName');
			$relatedmoduleModel = Vtiger_Module_Model::getInstance($relatedModuleName);
			$isCommentEnabled = $relatedmoduleModel->isCommentEnabled();

			if ($isCommentEnabled) {
				$tabid = getTabid($relatedModuleName);
				$modulePermission = $userPrevilegesModel->hasModulePermission($tabid);

				if ($modulePermission) {
				    if($relatedModuleName == 'Contacts') {
				        $contactComments = true;
				    }
					$commentEnabledModules['related_modules'][] = $relatedModuleModel->get('relation_id');
		        }
			}
		}

		//To get all the record ids for all the modules that are shown in related tab
		$indirectrelatedModuleRecordIds = $moduleModel->getRelatedModuleRecordIds(new Vtiger_Request($commentEnabledModules), array($recordId), true);

        // Get any "other" Contact IDs related to this Opportunity
        $otherContacts = array();
        if($contactComments) {
            $otherContacts = $this->getOtherContacts();
        }
        
		return array_merge($relatedModuleRecordIds, $directrelatedModuleRecordIds, $indirectrelatedModuleRecordIds, $otherContacts);
	}

    /**
     * Retrieve permitted Contacts which are related using the 
     * Potential specific vtiger_contpotentialrel table
     * See: https://code.vtiger.com/vtiger/vtigercrm/issues/1165
     *
    **/
    private function getOtherContacts() {
        $current_user = vglobal('current_user');
        require_once('include/Webservices/Query.php');
        $otherContactIds = array();
        $db = PearDatabase::getInstance();
        $query = "SELECT vtiger_contactdetails.contactid
                  FROM vtiger_potential
                  LEFT JOIN vtiger_contpotentialrel 
                    ON vtiger_contpotentialrel.potentialid = vtiger_potential.potentialid
                  INNER JOIN vtiger_contactdetails 
                    ON vtiger_contactdetails.contactid = vtiger_contpotentialrel.contactid
                  INNER JOIN vtiger_crmentity
                    ON vtiger_crmentity.crmid = vtiger_contactdetails.contactid
                  WHERE vtiger_potential.potentialid = ?
                  AND vtiger_crmentity.deleted = 0";
        $result = $db->pquery($query, array($this->getId()));
        $num_rows = $db->num_rows($result);
        if($num_rows > 0) {
            for($j=0; $j<$num_rows; $j++) {
                // vtws_retrieve_related doesn't work so have to test that user has permission to view each contact
                $vtwsQuery = "SELECT id FROM Contacts WHERE id = x" . $db->query_result($result, $j, 'contactid') . ";";
                $response = vtws_query($vtwsQuery, $current_user);
                if($response) {
                    $otherContactIds[] = array_pop(explode('x', $response[0]['id']));
                }
            }
        }
        return $otherContactIds;
    }

@lord_alan Thanks! for sharing the patch. I will review this and make necessary changes to source soon.

@uma.s This would be a good bug to fix before the next release... It's a fairly annoying limitation that vtws_retrieve_related() is not working properly.

i.e. Aside from the Contact<->Potential relationship should a developer create another scenario where there is a similar "dual" relationship between 2 modules then vtws_retrieve_related() should work.

@lord_alan Sure! will review this.

mentioned in merge request !572 (merged)

Status changed to closed by commit 1aba94d7

Milestone changed to 7.3

Added enhancement label

mentioned in issue #1749

mentioned in issue #1749