reportVolunteering.js

function readVolunteering(stmt, cell) {
	makeReport(stmt, {
		sheetName: "Volunteering",
		query: `
      SELECT
        \`first_name\` AS "First Name",
        \`nickname\` AS "Facebook Name",
        pd.cc_volunteer AS "Selected Roles",
        volunteer_caving_preevent_facebook_promo AS "FB promo",
        volunteer_caving_event_reporter AS "Reporter",
        volunteer_caving_head_chef AS "Head Chef",
        volunteer_caving_evening_meal_chef AS "EM Chef",
        volunteer_caving_breakfast_lunch_chef AS "B&L Chef",
        volunteer_caving_packed_lunch_marshal AS "Lunch Marshal",
        volunteer_caving_breakfast_marshal AS "Breakfast Marshal",
        volunteer_caving_lift_sharing_coordinator AS "Lift Coordinator",
        volunteer_caving_activities_coordinator AS "Activities Coordinator",
        volunteer_caving_kit_coordinator AS "Kit Coordinator",
        volunteer_caving_newbie_buddy_maker AS "Newbie Buddy",
        volunteer_caving_covid_marshal AS "Covid Marshal",
        volunteer_caving_evening_meal_washing_up_marshal AS "EM Wash Up",
        volunteer_caving_breakfast_washing_up_marshal AS "B&L Wash Up",
        volunteer_caving_event_assistant AS "Event Assistant",
        volunteer_caving_event_director AS "Trip Director",
        scores_volunteer_score_cached AS "Receptiveness",
        \`admin-first-timer-question\` AS "First time with Crew?",
        \`admin-first-timer-caving\` AS "First caving trip?",
        \`stats_volunteer_for_denominator_cached\` AS "Attended events",
        \`stats_attendance_caving_attended_cached\` AS "Attended caving trips",
        \`admin-caving-requests-notes\` AS "Requests and notes",
        pd.order_id AS "Order ID",
        pd.user_id AS "User ID"
      FROM jtl_member_db db
      JOIN jtl_order_product_customer_lookup pd ON pd.user_id = db.id
      WHERE product_id=${cell} AND status IN ("wc-processing", "wc-onhold", "wc-on-hold")
      ORDER BY FIELD(pd.cc_volunteer, "none", "trip_director", "buddy_coordinator", "kit_coordinator",
        "transport_coordinator", "lunch_breakfast_chef", "evening_meal_chef", "breakfast_marshal",
        "lunch_marshal", "caving_coordinator", "evening_meal_washingup_marshal", "postpromo1") ASC,
        \`admin-first-timer-caving\` DESC, CAST(stats_attendance_caving_attended_cached AS UNSIGNED INTEGER) ASC
    `,
		formatting: [
			{ type: "numberFormat", column: "Attended events", format: "0" },
			{
				type: "colorLessThanOrEqual",
				column: "Attended events",
				value: "10",
				color: colors.pink,
			},
			{
				type: "colorLessThanOrEqual",
				column: "Attended events",
				value: "20",
				color: colors.lightYellow,
			},
			{
				type: "colorLessThanOrEqual",
				column: "Attended events",
				value: "30",
				color: colors.yellow,
			},
			{
				type: "color",
				column: "Selected Roles",
				search: "none",
				color: colors.lightGreen,
			},
			{
				type: "color",
				column: "Selected Roles",
				search: "Selected",
				color: colors.white,
			},
			{
				type: "color",
				column: "Selected Roles",
				search: "",
				color: colors.lightBlue,
			},
			{ type: "text", column: "FB promo", search: "No", color: colors.grey },
			{ type: "text", column: "Reporter", search: "No", color: colors.grey },
			{ type: "text", column: "Head Chef", search: "No", color: colors.grey },
			{ type: "text", column: "EM Chef", search: "No", color: colors.grey },
			{ type: "text", column: "B&L Chef", search: "No", color: colors.grey },
			{
				type: "text",
				column: "Lunch Marshal",
				search: "No",
				color: colors.grey,
			},
			{
				type: "text",
				column: "Breakfast Marshal",
				search: "No",
				color: colors.grey,
			},
			{
				type: "text",
				column: "Lift Coordinator",
				search: "No",
				color: colors.grey,
			},
			{
				type: "text",
				column: "Activities Coordinator",
				search: "No",
				color: colors.grey,
			},
			{
				type: "text",
				column: "Kit Coordinator",
				search: "No",
				color: colors.grey,
			},
			{
				type: "text",
				column: "Newbie Buddy",
				search: "No",
				color: colors.grey,
			},
			{
				type: "text",
				column: "Covid Marshal",
				search: "No",
				color: colors.grey,
			},
			{ type: "text", column: "EM Wash Up", search: "No", color: colors.grey },
			{ type: "text", column: "B&L Wash Up", search: "No", color: colors.grey },
			{
				type: "text",
				column: "Event Assistant",
				search: "No",
				color: colors.grey,
			},
			{
				type: "text",
				column: "Trip Director",
				search: "No",
				color: colors.grey,
			},
			{
				type: "color",
				column: "First time with Crew?",
				search: "Yes",
				color: colors.lightYellow,
			},
			{
				type: "color",
				column: "First caving trip?",
				search: "Yes",
				color: colors.lightYellow,
			},
			{ type: "columnWidth", column: "Requests and notes", width: 300 },
			{ type: "columnWidth", column: "First Name", width: 150 },
			{ type: "wrap", column: "Requests and notes" },
		],
	});
}