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