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