def resolve_group(parent, info, id_from_token):
group = db.query(GroupModel).filter_by(id=parent.group_id).first()
group_user = db.query(GroupUserModel).filter_by(group_id=parent.group_id).all()
if id_from_token in group_user.user_id or group.access_level == GroupAccessEnum.OPEN:
return group
else:
return []
def changeAccountPW():
accountId = request.form['accountId']
befPW = request.form['befPW']
newPW = request.form['newPW']
account_row = db_session.query(Account).filter_by(accountId=accountId).filter_by(isValid=1)\
.with_entities(Account, func.password(befPW), func.password(newPW)).first()
account = None
if account_row:
account, pw, nPW = account_row
result = dict()
if(account):
print pw, account.password, nPW
if len(befPW)>0 and pw != account.password :
return jsonify({ "code": 302, "result": "Wrong password" })
update_result = db_session.query(Account).filter_by(accountId=accountId)\
.filter(Account.isValid>0).update({'password': nPW})
try:
db_session.commit()
except exc.IntegrityError as e:
db_session.rollback()
return jsonify({ "code": 400, "result": "DB Error" })
if(update_result==0):
result = { "code": 401, "result": "No affected rows" }
else:
result = { "code": 200, "result": "Success" }
else :
result["code"] = 301
result["result"] = "No Such User"
return jsonify(result)
def post(self):
parser = reqparse.RequestParser()
parser.add_argument('title', type=str, required=True)
parser.add_argument('content', type=str, required=True)
parser.add_argument('labels', type=int, required=False, action="append")
args = parser.parse_args()
labels = []
try:
#Checks for duplicate
if db_session.query(Blog).filter(Blog.title.ilike("%"+args.title+"%"))\
.first() is not None:
return self.response.warning(\
self.response.WARNINGS['duplicate'](args.title), None)
#Check of the labels exist
for label in args.labels:
label_instance = db_session.query(BlogLabel).get(label)
if label_instance is None:
return self.response.warning("Label with id %d does not exist." % label,
None)
labels.append(label_instance)
blog = Blog(title=args.title,
author_id=1, #TODO: Change this to user's current session
content=args.content)
#Now append the labels to the blog
for label_instance in labels:
blog.labels.append(label_instance)
db_session.add(blog)
db_session.commit()
return self.response.success(None)
except exc.SQLAlchemyError:
return self.response.error('Error creating new blog',
self.response.ERROR_SQLALCHEMY)
def mutate(self, info, group_id, user_id, id_from_token):
group_id = int(group_id)
user_id = int(user_id)
group = db.query(Group).filter_by(id=group_id).first()
if group is None:
raise Exception("No group with this ID found!")
admin_role = db.query(GroupUser.role_in_group).filter_by(
user_id=group.admin_id, group_id=group_id).first()
user_role = db.query(GroupUser.role_in_group).filter_by(
user_id=id_from_token, group_id=group_id).first()
if admin_role.role_in_group == RoleEnum.ORGANIZER:
if user_role is not None and user_role.role_in_group == RoleEnum.ORGANIZER:
try:
db.add(
GroupUser(group_id=group_id,
user_id=user_id,
role_in_group=RoleEnum.ORGANIZER))
commit_with_check(db)
except:
db.rollback()
return AddOrganizer(ok=True, message="Users have been added!")
return AddOrganizer(
ok=True,
message="Only admin with role ORGANIZER can add ORGANIZER!")
def getTopUserList():
companyId = request.form.get("companyId") and request.form.get("companyId") or session.get("companyId")
groupId = request.form.get("groupId")
condFilter = [1==1]
if companyId:
condFilter.append(PointTotal.companyId==companyId)
else :
return jsonify({"code":400, "result":"no company id"})
if groupId:
condFilter.append(PointTotal.groupId==groupId)
cnt = db_session.query(func.count())\
.filter(*condFilter)\
.first()[0]
halfCnt = math.ceil(cnt * 0.5)
result_rows = db_session.query(PointTotal, User.name)\
.join(User, PointTotal.userId==User.userId)\
.filter(*condFilter)\
.order_by(PointTotal.pointTotal.desc()).limit(halfCnt)
result = { "code":200, "entries": list() }
rank = 1
for row, name in result_rows :
rowJSON = row.tojson()
rowJSON["name"] = name
rowJSON["rank"] = rank
result["entries"].append(rowJSON)
rank += 1
return jsonify(result)
async def resolve_search(parent, info, search_text, id_from_token):
string_list = search_text.split(" ")
items = list()
wishlists = list()
users = list()
for i in string_list:
temp = db.query(UserDB).filter_by(nickname=i).first()
if temp is not None:
users.append(temp)
temp = db.query(UserDB).filter_by(user_name=i).first()
if temp is not None:
users.append(temp)
temp = db.query(UserDB).filter_by(surname=i).first()
if temp is not None:
users.append(temp)
temp = db.query(WishlistDB).filter_by(title=i,
access_level='ALL').all()
if len(temp) != 0:
for j in temp:
items.append(j)
temp = db.query(ItemDB).filter_by(title=i,
access_level='ALL').all()
if len(temp) != 0:
for j in temp:
items.append(j)
return items + wishlists + users
def index():
"""
Handles requests to the main page of the website
This function handles all of the requests to the default page it takes
no parameters, but relies on the request.form global for the flask
session to populate the indicators for the selected category
:returns: HTML file representing the index page
"""
# the list of selectable indicators
indicators = db_session.query(IndCons.indicator, IndCons.hexid).all()
# sort the list of indicators alphabetically
# Get a list of all of the geography/year combos in the db
db_geo = db_session.query(Estimates.geography, Estimates.year).distinct()
# Create a dict to store each geo and the years available for that geo
geos = {}
# place the years into different geographies
for g in db_geo:
if g.geography in geos.keys():
geos[g.geography].append(g.year)
else:
geos[g.geography] = [g.year]
# Sort the lists of years before displaying them
for g in geos.keys():
geos[g].sort()
return render_template("index.html", indicators=indicators, geoyears=geos)
def putPointFunc(userId, point, where, why):
#이벤트 체크 - 이벤트 당첨자(=해당 날)이면 point 2배
if amIinEvent(userId) == True :
point = point * 2
insert_row = PointHistory(userId, point, where, why)
db_session.add(insert_row)
if where.startswith("Praise"):
myPoint_update_result = db_session.query(PointTotal).filter_by(userId=userId)\
.update({PointTotal.pointTotal: PointTotal.pointTotal + point, PointTotal.pointPraise: PointTotal.pointPraise + point})
elif where.startswith("Thanks"):
myPoint_update_result = db_session.query(PointTotal).filter_by(userId=userId)\
.update({PointTotal.pointTotal: PointTotal.pointTotal + point, PointTotal.pointThanks: PointTotal.pointThanks + point})
elif where.startswith("Good") :
myPoint_update_result = db_session.query(PointTotal).filter_by(userId=userId)\
.update({PointTotal.pointTotal: PointTotal.pointTotal + point, PointTotal.pointGood: PointTotal.pointGood + point})
else :
myPoint_update_result = False
if not (myPoint_update_result > 0) : #insert when no row
insert_row2 = PointTotal(userId, request.form['companyId'], request.form['groupId'], point, where)
db_session.add(insert_row2)
try:
db_session.commit()
except exc.IntegrityError as e:
db_session.rollback()
if(insert_row.id==None or (not (myPoint_update_result > 0) )): # and insert_row2.inserted_primary_key==None)):
return False
else:
return { "pointId" : insert_row.id, "point" : point }
def mutate(self, info, item_id, id_from_token):
item = db.query(Item).filter_by(id=item_id).first()
if item is None:
raise Exception("No item was found with this ID!")
if item.giver_id is not None:
return SetGiverId(ok=False,
message="This item has already reserved!")
if item.status == StatusEnum.PERFORMED:
raise Exception("This item is already performed!")
if item.status == StatusEnum.RESERVED:
if item.giver_id == id_from_token:
# cancel reservation by giver_id user
item.giver_id = None
commit_with_check(db)
user = db.query(User).filter_by(id=item.owner_id).first()
return SetGiverId(ok=True,
message="Reserving of item is canceled!!",
user_=user)
else:
raise Exception("This item is already reserved!")
item.giver_id = id_from_token
item.status = StatusEnum.RESERVED
commit_with_check(db)
user = db.query(User).filter_by(id=item.owner_id).first()
return SetGiverId(ok=True, message="Item was reserved!!", user_=user)
def get_rc_children_meta(parent_id):
rc = (db_session.query(ResourceCertificate).filter(
ResourceCertificate.parent_id == parent_id).all())
roas = db_session.query(Roa).filter(Roa.parent_id == parent_id).all()
f = {"parent": parent_id, "data": []}
for c in rc:
c = c.asdict()
d = {
"id": c["id"],
"value": c["certificate_name"],
"webix_kids": c["has_kids"],
"mft_name": c["manifest"],
"crl_name": c["crl"],
"icon": c["validation_status"].lower(),
}
f["data"].append(d)
for roa in roas:
roa = roa.asdict()
d = {
"id": roa["id"],
"value": roa["roa_name"],
"webix_kids": False,
"icon": roa["validation_status"].lower(),
}
f["data"].append(d)
return jsonify(f)
def downloadSurvey():
if not session.get("companyId") and session.get("level")==2:
db_result = db_session.query(User, Answer)\
.join(Answer, Answer.userId==User.userId)\
.all()
elif session.get("companyId"):
db_result = db_session.query(User, Answer)\
.join(Answer, Answer.userId==User.userId)\
.filter(User.companyId==session["companyId"])\
.all()
# query_sets = Answer.query.join.limit(5).all()
# column_names = ['answerId']
# return excel.make_response_from_query_sets(query_sets, column_names, "xls")
else:
abort(401)
records = list()
userKeys = User.__table__.columns.keys()
answerKeys = Answer.__table__.columns.keys()
for row1, row2 in db_result:
od = OrderedDict()
rowJSON1 = row1.tojson()
for k in userKeys:
od[k] = rowJSON1[k]
rowJSON2 = row2.tojson()
for k in answerKeys:
od[k] = rowJSON2[k]
records.append(od)
return excel.make_response_from_records(records, "xls") #여기서 컬럼순서가 망가진다...
def get(self):
amount = current_app.config['CATCHAT_MESSAGE_PER_PAGE']
messages = db_session.query(Message).order_by(
Message.timestamp.asc())[-amount:]
user_amount = db_session.query(User).count()
return render_template('chat/home.html',
messages=messages,
user_amount=user_amount)
def resolve_lists(parent, info, id_from_token):
results = db.query(GroupListModel).filter_by(group_id=parent.id).all()
if parent.access_level == GroupAccessEnum.OPEN:
return results
user = db.query(GroupUserModel).filter_by(user_id=id_from_token, group_id=parent.id).first()
if parent.access_level == GroupAccessEnum.CLOSE and user is not None:
return results
return []
def resolve_items(parent, info, id_from_token):
if parent.access_level == AccessLevelEnum.ALL:
return db.query(ItemModel).filter_by(list_id=parent.id).all()
if parent.access_level == AccessLevelEnum.NOBODY and parent.user_id == id_from_token:
return db.query(ItemModel).filter_by(list_id=parent.id).all()
if parent.access_level == AccessLevelEnum.FRIENDS and db.query(FriendShipModel).filter_by(user_id_1=parent.user_id,
user_id_2=id_from_token).first():
return db.query(ItemModel).filter_by(list_id=parent.id, access_level=AccessLevelEnum.FRIENDS).all()
return []
async def resolve_group(parent, info, group_id, id_from_token):
group = db.query(GroupDB).filter_by(id=group_id).first()
if group.access_level == GroupAccessEnum.OPEN:
return group
user_group = db.query(GroupUserDB).filter_by(
user_id=id_from_token, group_id=group_id).first()
if group.access_level == GroupAccessEnum.CLOSE and user_group is not None:
return group
raise Exception("Access denied!")
def listGoodPost(one=False):
userId = request.form['userId']
companyId = request.form['companyId']
groupId = request.form['groupId']
listType = request.form.get("listType")
page = request.form.get("page")
if page==None: page = 1
else: page = int(page)
pageCnt = 10
condFilter = [1==1]
if listType=='my':
condFilter.append(GoodPost.userId==userId)
elif one==True:
pageCnt = 1
if request.form.get('postId') :
condFilter.append(GoodPost.postId==request.form.get('postId'))
likeCnt_query = db_session.query(GoodLike.postId, func.count().label('likeCnt') ).group_by(GoodLike.postId).subquery('likeCnt_query')
replyCnt_query = db_session.query(GoodReply.postId, func.count().label('replyCnt') ).filter_by(isValid=1).group_by(GoodReply.postId).subquery('replyCnt_query')
result_rows = db_session.query( GoodPost, User.name, User.thumbURL, User.jobTitle, Group.name, func.COALESCE(likeCnt_query.c.likeCnt,0), func.COALESCE(replyCnt_query.c.replyCnt,0) )\
.join(User, GoodPost.userId==User.userId)\
.join(Group, GoodPost.groupId==Group.groupId)\
.outerjoin(likeCnt_query, GoodPost.postId==likeCnt_query.c.postId)\
.outerjoin(replyCnt_query, GoodPost.postId==replyCnt_query.c.postId)\
.filter(GoodPost.isValid==1).filter(GoodPost.companyId==companyId)\
.filter(*condFilter)\
.group_by(GoodPost.postId)\
.order_by(GoodPost.postId.desc())[ (page-1)*pageCnt : page*pageCnt ]
#.filter_by(groupId=groupId)
result = { "code": 200, "posts": list(), "page": page }
for postRow, userName, thumbURL, jobTitle, groupName, likeCnt, replyCnt in result_rows :
rdateStr = postRow.rdate.strftime('%y/%m/%d %H:%I')
rowJSON = postRow.tojson()
rowJSON["rdate"] = rdateStr
if postRow.isHidden==1 :
rowJSON["userName"] = "******"
rowJSON["thumbURL"] = ""
rowJSON["jobTitle"] = "○○"
rowJSON["groupName"] = "○○○○○"
else :
rowJSON["userName"] = userName
rowJSON["thumbURL"] = thumbURL
rowJSON["jobTitle"] = jobTitle
rowJSON["groupName"] = groupName
rowJSON["likeCnt"] = likeCnt
rowJSON["replyCnt"] = replyCnt #cause of doubled count problem
result["posts"].append(rowJSON)
if one==True:
return result["posts"] and result["posts"][0] or None
return jsonify(result)
def get_device_location(mac_address, use_asynchronous_data=False):
items = get_devices_and_users(mac_address=mac_address)
registered_users = items['registered_users']
unknown_devices = items['unknown_devices']
if len(registered_users) == len(unknown_devices) == 0:
try:
location_api_extractor = get_location_api_extractor()
if location_api_extractor:
info = location_api_extractor.get_client_information(
mac_address)
if info:
item = {}
location_info = None
if location_api_extractor.deployment_type == 'On-premises':
if 'mapHierarchyString' in info['mapInfo']:
hierarchy = info['mapInfo']['mapHierarchyString']
coord_x = info['mapCoordinate']['x']
coord_y = info['mapCoordinate']['y']
last_modified = info['statistics'][
'lastLocatedTime']
location_info = __serialize_location_information(
coord_x, coord_y, hierarchy, last_modified,
'static')
elif location_api_extractor.deployment_type == 'Cloud' and location_api_extractor.demo:
floor_name = info['floors']
floor = db_session.query(Floor).filter(
Floor.name == floor_name).first()
if floor:
floor_hierarchy = floor.get_hierarchy()
x, y = __calculate_x_y_coordinates(
floor, info['lat'], info['lng'])
location_info = __serialize_location_information(
x, y, floor_hierarchy, info['seenString'],
'demo')
user = db_session.query(RegisteredUser).filter(
RegisteredUser.mac_address == mac_address).first()
if user:
item['user_info'] = __serialize_user_information(
user.name, user.phone, user.id)
registered_users.append(item)
else:
unknown_devices.append(item)
item['location'] = location_info
else:
raise Exception("No server has been setup")
except:
traceback.print_exc()
pass
return items
def resolve_groups(parent, info, id_from_token):
group_user = db.query(GroupUserModel).filter_by(user_id=parent.id).all()
if id_from_token == parent.id:
return group_user
response = list()
for i in group_user:
group = db.query(GroupModel).filter_by(id=i.group_id).first()
if group.access_level == GroupAccessEnum.OPEN:
response.append(group)
return response
def ranking():
userId = request.form['userId']
companyId = request.form.get("companyId")
groupId = request.form.get("groupId")
page = request.form.get("page")
# if page==None: page = 1
# else: page = int(page)
# pageCnt = 10
if request.form.get("rankType"):
if request.form["rankType"]=='thanks':
orderCol = PointTotal.pointThanks
elif request.form["rankType"]=='priase':
orderCol = PointTotal.pointPraise
elif request.form["rankType"]=='good':
orderCol = PointTotal.pointGood
else :
orderCol = PointTotal.pointTotal
else :
orderCol = PointTotal.pointTotal
if companyId!=None:
if groupId!=None:
result_rows = db_session.query(PointTotal, Group.name).join(Group, PointTotal.groupId==Group.groupId)\
.filter(PointTotal.companyId==companyId).filter(PointTotal.groupId==groupId)\
.order_by(orderCol.desc())[0:10]
# PointTotal.query.filter_by(companyId=companyId).filter_by(groupId=groupId).order_by(PointTotal.pointTotal.desc())[ (page-1)*pageCnt : page*pageCnt ]
else:
result_rows = db_session.query(PointTotal, Group.name).join(Group, PointTotal.groupId==Group.groupId)\
.filter(PointTotal.companyId==companyId)\
.order_by(orderCol.desc())[0:10]
# [ (page-1)*pageCnt : page*pageCnt ]
result = { "code": 200, "ranking": list(), "page": page, "rankType": request.form.get("rankType") }
rank = 1
for row, groupName in result_rows :
rowJSON = row.tojson()
rowJSON["userName"] = row.mate.name
rowJSON["jobTitle"] = row.mate.jobTitle
rowJSON["thumbURL"] = row.mate.thumbURL
rowJSON["groupName"] = groupName
rowJSON["rank"] = rank
# if str(rowJSON["userId"]) == userId :
# result.update({"myRank": rank, "myPoint": row.pointTotal,
# "myPointP": row.pointPraise, "myPointT": row.pointThanks, "myPointG": row.pointGood
# })
result["ranking"].append(rowJSON)
rank = rank + 1
# result["totalCnt"] = len(result["ranking"])
return jsonify(result)
async def resolve_wishlist(parent, info, list_id, id_from_token):
wishlist = db.query(WishlistDB).filter_by(id=int(list_id)).first()
if wishlist.access_level == AccessLevelEnum.ALL:
return wishlist
if wishlist.access_level == AccessLevelEnum.NOBODY and wishlist.user_id == id_from_token:
return wishlist
if wishlist.access_level == AccessLevelEnum.FRIENDS and db.query(
FSDB).filter_by(user_id_1=wishlist.user_id,
user_id_2=id_from_token).first():
return wishlist
raise Exception("Access denied!")
async def resolve_item(parent, info, item_id, id_from_token):
item = db.query(ItemDB).filter_by(id=int(item_id)).first()
if item.access_level == AccessLevelEnum.ALL:
return item
if item.access_level == AccessLevelEnum.NOBODY and item.owner_id == id_from_token:
return item
if item.access_level == AccessLevelEnum.FRIENDS and db.query(
FSDB).filter_by(user_id_1=item.owner_id,
user_id_2=id_from_token).first():
return item
raise Exception("Access denied!")
async def resolve_index(parent, info):
wishlists = db.query(WishlistDB) \
.join(WishlistDB.user_owner) \
.join(UserDB.friends) \
.filter(WishlistDB.access_level == 'ALL') \
.all()
items = db.query(ItemDB) \
.join(ItemDB.owner) \
.join(UserDB.friends) \
.filter(ItemDB.access_level == 'ALL') \
.all()
return items + wishlists
def before_first_request():
print("FIRST REQUEST RECEIVED")
try:
db_session.query(CMXServer).first()
except ProgrammingError as e:
if str(e).__contains__("does not exist"):
# DB Tables have not been created
invoke_db_migration()
else:
# Unknown error
raise Exception(e)
except Exception as e:
traceback.print_exc()
async def resolve_news(parent, info, id_from_token):
wishlists = db.query(WishlistDB)\
.join(WishlistDB.user_owner)\
.join(UserDB.friends)\
.filter(and_(UserDB.id == id_from_token, or_((WishlistDB.access_level == 'ALL'), (WishlistDB.access_level == 'FRIENDS'))))\
.all()
items = db.query(ItemDB)\
.join(ItemDB.owner)\
.join(UserDB.friends) \
.filter(and_(UserDB.id == id_from_token,
or_((ItemDB.access_level == 'ALL'), (ItemDB.access_level == 'FRIENDS'))))\
.all()
return items + wishlists
def delete(server_id):
output = None
try:
server = db_session.query(MerakiServer).filter(MerakiServer.id == server_id).first()
if request.method == 'GET':
output = render_template("server/meraki_server/delete.html", object=server, deployment="Cloud")
else:
server = db_session.query(MerakiServer).filter(MerakiServer.id == request.form["meraki_server_id"]).delete()
output = redirect(url_for('mod_meraki_server.show'))
except:
traceback.print_exc()
output = redirect(url_for('error'))
db_session.rollback()
return output
def mutate(self, info, list_id, items_id, id_from_token):
wlist = db.query(Wishlist).filter_by(id=list_id).first()
if wlist is None:
raise Exception("No wishlist with this ID found!")
if wlist.user_id != id_from_token:
return AddItemsToList(ok=False, message="Access denied!")
for item_id in items_id:
item = db.query(Item).filter_by(id=item_id).first()
if item.status == StatusEnum.RESERVED or \
item.status == StatusEnum.PERFORMED or item.owner_id != id_from_token:
continue
item.list_id = list_id
item.access_level = wlist.access_level
commit_with_check(db)
return AddItemsToList(ok=True, message="Items were added to wishlist!")
def verticalization_remove(server_id):
output = {
'error': None,
'error_message': None,
'redirect_url': None,
}
try:
form_json = request.json
location_system = db_session.query(LocationSystem).filter(
LocationSystem.id == server_id).first()
campi = location_system.campuses
for campus in campi:
campus.vertical_name = None
for building in campus.buildings:
building.vertical_name = None
for floor in building.floors:
floor.vertical_name = None
for zone in floor.zones:
zone.vertical_name = None
zone.max_occupation = -1
db_session.commit()
output['redirect_url'] = url_for('mod_cmx_server.details',
server_id=server_id)
except Exception as e:
output['error'] = True
output['error_message'] = str(e)
traceback.print_exc()
db_session.rollback()
output = Response(json.dumps(output), mimetype='application/json')
return output
def engagement_screen_dwell_select():
if request.method == 'GET':
floors = db_session.query(Floor).all()
output = render_template("engagement/screen/engagement_select.html", floors=floors)
else:
output = redirect(url_for('.engagement_screen_dwell_show', hierarchy=request.form['hierarchy']))
return output
def show():
try:
servers = db_session.query(MerakiServer).all()
return render_template("server/meraki_server/list.html", object=servers, deployment="Cloud")
except:
traceback.print_exc()
return "No response"
def mutate(self, info, to_user_id, id_from_token):
if db.query(FS).filter_by(user_id_1=id_from_token,
user_id_2=to_user_id).first() is not None:
SendFriendRequest(ok=False, message="You are already friends!")
if db.query(FR).filter_by(user_id_from=id_from_token,
user_id_to=to_user_id).first() is not None:
SendFriendRequest(
ok=False,
message="You have already sent friend request to this user!")
try:
db.add(FR(user_id_from=id_from_token, user_id_to=to_user_id))
commit_with_check(db)
except:
db.rollback()
return SendFriendRequest(ok=True,
message="Friend request has been sent!")
def command_list(message_text, room_id, person_id):
second_word = __replace_string_case_insensitive(message_text, 'list',
'').strip().lower()
post_markdown = None
post_text = None
if second_word in ['users', 'user', 'asset', 'assets']:
users = db_session.query(RegisteredUser).all()
post_text = '{} items were found:'.format(len(users))
post_markdown = "# {}\n".format(post_text)
user_names = []
for user in users:
user_names.append(user.name)
post_markdown += '{}. {} ({})\n'.format(len(user_names), user.name,
user.mac_address)
post_text += ' {}'.format(user_names)
elif second_word in ['device', 'devices']:
overview = get_devices_divided_by_hierarchy()
"""
post_text = '{} items were found:'.format(len(users))
post_markdown = "# {}\n".format(post_text)
user_names = []
for user in users:
user_names.append(user.name)
post_markdown += '{}. {} ({})\n'.format(len(user_names), user.name, user.mac_address)
post_text += ' {}'.format(user_names)
"""
count = 0
for hierarchy in overview:
count += len(hierarchy["unknown_devices"])
# this line below takes the first device of each hierarchy and adds to the list
devices = [
item["unknown_devices"][0]['mac_address'] for item in overview
]
post_text = "{} unknown devices and were found. Here's a sample:".format(
count)
post_markdown = "# {}\n".format(post_text)
for i in range(len(devices)):
post_markdown += '{}. {}\n'.format(i, devices[i])
post_text += ' {}'.format(devices)
else:
post_text = 'Invalid list command. Try list users/assets or list devices'
print('Posting on Spark... {}'.format(post_text))
write_to_spark(room_id, None, None, post_text, post_markdown, None)
return post_text
def mutate(self, info, list_id, items_id, id_from_token):
wlist = db.query(Wishlist).filter_by(id=list_id).first()
if wlist is None:
raise Exception("No wishlist with this ID found!")
if wlist.user_id != id_from_token:
return DeleteItemsFromList(ok=False, message="Access denied!")
for item_id in items_id:
item = db.query(Item).filter_by(id=item_id).first()
item.list_id = None
item.access_level = AccessLevelEnum.NOBODY
commit_with_check(db)
return DeleteItemsFromList(
ok=True,
message=
"Items were deleted from wishlist and were pasted to default wishlist!"
)
def user_init_func(row): if len(row['name'])==0: return None group_row = db_session.query(Group.groupId).filter(Group.companyId==companyId).filter(Group.name==row['groupName']).first() if group_row: groupId = group_row[0] elif len(row['groupName']) > 0 : newGroup = Group(companyId, row['groupName']) db_session.add(newGroup) try: db_session.commit() except exc.IntegrityError as e: db_session.rollback() if(newGroup.groupId==None): return None else: groupId = newGroup.groupId else : groupId = None # companyId, jobClass, groupId, email, accessKey, name, gender, birth, jobType, workYear, workMonth, jobTitle, jobTitleNo u = User(companyId, jobClass, groupId, row['email'], row['accessKey'], row['name'], row['gender'], row['birth'], row['jobType'], row['workYear'], row['workMonth'], row['jobTitle'], row['jobTitleNo']) return u
def resolve_items_owner(parent, info, id_from_token):
response = list()
items = db.query(ItemModel).filter_by(owner_id=parent.id, list_id=None, status=StatusEnum.FREE).all()
if len(items) == 0:
return items
for item in items:
if item.access_level == AccessLevelEnum.ALL:
response.append(item)
if item.access_level == AccessLevelEnum.NOBODY and item.owner_id == id_from_token:
response.append(item)
if item.access_level == AccessLevelEnum.FRIENDS and db.query(FriendShipModel).filter_by(user_id_1=item.owner_id,
user_id_2=id_from_token).first():
response.append(item)
if len(response) > 0:
return response
return []
def get_user(email):
user = db_session.query(User) \
.select_from(User) \
.filter(User.email == email) \
.first()
return user
def confirmMsg():
msgId = request.form['msgId']
msgType = request.form['msgType']
userId = request.form['userId']
update_result = db_session.query(Message).filter_by(id=msgId).filter_by(rUserId=userId).filter_by(msgType=msgType).filter_by(isRead=0)\
.update({'isRead': 1, 'readTime': datetime.now()})
try:
db_session.commit()
except exc.IntegrityError as e:
db_session.rollback()
if(update_result==None):
result = { "code": 400, "result": "DB Error" }
elif(update_result==0):
result = { "code": 401, "result": "No affected rows" }
else:
result = { "code": 200, "result": "Success" }
# 포인트 적립
msgTypeStr = params["msgType"]=='1' and 'PraiseRcv' or 'ThanksRcv'
point_result = putPointFunc(request.form['userId'], 200, msgTypeStr, msgId)
if point_result!=False :
result["pointId"] = point_result["pointId"]
result["point"] = point_result["point"]
return jsonify(result)
def mutate(self, info, files, item_id, id_from_token):
item = db.query(Item).filter_by(id=item_id).first()
if item is None:
raise Exception("No item was found with this ID!")
if item.owner_id != id_from_token:
return AddPictures(ok=False, message="Access denied!")
# TODO maybe change names for pictures?
i = 0
for pic in files:
if check_format(pic):
i += 1
name = 'items/item_' + str(item.id) + '_' + str(i)
if upload_file(pic, Config.bucket, name):
try:
db.add(
ItemPicture(item_id=item.id, path_to_picture=name))
commit_with_check(db)
except:
db.rollback()
else:
return AddPictures(
ok=False, message="Pictures haven't been uploaded!")
else:
return AddPictures(
ok=False,
message=
"Only pictures (.png, .jpeg, .bmp) can be downloaded!")
return AddPictures(ok=True, messages="Pictures have been uploaded!")
def get_amount_by_user(username):
transactions = db_session.query(PaymentTransaction).filter_by(username=username).all()
amounts = 0
for transaction in transactions:
amounts = amounts + int(transaction.amount)
return str(amounts)
def kill():
if request.method == "POST":
book_id = request.json
book = db_session.query(Book).filter_by(id=book_id).first()
db_session.delete(book)
db_session.commit()
return redirect(url_for('odminko'))
def add_example(new):
print('New is: %s' % new)
if db_session.query(ExampleModel.id).filter_by(id=new).scalar() is None:
ex = ExampleModel()
ex.id = new
ex.field1 = 'ExampleString'
db_session.add(ex)
db_session.commit()
#return url_for('app.get_example', variable=new)
body = 'Success!'
response = jsonify(body)
response.status_code = 201
return response
else:
body = 'That example record already exists'
response = jsonify(body)
response.status_code = 404
return response
def sendMsgList():
userId = request.form['userId']
msgType = request.form['msgType']
page = request.form.get("page")
if page==None: page = 1
else: page = int(page)
pageCnt = 10
# result_rows = Message.query.filter_by(sUserId=userId).filter_by(msgType=msgType).order_by(Message.id.desc())[ (page-1)*pageCnt : page*pageCnt ]
result_rows = db_session.query(Message, User.name, User.thumbURL, User.jobTitle, Group.name)\
.join(User, Message.rUserId==User.userId)\
.join(Group, Message.groupId==Group.groupId)\
.filter(Message.sUserId==userId).filter(Message.msgType==msgType)\
.order_by(Message.id.desc())[ (page-1)*pageCnt : page*pageCnt ]
result = { "code": 200, "msgs": list(), "page": page }
for row, userName, thumbURL, jobTitle, groupName in result_rows :
rdateStr = row.rdate.strftime('%y/%m/%d %H:%I')
rowJSON = row.tojson()
rowJSON["rdate"] = rdateStr
rowJSON["userName"] = userName
rowJSON["thumbURL"] = thumbURL
rowJSON["jobTitle"] = jobTitle
rowJSON["groupName"] = groupName
result["msgs"].append(rowJSON)
return jsonify(result)
def cmsReportIndex(userKey):
des = AESCipher('kit-works-lnp')
try:
decKey = urllib.unquote(userKey)
userId = des.decrypt(userKey)
try:
userId = int(userId)
except ValueError:
abort(403) #not allowed
except:
return "Not allowed access"
row, groupName, endDate = db_session.query(User, Group.name, Company.func_100days_edate)\
.join(Group, Group.groupId==User.groupId)\
.join(Company, Company.companyId==User.companyId)\
.filter(User.userId==userId).first()
if not row:
abort(401)
userData = { "userId":userId, "companyId":row.companyId, "groupId":row.groupId, "name":row.name, "jobTitle":row.jobTitle, "email":row.email, "groupName":groupName, "birth":str(row.birth)[0:7], "workYear":row.workYear, "workMonth":row.workMonth }
userData["jobType"] = row.jobType=='reg' and '정규직' or '비정규직'
userData["endDate"] = endDate.strftime("%Y-%m-%d")
activities = getActivityCnts(row.companyId, userId)
surveyPoints = getSurveyPoints(userId)
return render_template('cms_report.html', key=userKey, user=userData, data=activities, survey=surveyPoints)
def setPushToken():
userId = request.form['userId']
pushTokenStr = request.form['pushToken']
os = request.form['os']
update_result = db_session.query(PushToken).filter_by(userId=userId).update({'pushToken': pushTokenStr, 'os' : os})
try:
db_session.commit()
except exc.IntegrityError as e:
db_session.rollback()
if(update_result==None):
result = { "code": 400, "result": "DB Error" }
elif(update_result==0):
pushToken = PushToken(userId, pushTokenStr, os)
db_session.add(pushToken)
try:
db_session.commit()
except exc.IntegrityError as e:
db_session.rollback()
result = { "code": 201, "result": "Success to insert" }
else:
result = { "code": 200, "result": "Success to update" }
return jsonify(result)
def cmsLogin():
if request.method == 'GET':
return render_template('cmsLogin.html')
else :
email = request.form['email']
pwInput = request.form['password']
db_result = db_session.query(Account, Company.name, Company.thumbURL, func.password(pwInput))\
.outerjoin(Company, Company.companyId==Account.companyId)\
.filter(Account.email==email).first()
account, companyName, companyLogo, pwInputEnc = db_result
if account :
if pwInputEnc==account.password :
errMsg = None
else :
errMsg = "비밀번호가 맞지 않습니다."
else :
errMsg = "허용되지 않는 계정입니다."
if errMsg!=None:
return render_template('cmsLogin.html', email=email, errMsg=errMsg)
session['logged_in'] = True
session['accountId'] = account.accountId
session['email'] = email
session['level'] = account.level
session['companyId'] = account.companyId
session['companyName'] = companyName
session['companyLogo'] = companyLogo
if account.level==2:
return redirect(url_for('cmsSuperIndex'))
else:
return redirect(url_for('cmsIndex'))
def amIinEvent(userId):
if str(userId)=='10004': return True
todayDate = datetime.today().strftime("%Y-%m-%d")
event_result = db_session.query(EventDouble).filter_by(userId=userId).filter_by(eventDate=todayDate).count()
if event_result > 0 :
return True
return False
def homeData():
userId = request.form['userId']
groupId = request.form['groupId']
companyId = request.form['companyId']
today = datetime.today()
todayDate = today.strftime("%Y-%m-%d")
result = { "code" : 200 }
# 내 포인트
result["myPointTotal"] = None
point_result = db_session.query(PointTotal.pointTotal).filter(PointTotal.userId==userId).first()
if point_result!=None :
for point in point_result:
result["myPointTotal"] = point
# 내 랭킹, 총 회사인원수
if result["myPointTotal"]!=None:
rank_result = PointTotal.query.filter(PointTotal.companyId==companyId)\
.with_entities( func.count(), func.count( func.IF(PointTotal.pointTotal > result["myPointTotal"], 1, None) ) )
for totalCnt, higherCnt in rank_result:
result["totalCnt"] = totalCnt
result["myRank"] = higherCnt!=None and higherCnt + 1 or None
#이벤트 체크
result["isEvent"] = amIinEvent(userId)
#오늘의 행복도
result["todayHappiness"] = todayHappiness(True)
# 수신 메시지(타입별) 총개수
msgRcv_result = Message.query.filter_by(rUserId=userId)\
.with_entities( func.count( func.IF(Message.msgType==1, 1, None) ), func.count( func.IF(Message.msgType==2, 1, None) ) )
for cnt1, cnt2 in msgRcv_result :
result["totalMsgRcv"] = { "praise": cnt1, "thanks": cnt2 }
# 발신 메시지(타입별) 총개수
msgSend_result = Message.query.filter_by(sUserId=userId)\
.with_entities( func.count( func.IF(Message.msgType==1, 1, None) ), func.count( func.IF(Message.msgType==2, 1, None) ) )
for cnt1, cnt2 in msgSend_result :
result["totalMsgSend"] = { "praise": cnt1, "thanks": cnt2 }
# 오늘 수신한 메시지(타입별) 개수
todayMsgRcv_result = Message.query.filter_by(rUserId=userId).filter(Message.rdate >= todayDate)\
.with_entities( func.count( func.IF(Message.msgType==1, 1, None) ), func.count( func.IF(Message.msgType==2, 1, None) ) )
for cnt1, cnt2 in todayMsgRcv_result :
result["todayMsgRcv"] = { "praise": cnt1, "thanks": cnt2 }
#가장 최근 좋은일 게시물
# result["goodpost"] = listGoodPost(True)
#좋은일 게시물 오늘 총 갯수
result["goodpost_todayCnt"] = GoodPost.query.filter(GoodPost.isValid>0).filter(GoodPost.rdate >= todayDate).count()
return jsonify(result)
def getTopGroupList():
companyId = request.form.get("companyId") and request.form.get("companyId") or session.get("companyId")
cnt = db_session.query(func.count(distinct(PointTotal.groupId)))\
.filter(PointTotal.companyId==companyId).first()[0]
oneThirdCnt = math.ceil(cnt * 0.3)
result_rows = db_session.query(PointTotal.companyId, PointTotal.groupId, Group.name, func.sum(PointTotal.pointTotal).label("totalPoint"), func.sum(PointTotal.pointPraise), func.sum(PointTotal.pointThanks), func.sum(PointTotal.pointGood) )\
.join(Group, PointTotal.groupId==Group.groupId)\
.group_by(PointTotal.groupId)\
.filter(PointTotal.companyId==companyId)\
.order_by("totalPoint DESC").limit(oneThirdCnt)
result = { "entries": list() }
rank = 1
for companyId, groupId, name, pointTotal, pointPraise, pointThanks, pointGood in result_rows :
result["entries"].append({"rank":rank, "companyId":companyId, "groupId":groupId, "name":name, "totalPoint":int(pointTotal), "praisePoint":int(pointPraise), "thanksPoint":int(pointThanks), "goodPoint":int(pointGood) })
rank += 1
return jsonify(result)
def cmsIndex():
if request.args.get("id"):
session["companyId"] = request.args["id"]
db_result = db_session.query(Company.name, Company.thumbURL).filter(Company.companyId==request.args["id"]).first()
name, thumbURL = db_result
session["companyName"] = name
session["companyLogo"] = thumbURL
if not session.get('companyId'):
return "Not allowed access (No Company Info)"
return render_template('cms_partner.html', data=session)
def sendMsg():
params = dict()
params["sUserId"] = request.form['userId']
params["rUserId"] = request.form['rUserId']
params["companyId"] = request.form['companyId']
params["groupId"] = request.form['groupId']
params["msgType"] = request.form['msgType'] # 1:칭찬, 2:감사
params["msg"] = request.form['msg']
#일일한도 체크
todayCount = myTodayCounts(request.form['userId'], "Message");
if params["msgType"]==1 and todayCount["Praise"] >= 3 :
return jsonify({ "code": 310, "result": "Limit today's praise message" })
elif params["msgType"]==2 and todayCount["Thanks"] >= 3 :
return jsonify({ "code": 311, "result": "Limit today's thanks message" })
message = Message(**params)
db_session.add(message)
try:
db_session.commit()
except exc.IntegrityError as e:
db_session.rollback()
return jsonify({ "code": 400, "result": "DB Error" })
if(message.id==None):
result = { "code": 400, "result": "DB Error" }
else:
result = { "code": 200, "result": "Success" }
# 포인트 적립
msgTypeStr = params["msgType"]=='1' and 'PraiseSend' or 'ThanksSend'
point_result = putPointFunc(request.form['userId'], 100, msgTypeStr, message.id)
if point_result!=False :
result["pointId"] = point_result["pointId"]
result["point"] = point_result["point"]
# 상대방이 오늘 중 메시지 3번째 수신 확인 후, 그러면 푸쉬 보내기.
today = datetime.today()
todayDate = today.strftime("%Y-%m-%d")
rUserTodayRcvCnt = db_session.query(Message).filter_by(rUserId=params["rUserId"]).filter_by(msgType=params["msgType"]).filter(Message.rdate > todayDate).count()
if rUserTodayRcvCnt >= 3 :
pushMsg = params["msgType"]=='1' and '칭찬 메시지가 왔습니다.' or '감사 메시지가 왔습니다.'
push_result = sendPush(params["rUserId"], 11, '축하합니다! ' + pushMsg)
result["push"] = push_result
else : #보통 푸쉬.
pushMsg = params["msgType"]=='1' and '칭찬 메시지가 왔습니다.' or '감사 메시지가 왔습니다.'
push_result = sendPush(params["rUserId"], 1, pushMsg)
result["push"] = push_result
return jsonify(result)
def pushList():
companyId = request.form.get("companyId") and request.form.get("companyId") or session.get("companyId")
result_rows = db_session.query(PushMsg)\
.filter(PushMsg.companyId==companyId)\
.filter(PushMsg.isValid>0)\
.order_by(PushMsg.id.desc())
result = { "entries": list() }
for row in result_rows :
rowJSON = row.tojson()
rowJSON["repeatVal"] = row.repeatVal and int(row.repeatVal)
result["entries"].append(rowJSON)
return jsonify(result)
def getSurveyPoints(userId):
if not userId: return None
my_result = db_session.query(Result.nth, Result.perTotal).filter(Result.userId==userId).group_by(Result.nth).order_by(Result.nth).all()
result = {"first":0, "second":0, "zero":None}
for nth, per in my_result:
if nth==1:
result["first"] = per
elif nth==2:
result["second"] = per
else:
result["zero"] = per
return result
def login():
if request.method == "POST":
_username = request.form["username"];
_password = request.form["password"];
user = db_session.query(User) \
.filter(User.username.like(_username)) \
.first()
if user is not None and user.check_password(_password) is True:
login_user(user)
return redirect(url_for("landing.index"))
return render_template("login.html", display_register_form=False)
def delGoodPost():
update_result = db_session.query(GoodPost).filter_by(postId=request.form['postId'])\
.filter_by(userId=request.form['userId']).filter_by(isValid=1).update({'isValid': 0})
try:
db_session.commit()
except exc.IntegrityError as e:
db_session.rollback()
if(update_result==None):
result = { "code": 400, "result": "DB Error" }
elif(update_result==0):
result = { "code": 401, "result": "No affected rows" }
else:
result = { "code": 200, "result": "Success" }
return jsonify(result)
def uploadProfile():
userId = request.form.get("userId")
if userId==None :
return jsonify({"code": 304, "result": "No user ID"})
file = request.files['thumb']
if file and allowed_file(file.filename):
from werkzeug import secure_filename
now = datetime.now()
imgFilename = now.strftime("%y%m%d%H%M%S") + "_" + secure_filename(file.filename)
# file.save( app.config['UPLOAD_FOLDER'] + '/' + imgFilename )
from PIL import Image
try:
im = Image.open(file)
width = 200
imgW, imgH = im.size
if imgW > width:
height = int(width * (float(imgH) / float(imgW)))
im.thumbnail((width, height), Image.ANTIALIAS)
ext = imgFilename.rsplit('.', 1)[1]
if ext=='jpg' or ext=='JPG': ext='JPEG'
im.save(app.config['UPLOAD_FOLDER'] + '/' + imgFilename, ext.upper())
thumbURL = 'http://oasis.kwig.co.kr/loadImage/' + imgFilename
update_result = db_session.query(User).filter_by(userId=userId).update({'thumbURL': thumbURL})
try:
db_session.commit()
except exc.IntegrityError as e:
db_session.rollback()
if(update_result==None):
result = { "code": 400, "result": "DB Error" }
elif(update_result==0):
result = { "code": 401, "result": "No affected rows" }
else:
result = { "code": 200, "result": "Success", "imgURL": thumbURL }
except IOError:
print "cannot create thumbnail"
result = { "code": 500, "result": "File IO Error" }
else:
result = { "code": 303, "result": "File Not Aloowed" }
return jsonify(result)
def editGoodPostReply():
update_result = db_session.query(GoodReply).filter_by(replyId=request.form['replyId'])\
.filter_by(userId=request.form['userId']).filter_by(isValid=1)\
.update({ 'text': request.form['text'], 'rdate': datetime.now() })
try:
db_session.commit()
except exc.IntegrityError as e:
db_session.rollback()
if(update_result==None):
result = { "code": 400, "result": "DB Error" }
elif(update_result==0):
result = { "code": 401, "result": "No affected rows" }
else:
result = { "code": 200, "result": "Success" }
return jsonify(result)
def userList():
companyId = request.form.get("companyId") and request.form.get("companyId") or session.get("companyId")
result_rows = db_session.query(User, Group.name)\
.outerjoin(Group, Group.groupId==User.groupId)\
.filter(User.companyId==companyId)\
.filter(User.isValid>0)\
.order_by(User.userId.desc())
result = { "entries": list() }
for row, groupName in result_rows :
rowJSON = row.tojson()
rowJSON["groupName"] = groupName
rowJSON["birthYear"] = row.birth and row.birth.year
rowJSON["birthMonth"] = row.birth and row.birth.month
result["entries"].append(rowJSON)
return jsonify(result)
def groupList():
companyId = session.get('companyId')
if not companyId:
return jsonify({"code": 405, "result":"no company id"})
result_rows = db_session.query(Group, func.count(User.userId))\
.outerjoin(User, and_(Group.groupId==User.groupId, User.isValid>0) )\
.filter(Group.companyId==companyId)\
.group_by(Group.groupId)\
.order_by(Group.groupId.desc())
result = { "entries": list() }
for row, userCnt in result_rows :
rowJSON = row.tojson()
rowJSON["userCnt"] = userCnt
result["entries"].append(rowJSON)
return jsonify(result)
def partnerList():
result_rows = db_session.query(Company, CompanyCategory.name, Account.email, func.count(User.userId))\
.outerjoin(CompanyCategory, CompanyCategory.categoryId==Company.categoryId)\
.outerjoin(Account, and_(Company.companyId==Account.companyId, Account.level==0) )\
.outerjoin(User, and_(Company.companyId==User.companyId, User.isValid>0) )\
.group_by(Company.companyId)\
.filter(Company.isValid>0)\
.order_by(Company.companyId.desc())
result = { "entries": list() }
for row, category, email, userCnt in result_rows :
rowJSON = row.tojson()
rowJSON["rdate"] = row.rdate.strftime("%Y-%m-%d")
rowJSON["category"] = category
rowJSON["admin"] = email
rowJSON["userCnt"] = userCnt
result["entries"].append(rowJSON)
return jsonify(result)
def companyInfo():
companyId = session['companyId']
if not companyId:
return jsonify({"code": 405, "result": "no company id"})
result_rows = db_session.query(Company, CompanyCategory.name, Account.email)\
.outerjoin(CompanyCategory, CompanyCategory.categoryId==Company.categoryId)\
.outerjoin(Account, and_(Company.companyId==Account.companyId, Account.level==0) )\
.filter(Company.isValid>0).filter(Company.companyId==companyId)\
.limit(1)
result = { "entries": list() }
for row, category, email in result_rows :
rowJSON = row.tojson()
rowJSON["rdate"] = row.rdate.strftime("%Y-%m-%d")
rowJSON["category"] = category
rowJSON["admin"] = email
result["entries"].append(rowJSON)
return jsonify(result)
def syncProjects(username):
url='http://www.seerlabs.com:5556/api/getProject'
user = User.query.filter_by(username=username).first()
payload = {'email': user.email}
response = requests.post(url, data=payload)
responseJson = response.json()
print(responseJson['projects'])
for projectName in responseJson['projects']:
print projectName
(ret, ), = db_session.query(exists().where(Project.project_name==projectName))
if(ret):
project = Project.query.filter_by(project_name=projectName).first()
user.projects.append(project)
print "Existing"
else:
project = Project(projectName)
if(add_to_db(project)):
user.projects.append(project)
db_session.commit()
print "Project added"
