def doTransDB(cursor, opts, maxRow=10000, **filter): sqls = [] if filter: rows = Transaction.objects.filter(filter) else: rows = Transaction.objects.all()[:10] rows = rows[:maxRow].values() if len(rows) <= 0: return tail = opts['dexp_db_sql_tail'] head = opts['dexp_db_sql_head'] row_tail = '\n' row_fmt = opts['dexp_db_sql_trans'] has_alia = ("%(Device_Alias)" in row_fmt) has_alia = ("%(Device_Alias)" in row_fmt) has_state = ("%(State)" in row_fmt) has_verify = ("%(Verify)" in row_fmt) has_workcode = ("%(WorkCode)" in row_fmt) has_reserved = ("%(Reserved)" in row_fmt) has_userid = ("%(Employee_ID)" in row_fmt) if has_userid: userid_cache = {} emp_search_sql = opts['emp_search_sql'] if has_state: has_state = getTransState("c_state_", opts) if has_verify: has_verify = getTransState("c_verify_", opts) if has_workcode: has_workcode = getTransWC("c_wc_", opts) if has_reserved: has_reserved = getTransWC("c_rs_", opts) sqls.append(head) for r in rows: appendDevFields(r, 'SN_id', has_alia) appendTimeFields(r, "TTime_", r['TTime']) e = Employee.objByID(r['UserID_id']) r['PIN'] = e.PIN r['Employee_Name'] = e.EName r['TTime'] = ("%s" % r['TTime'])[:19] transKey(r, 'State', has_state) transKey(r, 'Verify', has_verify) transKey(r, 'WorkCode', has_workcode) transKey(r, 'Reserved', has_reserved) if has_userid: r['Employee_ID'] = searchUserID(cursor, r['PIN'], userid_cache, emp_search_sql) for c in r: if r[c] == None: r[c] = "" s = (row_fmt % r) sqls.append(s) sqls.append(tail) try_sql(cursor, sqls)
def doTransDB(cursor, opts, maxRow=10000, **filter): sqls=[] if filter: rows=Transaction.objects.filter(filter) else: rows=Transaction.objects.all()[:10] rows=rows[:maxRow].values() if len(rows)<=0: return tail=opts['dexp_db_sql_tail'] head=opts['dexp_db_sql_head'] row_tail='\n' row_fmt=opts['dexp_db_sql_trans'] has_alia=("%(Device_Alias)" in row_fmt) has_alia=("%(Device_Alias)" in row_fmt) has_state=("%(State)" in row_fmt) has_verify=("%(Verify)" in row_fmt) has_workcode=("%(WorkCode)" in row_fmt) has_reserved=("%(Reserved)" in row_fmt) has_userid=("%(Employee_ID)" in row_fmt) if has_userid: userid_cache={} emp_search_sql=opts['emp_search_sql'] if has_state: has_state=getTransState("c_state_", opts) if has_verify: has_verify=getTransState("c_verify_", opts) if has_workcode: has_workcode=getTransWC("c_wc_", opts) if has_reserved: has_reserved=getTransWC("c_rs_", opts) sqls.append(head) for r in rows: appendDevFields(r,'SN_id',has_alia) appendTimeFields(r, "TTime_", r['TTime']) e=Employee.objByID(r['UserID_id']) r['PIN']=e.PIN r['Employee_Name']=e.EName r['TTime']=("%s"%r['TTime'])[:19] transKey(r, 'State', has_state) transKey(r, 'Verify', has_verify) transKey(r, 'WorkCode', has_workcode) transKey(r, 'Reserved', has_reserved) if has_userid: r['Employee_ID']=searchUserID(cursor, r['PIN'], userid_cache, emp_search_sql) for c in r: if r[c]==None: r[c]="" s=(row_fmt%r) sqls.append(s) sqls.append(tail) try_sql(cursor, sqls)
def doTransExportData(opts, emps, fn): tail=opts['dexp_t_tail'] head=opts['dexp_t_head'] row_tail='\r\n' if opts['dexp_rowsp']=='rowsp13': row_tail='\n' elif opts['dexp_rowsp']=='rowsp10': row_tail='\r' row_fmt=opts['dexp_t_row'] has_alia=("%(Device_Alias)" in row_fmt) has_state=("%(State)" in row_fmt) has_verify=("%(Verify)" in row_fmt) has_workcode=("%(WorkCode)" in row_fmt) has_reserved=("%(Reserved)" in row_fmt) if has_state: has_state=getTransState("c_state_", opts) if has_verify: has_verify=getTransState("c_verify_", opts) if has_workcode: has_workcode=getTransWC("c_wc_", opts) if has_reserved: has_reserved=getTransWC("c_rs_", opts) f=file(fn, "a+b") try: if head: f.write(head+row_tail) for r in emps: appendDevFields(r,'SN_id',has_alia) appendTimeFields(r, "TTime_", r['TTime']) e=Employee.objByID(r['UserID_id']) r['PIN']=e.PIN r['Employee_Name']=e.EName r['TTime']=("%s"%r['TTime'])[:19] transKey(r, 'State', has_state) transKey(r, 'Verify', has_verify) transKey(r, 'WorkCode', has_workcode) transKey(r, 'Reserved', has_reserved) for c in r: if r[c]==None: r[c]="" s=(row_fmt%r)+row_tail f.write(s.encode('utf-8')) if tail: f.write(tail+row_tail) finally: f.close()
def mainReport(request, reportType=0): '''考勤汇总表/每日考勤统计表''' from mysite.att.models import AttException, attShifts, EmpSpecDay if settings.ATT_CALCULATE_NEW: from mysite.att.calculate.global_cache import C_ATT_RULE from mysite.att.att_param import GetLeaveClasses, GetRptIndex from mysite.att.report_utils import NormalAttValue, SaveValue C_ATT_RULE.action_init() else: from mysite.iclock.datas import GetLeaveClasses, GetRptIndex, NormalAttValue, SaveValue from mysite.iclock.datasproc import FetchDisabledFields AbnomiteRptItems = GetLeaveClasses() AttAbnomiteRptIndex = GetRptIndex(AbnomiteRptItems) userids, deptids, d1, d2, offset = parse_report_arg(request, True) auHour = 1 aaBLeave = 1003 if reportType == 0: dic = [('userid', _(u'用户ID')), ('badgenumber', _(u'人员编号')), ('username', _(u'姓名')), ('deptid', _(u'部门名称')), ('duty', _(u'应到')), ('realduty', _(u'实到')), ('late', _(u'迟到')), ('early', _(u'早退')), ('absent', _(u'旷工')), ('dutyinout', _(u'应签次数')), ('clockin', _(u'应签到')), ('clockout', _(u'应签退')), ('noin', _(u'未签到')), ('noout', _(u'未签退')), ('worktime', _(u'出勤时长')), ('overtime', _(u'加班时间')), ('SSpeDayNormalOT', _(u'平日加班')), ('SSpeDayWeekendOT', _(u'休息日加班')), ('SSpeDayHolidayOT', _(u'节假日加班')), ('Leave', _(u'请假'))] elif reportType == 1: dic = [ ('userid', _(u'用户ID')), ('badgenumber', _(u'人员编号')), ('username', _(u'姓名')), ('deptid', _(u'部门名称')), ] t = d1 while t <= d2: f = str(t.day) dic.append((f, f)) t = t + datetime.timedelta(1) dic = dic + [('duty', _(u'应到')), ('realduty', _(u'实到')), ('late', _(u'迟到')), ('early', _(u'早退')), ('absent', _(u'旷工')), ('overtime', _(u'加班时间')), ('SSpeDayNormalOT', _(u'平日加班')), ('SSpeDayWeekendOT', _(u'休息日加班')), ('SSpeDayHolidayOT', _(u'节假日加班')), ('Leave', _(u'请假'))] LClasses1 = GetLeaveClasses(1) for t in LClasses1: fName = 'Leave_' + str(t['LeaveId']) dic.append((fName, t['LeaveName'])) calculate = AttCalculateBase(dic) ct = 0 for uid in userids: ct += 1 rmdAttday = calculate.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: try: emp = Employee.objByID(uid) except: continue rmdAttday['userid'] = uid rmdAttday['deptid'] = emp.DeptID.name rmdAttday['badgenumber'] = emp.PIN rmdAttday['username'] = emp.EName 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() calculate.AddItem(rmdAttday) ################## 返回字典的属性补充 ################### Result = calculate.ResultDic(offset) if reportType == 0: Result['disableCols'] = FetchDisabledFields(request.user, 'attTotal') elif reportType == 1: Result['disableCols'] = FetchDisabledFields(request.user, 'attDailyTotal') return getJSResponse(smart_str(dumps(Result)))
def calcLeaveReport(request): ''' 请假汇总表 ''' from mysite.att.models import AttException, attShifts if settings.ATT_CALCULATE_NEW: from mysite.att.calculate.global_cache import C_ATT_RULE from mysite.att.att_param import GetLeaveClasses, GetRptIndex from mysite.att.report_utils import NormalAttValue, SaveValue C_ATT_RULE.action_init() else: from mysite.iclock.datas import GetLeaveClasses, GetRptIndex, NormalAttValue, SaveValue AbnomiteRptItems = GetLeaveClasses() AttAbnomiteRptIndex = GetRptIndex(AbnomiteRptItems) userids, deptids, d1, d2, offset = parse_report_arg(request, True) auHour = 1 aaBLeave = 1003 dic = [('userid', _(u'用户ID')), ('badgenumber', _(u'人员编号')), ('username', _(u'姓名')), ('deptid', _(u'部门名称')), ('Leave', _(u'请假'))] LClasses1 = GetLeaveClasses(1) for t in LClasses1: fName = 'Leave_' + str(t['LeaveId']) dic.append((fName, t['LeaveName'])) calculate = AttCalculateBase(dic) for uid in userids: rmdAttday = calculate.NewItem() uid = int(uid) try: emp = Employee.objByID(uid) except: continue attExcept = AttException.objects.filter(UserID=uid, AttDate__gte=d1, AttDate__lte=d2) if not len(attExcept) > 0: continue rmdAttday['userid'] = uid rmdAttday['deptid'] = emp.DeptID.name rmdAttday['badgenumber'] = emp.PIN rmdAttday['username'] = emp.EName try: for ex in attExcept: exceptid = ex.ExceptionID if settings.ATT_CALCULATE_NEW: from mysite.att.models import EmpSpecDay ask_obj = EmpSpecDay.objects.get(pk=ex.ExceptionID) exceptid = ask_obj.leaveclass.pk InScopeTime = ex.InScopeTime 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]: continue elif exceptid > 0: AbnomiteRptItem = AbnomiteRptItems[ AttAbnomiteRptIndex[exceptid]] v = NormalAttValue(InScopeTime, AbnomiteRptItem['MinUnit'], AbnomiteRptItem['Unit'], AbnomiteRptItem['RemaindProc'], auHour, wdmins) rmdAttday['Leave_' + str(exceptid)] = SaveValue( rmdAttday['Leave_' + str(exceptid)], v) if AbnomiteRptItems[AttAbnomiteRptIndex[exceptid]][ 'IsLeave'] == 1: #只有计为请假时才累计 aaBLeave_RptItem = AbnomiteRptItems[ AttAbnomiteRptIndex[aaBLeave]] v = NormalAttValue(InScopeTime, aaBLeave_RptItem['MinUnit'], aaBLeave_RptItem['Unit'], aaBLeave_RptItem['RemaindProc'], auHour, wdmins) #print 'hhhhhhhhhhhhh',v,InScopeTime,ex.ExceptionID rmdAttday['Leave'] = SaveValue(rmdAttday['Leave'], v) except: import traceback traceback.print_exc() calculate.AddItem(rmdAttday) return getJSResponse(smart_str(dumps(calculate.ResultDic(offset))))
def ForMakeDataExcepSum(hander, userids, d1, d2): from mysite.personnel.models import Employee from mysite.att.models import AttException, attShifts from mysite.att.calculate.global_cache import C_ATT_RULE from mysite.att.att_param import GetLeaveClasses, GetRptIndex from mysite.att.report_utils import NormalAttValue, SaveValue C_ATT_RULE.action_init() AbnomiteRptItems = GetLeaveClasses() AttAbnomiteRptIndex = GetRptIndex(AbnomiteRptItems) auHour = 1 aaBLeave = 1003 hander.grid.InitItems() for uid in userids: rmdAttday = hander.grid.NewItem() uid = int(uid) try: emp = Employee.objByID(uid) except: continue attExcept = AttException.objects.filter(UserID=uid, AttDate__gte=d1, AttDate__lte=d2) if not len(attExcept) > 0: continue rmdAttday['userid'] = uid rmdAttday['deptid'] = emp.DeptID.name rmdAttday['badgenumber'] = emp.PIN rmdAttday['username'] = emp.EName rmdAttday['Leave'] = 0 try: for ex in attExcept: exceptid = ex.ExceptionID if settings.ATT_CALCULATE_NEW: from mysite.att.models import EmpSpecDay ask_obj = EmpSpecDay.objects.get(pk=ex.ExceptionID) exceptid = ask_obj.leaveclass.pk InScopeTime = ex.InScopeTime 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]: continue elif exceptid > 0: AbnomiteRptItem = AbnomiteRptItems[ AttAbnomiteRptIndex[exceptid]] v = NormalAttValue(InScopeTime, AbnomiteRptItem['MinUnit'], AbnomiteRptItem['Unit'], AbnomiteRptItem['RemaindProc'], auHour, wdmins) rmdAttday['Leave_' + str(exceptid)] = SaveValue( rmdAttday['Leave_' + str(exceptid)], v) if AbnomiteRptItems[AttAbnomiteRptIndex[exceptid]][ 'IsLeave'] == 1: #只有计为请假时才累计 aaBLeave_RptItem = AbnomiteRptItems[ AttAbnomiteRptIndex[aaBLeave]] v = NormalAttValue(InScopeTime, aaBLeave_RptItem['MinUnit'], aaBLeave_RptItem['Unit'], aaBLeave_RptItem['RemaindProc'], auHour, wdmins) rmdAttday['Leave'] = SaveValue(rmdAttday['Leave'], v) except: import traceback traceback.print_exc() hander.grid.AddItem(rmdAttday)