CRM/Contact/Form/Search/Custom/EmployerListing.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26 */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Contact_Form_Search_Custom_EmployerListing implements CRM_Contact_Form_Search_Interface {
  36
  37   protected $_formValues;
  38
  39   /**
  40    * @param $formValues
  41    */
  42   function __construct(&$formValues) {
  43     $this->_formValues = $formValues;
  44
  45     /**
  46      * Define the columns for search result rows
  47      */
  48     $this->_columns = array(
  49       ts('Contact ID') => 'contact_id',
  50       ts('Individual Name') => 'sort_name',
  51       ts('Individual State') => 'indState',
  52       ts('Employer') => 'employer',
  53       ts('Employer State') => 'empState',
  54     );
  55   }
  56
  57   /**
  58    * @param $form
  59    */
  60   function buildForm(&$form) {
  61
  62     /**
  63      * You can define a custom title for the search form
  64      */
  65     $this->setTitle('List Employers for Individual Contacts');
  66
  67     /**
  68      * Define the search form fields here
  69      */
  70     $form->add('text',
  71       'sort_name',
  72       ts('Individual\'s Name (last, first)')
  73     );
  74
  75     $stateProvince = array('' => ts('- any state/province -')) + CRM_Core_PseudoConstant::stateProvince();
  76     $form->addElement('select', 'state_province_id', ts('Individual\'s Home State'), $stateProvince);
  77
  78     /**
  79      * If you are using the sample template, this array tells the template fields to render
  80      * for the search form.
  81      */
  82     $form->assign('elements', array('sort_name', 'state_province_id'));
  83   }
  84
  85   /*
  86      * Set search form field defaults here.
  87      */
  88   /**
  89    * @return array
  90    */
  91   function setDefaultValues() {
  92     // Setting default search state to California
  93     return array(
  94       'state_province_id' => 1004,
  95     );
  96   }
  97
  98   /**
  99    * Define the smarty template used to layout the search form and results listings.
 100    */
 101   function templateFile() {
 102     return 'CRM/Contact/Form/Search/Custom.tpl';
 103   }
 104
 105   /**
 106    * Construct the search query
 107    */
 108   function all($offset = 0, $rowcount = 0, $sort = NULL,
 109     $includeContactIDs = FALSE, $justIDs = FALSE
 110   ) {
 111     if ($justIDs) {
 112       $select = "cInd.id as contact_id";
 113     }
 114     else {
 115       $select = "
 116             DISTINCT cInd.id as contact_id,
 117             cInd.sort_name as sort_name,
 118             indSP.name as indState,
 119             cEmp.sort_name as employer,
 120             empSP.name as empState
 121             ";
 122     }
 123
 124     $from = $this->from();
 125
 126     $where = $this->where($includeContactIDs);
 127
 128     $having = $this->having();
 129     if ($having) {
 130       $having = " HAVING $having ";
 131     }
 132
 133     // Define GROUP BY here if needed.
 134     $grouping = "";
 135
 136     $sql = "
 137             SELECT $select
 138             FROM   $from
 139             WHERE  $where
 140             $grouping
 141             $having
 142             ";
 143     // Define ORDER BY for query in $sort, with default value
 144     if (!empty($sort)) {
 145       if (is_string($sort)) {
 146         $sort = CRM_Utils_Type::escape($sort, 'String');
 147         $sql .= " ORDER BY $sort ";
 148       }
 149       else {
 150         $sql .= " ORDER BY " . trim($sort->orderBy());
 151       }
 152     }
 153     else {
 154       $sql .= "ORDER BY sort_name asc";
 155     }
 156
 157     /* Uncomment the next 2 lines to see the exact query you're generating */
 158
 159     // CRM_Core_Error::debug('sql',$sql);
 160     // exit();
 161
 162     return $sql;
 163   }
 164
 165   /**
 166    * @return string
 167    */
 168   function from() {
 169     return "
 170             civicrm_relationship cR,
 171             civicrm_contact cInd
 172             LEFT JOIN civicrm_address indAddress ON ( indAddress.contact_id = cInd.id AND
 173             indAddress.is_primary       = 1 )
 174             LEFT JOIN civicrm_state_province indSP ON indSP.id = indAddress.state_province_id,
 175             civicrm_contact cEmp
 176             LEFT JOIN civicrm_address empAddress ON ( empAddress.contact_id = cEmp.id AND
 177             empAddress.is_primary       = 1 )
 178             LEFT JOIN civicrm_state_province empSP ON empSP.id = empAddress.state_province_id
 179             ";
 180   }
 181
 182   /*
 183      * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values
 184      *
 185      */
 186   /**
 187    * @param bool $includeContactIDs
 188    *
 189    * @return string
 190    */
 191   function where($includeContactIDs = FALSE) {
 192     $clauses = array();
 193
 194     // These are required filters for our query.
 195     $clauses[] = "cInd.contact_type = 'Individual'";
 196     $clauses[] = "cR.relationship_type_id = 4";
 197     $clauses[] = "cR.contact_id_a = cInd.id";
 198     $clauses[] = "cR.contact_id_b = cEmp.id";
 199     $clauses[] = "cR.is_active = 1";
 200
 201     // These are conditional filters based on user input
 202     $name = CRM_Utils_Array::value('sort_name',
 203       $this->_formValues
 204     );
 205     if ($name != NULL) {
 206       if (strpos($name, '%') === FALSE) {
 207         $name = "%{$name}%";
 208       }
 209       $clauses[] = "cInd.sort_name LIKE '$name'";
 210     }
 211
 212     $state = CRM_Utils_Array::value('state_province_id',
 213       $this->_formValues
 214     );
 215     if ($state) {
 216       $clauses[] = "indSP.id = $state";
 217     }
 218
 219     if ($includeContactIDs) {
 220       $contactIDs = array();
 221       foreach ($this->_formValues as $id => $value) {
 222         if ($value &&
 223           substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX
 224         ) {
 225           $contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN);
 226         }
 227       }
 228
 229       if (!empty($contactIDs)) {
 230         $contactIDs = implode(', ', $contactIDs);
 231         $clauses[] = "contact.id IN ( $contactIDs )";
 232       }
 233     }
 234
 235     return implode(' AND ', $clauses);
 236   }
 237
 238   /**
 239    * @param bool $includeContactIDs
 240    *
 241    * @return string
 242    */
 243   function having($includeContactIDs = FALSE) {
 244     $clauses = array();
 245     return implode(' AND ', $clauses);
 246   }
 247
 248   /*
 249      * Functions below generally don't need to be modified
 250      */
 251   function count() {
 252     $sql = $this->all();
 253
 254     $dao = CRM_Core_DAO::executeQuery($sql,
 255       CRM_Core_DAO::$_nullArray
 256     );
 257     return $dao->N;
 258   }
 259
 260   /**
 261    * @param int $offset
 262    * @param int $rowcount
 263    * @param null $sort
 264    *
 265    * @return string
 266    */
 267   function contactIDs($offset = 0, $rowcount = 0, $sort = NULL) {
 268     return $this->all($offset, $rowcount, $sort);
 269   }
 270
 271   /**
 272    * @return array
 273    */
 274   function &columns() {
 275     return $this->_columns;
 276   }
 277
 278   /**
 279    * @param $title
 280    */
 281   function setTitle($title) {
 282     if ($title) {
 283       CRM_Utils_System::setTitle($title);
 284     }
 285     else {
 286       CRM_Utils_System::setTitle(ts('Search'));
 287     }
 288   }
 289
 290   /**
 291    * @return null
 292    */
 293   function summary() {
 294     return NULL;
 295   }
 296 }
 297