tongce.py 2.6 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091
  1. from excel_util import ExcelUtil
  2. from mysql_db import MysqlDB
  3. class TongCe:
  4. """
  5. 同策测试数据清洗
  6. """
  7. # 统计筒体结果
  8. sql_1 = '''
  9. SELECT
  10. b.father_id,
  11. b.father_content,
  12. a.sub_question_id,
  13. b.sub_question_content,
  14. a.score,
  15. b.sub_option_content,
  16. count(1)
  17. FROM
  18. f_t_daren_score_2 a
  19. LEFT JOIN d_shangju_tiku_02 b ON a. STATUS = b. STATUS = 1
  20. WHERE
  21. a.testcase_id in (84, 85, 86, 87) and
  22. a.testcase_id = b.testcase_id
  23. AND a.sub_question_id = b.sub_question_id
  24. AND (
  25. a.score = b.score
  26. OR a.score = b.sub_option_id
  27. )
  28. GROUP BY
  29. b.father_id,
  30. a.sub_question_id,
  31. b.sub_question_content,
  32. a.score,
  33. b.sub_option_content
  34. '''
  35. # 选项信息
  36. sql_2 = '''
  37. SELECT
  38. b.id as question_id,
  39. b. NAME as question_title,
  40. a.id as sub_question_id,
  41. a. NAME as sub_question_title,
  42. d.id as option_id,
  43. d.content as option_title,
  44. c.id as sub_option_id,
  45. c.content as sub_option_title
  46. FROM
  47. bq_sub_question a
  48. LEFT JOIN bq_question b ON a.father_id = b.id
  49. LEFT JOIN bq_sub_option c ON a.id = c.sub_question_id
  50. LEFT JOIN bq_option d ON c.father_id = d.id
  51. WHERE
  52. FIND_IN_SET(
  53. a.id,
  54. (
  55. SELECT
  56. GROUP_CONCAT(question_ids)
  57. FROM
  58. bq_testcase
  59. WHERE
  60. house_ids = %s
  61. GROUP BY
  62. house_ids
  63. )
  64. )
  65. AND a. STATUS = b. STATUS = c. STATUS = 1
  66. ORDER BY
  67. a.id
  68. '''
  69. def __init__(self):
  70. self.shangju_db = MysqlDB('shangju')
  71. self.marketing_db = MysqlDB('bi_report')
  72. self.linshi_db = MysqlDB('linshi', db_type=1)
  73. self.options_info = ExcelUtil('工作表6', 'tongce.xlsx').read_options_info()
  74. def get_question_info_from_db(self):
  75. result = self.shangju_db.select(self.sql_2, [67])
  76. insert_data = []
  77. for rt in result:
  78. rt = list(rt)
  79. option_configuration = self.options_info.get('67' + str(rt[6]))
  80. if option_configuration and len(option_configuration) == 3:
  81. rt.extend(option_configuration)
  82. insert_data.append(rt)
  83. return result
  84. pass