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,descr  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.