unit classu;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ExtCtrls, DBCtrls, StdCtrls, Mask, DBTables, Grids, DBGrids,
  ADODB;

type
  TF_class = class(TForm)
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label8: TLabel;
    Btnadd: TButton;
    Btndlt: TButton;
    ADOQryClass: TADOQuery;
    ADOQryClassclassno: TStringField;
    ADOQryClassdepart: TStringField;
    ADOQryClasstype: TStringField;
    ADOQryClassenteryear: TIntegerField;
    ADOQryClassspecialty: TStringField;
    ADOQryClassname: TStringField;
    Edclassno: TEdit;
    Edspec: TEdit;
    Cmbdepart: TComboBox;
    Cmbdegree: TComboBox;
    Cmbyear: TComboBox;
    Cmbteacher: TComboBox;
    ADOQe: TADOQuery;
    ADOQe2: TADOQuery;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure CmbdepartChange(Sender: TObject);
    procedure BtnaddClick(Sender: TObject);
    procedure BtndltClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  F_class: TF_class;

implementation

uses main;

{$R *.dfm}

procedure TF_class.FormClose(Sender: TObject; var Action: TCloseAction);
begin
     Action := caFree;
end;

procedure TF_class.CmbdepartChange(Sender: TObject);
begin
    cmbteacher.Items.Clear ;
    adoqe.SQL.Clear ;
    adoqe.sql.Add('select teacherno,name from teacher where depart='''+Cmbdepart.Text +''' ');
    adoqe.Open;

    while not adoqe.Eof do
    begin
    cmbteacher.Items.Add(adoqe.fieldbyname('teacherno').AsString +':'+adoqe.fieldbyname('name').AsString );
    adoqe.Next ;
    end;

end;

procedure TF_class.BtnaddClick(Sender: TObject);
var teacherno:string;
    cnt:integer;
begin
    teacherno:=copy(cmbteacher.Text ,0,4);
    adoqe.sql.clear;
    adoqe.sql.add('select count(*) as cnt from class where classno='''+edclassno.Text +''' ');
    adoqe.open;
    cnt:= adoqe.fieldbyname('cnt').asinteger;
    if  cnt>0 then
    begin
    showmessage('對不起，這個班級號已經存在，請您重新錄入！');
    exit;
    end
    else
    begin
    mainform.adoconnection1.begintrans;
     try
     adoqe2.sql.clear;
     adoqe2.SQL.Add('insert into class (classno,depart,type,enteryear,teacherno,specialty) values ');
     adoqe2.SQL.Add(' ('''+edclassno.text+''','''+cmbdepart.text+''','''+cmbdegree.text+''','+cmbyear.text+','''+teacherno+''','''+edspec.text+''')');
     adoqe2.ExecSQL ;
     mainform.adoconnection1.committrans;
     adoqryclass.active:=false ;
     adoqryclass.active:=true ;
     except
     mainform.adoconnection1.rollbacktrans;
     end;
    end;


end;

procedure TF_class.BtndltClick(Sender: TObject);
var classno:string;
    cnt:integer;
begin
    classno:=adoqryclass.fieldbyname('classno').AsString  ;
    adoqe.sql.clear;
    adoqe.sql.add('select count(*) as cnt from student where classno='''+classno+''' ');
    adoqe.open;
    cnt:= adoqe.fieldbyname('cnt').asinteger;
    if  cnt>0 then
    if MessageDlg('這個班級下面還存在'+inttostr(cnt)+'條學生信息，是否同時刪除該班級所有學生的信息?',
    mtConfirmation, [mbYes, mbNo], 0) = mrno then
    exit;


    mainform.adoconnection1.begintrans;
     try
     adoqe2.sql.clear;
     adoqe2.SQL.Add('delete class where classno='''+classno+''' ');
     adoqe2.ExecSQL ;
     adoqe2.sql.clear;
     adoqe2.SQL.Add('delete student where classno='''+classno+''' ');
     adoqe2.ExecSQL ;
     mainform.adoconnection1.committrans;
     adoqryclass.active:=false ;
     adoqryclass.active:=true ;
     except
     mainform.adoconnection1.rollbacktrans;
     end;

end;

end.