ufmqueryout1.pas

港口货物管理系统

unit ufmqueryout1;

interface

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

type
  Tfmqueryout1 = 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;
    BatchMove1: TBatchMove;
    Query5: TQuery;
    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 BitBtn1Click(Sender: TObject);
 


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

var
  fmqueryout1: Tfmqueryout1;

implementation

uses ulogin, udm_user,ufmqueryoutprivew, Uqueryallout;

{$R *.dfm}





procedure Tfmqueryout1.ComboBox1Click(Sender: TObject);
var i:integer;
    dateyear,qstr3,qstr1,qstr2:string;
begin
query5.close;
query5.SQL.clear;
query1.Close;
query1.SQL.clear;
dateyear:=formatdatetime('yyyy',now);
qstr1:='select distinct atname from sumexportall where (ddate like '+''''+dateyear+'-'+combobox1.text+'%'+'''';
qstr3:='select * from sumexportall where ddate like '+''''+dateyear+'-'+combobox1.text+'%'+'''';
if combobox1.ItemIndex<>12 then
begin
query1.SQL.add(qstr1);
query1.sql.add(')');
query5.SQL.add(qstr3);
//query5.ExecSQL;
end
else
begin
query1.SQL.add('select  distinct atname from sumexportall where (ddate is not null)');
query5.SQL.add('select * from sumexportall where (ddate is not null)');
end;

if combobox2.ItemIndex<>query4.RecordCount then
begin
query1.sql.Add(' and (agoodd='''+combobox2.Text+'''');
query1.SQL.add(')');
query5.sql.add(' and (agoodd='''+combobox2.Text+'''');
query5.SQL.add(')');
end
else
begin
query1.sql.Add(' and (agoodd is not null)');
query5.sql.add(' and (agoodd is not null)');
end;
query5.Open;
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;

if query5.RecordCount>0 then
begin
table2.Close;
table2.EmptyTable;
//{table2.Close;
batchmove1.Execute;
table2.Active:=true;
table2.first;
if length(table2.FieldValues['ddate'])>2 then
begin
for i:=1 to table2.RecordCount
do
begin
table2.Edit;
table2.FieldValues['ddate']:=formatdatetime('mm',strtodate(table2.FieldValues['ddate']));
table2.Post;
table2.Next;
end;
end
else
showmessage('没有本月的出货数据！');
end;

end;

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

procedure Tfmqueryout1.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 Tfmqueryout1.TreeView1Click(Sender: TObject);
var a:string;
    b:string;
    i,oic,oic1,js,js1: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 件数,npiece 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 件数,npiece 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 件数,npiece 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;
js:=0;
js1:=0;
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;
if query3.FieldByName('支数').Value=null then js1:=0 else js1:=query3.FieldByName('支数').Value;
js:=js+js1;
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:='支数: '+inttostr(js)+' 支';
statusbar1.Panels[4].Text:='吨位: '+formatfloat('0.000',oit)+' 吨';

end;

procedure Tfmqueryout1.BitBtn2Click(Sender: TObject);
begin
fmqueryout1.Close;
end;



procedure Tfmqueryout1.BitBtn1Click(Sender: TObject);
begin
application.CreateForm(tfmqueryoutprivew,fmqueryoutprivew);
fmqueryoutprivew.Query1.Close;
fmqueryoutprivew.Query1.SQL.Clear;
fmqueryoutprivew.Query1.sql.Add('select ddate,atname,agname,agoodd,adir,sum(ncoil) as ncoil,sum(npiece) as npiece,sum(nton) as nton from sumoutall group by ddate,atname,agname,agoodd,adir');
fmqueryoutprivew.Query1.Open;
fmqueryoutprivew.Query1.Active:=true;
fmqueryoutprivew.QRLabel10.Caption:='制表人：'+fmuser.susername;
fmqueryoutprivew.QuickRep1.Preview;

end;

end.