inv_invandbill.pas

一个MRPII系统源代码版本

unit Inv_InvAndBill;

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_InvAndBill = Class(TFrm_Base_Qry)
    CheckBox1: TCheckBox;
    Label1: TLabel;
    lbl_qty: TLabel;
    Label2: TLabel;
    AdODataSet_Main: TAdODataSet;
    Button1: TButton;
    procedure FormCreate(Sender: TObject);
    procedure Act_nextExecute(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Act_ExcelExecute(Sender: TObject);
    procedure AdODataSet_MainAfterOpen(DataSet: TDataSet);
    procedure FormDestroy(Sender: TObject);
    procedure AdODataSet_MainBeforeEdit(DataSet: TDataSet);
  private
    { Private declarations }
  public
    procedure InitForm(Adoconnect:TAdoConnection);
    { Public declarations }
  end;

var
  Frm_Inv_InvAndBill: TFrm_Inv_InvAndBill;

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

procedure TFrm_Inv_InvAndBill.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_InvAndBill.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;
  Frm_Inv_QueryStatus.Label1.Refresh;
  if dbgrideh.Columns[0].ReadOnly then
    dbgrideh.Columns[0].ReadOnly :=False;
end;

procedure TFrm_Inv_InvAndBill.Act_nextExecute(Sender: TObject);
var
  sql_txt:string;
  flag1:Tfield;
begin
  inherited;
  sql_txt:='set noCount on '+
           //建立临时表
           ' create table #table '+
           ' (flag1 int  default 0 ,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 varchAr(10) '+
           ' DeclAre @ClosedMonth varchAr(7) '+
           ' DeclAre @beginDate varchAr(10) '+
           ' set @ClosedMonth=(select convert(varchAr(7),convert(decimal(7,2),Invstatus)) from Invstatus where InvstatusName=''clsperiod'') '+
       //    ' set @tmpdate=(select convert(datetime,@ClosedMonth+''.01'',102)+40) '+
          // ' set @tmpdate=(select convert(datetime,@ClosedMonth+''.01'')+40) '+
           //' set @SumMonth=dateName(YY,@tmpdate)+''.''+dateName(MM,@tmpdate) '+
           //' Set @beginDate=@sumMonth+''.01''' +
           ' select  @begindate=convert(varchAr(7),dateAdd (mm,1,@ClosedMonth+''.01'') ,102) '+
           //产生库存数据
           '     insert into #table '+
           '       select 0,whCode,ItemCode,WhPositionCode,'+
           '              case when onhandInv <>0 then onhandInv '+
//           '                   when onCheckInv <>0 then onCheckInv '+
           '                   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,InvInBillline IB '+
           '                where I.InvBillid=IB.InvBillId and '+
           '                  i.InvBillMonth>=@beginDate )c '+
           '        where  c.Flag not in '+
           '              (select rTrim(whCode)+rTrim(WhPosition)+ItemCode from #table) '+
           '     update #table '+
           '        set InBillqty=c.InvBillqty '+
           '       from (select sum(IB.InvBillqty)as InvBillqty,i.whCode,I.WhPositionCode,IB.ItemCode'+
           '               from InvInBill I,InvInBillline IB '+
           '              where I.InvBillid=IB.InvBillid and '+
           '                    I.InvBillMonth>=@beginDate and '+
           '                    (I.BillTypeCode not in (''0104'',''0105'') or I.InvBillwhchck=1)'+
           '              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 '+
           '                  InvBillMonth>=@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.InvBillMonth>=@beginDate and'+
           '                BillTypeCode Not In (''0206'',''0205'') and '+
           '                (I.BillTypeCode not in (''0201'',''0203'',''0204'') or InvBillwhchck=1)'+
           '              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  flag1 ,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 '+
           '   Order by t.whCode,t.WhPosition,t.ItemCode '+
           '     drop table #table';

  with AdODataSet_Main do
  begin
    CommandTimeout:=0;
    Close;
    CommandText:=sql_txt;
    Open;
    lbl_qty.Caption:=inttostr(RecordCount);
  end;
  {DBGridEh.ReadOnly :=False;
  with DBGridEh do
  begin
    TFloatField(AdODataSet_Main.Fields[4]).displayFormat:='0.##';
    TFloatField(AdODataSet_Main.Fields[5]).displayFormat:='0.##';
    TFloatField(AdODataSet_Main.Fields[6]).displayFormat:='0.##';
    Columns[0].Width:=20;
    columns[0].Field :=flag1;
    columns[0].Title.Caption:='选定标识';
    columns[0].Title.alignment:=taCenter ;
    columns[0].checkboxes:= True;
    columns[0].KeyList.Add('1');
    columns[0].KeyList.Add('0');
    columns[0].ReadOnly :=False;
    Columns[1].Width:=80;
    columns[1].ReadOnly :=True;
    columns[1].Title.alignment:=taCenter ;
    Columns[2].Width:=120;
    columns[2].ReadOnly :=True;
    columns[2].Title.alignment:=taCenter ;
    Columns[3].Width:=310;
    columns[3].ReadOnly :=True;
    columns[3].Title.alignment:=taCenter ;
    Columns[4].Width:=80;
    columns[4].ReadOnly :=True;
    columns[4].Title.alignment:=taCenter ;
    Columns[5].Width:=80;
    columns[5].ReadOnly :=True;
    columns[5].Title.alignment:=taCenter ;
    Columns[6].Width:=80;
    columns[6].ReadOnly :=True;
    columns[6].Title.alignment:=taCenter ;

    Frozencols:=0;
    Refresh;
  end;}
  //  CheckBox1.Checked:=DBGridEh.AutoFitColWidths;
 Frm_Inv_QueryStatus.Close;
end;

procedure TFrm_Inv_InvAndBill.Button1Click(Sender: TObject);
begin
  inherited;
    //dbgrideh.Enabled:=False;
    try
      with AdODataSet_Main do
      begin
        First;
        while not eof do
        begin
          if (AdODataSet_Main.fieldbyname ('flag1').asinteger=1 )
            and (AdODataSet_Main.fieldbyname('库存表库存数').asfloat-AdODataSet_Main.fieldbyname('差异').asfloat>=0)  then
          begin
            if not AdoQry_tmp.Connection.InTransaction  then
              AdoQry_tmp.Connection.beginTrans ;
            try
              Executesql(AdoQry_tmp,' update CurrentInv set OnHandInv=OnHandInv-('+AdODataSet_Main.fieldbyname('差异').asstring+')'
                        +'  where ItemCode= '''+getCode(AdODataSet_Main.fieldbyname('物料标识').asstring)+''''
                        +'  and whCode='''+getCode(AdODataSet_Main.fieldbyname('仓库标识').asstring)+''''
                        +' and WhPositionCode='''+getCode(AdODataSet_Main.fieldbyname('货位标识').asstring)+'''',1);
              Executesql(AdoQry_tmp,' update Item set CurrentOnHandInv=CurrentOnHandInv-('+AdODataSet_Main.fieldbyname('差异').asstring+')'
                        +'  where ItemCode= '''+getCode(AdODataSet_Main.fieldbyname('物料标识').asstring)+'''',1);
              AdoQry_tmp.Connection.CommitTrans ;
            except
              AdoQry_tmp.Connection.RollBackTrans ;
              DispInfo('数据更新错误！',3);
              abort;
            end;
          end;
          next;
        end;
      end;
  finally
    dbgrideh.Enabled :=True;
  end;
  Button1.enabled:=False;
  dbgrideh.Columns[0].ReadOnly :=True; 
end;

procedure TFrm_Inv_InvAndBill.Act_ExcelExecute(Sender: TObject);
begin
  Dbgrideh.columns[0].visible:=False;
  try
    inherited;
  finally
    Dbgrideh.columns[0].visible:=True;
  end;
end;

procedure TFrm_Inv_InvAndBill.AdODataSet_MainAfterOpen(DataSet: TDataSet);
begin
  inherited;
  TFloatField(AdODataSet_Main.Fields[4]).displayFormat:='0.##';
  TFloatField(AdODataSet_Main.Fields[5]).displayFormat:='0.##';
  TFloatField(AdODataSet_Main.Fields[6]).displayFormat:='0.##';
end;

procedure TFrm_Inv_InvAndBill.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Inv_InvAndBill:=nil;
end;

procedure TFrm_Inv_InvAndBill.AdODataSet_MainBeforeEdit(DataSet: TDataSet);
begin
  inherited;
  if ( AdODataSet_Main.fieldbyname('库存表库存数').asfloat-AdODataSet_Main.fieldbyname('差异').asfloat<0)  then
  begin
    DispInfo('不能调整数据:库存表库存数减差异小于零!',3);
    abort;
  end;
end;

end.