📄 unit1.~pas
字号:
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ComCtrls, StdCtrls, ExtCtrls, cxControls, cxContainer, cxEdit,
cxTextEdit, cxMaskEdit, cxDropDownEdit, cxCalendar, QRPrntr, jpeg,
QuickRpt, QRCtrls, WinSkinData, cxSpinEdit, dxCntner, dxEditor, dxExEdtr,
dxEdLib, cxImageComboBox, TeeProcs, TeEngine, Chart, DbChart, DB,
DBTables, Series, cxPC, ADODB, dxBarExtItems, dxBar;
type
TForm1 = class(TForm)
PageControl1: TPageControl;
TabSheet1: TTabSheet;
TabSheet2: TTabSheet;
TabSheet4: TTabSheet;
TabSheet5: TTabSheet;
TabSheet6: TTabSheet;
QRPreview1: TQRPreview;
StatusBar1: TStatusBar;
Image1: TImage;
QRPreview2: TQRPreview;
QRPreview4: TQRPreview;
DBChart1: TDBChart;
Series1: TBarSeries;
GroupBox11: TGroupBox;
RadioButton1: TRadioButton;
RadioButton2: TRadioButton;
RadioButton4: TRadioButton;
RadioButton5: TRadioButton;
QRPreview5: TQRPreview;
Query1: TADOQuery;
dxBarManager1: TdxBarManager;
dxBarSubItem1: TdxBarSubItem;
dxBarSubItem2: TdxBarSubItem;
dxBarListItem1: TdxBarListItem;
dxBarStatic1: TdxBarStatic;
dxBarStatic2: TdxBarStatic;
dxBarStatic3: TdxBarStatic;
dxBarSubItem3: TdxBarSubItem;
dxBarSubItem4: TdxBarSubItem;
dxBarButton1: TdxBarButton;
dxBarSubItem5: TdxBarSubItem;
dxBarStatic4: TdxBarStatic;
dxBarStatic5: TdxBarStatic;
dxBarLargeButton1: TdxBarLargeButton;
dxBarLargeButton2: TdxBarLargeButton;
dxBarDateCombo12: TdxBarDateCombo;
dxBarLargeButton3: TdxBarLargeButton;
dxBarDateCombo1: TdxBarDateCombo;
dxBarStatic6: TdxBarStatic;
dxBarStatic7: TdxBarStatic;
dxBarEdit1: TdxBarEdit;
dxBarLargeButton4: TdxBarLargeButton;
dxBarLargeButton5: TdxBarLargeButton;
CustomdxBarCombo1: TCustomdxBarCombo;
dxBarCombo1: TdxBarCombo;
dxBarCombo3: TdxBarCombo;
procedure Button8Click(Sender: TObject);
procedure cxDateEdit8PropertiesPopup(Sender: TObject);
procedure cxDateEdit7PropertiesPopup(Sender: TObject);
procedure RadioButton2Click(Sender: TObject);
procedure RadioButton4Click(Sender: TObject);
procedure RadioButton5Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure dxBarLargeButton2Click(Sender: TObject);
procedure dxBarDateCombo13CloseUp(Sender: TObject);
procedure dxBarDateCombo1CloseUp(Sender: TObject);
procedure dxBarLargeButton1Click(Sender: TObject);
procedure RadioButton1Click(Sender: TObject);
procedure dxBarCombo3Change(Sender: TObject);
procedure PageControl1Change(Sender: TObject);
procedure dxBarLargeButton3Click(Sender: TObject);
private
public
{ Public declarations }
end;
var
Form1: TForm1;
SqlTopNo: integer=0;
pageno: integer=1;//页数从第2页开始
implementation
uses unit2,unit3,unit4,unit5,unit6,unit7,u_dmd;
{$R *.dfm}
procedure TForm1.Button8Click(Sender: TObject);
begin
if qrpreview2.pagenumber>1 then
qrpreview2.PageNumber:=qrpreview2.PageNumber-1;
StatusBar1.Panels.Items[1].Text:='第'+IntToStr(Form1.QRPreview2.PageNumber)+'页';
statusbar1.Panels.Items[2].Text:='共' +IntToStr(Form3.QuickRep1.PageNumber)+'页';
end;
procedure TForm1.cxDateEdit8PropertiesPopup(Sender: TObject);
begin
radiobutton1.Checked:=false;
radiobutton2.Checked:=false;
radiobutton4.Checked:=false;
radiobutton5.Checked:=false;
end;
procedure TForm1.cxDateEdit7PropertiesPopup(Sender: TObject);
begin
radiobutton1.Checked:=false;
radiobutton2.Checked:=false;
radiobutton4.Checked:=false;
radiobutton5.Checked:=false;
end;
procedure TForm1.RadioButton2Click(Sender: TObject);
var RMaxNO2:string;
var i:integer;
begin
dbchart1.Series[0].YValues.valuesource:='';
dbchart1.Series[0].xlabelssource:='';
//dbchart1.LeftAxis.Maximum:=0;//保证MININUN为最小值就可以了
dbchart1.LeftAxis.Minimum:=0;
dbchart1.LeftAxis.Title.Angle:=270;
dbchart1.LeftAxis.Title.Caption:='会员出租排行TOP 10 (租借部数)';
query1.Close;
query1.SQL.Clear;
query1.SQL.Add('select top 10 atorinfo.姓名, count(atorinfo.姓名)AS 会员总出租部数');//加个AS，问题搞定
query1.SQL.Add('from rttransaction,atorinfo');
query1.SQL.add('where rttransaction.会员编号=atorinfo.会员编号');
query1.SQL.Add('and rttransaction.出租日期 between'+''''+ datetostr(form1.dxbardatecombo12.Date-1)+''''+' and '+''''+datetostr(form1.dxbardatecombo1.Date-1)+'''');
query1.SQL.Add('group by atorinfo.姓名');
query1.SQL.add(' order by count(atorinfo.姓名) desc');
query1.Open;
query1.Active:=true;
query1.first;
RMaxNo2:=query1.FieldByName('会员总出租部数').AsString;
if rmaxno2<>'' then
begin
dbchart1.leftAxis.Automatic:=false;
dbchart1.LeftAxis.Maximum:=strtoint(rmaxno2);//属性里MAX，MIN设置为0，0
if strtoint(rmaxno2)>=10 then
begin
for i:=1 to 9 do
begin
query1.next;
end;
rmaxno2:=query1.FieldByName('会员总出租部数').AsString;
dbchart1.LeftAxis.Minimum:=strtoint(rmaxno2)-1;
dbchart1.Series[0].DataSource:=query1;
dbchart1.Series[0].YValues.valuesource:='会员总出租部数';
dbchart1.Series[0].xlabelssource:='姓名';
end
else
showmessage('请扩大查找范围');
end
else
showmessage('无记录');
end;
procedure TForm1.RadioButton4Click(Sender: TObject);
var RMaxNO3:string;
var i:integer;
begin
dbchart1.Series[0].YValues.valuesource:='';
dbchart1.Series[0].xlabelssource:='';
//dbchart1.LeftAxis.Maximum:=0;//保证MININUN为最小值就可以了
dbchart1.LeftAxis.Minimum:=0;
dbchart1.LeftAxis.Title.Angle:=270;
dbchart1.LeftAxis.Title.Caption:='逾期出租用户TOP 10 (未归还部数)';
query1.Close;
query1.SQL.Clear;
query1.SQL.Add('select top 10 atorinfo.姓名, count(atorinfo.姓名)AS 会员逾期影片数');//加个AS，问题搞定
query1.SQL.Add('from rttransaction,atorinfo');
query1.SQL.add('where rttransaction.会员编号=atorinfo.会员编号 and rttransaction.返还日期 is null');
query1.SQL.Add('and rttransaction.出租日期 between ' +''''+ datetostr(form1.dxbardatecombo12.Date-1)+''''+' and '+''''+datetostr(form1.dxbardatecombo12.Date-1)+'''');
query1.SQL.Add('group by atorinfo.姓名');
//query1.SQL.Add('having count(rttransaction.影片编号)>40'); /////////暂时的
query1.SQL.add(' order by count(atorinfo.姓名) desc');
query1.Open;
query1.Active:=true;
query1.first;
RMaxNo3:=query1.FieldByName('会员逾期影片数').AsString;
if rmaxno3<>'' then
begin
dbchart1.leftAxis.Automatic:=false;
dbchart1.LeftAxis.Maximum:=strtoint(rmaxno3);//属性里MAX，MIN设置为0，0
if strtoint(rmaxno3)>=10 then
begin
for i:=1 to 9 do
begin
query1.next;
end;
rmaxno3:=query1.FieldByName('会员逾期影片数').AsString;
dbchart1.LeftAxis.Minimum:=strtoint(rmaxno3)-1;
dbchart1.Series[0].DataSource:=query1;
dbchart1.Series[0].YValues.valuesource:='会员逾期影片数';
dbchart1.Series[0].xlabelssource:='姓名';
end
else
showmessage('请扩大查找范围');
end
else
showmessage('无记录');
end;
procedure TForm1.RadioButton5Click(Sender: TObject);
var RMaxNO4:string;
var i:integer;
begin
dbchart1.Series[0].YValues.valuesource:='';
dbchart1.Series[0].xlabelssource:='';
//dbchart1.LeftAxis.Maximum:=0;//保证MININUN为最小值就可以了
dbchart1.LeftAxis.Minimum:=0;
dbchart1.LeftAxis.Title.Angle:=270;
dbchart1.LeftAxis.Title.Caption:='当日影片出租TOP 10 (出租部数)';
query1.Close;
query1.SQL.Clear;
query1.SQL.Add('select top 10 rttransaction.出租日期, count(rttransaction.出租日期)AS 当日出租部数');//加个AS，问题搞定
query1.SQL.Add('from rttransaction');
query1.SQL.Add('where rttransaction.出租日期 between ' +''''+ datetostr(form1.dxbardatecombo12.Date-1)+''''+' and '+''''+datetostr(form1.dxbardatecombo1.Date-1)+'''');
query1.SQL.Add('group by rttransaction.出租日期');
query1.SQL.add(' order by count(rttransaction.出租日期) desc');
query1.Open;
query1.Active:=true;
query1.first;
RMaxNo4:=query1.FieldByName('当日出租部数').AsString;
if rmaxno4<>'' then
begin
dbchart1.leftAxis.Automatic:=false;
dbchart1.LeftAxis.Maximum:=strtoint(rmaxno4);//属性里MAX，MIN设置为0，0
if strtoint(rmaxno4)>=10 then
begin
for i:=1 to 9 do
begin
query1.next;
end;
rmaxno4:=query1.FieldByName('当日出租部数').AsString;
dbchart1.LeftAxis.Minimum:=strtoint(rmaxno4)-1;
dbchart1.Series[0].DataSource:=query1;
dbchart1.Series[0].YValues.valuesource:='当日出租部数';
dbchart1.Series[0].xlabelssource:='出租日期';
end
else
showmessage('请扩大查找范围');
end
else
showmessage('无记录');
end;
procedure TForm1.FormCreate(Sender: TObject);
begin
form2:=Tform2.create(Application);
form3:=Tform3.create(Application);
form4:=Tform4.create(Application);
form5:=Tform5.create(Application);
form6:=Tform6.create(Application);
form7:=Tform7.create(Application);
dxbarcombo3.Enabled:=false;///////////
end;
procedure TForm1.dxBarLargeButton2Click(Sender: TObject);
var YQtime: string;
var yqdayno:integer;
begin
dxbarlargebutton1.Enabled:=true;
//------------------------------------------------------------------------//
if pagecontrol1.ActivePage.PageIndex=0 then
begin
with query1 do
begin
close;
sql.Clear;
sql.Add('select top 1 * from rttransaction ');
open;
end;
if query1.RecordCount=0 then
begin
application.messagebox('没有记录!', 'TCSVCD管理系统', mb_iconinformation + mb_defbutton1);
exit;
end;
sqltopno:=sqltopno+20;
pageno:=pageno+1;
image1.Visible:=false;
form2.query1.Close;
form2.query1.SQL.Clear;
form2.query1.SQL.Add('select top 20 rttransaction.影片编号,rent_cd.影片名称, count(rent_cd.影片名称)as 总出租部数');//加个AS，问题搞定
form2.query1.SQL.Add('from rttransaction,rent_cd');
form2.query1.SQL.Add('where rent_cd.影片编号=rttransaction.影片编号');
form2.query1.SQL.Add('and rttransaction.出租日期 between'+''''+ datetostr(form1.dxbardatecombo12.Date-1)+''''+' and '+''''+ datetostr(form1.dxbardatecombo1.Date+1)+'''');
form2.query1.SQL.Add('group by rttransaction.影片编号,rent_cd.影片名称');
//以下HAVING语句为子查询
form2.query1.SQL.Add('having count(rent_cd.影片名称) not in (select top '+ INTTOSTR(sqltopno) +' count(rent_cd.影片名称) ');//xx not in (select xx) 注意XX应该一样
form2.query1.SQL.Add('from rttransaction,rent_cd');
form2.query1.SQL.Add('where rent_cd.影片编号=rttransaction.影片编号');
form2.query1.SQL.Add('and rttransaction.出租日期 between'+''''+ datetostr(form1.dxbardatecombo12.Date-1)+''''+' and '+''''+ datetostr(form1.dxbardatecombo1.Date+1)+'''');
form2.query1.SQL.Add('group by rttransaction.影片编号,rent_cd.影片名称');
form2.query1.SQL.add(' order by count(rent_cd.影片名称) desc )');
//以下为主查询,HAVING语句一般在GROUP语句之后,所以应该把这个语句放在最后
form2.query1.SQL.add(' order by count(rent_cd.影片名称) desc');
form2.query1.Open;
form2.query1.Active:=true;
form2.qrdbtext3.DataField:='影片编号';
form2.qrdbtext5.DataField:='影片名称';
form2.qrdbtext1.DataField:='总出租部数';
form2.QRLabel9.Caption:=datetimetostr(form1.dxbardatecombo12.Date-1);
form2.QRLabel10.Caption:=datetimetostr(form1.dxbardatecombo1.Date-1);
//form2.QuickRep1.Prepare;//没有的话’共多少页’不能正常显示，点有双击问题
statusbar1.Panels.Items[1].Text:='第'+inttostr(pageno)+'页';
statusbar1.Panels.Items[2].Text:='分页';
form2.QuickRep1.Preview;
end;
//------------------------------------------------------------------------//
if pagecontrol1.ActivePage.PageIndex=1 then
begin
with query1 do
begin
close;
sql.Clear;
sql.Add('select top 1 * from rttransaction ');
open;
end;
if query1.RecordCount=0 then
begin
application.messagebox('没有记录!', 'TCSVCD管理系统', mb_iconinformation + mb_defbutton1);
exit;
end;
sqltopno:=sqltopno+20;
pageno:=pageno+1;
form3.Query1.Close;
form3.Query1.SQL.Clear;
form3.Query1.SQL.Add('select top 20 atorinfo.姓名,RtTransaction.出租日期,rent_cd.影片名称,RtTransaction.返还日期,RtTransaction.影片编号,RtTransaction.ID ');
form3.Query1.SQL.Add('from RtTransaction,atorinfo,rent_cd');
form3.Query1.SQL.Add('where RtTransaction.影片编号=rent_cd.影片编号 ');
form3.Query1.SQL.Add('and RtTransaction.会员编号=atorinfo.会员编号 ');
form3.Query1.SQL.Add('and RtTransaction.出租日期 between'+''''+ datetostr(form1.dxbardatecombo12.Date)+''''+' and '+''''+datetostr(form1.dxbardatecombo1.Date)+'''');
form3.Query1.SQL.Add('and RtTransaction.ID not in(select top '+ INTTOSTR(sqltopno) +' RtTransaction.ID FROM RtTransaction,atorinfo,rent_cd');
form3.Query1.SQL.Add('where RtTransaction.影片编号=rent_cd.影片编号 ');
form3.Query1.SQL.Add('and RtTransaction.会员编号=atorinfo.会员编号 ');
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -