//------------------------------------------------------------
//      作者:曾慶順
//      模塊:數據表的選擇窗口
//      時間：2002.09.17
//      功能介紹：
//-----------------------------------------------------------
unit xt_bffa_frm_Add;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Buttons, ExtCtrls, Db, DBTables;

type
  Txt_bffa_Add = class(TForm)
    Panel1: TPanel;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    GroupBox1: TGroupBox;
    GroupBox2: TGroupBox;
    SListBox: TListBox;
    DListBox: TListBox;
    RSpeedButton: TSpeedButton;
    LSpeedButton: TSpeedButton;
    RASpeedButton: TSpeedButton;
    LASpeedButton: TSpeedButton;
    Query: TQuery;
    procedure SListBoxDragOver(Sender, Source: TObject; X, Y: Integer;
      State: TDragState; var Accept: Boolean);
    procedure SListBoxDragDrop(Sender, Source: TObject; X, Y: Integer);
    procedure DListBoxDragDrop(Sender, Source: TObject; X, Y: Integer);
    procedure RSpeedButtonClick(Sender: TObject);
    procedure LSpeedButtonClick(Sender: TObject);
    procedure RASpeedButtonClick(Sender: TObject);
    procedure LASpeedButtonClick(Sender: TObject);
    procedure DListBoxDragOver(Sender, Source: TObject; X, Y: Integer;
      State: TDragState; var Accept: Boolean);
    procedure SListBoxDblClick(Sender: TObject);
    procedure DListBoxDblClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
  private
    { Private declarations }
    //數據表的中文名字和英文名字列表
    FNList,FCList:TStringList;
    Function GetTCode(TName:string):string;
    Function ExecQuery(PQuery:TQuery;PSql:string):Boolean;
    //得到5位
    Function GetFieldMaxValues(TableName,DBName,FieldName:string;n:integer;Filter:string=''):string;overload;
  public
    { Public declarations }
    Fabh:string;
    FSsql,FDsql:string;
  end;

var
  xt_bffa_Add: Txt_bffa_Add;

implementation

{$R *.DFM}
uses dmmain;

Function Txt_bffa_add.GetTCode(TName:string):string;
var index:integer;
begin
   index:=FNList.IndexOf(TName);
   if index <> -1 then
      result:=FCList.Strings[index]
   else
      result:='';
end;

procedure Txt_bffa_Add.FormCreate(Sender: TObject);
begin

  FNList:=TStringList.Create;
  FCList:=TStringList.Create;
  //加入名字和代碼
  try
    Query.close;
    Query.sql.Clear;
    Query.sql.Add('select * from USER_TAB_COMMENTS where COMMENTS is not null');
    Query.Open;
    Query.First;
    FNList.Clear;
    FCList.Clear;
    While not Query.Eof do
    begin
      FNList.Add(Query.FieldByName('COMMENTS').asstring);
      FCList.Add(Query.FieldByName('TABLE_NAME').asstring);
      Query.Next;
    end;
   except
   end;
end;
procedure Txt_bffa_Add.FormDestroy(Sender: TObject);
begin
  FNList.Free;
  FCList.Free;

end;
procedure Txt_bffa_Add.FormShow(Sender: TObject);
begin
   SListBox.Clear;
   DListBox.Clear;
   if (FSsql = '') or (FDsql = '') then exit;
   //未選表
   try
    Query.close;
    Query.sql.Clear;
    Query.sql.Add(FSsql);
    Query.Open;
    Query.First;
    While not Query.Eof do
    begin
      SListBox.Items.Add(Query.Fields[0].asstring);
      Query.Next;
    end;
   except
    exit;
   end;
   //已選表
   try
    Query.close;
    Query.sql.Clear;
    Query.sql.Add(FDsql);
    Query.Open;
    Query.First;
    While not Query.Eof do
    begin
      DListBox.Items.Add(Query.Fields[0].asstring);
      Query.Next;
    end;
   except
    exit;
   end;
end;

procedure Txt_bffa_Add.SListBoxDragOver(Sender, Source: TObject; X,
  Y: Integer; State: TDragState; var Accept: Boolean);
begin
   Accept := (Source as TListBox) = DListBox;
end;

procedure Txt_bffa_Add.DListBoxDragOver(Sender, Source: TObject; X,
  Y: Integer; State: TDragState; var Accept: Boolean);
begin
   Accept := (Source as TListBox) = SListBox;
end;

procedure Txt_bffa_Add.SListBoxDragDrop(Sender, Source: TObject; X,
  Y: Integer);
begin
   LSpeedButtonClick(nil);
end;

procedure Txt_bffa_Add.DListBoxDragDrop(Sender, Source: TObject; X,
  Y: Integer);
begin
   RSpeedButtonClick(nil);
end;
procedure Txt_bffa_Add.SListBoxDblClick(Sender: TObject);
begin
   RSpeedButtonClick(nil);
end;

procedure Txt_bffa_Add.DListBoxDblClick(Sender: TObject);
begin
   LSpeedButtonClick(nil);
end;
procedure Txt_bffa_Add.RSpeedButtonClick(Sender: TObject);
var i:integer;
begin
  //往目標添加選中的項
  for i := 0 to (SListBox.Items.Count - 1) do
  begin
    if SListBox.Selected[i] then
    begin
      DListBox.Items.Add(SListBox.Items.Strings[i]);
    end;
  end;
  //刪除選中的項
  While SListBox.SelCount > 0 do
  begin
    for i := 0 to (SListBox.Items.Count - 1) do
    begin
      if SListBox.Selected[i] then
      begin
         SListBox.Items.Delete(i);
         break;
      end;
    end;
  end;
end;

procedure Txt_bffa_Add.LSpeedButtonClick(Sender: TObject);
var i:integer;
begin
  //往目標添加選中的項
  for i := 0 to (DListBox.Items.Count - 1) do
  begin
    if DListBox.Selected[i] then
    begin
      SListBox.Items.Add(DListBox.Items.Strings[i]);
    end;
  end;
  //刪除選中的項
  While DListBox.SelCount > 0 do
  begin
    for i := 0 to (DListBox.Items.Count - 1) do
    begin
      if DListBox.Selected[i] then
      begin
         DListBox.Items.Delete(i);
         break;
      end;
    end;
  end;
end;

procedure Txt_bffa_Add.RASpeedButtonClick(Sender: TObject);
var i:integer;
begin
  //往目標添加選中的項
  for i := 0 to (SListBox.Items.Count - 1) do
  begin
    DListBox.Items.Add(SListBox.Items.Strings[i]);
  end;
  SListBox.Clear;
end;

procedure Txt_bffa_Add.LASpeedButtonClick(Sender: TObject);
var i:integer;
begin
  //往目標添加選中的項
  for i := 0 to (DListBox.Items.Count - 1) do
  begin
    SListBox.Items.Add(DListBox.Items.Strings[i]);
  end;
  DListBox.Clear;
end;

procedure Txt_bffa_Add.BitBtn1Click(Sender: TObject);
var i:integer;
    jlid:string;
begin
  //先刪除就數據
  ExecQuery(Query,'delete from T_XTGL_FACB WHERE fabh='''+fabh+'''');
  //往目標添加選中的項
  for i := 0 to (DListBox.Items.Count - 1) do
  begin
    try
      Query.close;
      Query.sql.Clear;
      Query.sql.Add('insert into T_XTGL_FACB (FABH,JLID,BYWM,BZWM) values(:p1,:p2,:p3,:p4)');
      Query.ParamByName('p1').asstring:=Fabh;
      jlid:=GetFieldMaxValues('T_XTGL_FACB','db_vipdl','JLID',1,'WHERE fabh='''+fabh+'''');
      Query.ParamByName('p2').asstring:=inttostr((strtoint(jlid)+1));
      Query.ParamByName('p3').asstring:=GetTCode(DListBox.Items.Strings[i]);
      Query.ParamByName('p4').asstring:=DListBox.Items.Strings[i];
      Query.ExecSQL;
     except

     end;
  end;
  close;
end;

function Txt_bffa_Add.ExecQuery(PQuery: TQuery; PSql: string): Boolean;
begin
  Result:=true;
  PQuery.close;
  PQuery.sql.Clear;
  PQuery.Filtered:=false;
  PQuery.sql.add(PSql);
  try
    PQuery.ExecSQL;
  except
    Result:=false;
  end;
end;

function Txt_bffa_Add.GetFieldMaxValues(TableName, DBName,
  FieldName: string; n: integer; Filter: string): string;
var query:TQuery;
    FSql:string;
begin
  try
      query:=TQuery.Create(nil);
  except
      result:='';
      exit;
  end;
  query.DatabaseName:=DBname;
  query.close;
  query.sql.Clear;
  FSql:='select nvl(max(to_number('+FieldName+')),0) from '+TableName;
  if Filter<>'' then
     FSql:=Fsql+' '+filter;
  query.sql.Add(FSql);
  query.Prepare;
  try
     query.open;
  except
     result:='';
     exit;
  end;
  query.First;
  result:=Query.Fields[0].AsString;
  query.free;
end;

end.