from excel_util import ExcelUtil
from mysql_db import MysqlDB


class TongCe:
    """
        同策测试数据清洗
    """
    # 统计筒体结果
    sql_1 = '''
        SELECT
			b.father_id,
			b.father_content,
            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 in (84, 85, 86, 87) 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
			b.father_id,
            a.sub_question_id,
            b.sub_question_content,
            a.score,
            b.sub_option_content
    '''

    # 选项信息
    sql_2 = '''
          SELECT
            b.id as question_id,
            b. NAME as question_title,
            a.id as sub_question_id,
            a. NAME as sub_question_title,
            d.id as option_id,
            d.content as option_title,
            c.id as sub_option_id,
            c.content as sub_option_title
        FROM
            bq_sub_question a
        LEFT JOIN bq_question b ON a.father_id = b.id
        
        LEFT JOIN bq_sub_option c ON a.id = c.sub_question_id
        LEFT JOIN bq_option d ON c.father_id = d.id
        WHERE
            FIND_IN_SET(
                a.id,
                (
                    SELECT
                        GROUP_CONCAT(question_ids)
                    FROM
                        bq_testcase
                    WHERE
                        house_ids = %s
                    GROUP BY
                        house_ids
                )
            )
        AND a. STATUS = b. STATUS = c. STATUS = 1
        ORDER BY
            a.id
    '''

    def __init__(self):
        self.shangju_db = MysqlDB('shangju')
        self.marketing_db = MysqlDB('bi_report')
        self.linshi_db = MysqlDB('linshi', db_type=1)
        self.options_info = ExcelUtil('工作表6', 'tongce.xlsx').read_options_info()

    def get_question_info_from_db(self):
        result = self.shangju_db.select(self.sql_2, [67])
        insert_data = []
        for rt in result:
            option_configuration = self.options_info.get('67' + str(rt[6]))
            if option_configuration and len(option_configuration) == 3:
                rt.extend(option_configuration)
                insert_data.append(rt)
        return result

    pass