update20040309.sql

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

/*
	2004-03-09
 增加表卡显示时应显示用电量而更改
 */
Set Term ^;

CREATE TABLE TB_TEMP_AMMETER_DETAIL 
(
  F_DATE	 TIMESTAMP NOT NULL,
  F_HOUSE_NO	 TP_HOUSE_NO  NOT NULL,
  F_AMMETER_ORDER	 TP_COUNT  default 0 NOT NULL,
  F_AMMETER_COUNT	 TP_AMMETER_COUNT ,
  F_ADJUST_COUNT	 TP_AMMETER_COUNT ,
  F_NOTE	 VARCHAR(50)
)
^

Create Table Tb_HouseCard
(
   f_House_No Tp_House_No,/*户号*/
   f_House_Name Tp_House_Name,/*户名*/
   f_Date Date,/*年月*/
   f_No1 Tp_Ammeter_No,/*表一*/
   f_Count1 Tp_Count,/*读数一*/
   f_UsedCount1 Tp_Count,/*月用电量一*/
   f_No2 Tp_Ammeter_No,/*表二*/
   f_Count2 Tp_Count,/*读数二*/
   f_UsedCount2 Tp_Count,/*月用电量二*/
   f_No3 Tp_Ammeter_No,/*表三*/
   f_Count3 Tp_Count,/*读数三*/
   f_UsedCount3 Tp_Count,/*月用电量三*/
   f_Adjust_Count Tp_Count,/*读数三*/
   f_Note Tp_Note/*备注*/
)
^

--生成指定用户的表卡
CREATE PROCEDURE P_GENHOUSECARD (
  V_HOUSE_NO CHAR(12)
)  AS         
Declare Variable v_House_Name Char(20);
Declare Variable v_Note Char(50);
Declare Variable v_Ammeter_No Char(10);
Declare Variable v_No1 Char(10);
Declare Variable v_No2 Char(10);
Declare Variable v_No3 Char(10);
Declare Variable v_Ammeter_Order Integer;
Declare Variable v_Order1 Integer;
Declare Variable v_Order2 Integer;
Declare Variable v_Order3 Integer;
Declare Variable v_Count Integer;
Declare Variable v_Ammeter_Count Numeric(15,2);
Declare Variable v_Count1 Numeric(15,2);
Declare Variable v_Count2 Numeric(15,2);
Declare Variable v_Count3 Numeric(15,2);
Declare Variable v_LastCount1 Numeric(15,2);
Declare Variable v_LastCount2 Numeric(15,2);
Declare Variable v_LastCount3 Numeric(15,2);
Declare Variable v_UsedCount1 Numeric(15,2);
Declare Variable v_UsedCount2 Numeric(15,2);
Declare Variable v_UsedCount3 Numeric(15,2);
Declare Variable v_Date Date;
Declare Variable v_LastDate Date;

Begin
   Delete From Tb_HouseCard Where f_House_No = :v_House_No;/*删除原有表卡数据*/
   Select f_House_Name, f_Note From Tb_House Where f_House_No = :v_House_No Into :v_House_Name, :v_Note;
   v_Count = 1;
   For 
      Select f_Ammeter_Order, f_Ammeter_No From Tb_Ammeter Where f_House_No = :v_House_No Order by f_Ammeter_Order Into :v_Ammeter_Order, :v_Ammeter_No
   Do
   Begin
      If (v_Count = 1) Then
      Begin
         v_Order1 = v_Ammeter_Order;
         v_No1 = v_Ammeter_No;
      End 
      Else If (v_Count = 2) Then
      Begin
         v_Order2 = v_Ammeter_Order;
         v_No2 = v_Ammeter_No;
      End
      Else If (v_Count = 3) Then
      Begin
         v_Order3 = v_Ammeter_Order;
         v_No3 = v_Ammeter_No;
      End
      Else
         Suspend;
      v_Count = v_Count + 1;
   End
   /*先将要使用的数据输出到临时表*/
   Delete From Tb_Temp_Ammeter_Detail where f_House_No = :v_House_No;
   Insert Into Tb_Temp_Ammeter_Detail Select * From Tb_Ammeter_Detail where f_House_No = :v_House_No;
   /*插入第一表的数据*/
   Insert Into Tb_HouseCard (f_House_No, f_House_Name, f_Date, f_No1, f_Count1, f_Count2, f_Count3, f_Adjust_Count, f_Note)
       Select f_House_No, :v_House_Name, f_Date, :v_No1,f_Ammeter_Count,Null,Null, f_Adjust_Count,f_Note 
          From Tb_Temp_Ammeter_Detail 
          where f_House_No = :v_House_No
             And f_Ammeter_Order = :v_Order1;
   /*更新第二块表数据*/
   For
      Select f_Ammeter_Count, f_Date From Tb_Temp_Ammeter_Detail where f_House_No = :v_House_No And f_Ammeter_Order = :v_Order2 Into :v_Ammeter_Count, :v_Date
   Do      
   Begin
      Update Tb_HouseCard Set f_No2 = :v_No2, f_Count2 = :v_Ammeter_Count Where f_House_No = :v_House_No And f_Date = :v_Date;
   End

   /*更新第三块表数据*/
   For
      Select f_Ammeter_Count, f_Date From Tb_Temp_Ammeter_Detail where f_House_No = :v_House_No And f_Ammeter_Order = :v_Order3 Into :v_Ammeter_Count, :v_Date
   Do      
   Begin
      Update Tb_HouseCard Set f_No3 = :v_No3, f_Count3 = :v_Ammeter_Count Where f_House_No = :v_House_No And f_Date = :v_Date;
   End
   /*计算用电量*/
   v_LastDate = Null;
   For
      Select f_Date, f_Count1, f_Count2, f_Count3 From Tb_HouseCard Where f_House_No = :v_House_No Order By f_Date Into :v_Date, :v_Count1, :v_Count2, :v_Count3
   Do
   Begin
      If (v_LastDate Is Not Null) Then
      Begin
         v_UsedCount1 = v_Count1 - v_LastCount1;
         v_UsedCount2 = v_Count2 - v_LastCount2;
         v_UsedCount3 = v_Count3 - v_LastCount3;
         Update Tb_HouseCard Set f_UsedCount1 = :v_UsedCount1, f_UsedCount2 = :v_UsedCount2, f_UsedCount3 = :v_UsedCount3
            Where f_House_No = :v_House_No And f_Date = :v_Date;
      End
      v_LastDate = v_Date;
      If (v_Count1 Is Not Null) Then v_LastCount1 = v_Count1;
      If (v_Count2 Is Not Null) Then v_LastCount2 = v_Count2;
      If (v_Count3 Is Not Null) Then v_LastCount3 = v_Count3;
   End
End
^
Set Term ;^