end;

//修改用戶密碼
function TDM.ModifyPassword(username,newpass:string):boolean;
var
  Query:TADOQuery;
begin
  Query:=TADOQuery.Create(self);
  Query.Connection:=ADOConnection1;
  try
    ADOConnection1.BeginTrans;

    Query.SQL.Text:='update user_info set userpassword=:pass where username=:name';
    Query.Parameters.ParamByName('name').Value:=username;
    Query.Parameters.ParamByName('pass').Value:=newpass;

    Query.ExecSQL;
    Adoconnection1.CommitTrans;
    result:=true;
  except
    Adoconnection1.RollbackTrans;
    result:=false;
  end;
  Query.Close;
  Query.Free;
end;

//刪除圖書數為0的所有圖書信息
function TDM.DeleteBook(bookid:string):boolean;
var
  Query:TADOQuery;
begin
  //result:=false;
  Query:=TADOQuery.Create(self);
  Query.Connection:=ADOConnection1;
  try
    ADOConnection1.BeginTrans;

    Query.SQL.Text:='delete from book_info where bookid=:id ';
    Query.Parameters.ParamByName('id').Value:=bookid;
    query.ExecSQL;
    query.SQL.Clear;
    Query.SQL.Text:='delete from book_stock where bookid=:id' ;
    Query.Parameters.ParamByName('id').Value:=bookid;
    query.ExecSQL;
    dm.ADOConnection1.CommitTrans;
    result:=true;
    dm.InitAdoDatasetBookEdit;        //刷新
    except
       dm.ADOConnection1.RollbackTrans;
       result:=false;
    end;
  Query.Close;
  query.Free;
end;

//獲得庫存中某書總數
function TDM.GetStockBookNum(bookid:string):integer;
var
  Query:TADOQuery;
begin
   result:=0;
   Query:=TADOQuery.Create(self);
   Query.Connection:=ADOConnection1;
   Query.SQL.Text:='select bookinnumber,bookoutnumber from book_stock '+
                   'where bookid=:id';
   Query.Parameters.ParamByName('id').Value:=bookid;
   Query.Open;
   while not Query.Eof do
   begin
       result:=result + Query.FieldByName('bookinnumber').AsInteger -
                                      Query.FieldByName('bookoutnumber').AsInteger;
       Query.Next;
   end;
   Query.Close;
   Query.Free;
end;

//根據書類型獲得類型名
function TDM.GetBookTypeName(typeid:string):string;
var
  Query:TADOQuery;
begin
   Query:=TADOQuery.Create(self);
   Query.Connection:=ADOConnection1;
   Query.SQL.Text:='select typename from book_type where booktypeid=:id';
   Query.Parameters.ParamByName('id').Value:=typeid;
   Query.Open;
   result:=Query.FieldByName('typename').AsString;
   Query.Close;
   Query.Free;
end;

//根據書類型名獲得類型ID
function TDM.GetBookTypeID(typename:string):string;
var
  Query:TADOQuery;
begin
   Query:=TADOQuery.Create(self);
   Query.Connection:=ADOConnection1;
   Query.SQL.Text:='select booktypeid from book_type where typename=:name';
   Query.Parameters.ParamByName('name').Value:=typename;
   Query.Open;
   result:=Query.FieldByName('booktypeid').AsString;
   Query.Close;
   Query.Free;
end;

//獲得某類圖書在庫中總數
function TDM.GetBookTypeNum(value:string):integer; 
var
  Query:TADOQuery;
begin
   Query:=TADOQuery.Create(self);
   Query.Connection:=ADOConnection1;
   Query.SQL.Text:='select bookid from book_INFO where booktypeid=:id';
   Query.Parameters.ParamByName('id').Value:=value;
   Query.Open;
   result:=query.RecordCount;
   Query.Close;
   Query.Free;
end;

//刪除圖書類型
function TDM.DeleteBookType(bookid:string):boolean;
var
  Query:TADOQuery;
begin
   Query:=TADOQuery.Create(self);
   Query.Connection:=ADOConnection1;
   Query.SQL.Add('select booktypeid from book_info where booktypeid='''+bookid+'''');
   Query.Open;
   if query.RecordCount>0 then         //庫中有此類的圖書就不能刪除
   begin
       result:=false;
       messagedlg('有此類圖書，不能刪除此類！!',mtinformation,[mbyes],0);
       Query.Close;
       Query.Free;
       exit;
   end;
   try
     try
       ADOconnection1.BeginTrans;
       Query.SQL.Text:='delete from book_type where booktypeid=:id';
       Query.Parameters.ParamByName('id').Value:=bookid;
       Query.ExecSQL;
       ADOConnection1.CommitTrans;
       result:=true;
     except
       ADOConnection1.RollbackTrans;
       result:=false;
       messagedlg('刪除失敗！!',mtinformation,[mbyes],0);
     end;
   finally
     Query.Close;
     Query.Free;
   end;
end;

//更新圖書類型
function TDM.UpdateBookType(value1,value2:string):boolean;
var
  Query:TADOQuery;
begin
   ADOconnection1.BeginTrans;
   Query:=TADOQuery.Create(self);
  try
   Query.Connection:=ADOConnection1;
   Query.SQL.Text:='update book_type set typename=:name where booktypeid=:id';
   Query.Parameters.ParamByName('name').Value:=value2;
   Query.Parameters.ParamByName('id').Value:=value1;
   Query.ExecSQL;
   ADOConnection1.CommitTrans;
   result:=true;
  except
    ADOConnection1.RollbackTrans;
    result:=false;
  end;
  Query.Close;
  Query.Free;
end;

//插入圖書類型
function TDM.InsertBookType(value1,value2:string):boolean;
var
  Query:TADOQuery;
begin
   Query:=TADOQuery.Create(self);
   Query.Connection:=ADOConnection1;
   Query.SQL.Text:='insert into book_type (booktypeid,typename) values (:id,:name)';
   Query.Parameters.ParamByName('name').Value:=value2;
   Query.Parameters.ParamByName('id').Value:=value1;
   try
     ADOconnection1.BeginTrans;
     Query.ExecSQL;
     ADOConnection1.CommitTrans;
     result:=true;
    except
      ADOConnection1.RollbackTrans;
      result:=false;
    end;
  Query.Close;
  Query.Free;
end;

//打開ADODATASET1
procedure TDM.InitAdoDatasetBookEdit;      
begin
  AdoDatasetBookEdit.Close;
  AdoDatasetBookEdit.CommandText:=' select book_info.* ' +
                             ' from Book_info,book_stock '+
                          ' where book_info.bookid = book_stock.bookid ' +
                          ' and book_stock.booklast=1 order by book_info.bookid asc';
  AdoDatasetBookEdit.CommandType:=cmdtext;
  AdoDatasetBookEdit.Open;
  AdoDatasetBookEdit.Active:=true;
  AdoDatasetBookEdit.Filtered:=false;
end;

//獲得圖書數
function  TDM.CCheckBookNumber(bookid:string):boolean;
var
  Query:TADOQuery;
begin
  Query:=TADOQuery.Create(self);
  Query.Connection:=ADOConnection1;
  Query.SQL.Text:='select booknumber from book_stock where bookid=:id ' +
                  ' and booklast=:last ' ;
  Query.Parameters.ParamByName('id').Value:=bookid;
  Query.Parameters.ParamByName('last').Value:=1;
  query.Open;
  if query.FieldByName('booknumber').AsInteger= 0 then
    result:=true
  else
    result:=false;
  Query.Close;
  query.Free;
end;
//檢查ID（通用）
function TDM.CCheckID(sql:string):boolean;
var
  Query:TADOQuery;
begin
   Query:=TADOQuery.Create(nil);
   Query.Connection:=ADOConnection1;
   query.SQL.Text:=sql;
   query.Open;
   if query.RecordCount >0 then
     result:=true
   else
   begin
       result:= false;
   end;
   query.Close;
   query.Free;
end;

//根據書號獲得書名
function TDM.GetBookName(bookid:string):string;
var
  Query:TADOQuery;
begin
  Query:=TADOQuery.Create(nil);
  Query.Connection:=dm.ADOConnection1;
  Query.SQL.Text:='select bookname from book_info where bookid=:bookid';
  Query.Parameters.ParamByName('bookid').Value:=bookid;
  Query.Open;
  result:=Query.FieldByName('bookname').AsString;
  Query.Close;
  Query.Free;
end;
function TDM.GetAllBookNum:integer; // 獲得庫中圖書總數
var
  Query:TADOQuery;
begin
   result:=0;
   Query:=TADOQuery.Create(self);
   Query.Connection:=ADOConnection1;
   Query.SQL.Text:='select * from book_stock';
   Query.Open;
   while not Query.Eof do
   begin
       result:=result + Query.FieldByName('bookinnumber').AsInteger -
                                      Query.FieldByName('bookoutnumber').AsInteger;
       Query.Next;
   end;
   Query.Close;
   Query.Free;;
end;

end.