Adding default sorting functionality in Custom Filters in Vtiger

Working with vTiger Since 10+ year I did lot of implementation and changes for many customers and prospects.

Recently for one of my client I implement customization of adding Sort field in Custom filter to add more flexibility to user to display listing with default sort set by user.

This implementation helps user of CRM to quickly see record listing based on sorting set by the them.

Vtiger provides a convenient tool for creating custom filters. Each user can customize the display of a list of modules. Select the desired columns in the table, filter the entries. But you cannot configure default sorting.

Below I will show what changes should be made to the code so that you have such an opportunity.

First, we need to make changes to the database, where we will store the default sort data:

 

alter table vtiger_customview

add sortingfield varchar(200) null;

 

alter table vtiger_customview

add sortingdirect varchar(10) null;

 

By this query we are adding two new fields for vtiger_customview: sortingfield and sortingdirect.

Then we should change file modules/CustomView/views/EditAjax.php, process function.

We should add this code after this block of code (str 107):

 

foreach ($customViewSharedMembers as $memberGroupLabel => $membersList) {

if(count($membersList) > 0){

$listShared = true;

break;

}

}

 

add this:

 

$sortField = $customViewModel->get('sortingfield');

$sortDirect = $customViewModel->get('sortingdirect');

$viewer->assign('SORTING_FIELD',$sortField);

$viewer->assign('SORTING_DIRECT',$sortDirect);

 

Then in file layouts/v7/modules/CustomView/EditView.tpl we should add template for sorting block:

After str 119:

<div>

<label class="filterHeaders">{vtranslate('LBL_CHOOSE_FILTER_CONDITIONS', $MODULE)} :</label>

<div class="filterElements well filterConditionContainer filterConditionsDiv">

{include file='AdvanceFilter.tpl'|@vtemplate_path}

</div>

</div>

 

add this code:

 

<div class="marginBottom10px">

<label class="filterHeaders">Sorting Conditions</label>

<div class="filterElements filterConditionContainer filterConditionsDiv">

<div class="row">

<div class="col-lg-6">

<select class="select2 col-lg-6" name="sortingfield">

<option value="none">{vtranslate('LBL_SELECT_FIELD',$MODULE)}</option>

{foreach key=BLOCK_LABEL item=BLOCK_FIELDS from=$RECORD_STRUCTURE}

<optgroup label='{vtranslate($BLOCK_LABEL, $SOURCE_MODULE)}'>

{foreach key=FIELD_NAME item=FIELD_MODEL from=$BLOCK_FIELDS}

{assign var=FIELD_INFO value=$FIELD_MODEL->getFieldInfo()}

{assign var=MODULE_MODEL value=$FIELD_MODEL->getModule()}

{assign var="SPECIAL_VALIDATOR" value=$FIELD_MODEL->getValidator()}

{if !empty($COLUMNNAME_API)}

{assign var=columnNameApi value=$COLUMNNAME_API}

{else}

{assign var=columnNameApi value=getCustomViewColumnName}

{/if}

<option value="{$FIELD_MODEL->$columnNameApi()}" data-fieldtype="{$FIELD_MODEL->getFieldType()}" data-field-name="{$FIELD_NAME}"

{if decode_html($FIELD_MODEL->$columnNameApi()) eq decode_html($SORTING_FIELD)}

{assign var=FIELD_TYPE value=$FIELD_MODEL->getFieldType()}

{assign var=SELECTED_FIELD_MODEL value=$FIELD_MODEL}

{if $FIELD_MODEL->getFieldDataType() == 'reference'  ||  $FIELD_MODEL->getFieldDataType() == 'multireference'}

{$FIELD_TYPE='V'}

{/if}

{$FIELD_INFO['value'] = decode_html($SORTING_FIELD)}

selected="selected"

{/if}

{if ($MODULE_MODEL->get('name') eq 'Calendar' || $MODULE_MODEL->get('name') eq 'Events') && ($FIELD_NAME eq 'recurringtype')}

{assign var=PICKLIST_VALUES value = Calendar_Field_Model::getReccurencePicklistValues()}

{$FIELD_INFO['picklistvalues'] = $PICKLIST_VALUES}

{/if}

{if ($MODULE_MODEL->get('name') eq 'Calendar') && ($FIELD_NAME eq 'activitytype')}

{$FIELD_INFO['picklistvalues']['Task'] = vtranslate('Task', 'Calendar')}

{/if}

{if $FIELD_MODEL->getFieldDataType() eq 'reference'}

{assign var=referenceList value=$FIELD_MODEL->getWebserviceFieldObject()->getReferenceList()}

{if is_array($referenceList) && in_array('Users', $referenceList)}

{assign var=USERSLIST value=array()}

{assign var=CURRENT_USER_MODEL value = Users_Record_Model::getCurrentUserModel()}

{assign var=ACCESSIBLE_USERS value = $CURRENT_USER_MODEL->getAccessibleUsers()}

{foreach item=USER_NAME from=$ACCESSIBLE_USERS}

{$USERSLIST[$USER_NAME] = $USER_NAME}

{/foreach}

{$FIELD_INFO['picklistvalues'] = $USERSLIST}

{$FIELD_INFO['type'] = 'picklist'}

{/if}

{/if}

data-fieldinfo='{Vtiger_Util_Helper::toSafeHTML(ZEND_JSON::encode($FIELD_INFO))}'

{if !empty($SPECIAL_VALIDATOR)}data-validator='{Zend_Json::encode($SPECIAL_VALIDATOR)}'{/if}>

{if $SOURCE_MODULE neq $MODULE_MODEL->get('name')}

({vtranslate($MODULE_MODEL->get('name'), $MODULE_MODEL->get('name'))}) {vtranslate($FIELD_MODEL->get('label'), $MODULE_MODEL->get('name'))}

{else}

{vtranslate($FIELD_MODEL->get('label'), $SOURCE_MODULE)}

{/if}

</option>

{/foreach}

</optgroup>

{/foreach}

{* Required to display event fields also while adding conditions *}

{foreach key=BLOCK_LABEL item=BLOCK_FIELDS from=$EVENT_RECORD_STRUCTURE}

<optgroup label='{vtranslate($BLOCK_LABEL, 'Events')}'>

{foreach key=FIELD_NAME item=FIELD_MODEL from=$BLOCK_FIELDS}

{assign var=FIELD_INFO value=$FIELD_MODEL->getFieldInfo()}

{assign var=MODULE_MODEL value=$FIELD_MODEL->getModule()}

{if !empty($COLUMNNAME_API)}

{assign var=columnNameApi value=$COLUMNNAME_API}

{else}

{assign var=columnNameApi value=getCustomViewColumnName}

{/if}

<option value="{$FIELD_MODEL->$columnNameApi()}" data-fieldtype="{$FIELD_MODEL->getFieldType()}" data-field-name="{$FIELD_NAME}"

{if decode_html($FIELD_MODEL->$columnNameApi()) eq $SORTING_FIELD}

{assign var=FIELD_TYPE value=$FIELD_MODEL->getFieldType()}

{assign var=SELECTED_FIELD_MODEL value=$FIELD_MODEL}

{if $FIELD_MODEL->getFieldDataType() == 'reference' || $FIELD_MODEL->getFieldDataType() == 'multireference'}

{$FIELD_TYPE='V'}

{/if}

{$FIELD_INFO['value'] = decode_html($SORTING_FIELD)}

selected="selected"

{/if}

{if ($MODULE_MODEL->get('name') eq 'Calendar' || $MODULE_MODEL->get('name') eq 'Events') && ($FIELD_NAME eq 'recurringtype')}

{assign var=PICKLIST_VALUES value = Calendar_Field_Model::getReccurencePicklistValues()}

{$FIELD_INFO['picklistvalues'] = $PICKLIST_VALUES}

{/if}

{if $FIELD_MODEL->getFieldDataType() eq 'reference'}

{assign var=referenceList value=$FIELD_MODEL->getWebserviceFieldObject()->getReferenceList()}

{if is_array($referenceList) && in_array('Users', $referenceList)}

{assign var=USERSLIST value=array()}

{assign var=CURRENT_USER_MODEL value = Users_Record_Model::getCurrentUserModel()}

{assign var=ACCESSIBLE_USERS value = $CURRENT_USER_MODEL->getAccessibleUsers()}

{foreach item=USER_NAME from=$ACCESSIBLE_USERS}

{$USERSLIST[$USER_NAME] = $USER_NAME}

{/foreach}

{$FIELD_INFO['picklistvalues'] = $USERSLIST}

{$FIELD_INFO['type'] = 'picklist'}

{/if}

{/if}

data-fieldinfo='{Vtiger_Util_Helper::toSafeHTML(ZEND_JSON::encode($FIELD_INFO))}' >

{if $SOURCE_MODULE neq $MODULE_MODEL->get('name')}

({vtranslate($MODULE_MODEL->get('name'), $MODULE_MODEL->get('name'))})  {vtranslate($FIELD_MODEL->get('label'), $MODULE_MODEL->get('name'))}

{else}

{vtranslate($FIELD_MODEL->get('label'), $SOURCE_MODULE)}

{/if}

</option>

{/foreach}

</optgroup>

{/foreach}

</select>

</div>

<div class="col-lg-6">

<select class="select2 col-lg-6" name="sortingdirect">

<option value="Asc" {if $SORTING_DIRECT eq 'Asc'}selected="true"{/if}>Asc</option>

<option value="Desc" {if $SORTING_DIRECT eq 'Desc'}selected="true"{/if}>Desc</option>

</select>

</div>

</div>

</div>

</div>

 

Then we should add functionality for inserting data in DB.

File modules/CustomView/actions/Save.php near str 80 before:

 

return $customViewModel->setData($customViewData);

 

Add this:

        $sortingfield = $request->get('sortingfield');

        if(!empty($sortingfield)) {

            $customViewData['sortingfield'] = $sortingfield;

        }

        $sortingdirect = $request->get('sortingdirect');

        if(!empty($sortingdirect)) {

            $customViewData['sortingdirect'] = $sortingdirect;

        }

 

And of course, we will need to correct requests for adding sorting to the database. File modules/CustomView/models/Record.php, function save.

Near str 260, after

$status = $this->get('status');

add this one:

$sortingfield = $this->get('sortingfield');

$sortingdirect = $this->get('sortingdirect');

Then near str 272 change query to following:

$sql = 'INSERT INTO vtiger_customview(cvid, viewname, setdefault, setmetrics, entitytype, status, userid, sortingfield, sortingdirect) VALUES (?,?,?,?,?,?,?,?,?)';

$params = array($cvId, $viewName, $setDefault, $setMetrics, $moduleName, $status, $currentUserModel->getId(), $sortingfield, $sortingdirect);

 

A similar trick should be done near line 280, changing the request to update the record:

 

$sql = 'UPDATE vtiger_customview SET viewname=?, setdefault=?, setmetrics=?, status=?, sortingfield=?, sortingdirect=? WHERE cvid=?';

$params = array($viewName, $setDefault, $setMetrics, $status, $sortingfield, $sortingdirect, $cvId);

 

Next, you can proceed to the direct sorting of the table.

First, create the necessary methods to get the sort. Add them to the modules/CustomView/CustomView.php

 

function getSortFieldByCvid($cvid)

{

$cv = $this->getCustomViewByCvid($cvid);

if (!empty($cv) && isset($cv['sortingfield']) && $cv['sortingfield']) {

return $cv['sortingfield'];

} else {

return false;

}

}

 

function getSortDirectByCvid($cvid)

{

$cv = $this->getCustomViewByCvid($cvid);

if (!empty($cv) && isset($cv['sortingdirect']) && $cv['sortingdirect']) {

return $cv['sortingdirect'];

} else {

return false;

}

}

 

To make them work, you need to make changes to the getCustomViewByCvid function in the same file, around line 181. Two lines need to be added:

 

$customviewlist["sortingfield"] = $cvrow["sortingfield"];

$customviewlist["sortingdirect"] = $cvrow["sortingdirect"];

 

For successful sorting, you need to make changes to the modules/Vtiger/models/ListView.php file

First, create new methods by entering them into this file.

 

protected function generateDefaultOrderBy($moduleName, $pagingModel)

    {

        $viewid = ListViewSession::getCurrentView($moduleName);

        if(empty($viewid)) {

            $viewid = $pagingModel->get('viewid');

        }

        $customView = new CustomView($moduleName);

        $sortfield = $customView->getSortFieldByCvid($viewid);

        $sortdirect = $customView->getSortDirectByCvid($viewid);

        if ($sortfield) {

            $orderBy = $this->parseFieldName($sortfield);

            $sortOrder = strtoupper($sortdirect);

            return ' ORDER BY ' . $orderBy . ' ' . $sortOrder;

        } else {

            return ' ORDER BY vtiger_crmentity.modifiedtime DESC';

        }

    }

 

    protected function parseFieldName($field)

    {

        $fieldArr = explode(':', $field);

        return $fieldArr[0] . '.' . $fieldArr[1];

    }

 

 And change the default sorting behavior by changing the getListViewEntries function around row 249

 You need to change line

$listQuery .= ' ORDER BY vtiger_crmentity.modifiedtime DESC';

 to following:

 $listQuery .= $this->generateDefaultOrderBy($moduleName, $pagingModel);

After all these manipulations, in the filter settings you should see a working section on custom data sorting in the table.