bas_invbalance.pas

一个MRPII系统源代码版本

unit Bas_InvBalance;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Outer, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
  StdCtrls, ExtCtrls, ComCtrls, ToolWin, DBCtrls, jpeg;

Type
  TFrm_Bas_InvBalance = Class(TFrm_Base_Outer)
    AdoQry_MainInvMonth: TStringField;
    AdoQry_MainwhCode: TStringField;
    AdoQry_MainWhPositionCode: TStringField;
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainItemCodeName: TStringField;
    AdoQry_MainInvQty: TFloatField;
    AdoQry_MainInvAmount: TBCDField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainPrice: TFloatField;
    Label3: TLabel;
    DBText1: TDBText;
    AdoQry_tmp1: TAdoQuery;
    AdoQry_MainwhName: TStringField;
    AdoQry_MainWhPositionName: TStringField;
    AdoQry_Mainwh_EmployeeCode: TStringField;
    procedure AdoQry_MainCalcFields(DataSet: TDataSet);
    procedure Act_FilterExecute(Sender: TObject);
    procedure Act_DeleteExecute(Sender: TObject);
  private
    { Private declarations }
  public
    function  SetDeleteSql:String;Override;
    procedure initform(AdOConnection:TAdOConnection;ReadOnly:boolean);Override;
    { Public declarations }
  end;

var
  Frm_Bas_InvBalance: TFrm_Bas_InvBalance;

implementation

uses Bas_InvBalance_D,Sys_Global;
{$R *.DFM}


procedure TFrm_Bas_InvBalance.initform(AdOConnection:TAdOConnection;ReadOnly:boolean);
begin
  inherited;
  AmountFields:='InvAmount,';
  PriceFields:='Price,';
  SelectFromSQL:=
    ' select InvBalance.InvMonth,'+
          '  InvBalance.whCode,'+
          '  Warehouse.whName,'+
          '  InvBalance.WhPositionCode,'+
          '  WhPosition.WhPositionName,'+
          '  InvBalance.ItemCode,'+
          '  Item.ItemName,'+
          '  InvBalance.ItemCode+'+''' '''+'+Item.ItemName as ItemCodeName, '+
          '  InvBalance.InvQty,'+
          '  InvBalance.InvAmount,'+
          '  Uom.UomName, '+
          '  Item.wh_EmployeeCode'+
    ' from InvBalance '+
    '  left join Item on InvBalance.ItemCode=Item.ItemCode '+
    '  left join Uom on Item.UomCode=Uom.UomCode '+
    '  left join Warehouse on  InvBalance.whCode=Warehouse.whCode '+
    '  left join WhPosition on  InvBalance.whCode=WhPosition.whCode '+
                         ' and  InvBalance.WhPositionCode=WhPosition.WhPositionCode '+
    '  left join whAccessCtrl on InvBalance.whCode=whAccessCtrl.whCode';
  OrderByFields:='ItemCode';
  ConditionUserDefine:=' whAccessCtrl.EmployeeCode='+Quotedstr(UserCode);
  getdata;
  lbl_Order.Caption:='物料标识';
  Frm_Sys_Detail:=TFrm_Bas_InvBalance_D.Create(Application);

end;


function TFrm_Bas_InvBalance.SetDeleteSql: String;
var
  lastMonth,oldInvQty,oldInvAmount,oldItemCode,oldwhCode,oldWhPositionCode,oldInvMonth,tmp_Itemfields,tmp_CurrentInvfields:string;
begin
  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(oldInvMonth)+'.01'),-1)),1,7);
    Close;
  end;
  oldInvMonth:=AdoQry_Main.fieldbyname('InvMonth').asstring;
  oldInvQty:=AdoQry_Main.fieldbyname('InvQty').asstring;
  oldInvAmount:=AdoQry_Main.fieldbyname('InvAmount').asstring;
  oldItemCode:=AdoQry_Main.fieldbyname('ItemCode').asstring;
  oldwhCode:=AdoQry_Main.fieldbyname('whCode').asstring;
  oldWhPositionCode:=AdoQry_Main.fieldbyname('WhPositionCode').asstring;
  lastMonth:=copy(datetostr(incMonth(strtodate(Trim(oldInvMonth)+'.01'),-1)),1,7);

  AdoQry_tmp1.Connection:=self.DBConnect;
  tmp_Itemfields:=getInvfield(AdoQry_tmp1,oldwhCode,oldWhPositionCode,'Item');
  tmp_CurrentInvfields:=getInvfield(AdoQry_tmp1,oldwhCode,oldWhPositionCode,'CurrentInv');
  Result:=
      //更改旧物料的当前库存数量

         '    update CurrentInv '+
         '       set '+
         tmp_CurrentInvfields+'='+tmp_CurrentInvfields+'-('+oldInvQty+') '+
         '     where whCode='''+oldwhCode+''''+
         '       and WhPositionCode='''+oldWhPositionCode+''''+
         '       and ItemCode='''+oldItemCode+''' '+
         '    update CurrentperiodInv '+
         '       set '+
         tmp_CurrentInvfields+'='+tmp_CurrentInvfields+'-('+oldInvQty+') '+
         '     where whCode='''+oldwhCode+''''+
         '       and WhPositionCode='''+oldWhPositionCode+''''+
         '       and ItemCode='''+oldItemCode+''' '+
    //更改旧物料的当前库存数量
         '    update CurrentInv '+
         '       set '+
         tmp_CurrentInvfields+'='+tmp_CurrentInvfields+'-('+oldInvQty+') '+
         '     where whCode='''+oldwhCode+''''+
         '       and WhPositionCode='''+oldWhPositionCode+''''+
         '       and ItemCode='''+oldItemCode+''' '+
         '    update CurrentperiodInv '+
         '       set '+
         tmp_CurrentInvfields+'='+tmp_CurrentInvfields+'-('+oldInvQty+') '+
         '     where whCode='''+oldwhCode+''''+
         '       and WhPositionCode='''+oldWhPositionCode+''''+
         '       and ItemCode='''+oldItemCode+''' '+
         //更改旧物料的物料表的总库存数量
         ' update Item '+
         '  set '+
         tmp_Itemfields+'='+tmp_Itemfields+'-('+oldInvQty+')'+
         ' where ItemCode='''+oldItemCode+''' '+
         //更改旧物料的月末结存表的数量，金额，单价
         ' update InvMonthSum '+
         '   set '+
             'Invblncqty=Invblncqty-('+oldInvQty+'),'+
             'InvblncAmount=InvblncAmount-('+oldInvAmount+'),'+
             'InvblncPrice=case when isnull((Invblncqty-('+oldInvQty+')),0)=0 then 0 else (InvblncAmount-('+oldInvAmount+'))/(Invblncqty-('+oldInvQty+')) end '+
              ' where InvMonth='''+lastMonth+''''+
              '  and whCode='''+oldwhCode+''''+
              '  and ItemCode='''+oldItemCode+''''+
         //更改whPMonthSum
         ' update whPMonthSum '+
         '   set '+
             'Invblncqty=Invblncqty-('+oldInvQty+') '+
              ' where InvMonth='''+lastMonth+''''+
              '  and whCode='''+oldwhCode+''''+
              '  and WhPositionCode='''+oldWhPositionCode+''''+
              '  and ItemCode='''+oldItemCode+''''+
        //更改旧物料的加权平均价的数量，金额
       ' if exists (select whCode '+
                     ' from Warehouse '+
                     ' where whCode='''+oldwhCode+''''+
                     '   and PriceType=0) '+
       ' begin '+
         ' update AveragePrice '+
              '   set '+
              '   Apqty=Apqty-('+oldInvQty+'),'+
              '   ApAmount=ApAmount-('+oldInvAmount+'),'+
              '   AveragePrice=case when isnull((Apqty-('+oldInvQty+')),0)=0 then AveragePrice else (ApAmount-('+oldInvAmount+'))/(Apqty-('+oldInvQty+')) end '+
              ' where whCode='''+oldwhCode+''''+
              '   and ItemCode='''+oldItemCode+''''+
         ' delete from InvBalance '+
         '   where InvMonth='''+oldInvMonth+''''+
         '     and whCode='''+oldwhCode+''''+
         '     and WhPositionCode='''+oldWhPositionCode+''''+
         '     and ItemCode='''+oldItemCode+''''+
       ' end ';
end;

procedure TFrm_Bas_InvBalance.AdoQry_MainCalcFields(DataSet: TDataSet);
begin
  inherited;
  if AdoQry_Main.fieldbyname('InvQty').asfloat<>0.0 then
     AdoQry_Main.fieldbyname('Price').asfloat:=AdoQry_Main.fieldbyname('InvAmount').asfloat/AdoQry_Main.fieldbyname('InvQty').asfloat;
end;

procedure TFrm_Bas_InvBalance.Act_FilterExecute(Sender: TObject);
begin
  inherited;
{  lbl_WhCode.Caption:=TFrm_Bas_InvBalance_C(Frm_Sys_Condition).cmbbx_WhCode.text;
  lbl_WhPosition.Caption:=TFrm_Bas_InvBalance_C(Frm_Sys_Condition).cmbbx_WhPositionCode.text;
  lbl_Month.Caption:=TFrm_Bas_InvBalance_C(Frm_Sys_Condition).medt_Month.text;
  if TFrm_Bas_InvBalance_C(Frm_Sys_Condition).onlyQuery=True then
    begin
      act_New.Enabled :=False;
      act_Modify.Enabled :=False;
      act_Delete.enabled:=False;
    end
  else
    begin
      act_New.Enabled :=True;
      act_Modify.Enabled :=True;
      act_Delete.enabled:=True;
    end;}
end;

procedure TFrm_Bas_InvBalance.Act_DeleteExecute(Sender: TObject);
var
  onlyQuery:boolean;
  oldwhCode,oldWhPositionCode:string;
begin
  oldwhCode:=AdoQry_Main.fieldbyname('whCode').asstring;
  oldWhPositionCode:=AdoQry_Main.fieldbyname('WhPositionCode').asstring;
  onlyQuery:=False;

  with AdoQry_tmp do
  begin
    Close;
    sql.Text :='select Count(*) cc from InvInBill i1 join InvInBillline i2 on i1.InvBillid=i2.InvBillid where i1.whCode='''+AdoQry_Main.fieldbyname('whCode').asstring+''' and i2.ItemCode='''+AdoQry_Main.fieldbyname('ItemCode').asstring+'''';
    open;
    if fieldbyname('cc').asinteger>0 then
    begin
      DispInfo('该物料在此仓库已发生出入库,不允许进行期初维护',3);
      exit;
    end;
    Close;
    sql.Text :='select Count(*) cc from InvOutBill i1 join InvOutBillline i2 on i1.InvBillid=i2.InvBillid where i1.whCode='''+AdoQry_Main.fieldbyname('whCode').asstring+''' and i2.ItemCode='''+AdoQry_Main.fieldbyname('ItemCode').asstring+'''';
    open;
    if fieldbyname('cc').asinteger>0 then
    begin
      DispInfo('该物料在此仓库已发生出入库,不允许进行期初维护',3);
      exit;
    end;
    Close;
    sql.Text :='select Count(*) cc from oncheckBill i1 join oncheckBillline i2 on i1.oncheckBillid=i2.oncheckBillid where i1.whCode='''+AdoQry_Main.fieldbyname('whCode').asstring+''' and i2.ItemCode='''+AdoQry_Main.fieldbyname('ItemCode').asstring+'''';
    open;
    if fieldbyname('cc').asinteger>0 then
    begin
      DispInfo('该物料在此仓库已发生出入库,不允许进行期初维护',3);
      exit;
    end;

  end;
  inherited; 
end;

end.