elab_mvp/test_info.py

from mysql_db import MysqlDB
from itertools import groupby



class TestInfo(object):
    """
        测试回收情况
    """
    # 获取测试的答题记录情况
    sql_1 = '''
        SELECT
            a.testcase_id,
            a.title,
            a.sub_question_id,
            b.sub_question_content,
            a.score,
            b.sub_option_content,
            count(1)
        FROM
            f_t_daren_score_2 a
        LEFT JOIN d_shangju_tiku_02 b ON a. STATUS = b. STATUS = 1
        WHERE
            a.testcase_id = %s and
            a.testcase_id = b.testcase_id
        AND a.sub_question_id = b.sub_question_id
        AND (
            a.score = b.score
            OR a.score = b.sub_option_id
        )
        GROUP BY
            a.testcase_id,
            a.title,
            a.sub_question_id,
            b.sub_question_content,
            a.score,
            b.sub_option_content
    '''

    # 获取测试题信息
    sql_2 = '''
        SELECT
            id,
            group_type,
            title,
            house_ids,
            creator,
            created,
            updator,
            updated
        FROM
            bq_testcase
        WHERE
            STATUS = 1
    '''

    # 统计测试完成人数
    sql_3 = '''
        SELECT
            COUNT(DISTINCT uuid)
        FROM
            f_t_daren_score_2 a
        WHERE
            testcase_id = %s
        AND is_last = 1
    '''

    # 统计参与答题人数
    sql_4 = '''
        SELECT
            COUNT(DISTINCT uuid)
        FROM
            f_t_daren_score_2 a
        WHERE
            testcase_id = %s
    '''

    sql_5 = '''
        SELECT
            count(1)
        FROM
            f_t_daren_score_2
        WHERE
            testcase_id = %s
    '''

    sql_6 = '''
            SELECT
                score,
                COUNT(uuid)
            FROM
                (
                    SELECT
                        score,
                        COUNT(DISTINCT uuid) AS uuid
                    FROM
                        f_t_daren_score_2
                    WHERE
                        testcase_id = 86
                    AND sub_question_id = 377
                    GROUP BY
                        uuid
                ) a
            GROUP BY
                a.score
    '''

    # 支付力占比统计
    sql_7 = '''
        
            SELECT
                score,
                COUNT(uuid)
            FROM
                (
                    SELECT
                        score,
                        COUNT(DISTINCT uuid) AS uuid
                    FROM
                        f_t_daren_score_2
                    WHERE
                        testcase_id in  (84, 85, 86, 87)
                    AND sub_question_id = 376
                    GROUP BY
                        uuid
                ) a
            GROUP BY
                a.score
    '''

    def __init__(self):
        # self.shangju_db = MysqlDB('shangju')
        self.bi_report_db = MysqlDB('bi_report')

    def test_detail_info(self, testcase_id):
        """
            查看每套测试回收数据的量
        :return:
        """
        response = {}
        try:
            people = self.bi_report_db.select(self.sql_4, [testcase_id])[0][0]
            finished = self.bi_report_db.select(self.sql_3, [testcase_id])[0][0]
            result = self.bi_report_db.select(self.sql_1, [testcase_id])
            count = self.bi_report_db.select(self.sql_5, [testcase_id])
            answers = []
            for index, data in enumerate(result):
                if index == 0:
                    response['testcase_id'] = data[0]
                    response['title'] = data[1]
                    response['答题记录数'] = count[0][0]
                    response['答题人数'] = people
                    response['完成人数'] = finished
                    if people != 0:
                        response['答题完成率'] = float(finished) / float(people)
                    else:
                        response['答题完成率'] = '无法统计'
                answers.append([data[2], data[3], data[4], data[5], data[6]])

            answers.sort(key=lambda obj: obj[0])
            sub_question_data = []
            for sub_question_id, others in groupby(answers, key=lambda obj: obj[0]):
                others_data = []
                for ot in others:
                    others_data.append([x for x in ot])
                sub_question = {}
                sub_question['id'] = sub_question_id
                sub_question['题干'] = others_data[0][1]
                sub_option = []
                for th in others_data:
                    sub_option.append([th[2], th[3], th[4]])
                sub_question['option'] = sub_option
                sub_question_data.append(sub_question)
            response['答题结果统计'] = sub_question_data
        except Exception as e:
            response['error'] = str(e)
        return response