function get_current_user_access_groups($module)
{
$log = vglobal('log');
$log->debug("Entering get_current_user_access_groups(" . $module . ") method ...");
global $adb, $noof_group_rows;
$current_user_group_list = getCurrentUserGroupList();
$sharing_write_group_list = getWriteSharingGroupsList($module);
$query = "select groupname,groupid from vtiger_groups";
$params = array();
if (count($current_user_group_list) > 0 && count($sharing_write_group_list) > 0) {
$query .= " where (groupid in (" . generateQuestionMarks($current_user_group_list) . ") or groupid in (" . generateQuestionMarks($sharing_write_group_list) . "))";
array_push($params, $current_user_group_list, $sharing_write_group_list);
$result = $adb->pquery($query, $params);
$noof_group_rows = $adb->num_rows($result);
} elseif (count($current_user_group_list) > 0) {
$query .= " where groupid in (" . generateQuestionMarks($current_user_group_list) . ")";
array_push($params, $current_user_group_list);
$result = $adb->pquery($query, $params);
$noof_group_rows = $adb->num_rows($result);
} elseif (count($sharing_write_group_list) > 0) {
$query .= " where groupid in (" . generateQuestionMarks($sharing_write_group_list) . ")";
array_push($params, $sharing_write_group_list);
$result = $adb->pquery($query, $params);
$noof_group_rows = $adb->num_rows($result);
}
$log->debug("Exiting get_current_user_access_groups method ...");
return $result;
}
function getUserIDS($viewscope = "all_to_me")
{
global $log;
$log->debug("Entering getUserIDS() method ...");
global $current_user;
if (empty($viewscope)) {
$viewscope = "all_to_me";
}
$key = "sqluserids_" . $viewscope . "_" . $current_user->id;
$userIDS = getSqlCacheData($key);
if (!$userIDS) {
global $adb;
$sec_query = "";
$userIDS = '';
if ($viewscope == "all_to_me") {
$sec_query = "select id as userid from ec_users where status='Active'";
$result = $adb->getList($sec_query);
$userIDS .= '(';
$i = 0;
foreach ($result as $row) {
$userid = $row['userid'];
if ($i != 0) {
$userIDS .= ', ';
}
$userIDS .= $userid;
$i++;
}
if ($userIDS != '(') {
$userIDS .= ', ' . $current_user->id;
} else {
$userIDS .= $current_user->id;
}
$userIDS .= ')';
} elseif ($viewscope == "sub_user") {
if (!isset($current_user_parent_role_seq) || $current_user_parent_role_seq == "") {
$current_user_parent_role_seq = fetchUserRole($current_user->id);
}
$sec_query = "select ec_user2role.userid from ec_user2role inner join ec_users on ec_users.id=ec_user2role.userid inner join ec_role on ec_role.roleid=ec_user2role.roleid where ec_role.parentrole like '%" . $current_user_parent_role_seq . "::%'";
$result = $adb->getList($sec_query);
$userIDS .= '(';
$i = 0;
foreach ($result as $row) {
$userid = $row['userid'];
if ($i != 0) {
$userIDS .= ', ';
}
$userIDS .= $userid;
$i++;
}
$userIDS .= ')';
} elseif ($viewscope == "current_user") {
$userIDS .= '(' . $current_user->id;
$userIDS .= ')';
} elseif ($viewscope == "current_group") {
$sec_query .= "select ec_users2group.userid from ec_users2group where ec_users2group.groupid in " . getCurrentUserGroupList() . "";
$result = $adb->getList($sec_query);
$userIDS .= '(';
$i = 0;
foreach ($result as $row) {
$userid = $row['userid'];
if ($i != 0) {
$userIDS .= ', ';
}
$userIDS .= $userid;
$i++;
}
$userIDS .= ')';
} else {
$userIDS .= '(' . $viewscope . ')';
}
setSqlCacheData($key, $userIDS);
}
if ($userIDS == "()") {
$userIDS = "(-1)";
}
$log->debug("Exiting getUserIDS method ...");
return $userIDS;
}
/** To get security parameter for a particular module -- By Pavani*/
function getSecParameterforMerge($module)
{
global $current_user;
$tab_id = getTabid($module);
$sec_parameter = "";
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
if ($module == "Accounts") {
$sec_parameter .= " AND (vtiger_crmentity.smownerid IN (" . $current_user->id . ")\n\t\t\t\t\tOR vtiger_crmentity.smownerid IN (\n\t\t\t\t\tSELECT vtiger_user2role.userid\n\t\t\t\t\tFROM vtiger_user2role\n\t\t\t\t\tINNER JOIN vtiger_users ON vtiger_users.id = vtiger_user2role.userid\n\t\t\t\t\tINNER JOIN vtiger_role ON vtiger_role.roleid = vtiger_user2role.roleid\n\t\t\t\t\tWHERE vtiger_role.parentrole LIKE '" . $current_user_parent_role_seq . "::%')\n\t\t\t\t\tOR vtiger_crmentity.smownerid IN (\n\t\t\t\t\tSELECT shareduserid\n\t\t\t\t\tFROM vtiger_tmp_read_user_sharing_per\n\t\t\t\t\tWHERE userid=" . $current_user->id . "\n\t\t\t\t\tAND tabid=" . $tab_id . ")\n\t\t\t\t\tOR (vtiger_crmentity.smownerid in (0)\n\t\t\t\t\tAND (";
if (sizeof($current_user_groups) > 0) {
$sec_parameter .= " vtiger_groups.groupname IN (\n\t\t\t\t\t\t\t\tSELECT groupname\n\t\t\t\t\t\t\t\tFROM vtiger_groups\n\t\t\t\t\t\t\t\tWHERE groupid IN (" . implode(",", getCurrentUserGroupList()) . ")) OR ";
}
$sec_parameter .= " vtiger_groups.groupname IN (\n\t\t\t\tSELECT vtiger_groups.groupname\n\t\t\t\tFROM vtiger_tmp_read_group_sharing_per\n\t\t\t\tINNER JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_tmp_read_group_sharing_per.sharedgroupid\n\t\t\t\tWHERE userid=" . $current_user->id . " AND tabid=" . $tab_id . ")))) ";
}
}
return $sec_parameter;
}
/** Function to get the list query for a module
* @param $module -- module name:: Type string
* @param $where -- where:: Type string
* @returns $query -- query:: Type query
*/
function getListQuery($module, $where = '')
{
global $log;
$log->debug("Entering getListQuery(" . $module . "," . $where . ") method ...");
global $current_user;
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
require 'user_privileges/sharing_privileges_' . $current_user->id . '.php';
$tab_id = getTabid($module);
switch ($module) {
case "HelpDesk":
$query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid,\n\t\t\tvtiger_troubletickets.title, vtiger_troubletickets.status,\n\t\t\tvtiger_troubletickets.priority, vtiger_troubletickets.parent_id,\n\t\t\tvtiger_contactdetails.contactid, vtiger_contactdetails.firstname,\n\t\t\tvtiger_contactdetails.lastname, vtiger_account.accountid,\n\t\t\tvtiger_account.accountname, vtiger_ticketcf.*, vtiger_troubletickets.ticket_no\n\t\t\tFROM vtiger_troubletickets\n\t\t\tINNER JOIN vtiger_ticketcf\n\t\t\t\tON vtiger_ticketcf.ticketid = vtiger_troubletickets.ticketid\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_troubletickets.ticketid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_troubletickets.parent_id = vtiger_contactdetails.contactid\n\t\t\tLEFT JOIN vtiger_account\n\t\t\t\tON vtiger_account.accountid = vtiger_troubletickets.parent_id\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_crmentity.smownerid = vtiger_users.id\n\t\t\tLEFT JOIN vtiger_products \n\t\t\t\tON vtiger_products.productid = vtiger_troubletickets.product_id \n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
break;
case "Accounts":
//Query modified to sort by assigned to
$query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid,\n\t\t\tvtiger_account.accountname, vtiger_account.email1,\n\t\t\tvtiger_account.email2, vtiger_account.website, vtiger_account.phone,\n\t\t\tvtiger_accountbillads.bill_city,\n\t\t\tvtiger_accountscf.*\n\t\t\tFROM vtiger_account\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_account.accountid\n\t\t\tINNER JOIN vtiger_accountbillads\n\t\t\t\tON vtiger_account.accountid = vtiger_accountbillads.accountaddressid\n\t\t\tINNER JOIN vtiger_accountshipads\n\t\t\t\tON vtiger_account.accountid = vtiger_accountshipads.accountaddressid\n\t\t\tINNER JOIN vtiger_accountscf\n\t\t\t\tON vtiger_account.accountid = vtiger_accountscf.accountid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_account vtiger_account2\n\t\t\t\tON vtiger_account.parentid = vtiger_account2.accountid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$query .= " AND (vtiger_crmentity.smownerid IN (" . $current_user->id . ")\n\t\t \t\t OR vtiger_crmentity.smownerid IN (\n\t\t\t\t\t SELECT vtiger_user2role.userid\n\t\t\t\t\t FROM vtiger_user2role\n\t\t\t\t\t INNER JOIN vtiger_users\n\t\t\t\t\t\t ON vtiger_users.id = vtiger_user2role.userid\n\t\t\t\t\t INNER JOIN vtiger_role\n\t\t\t\t\t\t ON vtiger_role.roleid = vtiger_user2role.roleid\n\t\t\t\t\t WHERE vtiger_role.parentrole LIKE '" . $current_user_parent_role_seq . "::%')\n\t\t\t\t\t OR vtiger_crmentity.smownerid IN (\n\t\t\t\t\t\t SELECT shareduserid\n\t\t\t\t\t\t FROM vtiger_tmp_read_user_sharing_per\n\t\t\t\t\t\t WHERE userid=" . $current_user->id . "\n\t\t\t\t\t\t AND tabid=" . $tab_id . ")\n\t\t\t\t\t OR (";
if (sizeof($current_user_groups) > 0) {
$query .= " vtiger_groups.groupid IN (" . implode(",", getCurrentUserGroupList()) . ")\n\t\t\t\t\tOR ";
}
$query .= " vtiger_groups.groupid IN (\n\t\t\t\t \tSELECT vtiger_tmp_read_group_sharing_per.sharedgroupid\n\t\t\t\t\tFROM vtiger_tmp_read_group_sharing_per\n\t\t\t\t\tWHERE userid=" . $current_user->id . "\n\t\t\t\t\tAND tabid=" . $tab_id . "))) ";
}
break;
case "Potentials":
//Query modified to sort by assigned to
$query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid,\n\t\t\tvtiger_account.accountname,\n\t\t\tvtiger_potential.related_to, vtiger_potential.potentialname,\n\t\t\tvtiger_potential.sales_stage, vtiger_potential.amount,\n\t\t\tvtiger_potential.currency, vtiger_potential.closingdate,\n\t\t\tvtiger_potential.typeofrevenue,\n\t\t\tvtiger_potentialscf.*\n\t\t\tFROM vtiger_potential\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_potential.potentialid\n\t\t\tINNER JOIN vtiger_potentialscf\n\t\t\t\tON vtiger_potentialscf.potentialid = vtiger_potential.potentialid\n\t\t\tLEFT JOIN vtiger_account\n\t\t\t\tON vtiger_potential.related_to = vtiger_account.accountid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_potential.related_to = vtiger_contactdetails.contactid\n\t\t\tLEFT JOIN vtiger_campaign\n\t\t\t\tON vtiger_campaign.campaignid = vtiger_potential.campaignid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
break;
case "Leads":
$query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid,\n\t\t\tvtiger_leaddetails.firstname, vtiger_leaddetails.lastname,\n\t\t\tvtiger_leaddetails.company, vtiger_leadaddress.phone,\n\t\t\tvtiger_leadsubdetails.website, vtiger_leaddetails.email,\n\t\t\tvtiger_leadscf.*\n\t\t\tFROM vtiger_leaddetails\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_leaddetails.leadid\n\t\t\tINNER JOIN vtiger_leadsubdetails\n\t\t\t\tON vtiger_leadsubdetails.leadsubscriptionid = vtiger_leaddetails.leadid\n\t\t\tINNER JOIN vtiger_leadaddress\n\t\t\t\tON vtiger_leadaddress.leadaddressid = vtiger_leadsubdetails.leadsubscriptionid\n\t\t\tINNER JOIN vtiger_leadscf\n\t\t\t\tON vtiger_leaddetails.leadid = vtiger_leadscf.leadid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0\n\t\t\tAND vtiger_leaddetails.converted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
break;
case "Products":
$query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.description, vtiger_products.*, vtiger_productcf.*\n\t\t\tFROM vtiger_products\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_products.productid\n\t\t\tINNER JOIN vtiger_productcf\n\t\t\t\tON vtiger_products.productid = vtiger_productcf.productid\n\t\t\tLEFT JOIN vtiger_vendor\n\t\t\t\tON vtiger_vendor.vendorid = vtiger_products.vendor_id\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_products.handler";
if (isset($_REQUEST["from_dashboard"]) && $_REQUEST["from_dashboard"] == true && (isset($_REQUEST["type"]) && $_REQUEST["type"] == "dbrd")) {
$query .= " INNER JOIN vtiger_inventoryproductrel on vtiger_inventoryproductrel.productid = vtiger_products.productid";
}
$query .= " WHERE vtiger_crmentity.deleted = 0 " . $where;
break;
case "Documents":
$query = "SELECT case when (vtiger_users.user_name not like '') then vtiger_users.user_name else vtiger_groups.groupname end as user_name,vtiger_crmentity.crmid, vtiger_crmentity.modifiedtime,\n\t\t\tvtiger_crmentity.smownerid,vtiger_attachmentsfolder.*,vtiger_notes.*\n\t\t\tFROM vtiger_notes\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_notes.notesid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_attachmentsfolder \n\t\t\t\tON vtiger_notes.folderid = vtiger_attachmentsfolder.folderid \n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
break;
case "Contacts":
//Query modified to sort by assigned to
$query = "SELECT vtiger_contactdetails.firstname, vtiger_contactdetails.lastname,\n\t\t\tvtiger_contactdetails.title, vtiger_contactdetails.accountid,\n\t\t\tvtiger_contactdetails.email, vtiger_contactdetails.phone,\n\t\t\tvtiger_crmentity.smownerid, vtiger_crmentity.crmid\n\t\t\tFROM vtiger_contactdetails\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_contactdetails.contactid\n\t\t\tINNER JOIN vtiger_contactaddress\n\t\t\t\tON vtiger_contactaddress.contactaddressid = vtiger_contactdetails.contactid\n\t\t\tINNER JOIN vtiger_contactsubdetails\n\t\t\t\tON vtiger_contactsubdetails.contactsubscriptionid = vtiger_contactdetails.contactid\n\t\t\tINNER JOIN vtiger_contactscf\n\t\t\t\tON vtiger_contactscf.contactid = vtiger_contactdetails.contactid\n\t\t\tLEFT JOIN vtiger_account\n\t\t\t\tON vtiger_account.accountid = vtiger_contactdetails.accountid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_contactdetails vtiger_contactdetails2\n\t\t\t\tON vtiger_contactdetails.reportsto = vtiger_contactdetails2.contactid\n\t\t\tLEFT JOIN vtiger_customerdetails\n\t\t\t\tON vtiger_customerdetails.customerid = vtiger_contactdetails.contactid";
if (isset($_REQUEST["from_dashboard"]) && $_REQUEST["from_dashboard"] == true && (isset($_REQUEST["type"]) && $_REQUEST["type"] == "dbrd")) {
$query .= " INNER JOIN vtiger_campaigncontrel on vtiger_campaigncontrel.contactid = vtiger_contactdetails.contactid";
}
$query .= " WHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
break;
case "Calendar":
$query = "SELECT vtiger_activity.activityid as act_id,vtiger_crmentity.crmid, vtiger_crmentity.smownerid, vtiger_crmentity.setype,\n\t\tvtiger_activity.*,\n\t\tvtiger_contactdetails.lastname, vtiger_contactdetails.firstname,\n\t\tvtiger_contactdetails.contactid,\n\t\tvtiger_account.accountid, vtiger_account.accountname\n\t\tFROM vtiger_activity\n\t\tLEFT JOIN vtiger_activitycf\n\t\t\tON vtiger_activitycf.activityid = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_cntactivityrel\n\t\t\tON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_contactdetails\n\t\t\tON vtiger_contactdetails.contactid = vtiger_cntactivityrel.contactid\n\t\tLEFT JOIN vtiger_seactivityrel\n\t\t\tON vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\t\tLEFT OUTER JOIN vtiger_activity_reminder\n\t\t\tON vtiger_activity_reminder.activity_id = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_crmentity\n\t\t\tON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\tLEFT JOIN vtiger_users\n\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\tLEFT JOIN vtiger_groups\n\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\tLEFT OUTER JOIN vtiger_account\n\t\t\tON vtiger_account.accountid = vtiger_contactdetails.accountid\n\t\tLEFT OUTER JOIN vtiger_leaddetails\n\t \t\tON vtiger_leaddetails.leadid = vtiger_seactivityrel.crmid\n\t\tLEFT OUTER JOIN vtiger_account vtiger_account2\n\t \tON vtiger_account2.accountid = vtiger_seactivityrel.crmid\n\t\tLEFT OUTER JOIN vtiger_potential\n\t \t\tON vtiger_potential.potentialid = vtiger_seactivityrel.crmid\n\t\tLEFT OUTER JOIN vtiger_troubletickets\n\t \t\tON vtiger_troubletickets.ticketid = vtiger_seactivityrel.crmid\n\t\tLEFT OUTER JOIN vtiger_salesorder\n\t\t\tON vtiger_salesorder.salesorderid = vtiger_seactivityrel.crmid\t\n\t\tLEFT OUTER JOIN vtiger_purchaseorder\n\t\t\tON vtiger_purchaseorder.purchaseorderid = vtiger_seactivityrel.crmid\t\n\t\tLEFT OUTER JOIN vtiger_quotes\n\t\t\tON vtiger_quotes.quoteid = vtiger_seactivityrel.crmid\n\t\tLEFT OUTER JOIN vtiger_invoice\n\t ON vtiger_invoice.invoiceid = vtiger_seactivityrel.crmid\n\t\tLEFT OUTER JOIN vtiger_campaign\n\t\tON vtiger_campaign.campaignid = vtiger_seactivityrel.crmid";
//added to fix #5135
if (isset($_REQUEST['from_homepage']) && ($_REQUEST['from_homepage'] == "upcoming_activities" || $_REQUEST['from_homepage'] == "pending_activities")) {
$query .= " LEFT OUTER JOIN vtiger_recurringevents\n\t\t\t ON vtiger_recurringevents.activityid=vtiger_activity.activityid";
}
//end
$query .= " WHERE vtiger_crmentity.deleted = 0 AND activitytype != 'Emails' " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
break;
case "Emails":
$query = "SELECT DISTINCT vtiger_crmentity.crmid, vtiger_crmentity.smownerid,\n\t\t\tvtiger_activity.activityid, vtiger_activity.subject,\n\t\t\tvtiger_activity.date_start,\n\t\t\tvtiger_contactdetails.lastname, vtiger_contactdetails.firstname,\n\t\t\tvtiger_contactdetails.contactid\n\t\t\tFROM vtiger_activity\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_seactivityrel\n\t\t\t\tON vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_contactdetails.contactid = vtiger_seactivityrel.crmid\n\t\t\tLEFT JOIN vtiger_cntactivityrel\n\t\t\t\tON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\tAND vtiger_cntactivityrel.contactid = vtiger_cntactivityrel.contactid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_salesmanactivityrel\n\t\t\t\tON vtiger_salesmanactivityrel.activityid = vtiger_activity.activityid\n\t\t\tLEFT JOIN vtiger_emaildetails\n\t\t\t\tON vtiger_emaildetails.emailid = vtiger_activity.activityid\n\t\t\tWHERE vtiger_activity.activitytype = 'Emails'\n\t\t\tAND vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
break;
case "Faq":
$query = "SELECT vtiger_crmentity.crmid, vtiger_crmentity.createdtime, vtiger_crmentity.modifiedtime,\n\t\t\tvtiger_faq.*\n\t\t\tFROM vtiger_faq\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_faq.id\n\t\t\tLEFT JOIN vtiger_products\n\t\t\t\tON vtiger_faq.product_id = vtiger_products.productid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
break;
case "Vendors":
$query = "SELECT vtiger_crmentity.crmid, vtiger_vendor.*\n\t\t\tFROM vtiger_vendor\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_vendor.vendorid\n\t\t\tINNER JOIN vtiger_vendorcf\n\t\t\t\tON vtiger_vendor.vendorid = vtiger_vendorcf.vendorid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
break;
case "PriceBooks":
$query = "SELECT vtiger_crmentity.crmid, vtiger_pricebook.*, vtiger_currency_info.currency_name\n\t\t\tFROM vtiger_pricebook\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_pricebook.pricebookid\n\t\t\tINNER JOIN vtiger_pricebookcf \n\t\t\t\tON vtiger_pricebook.pricebookid = vtiger_pricebookcf.pricebookid\n\t\t\tLEFT JOIN vtiger_currency_info\n\t\t\t\tON vtiger_pricebook.currency_id = vtiger_currency_info.id\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
break;
case "Quotes":
//Query modified to sort by assigned to
$query = "SELECT vtiger_crmentity.*,\n\t\t\tvtiger_quotes.*,\n\t\t\tvtiger_quotesbillads.*,\n\t\t\tvtiger_quotesshipads.*,\n\t\t\tvtiger_potential.potentialname,\n\t\t\tvtiger_account.accountname,\n\t\t\tvtiger_currency_info.currency_name\n\t\t\tFROM vtiger_quotes\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_quotes.quoteid\n\t\t\tINNER JOIN vtiger_quotesbillads\n\t\t\t\tON vtiger_quotes.quoteid = vtiger_quotesbillads.quotebilladdressid\n\t\t\tINNER JOIN vtiger_quotesshipads\n\t\t\t\tON vtiger_quotes.quoteid = vtiger_quotesshipads.quoteshipaddressid\n\t\t\tLEFT JOIN vtiger_quotescf\n\t\t\t\tON vtiger_quotes.quoteid = vtiger_quotescf.quoteid\n\t\t\tLEFT JOIN vtiger_currency_info\n\t\t\t\tON vtiger_quotes.currency_id = vtiger_currency_info.id\n\t\t\tLEFT OUTER JOIN vtiger_account\n\t\t\t\tON vtiger_account.accountid = vtiger_quotes.accountid\n\t\t\tLEFT OUTER JOIN vtiger_potential\n\t\t\t\tON vtiger_potential.potentialid = vtiger_quotes.potentialid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_contactdetails.contactid = vtiger_quotes.contactid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users as vtiger_usersQuotes\n\t\t\t ON vtiger_usersQuotes.id = vtiger_quotes.inventorymanager\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
break;
case "PurchaseOrder":
//Query modified to sort by assigned to
$query = "SELECT vtiger_crmentity.*,\n\t\t\tvtiger_purchaseorder.*,\n\t\t\tvtiger_pobillads.*,\n\t\t\tvtiger_poshipads.*,\n\t\t\tvtiger_vendor.vendorname,\n\t\t\tvtiger_currency_info.currency_name\n\t\t\tFROM vtiger_purchaseorder\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_purchaseorder.purchaseorderid\n\t\t\tLEFT OUTER JOIN vtiger_vendor\n\t\t\t\tON vtiger_purchaseorder.vendorid = vtiger_vendor.vendorid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_purchaseorder.contactid = vtiger_contactdetails.contactid\t\n\t\t\tINNER JOIN vtiger_pobillads\n\t\t\t\tON vtiger_purchaseorder.purchaseorderid = vtiger_pobillads.pobilladdressid\n\t\t\tINNER JOIN vtiger_poshipads\n\t\t\t\tON vtiger_purchaseorder.purchaseorderid = vtiger_poshipads.poshipaddressid\n\t\t\tLEFT JOIN vtiger_purchaseordercf\n\t\t\t\tON vtiger_purchaseordercf.purchaseorderid = vtiger_purchaseorder.purchaseorderid\n\t\t\tLEFT JOIN vtiger_currency_info\n\t\t\t\tON vtiger_purchaseorder.currency_id = vtiger_currency_info.id\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
break;
case "SalesOrder":
//Query modified to sort by assigned to
$query = "SELECT vtiger_crmentity.*,\n\t\t\tvtiger_salesorder.*,\n\t\t\tvtiger_sobillads.*,\n\t\t\tvtiger_soshipads.*,\n\t\t\tvtiger_quotes.subject AS quotename,\n\t\t\tvtiger_account.accountname,\n\t\t\tvtiger_currency_info.currency_name\n\t\t\tFROM vtiger_salesorder\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_salesorder.salesorderid\n\t\t\tINNER JOIN vtiger_sobillads\n\t\t\t\tON vtiger_salesorder.salesorderid = vtiger_sobillads.sobilladdressid\n\t\t\tINNER JOIN vtiger_soshipads\n\t\t\t\tON vtiger_salesorder.salesorderid = vtiger_soshipads.soshipaddressid\n\t\t\tLEFT JOIN vtiger_salesordercf\n\t\t\t\tON vtiger_salesordercf.salesorderid = vtiger_salesorder.salesorderid\n\t\t\tLEFT JOIN vtiger_currency_info\n\t\t\t\tON vtiger_salesorder.currency_id = vtiger_currency_info.id\n\t\t\tLEFT OUTER JOIN vtiger_quotes\n\t\t\t\tON vtiger_quotes.quoteid = vtiger_salesorder.quoteid\n\t\t\tLEFT OUTER JOIN vtiger_account\n\t\t\t\tON vtiger_account.accountid = vtiger_salesorder.accountid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_salesorder.contactid = vtiger_contactdetails.contactid\t\n\t\t\tLEFT JOIN vtiger_potential\n\t\t\t\tON vtiger_potential.potentialid = vtiger_salesorder.potentialid\n\t\t\tLEFT JOIN vtiger_invoice_recurring_info\n\t\t\t\tON vtiger_invoice_recurring_info.salesorderid = vtiger_salesorder.salesorderid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
break;
case "Invoice":
//Query modified to sort by assigned to
//query modified -Code contribute by Geoff(http://forums.vtiger.com/viewtopic.php?t=3376)
$query = "SELECT vtiger_crmentity.*,\n\t\t\tvtiger_invoice.*,\n\t\t\tvtiger_invoicebillads.*,\n\t\t\tvtiger_invoiceshipads.*,\n\t\t\tvtiger_salesorder.subject AS salessubject,\n\t\t\tvtiger_account.accountname,\n\t\t\tvtiger_currency_info.currency_name\n\t\t\tFROM vtiger_invoice\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_invoice.invoiceid\n\t\t\tINNER JOIN vtiger_invoicebillads\n\t\t\t\tON vtiger_invoice.invoiceid = vtiger_invoicebillads.invoicebilladdressid\n\t\t\tINNER JOIN vtiger_invoiceshipads\n\t\t\t\tON vtiger_invoice.invoiceid = vtiger_invoiceshipads.invoiceshipaddressid\n\t\t\tLEFT JOIN vtiger_currency_info\n\t\t\t\tON vtiger_invoice.currency_id = vtiger_currency_info.id\n\t\t\tLEFT OUTER JOIN vtiger_salesorder\n\t\t\t\tON vtiger_salesorder.salesorderid = vtiger_invoice.salesorderid\n\t\t\tLEFT OUTER JOIN vtiger_account\n\t\t\t ON vtiger_account.accountid = vtiger_invoice.accountid\n\t\t\tLEFT JOIN vtiger_contactdetails\n\t\t\t\tON vtiger_contactdetails.contactid = vtiger_invoice.contactid\n\t\t\tINNER JOIN vtiger_invoicecf\n\t\t\t\tON vtiger_invoice.invoiceid = vtiger_invoicecf.invoiceid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
break;
case "Campaigns":
//Query modified to sort by assigned to
//query modified -Code contribute by Geoff(http://forums.vtiger.com/viewtopic.php?t=3376)
$query = "SELECT vtiger_crmentity.*,\n\t\t\tvtiger_campaign.*\n\t\t\tFROM vtiger_campaign\n\t\t\tINNER JOIN vtiger_crmentity\n\t\t\t\tON vtiger_crmentity.crmid = vtiger_campaign.campaignid\n\t\t\tINNER JOIN vtiger_campaignscf\n\t\t\t ON vtiger_campaign.campaignid = vtiger_campaignscf.campaignid\n\t\t\tLEFT JOIN vtiger_groups\n\t\t\t\tON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_users\n\t\t\t\tON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\tLEFT JOIN vtiger_products\n\t\t\t\tON vtiger_products.productid = vtiger_campaign.product_id\n\t\t\tWHERE vtiger_crmentity.deleted = 0 " . $where;
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1 && $defaultOrgSharingPermission[$tab_id] == 3) {
$sec_parameter = getListViewSecurityParameter($module);
$query .= $sec_parameter;
}
break;
case "Users":
$query = "select id,user_name,roleid,first_name,last_name,email1,phone_mobile,phone_work,is_admin,status from vtiger_users inner join vtiger_user2role on vtiger_user2role.userid=vtiger_users.id where deleted=0 " . $where;
break;
default:
// vtlib customization: Include the module file
$focus = CRMEntity::getInstance($module);
$query = $focus->getListQuery($module, $where);
// END
}
$log->debug("Exiting getListQuery method ...");
return $query;
}
