def extResultPageNth(key, nth):
des = AESCipher('kit-works-lnp')
decKey = urllib.unquote(key)
userId = des.decrypt(key)
try:
userId = int(userId)
except ValueError:
abort(403) #not allowed
db_result = db_session.query(AppUser, Company.categoryId).join(Company, Company.companyId==AppUser.companyId).filter(AppUser.userId==userId).first()
userData = dict()
if db_result:
row, categoryId = db_result
userData["jobClass"] = categoryId
userData["gender"] = row.gender
userData["age"] = datetime.date.today().year - row.birth.year + 1
userData["jobType"] = row.jobType
userData["workYear"] = row.workYear
userData["workMonth"] = row.workMonth
userData["jobTitle"] = row.jobTitleNo
userData["nth"] = nth
else:
return "No such user"
return resultPage(userId, userData)
def extResultPage(key):
# 10001 => 5%2BhxstoyknhAlenXoNrmpyOMF3s9FyITTS9sAI0JP7ONQthKZDO1i5A2qC%2BGVlBp
# des = AESCipher('kit-works-lnp')
# key = des.encrypt(str(key).encode('utf-8'))
# encKey = urllib.quote(key, safe='')
# return encKey
des = AESCipher('kit-works-lnp')
decKey = urllib.unquote(key)
userId = des.decrypt(key)
try:
userId = int(userId)
except ValueError:
abort(403) #not allowed
db_result = db_session.query(AppUser, Company.categoryId).join(Company, Company.companyId==AppUser.companyId).filter(AppUser.userId==userId).first()
userData = dict()
if db_result:
row, categoryId = db_result
userData["jobClass"] = categoryId
userData["gender"] = row.gender
userData["age"] = datetime.date.today().year - row.birth.year + 1
userData["jobType"] = row.jobType
userData["workYear"] = row.workYear
userData["workMonth"] = row.workMonth
userData["jobTitle"] = row.jobTitleNo
else:
return "No such user"
return resultPage(userId, userData)
def logPage():
from sqlalchemy.sql.expression import text
monthago = func.subdate(func.now(), text('interval 1 month'))
accessLog = db_session.query(func.date(AccessLog.rdate).label("rdate"), func.count().label("cnt")\
,func.count(func.IF(AccessLog.platform=="web",1,None)).label("webCnt"), func.count(func.IF(AccessLog.platform=="mobile",1,None)).label("mobileCnt") )\
.group_by( func.date(AccessLog.rdate) ).filter(AccessLog.rdate > monthago).all()
infoLog = db_session.query(func.date(User.rdate).label("rdate"), func.count().label("cnt") ).group_by( func.date(User.rdate) ).filter(User.rdate > monthago).all()
resultLog = db_session.query(func.date(Result.rdate).label("rdate"), func.count().label("cnt") ).group_by( func.date(Result.rdate) ).filter(Result.rdate > monthago).all()
weekago = func.subdate(func.now(), text('interval 7 day'))
accessUsers = AccessLog.query.order_by(AccessLog.id.desc()).filter(AccessLog.rdate > weekago).all()
try:
return render_template('log.html', accessLog=accessLog, infoLog=infoLog, resultLog=resultLog, accessUsers=accessUsers)
except TemplateNotFound:
abort(404)
def resultPage(userId, sessionData):
data = dict()
# 세션 : 사용자 인구학적 정보
jobClassTop = sessionData["jobClass"][0:1] #0,1,2,3,4,5
data["jobClassTop"] = jobClassTop + "0" #00,10,20,30,40,50
data["demo"] = dict()
data["demo"]["targets"] = list()
data["demo"]["targets"].append(str(sessionData["gender"]))
ageRange = (int0(sessionData["age"]) / 10) * 10
data["demo"]["targets"].append("a"+str(ageRange))
data["demo"]["targets"].append(str(sessionData["jobType"]))
#"w0":"6개월 미만", "w.5":"6개월~1년미만", "w1":"1~3년", "w3":"3~5년", "w5":"5~10년", "w10":"10~20년", "w20":"20년 이상",
iWorkYear = int0(sessionData.get("workYear"))
iWorkMonth = int0(sessionData.get("workMonth"))
workMonthRangeFrom = 0
workMonthRangeTo = 12
if iWorkYear==0 :
workYearRangeFrom = 0
workYearRangeTo = 1
if iWorkMonth < 6:
workMonthRangeTo = 6
workYearRange = "w0"
else:
workMonthRangeFrom = 6
workYearRange = "w.5"
elif iWorkYear < 3:
workYearRangeFrom = 1
workYearRangeTo = 3
workYearRange = "w1"
elif iWorkYear < 5:
workYearRangeFrom = 3
workYearRangeTo = 5
workYearRange = "w3"
elif iWorkYear < 10:
workYearRangeFrom = 5
workYearRangeTo = 10
workYearRange = "w5"
elif iWorkYear < 20:
workYearRangeFrom = 10
workYearRangeTo = 20
workYearRange = "w10"
else:
workYearRangeFrom = 20
workYearRangeTo = 99
workYearRange = "w20"
data["demo"]["targets"].append(workYearRange)
data["demo"]["targets"].append(str(sessionData["jobTitle"]))
# DB : 내 점수
condFilter1 = [1==1]
if sessionData.get("nth"):
condFilter1.append(Result.nth==sessionData["nth"])
my_result = Result.query.filter(Result.userId==userId).filter(*condFilter1).order_by(Result.id.desc()).first()
if not my_result:
return "No survey data"
data["myPer"] = float0(my_result.perTotal)
data["part"] = dict()
data["part"]["origin"] = [ float0(my_result.per1), float0(my_result.per2), float0(my_result.per3), float0(my_result.per4), float0(my_result.per5) ]
# 파트별 점수 내림차순 변환
partDict = dict( (i, data["part"]["origin"][i]) for i in range(len(data["part"]["origin"])) ) # {"0":55.3, ... }
data["part"]["sort"] = sorted(partDict.items(), key=lambda x: x[1])[::-1] #tuple ((3,55.4),(0,55.3),...)
# DB : 전체 평균점수
total_result = Result.query.filter(Result.id < my_result.id).\
with_entities( func.avg(Result.perTotal), func.avg(Result.per1), func.avg(Result.per2), func.avg(Result.per3), func.avg(Result.per4), func.avg(Result.per5), func.count() ).first()
data["totalPer"] = float0(total_result[0])
data["part"]["total"] = [ float0(total_result[1]), float0(total_result[2]), float0(total_result[3]), float0(total_result[4]), float0(total_result[5])]
totalCnt = total_result[6] + 1
# DB : f1 전체 평균점수
total_f1_result = Answer.query.with_entities( func.avg(Answer.f1) ).first()
data["totalF1Per"] = float0(total_f1_result[0]) * 20.0
# DB : 같은 산업군 평균점수
jobClass_result = Result.query.filter(Result.id < my_result.id).filter(Result.jobClass.like(jobClassTop+'%'))\
.with_entities( func.avg(Result.perTotal), func.avg(Result.per1), func.avg(Result.per2), func.avg(Result.per3), func.avg(Result.per4), func.avg(Result.per5) ).first()
data["jobClassPer"] = float0(jobClass_result[0])
data["part"]["job"] = [ float0(jobClass_result[1]), float0(jobClass_result[2]), float0(jobClass_result[3]), float0(jobClass_result[4]), float0(jobClass_result[5])]
# DB : f1 같은 산업군 평균점수
jobClass_f1_result = db_session.query(Answer, Result).filter(Answer.id==Result.answerId).filter(Result.jobClass.like(jobClassTop+'%')).with_entities( func.avg(Answer.f1) ).first()
data["jobClassF1Per"] = float0(jobClass_f1_result[0]) * 20.0
# DB : 인구학적 정보에 따른 각각의 평균점수
data["demo"]["per"] = list()
## 성별
gender_result = Result.query.filter(Result.id < my_result.id).filter(Result.gender==sessionData['gender']).with_entities( func.avg(Result.perTotal)).first()
data["demo"]["per"].append(float0(gender_result[0]))
## 연령대
age_result = Result.query.filter(Result.id < my_result.id).filter(Result.age.between(ageRange, ageRange+10) ).with_entities( func.avg(Result.perTotal)).first()
data["demo"]["per"].append(float0(age_result[0]))
## 정규직
jobType_result = Result.query.filter(Result.id < my_result.id).filter(Result.jobType==sessionData['jobType'] ).with_entities( func.avg(Result.perTotal)).first()
data["demo"]["per"].append(float0(jobType_result[0]))
## 근속년도
workYear_result = Result.query.filter(Result.id < my_result.id).filter(Result.workYear.between(workYearRangeFrom, workYearRangeTo) )\
.filter(Result.workMonth.between(workMonthRangeFrom, workMonthRangeTo) )\
.with_entities( func.avg(Result.perTotal)).first()
data["demo"]["per"].append(float0(workYear_result[0]))
## 직책
jobTitle_result = Result.query.filter(Result.id < my_result.id).filter(Result.jobTitle==sessionData['jobTitle'] ).with_entities( func.avg(Result.perTotal)).first()
data["demo"]["per"].append(float0(jobTitle_result[0]))
# 상위 몇 % ?
# myTopRate_result = Result.query.filter(Result.perTotal > data["myPer"]).with_entities( func.count() ).first()
# moreThanMeCnt = myTopRate_result[0]
# data["myTopRate"] = round( float0(moreThanMeCnt) / float0(totalCnt) * 100.0 + 0.1, 1 )
## 내가 꼴찌면 상위 100%, 1위면 상위 0%
# DB : 긍정/부정적 답변 항목과 답변번호
condFilter2 = [1==1]
if sessionData.get("nth"):
condFilter2.append(Answer.nth==sessionData["nth"])
my_answer = Answer.query.filter(Answer.userId==userId).filter(*condFilter2).order_by(Answer.id.desc()).first()
data["f1"] = str(my_answer.f1)
data["f1per"] = my_answer.f1 * 20.0
data["answer"] = dict()
data["answer"]["pos"] = dict()
data["answer"]["neg"] = dict()
for key in ["a1","a2","a3","a4","a5","a6","a7","a8","b1","b2","b3","b4","b5","b6","b7","b8","c1","c2","c3","c4","c5","c6","c7","c8","d1","d2","d3","d4","d5","d6","d7","d8","e1","e2","e3","e4","e5","e6","e7","e8"] :
val = getattr(my_answer, key)
if val!=None and int0(val) > 3 :
data["answer"]["pos"][key] = val
elif val!=None and int0(val) < 3 :
data["answer"]["neg"][key] = val
# DB : 나보다 높은 사람 명수.
moreThanMy_result = Result.query.filter(Result.perTotal > data["myPer"]).with_entities( func.count() ).first()
moreThanMyCnt = moreThanMy_result[0]
#lessThanMyCnt = totalCnt - moreThanMyCnt - 1
# DB : 전체 평균(totalPer)보다 상위권은 몇 명 ?
#totalTop_result = Result.query.filter(Result.perTotal > data["totalPer"]).with_entities( func.count() ).first()
#moreThanAvgCnt = totalTop_result[0]
if moreThanMyCnt == 0 :
data["myTopRate"] = 0.1
data["myBotRate"] = 99.9
else :
data["myTopRate"] = round( float0(moreThanMyCnt) / float0(totalCnt) * 100.0, 1 )
data["myBotRate"] = 100.0 - data["myTopRate"]
app.logger.debug(data)
try:
return render_template('result.html', data=data)
except TemplateNotFound:
abort(404)
