update20050131.txt

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

COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE P_CALCHOUSEFEE 
(
  V_HOUSE_NO CHAR(12)
)
AS
BEGIN EXIT; END ^


ALTER PROCEDURE P_CALCHOUSEFEE 
(
  V_HOUSE_NO CHAR(12)
)
AS
declare variable v_Line_No Char(3);/*用户所属线路*/
Declare Variable v_Transfer_No Char(6);/*用户所属供电台变*/
declare variable v_used_count numeric(10,2);/*该户应收费用电量合计*/
declare variable v_used_Amount numeric(10,2);/*该户应收费合计*/
declare variable v_sum_count numeric(10,2);/*表见数合计*/
declare variable v_Base_Count numeric(10,2);/*基本用电限量*/
declare variable v_Adjust_Count numeric(10,2);/*调整电量*/
declare variable v_IronMode SmallInt;/*铁损结算模式*/
declare variable v_Iron_Used Integer;/*铁损*/
declare variable v_Trans_Used SmallInt;/*变损*/
declare variable v_Ct SmallInt;/*CT*/
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_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_Elect_Name VarChar(20);/*电价类别名称*/
Declare Variable v_Price Numeric(10,2);/*电价*/
Declare Variable v_Amount Numeric(10,2);/*金额*/
Declare Variable v_arrearage numeric(10,2);/*欠费*/
Declare variable v_LateFee numeric(10,2);/*滞纳金*/
Declare Variable v_2year char(1);/*跨年度标志*/
Declare variable v_ExcessType SmallInt;/*加价类型*/
Declare variable v_ExcessScale Numeric(10,2);/*加价比例*/
Declare variable v_ExcessPrice Numeric(10,2);/*加价单价*/
Declare variable v_ExcessCount Numeric(10,2);/*加价电量*/
Declare variable v_ExcessAmount Numeric(10,2);/*加价金额*/
Begin
   /*删除该户原有统计数据*/
   delete from tb_fee where f_house_no = :v_house_no;
   delete from tb_feeDeTail where f_house_no = :v_house_no;
   Select f_Transfer_No, f_House_No
      From Tb_House
      Where f_House_No = :v_House_No
      Into :v_Transfer_No, :v_House_No;
   Select f_Line_No, f_Transfer_No
      From Tb_Transfer
      Where f_Transfer_No = :v_Transfer_No
      Into :v_Line_No, :v_Transfer_No;
   If (v_Line_No Is Null Or v_Transfer_No Is Null Or v_House_No Is Null) Then
      Exit;
   /*计算该户表见用电量之和*/
   Select Sum(f_Count)
      From Tb_Ammeter
      Where f_house_no = :v_house_no
      Into :v_Sum_Count;
   /*取出CT, 调整，各比例, 基本用电限量, 铁损, 欠费, 加价类别, 以计算用电量*/
   Select f_Ct, f_Adjust_Count,
          f_Rate1, f_Rate2, f_Rate3, f_Base_count, f_Iron_Used,
          f_Elect_No, f_Elect_No1, f_Elect_No2, f_Elect_No3,
          f_2Year, F_ARREARAGE, f_ExcessType
      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_Iron_Used,
           :v_Elect_No, :v_Elect_No1, :v_Elect_No2, :v_Elect_No3,
           :v_2year, :v_Arrearage, :v_ExcessType;
   /*铁损结算方式*/
   Select f_Value From Tb_Option Where f_Option = 'IronMode' Into :v_IronMode;
   If (v_IronMode <> 1 Or v_IronMode Is Null) Then
      v_Iron_Used = 0;/*不加铁损*/
   /*变损及相关计算*/
   v_Trans_Used = 0;
   If (v_Iron_Used > 0) Then/*要加铁损*/
   Begin
      v_Used_Count = v_Sum_Count * v_Ct + v_Adjust_Count;/*表底差额 * CT + 调整 + 铁损*/
      If (v_Used_Count > 0) Then/*有用电时加铁损*/
      Begin
         v_Used_Count = v_Used_Count + v_Iron_Used;
         v_Trans_Used = f_45(v_Used_Count / 100, 0);
         v_Used_Count = v_Used_Count + v_Trans_Used;/*应收费电量*/
         v_Trans_Used = v_Trans_Used + v_Iron_Used;/*变损电量*/
      End
      Else
         v_Iron_Used = 0;
   End
   Else
      v_Used_Count = v_Sum_Count * v_Ct + v_Adjust_Count;/*表底差额 * CT + 调整 */
   /*计算各比例用电量*/
   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
   /*计算加价电量及电费*/
   If (v_ExcessType Is Null) Then
   Begin
      v_ExcessScale = 0;
      v_ExcessPrice = 0;
   End
   Else
   Begin
      Select f_ExcessScale, f_ExcessPrice
         From Tb_Excess
         Where f_ExcessType = :v_ExcessType Into :v_ExcessScale, :v_ExcessPrice;
      v_ExcessCount = f_45(v_Count * v_ExcessScale,0);/*加价电量*/
      v_ExcessAmount = f_45(v_ExcessCount * v_ExcessPrice,2);/*加价金额*/
      If (v_ExcessCount <> 0) Then
         Insert Into Tb_FeeDetail (f_Line_No, f_Transfer_No, f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount)
            Values (:v_Line_No, :v_Transfer_No, :v_House_No, Null, '加价电量', :v_ExcessCount, :v_ExcessPrice, :v_ExcessAmount);
   End
   /*计算滞纳金*/
   if(v_Arrearage > 0) Then/*有欠费*/
   Begin
      if ( v_elect_no = '01' or v_Elect_No = '00') Then/*居民生活用电，滞纳金以每日0.1%计*/
         v_LateFee = v_Arrearage * 0.03;/* 0.001 * 30 */
      Else/*非居民用电*/
      Begin
         /*跨年度时每日千分之三计*/
         if(v_2year='Y' or v_2year='y' or v_2year='1') then
            v_LateFee = v_Arrearage * 0.09;/*0.003 * 30*/
         Else
            v_LateFee = v_Arrearage * 0.06;/* 0.002 * 30 */
      End
      v_LateFee = f_45(v_LateFee,2);
      if(v_LateFee < 1) Then/*滞纳金不足1元时以1元计*/
         v_LateFee = 1;
      Insert Into Tb_FeeDetail (f_Line_No, f_Transfer_No, f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount)
         Values (:v_Line_No, :v_Transfer_No, :v_House_No, Null, '滞纳金', Null, Null, :v_LateFee);
   End
   Else/*没有欠费*/
      v_LateFee = 0;
   /*处理基本用电类别============================================================================*/
   If (v_Elect_No = '10') Then/*低保户应作特殊处理*/
   Begin
      If(v_Count > 30) Then
      Begin
         /*30度按0.402算*/
         Select f_Elect_Name, f_Price, f_Price * 30 From Tb_Elect Where f_Elect_No = '10' Into :v_Elect_Name, :v_Price, :v_Amount;
         Insert Into Tb_FeeDetail (f_Line_No, f_Transfer_No, f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount)
            Values (:v_Line_No, :v_Transfer_No, :v_House_No, NULL, :v_Elect_Name, 30, :v_Price, :v_Amount);
         /*超出30度部分按城市居民生活用电算*/
         Select f_Elect_Name, f_Price, f_Price * (:v_Count-30) From Tb_Elect Where f_Elect_No = '00' Into :v_Elect_Name, :v_Price, :v_Amount;
         Insert Into Tb_FeeDetail (f_Line_No, f_Transfer_No, f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount)
            Values (:v_Line_No, :v_Transfer_No, :v_House_No, NULL, :v_Elect_Name, :v_Count-30, :v_Price, :v_Amount);
      End
      Else--不足30度时
      Begin
         Select f_Elect_Name, f_Price, f_Price * :v_Count From Tb_Elect Where f_Elect_No = '10' Into :v_Elect_Name, :v_Price, :v_Amount;
         Insert Into Tb_FeeDetail (f_Line_No, f_Transfer_No, f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount)
            Values (:v_Line_No, :v_Transfer_No, :v_House_No, NULL, :v_Elect_Name, :v_Count, :v_Price, :v_Amount);
      End
   End
   Else If (v_Elect_No = '11') Then/*五保户特殊处理*/
   Begin
         If (v_Count > 5) Then/*五保户免5度*/
         Begin
             Select f_Elect_Name, f_Price, f_Price * :v_Count From Tb_Elect Where f_Elect_No = '11' Into :v_Elect_Name, :v_Price, :v_Amount;
             Insert Into Tb_FeeDetail (f_Line_No, f_Transfer_No, f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount)
                Values (:v_Line_No, :v_Transfer_No, :v_House_No, NULL, :v_Elect_Name, :v_Count, :v_Price, :v_Amount);
             v_Amount = v_Price * -5;
             Insert Into Tb_FeeDetail (f_Line_No, f_Transfer_No, f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount)
                Values (:v_Line_No, :v_Transfer_No, :v_House_No, NULL, '免费优惠', -5, :v_Price, :v_Amount);
         End
         Else
         Begin
             Select f_Elect_Name, f_Price, f_Price * :v_Count From Tb_Elect Where f_Elect_No = '11' Into :v_Elect_Name, :v_Price, v_Amount;
             Insert Into Tb_FeeDetail (f_Line_No, f_Transfer_No, f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount)
                Values (:v_Line_No, :v_Transfer_No, :v_House_No, NULL, :v_Elect_Name, :v_Count, :v_Price, :v_Amount);
             v_Amount = v_Price * -v_Count;
             Insert Into Tb_FeeDetail (f_Line_No, f_Transfer_No, f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount)
                Values (:v_Line_No, :v_Transfer_No, :v_House_No, NULL, '免费优惠', -:v_Count, :v_Price, :v_Amount);
         End
   End
   Else/*非特殊用用户处理*/
   Begin
      If (v_Elect_No Is Not Null) Then
      Begin
         Select f_Elect_Name, f_Price, :v_Count * f_Price From Tb_Elect Where f_Elect_No = :v_Elect_No Into :v_Elect_Name, :v_Price, :v_Amount;
         Insert Into Tb_FeeDetail (f_Line_No, f_Transfer_No, f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount)
            Values (:v_Line_No, :v_Transfer_No, :v_House_No, NULL, :v_Elect_Name, :v_Count, :v_Price, :v_Amount);
      End
   End
   /*处理基本用电类别============================================================================*/
   /*处理用电类别二、用电类别三 名称, 单价，金额等并插入表中*/
   If (v_Elect_No1 Is Not Null) Then
   Begin
      Select f_Elect_Name, f_Price, :v_Count1 * :v_Price From Tb_Elect Where f_Elect_No = :v_Elect_No1 Into :v_Elect_Name, :v_Price, :v_Amount;
      Insert Into Tb_FeeDetail (f_Line_No, f_Transfer_No, f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount)
         Values (:v_Line_No, :v_Transfer_No, :v_House_No, NULL, :v_Elect_Name, :v_Count1, :v_Price, :v_Amount);
   End
   If (v_Elect_No2 Is Not Null) Then
   Begin
      Select f_Elect_Name, f_Price, :v_Count2 * :v_Price From Tb_Elect Where f_Elect_No = :v_Elect_No2 Into :v_Elect_Name, :v_Price, :v_Amount;
      Insert Into Tb_FeeDetail (f_Line_No, f_Transfer_No, f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount)
         Values (:v_Line_No, :v_Transfer_No, :v_House_No, NULL, :v_Elect_Name, :v_Count2, :v_Price, :v_Amount);
   End
   /*处理电炊用电*/
   If (v_Elect_No3 Is Not Null And v_Count3 <> 0) Then
   Begin
      Select f_Elect_Name, f_Price, :v_Count3 * :v_Price From Tb_Elect Where f_Elect_No = :v_Elect_No3 Into :v_Elect_Name, :v_Price, :v_Amount;
      Insert Into Tb_FeeDetail (f_Line_No, f_Transfer_No, f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount)
         Values (:v_Line_No, :v_Transfer_No, :v_House_No, NULL, :v_Elect_Name, :v_Count3, :v_Price, :v_Amount);
   End
   /*更新用户表相关资料*/
   /*收费项目金额四舍五入*/
   update tb_FeeDetail Set f_Amount = f_45(f_Amount,2) where f_house_no = :v_house_no;
   /*计算应收费*/
   Select Sum(f_Amount) From Tb_FeeDetail Where f_House_no = :v_House_No Into :v_Used_Amount;
   Update Tb_House
         Set f_Sum_Count = :v_Sum_Count,
             f_Used_Count = :v_Used_Count,
             f_Used_Amount = :v_Used_Amount,
             f_Trans_Used = :v_Trans_Used,
             f_LateFee = :v_LateFee
      where f_house_no = :v_house_no;
   Delete From Tb_FeeDetail Where f_House_No = :v_House_No And f_Amount = 0;
End
 ^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;