Load Center and Pole Numbers

FVV told Rico that in order to minimize human errors in encoding Load Center and Pole Numbers, we should input them during creation of the customer account, and not when the meter has been installed already.

Makes sense, but it will require A LOT of changes in the system -- in the program and in the reports -- not to mention the shift in encoding practice...

Initially, I mentioned that we should just transfer the fields from the meter page/table to the consumer master page/table, but we will not be able to track the pole and LC histories. So now I'm thinking of transferring the 2 fields to a NEW table, while Rico suggests that we just get them transferred to the consumer_connection table, which I think will take up more server disk space than necessary, as it will entail recording of the 2 fields every time there's a Reco and Disco for a customer where there's no change in LC and Pole.

Update, Apr. 29, 2008:

I've decided to create a separate table, despite it having similar (redundant) fields like modified_by, created_modified, current_status...

Why? Because there are times when the Eng'g. Dept. has to replace rotten poles and there may be METERS that will have to be removed and subsequently put back. We will have no individual job orders per customer then.

Floridablanca server disk full

Last night at around 9pm, Rico texted me and told me that he's getting errors.

I told them to go home because the problem had a simple solution: erase binary logs... and so I did, this morning. I went to the office before 7am and quickly solved the problem.

I also included Michelle to the sudoers, for good measure. So in the future, I can just instruct her over the phone...

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 (?)

BCMS: Bill Printing

Finished modification of Bill Statement report form for 3 more PC's (Gina, Rose, DPP).

Spent several minutes for Rose's and DPP's (the longest to edit).

Took me almost half a day because of some other work that got in the way.

BCMS improvements and fixes

I've made a lot of improvements and a few fixes to BCMS since the last time I posted here. In fact, there has been a slew of minor updates that I've already lost track. :-(

Oct. 25, 2007:
Transaction Logging: Added more descriptive data to the transaction logging of some reports, particularly the Detailed Collection Reports. The need arose when Agnes complained that her "same day" printout of Obet's collection report summary was changed when she reprinted it about a week after. I had to dig deep into the transaction_logs to do some forensic investigation and concluded that she inadventently CHECKED the "Use existing VAT summary" checkbox so that Obet's last few receipts were not summarized, even if she denied having checked it when I mentioned it to her before my investigation.

Ledger Module:
Adjusted some object placements in the Ledger Module so that the PRINT button, which has been fixed to be a MOMENTARY switch several weeks ago, will not disappear when resizing the BCMS window. I also incorporated some changes that Rico needed to identify the exact payment type and A/R type in artran.

Meter Reading (manual input): I've also corrected the code to fetch a consumer's name to place a space between the firstname and the middle initial. Long overdue.

Street Lights Settings: I've incorporated the Street Lights Report into the module. It's "by individual account" only. I also created a table for the City/Municipal street lights so that I don't have to do it in a spreadsheet every time they re-count the bulbs per barangay.

Oct. 26, 2007:
Bill Cancellation (and Unbill): Finally!!! I managed to modify these processes to include the corresponding VAT and UC charges. This is a major accomplishment, administrative-wise.
I didn't realize that when I modified the "Unbill" process, I failed to add a criterion to the UC detection to include the Unbundled UC charges, so they were still being incorrectly posted.

Oct. 30, 2007:
Billing Statements: Printed out statements of accounts for 3 big customers for BIR-auditing purposes. CFC discovered that since we went "Unbundled" the TIN #'s for the EVAT were removed from the text, so I now have to reformat 4 reports on 4 separate computers (well, 3 now, as I've already finished Mike's copy). I hope Gina's PC will accept Mike's copy without need for adjustments since they have the same printer model.

Consumer Management: I let Gina watch me as I edited an account which she isn't supposed to be able to modify. I hope she gets the workaround.

I'll post more when I recall them.

Street Lights report

Finished. Now to include it in the BCMS Program...

BCMS Bill Posting

I forgot when I finished the BILL RANGE feature in the Bill Posting module, but yes, I've already finished it. I think that was last week or the week before.

I might remove the Sequence Number Range frame I've included (not yet functional), but that'll have to wait.

I think I will have to replace the prior button type of the PRINT button in the Ledger module so that the user will not have to click back on the previously used button whenever they changed customers after printing.

San Jose Malino customers

Finally!

I spent almost all day (and all afternoon yesterday preparing for today) in making the meter records for the Malino customers!

Now it will be up to RAJE to find the missing customers/meters.

BCMS User-Friendliness Updates

After the last time I made changes and improvements to BCMS, I've done the following:

1. Fixed the "not unpressed buttons" issue on the Consumer Ledger and Consumption buttons in the Ledger module.

2. Gave access to Disconnection clerk to the overpayment application module because she needs to see which (and how many) months the receivables were for. Initially, she only wanted me to force the Ledger tab in the Ledger module to display the last entry, but I was not able to find a way, at first.

3. In relation to #2, found the way to force the listviews on the Ledger module to ensure that the last item is visible [listview.listitems(listview.listiems.count-1).ensurevisible or some such hocus-pocus].

4. Switched the listviews in the Ledger module not to HIDE the SELECTION highlight when out of focus.

5. Added Syntax-error Prevention code on Bill Posting module when searching for areas.

6. Pressing ENTER on Area field will now erase the contents, if it already has been looked up prior.

7. Added TOTALs to the financial columns in Overpayment module.

8. Switched reports on Rose's new PC to the Epson FX-1170 driver.

BCMS: Overpayment application

Whew! Finally!!! The Overpayment module now takes into account the amount of VAT and UC to apply from bill to bill! Woohoo!!!

In the process, I eliminated some more primitive coding by streamlining again; specifically, using the iif(), min(), and max() functions. The latter 2 I had to build functions for. No big deal.

BCMS: Historical data on consumers

Finally got to finish the Connection History and Meter History tabs in the Consumer Ledger! :-)

BCMS: Additional Feature

Finished the enhancement for the Teller collection report module.

Will now deploy the new .exe for Agnes to witness the last remaining uncorrected VAT discrepancy.

BCMS improvements

Recent changes to BCMS:

1. Added another report (and corresponding button) - Daily Check Payment Report - to the Teller Collection report module.

2. Modified Bill Posting to include 3 options: Late bills only, Current bills only, All Bills.

3. Independent module: Discovered that sometimes, payment_vat_summary doesn't tally with payment_details_vat. I don't know how previous instances (I'm sure they exist) escaped Agnes, so I included pvs in the checking of the vatdisc program. I'm planning on integrating this, including the corrections in the report generating module, into BCMS. Well, at least the pvs data can be automatically corrected if that's the only problem. I can't automate correction of the pdv data if they don't tally with the pd data.

4. Discovered that the VAT_ME_EC variable was not being reset when a teller presses the NEW button in the Teller module. Fixed.

5. Allowed full application of over payments (now includes VAT and UC) but I haven't perfected this yet. Will update on this later, when I finish it.

Soc Proofing

Added some Soc-Proofing (TM) to BCMS. I made that she'll be forced to select a Bank whenever she changes the O.R.# manually. In turn, the date and O.R.#'s are blanked whenever she changes Bank.

I met a lot of resistance from her. In due time, I hope she somes to realize that it's doing all of us a favor.

bcms update

DPP wanted to cancel 2 bills for Account # 3134, but the bills she was cancelling were billtype 'A' (they were adjustments to bills), but BCMS only looks for billtype 'B'.

So: I changed criteria to "billtype in ('A','B') and adjusted<>'T'" instead of Lester's "billtype='B'" only.