import pandas as pd
import math
import json

student_info = pd.read_csv("./data/2_student_info.csv")
chengji = pd.read_csv("./data/5_chengji.csv")
stu_index = pd.read_csv("./data/stu_index.csv")
stu_index.rename(index=str, columns={'Unnamed: 0': 'stu_id'}, inplace=True)

def del_ht(s):
    if '\t' in s:
        return s[1:]
    else:
        return s

def nan_to_zero(s):
    if math.isnan(s):
        return 0
    else:
        return int(s)

chengji['exam_numname'] = chengji['exam_numname'].apply(del_ht)

stu_and_class = student_info[['bf_StudentID', 'cla_Name']].rename(index=str, columns={'bf_StudentID': 'stu_id', 'cla_Name': 'cla_name'})
t = stu_index[['stu_id']][(stu_index['t_info'] > 0) & (stu_index['t_grade'] > 0)]
t1 = pd.merge(chengji, pd.merge(t, stu_and_class, how='left'), how='left', left_on='mes_StudentID', right_on='stu_id')
t1['stu_id'] = t1['stu_id'].apply(nan_to_zero)
t1.drop_duplicates(subset=['exam_numname', 'mes_sub_name', 'stu_id'],keep='last',inplace=True)

t2 = t1[['exam_number', 'exam_numname', 'mes_sub_id', 'mes_sub_name', 'cla_name', 'stu_id', 'mes_T_Score']][~t1['cla_name'].isnull()]
t3 = t2[
    (t2['cla_name'].str.contains('高二')) &
    (~t2['cla_name'].str.contains('IB')) &
    (~t2['mes_sub_name'].isnull()) &
    (~t2['cla_name'].str.contains('未分班'))]

# 高一学生有效考试
# 300 2018-2019新高一7月测试
# 303 2018学年度第一学期平时成绩1
# 305 2018学年度第一学期平时成绩2
# 304 2018-1学期期中考试
# 高二学生有效考试
# 284     2017学年度第一学期期中考试
# 288     2017学年度第一学期期末考试
# 289     2017学年度第一学期期末总评
# 292     2017学年度第二学期期中考试
# 298     2017学年度第二学期期末考试
# 299      2017学年第二学期期末总评
# 304        2018-1学期期中考试
# 11   音乐
# 12   美术
# 9    体育
# 59   技术
# 1    语文
# 2    数学
# 3    英语
# 5    化学
# 6    生物
# 7    历史
# 8    地理
# 17   政治
# 4    物理
stu_g2 = [14460, 14471, 14505, 14465, 14506, 14494, 14462, 14473, 14458, 14498, 14507, 14466, 14472, 14463, 14499, 14474, 14491, 14487, 14459, 14464, 14496, 14475, 14479, 14511, 14461, 14493, 14478, 14492, 14508, 14476, 14455, 14504, 14500, 14512, 14469, 14501, 14486, 14481, 14454, 14502, 14470, 14483, 14488, 14489, 14482, 14503, 14513, 14497, 14495, 14477, 14456, 14480, 14457, 14468, 14467, 14510, 14620, 14900, 14930, 14798, 14910, 14892, 14886, 14940, 14923, 14937, 14905, 14916, 14864, 14889, 14929, 14897, 14894, 14538, 14661, 14902, 14906, 14817, 14549, 14769, 14525, 14687, 14571, 14861, 14728, 14751, 14771, 14529, 14556, 14618, 14553, 14725, 14873, 14848, 14563, 14734, 14598, 14932, 14693, 14887, 14935, 14903, 14757, 14885, 14917, 14871, 14775, 14904, 14583, 14707, 14692, 14852, 14920, 14888, 14565, 14518, 14710, 14677, 14662, 14740, 14548, 14516, 14534, 14933, 14901, 14694, 14806, 14934, 14790, 14854, 14918, 14611, 14884, 14870, 14926, 14600, 14664, 14912, 14891, 14859, 14667, 14635, 14785, 14539, 14913, 14784, 14860, 14632, 14668, 14863, 14895, 14572, 14606, 14670, 14893, 14797, 14753, 14914, 14882, 14818, 14835, 14595, 14627, 14755, 14921, 14883, 14761, 14915, 14665, 14562, 14633, 14594, 14722, 14922, 14858, 14602, 14754, 14669, 14936, 14879, 14584, 14896, 14909, 14686, 14586, 14715, 14866, 14585, 14898, 14779, 14609, 14617, 14814, 14527, 14846, 14810, 14672, 14907, 14705, 14939, 14621, 14833, 14522, 14942, 14713, 14941, 14928, 14745, 14587, 14908, 14876, 14684, 14943, 14526, 14872, 14931, 14622, 14555, 14623, 14619, 14706, 14938, 14911, 14899, 14844, 14746, 14787, 14826, 14878, 14794, 14654, 14714, 14666, 14782, 14634, 14750, 14682, 14558, 14590, 14699, 14819, 14691, 14827, 14603, 14749, 14796, 14881, 14927, 14799, 14735, 14639, 14849, 14575, 14543, 14780, 14524, 14523, 14944, 14812, 14880, 14560, 14688, 14656, 14588, 14593, 14764, 14875, 14732, 14700, 14604, 14557, 14658, 14717, 14690, 14803, 14638, 14850, 14541, 14813, 14683, 14877, 14651, 14702, 14829, 14815, 14842, 14758, 14776, 14741, 14680, 14648, 14645, 14726, 14581, 14545, 14840, 14774, 14592, 14614, 14582, 14550, 14737, 14777, 14869, 14738, 14544, 14663, 14739, 14514, 14579, 14547, 14631, 14578, 14610, 14695, 14855, 14727, 14759, 14772, 14708, 14676, 14644, 14823, 14865, 14552, 14839, 14793, 14704, 14628, 14536, 14559, 14724, 14825, 14655, 14551, 14697, 14719, 14788, 14601, 14874, 14537, 14681, 14624, 14629, 14597, 14679, 14711, 14576, 14640, 14531, 14528, 14567, 14851, 14811, 14809, 14756, 14650, 14841, 14535, 14843, 14791, 14554, 14786, 14747, 14659, 14822, 14521, 14616, 14649, 14789, 14821, 14853, 14630, 14744, 14625, 14723, 14530, 14532, 14808, 14919, 14689, 14596, 14721, 14657, 14867, 14783, 14800, 14519, 14736, 14768, 14856, 14824, 14792, 14760, 14696, 14743, 14568, 14591, 14857, 14847, 14890, 14762, 14731, 14608, 14807, 14924, 14805, 14675, 14515, 14868, 14612, 14580, 14770, 14837, 14709, 14801, 14613, 14517, 14641, 14742, 14673, 14678, 14646, 14718, 14647, 14828, 14607, 14830, 14862, 14542, 14573, 14652, 14605, 14701, 14765, 14685, 14767, 14574, 14703, 14671, 14589, 14636, 14816, 14540, 14733, 14763, 14838, 14795, 14781, 14570, 14643, 14637, 14766, 14674, 14836, 14834, 14773, 14804, 14569, 14660, 14599, 14831, 14712, 14748, 14845, 14698, 14626, 14820, 14752, 14730, 14778, 14615, 14533]
sub = [4, 5, 6, 17, 7, 8, 59]

t4 = t3[['exam_number', 'stu_id', 'mes_sub_id', 'mes_T_Score']][
    (t3['exam_number']==284) |
    (t3['exam_number']==288) |
    (t3['exam_number']==289) |
    (t3['exam_number']==292) |
    (t3['exam_number']==298) |
    (t3['exam_number']==299) |
    (t3['exam_number']==304)].set_index('exam_number')

def grade_trend(stu_id, sub_id):
    s = t4['mes_T_Score'][(t4['stu_id']==stu_id) & (t4['mes_sub_id']==sub_id)].sort_index().fillna(-1).round(decimals=2)
    return list(s)

obj = {}
for stu_id in stu_g2:
    for sub_id in sub:
        obj[str(stu_id)] = {
            'wl': grade_trend(stu_id, 4),
            'hx': grade_trend(stu_id, 5),
            'sw': grade_trend(stu_id, 6),
            'zz': grade_trend(stu_id, 17),
            'ls': grade_trend(stu_id, 7),
            'dl': grade_trend(stu_id, 8),
            'js': grade_trend(stu_id, 59),
        }

json1 = json.dumps(obj,ensure_ascii=False,indent=2)
# fl=open('./grade2_sub_trend.json', 'w')
# fl.write(json1)
# fl.close()
