Database Applications: College Park Auto Shop

	Database Applications: College Park Auto-Shop

Introduction

An auto repair application is one that allows an employee to enter information about the company's work orders of fixing cars. In most cases, there are parts used when fixing the cars. Besides these parts, there are some jobs performed on the car. It should be a good idea to list all parts that were used for the repair and all jobs that were performed on the car.

In this example, we will create a database application that allows a car repair shop to register customers orders.

If you haven't done so, install MSDE or Microsoft SQL Server.

Practical Learning: Creating the Application

If you are using MSDE, open the Command Prompt and type osql -E and press Enter
If you are using MS SQL Server, open the SQL Query Analyzer

To create the database used in this application, type the following:

-- ===========================================
-- Database: College Park Auto-Shop
-- ===========================================
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'CPAS')
DROP DATABASE CPAS
GO
CREATE DATABASE CPAS
GO

If you are using MSDE, to execute the statement, press Enter

If you are using MS SQL Server, press F5 and, after the database has been created, save the file as cpas.sql

If you are using MSDE, type the following (since it is long and a mistake on the last line would be a nightmare, you should type the following in Notepad)
If you are using MS SQL Server, on the main menu of SQL Query Analyzer, click File -> New -> Create Table -> Create Table Basic Template -> OK and type the following:

-- =============================================
-- Database: College Park Auto-Shop
-- Table:    Workorders
-- =============================================
USE CPAS
GO
IF EXISTS(SELECT name 
	  FROM 	 sysobjects 
	  WHERE  name = N'Workorders' 
	  AND 	 type = 'U')
    DROP TABLE Workorders
GO

CREATE TABLE Workorders (
WorkorderID int Primary Key Identity(1,1) NOT NULL, 
OrderDate datetime,
OrderTime datetime,
CustomerName varchar(50),
Address varchar(50),
City varchar(30),
State  varchar(30),
ZIPCode varchar(10),
Make varchar(20),
Model varchar(20),
CarYear varchar(10),
ProbDesc text,
PartName1 varchar(50),
UnitPrice1 varchar(10),
Quantity1 varchar(6),
SubTotal1 varchar(10),
PartName2 varchar(50),
UnitPrice2 varchar(10),
Quantity2 varchar(6),
SubTotal2 varchar(10),
PartName3 varchar(50),
UnitPrice3 varchar(10),
Quantity3 varchar(6),
SubTotal3 varchar(10),
PartName4 varchar(50),
UnitPrice4 varchar(10),
Quantity4 varchar(6),
SubTotal4 varchar(10),
JobPerformed1 varchar(50),
JobPrice1 varchar(10),
JobPerformed2 varchar(50),
JobPrice2 varchar(10),
JobPerformed3 varchar(50),
JobPrice3 varchar(10),
JobPerformed4 varchar(50),
JobPrice4 varchar(10),
TotalParts varchar(10),
TotalLabor varchar(10),
TaxRate varchar(10),
TaxAmount varchar(10),
TotalOrder varchar(10),
Recommendations text
)
GO

The Data Source

Before starting the application design, you should create an ODBC Data Source to connect to a SQL Server or MSDE database. Microsoft Windows (all versions) comes with an engine or wizard used to create this data source. Although there are various types of data sources you can use, one of the options provided by Microsoft is called ODBC, which stands for Open Database Connectivity. It is simply a driver or program you use to "translate" a database in a format that the needing programming environments can use.

Practical Learning: Creating an ODBC Data Source

On the taskbar,
If you are using MS Windows Server 2003, click Start -> Programs -> Administrative Tools -> Data Sources (ODBC)
If you are using MS Windows 2000, click Start -> Settings -> Control Panel, double-click Administrative Tools and double-click Data Sources (ODBC)
If you are using MS Windows XP, click Start -> Control Panel, double-click Administrative Tools and double-click Data Sources (ODBC)
In the User DSN property page of the ODBC Data Source Administrator property sheet, click the Add button:
In the Create New Data Source wizard, click SQL Server:
Click Finish
In the Name edit box of the Create A New Data Source To SQL Server wizard, type cpas and press Tab
In the Description edit box, type College Park Auto Shop
Click the arrow of the Server combo box and select the server where the CPAS database resides or select (local):
Click Next
Select the desired authentication. For this example, I accept the Windows NT Authentication. I also accept to connect to SQL Server with Default Settings by leaving the check box untouched:
Click Next
In the new page of the Create A New Data Source To SQL Server wizard, click the Change The Default Database To check box.
Once the combo box is enabled, click its arrow and select CPAS:
For this example, leave the Attach Database FileName check box unchecked and accept the other defaults. Click Next
Unless you have any other reason, accept the default (language, encryption, etc) settings of the new page:
Click Finish
On the ODBC Microsoft SQL Server Setup dialog box, click the Test Data Source button:
When the SQL Server ODBC Data Source Test dialog box confirms that the TESTS COMPLETED SUCCESSFULLY!, click OK:
On the ODBC Microsoft SQL Server Setup dialog box, click OK
In the User DSN property page of the ODBC Data Source Administrator dialog box, make sure that the cpas data store is listed in the User Data Sources list box
Click OK

Application Design

The design of this application is classic: no complication, only a lot of work. We will create a control for each column we added in the SQL statement. There are a few concerns you would have to address. We create one column for an order date and another column for an order time. These can easily be handled by text boxes but data entry can be made user friendly is you use a date picker and a time picker controls. Remember that the lesser typing your users have to perform, the less worries you have. We also created two text boxes that would need multiple lines. You would just have to decide how much room each needs. The first text box allows an employee to describe the problem that the car was brought for. The other text box allows a user or the perform who fixed the car to provide some recommendations to the customer concerning the repair.

Practical Learning: Creating the Application

Start Microsoft Visual Studio or Visual C++
Create a new MFC Application named CPAS
Create it as Single Document
Select Database View Without File Support
If you are using MSVC .NET, set the Client Type to ODBC
Click Data Source
If you are using MSVC .NET, in the Select Data Source dialog box, click Machine Data Source
In the list, select cpas
Click OK (if you are using MSVC .NET, click another OK)
If you are using MSVC .NET, in the Select Database Object dialog box, expand the Tables node followed by dbo
Select Workorders
Click OK

Click Finish
If you are using MSVC .NET, open the CPASSet.cpp source file. Look for a line that start with #error:

}
#error Security Issue: The connection string may contain a password
// The connection string below may contain plain text passwords and/or
// other sensitive information. Please remove the #error after reviewing
// the connection string for any security related issues. You may want to
// store the password in some other form or use a different user authentication.

Read that line and then comment it

Access the source file of the main frame and change it as follows:

int CMainFrame::OnCreate(LPCREATESTRUCT lpCreateStruct) { if (CFrameWnd::OnCreate(lpCreateStruct) == -1) return -1; if (!m_wndToolBar.CreateEx(this, TBSTYLE_FLAT, WS_CHILD | WS_VISIBLE | CBRS_TOP | CBRS_GRIPPER | CBRS_TOOLTIPS | CBRS_FLYBY | CBRS_SIZE_DYNAMIC) || !m_wndToolBar.LoadToolBar(IDR_MAINFRAME)) { TRACE0("Failed to create toolbar\n"); return -1; // fail to create } this->m_wndToolBar.SetWindowText(_T("Standard Toolbar")); if (!m_wndStatusBar.Create(this) || !m_wndStatusBar.SetIndicators(indicators, sizeof(indicators)/sizeof(UINT))) { TRACE0("Failed to create status bar\n"); return -1; // fail to create } // TODO: Delete these three lines if you don't want the toolbar to be dockable m_wndToolBar.EnableDocking(CBRS_ALIGN_ANY); EnableDocking(CBRS_ALIGN_ANY); DockControlBar(&m_wndToolBar); this->CenterWindow(); this->SetWindowText(_T("College Park Auto-Shop - Repair Orders")); return 0; } BOOL CMainFrame::PreCreateWindow(CREATESTRUCT& cs) { if( !CFrameWnd::PreCreateWindow(cs) ) return FALSE; // TODO: Modify the Window class or styles here by modifying // the CREATESTRUCT cs cs.cx = 640; cs.cy = 600; cs.style &= ~FWS_ADDTOTITLE; return TRUE; }

Display the IDD_CPAS_FORM form and design it as follows:

Control	ID	Caption	Other Properties
Group Box		Work Order Identification
Static Text		Order Date:
Date Time Picker	IDC_ORDERDATE
Static Text		Order Time:
Date Time Picker	IDC_ORDERTIME		Format: Time
Static Text		Customer Name:
Edit Control	IDC_CUSTNAME
Static Text		Address:
Edit Control	IDC_ADDRESS
Static Text		City:
Edit Control	IDC_CITY
Static Text		State:
Edit Control	IDC_STATE
Static Text		ZIP Code:
Edit Control	IDC_ZIPCODE
Static Text		Make/Model:
Edit Control	IDC_MAKE
Edit Control	IDC_MODEL
Static Text		Year:
Edit Control	IDC_YEAR		Align Text: Right Number: True
Static Text		Problem Description:
Edit Control	IDC_PROBDESC		Auto HScroll: False Auto VScroll: True Multiline: True Vertical Scroll: True Want Return: True
Group Box		Order Summary
Button	IDC_CALCULATE	Calculate Order
Static Text		Total Parts:
Edit Control	IDC_TOTALPARTS		Align Text: Right
Static Text		Total Labor:
Edit Control	IDC_TOTALLABOR		Align Text: Right
Static Text		Tax Rate:
Static Text		%
Edit Control	IDC_TAXRATE		Align Text: Right
Static Text		Tax Amount:
Edit Control	IDC_TAXAMOUNT		Align Text: Right
Static Text		Order Total:
Edit Control	IDC_ORDERTOTAL		Align Text: Right
Group Box		Parts Used
Static Text		Part Name
Static Text		Unit Price
Static Text		Qty
Static Text		Sub Total
Edit Control	IDC_PARTNAME1
Edit Control	IDC_UNITPRICE1		Align Text: Right
Edit Control	IDC_QUANTITY1		Align Text: Right Number: True
Edit Control	IDC_SUBTOTAL1		Align Text: Right
Edit Control	IDC_PARTNAME2
Edit Control	IDC_UNITPRICE2		Align Text: Right
Edit Control	IDC_QUANTITY2		Align Text: Right Number: True
Edit Control	IDC_SUBTOTAL2		Align Text: Right
Edit Control	IDC_PARTNAME3
Edit Control	IDC_UNITPRICE3		Align Text: Right
Edit Control	IDC_QUANTITY3		Align Text: Right Number: True
Edit Control	IDC_SUBTOTAL3		Align Text: Right
Edit Control	IDC_PARTNAME4
Edit Control	IDC_UNITPRICE4		Align Text: Right
Edit Control	IDC_QUANTITY4		Align Text: Right Number: True
Edit Control	IDC_SUBTOTAL4		Align Text: Right
Group Box		Jobs Performed
Static Text		Price	Align Text: Right
Edit Control	IDC_JOBPERFORMED1
Edit Control	IDC_JOBPRICE1		Align Text: Right
Edit Control	IDC_JOBPERFORMED2
Edit Control	IDC_JOBPRICE2		Align Text: Right
Edit Control	IDC_JOBPERFORMED3
Edit Control	IDC_JOBPRICE3		Align Text: Right
Edit Control	IDC_JOBPERFORMED4
Edit Control	IDC_JOBPRICE4		Align Text: Right
Group Box		Recommendations to Customer
Edit Control	IDC_RECOMMENDATIONS		Auto HScroll: False Auto VScroll: True Multiline: True Vertical Scroll: True Want Return: True

Save all

Data Controls Binding

After designing the application and its controls, you can bind each control to the table's column that corresponds to its data. This is done differently in MSVC 6 and MSVC .NET

Practical Learning: Binding Controls to Data: MSVC 6

If you are using MSVC 6, press Ctrl + W to display the ClassWizard. In the Member Variables property page, double-click each ID and associate with the suggested name. Here are two examples:

Based on this, bind the controls as follows:

Identifier	Member Variable Name	Variable Type
IDC_ADDRESS	m_Address	CString
IDC_CITY	m_City	CString
IDC_CUSTNAME	m_CustomerName	CString
IDC_JOBPERFORMED1	m_JobPerformed1	CString
IDC_JOBPERFORMED2	m_JobPerformed2	CString
IDC_JOBPERFORMED3	m_JobPerformed3	CString
IDC_JOBPERFORMED4	m_JobPerformed4	CString
IDC_JOBPRICE1	m_JobPrice1	CString
IDC_JOBPRICE2	m_JobPrice2	CString
IDC_JOBPRICE3	m_JobPrice3	CString
IDC_JOBPRICE4	m_JobPrice4	CString
IDC_MAKE	m_Make	CString
IDC_MODEL	m_Model	CString
IDC_ORDERDATME	m_OrderDate	CTime
IDC_ORDERTIME	m_OrderTime	CTime
IDC_ORDERTOTAL	m_TotalOrder	CString
IDC_PARTNAME1	m_PartName1	CString
IDC_PARTNAME2	m_PartName2	CString
IDC_PARTNAME3	m_PartName3	CString
IDC_PARTNAME4	m_PartName4	CString
IDC_PROBDESC	m_ProbDesc	CString
IDC_QUANTITY1	m_Quantity1	CString
IDC_QUANTITY2	m_Quantity2	CString
IDC_QUANTITY3	m_Quantity3	CString
IDC_QUANTITY4	m_Quantity4	CString
IDC_RECOMMENDATIONS	m_Recommendations	CString
IDC_STATE	m_State	CString
IDC_SUBTOTAL1	m_SubTotal1	CString
IDC_SUBTOTAL2	m_SubTotal2	CString
IDC_SUBTOTAL3	m_SubTotal3	CString
IDC_SUBTOTAL4	m_SubTotal4	CString
IDC_TAXAMOUNT	m_TaxAmount	CString
IDC_TAXRATE	m_TaxRate	CString
IDC_TOTALLABOR	m_TotalLabor	CString
IDC_TOTALPARTS	m_TotalParts	CString
IDC_UNITPRICE1	m_UnitPrice1	CString
IDC_UNITPRICE2	m_UnitPrice2	CString
IDC_UNITPRICE3	m_UnitPrice3	CString
IDC_UNITPRICE4	m_UnitPrice4	CString
IDC_YEAR	CString	m_CarYear
IDC_ZIPCODE	CString	m_ZIPCode

Access the CPASView.cpp source file and make the following changes:

// CPASView.cpp : implementation of the CCPASView class
//

#include "stdafx.h"
#include "CPAS.h"

#include "CPASSet.h"
#include "CPASDoc.h"
#include "CPASView.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CCPASView

IMPLEMENT_DYNCREATE(CCPASView, CRecordView)

BEGIN_MESSAGE_MAP(CCPASView, CRecordView)
	//{{AFX_MSG_MAP(CCPASView)
	//}}AFX_MSG_MAP
	// Standard printing commands
	ON_COMMAND(ID_FILE_PRINT, CRecordView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_DIRECT, CRecordView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_PREVIEW, CRecordView::OnFilePrintPreview)
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CCPASView construction/destruction

CCPASView::CCPASView()
	: CRecordView(CCPASView::IDD)
{
	//{{AFX_DATA_INIT(CCPASView)
	m_pSet = NULL;
	//}}AFX_DATA_INIT
}

CCPASView::~CCPASView()
{
}

void CCPASView::DoDataExchange(CDataExchange* pDX)
{
	CRecordView::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CCPASView)
	DDX_FieldText(pDX, IDC_ADDRESS, m_pSet->m_Address, m_pSet);
	DDX_FieldText(pDX, IDC_CITY, m_pSet->m_City, m_pSet);
	DDX_FieldText(pDX, IDC_CUSTNAME, m_pSet->m_CustomerName, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPERFORMED1, m_pSet->m_JobPerformed1, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPERFORMED2, m_pSet->m_JobPerformed2, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPERFORMED3, m_pSet->m_JobPerformed3, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPERFORMED4, m_pSet->m_JobPerformed4, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPRICE1, m_pSet->m_JobPrice1, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPRICE2, m_pSet->m_JobPrice2, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPRICE3, m_pSet->m_JobPrice3, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPRICE4, m_pSet->m_JobPrice4, m_pSet);
	DDX_FieldText(pDX, IDC_MAKE, m_pSet->m_Make, m_pSet);
	DDX_FieldText(pDX, IDC_MODEL, m_pSet->m_Model, m_pSet);
	DDX_DateTimeCtrl(pDX, IDC_ORDERDATE, m_pSet->m_OrderDate);
	DDX_DateTimeCtrl(pDX, IDC_ORDERTIME, m_pSet->m_OrderTime);
	DDX_FieldText(pDX, IDC_ORDERTOTAL, m_pSet->m_TotalOrder, m_pSet);
	DDX_FieldText(pDX, IDC_PARTNAME1, m_pSet->m_PartName1, m_pSet);
	DDX_FieldText(pDX, IDC_PARTNAME2, m_pSet->m_PartName2, m_pSet);
	DDX_FieldText(pDX, IDC_PARTNAME3, m_pSet->m_PartName3, m_pSet);
	DDX_FieldText(pDX, IDC_PARTNAME4, m_pSet->m_PartName4, m_pSet);
	DDX_FieldText(pDX, IDC_PROBDESC, m_pSet->m_ProbDesc, m_pSet);
	DDX_FieldText(pDX, IDC_QUANTITY1, m_pSet->m_Quantity1, m_pSet);
	DDX_FieldText(pDX, IDC_QUANTITY2, m_pSet->m_Quantity2, m_pSet);
	DDX_FieldText(pDX, IDC_QUANTITY3, m_pSet->m_Quantity3, m_pSet);
	DDX_FieldText(pDX, IDC_QUANTITY4, m_pSet->m_Quantity4, m_pSet);
	DDX_FieldText(pDX, IDC_RECOMMENDATIONS, m_pSet->m_Recommendations, m_pSet);
	DDX_FieldText(pDX, IDC_STATE, m_pSet->m_State, m_pSet);
	DDX_FieldText(pDX, IDC_SUBTOTAL1, m_pSet->m_SubTotal1, m_pSet);
	DDX_FieldText(pDX, IDC_SUBTOTAL2, m_pSet->m_SubTotal2, m_pSet);
	DDX_FieldText(pDX, IDC_SUBTOTAL3, m_pSet->m_SubTotal3, m_pSet);
	DDX_FieldText(pDX, IDC_SUBTOTAL4, m_pSet->m_SubTotal4, m_pSet);
	DDX_FieldText(pDX, IDC_TAXAMOUNT, m_pSet->m_TaxAmount, m_pSet);
	DDX_FieldText(pDX, IDC_TAXRATE, m_pSet->m_TaxRate, m_pSet);
	DDX_FieldText(pDX, IDC_TOTALLABOR, m_pSet->m_TotalLabor, m_pSet);
	DDX_FieldText(pDX, IDC_TOTALPARTS, m_pSet->m_TotalParts, m_pSet);
	DDX_FieldText(pDX, IDC_UNITPRICE1, m_pSet->m_UnitPrice1, m_pSet);
	DDX_FieldText(pDX, IDC_UNITPRICE2, m_pSet->m_UnitPrice2, m_pSet);
	DDX_FieldText(pDX, IDC_UNITPRICE3, m_pSet->m_UnitPrice3, m_pSet);
	DDX_FieldText(pDX, IDC_UNITPRICE4, m_pSet->m_UnitPrice4, m_pSet);
	DDX_FieldText(pDX, IDC_YEAR, m_pSet->m_CarYear, m_pSet);
	DDX_FieldText(pDX, IDC_ZIPCODE, m_pSet->m_ZIPCode, m_pSet);
	//}}AFX_DATA_MAP
}

BOOL CCPASView::PreCreateWindow(CREATESTRUCT& cs)
{
	return CRecordView::PreCreateWindow(cs);
}

void CCPASView::OnInitialUpdate()
{
	m_pSet = &GetDocument()->m_cPASSet;
	CRecordView::OnInitialUpdate();
	GetParentFrame()->RecalcLayout();
	ResizeParentToFit();

}

/////////////////////////////////////////////////////////////////////////////
// CCPASView printing

BOOL CCPASView::OnPreparePrinting(CPrintInfo* pInfo)
{
	// default preparation
	return DoPreparePrinting(pInfo);
}

void CCPASView::OnBeginPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
}

void CCPASView::OnEndPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
}

/////////////////////////////////////////////////////////////////////////////
// CCPASView diagnostics

#ifdef _DEBUG
void CCPASView::AssertValid() const
{
	CRecordView::AssertValid();
}

void CCPASView::Dump(CDumpContext& dc) const
{
	CRecordView::Dump(dc);
}

CCPASDoc* CCPASView::GetDocument() // non-debug version is inline
{
	ASSERT(m_pDocument->IsKindOf(RUNTIME_CLASS(CCPASDoc)));
	return (CCPASDoc*)m_pDocument;
}
#endif //_DEBUG

/////////////////////////////////////////////////////////////////////////////
// CCPASView database support
CRecordset* CCPASView::OnGetRecordset()
{
	return m_pSet;
}


/////////////////////////////////////////////////////////////////////////////
// CCPASView message handlers

Execute the application to test it

Practical Learning: Binding Controls to Data: MSVC .NET

If you are using MSVC .NET, open the CPASView.cpp source file and change its DoDataExchange method as follows:

void CCPASView::DoDataExchange(CDataExchange* pDX)
{
	CRecordView::DoDataExchange(pDX);
	// you can insert DDX_Field* functions here to 'connect' your controls to the database fields, ex.
	// DDX_FieldText(pDX, IDC_MYEDITBOX, m_pSet->m_szColumn1, m_pSet);
	// DDX_FieldCheck(pDX, IDC_MYCHECKBOX, m_pSet->m_bColumn2, m_pSet);
	// See MSDN and ODBC samples for more information
	DDX_DateTimeCtrl(pDX, IDC_ORDERDATE, m_pSet->m_OrderDate);
	DDX_DateTimeCtrl(pDX, IDC_ORDERTIME, m_pSet->m_OrderTime);
	DDX_FieldText(pDX, IDC_CUSTNAME, m_pSet->m_CustomerName, m_pSet);
	DDX_FieldText(pDX, IDC_ADDRESS, m_pSet->m_Address, m_pSet);
	DDX_FieldText(pDX, IDC_CITY, m_pSet->m_City, m_pSet);
	DDX_FieldText(pDX, IDC_STATE, m_pSet->m_State, m_pSet);
	DDX_FieldText(pDX, IDC_ZIPCODE, m_pSet->m_ZIPCode, m_pSet);
	DDX_FieldText(pDX, IDC_MAKE, m_pSet->m_Make, m_pSet);
	DDX_FieldText(pDX, IDC_MODEL, m_pSet->m_Model, m_pSet);
	DDX_FieldText(pDX, IDC_YEAR, m_pSet->m_CarYear, m_pSet);
	DDX_FieldText(pDX, IDC_PROBDESC, m_pSet->m_ProbDesc, m_pSet);
	DDX_FieldText(pDX, IDC_PARTNAME1, m_pSet->m_PartName1, m_pSet);
	DDX_FieldText(pDX, IDC_UNITPRICE1, m_pSet->m_UnitPrice1, m_pSet);
	DDX_FieldText(pDX, IDC_QUANTITY1, m_pSet->m_Quantity1, m_pSet);
	DDX_FieldText(pDX, IDC_SUBTOTAL1, m_pSet->m_SubTotal1, m_pSet);
	DDX_FieldText(pDX, IDC_PARTNAME2, m_pSet->m_PartName2, m_pSet);
	DDX_FieldText(pDX, IDC_UNITPRICE2, m_pSet->m_UnitPrice2, m_pSet);
	DDX_FieldText(pDX, IDC_QUANTITY2, m_pSet->m_Quantity2, m_pSet);
	DDX_FieldText(pDX, IDC_SUBTOTAL2, m_pSet->m_SubTotal2, m_pSet);
	DDX_FieldText(pDX, IDC_PARTNAME3, m_pSet->m_PartName3, m_pSet);
	DDX_FieldText(pDX, IDC_UNITPRICE3, m_pSet->m_UnitPrice3, m_pSet);
	DDX_FieldText(pDX, IDC_QUANTITY3, m_pSet->m_Quantity3, m_pSet);
	DDX_FieldText(pDX, IDC_SUBTOTAL3, m_pSet->m_SubTotal3, m_pSet);
	DDX_FieldText(pDX, IDC_PARTNAME4, m_pSet->m_PartName4, m_pSet);
	DDX_FieldText(pDX, IDC_UNITPRICE4, m_pSet->m_UnitPrice4, m_pSet);
	DDX_FieldText(pDX, IDC_QUANTITY4, m_pSet->m_Quantity4, m_pSet);
	DDX_FieldText(pDX, IDC_SUBTOTAL4, m_pSet->m_SubTotal4, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPERFORMED1, m_pSet->m_JobPerformed1, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPRICE1, m_pSet->m_JobPrice1, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPERFORMED2, m_pSet->m_JobPerformed2, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPRICE2, m_pSet->m_JobPrice2, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPERFORMED3, m_pSet->m_JobPerformed3, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPRICE3, m_pSet->m_JobPrice3, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPERFORMED4, m_pSet->m_JobPerformed4, m_pSet);
	DDX_FieldText(pDX, IDC_JOBPRICE4, m_pSet->m_JobPrice4, m_pSet);
	DDX_FieldText(pDX, IDC_TOTALPARTS, m_pSet->m_TotalParts, m_pSet);
	DDX_FieldText(pDX, IDC_TOTALLABOR, m_pSet->m_TotalLabor, m_pSet);
	DDX_FieldText(pDX, IDC_TAXRATE, m_pSet->m_TaxRate, m_pSet);
	DDX_FieldText(pDX, IDC_TAXAMOUNT, m_pSet->m_TaxAmount, m_pSet);
	DDX_FieldText(pDX, IDC_ORDERTOTAL, m_pSet->m_TotalOrder, m_pSet);
	DDX_FieldText(pDX, IDC_RECOMMENDATIONS, m_pSet->m_Recommendations, m_pSet);
}

Execute the application to test it

Order Related Calculations

In this application, we would like the employee to enter the list of parts used, the prices of those parts, and the list of the jobs performed when fixing the car. All these results in values that we don't have while designing the application. Because of this, we created a button that can be used to perform the necessary calculations. When writing the SQL statement of the database, we included fields that in fact should be calculated. Of course this is a matter of opinion: some people would not make these columns part of the database, probably because they are calculated and therefore should remain external.

Practical Learning: Performing Order-Related Calculations

Generate a BN_CLICKED message for the IDC_CALCULATE button and implement it as follows:

void CCPASView::OnBnClickedCalculate()
{
	// TODO: Add your control notification handler code here
	double unitPrice1, unitPrice2, unitPrice3, unitPrice4;
	int quantity1, quantity2, quantity3, quantity4;
	double subTotal1, subTotal2, subTotal3, subTotal4;
	double jobPrice1, jobPrice2, jobPrice3, jobPrice4;
	double totalParts, totalLabor, taxRate, taxAmount, orderTotal;

	CString strUnitPrice1, strUnitPrice2, strUnitPrice3, strUnitPrice4;
	CString strQuantity1, strQuantity2, strQuantity3, strQuantity4;
	CString strJobPrice1, strJobPrice2, strJobPrice3, strJobPrice4;
	CString strTaxRate;

	// Retrieve the value entered in each unit price controls
	// of the Parts Used section
	GetDlgItemText(IDC_UNITPRICE1, strUnitPrice1);
	unitPrice1 = atof(strUnitPrice1);
	GetDlgItemText(IDC_UNITPRICE2, strUnitPrice2);
	unitPrice2 = atof(strUnitPrice2);
	GetDlgItemText(IDC_UNITPRICE3, strUnitPrice3);
	unitPrice3 = atof(strUnitPrice3);
	GetDlgItemText(IDC_UNITPRICE4, strUnitPrice4);
	unitPrice4 = atof(strUnitPrice4);

	// Retrieve the quantity entered for each part 
	GetDlgItemText(IDC_QUANTITY1, strQuantity1);
	quantity1 = atoi(strQuantity1);
	GetDlgItemText(IDC_QUANTITY2, strQuantity2);
	quantity2 = atoi(strQuantity2);
	GetDlgItemText(IDC_QUANTITY3, strQuantity3);
	quantity3 = atoi(strQuantity3);
	GetDlgItemText(IDC_QUANTITY4, strQuantity4);
	quantity4 = atoi(strQuantity4);

	// Retrieve the price for each job
	GetDlgItemText(IDC_JOBPRICE1, strJobPrice1);
	jobPrice1 = atof(strJobPrice1);
	GetDlgItemText(IDC_JOBPRICE2, strJobPrice2);
	jobPrice2 = atof(strJobPrice2);
	GetDlgItemText(IDC_JOBPRICE3, strJobPrice3);
	jobPrice3 = atof(strJobPrice3);
	GetDlgItemText(IDC_JOBPRICE4, strJobPrice4);
	jobPrice4 = atof(strJobPrice4);

	// Get the tax rate of the current order
	GetDlgItemText(IDC_TAXRATE, strTaxRate);
	taxRate = atof(strTaxRate);

	// Calculate the sub-total of each part used
	// based on its unit price and quantitty
	subTotal1 = unitPrice1 * quantity1;
	subTotal2 = unitPrice2 * quantity2;
	subTotal3 = unitPrice3 * quantity3;
	subTotal4 = unitPrice4 * quantity4;

	// Caculate the total price of the parts used
	totalParts = subTotal1 + subTotal2 + subTotal3 + subTotal4;
	// Calculate the total amount of the jobs performed
	totalLabor = jobPrice1 + jobPrice2 + jobPrice3 + jobPrice4;

	// Add the total of parts and the total of labor
	// then calculate the amount of tax for this order
	taxAmount = (totalParts + totalLabor) * taxRate / 100;
	// Calculate the total amount of this repair job
	orderTotal = totalParts + totalLabor + taxAmount;

	CString strSubTotal1, strSubTotal2, strSubTotal3, strSubTotal4;
	
	// Before displaying the values, format each to display two decimals
	strSubTotal1.Format("%.2f", subTotal1);
	strSubTotal2.Format("%.2f", subTotal2);
	strSubTotal3.Format("%.2f", subTotal3);
	strSubTotal4.Format("%.2f", subTotal4);

	// Display the sub-totals in the Parts Used section
	SetDlgItemText(IDC_SUBTOTAL1, strSubTotal1);
	SetDlgItemText(IDC_SUBTOTAL2, strSubTotal2);
	SetDlgItemText(IDC_SUBTOTAL3, strSubTotal3);
	SetDlgItemText(IDC_SUBTOTAL4, strSubTotal4);

	CString strTotalParts, strTotalLabor, strTaxAmount, strOrderTotal;

	strTotalParts.Format("%.2f", totalParts);
	strTotalLabor.Format("%.2f", totalLabor);
	strTaxAmount.Format("%.2f", taxAmount);
	strOrderTotal.Format("%.2f", orderTotal);

	// Display the values of the Order Summary section
	SetDlgItemText(IDC_TOTALPARTS, strTotalParts);
	SetDlgItemText(IDC_TOTALLABOR, strTotalLabor);
	SetDlgItemText(IDC_TAXAMOUNT, strTaxAmount);
	SetDlgItemText(IDC_ORDERTOTAL, strOrderTotal);
}

Generate an event for the WM_KILLFOCUS message for the IDC_QUANTITY1 and name it OnLostFocusQuantity1

Implement it as follows:

void CCPASView::OnLostFocusQuantity1()
{
	// TODO: Add your control notification handler code here
	OnBnClickedCalculate();
}

Generate an event for the WM_KILLFOCUS message for the IDC_QUANTITY2 named OnLostFocusQuantity2 and implement it as follows:

void CCPASView::OnLostFocusQuantity2()
{
	// TODO: Add your control notification handler code here
	OnBnClickedCalculate();
}

Generate an event for the WM_KILLFOCUS message for the IDC_QUANTITY3 named OnLostFocusQuantity3 and implement it as follows:

void CCPASView::OnLostFocusQuantity3()
{
	// TODO: Add your control notification handler code here
	OnBnClickedCalculate();
}

Generate an event for the WM_KILLFOCUS message for the IDC_QUANTITY4 named OnLostFocusQuantity4 and implement it as follows:

void CCPASView::OnLostFocusQuantity4()
{
	// TODO: Add your control notification handler code here
	OnBnClickedCalculate();
}

Generate an event for the WM_KILLFOCUS message for the IDC_JOBPRICE1 named OnLostFocusJobPrice1 and implement it as follows:

void CCPASView::OnLostFocusJobPrice1()
{
	// TODO: Add your control notification handler code here
	OnBnClickedCalculate();
}

Generate an event for the WM_KILLFOCUS message for the IDC_JOBPRICE2 named OnLostFocusJobPrice2 and implement it as follows:

void CCPASView::OnLostFocusJobPrice2()
{
	// TODO: Add your control notification handler code here
	OnBnClickedCalculate();
}

Generate an event for the WM_KILLFOCUS message for the IDC_JOBPRICE3 named OnLostFocusJobPrice3 and implement it as follows:

void CCPASView::OnLostFocusJobPrice3()
{
	// TODO: Add your control notification handler code here
	OnBnClickedCalculate();
}

Generate an event for the WM_KILLFOCUS message for the IDC_JOBPRICE4 named OnLostFocusJobPrice4 and implement it as follows:

void CCPASView::OnLostFocusJobPrice4()
{
	// TODO: Add your control notification handler code here
	OnBnClickedCalculate();
}

Execute the application

Adding a Repair Order

In the Class View, right-click CCPASSet -> Add -> Add Function...
Set the Function Type to long
Set its Name to GetLastID and press Enter

Implement the method as follows:

long CCPASSet::GetLastID(void)
{
	MoveLast();
	return this->m_WorkorderID;
}

Access the IDR_MAINFRAME menu and, under the Record group, add a new menu item as follows:
Caption: New Repair Order
ID: ID_RECORD_ADD
Prompt: Adds a new work order\nNew Work Order
Access the IDR_MAINFRAME toolbar
Create a new button designed as follows and associate it the same identifier as the New Repaird Order menu item:
If you are using MSVC 6, press Ctrl + W to access the ClassWizard. In the Class Name combo box, select CCPASView and, in the Object IDs list box, select ID_RECORD_ADD. In the Messages list box, double-click COMMAND. Accept the suggested name of the function and click Edit Code
If you are using MSVC .NET, access the IDR_MAINFRAME menu, right-click New Repair Order and click Add Event Handler... Associate the event to the view class

Click Add and Edit

Implement the method as follows:

void CCPASView::OnRecordAdd()
{
	// TODO: Add your command handler code here
	if( m_pSet->CanUpdate() && !m_pSet->IsDeleted() )
	{
		m_pSet->Edit();
		if( !UpdateData() )
			return;

		m_pSet->Update();
	}

	long lngNewID = m_pSet->GetLastID() + 1;
	m_pSet->AddNew();
	m_pSet->m_WorkorderID = lngNewID;
	m_pSet->Update();
	m_pSet->Requery();
	m_pSet->MoveLast();
	UpdateData(FALSE);
}

Test the application and return to MSVC

Deleting a Record

Add a Delete menu item:
On the toolbar, add a button that has the same identifier as the Delete menu item:

Implement its COMMAND function as follows:

void CCPASView::OnRecordDelete()
{
	// TODO: Add your command handler code here
	int Answer = AfxMessageBox("Are you sure you want to delete this repair order?",
		                       MB_YESNO);

	if( Answer == IDYES )
	{
		m_pSet->Delete();
		m_pSet->MovePrev();
		UpdateData(FALSE);
	}
}

Test the application


Home	Copyright © 2005-2012, FunctionX, Inc.