unit1.~pas

报表制作

         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.SQL.Add('and  RtTransaction.出租日期 not in(select top '+ INTTOSTR(sqltopno)  +' RtTransaction.出租日期 FROM RtTransaction');

         //form3.Query1.SQL.Add(' where RtTransaction.出租日期 between '+''''+  datetostr(form1.dxbardatecombo12.Date)+''''+'  and '+ ''''+datetostr(form1.dxbardatecombo1.Date)+''''+ ')');




       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 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.dxBarDateCombo13CloseUp(Sender: TObject);
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;

       if (datetostr(form1.dxbardatecombo12.date)=null)or(datetostr(form1.dxbardatecombo1.date)=null) then
        showmessage('请输入查询日期范围')
       else
        begin
        form3.Query1.Close;
        form3.Query1.SQL.Clear;





         form3.Query1.SQL.Add('select  top 20 atorinfo.姓名,rttransaction.出租日期,rent_cd.影片名称,rttransaction.返还日期,rttransaction.影片编号');
         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.dxbardatecombo12.Date)+'''');

       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;

end;

procedure TForm1.dxBarDateCombo1CloseUp(Sender: TObject);
var i,j,k:string;
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;




       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.影片名称');

       form2.query1.SQL.add(' order by count(rent_cd.影片名称) desc');
       form2.query1.Open;


       pageno:=1;
       



       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);

       statusbar1.Panels.Items[1].Text:='第1页';
       statusbar1.Panels.Items[2].Text:='分页';
       form2.QuickRep1.Preview;

     //form2.Query1.Active:=false;///////////////////;

  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;







        form3.Query1.Close;
        form3.Query1.SQL.Clear;





         form3.Query1.SQL.Add('select  top 20 atorinfo.姓名,rttransaction.出租日期,rent_cd.影片名称,rttransaction.返还日期,rttransaction.影片编号');
         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.Open;
       form3.query1.Active:=true;
       form3.qrdbtext1.DataField:='姓名';
       form3.qrdbtext2.DataField:='出租日期';
       form3.qrdbtext3.DataField:='返还日期';
       form3.QRDBText4.DataField:='影片编号';
       form3.QRDBText5.DataField:='影片名称';

        pageno:=1;


       form3.QRLabel2.Caption:=datetimetostr(form1.dxbardatecombo12.Date);
       form3.QRLabel4.Caption:=datetimetostr(form1.dxbardatecombo1.Date);
       //form3.QuickRep1.Prepare;//没有的话’共多少页’不能正常显示，点有双击问题
       statusbar1.Panels.Items[1].Text:='第1页';
       statusbar1.Panels.Items[2].Text:='分页';
       form3.QuickRep1.Preview;

      // form3.Query1.Active:=false;///////////////////

   end;
  //----------------------------------------------------------------------------//

    if   pagecontrol1.ActivePage.PageIndex=4 then



begin

       form7.qrlabel4.Caption:=datetostr(form1.dxbardatecombo12.Date);
       form7.QRLabel6.Caption:=datetostr(form1.dxbardatecombo1.Date);


         form7.Query1.Close;
         form7.Query1.SQL.Clear;
         form7.Query1.SQL.Add('select sum(atorcharge.金额)as 会员缴费 from atorcharge where atorcharge.操作日期 between '+''''+ datetostr(form1.dxbardatecombo12.Date)+''''+' and '+''''+datetostr(form1.dxbardatecombo1.Date)+'''');
          showmessage(form7.Query1.SQL.CommaText);

         form7.query1.Open;

         form7.qrdbtext1.DataField:='会员缴费';



      if form7.Query1.FieldByName('会员缴费').IsNull then
         begin
         i:='0';
         end
       else
         begin
         
         i:=form7.Query1.FieldByName('会员缴费').AsString;
         end;



         form7.Query2.Close;
         form7.Query2.SQL.Clear;
         form7.Query2.SQL.Add('select sum(freetransaction.金额)as 零租租金,sum(freetransaction.金额)as 零租押金');
         form7.Query2.SQL.Add('from freetransaction');
         form7.Query2.SQL.Add('where freetransaction.操作日期 between'+''''+ datetostr(form1.dxbardatecombo12.Date)+''''+' and '+''''+datetostr(form1.dxbardatecombo1.Date)+''''  +'and 收支分类='+''''+'零租租金'+'''');


         form7.query2.Open;
         form7.query2.Active:=true;
         form7.qrdbtext2.DataField:='零租租金';




        if  form7.Query2.FieldByName('零租租金').IsNull then
        begin
          j:='0';
        end
      else
        begin
         j:=form7.query2.FieldValues['零租租金'];
        end;






         form7.Query3.Close;
         form7.Query3.SQL.Clear;

         form7.Query3.SQL.Add('select sum(freetransaction.金额)as 零租押金');
         form7.Query3.SQL.Add('from freetransaction');
         form7.Query3.SQL.Add('where freetransaction.操作日期 between' +''''+ datetostr(form1.dxbardatecombo12.Date)+''''+' and '+''''+datetostr(form1.dxbardatecombo1.Date)+''''     +' and 收支分类='+''''+'零租押金'+'''');

         form7.query3.Open;

         form7.qrdbtext3.DataField:='零租押金';








         form7.Query4.Close;
         form7.Query4.SQL.Clear;
         form7.Query4.SQL.Add('select sum(freetransaction.金额)as 影片进货');
         form7.Query4.SQL.Add('from freetransaction');
         form7.Query4.SQL.Add('where freetransaction.操作日期  between '  +''''+ datetostr(form1.dxbardatecombo12.Date)+''''+' and '+''''+datetostr(form1.dxbardatecombo1.Date)+''''       +' and 收支分类 = '+''''+'影片进货'+'''');

         form7.query4.Open;
         form7.query4.Active:=true;
         form7.qrdbtext4.DataField:='影片进货';

  

        if  form7.Query4.FieldByName('影片进货').IsNull then
        begin
          k:='0';
        end
      else
        begin
         k:=form7.query4.FieldValues['影片进货'];
        end;

         pageno:=1;



        if strtoint(i)+strtoint(j)-strtoint(k)>=0 then
           form7.QRLabel13.Caption:='恭喜您!从'+form7.QRLabel2.Caption+'至'+form7.QRLabel4.Caption+'日期范围内,你经营获利'+inttostr(strtoint(i)+strtoint(j)-strtoint(k))+'元'
        else
          begin
           form7.QRLabel13.Caption:='从'+form7.QRLabel4.Caption+'至'+form7.QRLabel6.Caption+'日期范围内,你经营亏损'+inttostr(strtoint(i)+strtoint(j)-strtoint(k))+'元';
          end;

       form7.QuickRep1.Prepare;//没有的话’共多少页’不能正常显示，点有双击问题
       statusbar1.Panels.Items[1].Text:='共1页';
       statusbar1.Panels.Items[2].Text:='全页';
       form7.QuickRep1.Preview;





       // form7.Query1.Active:=false;///////////////////
       // form7.Query2.Active:=false;///////////////////
       // form7.Query3.Active:=false;///////////////////
       // form7.Query4.Active:=false;///////////////////






  end;

end;

procedure TForm1.dxBarLargeButton1Click(Sender: TObject);
   var YQtime: string;
  var yqdayno:integer;