unit Unit6;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, StdCtrls, Grids, DBGrids, DB, ADODB, ExtCtrls,
  DBActns, ActnList, Buttons, XPMan, DBCtrls, Menus;

type
  TForm6 = class(TForm)
    StatusBar1: TStatusBar;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    Label13: TLabel;
    Edit7: TEdit;
    Edit8: TEdit;
    Edit9: TEdit;
    Label14: TLabel;
    Edit10: TEdit;
    Button2: TButton;
    Shape1: TShape;
    Label15: TLabel;
    Label16: TLabel;
    Edit11: TEdit;
    Label17: TLabel;
    Edit12: TEdit;
    Label18: TLabel;
    Label19: TLabel;
    Edit13: TEdit;
    Timer1: TTimer;
    XPManifest1: TXPManifest;
    BitBtn4: TBitBtn;
    BitBtn5: TBitBtn;
    ADOQuery4: TADOQuery;
    DBMemo1: TDBMemo;
    DataSource3: TDataSource;
    ADOQuery5: TADOQuery;
    ADOQuery6: TADOQuery;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    GroupBox1: TGroupBox;
    DBGrid1: TDBGrid;
    Label1: TLabel;
    ComboBox1: TComboBox;
    ActionList1: TActionList;
    DataSetFirst1: TDataSetFirst;
    DataSetPrior1: TDataSetPrior;
    DataSetNext1: TDataSetNext;
    DataSetLast1: TDataSetLast;
    DataSetInsert1: TDataSetInsert;
    DataSetDelete1: TDataSetDelete;
    DataSetRefresh1: TDataSetRefresh;
    SpeedButton5: TSpeedButton;
    SpeedButton3: TSpeedButton;
    Panel1: TPanel;
    Button3: TButton;
    Label2: TLabel;
    ADOQuery2: TADOQuery;
    DataSource2: TDataSource;
    DBGrid2: TDBGrid;
    Button4: TButton;
    RadioGroup1: TRadioGroup;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    Edit1: TEdit;
    Edit2: TEdit;
    BitBtn1: TBitBtn;
    ADOQuery7: TADOQuery;
    ADOQuery3: TADOQuery;
    Shape2: TShape;
    ADOQuery8: TADOQuery;
    MainMenu1: TMainMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    N3: TMenuItem;
    S1: TMenuItem;
    T1: TMenuItem;
    R1: TMenuItem;
    H1: TMenuItem;
    N4: TMenuItem;
    SpeedButton4: TSpeedButton;
    SpeedButton6: TSpeedButton;
    N5: TMenuItem;
    procedure Timer1Timer(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure ComboBox1DropDown(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure N3Click(Sender: TObject);
    procedure T1Click(Sender: TObject);
    procedure S1Click(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure N4Click(Sender: TObject);
    procedure SpeedButton4Click(Sender: TObject);
    procedure SpeedButton6Click(Sender: TObject);
    procedure N5Click(Sender: TObject);

  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form6: TForm6;

implementation

uses Unit2, Unit3, Unit5, Unit4, Unit1, Unit9;

{$R *.dfm}

procedure TForm6.Timer1Timer(Sender: TObject);
begin
statusbar1.Panels[2].text:=timetostr(time);
end;

procedure TForm6.FormCreate(Sender: TObject);
begin
statusbar1.Panels[1].text:=datetostr(now);
end;

procedure TForm6.ComboBox1DropDown(Sender: TObject);
var
a:string;       //定義字符串變量a
i:integer;      //定義整型變量i
begin

if combobox1.Items.Count>1 then      //如果combobox2里面有數(shù)據(jù)
 combobox1.Items.Clear;      //清空combobox2里面的數(shù)據(jù)
        adoquery7.close;
        adoquery7.Open;      //打開adoquery2（前提要設(shè)置連接和編輯SQL語言）
        adoquery7.First;      //指向第一條記錄（adoquery2查詢結(jié)果是表）
        for  i:=1   to  adoquery7.RecordCount  do      //定義循環(huán)變量i（RecordCount指記錄數(shù)）
        begin
        a:=adoquery7.FieldValues['sno'];      //讀取指定記錄的指定列（cname列）的值，存放在a
        combobox1.Items.Add(a);      //把a(bǔ)添加到combobox2中
        adoquery7.Next;      //指向下一條記錄
        end;
//循環(huán)體表明讀取查詢到的所有課程名，并把結(jié)果存放在combobox2中

end;
procedure TForm6.Button1Click(Sender: TObject);
begin
form2.show;
form6.close;
end;

procedure TForm6.Button3Click(Sender: TObject);
begin
if checkbox1.Checked and not checkbox2.Checked then
begin
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('select * from 歌手成績 where sno='''+trim(edit1.text)+'''');
adoquery1.open;
end
else if not checkbox1.Checked and checkbox2.Checked then
begin
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('select * from 歌手成績 where tno='''+trim(edit2.text)+'''');
adoquery1.open;
end
else if checkbox1.Checked and checkbox2.Checked then
begin
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('select * from 歌手成績 where sno='''+trim(edit1.text)+''''+'and tno='''+trim(edit2.text)+'''');
adoquery1.open;
end
else
showmessage('請選擇！');
end;


procedure TForm6.Button2Click(Sender: TObject);
begin
adoquery5.Close;
adoquery5.SQL.Clear;
adoquery5.SQL.Add('select sname,sage,song from 歌手信息 where sno='''+trim(edit10.text)+'''');
adoquery5.open;
adoquery4.Close;
adoquery4.SQL.Clear;
adoquery4.SQL.Add('select * from 歌手信息 where sno='''+trim(edit10.text)+'''');
adoquery4.open;
adoquery2.Close;
adoquery2.SQL.Clear;
adoquery2.SQL.Add('select 歌手信息.sno as 歌手號,sname as 歌手名,song as 參賽曲目,grade as 最后得分 from 歌手信息,歌手最后成績 where 歌手信息.sno=歌手最后成績.sno and 歌手信息.sno='''+trim(edit10.text)+'''');
adoquery2.open;
dbmemo1.Visible:=true;
try
edit11.Text:=adoquery5.fieldvalues['sname'];
edit12.text:=inttostr(adoquery5.FieldValues['sage']);
edit13.text:=adoquery5.FieldValues['song'];
except
edit11.Text:='';
edit12.text:='';
edit13.text:='';
end;

end;


procedure TForm6.BitBtn4Click(Sender: TObject);
begin
adoquery6.Close;
adoquery6.sql.Clear;
adoquery6.SQL.Add('select min(grade) as b from 歌手成績 where sno='''+trim(combobox1.text)+''''+'group by sno');
adoquery6.Open;
try
edit8.Text:=inttostr(adoquery6.FieldValues['b']);
except
edit8.Text:='';
end;
end;

procedure TForm6.ComboBox1Change(Sender: TObject);
begin
if combobox1.text<>'' then
begin
label9.Caption:=combobox1.Text;
end;
adoquery1.Close;
adoquery1.sql.Clear;
adoquery1.SQL.Add('select * from 歌手成績 where sno='''+trim(combobox1.text)+'''');
adoquery1.Open;
edit7.Clear;
edit8.Clear;
edit9.Clear;
end;

procedure TForm6.Button4Click(Sender: TObject);
begin
adoquery2.Close;
adoquery2.SQL.Clear;
adoquery2.sql.Add('select 歌手信息.sno as 歌手號,sname as 歌手名,song as 參賽曲目,grade as 最后得分 from 歌手信息,歌手最后成績 where 歌手信息.sno=歌手最后成績.sno order by grade desc');
adoquery2.Open;
end;

procedure TForm6.Button5Click(Sender: TObject);
begin
form2.Show;
form6.Close;
end;

procedure TForm6.BitBtn1Click(Sender: TObject);
begin
adoquery6.Close;
adoquery6.sql.Clear;
adoquery6.SQL.Add('select max(grade) as a from 歌手成績 where sno='''+trim(combobox1.text)+'''');
adoquery6.Open;
edit7.Text:=inttostr(adoquery6.FieldValues['a']);
end;

procedure TForm6.BitBtn5Click(Sender: TObject);
var
a,b,c:integer;
d:real;
begin
a:=strtoint(edit7.text);
b:=strtoint(edit8.text);
adoquery3.Close;
adoquery3.SQL.Clear;
adoquery3.SQL.Add('select sum(grade) as s from 歌手成績 where sno='''+trim(combobox1.text)+'''');
adoquery3.Open;
try
c:=adoquery3.FieldValues['s'];
except
c:=0;
end;
d:=(c-a-b)/4.0;
edit9.Text:=floattostr(d);
adoquery8.Close;
adoquery8.SQL.Clear;
adoquery8.sql.Add('select sno,grade from 歌手最后成績 where sno='''+trim(combobox1.text)+'''');
adoquery8.open;
with adoquery8 do
if adoquery8.FieldValues['grade']=null then
begin
edit;
adoquery8.FieldValues['grade']:=strtofloat(edit9.text);
post;
end;
adoquery2.Close;
adoquery2.SQL.Clear;
adoquery2.sql.Add('select 歌手信息.sno as 歌手號,sname as 歌手名,song as 參賽曲目,grade as 最后得分 from 歌手信息,歌手最后成績 where 歌手信息.sno=歌手最后成績.sno and grade is not null order by grade desc');
adoquery2.Open;

end;



procedure TForm6.N3Click(Sender: TObject);
begin
application.Terminate;
end;

procedure TForm6.T1Click(Sender: TObject);
begin
form5.show;
form6.Close;
end;

procedure TForm6.S1Click(Sender: TObject);
begin
form4.show;
form6.Close;
end;

procedure TForm6.N1Click(Sender: TObject);
begin
form1.show;
form1.Edit1.Clear;
form1.Edit2.Clear;
form6.Close;
end;

procedure TForm6.N2Click(Sender: TObject);
begin
form1.show;
form1.Edit1.Clear;
form1.Edit2.Clear;
form6.Close;
end;

procedure TForm6.N4Click(Sender: TObject);
begin
form3.Show;
end;

procedure TForm6.SpeedButton4Click(Sender: TObject);
begin
form2.Show;
form6.Close;
end;

procedure TForm6.SpeedButton6Click(Sender: TObject);
begin
form2.Show;
form6.Close;
end;

procedure TForm6.N5Click(Sender: TObject);
begin
form9.show;
end;

end.