怎樣在自己的進銷存裡匯入速達、管家婆和用友的資料 (轉)

gugu99發表於2007-08-17
怎樣在自己的進銷存裡匯入速達、管家婆和用友的資料 (轉)[@more@]

unit UpdateSD3000;

interface

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

type
  TfrmUpdateSD3000 = class(TfrmEditForm)
  PageControl: TSeSkinPageControl;
  Ta3000: 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;
  ADOCo: TADOConnection;
  SQLDataSet: TADODataSet;
  cbxSQLDB: TdxPickEdit;
  procedure edtSDDataButtonClick(Sender: T; 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 ConectGr(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 InputAllUft80Data; //全部用友資料
  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.Key := HKEY_LOCAL_MACHINE;
  if Reg.OpenKey('SoftwareSuperDataSD3000 et', 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('SoftwareSuperDataSD3000 et', 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.SQL.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,E,Learning,PhoneCall,Phone,PhoneMove,Dept,Sort';
  sField1 := 'EMPCODE,NAME,DUTY,POSTCODE,FOREHER,POSTDATE,PAY,IDCARD,ADDRESS,'
  +
  'SEXNAME,BIRTHDAY,,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_PROV);
  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.

 


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/10748419/viewspace-963472/,如需轉載,請註明出處,否則將追究法律責任。

相關文章