unit Control_yzU;

interface
uses DAHelper,ClassesU,ADODB,SysUtils;
//得到樓宇記錄
function GetBuildingArray:TADOQuery;
//根據樓宇id得到單元記錄
function GetBUnitByBID(BuildingID:integer):TADOQuery;
//添加樓宇
function AddBuilding(building:TBuilding):boolean;
//添加單元
function AddBUnit(bunit:TBUnit):boolean;
//編輯樓宇
function EditBuilding(building:TBuilding):boolean;
//編輯單元
function EditBUnit(bunit:TBUnit):boolean;
//通過樓牌號和單元號得到業主集
function GetYZByLPHandDYH(lphID,dyhID:integer):TADOQuery;
//添加業主
function AddYZinfo(yz:TYZinfo):boolean;
//更改業主信息
function EditYZinfo(yz:TYZinfo):boolean;
//得到繳費記錄
function GetFeeRecordByYZID(yzid:integer):TADOQuery;
implementation
function GetFeeRecordByYZID(yzid:integer):TADOQuery;
var
  sql:string;
begin
  sql:='select 項目,費用,繳費時間 from 收費記錄,價格表 where 收費記錄.業主id='+
       inttostr(yzID)+' and 項目id=價格表.id order by 繳費時間';
  result:=DAHelper.ExeSqlQuery(sql);
end;
function EditYZinfo(yz:TYZinfo):boolean;
var
  sql:string;
begin
  sql:='UPDATE 業主信息 SET 樓牌號id='+inttostr(yz.LPHid)+',單元號id='+inttostr(yz.DYHid)+
       ',門牌號='+inttostr(yz.mph)+',姓名='+''''+yz.yzName+''''+',工作單位='+
       ''''+yz.yzDW +''''+',面積='+inttostr(yz.yzMJ )+',戶型='+''''+yz.yzHX+''''+
       ',固定電話='+''''+yz.yzTel+''''+',手機='+''''+yz.yzMobile+''''+',備注='+''''+yz.remark +''''+
       ' where id='+inttostr(yz.id);
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function AddYZinfo(yz:TYZinfo):boolean;
var
  sql:string;
begin
   sql:='insert into 業主信息(樓牌號id,單元號id,門牌號,姓名,工作單位,面積,戶型,'+
       '固定電話,手機,備注) values('+inttostr(yz.LPHid)+','+inttostr(yz.DYHid)+','
        +inttostr(yz.mph)+','+''''+yz.yzName+''''+','+''''+yz.yzDW +''''+','+
        inttostr(yz.yzMJ)+','+''''+yz.yzHX+''''+','+''''+yz.yzTel+''''+','+''''+
        yz.yzMobile+''''+','+''''+yz.remark+''''+')';
   result:=DAHelper.ExeSqlNoQuery(sql);
end;
function GetYZByLPHandDYH(lphID,dyhID:integer):TADOQuery;
var
  sql:string;
begin
  sql:='select * from 業主信息 where 樓牌號id='+inttostr(lphID)+
          ' and 單元號id='+inttostr(dyhID);
  result:=DAHelper.ExeSqlQuery(sql);
end;
function EditBUnit(bunit:TBUnit):boolean;
var
  sql:string;
begin
  sql:='update 單元號 set 單元號='+''''+
              bunit.BUnitName+''''+
              ' where id='+inttostr(bunit.id);
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function EditBuilding(building:TBuilding):boolean;
var
  sql:string;
begin
  sql:='update 樓牌號 set 樓牌號='+''''+
              building.BuildingName+''''+' where id='+inttostr(building.id);
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function AddBUnit(bunit:TBUnit):boolean;
var
  sql:string;
begin
  sql:='insert into 單元號(單元號,樓牌號id) values('+''''+bunit.BUnitName+''''+
       ','+inttostr(bunit.buildingID)+')';
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function AddBuilding(building:TBuilding):boolean;
var
  sql:string;
begin
  sql:='insert into 樓牌號(樓牌號) values('+''''+building.BuildingName+''''+')';
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function GetBUnitByBID(BuildingID:integer):TADOQuery;
var
  sql:string;
begin
  sql:='select id,單元號 from 單元號 where 樓牌號id='+inttostr(Buildingid);
  result:=DAHelper.ExeSqlQuery(sql);
end;
function GetBuildingArray:TADOQuery;
var
  sql:string;
begin
  sql:='select id,樓牌號 from 樓牌號 order by 樓牌號';
  result:=DAHelper.ExeSqlQuery(sql);
end;
end.