Home

Database Applications:
College Park Auto-Shop

 
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

  1. 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
  2. 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
  3. 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
  4. 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 Practical Learning: Creating an ODBC Data Source

  1. 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)
  2. In the User DSN property page of the ODBC Data Source Administrator property sheet, click the Add button:
     
  3. In the Create New Data Source wizard, click SQL Server:
     
  4. Click Finish
  5. In the Name edit box of the Create A New Data Source To SQL Server wizard, type cpas and press Tab
  6. In the Description edit box, type College Park Auto Shop
  7. Click the arrow of the Server combo box and select the server where the CPAS database resides or select (local):
     
  8. Click Next
  9. 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:
     
  10. Click Next
  11. In the new page of the Create A New Data Source To SQL Server wizard, click the Change The Default Database To check box.
  12. Once the combo box is enabled, click its arrow and select CPAS:
     
  13. For this example, leave the Attach Database FileName check box unchecked and accept the other defaults. Click Next
  14. Unless you have any other reason, accept the default (language, encryption, etc) settings of the new page:
     
  15. Click Finish
  16. On the ODBC Microsoft SQL Server Setup dialog box, click the Test Data Source button:
     
  17. When the SQL Server ODBC Data Source Test dialog box confirms that the TESTS COMPLETED SUCCESSFULLY!, click OK:
     
  18. On the ODBC Microsoft SQL Server Setup dialog box, click OK
  19. 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
  20. 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

  1. Start Microsoft Visual Studio or Visual C++
  2. Create a new MFC Application named CPAS
  3. Create it as Single Document
     
  4. Select Database View Without File Support
     
  5. If you are using MSVC .NET, set the Client Type to ODBC
    Click Data Source
  6. If you are using MSVC .NET, in the Select Data Source dialog box, click Machine Data Source
    In the list, select cpas
     
  7. 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
  8. Select Workorders
  9. Click OK
  10. 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.
  11. Read that line and then comment it

  12. 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;
    }

  13. 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
  14. 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

  1. 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
  2. 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
    
  3. Execute the application to test it
 

Practical Learning: Binding Controls to Data: MSVC .NET

  1. 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);
    }
  2. 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

  1. 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);
    }
  2. Generate an event for the WM_KILLFOCUS message for the IDC_QUANTITY1 and name it OnLostFocusQuantity1
     
  3. Implement it as follows:
     
    void CCPASView::OnLostFocusQuantity1()
    {
    	// TODO: Add your control notification handler code here
    	OnBnClickedCalculate();
    }
  4. 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();
    }
  5. 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();
    }
  6. 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();
    }
  7. 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();
    }
  8. 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();
    }
  9. 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();
    }
  10. 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();
    }
  11. Execute the application

Adding a Repair Order

  1. In the Class View, right-click CCPASSet -> Add -> Add Function...
  2. Set the Function Type to long
  3. Set its Name to GetLastID and press Enter
  4. Implement the method as follows:
     
    long CCPASSet::GetLastID(void)
    {
    	MoveLast();
    	return this->m_WorkorderID;
    }
  5. 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
  6. Access the IDR_MAINFRAME toolbar
  7. Create a new button designed as follows and associate it the same identifier as the New Repaird Order menu item:
     
  8. 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
  9. 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);
    }
  10. Test the application and return to MSVC

Deleting a Record

  1. Add a Delete menu item:
     
  2. On the toolbar, add a button that has the same identifier as the Delete menu item:
     
  3. 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);
    	}
    }
  4. Test the application
 

Home Copyright © 2005-2007 FunctionX, Inc.