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

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