ubclass.pas

数据库试验题目

unit UBClass;

interface
uses
  ADODB, UPublic, StdCtrls, SysUtils, Dialogs, ComCtrls;
type
  TStudent = class(TObject)
  private
    _ID, _Name, _Sex, _NP, _Class, _Dept: string;
    _BDate: TDateTime;
    _Update: Boolean;
    _Conn: TADOConnection;
    _adqStudent: TADOQuery;
    procedure SetID(Value: string);
    procedure SetName(Value: string);
    procedure SetSex(Value: string);
    procedure SetNP(Value: string);
    procedure SetClass(Value: string);
    procedure SetDept(Value: string);
    procedure SetBDate(const Value: TDateTime);
    function GetClassName(ClassID: string): string;
    function GetDeptName(DeptID: string): string;
    function GetIDValue(FullValue: string): string;
  public
    property ID: string read _ID write SetID;
    property Name: string read _Name write SetName;
    property Sex: string read _Sex write SetSex;
    property NP: string read _NP write SetNP;
    property ClassID: string read _Class write SetClass;
    property Dept: string read _Dept write SetDept;
    property BDate: TDateTime read _BDate write SetBDate;
    constructor Create(Conn: TADOConnection);
    procedure SetValue(NotNULL: Boolean);overload;
    function SetValue(SID:string):boolean;overload;
    procedure GetClassList(cmbClass: TComboBox);
    procedure GetDeptList(cmbDept: TComboBox);
    procedure AddNew;
    procedure Delete;
    function MovePrior: Boolean;
    function MoveNext: Boolean;
    function GetNewSID: string;
    function GetNewCCID: string;
    function GetNewCSID: string;
    function GetNewCRID: string;
    procedure Update;
    procedure GetChangeList(cmbChange: TComboBox);
    procedure GetHistoryChange(lvwChange: TListView);
    procedure AddChange(CID: string; CTime: TDateTime; CD: string);
    procedure DelChange(CCID: string);
    procedure ModiChange(CCID, CID: string; CTime: TDateTime; CD: string);
    procedure GetPunishList(cmbPunish: TComboBox);
    procedure GetHistoryPunish(lvwPunish: TListView);
    procedure AddPunish(SID: string; STime: TDateTime; CD, SEnable: string);
    procedure ModiPunish(CCID, CID: string; CTime: TDateTime; CD, SEnable: string);
    procedure DelPunish(CCID: string);
    procedure GetRewardList(cmbReward: TComboBox);
    procedure GetHistoryReward(lvwReward: TListView);
    procedure AddReward(SID: string; STime: TDateTime; CD: string);
    procedure ModiReward(CCID, CID: string; CTime: TDateTime; CD: string);
    procedure DelReward(CCID: string);
  end;

implementation

{ TStudent }

procedure TStudent.AddChange(CID: string; CTime: TDateTime;
  CD: string);
var
  adqChange: TADOQuery;
begin
  adqChange := TADOQuery.Create(nil);
  try
    with adqChange do
    begin
      connection := _Conn;
      sql.Clear;
      sql.Add('select * from change where 1=0');
      open;
      append;
      fieldbyname('ID').AsString := GetNewCCID;
      fieldbyname('STUDENT').AsString := _ID;
      fieldbyname('CHANGE').AsString := GetIDValue(CID);
      fieldbyname('REC_TIME').AsDateTime := int(CTime);
      fieldbyname('DESCRIPTION').AsString := CD;
      post;
    end;
  finally
    adqChange.Free;
  end;
end;

procedure TStudent.AddNew;
begin
  with _adqStudent do
  begin
    append;
    fieldbyname('ID').AsString := _ID;
    fieldbyname('NAME').AsString := _Name;
    fieldbyname('SEX').AsString := _Sex;
    fieldbyname('BIRTHDAY').AsDateTime := int(_BDate);
    fieldbyname('NATIVE_PLACE').AsString := _NP;
    fieldbyname('CLASS').AsString := GetIDValue(_Class);
    fieldbyname('DEPARTMENT').AsString := GetIDValue(_Dept);
    post;
  end;
end;

procedure TStudent.AddPunish(SID: string; STime: TDateTime; CD,
  SEnable: string);
var
  adqPunish: TADOQuery;
begin
  adqPunish := TADOQuery.Create(nil);
  try
    with adqPunish do
    begin
      connection := _Conn;
      sql.Clear;
      sql.Add('select * from punishment where 1=0');
      open;
      append;
      fieldbyname('ID').AsString := GetNewCCID;
      fieldbyname('STUDENT').AsString := _ID;
      fieldbyname('LEVELS').AsString := GetIDValue(SID);
      fieldbyname('REC_TIME').AsDateTime := int(STime);
      fieldbyname('ENABLE').AsString := SEnable;
      fieldbyname('DESCRIPTION').AsString := CD;
      post;
    end;
  finally
    adqPunish.Free;
  end;
end;

procedure TStudent.AddReward(SID: string; STime: TDateTime; CD: string);
var
  adqReward: TADOQuery;
begin
  adqReward := TADOQuery.Create(nil);
  try
    with adqReward do
    begin
      connection := _Conn;
      sql.Clear;
      sql.Add('select * from reward where 1=0');
      open;
      append;
      fieldbyname('ID').AsString := GetNewCRID;
      fieldbyname('STUDENT').AsString := _ID;
      fieldbyname('LEVELS').AsString := GetIDValue(SID);
      fieldbyname('REC_TIME').AsDateTime := int(STime);
      fieldbyname('DESCRIPTION').AsString := CD;
      post;
    end;
  finally
    adqReward.Free;
  end;
end;

constructor TStudent.Create(Conn: TADOConnection);
begin
  _Conn := Conn;
  _adqStudent := TADOQuery.Create(nil);
  _adqStudent.Connection := Conn;
  with _adqStudent do
  begin
    sql.Clear;
    sql.Add('select * from Student');
    open;
    if isempty then SetValue(false)
    else
    begin
      first;
      SetValue(true);
    end;
  end;
  _Update := false;
end;

procedure TStudent.DelChange(CCID: string);
var
  adqChange: TADOQuery;
begin
  adqChange := TADOQuery.Create(nil);
  try
    with adqChange do
    begin
      connection := _Conn;
      sql.Clear;
      sql.Add('delete from change where id=''' + CCID + '''');
      execsql;
    end;
  finally
    adqChange.Free;
  end;
end;

procedure TStudent.Delete;
begin
  if _adqStudent.Locate('ID', _ID, []) then
  begin
    _adqStudent.Delete;
    SetValue(false);
  end;
end;

procedure TStudent.DelPunish(CCID: string);
var
  adqPunish: TADOQuery;
begin
  adqPunish := TADOQuery.Create(nil);
  try
    with adqPunish do
    begin
      connection := _Conn;
      sql.Clear;
      sql.Add('delete from punishment where id=''' + CCID + '''');
      execsql;
    end;
  finally
    adqPunish.Free;
  end;
end;


procedure TStudent.DelReward(CCID: string);
var
  adqReward: TADOQuery;
begin
  adqReward := TADOQuery.Create(nil);
  try
    with adqReward do
    begin
      connection := _Conn;
      sql.Clear;
      sql.Add('delete from reward where id=''' + CCID + '''');
      execsql;
    end;
  finally
    adqReward.Free;
  end;
end;

procedure TStudent.GetChangeList(cmbChange: TComboBox);
var
  adqChange: TADOQuery;
begin
  adqChange := TADOQuery.Create(nil);
  try
    cmbChange.Clear;
    with adqChange do
    begin
      connection := _Conn;
      sql.Clear;
      sql.Add('select * from CHANGE_CODE');
      open;
      if not isempty then
      begin
        first;
        while not eof do
        begin
          cmbChange.AddItem(GetStrDataSet(adqChange, 'CODE', '') + '-'
            + GetStrDataSet(adqChange, 'DESCRIPTION', ''), nil);
          next;
        end;
      end;
    end;
  finally
    adqChange.Free;
  end;
end;

procedure TStudent.GetClassList(cmbClass: TComboBox);
var
  adqClass: TADOQuery;
begin
  adqClass := TADOQuery.Create(nil);
  try
    cmbClass.Clear;
    with adqClass do
    begin
      connection := _Conn;
      sql.Clear;
      sql.Add('select * from Class');
      open;
      if not isempty then
      begin
        first;
        while not eof do
        begin
          cmbClass.AddItem(GetStrDataSet(adqClass, 'ID', '') + '-'
            + GetStrDataSet(adqClass, 'NAME', ''), nil);
          next;
        end;
      end;
    end;
  finally
    adqClass.Free;
  end;
end;

function TStudent.GetClassName(ClassID: string): string;
var
  adqClass: TADOQuery;
begin
  adqClass := TADOQuery.Create(nil);
  try
    with adqClass do
    begin
      connection := _Conn;
      sql.Clear;
      sql.Add('select * from Class');
      open;
      if locate('ID', ClassID, []) then
        result := GetStrDataSet(adqClass, 'NAME', '')
      else result := '';
    end;
  finally
    adqClass.Free;
  end;
end;

procedure TStudent.GetDeptList(cmbDept: TComboBox);
var
  adqDept: TADOQuery;
begin
  adqDept := TADOQuery.Create(nil);
  try
    cmbDept.Clear;
    with adqDept do
    begin
      connection := _Conn;
      sql.Clear;
      sql.Add('select * from DEPARTMENT');
      open;
      if not isempty then
      begin
        first;
        while not eof do
        begin
          cmbDept.AddItem(GetStrDataSet(adqDept, 'ID', '') + '-'
            + GetStrDataSet(adqDept, 'NAME', ''), nil);
          next;
        end;
      end;
    end;
  finally
    adqDept.Free;
  end;
end;

function TStudent.GetDeptName(DeptID: string): string;
var
  adqDept: TADOQuery;
begin
  adqDept := TADOQuery.Create(nil);
  try
    with adqDept do
    begin
      connection := _Conn;
      sql.Clear;
      sql.Add('select * from DEPARTMENT');
      open;
      if locate('ID', DeptID, []) then
        result := GetStrDataSet(adqDept, 'NAME', '')
      else result := '';
    end;
  finally
    adqDept.Free;
  end;
end;

procedure TStudent.GetHistoryChange(lvwChange: TListView);
var
  adqChange: TADOQuery;
begin
  adqChange := TADOQuery.Create(nil);
  try
    lvwChange.Clear;
    with adqChange do
    begin
      connection := _Conn;
      sql.Clear;
      sql.Add('select a.ID,a.Student,b.Name,a.Change,c.DESCRIPTION as CD,'
        + 'a.REC_TIME,a.DESCRIPTION as AD from CHANGE a,STUDENT b,CHANGE_CODE c'
        + ' where a.STUDENT=b.ID and a.Change=c.CODE'
        + ' and a.STUDENT=''' + _ID + '''');
      open;
      if not isempty then
      begin
        first;
        while not eof do
        begin
          with lvwChange.Items.Add do
          begin
            caption := GetStrDataSet(adqChange, 'ID', '');
            subitems.Add(GetStrDataSet(adqChange, 'REC_TIME', ''));
            subitems.Add(GetStrDataSet(adqChange, 'Change', '') + '-'
              + GetStrDataSet(adqChange, 'CD', ''));
            subitems.Add(GetStrDataSet(adqChange, 'AD', ''));
          end;
          next;
        end;
      end;
    end;
  finally
    adqChange.Free;
  end;
end;

procedure TStudent.GetHistoryPunish(lvwPunish: TListView);
var
  adqPunish: TADOQuery;
begin
  adqPunish := TADOQuery.Create(nil);
  try
    lvwPunish.Clear;
    with adqPunish do
    begin
      connection := _Conn;
      sql.Clear;
      sql.Add('select a.ID,a.Student,b.Name,a.LEVELS,c.DESCRIPTION as CD,'
        + 'a.REC_TIME,a.Enable,a.DESCRIPTION as AD from PUNISHMENT a,STUDENT b,PUNISH_LEVELS c'
        + ' where a.STUDENT=b.ID and a.LEVELS=c.CODE'
        + ' and a.STUDENT=''' + _ID + '''');
      open;
      if not isempty then
      begin
        first;
        while not eof do
        begin
          with lvwPunish.Items.Add do
          begin
            caption := GetStrDataSet(adqPunish, 'ID', '');
            subitems.Add(GetStrDataSet(adqPunish, 'REC_TIME', ''));
            subitems.Add(GetStrDataSet(adqPunish, 'LEVELS', '') + '-'
              + GetStrDataSet(adqPunish, 'CD', ''));
            subitems.add(GetStrDataSet(adqPunish, 'ENABLE', ''));
            subitems.Add(GetStrDataSet(adqPunish, 'AD', ''));
          end;
          next;
        end;
      end;
    end;
  finally
    adqPunish.Free;
  end;
end;

procedure TStudent.GetHistoryReward(lvwReward: TListView);
var