Author Topic: Extreme event reporting, with errors (Read 566 times)

wftl · February 10, 2013, 06:53:12 pm

Hello everyone,

I'm trying to generate a CSV report of event registrations and participants emailed to a customer. The query I'm using (with a couple of variations such as limiting by date) is below.

SELECT C1.first_name, C1.last_name, C2.display_name AS 'registered_by', E1.email, F1.phone, P1.register_date, P1.source, S1.name 'status_id' FROM civicrm_participant AS P1 LEFT JOIN civicrm_contact AS C1 ON C1.id = P1.contact_id LEFT OUTER JOIN civicrm_email AS E1 ON C1.id = E1.contact_id LEFT OUTER JOIN civicrm_participant_status_type AS S1 ON P1.status_id = S1.id LEFT OUTER JOIN civicrm_participant AS P2 ON P1.registered_by_id = P2.id LEFT OUTER JOIN civicrm_contact AS C2 ON P2.contact_id = C2.id LEFT OUTER JOIN civicrm_phone AS F1 ON C1.id = F1.contact_id LEFT OUTER JOIN civicrm_address AS A1 ON C1.id = A1.contact_id LEFT OUTER JOIN civicrm_state_province AS S2 ON A1.state_province_id = S2.id LEFT OUTER JOIN civicrm_country AS C3 on A1.country_id = C3.id WHERE P1.status_id IN (1,5) GROUP BY P1.id ORDER BY P1.status_id, P1.register_date;

It seems to work and it seems to give me what I want, but there's a problem. Many of the first_name and last_name fields just seem to be filled with whatever the same name appearing, seemingly randomly, over and over again.

Am I doing something terribly wrong here or am I running across some kind of MySQL bug? Please help, if you can.

--
Marcel Gagné
Note: This massagee wos nat speel or gramer-checkered.

xavier · February 11, 2013, 01:56:41 am

Hi,

99.99% of the my sql query doesn't run, it must be a bug in mysql, it's a problem in your query. For the reminding cases, the jury is still out, but the odds aren't good

Haven't looked at your request too much, but why are you using OUTER join? seems to be inner or left inner you want

X+

wftl · February 11, 2013, 06:32:41 am

([ insert appropriate smiley here ])

Point taken. Trying new variations on report. Weirdness stems from the fact that _most_ of it works. I would expect it all to fail, not work most of the time.

-- Marcel

wftl · February 11, 2013, 01:34:45 pm

Slowly getting there. The new version of the mail merge report is looking better, with sane looking data, but there are still problems and I'd appreciate some assistance if you can spare it. Here's the report.

SELECT C1.last_name, C1.first_name, C2.display_name AS 'registered_by', E1.email, F1.phone, A1.street_address, A1.city, S2.abbreviation, A1.postal_code, A1.postal_code_suffix, C3.name, P1.register_date, P1.source, P1.fee_level, P1.is_pay_later, S1.name 'status_id', P1.fee_amount FROM civicrm_participant AS P1 INNER JOIN civicrm_contact AS C1 ON C1.id = P1.contact_id INNER JOIN civicrm_email AS E1 ON C1.id = E1.contact_id INNER JOIN civicrm_participant_status_type AS S1 ON P1.status_id = S1.id INNER JOIN civicrm_participant AS P2 ON P1.registered_by_id = P2.id INNER JOIN civicrm_contact AS C2 ON P2.contact_id = C2.id INNER JOIN civicrm_phone AS F1 ON C1.id = F1.contact_id INNER JOIN civicrm_address AS A1 ON C1.id = A1.contact_id INNER JOIN civicrm_state_province AS S2 ON A1.state_province_id = S2.id INNER JOIN civicrm_country AS C3 on A1.country_id = C3.id WHERE P1.status_id IN (1,5) GROUP BY P1.id ORDER BY P1.status_id, P1.register_date;

I get the registered patrons, but not the people who did the registering (the first contact, if you will). So Person A will register B and C and I can see B and C in the report, but not A.

-- Marcel

CiviCRM Community Forums (archive)

News:

Author Topic: Extreme event reporting, with errors (Read 566 times)

wftl

Extreme event reporting, with errors

xavier

Re: Extreme event reporting, with errors

wftl

Re: Extreme event reporting, with errors

wftl

Re: Extreme event reporting, with errors