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_libro_de_ventas_softland 
   AutoRedraw      =   -1  'True
   BorderStyle     =   1  'Fixed Single
   Caption         =   "<"
   ClientHeight    =   4320
   ClientLeft      =   45
   ClientTop       =   375
   ClientWidth     =   3225
   Icon            =   "Form_libro_de_ventas_softland.frx":0000
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   4320
   ScaleWidth      =   3225
   Begin VB.OptionButton Option3 
      Caption         =   "Visualización Contablilidad"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   240
      TabIndex        =   8
      Top             =   2280
      Width           =   2415
   End
   Begin VB.OptionButton Option2 
      Caption         =   "Visualizar boletas una a una"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   195
      Left            =   240
      TabIndex        =   7
      Top             =   1920
      Width           =   2535
   End
   Begin VB.OptionButton Option1 
      Caption         =   "Visualizar boletas por rango"
      BeginProperty Font 
         Name            =   "Arial"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   240
      TabIndex        =   6
      Top             =   1440
      Value           =   -1  'True
      Width           =   2535
   End
   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            =   360
      Picture         =   "Form_libro_de_ventas_softland.frx":2A8B2
      Style           =   1  'Graphical
      TabIndex        =   0
      Top             =   2760
      Width           =   2535
   End
   Begin MSComCtl2.DTPicker dtp_inicio 
      Height          =   375
      Left            =   1440
      TabIndex        =   1
      Top             =   240
      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          =   52756481
      CurrentDate     =   42979
   End
   Begin MSComCtl2.DTPicker dtp_fin 
      Height          =   375
      Left            =   1440
      TabIndex        =   2
      Top             =   840
      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          =   52756481
      CurrentDate     =   43008
   End
   Begin ComctlLib.ProgressBar pgbar 
      Height          =   255
      Left            =   120
      TabIndex        =   5
      Top             =   3840
      Width           =   3015
      _ExtentX        =   5318
      _ExtentY        =   450
      _Version        =   327682
      Appearance      =   1
   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            =   240
      TabIndex        =   4
      Top             =   240
      Width           =   1095
   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            =   240
      TabIndex        =   3
      Top             =   840
      Width           =   1215
   End
End
Attribute VB_Name = "Form_libro_de_ventas_softland"
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 colegios(8) As String
    Dim conecta As Boolean
    
    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
    
    var_sql = "select 'Boleta' 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," _
            & "(SUM(case when gs.estado = 'V' and codprod = 'B3' then 1 else 0 end)) as emitidas," _
            & "(SUM(case when gs.estado = 'N' and codprod = 'B3' then 1 else 0 end)) 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.fecha,gs.CodBode" _
            & " order by gs.fecha,gs.CodBode"
    Else
        If Option2.Value = True Then
            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," _
                    & "(SUM(case when gs.estado = 'V' and codprod = 'B3' then 1 else 0 end)) as emitidas,(SUM(case when gs.estado = 'N' and codprod = 'B3' then 1 else 0 end)) 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"
        Else
            var_sql = "DROP TABLE IF EXISTS temp_boletas_libro"
            conn.Execute var_sql
            
            var_sql = "CREATE TABLE `temp_boletas_libro`(" _
                    & "`area` varchar(30) default NULL," _
                    & "`fecha` date default NULL," _
                    & "`folio` int(10) NOT NULL default '0'," _
                    & "`rut` varchar(10) collate latin1_spanish_ci default NULL," _
                    & "`apoderado` varchar(50) collate latin1_spanish_ci default NULL," _
                    & "`monto` int(10) default NULL," _
                    & "`nobecado` int(10) default NULL," _
                    & "`becado` int(10) default NULL," _
                    & "`exencion` int(10) default NULL," _
                    & "`forma_pago` varchar(30) default NULL," _
                    & "PRIMARY KEY  (`folio`)" _
                    & ") ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;"
            conn.Execute var_sql
            
            var_sql = "select gs.fecha,gs.folio folio,MIN(gs.codaux) AS Rut,MIN(t.nomaux) AS Apoderado,min(gs.codaux) as Rut,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,MIN(Cv.CveDes) AS forma_pago" _
                    & " FROM softland.iw_gsaen gs,softland.iw_gmovi AS gm,softland.cwtconv cv," _
                    & " (SELECT MIN(CodAux) AS cod_auxiliar,MIN(nomaux) AS nomaux FROM softland.iw_gsaen_tauxi GROUP BY codaux) AS  t" _
                    & " where gs.nroint = gm.nroint and gs.fecha >= '" & dtp_inicio.Value & "' and gs.fecha <= '" & dtp_fin.Value & "' and gs.tipo = gm.tipo" _
                    & " AND gs.CondPago = cv.CveCod and gs.tipo = 'B' AND gs.codaux = t.cod_auxiliar " _
                    & " group by gs.Tipo,gs.folio,gs.fecha,gs.CodBode order by gs.fecha,gs.folio"
            objRS.Open var_sql, connSQL
            
            While Not objRS.EOF
                var_sql = "insert into `temp_boletas_libro` (`fecha`, `folio`, `rut`,`apoderado`, `monto`, `nobecado`, `becado`, `exencion`,`forma_pago`)" _
                        & " values('" & Format(objRS("fecha"), "yyyy-mm-dd") & "','" & objRS("folio") & "','" & objRS("rut") & "','" & objRS("apoderado") & "','" & objRS("monto") _
                        & "','" & objRS("nobecado") & "','" & objRS("becado") & "','" & objRS("exencion") & "','" & objRS("forma_pago") & "');"
                conn.Execute var_sql
                objRS.MoveNext
            Wend
            objRS.Close
                            
            var_sql = "select gs.fecha,gs.folio folio,MIN(gs.codaux) AS Rut,MIN(t.nomaux) AS Apoderado,min(gs.codaux) as Rut,sum(gm.TotLinea) as monto," _
                    & "0 as nobecado,0 as becado,0 As exencion,MIN(Cv.CveDes) AS forma_pago" _
                    & " FROM softland.iw_gsaen gs,softland.iw_gmovi AS gm,softland.cwtconv cv," _
                    & " (SELECT MIN(CodAux) AS cod_auxiliar,MIN(nomaux) AS nomaux FROM softland.iw_gsaen_tauxi GROUP BY codaux) AS  t" _
                    & " 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' AND gs.CondPago = cv.CveCod AND gs.codaux = t.cod_auxiliar" _
                    & " group by gs.Tipo,gs.folio,gs.fecha,gs.CodBode order by gs.fecha,gs.folio"
            objRS.Open var_sql, connSQL
                    
            While Not objRS.EOF
                var_sql = "insert into `temp_boletas_libro` (`fecha`, `folio`, `rut`,`apoderado`, `monto`, `nobecado`, `becado`, `exencion`,`forma_pago`)" _
                        & " values('" & Format(objRS("fecha"), "yyyy/mm/dd") & "','" & objRS("folio") & "','" & objRS("rut") & "','" & objRS("apoderado") & "','" & objRS("monto") _
                        & "','" & objRS("nobecado") & "','" & objRS("becado") & "','" & objRS("exencion") & "','" & objRS("forma_pago") & "');"
                conn.Execute var_sql
                objRS.MoveNext
            Wend
            objRS.Close
            ' Format(Now, "dd/mm/yyyy")
            
            colegios(1) = "sanma_01"
            colegios(2) = "pudah_01"
            colegios(3) = "sanbe_01"
            colegios(4) = "laflo_01"
            colegios(5) = "huech_01"
            
            
            var_sql = "DROP TABLE IF EXISTS temp_colegio;"
            conn.Execute var_sql
            
            var_sql = "CREATE TABLE `temp_colegio` (" _
                    & "`folio` bigint(20) unsigned NOT NULL default '0'," _
                    & "`AREA` varchar(30) collate latin1_spanish_ci default NULL" _
                    & ") ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;"
            conn.Execute var_sql
            
            var_sql = "DROP TABLE IF EXISTS temp_colegio_fp;"
            conn.Execute var_sql
            
            var_sql = "CREATE TABLE `temp_colegio_fp` (" _
                    & "`folio` bigint(20) unsigned NOT NULL default '0'," _
                    & "`forma_pago` varchar(30) collate latin1_spanish_ci default NULL" _
                    & ") ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;"
            conn.Execute var_sql
            
            var_sql = "DROP TABLE IF EXISTS temp_colegio_nc;"
            conn.Execute var_sql
            
            var_sql = "CREATE TABLE `temp_colegio_nc` (" _
                    & "`folio` bigint(20) unsigned NOT NULL default '0'," _
                    & "`area` varchar(30) collate latin1_spanish_ci default NULL" _
                    & ") ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;"
            conn.Execute var_sql
            
            
            For j = 1 To 5
                conecta = ConectarBDSiempre(conn_deuda, "192.168.7.91", colegios(j), "groot", "kripplefight", True)
                
                '' colegio
                var_sql = " SELECT folio_blta_fisico AS folio,localidad_est AS AREA FROM fnza_boletas b,establecimientos" _
                        & " WHERE fecha_blta >='" & Format(dtp_inicio.Value, "yyyy-mm-dd") & "' AND fecha_blta <= '" & Format(dtp_fin.Value, "yyyy-mm-dd") & "' AND tipo_blta = 3;"
                rec.Open var_sql, conn_deuda
                
                While Not rec.EOF
                    var_sql = "insert into temp_colegio(folio,area) values('" & rec("folio") & "','" & rec("area") & "')"
                    conn.Execute var_sql
                    rec.MoveNext
                Wend
                rec.Close
                
                '' forma pago
                var_sql = " SELECT folio_blta_fisico AS folio,descripcion AS forma_pago" _
                        & " FROM fnza_boletas b,fnza_dtll_mvto n,fnza_movimientos_mensualidad m,fnza_cod_forma_pago fp" _
                        & " WHERE b.folio_blta = n.folio_blta AND n.folio_mvto = m.folio_mvto AND fecha_blta >= '" & Format(dtp_inicio.Value, "yyyy-mm-dd") & "'" _
                        & " AND fecha_blta <= '" & Format(dtp_fin.Value, "yyyy-mm-dd") & "' AND m.tipo_pago = fp.forma_pago GROUP BY folio_blta_fisico"
                rec.Open var_sql, conn_deuda
                
                While Not rec.EOF
                    var_sql = "insert into temp_colegio_fp(folio,forma_pago) values('" & rec("folio") & "','" & rec("forma_pago") & "')"
                    conn.Execute var_sql
                    rec.MoveNext
                Wend
                rec.Close
                
                '' notas credito
                var_sql = "SELECT folio_fisico_ntcr AS folio,localidad_est as area FROM fnza_ntcr b,establecimientos" _
                        & " WHERE fecha_ntcr >='" & Format(dtp_inicio.Value, "yyyy-mm-dd") & "' AND fecha_ntcr <= '" & Format(dtp_fin.Value, "yyyy-mm-dd") & "' AND tipo_ntcr = 3"
                rec.Open var_sql, conn_deuda
                
                While Not rec.EOF
                    var_sql = "insert into temp_colegio_nc(folio,area) values('" & rec("folio") & "','" & rec("area") & "')"
                    conn.Execute var_sql
                    rec.MoveNext
                Wend
                rec.Close
                
''                var_sql = "insert into temp_colegio(folio,area)" _
''                        & " SELECT folio_blta_fisico AS folio,localidad_est AS AREA FROM fnza_boletas b,establecimientos" _
''                        & " WHERE fecha_blta >='" & dtp_inicio.Value & "' AND fecha_blta <= '" & dtp_fin.Value & "' AND tipo_blta = 3;"
''                conn_deuda.Execute var_sql
                
'''                var_sql = "UPDATE temp_boletas_libro t,temp_colegio b" _
'''                        & " Set t.AREA = b.area" _
'''                        & " WHERE t.folio = b.folio"
'''                conn_deuda.Execute var_sql
                conn_deuda.Close
            Next j
            
            var_sql = "UPDATE temp_boletas_libro t,temp_colegio b" _
                    & " Set t.AREA = b.area" _
                    & " WHERE t.folio = b.folio"
            conn.Execute var_sql
            
            var_sql = "UPDATE temp_boletas_libro t,temp_colegio_fp b" _
                    & " Set t.forma_pago = b.forma_pago" _
                    & " WHERE t.folio = b.folio"
            conn.Execute var_sql
            
            var_sql = "UPDATE temp_boletas_libro t,temp_colegio_nc b" _
                    & " Set t.AREA = b.area" _
                    & " WHERE t.folio = b.folio"
            conn.Execute var_sql
            
            '' exportar excel tercera opcion
            var_sql = "SELECT * FROM temp_boletas_libro"
            objRS.Open var_sql, conn
            
            If objRS.RecordCount > 0 Then
            
            For I = 1 To 10
                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 = "Area"
            ApExcel.Cells(1, 2).Formula = "Fecha"
            ApExcel.Cells(1, 3).Formula = "Folio"
            ApExcel.Cells(1, 4).Formula = "Rut"
            ApExcel.Cells(1, 5).Formula = "Apoderado"
            ApExcel.Cells(1, 6).Formula = "Monto"
            ApExcel.Cells(1, 7).Formula = "NoBecado"
            ApExcel.Cells(1, 8).Formula = "Becado"
            ApExcel.Cells(1, 9).Formula = "Exención"
            ApExcel.Cells(1, 10).Formula = "FormaPago"
            
            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 9
                    If j = 1 Then
                        ApExcel.Cells(I, j + 1).Formula = Format(objRS(j), "yyyy/mm/dd")
                    Else
                        ApExcel.Cells(I, j + 1).Formula = objRS(j)
                    End If
                Next j
                ApExcel.Cells(I, 5).NumberFormat = "#,##0"
                ApExcel.Cells(I, 6).NumberFormat = "#,##0"
                ApExcel.Cells(I, 7).NumberFormat = "#,##0"
                ApExcel.Cells(I, 8).NumberFormat = "#,##0"
            
                monto = monto + objRS(5)
                nobecado = nobecado + objRS(6)
                becado = becado + objRS(7)
                exencion = exencion + objRS(8)
            
                pgbar.Value = I - 1
                'ApExcel.Cells(i , j).Formula =
                objRS.MoveNext
            Wend
            objRS.Close
            
            I = I + 1
            ApExcel.Cells(I, 6).Formula = monto
            ApExcel.Cells(I, 7).Formula = nobecado
            ApExcel.Cells(I, 8).Formula = becado
            ApExcel.Cells(I, 9).Formula = exencion
            ApExcel.Cells(I, 1).Formula = "Totales"
            ApExcel.Cells(I, 6).NumberFormat = "#,##0"
            ApExcel.Cells(I, 7).NumberFormat = "#,##0"
            ApExcel.Cells(I, 8).NumberFormat = "#,##0"
            ApExcel.Cells(I, 9).NumberFormat = "#,##0"
            ApExcel.Cells(I, 1).Font.Bold = True
            ApExcel.Cells(I, 6).Font.Bold = True
            ApExcel.Cells(I, 7).Font.Bold = True
            ApExcel.Cells(I, 8).Font.Bold = True
            ApExcel.Cells(I, 9).Font.Bold = True
                     
            ApExcel.Visible = True
            'ApExcel.Range("a1:x1").Borders.color = RGB(255, 0, 0)
            Set ApExcel = Nothing
            ' Salgo opcion 3
            End If
            Exit Sub
        End If
    End If
    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 = "Tipo Doc."
        ApExcel.Cells(1, 2).Formula = "Agno"
        ApExcel.Cells(1, 3).Formula = "Mes"
        ApExcel.Cells(1, 4).Formula = "Día"
        ApExcel.Cells(1, 5).Formula = "Bodega"
        ApExcel.Cells(1, 6).Formula = "Folio Desde"
        ApExcel.Cells(1, 7).Formula = "Folio Hasta"
        ApExcel.Cells(1, 8).Formula = "Emitidas"
        ApExcel.Cells(1, 9).Formula = "Nulas"
        ApExcel.Cells(1, 10).Formula = "Monto"
        ApExcel.Cells(1, 11).Formula = "NoBecado"
        ApExcel.Cells(1, 12).Formula = "Becado"
        ApExcel.Cells(1, 13).Formula = "Exención"
   
                
        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 12
                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.CodBode = gs.CodBode and 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.CodBode = gs.CodBode and 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
