Newer
Older
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
$button .= '<input type="hidden" name="' . $dependentColumn . '_type" id="' . $dependentColumn . '_type" value="' . $currentModule . '">';
$query = "SELECT vtiger_crmentity.*, $other->table_name.*";
$userNameSql = getSqlForNameInDisplayFormat(array('first_name'=>'vtiger_users.first_name',
'last_name' => 'vtiger_users.last_name'), 'Users');
$query .= ", CASE WHEN (vtiger_users.user_name NOT LIKE '') THEN $userNameSql ELSE vtiger_groups.groupname END AS user_name";
$more_relation = '';
if (!empty($other->related_tables)) {
foreach ($other->related_tables as $tname => $relmap) {
$query .= ", $tname.*";
// Setup the default JOIN conditions if not specified
if (empty($relmap[1]))
$relmap[1] = $other->table_name;
if (empty($relmap[2]))
$relmap[2] = $relmap[0];
$more_relation .= " LEFT JOIN $tname ON $tname.$relmap[0] = $relmap[1].$relmap[2]";
}
}
$query .= " FROM $other->table_name";
$query .= " INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = $other->table_name.$other->table_index";
$query .= $more_relation;
$query .= " INNER JOIN $this->table_name AS $this->table_name$this->moduleName ON $this->table_name$this->moduleName.$this->table_index = $dependentTableName.$dependentColumn";
$query .= " LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid";
$query .= " LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid";
$query .= " WHERE vtiger_crmentity.deleted = 0 AND $this->table_name$this->moduleName.$this->table_index = $id";
if($related_module == 'Leads') {
$query .= " AND vtiger_leaddetails.converted=0 ";
}
$return_value = GetRelatedList($currentModule, $related_module, $other, $query, $button, $returnset);
}
if ($return_value == null)
$return_value = Array();
$return_value['CUSTOM_BUTTON'] = $button;
return $return_value;
}
/**
* Move the related records of the specified list of id's to the given record.
* @param String This module name
* @param Array List of Entity Id's from which related records need to be transfered
* @param Integer Id of the the Record to which the related records are to be moved
*/
function transferRelatedRecords($module, $transferEntityIds, $entityId) {
global $adb, $log;
$log->debug("Entering function transferRelatedRecords ($module, ".implode(',',$transferEntityIds).", $entityId)");
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
foreach ($transferEntityIds as $transferId) {
// Pick the records related to the entity to be transfered, but do not pick the once which are already related to the current entity.
$relatedRecords = $adb->pquery("SELECT relcrmid, relmodule FROM vtiger_crmentityrel WHERE crmid=? AND module=?" .
" AND relcrmid NOT IN (SELECT relcrmid FROM vtiger_crmentityrel WHERE crmid=? AND module=?)", array($transferId, $module, $entityId, $module));
$numOfRecords = $adb->num_rows($relatedRecords);
for ($i = 0; $i < $numOfRecords; $i++) {
$relcrmid = $adb->query_result($relatedRecords, $i, 'relcrmid');
$relmodule = $adb->query_result($relatedRecords, $i, 'relmodule');
$adb->pquery("UPDATE vtiger_crmentityrel SET crmid=? WHERE relcrmid=? AND relmodule=? AND crmid=? AND module=?", array($entityId, $relcrmid, $relmodule, $transferId, $module));
}
// Pick the records to which the entity to be transfered is related, but do not pick the once to which current entity is already related.
$parentRecords = $adb->pquery("SELECT crmid, module FROM vtiger_crmentityrel WHERE relcrmid=? AND relmodule=?" .
" AND crmid NOT IN (SELECT crmid FROM vtiger_crmentityrel WHERE relcrmid=? AND relmodule=?)", array($transferId, $module, $entityId, $module));
$numOfRecords = $adb->num_rows($parentRecords);
for ($i = 0; $i < $numOfRecords; $i++) {
$parcrmid = $adb->query_result($parentRecords, $i, 'crmid');
$parmodule = $adb->query_result($parentRecords, $i, 'module');
$adb->pquery("UPDATE vtiger_crmentityrel SET relcrmid=? WHERE crmid=? AND module=? AND relcrmid=? AND relmodule=?", array($entityId, $parcrmid, $parmodule, $transferId, $module));
}
}
Martin Allen
committed
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
//lookup anything in vtiger_fieldmodulerel table where relmodule = this module
$sql = "SELECT fieldid FROM vtiger_fieldmodulerel WHERE relmodule = ?";
$query = $adb->pquery($sql, [$module]);
$numRows = $adb->num_rows($query);
//this gives a list of related fields that relate to this module
for ($i = 0; $i < $numRows; $i++) {
$field = $adb->query_result($query, $i, 'fieldid');
//then for each field lookup the tablename and columnname
$sql2 = "SELECT * FROM vtiger_field WHERE fieldid = ?";
$query2 = $adb->pquery($sql2, [$field]);
$numRows2 = $adb->num_rows($query2);
for ($j = 0; $j < $numRows2; $j++) {
$table = $adb->query_result($query2, $j, 'tablename');
$column = $adb->query_result($query2, $j, 'columnname');
//loop through and update any entry in those table/columns that points to the old ID and update to point to the new ID
foreach ($transferEntityIds as $transferId) {
$sql3 = "UPDATE $table SET $column = ? WHERE $column = ?";
$query3 = $adb->pquery($sql3, [$entityId, $transferId]);
}
}
}
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
$log->debug("Exiting transferRelatedRecords...");
}
/*
* Function to get the primary query part of a report for which generateReportsQuery Doesnt exist in module
* @param - $module Primary module name
* returns the query string formed on fetching the related data for report for primary module
*/
function generateReportsQuery($module, $queryPlanner) {
global $adb;
$primary = CRMEntity::getInstance($module);
vtlib_setup_modulevars($module, $primary);
$moduletable = $primary->table_name;
$moduleindex = $primary->table_index;
$modulecftable = $primary->customFieldTable[0];
$modulecfindex = $primary->customFieldTable[1];
if (isset($modulecftable) && $queryPlanner->requireTable($modulecftable)) {
$cfquery = "inner join $modulecftable as $modulecftable on $modulecftable.$modulecfindex=$moduletable.$moduleindex";
} else {
$cfquery = '';
}
$relquery = '';
$matrix = $queryPlanner->newDependencyMatrix();
$fields_query = $adb->pquery("SELECT vtiger_field.fieldname,vtiger_field.tablename,vtiger_field.fieldid from vtiger_field INNER JOIN vtiger_tab on vtiger_tab.name = ? WHERE vtiger_tab.tabid=vtiger_field.tabid AND vtiger_field.uitype IN (10) and vtiger_field.presence in (0,2)", array($module));
if ($adb->num_rows($fields_query) > 0) {
for ($i = 0; $i < $adb->num_rows($fields_query); $i++) {
$field_name = $adb->query_result($fields_query, $i, 'fieldname');
$field_id = $adb->query_result($fields_query, $i, 'fieldid');
$tab_name = $adb->query_result($fields_query, $i, 'tablename');
$ui10_modules_query = $adb->pquery("SELECT relmodule FROM vtiger_fieldmodulerel WHERE fieldid=?", array($field_id));
if ($adb->num_rows($ui10_modules_query) > 0) {
// Capture the forward table dependencies due to dynamic related-field
$crmentityRelModuleFieldTable = "vtiger_crmentityRel$module$field_id";
$crmentityRelModuleFieldTableDeps = array();
for ($j = 0; $j < $adb->num_rows($ui10_modules_query); $j++) {
$rel_mod = $adb->query_result($ui10_modules_query, $j, 'relmodule');
$rel_obj = CRMEntity::getInstance($rel_mod);
vtlib_setup_modulevars($rel_mod, $rel_obj);
$rel_tab_name = $rel_obj->table_name;
$rel_tab_index = $rel_obj->table_index;
$crmentityRelModuleFieldTableDeps[] = $rel_tab_name . "Rel$module$field_id";
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
}
$matrix->setDependency($crmentityRelModuleFieldTable, $crmentityRelModuleFieldTableDeps);
$matrix->addDependency($tab_name, $crmentityRelModuleFieldTable);
if ($queryPlanner->requireTable($crmentityRelModuleFieldTable, $matrix)) {
$relquery.= " left join vtiger_crmentity as $crmentityRelModuleFieldTable on $crmentityRelModuleFieldTable.crmid = $tab_name.$field_name and vtiger_crmentityRel$module$field_id.deleted=0";
}
for ($j = 0; $j < $adb->num_rows($ui10_modules_query); $j++) {
$rel_mod = $adb->query_result($ui10_modules_query, $j, 'relmodule');
$rel_obj = CRMEntity::getInstance($rel_mod);
vtlib_setup_modulevars($rel_mod, $rel_obj);
$rel_tab_name = $rel_obj->table_name;
$rel_tab_index = $rel_obj->table_index;
$rel_tab_name_rel_module_table_alias = $rel_tab_name . "Rel$module$field_id";
if ($queryPlanner->requireTable($rel_tab_name_rel_module_table_alias)) {
$relquery.= " left join $rel_tab_name as $rel_tab_name_rel_module_table_alias on $rel_tab_name_rel_module_table_alias.$rel_tab_index = $crmentityRelModuleFieldTable.crmid";
}
}
}
}
}
$query = "from $moduletable inner join vtiger_crmentity on vtiger_crmentity.crmid=$moduletable.$moduleindex";
// Add the pre-joined custom table query
$query .= " "."$cfquery";
if ($queryPlanner->requireTable('vtiger_groups'.$module)) {
$query .= " left join vtiger_groups as vtiger_groups" . $module . " on vtiger_groups" . $module . ".groupid = vtiger_crmentity.smownerid";
}
if ($queryPlanner->requireTable('vtiger_users'.$module)) {
$query .= " left join vtiger_users as vtiger_users" . $module . " on vtiger_users" . $module . ".id = vtiger_crmentity.smownerid";
}
if ($queryPlanner->requireTable('vtiger_lastModifiedBy'.$module)) {
$query .= " left join vtiger_users as vtiger_lastModifiedBy" . $module . " on vtiger_lastModifiedBy" . $module . ".id = vtiger_crmentity.modifiedby";
}
if ($queryPlanner->requireTable('vtiger_createdby'.$module)) {
$query .= " LEFT JOIN vtiger_users AS vtiger_createdby$module ON vtiger_createdby$module.id=vtiger_crmentity.smcreatorid";
}
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
// TODO Optimize the tables below based on requirement
$query .= " left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid";
$query .= " left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid";
// Add the pre-joined relation table query
$query .= " " . $relquery;
return $query;
}
/*
* Function to get the secondary query part of a report for which generateReportsSecQuery Doesnt exist in module
* @param - $module primary module name
* @param - $secmodule secondary module name
* returns the query string formed on fetching the related data for report for secondary module
*/
function generateReportsSecQuery($module, $secmodule,$queryPlanner) {
global $adb;
$secondary = CRMEntity::getInstance($secmodule);
vtlib_setup_modulevars($secmodule, $secondary);
$tablename = $secondary->table_name;
$tableindex = $secondary->table_index;
$modulecftable = $secondary->customFieldTable[0];
$modulecfindex = $secondary->customFieldTable[1];
if (isset($modulecftable) && $queryPlanner->requireTable($modulecftable)) {
$cfquery = "left join $modulecftable as $modulecftable on $modulecftable.$modulecfindex=$tablename.$tableindex";
} else {
$cfquery = '';
}
$relquery = '';
$matrix = $queryPlanner->newDependencyMatrix();
$fields_query = $adb->pquery("SELECT vtiger_field.fieldname,vtiger_field.tablename,vtiger_field.fieldid from vtiger_field INNER JOIN vtiger_tab on vtiger_tab.name = ? WHERE vtiger_tab.tabid=vtiger_field.tabid AND vtiger_field.uitype IN (10) and vtiger_field.presence in (0,2)", array($secmodule));
if ($adb->num_rows($fields_query) > 0) {
for ($i = 0; $i < $adb->num_rows($fields_query); $i++) {
$field_name = $adb->query_result($fields_query, $i, 'fieldname');
$field_id = $adb->query_result($fields_query, $i, 'fieldid');
$tab_name = $adb->query_result($fields_query, $i, 'tablename');
$ui10_modules_query = $adb->pquery("SELECT relmodule FROM vtiger_fieldmodulerel WHERE fieldid=?", array($field_id));
if ($adb->num_rows($ui10_modules_query) > 0) {
// Capture the forward table dependencies due to dynamic related-field
$crmentityRelSecModuleTable = "vtiger_crmentityRel$secmodule$field_id";
$crmentityRelSecModuleTableDeps = array();
for ($j = 0; $j < $adb->num_rows($ui10_modules_query); $j++) {
$rel_mod = $adb->query_result($ui10_modules_query, $j, 'relmodule');
$rel_obj = CRMEntity::getInstance($rel_mod);
vtlib_setup_modulevars($rel_mod, $rel_obj);
$rel_tab_name = $rel_obj->table_name;
$rel_tab_index = $rel_obj->table_index;
$crmentityRelSecModuleTableDeps[] = $rel_tab_name . "Rel$secmodule";
}
$matrix->setDependency($crmentityRelSecModuleTable, $crmentityRelSecModuleTableDeps);
$matrix->addDependency($tab_name, $crmentityRelSecModuleTable);
if ($queryPlanner->requireTable($crmentityRelSecModuleTable, $matrix)) {
$relquery .= " left join vtiger_crmentity as $crmentityRelSecModuleTable on $crmentityRelSecModuleTable.crmid = $tab_name.$field_name and $crmentityRelSecModuleTable.deleted=0";
}
for ($j = 0; $j < $adb->num_rows($ui10_modules_query); $j++) {
$rel_mod = $adb->query_result($ui10_modules_query, $j, 'relmodule');
$rel_obj = CRMEntity::getInstance($rel_mod);
vtlib_setup_modulevars($rel_mod, $rel_obj);
$rel_tab_name = $rel_obj->table_name;
$rel_tab_index = $rel_obj->table_index;
$rel_tab_name_rel_secmodule_table_alias = $rel_tab_name . "Rel$secmodule$field_id";
if ($queryPlanner->requireTable($rel_tab_name_rel_secmodule_table_alias)) {
$relquery .= " left join $rel_tab_name as $rel_tab_name_rel_secmodule_table_alias on $rel_tab_name_rel_secmodule_table_alias.$rel_tab_index = $crmentityRelSecModuleTable.crmid";
}
}
}
}
}
// Update forward table dependencies
$matrix->setDependency("vtiger_crmentity$secmodule", array("vtiger_groups$secmodule", "vtiger_users$secmodule", "vtiger_lastModifiedBy$secmodule"));
$matrix->addDependency($tablename, "vtiger_crmentity$secmodule");
if (!$queryPlanner->requireTable($tablename, $matrix) && !$queryPlanner->requireTable($modulecftable)) {
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
return '';
}
$query = $this->getRelationQuery($module, $secmodule, "$tablename", "$tableindex", $queryPlanner);
if ($queryPlanner->requireTable("vtiger_crmentity$secmodule", $matrix)) {
$query .= " left join vtiger_crmentity as vtiger_crmentity$secmodule on vtiger_crmentity$secmodule.crmid = $tablename.$tableindex AND vtiger_crmentity$secmodule.deleted=0";
}
// Add the pre-joined custom table query
$query .= " ".$cfquery;
if ($queryPlanner->requireTable("vtiger_groups$secmodule")) {
$query .= " left join vtiger_groups as vtiger_groups" . $secmodule . " on vtiger_groups" . $secmodule . ".groupid = vtiger_crmentity$secmodule.smownerid";
}
if ($queryPlanner->requireTable("vtiger_users$secmodule")) {
$query .= " left join vtiger_users as vtiger_users" . $secmodule . " on vtiger_users" . $secmodule . ".id = vtiger_crmentity$secmodule.smownerid";
}
if ($queryPlanner->requireTable("vtiger_lastModifiedBy$secmodule")) {
$query .= " left join vtiger_users as vtiger_lastModifiedBy" . $secmodule . " on vtiger_lastModifiedBy" . $secmodule . ".id = vtiger_crmentity" . $secmodule . ".modifiedby";
}
if ($queryPlanner->requireTable('vtiger_createdby'.$secmodule)) {
$query .= " LEFT JOIN vtiger_users AS vtiger_createdby$secmodule ON vtiger_createdby$secmodule.id=vtiger_crmentity.smcreatorid";
}
// Add the pre-joined relation table query
$query .= " " . $relquery;
return $query;
}
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
function getReportsUiType10Query($module, $queryPlanner){
$adb = PearDatabase::getInstance();
$relquery = '';
$matrix = $queryPlanner->newDependencyMatrix();
$params = array($module);
if($module == "Calendar") {
array_push($params,"Events");
}
$fields_query = $adb->pquery("SELECT vtiger_field.fieldname,vtiger_field.tablename,vtiger_field.fieldid from vtiger_field INNER JOIN vtiger_tab on vtiger_tab.name IN (". generateQuestionMarks($params).") WHERE vtiger_tab.tabid=vtiger_field.tabid AND vtiger_field.uitype IN (10) AND vtiger_field.presence IN (0,2)", $params);
if ($adb->num_rows($fields_query) > 0) {
for ($i = 0; $i < $adb->num_rows($fields_query); $i++) {
$field_name = $adb->query_result($fields_query, $i, 'fieldname');
$field_id = $adb->query_result($fields_query, $i, 'fieldid');
$tab_name = $adb->query_result($fields_query, $i, 'tablename');
$ui10_modules_query = $adb->pquery("SELECT relmodule FROM vtiger_fieldmodulerel WHERE fieldid=?", array($field_id));
if ($adb->num_rows($ui10_modules_query) > 0) {
// Capture the forward table dependencies due to dynamic related-field
$crmentityRelModuleFieldTable = "vtiger_crmentityRel$module$field_id";
$crmentityRelModuleFieldTableDeps = array();
$calendarFlag = false;
for ($j = 0; $j < $adb->num_rows($ui10_modules_query); $j++) {
$rel_mod = $adb->query_result($ui10_modules_query, $j, 'relmodule');
if(vtlib_isModuleActive($rel_mod)) {
if($rel_mod == 'Calendar') {
$calendarFlag = true;
}
if($calendarFlag && $rel_mod == 'Events') {
continue;
}
$rel_obj = CRMEntity::getInstance($rel_mod);
vtlib_setup_modulevars($rel_mod, $rel_obj);
$rel_tab_name = $rel_obj->table_name;
$rel_tab_index = $rel_obj->table_index;
$crmentityRelModuleFieldTableDeps[] = $rel_tab_name . "Rel$module$field_id";
}
}
$matrix->setDependency($crmentityRelModuleFieldTable, $crmentityRelModuleFieldTableDeps);
$matrix->addDependency($tab_name, $crmentityRelModuleFieldTable);
if ($queryPlanner->requireTable($crmentityRelModuleFieldTable, $matrix)) {
$relquery.= " LEFT JOIN vtiger_crmentity AS $crmentityRelModuleFieldTable ON $crmentityRelModuleFieldTable.crmid = $tab_name.$field_name AND vtiger_crmentityRel$module$field_id.deleted=0";
}
$calendarFlag = false;
for ($j = 0; $j < $adb->num_rows($ui10_modules_query); $j++) {
$rel_mod = $adb->query_result($ui10_modules_query, $j, 'relmodule');
if(vtlib_isModuleActive($rel_mod)) {
if($rel_mod == 'Calendar') {
$calendarFlag = true;
}
if($calendarFlag && $rel_mod == 'Events') {
continue;
}
$rel_obj = CRMEntity::getInstance($rel_mod);
vtlib_setup_modulevars($rel_mod, $rel_obj);
$rel_tab_name = $rel_obj->table_name;
$rel_tab_index = $rel_obj->table_index;
$rel_tab_name_rel_module_table_alias = $rel_tab_name . "Rel$module$field_id";
if ($queryPlanner->requireTable($rel_tab_name_rel_module_table_alias)) {
$relquery.= " LEFT JOIN $rel_tab_name AS $rel_tab_name_rel_module_table_alias ON $rel_tab_name_rel_module_table_alias.$rel_tab_index = $crmentityRelModuleFieldTable.crmid";
}
}
}
}
}
}
return $relquery;
}
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
/*
* Function to get the security query part of a report
* @param - $module primary module name
* returns the query string formed on fetching the related data for report for security of the module
*/
function getListViewSecurityParameter($module) {
$tabid = getTabid($module);
global $current_user;
if ($current_user) {
require('user_privileges/user_privileges_' . $current_user->id . '.php');
require('user_privileges/sharing_privileges_' . $current_user->id . '.php');
}
$sec_query = '';
if($is_admin==false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1
&& $defaultOrgSharingPermission[$tabid] == 3) {
$sec_query .= " and (vtiger_crmentity.smownerid in($current_user->id) or vtiger_crmentity.smownerid
in (select vtiger_user2role.userid from vtiger_user2role
inner join vtiger_users on vtiger_users.id=vtiger_user2role.userid
inner join vtiger_role on vtiger_role.roleid=vtiger_user2role.roleid
where vtiger_role.parentrole like '" . $current_user_parent_role_seq . "::%') or vtiger_crmentity.smownerid
in(select shareduserid from vtiger_tmp_read_user_sharing_per
where userid=" . $current_user->id . " and tabid=" . $tabid . ") or (";
if (php7_sizeof($current_user_groups) > 0) {
$sec_query .= " vtiger_crmentity.smownerid in (" . implode(",", $current_user_groups) . ") or ";
$sec_query .= " vtiger_crmentity.smownerid in(select vtiger_tmp_read_group_sharing_per.sharedgroupid
from vtiger_tmp_read_group_sharing_per where userid=" . $current_user->id . " and tabid=" . $tabid . "))) ";
}
return $sec_query;
}
/*
* Function to get the relation query part of a report
* @param - $module primary module name
* @param - $secmodule secondary module name
* returns the query string formed on relating the primary module and secondary module
*/
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 = isset($tables[1]) ? $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];
} else {
$condvalue = $table_name . "." . $column_name;
$condition = "$pritablename.$secfieldname=$condvalue";
}
// Look forward for temporary table usage as defined by the QueryPlanner
$secQueryFrom = " FROM $table_name INNER JOIN vtiger_crmentity ON " .
"vtiger_crmentity.crmid=$table_name.$column_name AND vtiger_crmentity.deleted=0 ";
//The relation field exists in custom field . relation field added from layout editor
if($pritablename != $table_name) {
$modulecftable = $this->customFieldTable[0];
$modulecfindex = $this->customFieldTable[1];
if (isset($modulecftable)) {
$columns = $this->db->getColumnNames($modulecftable);
//remove the primary key since it will conflict with base table column name or else creating temporary table will fails for duplicate columns
//eg : vtiger_potential has potentialid and vtiger_potentialscf has same potentialid
unset($columns[array_search($modulecfindex,$columns)]);
if(php7_count($columns) > 0) {
$cfSelectString = implode(',',$columns);
$selectColumns .= ','.$cfSelectString;
}
$cfquery = "LEFT JOIN $modulecftable ON $modulecftable.$modulecfindex=$table_name.$column_name";
$secQueryFrom .= $cfquery;
}
}
$secQueryTempTableQuery = $queryPlanner->registerTempTable($secQuery, array($column_name, isset($fields[1]) ? $fields[1] : '', $prifieldname),$secmodule);
$query = '';
if ($pritablename == 'vtiger_crmentityrel') {
$tableName = $table_name;
if($secmodule == "Emails") {
$tableName .='Emails';
}
$condition = "($tableName.$column_name={$tmpname}.{$secfieldname} " .
"OR $tableName.$column_name={$tmpname}.{$prifieldname})";
$query = " left join vtiger_crmentityrel as $tmpname ON (($condvalue={$tmpname}.{$secfieldname} " .
"OR $condvalue={$tmpname}.{$prifieldname})) AND ({$tmpname}.module='{$secmodule}' OR {$tmpname}.relmodule='{$secmodule}') ";
} elseif (strripos($pritablename, 'rel') === (strlen($pritablename) - 3)) {
$instance = self::getInstance($module);
$sectableindex = $instance->tab_name_index[$sectablename];
$condition = "$table_name.$column_name=$tmpname.$secfieldname";
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
if($secmodule == "Emails"){
$condition = $table_name.'Emails'.".$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
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
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
(select crmid from vtiger_crmentity where setype='$secmodule' and deleted=0))";
} else if($pritablename == 'vtiger_cntactivityrel') {
if($queryPlanner->requireTable('vtiger_cntactivityrel') && $secmodule == 'Contacts') {
$tmpname = 'vtiger_cntactivityrel';
$condition = "$table_name.$column_name=$tmpname.$secfieldname";
} else {
$query = " left join $pritablename as $tmpname ON ($sectablename.$sectableindex=$tmpname.$prifieldname)";
}
} 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";
}
if ($secmodule == "Emails") {
$table_name .="Emails";
}
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
$query .= " left join $secQueryTempTableQuery as $table_name on {$condition}";
return $query;
}
/** END * */
/**
* This function handles the import for uitype 10 fieldtype
* @param string $module - the current module name
* @param string fieldname - the related to field name
*/
function add_related_to($module, $fieldname) {
global $adb, $imported_ids, $current_user;
$related_to = $this->column_fields[$fieldname];
if (empty($related_to)) {
return false;
}
//check if the field has module information; if not get the first module
if (!strpos($related_to, "::::")) {
$module = getFirstModule($module, $fieldname);
$value = $related_to;
} else {
//check the module of the field
$arr = array();
$arr = explode("::::", $related_to);
$module = $arr[0];
$value = $arr[1];
}
$focus1 = CRMEntity::getInstance($module);
$entityNameArr = getEntityField($module);
$entityName = $entityNameArr['fieldname'];
$query = "SELECT vtiger_crmentity.deleted, $focus1->table_name.*
FROM $focus1->table_name
INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=$focus1->table_name.$focus1->table_index
where $entityName=? and vtiger_crmentity.deleted=0";
$result = $adb->pquery($query, array($value));
if (!isset($this->checkFlagArr[$module])) {
$this->checkFlagArr[$module] = (isPermitted($module, 'EditView', '') == 'yes');
}
if ($adb->num_rows($result) > 0) {
//record found
$focus1->id = $adb->query_result($result, 0, $focus1->table_index);
} elseif ($this->checkFlagArr[$module]) {
//record not found; create it
$focus1->column_fields[$focus1->list_link_field] = $value;
$focus1->column_fields['assigned_user_id'] = $current_user->id;
$focus1->column_fields['modified_user_id'] = $current_user->id;
$focus1->save($module);
$last_import = new UsersLastImport();
$last_import->assigned_user_id = $current_user->id;
$last_import->bean_type = $module;
$last_import->bean_id = $focus1->id;
$last_import->save();
} else {
//record not found and cannot create
$this->column_fields[$fieldname] = "";
return false;
}
if (!empty($focus1->id)) {
$this->column_fields[$fieldname] = $focus1->id;
return true;
} else {
$this->column_fields[$fieldname] = "";
return false;
}
}
/**
* To keep track of action of field filtering and avoiding doing more than once.
*
* @var Array
*/
protected $__inactive_fields_filtered = false;
/**
* Filter in-active fields based on type
*
* @param String $module
*/
function filterInactiveFields($module) {
if ($this->__inactive_fields_filtered) {
return;
}
global $adb, $mod_strings;
// Look for fields that has presence value NOT IN (0,2)
$cachedModuleFields = VTCacheUtils::lookupFieldInfo_Module($module, array('1'));
if ($cachedModuleFields === false) {
// Initialize the fields calling suitable API
getColumnFields($module);
$cachedModuleFields = VTCacheUtils::lookupFieldInfo_Module($module, array('1'));
}
$hiddenFields = array();
if ($cachedModuleFields) {
foreach ($cachedModuleFields as $fieldinfo) {
$fieldLabel = $fieldinfo['fieldlabel'];
// NOTE: We should not translate the label to enable field diff based on it down
$fieldName = $fieldinfo['fieldname'];
$tableName = str_replace("vtiger_", "", $fieldinfo['tablename']);
$hiddenFields[$fieldLabel] = array($tableName => $fieldName);
}
}
if (isset($this->list_fields)) {
$this->list_fields = array_diff_assoc($this->list_fields, $hiddenFields);
}
if (isset($this->search_fields)) {
$this->search_fields = array_diff_assoc($this->search_fields, $hiddenFields);
}
// To avoid re-initializing everytime.
$this->__inactive_fields_filtered = true;
}
/** END * */
function buildSearchQueryForFieldTypes($uitypes, $value=false) {
global $adb;
if (!is_array($uitypes))
$uitypes = array($uitypes);
$cachedModuleFields = VTCacheUtils::lookupFieldInfo_Module($module);
if ($cachedModuleFields === false) {
getColumnFields($module); // This API will initialize the cache as well
// We will succeed now due to above function call
$cachedModuleFields = VTCacheUtils::lookupFieldInfo_Module($module);
}
if($module == 'Calendar' || $module == 'Events') {
$cachedEventsFields = VTCacheUtils::lookupFieldInfo_Module('Events');
$cachedCalendarFields = VTCacheUtils::lookupFieldInfo_Module('Calendar');
$cachedModuleFields = array_merge($cachedEventsFields, $cachedCalendarFields);
}
$lookuptables = array();
$lookupcolumns = array();
foreach ($cachedModuleFields as $fieldinfo) {
if (in_array($fieldinfo['uitype'], $uitypes)) {
$lookuptables[] = $fieldinfo['tablename'];
$lookupcolumns[] = $fieldinfo['columnname'];
}
}
$query = "select crmid as id ";
$entityfields = getEntityField($module);
$querycolumnnames = implode(',', $lookupcolumns);
$entitycolumnnames = $entityfields['fieldname'];
if(!empty($querycolumnnames)){
$query .= ", $querycolumnnames";
}
if(!empty($entitycolumnnames)){
$query .= ", $entitycolumnnames as name ";
}
$query .= " INNER JOIN vtiger_crmentity ON $this->table_name.$this->table_index = vtiger_crmentity.crmid AND deleted = 0 ";
//remove the base table
$LookupTable = array_unique($lookuptables);
$indexes = array_keys($LookupTable, $this->table_name);
if (!empty($indexes)) {
foreach ($indexes as $index) {
unset($LookupTable[$index]);
}
}
foreach ($LookupTable as $tablename) {
$query .= " INNER JOIN $tablename
on $this->table_name.$this->table_index = $tablename." . $this->tab_name_index[$tablename];
}
if (!empty($lookupcolumns) && $value !== false) {
$query .=" WHERE ";
$i = 0;
$columnCount = php7_count($lookupcolumns);
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
foreach ($lookupcolumns as $columnname) {
if (!empty($columnname)) {
if ($i == 0 || $i == ($columnCount))
$query .= sprintf("%s = '%s'", $columnname, $value);
else
$query .= sprintf(" OR %s = '%s'", $columnname, $value);
$i++;
}
}
}
return $query;
}
/**
*
* @param String $tableName
* @return String
*/
public function getJoinClause($tableName) {
if (strripos($tableName, 'rel') === (strlen($tableName) - 3)) {
return 'LEFT JOIN';
} else if (Vtiger_Functions::isUserSpecificFieldTable($tableName, $this->moduleName)) {
return 'LEFT JOIN';
}
else {
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
return 'INNER JOIN';
}
}
/**
*
* @param <type> $module
* @param <type> $user
* @param <type> $parentRole
* @param <type> $userGroups
*/
function getNonAdminAccessQuery($module, $user, $parentRole, $userGroups) {
$query = $this->getNonAdminUserAccessQuery($user, $parentRole, $userGroups);
if (!empty($module)) {
$moduleAccessQuery = $this->getNonAdminModuleAccessQuery($module, $user);
if (!empty($moduleAccessQuery)) {
$query .= " UNION $moduleAccessQuery";
}
}
return $query;
}
/**
*
* @param <type> $user
* @param <type> $parentRole
* @param <type> $userGroups
*/
function getNonAdminUserAccessQuery($user, $parentRole, $userGroups) {
$query = "(SELECT $user->id as id) UNION (SELECT vtiger_user2role.userid AS userid FROM " .
"vtiger_user2role INNER JOIN vtiger_users ON vtiger_users.id=vtiger_user2role.userid " .
"INNER JOIN vtiger_role ON vtiger_role.roleid=vtiger_user2role.roleid WHERE " .
"vtiger_role.parentrole like '$parentRole::%')";
if (php7_count($userGroups) > 0) {
$query .= $this->getNonAdminUserGroupAccessQuery($userGroups);
/**
*Function to get all the users under groups
* @param <type> $userGroups
*/
function getNonAdminUserGroupAccessQuery($userGroups) {
$query = " UNION (SELECT groupid FROM vtiger_groups WHERE groupid IN (".implode(",", $userGroups)."))";
/**
*
* @param <type> $module
* @param <type> $user
*/
function getNonAdminModuleAccessQuery($module, $user) {
require('user_privileges/sharing_privileges_' . $user->id . '.php');
$tabId = getTabid($module);
$sharingRuleInfoVariable = $module . '_share_read_permission';
$sharingRuleInfo = isset($$sharingRuleInfoVariable) ? $$sharingRuleInfoVariable : array();
if (!empty($sharingRuleInfo) && (php7_count($sharingRuleInfo['ROLE']) > 0 ||
php7_count($sharingRuleInfo['GROUP']) > 0)) {
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
$query = " (SELECT shareduserid FROM vtiger_tmp_read_user_sharing_per " .
"WHERE userid=$user->id AND tabid=$tabId) UNION (SELECT " .
"vtiger_tmp_read_group_sharing_per.sharedgroupid FROM " .
"vtiger_tmp_read_group_sharing_per WHERE userid=$user->id AND tabid=$tabId)";
}
return $query;
}
/**
*
* @param <type> $module
* @param <type> $user
* @param <type> $parentRole
* @param <type> $userGroups
*/
protected function setupTemporaryTable($tableName, $tabId, $user, $parentRole, $userGroups) {
$module = null;
if (!empty($tabId)) {
$module = getTabModuleName($tabId);
}
$query = $this->getNonAdminAccessQuery($module, $user, $parentRole, $userGroups);
$tableName = Vtiger_Util_Helper::validateStringForSql($tableName);
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
2857
2858
2859
2860
2861
2862
2863
$query = "create temporary table IF NOT EXISTS $tableName(id int(11) primary key) ignore " .
$query;
$db = PearDatabase::getInstance();
$result = $db->pquery($query, array());
if (is_object($result)) {
return true;
}
return false;
}
/**
*
* @param String $module - module name for which query needs to be generated.
* @param Users $user - user for which query needs to be generated.
* @return String Access control Query for the user.
*/
function getNonAdminAccessControlQuery($module, $user, $scope = '') {
require('user_privileges/user_privileges_' . $user->id . '.php');
require('user_privileges/sharing_privileges_' . $user->id . '.php');
$query = ' ';
$tabId = getTabid($module);
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2]
== 1 && $defaultOrgSharingPermission[$tabId] == 3) {
$tableName = 'vt_tmp_u' . $user->id;
$sharingRuleInfoVariable = $module . '_share_read_permission';
$sharingRuleInfo = $$sharingRuleInfoVariable;
$sharedTabId = null;
if (!empty($sharingRuleInfo) && (php7_count($sharingRuleInfo['ROLE']) > 0 ||
php7_count($sharingRuleInfo['GROUP']) > 0)) {
$tableName = $tableName . '_t' . $tabId;
$sharedTabId = $tabId;
} elseif ($module == 'Calendar' || !empty($scope)) {
$tableName .= '_t' . $tabId;
}
$this->setupTemporaryTable($tableName, $sharedTabId, $user, $current_user_parent_role_seq, $current_user_groups);
// for secondary module we should join the records even if record is not there(primary module without related record)
if($scope == ''){
$query = " INNER JOIN $tableName $tableName$scope ON $tableName$scope.id = " .
"vtiger_crmentity$scope.smownerid ";
}else{
$query = " INNER JOIN $tableName $tableName$scope ON $tableName$scope.id = " .
"vtiger_crmentity$scope.smownerid OR vtiger_crmentity$scope.smownerid IS NULL";
}
}
return $query;
}
public function listQueryNonAdminChange($query, $scope = '') {
//make the module base table as left hand side table for the joins,
//as mysql query optimizer puts crmentity on the left side and considerably slow down
$query = preg_replace('/\s+/', ' ', $query);
if (strripos($query, ' WHERE ') !== false) {
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
$query = str_ireplace(' where ', " WHERE $this->table_name.$this->table_index > 0 AND ", $query);
}
return $query;
}
/*
* Function to get the relation tables for related modules
* @param String $secmodule - $secmodule secondary module name
* @return Array returns the array with table names and fieldnames storing relations
* between module and this module
*/
function setRelationTables($secmodule) {
$rel_tables = array(
"Documents" => array("vtiger_senotesrel" => array("crmid", "notesid"),
$this->table_name => $this->table_index),
);
return $rel_tables[$secmodule];
}
/**
* Function to clear the fields which needs to be saved only once during the Save of the record
* For eg: Comments of HelpDesk should be saved only once during one save of a Trouble Ticket
*/
function clearSingletonSaveFields() {
return;
}
/**
* Function to track when a new record is linked to a given record
*/
function trackLinkedInfo($module, $crmid, $with_module, $with_crmid) {
global $current_user;
$adb = PearDatabase::getInstance();
$currentTime = date('Y-m-d H:i:s');
$adb->pquery('UPDATE vtiger_crmentity SET modifiedtime = ?, modifiedby = ? WHERE crmid = ?', array($currentTime, $current_user->id, $crmid));
// @Note: We should extend this to event handlers
if(vtlib_isModuleActive('ModTracker')) {
// Track the time the relation was added
require_once 'modules/ModTracker/ModTracker.php';
ModTracker::linkRelation($module, $crmid, $with_module, $with_crmid);
}
}
/**
* Function to get sort order
* return string $sorder - sortorder string either 'ASC' or 'DESC'
*/
function getSortOrder() {
global $log,$currentModule;
$log->debug("Entering getSortOrder() method ...");
if (isset($_REQUEST['sorder']))
$sorder = $this->db->sql_escape_string($_REQUEST['sorder']);
else
$sorder = (($_SESSION[$currentModule . '_Sort_Order'] != '') ? ($_SESSION[$currentModule . '_Sort_Order']) : ($this->default_sort_order));
$log->debug("Exiting getSortOrder() method ...");
return $sorder;
}
/**
* Function to get order by
* return string $order_by - fieldname(eg: 'accountname')
*/
function getOrderBy() {
global $log, $currentModule;
$log->debug("Entering getOrderBy() method ...");
$use_default_order_by = '';
if (PerformancePrefs::getBoolean('LISTVIEW_DEFAULT_SORTING', true)) {
$use_default_order_by = $this->default_order_by;
}
if (isset($_REQUEST['order_by']))
$order_by = $this->db->sql_escape_string($_REQUEST['order_by']);
else
$order_by = (($_SESSION[$currentModule.'_Order_By'] != '') ? ($_SESSION[$currentModule.'_Order_By']) : ($use_default_order_by));
$log->debug("Exiting getOrderBy method ...");
return $order_by;
}
// Mike Crowe Mod --------------------------------------------------------
/**
* Function to Listview buttons
* return array $list_buttons - for module (eg: 'Accounts')
*/
$list_buttons = Array();
if (isPermitted($currentModule, 'Delete', '') == 'yes')
$list_buttons['del'] = $app_strings["LBL_MASS_DELETE"];
if (isPermitted($currentModule, 'EditView', '') == 'yes') {
$list_buttons['mass_edit'] = $app_strings["LBL_MASS_EDIT"];
// Mass Edit could be used to change the owner as well!
//$list_buttons['c_owner'] = $app_strings["LBL_CHANGE_OWNER"];
}
return $list_buttons;
}
/**
* Function to track when a record is unlinked to a given record
*/
function trackUnLinkedInfo($module, $crmid, $with_module, $with_crmid) {
global $current_user;
$adb = PearDatabase::getInstance();
$currentTime = date('Y-m-d H:i:s');
$adb->pquery('UPDATE vtiger_crmentity SET modifiedtime = ?, modifiedby = ? WHERE crmid = ?', array($currentTime, $current_user->id, $crmid));