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)
Beispiel #3
0
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, ())
Beispiel #4
0
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), ())
Beispiel #5
0
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), ())
Beispiel #6
0
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), ())
Beispiel #7
0
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), ())
Beispiel #8
0
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), ())