DTH Development

Script for SQL2Excel extension 13 years 4 months ago #14295

divalsafety Topic Author Offline Senior Boarder Posts: 45 Thank you received: 0	I'm looking for a script to use for generating downloadable reports using the SQL2Excel extension. Does anyone have one they could possibly post and share? Thanks in advance for your time! Check this box to be notified of replies to this topic. Note: BBcode and smileys are still usable.
	Please Log in or Create an account to join the conversation.

Script for SQL2Excel extension 13 years 4 months ago #14298

suyog
Offline
Senior Boarder
Posts: 41
Thank you received: 0

SQL2Excel is a great extension I have been using this one for few months.

Here is a sql statement that i have used to query individual registration only based on event

SELECT M.user_id,Y.title AS EVENT,
MAX(CASE WHEN M.field_id = 48 THEN value END) AS FIRSTNAME,
MAX(CASE WHEN M.field_id = 49 THEN value END) AS LASTNAME,
MAX(CASE WHEN M.field_id = 58 THEN value END) AS ORGANIZATION,
MAX(CASE WHEN M.field_id = 50 THEN value END) AS ADDRESS,
MAX(CASE WHEN M.field_id = 51 THEN value END) AS ADDRESS2,
MAX(CASE WHEN M.field_id = 52 THEN value END) AS CITY,
MAX(CASE WHEN M.field_id = 53 THEN value END) AS STATE,
MAX(CASE WHEN M.field_id = 54 THEN value END) AS ZIP,
MAX(CASE WHEN M.field_id = 56 THEN value END) AS Email,
MAX(CASE WHEN M.field_id = 57 THEN value END) AS Phone
FROM jos_dtregister_user_field_values AS M
INNER JOIN jos_dtregister_user AS Z ON Z.userID = M.user_id
INNER JOIN jos_dtregister_group_event AS Y ON slabId = Z.eventId
WHERE Z.type ="I" AND
Y.slabId = '{event}'
GROUP BY M.user_id

Please Log in or Create an account to join the conversation.

Script for SQL2Excel extension 13 years 4 months ago #14302

sacwebmaster
Offline
Junior Boarder
Posts: 24
Thank you received: 0

This is the script that we use. It produces a report thats almost the same as the DTREgister CSV report.

The constructs

The GROUP_CONCAT(IF(m.field_id = 13, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(m.value AS UNSIGNED)+1),'|',-1),NULL)) AS Class,

are used to return values from select lists or radio buttons.

You'll need to poke around in the #_dtregister_fields table to find your field ids.

The full code is below:

(SELECT TRIM(e.title) AS Tournament, u.userId AS UserID, u.type AS RegType, IF(u.memtot,"","") AS GroupSize, m.member_id AS MemberID, IF(p.fee,"","") AS Fee, IF(p.paid_amount,"","") AS Paid, IF(p.payment_method,"","") AS PayType,
	 GROUP_CONCAT(IF(m.field_id = 2, m.value, NULL)) AS FirstName,
	 GROUP_CONCAT(IF(m.field_id = 3, m.value, NULL)) AS LastName,
	 GROUP_CONCAT(IF(m.field_id = 10, m.value, NULL)) AS Email,
	 GROUP_CONCAT(IF(m.field_id = 11, m.value, NULL)) AS Phone,
	 GROUP_CONCAT(IF(m.field_id = 13, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(m.value AS UNSIGNED)+1),'|',-1),NULL)) AS Class,
	 GROUP_CONCAT(IF(m.field_id = 14, m.value, NULL)) AS YearofBirth, 
	 GROUP_CONCAT(IF(m.field_id BETWEEN 15 AND 18, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(m.value AS UNSIGNED)+1),'|',-1),NULL)) AS Category,
	 GROUP_CONCAT(IF(m.field_id = 19, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(m.value AS UNSIGNED)+1),'|',-1),NULL)) AS Division,
	 GROUP_CONCAT(IF(m.field_id = 20, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(m.value AS UNSIGNED)+1),'|',-1), NULL)) AS Membership,
	 GROUP_CONCAT(IF(m.field_id BETWEEN 24 AND 25, m.value,NULL)) AS College,
  	 GROUP_CONCAT(IF(m.field_id = 8, m.value, NULL)) AS ZipCode,
	 u.register_date AS RegDate,
	 u.confirmNum AS Confirm#
	 FROM cal_dtregister_user u 
	 LEFT JOIN cal_dtregister_group_member g ON g.groupUserId = u.userId  
	 INNER JOIN cal_dtregister_member_field_values m ON m.member_id = g.groupMemberId
	 INNER JOIN cal_dtregister_fee p ON p.user_id = u.userId
	 INNER JOIN cal_dtregister_group_event e  ON e.slabId = u.eventID
	 INNER JOIN cal_dtregister_fields f  ON m.field_id = f.id
	 WHERE u.eventID = 4
	 GROUP BY m.member_id)
UNION
 (Select TRIM(e.title) AS Tournament, u.userId AS UserID, u.type AS RegType, u.memtot AS GroupSize, IF(u.type, "", "") AS MemberID, p.fee AS Fee, p.paid_amount AS Paid, 
	CASE 
		WHEN p.payment_method = 2 THEN "Mail" 
		WHEN p.payment_method = 3 THEN "Phone" 
		WHEN p.payment_method = 4 THEN "On Site" 
		ELSE p.payment_method 
	END AS PayType,
	 GROUP_CONCAT(IF(v.field_id = 2, v.value, NULL)) AS FirstName,
	 GROUP_CONCAT(IF(v.field_id = 3, v.value, NULL)) AS LastName,
	 GROUP_CONCAT(IF(v.field_id = 10, v.value, NULL)) AS Email,
	 GROUP_CONCAT(IF(v.field_id = 11, v.value, NULL)) AS Phone,
	 GROUP_CONCAT(IF(v.field_id = 13, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(v.value AS UNSIGNED)+1),'|',-1),NULL)) AS Class,
	 GROUP_CONCAT(IF(v.field_id = 14, v.value, NULL)) AS YearofBirth, 
	 GROUP_CONCAT(IF(v.field_id BETWEEN 15 AND 18, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(v.value AS UNSIGNED)+1),'|',-1),NULL)) AS Category,
	 GROUP_CONCAT(IF(v.field_id = 19, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(v.value AS UNSIGNED)+1),'|',-1),NULL)) AS Division,
	 GROUP_CONCAT(IF(v.field_id = 20, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(v.value AS UNSIGNED)+1),'|',-1), NULL)) AS Membership,
	 GROUP_CONCAT(IF(v.field_id BETWEEN 24 AND 25, v.value,NULL)) AS College,
	 GROUP_CONCAT(IF(v.field_id = 8, v.value, NULL)) AS ZipCode,
	 u.register_date AS RegDate,
	 u.confirmNum AS Confirm#
	 FROM cal_dtregister_user u 
	 INNER JOIN cal_dtregister_user_field_values v  ON v.user_id = u.userId
	 INNER JOIN cal_dtregister_fee p ON p.user_id = u.userId
	 INNER JOIN cal_dtregister_group_event e  ON e.slabId = u.eventID
	 INNER JOIN cal_dtregister_fields f  ON v.field_id = f.id
	 WHERE u.eventID = 4
	 GROUP BY u.userId)
 ORDER BY RegType DESC, UserID, GroupSize DESC, FirstName

Please Log in or Create an account to join the conversation.

Script for SQL2Excel extension 13 years 4 months ago #14306

divalsafety Topic Author Offline Senior Boarder Posts: 45 Thank you received: 0	Wow! Great examples guys! Thanks for sharing, I'm using a combination of both to get the reports I need. Check this box to be notified of replies to this topic. Note: BBcode and smileys are still usable.
	Please Log in or Create an account to join the conversation.

Script for SQL2Excel extension 13 years 3 months ago #14828

divalsafety
Topic Author
Offline
Senior Boarder
Posts: 45
Thank you received: 0

Can someone assist with repeating the Organization names in group memberships in my example? As it stands now the Organization name is listed only once per group membership, we would like to have it repeated instead of blank fields in the report. Thanks in advance for your time!

(SELECT u.userId AS UserID, u.type AS RegType,
GROUP_CONCAT(IF(m.field_id = 12, m.value, NULL)) AS Organization,
GROUP_CONCAT(IF(m.field_id = 2, m.value, NULL)) AS FirstName,
GROUP_CONCAT(IF(m.field_id = 3, m.value, NULL)) AS LastName,
GROUP_CONCAT(IF(m.field_id = 10, m.value, NULL)) AS Email,
GROUP_CONCAT(IF(m.field_id = 11, m.value, NULL)) AS Phone,
GROUP_CONCAT(IF(m.field_id = 23, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(m.value AS UNSIGNED)+1),'|',-1),NULL)) AS Poll,
GROUP_CONCAT(IF(m.field_id = 21, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(m.value AS UNSIGNED)+1),'|',-1),NULL)) AS "Sales Rep",
u.register_date AS RegDate,
u.confirmNum AS Confirm#
FROM jos_dtregister_user u 
LEFT JOIN jos_dtregister_group_member g ON g.groupUserId = u.userId 
INNER JOIN jos_dtregister_member_field_values m ON m.member_id = g.groupMemberId
INNER JOIN jos_dtregister_group_event e ON e.slabId = u.eventID
INNER JOIN jos_dtregister_fields f ON m.field_id = f.id
WHERE u.eventID = 25
GROUP BY m.member_id)
UNION
(SELECT u.userId AS UserID, u.type AS RegType,  
GROUP_CONCAT(IF(v.field_id = 12, v.value, NULL)) AS Organization,
GROUP_CONCAT(IF(v.field_id = 2, v.value, NULL)) AS FirstName,
GROUP_CONCAT(IF(v.field_id = 3, v.value, NULL)) AS LastName,
GROUP_CONCAT(IF(v.field_id = 10, v.value, NULL)) AS Email,
GROUP_CONCAT(IF(v.field_id = 11, v.value, NULL)) AS Phone,
GROUP_CONCAT(IF(v.field_id = 23, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(v.value AS UNSIGNED)+1),'|',-1),NULL)) AS Class,
GROUP_CONCAT(IF(v.field_id = 21, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(v.value AS UNSIGNED)+1),'|',-1),NULL)) AS "Sales Rep",
u.register_date AS RegDate,
u.confirmNum AS Confirm#
FROM jos_dtregister_user u 
INNER JOIN jos_dtregister_user_field_values v ON v.user_id = u.userId
INNER JOIN jos_dtregister_group_event e ON e.slabId = u.eventID
INNER JOIN jos_dtregister_fields f ON v.field_id = f.id
WHERE u.eventID = 25
GROUP BY u.userID)
ORDER BY RegDate DESC, Organization DESC, FirstName

Please Log in or Create an account to join the conversation.

Script for SQL2Excel extension 13 years 3 months ago #14858

sacwebmaster Offline Junior Boarder Posts: 24 Thank you received: 0	Which table(s) is the "organization" field? Is it collected for both billing and members or just billing? Check this box to be notified of replies to this topic. Note: BBcode and smileys are still usable.
	Please Log in or Create an account to join the conversation.

TOPIC: Script for SQL2Excel extension

Script for SQL2Excel extension 13 years 4 months ago #14295

Script for SQL2Excel extension 13 years 4 months ago #14298

Script for SQL2Excel extension 13 years 4 months ago #14302

Script for SQL2Excel extension 13 years 4 months ago #14306

Script for SQL2Excel extension 13 years 3 months ago #14828

Script for SQL2Excel extension 13 years 3 months ago #14858