Viewing all the Customers.

    Well, I "borrowed" from Julie again and used her routine as a base. Problem is, the customers are all listed and then you can select one of them to view. Why is that a problem. Because of my insistence on smaller windows. Now I will either have to make the selection window bigger OR cause the selection to open a JavaScript window, which means I am going to have to coordinate Forms, JavaScript and PHP again. Hmmmmm... I'll get this figured out yet... maybe.

    Wellll... no, it isn't that bad. The menu selection doesn't pass anything. Just opens the viewing window. It is the viewing window that passes data and that is the master_id. So, since I am using the modify window to display the selections all is ok. Neat thing this modularity.

Customers PC

    The following is from our original design concepts of this Customer DataBase:

   create table computer (
   -> id int not null primary key auto_increment,
   -> master_id int not null,
   -> date_entered datetime, 
   -> date_updated datetime, 
   -> manufacturer varchar (25), 
   -> model varchar (20),
   -> processor varchar (10),
   -> mem_type varchar (10),
   -> mem_amnt varchar (10),
   -> OS_name varchar (15),
   -> CD_type varchar (10),
   -> DVD_type varchar (10),
   -> HD_size varchar (10),
   -> Video_type varchar (20),
   -> Serial_Number varchar (25),
   -> System_Number varchar (20),
   -> notes text
   -> );

Now then, we need some kind of a screen form layout and tie in with the other stuff. AND a way to select it from the customers record. Hmmmm... didn't think about that, did we?? Well, let's see what we have to modify or change in order to do this.

    Since we are creating, we should keep in mind the calls and solutions. These will all be more buttons at the bottom of the Mod/View/Del screen. The customer and not the PC is the central point for all this. However, might want some kind of trail thru the problems and solutions. It would help for reference in other problem cases. Just a thought ... for now. Ok, let's layout the Computer Info screen... which will also contain links to their peripheral info.

    Alllrighty now. We have an initial layout. As I look at it, I'm thinking that some of those fields need to be bigger but, we shall see. If they do, then we get to use the alter DB statements and write some code that will update/change our DB massively. Hmmmmmm... that sounds daunting. But I've done it before. At any rate, whilst writing this paragraph I just thought about the peripherals. Now then, we will want 'hooks' to them from here. Think I'll change the title from 'Computer Info' to 'System Info' with buttons that can venture off to the other items... like a Monitor, Printer, Scanner ... and ???? we'll leave some space for more.

The Peripherals

    Ok. Even though we have laid out the DB prior to this ... and created it, there may be some changes. All depends on how much detail we want to keep on these systems. Actually, the more the better since we are servicing them. That way, before we go on a call, we can pull up the customer and review their system info. Like the Scouts, being prepared. The following is a copy of our DB table:

   create table perif_equip (
   -> id int not null primary key auto_increment,
   -> master_id int not null,
   -> date_entered datetime,
   -> date_updated datetime,
   -> equip_type varchar (25),
   -> manufacturer varchar (25),
   -> model varchar (20),
   -> notes text
   -> );

Now then ... do we have enough info fields for all our different types of periphs?? Well, let's find out. Hmmmmm... now we gota think. There are only four fields:

• What it is
• Who made it
• What model
• And Notes

Is there anything else that we would want???

Monitor

    Well, in looking at this I got to thinking that we might want to know more about the video. Like is it a card or is it integrated? If a customer is complaining about the display we probably should have enough info to tell them if it can be better. So, I am increasing the space in the video section of the System Screen to allow for more details on it... and in the DB table. Now then, when we only want to change the size of an item in the DB, so we have to first drop it and then re-add it or can we change the size via an alter???

    alter table computer modify column Video_type varchar(40);

Ok. Is that enough now?? I mean, what else do you need to know that couldn't be included in the notes section?? Nothing else that I can think of at the moment.

Printer

    Now with the printer we can have nightmares. They are all different and trying to put special fields for describing them will be next to impossible. They are the most non-standard standard item in our computer systems. So we will leave just the 4 fields for them.

Scanner

    Here again, I'm not sure what else could be said.

Modem, Router, Hub

    I grouped these together cause they also fall into the what else could be said category. So on that note, let's create our peripheral form.

Peripheral Item
Equipment:
Manufacturer:	Model:
Notes:

    Ok, I think that will do. Now we must go back to the System form and add buttons to call each of these forms, if needed. Oooops. These buttons will NOT be part of the System form but just on the same page.

    Buttons, buttons everywhere and not the correct one to be found!!

    Well, it is a fine mess that I have gotten us into this time. Problem is I didn't think out this System Info and Peripheral Info additions well enough. Upon 'initial' entry of each customer, we won't have a master_id until the customer has been placed in our DB. Thennnn... we can start adding items and keying them to the master_id. BUT, this is done thru the Mod/Update/Del Customer screen. We will need a way of selecting 'New' or 'Update' for the System Info.

    Now then, as we can remember, there is an individual id for each item in addition to the master_id. We have already used this in the ModifyCust.php. So, how we handle the SysInfo will be dependent on whether or not it has an id. Simplifies things a little, also. Only have to have one form and routine, so to speak.

Allllrighty Now.

    We have a system where we can save customer info, their PC info and make modifications. Now all we have to do is straighten out this peripheral mess. We already have a form for them. Problem is, when I was thinking on the fly, I think I was flyin'. We have a 'General' form. The type of peripheral is one of the data fields on the form. So, how do we pick which one?? Wellllll, how do we select customers? Go read them all, or selectively, and put up a selection list. Well, can't we do a similar thing for the Peripherals?? Sure we can, and we will. Just need to re-think this and try to do it correctly this time.

    Nope. Need to re-think the SysInfo too. Updates are becoming Adds. So, we make one file for Entering and Modifying SysInfo. The determination as to whether it is an entry or an update is made in the DB update routine. However, I just noticed that I was not smart enough on that one. It only allows for the customer to have one PC. I have customers with more than one. So changes will have to be made.

    Alllrighty now. We will have an 'Add' and a 'Select' button for the SysInfo and for the peripheral equipment. Both will pass flags as to what is happening. Boy you gota be careful on this. I just spent a couple of hours wrestling with a problem. I had developed a scheme to Add or Update the peripherals first. I was adopting these ideas into the SysInfo procedures. Unfortunately, I couldn't get it to work. It always came up in Update mode. Finally the Lord opened my eyes and I saw that I was using a single '=' instead of a double '==' in a comparison for the Operation. So, I was always setting it for Update!! Bummer...

    Now we need to add the System ID to the peripheral info and modify everything to use it properly. (Don't you just love this design as you go??) Reason for this?? Well, think about it. Normally, if you have more than one system you will probably have peripherals for each. At least each will have their own monitor. Unless the customer is computer savy, or they had someone set it up, they probably won't have a sophisticated system with a router, printer server and such. So, we want to tack the peripherals onto the appropriate tower.

    First thing to change is the DB table for the peripherals. We need to add a SYS_id column.

    alter table perif_equip add SYS_id int;

With this change being made we can then modify the HTML and PHP code to handle it. Which will be in MosSysInfo and ModPeriph. Hmmmmm... me thinks there is another. Yep. UpdPeriph2DB. Ok I placed the changes in the files. Now then, I am going to dump all the data in the DB that we have entered thus far. Changes ... and errors ... have made it ... well unsatisfactory. Now, we can do this via mysql or phpadmin. Think I am gona play with phpadmin some more.

Cleaning Up the DB

    

• First we open a New Navigator Window.
• Then we call up our phpadmin - http://dusty-tr.trcomputing/phpMyAdmin-2.6.1-rc1/
• In the first window select TRcustomers DB.
• For each table: TRUNCATE TABLE tablename;
• Hmmm... once again GUI is not as 'convient' as you would think. The next part was accomplished in mysql... textual command line type.
• For each table: alter table tablename auto_increment=1;

Note: I added a little check, in TRDB_menu, to see if MySQL is running. Got tired of bringing things up and entering data only to find that the DB was down. The following was what I added:

$link = @mysql_connect($db_host, $db_user, $db_pass);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
printf("MySQL server version: %s\\n\", mysql_get_server_info());
mysql_close($link);

Some checks ... and fixes/changes

    After clearing the DB I naturally had to re-enter some data. Well, during the entries, I found that I still had an error and that the layout was not good for the customer screen. I was ussing an invalid variable to display the stree address in AddCust2DB. In addition it looked funny to have the 'notes' section in between the street and city. So, those got moved. Three files affected, EnterNewCust, AddCust2DB and ModifyCust.

    Welllll... this is why you do testing. Updates are not replacing but adding new records!! Hmmmm... thought that I had that working at one time. Probably did when I wasn't trying to have one file for two operations. I/program is confused...!

    Well, it was me. I was checking to see if the master_id matched but I forgot to include it in the selects!! Minor mistake.

Customer Call Portion

    Alllrighty now. With the mistakes fixed and corrections made for some other 'minor' items we are finally getting into the service calls. This has been a long road. What we need to determine is just what we want to record and how we will be indexing it. This will not only be for recording the individual instances but also saving the info for future reference. Now what did we lay out originally and does it still hold water???

create table call_reason (
-> id int not null primary key auto_increment,
-> master_id int not null,
-> date_entered datetime,
-> date_updated datetime,
-> pending enum ('Yes', 'No'),
-> call_date datetime,
-> note text,
-> reason varchar (65),    <---- I am adding this!!
-> SYS_id int              <---- I am adding this!!
-> );

create table solution (
-> id int not null primary key auto_increment,
-> master_id int not null,
-> date_entered datetime,
-> date_updated datetime,
-> free_pay enum ('Free', 'Pay'),
-> service_date datetime,
-> note text,
-> call_id int             <---- I am adding this!!
-> );

The above two tables are what we had speculated in the beginning. I believe that there is one item missing in the call_reason table..... the reason. I want a text box that will list the reason so that we can index it similar to the peripherals and then select the call info based on the reason. We will still have the notes text box for further information enhancements. Lets start with a 65 char description. Now then we also want to know which system, so we add the Sys ID.

    Now we move on to the solution. All we nee to add to this one is the call_id. There really should only be one solution to each problem.

    Alllrighty now. Let's design the call screen. We need another form, naturally. So, let's create one. Right off we will need a name, id and onrest=\"window.close()\". Then for the fields we will need: Call date, a Reason Line, two radio butts for pending or not and finally an area for notes. Hmmmmm... Why does the Call and the solution have to be two different tables?? I'm beginning to think that I am making to much separation here. Let's put the solution on the same page. So this will be a change in our database. We'll just drop the two tables and create a new one... that looks like this:

create table service_calls (
-> id int not null primary key auto_increment,
-> master_id int not null,
-> date_entered datetime,
-> date_updated datetime,
-> reason varchar (65),
-> pending enum ('Yes', 'No'),
-> tutor_repair ('Tutor', 'Repair'),
-> free_pay enum ('Free', 'Pay'),
-> call_date datetime,
-> service_date datetime,
-> notes text,
-> SYS_id int
-> );

Ok now, we are allowing 65 characters for a call reason. That should be enough for a description of most of the calls we recieve, including tutoring calls. This should be right at the top of the form.

Service Call Information
Call Type: Tutor Repair	Call Reason:
Call Pending: Yes No	Call Date: Time:
Money Type: Free Pay	Service Date: Time:
Service Notes:

    Ok. That gives us the general layout. Now one of the biggest headaches is to figure out how to handle the dates. We don't just enter them any old way cause we may at some time want to sort the calls by call date or service date. Date sorts work best if the year is first, followed by the month and then the day. However, we humans usually enter the date as month, day and year. So we can do it the human way and then translate it to the computer way. This is done with concatination when storing and substrings when extracting.

    Time. The time. Do we want to have AM and PM?? Sure, why not? Looks fancier that way. Just need to figure a proper page layout. All of this has caused changes in what we had. Hmmmmm... me thinks that we need to create another internal table and properly arrange the date and time.

Done! ??? maybe

    If you have suffered through all this and are still interested you can find the sources in my Products/Downloadable area...which is listed in the sidebar. All the basic information is there and the code can be modified for personal or business use. However, you are not allowed to sell it.

Closing Comments

    We have went through a lot of time and trouble to create a database, a relational one. This database will operate accross the Network and was designed with that in mind. However, the thought that nagged me from the very beginning was speed. Unfortunately, it is a problem ... if you are connected to the Internet and running your database from a WebSite. It can be, and is ... slow. Running the software on my local system is acceptable. But on the Internet it is really just to slow. My ISP has an 'Export' function but I couldn't find an 'Import' function. However, bouncing back and forth between the two systems could get, and is, dangerous. They would be out of sync to many times and determining which one was correct could be a problem.

    So I will just use my little database at my shop and leave the one on the internet for an example.

Parts, Labor and Costs

    Well, on entering the very first of my pervios customer calls I ran into a situation. The Lord pointed out to me that I had not allowed for Parts and Labor. I mean, if we are going to have info about a service call you need to know what was needed and what was provided. I started to just add some fields to the Service Call form and then realized that I was going to have to add this info to the DB also.

    Alllrighty... now just how do we add this to the DB?? As a separate table or as part of another? Hmmmmm... There really isn't another table that will fit the bill...heh, heh. At any rate I believe that another table is in order. Now there are really only two items needed for each part. Cost and description. However, most parts have part number. We don't have an inventory ...yet, so we don't need much... but as expansion takes place we may need more. So, we need to design this for current needs and future.

    Normally the first column is quantity, then usually cost and then finally the description. However, as mentioned above there is usually a part number. Now, do we want just our part number, or the manufactures part number, or both?? I vote for both. Now then, in doing so we will need to research a little on the size of these part numbers.

    Well, the first check was with HP and guess what?? They had their parts listed as quantity, part number, price and description. Their part numbers, at least the ones I saw, are 4 numbers a dash and 4 more numbers. so we have a total of 9 characters. Ooops, further looking showed 4 numbs dash 4 numbs dash and 3 letters. So, we have a new total of 13 characters. This is just for one model, mine. So, I am going to make the Manufactures Part numbers an allowable 15 characters. That should cover most. For mine I am going to have two letters, TR, followed by a dash, 3 numbers and then a dash and then 3 more numbers for a total of 10.

    I built this table using phpAdmin so I don't have the construction, only the results.

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     |      | PRI | NULL    | auto_increment |
| master_id    | int(11)     |      |     | -1      |                |
| date_entered | datetime    | YES  |     | NULL    |                |
| date_updated | datetime    | YES  |     | NULL    |                |
| quantity     | varchar(5)  | YES  |     | NULL    |                |
| TRpartnum    | varchar(10) | YES  |     | NULL    |                |
| Mftrpartnum  | varchar(15) | YES  |     | NULL    |                |
| description  | varchar(65) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

    Well, I found an neat way of creating a dynamic table to display the parts in but I am not sure how to make a variable entry. I think the way to do it will be to display the table of parts used, if any, and then have an entry line or call.

    Ok now, as usual I haven't kept up with my notes. So I have a lot to try to remember and then put down here. That is what I am going to do before I do anything else. I pretty much have the Customer Data Base finished except for fine tuning. Well, and also a means of printing out reports,if so desired. But for the most part it is finished. I am using it now. We will make a demo version for use on the site. So my room on the site doesn't get consumed, I will make modes to the code that will limit the number of entries.

    Allllrighty now. One thing that was overlooked during this design was "User Friendliness". Shame on me. I am a big advocator of that. What pointed that out to me was, when I was making the entries myself and made some mistakes. Like, we have relationships that are based on the record id's of the items. This record id doesn't get created until the item is placed in the DB. So, there has always got to be an initial entry and then updates.

The Entry Flow:

1. Enter/Add a New Customer: (Creates master_id)
      No other entries from this point.
   .
2. Modify/Delete Customer: (Requires master_id)
      Customer is selected by Full or Partial Last name.
   .
3. Pick From List: (Requies Full or Partial Last Name)
      The list of Customers with the above pattern will be presented.
   .
4. Modify Customer: (Requires master_id)
      Essentially this looks like the New Customer screen.
      Main difference is the System Buttons. Add/Update Sys Info.
      The first modification will, naturally, be to add Sys Info.
   .
5. Add/Upd Sys Info: (Creates SYS_id, Requires master_id)
      Must put in the initial system info and then save it so we can get a system id.
      Add/Upd Peripheral and New/Upd Calls will not work till after first save.
      Reason: Need Sys ID in both Peripherals and Calls.
   .
6. Add/Upd Peripheral: (Creates PRF_id, Requiers master_id, SYS_id)
      The only difference between Add and Update is that one comes up blank
      and the other with info.
      No other selections from here.
   .
7. New/Upd Service Calls: (Creates CALL_id, Requires master_id, SYS_id)
      There are two main differences between Add and Update.
      1. Add is initially blank and update has data.
      2. In Add, you can not Add/Upd Parts. Must have a call Id.
          Which is created when?? On Service Call record save.
   .
8. Add/Upd Parts: (Creates PU_id, Requires master_id, SYS_id, CALL_id)
      Called from Service Call Screen.
      You can add as many parts as needed. However, only 3 at a time are listed
      on the Service Call screen.

    Now as you can see by the above, Sys Info can not be entered untill we have a master_id. Peripheral info can not be entered untill we have a Sys_id and... well, I'm sure you get the picture by now. So, controls were put in that will not allow items to be added or updated unless the proper IDs are present. Also put in indicators of unsaved data. Only minor problem here is with the selects. Seems all I have to do is click on those and it sets the changed flag. They will need further investigation.

    JavaScript was used to create most of the above user friendliness.

    

???

sub???