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