def get_emp_run_time_temp(emp=None, start_date=None, end_date=None):
'''
返回处理后的时间段数据
例 ( (设定的签到时间, 设点的签退时间，时段ID，开始签到，结束签到，开始签退，结束签退, [ 覆盖类型标识, 工作类型, 排班ID ] ), )
'''
sql = '''
select t.ComeTime,t.LeaveTime,t.Flag,t.SchClassID,s.CheckInTime1, s.CheckInTime2,s.CheckOutTime1,s.CheckOutTime2,t.WorkType,t.id
from user_temp_sch as t,schclass as s
where t.UserID=%s and t.SchClassID = s.SchclassID and (t.LeaveTime>'%s' and t.ComeTime<'%s')
order by t.ComeTime
''' % (emp, start_date.strftime('%Y-%m-%d %H:%M:%S'),
end_date.strftime('%Y-%m-%d %H:%M:%S'))
rows = sql_utils.p_query(sql)
run_time_temp = []
for e in rows:
begin = [e[0]][0]
end = [e[1]][0]
e_ = list(e)
e_[0] = e_[0].time()
e_[1] = e_[1].time()
from get_run import deal_cross_day
result = deal_cross_day(begin, end, e_)
run_time_temp.append((begin, end, e[3], result[0], result[1],
result[2], result[3], [e[2], e[8], e[9]]))
return run_time_temp
def get_emps_by_deptidlist(deptlist, child=False):
from mysite.sql_utils import p_query
if child:
# 包含下级
sql = """
WITH NODES
AS (
SELECT DeptID,supdeptid FROM DBO.departments par WHERE par.DeptID in (%s)
UNION ALL
SELECT child.DeptID,child.supdeptid FROM departments AS child INNER JOIN
NODES AS RC ON child.supdeptid = RC.DeptID)
select userid from userinfo where userinfo.defaultdeptid in( (SELECT DeptID FROM NODES N )
)
""" % deptlist
else:
sql = """
select u.userid from userinfo u
left join departments d on u.defaultdeptid=d.DeptID
where d.DeptID in (%s)
""" % deptlist
res = p_query(sql)
if res:
return [u"%d" % (r[0]) for r in res]
else:
return ["-1"]
def get_setleave(emp, start_date, end_date):
'''
功能: 获取人员在某期间的调休里的休息类型
返回: ((id, datetime.date(2011, 6, 1), datetime.date(2012, 10, 1), 22),)
'''
sql = '''
select id,starttime,endtime from setuseratt
where UserID_id = %s and atttype=2 and (endtime>'%s' and starttime<'%s')
order by starttime
''' % (emp, start_date.strftime('%Y-%m-%d %H:%M:%S'),
end_date.strftime('%Y-%m-%d %H:%M:%S'))
rows = sql_utils.p_query(sql)
return rows
def get_emp_num_run():
'''
获取所有班次
返回" {班次ID : [周期单位, 周期数]}
{id:[Units,Cyle]}
'''
sql = '''
select Num_runID,Units,Cyle from num_run
'''
rows = sql_utils.p_query(sql)
ret = {}
for r in rows:
ret[r[0]] = [r[1], r[2]]
return ret
def get_temp_flex_id(emp=None, start_date=None, end_date=None):
'''
得到临时班次数据中的弹性时段类型ID
'''
sql = '''
select t.id
from user_temp_sch as t,schclass as s
where t.UserID=%s and t.SchClassID = s.SchclassID and (t.LeaveTime>'%s' and t.ComeTime<'%s') and t.SchClassID=1
order by t.ComeTime
''' % (emp, start_date.strftime('%Y-%m-%d %H:%M:%S'),
end_date.strftime('%Y-%m-%d %H:%M:%S'))
rows = sql_utils.p_query(sql)
id_list = [e[0] for e in rows]
return id_list
def get_askleave(emp, start_date, end_date):
'''
功能: 获取人员在某期间的审核通过的请假
返回: ((ID, 开始时间, 结束时间),...)
例 ((id, datetime.date(2011, 6, 1), datetime.date(2012, 10, 1)),...)
'''
sql = '''
select id,StartSpecDay,EndSpecDay from user_speday
where audit_status=2 and UserID=%s and (EndSpecDay>'%s' and StartSpecDay<'%s')
order by StartSpecDay
''' % (emp, start_date.strftime('%Y-%m-%d %H:%M:%S'),
end_date.strftime('%Y-%m-%d %H:%M:%S'))
rows = sql_utils.p_query(sql)
return rows
def get_initial_record(emp,start_date=None,end_date=None):
'''
功能: 获取人员某期间的原始签卡记录
返回: ((ID, 人员ID, 签卡时间, 签卡类型, 引用计数), )
((id, userid, checktime, checktype,counter),)
'''
sql = '''
select c.id,u.userid, c.checktime, c.checktype, 0 as counter
from checkinout as c
left join userinfo as u on u.badgenumber = c.pin
where u.userid=%s and c.checktime>='%s' and c.checktime<='%s'
order by c.checktime
'''%(emp,start_date.strftime('%Y-%m-%d %H:%M:%S'),end_date.strftime('%Y-%m-%d %H:%M:%S'))
rows = sql_utils.p_query(sql)
return rows
def get_emp_run(emp, start_date, end_date):
'''
功能: 获取人员在某期间的【排班】
返回: ( (人员ID, 开始时间, 结束时间, 班次ID, 排班ID), )
例 ((14, datetime.date(2011, 6, 1), datetime.date(2012, 10, 1), 22),)
'''
sql = '''
select UserID,StartDate,EndDate,NUM_OF_RUN_ID,id
from user_of_run
where UserID=%s and (enddate>'%s' and startdate<'%s')
order by user_of_run.StartDate
''' % (emp, start_date.strftime('%Y-%m-%d %H:%M:%S'),
end_date.strftime('%Y-%m-%d %H:%M:%S'))
rows = sql_utils.p_query(sql)
return rows
def check_repeating_data_log(sn):
from mysite.pos.models.model_icconsumerlist import ICConsumerList
check_sql = """
select * from(
select dev_serial_num from pos_icconsumerlist where dev_sn = '%s' and dev_serial_num is not null
union all
select dev_serial_num from dbo.pos_icerrorlog where dev_sn = '%s' and dev_serial_num is not null
) a group by dev_serial_num having count(1) >= 2 order by dev_serial_num
"""
# pos_data = ICConsumerList.objects.filter(dev_sn = sn,type_name__in=[6,10,9]).values_list('dev_serial_num','user_pin').order_by('dev_serial_num')
pos_data = p_query(check_sql%(sn,sn))
if pos_data:
print "repeating_data==",pos_data
return pos_data
else:
print "not repeating data"
return "not repeating data"
def get_run_detail(num_run_ID):
'''
功能: 获取某个班次的班次详细
返回：( (开始时间, 结束时间, 第几天, 时段ID, 开始签到时间, 结束签到时间, 开始签退时间, 结束签退时间) )
((datetime.time(7, 0), datetime.time(15, 0), 0, 15, ...),
(datetime.time(23, 0), datetime.time(7, 0), 0, 17, ...),
(datetime.time(19, 0), datetime.time(7, 0), 1, 14, ...),
(datetime.time(19, 0), datetime.time(7, 0), 2, 14, ...), ...
'''
sql = '''
select d.StartTime,d.EndTime,d.Sdays,d.SchclassID,s.CheckInTime1, s.CheckInTime2,s.CheckOutTime1,s.CheckOutTime2
from num_run_deil as d,schclass as s
where num_runid = %s and d.SchclassID = s.SchclassID
order by d.Sdays,d.StartTime
''' % num_run_ID
rows = sql_utils.p_query(sql)
return rows
def get_holiday(start_date=None, end_date=None):
'''
功能: 获取在某期间的节假日
返回: ((id, datetime.date(2011, 6, 1), datetime.date(2012, 10, 1), 22),)
'''
sql = '''
select HolidayID,StartTime,Duration from holidays
order by StartTime
'''
rows = sql_utils.p_query(sql)
result = []
for e in rows:
StartTime = datetime.datetime(e[1].year, e[1].month, e[1].day, 0, 0, 0)
EndTime = datetime.datetime(e[1].year, e[1].month, e[1].day, 23, 59,
59) + datetime.timedelta(days=e[2] - 1)
result.append((e[0], StartTime, EndTime))
return result
def load_emp_face(key):
u"""
根据人员 PIN 获取该人员面部信息
"""
pin, ver = key.split('|')
pin = format_pin(pin)
sql = """
select faceid,facetemp from face_template
where face_template.pin = '%s' and face_template.face_ver = '%s'
""" % (pin, ver)
temps = p_query(sql)
res = {}
if temps:
for e in temps:
res["face" + str(e[0])] = e[1]
else:
res = None
return res
def load_emp_fg(key):
u"""
根据人员 PIN 获取该人员指纹信息
"""
pin, ver = key.split('|')
pin = format_pin(pin)
sql = """
select FingerID,Template from finger_template
where finger_template.pin = '%s' and finger_template.Fpversion= '%s'
""" % (pin, ver)
temps = p_query(sql)
res = {}
if temps:
for e in temps:
res["fp" + str(e[0])] = e[1]
else:
res = None
return res
def MakeData(self, request, **arg):
userids, d1, d2 = parse_grid_arg(request)
if userids and d1 and d1:
params = {"userids": ','.join(userids), "st": d1, "et": d2}
userids_sql = sql_utils.get_sql('sql',
sqlid='exception_emp_ids',
app='att',
params=params)
rows = sql_utils.p_query(userids_sql)
if rows:
userids = [r[0] for r in rows]
else:
userids = []
self.Paging(arg['offset'], item_count=len(userids))
userids = userids[self.grid._begin:self.grid._end]
from emp_total import ForMakeDataExcepSum
ForMakeDataExcepSum(self, userids, d1, d2)
else:
self.SetBlank()
def check_device_pos_log(sn):
from mysite.pos.models.model_icconsumerlist import ICConsumerList
check_sql = """
select * from(
select dev_serial_num from pos_icconsumerlist where dev_sn = '%s' and dev_serial_num is not null
union all
select dev_serial_num from dbo.pos_icerrorlog where dev_sn = '%s' and dev_serial_num is not null
) a group by dev_serial_num order by dev_serial_num
"""
# pos_data = ICConsumerList.objects.filter(dev_sn = sn,type_name__in=[6,10,9]).values_list('dev_serial_num','user_pin').order_by('dev_serial_num')
pos_data = p_query(check_sql%(sn,sn))
pos_data_bak = pos_data
item = 0
data_len = len(pos_data)
b = pos_data[0][0]
e = pos_data[data_len-1][0]
err_log = []
for i in range(b,e):
if(i!=int(pos_data[item][0])):
item = item-1
err_log.append(i)
item = item + 1
print "end=================",item,err_log
def filter_userid(userids, sql_where):
"""
根据数据库 条件 过滤人员id ，返回过滤后的人员id
"""
from mysite.sql_utils import p_query
ret = []
if userids and sql_where:
lens = len(userids)
batch = 900
times = lens % batch == 0 and lens / batch or (lens / batch + 1)
in_str = "userid in (%s)" % (") or userid in (".join([
str(userids[i * batch:(i + 1) * batch]).replace("[", "").replace(
"]", "").replace("u", "") for i in range(times)
]))
select_sql = """
select userid from userinfo
where %s and %s
""" % (sql_where, in_str)
# print "select_sql=", select_sql
res = p_query(select_sql)
if res:
ret = [i[0] for i in res]
return ret
def outputattrate(request):
from dbapp.datautils import QueryData
json_data=[]
try:
from mysite.personnel.models.model_emp import Employee
emCounts = Employee_objects(request).count()
if emCounts==0:
return getJSResponse(json.dumps([]))
from mysite.iclock.models.model_trans import Transaction
from mysite.att.models.model_empspecday import EmpSpecDay
from mysite.sql_utils import p_query
curr_dt= datetime.datetime.now()
d1= datetime.datetime(curr_dt.year,curr_dt.month,curr_dt.day,0,0,1)
d2= datetime.datetime(curr_dt.year,curr_dt.month,curr_dt.day,23,59,59)
d3 = datetime.datetime(curr_dt.year,curr_dt.month,curr_dt.day)
sql_emps_count = """
select count(1) from userinfo u where u.isatt = 1 and u.status = 0
"""
sql_trans_emps = """
select count(1) from
(select distinct u.userid from checkinout c
inner join userinfo u on u.badgenumber = c.pin
where u.isatt = 1 and u.status = 0
and c.checktime between '%s' and '%s') as t
"""%(d1,d2)
sql_spe_cont="""
select count(1) from
(select distinct u.userid from user_speday us
inner join userinfo u on us.UserID = u.userid
where u.isatt = 1 and u.status=0
and us.EndSpecDay >= '%s'
and us.StartSpecDay <= '%s') as t
"""%(d1,d2)
emps_att_count = p_query(sql_emps_count)[0][0]
qs_atts_emp = p_query(sql_trans_emps)[0][0]
specialDays_count = p_query(sql_spe_cont)[0][0]
absents= emps_att_count -qs_atts_emp - specialDays_count
# qs_atts_emp=Transaction.objects.select_related().filter(TTime__range=(d1,d2),UserID__isatt=True).distinct().values("UserID__PIN").values_list("UserID")
#atts = Transaction.objects.select_related().filter(TTime__range=(d1,d2),UserID__isatt=True).distinct().values("UserID__PIN").count()
# atts,cl=QueryData(request,Transaction,None,qs_atts_emp)
# atts=atts.count()
#
# specialDays = EmpSpecDay.objects.select_related() \
# .filter(Q(end__gte=d3,start__lte=d3)
# |Q(start__year=d3.year,start__month=d3.month,start__day=d3.day)
# |Q(end__year=d3.year,end__month=d3.month,end__day=d3.day)
# ) \
# .exclude(emp__in=qs_atts_emp)
# specialDays,cl=QueryData(request,EmpSpecDay,None,specialDays)
#
# specialDays=specialDays.distinct().values("emp__PIN").count()
# emps,cl=QueryData(request,EmpSpecDay,None,Employee_objects(request).filter(isatt='1'))
# absents= emps.count() -atts - specialDays
json_data.append({
"label":u"%s"%_(u'签到 ')+ str(qs_atts_emp),
"data":[[20,qs_atts_emp]]
})
json_data.append({
"label":u"%s"%_(u'其他 ')+str(absents),
"data":[[20,absents]]
})
json_data.append({
"label":u"%s"%_(u'请假 ') + str(specialDays_count),
"data":[[20,specialDays_count]]
})
json_data.sort(lambda x1,x2:x1["data"][0][1]-x2["data"][0][1])
return getJSResponse(json.dumps(json_data))
except:
import traceback; traceback.print_exc()
finally:
pass
return getJSResponse(json.dumps([]))
def _ExecSql(self, sql):
"""
执行sql语言
"""
from mysite.sql_utils import p_query
return p_query(sql)
def get_photourl_by_uid(uid):
photo_select = """
select photo from userinfo where userid = %s
""" % uid
res = p_query(photo_select)
return res and res[0][0] or None
def ForMakeData(hander, userids, d1, d2, reportType=0):
'''考勤汇总表/每日考勤统计表'''
from mysite.personnel.models import Employee
from mysite.att.models import AttException, attShifts, EmpSpecDay
from mysite.att.calculate.global_cache import C_ATT_RULE, C_LEAVE_CLASS
from mysite.att.att_param import GetLeaveClasses, GetRptIndex
from mysite.att.report_utils import NormalAttValue, SaveValue, formatdTime
import mysite.att.report_sql as report_sql
from mysite.att.models.modelproc import customSql
from mysite.sql_utils import p_query
C_ATT_RULE.action_init()
C_LEAVE_CLASS.action_init()
AbnomiteRptItems = GetLeaveClasses()
AttAbnomiteRptIndex = GetRptIndex(AbnomiteRptItems)
auHour = 1
aaBLeave = 1003
ct = 0
hander.grid.InitItems()
useridstr = userids and ",".join(userids) or "-1"
user_sql = """
select u.userid,u.badgenumber,u.name,d.DeptID,d.DeptName,d.code from userinfo u
left join departments d on u.defaultdeptid = d.DeptID
where u.userid in (%s)
""" % useridstr
userinfo_res = p_query(user_sql)
userinfo_dict = {}
for u in userinfo_res:
if not userinfo_dict.has_key(u[0]):
userinfo_dict[u[0]] = u
for uid in userids:
ct += 1
rmdAttday = hander.grid.NewItem()
uid = int(uid)
sql = report_sql.get_calc_report_sql(uid, d1, d2)
cs = customSql(sql, False)
desc = cs.description
fldNames = {}
i = 0
for c in desc:
fldNames[c[0].lower()] = i
i = i + 1
rows = cs.fetchall()
if not len(rows) > 0:
rmdAttday['userid'] = uid
rmdAttday['deptid'] = userinfo_dict[uid][4]
rmdAttday['badgenumber'] = userinfo_dict[uid][1]
rmdAttday['username'] = userinfo_dict[uid][2]
try:
##################################### 常规计算结果汇总统计 ################################
for t in rows:
if not rmdAttday['userid']:
rmdAttday['userid'] = t[fldNames['userid']]
rmdAttday['deptid'] = t[fldNames['deptname']]
rmdAttday['badgenumber'] = t[fldNames['pin']]
rmdAttday['username'] = t[fldNames['name']]
################# 应到、实到、迟到、早退、旷工 ###################
rmdAttday['duty'] = SaveValue(rmdAttday['duty'],
t[fldNames['workday']])
rmdAttday['realduty'] = SaveValue(rmdAttday['realduty'],
t[fldNames['realworkday']])
rmdAttday['late'] = SaveValue(rmdAttday['late'],
t[fldNames['late']])
rmdAttday['early'] = SaveValue(rmdAttday['early'],
t[fldNames['early']])
if t[fldNames['absent']] > 0:
try:
rmdAttday['absent'] = SaveValue(
rmdAttday['absent'], t[fldNames['absent']])
except:
import traceback
traceback.print_exc()
######################### 加班时间计算 #########################
rmdAttday['overtime'] = SaveValue(rmdAttday['overtime'],
t[fldNames['overtime']])
rmdAttday['SSpeDayNormalOT'] = SaveValue(
rmdAttday['SSpeDayNormalOT'],
t[fldNames['sspedaynormalot']])
rmdAttday['SSpeDayWeekendOT'] = SaveValue(
rmdAttday['SSpeDayWeekendOT'],
t[fldNames['sspedayweekendot']])
rmdAttday['SSpeDayHolidayOT'] = SaveValue(
rmdAttday['SSpeDayHolidayOT'],
t[fldNames['sspedayholidayot']])
################# 应签次数、应签到、应签退、未签到、未签退、出勤时长、工作时间 ####################
if reportType == 0:
if t[fldNames['mustin']]:
rmdAttday['dutyinout'] = int(
float(SaveValue(rmdAttday['dutyinout'], 1)))
rmdAttday['clockin'] = int(
float(SaveValue(rmdAttday['clockin'], 1)))
if t[fldNames['mustout']]:
rmdAttday['dutyinout'] = int(
float(SaveValue(rmdAttday['dutyinout'], 1)))
rmdAttday['clockout'] = int(
float(SaveValue(rmdAttday['clockout'], 1)))
if t[fldNames['mustin']] and t[
fldNames['starttime']] is None:
rmdAttday['noin'] = int(
float(SaveValue(rmdAttday['noin'], 1)))
if t[fldNames['mustout']] and t[
fldNames['endtime']] is None:
rmdAttday['noout'] = int(
float(SaveValue(rmdAttday['noout'], 1)))
rmdAttday['worktime'] = SaveValue(rmdAttday['worktime'],
t[fldNames['worktime']])
#rmdAttday['workmins']=SaveValue(rmdAttday['workmins'],t[fldNames['workmins']])
######################### 每日考勤情况 #########################
if reportType == 1:
dt = t[fldNames['attdate']]
dof = str(dt.day)
tt = t[fldNames['symbol']]
if tt:
rmdAttday[dof] = rmdAttday[dof] + tt
##################################### 异常计算结果汇总统计 ################################
if len(rows) > 0 and (reportType == 0 or reportType == 1):
attExcept = AttException.objects.filter(UserID=uid,
AttDate__gte=d1,
AttDate__lte=d2)
for ex in attExcept:
if ex.UserID_id != rmdAttday['userid']:
continue
exceptid = EmpSpecDay.objects.get(
pk=ex.ExceptionID).leaveclass.pk #ex.ExceptionID
wd = attShifts.objects.filter(AttDate__exact=ex.AttDate,
UserID=ex.UserID)
wdmins = 0 #当天时段分钟数之和
for w in wd:
wdmins = wdmins + w.AttTime
if exceptid in [-4, -3, -2, -1]:
pass
elif exceptid > 0:
if exceptid in AttAbnomiteRptIndex:
if (reportType == 0) or (reportType == 1):
AbnomiteRptItem = AbnomiteRptItems[
AttAbnomiteRptIndex[exceptid]]
if AbnomiteRptItem['RemaindCount'] == 0:
v = NormalAttValue(
ex.InScopeTime,
AbnomiteRptItem['MinUnit'],
AbnomiteRptItem['Unit'],
AbnomiteRptItem['RemaindProc'], auHour,
wdmins)
else:
v = ex.InScopeTime
v = NormalAttValue(
ex.InScopeTime,
AbnomiteRptItem['MinUnit'],
AbnomiteRptItem['Unit'],
AbnomiteRptItem['RemaindProc'], auHour,
wdmins)
rmdAttday['Leave_' +
str(exceptid)] = SaveValue(
rmdAttday['Leave_' +
str(exceptid)], v)
if AbnomiteRptItem['IsLeave'] == 1: #请假汇总
aaBLeave_RptItem = AbnomiteRptItems[
AttAbnomiteRptIndex[aaBLeave]]
v = NormalAttValue(
ex.InScopeTime,
aaBLeave_RptItem['MinUnit'],
aaBLeave_RptItem['Unit'],
aaBLeave_RptItem['RemaindProc'],
auHour, wdmins)
rmdAttday['Leave'] = SaveValue(
rmdAttday['Leave'], v)
################## 计算结果的后处理 ################
if reportType == 0:
rmdAttday['worktime'] = formatdTime(rmdAttday['worktime'])
#rmdAttday['workmins']=formatdTime(rmdAttday['workmins'])
for ttt in rmdAttday.keys():
if type(rmdAttday[ttt]) == type(1.0):
if rmdAttday[ttt] > int(rmdAttday[ttt]):
rmdAttday[ttt] = smart_str(rmdAttday[ttt])
except:
import traceback
traceback.print_exc()
hander.grid.AddItem(rmdAttday)
def get_uid_by_pin(pin):
select_sql = """
select userid from userinfo where badgenumber = '%s'
""" % (format_pin(pin))
res = p_query(select_sql)
return res and res[0][0] or None
