Курсовые работы / ПРИС П _2
.pdfRange:=Workbook.WorkSheets[1].Range[Cell1,Cell3];
ExcelApp.Visible:=True;
End;
{$R *.dfm}
procedure TBalanc.FormCreate(Sender: TObject); begin
DoxQuery.SQL.Clear;
DoxQuery.SQL.Add(SQL1+';');
DoxQuery.Open;
RasQuery.SQL.Clear;
RasQuery.SQL.Add(SQL2+';');
RasQuery.Open;
ComboBox1.Items.Add(DBGrid1.Columns.Items[0].Title.Caption);
DateTimePicker1.Date:=StrToDate('01.01.2000');
DateTimePicker2.Date:=Now;
Button1.Click;
end;
procedure TBalanc.Button1Click(Sender: TObject); var
s1,s2:real; begin
s1:=0;
s2:=0;
DBGrid1.DataSource.DataSet.First;
DBGrid2.DataSource.DataSet.First;
while not DBGrid1.DataSource.DataSet.Eof do begin
s1:=s1+DBGrid1.DataSource.DataSet.FieldByName('Sum-ND_Sum').Value; DBGrid1.DataSource.DataSet.Next;
end;
while not DBGrid2.DataSource.DataSet.Eof do begin
s2:=s2+DBGrid2.DataSource.DataSet.FieldByName('Sum-NR_Sum').Value; DBGrid2.DataSource.DataSet.Next;
end;
Edit2.Text:=FloatToStr(s1)+' РУБ.'; Edit3.Text:=FloatToStr(s2)+' РУБ.'; Edit4.Text:=FloatToStr(s1-s2)+' РУБ.'; end;
procedure TBalanc.DBGrid2TitleClick(Column: TColumn); var
i:integer;
clr:TColor; begin
s:=' ASC';
clr:= clMoneyGreen;
if RadioButton2.Checked then begin
s:=' DESC'; clr:= clSkyBlue;
end;
for i:=0 to DBGrid1.Columns.Count-1 do DBGrid2.Columns.Items[i].Title.Color:= clBtnFace; DBGrid2.Columns.Items[Column.Index].Title.Color:=clr; RasQuery.Sort:= DBGrid2.Columns.Items[Column.Index].FieldName+s;
end;
procedure TBalanc.DBGrid1TitleClick(Column: TColumn); var
i:integer;
clr:TColor; begin
s:=' ASC';
clr:= clMoneyGreen;
if RadioButton2.Checked then begin
s:=' DESC'; clr:= clSkyBlue;
end;
for i:=0 to DBGrid1.Columns.Count-1 do DBGrid1.Columns.Items[i].Title.Color:= clBtnFace; DBGrid1.Columns.Items[Column.Index].Title.Color:=clr; DoxQuery.Sort:= DBGrid1.Columns.Items[Column.Index].FieldName+s;
end;
71
procedure TBalanc.Edit1Change(Sender: TObject); var
s1,s2:string; begin
if ComboBox1.ItemIndex=-1 then ComboBox1.ItemIndex:=0; begin
s1:=DBGrid1.Columns.Items[ComboBox1.ItemIndex].FieldName;
s2:=DBGrid2.Columns.Items[ComboBox1.ItemIndex].FieldName;
end;
period1:=Datetostr(DateTimePicker1.Date);
period2:=Datetostr(DateTimePicker2.Date);
DoxQuery.SQL.Clear;
DoxQuery.SQL.Add('SELECT Family.F_Name, Sum(Now_Doxod.ND_Sum) AS [Sum-ND_Sum], Now_Doxod.ND_Data'
+' FROM Family INNER JOIN Now_Doxod ON Family.F_ID = Now_Doxod.ND_FIO'
+' WHERE '+s1+' LIKE "'+Edit1.text+'%" AND (Now_Doxod.ND_Data Between :period1 And
:period2)'
+' GROUP BY Family.F_Name, Now_Doxod.ND_Data;'); DoxQuery.Parameters.ParamByName('period1').Value:=period1; DoxQuery.Parameters.ParamByName('period2').Value:= period2;
DoxQuery.Close;
DoxQuery.Open;
RasQuery.SQL.Clear;
RasQuery.SQL.Add('SELECT Family.F_Name, Sum(Now_Rasxod.NR_Sum) AS [Sum-NR_Sum], Now_Rasxod.NR_Data'
+' FROM Family INNER JOIN Now_Rasxod ON Family.F_ID = Now_Rasxod.NR_FIO'
+' WHERE '+s2+' LIKE "'+Edit1.text+'%" AND (Now_Rasxod.NR_Data Between :period1 And
:period2)'
+' GROUP BY Family.F_Name, Now_Rasxod.NR_Data;'); RasQuery.Parameters.ParamByName('period1').Value:=period1; RasQuery.Parameters.ParamByName('period2').Value:= period2; RasQuery.Close;
RasQuery.Open;
Button1.Click;
end;
procedure TBalanc.FormClose(Sender: TObject; var Action: TCloseAction); begin
DoxQuery.Close;
RasQuery.Close;
end;
procedure TBalanc.DateTimePicker2Change(Sender: TObject); begin
If DateTimePicker2.DateTime < DateTimePicker1.DateTime then begin
showmessage('Начальный период не может быть больше конечного!');
DateTimePicker2.Date:=DateTimePicker1.Date; end
else begin
period1:=Datetostr(DateTimePicker1.Date);
period2:=Datetostr(DateTimePicker2.Date);
DoxQuery.SQL.Clear;
DoxQuery.SQL.Add('SELECT Family.F_Name, Sum(Now_Doxod.ND_Sum) AS [Sum-ND_Sum], Now_Doxod.ND_Data'
+' FROM Family INNER JOIN Now_Doxod ON Family.F_ID = Now_Doxod.ND_FIO' +' WHERE (Now_Doxod.ND_Data Between :period1 And :period2)'
+' GROUP BY Family.F_Name, Now_Doxod.ND_Data;'); DoxQuery.Parameters.ParamByName('period1').Value:=period1; DoxQuery.Parameters.ParamByName('period2').Value:= period2; DoxQuery.Open;
RasQuery.SQL.Clear;
RasQuery.SQL.Add('SELECT Family.F_Name, Sum(Now_Rasxod.NR_Sum) AS [Sum-NR_Sum], Now_Rasxod.NR_Data'
+' FROM Family INNER JOIN Now_Rasxod ON Family.F_ID = Now_Rasxod.NR_FIO' +' WHERE (Now_Rasxod.NR_Data Between :period1 And :period2)'
+' GROUP BY Family.F_Name, Now_Rasxod.NR_Data;'); RasQuery.Parameters.ParamByName('period1').Value:=period1; RasQuery.Parameters.ParamByName('period2').Value:= period2; RasQuery.Open;
Button1.Click;
end;
end;
procedure TBalanc.Excel1Click(Sender: TObject); begin
period1:=Datetostr(DateTimePicker1.Date);
72
period2:=Datetostr(DateTimePicker2.Date); if SaveDialog1.Execute then
SendToExcel(SaveDialog1.FileName,DoxQuery,RasQuery,period1,period2);
end;
end.
Программный код модуля подключения к БД:
unit Data_Modul;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, DB, ADODB,IniFiles;
type
TDM = class(TForm) ADOConnection1: TADOConnection; DoxQuery1: TADOQuery; RasQuery2: TADOQuery; FamilyQuery3: TADOQuery; USSource1: TDataSource; USQuery1: TADOQuery; DataSource1: TDataSource; CheckQuery1: TADOQuery; DoxDataSource1: TDataSource; RasDataSource1: TDataSource; FamilyDataSource4: TDataSource; DoxNQuery4: TADOQuery; RasNQuery5: TADOQuery; DoxNDataSource5: TDataSource; RasNDataSource6: TDataSource; DoxNQuery5: TADOQuery; RasNQuery6: TADOQuery; DoxQuery2: TADOQuery; RasQuery3: TADOQuery; FamilyQuery4: TADOQuery;
procedure DoxQuery1BeforePost(DataSet: TDataSet); procedure RasQuery2BeforePost(DataSet: TDataSet); procedure FamilyQuery3BeforePost(DataSet: TDataSet); procedure ADOConnection1BeforeConnect(Sender: TObject); procedure FormCreate(Sender: TObject);
private
{Private declarations } public
{Public declarations } end;
var
DM: TDM; DBPath:widestring; F:TIniFile;
function GetFileFormat(St: string): string;
function ExtractFileNameEx(FileName: string; ShowExtension: Boolean): string;
implementation
uses Familyes, Rasxods, Doxods, Home_page;
{$R *.dfm}
function GetFileFormat(St: string): string; var
z: integer; n: byte;
begin
for z := length(St) -1 downto 0 do if (St[z] = '.') then
begin
n := z; break;
end; Delete(St, 1, n); Result := St;
end;
function ExtractFileNameEx(FileName: string; ShowExtension: Boolean): string; var
73
I: Integer; S, S1: string;
begin
I:= Length(FileName); if I <> 0 then
begin
while (FileName[i] <> '\') and (i > 0) do i := i - 1; S:= Copy(FileName, i + 1, Length(FileName) - i);
i:= Length(S); if i = 0 then begin
Result:= ''; Exit;
end;
while (S[i] <> '.') and (i > 0) do i := i - 1;
S1:= Copy(S, 1, i - 1); if s1 = '' then
s1:= s;
if ShowExtension = TRUE then Result:= s
else
Result:= s1;
end else
Result:= ''; end;
procedure TDM.ADOConnection1BeforeConnect(Sender: TObject); var
DBPath:widestring;
F:TIniFile; begin
F:=TIniFile.Create(ExtractFilePath(Application.ExeName)+'Options.ini'); DBPath:=F.ReadString('Section_DBPath','Path', ''); ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='+DBPath+'Home_accouting.mdb;Persist Security Info=False'; end;
procedure TDM.DoxQuery1BeforePost(DataSet: TDataSet); begin
if (Doxod.DBEdit2.Text='') or (Doxod.DBEdit3.Text='') then begin
MessageDlg('Введитевседанные!',mtError,[mbOk],0); Abort;
end;
end;
procedure TDM.RasQuery2BeforePost(DataSet: TDataSet); begin
if (Rasxod.DBEdit2.Text='') or (Rasxod.DBEdit3.Text='') then begin
MessageDlg('Введитевседанные!',mtError,[mbOk],0); Abort;
end;
end;
procedure TDM.FamilyQuery3BeforePost(DataSet: TDataSet); begin
if (Family.DBEdit2.Text='') or (Family.DBEdit3.Text='') or (Family.DBEdit4.Text='') or (Family.DBEdit5.Text='') or (Family.DBEdit6.Text='') then
begin
MessageDlg('Введитевседанные!',mtError,[mbOk],0); Abort;
end;
end;
procedure TDM.FormCreate(Sender: TObject); begin
DoxQuery1.Open;
RasQuery2.Open;
FamilyQuery3.Open;
DoxNQuery4.Open;
RasNQuery5.Open;
USQuery1.Open;
end;
end.
74