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

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