unit2.pas

实现图书馆数据库管理功能

table1.ReadOnly:=true;
form3.Edit1.SetFocus;
form3.Edit1.SelStart:=0;
form3.Edit1.SelLength:=length(form3.edit1.text);


end;

procedure TForm2.SpeedButton8Click(Sender: TObject);
var strr:string;
begin
speedbutton7.Enabled:=true;
form2.Label15.Visible:=false;
form2.DBText1.Visible:=false;
query1.Active:=false;
strr:='select a.读者编号,a.姓名,b.书籍编号,b.书籍名称,d.图书类别 as 书籍类别,b.出版社,c.借书日期,c.是否超期 ';
strr:=strr+' from (((读者管理 a inner join 借书情况 c on a.读者编号=c.读者编号)inner join 书库管理 b on c.书籍编号=b.书籍编号) inner join 图书类别 d on d.类别代码=b.类别代码) ';
strr:=strr+' where c.读者编号='''+''+'''';
query1.SQL.Clear;
query1.SQL.Add(strr);
query1.Active:=true;
table1.Active:=false;
table_book.Active:=false;
table_bktype.Active:=false;
edit1.Enabled:=false;
edit2.Text:='';
edit3.Text:='';
edit4.Text:='';
end;

procedure tform2.showhowmanybooks(); //此处为在“所借的图书”的dbgrid中的要显示的数据
var strr:string;
begin
readerno2:=form3.readerno;
if readerno2<>'' then
begin
strr:='select a.读者编号,a.姓名,b.书籍编号,b.书籍名称,d.图书类别 as 书籍类别,b.出版社,c.借书日期,c.最长日期,c.是否超期 ';
strr:=strr+' from (((读者管理 a inner join 借书情况 c on a.读者编号=c.读者编号)inner join 书库管理 b on c.书籍编号=b.书籍编号) inner join 图书类别 d on d.类别代码=b.类别代码) ';
strr:=strr+' where c.读者编号='''+edit2.Text+''' order by  c.借书日期';
query1.SQL.Clear;
query1.SQL.Add(strr);
query1.Active:=true;
end;
end;



procedure tform2.checkoveredbook();//此处为是否有超期的判断
var i,borr_day,allowday,n:integer;
    bkno,bkname,bkss:string;
begin
query2.SQL.Clear;
query2.SQL.add('select e.书籍编号,e.书籍名称,g.借书日期,f.可借天数 from ((图书类别 f inner join 书库管理 e on f.类别代码=e.类别代码) inner join 借书情况 g  on e.书籍编号=g.书籍编号) where g.读者编号='''+form3.readerno+'''');
query2.Active:=true;

n:=query2.RecordCount;
for i:=1 to n do
begin
dt1.Date:=query2.fieldbyname('借书日期').Value;
borr_day:=Round(Int(dt2.date-dt1.Date)+1);
allowday:= query2.fieldbyname('可借天数').AsInteger;
bkno:=query2.fieldbyname('书籍编号').AsString;

if borr_day>allowday then
begin
f_chaoqi:=true;
edit1.Enabled:=false;
speedbutton7.Enabled:=false;
bkname:=query2.fieldbyname('书籍名称').Value;
bkss:='您所借阅的《'+bkname+'》已过期';
MessageDlg(bkss, mtwarning,[mbOk], 0);
     t_tem.close;
     t_tem.TableName:='借书情况';
     t_tem.Filtered:=false;
     t_tem.Active:=true;
     t_tem.Locate('书籍编号',bkno,[locaseinsensitive]);

     t_tem.Edit;
     t_tem.FieldByName('是否超期').AsString:='超期';
     t_tem.Post;
     t_tem.Close;


table_book.Active:=false;
table_bktype.Active:=false;
end;
query2.Next;
end;
end;


procedure TForm2.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
  DataCol: Integer; Column: TColumn; State: TGridDrawState);
begin
if query1.Active then
if query1.FieldByName('是否超期').AsString='超期' then
begin
DBGrid1.Canvas.brush.Color := clmaroon;
DBGrid1.Canvas.Font.Color := clwhite;
DBGrid1.DefaultDrawColumnCell(Rect, DataCol, Column, State);

end;
end;

procedure TForm2.Edit1KeyPress(Sender: TObject; var Key: Char);
begin
  if key=#13 then button2.Click;
end;

procedure TForm2.SpeedButton7Click(Sender: TObject);
   var
       hasno1,allowno1,day_1:integer;

   begin
     if table_book.Active then
     begin
     table_write1.Open;;
     table_write1.Edit;
     table_write1.FieldByName('已借册数').Value:= table_write1.FieldByName('已借册数').Value+1;
     table_write1.Post;
     hasno1:=table_write1.FieldByName('已借册数').AsInteger;

     t_tem2.Active:=true;
     t_tem2.Filter:='类型编号='''+table_write1.FieldValues['读者类型']+'''';
     t_tem2.Filtered:=true;
     allowno1:=t_tem2.FieldByName('借出册数').AsInteger;
     if hasno1>=allowno1 then
       begin
        form2.f_chaoce:=true;
        form2.edit1.Enabled:=false;
        speedbutton7.Enabled:=false;
        query1.Close;
        query1.Open;
        messagedlg('您的可借图书册数将满，若再借，请先归还！',mtwarning,[mbok],0);
       end  ;

        TABLE_BKTYPE.Open;
        day_1:=table_bktype.fieldbyname('可借天数').asinteger;
        dt2.Date:=dt2.Date+day_1;
        table_write2.Open;
        table_write2.Append;
        table_write2.FieldByName('读者编号').Value:=edit2.Text;
        table_write2.FieldByName('书籍编号').Value:=edit1.Text;
        table_write2.FieldByName('借书日期').Value:=ddtt0.Date;
        table_write2.FieldByName('最长日期').Value:=formatdatetime('yyyy-mm-dd',dt2.Date);
        table_write2.fieldbyname('是否超期').value:='否';
        table_write2.Post;
        dt2.Date:=dt2.Date-day_1;

//        table_write2.Close;
        table_write.Open;
        table_write.Filter:='书籍编号='''+edit1.Text+'''';
        table_write.Filtered:=true;
        table_write.Edit;
        table_write.FieldByName('是否借出').Value:='是';
        table_write.Post;
        table_write.Close;
        query1.Close;
        query1.Open;
        table_book.Active:=false;
        table_bktype.Active:=false;
        edit1.SelectAll;
       end
       else
       begin
       if edit2.Text='' then
          messagedlg('请按“读者”按钮登陆',mtwarning,[mbyes],0)
       else
          begin
             messagedlg('请正确输入书籍编号',mtwarning,[mbyes],0);
             edit1.SelectAll;
          end;
       end
end;


procedure TForm2.btn_QueryClick(Sender: TObject);
var
   str:string;

   k:extended;
begin
   edt_CDays.Text:='0';
   edt_FNum.Text:='0';
   str:='select 读者管理.读者编号,读者管理.联系电话,读者管理.读者类型,读者管理.姓名,读者管理.已借册数,';
   str:=str+'借书情况.借书日期,书库管理.总库存量,书库管理.书籍编号,';
   str:=str+'书库管理.书籍名称,书库管理.书籍价格,书库管理.类别代码,图书类别.可借天数,图书类别.最少天数 ';
   str:=str+' from ((( 读者类型 inner join 读者管理 ';
   str:=str+' on 读者类型.类型编号 = 读者管理.读者类型) inner join 借书情况 ';
   str:=str+' on 读者管理.读者编号 = 借书情况.读者编号) inner join 书库管理 ';
   str:=str+' on 借书情况.书籍编号 = 书库管理.书籍编号) inner join 图书类别 ';
   str:=str+' on 书库管理.类别代码 = 图书类别.类别代码 ';
   str:=str+' where 借书情况.书籍编号='+ ''''+self.edt_InsideID.Text+'''';
   str:=str+' and 书库管理.是否借出='+''''+'是'+'''';
   self.ADODataSet1.Active:=False;
   self.ADODataSet1.CommandText:=str;
   self.ADODataSet1.Active :=True;
   self.btn_Return.Enabled:=True;
   if self.edt_InsideID.Text='' then
      begin
          messagedlg('请输入“书籍编号”！',mtError,[mbOk],0);

          self.btn_Return.Enabled:=False;
          edt_InsideID.SetFocus;
          edt_InsideID.SelectAll;
          exit;
      end;
   if self.ADODataSet1.Recordset.BOF and self.ADODataSet1.Recordset.EOF then
   begin

       self.btn_Return.Enabled:=False;
       messagedlg('此书未借出或无此书'+#13+'请确认所输入的“书籍编号”是否正确!',mtInformation,[mbOk],0);
       edt_InsideID.SetFocus;
       edt_InsideID.SelectAll;
       exit;
   end ;


   var_BookName:=VarToStr(ADODataSet1.Recordset.Fields['书籍名称'].Value);
   var_BookKind:=VarToStr(ADODataSet1.Recordset.Fields['类别代码'].Value);
   var_BookRemainNum:=VarToStr(ADODataSet1.Recordset.Fields['总库存量'].Value);
   var_ReaderKind:=VarToStr(ADODataSet1.Recordset.Fields['读者类型'].Value);
   var_ReaderID:=VarToStr(ADODataSet1.Recordset.Fields['读者编号'].Value);
   var_ReaderName:=VarToStr(ADODataSet1.Recordset.Fields['姓名'].Value);
   var_MBook:=VarToStr(ADODataSet1.Recordset.Fields['书籍价格'].Value);
   var_JBook:=VarToStr(ADODataSet1.Recordset.Fields['已借册数'].Value);
   var_Phone:=VarToStr(ADODataSet1.Recordset.Fields['联系电话'].Value);
   var_MinDays:=varTostr(ADODataSet1.Recordset.Fields['最少天数'].Value);
   {计算罚款数额}
   var_JDate:=varToStr(FormatDateTime('yy-mm-dd',(ADODataSet1.Recordset.Fields['借书日期'].Value)));
   var_HDate:=VarToStr(FormatdateTime('yy-mm-dd',Now()));
   var_GDays:=VarToStr(ADODataSet1.Recordset.Fields['可借天数'].Value);
   self.DateTimePicker1.Date:=GetCurrentDateTime;
   self.DateTimePicker1.Date:=varTodatetime(var_HDate);
   self.DateTimePicker2.Date:=varTodatetime(var_JDate);
   var_SDays:=intTostr(round(int(self.DateTimePicker1.Date-self.DateTimePicker2.Date)+1));
   var_CDays:=intTostr(strToint(var_SDays)-strToint(var_GDays));
   t_tem3.Close;
   t_tem3.Open;
   k:=t_tem3.fieldbyname('元/天').value;
   var_FNum:=floatTostr(strToint(var_CDays)* k);

   self.edt_BookName.Text:=var_BookName;
   self.edt_BookKind.Text:=var_BookKind;
   self.edt_GDays.Text:=var_GDays;
   self.edt_SDays.Text:=var_SDays;
   self.edt_ReaderID.Text:=var_ReaderID;
   self.edt_ReaderName.Text:=var_ReaderName;
   self.edt_MBook.Text:=var_MBook;
   self.edt_JDate.Text:=var_JDate;
   self.edt_HDate.Text:=var_HDate;
   self.edt_JBook.Text:=var_JBook;
   self.edt_Phone.Text:=var_Phone;
   self.edt_ReaderKind.Text:=var_ReaderKind;
   {判断最小期限}
   if strToint(var_SDays)<strToint(var_MinDays) then
   begin
      messagedlg('此书未到还书期限！',mtInformation,[mbOk],0);
      self.btn_Return.Enabled:=False;
      edt_InsideID.SetFocus;
      edt_InsideID.SelectAll;
      exit;
   end;
   {显示超期天数和罚款数额}
   if strToint(var_CDays)>0 then
   begin
   self.edt_CDays.Text:=var_CDays;
   self.edt_FNum.Text:=var_FNum;
   messagedlg('此书已超期'+var_CDays+'天！ '+'应罚款'+var_FNum+'元！',mtInformation,[mbOk],0);
   end;
   end;



procedure TForm2.btn_ReturnClick(Sender: TObject);
var
  str,s3:string;
  cq:string;
  var_BookTotalNum:integer;
  var_BorrowNum:integer;
begin

  if strToint(edt_CDays.Text)>0 then
  begin
  {添加罚款情况}
  self.ADOTable1.TableName:='罚款情况';
  self.ADOTable1.Open;
  self.ADOTable1.Active:=true;
  self.ADOTable1.Append;
  self.ADOTable1.FieldByName('天数').Value:=edt_CDays.Text;
  self.ADOTable1.FieldByName('读者编号').Value:=edt_ReaderID.Text;
  self.ADOTable1.FieldByName('书籍编号').Value:=edt_InsideID.Text;
  self.ADOTable1.FieldByName('罚款数额').Value:=edt_FNum.Text;
  self.ADOTable1.FieldByName('罚款日期').value:=formatdatetime('yy-mm-dd',now());
  self.ADOTable1.Post;
  self.ADOTable1.Close;
  cq:='超期';
   end
  else
   begin
   cq:='否';
   end;
  { 删除记录}
  str:='delete * from 借书情况 where 书籍编号='+''''+edt_InsideID.Text+'''';
  self.ADOCommand1.CommandText:=str;
  self.ADOCommand1.Execute;
   {库存量+1}
  str:='select 总库存量 from 书库管理 where 书籍编号='+''''+self.edt_InsideID.Text+'''';
  self.ADODataSet1.Active:=False;
  self.ADODataSet1.CommandText:=str;
  self.ADODataSet1.Active:=True;
  if self.ADODataSet1.Recordset.BOF and self.ADODataSet1.Recordset.EOF then
     begin
       exit;
     end;
  var_BookTotalNum:=strToint(varTostr(self.ADODataSet1.Recordset.Fields['总库存量'].Value));
  var_BookTotalNum:=var_BookTotalNum+1;
  str:='update 书库管理 set 书库管理.总库存量='+intTostr(var_BookTotalNum);
  str:=str+' where 书库管理.书籍编号='+''''+self.edt_InsideID.Text+'''';
  self.ADOCommand1.CommandText:=str;
  self.ADOCommand1.Execute;
  {已借册数-1}
  str:='select 已借册数 from 读者管理 where 读者管理.读者编号=';
  str:=str+''''+self.edt_ReaderID.Text+'''';
  self.ADODataSet1.Active:=False;
  self.ADODataSet1.CommandText:=str;
  self.ADODataSet1.Active:=True;
  var_BorrowNum:=strToint(varTostr(self.ADODataSet1.Recordset.Fields['已借册数'].Value));
  var_BorrowNum:=var_BorrowNum-1;
  str:='update 读者管理 set 已借册数='+intTostr(var_BorrowNum);
  str:=str+' where 读者管理.读者编号='+''''+self.edt_ReaderID.Text+'''';
  self.ADOCommand1.CommandText:=str;
  self.ADOCommand1.Execute;
  {更改是否借出}
  str:='update 书库管理 set 是否借出='+''''+'否'+'''';
  str:=str+' where 书籍编号='+''''+self.edt_InsideID.Text+'''';
  self.ADOCommand1.CommandText:=str;
  self.ADOCommand1.Execute;
  
  //添写还书信息
    adodataset1.Active:=false;
    adodataset1.CommandText:='select * from 还书情况';
    adodataset1.Open;
    adodataset1.Append;
    adodataset1.FieldByName('读者编号').AsString:=edt_ReaderID.Text ;
        adodataset1.FieldByName('书籍编号').AsString:=edt_insideID.Text ;
            adodataset1.FieldByName('还书日期').Value:=formatdatetime('yy-mm-dd',now());
            adodataset1.Post;
            adodataset1.Close;

{设初值}
  messagedlg('还书成功!',mtconfirmation,[mbok],0);
  btn_Return.Enabled:=false;
self.edt_BookName.Text:='';
self.edt_BookKind.Text:='';
self.edt_GDays.Text:='';
self.edt_SDays.Text:='';
self.edt_CDays.Text:='';
self.edt_FNum.Text:='';
self.edt_ReaderID.Text:='';
self.edt_ReaderName.Text:='';
self.edt_MBook.Text:='';
self.edt_JDate.Text:='';
self.edt_HDate.Text:='';
self.edt_JBook.Text:='';
self.edt_Phone.Text:='';
self.edt_ReaderKind.Text:='';
self.edt_InsideID.Text:='';
self.btn_Return.Enabled:=False;
self.DateTimePicker1.Date:=GetCurrentDateTime;
  if query1.Active then
     begin
       query1.Close;
       query1.Open;
       showhowmanybooks()
     end;
  if query2.Active then
     begin
         s3:='select b.书籍编号,b.书籍名称,a.图书类别,b.出版社,b.作者名称,b.书籍价格,c.借书日期,c.最长日期,a.最少天数,a.可借天数';
         s3:=s3+' from ((图书类别 a inner join 书库管理 b on a.类别代码=b.类别代码 ) inner join 借书情况 c on b.书籍编号=c.书籍编号)where c.读者编号='''+edit5.text+'''' ;
         query_b2.Close;
         query_b2.SQL.Clear;
         query_b2.SQL.add(s3);
          query_b2.Active:=true;

     end;


end;





procedure TForm2.edt_InsideIDKeyPress(Sender: TObject; var Key: Char);
begin
 if key=#13 then
  begin
   btn_QueryClick(Sender);
  end;
end;

procedure TForm2.Edit5KeyPress(Sender: TObject; var Key: Char);
var s3:string; t_tem4:ttable;
begin
if key=#13 then

if edit5.Text='' then
 begin
 messagedlg('请输入读者编号',mtwarning,[mbyes],0);
 edit5.SetFocus;
 end
 else
 begin
   s3:='select b.书籍编号,b.书籍名称,a.图书类别,b.出版社,b.作者名称,b.书籍价格,c.借书日期,c.最长日期,a.最少天数,a.可借天数';
   s3:=s3+' from ((图书类别 a inner join 书库管理 b on a.类别代码=b.类别代码 ) inner join 借书情况 c on b.书籍编号=c.书籍编号)where c.读者编号='''+edit5.text+'''' ;
   query_b2.Close;
   query_b2.SQL.Clear;
   query_b2.SQL.add(s3);
   query_b2.Active:=true;
   if query_b2.Bof and query_b2.Eof then
     begin
      messagedlg('此读者还未借书',mtwarning,[mbyes],0);
      form2.edit5.SelectAll;
      exit;
     end
    else
        t_tem4:=ttable.Create(self);
        t_tem4.Close;
        t_tem4.TableName:='读者管理';
        t_tem4.Filter:='读者编号='''+edit5.Text+'''';
        t_tem4.Filtered:=true;
        t_tem4.Open;
        edit7.Text:=t_tem4.FieldByName('姓名').Value;
        t_tem4.Filtered:=false;
        t_tem4.Close;

        dbgrid2.Options:=dbgrid2.Options+[dgrowselect];
 end;

end;

procedure TForm2.DBGrid2CellClick(Column: TColumn);
var i:integer;y:Extended; 
begin
if (query_b2.Eof and query_b2.Bof) then
  exit;
  edit9.text:=query_b2.fieldbyname('书籍编号').AsString;
  edit10.Text:=query_b2.fieldbyname('书籍名称').AsString;
  edit11.Text:=query_b2.fieldbyname('借书日期').Value;
  dt4.date:=query_b2.fieldbyname('最长日期').Value;