CRM/Contact/Form/Search/Custom/PriceSet.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_PriceSet extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface {
  36
  37   protected $_eventID = NULL;
  38
  39   protected $_tableName = NULL;
  40   public $_permissionedComponent;
  41
  42   /**
  43    * @param $formValues
  44    */
  45   function __construct(&$formValues) {
  46     parent::__construct($formValues);
  47
  48     $this->_eventID = CRM_Utils_Array::value('event_id',
  49       $this->_formValues
  50     );
  51
  52     $this->setColumns();
  53
  54     if ($this->_eventID) {
  55       $this->buildTempTable();
  56       $this->fillTable();
  57     }
  58
  59     // define component access permission needed
  60     $this->_permissionedComponent = 'CiviEvent';
  61   }
  62
  63   function __destruct() {
  64     /*
  65         if ( $this->_eventID ) {
  66             $sql = "DROP TEMPORARY TABLE {$this->_tableName}";
  67             CRM_Core_DAO::executeQuery( $sql );
  68         }
  69         */
  70   }
  71
  72   function buildTempTable() {
  73     $randomNum        = md5(uniqid());
  74     $this->_tableName = "civicrm_temp_custom_{$randomNum}";
  75     $sql              = "
  76 CREATE TEMPORARY TABLE {$this->_tableName} (
  77   id int unsigned NOT NULL AUTO_INCREMENT,
  78   contact_id int unsigned NOT NULL,
  79   participant_id int unsigned NOT NULL,
  80 ";
  81
  82     foreach ($this->_columns as $dontCare => $fieldName) {
  83       if (in_array($fieldName, array(
  84         'contact_id',
  85             'participant_id',
  86             'display_name',
  87           ))) {
  88         continue;
  89       }
  90       $sql .= "{$fieldName} int default 0,\n";
  91     }
  92
  93     $sql .= "
  94 PRIMARY KEY ( id ),
  95 UNIQUE INDEX unique_participant_id ( participant_id )
  96 ) ENGINE=HEAP
  97 ";
  98
  99     CRM_Core_DAO::executeQuery($sql);
 100   }
 101
 102   function fillTable() {
 103     $sql = "
 104 REPLACE INTO {$this->_tableName}
 105 ( contact_id, participant_id )
 106 SELECT c.id, p.id
 107 FROM   civicrm_contact c,
 108        civicrm_participant p
 109 WHERE  p.contact_id = c.id
 110   AND  p.is_test    = 0
 111   AND  p.event_id = {$this->_eventID}
 112   AND  p.status_id NOT IN (4,11,12)
 113   AND  ( c.is_deleted = 0 OR c.is_deleted IS NULL )
 114 ";
 115     CRM_Core_DAO::executeQuery($sql);
 116
 117     $sql = "
 118 SELECT c.id as contact_id,
 119        p.id as participant_id,
 120        l.price_field_value_id as price_field_value_id,
 121        l.qty
 122 FROM   civicrm_contact c,
 123        civicrm_participant  p,
 124        civicrm_line_item    l
 125 WHERE  c.id = p.contact_id
 126 AND    p.event_id = {$this->_eventID}
 127 AND    p.id = l.entity_id
 128 AND    l.entity_table ='civicrm_participant'
 129 ORDER BY c.id, l.price_field_value_id;
 130 ";
 131
 132     $dao = CRM_Core_DAO::executeQuery($sql);
 133
 134     // first store all the information by option value id
 135     $rows = array();
 136     while ($dao->fetch()) {
 137       $contactID = $dao->contact_id;
 138       $participantID = $dao->participant_id;
 139       if (!isset($rows[$participantID])) {
 140         $rows[$participantID] = array();
 141       }
 142
 143       $rows[$participantID][] = "price_field_{$dao->price_field_value_id} = {$dao->qty}";
 144     }
 145
 146     foreach (array_keys($rows) as $participantID) {
 147       $values = implode(',', $rows[$participantID]);
 148       $sql = "
 149 UPDATE {$this->_tableName}
 150 SET $values
 151 WHERE participant_id = $participantID;
 152 ";
 153       CRM_Core_DAO::executeQuery($sql);
 154     }
 155   }
 156
 157   /**
 158    * @param null $eventID
 159    *
 160    * @return Object
 161    */
 162   function priceSetDAO($eventID = NULL) {
 163
 164     // get all the events that have a price set associated with it
 165     $sql = "
 166 SELECT e.id    as id,
 167        e.title as title,
 168        p.price_set_id as price_set_id
 169 FROM   civicrm_event      e,
 170        civicrm_price_set_entity  p
 171
 172 WHERE  p.entity_table = 'civicrm_event'
 173 AND    p.entity_id    = e.id
 174 ";
 175
 176     $params = array();
 177     if ($eventID) {
 178       $params[1] = array($eventID, 'Integer');
 179       $sql .= " AND e.id = $eventID";
 180     }
 181
 182     $dao = CRM_Core_DAO::executeQuery($sql,
 183       $params
 184     );
 185     return $dao;
 186   }
 187
 188   /**
 189    * @param $form
 190    *
 191    * @throws Exception
 192    */
 193   function buildForm(&$form) {
 194     $dao = $this->priceSetDAO();
 195
 196     $event = array();
 197     while ($dao->fetch()) {
 198       $event[$dao->id] = $dao->title;
 199     }
 200
 201     if (empty($event)) {
 202       CRM_Core_Error::fatal(ts('There are no events with Price Sets'));
 203     }
 204
 205     $form->add('select',
 206       'event_id',
 207       ts('Event'),
 208       $event,
 209       TRUE
 210     );
 211
 212     /**
 213      * You can define a custom title for the search form
 214      */
 215     $this->setTitle('Price Set Export');
 216
 217     /**
 218      * if you are using the standard template, this array tells the template what elements
 219      * are part of the search criteria
 220      */
 221     $form->assign('elements', array('event_id'));
 222   }
 223
 224   function setColumns() {
 225     $this->_columns = array(
 226       ts('Contact ID') => 'contact_id',
 227       ts('Participant Id') => 'participant_id',
 228       ts('Name') => 'display_name',
 229     );
 230
 231     if (!$this->_eventID) {
 232       return;
 233     }
 234
 235     // for the selected event, find the price set and all the columns associated with it.
 236     // create a column for each field and option group within it
 237     $dao = $this->priceSetDAO($this->_formValues['event_id']);
 238
 239     if ($dao->fetch() &&
 240       !$dao->price_set_id
 241     ) {
 242       CRM_Core_Error::fatal(ts('There are no events with Price Sets'));
 243     }
 244
 245     // get all the fields and all the option values associated with it
 246     $priceSet = CRM_Price_BAO_PriceSet::getSetDetail($dao->price_set_id);
 247     if (is_array($priceSet[$dao->price_set_id])) {
 248       foreach ($priceSet[$dao->price_set_id]['fields'] as $key => $value) {
 249         if (is_array($value['options'])) {
 250           foreach ($value['options'] as $oKey => $oValue) {
 251             $columnHeader = CRM_Utils_Array::value('label', $value);
 252             if (CRM_Utils_Array::value('html_type', $value) != 'Text') {
 253               $columnHeader .= ' - ' . $oValue['label'];
 254             }
 255
 256             $this->_columns[$columnHeader] = "price_field_{$oValue['id']}";
 257           }
 258         }
 259       }
 260     }
 261   }
 262
 263   /**
 264    * @return null
 265    */
 266   function summary() {
 267     return NULL;
 268   }
 269
 270   /**
 271    * @param int $offset
 272    * @param int $rowcount
 273    * @param null $sort
 274    * @param bool $includeContactIDs
 275    * @param bool $justIDs
 276    *
 277    * @return string
 278    */
 279   function all($offset = 0, $rowcount = 0, $sort = NULL,
 280     $includeContactIDs = FALSE, $justIDs = FALSE
 281   ) {
 282     if ($justIDs) {
 283       $selectClause = "contact_a.id as contact_id";
 284     }
 285     else {
 286       $selectClause = "
 287 contact_a.id             as contact_id  ,
 288 contact_a.display_name   as display_name";
 289
 290       foreach ($this->_columns as $dontCare => $fieldName) {
 291         if (in_array($fieldName, array(
 292               'contact_id',
 293               'display_name',
 294             ))) {
 295           continue;
 296         }
 297         $selectClause .= ",\ntempTable.{$fieldName} as {$fieldName}";
 298       }
 299     }
 300
 301     return $this->sql($selectClause,
 302       $offset, $rowcount, $sort,
 303       $includeContactIDs, NULL
 304     );
 305   }
 306
 307   /**
 308    * @return string
 309    */
 310   function from() {
 311     return "
 312 FROM       civicrm_contact contact_a
 313 INNER JOIN {$this->_tableName} tempTable ON ( tempTable.contact_id = contact_a.id )
 314 ";
 315   }
 316
 317   /**
 318    * @param bool $includeContactIDs
 319    *
 320    * @return string
 321    */
 322   function where($includeContactIDs = FALSE) {
 323     return ' ( 1 ) ';
 324   }
 325
 326   /**
 327    * @return string
 328    */
 329   function templateFile() {
 330     return 'CRM/Contact/Form/Search/Custom.tpl';
 331   }
 332
 333   /**
 334    * @return array
 335    */
 336   function setDefaultValues() {
 337     return array();
 338   }
 339
 340   /**
 341    * @param $row
 342    */
 343   function alterRow(&$row) {}
 344
 345   /**
 346    * @param $title
 347    */
 348   function setTitle($title) {
 349     if ($title) {
 350       CRM_Utils_System::setTitle($title);
 351     }
 352     else {
 353       CRM_Utils_System::setTitle(ts('Export Price Set Info for an Event'));
 354     }
 355   }
 356 }
 357