 |
Модуль FormProviderDlg.pas
|
|
|
|
Модуль Main.pas
unit Main;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.Menus, Vcl.StdCtrls, Vcl.Grids,
Vcl.DBGrids, DataModule, Vcl.DBCtrls, TeeGDIPlus, TeEngine, Series,
Vcl.ExtCtrls, TeeProcs, Chart, ExcelXP, ComObj;
type
TFormMain = class(TForm)
GroupBox: TGroupBox;
DBGrid: TDBGrid;
MainMenu: TMainMenu;
N1: TMenuItem;
N2: TMenuItem;
N3: TMenuItem;
N4: TMenuItem;
N5: TMenuItem;
N6: TMenuItem;
LabelModel: TLabel;
EditModel: TEdit;
LabelCategory: TLabel;
ComboBoxCategory: TComboBox;
LabelProvider: TLabel;
ComboBoxProvider: TComboBox;
Button1: TButton;
Button2: TButton;
N7: TMenuItem;
N8: TMenuItem;
N9: TMenuItem;
N10: TMenuItem;
Chart: TChart;
Series1: TPieSeries;
N11: TMenuItem;
N12: TMenuItem;
N13: TMenuItem;
Image: TImage;
procedure N2Click(Sender: TObject);
procedure N4Click(Sender: TObject);
procedure N5Click(Sender: TObject);
procedure N6Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure N8Click(Sender: TObject);
procedure N9Click(Sender: TObject);
procedure N10Click(Sender: TObject);
procedure N11Click(Sender: TObject);
procedure N12Click(Sender: TObject);
procedure N13Click(Sender: TObject);
private
{ Private declarations }
public
procedure UpdateComboBox;
procedure UpdateQuery1;
procedure UpdateQuery2;
procedure UpdateQuery3;
procedure UpdateQuery4;
procedure UpdateQuery5;
end;
var
FormMain: TFormMain;
implementation
{$R *.dfm}
uses
FormCategoryDlg, FormProviderDlg, FormDeviceDlg;
procedure TFormMain.Button1Click(Sender: TObject);
begin
UpdateQuery1;
end;
procedure TFormMain.Button2Click(Sender: TObject);
begin
ComboBoxCategory.ItemIndex:= 0;
ComboBoxProvider.ItemIndex:= 0;
EditModel.Text:= '';
end;
procedure TFormMain.FormCreate(Sender: TObject);
begin
UpdateComboBox;
end;
procedure TFormMain.N10Click(Sender: TObject);
begin
UpdateQuery3;
end;
procedure TFormMain.N11Click(Sender: TObject);
begin
UpdateQuery4;
end;
procedure TFormMain.N12Click(Sender: TObject);
begin
UpdateQuery5;
end;
procedure TFormMain.N13Click(Sender: TObject);
var
VExcel: Variant;
i, j: Integer;
begin
VExcel:= CreateOleObject('Excel.Application');
VExcel.Workbooks.Add;
VExcel.Visible:= True;
VExcel.Range['A1:F'+IntToStr(DM.ADOQueryWork.RecordCount+1)].Select;
VExcel.Selection.Borders.LineStyle:= 1;
VExcel.Selection.Borders.Weight:= 3;
VExcel.Selection.HorizontalAlignment:= 3;
VExcel.Columns[1].ColumnWidth:= 60;
VExcel.Columns[2].ColumnWidth:= 25;
VExcel.Columns[3].ColumnWidth:= 25;
VExcel.Columns[4].ColumnWidth:= 25;
|
|
|
VExcel.Columns[5].ColumnWidth:= 25;
VExcel.Columns[6].ColumnWidth:= 25;
VExcel.Range['A1']:= 'Модель';
VExcel.Range['B1']:= 'Категория';
VExcel.Range['C1']:= 'Производитель';
VExcel.Range['D1']:= 'Кол-во';
VExcel.Range['E1']:= 'Стоим. ед., р';
VExcel.Range['F1']:= 'Общая ст., р';
DM.ADOQueryWork.First;
for i:= 0 to DM.ADOQueryWork.RecordCount-1 do begin
VExcel.Range['A'+IntToStr(i+2)]:= DM.ADOQueryWork.Fields[0].AsString;
VExcel.Range['B'+IntToStr(i+2)]:= DM.ADOQueryWork.Fields[1].AsString;
VExcel.Range['C'+IntToStr(i+2)]:= DM.ADOQueryWork.Fields[2].AsString;
VExcel.Range['D'+IntToStr(i+2)]:= DM.ADOQueryWork.Fields[3].AsString;
VExcel.Range['E'+IntToStr(i+2)]:= DM.ADOQueryWork.Fields[4].AsString;
VExcel.Range['F'+IntToStr(i+2)]:= DM.ADOQueryWork.Fields[5].AsString;
DM.ADOQueryWork.Next;
end;
end;
procedure TFormMain.N2Click(Sender: TObject);
begin
Close;
end;
procedure TFormMain.N4Click(Sender: TObject);
begin
FormCategoryOpen;
UpdateComboBox;
end;
procedure TFormMain.N5Click(Sender: TObject);
begin
FormProviderOpen;
UpdateComboBox;
end;
procedure TFormMain.N6Click(Sender: TObject);
begin
FormDeviceOpen;
UpdateComboBox;
end;
procedure TFormMain.N8Click(Sender: TObject);
begin
UpdateQuery1;
end;
procedure TFormMain.N9Click(Sender: TObject);
begin
UpdateQuery2;
end;
procedure TFormMain.UpdateComboBox;
var
i: Integer;
begin
// Формирование комбобокса Категории
DM.ADOQueryTemp.Active:= False;
DM.ADOQueryTemp.SQL.Clear;
DM.ADOQueryTemp.SQL.Add('SELECT title');
DM.ADOQueryTemp.SQL.Add('FROM category');
DM.ADOQueryTemp.SQL.Add('ORDER BY title');
DM.ADOQueryTemp.Active:= True;
//
ComboBoxCategory.Items.Clear;
ComboBoxCategory.Items.Add('Все');
DM.ADOQueryTemp.First;
for i:= 0 to DM.ADOQueryTemp.RecordCount-1 do begin
ComboBoxCategory.Items.Add(DM.ADOQueryTemp.FieldByName('title').AsString);
DM.ADOQueryTemp.Next;
end;
ComboBoxCategory.ItemIndex:= 0;
// Формирование комбобокса Производители
DM.ADOQueryTemp.Active:= False;
DM.ADOQueryTemp.SQL.Clear;
DM.ADOQueryTemp.SQL.Add('SELECT title');
DM.ADOQueryTemp.SQL.Add('FROM provider');
DM.ADOQueryTemp.SQL.Add('ORDER BY title');
DM.ADOQueryTemp.Active:= True;
//
ComboBoxProvider.Items.Clear;
ComboBoxProvider.Items.Add('Все');
DM.ADOQueryTemp.First;
for i:= 0 to DM.ADOQueryTemp.RecordCount-1 do begin
ComboBoxProvider.Items.Add(DM.ADOQueryTemp.FieldByName('title').AsString);
DM.ADOQueryTemp.Next;
end;
ComboBoxProvider.ItemIndex:= 0;
end;
procedure TFormMain.UpdateQuery1;
begin
GroupBox.Visible:= True;
DBGrid.Visible:= True;
N13.Enabled:= True;
Chart.Visible:= False;
Image.Visible:= False;
//
DM.ADOQueryWork.Active:= False;
DM.ADOQueryWork.SQL.Clear;
DM.ADOQueryWork.SQL.Add('SELECT D.title, C.title, P.title, D.count, D.cost, D.count*D.cost AS allcost');
DM.ADOQueryWork.SQL.Add('FROM device AS D, category AS C, provider AS P');
DM.ADOQueryWork.SQL.Add('WHERE D.idcategory = C.id AND D.idprovider = P.id');
if ComboBoxCategory.ItemIndex <> 0 then begin
DM.ADOQueryWork.SQL.Add('AND C.title =:P1');
|
|
|
DM.ADOQueryWork.Parameters.ParamByName('P1').Value:= ComboBoxCategory.Text;
end;
if ComboBoxProvider.ItemIndex <> 0 then begin
DM.ADOQueryWork.SQL.Add('AND P.title =:P2');
DM.ADOQueryWork.Parameters.ParamByName('P2').Value:= ComboBoxProvider.Text;
end;
if EditModel.Text <> '' then begin
DM.ADOQueryWork.SQL.Add('AND D.title LIKE:P3');
DM.ADOQueryWork.Parameters.ParamByName('P3').Value:= '%'+EditModel.Text+'%';
end;
DM.ADOQueryWork.SQL.Add('ORDER BY D.title');
DM.ADOQueryWork.Active:= True;
//
DBGrid.Columns[0].Title.Caption:= 'Модель';
DBGrid.Columns[0].Width:= 320;
DBGrid.Columns[1].Title.Caption:= 'Категория';
DBGrid.Columns[1].Width:= 100;
DBGrid.Columns[2].Title.Caption:= 'Производитель';
DBGrid.Columns[2].Width:= 100;
DBGrid.Columns[3].Title.Caption:= 'Кол-во';
DBGrid.Columns[3].Width:= 60;
DBGrid.Columns[4].Title.Caption:= 'Стоим. ед., р';
DBGrid.Columns[4].Width:= 80;
DBGrid.Columns[5].Title.Caption:= 'Общая ст., р';
DBGrid.Columns[5].Width:= 80;
end;
procedure TFormMain.UpdateQuery2;
begin
GroupBox.Visible:= False;
DBGrid.Visible:= True;
N13.Enabled:= True;
Chart.Visible:= False;
Image.Visible:= False;
//
DM.ADOQueryWork.Active:= False;
DM.ADOQueryWork.SQL.Clear;
DM.ADOQueryWork.SQL.Add('SELECT D.title, C.title, P.title, D.count, D.cost, D.count*D.cost AS allcost');
DM.ADOQueryWork.SQL.Add('FROM device AS D, category AS C, provider AS P');
DM.ADOQueryWork.SQL.Add('WHERE D.idcategory = C.id AND D.idprovider = P.id AND D.count <= 5');
DM.ADOQueryWork.SQL.Add('ORDER BY D.title');
DM.ADOQueryWork.Active:= True;
//
DBGrid.Columns[0].Title.Caption:= 'Модель';
DBGrid.Columns[0].Width:= 320;
DBGrid.Columns[1].Title.Caption:= 'Категория';
DBGrid.Columns[1].Width:= 100;
DBGrid.Columns[2].Title.Caption:= 'Производитель';
DBGrid.Columns[2].Width:= 100;
DBGrid.Columns[3].Title.Caption:= 'Кол-во';
DBGrid.Columns[3].Width:= 60;
DBGrid.Columns[4].Title.Caption:= 'Стоим. ед., р';
DBGrid.Columns[4].Width:= 80;
DBGrid.Columns[5].Title.Caption:= 'Общая ст., р';
DBGrid.Columns[5].Width:= 80;
end;
procedure TFormMain.UpdateQuery3;
begin
GroupBox.Visible:= False;
DBGrid.Visible:= True;
N13.Enabled:= True;
Chart.Visible:= False;
Image.Visible:= False;
//
DM.ADOQueryWork.Active:= False;
DM.ADOQueryWork.SQL.Clear;
DM.ADOQueryWork.SQL.Add('SELECT D.title, C.title, P.title, D.count, D.cost, D.count*D.cost AS allcost');
DM.ADOQueryWork.SQL.Add('FROM device AS D, category AS C, provider AS P');
DM.ADOQueryWork.SQL.Add('WHERE D.idcategory = C.id AND D.idprovider = P.id AND D.count >= 25');
DM.ADOQueryWork.SQL.Add('ORDER BY D.title');
DM.ADOQueryWork.Active:= True;
//
DBGrid.Columns[0].Title.Caption:= 'Модель';
DBGrid.Columns[0].Width:= 320;
DBGrid.Columns[1].Title.Caption:= 'Категория';
DBGrid.Columns[1].Width:= 100;
DBGrid.Columns[2].Title.Caption:= 'Производитель';
DBGrid.Columns[2].Width:= 100;
DBGrid.Columns[3].Title.Caption:= 'Кол-во';
DBGrid.Columns[3].Width:= 60;
DBGrid.Columns[4].Title.Caption:= 'Стоим. ед., р';
DBGrid.Columns[4].Width:= 80;
DBGrid.Columns[5].Title.Caption:= 'Общая ст., р';
DBGrid.Columns[5].Width:= 80;
end;
procedure TFormMain.UpdateQuery4;
var
i: Integer;
begin
GroupBox.Visible:= False;
DBGrid.Visible:= False;
N13.Enabled:= False;
Chart.Visible:= True;
Image.Visible:= False;
//
DM.ADOQueryWork.Active:= False;
DM.ADOQueryWork.SQL.Clear;
DM.ADOQueryWork.SQL.Add('SELECT C.title, SUM(D.count) AS DC');
DM.ADOQueryWork.SQL.Add('FROM device AS D, category AS C');
DM.ADOQueryWork.SQL.Add('WHERE D.idcategory = C.id');
DM.ADOQueryWork.SQL.Add('GROUP BY C.title');
DM.ADOQueryWork.Active:= True;
//
Series1.Clear;
|
|
|
DM.ADOQueryWork.First;
for i:= 0 to DM.ADOQueryWork.RecordCount-1 do begin
Series1.AddPie(DM.ADOQueryWork.FieldByName('dc').AsInteger, DM.ADOQueryWork.FieldByName('title').AsString);
DM.ADOQueryWork.Next;
end;
end;
procedure TFormMain.UpdateQuery5;
var
i: Integer;
begin
GroupBox.Visible:= False;
DBGrid.Visible:= False;
N13.Enabled:= False;
Chart.Visible:= True;
Image.Visible:= False;
//
DM.ADOQueryWork.Active:= False;
DM.ADOQueryWork.SQL.Clear;
DM.ADOQueryWork.SQL.Add('SELECT P.title, SUM(D.count) AS DC');
DM.ADOQueryWork.SQL.Add('FROM device AS D, provider AS P');
DM.ADOQueryWork.SQL.Add('WHERE D.idprovider = P.id');
DM.ADOQueryWork.SQL.Add('GROUP BY P.title');
DM.ADOQueryWork.Active:= True;
//
Series1.Clear;
DM.ADOQueryWork.First;
for i:= 0 to DM.ADOQueryWork.RecordCount-1 do begin
Series1.AddPie(DM.ADOQueryWork.FieldByName('dc').AsInteger, DM.ADOQueryWork.FieldByName('title').AsString);
DM.ADOQueryWork.Next;
end;
end;
end.
Модуль FormProviderDlg.pas
unit FormProviderDlg;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.ExtCtrls, Vcl.DBCtrls, Vcl.Grids,
Vcl.DBGrids, DataModule, Vcl.StdCtrls, Vcl.Mask;
type
TFormProvider = class(TForm)
DBGrid: TDBGrid;
DBNavigator: TDBNavigator;
GroupBox: TGroupBox;
LabelTitle: TLabel;
DBEditTitle: TDBEdit;
LabelInfo: TLabel;
DBMemoInfo: TDBMemo;
private
{ Private declarations }
public
{ Public declarations }
end;
procedure FormProviderOpen;
implementation
{$R *.dfm}
procedure FormProviderOpen;
var
FormProvider: TFormProvider;
begin
FormProvider:= TFormProvider.Create(Application);
FormProvider.ShowModal;
FormProvider.Free;
end;
end.
Модуль FormDeviceDlg.pas
unit FormDeviceDlg;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DataModule, Vcl.StdCtrls, Vcl.Mask,
Vcl.DBCtrls, Vcl.ExtCtrls, Vcl.Grids, Vcl.DBGrids;
type
TFormDevice = class(TForm)
DBGrid: TDBGrid;
DBNavigator: TDBNavigator;
GroupBox: TGroupBox;
LabelTitle: TLabel;
DBEditTitle: TDBEdit;
LabelParam: TLabel;
DBMemoParam: TDBMemo;
LabelCategory: TLabel;
DBLookupComboBoxCategory: TDBLookupComboBox;
LabelProvider: TLabel;
DBLookupComboBoxProvider: TDBLookupComboBox;
LabelCount: TLabel;
DBEditCount: TDBEdit;
LabelCost: TLabel;
DBEditCost: TDBEdit;
DBImagePicture: TDBImage;
procedure DBImagePictureClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
procedure FormDeviceOpen;
implementation
{$R *.dfm}
procedure FormDeviceOpen;
var
FormDevice: TFormDevice;
begin
FormDevice:= TFormDevice.Create(Application);
FormDevice.ShowModal;
FormDevice.Free;
end;
procedure TFormDevice.DBImagePictureClick(Sender: TObject);
var
OD: TOpenDialog;
begin
OD:= TOpenDialog.Create(Self);
OD.Filter:= '*.bmp|*.bmp';
if OD.Execute then begin
DM.ADOTableDevice.Edit;
DM.ADOTableDevicePicture.LoadFromFile(OD.FileName);
DM.ADOTableDevice.Post;
end;
OD.Free;
end;
end.
|
|
|
