pm_qry_sspoinfo_d.pas

一个MRPII系统源代码版本

unit Pm_Qry_SsPoInfo_D;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Qry, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
  StdCtrls, ExtCtrls, ComCtrls, ToolWin;

Type
  TFrm_Pm_Qry_SsPoInfo_D = Class(TFrm_Base_Qry)
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_Mainldtime: TFloatField;
    AdoQry_MainPmBatch: TIntegerField;
    AdoQry_MainCurrentonhandInv: TFloatField;
    AdoQry_Mainssdate: TDateTimeField;
    AdoQry_Mainssqty: TFloatField;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
  public
       procedure setconditionHint(lbl:Tlabel;cAp:string);
       procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Pm_Qry_SsPoInfo_D: TFrm_Pm_Qry_SsPoInfo_D;
        days:integer;

implementation
uses Pm_Qry_SsPoInfo,Sys_Global;
{$R *.DFM}

{ TFrm_Pm_Qry_SsPoInfo_D }

procedure TFrm_Pm_Qry_SsPoInfo_D.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
  var sqltext:string;
begin
  inherited;
  try
  with TFrm_Pm_Qry_SsPoInfo.Create(self) do
    begin
     showmodal;
     if modalResult<>mrok then
        begin
        self.Close;
        exit;
        end;
     days:=strtoint(edtdays.text);
     end;
  finally
   Frm_Pm_Qry_SsPoInfo.Free;
  end;
    sqltext:='  select top 0 ItemCode,ssdate,ssqty into #tmpres  from ss     '
             +'  select ItemCode,ssdate,ssqty                                 '
             +'  into #tMpss                                                  '
             +'  from ss                                                      '
             +'  where PmType=1 or PmType=2                                   '
             +'   Order by ItemCode,ssdate                                    '
             +'  declAre @Count int,                                          '
             +'          @ItemCode varchAr(16),                               '
             +'          @ssdate   datetime,                                  '
             +'          @date     datetime,                                  '
             +'          @ssqty    float(8)                                   '
             +'   set rowCount 1                                              '
             +'  insert into #tmpres                                          '
             +'  select * from #tMpss                                         '
             +'  select @date=(select ssdate from #tmpres)                    '
             +'  delete from #tMpss                                           '
             +'  select @Count=(select Count(*) from #tMpss)                  '
             +'   while @Count>0                                              '
             +'     begin                                                     '
             +'       select @ItemCode=ItemCode from #tMpss                   '
             +'       select @ssdate=ssdate from #tMpss                       '
             +'       select @ssqty=ssqty from #tMpss                         '
             +'      if exists(select * from #tmpres where ItemCode=@ItemCode)'
             +'           begin                                               '
             +'              if dateDiff(dd,@date,@ssdate)<'+inttostr(days)
             +'                update #tmpres                                 '
             +'                set ssqty=ssqty+@ssqty                         '
             +'                where ItemCode=@ItemCode                       '
             +'                 and dateDiff(dd,ssdate,@ssdate)<'+inttostr(days)
             +'              else                                             '
             +'                 begin                                         '
             +'                  insert into #tmpres                          '
             +'                  select * from #tMpss                         '
             +'                  select @date=@ssdate                         '
             +'                 end                                           '
             +'          end                                                  '
             +'      else                                                     '
             +'        begin                                                  '
             +'          insert into #tmpres                                  '
             +'          select * from #tMpss                                 '
             +'          select @date=@ssdate                                 '
             +'        end                                                    '
             +'        delete from #tMpss                                     '
             +'        select @Count=@Count-1                                 '
             +'  end                                                          '
             +'     set rowCount 999999999                                    '
             +'     select      #tmpres.ItemCode,                             '
             +'                 Item.ItemName,                                '
             +'                 Uom.UomName,                                  '
             +'                 ldtime=Item.purchldtime,                      '
             +'                 Item.PmBatch,                                 '
             +'                 Item.CurrentonhandInv,                        '
             +'                 #tmpres.ssdate,                               '
             +'                 #tmpres.ssqty                                 '
             +'      into #tmp                                                '
             +'    from  #tmpres,Item(nolock),Uom(nolock)                     '
             +'     where #tmpres.ItemCode=Item.ItemCode                      '
             +'      and  Item.UomCode=Uom.UomCode                            '
             +'     Order by #tmpres.ItemCode,#tmpres.ssdate                  ';
  with AdoQry_Main do
    begin
      Close;
      sql.clear;
      sql.Add(sqltext);
      Prepared;
      try
      execsql;
      except
      end;

      Close;
      sql.clear;
      sql.Add('select * from #tmp');
      Prepared;
      try
      open;
      except
      end;

      if recordCount=0 then
       begin
        Application.MessageBox('无相关资料！','提示信息',mb_ok);
        self.Close;
       end;
    end;

  selectfromsql:='select * from #tmp';
 { sqltext:=' select ssdate,ssqty into #tmp2 from ss(nolock) '+#13+
           ' where  (PmType=1 or PmType=2 )      '+#13+
           ' and    ItemCode='''+Frm_Pm_Qry_SsPoInfo_H.AdoQry_Head.fieldbyname('ItemCode').asstring+'''';
 if Frm_Pm_Qry_SsPoInfo_H.AdoQry_Head.fieldbyname('TotalType').asstring='日' then
    condition:=' and cast(datepArt(yy,SSDate) as varchAr)+''.''+cast(datepArt(mm,SSDate) as varchAr)+''.''+cast(datepArt(dd,SSDate) as varchAr)='''+Frm_Pm_Qry_SsPoInfo.AdoQry_Head.fieldbyname('ssdate').asstring+''''
 else
 if Frm_Pm_Qry_SsPoInfo_H.AdoQry_Head.fieldbyname('TotalType').asstring='周' then
    condition:=' and Convert(varchAr,DatePArt(Year,SSDate))+''-''+Convert(varchAr,DatepArt(Week,SSDate))='''+Frm_Pm_Qry_SsPoInfo.AdoQry_Head.fieldbyname('ssdate').asstring+''''
 else if  Frm_Pm_Qry_SsPoInfo_H.AdoQry_Head.fieldbyname('TotalType').asstring='月' then
     condition:=' and Convert(varchAr,DatePArt(Year,SSDate))+''-''+Convert(varchAr,DatepArt(mm,SSDate))='''+Frm_Pm_Qry_SsPoInfo.AdoQry_Head.fieldbyname('ssdate').asstring+'''';
   sqltext:=sqltext+condition;

  with AdoQry_Main do
    begin
     Close;
     sql.clear;
     sql.Add(sqltext);
     Prepared;
     try
     execsql;
     except
     end;
      Close;
      sql.clear;
      sql.Add('select * from #tmp2');
      open;
     if recordCount=0 then
       begin
         DispInfo('无相关资料！',3);
         self.Close;
       end;
    end;}
 end;

procedure TFrm_Pm_Qry_SsPoInfo_D.setconditionHint(lbl: Tlabel;
  cAp: string);
begin
   lbl.Caption:=cAp;
end;

procedure TFrm_Pm_Qry_SsPoInfo_D.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
    with AdoQry_tmp do
      begin
          Close;
          sql.clear;
          sql.Add('drop table #tMpss,#tmpres,#tmp');
          try
          execsql;
          except
          end;
      end;

  inherited;

end;

procedure TFrm_Pm_Qry_SsPoInfo_D.FormDestroy(Sender: TObject);
begin
  inherited;
 Frm_Pm_Qry_SsPoInfo_D:=nil;
end;

end.