unit Control_feeU;

interface
uses DAHelper,ClassesU,ADODB,SysUtils;
//得到費用集
function GetFeeSetArray:TADOQuery;
//刪除費用
function DelFeeSet(feeID:integer):boolean;
//查找是否存在給定的項目名稱,如果存在返回true
function SearchFeeName(feeName:string):boolean;
//添加收費種類
function AddFee(feeSet:TFeeSet):boolean;
//修改收費種類
function EditFee(feeSet:TFeeSet):boolean;
//得到用戶的繳費記錄
function GetFeeRecordArray(yzid:integer):TADOQuery;
//得到最后一次記錄數
function GetLastRecord(feeID,yzid:integer):double;
//添加收費記錄
function AddFeeRecord(feeRecord:TFeeRecord):boolean;
implementation
function AddFeeRecord(feeRecord:TFeeRecord):boolean;
var
  sql:string;
begin
   sql:='insert into 收費記錄(業主id,項目id,度數,費用,繳費時間) values('+
       inttostr(feeRecord.yzID)+','+inttostr(feeRecord.feeID)+','+
       floattostr(feeRecord.degree)+','+floattostr(feeRecord.fee)+',GETDATE())';
   result:=DAHelper.ExeSqlNoQuery(sql);
end;
function GetLastRecord(feeID,yzid:integer):double;
var
  sql:string;
  query:TADOQuery;
begin
  //在收費記錄中找到某業主某項交費項目中的最新儀表數據
  sql:='select top 1 度數 from 收費記錄 where 項目id='+
            inttostr(feeid)+
            'and 業主id='+inttostr(yzID) +' order by id desc';
  query:=DAHelper.ExeSqlQuery(sql);
  //若收費記錄表中無相關記錄則返回0，否則返回度數字段的值
  if query.Fields[0].IsNull then
    result:=0
  else
    result:=query.Fields[0].Value;
end;
function GetFeeRecordArray(yzid:integer):TADOQuery;
var
  sql:string;
begin
  sql:='select id,業主id,項目id,度數,費用,繳費時間 from 收費記錄 where 業主id='+
       ''''+inttostr(yzid)+'''';
  result:=DAHelper.ExeSqlQuery(sql);
end;
function EditFee(feeSet:TFeeSet):boolean;
var
  sql:string;
begin
  sql:='update 價格表 set 項目='+''''+feeSet.FeeName+''''+',價格='+floattostr(feeSet.Price)+
       ',收費方式='+''''+feeSet.FeeStyle+''''+' where id='+inttostr(feeSet.id);
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
function AddFee(feeSet:TFeeSet):boolean;
var
  sql:string;
begin
  sql:='insert into 價格表(項目,價格,收費方式) values('+''''+feeSet.FeeName+
        ''''+','+floattostr(feeSet.Price)+
       ','+''''+feeSet.FeeStyle+''''+')';
  result:=DAHelper.ExeSqlNoQuery(sql);
end;

function SearchFeeName(feeName:string):boolean;
var
  sql:string;
  query:TADOQuery;
begin
  sql:='select count(*) from 價格表 where 項目='+''''+feeName+'''';
  query:=DAHelper.ExeSqlQuery(sql);
  if query.Fields[0].Value>0 then   //如果有返回值，返回true，否則為false
    result:=true
  else
    result:=false;
  query:=nil;   //銷毀建立的query
end;
function GetFeeSetArray:TADOQuery;
var
  sql:string;
  query:TADOQuery;
begin
  sql:='select id,項目,價格,收費方式 from 價格表';
  query:=DAHelper.ExeSqlQuery(sql);   //執行sql語句，返回值到query
  result:=query;
end;
function DelFeeSet(feeID:integer):boolean;
var
  sql:string;
begin
  sql:='delete from 價格表 where id='+inttostr(feeID);
  result:=DAHelper.ExeSqlNoQuery(sql);
end;
end.