VERSION 5.00
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Object = "{6B7E6392-850A-101B-AFC0-4210102A8DA7}#1.3#0"; "comctl32.ocx"
Begin VB.Form Form_informe_talleres 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Informe Talleres"
   ClientHeight    =   2760
   ClientLeft      =   45
   ClientTop       =   375
   ClientWidth     =   3075
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   2760
   ScaleWidth      =   3075
   Begin VB.CommandButton Command1 
      Caption         =   "Generar reporte"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   855
      Left            =   240
      Picture         =   "Form_informe_talleres.frx":0000
      Style           =   1  'Graphical
      TabIndex        =   0
      Top             =   1320
      Width           =   2535
   End
   Begin MSComCtl2.DTPicker dtp_inicio 
      Height          =   375
      Left            =   1320
      TabIndex        =   1
      Top             =   120
      Width           =   1575
      _ExtentX        =   2778
      _ExtentY        =   661
      _Version        =   393216
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Format          =   51642369
      CurrentDate     =   42979
   End
   Begin MSComCtl2.DTPicker dtp_fin 
      Height          =   375
      Left            =   1320
      TabIndex        =   2
      Top             =   720
      Width           =   1575
      _ExtentX        =   2778
      _ExtentY        =   661
      _Version        =   393216
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Format          =   51642369
      CurrentDate     =   43008
   End
   Begin ComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   0
      TabIndex        =   3
      Top             =   2400
      Width           =   3015
      _ExtentX        =   5318
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   End
   Begin VB.Label Label2 
      Caption         =   "Fecha final"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   120
      TabIndex        =   5
      Top             =   720
      Width           =   1215
   End
   Begin VB.Label Label1 
      Caption         =   "Fecha inicial"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   120
      TabIndex        =   4
      Top             =   120
      Width           =   1095
   End
End
Attribute VB_Name = "Form_informe_talleres"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Command1_Click()
    Dim ApExcel As Variant
    Dim monto As Long
    Dim nobecado As Long
    Dim becado As Long
    Dim exencion As Long
    Dim emitidas As Long
    Dim nulas As Long
    Set ApExcel = CreateObject("Excel.application")
    ApExcel.Workbooks.Add
    Dim objRS As ADODB.Recordset
    Set objRS = New ADODB.Recordset
    Dim rec As ADODB.Recordset
    Set rec = New ADODB.Recordset
    
    Dim var_sql As String
'    MsgBox Month(GrillaDtll.TextMatrix(GrillaDtll.Row, 14))                        ' Grilla.TextMatrix(Grilla.Row, 0)        'Text2.Text    Combo1.Text
    pgbar.Visible = True
    
    
    CODBC = DevuelveCampo("cfg", "", "valor", "param = 'CODBC'", conn)
        
    Select Case CODBC
    Case "SNB001"
      colegio = "SANBDO"
    Case "LON001"
      colegio = "LONGITUDINAL"
    Case "FLO001"
      colegio = "LAFLORIDA"
    Case "FAR001"
      colegio = "LAFARFANA"
    Case "HUE001"
      colegio = "HUECHURABA"
    Case "SNM001"
      colegio = "SANMARTIN"
    Case "PUD001"
      colegio = "PUDAHUEL"
    Case "ALT001"
      colegio = "ALTOMACUL"
    End Select
    'Format(Now, "dd/mm/yyyy")
    If (CODBC = "SNB001" Or CODBC = "FLO001" Or CODBC = "SNM001" Or CODBC = "PUD001") And Format(dtp_fin.Value, "yyyy/mm/dd") > Format("30-06-2018", "yyyy/mm/dd") Then
        colegio = "CORPORACIONBC"
    End If
        
    'colegio = "LONGITUDINAL"
    aaa = ConectarSQL(connSQL, "192.168.7.13", colegio, "sa", "passCORPsa.2024", True)
    
    '''''''''''If Option1.Value = True Then
    '''''''Else
'    var_sql = "select  max(gs.solicitadopor),max(gs.glosa),max(gs.codaux),gs.Tipo, YEAR(gs.fecha) as agno, CASE when month(gs.fecha) = 1 then 'ENERO' when month(gs.fecha) = 2 then 'FEBRERO' when month(gs.fecha) = 3 then 'MARZO' when month(gs.fecha) = 4 then 'ABRIL' when month(gs.fecha) = 5 then 'MAYO' when month(gs.fecha) = 6 then 'JUNIO'" _
'            & "when month(gs.fecha) = 7 then 'JULIO' when month(gs.fecha) = 8 then 'AGOSTO' when month(gs.fecha) = 9 then 'SEPTIEMBRE' when month(gs.fecha) = 10 then 'OCTUBRE' when month(gs.fecha) = 11 then 'NOVIEMBRE'" _
'            & "when month(gs.fecha) = 12 then 'DICIEMBRE' end as mes,DAY(gs.fecha) as dia,gs.CodBode Bodega,MIN(gs.folio) folio_desde ,Max(gs.folio) folio_hasta," _
'            & "(SUM(case when gs.estado = 'V' then 1 else 0 end) / 5) as emitidas,(SUM(case when gs.estado = 'N' then 1 else 0 end) / 5) as nulas," _
'            & "sum(gm.TotLinea) as monto,sum(case when gm.TotalDescMov = 0 then gm.totlinea else 0 END) as nobecado," _
'            & "sum(case when gm.TotalDescMov > 0 then gm.totlinea else 0 END) as becado,SUM(gm.TotalDescMov) As exencion" _
'            & " from softland.iw_gsaen gs,softland.iw_gmovi as gm" _
'            & " where gs.nroint = gm.nroint and gs.fecha >= '" & dtp_inicio.Value & "' and gs.fecha <= '" & dtp_fin.Value & "'" _
'            & " and gs.tipo = gm.tipo and gs.tipo = 'B' group by gs.Tipo,gs.folio,gs.fecha,gs.CodBode" _
'            & " order by gs.fecha,gs.folio"
'    '''''''''End If
    
    var_sql = "SELECT gs.solicitadopor as Academia,SUBSTRING(gs.glosa,1,(charindex('Curso',gs.glosa))-3) As Alumno," _
            & "SUBSTRING(gs.glosa,(charindex('Curso',gs.glosa)),100) As Curso,gs.codaux as Rut, YEAR(gs.fecha) AS agno," _
            & "CASE WHEN MONTH(gs.fecha) = 1 THEN 'ENERO'" _
            & "WHEN MONTH(gs.fecha) = 2 THEN 'FEBRERO' WHEN MONTH(gs.fecha) = 3 THEN 'MARZO'" _
            & "WHEN MONTH(gs.fecha) = 4 THEN 'ABRIL' WHEN MONTH(gs.fecha) = 5 THEN 'MAYO' WHEN MONTH(gs.fecha) = 6 THEN 'JUNIO'WHEN MONTH(gs.fecha) = 7 THEN 'JULIO'" _
            & "WHEN MONTH(gs.fecha) = 8 THEN 'AGOSTO' WHEN MONTH(gs.fecha) = 9 THEN 'SEPTIEMBRE' WHEN MONTH(gs.fecha) = 10 THEN 'OCTUBRE' WHEN MONTH(gs.fecha) = 11" _
            & "THEN 'NOVIEMBRE'WHEN MONTH(gs.fecha) = 12 THEN 'DICIEMBRE' END AS mes,DAY(gs.fecha) AS dia,gs.CodBode Bodega,gs.folio,total," _
            & "CASE when CondPago = '101' then 'CONTADO' when CondPago = '102' then 'CHEQUE'" _
            & "when CondPago = '103' then 'IBR' when CondPago = '104' then 'BCI' when CondPago = '105' then 'TARJETA DEBITO'" _
            & "when CondPago = '106' then 'TARJETA CREDITO'" _
            & "when CondPago = '107' then 'PAT' when CondPago = '108' then 'TRANSFERENCIA' END" _
            & " FROM huechuraba.softland.iw_gsaen gs,huechuraba.softland.iw_gmovi AS gm WHERE gs.nroint = gm.nroint" _
            & " AND gs.fecha >= '" & dtp_inicio.Value & "' AND gs.fecha <= '" & dtp_fin.Value & "'" _
            & " AND gs.tipo = gm.tipo AND gs.tipo = 'B' and (codprod = 'B4' and preUniMB > 0)" _
            & " ORDER BY gs.fecha,gs.folio"
    
    
    
    objRS.Open var_sql, connSQL
    If objRS.RecordCount > 0 Then
                  
        For I = 1 To 13
            ApExcel.Cells(1, I).Font.Size = 12
            ApExcel.Cells(1, I).Font.Bold = True
            'ApExcel.Cells(1, i).Formula = Me.MSFlexGrid1.TextMatrix(0, i)
        Next I
        ApExcel.Cells(1, 1).Formula = "Academia"
        ApExcel.Cells(1, 2).Formula = "Alumno"
        ApExcel.Cells(1, 3).Formula = "Curso"
        ApExcel.Cells(1, 4).Formula = "Rut Apoderado"
        ApExcel.Cells(1, 5).Formula = "Año"
        ApExcel.Cells(1, 6).Formula = "Mes"
        ApExcel.Cells(1, 7).Formula = "Dia"
        ApExcel.Cells(1, 8).Formula = "Bodega"
        ApExcel.Cells(1, 9).Formula = "Folio"
        ApExcel.Cells(1, 10).Formula = "Monto"
        ApExcel.Cells(1, 11).Formula = "Forma Pago"
       
                
        pgbar.Min = 1
        pgbar.Max = objRS.RecordCount
        pgbar.Value = pgbar.Min
        I = 1
        monto = 0
        nobecado = 0
        becado = 0
        exencion = 0
        While Not objRS.EOF
            I = I + 1
            For j = 0 To 10
                ApExcel.Cells(I, j + 1).Formula = objRS(j)
            Next j
'            ApExcel.Cells(i, 8).NumberFormat = "#,##0"
'            ApExcel.Cells(i, 9).NumberFormat = "#,##0"
            ApExcel.Cells(I, 10).NumberFormat = "#,##0"
'            ApExcel.Cells(i, 11).NumberFormat = "#,##0"
'            ApExcel.Cells(i, 12).NumberFormat = "#,##0"
'            ApExcel.Cells(i, 13).NumberFormat = "#,##0"

'            emitidas = emitidas + objRS(7)
'            nulas = nulas + objRS(8)
            monto = monto + objRS(9)
 '           nobecado = nobecado + objRS(10)
            'becado = becado + objRS(11)
            'exencion = exencion + objRS(12)
            
            pgbar.Value = I - 1
            'ApExcel.Cells(i , j).Formula =
            objRS.MoveNext
        Wend
        objRS.Close
                
''''''        '' Nota Credito
''''''        If Option1.Value = True Then
''''''        var_sql = "select 'Nota Crédito' as tipo,year(gs.fecha) as agno,CASE when month(gs.fecha) = 1 then 'ENERO' when month(gs.fecha) = 2 then 'FEBRERO' when month(gs.fecha) = 3 then 'MARZO' when month(gs.fecha) = 4 then 'ABRIL' when month(gs.fecha) = 5 then 'MAYO' when month(gs.fecha) = 6 then 'JUNIO'" _
''''''                & "when month(gs.fecha) = 7 then 'JULIO' when month(gs.fecha) = 8 then 'AGOSTO' when month(gs.fecha) = 9 then 'SEPTIEMBRE' when month(gs.fecha) = 10 then 'OCTUBRE' when month(gs.fecha) = 11 then 'NOVIEMBRE'" _
''''''                & "when month(gs.fecha) = 12 then 'DICIEMBRE' end as mes,DAY(gs.fecha) as dia,gs.CodBode Bodega,MIN(gs.folio) folio_desde ,Max(gs.folio) folio_hasta," _
''''''                & "isnull((select COUNT(distinct g.folio) From softland.iw_gsaen g Where g.fecha = gs.fecha and g.tipo = 'N' and g.estado = 'V'" _
''''''                & " group by g.fecha),0) as emitidas,isnull((select COUNT(distinct g.folio) From softland.iw_gsaen g Where" _
''''''                & " g.fecha = gs.fecha and g.tipo = 'N' and g.estado = 'N'" _
''''''                & " group by g.fecha),0) as nulas,sum(gm.TotLinea) as monto,0 as nobecado,0 as becado,SUM(gm.TotalDescMov) As excencion" _
''''''                & " from softland.iw_gsaen gs,softland.iw_gmovi as gm" _
''''''                & " where gs.nroint = gm.nroint and gs.fecha >= '" & dtp_inicio.Value & "' and gs.fecha <= '" & dtp_fin.Value & "'" _
''''''                & " and gs.tipo = gm.tipo and gs.tipo = 'N'" _
''''''                & " group by gs.Tipo,gs.fecha,gs.CodBode"
''''''        Else
''''''        var_sql = "select gs.Tipo,year(gs.fecha) as agno,CASE when month(gs.fecha) = 1 then 'ENERO' when month(gs.fecha) = 2 then 'FEBRERO' when month(gs.fecha) = 3 then 'MARZO'" _
''''''                & " when month(gs.fecha) = 4 then 'ABRIL' when month(gs.fecha) = 5 then 'MAYO' when month(gs.fecha) = 6 then 'JUNIO'" _
''''''                & " when month(gs.fecha) = 7 then 'JULIO' when month(gs.fecha) = 8 then 'AGOSTO' when month(gs.fecha) = 9 then 'SEPTIEMBRE' when month(gs.fecha) = 10" _
''''''                & " then 'OCTUBRE' when month(gs.fecha) = 11 then 'NOVIEMBRE'" _
''''''                & " when month(gs.fecha) = 12 then 'DICIEMBRE' end as mes,DAY(gs.fecha) as dia,gs.CodBode Bodega,MIN(gs.folio) folio_desde ,Max(gs.folio) folio_hasta," _
''''''                & "isnull(( select COUNT(distinct g.folio) From softland.iw_gsaen g Where g.folio = gs.folio and g.tipo = 'N' and g.estado = 'V' group by g.folio" _
''''''                & "),0) as emitidas,isnull(( select COUNT(distinct g.folio) From softland.iw_gsaen g Where g.folio = gs.folio and g.tipo = 'N' and g.estado = 'N'" _
''''''                & " group by g.folio),0) as nulas,sum(gm.TotLinea) as monto,0 as nobecado,0 as becado,SUM(gm.TotalDescMov) As excencion" _
''''''                & " from softland.iw_gsaen gs,softland.iw_gmovi as gm where gs.nroint = gm.nroint and gs.fecha >= '" & dtp_inicio.Value & "' and gs.fecha <= '" & dtp_fin.Value & "'" _
''''''                & " and gs.tipo = gm.tipo and gs.tipo = 'N' group by gs.Tipo,gs.folio,gs.CodBode,gs.fecha"
''''''        End If
''''''        rec.Open var_sql, connSQL
''''''        If rec.RecordCount > 0 Then
''''''
''''''        While Not rec.EOF
''''''            i = i + 1
''''''            For j = 0 To 12
''''''                ApExcel.Cells(i, j + 1).Formula = rec(j)
''''''            Next j
''''''            emitidas = emitidas + rec(7)
''''''            nulas = nulas + rec(8)
''''''            monto = monto + rec(9)
''''''            nobecado = nobecado + rec(10)
''''''            becado = becado + rec(11)
''''''            exencion = exencion + rec(12)
''''''
''''''            'pgbar.Value = i - 1
''''''            'ApExcel.Cells(i , j).Formula =
''''''            rec.MoveNext
''''''        Wend
''''''        rec.Close
''''''        End If
        
        I = I + 1
'        ApExcel.Cells(i, 8).Formula = emitidas
'        ApExcel.Cells(i, 9).Formula = nulas
        ApExcel.Cells(I, 10).Formula = monto
'        ApExcel.Cells(i, 11).Formula = nobecado
'        ApExcel.Cells(i, 12).Formula = becado
'        ApExcel.Cells(i, 13).Formula = exencion
        ApExcel.Cells(I, 1).Formula = "Totales"
'        ApExcel.Cells(i, 8).NumberFormat = "#,##0"
'        ApExcel.Cells(i, 9).NumberFormat = "#,##0"
        ApExcel.Cells(I, 10).NumberFormat = "#,##0"
'        ApExcel.Cells(i, 11).NumberFormat = "#,##0"
'        ApExcel.Cells(i, 12).NumberFormat = "#,##0"
'        ApExcel.Cells(i, 13).NumberFormat = "#,##0"
        ApExcel.Cells(I, 1).Font.Bold = True
        ApExcel.Cells(I, 8).Font.Bold = True
        ApExcel.Cells(I, 9).Font.Bold = True
        ApExcel.Cells(I, 10).Font.Bold = True
        ApExcel.Cells(I, 11).Font.Bold = True
        ApExcel.Cells(I, 12).Font.Bold = True
        ApExcel.Cells(I, 13).Font.Bold = True
            
        ApExcel.Visible = True
        'ApExcel.Range("a1:x1").Borders.color = RGB(255, 0, 0)
        Set ApExcel = Nothing
    
    End If
    connSQL.Close
    pgbar.Visible = False
End Sub

Private Sub Form_Load()
    dtp_inicio.Value = Date
    dtp_fin.Value = Date
    
End Sub


