The Workings of the Purchase Management Application
|
The Purchase Management application will help WeMakeCars, Inc. improve its process of purchasing material from different suppliers. It will reduce the time cycle from requisitions to material being received at production setup. End users in the Purchasing department will be able to generate purchase orders automatically based on the requisition data. The purchase orders will be sent to suppliers in the XML format, and the invoice will be received from suppliers in the XML format. The Purchase Management application will import data from the XML-formatted invoice files into the WeMakeCars database.
Before using this application, the administrator needs to create the WeMakeCars data source using the ColdFusion MX Administrator page. The steps for creating data sources were discussed in Chapter 13, "Creating the Knowledge Bank Application," in the "Configuring the Data Source" section.
Working with Purchase Orders
This section will explain how to generate a purchase order and create an XML file for it.
Generating Purchase Orders
Generating Purchase Orders is the first page of the Purchase Management application. The page is loaded when the end user opens the WorkingWithPO.cfm file in the browser. The end user then selects the name of the supplier to generate the purchase order. The following query displays the list of suppliers:
<CFQUERY NAME="get_Supplier_Names" DATASOURCE="WeMakeCars"> SELECT Distinct tblRequisitions.Supplier_Code AS SupCode, tblSuppliers.Supplier_Name AS SupName FROM tblSuppliers,tblRequisitions WHERE Qty_Required > Qty_Ordered AND tblSuppliers.Supplier_Code = tblRequisitions.Supplier_Code </CFQUERY>
The query also shows the names of suppliers whose items are pending in the requisition data. This is indicated if the quantity required is greater than the quantity ordered. The get_Supplier_Names query is used in the <CFSELECT> tag to display the list of suppliers. Supplier Name is a required field for generating a purchase order. The end user also has to specify the purchase order date. This too is a required field. Once these two fields are filled, the end user can click Generate Purchase Order to generate the purchase order based on the requisition data.
GeneratePO.cfm is loaded to insert the purchase order date and supplier code in the tblPurchaseHeader table. The purchase order number is generated automatically and saved in the variable varPONum. The following code snippet shows how the purchase order number is generated automatically and the purchase order date and supplier code are inserted into the tblPurchaseOrder table:
<CFQUERY NAME="InsertDataForPOHeader" DATASOURCE="WeMakeCars"> INSERT INTO tblPurchaseHeader (GenDate, Supplier_Code) VALUES ('#Form.PODate#','#Form.Selected_Supplier#') </CFQUERY> <CFQUERY NAME = "get_PO_Number" DATASOURCE="WeMakeCars"> SELECT Max(PO_Number) as PONum FROM tblPurchaseHeader </CFQUERY> <CFOUTPUT QUERY = "get_PO_Number"> <CFSET varPONum = #PONum#> </CFOUTPUT> The following code snippet shows how purchase order details are inserted into the tblPurchaseDetails table using the InsertItemsForPODetail query:
<CFQUERY NAME="InsertItemsForPODetail" DATASOURCE="WeMakeCars"> INSERT INTO tblPurchaseDetails SELECT #varPONum# as PONUM,tblRequisitions.Item_code, Qty_Required, UNIT_PRICE,RequiredByDate FROM tblRequisitions,tblItemSupplierCostList WHERE Qty_Required > Qty_Ordered AND tblRequisitions.Supplier_Code = '#Form.Selected_Supplier#' AND tblItemSupplierCostList.Item_Code = tblRequisitions.Item_Code AND tblItemSupplierCostList.Supplier_Code = tblRequisitions.Supplier_Code </CFQUERY>
After data has been inserted in tblPurchaseDetails, the related item rows in tblRequisitions are updated. The Qty_Ordered field is set to be equal to the Qty_Required field. Use the query UpdateReqData to update the desired rows:
<CFQUERY NAME="UpdateReqData" DATASOURCE="WeMakeCars"> UPDATE tblRequisitions SET QTY_Ordered = Qty_Required WHERE Qty_Required > QTY_Ordered AND tblRequisitions.Supplier_Code = '#Form.Selected_Supplier#' </CFQUERY>
After the purchase order has been generated, ShowPO.cfm is called with the purchase order number as a parameter.
Creating an XML File from the Purchase Order
The purchase order number from ShowPO.cfm fetches the details from tblPurchaseHeader and tblPurchaseDetails. Create separate queries to calculate the total amount of the purchase order and fetch the supplier name from the supplier master. The following code snippet shows the various queries executed to fetch the purchase order information:
<CFSET varPONum = #URL.PO#> <CFQUERY NAME="getPOHeaderInfo" DATASOURCE="WeMakeCars"> SELECT * FROM tblPurchaseHeader WHERE PO_Number = #varPONum# </CFQUERY> <CFOUTPUT QUERY = "getPOHeaderInfo"> <CFSET varSupplierCode = #Supplier_Code#> <CFSET varPODate = #GenDate#> </CFOUTPUT> <CFQUERY NAME="SelectItemsFormPODetail" DATASOURCE="WeMakeCars"> SELECT tblPurchaseDetails.Item_code, Item_Description,Qty_Ordered, Unit_Price,DateRequiredBy,(Qty_Ordered*Unit_Price) AS AMOUNT FROM tblPurchaseDetails,tblItemMaster WHERE PO_Number = #varPONum# AND tblItemMaster.Item_Code = tblPurchaseDetails.Item_Code </CFQUERY> <CFQUERY NAME="getTotalAmount" DATASOURCE="WeMakeCars"> SELECT sum((Qty_Ordered*Unit_Price)) AS TotalAmount FROM tblPurchaseDetails WHERE PO_Number = #varPONum# </CFQUERY> <CFOUTPUT QUERY = "getTotalAmount"> <CFSET varTotalAmount = #TotalAmount#> </CFOUTPUT> <CFQUERY NAME="getSupplierName" DATASOURCE="WeMakeCars"> SELECT Supplier_Name FROM tblSuppliers WHERE Supplier_Code = '#varSupplierCode#' </CFQUERY> <CFOUTPUT QUERY = "getSupplierName"> <CFSET varSupplierName = #Supplier_Name#> </CFOUTPUT>
The following code displays the details fetched for the purchase order on the page:
<cfoutput> <P > The Purchase Order Number: #varPONum# Is Generated for Supplier: #varSupplierName#.<br> The Purchase Date Is : #dateformat(varPODate,"mm/dd/yyyy")#. The Total Purchase Order Amount Is: #varTotalAmount# <br> <U>Click the Convert to XML Button to Create and Save the XML File for This Purchase Order.</U> </P> </cfoutput> <DIV align="center"> <form Name= "ShowPO" ACTION="CreateXMLFile.cfm" METHOD="POST"> <cfoutput> <input type="hidden" name="PONUM" Value=#varPONum#> </cfoutput> <TABLE cellpadding="3" cellspacing="3"> <TH>Item Code</TH> <TH>Item Description</TH> <TH>Quantity Ordered</TH> <TH>Unit Price</TH> <TH>Amount</TH> <TH>Required By Date</TH> <cfoutput query="SelectItemsFormPODetail"> <TR> <TD>#Item_Code#</TD> <TD>#Item_Description#</TD> <TD>#Qty_Ordered#</TD> <TD>#Unit_Price#</TD> <TD>#Amount#</TD> <TD>#dateformat(DateRequiredBy,"mm/dd/yyyy")#</TD> </TR> </cfoutput> </TABLE>
The end user clicks Create XML File to load CreateXMLFile.cfm. This file uses the purchase order information to create the XML file. As mentioned earlier, you can fetch the information pertaining to the purchase order using various queries. Use the <cfxml> tag to create the ColdFusion MX XML document object in the POXML variable. You can design the structure of the XML document by creating your own tags, such as <PurchaseOrder>, <ItemDetails>, and <Code>. These details are shown in the following code snippet:
<cfxml variable="POXML"> <cfoutput> <PurchaseOrder Number = "#Form.PONUM#" PODate = "#varPODate#" SupplierCode = "#varSupplierCode#" SupplierName = "#varSupplierName#"> </cfoutput> <ItemDetails> <cfoutput query="SelectItemsFormPODetail"> <Code> #Item_Code# </Code> <Desc> #Item_Description# </Desc> <QtyOrdered> #Qty_Ordered# </QtyOrdered> <UnitPrice> #Unit_Price# </UnitPrice> <RequiredDate> #dateformat(DateRequiredBy,"mm/dd/yy")# </RequiredDate> <Amount> #Amount# </Amount> </cfoutput> </ItemDetails> </PurchaseOrder> </cfxml>
Once the structure of the purchase order has been created in the XML document object, it's converted to a string and saved in a file in the C:\PurchaseOrders directory, as follows:
<cfset XMLTextForFile=ToString(POXML)> <cfset varFile = "C:\PurchaseOrders\PO_" & #Form.PONUM# & ".xml"> <cffile action="write" file="#varFile#" output="#XMLTextForFile#">
The following is the structure of the XML generated for the purchase order:
<?xml version="1.0" encoding="UTF-8"?> <PurchaseOrder Number="6" PODate="2002-02-12 00:00:00.0" SupplierCode="SUP003 " SupplierName="Quick Assembly"> <ItemDetails> <Code>IT0006</Code> <Desc>Front Indicator Assembly</Desc> <QtyOrdered>130</QtyOrdered> <UnitPrice>14</UnitPrice> <RequiredDate>12/12/02</RequiredDate> <Amount>1820</Amount> </ItemDetails> </PurchaseOrder>
The XML purchase order files are then sent to the suppliers.
Working with Invoices
This section covers the features that enable end users to work on the XML-formatted invoices sent by the suppliers. They can view the new invoice XML files and save the invoice information in the database.
View the New Invoice Files
ShowXMLInvoiceFiles is the first page of this part of the application. This page shows all the files from the C:\NewInvoices directory. These files contain information about individual invoices. The files are displayed in a tabular form, as shown in the following code:
<CFSET varDir = "C:\NewInvoices"> <CFDIRECTORY ACTION="List" DIRECTORY=#varDir# NAME="ShowDir" SORT="Name ASC"> <TABLE BORDER=0 WIDTH=100%> <TR> <TD WIDTH=25%><STRONG><U>Name of the File</U></STRONG></TD> <TD WIDTH=25%><STRONG><U>Size (in bytes)</U></STRONG></TD> <TD><STRONG><U>Date Last Modified</U></STRONG></TD> </TR> <CFOUTPUT QUERY="ShowDir"> <TR> <TD><a href ="ShowInvoice.cfm?FN=#URLEncodedFormat(Name)#"> #Name#</a></TD> <TD>#Size#</TD> <TD>#DateLastModified#</TD> </TR> </CFOUTPUT> </TABLE>
ShowInvoice.cfm is displayed as a hyperlink and is called when the link is clicked. The filename is passed as a parameter to this file. This file reads the XML file and displays the invoice in a tabular format.
View the Individual Invoice and Save It
ShowInvoice.cfm uses the XML document object and reads the XML file. The individual components are extracted from the XML-formatted invoice file. These components are then displayed as invoice number, invoice date, and other details.
The following code shows the XML format for the invoice:
<Invoice Number="INV001" InvDate = "12/17/02" PONum="1"> <Supplier name="AutoLights" code="SUP005"/> <InvoiceItems> <item code="IT0001"> <SuppliedQty> 120 </SuppliedQty> <UnitPrice> 10 </UnitPrice> </item> <item code="IT0002"> <SuppliedQty> 130 </SuppliedQty> <UnitPrice> 13 </UnitPrice> </item> </InvoiceItems> </Invoice>
The following code shows how the invoice information is read from the XML file and displayed on the Show Invoice page:
<cfset varFile = "C:\NewInvoices\" & #URL.FN#> <cffile action="read" file=#varFile# variable="getInvXML"> <cfset InvXMLDoc = XmlParse(getInvXML)> <cfset varInvoiceNum=#InvXMLDoc.Invoice.XmlAttributes.Number#> <cfset varInvoiceDate=#InvXMLDoc.Invoice.XmlAttributes.InvDate#> <cfset varInvoicePONum=#InvXMLDoc.Invoice.XmlAttributes.PONum#> <cfset varInvoiceSupName=#InvXMLDoc.Invoice.Supplier.XmlAttributes.name#> <cfset varInvoiceSupCode=#InvXMLDoc.Invoice.Supplier.XmlAttributes.code#> <cfset InvnumItems = ArrayLen(InvXMLDoc.Invoice.InvoiceItems.XmlChildren)> <html> <head> <title>WeMakeCars Inc. Purchase Management Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project6.css"> </head> <body> <DIV align="center" >WeMakeCars Inc. Purchase Management Application<BR> Working with Invoices </DIV> <cfoutput> <P > The Invoice Number: #varInvoiceNum# Is Generated By Supplier: #varInvoiceSupName#.<br> The Invoice Date is : #dateformat(varInvoiceDate,"mm/dd/yyyy")#. The Invoice Is Raised Against the Purchase Order Number: #varInvoicePONum#. The Total Number of Items in the Invoice Are: #InvnumItems#.<br> <U>To Save the Invoice in the Database, Click the Save Invoice Button.</U> </P> </cfoutput> <cfoutput> <DIV align="center" > <TABLE cellpadding="3" cellspacing="3"> <TH>Item Code</TH> <TH>Quantity Supplied</TH> <TH>Unit Price</TH> <cfloop index="i" from = "1" to = #InvnumItems#> <TR> <TD>#InvXMLDoc.Invoice.InvoiceItems.item[i].XmlAttributes.code# </TD> <TD>#InvXMLDoc.Invoice.InvoiceItems.item[i].SuppliedQty.XmlText# </TD> <TD>#InvXMLDoc.Invoice.InvoiceItems.item[i].UnitPrice.XmlText#</TD> </TR> </cfloop> </TABLE> <BR> <form action="SaveInvoice.cfm" method="post"> <input type="hidden" name="XMLFILE" value="#URL.FN#"> <INPUT TYPE="Submit" NAME="btnSave" VALUE="Save Invoice"> </form> <BR> <BR> <a href ="ShowXMLInvoiceFiles.cfm"><B>Work With Invoices</B></a><BR> <a href ="WorkingWithPO.cfm"><B>Back to Working with Purchase Orders</B></a> </DIV> </cfoutput> </body> </html>
The XML file is read into the getInvXML variable by using the <cffile> tag. This variable is converted to an XML document object by the XmlParse function and assigned to the InvXMLDoc variable. Different elements are extracted from the XML document object by referring to the different elements by using the dot notation. The elements are arranged in a hierarchy in the XML document object. Item details are extracted in a loop using an HTML Table control. The name of the XML file is stored in a hidden file within a form container. Clicking Save Invoice saves the invoice displayed on the page.
When the end user clicks Save Invoice, SaveInvoice.cfm is loaded. This file extracts the invoice details from the XML file and inserts them into the invoice tables. SaveInvoice.cfm checks whether the record for this invoice number already exists. If not, the record is inserted in tblInvoiceHeader and tblInvoiceDetails. The following code shows how this is done:
<CFQUERY NAME="InsertInvHeaderInfo" DATASOURCE="WeMakeCars"> INSERT INTO tblInvoiceHeader VALUES ('#varInvoiceNum#','#dateformat(varInvoiceDate,"mm/dd/yyyy")#' ,'#varInvoiceSupCode#',#varInvoicePONum#) </CFQUERY> <cfoutput> <cfloop index="i" from = "1" to = #InvnumItems#> <CFQUERY NAME="InsertInvDetailInfo" DATASOURCE="WeMakeCars"> INSERT INTO tblInvoiceDetails VALUES ('#varInvoiceNum#', '#InvXMLDoc.Invoice.InvoiceItems.item[i].XmlAttributes.code#', #InvXMLDoc.Invoice.InvoiceItems.item[i].SuppliedQty.XmlText#, #InvXMLDoc.Invoice.InvoiceItems.item[i].UnitPrice.XmlText#) </CFQUERY> </cfloop> After the invoice details are stored in their respective tables, the XML file is moved from C:\NewInvoices to C:\OldInvoices.
Now that you understand the code for all the Web pages, let's look at the code listing for all these pages.
Listing 25.1 provides the complete code for WorkingWithPO.cfm.
Listing 25.1: WorkingWithPO.cfm
 |
<CFQUERY NAME="get_Supplier_Names" DATASOURCE="WeMakeCars"> SELECT Distinct tblRequisitions.Supplier_Code AS SupCode, tblSuppliers.Supplier_Name AS SupName FROM tblSuppliers,tblRequisitions WHERE Qty_Required > Qty_Ordered AND tblSuppliers.Supplier_Code = tblRequisitions.Supplier_Code </CFQUERY> <html> <head> <title>WeMakeCars Inc. Purchase Management Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project6.css"> </head> <body> <DIV align="center" >WeMakeCars Inc. Purchase Management Application <BR> Working with Purchase Orders </DIV> <P > Select a Supplier and Generate Purchase Order Based on Requisition Data:</P> <DIV align="center"> <cfform Name= "GenPurchase" ACTION="GeneratePO.cfm" METHOD="POST"> <TABLE> <TR> <TD>* Select a Supplier Name:</TD> <TD><CFSELECT Name = "Selected_Supplier" Size = "7" QUERY="get_Supplier_Names" Value="SupCode" DISPLAY="SupName" REQUIRED="YES" MESSAGE="Select a Supplier Name Before Proceeding for Purchase Order Generation"></CFSELECT></TD> </TR> <TR> <TD>* Purchase Order Date:</TD> <TD><cfinput TYPE="Text" NAME="PODate" SIZE="15" MAXLENGTH="10" validate="date" message="Purchase Order Date Is Required and Should Be in a Proper Format"></TD> </TR> </TABLE> <BR> <INPUT TYPE="Submit" NAME="btnGenPurchaseOrder" VALUE="Generate Purchase Order"> <BR><BR> <a href ="ShowXMLInvoiceFiles.cfm"><B>Work With Invoices</B></a><BR> </cfform> </DIV> </body> </html>
|
Listing 25.2 provides the complete code for the GeneratePO.cfm page.
Listing 25.2: GeneratePO.cfm
|
<CFTRANSACTION> <CFQUERY NAME="InsertDataForPOHeader" DATASOURCE="WeMakeCars"> INSERT INTO tblPurchaseHeader (GenDate, Supplier_Code) VALUES ('#Form.PODate#','#Form.Selected_Supplier#') </CFQUERY> <CFQUERY NAME = "get_PO_Number" DATASOURCE="WeMakeCars"> SELECT Max(PO_Number) as PONum FROM tblPurchaseHeader </CFQUERY> <CFOUTPUT QUERY = "get_PO_Number"> <CFSET varPONum = #PONum#> </CFOUTPUT> <CFQUERY NAME="InsertItemsForPODetail" DATASOURCE="WeMakeCars"> INSERT INTO tblPurchaseDetails SELECT #varPONum# as PONUM,tblRequisitions.Item_code, Qty_Required, UNIT_PRICE,RequiredByDate FROM tblRequisitions,tblItemSupplierCostList WHERE Qty_Required > Qty_Ordered AND tblRequisitions.Supplier_Code = '#Form.Selected_Supplier#' AND tblItemSupplierCostList.Item_Code = tblRequisitions.Item_Code AND tblItemSupplierCostList.Supplier_Code = tblRequisitions.Supplier_Code </CFQUERY> <CFQUERY NAME="UpdateReqData" DATASOURCE="WeMakeCars"> UPDATE tblRequisitions SET QTY_Ordered = Qty_Required WHERE Qty_Required > QTY_Ordered AND tblRequisitions.Supplier_Code = '#Form.Selected_Supplier#' </CFQUERY> </CFTRANSACTION> <CFLOCATION URL="ShowPO.cfm?PO=#varPONum#" ADDTOKEN="no"> |
Listing 25.3 provides the complete code for the ShowPO.cfm page.
Listing 25.3: ShowPO.cfm
|
<CFSET varPONum = #URL.PO#> <CFQUERY NAME="getPOHeaderInfo" DATASOURCE="WeMakeCars"> SELECT * FROM tblPurchaseHeader WHERE PO_Number = #varPONum# </CFQUERY> <CFOUTPUT QUERY = "getPOHeaderInfo"> <CFSET varSupplierCode = #Supplier_Code#> <CFSET varPODate = #GenDate#> </CFOUTPUT> <CFQUERY NAME="SelectItemsFormPODetail" DATASOURCE="WeMakeCars"> SELECT tblPurchaseDetails.Item_code, Item_Description,Qty_Ordered, Unit_Price,DateRequiredBy,(Qty_Ordered*Unit_Price) AS AMOUNT FROM tblPurchaseDetails,tblItemMaster WHERE PO_Number = #varPONum# AND tblItemMaster.Item_Code = tblPurchaseDetails.Item_Code </CFQUERY> <CFQUERY NAME="getTotalAmount" DATASOURCE="WeMakeCars"> SELECT sum((Qty_Ordered*Unit_Price)) AS TotalAmount FROM tblPurchaseDetails WHERE PO_Number = #varPONum# </CFQUERY> <CFOUTPUT QUERY = "getTotalAmount"> <CFSET varTotalAmount = #TotalAmount#> </CFOUTPUT> <CFQUERY NAME="getSupplierName" DATASOURCE="WeMakeCars"> SELECT Supplier_Name FROM tblSuppliers WHERE Supplier_Code = '#varSupplierCode#' </CFQUERY> <CFOUTPUT QUERY = "getSupplierName"> <CFSET varSupplierName = #Supplier_Name#> </CFOUTPUT> <html> <head> <title>WeMakeCars Inc. Purchase Management Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project6.css"> </head> <body> <DIV align="center" >WeMakeCars Inc. Purchase Management Application<BR> Working with Purchase Orders </DIV> <cfoutput> <P > The Purchase Order Number: #varPONum# is Generated for Supplier: #varSupplierName#.<br> The Purchase Date Is : #dateformat(varPODate,"mm/dd/yyyy")#. The Total Purchase Order Amount Is: #varTotalAmount# <br> <U>Click the Convert to XML Button to Create and Save the XML File for This Purchase Order.</U> </P> </cfoutput> <DIV align="center"> <form Name= "ShowPO" ACTION="CreateXMLFile.cfm" METHOD="POST"> <cfoutput> <input type="hidden" name="PONUM" Value=#varPONum#> </cfoutput> <TABLE cellpadding="3" cellspacing="3"> <TH>Item Code</TH> <TH>Item Description</TH> <TH>Quantity Ordered</TH> <TH>Unit Price</TH> <TH>Amount</TH> <TH>Required By Date</TH> <cfoutput query="SelectItemsFormPODetail"> <TR> <TD>#Item_Code#</TD> <TD>#Item_Description#</TD> <TD>#Qty_Ordered#</TD> <TD>#Unit_Price#</TD> <TD>#Amount#</TD> <TD>#dateformat(DateRequiredBy,"mm/dd/yyyy")#</TD> </TR> </cfoutput> </TABLE> <BR> <INPUT TYPE="Submit" NAME="btnConvertToXML" VALUE="Create XML File"> <BR> <a href ="WorkingWithPO.cfm"><B>Back to Working with Purchase Orders</B></a><BR> <a href ="ShowXMLInvoiceFiles.cfm"><B>Work With Invoices</B></a><BR> </form> </DIV> </body> </html>
|
Listing 25.4 provides the complete code for the CreateXMLFile.cfm page.
Listing 25.4: CreateXMLFile.cfm
|
CFQUERY NAME="getPOHeaderInfo" DATASOURCE="WeMakeCars"> SELECT * FROM tblPurchaseHeader WHERE PO_Number = #Form.PONUM# </CFQUERY> <CFOUTPUT QUERY = "getPOHeaderInfo"> <CFSET varSupplierCode = #Supplier_Code#> <CFSET varPODate = #GenDate#> </CFOUTPUT> <CFQUERY NAME="SelectItemsFormPODetail" DATASOURCE="WeMakeCars"> SELECT tblPurchaseDetails.Item_code, Item_Description,Qty_Ordered, Unit_Price,DateRequiredBy,(Qty_Ordered*Unit_Price) AS AMOUNT FROM tblPurchaseDetails,tblItemMaster WHERE PO_Number = #Form.PONUM# AND tblItemMaster.Item_Code = tblPurchaseDetails.Item_Code </CFQUERY> <CFQUERY NAME="getTotalAmount" DATASOURCE="WeMakeCars"> SELECT sum((Qty_Ordered*Unit_Price)) AS TotalAmount FROM tblPurchaseDetails WHERE PO_Number = #Form.PONUM# </CFQUERY> <CFOUTPUT QUERY = "getTotalAmount"> <CFSET varTotalAmount = #TotalAmount#> </CFOUTPUT> <CFQUERY NAME="getSupplierName" DATASOURCE="WeMakeCars"> SELECT Supplier_Name FROM tblSuppliers WHERE Supplier_Code = '#varSupplierCode#' </CFQUERY> <CFOUTPUT QUERY = "getSupplierName"> <CFSET varSupplierName = #Supplier_Name#> </CFOUTPUT> <cfxml variable="POXML"> <cfoutput> <PurchaseOrder Number = "#Form.PONUM#" PODate = "#varPODate#" SupplierCode = "#varSupplierCode#" SupplierName = "#varSupplierName#"> </cfoutput> <ItemDetails> <cfoutput query="SelectItemsFormPODetail"> <Code>#Item_Code#</Code> <Desc>#Item_Description#</Desc> <QtyOrdered>#Qty_Ordered#</QtyOrdered> <UnitPrice>#Unit_Price#</UnitPrice> <RequiredDate>#dateformat(DateRequiredBy,"mm/dd/yy")#</RequiredDate> <Amount>#Amount#</Amount> </cfoutput> </ItemDetails> </PurchaseOrder> </cfxml> <cfset XMLTextForFile=ToString(POXML)> <cfset varFile = "C:\PurchaseOrders\PO_" & #Form.PONUM# & ".xml"> <cffile action="write" file="#varFile#" output="#XMLTextForFile#"> <html> <head> <title>WeMakeCars Inc. Purchase Management Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project6.css"> </head> <body> <DIV align="center" >WeMakeCars Inc. Purchase Management Application<BR> Working with Purchase Orders </DIV> <cfoutput> <P >The #varFile# has been created.</P> </cfoutput> <BR><BR> <a href ="WorkingWithPO.cfm"><B>Back to Working with Purchase Orders</B></a><BR> <a href ="ShowXMLInvoiceFiles.cfm"><B>Work with Invoices</B></a> </body> </html>
|
Listing 25.5 provides the complete code for the ShowXMLInvoiceFiles.cfm page.
Listing 25.5: ShowXMLInvoiceFiles.cfm
|
<html> <html> <head> <title>WeMakeCars Inc. Purchase Management Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project6.css"> </head> <body> <DIV align="center" >WeMakeCars Inc. Purchase Management Application<BR> Working with Invoices </DIV> <br><P > The List of New Invoice XML Files. Click on Filename to View the Invoice.</P> <CFSET varDir = "C:\NewInvoices"> <CFDIRECTORY ACTION="List" DIRECTORY=#varDir# NAME="ShowDir" SORT="Name ASC"> <TABLE BORDER=0 WIDTH=100%> <TR> <TD WIDTH=25%><STRONG><U>Name of the File</U></STRONG></TD> <TD WIDTH=25%><STRONG><U>Size (in bytes)</U></STRONG></TD> <TD><STRONG><U>Date Last Modified</U></STRONG></TD> </TR> <CFOUTPUT QUERY="ShowDir"> <TR> <TD><a href ="ShowInvoice.cfm?FN=#URLEncodedFormat(Name )#">#Name#</a></TD> <TD>#Size#</TD> <TD>#DateLastModified#</TD> </TR> </CFOUTPUT> </TABLE> <br><br> <a href ="WorkingWithPO.cfm"><B>Back to Working with Purchase Orders</B></a><BR> </body> </html>
|
Listing 25.6 provides the complete code for the ShowInvoice.cfm page.
Listing 25.6: ShowInvoice.cfm
|
<cfset varFile = "C:\NewInvoices\" & #URL.FN#> <cffile action="read" file=#varFile# variable="getInvXML"> <cfset InvXMLDoc = XmlParse(getInvXML)> <cfset varInvoiceNum=#InvXMLDoc.Invoice.XmlAttributes.Number#> <cfset varInvoiceDate=#InvXMLDoc.Invoice.XmlAttributes.InvDate#> <cfset varInvoicePONum=#InvXMLDoc.Invoice.XmlAttributes.PONum#> <cfset varInvoiceSupName=#InvXMLDoc.Invoice.Supplier.XmlAttributes.name#> <cfset varInvoiceSupCode=#InvXMLDoc.Invoice.Supplier.XmlAttributes.code#> <cfset InvnumItems = ArrayLen(InvXMLDoc.Invoice.InvoiceItems.XmlChildren)> <html> <head> <title>WeMakeCars Inc. Purchase Management Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project6.css"> </head> <body> <DIV align="center" >WeMakeCars Inc. Purchase Management Application<BR> Working with Invoices </DIV> <cfoutput> <P > The Invoice Number: #varInvoiceNum# is Generated By Supplier: #varInvoiceSupName#.<br> The Invoice Date is : #dateformat(varInvoiceDate,"mm/dd/yyyy")#. The Invoice Is Raised Against the Purchase Order Number: #varInvoicePONum#. The Total Number of Items in the Invoice Are: #InvnumItems#.<br> <U>To Save the Invoice in the Database, Click the Save Invoice Button.</U> </P> </cfoutput> <cfoutput> <DIV align="center" > <TABLE cellpadding="3" cellspacing="3"> <TH>Item Code</TH> <TH>Quantity Supplied</TH> <TH>Unit Price</TH> <cfloop index="i" from = "1" to = #InvnumItems#> <TR> <TD>#InvXMLDoc.Invoice.InvoiceItems.item[i].XmlAttributes.code#</TD> <TD>#InvXMLDoc.Invoice.InvoiceItems.item[i].SuppliedQty.XmlText#</TD> <TD>#InvXMLDoc.Invoice.InvoiceItems.item[i].UnitPrice.XmlText#</TD> </TR> </cfloop> </TABLE> <BR> <form action="SaveInvoice.cfm" method="post"> <input type="hidden" name="XMLFILE" value="#URL.FN#"> <INPUT TYPE="Submit" NAME="btnSave" VALUE="Save Invoice"> </form> <BR> <BR> <a href ="ShowXMLInvoiceFiles.cfm"><B>Work with Invoices</B></a><BR> <a href ="WorkingWithPO.cfm"><B>Back to Working with Purchase Orders</B></a> </DIV> </cfoutput> </body> </html>
|
Listing 25.7 provides the complete code for the SaveInvoice.cfm page.
Listing 25.7: Savelnvoice.cfm
|
html> <head> <title>WeMakeCars Inc. Purchase Management Application</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <LINK REL = "stylesheet" TYPE ="text/css" HREF ="Project6.css"> </head> <body> <DIV align="center" >WeMakeCars Inc. Purchase Management Application<BR> Working with Invoices </DIV> <cfset varFile = "C:\NewInvoices\" & #Form.XMLFILE#> <cffile action="read" file=#varFile# variable="getInvXML"> <cfset InvXMLDoc = XmlParse(getInvXML)> <cfset varInvoiceNum=#InvXMLDoc.Invoice.XmlAttributes.Number#> <CFQUERY NAME="getInvHeaderInfo" DATASOURCE="WeMakeCars"> SELECT InvoiceNo FROM tblInvoiceHeader WHERE InvoiceNo = '#varInvoiceNum#' </CFQUERY> <CFIF getInvHeaderInfo.RECORDCOUNT IS 0> <cfset varInvoiceDate=#InvXMLDoc.Invoice.XmlAttributes.InvDate#> <cfset varInvoicePONum=#InvXMLDoc.Invoice.XmlAttributes.PONum#> <cfset varInvoiceSupName=#InvXMLDoc.Invoice.Supplier.XmlAttributes.name#> <cfset varInvoiceSupCode=#InvXMLDoc.Invoice.Supplier.XmlAttributes.code#> <cfset InvnumItems = ArrayLen(InvXMLDoc.Invoice.InvoiceItems.XmlChildren)> <cftransaction> <CFQUERY NAME="InsertInvHeaderInfo" DATASOURCE="WeMakeCars"> INSERT INTO tblInvoiceHeader VALUES ('#varInvoiceNum#','#dateformat(varInvoiceDate,"mm/dd/yyyy")#', '#varInvoiceSupCo de#',#varInvoicePONum#) </CFQUERY> <cfoutput> <cfloop index="i" from = "1" to = #InvnumItems#> <CFQUERY NAME="InsertInvDetailInfo" DATASOURCE="WeMakeCars"> INSERT INTO tblInvoiceDetails VALUES ('#varInvoiceNum#', '#InvXMLDoc.Invoice.InvoiceItems.item[i].XmlAttributes.code#', #InvXMLDoc.Invoice.InvoiceItems.item[i].SuppliedQty.XmlText#, #InvXMLDoc.Invoice.InvoiceItems.item[i].UnitPrice.XmlText#) </CFQUERY> </cfloop> <P > The Invoice Number: #varInvoiceNum# is Successfully Saved.</P> <CFFILE ACTION="move" SOURCE = #varFile# DESTINATION = "C:\OldInvoices"> </cfoutput> </cftransaction> <CFELSE> <cfoutput> <P > The Invoice Number: #varInvoiceNum# is Already Saved.</P> </cfoutput> </CFIF><BR> <a href ="ShowXMLInvoiceFiles.cfm"><B>Work with Invoices</B></a><BR> <a href ="WorkingWithPO.cfm"><B>Back to Working with Purchase Orders</B></a> </body> </HTML> |
Listing 25.8 provides the complete code for the Project6.css file.
Listing 25.8: Project6.css
|
body { background-color: #CCCCCC; font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 12px; line-height: 24px; color: #3300CC; } td { font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 11px; line-height: 24px; color: #000000; font-weight: bold; } th { font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 11px; line-height: 24px; color: #FFFFFF; font-weight: bold; background-color: #666666; } a { color: #000000; font-weight: bold; } form { background-color; #999999; font-family: Tahoma, Arial, Helvetica, sans-serif; } div { background-color: #999999; color: #FFFFFF; } .title { font-family: Georgia, "Times New Roman", Times, serif; font-size: 18px; line-height: 30px; background-color: #CC6666; color: #000000; font-weight: bold; text-decoration: underline; } a:hover { color: #000000; background-color: #CC6666; } .message { font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 12px; font-weight: bold; line-height: 30px; color: #990000; background-color: #999999; } .summary { font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 10px; line-height: 30px; background-color: #CCCC99; } .InfoLabel { font-family: Tahoma, Arial, Helvetica, sans-serif; font-size: 11px; line-height: 30px; background-color: #993333; color: #000000; font-weight: bold; } cfform { background-color: #999999; color: #99CCFF; } .DivText { color: #FFFFFF; background-color: #666666; } .divwithform { background-color: #999999; font-family: Tahoma, Arial, Helvetica, sans-serif; } |
|
EAN: 2147483647
Pages: 200