update 20041210.sql.txt

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

Set Term ^;

--修改照明用电的基本用电量为100
Update Tb_House 
      Set f_Base_Count = 100 
   Where f_Elect_No = '01'
^

--增加城镇居民生活用电类别
Insert Into Tb_Elect (f_Elect_No, f_Elect_Name, f_Trade_No, f_Country_Price, f_Service_Price, f_Price)
   Values ('00','城镇居民生活用电','01',0.45,0,0.45)
^

--修改原居民生活用电为农村居民生活用电及电价
Update Tb_Elect 
      set f_Country_Price = 0.43, f_Price = 0.43, f_Elect_Name = '农村居民生活用电'
   Where f_Elect_No = '01'
^

--修改电炊电价
Update Tb_Elect 
      set f_Country_Price = 0.33, f_Price = 0.33
   Where f_Elect_No = '04'
^

Drop TRIGGER TR_HOUSE_UPD
^
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') 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;
   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
 ^

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);
Declare Variable v_Elect_Name Char(20);
Declare Variable v_Price Numeric(10,2);
Begin
/*因有统计表，所以用电类别不可更改*/
   /*求电费*/
   v_Amount = f_45(v_CountryAmount + v_ServiceAmount,2);
   /*插入分类明细*/
   If (v_Count <> 0) Then
   Begin
      Select f_Elect_Name, f_Price From Tb_Elect Where f_Elect_No = :v_Elect_No Into :v_Elect_Name, v_Price;/*得到用电类别名称*/
      Insert Into Tb_FeeDetail (f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount) 
         Values (:v_House_No, NULL, :v_Elect_Name, :v_Count, :v_Price, :v_Amount);
   End
   If ( v_Elect_No = '01' Or v_Elect_No = '00') 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') ) 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
   /*多个动力用电类别时电量累加*/
   If ( (v_Elect_No = '32') ) Then/*动力用电2*/
   Begin
      Update Tb_Fee
            Set F_Count4 = f_Count4 + :v_Count,
                f_CountryAmount4 = f_CountryAmount4 + :v_CountryAmount,
                f_ServiceAmount4 = f_ServiceAmount4 + :v_ServiceAmount,
                f_Amount4 = f_Amount4 + :v_Amount
         Where f_House_No = :v_House_No;
   End
End
^

ALTER PROCEDURE P_GETHOUSEARREARAGE 
(
  V_HOUSE_NO CHAR(12)
)
RETURNS
(
  V_LATEFEE NUMERIC(15, 2)
)
AS
Declare Variable v_arrearage numeric(10,2);/*欠费*/
Declare Variable v_2year char(1);/*跨年度标志*/
Declare Variable v_elect_no char(2);/*用电类别*/
Begin
   if (Exists(select f_house_no 
                from tb_house 
                where f_house_no = :v_house_no)) Then
   Begin
      select f_elect_no, f_2year, f_Arrearage
         from tb_house 
         where f_house_no = :v_house_no
         into :v_elect_no, v_2year, :v_Arrearage;
      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;
      End
      Else/*没有欠费*/
         v_LateFee = 0;
   End
End
^

ALTER PROCEDURE P_SETPRICE 
AS
Declare variable v_Elect_No Char(2);
Declare variable v_Price Numeric(10,2);
Begin
   /*清除原有价格*/
   Update Tb_House 
         Set F_Price = 0, 
             F_Price1 = 0, 
             F_Price2 = 0,
             F_Price3 = 0;
   For
     Select f_Elect_No,
            f_Price
        From tb_Elect
        Into :v_Elect_No,
             :v_Price
   Do
   Begin
      Update Tb_House
            Set F_Price = :v_Price
         Where f_Elect_No = :V_Elect_No;
      Update Tb_House
            Set F_Price1 = :v_Price
         Where f_Elect_No1 = :V_Elect_No;
      Update Tb_House
            Set F_Price2 = :v_Price
         Where f_Elect_No2 = :V_Elect_No;
      If (v_Elect_No = '04') Then
      Begin/*只更改照明用户*/
           Update Tb_House
               Set F_Price3 = :v_Price
            Where f_Elect_No = '01' Or f_Elect_No = '00';
          End
   End
End
 ^