inv_monthsum1.pas

一个MRPII系统源代码版本

//功能：完成仓库月末结帐。
unit Inv_MonthSum1;

Interface

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

Type
  TFrm_Inv_MonthSum1 = Class(TFrm_Base_Dialog)
    Label1: TLabel;
    lbl_ShowMonth: TLabel;
    lbl_Status: TLabel;
    AdOCommand1: TAdOCommand;
    AdoQry_Temp: TAdoQuery;
    AdoQuery: TAdoQuery;
    Label2: TLabel;
    procedure btn_okClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure btn_CancelClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    LC_Counted_Month:string;
    SpPrice:double;                           //标准成本价
    procedure UpdateInvOutBillLinePrice;      //更改出库单价格；
    procedure UpdateInvInBillLinePrice;         //更改入库单价格；
    procedure InvMonthDiversitySum;             //产生采购差异数据；
    procedure DelIns_CurMonth_Record;           //删除、插入更新记录
    procedure Convert;                          //转换新的数据
    procedure Caculate;                         //汇总计算值
    procedure CaculatePrice(UpdateMonth:String);
    procedure CalcCurrentInv;                   //重新计算从处理月份到现在的当前库存量；
    procedure CalcOPQtyMonthSum;                //计算委外材料数量收发存;
    procedure AdoCmdExecSql(DispStr,SqlStr:string);
    { Private declarations }
  public
  procedure SetDBConnect(AdOConnection:TAdOConnection);Override;
    { Public declarations }
  end;

var
  Frm_Inv_MonthSum1: TFrm_Inv_MonthSum1;

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

{ TFrm_Inv_MonthSum }

procedure TFrm_Inv_MonthSum1.btn_okClick(Sender: TObject);
begin
  inherited;
  if lbl_Status.Caption<>'月末处理完成!' then
  begin
    if Application.messagebox('真的开始月末处理吗?','提示信息',MB_OKCancel + MB_DEFBUTTON1)=IDOk then
    begin
      lbl_Status.Caption:='正在进行月末处理......';
      lbl_Status.Refresh;
    end
    else
    begin
      lbl_Status.Caption:='用户取消月末处理';
      lbl_Status.Refresh;
      exit;
    end;
    btn_ok.Enabled:=False;
    btn_Cancel.Enabled:=False;
    {DBConnect.beginTrans;
    try
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update AveragePrice Set ApQty=0'
        +',ApAmount=0'
        +',AveragePrice=0';
      AdoQry_Tmp.ExecSQL;
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update OpAveragePrice Set OpApQty=0'
        +',OpApAmount=0'
        +',OpAveragePrice=0';
      AdoQry_Tmp.ExecSQL;

      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update AveragePrice Set ApQty=InvBlncQty'
        +',ApAmount=InvBlncAmount'
        +',AveragePrice=InvBlncPrice'
        +' From InvMonthSum'
        +' Where AveragePrice.ItemCode=InvMonthSum.ItemCode'
        +' And AveragePrice.WHCode=InvMonthSum.WHCode'
        +' And InvMonthSum.InvMonth='''+FormatDateTime('yyyy.mm',IncMonth(StrToDate(lbl_ShowMonth.Caption+'.01'),-1))+'''';
      AdoQry_Tmp.ExecSQL;

      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Update OpAveragePrice Set OpApQty=InvBlncQty'
        +',OpApAmount=InvBlncAmount'
        +',OpAveragePrice=InvBlncPrice'
        +' From OpMonthSum'
        +' Where OpAveragePrice.ItemCode=OpMonthSum.ItemCode'
        +' And OpAveragePrice.VendorCode=OpMonthSum.VendorCode'
        +' And OpMonthSum.InvMonth='''+FormatDateTime('yyyy.mm',IncMonth(StrToDate(lbl_ShowMonth.Caption+'.01'),-1))+'''';
      AdoQry_Tmp.ExecSQL;

      DBConnect.CommitTrans;
    except
      on e:Exception do
      begin
        DBConnect.RollBackTrans;
        RecOrderror(Self,AdoQry_tmp,E.message);
        DispInfo('月末处理发生错误！',1);
        btn_ok.Enabled:=True;
        btn_Cancel.Enabled:=True;
        Abort;
      end;
    end;}
    //CaculatePrice(lbl_ShowMonth.Caption);
    AdOCommand1.Connection.beginTrans;
    try
      UpdateInvOutBillLinePrice;                 //更改出库单价格；
      UpdateInvInBillLinePrice;                  //更改入库单价格；
      //InvMonthDiversitySum;                    //产生采购差异数据；
      DelIns_CurMonth_Record;
      Convert;
      Caculate;
      CalcOPQtyMonthSum;                         //计算委外材料数量收发存; (021226)
      CalcCurrentInv;                            //重新计算从处理月份到现在的当前库存量；
      AdOCommand1.Connection.CommitTrans;
    except
      on e:Exception do
      begin
        DBConnect.RollBackTrans;
        RecOrderror(Self,AdoQry_tmp,E.message);
        DispInfo('月末处理发生错误！',1);
        btn_ok.Enabled:=True;
        btn_Cancel.Enabled:=True;
        Abort;
      end;
    end;

    {DBConnect.beginTrans;
    try
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:=' Update InvMonthSum Set'
        +' InvBlncPrice=AveragePrice.AveragePrice'
        +' From AveragePrice'
        +' Where AveragePrice.WHCode=InvMonthSum.WHCode'
        +' And AveragePrice.ItemCode=InvMonthSum.ItemCode'
        +' And InvMonthSum.InvBlncQty=0'
        +' And InvMonthSum.InvMonth='''+lbl_ShowMonth.Caption+'''';
      AdoQry_Tmp.ExecSQL;
      DBConnect.CommitTrans;
    except
      on e:Exception do
      begin
        DBConnect.RollBackTrans;
        RecOrderror(Self,AdoQry_tmp,E.message);
        DispInfo('月末处理发生错误！',1);
        btn_ok.Enabled:=True;
        btn_Cancel.Enabled:=True;
        Abort;
      end;
    end;}

    //CaculatePrice(FormatDateTime('yyyy.mm',IncMonth(StrToDate(lbl_ShowMonth.Caption+'.01'),1)));
    lbl_Status.Caption:='月末处理完成!';
    btn_ok.Enabled:=True;
    btn_Cancel.Enabled:=True;
  end
  else
    btn_Cancel.Click;
end;

procedure TFrm_Inv_MonthSum1.DelIns_CurMonth_Record;
var
  SQL_Txt,SQL_Txt1,SQL_Txt2:string;
begin
  SQL_Txt:='Delete From InvMonthSum '+  //删除InvMonthSum中等于计算月份记录
           'where InvMonth='+
           ' '''+lbl_ShowMonth.Caption+''' ';
  SQL_Txt1:='Delete From OpMonthSum '+ //删除OpMonthSum中等于计算月份记录
            'where InvMonth='+
            ' '''+lbl_ShowMonth.Caption+''' ';
  SQL_Txt2:='Delete From WhPMonthSum '+  //删除WhPMonthSum中等于计算月份记录
         'where InvMonth='+
         ' '''+lbl_ShowMonth.Caption+''' ';

  with AdOCommand1 do
  begin
    Application.ProcessMessages;
    lbl_Status.Caption:='正在清除上次计算的结果......';
    lbl_Status.Refresh;
    Application.ProcessMessages;

    //删除InvMonthSum中等于计算月份记录
    commandtext:=SQL_Txt;
    Prepared;
    Execute;

    //删除OpMonthSum中等于计算月份记录
    CommandText:=SQL_Txt1;
    Prepared;
    Execute;

    //删除whPMonthSum中等于计算月份记录
    CommandText:=SQL_Txt2;
    Prepared;
    Execute;

    Application.ProcessMessages;
    lbl_Status.Caption:='正在对本月的期初数据进行初始化......';
    lbl_Status.Refresh;
    Application.ProcessMessages;
//插入InvMonthSum中
    SQL_Txt2:='insert into InvMonthSum '+
              '(InvMonth,whCode,ItemCode,InvLMQty,InvLMAmount,'+
              'InvInQty,InvInAmount,InvOutQty,'+
              'InvOutAmount,InvBlncQty,InvBlncAmount,'+
              'InvLMPrice,InvInPrice,InvOutPrice,InvBlncPrice,InvLmRealPrice)'+
                'select '''+lbl_ShowMonth.Caption+''','+
                        'whCode,'+
                        'ItemCode,'+
                        'InvBlncQty,'+
                        'InvBlncAmount,'+
                        '0,'+
                        '0,'+
                        '0,'+
                        '0,'+
                        '0,'+
                        '0,'+
                        'InvBlncPrice,'+
                        '0,'+
                        '0,'+
                        '0 ,'+
                        'InvBlncRealPrice '+
              'from InvMonthSum'+
              ' where InvMonth='+
              format('''%s''',[LC_Counted_Month])+
              ' and (InvBlncQty<>0 or InvBlncAmount<>0)';

    CommandText:=SQL_Txt2;
    Prepared;
    Execute;
    //插入WhPMonthSum中
    SQL_Txt2:='insert into WhPMonthSum '+
                'select InvMonth='''+lbl_ShowMonth.Caption+''','+
                        'whCode,'+
                        'WhPositionCode,'+
                        'ItemCode,'+
                        'InvLMQty=InvBlncQty,'+
                        'InvInQty=0,'+
                        'InvOutQty=0,'+
                        'InvBlncQty=0 '+
              'from WhPMonthSum '+
              ' where InvMonth='+
              format('''%s''',[LC_Counted_Month])+
              ' and InvBlncQty<>0';

    CommandText:=SQL_Txt2;
    Prepared;
    Execute;

    //插入OpMonthSum中
    SQL_Txt2:='insert into OpMonthSum '+
              '(InvMonth,VendorCode,ItemCode,InvLMQty,'+
              ' InvLMAmount,InvInQty,InvInAmount,InvOutQty,'+
              'InvOutAmount,'+
              ' InvBlncQty,InvBlncAmount,InvLMPrice,'+
              'InvInPrice,InvOutPrice,InvBlncPrice)'+
                'select '''+lbl_ShowMonth.Caption+''','+
                        'VendorCode,'+
                        'ItemCode,'+
                        'InvBlncQty,'+
                        'InvBlncAmount,'+
                        '0,'+
                        '0,'+
                        '0,'+
                        '0,'+
                        '0,'+
                        '0,'+
                        'InvBlncPrice,'+
                        '0,'+
                        '0,'+
                        '0 '+