县级供电企业电费核算源码, 在客户处正常运行8年以上, Delphi 5开发,数据库为Interbase/Firebird, 深入使用Procedure和Trigger等, 对入门者具有很好的参考价值

--2002-12-26
--增加非居民用电时,用电类别置为05
--用电类别编码不可更改或增加
--用电类别限制为: 01 02 04 05 31/32
Alter PROCEDURE P_ADDFEE (
  V_HOUSE_NO CHAR(12),
  V_HOUSE_NAME CHAR(20),
  V_ELECT_NO CHAR(2),
  V_COUNT NUMERIC(15, 2),
  V_COUNTRYAMOUNT NUMERIC(15, 2),
  V_SERVICEAMOUNT NUMERIC(15, 2)
)  AS    
Declare Variable V_Amount numeric(10,2);
Begin
/*因有统计表，所以用电类别不可更改*/
   
   /*求电费*/
   v_Amount = f_45(v_CountryAmount + v_ServiceAmount,2);
   If ( v_Elect_No = '01' ) Then/*居民生活用电*/
   Begin
      Update Tb_Fee
            Set F_Count1 = :v_Count,
                f_CountryAmount1 = :v_CountryAmount,
                f_ServiceAmount1 = :v_ServiceAmount,
                f_Amount1 = :v_Amount
         Where f_House_No = :v_House_No;
   End
   Else 
   If ( v_Elect_No = '02' ) Then/*营业性用电*/
   Begin
      Update Tb_Fee
            Set F_Count2 = :v_Count,
                f_CountryAmount2 = :v_CountryAmount,
                f_ServiceAmount2 = :v_ServiceAmount,
                f_Amount2 = :v_Amount
         Where f_House_No = :v_House_No;
   End
   Else
   If ( v_Elect_No = '04' ) Then/*电炊用电*/
   Begin
      Update Tb_Fee
            Set F_Count3 = :v_Count,
                f_CountryAmount3 = :v_CountryAmount,
                f_ServiceAmount3 = :v_ServiceAmount,
                f_Amount3 = :v_Amount
         Where f_House_No = :v_House_No;
   End
   Else
   If ( v_Elect_No = '05' ) Then/*非居民用电*/
   Begin
      Update Tb_Fee
            Set F_Count5 = :v_Count,
                f_CountryAmount5 = :v_CountryAmount,
                f_ServiceAmount5 = :v_ServiceAmount,
                f_Amount5 = :v_Amount
         Where f_House_No = :v_House_No;
   End
   Else
   If ( (v_Elect_No = '31') Or (v_Elect_No = '32') ) Then/*动力用电*/
   Begin
      Update Tb_Fee
            Set F_Count4 = :v_Count,
                f_CountryAmount4 = :v_CountryAmount,
                f_ServiceAmount4 = :v_ServiceAmount,
                f_Amount4 = :v_Amount
         Where f_House_No = :v_House_No;
   End
End

/*2003-01-08*/
/*计算抄表率时算法有错: 应以tb_house为基础去连接tb_ammeter*/
/*应用程序中也存在错误: 报表打印时数据初始化应在报表BeforePrint时,而不是列头BeforePrint
时*/
ALTER PROCEDURE P_COPYRATE (V_TRANSFERNO CHAR(6))
AS 
  
Declare Variable v_ActualHouse Integer;
Declare Variable v_ShouldHouse Integer;
Declare Variable v_CopyHouse Integer;
Declare Variable v_NotCopyHouse Integer;
Declare Variable v_ErrorCopyHouse Integer;
Declare Variable v_HouseNo Char(12);
Begin
   /*实有用户数*/
   Select Count(*) 
      From Tb_House 
      Where f_Transfer_No = :v_TransferNo 
      Into :v_ActualHouse;
   /*应抄数*/
   Select Count(*) 
      From Tb_House 
      Where f_Transfer_No = :v_TransferNo 
      Into :v_ShouldHouse;
   /*实抄数*/
   v_CopyHouse = 0;
   for
      select distinct f_house_no
         from tb_house h join tb_ammeter a 
/*不可写作from tb_ammeter a join tb_house h*/ 
           on a.f_house_no = h.f_house_no and H.f_Transfer_No = :v_TransferNo
         where f_this_count is not Null
         Into :v_houseNo
   do
   Begin
      v_CopyHouse = v_CopyHouse + 1;   
   End
   /*错抄数*/
   Select Count(*) 
      From Tb_House House, Tb_Fee Fee
      Where House.f_House_No = Fee.f_House_no 
            And House.f_Transfer_No = :v_TransferNo
            And Fee.f_Count < 0/*电度为负*/
      Into :v_ErrorCopyHouse;
   v_NotCopyHouse = v_ShouldHouse - v_CopyHouse;
   If(v_ShouldHouse = 0) Then
      v_ShouldHouse = Null;
   If(v_CopyHouse = 0) Then
      v_CopyHouse = Null;
   Update Tb_Transfer 
         Set f_PErrorCopyHouse = 0
      Where (f_Transfer_No = :v_TransferNo) And (f_PErrorCopyHouse Is Null);
   /*更新统计表的值*/
   Update Tb_Transfer 
         Set f_ActualHouse = :v_ActualHouse, 
      f_ShouldHouse = :v_ShouldHouse, 
             f_CopyHouse = :v_CopyHouse,
             f_ErrorCopyHouse = :v_ErrorCopyHouse,
             f_NotCopyHouse = :v_NotCopyHouse,
             f_CopyRate = :v_CopyHouse / :v_ShouldHouse, 
             f_ErrorCopyRate = (:v_ErrorCopyHouse + f_PErrorCopyHouse) / :v_CopyHouse
      Where f_Transfer_No = :v_TransferNo;
End
^