dbmain.pas

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

        while not q.EOF do begin
           rows[0, i]:=q[outfield];
           i:=i+1;
           // Next row
           q.Next;

        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;
// return batch record
//==============================================================================
function Tsup.PS_intIntoOut(const table, infield,
  outfield: WideString; invalue: Integer): OleVariant;
var q: TPodmsQuery;
    Rows:OleVariant;
    i:integer;
begin
     Result:=Unassigned;
     if not bConnected then Exit;
     // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then Exit;

     rows:=Unassigned;
     try
        q.SQLAdd('Select '+outfield+' From '+table+' where '+infield+'='+intToStr(invalue));
        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, q.RecordCount-1], varVariant);
        end;
        //for i:=0 to el.Count - 1 do error_list[i]:=el[i];
        i:=0;
        while not q.EOF do begin
           rows[0, i]:=q[outfield];
           i:=i+1;
           q.Next;
        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;
// Delete User
//==============================================================================
function Tsup.PS_Del_User(const username: WideString): Integer;
var q,q1,q4: TPodmsQuery;
    uid:string;
begin
     Result:=GE_OK;
     if not bConnected then begin
        Result:=RetErr(ER_NOTCONNECT,'没有连接服务器.');
        Exit;
     end;

     if (username='')  then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'用户名不能为空.');
        Exit;
     end;
     if CheckIssystem(username,'U') then  begin
        Result:=RetErr(ER_SECU_BUILTIN,'不能删除系统用户.');
        Exit;
     end;

     uid:=PS_valStrToStr('csi_secu_user','c_user_name','c_user_id',username);

     if t_Connection.InTransaction then t_Connection.Rollback;
     t_connection.StartTransaction;


     // Delete Relation of user - user->policy
     //=====================================================================================
     q4:=TPodmsQuery.Create(t_connection);
     if q4=nil then begin
        Result:=RetErr(ER_QUERY_ERROR,'删除拥护失败.');
        Exit;
     end;
     try
       q4.SQLClear;
       q4.SQLAdd(' Delete from CSI_POLICY_SEC where c_relat_id='+''''+uid+''''+' and c_sour_id='+
                 ''''+uid+''''+' and c_RELAT_TYPE='+''''+'UU'+'''');
       q4.ExecSQL;
     except on E:Exception do  begin
        // Exception raised on SQL statement
        Result:=RetErr(ER_EXCEPT,'删除用户失败.');
        t_connection.Rollback;
        closeNewQuery(q4);
        Exit;
     end;
     end;
     // Delete Relation of user  - user->group
     //=====================================================================================
     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_user_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 user
     //===================================================================================
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then begin
        // Error creating query object
        Result:=RetErr(ER_QUERY_ERROR,'删除用户失败.');
        Exit;
     end;
     try
        q.SQLAdd('Delete from '+CSI_SECU_USER);
        q.SQLAdd('where C_USER_NAME=:vgrpname');
        q.ParamString('vgrpname', username);
        // Execute SQL statement
        q.ExecSQL;

     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:'+username+' by '+sUsername);
     // Close query and exit
     closeNewQuery(q);
     CloseNewQuery(q1);


end;
//==============================================================================
function Tsup.PS_valIntTostr(const table, infield,
  outfield: WideString; invalue: Integer): WideString;
var outintval:integer;
    outstrval:string;
begin
  valtoval('I2S',table,infield,outfield,'',invalue,outstrval,outintval);
  result:=outstrval;
end;

function Tsup.PS_ChangePwd(const username, oldPassword,
  password: WideString): Integer;
var q,q1: TPodmsQuery;
    Rows_Str:string;
    t_password:string;
    t_oldPwd:string;
begin
     Result:=GE_OK;
     {if uppercase(username)<>uppercase(sUsername) then begin
           Result:=RetErr(ER_VAR_NOTEXIST,'You can only change own password');
           Exit;
     end;}

     // Create query object
     q1:=TPodmsQuery.Create(t_connection);
     if q1=nil then begin
        // Error creating query object
        Result:=-1;
        Exit;
     end;
     t_oldpwd:=utils.Encrypt(oldpassword);
     // Construct SQL statement
     try

        q1.SQLAdd('Select * From '+CSI_SECU_USER);
        q1.SQLAdd(' where C_USER_NAME=:vname'+' and C_USER_PWD=:pwd');
        q1.ParamString('vname',username);
        q1.ParamString('pwd',t_oldpwd);
        q1.Open;
        if Q1.RecordCount=0 then begin
             Result:=RetErr(-1,'老的密码不匹配.');
             Exit;
        end;
      except on E:Exception do begin
               // Exception raised on SQL statement
               Result:=RetErr(ER_EXCEPT,'修改密码失败.');
               Exit;
      end;
      end;
      CloseNewQuery(q1);
     // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then begin
        Result:=RetErr(ER_QUERY_ERROR,'修改密码失败.');
        Exit;
     end;
     t_password:=utils.Encrypt(password);
     try

            q.SQLClear;
            rows_str:='Update '+CSI_SECU_USER+' set C_USER_PWD ='+''''+t_password+''''+
                                  ' where c_user_name='+''''+username+'''';
            // Update SQL statement
            q.SQLAdd(rows_str);
            q.ExecSQL;

     except on E:Exception do begin
               // Exception raised on SQL statement
               Result:=RetErr(ER_EXCEPT,'修改密码失败');
               PS_Ins_Logs('ERROR','UPDATE',err_Msg+e.Message);

               CloseNewQuery(q);
            end;
     end;
     CloseNewQuery(q);
end;

{OLE[0] ='C_USER_ID';      //          VARCHAR(32)
OLE[1]  ='C_USER_NAME';  //          VARCHAR(32)
OLE[2]  ='C_USER_DESC';  //          VARCHAR(128)
OLE[3] ='C_USER_EMAIL'; //          VARCHAR(64)
OLE[4] ='C_RIGHTS';        //    CHAR(1)
OLE[5] ='C_USER_STATUS';   //    CHAR(1)
OlE[6]=password days                    }

//C_USER_STATUS D-DISABLED A-ACTIVE C-CREATED P-PEDING*/
//RIGTHS A-ADMIN M-MANAGE N-NULL B -BOTH*/
//const username, description, password,
//  email: WideString; pwdday: Integer; const rights,
//  status: WideString): Intege
//==============================================================================
function Tsup.PS_Mod_Users(ole: OleVariant): Integer;
var
    q: TPodmsQuery;
    pwdexpdate:string;
    username,description,email,status,t_status,rights,t_rights:string;
    pwdday:integer;
    old_user:string;

begin

     if (VarIsEmpty(ole)) then begin
        Result:=RetErr(ER_OLEVAR_EMPTY,'没有记录');;
        Exit;
     end;

     username:=PS_valStrToStr('csi_secu_user','c_user_id','c_user_name',ole[0]);
     //if username<>ole[1] then begin
     if CheckIssystem(username,'U') then  begin
          Result:=RetErr(ER_SECU_BUILTIN,'不能修改系统用户.');
          Exit;
     end;
     //end;

     if not bAdmin then begin
           Result:=RetErr(ER_SECU_BUILTIN,'不能修改用户.');
           Exit;
     end;
     username:=ole[1];
     description:=ole[2];
     email:=ole[3];
     status:=ole[5];
     rights:=ole[4];
     pwdday:=ole[6];
     if pwdday=0 then pwdexpdate:='NULL'
     else pwdexpdate:='getdate()';

     Result:=GE_OK;
     if (ole[2]='') or (Ole[1]='') then begin
        Result:=RetErr(ER_NOTCONNECT,'用户帐号或不能名字为空.');
                Exit;
     end;
     old_user:=PS_valStrToStr('csi_secu_user','c_user_id','c_user_name',ole[0]);

     if uppercase(ole[1])<> uppercase(old_user) then
       if (CheckExistStrVar(t_connection,'CSI_SECU_USER','C_USER_NAME',userName)) then begin
          Result:=RetErr(ER_VAR_NOTEXIST,'用户已存在');
          Exit;
       end;

     if status='' then t_status:='' else t_status:=GetInstr(status,1);
     if rights='' then t_rights:='' else t_rights:=GetInstr(rights,2);
     //pwdexpdate:=now+pwdday;
     // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then begin
        // Error creating query object
        Result:=RetErr(ER_QUERY_ERROR,'修改拥护失败.');
        Exit;
     end;
     try

         q.SQLClear;
         q.SQLAdd('Update CSI_SECU_USER ');
         q.SQLAdd(' set c_user_name=:v1');
         q.ParamString('v1', username);
         //AddUpdateValue(username, q, 'c_user_name',DT_DATETIME);
         AddUpdateValue(description, q, 'C_USER_LOGID',DT_DATETIME);
         AddUpdateValue(email, q, 'C_USER_ZW',DT_DATETIME);
         if pwdday=0 then  begin
              AddUpdateValue(null, q, 'D_PASSWORD_DATE', DT_DATETIME);
         end else begin
              AddUpdateValue(now, q, 'D_PASSWORD_DATE', DT_DATETIME);
         end;

         AddUpdateValue(pwdday, q, 'D_EXPIRED_DATE', DT_DATETIME);
         AddUpdateValue(t_rights, q, 'C_RIGHTS', DT_DATETIME);
         AddUpdateValue(t_status, q, 'C_USER_STATUS', DT_DATETIME);
         q.SQLAdd(' where c_user_id=:v8');
          q.ParamString('v8',ole[0]);
          // Execute SQL statement
          q.ExecSQL;
         except on E:Exception do begin
               // Exception raised on SQL statement
               Result:=RetErr(ER_EXCEPT,'修改用户失败.');
               CloseNewQuery(q);
            end;
         end;
         PS_Ins_Logs('普通','修改','UPDATE user:'+ole[1]+' by '+sUsername);
         try
            CloseNewQuery(q);
         except
         end;

end;

// Flag: A-Admin M-Manager, N-General user
// ==============================================================================
function Tsup.PS_CheckUser(const username: WideString;
  out flag: WideString): Integer;
var
  sRights:string;
  q: TPodmsQuery;

begin

      Result:=0;

     if username='' then begin
       Result:=-1;
       Exit;
     end;

     q:=nil;
     // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then begin
        // Error creating query object
        Result:=-1;
        Exit;
     end;

     // Construct SQL statement
     try

        q.SQLAdd('Select * From '+CSI_SECU_USER);
        q.SQLAdd(' where C_USER_NAME=:vname');
        q.ParamString('vname',username);
        q.Open;
        if Q.RecordCount=0 then begin
             Result:=RetErr(-3,'用户名不合适.');
             Exit;
        end;

        // Get results
        if not q.EOF then begin

           sRights      := q.StringFieldByName('C_RIGHTS');

           // Check rights
           flag:=sRights;
        end;
     except on E:Exception do begin
           // Exception raised on SQL statement
           Result:=RetErr(-3,'查询失败.');
           Exit;
        end;
     end;

     CloseNewQuery(q);