dbmain.pas

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

                     ')');

            // 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;
// Get Logs
// =============================================================================
function Tsup.PS_Get_Logs(const c_type,
  c_source: WideString): OleVariant;
var q: TPodmsQuery;
    cur_row:integer;
    Rows:OleVariant;

begin
     rows:=Unassigned;
     if not bConnected then Exit;
      // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then Exit;

     // Construct SQL statement
     try
        if (c_type='') and (c_source='') then begin
           q.SQLAdd('Select * From CSI_LOGS');
        end else if (c_type='') and (c_source<>'') then begin
           q.SQLAdd('Select * From CSI_LOGS where C_SOURCE='+''''+c_source+'''');
         end else if (c_type<>'') and (c_source='') then begin
           q.SQLAdd('Select * From CSI_LOGS where C_TYPE='+''''+c_type+'''');
        end else if (c_type<>'') and (c_source<>'') then begin
           q.SQLAdd('Select * From CSI_LOGS where C_SOURCE='+''''+c_source+''''+' and C_TYPE='+''''+c_type+'''');
        end;

        // Execute SQL statement
        Result:=0;
        q.Open;

        If q.RecordCount=0 then begin
           RetErr(ER_SECU_GROUP_NORECORD,'没有记录.');
           Result:=unassigned;
           Exit;
        end;
        // Get results
        if not q.EOF then begin
           rows:=VarArrayCreate([0,CSI_LOGS_CLOUMNS-1, 0, 0], varVariant);
        end;
        while not q.EOF do begin
           // Store row
           cur_row:=VarArrayHighBound(rows, 2);
           rows[0, cur_row]:=q.StringFieldByName('C_TYPE');
           rows[1, cur_row]:=q.DateTimeFieldByName('D_DATETIME');
           rows[2, cur_row]:=q.StringFieldByName('C_USER');
           rows[3, cur_row]:=q.StringFieldByName('C_SOURCE');
           rows[4, cur_row]:=q.StringFieldByName('C_DESCRIPTION');


           // 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_Logs(const condit: WideString): Integer;
var q:TPodmsQuery;
begin
   // Create query object
     q:=TPodmsQuery.Create(t_connection);
     if q=nil then begin
        Result:=RetErr(ER_QUERY_ERROR,'建立查询失败.');
        Exit;
     end;
      // Construct SQL statement
     try
        q.SQLAdd('Delete from CSI_LOGS');
        if condit<>'' then
           q.SQLAdd(condit);

        // Execute SQL statement
        q.ExecSQL;
        if q.RowsAffected>0 then begin
           //FrontEnd.g_Audit.Audit(AUDIT_REQUEST, reqin.audit_user, reqin.audit_location, reqin.req_type, reqin.rhandle, q);
           Result:=GE_OK;
        end else
           Result:=ER_GEN;
     except on E:Exception do  begin
        // Exception raised on SQL statement
        Result:=RetErr(ER_EXCEPT,'删除日志失败.');

     end;
     end;

     // Close query and exit
     CloseNewQuery(q);

end;
// Get users
//==============================================================================
function Tsup.PS_Get_Users(const username: WideString): OleVariant;
var q: TPodmsQuery;
    cur_row:integer;
    Rows:OleVariant;

begin
     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 username='' then begin
           q.SQLAdd('Select * From CSI_SECU_USER');
        end else begin
           q.SQLAdd('Select * From CSI_SECU_USER where C_USER_NAME='+''''+username+'''');

        end;
        //showmessage('Select * From '+CSI_SECU_GRP+' where C_GRP_NAME='+''''+groupname+'''');
        // Execute SQL statement
        Result:=0;
        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
           //FrontEnd.g_Audit.Audit(AUDIT_REQUEST, reqin.audit_user, reqin.audit_location, reqin.req_type, reqin.rhandle, q);
           rows:=VarArrayCreate([0, CSI_SECU_USER_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_USER_ID'];
           rows[1, cur_row]:=q['C_USER_NAME'];
           rows[2, cur_row]:=q['C_USER_LOGID'];
           rows[3, cur_row]:=q['C_USER_PWD'];//utils.Decrypt(q['C_USER_PWD']);
           rows[4, cur_row]:=q['C_USER_ZW'];
           rows[5, cur_row]:=q['C_CRT_USER'];
           rows[6, cur_row]:=q['D_CRT_DATE'];
           rows[7, cur_row]:=q['D_LAST_LOGIN'];
           rows[8, cur_row]:=q['D_PASSWORD_DATE'];
           rows[9, cur_row]:=q['D_EXPIRED_DATE'];
           rows[10, cur_row]:=ConvalOut(q.StringFieldByName('C_RIGHTS'),'CSI_SECU_USER','C_RIGHTS');
           rows[11, cur_row]:=ConvalOut(q.StringFieldByName('C_USER_STATUS'),'CSI_SECU_USER','C_USER_STATUS');

           // 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:=SelectExceptionRaised(reqin.in_table.table_type, E, reqout);
           Result:=unassigned;
           Exit;
        end;
     end;

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

end;

{
C_USER_ID            VARCHAR(32)          not null,
C_USER_NAME          VARCHAR(32)          not null,
C_USER_DESC          VARCHAR(128)         null,
C_USER_PWD           VARCHAR(32)          not null default (''''),
C_USER_EMAIL         VARCHAR(64)          NULL,
C_CRT_USER           VARCHAR(32)          not null,
D_CRT_DATE           DATETIME             not null default (Getdate()),
D_LAST_LOGIN         DATETIME             null,
D_PASSWORD_DATE      DATETIME             null,
D_EXPIRED_DATE       DATETIME             null,
C_RIGHTS             CHAR(1)              not null,
C_USER_STATUS        CHAR(1)              NOT NULL,
/*C_USER_STATUS D-DESABLED A-ACTIVE C-CREATED P-PEDING*/
/*RIGTHS A-ADMIN M-MANAGE N-NULL B -BOTH*/}
// =============================================================================
function Tsup.PS_Ins_Users(const username, description, password,
  email: WideString; pwdday: Integer; const rights,
  status: WideString): Integer;
var
    q: TPodmsQuery;
    uKey:string;
    Rows_Str,values_str:string;
    //pwdexpdate:TDatetime;
    t_status:string;
    t_rights:string;
    t_password:widestring;
begin

     Result:=GE_OK;
     if  (username='') or (description='') then begin
        Result:=RetErr(ER_NOTCONNECT,'没有用户名或帐号.');
        Exit;
     end;
     uKey:=GetUKey(32);
     if (CheckExistStrVar(t_connection,'CSI_SECU_USER','C_USER_NAME',userName)) then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'用户名已存在');
        Exit;
     end;
     if (CheckExistStrVar(t_connection,'CSI_SECU_USER','C_USER_LOGID',description)) then begin
        Result:=RetErr(ER_VAR_NOTEXIST,'用户帐号已存在');
        Exit;
     END;
     if status='' then t_status:='A' else t_status:=GetInstr(status,1);
     if rights='' then t_rights:='N' else t_rights:=GetInstr(rights,2);
     //showmessage(t_rights);
     t_password:=utils.Encrypt(password);
     //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
         // Insert statement
            q.SQLClear;
            q.SQLAdd('Insert into CSI_SECU_USER');
            // Controlled columns

            //D_LAST_LOGIN
            rows_str:='(C_USER_ID,C_USER_NAME,C_USER_LOGID,C_USER_PWD,C_USER_ZW,C_CRT_USER,D_CRT_DATE,D_PASSWORD_DATE,D_EXPIRED_DATE,C_RIGHTS,C_USER_STATUS';
            values_str:=' values (:v1, :v2, :v3,:v4,:v5,:v6,:v7,:v8,:v9,:v10,:v11';
           // Update SQL statement
            q.SQLAdd(rows_str+')');
            q.SQLAdd(values_str+')');
            AddValue(uKey, q, 'v1',DT_DATETIME);
            AddValue(username, q, 'v2',DT_DATETIME);
            AddValue(description, q, 'v3',DT_DATETIME);
            AddValue(t_password, q, 'v4',DT_DATETIME);
            AddValue(email, q, 'v5',DT_DATETIME);
            AddValue(sUsername, q, 'v6',DT_DATETIME);
            AddValue(now, q, 'v7', DT_DATETIME);
            if pwdday=0 then AddValue(null, q, 'v8', DT_DATETIME)
            else AddValue(now, q, 'v8', DT_DATETIME);

            AddValue(pwdday, q, 'v9', DT_DATETIME);
            AddValue(t_rights, q, 'v10', DT_DATETIME);
            AddValue(t_status, q, 'v11', 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;

//// Get a string field value according to another field value
//==============================================================================
function Tsup.PS_valStrToStr(const table, infield, outfield,
  invalue: WideString): WideString;
var outintval:integer;
    outstrval:string;
begin

  valtoval('S2S',table,infield,outfield,invalue,0,outstrval,outintval);
  result:=outstrval;
end;


function Tsup.PS_valStrToInt(const table, infield, outfield,
  invalue: WideString): Integer;
var outintval:integer;
    outstrval:string;
begin
  valtoval('S2I',table,infield,outfield,invalue,0,outstrval,outintval);
  result:=outintval;
end;
// flag:='I2I','S2S',I2S,S2I
procedure Tsup.valtoval(const flag,table,infield,outfield:string;
         instrval:string;inintval:integer; out outstrval:string;out outintval:integer);
var q: TPodmsQuery;
    sql_str:string;
begin

     q:=TPodmsQuery.Create(t_connection);
     if q=nil then Exit;
     try
        if flag='I2I' then
           sql_str:='Select '+outfield+' From '+table+' where '+infield+'='+inttostr(inintval)
        else if flag='I2S' then
           sql_str:='Select '+outfield+' From '+table+' where '+infield+'='+inttostr(inintval)
        else if flag='S2I' then begin
           sql_str:='Select '+outfield+' From '+table+' where '+infield+'='+''''+instrval+'''';
        end else if flag='S2S' then
           sql_str:='Select '+outfield+' From '+table+' where '+infield+'='+''''+instrval+'''';
        q.SQLAdd(sql_str);
        q.Open;
        If q.RecordCount=0 then begin
           RetErr(ER_SECU_GROUP_NORECORD,'没有记录.');
           Exit;
        end;
        //
        if flag='I2I' then
           outintval:=q.IntegerFieldByName(outfield)
        else if flag='I2S' then
           outstrval:=q.StringFieldByName(outfield)
        else if flag='S2I' then
           outIntval:=q.IntegerFieldByName(outfield)
        else if flag='S2S' then
           outstrval:=q.StringFieldByName(outfield);
        //


     except on E:Exception do begin
           RetErr(-3,'查询错误.');
           Exit;
        end;
     end;

     // Close query and exit

     CloseNewQuery(q);

end;
//==============================================================================
function Tsup.PS_valInttoint(const table, infield,
  outfield: WideString; invalue: Integer): Integer;
var outintval:integer;
    outstrval:string;
begin
  valtoval('I2I',table,infield,outfield,'',invalue,outstrval,outintval);
  result:=outintval;
end;
//==============================================================================
function Tsup.PS_strInToOut(const table, infield, outfield,
  invalue: WideString): 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+'='+''''+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;