def import_articles(book_file_name, year): data_records = maxexcel.get_data_from_excel(book_file_name=book_file_name, sheet_name='Metrics - Articles', initial_row=8, number_columns=19) maxsqlite3.update_data(database=resolve_path('Metrics.db3'), query="DELETE FROM PUBLICATION WHERE strftime('%Y' , date) = ? AND TYPE = 'article' ", params=(year,)) maxsqlite3.executemany_in_database(database=resolve_path('Metrics.db3'), insert_query='INSERT INTO PUBLICATION VALUES("article",?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0,0,0)', matrix_data=data_records)
def import_videos(book_file_name, year): data_records = maxexcel.get_data_from_excel(book_file_name=book_file_name, sheet_name='Metrics - Videos', initial_row=8, number_columns=17) maxsqlite3.update_data(database=resolve_path('Metrics.db3'), query="DELETE FROM PUBLICATION WHERE strftime('%Y' , date) = ? AND TYPE = 'video' ", params=(year,)) maxsqlite3.executemany_in_database(database=resolve_path('Metrics.db3'), insert_query='''INSERT INTO PUBLICATION (type, title, owner, date, gca, board_area, customer, region, Influencers, corporate_portal_views, corporate_portal_comments, news_center_views, news_center_comments, sap_tv_views, youtube_views, youtube_comments, twitter_impressions, twitter_engagement) VALUES("video",?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', matrix_data=data_records)
def get_data_report_per_year(year): query = """ select strftime('%d-%m-%Y',date), title from InfographicPublications where year = '{}' """.format(year) return get_data(resolve_path('Metrics.db3'), query, ())
def get_data_report_number_views_per_month(year, month, type): headers = type_headers.get(type, 'Nothing') return get_data(resolve_path('Metrics.db3') ,""" select {} from 'Metrics per type and year' where year = '{}' and month = '{}' and type in ({}) """.format(headers, year, month, type), ())
def get_data_report_regions(year, months, types, factor): return get_data(resolve_path('Metrics.db3') ,""" select {0}, sum(total) as total from {1} where year = '{2}' and month in ({3}) and type in ({4}) group by {0} order by total DESC """.format(column_from_factor(factor), table_from_factor(factor), year, months, types), ())
def get_data_report_publications_per(year): return get_data(resolve_path('Metrics.db3') ,"""select month, corporate_portal_views, corporate_portal_comments, forbes_views, forbes_comments, news_center_views, news_center_comments, business_trends_views, business_trends_comments, twitter_impressions, twitter_engagement from 'metrics per type and year' where type = 'article' and year = '{}'""".format(year), ())
def get_data_report_publications_per(year): return get_data(resolve_path('Metrics.db3') ,"""select month, corporate_portal_views, corporate_portal_comments, news_center_views, news_center_comments, sap_tv_views, youtube_views, youtube_comments, twitter_impressions, twitter_engagement from 'metrics per type and year' where type = 'video' and year = '{}'""".format(year), ())
def get_data_report_topowner_per_quarter(year, quarter, type): return get_data(resolve_path('Metrics.db3') ,""" select owner, sum(number) as number from topowners where year = '{}' and month in({}) and type in({}) group by owner order by number DESC limit 10 """.format(year, quarter, type), ())