bas_opbalance.pas

一个MRPII系统源代码版本

unit Bas_OpBalance;

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_OpBalance = Class(TFrm_Base_Outer)
    Label4: TLabel;
    AdoQry_MainInvMonth: TStringField;
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainInvQty: TFloatField;
    AdoQry_MainInvAmount: TBCDField;
    AdoQry_MainUomName: TStringField;
    Label3: TLabel;
    DBText1: TDBText;
    AdoQry_tmp1: TAdoQuery;
    AdoQry_MainInvPrice: TFloatField;
    Label1: TLabel;
    DBText2: TDBText;
    AdoQry_MaInvendorName: TStringField;
    AdoQry_MainItemName2: 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_OpBalance: TFrm_Bas_OpBalance;

implementation

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


procedure TFrm_Bas_OpBalance.initform(AdOConnection:TAdOConnection;ReadOnly:boolean);
begin
  inherited;
  PriceFields:='InvPrice,';
  SelectFromSQL:=
    'select OpBalance.VendorCode,Vendor.VendorName,'+
            'OpBalance.ItemCode,Item.ItemName,'+
            'OpBalance.InvMonth,'+
            'Uom.UomName,'+
            'OpBalance.InvQty,'+
            'OpBalance.InvAmount '+
     'from OpBalance '+
      'left join Vendor on OpBalance.VendorCode=Vendor.VendorCode '+
      'left join Item on OpBalance.ItemCode=Item.ItemCode '+
      'left join Uom on Item.UomCode=Uom.UomCode ';
  OrderByFields:='ItemCode';
  lbl_Order.Caption:='物料标识';
  getdata;
  Frm_Sys_Detail:=TFrm_Bas_OpBalance_D.Create(Application);
//  Frm_Sys_Condition:=TFrm_Bas_OpBalance_C.Create(Self);
//  TFrm_Bas_OpBalance_C(Frm_Sys_Condition).InitForm(AdoQry_Main.Connection,UserCode,LoginDate);
//  Act_Filter.Execute;
//  lbl_Month.Caption:=TFrm_Bas_OpBalance_C(Frm_Sys_Condition).medt_Month.text;
{  if TFrm_Bas_OpBalance_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;


function TFrm_Bas_OpBalance.SetDeleteSql: String;
var
  lastMonth,oldInvMonth1,oldInvQty,oldv_Code,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;
  oldInvMonth1:=AdoQry_Main.fieldbyname('InvMonth').asstring;
  oldInvMonth:=copy(datetostr(incMonth(strtodate(Trim(oldInvMonth1)+'.01'),-1)),1,7);
  oldInvQty:=AdoQry_Main.fieldbyname('InvQty').asstring;
  oldInvAmount:=AdoQry_Main.fieldbyname('InvAmount').asstring;
  oldItemCode:=getCode(AdoQry_Main.fieldbyname('ItemCode').asstring);
  oldv_Code:=AdoQry_Main.fieldbyname('VendorCode').asstring;
  oldwhCode:=AdoQry_Main.fieldbyname('UomName').asstring;
  oldWhPositionCode:=AdoQry_Main.fieldbyname('InvPrice').asstring;
  AdoQry_tmp1.Connection:=self.DBConnect;
// tmp_Itemfields:=getInvfield(AdoQry_tmp1,getCode(lbl_WhCode.Caption),getCode(lbl_WhPosition.Caption),'Item');
//  tmp_CurrentInvfields:=getInvfield(AdoQry_tmp1,getCode(lbl_WhCode.Caption),getCode(lbl_WhPosition.Caption),'CurrentInv');
  Result:=
    //更改旧物料的当前库存数量
         '    update opCurrentInv '+
         '       set '+
         'opInv=opInv-'+oldInvQty+
         '     where VendorCode='''+oldv_Code+''''+
         '       and ItemCode='''+oldItemCode+''' '+
         //更改旧物料的物料表的总库存数量
         //更改旧物料的月末结存表的数量，金额，单价
         ' update OpMonthSum '+
         '   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='''+oldInvMonth+''''+
              '  and VendorCode='''+oldv_Code+''''+
              '  and ItemCode='''+oldItemCode+''''+
        //更改旧物料的加权平均价的数量，金额
         ' update opAveragePrice '+
              '   set '+
              '   opApqty=opApqty-'+oldInvQty+','+
              '   opApAmount=opApAmount-'+oldInvAmount+','+
              '   opAveragePrice=case when isnull((opApqty-'+oldInvQty+'),0)=0 then 0 else (opApAmount-'+oldInvAmount+')/(opApqty-'+oldInvQty+') end '+
              ' where VendorCode='''+oldv_Code+''''+
              '   and ItemCode='''+oldItemCode+''''+
         ' delete from OpBalance '+
         '   where InvMonth='''+oldInvMonth1+''''+
         '     and VendorCode='''+oldv_Code+''''+
         '     and ItemCode='''+oldItemCode+'''';
end;

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

procedure TFrm_Bas_OpBalance.Act_FilterExecute(Sender: TObject);
var
  i:integer;
begin
  for i:=0 to dbgrideh.Columns.Count-1 do
  begin
    if dbgrideh.Columns[i].FieldName='VendorName' then
      dbgrideh.Columns[i].Field.Origin:='Vendor'
    else if dbgrideh.Columns[i].FieldName='UomName' then
      dbgrideh.Columns[i].Field.Origin:='Uom'
    else
      dbgrideh.Columns[i].Field.Origin:='OpBalance'
  end;
  inherited;
{  lbl_Month.Caption:=TFrm_Bas_OpBalance_C(Frm_Sys_Condition).medt_Month.text;
  if TFrm_Bas_OpBalance_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_OpBalance.Act_DeleteExecute(Sender: TObject);
begin
    with AdoQry_tmp do
    begin
      Close;
      sql.Text :='select Count(*) cc from InvInBill i1 join InvInBillline i2 on i1.InvBillid=i2.InvBillid where i1.VendorCode='''+AdoQry_Main.fieldbyname('VendorCode').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.VendorCode='''+AdoQry_Main.fieldbyname('VendorCode').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.