def get_screens(page, page_size):
parameters = {
'user_id': g.user.id,
'offset': (page - 1) * page_size,
'limit': page_size
}
screens = raw_db.query(screen_sqls.get_screens, parameters)
counts = raw_db.query(screen_sqls.total_counts, parameters).first().count
return screens, counts
def m_assets_statistics(set):
statistics = {}
entities = raw_db.query(asset_sqls.m_assets_statistics, set_id = set.id)
total_amount = raw_db.query(asset_sqls.m_total_amount, set_id = set.id, date_key = set.date_key)
statistics = {
'entities': entities.to_dict(),
'totalAmount': total_amount.first().total_amount
}
return statistics
def m_assets_level_details(level, set, page, page_size):
parameters = {
'date_key': set.date_key,
'offset': (page - 1) * page_size,
'limit': page_size,
'set_id': set.id,
'level': level
}
counts = raw_db.query(asset_sqls.m_assets_level_detail_counts, parameters).first().counts
details = raw_db.query(asset_sqls.m_assets_level_detail, parameters)
level_name = Structure.query.filter_by(set_id = set.id).filter_by(level = level).first().level_name
return details, level_name, counts
def m_all_funds(entity_id, lowest_level, date_key, page, page_size):
parameters = {
'id': entity_id,
'lowest_level': lowest_level,
'date_key': date_key,
'offset': (page - 1) * page_size,
'limit': page_size
}
counts = raw_db.query(asset_sqls.m_holding_funds_counts, parameters).first().counts
funds = raw_db.query(asset_sqls.m_holding_funds, parameters)
return funds, counts, 'Manager'
def get_user_active():
parameters = {"status": 1, 'group_id': g.user.group_id}
activated = raw_db.query(user_sqls.get_active_from_user,
parameters).first()
inactivated = raw_db.query(user_sqls.get_active_from_user,
parameters).first()
results = {
"activated_count": activated.count,
"all_count": activated.count + inactivated.count
}
return results
def get_users_except_current_user():
users = raw_db.query(user_sqls.get_users_except_current_user,
group_id=g.user.group_id,
user_id=g.user.id)
return users
def workbench_collections(page, page_size):
parameters = {
'user_id': g.user.id,
'offset': (page - 1) * page_size,
'limit': page_size
}
collection_items = raw_db.query(report_sqls.get_collections_for_workbench,
parameters).all()
res = defaultdict(list)
id_map = defaultdict()
ret = []
for item in collection_items:
if item.charting_data:
res[item.collection_title].append({
'chartingData':
item.charting_data,
'name':
item.charting_data.get('title'),
'itemId':
item.collection_item_id
})
else:
res[item.collection_title] = []
id_map[item.collection_title] = item.collection_id
for key, value in res.items():
ret.append({'charts': value, 'name': key, 'id': id_map.get(key)})
return ret
def m_get_first_upcoming_meeting_by_entity_id(entity_id, date):
parameters = {
'user_id': g.user.id,
'date_key': date,
'entity_id': entity_id
}
meeting = raw_db.query(meeting_sqls.m_get_first_upcoming_meeting_after_date_by_entity, parameters)\
.first()
if meeting:
meeting = meeting.to_dict({'startTime': datetime_to_timestamp})
attendance = raw_db.query(meeting_sqls.m_get_attendance_of_meeting,
meeting_id=meeting.get('id'))
meeting['attendance'] = attendance.to_dict()
return meeting
def create_comment(task_id):
comment = Comment.from_dict(g.args)
comment.task_id = task_id
comment.from_uid = g.user.id
comment.created_time = datetime.datetime.utcnow()
db.session.add(comment)
task = Task.query.get(task_id)
notify_users = raw_db.query(task_sqls.get_notifier,
task_id=task_id,
user_id=g.user.id)
for notify_user in notify_users:
message = Message.from_dict({
'content':
'{} commented on the task - {}'.format(g.user.name, task.title),
'to_uid':
notify_user.id,
'type':
3,
'message_entity_id':
task.id
})
db.session.add(message)
# update the status for all the other user
raw_db.query(task_sqls.update_new_reply_status_when_comment,
task_id=task_id,
user_id=g.user.id)
db.session.flush()
# add attachments
attachments = g.args.get('attachments')
if attachments:
for attachment in g.args.get('attachments'):
atta = CommentAttachment.from_dict({
'comment_id': comment.id,
'attachment_id': attachment
})
db.session.add(atta)
db.session.commit()
return comment
def get_bookmarks_analytics(page, page_size):
parameters = {
'offset': (page - 1) * page_size,
'limit': page_size
}
return raw_db.query(bookmark_sqls.get_bookmarks_analytics, parameters).to_dict()
def get_assignees_of_task(task_id):
task = Task.query.get(task_id)
assignees = raw_db.query(task_sqls.get_assignees,
task_id=task_id,
from_uid=task.from_uid)
return assignees
def get_bookmarks_reports(page, page_size):
parameters = {
'offset': (page - 1) * page_size,
'limit': page_size
}
return raw_db.query(bookmark_sqls.get_bookmarks_reports, parameters).to_dict({'updatedTime': datetime_to_timestamp})
def get_all_user(page, page_size):
parameters = {
'offset': (page - 1) * page_size,
'limit': page_size,
'group_id': g.user.group_id
}
users = raw_db.query(user_sqls.get_all_user, parameters).to_dict()
total_users_count = raw_db.query(user_sqls.get_all_count,
parameters).first()
result = {}
result['users'] = users
result['totalUsersCount'] = total_users_count.total_users_count
return result
def get_entity_ids_by_level(level):
set_id = asset_services.get_default_set()
strutures = raw_db.query(asset_sqls.select_structure_by_level, {"level": level, "set_id": set_id.id}).all()
entity_ids = []
for struture in strutures:
entity_ids.append(struture.entity_id)
return entity_ids
def email_job():
print("email_job is working")
items = connection.get_all_unprocessed_meeting_invites()
# print(items)
for item in items:
meeting_invite = EmailMeeting(item)
# print(meeting_invite)
organizer = meeting_invite.get_meeting_organizer().email_address
subject = meeting_invite.get_mail_subject()
location = meeting_invite.get_meeting_location()
attendees = meeting_invite.get_meeting_attendees()
start_time = meeting_invite.get_meeting_start_time()
end_time = meeting_invite.get_meeting_end_time()
start_time = time.mktime(start_time.timetuple())
end_time = time.mktime(end_time.timetuple())
start_time = time.ctime(start_time)
end_time = time.ctime(end_time)
attendees_list = []
for attendee in attendees:
attendees_list.append(attendee.mailbox.email_address)
# sql = 'SELECT * FROM public.user'
# users = scheduler_db.query(sql)
# for user in users:
# print(user.name)
with db.app.app_context():
user_list = User.query.filter(
User.email.in_(attendees_list)).filter_by(role_id=1).all()
users = [user.id for user in user_list]
organizer_id = user_services.get_user_id_by_email(organizer)
sql = """SELECT *
FROM public.manager_fund mf
LEFT JOIN public.user u
ON mf.manager_id = u.id
where u.email in :lists"""
# results = db.execute(text(sql), lists = tuple(attendees_list)).fetchall()
results = raw_db.query(sql, lists=tuple(attendees_list)).to_dict()
# print(results)
meeting_services.save_email_meeting(subject, organizer_id,
location, start_time, end_time,
results[0].get('fundId'),
users)
def get_user_id_by_email(email):
user_id = raw_db.query(user_sqls.get_user_id_by_email, email=email).first()
if user_id:
user_id = user_id.to_dict()
else:
print("user_id is null")
return None
return user_id.get('id')
def get_collection_items_by_collection_id(collection_id, page, page_size):
parameters = {
'offset': (page - 1) * page_size,
'limit': page_size,
'collection_id': collection_id
}
return raw_db.query(collection_sqls.get_collection_item_by_collection_id,
parameters).to_dict()
def check_drafts_detail(report_id, previous_report_id):
ret_dic = {}
client_id = None
showtime = None
red_flag = False
if int(previous_report_id) != 0:
report, last_report = report_services.get_drafts_columns(
report_id, previous_report_id)
for i in report:
client_id = i.client_id
for i in report:
showtime = i.showtime
client_master = raw_db.query(client_master_sql,
client_id=client_id).first()
if client_master.data_showtime > showtime:
last_last_report = report_services.get_source_data(client_id)
red_flag = change_color(report_id, client_id, red_flag)
if red_flag:
ret_dic['tab_latest_latest'] = last_last_report.to_dict()
ret_dic['tab_time'] = last_report.to_dict()
ret_dic['tab_latest'] = report.to_dict()
else:
report, _ = report_services.get_drafts_columns(report_id,
previous_report_id)
for i in report:
client_id = i.client_id
showtime = i.showtime
client_master = raw_db.query(client_master_sql,
client_id=client_id).first()
if client_master.data_showtime > showtime:
last_last_report = report_services.get_source_data(client_id)
# ret_dic['tab_latest'] = last_last_report.to_dict()
red_flag = change_color(report_id, client_id, red_flag)
if red_flag:
ret_dic['tab_latest'] = last_last_report.to_dict()
ret_dic['tab_time'] = report.to_dict()
return ret_dic, red_flag
def query_chart_insight(chart_id, user_id):
result = raw_db.query(chart_sqls.query_chart_insight, {
"chart_id": chart_id,
"user_id": user_id
})
result.all()
# print(result)
return result
def query_chart_comment(chart_id, page_size, page):
# print(chart_id,'-',page_size, '-',page)
parameters = {
'offset': (page - 1) * page_size,
'limit': page_size,
'chart_id': chart_id
}
result = raw_db.query(chart_sqls.query_chart_comment, parameters)
total_count = raw_db.query(chart_sqls.query_chart_comment_count,
parameters).first()
result.all()
data = {"comment": result.to_dict(), "total_count": total_count.count}
return data
def m_get_meetings_by_page_and_attendance_after_date(date, page, page_size):
parameters = {
'user_id': g.user.id,
'date_key': date,
'offset': (page - 1) * page_size,
'limit': page_size
}
meetings = raw_db.query(meeting_sqls.m_get_upcoming_meeting_after_date,
parameters).to_dict(
{'startTime': datetime_to_timestamp})
for meeting in meetings:
attendance = raw_db.query(meeting_sqls.m_get_attendance_of_meeting,
meeting_id=meeting.get('id'))
meeting['attendance'] = attendance.to_dict()
return meetings
def get_alert_threshold_by_page(page, page_size):
parameters = {
'offset': (page - 1) * page_size,
'limit': page_size,
'user_id': g.user.id
}
alert_thresholds = raw_db.query(alert_sqls.get_alert_threshold_by_page, parameters).to_dict(camelcase = False)
return alert_thresholds
def get_related_alerts(alert):
parameters = {
'user_id': g.user.id,
'group_id': g.user.group_id,
'alert_type_id':alert.alert_type_id,
'date_key': alert.date_key
}
related_alerts = raw_db.query(alert_sqls.get_related_alerts, parameters)
return related_alerts
def get_meetings_by_page(page, page_size):
parameters = {
'user_id': g.user.id,
'offset': (page - 1) * page_size,
'limit': page_size
}
meetings = raw_db.query(meeting_sqls.get_upcoming_meeting, parameters)
return meetings
def m_top3_holding_funds(entity_id, lowest_level, date_key):
parameters = {
'id': entity_id,
'lowest_level': lowest_level,
'date_key': date_key
}
top3_holding_funds = raw_db.query(asset_sqls.m_top3_holding_funds, parameters)
return top3_holding_funds
def m_get_meetings_by_page_and_attendance(page, page_size):
meetings = get_meetings_by_page(page, page_size).to_dict(
{'startTime': datetime_to_timestamp})
for meeting in meetings:
attendance = raw_db.query(meeting_sqls.m_get_attendance_of_meeting,
meeting_id=meeting.get('id'))
meeting['attendance'] = attendance.to_dict()
return meetings
def get_access_entity_ids():
set_id = asset_services.get_default_set()
user_entity_ids = raw_db.query(user_extend_sqls.get_user_entity, {
"user_id": g.user.id,
"set_id": set_id.id
}).to_dict()
child_tree = set([])
for user_entity_id in user_entity_ids:
child_tree.add(user_entity_id.get('entityId'))
structures = raw_db.query(
user_extend_sqls.select_structure_by_parent_id, {
"set_id": set_id.id,
"entity_id": user_entity_id.get('entityId')
}).all()
# print("*** ",structures ," ***")
if structures:
for structure in structures:
get_structure(structure.id, set_id.id, child_tree)
# print("*** ", child_tree, " ***")
return child_tree
def get_structure(structure_id, set_id, entity_ids):
structures = raw_db.query(user_extend_sqls.select_by_parent_id_and_set_id,
{
"set_id": set_id,
"parent_id": structure_id
}).all()
if structures:
for structure in structures:
# print("********* " ,structure.id, " *********")
entity_ids.add(structure.entity_id)
get_structure(structure.id, set_id, entity_ids)
def get_pre_assignee_of_task():
selected_ids = g.args.get('selected_ids')
selected_ids.append(-1)
parameters = {
'query_string': '%{}%'.format(g.args.get('query_string').lower()),
'selected_ids': tuple(selected_ids)
}
assignees = raw_db.query(task_sqls.fuzzy_matching_pre_assignee,
parameters).to_dict()
return assignees
def get_reports_of_template(template_id, page, page_size):
parameters = {
'template_id': template_id,
'offset': (page - 1) * page_size,
'limit': page_size
}
reports = raw_db.query(report_sqls.get_reports_of_template,
parameters).to_dict(
{'updatedTime': datetime_to_timestamp})
return reports
