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