createdatabase.sql

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

             F_Price1 = 0, 
             F_Price2 = 0,
                      F_Price3 = 0;
   For
     Select f_Elect_No,
            f_Price
        From tb_Elect
        Into :v_Elect_No,
             :v_Price
   Do
   Begin
      Update Tb_House
            Set F_Price = :v_Price
         Where f_Elect_No = :V_Elect_No;
      Update Tb_House
            Set F_Price1 = :v_Price
         Where f_Elect_No1 = :V_Elect_No;
      Update Tb_House
            Set F_Price2 = :v_Price
         Where f_Elect_No2 = :V_Elect_No;
          If (v_Elect_No = '04') Then
          Begin/*只更改照明用户*/
           Update Tb_House
               Set F_Price3 = :v_Price
            Where f_Elect_No = '01' ;
          End
   End
End
 ^

ALTER PROCEDURE P_SETAMMETERLAST (V_HOUSE_NO CHAR(12),
V_AMMETER_ORDER INTEGER)
AS 

Declare variable v_ammeter_count numeric(10,2);
Declare variable v_date date;
Begin
   /*取得最近的抄表日期*/
   Select Max(f_Date)  
      From Tb_ammeter_Detail
      Where (f_house_no = :v_house_no)
        and (f_ammeter_order = :V_ammeter_order)
      Into :v_Date;
   If (Not v_Date is null) Then/*已有抄表数据*/
   Begin
      Select f_ammeter_count
         From Tb_Ammeter_Detail
         Where (f_house_no = :v_house_no)
            And (f_ammeter_order = :v_ammeter_order)
            And (f_Date = :v_Date) 
         Into :v_ammeter_count;/*取得上月抄表数据*/
      Update tb_ammeter 
            Set f_ammeter_last_count = :v_ammeter_count
         Where (f_house_no = :v_house_no)
            And (f_ammeter_order = :v_ammeter_order);
   End
   Else
   Begin
      /*没有抄表数据,则将底数设为上月抄表数*/
      Update tb_ammeter 
           Set f_ammeter_last_count = f_ammeter_count
         Where (f_house_no = :v_house_no)
            And (f_ammeter_order = :v_ammeter_order);
   End
End
 ^

ALTER PROCEDURE P_SETLAST AS 

Declare variable v_house_no char(12);
Declare variable v_ammeter_order integer;
Begin
   /*重新设置相应表的上次抄表数据 */
   For
      Select f_house_no, f_ammeter_order
         From tb_ammeter
         Into :v_house_no, :v_ammeter_order
   Do
   Begin
      Execute Procedure P_SetAmmeterLast :v_House_No, :v_Ammeter_order;
   End
End
 ^

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
 ^

ALTER PROCEDURE P_EXTRACTION (V_YEAR INTEGER,
V_MONTH INTEGER)
AS 

Declare variable v_Date Date;
Declare variable v_House_No Char(12);
Declare variable v_Ammeter_Order Integer;
Declare variable v_Ammeter_Count Numeric(10,2);
Declare variable v_Adjust_Count Numeric(10,2);
Declare variable v_Note varchar(50);
Begin
   For
      Select f_Date, f_House_No, f_Ammeter_Order, f_Ammeter_Count, f_Adjust_Count, F_Note
      From Tb_Ammeter_Detail
      Where f_Year(f_Date) = :v_Year
        And f_Month(f_Date) = :v_Month
      Into :v_Date, :V_House_No, :v_Ammeter_Order, :v_Ammeter_Count, :v_Adjust_Count, :V_Note
   Do
   Begin
      /*更新用户调整数及说明*/
      Update Tb_House
            Set f_Adjust_Count = :v_Adjust_Count,
                f_Note = :v_Note
         Where f_House_No = :v_House_No;
      /*更新现在的抄表数据*/
      Update Tb_Ammeter
            Set f_This_Count = :v_Ammeter_Count
         Where f_House_No = :v_House_No ANd f_Ammeter_Order = :v_Ammeter_Order;
      /*删除已存储的抄表数据*/
      Delete From Tb_Ammeter_Detail
         Where f_Date = :v_Date
           and f_House_no = :v_House_No
           and f_Ammeter_Order = :v_Ammeter_Order;
   End
   /*设置上次抄表数据*/
   Execute Procedure p_SetLast;
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_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 v_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_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_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
 ^

ALTER PROCEDURE P_MAXTOWNNO RETURNS (V_TOWN_NO CHAR(3))
AS 

Begin
   Select Max(f_Town_No) 
      From Tb_Town 
      Into :v_Town_No;
   If(v_Town_No is Null) Then
      v_Town_No = '000';
End
 ^

ALTER PROCEDURE P_MAXVILLAGENO (V_TOWN_NO CHAR(3))
RETURNS (V_VILLAGE_NO CHAR(5))
AS 

Begin
   Select Max(f_Village_No) 
      From Tb_Village 
      Where f_Town_No Like :v_Town_No || '%'
      Into :v_Village_No;
   If(v_Village_No is Null) Then
      v_Village_No = v_Town_No || '00';
End
 ^

ALTER PROCEDURE P_MAXLINENO (V_STATION_NO CHAR(1))
RETURNS (V_LINE_NO CHAR(3))
AS 

Begin
   Select Max(f_Line_No) 
      From Tb_Line 
      Where f_Station_No Like :v_Station_No || '%'
      Into :v_Line_No;
   If(v_Line_No is Null) Then
      v_Line_No = v_Station_No || '00';
End
 ^

ALTER PROCEDURE P_MAXTRANSFERNO (V_LINE_NO CHAR(3))
RETURNS (V_TRANSFER_NO CHAR(6))
AS 

Begin
   Select Max(f_Transfer_No) 
      From Tb_Transfer 
      Where f_Line_No Like :v_Line_No || '%'
      Into :v_Transfer_No;
   If(v_Transfer_No is Null) Then
      v_Transfer_No = v_Line_No || '000';
End
 ^

ALTER PROCEDURE P_MAXHOUSENO (V_TRANSFER_NO CHAR(7))
RETURNS (V_HOUSE_NO CHAR(12))