Update google sheets automatically
Change-Id: Ia781913547a152f50d08cdda80becdf26c9ed468
Related-PROD: PROD-30291
diff --git a/daily_jenkins_job_report/daily_report/config.py b/daily_jenkins_job_report/daily_report/config.py
index 6806af3..9a73a06 100644
--- a/daily_jenkins_job_report/daily_report/config.py
+++ b/daily_jenkins_job_report/daily_report/config.py
@@ -3,6 +3,11 @@
PASSWORD = 'ahvoNg4mae'
JENKINS_URL = 'https://ci.mcp.mirantis.net'
+# For updating Google sheets
+GOOGLE_AUTH = '/home/ubuntu/osccore-qa-testing-tools/daily_jenkins_job_report/daily_report/oscore-e717344565a0.json'
+GOOGLE_SHEET_NAME = 'May 2019'
+ALL_GS_JOBS = [{'gs_job_name': 'oscore-promote-openstack-pike-xenial_virtual-mcp11-aio-barbican-pike', 'row_number': 4, 'column_number': 'A'}, {'gs_job_name': 'oscore-promote-openstack-pike-xenial_virtual-mcp11-aio-ssl-pike', 'row_number': 5, 'column_number': 'A'}, {'gs_job_name': 'oscore-promote-openstack-pike-xenial_virtual-mcp11-aio-octavia-pike', 'row_number': 6, 'column_number': 'A'}, {'gs_job_name': 'oscore-promote-openstack-pike-xenial_virtual-mcp11-aio-manila-pike', 'row_number': 7, 'column_number': 'A'}, {'gs_job_name': 'oscore-promote-openstack-pike-xenial_virtual-mcp11-aio-telemetry-pike', 'row_number': 8, 'column_number': 'A'}, {'gs_job_name': 'oscore-promote-openstack-queens-xenial_virtual-mcp11-aio-barbican-queens', 'row_number': 9, 'column_number': 'A'}, {'gs_job_name': 'oscore-promote-openstack-queens-xenial_virtual-mcp11-aio-telemetry-queens', 'row_number': 10, 'column_number': 'A'}, {'gs_job_name': 'oscore-promote-openstack-queens-xenial_virtual-mcp11-aio-manila-queens', 'row_number': 11, 'column_number': 'A'}, {'gs_job_name': 'oscore-promote-openstack-queens-xenial_virtual-mcp11-aio-ssl-queens', 'row_number': 12, 'column_number': 'A'}, {'gs_job_name': 'oscore-promote-openstack-queens-xenial_virtual-mcp11-aio-octavia-queens', 'row_number': 13, 'column_number': 'A'}, {'gs_job_name': 'oscore-promote-openstack-ocata-xenial_virtual-mcp11-aio-stable/ocata', 'row_number': 14, 'column_number': 'A'}, {'gs_job_name': 'oscore-promote-openstack-ocata-xenial_openstack-ovs-core-designate-ocata', 'row_number': 15, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_virtual-mcp11-aio-newton', 'row_number': 16, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_virtual-mcp11-aio-mitaka', 'row_number': 17, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_virtual-mcp11-aio-ocata', 'row_number': 18, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_virtual-mcp11-aio-pike', 'row_number': 19, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_virtual-mcp11-aio-advanced-keystone-queens', 'row_number': 20, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_openstack-ovs-core-extra-ssl-pike', 'row_number': 21, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_openstack-ovs-core-telemetry-ceph-queens', 'row_number': 22, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_openstack-ovs-core-octavia-pike', 'row_number': 23, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_openstack-ovs-core-extra-ssl-queens', 'row_number': 24, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_openstack-ovs-core-octavia-queens', 'row_number': 25, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_virtual-mcp11-aio-barbican-pike', 'row_number': 26, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_openstack-ovs-core-barbican-ssl-queens', 'row_number': 27, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_virtual-mcp11-aio-ironic-pike', 'row_number': 28, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_openstack-ovs-core-ironic-queens', 'row_number': 29, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_virtual-mcp11-aio (stepler AIO)', 'row_number': 30, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_openstack-ovs-core-ceph-queens (stepler CC)', 'row_number': 31, 'column_number': 'A'}, {'gs_job_name': 'oscore-oscc-ci_openstack-ovs-core-ironic-ceph-queens', 'row_number': 32, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-release-2019.2.0_openstack-ovs-core-extra-ssl-queens', 'row_number': 33, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-release-2019.2.0_openstack-ovs-core-barbican-ssl-queens', 'row_number': 34, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-release-2019.2.0_openstack-ovs-core-telemetry-ceph-queens', 'row_number': 35, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-release-2019.2.0_openstack-ovs-core-extra-ssl-pike', 'row_number': 36, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-release-2018.4.0_openstack-ovs-core-mitaka', 'row_number': 37, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-release-2018.4.0_openstack-ovs-core-extra-ssl-pike', 'row_number': 38, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-release-2018.4.0_openstack-ovs-core-designate-ocata', 'row_number': 39, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-release-2018.8.0_openstack-ovs-core-pike', 'row_number': 40, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-release-2018.8.0_openstack-ovs-core-queens', 'row_number': 41, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-release-2018.11.0_openstack-ovs-core-ssl-queens', 'row_number': 42, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-release-2018.11.0_openstack-ovs-core-mitaka', 'row_number': 43, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-release-2018.11.0_openstack-ovs-core-ocata', 'row_number': 44, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-release-2018.11.0_openstack-ovs-core-ssl-pike', 'row_number': 45, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-openstack-upgrade-pike-queens-core-barbican', 'row_number': 46, 'column_number': 'A'}, {'gs_job_name': 'oscore-test-openstack-upgrade-pike-queens-core-ssl', 'row_number': 47, 'column_number': 'A'}]
+
# For get_jobs_results.py save_results_to_html method
GENERATED_REPORT = '/var/www/oscore_jobs.com/html/reports/'
REPORT_TEMPLATE = '/home/ubuntu/osccore-qa-testing-tools/daily_jenkins_job_report/daily_report/templates/report_template.html'
diff --git a/daily_jenkins_job_report/daily_report/generate_report.py b/daily_jenkins_job_report/daily_report/generate_report.py
index c26b1ed..3b6e93f 100644
--- a/daily_jenkins_job_report/daily_report/generate_report.py
+++ b/daily_jenkins_job_report/daily_report/generate_report.py
@@ -17,12 +17,13 @@
# See the License for the specific language governing permissions and
# limitations under the License.
-# from daily_report
import config
import datetime
import logging
import re
+import update_google_sheets
+
import jinja2
from jinja2 import Template
from jenkinsapi import custom_exceptions
@@ -203,4 +204,6 @@
if __name__ == '__main__':
all_jobs_results = get_all_jobs_results()
+ print(all_jobs_results)
save_results_to_html(all_jobs_results)
+ update_google_sheets.update_google_sheet(all_jobs_results)
diff --git a/daily_jenkins_job_report/daily_report/requirements.txt b/daily_jenkins_job_report/daily_report/requirements.txt
new file mode 100644
index 0000000..f1cbc3a
--- /dev/null
+++ b/daily_jenkins_job_report/daily_report/requirements.txt
@@ -0,0 +1,5 @@
+nsapi>=0.3.6
+jinja2>=2.10
+setuptools>=40.8.0
+gspread>=3.1.0
+oauth2client>=4.1.3
diff --git a/daily_jenkins_job_report/daily_report/update_google_sheets.py b/daily_jenkins_job_report/daily_report/update_google_sheets.py
new file mode 100644
index 0000000..5661efc
--- /dev/null
+++ b/daily_jenkins_job_report/daily_report/update_google_sheets.py
@@ -0,0 +1,158 @@
+import config
+import generate_report
+
+from datetime import datetime, timedelta
+
+import gspread
+import logging
+from oauth2client.service_account import ServiceAccountCredentials
+
+
+logging.basicConfig(
+ format='[%(asctime)s][%(name)s][%(levelname)s] %(message)s',
+ datefmt='%d-%m-%Y %H:%M:%S',
+ handlers=[logging.FileHandler('{}{}'.format(
+ config.LOG_FOLDER, config.LOG_FILENAME)), logging.StreamHandler()],
+ level=logging.INFO)
+logger = logging.getLogger('upd_gs')
+
+
+class UpdateGoogleSheets:
+ """
+ Working with Google sheets
+ """
+ def __init__(self):
+ scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
+ credentials = ServiceAccountCredentials.from_json_keyfile_name(config.GOOGLE_AUTH, scope)
+ gc = gspread.authorize(credentials)
+ self.wks = gc.open('OSCORE QA daily reports').worksheet(config.GOOGLE_SHEET_NAME)
+ logger.info('Opening OSCORE QA daily reports for May 2019')
+
+ def get_gs_jobs_list(self, gs_jobs):
+ """
+ Gets google sheet jobs list from the first column
+ returns list of jobs
+ """
+ logger.info('Getting google sheet jobs list from the first column')
+ gs_jobs_list = []
+ for i in gs_jobs:
+ gs_jobs_list.append(i['gs_job_name'])
+ return gs_jobs_list
+
+ def get_gobs_for_update(self, all_jobs_results, gs_jobs):
+ """
+ Gets google sheet jobs for updating.
+ returns list of jobs for update
+ """
+ logger.info('Getting google sheet jobs for updating')
+ jobs_for_update = []
+ gs_jobs_list = self.get_gs_jobs_list(gs_jobs)
+
+ for key, value in all_jobs_results['multi_results'].items():
+ for i in all_jobs_results['multi_results'][key]:
+
+ if i['job_name'] in gs_jobs_list:
+ jobs_for_update.append(i)
+ return jobs_for_update
+
+ def update_gs_jobs(self, gs_jobs, jobs_for_update, column_to_update):
+ """
+ Updates google sheet column with jobs
+ """
+ logger.info('Updating google sheet jobs')
+ for upd in jobs_for_update:
+ for gs in gs_jobs:
+ if gs['gs_job_name'] == upd['job_name'] and upd['build_status'] != 'None' and check_datetime_today(upd['timestamp']):
+
+ cell = column_to_update + str(gs['row_number'])
+ if upd['build_status'] == 'FAILURE':
+ upd['build_status'] = 'FAILED'
+ self.wks.update_acell(cell, upd['build_status'])
+ print( self.wks.acell(cell).value)
+ if not check_datetime_today(upd['timestamp']):
+ cell = column_to_update + str(gs['row_number'])
+ self.wks.update_acell(cell, 'NOT EXEQUTED')
+
+ def get_all_jobs(self, column_number='A'):
+ """
+ Gets all the google sheet jobs for updating from the first column
+ """
+ logger.info('Getting all the google sheet jobs for updating from the first column')
+ all_jobs = []
+ for i in range(4, 48):
+ all_jobs.append({'gs_job_name': self.wks.acell(column_number + str(i)).value,
+ 'row_number': i,
+ 'column_number': column_number})
+ return all_jobs
+
+ def get_today_date_column(self):
+ """
+ Gets today column
+ Compares value from 3-d row and list_of_columns in column with
+ today date.
+ If they are the same, returns column value from the list_of_columns
+ """
+ logger.info('Getting date from gs')
+ list_of_columns = ['D', 'F', 'H', 'J', 'L', 'N', 'P', 'R', 'T', 'V', 'X', 'Z',
+ 'AB', 'AD', 'AF', 'AH', 'AJ', 'AL', 'AN', 'AP', 'AR', 'AT', 'AV', 'AX', 'AZ']
+ for i in list_of_columns:
+ cell = i + '3'
+ now = datetime.now()
+ if self.wks.acell(cell).value == now.strftime("%Y-%m-%d"):
+ print(cell, i, self.wks.acell(cell).value)
+ return i
+
+
+def update_multy_job_names(results):
+ """
+ Fron Jenkins we get job names and job URLS.
+ Combinating this we get the job names.
+ Returns updated Jenkins results
+ """
+ logger.info('Updating multy jobs names')
+ for key, value in results['multi_results'].items():
+ for i in value:
+
+ if 'virtual-mcp11-aio' in i['job_name']:
+ version = i['baseurl'][72:]
+ version = version[:version.find("/")]
+ new_name = key + '_' + i['job_name'] + version
+ i['job_name'] = new_name
+ else:
+ new_name = key + '_' + i['job_name']
+ i['job_name'] = new_name
+
+ return results
+
+
+def check_datetime_today(timestamp):
+ """
+ If timestamp is appied for today - returns True
+ """
+ logger.info('Checking datetime today')
+ to_date_obj = datetime.fromtimestamp(timestamp)
+
+ yesterday = datetime.now() - timedelta(days=1)
+
+ # Returns True or False
+ return to_date_obj.date() == datetime.today().date() or \
+ to_date_obj.date() == datetime.today().date() - timedelta(days=1)
+
+
+
+def update_google_sheet(all_jobs_results):
+ """
+ The main function for updating google sheets.
+ All the functions for updating are here
+ """
+ all_gs_jobs = config.ALL_GS_JOBS
+ update_multy_job_names(all_jobs_results)
+ gs = UpdateGoogleSheets()
+
+ # all_gs_jobs = gs.get_gs_jobs_list(gs_jobs)
+ jobs_for_update = gs.get_gobs_for_update(all_jobs_results, all_gs_jobs)
+
+ column = gs.get_today_date_column()
+
+ gs.update_gs_jobs(all_gs_jobs, jobs_for_update, column)
+