unit Unit3;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, DB, ADODB, StdCtrls, Buttons;

type
  TForm3 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Edit1: TEdit;
    Edit2: TEdit;
    Edit3: TEdit;
    ADOQuery2: TADOQuery;
    ADOQuery3: TADOQuery;
    DataSource2: TDataSource;
    DBGrid2: TDBGrid;
    Edit4: TEdit;
    Edit5: TEdit;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    ADOCommand1: TADOCommand;
    GroupBox1: TGroupBox;
    GroupBox2: TGroupBox;
    GroupBox3: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    BitBtn3: TBitBtn;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Button1: TButton;
    procedure Edit1KeyPress(Sender: TObject; var Key: Char);
    procedure FormCreate(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure DataSource1DataChange(Sender: TObject; Field: TField);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure DataSource2DataChange(Sender: TObject; Field: TField);
    procedure DBGrid2CellClick(Column: TColumn);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure listcourse(id:string);
  end;

var
  Form3: TForm3;
   id:string;//選課學生的學號
   flag,flag2:integer;
implementation

{$R *.dfm}

procedure TForm3.Edit1KeyPress(Sender: TObject; var Key: Char);
begin
  if not (key in ['0'..'9',#8,#13]) then //該文本框中只能輸入數字
  begin
   key:=#0;
   exit;
  end;
//回車輸入，將在籍學生的信息顯示在文本框中
if key=#13 then
 begin
  id:=trim(edit1.Text);
  //清除上一次的數據
  edit2.Clear;
  edit3.Clear;
  adoquery2.Close;

  adoquery3.Close;
  adoquery3.SQL.Clear;
  adoquery3.SQL.Add('select 姓名,年級 from 學生信息表 where 學號='''+id+'''');

  adoquery3.Open;
  if adoquery3.RecordCount<>0 then
  begin
  edit2.Text:=trim(adoquery3.fieldbyname('姓名').AsString);
    edit3.Text:=trim(adoquery3.fieldbyname('年級').AsString);
  listcourse(id); //列出已選課程
  //允許開始選課

  dbgrid2.Columns[0].Width:=100;
   dbgrid2.Columns[1].Width:=100;
    dbgrid2.Columns[2].Width:=100;
    dbgrid2.Columns[3].Width:=80;
    dbgrid2.Columns[4].Width:=80;
   dbgrid2.Columns[5].Width:=80;
 
  flag:=1;
  flag2:=1;

  end
  else
   begin
   //只有有這樣的學號才能選課
   showmessage('該生非在籍學生，不能選課');
   bitbtn2.Enabled:=false;

   flag:=0;
  // bitbtn4.Enabled:=false;
   id:='';
    flag2:=0;
   end;
 end;


end;
   //--------顯示學生已經選的課程----------
procedure TForm3.listcourse(id: string);
begin
  //將符合選課條件的學生已經選過的課程列在表中
   adoquery2.Active:=false;
     adoquery2.Active:=true;
  adoquery2.Close;
  adoquery2.SQL.Clear;
  adoquery2.SQL.Add('select a.學號, a.課程號,b.課程名,b.開課系,c.上課時間天,c.上課時間節,c.上課地點');
  adoquery2.SQL.Add('from 選課表 a,課程信息 b,課程表 c');
  adoquery2.SQL.Add('where (a.學號='''+id+''')and(a.課程號=c.課程號)and(b.課程號=c.課程號)');
  adoquery2.Open;

end;
procedure TForm3.FormCreate(Sender: TObject);
begin
 dbgrid1.Columns[1].Width:=80;
    dbgrid1.Columns[2].Width:=60;
    dbgrid1.Columns[3].Width:=60;
    dbgrid1.Columns[4].Width:=60;
   dbgrid2.Columns[0].Width:=100;
   dbgrid2.Columns[1].Width:=100;
    dbgrid2.Columns[2].Width:=100;
    dbgrid2.Columns[3].Width:=80;
    dbgrid2.Columns[4].Width:=80;
   dbgrid2.Columns[5].Width:=80;

end;

procedure TForm3.BitBtn2Click(Sender: TObject);

var
num:string;
day,section:integer;
begin
//未選擇課程時，不允許提交
if adoquery1.RecordCount=0 then
 begin
  showmessage('請先選擇一門課程');
  exit;
 end;
num:=trim(dbgrid1.Fields[0].Text);//保存課程號

//判斷所選的課程是否已選和是否與其他已經選的課程有時間沖突
adoquery3.Close;
adoquery3.SQL.Clear;
adoquery3.SQL.Add('select 上課時間天,上課時間節 from 課程表');
adoquery3.SQL.Add('where 課程號='''+dbgrid1.Fields[0].Text+'''');
adoquery3.Open;

while not adoquery3.Eof do
 begin
  day:=adoquery1.fieldbyname('上課時間天').AsInteger;
  section:=adoquery1.fieldbyname('上課時間節').AsInteger;
  adoquery2.First;
  while not adoquery2.Eof do
   begin
   //判斷課程是否已選
   if (trim(adoquery2.fieldbyname('課程號').AsString)=num) then
    begin
     showmessage('所選的課程：'+trim(adoquery2.fieldbyname('課程名').AsString)+' 已在課表中，請選擇其他課程');
     exit;
    end;
   //判斷課程是否與已選課程有時間沖突
   if (adoquery2.fieldbyname('上課時間天').AsInteger=day)and(adoquery2.fieldbyname('上課時間節').AsInteger=section) then
    begin
     showmessage('所選課程的上課時間與已選的 '+trim(adoquery2.fieldbyname('課程名').AsString)+' 沖突!');
     exit;
    end;
   adoquery2.Next;
   end;
  adoquery3.Next;
 end;

//選課


adocommand1.CommandText:='insert into 選課表([學號],[課程號]) values('''+id+''','''+trim(dbgrid1.Fields[0].Text)+''')';
adocommand1.Execute;
listcourse(id);//刷新數據表
 dbgrid2.Columns[0].Width:=100;
   dbgrid2.Columns[1].Width:=100;
    dbgrid2.Columns[2].Width:=100;
    dbgrid2.Columns[3].Width:=80;
    dbgrid2.Columns[4].Width:=80;
   dbgrid2.Columns[5].Width:=80;
end;

procedure TForm3.DataSource1DataChange(Sender: TObject; Field: TField);
begin
   edit4.Text:= dbgrid1.Fields[0].Text;
   edit5.Text:= dbgrid1.Fields[1].Text;
end;

procedure TForm3.BitBtn3Click(Sender: TObject);

begin
if application.MessageBox('確實刪除所選課程？','確定刪除',MB_OKCANCEL)=IDOK then
 begin
  adocommand1.CommandText:='delete from 選課表 where (學號='''+trim(dbgrid2.Fields[0].Text)+''')and(課程號='''+trim(dbgrid2.Fields[1].Text)+''')';
  adocommand1.Execute;
  listcourse(id);//刷現列表
 end;

end;

procedure TForm3.BitBtn1Click(Sender: TObject);

var
sql:string;
begin
sql:='';
if trim(edit4.Text)<>'' then
 sql:=sql+'課程號='''+trim(edit4.Text)+''' AND ';
if trim(edit5.Text)<>'' then
 sql:=sql+'課程名='''+trim(edit5.Text)+''' AND ';

sql:=copy(sql,1,length(sql)-4);
adoquery1.Filtered:=false;
adoquery1.Active:=false;
adoquery1.Filter:=sql;
adoquery1.Active:=true;
adoquery1.Filtered:=true;


end;

procedure TForm3.DataSource2DataChange(Sender: TObject; Field: TField);
begin
label6.Caption:=inttostr(ADOQuery2.RecordCount);
end;

procedure TForm3.DBGrid2CellClick(Column: TColumn);
begin
if flag=1 then  bitbtn3.Enabled:=true
else  bitbtn3.Enabled:=false;
bitbtn2.Enabled:=false;
end;

procedure TForm3.DBGrid1CellClick(Column: TColumn);
begin
    bitbtn3.Enabled:=false;
  if flag2=1 then  bitbtn2.Enabled:=true
else  bitbtn2.Enabled:=false;
end;

procedure TForm3.Button1Click(Sender: TObject);
begin
close;
end;

end.