diff --git a/attendancebook_template.xlsx b/attendancebook_template.xlsx index 242838d..7f0692d 100644 --- a/attendancebook_template.xlsx +++ b/attendancebook_template.xlsx Binary files differ diff --git a/call.cgi b/call.cgi index 4ef7c27..2751a33 100755 --- a/call.cgi +++ b/call.cgi @@ -5,9 +5,10 @@ import cgi import re import json -import pickle +import sqlite3 from datetime import datetime from datetime import timedelta +import pprint if sys.argv[-1] == "--test": logdir="./" @@ -50,7 +51,6 @@ if fname.startswith("access_log-") and lower <= fname <=higher] if not logfiles: logfiles.append('access_log') -maildb = pickle.load(open("name.pickle")) db = {} lines = [] for fname in logfiles[-2:]: @@ -66,6 +66,11 @@ db[host].append([date,action]) else: db[host] = [[date,action]] + +conn = sqlite3.connect("/var/www/etc/kguid.sqlite3") +c = conn.cursor() +query= "SELECT number, name_kanji, name_kana, depart_name, pos_code FROM user WHERE id = ?" + for machine in db.keys(): candidate = [] active = False @@ -77,14 +82,16 @@ if date < to: date = date.strftime(datefmt) active = True - if id_ in maildb: + res = c.execute(query, (id_,)) + dd = res.fetchone() + if dd: candidate.append( # 0 1 2 3 4 [machine, date, "---", action, id_, # 5 6 - maildb[id_]["no"], maildb[id_]["name"], + dd[0], dd[1], # 7 8 - maildb[id_]["reading"], maildb[id_]["dep"]]) + dd[2], dd[3], dd[4]]) else: candidate.append( # 0 1 2 3 4 @@ -92,7 +99,7 @@ # 5 6 "????", id_, # 7 8 - "--", "--"]) + "--", "--", "--"]) elif action.startswith("reject") and candidate: # exclude double-login action, id_, host = action.split(":") @@ -114,6 +121,9 @@ candidate[-1][1] += "〜"+date.strftime(datefmt) lines.extend(candidate) +c.close() +conn.close() + if not "excel" in form: print "Content-Type: application/json" print "" @@ -129,13 +139,17 @@ wb = load_workbook("./attendancebook_template.xlsx") lines.sort(lambda x,y: cmp(x[4]+x[1],y[4]+y[1])) - for room in ["GRL", "ML1", "ML2", "HSJ"]: + for room in ["GRL", "ML1", "ML2", "IML"]: wb.remove(wb[room+u"出席表"]) if room in form: ws = wb.create_sheet(title = room+u"出席表") - extract = [[host, str(student_no),logon[:5],logoff[-5:], name, reading, dep] + if room == "IML": + prefix = "HSJ" + else: + prefix = room + extract = [[host, str(student_no),logon[:5],logoff[-5:], name, reading, dep, pos] for host, logon, logoff, _, _, - student_no, name, reading, dep in lines if host.startswith(room)] + student_no, name, reading, dep, pos in lines if host.startswith(prefix)] ws.column_dimensions["A"].width =9.0; ws.column_dimensions["B"].width =11.0; ws.column_dimensions["C"].width =6.0; ws.column_dimensions["D"].width =6.0; @@ -148,7 +162,7 @@ prev = None row = None align = Alignment(wrap_text=True, vertical="center", horizontal="center") - HSJroomA = [ "01","02","03","04","05","06", + IMLroomA = [ "01","02","03","04","05","06", "11","12","13","14","15","16", "21","22","23","24","25","26", "31","32","33","34","35","36", @@ -156,37 +170,37 @@ "51","52","53","54", "K01","K03","61"]; for e in extract: - if room == "HSJ": - in_rooma = e[0][4:] in HSJroomA + if room == "IML": + in_rooma = e[0][4:] in IMLroomA if in_rooma: - if not "HSJ-A" in form: + if not "IML-A" in form: continue else: - if not "HSJ-B" in form: + if not "IML-B" in form: continue - if e[6] != "STAFF" and not e[1].startswith("M"): + if e[7] == "01" and not e[1].startswith("M"): if prev == e[1]: row = str(ws.max_row) ws['A'+row]=e[0] ws['C'+row]=ws['C'+row].value + "\n"+e[2] ws['D'+row]=ws['D'+row].value + "\n"+e[3] else: - ws.append(e) + ws.append(e[:-1]) row = str(ws.max_row) ws['A'+row].alignment = align; ws['B'+row].alignment = align ws['C'+row].alignment = align; ws['D'+row].alignment = align ws['E'+row].alignment = align; ws['F'+row].alignment = align ws['G'+row].alignment = align # TODO need refactor: maybe it can be integrated - else: stafflist.append(e) + else: stafflist.append(e[:-1]) prev = e[1] - tab = Table(displayName="StudentTable"+room, ref="A2:G"+str(ws.max_row)) + tab = Table(displayName="StudentTable"+prefix, ref="A2:G"+str(ws.max_row)) style = TableStyleInfo(name="TableStyleLight1", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False) tab.tableStyleInfo = style ws.add_table(tab) ws.append([]) - ws.append(['合計', '=COUNTA(StudentTable'+room+'[学籍番号])', '人']) + ws.append(['合計', '=COUNTA(StudentTable'+prefix+'[学籍番号])', '人']) ws.append([]) ws.append(["スタッフ"]) ws.append(["ホスト名", "ユーザ名", "開始", "終了", "名前", "よみ", "所属"]) @@ -200,7 +214,7 @@ ws['G'+row].alignment = align # TODO need refactor: maybe it can be integrated if ws.max_row == staff_start: ws.append(["-","-","-","-","-","-","-"]) - tab = Table(displayName="StaffTable"+room, ref="A"+str(staff_start)+":G"+str(ws.max_row)) + tab = Table(displayName="StaffTable"+prefix, ref="A"+str(staff_start)+":G"+str(ws.max_row)) style = TableStyleInfo(name="TableStyleLight2", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False) tab.tableStyleInfo = style