BCMS: Bug Fix

My additional code for automatically Unvalidating zero and negative consumptions bit me.

I should've never touched the VALIDATED field if the computed consumption is negative (zero is now acceptable as valid), so I corrected it not to touch the VALIDATED field if the computed consumption is NOT negative. Otherwise, UNVALIDATE it.

I also corrected the Rover UPLOAD module to check for a matching area_id where searching for the account_no_id of the current consumer whose reading is being uploaded. I did this because some old account numbers in FB were equivalent to SF old account numbers when the hyphens are removed, and this screws up the upload process for some accounts.

I hope I didn't introduce new bugs this time.

BCMS: Data integrity fix

DPP and ELP approached me and reported that there was a NEGATIVE BILL last month. ELP was very quick to point out that i should put in code that checks for negative consumptions during bill computation.

My first reaction was unfavorable; I told him the flow of the system should be sufficient in preventing such problems. After some more thought, though, I realized that it's not a bad idea to do that as a "last line of defense" against this particular problem.

I then proceeded to search for bugs. During this search, I found out that the account is a mother meter and quickly realized that it was already PreVALIDATED by the program and retained its validation status even after the encoder has subtracted the sub-meters' consumptions. So I also added code that re-validates the reading after said input.

BCMS: Data Archiving note #1

select min(date_processed) from sfelapco.bill_detailed_summarized
11/1/2007
select min(date_processed) from old_data.bill_detailed_summarized
6/1/2006
select min(date_processed) from sfelapco2.bill_detailed_summarized
6/1/2006

select max(date_processed) from sfelapco.bill_detailed_summarized
1/20/2008
select max(date_processed) from old_data.bill_detailed_summarized
5/1/2007
select max(date_processed) from sfelapco2.bill_detailed_summarized
10/20/2007

select count(*) from sfelapco2.bill_detailed_summarized
where date_processed between '2007-10-01' and '2007-10-31'
70187
select count(*) from sfelapco2.bill_detailed_summarized
where date_processed between '2007-09-01' and '2007-09-30'
70408
select count(*) from sfelapco2.bill_detailed_summarized
where date_processed between '2007-08-01' and '2007-08-31'
69943
select count(*) from sfelapco2.bill_detailed_summarized
where date_processed between '2007-07-01' and '2007-07-31'
69272

BCMS: Data Archiving

This entry serves as the main documentation for BCMS Data Archiving. Upon completion, it should have the following benefits:

1. Quicker backups.
2. Quicker data transfers/uploads.
3. Faster report generation.
4. Faster queries.

Now for the policies:

1. Following the method used for archiving the artran table, we will also archive these tables alongside artran_history to a new database called "history":
a. consumer_bill - Monthly (leave the previous month's data) - indexed on account_no_id, bill_no, bill_no_id
b. consumer_bill_details - Monthly (leave the previous month's data) - indexed on bill_no_id
c. bill_detailed_summarized - Monthly (leave the previous month's data) - indexed on account_no_id, bill_no
d. meter_reading - Monthly (leave the previous month's data)? - indexed on meter_reading_id, account_no_id, billing_month, actual_billing_month
e. payment - Monthly (leave the previous month's data) - indexed on account_no_id, ornumber, payment_date+teller_id
f. payment_details - Monthly (leave the previous month's data) - indexed on payment_detail_id, payment_id
g. payment_details_vat - Monthly (leave the previous month's data) - indexed on id, payment_detail_id
h. sql_logs, transaction_logs - Monthly (purge) - indexed on user_name, date?

2. All historical tables should be MyISAM.

3. Convert bill_detailed_summarized to MyISAM.

4. Update reports.

5. Immediately make a backup of the history database after archiving.

BCMS: Sacrificial Lamb

I heavily sacrified myself yesterday...

We were in a meeting to resolve the issues of LC's (load centers) servicing consumers in multiple areas. This problem surfaced because we wanted to be able to track consumers using feeders and load centers they are connected to, but our old feeder records are already several-years outdated (long ago, there were only 7 feeders but now there are 14 or 15), and this entails the need to update (and correct mistakes in) consumer records.

Several issues (some only related) came up:

1. RAJE (outsourced meter readers) will have to identify consumers which are recorded in the wrong areas (just a side problem).

2. Using new feeder-area groupings will be chaotic on RAJE's part (scheduling, etc.)

3. To achieve a proper hierarchical structure (Feeder - Area -LC - Meter), all individual LC's will have to be confined to one area each... an ENORMOUS task, to say the least, for the Engineering Department.

So I asked Eng'r. Lapuz if he needs to subdivide Feeders into areas, and when he said NO, I proposed that we:

1. Retain old feeder-area assignments as Revenue Areas, not Engineering Areas. The old FEEDER numbers will be renamed REVENUE GROUP numbers.

2. The FRANCHISE code at the beginning of each consumer's account number will be replaced with the FEEDER code/number they are serviced by/connected to.

The benefits of this scheme are:

1. NO CHANGES in RAJE's scheduling. They don't need to do any additional work!

2. NO TRANSFERRING of meters from LC to LC. They don't need to do any additional work!

3. NO DRASTIC CHANGES in Billing Reports and other stuff that refer to the old FEEDER-AREA numbers. (Note: Will assign modifications of report files to Rico.)

4. NO UNNECESSARY waste of paper (reports that fit 7/8 pages don't need to become 15 pages because of summarization by FEEDER).

The disadvantages:

1. SUBSTANTIALLY MORE WORK FOR moi!!! Aaaaaarrrgggghhh.... :

a. I need to modify the program to refer to old feeder numbers as revenue groups (I need to educate employees on referring to them from now on as such).

b. I need to restructure some tables in the database, which means I need to make sure the changes occur simultaneously at LH and RBM (2 San Fernando payment centers). Florida can be modified the day after.

c. For (b) to occur, I will have to create a temporary database for practice, along with a new set of code, just in case I commit a major blunder and have to revert to the old code.

Notes on 1.b.:
(i) Looks like uploading data from LH and RBM doesn't touch the consumer_master and consumer_meter_info tables, so I won't have to do a synchronized alteration of the table structures.

(ii) consumer_master: feeder_id and meter_information_id0 are both int(10) unsigned. Change these to transformer_id and pole_id, respectively. these have matching types. The feeder_id should then be picked up from the transformer_lup table. transformer here means LC (?)

MySQL InnoDB tablespace reclamation

Been having "table is full" and disk full errors again, so I decided to prune the data.

The usual steps were taken, following the procedures in this site: http://www.saturn.in/gpl/mysql.html

But before following those steps, ...
1. I made sure my latest backup of the main database was complete.
a. copied bill_settings, datafile_settings, and system_settings tables to my own database.
2. I dropped the main database.
3. Followed the instructions mentioned above.
4. Restored the 3 copied tables from my database because they're essential to the restore utility program.
5. Used "restore utility.exe" program to restore the tables.

For this housecleaning instance, I also used "remove zero from artran" program to transfer the fully-paid bills to the artran history table (this took more than 3 hours!!!). While doing this, I made a temporary database on the other server and uploaded last night's backup of artran and artran_vat. I created a table from the still unpaid bills (select account_no_id,applytodocno,sum(docamt) amount,docdate from artran group by account_no_id,applytodocno having amount <> 0.00).

Then I "inner joined" the resulting table to artran_vat and selected matching vat data into an outfile. I'm curious, though, why artran_vat shrunk from 476MB to 5.5MB (??!!). Only 61898 records?!?! With an average of 5 VAT charges per bill, those are only about 12,000 bills unpaid! What the ?!?!

Anyway, I'm rechecking now.

Found the problem. Looks like either a file system or a MyISAM limit was hit when I uploaded the latest artran_vat data to the new server.

Now, from an erroneous 5.5MB outfile, I have a 17MB one. And this time I'm sure the data is complete. Heh.

Addendum: Need to erase the BINLOGs after uploading the data.

BCMS improvement, plus possible bug

First, the good news. DPP requested me to include the Suffix in the Search window when looking for a name in the Ledger module. I did it one better: I included the middle name, too.

Now for the bad news. Agnes complained that the Miscellaneous Report-Final VAT Report is inconsistent with the "FINAL VAT Detailed Collection report per pay type". Particularly with the Oct. 2, 2007 Final VAT remittance of Account # 32780 (168 Victoria Square). I was at a loss why the report was not seeing the 2 payment details for that single O.R., and had spent several minutes looking for the problem on the query statement when I noticed my Payment Editor that the bill_no_id fields for the 2 "invisible" details was too high (>7,000,000) particularly because the bill_no was for the month of August 2007 and the bill_no_id for the 3rd detail was only in the 2,200,000 range and that was for Sept 2007! So I merely corrected the bill_no_id and the bill_date (which contained the "current" date for that posting - 10/02/2007 - instead of the real bill_date which is 08/14/2007).

I'll have to do experiments on this to catch the bug. Low priority, though.