def calcReport(request): ''' 考勤汇总表 ''' deptIDs=request.POST.get('DeptIDs',"") userIDs=request.POST.get('UserIDs',"") st=request.POST.get('ComeTime','') et=request.POST.get('EndTime','') st=datetime.datetime.strptime(st,'%Y-%m-%d') et=datetime.datetime.strptime(et,'%Y-%m-%d') r=CalcReportItem(request,deptIDs,userIDs,st,et) loadall=request.REQUEST.get('pa','') if not loadall: objdata={} allr=CalcReportItem(request,deptIDs,userIDs,st,et,0,True) objdata['data']=allr['datas'] objdata['fields']=allr['fieldnames'] heads={} for i in range(len(allr['fieldnames'])): heads[allr['fieldnames'][i]]=allr['fieldcaptions'][i] objdata['heads']=heads tmp_name=save_datalist(objdata) r['tmp_name']=tmp_name return getJSResponse(smart_str(dumps(r)))
def dailycalcReport(request): deptIDs=request.POST.get('DeptIDs',"") userIDs=request.POST.get('UserIDs',"") st=request.POST.get('ComeTime','') et=request.POST.get('EndTime','') st=datetime.datetime.strptime(st,'%Y-%m-%d') et=datetime.datetime.strptime(et,'%Y-%m-%d') #修改 郭学文 2010-05-17 # et=et+datetime.timedelta(days=-1) # print "-------------------",st,et,deptIDs,userIDs # st=datetime.datetime(2008,5,1) # et=datetime.datetime(2008,5,30) r=CalcReportItem(request,deptIDs,userIDs,st,et,1) # print "11111111111111111111111" loadall=request.REQUEST.get('pa','') if not loadall: allr=CalcReportItem(request,deptIDs,userIDs,st,et,1,True) objdata={} objdata['data']=allr['datas'] objdata['fields']=allr['fieldnames'] heads={} for i in range(len(allr['fieldnames'])): heads[allr['fieldnames'][i]]=allr['fieldcaptions'][i] objdata['heads']=heads tmp_name=save_datalist(objdata) r['tmp_name']=tmp_name # print "allr['fieldnames']:%s"%r['fieldnames'] return getJSResponse(smart_str(dumps(r)))
def calcReport(request): ''' 考勤汇总表 ''' deptIDs = request.POST.get('DeptIDs', "") userIDs = request.POST.get('UserIDs', "") st = request.POST.get('ComeTime', '') et = request.POST.get('EndTime', '') st = datetime.datetime.strptime(st, '%Y-%m-%d') et = datetime.datetime.strptime(et, '%Y-%m-%d') r = CalcReportItem(request, deptIDs, userIDs, st, et) loadall = request.REQUEST.get('pa', '') if not loadall: objdata = {} allr = CalcReportItem(request, deptIDs, userIDs, st, et, 0, True) objdata['data'] = allr['datas'] objdata['fields'] = allr['fieldnames'] heads = {} for i in range(len(allr['fieldnames'])): heads[allr['fieldnames'][i]] = allr['fieldcaptions'][i] objdata['heads'] = heads tmp_name = save_datalist(objdata) r['tmp_name'] = tmp_name return getJSResponse(smart_str(dumps(r)))
def dailycalcReport(request): deptIDs = request.POST.get('DeptIDs', "") userIDs = request.POST.get('UserIDs', "") st = request.POST.get('ComeTime', '') et = request.POST.get('EndTime', '') st = datetime.datetime.strptime(st, '%Y-%m-%d') et = datetime.datetime.strptime(et, '%Y-%m-%d') #修改 郭学文 2010-05-17 # et=et+datetime.timedelta(days=-1) # print "-------------------",st,et,deptIDs,userIDs # st=datetime.datetime(2008,5,1) # et=datetime.datetime(2008,5,30) r = CalcReportItem(request, deptIDs, userIDs, st, et, 1) # print "11111111111111111111111" loadall = request.REQUEST.get('pa', '') if not loadall: allr = CalcReportItem(request, deptIDs, userIDs, st, et, 1, True) objdata = {} objdata['data'] = allr['datas'] objdata['fields'] = allr['fieldnames'] heads = {} for i in range(len(allr['fieldnames'])): heads[allr['fieldnames'][i]] = allr['fieldcaptions'][i] objdata['heads'] = heads tmp_name = save_datalist(objdata) r['tmp_name'] = tmp_name # print "allr['fieldnames']:%s"%r['fieldnames'] return getJSResponse(smart_str(dumps(r)))
def le_reprot(request): ''' 汇总最早与最晚计算报表 ''' deptids = request.POST.get('DeptIDs', '') userids = request.POST.get('UserIDs', '') st = request.POST.get('ComeTime', '') et = request.POST.get('EndTime', '') st = datetime.datetime.strptime(st, '%Y-%m-%d') et = datetime.datetime.strptime(et, '%Y-%m-%d') r = le_reprot_calculate(request, deptids, userids, st, et) loadall = request.REQUEST.get('pa', '') if not loadall: objdata = {} allr = le_reprot_calculate(request, deptids, userids, st, et, True) objdata['data'] = allr['datas'] objdata['fields'] = allr['fieldnames'] heads = {} for i in range(len(allr['fieldnames'])): heads[allr['fieldnames'][i]] = allr['fieldcaptions'][i] objdata['heads'] = heads tmp_name = save_datalist(objdata) r['tmp_name'] = tmp_name #用于导出 没有这个字段导出报错。 return getJSResponse(smart_str(dumps(r))) '''
def le_reprot(request): ''' 汇总最早与最晚计算报表 ''' deptids=request.POST.get('DeptIDs','') userids=request.POST.get('UserIDs','') st=request.POST.get('ComeTime','') et=request.POST.get('EndTime','') st=datetime.datetime.strptime(st,'%Y-%m-%d') et=datetime.datetime.strptime(et,'%Y-%m-%d') r=le_reprot_calculate(request,deptids,userids,st,et) loadall=request.REQUEST.get('pa','') if not loadall: objdata={} allr=le_reprot_calculate(request,deptids,userids,st,et,True) objdata['data']=allr['datas'] objdata['fields']=allr['fieldnames'] heads={} for i in range(len(allr['fieldnames'])): heads[allr['fieldnames'][i]]=allr['fieldcaptions'][i] objdata['heads']=heads tmp_name=save_datalist(objdata) r['tmp_name']=tmp_name#用于导出 没有这个字段导出报错。 return getJSResponse(smart_str(dumps(r))) '''
def GenerateEmpCardList(request): from mysite.personnel.models.model_issuecard import IssueCard try: depts =userDeptList(request.user) if depts: data=IssueCard.objects.filter(UserID__DeptID__in=userDeptList(request.user)).order_by("UserID__PIN").values_list("UserID__PIN","UserID__EName","cardno","issuedate","effectivenessdate","cardstatus") else: data=IssueCard.objects.all().order_by("UserID__PIN").values_list("UserID__PIN","UserID__EName","cardno","issuedate","effectivenessdate","cardstatus") header0=[_(u'工号'),_(u'姓名'),_(u'卡号'),_(u'发卡日期')] #,_(u'有效日期'),_(u'卡状态') headers= "["+",".join([u"'%s'"%i for i in header0])+"]" fields=['PIN','EName','cardno','issuedate'] #,'effectivenessdate','cardstatus' header={'PIN':u'%s'%_(u'工号'),'EName':u'%s'%_(u'姓名'), 'cardno':u'%s'%_(u'卡号'),'issuedate':u'%s'%_(u'发卡日期')} # 'effectivenessdate':u'%s'%_(u'有效日期'), # 'cardstatus':u'%s'%_(u'卡状态')} r={} datatotmp=[] for row in data: # r[fields[5]] = row[5] # r[fields[4]] = row[4] r[fields[3]] = row[3] r[fields[2]] = row[2] r[fields[1]] = row[1] r[fields[0]] = row[0] datatotmp.append(r.copy()) tmp_name=save_datalist({"data":datatotmp,"fields":fields,"heads":header}) try: offset = int(request.REQUEST.get(PAGE_VAR, 1)) except: offset=1 if len(data)==0: data=[] data.append([]) limit= int(request.REQUEST.get(PAGE_LIMIT_VAR, settings.PAGE_LIMIT)) mnp=request.REQUEST.get(MAX_NO_PAGE, 0) if len(data)<=int(mnp): limit=int(mnp) paginator = Paginator(data, limit) item_count = paginator.count if offset>paginator.num_pages: offset=paginator.num_pages if offset<1: offset=1 pgList = paginator.page(offset) cc={} cc["page_count"]=paginator.num_pages cc["record_count"]=item_count cc["page_number"]=offset cc["heads"] =headers cc["data"] =pgList.object_list cc["fields"]= fields cc["tmp_name"]= tmp_name except: import traceback; traceback.print_exc() return GenerateEmpCardJsonData(request,cc)
def GenerateEmpPunchCard(request): from mysite.iclock.models.model_trans import Transaction import datetime cudt = datetime.datetime.now().strftime("%Y-%m-%d") d1=request.POST.get("starttime",cudt) + " 00:00:00" d2=request.POST.get("endtime",cudt) +" 23:59:59" empids = request.POST.get("empids") deptids =request.POST.get("deptids") sql =""" select a.badgenumber,a.name,b.checktime from userinfo a inner join checkinout b on a.userid = b.userid where b.checktime>='%s' and b.checktime<='%s' """%(d1,d2) if empids: sql=sql+" and a.userid in (%s) "%(empids) if deptids and not empids: sql =sql + " and a.defaultdeptid in (%s) "%(deptids) sql = sql + " order by a.badgenumber,b.checktime " dts=[] d1_dt = datetime.datetime.strptime(request.POST.get("starttime",cudt),"%Y-%m-%d") d2_dt = datetime.datetime.strptime(request.POST.get("endtime",cudt),"%Y-%m-%d") diffdays = (d2_dt-d1_dt).days i =0 while i<=diffdays: dts.append(d1_dt+datetime.timedelta(days=i)) i+=1 readybaseEmp=[] if empids: requiredAtt =Employee.objects.filter(isatt='1',id__in=empids).order_by("PIN").values_list("PIN","EName") elif deptids: requiredAtt =Employee.objects.filter(isatt='1',DeptID__in=deptids).order_by("PIN").values_list("PIN","EName") else: requiredAtt =Employee.objects.filter(isatt='1').order_by("PIN").values_list("PIN","EName") if not requiredAtt.count(): tmp_name=save_datalist({"data":[],"fields":fields,"heads":header}) return getJSResponse([[]]) else: for i in requiredAtt: for dt_temp in dts: l = list(i) l.append(dt_temp.strftime("%Y-%m-%d")) readybaseEmp.append(l) cs = connection.cursor() cs.execute(sql) data = cs.fetchall() datas=[] for row in data: datas.append(list(row)) for item in datas: if item[2]: item.append(item[2].strftime("%Y-%m-%d")) item.append(item[2].strftime("%H:%M")) item.remove(item[2]) i=0 ii=len(datas) for emp in readybaseEmp: cc= filter(lambda x:x[0]==emp[0] and x[2]==emp[2],datas) punchcarddetail = ",".join( [i[3] for i in cc]) emp.append(punchcarddetail) fields=['badgenumber','name','checkdate','time'] header0=[{'badgenumber':_(u'人员编号')},{'name':_(u'姓名')},{'checkdate':_(u'打卡日期')},{'time':_(u'打卡时间')}] header={'badgenumber':u'%s'%_(u'人员编号'),'name':u'%s'%_(u'姓名'),'checkdate':u'%s'%_(u'打卡日期'),'time':u'%s'%_(u'打卡时间')} headers= "["+",".join([u"'%s'"%i.values()[0] for i in header0])+"]" r={} datatotmp=[] for row in readybaseEmp: datatotmp.append({'badgenumber':row[0],'name':row[1],'checkdate':row[2],'time':row[3]}) tmp_name=save_datalist({"data":datatotmp,"fields":fields,"heads":header}) if len(readybaseEmp)==0:readybaseEmp.append([]) try: offset = int(request.REQUEST.get(PAGE_VAR, 1)) except: offset=1 limit= int(request.REQUEST.get(PAGE_LIMIT_VAR, settings.PAGE_LIMIT)) mnp=request.REQUEST.get(MAX_NO_PAGE, 0) if len(data)<=int(mnp): limit=int(mnp) paginator = Paginator(readybaseEmp, limit) item_count = paginator.count if offset>paginator.num_pages: offset=paginator.num_pages if offset<1: offset=1 pgList = paginator.page(offset) cc={} cc["page_count"]=paginator.num_pages cc["record_count"]=item_count cc["page_number"]=offset cc["heads"] =headers cc["data"] =pgList.object_list cc["fields"]= fields cc["tmp_name"]=tmp_name return GenerateEmpPunchCardJsonData(request,cc)
def GenerateEmpCardList(request): from mysite.personnel.models.model_issuecard import IssueCard try: depts = userDeptList(request.user) if depts: data = IssueCard.objects.filter(UserID__DeptID__in=userDeptList( request.user)).order_by("UserID__PIN").values_list( "UserID__PIN", "UserID__EName", "cardno", "issuedate", "effectivenessdate", "cardstatus") else: data = IssueCard.objects.all().order_by("UserID__PIN").values_list( "UserID__PIN", "UserID__EName", "cardno", "issuedate", "effectivenessdate", "cardstatus") header0 = [_(u'工号'), _(u'姓名'), _(u'卡号'), _(u'发卡日期')] #,_(u'有效日期'),_(u'卡状态') headers = "[" + ",".join([u"'%s'" % i for i in header0]) + "]" fields = ['PIN', 'EName', 'cardno', 'issuedate'] #,'effectivenessdate','cardstatus' header = { 'PIN': u'%s' % _(u'工号'), 'EName': u'%s' % _(u'姓名'), 'cardno': u'%s' % _(u'卡号'), 'issuedate': u'%s' % _(u'发卡日期') } # 'effectivenessdate':u'%s'%_(u'有效日期'), # 'cardstatus':u'%s'%_(u'卡状态')} r = {} datatotmp = [] for row in data: # r[fields[5]] = row[5] # r[fields[4]] = row[4] r[fields[3]] = row[3] r[fields[2]] = row[2] r[fields[1]] = row[1] r[fields[0]] = row[0] datatotmp.append(r.copy()) tmp_name = save_datalist({ "data": datatotmp, "fields": fields, "heads": header }) try: offset = int(request.REQUEST.get(PAGE_VAR, 1)) except: offset = 1 if len(data) == 0: data = [] data.append([]) limit = int(request.REQUEST.get(PAGE_LIMIT_VAR, settings.PAGE_LIMIT)) mnp = request.REQUEST.get(MAX_NO_PAGE, 0) if len(data) <= int(mnp): limit = int(mnp) paginator = Paginator(data, limit) item_count = paginator.count if offset > paginator.num_pages: offset = paginator.num_pages if offset < 1: offset = 1 pgList = paginator.page(offset) cc = {} cc["page_count"] = paginator.num_pages cc["record_count"] = item_count cc["page_number"] = offset cc["heads"] = headers cc["data"] = pgList.object_list cc["fields"] = fields cc["tmp_name"] = tmp_name except: import traceback traceback.print_exc() return GenerateEmpCardJsonData(request, cc)
def GenerateEmpEducation(request): deptids = request.POST.get("deptids") if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = ''' select a."DeptName" , (select count(1) from userinfo where "Education" ='0' and userinfo.defaultdeptid = a."DeptID" ) pupil, (select count(1) from userinfo where "Education" ='1' and userinfo.defaultdeptid = a."DeptID" ) middle_studemt, (select count(1) from userinfo where "Education" ='2' and userinfo.defaultdeptid = a."DeptID" ) high_studemt, (select count(1) from userinfo where "Education" ='3' and userinfo.defaultdeptid = a."DeptID" ) university_studemt, (select count(1) from userinfo where "Education" ='4' and userinfo.defaultdeptid = a."DeptID" ) graduate_studemt, (select count(1) from userinfo where "Education" ='5' and userinfo.defaultdeptid = a."DeptID" ) doctor from departments a where status=0 ''' else: sql = ''' select a.DeptName , (select count(1) from userinfo where Education ='0' and userinfo.defaultdeptid = a.DeptID ) pupil, (select count(1) from userinfo where Education ='1' and userinfo.defaultdeptid = a.DeptID ) middle_studemt, (select count(1) from userinfo where Education ='2' and userinfo.defaultdeptid = a.DeptID ) high_studemt, (select count(1) from userinfo where Education ='3' and userinfo.defaultdeptid = a.DeptID ) university_studemt, (select count(1) from userinfo where Education ='4' and userinfo.defaultdeptid = a.DeptID ) graduate_studemt, (select count(1) from userinfo where Education ='5' and userinfo.defaultdeptid = a.DeptID ) doctor from departments a where status=0 ''' if deptids: if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql + """ and a."DeptID" in (%s)""" % deptids else: sql = sql + " and a.DeptID in (%s)" % deptids else: depts = userDeptList(request.user) if depts: if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql + """ and a."DeptID" in (%s)""" % ",".join( [str(i.id) for i in depts]) else: sql = sql + " and a.DeptID in (%s)" % ",".join( [str(i.id) for i in depts]) header0 = [ _(u'部门'), _(u'小学'), _(u'中学'), _(u'高中'), _(u'大学'), _(u'硕士'), _(u'博士') ] headers = "[" + ",".join([u"'%s'" % i for i in header0]) + "]" header = { 'DeptName': u'%s' % _(u'部门'), 'pupil': u'%s' % _(u'小学'), 'middle_studemt': u'%s' % _(u'中学'), 'high_studemt': u'%s' % _(u'高中'), 'university_studemt': u'%s' % _(u'大学'), 'graduate_studemt': u'%s' % _(u'硕士'), 'doctor': u'%s' % _(u'博士') } fields = [ 'DeptName', 'pupil', 'middle_studemt', 'high_studemt', 'university_studemt', 'graduate_studemt', 'doctor' ] cs = connection.cursor() cs.execute(sql) # data=[] # while True: # t=cs.fetchone() # if t is None: # break # data.append(list(t)) data = cs.fetchall() r = {} datatotmp = [] for row in data: r[fields[6]] = row[6] r[fields[5]] = row[5] r[fields[4]] = row[4] r[fields[3]] = row[3] r[fields[2]] = row[2] r[fields[1]] = row[1] r[fields[0]] = row[0] datatotmp.append(r.copy()) tmp_name = save_datalist({ "data": datatotmp, "fields": fields, "heads": header }) if len(data) == 0: data.append([]) try: offset = int(request.REQUEST.get(PAGE_VAR, 1)) except: offset = 1 limit = int(request.REQUEST.get(PAGE_LIMIT_VAR, settings.PAGE_LIMIT)) mnp = request.REQUEST.get(MAX_NO_PAGE, 0) if len(data) <= int(mnp): limit = int(mnp) paginator = Paginator(data, limit) item_count = paginator.count if offset > paginator.num_pages: offset = paginator.num_pages if offset < 1: offset = 1 pgList = paginator.page(offset) cc = {} cc["page_count"] = paginator.num_pages cc["record_count"] = item_count cc["page_number"] = offset cc["heads"] = headers cc["data"] = pgList.object_list cc["fields"] = fields cc["tmp_name"] = tmp_name return GenerateEmpEducationJsonData(request, cc)
def GenerateDeptRoster(request): deptids = request.POST.get("deptids") #emps =Employee.objects.filter(DeptID__in=deptids).order_by('DeptID','PIN').values_list('PIN','EName','DeptID__name','Title','Mobile','Birthday','birthplace','Education') if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = """ select a.badgenumber,a.name,c."DeptName",( select b.display from base_basecode b where a.title =b.value and b.content='TITLE' ) title,a.pager,a."Birthday", ( select b.display from base_basecode b where a.birthplace =b.value and b.content='CN_PROVINCE' ) native, ( select b.display from base_basecode b where a."Education" =b.value and b.content='EDUCATION' ) education from userinfo a left join departments c on a.defaultdeptid=c."DeptID" where a.status=0 """ else: sql = """ select a.badgenumber,a.name,c.DeptName,( select b.display from base_basecode b where a.title =b.value and b.content='TITLE' ) title,a.pager,a.Birthday, ( select b.display from base_basecode b where a.birthplace =b.value and b.content='CN_PROVINCE' ) native, ( select b.display from base_basecode b where a.Education =b.value and b.content='EDUCATION' ) education from userinfo a left join departments c on a.defaultdeptid=c.DeptID where a.status=0 """ if deptids: if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql + " and a.defaultdeptid in (%s)" % deptids else: sql = sql + " and a.defaultdeptid in (%s)" % deptids else: depts = userDeptList(request.user) if depts: if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql + " and a.defaultdeptid in (%s)" % ",".join( [str(i.id) for i in depts]) else: sql = sql + " and a.defaultdeptid in (%s)" % ",".join( [str(i.id) for i in depts]) if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql + " order by a.defaultdeptid,a.badgenumber " else: sql = sql + " order by a.defaultdeptid,a.badgenumber " cs = connection.cursor() cs.execute(sql) header0 = [ _(u'工号'), _(u'姓名'), _(u'部门'), _(u'职务'), _(u'手机'), _(u'年龄'), _(u'籍贯'), _(u'学历') ] header = { 'PIN': u'%s' % _(u'工号'), 'EName': u'%s' % _(u'姓名'), 'Department': u'%s' % _(u'部门'), 'Title': u'%s' % _(u'职务'), 'mobile': u'%s' % _(u'手机'), 'age': u'%s' % _(u'年龄'), 'native': u'%s' % _(u'籍贯'), 'education': u'%s' % _(u'学历') } headers = "[" + ",".join([u"'%s'" % i for i in header0]) + "]" fields = [ 'PIN', 'EName', 'Department', 'Title', 'mobile', 'age', 'native', 'education' ] cc = {} data = cs.fetchall() r = {} datatotmp = [] cudt = datetime.datetime.now() cudd = datetime.date(cudt.year, cudt.month, cudt.day) for row in data: r[fields[7]] = row[7] r[fields[6]] = row[6] if row[5]: r[fields[5]] = (cudd - row[5]).days / 365 else: r[fields[5]] = row[5] r[fields[4]] = row[4] r[fields[3]] = row[3] r[fields[2]] = row[2] r[fields[1]] = row[1] r[fields[0]] = row[0] datatotmp.append(r.copy()) tmp_name = save_datalist({ "data": datatotmp, "fields": fields, "heads": header }) try: data_list = [] for item in data: tmp = list(item) if tmp[5]: tmp[5] = (cudd - tmp[5]).days / 365 data_list.append(list(tmp)) if len(data_list) == 0: data_list = [] data_list.append([]) try: offset = int(request.REQUEST.get(PAGE_VAR, 1)) except: offset = 1 limit = int(request.REQUEST.get(PAGE_LIMIT_VAR, settings.PAGE_LIMIT)) mnp = request.REQUEST.get(MAX_NO_PAGE, 0) if len(data_list) <= int(mnp): limit = int(mnp) paginator = Paginator(data_list, limit) item_count = paginator.count if offset > paginator.num_pages: offset = paginator.num_pages if offset < 1: offset = 1 pgList = paginator.page(offset) cc = {} cc["page_count"] = paginator.num_pages cc["record_count"] = item_count cc["page_number"] = offset cc["heads"] = headers cc["data"] = pgList.object_list cc["fields"] = fields cc["tmp_name"] = tmp_name except: import traceback traceback.print_exc() return GenerateEmpRosterJsonData(request, cc)
def GenerateEmpFlow(request): cudt = datetime.datetime.now().strftime("%Y-%m-%d") d1 = request.POST.get("starttime", cudt) d2 = request.POST.get("endtime", cudt) deptids = request.POST.get("deptids") if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = '''select a."DeptName", (select count(1) from userinfo where userinfo."Hiredday">='%s' and userinfo."Hiredday"<='%s' and userinfo.defaultdeptid = a."DeptID") newin, (select count(1) from personnel_empchange cc where cc.changepostion =1 and cc.isvalid=True and cc.newvalue = to_char(a."DeptID",'999999999999999') and cc.changedate>='%s' and cc.changedate<='%s') transferin, (select count(1) from personnel_empchange dd where dd.changepostion =1 and dd.isvalid=True and dd.oldvalue = to_char(a."DeptID",'999999999999999') and dd.changedate>='%s' and dd.changedate<='%s') transferout, ( select count(1) from personnel_leavelog ea left join userinfo eb on ea."UserID_id" = eb.userid where ea.leavetype =1 and eb.defaultdeptid = a."DeptID" and ea.leavedate >='%s' and ea.leavedate<= '%s') selfleave , ( select count(1) from personnel_leavelog ea left join userinfo eb on ea."UserID_id" = eb.userid where ea.leavetype =2 and eb.defaultdeptid = a."DeptID" and ea.leavedate >='%s' and ea.leavedate<= '%s') passiveleave , ( select count(1) from personnel_leavelog ea left join userinfo eb on ea."UserID_id" = eb.userid where ea.leavetype =3 and eb.defaultdeptid = a."DeptID" and ea.leavedate >='%s' and ea.leavedate<= '%s') normalleave from departments a where status=0 ''' % ( d1, d2, d1, d2, d1, d2, d1, d2, d1, d2, d1, d2) else: sql = '''select a.DeptName, (select count(1) from userinfo where userinfo.Hiredday>='%s' and userinfo.Hiredday<='%s' and userinfo.defaultdeptid = a.DeptID) newin, (select count(1) from personnel_empchange cc where cc.changepostion ='1' and cc.isvalid='1' and cc.newvalue = a.DeptID and cc.changedate>='%s' and cc.changedate<='%s') transferin, (select count(1) from personnel_empchange dd where dd.changepostion ='1' and dd.isvalid='1' and dd.oldvalue = a.DeptID and dd.changedate>='%s' and dd.changedate<='%s') transferout, ( select count(1) from personnel_leavelog ea left join userinfo eb on ea.UserID_id = eb.userid where ea.leavetype ='1' and eb.defaultdeptid = a.DeptID and ea.leavedate >='%s' and ea.leavedate<= '%s') selfleave , ( select count(1) from personnel_leavelog ea left join userinfo eb on ea.UserID_id = eb.userid where ea.leavetype ='2' and eb.defaultdeptid = a.DeptID and ea.leavedate >='%s' and ea.leavedate<= '%s') passiveleave , ( select count(1) from personnel_leavelog ea left join userinfo eb on ea.UserID_id = eb.userid where ea.leavetype ='3' and eb.defaultdeptid = a.DeptID and ea.leavedate >='%s' and ea.leavedate<= '%s') normalleave from departments a where status=0 ''' % (d1, d2, d1, d2, d1, d2, d1, d2, d1, d2, d1, d2) if deptids: if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql + """ and a."DeptID" in (%s)""" % deptids else: sql = sql + " and a.DeptID in (%s)" % deptids else: depts = userDeptList(request.user) if depts: if settings.DATABASES["default"][ "ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql + """ and a."DeptID" in (%s)""" % ",".join( [str(i.id) for i in depts]) else: sql = sql + " and a.DeptID in (%s)" % ",".join( [str(i.id) for i in depts]) cs = connection.cursor() cs.execute(sql) header0 = [{ 'DeptName': _(u'部门') }, { 'newin': _(u'新进') }, { 'transferin': _(u'调入') }, { 'transferout': _(u'调出') }, { 'selfleave': _(u'自离') }, { 'passiveleave': _(u'辞退') }, { 'normalleave': _(u'辞职') }] header = { 'DeptName': u'%s' % _(u'部门'), 'newin': u'%s' % _(u'新进'), 'transferin': u'%s' % _(u'调入'), 'transferout': u'%s' % _(u'调出'), 'selfleave': u'%s' % _(u'自离'), 'passiveleave': u'%s' % _(u'辞退'), 'normalleave': u'%s' % _(u'辞职') } headers = "[" + ",".join([u"'%s'" % i.values()[0] for i in header0]) + "]" fields = [ 'DeptName', 'newin', 'transferin', 'transferout', 'selfleave', 'passiveleave', 'normalleave' ] cc = {} data = cs.fetchall() r = {} datatotmp = [] for row in data: r[fields[6]] = row[6] r[fields[5]] = row[5] r[fields[4]] = row[4] r[fields[3]] = row[3] r[fields[2]] = row[2] r[fields[1]] = row[1] r[fields[0]] = row[0] datatotmp.append(r.copy()) tmp_name = save_datalist({ "data": datatotmp, "fields": fields, "heads": header }) if len(data) == 0: data = [] data.append([]) try: offset = int(request.REQUEST.get(PAGE_VAR, 1)) except: offset = 1 limit = int(request.REQUEST.get(PAGE_LIMIT_VAR, settings.PAGE_LIMIT)) mnp = request.REQUEST.get(MAX_NO_PAGE, 0) if len(data) <= int(mnp): limit = int(mnp) paginator = Paginator(data, limit) item_count = paginator.count if offset > paginator.num_pages: offset = paginator.num_pages if offset < 1: offset = 1 pgList = paginator.page(offset) cc["page_count"] = paginator.num_pages cc["record_count"] = item_count cc["page_number"] = offset cc["heads"] = headers cc["data"] = pgList.object_list cc["fields"] = fields cc["tmp_name"] = tmp_name return GenerateEmpFlowJsonData(request, cc)
def GenerateEmpEducation(request): deptids = request.POST.get("deptids") if settings.DATABASES["default"]["ENGINE"] == "django.db.backends.postgresql_psycopg2": sql =''' select a."DeptName" , (select count(1) from userinfo where "Education" ='0' and userinfo.defaultdeptid = a."DeptID" ) pupil, (select count(1) from userinfo where "Education" ='1' and userinfo.defaultdeptid = a."DeptID" ) middle_studemt, (select count(1) from userinfo where "Education" ='2' and userinfo.defaultdeptid = a."DeptID" ) high_studemt, (select count(1) from userinfo where "Education" ='3' and userinfo.defaultdeptid = a."DeptID" ) university_studemt, (select count(1) from userinfo where "Education" ='4' and userinfo.defaultdeptid = a."DeptID" ) graduate_studemt, (select count(1) from userinfo where "Education" ='5' and userinfo.defaultdeptid = a."DeptID" ) doctor from departments a where status=0 ''' else: sql =''' select a.DeptName , (select count(1) from userinfo where Education ='0' and userinfo.defaultdeptid = a.DeptID ) pupil, (select count(1) from userinfo where Education ='1' and userinfo.defaultdeptid = a.DeptID ) middle_studemt, (select count(1) from userinfo where Education ='2' and userinfo.defaultdeptid = a.DeptID ) high_studemt, (select count(1) from userinfo where Education ='3' and userinfo.defaultdeptid = a.DeptID ) university_studemt, (select count(1) from userinfo where Education ='4' and userinfo.defaultdeptid = a.DeptID ) graduate_studemt, (select count(1) from userinfo where Education ='5' and userinfo.defaultdeptid = a.DeptID ) doctor from departments a where status=0 ''' if deptids: if settings.DATABASES["default"]["ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql + """ and a."DeptID" in (%s)"""%deptids else: sql = sql + " and a.DeptID in (%s)"%deptids else: depts = userDeptList(request.user) if depts: if settings.DATABASES["default"]["ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql + """ and a."DeptID" in (%s)"""%",".join([str(i.id) for i in depts ]) else: sql = sql + " and a.DeptID in (%s)"%",".join([str(i.id) for i in depts ]) header0=[_(u'部门'),_(u'小学'),_(u'中学'),_(u'高中'),_(u'大学'),_(u'硕士'),_(u'博士')] headers= "["+",".join([u"'%s'"%i for i in header0])+"]" header={'DeptName':u'%s'%_(u'部门'),'pupil':u'%s'%_(u'小学'), 'middle_studemt':u'%s'%_(u'中学'),'high_studemt':u'%s'%_(u'高中'), 'university_studemt':u'%s'%_(u'大学'),'graduate_studemt':u'%s'%_(u'硕士'), 'doctor':u'%s'%_(u'博士')} fields=['DeptName','pupil','middle_studemt','high_studemt','university_studemt','graduate_studemt','doctor'] cs = connection.cursor() cs.execute(sql) # data=[] # while True: # t=cs.fetchone() # if t is None: # break # data.append(list(t)) data = cs.fetchall() r={} datatotmp=[] for row in data: r[fields[6]] = row[6] r[fields[5]] = row[5] r[fields[4]] = row[4] r[fields[3]] = row[3] r[fields[2]] = row[2] r[fields[1]] = row[1] r[fields[0]] = row[0] datatotmp.append(r.copy()) tmp_name=save_datalist({"data":datatotmp,"fields":fields,"heads":header}) if len(data)==0:data.append([]) try: offset = int(request.REQUEST.get(PAGE_VAR, 1)) except: offset=1 limit= int(request.REQUEST.get(PAGE_LIMIT_VAR, settings.PAGE_LIMIT)) mnp=request.REQUEST.get(MAX_NO_PAGE, 0) if len(data)<=int(mnp): limit=int(mnp) paginator = Paginator(data, limit) item_count = paginator.count if offset>paginator.num_pages: offset=paginator.num_pages if offset<1: offset=1 pgList = paginator.page(offset) cc={} cc["page_count"]=paginator.num_pages cc["record_count"]=item_count cc["page_number"]=offset cc["heads"] =headers cc["data"] =pgList.object_list cc["fields"]= fields cc["tmp_name"]=tmp_name return GenerateEmpEducationJsonData(request,cc)
def GenerateEmpFlow(request): cudt = datetime.datetime.now().strftime("%Y-%m-%d") d1=request.POST.get("starttime",cudt) d2=request.POST.get("endtime",cudt) deptids = request.POST.get("deptids") if settings.DATABASES["default"]["ENGINE"] == "django.db.backends.postgresql_psycopg2": sql='''select a."DeptName", (select count(1) from userinfo where userinfo."Hiredday">='%s' and userinfo."Hiredday"<='%s' and userinfo.defaultdeptid = a."DeptID") newin, (select count(1) from personnel_empchange cc where cc.changepostion =1 and cc.isvalid=True and cc.newvalue = to_char(a."DeptID",'999999999999999') and cc.changedate>='%s' and cc.changedate<='%s') transferin, (select count(1) from personnel_empchange dd where dd.changepostion =1 and dd.isvalid=True and dd.oldvalue = to_char(a."DeptID",'999999999999999') and dd.changedate>='%s' and dd.changedate<='%s') transferout, ( select count(1) from personnel_leavelog ea left join userinfo eb on ea."UserID_id" = eb.userid where ea.leavetype =1 and eb.defaultdeptid = a."DeptID" and ea.leavedate >='%s' and ea.leavedate<= '%s') selfleave , ( select count(1) from personnel_leavelog ea left join userinfo eb on ea."UserID_id" = eb.userid where ea.leavetype =2 and eb.defaultdeptid = a."DeptID" and ea.leavedate >='%s' and ea.leavedate<= '%s') passiveleave , ( select count(1) from personnel_leavelog ea left join userinfo eb on ea."UserID_id" = eb.userid where ea.leavetype =3 and eb.defaultdeptid = a."DeptID" and ea.leavedate >='%s' and ea.leavedate<= '%s') normalleave from departments a where status=0 '''%(d1,d2,d1,d2,d1,d2,d1,d2,d1,d2,d1,d2) else: sql='''select a.DeptName, (select count(1) from userinfo where userinfo.Hiredday>='%s' and userinfo.Hiredday<='%s' and userinfo.defaultdeptid = a.DeptID) newin, (select count(1) from personnel_empchange cc where cc.changepostion ='1' and cc.isvalid='1' and cc.newvalue = a.DeptID and cc.changedate>='%s' and cc.changedate<='%s') transferin, (select count(1) from personnel_empchange dd where dd.changepostion ='1' and dd.isvalid='1' and dd.oldvalue = a.DeptID and dd.changedate>='%s' and dd.changedate<='%s') transferout, ( select count(1) from personnel_leavelog ea left join userinfo eb on ea.UserID_id = eb.userid where ea.leavetype ='1' and eb.defaultdeptid = a.DeptID and ea.leavedate >='%s' and ea.leavedate<= '%s') selfleave , ( select count(1) from personnel_leavelog ea left join userinfo eb on ea.UserID_id = eb.userid where ea.leavetype ='2' and eb.defaultdeptid = a.DeptID and ea.leavedate >='%s' and ea.leavedate<= '%s') passiveleave , ( select count(1) from personnel_leavelog ea left join userinfo eb on ea.UserID_id = eb.userid where ea.leavetype ='3' and eb.defaultdeptid = a.DeptID and ea.leavedate >='%s' and ea.leavedate<= '%s') normalleave from departments a where status=0 '''%(d1,d2,d1,d2,d1,d2,d1,d2,d1,d2,d1,d2) if deptids: if settings.DATABASES["default"]["ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql + """ and a."DeptID" in (%s)"""%deptids else: sql = sql + " and a.DeptID in (%s)"%deptids else: depts = userDeptList(request.user) if depts: if settings.DATABASES["default"]["ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql + """ and a."DeptID" in (%s)"""%",".join([str(i.id) for i in depts ]) else: sql = sql + " and a.DeptID in (%s)"%",".join([str(i.id) for i in depts ]) cs = connection.cursor() cs.execute(sql) header0=[{'DeptName':_(u'部门')},{'newin':_(u'新进')},{'transferin':_(u'调入')},{'transferout':_(u'调出')},{'selfleave':_(u'自离')},{'passiveleave':_(u'辞退')},{'normalleave':_(u'辞职')}] header={'DeptName':u'%s'%_(u'部门'),'newin':u'%s'%_(u'新进'),'transferin':u'%s'%_(u'调入'), 'transferout':u'%s'%_(u'调出'),'selfleave':u'%s'%_(u'自离'),'passiveleave':u'%s'%_(u'辞退'), 'normalleave':u'%s'%_(u'辞职')} headers= "["+",".join([u"'%s'"%i.values()[0] for i in header0])+"]" fields=['DeptName','newin','transferin','transferout','selfleave','passiveleave','normalleave'] cc={} data = cs.fetchall() r={} datatotmp=[] for row in data: r[fields[6]] = row[6] r[fields[5]] = row[5] r[fields[4]] = row[4] r[fields[3]] = row[3] r[fields[2]] = row[2] r[fields[1]] = row[1] r[fields[0]] = row[0] datatotmp.append(r.copy()) tmp_name=save_datalist({"data":datatotmp,"fields":fields,"heads":header}) if len(data)==0: data =[] data.append([]) try: offset = int(request.REQUEST.get(PAGE_VAR, 1)) except: offset=1 limit= int(request.REQUEST.get(PAGE_LIMIT_VAR, settings.PAGE_LIMIT)) mnp=request.REQUEST.get(MAX_NO_PAGE, 0) if len(data)<=int(mnp): limit=int(mnp) paginator = Paginator(data, limit) item_count = paginator.count if offset>paginator.num_pages: offset=paginator.num_pages if offset<1: offset=1 pgList = paginator.page(offset) cc["page_count"]=paginator.num_pages cc["record_count"]=item_count cc["page_number"]=offset cc["heads"] =headers cc["data"] =pgList.object_list cc["fields"]= fields cc["tmp_name"]=tmp_name return GenerateEmpFlowJsonData(request,cc)
def GenerateDeptRoster(request): deptids = request.POST.get("deptids") #emps =Employee.objects.filter(DeptID__in=deptids).order_by('DeptID','PIN').values_list('PIN','EName','DeptID__name','Title','Mobile','Birthday','birthplace','Education') if settings.DATABASES["default"]["ENGINE"] == "django.db.backends.postgresql_psycopg2": sql =""" select a.badgenumber,a.name,c."DeptName",( select b.display from base_basecode b where a.title =b.value and b.content='TITLE' ) title,a.pager,a."Birthday", ( select b.display from base_basecode b where a.birthplace =b.value and b.content='CN_PROVINCE' ) native, ( select b.display from base_basecode b where a."Education" =b.value and b.content='EDUCATION' ) education from userinfo a left join departments c on a.defaultdeptid=c."DeptID" where a.status=0 """ else: sql =""" select a.badgenumber,a.name,c.DeptName,( select b.display from base_basecode b where a.title =b.value and b.content='TITLE' ) title,a.pager,a.Birthday, ( select b.display from base_basecode b where a.birthplace =b.value and b.content='CN_PROVINCE' ) native, ( select b.display from base_basecode b where a.Education =b.value and b.content='EDUCATION' ) education from userinfo a left join departments c on a.defaultdeptid=c.DeptID where a.status=0 """ if deptids: if settings.DATABASES["default"]["ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql + " and a.defaultdeptid in (%s)"%deptids else: sql = sql + " and a.defaultdeptid in (%s)"%deptids else: depts = userDeptList(request.user) if depts: if settings.DATABASES["default"]["ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql + " and a.defaultdeptid in (%s)"%",".join([str(i.id) for i in depts ]) else: sql = sql + " and a.defaultdeptid in (%s)"%",".join([str(i.id) for i in depts ]) if settings.DATABASES["default"]["ENGINE"] == "django.db.backends.postgresql_psycopg2": sql = sql +" order by a.defaultdeptid,a.badgenumber " else: sql = sql +" order by a.defaultdeptid,a.badgenumber " cs = connection.cursor() cs.execute(sql) header0=[_(u'工号'),_(u'姓名'),_(u'部门'),_(u'职务'),_(u'手机'),_(u'年龄'),_(u'籍贯'),_(u'学历')] header={'PIN':u'%s'%_(u'工号'),'EName':u'%s'%_(u'姓名'),'Department':u'%s'%_(u'部门'), 'Title':u'%s'%_(u'职务'),'mobile':u'%s'%_(u'手机'),'age':u'%s'%_(u'年龄'), 'native':u'%s'%_(u'籍贯'),'education':u'%s'%_(u'学历') } headers= "["+",".join([u"'%s'"%i for i in header0])+"]" fields=['PIN','EName','Department','Title','mobile','age','native','education'] cc={} data = cs.fetchall() r={} datatotmp=[] cudt = datetime.datetime.now() cudd = datetime.date(cudt.year,cudt.month,cudt.day) for row in data: r[fields[7]] = row[7] r[fields[6]] = row[6] if row[5]: r[fields[5]] = (cudd - row[5]).days /365 else: r[fields[5]] = row[5] r[fields[4]] = row[4] r[fields[3]] = row[3] r[fields[2]] = row[2] r[fields[1]] = row[1] r[fields[0]] = row[0] datatotmp.append(r.copy()) tmp_name=save_datalist({"data":datatotmp,"fields":fields,"heads":header}) try: data_list =[] for item in data: tmp= list(item) if tmp[5]: tmp[5]=(cudd - tmp[5]).days /365 data_list.append(list(tmp)) if len(data_list)==0: data_list=[] data_list.append([]) try: offset = int(request.REQUEST.get(PAGE_VAR, 1)) except: offset=1 limit= int(request.REQUEST.get(PAGE_LIMIT_VAR, settings.PAGE_LIMIT)) mnp=request.REQUEST.get(MAX_NO_PAGE, 0) if len(data_list)<=int(mnp): limit=int(mnp) paginator = Paginator(data_list, limit) item_count = paginator.count if offset>paginator.num_pages: offset=paginator.num_pages if offset<1: offset=1 pgList = paginator.page(offset) cc={} cc["page_count"]=paginator.num_pages cc["record_count"]=item_count cc["page_number"]=offset cc["heads"] =headers cc["data"] =pgList.object_list cc["fields"]= fields cc["tmp_name"]=tmp_name except: import traceback; traceback.print_exc() return GenerateEmpRosterJsonData(request,cc)
def GenerateEmpPunchCard(request): from mysite.iclock.models.model_trans import Transaction import datetime cudt = datetime.datetime.now().strftime("%Y-%m-%d") d1 = request.POST.get("starttime", cudt) + " 00:00:00" d2 = request.POST.get("endtime", cudt) + " 23:59:59" empids = request.POST.get("empids") deptids = request.POST.get("deptids") sql = """ select a.badgenumber,a.name,b.checktime from userinfo a inner join checkinout b on a.userid = b.userid where b.checktime>='%s' and b.checktime<='%s' """ % (d1, d2) if empids: sql = sql + " and a.userid in (%s) " % (empids) if deptids and not empids: sql = sql + " and a.defaultdeptid in (%s) " % (deptids) sql = sql + " order by a.badgenumber,b.checktime " dts = [] d1_dt = datetime.datetime.strptime(request.POST.get("starttime", cudt), "%Y-%m-%d") d2_dt = datetime.datetime.strptime(request.POST.get("endtime", cudt), "%Y-%m-%d") diffdays = (d2_dt - d1_dt).days i = 0 while i <= diffdays: dts.append(d1_dt + datetime.timedelta(days=i)) i += 1 readybaseEmp = [] if empids: requiredAtt = Employee.objects.filter( isatt='1', id__in=empids).order_by("PIN").values_list("PIN", "EName") elif deptids: requiredAtt = Employee.objects.filter( isatt='1', DeptID__in=deptids).order_by("PIN").values_list("PIN", "EName") else: requiredAtt = Employee.objects.filter( isatt='1').order_by("PIN").values_list("PIN", "EName") if not requiredAtt.count(): tmp_name = save_datalist({ "data": [], "fields": fields, "heads": header }) return getJSResponse([[]]) else: for i in requiredAtt: for dt_temp in dts: l = list(i) l.append(dt_temp.strftime("%Y-%m-%d")) readybaseEmp.append(l) cs = connection.cursor() cs.execute(sql) data = cs.fetchall() datas = [] for row in data: datas.append(list(row)) for item in datas: if item[2]: item.append(item[2].strftime("%Y-%m-%d")) item.append(item[2].strftime("%H:%M")) item.remove(item[2]) i = 0 ii = len(datas) for emp in readybaseEmp: cc = filter(lambda x: x[0] == emp[0] and x[2] == emp[2], datas) punchcarddetail = ",".join([i[3] for i in cc]) emp.append(punchcarddetail) fields = ['badgenumber', 'name', 'checkdate', 'time'] header0 = [{ 'badgenumber': _(u'人员编号') }, { 'name': _(u'姓名') }, { 'checkdate': _(u'打卡日期') }, { 'time': _(u'打卡时间') }] header = { 'badgenumber': u'%s' % _(u'人员编号'), 'name': u'%s' % _(u'姓名'), 'checkdate': u'%s' % _(u'打卡日期'), 'time': u'%s' % _(u'打卡时间') } headers = "[" + ",".join([u"'%s'" % i.values()[0] for i in header0]) + "]" r = {} datatotmp = [] for row in readybaseEmp: datatotmp.append({ 'badgenumber': row[0], 'name': row[1], 'checkdate': row[2], 'time': row[3] }) tmp_name = save_datalist({ "data": datatotmp, "fields": fields, "heads": header }) if len(readybaseEmp) == 0: readybaseEmp.append([]) try: offset = int(request.REQUEST.get(PAGE_VAR, 1)) except: offset = 1 limit = int(request.REQUEST.get(PAGE_LIMIT_VAR, settings.PAGE_LIMIT)) mnp = request.REQUEST.get(MAX_NO_PAGE, 0) if len(data) <= int(mnp): limit = int(mnp) paginator = Paginator(readybaseEmp, limit) item_count = paginator.count if offset > paginator.num_pages: offset = paginator.num_pages if offset < 1: offset = 1 pgList = paginator.page(offset) cc = {} cc["page_count"] = paginator.num_pages cc["record_count"] = item_count cc["page_number"] = offset cc["heads"] = headers cc["data"] = pgList.object_list cc["fields"] = fields cc["tmp_name"] = tmp_name return GenerateEmpPunchCardJsonData(request, cc)