<<

. 77
( 132 .)



>>

}

fetch_customer()
This function works very much like fetch_staff(), except that there™s no need to
determine whether the argument is a string or a numeric value. Because we are
building the query based on named parameters from the $params argument, we can
look explicitly for the column values we need. So if we™ve been given a customer ID
value, we use that. If not, we check for a customer code string, and if we have one,
use that. If we don™t have values for either of these columns, then we can™t run the
query, and we just error out of the function. DB lets us supply the column name
we™re using as a parameter by using the ! token character in its place in the query.
Here™s the full function.

function fetch_customer(&$params)
{
$query = ˜select * from customers where ! = ?™;
Chapter 13: Problem-Tracking System 455

if (!empty($params[˜customer_id™]))
{
$bind = array(˜customer_id™, (int)$params[˜customer_id™]);
}
elseif (!empty($params[˜customer_code™]))
{
$bind = array(˜customer_code™, $params[˜customer_code™]);
}
else
{
user_error(
˜Could not fetch customer - no ID or code specified™
, E_USER_ERROR
);
return FALSE;
}
$dbh = db_connect();
$record = $dbh->getRow($query, $bind, DB_FETCHMODE_ASSOC);
if ($record === FALSE)
{
$private_error = ˜fetch_customer: error in query: ˜
. $dbh->last_query
;
user_error(
˜Could not fetch customer from database™
, E_USER_ERROR
);
return FALSE;
}
$params = array_merge($params, $record);
return TRUE;
}

fetch_problem()
The difference between fetch_problem() and fetch_customer() is at the end of
the function. When you get a problem from the database, you want more than the
problem record itself. You want information about the customer who has the prob-
lem, and about the history of our work on the problem to date. So in addition to the
usual query against the problems table, fetch_problem() also runs the
fetch_customer() and fetch_history() functions. Here™s the code:

function fetch_problem(&$params)
{
$query = ˜select * from problems where ! = ?™;
if (!empty($params[˜problem_id™]))
456 Part IV: Not So Simple Applications

{
$bind = array(˜problem_id™, $params[˜problem_id™]);
}
elseif (!empty($params[˜problem_code™]))
{
$bind = array(˜problem_code™, $params[˜problem_code™]);
}
else
{
user_error(
˜Could not fetch problem: no ID or code specified™
, E_USER_ERROR
);
return FALSE;
}

$dbh = db_connect();

$record = $dbh->getRow($query, $bind, DB_FETCHMODE_ASSOC);
if (!$record)
{
$private_error = ˜fetch_problem: error with query: ˜
. $dbh->last_query
;
user_error(
˜Could not fetch problem from database™
, E_USER_ERROR
);
return FALSE;
}
$params = array_merge($params, $record);
if (empty($params[˜source™]) && !empty($params[˜source_id™]))
{
$params[˜source™] = source($params[˜source_id™]);
}

if (!fetch_customer($params) or !fetch_history($params))
{
return FALSE;
}

return TRUE;
}
Chapter 13: Problem-Tracking System 457

find_customer()
Remember that you would like to enable users to report their problems over the
Web. In this application, we™ve decided that while a numeric primary key exists for
each user, the application should be able to identify the user by either a phone
number or an email address. So when a user enters information, you will need to
check if someone with an identical email address or phone number has come along.

function find_customer($email=””
,$day_area=™™,$day_prefix=™™,$day_suffix=™™
,$eve_area=™™,$eve_prefix=™™,$eve_suffix=™™
)
{
$wheres = array();
$bind = array();
if ($day_prefix != ˜™)
{
// there must be a prefix for this to be a valid phone
number
$wheres[] = ˜(day_area like ? and day_prefix like ? and
day_suffix like ?)™;
$bind[] = $day_area;
$bind[] = $day_prefix;
$bind[] = $day_suffix;
}
if ($eve_prefix != ˜™)
{
// there must be a prefix for this to be a valid phone
number
$wheres[] = ˜(eve_area like ? and eve_prefix like ? and
eve_suffix like ?)™;
$bind[] = $eve_area;
$bind[] = $eve_prefix;
$bind[] = $eve_suffix;
}
if ($email != ˜™)
{
$wheres[] = ˜(email like ?)™;
$bind[] = $email;
}
if (count($wheres) == 0)
{
// nothing to look for
user_error(
˜find_customer: no wheres supplied™
458 Part IV: Not So Simple Applications

, E_USER_NOTICE
);
return FALSE;
}

// run a query with the constructed qualification
// and return the result.
// separate each part of the qualification with OR -
// any part constitutes a valid match.
$query = ˜select * from customers where ˜
. implode(˜ or ˜, $wheres)
. ˜ order by customer_id ˜
;
$results = db_connect()->getAll($query, $bind,
DB_FETCHMODE_ASSOC);
return $results;
}

With this function you will know if the user has an existing record that can be
used or that might need to be updated. Figure 13-6 shows the form for updating
customer data.




Figure 13-6: Form for updating customer information
Chapter 13: Problem-Tracking System 459


If you are interested, you can set a cookie to make identifying the user a bit
easier.




history_entry()
When a staff member enters an update on a problem, the step is stored in the his-
tory table. If the entry is public the user will be informed of the update by email; if
not, no email will be sent.

function history_entry($problem_id=NULL
, $entry_type_id=NULL
, $entered_by=NULL
, $source=NULL
, $notes=NULL
)
{
if (empty($problem_id))
{
user_error(˜Error: no problem ID for history entry™,
E_USER_ERROR);
return FALSE;
}

if (empty($entered_by)) { $entered_by = ˜customer™; }

$entry_type = entry_type($entry_type_id);
$source_id = source_id($source);

// create a record in the history table

$dbh = db_connect();
$entry_id = $dbh->nextId(˜history™);
$query = ˜insert into history

(entry_id,problem_id,entry_type_id,entered_by,source_id,notes)
values (?,?,?,?,?,?)
˜;
$bind = array($entry_id,$problem_id,$entry_type_id,$entered_by
,$source_id,$notes
);
$result = $dbh->query($query,$bind);
if (!$result)
460 Part IV: Not So Simple Applications

{
$private_error = ˜error: could not create history entry: ˜
.™<li>query=™.$query
.™<li>result=™.var_export($result,TRUE)
.™<li>last_query=™.$dbh->last_query
;
user_error(˜Error: could not create history entry™,
E_USER_ERROR);
return FALSE;
}

// update the problem record
$query = ˜update problems set last_entry_id=? ˜;
$bind = array($entry_id);
if ($entry_type == ˜public™)
{
$query .= ˜, last_public_entry_id=? ˜;
$bind[] = $entry_id;
}
$query .= ˜ where problem_id = ? ˜;
$bind[] = $problem_id;
$dbh->query($query,$bind);

// get the email address of the customer who opened this call
// if this was a public history entry, and if the email address
// is not empty
if ($entry_type == ˜public™)
{
$query = ˜select c.email, p.problem_code from problems p,
customers c
where p.problem_id = ? and p.customer_id = c.customer_id
and trim(ifnull(c.email,””)) <> “”
˜;
$email = NULL;
list($email,$problem_code) = $dbh->getRow(
$query
, array($problem_id)
, DB_FETCHMODE_ORDERED
);

<<

. 77
( 132 .)



>>