[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 Potentials_Module_Model extends Vtiger_Module_Model {
  12  
  13      /**
  14       * Function to get the Quick Links for the module
  15       * @param <Array> $linkParams
  16       * @return <Array> List of Vtiger_Link_Model instances
  17       */
  18  	public function getSideBarLinks($linkParams) {
  19          $parentQuickLinks = parent::getSideBarLinks($linkParams);
  20  
  21          $quickLink = array(
  22              'linktype' => 'SIDEBARLINK',
  23              'linklabel' => 'LBL_DASHBOARD',
  24              'linkurl' => $this->getDashBoardUrl(),
  25              'linkicon' => '',
  26          );
  27          
  28          //Check profile permissions for Dashboards
  29          $moduleModel = Vtiger_Module_Model::getInstance('Dashboard');
  30          $userPrivilegesModel = Users_Privileges_Model::getCurrentUserPrivilegesModel();
  31          $permission = $userPrivilegesModel->hasModulePermission($moduleModel->getId());
  32          if($permission) {
  33              $parentQuickLinks['SIDEBARLINK'][] = Vtiger_Link_Model::getInstanceFromValues($quickLink);
  34          }
  35          
  36          return $parentQuickLinks;
  37      }
  38  
  39      /**
  40       * Function returns number of Open Potentials in each of the sales stage
  41       * @param <Integer> $owner - userid
  42       * @return <Array>
  43       */
  44  	public function getPotentialsCountBySalesStage($owner, $dateFilter) {
  45          $db = PearDatabase::getInstance();
  46  
  47          if (!$owner) {
  48              $currenUserModel = Users_Record_Model::getCurrentUserModel();
  49              $owner = $currenUserModel->getId();
  50          } else if ($owner === 'all') {
  51              $owner = '';
  52          }
  53  
  54          $params = array();
  55          if(!empty($owner)) {
  56              $ownerSql =  ' AND smownerid = ? ';
  57              $params[] = $owner;
  58          }
  59          if(!empty($dateFilter)) {
  60              $dateFilterSql = ' AND closingdate BETWEEN ? AND ? ';
  61              $params[] = $dateFilter['start'];
  62              $params[] = $dateFilter['end'];
  63          }
  64  
  65          $result = $db->pquery('SELECT COUNT(*) count, sales_stage FROM vtiger_potential
  66                          INNER JOIN vtiger_crmentity ON vtiger_potential.potentialid = vtiger_crmentity.crmid
  67                          AND deleted = 0 '.Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()). $ownerSql . $dateFilterSql . ' AND sales_stage NOT IN ("Closed Won", "Closed Lost")
  68                              GROUP BY sales_stage ORDER BY count desc', $params);
  69          
  70          $response = array();
  71          for($i=0; $i<$db->num_rows($result); $i++) {
  72              $saleStage = $db->query_result($result, $i, 'sales_stage');
  73              $response[$i][0] = $saleStage;
  74              $response[$i][1] = $db->query_result($result, $i, 'count');
  75              $response[$i][2] = vtranslate($saleStage, $this->getName());
  76          }
  77          return $response;
  78      }
  79  
  80      /**
  81       * Function returns number of Open Potentials for each of the sales person
  82       * @param <Integer> $owner - userid
  83       * @return <Array>
  84       */
  85  	public function getPotentialsCountBySalesPerson() {
  86          $db = PearDatabase::getInstance();
  87          //TODO need to handle security
  88          $params = array();
  89          $result = $db->pquery('SELECT COUNT(*) AS count, concat(first_name," ",last_name) as last_name, vtiger_potential.sales_stage FROM vtiger_potential
  90                          INNER JOIN vtiger_crmentity ON vtiger_potential.potentialid = vtiger_crmentity.crmid
  91                          INNER JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid AND vtiger_users.status="ACTIVE"
  92                          AND vtiger_crmentity.deleted = 0'.Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()).'
  93                          INNER JOIN vtiger_sales_stage ON vtiger_potential.sales_stage =  vtiger_sales_stage.sales_stage 
  94                          GROUP BY smownerid, sales_stage ORDER BY vtiger_sales_stage.sortorderid', $params);
  95  
  96          $response = array();
  97          for($i=0; $i<$db->num_rows($result); $i++) {
  98              $row = $db->query_result_rowdata($result, $i);
  99              $response[$i]['count'] = $row['count'];
 100              $response[$i]['last_name'] = decode_html($row['last_name']);
 101              $response[$i]['sales_stage'] = $row['sales_stage'];
 102              //$response[$i][2] = $row['']
 103           }
 104          return $response;
 105      }
 106  
 107      /**
 108       * Function returns Potentials Amount for each Sales Person
 109       * @return <Array>
 110       */
 111  	function getPotentialsPipelinedAmountPerSalesPerson() {
 112          $db = PearDatabase::getInstance();
 113          //TODO need to handle security
 114          $params = array();
 115          $result = $db->pquery('SELECT sum(amount) AS amount, concat(first_name," ",last_name) as last_name, vtiger_potential.sales_stage FROM vtiger_potential
 116                          INNER JOIN vtiger_crmentity ON vtiger_potential.potentialid = vtiger_crmentity.crmid
 117                          INNER JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid AND vtiger_users.status="ACTIVE"
 118                          AND vtiger_crmentity.deleted = 0 '.Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()).
 119                          'INNER JOIN vtiger_sales_stage ON vtiger_potential.sales_stage =  vtiger_sales_stage.sales_stage 
 120                          WHERE vtiger_potential.sales_stage NOT IN ("Closed Won", "Closed Lost")
 121                          GROUP BY smownerid, sales_stage ORDER BY vtiger_sales_stage.sortorderid', $params);
 122          for($i=0; $i<$db->num_rows($result); $i++) {
 123              $row = $db->query_result_rowdata($result, $i);
 124                          $row['last_name'] = decode_html($row['last_name']);
 125              $data[] = $row;
 126          }
 127          return $data;
 128      }
 129  
 130      /**
 131       * Function returns Total Revenue for each Sales Person
 132       * @return <Array>
 133       */
 134  	function getTotalRevenuePerSalesPerson($dateFilter) {
 135          $db = PearDatabase::getInstance();
 136          //TODO need to handle security
 137          $params = array();
 138          $params[] = 'Closed Won';
 139          if(!empty($dateFilter)) {
 140              $dateFilterSql = ' AND createdtime BETWEEN ? AND ? ';
 141              //client is not giving time frame so we are appending it
 142              $params[] = $dateFilter['start']. ' 00:00:00';
 143              $params[] = $dateFilter['end']. ' 23:59:59';
 144          }
 145          
 146          $result = $db->pquery('SELECT sum(amount) amount, concat(first_name," ",last_name) as last_name,vtiger_users.id as id,DATE_FORMAT(closingdate, "%d-%m-%Y") AS closingdate  FROM vtiger_potential
 147                          INNER JOIN vtiger_crmentity ON vtiger_potential.potentialid = vtiger_crmentity.crmid
 148                          INNER JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid AND vtiger_users.status="ACTIVE"
 149                          AND vtiger_crmentity.deleted = 0 '.Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()).'WHERE sales_stage = ? '.' '.$dateFilterSql.' GROUP BY smownerid', $params);
 150          $data = array();
 151          for($i=0; $i<$db->num_rows($result); $i++) {
 152              $row = $db->query_result_rowdata($result, $i);
 153                          $row['last_name'] = decode_html($row['last_name']);
 154              $data[] = $row;
 155          }
 156          return $data;
 157      }
 158  
 159      /**
 160       * Function returns Top Potentials
 161       * @return <Array of Vtiger_Record_Model>
 162       */
 163  	function getTopPotentials($pagingModel) {
 164          $currentUser = Users_Record_Model::getCurrentUserModel();
 165          $db = PearDatabase::getInstance();
 166          $query = "SELECT crmid, amount, potentialname, related_to FROM vtiger_potential
 167                          INNER JOIN vtiger_crmentity ON vtiger_potential.potentialid = vtiger_crmentity.crmid
 168                              AND deleted = 0 ".Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName())."
 169                          WHERE sales_stage NOT IN ('Closed Won', 'Closed Lost') AND amount > 0
 170                          ORDER BY amount DESC LIMIT ".$pagingModel->getStartIndex().", ".$pagingModel->getPageLimit()."";
 171          $result = $db->pquery($query, array());
 172  
 173          $models = array();
 174          for($i=0; $i<$db->num_rows($result); $i++) {
 175              $modelInstance = Vtiger_Record_Model::getCleanInstance('Potentials');
 176              $modelInstance->setId($db->query_result($result, $i, 'crmid'));
 177              $modelInstance->set('amount', $db->query_result($result, $i, 'amount'));
 178              $modelInstance->set('potentialname', $db->query_result($result, $i, 'potentialname'));
 179              $modelInstance->set('related_to', $db->query_result($result, $i, 'related_to'));
 180              $models[] = $modelInstance;
 181          }
 182          return $models;
 183      }
 184  
 185      /**
 186       * Function returns Potentials Forecast Amount
 187       * @return <Array>
 188       */
 189  	function getForecast($closingdateFilter,$dateFilter) {
 190          $currentUser = Users_Record_Model::getCurrentUserModel();
 191          $db = PearDatabase::getInstance();
 192  
 193          $params = array();
 194          $params[] = $currentUser->getId();
 195          if(!empty($closingdateFilter)) {
 196              $closingdateFilterSql = ' AND closingdate BETWEEN ? AND ? ';
 197              $params[] = $closingdateFilter['start'];
 198              $params[] = $closingdateFilter['end'];
 199          }
 200          
 201          if(!empty($dateFilter)) {
 202              $dateFilterSql = ' AND createdtime BETWEEN ? AND ? ';
 203              //client is not giving time frame so we are appending it
 204              $params[] = $dateFilter['start']. ' 00:00:00';
 205              $params[] = $dateFilter['end']. ' 23:59:59';
 206          }
 207          
 208          $result = $db->pquery('SELECT forecast_amount, DATE_FORMAT(closingdate, "%m-%d-%Y") AS closingdate FROM vtiger_potential
 209                      INNER JOIN vtiger_crmentity ON vtiger_potential.potentialid = vtiger_crmentity.crmid
 210                      AND deleted = 0 AND smownerid = ? WHERE closingdate >= CURDATE() AND sales_stage NOT IN ("Closed Won", "Closed Lost")'.
 211                      ' '.$closingdateFilterSql.$dateFilterSql,
 212                      $params);
 213  
 214          $forecast = array();
 215          for($i=0; $i<$db->num_rows($result); $i++) {
 216              $row = $db->query_result_rowdata($result, $i);
 217              $forecast[] = $row;
 218          }
 219          return $forecast;
 220  
 221      }
 222  
 223      /**
 224       * Function to get relation query for particular module with function name
 225       * @param <record> $recordId
 226       * @param <String> $functionName
 227       * @param Vtiger_Module_Model $relatedModule
 228       * @return <String>
 229       */
 230  	public function getRelationQuery($recordId, $functionName, $relatedModule) {
 231          if ($functionName === 'get_activities') {
 232              $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
 233  
 234              $query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN $userNameSql ELSE vtiger_groups.groupname END AS user_name,
 235                          vtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,
 236                          vtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, vtiger_seactivityrel.crmid AS parent_id,
 237                          CASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status
 238                          FROM vtiger_activity
 239                          INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid
 240                          LEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid
 241                          LEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid
 242                          LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
 243                          LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid
 244                              WHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.activitytype <> 'Emails'
 245                                  AND vtiger_seactivityrel.crmid = ".$recordId;
 246  
 247              $relatedModuleName = $relatedModule->getName();
 248              $query .= $this->getSpecificRelationQuery($relatedModuleName);
 249              $nonAdminQuery = $this->getNonAdminAccessControlQueryForRelation($relatedModuleName);
 250              if ($nonAdminQuery) {
 251                  $query = appendFromClauseToQuery($query, $nonAdminQuery);
 252              }
 253          } else {
 254              $query = parent::getRelationQuery($recordId, $functionName, $relatedModule);
 255          }
 256  
 257          return $query;
 258      }
 259      
 260      /**
 261       * Function returns Potentials Amount for each Sales Stage
 262       * @return <Array>
 263       */
 264  	function getPotentialTotalAmountBySalesStage() {
 265          //$currentUser = Users_Record_Model::getCurrentUserModel();
 266          $db = PearDatabase::getInstance();
 267  
 268          $picklistValues = Vtiger_Util_Helper::getPickListValues('sales_stage');
 269          $data = array();
 270          foreach ($picklistValues as $key => $picklistValue) {
 271              $result = $db->pquery('SELECT SUM(amount) AS amount FROM vtiger_potential
 272                                     INNER JOIN vtiger_crmentity ON vtiger_potential.potentialid = vtiger_crmentity.crmid
 273                                     AND deleted = 0 '.Users_Privileges_Model::getNonAdminAccessControlQuery($this->getName()).' WHERE sales_stage = ?', array($picklistValue));
 274              $num_rows = $db->num_rows($result);
 275              for($i=0; $i<$num_rows; $i++) {
 276                  $values = array();
 277                  $amount = $db->query_result($result, $i, 'amount');
 278                  if(!empty($amount)){
 279                      $values[0] = $db->query_result($result, $i, 'amount');
 280                      $values[1] = vtranslate($picklistValue, $this->getName());
 281                      $data[] = $values;
 282                  }
 283                  
 284              }
 285          }
 286          return $data;
 287      }
 288  
 289      /**
 290       * Function to get list view query for popup window
 291       * @param <String> $sourceModule Parent module
 292       * @param <String> $field parent fieldname
 293       * @param <Integer> $record parent id
 294       * @param <String> $listQuery
 295       * @return <String> Listview Query
 296       */
 297  	public function getQueryByModuleField($sourceModule, $field, $record, $listQuery) {
 298          if (in_array($sourceModule, array('Products', 'Services'))) {
 299              if ($sourceModule === 'Products') {
 300                  $condition = " vtiger_potential.potentialid NOT IN (SELECT crmid FROM vtiger_seproductsrel WHERE productid = '$record')";
 301              } elseif ($sourceModule === 'Services') {
 302                  $condition = " vtiger_potential.potentialid NOT IN (SELECT relcrmid FROM vtiger_crmentityrel WHERE crmid = '$record' UNION SELECT crmid FROM vtiger_crmentityrel WHERE relcrmid = '$record') ";
 303              }
 304  
 305              $pos = stripos($listQuery, 'where');
 306              if ($pos) {
 307                  $split = spliti('where', $listQuery);
 308                  $overRideQuery = $split[0] . ' WHERE ' . $split[1] . ' AND ' . $condition;
 309              } else {
 310                  $overRideQuery = $listQuery . ' WHERE ' . $condition;
 311              }
 312              return $overRideQuery;
 313          }
 314      }
 315  
 316      /**
 317       * Function returns query for module record's search
 318       * @param <String> $searchValue - part of record name (label column of crmentity table)
 319       * @param <Integer> $parentId - parent record id
 320       * @param <String> $parentModule - parent module name
 321       * @return <String> - query
 322       */
 323  	public function getSearchRecordsQuery($searchValue, $parentId=false, $parentModule=false) {
 324          if($parentId && in_array($parentModule, array('Accounts', 'Contacts'))) {
 325              $query = "SELECT * FROM vtiger_crmentity
 326                          INNER JOIN vtiger_potential ON vtiger_potential.potentialid = vtiger_crmentity.crmid
 327                          WHERE deleted = 0 AND vtiger_potential.related_to = $parentId AND label like '%$searchValue%'";
 328              return $query;
 329          }
 330          return parent::getSearchRecordsQuery($parentId, $parentModule);
 331      }
 332  }