unit MC_FYList;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  DBCtrls, StdCtrls, Db, DBTables, Grids, DBGrids, Buttons;

type
  TFYList = class(TForm)
    GroupBox1: TGroupBox;
    DBLookupComboBox1: TDBLookupComboBox;
    DBGrid1: TDBGrid;
    Table1: TTable;
    Table2: TTable;
    Table1BDEDesigner: TStringField;
    Table1BDEDesigner2: TStringField;
    Table2BDEDesigner: TStringField;
    Table2BDEDesigner2: TStringField;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    Table2BDEDesigner3: TStringField;
    GroupBox2: TGroupBox;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    DBGrid2: TDBGrid;
    Query1: TQuery;
    DataSource3: TDataSource;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    GroupBox3: TGroupBox;
    Query2: TQuery;
    Table3: TTable;
    Table3BDEDesigner: TStringField;
    DataSource4: TDataSource;
    DBGrid3: TDBGrid;
    ListBox1: TListBox;
    BitBtn3: TBitBtn;
    BitBtn4: TBitBtn;
    BitBtn5: TBitBtn;
    DataSource5: TDataSource;
    GroupBox4: TGroupBox;
    DBGrid4: TDBGrid;
    procedure BitBtn1Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FYList: TFYList;

implementation

{$R *.DFM}

procedure TFYList.BitBtn1Click(Sender: TObject);
begin
     GroupBox4.Visible:=False;
     GroupBox3.Visible:=False;
     GroupBox2.Visible:=True;
     Query1.Close;
     Query1.SQL.Clear;
     Query1.SQL.Add('Select Sum(水費.應收金額)');
     Query1.SQL.Add(',Sum(水費.實收金額),Sum(電費.應收金額)');
     Query1.SQL.Add(',Sum(電費.實收金額),Sum(煤氣費.應收金額)');
     Query1.SQL.Add(',Sum(煤氣費.實收金額) From 水費');
     Query1.SQL.Add(',電費,煤氣費');
     Query1.SQL.Add('Where 水費.水表編號=電費.電表編號 and 煤氣費.煤氣表編號=水費.水表編號 and');
     Query1.SQL.Add('水表編號 in (Select 房間編號 From 房屋信息表 Where 區名=:A and 大樓名=:B)');
     Query1.ParamByName('A').AsString:=Table1.FielDbyName('小區名').AsString;
     Query1.ParamByName('B').AsString:=Table2.FielDbyName('大樓名稱').AsString;
     Query1.Open;
     Label2.Caption:=Query1.Fields[0].AsString;
     Label4.Caption:=Query1.Fields[1].AsString;
     Label6.Caption:=Query1.Fields[2].AsString;
     Label8.Caption:=Query1.Fields[3].AsString;
     Label10.Caption:=Query1.Fields[4].AsString;
     Label12.Caption:=Query1.Fields[5].AsString;
//水費.水表編號,水費.戶主姓名,
//--------------------------------------------------------------------------
     Query1.Close;
     Query1.SQL.Clear;
     Query1.SQL.Add('Select 水費.水表編號,水費.戶主姓名,水費.應收金額');
     Query1.SQL.Add(',水費.實收金額,水費.剩余金額,電費.應收金額');
     Query1.SQL.Add(',電費.實收金額,電費.剩余金額,煤氣費.應收金額');
     Query1.SQL.Add(',煤氣費.實收金額,煤氣費.剩余金額 From 水費');
     Query1.SQL.Add(',電費,煤氣費');
     Query1.SQL.Add('Where 水費.水表編號=電費.電表編號 and 煤氣費.煤氣表編號=水費.水表編號 and');
     Query1.SQL.Add('水表編號 in (Select 房間編號 From 房屋信息表 Where 區名=:A and 大樓名=:B)');
     Query1.ParamByName('A').AsString:=Table1.FielDbyName('小區名').AsString;
     Query1.ParamByName('B').AsString:=Table2.FielDbyName('大樓名稱').AsString;
     Query1.Open;
     DBGrid2.Columns[0].FieldName:='水表編號';
     DBGrid2.Columns[1].FieldName:='戶主姓名';
     DBGrid2.Columns[2].FieldName:='應收金額';
     DBGrid2.Columns[3].FieldName:='實收金額';
     DBGrid2.Columns[4].FieldName:='剩余金額';
     DBGrid2.Columns[5].FieldName:='應收金額_1';
     DBGrid2.Columns[6].FieldName:='實收金額_1';
     DBGrid2.Columns[7].FieldName:='剩余金額_1';
     DBGrid2.Columns[8].FieldName:='應收金額_2';
     DBGrid2.Columns[9].FieldName:='實收金額_2';
     DBGrid2.Columns[10].FieldName:='剩余金額_2';
end;

procedure TFYList.FormClose(Sender: TObject; var Action: TCloseAction);
begin
     FYList.release;
     FYList:=Nil;
end;

procedure TFYList.BitBtn3Click(Sender: TObject);
var a,b:integer;
begin
     For a:=0 to ListBox1.Items.Count-1 do
     begin
          if ListBox1.Items.Strings[a]=Table3.FieldByName('科目名').AsString then
             begin
                  b:=0;
                  Break;
             end else b:=1;
     end;
     if b=0 then
        begin
             ShowMessage('此科目以被選種!!');
        end
     else
        begin
             ListBox1.Items.Add(Table3.FieldByName('科目名').AsString);
        end;
end;

procedure TFYList.BitBtn4Click(Sender: TObject);
begin
     if ListBox1.Items.Count<>0 then
        begin
             ListBox1.Items.Delete(ListBox1.ItemIndex);
        end;
end;

procedure TFYList.BitBtn2Click(Sender: TObject);
begin
     GroupBox3.Visible:=True;
end;

procedure TFYList.BitBtn5Click(Sender: TObject);
var KM: array of String;
    a:integer;
begin
     GroupBox4.Visible:=True;
     GroupBox3.Visible:=False;
     GroupBox2.Visible:=False;
     SetLength(KM,ListBox1.Items.Count);
     Query2.close;
     Query2.SQL.Clear;
     Query2.SQL.Add('select 收費編號,戶主姓名,費用科目,應收金額,實收金額,剩余金額 from 常用費用');
     Query2.SQL.Add('where 收費編號 in (Select 房間編號 From 房屋信息表 Where 區名=:A and 大樓名=:B)');
     Query2.SQL.Add('order by 收費編號,費用科目 ASC');
     Query2.ParamByName('A').AsString:=Table1.FielDbyName('小區名').AsString;
     Query2.ParamByName('B').AsString:=Table2.FielDbyName('大樓名稱').AsString;
     KM[0]:=ListBox1.Items.Strings[0];
     Query2.Filter:=Format('費用科目'+'='+'''%s''',[KM[0]]);
     For a:=1 to ListBox1.Items.count-1 do
     begin
          KM[a]:=ListBox1.Items.Strings[a];
          Query2.Filter:=Query2.Filter+' or '+Format('費用科目'+'='+'''%s''',[ListBox1.Items.Strings[a]]);
     end;
     Query2.Filtered:=True;     
     Query2.Open;
     KM:=Nil;
end;

end.