[Summary view] [Print] [Text view]

   1  <?php
   2  /*+**********************************************************************************
   3   * The contents of this file are subject to the vtiger CRM Public License Version 1.1
   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  class Settings_Picklist_Module_Model extends Vtiger_Module_Model {
  12  
  13      public function getPickListTableName($fieldName) {
  14          return 'vtiger_'.$fieldName;
  15      }
  16  
  17      public function getFieldsByType($type) {
  18          $presence = array('0','2');
  19  
  20          $fieldModels = parent::getFieldsByType($type);
  21          $fields = array();
  22          foreach($fieldModels as $fieldName=>$fieldModel) {
  23              if(($fieldModel->get('displaytype') != '1' && $fieldName != 'salutationtype') || !in_array($fieldModel->get('presence'),$presence)) {
  24                  continue;
  25              }
  26              $fields[$fieldName] = Settings_Picklist_Field_Model::getInstanceFromFieldObject($fieldModel);
  27          }
  28          return $fields;
  29      }
  30  
  31      public function addPickListValues($fieldModel, $newValue, $rolesSelected = array()) {
  32          $db = PearDatabase::getInstance();
  33          $pickListFieldName = $fieldModel->getName();
  34          $id = $db->getUniqueID("vtiger_$pickListFieldName");
  35          vimport('~~/include/ComboUtil.php');
  36          $picklist_valueid = getUniquePicklistID();
  37          $tableName = 'vtiger_'.$pickListFieldName;
  38          $maxSeqQuery = 'SELECT max(sortorderid) as maxsequence FROM '.$tableName;
  39          $result = $db->pquery($maxSeqQuery, array());
  40          $sequence = $db->query_result($result,0,'maxsequence');
  41  
  42          if($fieldModel->isRoleBased()) {
  43              $sql = 'INSERT INTO '.$tableName.' VALUES (?,?,?,?,?)';
  44              $db->pquery($sql, array($id, $newValue, 1, $picklist_valueid,++$sequence));
  45          }else{
  46              $sql = 'INSERT INTO '.$tableName.' VALUES (?,?,?,?)';
  47              $db->pquery($sql, array($id, $newValue, ++$sequence, 1));
  48          }
  49  
  50          if($fieldModel->isRoleBased() && !empty($rolesSelected)) {
  51              $sql = "select picklistid from vtiger_picklist where name=?";
  52              $result = $db->pquery($sql, array($pickListFieldName));
  53              $picklistid = $db->query_result($result,0,"picklistid");
  54              //add the picklist values to the selected roles
  55              for($j=0;$j<count($rolesSelected);$j++){
  56                  $roleid = $rolesSelected[$j];
  57  
  58                  $sql ="SELECT max(sortid)+1 as sortid
  59                         FROM vtiger_role2picklist left join vtiger_$pickListFieldName
  60                             on vtiger_$pickListFieldName.picklist_valueid=vtiger_role2picklist.picklistvalueid
  61                         WHERE roleid=? and picklistid=?";
  62                  $sortid = $db->query_result($db->pquery($sql, array($roleid, $picklistid)),0,'sortid');
  63  
  64                  $sql = "insert into vtiger_role2picklist values(?,?,?,?)";
  65                  $db->pquery($sql, array($roleid, $picklist_valueid, $picklistid, $sortid));
  66              }
  67  
  68          }
  69          return array('picklistValueId' => $picklist_valueid,
  70                      'id' => $id);
  71      }
  72  
  73      public function renamePickListValues($pickListFieldName, $oldValue, $newValue, $moduleName, $id) {
  74          $db = PearDatabase::getInstance();
  75  
  76          $query = 'SELECT tablename,columnname FROM vtiger_field WHERE fieldname=? and presence IN (0,2)';
  77          $result = $db->pquery($query, array($pickListFieldName));
  78          $num_rows = $db->num_rows($result);
  79  
  80          //As older look utf8 characters are pushed as html-entities,and in new utf8 characters are pushed to database
  81          //so we are checking for both the values
  82          $primaryKey = Vtiger_Util_Helper::getPickListId($pickListFieldName);
  83          
  84          $query = 'UPDATE ' . $this->getPickListTableName($pickListFieldName) . ' SET ' . $pickListFieldName . '=? WHERE '.$primaryKey.' = ?';
  85          $db->pquery($query, array($newValue, $id));
  86  
  87          for ($i = 0; $i < $num_rows; $i++) {
  88              $row = $db->query_result_rowdata($result, $i);
  89              $tableName = $row['tablename'];
  90              $columnName = $row['columnname'];
  91              $query = 'UPDATE ' . $tableName . ' SET ' . $columnName . '=? WHERE ' . $columnName . '=?';
  92              $db->pquery($query, array($newValue, $oldValue));
  93          }
  94  
  95          $query = "UPDATE vtiger_field SET defaultvalue=? WHERE defaultvalue=? AND columnname=?";
  96          $db->pquery($query, array($newValue, $oldValue, $columnName));
  97  
  98          vimport('~~/include/utils/CommonUtils.php');
  99  
 100          $query = "UPDATE vtiger_picklist_dependency SET sourcevalue=? WHERE sourcevalue=? AND sourcefield=?";
 101          $db->pquery($query, array($newValue, $oldValue, $pickListFieldName));
 102                  
 103          $em = new VTEventsManager($db);
 104          $data = array();
 105          $data['fieldname'] = $pickListFieldName;
 106          $data['oldvalue'] = $oldValue;
 107          $data['newvalue'] = $newValue;
 108          $data['module'] = $moduleName;
 109          $em->triggerEvent('vtiger.picklist.afterrename', $data);
 110          
 111          return true;
 112      }
 113  
 114      public function remove($pickListFieldName , $valueToDeleteId, $replaceValueId , $moduleName) {
 115          $db = PearDatabase::getInstance();
 116          if(!is_array($valueToDeleteId)) {
 117              $valueToDeleteId = array($valueToDeleteId);
 118          }
 119          $primaryKey = Vtiger_Util_Helper::getPickListId($pickListFieldName);
 120          
 121          $pickListValues = array();
 122          $valuesOfDeleteIds = "SELECT $pickListFieldName FROM ".$this->getPickListTableName($pickListFieldName)." WHERE $primaryKey IN (".generateQuestionMarks($valueToDeleteId).")";
 123          $pickListValuesResult = $db->pquery($valuesOfDeleteIds,array($valueToDeleteId));
 124          $num_rows = $db->num_rows($pickListValuesResult);
 125          for($i=0;$i<$num_rows;$i++) {
 126              $pickListValues[] = decode_html($db->query_result($pickListValuesResult,$i,$pickListFieldName)); 
 127          }
 128          
 129          $replaceValueQuery = $db->pquery("SELECT $pickListFieldName FROM ".$this->getPickListTableName($pickListFieldName)." WHERE $primaryKey IN (".generateQuestionMarks($replaceValueId).")",array($replaceValueId));
 130          $replaceValue = decode_html($db->query_result($replaceValueQuery,0,$pickListFieldName));
 131          
 132          //As older look utf8 characters are pushed as html-entities,and in new utf8 characters are pushed to database
 133          //so we are checking for both the values
 134                  $encodedValueToDelete = array(); 
 135          foreach ($pickListValues as $key => $value) {
 136              $encodedValueToDelete[$key]  = Vtiger_Util_Helper::toSafeHTML($value);
 137          }
 138          $mergedValuesToDelete = array_merge($pickListValues, $encodedValueToDelete);
 139          
 140          $fieldModel = Settings_Picklist_Field_Model::getInstance($pickListFieldName,$this);
 141          //if role based then we need to delete all the values in role based picklist
 142          if($fieldModel->isRoleBased()) {
 143              $picklistValueIdToDelete = array();
 144              $query = 'SELECT picklist_valueid FROM '.$this->getPickListTableName($pickListFieldName).
 145                       ' WHERE '.$primaryKey.' IN ('.generateQuestionMarks($valueToDeleteId).')';
 146              $result = $db->pquery($query,$valueToDeleteId);
 147              $num_rows = $db->num_rows($result);
 148              for($i=0;$i<$num_rows;$i++) {
 149                  $picklistValueIdToDelete[] = $db->query_result($result,$i,'picklist_valueid');
 150              }
 151              $query = 'DELETE FROM vtiger_role2picklist WHERE picklistvalueid IN ('.generateQuestionMarks($picklistValueIdToDelete).')';
 152              $db->pquery($query,$picklistValueIdToDelete);
 153          }
 154  
 155          $query = 'DELETE FROM '. $this->getPickListTableName($pickListFieldName).
 156                      ' WHERE '.$primaryKey.' IN ('.  generateQuestionMarks($valueToDeleteId).')';
 157          $db->pquery($query,$valueToDeleteId);
 158  
 159          vimport('~~/include/utils/CommonUtils.php');
 160          $tabId = getTabId($moduleName);
 161          $query = 'DELETE FROM vtiger_picklist_dependency WHERE sourcevalue IN ('. generateQuestionMarks($pickListValues) .')'.
 162                  ' AND sourcefield=?';
 163          $params = array();
 164          array_push($params, $pickListValues);
 165          array_push($params, $pickListFieldName);
 166          $db->pquery($query, $params);
 167  
 168          $query='SELECT tablename,columnname FROM vtiger_field WHERE fieldname=? AND presence in (0,2)';
 169          $result = $db->pquery($query, array($pickListFieldName));
 170          $num_row = $db->num_rows($result);
 171  
 172          for($i=0; $i<$num_row; $i++) {
 173              $row = $db->query_result_rowdata($result, $i);
 174              $tableName = $row['tablename'];
 175              $columnName = $row['columnname'];
 176  
 177              $query = 'UPDATE '.$tableName.' SET '.$columnName.'=? WHERE '.$columnName.' IN ('.  generateQuestionMarks($pickListValues).')';
 178              $params = array($replaceValue);
 179              array_push($params, $pickListValues);
 180              $db->pquery($query, $params);
 181          }
 182          
 183          $query = 'UPDATE vtiger_field SET defaultvalue=? WHERE defaultvalue IN ('. generateQuestionMarks($pickListValues) .') AND columnname=?';
 184          $params = array($replaceValue);
 185          array_push($params, $pickListValues);
 186          array_push($params, $columnName);
 187          $db->pquery($query, $params);
 188          
 189          $em = new VTEventsManager($db);
 190          $data = array();
 191          $data['fieldname'] = $pickListFieldName;
 192          $data['valuetodelete'] = $pickListValues;
 193          $data['replacevalue'] = $replaceValue;
 194          $data['module'] = $moduleName;
 195          $em->triggerEvent('vtiger.picklist.afterdelete', $data);
 196  
 197          return true;
 198      }
 199  
 200      public function enableOrDisableValuesForRole($picklistFieldName, $valuesToEnables, $valuesToDisable, $roleIdList) {
 201          $db = PearDatabase::getInstance();
 202          //To disable die On error since we will be doing insert without chekcing
 203          $dieOnErrorOldValue = $db->dieOnError;
 204          $db->dieOnError = false;
 205  
 206          $sql = "select picklistid from vtiger_picklist where name=?";
 207          $result = $db->pquery($sql, array($picklistFieldName));
 208          $picklistid = $db->query_result($result,0,"picklistid");
 209          
 210          $primaryKey = Vtiger_Util_Helper::getPickListId($picklistFieldName);
 211  
 212          $pickListValueList = array_merge($valuesToEnables,$valuesToDisable);
 213          $pickListValueDetails = array();
 214          $query = 'SELECT picklist_valueid,'. $picklistFieldName.', '.$primaryKey.
 215                   ' FROM '.$this->getPickListTableName($picklistFieldName).
 216                   ' WHERE '.$primaryKey .' IN ('.  generateQuestionMarks($pickListValueList).')';
 217          $params = array();
 218          array_push($params, $pickListValueList);
 219  
 220          $result = $db->pquery($query, $params);
 221          $num_rows = $db->num_rows($result);
 222  
 223          for($i=0; $i<$num_rows; $i++) {
 224              $row = $db->query_result_rowdata($result,$i);
 225  
 226              $pickListValueDetails[decode_html($row[$primaryKey])] =array('picklistvalueid'=>$row['picklist_valueid'],
 227                                                                      'picklistid'=>$picklistid);
 228          }
 229          $insertValueList = array();
 230          $deleteValueList = array();
 231          foreach($roleIdList as $roleId) {
 232              foreach($valuesToEnables  as $picklistValue) {
 233                  $valueDetail = $pickListValueDetails[$picklistValue];
 234                  if(empty($valueDetail)){
 235                       $valueDetail = $pickListValueDetails[Vtiger_Util_Helper::toSafeHTML($picklistValue)];
 236                  }
 237                  $pickListValueId = $valueDetail['picklistvalueid'];
 238                  $picklistId = $valueDetail['picklistid'];
 239                  $insertValueList[] = '("'.$roleId.'","'.$pickListValueId.'","'.$picklistId.'")';
 240              }
 241  
 242              foreach($valuesToDisable as $picklistValue) {
 243                  $valueDetail = $pickListValueDetails[$picklistValue];
 244                  if(empty($valueDetail)){
 245                       $valueDetail = $pickListValueDetails[Vtiger_Util_Helper::toSafeHTML($picklistValue)];
 246                  }
 247                  $pickListValueId = $valueDetail['picklistvalueid'];
 248                  $picklistId = $valueDetail['picklistid'];
 249                  $deleteValueList[] = ' ( roleid = "'.$roleId.'" AND '.'picklistvalueid = "'.$pickListValueId.'") ';
 250              }
 251          }
 252          $query = 'INSERT IGNORE INTO vtiger_role2picklist (roleid,picklistvalueid,picklistid) VALUES '.implode(',',$insertValueList);
 253          $result = $db->pquery($query,array());
 254  
 255          $deleteQuery = 'DELETE FROM vtiger_role2picklist WHERE '.implode(' OR ',$deleteValueList);
 256  
 257          $result = $db->pquery($deleteQuery,array());
 258  
 259          //retaining to older value
 260          $db->dieOnError = $dieOnErrorOldValue;
 261  
 262      }
 263  
 264      public function updateSequence($pickListFieldName , $picklistValues) {
 265          $db = PearDatabase::getInstance();
 266  
 267          $primaryKey = Vtiger_Util_Helper::getPickListId($pickListFieldName);
 268          
 269          $query = 'UPDATE '.$this->getPickListTableName($pickListFieldName).' SET sortorderid = CASE ';
 270          foreach($picklistValues as $values => $sequence) {
 271              $query .= ' WHEN '.$primaryKey.'="'.$values.'" THEN "'.$sequence.'"';
 272          }
 273          $query .= ' END';
 274          $db->pquery($query, array());
 275      }
 276  
 277  
 278      public static function getPicklistSupportedModules() {
 279           $db = PearDatabase::getInstance();
 280  
 281          // vtlib customization: Ignore disabled modules.
 282          $query = 'SELECT distinct vtiger_tab.tablabel, vtiger_tab.name as tabname
 283                    FROM vtiger_tab
 284                          inner join vtiger_field on vtiger_tab.tabid=vtiger_field.tabid
 285                    WHERE uitype IN (15,33,16) and vtiger_field.tabid NOT IN (29,10)  and vtiger_tab.presence != 1 and vtiger_field.presence in (0,2)
 286                    ORDER BY vtiger_tab.tabid ASC';
 287          // END
 288          $result = $db->pquery($query, array());
 289  
 290          $modulesModelsList = array();
 291          while($row = $db->fetch_array($result)){
 292              $moduleLabel = $row['tablabel'];
 293              $moduleName  = $row['tabname'];
 294              $instance = new self();
 295              $instance->name = $moduleName;
 296              $instance->label = $moduleLabel;
 297              $modulesModelsList[] = $instance;
 298          }
 299          return $modulesModelsList;
 300      }
 301  
 302  
 303      /**
 304       * Static Function to get the instance of Vtiger Module Model for the given id or name
 305       * @param mixed id or name of the module
 306       */
 307  	public static function getInstance($value) {
 308          //TODO : add caching
 309          $instance = false;
 310              $moduleObject = parent::getInstance($value);
 311              if($moduleObject) {
 312              $instance = self::getInstanceFromModuleObject($moduleObject);
 313              }
 314          return $instance;
 315      }
 316  
 317      /**
 318       * Function to get the instance of Vtiger Module Model from a given Vtiger_Module object
 319       * @param Vtiger_Module $moduleObj
 320       * @return Vtiger_Module_Model instance
 321       */
 322  	public static function getInstanceFromModuleObject(Vtiger_Module $moduleObj){
 323          $objectProperties = get_object_vars($moduleObj);
 324          $moduleModel = new self();
 325          foreach($objectProperties as $properName=>$propertyValue){
 326              $moduleModel->$properName = $propertyValue;
 327          }
 328          return $moduleModel;
 329      }
 330  }