control_roomu.~pas

学生公寓管理系统 很好的设计 呵呵 绝对很好

unit Control_roomU;

interface

uses DAHelper,ClassesU,ADODB,SysUtils;
//添加寝室信息
function AddRoom(Room:TRoom):boolean;
//修改寝室信息
function EditRoom(Room:TRoom):boolean;
//删除寝室信息
function DelRoom(ID:integer):boolean;
//得到寝室信息
function GetRoomArray:TADOQuery;
//查找寝室by寝室号
function GetRoomByRoomNo(roomNo,houseNo:string):TRoom;
//查找寝室by寝室号
function GetRoomByRoomNoForSearch(roomNo,houseNo:string):TADOQuery;
//得到某一个公寓的寝室信息
function GetRoomArrayByHouseID(houseID:string):TADOQuery;
//查找空置的床位
function GetNoUseInRoom(houseNo:string):TADOQuery;
implementation
function GetNoUseInRoom(houseNo:string):TADOQuery;
var
  sql:string;
begin
   if(houseNo='全部') then
     sql:='SELECT a.公寓号, a.寝室号, ISNULL(a.可住人数 - b.已住人数, a.可住人数) AS 空床数'+
        ' FROM 寝室信息 a LEFT OUTER JOIN '+
        ' (SELECT COUNT(*) 已住人数, 公寓号, 寝室号 FROM 学生信息 '+
        ' GROUP BY 寝室号, 公寓号) b ON a.公寓号 = b.公寓号 AND a.寝室号 = b.寝室号'
   else
     sql:='SELECT a.公寓号, a.寝室号, ISNULL(a.可住人数 - b.已住人数, a.可住人数) AS 空床数'+
        ' FROM (SELECT * FROM 寝室信息 WHERE 公寓号 = +'+''''+houseNo+''''+
        ') a LEFT OUTER JOIN '+
        ' (SELECT COUNT(*) 已住人数, 公寓号, 寝室号 FROM 学生信息 '+
        ' GROUP BY 寝室号, 公寓号) b ON b.公寓号 ='+
        ''''+houseNo+''''+' AND a.寝室号 = b.寝室号';
  result:=DAHelper.ExeSqlQuery(sql);
end;
function GetRoomArrayByHouseID(houseID:string):TADOQuery;
var
  sql:string;
begin
  sql:='select id,寝室号,可住人数,住宿费用,isnull(电话,'''') 电话,公寓号,'+
       'isnull(备注,'''') 备注 from 寝室信息 where '+
       ' 公寓号='+''''+houseID+'''';
  result:=DAHelper.ExeSqlQuery(sql);
end;
function GetRoomByRoomNoForSearch(roomNo,houseNo:string):TADOQuery;
var
  sql:string;
  Room:TRoom;
begin
  if houseNo='' then
    begin
      sql:='select id,寝室号,可住人数,住宿费用,isnull(电话,'''') 电话,公寓号,'+
           ' isnull(备注,'''') 备注 from 寝室信息 where 寝室号='+
           ''''+roomNo+'''';
    end
  else
    begin
      sql:='select id,寝室号,可住人数,住宿费用,isnull(电话,'''') 电话,公寓号,'+
           ' isnull(备注,'''') 备注 from 寝室信息 where 寝室号='+
           ''''+roomNo+''''+' and 公寓号='+''''+HouseNo+'''';
    end;
  result:=DAHelper.ExeSqlQuery(sql);
end;
function GetRoomByRoomNo(roomNo,houseNo:string):TRoom;
var
  sql:string;
  Room:TRoom;
  ADOQuery:TADOQuery;
begin
  ADOQuery:=TADOQuery.Create(nil);
  sql:='select id,寝室号,可住人数,住宿费用,isnull(电话,'''') 电话,公寓号,'+
       ' isnull(备注,'''') 备注 from 寝室信息 where 寝室号='+
       ''''+roomNo+''''+' and 公寓号='+''''+HouseNo+'''';
  ADOQuery:=DAHelper.ExeSqlQuery(sql);
  if(ADOQuery.RecordCount<1) then
    begin
      result:=nil;
    end
  else
      begin
        Room:=TRoom.Create;
        Room.id:=ADOQuery.Fields[0].Value;
        Room.RoomID:=ADOQuery.Fields[1].Value;
        Room.capability:=ADOQuery.Fields[2].Value;
        Room.fee :=ADOQuery.Fields[3].Value;
        Room.tel :=ADOQuery.Fields[4].Value;
        Room.HouseID:=ADOQuery.Fields[5].Value;
        Room.remark:=ADOQuery.Fields[6].Value;
        result:=Room;
      end;
  ADOQuery:=nil;
end;
function GetRoomArray:TADOQuery;
var
  sql:string;
begin
  sql:='select id,寝室号,可住人数,住宿费用,isnull(电话,'''') 电话,公寓号,'+
       'isnull(备注,'''') 备注 from 寝室信息 ';
  result:=DAHelper.ExeSqlQuery(sql);
end;
function DelRoom(ID:integer):boolean;
var
  sql:string;
begin
  sql:='delete from 寝室信息 where id='+inttostr(ID);
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function EditRoom(Room:TRoom):boolean;
var
  sql:string;
begin
  sql:='update 寝室信息 set 寝室号='+''''+Room.RoomID+''''+
       ',可住人数='+inttostr(Room.capability)+
       ',住宿费用='+floattostr(Room.fee)+
       ',电话='+''''+Room.tel+''''+
       ',公寓号='+''''+Room.HouseID+''''+
       ',备注='+''''+Room.remark+''''+
       ' where id='+inttostr(Room.id);
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function AddRoom(Room:TRoom):boolean;
var
  sql:string;
begin
  sql:='insert into 寝室信息(寝室号,可住人数,住宿费用,电话,公寓号,备注)'+
       ' values('+''''+Room.RoomID+''''+','+
       inttostr(Room.capability)+','+floattostr(Room.fee)+
       ','+''''+Room.tel+''''+','+''''+Room.HouseID+''''+
       ','+''''+Room.remark+''''+ ')';
  result:=DAHelper.ExeSqlNoQuery(sql);
end;

end.