Attribute VB_Name = "Mod_Funciones_AsigBecas"
Public Function Puntaje_JefeHogar(ByVal JefeHogar As String, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Long
    ' Funcion
    ' Tabla de Referencia: becas_jefehogar
    Dim QRY As String
    Dim REC As ADODB.Recordset
    
    Puntaje_JefeHogar = 0
    QRY = "SELECT * FROM becas_jefehogar WHERE jefehogar='" & JefeHogar & "' and agno=" & agno & ""
    Set REC = New ADODB.Recordset
    REC.Open QRY, CNX
    If REC.RecordCount > 0 Then
        REC.MoveFirst
        Puntaje_JefeHogar = Val(REC!puntaje)
    End If
    REC.Close
    
End Function

Public Function Puntaje_Vivienda(ByVal Vivienda As String, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Long
    ' Funcion
    ' Tabla de Referencia: becas_vivienda
    Dim QRY As String
    Dim REC As ADODB.Recordset
    
    Puntaje_Vivienda = 0
    QRY = "SELECT * FROM becas_vivienda WHERE vivienda='" & Vivienda & "' and agno=" & agno & ";"
    Set REC = New ADODB.Recordset
    REC.Open QRY, CNX
    If REC.RecordCount > 0 Then
        REC.MoveFirst
        Puntaje_Vivienda = Val(REC!puntaje)
    End If
    REC.Close
    
End Function

Public Function Puntaje_EnfermedadCatastrofica(ByVal Enfermo As String, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Long
    ' Funcion
    ' Tabla de Referencia: becas_enfermedadcatastrofica
    Dim QRY As String
    Dim REC As ADODB.Recordset
    
    Puntaje_EnfermedadCatastrofica = 0
    QRY = "SELECT * FROM becas_EnfermedadCatastrofica WHERE enfermedad='" & Enfermo & "' and agno=" & agno & ";"
    Set REC = New ADODB.Recordset
    REC.Open QRY, CNX
    If REC.RecordCount > 0 Then
        REC.MoveFirst
        Puntaje_EnfermedadCatastrofica = Val(REC!puntaje)
    End If
    REC.Close
End Function

Public Function Puntaje_SituacionLaboral(ByVal Situacion As String, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Long
    ' Funcion
    ' Tabla de Referencia: becas_situacionlaboral
    Dim QRY As String
    Dim REC As ADODB.Recordset
    
    Puntaje_SituacionLaboral = 0
    QRY = "SELECT * FROM becas_situacionlaboral WHERE situacion='" & Situacion & "' and agno=" & agno & ";"
    Set REC = New ADODB.Recordset
    REC.Open QRY, CNX
    If REC.RecordCount > 0 Then
        REC.MoveFirst
        Puntaje_SituacionLaboral = Val(REC!puntaje)
    End If
    REC.Close

End Function

Public Function Puntaje_EstudiosHermanos(ByVal Ingreso As Long, ByVal NumPersonasFamilia As Integer, ByVal GastoHermanos As Long, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Long
    ' Funcion
    ' Tabla de Referencia: becas_estudio_hermanos
    Dim Ingr_Per_Capita As Long
    Dim QRY As String
    Dim REC As ADODB.Recordset
    Dim Xc As Long
    Dim Yc As Long
    Dim Xu As Long
    Dim Yu As Long
    Dim PE As Double
    Dim Ka As Double
    
    Ingr_Per_Capita = Ingreso \ NumPersonasFamilia
    Puntaje_EstudiosHermanos = 0
    
    Set REC = New ADODB.Recordset
    
    Xc = 0: Xu = 0: Yc = 0: Yu = 0
    QRY = "SELECT * FROM becas_estudio_hermanos WHERE  agno=" & agno & " ORDER BY gasto;"
    REC.Open QRY, CNX
    If (REC.RecordCount > 0 And Ingr_Per_Capita > 0) Then
        REC.MoveFirst
        For i = 1 To REC.RecordCount
            If GastoHermanos >= Val(REC!gasto) Then
                Xc = Val(REC!gasto)
                Yc = Val(REC!puntaje)
            Else
                Xu = Val(REC!gasto)
                Yu = Val(REC!puntaje)
                i = REC.RecordCount
            End If
            REC.MoveNext
        Next i
        If Not (Xu = 0 And Yu = 0) Then
            PE = (Yc - Yu) / (Xc - Xu)
            Ka = Yc - (PE * Xc)
            Puntaje_EstudiosHermanos = ((PE * GastoHermanos) + Ka) \ 1
        Else
            Puntaje_EstudiosHermanos = Yc
        End If
    End If
    
    If GastoHermanos > (2 * Ingr_Per_Capita) And GastoHermanos < (Ingreso * 0.75) Then
        Puntaje_EstudiosHermanos = (Puntaje_EstudiosHermanos * (GastoHermanos / (2 * Ingr_Per_Capita))) \ 1
    End If
    If GastoHermanos > (Ingreso * 0.75) Then
        Puntaje_EstudiosHermanos = (Puntaje_EstudiosHermanos * ((Ingreso * 0.75) / GastoHermanos)) \ 1
    End If
    REC.Close
    
End Function

Public Function Puntaje_Notas(ByVal Promedio As Double, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Long
    ' Funcion
    ' Tabla de Referencia: becas_promedio
    Dim QRY As String
    Dim REC As ADODB.Recordset
    Dim Xc As Double
    Dim Yc As Double
    Dim Xu As Double
    Dim Yu As Double
    Dim PE As Double
    Dim Ka As Double
    
    Puntaje_Notas = 0
    Set REC = New ADODB.Recordset
    
    Xc = 0: Xu = 0: Yc = 0: Yu = 0
    QRY = "SELECT * FROM becas_promedio WHERE  agno=" & agno & " ORDER BY promedio;"
    REC.Open QRY, CNX
    If (REC.RecordCount > 0 And Promedio >= 1) Then
        REC.MoveFirst
        For i = 1 To REC.RecordCount
            If Promedio >= Val(REC!Promedio) Then
                Xc = Val(REC!Promedio)
                Yc = Val(REC!puntaje)
            Else
                Xu = Val(REC!Promedio)
                Yu = Val(REC!puntaje)
                i = REC.RecordCount
            End If
            REC.MoveNext
        Next i
        If Not (Xu = 0 And Yu = 0) Then
            PE = (Yc - Yu) / (Xc - Xu)
            Ka = Yc - (PE * Xc)
            Puntaje_Notas = ((PE * Promedio) + Ka) \ 1
        Else
            Puntaje_Notas = Yc
        End If
    End If
    REC.Close
    
End Function

Public Function Puntaje_ArrDiv(ByVal IngresoFamiliar As Long, ByVal MontoArrDiv As Long, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Long
    ' Funcion
    ' Tabla de Referencia: becas_ad_monto
    ' Tabla de Referencia: becas_ad_porcentaje
    Dim QRY As String
    Dim REC As ADODB.Recordset
    Dim Xc As Double
    Dim Yc As Double
    Dim Xu As Double
    Dim Yu As Double
    Dim PE As Double
    Dim Ka As Double
    Dim Puntaje_Monto As Double
    Dim Puntaje_Porcentaje As Double
    
    Puntaje_ArrDiv = 0
    Puntaje_Monto = 0
    Puntaje_Porcentaje = 0
    
    Xc = 0: Xu = 0: Yc = 0: Yu = 0
    QRY = "SELECT * FROM becas_ad_monto WHERE  agno=" & agno & " ORDER BY monto;"
    Set REC = New ADODB.Recordset
    REC.Open QRY, CNX
    If (REC.RecordCount > 0 And MontoArrDiv >= 1) Then
        REC.MoveFirst
        For i = 1 To REC.RecordCount
            If MontoArrDiv >= Val(REC!Monto) Then
                Xc = Val(REC!Monto)
                Yc = Val(REC!puntaje)
            Else
                Xu = Val(REC!Monto)
                Yu = Val(REC!puntaje)
                i = REC.RecordCount
            End If
            REC.MoveNext
        Next i
        If Not (Xu = 0 And Yu = 0) Then
            PE = (Yu - Yc) / (Xu - Xc)
            Ka = Yu - (PE * Xu)
            Puntaje_Monto = ((PE * MontoArrDiv) + Ka) \ 1
        Else
            Puntaje_Monto = Yc
        End If
    End If
    REC.Close
    
    Xc = 0: Xu = 0: Yc = 0: Yu = 0
    QRY = "SELECT * FROM becas_ad_porcentaje WHERE  agno=" & agno & " ORDER BY porcentaje;"
    Set REC = New ADODB.Recordset
    REC.Open QRY, CNX
    If (REC.RecordCount > 0 And MontoArrDiv >= 1) Then
        REC.MoveFirst
        For i = 1 To REC.RecordCount
            If ((MontoArrDiv / IngresoFamiliar) * 100) >= Val(REC!Porcentaje) Then
                Xc = Val(REC!Porcentaje)
                Yc = Val(REC!puntaje)
            Else
                Xu = Val(REC!Porcentaje)
                Yu = Val(REC!puntaje)
                i = REC.RecordCount
            End If
            REC.MoveNext
        Next i
        If Not (Xu = 0 And Yu = 0) Then
            PE = (Yc - Yu) / (Xc - Xu)
            Ka = Yc - (PE * Xc)
            Puntaje_Porcentaje = ((PE * ((MontoArrDiv / IngresoFamiliar) * 100)) + Ka) \ 1
        Else
            Puntaje_Porcentaje = Yc
        End If
    End If
    REC.Close
    
    Puntaje_ArrDiv = ((Puntaje_Monto * 0.3) + (Puntaje_Porcentaje * 0.7)) \ 1
    
End Function

Public Function Puntaje_EnfCatGasto(ByVal IngresoFamiliar As Long, ByVal MontoEnfCat As Long, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Long
    ' Funcion
    ' Tabla de Referencia: becas_ec_monto
    ' Tabla de Referencia: becas_ec_porcentaje
    Dim QRY As String
    Dim REC As ADODB.Recordset
    Dim Xc As Double
    Dim Yc As Double
    Dim Xu As Double
    Dim Yu As Double
    Dim PE As Double
    Dim Ka As Double
    Dim Puntaje_Monto As Double
    Dim Puntaje_Porcentaje As Double
    
    Puntaje_EnfCatGasto = 0
    Puntaje_Monto = 0
    Puntaje_Porcentaje = 0
    
    Xc = 0: Xu = 0: Yc = 0: Yu = 0
    QRY = "SELECT * FROM becas_ec_monto WHERE  agno=" & agno & " ORDER BY monto;"
    Set REC = New ADODB.Recordset
    REC.Open QRY, CNX
    If (REC.RecordCount > 0 And MontoEnfCat >= 1) Then
        REC.MoveFirst
        For i = 1 To REC.RecordCount
            If MontoEnfCat >= Val(REC!Monto) Then
                Xc = Val(REC!Monto)
                Yc = Val(REC!puntaje)
            Else
                Xu = Val(REC!Monto)
                Yu = Val(REC!puntaje)
                i = REC.RecordCount
            End If
            REC.MoveNext
        Next i
        If Not (Xu = 0 And Yu = 0) Then
            PE = (Yu - Yc) / (Xu - Xc)
            Ka = Yu - (PE * Xu)
            Puntaje_Monto = ((PE * MontoEnfCat) + Ka) \ 1
        Else
            Puntaje_Monto = Yc
        End If
    End If
    REC.Close
    
    Xc = 0: Xu = 0: Yc = 0: Yu = 0
    QRY = "SELECT * FROM becas_ec_porcentaje WHERE  agno=" & agno & " ORDER BY porcentaje;"
    Set REC = New ADODB.Recordset
    REC.Open QRY, CNX
    If (REC.RecordCount > 0 And MontoEnfCat >= 1) Then
        REC.MoveFirst
        For i = 1 To REC.RecordCount
            If ((MontoEnfCat / IngresoFamiliar) * 100) >= Val(REC!Porcentaje) Then
                Xc = Val(REC!Porcentaje)
                Yc = Val(REC!puntaje)
            Else
                Xu = Val(REC!Porcentaje)
                Yu = Val(REC!puntaje)
                i = REC.RecordCount
            End If
            REC.MoveNext
        Next i
        If Not (Xu = 0 And Yu = 0) Then
            PE = (Yc - Yu) / (Xc - Xu)
            Ka = Yc - (PE * Xc)
            Puntaje_Porcentaje = ((PE * ((MontoEnfCat / IngresoFamiliar) * 100)) + Ka) \ 1
        Else
            Puntaje_Porcentaje = Yc
        End If
    End If
    REC.Close
    
    Puntaje_EnfCatGasto = ((Puntaje_Monto * 0.3) + (Puntaje_Porcentaje * 0.7)) \ 1
    
End Function

Public Function Puntaje_IngresoFamiliar(ByVal Ingreso As Long, ByVal NumPersonasFamilia As Integer, ByVal agno As Integer, ByRef CNX As ADODB.Connection) As Long
    ' Funcion
    ' Tabla de Referencia: becas_ingreso_pc
    Dim Ingr_Per_Capita As Double
    Dim QRY As String
    Dim REC As ADODB.Recordset
    Dim Xc As Long
    Dim Yc As Long
    Dim Xu As Long
    Dim Yu As Long
    Dim PE As Double
    Dim Ka As Double
    
    NumPersonasFamilia = IIf(NumPersonasFamilia <= 0, 1, NumPersonasFamilia)
    Ingr_Per_Capita = Ingreso / NumPersonasFamilia
    
    Puntaje_IngresoFamiliar = 0
    Set REC = New ADODB.Recordset
    
    Xc = 0: Xu = 0: Yc = 0: Yu = 0
    QRY = "SELECT * FROM becas_ingreso_pc WHERE  agno=" & agno & " ORDER BY ingreso_pc;"
    REC.Open QRY, CNX
    If (REC.RecordCount > 0 And Ingr_Per_Capita >= 0) Then
        REC.MoveFirst
        For i = 1 To REC.RecordCount
            If Ingr_Per_Capita >= Val(REC!ingreso_pc) Then
                Xc = Val(REC!ingreso_pc)
                Yc = Val(REC!puntaje)
            Else
                Xu = Val(REC!ingreso_pc)
                Yu = Val(REC!puntaje)
                i = REC.RecordCount
            End If
            REC.MoveNext
        Next i
        If Not (Xu = 0 And Yu = 0) Then
            PE = (Yc - Yu) / (Xc - Xu)
            Ka = Yc - (PE * Xc)
            Puntaje_IngresoFamiliar = ((PE * Ingr_Per_Capita) + Ka) \ 1
        Else
            Puntaje_IngresoFamiliar = Yc
        End If
    End If
    REC.Close
    
End Function
