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 filterdata_by_user(query_set, user): model = query_set.model lname = "limit_%s_to" % model.__name__.lower() if hasattr(model, lname): #特殊的地方就直接用自定义的方法(定义的模型中),否则走通用的 return getattr(model(), lname)(query_set, user) from mysite.iclock.iutils import userDeptList, userAreaList Department = GetModel("personnel", "Department") Area = GetModel("personnel", "Area") Device = GetModel("iclock", "Device") if model == Area: #区域表直接过滤 areaids = userAreaList(user) if not areaids: return query_set areaids = [area.pk for area in areaids] return query_set.filter(pk__in=areaids) if model == Device: #如果设备表就直接根据区域控制行权限 areaids = userAreaList(user) if not areaids: return query_set areaids = [area.pk for area in areaids] q = {"area__in": areaids} return query_set.filter(Q(**q)) q = {} #以设备为外键的模型按照区域过滤(通用) for f in model._meta.fields: if isinstance(f, models.fields.related.ForeignKey): if f.rel.to.__name__ == "Device": areaids = userAreaList(user) if areaids: q = {f.name + "__area__in": areaids} return query_set.filter(Q(**q)) else: return query_set deptids = userDeptList(user) # 获取授权的部门 if not deptids: return query_set deptids = [int(dept.pk) for dept in deptids] if model == Department: #部门表直接过滤 return query_set.filter(pk__in=deptids) for f in model._meta.fields: # 遍历每一个字段 if isinstance(f, models.fields.related.ForeignKey): if f.rel.to.__name__ == "Department": #以部门为外键的部门过滤 q = {f.name + "__in": deptids} return query_set.filter(Q(**q)) elif f.rel.to.__name__ == "Employee": q = { f.name + "__DeptID__in": deptids } #以部门为外键的人员过滤(如Employee中通过DeptID过滤) q = {f.name + "__DeptID__in": deptids} query_set.exclude() return query_set.filter(Q(**q)) # return query_set.filter(Q(**q)) return query_set
def limit_transaction_to(self,query_set,user): from mysite.iclock.iutils import userDeptList,userAreaList deptids = userDeptList(user) if not deptids: return query_set deptids = [ int(dept.pk) for dept in deptids ] return query_set.filter(UserID__DeptID__in=deptids)
def filterdata_by_user(query_set,user): model = query_set.model lname = "limit_%s_to" % model.__name__.lower() if hasattr(model, lname):#特殊的地方就直接用自定义的方法(定义的模型中),否则走通用的 return getattr(model(), lname)(query_set, user) from mysite.iclock.iutils import userDeptList, userAreaList Department=GetModel("personnel", "Department") Area=GetModel("personnel", "Area") Device=GetModel("iclock", "Device") if model==Area: #区域表直接过滤 areaids = userAreaList(user) if not areaids: return query_set areaids = [ area.pk for area in areaids ] return query_set.filter(pk__in=areaids) if model==Device: #如果设备表就直接根据区域控制行权限 areaids = userAreaList(user) if not areaids: return query_set areaids = [ area.pk for area in areaids ] q = {"area__in":areaids} return query_set.filter(Q(**q)) q={} #以设备为外键的模型按照区域过滤(通用) for f in model._meta.fields: if isinstance(f,models.fields.related.ForeignKey): if f.rel.to.__name__=="Device": areaids = userAreaList(user) if areaids: q = {f.name +"__area__in":areaids} return query_set.filter(Q(**q)) else: return query_set deptids = userDeptList(user) # 获取授权的部门 if not deptids: return query_set deptids = [ int(dept.pk) for dept in deptids ] if model ==Department: #部门表直接过滤 return query_set.filter(pk__in=deptids) for f in model._meta.fields: # 遍历每一个字段 if isinstance(f,models.fields.related.ForeignKey): if f.rel.to.__name__=="Department": #以部门为外键的部门过滤 q = {f.name +"__in":deptids} return query_set.filter(Q(**q)) elif f.rel.to.__name__=="Employee": q = {f.name+"__DeptID__in":deptids}#以部门为外键的人员过滤(如Employee中通过DeptID过滤) q = {f.name+"__DeptID__in":deptids} query_set.exclude() return query_set.filter(Q(**q)) # return query_set.filter(Q(**q)) return query_set
def GenerateEmpFlow_sql(request,deptids,d1,d2): params={"deptids":deptids,"d1":d1,"d2":d2} id_part={} if deptids: id_part["where"]="hasdeptids" else: depts = userDeptList(request.user) if depts: params["depts"]=",".join([str(i.id) for i in depts]) id_part["where"]="nodeptids" sql=sql_utils.get_sql('sql',sqlid='GenerateEmpFlow_sql',app='personnel',params=params,id_part=id_part) return sql
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_sql(request,deptids): params={"deptids":deptids} id_part={} if deptids: id_part["where"]="hasdeptids" else: depts = userDeptList(request.user) if depts: params["depts"]=",".join([str(i.id) for i in depts]) id_part["where"]="nodeptids" sql=sql_utils.get_sql('sql',sqlid='GenerateEmpEducation_sql',app='personnel',params=params,id_part=id_part) return sql # if settings.DATABASES["default"]["ENGINE"] == "django.db.backends.postgresql_psycopg2": # sql =''' # select a."DeptName" , # (select count(1) from userinfo where "education_id" =1 and userinfo.defaultdeptid = a."DeptID" ) pupil, # (select count(1) from userinfo where "education_id" =2 and userinfo.defaultdeptid = a."DeptID" ) middle_studemt, # (select count(1) from userinfo where "education_id" =3 and userinfo.defaultdeptid = a."DeptID" ) high_studemt, # (select count(1) from userinfo where "education_id" =8 and userinfo.defaultdeptid = a."DeptID" ) university_studemt, # (select count(1) from userinfo where "education_id" =9 and userinfo.defaultdeptid = a."DeptID" ) graduate_studemt, # (select count(1) from userinfo where "education_id" =10 and userinfo.defaultdeptid = a."DeptID" ) doctor # from departments a where status=0 # ''' # else: # sql =''' # select a.DeptName , # (select count(1) from userinfo where education_id =1 and userinfo.defaultdeptid = a.DeptID ) pupil, # (select count(1) from userinfo where education_id =2 and userinfo.defaultdeptid = a.DeptID ) middle_studemt, # (select count(1) from userinfo where education_id =3 and userinfo.defaultdeptid = a.DeptID ) high_studemt, # (select count(1) from userinfo where education_id =8 and userinfo.defaultdeptid = a.DeptID ) university_studemt, # (select count(1) from userinfo where education_id =9 and userinfo.defaultdeptid = a.DeptID ) graduate_studemt, # (select count(1) from userinfo where education_id =10 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 ]) # return sql
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 myData(user): if user.username=='employee': #employee user return Transaction.objects.filter(UserID=request.employee) return Transaction.objects.filter(UserID__DeptID__in=userDeptList(user))