reportLeadBelayTraining.js

function readLeadBelayTraining(stmt) {
  makeReport(stmt, {
    sheetName: "Lead Training",
    query: `
      SELECT DISTINCT 
        db.\`first_name\` "Name", 
        db.\`nickname\` "fbname", 
        CAST(db.\`stats_volunteer_for_numerator_cached\` AS UNSIGNED INTEGER) "Volunteering", 
        db.\`scores_volunteer_score_cached\` "Receptiveness", 
        db.\`skills-belaying\` "Belaying Skills", 
        db.\`climbing-indoors-toproping-grades\` "Indoor TR", 
        db.\`admin-wednesday-requests-notes\` as \`Requests and notes\`, 
        db.\`climbing-indoors-leading-grades\` "Indoor Lead", 
        db.\`skills-sport-climbing\` "Sport Skills" 
      FROM wp_member_db db 
      LEFT JOIN wp_order_product_customer_lookup pd ON pd.user_id = db.id 
      JOIN wp_member_db_scores s ON s.user_id = db.id 
      WHERE \`product_id\`=${cell} 
        AND \`cc_location\`="${cc_location}" 
        AND status IN ("wc-processing", "wc-onhold", "wc-on-hold") 
        AND db.\`skills-belaying\` IN ("Top-rope-belaying","learner-lead-belayer") 
        AND (db.\`skills-sport-climbing\` IS NULL OR db.\`skills-sport-climbing\` NOT LIKE "%Lead%") 
      ORDER BY CAST(db.\`stats_volunteer_for_numerator_cached\` AS UNSIGNED INTEGER) DESC, 
        db.\`skills-belaying\` DESC, 
        db.\`skills-trad-climbing\` ASC, 
        db.\`climbing-happy-to-supervise\` DESC
    `,
    formatting: [
      { type: 'colorLessThanOrEqual', column: "Volunteering", value: "0", color: colors.pink },
      { type: 'colorLessThanOrEqual', column: "Volunteering", value: "2", color: colors.lightYellow },
      { type: 'colorLessThanOrEqual', column: "Volunteering", value: "5", color: colors.yellow },
      { 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: "Belaying Skills", search: "learner-lead-belayer", color: colors.brightYellow },
      { type: 'color', column: "Belaying Skills", search: "lead-belayer", color: colors.green },
      { type: 'color', column: "Belaying Skills", search: "top", color: colors.lightBlue },
      { type: 'color', column: "Belaying Skills", search: "No-belaying", color: colors.lightRed },
      { type: 'numberFormat', column: "Receptiveness", format: "0" },
      { type: 'columnWidth', column: "fbname", width: 150 },
      { type: 'columnWidth', column: "Requests and notes", width: 300 },
      { type: 'wrap', column: "Requests and notes" }
    ]
  });
}