accessunit.pas

一个小的成绩信息管理系统

unit accessUnit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Buttons, ComCtrls, DB, ADODB, ExtCtrls;

type
  TaccessForm = class(TForm)
    listview1: TListView;
    ListView2: TListView;
    ListView3: TListView;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    SpeedButton4: TSpeedButton;
    ADOQuery1: TADOQuery;
    Bevel1: TBevel;
    ListView4: TListView;
    procedure SpeedButton4Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure listview1SelectItem(Sender: TObject; Item: TListItem;
      Selected: Boolean);
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  accessForm: TaccessForm;

implementation
  uses
   mainunit;
{$R *.dfm}

procedure TaccessForm.SpeedButton4Click(Sender: TObject);
begin
  close;
end;

procedure TaccessForm.FormShow(Sender: TObject);
var
  listitem:Tlistitem;
  i:integer;
begin
  //把用户信息从数据库读取到listview中
  ADOQuery1.Sql.Clear;
  ADOQuery1.SQL.Add('select userid,name,acount,password,state from users');
  ADOQuery1.open;
  listview1.Items.Clear();
  while (not  ADOQuery1.Eof ) do
    begin
        listitem:=listview1.Items.Add();
        listitem.Caption:=Trim(ADOQuery1.FieldByName('userid').AsString);
        if Trim(ADOQuery1.FieldByName('name').AsString)='' then
           listitem.SubItems.Add(' ')
        else
          listitem.SubItems.Add(Trim(ADOQuery1.FieldByName('name').AsString));
        if Trim(ADOQuery1.FieldByName('acount').AsString)='' then
           listitem.SubItems.Add(' ')
        else
          listitem.SubItems.Add(Trim(ADOQuery1.FieldByName('acount').AsString));
        if Trim(ADOQuery1.FieldByName('password').AsString)='' then
           listitem.SubItems.Add(' ')
        else
          listitem.SubItems.Add(Trim(ADOQuery1.FieldByName('password').AsString));

        if Trim(ADOQuery1.FieldByName('state').AsString)='' then
           listitem.SubItems.Add(' ')
        else
          listitem.SubItems.Add(Trim(ADOQuery1.FieldByName('state').AsString));
        ADOQuery1.Next();
     end;
  //读取系统菜单名到listview4中----------------------------------------
  listview4.Items.Clear;
  Adoquery1.SQL.Clear;
  Adoquery1.SQL.Add('Select aname,aid from Access');
  Adoquery1.Open;
  while not Adoquery1.Eof do
  begin
    ListItem:=listview4.Items.Add;
    listItem.Caption :=trim(Adoquery1.Fieldbyname('aname').asstring);
    listItem.SubItems.Add(trim(Adoquery1.Fieldbyname('aid').asstring));
    Adoquery1.Next;
  end;
end;
procedure TaccessForm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action:=cafree;
end;

procedure TaccessForm.listview1SelectItem(Sender: TObject; Item: TListItem;
  Selected: Boolean);
  var
   listitem:Tlistitem;
   i:integer;
begin
  //读取系统菜单名到listview4中----------------------------------------
  listview3.Items:=listview4.Items;
  if listview1.Selected<>nil then
     begin
        listview2.Items.Clear;
        adoquery1.SQL.Clear;
        adoquery1.SQL.Add('select a.aname,b.aid from access a join [u-a] b on a.aid=b.aid where b.userid='''+trim(listview1.Selected.Caption)+''' ');
        adoquery1.open;
        while not Adoquery1.Eof  do
        begin
          //该用户已分配的权限，不用再显示到listview3中；
          for i:=0 to listview3.Items.Count-1 do
              if trim(Adoquery1.Fieldbyname('aname').asstring)=trim(listview3.Items[i].Caption) then
                 begin
                   listview3.Items[i].Delete;
                   break;
                 end;
          //-------------------------------------
          ListItem:=listview2.Items.Add;
          listItem.Caption :=trim(Adoquery1.Fieldbyname('aname').asstring);
          listItem.SubItems.Add(trim(Adoquery1.Fieldbyname('aid').asstring));
          Adoquery1.Next;
        end;
     end;
end;

procedure TaccessForm.SpeedButton1Click(Sender: TObject);
var
  listitem:Tlistitem;
begin
  if listview2.Selected=nil then
     exit;
  try
   Adoquery1.SQL.Clear;
   Adoquery1.SQL.Add('Delete from [u-a] where (userid=:userid and aid=:aid)');
   Adoquery1.Parameters.ParamByName('userid').Value:=trim(listview1.Selected.Caption);
   Adoquery1.Parameters.ParamByName('aid').Value:=trim(listview2.Selected.SubItems.Strings[0]);
   Adoquery1.ExecSQL;
  except
    application.MessageBox('更新数据库失败，请检查后再试！','错误',MB_OK+MB_ICONSTOP);
    exit;
  end;
  listitem:=listview3.Items.Add;
  listitem.Caption:=trim(listview2.Selected.Caption);
  listitem.SubItems.Add(trim(listview2.Selected.SubItems.Strings[0]));
  listview2.Selected.Delete;
end;

procedure TaccessForm.SpeedButton2Click(Sender: TObject);
var
  i:integer;
  listitem:Tlistitem;
begin
  for i:=0 to listview3.Items.Count-1 do
      if listview3.Items[i].Checked=true then
         begin
           try
           Adoquery1.SQL.Clear;
           Adoquery1.SQL.Add('insert into [u-a](userid,aid) values(:userid,:aid)');
           Adoquery1.Parameters.ParamByName('userid').Value:=trim(listview1.Selected.Caption);
           Adoquery1.Parameters.ParamByName('aid').Value:=trim(listview3.Items[i].SubItems.Strings[0]);
           Adoquery1.ExecSQL;
           except
             application.MessageBox('更新数据库失败，请检查后再试！','错误',MB_OK+MB_ICONSTOP);
             exit;
           end;
           //------------------------------------------
           listitem:=listview2.Items.Add;
           listitem.Caption:=trim(listview3.Items[i].Caption);
           listitem.SubItems.Add(trim(listview3.Items[i].SubItems.Strings[0]));
         end;
    //----------------------------------------------------
    for i:=listview3.Items.Count-1 downto 0 do
         if listview3.Items[i].Checked=true then
            listview3.Items[i].Delete;
end;

end.