1#!/usr/bin/python3
2#
3# Copyright (C) 2023 The Android Open Source Project
4#
5# Licensed under the Apache License, Version 2.0 (the "License"); you may not
6# use this file except in compliance with the License. You may obtain a copy of
7# the License at
8#
9#      http://www.apache.org/licenses/LICENSE-2.0
10#
11# Unless required by applicable law or agreed to in writing, software
12# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
13# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
14# License for the specific language governing permissions and limitations under
15# the License.
16#
17"""Convert the compare result csv file to a spreadsheet.
18Prerequisite:
19  - Install the `gspread` python package.
20  - Create credentials to allow access to spreadsheets via Google Sheets API.
21
22Usage example:
23python3 generate_spread_sheet.py \
24  --compared_result compare_result/diff.csv \
25  --sheet_name "CTS Compare Result" \
26  --credentials_dir ~/.config/gspread/
27"""
28
29import argparse
30import constant
31import csv
32import gspread
33import os
34
35from typing import List, Tuple
36
37
38_COLOR_GREY = {'red': 0.37, 'green': 0.42, 'blue': 0.42}
39_COLOR_WHITE = {'red': 0.95, 'green': 0.95, 'blue': 0.95}
40_COLOR_YELLOW = {'red': 0.9, 'green': 0.8, 'blue': 0.07}
41_COLOR_DARK_BLUE = {'red': 0.15, 'green': 0.15, 'blue': 0.46}
42
43
44_SHEET_HEADER_FORMAT = {
45    'backgroundColor': _COLOR_GREY,
46    'horizontalAlignment': 'CENTER',
47    'textFormat': {
48        'foregroundColor': _COLOR_WHITE,
49        'fontSize': 11,
50        'bold': True,
51    },
52}
53
54
55_MODULE_HEADER_FORMAT = {
56    'backgroundColor': _COLOR_YELLOW,
57    'horizontalAlignment': 'LEFT',
58    'textFormat': {
59        'foregroundColor': _COLOR_DARK_BLUE,
60        'fontSize': 10,
61        'bold': True,
62    },
63}
64
65
66# The first four columns in compare_results are for test info.
67_NUM_OF_INFO_COLUMNS = 4
68
69
70def _parse_args() -> argparse.Namespace:
71  """Parse the script arguments.
72
73  Returns:
74    An object of argparse.Namespace.
75  """
76  parser = argparse.ArgumentParser()
77  parser.add_argument('--compared_result', required=True,
78                      help='Path to the compared csv file.')
79  parser.add_argument('--sheet_name', required=True,
80                      help='Name for the output spreadsheet.')
81  parser.add_argument('--credentials_dir', required=True,
82                      help='Path to the directory that contains gspread '
83                           'credentials files.')
84  return parser.parse_args()
85
86
87def _read_csv(csv_path: str) -> Tuple[List[str], List[List[str]]]:
88  """Read a csv comparison report and return as lists.
89
90  Args:
91    csv_path: The path to the csv comparison report.
92
93  Returns:
94    A List of report names, A List of parsed results.
95  """
96  parsed_result = []
97  with open(csv_path, 'r') as csvfile:
98    result_reader = csv.reader(csvfile, delimiter=',')
99    header = next(result_reader)
100    report_names = header[_NUM_OF_INFO_COLUMNS:]
101    for row in result_reader:
102      parsed_result.append(row)
103  return report_names, parsed_result
104
105
106def _create_spread_sheet(
107    new_sheet_name: str, credentials_dir: str
108) -> gspread.Spreadsheet:
109  """Create a spread sheet at the user's Drive directory.
110
111  Args:
112    new_sheet_name: The name of this spread sheet.
113    credentials_dir: The path to the directory that contains gspread
114                     credentials files.
115
116  Returns:
117    An object of gspread.Spreadsheet.
118  """
119  credentials = os.path.join(credentials_dir, 'credentials.json')
120  authorized_user = os.path.join(credentials_dir, 'authorized_user.json')
121  gc = gspread.oauth(credentials_filename=credentials,
122                     authorized_user_filename=authorized_user)
123  sh = gc.create(new_sheet_name)
124  return sh
125
126
127def _get_range_cell(
128    begin_row: int, begin_column: str, num_rows: int, num_columns: int
129) -> str:
130  """Get the sheet cell range in the string format.
131
132  Args:
133    begin_row: The begin row, in integer format.
134    begin_column: The begin column, in string format.
135    num_rows: Number of rows.
136    num_columns: Number of columns.
137
138  Return:
139    The range cell in the string format.
140  """
141  end_row = begin_row + num_rows - 1
142  end_column = chr(ord(begin_column) + num_columns - 1)
143  return f'{begin_column}{begin_row}:{end_column}{end_row}'
144
145
146def _write_compare_info(
147    sheet: gspread.Worksheet, report_names: List[str]
148) -> None:
149  """Write the compare information to a worksheet.
150
151  Args:
152    sheet: The object to worksheet for writing.
153    report_names: A list of cts report names.
154  """
155  sheet.update_title('Test Info')
156  build_info = []
157  for i, name in enumerate(report_names):
158    build_info.append([f'Build {i}', name])
159  sheet.update(build_info)
160
161
162def _write_compare_details(
163    sheet: gspread.Worksheet, compare_results: List[List[str]], start_row: int
164) -> None:
165  """Write the detailed comparison result to a worksheet.
166
167  Args:
168    sheet: The object to worksheet for writing.
169    compare_results: A list of comparison results.
170    start_row: The starting row for writing comparison results.
171  """
172  curr_module = 'None'
173  curr_row = start_row
174  module_header_row = start_row
175  rows_content = []
176  module_header_formats = []
177
178  num_reports = len(compare_results[0]) - _NUM_OF_INFO_COLUMNS
179  module_failures = [0] * num_reports
180  for row_index, row_values in enumerate(compare_results):
181    module_name, abi, test_class, test_item, *test_statuses = row_values
182    module_end = ((row_index == len(compare_results) - 1) or
183                  (module_name != compare_results[row_index + 1][0]))
184
185    # Module changes, need a new header row.
186    if module_name != curr_module:
187      module_with_abi = (module_name if abi == constant.ABI_IGNORED
188                         else f'{module_name} [{abi}]')
189      rows_content.append([module_with_abi, ''] + [''] * num_reports)
190      module_header_row = len(rows_content)
191      header_cell = _get_range_cell(
192          begin_row=curr_row, begin_column='A',
193          num_rows=1, num_columns=len(rows_content[0]))
194      module_header_formats.append({
195          'range': header_cell,
196          'format': _MODULE_HEADER_FORMAT,
197      })
198      curr_row += 1
199
200    curr_module = module_name
201    for i, status in enumerate(test_statuses):
202      test_statuses[i] = '-' if status == 'pass' else status.upper()
203      if test_statuses[i] not in ['-', 'ASSUMPTION_FAILURE', 'NULL']:
204        module_failures[i] += 1
205    rows_content.append([test_class, test_item] + test_statuses)
206    curr_row += 1
207
208    # Module ends, update number of failed items in the header.
209    if module_end:
210      for index, count in enumerate(module_failures):
211        # The first two columns are for module info.
212        rows_content[module_header_row - 1][index + 2] = f'Failed: {count}'
213      module_failures = [0] * num_reports
214
215  if rows_content and module_header_formats:
216    content_cell = _get_range_cell(
217        begin_row=start_row, begin_column='A',
218        num_rows=len(rows_content), num_columns=len(rows_content[0]))
219    sheet.update(content_cell, rows_content)
220    sheet.batch_format(module_header_formats)
221
222
223def main():
224  args = _parse_args()
225
226  # Get the comparison result
227  report_names, compare_results = _read_csv(args.compared_result)
228
229  # Create a google spread sheet
230  sheets = _create_spread_sheet(args.sheet_name, args.credentials_dir)
231
232  # Write test info to the fist worksheet
233  _write_compare_info(sheets.sheet1, report_names)
234
235  # Write comapre details to the second worksheet
236  # Limit the rows to len(compare_results) * 2 because we need module headers
237  detail_sheet = sheets.add_worksheet(
238      title='Detailed Comparison',
239      rows=len(compare_results) * 2,
240      cols=len(compare_results[0]))
241
242  # Format the first row
243  row_header = ['Test Class', 'Test Item'] + report_names
244  cell = _get_range_cell(
245      begin_row=1, begin_column='A', num_rows=1, num_columns=len(row_header))
246  detail_sheet.update(cell, [row_header])
247  detail_sheet.format(cell, _SHEET_HEADER_FORMAT)
248
249  # write details to the worksheet, starting at the second row
250  _write_compare_details(detail_sheet, compare_results, 2)
251
252
253if __name__ == '__main__':
254  main()
255