update20040322.sql

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

Set Term ^;

Create Table Tb_Excess
(
   f_ExcessType SmallInt DEFAULT 0 Not Null ,/*加价电量类别*/
   f_ExcessName Char(20),/*加价电量名称*/
   f_ExcessScale Tp_Rate DEFAULT 0 Not Null ,/*加价电量比例*/
   f_ExcessPrice Tp_Price DEFAULT 0 Not Null ,/*单价*/
   Primary Key (f_ExcessType)
)
^
Insert Into Tb_Excess Values (1,'动力加价',0.50,0.035)
^
Insert Into Tb_Excess Values (0,'动力加价',0,0)
^

/*Tb_House中增加价电量相关信息*/
Alter Table Tb_House 
   Add f_ExcessType SmallInt/*加价类别*/
^

Alter Table Tb_Fee
   Add f_ExcessCount  Tp_Count DEFAULT 0,/*加价电量*/
   Add f_ExcessAmount Tp_Money DEFAULT 0 /*加价金额*/
^

drop view v_Line_Fee
^
drop view V_TRANSFER_WASTING
^
drop view v_transfer_Fee
^
drop view V_SINGLE_FEE
^
drop view V_Multi_FEE
^
drop view V_FEE
^

CREATE VIEW V_FEE As
   Select * 
   From Tb_Fee
   Where f_Amount <> 0
^

CREATE VIEW V_MULTI_FEE AS
   Select * 
   From V_Fee
   Where f_substr(F_House_No,7,7) = '/'
^

CREATE VIEW V_SINGLE_FEE AS
   Select * 
   From V_Fee
   Where f_substr(F_House_No,7,7) <> '/'
^

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,
  F_ExcessCount,
  F_ExcessAmount
) 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),
       Sum(F_ExcessCount),
       Sum(F_ExcessAmount)
   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,
  F_ExcessCount,
  F_ExcessAmount
) 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),
       Sum(F_ExcessCount),
       Sum(F_ExcessAmount)
   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
^

Alter Procedure P_TransferProvide
As
Begin
   Exit;
End
^

Alter Procedure P_NewMonth
As
Begin
   Exit;
End
^

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

Alter PROCEDURE P_HOUSEFEE (
  V_HOUSE_NO CHAR(12)
)  AS         

Declare variable v_house_name char(20);
Declare variable v_Box_No char(6);
Declare variable v_Arrearage numeric(10,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_no4 char(2);
Declare variable v_price1 numeric(10,2);
Declare variable v_price2 numeric(10,2);
Declare variable v_price3 numeric(10,2);
Declare variable v_price4 numeric(10,2);
Declare variable v_Count  numeric(10,2);
Declare variable v_Count1 numeric(10,2);
Declare variable v_Count2 numeric(10,2);
Declare variable v_Count3 numeric(10,2);
Declare variable v_Count4 numeric(10,2);
Declare variable v_CountryAmount1 numeric(10,2);
Declare variable v_CountryAmount2 numeric(10,2);
Declare variable v_CountryAmount3 numeric(10,2);
Declare variable v_CountryAmount4 numeric(10,2);
Declare variable v_CountryAmount numeric(10,2);
Declare variable v_ServiceAmount1 numeric(10,2);
Declare variable v_ServiceAmount2 numeric(10,2);
Declare variable v_ServiceAmount3 numeric(10,2);
Declare variable v_ServiceAmount4 numeric(10,2);
Declare variable v_ServiceAmount numeric(10,2);
Declare variable v_LateFee numeric(10,2);/*滞纳金*/
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
   Select f_house_name,
          f_box_no,
          F_ARREARAGE,
          f_elect_no,
          f_elect_no1,
          f_elect_no2,
          f_elect_no3,
          f_Count,
          f_Count1,
          f_Count2,
          f_count3,
          f_ExcessType
      From Tb_House
      Where f_house_no = :v_house_no
      Into :v_house_Name,
           :v_box_no,
           :v_Arrearage,
           :v_elect_no1,
           :v_elect_no2,
           :v_elect_no3,
           :v_elect_no4,
           :v_Count1,
           :v_Count2,
           :v_Count3,
           :v_Count4,
           :v_ExcessType;

   /*取得加价比例及单价*/
   If (v_ExcessType Is Null) Then
   Begin
      v_ExcessScale = 0;
      v_ExcessPrice = 0;
   End
   Else
      Select f_ExcessScale, f_ExcessPrice From Tb_Excess Where f_ExcessType = :v_ExcessType Into :v_ExcessScale, :v_ExcessPrice;

   /*删除该户原有统计数据*/
   delete from tb_fee where f_house_no = :v_house_no;
   delete from tb_feeDeTail where f_house_no = :v_house_no;

   Insert Into Tb_Fee (f_House_No, F_House_Name, f_Box_No, f_Arrearage) Values (:v_House_No, :v_House_Name, :v_Box_No, :v_Arrearage);
   /*求国家价部分*/
   Execute Procedure p_GetCountry :v_House_No
      Returning_Values :v_Price1, :v_Price2, :v_Price3, :v_Price4,
                       :v_CountryAmount1, :v_CountryAmount2, :v_CountryAmount3, :v_CountryAmount4,
                       :v_CountryAmount;
   /*求维护费部分*/
   Execute Procedure p_GetService :v_House_No
      Returning_Values :v_Price1, :v_Price2, :v_Price3, :v_Price4,
                       :v_ServiceAmount1, :v_ServiceAmount2, :v_ServiceAmount3, :v_ServiceAmount4, 
                       :v_ServiceAmount;
   /*计算滞纳金*/
   Execute Procedure P_GetHouseArrearage :v_House_No Returning_Values :v_LateFee;
   /*各部分金额*/
   Update Tb_House
         Set f_Amount = :v_CountryAmount1 + :v_ServiceAmount1,
             f_Amount1 = :v_CountryAmount2 + :v_ServiceAmount2,
             f_Amount2 = :v_CountryAmount3 + :v_ServiceAmount3,
             f_Amount3 = :v_CountryAmount4 + :v_ServiceAmount4,
             f_LateFee = :v_LateFee
      Where f_House_No = :v_House_No;
   /*把各部分的金额分别插入到相应对帐表的合适位置*/
   Execute Procedure P_AddFee :v_House_No, :v_House_Name, :v_Elect_No1, :V_Count1, :V_CountryAmount1, :V_ServiceAmount1;
   Execute Procedure P_AddFee :v_House_No, :v_House_Name, :v_Elect_No2, :V_Count2, :V_CountryAmount2, :V_ServiceAmount2;
   Execute Procedure P_AddFee :v_House_No, :v_House_Name, :v_Elect_No3, :V_Count3, :V_CountryAmount3, :V_ServiceAmount3;
   Execute Procedure P_AddFee :v_House_No, :v_House_Name, :v_Elect_No4, :V_Count4, :V_CountryAmount4, :V_ServiceAmount4;
   /*计算合计*/
   v_Count = v_Count1 + v_Count2 + v_Count3 + v_Count4;
   v_ExcessCount = f_45(v_Count * v_ExcessScale,0);/*加价电量*/
   v_ExcessAmount = f_45(v_ExcessCount * v_ExcessPrice,2);/*加价金额*/

   Update Tb_Fee
         Set 
             f_ExcessCount = :v_ExcessCount,
             f_ExcessAmount = :v_ExcessAmount,
             f_Count = f_Count1 + f_Count2 + f_Count3 + f_Count4 + f_Count5,
             f_CountryAmount = f_CountryAmount1 + f_CountryAmount2 + f_CountryAmount3 + f_CountryAmount4 + f_CountryAmount5,
             f_ServiceAmount = f_ServiceAmount1 + f_ServiceAmount2 + f_ServiceAmount3 + f_ServiceAmount4 + f_ServiceAmount5,
             f_LateFee = :v_LateFee,
             f_Amount = f_CountryAmount + f_ServiceAmount + f_LateFee + f_ExcessAmount
      Where f_House_No = :v_House_No;

   /*插入相应的数据到Tb_FeeDetail, 其它分类电量由P_AddFee加入*/ 
   /*滞纳金*/
   If (v_LateFee <> 0) Then
      Insert Into Tb_FeeDetail (f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount) Values (:v_House_No, Null, '滞纳金', Null, Null, :v_LateFee);
   /*加价电量*/
   If (v_ExcessCount <> 0) Then
      Insert Into Tb_FeeDetail (f_House_No, f_IsTotal, f_Item, f_Count, f_Price, f_Amount) Values (:v_House_No, Null, '加价电量', :v_ExcessCount, :v_ExcessPrice, :v_ExcessAmount);
End
^
ALTER PROCEDURE P_NEWMONTH 
AS
/*转存上月的抄表数据，并设置新月份开始*/
Declare variable v_Year Integer;
Declare variable v_Month Integer;
Declare variable v_Date Date;
Begin
   /*转存上月抄表数据*/

   /*记录未发生的用户到指定表*/
   Delete From Tb_ZeroFee;
   Insert Into Tb_ZeroFee (f_House_No, f_House_Name) 
      Select f_House_No, f_House_Name 
         From tb_House;
   
   /*取得当前日期所对应抄数据数据日期*/
   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_Count = 0, F_Amount = 0,
             F_Count1 = 0, F_Amount1 = 0,
             F_Count2 = 0, F_Amount2 = 0,
             F_Count3 = 0, F_Amount3 = 0,
             f_arrearage = 0,f_lateFee = 0,f_2year = '';

   Delete From Tb_Fee;
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
^


Update Tb_Ver Set f_Ver = "1.11.20040317", f_Note = "多用户版"
^

Set Term ;^