bas_invbalance_d.pas

一个MRPII系统源代码版本

unit Bas_InvBalance_D;

Interface

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

Type
  TFrm_Bas_InvBalance_D = Class(TFrm_Base_Detail)
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    lbl_UomName: TLabel;
    lbl_Price: TLabel;
    Extedt_ItemCode: TExtEdit;
    Extedt_InvAmount: TMaskEdit;
    Extedt_InvQty: TExtEdit;
    lbl_ItemName: TLabel;
    medt_Month: TMaskEdit;
    cmbbx_WhCode: TComboBox;
    cmbbx_WhPositionCode: TComboBox;
    procedure Extedt_InvQtyExit(Sender: TObject);
    procedure InOutItemCodeCheck(Sender: TObject);
    procedure Extedt_InvAmountExit(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure cmbbx_WhCodeChange(Sender: TObject);
    procedure cmbbx_WhPositionCodeExit(Sender: TObject);
    procedure cmbbx_WhCodeExit(Sender: TObject);
    procedure btn_okClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
  private
    { Private declarations }
    old_WhCode,oldItemCode,oldInvQty,oldInvAmount:string;
    procedure InitCmbbx_WhPositionCode(whCode:string);
    function whCode_Check:boolean;
    function DefaultWHCheck(AdoQuery:TAdoQuery;WHCode:String;ItemCode:String):Boolean;
    function GetCbxPosition(InputCmBx:TcomboBox;AdoCode:string):integer;
  public
    procedure InitControls; Override;
    procedure SaveData; Override;
    { Public declarations }
  end;

var
  Frm_Bas_InvBalance_D: TFrm_Bas_InvBalance_D;

implementation

uses Bas_InvBalance,Sys_Global;

{$R *.DFM}

procedure TFrm_Bas_InvBalance_D.InitControls;
begin
  inherited;
  InitUsableWHCmBx(AdoQry_tmp,UserCode,cmbbx_WhCode,False);
  cmbbx_WhCodeChange(cmbbx_WhCode);
  with AdoQry_Maintain do
  begin
    medt_Month.text:=fieldbyname('InvMonth').asstring;
    Extedt_ItemCode.text:=fieldbyname('ItemCode').asstring;
    oldItemCode:=fieldbyname('ItemCode').asstring;
    lbl_ItemName.Caption:=fieldbyname('ItemName').asstring;
    lbl_UomName.Caption:=fieldbyname('UomName').asstring;
    Extedt_InvQty.text:=fieldbyname('InvQty').asstring;
    oldInvQty:=fieldbyname('InvQty').asstring;
    Extedt_InvAmount.text:=fieldbyname('InvAmount').asstring;
    oldInvAmount:=fieldbyname('InvAmount').asstring;
    InitCmBxText(cmbbx_WhCode,fieldbyname('whCode').asstring);
    InitCmBxText(cmbbx_WhPositionCode,fieldbyname('WhPositionCode').asstring);
//    GetCbxPosition(cmbbx_WhCode,fieldbyname('whCode').asstring);
    old_WhCode:='';
  end;

  if status='Edit' then
  begin
    cmbbx_WhCode.Style :=csdropdown;
    cmbbx_WhPositionCode.Style :=csdropdown;
    cmbbx_WhCode.Text :=AdoQry_Maintain.fieldbyname('whCode').asstring+' '+AdoQry_Maintain.fieldbyname('whName').asstring;
    cmbbx_WhPositionCode.Text :=AdoQry_Maintain.fieldbyname('WhPositionCode').asstring+' '+AdoQry_Maintain.fieldbyname('WhPositionName').asstring;
    medt_Month.ReadOnly :=True;
    cmbbx_WhCode.Enabled :=False;
    cmbbx_WhPositionCode.Enabled :=False;
    Extedt_ItemCode.Enabled :=False;
    Extedt_InvQty.SetFocus ;
  end
  else if status='Add' then
  begin
    medt_Month.ReadOnly :=False;
    cmbbx_WhCode.Enabled :=True;
    cmbbx_WhPositionCode.Enabled :=True;
    Extedt_ItemCode.Enabled :=True;

  end;
end;

function TFrm_Bas_InvBalance_D.DefaultWHCheck(AdoQuery:TAdoQuery;WHCode,ItemCode:String):Boolean;
begin
  AdoQuery.Close;
  AdoQuery.SQL.Text:='Select Item.WHCode,Item.OnlyDefaultWH,Warehouse.WHName'
    +' From Item Join Warehouse On Item.WHCode=Warehouse.WHCode'
    +' Where ItemCode='''+ItemCode+'''';
  AdoQuery.Open;
  Result:=True;
  if (AdoQuery.fieldbyname('WHCode').AsString<>WHCode)and
    (AdoQuery.fieldbyname('WHCode').AsString<>'') then
  begin
    if AdoQuery.fieldbyname('OnlyDefaultWH').AsString='1' then
    begin
      DispInfo('该物料只能使用缺省仓库:'+AdoQuery.fieldbyname('WHName').AsString,1);
      Result:=False;
    end
    else
      DispInfo('该物料的缺省仓库应该是:'+AdoQuery.fieldbyname('WHName').AsString,3);
  end;
end;

procedure TFrm_Bas_InvBalance_D.SaveData;
var
  sqltext,tmp_Itemfields,tmp_CurrentInvfields,lastMonth,jiezhang_Month:string;
begin
  inherited;
   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
      jiezhang_Month:=Trim(fieldbyname('Invstatus').asstring)
    else
      jiezhang_Month:=copy(datetostr(incMonth(strtodate(Trim(medt_Month.text)+'.01'),-1)),1,7);
   end;
   tmp_Itemfields:=getInvfield(AdoQry_tmp,getCode(cmbbx_WhCode.text),getCode(cmbbx_WhPositionCode.text),'Item');
   tmp_CurrentInvfields:=getInvfield(AdoQry_tmp,getCode(cmbbx_WhCode.text),getCode(cmbbx_WhPositionCode.text),'CurrentInv');
  //如果已有结帐，则将其写入最近的结帐月份

  //如果当前库存没有记录，增加，有则更改
  sqltext:=' if not exists (select ItemCode from CurrentInv '+
         '                where whCode='''+getCode(cmbbx_WhCode.text)+''''+
         '                  and WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
         '                  and ItemCode='''+Extedt_ItemCode.text+''') '+
         '    insert CurrentInv(whCode,'+
                             ' WhPositionCode,'+
                             'ItemCode,'+
                             tmp_CurrentInvfields+')'+
         '    Values('''+getCode(cmbbx_WhCode.text)+''','+
                    ''''+getCode(cmbbx_WhPositionCode.text)+''','+
                    ''''+Extedt_ItemCode.text+''','+
                    Extedt_InvQty.text+')'+
         '  else '+
         '    update CurrentInv '+
         '       set '+
         tmp_CurrentInvfields+'='+tmp_CurrentInvfields+'+'+Extedt_InvQty.text+
         '     where whCode='''+getCode(cmbbx_WhCode.text)+''''+
         '                  and WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
         '                  and ItemCode='''+Extedt_ItemCode.text+''' '+
         ' if not exists (select ItemCode from CurrentPeriodInv '+
         '                where whCode='''+getCode(cmbbx_WhCode.text)+''''+
         '                  and WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
         '                  and ItemCode='''+Extedt_ItemCode.text+''') '+
         '    insert CurrentPeriodInv(whCode,'+
                             ' WhPositionCode,'+
                             'ItemCode,'+
                             tmp_CurrentInvfields+')'+
         '    Values('''+getCode(cmbbx_WhCode.text)+''','+
                    ''''+getCode(cmbbx_WhPositionCode.text)+''','+
                    ''''+Extedt_ItemCode.text+''','+
                    Extedt_InvQty.text+')'+
         '  else '+
         '    update CurrentPeriodInv '+
         '       set '+
         tmp_CurrentInvfields+'='+tmp_CurrentInvfields+'+'+Extedt_InvQty.text+
         '     where whCode='''+getCode(cmbbx_WhCode.text)+''''+
         '                  and WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
         '                  and ItemCode='''+Extedt_ItemCode.text+''' '+
        // 更改物料主件中产总库存
         ' update Item '+
         '  set '+
         tmp_Itemfields+'='+tmp_Itemfields+'+'+Extedt_InvQty.text+
         ' where ItemCode='''+Extedt_ItemCode.text+''' '+
         //月末收发存汇总表有记录，则更改当前库存数量，当前库存金额，当前库单价，没有就增加
         ' if not exists (select ItemCode '+
                           ' from InvMonthSum '+
                           ' where InvMonth='''+jiezhang_Month+''''+
                           '   and whCode='''+getCode(cmbbx_WhCode.text)+''''+
                           '   and ItemCode='''+Extedt_ItemCode.text+''') '+
         '    insert InvMonthSum '+
         '        (InvMonth,'+
                  'whCode,'+
                  'ItemCode,'+
                  'Invblncqty,'+
                  'InvblncAmount,'+
                  'InvblncPrice) '+
            ' Values('''+jiezhang_Month+''','+
                    ''''+getCode(cmbbx_WhCode.text)+''','+
                    ''''+Extedt_ItemCode.text+''','+
                    Extedt_InvQty.text+','+
                    Extedt_InvAmount.text+','+
                    lbl_Price.Caption+')'+
          ' else '+
         '     update InvMonthSum '+
                 ' set '+
                 'Invblncqty=Invblncqty+'+Extedt_InvQty.text+','+
                 'InvblncAmount=InvblncAmount+'+Extedt_InvAmount.text+','+
                 'InvblncPrice=case when isnull((Invblncqty+'+Extedt_InvQty.text+'),0)=0 then 0 else (InvblncAmount+'+Extedt_InvAmount.text+')/(Invblncqty+'+Extedt_InvQty.text+') end '+
              ' where InvMonth='''+jiezhang_Month+''''+
              '  and whCode='''+getCode(cmbbx_WhCode.text)+''''+
              '  and ItemCode='''+Extedt_ItemCode.text+''''+

        //更新表WhPMonthSum
         ' if not exists (select ItemCode '+
                           ' from whPMonthSum '+
                           ' where InvMonth='''+jiezhang_Month+''''+
                           '   and whCode='''+getCode(cmbbx_WhCode.text)+''''+
                           '   and WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
                           '   and ItemCode='''+Extedt_ItemCode.text+''') '+
         '    insert whPMonthSum '+
         '        (InvMonth,'+
                  'whCode,'+
                  'WhPositionCode,'+
                  'ItemCode,'+
                  'Invblncqty)'+
            ' Values('''+jiezhang_Month+''','+
                    ''''+getCode(cmbbx_WhCode.text)+''','+
                    ''''+getCode(cmbbx_WhPositionCode.text)+''','+
                    ''''+Extedt_ItemCode.text+''','+
                    Extedt_InvQty.text+')'+
       ' else '+
         '     update whPMonthSum '+
                 ' set '+
                 'Invblncqty=Invblncqty+'+Extedt_InvQty.text+
              ' where InvMonth='''+jiezhang_Month+''''+
              '  and whCode='''+getCode(cmbbx_WhCode.text)+''''+
              '  and WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
              '  and ItemCode='''+Extedt_ItemCode.text+''''+

       //判断当前仓库是否为加权平权价
       ' if exists (select whCode '+
                     ' from Warehouse '+
                     ' where whCode='''+getCode(cmbbx_WhCode.text)+''''+
                     '   and PriceType=0) '+
       ' begin '+
        //移动加权平均价中存在记录，则更改数量，金额，单价，没有则增加
         ' if not exists (select ItemCode '+
                          ' from AveragePrice '+
                          ' where whCode='''+getCode(cmbbx_WhCode.text)+''''+
                          '   and ItemCode='''+Extedt_ItemCode.text+''') '+
         '    insert AveragePrice '+
                 '(whCode,'+
                 ' ItemCode,'+
                 'Apqty,'+
                 'ApAmount,'+
                 'AveragePrice)'+
              ' Values('''+getCode(cmbbx_WhCode.text)+''','+
                      ''''+Extedt_ItemCode.text+''','+
                      Extedt_InvQty.text+','+
                      Extedt_InvAmount.text+','+
                      lbl_Price.Caption+') '+
         '  else '+
              ' update AveragePrice '+
              '   set '+
              '   Apqty=Apqty+'+Extedt_InvQty.text+','+
              '   ApAmount=ApAmount+'+Extedt_InvAmount.text+','+
              '   AveragePrice=case when isnull((Apqty+'+Extedt_InvQty.text+'),0)=0 then AveragePrice else (ApAmount+'+Extedt_InvAmount.text+')/(Apqty+'+Extedt_InvQty.text+') end '+
              ' where whCode='''+getCode(cmbbx_WhCode.text)+''''+
              '   and ItemCode='''+Extedt_ItemCode.text+''''+
       ' end ';
  if (Add) then
  begin
    with AdoQry_Tmp do
    begin
      Close;
      SQL.clear;
      sql.text:=
      //增加记录到InvBALACE
         'Insert InvBalance(InvMonth,'+
                          'whCode,'+
                          'WhPositionCode,'+
                          'ItemCode,'+
                          'InvQty,'+
                          'InvAmount) '+
         ' ValueS ('''+medt_Month.text+''','+
                   ''''+getCode(cmbbx_WhCode.text)+''','+
                   ''''+getCode(cmbbx_WhPositionCode.text)+''','+
                   ''''+ExtEdt_ItemCode.text+''','+
                    ExtEdt_InvQty.text+','+
                   ExtEdt_InvAmount.text+')'+
                   sqltext;
     ExecSQL;
    end;
  end
  else
  begin
    with AdoQry_Tmp do
    begin
      Close;
      SQL.clear;
      sql.text:=
      //更改旧的物料Balance
         ' update InvBalance set '+
             'InvQty=InvQty-('+oldInvQty+'),'+
             'InvAmount=InvAmount-('+oldInvAmount+')'+
             ' where whCode='''+getCode(cmbbx_WhCode.text)+''''+
             '   and WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
             '   and ItemCode='''+oldItemCode+''''+
      //更新新的物料的Balance
         ' if not exists (select ItemCode from InvBalance '+
         '                where whCode='''+getCode(cmbbx_WhCode.text)+''''+
         '                  and WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
         '                  and ItemCode='''+Extedt_ItemCode.text+''') '+
         '   Insert InvBalance(InvMonth,'+
                          'whCode,'+
                          'WhPositionCode,'+
                          'ItemCode,'+
                          'InvQty,'+
                          'InvAmount) '+
         '   ValueS ('''+medt_Month.text+''','+
                    ''''+getCode(cmbbx_WhCode.text)+''','+
                    ''''+getCode(cmbbx_WhPositionCode.text)+''','+
                    ''''+ExtEdt_ItemCode.text+''','+
                    ExtEdt_InvQty.text+','+
                    ExtEdt_InvAmount.text+')'+
         ' else '+
         '    update InvBalance '+
                ' set '+
                ' InvQty=InvQty+'+Extedt_InvQty.text+','+
                ' InvAmount=InvAmount+'+Extedt_InvAmount.text+
              ' where whCode='''+getCode(cmbbx_WhCode.text)+''''+
              '   and WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
              '   and ItemCode='''+Extedt_ItemCode.text+''' '+
        //更改旧物料的当前库存数量
         '    update CurrentInv '+
         '       set '+
         tmp_CurrentInvfields+'='+tmp_CurrentInvfields+'-('+oldInvQty+') '+
         '     where whCode='''+getCode(cmbbx_WhCode.text)+''''+
         '       and WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
         '       and ItemCode='''+Extedt_ItemCode.text+''' '+
         '    update CurrentPeriodInv '+
         '       set '+
         tmp_CurrentInvfields+'='+tmp_CurrentInvfields+'-('+oldInvQty+') '+
         '     where whCode='''+getCode(cmbbx_WhCode.text)+''''+
         '       and WhPositionCode='''+getCode(cmbbx_WhPositionCode.text)+''''+
         '       and ItemCode='''+Extedt_ItemCode.text+''' '+
         //更改旧物料的物料表的总库存数量
         ' update Item '+
         '  set '+
         tmp_Itemfields+'='+tmp_Itemfields+'-('+oldInvQty+')'+
         ' where ItemCode='''+Extedt_ItemCode.text+''' '+
         //更改旧物料的月末结存表的数量，金额，单价
         ' update InvMonthSum '+