inv_deptmonthclasssumqry_c.pas

一个MRPII系统源代码版本

unit Inv_DeptMonthClassSumQry_C;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Condition, Db, AdODB, StdCtrls, Mask, ExtEdit, linkedit;

Type
  TFrm_Inv_DeptMonthClassSumQry_C = Class(TFrm_Base_Condition)
    Label1: TLabel;
    MEdt_Starttime: TMaskEdit;
    Label3: TLabel;
    CmBx_Type: TComboBox;
    Label2: TLabel;
    AdoQry_tmp1: TAdoQuery;
    AdoQry_tmp2: TAdoQuery;
    Edit2: TEdit;
    edit1: TLinkEdit;
    procedure btn_okClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure Edit1Exit(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure SetDBConnect(AdOConnection:TAdOConnection); Override;
  end;

var
  Frm_Inv_DeptMonthClassSumQry_C: TFrm_Inv_DeptMonthClassSumQry_C;

implementation

uses Sys_Global,Inv_Global;

{$R *.DFM}

procedure TFrm_Inv_DeptMonthClassSumQry_C.btn_okClick(Sender: TObject);
var
  StrTemp1,StrTemp2,StrTemp3,tmpClassCode:String;
  ClassgrAde:integer;
begin
  inherited;
  if Trim(edit1.Text)='' then
  begin
    DispInfo('部门代码有误！',1);
    edit1.SetFocus;
    Abort;
  end;
  ClassgrAde:=1;
  if Trim(CmBx_Type.text)='' then
  begin
    DispInfo('物料类别不能为空！',1);
    exit;
  end;
  if Trim(CmBx_Type.text)='全部' then
  else
  begin
    ClassgrAde:=strtoint(copy(Trim(CmBx_Type.text),3,1));
  end;
  try
    AdoQry_tmp.Close;
    AdoQry_tmp.sql.clear;
    AdoQry_tmp.sql.Add('drop table #wtmp0,#wtmp1,#wtmp2,#wtmp3');
    AdoQry_tmp.ExecSQL;
  except
  end;
  with AdoQry_tmp do
  begin
  //从用户定义的级别中找出没有子项的类
  // #wtmp0记录了该级别下的所有子类
    Close;
    sql.clear;
    sql.Add(' create table #wtmp0(pClassCode varchAr(16),ClassCode varchAr(16))'+
            ' insert #wtmp0 select a.ClassCode,a.ClassCode from '+
            ' (select ClassCode from ItemClass where ClassgrAde='+inttostr(ClassgrAde)+') a '+
            ' where a.ClassCode not in (select pClassCode from ItemClass)');
    ExecSQL;
    //找出所有该级别的类
    Close;
    sql.clear;
    sql.Add('select ClassCode from ItemClass where ClassgrAde='+inttostr(ClassgrAde));
    open;
    First;
    while not eof do
    begin
      tmpClassCode:=fieldbyname('ClassCode').asstring;
      //逐个找子项
      AdoQry_tmp1.Close;
      AdoQry_tmp1.sql.clear;
      AdoQry_tmp1.sql.Add('select ClassCode into #wtmp1 from ItemClass where pClassCode='+quotedstr(tmpClassCode));
      AdoQry_tmp1.ExecSQL;
      AdoQry_tmp2.Close;
      AdoQry_tmp2.SQL.clear;
      AdoQry_tmp2.sql.Add('select * from #wtmp1');
      AdoQry_tmp2.open;
      while not AdoQry_tmp2.eof do
      begin
        AdoQry_tmp2.Close;
        AdoQry_tmp2.SQL.clear;
        AdoQry_tmp2.sql.Add('insert #wtmp0 select '+quotedstr(tmpClassCode)+',ClassCode from #wtmp1');
        AdoQry_tmp2.ExecSQL;
        AdoQry_tmp1.Close;
        AdoQry_tmp1.sql.clear;
        AdoQry_tmp1.sql.Add('select ClassCode into #wtmp2 from ItemClass'+
                            ' where pClassCode in (select ClassCode from #wtmp1)'+
                            ' delete from #wtmp1'+
                            ' insert #wtmp1 select * from #wtmp2'+
                            ' drop table #wtmp2' );
        AdoQry_tmp1.ExecSQL;
        AdoQry_tmp2.Close;
        AdoQry_tmp2.SQL.clear;
        AdoQry_tmp2.sql.Add('select * from #wtmp1');
        AdoQry_tmp2.open;
      end;
      AdoQry_tmp1.Close;
      AdoQry_tmp1.sql.clear;
      AdoQry_tmp1.sql.Add(' drop table #wtmp1 ' );
      AdoQry_tmp1.ExecSQL;
      next;
    end;

  end;

  StrTemp1:='';
  if edit1.Text<>'' then
  begin
    StrTemp1:=Trim(edit1.Text)+'%';
  end;
  Condition:='select i.DeptCode+'' ''+Dept.DeptName as DeptCodeName,'+
             '#wtmp0.pClassCode+'' ''+ItemClass.ClassName as ClassName,'+
             'sum(il.InvBillqty) as qty,'+
             'sum(il.InvBillnotaxAmount) as Amount from InvOutBill i '+
             'left join InvOutBillline il on i.InvBillid=il.InvBillid '+
             'left join Item  on il.ItemCode=Item.ItemCode '+
             'left join #wtmp0 on #wtmp0.ClassCode=Item.ClassCode '+
             'left join Dept on Dept.DeptCode=i.DeptCode '+
             'left join ItemClass on ItemClass.ClassCode=#wtmp0.pClassCode '+
             'where i.InvBillMonth='+quotedstr(MEdt_Starttime.Text)+
             ' and il.ItemCode is not null '+
             ' and ((i.BillTypeCode=''0201'' and  InvBillWHChck=1) or '+
             '(i.BillTypeCode=''0299'' and (i.BillType2Code=''130'' or i.AmountAdjust=1))) '+
             '  and i.DeptCode like '+QuotedStr(StrTemp1)+
             ' group by i.DeptCode,Dept.DeptName,#wtmp0.pClassCode,ItemClass.ClassName ';
  ConditionHint:='部门： '+edit1.Text+'/查询月份： '+MEdt_Starttime.Text
    +'/物料分类： '+CmBx_Type.Text;
  ModalResult:=mrOk;
end;

procedure TFrm_Inv_DeptMonthClassSumQry_C.FormActivate(Sender: TObject);
var
  n,i:integer;     //物料类别有多少级
  ns0,ns1:string;
begin
  inherited;
  MEdt_Starttime.Text:=FormatDateTime('yyyy.mm',Now);

  CmBx_Type.clear;
  n:=0;
  AdoQry_tmp.Close;
  AdoQry_tmp.sql.clear;
  AdoQry_tmp.sql.Add('select max(ClassgrAde) as ClassgrAde from ItemClass');
  AdoQry_tmp.open;
  n:=AdoQry_Tmp.fieldbyname('ClassgrAde').AsInteger;
  for i :=1  to n do
  begin
     CmBx_Type.Items.Add('第'+Trim(inttostr(i))+'级');
  end;
  CmBx_Type.Itemindex:=0;
end;

procedure TFrm_Inv_DeptMonthClassSumQry_C.SetDBConnect(AdOConnection: TAdOConnection);
begin
  inherited;
  AdoQry_tmp1.Connection:=AdOConnection;
  AdoQry_tmp2.Connection:=AdOConnection;
end;

procedure TFrm_Inv_DeptMonthClassSumQry_C.Edit1Exit(Sender: TObject);
begin
  inherited;
  with AdoQry_tmp do
  begin
    Close;
    sql.text:='select DeptName from Dept where DeptCode='+QuotedStr(edit1.text);
    open;
    if not eof then
    begin
      edit2.Text:=fieldbyname('DeptName').AsString;
    end
    else
    begin
       DispInfo('部门代码有误',1);
       edit1.SetFocus;
    end;
  end;
end;

end.