unit Epr_SelectUnit2;

interface

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

type
  TEpr_Select2 = class(TForm)
    Panel1: TPanel;
    ADOQuery1: TADOQuery;
    ListView1: TListView;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    SpeedButton3: TSpeedButton;
    SpeedButton4: TSpeedButton;
    ListView2: TListView;
    ADOQuery2: TADOQuery;
    Panel2: TPanel;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    Panel3: TPanel;
    Label1: TLabel;
    ComboBox1: TComboBox;
    BitBtn3: TBitBtn;
    ComboBox2: TComboBox;
    Label2: TLabel;
    ADOQuery3: TADOQuery;
    ADOCommand1: TADOCommand;
    procedure FormShow(Sender: TObject);
    procedure ListView1SelectItem(Sender: TObject; Item: TListItem;
      Selected: Boolean);
    procedure SpeedButton1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpeedButton3Click(Sender: TObject);
    procedure ListView2SelectItem(Sender: TObject; Item: TListItem;
      Selected: Boolean);
    procedure SpeedButton4Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure InitComboBox1();
  private
    { Private declarations }
  public
        sString:String;
        EprCode,EprName,TotalEpr:String;
        LView1Record,LView2Record:integer;
        aditems:TListItem;
        procedure CheckStatus(Left:integer;Right:integer);
    { Public declarations }
  end;

var
  Epr_Select2: TEpr_Select2;

implementation

uses BusTradeDataModuleUnit, Epr_NameBookUnit;

{$R *.dfm}
procedure TEpr_Select2.InitComboBox1();
var
        sqlstr,ss : string;
begin
        ComboBox1.Items.Clear;
        sqlstr := 'select distinct 統計年份 from 公用事業單位基本情況表';
        AdoQuery2.Close;
        AdoQuery2.SQL.Clear;
        AdoQuery2.SQL.Add(sqlstr);
        AdoQuery2.Open;
        while (not AdoQuery2.Eof) do
        begin
                ss := AdoQuery2.fieldbyname('統計年份').AsString;
                if (ss <> ComboBox2.Text) then
                        ComboBox1.Items.Add(ss);
                AdoQuery2.Next;
        end;
        if (ComboBox1.Items.Count>0) then
                //ComboBox1.Text := ComboBox1.Items[0];
                ComBoBox1.ItemIndex:=0;
end;

procedure TEpr_Select2.CheckStatus(Left:integer;Right:integer);
begin
        if Left=0 then
        begin
                SpeedButton1.Enabled:=False;
                SpeedButton2.Enabled:=False;
        end else
        begin
                SpeedButton1.Enabled:=True;
                SpeedButton2.Enabled:=True;
        end;

        if Right=0 then
        begin
                SpeedButton3.Enabled:=False;
                SpeedButton4.Enabled:=False;
                BitBtn1.Enabled := false;
        end else
        begin
                SpeedButton3.Enabled:=True;
                SpeedButton4.Enabled:=True;
                BitBtn1.Enabled := true;
        end;

end;

procedure TEpr_Select2.FormShow(Sender: TObject);
var
        aditems:TListItem;
        i:integer;
begin
        listview1.Selected := nil;
        ListView1.Items.Clear;
        ComboBox2.Text := Epr_NameBook.ComboBox1.Text;
        InitComboBox1();
        Epr_Select2.ComboBox1Change(nil);

        
        listview2.Selected := nil;
        ListView2.Items.Clear;
        //ComboBox1.Text:='';
        LView1Record:=Listview1.Items.Count;
        LView2Record:=0;
        sString:='';
        EprCode:='';
        EprName:='';
        BitBtn1.Enabled:=TRUE;
        BitBtn2.Enabled:=True;
        BitBtn3.Enabled:=true;
        CheckStatus(LView1Record,LView2Record);
end;

procedure TEpr_Select2.ListView1SelectItem(Sender: TObject; Item: TListItem;
  Selected: Boolean);
begin
        EprCode:=Item.Caption;
        EprName:=Item.SubItems[0];
end;

procedure TEpr_Select2.SpeedButton1Click(Sender: TObject);{增加一條}
begin
        if ListView1.Selected<>nil then
        begin
                aditems:=ListView2.Items.Add;
                aditems.Caption:=EprCode;
                aditems.SubItems.Add(EprName);
                LView1Record:=LView1Record-1;
                LView2Record:=LView2Record+1;
                CheckStatus(LView1Record,LView2Record);
                ListView1.Selected.Delete;
        end else
        begin
                showmessage('請您選擇一個企業！');
                exit;
        end;
        ListView1.AlphaSort;
        ListView2.AlphaSort;
end;

procedure TEpr_Select2.BitBtn2Click(Sender: TObject);
begin
        sString:='';
        Epr_Select2.Close;
end;

procedure TEpr_Select2.SpeedButton2Click(Sender: TObject);{全部添加}
var
        i:integer;
begin
        for i:=0 to LView1Record-1 do
        begin
                aditems:=ListView2.Items.Add;
                aditems.Caption:=ListView1.Items[0].Caption;
                aditems.SubItems.Add(ListView1.Items[0].SubItems[0]);
                ListView1.Items[0].Delete;
        end;
        LView2Record:=LView2Record+LView1Record;
        LView1Record:=0;
        CheckStatus(LView1Record,LView2Record);
        ListView1.AlphaSort;
        ListView2.AlphaSort;
end;

procedure TEpr_Select2.SpeedButton3Click(Sender: TObject);{刪除一條}
begin
        if (ListView2.Selected<>nil) then
        begin
                aditems:=ListView1.Items.Add;
                aditems.Caption:=EprCode;
                aditems.SubItems.Add(EprName);
                LView1Record:=LView1Record+1;
                LView2Record:=LView2Record-1;
                CheckStatus(LView1Record,LView2Record);
                ListView2.Selected.Delete;
        end else
        begin
                showmessage('請您選擇一個企業！');
                exit;
        end;
        ListView1.AlphaSort;
        ListView2.AlphaSort;
end;

procedure TEpr_Select2.ListView2SelectItem(Sender: TObject; Item: TListItem;
  Selected: Boolean);
begin
        EprCode:=Item.Caption;
        EprName:=Item.SubItems[0];
end;

procedure TEpr_Select2.SpeedButton4Click(Sender: TObject); {刪除全部}
var
        i:integer;
begin
        for i:=0 to LView2Record-1 do
        begin
                aditems:=ListView1.Items.Add;
                aditems.Caption:=ListView2.Items[0].Caption;
                aditems.SubItems.Add(ListView2.Items[0].SubItems[0]);
                ListView2.Items[0].Delete;
        end;
        LView1Record:=LView1Record+LView2Record;
        LView2Record:=0;
        CheckStatus(LView1Record,LView2Record);
        ListView1.AlphaSort;
        ListView2.AlphaSort;
end;

procedure TEpr_Select2.BitBtn1Click(Sender: TObject);
var
        i:integer;
        sqlstr, codes,ss1,ss2: string;
        strs: tFieldlist;
begin
        codes := '';
        i := 0;
        while (  i<listview2.Items.Count-1 )do
        begin
                codes := codes+''''+listview2.Items.Item[i].Caption+''''+',';
                i:=i+1;
        end;
        codes := codes+''''+listview2.Items.Item[listview2.Items.Count-1].Caption+'''';
        ////////////////////////////////// insert into
        AdoQuery3.Close;
        AdoQuery3.SQL.Clear;
        AdoQuery3.SQL.Add('select * from 公用事業單位基本情況表');
        AdoQuery3.Open;
        ss1 :='';  ss2:='';
        strs := Adoquery3.FieldList;
        for i := 0 to strs.Count-2 do
        begin
                if (strs.Strings[i]<>'統計年份') and (strs.strings[i]<>'企業(單位)登記注冊類型') then
                        ss1:=ss1+strs.Strings[i]+','
                else  if (strs.strings[i]='企業(單位)登記注冊類型') then
                        ss1:=ss1+'[企業(單位)登記注冊類型]'+','
                else  ss1:=ss1+ComboBox2.Text+' as 統計年份 ,';
        end;
        ss1:=ss1+strs.Strings[strs.count-1];
        for i := 0 to strs.Count-2 do
        begin
                if (strs.strings[i]<>'企業(單位)登記注冊類型') then
                        ss2:=ss2+strs.Strings[i]+','
                else
                        ss2:=ss2+'[企業(單位)登記注冊類型]'+',';

        end;
        ss2:=ss2+strs.Strings[strs.count-1];

        sqlstr := 'insert into 公用事業單位基本情況表 ('+ss2+') select '+ss1+' from 公用事業單位基本情況表 where 統計年份='+ComboBox1.Text+ ' and 單位代碼 in ('+codes+')';
        AdoCommand1.CommandText := sqlstr;
        AdoCommand1.Execute;

        ////////////////////////////////////////////////////////////////////////
        {AdoQuery3.Close;
        AdoQuery3.SQL.Clear;
        AdoQuery3.SQL.Add('select * from 主要業務活動表');
        AdoQuery3.Open;
        ss1 :='';  ss2:='';
        strs := Adoquery3.FieldList;
        for i := 0 to strs.Count-2 do        //取值
        begin
                if (strs.Strings[i]<>'統計年份') then
                        ss1:=ss1+strs.Strings[i]+','
                else
                        ss1:=ss1+ComboBox2.Text+' as 統計年份 ,';
        end;
        ss1:=ss1+strs.Strings[strs.count-1];

        for i := 0 to strs.Count-2 do         //取字段
                        ss2:=ss2+strs.Strings[i]+',';
        ss2:=ss2+strs.Strings[strs.count-1];
        ///////////////////////////////////insert into 主要業務活動表
        sqlstr := 'insert into 主要業務活動表 ('+ss2+') select '+ss1+' from 主要業務活動表 where 統計年份='+ComboBox1.Text+ ' and 企業代碼 in ('+codes+')';
        AdoCommand1.CommandText := sqlstr;
        AdoCommand1.Execute;    }

        Epr_NameBook.initbox();
        Epr_NameBook.ComboBox1Change(nil);
end;

procedure TEpr_Select2.ComboBox1Change(Sender: TObject);
var
        aditems:TListItem;
        i:integer;
        temp:integer;
        sqlstr : string;
begin
        if (ComboBox1.Text='') then
        begin
                ShowMessage('數據庫中沒有可以復制的名錄信息！');
                exit;
        end;
        
        try
                temp:=StrToInt(ComboBox1.Text);
        except
                showmessage('對不起！您選擇的"統計年份"非法！請重新選擇！');
                exit;
        end;
        if (length(ComboBox1.Text)<>4) or (StrToInt(ComboBox1.Text)>3000) or (StrToInt(ComboBox1.Text)<1000) then
        begin
                showmessage('對不起！請您在1000年-3000年之間選擇"統計年份"！');
                exit;
        end;

        listview1.Selected := nil;
        ListView1.Items.Clear;
        ADOQuery1.Close;
        ADOQuery1.SQL.Clear;
        sqlstr := 'select 單位代碼 ,單位名稱 from 公用事業單位基本情況表 where 統計年份='+ComboBox1.Text+' and 單位代碼 not in ( select 單位代碼 from 公用事業單位基本情況表 where 統計年份='+ComboBox2.Text+')';
        ADOQuery1.SQL.Add(sqlstr);
        ADOQuery1.Open;
        LView1Record:=ADOQuery1.RecordCount;

        if LView1Record<>0 then
        begin
                BitBtn1.Enabled:=true;
                BitBtn3.Enabled:=true;
                ADOQuery1.First;
                for i:=1 to LView1Record do
                begin
                        aditems:=ListView1.Items.Add;
                        aditems.Caption:=ADOQuery1.fieldbyname('單位代碼').AsString;
                        aditems.SubItems.Add(ADOQuery1.fieldbyname('單位名稱').AsString);
                        ADOQuery1.Next;
                end;
        end;

        EprCode:='';
        EprName:='';
        CheckStatus(LView1Record,LView2Record);
end;

procedure TEpr_Select2.BitBtn3Click(Sender: TObject);
begin
        listview1.Selected := nil;
        ListView1.Items.Clear;
        listview2.Selected := nil;
        ListView2.Items.Clear;
        ComboBox1.Text:='';
        LView1Record:=0;
        LView2Record:=0;
        sString:='';
        EprCode:='';
        EprName:='';
        BitBtn1.Enabled:=False;
        BitBtn2.Enabled:=True;
        BitBtn3.Enabled:=False;
        CheckStatus(LView1Record,LView2Record);
end;

end.