uqueryallout.pas

港口货物管理系统

unit Uqueryallout;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBTables, ComCtrls, Grids, DBGrids, StdCtrls, ExtCtrls,
  Buttons, ImgList,DateUtils;

type
  Tfmqueryallout = class(TForm)
    TreeView1: TTreeView;
    DataSource1: TDataSource;
    Query1: TQuery;
    Query2: TQuery;
    DBGrid1: TDBGrid;
    Bevel2: TBevel;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    Query3: TQuery;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    Label1: TLabel;
    Label3: TLabel;
    Table2: TTable;
    StatusBar1: TStatusBar;
    Label2: TLabel;
    Query4: TQuery;
    GroupBox1: TGroupBox;
    ImageList1: TImageList;
    Bevel1: TBevel;

    procedure ComboBox1Click(Sender: TObject);
    procedure ComboBox2Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure TreeView1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);



  private
    { Private declarations }
  public
    node:ttreenode;{ Public declarations }
  end;

var
  fmqueryallout: Tfmqueryallout;

implementation

uses ulogin, udm_user, uprintdbgrid;

{$R *.dfm}





procedure Tfmqueryallout.ComboBox1Click(Sender: TObject);
var i:integer;
    dateyear,qstr1,qstr2:string;
begin
query1.Close;
query1.SQL.clear;

dateyear:=formatdatetime('yyyy',now);
qstr1:='select distinct atname from sumexportall where (ddate like '+''''+dateyear+'-'+combobox1.text+'%'+'''';

if combobox1.ItemIndex<>12 then
begin
query1.SQL.add(qstr1);
query1.sql.add(')');
end
else
query1.SQL.add('select  distinct atname from sumexportall where (ddate is not null)');
if combobox2.ItemIndex<>query4.RecordCount then
begin
query1.sql.Add(' and (agoodd='''+combobox2.Text+'''');
query1.SQL.add(')');
end
else
query1.sql.Add(' and (agoodd is not null)');
query1.ExecSQL;
treeview1.Items.Clear;
node:=treeview1.Items.Add(nil,'出货工具名称');
query1.Open;
query1.First;
for i:=1 to query1.RecordCount do
begin
treeview1.Items.AddChild(node,query1.FieldValues['atname']);
query1.next;
end;



query1.Open;
query1.First;
while not query1.Eof
do
begin
query2.Close;
query2.sql.Clear;
qstr2:='select distinct agname from sumexportall where (ddate like '+''''+dateyear+'-'+combobox1.text+'%'+'''';

if combobox1.ItemIndex<>12 then
query2.sql.add(qstr2)
else
query2.SQL.add('select distinct agname from sumexportall where (ddate is not null');
if combobox2.ItemIndex<>query4.RecordCount then
query2.sql.Add(') and (agoodd='''+combobox2.Text+'''')
else
query2.sql.Add(') and (agoodd is not null');
query2.sql.add(') and (atname='''+query1.FieldValues['atname']+'''');
query2.SQL.add(') order by agname');
query2.ExecSQL;

query2.Open;
query2.First;
while not query2.Eof do
begin
treeview1.Items.Addchild(node.Item[query1.recno-1],query2.FieldValues['agname']);
query2.Next;
end;
query1.Next;
end;
end;

procedure Tfmqueryallout.ComboBox2Click(Sender: TObject);
begin
combobox1.OnClick(sender);
end;

procedure Tfmqueryallout.FormCreate(Sender: TObject);
var i:integer;
    qstr1,dateyear:string;
begin
application.OnMessage:=fmlogin.doenterastab;
query4.First;
for i:=1 to query4.RecordCount do
begin
combobox2.Items.add(query4.FieldValues['agoodd']);
query4.Next;
end;
combobox2.Items.Add('所有货代');
combobox2.ItemIndex:=0;


combobox1.ItemIndex:=strtoint(formatdatetime('mm',now))-1;
dateyear:=formatdatetime('yyyy',now);
qstr1:='select distinct atname from sumexportall where (ddate like '+''''+dateyear+'-'+combobox1.text+'%'+'''';
query1.Close;
query1.SQL.clear;
query1.sql.add(qstr1);
query1.sql.add(')');
query1.sql.Add(' and (agoodd='''+combobox2.Text+'''');
query1.SQL.add(')');
query1.ExecSQL;

node:=treeview1.Items.Add(nil,'出货工具名称');
query1.Open;

query1.First;
for i:=1 to query1.RecordCount do
begin
treeview1.Items.AddChild(node,query1.FieldValues['atname']);
query1.next;
end;


query1.Open;
query1.First;
while not query1.Eof
do
begin
query2.Close;
query2.sql.Clear;
query2.SQL.add('select distinct agname from sumexportall where (ddate is not null');
query2.SQL.add(') and (atname='''+query1.FieldValues['atname']+'''');
query2.sql.add(')');
query2.sql.Add(' and (agoodd='''+combobox2.Text+'''');
query2.SQL.add(')');
query2.SQL.add(' order by agname');
query2.ExecSQL;
query2.Open;
query2.First;

while not query2.Eof do
begin
treeview1.Items.Addchild(node.Item[query1.recno-1],query2.FieldValues['agname']);
query2.Next;
end;
query1.Next;
end;

end;


procedure Tfmqueryallout.TreeView1Click(Sender: TObject);
var a:string;
    b:string;
    i,oic,oic1:integer;
    oit,oit1:single;
    dateyear,qstr1,qstr2:string;
begin
dateyear:=formatdatetime('yyyy',now);
qstr1:=' where (ddate like '+''''+dateyear+'-'+combobox1.text+'%'+'''';
qstr2:=' and (ddate like '+''''+dateyear+'-'+combobox1.text+'%'+'''';
a:=treeview1.Selected.Text;
if (treeview1.Selected<>node) then
if treeview1.Selected.TreeView.Selected.Text<>null then
begin
b:=trim(treeview1.Selected.Parent.Text);
query3.Close;
query3.sql.clear;
query3.SQL.add('select ddate as 出货日期,atname as 出货工具名,atnum as 提单号,agname as 货名,adime as 规格,ncoil as 出货件数,nton as 出货吨位');
query3.SQL.Add(' ,agoodd as 货代,adir as 流向 from sumexportall where (atname='''+b+'''');
query3.sql.Add(') and (agname='''+a+'''');
query3.sql.Add(')');
if combobox1.ItemIndex<>12 then
query3.sql.add(qstr2)
else
query3.SQL.add(' and (ddate is not null');
query3.SQL.add(')');

if combobox2.ItemIndex<>query4.RecordCount then
query3.SQL.add(' and (agoodd='''+combobox2.Text+'''')
else
query3.SQL.add(' and (agoodd is not null');
query3.SQL.add(')');


query3.sql.Add(' order by ddate,atname,adime,agoodd');
query3.Open;
end;

if  treeview1.Selected.Parent=node then
begin
b:=treeview1.Selected.Text;
query3.Close;
query3.sql.clear;
query3.SQL.add('select ddate as 出货日期,atname as 出货工具名,atnum as 提单号,agname as 货名,adime as 规格,ncoil as 出货件数,nton as 出货吨位');
query3.SQL.Add(' ,agoodd as 货代,adir as 流向 from sumexportall  where (atname='''+b+'''');
query3.sql.Add(')');
if combobox1.ItemIndex<>12 then
query3.sql.add(qstr2)
else
query3.SQL.add(' and (ddate is not null');
query3.SQL.add(')');
if combobox2.ItemIndex<>query4.RecordCount then
query3.SQL.add(' and (agoodd='''+combobox2.Text+'''')
else
query3.SQL.add(' and (agoodd is not null');
query3.SQL.add(')');
query3.sql.Add(' order by ddate,atname,agname,adime,agoodd');
query3.Open;
end

else if  treeview1.Selected=node
then
begin
query3.Close;
query3.sql.clear;
query3.SQL.add('select ddate as 出货日期,atname as 出货工具名,atnum as 提单号 ,agname as 货名,adime as 规格,ncoil as 出货件数,nton as 出货吨位');
query3.SQL.Add(' ,agoodd as 货代,adir as 流向 from sumexportall ');
if combobox1.ItemIndex<>12 then
query3.SQL.add(qstr1)
else
query3.SQL.add(' where (ddate is not null');
query3.SQL.add(')');
if combobox2.ItemIndex<>query4.RecordCount then
query3.SQL.add(' and (agoodd='''+combobox2.Text+'''')
else
query3.SQL.add(' and (agoodd is not null');
query3.SQL.add(')');
query3.sql.Add(' order by ddate,atname,agname,adime,agoodd');
query3.Open;
end;

oic:=0;
oit:=0.000;
oic1:=0;
oit1:=0.000;
query3.First;
while not query3.Eof do
begin
if query3.FieldByName('出货件数').Value=null then oic1:=0 else oic1:=query3.FieldByName('出货件数').Value;
oic:=oic+oic1;
if query3.FieldByName('出货吨位').Value=null then oit1:=0 else oit1:=query3.FieldByName('出货吨位').Value;
oit:=oit+oit1;
query3.Next;
end;
for i:=0 to dbgrid1.Columns.Count-1 do
begin
dbgrid1.Columns[i].Title.Alignment:=taCenter;
end;
statusbar1.Panels[1].Text:='记录数: '+inttostr(query3.RecordCount)+'个记录';
statusbar1.Panels[2].Text:='出货件数: '+inttostr(oic)+' 件';
statusbar1.Panels[3].Text:='出货吨位: '+formatfloat('0.000',oit)+' 吨';

end;

procedure Tfmqueryallout.BitBtn2Click(Sender: TObject);
begin
fmqueryallout.Close;
end;

procedure Tfmqueryallout.FormActivate(Sender: TObject);
begin
treeview1.OnClick(sender);
end;

procedure Tfmqueryallout.BitBtn1Click(Sender: TObject);
begin
 fmuser.fmid:=2;
application.CreateForm(tfmprintdbgrid,fmprintdbgrid);
fmprintdbgrid.showmodal;
end;

end.