#!/bin/python3

import pandas as pd
import glob
import openpyxl
from colorama import Fore, Back, Style, init

init(autoreset=True)

# Опция отвечающая за вывод всех колонок в консоли
pd.set_option('display.expand_frame_repr', False)

key_Filter_M = ["М"]
key_Filter_F = ["Ж"]

current_month = [07.2021]

count_sexGL = None
count_sexM = None
count_sexF = None
count_sexM_Emp = None
count_sexF_Emp = None

location_file = r'/home/aser/Рабочий стол/диспансерное наблюдение/Общий_автоматически.xlsx'
save_files = r'/home/aser/Рабочий стол/диспансерное наблюдение/Автомат.xlsx'

file = glob.glob(location_file)

df = pd.read_excel(location_file, usecols = "A, B, D, E, H, O, R, S")

#Фильтр для диагнозов
keys_Filter_i20 = ["I20.0", "I20.1", "I20.8", "I20.9", "I25.0", "I25.1", "I25.2", "I25.5", "I25.6", "I25.8", "I25.9"]
keys_Filter_i10 = ["I10","I10.0","I10.1","I10.2", "I10.3", "I10.4", "I10.5", "I10.6", "I10.7", "I10.8", "I10.9", "I11", "I11.0", "I11.1", "I11.2", "I11.3", "I11.4", "I11.5", "I11.6", "I11.7", "I11.8", "I11.9", "I12", "I12.0", "I12.1", "I12.2", "I12.3", "I12.4", "I12.5", "I12.6", "I12.7", "I12.8", "I12.9", "I13", "I13.0", "I13.1", "I13.2", "I13.3", "I13.4", "I13.5", "I13.6", "I13.7", "I13.8", "I13.9", "I15", "I15.0", "I15.1", "I15.5", "I15.3", "I15.4", "I15.5", "I15.6", "I15.7", "I15.8", "I15.9"]
keys_Filter_i50 = ["I50.0","I50.1","I50.2", "I50.3", "I50.4", "I50.5", "I50.6", "I50.7", "I50.8", "I50.9"]
keys_Filter_i48 = ["I48", "I48.0"]
keys_Filter_i47 = ["I47", "I47.0"]
keys_Filter_i65 = ["I65.2"]
keys_Filter_r73 = ["R73", "R73.0", "R73.0"]
keys_Filter_e11 = ["E11","E11.0","E11.1","E11.2", "E11.3", "E11.4", "E11.5", "E11.6", "E11.7", "E11.8", "E11.9"]
keys_Filter_i69 = ["I69.0", "I69.1", "I69.2", "I69.3", "I69.4", "I67.8"]
keys_Filter_e78 = ["E78", "E78.0"]
keys_Filter_k20 = ["K20", "K20.0"]
keys_Filter_k21 = ["K21", "K21.0"]
keys_Filter_k25 = ["K25", "K25.0", "K25.1", "K25.2", "K25.3", "K25.4", "K25.5", "K25.6", "K25.7", "K25.8", "K25.9"]
keys_Filter_k26 = ["K26", "K26.0", "K26.1", "K26.2", "K26.3", "K26.4", "K26.5", "K26.6", "K26.7", "K26.8", "K26.9"]
keys_Filter_k29 = ["K29.4", "K29.5"]
keys_Filter_k31 = ["K31.7"]
keys_Filter_k86 = ["K86", "K86.1"]
keys_Filter_j44 = ["J44", "J44.0", "J44.1", "J44.2", "J44.3", "J44.4", "J44.5", "J44.6", "J44.7", "J44.8", "J44.9"]
keys_Filter_j47 = ["J47", "J47.0"]
keys_Filter_j45 = ["J45", "J45.0", "J45.1", "J45.2", "J45.3", "J45.4", "J45.5", "J45.6", "J45.7", "J45.8", "J45.9"]
keys_Filter_j12_13_14 = ["J12", "J12.0", "J12.1", "J12.2", "J12.3", "J12.4", "J12.5", "J12.6", "J12.7", "J12.8", "J12.9",
"J13", "J13.0", "J13.1", "J13.2", "J13.3", "J13.4", "J13.5", "J13.6", "J13.7", "J13.8", "J13.9", "J14", "J14.0", "J14.1", "J14.2", "J14.3", "J14.4", "J14.5", "J14.6", "J14.7", "J14.8", "J14.9"]
keys_Filter_j84 = ["J84", "J84.1"]
keys_Filter_n18 = ["N18.1"]
keys_Filter_n18_9 = ["N18.5", "N18.8", "N18.9"]
keys_Filter_m81 = ["M81.5"]

#Создаём список для перебора из фильтра
gen_list = [keys_Filter_i20, keys_Filter_i10, keys_Filter_i50, keys_Filter_i48, keys_Filter_i47, keys_Filter_i65, keys_Filter_r73, keys_Filter_e11, keys_Filter_i69, keys_Filter_e78, keys_Filter_k20, keys_Filter_k21, keys_Filter_k25, keys_Filter_k26, keys_Filter_k29, keys_Filter_k31, keys_Filter_k86, keys_Filter_j44, keys_Filter_j47, keys_Filter_j45, keys_Filter_j12_13_14, keys_Filter_j84, keys_Filter_n18, keys_Filter_n18_9, keys_Filter_m81]

#df_1 = df.loc[df['Диагноз'].isin(keys_Filter_i20)]

for elem in gen_list:
    #Применяем фильтр к столбцу "Диагноз"
    df_1 = df.loc[df['Диагноз'].isin(elem)]
    #Создаём переменную с общим кол-вом отобранных пациентов по диагнозам I2....
    count_sexGL = len(df_1.index)
    print(df_1)
    df_new_pacient_on_year = df_1.loc[df_1['Дата взятия на учет'].isin(current_month)]
    print(df_new_pacient_on_year)
    #count_sexGL_New = len(df_new_pacient_on_year.index)

    #Фильтруем по полу (М) и определяем количество человек
    df_1_M = df_1.loc[df['Пол'].isin(key_Filter_M)]
    count_sexM = len(df_1_M.index)

    #Выбираем исключительно трудоспособный возраст (М) и определяем количество
    df_1_M = df_1_M.loc[df['Возраст'] < 65]
    count_sexM_Emp = len(df_1_M.index)

    #Фильтруем по полу (Ж) и определяем количество человек
    df_2_F = df_1.loc[df['Пол'].isin(key_Filter_F)]
    count_sexF = len(df_2_F.index)

    #Выбираем исключительно трудоспособный возраст (М) и определяем количество
    df_2_F = df_2_F.loc[df['Возраст'] < 60]
    count_sexF_Emp = len(df_2_F.index)

    print(Fore.RED + "Общее количество с диагнозом", ' '.join(elem), Fore.CYAN + " = ", count_sexGL)
    print(Fore.RED + "Общее количество с диагнозом впервые поставленных в этом году", ' '.join(current_month), Fore.CYAN + " = ", count_sexGL_New)
    print(Fore.YELLOW + "Из них мужчин = ",  count_sexM)
    print(Fore.YELLOW + "Трудоспособного возраста = ",  count_sexM_Emp)
    print(Fore.YELLOW + "Старше трудоспособного возраста = ", count_sexM - count_sexM_Emp)
    print(Fore.GREEN + "Женщин = ",  count_sexF)
    print(Fore.GREEN + "Трудоспособного возраста = ",  count_sexF_Emp)
    print(Fore.GREEN + "Старше трудоспособного возраста = ", count_sexF - count_sexF_Emp)
    print("=========================================================================================================================================================================================================================================")