update 20041221.sql.txt

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

      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 * 0 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
^

ALTER PROCEDURE P_HOUSEMONTHFEE 
(
  V_HOUSE_NO CHAR(12)
)
AS
Begin
   Execute procedure p_CalcHouseFee :v_House_No;
End
^


ALTER PROCEDURE P_REBUILDTRANSFERFEE
(
  V_TRANSFER_NO CHAR(6)
)
AS
Declare variable v_house_no char(12);
Begin
   For
      Select f_house_no
         From Tb_House
         Where f_transfer_no = :v_transfer_no
         Into :v_house_no
   Do
   Begin
      Execute Procedure P_CalcHouseFee :v_House_No;/*以当前值计算用户的电费*/
   End
   Delete From Tb_FeeDetail Where f_Transfer_No = :v_Transfer_No And f_Amount = 0;
End
^
--完全重新统计电费
Create PROCEDURE P_REBUILDFEE
AS
Declare Variable v_Transfer_No char(12);
Begin
   For
      Select f_Transfer_No
         From Tb_Transfer
         Into :v_Transfer_No
   Do
   Begin
      Execute Procedure P_RebuildTransferFee :v_Transfer_No;/*重新计算台变用户的电费*/
   End
End
^

ALTER PROCEDURE P_NEWMONTH 
AS
/*转存上月的抄表数据，并设置新月份开始*/
Declare variable v_Year Integer;
Declare variable v_Month Integer;
Declare variable v_Date Date;
Begin
   /*取得当前日期所对应抄数据数据日期*/
   Execute Procedure P_GetCurrent '' Returning_Values :v_Year, :v_Month;
   v_Date = v_Month || '-01-' || v_Year;
   /*存储本月已抄表的行(本月抄表数不为空)*/
   Insert into tb_ammeter_detail
         (f_date, f_house_no, f_ammeter_Order, f_ammeter_count, f_adjust_count, f_note)
      select  :v_Date, h.f_house_no, a.f_ammeter_Order, a.f_this_count, h.f_adjust_count, h.f_note
         from tb_ammeter a, tb_house h
         where a.f_house_no = h.f_house_no and a.f_this_count is not null;
   /*更新最大用电量及最小用电量*/
   Update tb_Ammeter Set f_MaxCount = f_Count Where f_MaxCount < f_Count;
   Update tb_Ammeter Set f_MinCount = f_Count Where f_MinCount > f_Count;
   /*生成上次抄表数据*/
   Update tb_ammeter
         set f_Ammeter_Last_Count = f_This_Count
      Where f_This_Count is Not NULL;
   /*清除本次抄表数据,所有本次抄表置为未抄-NULL, 减分表数为0, 调整数为0*/
   Update tb_ammeter
         set f_This_Count = NULL, f_Count = 0
      Where f_This_Count is Not NULL;
   Update Tb_House
         Set f_Sum_Count = 0,
             f_Son_Count = 0,
             F_Adjust_Count = 0,
             f_Note = Null,
             f_Used_Count = 0,
             f_Used_Amount = 0,
             F_Arrearage = 0,F_LateFee = 0,f_2Year = '',
             F_Receiver = "" , /*收费员*/
             F_Charge_Date = NULL, /*收费日期*/
             F_Last_Balance = F_Balance, /*上期余额*/
             F_Gathering = 0,/*收款*/
             F_Give_Change = 0,/*找零*/
             F_Actual_Receive = 0, /*实收*/
             F_Balance = 0;/*本期余额*/
   Delete From Tb_Fee;
   Delete From Tb_FeeDetail;
   --整理抄表明细
   v_Year = v_Year - 1;
   v_Date = v_Year||'-01-01';
   Delete From Tb_Ammeter_Detail Where f_Date < :v_Date;
End
 ^
ALTER PROCEDURE P_SETTHIS_COUNT 
(
  V_HOUSE_NO CHAR(12),
  V_AMMETER_ORDER INTEGER,
  V_THIS_COUNT NUMERIC(15, 2),
  V_ADJUST_COUNT NUMERIC(15, 2),
  V_NOTE VARCHAR(50)
)
AS
Declare Variable v_Ammeter_Last_Count Numeric(15,2);
Declare Variable v_Count Numeric(15,2);
begin
   Select f_ammeter_last_count
      From tb_ammeter
      Where f_house_no = :v_house_no and f_ammeter_order = :v_ammeter_order
      Into :v_Ammeter_Last_Count;
   v_Count = v_This_Count - v_Ammeter_Last_Count;
   update tb_ammeter 
      set f_This_Count = :v_This_count, f_Count = :v_Count
      where f_house_no = :v_house_no And f_ammeter_order = :v_ammeter_order;

   update tb_house
      set f_adjust_count = :v_Adjust_count,
          f_Note = :v_Note
      where f_house_no = :v_house_no;

   execute procedure p_CalcHouseFee :v_House_No;
end
^

ALTER PROCEDURE P_SETTHIS_NULL 
(
  V_HOUSE_NO CHAR(12),
  V_AMMETER_ORDER INTEGER
)
AS
Declare variable v_OldThis_Count Numeric(10,2);
begin
   update tb_ammeter 
         set f_This_Count = null, F_Count = 0
      where f_house_no = :v_house_no And f_ammeter_Order = :v_ammeter_Order;
   update tb_house
         set f_adjust_count = 0,  f_Note = ''
      where f_house_no = :v_house_no;
   /*计算此户人家电费*/
   execute procedure p_CalcHouseFee :v_House_No;
end
^

Create TRIGGER TR_HOUSE_UPD FOR TB_HOUSE
ACTIVE BEFORE UPDATE POSITION 0
as
Declare variable v_new_house_no char(12);
Declare variable v_old_house_no char(12);
begin
   if(new.f_house_no <> old.f_house_no) then
   begin
      v_new_house_no = new.f_house_no;
      v_old_house_no = old.f_house_no;
      /*修正所属变压器和卡本*/
      new.f_transfer_no = f_substr(new.f_house_no,1,6);
      new.f_volume_no = f_substr(new.f_house_no,7,7);
      /*修改相关的计量表的户号*/
      Update Tb_Ammeter
            Set f_House_No = :v_new_House_no
         where f_house_no = :v_old_house_no;
      Update Tb_Fee
            Set f_House_No = :v_new_House_no
         where f_house_no = :v_old_house_no;
   End
   If (New.F_Rate1 Is Null or New.F_Rate1 = 0) Then
      New.F_Elect_No1 = Null;
   If (New.F_Rate2 Is Null or New.F_Rate2 = 0) Then
      New.F_Elect_No2 = Null;
   /*当基本用电类别为普通照明且限定照明电量或有电炊比时，用电类别自动设为电炊-04*/
   If (New.F_Base_Count Is Null) Then
      New.F_Base_Count = 0;
   If (New.F_Elect_No = '01' Or New.f_Elect_No = '00' Or New.f_Elect_No = '10') Then
   Begin
      If (New.F_Base_Count > 0) Then
         New.F_Rate3 = 0;
      If (New.F_Base_Count > 0 Or (New.F_Rate3 > 0 And New.F_Rate3 < 1)) Then
         New.F_Elect_No3 = '04';
      Else
         New.F_Elect_No3 = NULL;
      New.f_ExcessType = Null;--照明户不允许设动力加价
   End
   Else
   Begin
      New.F_Base_Count = 0;
      New.F_Rate3 = 0;
      New.F_Elect_No3 = NULL;
   End
   If (New.F_Arrearage Is Null) Then/*欠费数据为NULL时自动转为0*/
      New.F_Arrearage = 0;
   If (New.F_2Year Is Null) Then/*跨年度标志为空时置为''*/
      New.F_2Year = '';
End
^

CREATE TRIGGER TR_HOUSE_AFTUPD FOR TB_HOUSE 
ACTIVE AFTER UPDATE POSITION 0
as
Declare variable v_house_no char(12);
Declare variable v_ExcessType SmallInt;
begin
   /*在欠费/加价类型/打印发票 数据被修改时应重新计算应收费*/
   If (New.F_Arrearage Is Null) Then
      New.F_Arrearage = 0;
   v_ExcessType = Old.f_ExcessType;
   If (v_ExcessType Is Null) Then
      v_ExcessType = 0;
   If ( (New.F_Arrearage <> Old.F_Arrearage) 
     Or (New.f_ExcessType <> v_ExcessType) 
     Or (New.f_PrintInvoice <> Old.f_PrintInvoice) )Then
   Begin
      v_house_no = new.f_house_no;
      Execute Procedure P_CalcHouseFee :v_House_No;
   End
End
^

Drop Procedure p_AddFee
^
Drop Procedure P_HouseFee
^
Drop Procedure P_HouseUsedCount
^
Drop Procedure p_Extraction
^
Drop Table Tb_ZeroFee
^

--整理原有表底明细
Create Desc Index Idx_Ammeter_Detail_Desc On Tb_Ammeter_Detail(f_House_No)
^
Create Asc Index Idx_Ammeter_Detail_Asc On Tb_Ammeter_Detail(f_House_No)
^