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

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') 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
^

/*========================================================================================
2001-09-20
为在一部份统计表增加票数功能而做
*/
/*在变压器表中增加电炊票*/
alter table tb_transfer add f_cooks integer default 0 not null
^

update tb_transfer set f_cooks = 0
^

/*计算电炊户数*/

Create Procedure P_CalcCooks
As
Begin
   Exit;
End
^
Alter Procedure P_CalcCooks
As
Declare Variable vTransferNo Char(6);/*台区号*/
Declare Variable vOldCooks Integer;/*原电炊户数*/
Declare Variable vNewCooks Integer;/*电炊户数*/
Begin
   For
      Select f_Transfer_No, f_Cooks From Tb_Transfer Into :vTransferNo, :vOldCooks
   Do
   Begin
      Select Count(*) From Tb_House 
         Where f_Transfer_No = :vTransferNo And f_Count3 > 0 Into :vNewCooks;
      If (vNewCooks <> vOldCooks) Then
         Update Tb_Transfer Set f_Cooks = :vNewCooks Where f_Transfer_No = :vTransferNo;
   End
End
^

Create Procedure P_CalcTransferCooks
As
Begin
   Exit;
End
^
Alter Procedure P_CalcTransferCooks
(
   vTransferNo Char(6)
)
As
Declare Variable vOldCooks Integer;/*原电炊户数*/
Declare Variable vNewCooks Integer;/*电炊户数*/
Begin
   Select f_Cooks From Tb_Transfer Where f_Transfer_No = :vTransferNo Into :vOldCooks ;
   Select Count(*) From Tb_House 
      Where f_Transfer_No = :vTransferNo And f_Count3 > 0 Into :vNewCooks;
   If (vNewCooks <> vOldCooks) Then
      Update Tb_Transfer Set f_Cooks = :vNewCooks Where f_Transfer_No = :vTransferNo;
End
^

/*计算电炊户数*/
Execute Procedure P_CalcCooks
^
Drop View v_Line_Fee;
^
Drop View v_Transfer_Wasting;
^
Drop View v_Transfer_Fee
^

CREATE VIEW V_TRANSFER_FEE (F_LINE_NO, F_TRANSFER_NO, F_TRANSFER_NAME, F_COUNTER, F_Cooks,  F_BUYCOUNT, F_COUNT1, F_COUNTRYAMOUNT1, F_SERVICEAMOUNT1, F_AMOUNT1, F_COUNT2, F_COUNTRYAMOUNT2, F_SERVICEAMOUNT2, F_AMOUNT2, F_COUNT3, F_COUNTRYAMOUNT3, F_SERVICEAMOUNT3, F_AMOUNT3, F_COUNT4, F_COUNTRYAMOUNT4, F_SERVICEAMOUNT4, F_AMOUNT4, F_COUNT5, F_COUNTRYAMOUNT5, F_SERVICEAMOUNT5, F_AMOUNT5, F_COUNT, F_COUNTRYAMOUNT, F_SERVICEAMOUNT, F_ARREARAGE, F_LATEFEE, F_AMOUNT) AS

Select f_Substr(T.f_Transfer_No,1,3),
       T.F_Transfer_No,
       T.F_Transfer_Name,
       Count(F.f_Transfer_no),/*票数*/
       T.F_Cooks,/*电炊*/
       T.f_BuyCount,/*购电量*/      
       Sum(F_Count1),
       Sum(F_CountryAmount1),
       Sum(F_ServiceAmount1),
       Sum(F_Amount1),
       Sum(F_Count2),
       Sum(F_CountryAmount2),
       Sum(F_ServiceAmount2),
       Sum(F_Amount2),
       Sum(F_Count3),
       Sum(F_CountryAmount3),
       Sum(F_ServiceAmount3),
       Sum(F_Amount3),
       Sum(F_Count4),
       Sum(F_CountryAmount4),
       Sum(F_ServiceAmount4),
       Sum(F_Amount4),
       Sum(F_Count5),
       Sum(F_CountryAmount5),
       Sum(F_ServiceAmount5),
       Sum(F_Amount5),
       Sum(F_Count),
       Sum(F_CountryAmount),
       Sum(F_ServiceAmount),
       Sum(F_Arrearage),
       Sum(F_LateFee),
       Sum(F_Amount)
   From Tb_Transfer T, V_Fee F
   Where T.F_Transfer_No = F.F_Transfer_No
   Group by T.F_Transfer_No, T.F_Transfer_Name, T.F_BuyCount, T.F_Cooks
^

CREATE VIEW V_TRANSFER_WASTING (F_LINE_NO, F_TRANSFER_NO, F_TRANSFER_NAME, F_COUNTER, F_Cooks, F_COUNT, F_COUNTRYAMOUNT, F_SERVICEAMOUNT, F_ARREARAGE, F_LATEFEE, F_AMOUNT, F_BUYCOUNT, F_WASTCOUNT, F_WASTRATE) AS

Select f_substr(f_transfer_no,1,3),
       f_transfer_no,
       f_transfer_name,
       f_counter,
       F_Cooks, 
       f_count,
       f_countryAmount,
       f_serviceAmount,
       f_Arrearage,
       f_LateFee,
       f_Amount,
       f_BuyCount,
       f_BuyCount - f_Count,
       (f_BuyCount - f_Count)/f_BuyCount * 100
   From v_Transfer_Fee
^

CREATE VIEW V_LINE_FEE (F_STATION_NO, F_LINE_NO, F_LINE_NAME, F_COUNTER, F_Cooks, F_BUYCOUNT, F_COUNT1, F_COUNTRYAMOUNT1, F_SERVICEAMOUNT1, F_AMOUNT1, F_COUNT2, F_COUNTRYAMOUNT2, F_SERVICEAMOUNT2, F_AMOUNT2, F_COUNT3, F_COUNTRYAMOUNT3, F_SERVICEAMOUNT3, F_AMOUNT3, F_COUNT4, F_COUNTRYAMOUNT4, F_SERVICEAMOUNT4, F_AMOUNT4, F_COUNT5, F_COUNTRYAMOUNT5, F_SERVICEAMOUNT5, F_AMOUNT5, F_COUNT, F_COUNTRYAMOUNT, F_SERVICEAMOUNT, F_ARREARAGE, F_LATEFEE, F_AMOUNT) AS

Select f_Substr(L.f_Line_No,1,1),
       L.F_Line_No,
       L.F_Line_Name,
       Sum(T.f_Counter),/*票数*/
       Sum(T.F_Cooks),/*电炊票*/
       Sum(T.f_BuyCount),/*购电量*/      
       Sum(F_Count1),
       Sum(F_CountryAmount1),
       Sum(F_ServiceAmount1),
       Sum(F_Amount1),
       Sum(F_Count2),
       Sum(F_CountryAmount2),
       Sum(F_ServiceAmount2),
       Sum(F_Amount2),
       Sum(F_Count3),
       Sum(F_CountryAmount3),
       Sum(F_ServiceAmount3),
       Sum(F_Amount3),
       Sum(F_Count4),
       Sum(F_CountryAmount4),
       Sum(F_ServiceAmount4),
       Sum(F_Amount4),
       Sum(F_Count5),
       Sum(F_CountryAmount5),
       Sum(F_ServiceAmount5),
       Sum(F_Amount5),
       Sum(F_Count),
       Sum(F_CountryAmount),
       Sum(F_ServiceAmount),
       Sum(F_Arrearage),
       Sum(F_LateFee),
       Sum(F_Amount)
   From Tb_Line L ,V_Transfer_Fee T 
   Where L.F_Line_No = T.F_Line_No
   Group by L.F_Line_No, L.F_Line_Name
^

/*==============================================================================
2001-09-21
2001-09-23
记录月最大用电量及最小用电量
Interbase 4.2中必须手工置初值
*/
Alter Table Tb_Ammeter Add f_MaxCount Numeric(10,2) Default 0 Not Null, 
                       Add f_MinCount Numeric(10,2) Default 9999 Not Null
^

update tb_ammeter Set f_MaxCount = 0, f_MinCount = 9999
^

/*存储抄表数据*/
Alter procedure p_SaveAmmeter_Data 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, f_house_no, f_ammeter_order, f_this_count, f_adjust_count, f_note
         From V_Ammeter_Data
         Where Not f_this_count is null;
   /*生成上次抄表数据*/
   Update tb_ammeter 
         set f_Ammeter_Last_Count = f_This_Count
      Where Not f_This_Count is 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;
End
^

/*
  2001-09-17
  2001-09-21
*/
/*检查抄表数据的合法性*/
Alter procedure p_valid As
Declare variable v_house_no char(12);
Declare variable v_ammeter_no char(10);
Declare variable v_ammeter_last_count numeric(10,2);
Declare variable v_this_count numeric(10,2);
Declare variable v_count numeric(10,2);
Declare variable v_ammeter_turn numeric(10,2);
Declare variable v_ammeter_model char(10);
Declare variable v_msg char(80);
Declare variable v_LNo char(4);
Declare variable v_Ammeter_Count Numeric(10,2);
Declare variable v_MaxCount Numeric(10,2);
Declare variable v_MinCount Numeric(10,2);

Begin
   Delete From tb_msg;/*删除原有消息库中的内容*/
   For
      Select f_house_no,
             f_ammeter_no, 
             f_ammeter_last_count,
             f_this_count,
             f_ammeter_model,
             f_MaxCount,
             f_MinCount,
             f_LNo
         from tb_ammeter
         into :v_house_no,
              :v_ammeter_no,
              :v_ammeter_last_count,
              :v_this_count,
              :v_ammeter_model,
              :v_MaxCount, 
              :v_MinCount,
              :v_LNo
   Do
   Begin
      /*取得电表的最大量程*/
      select f_ammeter_turn
         from tb_ammeter_model
         where f_ammeter_model = :v_ammeter_model
         into :v_ammeter_turn;
      v_count = f_abs(v_this_count - v_ammeter_last_count);
      /*用电是超过计量表量程的一半*/
      if (v_count > v_ammeter_turn / 2) then
      begin
         v_count = v_ammeter_turn - v_count;
         if (v_this_count > v_ammeter_last_count) then/*倒转*/
         begin
            v_msg =     '表号:' || v_Ammeter_No
                    || ' LNo:'  || v_LNo 
                    || ' 上月:' || Cast(v_Ammeter_Last_Count As Char(8)) 
                    || ' 本月:' || Cast(v_This_Count As Char(8))
                    || ' 计量:' || Cast(v_count As Char(8))
                    || ' 结论:翻转';
         end
         else
         begin
            v_msg =     '表号:' || v_Ammeter_No
                    || ' LNo:'  || v_LNo 
                    || ' 上月:' || Cast(v_Ammeter_Last_Count As Char(8)) 
                    || ' 本月:' || Cast(v_This_Count As Char(8))
                    || ' 计量:' || Cast(v_count As Char(8))
                    || ' 结论:倒转';
         end
         execute procedure p_AddMsg :v_house_no, :v_Msg;
      end
      Else
      Begin
         If(v_This_Count < v_Ammeter_Last_Count) Then
         begin
            v_msg =     '表号:' || v_Ammeter_No
                    || ' LNo:'  || v_LNo 
                    || ' 上月:' || Cast(v_Ammeter_Last_Count As Char(8)) 
                    || ' 本月:' || Cast(v_This_Count As Char(8))
                    || ' 计量:' || Cast(v_count As Char(8))
                    || ' 结论:倒转';
            execute procedure p_AddMsg :v_house_no, :v_Msg;
         end
      End
   End
   For
      Select f_house_no,
             f_ammeter_no, 
             f_ammeter_last_count,
             f_this_count,
             f_MaxCount,
             f_MinCount,
             f_Count, 
             f_LNo
         from tb_ammeter
         Where f_Count < f_MinCount Or f_Count > f_MaxCount
         into :v_house_no,
              :v_ammeter_no,
              :v_ammeter_last_count,
              :v_this_count,
              :v_MaxCount, 
              :v_MinCount,
              :v_Count,
              :v_LNo
   Do
   Begin
      v_msg =     '表号:' || v_Ammeter_No
              || ' LNo:'  || v_LNo 
              || ' 上月:' || Cast(v_Ammeter_Last_Count As Char(8)) 
              || ' 本月:' || Cast(v_This_Count As Char(8))
              || ' 计量:' || Cast(v_count As Char(8));
      execute procedure p_AddMsg :v_house_no, :v_Msg;
   End
End
^

set term ;^