Take On Customers

Home
Objectives
Requirements
VCU Policies
Email
Enterprises
Grades
Naming Conventions
The Assignment
Chart of Accounts
Quiz #1
Debits & Credits
Sample Accounting
Structure
Warnier Usage
Take On Customers
Project #1
Database
Table Maint Prototype
Make it Portable
Common Problems
Project #2
PB Prototype
Linux Prototypes
MAPI?
ANSI X.12
pseudo X12
Displaying JVs
Display JV VB.NET
CAT --> Text
Email CAT PB
Email CAT VB.NET
Sample Quiz 2
DataWindowChild
Project #3
Ledger Engine
Quiz #3
Table Of Contents

'Taking On' records from a 'flat file' or other non-database source is an ordinary event for programmers. Records show up on a tape or in some file and they need a script to be massaged into the database.

There's a need to avoid creating Duplicate Records when lists are taken on, so checking to see if a record in the file is already present in the database is an important step.

'Checking for Dupes' can be a difficult process in the real world unless a key or other unique identifier is transmitted along with the name & address data.

In this case, there's an Id provided by the Source (Website is a fine Source to use) of these records that can be used to determine whether a record just read from the Flat File is already present on your enterprise's database and may need to be updated, or whether it's new and needs to be inserted into the database.

Here is 20030916CustomerList.tab, which contains some Records to be Taken On into your application's database as entries in the 'BusinessEntity' or 'Users' table.

These records are in a 'Tab Delimited Text' file, where the fields are separated by an ASCII Character 9 and the records are separated by a 'standard Carriage Return & Line Feed'.

The Warnier diagrams below assume no Key Field in the file and suggest a method for pulling 'fixed width' fields from the records. They need to be adjusted to use the Key Field and tab-delimited fields in the file.

The Fields are:

	Id - Alphanumeric, Unique key for the record in the ClassWebsite database
	First Name - Character, may be left empty for a Company or other User with no FirstName - max length 20
	Last Name - Character, required field, max length 45
	Extra Line - Character, may be needed to direct correspondence beyond the Street Address - max length 45
	Street Address - Character, holds the number and street or 'Post Office Box' and # for a Post Office Box - max length 45
	Post Office - Character, City & State for a US Post Office, State abbreviation follows a comma - max length 35
	Zip Code - Character - US Zip Code for the record.

Errata:

Occasionally the output routine will write an Empty Record. Any record that is less than 5 characters long should be considered empty.

A later version, or versions, of the Customers file will be provided to exercise your application's functions for taking on new records that might be in a later version, and updating Customers' records where the name and/or address changes.

It's each student's responsibility to design & write the script that will take on records from the file that's provided on the page for Project #1. It can be placed on a button on the Main Panel for project #1. For later projects it should be available for the Manager's/Owner's use only.

The process should run without errors and add new records to the Business Entities table. It should not put duplicate records into the database.

It should deliver a count of the number of records read from the file, number of records that were already in the database, and the number of records inserted into the database.

Here are PowerScript statements that will be required, used with the variable names from the Warnier diagram below. Use the on-line documentation to get more details.

	GotFileName = GetFileOpenName("Select a File", FilePath, FileName, "", "Text Files (.TXT), .txt")
	MessageBox("An Error was Encountered", "More specifics about the error")
	CustomerFile = FileOpen(FilePath) Returns an Integer to CustomerFile, -1 is an error
	ReadResult = FileRead(CustomerFile, CustomerRec) Returns an Integer to ReadResult, < 0 is an error
	CONNECT USING SQLCA;
	If SQLCA.SQLCode <> 0 and SQLCA.SQLCode <> -1 then ...
	Do While ReadResult >= 0 ... Loop
	FirstName = Trim(Mid(CustomerRec, 1, 20))
	CommaPos = pos(PostOffice, ",")
	SELECT id INTO :BeId FROM be WHERE ... ;
	FileClose(CustomerFile)
	DISCONNECT;

Here are PHP statements for the Open Source Prototype. Use the PHP Documentation for more details.

	$BlockedFile = fopen($BlockedFileName,"r"); a null $BlockedFile is an error
	if (feof($BlockedFile)) {}
	$DBLink = mysql_connect('localhost',$SVSiteDatabase,$SVDatabaseUser,$SVDatabasePasswd);
	$SiteDB = mysql_select_db($SVSiteDatabase);
	while (!feof($BlockedFile) {}
	$RecordIn = fgets($BlockedFile);
	$FirstName = addslashes(trim(substr($RecordIn,0,20)))
	$SQLStmt = "select Id from Users where LastName='$LastName' and FirstName='$FirstName' and StreetAddress='$StreetAddress';
	$UserResult = mysql_query($SQLStmt) or die("Unable to get User Result using '$SQLQuery'...");
	$TimeStamp = date("YmsHis");
	$SQLStmt = "insert into Users set LastName='$LastName', ...";

Here is a Visual Basic.net sample script and brief discussion of language elements and objects that can be used in scripts to work with sequential files to update a table in a database. Visual Basic.NET uses an EOF flag to detect the end of the file, so it's logic is similar to the PHP example below.

Here's Structured Notation for the scripts to take on customer data from a file that uses 'fixed width' column to separate fields instead of the tab-delimited format of this semesters' data.

Although structured notation is sometimes expected to be 'pure logic' and not 'language dependent' the logic is influenced depending on the language in use. File processing is a good case to consider whether the loops' flavored as Until or While:

PowerScript checks for End of File _after_ a FileRead statement has been executed so the file processing logic needs to include a check after the FileRead to see if a loop should continue.

PHP uses the feof function to identify End of File _before_ an fgets statement reads from the file.

Visual Basic.NET also uses the EOF flag.

Logic structured for one environment might not work in another environment. It's the programmer/analysts job to adapt the program's logic appropriately.

Here's a B-Liner-produced Warnier Diagram for a Take On process, geared toward PowerBuilder. Here's another that reflects PHP's file handling.

You're welcome to use either as a design for your Take On Customers script. Please recognize that the logic in these diagrams will not work for the Use Case requirements in this semester's project where a Customer's record Id from the 'source file' must be used to insert new records into your database or make name & address changes. Take care to accurately diagram your algorithm for Taking On Customers.

Multi-line MessageBoxes probably need a comment for PowerBuilders. The programmer needs to use 'Carriage Return' and 'Line Feed' characters in the string that goes into the box so there will be line breaks where desired. This combination, ASCII Characters 13 & 10, gets used in several places in the prototype application so a constant, CrLf, has been defined in the global variables for the programmer's convenience.

Here's the syntax for the messagebox in the Wrapup section that delivers the counts. It's a long statement so it wraps here. In your script, either key it all in on one long line, or use the & line continuation character if you want to put it on two or more shorter lines:

messagebox("Customer TakeOn Results", string(ReadCounter) + " Records Read" + CrLf + string(AlreadyThereCounter) + " Duplicates Avoided" + CrLf + string(InsertCounter) + " Records Inserted")

[ Back ] [ Home ] [ Next ]

Last modified: Saturday October 18, 2003.