update20040515.sql

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

Set Term ^;
/*月结帐过程修改===================================================================
  上期余额及收款日期等应重置
  2004-05-16
*/
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 = '',
             F_Receiver = "" , /*收费员*/
             F_Charge_Date = NULL, /*收费日期*/
             F_Last_Balance = F_Balance, /*上期余额*/
             F_Gathering = 0,/*收款*/
             F_Give_Change = 0,/*找零*/
             F_Actual_Receive = 0, /*实收*/
             F_Balance = 0;/*本期余额*/
   Delete From Tb_Fee;
   
   /*删除10KV收费记录*/
   Delete From Tb_10KV_Gathering;
End
^
Set Term ;^