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)