lkft: update to cache result to database

but has a problem on the datetime setting like following:

python2.7/site-packages/django/db/models/fields/__init__.py:1451: RuntimeWarning: DateTimeField CiBuild.timestamp received a naive datetime (2020-02-19 18:26:07) while time zone support is active.
  RuntimeWarning)

Signed-off-by: Yongqin Liu <yongqin.liu@linaro.org>

7 files changed, 456 insertions, 101 deletions

diff --git a/lcr/qa_report.py b/lcr/qa_report.py
index 90f9c48..ebe3a41 100644
--- a/lcr/qa_report.py
+++ b/lcr/qa_report.py
@@ -72,7 +72,6 @@ class JenkinsApi(RESTFullApi):
         # https://ci.linaro.org/job/trigger-lkft-aosp-mainline/api/json
         return 'https://%s/job/%s/api/json/' % (self.domain, detail_url)
 
-
     def get_last_build(self, cijob_name=''):
         if not cijob_name:
             return None
@@ -83,6 +82,14 @@ class JenkinsApi(RESTFullApi):
         else:
             return None
 
+    def is_build_disabled(self, cibuild_name):
+        build_details = self.get_build_details_with_cibuild_name(cibuild_name)
+        return not build_details.get('buildable')
+
+    def get_build_details_with_cibuild_name(self, cibuild_name):
+        full_api_url = self.get_api_url_prefix(detail_url=cibuild_name)
+        return self.call_with_full_url(request_url=full_api_url)
+
     def get_build_details_with_job_url(self, job_url):
         full_api_url = self.get_api_url_prefix(detail_url=job_url)
         return self.call_with_full_url(request_url=full_api_url)
@@ -99,6 +106,33 @@ class JenkinsApi(RESTFullApi):
         else:
             return "https://%s/job/%s/%s/" % (self.domain, name, number)
 
+    def get_queued_items(self):
+        # https://ci.linaro.org/queue/api/json?pretty=true
+        queue_api = 'https://%s/queue/api/json/' % (self.domain)
+        result = self.call_with_full_url(request_url=queue_api)
+        if result:
+            queued_items = result.get('items')
+            items_tobe_returned = []
+            for item in queued_items:
+                cibuild_name = item.get('task').get('name')
+                if not cibuild_name.startswith('lkft-'):
+                    continue
+                params_list = item.get('params').strip().split('\n')
+                params_dict = {}
+                for key_val_str in params_list:
+                    (key, value) = key_val_str.split('=')
+                    if key is not None:
+                        params_dict[key] = value
+                if not params_dict.get('KERNEL_DESCRIBE'):
+                    continue
+                item['KERNEL_DESCRIBE'] = params_dict.get('KERNEL_DESCRIBE')
+                item['build_name'] = cibuild_name
+                items_tobe_returned.append(item)
+
+            return items_tobe_returned
+        else:
+            return []
+
 
 class LAVAApi(RESTFullApi):
     def get_api_url_prefix(self):
diff --git a/lkft/admin.py b/lkft/admin.py
index 4e267da..7a59751 100644
--- a/lkft/admin.py
+++ b/lkft/admin.py
@@ -2,7 +2,7 @@
 from __future__ import unicode_literals
 
 from django.contrib import admin
-from .models import KernelChange, CiBuild
+from .models import KernelChange, CiBuild, ReportBuild
 
 # Register your models here.
 class KernelChangeAdmin(admin.ModelAdmin):
@@ -12,6 +12,9 @@ class KernelChangeAdmin(admin.ModelAdmin):
 class CiBuildAdmin(admin.ModelAdmin):
     list_display = ['name', 'number']
 
+class ReportBuildAdmin(admin.ModelAdmin):
+    list_display = ['group', 'name', 'version', 'number_passed', 'number_failed', 'number_total', 'modules_done', 'modules_total']
 
 admin.site.register(KernelChange, KernelChangeAdmin)
 admin.site.register(CiBuild, CiBuildAdmin)
+admin.site.register(ReportBuild, ReportBuildAdmin)
diff --git a/lkft/lkft_config.py b/lkft/lkft_config.py
index 1ad4d4b..5ac3eb9 100644
--- a/lkft/lkft_config.py
+++ b/lkft/lkft_config.py
@@ -111,6 +111,16 @@ citrigger_lkft_rcs = {
 }
 
 
+def find_expect_cibuilds(trigger_name=None):
+    if not trigger_name:
+        return None
+    lkft_builds = citrigger_lkft.get(trigger_name)
+    lkft_rc_builds = citrigger_lkft_rcs.get(trigger_name)
+    if lkft_builds is not None:
+        return set(lkft_builds.values())
+    else:
+        return set(lkft_rc_builds.values())
+
 def get_ci_trigger_info(project=None):
     if not project.get('full_name'):
         return (None, None, None)
diff --git a/lkft/management/commands/lkftreport.py b/lkft/management/commands/lkftreport.py
index cb864c5..c013cdf 100644
--- a/lkft/management/commands/lkftreport.py
+++ b/lkft/management/commands/lkftreport.py
@@ -14,7 +14,10 @@ import urllib2
 import yaml
 
 from django.core.management.base import BaseCommand, CommandError
-from lkft.models import KernelChange, CiBuild
+
+from django.utils.timesince import timesince
+
+from lkft.models import KernelChange, CiBuild, ReportBuild
 
 from lcr import qa_report
 
@@ -23,6 +26,7 @@ from lcr.settings import QA_REPORT, QA_REPORT_DEFAULT
 from lkft.views import get_test_result_number_for_build, get_lkft_build_status
 from lkft.views import extract
 from lkft.views import get_lkft_bugs, get_hardware_from_pname, get_result_file_path, get_kver_with_pname_env
+from lkft.lkft_config import find_expect_cibuilds
 
 qa_report_def = QA_REPORT[QA_REPORT_DEFAULT]
 qa_report_api = qa_report.QAReportApi(qa_report_def.get('domain'), qa_report_def.get('token'))
@@ -44,7 +48,8 @@ class Command(BaseCommand):
         return None
 
 
-    def get_configs(self, build_name=None):
+    def get_configs(self, ci_build=None):
+        build_name = ci_build.get('name')
         configs = []
         ci_config_file_url = "https://git.linaro.org/ci/job/configs.git/plain/%s.yaml" % build_name
         content = self.get_url_content(url=ci_config_file_url)
@@ -52,7 +57,8 @@ class Command(BaseCommand):
             pat_configs = re.compile("\n\s+name:\s*ANDROID_BUILD_CONFIG\n\s+default:\s*'(?P<value>[a-zA-Z0-9\ -_.]+)'\s*\n")
             configs_str = pat_configs.findall(content)
             if len(configs_str) > 0:
-                configs = ' '.join(configs_str[0].split()).split()
+                for config in ' '.join(configs_str[0].split()).split():
+                    configs.append((config, ci_build))
 
         return configs
 
@@ -133,6 +139,7 @@ class Command(BaseCommand):
         total_reports = []
 
         lkft_projects = self.get_lkft_qa_report_projects()
+        queued_ci_items = jenkins_api.get_queued_items()
         kernel_changes = KernelChange.objects_needs_report.all()
         for kernel_change in kernel_changes:
             lkft_build_configs = []
@@ -140,131 +147,289 @@ class Command(BaseCommand):
             trigger_build = jenkins_api.get_build_details_with_full_url(build_url=trigger_url)
             trigger_build['start_timestamp'] = datetime.datetime.fromtimestamp(int(trigger_build['timestamp'])/1000)
             trigger_build['duration'] = datetime.timedelta(milliseconds=trigger_build['duration'])
+            trigger_build['name'] = kernel_change.trigger_name
+            trigger_build['kernel_change'] = kernel_change
+            kernel_change_finished_timestamp = trigger_build['start_timestamp']
 
             kernel_change_status = "TRIGGER_BUILD_COMPLETED"
+
+            dbci_builds = CiBuild.objects_kernel_change.get_builds_per_kernel_change(kernel_change=kernel_change).order_by('name', '-number')
+
             ## TODO: how to check if a ci build is still in queue?
             ##       check which ci build should be started from the information of the trigger build?
-
-            ci_builds = CiBuild.objects_kernel_change.get_builds_per_kernel_change(kernel_change=kernel_change).order_by('name', '-number')
+            expect_build_names = find_expect_cibuilds(trigger_name=kernel_change.trigger_name)
 
             jenkins_ci_builds = []
             ci_build_names = []
-            for ci_build in ci_builds:
-                if ci_build.name in ci_build_names:
+            has_build_inprogress = False
+            for dbci_build in dbci_builds:
+                if dbci_build.name in ci_build_names:
                     continue
                 else:
-                    ci_build_names.append(ci_build.name)
+                    ci_build_names.append(dbci_build.name)
 
-                build_url = jenkins_api.get_job_url(name=ci_build.name, number=ci_build.number)
+                build_url = jenkins_api.get_job_url(name=dbci_build.name, number=dbci_build.number)
                 build = jenkins_api.get_build_details_with_full_url(build_url=build_url)
                 build['start_timestamp'] = datetime.datetime.fromtimestamp(int(build['timestamp'])/1000)
-                build['db_ci_build'] = ci_build
+                build['dbci_build'] = dbci_build
 
                 if build.get('building'):
                     build_status = 'INPROGRESS'
+                    has_build_inprogress = True
                 else:
                     build_status = build.get('result') # null or SUCCESS, FAILURE, ABORTED
                     build['duration'] = datetime.timedelta(milliseconds=build['duration'])
 
                 build['status'] = build_status
+                build['name'] = dbci_build.name
                 jenkins_ci_builds.append(build)
-                configs = self.get_configs(build_name=ci_build.name)
+                configs = self.get_configs(ci_build=build)
                 lkft_build_configs.extend(configs)
 
+            not_started_ci_builds = expect_build_names - set(ci_build_names)
+
+            queued_ci_builds = []
+            diabled_ci_builds = []
+            not_reported_ci_builds = []
+            if len(not_started_ci_builds) > 0:
+                for cibuild_name in not_started_ci_builds:
+                    is_queued_build = False
+                    for queued_item in queued_ci_items:
+                        if cibuild_name == queued_item.get('build_name') and \
+                            kernel_change.describe == queued_item.get('KERNEL_DESCRIBE'):
+                                is_queued_build = True
+                                queued_ci_builds.append(queued_item)
+                    if is_queued_build:
+                        continue
+
+                    if jenkins_api.is_build_disabled(cibuild_name):
+                        diabled_ci_builds.append(cibuild_name)
+                    else:
+                        not_reported_ci_builds.append(cibuild_name)
+
+            if queued_ci_builds:
+                kernel_change_status = "CI_BUILDS_IN_QUEUE"
+            elif not_reported_ci_builds:
+                kernel_change_status = "CI_BUILDS_NOT_REPORTED"
+            elif has_build_inprogress:
+                kernel_change_status = "CI_BUILDS_IN_PROGRESS"
+            else:
+                kernel_change_status = "CI_BUILDS_COMPLETED"
+
             qa_report_builds = []
-            for lkft_build_config in lkft_build_configs:
-                (team, project) = self.get_qa_server_project(lkft_build_config_name=lkft_build_config)
-                target_lkft_project_full_name = "%s/%s" % (team, project)
-                found_build = False
+            has_jobs_not_submitted = False
+            has_jobs_in_progress = False
+            all_jobs_finished = False
+
+            qareport_project_not_found_configs = []
+            qareport_build_not_found_configs = []
+            for lkft_build_config, ci_build in lkft_build_configs:
+                if ci_build.get('status') != 'SUCCESS':
+                    # no need to check the build/job results as the ci build not finished successfully yet
+                    continue
+
+                (project_group, project_name) = self.get_qa_server_project(lkft_build_config_name=lkft_build_config)
+                target_lkft_project_full_name = "%s/%s" % (project_group, project_name)
+
+                target_qareport_project = None
                 for lkft_project in lkft_projects:
-                    if lkft_project.get('full_name') != target_lkft_project_full_name:
-                        continue
+                    if lkft_project.get('full_name') == target_lkft_project_full_name:
+                        target_qareport_project = lkft_project
+                        break
+
+                if target_qareport_project is None:
+                    qareport_project_not_found_configs.append(lkft_build_config)
+                    continue
 
-                    builds = qa_report_api.get_all_builds(lkft_project.get('id'))
-                    for build in builds:
-                        if build.get('version') != kernel_change.describe:
-                            continue
+                target_qareport_build = None
+                builds = qa_report_api.get_all_builds(target_qareport_project.get('id'))
+                for build in builds:
+                    if build.get('version') == kernel_change.describe:
+                        target_qareport_build = build
+                        break
 
-                        created_str = build.get('created_at')
-                        build['created_at'] = datetime.datetime.strptime(str(created_str), '%Y-%m-%dT%H:%M:%S.%fZ')
+                if target_qareport_build is None:
+                    qareport_build_not_found_configs.append(lkft_build_config)
+                    continue
 
-                        jobs = qa_report_api.get_jobs_for_build(build.get("id"))
-                        build_status = get_lkft_build_status(build, jobs)
-                        if build_status['has_unsubmitted']:
-                            build['build_status'] = "JOBSNOTSUBMITTED"
-                        elif build_status['is_inprogress']:
-                            build['build_status'] = "JOBSINPROGRESS"
-                        else:
-                            build['build_status'] = "JOBSCOMPLETED"
-                            build['last_fetched_timestamp'] = build_status['last_fetched_timestamp']
-
-                        build['numbers_of_result'] = get_test_result_number_for_build(build, jobs)
-                        build['qa_report_project'] = lkft_project
-                        final_jobs = []
-                        resubmitted_or_duplicated_jobs = []
-                        for job in jobs:
-                            is_resubmited_job = job.get('resubmitted')
-                            is_duplicated_job = job.get('duplicated')
-                            if is_resubmited_job is None and is_duplicated_job is None:
-                                final_jobs.append(job)
-                            else:
-                                resubmitted_or_duplicated_jobs.append(job)
-
-                        build['final_jobs'] = final_jobs
-                        build['resubmitted_or_duplicated_jobs'] = resubmitted_or_duplicated_jobs
-                        qa_report_builds.append(build)
-                        found_build = True
-
-                        project_name = lkft_project.get('name')
-                        bugs = get_lkft_bugs(summary_keyword=project_name, platform=get_hardware_from_pname(project_name))
-                        build['bugs'] = bugs
-
-                        failures = {}
-                        for job in final_jobs:
-                            if job.get('job_status') is None and \
-                                job.get('submitted') and \
-                                not job.get('fetched'):
-                                job['job_status'] = 'Submitted'
-                            if job.get('failure'):
-                                failure = job.get('failure')
-                                new_str = failure.replace('"', '\\"').replace('\'', '"')
-                                try:
-                                    failure_dict = json.loads(new_str)
-                                except ValueError:
-                                    failure_dict = {'error_msg': new_str}
-
-
-                            result_file_path = get_result_file_path(job=job)
-                            if not result_file_path or not os.path.exists(result_file_path):
-                                continue
-
-                            kernel_version = get_kver_with_pname_env(prj_name=project_name, env=job.get('environment'))
-
-                            platform = job.get('environment').split('_')[0]
-
-                            metadata = {
-                                'job_id': job.get('job_id'),
-                                'qa_job_id': qa_report_api.get_qa_job_id_with_url(job_url=job.get('url')),
-                                'result_url': job.get('attachment_url'),
-                                'lava_nick': job.get('lava_config').get('nick'),
-                                'kernel_version': kernel_version,
-                                'platform': platform,
-                                }
-                            extract(result_file_path, failed_testcases_all=failures, metadata=metadata)
-
-                        build['failures'] = failures
-                        classification = self.classify_bugs_and_failures(bugs=bugs, failures=failures)
-                        build['classification'] = classification
+                created_str = target_qareport_build.get('created_at')
+                target_qareport_build['created_at'] = datetime.datetime.strptime(str(created_str), '%Y-%m-%dT%H:%M:%S.%fZ')
+                target_qareport_build['project_name'] = project_name
+                target_qareport_build['project_group'] = project_group
+
+                jobs = qa_report_api.get_jobs_for_build(target_qareport_build.get("id"))
+                build_status = get_lkft_build_status(target_qareport_build, jobs)
+                if build_status['has_unsubmitted']:
+                    target_qareport_build['build_status'] = "JOBSNOTSUBMITTED"
+                    has_jobs_not_submitted = True
+                elif build_status['is_inprogress']:
+                    target_qareport_build['build_status'] = "JOBSINPROGRESS"
+                    has_jobs_in_progress = True
+                else:
+                    target_qareport_build['build_status'] = "JOBSCOMPLETED"
+                    target_qareport_build['last_fetched_timestamp'] = build_status['last_fetched_timestamp']
+                    if kernel_change_finished_timestamp is None or \
+                        kernel_change_finished_timestamp < build_status['last_fetched_timestamp']:
+                        kernel_change_finished_timestamp = build_status['last_fetched_timestamp']
+                    target_qareport_build['duration'] = build_status['last_fetched_timestamp'] - target_qareport_build['created_at']
+
+                target_qareport_build['numbers_of_result'] = get_test_result_number_for_build(target_qareport_build, jobs)
+                target_qareport_build['qa_report_project'] = target_qareport_project
+                final_jobs = []
+                resubmitted_or_duplicated_jobs = []
+                for job in jobs:
+                    is_resubmited_job = job.get('resubmitted')
+                    is_duplicated_job = job.get('duplicated')
+                    if is_resubmited_job is None and is_duplicated_job is None:
+                        final_jobs.append(job)
+                    else:
+                        resubmitted_or_duplicated_jobs.append(job)
+
+                target_qareport_build['final_jobs'] = final_jobs
+                target_qareport_build['resubmitted_or_duplicated_jobs'] = resubmitted_or_duplicated_jobs
+                qa_report_builds.append(target_qareport_build)
+
+                project_name = target_qareport_project.get('name')
+                bugs = get_lkft_bugs(summary_keyword=project_name, platform=get_hardware_from_pname(project_name))
+                build['bugs'] = bugs
+
+                failures = {}
+                for job in final_jobs:
+                    if job.get('job_status') is None and \
+                        job.get('submitted') and \
+                        not job.get('fetched'):
+                        job['job_status'] = 'Submitted'
+                    if job.get('failure'):
+                        failure = job.get('failure')
+                        new_str = failure.replace('"', '\\"').replace('\'', '"')
+                        try:
+                            failure_dict = json.loads(new_str)
+                        except ValueError:
+                            failure_dict = {'error_msg': new_str}
+
+
+                    result_file_path = get_result_file_path(job=job)
+                    if not result_file_path or not os.path.exists(result_file_path):
+                        continue
+
+                    kernel_version = get_kver_with_pname_env(prj_name=project_name, env=job.get('environment'))
+
+                    platform = job.get('environment').split('_')[0]
+
+                    metadata = {
+                        'job_id': job.get('job_id'),
+                        'qa_job_id': qa_report_api.get_qa_job_id_with_url(job_url=job.get('url')),
+                        'result_url': job.get('attachment_url'),
+                        'lava_nick': job.get('lava_config').get('nick'),
+                        'kernel_version': kernel_version,
+                        'platform': platform,
+                        }
+                    extract(result_file_path, failed_testcases_all=failures, metadata=metadata)
+
+                target_qareport_build['failures'] = failures
+                classification = self.classify_bugs_and_failures(bugs=bugs, failures=failures)
+                target_qareport_build['classification'] = classification
+                target_qareport_build['ci_build'] = ci_build
+
+            has_error = False
+            error_dict = {}
+            if kernel_change_status == "CI_BUILDS_COMPLETED":
+                if qareport_project_not_found_configs or qareport_build_not_found_configs:
+                    has_error = True
+                    if qareport_project_not_found_configs:
+                        kernel_change_status = 'HAS_QA_PROJECT_NOT_FOUND'
+                        error_dict['qareport_project_not_found_configs'] = qareport_project_not_found_configs
+                    if qareport_build_not_found_configs:
+                        kernel_change_status = 'HAS_QA_BUILD_NOT_FOUND'
+                        error_dict['qareport_build_not_found_configs'] = qareport_build_not_found_configs
+                elif has_jobs_not_submitted:
+                    kernel_change_status = 'HAS_JOBS_NOT_SUBMITTED'
+                elif has_jobs_in_progress:
+                    kernel_change_status = 'HAS_JOBS_IN_PROGRESS'
+                else:
+                    kernel_change_status = 'ALL_COMPLETED'
 
             kernel_change_report = {
                     'kernel_change': kernel_change,
                     'trigger_build': trigger_build,
                     'jenkins_ci_builds': jenkins_ci_builds,
                     'qa_report_builds': qa_report_builds,
+                    'kernel_change_status': kernel_change_status,
+                    'error_dict': error_dict,
+                    'queued_ci_builds': queued_ci_builds,
+                    'diabled_ci_builds': diabled_ci_builds,
+                    'not_reported_ci_builds': not_reported_ci_builds,
+                    'finished_timestamp': kernel_change_finished_timestamp,
                 }
 
             total_reports.append(kernel_change_report)
 
+        ## cache to database
+        for kernel_change_report in total_reports:
+            status = kernel_change_report.get('kernel_change_status')
+            if status != 'ALL_COMPLETED':
+                continue
+
+            kernel_change = kernel_change_report.get('kernel_change')
+            kernel_change.reported = True
+            kernel_change.save()
+
+            trigger_build = kernel_change_report.get('trigger_build')
+            if not trigger_build.get('building'):
+                # should always be here
+                CiBuild.objects.filter(name=trigger_build.get('name'),
+                                        number=trigger_build.get('number')
+                                    ).update(duration=trigger_build.get('duration').total_seconds(),
+                                            timestamp=trigger_build.get('start_timestamp'),
+                                            result=trigger_build.get('result'))
+
+            jenkins_ci_builds = kernel_change_report.get('jenkins_ci_builds')
+            for ci_build in jenkins_ci_builds:
+                if build.get('building'):
+                    # there should be no such case
+                    continue
+                CiBuild.objects.filter(name=ci_build.get('name'),
+                                     number=ci_build.get('number')
+                                 ).update(duration=ci_build.get('duration').total_seconds(),
+                                     timestamp=ci_build.get('start_timestamp'),
+                                     result=ci_build.get('result'))
+
+            for qareport_build in kernel_change_report.get('qa_report_builds'):
+                jenkins_ci_build = qareport_build.get('ci_build')
+                dbci_build = jenkins_ci_build.get('dbci_build')
+                result_numbers = qareport_build.get('numbers_of_result')
+
+                try:
+                    report_build = ReportBuild.objects.get(group=qareport_build.get('project_group'),
+                                                            name=qareport_build.get('project_name'),
+                                                            version=kernel_change.describe)
+                    report_build.kernel_change = kernel_change
+                    report_build.ci_build = dbci_build
+                    report_build.ci_trigger_build = CiBuild.objects.get(name=trigger_build.get('name'), number=trigger_build.get('number'))
+                    report_build.number_passed = result_numbers.get('number_passed')
+                    report_build.number_failed = result_numbers.get('number_failed')
+                    report_build.number_total = result_numbers.get('number_total')
+                    report_build.modules_done = result_numbers.get('modules_done')
+                    report_build.modules_total = result_numbers.get('modules_total')
+                    report_build.started_at = trigger_build.get('start_timestamp')
+                    report_build.fetched_at = qareport_build.get('last_fetched_timestamp')
+                    report_build.save()
+                except KernelChange.DoesNotExist:
+                    ReportBuild.objects.create(group=qareport_build.get('project_group'),
+                                        name=qareport_build.get('project_name'),
+                                        version=kernel_change.describe,
+                                        kernel_change=kernel_change,
+                                        ci_build=dbci_build,
+                                        ci_trigger_build=CiBuild.objects.get(name=trigger_build.get('name'), number=trigger_build.get('number')),
+                                        number_passed=result_numbers.get('number_passed'),
+                                        number_failed=result_numbers.get('number_failed'),
+                                        number_total=result_numbers.get('number_total'),
+                                        modules_done=result_numbers.get('modules_done'),
+                                        modules_total=result_numbers.get('modules_total'),
+                                        started_at=trigger_build.get('start_timestamp'),
+                                        fetched_at=qareport_build.get('last_fetched_timestamp'))
+
+
         # print out the reports
         print "########## REPORTS FOR KERNEL CHANGES#################"
         for kernel_change_report in total_reports:
@@ -272,22 +437,58 @@ class Command(BaseCommand):
             trigger_build = kernel_change_report.get('trigger_build')
             jenkins_ci_builds = kernel_change_report.get('jenkins_ci_builds')
             qa_report_builds = kernel_change_report.get('qa_report_builds')
-            print "%s started at %s, took %s" % (kernel_change, trigger_build['start_timestamp'], trigger_build['duration'])
+            status = kernel_change_report.get('kernel_change_status')
+            trigger_starttimestamp = trigger_build['start_timestamp']
+            finished_timestamp = kernel_change_report.get('finished_timestamp')
+            if status == "ALL_COMPLETED":
+                print "%s started at %s, %s ago, took %s" % (kernel_change, trigger_starttimestamp, timesince(trigger_starttimestamp), finished_timestamp - trigger_starttimestamp)
+            else:
+                print "%s started at %s, %s ago, %s" % (kernel_change, trigger_starttimestamp, timesince(trigger_starttimestamp), status)
 
             print "\t Reports for CI Builds:"
             for build in jenkins_ci_builds:
-                db_ci_build = build.get('db_ci_build')
-                print "\t\t %s#%s %s, started at %s, took %s" % (db_ci_build.name, db_ci_build.number,
+                dbci_build = build.get('dbci_build')
+                if build.get('status') == 'INPROGRESS':
+                    print "\t\t %s#%s %s, started at %s, %s ago" % (dbci_build.name, dbci_build.number,
                                                              build.get('status'),
                                                              build.get('start_timestamp'),
+                                                             timesince(build.get('start_timestamp')))
+                else:
+                    print "\t\t %s#%s %s, started at %s, %s ago, took %s" % (dbci_build.name, dbci_build.number,
+                                                             build.get('status'),
+                                                             build.get('start_timestamp'),
+                                                             timesince(build.get('start_timestamp')),
                                                              build.get('duration'))
 
+            queued_ci_builds = kernel_change_report.get('queued_ci_builds')
+            for build in  queued_ci_builds:
+                inqueuesince = datetime.datetime.fromtimestamp(int(build.get('inQueueSince')/1000))
+                #duration = datetime_now - inqueuesince
+                print "\t\t %s: still in queue since %s ago" % (build.get('build_name'), timesince(inqueuesince))
+
+            not_reported_ci_builds = kernel_change_report.get('not_reported_ci_builds')
+            for build in not_reported_ci_builds:
+                print "\t\t %s: not reported" % (str(build))
+
+            diabled_ci_builds = kernel_change_report.get('diabled_ci_builds')
+            for build in diabled_ci_builds:
+                print "\t\t %s: disabled" % (str(build))
+
             print "\t Summary of Projects Status:"
             for build in qa_report_builds:
                 qa_report_project = build.get('qa_report_project')
-                print "\t\t %s %s %s" % (qa_report_project.get('full_name'),
-                                            build.get('build_status'),
-                                            build.get('created_at'))
+                if build.get('build_status') == "JOBSCOMPLETED":
+                    print "\t\t %s %s, created at %s, %s ago, took %s" % (qa_report_project.get('full_name'),
+                                                build.get('build_status'),
+                                                build.get('created_at'),
+                                                timesince(build.get('created_at')),
+                                                build.get('duration'))
+                else:
+                    print "\t\t %s %s, created at %s, %s ago" % (qa_report_project.get('full_name'),
+                                                build.get('build_status'),
+                                                build.get('created_at'),
+                                                timesince(build.get('created_at')))
+
                 numbers_of_result = build.get('numbers_of_result')
                 str_numbers = "\t\t\t Summary: modules_total=%s, modules_done=%s, number_total=%s, number_failed=%s"
                 print str_numbers % (numbers_of_result.get('modules_total'),
diff --git a/lkft/migrations/0002_auto_20200221_0212.py b/lkft/migrations/0002_auto_20200221_0212.py
new file mode 100644
index 0000000..ae37867
--- /dev/null
+++ b/lkft/migrations/0002_auto_20200221_0212.py
@@ -0,0 +1,67 @@
+# -*- coding: utf-8 -*-
+# Generated by Django 1.11.5 on 2020-02-21 02:12
+from __future__ import unicode_literals
+
+from django.db import migrations, models
+import django.db.models.deletion
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('lkft', '0001_initial'),
+    ]
+
+    operations = [
+        migrations.CreateModel(
+            name='ReportBuild',
+            fields=[
+                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
+                ('group', models.CharField(max_length=100)),
+                ('name', models.CharField(max_length=100)),
+                ('version', models.CharField(max_length=100)),
+                ('number_passed', models.IntegerField()),
+                ('number_failed', models.IntegerField()),
+                ('number_total', models.IntegerField()),
+                ('modules_done', models.IntegerField()),
+                ('modules_total', models.IntegerField()),
+                ('started_at', models.DateTimeField(null=True)),
+                ('fetched_at', models.DateTimeField(null=True)),
+            ],
+        ),
+        migrations.AddField(
+            model_name='cibuild',
+            name='duration',
+            field=models.IntegerField(default=0),
+        ),
+        migrations.AddField(
+            model_name='cibuild',
+            name='result',
+            field=models.CharField(max_length=100, null=True),
+        ),
+        migrations.AddField(
+            model_name='cibuild',
+            name='timestamp',
+            field=models.DateTimeField(null=True),
+        ),
+        migrations.AlterField(
+            model_name='cibuild',
+            name='kernel_change',
+            field=models.ForeignKey(null=True, on_delete=django.db.models.deletion.CASCADE, to='lkft.KernelChange'),
+        ),
+        migrations.AddField(
+            model_name='reportbuild',
+            name='ci_build',
+            field=models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, related_name='ci_build', to='lkft.CiBuild'),
+        ),
+        migrations.AddField(
+            model_name='reportbuild',
+            name='ci_trigger_build',
+            field=models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, related_name='trigger_build', to='lkft.CiBuild'),
+        ),
+        migrations.AddField(
+            model_name='reportbuild',
+            name='kernel_change',
+            field=models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='lkft.KernelChange'),
+        ),
+    ]
diff --git a/lkft/models.py b/lkft/models.py
index f69d34b..3cb1b4f 100644
--- a/lkft/models.py
+++ b/lkft/models.py
@@ -32,7 +32,10 @@ class CiBuildKernelChangeManager(models.Manager):
 class CiBuild(models.Model):
     name = models.CharField(max_length=255)
     number = models.IntegerField()
-    kernel_change = models.ForeignKey(KernelChange, on_delete=None)
+    kernel_change = models.ForeignKey(KernelChange, null=True, on_delete=None)
+    timestamp = models.DateTimeField(null=True)
+    duration = models.IntegerField(default=0) # total_seconds
+    result = models.CharField(max_length=100, null=True, default="NOINFO")
 
     def __str__(self):
         return "%s#%s" % (self.name, self.number)
@@ -42,3 +45,36 @@ class CiBuild(models.Model):
 
     objects = models.Manager()
     objects_kernel_change = CiBuildKernelChangeManager()
+
+
+class ReportBuild(models.Model):
+    # the group that this build belongs to
+    group = models.CharField(max_length=100)
+    # the name of the qareport project
+    name = models.CharField(max_length=100)
+    # the version of the qareport build
+    version = models.CharField(max_length=100)
+
+    kernel_change = models.ForeignKey(KernelChange, on_delete=None)
+    ci_build = models.ForeignKey(CiBuild, on_delete=None, related_name="ci_build")
+    ci_trigger_build = models.ForeignKey(CiBuild, on_delete=None, related_name='trigger_build')
+
+    number_passed = models.IntegerField()
+    number_failed = models.IntegerField()
+    number_total = models.IntegerField()
+    modules_done = models.IntegerField()
+    modules_total = models.IntegerField()
+
+    # the time the trigger build was started
+    started_at = models.DateTimeField(null=True)
+    # the time the last job was fetched
+    fetched_at = models.DateTimeField(null=True)
+
+
+    def __str__(self):
+        return "%s#%s#%s" % (self.group, self.name, self.version)
+
+    def __unicode__(self):
+        return "%s#%s#%s" % (self.group, self.name, self.version)
+
+    objects = models.Manager()
\ No newline at end of file
diff --git a/lkft/views.py b/lkft/views.py
index f3b9e9a..57df19d 100644
--- a/lkft/views.py
+++ b/lkft/views.py
@@ -461,7 +461,8 @@ def get_project_info(project):
         project['last_build']['build_status'] == "JOBSCOMPLETED":
         last_ci_build = project.get('last_ci_build')
         last_build = project.get('last_build')
-        project['duration'] = last_build.get('last_fetched_timestamp') - last_ci_build.get('start_timestamp')
+        if last_ci_build.get('ci_build_last_start_timestamp'):
+            project['duration'] = last_build.get('last_fetched_timestamp') - last_ci_build.get('start_timestamp')
 
     logger.info("%s: finished to get information for project", project.get('name'))
 
@@ -1076,11 +1077,14 @@ def new_kernel_changes(request, branch, describe, trigger_name, trigger_number):
         err_msg = 'request for branch=%s, describe=%s is already there' % (branch, describe)
         logger.info(err_msg)
     except KernelChange.DoesNotExist:
-        KernelChange.objects.create(branch=branch,
+        kernel_change = KernelChange.objects.create(branch=branch,
                                     describe=describe,
                                     reported=False,
                                     trigger_name=trigger_name,
                                     trigger_number=trigger_number)
+        CiBuild.objects.create(name=trigger_name,
+                                number=trigger_number,
+                                kernel_change=kernel_change)
 
 
     if err_msg is None: