[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  class Leads_Module_Model extends Vtiger_Module_Model {
  12      /**
  13       * Function to get the Quick Links for the module
  14       * @param <Array> $linkParams
  15       * @return <Array> List of Vtiger_Link_Model instances
  16       */
  17  	public function getSideBarLinks($linkParams) {
  18          $links = parent::getSideBarLinks($linkParams);
  19  
  20          $quickLink = array(
  21              'linktype' => 'SIDEBARLINK',
  22              'linklabel' => 'LBL_DASHBOARD',
  23              'linkurl' => $this->getDashBoardUrl(),
  24              'linkicon' => '',
  25          );
  26          
  27          //Check profile permissions for Dashboards
  28          $moduleModel = Vtiger_Module_Model::getInstance('Dashboard');
  29          $userPrivilegesModel = Users_Privileges_Model::getCurrentUserPrivilegesModel();
  30          $permission = $userPrivilegesModel->hasModulePermission($moduleModel->getId());
  31          if($permission) {
  32              $links['SIDEBARLINK'][] = Vtiger_Link_Model::getInstanceFromValues($quickLink);
  33          }
  34          
  35          return $links;
  36      }
  37  
  38      /**
  39       * Function returns Settings Links
  40       * @return Array
  41       */
  42  	public function getSettingLinks() {
  43          $currentUserModel = Users_Record_Model::getCurrentUserModel();
  44          $settingLinks = parent::getSettingLinks();
  45          
  46          if($currentUserModel->isAdminUser()) {
  47              $settingLinks[] = array(
  48                      'linktype' => 'LISTVIEWSETTING',
  49                      'linklabel' => 'LBL_CUSTOM_FIELD_MAPPING',
  50                      'linkurl' => 'index.php?parent=Settings&module=Leads&view=MappingDetail',
  51                      'linkicon' => '');
  52              
  53          }
  54          return $settingLinks;
  55      }
  56  
  57      /**
  58      * Function returns deleted records condition
  59      */
  60      public function getDeletedRecordCondition() {
  61         return 'vtiger_crmentity.deleted = 0 AND vtiger_leaddetails.converted = 0';
  62      }
  63  
  64      /**
  65       * Function to get the list of recently visisted records
  66       * @param <Number> $limit
  67       * @return <Array> - List of Vtiger_Record_Model or Module Specific Record Model instances
  68       */
  69  	public function getRecentRecords($limit=10) {
  70          $db = PearDatabase::getInstance();
  71  
  72          $currentUserModel = Users_Record_Model::getCurrentUserModel();
  73          $deletedCondition = $this->getDeletedRecordCondition();
  74          $query = 'SELECT * FROM vtiger_crmentity '.
  75              ' INNER JOIN vtiger_leaddetails ON
  76                  vtiger_leaddetails.leadid = vtiger_crmentity.crmid
  77                  WHERE setype=? AND '.$deletedCondition.' AND modifiedby = ? ORDER BY modifiedtime DESC LIMIT ?';
  78          $params = array($this->get('name'), $currentUserModel->id, $limit);
  79          $result = $db->pquery($query, $params);
  80          $noOfRows = $db->num_rows($result);
  81  
  82          $recentRecords = array();
  83          for($i=0; $i<$noOfRows; ++$i) {
  84              $row = $db->query_result_rowdata($result, $i);
  85              $row['id'] = $row['crmid'];
  86              $recentRecords[$row['id']] = $this->getRecordFromArray($row);
  87          }
  88          return $recentRecords;
  89      }
  90  
  91      /**
  92       * Function returns the Number of Leads created per week
  93       * @param type $data
  94       * @return <Array>
  95       */
  96  	public function getLeadsCreated($owner, $dateFilter) {
  97          $db = PearDatabase::getInstance();
  98  
  99          $ownerSql = $this->getOwnerWhereConditionForDashBoards($owner);
 100          if(!empty($ownerSql)) {
 101              $ownerSql = ' AND '.$ownerSql;
 102          }
 103          
 104          $params = array();
 105          if(!empty($dateFilter)) {
 106              $dateFilterSql = ' AND createdtime BETWEEN ? AND ? ';
 107              //client is not giving time frame so we are appending it
 108              $params[] = $dateFilter['start']. ' 00:00:00';
 109              $params[] = $dateFilter['end']. ' 23:59:59';
 110          }
 111  
 112          $result = $db->pquery('SELECT COUNT(*) AS count, date(createdtime) AS time FROM vtiger_leaddetails
 113                          INNER JOIN vtiger_crmentity ON vtiger_leaddetails.leadid = vtiger_crmentity.crmid
 114                          AND deleted=0 '.Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()).$ownerSql.' '.$dateFilterSql.' AND converted = 0 GROUP BY week(createdtime)',
 115                      $params);
 116  
 117          $response = array();
 118          for($i=0; $i<$db->num_rows($result); $i++) {
 119              $row = $db->query_result_rowdata($result, $i);
 120              $response[$i][0] = $row['count'];
 121              $response[$i][1] = $row['time'];
 122          }
 123          return $response;
 124      }
 125  
 126      /**
 127       * Function returns Leads grouped by Status
 128       * @param type $data
 129       * @return <Array>
 130       */
 131  	public function getLeadsByStatus($owner,$dateFilter) {
 132          $db = PearDatabase::getInstance();
 133  
 134          $ownerSql = $this->getOwnerWhereConditionForDashBoards($owner);
 135          if(!empty($ownerSql)) {
 136              $ownerSql = ' AND '.$ownerSql;
 137          }
 138          
 139          $params = array();
 140          if(!empty($dateFilter)) {
 141              $dateFilterSql = ' AND createdtime BETWEEN ? AND ? ';
 142              //client is not giving time frame so we are appending it
 143              $params[] = $dateFilter['start']. ' 00:00:00';
 144              $params[] = $dateFilter['end']. ' 23:59:59';
 145          }
 146  
 147          $result = $db->pquery('SELECT COUNT(*) as count, CASE WHEN vtiger_leadstatus.leadstatus IS NULL OR vtiger_leadstatus.leadstatus = "" THEN "" ELSE 
 148                          vtiger_leadstatus.leadstatus END AS leadstatusvalue FROM vtiger_leaddetails 
 149                          INNER JOIN vtiger_crmentity ON vtiger_leaddetails.leadid = vtiger_crmentity.crmid
 150                          AND deleted=0 AND converted = 0 '.Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()). $ownerSql .' '.$dateFilterSql.
 151                          'INNER JOIN vtiger_leadstatus ON vtiger_leaddetails.leadstatus = vtiger_leadstatus.leadstatus 
 152                          GROUP BY leadstatusvalue ORDER BY vtiger_leadstatus.sortorderid', $params);
 153  
 154          $response = array();
 155          
 156          for($i=0; $i<$db->num_rows($result); $i++) {
 157              $row = $db->query_result_rowdata($result, $i);
 158              $response[$i][0] = $row['count'];
 159              $leadStatusVal = $row['leadstatusvalue'];
 160              if($leadStatusVal == '') {
 161                  $leadStatusVal = 'LBL_BLANK';
 162              }
 163              $response[$i][1] = vtranslate($leadStatusVal, $this->getName());
 164              $response[$i][2] = $leadStatusVal;
 165          }
 166          return $response;
 167      }
 168  
 169      /**
 170       * Function returns Leads grouped by Source
 171       * @param type $data
 172       * @return <Array>
 173       */
 174  	public function getLeadsBySource($owner,$dateFilter) {
 175          $db = PearDatabase::getInstance();
 176  
 177          $ownerSql = $this->getOwnerWhereConditionForDashBoards($owner);
 178          if(!empty($ownerSql)) {
 179              $ownerSql = ' AND '.$ownerSql;
 180          }
 181          
 182          $params = array();
 183          if(!empty($dateFilter)) {
 184              $dateFilterSql = ' AND createdtime BETWEEN ? AND ? ';
 185              //client is not giving time frame so we are appending it
 186              $params[] = $dateFilter['start']. ' 00:00:00';
 187              $params[] = $dateFilter['end']. ' 23:59:59';
 188          }
 189          
 190          $result = $db->pquery('SELECT COUNT(*) as count, CASE WHEN vtiger_leaddetails.leadsource IS NULL OR vtiger_leaddetails.leadsource = "" THEN "" 
 191                          ELSE vtiger_leaddetails.leadsource END AS leadsourcevalue FROM vtiger_leaddetails 
 192                          INNER JOIN vtiger_crmentity ON vtiger_leaddetails.leadid = vtiger_crmentity.crmid
 193                          AND deleted=0 AND converted = 0 '.Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()). $ownerSql .' '.$dateFilterSql.
 194                          'INNER JOIN vtiger_leadsource ON vtiger_leaddetails.leadsource = vtiger_leadsource.leadsource 
 195                          GROUP BY leadsourcevalue ORDER BY vtiger_leadsource.sortorderid', $params);
 196          
 197          $response = array();
 198          for($i=0; $i<$db->num_rows($result); $i++) {
 199              $row = $db->query_result_rowdata($result, $i);
 200              $response[$i][0] = $row['count'];
 201              $leadSourceVal =  $row['leadsourcevalue'];
 202              if($leadSourceVal == '') {
 203                  $leadSourceVal = 'LBL_BLANK';
 204              }
 205              $response[$i][1] = vtranslate($leadSourceVal, $this->getName());
 206              $response[$i][2] = $leadSourceVal;
 207          }
 208          return $response;
 209      }
 210  
 211      /**
 212       * Function returns Leads grouped by Industry
 213       * @param type $data
 214       * @return <Array>
 215       */
 216  	public function getLeadsByIndustry($owner,$dateFilter) {
 217          $db = PearDatabase::getInstance();
 218  
 219          $ownerSql = $this->getOwnerWhereConditionForDashBoards($owner);
 220          if(!empty($ownerSql)) {
 221              $ownerSql = ' AND '.$ownerSql;
 222          }
 223          
 224          $params = array();
 225          if(!empty($dateFilter)) {
 226              $dateFilterSql = ' AND createdtime BETWEEN ? AND ? ';
 227              //client is not giving time frame so we are appending it
 228              $params[] = $dateFilter['start']. ' 00:00:00';
 229              $params[] = $dateFilter['end']. ' 23:59:59';
 230          }
 231          
 232          $result = $db->pquery('SELECT COUNT(*) as count, CASE WHEN vtiger_leaddetails.industry IS NULL OR vtiger_leaddetails.industry = "" THEN "" 
 233                          ELSE vtiger_leaddetails.industry END AS industryvalue FROM vtiger_leaddetails 
 234                          INNER JOIN vtiger_crmentity ON vtiger_leaddetails.leadid = vtiger_crmentity.crmid
 235                          AND deleted=0 AND converted = 0 '.Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()). $ownerSql .' '.$dateFilterSql.'
 236                          INNER JOIN vtiger_industry ON vtiger_leaddetails.industry = vtiger_industry.industry 
 237                          GROUP BY industryvalue ORDER BY vtiger_industry.sortorderid', $params);
 238          
 239          $response = array();
 240          for($i=0; $i<$db->num_rows($result); $i++) {
 241              $row = $db->query_result_rowdata($result, $i);
 242              $response[$i][0] = $row['count'];
 243              $industyValue = $row['industryvalue'];
 244              if($industyValue == '') {
 245                  $industyValue = 'LBL_BLANK';
 246              }
 247              $response[$i][1] = vtranslate($industyValue, $this->getName());
 248              $response[$i][2] = $industyValue;
 249          }
 250          return $response;
 251      }
 252  
 253      /**
 254       * Function to get relation query for particular module with function name
 255       * @param <record> $recordId
 256       * @param <String> $functionName
 257       * @param Vtiger_Module_Model $relatedModule
 258       * @return <String>
 259       */
 260  	public function getRelationQuery($recordId, $functionName, $relatedModule) {
 261          if ($functionName === 'get_activities') {
 262              $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
 263  
 264              $query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN $userNameSql ELSE vtiger_groups.groupname END AS user_name,
 265                          vtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,
 266                          vtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, vtiger_seactivityrel.crmid AS parent_id,
 267                          CASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status
 268                          FROM vtiger_activity
 269                          INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid
 270                          LEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid
 271                          LEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid
 272                          LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
 273                          LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid
 274                              WHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.activitytype <> 'Emails'
 275                                  AND vtiger_seactivityrel.crmid = ".$recordId;
 276  
 277              $relatedModuleName = $relatedModule->getName();
 278              $query .= $this->getSpecificRelationQuery($relatedModuleName);
 279              $nonAdminQuery = $this->getNonAdminAccessControlQueryForRelation($relatedModuleName);
 280              if ($nonAdminQuery) {
 281                  $query = appendFromClauseToQuery($query, $nonAdminQuery);
 282              }
 283          } else {
 284              $query = parent::getRelationQuery($recordId, $functionName, $relatedModule);
 285          }
 286  
 287          return $query;
 288      }
 289  
 290      /**
 291       * Function to get Converted Information for selected records
 292       * @param <array> $recordIdsList
 293       * @return <array> converted Info
 294       */
 295  	public static function getConvertedInfo($recordIdsList = array()) {
 296          $convertedInfo = array();
 297          if ($recordIdsList) {
 298              $db = PearDatabase::getInstance();
 299              $result = $db->pquery("SELECT converted FROM vtiger_leaddetails WHERE leadid IN (".generateQuestionMarks($recordIdsList).")", $recordIdsList);
 300              $numOfRows = $db->num_rows($result);
 301  
 302              for ($i=0; $i<$numOfRows; $i++) {
 303                  $convertedInfo[$recordIdsList[$i]] = $db->query_result($result, $i, 'converted');
 304              }
 305          }
 306          return $convertedInfo;
 307      }
 308  
 309      /**
 310       * Function to get list view query for popup window
 311       * @param <String> $sourceModule Parent module
 312       * @param <String> $field parent fieldname
 313       * @param <Integer> $record parent id
 314       * @param <String> $listQuery
 315       * @return <String> Listview Query
 316       */
 317  	public function getQueryByModuleField($sourceModule, $field, $record, $listQuery) {
 318          if (in_array($sourceModule, array('Campaigns', 'Products', 'Services', 'Emails'))) {
 319              switch ($sourceModule) {
 320                  case 'Campaigns'    : $tableName = 'vtiger_campaignleadrel';    $fieldName = 'leadid';    $relatedFieldName ='campaignid';    break;
 321                  case 'Products'        : $tableName = 'vtiger_seproductsrel';        $fieldName = 'crmid';        $relatedFieldName ='productid';        break;
 322              }
 323  
 324              if ($sourceModule === 'Services') {
 325                  $condition = " vtiger_leaddetails.leadid NOT IN (SELECT relcrmid FROM vtiger_crmentityrel WHERE crmid = '$record' UNION SELECT crmid FROM vtiger_crmentityrel WHERE relcrmid = '$record') ";
 326              } elseif ($sourceModule === 'Emails') {
 327                  $condition = ' vtiger_leaddetails.emailoptout = 0';
 328              } else {
 329                  $condition = " vtiger_leaddetails.leadid NOT IN (SELECT $fieldName FROM $tableName WHERE $relatedFieldName = '$record')";
 330              }
 331  
 332              $position = stripos($listQuery, 'where');
 333              if($position) {
 334                  $split = spliti('where', $listQuery);
 335                  $overRideQuery = $split[0] . ' WHERE ' . $split[1] . ' AND ' . $condition;
 336              } else {
 337                  $overRideQuery = $listQuery. ' WHERE ' . $condition;
 338              }
 339              return $overRideQuery;
 340          }
 341      }
 342       public function getDefaultSearchField(){
 343          return "lastname";
 344      }
 345  }