dbmain.pas

详细说明:毕业论文中关于小型宾馆管理系统的详细设计毕 业论文中关于小型宾馆...一个酒店管理系统VB+Access [学生学籍管理系统(VB+Acess).zip] - !这个是刚刚编的毕业设计,可能

           Result:='';
           Exit;
        end;
     end;

     result:=l_username;
     CloseNewQuery(q);


end;

procedure Tsup.Initialize;
begin

end;

// Login
// =============================================================================
function Tsup.PS_Login(const username,
  password: WideString): WordBool;
begin

  // Load preference settings
  try
    LoadSettings;
  except on E:Exception do begin
           RetErr(-3,'不能读取设置的信息: '+E.Message);
           bConnected:=false;
        end;
  end;

  // Connect DB
  if not connectDB then begin
      bConnected:=false;
  end else bConnected:=True;

   //Init
   if username='' then begin
      result:=false;
      exit;
   end;

   sid:=username;
   sPwd:=password;
   //
   Result:=LoginCheck(sid,sPwd);
   bConnected:=Result;
   PS_Ins_Logs('日志','登陆','系统登陆');

end;

function Tsup.PS_Logout: WordBool;
begin
 PS_Ins_Logs('日志','退出','退出系统');
 // Initialise
 sUsername:='';
 sPwd:='';
 err_code:= 0;
 err_msg:='已断开服务器';

 try
  result:=true;
  bConnected:=false;
 except
   result:=true;
 end;

 //inherited Destroy;
end;
// Get last error message
// =============================================================================
function Tsup.PS_LastError: WideString;
begin
   if err_Msg='' then err_Msg:='正常工作.';
   //errCode :=  err_Code;
   result  :=  err_Msg;

end;



function Tsup.PS_Is_Admin: WordBool;
begin
   Result:=bAdmin;
end;

// Get group
// =============================================================================
function Tsup.PS_Get_Groups(const groupname: WideString): OleVariant;
var q: TPodmsQuery;
    cur_row:integer;
    Rows:OleVariant;

begin
     Result:=Unassigned;
     if not bConnected then Exit;
     // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then Exit;
     rows:=Unassigned;
     // Construct SQL statement
     try
        if groupname='' then begin
           q.SQLAdd('Select * From CSI_SECU_GRP');
        end else begin
           q.SQLAdd('Select * From '+CSI_SECU_GRP+' where C_GRP_NAME='+''''+groupname+'''');

        end;
        // Execute SQL statement
        q.Open;
        If q.RecordCount=0 then begin
           RetErr(ER_SECU_GROUP_NORECORD,'没有记录.');
           CloseNewQuery(q);
           Exit;
        end;
        // Get results
        if not q.EOF then begin

           rows:=VarArrayCreate([0, CSI_SECU_GRP_CLOUMNS-1, 0, 0], varVariant);
        end;
        while not q.EOF do begin
           // Store row
           cur_row:=VarArrayHighBound(rows, 2);
           rows[0, cur_row]:=q['C_GRP_ID'];
           rows[1, cur_row]:=q['C_GRP_PARENT_ID'];
           rows[2, cur_row]:=q['C_GRP_NAME'];
           rows[3, cur_row]:=q['C_GRP_ZG'];
           rows[4, cur_row]:=q['C_GRP_TYPE'];
           rows[5, cur_row]:=q['C_CRT_USER'];
           rows[6, cur_row]:=q['D_CRT_DATE'];

           // Next row
           q.Next;
           if not q.EOF then VarArrayRedim(rows, cur_row + 1);
        end;

     except on E:Exception do begin
           // Exception raised on SQL statement
           RetErr(-3,'查询部门错误.');
           Exit;
        end;
     end;

     // Success
     Result:=Rows;
     // Close query and exit
     CloseNewQuery(q);

end;
// Create unique key
// =============================================================================
function Tsup.PS_NewKey(max_length: Integer): WideString;
begin
    Result:=GetUKey(max_length);
end;

function Tsup.PS_Ins_Groups(const GroupName,
  GroupDes: WideString): Integer;
var q: TPodmsQuery;
    uKey:string;
    Rows_Str,values_str:string;
begin
     Result:=GE_OK;
     if not bConnected then begin
        Result:=RetErr(ER_NOTCONNECT,'没有连接服务器');
        Exit;
     end;
     if (GroupName='')  then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'部门必须输入一个名字');
        Exit;
     end;
     uKey:=GetUKey(32);
     if (CheckExistStrVar(t_connection,'CSI_SECU_GRP','C_GRP_NAME',GroupName)) then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'这个部门已存在.');
        Exit;
     end;
     if (CheckExistStrVar(t_connection,'CSI_SECU_GRP','C_GRP_ID',uKey)) then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'这个部门已存在');
        Exit;
     end;

     // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then begin
        // Error creating query object
        Result:=RetErr(ER_QUERY_ERROR,'查询失败.');
        Exit;
     end;
     // Construct SQL statement
     try

         // Insert statement
            q.SQLClear;
            q.SQLAdd('Insert into '+CSI_SECU_GRP);

            // Controlled columns
            rows_str:='(C_GRP_ID,C_GRP_PARENT_ID,C_GRP_NAME,C_GRP_ZG,C_GRP_TYPE,C_CRT_USER,D_CRT_DATE';
            values_str:='values (:vgrp_id, :vgrp_pid, :vgrp_name,:vbrp_des,:vgrp_type,:vgrp_user,:vmdate_time';

            // Update SQL statement
            q.SQLAdd(rows_str+')');
            q.SQLAdd(values_str+')');
            // Controlled columns values
            AddValue(uKey, q, 'vgrp_id',DT_DATETIME);
            AddValue(ukey, q, 'vgrp_pid',DT_DATETIME);
            AddValue(groupName, q, 'vgrp_name',DT_DATETIME);
            AddValue(GroupDes, q, 'vbrp_des',DT_DATETIME);
            AddValue('C', q, 'vgrp_type',DT_DATETIME);
            AddValue(sUsername, q, 'vgrp_user',DT_DATETIME);
            AddValue(now, q, 'vmdate_time', DT_DATETIME);
            // Execute SQL statement
            q.ExecSQL;

         except on E:Exception do begin
               // Exception raised on SQL statement
               Result:=RetErr(ER_EXCEPT,'添加部门失败.');
               CloseNewQuery(q);
            end;
         end;

         try
            CloseNewQuery(q);
         except
         end;

end;


// Delete group
//==============================================================================
function Tsup.PS_Del_Groups(const groupname: WideString): Integer;
var q,q1: TPodmsQuery;
    uid:string;
begin
     Result:=GE_OK;
     if not bConnected then begin
        Result:=RetErr(ER_NOTCONNECT,'未连接服务器.');
        Exit;
     end;

     if (uppercase(GroupName)=uppercase('System')) then begin
        Result:=RetErr(ER_GEN,'不能删除系统组.');
        Exit;
     end;
     if (GroupName='')  then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'部门必须有一个值.');
        Exit;
     end;
     if CheckIssystem(Groupname,'G') then  begin
        Result:=RetErr(ER_SECU_BUILTIN,'不能删除内置的组.');
        Exit;
     end;

     uid:=PS_valStrToStr('csi_secu_grp','c_grp_name','c_grp_id',groupname);
     // start transaction
     if t_connection.InTransaction then t_connection.Rollback;
     t_connection.StartTransaction;

     // Delete user-group relation
     //=================================================================================
     q1:=TPodmsQuery.Create(t_connection);
     if q1=nil then begin
        Result:=RetErr(ER_QUERY_ERROR,'建立查询失败.');
        Exit;
     end;
     try
       q1.SQLClear;
       q1.SQLAdd(' Delete from CSI_SECU_USER_GRP where c_grp_id='+''''+uid+'''');
       q1.ExecSQL;
     except on E:Exception do  begin
        // Exception raised on SQL statement
        Result:=RetErr(ER_EXCEPT,'删除部门失败.');
        t_connection.Rollback;
        closeNewQuery(q1);
        Exit;
     end;
     end;
     // Delete group
     //=================================================================================
     // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then begin
        // Error creating query object
        Result:=RetErr(ER_QUERY_ERROR,'建立查询失败.');
        Exit;
     end;
      // Construct SQL statement
     try
        q.SQLAdd('Delete From '+CSI_SECU_GRP);
        q.SQLAdd('where C_GRP_NAME=:vgrpname');
        q.ParamString('vgrpname', groupname);

        // Execute SQL statement
        q.ExecSQL;
        if q.RowsAffected>0 then begin
           Result:=GE_OK;
        end else
           Result:=ER_GEN;


     except on E:Exception do  begin
        // Exception raised on SQL statement
        Result:=RetErr(ER_EXCEPT,'删除部门失败 .');
        t_connection.Rollback;
     end;
     end;

     t_connection.Commit;
     PS_Ins_Logs('GEN','DELETE','Delete user:'+groupname+' by '+sUsername);
     // Close query and exit
     CloseNewQuery(q);
     CloseNewQuery(q1);



end;
// Modify group
// =============================================================================
function Tsup.PS_Mod_Groups(const groupname, groupdes,
  oldgroupname: WideString): Integer;
var q: TPodmsQuery;
    Rows_Str:string;
    id:widestring;
begin
     Result:=GE_OK;
     if (GroupName='') or (oldgroupname='') then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'部门必须填入一个名称');
        Exit;
     end;
     if CheckIssystem(oldGroupname,'G') then  begin
        Result:=RetErr(ER_SECU_BUILTIN,'不能修改系统的组.');
        Exit;
     end;
     if uppercase(groupname)<>uppercase(oldgroupname) then
       if (CheckExistStrVar(t_connection,'CSI_SECU_GRP','C_GRP_NAME',groupname)) then begin
          Result:=RetErr(ER_VAR_NOTEXIST,'值已经存在');
          Exit;
       end;

     // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then begin
        Result:=RetErr(ER_QUERY_ERROR,'建立查询失败.');
        Exit;
     end;
     id:=PS_valStrToStr(CSI_SECU_GRP,'C_GRP_NAME','C_GRP_ID',oldgroupname);
     // Construct SQL statement
     try

            q.SQLClear;
            // Controlled columns
            rows_str:='Update '+CSI_SECU_GRP+' set c_grp_name ='+''''+groupname+''''+','+' c_grp_ZG='+''''+groupdes+'''' +
                      ' where c_grp_id='+''''+id+'''';
            // Update SQL statement
            q.SQLAdd(rows_str);
            q.ExecSQL;

         except on E:Exception do begin
               // Exception raised on SQL statement
               Result:=RetErr(ER_EXCEPT,'修改部门失败.'+E.Message);
               PS_Ins_Logs('错误','修改',err_Msg);

               CloseNewQuery(q);
            end;
         end;

end;

// Insert log infomation
{
C_TYPE               VARCHAR(5)           null,
D_DATETIME           DATETIME             null,
C_USER               VARCHAR(32)          null,
C_SOURCE             VARCHAR(10)          null,
C_DESCRIPTION        VARCHAR(255)         null
)
/*C_TYPE: DB,ERROR
C_SOURCE:Insert,Update,Delete,Select,General
}
// =============================================================================
function Tsup.PS_Ins_Logs(const c_type, c_source,
  c_description: WideString): Integer;
var q:TPodmsQuery;
    s_type,s_source:widestring;
begin
     Result:=GE_OK;
     s_type:=c_type;
     s_source:=c_source;
     if c_type='' then s_type:='普通';
     if c_source='' then s_source:='正常';
     // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then begin
        Result:=RetErr(ER_QUERY_ERROR,'建立查询失败.');
        Exit;
     end;
     // Construct SQL statement
     try

         // Insert statement
            q.SQLClear;
            q.SQLAdd('Insert into CSI_LOGS values('+
                     ''''+s_type+''''+','+
                     'getdate()'+','+
                     ''''+sUsername+''''+','+
                     ''''+s_source+''''+','+
                     ''''+c_description+''''+