def get_attendance_record():
params = request.get_json() or request.form or request.args
current, size = get_pagination(params)
event_alias1 = db.aliased(Event)
event_alias2 = db.aliased(Event)
query = _build_query(params, Attendance.query.outerjoin(event_alias1, Attendance.earliest_event)
.outerjoin(event_alias2, Attendance.latest_event)
.join(Attendance.subject))
query = query.options(db.contains_eager(Attendance.earliest_event, alias=event_alias1),
db.contains_eager(Attendance.latest_event, alias=event_alias2),
db.contains_eager(Attendance.subject),
)
pagination = query.paginate(current, size, False)
page = page_format(pagination)
result = [attendance.get_json(with_subject=True) for attendance in pagination.items]
return success_result(result, page)
def get_attendance_record_monthly():
params = request.get_json() or request.form or request.args
date = params.get('date')
subject_id = params.get('subject_id')
try:
start_date, end_date = month_range(date)
except:
return error_result(ErrorCode.ERROR_INVALID_PARAM)
event_alias1 = db.aliased(Event)
event_alias2 = db.aliased(Event)
attendances = Attendance.query.outerjoin(event_alias1, Attendance.earliest_event)\
.outerjoin(event_alias2, Attendance.latest_event)\
.filter(Attendance.subject_id == subject_id,
Attendance.date >= start_date,
Attendance.date < end_date)\
.options(db.contains_eager(Attendance.earliest_event, alias=event_alias1),
db.contains_eager(Attendance.latest_event, alias=event_alias2))\
.all()
calendar = AttendanceCalendar.get_or_create(g.user.company_id, datetime.date.today().year)
stats = AttendanceStats()
for attendance in attendances:
if calendar.check_day(attendance.date):
stats.add_attendance(attendance.clock_in, attendance.clock_out, 1)
records_dict = {attendance.date:attendance.get_json() for attendance in attendances}
records = []
date = start_date
while date < end_date:
records.append(records_dict.get(date, None))
date = date + timedelta(days=1)
result = {
'records': records,
'stats': stats.to_dict()
}
return success_result(result)
def get_attendance_stats():
params = request.get_json() or request.args
current, size = get_pagination(params)
date = params.get('date')
search = params.get('search')
try:
start_date, end_date = month_range(date)
except:
return error_result(ErrorCode.ERROR_INVALID_PARAM)
if search:
search = search.replace('\\', '\\\\')
query = db.session.query(Subject.id).filter(Subject.company_id == g.user.company_id,
Subject.subject_type == SubjectType.TYPE_EMPLOYEE,
db.or_(Subject.real_name.contains(search),
Subject.pinyin.contains(search),
))
pagination = fast_pagination(query, current, size)
if not pagination.items:
query = db.session.query(Subject.id).filter(Subject.company_id == g.user.company_id,
Subject.subject_type == SubjectType.TYPE_EMPLOYEE,
Subject.department.contains(search))
pagination = fast_pagination(query, current, size)
else:
query = db.session.query(Subject.id).filter(Subject.company_id == g.user.company_id,
Subject.subject_type == SubjectType.TYPE_EMPLOYEE)
pagination = fast_pagination(query, current, size)
subjects = pagination.items
subject_ids = [id for id, in subjects]
subjects = Subject.query.outerjoin(Attendance, db.and_(Attendance.subject_id == Subject.id,
Attendance.date < end_date,
Attendance.date >= start_date))\
.options(db.contains_eager(Subject.all_attendances))\
.filter(Subject.id.in_(subject_ids)).all()
calendar = AttendanceCalendar.get_or_create(g.user.company_id, datetime.date.today().year)
result = []
for subject in subjects:
subject_json = subject.get_json()
stats = AttendanceStats()
for _attendance in subject.all_attendances:
if calendar.check_day(_attendance.date):
stats.add_attendance(_attendance.clock_in, _attendance.clock_out, 1)
subject_json['attendance'] = stats.to_dict()
result.append(subject_json)
return success_result(result, page_format(pagination))
def get_photos(company):
people = []
now = g.TIMESTAMP
for subject in company.subjects\
.join(Subject.photos)\
.options(db.contains_eager(Subject.photos))\
.filter(Photo.version==company.feature_version)\
.all():
if subject.subject_type != SubjectType.TYPE_EMPLOYEE and now > subject.end_time:
continue
item = {
'id': str(subject.id),
'photos': [photo.url for photo in subject.photos if photo.url],
'tag': json.dumps(subject.get_data())
}
if len(item['photos']) > 0:
people.append(item)
return people
def export_single(export_name):
params = request.get_json() or request.form or request.args
date = params.get('date')
subject_id = params.get('subject_id')
try:
start_date, end_date = month_range(date)
except:
return error_result(ErrorCode.ERROR_INVALID_PARAM)
event_alias1 = db.aliased(Event)
event_alias2 = db.aliased(Event)
attendances = Attendance.query.outerjoin(event_alias1, Attendance.earliest_event)\
.outerjoin(event_alias2, Attendance.latest_event)\
.filter(Attendance.subject_id == subject_id,
Attendance.date >= start_date,
Attendance.date < end_date)\
.options(db.contains_eager(Attendance.earliest_event, alias=event_alias1),
db.contains_eager(Attendance.latest_event, alias=event_alias2))\
.all()
calendar = AttendanceCalendar.get_or_create(g.user.company_id, datetime.date.today().year)
stats = AttendanceStats()
for attendance in attendances:
if calendar.check_day(attendance.date):
stats.add_attendance(attendance.clock_in, attendance.clock_out, 1)
temp = tempfile.TemporaryFile()
workbook = xlsxwriter.Workbook(temp)
worksheet = workbook.add_worksheet("attendances")
worksheet.set_column('A:K', 15)
worksheet.freeze_panes(4, 0)
xlsx_common = {
'font_color' : '#000000',
'font_size' : 14,
'font_name' : '微软雅黑',
'border' : 1,
'border_color' : '#cccccc',
'align' : 'right',
'valign' : 'bottom'
}
def f(d,xlsx_common=xlsx_common):
xlsx_common_copy = xlsx_common.copy()
xlsx_common_copy.update(d)
return xlsx_common_copy
xlsx_blue = workbook.add_format(f({ 'bg_color' : 'b8dee7', 'bold' : True }))
xlsx_blue2 = workbook.add_format(f({ 'bg_color' : 'b9cce3', 'bold' : True }))
xlsx_green = workbook.add_format(f({ 'bg_color' : 'd8efa9', 'bold' : True }))
xlsx_red = workbook.add_format(f({ 'bg_color' : 'fbd5b6', 'font_color' : 'red', 'bold' : True }))
xlsx_content = workbook.add_format(f(xlsx_common))
xlsx_content_red = workbook.add_format(f({ 'font_color' : 'red' }))
worksheet.write(0, 0, u'%d年%d月个人考勤明细表' % (start_date.year, start_date.month), workbook.add_format(f({ 'align' : 'left' }, xlsx_common)))
col = 0
worksheet.write(1, col, u'正常（天）', xlsx_blue); col += 1
worksheet.write(1, col, u'迟到（次）', xlsx_red); col += 1
worksheet.write(1, col, u'早退（次）', xlsx_red); col += 1
worksheet.write(1, col, u'漏打卡（次）', xlsx_red); col += 1
worksheet.write(1, col, u'缺勤（天）', xlsx_red); col += 1
col = 0
worksheet.write(2, col, stats.normal, xlsx_content); col += 1
worksheet.write(2, col, stats.late, xlsx_content_red); col += 1
worksheet.write(2, col, stats.leave_early, xlsx_content_red); col += 1
worksheet.write(2, col, stats.unchecked, xlsx_content_red); col += 1
worksheet.write(2, col, stats.absenteeism, xlsx_content_red); col += 1
col = 0
worksheet.write(3, col, u'日期', workbook.add_format(f({ 'align' : 'left', 'bg_color' : 'b8dee7', 'bold' : True }, xlsx_common))); col += 1
worksheet.write(3, col, u'最早打卡时间', xlsx_blue); col += 1
worksheet.write(3, col, u'迟到分钟数', xlsx_blue); col += 1
worksheet.write(3, col, u'最晚打卡时间', xlsx_blue); col += 1
worksheet.write(3, col, u'早退分钟数', xlsx_blue); col += 1
worksheet.write(3, col, u'工作时长', xlsx_blue); col += 1
worksheet.write(3, col, u'考勤状态', xlsx_blue); col += 1
attendances = {attendance.date: attendance for attendance in attendances}
normal_work_time = json.loads(g.user.company.normal_time)
row = 4
date = start_date
while date < end_date:
col = 0
worksheet.write(row, col, date.strftime('%m/%d'), workbook.add_format(f({ 'align' : 'left' }, xlsx_common))); col += 1
if date in attendances:
attendance = attendances[date]
if attendance.earliest_record is not None:
event = attendance.earliest_event
worksheet.write(row, col, time.strftime('%H:%M', time.localtime(event.timestamp)), xlsx_content); col += 1
dt = datetime.datetime.fromtimestamp(event.timestamp)
standard_time = dt.replace(hour=normal_work_time[0][0], minute=normal_work_time[0][1], second=0)
diff_time = max((dt - standard_time).total_seconds(), 0)
if diff_time:
worksheet.write(row, col, u'%d' % (diff_time/60), xlsx_content)
col += 1
else:
col += 2
if attendance.latest_record is not None:
event = attendance.latest_event
worksheet.write(row, col, time.strftime('%H:%M', time.localtime(event.timestamp)), xlsx_content); col += 1
dt = datetime.datetime.fromtimestamp(event.timestamp)
standard_time = dt.replace(hour=normal_work_time[1][0], minute=normal_work_time[1][1], second=0)
diff_time = max((standard_time - dt).total_seconds(), 0)
if diff_time:
worksheet.write(row, col, u'%d' % (diff_time/60), xlsx_content)
col += 1
else:
col += 2
#工作时长
worktime = datetime.datetime.utcfromtimestamp(attendance.worktime if attendance.worktime is not None else 0)
worksheet.write(row, col, worktime.strftime(u'%H小时%M分'), xlsx_content); col += 1
# 考勤状态
statuses = AttendanceStats.calc_statuses(attendance.clock_in, attendance.clock_out)
statuses_text = ','.join([AttendanceStatus.get_desc(status) for status in statuses])
worksheet.write(row, col, statuses_text, xlsx_content); col += 1
if not calendar.check_day(date):
worksheet.write(row, 6, u'休息日', xlsx_content)
date = date + timedelta(days=1)
row += 1
workbook.close()
temp.seek(0)
sio = StringIO(temp.read())
temp.close()
return send_file(sio, mimetype='application/octet-stream', attachment_filename=export_name)
def export_monthly(export_name):
params = request.get_json() or request.args
date = params.get('date')
search = params.get('search')
try:
start_date, end_date = month_range(date)
begin_weekday = start_date.weekday()
except:
return error_result(ErrorCode.ERROR_INVALID_PARAM)
days = (end_date - start_date).days
query = Subject.query.outerjoin(Attendance, db.and_(Attendance.subject_id == Subject.id,
Attendance.date < end_date,
Attendance.date >= start_date))\
.options(db.contains_eager(Subject.all_attendances))\
.filter(Subject.company_id == g.user.company_id,
Subject.subject_type == SubjectType.TYPE_EMPLOYEE)
if search:
search = search.replace('\\', '\\\\')
subjects = db.session.query(Subject.id).filter(Subject.company_id == g.user.company_id,
db.or_(Subject.real_name.contains(search),
Subject.pinyin.contains(search),
)).all()
if not subjects:
subjects = db.session.query(Subject.id).filter(Subject.company_id == g.user.company_id,
Subject.department.contains(search)).all()
subject_ids = [id for id, in subjects]
query = query.filter(Subject.id.in_(subject_ids))
subjects = query.all()
temp = tempfile.TemporaryFile()
workbook = xlsxwriter.Workbook(temp)
worksheet = workbook.add_worksheet("attendances")
worksheet.freeze_panes(3, 9)
worksheet.set_column('A:AN', 15)
DAILY_START = 9
xlsx_common = {
'font_color' : '#000000',
'font_size' : 14,
'font_name' : '微软雅黑',
'border' : 1,
'border_color' : '#cccccc',
'align' : 'right',
'valign' : 'bottom'
}
def f(d):
xlsx_common = {
'font_color': '#000000',
'font_size': 14,
'font_name': '微软雅黑',
'border': 1,
'border_color': '#cccccc',
'align': 'right',
'valign': 'bottom'
}
xlsx_common.update(d)
return xlsx_common
xlsx_blue = workbook.add_format(f({ 'bg_color' : 'b8dee7', 'bold' : True }))
xlsx_blue_left = workbook.add_format(f({ 'bg_color' : 'b8dee7', 'bold' : True, 'align' : 'left' }))
xlsx_blue2 = workbook.add_format(f({ 'bg_color' : 'b9cce3', 'bold' : True }))
xlsx_green = workbook.add_format(f({ 'bg_color' : 'd8efa9', 'bold' : True }))
xlsx_red = workbook.add_format(f({ 'bg_color' : 'fbd5b6', 'font_color' : 'red', 'bold' : True }))
xlsx_content = workbook.add_format(xlsx_common)
xlsx_content_red = workbook.add_format(f({ 'font_color' : 'red' }))
worksheet.merge_range(0, 0, 0, 1, u'%d年%d月考勤详细表' % (start_date.year, start_date.month), workbook.add_format(f({ 'align' : 'left' })))
WEEKDAYS = u"一二三四五六天"
for i in xrange(0, days):
worksheet.write(1, DAILY_START + i, u'%d月%s日' % (start_date.month, i + 1), xlsx_green)
worksheet.write(2, DAILY_START + i, u'星期' + WEEKDAYS[(begin_weekday + i) % 7], xlsx_blue)
TITLES = [ u'姓名', u'部门', u'工号', u'正常天数', u'迟到次数', u'早退次数', u'漏打卡次数', u'缺勤天数', u'时间']
for i in xrange(0, DAILY_START):
if i==0:
worksheet.merge_range(1, i, 2, i, TITLES[i], xlsx_blue_left)
elif i < 4 or i == DAILY_START - 1:
worksheet.merge_range(1, i, 2, i, TITLES[i], xlsx_blue)
else:
worksheet.merge_range(1, i, 2, i, TITLES[i], xlsx_red)
calendar = AttendanceCalendar.get_or_create(g.user.company_id, datetime.date.today().year)
for i, subject in enumerate(subjects):
row_index = i * 2 + 3
stats = AttendanceStats()
worksheet.write(row_index, DAILY_START - 1, u'上班', xlsx_content)
worksheet.write(row_index + 1, DAILY_START - 1, u'下班', xlsx_content)
for _attendance in subject.all_attendances:
if calendar.check_day(_attendance.date):
stats.add_attendance(_attendance.clock_in, _attendance.clock_out, 1)
day = (_attendance.date - start_date).days
clock_in_status = AttendanceStatus.get_desc(_attendance.clock_in)
clock_out_status = AttendanceStatus.get_desc(_attendance.clock_out)
if clock_in_status == u'正常':
clock_in_status = u'√'
if clock_out_status == u'正常':
clock_out_status = u'√'
worksheet.write(row_index, DAILY_START + day, clock_in_status, xlsx_content)
worksheet.write(row_index + 1, DAILY_START + day, clock_out_status, xlsx_content)
# 合并单元格
for i in xrange(DAILY_START - 1):
worksheet.merge_range(row_index, i, row_index + 1, i, '')
col = 0
worksheet.write(row_index, col, subject.real_name, workbook.add_format(f({ 'align' : 'left' }))); col += 1
worksheet.write(row_index, col, subject.department, xlsx_content); col += 1
worksheet.write(row_index, col, subject.job_number, xlsx_content); col += 1
worksheet.write(row_index, col, stats.normal, xlsx_content); col += 1
worksheet.write(row_index, col, stats.late, xlsx_content_red); col += 1
worksheet.write(row_index, col, stats.leave_early, xlsx_content_red); col += 1
worksheet.write(row_index, col, stats.unchecked, xlsx_content_red); col += 1
worksheet.write(row_index, col, stats.absenteeism, xlsx_content_red); col += 1
workbook.close()
temp.seek(0)
sio = StringIO(temp.read())
temp.close()
return send_file(sio, mimetype='application/octet-stream', attachment_filename=export_name)