VERSION 5.00
Object = "{67397AA1-7FB1-11D0-B148-00A0C922E820}#6.0#0"; "MSADODC.OCX"
Object = "{0ECD9B60-23AA-11D0-B351-00A0C9055D8E}#6.0#0"; "MSHFLXGD.OCX"
Begin VB.Form frmConsCtaCteCliente 
   Caption         =   "Consulta Cta. Cte. de Clientes"
   ClientHeight    =   9600
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   17175
   LinkTopic       =   "Form3"
   ScaleHeight     =   9600
   ScaleWidth      =   17175
   StartUpPosition =   3  'Windows Default
   Begin VB.TextBox txtFiltro 
      Height          =   285
      Left            =   2520
      TabIndex        =   1
      Top             =   0
      Width           =   10575
   End
   Begin MSAdodcLib.Adodc Adodc1 
      Height          =   330
      Left            =   7440
      Top             =   8280
      Visible         =   0   'False
      Width           =   1215
      _ExtentX        =   2143
      _ExtentY        =   582
      ConnectMode     =   0
      CursorLocation  =   3
      IsolationLevel  =   -1
      ConnectionTimeout=   15
      CommandTimeout  =   30
      CursorType      =   3
      LockType        =   3
      CommandType     =   8
      CursorOptions   =   0
      CacheSize       =   50
      MaxRecords      =   0
      BOFAction       =   0
      EOFAction       =   0
      ConnectStringType=   1
      Appearance      =   1
      BackColor       =   -2147483643
      ForeColor       =   -2147483640
      Orientation     =   0
      Enabled         =   -1
      Connect         =   ""
      OLEDBString     =   ""
      OLEDBFile       =   ""
      DataSourceName  =   ""
      OtherAttributes =   ""
      UserName        =   ""
      Password        =   ""
      RecordSource    =   ""
      Caption         =   "Adodc1"
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      _Version        =   393216
   End
   Begin MSHierarchicalFlexGridLib.MSHFlexGrid MSHFlexGrid2 
      Height          =   8775
      Left            =   0
      TabIndex        =   0
      Top             =   360
      Width           =   16935
      _ExtentX        =   29871
      _ExtentY        =   15478
      _Version        =   393216
      _NumberOfBands  =   1
      _Band(0).Cols   =   2
   End
   Begin VB.Label Label1 
      Caption         =   "Cliente:"
      BeginProperty Font 
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   720
      TabIndex        =   2
      Top             =   0
      Width           =   1815
   End
End
Attribute VB_Name = "frmConsCtaCteCliente"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub CargarCliente()
On Error GoTo MERROR

Dim strCn As String
Dim i As Integer

strCn = "PROVIDER=MSDataShape;Data Provider=SQLOLEDB.1;Persist Security Info=0;User ID=" & psUserName & ";Password=" & psPassWord & ";Initial Catalog=" & psDataBase & ";Data Source=" & psDataServer


Dim strSh As String
Dim strVta As String
Dim strCliente As String
Dim strPagos As String


strCliente = "SELECT v.cClienteCode,v.IdLocal,v.DscCliente,'Local'=case when IdLocal =1 then 'Sur' when IdLocal =2 then 'Norte' end,'Tot.Ventas'=sum(v.TotVta)," _
& "'Tot.Pagos'=cast(isnull((select sum(case p.moneda when 'N' then p.monto when 'E' then p.monto*p.ntipocambio end)  from pagos p Where p.cClieCode =v.cClienteCode and p.IdLocal=v.IdLocal),0) as numeric(13,2))," _
& "'Tot.Deuda'=sum(v.TotVta)-isnull((select sum(case p.moneda when 'N' then p.monto when 'E' then p.monto*p.ntipocambio end)  from pagos p Where p.cClieCode =v.cClienteCode and p.IdLocal=v.IdLocal),0) " _
& "FROM venta01 v  WHERE FechaAnulacion is null  group by v.cClienteCode,v.IdLocal,v.DscCliente ORDER BY v.DscCliente "



strVta = "SELECT v.cSerieDoc,v.NroDoc,v.cClienteCode,v.IdLocal,'Vta Soles'=v.TotVta,'Vta USD'=v.TotVtaUSD,Moneda=case v.cCodMoneda when 'N' then 'Soles' when 'E' then 'Dolares' end, " _
& "Credito=(select vDscTipoCredito from tipocredito where ccodtipocredito=v.cCodTipoCredito),'Fec Emision'=v.fechaemision, " _
& "'Pag Sol'=cast(isnull((select sum(case p.moneda when 'N' then p.monto when 'E' then p.monto*p.ntipocambio end) from pagos p Where p.cSerieDoc = v.cSerieDoc and p.nrodoc=v.NroDoc and p.fechaanulacion is null group by p.tipodoc,p.cseriedoc,p.nrodoc),0)as numeric(13,2)), " _
& "'Pag USD'=cast(isnull((select sum(case p.moneda when 'N' then p.monto/p.ntipocambio when 'E' then p.monto end) from pagos p Where p.cSerieDoc = v.cSerieDoc and p.nrodoc=v.NroDoc and p.fechaanulacion is null group by p.tipodoc,p.cseriedoc,p.nrodoc),0)as numeric(13,2)), " _
& "Estado=Case v.cCodMoneda when 'N' then case when v.TotVta-(round(isnull((select sum(case p.moneda when 'N' then p.monto when 'E' then p.monto*p.ntipocambio end) from pagos p Where p.cSerieDoc = v.cSerieDoc and p.nrodoc=v.NroDoc and p.fechaanulacion is null group by p.tipodoc,p.cseriedoc,p.nrodoc),0),2))=0 then 'Cancelado' else 'Pendiente' end when 'E' then case when v.TotVtaUSD-(round(isnull((select sum(case p.moneda when 'N' then p.monto/p.ntipocambio when 'E' then p.monto end) from pagos p Where p.cSerieDoc = v.cSerieDoc And p.NroDoc = v.NroDoc and p.fechaanulacion is null group by p.tipodoc,p.cseriedoc,p.nrodoc),0),2))=0 then 'Cancelado' else 'Pendiente' end End " _
& "FROM venta01 v WHERE FechaAnulacion is null ORDER BY fechaemision "



strPagos = "SELECT cSerieDoc,NroDoc,cSerieDocPago,NroDocPago,'Fec Pago'=FechaPago,'Forma Pago'=(select vDscTipoPago from tipoformapago  where cCodTipoPago=pagos.FormaPago), " _
& "Moneda=case Moneda when 'N' then 'Soles' else 'Dolares' end,  'Pag Sol'=cast(case Moneda when 'N' then Monto   else Monto*nTipoCambio  end as numeric(13,2)) , " _
& "'Pag USD'=cast(case Moneda when 'E' then Monto   else Monto/nTipoCambio  end as numeric(13,2))  from pagos where fechaanulacion is null order by fechapago "


'strSh = "SHAPE {select distinct cClienteCode,IdLocal,DscCliente,'Local'=case when IdLocal =1 then 'Sur' when IdLocal =2 then 'Norte' end from venta01 order by DscCliente} " & _
'"APPEND ({select cClienteCode,idLocal,cSerieDoc,NroDoc,TotVta from venta01} AS Ventas RELATE " & _
'"cClienteCode TO cClienteCode, IdLocal TO IdLocal) "


'strSh = "SHAPE {select distinct cClienteCode,IdLocal,Cliente=DscCliente ,'Local'=case when IdLocal =1 then 'Sur' when IdLocal =2 then 'Norte' end from venta01 order by DscCliente} " & _
'"APPEND ((SHAPE{select cClienteCode,idLocal,cSerieDoc,NroDoc,TotVta from venta01} " & _
'"APPEND ({select  cSerieDoc,NroDoc,cSerieDocPago,NroDocPago,FechaPago,FormaPago=(select vDscTipoPago from tipoformapago  where cCodTipoPago=pagos.FormaPago),Monto, Moneda=case Moneda when 'N' then 'Soles' else 'Dolares' end  from pagos order by fechapago} as Pagos " & _
'"RELATE cSerieDoc TO cSerieDoc, NroDoc TO NroDoc)) " & _
'"RELATE cClienteCode TO cClienteCode, IdLocal TO IdLocal) "


strSh = "SHAPE {" & strCliente & "} as Clientes " & _
"APPEND ((SHAPE{" & strVta & "} as Ventas " & _
"APPEND ({" & strPagos & "} as Pagos " & _
"RELATE cSerieDoc TO cSerieDoc, NroDoc TO NroDoc)) " & _
"RELATE cClienteCode TO cClienteCode, IdLocal TO IdLocal) "


With Adodc1
.ConnectionString = strCn
.RecordSource = strSh
End With

Set MSHFlexGrid2.DataSource = Adodc1

MSHFlexGrid2.BandDisplay = flexBandDisplayHorizontal 'flexBandDisplayVertical

'MSHFlexGrid2.ColHeader(0) = flexColHeaderOn
'MSHFlexGrid2.ColHeader(1) = flexColHeaderOn
'MSHFlexGrid2.ColHeader(2) = flexColHeaderOn
'
'MSHFlexGrid2.BandIndent(1) = 6
'MSHFlexGrid2.BandIndent(2) = 10


With MSHFlexGrid2

    For i = 1 To MSHFlexGrid2.Rows - 1
        .Row = i
        'MsFlexGrid1.TextMatrix(1,1)=Format(Rs("Campo"), "#,##0.00")
        If .TextMatrix(i, 19) = "Pendiente" Then
            .Col = 19
            '.CellForeColor = vbRed
            .CellBackColor = vbYellow
        End If
    Next


    .CollapseAll


    .ColWidth(1) = 0
    .ColWidth(2) = 0
    .ColWidth(3) = 4400
    .ColWidth(4) = 600
    .ColWidth(5) = 850
    .ColWidth(6) = 850
    .ColWidth(7) = 850

    .ColWidth(0, 1) = 600
    .ColWidth(1, 1) = 700
    .ColWidth(2, 1) = 0
    .ColWidth(3, 1) = 0
    .ColWidth(4, 1) = 750
    .ColWidth(5, 1) = 750
    .ColWidth(6, 1) = 750
    .ColWidth(7, 1) = 1300
    .ColWidth(9, 1) = 750
    .ColWidth(10, 1) = 750
    .ColWidth(11, 1) = 850
    
    .ColWidth(0, 2) = 0
    .ColWidth(1, 2) = 0
    .ColWidth(2, 2) = 600
    .ColWidth(3, 2) = 700
    .ColWidth(5, 2) = 1100
    .ColWidth(6, 2) = 750
    .ColWidth(7, 2) = 750
    .ColWidth(8, 2) = 750


    .ColAlignmentBand(1, 4) = flexAlignRightCenter
    .ColAlignmentBand(1, 5) = flexAlignRightCenter
    .ColAlignmentBand(1, 9) = flexAlignRightCenter
    .ColAlignmentBand(1, 10) = flexAlignRightCenter
    
    .ColAlignmentBand(2, 7) = flexAlignRightCenter
    .ColAlignmentBand(2, 8) = flexAlignRightCenter
    
'.GridColorBand(0) = vbGreen
'.GridColorBand(1) = vbBlue
'.GridColorBand(2) = vbYellow

    .BackColorBand(0) = &HFFC0C0
    .BackColorBand(1) = &HC0FFC0
    .BackColorBand(2) = &HC0E0FF


End With



Exit Sub
MERROR:
MsgBox Err.Description


End Sub



Private Sub pvSetCellForeColor(MSHFlex As MSHFlexGrid, lColor As Long)
    Dim c   As Integer
    With MSHFlex
        For c = 0 To .Cols - 1
            .Col = c
            .CellForeColor = lColor
        Next
    End With
End Sub



Private Sub Form_Load()

CargarCliente

End Sub

Private Sub Form_Resize()
On Error Resume Next
  MSHFlexGrid2.Width = Screen.ActiveForm.Width - 200
  MSHFlexGrid2.Height = Screen.ActiveForm.Height - 1000
End Sub

Private Sub txtFiltro_Change()
' CargarCliente

    If txtFiltro <> "" Then
        
        Adodc1.Recordset.Filter = "DscCliente LIKE '*" + txtFiltro.Text + "*'"
        
    Else
        
        Adodc1.Recordset.Filter = ""
           
        Set MSHFlexGrid2.DataSource = Adodc1
           
        If Not Adodc1.Recordset.EOF Then
            Adodc1.Recordset.MoveFirst
        End If
        
    End If
    
    
    MSHFlexGrid2.CollapseAll
    
    
End Sub
