unit pfm_viewxsrz;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ComCtrls, Buttons, Grids, DBGrids, Db, ADODB, DBTables,
pvar_lib;

type
  Tfm_viewxsrz = class(TForm)
    GroupBox1: TGroupBox;
    GroupBox2: TGroupBox;
    DTPicker1: TDateTimePicker;
    Label2: TLabel;
    BBtnView: TBitBtn;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Query1: TQuery;
    BBtnSetQry: TBitBtn;
    BBtnQuit: TBitBtn;
    GroupBox3: TGroupBox;
    Query2: TQuery;
    edit1: TEdit;
    procedure BBtnViewClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure BBtnQuitClick(Sender: TObject);
    procedure BBtnSetQryClick(Sender: TObject);
    procedure Query1AfterOpen(DataSet: TDataSet);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  fm_viewxsrz: Tfm_viewxsrz;

implementation

uses pfm_query;
var
gridcolname:TStringlist;
qry2sql:string;
{$R *.DFM}

procedure Tfm_viewxsrz.BBtnViewClick(Sender: TObject);
var
mysql,tmpstr:string;
i:integer;
begin

    datetimetostring(tmpstr,'yyyy-mm-dd',dtpicker1.datetime);
    mysql:='select * from tab_xsrz where convert(char(10),rq,23)='''+tmpstr+'''';
    qry2sql:='select sum(skje) as totalmon from tab_xsrz where convert(char(10),rq,23)='''+tmpstr+'''';
    query1.active:=false;
    query1.SQL.Clear;
    query1.sql.add(mysql);
    query1.active:=true;
    for i:=0 to dbgrid1.columns.count-1 do
    begin
        dbgrid1.columns[i].Title.caption:=gridcolname.values[dbgrid1.columns[i].Title.caption];
    end;
end;

procedure Tfm_viewxsrz.FormCreate(Sender: TObject);
var
i:integer;
mysql:string;
begin
    dtpicker1.DateTime:=date();
    //字段中文名
    gridcolname:=TStringlist.create;
    with gridcolname do
    begin
    add('lsh=流水號(hào)');
    add('xh=序號(hào)');
    add('spbm=商品編碼');
    add('sptxm=商品條形碼');
    add('spmc=商品名稱');
    add('ggxh=規(guī)格型號(hào)');
    add('dj=單價(jià)');
    add('yhfs=優(yōu)惠方式');
    add('yhj=優(yōu)惠價(jià)');
    add('spnum=商品數(shù)量');
    add('rq=銷售日期');
    add('skje=收款金額');
    add('skym=收款員碼');
    add('posid=POS機(jī)號(hào)');
    add('gzh=柜組號(hào)');
    add('id=自動(dòng)編號(hào)');
    end;
    if query1.active=false then
    begin
        query1.sql.Clear;
        mysql:='select * from tab_xsrz where 1=2';
        qry2sql:='select sum(skje) as totalmon from tab_xsrz where 1=2';
        Query1.SQL.add(mysql);
        query1.active:=true;
        for i:=0 to dbgrid1.columns.count-1 do
        begin
            dbgrid1.columns[i].Title.caption:=gridcolname.values[dbgrid1.columns[i].Title.caption];
        end;
    end;
end;

procedure Tfm_viewxsrz.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
    gridcolname.free;
    action:=cafree;
end;

procedure Tfm_viewxsrz.BBtnQuitClick(Sender: TObject);
begin
    close;
end;

procedure Tfm_viewxsrz.BBtnSetQryClick(Sender: TObject);
var
i:integer;
tmpstr,mysql:string;
begin

        for i:=0 to dbgrid1.Columns.Count-1 do
        begin
                mFieldCap[i]:=dbGrid1.Columns[i].Title.Caption;
                mFieldName[i]:=dbGrid1.Columns[i].fieldname;
                mFieldType[i]:=dbGrid1.Columns[i].Field.datatype;
                mFieldNum:=i;
        end;

        fm_query:=Tfm_query.Create(self);
        if fm_query.ShowModal=mrOK then
        begin
            query1.Close;
            query1.sql.Clear;
            datetimetostring(tmpstr,'yyyy-mm-dd',dtpicker1.datetime);
            mysql:='select * from tab_xsrz where convert(char(10),rq,23)='''+tmpstr+''' and '+sQuerySql;
            qry2sql:='select sum(skje) as totalmon from tab_xsrz where convert(char(10),rq,23)='''+tmpstr+''' and '+sQuerySql;
            Query1.SQL.add(mysql);
            query1.open;
            for i:=0 to dbgrid1.columns.count-1 do
            begin
                dbgrid1.columns[i].Title.caption:=gridcolname.values[dbgrid1.columns[i].Title.caption];
            end;
        end;
end;

procedure Tfm_viewxsrz.Query1AfterOpen(DataSet: TDataSet);
begin
    query2.active:=false;
    query2.sql.clear;
    query2.SQL.Add(qry2sql);
    query2.Active:=true;
    if not query2.eof then
    begin
        edit1.text:=format('%.2f',[query2.fields[0].asfloat]);
    end
    else
    begin
        edit1.text:='0.00';
    end;



end;

end.