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