cividiscount failing to retrieve date based priceset

[wellebee]

I am tracking down a problem with the cividiscount extension. An 'early registration' priceset is failing to be retrieved because the code is expecting a record in civicrm_price_set_entity, and there is none. The specific query is:

SELECT    pfv.id as item_id,
          pfv.label as item_label,
          pf.label as pf_label,
          pfv.membership_type_id as membership_type_id,
          ps.title as ps_label
FROM      civicrm_price_field_value as pfv
LEFT JOIN civicrm_price_field as pf on (pf.id = pfv.price_field_id)
LEFT JOIN civicrm_price_set as ps on (ps.id = pf.price_set_id AND ps.is_active = 1)
INNER JOIN civicrm_price_set_entity as pse on (ps.id = pse.price_set_id)
WHERE ps.id = 12345;

Changing the last join from an inner to a left join seems to return the priceset correctly. However, I can't tell if this is a cividiscount bug (left join should be used) or a CiviEvent bug (the priceset should in fact have a record in civicrm_price_set_entity).

Is an early registration priceset required to have civicrm_price_set_entity record?

[wellebee]

In https://issues.civicrm.org/jira/browse/CRM-15013 David Greenberg wrote:

Keith - Current behavior is that the date-based discount price sets (created via Manage Events > Configure > Fees) are linked to the event in the civicrm_discount table rather than civicrm_price_set_entity).

I'd recommend continuing the discussion on the forums - would help to understand what you're trying to achieve (sounds like it MAY required some additional functionality in the CiviDiscount extension - assuming you're using the latest 4.4 compatible code which will be tagged as a 2.6 release soon, but is available here: https://github.com/dlobo/org.civicrm.module.cividiscount/tree/4.4)

[wellebee]

Using latest pull from https://github.com/dlobo/org.civicrm.module.cividiscount/tree/4.4

cividiscount.php line 389 reads:
$priceSetInfo = CRM_CiviDiscount_Utils::getPriceSetsInfo($psid);

This line fails to retrieve a value when $psid refers to a date based priceset. The result is that any events offering a date based (e.g., 'early bird') price may not be further discounted by any cividiscount code (during the period the date price is valid).

We believe this is a regression bug. Events with date based pricing worked correctly in CiviCRM 4.2, (cividiscount unversioned).

As I stated above, the problem seem to reside in CRM_CiviDiscount_Utils::getPriceSetsInfo(), in the SQL query. The inner join with civicrm_price_set_entity is failing because (apparently) date based price sets are not required to have a corresponding record in civicrm_price_set_entity.

SELECT    pfv.id as item_id,
          pfv.label as item_label,
          pf.label as pf_label,
          pfv.membership_type_id as membership_type_id,
          ps.title as ps_label
FROM      civicrm_price_field_value as pfv
LEFT JOIN civicrm_price_field as pf on (pf.id = pfv.price_field_id)
LEFT JOIN civicrm_price_set as ps on (ps.id = pf.price_set_id AND ps.is_active = 1)
INNER JOIN civicrm_price_set_entity as pse on (ps.id = pse.price_set_id)
WHERE ps.id = 12345;

This join was added in Commit 0b8041336b9c4c24ca845ec3466991945a31b098 'CRM-13311 fix'.

[Dave Greenberg]

I've reopened https://issues.civicrm.org/jira/browse/CRM-13311

Keith - if you can provide a suggested patch which replaces the INNER JOIN still limits results to price sets 'in use' - and verify that things are working properly with your data - that would be helpful. Please comment on that issue. Note that we would still want to exclude inactive price sets and price sets not linked to any event.

[wellebee]

Thanks Dave. Followups to https://issues.civicrm.org/jira/browse/CRM-13311.