inv_compareinvqty.pas

一个MRPII系统源代码版本

unit Inv_CompareInvQty;

Interface

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

Type
  TFrm_Inv_CompareInvQty = Class(TFrm_Base_Qry)
    Label1: TLabel;
    DBText1: TDBText;
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainItemCode2: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainnewInvQty: TFloatField;
    AdoQry_MainInvQty: TFloatField;
    AdoQry_Mainold_New: TFloatField;
    Label2: TLabel;
    edt_Count: TEdit;
    chbx_CheckInv: TCheckBox;
    procedure FormShow(Sender: TObject);
    procedure AdoQry_MainCalcFields(DataSet: TDataSet);
    procedure AdoQueryAfterOpen(DataSet: TDataSet);
    procedure Act_FilterExecute(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure chbx_CheckInvClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
  end;

var
  Frm_Inv_CompareInvQty: TFrm_Inv_CompareInvQty;

implementation
 uses Sys_SortOrder,Sys_Condition;
{$R *.DFM}

procedure TFrm_Inv_CompareInvQty.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var
  i:integer;
begin
  inherited;
  Frm_Sys_SortOrder:=TFrm_Sys_SortOrder.Create(self);
  OrderByFields:='ItemCode,ItemCode2,';
  lbl_Order.Caption :='新物料标识，旧物料代码';

  with AdoQry_tmp do
  begin
    Close;
    sql.Text:='create table #Item_old (ItemCode varchAr(100) null,'+
                                     ' ItemCode2 varchAr(16) null,'+
                                     ' UomName   varchAr(4)  null,'+
                                     ' newInvQty float       null,'+
                                     ' InvQty    float       null)';
    try
    execsql;
    except
    Close;
    sql.text:='drop table #Item_old '+
              'create table #Item_old (ItemCode varchAr(100) null,'+
                                     ' ItemCode2 varchAr(16) null,'+
                                     ' UomName   varchAr(4)  null,'+
                                     ' newInvQty float       null,'+
                                     ' InvQty    float       null)';
    try
    execsql;
    except
    end;                                     
    end;
  end;
//  PriceFields:='ApAmount,';
  SelectFromSQL:='insert #Item_old (ItemCode,ItemCode2,UomName,newInvQty,InvQty) '+
       'select  Item.ItemCode+'''+' '+'''+Item.ItemName ItemCode,Item.ItemCode2,Uom.UomName,Item.newInvQty,'+
       'oldSysInv.InvQty'+
      ' from'+
      ' (select ItemCode,ItemCode2,ItemName,UomCode,'+
      ' (CurrentonhandInv+CurrentfreezeInv+CurrentwasterInv+CurrentonShipInv) newInvQty'+
      '       from Item) Item'+
      ' left join'+
      '   (select ItemCode,sum(InvQty) InvQty from oldSysInv where ItemCode<>'''+''+''' group by ItemCode) '+
      '    oldSysInv'+
      ' on Item.ItemCode=oldSysInv.ItemCode'+
      ' left join Uom on Item.UomCode=Uom.UomCode ';
  with AdoQry_tmp do
  begin
    Close;
    sql.text:=SelectFromSQL;
    execsql;
  end;
  SelectFromSQL:='insert #Item_old (ItemCode2,InvQty) '+
      ' select ItemCode2,sum(InvQty) InvQty from oldSysInv where ItemCode='''+''+''' group by ItemCode2 '+
      ' update #Item_old set newInvQty=0 where newInvQty is null '+
      ' update #Item_old set InvQty=0 where InvQty is null '+
      ' delete #Item_old where newInvQty=InvQty';
  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.Add(SelectFromSQL);
    execsql;
  end;
  selectfromsql:='select * from #Item_old ';
  getdata;
end;

procedure TFrm_Inv_CompareInvQty.FormShow(Sender: TObject);
begin
  inherited;
  TlBtn_Look.Enabled :=False;
  TlBtn_Look.Visible :=False;

end;

procedure TFrm_Inv_CompareInvQty.AdoQry_MainCalcFields(DataSet: TDataSet);
begin
  inherited;
  if dataset.fieldbyname('InvQty').asstring='' then
     dataset.fieldbyname('old_New').asfloat:=0-dataset.fieldbyname('newInvQty').asfloat
  else
     dataset.fieldbyname('old_New').asfloat:=dataset.fieldbyname('InvQty').asfloat-dataset.fieldbyname('newInvQty').asfloat;
end;   //

procedure TFrm_Inv_CompareInvQty.AdoQueryAfterOpen(DataSet: TDataSet);
begin
  inherited;
  edt_Count.Text :=inttostr(AdoQry_Main.RecordCount);
 
end;

procedure TFrm_Inv_CompareInvQty.Act_FilterExecute(Sender: TObject);
var
 TableName:string;
begin
  TableName:= '#'+self.Name   ;
  if Frm_Sys_Condition=nil then
    Frm_Sys_Condition:=TFrm_Sys_Condition.Create(Application);
  if Frm_Sys_Condition is TFrm_Sys_Condition then
    TFrm_Sys_Condition(Frm_Sys_Condition).SetSrcGrid(DBGridEh,tableName)
  else
  begin
    Frm_Sys_Condition.SetUserParam(Param1,Param2,Param3,Param4,Param5,Param6);
    Frm_Sys_Condition.SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
  end;
  Frm_Sys_Condition.SetDBConnect(DBConnect);
  Frm_Sys_Condition.FatherForm:=Self;
  if Frm_Sys_Condition.ShowModal=mrOk then
  begin
    Condition:=Frm_Sys_Condition.Condition;
    Lbl_Condition.Caption:=Frm_Sys_Condition.ConditionHint;
    GetData;
  end;

end;

procedure TFrm_Inv_CompareInvQty.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  with AdoQry_tmp do
  begin
    Close;
    sql.Text :='drop table #Item_old';
    try
    execsql;
    except
    end;
  end;
end;

procedure TFrm_Inv_CompareInvQty.chbx_CheckInvClick(Sender: TObject);
var tMpstr:string;
begin
  inherited;
  Screen.Cursor:=crHourGlass;  
  if chbx_CheckInv.Checked then
    tMpstr:=' (CurrentonCheckInv+CurrentonhandInv+CurrentfreezeInv+CurrentwasterInv+CurrentonShipInv) newInvQty'
  else
    tMpstr:=' (CurrentonhandInv+CurrentfreezeInv+CurrentwasterInv+CurrentonShipInv) newInvQty';
  AdoQry_Main.DisableControls ;
  with AdoQry_tmp do
  begin
    Close;
    sql.Text:='delete from #Item_old  insert #Item_old (ItemCode,ItemCode2,UomName,newInvQty,InvQty) '+
     'select  Item.ItemCode+'''+' '+'''+Item.ItemName ItemCode,Item.ItemCode2,Uom.UomName,Item.newInvQty,'+
     'oldSysInv.InvQty'+
    ' from'+
    ' (select ItemCode,ItemCode2,ItemName,UomCode,'+tMpstr+
    '       from Item) Item'+
    ' left join'+
    '   (select ItemCode,sum(InvQty) InvQty from oldSysInv where ItemCode<>'''+''+''' group by ItemCode) '+
    '    oldSysInv'+
    ' on Item.ItemCode=oldSysInv.ItemCode'+
    ' left join Uom on Item.UomCode=Uom.UomCode '+
    ' insert #Item_old (ItemCode2,InvQty) '+
    ' select ItemCode2,sum(InvQty) InvQty from oldSysInv where ItemCode='''+''+''' group by ItemCode2 '+
    ' update #Item_old set newInvQty=0 where newInvQty is null '+
    ' update #Item_old set InvQty=0 where InvQty is null '+
    ' delete #Item_old where newInvQty=InvQty';
    execsql;
    selectfromsql:='select * from #Item_old ';
    OrderByFields:='ItemCode,ItemCode2,';
    getdata;
    AdoQry_Main.EnableControls ;
    Screen.Cursor:=crArrow;    
  end;
end;

end.