bas_vendormerge.pas

一个MRPII系统源代码版本

//功能:完成A、B两供应商的合并。
unit Bas_VendorMerge;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Dialog, Db, AdODB, StdCtrls, ExtEdit;

Type
  TFrm_Bas_VendorMerge = Class(TFrm_Base_Dialog)
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    edt_SRCVendor: TExtEdit;
    edt_DESTVendor: TExtEdit;
    lbl_SRCVendor: TLabel;
    lbl_DESTVendor: TLabel;
    lbl_Status: TLabel;
    procedure FormActivate(Sender: TObject);
    procedure edt_SRCVendorExit(Sender: TObject);
    procedure edt_DESTVendorExit(Sender: TObject);
    procedure btn_okClick(Sender: TObject);
    procedure btn_CancelClick(Sender: TObject);
  private
    procedure controls_all(status:boolean);
    procedure Merge;
    { Private declarations }
  public
  procedure SetDBConnect(AdOConnection:TAdOConnection);Override;
    { Public declarations }
  end;

var
  Frm_Bas_VendorMerge: TFrm_Bas_VendorMerge;

implementation
uses Sys_Global;
{$R *.DFM}

procedure TFrm_Bas_VendorMerge.FormActivate(Sender: TObject);
begin
  inherited;
  edt_SRCVendor.SetFocus;
end;

procedure TFrm_Bas_VendorMerge.SetDBConnect(AdOConnection: TAdOConnection);
begin
  inherited;
  AdoQry_Tmp.Connection:=AdOConnection;
end;

procedure TFrm_Bas_VendorMerge.edt_SRCVendorExit(Sender: TObject);
begin
  inherited;
  VendorCodeCheck(sender);
  with AdoQry_Tmp do
  begin
  Close;
  sql.clear;
  sql.Add('select VendorName from Vendor where VendorCode='+
          ' '''+edt_SRCVendor.text+''' ');
  Prepared;
  open;
  if not eof then
  lbl_SRCVendor.Caption:=fieldbyname('VendorName').asstring;
  end;
end;

procedure TFrm_Bas_VendorMerge.edt_DESTVendorExit(Sender: TObject);
begin
  inherited;
  VendorCodeCheck(edt_DESTVendor);
  with AdoQry_Tmp do
  begin
  Close;
  sql.clear;
  sql.Add('select VendorName from Vendor where VendorCode='+
          ' '''+edt_DESTVendor.text+''' ');
  Prepared;
  open;
  if not eof then
  lbl_DESTVendor.Caption:=fieldbyname('VendorName').asstring;
  end;
end;

procedure TFrm_Bas_VendorMerge.btn_okClick(Sender: TObject);
begin
  inherited;
  if (edt_SRCVendor.text=edt_DESTVendor.text) then
  begin
    DispInfo('两项供应商不能相同!',1);
    edt_SRCVendor.setfocus;
    abort;
  end
  else
  begin
    if DispInfo('供应商'+chr(13)+chr(10)+'('+edt_SRCVendor.text+lbl_SRCVendor.Caption+')'+chr(13)+chr(10)+chr(13)+chr(10)+
    '在系统中的所有数据将被'+chr(13)+chr(10)+chr(13)+chr(10)+'供应商'+chr(13)+chr(10)+'('+edt_DestVendor.text+
    lbl_DESTVendor.Caption+')'+chr(13)+chr(10)+'替换，请确认！',2)='n' then
    abort
    else
    if DispInfo('供应商'+chr(13)+chr(10)+'('+edt_SRCVendor.text+lbl_SRCVendor.Caption+')'+chr(13)+chr(10)+chr(13)+chr(10)+
    '在系统中的所有数据将被'+chr(13)+chr(10)+chr(13)+chr(10)+'供应商'+chr(13)+chr(10)+'('+edt_DestVendor.text+
    lbl_DESTVendor.Caption+')'+chr(13)+chr(10)+'替换，本操作不可逆转，请再次确认！',2)='n' then
    abort;
    lbl_Status.Caption:='正在进行合并，请稍候......' ;
    lbl_Status.Refresh;
    controls_all(False);
    DBConnect.beginTrans;
    try
    Merge;
    except
    DbConnect.RollBackTrans;
    DispInfo('供应商合并不成功,请稍候再试!',1);
    lbl_Status.Caption:='供应商合并不成功,请稍候再试!';
    Abort;
    end;
    DbConnect.CommitTrans;
    lbl_Status.Caption:='供应商合并成功!';
    controls_all(True);
    edt_SRCVendor.text:='';
    edt_DESTVendor.Text:='';
    edt_SRCVendor.SetFocus;
  end;
end;

procedure TFrm_Bas_VendorMerge.controls_all(status: boolean);
begin
  if status then
  begin
    edt_SRCVendor.Enabled:=True;
    edt_SRCVendor.Color:=clwindow;
    edt_DESTVendor.Enabled:=True;
    edt_DESTVendor.Color:=clwindow;
    btn_ok.Enabled:=True;
    btn_Cancel.Enabled:=True;
  end
  else
  begin
    edt_SRCVendor.Enabled:=False;
    edt_SRCVendor.Color:=clmenu;
    edt_DESTVendor.Enabled:=False;
    edt_DESTVendor.Color:=clmenu;
    btn_ok.Enabled:=False;
    btn_Cancel.Enabled:=False;
  end;
end;

procedure TFrm_Bas_VendorMerge.Merge;
var
  Src,Dest,sql_txt:string;
begin
  src:=edt_SRCVendor.Text;
  Dest:=edt_DESTVendor.text;
  with AdoQry_Tmp do
  begin
  //VendorItem
    sql_txt:='delete from VendorItem '+
             'where VendorCode='+
             format('''%s''',[src])+
             ' and '+
             'ItemCode in '+
             '(select ItemCode '+
             ' from VendorItem '+
             'where VendorCode='+
             format('''%s''',[Dest])+')';
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;

    sql_txt:='update VendorItem '+
             'set VendorCode='+
             format('''%s''',[Dest])+
             ' where VendorCode='+
             format('''%s''',[src]);
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //ss
    sql_txt:='update ss '+
             'set DeptVendorCode='+
               format('''%s''',[Dest])+
             ' where DeptVendorCode='+
               format('''%s''',[src])+
             ' and '+
             ' PmType in (1,2)';
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //InformalSS
    sql_txt:='update InformalSS '+
             'set DeptVendorCode='+
               format('''%s''',[Dest])+
             ' where DeptVendorCode='+
               format('''%s''',[src])+
             ' and '+
             ' PmType in (1,2)';
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
  //VendorItemAssign
    sql_txt:='update VendorItemAssign '+
             'set AssignRate='+
             'convert(decimal(15,2),VendorItemAssign.AssignRate+aaa.AssignRate) '+
             'from VendorItemAssign,(select ItemCode,AssignRate from VendorItemAssign where VendorCode='+
             format('''%s''',[src])+
             'and ItemCode in '+
             '(select ItemCode from VendorItemAssign where VendorCode='+
             format('''%s''',[Dest])+')) aaa '+
             'where VendorItemAssign.ItemCode in '+
             '(select ItemCode from VendorItemAssign where VendorCode='+
             format('''%s''',[src])+
             'and ItemCode in (select ItemCode from VendorItemAssign where VendorCode='+
             format('''%s''',[Dest])+')) and '+
             'VendorCode='+
             format('''%s''',[Dest]);
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;

    sql_txt:='delete from VendorItemAssign '+
             'where VendorCode='+
             format('''%s''',[src])+' and '+
             'ItemCode in '+
             '(select ItemCode from VendorItemAssign where VendorCode='+
             format('''%s''',[src])+
             'and ItemCode in '+
             '(select ItemCode from VendorItemAssign where VendorCode='+
             format('''%s''',[Dest])+'))';
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;

    sql_txt:='update VendorItemAssign '+
             'set VendorCode='+
             format('''%s''',[Dest])+
             'where VendorCode='+
             format('''%s''',[src])+' and '+
             'ItemCode not in '+
             '(select ItemCode from VendorItemAssign where VendorCode='+
             format('''%s''',[src])+
             ' and ItemCode in '+
             '(select ItemCode from VendorItemAssign where VendorCode='+
             format('''%s''',[Dest])+'))';
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //PO
    sql_txt:='update po '+
             'set VendorCode='+
             format('''%s''',[Dest])+
             ' where VendorCode='+
             format('''%s''',[Src]);
    Close;
    sql.clear;
    sql.Add(sql_txt);
    Prepared;
    execsql;
    //pc