def index():
users = []
rows = utils.fetch_all_json(
db_manager.query(
"""
SELECT * FROM USERACCOUNT
WHERE user_id != 'None'
ORDER BY user_id
""", ))
for row in rows:
cal = utils.fetch_all_json(
db_manager.query(
"""
SELECT * FROM CALENDAR AS c, EVENT AS e
WHERE account_hashkey = %s
AND c.calendar_hashkey = e.calendar_hashkey
""", (row["account_hashkey"], )))
if len(cal) != 0:
users.append({
"email": row["user_id"],
"loginPlatform": row["login_platform"],
"accountHashkey": row["account_hashkey"]
})
return render_template('index.html', users=users)
def hello():
result = utils.fetch_all_json(
db_manager.query(
"""
SELECT * FROM USER ACCOUNT
WHERE is_active = %s
""", (1, )))
def __getLocationFilteredList(self):
locationList = self.jsonData['locations']
# TODO : region에 입력된 값이 db에 존재하는지 체크해야하지 않을까?
queryOptionParam = ", ".join("'%s'" % locationData['region']
for locationData in locationList)
dataList = utils.fetch_all_json(
db_manager.query("""
SELECT
r.reco_hashkey,
r.region,
r.title,
r.price,
r.distance,
r.category,
r.property_romantic,
r.property_active_dynamic,
r.property_active_static,
r.property_food_korean,
r.property_food_chinese,
r.property_food_japanese,
r.property_food_italian,
CONCAT(
"[",
GROUP_CONCAT(
JSON_OBJECT(
'id', etr.id,
'event_type_id', etr.event_type_id,
'ing', etr.ing
)
),
"]"
) as event_availability
FROM RECOMMENDATION as r
LEFT JOIN EVENT_TYPE_RECO as etr
ON
r.reco_hashkey = etr.reco_hashkey
WHERE
region IN (%s)
GROUP BY r.reco_hashkey
""" % queryOptionParam))
recoList = {'restaurant': [], 'cafe': [], 'place': []}
for dataItem in dataList:
recoList[dataItem['category']].append(dataItem)
for dataItem in recoList:
for recoItem in recoList[dataItem]:
jsonConvertedItem = json.loads(recoItem['event_availability'])
recoItem['event_availability'] = {}
for jsonItem in jsonConvertedItem:
if jsonItem['event_type_id'] == None:
continue
recoItem['event_availability'][
jsonItem['event_type_id']] = jsonItem
return recoList
def __getUserMainRegion(self):
self.userMainRegion = utils.fetch_all_json(
db_manager.query(
"""
SELECT region, count(*) as locationCnt FROM
(
SELECT ul.region,ul.location_hashkey,ul.priority
FROM EVENT e, USER_EVENT_ANALYSIS ue , USER_EVENT_LOCATION ul ,
(
# 여기까지 유저 accounthashkey가 같은 calendar 가져오기
SELECT calendar_hashkey FROM CALENDAR
INNER JOIN(
#여기까지 이벤트 해시키로 유저 account_hashkey들 가져오는쿼리
SELECT account_hashkey FROM USERACCOUNT
WHERE user_hashkey IN (
SELECT user_hashkey
FROM EVENT e, CALENDAR c, USERACCOUNT u
WHERE e.event_hashkey = %s
AND c.calendar_hashkey = e.calendar_hashkey
AND u.account_hashkey = c.account_hashkey
)
#############
) AS u
ON u.account_hashkey = CALENDAR.account_hashkey
#############
) cal
WHERE e.calendar_hashkey = cal.calendar_hashkey
AND ue.event_hashkey = e.event_hashkey
AND ue.location_hashkey = ul.location_hashkey
AND ul.priority = 0
) AS locations
GROUP by region
ORDER BY locationCnt DESC
LIMIT 3
""", (self.user_event_hashkey, )))
def __getCalendarList(self, account_hashkey):
return utils.fetch_all_json(
db_manager.query(
"""
SELECT * FROM CALENDAR
WHERE account_hashkey = %s
""", (account_hashkey, )))
def __getEventsList(self, calendar_hashkey):
return utils.fetch_all_json(
db_manager.query(
"""
SELECT * FROM EVENT
WHERE calendar_hashkey = %s
ORDER BY start_dt DESC
""", (calendar_hashkey, )))
def customEvent():
event_name = request.args['eventName']
location = request.args['location']
start_dt = request.args['startDt']
end_dt = request.args['endDt']
event_hashkey = utils.make_hashkey("salttt")
if len(event_name) == 0:
event_name = None
if len(location) == 0:
location = None
db_manager.query(
"""
INSERT INTO EVENT
(event_hashkey,calendar_hashkey,event_id,summary,start_dt,end_dt,location)
VALUES
(%s, 'admin_calendar_hashkey','admin_event_id', %s, %s, %s, %s)
""", (event_hashkey, event_name, start_dt, end_dt, location))
reco_maestro = recoMaestro.RecoMaestro(
account_hashkey='admin_account_hashkey', switchExtractor=True)
return json.dumps(reco_maestro.result_final)
def __getAllList(self):
dataList = utils.fetch_all_json(
db_manager.query("""
SELECT
r.reco_hashkey,
r.region,
r.title,
r.price,
r.distance,
r.category,
CONCAT(
"[",
GROUP_CONCAT(
JSON_OBJECT(
'id', etr.id,
'event_type_id', etr.event_type_id,
'ing', etr.ing
)
),
"]"
) as event_availability
FROM RECOMMENDATION as r
LEFT JOIN EVENT_TYPE_RECO as etr
ON
r.reco_hashkey = etr.reco_hashkey
GROUP BY r.reco_hashkey
"""))
recoList = {'restaurant': [], 'cafe': [], 'place': []}
for dataItem in dataList:
recoList[dataItem['category']].append(dataItem)
for dataItem in recoList:
for recoItem in recoList[dataItem]:
jsonConvertedItem = json.loads(recoItem['event_availability'])
recoItem['event_availability'] = {}
for jsonItem in jsonConvertedItem:
if jsonItem['event_type_id'] == None:
continue
recoItem['event_availability'][
jsonItem['event_type_id']] = jsonItem
return recoList
def initData(self):
self.locationPriorityList = {}
for locationData in self.jsonData['locations']:
self.locationPriorityList[
locationData['region']] = locationData['no']
self.eventTypeIdList = []
for eventType in self.jsonData['event_types']:
self.eventTypeIdList.append(eventType['id'])
priceList = utils.fetch_all_json(
db_manager.query("""
SELECT price
FROM RECOMMENDATION
ORDER BY price
"""))
distanceRowList = utils.fetch_all_json(
db_manager.query("""
SELECT distance
FROM RECOMMENDATION
ORDER BY distance
"""))
distanceList = []
for distanceRow in distanceRowList:
distanceString = re.search(r'\d+', distanceRow['distance'])
if distanceString is None:
distanceData = 99
else:
distanceData = int(distanceString.group())
distanceList.append(distanceData)
distanceList.sort()
self.priceGradeList = []
self.distanceGradeList = []
n = 9
for i in range(1, n + 1):
position = int(self.getSNDPercent(n, i) * len(priceList)) - 1
self.priceGradeList.append(priceList[position]['price'])
position = int(self.getSNDPercent(n, i) * len(distanceList)) - 1
self.distanceGradeList.append(distanceList[position])
print(self.getSNDPercent(n, i))
print(self.priceGradeList)
print(self.distanceGradeList)
#load user data
recoLogList = json.loads(
dumps(
mongo_manager.reco_log.find(
{"accountHashkey": self.accountHashKey})))
logRecoHashKeyList = []
for recoLog in recoLogList:
if (recoLog['action'] == "click" and recoLog['category'] == "recoCell" and recoLog['label'] == "deepLink") or \
(recoLog['action'] == "click" and recoLog['category'] == "recoCell" and recoLog['label'] == "sharingKakao") or \
(recoLog['action'] == "click" and recoLog['category'] == "recoCell" and recoLog['label'] == "sharingKakaoInBlog") or \
(recoLog['action'] == "click" and recoLog['category'] == "recoMapCell" and recoLog['label'] == "deepLink") or \
(recoLog['action'] == "click" and recoLog['category'] == "recoMapCell" and recoLog['label'] == "sharingKakaoInCell"):
logRecoHashKeyList.append(recoLog['recoHashkey'])
if len(logRecoHashKeyList) != 0:
result = utils.fetch_all_json(
db_manager.query("""
SELECT
reco_hashkey,
property_romantic,
property_active_dynamic,
property_active_static,
property_food_korean,
property_food_chinese,
property_food_japanese,
property_food_italian
FROM RECOMMENDATION
WHERE
RECOMMENDATION.reco_hashkey IN (%s)
""" % (", ".join("'%s'" % row
for row in logRecoHashKeyList))))
else:
result = []
self.userTypeClickCount = {
'property_romantic': 0,
'property_active_dynamic': 0,
'property_active_static': 0,
'property_food_korean': 0,
'property_food_chinese': 0,
'property_food_japanese': 0,
'property_food_italian': 0,
'all': len(logRecoHashKeyList)
}
for row in result:
hashkeyNum = logRecoHashKeyList.count(row['reco_hashkey'])
for key in row:
if row[key] == None:
continue
if key == 'reco_hashkey':
continue
self.userTypeClickCount[key] += row[key] * hashkeyNum
print(self.userTypeClickCount)
self.userPropertyScore = {}
if self.userTypeClickCount['all'] == 0:
self.userPropertyScore['romanticPriority'] = 0.5
else:
self.userPropertyScore['romanticPriority'] = (
self.userTypeClickCount['property_romantic'] /
self.userTypeClickCount['all'])
activeList = ['property_active_dynamic', 'property_active_static']
foodList = [
'property_food_korean', 'property_food_chinese',
'property_food_japanese', 'property_food_italian'
]
foodList.sort(key=lambda e: (self.userTypeClickCount[e]), reverse=True)
activeList.sort(key=lambda e: (self.userTypeClickCount[e]),
reverse=True)
i = 0
for activeRow in activeList:
self.userPropertyScore[activeRow] = 4.5 - i
i += 1
i = 0
for foodRow in foodList:
self.userPropertyScore[foodRow] = 4.5 - i
i += 1
print(self.userPropertyScore)