createdatabase.sql

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

AS 

Begin
   Select Max(f_House_No) 
      From Tb_House 
      Where f_House_No Like :v_Transfer_No || '%'
      Into :v_House_No;
   If(v_House_No is Null) Then
      v_House_No = v_Transfer_No || '0000';
End
 ^

ALTER PROCEDURE P_GETAMMETER (V_HOUSE_NO CHAR(12))
RETURNS (V_NO1 CHAR(10),
V_NO2 CHAR(10),
V_NO3 CHAR(10),
V_LAST1 NUMERIC(15, 2),
V_LAST2 NUMERIC(15, 2),
V_LAST3 NUMERIC(15, 2),
V_COUNT1 NUMERIC(15, 2),
V_COUNT2 NUMERIC(15, 2),
V_COUNT3 NUMERIC(15, 2))
AS 
  
Declare variable v_Counter smallint;
Declare variable v_no char(10);
Declare variable v_last numeric(10,2);
Declare variable v_count numeric(10,2);
Begin
   v_Counter = 0;
   v_No1 = '';
   v_No2 = '';
   v_No3 = '';
   v_Last1 = -1;
   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_No Is Null) Then/*表号为空*/
         v_No = "";
      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_used_count numeric(10,2);
declare variable v_sum_count numeric(10,2);
declare variable v_Base_Count numeric(10,2);
declare variable v_Adjust_Count Integer;
declare variable v_Trans_Used SmallInt;
declare variable v_Ct SmallInt;
declare variable v_Count  Integer;
declare variable v_Count1 Integer;
declare variable v_Count2 Integer;
declare variable v_Count3 Integer;
declare variable v_Rate1 numeric(10,2);
declare variable v_Rate2 numeric(10,2);
declare variable v_Rate3 numeric(10,2);
declare variable v_old_count 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_old_count
   Do
   Begin
      Execute Procedure p_GetUsedCount :v_House_No, :v_Ammeter_order 
                Returning_Values :v_Count;
      If (v_Count <> v_Old_Count) 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

   /*取出CT, 调整，电炊比, 基本用电限量, 计算用电量*/
   Select f_Ct, f_Adjust_Count, f_Rate1, f_Rate2, f_Rate3, f_Base_count 
      From Tb_House 
      Where f_House_No = :v_House_No 
      Into :v_Ct, :v_Adjust_Count, :v_Rate1, :v_Rate2, :v_Rate3, :v_Base_count;

   /*计算各比例用电量*/
   v_Used_Count = v_Sum_Count * v_Ct + v_Adjust_Count;
   v_Trans_Used = 1 + f_45(v_Used_Count / 100, 0);
   v_Used_Count = v_Used_Count + v_Trans_Used;
   v_Count1 = f_45(v_Rate1 * v_Used_Count, 0);
   v_Count2 = f_45(v_Rate2 * v_Used_Count, 0);
   v_Count = v_Used_Count - v_Count1 - v_Count2;

   if (v_Base_Count > 0) Then/*限制基本用电量*/
   Begin
      v_Count3 = 0;
      if (v_Count > v_Base_Count) Then
      Begin
         v_Count3 = v_Count - v_Base_Count;
         v_Count = v_Base_Count;
      End
   End
   Else
   Begin
      If (v_Rate3 < 1) then/*此时表示电炊用电以基本用电量的比例计*/
      Begin
          v_Count3 = f_45(v_Rate3 * v_Count, 0);
          v_Count = v_Count - v_Count3;
      End
   End
   Update Tb_House
         Set f_Used_Count = :v_Used_Count,
             f_Count1 = :v_Count1,
             f_Count2 = :v_Count2,
             f_Count3 = :v_Count3,
             f_Count = :v_Count,
             f_TransUsed = :v_Trans_Used
      where f_house_no = :v_house_no;
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_CountryAmou