Monday, 25 June 2018

Some Programming Stuff

When time permits and I'm in one of my agreeable moods, I usually write a piece for my technical blog called Through The SQL. That blog is all about software programming and a few bits of troubleshooting stuff. Anyway, here's a post that I wrote for that blog sometime ago with its original content.
In Visual Foxpro 9 (VFP) and it’s lower versions, there exists a logical field with which a programmer can use to have some kind of a digital flip-type switch that we regularly use in queries or in a control, like a checkbox, for example. If one can imagine how a light switch works, the idea behind is the same with that of a logical field. The apparent difference emerging from the point being that is, the usual ON and OFF status that we regularly see on a common household light switch is omitted, the logical field in VFP uses a boolean value of either .T. or .F. instead, with “.T.” representing the value for TRUE and “.F.” for FALSE.

So it’s kind of like when your dad asks you if you have left the garage lights on overnight, you may probably hear, “Is it TRUE that you have left the lights on last night?”

Anyway, Creating a logical field in a VFP cursor is as simple as this.

Create Cursor curTest (field1 l)

Select curTest

But in MS SQL Server, with versions like 2008 or 2012, there is no Boolean or Logical field type. This is probably the reason why I see many software developers turning their trust to characters and integer types just to work around this inadequacy. 

For instance, a programmer can use several of the INT (integers) type field just to represent a value of 0 or 1. Because 0 and 1 can both respectively represent the ON and OFF nature of a basic switch. The idea makes sense until you add another value, like “2” then it loses the foremost characteristic of being a “flip-type” switch. 

The Workarounds in SQL Server

I’ve seen programmers use the int type, which has a range of -2^31 (-2,147,483,648) to  2^31-1 (2,147,483,647) and can take up storage of 4 Bytes in the table. Others use the smallint type, of which range is shorter than the int type but takes up 2 Bytes of storage space. There is also the tinyint that has a range of only 0 to 255 and takes up one Byte of storage space.

One thing to consider when you are feeling a bit determined to use these INT types for a simple switch is that each column of int created in a table will take up the storage space that I mentioned above. For example, if you have two columns of a tinyint field, each column will respectively take up one Byte of storage in a table.

At the other end of the spectrum, there are also those who use a character field to represent a switch in a similar fashion. These programmers, like I do in the early stages of my programming career, have liked the idea of using CHAR(1) type field to mimic the purpose of a logical field. The reason being is that it’s a straightforward solution and it’s easy to read. All that one needs to do is update the char field with a string having a value of “T” or an “F” character. 

The downside of using a char(1) field, however, is that it can take up other characters as well like “A” for instance, aside from the “T” or “F” characters. Also, each char(1) type column’s value in a table will take up 1 Byte of storage. Therefore, if you have two of this column in your table, the values will take up 2 Bytes and so on.

What I Think Is the Right Way to Do It

The best thing to do, in my humble opinion, is to use a bit type field in MS SQL Server. This type accepts integer data type like a bit value of 1 or 0, and it can have a NULL value as well.  

In this example, I’ve created a temporary table with a bit type field. I then inserted a 1 value.

If (Select name from sys.tables where name like '%#testTable%'is not null
            drop table #testTable;

            create table #testTable (field1 bit);
            insert into #testTable (field1values (1);
            select field1 from #testTable;     

The advantage of the bit type is that it can automatically convert the string values TRUE and FALSE to bit values.  The “TRUE” becomes 1 and “FALSE” becomes 0.

If (Select name from sys.tables where name like '%#testTable%'is not null
            drop table #testTable;

            create table #testTable (field1 bit);
            insert into #testTable (field1values ('TRUE');
            select field1 from #testTable;     

Sending Updates from Visual Foxpro

Luckily, MS SQL Server will convert the boolean value sent from Visual Foxpro by way of SQL-Pass Through or (SPT) connection.  

For this example, I have a table that I use for keeping LEAVE PAYS. It has a bit type column named isLeapYear. Let’s see what’s in it.

Notice that all other values in the isLeapYear column has zero values except for what’s in the fourth row, which has a bit value of 1.

Now, let’s try and change that into 0 (zero) by sending an UPDATE from Visual Foxpro with this command.

=SQLExec(oHandle.nHandle,'Update MyDatabase.dbo.MyTAble Set isLeapYear = .F.')

Now, we can see that all of the values in the isLeapYear column became zeros. 

Showing the Results with a Checkbox Control in a Grid

Let’s change the value of the fifth column, in the first row to 1. Then we’ll pull the records from MS SQL Server to a Visual Foxpro Grid that has a checkbox column.

=SQLExec(oHandle.nHandle,'Update MyDatabase.dbo.MyTAble Set isLeapYear = .F. where lpID = 9')

 So, this is how it should look like now. 

After we pulled the records from SQL Server, this is what a grid with a checkbox control would look like. Notice that the checkbox is automatically ticked because the underlying value in that column is .T. while the rest have .F. values. I will discuss how I created this checkbox in a grid  on a new post once I get a free time again.

Final Thoughts

Visual Foxpro is somehow lucky to have found its Logical values automatically converted by MS SQL Server to bit values. 

But another point worth considering for using a bit type field is that the SQL Server Database Engine optimizes storage of bit columns.  For example, if a table has less than 8 bit columns, all of the columns are stored as 1 byte. Having more than 8 bit columns but less than 16 will be stored as 2 bytes and so on.

As opposed to using an int type that always stores 1 byte or more per column, this is a great way to conserve server storage space if one is too concern about data normalization and storage conservation. 

Have a nice day!

Friday, 11 May 2018

A Bout of Chicken Pox

Time heals everything, even time itself.

I heard a speaker who once said that if you wanted to know the value of each second, ask someone who had a close call with accident. I never had the chance to ask one. But I might have a luck in asking a random husband that I see sitting next to a women’s clothing shop in the mall. He probably knows the true value of every second he spends to get by the time waiting for her wife to return from shopping.

This is what my scar-ridden face looked like in the aftermath of a Chicken Pox attack. The picture is a bit over-exposed so much of the scars aren’t visible but if you look closely, there are lots of dark spots around my temples and cheeks. The person behind me is Prabin, our new Nepali manager. We hitched a ride on the back of a small truck on the way home.

It’s a good thing that we only get infected by Varicella Zoster virus a.k.a. Chicken Pox once in our life time. No one likes to have troublesome scars from time to time. But according to the doctors, even after a successful bout with the virus, not all of which are eradicated from our body. Some of the surviving viruses can hide in our body and avoid the detection of the immune cells. Later on in life, when our immune system gets weaker due to old age, they can travel back to the skin surface and inflict on us a slightly altered version of skin condition called shingles.

Setting what the palindrome was saying aside; these days, I am following a not-so-strict exercise routine. I usually exercise on every other day. The physical regimen involves riding a stationary bike for 30 minutes, followed by doing three sets of different dumbbell exercises. I finish the routine by working on the abs roller. 

This is the second time that I have peeled off a medium-sized pineapple. The first was when I was nine years old. I don’t like eating pineapple that much not because peeling its spikey skin off is a bit challenging, but because I didn’t like the taste and texture.  

Here’s a fake tattoo that I drew on my left arm using a small brush dipped in an India ink. I find it difficult to draw on the human skin as opposed to drawing on paper. Unlike the sedentary paper, human skin is elastic, it responds to room temperature and has contours that differ from person to person. Someday, I’m thinking of retiring as a tattoo artist. But given my age right now, I would be too old by then. 

That begs a question, who would want to have their skin tattooed on by an old man with shaky hands anyway? Not me sir.

Here’s an egg recipe that I invented. I like to use the word “invented” because it feels like I have done something so relevant in the history of mankind. I didn’t have a name for it yet but the basic recipes are: oil, garlic, onion, salt, potato and egg. 

Here’s a Mechado that I cooked. This and Ginisang Munggo are the two recipes that I always like to cook every now and then. The reason being that is so I can whipped out a dish of this or two at any given time without having to ask Google for the recipe.

These days, I’m eating a bit hefty to recover the weights that I lost during the Chicken Pox bout. Remember that when you get infected by this virus, there is nothing that you can do much. You are in the mercy of your own immune system. You can increase its efficacy by eating healthy foods and drinking lots of fluids, but the flu and fever that come along with the infection will keep you, most of the time in bed, and make you feel weaker that you’ll hardly feel hungry at all.

But luckily, time is your best friend. Your body will heal in time. The total period of recovery is just around a week although some may take it up to two weeks.

That’s all for me this weekend. Bye!

Wednesday, 9 May 2018

Attendance Program and CinchPro Payroll System

In 2013, the management in the company that I am working with bought a couple of attendance biometric machines from overseas. It didn't have an attendance software to go with it and it would be a waste if we couldn't use it.So what I did was that I asked the company to give me a whole fortnight (14 days) to program an attendance software that can automate our existing manual time sheet calculations.

This is the software that I came up with. It gets the data from what the biometric machine yields and could do rapid calculations of the total time incurred as well as figure out late penalties. The note on the leftmost side at the bottom says it's already in its 4th revision.Since 2013, the reason why it's very easy and fast to generate time sheet reports has largely been due to this. We didn't have to calculate time from the time cards manually.

This is just one of those biometric machines, and it remarkably held in time. For five years, the company that I'm working with is still using these machines together with the attendance software that I have created for them.

This is the Payroll System that I developed from scratch. In the early years leading up to its creation, I had an intermittent opportunity to help our accounting office with payroll works for quite some time. Those good times had helped me earned a considerable amount of knowledge in payroll stuffs, which then led me to the creation of this latest software, the CinchPro Payroll System in 2017.

The CinchPro Payroll System works in tandem with my Attendance Program. With so many things going on in the payroll, one could easily make a mistake if it's done in Excel. Also, the repetitive tasks can be tedious. By using CinchPro, one can easily generate reports on the ply.

The modules in the CinchPro Payroll Systems include:

  • Staff Recording
  • Unique Position Monitoring
  • Money Booking
  • Bread or Item Booking
  • Uniform Deductions
  • Loans
  • Other Deductions
  • Taxes
  • Entitlements such as Leave Pay, Finished Pay and Long Service
  • Monitoring absences through Medical Certificates 
  • Generation of Pay Slips and other reports
  • Payroll for Factory workers that gets paid by pieces of work.
  • Generating Excel file counterparts for sending payroll to BSP or ANZ bank for processing.

Friday, 4 May 2018

A Short Work in Daru Island

Daru Island reminds me of the old Palayan City that I used to live when I was a very much younger version of me. From the familiar unpaved roads and to the homochromous vegetable stalls that lined up the sidewalk, the whole scenery is like a blue pill for the mind. One can think about ingesting that pill as a wonder drug that can instantly bring forth a recollection of experiences in the past, and the most apparent effect is a blissful feeling of having been transported back to the time when almost everyone else is living in simplicity.

Back when the words mobile phones and tablets do not exist in the dictionaries.

But in Daru Island, the unpaved roads and small makeshift houses do not equate to being held back in time. While most people there don’t own a mobile phone, a number of residents and visitors from the mainland that come from all walks of life do carry a communicating device with them. 

While I’m happily expecting a kind of communicating device of local ingenuity, the advancement in technology in Daru is catching up to the latest trend. Gone is the era when empty tin cans hooked up to a short distance by a piece of string is used as a phone. The influx of overseas items there has brought people to get closer to using touchscreen phones or tablets.

This is the room that was assigned to me for the duration of my stay. It’s a bit spacious and has its own bath and toilet.  I have a window that is facing towards the beachfront. In the night, I always have enough time to admire the beauty of the seas, but during such time, everything outside is engulfed in total darkness.

Here’s the old shop that our company is replacing with a much bigger shop within the compound. At that time, it’s still operating.  Part of my job there is to install the network that new POS machines will use.

This is kuya Levi and he was showing me things around the old shop. One can buy almost anything from there.  At one point in time, I even saw one customer buying a set of wheel bearing from our shop. I didn’t know we’re selling bearings.

Now, that was nice. 

The cashiers in the old shop are all using cash registers to record sales. In the new building that we were setting up, all of these cash registers would be replaced by POS machines.

Here’s the new building for the new shop. The guy wearing a red hat is Sirigi and the guy in the black hat is Kelly, and what they were working on at the time was grinding holes underneath the POS counters to make way for the network and power cables. I took the measurements beforehand and drew some rectangle patterns at the bottom of the counters so Sirigi could easily cut them out.

Buried within the floors are networks of cables that go out to these golden ports.  Once the counters are placed over them, they will cover up these ports and it will defeat the whole purpose so my idea was to cut out holes at the bottom.

Cutting out a hole at the bottom wasn’t an easy task because the metal sheets are a hundred times thicker than an average paper. It required us to use a grinder and a pair of sturdy hands. In this case, Sirigi has that pair.

While Sirigi was doing all the relentless cutting, I assigned Kelly to help me with the installation of conduits on the walls through which external cables would go.  

Hey look! I think that was Levy trying to show Kelly where to drill the tucks into. 

The new shop features new type of shelves and has sufficient number of ceiling fans to cool the place down. I was also tasked of installing speakers and CCTV cameras on the ceiling.

My strategy at the time being was to work on what’s important the most. Our POS supplier, Harsya, had only 2 weeks to set his POS up. He’d be needing all the help that he could if he wanted his job done on time. We agreed about letting him work on his POS while I did the cabling. 

I’m a network enthusiast and even though I seldom do network cabling installations, I have network tools that I buy just so I can have one to use. For example, I have bought this LAN tester as a gift to myself when I had an extra money. This is a bit pricey compared to the other types because this model can also test for continuity in coaxial cables.

Kelly is the kind of person that you can depend on at the workplace. If you showed him once how things are done right, he’d catch up easily. 

Installing CCTV cameras in Daru is quite an enjoyable thing to do,  it doesn’t require much effort if you already know what you’re doing. Actually, I only remember drawing something like a floor plan on a piece of paper and using a little math for calculating the length of cables. 

Just kidding. Installing CCTV cameras require a certain degree of effort. First, you need to know what type of CCTV System suits the place well. Next is to estimate the amount of cables to use based on the camera locations if you prefer not to use a wireless system. Another thing to consider is how you’re going to power up the cameras. For Daru branch, I chose to use the 18-port 12-volt power box that could supply all 16 cameras at once. 

Here’s the power box that I’m discussing about. It’s straightforward and easy to install. Each port has female sockets to which there are two clips that the wires can be plugged into.

The only thing that you need to watch out for is the strictness in polarity.

I took care of soldering the BNC connectors to the cables. How do I know these things? In 2008, I took up short courses in practical electronics and automotive electronics at TESDA in the Philippines in two different occasions. 

Here’s what our cameras looked like after the installation. Pretty neat huh?

For outdoor cameras, we used pvc pipes and chemical pipes as conduits to hold our cables in place. 

The black box on the top left is a 24-channel public address system. I’m testing its sound quality with two speakers. For some reason, there are some people who prefer to place the speakers under a series connection. But I digress, because speakers are better off in parallel connection.

I showed Kelly how to affix the speakers to the ceilings and as expected, he picked up quickly. Back in the old days, we used to make perfect circles in elementary school using a cheap compass. It's pretty surprising that even after more than two decades, the way we make perfect circles didn't change that much.

My conclusion is that it is much easier and faster to install a public address sound system than the CCTV systems.

Here’s Alejandro, a Filipino manager in the shop, trying out the quality of the sound system after the installation. He seems happy enough after the initial testing.

And here’s Harysa (on the left) and Levi (on the right) giving thumbs up after the successful installation of Airpos POS machines on the counters. 

That’s all for this week. Until next time.

Sunday, 25 March 2018

My Future Dream and a Partial Review of Eheim 2010 Canister Filter

Note to Readers: In the dusty pile of unposted blog entries in the forgotten premises of this Blog's Draft section, I stumbled upon this post that I wrote in the 7th of December 2014. So on this day, this post is more than three years already. But as what people always say, it's never too late for everything. So anyway, I'm posting this in its unaltered state to preserve consistency despite its inconsistency with this blog's chronological order.

After three years of working here in Papua New Guinea, I have been spending some time tinkering about an idea of doing something useful once I go back to Philippines for good.

I have several plans in mind but they are yet to pass through their malleable stage. I believe that for a plan to work out in the future, it has to be well carefully thought of and must be as hard as a concrete. For example, aside from planning to run a computer shop in a distant time, I am also considering other options that spark my interest. One of my options is to own a Local Aquarium Shop.

I am not fond of fish in general, but I like the idea of creating your own personal nature scene inside an aquarium, with tropical fishes swimming within graciously. They say that owning a pet alleviates stress but I think it depends upon what kind of pet. For example, I find it stressful to clean up dog droppings but I have no qualm about cleaning an aquarium tank. 

And I also find it very relaxing to look at an aquarium for hours on end. Interestingly, you may occasionally find a fish glaring back at you like you're some kind of food before gradually losing interest of you.

Having an Aquarium Shop that produces own tropical fish is what I have in my mind. But one must have to be fundamentally knowledgeable in the basics of breeding tropical fishes if one wishes to embark on such venture. My particular interest in this field is breeding live-bearers as they are the easiest to breed.  A couple of months ago, I was successful at breeding a wild specie of guppy that I bought from PNG Gardener. Now, I'm up for a bigger challenge of breeding a pair of red swordtails.

Unlike guppies, it's quite difficult to breed swordtails due to their preference on habitat. For instance, they require a breeding space with a minimum of 20 gallon. And because this is only for the sake of learning how to breed them, I purchased a 75 Litter rectangular plastic bin instead of a glass aquarium. 

Although not an eye candy, it has saved me a huge amount of money.

For a fish tank this big, it will require a bigger and powerful filter too. The Eheim 2010 or Eheim 160 was the perfect choice by the availability. I bought it for K175.00 at PNG Gardener. It is both a submersible and a canister-type filter that sucks water from the bottom and spew it up on top leaving the pollutants trapped within the pores in the sponge filter inside.

It runs on 6-Watt power so it doesn't really consume much electricity and a perfect choice for a 24/7 tank filtration. 

Another thing that I like most about Eheim 2010 is that it has a protruding tube on top from which it draws air to generate micro bubbles that goes along with the water movement. Although it appears to be not contributing anything other than being interesting, it actually helps to aerate water and oxygenate aerobic bacteria to help in the fish' byproduct nitrification process.  The pump causes water movements and the bubbles that go on top increase the surface area and improve the oxygen exchange on the water surface.

This is one of the display rack from PNG Gardener at their Gordons branch. As you can see, they have the Eheim models 2006, 2008 and 2010. All of these Eheim products are internal filters. Both 2008 and 2010 have the bubble feature which the 2006 model doesn't have.

The filter in action. You can see the bubbles rushing out from the opening on top. It is always advisable to keep the filter submerged at a quarter of an inch below the water surface to give the whole tank a good water circulation.

Here's what my plastic tank looks like from another perspective. The source of light is from a 2-Watt fluorescent lamp attached to a modified overhead reading lamp. It's a weak source of light, to be exact, but I'm guessing that the impurities in the water and the tank itself being translucent are both reflective of lights thus making the whole bin glow in the dark.

This tank is  currently holding 19 gallons of water, a couple of rocks to weigh down artificial plants and a pair of red swordtail fish.

If I'm successful at breeding this particular specie, then I'm considering it as a good sign that I should continue with my future plans. 

Tuesday, 27 February 2018

Improving Things at Daru Island

If there is one thing a reluctant flyer is not so excited about going through is to arrive at the destination in the blanket of a passionate night. But a true traveller is unfazed by such a miniscule inconvenience, and the inherent confusion bought about by the darkened unfamiliar place is a welcome challenge.

I arrived at Daru a couple of hours earlier before the sun set down to the East. Interestingly, there are only few cars traveling down the road at any given time. So mostly, it is silent. You can actually hear the neighbours casually chatting to each other if you stay put and say nothing.

Now, isn’t that an ideal place to live in?

This is the house where the managers live in the company that I work with. In the Philippines, we used to have this joke about a house that may look like it’s ready to go any moment: “Isang anay na lang ang ‘di pumipirma, magigiba na ‘yan.”

So in this case, the house looks like it’s about to go down and is just patiently waiting for that single termite to sign it off.

That night, we ate in this old but surprisingly sturdy house. The managers took turns cooking and that was the whole setup. It was this guy’s turn to cook at that time and they said he cooked a sea worm. 

I don’t know anything about a sea worm, but it does taste good.

The next night, it was Levi’s turn to cook. He whipped out some tin fish with chopped veggies for us. Although I’m all set for cooking, I’m not so familiar yet with all kinds of manner of things in this kitchen. I was told  to just watch and do nothing. Well, that seems to be an easy thing to do except that I still did something and took this photo.

While Levi is preoccupied with his tin fish recipe, another manager shoved a real fish into a hot and steamy pot. This in turn makes me wonder, why bother with tin fish when there are plenty of fish around to catch in Daru Island? 

I guess the answer is that, a tin fish recipe is not a tin fish recipe without the canned fish.

The next day, everyone was up early. I climbed up the new building and had a look at the old accommodation where I had my dinner for two nights. The contractor had informed us the day before that they would begin dismantling this house while everyone was out working. 

By the time the noon came, the house looked like this. That was strange, I thought. Because I saw only two men there.  Unless one is the Flash, and the other is Quicksilver, someone without an intermediate skill in carpentry would have wondered profoundly how these two gentlemen have pulled that off. And while it's an obvious work of human origin, my child-like imagination was suggesting a slightly different theory, thinking that it's a bizarre case of tornado attack right up the alley. If that was really the case, I have got to see that for myself.

My inquisitive instinct has got the better of me and I came back later that afternoon only to feel a little frustrated that my theory about a rogue tornado was not proven true at all. It turns out that life, indeed, has full of surprises. Who said that four men could not demolish an entire building in only a few hours?

I think no one has said that before.

When I look at this photo, all I can think about was the Doozers from The Muppet Show. The Doozers are the tiny characters in Fraggle Rock that love to work and build structures. We used to watch that show when I was younger.  I would always wonder why the bigger puppets in that show love to bite on and destroy the things that the tinier Doozers built.

The Doozers.

After a couple of days, what’s left of it are just the posts and metal beams. This helped me conclude that humans not only evolved to effectively build things but had also become efficient in destroying things.

And while some primates, like Chimpanzees, are known to use tools like sticks to hunt ants for food, only humans are endowed with brains that can think of using complex machines to help bring down things when our primitive hands have met their limits. 

This was the room where I stayed in Daru. It didn’t have much things in between, but the bed was enough for a tired IT Manager.  Anyway, I was hoping to finish my work assignment in a month or so. There were lots of things to be done and I could always use a good night sleep.

Until next time.

Related Posts Plugin for WordPress, Blogger...