unit RealtyC441;

interface

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

type
    Tf_RealtyC441 = class(TForm)
    Label1: TLabel;
    Edit1: TEdit;
    Edit2: TEdit;
    Label3: TLabel;
    Edit3: TEdit;
    Label4: TLabel;
    Edit7: TEdit;
    edit6: TComboBox;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    DateTimePicker3: TDateTimePicker;
    Edit8: TEdit;
    Edit9: TEdit;
    Edit10: TEdit;
    Edit11: TEdit;
    DateTimePicker4: TDateTimePicker;
    Edit12: TEdit;
    Label10: TLabel;
    Edit13: TEdit;
    Label11: TLabel;
    Edit14: TEdit;
    Label12: TLabel;
    Edit15: TEdit;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    Label13: TLabel;
    Edit16: TEdit;
    Label14: TLabel;
    Edit17: TEdit;
    Label15: TLabel;
    Edit18: TEdit;
    Label16: TLabel;
    Label17: TLabel;
    Label18: TLabel;
    Label19: TLabel;
    Label20: TLabel;
    Label21: TLabel;
    Label22: TLabel;
    Label23: TLabel;
    Label24: TLabel;
    Label25: TLabel;
    Edit19: TEdit;
    Edit20: TEdit;
    Label26: TLabel;
    Label27: TLabel;
    Label28: TLabel;
    Label29: TLabel;
    Label30: TLabel;
    Button5: TButton;
    Button7: TButton;
    Button1: TButton;
    Button2: TButton;
    edit5: TComboBox;
    Edit21: TEdit;
    Label31: TLabel;
    DateTimePicker5: TDateTimePicker;
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure GetID;
    procedure FormCreate(Sender: TObject);
    procedure Button7Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Edit9Exit(Sender: TObject);

    procedure Edit12Exit(Sender: TObject);
    procedure Edit13Exit(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure edit5Select(Sender: TObject);
    procedure edit6Exit(Sender: TObject);
    procedure Edit7Exit(Sender: TObject);
    procedure Edit11Exit(Sender: TObject);
    procedure Edit16Exit(Sender: TObject);

  private
    { Private declarations }
  public
    { Public declarations }
     bMod:bool;
     sTable:string;
     cols:integer;
  end;


var
  f_RealtyC441: Tf_RealtyC441;
implementation
uses data, main,RealtyC44,RealtyB22,RealtyA14;
{$R *.dfm}
//Initial******************************
procedure Tf_RealtyC441.FormCreate(Sender: TObject);
Var
    query,query1:TADOQuery;
    s1,s2,s3,s4:string;
begin
    query:=TADOQuery.Create(nil);
    query.Connection:=DataModuleADO.ADOConnection1;
    query1:=TADOQuery.Create(nil);
    query1.Connection:=DataModuleADO.ADOConnection1;

    query.SQL.Add('select * from 收費項目 where 費用項目類型='''+'其它收費項目'+'''');
    query.Open;
    while not query.Eof do
    begin
        Edit5.Items.Add(query.fieldbyname('編號').Value+'--'+query.fieldbyname('名稱').Value);
        query.Next;
    end;  


    if f_RealtyC44.bMod=false then
    begin
        GetID;
        Datetimepicker1.Date:=date;
        Datetimepicker2.Date:=date;
        Datetimepicker3.Date:=date;
        Datetimepicker4.Date:=date;
        Datetimepicker5.Date:=date;
        edit18.text:=czy;
    end;

    if f_RealtyC44.bMod then
    begin
        if f_RealtyC44.ListView1.Selected.SubItems[32]='Y' then button7.Enabled:=false;
        query.sql.Clear;
        query.SQL.add('select * from 其它費用 where 單號='''+mainform.t1+'''');
        query.Open;
        if query.RecordCount>0 then
        begin
            query1.sql.Clear;
            query1.SQL.add('select * from 房間 where 編號='''+query.fieldbyname('房間').value+'''');
            query1.Open;
            if query1.RecordCount>0 then   s1:=query1.fieldbyname('編號').value+'--'+query1.fieldbyname('名稱').Value;

            query1.sql.Clear;
            query1.SQL.add('select * from 客戶資料 where 客戶代碼='''+query.fieldbyname('客戶').value+'''');
            query1.Open;
            if query1.RecordCount>0 then   s2:=query1.fieldbyname('客戶代碼').value+'--'+query1.fieldbyname('客戶名稱').Value;

            query1.sql.Clear;
            query1.SQL.add('select * from 收費項目 where 編號='''+query.fieldbyname('收費項目').value+'''');
            query1.Open;
            if query1.RecordCount>0 then   s3:=query1.fieldbyname('編號').value+'--'+query1.fieldbyname('名稱').Value;

            query1.sql.Clear;
            query1.SQL.add('select * from 收費標準 where 編號='''+query.fieldbyname('收費標準').value+'''');
            query1.Open;
            if query1.RecordCount>0 then   s4:=query1.fieldbyname('編號').value+'--'+query1.fieldbyname('名稱').Value;

            edit1.Text:=query.fieldbyname('單號').Value;
            edit2.Text:=s1;
            edit3.Text:=s2;
            edit5.ItemIndex:=edit5.Items.IndexOf(s3);

            edit6.Items.Add(s4);
            edit6.ItemIndex:=edit6.Items.IndexOf(s4);

            datetimepicker1.date:=strtodate(query.fieldbyname('應收日期').Value);
            datetimepicker2.date:=strtodate(query.fieldbyname('計費開始日期').Value);
            datetimepicker3.Date:=strtodate(query.fieldbyname('計費結束日期').Value);

            datetimepicker5.Date:=strtodate(query.fieldbyname('收費月份').Value+'-01');

            edit7.Text:=query.fieldbyname('單價').Value;
            edit8.Text:=query.fieldbyname('金額').Value;

            edit9.Text:=query.fieldbyname('優惠金額').Value;
            edit10.Text:=query.fieldbyname('優惠后金額').Value;
            datetimepicker4.Date:=strtodate(query.fieldbyname('滯納金截至日期').Value);
            edit11.Text:=query.fieldbyname('滯納金天數').Value;
            edit12.Text:=query.fieldbyname('滯納金').Value;

            edit13.Text:=query.fieldbyname('減免繳滯納金').Value;
            edit14.Text:=query.fieldbyname('應收滯納金').Value;
            if query.fieldbyname('收取滯納金').Value<>'N' then checkbox1.Checked:=true
            else checkbox1.Checked:=false;
            edit15.Text:=query.fieldbyname('應收金額').Value;
            edit16.Text:=query.fieldbyname('已收金額').Value;

            if query.fieldbyname('已收全').Value<>'N' then checkbox2.Checked:=true
            else checkbox2.Checked:=false;
            edit21.text:=query.fieldbyname('收全日期').Value;

            edit17.Text:=query.fieldbyname('備注').Value;
            edit18.Text:=query.fieldbyname('制單人').Value;
            edit19.Text:=czy;
            edit20.Text:=query.fieldbyname('審核人').Value;
        end;
    end;
    query1.close;
    query1.free;
    query.close;
    query.free;
end;

procedure Tf_RealtyC441.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, 7);
        i:= strtoint(s)+1;
        if i<10 then edit1.text := '000000'+inttostr(i)
        else if (i > 9) and (i<100) then edit1.text := '00000'+inttostr(i)
        else if (i > 99) and (i<1000) then edit1.text := '0000'+inttostr(i)
        else if (i > 999) and (i<10000) then edit1.text := '000'+inttostr(i)
        else if (i > 9999) and (i<100000) then edit1.text := '00'+inttostr(i)
        else if (i > 99999) and (i<1000000) then edit1.text := '0'+inttostr(i)
        else if i>999999 then edit1.text := inttostr(i);
    end
    else  edit1.text := '0000001';

    query.Close;
    query.free;
end;

//Tab****************************************
procedure Tf_RealtyC441.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;

//FreeForm**********************************
procedure Tf_RealtyC441.FormClose(Sender: TObject; var Action: TCloseAction);
begin
    Action := caFree;
end;

//Close*************************************
procedure Tf_RealtyC441.Button7Click(Sender: TObject);
Var
    query,query1:TADOQuery;
    s,s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,r1,r2,rr,r3,r5:string;
    i,j:integer;
begin
    r1:=copy(edit3.text,1,7);
    r3:=mainform.getNum(edit5.text);
    r5:=mainform.getNum(edit6.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
    begin
        showmessage('客戶不能為空!');
        edit3.SetFocus;
        exit;
    end;

    if edit5.Text='' then
    begin
        showmessage('收費項目不能為空!');
        edit5.SetFocus;
        exit;
    end;

    if edit13.Text='' then
    begin
        showmessage('金額不能為空!');
        edit13.SetFocus;
        exit;
    end;

    if edit17.text='' then edit17.Text:='-';
    if edit19.text='' then edit19.Text:='-';
    if edit21.text='' then edit21.Text:='-';

    r1:=copy(edit3.Text,1,7);
    r3:=mainform.getNum(edit5.Text);
    r5:=mainform.getNum(edit6.text);

    edit7.Text:=mainform.stddou2(edit7.text);
    edit8.Text:=mainform.stddou2(edit8.text);
    edit9.Text:=mainform.stddou2(edit9.text);
    edit10.Text:=mainform.stddou2(edit10.text);
    edit11.Text:=mainform.stddou2(edit11.text);
    edit12.Text:=mainform.stddou2(edit12.text);
    edit13.Text:=mainform.stddou2(edit13.text);
    edit14.Text:=mainform.stddou2(edit14.text);
    edit15.Text:=mainform.stddou2(edit15.text);
    edit16.Text:=mainform.stddou2(edit16.text);

    if checkbox1.Checked then s1:='Y' else s1:='N';
    if checkbox2.Checked then s2:='Y' else s2:='N';

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

    if f_RealtyC44.bMod then
    begin
        if mainform.t1<>edit1.Text then
        begin
            query.SQL.Clear;
            query.SQL.add('select * from 其它費用 where 單號='''+edit1.Text+'''');
            query.open;
            if query.RecordCount>0 then
            begin
                showmessage('單號重復!');
                edit1.SetFocus;
                query.close;
                query.free;
                exit;
            end;
        end;

        s:='update  其它費用 set 單號='''+edit1.Text;
        s:=s+''',房間='''+mainform.getNum(edit2.Text);
        s:=s+''',客戶='''+r1;
        s:=s+''',收費項目='''+r3;
        s:=s+''',收費標準='''+r5;

        s:=s+''',應收日期='''+datetostr(datetimepicker1.Date);
        s:=s+''',計費開始日期='''+datetostr(datetimepicker2.Date);
        s:=s+''',計費結束日期='''+datetostr(datetimepicker3.Date);
        s:=s+''',單價='+edit7.Text;
        s:=s+',金額='+edit8.Text;

        s:=s+',優惠金額='+edit9.Text;
        s:=s+',優惠后金額='+edit10.Text;
        s:=s+',滯納金截至日期='''+datetostr(datetimepicker4.Date);
        s:=s+''',滯納金天數='+edit11.Text;
        s:=s+',滯納金='+edit12.Text;

        s:=s+',減免繳滯納金='+edit13.Text;
        s:=s+',應收滯納金='+edit14.Text;
        s:=s+',收取滯納金='''+s1;
        s:=s+''',應收金額='+edit15.Text;
        s:=s+',已收金額='+edit16.Text;

        s:=s+',已收全='''+s2;
        s:=s+''',收全日期='''+edit21.text;
        s:=s+''',備注='''+edit17.text;
        s:=s+''',制單人='''+edit18.text;
        s:=s+''', 修訂人='''+edit19.text;
        s:=s+''' where 單號='''+mainform.t1+'''';

        query.SQL.Clear;
        query.sql.Add(s);
        DataModuleADO.ADOConnection1.BeginTrans;
        query.ExecSQL;
        DataModuleADO.ADOConnection1.CommitTrans;

        query.sql.clear;
        Query.SQL.Add('select * from 其它費用 where 單號 ='''+edit1.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 s1:=query1.fieldbyname('客戶名稱').Value;

             query1.SQL.clear;
             query1.SQL.add('select * from 房間 where 編號='''+query.fieldbyname('房間').Value+'''');
             query1.Open;
             if query1.RecordCount>0 then
             begin
                rr:=query1.fieldbyname('名稱').Value;
                 s2:=query1.fieldbyname('樓宇').Value;
                 s3:=mainform.getName('樓宇',s2);
                 s4:=query1.fieldbyname('管理區').Value;
                 s5:=mainform.getName('管理區',s4);
                 s6:=query1.fieldbyname('管理處').Value;
                 s7:=mainform.getName('組織機構',s6);
                 s10:=query1.fieldbyname('使用面積').value;
             end;

             query1.SQL.clear;
             query1.SQL.add('select * from 收費項目 where 編號='''+query.fieldbyname('收費項目').Value+'''');
             query1.Open;
             if query1.RecordCount>0 then s8:=query1.fieldbyname('名稱').Value;

             query1.SQL.clear;