inv_closebook.pas

一个MRPII系统源代码版本

unit Inv_CloseBook;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Dialog, Db, AdODB, StdCtrls;

Type
  TFrm_Inv_CloseBook = Class(TFrm_Base_Dialog)
    Label1: TLabel;
    Lbl_Month: TLabel;
    Lbl_State: TLabel;
    AdoQry_Temp: TAdoQuery;
    AdoQuery: TAdoQuery;
    Label2: TLabel;
    AdOCommand1: TAdOCommand;
    procedure btn_okClick(Sender: TObject);
    procedure btn_CancelClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
    UpdateMonth:String;
  public
    { Public declarations }
    procedure SetDBConnect(AdOConnection:TAdOConnection);Override;
  end;

var
  Frm_Inv_CloseBook: TFrm_Inv_CloseBook;

implementation

uses Sys_Global;

{$R *.DFM}

procedure TFrm_Inv_CloseBook.btn_okClick(Sender: TObject);
var
  PoNo,PoLineNo,OldPrice,nextMonth,prioMonth:String;
begin
  inherited;
  nextMonth:=copy(datetostr(incMonth(strtodate(lbl_Month.Caption+'.01'),1)),1,7);
  try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='drop TABLE #MonthSumQry ';
    AdoQry_Tmp.ExecSQL;
  except

  end;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='CREATE TABLE #MonthSumQry ('
    +' [JournalQryId] [numeric](18, 0) IDENTITY (1, 1) NOT NULL'
    +',[ItemCode] [varchAr] (16) NULL'
    +',[DeptCode] [varchAr] (8) NULL'
    +',[whCode] [varchAr] (8) NULL'
    +',[InvlmQty] [float] default 0'
    +',[InvlmAmount] [float] default 0'
    +',[InvInQty] [float] default 0'
    +',[InvInAmount] [float] default 0'
    +' )';
  AdoQry_Tmp.ExecSQL;

  if Lbl_State.Caption<>'' then
  begin
    Close;
    Exit;
  end
  else if DispInfo('结帐后,该月所有出入库数据不允许更改'+#13
    +#10+#13+#10+'请确认是否开始结帐！',2)='y' then
  begin
    Lbl_State.Caption:='正在进行月结前的检查......';
    btn_ok.Enabled:=False;
    btn_Cancel.Enabled:=False;

    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Select InvStatus From InvStatus'
      +' Where InvStatus=1'
      +' And InvStatusName='''+FormatDateTime('yymm',StrToDateTime(Lbl_Month.Caption+'.01'))+'''' ;
    AdoQry_Tmp.Open;
    if AdoQry_Tmp.fieldbyname('InvStatus').AsString<>'' then
    begin
      DispInfo('该月数据发生变化后,没有重新进行月末计算,不允许结帐！',3);
      Close;
      Exit;
    end;

    //新程序
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Select Count(*) As CountTatol'
      +' From Po'
      +' Where Po.PoType=1'
      +' And Po.PoNo Not In'
      +' (Select Distinct PoNo From PoLine Where PoLineStatus<>7)'
      +' And Po.PoNo Not In'
      +' (Select PoNo From InvInBill'
      +' Where BillTypeCode=''0103'''
      +' And InvBillMonth='''+Lbl_Month.Caption+''''
      +')';

    AdoQry_Tmp.Open;
    if AdoQry_Tmp.fieldbyname('CountTatol').AsInteger<>0 then
      if DispInfo('存在有入库记录,但未进行订单结算的委外订单'+#13+#10
        +'将导致某些委外报表的材料定额和材料费不确定'+#13+#10
        +'是否继续进行结帐?',2)='n' then
      begin
        Close;
        Exit;
      end;

    DBConnect.beginTrans;
    try
    with Adocommand1 do
    begin
    //添加InvBalance的数据给InvMonthSum--(wxp)下个月的期初作为本月的结存
      CommandText:=' select whCode,ItemCode,sum(InvQty) as InvQty,sum(InvAmount) as InvAmount into #wtmp1 from InvBalance where InvMonth='+
              quotedstr(nextMonth)+' group by whCode,ItemCode '+
              ' delete InvMonthSum  from #wtmp1 where InvMonthSum.whCode=#wtmp1.whCode and InvMonthSum.ItemCode=#wtmp1.ItemCode '+
              ' Insert into InvMonthSum '
                          +'(InvMonth,'
                            +'whCode,'
                            +'ItemCode,'
                            +'InvBlncQty,'
                            +'InvBlncAmount) '
                            +' select '
                            + quotedstr(lbl_Month.Caption)
                            +',whCode,ItemCode,InvQty,InvAmount from #wtmp1 '+
              'Update InvMonthSum '+
              'set InvBlncPrice=case Invblncqty when 0 then 0 else InvblncAmount/Invblncqty end '+
              ' from #wtmp1 where InvMonthSum.whCode=#wtmp1.whCode and InvMonthSum.ItemCode=#wtmp1.ItemCode '+
              ' drop table #wtmp1' ;
       Prepared;
       Execute;
       //计算OpBalance中的下个月的期初数据作为本月的结存

       CommandText:=' select VendorCode,ItemCode,sum(InvQty) as InvQty,sum(InvAmount) as InvAmount into #wtmp2 from OpBalance where InvMonth='+
              quotedstr(nextMonth)+' group by VendorCode,ItemCode '+
              ' delete OpMonthSum  from #wtmp2 where OpMonthSum.VendorCode=#wtmp2.VendorCode and OpMonthSum.ItemCode=#wtmp2.ItemCode '+
              ' Insert into OpMonthSum '
                          +'(InvMonth,'
                            +'VendorCode,'
                            +'ItemCode,'
                            +'InvBlncQty,'
                            +'InvBlncAmount) '
                            +' select '
                            + quotedstr(lbl_Month.Caption)
                            +',VendorCode,ItemCode,InvQty,InvAmount from #wtmp2 '+
              'Update OpMonthSum '+
              'set InvBlncPrice=case Invblncqty when 0 then 0 else InvblncAmount/Invblncqty end '+
              ' from #wtmp2 where OpMonthSum.VendorCode=#wtmp2.VendorCode and OpMonthSum.ItemCode=#wtmp2.ItemCode '+
              ' drop table #wtmp2' ;
       Prepared;
       Execute;

       //添加InvBalance的数据--(wxp)下个月的期初作为本月的结存
       CommandText:=' select whCode,WhPositionCode,ItemCode,InvQty into #wtmp3 from InvBalance where InvMonth='+
              quotedstr(nextMonth)+
              ' delete whPMonthSum  from #wtmp3 where whPMonthSum.whCode=#wtmp3.whCode and whPMonthSum.WhPositionCode=#wtmp3.WhPositionCode and whPMonthSum.ItemCode=#wtmp3.ItemCode '+
              ' Insert into whPMonthSum '
                          +'(InvMonth,'
                            +'whCode,'
                            +'WhPositionCode,'
                            +'ItemCode,'
                            +'InvBlncQty) '
                            +' select '
                            + quotedstr(lbl_Month.Caption)
                            +',whCode,WhPositionCode,ItemCode,InvQty from #wtmp3 '+
              ' drop table #wtmp3'  ;
       Prepared;
       Execute;
       {
       // 计算车间月末金额
       prioMonth:=formatdatetime('yyyy.mm',incMonth(strtodate(lbl_Month.Caption+'.01'),-1));
       CommandText:='Insert #MonthSumQry'
           +' Select ItemCode,'
           +' DeptCode'
           +',null'
           +',wipInvQty'
           +',wipAmount'
           +',0'
           +',0'
           +' From WipInv'
           +' Where ItemType=0 and WipInvMonth='''+PrioMonth+'''';
       Prepared;
       Execute;

       CommandText:='Insert #MonthSumQry'
          +' Select ItemCode'
          +',DeptCode'
          +',InvOutBill.whCode'
          +',0'
          +',0'
          +',Sum(InvOutBillLine.InvBillQty)'
          +',Sum(InvOutBillLine.InvBillnotaxAmount)'
          +' From InvOutBillLine,InvOutBill '
          +' Where InvOutBillLine.InvBillId=InvOutBill.InvBillId and InvOutBill.InvBillMonth='''+lbl_Month.Caption+''''
          +' And ((InvOutBill.InvBillWHChck=1'
          +' And InvOutBill.BillTypeCode=''0201'') or (InvOutBill.BillTypeCode=''0299'' and InvOutBill.AmountAdjust=1)'
          +' or (InvOutBill.BillTypeCode=''0299'' and InvOutBill.BillType2Code=''130''))'   //其他出库到车间  ：注意130是用户定义的
    //      +' and InvOutBill.WipWaster<>1 '  //料费
          +' Group By InvOutBillLine.ItemCode,InvOutBill.DeptCode,InvOutBill.whCode';
       Prepared;
       Execute;

       CommandText:='Insert #MonthSumQry1'
          +' Select InvOutBillLine.ItemCode'
          +',IsNull(InvOutBill.DeptCode,'''')'
          +',null'
          +',sum(Invlmqty) as Invlmqty'
          +',sum(InvlmAmount) as InvlmAmount'
          +',Sum(InvInQty) as InvInqty'
          +',Sum(InvInAmount) as InvInAmount'
          +' From #MonthSumQry '
          +' Group By ItemCode,DeptCode';
       Prepared;
       Execute;

       CommandText:=' Update wipInv Set wipAmount=wipInvQty*((a.InvInAmount+a.InvlmAmount)/(a.Invlmqty+a.InvInqty)) '
        +' from (select DeptCode,ItemCode,sum(InvlmAmount) as InvlmAmount,sum(Invlmqty) as Invlmqty,sum(InvInqty) as InvInqty,sum(InvInAmount) as InvInAmount '
        +' from #MonthSumQry group by DeptCode,ItemCode) a '
        +' Where wipInv.DeptCode=a.DeptCode and wipInv.ItemCode=a.ItemCode '
        +' and wipInv.wipInvMonth='+quotedstr(lbl_Month.Caption)
        +' and wipInv.ItemType=0 and (a.InvInqty+a.Invlmqty)<>0 ';
       Prepared;
       Execute;

       CommandText:=' Update wipInv Set wipInv.wipAmount=wipInv.wipInvQty*wipPrice.Itemprice '
        +' from wipPrice '
        +' Where wipInv.ItemCode=wipPrice.ItemCode '
        +' and wipPrice.DeptCode=wipInv.DeptCode '
        +' and wipPrice.wiPMonth=wipInv.wipInvMonth '
        +' and wipInv.wipInvMonth='+quotedstr(lbl_Month.Caption)
        +' and wipInv.ItemType=0 and wipInv.wipInvQty<>0 and isnull(wipInv.wipAmount,0)=0 ';
       Prepared;
       Execute;
        }
      Label2.Caption:='';
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update InvStatus Set InvStatus='''+Lbl_Month.Caption+''''
        +' Where InvStatusName=''clsperiod''';
      AdoQry_Tmp.ExecSQL;

      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Delete CurrentPeriodInv '+
                           ' drop table #MonthSumQry ';
      AdoQry_Tmp.ExecSQL;
      AdoQry_Tmp.Close;

      CommandText:='Insert CurrentPeriodInv (WHCode,WhPositionCode'
        +',ItemCode,OnHandInv,OnCheckInv,FreezeInv,WasterInv,OnShipInv)'
        +' Select WHCode,WhPositionCode'
        +',ItemCode,OnHandInv,OnCheckInv,FreezeInv,WasterInv,OnShipInv'
        +' From CurrentInv';
       Prepared;
       Execute;

    end;


      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Insert InvParam (InvParamCode,InvParamName'
        +',InvValueC)'
        +' Values('
        +' ''ClsPeriodRecord'''
        +',''结帐记录'''
        +',''结帐月份:'+Lbl_Month.Caption
        +'.结帐日期:''+Convert(varChAr(10),GetDate())+'+'''.操作人:'+UserCode+''''
        +')';
      AdoQry_Tmp.ExecSQL;

      DBConnect.CommitTrans;

    except
      DBConnect.RollBackTrans;
      Lbl_State.Caption:='结帐失败！';
      btn_ok.Enabled:=True;
      btn_Cancel.Enabled:=True;
      Abort;
    end;
    Lbl_State.Caption:='结帐完成！';
    btn_ok.Enabled:=True;
    btn_Cancel.Enabled:=True;
  end;
end;

procedure TFrm_Inv_CloseBook.SetDBConnect(AdOConnection: TAdOConnection);
begin
  inherited;
  AdoQry_Temp.Connection:=DBConnect;
  Adocommand1.Connection:=DBConnect;
  AdoQuery.Connection:=DBConnect;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select InvStatus As InvStatus From InvStatus'
    +' Where InvStatusName=''clsperiod''';
  AdoQry_Tmp.Open;
  Lbl_Month.Caption:=FormatDateTime('yyyy.mm',IncMonth(StrToDateTime(
    FormatFloat('0000.00',AdoQry_Tmp.fieldbyname('InvStatus').AsFloat)+'.01'),1));
  Lbl_State.Caption:='';
  UpdateMonth:=FormatDateTime('yyyy.mm',IncMonth(StrToDateTime(
    FormatFloat('0000.00',AdoQry_Tmp.fieldbyname('InvStatus').AsFloat)+'.01'),2));
end;

procedure TFrm_Inv_CloseBook.btn_CancelClick(Sender: TObject);
begin
  Close;
end;

procedure TFrm_Inv_CloseBook.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  Action:=caFree;
end;

end.