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

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.3                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2013                                |
   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-2013
  32  * $Id$
  33  *
  34  */
  35 class CRM_Contact_Form_Search_Custom_EmployerListing implements CRM_Contact_Form_Search_Interface {
  36
  37   protected $_formValues;
  38
  39   function __construct(&$formValues) {
  40     $this->_formValues = $formValues;
  41
  42     /**
  43      * Define the columns for search result rows
  44      */
  45     $this->_columns = array(
  46       ts('Contact Id') => 'contact_id',
  47       ts('Individual Name') => 'sort_name',
  48       ts('Individual State') => 'indState',
  49       ts('Employer') => 'employer',
  50       ts('Employer State') => 'empState',
  51     );
  52   }
  53
  54   function buildForm(&$form) {
  55
  56     /**
  57      * You can define a custom title for the search form
  58      */
  59     $this->setTitle('List Employers for Individual Contacts');
  60
  61     /**
  62      * Define the search form fields here
  63      */
  64     $form->add('text',
  65       'sort_name',
  66       ts('Individual\'s Name (last, first)')
  67     );
  68
  69     $stateProvince = array('' => ts('- any state/province -')) + CRM_Core_PseudoConstant::stateProvince();
  70     $form->addElement('select', 'state_province_id', ts('Individual\'s Home State'), $stateProvince);
  71
  72     /**
  73      * If you are using the sample template, this array tells the template fields to render
  74      * for the search form.
  75      */
  76     $form->assign('elements', array('sort_name', 'state_province_id'));
  77   }
  78
  79   /*
  80      * Set search form field defaults here.
  81      */
  82   function setDefaultValues() {
  83     // Setting default search state to California
  84     return array(
  85       'state_province_id' => 1004,
  86     );
  87   }
  88
  89   /**
  90    * Define the smarty template used to layout the search form and results listings.
  91    */
  92   function templateFile() {
  93     return 'CRM/Contact/Form/Search/Custom.tpl';
  94   }
  95
  96   /**
  97    * Construct the search query
  98    */
  99   function all($offset = 0, $rowcount = 0, $sort = NULL,
 100     $includeContactIDs = FALSE, $justIDs = FALSE
 101   ) {
 102     if ($justIDs) {
 103       $select = "cInd.id as contact_id";
 104     }
 105     else {
 106       $select = "
 107             DISTINCT cInd.id as contact_id,
 108             cInd.sort_name as sort_name,
 109             indSP.name as indState,
 110             cEmp.sort_name as employer,
 111             empSP.name as empState
 112             ";
 113     }
 114
 115     $from = $this->from();
 116
 117     $where = $this->where($includeContactIDs);
 118
 119     $having = $this->having();
 120     if ($having) {
 121       $having = " HAVING $having ";
 122     }
 123
 124     // Define GROUP BY here if needed.
 125     $grouping = "";
 126
 127     $sql = "
 128             SELECT $select
 129             FROM   $from
 130             WHERE  $where
 131             $grouping
 132             $having
 133             ";
 134     // Define ORDER BY for query in $sort, with default value
 135     if (!empty($sort)) {
 136       if (is_string($sort)) {
 137         $sql .= " ORDER BY $sort ";
 138       }
 139       else {
 140         $sql .= " ORDER BY " . trim($sort->orderBy());
 141       }
 142     }
 143     else {
 144       $sql .= "ORDER BY sort_name asc";
 145     }
 146
 147     /* Uncomment the next 2 lines to see the exact query you're generating */
 148
 149     // CRM_Core_Error::debug('sql',$sql);
 150     // exit();
 151
 152     return $sql;
 153   }
 154
 155   function from() {
 156     return "
 157             civicrm_relationship cR,
 158             civicrm_contact cInd
 159             LEFT JOIN civicrm_address indAddress ON ( indAddress.contact_id = cInd.id AND
 160             indAddress.is_primary       = 1 )
 161             LEFT JOIN civicrm_state_province indSP ON indSP.id = indAddress.state_province_id,
 162             civicrm_contact cEmp
 163             LEFT JOIN civicrm_address empAddress ON ( empAddress.contact_id = cEmp.id AND
 164             empAddress.is_primary       = 1 )
 165             LEFT JOIN civicrm_state_province empSP ON empSP.id = empAddress.state_province_id
 166             ";
 167   }
 168
 169   /*
 170      * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values
 171      *
 172      */
 173   function where($includeContactIDs = FALSE) {
 174     $clauses = array();
 175
 176     // These are required filters for our query.
 177     $clauses[] = "cInd.contact_type = 'Individual'";
 178     $clauses[] = "cR.relationship_type_id = 4";
 179     $clauses[] = "cR.contact_id_a = cInd.id";
 180     $clauses[] = "cR.contact_id_b = cEmp.id";
 181     $clauses[] = "cR.is_active = 1";
 182
 183     // These are conditional filters based on user input
 184     $name = CRM_Utils_Array::value('sort_name',
 185       $this->_formValues
 186     );
 187     if ($name != NULL) {
 188       if (strpos($name, '%') === FALSE) {
 189         $name = "%{$name}%";
 190       }
 191       $clauses[] = "cInd.sort_name LIKE '$name'";
 192     }
 193
 194     $state = CRM_Utils_Array::value('state_province_id',
 195       $this->_formValues
 196     );
 197     if ($state) {
 198       $clauses[] = "indSP.id = $state";
 199     }
 200
 201     if ($includeContactIDs) {
 202       $contactIDs = array();
 203       foreach ($this->_formValues as $id => $value) {
 204         if ($value &&
 205           substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX
 206         ) {
 207           $contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN);
 208         }
 209       }
 210
 211       if (!empty($contactIDs)) {
 212         $contactIDs = implode(', ', $contactIDs);
 213         $clauses[] = "contact.id IN ( $contactIDs )";
 214       }
 215     }
 216
 217     return implode(' AND ', $clauses);
 218   }
 219
 220   function having($includeContactIDs = FALSE) {
 221     $clauses = array();
 222     return implode(' AND ', $clauses);
 223   }
 224
 225   /*
 226      * Functions below generally don't need to be modified
 227      */
 228   function count() {
 229     $sql = $this->all();
 230
 231     $dao = CRM_Core_DAO::executeQuery($sql,
 232       CRM_Core_DAO::$_nullArray
 233     );
 234     return $dao->N;
 235   }
 236
 237   function contactIDs($offset = 0, $rowcount = 0, $sort = NULL) {
 238     return $this->all($offset, $rowcount, $sort);
 239   }
 240
 241   function &columns() {
 242     return $this->_columns;
 243   }
 244
 245   function setTitle($title) {
 246     if ($title) {
 247       CRM_Utils_System::setTitle($title);
 248     }
 249     else {
 250       CRM_Utils_System::setTitle(ts('Search'));
 251     }
 252   }
 253
 254   function summary() {
 255     return NULL;
 256   }
 257 }
 258