/bbe.lua

-- bbe.lua
-- generate 4 csv files nightly and for upload to Edline (Blackboard Engage)
-- uses Lua-cURL module
-- see https://github.com/Lua-cURL/Lua-cURLv3
-- install with luarocks install Lua-cURL

require 'table_print'
require 'app_config'

debugFlag = 1
verboseFlag = 1

-- bbc-students AutoSend fields
-- tab field delimiter, lf line delimiter, no headers
autosend_student_fields = [[
Student_Number
First_Name
Last_Name
Grade_Level
Gender
HomeRoom_Teacher
SchoolID
Home_Phone
Mother_Work_Phone
Mother_Cell
Father_Work_Phone
Father_Cell
Mother_Email
Father_Email
Home2_Phone
Mother2_Work_Phone
Mother2_Cell
Father2_Work_Phone
Father2_Cell
Mother2_Email
Father2_Email
Enroll_Status
Network_Id
Network_Password
Web_Id
Web_Password
Family_Ident
Mother_Staff_Id
Mother_First
Mother
Father_Staff_Id
Father_First
Father
Student_Web_Id
Mother2_Staff_Id
Mother2_First
Mother2_Last
Father2_Staff_Id
Father2_First
Father2_Last
EntryCode
Lang_Adults_Primary
CA_ELAStatus
Reg_Will_Attend
Reg_Grade_Level
ExitCode
Form3_Updated_At
Form4_Updated_At
Form6_Updated_At
Form9_Updated_At
Form10_Updated_At
Form15_Updated_At
Form1_Updated_At
Form16_Updated_At
]]

-- bbc-staff AutoSend fields
-- tab field delimiter, lf line delimiter, no headers
autosend_staff_fields = [[
Status
Staffstatus
Title
Teachernumber
First_Name
Last_Name
Gender
Schoolid
Home_Phone
Cell
Email_Addr
Email_Personal
Network_Id
Network_Password
Group_Membership
]]

-- course fields
autosend_section_fields = [[
id
schoolid
course_number
[02]course_name
[02]code
section_number
[13]abbreviation
expression
[05]teachernumber
[05]last_name
]]

-- cc fields
autosend_roster_fields = [[
sectionid
schoolid
course_number
[02]course_name
[02]code
section_number
[13]abbreviation
expression
[05]teachernumber
[05]last_name
[01]student_number
]]

-- number of elements in table (table.getn is for "lists" only)
function tlength(tbl)
  local count = 0
  for _ in pairs(tbl) do count = count + 1 end
  return count
end

-- strip whitespace from beginning and end of string
function string:trim6()
  return self:match'^()%s*$' and '' or self:match'^%s*(.*%S)'
end

-- headers for staff file
function string:is_empty()
  return not string.find(self, "%S")
end

function string:split(delimiter)
  local t = { }
  local from  = 1
  local delim_from, delim_to = string.find(self, delimiter, from)
  while delim_from do
    table.insert(t, string.sub(self, from, delim_from - 1))
    from  = delim_to + 1
    delim_from, delim_to = string.find(self, delimiter, from)
  end
  table.insert(t, string.sub(self, from))
  return t
end

function wordsplit(s)
	local t = { }
	for word in s:gmatch('%w+') do 
		table.insert(t, word) 
	end
	return t
end

function string:splitcsv()
  s = self .. ','     -- ending comma
  local t = {}        -- table to collect fields
  local fieldstart = 1
  repeat
    -- next field is quoted? (start with `"'?)
    if string.find(s, '^"', fieldstart) then
      local a, c
      local i  = fieldstart
      repeat
        -- find closing quote
        a, i, c = string.find(s, '"("?)', i + 1)
      until c ~= '"'    -- quote not followed by quote?
      if not i then error('unmatched "') end
      local f = string.sub(s, fieldstart + 1, i - 1)
      table.insert(t, (string.gsub(f, '""', '"')))
      fieldstart = string.find(s, ',', i) + 1
    else                -- unquoted; find next comma
      local nexti = string.find(s, ',', fieldstart)
      table.insert(t, string.sub(s, fieldstart, nexti - 1))
      fieldstart = nexti + 1
    end
  until fieldstart > string.len(s)
  return t
end

function course_abbr(name)
  local words = wordsplit(name)
  local abbr = string.sub(words[1], 1, 4)
  local suffix = ''
  local nwords = table.getn(words) 
  if nwords > 1 then
    local lastw = words[nwords]
    if lastw == 'K' or tonumber(lastw) ~= nil and tonumber(lastw) > 0 then
    	suffix = lastw
    end
  end
  return abbr .. suffix
end

function readcsv(fname, headers, rowfn)
  io.input(fname)
  local lno = headers and 0 or 1
  local columns = { }
  while true do
    local line = io.read()
    if line == nil then break end
    line = string.gsub(line, "\r$", "") -- handle CRLF
    if lno == 0 then
      columns = string.splitcsv(line)
    else
      local row = { }
      if headers then
        local values = string.splitcsv(line)
        for k, v in pairs(columns) do
          row[v] = values[k]
        end
      else
        row = string.splitcsv(line)
      end
      for k, v in pairs(row) do
        if string.is_empty(v) then row[k] = nil end
      end
      rowfn(row)
    end
    lno = lno + 1
  end
end

function readtab(fname, headers, rowfn, sumfn)
  io.input(sourceDir..fname)
  local lno = headers and 0 or 1
  local columns = { }
  while true do
    local line = io.read()
    if verboseFlag > 2 then io.stderr:write("reading " .. fname .. " line " .. lno .. "\n") end
    if line == nil then break end
    line = string.gsub(line, "\r$", "") -- handle CRLF
    if lno == 0 then
      columns = string.split(line, "\t")
    else
      local row = { }
      if headers then
        local values = string.split(line, "\t")
        for k, v in pairs(columns) do
          row[v] = values[k]
        end
      else
        row = string.split(line, "\t")
      end
      for k, v in pairs(row) do
        if string.is_empty(v) then row[k] = nil end
      end
      local status, err = pcall(rowfn, row, fname, lno)
      if verboseFlag > 0 and not status then
        io.stderr:write("row invalid: " .. err .. "\n")
      end
    end
    lno = lno + 1
  end
	if sumfn then
		local status, err = pcall(sumfn, fname, lno)
	end
end

-- edline specific stuff 
included_students = nil
if debugFlag then
	included_students = { ['111984']=true, ['111985']=true }
end

excluded_courses = { 
	['AAAA']=true, -- attendance
	['9966']=true, -- study period
	['9977']=true, -- student aide
	['9991']=true, 
	['9993']=true, 
	['9996']=true, 
	['9997']=true, -- grade level grouping
}
class_table = { }
course_terms = { }
assigned_teachers = { }
enrolled_students = { }
enrolled_classes = { }

-- if we are limiting students for testing purposes
function check_student(sn)
	return sn~= "" and (not included_students or included_students[sn])
end

function check_teacher(tn)
	return tn~= ""
end

function check_course(cn)
	return cn ~= "" and not excluded_courses[cn]
end

function check_school(sid)
	return sid == "103" or sid == "104"
end

function check_class(cid)
	return cid and enrolled_classes[cid]
end

function full_year(term)
	return not term:match('^[STQH][1-6]$')
end

function make_class_id(class_abbr, teacher_last, term, course_name, course_number)
	if not class_abbr then
		io.stderr:write("no abbreviation for course " .. course_name .. " (" .. course_number .. ")\n")
		return nil
	end
	local class_id = class_abbr .. "-" .. string.upper(string.sub(teacher_last, 1, 3))
	if not full_year(term) then 
		class_id = class_id .. "-" .. term
	end
	return class_id
end

-- convert row. if autosend fields are changed, you must change the logic
-- in this function
function writestudentrow(row, fname, lno)
  -- student fields
  local student_number = row[1] or ""
  local first_name = row[2]
  local last_name = row[3]
  local grade_level = 0 + row[4]
  local schoolid = row[7]
  local enroll_status = tonumber(row[22] or 0)
  if enroll_status == 0 and check_student(student_number) and check_school(schoolid) then		
		enrolled_students[student_number] = true
		local s_grade_level = string.format("%02d", grade_level);
		
	  -- output a row 
	  -- "ID","LastName","FirstName","GradeLevel","SchoolID" 
	  io.write(string.format("%q,%q,%q,%q,%q\r\n", "S" .. student_number, last_name, first_name, s_grade_level, schoolid))
  
	  if verboseFlag > 2 then io.stderr:write("row written\n") end
	end
end

-- convert row. if autosend fields are changed, you must change the logic
-- in this function
function writeschedulerow(row, fname, lno)
  -- cc fields
  local schoolid = row[2]
	local course_number = row[3]
  local student_number = row[11] or ""

  if enrolled_students[student_number] and check_course(course_number) and check_school(schoolid) then
		local course_name = (row[4] or ""):trim6()
		local class_abbr = row[5]
  	local section_number = row[6]
		local term = row[7]
		local teachernumber = row[9]
		local teacher_last = row[10]
		local class_id = make_class_id(class_abbr, teacher_last, term, course_name, course_number)
		if class_id then
			enrolled_classes[class_id] = true
		
		  -- output a row 
		  -- "ClassID","StudentID","SchoolID" 
		  io.write(string.format("%q,%q,%q\r\n", class_id, "S" .. student_number, schoolid))
  
		  if verboseFlag > 2 then io.stderr:write("row written\n") end
		end
	end
end

-- convert row. if autosend fields are changed, you must change the logic
-- in this function
function writeclassrow(row, fname, lno)
  -- section fields
  local schoolid = row[2]
	local course_number = row[3]
	local course_name = (row[4] or ""):trim6()
	local teachernumber = row[9] or ""
	local teacher_last = row[10]
	
  if check_teacher(teachernumber) and check_course(course_number) and check_school(schoolid) then
		local class_abbr = row[5]
		local section_number = row[6]
		local term = row[7]
		local class_id = make_class_id(class_abbr, teacher_last, term, course_name, course_number)
		if check_class(class_id) then
			local course_term = class_abbr
			local class_teacher_name = course_name .. "-" .. teacher_last
			local class_bare_name    = course_name
			if not full_year(term) then
				course_term = course_term .. "-" .. term
				class_teacher_name = class_teacher_name .. "-" .. term
				class_bare_name    = class_bare_name .. "-" .. term
			end
			if not class_table[class_id] then
				assigned_teachers[teachernumber] = true
				class_table[class_id] = { class_bare_name, class_teacher_name, "T" .. teachernumber, schoolid }
			end
			if not course_terms[course_term] then
				course_terms[course_term] = {  }
			end
			course_terms[course_term][class_id] = true
		end
	else
  	if verboseFlag > 0 then io.stderr:write("rejecting class " .. course_name .. " (" .. course_number .. ") " .. teacher_last .. "\n") end
	end
end

function writeclasses(fname, lno)
	for course_term, class_id_table in pairs(course_terms) do
		local nclass_ids = tlength(class_id_table)
		for class_id, _ in pairs(class_id_table) do
			local data = class_table[class_id]
			local class_name = nclass_ids > 1 and data[2] or data[1]
		  -- output a row 
		  -- ClassID","Class Name","TeacherID","SchoolID"
		  io.write(string.format("%q,%q,%q,%q\r\n", class_id, class_name, data[3], data[4]))
  	end
	end
end

-- convert row. if autosend fields are changed, you must change the logic
-- in this function
function writestaffrow(row, fname, lno)
  -- staff fields
  local status = 0 + (row[1] or 0)
  local staffstatus = 0 + (row[2] or 0)
  local teachernumber = row[4] or ""
  local first_name = row[5] or ""
  local last_name = row[6] or ""
  local schoolid = row[8] or ""

  if assigned_teachers[teachernumber] then
    -- output a row

    -- "ID","LastName","FirstName","GradeLevel","SchoolID”
    io.write(string.format("%q,%q,%q,%q,%q\r\n", "T" .. teachernumber, last_name, first_name, "", schoolid))
    if verboseFlag > 2 then io.stderr:write("row written\n") end
  end
end

-- convert powerschool autosend files to csv format required by Edline
function create_csv_file(psFile, csvFile, mode, rowfn, sumfn)
  local o = assert(io.open(edlineDir..csvFile, mode))
  io.output(o)
  readtab(psFile, false, rowfn, sumfn)
  o:close()
end

-- begin main script

-- must run students first (to get enroll_status)!
create_csv_file("ps-students.txt",        "student.csv",  'wb', writestudentrow, nil)
-- must run rosters before sections!
create_csv_file("ps-rosters-bacich.txt",  "schedule.csv", 'wb', writeschedulerow, nil)
create_csv_file("ps-rosters-kent.txt",    "schedule.csv", 'ab', writeschedulerow, nil)
-- must run sections before staff!
create_csv_file("ps-sections-bacich.txt", "class.csv",    'wb', writeclassrow, nil)
create_csv_file("ps-sections-kent.txt",   "class.csv",    'ab', writeclassrow, writeclasses)
create_csv_file("ps-staff.txt",           "teacher.csv",  'wb', writestaffrow, nil)