[Summary view] [Print] [Text view]

   1  <?php
   2  /*+********************************************************************************
   3   * The contents of this file are subject to the vtiger CRM Public License Version 1.0
   4   * ("License"); You may not use this file except in compliance with the License
   5   * The Original Code is:  vtiger CRM Open Source
   6   * The Initial Developer of the Original Code is vtiger.
   7   * Portions created by vtiger are Copyright (C) vtiger.
   8   * All Rights Reserved.
   9   *********************************************************************************/
  10  
  11  require_once  'include/utils/utils.php';
  12  require_once  'modules/PickList/PickListUtils.php';
  13  
  14  class Vtiger_DependencyPicklist {
  15  
  16  	static function getDependentPicklistFields($module='') {
  17          global $adb;
  18  
  19          if(empty($module)) {
  20              $result = $adb->pquery('SELECT DISTINCT sourcefield, targetfield, tabid FROM vtiger_picklist_dependency', array());
  21          } else {
  22              $tabId = getTabid($module);
  23              $result = $adb->pquery('SELECT DISTINCT sourcefield, targetfield, tabid FROM vtiger_picklist_dependency WHERE tabid=?', array($tabId));
  24          }
  25          $noofrows = $adb->num_rows($result);
  26  
  27          $dependentPicklists = array();
  28          if($noofrows > 0) {
  29              $fieldlist = array();
  30              for($i=0; $i<$noofrows; ++$i) {
  31                  $fieldTabId = $adb->query_result($result,$i,'tabid');
  32                  $sourceField = $adb->query_result($result,$i,'sourcefield');
  33                  $targetField = $adb->query_result($result,$i,'targetfield');
  34  
  35                  if(getFieldid($fieldTabId, $sourceField) == false || getFieldid($fieldTabId, $targetField) == false) {
  36                      continue;
  37                  }
  38  
  39                  $fieldResult = $adb->pquery('SELECT fieldlabel FROM vtiger_field WHERE fieldname = ?', array($sourceField));
  40                  $sourceFieldLabel = $adb->query_result($fieldResult,0,'fieldlabel');
  41  
  42                  $fieldResult = $adb->pquery('SELECT fieldlabel FROM vtiger_field WHERE fieldname = ?', array($targetField));
  43                  $targetFieldLabel = $adb->query_result($fieldResult,0,'fieldlabel');
  44  
  45                  $dependentPicklists[] = array('sourcefield'=>$sourceField, 'sourcefieldlabel'=>$sourceFieldLabel,
  46                          'targetfield'=>$targetField, 'targetfieldlabel'=>$targetFieldLabel,
  47                          'module'=>getTabModuleName($fieldTabId));
  48              }
  49          }
  50          return $dependentPicklists;
  51      }
  52  
  53  	static function getAvailablePicklists($module) {
  54          global $adb, $log;
  55          $tabId = getTabid($module);
  56  
  57          $query="select vtiger_field.fieldlabel,vtiger_field.fieldname" .
  58                  " FROM vtiger_field inner join vtiger_picklist on vtiger_field.fieldname = vtiger_picklist.name" .
  59                  " where displaytype=1 and vtiger_field.tabid=? and vtiger_field.uitype in ('15','16') " .
  60                  " and vtiger_field.presence in (0,2) ORDER BY vtiger_picklist.picklistid ASC";
  61  
  62          $result = $adb->pquery($query, array($tabId));
  63          $noofrows = $adb->num_rows($result);
  64  
  65          $fieldlist = array();
  66          if($noofrows > 0) {
  67              for($i=0; $i<$noofrows; ++$i) {
  68                  $fieldlist[$adb->query_result($result,$i,"fieldname")] = $adb->query_result($result,$i,"fieldlabel");
  69              }
  70          }
  71          return $fieldlist;
  72      }
  73  
  74  	static function savePickListDependencies($module, $dependencyMap) {
  75          global $adb;
  76          $tabId = getTabid($module);
  77          $sourceField = $dependencyMap['sourcefield'];
  78          $targetField = $dependencyMap['targetfield'];
  79  
  80          $valueMapping = $dependencyMap['valuemapping'];
  81          for($i=0; $i<count($valueMapping); ++$i) {
  82              $mapping = $valueMapping[$i];
  83              $sourceValue = $mapping['sourcevalue'];
  84              $targetValues = $mapping['targetvalues'];
  85              $serializedTargetValues = Zend_Json::encode($targetValues);
  86  
  87              $optionalsourcefield = $mapping['optionalsourcefield'];
  88              $optionalsourcevalues = $mapping['optionalsourcevalues'];
  89  
  90              if(!empty($optionalsourcefield)) {
  91                  $criteria = array();
  92                  $criteria["fieldname"] = $optionalsourcefield;
  93                  $criteria["fieldvalues"] = $optionalsourcevalues;
  94                  $serializedCriteria = Zend_Json::encode($criteria);
  95              } else {
  96                  $serializedCriteria = null;
  97              }
  98              //to handle Accent Sensitive search in MySql
  99              //reference Links http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html , http://stackoverflow.com/questions/500826/how-to-conduct-an-accent-sensitive-search-in-mysql
 100              $checkForExistenceResult = $adb->pquery("SELECT id FROM vtiger_picklist_dependency WHERE tabid=? AND sourcefield=? AND targetfield=? AND sourcevalue=CAST(? AS CHAR CHARACTER SET utf8) COLLATE utf8_bin",
 101                      array($tabId, $sourceField, $targetField, $sourceValue));
 102              if($adb->num_rows($checkForExistenceResult) > 0) {
 103                  $dependencyId = $adb->query_result($checkForExistenceResult, 0, 'id');
 104                  $adb->pquery("UPDATE vtiger_picklist_dependency SET targetvalues=?, criteria=? WHERE id=?",
 105                          array($serializedTargetValues, $serializedCriteria, $dependencyId));
 106              } else {
 107                  $adb->pquery("INSERT INTO vtiger_picklist_dependency (id, tabid, sourcefield, targetfield, sourcevalue, targetvalues, criteria)
 108                                  VALUES (?,?,?,?,?,?,?)",
 109                          array($adb->getUniqueID('vtiger_picklist_dependency'), $tabId, $sourceField, $targetField, $sourceValue,
 110                          $serializedTargetValues, $serializedCriteria));
 111              }
 112          }
 113      }
 114  
 115  	static function deletePickListDependencies($module, $sourceField, $targetField) {
 116          global $adb;
 117  
 118          $tabId = getTabid($module);
 119  
 120          $adb->pquery("DELETE FROM vtiger_picklist_dependency WHERE tabid=? AND sourcefield=? AND targetfield=?",
 121                  array($tabId, $sourceField, $targetField));
 122      }
 123  
 124  	static function getPickListDependency($module, $sourceField, $targetField) {
 125          global $adb;
 126  
 127          $tabId = getTabid($module);
 128          $dependencyMap = array();
 129          $dependencyMap['sourcefield'] = $sourceField;
 130          $dependencyMap['targetfield'] = $targetField;
 131  
 132          $result = $adb->pquery('SELECT * FROM vtiger_picklist_dependency WHERE tabid=? AND sourcefield=? AND targetfield=?',
 133                  array($tabId,$sourceField,$targetField));
 134          $noOfMapping = $adb->num_rows($result);
 135  
 136          $valueMapping = array();
 137          $mappedSourceValues = array();
 138          for($i=0; $i<$noOfMapping; ++$i) {
 139              $sourceValue = $adb->query_result($result, $i, 'sourcevalue');
 140              $targetValues = $adb->query_result($result, $i, 'targetvalues');
 141              $unserializedTargetValues = Zend_Json::decode(html_entity_decode($targetValues));
 142  
 143              $mapping = array();
 144              $mapping['sourcevalue'] = $sourceValue;
 145              $mapping['targetvalues'] = $unserializedTargetValues;
 146  
 147              $valueMapping[$i] = $mapping ;
 148          }
 149          $dependencyMap['valuemapping'] = $valueMapping;
 150  
 151          return $dependencyMap;
 152      }
 153  
 154  	static function getPicklistDependencyDatasource($module) {
 155          global $adb;
 156  
 157          $tabId = getTabid($module);
 158  
 159          $result = $adb->pquery('SELECT * FROM vtiger_picklist_dependency WHERE tabid=?', array($tabId));
 160          $noofrows = $adb->num_rows($result);
 161  
 162          $picklistDependencyDatasource = array();
 163          for($i=0; $i<$noofrows; ++$i) {
 164              $pickArray = array();
 165              $sourceField = $adb->query_result($result, $i, 'sourcefield');
 166              $targetField = $adb->query_result($result, $i, 'targetfield');
 167              $sourceValue = decode_html($adb->query_result($result, $i, 'sourcevalue'));
 168              $targetValues = decode_html($adb->query_result($result, $i, 'targetvalues'));
 169              $unserializedTargetValues = Zend_Json::decode(html_entity_decode($targetValues));
 170              $criteria = decode_html($adb->query_result($result, $i, 'criteria'));
 171              $unserializedCriteria = Zend_Json::decode(html_entity_decode($criteria));
 172  
 173              if(!empty($unserializedCriteria) && $unserializedCriteria['fieldname'] != null) {
 174                  $conditionValue = array(
 175                          "condition" => array($unserializedCriteria['fieldname'] => $unserializedCriteria['fieldvalues']),
 176                          "values" => $unserializedTargetValues
 177                  );
 178                  $picklistDependencyDatasource[$sourceField][$sourceValue][$targetField][] = $conditionValue;
 179              } else {
 180                  $picklistDependencyDatasource[$sourceField][$sourceValue][$targetField] = $unserializedTargetValues;
 181              }
 182              if(empty($picklistDependencyDatasource[$sourceField]['__DEFAULT__'][$targetField])) {
 183                  foreach(getAllPicklistValues($targetField) as $picklistValue) {
 184                      $pickArray[] = decode_html($picklistValue);
 185                  }
 186                  $picklistDependencyDatasource[$sourceField]['__DEFAULT__'][$targetField] = $pickArray;
 187              }
 188          }
 189          return $picklistDependencyDatasource;
 190      }
 191  
 192  	static function getJSPicklistDependencyDatasource($module) {
 193          $picklistDependencyDatasource = Vtiger_DependencyPicklist::getPicklistDependencyDatasource($module);
 194          return Zend_Json::encode($picklistDependencyDatasource);
 195      }
 196  
 197  	static function checkCyclicDependency($module, $sourceField, $targetField) {
 198          $adb = PearDatabase::getInstance();
 199  
 200          // If another parent field exists for the same target field - 2 parent fields should not be allowed for a target field
 201          $result = $adb->pquery('SELECT 1 FROM vtiger_picklist_dependency
 202                                      WHERE tabid = ? AND targetfield = ? AND sourcefield != ?',
 203                  array(getTabid($module), $targetField, $sourceField));
 204          if($adb->num_rows($result) > 0) {
 205              return true;
 206          }
 207  
 208          //TODO - Add required check for cyclic dependency
 209  
 210          return false;
 211      }
 212  
 213  	static function getDependentPickListModules() {
 214          $adb = PearDatabase::getInstance();
 215  
 216          $query = 'SELECT distinct vtiger_field.tabid, vtiger_tab.tablabel, vtiger_tab.name as tabname FROM vtiger_field
 217                          INNER JOIN vtiger_tab ON vtiger_tab.tabid = vtiger_field.tabid
 218                          INNER JOIN vtiger_picklist ON vtiger_picklist.name = vtiger_field.fieldname
 219                      WHERE uitype IN (15,16)
 220                          AND vtiger_field.tabid != 29
 221                          AND vtiger_field.displaytype = 1
 222                          AND vtiger_field.presence in (0,2)
 223                      GROUP BY vtiger_field.tabid HAVING count(*) > 1';
 224          // END
 225          $result = $adb->pquery($query, array());
 226          while($row = $adb->fetch_array($result)) {
 227              $modules[$row['tablabel']] = $row['tabname'];
 228          }
 229          ksort($modules);
 230          return $modules;
 231      }
 232  
 233  }
 234  ?>