怎样在自己的进销存里导入速达、管家婆和用友的数据

核心提示： unit UpdateSD3000;interfaceuses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, EditForm, KsControls, KsLabels, KsSkinLab

 

unit UpdateSD3000;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, EditForm, KsControls, KsLabels, KsSkinLabels, dxEditor,
  dxExEdtr, dxEdLib, dxCntner, KsButtons, KsSkinButtons, KsTabs,
  KsSkinTabs, KsHooks, KsForms, KsSkinForms, KsEdits, KsComboBoxs,
  KsSkinComboBoxs, DB, IBDatabase, Registry, SysPublic, IBCustomDataSet,
  Grids, DBGrids, ADODB;

type
  TfrmUpdateSD3000 = class(TfrmEditForm)
　  PageControl: TSeSkinPageControl;
　  TabSD3000: TKsCustomTabSheet;
　  KsCustomTabSheet2: TKsCustomTabSheet;
　  bbOk: TSeSkinButton;
　  bbNo: TSeSkinButton;
　  OpenDlg: TOpenDialog;
　  edtSDData: TdxButtonEdit;
　  SeSkinLabel1: TSeSkinLabel;
　  SeSkinLabel2: TSeSkinLabel;
　  SeSkinLabel3: TSeSkinLabel;
　  SeSkinLabel4: TSeSkinLabel;
　  SeSkinLabel5: TSeSkinLabel;
　  edtServeName: TdxEdit;
　  SeSkinLabel6: TSeSkinLabel;
　  SeSkinLabel7: TSeSkinLabel;
　  edtLoginName: TdxEdit;
　  SeSkinLabel8: TSeSkinLabel;
　  edtLoginPass: TdxEdit;
　  bbLinkServer: TSeSkinButton;
　  SeSkinLabel9: TSeSkinLabel;
　  SeSkinLabel10: TSeSkinLabel;
　  SeSkinLabel11: TSeSkinLabel;
　  IBDatabase1: TIBDatabase;
　  IBTransaction1: TIBTransaction;
　  IBDataSet1: TIBDataSet;
　  AdoDataSet: TADODataSet;
　  KsCustomTabSheet3: TKsCustomTabSheet;
　  SeSkinLabel15: TSeSkinLabel;
　  edtYYData: TdxButtonEdit;
　  SeSkinLabel12: TSeSkinLabel;
　  SeSkinLabel13: TSeSkinLabel;
　  SeSkinLabel14: TSeSkinLabel;
　  SeSkinLabel16: TSeSkinLabel;
　  SQLADOConnet: TADOConnection;
　  SQLDataSet: TADODataSet;
　  cbxSQLDB: TdxPickEdit;
　  PRocedure edtSDDataButtonClick(Sender: TObject; AbsoluteIndex: Integer);
　  procedure bbNoClick(Sender: TObject);
　  procedure bbOkClick(Sender: TObject);
　  procedure edtYYDataButtonClick(Sender: TObject;
　　  AbsoluteIndex: Integer);
　  procedure bbLinkServerClick(Sender: TObject);
  private
　  { Private declarations }
　  bReturn: Boolean;
　  function GetSD3000Path: string; //得到速达的安装目录
　  function GetUFERPPath: string; //得到用友的安装目录
　  function ConectGrasp(sData: string): Boolean;
　  function ConectSD3000(sDBPath: string): Boolean;
　  function OpenIBDataSet(sDBName: string): Boolean;
　  procedure InputGrasp(sSql1, sSql2, sField1, sField2, sConst: string);
　  procedure InputSD3000(sSql1, sSql2, sField1, sField2, sConst: string);
　  procedure InputAllSD3000Data; //全部速达数据
　  procedure InputAllGraspData; //全部管家婆数据
　  procedure InputAllUfsoft80Data; //全部用友数据
　  procedure MainShow;
　  procedure LoadData;
  public
　  { Public declarations }
  end;

function UpdateSD3000Show: Boolean;
implementation

uses DBData;
{$R *.dfm}

function UpdateSD3000Show: Boolean;
var
  frmUpdateSD3000: TfrmUpdateSD3000;
begin
  frmUpdateSD3000 := TfrmUpdateSD3000.Create(application);
  with frmUpdateSD3000 do
  begin
　  MainShow;
　  Result := bReturn;
　  Free;
  end;
end;

procedure TfrmUpdateSD3000.MainShow;
begin
  LoadData;
  ShowModal;
end;

function TfrmUpdateSD3000.GetSD3000Path: string;
var
  Reg: TRegistry;
begin
  Result := '';
  Reg := TRegistry.Create;
  try
　  Reg.RootKey := HKEY_LOCAL_MACHINE;
　  if Reg.OpenKey('\Software\SuperData\SD3000\net', False) then
　　  Result := Reg.ReadString('RemoteDBDir')
  finally
　  Reg.CloseKey;
　  Reg.Free;
  end;
end;

function TfrmUpdateSD3000.GetUFERPPath: string;
var
  Reg: TRegistry;
begin
  Result := '';
  Reg := TRegistry.Create;
  try
　  Reg.RootKey := HKEY_LOCAL_MACHINE;
　  if Reg.OpenKey('\Software\SuperData\SD3000\net', False) then
　　  Result := Reg.ReadString('RemoteDBDir')
  finally
　  Reg.CloseKey;
　  Reg.Free;
  end;
end;

procedure TfrmUpdateSD3000.LoadData;
begin
  PageControl.TabIndex := 0;
  edtSDData.Text := GetSD3000Path;
  edtYYData.text := GetUFERPPath;
end;

procedure TfrmUpdateSD3000.edtSDDataButtonClick(Sender: TObject;
  AbsoluteIndex: Integer);
var
  sFileName: string;
begin
  inherited;
  OpenDlg.FileName := edtSDData.Text;
  if OpenDlg.Execute then
  begin
　  sFileName := OpenDlg.FileName;
　  edtSDData.Text := sFileName;
  end;
end;

function TfrmUpdateSD3000.ConectSD3000(sDBPath: string): Boolean;
begin
  Result := False;
  if Trim(sDBPath) = '' then
  begin
　  ShowMsg('请输入数据库路径！');
　  Exit;
  end;
  if not FileExists(sDBPath) then
  begin
　  ShowMsg('数据库路径不正确，系统找不到指定的文件！');
　  Exit;
  end;
  IBDatabase1.DefaultTransaction := IBTransaction1;
  IBDatabase1.DatabaseName := sDBPath;
  try
　  IBDatabase1.Open;
  except
  end;
  if IBDatabase1.Connected then
　  Result := True
  else
　  ShowMsg('连接数据库出错，请输入正确的速达数据库文件！');
end;

function TfrmUpdateSD3000.OpenIBDataSet(sDBName: string): Boolean;
begin
  Result := True;
  if IBDataSet1.Active then
　  IBDataSet1.Close;
  IBDataSet1.SelectSQL.Text := sDBName;
  try
　  IBDataSet1.Open;
  except
　  result := false;
  end;
end;

procedure TfrmUpdateSD3000.bbNoClick(Sender: TObject);
begin
  inherited;
  Close;
end;

procedure TfrmUpdateSD3000.bbOkClick(Sender: TObject);
begin
  inherited;
  if PageControl.TabIndex = 0 then
　  InputAllSD3000Data
  else if PageControl.TabIndex = 1 then
　  InputAllUfSoft80Data
  else if PageControl.TabIndex = 2 then
　  InputAllGraspData;
end;

procedure TfrmUpdateSD3000.InputAllUfSoft80Data;
begin
  ShowMsg('请选择正确的用友财务UFERP-M8系列的数据库文件！');
end;

procedure TfrmUpdateSD3000.InputAllSD3000Data;
var
  sSql1, sSql2, sField1, sField2, sConst: string;
begin
  if not ConectSD3000(edtSDData.Text) then
　  Exit;
  Screen.Cursor := crHourglass;
  sSql1 := '';
  sSql2 := '';
  sField1 := '';
  sField2 := '';
  sConst := '';
  //部门
  sSql1 := 'SELECT * FROM DEPARTMENT';
  sSql2 := 'SELECT * FROM BaseInfo';
  sField2 := 'Mode,Name1';
  sField1 := '!CONST,Name';
  sConst := intToStr(BASE_DEPT);
  InputSD3000(sSql1, sSql2, sField1, sField2, sConst);
  //职员类别
  sSql1 := 'SELECT * FROM EMPTYPE';
  sSql2 := 'SELECT * FROM BaseInfo';
  sField2 := 'Mode,Name1';
  sField1 := '!CONST,Name';
  sConst := intToStr(BASE_EMPLOYE_SORT);
  InputSD3000(sSql1, sSql2, sField1, sField2, sConst);
  //职员
  sSql1 := 'SELECT e.*,s.Name as SexName,d.Name as DEPARTMENTName,t.Name as EmpTypeName '
　  +
　  'FROM EMPLOY e,EMPTYPE t,SEX s,DEPARTMENT d ' +
　  'WHERE e.EMPTYPEID=t.EmpTYPEID and e.Sex=s.ID and e.DEPARTMENT=d.ID';
  sSql2 := 'SELECT * FROM Employe';
  sField2 := 'Usercode,Name,Business,PostalCode,Place,DutyDate,Wage,ID_Card,Address,'
　  +
　  'Sex,ComeDate,EMail,Learning,PhoneCall,Phone,PhoneMove,Dept,Sort';
  sField1 := 'EMPCODE,NAME,DUTY,POSTCODE,FOREFATHER,POSTDATE,PAY,IDCARD,ADDRESS,'
　  +
　  'SEXNAME,BIRTHDAY,EMAIL,CULTURE,BP,TELEPHONE,HOMEPHONE,DEPARTMENTNAME,EMPTYPENAME';
  InputSD3000(sSql1, sSql2, sField1, sField2, sConst);
  //商品单位
  sSql1 := 'SELECT * FROM UNIT';
  sSql2 := 'SELECT * FROM BaseInfo';
  sField2 := 'Mode,Name1';
  sField1 := '!CONST,Name';
  sConst := intToStr(BASE_WARE_UNIT);
  InputSD3000(sSql1, sSql2, sField1, sField2, sConst);
  //仓库
  sSql1 := 'SELECT * FROM STORE';
  sSql2 := 'SELECT * FROM Depot';
  sField2 := 'UserCode,Name,Address,Memo';
  sField1 := 'STOREID,NAME,LOCATION,Memo';
  InputSD3000(sSql1, sSql2, sField1, sField2, sConst);
  //商品分类
  sSql1 := 'SELECT * FROM GOODSTYPE';
  sSql2 := 'SELECT * FROM BaseInfo';
  sField2 := 'mode,name1,name2';
  sField1 := '!CONST,Name,description';
  sConst := intToStr(BASE_WARE_SORT);
  InputSD3000(sSql1, sSql2, sField1, sField2, sConst);
  //商品
  sSql1 :=
　  'SELECT g.*,t.name as GTypeName FROM GOODS g,GOODSTYPE t WHERE g.goodstypeid=t.goodstypeid';
  sSql2 := 'SELECT * FROM Ware';
  sField2 := 'UserCode, Name, ShortName,Sort,Unit,Price1,ConstPrice';
  sField1 := 'goodsid,name,name,GTypeName,Unit,sprice,Pprice';
  InputSD3000(sSql1, sSql2, sField1, sField2, sConst);
  //地区
  sSql1 := 'SELECT * FROM AREA';
  sSql2 := 'SELECT * FROM BaseInfo';
  sField2 := 'Mode,Name1';
  sField1 := '!CONST,Name';
  sConst := intToStr(BASE_AREA);
  InputSD3000(sSql1, sSql2, sField1, sField2, sConst);
  //客户
  sSql1 :=
　  'SELECT C.*,A.Name as AreaName FROM CLIENT c LEFT JOIN AREA A on A.AREAID=c.AREAID';
  sSql2 := 'SELECT * FROM Unit';
  sField2 := 'Mode,UserCode,ShortName,Name,AreaName,LinkMan,Phone,PhoneMove,PhoneFax,PostalCode,'
　  +
　  'Address,Memo,Banking,Accounts,WWW,EMail,Receive';
  sField1 := '!CONST,ClientID,ShortName,Name,AreaName,CONTATOR,Phone,MOBILEPHONE,Fax,zip,'
　  +
　  'Address,memo,bank,bankID,URL,EMail,BALANCE';
  sConst := IntToStr(BASE_CLIENT);
  InputSD3000(sSql1, sSql2, sField1, sField2, sConst);
  //供应商
  sSql1 :=
　  'SELECT C.*,A.Name as AreaName FROM VENDOR c LEFT JOIN AREA A on A.AREAID=c.AREAID';
  sSql2 := 'SELECT * FROM Unit';
  sField2 := 'Mode,UserCode,ShortName,Name,AreaName,LinkMan,Phone,PhoneMove,PhoneFax,PostalCode,'
　  +
　  'Address,Memo,Banking,Accounts,WWW,EMail,Payable';
  sField1 := '!CONST,VENDORID,ShortName,Name,AreaName,CONTATOR,Phone,MOBILEPHONE,Fax,zip,'
　  +
　  'Address,memo,bank,bankID,URL,EMail,BALANCE';
  sConst := IntToStr(BASE_PROVIDE);
  InputSD3000(sSql1, sSql2, sField1, sField2, sConst);

  ShowMsg('导入数据成功！');
  Screen.Cursor := crDefault;
end;

procedure TfrmUpdateSD3000.InputAllGraspData;
var
  sSqlData: string;
  sSql1, sSql2, sField1, sField2, sConst: string;
begin
  sSql1 := '';
  sSql2 := '';
  sField1 := '';
  sField2 := '';
  sConst := '';
  if cbxSQLDB.ItemIndex < 0 then
　  Exit;
  sSqlData := cbxSQLDB.Items.Strings[cbxSQLDB.ItemIndex];
  if sSQLData = '' then
  begin
　  ShowMsg('请先连接SQLServer数据库！');
　  Exit;
  end;
  if not ConectGrasp(sSqlData) then
  begin
　  ShowMsg('连接SQLServer数据库出错，请重新输入服务器名、用户名、密码！');
　  Exit;
  end;
  Screen.Cursor := crHourglass;
  //仓库
  sSql1 := 'SELECT * FROM STOCK WHERE TYPEID<>''00000''';
  sSql2 := 'SELECT * FROM Depot';
  sField1 := 'USERCODE,FULLNAME,COMMENT';
  sField2 := 'UserCode,Name,Memo';
  InputGrasp(sSql1, sSql2, sField1, sField2, sConst);
  //商品
  sSql1 := 'SELECT * FROM ptype WHERE TYPEID<>''00000''';
  sSql2 := 'SELECT * FROM Ware';
  sField1 := 'UserCode, FullName, Name,Unit1,preprice2,preprice1';
  sField2 := 'UserCode, Name, ShortName,Unit,Price1,ConstPrice';
  InputGrasp(sSql1, sSql2, sField1, sField2, sConst);
  //部门
  sSql1 := 'SELECT * FROM Department WHERE TYPEID<>''00000''';
  sSql2 := 'SELECT * FROM BaseInfo';
  sField1 := '!CONST,FullName';
  sField2 := 'Mode,Name1';
  sConst := intToStr(BASE_DEPT);
  InputGrasp(sSql1, sSql2, sField1, sField2, sConst);
  //职员
  sSql1 := 'SELECT D.FullName AS DepName, E.* FROM employee E LEFT OUTER JOIN '
　  +
　  'Department D ON E.Department = D.typeid WHERE E.TYPEID<>''00000''';
  sSql2 := 'SELECT * FROM Employe';
  sField1 := 'UserCode,FullName,ADDRESS,TEL,DepName,Comment';
  sField2 := 'Usercode,Name,Address,Phone,Dept,Memo';
  InputGrasp(sSql1, sSql2, sField1, sField2, sConst);
  //地区
  sSql1 := 'SELECT * FROM AreaType WHERE TYPEID<>''00000''';
  sSql2 := 'SELECT * FROM BaseInfo';
  sField1 := '!CONST,FullName';
  sField2 := 'Mode,Name1';
  sConst := intToStr(BASE_AREA);
  InputGrasp(sSql1, sSql2, sField1, sField2, sConst);
  //客户
  sSql1 := 'SELECT A.FullName AS AreaName, B.* FROM btype B LEFT OUTER JOIN AreaType A'
　  +
　  ' ON B.AreaTypeID = A.TypeID WHERE B.TypeID<>''00000''';
  sSql2 := 'SELECT * FROM Unit';
  sField1 :=
　  '!CONST,UserCode,Name,FullName,AreaName,PERSON,TELANDADDRESS,FAX,POSTCODE,AREA,Comment,BANKANDACOUNT,BANKANDACOUNT,ARTotal';
  sField2 :=
　  'Mode,UserCode,ShortName,Name,AreaName,LinkMan,Phone,PhoneFax,PostalCode,Address,Memo,Banking,Accounts,Receive';
  sConst := intToStr(BASE_CLIENT);
  InputGrasp(sSql1, sSql2, sField1, sField2, sConst);
  //供应商
  sSql1 := 'SELECT A.FullName AS AreaName, B.* FROM btype B LEFT OUTER JOIN AreaType A'
　  +
　  ' ON B.AreaTypeID = A.TypeID WHERE B.TypeID<>''00000''';
  sSql2 := 'SELECT * FROM Unit';
  sField1 :=
　  '!CONST,UserCode,Name,FullName,AreaName,PERSON,TELANDADDRESS,FAX,POSTCODE,AREA,Comment,BANKANDACOUNT,BANKANDACOUNT,APTotal';
  sField2 :=
　  'Mode,UserCode,ShortName,Name,AreaName,LinkMan,Phone,PhoneFax,PostalCode,Address,Memo,Banking,Accounts,Payable';
  sConst := intToStr(BASE_PROVIDE);
  InputGrasp(sSql1, sSql2, sField1, sField2, sConst);
  ShowMsg('导入数据成功！');
  Screen.Cursor := crDefault;
end;

procedure TfrmUpdateSD3000.InputGrasp(sSql1, sSql2, sField1, sField2, sConst:
  string);
begin
  if OpenDataSetEx(SQLADOConnet, SQLDataSet, sSql1) and OpenDataSet(AdoDataSet, sSql2) then
　  DataSetInput(SQLDataSet, AdoDataSet, sField1, sField2, sConst);
end;

procedure TfrmUpdateSD3000.InputSD3000(sSql1, sSql2, sField1, sField2, sConst:
  string);
begin
  if OpenIBDataSet(sSql1) and OpenDataSet(AdoDataSet, sSql2) then
　  DataSetInput(IBDataSet1, AdoDataSet, sField1, sField2, sConst);
end;

procedure TfrmUpdateSD3000.edtYYDataButtonClick(Sender: TObject;
  AbsoluteIndex: Integer);
var
  sFileName: string;
begin
  inherited;
  OpenDlg.FileName := edtYYData.Text;
  if OpenDlg.Execute then
  begin
　  sFileName := OpenDlg.FileName;
　  edtYYData.Text := sFileName;
  end;
end;

function TfrmUpdateSD3000.ConectGrasp(sData: string): Boolean;
var
  sServer, sName, sPass: string;
begin
  sServer := edtServeName.Text;
  sName := edtLoginName.Text;
  sPass := edtLoginPass.Text;
  if Trim(sServer) = '' then
　  sServer := 'LocalHost';
  if Trim(sName) = '' then
　  sName := 'sa';
  with SQLADOConnet do
  begin
　  if Connected = True then
　　  Close;
　  ConnectionString := GetSQLConnectionString(sServer, sData, sName, sPass);
　  LoginPrompt := False;
　  Open(sName, sPass);
　  Result := Connected;
  end;
end;

procedure TfrmUpdateSD3000.bbLinkServerClick(Sender: TObject);
var
  sSql, sName, sTmp: string;
begin
  inherited;
  sSql := 'SELECT * FROM sysdatabases WHERE (dbid > 5)';
  sName := 'Name';
  sTmp := '';
  if ConectGrasp('Master') then
  begin
　  if OpenDataSetEx(SQLADOConnet, SQLDataSet, sSql) then
　  begin
　　  TableToStrings2(SQLDataSet, sName, sTmp, sTmp, #13);
　　  cbxSQLDB.Items.Text := sName;
　　  if cbxSQLDB.Items.Count > 0 then
　　　  cbxSQLDB.ItemIndex := 0;
　  end;
  end
  else
　  ShowMsg('连接SQLServer数据库出错，请重新输入服务器名、用户名、密码！');
end;

end.