unit RealtyB13A;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, dbcgrids, Grids, DBGrids, DB, ADODB, StdCtrls, Buttons, ExtCtrls,
  StrUtils, ExtDlgs, ComCtrls, math, Menus, ImgList, ToolWin;

type
    Tf_RealtyB13A = class(TForm)
    L1: TLabel;
    L2: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label9: TLabel;
    Edit1: TEdit;
    Edit2: TEdit;
    Label1: TLabel;
    Edit3: TEdit;
    combobox1: TComboBox;
    Button1: TButton;
    DateTimePicker1: TDateTimePicker;
    Button2: TButton;
    Label4: TLabel;
    Edit4: TEdit;
    ToolButton5: TButton;
    Button4: TButton;
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure FormCreate(Sender: TObject);
    procedure ToolButton10Click(Sender: TObject);
    procedure NN6Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure GetID;
    procedure Button4Click(Sender: TObject);
    procedure ToolButton5Click(Sender: TObject);
    procedure combobox1Select(Sender: TObject);

  private
    { Private declarations }
  public
    { Public declarations }
       bOK:bool;
  end;

var
  f_RealtyB13A: Tf_RealtyB13A;
  sNum:string;

implementation

uses data, main, RealtyA12,RealtyA14,RealtyB22,RealtyB41,Car4s931;

{$R *.dfm}

procedure Tf_RealtyB13A.FormCreate(Sender: TObject);
Var
      query,query1:TADOQuery;
      s,s1,s2:string;
      i:integer;
begin
     Query:=TADOQuery.Create(nil);
     Query.Connection:=DataModuleADO.ADOConnection1;
     Query1:=TADOQuery.Create(nil);
     Query1.Connection:=DataModuleADO.ADOConnection1;

     combobox1.Items.Add('新增遷入性質');

     query.SQL.add('select * from 輔助資料 where 類別='''+'遷入性質'+'''');
     query.Open;
     while not query.Eof do
     begin
          combobox1.Items.Add(query.fieldbyname('名稱').Value);
          query.Next;
     end;

    if f_RealtyB41.bMod=false then
    begin
        datetimepicker1.Date:=date;
        getID;
    end;

      if f_RealtyB41.bMod then
      begin
          if f_RealtyB41.ListView1.Selected.SubItems[13]='Y' then button4.Enabled:=false;
          
          query.SQL.Clear;
          query.SQL.Add('select * from 客戶遷入 where 編號='''+f_RealtyB41.ListView1.Selected.SubItems[12]+'''');
          query.Open;
          if query.RecordCount>0 then
          begin
              s2:=mainform.getName('房間',query.fieldbyname('房間').Value);
              edit4.Text:=query.fieldbyname('編號').Value;
              edit1.Text:=query.fieldbyname('房間').Value+'--'+s2;
              edit2.Text:=query.fieldbyname('客戶').Value;
              edit3.Text:=query.fieldbyname('遷入說明').Value;

              datetimepicker1.Date:=strtodate(query.fieldbyname('遷入日期').Value);
              combobox1.ItemIndex:=combobox1.Items.IndexOf(query.fieldbyname('遷入性質').value);
          end;
          query.Next;
      end;
end;

procedure Tf_RealtyB13A.GetID;
var
      query:TADOquery;
      s:string;
      i:integer;
begin
      Query:=TADOQuery.Create(nil);
      Query.Connection:=DataModuleADO.ADOConnection1;

      s := 'select top 1 * from 客戶遷入 ORDER BY 編號 DESC';

      Query.SQL.Add(s);
      Query.Open ;
      if query.recordcount > 0 then
      begin
          s:= Query.Fieldbyname('編號').AsString;
          s:= Copy(S, 1, 4);
          i:= strtoint(s)+1;
          if i<10 then edit4.text := '000'+inttostr(i)
          else if (i > 9) and (i<100) then edit4.text := '00'+inttostr(i)
          else if (i > 99) and (i<1000) then edit4.text := '0'+inttostr(i)
          else if i>999 then edit4.text := inttostr(i);
      end
      else  edit4.text := '0001';
      Query.Close;
      Query.Free;
end;

//Tab
procedure Tf_RealtyB13A.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
    if key=13 then
        Begin
            key:=0;
            perform(WM_NEXTDLGCTL,0,0);
        end
    else if key=VK_ESCAPE then close;
end;


procedure Tf_RealtyB13A.ToolButton10Click(Sender: TObject);
begin
    NN6Click(Sender);
end;

procedure Tf_RealtyB13A.NN6Click(Sender: TObject);
begin
    close;
end;

procedure Tf_RealtyB13A.Button1Click(Sender: TObject);
begin
     f_RealtyA14:=Tf_RealtyA14.Create(self);
     f_RealtyA14.ToolButton6.Visible:=true;
     f_RealtyA14.ShowModal;

     if  f_RealtyA14.bOk=true then
     begin
        edit1.Text:=f_RealtyA14.sNum+'--'+f_RealtyA14.sName;
     end;
end;

procedure Tf_RealtyB13A.Button2Click(Sender: TObject);
begin
     f_RealtyB22:=Tf_RealtyB22.Create(self);
     f_RealtyB22.ToolButton6.Visible:=true;
     f_RealtyB22.ShowModal;

     if  f_RealtyB22.bOk=true then
     begin
         edit2.Text:=f_RealtyB22.ListView1.Selected.SubItems[0];
     end;
end;

procedure Tf_RealtyB13A.Button4Click(Sender: TObject);
Var
    query,query1:TADOQuery;
    s,s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12:String;
    r1,r2:string;
    i:integer;
begin
    r1:=mainform.getNum(edit1.Text);

    if edit1.Text='' then
    begin
         showmessage('請輸入房間!');
         edit1.SetFocus;
         exit;
    end;

    if edit2.Text='' then
    begin
         showmessage('請輸入客戶!');
         edit2.SetFocus;
         exit;
    end;

    if edit3.Text='' then edit3.Text:='-';

    query:=TADOquery.Create(nil);
    query.Connection:=DataModuleADO.ADOConnection1;
    query1:=TADOquery.Create(nil);
    query1.Connection:=DataModuleADO.ADOConnection1;

    query.SQL.Clear;
    query.SQL.Add('select * from 房間 where 編號='''+r1+'''');
    query.Open;
    if query.RecordCount>0 then
    begin
        r2:=query.fieldbyname('管理處').Value;
    end;

    if f_realtyB41.bMod then
    begin
          s:='update 客戶遷入 set 編號='''+edit4.Text;
          s:=s+''',房間='''+r1;
          s:=s+''',客戶='''+edit2.Text;
          s:=s+''',遷入日期='''+datetostr(datetimepicker1.Date);
          s:=s+''',遷入性質='''+combobox1.Text;
          s:=s+''',遷入說明='''+edit3.Text;
          s:=s+''',管理處='''+r2;
          s:=s+''' where 編號='''+f_RealtyB41.ListView1.Selected.SubItems[12]+'''';

          DataModuleADO.ADOConnection1.BeginTrans;
          query.SQL.clear;
          query.SQL.Text:=s;
          query.ExecSQL;
          DataModuleADO.ADOConnection1.CommitTrans;

          query.SQL.Clear;
          Query.SQL.Add('select * from 客戶遷入 where 編號 ='''+edit4.Text+'''');
          Query.Open ;
          if query.RecordCount>0 then
          begin
              s1:='-';
              s2:='-';
              s3:='-';
              s4:='-';
              s5:='-';
              s6:='-';
              s7:='-';
              s8:='-';
              s9:='-';
              s10:='-';
              s11:='-';
              s12:='-';

              query1.sql.Clear;
              query1.SQL.Add('select * from 房間 where 編號='''+query.fieldbyname('房間').Value+'''');
              query1.Open;
              if query1.RecordCount>0 then
              begin
                  r1:=query1.fieldbyname('名稱').Value;
                  s1:=query1.fieldbyname('樓宇').Value;
                  s10:=query1.fieldbyname('管理區').Value;
                  s2:=query1.fieldbyname('建筑面積').Value;
              end;

              query1.sql.Clear;
              query1.SQL.Add('select * from 管理區 where 編號='''+s10+'''');
              query1.Open;
              if query1.RecordCount>0 then s11:=query1.fieldbyname('名稱').Value;

              query1.sql.Clear;
              query1.SQL.Add('select * from 樓宇 where 編號='''+s1+'''');
              query1.Open;
              if query1.RecordCount>0 then s12:=query1.fieldbyname('名稱').Value;


              query1.SQL.Clear;
              query1.SQL.Add('select * from 客戶資料 where 客戶名稱 ='''+query.fieldbyname('客戶').Value+'''');
              query1.Open;
              if query1.RecordCount>0 then
              begin
                  s3:=query1.fieldbyname('客戶代碼').Value;
                  s4:=query1.fieldbyname('電話').Value;
                  s5:=query1.fieldbyname('手機號').Value;
                  s6:=query1.fieldbyname('客戶類型').Value;
                  s7:=query1.fieldbyname('工作單位').Value;
              end;

              query1.sql.Clear;
              query1.SQL.Add('select * from 客戶遷出 where 遷入編號='''+query.fieldbyname('編號').Value+''' and 房間='''+query.fieldbyname('房間').Value+'''');
              query1.Open;
              if query1.RecordCount>0 then
              begin
                  s9:=query1.fieldbyname('遷出日期').Value;
              end;

              with f_realtyB41.ListView1.Selected do
              begin
                  Caption:=r1;
                  SubItems[0]:=s3;
                  SubItems[1]:=query.fieldbyname('客戶').Value;
                  SubItems[2]:=s2;
                  SubItems[3]:=Query.FieldByName('遷入性質').Value;

                  SubItems[4]:=Query.FieldByName('遷入日期').Value;
                  SubItems[5]:=s9;
                  SubItems[6]:=s4;
                  SubItems[7]:=s5;
                  SubItems[8]:=s6;
                  SubItems[9]:=s7;
                  SubItems[10]:=s12;
                  SubItems[11]:=s11;
                  SubItems[12]:=Query.FieldByName('編號').Value;
                  SubItems[13]:=Query.FieldByName('審核').Value;
              end;
          end;
          query1.Close;
          query1.Free;
          query.Close;
          query.Free;
          close;
    end
    else
    begin
          s:='insert into 客戶遷入 (編號,房間,客戶,遷入日期,遷入性質,遷入說明,管理處,審核)';
          s:=s+'values(:s1,:s2,:s3,:s4,:s5,:s6,:s7,:s8)';

          query.sql.Clear;
          query.SQL.Add(s);

          query.Parameters.ParamByName('s1').Value:=edit4.Text;
          query.Parameters.ParamByName('s2').Value:=r1;
          query.Parameters.ParamByName('s3').Value:=edit2.Text;
          query.Parameters.ParamByName('s4').Value:=datetostr(datetimepicker1.Date);
          query.Parameters.ParamByName('s5').Value:=combobox1.Text;
          query.Parameters.ParamByName('s6').Value:=edit3.Text;
          query.Parameters.ParamByName('s7').Value:=r2;
          query.Parameters.ParamByName('s8').Value:='N';

          DataModuleADO.ADOConnection1.BeginTrans;
          query.ExecSQL;
          DataModuleADO.ADOConnection1.CommitTrans;

          showmessage('保存成功!');

          query.sql.Clear;
          query.sql.add('select * from 客戶遷入 where 編號='''+edit4.text+'''');
          Query.Open ;
          if query.recordcount>0 then
          begin
              s1:='-';
              s2:='-';
              s3:='-';
              s4:='-';
              s5:='-';
              s6:='-';
              s7:='-';
              s8:='-';
              s9:='-';

              query1.sql.Clear;
              query1.SQL.Add('select * from 房間 where 編號='''+query.fieldbyname('房間').Value+'''');
              query1.Open;
              if query1.RecordCount>0 then
              begin
                  r1:=query1.fieldbyname('名稱').Value;
                  s1:=query1.fieldbyname('樓宇').Value;
                  s10:=query1.fieldbyname('管理區').Value;
                  s2:=query1.fieldbyname('建筑面積').Value;
              end;

              query1.sql.Clear;
              query1.SQL.Add('select * from 管理區 where 編號='''+s10+'''');
              query1.Open;
              if query1.RecordCount>0 then s11:=query1.fieldbyname('名稱').Value;

              query1.sql.Clear;
              query1.SQL.Add('select * from 樓宇 where 編號='''+s1+'''');
              query1.Open;
              if query1.RecordCount>0 then s12:=query1.fieldbyname('名稱').Value;


              query1.SQL.Clear;
              query1.SQL.Add('select * from 客戶資料 where 客戶名稱 ='''+query.fieldbyname('客戶').Value+'''');
              query1.Open;
              if query1.RecordCount>0 then
              begin
                  s3:=query1.fieldbyname('客戶代碼').Value;
                  s4:=query1.fieldbyname('電話').Value;
                  s5:=query1.fieldbyname('手機號').Value;
                  s6:=query1.fieldbyname('客戶類型').Value;
                  s7:=query1.fieldbyname('工作單位').Value;
              end;

              query1.sql.Clear;
              query1.SQL.Add('select * from 客戶遷出 where 遷入編號='''+query.fieldbyname('編號').Value+''' and 房間='''+query.fieldbyname('房間').Value+'''');
              query1.Open;
              if query1.RecordCount>0 then
              begin
                  s9:=query1.fieldbyname('遷出日期').Value;
              end;

              with f_realtyB41.ListView1.Items.Add do
              begin
                  Caption:=r1;
                  SubItems.Add(s3);
                  SubItems.Add(query.fieldbyname('客戶').Value);
                  SubItems.Add(s2);
                  SubItems.Add(Query.FieldByName('遷入性質').Value);

                  SubItems.Add(Query.FieldByName('遷入日期').Value);
                  SubItems.Add(s9);
                  SubItems.Add(s4);
                  SubItems.Add(s5);
                  SubItems.Add(s6);
                  SubItems.Add(s7);
                  SubItems.Add(s12);
                  SubItems.Add(s11);
                  SubItems.Add(Query.FieldByName('編號').Value);
                  SubItems.Add(Query.FieldByName('審核').Value);
              end;
          end;

          query1.Close;
          query1.Free;
          query.Close;
          query.Free;
          if f_realtyB41.listview1.Items.Count > 0 then f_realtyB41.ListView1.Items.Item[0].Selected := True;

          edit1.Text:='';
          edit2.Text:='';
          edit3.Text:='';
          edit4.text:='';
          datetimepicker1.date:=date;
          combobox1.ItemIndex:=combobox1.Items.IndexOf('');
    end;  
end;

procedure Tf_RealtyB13A.ToolButton5Click(Sender: TObject);
begin
    close;
end;

procedure Tf_RealtyB13A.combobox1Select(Sender: TObject);
begin
    if combobox1.Text='新增遷入性質' then
    begin
        mainform.t1:='遷入性質';
        f_Car4s931:=Tf_Car4s931.Create(self);
        f_Car4s931.showmodal;

        if f_Car4s931.bOK=true then
        begin
            combobox1.Items.Add(mainform.t1);
            combobox1.ItemIndex:=combobox1.Items.IndexOf(mainform.t1);
        end
        else combobox1.ItemIndex:=combobox1.Items.IndexOf('');
    end;
end;

end.