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 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)
