inv_monthsum2.pas

一个MRPII系统源代码版本

//全月加权平均

unit Inv_MonthSum2;

Interface

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

Type
  TFrm_Inv_MonthSum2 = Class(TFrm_Base_Dialog)
    Label1: TLabel;
    lbl_ShowMonth: TLabel;
    lbl_Status: TLabel;
    AdOCommand1: TAdOCommand;
    AdoQry_Temp: TAdoQuery;
    AdoQuery: TAdoQuery;
    Label2: TLabel;
    AdoDS_OpBill: TAdODataSet;
    ChckBx_NextMonth: TCheckBox;
    procedure btn_okClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure btn_CancelClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    LastMonth,CalcMonth,NextMonth:string;
    //删除上次计算产生的汇总数据
    procedure DelCalcMonthData;
    //初始化处理月份上月结存数据
    procedure InitCalcMonthLmData;
    //清除其它出库（0299）外的所有出库单据不含税价格、金额(后来变成全部都清除)
    //清除委外材料费、定额差异调整等单据的金额
    procedure clearBillPrice;
    //计算委外材料数量收发存
    procedure CalcOPQtyMonthSum;
    //计划价核算仓库单据价格替换
    procedure SPWhPriceConvert;
    //本厂收入汇总
    procedure InSum;
    //本厂发出汇总
    procedure OutSum;
    //本厂月末数据计算
    procedure CalcBlncData;
    //委外加工产品价格计算
    procedure CalcOPBill;
    //最后再次计算本厂的月末数据  wxp
    procedure calcblncenddata;
    //重新计算下月移动加权平均价
    procedure CalcNextMonthPrice;
    //仓库数据发生改动标志复位
    procedure ResetInvStatus;
    //计算某月份的移动加权平均价
    procedure CaculatePrice(UpdateMonth:String);
    //委外材料费计算
    procedure OPCalculate(InputItemCode:string);
    //供应商平均价计算
    function CalculateOpPrice(InputItemCode,InputVendorCode:string):double;

    procedure AdoCmdExecSql(DispStr,SqlStr:string);
    procedure CalcCurrentInv;  //重新计算从处理月份到现在的当前库存量；
    { Private declarations }
  public
  procedure SetDBConnect(AdOConnection:TAdOConnection);Override;
    { Public declarations }
  end;

var
  Frm_Inv_MonthSum2: TFrm_Inv_MonthSum2;

implementation
uses Sys_Global;
{$R *.DFM}

{ TFrm_Inv_MonthSum }

procedure TFrm_Inv_MonthSum2.SetDBConnect(AdOConnection: TAdOConnection);
begin
  inherited;
  //定义AdoQuery等控件的数据库连接
  AdoQry_Temp.Connection:=AdOConnection;
  AdoQry_Temp.EnableBCD:=False;
  AdoQuery.Connection:=AdOConnection;
  AdoQuery.EnableBCD:=False;
  AdoQry_Tmp.Connection:=AdOConnection;
  AdoQry_Tmp.EnableBCD:=False;

  AdODS_OpBill.Connection:=AdOConnection;
  AdODS_OpBill.EnableBCD:=False;

  AdOCommand1.Connection:=AdOConnection;

  lbl_ShowMonth.Caption:='';
  lbl_Status.Caption:='';
  with AdoQry_Tmp do
  begin
    Close;
    sql.text:='select left(convert(varchAr,Invstatus),7) Invstatus from Invstatus where InvstatusName=''clsperiod''';
    open;
    LastMonth:=fieldbyname('Invstatus').asstring;
    lbl_ShowMonth.Caption:=formatdatetime('yyyy.mm',
        strtodatetime(fieldbyname('Invstatus').asstring+'.28')+20);
    CalcMonth:=lbl_ShowMonth.Caption;
    NextMonth:=formatdatetime('yyyy.mm',
        strtodatetime(CalcMonth+'.28')+20);
    btn_ok.SetFocus;
  end;
end;

procedure TFrm_Inv_MonthSum2.btn_okClick(Sender: TObject);
begin
  inherited;
  if lbl_Status.Caption<>'月末处理完成!' then
  begin
    if DispInfo('确认开始月末处理吗?',2)='y' then
    begin
      lbl_Status.Caption:='正在进行月末处理......';
      lbl_Status.Refresh;
    end
    else
    begin
      lbl_Status.Caption:='用户取消月末处理!';
      lbl_Status.Refresh;
      exit;
    end;
    //检查是否有未核价单据
    with AdoQry_tmp do
    begin
      Close;
      sql.text:='select Count(*) aa from InvInBill where InvBillStkchck=0 '+
                ' and InvBillMonth='+QuotedStr(lbl_ShowMonth.Caption)+
                ' and BillTypeCode in (''0101'',''0102'',''0103'')';
      open;
      if fieldbyname('aa').AsInteger>0 then
      begin
        if DispInfo('有未核价的单据存在，是否继续月末处理?',2)='y' then
        begin
          lbl_Status.Caption:='正在进行月末处理......';
          lbl_Status.Refresh;
        end
        else
        begin
          lbl_Status.Caption:='用户取消月末处理!';
          lbl_Status.Refresh;
          exit;
        end;
      end;
    end;

    btn_ok.Enabled:=False;
    btn_Cancel.Enabled:=False;
    ChckBx_NextMonth.enabled:=False;

    //计算处理月份平均价，其中包括委外产品的材料费
    DBConnect.beginTrans;
    try
      //删除上次计算产生的汇总数据
      DelCalcMonthData;
      //初始化处理月份上月结存数据
      InitCalcMonthLmData;
      //清除其它出库（0299）外的所有出库单据不含税价格、金额(后来变成全部都清除)
      //清除委外材料费、定额差异调整等单据的金额
      clearBillPrice;
      //计算委外材料数量收发存
      CalcOPQtyMonthSum;
      //计划价核算仓库单据价格替换
      SPWhPriceConvert;
      //本厂收入汇总
      InSum;
      //本厂发出汇总
      OutSum;
      //本厂月末数据计算
      CalcBlncData;
      //委外加工产品价格计算
      CalcOPBill;
      //本厂月末数据最后计算 wxp
      calcblncenddata;

      DBConnect.CommitTrans;
    except
      on e:Exception do
      begin
        DBConnect.RollBackTrans;
        RecOrderror(Self,AdoQry_tmp,E.message);
        DispInfo('月末处理发生错误，请查看系统异常错误记录！',1);
        btn_Cancel.Enabled:=True;
        Abort;
      end;
    end;

    //重新计算下月移动加权平均价
    if ChckBx_NextMonth.checked then
      CalcNextMonthPrice;

    DBConnect.beginTrans;
    try
      CalcCurrentInv;  //重新计算从处理月份到现在的当前库存量；( Add by zks 02.11.21)
      //仓库数据发生改动标志复位
      ResetInvStatus;
      DBConnect.CommitTrans;
    except
      on e:Exception do
      begin
        DBConnect.RollBackTrans;
        RecOrderror(Self,AdoQry_tmp,E.message);
        DispInfo('月末处理发生错误，请查看系统异常错误记录！',1);
        btn_Cancel.Enabled:=True;
        Abort;
      end;
    end;
    lbl_Status.Caption:='月末处理完成!';
    Label2.Caption:='';

    btn_Cancel.Enabled:=True;
  end
  else
    btn_Cancel.Click;
end;

//删除上次计算产生的汇总数据
procedure TFrm_Inv_MonthSum2.DelCalcMonthData;
begin
  AdoCmdExecSql
    ('正在清除上次计算的结果......',
     'Delete From InvMonthSum '+
      '  where InvMonth='+quotedstr(CalcMonth));
  AdoCmdExecSql
    ('正在清除上次计算的结果......',
     'Delete From OpMonthSum '+
      '  where InvMonth='+quotedstr(CalcMonth));
  AdoCmdExecSql
    ('正在清除上次计算的结果......',
     'Delete From WhPMonthSum '+
      '  where InvMonth='+quotedstr(CalcMonth));
end;

//初始化处理月份上月结存数据
procedure TFrm_Inv_MonthSum2.InitCalcMonthLmData;
begin
  AdoCmdExecSql
    ('正在对本月的期初数据进行初始化......',
     'insert into InvMonthSum '
        +'(InvMonth,whCode,ItemCode,InvLMQty,InvLMPrice,InvLMAmount) '
        +'select '+quotedstr(CalcMonth)
        +'    ,whCode,ItemCode,InvBlncQty,InvBlncPrice,InvBlncAmount '
        +'  from InvMonthSum '
        +'  where InvMonth='+quotedstr(LastMonth)
        +' ');
  AdoCmdExecSql
    ('正在对本月的期初数据进行初始化......',
     'insert into whPMonthSum '
        +'(InvMonth,whCode,WhPositionCode,ItemCode,InvLMQty) '
        +'select '+quotedstr(CalcMonth)
        +'    ,whCode,WhPositionCode,ItemCode,InvBlncQty '
        +'  from whPMonthSum '
        +'  where InvMonth='+quotedstr(LastMonth)
        +'    and InvBlncQty<>0');
  AdoCmdExecSql
    ('正在对本月的期初数据进行初始化......',
     'insert into OpMonthSum '
        +'(InvMonth,VendorCode,ItemCode,InvLMQty,InvLMPrice,InvLMAmount) '
        +'select '+quotedstr(CalcMonth)
        +'    ,VendorCode,ItemCode,InvBlncQty,InvBlncPrice,InvBlncAmount '
        +'  from OpMonthSum '
        +'  where InvMonth='+quotedstr(LastMonth)
        +' ');
end;

//清除其它出库（0299）外的所有出库单据不含税价格、金额(后来变成全部都清除)
//清除委外材料费、定额差异调整等单据的金额
procedure TFrm_Inv_MonthSum2.clearBillPrice;
begin
  AdoCmdExecSql
    ('正在清除出库单据的价格、金额......',
     'update InvOutBillLine '+
     '  set InvBillNoTaxPrice=0,InvBillNoTaxAmount=0 '+
     '  from InvOutBill b '+
     '  where InvOutBillLine.InvBillID=b.InvBillID '+
     '    and b.AmountAdjust=0 '+
     '    and b.BillTypeCode not in (''0205'',''0206'') '+
     '    and b.InvBillMonth='+quotedstr(CalcMonth));
//     '    and b.BillTypeCode<>''0299''');

  AdoCmdExecSql
    ('正在清除委外产品材料费等单据的金额......',
     'update InvInBillLine '+
     '  set InvBillNoTaxPrice=0,InvBillNoTaxAmount=0 '+
     '  from InvInBill b '+
     '  where InvInBillLine.InvBillID=b.InvBillID '+
     '    and b.InvBillMonth='+quotedstr(CalcMonth)+
     '    and b.BillTypeCode in (''1201'',''1202'')');
  AdoCmdExecSql
    ('正在清除委外产品材料费等单据的金额......',
     'update OpBill '+
     '  set OPBillNoTaxPrice=0,OPBillNoTaxAmount=0 '+
     '  from InvInBill b '+
     '  where OPBill.InvBillID=b.InvBillID '+
     '    and b.InvBillMonth='+quotedstr(CalcMonth));
end;

//计算委外材料数量收发存
procedure TFrm_Inv_MonthSum2.CalcOPQtyMonthSum;
begin
//收入方应该是InvBillLINE中InvBillQTY>0的记录  wxp
  AdoCmdExecSql
    ('正在进行委外加工材料收入方数量汇总......',
     'Insert OpMonthSum '+
     '  (InvMonth,VendorCode,ItemCode) '+
     '  Select a.InvBillMonth,a.VendorCode,a.ItemCode '+
     '    from '+
     '      (select distinct b.InvBillMonth,b.VendorCode,bl.ItemCode'+
     '         from InvOutBill b,InvOutBillLine bl'+
     '         where b.InvBillMonth='+quotedstr(CalcMonth)+
     '           and b.InvBillid=bl.InvBillid  and bl.InvBillqty>=0 '+
     '           and (b.BillTypeCode=''0202'' or (b.BillTypeCode=''0299'' and b.OpBill=1))) a '+
     '    Left Join OpMonthSum OPm'+
     '      on OPm.InvMonth=a.InvBillMonth'+
     '        and OPm.VendorCode=a.VendorCode'+
     '        and OPm.ItemCode=a.ItemCode'+
     '        and OPm.InvMonth='+quotedstr(CalcMonth)+
     '    where OPm.InvMonth is Null');

  AdoCmdExecSql
    ('正在进行委外加工材料收入方数量汇总......',
     'update OpMonthSum '+