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

Connect "YL:C:\Mis\Output\SMDLJF200106.GDB" user "sysdba" password "masterkey";
set term ^;

/*========================================================================================
已处理：嵩阳、杨桥、杨林
20010705对计费系统作以下变动：
1、在抄表数据录入窗口中，去掉补差价功能,此功能将造成公布表与其他报表的不一致，应完善以下功能：
　　当遇有退费或补交时，都应以电量为基准，可设置所补电量的单价（包括国家价及维护费）
2、完善逻辑表号设置功能
*/

/*
--2001-7-16
对SMDLJF.GDB中的表Tb_Transfer增加以下与自动抄表有关的字段*/
/*以下定义与自动抄表相关*/
Alter table tb_transfer add    f_Port smallint default 1 not null ,/*抄表端口*/
   add f_Telephone Char(12) Default "" Not Null,/*抄表电话*/
   add f_RecentTime Date ,/*最近抄表时间*/
   add f_MeterCount Integer Default 0 Not Null,/*自动抄表表数*/
   add f_IntervalMinute Integer Default 1440 Not Null,/*抄表周期*/
   add f_FreezeDay Integer Default 1 Not Null,/*结算日1-31*/
   add f_CenterNO Integer Default 1 Not Null/*集中器编号*/
^

update tb_transfer set f_port = 1, f_telephone = "",f_metercount = 0,F_INTERVALMINUTE = 1440, F_FREEZEDAY =  1,F_CENTERNO = 1
^

/*在电表明细表中增加所属变压器编号*/
Alter Table Tb_Ammeter 
   Add f_LConstant smallint default 1 not null,/*逻辑表常数*/
   Add f_PLRate smallint default 1 not null,/*常数倍率*/
   Add f_CopyTime Date,/*抄表时间*/
   add f_transfer_no char(6),/*台变编号*/
   add f_ok smallint default 0 not null,
   drop f_copytime,
   add f_copytime char(19)
^
update tb_ammeter set f_lconstant = 1, f_plrate = 1, f_ok = 0, f_transfer_no = f_substr(f_house_no,1,6);
^

/*
   Add by jiangyueming at 2001-06-16 10:00
*/
/*
  根据户号得到有关变压器的其他信息
*/
Create Procedure p_GetTransferInfo
As
Begin
   Exit;
End
^

Alter procedure p_GetTransferInfo 
(
   VTRANSFERNO CHAR(6)
)
RETURNS 
(  VTRANSFERNAME CHAR(20),
   VAMMETERCOUNT INTEGER,
   VAUTOAMMETERCOUNT INTEGER
)
AS  
begin
   Select f_Transfer_Name From Tb_Transfer Where f_Transfer_No = :vTransferNo Into :vTransferName;
   Select Count(*) From Tb_Ammeter 
      Where f_House_No In (Select f_House_No From Tb_House Where f_Transfer_No = :vTransferNo) 
      Into :vAmmeterCount;
   Select Count(*) From Tb_Ammeter 
      Where (f_LNo <> '') And (f_House_No In (Select f_House_No From Tb_House Where f_Transfer_No = :vTransferNo))
      Into :vAutoAmmeterCount;
   Update Tb_Transfer Set F_MeterCount = :vAutoAmmeterCount Where f_Transfer_No = :vTransferNo;
end
^

/*
--2001-7-26
--增加计算指定台变下用户电费的Procedure
计算台区下所有用户的电费
*/
Create Procedure P_RebuildTransferFee
As
Begin
   Exit;
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_Ammeter
         Where f_transfer_no = :v_transfer_no
         Into :v_house_no
   Do
   Begin
      Execute Procedure P_HouseMonthFee :v_House_No;/*以当前值计算用户的电费*/
   End
End
^

/*根据逻辑表号及集中器编号得到用户编号，返回满足要求的用户数*/
Create procedure p_LNo2HouseNo
As
Begin
   Exit;
End
^

Alter procedure p_LNo2HouseNo
(
   vCenterNo Integer,
   vLNo char(4)
)
Returns
(
   vCount integer,
   vHouseNo char(12)
)
As
Begin
   vHouseNo = "";
   select count(f_house_no) 
      From tb_transfer t, tb_ammeter m
      where t.f_tranSfer_no = m.f_transfer_no
         and f_centerno = :vCenterNo and f_lno = :vLNo
      Into :vCount;
   If (vCount = 1) Then
   Begin
      select f_house_no
         From tb_transfer t, tb_ammeter m
         where t.f_tranSfer_no = m.f_transfer_no
            and f_centerno = :vCenterNo and f_lno = :vLNo
         Into :vHouseNo;
   End
End
^

/*  2001-8-14
增加临时表用来存储抄表数据
*/
Create Table Tb_CWCMeter
(
   F_CenterNo Integer Not Null,/*集中器编号*/
   F_LNo char(4) Not Null,/*逻辑表号*/
   F_Batch SmallInt Not Null,/*批次*/
   F_Count Integer,/*表底*/
   F_FactCount Numeric(12,2),/*实抄表底*/
   F_CopyTime Char(19),/*抄表时间*/
   F_Ok SmallInt,/*数据有效性*/
   Primary key(f_CenterNo, f_LNo, f_Batch)
)
^

/*=========================================================================================
建立：2001-08-16
修改：2001-09-11
      2001-09-13
      2001-09-14

  P_CWC2Ammeter从抄表库转入数据时可能发生倍率问题
  已处理: 杨林、杨桥

  P_CWC2Ammeter在处理计费时可能发生错误
  已修改,未处理
*/

/* 
将载波自动抄表Tb_CWCMeter中的数据转存到tb_Ammeter中并计算电费
*/
CREATE PROCEDURE P_CWC2AMMETER
As
Begin
   Exit;
End
^

Alter PROCEDURE P_CWC2AMMETER 
(
  VCENTERNO INTEGER,
  VBATCH INTEGER
)  AS              
Declare Variable vLNo char(4);
Declare Variable vCount Integer;
Declare Variable vTransferNo char(6);
Declare Variable vHouseNo char(12);
Declare Variable vAmmeterOrder Integer;
Declare Variable vPLRate Integer;
Begin
   For
      Select f_Transfer_No From Tb_Transfer Where f_CenterNo = :vCenterNo Into :vTransferNo
   Do
   Begin
      For
         Select c.f_Count,f_house_no,f_ammeter_order, f_PlRate
            From TB_CWCMeter C, tb_ammeter A
            Where f_CenterNo = :vCenterNo 
              And f_Batch = :vBatch 
              And f_transfer_no = "C07004" 
              And C.f_lno = A.f_lno
            Into :vCount, :vHouseNo, :vAmmeterOrder, :vPlRate
      Do
      Begin
         If (vBatch <> 0) Then/*固定集中器数据含有倍率*/
            vCount = cast((vCount - 0.1) / vPlRate as integer);
         Execute Procedure P_SetThis_Count :vHouseNo, :vAmmeterOrder, :vCount, 0, "";
      End
   End
End
^

/*20001-8-17
删除计算所有用户电费的Procedure
*/
Drop Procedure P_MonthFee
^

/*补差价功能，此procedure造成统计表不一致*/
Drop Procedure P_SetExtra
^

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_OldThis_Count Numeric(10,2);
Declare variable v_OldAdjust_Count Numeric(10,2);
Declare variable v_OldNote char(50);
Declare variable v_Rebuild char(1);
Declare variable v_Count Numeric(10,2);
begin
   v_Rebuild = '0';/*默认不须重算*/
   Select f_This_Count
      From Tb_Ammeter
      Where f_house_no = :v_house_no And f_ammeter_order = :v_ammeter_order
      Into :v_OldThis_Count;
   /*抄表数据改变时(上次为空或不等)*/
   if ( v_OldThis_Count <> v_This_Count Or v_OldThis_Count Is Null) Then
   Begin
      update tb_ammeter 
         set f_This_Count = :v_This_count
         where f_house_no = :v_house_no And f_ammeter_order = :v_ammeter_order;
      Execute Procedure P_GetUsedCount :v_House_No, :v_Ammeter_Order Returning_Values :v_Count;
      Update Tb_Ammeter 
         Set F_Count = :v_Count
         where f_house_no = :v_house_no And f_ammeter_order = :v_ammeter_order;
      v_Rebuild = '1';
   End

   Select f_Adjust_Count, f_Note
      From Tb_House
      Where f_House_No = :v_House_No
      Into :v_OldAdjust_Count, :v_OldNote;
   if(v_OldNote Is Null) Then
      v_OldNote = '';
   if(v_Note Is Null) Then
      v_Note = '';
   /*调整量或说明改变时*/
   if (v_OldAdjust_Count <> v_Adjust_Count Or v_OldNote <> v_Note) Then
   Begin
      update tb_house
         set f_adjust_count = :v_Adjust_count,
             f_Note = :v_Note
      where f_house_no = :v_house_no;
      If (v_OldAdjust_Count <> v_Adjust_Count) Then
         v_Rebuild = '1';
   End

   If (v_Rebuild = '1') Then/*计算此户人家电费*/
   Begin
      execute procedure p_HouseMonthFee :v_House_No;
   End
end
^

ALTER PROCEDURE P_HOUSEUSEDCOUNT (V_HOUSE_NO CHAR(12))
AS 
declare variable v_ammeter_order integer;
declare variable v_count numeric(10,2);
declare variable v_sum_count numeric(10,2);
declare variable v_Base_Count numeric(10,2);
declare variable v_Rate3 numeric(10,2);
declare variable v_oldcount 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_oldcount
   Do
   Begin
      Execute Procedure p_GetUsedCount :v_House_No, :v_Ammeter_order 
                Returning_Values :v_Count;
      If (v_Count <> v_OldCount) 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
   update tb_house
         set f_sum_count = :v_sum_count
      where f_house_no = :v_house_no;

   /*计算各比例用电量*/
   Select f_Rate3, f_Base_count 
      From Tb_House
      Where f_house_no = :v_house_no
      Into :v_Rate3, :v_Base_count;
   Update Tb_House
         Set f_Count1 = f_45(f_Rate1 * f_Used_Count, 0),
             f_Count2 = f_45(f_Rate2 * f_Used_Count, 0),
             f_Count3 = 0,
             f_Count = f_45(f_Used_Count - f_Count1 - f_Count2, 0)
      where f_house_no = :v_house_no;
   if (v_Base_Count > 0) Then/*限制基本用电量*/
   Begin
      Update Tb_House/*基本用电只计固定度数, 其余计为电炊*/
            Set f_Count3 = f_Count - f_Base_Count,
                f_Count = f_Base_Count
         Where f_House_No = :v_House_No And f_Count > f_Base_Count;
   End
   Else
   Begin
      If (v_Rate3 < 1) then/*此时表示电炊用电以基本用电量的比例计*/
      Begin
         Update Tb_House
               Set f_Count3 = f_45(f_Rate3 * f_Count, 0),
                   f_Count = f_45(f_Count - f_Count3, 0)
         where f_house_no = :v_house_no;
      End
   End
End
^

/*=========================================================================================
  2001-08-31 
已处理: 小新街、杨林、嵩阳、杨桥
  滞纳金计算时应四舍五入
*/
ALTER PROCEDURE P_GETHOUSEARREARAGE (V_HOUSE_NO CHAR(12))
RETURNS (V_LATEFEE NUMERIC(15, 2))
AS