create.sql

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

,
        F_ELECT_NO TP_ELECT_NO NOT NULL,
        F_PRICE TP_PRICE  default 0
 NOT NULL,
        F_COUNT TP_AMMETER_COUNT,
        F_AMOUNT TP_MONEY,
        F_RATE1 TP_RATE  default 0
,
        F_ELECT_NO1 TP_ELECT_NO,
        F_PRICE1 TP_PRICE  default 0
,
        F_COUNT1 TP_AMMETER_COUNT,
        F_AMOUNT1 TP_MONEY,
        F_RATE2 TP_RATE  default 0
,
        F_ELECT_NO2 TP_ELECT_NO,
        F_PRICE2 TP_PRICE  default 0
,
        F_COUNT2 TP_AMMETER_COUNT,
        F_AMOUNT2 TP_MONEY,
        F_RATE3 TP_RATE  default 0
,
        F_ELECT_NO3 TP_ELECT_NO,
        F_PRICE3 TP_PRICE  default 0
,
        F_COUNT3 TP_AMMETER_COUNT,
        F_AMOUNT3 TP_MONEY,
        F_NOTE CHAR(50),
        F_ARREARAGE TP_MONEY,
        F_2YEAR CHAR(1) default ''
,
        F_LATEFEE TP_MONEY,
        F_USED_AMOUNT COMPUTED BY (f_amount + f_amount1 + f_amount2 + f_amount3 + f_latefee),
        F_CT INTEGER default 1
 NOT NULL,
        F_USED_COUNT COMPUTED BY (f_sum_count * f_ct /*本表计量数*/
                             + f_adjust_count /*调整量*/
                             - f_son_count),
        F_NOTFEECOUNTS SMALLINT Default 0
 NOT NULL,
PRIMARY KEY (F_HOUSE_NO));

/* Table: TB_LINE, Owner: SYSDBA */
CREATE TABLE TB_LINE (F_LINE_NO TP_LINE_NO NOT NULL,
        F_STATION_NO TP_STATION_NO,
        F_LINE_NAME TP_LINE_NAME,
        F_ACTUALHOUSE INTEGER,
        F_SHOULDHOUSE INTEGER,
        F_COPYHOUSE INTEGER,
        F_NOTCOPYHOUSE INTEGER,
        F_ERRORCOPYHOUSE INTEGER,
        F_COPYRATE NUMERIC(15, 3),
        F_ERRORCOPYRATE NUMERIC(15, 3),
PRIMARY KEY (F_LINE_NO));

/* Table: TB_LOG, Owner: SYSDBA */
CREATE TABLE TB_LOG (F_ID INTEGER NOT NULL,
        F_DATE DATE Default 'Now'
 NOT NULL,
        F_LOG VARCHAR(100),
        F_TYPE CHAR(20) default ""
,
PRIMARY KEY (F_ID));

/* Table: TB_MSG, Owner: SYSDBA */
CREATE TABLE TB_MSG (F_ID INTEGER NOT NULL,
        F_DATE DATE default 'now'
 NOT NULL,
        F_SENDER CHAR(20),
        F_CONTENT CHAR(80),
PRIMARY KEY (F_ID));

/* Table: TB_PERSON, Owner: SYSDBA */
CREATE TABLE TB_PERSON (F_PERSON_NO TP_PERSON_NO NOT NULL,
        F_PERSON_NAME TP_PERSON_NAME,
        F_PERSON_PASSWORD TP_PASSWORD,
PRIMARY KEY (F_PERSON_NO));

/* Table: TB_SQL, Owner: SYSDBA */
CREATE TABLE TB_SQL (F_ID CHAR(5) NOT NULL,
        F_TOPIC CHAR(40),
        F_SQL BLOB SUB_TYPE 0 SEGMENT SIZE 80,
PRIMARY KEY (F_ID));

/* Table: TB_STATION, Owner: SYSDBA */
CREATE TABLE TB_STATION (F_STATION_NO TP_STATION_NO NOT NULL,
        F_STATION_NAME TP_STATION_NAME,
        F_TELEPHONE TP_TELEPHONE,
PRIMARY KEY (F_STATION_NO));

/* Table: TB_SYSINFO, Owner: SYSDBA */
CREATE TABLE TB_SYSINFO (F_ITEM CHAR(20),
        F_IVALUE INTEGER,
        F_SVALUE CHAR(40),
        F_NOTE CHAR(40));

/* Table: TB_TOWN, Owner: SYSDBA */
CREATE TABLE TB_TOWN (F_TOWN_NO TP_TOWN_NO NOT NULL,
        F_TOWN_NAME TP_TOWN_NAME,
PRIMARY KEY (F_TOWN_NO));

/* Table: TB_TRANSFER, Owner: SYSDBA */
CREATE TABLE TB_TRANSFER (F_TRANSFER_NO TP_TRANSFER_NO NOT NULL,
        F_LINE_NO TP_LINE_NO,
        F_TRANSFER_NAME TP_TRANSFER_NAME,
        F_BUYCOUNT TP_AMMETER_COUNT,
        F_ACTUALHOUSE INTEGER,
        F_SHOULDHOUSE INTEGER,
        F_COPYHOUSE INTEGER,
        F_NOTCOPYHOUSE INTEGER,
        F_ERRORCOPYHOUSE INTEGER,
        F_PERRORCOPYHOUSE INTEGER,
        F_COPYRATE NUMERIC(15, 3),
        F_ERRORCOPYRATE NUMERIC(15, 3),
        F_POWERCOUNT INTEGER,
        F_NORMALCOUNT INTEGER,
        F_PROVIDECOUNT NUMERIC(15, 2),
        F_PORT SMALLINT default 1
 NOT NULL,
        F_TELEPHONE CHAR(12) Default ""
 NOT NULL,
        F_RECENTTIME DATE,
        F_METERCOUNT INTEGER Default 0
 NOT NULL,
        F_INTERVALMINUTE INTEGER Default 1440
 NOT NULL,
        F_FREEZEDAY INTEGER Default 1
 NOT NULL,
        F_CENTERNO INTEGER Default 1
 NOT NULL,
        F_COOKS INTEGER default 0
 NOT NULL,
PRIMARY KEY (F_TRANSFER_NO));

/* Table: TB_VILLAGE, Owner: SYSDBA */
CREATE TABLE TB_VILLAGE (F_VILLAGE_NO TP_VILLAGE_NO NOT NULL,
        F_TOWN_NO TP_TOWN_NO NOT NULL,
        F_VILLAGE_NAME TP_VILLAGE_NAME,
PRIMARY KEY (F_VILLAGE_NO));

/* Table: TB_VOLTAGE, Owner: SYSDBA */
CREATE TABLE TB_VOLTAGE (F_VOLTAGE_NO TP_VOLTAGE_NO NOT NULL,
        F_VOLTAGE_NAME TP_VOLTAGE_NAME,
PRIMARY KEY (F_VOLTAGE_NO));

/* Table: TB_VOLUME, Owner: SYSDBA */
CREATE TABLE TB_VOLUME (F_VOLUME_NO TP_VOLUME_NO NOT NULL,
        F_VOLUME_NAME TP_VOLUME_NAME,
PRIMARY KEY (F_VOLUME_NO));

/* Table: TB_ZEROFEE, Owner: SYSDBA */
CREATE TABLE TB_ZEROFEE (F_HOUSE_NO TP_HOUSE_NO,
        F_HOUSE_NAME TP_HOUSE_NAME);
ALTER TABLE TB_VILLAGE ADD FOREIGN KEY (F_TOWN_NO) REFERENCES TB_TOWN(F_TOWN_NO);
ALTER TABLE TB_LINE ADD FOREIGN KEY (F_STATION_NO) REFERENCES TB_STATION(F_STATION_NO);
ALTER TABLE TB_TRANSFER ADD FOREIGN KEY (F_LINE_NO) REFERENCES TB_LINE(F_LINE_NO);
ALTER TABLE TB_HOUSE ADD FOREIGN KEY (F_TRANSFER_NO) REFERENCES TB_TRANSFER(F_TRANSFER_NO);
ALTER TABLE TB_HOUSE ADD FOREIGN KEY (F_VILLAGE_NO) REFERENCES TB_VILLAGE(F_VILLAGE_NO);
ALTER TABLE TB_HOUSE ADD FOREIGN KEY (F_VOLTAGE_NO) REFERENCES TB_VOLTAGE(F_VOLTAGE_NO);
ALTER TABLE TB_HOUSE ADD FOREIGN KEY (F_ELECT_NO) REFERENCES TB_ELECT(F_ELECT_NO);

CREATE GENERATOR GEN_AUTO;
CREATE GENERATOR GEN_AUTO_LONG;
CREATE GENERATOR GEN_AUTO_FREE;


/* View: V_AMMETER_DATA, Owner: SYSDBA */
CREATE VIEW V_AMMETER_DATA (F_TRANSFER_NO, F_HOUSE_NO, F_CT, F_AMMETER_ORDER, F_LNO, F_AMMETER_NO, F_HOUSE_NAME, F_BOX_NO, F_THIS_COUNT, F_AMMETER_LAST_COUNT, F_COUNT, F_ADJUST_COUNT, F_NOTE) AS

select h.f_Transfer_no,
       h.f_house_no, 
       h.f_ct,
       a.f_Ammeter_order,
       a.f_LNo,
       a.f_ammeter_no, 
       h.f_house_name, 
       h.f_box_no,
       a.f_this_count,/*本月抄表数*/
       a.f_ammeter_last_count,/*上月抄表数*/
       a.f_count,/*表计量*/
       h.f_adjust_count,/*调整*/
       h.f_note/*说明*/
from tb_ammeter a,
     tb_house h,
     tb_current c
where a.f_house_no = h.f_house_no 
  and c.f_value = h.f_transfer_no
  and c.f_class = 'transfer'
;

/* View: V_FEE, Owner: SYSDBA */
CREATE VIEW V_FEE (F_HOUSE_NO, F_HOUSE_NAME, F_TRANSFER_NO, 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_AMOUNT, F_ARREARAGE, F_LATEFEE) AS

Select * From Tb_Fee
Where f_Amount <> 0

;

/* View: V_HOUSE, Owner: SYSDBA */
CREATE VIEW V_HOUSE (F_HOUSE_NO, F_TRANSFER_NO, F_VOLUME_NO, F_VILLAGE_NO, F_HOUSE_NAME, F_POSITION, F_TELEPHONE, F_VOLTAGE_NO, F_MASTER_AMMETER_NO, F_BOX_NO, F_DATE, F_ADJUST_COUNT, F_SON_COUNT, F_SUM_COUNT, F_BASE_COUNT, F_ELECT_NO, F_PRICE, F_COUNT, F_AMOUNT, F_RATE1, F_ELECT_NO1, F_PRICE1, F_COUNT1, F_AMOUNT1, F_RATE2, F_ELECT_NO2, F_PRICE2, F_COUNT2, F_AMOUNT2, F_RATE3, F_ELECT_NO3, F_PRICE3, F_COUNT3, F_AMOUNT3, F_NOTE, F_ARREARAGE, F_2YEAR, F_LATEFEE, F_USED_AMOUNT, F_CT, F_USED_COUNT) AS

Select * 
From Tb_House
Where f_Used_Amount <> 0
;

/* View: V_HOUSE_AMMETER, Owner: SYSDBA */
CREATE VIEW V_HOUSE_AMMETER (F_HOUSE_NO, F_TRANSFER_NO, F_HOUSE_NAME, F_BOX_NO, F_CT, F_ELECT_NO, F_RATE1, F_ELECT_NO1, F_RATE2, F_ELECT_NO2, F_AMMETER_NO, F_AMMETER_COUNT) AS

   Select
         H.f_house_no,/*户号*/
         H.f_Transfer_No,/*变压器编号*/
         H.f_house_name,/*户名*/
         H.f_box_no,/*箱号*/
         H.f_ct,/*CT*/
         H.f_elect_no,
         H.f_rate1,
         H.f_elect_no1,
         H.f_rate2,
         H.f_elect_no2,
         A.f_ammeter_no,/*表号*/
         A.f_ammeter_count/*底数*/
      From Tb_House H Left Join Tb_Ammeter A 
           On H.f_house_no = A.f_house_no
;

/* View: V_LINE_FEE, Owner: SYSDBA */
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) 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)
   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

;

/* View: V_MULTI_FEE, Owner: SYSDBA */
CREATE VIEW V_MULTI_FEE (F_HOUSE_NO, F_HOUSE_NAME, F_TRANSFER_NO, 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_AMOUNT, F_ARREARAGE, F_LATEFEE) AS

Select * 
From V_Fee
Where f_substr(F_House_No,7,7) = '/'

;

/* View: V_SINGLE_FEE, Owner: SYSDBA */
CREATE VIEW V_SINGLE_FEE (F_HOUSE_NO, F_HOUSE_NAME, F_TRANSFER_NO, 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_AMOUNT, F_ARREARAGE, F_LATEFEE) AS

Select * 
From V_Fee
Where f_substr(F_House_No,7,7) <> '/'

;

/* View: V_TRANSFER, Owner: SYSDBA */
CREATE VIEW V_TRANSFER (F_LINE_NO, F_LINE_NAME, F_TRANSFER_NO, F_TRANSFER_NAME) AS

Select L.f_line_no,
       L.f_line_name,
       T.f_transfer_no,
       T.f_transfer_name
   from tb_line L join tb_transfer T
      on L.f_line_no = T.f_line_no
;

/* View: V_TRANSFER_FEE, Owner: SYSDBA */
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) 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)
   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

;

/* View: V_TRANSFER_WASTING, Owner: SYSDBA */
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

;

/* View: V_VILLAGE, Owner: SYSDBA */
CREATE VIEW V_VILLAGE (F_TOWN_NO, F_TOWN_NAME, F_VILLAGE_NO, F_VILLAGE_NAME) AS

Select t.f_town_no,
       t.f_town_name,
       v.f_village_no,
       v.f_village_name
   from tb_town t join tb_village v
      on t.f_town_no = v.f_town_no
;

/* View: V_ZEROFEE, Owner: SYSDBA */
CREATE VIEW V_ZEROFEE (F_HOUSE_NO, F_HOUSE_NAME) AS

Select f_House_No, f_House_Name 
From Tb_House
Where f_Used_Amount = 0
;
ALTER TABLE TB_HOUSE ADD 
        check (f_2year in ('Y',''))
;

/*  Exceptions */
CREATE EXCEPTION RAISE_AMMETERNOTEXIST "你所输入的表号并不存在";
CREATE EXCEPTION RAISE_TOWNNOTEMPTY "乡镇下还有村子，不能删除";
CREATE EXCEPTION RAISE_VILLAGENOTEMPTY "村下还有用户，不能删除";
CREATE EXCEPTION RAISE_STATIONNOTEMPTY "电站下还有线路，不能删除";
CREATE EXCEPTION RAISE_LINENOTEMPTY "线路下还有变压器，不能删除";
CREATE EXCEPTION RAISE_TRANSFERNOTEXIST "你所指定的变压器并不存在";
CREATE EXCEPTION RAISE_HOUSEONLYDELETE "户号不允许修改,只可删除后重建";
CREATE EXCEPTION RAISE_AMMETERDUPLICATE "表号必须唯一,不允许重复";
CREATE EXCEPTION RAISE_TRANSFERNOTEMPTY "此变压器下还有用户,不能删除";
COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */
CREATE PROCEDURE P_LOG AS BEGIN EXIT; END ^
CREATE PROCEDURE P_ADDMSG AS BEGIN EXIT; END ^
CREATE PROCEDURE P_SETMSG AS BEGIN EXIT; END ^
CREATE PROCEDURE P_GETMSG AS BEGIN EXIT; END ^
CREATE PROCEDURE P_UPD_PASSWORD AS BEGIN EXIT; END ^
CREATE PROCEDURE P_GETHOUSE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_GETLAST AS BEGIN EXIT; END ^
CREATE PROCEDURE P_SETCURRENT AS BEGIN EXIT; END ^
CREATE PROCEDURE P_GETPRICE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_GETSERVICEPRICE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_GETCOUNTRYPRICE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_GETCOUNTRY AS BEGIN EXIT; END ^