dmfrm.pas

基于Interbase的图书管理系统

       if SPsmalltype.Active then SPsmalltype.Active:=false;
        SPsmalltype.StoredProcName:='INSERT_BOOKSMALLTYPE';
        SPsmalltype.Prepare;
        SPsmalltype.Params[0].AsString:=Abooktype;
        SPsmalltype.Params[1].AsString:=Abigtype;
        SPsmalltype.Params[2].AsString:=Asmallname;
        SPsmalltype.ExecProc;
 dbasebook.Commit;
except
  dbasebook.Rollback;
  Result:=false;
end;
end;

function TDMbook.deletebigtype(Abigtype: string): boolean;
begin
     Result:=true;
  dbasebook.StartTransaction;
try
    if SPbigtype.Active then SPbigtype.Active:=false;
        SPbigtype.StoredProcName:='DELETE_BOOKBIGTYPE';
        try
        SPbigtype.Prepare;
        SPbigtype.Params[0].AsString:=Abigtype;
        SPbigtype.Open;
        except
        end;
    if SPbigtype.ParamByName('ISEXISTS').AsInteger=1 then
       begin
       messagedlg('大类在小类中不为空,不能删除！',mtWarning,[mbOK],0);
        Result:=false;
       end;
   dbasebook.Commit;
 except
   dbasebook.Rollback;
   Result:=false;
 end;
end;

function TDMbook.deletesmalltype(Asmalltype: string): boolean;
var
 tempint:integer;
 temptype:string;
 tempbool:boolean;
begin
  Result:=true;
  tempbool:=true;
  if qrybook.Active=true then qrybook.Close;
  qrybook.Open;
  qrybook.First;
  while not qrybook.Eof do
  begin
    temptype:=qrybook['book_id'];
    tempint:=pos('-',qrybook['book_id'])-1;
    temptype:=copy(temptype,1,tempint);
    if temptype=Asmalltype then
     begin
        messagedlg('小类在书库中不为空,不能删除！',mtWarning,[mbOK],0);
        tempbool:=false;
        break;
     end;
    qrybook.Next;
  end;
if tempbool then
 begin
     dbasebook.StartTransaction;
     try
      if SPsmalltype.Active then SPsmalltype.Active:=false;
        SPsmalltype.StoredProcName:='DELETE_BOOKSMALLTYPE';
        try
        SPsmalltype.Prepare;
        SPsmalltype.Params[0].AsString:=Asmalltype;
        SPsmalltype.ExecProc;
        except
        end;
      dbasebook.Commit;
     except
       dbasebook.Rollback;
      Result:=false;
     end;
  end
 else Result:=false;
end;

procedure TDMbook.closebook;
begin
if Qrybook.Active then Qrybook.close;
qrybook.Filter:=emptystr;
qrybook.Filtered:=false;
end;

procedure TDMbook.openbook;
begin
if Qrybook.Active=true then Qrybook.Close;
Qrybook.Open;
end;

procedure TDMbook.booksave(Abookid,Abookname,Apenster,Aprice,Aremark,Apublish:string;
Anumber:integer;Adate,Apubdate:Tdatetime);
begin
 begin
  dbasebook.StartTransaction;
 try
     with SPbook do
    begin
      Prepare;
      Params[0].AsString:=Abookid;
     //showmessage('eee');
      Params[1].AsString:=Abookname;
      Params[2].AsString:=Apenster;
      Params[3].Asstring:=Aprice;
      Params[4].AsInteger:=Anumber;
      Params[5].AsDateTime:=Adate;
      Params[6].AsBlob:=Aremark;
      Params[7].AsString:=Apublish;
      Params[8].AsInteger:=Anumber;
      Params[9].AsDateTime:=Apubdate;
      ExecProc;
   end;
      dbasebook.Commit;
 except
    dbasebook.Rollback;
    raise;
 end;
end;
end;

procedure TDMbook.bookReside(Abookid: string;Acount:integer);
var
 booksql:string;
begin
 closebook;
 booksql:=Qrybook.SQL.Text;
 Qrybook.SQL.Clear;
 Qrybook.SQL.Add('UPDATE BOOK SET BOOK_RESIDE=BOOK_RESIDE-:ICOUNT');
 Qrybook.SQL.Add('WHERE BOOK_ID=:IBOOKID');
 Qrybook.ParamByName('ICOUNT').AsInteger:=Acount;
 Qrybook.ParamByName('IBOOKID').AsString:=Abookid;
 Qrybook.ExecSQL;
 closebook;
 Qrybook.SQL.Clear;
 Qrybook.SQL.Text:=booksql;
 Qrybook.Open;
end;

function TDMbook.bookISexists(Abook_id: string): boolean;
begin
  Result:=false;
   if SPbook.Active then SPbook.Active:=false;
  try
  SPbook.StoredProcName:='SELECT_BOOK';
  SPbook.Prepare;
  SPbook.Params[0].AsString:=uppercase(Abook_id);
  SPbook.Open;
  except
  // showmessage('exc');
  end;
   if SPbook.ParamByName('RESULT_ID').AsInteger<>0 then
    Result:=true
   else begin
      if SPbook.Active then SPbook.Active:=false;
 SPbook.StoredProcName:='INSERT_BOOK';
        end;
end;

procedure TDMbook.InsertReader(Areaderid, Areaderno, Areadername,
  Areaderdept, Areaderphoto: string; Areaderdodate: Tdatetime);
begin
   dbasebook.StartTransaction;
 try
     with SPreader do
    begin
      Prepare;
      Params[0].AsString:=Areaderid;
      Params[1].AsString:=Areaderno;
      Params[2].AsString:=Areadername;
      Params[3].Asstring:=Areaderdept;
      Params[4].AsDatetime:=Areaderdodate;
      if Areaderphoto<>Emptystr then
      Params[5].LoadFromFile(Areaderphoto,ftGraphic);
      ExecProc;
   end;
      dbasebook.Commit;
 except
    dbasebook.Rollback;
    raise;
 end;
end;

function TDMbook.getborrowedreader(Abookid: string): string;
begin
  Result:=Emptystr;
  if Qryborrowitems.Active then Qryborrowitems.Active:=false;
  Qryborrowitems.SQL.Clear;
  Qryborrowitems.SQL.Add('SELECT * FROM BORROWITEMS');
  Qryborrowitems.SQL.Add('WHERE BOOK_ID=:IBOOK_ID');
try
  Qryborrowitems.Prepare;
  Qryborrowitems.ParamByName('IBOOK_ID').AsString:=Abookid;
  Qryborrowitems.Open;
  Qryborrowitems.First;
  while not Qryborrowitems.Eof do
  begin
   if qryborrow.Active=false then qryborrow.Open;
   qryborrow.First;
   qryborrow.Locate('BORROW_ID',Qryborrowitems['BORROW_ID'],[]);
   Qryreader.First;
   Qryreader.Locate('READER_ID',Qryborrow['READER_ID'],[]);
Result:=Result+'['+Qryreader['READER_NO']+']['+Qryreader['READER_ID']+']'+Qryreader['READER_NAME']+#13#10;
   Qryborrowitems.Next;
  end;
 except
 end;
end;

function TDMbook.ReaderISexists(Areaderid, Areaderno: string): Boolean;
begin
 Result:=false;
   if SPreader.Active then SPreader.Active:=false;
  try
 SPreader.StoredProcName:='SELECT_READER';
 SPreader.Prepare;
 SPreader.Params[0].AsString:=Areaderid;
 SPreader.Params[1].AsString:=Areaderno;
 SPreader.Open;
  except
   // showmessage('exc');
  end;
  if SPreader.ParamByName('RESULT_ID').AsInteger<>0 then
    Result:=true;
  if SPreader.ParamByName('RESULT_NO').AsInteger<>0 then
     Result:=true;
  if SPreader.Active then SPreader.Active:=false;
  SPreader.StoredProcName:='INSERT_READER';
end;

procedure TDMbook.QrybookCalcFields(DataSet: TDataSet);
begin
Qrybook['book_borrowed']:=Qrybook['book_number']-Qrybook['book_reside'];
end;

procedure TDMbook.openreader;
begin
if Qryreader.Active=true then Qryreader.Close;
Qryreader.Open; 
end;

procedure TDMbook.closereader;
begin
 if Qryreader.Active then Qryreader.Active:=false;
 qryreader.Filter:=emptystr;
 qryreader.Filtered:=false;
end;

procedure TDMbook.closeborrow;
begin
if Qryborrow.Active then Qryborrow.Close;
qryborrow.Filter:=emptystr;
qryreader.Filtered:=false;
end;

procedure TDMbook.openborrow;
begin
if Qryborrow.Active then Qryborrow.Close;
Qryborrow.Open; 
end;

function TDMbook.getborrowedbook(Areaderid: string): string;
var
 borrowsql:string;
begin
  Result:=Emptystr;
  if Qryborrow.Active then Qryborrow.Active:=false;
  borrowsql:=Qryborrow.SQL.Text;
  Qryborrow.SQL.Clear;
  Qryborrow.SQL.Add('select * from BORROWITEMS');
  Qryborrow.SQL.Add('where BORROW_ID=(SELECT BORROW_ID FROM BORROWBOOKS');
  Qryborrow.SQL.Add('WHERE READER_ID=:IREADER_ID)');
 try
  Qryborrow.Prepare;
  Qryborrow.ParamByName('IREADER_ID').AsString:=Areaderid;
  Qryborrow.Open;
  while not Qryborrow.Eof do
   begin
Result:=Result+'['++Qryborrow['BOOK_ID']+']'+Qryborrow['BOOK_NAME']+#13#10;
   Qryborrow.next;
   end;
 finally
  if Qryborrow.Active then Qryborrow.Active:=false;
  Qryborrow.SQL.Clear;
  Qryborrow.SQL.Text:=borrowsql;
  Qryborrow.Open;
 end;
end;

function TDMbook.insertborrow(Areaderid: string;
  Aborrownumber: integer): integer;
var
 Asql:string;
 tmpint:integer;
begin
  openborrow;
  if Qryborrow.Locate('reader_id',Areaderid,[]) then
    begin
       Result:=Qryborrow['borrow_id'];
       tmpint:=qryborrow['borrow_number']+Aborrownumber;
       closeborrow;
       Asql:=Qryborrow.SQL.Text;
       Qryborrow.SQL.Clear;
       Qryborrow.SQL.Add('update borrowbooks set borrow_number=:Anumber');
       Qryborrow.SQL.Add('where reader_id=:Areader_id');
      try
       Qryborrow.Prepare;
       Qryborrow.ParamByName('Areader_id').AsString:=Areaderid;
       Qryborrow.ParamByName('Anumber').AsInteger:=tmpint;
       Qryborrow.ExecSQL;
      finally
       closeborrow;
       Qryborrow.SQL.Clear;
       Qryborrow.SQL.Text:=Asql;
       Qryborrow.Open;
      end;  
    end
  else begin
        if SPborrow.Active then SPborrow.Active:=false;
        SPborrow.StoredProcName:='INSERT_BORROW_BOOK';
        try
        SPborrow.Prepare;
        SPborrow.Params[0].AsString:=Areaderid;
        SPborrow.Params[1].AsInteger:=Aborrownumber;
        SPborrow.Open;
        except
        end;
       Result:=SPborrow.ParamByName('RBORROW_ID').AsInteger;
      end;
end;



procedure TDMbook.insertborrowitems(Aborrowid, Aborrowday: integer;
  Abookid, Abookname: string; Aborrowdate: Tdatetime);
begin
 try
 SPborrowitems.Prepare;
 SPborrowitems.Params[0].AsInteger:=Aborrowid;
 SPborrowitems.Params[1].AsString:=Abookid;
 SPborrowitems.Params[2].AsString:=Abookname;
 SPborrowitems.Params[3].AsDateTime:=Aborrowdate;
 SPborrowitems.Params[4].AsInteger:=Aborrowday;
 SPborrowitems.ExecProc;
 except
 end;
end;

procedure TDMbook.getborrowdateday(Areaderid, Abookid: string;
  var Aborrowdate, Aborrowday,Arenewaldate,Arenewalday: string);
var
 tempsql:string;
begin
  if Qryborrow.Active then Qryborrow.Active:=false;
  tempsql:=Qryborrow.SQL.Text;
  Qryborrow.SQL.Clear;
  Qryborrow.SQL.Add('select * from BORROWITEMS');
  Qryborrow.SQL.Add('where (BORROW_ID=(SELECT BORROW_ID FROM BORROWBOOKS');
  Qryborrow.SQL.Add('WHERE READER_ID=:IREADER_ID))and(BOOK_ID=:IBOOK_ID)');
  Qryborrow.Prepare;
  Qryborrow.ParamByName('IREADER_ID').AsString:=Areaderid;
  Qryborrow.ParamByName('IBOOK_ID').AsString:=Abookid;
  Qryborrow.Open;
 try
  Aborrowdate:=Qryborrow['BORROW_DATE'];
 except
  Aborrowdate:=Emptystr;
 end;
 try
  Aborrowday:=Qryborrow['BORROW_DAY'];
 except
  Aborrowday:=Emptystr;
 end;
  try
  Arenewaldate:=Qryborrow['RENEWAL_DATE'];
  except
  Arenewaldate:=Emptystr;
  end;
  try
  Arenewalday:=Qryborrow['RENEWAL_DAY'];
  except
   Arenewalday:=Emptystr;
  end;
  if Qryborrow.Active then Qryborrow.Active:=false;
  Qryborrow.SQL.Clear;
  Qryborrow.SQL.Text:=tempsql;
  Qryborrow.Open;
end;

procedure TDMbook.openBorrowitems;
begin
if Qryborrowitems.Active then Qryborrowitems.Active:=false;
  Qryborrowitems.SQL.Clear;
  Qryborrowitems.SQL.Add('SELECT * FROM BORROWITEMS');
  try
  Qryborrowitems.Prepare;
  Qryborrowitems.Open;
  except
  end;
end;

procedure TDMbook.closeBorrowitems;
begin
 if Qryborrowitems.Active then Qryborrowitems.Close;
 qryborrowitems.Filter:=emptystr;
 qryborrowitems.Filtered:=false;
end;

procedure TDMbook.isrenewaled(Areaderid,Abookid:string;var Atempbool:boolean);
var
 Rewsql:string;
begin
  Atempbool:=false;
  if QryRenewal.Active then QryRenewal.Active:=false;
  Rewsql:=QryRenewal.SQL.Text;
  QryRenewal.SQL.Clear;
  QryRenewal.SQL.Add('select * from BORROWITEMS');
  QryRenewal.SQL.Add('where (BORROW_ID=(SELECT BORROW_ID FROM BORROWBOOKS');
  QryRenewal.SQL.Add('WHERE READER_ID=:IREADER_ID))and(BOOK_ID=:IBOOK_ID)');
  QryRenewal.Prepare;
  QryRenewal.ParamByName('IREADER_ID').AsString:=Areaderid;
  QryRenewal.ParamByName('IBOOK_ID').AsString:=Abookid;
  QryRenewal.Open;
  Atempbool:=(QryRenewal['RENEWAL_DAY']>0);
  if QryRenewal.Active then QryRenewal.Active:=false;
  QryRenewal.SQL.Clear;
  QryRenewal.SQL.Text:=Rewsql;
  QryRenewal.Open;
end;

procedure TDMbook.insertborrowhistory(Areaderid, Abookid,
  Abookname: string; Aborrowdate: Tdatetime; Aborrowday: integer);
begin
 try
  SPborrowhistory.Prepare;
  SPborrowhistory.Params[0].AsString:=Areaderid;
  SPborrowhistory.Params[1].AsString:=Abookid;
  SPborrowhistory.Params[2].AsString:=Abookname;
  SPborrowhistory.Params[3].AsDateTime:=Aborrowdate;
  SPborrowhistory.Params[4].AsInteger:=Aborrowday;
  SPborrowhistory.ExecProc;
 except
 end;
end;

procedure TDMbook.renewalbook(Areaderid, Abookid:string;
Arenewaldate:Tdatetime;Arenewalday: integer);
begin
     dbasebook.StartTransaction;
 try
     with SPrenewal do
    begin
      Prepare;
      Params[0].AsString:=Areaderid;
      Params[1].AsString:=Abookid;
      Params[2].AsDateTime:=Arenewaldate;
      Params[3].AsInteger:=Arenewalday;
      ExecProc;
   end;
      dbasebook.Commit;
 except
    dbasebook.Rollback;
    raise;
 end;
end;

function TDMbook.passdaymothed(Anow, Athen: Tdatetime;
  Aday: integer): integer;
begin
   Result:=DaysBetween(Anow,Athen)-Aday;
end;

function TDMbook.comparedatemothed(A,B: Tdatetime): integer;
var
 temptype:-1..1;
begin
  Result:=0;
   temptype:=CompareDate(A,B);
   case temptype of
    -1:Result:=-1;
     0:Result:=0;
     1:Result:=1;
   end;
end;

procedure TDMbook.returnbook(Areaderid, Abookid: string; Aborrowday,
  Aborrowpassday, Arenewalday, Arenewalpassday: integer; Aborrowdate,
  Arenewaldate, Areturndate: Tdatetime);
begin
      dbasebook.StartTransaction;
 try
     with SPreturn do
    begin
      Prepare;
      Params[0].AsString:=Areaderid;
      Params[1].AsString:=Abookid;
      Params[2].AsDateTime:=Areturndate;
      Params[3].AsDateTime:=Aborrowdate;
      Params[4].AsInteger:=Aborrowday;
      Params[5].AsDateTime:=Arenewaldate;
      Params[6].AsInteger:=Arenewalday;
      Params[7].AsInteger:=Aborrowpassday;
      Params[8].AsInteger:=Arenewalpassday;
      ExecProc;
   end;
      dbasebook.Commit;
 except
    dbasebook.Rollback;
    raise;
 end;
end;

procedure TDMbook.goreaderid(Areaderid: string;var Aresult:boolean);
begin
 Aresult:=false;
   dbasebook.StartTransaction;
 try
     with SPgoid do
    begin
      try
      Prepare;