unit Car4ss63;

interface

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

type
    Tf_Car4ss63 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    GroupBox1: TGroupBox;
    ListView1: TListView;
    ComboBox2: TComboBox;
    Label1: TLabel;
    Button6: TButton;
    Label4: TLabel;
    ComboBox1: TComboBox;
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure ListView1CustomDrawItem(Sender: TCustomListView;
      Item: TListItem; State: TCustomDrawState; var DefaultDraw: Boolean);
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);

  private
    { Private declarations }
  public
    { Public declarations }

  end;

var
  f_Car4ss63: Tf_Car4ss63;


implementation

uses data, main,RealtyPrint1;

{$R *.dfm}

procedure Tf_Car4ss63.FormCreate(Sender: TObject);
var
    Query:TADOQuery;
begin
    Query:=TADOQuery.Create(nil);
    Query.Connection:=DataModuleADO.ADOConnection1;
    Query.SQL.Add('select * from 客戶資料');
    Query.Open ;
    while not query.Eof do
    begin
        ComboBox2.Items.Add(Query.FieldByName('客戶名稱').Value);
        query.Next;
    end;

    query.close;
    query.free;
end;

procedure Tf_Car4ss63.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_Car4ss63.ListView1CustomDrawItem(Sender: TCustomListView;
  Item: TListItem; State: TCustomDrawState; var DefaultDraw: Boolean);
begin
    if   Item.Selected = true then
    begin
          Sender.Canvas.Brush.Color   :=   clMenuHighlight;
          (Sender   as   TListView).Canvas.Font.Color   :=  clWhite;
    end;
end;

//查找
procedure Tf_Car4ss63.Button1Click(Sender: TObject);
var
    Query,query1,query2:TADOQuery;
    i,j:integer;
    s1,s2:string;
    d1,d2,d3,d4:double;
    c1,c2,c3,c4:double;
begin
    ListView1.Items.Clear;
    Query:=TADOQuery.Create(nil);
    Query.Connection:=DataModuleADO.ADOConnection1;
    Query1:=TADOQuery.Create(nil);
    Query1.Connection:=DataModuleADO.ADOConnection1;
    Query2:=TADOQuery.Create(nil);
    Query2.Connection:=DataModuleADO.ADOConnection1;

    c1:=0;
    c2:=0;
    c3:=0;
    c4:=0;



    Query.SQL.Add('select DISTINCT 客戶名稱 from 應收款 where 客戶名稱  like ''%'+ComboBox2.Text+'%'' and 付款類別 like ''%'+ComboBox1.Text+'%''');
    Query.Open ;
    while  not query.Eof do
    begin
        d1:=0;
        d2:=0;
        d3:=0;
        d4:=0;

        query1.SQL.Clear;
        Query1.SQL.Add('select * from 應收款 where 客戶名稱 ='''+Query.FieldByName('客戶名稱').Value+''' and 付款類別 like ''%'+ComboBox1.Text+'%''');
        Query1.Open ;
        while  not query1.Eof do
        begin
            d1:=d1+Query1.FieldByName('應收金額').AsVariant;
            d2:=d2+Query1.FieldByName('已收金額').AsVariant;
            d3:=d3+Query1.FieldByName('未收金額').AsVariant;
            d4:=d2+Query1.FieldByName('已退金額').AsVariant;

            s1:= Query1.FieldByName('聯系人').Value;
            s2:= Query1.FieldByName('聯系電話').Value;
            
            query1.Next ;
        end;

        if d1<>0 then
        begin
            with ListView1.Items.Add do
            begin
                Caption:=Query.FieldByName('客戶名稱').Value;
                SubItems.Add(s1);
                SubItems.Add(s2);
                SubItems.Add(floattostr(d1));
                SubItems.Add(floattostr(d2));
                SubItems.Add(floattostr(d4));
                SubItems.Add(floattostr(d3));
            end;
        end;

        c1:=c1+d1;
        c2:=c2+d2;
        c3:=c3+d3;
        c4:=c4+d4;

        query.Next ;
    end;
    query.close;
    query.free;
    query1.close;
    query1.free;

    if ListView1.Items.Count>0 then
    begin
        ListView1.Items.Item[0].Selected := True;

        with ListView1.Items.Add do
        begin
            Caption:='小計匯總';
            SubItems.Add('');
            SubItems.Add('');
            SubItems.Add(floattostr(c1));
            SubItems.Add(floattostr(c2));
            SubItems.Add(floattostr(c4));
            SubItems.Add(floattostr(c3));
        end;
    end;
end;

//退出
procedure Tf_Car4ss63.Button6Click(Sender: TObject);
begin
    close;
end;

procedure Tf_Car4ss63.Button2Click(Sender: TObject);
var
    i:integer;
    Query:TADOQuery;
    s:string;
begin
    Query:=TADOQuery.Create(nil);
    Query.Connection:=DataModuleADO.ADOConnection1;

    DataModuleADO.ADOConnection1.BeginTrans;
    Query.SQL.Text:='delete from temp';
    query.ExecSQL;
    DataModuleADO.ADOConnection1.CommitTrans;

    for i:=0 to listview1.Items.Count-1 do
    begin
        s := 'insert into temp(列1,列2,列3,列4,列5,列6)';
        s :=s+' values(:s1,:s2,:s3,:s4,:s5,:s6)';

        query.sql.Clear ;
        query.sql.add(s);
        query.Parameters.ParamByName('s1').Value:=listview1.Items.Item[i].Caption;
        query.Parameters.ParamByName('s2').Value:=listview1.Items.Item[i].SubItems[0];
        query.Parameters.ParamByName('s3').Value:=listview1.Items.Item[i].SubItems[1];
        query.Parameters.ParamByName('s4').Value:=listview1.Items.Item[i].SubItems[2];
        query.Parameters.ParamByName('s5').Value:=listview1.Items.Item[i].SubItems[3];
        query.Parameters.ParamByName('s6').Value:=listview1.Items.Item[i].SubItems[4];

        DataModuleADO.ADOConnection1.BeginTrans;
        query.execsql;
        DataModuleADO.ADOConnection1.CommitTrans;
    end;

    query.Close;
    query.Free;

    sFilep:='Car4ss63.fr3';

    sTitlep:=Caption;
    sPrintc:='select * from temp';
    f_RealtyPrint1:=Tf_RealtyPrint1.Create(self);
    f_RealtyPrint1.showmodal;
end;

procedure Tf_Car4ss63.Button5Click(Sender: TObject);
begin
    //mainform.showChmHelp('03-09-03.htm');
end;

end.