Welcome, Guest
Username: Password: Remember me

TOPIC: Script for SQL2Excel extension

Script for SQL2Excel extension 13 years 4 months ago #14295

  • divalsafety
  • divalsafety's Avatar Topic Author
  • Offline
  • Senior Boarder
  • 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!

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

Script for SQL2Excel extension 13 years 4 months ago #14298

  • suyog
  • suyog's Avatar
  • Offline
  • Senior Boarder
  • 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
  • sacwebmaster's Avatar
  • Offline
  • Junior Boarder
  • 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
  • divalsafety's Avatar Topic Author
  • Offline
  • Senior Boarder
  • 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.

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

Script for SQL2Excel extension 13 years 3 months ago #14828

  • divalsafety
  • divalsafety's Avatar Topic Author
  • Offline
  • Senior Boarder
  • 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
  • sacwebmaster's Avatar
  • Offline
  • Junior Boarder
  • 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?

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

Time to create page: 0.502 seconds