PriceSet.php 8.75 KB
Newer Older
totten's avatar
totten committed
1 2 3
<?php
/*
 +--------------------------------------------------------------------+
4
 | Copyright CiviCRM LLC. All rights reserved.                        |
totten's avatar
totten committed
5
 |                                                                    |
6 7 8
 | This work is published under the GNU AGPLv3 license with some      |
 | permitted exceptions and without any warranty. For full license    |
 | and copyright information, see https://civicrm.org/licensing       |
totten's avatar
totten committed
9
 +--------------------------------------------------------------------+
10
 */
totten's avatar
totten committed
11 12 13 14

/**
 *
 * @package CRM
15
 * @copyright CiviCRM LLC https://civicrm.org/licensing
totten's avatar
totten committed
16 17 18 19
 */
class CRM_Contact_Form_Search_Custom_PriceSet extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface {

  protected $_eventID = NULL;
20 21
  protected $_aclFrom = NULL;
  protected $_aclWhere = NULL;
22 23 24
  protected $_tableName = NULL;
  public $_permissionedComponent;

25
  /**
eileenmcnaugton's avatar
eileenmcnaugton committed
26 27 28
   * Class constructor.
   *
   * @param array $formValues
29
   */
30
  public function __construct(&$formValues) {
totten's avatar
totten committed
31 32 33 34 35 36 37 38 39 40 41 42
    parent::__construct($formValues);

    $this->_eventID = CRM_Utils_Array::value('event_id',
      $this->_formValues
    );

    $this->setColumns();

    if ($this->_eventID) {
      $this->buildTempTable();
      $this->fillTable();
    }
43 44 45

    // define component access permission needed
    $this->_permissionedComponent = 'CiviEvent';
totten's avatar
totten committed
46 47
  }

48
  public function __destruct() {
totten's avatar
totten committed
49
    /*
50 51 52 53 54
    if ( $this->_eventID ) {
    $sql = "DROP TEMPORARY TABLE {$this->_tableName}";
    CRM_Core_DAO::executeQuery( $sql );
    }
     */
totten's avatar
totten committed
55 56
  }

57
  public function buildTempTable() {
58
    $sql = "id int unsigned NOT NULL AUTO_INCREMENT,
totten's avatar
totten committed
59 60 61 62 63
  contact_id int unsigned NOT NULL,
  participant_id int unsigned NOT NULL,
";

    foreach ($this->_columns as $dontCare => $fieldName) {
64
      if (in_array($fieldName, [
totten's avatar
totten committed
65
        'contact_id',
totten's avatar
totten committed
66 67
        'participant_id',
        'display_name',
68
      ])) {
totten's avatar
totten committed
69 70 71 72 73 74
        continue;
      }
      $sql .= "{$fieldName} int default 0,\n";
    }

    $sql .= "
75 76
      PRIMARY KEY ( id ),
      UNIQUE INDEX unique_participant_id ( participant_id )";
totten's avatar
totten committed
77

78
    $this->_tableName = CRM_Utils_SQL_TempTable::build()->setCategory('priceset')->setMemory()->createWithColumns($sql)->getName();
totten's avatar
totten committed
79 80
  }

81
  public function fillTable() {
totten's avatar
totten committed
82 83 84 85 86 87 88 89
    $sql = "
REPLACE INTO {$this->_tableName}
( contact_id, participant_id )
SELECT c.id, p.id
FROM   civicrm_contact c,
       civicrm_participant p
WHERE  p.contact_id = c.id
  AND  p.is_test    = 0
90
  AND  p.event_id = %1
totten's avatar
totten committed
91 92 93
  AND  p.status_id NOT IN (4,11,12)
  AND  ( c.is_deleted = 0 OR c.is_deleted IS NULL )
";
94
    CRM_Core_DAO::executeQuery($sql, [1 => [$this->_eventID, 'Positive']]);
totten's avatar
totten committed
95 96

    $sql = "
97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116
      SELECT c.id as contact_id,
        p.id as participant_id,
        l.price_field_value_id AS price_field_value_id,
        l.qty
      FROM civicrm_contact c
        INNER JOIN civicrm_participant p
          ON p.contact_id = c.id AND c.is_deleted = 0
        INNER JOIN civicrm_line_item l
          ON p.id = l.entity_id AND l.entity_table ='civicrm_participant'
        INNER JOIN civicrm_price_field_value cpfv
          ON cpfv.id = l.price_field_value_id AND cpfv.is_active = 1
        INNER JOIN civicrm_price_field cpf
          ON cpf.id = l.price_field_id AND cpf.is_active = 1
        INNER JOIN civicrm_price_set cps
          ON cps.id = cpf.price_set_id AND cps.is_active = 1
      WHERE  p.event_id = %1
      ORDER BY c.id, l.price_field_value_id;
    ";

    $dao = CRM_Core_DAO::executeQuery($sql, [1 => [$this->_eventID, 'Positive']]);
totten's avatar
totten committed
117 118

    // first store all the information by option value id
119
    $rows = [];
totten's avatar
totten committed
120 121 122 123
    while ($dao->fetch()) {
      $contactID = $dao->contact_id;
      $participantID = $dao->participant_id;
      if (!isset($rows[$participantID])) {
124
        $rows[$participantID] = [];
totten's avatar
totten committed
125 126 127 128 129 130 131
      }

      $rows[$participantID][] = "price_field_{$dao->price_field_value_id} = {$dao->qty}";
    }

    foreach (array_keys($rows) as $participantID) {
      $values = implode(',', $rows[$participantID]);
132 133
      if ($values) {
        $sql = "
totten's avatar
totten committed
134 135 136 137
UPDATE {$this->_tableName}
SET $values
WHERE participant_id = $participantID;
";
138 139
        CRM_Core_DAO::executeQuery($sql);
      }
totten's avatar
totten committed
140 141 142
    }
  }

143
  /**
colemanw's avatar
colemanw committed
144
   * @param int $eventID
145 146 147
   *
   * @return Object
   */
148
  public function priceSetDAO($eventID = NULL) {
totten's avatar
totten committed
149 150 151 152 153 154 155 156 157 158 159 160 161

    // get all the events that have a price set associated with it
    $sql = "
SELECT e.id    as id,
       e.title as title,
       p.price_set_id as price_set_id
FROM   civicrm_event      e,
       civicrm_price_set_entity  p

WHERE  p.entity_table = 'civicrm_event'
AND    p.entity_id    = e.id
";

162
    $params = [];
totten's avatar
totten committed
163
    if ($eventID) {
164
      $params[1] = [$eventID, 'Integer'];
totten's avatar
totten committed
165 166 167 168 169 170 171 172 173
      $sql .= " AND e.id = $eventID";
    }

    $dao = CRM_Core_DAO::executeQuery($sql,
      $params
    );
    return $dao;
  }

174
  /**
175
   * @param CRM_Core_Form $form
176 177 178
   *
   * @throws Exception
   */
179
  public function buildForm(&$form) {
totten's avatar
totten committed
180 181
    $dao = $this->priceSetDAO();

182
    $event = [];
totten's avatar
totten committed
183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206
    while ($dao->fetch()) {
      $event[$dao->id] = $dao->title;
    }

    if (empty($event)) {
      CRM_Core_Error::fatal(ts('There are no events with Price Sets'));
    }

    $form->add('select',
      'event_id',
      ts('Event'),
      $event,
      TRUE
    );

    /**
     * You can define a custom title for the search form
     */
    $this->setTitle('Price Set Export');

    /**
     * if you are using the standard template, this array tells the template what elements
     * are part of the search criteria
     */
207
    $form->assign('elements', ['event_id']);
totten's avatar
totten committed
208 209
  }

210
  public function setColumns() {
211
    $this->_columns = [
212
      ts('Contact ID') => 'contact_id',
213
      ts('Participant ID') => 'participant_id',
totten's avatar
totten committed
214
      ts('Name') => 'display_name',
215
    ];
totten's avatar
totten committed
216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231

    if (!$this->_eventID) {
      return;
    }

    // for the selected event, find the price set and all the columns associated with it.
    // create a column for each field and option group within it
    $dao = $this->priceSetDAO($this->_formValues['event_id']);

    if ($dao->fetch() &&
      !$dao->price_set_id
    ) {
      CRM_Core_Error::fatal(ts('There are no events with Price Sets'));
    }

    // get all the fields and all the option values associated with it
232
    $priceSet = CRM_Price_BAO_PriceSet::getSetDetail($dao->price_set_id);
totten's avatar
totten committed
233 234 235 236
    if (is_array($priceSet[$dao->price_set_id])) {
      foreach ($priceSet[$dao->price_set_id]['fields'] as $key => $value) {
        if (is_array($value['options'])) {
          foreach ($value['options'] as $oKey => $oValue) {
237
            $columnHeader = $value['label'] ?? NULL;
totten's avatar
totten committed
238 239 240 241 242 243 244 245 246 247 248
            if (CRM_Utils_Array::value('html_type', $value) != 'Text') {
              $columnHeader .= ' - ' . $oValue['label'];
            }

            $this->_columns[$columnHeader] = "price_field_{$oValue['id']}";
          }
        }
      }
    }
  }

249 250 251
  /**
   * @return null
   */
252
  public function summary() {
totten's avatar
totten committed
253 254 255
    return NULL;
  }

256 257 258 259 260 261 262 263 264
  /**
   * @param int $offset
   * @param int $rowcount
   * @param null $sort
   * @param bool $includeContactIDs
   * @param bool $justIDs
   *
   * @return string
   */
yashodha's avatar
yashodha committed
265
  public function all(
totten's avatar
totten committed
266
    $offset = 0, $rowcount = 0, $sort = NULL,
totten's avatar
totten committed
267 268 269 270 271 272 273 274 275 276 277
    $includeContactIDs = FALSE, $justIDs = FALSE
  ) {
    if ($justIDs) {
      $selectClause = "contact_a.id as contact_id";
    }
    else {
      $selectClause = "
contact_a.id             as contact_id  ,
contact_a.display_name   as display_name";

      foreach ($this->_columns as $dontCare => $fieldName) {
278
        if (in_array($fieldName, [
totten's avatar
totten committed
279 280
          'contact_id',
          'display_name',
281
        ])) {
totten's avatar
totten committed
282 283 284 285 286 287 288 289 290 291 292 293
          continue;
        }
        $selectClause .= ",\ntempTable.{$fieldName} as {$fieldName}";
      }
    }

    return $this->sql($selectClause,
      $offset, $rowcount, $sort,
      $includeContactIDs, NULL
    );
  }

294 295 296
  /**
   * @return string
   */
297
  public function from() {
298 299
    $this->buildACLClause('contact_a');
    $from = "
totten's avatar
totten committed
300
FROM       civicrm_contact contact_a
301
INNER JOIN {$this->_tableName} tempTable ON ( tempTable.contact_id = contact_a.id ) {$this->_aclFrom}
totten's avatar
totten committed
302
";
303
    return $from;
totten's avatar
totten committed
304 305
  }

306 307 308 309 310
  /**
   * @param bool $includeContactIDs
   *
   * @return string
   */
311
  public function where($includeContactIDs = FALSE) {
312 313
    $where = ' ( 1 ) ';
    if ($this->_aclWhere) {
Seamus Lee's avatar
Seamus Lee committed
314
      $where .= " AND {$this->_aclWhere} ";
315 316
    }
    return $where;
totten's avatar
totten committed
317 318
  }

319 320 321
  /**
   * @return string
   */
322
  public function templateFile() {
totten's avatar
totten committed
323 324 325
    return 'CRM/Contact/Form/Search/Custom.tpl';
  }

326 327 328
  /**
   * @param $row
   */
totten's avatar
totten committed
329 330
  public function alterRow(&$row) {
  }
totten's avatar
totten committed
331

332 333 334
  /**
   * @param $title
   */
335
  public function setTitle($title) {
totten's avatar
totten committed
336 337 338 339 340 341 342
    if ($title) {
      CRM_Utils_System::setTitle($title);
    }
    else {
      CRM_Utils_System::setTitle(ts('Export Price Set Info for an Event'));
    }
  }
343

344 345 346 347 348 349 350
  /**
   * @param string $tableAlias
   */
  public function buildACLClause($tableAlias = 'contact') {
    list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
  }

totten's avatar
totten committed
351
}