uqueryall.~pas

港口货物管理系统

unit uqueryall;

interface

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

type
  Tfmqueryall = 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;
    StatusBar2: TStatusBar;
    StatusBar1: TStatusBar;
    RadioGroup1: TRadioGroup;
    Query4: TQuery;
    GroupBox1: TGroupBox;
    ImageList1: TImageList;
    Panel1: TPanel;
    Label2: TLabel;
    procedure TreeView1Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure ComboBox1Click(Sender: TObject);
    procedure ComboBox2Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);

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

var
  fmqueryall: Tfmqueryall;

implementation

uses ulogin, uprintdbgrid, udm_user, ufmqueryallbb;

{$R *.dfm}

procedure Tfmqueryall.TreeView1Click(Sender: TObject);
var a:string;
    b:string;
    i,nic,oic,nic1,oic1:integer;
    npton,jzs,jzs1,czs,czs1,jczs:integer;
    nit,oit,nit1,oit1:single;
    njcc:integer;
    njct:single;
begin

if radiogroup1.ItemIndex=0 then
fstr:='atnum'
else fstr:='adime';


a:=treeview1.Selected.Text;
if (treeview1.Selected<>node) then
if treeview1.Selected.TreeView.Selected.Text<>null then
begin
b:=treeview1.Selected.Parent.Text;
query3.Close;
query3.sql.clear;
query3.SQL.add('select cc.ddate as 月份 ,cc.atnum as 提单号 ,cc.agname as 货名,cc.adime as 规格,cc.ncoil as 进货件数,cc.npiece as 进货支数,cc.nton as 进货吨位,bb.ncoil as 出货件数,bb.npiece as 出货支数,bb.nton as 出货吨位,');
query3.SQL.Add(' cc.nplanton as 计划吨位,cc.lend as 结束标记,cc.agoodd as 货代 from sumimport cc left join sumexport bb on (cc.atnum=bb.atnum) and (cc.adime=bb.adime) and (cc.agname=bb.agname) where (cc.agname='''+b+'''');
query3.sql.Add(') and (');
query3.sql.add(fstr+'='''+a+'''');
query3.sql.Add(')');
if combobox1.ItemIndex<>12 then
query3.SQL.add(' and (ddate='''+combobox1.Text+'''')
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 cc.ddate,cc.agoodd,cc.atnum,cc.agname,cc.atnum');
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 月份 ,atnum as 提单号 ,agname as 货名,adime as 规格,ncoil as 进货件数,npiece as 进货支数,nton as 进货吨位,bb.ncoil as 出货件数,bb.npiece as 出货支数,bb.nton as 出货吨位,');
query3.SQL.Add(' cc.nplanton as 计划吨位,cc.lend as 结束标记,cc.agoodd as 货代 from sumimport cc left join sumexport bb on (cc.atnum=bb.atnum) and (cc.adime=bb.adime) and (cc.agname=bb.agname) where (agname='''+b+'''');
query3.sql.Add(')');
if combobox1.ItemIndex<>12 then
query3.SQL.add(' and (ddate='''+combobox1.Text+'''')
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 cc.ddate,cc.agoodd,cc.agname,cc.atnum');
query3.Open;
end

else if  treeview1.Selected=node
then
begin
query3.Close;
query3.sql.clear;
query3.SQL.add('select ddate as 月份 ,atnum as 提单号 ,agname as 货名,adime as 规格,cc.ncoil as 进货件数,cc.npiece as 进货支数,cc.nton as 进货吨位,bb.ncoil as 出货件数,bb.npiece as 出货支数,bb.nton as 出货吨位,');
query3.SQL.Add(' cc.nplanton as 计划吨位,cc.lend as 结束标记,cc.agoodd as 货代 from sumimport cc left join sumexport bb on (cc.atnum=bb.atnum) and (cc.adime=bb.adime) and (cc.agname=bb.agname)');
if combobox1.ItemIndex<>12 then
query3.SQL.add(' where (ddate='''+combobox1.Text+'''')
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 cc.ddate,cc.agoodd,cc.agname,cc.atnum,cc.adime');
query3.Open;
end;

jzs:=0;
czs:=0;
jzs1:=0;
czs1:=0;
jczs:=0;
nic:=0;
nit:=0.000;
oic:=0;
oit:=0.000;
nic1:=0;
nit1:=0.000;
oic1:=0;
oit1:=0.000;
npton:=0;
query3.First;
while not query3.Eof do
begin
if query3.Fields[4].Value=null then nic1:=0 else nic1:=query3.Fields[4].Value;
nic:=nic+nic1;
if query3.Fields[6].Value=null then nit1:=0 else nit1:=query3.Fields[6].Value;
nit:=nit+nit1;
if query3.Fields[5].Value=null then jzs1:=0 else jzs1:=query3.Fields[5].Value;
jzs:=jzs+jzs1;
if query3.Fields[8].Value=null then czs1:=0 else czs1:=query3.Fields[8].Value;
czs:=czs+czs1;

if query3.Fields[7].Value=null then oic1:=0 else oic1:=query3.Fields[7].Value;
oic:=oic+oic1;
if query3.Fields[9].Value=null then oit1:=0.000 else oit1:=query3.Fields[9].Value;
oit:=oit+oit1;
npton:=npton+query3.Fields[10].Value;
query3.Next;
end;
njcc:=nic-oic;
jczs:=jzs-czs;
if njcc=0 then njct:=0 else njct:=nit-oit;
statusbar1.Panels[1].Text:=inttostr(query3.RecordCount)+'条记录';
statusbar1.Panels[2].Text:=inttostr(nic)+' 件';
statusbar1.Panels[3].Text:=inttostr(jzs)+' 支';
statusbar1.Panels[4].Text:=formatfloat('0.000',nit)+' 吨';
statusbar1.Panels[5].Text:=inttostr(oic)+' 件';
statusbar1.Panels[6].Text:=inttostr(czs)+' 支';
statusbar1.Panels[7].Text:=formatfloat('0.000',oit)+' 吨';
statusbar1.Panels[8].Text:=inttostr(njcc)+' 件';
statusbar1.Panels[9].Text:=inttostr(jczs)+' 支';
statusbar1.Panels[10].Text:=formatfloat('0.000',njct)+' 吨';
statusbar1.Panels[11].Text:=inttostr(npton)+' 吨';
for i:=0 to dbgrid1.Columns.Count-1 do
begin
dbgrid1.Columns[i].Title.Alignment:=taCenter;
end;
end;

procedure Tfmqueryall.BitBtn2Click(Sender: TObject);
begin
fmqueryall.Close;
end;

procedure Tfmqueryall.ComboBox1Click(Sender: TObject);
var i:integer;
begin

if radiogroup1.ItemIndex=0 then
fstr:='atnum'
else fstr:='adime';


query1.Close;
query1.SQL.clear;
if combobox1.ItemIndex<>12 then
begin
query1.SQL.add('select distinct agname from sumimport where (ddate='''+combobox1.Text+'''');
query1.sql.add(')');
end
else
query1.SQL.add('select  distinct agname from sumimport 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['agname']);
query1.next;
end;



query1.Open;
query1.First;
while not query1.Eof
do
begin
query2.Close;
query2.sql.Clear;
if combobox1.ItemIndex<>12 then
begin
query2.SQL.add('select distinct ');
query2.SQL.Add(fstr+' from sumimport where (ddate='''+combobox1.Text+'''');
end
else
begin
query2.SQL.add('select distinct ');
query2.SQL.add(fstr+' from sumimport where (ddate is not null');
end;
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 (agname='''+query1.FieldValues['agname']+'''');
query2.SQL.add(') order by '+fstr);
query2.ExecSQL;

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

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

procedure Tfmqueryall.FormCreate(Sender: TObject);
var i:integer;

begin
application.OnMessage:=fmlogin.doenterastab;
query4.Active:=true;
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;


if radiogroup1.ItemIndex=0 then
fstr:='atnum'
else fstr:='adime';
query1.Close;
query1.SQL.clear;
query1.SQL.add('select distinct agname from sumimport where (ddate='''+formatdatetime('mm',now)+'''');
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['agname']);
query1.next;
end;



query1.Open;
query1.First;
while not query1.Eof
do
begin
query2.Close;
query2.sql.Clear;
query2.SQL.add('select distinct ');
query2.SQL.Add(fstr+' from sumimport where (ddate='''+formatdatetime('mm',now)+'''');
query2.SQL.add(') and (agname='''+query1.FieldValues['agname']+'''');
query2.sql.add(')');
query2.sql.Add(' and (agoodd='''+combobox2.Text+'''');
query2.SQL.add(')');
query2.SQL.add(' order by '+fstr);
query2.ExecSQL;
query2.Open;
query2.First;

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


combobox1.ItemIndex:=strtoint(formatdatetime('mm',now))-1;
end;

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

procedure Tfmqueryall.BitBtn1Click(Sender: TObject);
begin
application.CreateForm(tfmqueryallbb,fmqueryallbb);
fmqueryallbb.QuickRep1.DataSet:=fmqueryall.Query3;
fmqueryallbb.QRLabel10.Caption:='制表人：'+fmuser.susername;
fmqueryallbb.QuickRep1.Preview;

end;

end.