create.sql

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

   v_Last2 = -1;
   v_Last3 = -1;
   v_Count1 = -1;
   v_Count2 = -1;
   v_Count3 = -1;
   For
      Select f_Ammeter_No,
             f_Ammeter_Last_Count,
             f_This_Count
         From Tb_Ammeter
         Where f_House_No = :V_house_no
         Order by f_Ammeter_Order
         Into :v_no, :v_last, :v_count
   Do
   Begin
      v_Counter = v_Counter + 1;   
      If (V_Count is null) Then/*无抄表*/
         v_Count = -1;
      If (v_Counter = 1) Then
      Begin
         v_No1 = v_no;
         v_Last1 = v_Last;
         v_Count1 = v_Count;
      End
      Else If (v_Counter = 2) Then
      Begin
         v_No2 = v_no;
         v_Last2 = v_Last;
         v_Count2 = v_Count;
      End
      Else 
      Begin
         v_No3 = v_no;
         v_Last3 = v_Last;
         v_Count3 = v_Count;
      End
   End
End
 ^

ALTER PROCEDURE P_GETAMMETERNO (V_HOUSE_NO CHAR(12))
RETURNS (V_NO1 CHAR(6),
V_NO2 CHAR(6),
V_NO3 CHAR(6))
AS 

Declare variable v_no char(10);
Declare Variable v_Counter SmallInt;
Declare variable v_len smallint;
Begin
   v_Counter = 0;
   v_No1 = '';
   v_No2 = '';
   v_No3 = '';
   For
      Select f_trim(f_Ammeter_No)
         From Tb_Ammeter
         Where f_House_No = :V_house_no
         Order by f_Ammeter_Order
         Into :v_no
   Do
   Begin
      v_len = f_strlen(f_Trim(v_no))-6;
      if(v_len>0) then
         v_No = f_Substr(v_no, v_len+1, v_len+6);
      v_Counter = v_Counter + 1;   
      If (v_Counter = 1) Then
      Begin
         v_No1 = v_no;
      End
      Else If (v_Counter = 2) Then
      Begin
         v_No2 = v_no;
      End
      Else 
      Begin
         v_No3 = v_no;
      End
   End
End
 ^

ALTER PROCEDURE P_GETUSEDCOUNT (V_HOUSE_NO CHAR(12),
V_AMMETER_ORDER INTEGER)
RETURNS (V_COUNT NUMERIC(15, 2))
AS 

declare variable v_ammeter_model char(10);
declare variable v_ammeter_turn numeric(10,2);
Begin
   /*计算表见用电量*/
   /*取得表的差额和电表型号*/
   Select f_this_count - f_ammeter_last_count,f_ammeter_model
      From tb_ammeter
      Where f_house_no = :v_house_no and f_ammeter_order = :v_ammeter_order
      Into :v_count,:v_ammeter_model;
   If (not v_count is null) then/*未抄或底数为NULL,不计量*/
   begin
      v_count = f_abs(v_count);
      /*取得电表的最大量程*/
      select f_ammeter_turn
         from tb_ammeter_model
         where f_ammeter_model = :v_ammeter_model
         into :v_ammeter_turn;
      /*计量数大于总量程一半,认为是翻转或倒转，但取小的一种算法*/
      If (v_count > (v_ammeter_turn / 2)) Then
         v_count = v_ammeter_turn - v_count;
   end
End
 ^

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 (Not Exists(Select * 
                     From Tb_Fee
                     Where F_House_No = :v_House_No) ) Then
   Begin
      Insert Into Tb_Fee (f_House_No, F_House_Name)
         Values (:v_House_No, :v_House_Name);
   End
   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 = '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
   Else
   If ( v_Elect_No = '21' ) 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
End
 ^

ALTER PROCEDURE P_HOUSEUSEDCOUNT (V_HOUSE_NO CHAR(12))
AS 
 
declare variable v_ammeter_order integer;
declare variable v_count numeric(10,2);
declare variable v_sum_count numeric(10,2);
declare variable v_Base_Count numeric(10,2);
declare variable v_Rate3 numeric(10,2);
declare variable v_oldcount integer;
Begin
   /*计算各表用电量*/

   v_Sum_Count = 0;
   For /*取得该用户每一只表的差额，即用电量*/
      Select f_ammeter_order, f_count
         From tb_ammeter
         Where f_house_no = :v_house_no
         Into :v_ammeter_order, :v_oldcount
   Do
   Begin
      Execute Procedure p_GetUsedCount :v_House_No, :v_Ammeter_order 
                Returning_Values :v_Count;
      If (v_Count <> v_OldCount) Then
      Begin
         Update Tb_Ammeter Set f_Count = :v_Count Where f_House_No = :v_House_No And f_Ammeter_Order = :v_Ammeter_Order;
      End
      If (not v_count is null) then
      begin/*用户的表见数增加, 有些户有多块表*/
         v_Sum_Count = v_Sum_Count + v_Count;
      end
   End
   update tb_house
         set f_sum_count = :v_sum_count
      where f_house_no = :v_house_no;

   /*计算各比例用电量*/
   Select f_Rate3, f_Base_count 
      From Tb_House
      Where f_house_no = :v_house_no
      Into :v_Rate3, :v_Base_count;
   Update Tb_House
         Set f_Count1 = f_45(f_Rate1 * f_Used_Count, 0),
             f_Count2 = f_45(f_Rate2 * f_Used_Count, 0),
             f_Count3 = 0,
             f_Count = f_45(f_Used_Count - f_Count1 - f_Count2, 0)
      where f_house_no = :v_house_no;
   if (v_Base_Count > 0) Then/*限制基本用电量*/
   Begin
      Update Tb_House/*基本用电只计固定度数, 其余计为电炊*/
            Set f_Count3 = f_Count - f_Base_Count,
                f_Count = f_Base_Count
         Where f_House_No = :v_House_No And f_Count > f_Base_Count;
   End
   Else
   Begin
      If (v_Rate3 < 1) then/*此时表示电炊用电以基本用电量的比例计*/
      Begin
         Update Tb_House
               Set f_Count3 = f_45(f_Rate3 * f_Count, 0),
                   f_Count = f_45(f_Count - f_Count3, 0)
         where f_house_no = :v_house_no;
      End
   End
End
 ^

ALTER PROCEDURE P_HOUSESETPRICE (V_HOUSE_NO CHAR(12))
AS 

Declare variable v_Elect_No Char(2);
Declare variable v_Elect_No1 Char(2);
Declare variable v_Elect_No2 Char(2);
Declare variable v_Elect_No3 Char(2);
Declare variable v_Price Numeric(10,2);
Declare variable v_Price1 Numeric(10,2);
Declare variable v_Price2 Numeric(10,2);
Declare variable v_Price3 Numeric(10,2);
Begin
   Select f_Elect_No, f_Elect_No1, f_Elect_No2, f_Elect_No3
      From Tb_House
      Where f_House_No = :v_House_No
      Into :v_Elect_No, :v_Elect_No1, :v_Elect_No2, :v_Elect_No3;
   Select f_Price From Tb_Elect Where :v_Elect_No  = f_Elect_No Into :v_Price;
   Select f_Price From Tb_Elect Where :v_Elect_No1 = f_Elect_No Into :v_Price1;
   Select f_Price From Tb_Elect Where :v_Elect_No2 = f_Elect_No Into :v_Price2;
   Select f_Price From Tb_Elect Where :v_Elect_No3 = f_Elect_No Into :v_Price3;
   if (v_Price  Is Null) Then v_Price  = 0;
   if (v_Price1 Is Null) Then v_Price1 = 0;
   if (v_Price2 Is Null) Then v_Price2 = 0;
   if (v_Price3 Is Null) Then v_Price3 = 0;
   /*清除原有价格*/
   Update Tb_House 
         Set F_Price  = :v_Price, 
             F_Price1 = :v_Price1, 
             F_Price2 = :v_Price2,
             F_Price3 = :v_Price3
      Where f_house_no = :v_house_no;
End
 ^

ALTER PROCEDURE P_HOUSEFEE (V_HOUSE_NO CHAR(12))
AS 
 

Declare variable v_house_name char(20);
Declare variable v_elect_no1 char(2);
Declare variable v_elect_no2 char(2);
Declare variable v_elect_no3 char(2);
Declare variable v_elect_no4 char(2);
Declare variable v_price1 numeric(10,2);
Declare variable v_price2 numeric(10,2);
Declare variable v_price3 numeric(10,2);
Declare variable v_price4 numeric(10,2);
Declare variable v_Count1 numeric(10,2);
Declare variable v_Count2 numeric(10,2);
Declare variable v_Count3 numeric(10,2);
Declare variable v_Count4 numeric(10,2);
Declare variable v_CountryAmount1 numeric(10,2);
Declare variable v_CountryAmount2 numeric(10,2);
Declare variable v_CountryAmount3 numeric(10,2);
Declare variable v_CountryAmount4 numeric(10,2);
Declare variable v_CountryAmount numeric(10,2);
Declare variable v_ServiceAmount1 numeric(10,2);
Declare variable v_ServiceAmount2 numeric(10,2);
Declare variable v_ServiceAmount3 numeric(10,2);
Declare variable v_ServiceAmount4 numeric(10,2);
Declare variable v_ServiceAmount numeric(10,2);
Declare variable v_LateFee numeric(10,2);/*滞纳金*/

Begin
   /*删除该户原有统计数据*/
   delete from tb_fee where f_house_no = :v_house_no;

   Select f_house_name,
          f_elect_no,
          f_elect_no1,
          f_elect_no2,
          f_elect_no3,
          f_Count,
          f_Count1,
          f_Count2,
          f_count3
      From Tb_House
      Where f_house_no = :v_house_no
      Into :v_house_Name,
           :v_elect_no1,
           :v_elect_no2,
           :v_elect_no3,
           :v_elect_no4,
           :v_Count1,
           :v_Count2,
           :v_Count3,
           :v_Count4;
   /*求国家价部分*/
   Execute Procedure p_GetCountry :v_House_No
      Returning_Values :v_Price1, :v_Price2, :v_Price3, :v_Price4,
                       :v_CountryAmount1, :v_CountryAmount2, :v_CountryAmount3, :v_CountryAmount4,
                       :v_CountryAmount;
   /*求维护费部分*/
   Execute Procedure p_GetService :v_House_No
      Returning_Values :v_Price1, :v_Price2, :v_Price3, :v_Price4,
                       :v_ServiceAmount1, :v_ServiceAmount2, :v_ServiceAmount3, :v_ServiceAmount4, 
                       :v_ServiceAmount;
   /*计算滞纳金*/
   Execute Procedure P_GetHouseArrearage :v_House_No Returning_Values :v_LateFee;
   /*各部分金额*/
   Update Tb_House
         Set f_Amount = :v_CountryAmount1 + :v_ServiceAmount1,
             f_Amount1 = :v_CountryAmount2 + :v_ServiceAmount2,
             f_Amount2 = :v_CountryAmount3 + :v_ServiceAmount3,
             f_Amount3 = :v_CountryAmount4 + :v_ServiceAmount4,
             f_LateFee = :v_LateFee
      Where f_House_No = :v_House_No;
   /*把各部分的金额分别插入到相应对帐表的合适位置*/
   Execute Procedure P_AddFee :v_House_No, :v_House_Name, :v_Elect_No1, :V_Count1, :V_CountryAmount1, :V_ServiceAmount1;
   Execute Procedure P_AddFee :v_House_No, :v_House_Name, :v_Elect_No2, :V_Count2, :V_CountryAmount2, :V_ServiceAmount2;
   Execute Procedure P_AddFee :v_House_No, :v_House_Name, :v_Elect_No3, :V_Count3, :V_CountryAmount3, :V_ServiceAmount3;
   Execute Procedure P_AddFee :v_House_No, :v_House_Name, :v_Elect_No4, :V_Count4, :V_CountryAmount4, :V_ServiceAmount4;
   /*计算合计*/
   Update Tb_Fee
         Set f_Count = f_Count1 + f_Count2 + f_Count3 + f_Count4 + f_Count5,
             f_CountryAmount = f_CountryAmount1 + f_CountryAmount2 + f_CountryAmount3 + f_CountryAmount4 + f_CountryAmount5,
             f_ServiceAmount = f_ServiceAmount1 + f_ServiceAmount2 + f_ServiceAmount3 + f_ServiceAmount4 + f_ServiceAmount5,
             f_LateFee = :v_LateFee,
             f_Amount = f_CountryAmount + f_ServiceAmount + f_LateFee
      Where f_House_No = :v_House_No;
End
 ^

ALTER PROCEDURE P_AMMETERMOVE (V_SHOUSE_NO CHAR(12),
V_AMMETER_NO CHAR(10),
V_DHOUSE_NO CHAR(12))
AS 
    
declare variable v_max_ammeter_order integer;
begin
   select max(f_ammeter_order) from tb_ammeter where f_house_no = :v_shouse_no into :v_max_ammeter_order;
   if (v_max_ammeter_order is null) then
      v_max_ammeter_order = 0;
   if (v_ammeter_no = "") then
      update tb_ammeter set f_house_no = :v_dhouse_no,f_ammeter_order = f_ammeter_order + :v_max_ammeter_order
         where f_house_no = :v_shouse_no;
   else
      update tb_ammeter set f_house_no = :v_dhouse_no,f_ammeter_order = f_ammeter_order + :v_max_ammeter_order
         where f_house_no = :v_shouse_no and f_ammeter_no = :v_ammeter_no;
end
 ^

ALTER PROCEDURE P_HOUSEMONTHFEE (V_HOUSE_NO CHAR(12))
AS 

Begin
   /*设置上次抄表数,因系统在数据修改的设置上次抄表数很可能不对，所以此处不可省略此步*/
/*   Execute procedure p_HouseSetLast :v_House_No;*/
   /*计算用电量*/
   Execute Procedure p_HouseUsedCount :v_house_no;
   /*设置单价*/
   Execute Procedure p_HouseSetPrice :v_House_No;
   /*计处各表应收的电费*/
   Execute procedure p_HouseFee :v_House_No;
End
 ^

ALTER PROCEDURE P_SETTHIS_COUNT (V_HOUSE_NO CHAR(12),
V_A