dbmain.pas

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

end;
//==============================================================================
function Tsup.PS_Ins_UserGrp(const username,
  groupname: WideString): Integer;
var uid,gid:string;
    q: TPodmsQuery;
    Rows_Str,values_str:string;

begin
     Result:=GE_OK;
     if (username='') or (groupname='') then begin
        Result:=RetErr(-1,'用户或部门必须有值.');
        Exit;
     end;
     uid:=PS_valStrToStr('csi_secu_user','c_user_name','c_user_id',username);
     gid:=PS_valStrToStr('csi_secu_grp','c_grp_name','c_grp_id',groupname);
     //
     if  PS_CheckExistStrVar('csi_secu_user_grp','c_user_id',uid) then begin
         Result:=RetErr(-1,'这个用户已经属于一个部门.');
         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;
     try
         // Insert statement
            q.SQLClear;
            q.SQLAdd('Insert into CSI_SECU_USER_GRP');
            // Controlled columns

            //D_LAST_LOGIN
            rows_str:='(C_USER_ID,C_GRP_ID,C_CRT_USER,D_CRT_DATE';
            values_str:=' values (:v1, :v2, :v3,:v4';
           // Update SQL statement
            q.SQLAdd(rows_str+')');
            q.SQLAdd(values_str+')');
            AddValue(uid, q, 'v1',DT_DATETIME);
            AddValue(gid, q, 'v2',DT_DATETIME);
            AddValue(sUsername, q, 'v3',DT_DATETIME);
            AddValue(now, q, 'v4', 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;

         CloseNewQuery(q);

end;

//  if username is empty, result Group
//  if groupname is empty, result User
//==============================================================================
function Tsup.PS_Get_UserGrp(const username,
  groupname: WideString): OleVariant;
var uid,gid:string;
    q: TPodmsQuery;
    cur_row:integer;
    Rows:OleVariant;
    sql_str:string;
begin

     Result:=Unassigned;
     if username='' then uid:='' else
        uid:=PS_valStrToStr('csi_secu_user','c_user_name','c_user_id',username);
     if groupname='' then gid:='' else
        gid:=PS_valStrToStr('csi_secu_grp','c_grp_name','c_grp_id',groupname);

     if not bConnected then begin
        Result:=Unassigned;
        Exit;
     end;
     // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then begin
        // Error creating query object
        Result:=Unassigned;
        Exit;
     end;
     rows:=Unassigned;
     // Construct SQL statement
     try
        if (gid='') and (uid<>'') then
           sql_str:='Select * From CSI_SECU_USER_GRP where '+'C_USER_id='+''''+uid+''''
        else if (gid<>'') and (uid='') then
           sql_str:='Select * From CSI_SECU_USER_GRP where C_grp_id='+''''+gid+''''
        else if (gid='') and (uid='') then
           sql_str:='Select * From CSI_SECU_USER_GRP'
        else if (gid<>'') and (uid<>'') then
           sql_str:='Select * From CSI_SECU_USER_GRP where C_grp_id='+''''+gid+''''+' and C_USER_id='+''''+uid+'''';


        q.SQLAdd(sql_str);
        q.Open;
        If q.RecordCount=0 then begin
           err_Code:=ER_SECU_GROUP_NORECORD;
           err_Msg:='没有记录.';
           Result:=unassigned;
           Exit;
        end;
        // Get results
        if not q.EOF then begin
           rows:=VarArrayCreate([0, CSI_SECU_USER_GRP_CLOUMNS-1, 0, 0], varVariant);
        end;
        while not q.EOF do begin
           // Store row
           cur_row:=VarArrayHighBound(rows, 2);
           rows[0, cur_row]:=PS_valStrToStr('csi_secu_user','c_user_id','c_user_name',q['C_USER_ID']);;
           rows[1, cur_row]:=PS_valStrToStr('csi_secu_grp','c_grp_id','c_grp_name',q['C_GRP_ID']);;
           rows[2, cur_row]:=q['C_CRT_USER'];
           rows[3, 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
           err_code:=-3;
           Err_Msg:='查询失败';
           Result:=unassigned;
           Exit;
        end;
     end;

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

end;
//==============================================================================
function Tsup.PS_Del_UserGrp(const username,
  groupname: WideString): Integer;
var uid,gid:string;
    q1: TPodmsQuery;
begin
     Result:=GE_OK;

     if (username='') or (groupname='') then begin
        Result:=RetErr(-1,'用户或部门必须有一个值.');
        Exit;
     end;

     if (uppercase(username)=uppercase('admin')) or (uppercase(username)=uppercase('Manager')) then
        if (uppercase(GroupName)=uppercase('System')) then begin
           Result:=RetErr(ER_GEN,'不能移动系统组.');
           Exit;
         end;
     uid:=PS_valStrToStr('csi_secu_user','c_user_name','c_user_id',username);
     gid:=PS_valStrToStr('csi_secu_grp','c_grp_name','c_grp_id',groupname);

     // 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='+''''+gid+''''+ ' and '+'c_user_id='+''''+uid+'''');
       q1.ExecSQL;
     except on E:Exception do  begin
        // Exception raised on SQL statement
        Result:=RetErr(ER_EXCEPT,'查询失败.');
     end;
     end;
     closeNewQuery(q1);
end;
//==============================================================================
function Tsup.PS_MaxVal(const tablename,
  intfield: WideString): WideString;
var q: TPodmsQuery;
    s:string;
    dt:TDateTime;
begin
     dt:=Date;
     DateTimeToString(s,'yyyymmdd',dt);
     Result:=s+'001';

     // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then Exit;
     // Construct SQL statement
     try
        q.SQLAdd('select * from '+tablename);
        q.Open;
        if q.RecordCount=0 then begin
           CloseNewQuery(q);
           Exit;
        end;
        q.close;
        q.SQLClear;
        q.SQLAdd('Select max('+intfield+') as ID From '+tablename);
        q.Open;
        If q.RecordCount=0 then begin

           CloseNewQuery(q);
           Exit;
        end;
        //q.Last;
        s:=q.StringFieldByName('ID');
        s:=IntToStr(StrToInt64(s)+1);
        Result:=s;

     except on E:Exception do begin
           result:='';
           Exit;
        end;
     end;

     CloseNewQuery(q);

end;
// val - the value of checking
//==============================================================================
function Tsup.PS_CheckExistStrVar(const table, field,
  val: WideString): WordBool;
begin
  Result:=CheckExistStrVar(t_connection,table,field,val);
end;
//==============================================================================
function Tsup.PS_Username: WideString;
begin
   Result:=sUsername;
end;
// Ture on access group
//==============================================================================
function Tsup.PS_UserAccGroup(const username,
  groupname: WideString): WordBool;
var uid,gid:string;
    q: TPodmsQuery;
    sql_str:string;
begin

     Result:=false;
     if (username='') or (groupname='') then exit;
     if not bConnected then Exit;

     uid:=PS_valStrToStr('csi_secu_user','c_user_name','c_user_id',username);
     gid:=PS_valStrToStr('csi_secu_grp','c_grp_name','c_grp_id',groupname);

     // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then Exit;

     // Construct SQL statement
     try
        sql_str:='Select * From CSI_SECU_USER_GRP where C_grp_id='+''''+gid+''''+' and C_USER_id='+''''+uid+'''';
        q.SQLAdd(sql_str);
        q.Open;
        If q.RecordCount=0 then begin
           err_Code:=ER_SECU_GROUP_NORECORD;
           err_Msg:='没有记录.';
           Exit;
        end else Result:=true;
     except on E:Exception do begin
           // Exception raised on SQL statement
           err_code:=-3;
           Err_Msg:='查询失败';
           Exit;
        end;
     end;

     CloseNewQuery(q);

end;
//==============================================================================
procedure Tsup.PS_LastErrorSet(Errorcode: Integer;
  const ErrMsg: WideString);
begin
  if err_code<>0 then
     err_Code:=ErrorCode;
  err_Msg:=ErrMsg;
end;

// Policy type
// UG - User to Group, UR - User to Role,UP - User to Project UU-user to user
//==============================================================================
function Tsup.PS_Ins_UserPolicy(const username,
  policy: WideString): Integer;
var uid:string;
    q: TPodmsQuery;
    Rows_Str,values_str:string;
begin
     Result:=0;
     if (username='') or (policy='') then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'用户或模块必须有值.');
        Exit;
     end;
     if not PS_CheckExistStrVar('CSI_POLICYS','C_POLICY_NAME',policy) then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'模块不存在.');
        Exit;
     end;
     if not PS_CheckExistStrVar('CSI_SECU_USER','C_USER_NAME',username) then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'用户不存在.');
        Exit;
     end;
     uid:=PS_valStrToStr('csi_secu_user','c_user_name','c_user_id',username);
     if uid='' then begin
        Result:=-1;
        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;
     try
         // Insert statement
            q.SQLClear;
            q.SQLAdd('Insert into CSI_POLICY_SEC');
            // Controlled columns
            //D_LAST_LOGIN
            rows_str:='(C_POLICY_NAME,C_RELAT_TYPE,C_RELAT_ID,C_SOUR_ID';
            values_str:=' values (:v1, :v2, :v3,:v4';
           // Update SQL statement
            q.SQLAdd(rows_str+')');
            q.SQLAdd(values_str+')');
            AddValue(policy, q, 'v1',DT_DATETIME);
            AddValue('UU', q, 'v2',DT_DATETIME);
            AddValue(uid, q, 'v3',DT_DATETIME);
            AddValue(uid, q, 'v4', DT_DATETIME);

            // Execute SQL statement
            q.ExecSQL;
         except on E:Exception do begin
               // Exception raised on SQL statement
               Result:=RetErr(ER_EXCEPT,'查询失败.');

            end;
         end;

         CloseNewQuery(q);

end;
//
//==============================================================================
function Tsup.PS_Del_UserPolicy(const username,
  policy: WideString): Integer;
var uid:string;
    q1: TPodmsQuery;
begin
     Result:=GE_OK;

     if (username='')  or (policy='') then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'用户名或模块必须有一个值.');
        Exit;
     end;
     if not PS_CheckExistStrVar('CSI_POLICYS','C_POLICY_NAME',policy) then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'模块不存在.');
        Exit;
     end;
     if not PS_CheckExistStrVar('CSI_SECU_USER','C_USER_NAME',username) then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'用户名不存在.');
        Exit;
     end;

     uid:=PS_valStrToStr('csi_secu_user','c_user_name','c_user_id',username);
     if uid='' then begin
        Result:=-1;
        exit;
     end;
     {if (uppercase(username)=uppercase('admin')) or (uppercase(username)=uppercase('Manager')) then
        if (uppercase(GroupName)=uppercase('System')) then begin
           Result:=RetErr(ER_GEN,'Unable to move system group.');
           Exit;
         end;}

     // Delete user-policy 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_POLICY_SEC where C_RELAT_ID='+''''+uid+''''+ ' and '+'C_SOUR_ID='+''''+uid+'''' +
                 ' and '+'C_POLICY_NAME='+''''+policy+''''+' and '+'C_RELAT_TYPE='+''''+'UU'+'''' );
       q1.ExecSQL;
     except on E:Exception do  begin
        // Exception raised on SQL statement
        Result:=RetErr(ER_EXCEPT,'查询失败.');
     end;
     end;
     closeNewQuery(q1);
end;
// Get user policy
//==============================================================================
function Tsup.PS_Get_UserPolicy(const username,
  policy: Wide