reportVolunteering.js

function readVolunteers(stmt) {
	makeReport(stmt, {
		sheetName: "Volunteering",
		title: "Volunteers",
		query: `
      SELECT 
        db.\`first_name\` AS "First Name",
        db.\`last_name\` AS "Last Name",
        pd.cc_volunteer AS "Selected Roles",
        volunteer_indoor_preevent_facebook_promo AS "FB promo",
        volunteer_indoor_event_reporter AS "Rprtr",
        volunteer_indoor_check_in AS "Check-in",
        volunteer_indoor_welcome_liaison AS "WelcLn",
        volunteer_indoor_rounding_up AS "Rnd Up",
        volunteer_indoor_before_cake_tidying AS "PreCake",
        volunteer_indoor_after_cake_tidying AS "PostCake",
        volunteer_indoor_give_announcements AS "Anncments Vol",
        competency_indoor_announcements AS "Anncments Compet",
        competency_indoor_pairing AS "Pair",
        competency_indoor_trip_director AS "Event Dir",
        scores_volunteer_score_cached AS "Receptiveness",
        scores_volunteer_reliability_score_cached AS "V Reliability%",
        stats_attendance_indoor_wednesday_attended_cached AS "Attended",
        \`admin-wednesday-requests-notes\` AS \`Requests and notes\`,
        pd.order_id AS "Order ID",
        pd.user_id AS "User ID",
        competency_indoor_floorwalker AS "Flwlkr",
        competency_indoor_skillsharer,
        competency_indoor_checkin
      FROM wp_member_db db
      JOIN wp_order_product_customer_lookup pd ON pd.user_id = db.id
      JOIN wp_member_db_volunteering vl ON pd.user_id = vl.id
      WHERE product_id = ${cell}
        AND \`cc_location\` = "${cc_location}"
        AND status IN ("wc-processing", "wc-onhold", "wc-on-hold")
        AND stats_attendance_indoor_wednesday_attended_cached >= 2
        AND cc_compliance_last_date_of_climbing BETWEEN DATE_SUB(NOW(), INTERVAL 2 MONTH) AND NOW()
      ORDER BY 
        FIELD(pd.cc_volunteer, "none", "Tuesday Facebook Promo", "tuesdaypromo2", "Wednesday Facebook Promo", "wednesdaypromo2", "check in", "welcome liaison", "pairing", "rounding up", "Pre-cake Preparation", "announcements", "after cake tidy", "event director", "Event Reporter", "Wednesday Evening Trainer", "floorwalker", "Friday Facebook Promo", "Summit Up Promo") ASC,
        pd.cc_volunteer ASC,
        CAST(db.scores_volunteer_score_cached AS UNSIGNED INTEGER) ASC,
        CAST(db.stats_attendance_indoor_wednesday_attended_cached AS UNSIGNED INTEGER) DESC,
        volunteer_indoor_event_reporter,
        volunteer_indoor_check_in
    `,
		formatting: [
			{ type: "numberFormat", column: "Receptiveness", format: "0" },
			{
				type: "colorLessThanOrEqual",
				column: "V Reliability%",
				value: "50",
				color: colors.yellow,
			},
			{
				type: "colorLessThanOrEqual",
				column: "V Reliability%",
				value: "80",
				color: colors.pink,
			},
			{
				type: "colorLessThanOrEqual",
				column: "V Reliability%",
				value: "90",
				color: colors.lightYellow,
			},
			{
				type: "colorLessThanOrEqual",
				column: "Receptiveness",
				value: "10",
				color: colors.pink,
			},
			{
				type: "colorLessThanOrEqual",
				column: "Receptiveness",
				value: "20",
				color: colors.lightYellow,
			},
			{
				type: "colorLessThanOrEqual",
				column: "Receptiveness",
				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: "Rprtr", search: "No", color: colors.grey },
			{ type: "text", column: "Check-in", search: "No", color: colors.grey },
			{ type: "text", column: "WelcLn", search: "No", color: colors.grey },
			{ type: "text", column: "Rnd Up", search: "No", color: colors.grey },
			{ type: "text", column: "PreCake", search: "No", color: colors.grey },
			{ type: "text", column: "PostCake", search: "No", color: colors.grey },
			{
				type: "text",
				column: "Anncments Vol",
				search: "No",
				color: colors.grey,
			},
			{
				type: "text",
				column: "Anncments Compet",
				search: "No",
				color: colors.grey,
			},
			{ type: "text", column: "Pair", search: "No", color: colors.grey },
			{ type: "text", column: "Event Dir", search: "No", color: colors.grey },
			{ type: "text", column: "Flwlkr", search: "No", color: colors.grey },
			{ type: "columnWidth", column: "Requests and notes", width: 200 },
			{ type: "wrap", column: "Requests and notes" },
		],
	});
}

function volunteerData() {
	const conn = Jdbc.getConnection(url, username, password);
	const stmt = conn.createStatement();

	readVolunteers(stmt);

	stmt.close();
	conn.close();

	return "done";
}