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 render(self, **prop):
self.dom_id = prop.get("id") # client dom id
self.cols = prop.get("cols") # entity field list
self.titles = prop.get("titles") # title list
self.entity_full_name = prop.get("entity") # entity name
autoform = prop.get("autoform")
checkable = prop.get("checkable")
search_fields = prop.get("search_fields") # default search field name,and text type,
search_tip = prop.get("search_tip")
point = EmptyClass()
point.list = prop.get("point_list")
point.view = prop.get("point_view")
point.add = prop.get("point_add")
point.update = prop.get("point_update")
point.delete = prop.get("point_delete")
if not self.entity_full_name:
return "Require entity full name."
self.datatable_key = Utils.md5(self.entity_full_name)
DataTableModule.__security_points__[self.datatable_key] = point
if search_fields:
DataTableModule.__default_search_fields__[self.datatable_key] = search_fields
else:
DataTableModule.__default_search_fields__[self.datatable_key] = []
sub = dict()
sub["name"] = self.entity_full_name
sub["cols"] = self.cols
DataTableModule.__entity_mapping__[self.datatable_key] = sub
tag = ""
if checkable:
tag += (
"<th><input type='checkbox' style='width: 13px; height: 13px;' onclick='%s_.CheckAll(this);'/></th>"
% self.dom_id
)
for title in self.titles:
tag += "<th>" + title + "</th>"
tag += "<th>#</th>"
opt = dict()
opt["point"] = point
opt["id"] = self.dom_id
opt["autoform"] = autoform
opt["checkable"] = checkable
if not search_tip:
search_tip = ""
opt["search_tip"] = search_tip
opt["thTags"] = tag
opt["entity_name_md5"] = self.datatable_key
if autoform:
opt["cols"] = self.create_editform()
html_col = list()
index = 0
for col in self.cols:
html_col.append(
{"mData": col, "sTitle": self.titles[index], "sClass": "datatable_column_" + col, "sDefaultContent": ""}
)
index += 1
opt["col_defs"] = json.dumps(html_col)
self.create_editform()
return self.render_string("widgets/datatable_html.html", opt=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())
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