public function populateTo($request) { $viewer = $this->getViewer($request); $inventoryRecordId = $request->get('record'); $recordModel = Vtiger_Record_Model::getInstanceById($inventoryRecordId, $request->getModule()); $inventoryModule = $recordModel->getModule(); $inventotyfields = $inventoryModule->getFields(); $toEmailConsiderableFields = array('contact_id', 'account_id', 'vendor_id'); $db = PearDatabase::getInstance(); $to = array(); $to_info = array(); $toMailNamesList = array(); foreach ($toEmailConsiderableFields as $fieldName) { if (!array_key_exists($fieldName, $inventotyfields)) { continue; } $fieldModel = $inventotyfields[$fieldName]; if (!$fieldModel->isViewable()) { continue; } $fieldValue = $recordModel->get($fieldName); if (empty($fieldValue)) { continue; } $referenceList = $fieldModel->getReferenceList(); $referenceModule = $referenceList[0]; $fieldLabel = Vtiger_Util_Helper::getLabel($fieldValue); $referenceModuleModel = Vtiger_Module_Model::getInstance($referenceModule); $emailFields = $referenceModuleModel->getFieldsByType('email'); if (count($emailFields) <= 0) { continue; } $current_user = Users_Record_Model::getCurrentUserModel(); $queryGenerator = new QueryGenerator($referenceModule, $current_user); $queryGenerator->setFields(array_keys($emailFields)); $query = $queryGenerator->getQuery(); $query .= ' AND crmid = ' . $fieldValue; $result = $db->pquery($query, array()); $num_rows = $db->num_rows($result); if ($num_rows <= 0) { continue; } foreach ($emailFields as $fieldName => $emailFieldModel) { $emailValue = $db->query_result($result, 0, $fieldName); if (!empty($emailValue)) { $to[] = $emailValue; $to_info[$fieldValue][] = $emailValue; $toMailNamesList[$fieldValue][] = array('label' => $fieldLabel, 'value' => $emailValue); break; } } if (!empty($to)) { break; } } $viewer->assign('TO', $to); $viewer->assign('TOMAIL_NAMES_LIST', $toMailNamesList); $viewer->assign('TOMAIL_INFO', $to_info); }
public function getWorkflowQuery($workflow) { $conditions = Zend_Json::decode(decode_html($workflow->test)); $moduleName = $workflow->moduleName; $queryGenerator = new QueryGenerator($moduleName, $this->user); $queryGenerator->setFields(array('id')); $this->addWorkflowConditionsToQueryGenerator($queryGenerator, $conditions); if ($moduleName == 'Calendar' || $moduleName == 'Events') { if ($conditions) { $queryGenerator->addConditionGlue('AND'); } // We should only get the records related to proper activity type if ($moduleName == 'Calendar') { $queryGenerator->addCondition('activitytype', 'Emails', 'n'); $queryGenerator->addCondition('activitytype', 'Task', 'e', 'AND'); } else { if ($moduleName == "Events") { $queryGenerator->addCondition('activitytype', 'Emails', 'n'); $queryGenerator->addCondition('activitytype', 'Task', 'n', 'AND'); } } } $query = $queryGenerator->getQuery(); return $query; }
/** * Function returns the Query for the relationhips * @param <Vtiger_Record_Model> $recordModel * @param type $actions * @return <String> */ public function getQuery($recordModel, $actions = false) { $parentModuleModel = $this->getParentModuleModel(); $relatedModuleModel = $this->getRelationModuleModel(); $relatedModuleName = $relatedModuleModel->get('name'); $parentModuleName = $parentModuleModel->get('name'); $functionName = $this->get('name'); $focus = CRMEntity::getInstance($parentModuleName); $focus->id = $recordModel->getId(); if (method_exists($parentModuleModel, $functionName)) { $query = $parentModuleModel->{$functionName}($recordModel, $relatedModuleModel); } else { $result = $focus->{$functionName}($recordModel->getId(), $parentModuleModel->getId(), $relatedModuleModel->getId(), $actions); $query = $result['query']; } //modify query if any module has summary fields, those fields we are displayed in related list of that module $relatedListFields = $relatedModuleModel->getConfigureRelatedListFields(); if (count($relatedListFields) > 0) { $currentUser = Users_Record_Model::getCurrentUserModel(); $queryGenerator = new QueryGenerator($relatedModuleName, $currentUser); $queryGenerator->setFields($relatedListFields); $selectColumnSql = $queryGenerator->getSelectClauseColumnSQL(); $newQuery = spliti('FROM', $query); $selectColumnSql = 'SELECT DISTINCT vtiger_crmentity.crmid,' . $selectColumnSql; } if ($functionName == ('get_pricebook_products' || 'get_pricebook_services')) { $selectColumnSql = $selectColumnSql . ', vtiger_pricebookproductrel.listprice'; } $query = $selectColumnSql . ' FROM ' . $newQuery[1]; return $query; }
function getCalculations(Vtiger_Request $request) { $fromModule = $request->get('fromModule'); $record = $request->get('record'); $showtype = $request->get('showtype'); $rqLimit = $request->get('limit'); $db = PearDatabase::getInstance(); $fields = ['id', 'name', 'calculationsstatus']; $limit = 10; $params = []; if (!empty($rqLimit)) { $limit = $rqLimit; } if ($fromModule == 'Accounts') { $fields[] = 'potentialid'; } elseif ($fromModule == 'Potentials') { $fields[] = 'assigned_user_id'; } $calculationConfig = Settings_SalesProcesses_Module_Model::getConfig('calculation'); $calculationsStatus = $calculationConfig['calculationsstatus']; $currentUser = Users_Record_Model::getCurrentUserModel(); $module = 'Calculations'; $instance = CRMEntity::getInstance($module); $securityParameter = $instance->getUserAccessConditionsQuerySR($module, $currentUser); $queryGenerator = new QueryGenerator($module, $currentUser); $queryGenerator->setFields($fields); $sql = $queryGenerator->getQuery(); if ($securityParameter != '') { $sql .= $securityParameter; } $calculationsStatusSearch = implode("','", $calculationsStatus); $showtype = $request->get('showtype'); if ($showtype == 'archive') { $sql .= " AND vtiger_calculations.calculationsstatus IN ('{$calculationsStatusSearch}')"; } else { $sql .= " AND vtiger_calculations.calculationsstatus NOT IN ('{$calculationsStatusSearch}')"; } if ($fromModule == 'Accounts') { $sql .= ' AND vtiger_calculations.relatedid = ?'; $params[] = $record; } elseif ($fromModule == 'Potentials') { $sql .= ' AND vtiger_calculations.potentialid = ?'; $params[] = $record; } $sql .= ' LIMIT ' . $limit; $result = $db->pquery($sql, $params); $returnData = array(); for ($i = 0; $i < $db->num_rows($result); $i++) { $returnData[] = $db->query_result_rowdata($result, $i); } return $returnData; }
/** * Function that generates Export Query based on the mode * @param Vtiger_Request $request * @return <String> export query */ function getExportQuery(Vtiger_Request $request) { $currentUser = Users_Record_Model::getCurrentUserModel(); $cvId = $request->get('viewname'); $moduleName = $request->get('source_module'); $queryGenerator = new QueryGenerator($moduleName, $currentUser); if (!empty($cvId)) { $queryGenerator->initForCustomViewById($cvId); } $acceptedFields = array('user_name', 'title', 'first_name', 'last_name', 'email1', 'email2', 'secondaryemail', 'phone_work', 'phone_mobile', 'phone_fax', 'address_street', 'address_city', 'address_state', 'address_country', 'address_postalcode'); $queryGenerator->setFields($acceptedFields); $query = $queryGenerator->getQuery(); return $query; }
private function convertMap2Array() { global $adb, $current_user; $xml = $this->getXMLContent(); if (isset($xml->records)) { foreach ($xml->records->record as $k => $v) { if (isset($v->action)) { $action = strtolower((string) $v->action); if (!in_array($action, $this->actions)) { $action = $this->default_action; } } else { $action = $this->default_action; } if (isset($v->id)) { $rs = $adb->pquery('select setype from vtiger_crmentity where crmid=? and deleted=0', array((int) $v->id)); if ($adb->num_rows($rs) == 1) { $recinfo = $adb->fetch_array($rs); $this->mapping[$action]['ids'][] = (int) $v->id; $this->mapping[$action][$recinfo['setype']][] = (int) $v->id; if (!in_array($recinfo['setype'], $this->mapping['modules'])) { $this->mapping['modules'][] = $recinfo['setype']; } } } else { $tabid = getTabid((string) $v->module); $ui4rs = $adb->pquery('select fieldname from vtiger_field where uitype=4 and tabid=?', array($tabid)); $ui4 = $adb->query_result($ui4rs, 0, 0); $queryGenerator = new QueryGenerator((string) $v->module, $current_user); $queryGenerator->setFields(array('id')); $queryGenerator->addCondition($ui4, (string) $v->value, 'e'); $query = $queryGenerator->getQuery(); $idrs = $adb->pquery($query, array()); if ($idrs and $adb->num_rows($idrs) > 0) { $id = $adb->query_result($idrs, 0, 0); $this->mapping[$action]['ids'][] = (int) $id; $this->mapping[$action][(string) $v->module][] = (int) $id; if (!in_array($recinfo['setype'], $this->mapping['modules'])) { $this->mapping['modules'][] = $recinfo['setype']; } } } } } }
/** * Function returns the Query for the relationhips * @param <Vtiger_Record_Model> $recordModel * @param type $actions * @return <String> */ public function getQuery($recordModel, $actions = false, $relationListView_Model = false) { $parentModuleModel = $this->getParentModuleModel(); $relatedModuleModel = $this->getRelationModuleModel(); $relatedModuleName = $relatedModuleModel->get('name'); $parentModuleName = $parentModuleModel->get('name'); $functionName = $this->get('name'); $focus = CRMEntity::getInstance($parentModuleName); $focus->id = $recordModel->getId(); if (method_exists($parentModuleModel, $functionName)) { $query = $parentModuleModel->{$functionName}($recordModel, $relatedModuleModel); } else { $result = $focus->{$functionName}($recordModel->getId(), $parentModuleModel->getId(), $relatedModuleModel->getId(), $actions); $query = $result['query']; } //modify query if any module has summary fields, those fields we are displayed in related list of that module $relatedListFields = $this->getRelationFields(true, true); if (count($relatedListFields) == 0) { $relatedListFields = $relatedModuleModel->getConfigureRelatedListFields(); } if (count($relatedListFields) > 0) { $currentUser = Users_Record_Model::getCurrentUserModel(); $queryGenerator = new QueryGenerator($relatedModuleName, $currentUser); $queryGenerator->setFields($relatedListFields); $selectColumnSql = $queryGenerator->getSelectClauseColumnSQL(); $newQuery = explode('FROM', $query); $selectColumnSql = 'SELECT DISTINCT vtiger_crmentity.crmid, ' . $selectColumnSql; } if ($functionName == 'get_product_pricebooks') { $selectColumnSql = $selectColumnSql . ' ,vtiger_pricebookproductrel.listprice, vtiger_pricebook.currency_id, vtiger_products.unit_price'; } if ($functionName == 'get_service_pricebooks') { $selectColumnSql = $selectColumnSql . ' ,vtiger_pricebookproductrel.listprice, vtiger_pricebook.currency_id, vtiger_service.unit_price'; } if ($selectColumnSql && $newQuery[1]) { $query = $selectColumnSql . ' FROM ' . $newQuery[1]; } if ($relationListView_Model) { $searchParams = $relationListView_Model->get('search_params'); $this->addSearchConditions($query, $searchParams, $relatedModuleName); } return $query; }
public function getData(Vtiger_Request $request, $widget) { $db = PearDatabase::getInstance(); $fields = ['id', 'name', 'calculationsstatus', 'relatedid', 'hdnGrandTotal', 'assigned_user_id']; $limit = 10; $params = []; if (!empty($widget->get('limit'))) { $limit = $widget->get('limit'); } $calculationConfig = Settings_SalesProcesses_Module_Model::getConfig('calculation'); $calculationsStatus = $calculationConfig['calculationsstatus']; $currentUser = Users_Record_Model::getCurrentUserModel(); $module = 'Calculations'; $instance = CRMEntity::getInstance($module); $securityParameter = $instance->getUserAccessConditionsQuerySR($module, $currentUser); $queryGenerator = new QueryGenerator($module, $currentUser); $queryGenerator->setFields($fields); $sql = $queryGenerator->getQuery(); if ($securityParameter != '') { $sql .= $securityParameter; } if (!empty($calculationsStatus)) { $calculationsStatusSearch = implode("','", $calculationsStatus); $sql .= " AND vtiger_calculations.calculationsstatus NOT IN ('{$calculationsStatusSearch}')"; } $showtype = $request->get('showtype'); if ($showtype == 'common') { $shownersTable = Vtiger_SharedOwner_UIType::getShownerTable($module); $sql .= ' AND vtiger_crmentity.crmid IN (SELECT DISTINCT crmid FROM ' . $shownersTable . ' WHERE userid = ?'; } else { $sql .= ' AND vtiger_crmentity.smownerid = ?'; } $params[] = $currentUser->getId(); $sql .= ' LIMIT ' . $limit; $result = $db->pquery($sql, $params); $returnData = array(); for ($i = 0; $i < $db->num_rows($result); $i++) { $returnData[] = $db->query_result_rowdata($result, $i); } return $returnData; }
public function getData(Vtiger_Request $request, $widget) { $db = PearDatabase::getInstance(); $fields = ['id', 'potentialname', 'sales_stage', 'related_to', 'assigned_user_id']; $limit = 10; $params = []; if (!empty($widget->get('limit'))) { $limit = $widget->get('limit'); } $potentialConfig = Settings_SalesProcesses_Module_Model::getConfig('potential'); $potentialSalesStage = $potentialConfig['salesstage']; $currentUser = Users_Record_Model::getCurrentUserModel(); $module = 'Potentials'; $instance = CRMEntity::getInstance($module); $securityParameter = $instance->getUserAccessConditionsQuerySR($module, $currentUser); $queryGenerator = new QueryGenerator($module, $currentUser); $queryGenerator->setFields($fields); $sql = $queryGenerator->getQuery(); if ($securityParameter != '') { $sql .= $securityParameter; } if (!empty($potentialSalesStage)) { $potentialSalesStageSearch = implode("','", $potentialSalesStage); $sql .= " AND vtiger_potential.sales_stage NOT IN ('{$potentialSalesStageSearch}')"; } $showtype = $request->get('showtype'); if ($showtype == 'common') { $sql .= ' AND FIND_IN_SET( ?, vtiger_crmentity.shownerid )'; } else { $sql .= ' AND vtiger_crmentity.smownerid = ?'; } $params[] = $currentUser->getId(); $sql .= ' LIMIT ' . $limit; $result = $db->pquery($sql, $params); $returnData = array(); for ($i = 0; $i < $db->num_rows($result); $i++) { $returnData[] = $db->query_result_rowdata($result, $i); } return $returnData; }
public function getData(Vtiger_Request $request, $widget) { $db = PearDatabase::getInstance(); $fields = ['id', 'assetname', 'dateinservice', 'parent_id']; $limit = 10; $params = []; if (!empty($widget->get('limit'))) { $limit = $widget->get('limit'); } $assetConfig = Settings_SalesProcesses_Module_Model::getConfig('asset'); $currentUser = Users_Record_Model::getCurrentUserModel(); $module = 'Assets'; $instance = CRMEntity::getInstance($module); $securityParameter = $instance->getUserAccessConditionsQuerySR($module, $currentUser); $queryGenerator = new QueryGenerator($module, $currentUser); $queryGenerator->setFields($fields); $sql = $queryGenerator->getQuery(); if ($securityParameter != '') { $sql .= $securityParameter; } if (!empty($assetStatus)) { $assetStatus = implode("','", $assetConfig['assetstatus']); $sql .= " AND vtiger_assets.assetstatus NOT IN ('{$assetStatus}')"; } $showtype = $request->get('showtype'); if ($showtype == 'common') { $sql .= ' AND FIND_IN_SET( ?, vtiger_crmentity.shownerid )'; } else { $sql .= ' AND vtiger_crmentity.smownerid = ?'; } $sql .= ' AND vtiger_assets.pot_renewal = 0'; $params[] = $currentUser->getId(); $sql .= ' ORDER BY vtiger_assets.dateinservice ASC LIMIT ' . $limit; $result = $db->pquery($sql, $params); $returnData = array(); for ($i = 0; $i < $db->num_rows($result); $i++) { $returnData[] = $db->query_result_rowdata($result, $i); } return $returnData; }
/** * Function to get all the available picklist values for the current field * @return <Array> List of picklist values if the field is of type MultiReferenceValue. */ public function getPicklistValues() { if (!empty($this->get('picklistValues'))) { return $this->get('picklistValues'); } $params = $this->get('field')->getFieldParams(); $db = PearDatabase::getInstance(); $currentUser = Users_Record_Model::getCurrentUserModel(); $fieldInfo = Vtiger_Functions::getModuleFieldInfoWithId($params['field']); $queryGenerator = new QueryGenerator($params['module'], $currentUser); $queryGenerator->setFields([$fieldInfo['columnname']]); if ($params['filterField'] != '-') { $queryGenerator->addCondition($params['filterField'], $params['filterValue'], 'e'); } $query = $queryGenerator->getQuery(); $result = $db->query($query); $values = []; while ($value = $db->getSingleValue($result)) { $values[$value] = vtranslate($value, $params['module']); } $this->set('picklistValues', $values); return $values; }
/** * this function returns the widget information for an module type widget */ private function getModuleFilters($sid) { global $adb, $current_user; $querycvid = "select vtiger_homemoduleflds.fieldname,vtiger_homemodule.* from vtiger_homemoduleflds\n\t\t\t\t\tleft join vtiger_homemodule on vtiger_homemodule.stuffid=vtiger_homemoduleflds.stuffid\n\t\t\t\t\twhere vtiger_homemoduleflds.stuffid=?"; $resultcvid = $adb->pquery($querycvid, array($sid)); $modname = $adb->query_result($resultcvid, 0, "modulename"); $cvid = $adb->query_result($resultcvid, 0, "customviewid"); $maxval = $adb->query_result($resultcvid, 0, "maxentries"); $column_count = $adb->num_rows($resultcvid); $cvid_check_query = $adb->pquery("SELECT * FROM vtiger_customview WHERE cvid = ?", array($cvid)); if (isPermitted($modname, 'index') == "yes") { if ($adb->num_rows($cvid_check_query) > 0) { $focus = CRMEntity::getInstance($modname); $oCustomView = new CustomView($modname); if ($modname == "Calendar") { $listquery = getListQuery($modname); if (trim($listquery) == '') { $listquery = $focus->getListQuery($modname); } $query = $oCustomView->getModifiedCvListQuery($cvid, $listquery, $modname); } else { $queryGenerator = new QueryGenerator($modname, $current_user); $queryGenerator->initForCustomViewById($cvid); $customViewFields = $queryGenerator->getCustomViewFields(); $fields = $queryGenerator->getFields(); $newFields = array_diff($fields, $customViewFields); for ($l = 0; $l < $column_count; $l++) { $customViewColumnInfo = $adb->query_result($resultcvid, $l, "fieldname"); $details = explode(':', $customViewColumnInfo); $newFields[] = $details[2]; } $queryGenerator->setFields($newFields); $query = $queryGenerator->getQuery(); } $count_result = $adb->query(mkCountQuery($query)); $noofrows = $adb->query_result($count_result, 0, "count"); $navigation_array = getNavigationValues(1, $noofrows, $maxval); //To get the current language file global $current_language, $app_strings; $fieldmod_strings = return_module_language($current_language, $modname); if ($modname == "Calendar") { $query .= "AND vtiger_activity.activitytype NOT IN ('Emails')"; } $list_result = $adb->query($query . " LIMIT 0," . $maxval); if ($modname == "Calendar") { for ($l = 0; $l < $column_count; $l++) { $fieldinfo = $adb->query_result($resultcvid, $l, "fieldname"); list($tabname, $colname, $fldname, $fieldmodlabel) = explode(":", $fieldinfo); $fieldheader = explode("_", $fieldmodlabel, 2); $fldlabel = $fieldheader[1]; $pos = strpos($fldlabel, "_"); if ($pos == true) { $fldlabel = str_replace("_", " ", $fldlabel); } $field_label = isset($app_strings[$fldlabel]) ? $app_strings[$fldlabel] : (isset($fieldmod_strings[$fldlabel]) ? $fieldmod_strings[$fldlabel] : $fldlabel); $cv_presence = $adb->pquery("SELECT * from vtiger_cvcolumnlist WHERE cvid = ? and columnname LIKE '%" . $fldname . "%'", array($cvid)); if ($is_admin == false) { $fld_permission = getFieldVisibilityPermission($modname, $current_user->id, $fldname); } if ($fld_permission == 0 && $adb->num_rows($cv_presence)) { $field_query = $adb->pquery("SELECT fieldlabel FROM vtiger_field WHERE fieldname = ? AND tablename = ? and vtiger_field.presence in (0,2)", array($fldname, $tabname)); $field_label = $adb->query_result($field_query, 0, 'fieldlabel'); $header[] = $field_label; } $fieldcolumns[$fldlabel] = array($tabname => $colname); } $listview_entries = getListViewEntries($focus, $modname, $list_result, $navigation_array, "", "", "EditView", "Delete", $oCustomView, 'HomePage', $fieldcolumns); } else { $controller = new ListViewController($adb, $current_user, $queryGenerator); $controller->setHeaderSorting(false); $header = $controller->getListViewHeader($focus, $modname, '', '', '', true); $listview_entries = $controller->getListViewEntries($focus, $modname, $list_result, $navigation_array, true); } $return_value = array('ModuleName' => $modname, 'cvid' => $cvid, 'Maxentries' => $maxval, 'Header' => $header, 'Entries' => $listview_entries); if (sizeof($header) != 0) { return $return_value; } else { return array('Entries' => "Fields not found in Selected Filter"); } } else { return array('Entries' => "<font color='red'>Filter You have Selected is Not Found</font>"); } } else { return array('Entries' => "<font color='red'>Permission Denied</font>"); } }
/** * Function to get relation query for particular module with function name * @param <record> $recordId * @param <String> $functionName * @param Vtiger_Module_Model $relatedModule * @return <String> */ public function getRelationQuery($recordId, $functionName, $relatedModule) { $relatedModuleName = $relatedModule->getName(); $focus = CRMEntity::getInstance($this->getName()); $focus->id = $recordId; $result = $focus->{$functionName}($recordId, $this->getId(), $relatedModule->getId()); $query = $result['query'] . ' ' . $this->getSpecificRelationQuery($relatedModuleName); $nonAdminQuery = $this->getNonAdminAccessControlQueryForRelation($relatedModuleName); //modify query if any module has summary fields, those fields we are displayed in related list of that module $relatedListFields = $relatedModule->getConfigureRelatedListFields(); if ($relatedModuleName == 'Documents') { $relatedListFields['filelocationtype'] = 'filelocationtype'; $relatedListFields['filestatus'] = 'filestatus'; } if (count($relatedListFields) > 0) { $currentUser = Users_Record_Model::getCurrentUserModel(); $queryGenerator = new QueryGenerator($relatedModuleName, $currentUser); $queryGenerator->setFields($relatedListFields); $selectColumnSql = $queryGenerator->getSelectClauseColumnSQL(); $newQuery = spliti('FROM', $query); $selectColumnSql = 'SELECT DISTINCT vtiger_crmentity.crmid,' . $selectColumnSql; $query = $selectColumnSql . ' FROM ' . $newQuery[1]; } if ($nonAdminQuery) { $query = appendFromClauseToQuery($query, $nonAdminQuery); } return $query; }
function getExportRecordIds($moduleName, $viewid, $input) { global $adb, $current_user, $list_max_entries_per_page; $idstring = vtlib_purify($input['idstring']); $export_data = vtlib_purify($input['export_data']); if (in_array($moduleName, getInventoryModules()) && $export_data == 'currentpage') { $queryGenerator = new QueryGenerator($moduleName, $current_user); $queryGenerator->initForCustomViewById($viewid); if ($input['query'] == 'true') { $queryGenerator->addUserSearchConditions($input); } $queryGenerator->setFields(array('id')); $query = $queryGenerator->getQuery(); $current_page = ListViewSession::getCurrentPage($moduleName, $viewid); $limit_start_rec = ($current_page - 1) * $list_max_entries_per_page; if ($limit_start_rec < 0) { $limit_start_rec = 0; } $query .= ' LIMIT ' . $limit_start_rec . ',' . $list_max_entries_per_page; $result = $adb->pquery($query, array()); $idstring = array(); $focus = CRMEntity::getInstance($moduleName); for ($i = 0; $i < $adb->num_rows($result); $i++) { $idstring[] = $adb->query_result($result, $i, $focus->table_index); } $idstring = implode(';', $idstring); $export_data = 'selecteddata'; } return $idstring . '#@@#' . $export_data; }
/** function used to get the top 5 quotes from the ListView query * @return array $values - array with the title, header and entries like Array('Title'=>$title,'Header'=>$listview_header,'Entries'=>$listview_entries) where as listview_header and listview_entries are arrays of header and entity values which are returned from function getListViewHeader and getListViewEntries */ function getTopQuotes($maxval, $calCnt) { require_once "data/Tracker.php"; require_once 'modules/Quotes/Quotes.php'; require_once 'include/logging.php'; require_once 'include/ListView/ListView.php'; require_once 'include/utils/utils.php'; require_once 'modules/CustomView/CustomView.php'; global $app_strings, $current_language, $current_user; $current_module_strings = return_module_language($current_language, 'Quotes'); global $list_max_entries_per_page, $adb, $theme, $mod_strings; $log = LoggerManager::getLogger('quote_list'); $url_string = ''; $sorder = ''; $oCustomView = new CustomView("Quotes"); $customviewcombo_html = $oCustomView->getCustomViewCombo(); if (isset($_REQUEST['viewname']) == false || $_REQUEST['viewname'] == '') { if ($oCustomView->setdefaultviewid != "") { $viewid = $oCustomView->setdefaultviewid; } else { $viewid = "0"; } } $theme_path = "themes/" . $theme . "/"; $image_path = $theme_path . "images/"; //Retreive the list from Database //<<<<<<<<<customview>>>>>>>>> $date_var = date('Y-m-d'); $currentModule = 'Quotes'; $viewId = getCvIdOfAll($currentModule); $queryGenerator = new QueryGenerator($currentModule, $current_user); $queryGenerator->initForCustomViewById($viewId); $meta = $queryGenerator->getMeta($currentModule); $accessibleFieldNameList = array_keys($meta->getModuleFields()); $customViewFields = $queryGenerator->getCustomViewFields(); $fields = $queryGenerator->getFields(); $newFields = array_diff($fields, $customViewFields); $widgetFieldsList = array('subject', 'potential_id', 'account_id', 'total'); $widgetFieldsList = array_intersect($accessibleFieldNameList, $widgetFieldsList); $widgetSelectedFields = array_chunk(array_intersect($customViewFields, $widgetFieldsList), 2); //select the first chunk of two fields $widgetSelectedFields = $widgetSelectedFields[0]; if (count($widgetSelectedFields) < 2) { $widgetSelectedFields = array_chunk(array_merge($widgetSelectedFields, $accessibleFieldNameList), 2); //select the first chunk of two fields $widgetSelectedFields = $widgetSelectedFields[0]; } $newFields = array_merge($newFields, $widgetSelectedFields); $queryGenerator->setFields($newFields); $_REQUEST = getTopQuotesSearch($_REQUEST, array('assigned_user_id' => $current_user->column_fields['user_name'], 'validtill' => $date_var, 'quotestage.Rejected' => $current_module_strings['Rejected'], 'quotestage.Accepted' => $current_module_strings['Accepted'])); $queryGenerator->addUserSearchConditions($_REQUEST); $search_qry = '&query=true' . getSearchURL($_REQUEST); $query = $queryGenerator->getQuery(); //<<<<<<<<customview>>>>>>>>> $query .= " LIMIT " . $adb->sql_escape_string($maxval); if ($calCnt == 'calculateCnt') { $list_result_rows = $adb->query(mkCountQuery($query)); return $adb->query_result($list_result_rows, 0, 'count'); } $list_result = $adb->query($query); //Retreiving the no of rows $noofrows = $adb->num_rows($list_result); //Retreiving the start value from request if (isset($_REQUEST['start']) && $_REQUEST['start'] != '') { $start = vtlib_purify($_REQUEST['start']); } else { $start = 1; } //Retreive the Navigation array $navigation_array = getNavigationValues($start, $noofrows, $list_max_entries_per_page); if ($navigation_array['start'] == 1) { if ($noofrows != 0) { $start_rec = $navigation_array['start']; } else { $start_rec = 0; } if ($noofrows > $list_max_entries_per_page) { $end_rec = $navigation_array['start'] + $list_max_entries_per_page - 1; } else { $end_rec = $noofrows; } } else { if ($navigation_array['next'] > $list_max_entries_per_page) { $start_rec = $navigation_array['next'] - $list_max_entries_per_page; $end_rec = $navigation_array['next'] - 1; } else { $start_rec = $navigation_array['prev'] + $list_max_entries_per_page; $end_rec = $noofrows; } } $focus = new Quotes(); $title = array('TopOpenQuotes.gif', $current_module_strings['LBL_MY_TOP_QUOTE'], 'home_mytopquote'); //Retreive the List View Table Header $controller = new ListViewController($adb, $current_user, $queryGenerator); $controller->setHeaderSorting(false); $header = $controller->getListViewHeader($focus, $currentModule, $url_string, $sorder, $order_by, true); $entries = $controller->getListViewEntries($focus, $currentModule, $list_result, $navigation_array, true); $values = array('ModuleName' => 'Quotes', 'Title' => $title, 'Header' => $header, 'Entries' => $entries, 'search_qry' => $search_qry); if ($display_empty_home_blocks && $noofrows == 0 || $noofrows > 0) { return $values; } }
/** * Function that generates Export Query based on the mode * @param Vtiger_Request $request * @return <String> export query */ function getExportQuery(Vtiger_Request $request) { $currentUser = Users_Record_Model::getCurrentUserModel(); $mode = $request->getMode(); $cvId = $request->get('viewname'); $moduleName = $request->get('source_module'); $queryGenerator = new QueryGenerator($moduleName, $currentUser); $queryGenerator->initForCustomViewById($cvId); $fieldInstances = $this->moduleFieldInstances; $accessiblePresenceValue = array(0, 2); foreach ($fieldInstances as $field) { // Check added as querygenerator is not checking this for admin users $presence = $field->get('presence'); if (in_array($presence, $accessiblePresenceValue)) { $fields[] = $field->getName(); } } $queryGenerator->setFields($fields); $query = $queryGenerator->getQuery(); if (in_array($moduleName, getInventoryModules())) { $query = $this->moduleInstance->getExportQuery($this->focus, $query); } $this->accessibleFields = $queryGenerator->getFields(); switch ($mode) { case 'ExportAllData': return $query; break; case 'ExportCurrentPage': $pagingModel = new Vtiger_Paging_Model(); $limit = $pagingModel->getPageLimit(); $currentPage = $request->get('page'); if (empty($currentPage)) { $currentPage = 1; } $currentPageStart = ($currentPage - 1) * $limit; if ($currentPageStart < 0) { $currentPageStart = 0; } $query .= ' LIMIT ' . $currentPageStart . ',' . $limit; return $query; break; case 'ExportSelectedRecords': $idList = $this->getRecordsListFromRequest($request); $baseTable = $this->moduleInstance->get('basetable'); $baseTableColumnId = $this->moduleInstance->get('basetableid'); if (!empty($idList)) { if (!empty($baseTable) && !empty($baseTableColumnId)) { $idList = implode(',', $idList); $query .= ' AND ' . $baseTable . '.' . $baseTableColumnId . ' IN (' . $idList . ')'; } } else { $query .= ' AND ' . $baseTable . '.' . $baseTableColumnId . ' NOT IN (' . implode(',', $request->get('excluded_ids')) . ')'; } return $query; break; default: return $query; break; } }
/** * @param String $term: search term * @param String $filter: operator to use: eq, neq, startswith, endswith, contains * @param String $searchinmodule: valid module to search in * @param String $fields: comma separated list of fields to search in * @param String $returnfields: comma separated list of fields to return as result, if empty $fields will be returned * @param Number $limit: maximum number of values to return * @param Users $user * @return Array values found: crmid => array($returnfields) */ function getFieldAutocomplete($term, $filter, $searchinmodule, $fields, $returnfields, $limit, $user) { global $current_user, $log, $adb, $default_charset; $respuesta = array(); if (empty($searchinmodule) or empty($fields)) { return $respuesta; } if (!(vtlib_isModuleActive($searchinmodule) and isPermitted($searchinmodule, 'DetailView'))) { return $respuesta; } if (empty($returnfields)) { $returnfields = $fields; } if (empty($limit)) { $limit = 30; } // hard coded default if (empty($term)) { $term = '%'; $op = 'like'; } else { switch ($filter) { case 'eq': $op = 'e'; break; case 'neq': $op = 'n'; break; case 'startswith': $op = 's'; break; case 'endswith': $op = 'ew'; break; case 'contains': $op = 'c'; break; default: $op = 'e'; break; } } $current_user = VTWS_PreserveGlobal::preserveGlobal('current_user', $user); $smod = new $searchinmodule(); $sindex = $smod->table_index; $queryGenerator = new QueryGenerator($searchinmodule, $current_user); $sfields = explode(',', $fields); $rfields = explode(',', $returnfields); $flds = array_unique(array_merge($rfields, $sfields, array('id'))); $queryGenerator->setFields($flds); foreach ($sfields as $sfld) { $queryGenerator->addCondition($sfld, $term, $op); } $query = $queryGenerator->getQuery(); $rsemp = $adb->query($query); global $log; $log->fatal($rsemp); $wsid = vtyiicpng_getWSEntityId($searchinmodule); while ($emp = $adb->fetch_array($rsemp)) { $rsp = array(); foreach ($rfields as $rf) { $rsp[$rf] = html_entity_decode($emp[$rf], ENT_QUOTES, $default_charset); } $respuesta[] = array('crmid' => $wsid . $emp[$sindex], 'crmfields' => $rsp); if (count($respuesta) >= $limit) { break; } } VTWS_PreserveGlobal::flush(); return $respuesta; }
function __FQNExtendedQueryGetQuery($q, $user) { global $adb, $log; $moduleRegex = "/[fF][rR][Oo][Mm]\\s+([^\\s;]+)(.*)/"; preg_match($moduleRegex, $q, $m); $mainModule = trim($m[1]); // pickup meta data of module $webserviceObject = VtigerWebserviceObject::fromName($adb, $mainModule); $handlerPath = $webserviceObject->getHandlerPath(); $handlerClass = $webserviceObject->getHandlerClass(); require_once $handlerPath; $handler = new $handlerClass($webserviceObject, $user, $adb, $log); $meta = $handler->getMeta(); $mainModule = $meta->getTabName(); // normalize module name // check modules if (!$meta->isModuleEntity()) { throw new WebserviceException('INVALID_MODULE', "Given main module ({$mainModule}) cannot be found"); } // check permission on module $entityName = $meta->getEntityName(); $types = vtws_listtypes(null, $user); if (!in_array($entityName, $types['types'])) { throw new WebServiceException(WebServiceErrorCode::$ACCESSDENIED, "Permission to perform the operation on module ({$mainModule}) is denied"); } if (!$meta->hasReadAccess()) { throw new WebServiceException(WebServiceErrorCode::$ACCESSDENIED, "Permission to read module is denied"); } // user has enough permission to start process $fieldcolumn = $meta->getFieldColumnMapping(); $queryGenerator = new QueryGenerator($mainModule, $user); $queryColumns = trim(substr($q, 6, stripos($q, ' from ') - 5)); $queryColumns = explode(',', $queryColumns); $queryColumns = array_map(trim, $queryColumns); $countSelect = $queryColumns == array('count(*)'); $queryRelatedModules = array(); foreach ($queryColumns as $k => $field) { if (strpos($field, '.') > 0) { list($m, $f) = explode('.', $field); if (!isset($queryRelatedModules[$m])) { $relhandler = vtws_getModuleHandlerFromName($m, $user); $relmeta = $relhandler->getMeta(); $mn = $relmeta->getTabName(); // normalize module name $queryRelatedModules[$mn] = $relmeta; if ($m != $mn) { $queryColumns[$k] = $mn . '.' . $f; } } } } $queryColumns[] = 'id'; // add ID column to follow REST interface behaviour $queryGenerator->setFields($queryColumns); // take apart conditionals $queryConditions = trim($m[2], ' ;'); $moduleRegex = "/[fF][rR][Oo][Mm]\\s+([^\\s;]+)(.*)/"; preg_match($moduleRegex, $q, $m); $queryConditions = trim($m[2], ' ;'); if (strtolower(substr($queryConditions, 0, 5)) == 'where') { $queryConditions = substr($queryConditions, 6); } $orderbyCond = "/([oO][rR][dD][eE][rR]\\s+[bB][yY]\\s+)+(.*)/"; preg_match($orderbyCond, $queryConditions, $ob); $obflds = isset($ob[2]) ? $ob[2] : ''; if (stripos($obflds, ' limit ') > 0) { $obflds = substr($obflds, 0, stripos($obflds, ' limit ')); } $limitCond = "/([lL][iI][mM][iI][tT]\\s+)+(.*)/"; preg_match($limitCond, $queryConditions, $lm); $lmoc = isset($lm[2]) ? $lm[2] : ''; if (stripos($lmoc, ' order ') > 0) { $lmoc = substr($lmoc, 0, stripos($lmoc, ' order ')); } if (stripos($queryConditions, ' order ') > 0) { $queryConditions = substr($queryConditions, 0, stripos($queryConditions, ' order ')); } if (stripos($queryConditions, ' limit ') > 0) { $queryConditions = substr($queryConditions, 0, stripos($queryConditions, ' limit ')); } $qcst = strtolower(substr(trim($queryConditions), 0, 5)); if ($qcst == 'order' or $qcst == 'limit') { $queryConditions = ''; } // $queryConditions has all the where conditions // $obflds has the list of order by fields // $limit is the full correct limit SQL part // transform REST ids $relatedCond = "/=\\s*'*\\d+x(\\d+)'*/"; $afterwhere = preg_replace($relatedCond, ' = $1 ', $afterwhere); // where if (strlen($queryConditions) > 0) { $queryGenerator->startGroup(); $qc = trim($queryConditions); if (substr($qc, 0, 1) == '(') { $queryGenerator->startGroup(); $qc = substr($qc, 1); } $inopRegex = "/\\s+in\\s+\\(/"; $posand = stripos($qc, ' and '); $posor = stripos($qc, ' or '); $glue = ''; while ($posand > 0 or $posor > 0 or strlen($qc)) { $endgroup = false; preg_match($inopRegex, $qc, $qcop); $inop = count($qcop) > 0; $lasttwo = ''; if ($inop) { $lasttwo = str_replace(' ', '', $qc); $lasttwo = substr($lasttwo, -2); } if ($posand == 0 and $posor == 0) { if (!$inop and substr($qc, -1) == ')' or $inop and $lasttwo == '))') { $qc = substr($qc, 0, strlen($qc) - 1); $endgroup = true; } __FQNExtendedQueryAddCondition($queryGenerator, $qc, $glue, $mainModule, $fieldcolumn, $user); $qc = ''; } elseif ($posand == 0 or $posand > $posor and $posor != 0) { $qcond = trim(substr($qc, 0, $posor)); if (!$inop and substr($qcond, -1) == ')' or $inop and $lasttwo == '))') { $qcond = substr($qcond, 0, strlen($qcond) - 1); $endgroup = true; } __FQNExtendedQueryAddCondition($queryGenerator, $qcond, $glue, $mainModule, $fieldcolumn, $user); $glue = $queryGenerator::$OR; $qc = trim(substr($qc, $posor + 4)); } else { $qcond = trim(substr($qc, 0, $posand)); if (!$inop and substr($qcond, -1) == ')' or $inop and $lasttwo == '))') { $qcond = substr($qcond, 0, strlen($qcond) - 1); $endgroup = true; } __FQNExtendedQueryAddCondition($queryGenerator, $qcond, $glue, $mainModule, $fieldcolumn, $user); $glue = $queryGenerator::$AND; $qc = trim(substr($qc, $posand + 5)); } if ($endgroup) { $queryGenerator->endGroup(); } if (substr($qc, 0, 1) == '(') { $queryGenerator->startGroup($glue); $glue = ''; $qc = substr($qc, 1); } $posand = stripos($qc, ' and '); $posor = stripos($qc, ' or '); } $queryGenerator->endGroup(); } $query = 'select '; if ($countSelect) { $query .= 'count(*) '; } else { $query .= $queryGenerator->getSelectClauseColumnSQL() . ' '; } $query .= $queryGenerator->getFromClause() . ' '; $query .= $queryGenerator->getWhereClause() . ' '; // limit and order if (!empty($obflds)) { $obflds = trim($obflds); if (strtolower(substr($obflds, -3)) == 'asc') { $dir = ' asc '; $obflds = trim(substr($obflds, 0, strlen($obflds) - 3)); } elseif (strtolower(substr($obflds, -4)) == 'desc') { $dir = ' desc '; $obflds = trim(substr($obflds, 0, strlen($obflds) - 4)); } else { $dir = ''; } $obflds = explode(',', $obflds); foreach ($obflds as $k => $field) { $obflds[$k] = __FQNExtendedQueryField2Column($field, $mainModule, $fieldcolumn, $user); } $query .= ' order by ' . implode(',', $obflds) . $dir . ' '; } if (!empty($lmoc)) { $query .= " limit {$lmoc} "; } return array($query, $queryRelatedModules); }
function getSelectAllQuery($input, $module) { global $adb, $current_user; $viewid = vtlib_purify($input['viewname']); if ($module == "Calendar") { $listquery = getListQuery($module); $oCustomView = new CustomView($module); $query = $oCustomView->getModifiedCvListQuery($viewid, $listquery, $module); $where = ''; if ($input['query'] == 'true') { list($where, $ustring) = explode("#@@#", getWhereCondition($module, $input)); if (isset($where) && $where != '') { $query .= " AND " . $where; } } } else { $queryGenerator = new QueryGenerator($module, $current_user); $queryGenerator->initForCustomViewById($viewid); if ($input['query'] == 'true') { $queryGenerator->addUserSearchConditions($input); } $queryGenerator->setFields(array('id')); $query = $queryGenerator->getQuery(); if ($module == 'Documents') { $folderid = vtlib_purify($input['folderidstring']); $folderid = str_replace(';', ',', $folderid); $query .= " AND vtiger_notes.folderid in (" . $folderid . ")"; } } $result = $adb->pquery($query, array()); return $result; }
public function getExpressionQuery($xml) { global $adb, $current_user; $workflowScheduler = new WorkFlowScheduler($adb); $moduleName = (string) $xml->module; $queryGenerator = new QueryGenerator($moduleName, $current_user); $conditions = (string) $xml->conditions; $conditions = json_decode(decode_html($conditions)); if (isset($xml->fields)) { $fields = explode(',', trim((string) $xml->fields)); $queryGenerator->setFields($fields); } else { $queryGenerator->setFields(array('id')); } $workflowScheduler->addWorkflowConditionsToQueryGenerator($queryGenerator, $conditions); if ($moduleName == 'Calendar' || $moduleName == 'Events') { if ($conditions) { $queryGenerator->addConditionGlue('AND'); } // We should only get the records related to proper activity type if ($moduleName == 'Calendar') { $queryGenerator->addCondition('activitytype', 'Emails', 'n'); $queryGenerator->addCondition('activitytype', 'Task', 'e', 'AND'); } else { if ($moduleName == "Events") { $queryGenerator->addCondition('activitytype', 'Emails', 'n'); $queryGenerator->addCondition('activitytype', 'Task', 'n', 'AND'); } } } $query = $queryGenerator->getQuery(); return $query; }
public function createRecords() { $adb = PearDatabase::getInstance(); $moduleName = $this->module; $focus = CRMEntity::getInstance($moduleName); $moduleHandler = vtws_getModuleHandlerFromName($moduleName, $this->user); $moduleMeta = $moduleHandler->getMeta(); $moduleObjectId = $moduleMeta->getEntityId(); $moduleFields = $moduleMeta->getModuleFields(); $tableName = Import_Utils::getDbTableName($this->user); $sql = 'SELECT * FROM ' . $tableName . ' WHERE status = ' . Import_Data_Controller::$IMPORT_RECORD_NONE; if ($this->batchImport) { $configReader = new ConfigReader('modules/Import/config.inc', 'ImportConfig'); $importBatchLimit = $configReader->getConfig('importBatchLimit'); $sql .= ' LIMIT ' . $importBatchLimit; } $result = $adb->query($sql); $numberOfRecords = $adb->num_rows($result); if ($numberOfRecords <= 0) { return; } $fieldMapping = $this->fieldMapping; $fieldColumnMapping = $moduleMeta->getFieldColumnMapping(); for ($i = 0; $i < $numberOfRecords; ++$i) { $row = $adb->raw_query_result_rowdata($result, $i); $rowId = $row['id']; $entityInfo = null; $fieldData = array(); foreach ($fieldMapping as $fieldName => $index) { $fieldData[$fieldName] = $row[$fieldName]; } $mergeType = $this->mergeType; $createRecord = false; if (method_exists($focus, 'importRecord')) { $entityInfo = $focus->importRecord($this, $fieldData); } else { if (!empty($mergeType) && $mergeType != Import_Utils::$AUTO_MERGE_NONE) { $queryGenerator = new QueryGenerator($moduleName, $this->user); $queryGenerator->initForDefaultCustomView(); $fieldsList = array('id'); $queryGenerator->setFields($fieldsList); $mergeFields = $this->mergeFields; foreach ($mergeFields as $index => $mergeField) { if ($index != 0) { $queryGenerator->addConditionGlue(QueryGenerator::$AND); } $comparisonValue = $fieldData[$mergeField]; $fieldInstance = $moduleFields[$mergeField]; if ($fieldInstance->getFieldDataType() == 'owner') { $userId = getUserId_Ol($comparisonValue); $comparisonValue = getUserFullName($userId); } if ($fieldInstance->getFieldDataType() == 'reference') { if (strpos($comparisonValue, '::::') > 0) { $referenceFileValueComponents = explode('::::', $comparisonValue); } else { $referenceFileValueComponents = explode(':::', $comparisonValue); } if (count($referenceFileValueComponents) > 1) { $comparisonValue = trim($referenceFileValueComponents[1]); } } $queryGenerator->addCondition($mergeField, $comparisonValue, 'e'); } $query = $queryGenerator->getQuery(); $duplicatesResult = $adb->query($query); $noOfDuplicates = $adb->num_rows($duplicatesResult); if ($noOfDuplicates > 0) { if ($mergeType == Import_Utils::$AUTO_MERGE_IGNORE) { $entityInfo['status'] = self::$IMPORT_RECORD_SKIPPED; } elseif ($mergeType == Import_Utils::$AUTO_MERGE_OVERWRITE || $mergeType == Import_Utils::$AUTO_MERGE_MERGEFIELDS) { for ($index = 0; $index < $noOfDuplicates - 1; ++$index) { $duplicateRecordId = $adb->query_result($duplicatesResult, $index, $fieldColumnMapping['id']); $entityId = vtws_getId($moduleObjectId, $duplicateRecordId); vtws_delete($entityId, $this->user); } $baseRecordId = $adb->query_result($duplicatesResult, $noOfDuplicates - 1, $fieldColumnMapping['id']); $baseEntityId = vtws_getId($moduleObjectId, $baseRecordId); if ($mergeType == Import_Utils::$AUTO_MERGE_OVERWRITE) { $fieldData = $this->transformForImport($fieldData, $moduleMeta); $fieldData['id'] = $baseEntityId; $entityInfo = vtws_update($fieldData, $this->user); $entityInfo['status'] = self::$IMPORT_RECORD_UPDATED; //Prepare data for event handler $entityData = array(); $entityData['rowId'] = $rowId; $entityData['tableName'] = $tableName; $entityData['entityInfo'] = $entityInfo; $entityData['fieldData'] = $fieldData; $entityData['moduleName'] = $moduleName; $entityData['user'] = $this->user; cbEventHandler::do_action('corebos.entity.import.overwrite', $entityData); } if ($mergeType == Import_Utils::$AUTO_MERGE_MERGEFIELDS) { $filteredFieldData = array(); $defaultFieldValues = $this->getDefaultFieldValues($moduleMeta); foreach ($fieldData as $fieldName => $fieldValue) { if (!empty($fieldValue)) { $filteredFieldData[$fieldName] = $fieldValue; } } $existingFieldValues = vtws_retrieve($baseEntityId, $this->user); foreach ($existingFieldValues as $fieldName => $fieldValue) { if (empty($fieldValue) && empty($filteredFieldData[$fieldName]) && !empty($defaultFieldValues[$fieldName])) { $filteredFieldData[$fieldName] = $fieldValue; } } $filteredFieldData = $this->transformForImport($filteredFieldData, $moduleMeta, false, true); $filteredFieldData['id'] = $baseEntityId; $entityInfo = vtws_revise($filteredFieldData, $this->user); $entityInfo['status'] = self::$IMPORT_RECORD_MERGED; //Prepare data for event handler $entityData = array(); $entityData['rowId'] = $rowId; $entityData['tableName'] = $tableName; $entityData['entityInfo'] = $entityInfo; $entityData['fieldData'] = $fieldData; $entityData['moduleName'] = $moduleName; $entityData['user'] = $this->user; cbEventHandler::do_action('corebos.entity.import.merge', $entityData); } } else { $createRecord = true; } } else { $createRecord = true; } } else { $createRecord = true; } if ($createRecord) { $fieldData = $this->transformForImport($fieldData, $moduleMeta); if ($fieldData == null) { $entityInfo = null; } else { $entityInfo = vtws_create($moduleName, $fieldData, $this->user); $entityInfo['status'] = self::$IMPORT_RECORD_CREATED; //Prepare data for event handler $entityData = array(); $entityData['rowId'] = $rowId; $entityData['tableName'] = $tableName; $entityData['entityInfo'] = $entityInfo; $entityData['fieldData'] = $fieldData; $entityData['moduleName'] = $moduleName; $entityData['user'] = $this->user; cbEventHandler::do_action('corebos.entity.import.create', $entityData); } } } if ($entityInfo == null) { $entityInfo = array('id' => null, 'status' => self::$IMPORT_RECORD_FAILED); } $this->importedRecordInfo[$rowId] = $entityInfo; $this->updateImportStatus($rowId, $entityInfo); } unset($result); return true; }
/** * Function to display the Search Results * @param Vtiger_Request $request */ function showSearchResults(Vtiger_Request $request) { $db = PearDatabase::getInstance(); $viewer = $this->getViewer($request); $moduleName = $request->getModule(); $advFilterList = $request->get('advfilterlist'); //used to show the save modify filter option $isAdvanceSearch = false; $matchingRecords = array(); if (is_array($advFilterList) && count($advFilterList) > 0) { $isAdvanceSearch = true; $user = Users_Record_Model::getCurrentUserModel(); $queryGenerator = new QueryGenerator($moduleName, $user); $queryGenerator->setFields(array('id')); vimport('~~/modules/CustomView/CustomView.php'); $customView = new CustomView($moduleName); $dateSpecificConditions = $customView->getStdFilterConditions(); foreach ($advFilterList as $groupindex => $groupcolumns) { $filtercolumns = $groupcolumns['columns']; if (count($filtercolumns) > 0) { $queryGenerator->startGroup(''); foreach ($filtercolumns as $index => $filter) { $nameComponents = explode(':', $filter['columnname']); if (empty($nameComponents[2]) && $nameComponents[1] == 'crmid' && $nameComponents[0] == 'vtiger_crmentity') { $name = $queryGenerator->getSQLColumn('id'); } else { $name = $nameComponents[2]; } if (($nameComponents[4] == 'D' || $nameComponents[4] == 'DT') && in_array($filter['comparator'], $dateSpecificConditions)) { $filter['stdfilter'] = $filter['comparator']; $valueComponents = explode(',', $filter['value']); if ($filter['comparator'] == 'custom') { $filter['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]); $filter['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]); } $dateFilterResolvedList = $customView->resolveDateFilterValue($filter); $value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['startdate']); $value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['enddate'], false); $queryGenerator->addCondition($name, $value, 'BETWEEN'); } else { $queryGenerator->addCondition($name, $filter['value'], $filter['comparator']); } $columncondition = $filter['column_condition']; if (!empty($columncondition) && array_key_exists($index + 1, $filtercolumns)) { $queryGenerator->addConditionGlue($columncondition); } } $queryGenerator->endGroup(); $groupConditionGlue = $groupcolumns['condition']; if (!empty($groupConditionGlue)) { $queryGenerator->addConditionGlue($groupConditionGlue); } } } $query = $queryGenerator->getQuery(); //Remove the ordering for now to improve the speed //$query .= ' ORDER BY createdtime DESC'; $result = $db->pquery($query, array()); $rows = $db->num_rows($result); for ($i = 0; $i < $rows; ++$i) { $row = $db->query_result_rowdata($result, $i); $recordInstance = Vtiger_Record_Model::getInstanceById($row[0]); $moduleName = $recordInstance->getModuleName(); $matchingRecords[$moduleName][$row[0]] = $recordInstance; } $viewer->assign('SEARCH_MODULE', $moduleName); } else { $searchKey = $request->get('value'); $searchModule = false; if ($request->get('searchModule')) { $searchModule = $request->get('searchModule'); } $viewer->assign('SEARCH_KEY', $searchKey); $viewer->assign('SEARCH_MODULE', $searchModule); $matchingRecords = Vtiger_Record_Model::getSearchResult($searchKey, $searchModule); } $matchingRecordsList = array(); if ($matchingRecords[$moduleName]) { $matchingRecordsList[$moduleName] = $matchingRecords[$moduleName]; } foreach ($matchingRecords as $module => $recordModelsList) { $matchingRecordsList[$module] = $recordModelsList; } $viewer->assign('MODULE', $moduleName); $viewer->assign('MATCHING_RECORDS', $matchingRecordsList); $viewer->assign('IS_ADVANCE_SEARCH', $isAdvanceSearch); echo $viewer->view('UnifiedSearchResults.tpl', '', true); }
/** * Function to get Relation query * @return <String> */ public function getRelationQuery() { $relationModel = $this->getRelationModel(); if (!empty($relationModel) && $relationModel->get('name') != NULL) { $recordModel = $this->getParentRecordModel(); $query = $relationModel->getQuery($recordModel, false, $this); return $query; } $searchParams = $this->get('search_params'); if (empty($searchParams)) { $searchParams = array(); } $relatedModuleModel = $this->getRelatedModuleModel(); $relatedModuleName = $relatedModuleModel->getName(); $relatedModuleBaseTable = $relatedModuleModel->basetable; $relatedModuleEntityIdField = $relatedModuleModel->basetableid; $parentModuleModel = $relationModel->getParentModuleModel(); $parentModuleBaseTable = $parentModuleModel->basetable; $parentModuleEntityIdField = $parentModuleModel->basetableid; $parentRecordId = $this->getParentRecordModel()->getId(); $parentModuleDirectRelatedField = $parentModuleModel->get('directRelatedFieldName'); $relatedModuleFields = array_keys($this->getHeaders()); $currentUserModel = Users_Record_Model::getCurrentUserModel(); $queryGenerator = new QueryGenerator($relatedModuleName, $currentUserModel); $queryGenerator->setFields($relatedModuleFields); if (count($searchParams) > 0) { $queryGenerator->parseAdvFilterList($searchParams); } $joinQuery = ' INNER JOIN ' . $parentModuleBaseTable . ' ON ' . $parentModuleBaseTable . '.' . $parentModuleDirectRelatedField . " = " . $relatedModuleBaseTable . '.' . $relatedModuleEntityIdField; $query = $queryGenerator->getQuery(); $queryComponents = spliti(' FROM ', $query); foreach ($queryComponents as $key => $val) { if ($key == 0) { $query = $queryComponents[0] . ' ,vtiger_crmentity.crmid'; } else { $query .= ' FROM ' . $val; } } $whereSplitQueryComponents = spliti(' WHERE ', $query); $query = $whereSplitQueryComponents[0] . $joinQuery; foreach ($whereSplitQueryComponents as $key => $val) { if ($key == 0) { $query .= " WHERE {$parentModuleBaseTable}.{$parentModuleEntityIdField} = {$parentRecordId} AND "; } else { $query .= $val . ' WHERE '; } } $query = trim($query, "WHERE "); return $query; }
/** * Function to get relation query for particular module with function name * @param <record> $recordId * @param <String> $functionName * @param Vtiger_Module_Model $relatedModule * @return <String> */ public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false) { $relatedModuleName = $relatedModule->getName(); $focus = CRMEntity::getInstance($this->getName()); $focus->id = $recordId; $result = $focus->{$functionName}($recordId, $this->getId(), $relatedModule->getId()); $query = $result['query'] . ' ' . $this->getSpecificRelationQuery($relatedModuleName); //modify query if any module has summary fields, those fields we are displayed in related list of that module $relatedListFields = array(); if ($relationModel) { $relatedListFields = $relationModel->getRelationFields(true, true); } if (count($relatedListFields) == 0) { $relatedListFields = $relatedModule->getConfigureRelatedListFields(); if ($relatedModuleName == 'Documents') { $relatedListFields['filelocationtype'] = 'filelocationtype'; $relatedListFields['filestatus'] = 'filestatus'; } } if (count($relatedListFields) > 0) { $currentUser = Users_Record_Model::getCurrentUserModel(); $queryGenerator = new QueryGenerator($relatedModuleName, $currentUser); $queryGenerator->setFields($relatedListFields); $selectColumnSql = $queryGenerator->getSelectClauseColumnSQL(); $newQuery = spliti('FROM', $query); $selectColumnSql = 'SELECT DISTINCT vtiger_crmentity.crmid,' . $selectColumnSql; $query = $selectColumnSql . ' FROM ' . $newQuery[1]; } $instance = CRMEntity::getInstance($relatedModuleName); $securityParameter = $instance->getUserAccessConditionsQuerySR($relatedModuleName, false, $recordId); if ($securityParameter != '') { $query .= $securityParameter; } return $query; }
public function createRecords() { $adb = PearDatabase::getInstance(); $moduleName = $this->module; $focus = CRMEntity::getInstance($moduleName); $moduleHandler = vtws_getModuleHandlerFromName($moduleName, $this->user); $moduleMeta = $moduleHandler->getMeta(); $moduleObjectId = $moduleMeta->getEntityId(); $moduleFields = $moduleMeta->getModuleFields(); $entityData = array(); $tableName = Import_Utils_Helper::getDbTableName($this->user); $sql = 'SELECT * FROM ' . $tableName . ' WHERE status = ' . Import_Data_Action::$IMPORT_RECORD_NONE; if ($this->batchImport) { $configReader = new Import_Config_Model(); $importBatchLimit = $configReader->get('importBatchLimit'); $sql .= ' LIMIT ' . $importBatchLimit; } $result = $adb->query($sql); $numberOfRecords = $adb->num_rows($result); if ($numberOfRecords <= 0) { return; } $fieldMapping = $this->fieldMapping; $fieldColumnMapping = $moduleMeta->getFieldColumnMapping(); for ($i = 0; $i < $numberOfRecords; ++$i) { $row = $adb->raw_query_result_rowdata($result, $i); $rowId = $row['id']; $entityInfo = null; $fieldData = array(); foreach ($fieldMapping as $fieldName => $index) { $fieldData[$fieldName] = $row[$fieldName]; } $mergeType = $this->mergeType; $createRecord = false; if (method_exists($focus, 'importRecord')) { $entityInfo = $focus->importRecord($this, $fieldData); } else { if (!empty($mergeType) && $mergeType != Import_Utils_Helper::$AUTO_MERGE_NONE) { $queryGenerator = new QueryGenerator($moduleName, $this->user); $customView = new CustomView($moduleName); $viewId = $customView->getViewIdByName('All', $moduleName); if (!empty($viewId)) { $queryGenerator->initForCustomViewById($viewId); } else { $queryGenerator->initForDefaultCustomView(); } $fieldsList = array('id'); $queryGenerator->setFields($fieldsList); $mergeFields = $this->mergeFields; if ($queryGenerator->getWhereFields() && $mergeFields) { $queryGenerator->addConditionGlue(QueryGenerator::$AND); } foreach ($mergeFields as $index => $mergeField) { if ($index != 0) { $queryGenerator->addConditionGlue(QueryGenerator::$AND); } $comparisonValue = $fieldData[$mergeField]; $fieldInstance = $moduleFields[$mergeField]; if ($fieldInstance->getFieldDataType() == 'owner') { $userId = getUserId_Ol($comparisonValue); $comparisonValue = getUserFullName($userId); } if ($fieldInstance->getFieldDataType() == 'reference') { if (strpos($comparisonValue, '::::') > 0) { $referenceFileValueComponents = explode('::::', $comparisonValue); } else { $referenceFileValueComponents = explode(':::', $comparisonValue); } if (count($referenceFileValueComponents) > 1) { $comparisonValue = trim($referenceFileValueComponents[1]); } } $queryGenerator->addCondition($mergeField, $comparisonValue, 'e', '', '', '', true); } $query = $queryGenerator->getQuery(); $duplicatesResult = $adb->query($query); $noOfDuplicates = $adb->num_rows($duplicatesResult); if ($noOfDuplicates > 0) { if ($mergeType == Import_Utils_Helper::$AUTO_MERGE_IGNORE) { $entityInfo['status'] = self::$IMPORT_RECORD_SKIPPED; } elseif ($mergeType == Import_Utils_Helper::$AUTO_MERGE_OVERWRITE || $mergeType == Import_Utils_Helper::$AUTO_MERGE_MERGEFIELDS) { for ($index = 0; $index < $noOfDuplicates - 1; ++$index) { $duplicateRecordId = $adb->query_result($duplicatesResult, $index, $fieldColumnMapping['id']); $entityId = vtws_getId($moduleObjectId, $duplicateRecordId); vtws_delete($entityId, $this->user); } $baseRecordId = $adb->query_result($duplicatesResult, $noOfDuplicates - 1, $fieldColumnMapping['id']); $baseEntityId = vtws_getId($moduleObjectId, $baseRecordId); if ($mergeType == Import_Utils_Helper::$AUTO_MERGE_OVERWRITE) { $fieldData = $this->transformForImport($fieldData, $moduleMeta); $fieldData['id'] = $baseEntityId; $entityInfo = vtws_update($fieldData, $this->user); $entityInfo['status'] = self::$IMPORT_RECORD_UPDATED; } if ($mergeType == Import_Utils_Helper::$AUTO_MERGE_MERGEFIELDS) { $filteredFieldData = array(); foreach ($fieldData as $fieldName => $fieldValue) { // empty will give false for value = 0 if (!empty($fieldValue) || $fieldValue != "") { $filteredFieldData[$fieldName] = $fieldValue; } } // Custom handling for default values & mandatory fields // need to be taken care than normal import as we merge // existing record values with newer values. $fillDefault = false; $mandatoryValueChecks = false; $existingFieldValues = vtws_retrieve($baseEntityId, $this->user); $defaultFieldValues = $this->getDefaultFieldValues($moduleMeta); foreach ($existingFieldValues as $fieldName => $fieldValue) { if (empty($fieldValue) && empty($filteredFieldData[$fieldName]) && !empty($defaultFieldValues[$fieldName])) { $filteredFieldData[$fieldName] = $defaultFieldValues[$fieldName]; } } $filteredFieldData = $this->transformForImport($filteredFieldData, $moduleMeta, $fillDefault, $mandatoryValueChecks); $filteredFieldData['id'] = $baseEntityId; $entityInfo = vtws_revise($filteredFieldData, $this->user); $entityInfo['status'] = self::$IMPORT_RECORD_MERGED; $fieldData = $filteredFieldData; } } else { $createRecord = true; } } else { $createRecord = true; } } else { $createRecord = true; } if ($createRecord) { $fieldData = $this->transformForImport($fieldData, $moduleMeta); if ($fieldData == null) { $entityInfo = null; } else { try { $entityInfo = vtws_create($moduleName, $fieldData, $this->user); } catch (Exception $e) { } } } } if ($entityInfo == null) { $entityInfo = array('id' => null, 'status' => self::$IMPORT_RECORD_FAILED); } else { if ($createRecord) { $entityInfo['status'] = self::$IMPORT_RECORD_CREATED; } } if ($createRecord || $mergeType == Import_Utils_Helper::$AUTO_MERGE_MERGEFIELDS || $mergeType == Import_Utils_Helper::$AUTO_MERGE_OVERWRITE) { $entityIdComponents = vtws_getIdComponents($entityInfo['id']); $recordId = $entityIdComponents[1]; $entityfields = getEntityFieldNames($this->module); switch ($this->module) { case 'HelpDesk': $entityfields['fieldname'] = array('ticket_title'); break; case 'Documents': $entityfields['fieldname'] = array('notes_title'); break; case 'Documents': $entityfields['fieldname'] = array('notes_title'); break; } $label = ''; if (is_array($entityfields['fieldname'])) { foreach ($entityfields['fieldname'] as $field) { $label .= $fieldData[$field] . " "; } } else { $label = $fieldData[$entityfields['fieldname']]; } $label = trim($label); $adb->pquery('UPDATE vtiger_crmentity SET label=? WHERE crmid=?', array($label, $recordId)); } $this->importedRecordInfo[$rowId] = $entityInfo; $this->updateImportStatus($rowId, $entityInfo); } if ($this->entityData) { $entity = new VTEventsManager($adb); $entity->triggerEvent('vtiger.batchevent.save', $this->entityData); } $this->entityData = null; $result = null; return true; }
/** * Function to search emails for send email * @param <String> $searchValue * @return <Array> Result of searched emails */ public function searchEmails($searchValue) { $emailsResult = array(); $db = PearDatabase::getInstance(); $currentUserModel = Users_Record_Model::getCurrentUserModel(); $emailSupportedModulesList = $this->getEmailRelatedModules(); foreach ($emailSupportedModulesList as $moduleName) { $searchFields = array(); $moduleModel = Vtiger_Module_Model::getInstance($moduleName); $emailFieldModels = $moduleModel->getFieldsByType('email'); foreach ($emailFieldModels as $fieldName => $fieldModel) { if ($fieldModel->isViewable()) { $searchFields[] = $fieldName; } } $emailFields = $searchFields; $nameFields = $moduleModel->getNameFields(); foreach ($nameFields as $fieldName) { $fieldModel = Vtiger_Field_Model::getInstance($fieldName, $moduleModel); if ($fieldModel->isViewable()) { $searchFields[] = $fieldName; } } if ($emailFields) { $moduleInstance = CRMEntity::getInstance($moduleName); $queryGenerator = new QueryGenerator($moduleName, $currentUserModel); $listFields = $searchFields; $listFields[] = 'id'; $queryGenerator->setFields($listFields); //Opensource fix for showing up deleted records on email search $queryGenerator->startGroup(""); foreach ($searchFields as $key => $emailField) { $queryGenerator->addCondition($emailField, trim($searchValue), 'c', 'OR'); } $queryGenerator->endGroup(); $result = $db->pquery($queryGenerator->getQuery(), array()); $numOfRows = $db->num_rows($result); for ($i = 0; $i < $numOfRows; $i++) { $row = $db->query_result_rowdata($result, $i); foreach ($emailFields as $emailField) { $emailFieldValue = $row[$emailField]; if ($emailFieldValue) { $recordLabel = getEntityFieldNameDisplay($moduleName, $nameFields, $row); if (strpos($emailFieldValue, $searchValue) !== false || strpos($recordLabel, $searchValue) !== false) { $emailsResult[vtranslate($moduleName, $moduleName)][$row[$moduleInstance->table_index]][] = array('value' => $emailFieldValue, 'label' => $recordLabel . ' <b>(' . $emailFieldValue . ')</b>'); } } } } } } return $emailsResult; }
public function getUsersAndGroupForModuleList($module, $view) { $currentUser = Users_Record_Model::getCurrentUserModel(); $db = PearDatabase::getInstance(); $userEntityInfo = Vtiger_Functions::getEntityModuleInfo('Users'); $table = $userEntityInfo['tablename']; $columnsName = explode(',', $userEntityInfo['fieldname']); $queryGenerator = new QueryGenerator($module, $currentUser); $queryGenerator->initForCustomViewById($view); $queryGenerator->setFields(['assigned_user_id']); $queryGenerator->addCustomColumn('vtiger_groups.groupname'); foreach ($columnsName as &$column) { $queryGenerator->addCustomColumn($table . '.' . $column); } $listQuery = $queryGenerator->getQuery('SELECT DISTINCT'); $listQuery .= ' ORDER BY last_name ASC, first_name ASC'; $result = $db->query($listQuery); $users = $group = []; while ($row = $db->fetch_array($result)) { if (isset($row['groupname'])) { $group[$row['smownerid']] = $row['groupname']; } else { $name = ''; foreach ($columnsName as &$column) { $name .= $row[$column] . ' '; } $users[$row['smownerid']] = trim($name); } } return ['users' => $users, 'group' => $group]; }
echo "{$query}<br>"; testquery($query); echo "<h2>Query for modules with relation on themselves</h2>"; $queryGenerator = new QueryGenerator('Accounts', $current_user); $queryGenerator->setFields(array('id', 'accountname', 'website', 'Accounts.accountname', 'Accounts.website')); $query = $queryGenerator->getQuery(); echo "{$query}<br>"; testquery($query); $queryGenerator = new QueryGenerator('Contacts', $current_user); $queryGenerator->setFields(array('id', 'firstname', 'lastname', 'Contacts.firstname', 'Contacts.lastname')); $query = $queryGenerator->getQuery(); echo "{$query}<br>"; testquery($query); echo "<h2>Query for HelpDesk</h2>"; $queryGenerator = new QueryGenerator('HelpDesk', $current_user); $queryGenerator->setFields(array('id', 'ticket_title', 'ticketstatus')); $query = $queryGenerator->getQuery(); echo "{$query}<br>"; testquery($query); $queryGenerator = new QueryGenerator('HelpDesk', $current_user); $queryGenerator->setFields(array('id', 'ticket_title', 'ticketstatus', 'Accounts.accountname', 'Contacts.firstname')); $query = $queryGenerator->getQuery(); echo "{$query}<br>"; testquery($query); //error_reporting(E_ALL);ini_set("display_errors", "on"); // echo "<h2>Query with custom field</h2>"; // $queryGenerator = new QueryGenerator($moduleName, $current_user); // $queryGenerator->setFields(array('id','cf_681')); // $query = $queryGenerator->getQuery(); // echo "$query<br>"; // testquery($query);
public function getUsersAndGroupForModuleList($module, $view) { $currentUser = Users_Record_Model::getCurrentUserModel(); $db = PearDatabase::getInstance(); $queryGenerator = new QueryGenerator($module, $currentUser); $queryGenerator->initForCustomViewById($view); $queryGenerator->setFields(['assigned_user_id']); $queryGenerator->addCustomColumn('vtiger_users.first_name'); $queryGenerator->addCustomColumn('vtiger_users.last_name'); $queryGenerator->addCustomColumn('vtiger_groups.groupname'); $listQuery = $queryGenerator->getQuery('SELECT DISTINCT'); $result = $db->query($listQuery); $users = $group = []; while ($row = $db->fetch_array($result)) { if (isset($row['groupname'])) { $group[$row['smownerid']] = $row['groupname']; } else { $users[$row['smownerid']] = $row['last_name'] . ' ' . $row['first_name']; } } return ['users' => $users, 'group' => $group]; }
/** * Function to get Relation query * @return <String> */ public function getRelationQuery() { $relationModel = $this->getRelationModel(); if(!empty($relationModel) && $relationModel->get('name') != NULL){ $recordModel = $this->getParentRecordModel(); $query = $relationModel->getQuery($recordModel); return $query; } $relatedModuleModel = $this->getRelatedModuleModel(); $relatedModuleName = $relatedModuleModel->getName(); $relatedModuleBaseTable = $relatedModuleModel->basetable; $relatedModuleEntityIdField = $relatedModuleModel->basetableid; $parentModuleModel = $relationModel->getParentModuleModel(); $parentModuleBaseTable = $parentModuleModel->basetable; $parentModuleEntityIdField = $parentModuleModel->basetableid; $parentRecordId = $this->getParentRecordModel()->getId(); $parentModuleDirectRelatedField = $parentModuleModel->get('directRelatedFieldName'); $relatedModuleFields = array_keys($this->getHeaders()); $currentUserModel = Users_Record_Model::getCurrentUserModel(); $queryGenerator = new QueryGenerator($relatedModuleName, $currentUserModel); $queryGenerator->setFields($relatedModuleFields); $query = $queryGenerator->getQuery(); $queryComponents = spliti(' FROM ', $query); $query = $queryComponents[0].' ,vtiger_crmentity.crmid FROM '.$queryComponents[1]; $whereSplitQueryComponents = spliti(' WHERE ', $query); $joinQuery = ' INNER JOIN '.$parentModuleBaseTable.' ON '.$parentModuleBaseTable.'.'.$parentModuleDirectRelatedField." = ".$relatedModuleBaseTable.'.'.$relatedModuleEntityIdField; $query = "$whereSplitQueryComponents[0] $joinQuery WHERE $parentModuleBaseTable.$parentModuleEntityIdField = $parentRecordId AND $whereSplitQueryComponents[1]"; return $query; }