inv_monthclasssumqry_c.pas

一个MRPII系统源代码版本

unit Inv_MonthClassSumQry_C;

Interface

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

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

var
  Frm_Inv_MonthClassSumQry_C: TFrm_Inv_MonthClassSumQry_C;

implementation

uses Sys_Global,Inv_Global;

{$R *.DFM}

procedure TFrm_Inv_MonthClassSumQry_C.btn_okClick(Sender: TObject);
var
  StrTemp1,StrTemp2,StrTemp3,tmpClassCode:String;
  ClassgrAde:integer;
begin
  inherited;
  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;

{
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select SysParamCode From SysParam'
    +' Where SysParamValueC='''+CmBx_Type.Text+'''';
  AdoQry_Tmp.Open;
  StrTemp3:=AdoQry_Tmp.fieldbyname('SysParamCode').AsString;
  StrTemp3:=Copy(StrTemp3,1,10);
  if CmBx_Warehouse.Text<>'全部仓库' then
  begin
    StrTemp1:=' Warehouse.WHName,Item.'+StrTemp3+' As ItemClass';
    StrTemp2:=' Group By '+'Warehouse.WHName,Item.'+StrTemp3;
    StrTemp3:=' And InvMonthSum.WHCode='''+GetCode(CmBx_Warehouse.Text)+'''';
  end
  else
  begin
    StrTemp1:=' Warehouse.WHName,Item.'+StrTemp3+' As ItemClass';
    StrTemp2:=' Group By '+'Warehouse.WHName,Item.'+StrTemp3;
    StrTemp3:='';
  end;
 }
  StrTemp1:='';
  if CmBx_Warehouse.Text<>'全部仓库' then
  begin
    StrTemp1:=' and Inv.whCode='+QuotedStr(getCode(CmBx_Warehouse.Text));
  end;

    Condition:=
    ' select a.whCode+'' ''+Warehouse.whName as whName,'+
           '#wtmp0.pClassCode+'' ''+ItemClass.ClassName as ItemClass,'+
           'Sum(a.InvLMQty) As SInvLMQty,'+
           'Sum(a.InvLMAmount) As SInvLMAmount,'+
           'Sum(a.InvInQty) As SInvInQty,'+
           'Sum(a.InvInAmount) As SInvInAmount,'+
           'Sum(a.InvOutQty) As SInvOutQty,'+
           'Sum(a.InvOutAmount) As SInvOutAmount,'+
           'Sum(a.InvBlncQty) As SInvBlncQty,'+
           'Sum(a.InvBlncAmount) As SInvBlncAmount '+
     'from '+
         '(select Inv.whCode,Item.ClassCode,sum(Inv.InvLMQty) as InvLMQty,'+
         'Sum(Inv.InvLMAmount) As InvLMAmount,'+
         'Sum(Inv.InvInQty) As InvInQty,'+
         'Sum(Inv.InvInAmount) As InvInAmount,'+
         'Sum(Inv.InvOutQty) As InvOutQty,'+
         'Sum(Inv.InvOutAmount) As InvOutAmount,'+
         'Sum(Inv.InvBlncQty) As InvBlncQty,'+
         'Sum(Inv.InvBlncAmount) As InvBlncAmount '+
         'from InvMonthSum Inv '+
                'left join Item  on Item.ItemCode=Inv.ItemCode '+
                'where Inv.InvMonth='+quotedstr(MEdt_Starttime.Text)+StrTemp1+
                ' group by Inv.whCode,Item.ClassCode) a '+
    'left join #wtmp0 on #wtmp0.ClassCode=a.ClassCode '+
    'left join Warehouse on Warehouse.whCode=a.whCode ' +
    'left join ItemClass on #wtmp0.pClassCode=ItemClass.ClassCode ' +
    'group by a.whCode,Warehouse.whName,#wtmp0.pClassCode,ItemClass.ClassName';

{

  Condition:='Select'+StrTemp1
    +',Sum(InvMonthSum.InvLMQty) As SInvLMQty'
    +',Sum(InvMonthSum.InvLMAmount) As SInvLMAmount'
    +',Sum(InvMonthSum.InvInQty) As SInvInQty'
    +',Sum(InvMonthSum.InvInAmount) As SInvInAmount'
    +',Sum(InvMonthSum.InvOutQty) As SInvOutQty'
    +',Sum(InvMonthSum.InvOutAmount) As SInvOutAmount'
    +',Sum(InvMonthSum.InvBlncQty) As SInvBlncQty'
    +',Sum(InvMonthSum.InvBlncAmount) As SInvBlncAmount'
    +' From InvMonthSum'
    +' Join Item On InvMonthSum.ItemCode=Item.ItemCode'
    +' Join Warehouse On InvMonthSum.WHCode=Warehouse.WHCode'
    +' Where InvMonthSum.InvMonth='''+MEdt_Starttime.Text+''''
    +StrTemp3
    +StrTemp2;
  }
  ConditionHint:='仓库： '+CmBx_Warehouse.Text+'/查询月份： '+MEdt_Starttime.Text
    +'/物料： '+CmBx_Type.Text;
  ModalResult:=mrOk;
end;

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

    InitUsableWHCmBx(AdoQry_tmp,UserCode,CmBx_Warehouse,False);
    CmBx_Warehouse.Items.Insert(0,'全部仓库');
    CmBx_Warehouse.ItemIndex:=0;

    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;
end;

procedure TFrm_Inv_MonthClassSumQry_C.MonthCheck(Sender: TObject);
begin
  inherited;
  if Param1='Stk' then
    InvDataChangeCheck(AdoQry_Tmp,TEdit(Sender).Text);
end;

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

end.