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;
}
