# cargamos la extensión de sql
%load_ext sql

# Nos conectamos a una base de datos en sqlite
%sql sqlite://

# Ahora creamos una tabla de pruebas pequeña
%sql \
CREATE TABLE escritor (nombres, apellidos, muerte); \
INSERT INTO escritor VALUES ('Gabriel', 'García Marquez', 2014); \
INSERT INTO escritor VALUES ('Bertold', 'Bretch', 1956);

%sql select * from escritor

# Importamos los módulos de soporte para bases a datos desde Pandas
import pandas.io.sql as pdsql
import MySQLdb

# Importamos dataframe y pandas que nos permitirá trabajar con estructuras matriciales de datos
from pandas import DataFrame
import pandas as pd

# Importamos el soporte de graficación
from matplotlib import pyplot
# colocamos las gráficas dentro del notebook
%matplotlib inline

# Importamos el soporte para operaciones numéricas y arreglos vía numpy
import numpy as np

# Definimos el total de hospitales para este levantamiento
# de información, que es una constante
totalHospitales = 7

datosIngreso = eval(open("ingresoDb.py", "r").read())

# Establecemos la conexión a la base de datos
conexion = MySQLdb.connect(host = datosIngreso["host"],
                           port = datosIngreso["port"],
                           user = datosIngreso["user"],
                           passwd = datosIngreso["password"],
                           db = datosIngreso["db"])

# Definamos unas consultas del número de pacientes (Población total) en
# cada uno de los hospitales
consultaSqlTotalPacientes = """
            SELECT COUNT(DISTINCT id_paciente) 
            FROM e_consolidado 
            WHERE fecha_folio_hc 
                BETWEEN '2013-10-01' AND '2014-03-31' 
                AND tipo_ingreso=1 
                AND institucion=1;
            """

# Seleccionemos los datos a partir de esta consulta 
totalPacientesHospital1 = pdsql.read_sql(consultaSqlTotalPacientes, conexion) 
totalPacientesHospital1

def sqlTotalPacientesHospital(n):
    # Parte comun de la consulta
    comun = """
         SELECT COUNT(DISTINCT id_paciente) 
            FROM e_consolidado 
            WHERE fecha_folio_hc 
                BETWEEN '2013-10-01' AND '2014-03-31' 
                AND tipo_ingreso=1 
                AND institucion=
        """
    # Parte variable de la consulta
    hospital = n
    # Unimos la parte común y la variable para tener nuestro resultado final
    return comun + str(hospital) + ";"

def totalPacientesHospital(n):
    return pdsql.read_sql(sqlTotalPacientesHospital(n), conexion)

totalPacientesHospital(1).ix[0,0]

totalPacientesHospital(2).ix[0,0]

pacientesPorHospital = {}
for i in range(1,totalHospitales + 1): 
    pacientesPorHospital[i] = totalPacientesHospital(i).ix[0,0]
pacientesPorHospital

dfPacientesPorHospital = DataFrame(pacientesPorHospital, index=[0])
dfPacientesPorHospital.plot(kind='bar', stacked=False, title= u'Pacientes por hospital')

## Se define la totalidad de pacientes por Sexo de acuerdo a la siguiente convención:
## 1 Hombre
## 2 Mujer

sqlPacientesPorSexo = """
    SELECT sexo, 
    COUNT(DISTINCT id_paciente)
    FROM e_consolidado
    WHERE fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31'
    AND institucion = '1'
    AND tipo_ingreso = '1'
    GROUP BY sexo;"""

# Seleccionemos los datos a partir de esta consulta 
totalPacientesPorSexoHospital1 = pdsql.read_sql(sqlPacientesPorSexo, conexion) 
totalPacientesPorSexoHospital1 

# Automatizamos la consulta para que use variables para la institución en lugar de valores fijos
def sqlTotalPacientesPorSexoHospital(n):
    # Parte comun de la consulta
    comun = """
         SELECT sexo, 
         COUNT(DISTINCT id_paciente)
         FROM e_consolidado
         WHERE fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31'
           AND institucion = '%i'
           AND tipo_ingreso = '1'
         GROUP BY sexo;"""
    # Unimos la parte común y la variable para tener nuestro resultado final
    return comun %(n)

def totalPacientesPorSexoHospital(n):
    return pdsql.read_sql(sqlTotalPacientesPorSexoHospital(n), conexion) 

pacientesPorSexoHospital = {}
for i in range(1,totalHospitales + 1): 
    pacientesPorSexoHospital[i] = totalPacientesPorSexoHospital(i)
# pacientesPorSexoHospital

# Consolidado Pacientes Por Sexo- Tenemos que dividir esto en dos
# ciclos disintos e integrarlos al final. No sabemos si es una limitación
# de pandas (PREGUNTAR en stackoverflow). 
dfPacientesPorSexoHospital1 = pacientesPorSexoHospital[1]
for i in range(2,4):
    dfPacientesPorSexoHospital1 = pd.merge(dfPacientesPorSexoHospital1, pacientesPorSexoHospital[i], on='sexo', how='outer')
dfPacientesPorSexoHospital2 = pacientesPorSexoHospital[4]
for i in range(5,8):
    dfPacientesPorSexoHospital2 = pd.merge(dfPacientesPorSexoHospital2, pacientesPorSexoHospital[i], on='sexo', how='outer')
dfPacientesPorSexoHospital = pd.merge(dfPacientesPorSexoHospital1, dfPacientesPorSexoHospital2, on='sexo', how='outer')
dfPacientesPorSexoHospital

listaPacientesPorSexoHospital = np.array(dfPacientesPorSexoHospital.get_values()).tolist()
totalHombres = listaPacientesPorSexoHospital[0][1:8]
totalMujeres = listaPacientesPorSexoHospital[1][1:8]

# Basados en: http://people.duke.edu/~ccc14/pcfb/numpympl/MatplotlibBarPlots.html#simple-bar-plot

import numpy as np
import matplotlib.pyplot as plt

fig = plt.figure()
ax = fig.add_subplot(111)

## the data
N = totalHospitales
menMeans = totalHombres
womenMeans = totalMujeres

## necessary variables
ind = np.arange(N)                # the x locations for the groups
width = 0.35                      # the width of the bars

## the bars
rects1 = ax.bar(ind, menMeans, width,
                color='black')

rects2 = ax.bar(ind+width, womenMeans, width,
                    color='red')

# axes and labels
ax.set_xlim(-width,len(ind)+width)
ax.set_ylim(0,20000)
ax.set_ylabel(u'Número de pacientes')
ax.set_title(u'Número de pacientes agrupados por sexo')
xTickMarks = ['Hospital '+str(i) for i in range(1,8)]
ax.set_xticks(ind+width)
xtickNames = ax.set_xticklabels(xTickMarks)
plt.setp(xtickNames, rotation=45, fontsize=10)

## add a legend
ax.legend( (rects1[0], rects2[0]), ('Hombres', 'Mujeres') )

plt.show()

## Consultas médicas por mes

totalConsultasporMes = """
    SELECT MONTH(fecha_folio_hc),YEAR(fecha_folio_hc), COUNT(DISTINCT CONCAT(fecha_folio_hc,id_paciente)) 
    FROM e_consolidado 
    WHERE fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31' 
    AND institucion ='1' 
    AND tipo_ingreso ='1' 
    GROUP BY MONTH(fecha_folio_hc),YEAR(fecha_folio_hc)
    ORDER BY YEAR(fecha_folio_hc);"""

totalConsultasporMesHospital1 = pdsql.read_sql(totalConsultasporMes, conexion) 
totalConsultasporMesHospital1

#automatizar

## Los medicamentos antihipertensivos identificados en los hospitales se han agrupado 
## por grupo farmacológico. La siguiente consulta tiene como objetivo identificar 
## cuantos estan en tratamiento con cada grupo farmacológico

pacientesporGrupoAntihipertensivo = """

    SELECT Antihipertensivo, COUNT(DISTINCT id_paciente)
    FROM e_consolidado 
    WHERE fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31' 
    AND institucion ='1' 
    AND tipo_ingreso ='1'
    AND Antihipertensivo <>'' 
    GROUP BY Antihipertensivo;"""

dfPacientesGrupoAntihipertensivoHospital1 = pdsql.read_sql(pacientesporGrupoAntihipertensivo, conexion) 
dfPacientesGrupoAntihipertensivoHospital1

# Gráfica de torta. Base tomada de: 
# http://matplotlib.org/1.2.1/examples/pylab_examples/pie_demo.html
"""
Make a pie chart - see
http://matplotlib.sf.net/matplotlib.pylab.html#-pie for the docstring.

This example shows a basic pie chart with labels optional features,
like autolabeling the percentage, offsetting a slice with "explode",
adding a shadow, and changing the starting angle.

"""


# make a square figure and axes
figure(1, figsize=(6,6))
ax = axes([0.1, 0.1, 0.8, 0.8])

# The slices will be ordered and plotted counter-clockwise.
labels = 'Frogs', 'Hogs', 'Dogs', 'Logs'
fracs = [15, 30, 45, 10]
explode=(0, 0.05, 0, 0)

pie(fracs, explode=explode, labels=labels,
                autopct='%1.1f%%', shadow=True, startangle=90)
                # The default startangle is 0, which would start
                # the Frogs slice on the x-axis.  With startangle=90,
                # everything is rotated counter-clockwise by 90 degrees,
                # so the plotting starts on the positive y-axis.

title('Raining Hogs and Dogs', bbox={'facecolor':'0.8', 'pad':5})

show()

#automatizar

## Calculamos el total de médicos por institución durante el periodo
## (Octubre de 2013 a Marzo de 2014)

medicosPeriodo = """
    SELECT COUNT(DISTINCT medico) 
    FROM e_consolidado 
    WHERE fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31' 
        AND institucion ='1' 
        AND tipo_ingreso ='1';"""

medicosPeriodoHospital1 = pdsql.read_sql(medicosPeriodo, conexion) 
medicosPeriodoHospital1

#Sin embargo se identifica que existen especialidades no médicas en la 
#base de datos, dado que otros profesionales de la salud también utilizan
#la historia clínica, por tanto se deben identificar cuales son esas 
#profesiones distintas para excluirlas del conteo

Especialidades = """
    SELECT DISTINCT especialidad 
    FROM e_consolidado
    WHERE fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31'
        AND tipo_ingreso = '1'
        AND especialidad <>'';"""

diferentesEspecialidadesdb = pdsql.read_sql(Especialidades, conexion) 
diferentesEspecialidadesdb

##Se realiza nuevamente la consulta excluyendo las especialidades
## no médicas (Enfermera jefe, Optometría, Enfermeria jefe, Odontología pediátrica, 
## Endodoncia, Nutrición, Odontología integral del adulto, Enfermeria en Hospitalización
##Enfermeria promocion y prevencion, Enfermeria, Higienista oral, Auxiliar de enfermeria

especialidadesMedicas = """
    SELECT COUNT(DISTINCT medico)
    FROM e_consolidado
    WHERE fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31'
        AND tipo_ingreso = '1'
        AND especialidad not REGEXP 'enferme|optome|odontol|endodon|nutric|higienis'
        AND institucion = '1';"""

medicosPeriodo = pdsql.read_sql(especialidadesMedicas, conexion) 
medicosPeriodo

#automatizar

##Considerando que la consulta medicosPeriodo corresponde a la totalidad de medicos
##durante el periodo comprendido entre octubre de 2013 y marzo de 2014 calculamos 
## ahora la cantidad de medicos por mes para establecer el promedio de médicos de 
## la institución dado que existe alta rotación de personal

sqlMedicosPorMes = """
    SELECT MONTH(fecha_folio_hc), YEAR(fecha_folio_hc), 
    COUNT(DISTINCT medico)
    FROM e_consolidado
    WHERE fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31'
        AND tipo_ingreso = '1'
        AND especialidad not REGEXP 'enferme|optome|odontol|endodon|nutric|higienis'
        AND institucion = '1'
    GROUP BY MONTH(fecha_folio_hc), YEAR(fecha_folio_hc)
    ORDER BY YEAR(fecha_folio_hc);"""

totalMedicosPorMes = pdsql.read_sql(sqlMedicosPorMes, conexion) 
totalMedicosPorMes

##Automatizar

## Seria conveniente poder estimar la rotación de medicos durante el periodo

##Establecemos la totalidad de médicos por especialidad. Esto nos ayudará a 
##definir cuales son los médicos que más prescriben antihipertensivos y así
##priorizar los grupos que requieren mayor intervención

sqlMedicosPorEspecialidad = """
    SELECT especialidad, 
    COUNT(DISTINCT medico)
    FROM e_consolidado
    WHERE fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31'
        AND tipo_ingreso = '1'
        AND especialidad not REGEXP 'enferme|optome|odontol|endodon|nutric|higienis'
        AND institucion = '1'
    GROUP BY especialidad
    ORDER BY especialidad;"""

totalMedicosPorEspecialidad = pdsql.read_sql(sqlMedicosPorEspecialidad , conexion) 
totalMedicosPorEspecialidad

##Automatizar

##Considerar las siguientes opciones: Agrupar por principales especialidades y otras o
##mostrar el detalle y agrupar las que corresponden a la misma pero cambia denominación
##entre hospitales

##Ahora identificamos la totalidad de pacientes con cada medicamento antihipertensivo

sqlPacientesPorAntihipertensivo = """
     SELECT med_nombre, 
     COUNT(DISTINCT id_paciente) 
     FROM e_consolidado 
     WHERE antihipertensivo NOT LIKE '' 
         AND fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31' 
         AND institucion = '1' 
         AND tipo_ingreso = '1'
    GROUP BY med_nombre;"""

totalPacientesPorAntihipertensivo = pdsql.read_sql(sqlPacientesPorAntihipertensivo , conexion) 
totalPacientesPorAntihipertensivo

##Afinamos la consulta para que nos agrupe por principio activo (ej: total de 
##pacientes con Enalapril, sin considerar concentración) y excluya inyectables
##dado que los medicamentos en esta presentación no son administrados de 
##manera ambulatoria

##1-Extracción de la concentración de la columna medicamento

## Pacientes a quienes se les prescribieron 2 IECA (Captopril y Enalapril) de manera simultanea (en la misma fecha)
PacientesIECA_IECA = """
    SELECT COUNT(DISTINCT ec.id_paciente) 
    FROM e_consolidado AS ec
      JOIN (SELECT id_paciente, fecha_folio_hc 
            FROM e_consolidado 
            WHERE med_nombre LIKE '%capto%') AS t
    ON ec.id_paciente = t.id_paciente AND ec.fecha_folio_hc = t.fecha_folio_hc
    WHERE (ec.med_nombre LIKE '%enalap%' 
          AND ec.fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31' 
          AND ec.tipo_ingreso=1 
          AND ec.institucion=1);"""

TotalPacientesIECA_IECA_Hospital1 = pdsql.read_sql(PacientesIECA_IECA, conexion)
TotalPacientesIECA_IECA_Hospital1 

def pacientesIECA_IECA(n):
    #Parte comun de la consulta
    comun = """
        SELECT COUNT(DISTINCT ec.id_paciente) 
        FROM e_consolidado 
        AS ec
        JOIN (SELECT id_paciente, fecha_folio_hc 
              FROM e_consolidado 
              WHERE med_nombre like '%capto%') 
              AS t

        ON ec.id_paciente = t.id_paciente AND ec.fecha_folio_hc = t.fecha_folio_hc
        WHERE (ec.med_nombre like '%enalap%' AND ec.fecha_folio_hc 
            BETWEEN '2013-10-01' AND '2014-03-31'
            AND ec.tipo_ingreso=1 AND ec.institucion=  """
    #Parte variable de la consulta
    hospital = n
    # Unimos la parte comun y la variable para tener nuestro resultado final
    return comun + str(hospital) + ") ;"

def totalPacientesIECA_IECA_Hospital(n):
    return pdsql.read_sql(pacientesIECA_IECA(n),conexion)

totalPacientesIECA_IECA_Hospital(1)

## Pacientes a quienes se les prescribió 1 IECA (Captopril o Enalapril) y 1 ARA 
##(Losartan o Losartan + Hidroclorotiazida) de manera simultanea (en la misma fecha)

PacientesIECA_ARA0 = """
    SELECT COUNT(DISTINCT ec.id_paciente) 
    FROM e_consolidado AS ec
      JOIN (SELECT id_paciente, fecha_folio_hc 
            FROM e_consolidado 
            WHERE med_nombre REGEXP 'capto|enalap') AS t
    ON ec.id_paciente = t.id_paciente AND ec.fecha_folio_hc = t.fecha_folio_hc
    WHERE (ec.med_nombre REGEXP 'losart' 
          AND ec.fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31' 
          AND ec.tipo_ingreso=1 
          AND ec.institucion=1);"""

totalPacientesIECA_ARA0_Hospital1 = pdsql.read_sql(PacientesIECA_ARA0, conexion)
totalPacientesIECA_ARA0_Hospital1 

## Pacientes a quienes se les prescribió 1 IECA (Captopril) y 1 ARA (Losartan o Losartan + Hidroclorotiazida) de manera simultanea (en la misma fecha)
PacientesIECA_ARA = """
    SELECT COUNT(DISTINCT ec.id_paciente) 
    FROM e_consolidado AS ec
      JOIN (SELECT id_paciente, fecha_folio_hc 
            FROM e_consolidado 
            WHERE med_nombre LIKE '%capto%') AS t
    ON ec.id_paciente = t.id_paciente AND ec.fecha_folio_hc = t.fecha_folio_hc
    WHERE (ec.med_nombre REGEXP 'losart' 
          AND ec.fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31' 
          AND ec.tipo_ingreso=1 
          AND ec.institucion=1);"""

totalPacientesIECA_ARA_Hospital1 = pdsql.read_sql(PacientesIECA_ARA, conexion)
totalPacientesIECA_ARA_Hospital1 

##automatizar

## Pacientes a quienes se les prescribió 1 IECA (Enalapril) y 1 ARA (Losartan o Losartan + Hidroclorotiazida) 
## de manera simultanea (en la misma fecha)

PacientesIECA_ARA2 = """
    SELECT COUNT(DISTINCT ec.id_paciente) 
    FROM e_consolidado AS ec
      JOIN (SELECT id_paciente, fecha_folio_hc 
            FROM e_consolidado 
            WHERE med_nombre LIKE '%enala%') AS t
    ON ec.id_paciente = t.id_paciente AND ec.fecha_folio_hc = t.fecha_folio_hc
    WHERE (ec.med_nombre REGEXP 'losart' 
          AND ec.fecha_folio_hc BETWEEN '2013-10-01' AND '2014-03-31' 
          AND ec.tipo_ingreso=1 
          AND ec.institucion=1);"""

totalPacientesIECA_ARA2_Hospital1 = pdsql.read_sql(PacientesIECA_ARA2, conexion)
totalPacientesIECA_ARA2_Hospital1