We have made customizations to add custom subpanels under Accounts Module. Earlier in 7.8.2, we have used joinRaw method to join the result set of custom query. But while upgrading the instance from 7.8.2 to 7.9.5, we got healthcheck error for joinRaw. So we replaced joinRaw with joinTable method.
But joinTable does not allow to join custom query as below:
public function buildJoinSugarQuery($sugar_query, $options = array()) {
$joinParams = array('joinType' => isset($options['joinType']) ? $options['joinType'] : 'INNER');
$jta = 'accounts_commercialnew_1';
if (!empty($options['joinTableAlias'])) {
$jta = $joinParams['alias'] = $options['joinTableAlias'];
}
$sugar_query->joinTable($this->getCustomJoin($options), $joinParams);
$sugar_query->where()->queryOr()
->between("shipmentdate",date("Y-m-d"),date("Y-m-d",strtotime("+30 day")))
->between("excinvdate_c",date("Y-m-d",strtotime("-365 day")),date("Y-m-d"))
->between("customerrequesteddate_c",date("Y-m-d"),date("Y-m-d",strtotime("+30 day")));
return $sugar_query->join[$jta];
}
protected function getCustomJoin($params = array())
{
$sql = " (";
// Give me every opportunity
$sql .= <<<MYCUSTOMQUERY
SELECT
commercialnew.id AS commer_id
FROM
commercialnew JOIN accounts_divis_commercialnew_1_c ON commercialnew.id = accounts_commercialnew_1_c.accounts_commercialnew_1commercialnew_idb
WHERE accounts_commercialnew_1_c.deleted=0 AND accounts_commercialnew_1_c.accounts_commercialnew_1accounts_ida = '{$this->focus->id}'
MYCUSTOMQUERY;
$sql .= ") commercialnew_to_show ON commercialnew_to_show.commer_id = commercialnew.id";
return $sql;
}
How do I arrange this so I can get the join right ? Any help will be greatly appreciated