sys_enter_mrpaccount_d.pas

一个MRPII系统源代码版本

unit Sys_Enter_MrpAccount_D;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Buttons,filectrl,adodb;

type
  TFrm_Sys_Enter_MrpAccount_D = class(TForm)
    btn_ok: TButton;
    btn_cancel: TButton;
    lbl_accountid: TLabel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    selectdir: TSpeedButton;
    edt_accountname: TEdit;
    edt_servername: TEdit;
    edt_databasepath: TEdit;
    edt_systemname: TEdit;
    edt_systempassword: TEdit;
    cmb_iscanuse: TComboBox;
    edt_databasename: TEdit;
    cmb_id: TEdit;
    procedure selectdirClick(Sender: TObject);
    procedure btn_okClick(Sender: TObject);
  private
     function  existsaccount(id:string):boolean;

    { Private declarations }
  public
  function  connectserver(servername,systemname,systempassword:string):boolean;
  function  existsdatabase(servername,databasename,systemname,systempassword:string):boolean;
  function createdatabase(databasename,databasepath,servername,systemname,systempassword:string):boolean;
  function restoredatabase(databasename,servername,systemname,systempassword:string):boolean;
  function insertaccount(id,accountname,servername,databasename,databasepath,systemname,systempassword:string;createdate,modifydate:string;iscanuse:integer):boolean;
  function deleteaccount(id:string):boolean;
  function deletedatabase(databasename,databasepath,servername,systemname,systempassword:string):boolean;
  function updateaccount(oldid,newid:string):boolean;
    procedure allchange(sender:Tobject);
    procedure setallchange;

    { Public declarations }
  end;

var
  Frm_Sys_Enter_MrpAccount_D: TFrm_Sys_Enter_MrpAccount_D;
  id,accountname,servername,databasename,databasepath,systemname,systempassword:string;
  iscanuse:integer;

implementation
uses Sys_Enter_MrpAccount;
{$R *.DFM}

procedure TFrm_Sys_Enter_MrpAccount_D.selectdirClick(Sender: TObject);
var mydir:string;
begin
SelectDirectory(extractfilepath('mmc.exe'), '',mydir);
edt_databasepath.Text:=mydir;
end;


procedure TFrm_Sys_Enter_MrpAccount_D.btn_okClick(Sender: TObject);
begin
 if length(trim(cmb_id.Text))=0 then
    begin
     showmessage('帐套号不能为空！');
     cmb_id.SetFocus;
     exit;
    end;
 if length(trim(edt_accountname.Text))=0 then
    begin
     showmessage('帐套名不能为空！');
     edt_accountname.SetFocus;
     exit;
    end;
 if length(trim(edt_servername.Text))=0 then
    begin
       showmessage('数据库服务器名不能为空！');
       edt_servername.SetFocus;
       exit;
    end;
 if length(trim(edt_databasepath.Text))=0 then
    begin
      showmessage('数据库存放路径不能为空！');
      edt_databasepath.SetFocus;
      exit;
    end ;
 if length(trim(edt_databasename.Text))=0 then
    begin
      showmessage('数据库名不能为空！');
      edt_databasename.SetFocus;
      exit;
    end;
 if length(trim(edt_systemname.Text))=0 then
    begin
      showmessage('超级用户名不能为空！');
      edt_systemname.setfocus;
      exit;
    end;
 id:=cmb_id.text;
 accountname:=edt_accountname.Text;
 servername:=edt_servername.Text;
 databasename:=edt_databasename.text;
 databasepath:=edt_databasepath.Text;
 systemname:=edt_systemname.Text;
 systempassword:=edt_systempassword.Text;
 iscanuse:=cmb_iscanuse.itemindex;
if  self.Caption='帐套新增' then
 begin
if not connectserver(servername,systemname,systempassword) then
   begin
   showmessage('数据库服务器不能连接，请重试！');
   exit;
   end;
if existsaccount(id) then
   begin
   showmessage('已存在帐套号为'+id+'的帐套，请重新输入帐套号！');
   cmb_id.SetFocus;
   exit;
   end;
if existsdatabase(servername,databasename,systemname,systempassword) then
   begin
   showmessage('数据库实体'+databasename+'已经存在，请重新输入数据库名！');
   edt_databasename.SetFocus;
   exit;
   end;
if not createdatabase(databasename,databasepath,servername,systemname,systempassword) then
   begin
   showmessage('创建帐套失败，请重试！');
   exit;
   end;
 end
else
 begin
  if id<> frm_Sys_Enter_MrpAccount.AdoQry_Head.FieldByName('id').asstring then
   if existsaccount(id) then
     begin
     showmessage('已存在帐套号为'+id+'的帐套，请重新输入帐套号！');
     cmb_id.SetFocus;
     exit;
     end;
   if not updateaccount(frm_Sys_Enter_MrpAccount.AdoQry_Head.FieldByName('id').asstring,id) then
     begin
       showmessage('修改帐套时出错！');
       exit;
     end;
  end;
modalresult:=mrok;
end;

procedure TFrm_Sys_Enter_MrpAccount_D.allchange(sender: Tobject);
begin
 btn_ok.Enabled:=true;
end;

procedure TFrm_Sys_Enter_MrpAccount_D.setallchange;
var i:integer;
begin
  for i:=0 to ControlCount-1 do
    begin
     if (controls[i] is Tedit)  then
      Tedit(Controls[i]).onchange:=allchange
     else if controls[i] is Tcombobox then
      Tcombobox(controls[i]).onchange:=allchange;
    end;
end;

function TFrm_Sys_Enter_MrpAccount_D.existsaccount(id: string): boolean;
var adoqry_tm:Tadoquery;
con:widestring;
begin
 result:=true;
 try
  adoqry_tm:=Tadoquery.Create(self);
 con:='Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source='+extractfilepath(application.exename)+'\MrpACT.mdb;Persist Security Info=True';
 with adoqry_tm do
   begin
   connectionstring:=con;
    close;
    sql.Clear;
    sql.Add('select * from account where id='+quotedstr(id));
    prepared;
    try
    open;
    except
    end;
    if recordcount=0 then
     result:=false;
  end;
 finally
   adoqry_tm.Free;
 end;
end;



function TFrm_Sys_Enter_MrpAccount_D.connectserver(servername, systemname,
  systempassword: string): boolean;
var adoconnection:Tadoconnection;
begin
result:=true;
   try
    adoconnection:=Tadoconnection.Create(self);
    with adoconnection do
      begin
        loginprompt:=false;
        connectionstring:='Provider=SQLOLEDB.1;Password='+systempassword+';Persist Security Info=True;User ID='+systemname+';Initial Catalog=master;Data Source='+servername+';Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096';
        try
         connected:=true;
        except
         result:=false;
        end;
     end;
    finally
     adoconnection.free;
    end;
end;


function TFrm_Sys_Enter_MrpAccount_D.createdatabase(databasename,
  databasepath, servername, systemname, systempassword: string):boolean;
var sqlstr,sqlstr1:string;adoconnect:Tadoconnection;adoqry_tmp:Tadoquery;
begin
result:=true;
   sqlstr:='RESTORE FILELISTONLY  '
           +'	FROM mdrcmrmbak   '
           +' RESTORE DATABASE   '+databasename
	   +'   FROM mdrcmrmbak         '
           +' WITH MOVE ''mdrcmrm_data'' TO '+quotedstr(databasepath+'\'+databasename+'_dat.mdf')+','
            +'	MOVE ''mdrcmrm_log'' TO '+quotedstr(databasepath+'\'+databasename+'_log.ldf');
try
adoconnect:=Tadoconnection.Create(self);
adoqry_tmp:=Tadoquery.Create(self);
adoconnect.ConnectionString:='Provider=SQLOLEDB.1;Password='+systempassword+';Persist Security Info=True;User ID='+systemname+';Initial Catalog=master;Data Source='+servername+';Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096';
adoconnect.LoginPrompt:=false;
adoconnect.Connected:=true;
adoqry_tmp.Connection:=adoconnect;
adoconnect.BeginTrans;
with adoqry_tmp do
begin
  close;
  sql.Clear;
  sql.Add(sqlstr);
  showmessage(sqlstr);
  prepared;
  try
  screen.Cursor:=crsqlwait;
  execsql;
  except
   result:=false;
   adoconnect.RollbackTrans;
   exit;
  end;

  adoconnect.CommitTrans;
  adoconnect.Close;
  adoconnect.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source='+extractfilepath(application.exename)+'\MrpACT.mdb;Persist Security Info=True';
  adoconnect.Connected:=true;
  close;
  sql.Clear;
  sql.Add('insert into account values('+quotedstr(id)+','+quotedstr(accountname)+','+quotedstr(servername)+','+quotedstr(databasename)+','+quotedstr(databasepath)+','+quotedstr(systemname)+','+quotedstr(systempassword)+','+quotedstr(datetostr(date))+','+quotedstr(datetostr(date))+','+quotedstr(inttostr(iscanuse))+')');
  prepared;
  try
   execsql;
   showmessage('创建帐套成功！');
  except
   result:=false;
   exit;
  end;
 with  Frm_Sys_Enter_MrpAccount.adoqry_head do
  begin
  append;
  fieldbyname('id').asstring:=id;
  fieldbyname('name').asstring:=accountname;
  fieldbyname('servername').asstring:=servername;
  fieldbyname('databasename').asstring:=databasename;
  fieldbyname('databasepath').asstring:=databasepath;
  fieldbyname('systemname').asstring:=systemname;
  fieldbyname('systempassword').asstring:=systempassword;
  fieldbyname('createdate').asstring:=datetostr(now);
  fieldbyname('modifydate').asstring:=datetostr(now);
  fieldbyname('valid').asstring:=inttostr(iscanuse);
  post;
  requery;
  end;
end;
finally
 screen.Cursor:=crdefault;
 adoconnect.Free;
 adoqry_tmp.Free;
end;
end;

function TFrm_Sys_Enter_MrpAccount_D.deleteaccount(id: string):boolean;
 var adoqry_tmp:Tadoquery;
    con:widestring;
begin
result:=true;
try
  adoqry_tmp:=Tadoquery.Create(self);
with adoqry_tmp do
  begin
con:='Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source='+extractfilepath(application.exename)+'\MrpACT.mdb;Persist Security Info=True';
connectionstring:=con;
  close;
  sql.Clear;
  sql.Add('delete from account where id='+quotedstr(id));
  prepared;
  try
  execsql;
  except
   result:=false;
  end;
  end;
finally
  adoqry_tmp.Free;
end;

end;


function TFrm_Sys_Enter_MrpAccount_D.deletedatabase(databasename,
  databasepath, servername, systemname, systempassword: string):boolean;
var sqlstr:string;adoqry_tmp:Tadoquery;
begin
result:=true;
  sqlstr:='drop database '+databasename;
try
adoqry_tmp:=Tadoquery.Create(self);
with adoqry_tmp do
begin
  connectionstring:='Provider=SQLOLEDB.1;Password='+systempassword+';Persist Security Info=True;User ID='+systemname+';Initial Catalog=master;Data Source='+servername+';Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096';
  close;
  sql.Clear;
  sql.Add(sqlstr);
  prepared;
  try
  screen.Cursor:=crsqlwait;
  execsql;
  showmessage('删除帐套成功！');
  except
  result:=false;
  end;
end;
finally
 screen.Cursor:=crdefault;
 adoqry_tmp.Free;
end;
end;

function TFrm_Sys_Enter_MrpAccount_D.existsdatabase(servername,
  databasename, systemname, systempassword: string): boolean;
var adoqry_tm:Tadoquery;
begin
 result:=true;
 try
  adoqry_tm:=Tadoquery.Create(self);
 with adoqry_tm do
   begin
    connectionstring:='Provider=SQLOLEDB.1;Password='+systempassword+';Persist Security Info=True;User ID='+systemname+';Initial Catalog=master;Data Source='+servername+';Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096';
    close;
    sql.Clear;
    sql.Add('select * from sysdatabases where name='+quotedstr(databasename));
    prepared;
    open;
        if recordcount=0 then
        result:=false;
    end;
  finally
   adoqry_tm.Free;
   end;
end;

function TFrm_Sys_Enter_MrpAccount_D.insertaccount(id, accountname,
  servername, databasename, databasepath, systemname, systempassword,
  createdate, modifydate: string; iscanuse: integer):boolean;
var adoqry_tmp:Tadoquery;
    con:widestring;
begin
result:=true;
try
  adoqry_tmp:=Tadoquery.Create(self);
with adoqry_tmp do
  begin
con:='Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source='+extractfilepath(application.exename)+'\MrpACT.mdb;Persist Security Info=True';
connectionstring:=con;
  close;
  sql.Clear;
  sql.Add('insert into account values('+quotedstr(id)+','+quotedstr(accountname)+','+quotedstr(servername)+','+quotedstr(databasename)+','+quotedstr(databasepath)+','+quotedstr(systemname)+','+quotedstr(systempassword)+','+quotedstr(datetostr(date))+','+quotedstr(datetostr(date))+','+quotedstr(inttostr(iscanuse))+')');
  prepared;
  try
  execsql;
  except
  showmessage('保存帐套信息出错！');
  result:=false;
  end;
  end;
finally
  adoqry_tmp.Free;
end;
end;


function TFrm_Sys_Enter_MrpAccount_D.restoredatabase(databasename,
  servername, systemname, systempassword: string):boolean;
var sqlstr:string;
    adoqry_tmp:Tadoquery;
begin
result:=true;
 sqlstr:='restore database '+databasename
         +#13+' from disk='+quotedstr(extractfilepath(application.exename)+'\act0002.bak')+' with replace';
 try
  adoqry_tmp:=Tadoquery.Create(self);
   with adoqry_tmp do
    begin
      close;
      connectionstring:='Provider=SQLOLEDB.1;Password='+systempassword+';Persist Security Info=True;User ID='+systemname+';Initial Catalog=master;Data Source='+servername+';Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096';
      sql.Clear;
      sql.Add(sqlstr);
      prepared;
      try
       screen.Cursor:=crsqlwait;
       execsql;
       showmessage('帐套创建完毕！');
      except
       showmessage('创建帐套时出错！');
       result:=false;
      end;
    end;
 finally
  screen.Cursor:=crdefault;
  adoqry_tmp.Free;
 end;
end;


function TFrm_Sys_Enter_MrpAccount_D.updateaccount(oldid,newid: string): boolean;
 var adoqry_tmp:Tadoquery;adoconnect:Tadoconnection;
    con:widestring;
begin
result:=true;
try
  adoconnect:=Tadoconnection.Create(self);
  adoqry_tmp:=Tadoquery.Create(self);
adoconnect.LoginPrompt:=false;
adoconnect.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source='+extractfilepath(application.exename)+'\MrpACT.mdb;Persist Security Info=True';
adoconnect.Connected:=true;
adoconnect.BeginTrans;
with adoqry_tmp do
  begin
  connection:=adoconnect;

  close;
  sql.Clear;
  sql.Add('delete from account where id='+quotedstr(oldid));
  prepared;
  try
  execsql;
  except
   adoconnect.RollbackTrans;
   result:=false;
  end;

  close;
  sql.Clear;
  sql.Add('insert into account values('+quotedstr(newid)+','+quotedstr(accountname)+','+quotedstr(servername)+','+quotedstr(databasename)+','+quotedstr(databasepath)+','+quotedstr(systemname)+','+quotedstr(systempassword)+','+quotedstr(datetostr(date))+','+quotedstr(datetostr(date))+','+quotedstr(inttostr(iscanuse))+')');
  prepared;
  try
  execsql;
  showmessage('修改帐套成功！');
  except
  adoconnect.RollbackTrans;
  result:=false;
  end;

  end;
adoconnect.CommitTrans;
finally
  adoconnect.Free;
  adoqry_tmp.Free;
end;

end;


end.