source: main/trunk/model-sites-dev/eurovision-lod/collect/eurovision/prepare/voting-excel/xlsx-to-jsonmetadata.py@ 34863

#!/usr/bin/env python

from __future__ import print_function

import os
import re
import sys
import json

import argparse
import openpyxl

import xlsxutil

def eprint(*args, **kwargs):
    print(*args, file=sys.stderr, **kwargs)
    
def fileset_voting_for_esc_country_in_year(data_hashmap):

    # Only generating a single fileset record here, however
    # the Greenstone format allows for this to be an array
    # of fileset entries => return [ fileset ]
    
    fileset = {}
    
    return [ fileset ]


def filter_finalist_votes(entry):
    return entry.get('(semi-) final') == "f" and entry.get('Duplicate') == None

def sortkey_year_to_country(entry):
    return str(entry.get('Year'))+entry.get('To country')


def create_to_country_voting_groups(data_hashmap_array):

    # Example values for header-names
    #   (semi-) final: f, sf
    #   Jury or Televoting: J, T
    #   Year: 1975
    #   To country: Belgium
    #   From country: Belgium
    #   Edition: 1975f, 1975sf

    
    # Filter down to just the voting results concerning finals
    data_hashmap_array_finals = list(filter(filter_finalist_votes, data_hashmap_array))

    # Sort so array entries are grouped by the country receiving the votes in a given year
    data_hashmap_array_finals.sort(key=sortkey_year_to_country)


    # Debug output
    #
    # for data_hashmap in data_hashmap_array_finals:
    #    print data_hashmap.get('To country'), data_hashmap.get('Year'), data_hashmap.get('Points'), "(Points from " + data_hashmap.get('From country') + ")"


    # Build array of country groups
    #   A country grouping includes all the votes that country receive that year
    country_groups = []

    prev_data_hashmap = data_hashmap_array_finals[0]
    country_group = [ ]

    i = 1
    num_finals = len(data_hashmap_array_finals)

    while (i < num_finals):        
        country_group.append(prev_data_hashmap)
        data_hashmap = data_hashmap_array_finals[i]

        if (data_hashmap.get('To country') != prev_data_hashmap.get('To country')):
            # moving on to a new country group
            country_groups.append(country_group)
            country_group = [ ]
            
        prev_data_hashmap = data_hashmap
        i = i + 1
        
    country_group.append(prev_data_hashmap)        
    country_groups.append(country_group)

    return country_groups


def fileset_voting_for_esc_country_in_year(to_country_year_votes):

    # Looking to build data-structure (for output as JSON) in the form
    # { "FileSet":
    #   [
    #     { "FileName": "France1991\.nul" },
    #     { "Description":
    #       {
    #         "Metadata":
    #           [
    #             { "name": "Germany-J", "content": 12 }, # J = Jury Vote
    #             { "name": "Germany-T", "content": 6 },  # T = Televote (if present)
    #             ...
    #           ]
    #       }
    #     }
    #   ]
    # }

    fileset_array = []
    
    metadata_array = []

    jury_metadata_vals = []
    tele_metadata_vals = []
    
    for to_country_year_vote in to_country_year_votes:
        to_country   = to_country_year_vote.get('To country')
        year         = to_country_year_vote.get('Year')
        from_country = to_country_year_vote.get('From country')
        vote_type    = to_country_year_vote.get('Jury or Televoting')
        points       = to_country_year_vote.get('Points')

        voting_rec = { "name": from_country+"-"+vote_type, "content": points }

        metadata_array.append(voting_rec)

        if (vote_type == "J"):
            jury_metadata_vals.append(from_country+"-J")
        elif (vote_type == "T"):
            tele_metadata_vals.append(from_country+"-T")
        else:            
            eprint("Warning: Unrecognized voting type: " + vote_type)

    if (len(jury_metadata_vals)>0):
        metadata_array.append({ "name": "JuryVotesJSON", "content": json.dumps(jury_metadata_vals) })
    if (len(tele_metadata_vals)>0):
        metadata_array.append({ "name": "TeleVotesJSON", "content": json.dumps(tele_metadata_vals) })
                          
    id_to_country = to_country_year_votes[0].get('To country')
    id_to_country = re.sub(r'\s+', '', id_to_country)    
    id_year = to_country_year_votes[0].get('Year');
    id = id_to_country + str(id_year);
    filename_id = id + "\\.nul"

    fileset = {
        "FileSet" : [            
            { "FileName": filename_id, },
            { "Description" : { "Metadata" : metadata_array } }
        ]
    }
              
    return fileset

    
if __name__ == "__main__":

    parser = argparse.ArgumentParser()
    parser.add_argument('input-file.xlsx')
    parser.add_argument('output-file.json', nargs='?')
    parser.add_argument('--sheetname')
    
    args = parser.parse_args()

    excel_input_filename = getattr(args,'input-file.xlsx');
    json_output_filename = getattr(args,'output-file.json');
    sheetname = getattr(args,'sheetname');
    
    if (json_output_filename == None):
        json_output_filename = os.path.splitext(excel_input_filename)[0]+'.json'
        
    worksheet = xlsxutil.load_xslx_sheet(excel_input_filename,sheetname)

    data_hashmap_array = xlsxutil.convert_worksheet_to_hashmaps(worksheet)

    print("Number of data rows: " + str(len(data_hashmap_array)))

    
    to_country_year_voting_groups = create_to_country_voting_groups(data_hashmap_array)

    # Debug output
    #
    # print to_country_year_voting_groups
    
    # Next step is to express the grouped by-country voting data
    # in the Greenstone JSON metadata format:
    
    # { "DirectoryMetadata":
    #   [
    #     { "FileSet":
    #       [
    #         { "FileName": "France1991\.nul" },
    #         { "Description":
    #           {
    #             "Metadata":
    #              [
    #                { "name": "Germany-J", "content": "12" }, # J = Jury Vote
    #                  ...
    #              ]
    #           }
    #         }
    #       ]
    #     }
    #     ...
    #    ]
    #  }

    directory_metadata = []
        
    print("Creating Greenstone JSON voting metadata for:")
    for to_country_year_votes in to_country_year_voting_groups:
        
        fileset = fileset_voting_for_esc_country_in_year(to_country_year_votes)
        directory_metadata.append(fileset)

        filename_id = fileset.get('FileSet')[0].get('FileName')
        num_countries_voting_data = len(fileset.get('FileSet')[1].get('Description').get('Metadata'))
        
        print("  " + filename_id.ljust(28) + ": " + str(num_countries_voting_data) + " votes")

    greenstone_metadata_json = { "DirectoryMetadata": directory_metadata }


    with open(json_output_filename, 'w') as outfile:
        json.dump(greenstone_metadata_json, outfile, indent=2)