def list(self, kw, start, limit, http_req):
sf = SessionFactory.new()
subq = sf.query(Archives.id, Archives.name).subquery()
q = sf.query(ValidWorkAskForLeave, Archives.name, subq.c.name.label("creator"))\
.join(Archives, ValidWorkAskForLeave.archives_id == Archives.id)\
.outerjoin(subq, ValidWorkAskForLeave.creator == subq.c.id)
if kw:
q = q.filter(Archives.name.contains(kw))
dataset = q.order_by(ValidWorkAskForLeave.id.desc()).offset(start).limit(limit).all()
items = list()
for row in dataset:
ot = row[0]
name = row[1]
creator = row[2]
item = dict()
item["id"] = ot.id
item["kind"] = ot.kind
item["ask_date"] = Utils.format_date(ot.ask_date)
item["start_datetime"] = Utils.format_datetime_short(ot.start_datetime)
item["end_datetime"] = Utils.format_datetime_short(ot.end_datetime)
item["create_datetime"] = Utils.format_datetime_short(ot.create_datetime)
item["name"] = name
item["creator"] = creator
items.append(item)
return items
def count(self, search_text, http_req):
current_date = None
if not current_date:
current_date = Utils.format_date(Utils.current_datetime())
sf = SessionFactory.new()
subq = sf.query(Term.name.label("term_name"), TermTaxonomy.id).filter(TermTaxonomy.term_id == Term.id).subquery()
q = sf.query(func.count(ValidWorkCheckOn.id))\
.join(Archives, ValidWorkCheckOn.archives_id == Archives.id)\
.outerjoin(subq, subq.c.id == Archives.org_id)
if search_text:
q = q.filter(Archives.name.contains(search_text))
q = q.filter(cast(ValidWorkCheckOn.valid_start_time, Date) == current_date)
return q.scalar()
def list(self, search_text, start, limit, http_req):
current_date = None
if not current_date:
current_date = Utils.format_date(Utils.current_datetime())
sf = SessionFactory.new()
subq = sf.query(Term.name.label("term_name"), TermTaxonomy.id).filter(TermTaxonomy.term_id == Term.id).subquery()
q = sf.query(ValidWorkCheckOn.id,
Archives.code,
Archives.name,
ValidWorkTimeBlock.name,
ValidWorkTimeBlock.start_time,
ValidWorkTimeBlock.end_time,
ValidWorkCheckOn.status_in,
ValidWorkCheckOn.status_out,
ValidWorkCheckOn.status_no_sign,
ValidWorkCheckOn.check_in_time,
ValidWorkCheckOn.check_out_time,
subq.c.term_name,
MinuteDiff(ValidWorkCheckOn.valid_start_time, ValidWorkCheckOn.valid_end_time).label("diff")
).select_from(ValidWorkCheckOn)\
.join(ValidWorkTimeBlock, ValidWorkCheckOn.time_block_id == ValidWorkTimeBlock.id)\
.join(Archives, ValidWorkCheckOn.archives_id == Archives.id)\
.outerjoin(subq, subq.c.id == Archives.org_id)
if search_text:
q = q.filter(Archives.name.contains(search_text))
q = q.order_by(Archives.name).filter(cast(ValidWorkCheckOn.valid_start_time, Date) == current_date)
ds = q.offset(start).limit(limit).all()
items = list()
for row in ds:
obj = EmptyClass()
obj.id = row[0]
obj.code = row[1]
obj.name = row[2]
obj.tb_name = row[3]
obj.start_time = Utils.format_time(row[4])
obj.end_time = Utils.format_time(row[5])
obj.status_in = row[6]
obj.status_out = row[7]
obj.status_no_sign = row[8]
obj.check_in_time = Utils.format_time(row[9])
obj.check_out_time = Utils.format_time(row[10])
obj.org_name = row[11]
obj.no_work_timediff = row[12]
items.append(obj.__dict__)
return items
def count(self, search_text, http_req):
current_date = None
if not current_date:
current_date = Utils.format_date(Utils.current_datetime())
cur_datetime = Utils.parse_datetime(current_date+" 00:00")
year = cur_datetime.year
month = cur_datetime.month
sf = SessionFactory.new()
#某月考勤人员，分组统计
q = sf.query(func.count(ValidWorkCheckOn.id))\
.join(Archives, ValidWorkCheckOn.archives_id == Archives.id)\
.filter(func.YEAR(ValidWorkCheckOn.valid_start_time) == year)\
.filter(func.MONTH(ValidWorkCheckOn.valid_start_time) == month)\
.group_by(ValidWorkCheckOn.archives_id, Archives.name)
if search_text:
q = q.filter(Archives.name.contains(search_text))
return q.scalar()
def get(self, *args, **kwargs):
current_date = Utils.format_date(Utils.current_datetime())+" 00:00"
opt = dict()
opt["current_date"] = current_date
return self.render("validwork/overtime.html", context=opt)
def list(self, search_text, start, limit, http_req):
current_date = None
if not current_date:
current_date = Utils.format_date(Utils.current_datetime())
cur_datetime = Utils.parse_datetime(current_date+" 00:00")
year = cur_datetime.year
month = cur_datetime.month
sf = SessionFactory.new()
#group by all people with the month
checkon_subq = sf.query(ValidWorkCheckOn.archives_id)\
.join(Archives, ValidWorkCheckOn.archives_id == Archives.id)\
.filter(func.YEAR(ValidWorkCheckOn.valid_start_time) == year)\
.filter(func.MONTH(ValidWorkCheckOn.valid_start_time) == month)\
.group_by(ValidWorkCheckOn.archives_id).subquery()
#迟到,早退,旷工,请假,加班分组统计
late_subq = sf.query(ValidWorkCheckOn.archives_id, (func.count(1)).label("total"))\
.filter(ValidWorkCheckOn.status_in == 1)\
.filter(func.YEAR(ValidWorkCheckOn.valid_start_time) == year)\
.filter(func.MONTH(ValidWorkCheckOn.valid_start_time) == month)\
.group_by(ValidWorkCheckOn.archives_id).subquery()
early_leave_subq = sf.query(ValidWorkCheckOn.archives_id, (func.count(1)).label("total"))\
.filter(ValidWorkCheckOn.status_out == 1)\
.filter(func.YEAR(ValidWorkCheckOn.valid_start_time) == year)\
.filter(func.MONTH(ValidWorkCheckOn.valid_start_time) == month)\
.group_by(ValidWorkCheckOn.archives_id).subquery()
no_work_subq = sf.query(ValidWorkCheckOn.archives_id,
(func.sum(MinuteDiff(ValidWorkCheckOn.valid_start_time,
ValidWorkCheckOn.valid_end_time))/(60*24)).label("total"))\
.filter(ValidWorkCheckOn.status_no_sign == 1)\
.filter(func.YEAR(ValidWorkCheckOn.valid_start_time) == year)\
.filter(func.MONTH(ValidWorkCheckOn.valid_start_time) == month)\
.group_by(ValidWorkCheckOn.archives_id).subquery()
#事假
askforleave_subq1 = sf.query(ValidWorkAskForLeave.archives_id,
(func.sum(MinuteDiff(ValidWorkAskForLeave.start_datetime,
ValidWorkAskForLeave.end_datetime))/(60*24)).label("total"))\
.filter(func.YEAR(ValidWorkAskForLeave.start_datetime) == year)\
.filter(func.MONTH(ValidWorkAskForLeave.start_datetime) == month)\
.filter(ValidWorkAskForLeave.kind == 0)\
.group_by(ValidWorkAskForLeave.archives_id).subquery()
#病假
askforleave_subq2 = sf.query(ValidWorkAskForLeave.archives_id,
(func.sum(MinuteDiff(ValidWorkAskForLeave.start_datetime,
ValidWorkAskForLeave.end_datetime))/(60*24)).label("total"))\
.filter(func.YEAR(ValidWorkAskForLeave.start_datetime) == year)\
.filter(func.MONTH(ValidWorkAskForLeave.start_datetime) == month)\
.filter(ValidWorkAskForLeave.kind == 1)\
.group_by(ValidWorkAskForLeave.archives_id).subquery()
#其它假
askforleave_subq3 = sf.query(ValidWorkAskForLeave.archives_id,
(func.sum(MinuteDiff(ValidWorkAskForLeave.start_datetime,
ValidWorkAskForLeave.end_datetime))/(60*24)).label("total"))\
.filter(func.YEAR(ValidWorkAskForLeave.start_datetime) == year)\
.filter(func.MONTH(ValidWorkAskForLeave.start_datetime) == month)\
.filter(ValidWorkAskForLeave.kind == 2)\
.group_by(ValidWorkAskForLeave.archives_id).subquery()
overtime_subq = sf.query(ValidWorkOvertime.archives_id,
(func.sum(MinuteDiff(ValidWorkOvertime.start_datetime,
ValidWorkOvertime.end_datetime))/60).label("total"))\
.filter(func.YEAR(ValidWorkOvertime.start_datetime) == year)\
.filter(func.MONTH(ValidWorkOvertime.start_datetime) == month)\
.group_by(ValidWorkOvertime.archives_id).subquery()
term_subq = sf.query(Term.name.label("term_name"), TermTaxonomy.id)\
.filter(TermTaxonomy.term_id == Term.id).subquery()
subq = sf.query(Archives.id, Archives.code, Archives.name, term_subq.c.term_name)\
.select_from(Archives).outerjoin(term_subq, Archives.org_id == term_subq.c.id).subquery()
q = sf.query(checkon_subq.c.archives_id,
subq.c.code,
subq.c.name,
late_subq.c.total,
early_leave_subq.c.total,
no_work_subq.c.total,
askforleave_subq1.c.total,
askforleave_subq2.c.total,
askforleave_subq3.c.total,
overtime_subq.c.total,
subq.c.term_name).select_from(checkon_subq)\
.outerjoin(late_subq, checkon_subq.c.archives_id == late_subq.c.archives_id)\
.outerjoin(early_leave_subq, checkon_subq.c.archives_id == early_leave_subq.c.archives_id)\
.outerjoin(no_work_subq, checkon_subq.c.archives_id == no_work_subq.c.archives_id)\
.outerjoin(askforleave_subq1, checkon_subq.c.archives_id == askforleave_subq1.c.archives_id)\
.outerjoin(askforleave_subq2, checkon_subq.c.archives_id == askforleave_subq2.c.archives_id)\
.outerjoin(askforleave_subq3, checkon_subq.c.archives_id == askforleave_subq3.c.archives_id)\
.outerjoin(overtime_subq, checkon_subq.c.archives_id == overtime_subq.c.archives_id)\
.join(subq, checkon_subq.c.archives_id == subq.c.id)
if search_text:
q = q.filter(checkon_subq.c.name.contains(search_text))
ds = q.order_by(checkon_subq.c.archives_id).offset(start).limit(limit).all()
items = list()
for row in ds:
obj = EmptyClass()
obj.id = row[0]
obj.code = row[1]
obj.name = row[2]
obj.late_total = row[3]
obj.early_leave_total = row[4]
obj.no_work_total = row[5]
obj.askforleave_total1 = row[6]
obj.askforleave_total2 = row[7]
obj.askforleave_total3 = row[8]
obj.overtime_total = row[9]
obj.org_name = row[10]
items.append(obj.__dict__)
return items
def organization_of_work():
current_datetime = Utils.current_datetime()
#是否为法定节日
isholiday = ValidWorkSchedulerThread.is_holiday(Utils.format_date(current_datetime))
if isholiday:
return
sf = SessionFactory.new()
#标识-1的状态为旷工，这种情况是没有按指纹的，CheckOn 有效时间段结束时间已经过期的时候
updates = sf.query(ValidWorkCheckOn) \
.filter(or_(ValidWorkCheckOn.status_in == -1, ValidWorkCheckOn.status_out == -1)) \
.filter(ValidWorkCheckOn.valid_end_time < Utils.format_datetime(current_datetime)).all()
for row in updates:
row.status_no_sign = 1
sf.commit()
#列出所有考勤计划
tasks = sf.query(ValidWorkScheduleTask.id).all()
tasks = [task[0] for task in tasks]
for task_id in tasks:
#得到班次最小的时间点
#min_time = sf.query(ValidWorkTimeBlock.start_time) \
# .join(ValidWorkScheduleTask, ValidWorkTimeBlock.validworkscheduletasks) \
# .order_by(asc(ValidWorkTimeBlock.start_time)) \
# .filter(ValidWorkScheduleTask.id == task_id).limit(1).scalar()
#if min_time:
# #提前30分钟安排下一档工作
# min_datetime = datetime.combine(current_datetime.date(), min_time)
# start_datetime = min_datetime - timedelta(minutes=30)
# if start_datetime <= current_datetime <= min_datetime:
#当天是否已经安排完成
e = sf.query(func.count(ValidWorkCheckOn.id)) \
.filter(ValidWorkCheckOn.task_id == task_id) \
.filter(cast(ValidWorkCheckOn.valid_start_time, Date) == Utils.format_date(current_datetime)).scalar()
if e == 0:
#安排新的工作
#1,得到拥有此考勤计划的所有人员ID
usrs = sf.query(Archives.id).join(ValidWorkScheduleTask, Archives.validworkscheduletasks) \
.filter(ValidWorkScheduleTask.id == task_id).all()
usrs = [u[0] for u in usrs]
#2,得到此考勤计划的所有班次
time_blocks = sf.query(ValidWorkTimeBlock) \
.join(ValidWorkScheduleTask, ValidWorkTimeBlock.validworkscheduletasks) \
.filter(ValidWorkScheduleTask.id == task_id).all()
#3,批量插入CheckOn
workers_tb = list()
for usr_id in usrs:
for tb in time_blocks:
vwco = ValidWorkCheckOn()
vwco.archives_id = usr_id
vwco.task_id = task_id
vwco.time_block_id = tb.id
start = datetime.combine(current_datetime.date(), tb.start_time)
end = datetime.combine(current_datetime.date(), tb.end_time)
start = start - timedelta(minutes=tb.normal_in_space)
end = end + timedelta(minutes=tb.normal_out_space)
vwco.valid_start_time = start
vwco.valid_end_time = end
workers_tb.append(vwco)
pass
pass
pass
sf.add_all(workers_tb)
sf.commit()
pass
pass
pass
