inv_outinvandbill.pas

一个MRPII系统源代码版本

unit Inv_OutInvAndBill;

Interface

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

Type
  TFrm_Inv_OutInvAndBill = Class(TFrm_Base_Qry)
    CheckBox1: TCheckBox;
    Label1: TLabel;
    lbl_RecordCount: TLabel;
    Label2: TLabel;
    AdODataSet_Main: TAdODataSet;
    procedure FormCreate(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure Act_nextExecute(Sender: TObject);
  private
    { Private declarations }
  public
    procedure InitForm(Adoconnect:TAdoConnection);  
    { Public declarations }
  end;

var
  Frm_Inv_OutInvAndBill: TFrm_Inv_OutInvAndBill;

implementation
uses Inv_QueryStatus;
{$R *.DFM}

procedure TFrm_Inv_OutInvAndBill.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_OutInvAndBill.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_OutInvAndBill.CheckBox1Click(Sender: TObject);
begin
  inherited;
  DBGridEh.AutoFitColWidths:=CheckBox1.Checked;
end;

procedure TFrm_Inv_OutInvAndBill.Act_nextExecute(Sender: TObject);
var
  Sql_txt:string;
begin
  inherited;
  Sql_Txt:='set noCount on'+
           //建立临时表
           ' create table #table'+
           ' (whCode varchAr(4),'+
           ' ItemCode varchAr(16),'+
           ' WhPosition varchAr(4),'+
           ' qty decimal(20,8) default 0,'+
           ' InBillQty decimal(20,8) default 0,'+
           ' OutBillqty decimal(20,8) default 0,'+
           ' Billqty decimal(20,8) default 0,'+
           ' MonthQty decimal(20,8) default 0)'+
           ' DeclAre @SumMonth varchAr(12),@tmpdate datetime'+
           ' DeclAre @ClosedMonth varchAr(7)'+
           ' DeclAre @beginDate chAr(10)'+
           ' set @ClosedMonth=(select convert(chAr(7),convert(decimal(7,2),Invstatus)) from Invstatus where InvstatusName=''clsperiod'')'+
           ' set @tmpdate=(select convert(datetime,@ClosedMonth+''.01'')+40)'+
           ' set @SumMonth=dateName(YY,@tmpdate)+''.''+dateName(MM,@tmpdate)'+
           ' Set @beginDate=@sumMonth+''.01'''+

           //产生库存数据
           '     insert into #table'+
           '       select whCode,ItemCode,WhPositionCode,'+
           '              case when onhandInv <>0 then onhandInv'+
           '                   when freezeInv <>0 then freezeInv'+
           '                   when wasterInv <>0 then wasterInv'+
           '                   when onShipInv <>0 then onShipInv'+
           '                   else 0 end Billqty,0,0,0,0'+
           '         from CurrentInv'+
           //产生期初数据
           '     insert into #table(whCode,WhPosition,ItemCode)'+
           '       select whCode,WhPositionCode,ItemCode'+
           '         from whPMonthSum'+
           '        where rTrim(whCode)+rTrim(WhPositionCode)+ItemCode not in'+
           '              (select rTrim(whCode)+rTrim(WhPosition)+ItemCode'+
           '                 from #table) and'+
           '               InvMonth=@ClosedMonth'+

           '     update #table'+
           '       set Monthqty=c.Invblncqty'+
           '       from (select InvblncQty,whCode,WhPositionCode,ItemCode'+
           '               from whPMonthSum'+
           '              where InvMonth=@ClosedMonth )c'+
           '       where c.whCode=#table.whCode and c.WhPositionCode=#table.WhPosition and c.ItemCode=#table.ItemCode'+

           //产生收入方数据
           '     insert into #table(whCode,WhPosition,ItemCode)'+
           '       select distinct whCode,WhPositionCode,ItemCode'+
           '         from (select distinct I.whCode,I.WhPositionCode,IB.ItemCode,rTrim(I.whCode)+rTrim(I.WhPositionCode)+IB.ItemCode as flag'+
           '                 from InvInBill I,opBill IB'+
           '                where I.InvBillid=IB.InvBillId and'+
           '                      (I.BillTypeCode<>''0103'' or InvBillwhchck=0) and '+
           '                  I.InvBillDate>=@beginDate )c'+
           '        where  c.Flag not in'+
           '              (select rTrim(whCode)+rTrim(WhPosition)+ItemCode from #table)'+

           '     update #table'+
           '        set InBillqty=c.opBillqty'+
           '       from (select sum(IB.opBillqty)as opBillqty,i.whCode,I.WhPositionCode,IB.ItemCode'+
           '               from InvInBill I,opBill IB'+
           '              where I.InvBillid=IB.InvBillid and'+
           '                    (I.BillTypeCode<>''0103'' or InvBillwhchck=0) and'+ 
           '                    I.InvBilldate>=@beginDate'+
           '              group by I.whCode,WhPositionCode,ib.ItemCode)c'+
           '       where c.whCode=#table.whCode and c.WhPositionCode=#table.WhPosition and c.ItemCode=#table.ItemCode'+
           //产生发出方数据

           '     insert into #table(whCode,WhPosition,ItemCode)'+
           '       select distinct whCode,WhPositionCode,ItemCode'+
           '         from (select distinct I.whCode,I.WhPositionCode,IB.ItemCode,rTrim(I.whCode)+rTrim(I.WhPositionCode)+IB.ItemCode as flag'+
           '                 from InvOutBill I,InvOutBillline IB'+
           '                where I.InvBillid=IB.InvBillId and'+
           '                I.BillTypeCode=''0202'' and'+
           '                  I.InvBillDate>=@beginDate )c'+
           '        where  c.Flag not in'+
           '              (select rTrim(whCode)+rTrim(WhPosition)+ItemCode from #table)'+

           '     update #table'+
           '        set OutBillqty=c.InvBillqty'+
           '       from (select sum(IB.InvBillqty)as InvBillqty,I.whCode,I.WhPositionCode,IB.ItemCode'+
           '               from InvOutBill I,InvOutBillline IB'+
           '              where I.InvBillid=IB.InvBillid and'+
           '                I.InvBilldate>=@beginDate and'+
           '                I.BillTypeCode=''0202'''+
           '              group by i.whCode,I.WhPositionCode,IB.ItemCode )c'+
           '       where c.whCode=#table.whCode and c.WhPositionCode=#table.WhPosition and c.ItemCode=#table.ItemCode'+
           //更新临时表,获得单据的库存数据

           '     update #table'+
           '        set Billqty=Monthqty+InBillqty-OutBillqty'+

           '    select T.whCode+'' ''+W.whName 仓库标识,'+
           '            rTrim(T.WhPosition)+'' ''+P.WhPositionName 货位标识,'+
           '            T.ItemCode+'' ''+I.ItemName 物料标识,'+
           '            T.qty 库存表库存数,'+
           '            T.Billqty 计算得出库存数,'+
           '            T.qty-T.BillQty 差异'+
           ' from #table T,Item I,Warehouse W,WhPosition P'+
           '   where  I.ItemCode=T.ItemCode and'+
           '     W.whCode=T.whCode and'+
           '     P.whCode=T.whCode and P.WhPositionCode=T.WhPosition'+
           '     and T.qty-T.BillQty<>0 and T.BillQty<>0'+
           '   Order by t.whCode,t.WhPosition,t.ItemCode'+
           '    drop table #table';

  with AdODataSet_Main do
  begin
    CommandTimeout:=0;
    Close;
    CommandText:=sql_txt;
    Open;
    lbl_RecordCount.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:=150;
    Columns[1].Width:=200;
    Columns[2].Width:=200;
    Columns[3].Width:=60;
    Columns[4].Width:=60;
    Columns[5].Width:=60;
    Frm_Inv_QueryStatus.Close;
    Frozencols:=3;
    Refresh;
  end;
end;

end.