function getRelationQuery($module, $secmodule, $table_name, $column_name, $queryPlanner) { $tab = getRelationTables($module, $secmodule); foreach ($tab as $key => $value) { $tables[] = $key; $fields[] = $value; } $pritablename = $tables[0]; $sectablename = $tables[1]; $prifieldname = $fields[0][0]; $secfieldname = $fields[0][1]; $tmpname = $pritablename . 'tmp' . $secmodule; $condition = ""; if (!empty($tables[1]) && !empty($fields[1])) { $condvalue = $tables[1] . "." . $fields[1]; $condition = "{$pritablename}.{$prifieldname}={$condvalue}"; } else { $condvalue = $table_name . "." . $column_name; $condition = "{$pritablename}.{$secfieldname}={$condvalue}"; } // Look forward for temporary table usage as defined by the QueryPlanner $secQuery = "select {$table_name}.* from {$table_name} inner join vtiger_crmentity on " . "vtiger_crmentity.crmid={$table_name}.{$column_name} and vtiger_crmentity.deleted=0"; $secQueryTempTableQuery = $queryPlanner->registerTempTable($secQuery, array($column_name, $fields[1], $prifieldname)); $query = ''; if ($pritablename == 'vtiger_crmentityrel') { $condition = "({$table_name}.{$column_name}={$tmpname}.{$secfieldname} " . "OR {$table_name}.{$column_name}={$tmpname}.{$prifieldname})"; $query = " left join vtiger_crmentityrel as {$tmpname} ON ({$condvalue}={$tmpname}.{$secfieldname} " . "OR {$condvalue}={$tmpname}.{$prifieldname}) "; } elseif (strripos($pritablename, 'rel') === strlen($pritablename) - 3) { $instance = self::getInstance($module); $sectableindex = $instance->tab_name_index[$sectablename]; $condition = "{$table_name}.{$column_name}={$tmpname}.{$secfieldname}"; if ($pritablename == 'vtiger_seactivityrel') { if ($module == "Emails" || $secmodule == "Emails") { $tmpModule = "Emails"; } else { $tmpModule = "Calendar"; } $query = " left join {$pritablename} as {$tmpname} ON ({$sectablename}.{$sectableindex}={$tmpname}.{$prifieldname}\n AND {$tmpname}.activityid IN (SELECT crmid FROM vtiger_crmentity WHERE setype='{$tmpModule}' AND deleted = 0))"; } else { if ($pritablename == 'vtiger_senotesrel') { $query = " left join {$pritablename} as {$tmpname} ON ({$sectablename}.{$sectableindex}={$tmpname}.{$prifieldname}\n AND {$tmpname}.notesid IN (SELECT crmid FROM vtiger_crmentity WHERE setype='Documents' AND deleted = 0))"; } else { if ($pritablename == 'vtiger_inventoryproductrel' && ($module == "Products" || $module == "Services") && ($secmodule == "Invoice" || $secmodule == "SalesOrder" || $secmodule == "PurchaseOrder" || $secmodule == "Quotes")) { /** In vtiger_inventoryproductrel table, we'll have same product related to quotes/invoice/salesorder/purchaseorder * we need to check whether the product joining is related to secondary module selected or not to eliminate duplicates */ $query = " left join {$pritablename} as {$tmpname} ON ({$sectablename}.{$sectableindex}={$tmpname}.{$prifieldname} AND {$tmpname}.id in \n (select crmid from vtiger_crmentity where setype='{$secmodule}' and deleted=0))"; } else { $query = " left join {$pritablename} as {$tmpname} ON ({$sectablename}.{$sectableindex}={$tmpname}.{$prifieldname})"; } } } if ($secmodule == 'Calendar') { $condition .= " AND {$table_name}.activitytype != 'Emails'"; } else { if ($secmodule == 'Leads') { $condition .= " AND {$table_name}.converted = 0"; } } } else { if ($module == "Contacts" && $secmodule == "Potentials") { // To get all the Contacts from vtiger_contpotentialrel table $condition .= " OR {$table_name}.potentialid = vtiger_contpotentialrel.potentialid"; $query .= " left join vtiger_contpotentialrel on vtiger_contpotentialrel.contactid = vtiger_contactdetails.contactid"; } else { if ($module == "Potentials" && $secmodule == "Contacts") { // To get all the Potentials from vtiger_contpotentialrel table $condition .= " OR {$table_name}.contactid = vtiger_contpotentialrel.contactid"; $query .= " left join vtiger_contpotentialrel on vtiger_potential.potentialid = vtiger_contpotentialrel.potentialid"; } } } $query .= " left join {$secQueryTempTableQuery} as {$table_name} on {$condition}"; return $query; }
function getRelationQuery($module, $secmodule, $table_name, $column_name) { $tab = getRelationTables($module, $secmodule); foreach ($tab as $key => $value) { $tables[] = $key; $fields[] = $value; } $pritablename = $tables[0]; $sectablename = $tables[1]; $prifieldname = $fields[0][0]; $secfieldname = $fields[0][1]; $tmpname = $pritablename . 'tmp' . $secmodule; $condition = ""; if (!empty($tables[1]) && !empty($fields[1])) { $condvalue = $tables[1] . "." . $fields[1]; $condition = "{$pritablename}.{$prifieldname}={$condvalue}"; } else { $condvalue = $table_name . "." . $column_name; $condition = "{$pritablename}.{$secfieldname}={$condvalue}"; } $secQuery = "select {$table_name}.* from {$table_name} inner join vtiger_crmentity on " . "vtiger_crmentity.crmid={$table_name}.{$column_name} and vtiger_crmentity.deleted=0"; $query = ''; if ($pritablename == 'vtiger_crmentityrel') { $condition = "({$table_name}.{$column_name}={$tmpname}.{$secfieldname} " . "OR {$table_name}.{$column_name}={$tmpname}.{$prifieldname})"; $query = " left join vtiger_crmentityrel as {$tmpname} ON ({$condvalue}={$tmpname}.{$secfieldname} " . "OR {$condvalue}={$tmpname}.{$prifieldname}) "; } elseif (strripos($pritablename, 'rel') === strlen($pritablename) - 3) { $instance = self::getInstance($module); $sectableindex = $instance->tab_name_index[$sectablename]; $condition = "{$table_name}.{$column_name}={$tmpname}.{$secfieldname}"; $query = " left join {$pritablename} as {$tmpname} ON ({$sectablename}.{$sectableindex}={$tmpname}.{$prifieldname})"; if ($secmodule == 'Calendar') { $condition .= " AND {$table_name}.activitytype != 'Emails'"; } else { if ($secmodule == 'Leads') { $condition .= " AND {$table_name}.converted = 0"; } } } $query .= " left join ({$secQuery}) as {$table_name} on {$condition}"; return $query; }
function getRelationQuery($module, $secmodule, $table_name, $column_name) { $tab = getRelationTables($module, $secmodule); foreach ($tab as $key => $value) { $tables[] = $key; $fields[] = $value; } $tabname = $tables[0]; $prifieldname = $fields[0][0]; $secfieldname = $fields[0][1]; $tmpname = $tabname . "tmp" . $secmodule; $condition = ""; if (!empty($tables[1]) && !empty($fields[1])) { $condvalue = $tables[1] . "." . $fields[1]; } else { $condvalue = $tabname . "." . $prifieldname; } $condition = " {$tmpname}.{$prifieldname} = {$condvalue} "; $entity_check_query = " {$tmpname}.{$secfieldname} IN (SELECT crmid FROM vtiger_crmentity WHERE vtiger_crmentity.deleted=0 AND vtiger_crmentity.setype='{$secmodule}')"; $condition_secmod_table = " {$table_name}.{$column_name} = {$tmpname}.{$secfieldname} "; if ($tabname == 'vtiger_crmentityrel') { $condition = " ({$condition} OR {$tmpname}.{$secfieldname} = {$condvalue}) and"; $entity_check_query = "({$entity_check_query} OR {$tmpname}.{$prifieldname} IN (SELECT crmid FROM vtiger_crmentity WHERE vtiger_crmentity.deleted=0 AND vtiger_crmentity.setype='{$secmodule}')) "; $condition_secmod_table = "({$condition_secmod_table} OR {$table_name}.{$column_name} = {$tmpname}.{$prifieldname})"; } else { $condition .= " and "; } $query = " left join {$tabname} as {$tmpname} on {$condition} {$entity_check_query}"; $query .= " LEFT JOIN {$table_name} ON {$condition_secmod_table}"; return $query; }
function getRelationQuery($module, $secmodule, $table_name, $column_name) { $tab = getRelationTables($module, $secmodule); foreach ($tab as $key => $value) { $tables[] = $key; $fields[] = $value; } $pritablename = $tables[0]; $sectablename = $tables[1]; $prifieldname = $fields[0][0]; $secfieldname = $fields[0][1]; $tmpname = $pritablename . 'tmp' . $secmodule; $condition = ""; if (!empty($tables[1]) && !empty($fields[1])) { $condvalue = $tables[1] . "." . $fields[1]; $condition = "{$pritablename}.{$prifieldname}={$condvalue}"; } else { $condvalue = $table_name . "." . $column_name; $condition = "{$pritablename}.{$secfieldname}={$condvalue}"; } //$secQuery = "select $table_name.* from $table_name inner join vtiger_crmentity on " . // "vtiger_crmentity.crmid=$table_name.$column_name and vtiger_crmentity.deleted=0"; $secQuery2 = "inner join vtiger_crmentity as crmentity{$table_name} on " . "crmentity{$table_name}.crmid={$table_name}.{$column_name} and crmentity{$table_name}.deleted=0"; $query = ''; if ($pritablename == 'vtiger_crmentityrel') { $condition = "({$table_name}.{$column_name}={$tmpname}.{$secfieldname} " . "OR {$table_name}.{$column_name}={$tmpname}.{$prifieldname})"; $query = " left join vtiger_crmentityrel as {$tmpname} ON ({$condvalue}={$tmpname}.{$secfieldname} " . "OR {$condvalue}={$tmpname}.{$prifieldname}) "; } elseif (strripos($pritablename, 'rel') === strlen($pritablename) - 3) { $instance = self::getInstance($module); $sectableindex = $instance->tab_name_index[$sectablename]; $condition = "{$table_name}.{$column_name}={$tmpname}.{$secfieldname}"; $query = " left join {$pritablename} as {$tmpname} ON ({$sectablename}.{$sectableindex}={$tmpname}.{$prifieldname})"; if ($secmodule == 'Calendar') { $condition .= " AND {$table_name}.activitytype != 'Emails'"; } else { if ($secmodule == 'Leads') { $condition .= " AND {$table_name}.converted = 0"; } } } //$query .= " left join ($secQuery) as $table_name on {$condition}"; //performance improvement, this should be equivalent to the subquery but masses faster $query .= " left join {$table_name} on {$condition}"; $query .= " {$secQuery2}"; return $query; }
function getRelationQuery($module, $secmodule, $table_name, $column_name, $queryPlanner) { $tab = getRelationTables($module, $secmodule); foreach ($tab as $key => $value) { $tables[] = $key; $fields[] = $value; } $pritablename = $tables[0]; $sectablename = $tables[1]; $prifieldname = $fields[0][0]; $secfieldname = $fields[0][1]; $tmpname = $pritablename . 'tmp' . $secmodule; $condition = ""; if (!empty($tables[1]) && !empty($fields[1])) { $condvalue = $tables[1] . "." . $fields[1]; $condition = "{$pritablename}.{$prifieldname}={$condvalue}"; } else { $condvalue = $table_name . "." . $column_name; $condition = "{$pritablename}.{$secfieldname}={$condvalue}"; } // Look forward for temporary table usage as defined by the QueryPlanner $secQuery = "select {$table_name}.* from {$table_name} inner join vtiger_crmentity on " . "vtiger_crmentity.crmid={$table_name}.{$column_name} and vtiger_crmentity.deleted=0"; $secQueryTempTableQuery = $queryPlanner->registerTempTable($secQuery, array($column_name, $fields[1], $prifieldname)); $query = ''; if ($pritablename == 'vtiger_crmentityrel') { $condition = "({$table_name}.{$column_name}={$tmpname}.{$secfieldname} " . "OR {$table_name}.{$column_name}={$tmpname}.{$prifieldname})"; $query = " left join vtiger_crmentityrel as {$tmpname} ON ({$condvalue}={$tmpname}.{$secfieldname} " . "OR {$condvalue}={$tmpname}.{$prifieldname}) "; } elseif (strripos($pritablename, 'rel') === strlen($pritablename) - 3) { $instance = self::getInstance($module); $sectableindex = $instance->tab_name_index[$sectablename]; $condition = "{$table_name}.{$column_name}={$tmpname}.{$secfieldname}"; if ($pritablename == 'vtiger_senotesrel') { $query = " left join {$pritablename} as {$tmpname} ON ({$sectablename}.{$sectableindex}={$tmpname}.{$prifieldname}\n AND {$tmpname}.notesid IN (SELECT crmid FROM vtiger_crmentity WHERE setype='Documents' AND deleted = 0))"; } else { $query = " left join {$pritablename} as {$tmpname} ON ({$sectablename}.{$sectableindex}={$tmpname}.{$prifieldname})"; } if ($secmodule == 'Calendar') { $condition .= " AND {$table_name}.activitytype != 'Emails'"; } else { if ($secmodule == 'Leads') { $condition .= " AND {$table_name}.converted = 0"; } } } $query .= " left join {$secQueryTempTableQuery} as {$table_name} on {$condition}"; return $query; }
function getRelationQuery($module, $secmodule, $table_name, $column_name) { $tab = getRelationTables($module, $secmodule); foreach ($tab as $key => $value) { $tables[] = $key; $fields[] = $value; } $tabname = $tables[0]; $prifieldname = $fields[0][0]; $secfieldname = $fields[0][1]; $tmpname = $tabname . "tmp" . $secmodule; $condition = ""; if (!empty($tables[1]) && !empty($fields[1])) { $condvalue = $tables[1] . "." . $fields[1]; } else { $condvalue = $tabname . "." . $prifieldname; } $condition = " {$tmpname}.{$prifieldname} = {$condvalue} "; $condition_secmod_table = " {$table_name}.{$column_name} = {$tmpname}.{$secfieldname} "; if ($tabname == 'vtiger_crmentityrel') { $condition = " ({$condition} OR {$tmpname}.{$secfieldname} = {$condvalue}) "; $condition_secmod_table = "({$condition_secmod_table})"; } $query = " left join {$tabname} as {$tmpname} on {$condition}"; $query .= " LEFT JOIN {$table_name} ON {$condition_secmod_table}"; return $query; }