update 20041221.sql.txt

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

Set Term ^;
--2004-12-21
--蒋跃明修改于SMT生产厂
--由于针对城市低保户的用电政策：
/*	每月30KWH内按0.402元/KWH收取，超出部分：31-100KWH部分按城镇居民生活用电0.45元/KWH收取，100KWH以上部分按电炊价0.33元/KWH执行
    统计报表要求按分类电价进行，原有报表格式已不适应：目前有三类居民照明电价。
所有分类统计表使用如下表进行统计：
Tb_FeeDetail
  f_LineNo--线路编号
  f_TransferNo--台变编号
  f_HouseNo--户号
  f_Item--收费项目
  f_Count--收费电量
  f_Price--单价
  f_Amount--金额
*/

--统一各供电所库结构及计算电费方式
--目前主要差异：城区所需计算变损=铁损+...
--Tb_House中F_Used_Amount及f_Used_Count改为由程序计算而不是Computed By

Alter Table Tb_FeeDetail 
      Add f_Line_No Char(3), 
      Add f_Transfer_No Char(6)
^
Alter Procedure P_TransferProvide As Begin Exit; End^
ALTER PROCEDURE P_HOUSEUSEDCOUNT( V_HOUSE_NO CHAR(12)) AS Begin  Exit; End ^
Alter Procedure p_HouseFee As Begin Exit; End^
Alter Procedure p_AddFee As Begin Exit; End^

Drop Table Tb_Option
^
CREATE TABLE TB_OPTION 
(
  F_OPTION	CHAR(10) NOT NULL,
  F_VALUE	INTEGER,
  F_SECTION	VARCHAR(20),
  F_NOTE	VARCHAR(30),
  F_SVALUE	VARCHAR(50),
 PRIMARY KEY (F_OPTION)
)
^
/*城区所
Drop Procedure p_GetUsedCount
^
INSERT INTO TB_OPTION (F_OPTION, F_VALUE, F_SECTION, F_NOTE, F_SVALUE) VALUES ('IronMode', 1, '电费结算', '是否加铁损计算电费', NULL)
^
*/

--其它所
INSERT INTO TB_OPTION (F_OPTION, F_VALUE, F_SECTION, F_NOTE, F_SVALUE) VALUES ('IronMode', 0, '电费结算', '是否加铁损计算电费', NULL)
^

--============================================================================================
INSERT INTO TB_OPTION (F_OPTION, F_VALUE, F_SECTION, F_NOTE, F_SVALUE) VALUES ('Year', 2004, '数据库', '当前年份', '2004')
^
INSERT INTO TB_OPTION (F_OPTION, F_VALUE, F_SECTION, F_NOTE, F_SVALUE) VALUES ('Month', 3, '数据库', '当前月份', '4')
^

Drop View v_House 
^
Drop View v_ZeroFee
^
Alter Table Tb_House Drop f_Used_Count
^
Alter Table Tb_House Drop f_Used_Amount
^
Alter Table Tb_House Add f_Used_Count Numeric(10,2)
^
Alter Table Tb_House Add f_Used_Amount Numeric(10,2)
^
Drop Trigger Tr_House_AFTUPD
^
Drop Trigger Tr_House_UPD
^

--更改用电类别类型
Alter Table Tb_Elect Alter Column f_Elect_Name Type Varchar(20)
^
Update Tb_Elect Set f_Elect_Name = f_Trim(f_Elect_Name)
^

--增加城镇低保户用电类别
Insert Into Tb_Elect (f_Elect_No, f_Elect_Name, f_Trade_No, f_Country_Price, f_Service_Price, f_Price)  Values ('10','城镇低保户用电','01',0.402,0,0.402)
^
--农村五保户
Insert Into Tb_Elect (f_Elect_No, f_Elect_Name, f_Trade_No, f_Country_Price, f_Service_Price, f_Price)  Values ('11','农村五保户用电','01',0.43,0,0.43)
^
--修改电炊用电名称
Update Tb_Elect Set f_ELect_Name = '居民优惠用电' Where f_Elect_No = '04';
^

CREATE VIEW V_ZEROFEE (
  F_HOUSE_NO, 
  F_HOUSE_NAME
) AS
Select f_house_no, f_house_name
   from tb_house 
where f_used_Amount = 0
^

CREATE VIEW V_HOUSE 
AS
Select * 
From Tb_House
Where f_Used_Amount <> 0
^

Create PROCEDURE P_CALCHOUSEFEE
(
  V_HOUSE_NO CHAR(12)
)
AS
Begin
   Exit;
End
^

ALTER PROCEDURE P_TRANSFERPROVIDE 
AS
Declare Variable v_Transfer_No Char(6);
Declare variable v_PowerCount Integer;
Declare variable v_NormalCount Integer;
Declare variable v_ProvideCount Numeric(12,2);
Begin
   /*清除原有数据：动力户数、照明户数（非动力户数）,供电量*/
   Update Tb_Transfer
      Set F_PowerCount = 0, F_NormalCount = 0, f_ProvideCount = 0;
   For
      Select f_Transfer_No
         From v_House
         Group By f_Transfer_No
         Into :v_Transfer_No
   Do
   Begin
      /*计算动力户数*/
      Select Count(f_House_No)
         From v_House
         Where f_Transfer_No = :v_Transfer_No And f_Elect_No = '31'
         Into :v_PowerCount;
      /*计算非动力户数*/
      Select Count(f_House_No)
         From v_House
         Where f_Transfer_No = :v_Transfer_No And f_Elect_No <> '31'
         Into :v_NormalCount;
      /*计算总的供电量*/
      Select Sum(f_Used_Count)
         From v_House
         Where f_Transfer_No = :v_Transfer_No
         Into :v_ProvideCount;
      Update Tb_Transfer
         Set F_PowerCount = :v_PowerCount,
             F_NormalCount = :v_NormalCount,
             f_ProvideCount = :v_ProvideCount
         Where f_Transfer_No = :v_Transfer_No;
   End
End
^

ALTER PROCEDURE P_HOUSEUSEDCOUNT 
(
  V_HOUSE_NO CHAR(12)
)
AS
declare variable v_ammeter_order integer;
declare variable v_used_count 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_Iron_Used Integer;
declare variable v_Trans_Used SmallInt;
declare variable v_Ct SmallInt;
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_old_count integer;
Begin
   /*计算各表用电量*/
   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
      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_Used_Count = v_Sum_Count * v_Ct + v_Adjust_Count;
   if(v_Used_Count <> 0) Then/*用电量不为零时加铁损*/
   Begin
      v_Used_Count = v_Used_Count + v_Iron_Used;
   End
   v_Trans_Used = f_45(v_Used_Count / 100, 0);
   v_Used_Count = v_Used_Count + v_Trans_Used;
   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
   v_Trans_Used = v_Trans_Used + v_Iron_Used;
   Update Tb_House
         Set f_Sum_Count = :v_Sum_Count,
             f_Used_Count = :v_Used_Count,
             f_Count1 = :v_Count1,
             f_Count2 = :v_Count2,
             f_Count3 = :v_Count3,
             f_Count = :v_Count,
             f_Trans_Used = :v_Trans_Used
      where f_house_no = :v_house_no;
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;