update20040401.sql

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

Set Term ^;
/*================================================================================
    2004-03-31以下为打印发票而增加
*/

/*------------------------------------------------------------------------------------*/
/*使用人员表增加权限限制'
  用SmallInt按位表示权限, 0-不允许 1-允许 当f_Privilege=0时只允许进行一般功能的访问
  bit0--表计, 允许录入用户资料，录入每月抄表表底
  bit1--收费, 允许进行收费及发票打印
*/
Alter Table Tb_Person
	Add f_Privilege SmallInt Default 0 Not Null,
	Add f_Privilege_Name Char(40) Default '无权限'
^

/*------------------------------------------------------------------------------------*/
/* 增台变抄表日期
*/
Alter Table Tb_Transfer 
    Add f_CopyDay SmallInt Default 0 Not Null,/*抄表日*/
    Add f_IsAuto Char(1) Default 'N' Not Null/*是自动抄表吗Yy=Yes*/
^

/*------------------------------------------------------------------------------------*/
/*当前用户发票打印状态
   0--不打印发票
   1--要打印发票, 但未打印
   3--发票已打印
*/
Alter Table Tb_House 
	Add f_PrintInvoice Char(1) Default 'N' Not Null,
    Add f_PrintedInvoice Char(1) Default 'N' Not Null,
	Add f_Charged Char(1) Default 'N' Not Null/*已收费 Yy=Yes*/
^

/*------------------------------------------------------------------------------------*/
/*
    Tb_FeeDetail: 存储用户的分类收费项目
*/
Create Table Tb_FeeDetail
(
    f_House_No Tp_House_No,/*户号*/
    f_Item Char(20),/*收费项目*/
    f_Count Integer,/*计费电量*/
    f_Price Numeric(10,3),/*单价*/
    f_Amount Numeric(10,2),/*金额*/
    f_IsTotal char(1)/*是合计吗？*/
)
^
Create Index Idx_FeeDetail On Tb_FeeDetail(f_House_No)
^
/*------------------------------------------------------------------------------------*/
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' ) 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_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
^
/*------------------------------------------------------------------------------------*/
/*
   重建触发器以适应分类加价及发票打印
*/
Drop Trigger TR_House_AftUpd
^
CREATE TRIGGER TR_HOUSE_AFTUPD FOR TB_HOUSE AFTER UPDATE POSITION 0 as
Declare variable v_house_no char(12);
Declare variable v_ExcessType SmallInt;
begin
   /*在欠费/加价类型/打印发票 数据被修改时应重新计算应收费*/
   If (New.F_Arrearage Is Null) Then
      New.F_Arrearage = 0;
   v_ExcessType = Old.f_ExcessType;
   If (v_ExcessType Is Null) Then
      v_ExcessType = 0;
   If ( (New.F_Arrearage <> Old.F_Arrearage) 
     Or (New.f_ExcessType <> v_ExcessType) 
     Or (New.f_PrintInvoice <> Old.f_PrintInvoice) )Then
   Begin
      v_house_no = new.f_house_no;
      Execute Procedure P_HouseMonthFee :v_House_No;
   End
End
^

/*------------------------------------------------------------------------------------
   修改村子名为varchar(20), 原为char(20)
*/
Alter Domain Tp_Village_Name Type Varchar(20)
^
Update Tb_Village Set f_Village_Name = f_Trim(f_Village_Name)
^

/*更改选项表以增系统其它设置*/
Alter Table Tb_Option Add f_Section Varchar(20), Add f_Note Varchar(30), Add f_SValue Varchar(50)
^
Update Tb_Option Set f_Section = '电费结算', f_Note = '是否加铁损计算电费' Where f_Option = 'IronMode'
^
Insert Into Tb_Option (f_Section, f_Option, f_Note, f_Value, f_sValue) Values ('数据库', 'Year','当前年份',2004, '2004')
^
Insert Into Tb_Option (f_Section, f_Option, f_Note, f_Value, f_sValue) Values ('数据库', 'Month','当前月份',3, '4')
^
Update Tb_Ver Set f_Ver = "1.12.20040422", f_Note = "多用户版"
^
Set Term ;^