База данных Контроль Сессионной успеваемости студентов

ShowModal;
end;

procedure Tmain.N12Click(Sender: TObject);
begin
dm1.ADOQDisciplines.SQL.Text:='select * from disciplines where type="курсовая"';
dm1.ADOQDisciplines.Active:=false;
dm1.ADOQDisciplines.Active:=true;
ctype :=3;
accreate.Caption:='Создать ведомость по курсовой';
accreate.ShowModal;
end;

procedure Tmain.N15Click(Sender: TObject);
begin
dm1.ADOQDisciplines.SQL.Text:='select * from disciplines where type="курсовая"';
dm1.ADOQDisciplines.Active:=false;
dm1.ADOQDisciplines.Active:=true;
ctype :=3;
acopen.ShowModal;
end;

procedure Tmain.N16Click(Sender: TObject);
begin
report.ShowModal;
end;

end.
Файл UAcCreate.pas
unit UAcCreate;

interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs,udm1, StdCtrls, DBCtrls;

type
TAcCreate = class(TForm)
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Button1: TButton;
DBLCB1: TDBLookupComboBox;
DBLCB2: TDBLookupComboBox;
DBLCB3: TDBLookupComboBox;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
AcCreate: TAcCreate;
group_id,dis_id,teacher_id : integer;
group_name,dis_name,teacher_fam : string;
students_count,mode:integer;
// 1 - создание ведомости
// 2 - открытие ведомости
ctype:integer;
// 1 - зачёт
// 2 - экзамен
// 3 - курсовая
implementation

uses UAcForm;

{$R *.dfm}

procedure TAcCreate.Button1Click(Sender: TObject);
begin
if length(DBLCB1.Text)=0 then
showmessage('Не указана группа')
else if length(DBLCB2.Text)=0 then
showmessage('Не указан предмет')
else if length(DBLCB3.Text)=0 then
showmessage('Не указан преподаватель')
else
begin
mode:=1;
group_id:=DBLCB1.KeyValue;
dis_id:= DBLCB2.KeyValue;
teacher_id:=DBLCB3.KeyValue;
group_name:=DBLCB1.Text;
dis_name :=DBLCB2.Text;
teacher_fam:=DBLCB3.Text;

dm1.ADOQCheck.SQL.Text:='select * from ac where (groupid = '+inttostr(group_id)+
') and (disciplineid = ' +inttostr(dis_id)+
') and (teacherid = ' +inttostr(teacher_id)+
')';
dm1.ADOQCheck.Active:=false;
dm1.ADOQCheck.Active:=true;

if dm1.ADOQCheck.RecordCount > 0 then
begin
case ctype of
1:showmessage('Зачётная ведомость уже сформирована. Для просмотра выберите пункт "Просмотреть ведомости"');
2:showmessage('Экзаменационная ведомость уже сформирована. Для просмотра выберите пункт "Просмотреть ведомости"');
3:showmessage('Ведомость уже сформирована. Для просмотра выберите пункт "Просмотреть ведомости"');
end;
end
else
begin
case ctype of
1:acform.Caption:='Зачётная ведомость';
2:acform.Caption:='Экзаменационная ведомость';
3:acform.Caption:='Ведомость по курсовым работам';
end;
acform.ShowModal;
end;
end;
end;
end.
Файл UAcForm.pas
unit UAcForm;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs,udm1, Grids, StdCtrls,uaccreate;
type
Tacform = class(TForm)
Label1: TLabel;
SG: TStringGrid;
Label2: TLabel;
Label3: TLabel;
teacher: TLabel;
discipline: TLabel;
group: TLabel;
Button1: TButton;
Label4: TLabel;
procedure FormShow(Sender: TObject);
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
acform: Tacform;
ac_id: integer;
implementation

uses DB;
{$R *.dfm}

procedure Tacform.FormShow(Sender: TObject);
var
i, stud_id:integer;
begin
if mode = 1 then
begin
// Формирование шапки ведомости
dm1.ADOQCheck.Insert;
dm1.ADOQCheck.FieldByName('teacherid').AsInteger:= teacher_id;
dm1.ADOQCheck.FieldByName('disciplineid').AsInteger:= dis_id;
dm1.ADOQCheck.FieldByName('groupid').AsInteger:= group_id;
dm1.ADOQCheck.Post;

dm1.ADOQCheck.Active:=false;
dm1.ADOQCheck.Active:=true;
end;

dm1.ADOQCheck.SQL.Text:='select id from ac where (groupid = '+inttostr(group_id)+
') and (disciplineid = ' +inttostr(dis_id)+
') and (teacherid = ' +inttostr(teacher_id)+
')';
dm1.ADOQCheck.Active:=false;
dm1.ADOQCheck.Active:=true;

ac_id:=dm1.ADOQCheck.fieldbyname('id').AsInteger;

if mode = 1 then
begin
dm1.ADOQCheck.SQL.Text:='select * from academic';
dm1.ADOQCheck.Active:=false;
dm1.ADOQCheck.Active:=true;

dm1.ADOQAcCreate.SQL.Text:='select * from students where groupid = '+inttostr(group_id);
dm1.ADOQAcCreate.Active:=false;
dm1.ADOQAcCreate.Active:=true;

students_count:=dm1.ADOQAcCreate.RecordCount;
// Формирование списка
dm1.ADOQAcCreate.First;
for i:=1 to students_count do
begin
stud_id:= dm1.ADOQAcCreate.fieldbyname('id').AsInteger;

dm1.ADOQCheck.Insert;
dm1.ADOQCheck.FieldByName('studentid').AsInteger:= stud_id;
dm1.ADOQCheck.FieldByName('acid').AsInteger:= ac_id;
dm1.ADOQCheck.Post;

dm1.ADOQAcCreate.Next;
end;
dm1.ADOQCheck.Active:=false;
dm1.ADOQCheck.Active:=true;
end;

// Вывод списка
dm1.ADOQAcCreate.SQL.Text:='select s.fam, a.mark '+
'from students s, academic a '+
'where (a.acid ='+inttostr(ac_id)+') and '+
'(s.id=a.studentid)';
dm1.ADOQAcCreate.Active:=false;
dm1.ADOQAcCreate.Active:=true;

students_count:= dm1.ADOQAcCreate.RecordCount;

dm1.ADOQAcCreate.First;
sg.RowCount:= students_count + 1;
group.Caption:=group_name;
discipline.Caption:=dis_name;
teacher.Caption:=teacher_fam;
sg.Cells[0,0]:='Фамилия';
sg.Cells[1,0]:='Оценка';
for i:=1 to students_count do
begin
sg.Cells[0,i]:=dm1.ADOQAcCreate.fieldbyname('fam').AsString;
sg.Cells[1,i]:=dm1.ADOQAcCreate.fieldbyname('mark').AsString;
dm1.ADOQAcCreate.Next;
end;

end;

procedure Tacform.Button1Click(Sender: TObject);
var
i:integer;
begin
dm1.ADOQAcCreate.SQL.Text:='select * '+
'from academic a '+
'where a.acid ='+inttostr(ac_id);
dm1.ADOQAcCreate.Active:=false;
dm1.ADOQAcCreate.Active:=true;
dm1.ADOQAcCreate.First;
for i:=1 to students_count do
begin
dm1.ADOQAcCreate.edit;
dm1.ADOQAcCreate.fieldbyname('mark').AsString:=sg.Cells[1,i];
dm1.ADOQAcCreate.Post;
dm1.ADOQAcCreate.Next;
end;
dm1.ADOQAcCreate.Active:=false;
dm1.ADOQAcCreate.Active:=true;
end;
end.
Файл UAcOpen.pas
unit UAcOpen;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, udm1, StdCtrls, DBCtrls, uaccreate;

type
TAcOpen = class(TForm)
Label1: TLabel;
Label2: TLabel;
Button1: TButton;
DBLCB1: TDBLookupComboBox;
DBLCB2: TDBLookupComboBox;
Label3: TLabel;
DBLCB3: TDBLookupComboBox;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
AcOpen: TAcOpen;

implementation

uses UAcForm;

{$R *.dfm}

procedure TAcOpen.Button1Click(Sender: TObject);
begin
if length(DBLCB1.Text)=0 then
showmessage('Не указана группа')
else if length(DBLCB2.Text)=0 then
showmessage('Не указан предмет')
else if length(DBLCB3.Text)=0 then
showmessage('Не указан преподаватель')
else
begin
mode:=2;
group_id:=DBLCB1.KeyValue;
dis_id:= DBLCB2.KeyValue;
teacher_id:= DBLCB3.KeyValue;
group_name:=DBLCB1.Text;
dis_name :=DBLCB2.Text;
teacher_fam:=DBLCB3.Text;

dm1.ADOQCheck.SQL.Text:='select * from ac where (groupid = '+inttostr(group_id)+
') and (disciplineid = ' +inttostr(dis_id)+
') and (teacherid = ' +inttostr(teacher_id)+
')';
dm1.ADOQCheck.Active:=false;
dm1.ADOQCheck.Active:=true;

if dm1.ADOQCheck.RecordCount > 0 then
acform.ShowModal
else
begin
case ctype of
1:showmessage('Зачетная ведомость ещё не сформирована. Для создания выберите пункт "Создать ведомости"');
2:showmessage('Экзаменационная ведомость ещё не сформирована. Для создания выберите пункт "Создать ведомости"');
3:showmessage('Ведомость ещё не сформирована. Для создания выберите пункт "Создать ведомости"');
end;
end;
end;
end;

end.
Файл UDisciplines.pas

unit UDisciplines;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, udm1, ExtCtrls, DBCtrls, Grids, DBGrids;

type
Tdisciplines = class(TForm)
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
private
{ Private declarations }
public
{ Public declarations }
end;

var
disciplines: Tdisciplines;

implementation

{$R *.dfm}

end.
Файл UDM1.pas

unit UDM1;

interface

uses
SysUtils, Classes, DB, ADODB;

type
TDM1 = class(TDataModule)
ADOQTeachers: TADOQuery;
DSTeachers: TDataSource;
ADOConnection1: TADOConnection;
DADisciplines: TDataSource;
ADOQDisciplines: TADOQuery;
ADOQStudents: TADOQuery;
ADOQGroups: TADOQuery;
ADOQFaculty: TADOQuery;
ADOQAcadem: TADOQuery;
DSStudents: TDataSource;
DSGroups: TDataSource;
DSFaculty: TDataSource;
DSAcadem: TDataSource;
ADOQGroupsid: TIntegerField;
ADOQGroupsname: TWideStringField;
ADOQGroupsfacultyid: TIntegerField;
ADOQStudentsid: TAutoIncField;
ADOQStudentsfam: TWideStringField;
ADOQStudentsname: TWideStringField;
ADOQStudentssurname: TWideStringField;
ADOQStudentszachetid: TIntegerField;
ADOQStudentsgroupid: TIntegerField;
ADOQCheck: TADOQuery;
DSCheck: TDataSource;
ADOQAcCreate: TADOQuery;
DSAcCreate: TDataSource;
ADOQDisciplinesid: TAutoIncField;
ADOQDisciplinesname: TWideStringField;
ADOQDisciplinestype: TWideStringField;
ADOQReport: TADOQuery;
DSReport: TDataSource;
ADOQRepstud: TADOQuery;
AutoIncField1: TAutoIncField;
WideStringField1: TWideStringField;
WideStringField2: TWideStringField;
WideStringField3: TWideStringField;
IntegerField1: TIntegerField;
IntegerField2: TIntegerField;
DSRepstud: TDataSource;
private
{ Private declarations }
public
{ Public declarations }
end;

var
DM1: TDM1;

implementation

{$R *.dfm}

end.
Файл UFaculty.pas

unit UFaculty;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, udm1, Grids, DBGrids, ExtCtrls, DBCtrls;

type
Tfaculty = class(TForm)
DBNavigator1: TDBNavigator;
DBGrid1: TDBGrid;
private
{ Private declarations }
public
{ Public declarations }
end;

var
faculty: Tfaculty;

implementation

{$R *.dfm}

end.
Файл UGroups.pas
unit UGroups;
interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, udm1, ExtCtrls, DBCtrls, Grids, DBGrids, StdCtrls;

type
Tgroups = class(TForm)
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
Label1: TLabel;
DBLookupComboBox1: TDBLookupComboBox;
procedure DBLookupComboBox1Click(Sender: TObject);
procedure DBNavigator1Click(Sender: TObject; Button: TNavigateBtn);
private
{ Private declarations }
public
{ Public declarations }
end;

var
groups: Tgroups;

implementation
uses ADODB;
{$R *.dfm}

procedure Tgroups.DBLookupComboBox1Click(Sender: TObject);
begin
dm1.ADOQGroups.SQL.Text:= 'select * from groups where facultyid ='+ vartoStr(DBLookupComboBox1.KeyValue);
dm1.ADOQGroups.Active:= false;
dm1.ADOQGroups.Active:= true;
end;

procedure Tgroups.DBNavigator1Click(Sender: TObject; Button: TNavigateBtn);
begin
if (Button=nbInsert) then
dm1.ADOQGroups.FieldByName('facultyid').Value:= vartoStr(DBLookupComboBox1.KeyValue);
end;

end.
Файл UReport.pas
unit UReport;

interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs,udm1, Grids, DBGrids, DBCtrls, StdCtrls;
type
Treport = class(TForm)
Label1: TLabel;
Label2: TLabel;
DBLCB1: TDBLookupComboBox;
DBLCB2: TDBLookupComboBox;
DBGrid1: TDBGrid;
procedure DBLCB1Click(Sender: TObject);
procedure DBLCB2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
report: Treport;

implementation
{$R *.dfm}

procedure Treport.DBLCB1Click(Sender: TObject);
begin
dm1.ADOQRepstud.SQL.Text:= 'select * from students where groupid ='+ vartoStr(DBLCB1.KeyValue);
dm1.ADOQRepstud.Active:= false;
dm1.ADOQRepstud.Active:= true;
end;

procedure Treport.DBLCB2Click(Sender: TObject);
begin
dm1.ADOQReport.SQL.Text:= 'select t.fam,d.type,d.name,a.mark,a.studentid from academic a,disciplines d, teachers t, ac '+
'where (a.acid=ac.id) and (ac.disciplineid=d.id) and (ac.teacherid=t.id) and (a.studentid ='+ vartoStr(DBLCB2.KeyValue)+')';
dm1.ADOQReport.Active:= false;
dm1.ADOQReport.Active:= true;
end;
end.
Файл UStudents.pas
unit UStudents;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, udm1, ExtCtrls, DBCtrls, Grids, DBGrids, StdCtrls;

type
Tstudents = class(TForm)
Label1: TLabel;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
DBLookupComboBox1: TDBLookupComboBox;
procedure DBLookupComboBox1Click(Sender: TObject);
procedure DBNavigator1Click(Sender: TObject; Button: TNavigateBtn);
private
{ Private declarations }
public
{ Public declarations }
end;

var
students: Tstudents;

implementation

{$R *.dfm}

procedure Tstudents.DBLookupComboBox1Click(Sender: TObject);
begin
dm1.ADOQStudents.SQL.Text:= 'select * from students where students.groupid ='+ vartoStr(DBLookupComboBox1.KeyValue);
dm1.ADOQStudents.Active:= false;
dm1.ADOQStudents.Active:= true;
end;

procedure Tstudents.DBNavigator1Click(Sender: TObject;
Button: TNavigateBtn);
begin
if (Button=nbInsert) then
dm1.ADOQGroups.FieldByName('groupid').Value:= vartoStr(DBLookupComboBox1.KeyValue);
end;
end.
Файл UTeachers
unit UTeachers;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, udm1, ExtCtrls, DBCtrls, Grids, DBGrids;

type
Tteachers = class(TForm)
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
private
{ Private declarations }
public
{ Public declarations }
end;

var
teachers: Tteachers;

implementation

{$R *.dfm}

end.