bas_opbalance_d.pas

一个MRPII系统源代码版本

unit Bas_OpBalance_D;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Detail, Mask, StdCtrls, ExtEdit, Db, AdODB, ExtCtrls;

Type
  TFrm_Bas_OpBalance_D = Class(TFrm_Base_Detail)
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    lbl_Vendor: TLabel;
    lbl_UomName: TLabel;
    lbl_Price: TLabel;
    Extedt_ItemCode: TExtEdit;
    Extedt_InvAmount: TMaskEdit;
    Extedt_InvQty: TExtEdit;
    lbl_ItemName: TLabel;
    Extedt_Vendor: TExtEdit;
    medt_InvMonth: TMaskEdit;
    procedure Extedt_InvQtyExit(Sender: TObject);
    procedure Extedt_InvAmountExit(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure btn_okClick(Sender: TObject);
    procedure InOutItemCodeCheck(Sender: TObject);
    procedure MonthCheck(Sender: TObject);

  private
    { Private declarations }
    oldItemCode,oldInvQty,oldInvAmount,oldVendorCode:string;
  public
//    procedure SetStatus(CurrentStatus:String;var EnableControls:String); Override;
    procedure InitControls; Override;
    procedure SaveData; Override;
    { Public declarations }
  end;

var
  Frm_Bas_OpBalance_D: TFrm_Bas_OpBalance_D;

implementation

uses Bas_OpBalance,Sys_Global;

{$R *.DFM}
{procedure TFrm_Bas_OpBalance_D.SetStatus(CurrentStatus: String;
  var EnableControls: String); //子类重载此过程
begin
  inherited;
  //指定不需要Enable的控制件名称用","分开,最后一定加上","
  EnableControls:='medt_InvMonth,Extedt_Vendor,Extedt_ItemCode';
end; }
procedure TFrm_Bas_OpBalance_D.InitControls;
begin
  inherited;
  with AdoQry_Maintain do
  begin
//    lbl_Month.Caption:=Frm_Bas_OpBalance.lbl_Month.Caption;
    medt_InvMonth.Text :=fieldbyname('InvMonth').asstring;
    Extedt_Vendor.Text :=fieldbyname('VendorCode').asstring;
    lbl_Vendor.Caption :=fieldbyname('VendorName').asstring;
    oldVendorCode:=fieldbyname('VendorCode').asstring;
    Extedt_ItemCode.text:=fieldbyname('ItemCode').asstring;
    lbl_ItemName.Caption :=fieldbyname('ItemName').asstring;
    oldItemCode:=fieldbyname('ItemCode').asstring;
    lbl_UomName.Caption:=fieldbyname('UomName').asstring;
    Extedt_InvQty.text:=fieldbyname('InvQty').asstring;
    if fieldbyname('InvQty').asstring='' then
      oldInvQty:='0'
    else
      oldInvQty:=fieldbyname('InvQty').asstring;
    Extedt_InvAmount.text:=fieldbyname('InvAmount').asstring;
    if fieldbyname('InvAmount').asstring='' then
      oldInvAmount:='0'
    else
      oldInvAmount:=fieldbyname('InvAmount').asstring;
  end;
  if status='Edit' then
  begin
    medt_InvMonth.ReadOnly :=True;
    Extedt_Vendor.ReadOnly :=True;
    Extedt_ItemCode.ReadOnly :=True;
    Extedt_InvQty.SetFocus ;
  end
  else if status='Add' then
  begin
    medt_InvMonth.ReadOnly :=False;
    Extedt_Vendor.ReadOnly :=False;
    Extedt_ItemCode.ReadOnly :=False;
    medt_InvMonth.Text :=copy(datetostr(now),1,7);
//    medt_InvMonth.SetFocus ;
  end;

end;

procedure TFrm_Bas_OpBalance_D.SaveData;
var
  sqltext,aa,tmp_Itemfields,tmp_CurrentInvfields,lastMonth:string;
begin
  inherited;
  //如果已有结帐，则将其写入最近的结帐月份 wxp
  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.text:=
      ' select left(convert(varchAr,Invstatus),7) as InvStatus '+
      '  from Invstatus '+
      ' where InvStatusName=''clsperiod'' ';
    open;
    if not eof then
      lastMonth:=Trim(fieldbyname('Invstatus').asstring)
    else
      lastMonth:=copy(datetostr(incMonth(strtodate(Trim(medt_InvMonth.Text)+'.01'),-1)),1,7);
  end;

  //如果当前库存没有记录，增加，有则更改
  sqltext:=' if not exists (select ItemCode from opCurrentInv '+
         '                where VendorCode='''+Extedt_Vendor.Text+''''+
         '                  and ItemCode='''+Extedt_ItemCode.text+''') '+
         '    insert opCurrentInv(VendorCode,'+
                             ' ItemCode,'+
                             'OPInv )'+
         '    Values ('''+Extedt_Vendor.Text+''','+
                    ''''+Extedt_ItemCode.text+''','+
                    Extedt_InvQty.text+')'+
         '  else '+
         '    update opCurrentInv set '+
         '    opInv='+Extedt_InvQty.text+
         '     where VendorCode='''+Extedt_Vendor.Text+''''+
         '                  and ItemCode='''+Extedt_ItemCode.text+''' '+
        // 更改物料主件中产总库存

        //月末收发存汇总表有记录，则更改当前库存数量，当前库存金额，当前库单价，没有就增加
         ' if not exists (select ItemCode '+
                           ' from OpMonthSum '+
                           ' where InvMonth='''+lastMonth+''''+
                           '   and VendorCode='''+Extedt_Vendor.Text+''''+
                           '   and ItemCode='''+Extedt_ItemCode.text+''') '+
         '    insert OpMonthSum '+
         '        (InvMonth,'+
                  'VendorCode,'+
                  'ItemCode,'+
                  'Invblncqty,'+
                  'InvblncAmount,'+
                  'InvblncPrice) '+
            ' Values('''+lastMonth+''','+
                    ''''+Extedt_Vendor.Text+''','+
                    ''''+Extedt_ItemCode.text+''','+
                    Extedt_InvQty.text+','+
                    Extedt_InvAmount.text+','+
                    lbl_Price.Caption+')'+
          ' else '+
         '     update OpMonthSum '+
                 ' set '+
                 'Invblncqty=Invblncqty+'+Extedt_InvQty.text+'-('+oldInvQty+'),'+
                 'InvblncAmount=InvblncAmount+'+Extedt_InvAmount.text+'-('+oldInvAmount+'),'+
                 'InvblncPrice=case when isnull((Invblncqty+'+Extedt_InvQty.text+'-('+oldInvQty+')),0)=0 then 0 else (InvblncAmount+'+Extedt_InvAmount.text+'-('+oldInvAmount+'))/(Invblncqty+'+Extedt_InvQty.text+'-('+oldInvQty+')) end '+
              ' where InvMonth='''+lastMonth+''''+
              '  and VendorCode='''+Extedt_Vendor.Text+''''+
              '  and ItemCode='''+Extedt_ItemCode.text+''''+
       //判断当前仓库是否为加权平权价
       {' if exists (select VendorCode '+
                     ' from opAveragePrice '+
                     ' where VendorCode='''+getCode(Extedt_Vendor.Text)+''''+
                     ' ) '+
       ' begin '+   }
        //移动加权平均价中存在记录，则更改数量，金额，单价，没有则增加
         ' if not exists (select ItemCode '+
                          ' from opAveragePrice '+
                          ' where VendorCode='''+Extedt_Vendor.Text+''''+
                          '   and ItemCode='''+Extedt_ItemCode.text+''') '+
         '    insert opAveragePrice '+
                 '(VendorCode,'+
                 ' ItemCode,'+
                 'opApqty,'+
                 'opApAmount,'+
                 'opAveragePrice)'+
              ' Values('''+Extedt_Vendor.Text+''','+
                      ''''+Extedt_ItemCode.text+''','+
                      Extedt_InvQty.text+','+
                      Extedt_InvAmount.text+','+
                      lbl_Price.Caption+') '+
         '  else '+
              ' update opAveragePrice '+
              '   set '+
              '   opApqty=opApqty+'+Extedt_InvQty.text+'-('+oldInvQty+'),'+
              '   opApAmount=opApAmount+'+Extedt_InvAmount.text+'-('+oldInvAmount+'),'+
              '   opAveragePrice=case when isnull((opApqty+'+Extedt_InvQty.text+'-('+oldInvQty+')),0)=0 then 0 else (opApAmount+'+Extedt_InvAmount.text+'-('+oldInvAmount+'))/(opApqty+'+Extedt_InvQty.text+'-('+oldInvQty+')) end '+
              ' where VendorCode='''+Extedt_Vendor.Text+''''+
              '   and ItemCode='''+Extedt_ItemCode.text+'''';
//       ' end ';
  if (Add) then
  begin
    with AdoQry_Tmp do
    begin
      Close;
      SQL.clear;
      aa:=
      //增加记录到InvBALACE
         'Insert OpBalance (InvMonth,'+
                          'VendorCode,'+
                          'ItemCode,'+
                          'InvQty,'+
                          'InvAmount) ValueS ('+
                   ''''+medt_InvMonth.Text+''','+
                   ''''+Extedt_Vendor.Text+''','+
                   ''''+ExtEdt_ItemCode.text+''','+
                    ExtEdt_InvQty.text+','+
                   ExtEdt_InvAmount.text+') ';
        sql.text:=aa+sqltext;

    ExecSQL;
    end
  end
  else
  begin
    with AdoQry_Tmp do
    begin
      Close;
      SQL.clear;
      sql.text:=
      //更改旧的物料Balance
         ' update OpBalance set VendorCode='''+Extedt_Vendor.Text+''','+
             'ItemCode='''+Extedt_ItemCode.Text+''','+
             'InvQty=('+Extedt_InvQty.Text+'),'+
             'InvAmount=('+Extedt_InvAmount.Text+')'+
             ' where VendorCode='''+oldVendorCode+''''+
             '   and ItemCode='''+oldItemCode+'''';
      if (oldVendorCode<>Extedt_Vendor.Text) or (oldItemCode<>Extedt_ItemCode.Text) then
        sql.text:=sql.text+ ' update opCurrentInv set '+
         'opInv=opInv-('+oldInvQty+
         ')     where VendorCode='''+oldVendorCode+''''+
         '                  and ItemCode='''+oldItemCode+''' '+
         '     update OpMonthSum '+
                 ' set '+
                 'Invblncqty=Invblncqty'+'-('+oldInvQty+'),'+