怎樣在自己的進銷存裡匯入速達、管家婆和用友的資料 (轉)
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 怎樣在Oracle資料庫中高速匯出/匯入Oracle資料庫
- onethink裡自己匯入新的資料表，無法進行運算元據表怎麼辦？
- 使用Dbeaver 進行資料的匯入和匯出
- MySQL資料庫結構和資料的匯出和匯入 (轉)MySql資料庫
- 生鮮行業怎樣進行妥善的進銷存管理?行業
- 利用Java進行MySql資料庫的匯入和匯出JavaMySql資料庫
- 匯入和匯出AWR的資料
- BCP 資料的匯入和匯出
- NDS的資料匯入和匯出
- 怎樣在IE工具欄上加入自己的工具 (轉)
- 怎樣快速搜尋自己所需的資料？
- 極速匯入elasticsearch測試資料Elasticsearch
- SQL資料庫的匯入和匯出SQL資料庫
- Oracle資料泵的匯入和匯出Oracle
- 怎樣在資料庫中儲存貨幣資料庫
- 在不同字符集的資料庫之間匯入資料的方法(轉)資料庫
- Oracle資料庫的匯入和匯出命令Oracle資料庫
- SQL SERVER 和EXCEL的資料匯入匯出SQLServerExcel
- 速達3000 自動匯入工具
- 使用 csv 匯入的方式在 SAP S/4HANA 裡建立 employee 資料
- Net.Core匯入EXCel檔案裡的資料Excel
- 大資料學習：怎樣進行大資料的入門級學習？大資料
- ASP.NET EXCEL資料的匯出和匯入ASP.NETExcel
- AWR資料的匯出和匯入全過程
- postgresql 資料匯入和匯出SQL
- MySQL入門--匯出和匯入資料MySql
- 這樣的環境資料怎麼儲存？
- 用友裡面的形態轉換單是怎麼回事？
- 在oracle中，匯出某使用者的資料和匯入的具體步驟Oracle
- 使用xml檔案,做資料的匯入,匯出 (轉)XML
- 在SQL Server資料庫中匯入匯出資料SQLServer資料庫
- 資料庫的匯入匯出資料庫
- 資料泵的匯入匯出
- 資料泵匯入-在物件已存在的情況下執行匯入物件
- Elasticsearch Lucene是怎樣資料寫入的Elasticsearch
- （轉）excel和sql server的匯入匯出ExcelSQLServer
- 【mysql】資料庫匯出和匯入MySql資料庫
- 進銷存系統資料庫設計資料庫