inv_opinvandopap.pas

一个MRPII系统源代码版本

unit Inv_OpInvAndopAp;

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_Inv_OpInvAndOPAp = Class(TFrm_Base_Qry)
    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_OpInvAndOPAp: TFrm_Inv_OpInvAndOPAp;

implementation
uses Inv_QueryStatus;
{$R *.DFM}

procedure TFrm_Inv_OpInvAndOPAp.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_OpInvAndOPAp.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_OpInvAndOPAp.Act_nextExecute(Sender: TObject);
var
  Sql_Txt:string;
begin
  inherited;
  Pnl_Hint.Caption:='正在查询,请稍候......';
  Pnl_Hint.Refresh;
  Sql_Txt:='set noCount on '+
           //创建临时表
           '       create table #table'+
           '             (VendorCode varchAr(10),'+
           '              ItemCode varchAr(10),'+
           '              InvQty decimal(20,8) default 0,'+
           '              InvInqty decimal(20,8) default 0,'+
           '              Invoutqty decimal(20,8) default 0, '+
           '              Apqty decimal(20,8) default 0,'+
           '              Differentqty decimal(20,8) default 0)'+
           //得到结帐月份和下下月月份@NNMonth
           '       DeclAre @SumMonth varchAr(10),@NNMonth varchAr(10),@tmp varchAr(7)'+
           '       set @tmp=(select convert(varchAr(7),convert(decimal(7,2),Invstatus)) from Invstatus where InvstatusName=''clsperiod'')'+
           '       set @SumMonth=(select convert(datetime,@tmp+''.01''))'+
           '       set @sumMonth=dateName(YYYY,@sumMonth)+''.''+dateName(MM,@sumMonth)'+
           '       set @NNMonth=(select convert(datetime,@tmp+''.01'')+70)'+
           '       set @NNMonth=dateName(YYYY,@NNMonth)+''.''+dateName(MM,@NNMonth)'+
           //初始化临时表
           '       insert into #table'+
           '              (VendorCode,ItemCode,InvQty)'+
           '            select VendorCode,'+
           '                   ItemCode,'+
           '                   opApqty'+
           '              from opAveragePrice'+
           //插入InvInBill收入记录
           '       insert into #table(VendorCode,ItemCode)'+
           '            select distinct aa.VendorCode,aa.ItemCode'+
           '              from (select I.VendorCode,'+
           '                           IB.ItemCode'+
           '                      from InvInBill I'+
           '                       Join InvInBillLine IB'+
           '                         on IB.InvBillId=I.InvBillId'+
           '                      where I.InvBillMonth=@NNMonth and'+
           '                            I.BillTypeCode in (''0103'',''0202'')) aa'+
           '                       Left Join #table T'+
           '                         on T.VendorCode=aa.VendorCode and'+
           '                            T.ItemCode=aa.ItemCode'+
           '            where T.VendorCode is Null and'+
           '                  T.ItemCode is Null'+
           '       update #table'+
           '          set InvInQty=aa.InvInqty'+
           '         from (select sum(IB.InvBillQty)InvInqty,IB.ItemCode,I.VendorCode'+
           '                 from InvInBill I,InvInBillLine IB'+
           '                where IB.InvBillId=I.InvBillId and'+
           '                      I.BillTypeCode in (''0103'',''0202'') and'+
           '                      I.InvBillMonth=@NNMonth'+
           '                group by I.VendorCode,IB.ItemCode)aa'+
           '        where aa.ItemCode=#table.ItemCode and'+
           '              aa.VendorCode=#table.VendorCode'+
           //插入InvOutBill发出记录
           '       insert into #table(VendorCode,ItemCode)'+
           '            select distinct aa.VendorCode,aa.ItemCode'+
           '              from (select I.VendorCode,'+
           '                           IB.ItemCode'+
           '                      from InvOutBill I'+
           '                       Join InvOutBillLine IB'+
           '                         on IB.InvBillId=I.InvBillId'+
           '                      where I.InvBillMonth=@NNMonth and'+
           '                            I.BillTypeCode in (''0103'',''0202'')) aa'+
           '                       Left Join #table T'+
           '                         on T.VendorCode=aa.VendorCode and'+
           '                            T.ItemCode=aa.ItemCode'+
           '            where T.VendorCode is Null and'+
           '                  T.ItemCode is Null'+
           '       update #table'+
           '          set InvOutQty=aa.InvOutQty'+
           '         from (select sum(OB.InvBillQty)InvOutQty,'+
           '                      O.VendorCode,'+
           '                      OB.ItemCode'+
           '                 from InvOutBill O,InvOutBillLine OB'+
           '                where OB.InvBillId=O.InvBillId and'+
           '                      O.BillTypeCode in (''0103'',''0202'') and'+
           '                      O.InvBillMonth=@NNMonth'+
           '                group by O.VendorCode,OB.ItemCode)aa'+
           '         where aa.VendorCode=#table.VendorCode and'+
           '               aa.ItemCode=#table.ItemCode'+
           //插入委外opBill中的记录
           '       Insert into #table(VendorCode,ItemCode)'+
           '            select distinct aa.VendorCode,aa.ItemCode'+
           '              from (select OP.ItemCode,'+
           '                           I.VendorCode'+
           '                      from opBill OP,InvInBill I'+
           '                     where Op.InvBillid=I.InvBillid and'+
           '                           I.InvBillMonth=@NNMonth)aa'+
           '                   Left Join #table T'+
           '                      on T.VendorCode=aa.VendorCode and'+
           '                         T.ItemCode=aa.ItemCode'+
           '           where T.VendorCode is Null and'+
           '                 T.ItemCode is Null'+
           '       update #table'+
           '          set #table.InvOutQty=#table.InvOutQty+aa.InvOutQty'+
           '         from (select sum(OP.opBillQty)InvOutQty,'+
           '                      OP.ItemCode,'+
           '                      I.VendorCode'+
           '                 from OpBill OP,InvInBill I'+
           '                where OP.InvBillId=I.InvBillId and'+
           '                      I.InvBillMonth=@NNMonth'+
           '                 group by op.ItemCode,I.VendorCode)aa'+
           '         where aa.VendorCode=#table.VendorCode and'+
           '               aa.ItemCode=#table.ItemCode'+
           //插入opCurrentInv中的记录
           '       insert into #table(VendorCode,ItemCode)'+
           '            select distinct VendorCode,'+
           '                            ItemCode'+
           '              from (select O.VendorCode,'+
           '                           O.ItemCode'+
           '                      from opCurrentInv O'+
           '                       Left Join #table T'+
           '                           on T.VendorCode=O.VendorCode and'+
           '                              T.ItemCode=O.ItemCode'+
           '                     where T.ItemCode is Null and'+
           '                           T.VendorCode is Null)tt'+
           '       update #table'+
           '          set Apqty=aa.opInv'+
           '         from (select VendorCode,'+
           '                      ItemCode,'+
           '                      opInv'+
           '                 from opCurrentInv o)aa'+
           '         where aa.VendorCode=#table.VendorCode and'+
           '               aa.ItemCode=#table.ItemCode'+
           //计算临时表中的各项值
           '       update #table'+
           '          set InvQty=InvQty+InvInQty-InvOutQty'+
           '       update #table'+
           '          set DifferentQty=InvQty-Apqty'+
           //取数据
           '       select T.VendorCode+'' ''+V.VendorName 供应商标识,'+
           '              T.ItemCode+'' ''+IT.ItemName 物料标识,'+
           '              T.Apqty 库存数量,'+
           '              T.InvQty 平均价数量,'+
           '              T.DifferentQty 差异'+
           '         from #table T,Item IT,Vendor V'+
           '        where T.DifferentQty<>0 and'+
           '              T.VendorCode=V.VendorCode and'+
           '              T.ItemCode=IT.ItemCode '+
           '       drop table #table';
  with AdODataSet_Main do
  begin
    CommandTimeout:=0;
    Close;
    CommandText:=sql_txt;
    Open;
    Pnl_Hint.Caption:='记录共_'+inttostr(RecordCount)+'条';
  end;
  with DBGridEh do
  begin
    TFloatField(AdODataSet_Main.Fields[2]).displayFormat:='0.##';
    TFloatField(AdODataSet_Main.Fields[3]).displayFormat:='0.##';
    TFloatField(AdODataSet_Main.Fields[4]).displayFormat:='0.##';
    Columns[0].Width:=250;
    Columns[1].Width:=200;
    Columns[2].Width:=100;
    Columns[3].Width:=100;
    Columns[4].Width:=100;
    Frm_Inv_QueryStatus.Close;
    Frozencols:=2;
    ReFresh;
  end;     
end;

end.