unit Code;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Buttons, Mask, DBCtrls, DB, ADODB, ExtCtrls, ComCtrls;

type
  TCodeSelect = class(TForm)
    BitBtn1: TBitBtn;
    ADOQuery1: TADOQuery;
    ComboBox2: TComboBox;
    Panel1: TPanel;
    Label3: TLabel;
    ListView1: TListView;
    Btn1: TButton;
    BitBtn2: TBitBtn;
    Label1: TLabel;
    ComboBox1: TComboBox;
    ADOQuery2: TADOQuery;
    Button1: TButton;
    Edit1: TEdit;
    Bevel1: TBevel;
    procedure FormShow(Sender: TObject);
    procedure ComboBox2Change(Sender: TObject);

    procedure BitBtn1Click(Sender: TObject);
    procedure ListView1SelectItem(Sender: TObject; Item: TListItem;
      Selected: Boolean);
    procedure ComboBox1Change(Sender: TObject);
    procedure Button1Click(Sender: TObject);

  private
    { Private declarations }
  public
    { Public declarations }
    cocode: string;
    coname: string;
    coyear: string;
    ok : integer;
  end;

var
  CodeSelect: TCodeSelect;

implementation

uses BusDataModuleUnit;

{$R *.dfm}

procedure TCodeSelect.FormShow(Sender: TObject);
var
        sqlstr : string;
begin
        sqlstr:='select distinct 統計年份 from 公用事業單位基本情況表 order by 統計年份';
        AdoQuery2.Close;
        AdoQuery2.SQL.Clear;
        AdoQuery2.SQL.Add(sqlstr);
        AdoQuery2.Open;
        ComboBox1.Items.Clear;
        ComboBox1.Text:='';
        if (AdoQuery2.RecordCount>0) then
        begin
                while (not AdoQuery2.Eof) do
                begin
                        ComboBox1.Items.Add(AdoQuery2.fieldbyname('統計年份').AsString);
                        AdoQuery2.Next;
                end;
        end;

        if (ComboBox1.Text='') then
                sqlstr := 'select 單位名稱 from 公用事業單位基本情況表 order by 單位名稱'
        else    sqlstr := 'select 單位名稱 from 公用事業單位基本情況表 where 統計年份='+ComboBox1.Text+'  by 單位名稱';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr);
        AdoQuery1.Open;
        ComboBox2.Items.Clear;
        ComboBox2.Text := '';
        listview1.Selected := nil;
        listview1.Items.Clear;
        coname := '';
        cocode := '';
        ok := 0;
        while (not AdoQuery1.Eof)
        do
        begin
                ComboBox2.Items.Add(AdoQuery1.fieldbyname('單位名稱').AsString);
                AdoQuery1.Next;
        end
end;

procedure TCodeSelect.ComboBox2Change(Sender: TObject);
var
        sqlstr : string;
        coc: string;
        ss1,ss2 : string;
        item : TListItem;
begin
        coc := ComboBox2.Text;
        if (ComboBox1.Text='') then
                sqlstr := 'select 單位代碼,統計年份 from 公用事業單位基本情況表 where 單位名稱='+''''+coc+''''
                //sqlstr := 'select 單位代碼,統計年份 from 公用事業單位基本情況表 where 單位名稱 >= '+''''+coc+''''
        else    sqlstr := 'select 單位代碼,統計年份 from 公用事業單位基本情況表 where 統計年份='+ComboBox1.Text+' and 單位名稱='+''''+coc+'''';
                //sqlstr := 'select 單位代碼,統計年份 from 公用事業單位基本情況表 where 統計年份='+ComboBox1.Text+' and 單位名稱 >= '+''''+coc+'''';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr);
        AdoQuery1.Open;
        listview1.Items.Clear;
        while (not AdoQuery1.Eof)
        do
        begin
                ss1 :=AdoQuery1.fieldbyname('單位代碼').AsString;
                ss2 := AdoQuery1.fieldbyname('統計年份').AsString;
                item :=listview1.Items.Add;
                item.Caption := coc;
                item.SubItems.Add(ss1);
                item.SubItems.Add(ss2);
                AdoQuery1.Next;
        end;
end;

procedure TCodeSelect.BitBtn1Click(Sender: TObject);
var
        fin : integer;
        Item: TListItem;
begin
        //coname := ComboBox2.Text;
        fin := 1;
        {if ((listview1.Selected = nil) and (listview1.Items.Count > 1))
        then
        begin
                ShowMessage('請在表單中選擇一個單位名稱！');
                fin := 0;
        end;

        if ((coname = ''))
        then
        begin
                ShowMessage('請選擇企業！');
                fin := 0;
        end; }

        if (listview1.Items.Count =1)
        then
        begin
                cocode := listview1.Items.Item[0].subitems[0];
                coyear := listview1.Items.Item[0].SubItems[1];
                coname := listview1.items.item[0].caption;
        end
        else
        begin
                if (listview1.Selected = nil)
                then
                    begin
                        ShowMessage('請在表單中選擇一個單位名稱！');
                        fin := 0;
                    end
                else
                    begin
                        item := listview1.Selected;
                        coname := item.Caption;
                        cocode := item.subitems[0];
                        coyear := item.SubItems[1];
                    end;
        end;

        if ( fin = 1)
        then
        begin
                //Close;
                Btn1.click;
        end;
end;

procedure TCodeSelect.ListView1SelectItem(Sender: TObject; Item: TListItem;
  Selected: Boolean);
begin
        cocode := item.Subitems[0];
        coyear := item.SubItems[1];

end;
procedure TCodeSelect.ComboBox1Change(Sender: TObject);
var
        sqlstr: string;
begin
        if (ComboBox1.Text='') then
                sqlstr := 'select 單位名稱 from 公用事業單位基本情況表 order by 單位名稱'
        else    sqlstr := 'select 單位名稱 from 公用事業單位基本情況表 where 統計年份='+ComboBox1.Text+' order by 單位名稱';
        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr);
        AdoQuery1.Open;
        ComboBox2.Items.Clear;
        ComboBox2.Text := '';
        listview1.Selected := nil;
        listview1.Items.Clear;
        coname := '';
        cocode := '';
        ok := 0;
        while (not AdoQuery1.Eof)
        do
        begin
                ComboBox2.Items.Add(AdoQuery1.fieldbyname('單位名稱').AsString);
                AdoQuery1.Next;
        end
end;

procedure TCodeSelect.Button1Click(Sender: TObject);
var
        sqlstr : string;
        coc: string;
        ss1,ss2 : string;
        item : TListItem;
        tmp : integer;
begin
        listview1.Items.Clear;
        coc := Edit1.Text;
        if (ComboBox1.Text='') then
                sqlstr := 'select 單位代碼,統計年份,單位名稱 from 公用事業單位基本情況表 where 單位名稱 like '+''''+'%'+coc+'%'+''''
        else    sqlstr := 'select 單位代碼,統計年份,單位名稱 from 公用事業單位基本情況表 where 統計年份 = '+ComboBox1.Text+' and 單位名稱 like '+''''+'%'+coc+'%'+'''';

        AdoQuery1.Close;
        AdoQuery1.SQL.Clear;
        AdoQuery1.SQL.Add(sqlstr);
        AdoQuery1.Open;

        tmp:= AdoQuery1.recordcount;
        while (not AdoQuery1.Eof)
        do
        begin
                ss1 :=AdoQuery1.fieldbyname('單位代碼').AsString;
                ss2 := AdoQuery1.fieldbyname('統計年份').AsString;
                item :=listview1.Items.Add;
                item.Caption := AdoQuery1.fieldbyname('單位名稱').AsString;
                item.SubItems.Add(ss1);
                item.SubItems.Add(ss2);
                AdoQuery1.Next;
        end;
end;

end.