Mouth Sores

A doctor friend of mine told me about a remedy for mouth sores ("singaw" or "lasu"):

benadryl syrup 30ml.
2x 500mg tetracycline capsule dissolve in 30ml water
daktarin or daktacort oral gel, 1/4 or 1/2 tspn, dissolve in 30ml water

Mix them all together, you get a 90ml solution.

Gargle it 30ml 3x a day (so 1 set will last only 1 day).

His Needs, Her Needs: Building an Affair-Proof Marriage

From http://tsikot.yehey.com/forums/showthread.php?t=29399

How can a couple prevent adultery? Dr. Willard Harley in his book His Needs, Her Needs: Building an Affair-Proof Marriage provides some answers. He has
found that marriages that fail to meet a spouse's needs are more vulnerable to an extramarital affair.
Often the failure of men and women to meet each other's needs is due to a lack of knowledge rather than a selfish unwillingness to be considerate.
Meeting these needs is critically important because in marriages that fail to meet needs, it is striking and alarming how consistently married people seek to
satisfy their unmet needs through an extramarital affair. If any of a spouse's five basic needs goes unmet, that spouse becomes vulnerable to the
temptation of an affair.

First, let's look at the five needs of a wife. The first need is for affection. To most women affection symbolizes security, protection, comfort, and
approval. When a husband shows his wife affection, he sends the following messages: (1) I'll take care of you and protect you; (2) I'm concerned about the problems you face, and I am with you; (3) I think you've done a good job, and I'm so proud of you.

Men need to understand how strongly women need these affirmations. For the typical wife, there can hardly be enough of them. A hug can communicate all of the affirmations of the previous paragraph. But, affection
can be shown in many ways such as: kisses, cards, flowers, dinners out, opening the car door, holding hands, walks after dinner, back rubs, phone
calls…there are a thousand ways to say "I love you." From a woman's point of view, affection is the essential cement of her relationship with a man.

The second need is conversation. Wives need their husbands to talk to them and to listen to them; they need lots of two-way conversation. In their dating
life prior to marriage, most couples spent time showing each other affection and talking. This shouldn't be dropped after the wedding. When two people get married, each partner has a right to expect the same loving care and attention that prevailed during courtship to continue after the wedding. The
man who takes time to talk to a woman will have an inside track to her heart.

The third need is honesty and openness. A wife needs to trust her husband totally. A sense of security is the common thread woven through all of a woman's five basic needs. If a husband does not keep up honest and open communication with his wife, he undermines her trust and eventually destroys her security. To feel secure, a wife must trust her husband to give her accurate information about his past, the present, and the future. If she can't trust the signals he sends, she has no foundation on which to build a solid
relationship. Instead of adjusting to him, she always feels off balance; instead of growing toward him, she grows away from him.

Financial commitment is a fourth need a wife experiences. She needs enough money to live comfortably: she needs financial support. No matter how successful a career a woman might have, she usually wants her husband to earn enough money to allow her to feel supported and to feel cared for.

The fifth need is family commitment. A wife needs her husband to be a good father and have a family commitment. The vast majority of women who get married have a powerful instinct to create a home and have children. Above all, wives want their husbands to take a leadership role in the family and to commit themselves to the moral and educational development of their children.

Preventing Adultery: His Needs
Now, let's look at the five needs husbands have. The first is sexual fulfillment . The typical wife doesn't understand her husband's deep need for sex anymore than the typical husband understands his wife's deep need for affection. But these two ingredients can work very closely together in a happy, fulfilled marriage. Sex can come naturally and often, if there is enough
affection.

The second need for a man is recreational companionship. He needs her to be his playmate. It is not uncommon for women, when they are single, to join
men in pursuing their interests. They find themselves hunting, fishing, playing football, and watching sports and movies they would never have chosen on
their own.

After marriage wives often try to interest their husbands in activities more to their own liking. If their attempts fail, they may encourage their husbands
to continue their recreational activities without them. But this option is very dangerous to a marriage, because men place surprising importance on having
their wives as recreational companions. Among the five basic male needs, spending recreational time with his wife is second only to sex for the typical husband.

A husband's third need is an attractive spouse. A man needs a wife who looks good to him. Dr. Harley states that in sexual relationships most men find it nearly impossible to appreciate a woman for her inner qualities alone--there must be more. A man's need for physical attractiveness in a mate is profound.

The fourth need for a man is domestic support. He needs peace and quiet. So deep is a husband's need for domestic support from his wife that he often
fantasizes about how she will greet him lovingly and pleasantly at the door, about well-behaved children who likewise act glad to see him and welcome him to the comfort of a well-maintained home.

The fantasy continues as his wife urges him to sit down and relax before taking part in a tasty dinner.Later the family goes out for an evening stroll, and he returns to put the children to bed with no hassle or fuss. Then he and his wife relax, talk together, and perhaps watch a little television until they
retire at a reasonable hour to love each other. Wives may chuckle at this scenario, but this vision is quite common in the fantasy lives of many men. The male need for his wife to "take care of things"--especially him--is widespread, persistent, and deep.

The fifth need is admiration. He needs her to be proud of him. Wives need to learn how to express the admiration they already feel for their husbands
instead of pressuring them to greater achievements. Honest admiration is a great motivator for men. When a woman tells a man she thinks he's wonderful, that inspires him to achieve more. He sees himself capable
of handling new responsibilities and perfecting skills far above those of his present level.

If any of a spouse's five basic needs go unmet, that person becomes vulnerable to the temptation of an affair. Therefore, the best way to prevent adultery is to meet the needs of your spouse.

MMSC Settings

Got these from http://blog.nowsms.com/2008/06/mobile-operator-mmsc-settings.html:

[Philippines - Globe]
APN=mms.globe.com.ph
WAPGateway=192.40.100.20
MMSURL=http://192.40.100.22:10021/mmsc

[Philippines - Smart]
APN=mms
WAPGateway=10.102.61.46
MMSURL=http://10.102.61.238:8002/

[Philippines - SUN / Digitell]
APN=mms
WAPGateway=202.138.159.78
MMSURL=http://mmscenter.suncellular.com.ph/

Thinkvantage Password Manager and Firefox 3

Woohoo! I found the solution here!

1. Find tvtpwm_moz_xpi.xpi in Client Security Solution install directory (C:\Program Files\Lenovo\Client Security Solution)
2. Open it using your fav zip program (I used 7-Zip)
3. Change install.rdf and update 2.0+ to 3.0+
4. Open chrome/tvtpwm.jar
   
5. Edit content/tvtpwm.js and replace window.addEventListener("load", onLoad, true); with following:
   window.addEventListener("load", function () { gBrowser.addEventListener("load", onLoad, true); }, false);
   
6. Now install tvtpwm_moz_xpi.xpi. You can do this by opening "Add-ons" window (Tools/Add-ons menu) and drag-and-drop xpi file in it.

Preventive Car Maintenance (DIY)

Things To Check and Do Weekly


Things to check:

Radiator coolant. Check the plastic overflow bottle, attached by a hose to the radiator. if the level is below the Full mark, add water and antifreeze in equal amounts. If the level keeps dropping, check for leaks.

Oil Level. park the car on level ground wait a few minutes. Then pull out and wipe the dipstick and reinsert it. If the level is below the Add line, add enough oil to bring the level to Full mark. Don't overfill.

Things to do:

Keep the body clean. Dirt and pollutants are tough on paint. Wash the car every week, using enough water to avoid scratching the paint. Hose out the fender wells and undercarriage with a strong spray to dislodge accumulated dirt and road salt, which can cause corrosion. When water beads on the body panels become larger than a quarter, wax the body to protect the paint from tree sap, bird droppings and other damaging dirt.

Things to Check Monthly

Things to check:

Tire pressure. Check with an accurate gauge when the tires are cold. (Don't trust the air-pump gauges at service stations, as they tend to be very inaccurate.) See your car owner's manual or label in the glove compartment or on the driver's doorpost for recommended pressures. And don't forget the spare.

Automatic transmission fluid. Check the fluid level and color, using the transmission dipstick (See the owner's manual for details.) If the level is low, add fluid to between the Add and Full marks. If the fluid is brown or black or smells burnt, change the fluid and filter. If the fluid is sickly white or pale, the transmission oil-cooler may need repair.

Power steering fluid. check the level with the dipstick (usually attached to the fluid reservoir cap). If the level is low, top it up and have the system checked for leaks.

Brake fluid. Check the level in the master cylinder. (Wipe the cap clean before removing it.) If the fluid level is low, top it up and have the system checked for leaks.

Battery. On a low-maintenance battery, pry off the covers or unscrew the caps. If necessary, add distilled water. If the battery has an "eye", check its color. If the eye is green or blue, the battery is OK. If it's black, have the battery tested and charged. If it's pale or yellow, replace the battery.

Things To Check and Do at Each Oil Change


Things to check:

Tires. Check for cuts and bulges, signs of an imminent blowout. Don't worry about fine surface cracks.

Differential fluid. On rear-wheel-drive cars, remove the plug in the differential and feel for fluid with your little finger. If you don't feel any, add fluid until you can and have the system checked.

Air filter. Remove the air-filter cover and hold the filter up to the sun or a bright light. If you don't see light through the element, replace the filter.

Cooling-system hoses. At each oil change, squeeze the hoses when the engine is cold. Replace the hoses if you find cracks or hard or mushy areas. Then start the engine and watch the hoses. Replace them if they bulge while the engine is running.

source: motorzone.com.my


Car Breakdown Prevention

Breakdowns Can Be Very Annoying

Don't let a highway breakdown ruin your trip to balik kampung. Be prepared, to help increase your chances of arriving on time and safe and sound. Preventive maintenance is a must. A well maintained vehicle is more enjoyable to drive, will last longer, and should command a higher resale price.


Read your owner's manual and follow the manufacturer's recommended service schedules.

Engine Performance -- Get engine driveability problems (hard starts, rough idling, stalling, diminished power, etc.) corrected at a good repair shop. Replace dirty filters (air, fuel, PCV, etc.).

Fuel -- Note that a gas tank that is kept filled helps keep moisture from forming.

Oil -- Change your oil and oil filter as specified in your manual -- more often if your driving is mostly stop-and-go or consists of frequent short trips.

Cooling System -- The cooling system should be flushed and refilled as recommended. The level, condition, and concentration of the coolant should be checked periodically. Never remove the radiator cap until the engine has thoroughly cooled! The tightness and condition of drive belts, clamps, and hoses should be checked by a pro.

Windshield Wipers -- Replace old blades. U know lah here in malaysia it rains lembu and gajah even worse than raining cats and dogs..


Battery -- The only accurate way to detect a weak battery is with professional equipment. Routine care: Scrape away corrosion from posts and cable connections; clean all surfaces; re-tighten all connections. Avoid contact with corrosive deposits and battery acid. Wear eye protection and rubber gloves. If battery caps are removable, check fluid level monthly. Note that removal of cables can cause damage or loss of data/codes on some newer vehicles, so be sure to check your manual.

Lights -- Inspect all lights and bulbs; replace burned out bulbs; periodically clean road grime from all lenses. To prevent scratching, never use a dry rag.

Exhaust System -- Your vehicle should be placed on a lift and the exhaust system examined for leaks. The trunk and floorboards should be inspected for small holes. Exhaust fumes can be deadly.

Tires -- Examine tires for remaining tread life, uneven wearing, and cupping; check the sidewalls for cuts and nicks. Check tire pressure once a month. Let the tires "cool down" before checking the pressure. Rotate as recommended. Don't forget your spare, and be sure the jack is in good condition.


Emergencies -- Carry gloves, boots, blankets, flares, a small shovel, sand or kitty litter, tire chains, a flashlight, and a cell phone. Put a few "high-energy" snacks in your glove box.
Yeah keep some ''safety rubber glove'' too , u never know when u might need it..

Windows XP BSOD

I've had enough!!!

This particular workstation cannot stay up for 3 full days!! :-(

When it had only 512MB RAM, I timed it and it couldn't last more than 18hours. Now that it has 2GB RAM, it can stay up for more than 48 hours. I haven't timed it on 2GB RAM yet.

I am pretty sure that this problem is related to VIATXT's SUPERLOAD software. This software was written on VS.net. I know this for a fact, because I've already set up dozens of computers with the same configuration, and without this SUPERLOAD program, they do not suffer crashes like this one does. AFAIK, these crashes occur EVEN AFTER SuperLoad has closed.

Here's the manifest.txt contents:

Server=watson.microsoft.com
UI LCID=1033
Flags=1696082
Brand=WINDOWS
TitleName=Microsoft Windows
DigPidRegPath=HKLM\Software\Microsoft\Windows NT\CurrentVersion\DigitalProductId
ErrorText=A log of this error has been created.
HeaderText=The system has recovered from a serious error.
Stage2URL=
Stage2URL=/dw/bluetwo.asp?BCCode=f4&BCP1=00000003&BCP2=8A1864E0&BCP3=8A186654&BCP4=80604528&OSVer=5_1_2600&SP=2_0&Product=256_1
DataFiles=C:\DOCUME~1\xxxxx\LOCALS~1\Temp\WER8d0c.dir00\Mini061308-01.dmp|C:\DOCUME~1\xxxxx\LOCALS~1\Temp\WER8d0c.dir00\sysdata.xml
ErrorSubPath=blue
DirectoryDelete=C:\DOCUME~1\xxxxx\LOCALS~1\Temp\WER8d0c.dir00

Screenshot:

Getvat new look

Yes! I managed to do it in less than an hour:

I improved getvat2.php to display additional fields at the top of the page: METER #, CONSUMER CLASS, ADDRESS and removed the first bill's details, since they will be included in the table below anyway.

The table now has no more SYSLOSS, AGRA, TRAM VAT amounts, and has followed Gina's Report format:

BILL/READING					UNIV. CHARGES		VAT Amount
Month	Bill No.	Rdg. Date	Reading	KWH	ME	EC	Gen.	Trans.	Dist.	Bill Amt.
2008-02	2716258	2008-02-20	3261	0	0.00	0.00	0.00	0.00	0.30	2.81
2008-03	2771587	2008-03-21	3261	0	0.00	0.00	0.00	0.00	0.30	2.81
2008-04	2836147	2008-04-21	3261	0	0.00	0.00	0.00	0.00	0.30	2.81
2008-02	2898884	2008-02-20	3317	56	2.09	0.14	16.69	8.19	0.00	258.13
2008-03	2898885	2008-03-21	3373	56	2.09	0.14	18.40	8.90	0.00	247.60
2008-04	2898886	2008-04-21	3429	56	2.09	0.14	18.09	8.45	0.00	242.33
2008-05	2898887	2008-05-21	38	56	2.09	0.14	21.24	5.68	0.00	239.09

Cool, 'no? :-)

Illegal Connections

Finally, I was able to modify the Ledger and Teller modules to FLASH THE STATUS of the consumer when it's current status is "ILLEGAL", whether or not it is ACTIVE or INACTIVE.

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.

Toyota Casa bad feedback

from http://tsikot.yehey.com/forums/showthread.php?p=1046236#post1046236

Originally Posted by jaspi11

eto naman yung detalye ng 20k check up ko last march 26;

1.inspect valve clearance ( tiananong ko kung paano nila ini-inspect, di na daw at self adjustment daw yun)
2. inspect drive belt (parang di ko nakitang ininspect)
3. replace engine oil (nakita ko naman nilagyan ng 3 liters)
4. engine oil filter (pinalitan talaga)
5. inspect engine collant (dinagdagan lang ng tubig yung reserve)
6. inspect exhaust pipe and mounting (parang di ko nakitang ininspect)
7. replace sparkplug (meron namang recovery ng luma nilagay dun sa flooring ko)
8. inpsected battery( eto ang merong kwento mamaya)
9. inspected air filter (ang sabi ng SA papalitan daw pero nung tingnan hinanginan lang)
10. inspect PCV valve ventilation hoses & connection (di ko din nakitang ininspect)
11. inspect brake pedal and parking brakes (ininspect naman)
12. inspect clean and adjust break (oks naman nakita ko)
13. inspect break pad and disk (oks din)
14. inspect brake and clutch fuids (oks din)
15. power steering fuids (oks din)
16. inspect ball joint and dust cover (parang di ko din nakitang ininspect)
17 inspect steering wheel and linkage (basta parang more on brakes and change oil lang ginawa)
18. inspect transaxel and transmission oil (di nga sinilip eh)
19. inpsect front/rear wheel suspension (paano kaya ininspect)
20. inspect bolt and nuts on chassis & body (ang pinihit lag eh yung pang drain ng oil)
21. inpsect tire and inflation presure (oks naman na check)
22. inspect light horn washer road test( di din nagawa)
23. inspect aircon (parang di din nagawa)

tgmo (SL 20W50) 415.89
oil filter 657.02
spark plug 491.28
gasket 23.82
sand paper 18.00
brake cleaner 199.37
misc 122.45
----------
2,952.83
vat 354.34
----------
total 3,307.17

sa totoo lang wala pong malasakit ang toyota mkt sa customer basta maperform nila yung change oil kahit ang daming nakalagay sa job order/specs nila, tapos na, di lahat ginagawa.yung sa may battery ko nga meron palang umido di nila ginalaw at ng itawag ko kay toti meruena yung SA dun di daw kasali yung paglilinis ng batterykaya nga ako nagpa preventive maintenance eh..ituturo pa ako sa labas na lang daw ipalinis kundi ba naman talagang nasa loob na ako ng casa ituturo pa ako sa labas...kaya sa susunod na check up ko sa iba na lang casa.....yung carpet replacement ko di ko na din kukunin yun.... kasi iiwan yung car doon for 1 day...wala na akong tiwala sa toyota mkt...bibili na lang ako kay MARFIN nung leather carpet na kinabit nya worth 2k lang naman.

Linksys Gigabit switch down

This morning, Rose complained that she couldn't check her email.

I thought it was merely a proxy problem, but changing the proxy settings didn't matter.

Then Gina said she couldn't load up BCMS program.

While I was checking the server with a lot of "what if" scenarios on my mind, Mike announced that his network connection is "UNPLUGGED."

That's when I checked the switch, and voila, 3 of the 4 groups of 4 ports each were down.

Good thing I haven't installed the old Cnet 100Mbps switch at LH sub-office yet. I transferred all our network cables to this old unit so that Dinah can have the Linksys replaced, since it has warranty coverage up to Sept. 2008. Whew.

I'll be requesting for a spare unit ASAP, though.

Payment Editor

I snapped. Heh.

It was about time I grew tired of all the editing I have to do with all the mistakes in posting payments. Every time Agnes calls me to adjust batches of O.R. numbers, I have had to:

1. Load up the SQL front end.
2. Open my database of commands.
3. Open the commands table.
4. Select the second-to-last record for the template.
5. Copy the template's starting O.R.# to the clipboard.
6. Ask Agnes what the starting O.R.# to edit is.
7. Search and replace.
8. Copy the template's ending O.R.# to the clipboard.
9. Ask Agnes what the ending O.R.# to edit is.
10. Search and replace.
11. Copy the template's date of payment to the clipboard.
12. Ask Agnes what the posting date to edit is.
13. Search and replace.
14. Ask Agnes what to edit (teller_id, payment_date, O.R.#'s)
15. If O.R.#'s, switch to the command prompt or load one up.
16. Calculate the difference between correct and wrong starting O.R.#'s.
17. Search and replace.
18. Execute appropriate commands.

Now, with editpay.bat, I have compressed these steps into:

1. Editpay , , ,
2. Execute appropriate commands.

Kewl!!!

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...

Uploading meter reading data

Last week, I modified the MR Uploading module to include a check for the area's existence before the uploading proper started. In my blog entry, I prayed that I didn't introduce a bug "this time" but, guess what... (altogether now...) I DID!!! :-(

My latest quick fix failed to take Power Bills into consideration. Since Power Bills were read not by area but by reading day, the uploading process stops dead on its tracks at the area check.

I fixed it immediately.

Dvorak keyboard layout

There's been a sudden surge of the Dvorak keyboard layout in the plug mailing list lately, and it piqued my curiosity.

Now I switch to Dvorak when I can take time typing. Like now.

I'm still far from my qwerty speed though.

New thinkpad joins domain

I got my new company-issued Lenovo Thinkpad X61 7676A14 last Friday! Woot!

It's pre-loaded with "Vuisit-a" Vista Business, even though the sticker on the keyboard says Vista Basic. Nice, huh. Another pleasant surprise is that it's a Centrino Pro (I thought it was Centrino Duo only, not knowing of the existence of the Pro)! Centrino Pro is targeted at businesses. It's essentially the new Centrino Duo platform with an extra Intel Active Management chip. What this chip does is allow remote management and enhanced security features, even without an operating system.

Today I got the first chance to make it a domain member, but I kept getting "incorrect password" errors, despite disabling the usual "requiresign" etc... features. So I googled and found, among other links, this link.


- Click Start
- Click Control Panel
- Click System and Maintenance
- Click Administrative Tools
- Double-Click Local Security Policy
- In the left pane, click the triangle next to Local Policy
- In the left pane, click Security Options
- In the right pane near the bottom, double-click "Network security: LAN
manager authentication level"
- Click the drop-down box, and click "Send LM & NTLM - use NTLMv2 session
security if negotiated"
- Click OK
- Restart the computer


But since I was already in gpedit.msc (my usual approach), I looked for it in Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> Security Options -> Network Security: LAN Manager authentication level, and selected the same setting as above excerpt.

Voila! Instant access to the network! Next project is to replace our wireless access points with 802.11N... Woot!!!

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.