📄 unit1.~pas
字号:
begin
//------------------------------------------------------------------------------//
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;
if sqltopno<0 then
begin
application.messagebox('已经到最后一页!', 'TCSVCD管理系统', mb_iconinformation + mb_defbutton1);
dxbarlargebutton1.Enabled:=false;
exit;
end;
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;
if sqltopno<0 then
begin
application.messagebox('已经到最后一页!', 'TCSVCD管理系统', mb_iconinformation + mb_defbutton1);
dxbarlargebutton1.Enabled:=false;
exit;
end;
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.会员编号 ');
form3.Query1.SQL.Add(' and RtTransaction.出租日期 between '+''''+ datetostr(form1.dxbardatecombo12.Date)+''''+' and '+ ''''+datetostr(form1.dxbardatecombo1.Date)+''''+ 'order by RtTransaction.id)order by RtTransaction.id' );
form3.query1.Open;
form3.query1.Active:=true;
form3.qrdbtext1.DataField:='姓名';
form3.qrdbtext2.DataField:='出租日期';
form3.qrdbtext3.DataField:='返还日期';
form3.QRDBText4.DataField:='影片编号';
form3.QRDBText5.DataField:='影片名称';
form3.QRLabel2.Caption:=datetostr(form1.dxbardatecombo12.date);
form3.QRLabel4.Caption:=datetostr(form1.dxbardatecombo1.date);
//form3.QuickRep1.Prepare;//没有的话’共多少页’不能正常显示，点有双击问题
statusbar1.Panels.Items[1].Text:='第'+inttostr(pageno)+'页';
statusbar1.Panels.Items[2].Text:='分页';
form3.QuickRep1.Preview;
end;
//-------------------------------------------------------------------------//
if pagecontrol1.ActivePage.PageIndex=2 then
begin
with query1 do
begin
close;
sql.Clear;
sql.Add('select TOP 1 rttransaction.影片编号 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;
if sqltopno<0 then
begin
application.messagebox('已经到最后一页!', 'TCSVCD管理系统', mb_iconinformation + mb_defbutton1);
dxbarlargebutton1.Enabled:=false;
exit;
end;
if dxbarcombo3.text='1天' then yqdayno:=1
else if dxbarcombo3.text='3天' then yqdayno:=3
else if dxbarcombo3.text='1个星期' then yqdayno:=7
else if dxbarcombo3.text='2个星期' then yqdayno:=14
else if dxbarcombo3.text='3个星期' then yqdayno:=21
else if dxbarcombo3.text='1个月' then yqdayno:=31
else if dxbarcombo3.text='2个月' then yqdayno:=62
else if dxbarcombo3.text='3个月' then yqdayno:=93
else if dxbarcombo3.text='半年' then yqdayno:=182
else if dxbarcombo3.text='1年' then yqdayno:=365;
YQtime:=datetostr(date-yqdayno);
dxbaredit1.text:=YQtime;
form5.Query1.Close;
form5.Query1.SQL.Clear;
form5.Query1.SQL.Add('select top 20 rttransaction.影片编号,atorinfo.姓名,rent_cd.影片名称,atorinfo.联系电话,atorinfo.家庭地址,rttransaction.出租日期');
form5.Query1.SQL.Add('from rttransaction,rent_cd,atorinfo');
form5.query1.SQL.Add('where rttransaction.影片编号=rent_cd.影片编号 and rttransaction.会员编号=atorinfo.会员编号');
form5.Query1.SQL.Add('and rttransaction.出租日期<='+''''+YQtime+'''' +'and rttransaction.返还日期 is null ');
form5.Query1.SQL.Add('and rttransaction.出租日期 not in (select top '+ INTTOSTR(sqltopno) +'rttransaction.出租日期 from rttransaction');
form5.Query1.SQL.Add('where rttransaction.出租日期<='+''''+YQtime+'''' +'and rttransaction.返还日期 is null )');
form5.Query1.Open;
form5.query1.Active:=true;
form5.qrdbtext1.DataField:='姓名';
form5.qrdbtext2.DataField:='联系电话';
form5.qrdbtext3.DataField:='影片编号';
form5.QRDBText4.DataField:='影片名称';
form5.QRDBText5.DataField:='出租日期';
form5.QRDBText7.DataField:='家庭地址';
form5.QRLabel12.Caption:=datetostr(date-strtodate(dxbaredit1.text));
form5.QRLabel2.Caption:=dxbaredit1.text;
statusbar1.Panels.Items[1].Text:='第'+inttostr(pageno)+'页';
statusbar1.Panels.Items[2].Text:='分页';
form5.QuickRep1.Preview;
end;
form5.Query1.Active:=false;
end;
procedure TForm1.RadioButton1Click(Sender: TObject);
var RMaxNO: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 rent_cd.影片名称,count(rent_cd.影片名称)as 总出租部数');//加个AS，问题搞定
query1.SQL.Add('from rttransaction,rent_cd');
//query1.SQL.Add('where rent_cd.影片编号=rttransaction.影片编号');
query1.SQL.Add('where rttransaction.出租日期 between'+''''+ datetostr(form1.dxbardatecombo12.Date-1)+''''+' and '+''''+ datetostr(form1.dxbardatecombo1.Date+1)+'''');
query1.SQL.Add('group by rent_cd.影片名称');
query1.SQL.add(' order by count(rent_cd.影片名称) desc');
query1.Open;
query1.Active:=true;
query1.first;
RMaxNo:=query1.FieldByName('总出租部数').AsString;
if rmaxno<>'' then
begin
dbchart1.leftAxis.Automatic:=false;
dbchart1.LeftAxis.Maximum:=strtoint(rmaxno);//属性里MAX，MIN设置为0，0
if strtoint(rmaxno)>=10 then
begin
for i:=1 to 9 do
begin
query1.next;
end;
rmaxno:=query1.FieldByName('总出租部数').AsString;
dbchart1.LeftAxis.Minimum:=strtoint(rmaxno)-1;
dbchart1.Series[0].DataSource:=query1;
dbchart1.Series[0].YValues.valuesource:='总出租部数';
dbchart1.Series[0].xlabelssource:='影片名称';
end
else
showmessage('请扩大查找范围');
end
else
showmessage('无记录');
end;
procedure TForm1.dxBarCombo3Change(Sender: TObject);
var YQtime: string;
var yqdayno:integer;
begin
with form5.query1 do
begin
close;
sql.Clear;
sql.Add('select TOP 1 rttransaction.影片编号 from rttransaction ');
open;
end;
if form5.query1.RecordCount=0 then
begin
application.messagebox('没有记录!', 'TCSVCD管理系统', mb_iconinformation + mb_defbutton1);
exit;
end;
if dxbarcombo3.text='1天' then yqdayno:=1
else if dxbarcombo3.text='3天' then yqdayno:=3
else if dxbarcombo3.text='1个星期' then yqdayno:=7
else if dxbarcombo3.text='2个星期' then yqdayno:=14
else if dxbarcombo3.text='3个星期' then yqdayno:=21
else if dxbarcombo3.text='1个月' then yqdayno:=31
else if dxbarcombo3.text='2个月' then yqdayno:=62
else if dxbarcombo3.text='3个月' then yqdayno:=93
else if dxbarcombo3.text='半年' then yqdayno:=182
else if dxbarcombo3.text='1年' then yqdayno:=365;
YQtime:=datetostr(date-yqdayno);
dxbaredit1.text:=YQtime;
form5.Query1.Close;
form5.Query1.SQL.Clear;
form5.Query1.SQL.Add('select top 20 rttransaction.影片编号,atorinfo.姓名,rent_cd.影片名称,atorinfo.联系电话,atorinfo.家庭地址,rttransaction.出租日期');
form5.Query1.SQL.Add('from rttransaction,rent_cd,atorinfo');
form5.query1.SQL.Add('where rttransaction.影片编号=rent_cd.影片编号 and rttransaction.会员编号=atorinfo.会员编号');
form5.Query1.SQL.Add('and rttransaction.出租日期<='+''''+YQtime+'''' +'and rttransaction.返还日期 is null ');
form5.Query1.Open;
form5.query1.Active:=true;
form5.qrdbtext1.DataField:='姓名';
form5.qrdbtext2.DataField:='联系电话';
form5.qrdbtext3.DataField:='影片编号';
form5.QRDBText4.DataField:='影片名称';
form5.QRDBText5.DataField:='出租日期';
form5.QRDBText7.DataField:='家庭地址';
//form5.QRLabel12.Caption:=dxbarcombo3.text;
//form5.QRLabel2.Caption:=dxbaredit1.text;//
statusbar1.Panels.Items[1].Text:='第1页';
statusbar1.Panels.Items[2].Text:='分页';
form5.QuickRep1.Preview;
form5.Query1.Active:=false;///////////////////
end;
procedure TForm1.PageControl1Change(Sender: TObject);
begin
if pagecontrol1.ActivePage.PageIndex=2 then
begin
dxbarcombo3.Enabled:=true;
dxbardatecombo1.Enabled:=false;
dxbardatecombo12.Enabled:=false;
end
else
begin
dxbarcombo3.Enabled:=false;
dxbardatecombo1.Enabled:=true;
dxbardatecombo12.Enabled:=true;
end;
pageno:=0;
end;
procedure TForm1.dxBarLargeButton3Click(Sender: TObject);
begin
showmessage('毛,这个你都要测试');
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -