Курсовые работы / ПРИС П _27
.pdfDM.PutevkaQuery.Open; id_put:=dm.PutevkaQuery.Fields[0].AsString; DM.OplataQuery.SQL.Clear;
//формирование запроса
DM.OplataQuery.SQL.Add('select ID_opl,data_opl,stoim_opl from Oplata WHERE (ID_put='+id_put+')');
//исполнение запроса и вывод результата
DM.OPlataQuery.Open; DBGrid1.Columns.Items[0].Title.Caption:='Номер';
DBGrid1.Columns.Items[1].Title.Caption:='Фамилия клиента'; DBGrid1.Columns.Items[2].Title.Caption:='Серия и номер паспорта'; DBGrid1.Columns.Items[3].Title.Caption:='Название маршрута'; DBGrid1.Columns.Items[4].Title.Caption:='Стоимость'; DBGrid1.Columns.Items[5].Title.Caption:='Дата оплаты';
DBGrid2.Columns.Items[0].Title.Caption:='Дата оплаты туроператору';
DBGrid2.Columns.Items[1].Title.Caption:='Стоимость для турагента'; for i:=0 to DBGrid1.Columns.Count-1 do
ComboBox1.Items.Add(DBGrid1.Columns.Items[i].Title.Caption);
end;
procedure TPutevkaForm.Refresh; begin
DM.PutevkaQuery.Close;
DM.PutevkaQuery.Open;
end;
procedure TPutevkaForm.BitBtn1Click(Sender: TObject); begin
RPutevkaForm:=TRPutevkaForm.Create(self);
RPutevkaform.ComboBox1.Clear;
RPutevkaform.ComboBox2.Clear;
RPutevkaform.ComboBox3.Clear;
dm.temp.Active:=false;
dm.temp.CommandText:='select sernompas_kl from Klient order by sernompas_kl';
dm.temp.Active:=true; while not dm.temp.Eof do begin
RPutevkaform.ComboBox1.Items.Add(dm.temp.Fields[0].asstring);
dm.temp.Next;
end;
dm.temp.Active:=false;
dm.temp.CommandText:='select nazv_mar from Marshrut order by nazv_mar';
dm.temp.Active:=true; while not dm.temp.Eof do begin
RPutevkaform.ComboBox2.Items.Add(dm.temp.Fields[0].asstring);
dm.temp.Next;
end;
tm:=0;
RPutevkaForm.Caption:=RPutevkaForm.Caption+' : '+PutevkaForm.BitBtn1.Caption;
RPutevkaForm.ShowModal;
if RPutevkaform.BitBtn1.ModalResult=mrOK then begin id_put:=dm.PutevkaQuery.Fields[0].AsString;
DM.OplataQuery.SQL.Clear;
//формирование запроса
DM.OplataQuery.SQL.Add('select ID_opl,data_opl,stoim_opl from Oplata WHERE (ID_put='+id_put+')');
//исполнение запроса и вывод результата
DM.OplataQuery.Open;
end;
end;
procedure TPutevkaForm.BitBtn2Click(Sender: TObject); var data:string;
begin
if dm.PutevkaQuery.RecordCount=0 then showmessage('Записи отсутствуют')
else begin
id_put:=dm.PutevkaQuery.Fields[0].AsString; tm:=1;
52
if accessdb.Update('put','Putevka',id_put)=false then showmessage('Запись используется другим пользователем')
else begin
RPutevkaForm:=TRPutevkaForm.Create(self);
RPutevkaform.ComboBox1.Clear;
RPutevkaform.ComboBox2.Clear;
RPutevkaform.ComboBox3.Clear;
dm.temp.Active:=false;
dm.temp.CommandText:='select sernompas_kl from Klient order by sernompas_kl';
dm.temp.Active:=true; while not dm.temp.Eof do begin
RPutevkaform.ComboBox1.Items.Add(dm.temp.Fields[0].asstring);
dm.temp.Next;
end;
dm.temp.Active:=false;
dm.temp.CommandText:='select nazv_mar from Marshrut order by nazv_mar';
dm.temp.Active:=true; while not dm.temp.Eof do begin
RPutevkaform.ComboBox2.Items.Add(dm.temp.Fields[0].asstring);
dm.temp.Next;
end;
rn:=dm.PutevkaQuery.RecNo;
RPutevkaForm.Edit1.Text:=dm.PutevkaQuery.fieldbyname('nomer_put').
AsString;
RPutevkaForm.Combobox1.Text:=dm.PutevkaQuery.fieldbyname('serno mpas_kl').AsString;
RPutevkaForm.Caption:=caption+' : '+bitbtn2.Caption; RPutevkaForm.ShowModal;
end;
end;
end;
procedure TPutevkaForm.BitBtn3Click(Sender: TObject); begin
if DM.PutevkaQuery.RecordCount=0 then showmessage('Записи отсутствуют')
else begin
id_put:=DM.PutevkaQuery.Fields[0].AsString; tm:=1;
if accessdb.Delete('put','Putevka',id_put)=false then showmessage('Запись используется другим пользователем')
else DM.PutevkaQuery.Requery(); end;
end;
procedure TPutevkaForm.DBGrid1CellClick(Column: TColumn); begin
id_put:=dm.PutevkaQuery.Fields[0].AsString; DM.OplataQuery.SQL.Clear;
//формирование запроса
DM.OplataQuery.SQL.Add('select ID_opl,data_opl,stoim_opl from Oplata WHERE (ID_put='+id_put+')');
//исполнение запроса и вывод результата
DM.OplataQuery.Open;
end;
procedure TPutevkaForm.DBGrid1KeyDown(Sender: TObject; var Key: Word;
Shift: TShiftState); begin
id_put:=dm.PutevkaQuery.Fields[0].AsString; DM.OplataQuery.SQL.Clear;
//формирование запроса
DM.OplataQuery.SQL.Add('select ID_opl,data_opl,stoim_opl from Oplata WHERE (ID_put='+id_put+')');
//исполнение запроса и вывод результата
DM.OplataQuery.Open;
end;
procedure TPutevkaForm.DBGrid1KeyUp(Sender: TObject; var Key: Word;
Shift: TShiftState); begin
id_put:=dm.PutevkaQuery.Fields[0].AsString; DM.OplataQuery.SQL.Clear;
//формирование запроса
DM.OplataQuery.SQL.Add('select ID_opl,data_opl,stoim_opl from Oplata WHERE (ID_put='+id_put+')');
//исполнение запроса и вывод результата
DM.OplataQuery.Open;
end;
procedure TPutevkaForm.BitBtn4Click(Sender: TObject); begin
if dm.OplataQuery.RecordCount=1 then showmessage('Путевка уже оплачена')
else begin tm:=0;
ROplataForm:=TROplataForm.Create(self); ROplataForm.Caption:=ROplataForm.Caption+' : '+PutevkaForm.BitBtn4.Caption;
ROplataForm.ShowModal;
end;
end;
procedure TPutevkaForm.BitBtn5Click(Sender: TObject); begin
if dm.OplataQuery.RecordCount=0 then showmessage('Записи отсутствуют')
else begin
id_put:=dm.PutevkaQuery.Fields[0].AsString; id_opl:=dm.OplataQuery.Fields[0].AsString; tm:=1;
if accessdb.Update('opl','Oplata',id_opl)=false then showmessage('Запись используется другим пользователем')
else begin
ROplataForm:=TROplataForm.Create(self);
rn:=dm.OplataQuery.RecNo; ROplataForm.Caption:=caption+' : '+bitbtn5.Caption;
ROplataForm.ShowModal;
end;
end;
end;
procedure TPutevkaForm.BitBtn6Click(Sender: TObject); begin
if DM.OplataQuery.RecordCount=0 then showmessage('Записи отсутствуют')
else begin
id_opl:=dm.OplataQuery.Fields[0].AsString; tm:=1;
if accessdb.Delete('opl','Oplata',id_opl)=false then showmessage('Запись используется другим пользователем')
else DM.OplataQuery.Requery(); end;
end;
end.
unit RPutevkaUnit;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, Klientunit, DataModule, StdCtrls, Mask, DBCtrls, ExtCtrls, ComCtrls, Buttons, accessdb;
type
TRPutevkaForm = class(TForm) DateTimePicker1: TDateTimePicker; Edit1: TEdit;
53
ComboBox1: TComboBox;
ComboBox2: TComboBox; Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Label4: TLabel;
BitBtn1: TBitBtn;
BitBtn2: TBitBtn;
Label5: TLabel; ComboBox3: TComboBox;
procedure BitBtn2Click(Sender: TObject);
procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean); procedure ComboBox1Change(Sender: TObject);
procedure ComboBox2Change(Sender: TObject); procedure BitBtn1Click(Sender: TObject); procedure ComboBox3Change(Sender: TObject);
private
{Private declarations } public
{Public declarations } end;
var
RPutevkaForm: TRPutevkaForm;
implementation
{$R *.dfm}
procedure TRPutevkaForm.BitBtn2Click(Sender: TObject); begin
closequery;
end;
procedure TRPutevkaForm.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin accessdb.Zan('put','Putevka',id_put,tm); Edit1.Clear; DateTimePicker1.DateTime:=date; close;
end;
procedure TRPutevkaForm.ComboBox1Change(Sender: TObject); begin
dm.temp.active:=false;
dm.temp.CommandText:='Select id_kl from Klient where (sernompas_kl="'+combobox1.Text+'")';
dm.temp.active:=true; id_kl:=dm.temp.Fields[0].AsString; end;
procedure TRPutevkaForm.ComboBox2Change(Sender: TObject); begin
dm.temp.active:=false;
dm.temp.CommandText:='Select id_mar from Marshrut where (nazv_mar="'+combobox2.Text+'")';
dm.temp.active:=true; id_mar:=dm.temp.Fields[0].AsString; RPutevkaform.ComboBox3.Clear; dm.temp.Active:=false;
dm.temp.CommandText:='SELECT Stoimost.ID_st, Stoimost.ID_mar, Stoimost.Data_st, Stoimost.Stoim_st FROM Stoimost WHERE (id_mar='+id_mar+')';
dm.temp.Active:=true; while not dm.temp.Eof do begin
RPutevkaform.ComboBox3.Items.Add(dm.temp.Fields[2].asstring);
dm.temp.Next;
end;
end;
procedure TRPutevkaForm.BitBtn1Click(Sender: TObject); begin
if (Edit1.Text='') OR (combobox1.Text='') OR (combobox2.Text='') OR (combobox3.Text='') then showmessage('Вы не заполнили одно или несколько полей')
else
begin
if tm=0 then begin
dm.temp.Active:=false;
dm.temp.CommandText:='Select id_put from Putevka where (nomer_put="'+Edit1.Text+'")';
dm.temp.Active:=true;
if dm.temp.RecordCount>0 then showmessage('Подобная запись уже существует')
else begin
dm.ADOCommand1.CommandText:='Insert into Putevka (nomer_put, id_kl, id_mar, dataoplaty_put, stoim_put) values ("'+Edit1.Text+'",'+id_kl+','+id_mar+',"'+datetostr(DateTimePicker1.Date )+'", '+stoim_put+')';
dm.ADOCommand1.Execute;
dm.PutevkaQuery.Requery();
dm.PutevkaQuery.Last;
closequery;
end; end
else begin
dm.temp.Active:=false;
dm.temp.CommandText:='Select id_put from Putevka where (nomer_put="'+Edit1.Text+'")';
dm.temp.Active:=true;
if (dm.temp.RecordCount>0) and (id_put<>dm.temp.Fields[0].asstring) then showmessage('Подобная запись уже существует')
else begin
dm.ADOCommand1.CommandText:='Update Putevka SET nomer_put="'+Edit1.Text+'", id_kl="'+id_kl+'", id_mar="'+id_mar+'", dataoplaty_put="'+datetostr(DateTimePicker1.Date)+'", stoim_put='+stoim_put+' WHERE (id_put='+id_put+')';
dm.ADOCommand1.Execute;
dm.PutevkaQuery.Requery();
closequery;
dm.PutevkaQuery.RecNo:=rn;
end;
end;
end;
end;
procedure TRPutevkaForm.ComboBox3Change(Sender: TObject); var data:string;
begin data:=ComboBox3.Text;
data:=Copy(data,4,2)+'/'+Copy(data,1,2)+'/'+Copy(data,7,4);
dm.temp.Active:=false;
dm.temp.CommandText:='SELECT Stoimost.ID_st, Stoimost.ID_mar, Stoimost.Data_st, Stoimost.Stoim_st FROM Stoimost WHERE (id_mar='+id_mar+') and (Data_st=#'+data+'#)';
dm.temp.Active:=true; stoim_put:=dm.temp.Fields[3].AsString; end;
end.
unit ROplataUnit;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, Turoperatorunit, DataModule, StdCtrls, Mask, DBCtrls, ExtCtrls,
ComCtrls, Buttons, accessdb;
type
TROplataForm = class(TForm) DateTimePicker1: TDateTimePicker; Label1: TLabel;
BitBtn1: TBitBtn;
BitBtn2: TBitBtn;
DateTimePicker2: TDateTimePicker; procedure BitBtn2Click(Sender: TObject);
54
procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean); procedure BitBtn1Click(Sender: TObject);
private
{Private declarations } public
{Public declarations } end;
var
ROplataForm: TROplataForm;
implementation
{$R *.dfm}
procedure TROplataForm.BitBtn2Click(Sender: TObject); begin
closequery;
end;
procedure TROplataForm.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin accessdb.Zan('opl','Oplata',id_opl,tm); close;
end;
procedure TROplataForm.BitBtn1Click(Sender: TObject); var s:real;
s1,period1:string; begin
if tm=0 then begin
dm.temp.Active:=false;
dm.temp.CommandText:='SELECT Putevka.ID_put, Putevka.stoim_put, dataoplaty_put FROM Putevka WHERE (id_put='+id_put+')'; dm.temp.Active:=true;
period1:=dm.temp.Fields[2].AsString;
period1:=Copy(period1,1,2)+'.'+Copy(period1,4,2)+'.'+Copy(period1,7,4)
;
DateTimePicker2.Date:=strtodate(period1);
if DateTimePicker2.DateTime > DateTimePicker1.DateTime then showmessage('Путевка может быть оплачена туроперотору только после приобретения ее клиентом!') else
begin stoim_put:=dm.temp.Fields[1].AsString; s:=0.8*strtofloat(stoim_put); s1:=floattostr(s);
dm.ADOCommand1.CommandText:='Insert into Oplata (id_put,data_opl,stoim_opl) values ('+id_put+',"'+datetostr(DateTimePicker1.Date)+'",'+s1+')';
dm.ADOCommand1.Execute;
dm.OplataQuery.Requery();
dm.OplataQuery.Last;
closequery;
end;
if tm<>0 then begin
dm.ADOCommand1.CommandText:='Update Oplata SET data_opl="'+datetostr(DateTimePicker1.Date)+'" WHERE (id_opl='+id_opl+')';
dm.ADOCommand1.Execute;
dm.OplataQuery.Requery();
closequery;
dm.OplataQuery.RecNo:=rn;
end;
end;
end;
end.
unit OtchPutUnit;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls,
Forms,
Dialogs, Grids, DBGrids, DataModule, StdCtrls, Buttons, accessdb,
ComCtrls, PutevkaUnit;
type
TOtchPutForm = class(TForm) DateTimePicker1: TDateTimePicker; DateTimePicker2: TDateTimePicker; Label1: TLabel;
Label2: TLabel;
Label3: TLabel; Button1: TButton; DBGrid1: TDBGrid;
procedure Button1Click(Sender: TObject); procedure FormCreate(Sender: TObject);
private
{Private declarations } public
{Public declarations } end;
var
OtchPutForm: TOtchPutForm; period1,period2:string;
implementation
{$R *.dfm}
procedure TOtchPutForm.Button1Click(Sender: TObject); var s:real;
begin
if DateTimePicker2.DateTime < DateTimePicker1.DateTime then showmessage('Период введен некорректно!') else
begin period1:=datetostr(DateTimePicker1.Date); period2:=datetostr(DateTimePicker2.Date);
period1:=Copy(period1,4,2)+'/'+Copy(period1,1,2)+'/'+Copy(period1,7,4)
;
period2:=Copy(period2,4,2)+'/'+Copy(period2,1,2)+'/'+Copy(period2,7,4)
;
//подключение к БД
DM.ADOConnection1.Connected:=true; //очистка текста запроса
DM.PutevkaQuery.SQL.Clear; //формирование запроса
DM.PutevkaQuery.SQL.Add('SELECT Putevka.ID_put, Putevka.nomer_put, Klient.sernompas_kl, Putevka.dataoplaty_put, Marshrut.nazv_mar');
DM.PutevkaQuery.SQL.Add('FROM Marshrut INNER JOIN (Klient INNER JOIN Putevka ON Klient.ID_kl = Putevka.ID_kl) ON Marshrut.ID_mar = Putevka.ID_mar'); DM.PutevkaQuery.SQL.Add('WHERE (((Putevka.dataoplaty_put) Between #'+period1+'# And #'+period2+'#));');
//исполнение запроса и вывод результата
DM.PutevkaQuery.Open;
DBGrid1.Columns.Items[0].Title.Caption:='Номер'; DBGrid1.Columns.Items[1].Title.Caption:='Серия и номер паспорта'; DBGrid1.Columns.Items[2].Title.Caption:='Название маршрута'; DBGrid1.Columns.Items[3].Title.Caption:='Дата оплаты';
end;
end;
procedure TOtchPutForm.FormCreate(Sender: TObject); begin
//очистка текста запроса
DM.PutevkaQuery.SQL.Clear;
end;
end.
unit OtchOpUnit;
55
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, Grids, DBGrids, DataModule, StdCtrls, Buttons, accessdb, ComCtrls, PutevkaUnit;
type
TOtchOpForm = class(TForm) DBGrid1: TDBGrid;
procedure FormCreate(Sender: TObject); private
{Private declarations } public
{Public declarations } end;
var
OtchOpForm: TOtchOpForm;
implementation
{$R *.dfm}
procedure TOtchOpForm.FormCreate(Sender: TObject); begin
//подключение к БД
DM.ADOConnection1.Connected:=true; //очистка текста запроса
DM.TuroperatorQuery.SQL.Clear; //формирование запроса
DM.TuroperatorQuery.SQL.Add('SELECT Turoperator.nomerdog_op, Turoperator.nazv_op, Turoperator.adres_op, Turoperator.datazakdog_op, Turoperator.dataokdog_op');
DM.TuroperatorQuery.SQL.Add('FROM Turoperator WHERE
(((Turoperator.dataokdog_op) Between #1/1/2015# And #12/31/2015#));');
//исполнение запроса и вывод результата
DM.TuroperatorQuery.Open;
DBGrid1.Columns.Items[0].Title.Caption:='Номер договора'; DBGrid1.Columns.Items[1].Title.Caption:='Название'; DBGrid1.Columns.Items[2].Title.Caption:='Адрес'; DBGrid1.Columns.Items[3].Title.Caption:='Дата заключения договора';
DBGrid1.Columns.Items[4].Title.Caption:='Дата окончания договора'; end;
end.
unit OtchPribUnit;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, ComCtrls, Grids;
type
TOtchPribForm = class(TForm) DateTimePicker1: TDateTimePicker; DateTimePicker2: TDateTimePicker; Label1: TLabel;
Label2: TLabel;
Label3: TLabel; Button1: TButton;
StringGrid1: TStringGrid;
procedure Button1Click(Sender: TObject); private
{Private declarations } public
{Public declarations } end;
var
OtchPribForm: TOtchPribForm;
implementation
uses DataModule;
{$R *.dfm}
procedure TOtchPribForm.Button1Click(Sender: TObject); var period1,period2,str:string;
i,i2,j,max:integer;
d:real; begin
Stringgrid1.Cells[0,0]:='Страна'; Stringgrid1.Cells[1,0]:='Прибыль';
if DateTimePicker2.DateTime < DateTimePicker1.DateTime then showmessage('Период введен некорректно!') else
begin period1:=datetostr(DateTimePicker1.Date); period2:=datetostr(DateTimePicker2.Date);
period1:=Copy(period1,4,2)+'/'+Copy(period1,1,2)+'/'+Copy(period1,7,4)
;
period2:=Copy(period2,4,2)+'/'+Copy(period2,1,2)+'/'+Copy(period2,7,4)
;
//подключение к БД
DM.ADOConnection1.Connected:=true; //очистка текста запроса
DM.PutevkaQuery.SQL.Clear; //формирование запроса
DM.PutevkaQuery.SQL.Add('SELECT Putevka.ID_put, Marshrut.strana_mar, Putevka.dataoplaty_put, Putevka.stoim_put FROM Marshrut INNER JOIN Putevka ON Marshrut.ID_mar = Putevka.ID_mar');
DM.PutevkaQuery.SQL.Add('WHERE (((Putevka.dataoplaty_put) Between #'+period1+'# And #'+period2+'#)) ORDER BY Marshrut.strana_mar;');
DM.PutevkaQuery.Open;
stringgrid1.Cells[0,1]:=DM.PutevkaQuery.Fields[1].Asstring;
stringgrid1.Cells[1,1]:='0';
j:=1;
stringgrid1.RowCount:=dm.PutevkaQuery.RecordCount+1; while not DM.PutevkaQuery.Eof do
begin
if DM.PutevkaQuery.Fields[1].Asstring=stringgrid1.Cells[0,j] then begin
d:=strtofloat(stringgrid1.Cells[1,j])+0.2*strtofloat(DM.PutevkaQuery.Fiel
ds[3].asstring);
stringgrid1.Cells[1,j]:=floattostr(d); end
else begin
stringgrid1.Cells[0,j+1]:=DM.PutevkaQuery.Fields[1].Asstring;
stringgrid1.Cells[1,j+1]:=floattostr(strtofloat(DM.PutevkaQuery.Fields[3]
.asstring)*0.2);
j:=j+1;
end;
DM.PutevkaQuery.Next;
end;
For i:=1 to dm.PutevkaQuery.RecordCount do begin
if stringgrid1.Cells[0,i]='' then begin StringGrid1.RowCount:=i; break;
end;
end;
max:=strtoint(stringgrid1.cells[1,1]); for i:=1 to stringgrid1.RowCount-1 do begin
if strtoint(stringgrid1.cells[1,i])>max then begin max:=strtoint(stringgrid1.cells[1,i]); i2:=i;
56
str:=stringgrid1.cells[0,i];
end;
end;
ShowMessage('за заданный период максимальную прибыль равную
'+inttostr(max)+' принесла страна '+stringgrid1.cells[0,i2+1]); end;
end;
end.
unit OtchMarFormUnit;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, Grids, DBGrids, DataModule, StdCtrls, Buttons, accessdb, ComCtrls, PutevkaUnit;
type
TOtchMarForm = class(TForm) DBGrid1: TDBGrid;
procedure FormCreate(Sender: TObject); private
{Private declarations } public
{Public declarations } end;
var
OtchMarForm: TOtchMarForm;
implementation
{$R *.dfm}
procedure TOtchMarForm.FormCreate(Sender: TObject); begin
//подключение к БД
DM.ADOConnection1.Connected:=true; //очистка текста запроса
DM.MarshrutQuery.SQL.Clear; //формирование запроса
DM.MarshrutQuery.SQL.Add('SELECT Marshrut.nazv_mar, Marshrut.strana_mar, Stoimost.Stoim_st, Stoimost.Data_st');
DM.MarshrutQuery.SQL.Add('FROM Marshrut INNER JOIN Stoimost ON Marshrut.ID_mar = Stoimost.ID_mar');
DM.MarshrutQuery.SQL.Add('ORDER BY Marshrut.nazv_mar, Stoimost.Data_st');
//исполнение запроса и вывод результата
DM.MarshrutQuery.Open; DBGrid1.Columns.Items[0].Title.Caption:='Название маршрута'; DBGrid1.Columns.Items[1].Title.Caption:='Страна'; DBGrid1.Columns.Items[2].Title.Caption:='Стоимость маршрута';
DBGrid1.Columns.Items[3].Title.Caption:='Дата, начиная с которой действует стоимость';
end;
end.
unit OtchPriceUnit;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, ComCtrls, Grids, Menus, ComObj;
type
TOtchPriceForm = class(TForm) StringGrid1: TStringGrid; Label1: TLabel;
Button1: TButton;
procedure FormCreate(Sender: TObject);
procedure Button1Click(Sender: TObject); private
{Private declarations } public
{Public declarations } end;
var
OtchPriceForm: TOtchPriceForm;
implementation
uses DataModule;
{$R *.dfm}
procedure TOtchPriceForm.FormCreate(Sender: TObject); var i,j:integer;
begin
Stringgrid1.Cells[0,0]:='Название турмаршрута'; Stringgrid1.Cells[1,0]:='Страна'; Stringgrid1.Cells[2,0]:='Стоимость';
//подключение к БД
DM.ADOConnection1.Connected:=true; //очистка текста запроса
DM.MarshrutQuery.SQL.Clear; //формирование запроса
DM.MarshrutQuery.SQL.Add('SELECT Marshrut.ID_mar, Marshrut.nazv_mar, Marshrut.strana_mar, Stoimost.Data_st, Stoimost.Stoim_st FROM Marshrut INNER JOIN Stoimost ON Marshrut.ID_mar = Stoimost.ID_mar ORDER BY Marshrut.nazv_mar, Stoimost.Data_st;');
//исполнение запроса и вывод результата
DM.MarshrutQuery.Open;
stringgrid1.Cells[0,1]:=DM.MarshrutQuery.Fields[1].Asstring;
stringgrid1.Cells[1,1]:=DM.MarshrutQuery.Fields[2].Asstring;
stringgrid1.Cells[2,1]:=DM.MarshrutQuery.Fields[4].Asstring;
j:=1;
stringgrid1.RowCount:=dm.MarshrutQuery.RecordCount+1; while not DM.MarshrutQuery.Eof do
begin
if DM.MarshrutQuery.Fields[1].Asstring=stringgrid1.Cells[0,j] then begin stringgrid1.Cells[2,j]:=DM.MarshrutQuery.Fields[4].Asstring;
end else begin
stringgrid1.Cells[0,j+1]:=DM.MarshrutQuery.Fields[1].Asstring;
stringgrid1.Cells[1,j+1]:=DM.MarshrutQuery.Fields[2].Asstring;
stringgrid1.Cells[2,j+1]:=DM.MarshrutQuery.Fields[4].Asstring;
j:=j+1;
end;
DM.MarshrutQuery.Next;
end;
For i:=1 to dm.MarshrutQuery.RecordCount do begin
if stringgrid1.Cells[0,i]='' then begin StringGrid1.RowCount:=i; break;
end;
end;
end;
procedure TOtchPriceForm.Button1Click(Sender: TObject); var
i,j,index,count: Integer; ExcelApp,sheet: oleVariant;
begin try
ExcelApp := CreateOleObject('Excel.Application'); except
ShowMessage('Не удалось запустить Excel.');
57
Exit;
end; ExcelApp.WorkBooks.Add(-4167);
ExcelApp.Worksheets[1].PageSetup.Orientation := 1; ExcelApp.WorkBooks[1].WorkSheets[1].name := 'Прайс-лист'; ExcelApp.WorkBooks[1].WorkSheets[1].Columns[1].ColumnWidth :=
30;
ExcelApp.WorkBooks[1].WorkSheets[1].Columns[2].ColumnWidth := 20;
ExcelApp.WorkBooks[1].WorkSheets[1].Columns[3].ColumnWidth := 20;
sheet:=ExcelApp.WorkBooks[1].WorkSheets['Прайс-лист']; sheet.Range[sheet.Cells[1, 1], sheet.Cells[1,3]].Select; ExcelApp.Selection.MergeCells:=True; ExcelApp.Selection.HorizontalAlignment:=3; sheet.Range['A1']:= 'Прайс-лист';
index:=2;
for i := 1 to 3 do begin
sheet.cells[index,i]:=Stringgrid1.Cells[i-1,0]; sheet.Cells[index,i].Select; ExcelApp.Selection.Borders.Weight:=3;
end;
inc(index);
for i:=1 to Stringgrid1.RowCount-1 do begin
for j:=1 to Stringgrid1.ColCount do begin
sheet.cells[index,j]:=Stringgrid1.Cells[j-1,i]; sheet.Cells[index,j].Select; ExcelApp.Selection.Borders.Weight:=2;
end;
inc(index);
end;
ExcelApp.Visible := true; end;
end.
unit OtchDohodUnit;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, ComCtrls, Grids, ComObj;
type
TOtchDohodForm = class(TForm) Label1: TLabel;
Label2: TLabel;
DateTimePicker1: TDateTimePicker; Label3: TLabel;
DateTimePicker2: TDateTimePicker; Button1: TButton;
StringGrid1: TStringGrid; Button2: TButton;
procedure Button1Click(Sender: TObject); procedure Button2Click(Sender: TObject);
private
{Private declarations } public
{Public declarations } end;
var
OtchDohodForm: TOtchDohodForm;
implementation
uses DataModule;
{$R *.dfm}
procedure TOtchDohodForm.Button1Click(Sender: TObject); var period1,period2,str:string;
i,i2,j,max:integer;
d:real; begin
Stringgrid1.Cells[0,0]:='Название турмаршрута'; Stringgrid1.Cells[1,0]:='Страна'; Stringgrid1.Cells[2,0]:='Доход';
if DateTimePicker2.DateTime < DateTimePicker1.DateTime then showmessage('Период введен некорректно!') else
begin period1:=datetostr(DateTimePicker1.Date); period2:=datetostr(DateTimePicker2.Date);
period1:=Copy(period1,4,2)+'/'+Copy(period1,1,2)+'/'+Copy(period1,7,4)
;
period2:=Copy(period2,4,2)+'/'+Copy(period2,1,2)+'/'+Copy(period2,7,4)
;
//подключение к БД
DM.ADOConnection1.Connected:=true; //очистка текста запроса
DM.PutevkaQuery.SQL.Clear; //формирование запроса
DM.PutevkaQuery.SQL.Add('SELECT Putevka.ID_put, Putevka.ID_mar, Marshrut.nazv_mar, Marshrut.strana_mar, Putevka.dataoplaty_put, Putevka.stoim_put FROM Marshrut INNER JOIN Putevka ON Marshrut.ID_mar = Putevka.ID_mar'); DM.PutevkaQuery.SQL.Add('WHERE (((Putevka.dataoplaty_put) Between #'+period1+'# And #'+period2+'#)) ORDER BY Putevka.ID_mar;');
DM.PutevkaQuery.Open;
stringgrid1.Cells[0,1]:=DM.PutevkaQuery.Fields[2].Asstring;
stringgrid1.Cells[1,1]:=DM.PutevkaQuery.Fields[3].Asstring;
stringgrid1.Cells[2,1]:='0';
j:=1;
stringgrid1.RowCount:=dm.PutevkaQuery.RecordCount+1; while not DM.PutevkaQuery.Eof do
begin
if DM.PutevkaQuery.Fields[2].Asstring=stringgrid1.Cells[0,j] then begin
d:=strtofloat(stringgrid1.Cells[2,j])+strtofloat(DM.PutevkaQuery.Fields[5
].asstring);
stringgrid1.Cells[2,j]:=floattostr(d); end
else begin
stringgrid1.Cells[0,j+1]:=DM.PutevkaQuery.Fields[2].Asstring;
stringgrid1.Cells[1,j+1]:=DM.PutevkaQuery.Fields[3].Asstring;
stringgrid1.Cells[2,j+1]:=DM.PutevkaQuery.Fields[5].Asstring;
j:=j+1;
end;
DM.PutevkaQuery.Next;
end;
For i:=1 to dm.PutevkaQuery.RecordCount do begin
if stringgrid1.Cells[0,i]='' then begin StringGrid1.RowCount:=i; break;
end;
end;
end;
end;
procedure TOtchDohodForm.Button2Click(Sender: TObject); var
i,j,index,count: Integer; ExcelApp,sheet: oleVariant;
begin try
ExcelApp := CreateOleObject('Excel.Application'); except
ShowMessage('Не удалось запустить Excel.');
Exit;
end; ExcelApp.WorkBooks.Add(-4167);
ExcelApp.Worksheets[1].PageSetup.Orientation := 1;
ExcelApp.WorkBooks[1].WorkSheets[1].name := 'Доход от турмаршрутов';
ExcelApp.WorkBooks[1].WorkSheets[1].Columns[1].ColumnWidth := 30;
ExcelApp.WorkBooks[1].WorkSheets[1].Columns[2].ColumnWidth := 20;
ExcelApp.WorkBooks[1].WorkSheets[1].Columns[3].ColumnWidth := 20;
sheet:=ExcelApp.WorkBooks[1].WorkSheets['Доход от турмаршрутов'];
sheet.Range[sheet.Cells[1, 1], sheet.Cells[1,3]].Select; ExcelApp.Selection.MergeCells:=True; ExcelApp.Selection.HorizontalAlignment:=3; sheet.Range['A1']:= 'Доход от турмаршрутов'; index:=2;
for i := 1 to 3 do begin
sheet.cells[index,i]:=Stringgrid1.Cells[i-1,0]; sheet.Cells[index,i].Select; ExcelApp.Selection.Borders.Weight:=3;
end;
inc(index);
for i:=1 to Stringgrid1.RowCount-1 do begin
for j:=1 to Stringgrid1.ColCount do begin
sheet.cells[index,j]:=Stringgrid1.Cells[j-1,i]; sheet.Cells[index,j].Select; ExcelApp.Selection.Borders.Weight:=2;
end;
inc(index);
end;
ExcelApp.Visible := true; end;
end.
58