inv_billandlist.pas

一个MRPII系统源代码版本

unit Inv_BillAndList;

Interface

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

Type
  TFrm_Inv_BillAndList = Class(TFrm_Base_Qry)
    lbl_Status: TLabel;
    AdODataSet_Main: TAdODataSet;
    procedure FormCreate(Sender: TObject);
    procedure Act_nextExecute(Sender: TObject);
  private
    { Private declarations }
  public
    procedure InitForm(Adoconnect:TAdoConnection);  
    { Public declarations }
  end;

var
  Frm_Inv_BillAndList: TFrm_Inv_BillAndList;

implementation
uses Inv_QueryStatus;
{$R *.DFM}

{ TFrm_Inv_BillAndList }

procedure TFrm_Inv_BillAndList.InitForm(Adoconnect: TAdoConnection);
begin
  AdODataSet_Main.Connection:=Adoconnect;
  AdoQry_Main.Connection:=Adoconnect;
  AdoQry_Tmp.Connection:=Adoconnect;
  Frm_Inv_QueryStatus.SetFocus;
  act_next.Execute;
end;

procedure TFrm_Inv_BillAndList.FormCreate(Sender: TObject);
begin
  inherited;
  TlBtn_Look.Action:=act_next;
  act_next.Visible:=False;
  Frm_Inv_QueryStatus:=TFrm_Inv_QueryStatus.Create(Application);
  Frm_Inv_QueryStatus.Show;
  Frm_Inv_QueryStatus.refresh;
end;

procedure TFrm_Inv_BillAndList.Act_nextExecute(Sender: TObject);
var
  Sql_Txt:String;
begin
  inherited;
  lbl_Status.Caption:='正在查询,请稍候......';
  lbl_Status.Refresh;
  sql_txt:='set noCount on '+
           //建立临时表
          ' Create table #table '+
          '     (mono varchAr(12),'+
          '      MoLineno varchAr(12),'+
          //'      ite_ItemCode varchAr(10),'+
          '      ItemCode varchAr(10),'+
          '      InvBillqty decimal(20,8) default 0,'+
          '      MoRealqty decimal(20,8) default 0,'+
          '      Differentqty decimal(20,8) default 0)'+
          //插入InvOutBill中的作为基值
          ' insert into #table'+
          '   select b.mono,bl.MoLineno,bl.ItemCode,sum(bl.InvBillqty) InvBillqty ,0,0'+
          '    from InvOutBill B,InvOutBillLine BL'+
          '   where b.InvBillid=bl.InvBillid and'+
          '         b.mono is not null and'+
          '         b.InvBillwhchck=1'+
          '   group by b.mono,bl.MoLineno,bl.ItemCode'+
          //插入InvOutBill和mnItemList的对称差
          ' Insert into #table'+
          '  select ML.mono,ML.MoLineno,ML.ItemCode,0,sum(ML.MoRealqty) MoRealqty,0'+
          '   from mnItemList ML'+
          '  where rTrim(ML.mono)+rTrim(ML.MoLineno)+ML.ItemCode not in'+
          '        (select rTrim(mono)+rTrim(MoLineno)+ItemCode'+
          '           from #table)'+
          '   group by ML.mono,ML.MoLineno,ML.ItemCode'+
          //更新MoRealqty字段
          ' update #table'+
          '   set MoRealqty=c.MoRealqty'+
          '  from (select mono,MoLineno,ItemCode,MoRealqty'+
          '          from mnItemList)c'+
          ' where c.mono=#table.mono and'+
          '       c.MoLineno=#table.MoLineno and'+
          //'       c.ite_ItemCode=#table.ite_ItemCode and'+
          '       c.ItemCode=#table.ItemCode'+
          //计算差值
          ' update #table'+
          '   set DifferentQty=MoRealqty-InvBillqty'+
          //返回数据集
          ' select T.mono 单据号,T.MoLineno 行号,'+
          //' T.ite_ItemCode+'' ''+I1.ItemName 父项物料标识,'+
          ' T.ItemCode+'' ''+I2.ItemName 物料标识,'+
          ' T.MoRealqty 单据数量,'+
          ' T.InvBillqty 领料清单数量,'+
          ' T.Differentqty 差异 '+
          ' from #table T'+
          //' Left Join Item I1 '+
          //' on I1.ItemCode=T.ite_ItemCode '+
          ' Left Join Item I2 '+
          ' on I2.ItemCode=T.ItemCode '+
          ' where DifferentQty<>0'+
          '  Order by T.mono,T.MoLineno,T.ItemCode'+

          ' drop table #table';

  with AdODataSet_Main do
  begin
    CommandTimeout:=0;
    Close;
    CommandText:=sql_txt;
    Open;
    lbl_Status.Caption:='记录共_'+inttostr(RecordCount)+'条';
  end;
  with DBGridEh do
  begin
    TFloatField(AdODataSet_Main.Fields[3]).displayFormat:='0.##';
    TFloatField(AdODataSet_Main.Fields[4]).displayFormat:='0.##';
    TFloatField(AdODataSet_Main.Fields[5]).displayFormat:='0.##';
    Columns[0].Width:=80;
    Columns[1].Width:=60;
    Columns[2].Width:=350;
    Columns[3].Width:=60;
    Columns[4].Width:=60;
    Columns[5].Width:=60;
    //Columns[6].Width:=60;
    Frm_Inv_QueryStatus.Close;
    Frozencols:=3;
    ReFresh;
  end;
end;

end.