property runNumber : 1 -- leave at 1 except for test purposes
property absenceFileName : "Absences.numbers"
property containingFolder : path to downloads folder from user domain as string
property dailyAbsenceTable : "Daily Absence Record" -- title of each daily table, change to whatever you like
property totalAbsenceSheetName : "Total absences" --the name of the "Total Absences" sheet, DO NOT change this

global dateString
global rowCount -- count of rows in "Names" table of "Total absences" sheet
global nameList

on run
	set stopnow to my filecheck()
	if stopnow then -- absences file not found
		display dialog "No absences file in Downloads folder!" buttons {"OK"} default button 1 with title "Missing file" with icon caution
		return --stop now
	else
		repeat with nextDate from 0 to (runNumber - 1)
			set dateString to my makeDatestring(nextDate)
			set stopnow to my makeDailysheet()
			if stopnow then --user cancelled  the choose from list dialog
				return --stop now
			end if
			my logTotalAbsences()
		end repeat
	end if
end run

on filecheck()
	--define path to numbers document in its containing folder:
	set absenceFile to containingFolder & absenceFileName
	set stopnow to false
	--check existence of file:
	try
		absenceFile as alias
	on error number errorNumber
		if errorNumber = -1700 then --file does not exist
			set stopnow to true
			return stopnow
		end if
	end try
	--file is there, so we can open it:
	tell application "Finder" to open absenceFile
	return stopnow
end filecheck

on makeDatestring(nextDate)
	--build a date string for the title of the individual absence record sheets
	set today to (current date) + (nextDate * days)
	set theDay to day of today as string
	set theDayName to weekday of today as string
	set theMonth to month of today as string
	set theYear to year of today as string
	set dateString to theDayName & ", " & theDay & " " & theMonth & " " & theYear as text
end makeDatestring

on makeDailysheet()
	tell application "Numbers"
		delay 1 -- to allow document to open
		tell document 1
			tell sheet totalAbsenceSheetName
				tell table "Names" --create a list of students' names
					set rowCount to count rows
					set nameList to value of cells of column 1
				end tell
			end tell --total absence sheet
			
			
			
			--make new daily sheet, delete any existing one (to allow for mistakes and re-runs:
			
			if exists sheet dateString then delete sheet dateString
			set juanSheet to (make new sheet at end of sheets with properties {name:dateString})
			set active sheet to juanSheet
			tell juanSheet
				--modify default table (no headers, 2 columns, as many rows as students, name using dailyAbsenceTable)
				tell table 1
					set name to dailyAbsenceTable
					set column count to 2
					set row count to rowCount
					set {header row count, header column count} to {0, 0}
					set format of every cell of column 2 to checkbox --false by default
					
					
					
					--add student names to first column
					repeat with x from 1 to rowCount
						set nextName to item x of nameList
						tell column 1
							tell row x
								tell cell 1 to set value to nextName
							end tell --row x
						end tell --column 1
					end repeat --row count
					
					--ask for absences
					set stopnow to false
					set absentNames to (choose from list nameList with prompt "It's " & dateString & "." & return & "Who is absent? (Click Cancel if no-one.)" with multiple selections allowed)
					if the result is false then
						set stopnow to true
						return stopnow
					end if --user cancelled, end run
					repeat with x from 1 to rowCount
						tell column 1
							tell row x
								tell cell 1 to set studentName to its value
								tell cell 2
									if studentName is in absentNames then set value to true
								end tell --cell 2
							end tell --row x
						end tell --column 1
					end repeat --row count
				end tell --table 1
			end tell --juan sheet
		end tell -- document 1
	end tell --numbers
	return stopnow
end makeDailysheet

on logTotalAbsences()
	--build list of student records, absences zeroed:
	set recordList to {}
	repeat with eachName in nameList
		set nextRecord to {student:eachName as string, absences:0}
		set end of recordList to nextRecord
	end repeat
	tell application "Numbers"
		tell document 1
			--build record of absences from across all dated sheets:
			set dailySheets to (every sheet whose name is not totalAbsenceSheetName)
			repeat with eachSheet in dailySheets
				tell eachSheet
					tell table dailyAbsenceTable
						set theRows to rows
						repeat with eachRow in theRows
							tell eachRow
								set searchName to value of cell 1 -- name of each student in turn
								if value of cell 2 as integer = 1 then --student was marked absent (checkbox is ticked)
									repeat with eachRecord in recordList
										if student of eachRecord is searchName then
											set absences of eachRecord to (absences of eachRecord) + 1
										end if
									end repeat --eachrecord
								end if
							end tell --eachrow
						end repeat --each row in theRows
					end tell --absence record table
				end tell --eachSheet
			end repeat --each sheet in daily sheets
			
			
			--update total absences sheet from absences record:
			tell sheet totalAbsenceSheetName
				tell table "Names"
					repeat with eachRow from 1 to rowCount
						set value of cell 2 of row eachRow to absences of item eachRow of recordList
					end repeat --each row
				end tell -- Names table
			end tell -- Total Absences Sheet
			
		end tell --document 1
	end tell --numbers
	
end logTotalAbsences