|
|
|
Now it's time to implement the various functions needed to access the database. In this problem you will use the TQuery in conjunctions with SQL to access data. Before writing the functions, you should review the scenarios and scripts since they will determine how the queries are created. In the customer window, there are three buttons and a window double click action that will require database queries. The "Confirm", "Find By Name", "Add Customer" and "Get Info" buttons each have different queries. A double click on an item in the information window could cause one of two different queries to happen.
First list the results of each of these user actions.
|
|
|
|
|
Most of the queries for the Customer window are based on those described in weeks 1 , 2 , and 3 . The queries are fairly simple with the exception of the "Find By Name" and the "Find Info" buttons.
The "Confirm" button is very similar to most of the simple quires in this program. If the customer data has changed, you want the database to be updated. The data member _modified is set to true whenever a field changes. One handler is used to handle changes in any of the boxes. This handler also calls enablers various buttons. The variable _modify is set to false in the GetInfo hander (which is also called on certain double clickes in the information box.
void __fastcall TCustomerForm1::CityChange(TObject *Sender)
{
FirstCheckBox->Enabled = FirstName->Text != "";
ZipCheckBox->Enabled = ZipCode->Text.Pos(" ") ==0;
StreetCheckBox->Enabled = StreetAddress->Text != "";
PhoneCheckBox->Enabled = Phone->Text.Pos(" ")==0;
LastCheckBox->Enabled = LastName->Text != "";
CityCheckBox->Enabled = City->Text != "";
StateCheckBox->Enabled = State->Text.Length()==2;
_modified = true ;
enableAdd();
enableConfirm();
enableFindByName();
}
If _modified is true, a query that selects the customer with a given id is created and Openned. The cursor is set to the first item in the Query and the Query is set to Edit mode. Notice that you must have the RequestLive property of the query set to true.
void __fastcall TCustomerForm1::ConfirmBtnClick(TObject *Sender)
{
if (_modified)
{
CustomerQuery->Close();
CustomerQuery->SQL->Clear();
CustomerQuery->SQL->Add("Select * from Customers where customerId like "+ CustomerId->Text);
CustomerQuery->Open();
CustomerQuery->First();
CustomerQuery->Edit();
Customer c(LastName->Text,ZipCode->Text,FirstName->Text,
StreetAddress->Text,City->Text, State->Text,Phone->Text);
c.place(CustomerQuery);
CustomerQuery->Post();
}
MainForm->RentalForm()->confirmed();
MainForm->RentalForm()->CustomerId->Text= CustomerId->Text;
}
"SELECT * FROM Customers WHERE postalCode like " + ZipCode->Text " AND lastName like " + LastName->TextBorland would not execute this request (see commented out code below), so I substituted a query by zip code and then sorted out those records with the same last name when inserting the descriptions into the SummaryListBox.
void __fastcall TCustomerForm1::FindNameClick(TObject *Sender)
{
CustomerId->Text= "" ; //find the customer, want click on Info to get that customer.
CustomerQuery->Close();
CustomerQuery->SQL->Clear();
CustomerQuery->SQL->Add( "Select * from Customers WHERE postalCode like " +ZipCode->Text );
// The following should separate by last name but doesn't. This is fixed in the loop below.
//CustomerQuery->SQL->Add("WHERE postalCode like " + ZipCode->Text);
//CustomerQuery->SQL->Add("AND lastName like " + LastName->Text);
CustomerQuery->Open();
CustomerQuery->First();
SummaryListBox->Clear();
while (!CustomerQuery->Eof)
{
Customer c;
c.retrieve(CustomerQuery);
if (c.lastName()== LastName->Text) //fake the commented query above
SummaryListBox->Items->Append(c.description());
CustomerQuery->Next();
}
}
An alternative to creating the query in the code is to create the query in the object inspector. The customer query can have the following inserted as its SQL, right in the object inspector.
SELECT * FROM Customers WHERE postalCode = :postalCode AND lastName =:lastNameIn this case the :postalCode and :lastName are parameters that are set at runtime. The handler now does not clear the SQL, but sets the parameters instead.
void __fastcall TCustomerForm1::FindNameClick(TObject *Sender)
{
CustomerId->Text=""; //find the customer, want click on Info to get that customer.
CustomerQuery->Close();
CustomerQuery->Prepare(); //Prepare query to receive parameters
//Set the parameters
CustomerQuery->Params->Items[0]->AsString=ZipCode->Text;
CustomerQuery->Params->Items[1]->AsString=LastName->Text;
CustomerQuery->Open();
CustomerQuery->First();
SummaryListBox->Clear();
while (!CustomerQuery->Eof)
{
Customer c;
c.retrieve(CustomerQuery);
SummaryListBox->Items->Append(c.description());
CustomerQuery->Next();
}
}
This form of the query performs perfectly.
A double click on the summary box is a context sensitive action. If the summary box is showing lists of customers, double click should retrieve a customer's information and display it in the appropriate places in the CustomerForm. On the other hand if the summary box has a list of customer rentals, double click must send a message to the RentalForm to display the information in that form.
Customers are listed in the summary box when you have the customers last name and zip code but not the customer id. In this case the handler for the Find by Name button sets the customer id to "", so you know in the case of a double click with empty customer id you are in the click on customer state. The only time customer rentals are displayed is when you have found the actual customer (and their id) so the id is not the empty string.
void __fastcall TCustomerForm1::SummaryListBoxDblClick(TObject *Sender)
{
int where = SummaryListBox->ItemIndex;
if (where >=0 && where < SummaryListBox->Items->Count - 1)
{
String s= SummaryListBox->Items->Strings[where];
s = s.SubString(0,s.Pos(" ")-1);
if (CustomerId->Text == "")
//find the customer and get the information
{
CustomerId->Text = s;
GetInfoClick(Sender);
}
else // get the rental record and fill in fields of rental dialog
MainForm->RentalForm()->setOrder(s,Sender);
}
}
Next week you will learn how to implement the handlers that use an abstract factory to create rentals from a database..