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.

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.

SM Bill Adjustment

For the third time, I had to assist Mike in billing SM Pampanga because there needs to be a certain sequence in printing their monthly bill. Some monthly adjustments are posted so that it will reflect on their current bill.

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.

Network access for Ronadeth

Gave network access to "Rona 2" by installing a 16-port C-Net 10/100 switch near GloPM and making 2 short LAN cables. Took me about an hour because I had to McGyver the DC Adaptor just to plug it into the APC UPS. We all know how APC UPSes are "for PCs only" because of their "monitor cable" jacks.

Philhealth

Answered queries about their overpayment for 1 account, June 2007 arrears (no FINAL VAT posted yet) for 2 accounts, etc...

Street Lights report

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

Street Lights report

DPP asked me to print out the bulb details of a particular Street Light account, so now I'm creating a Kristal report...

iPod working again

(Sept. 15, 2007):

Thank God, my iPod is working again!

I thought I would open up the case, since I've got "nothing to lose" cuz it's been displaying the SAD iPOD icon for about 2 weeks already...

To my surprise, a piece of broken copper connector fell out as one side of the cover was lifted.


While I was at it, I also reseated the ribbon cables on the boards and hard drive.

After this, the iPod started working properly again! No problems whatsoever!

That piece of copper might have been shorting out some circuits inside the iPod that's why the hangs were intermittent!

Oh well, at least now it's working again...

Auction

Woohoo!!! I won again yesterday (Victoria's Secret Pretty in Pink perfume and lotion) but it was "under contract" with Rose so she's gonna pay for it. Still, it feels pretty good to win again!

Oh, and 2 weeks ago, after winning the perfume, I won perfume for women, using my wife's account.


After I won, GEMD won for the first time this morning! Yehesss!!!

Firewalls and proxy servers

I'm still at a loss if I will take the Fortigate 60 for our Main Office. Marvin Lim of Comclark has managed to convince me to go for their wireless link by lowering the MSF's. But still, I would like to use Endian for the firewalls and the VPN, because they are Linux-based and I am more familiar with Linux than FreeBSD on which pfSense is based. I cannot find a way to make EFW to connect to the Fortigate 100A demo unit thru IPSEC. However, Endian Firewall Appliance seems too expensive for our needs. :-(

Gadgets

When it rains, it pours!!!

At my favorite auction site, I won a scanner last week, and a bottle of perfume today! Plus, I have a new digital camera, which I got just last night! I went to SM and I bought a neoprene carrying case just enough to house the camera unit (a Samsung S730) and 12 extra AA batteries (of course, I bought 8 more Ni-MH AA's to augment my 4 batteries). I also put in a 2GB 150x Apacer SD Card. Man, is it fast!!! :-)

My wife's cousin Bernard was kind enough to promise to give me his 4 Duracell rechargeable AA's before he goes back to the U.S. of A.

Indeed, it pours!!!

Thank you, thank you, thank you, Lord!!!

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.

San Jose Malino take-over

Oh, man!

I've been spending lots of precious time manually matching RAJE's data with PRESCO's. I started this on Friday morning, and passed it on to Rico, who passed it on to their 2 OJT's. Now I'm posting this at home, after a few hours of more manual matching! The OJT's didn't seem to get much work done. :-(

Okay, just to vent out my frustration with RAJE, especially with Mariliz, who should've known better and asked for Account Numbers in the first place:

I used VLOOKUP() functions to search for account numbers in the PRESCO data where the name matches RAJE's. Problem is, Mariliz just typed into the computer whatever RAJE's meter readers wrote by hand, so there were a lot of TYPO's, both in names and serial numbers, hence, a lot of "#N/A" values found. These "N/A's" are the ones we need to scrutinize. Some are simple enough to find, like missing MIDDLE INITIALs. Others are a little tougher, like misspelled surnames and/or first names. A little bit tougher still are those with two first names and/or full names (!).

I also included another VLOOKUP() for the serial number just to verify that the account number found was "legitimate." I had to do this because there were multiple records with the same registered names. Same problem with TYPO's here.

This is the manual matching procedure:

I find the next record with no matching account number (#N/A). I press CTRL-PGDN to go to the PRESCO sheet, press CTRL-F to open the Find dialog box, type in the surname, press ENTER, and if there's a matching surname, i look for the first one with a matching first name. If still not found, I look for a similarly spelled surname, and so on. If the surname search fails, I search for a matching FIRST NAME with a SIMILAR-SOUNDING SURNAME, and this usually is the last step. If still not found, I mark it so.

Here are a few samples of matches I made, after using much effort:

RAJE: Dolante, Dexter.
Dolante not found in PRESCO's data.
Dexter found with surname Dollente. See what I mean?

RAJE: Ignacio, Cynthia
found: Ignacio, Abel / Cynthia. not so hard.

RAJE: Dizon, Angelito.
found 1 Dizon, Angelito, METER doesn't match.
found 2 "Dizon, Angelito / Gertrudes", second one matches Meter serial #. I add "#2" to the name in PRESCO's data, and make a similar change in RAJE's data. Marked.

RAJE: Figueras, Elizabeth.
Figueras not found.
Elizabeth searched, found "Figuerres, Gerald / Elizabeth"

RAJE: Garcia, Maria
found several Garcia's. matching meter serial# is "Garcia, Ma. Blecille"

another FIGUERAS -> Figuerres.

"Binondo, Alvin" -> Binondo, Arvin

"Nario, Salta" -> "Salta, Roda / Mario". serial#39308591 vs. 39308519

"Varba, Percival" -> "Barba, Percibal"

"Dobie, Jude" -> "Doble, Jude"

"Mecedita, Santos" -> "SANTOS, MERCEDITHA B."


THIS ONE'S TOUGH:

"Caplasa, Lee Joy" -> "Gadiaza, Leo Joy"

Let's go on...

"Tubon, Mario Jr. I" -> "Tubon, Mario L. Sr. / Marie"

"Antonio, Ramil" -> "Antonio, Raminer L."


..... and many, many more....

After this, I'll have to make a pivot table to count instances of account numbers, to check for duplicate records...

For now, I think I'll go to sleep.

It's already 11:23 PM, I can feel my pimples popping out. :-(

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.

Glo's PC

Last month, GPM's PC blew its PSU. Rather than replacing it with a working one and risking another blow-out, I lent her Rose's replacement PC. The next day, that too, blew out its PSU. Mike suggested that I replace the monitor because it might be the culprit. Fortunately, the replacement PSU's arrived that day and the problem is solved. I claimed back the lent PC and she's now using her original PC.

Home DSL

Oooh.... 62.6kB/sec on my home DSL which is only rated at 384kbps! Nice....

Infotext project

Finally, I was able to inform ~ 1100 subscribers about a scheduled power interruption on May 3...

Some of them replied with thanks, some of them are just so darn intellectually challenged and some are just plain rude...

Note that Garbes/Globe sold them the SIM Cards when they paid their bills at our office.

Here are some funny and not so funny responses when I sent an introductory message saying something similar to "This is the SFELAPCO Txtline, we will use it to inform you of scheduled power interruptions, etc.":

"Hus dis pls,dong?"

"Hu u"

"H0w did u trace my #?"

"Cno po cla?"

"Who u bt u lam # me txbk."

"Hv u."

"Ha?"

"Bolang"

"hU u PoEh?"

"Ok.. Wer u frm?"

"Y?"

"Hu u? Wat's ur # gLobe or TM? Plz...Txtbk!"


paano nga naman aasenso ang Pilipinas nito???

Payment adjustments

Been doing the usual corrections to payments for Soc and Agnes day by day, by day... (Hahaha... reminds me of "Meet The Parents")

Duplicate adjustments

Deleted another set of duplicate artran adjustments originating from LH sub-office. this time acct # 18538

delete from artran where id between 5542400 and 5545235
delete from artran where id between 6515478 and 6515479

then applied the jan ONACT to remaining balance...

Payment Corrections

Did a few payment corrections yesterday for Soc. I also corrected readings made by Tolio because he input May instead of April in his handheld's Date setting. I also corrected 5 areas because his niece, Mariliz, also input May as the reading month when she created the download .dat files.

Today, had to delete a payment because PSP accidentally posted it when someone queried for their outstanding bills.

Received an invitation to attend a "seminar" on Business Intelligence alignment with I.T. strategies. My eyes bulged out when I saw the price tag ($987 !!!). Whoah!! I guess I won't be going, then.

Wi-Fi

Woohoo!!! I've just setup my first Hotspot!

I installed a Linksys WAP54G (firmware in the 3.x series) and I managed to connect 2 N93's and RCD's IBM Thinkpad R50E, which I used to test how far the connection can go. I placed the AP near the corner of the Gigabit switch, to which I connected it, and I could get a connection from my cubicle [3 concrete walls!], although a tad intermittent, to JTL's doorway. Hmmm.... I guess I need at least 1 repeater for Admin, 1 for Billing and 1 for the other Building.

Payment Corrections

For Agnes:
(Joel's fault)
update payment p set ornumber=concat('0',ornumber+713450,'T') where ornumber between '0079271T' and '0079278T' and payment_date='2007-04-23'
update artran p set docno=concat('0',docno+713450,'T') where docno between '0079271T' and '0079278T' and docdate='2007-04-23'
update payment p set ornumber=concat('0',ornumber-7134500,'T') where ornumber between '7927229T' and '7927342T' and payment_date='2007-04-23'
update artran p set docno=concat('0',docno-7134500,'T') where docno between '7927229T' and '7927342T' and docdate='2007-04-23'

Soc's fault?
change from Wtax to FinalVAT: 64.84 pd_id=1369248, o.r.# 0006202B

MCAJr's PC

Took over from Rico after he reformatted drive C: and reinstalled Windows and Office.

Installed Autopatcher until Feb 2007 patches. Turned on automatic updates, babysat it for a while as I installed more apps and tried out Comclark's internet connection.

Payment corrections

For Agnes, added 0 at the start of 1 O.R.#.

For Soc, transferred 1 O.R. to PCI Dolores.

Payment Type Correction

For Agnes:

Changed payment type from Final VAT to Withholding Tax: O.R.# 0006277B, account #47978.

Duplicate account

Aching Glo asked me to delete the new account she created because there's already an existing account (she didn't know someone else had created it already):

delete from consumer_master where establishment_businessname='E.L. VALERIO REALTY'
and account_no_id=77854

Peachtree problem

MCAJr's Peachtree on his workstation suddenly stopped working (IE script errors). Links found via google all said UNINSTALL-REINSTALL is the fix.

I'm thinking I should just reinstall the O.S. and apps altogether just to get rid of the "Access Denied" issue on several folders (even when using an Admin account!).

Payment Correction

For Agnes:

Posted correction to a misposting by Obet to Acct # 557 (Shd. be #50557).

For Soc:

Edited 0053513B to add Php367.22.

Corrections

April 18, 2007:

13:12 for DPP:
update consumer_connection set current_status=0 where consumer_connection_id=44564;
update consumer_connection set current_status=1 where consumer_connection_id=45853;
update consumer_connection set account_no_id=44532,meter_information_id=44564 where consumer_connection_id=100034403;

13:29 for RAJE:
update meter_lup set demand_multiplier=multiplier where demand_multiplier=0

for Soc:
0051402B deleted for Soc.

0004625B ??? (can't remember if I did something to this O.R., it just got pasted here. I'm retaining it for future reference)

0052583B change to 0052883B

April 19, 2007:


changed o.r. #611011443T to #0786455T
input 0786348T just to cancel

along with corresponding updates to payment_details_vat:
update payment_vat_summary set generation=-84.08 where payment_detail_id=1375817
update payment_vat_summary set distribution=-21.97,generation=-37.21,system_loss_gen=-4.96,transmission=-16.04,system_loss_trans=-2.44 where payment_detail_id=1375817

corrected several consecutive O.R.#'s for Soc. Whew! This one took much time!

Payment and Job Order corrections

For Soc:

transfer bank from 94 (Plantersbank Mt Carmel) to 91 (AUB) 0052924B

subtract Php 0.19 from 0049242B


For DPP:

bring back previous connection record as current status (someone misposted a disconnection to account # 45167)

Annual Stockholders' Report

Whew! I've just finished 40 copies. While I was printing the 40 cover pages on the glossy photo paper I bought at SM Dept. Store last night, I had someone buy the plastic binders and I punched the holes on the pages.

As usual, I had to cut the papers containing the body of the report on the right side because they're in letter size while the cover pages are A4. Then I had to cut the cover pages on the bottom side because they're more than 11 inches long.

All in all, I did an almost perfect job in the binding process. I didn't ruin any pages today! Hurray!!! The only mistake I made was on 3 sets of Financial statements and I remedied it by cutting the mispunched side instead of the right side. :-)

Monthly reports

Finally, I've finished my monthly reports after getting submissions from CFC and Amy A.

I kinda forgot to send Yvonne A. her copies, though, because I was juggling the annual stockholders' report, my monthly reports, Garbes/Globe's request for a list of residential customers, Virtualization research (I'm itching to try it!).

(Yawn) All this work is tiring me, I think I'll catch 40 winks...

Oh, no! I just realized, JTL still hasn't given me the pictures I'll be using for the cover! :-(

Annual Stockholders' Report

Whew!

Seems that JTL sent Mr. President his own FINAL version before Irwin consolidated all the corrections and changes by CFC and AGD! I had a hard time reviewing two (2) final versions again.

I hope I didn't mess it up...

Payment Correction

for Agnes: (Anna Romina Yalung's receipts)

update payment p set ornumber=CONCAT('0',ORNUMBER+30000,'T') where ornumber between '0738433T' and '0738609T' and payment_date='2007-04-03';
update artran p set docno=CONCAT('0',docno+30000,'T') where docno between '0738433T' and '0738609T' and docdate='2007-04-03';

Name correction

for Joylan:

UPDATE `consumer_master` SET `firstname`='MAYNARDO MALCOM' WHERE `account_no_id`=77772;

Payment Correction

for Soc:

Subtracted Php 0.42 from O.R. # 0032438B.

New Meters

Employed my meter_lup robot adder workbook again for Jorge. imported 101 meters from SFE # 83158 to 83258.

Gateway PSU problem

Upon arrival at the office, Gina told me we had to internet access.

I immediately realized that the gateway PC's PSU has died. So I quickly replaced it, and within 15 minutes, it was up and doing its job once more.

O.R.# correction etc

Soc told me Mang Efren repeated a batch of O.R.#'s which he'd already issued on 04/11:

update payment set ornumber=concat('00',ornumber+13,'B') where ornumber between '0052871B' and '0052882B' and payment_date='2007-04-12'

update artran set docno=concat('00',docno+13,'B') where docno between '0052871B' and '0052882B' and docdate='2007-04-12'

Subtracted Php10,000 from O.R.# 0053178B.


For Agnes:
Inserted details for a cancelled O.R.:

insert into payment_details values (null,1334289,1732418,20,9.78,0,0,0,0,NULL,NULL,NULL,20070402171034,"",NULL,2047610,'2007-02-19',NULL,NULL,0,0,864405,3)

Changed O.R.# from 9743626T to 0743626T.

VAT Discrepancy

Corrected a 1-centavo discrepancy for O.R.# 0778993T. Subtracted it from the EVAT-Generation.

This is for acct # 4136.

Payment Correction

Modified 3 payments for Soc:

1. O.R.# 0050393B: transfer from Field Collector (id # 43) to EWTax (# 45) and change O.R.# to 0006202B
2. O.R.# 0052565B: subtracted Php6.00
3. O.R.# 0052896B: subtracted Php1.00

Annual Stockholders Report

Finished the first Final Draft. AGD missed several grammatical errors by Irwin. Had to manually add additional spaces after sentences. The graphs look nice! Hehe...

Payment Date Correction

Changed payment date of O.R.# 0006198B from 4/12/2007 to 4/13/2007, for Agnes.

Payment Correction

Modified 4 payments (4 O.R.'s) for Soc, corrected 2 amounts, transferred 2 from Maybank to RCBC SF Main.

SM Payment Collection

Ms. Vona asked me to resend to her our account numbering convention. I also gave her 20 actual account numbers for their test run.

I also finally got around to scanning an actual billing statement and sent it to her.

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.

rate computation

Finally, the April 2007 rates are done. Rocky and I spent a few hours checking and counter-checking because I wasn't consistent in my changes to the "Unbundled Power/Florida Bill" workbooks. Darn MSP Common Charge!

annual stockholders report

Despite having my printout of Load and Power Factor, Irwin still asked me which figures are which. Duh... :-(

payment correction

Modified a couple of payment records for Agnes. Can't remember the O.R.#'s right now, because that was a few hours ago.

annual stockholders report

Irwin asked me for a copy of "Load and Power Factor" report for 2006, so I gave him one fresh printout.

Yesterday he asked me for the summary and I also gave him a copy. That was the copy I asked Rocky to give to him, but apparently he didn't get it. :-O

rate computation

Whew! Finally, I'm done with the April rate computation. Had to augment my formulas for the EVAT and UC sheets on the individual franchise workbooks... forgot to include the late bills' UC and VAT values...

meter reading date correction

Corrected dates for Power Bills days 6,7,8.

Problem is, Mike uploaded the uncorrected data before I corrected the upload data files!

So I had to:

update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611011265';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611010955';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0511010556';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611010747';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0511010557';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411010540';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411010872';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0511011295';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611011495';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0511010325';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0511010470';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0511010456';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0511011446';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0101020517';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0211010582';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611011210';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611011211';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0511010560';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411010643';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411014285';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611010386';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611010011';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0111010666';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0511010000';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0111011451';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411010522';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0111010139';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0111010140';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0111014270';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0111014271';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0511010072';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0511013914';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0100076916';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0111010122';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611011366';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611010064';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411010576';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411011616';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611010123';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611010280';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411010101';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411010647';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411010605';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0111010251';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611014253';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411010032';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0111014159';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0811010734';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411010049';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0511010050';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411010053';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411010054';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0500076565';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611010570';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0611010222';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0411010505';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0511010574';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0111014116';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0811010732';
update meter_reading set presentreading_date='2007-04-07' where actual_billing_month='200704' and oldaccount_no='0111011532';

meter reading date correction

Corrected dates for several records in area 0231 (000231u.dat)

payment correction

Corrections made for Soc:

update payment set payment_date='2007-04-03' where ornumber between '0047904B' and '0047916B' and payment_date='2007-04-02'
update artran set docdate='2007-04-03' where docno between '0047904B' and '0047916B' and docdate='2007-04-02'
update payment set payment_date='2007-04-03' where ornumber between '0050956B' and '0050984B' and payment_date='2007-04-02'
update artran set docdate='2007-04-03' where docno between '0050956B' and '0050984B' and docdate='2007-04-02'
update payment set payment_date='2007-04-03' where ornumber between '0048598B' and '0048605B' and payment_date='2007-04-02'
update artran set docdate='2007-04-03' where docno between '0048598B' and '0048605B' and docdate='2007-04-02'
update payment set payment_date='2007-04-03' where ornumber between '0046289B' and '0046302B' and payment_date='2007-04-02'
update artran set docdate='2007-04-03' where docno between '0046289B' and '0046302B' and docdate='2007-04-02'
update artran set docdate='2007-04-04', user_id=27 where docno IN ('0038833B','0038855B','0038856B','0051651B','0051652B','0051653B','0051654B','0051655B','0051657B')
and user_id=23
update payment set payment_date='2007-04-04', teller_id=27,created_by=27 where ornumber IN ('0038833B','0038855B','0038856B','0051651B','0051652B','0051653B','0051654B','0051655B','0051657B')
and teller_id=23

0050992B less 0.50
0050216B add 279.24
0052499B add 0.56

BCMS issue

Hmmm... the PAYMENT vs. VAT+UCME+UCEC issue has resurfaced!

I just asked A. Glo to exit the teller module and re-enter.

Hmmm.... I wonder...

Payment Correction

Modified 2 O.R.'s for Agnes (switched FINAL VAT and W/HOLDING TAX entries).

JTL O2007

JTL asked me to install O2007 on his new laptop. Spent about an hour on it.

And this is right in the middle of editing 000205u.dat for RAJE. I spent more than 2 hours on that and still MOVBLANK won't move unread records!!! Sheesh!!!

Well, at least I was reminded that I have QEdit's macros on my arsenal when it comes to such problems....

Meter Reading Date Correction

4/10/2007 9:47 43,522 000107u.dat
4/10/2007 9:47 31,772 000110u.dat
4/10/2007 9:48 18,988 000125u.dat
4/10/2007 9:48 8,836 000126u.dat
4/10/2007 9:48 27,354 000120u.dat

Justine Gabionza likes Linux?

While searching for a name associated with someone who has kept texting and sometimes miss-calling me since Feb 23, 2007, I came across Justine Gabionza's name (she's a Filipina Beauty Queen) so I wondered what she looked like.

I found this picture of hers. I wonder if she uses Linux? :-)

Payment correction

Modified a payment record for Agnes/Soc: Made them cancel the misposting and made Soc post it on another date so I can just modify the date.

Windows Vista validation

Hmmm... so it was another firewall (iptables) issue that prevented the Neo notebook PC from connecting outside our LAN. Sheesh!

Was finallly able to VALIDATE Vista after I put the SQUID entry for it BEFORE all the other SQUID entries in iptables.

I guess I'll have to research on iptables limits...

Windows Vista activation and validation

Yesterday, JTL asked for my help in activating Vista Home Premium on his newly purchased Neo QNote Core 2 Duo.

I didn't want to connect it to the office network, just as a precautionary measure. So I opted for the toll-free telephone activation.

I got an "invalid Installation ID" from the robot. Duh?!?! Then I noticed that there's some text on the bottom-right corner of the screen saying "... This copy of Windows is not genuine." OOPS!!!

I thought it was a pirated copy, until this morning when I got to research on it. Seems like there've been plenty of FALSE ALARMS regarding this activation/validation thing.

I told JTL that I'll try activating over the net (by connecting it to our office LAN), but I decided to avoid any detection of a potential pirated copy coming from our network, so I tried the telephone activation again. Voila! This time, it worked! Sweet, Microsoft, just sweet. Duh!!!

Wait a minute!!! Why is the "this copy is not genuine" text still on the corner of the screen?!?! Oh, so I still need to validate it, huh? Sheesh!

Will get there later... "Network Access: Local only" is now my problem. :-(

Payment misposting

Posted adjustment due to misposting by Nette to #14118 (should be #14115), amounting to Php 1,384.59.

Canon lIDE scanner

MCAJr asked me to install the scanner mentioned and its accompanying software on his john2 workstation.

First I booted into safe-mode to get rid of the VBSilly-B virus(es).

I then spent about 2 hours trying to force the installation of the scanner software, to no avail. (I had to set up security for "c:\Program Files" and give FULL access to Administrators, but I couldn't access "c:\Program Files\installshield installation information" !!!

I eventually gave up.

Payments correction

Transferred 5 receipts from March 30, 2007 to March 29, 2007 (wrong date) - for Soc.

Transferred 2 receipts to EPCI-SINDALAN, corrected 1 of them (ornumber) - for Soc.

INFOTEXT Project

Last Thursday (March 29, 2007), our Superload trial period ended (I was wondering where the REPLY button disappeared into, HEH!).

So I researched on how to use it on Linux so I can deploy it on our server.

I followed the instructions to a TEE, but BEARERBOX would SEGFAULT after OPENING the device.

After several retries, I tried making the modemtype entry ALL CAPS, because that's how it was written on the modems entry. Surprise, surprise! Now BEARERBOX isn't SEGFAULTing! Bad news, though: it cannot get a response from the iTegno WM-1080+ GSM Modem, even if I configure it on /dev/ttyUSB0 or /dev/usb/ttyUSB0 (that's how dmesg reported it).

I tried minicom but it would just hang. I also tried installing a later kannel version than the one apt installed, but it needs a newer libxml2, which in turn needed a whole slew of updates on other dependencies. Boo-hoo-hoo!!!

Now my only option is fixing the communication problem with the GSM modem.

I'll ask on PLUG or maybe Tonton on YM.

iPod still hanging

Aaaaaarrrgh!!! My iPod kept hanging again! Even during restoration! :-( :-(

I borrowed another cable (Firewire+USB) and it still hangs when connected to the PC! :-(

I think I restored it half a dozen times yesterday, and finally, it didn't hang UPON CONNECTION to my workstation. Prior to this, it only connects when in manual DISK MODE (RESET it, then press SELECT and PLAY simultaneously when the Apple logo appears).

I succeeded in copying the latest episodes of 24 and Prison Break, and then all of Heroes Season 1, and some unplayed podcasts. Then it started hanging again when I was copying songs. :-(

I wish I had gone on with my upgrade and SOLD it on eBay last month! :-(

PC Reboots

Last week, we started using SuperLoad and an iTegno WM1080 on a 1-week demo period for our INFOTEXT project.

The computer I installed it on seems to have a hardware problem (probably the hard disk and/or the power supply unit) and it keeps on rebooting (almost daily). I'd leave it on at night and the next morning, it's waiting for a NEW LOGIN to Windows XP. Today, the Event Viewer reported that there are problems on the D partition (which has no files on it). I'm running CHKDSK32 on all partitions now.

FB sequence numbers

Found 5 new sequence numbers in area 0901. Discovered in horror that DPP was already printing bills without my go signal (!!!). Good thing Rico updated the sequence numbers last night before he left the office (after uploading FB data). He told me he asked Mitch to stick a reminder on the USB flash drive so that he won't forget to update the sequence numbers after uploading.

Well, this morning, after I realized that DPP is as forgetful as they come, I decided to include a CHECK for sequence number discrepancies for each area BEFORE APPLYING bill numbers. Better yet, I think I will not let them load the data IF any discrepancy was found.

Fixing Receivables

Oh, my! I just realized that I can use the artran_history table to SOLVE the Balle problem! See, Balle's account's artran records were so complicated because of the payment-center-adjustment-going-back-and-forth issue that it would literally give me headache and nausea everytime I tried to fix it! So I fixed it once and for all by transferring the problematic records to artran_history. After all, those bills were already FULLY PAID anyway. It was just a matter of applying credits to the proper bills.

Problem solved. Brilliant workaround, eh? I thought so, too. The ledger shows up the same way. Tellers will see only the March 2007 bill on their screens when the consumer pays their bill.

Then I realized that I haven't checked the Statement of Account report. To my chagrin, it shows the unsolved data as they were (as it should)! Aaaaarrrrrgggh!!!

Oh, well, I guess I'll just have to be content in knowing that the tellers will not be seeing the confusing posts anymore. I'll just have to pray that the consumer won't ask for a Statement of Account anytime soon. I promise I'll still try to fix the transferred records.

hanging iPod

Aaaarggghh!! My iPod started hanging a few weeks ago! It would hang when iTunes is copying files onto it. Several websites I found say the same thing: REPLACE THE SYNC CABLE. I tried to find one, but couldn't. The problem re-occurred a few times over the next couple of weeks and then, last Saturday, it wouldn't accept any more movie files (specifically, the latest episodes of 24 and Prison Break).

I had to be able to copy them over because I only watch these TV series episodes on my iPod, so I decided to back up my files and RESTORED the iPod, using ANOTHER PC than that which I usually use with my iPod.

I then proceeded to copy back my music and podcasts, and only the latest TV episodes. So far so good. I ruled out the cable as being the culprit.

After 2 days, the iPod had no more charge, so I recharged it using my cheap China-made wall charger bought from Compex, at home (I don't bring home my sync cable anymore). Then, at the office, the iPod started hanging again when copying files over! I started suspecting my workstation's USB extension cable/USB jack/motherboard. It's either that or the cheap wall CHARGER. But I tried it on the other PC and it worked! So now I think I'll be copying files onto the iPod using the other PC.

More on this later...