pm_qry_purchaseassignalanysis_c.pas

一个MRPII系统源代码版本

unit Pm_Qry_PurchaseAssignAlanysis_C;
Interface

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

Type
  TFrm_Pm_Qry_PurchaseAssignAlanysis_C = Class(TFrm_Base_Condition)
    Label2: TLabel;
    Label3: TLabel;
    Label5: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label10: TLabel;
    ExtEdit1: TExtEdit;
    ExtEdit2: TExtEdit;
    ExtEdit5: TExtEdit;
    ExtEdit6: TExtEdit;
    MaskEdit1: TMaskEdit;
    MaskEdit2: TMaskEdit;
    procedure FormDestroy(Sender: TObject);
    procedure btn_okClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure ExtEdit5KeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frm_Pm_Qry_PurchaseAssignAlanysis_C: TFrm_Pm_Qry_PurchaseAssignAlanysis_C;

implementation

uses Pm_Qry_PurchaseAssignAlanysis,Sys_Global;

{$R *.DFM}

procedure TFrm_Pm_Qry_PurchaseAssignAlanysis_C.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Pm_Qry_PurchaseAssignAlanysis_C:=nil;
end;

procedure TFrm_Pm_Qry_PurchaseAssignAlanysis_C.btn_okClick(Sender: TObject);
var SQLText:String;
  {  TmpItemCode:String;
    TmpQry:TAdoQuery;
    TmpFlag:Integer; }
begin
  inherited;
{ 
  TmpQry:=TAdoQuery.Create(nil);
  TmpQry.Connection:=AdoQry_Tmp.Connection;
  TmpQry.EnableBCD:=False;
} 
  sqltext:=' select  po.VendorCode,        '
                 +'  Vendor.VendorName,          '
                 +'  poline.ItemCode,            '
                 +'  Item.ItemName,              '
                 +'  sum(poline.poqty) as poqty, '      
                 +'  sum(poline.porealinqty) as porealinqty, ' 
                 +'  sum(poline.ponoFinishqty) as ponoFinishqty '
     +' into #tmppoline                 '
     +' from poline                     '
     +' join po on poline.pono=po.pono  '
     +' left join Vendor on po.VendorCode=Vendor.VendorCode   '
     +' left join Item on poline.ItemCode=Item.ItemCode       '
     +' where poline.ItemCode in ( select distinct ItemCode from VendorItemAssign ) '
     +'   and poline.ItemCode between '+quotedstr(Extedit5.Text)+' and '+quotedstr(Extedit6.text)
     +'   and po.VendorCode between '+quotedstr(Extedit1.text)+' and '+quotedstr(Extedit2.text)
     +'   and poline.polinedate between '+quotedstr(maskedit1.text)+' and '+quotedstr(maskedit2.text+' 23:59:59 ')
     +'   and poline.polinestatus>=6 '
     +' group by po.VendorCode,Vendor.VendorName, poline.ItemCode,Item.ItemName     '
     +' select poline.ItemCode,    '
     +'        sum(poline.poqty) as poqty,  '
     +'        sum(poline.porealinqty ) as porealinqty    '
     +' into #tmppolinesum                             '
     +' from poline                                    '
     +' where poline.ItemCode in ( select distinct ItemCode from VendorItemAssign )'
     +'   and poline.ItemCode between '+quotedstr(Extedit5.Text)+' and '+quotedstr(Extedit6.text)
     +'   and poline.polinedate between '+quotedstr(maskedit1.text)+' and '+quotedstr(maskedit2.text+' 23:59:59 ')
     +'   and poline.polinestatus>=6 '
     +' group by poline.ItemCode   '   
 
     +' select 0 as flag   ,           '
     +'        #tmppoline.ItemCode,    '
     +'        #tmppoline.ItemName,     '
     +'        #tmppoline.VendorCode,   '
     +'        #tmppoline.VendorName,         '
     +'        convert(varchAr,convert(decimal(20,2),isnull(VendorItemAssign.Assignrate,0)))+''%'' as Assignrate,   '
     +'        #tmppoline.poqty as poqty,     '
     +'        case when #tmppolinesum.poqty=0 then '''' '
     +'        else convert(varchAr,convert(decimal(20,2),#tmppoline.poqty/#tmppolinesum.poqty*100.00))+''%'' end as per_Sumpoqty,        '
     +'        case when #tmppolinesum.poqty=0 then '''' '
     +'        else convert(varchAr,convert(decimal(20,2),#tmppoline.poqty/#tmppolinesum.poqty*100.00-isnull(VendorItemAssign.Assignrate,0)))+''%'' end as Diffpoqty,      '
     +'        #tmppoline.porealinqty as porealinqty,                                             '
     +'        case when #tmppolinesum.porealinqty=0 then '''' '
     +'        else convert(varchAr,convert(decimal(20,2),#tmppoline.porealinqty/#tmppolinesum.porealinqty*100.00))+''%'' end as per_Sumporealinqty,           '
     +'        case when #tmppoline.poqty=0 then '''' '
     +'        else convert(varchAr,convert(decimal(20,2),(#tmppoline.poqty-#tmppoline.ponoFinishqty)/#tmppoline.poqty*100.00))+''%'' end as per_Finishqty     '
     +'  into #poResult  '
     +' from  #tmppoline  '
     +' left join VendorItemAssign on VendorItemAssign.ItemCode=#tmppoline.ItemCode   '
     +'      and  VendorItemAssign.VendorCode=#tmppoline.VendorCode             '
     +' join #tmppolinesum on #tmppoline.ItemCode=#tmppolinesum.ItemCode        ' ;
  Executesql(AdoQry_tmp,sqltext,1);
{  Executesql(AdoQry_tmp,'select * from #poResult',0);
  TmpItemCode:=AdoQry_Tmp.fieldbyname('ItemCode').asstring;
  TmpFlag:=AdoQry_Tmp.fieldbyname('Flag').AsInteger;
  while not AdoQry_tmp.Eof do
  begin
    If AdoQry_Tmp.fieldbyname('ItemCode').asstring<>TmpItemCode Then
    begin
      sqltext:='Update #poResult '
              +'   set flag=1    '      ;
    end;    
    AdoQry_Tmp.Next;
  end;
  TmpQry.Free;  }
  sqltext:=' select * from #poResult Order by ItemCode,VendorCode ';
  Executesql(Frm_Pm_Qry_PurchaseAssignAlanysis.AdoQry_Main,sqltext,0)     ;

  if Frm_Pm_Qry_PurchaseAssignAlanysis.AdoQry_Main.RecordCount=0 then
  begin
    DispInfo('没有合符条件的数据！',3);
    try
      Executesql(AdoQry_tmp,'drop table #tmppoline',1);
    except
    end;
    try
      Executesql(AdoQry_tmp,'drop table #tmppolinesum',1);
    except
    end;
    try
      Executesql(AdoQry_tmp,'drop table #poResult',1);
    except
    end;
    Extedit1.setfocus;
    exit;
  end;
  
  ConditionHint:='供应商代码从 '+Extedit1.Text+' 到 '+Extedit2.Text+' /物料代码从 '+Extedit5.Text+' 到 '+Extedit6.Text+' /约定交货日从 '+maskedit1.Text+' 到 '+maskedit2.Text;
  self.ModalResult:=mrOk;
end;

procedure TFrm_Pm_Qry_PurchaseAssignAlanysis_C.FormShow(Sender: TObject);
begin
  inherited;
  maskedit1.Text:=formatdatetime('yyyy.mm.dd',date-15);
  maskedit2.Text:=formatdatetime('yyyy.mm.dd',date);
end;

procedure TFrm_Pm_Qry_PurchaseAssignAlanysis_C.ExtEdit5KeyDown(Sender: TObject;
  var Key: Word; Shift: TShiftState);
begin
  inherited;
  If key=120 Then
  CommOnHint(Sender,AdoQry_Tmp,'ItemName','物料描述','ItemCode',
             '物料代码',' Item ',' PmCode In (1,2,3) and ItemUsable=1 ');
end;

end.