Fórum Relatório Plano de Contas #344454

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, IBCustomDataSet, IBQuery, IBDatabase, ComCtrls, StdCtrls, StrUtils,
  DBClient, Provider, Grids, DBGrids, DBCtrls, ExtCtrls;

type
  TForm1 = class(TForm)
    BD: TIBDatabase;
    TransBD: TIBTransaction;
    Qry_PlanoContas: TIBQuery;
    Qry_PlanoContasID_PLANO: TIntegerField;
    Qry_PlanoContasDESCRICAO: TIBStringField;
    TreeView1: TTreeView;
    btAbreDados: TButton;
    btVerID: TButton;
    DBGrid1: TDBGrid;
    Label1: TLabel;
    DataSource1: TDataSource;
    qry_Lancamentos: TIBQuery;
    dspLancamentos: TDataSetProvider;
    cdsLancamentos: TClientDataSet;
    cdsLancamentosID_LANCAMENTO: TIntegerField;
    cdsLancamentosID_PLANO: TIntegerField;
    cdsLancamentosDESCRICAO: TStringField;
    cdsLancamentosDATA: TDateField;
    cdsLancamentosVALOR: TBCDField;
    cdsLancamentosTIPO_LANCAMENTO: TStringField;
    Panel1: TPanel;
    Label2: TLabel;
    TransConsultas: TIBTransaction;
    Qry_Saldo: TIBQuery;
    lbSaldo: TLabel;
    procedure btAbreDadosClick(Sender: TObject);
    procedure btVerIDClick(Sender: TObject);
    procedure TreeView1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.btAbreDadosClick(Sender: TObject);
var
  TVS :TTreeNode;
  S : String;
  Posicao, UltPosicao, I : Integer;
  P : ^Integer;
begin
  Qry_PlanoContas.Open;
  Qry_PlanoContas.First;

  TreeView1.Items.BeginUpdate;
  Qry_PlanoContas.DisableControls;

  TreeView1.Items.Clear;
  UltPosicao := 0;

  while not Qry_PlanoContas.Eof do begin

    S := Qry_PlanoContasDESCRICAO.AsString;
    S := Trim(Copy( S , 1 , Pos(´-´,S)-1 ));
    Posicao := Length(AnsiReplaceStr(S,´.´,´´)) div 2;

    if Posicao = 1 then
      TVS:=TreeView1.Items.Add( Nil, Qry_PlanoContasDESCRICAO.AsString )
    else if Posicao > 1 then begin
      if Posicao = UltPosicao+1 then
        TVS := TreeView1.Items.AddChild( TVS , Qry_PlanoContasDESCRICAO.AsString )
      else if Posicao < UltPosicao then begin
        for I := Posicao to UltPosicao do
          TVS := TVS.Parent;
        TVS := TreeView1.Items.AddChild( TVS , Qry_PlanoContasDESCRICAO.AsString )
      end
      else begin
        TVS := TVS.Parent;
        TVS := TreeView1.Items.AddChild( TVS , Qry_PlanoContasDESCRICAO.AsString )
      end;
    end;

    P := GetMemory(SizeOf(Integer));
    P^ := Qry_PlanoContasID_PLANO.AsInteger;
    TVS.Data := P;

    UltPosicao := Posicao;
    Qry_PlanoContas.Next;

  end;

  Qry_PlanoContas.EnableControls;
  TreeView1.Items.EndUpdate;

  TransBD.Rollback;

end;

procedure TForm1.btVerIDClick(Sender: TObject);
var
  P : Pointer;
begin
  if TreeView1.Selected = Nil then
    Exit;
  P := TreeView1.Selected.Data;
  if P = Nil then
    Exit;
  Showmessage( ´O ID é ´+IntToStr( Integer(P^) ) );
end;

procedure TForm1.TreeView1Click(Sender: TObject);
var
  P : Pointer;
  ID:Integer;
begin
  if TreeView1.Selected = Nil then
    Exit;
  P := TreeView1.Selected.Data;
  if P = Nil then
    Exit;
  ID := Integer(P^);
  cdsLancamentos.Close;
  cdsLancamentos.Params[0].AsInteger := ID;
  cdsLancamentos.Open;
  Qry_Saldo.Params[0].AsInteger := ID;
  Qry_Saldo.Open;
  lbSaldo.Caption := FormatCurr( ´R$ 0.00´ , Qry_Saldo.Fields[0].AsCurrency );
  Qry_Saldo.Close;
  if TransConsultas.InTransaction then
    TransConsultas.Rollback;
end;

end.

object FPlanoC: TFPlanoC
  Left = 192
  Top = 114
  Width = 696
  Height = 480
  Caption = ´Consulta ao Plano de Contas´
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = ´MS Sans Serif´
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 360
    Top = 8
    Width = 110
    Height = 13
    Caption = ´Lançamentos da Conta´
  end
  object TreeView1: TTreeView
    Left = 8
    Top = 8
    Width = 337
    Height = 407
    Anchors = [akLeft, akTop, akBottom]
    Indent = 19
    TabOrder = 0
    OnClick = TreeView1Click
  end
  object btAbreDados: TButton
    Left = 8
    Top = 417
    Width = 105
    Height = 25
    Anchors = [akLeft, akBottom]
    Caption = ´Abrir Dados´
    TabOrder = 1
    OnClick = btAbreDadosClick
  end
  object btVerID: TButton
    Left = 120
    Top = 416
    Width = 105
    Height = 25
    Anchors = [akLeft, akBottom]
    Caption = ´Ver ID´
    TabOrder = 2
    OnClick = btVerIDClick
  end
  object DBGrid1: TDBGrid
    Left = 352
    Top = 24
    Width = 329
    Height = 353
    Anchors = [akLeft, akTop, akRight, akBottom]
    DataSource = DataSource1
    Options = [dgTitles, dgIndicator, dgColumnResize, dgColLines, dgRowLines, dgTabs, dgConfirmDelete, dgCancelOnExit]
    ReadOnly = True
    TabOrder = 3
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = ´MS Sans Serif´
    TitleFont.Style = []
    Columns = <
      item
        Expanded = False
        FieldName = ´ID_LANCAMENTO´
        Visible = True
      end
      item
        Expanded = False
        FieldName = ´DESCRICAO´
        Visible = True
      end
      item
        Expanded = False
        FieldName = ´DATA´
        Visible = True
      end
      item
        Expanded = False
        FieldName = ´VALOR´
        Visible = True
      end
      item
        Expanded = False
        FieldName = ´TIPO_LANCAMENTO´
        Visible = True
      end>
  end
  object Panel1: TPanel
    Left = 353
    Top = 380
    Width = 330
    Height = 33
    Anchors = [akLeft, akRight, akBottom]
    TabOrder = 4
    object Label2: TLabel
      Left = 8
      Top = 8
      Width = 76
      Height = 13
      Caption = ´Saldo da Conta:´
    end
    object lbSaldo: TLabel
      Left = 96
      Top = 8
      Width = 3
      Height = 13
    end
  end
  object Button1: TButton
    Left = 232
    Top = 416
    Width = 105
    Height = 25
    Caption = ´Cadastrar´
    TabOrder = 5
    OnClick = Button1Click
  end
  object BD: TIBDatabase
    Connected = True
    DatabaseName = ´DBEXEMPLO.FDB´
    Params.Strings = (
      ´user_name=SYSDBA´
      ´password=masterkey´
      ´lc_ctype=WIN1252´)
    LoginPrompt = False
    DefaultTransaction = TransBD
    IdleTimer = 0
    SQLDialect = 3
    TraceFlags = []
    Left = 80
    Top = 32
  end
  object TransBD: TIBTransaction
    Active = True
    DefaultDatabase = BD
    Params.Strings = (
      ´read_committed´
      ´rec_version´
      ´nowait´)
    AutoStopAction = saNone
    Left = 112
    Top = 32
  end
  object Qry_PlanoContas: TIBQuery
    Database = BD
    Transaction = TransBD
    BufferChunks = 1000
    CachedUpdates = False
    SQL.Strings = (
      ´SELECT * FROM sp_planocontas´)
    Left = 144
    Top = 32
    object Qry_PlanoContasID_PLANO: TIntegerField
      FieldName = ´ID_PLANO´
      Origin = ´"SP_PLANOCONTAS"."ID_PLANO"´
    end
    object Qry_PlanoContasDESCRICAO: TIBStringField
      FieldName = ´DESCRICAO´
      Origin = ´"SP_PLANOCONTAS"."DESCRICAO"´
      Size = 90
    end
  end
  object DataSource1: TDataSource
    DataSet = cdsLancamentos
    Left = 240
    Top = 72
  end
  object qry_Lancamentos: TIBQuery
    Database = BD
    Transaction = TransBD
    BufferChunks = 1000
    CachedUpdates = False
    SQL.Strings = (
      ´SELECT´
      #9´ID_LANCAMENTO,´
      9´ID_PLANO,´
      9´DESCRICAO,´
      9´DATA,´
      9´VALOR,´
      9´CASE TIPO ´
      99´WHEN ´39´E´39´ THEN ´39´Entrada´39´ ´
      99´ELSE ´39´Saída´39
      9´END AS TIPO_LANCAMENTO´
      ´FROM LANCAMENTOS´
      ´WHERE ID_PLANO = :ID_PLANO´)
    Left = 144
    Top = 72
    ParamData = <
      item
        DataType = ftUnknown
        Name = ´ID_PLANO´
        ParamType = ptUnknown
      end>
  end
  object dspLancamentos: TDataSetProvider
    DataSet = qry_Lancamentos
    Constraints = True
    Left = 176
    Top = 72
  end
  object cdsLancamentos: TClientDataSet
    Aggregates = <>
    Params = <
      item
        DataType = ftUnknown
        Name = ´ID_PLANO´
        ParamType = ptUnknown
      end>
    ProviderName = ´dspLancamentos´
    Left = 208
    Top = 72
    object cdsLancamentosID_LANCAMENTO: TIntegerField
      DisplayLabel = ´Nº Lançamento´
      FieldName = ´ID_LANCAMENTO´
      Required = True
    end
    object cdsLancamentosID_PLANO: TIntegerField
      FieldName = ´ID_PLANO´
      Required = True
      Visible = False
    end
    object cdsLancamentosDESCRICAO: TStringField
      DisplayLabel = ´Descrição do Lançamento´
      FieldName = ´DESCRICAO´
      Size = 60
    end
    object cdsLancamentosDATA: TDateField
      DisplayLabel = ´Data do Lançamento´
      FieldName = ´DATA´
      Required = True
    end
    object cdsLancamentosVALOR: TBCDField
      DisplayLabel = ´Valor´
      FieldName = ´VALOR´
      Required = True
      currency = True
      Precision = 18
      Size = 3
    end
    object cdsLancamentosTIPO_LANCAMENTO: TStringField
      DisplayLabel = ´Tipo´
      FieldName = ´TIPO_LANCAMENTO´
      FixedChar = True
      Size = 7
    end
  end
  object TransConsultas: TIBTransaction
    Active = False
    DefaultDatabase = BD
    Params.Strings = (
      ´read_committed´
      ´rec_version´
      ´nowait´)
    AutoStopAction = saNone
    Left = 112
    Top = 72
  end
  object Qry_Saldo: TIBQuery
    Database = BD
    Transaction = TransBD
    BufferChunks = 1000
    CachedUpdates = False
    SQL.Strings = (
      ´SELECT´
      #9´SUM(´
      99´CASE TIPO´
      #999´WHEN ´39´E´39´ THEN VALOR´
      999´ELSE VALOR * (-1)´
      99´END´
      9´) AS SALDO´
      ´FROM LANCAMENTOS´
      ´WHERE ID_PLANO = :ID_PLANO´)
    Left = 144
    Top = 112
    ParamData = <
      item
        DataType = ftUnknown
        Name = ´ID_PLANO´
        ParamType = ptUnknown
      end>
  end
  object Qry_Insere: TIBQuery
    Database = BD
    Transaction = TransBD
    BufferChunks = 1000
    CachedUpdates = False
    Left = 176
    Top = 32
  end
end