Knowledgebase
[QODBC-Desktop] Sample Code for Inserting InvoiceLine to existing Invoice using PHP
Posted by Jack - QODBC Support on 02 March 2017 02:26 PM

Sample Code for Inserting InvoiceLine to existing Invoice using PHP

Sample Application:

Please click here to download Sample Code.

Please refer below steps for using the application for Inserting InvoiceLine to existing Invoice using PHP.

Run application.

The application has two functionality:

1. Append the existing Invoice with a new Description Line, adding a new Description Line to the current Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice & description which you want to enter and click on the "Insert New Invoice Line (Description Only)" button.

New Description Line is added to the existing Invoice.

Result in QuickBooks.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the current Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, the Full Item Name, Quantity, Rate & Description which you want to enter, and click on the "Insert New Invoice Line (Inventory/Service)" button.

New Item Line is added to the existing Invoice.

Result in QuickBooks.

Application Source Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
    <title>QODBC PHP Script To Display SQL Results</title>

</head>
<body topmargin="3" leftmargin="3" marginheight="0" marginwidth="0" bgcolor="#ffffff" link="#000066" vlink="#000000" alink="#0000ff" text="#000000">


<table>
	<tr>
		<td>
			Append existing Invoice with a new Description line
			<form name="frmDescriptionLine" id="frmDescriptionLine" method="post">
			<table>
				<tr>
					<td>Enter Invoice No. (RefNumber)</td>
					<td><input type="text" name="txtInvoiceNo" id="txtInvoiceNo" style="width:250px;"/></td>
				</tr>
				<tr>
					<td>Enter New Item Description</td>
					<td><input type="text" name="txtDescription" id="txtDescription"style="width:250px;"/></td>
				</tr>
				<tr>
					<td colspan="2"><input type="submit" name="btnDescriptionLine" id="btnDescriptionLine" value="Insert New Invoice Line (Description Only)" onclick="InsertRecord()"/></td>
				</tr>
				</table>
			</form>
		</td>		
		<td>
			Append existing Invoice with a new  ItemInventory/ItemService Line
			<form name="frmItemLine" id="frmItemLine" method="post">
			<table>
				<tr>
					<td>Enter Invoice No. (RefNumber)</td>
					<td><input type="text" name="txtItemInvoiceNo" id="txtItemInvoiceNo" style="width:250px;"/></td>
				</tr>
				<tr>
					<td>Enter Item FullName</td>
					<td><input type="text" name="txtItemRefDescription" id="txtItemRefDescription" style="width:250px;"/></td>
				</tr>
				<tr>
					<td>Quantity</td>
					<td><input type="text" name="txtItemQuantity" id="txtItemQuantity" value="1"/></td>					
				</tr>
				<tr>
				<td>Rate</td>
				<td> <input type="text" name="txtItemRate" id="txtItemRate" value="1"/></td>
				</tr>
				<tr>
					<td>Enter Description</td>
					<td><input type="text" name="txtItemDescription" id="txtItemDescription" style="width:250px;"/></td>
				</tr>
				<tr>
					<td colspan="2"><input type="submit" name="btnItemLine" id="btnItemLine" value="Insert Invoice Line (Inventory/Service)" /></td>
				</tr>	
				</table>
			</form>
		</td>
	</tr>
</table>


    
</body>
</html>



<?php
if(isset($_POST['btnDescriptionLine']))
{

	$invoiceNo= $_POST['txtInvoiceNo'];
	$invoiceDescr = $_POST['txtDescription'];	
	if($invoiceNo == "" || $invoiceDescr == "" )
	{
		echo '<script language="javascript">';
		echo 'alert("Enter Invoice No. and Invoice Line Description both.")';
		echo '</script>';
	}
	else
	{
		set_time_limit(120);
		$oConnect = odbc_connect("QuickBooks Data QRemote", "", "");
		$sSQL = "select txnid from InvoiceLine where RefNumber='$invoiceNo'";
		$oResult = odbc_exec($oConnect, $sSQL);
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		while ($lFldCnt < $lFieldCount) {
		$lFldCnt++;
        $sFieldName = odbc_field_name($oResult, $lFldCnt);
		//print("                        $sFieldName\n");
		}		
		$lRecCnt = 0;
		while(odbc_fetch_row($oResult)) {
		$lRecCnt++;		
		//print("$lRecCnt");
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);		
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt++;
			$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
			If ($sFieldValue == "") {
				print("\n");
			}
			else {
				//print("$sFieldValue\n");
			}
		}
		//print("\n");
		}				
		$sSQL = "Insert into invoiceline(txnid,InvoiceLineDesc) values('$sFieldValue','$invoiceDescr')";
		$oResult = odbc_exec($oConnect, $sSQL);
		//$sSQL = "SELECT * FROM InvoiceLine Where txnid='$sFieldValue'";
		$sSQL = "SELECT RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineRate,InvoiceLineQuantity,InvoiceLineAmount  FROM InvoiceLine Where txnid='$sFieldValue'";
		#Perform the query
		$oResult = odbc_exec($oConnect, $sSQL);
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		//print("$lFieldCount");
		print("<table border=\"1\">");
		print("<th>Line No.</th>\n");
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt++;
				$sFieldName = odbc_field_name($oResult, $lFldCnt);
			print("<th>$sFieldName</th>\n");
		}
		
		
				$lRecCnt = 0;
		#Fetch the data from the database
		while(odbc_fetch_row($oResult)) {
			$lRecCnt++;
			print("                    <tr>\n");
			print("                        <td>$lRecCnt</td>\n");
			$lFldCnt = 0;
			$lFieldCount = odbc_num_fields($oResult);
			while ($lFldCnt < $lFieldCount) {
				$lFldCnt++;
				$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
				If ($sFieldValue == "") {
					print("<td> </td>\n");
				}
				else {
					print("<td valign=\"Top\">$sFieldValue</td>\n");
				}
			}
			print("</tr>\n");
		}
		print("</table>");
		
		odbc_close($oConnect);
				//echo("Invoice No: " .  $invoiceNo. "<br />\n");
				//echo("Invoice Desc: " . $invoiceDescr. "<br />\n");	
			}
}


if(isset($_POST['btnItemLine']))
{
	$invoiceItemNo= $_POST['txtItemInvoiceNo'];
	$invoiceItemRef = $_POST['txtItemRefDescription'];
	$invoiceItemQuantity = $_POST['txtItemQuantity'];
	$invoiceItemRate = $_POST['txtItemRate'];
	$invoiceItemDescr = $_POST['txtItemDescription'];	
	if($invoiceItemNo == "" || $invoiceItemDescr == "" || $invoiceItemRef == "" || $invoiceItemQuantity =="" || $invoiceItemRate =="" )
	{
		echo '<script language="javascript">';
		echo 'alert("Fill the Details properly")';
		echo '</script>';
	}
	else
	{		
		set_time_limit(120);
		$oConnect = odbc_connect("QuickBooks Data QRemote", "", "");
		$sSQL = "select txnid from InvoiceLine where RefNumber='$invoiceItemNo'";
		$oResult = odbc_exec($oConnect, $sSQL);
		//echo $oResult;
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		while ($lFldCnt < $lFieldCount) {
		$lFldCnt++;
        $sFieldName = odbc_field_name($oResult, $lFldCnt);		
		}		
		$lRecCnt = 0;
		while(odbc_fetch_row($oResult)) {
		$lRecCnt++;		
		//print("$lRecCnt");
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);		
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt++;
			$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
			If ($sFieldValue == "") {
				print("\n");
			}
			else {
				//print("$sFieldValue\n");
			}
		}
		print("\n");
		}		
		
		$sSQL = "Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('$sFieldValue','$invoiceItemRef',$invoiceItemQuantity,$invoiceItemRate,'$invoiceItemDescr')";
		/*if($oResult = odbc_exec($oConnect, $sSQL)){
			echo '<script language="javascript">';
			echo 'alert("Success")';
			echo '</script>';
		}
		else
		{
			echo $oResult; exit();
		}*/		
		//print($oResult);
		$oResult = odbc_exec($oConnect, $sSQL);
		
		$sSQL = "SELECT RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineRate,InvoiceLineQuantity,InvoiceLineAmount FROM InvoiceLine Where txnid='$sFieldValue'";
		//$sSQL = "SELECT * FROM InvoiceLine Where txnid='$sFieldValue'";
		#Perform the query
		$oResult = odbc_exec($oConnect, $sSQL);
		
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		//print("$lFieldCount");
		print("<table border=\"1\">");
		print("<th>Line No.</th>\n");
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt= $lFldCnt+1;
				$sFieldName = odbc_field_name($oResult, $lFldCnt);
			print("<th>$sFieldName</th>\n");
		}
				$lRecCnt = 0;
		#Fetch the data from the database
		while(odbc_fetch_row($oResult)) {
			$lRecCnt++;
			print("<tr>\n");
			print("<td>$lRecCnt</td>\n");
			$lFldCnt = 0;
			$lFieldCount = odbc_num_fields($oResult);
			while ($lFldCnt < $lFieldCount) {
				$lFldCnt++;
				$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
				If ($sFieldValue == "") {
					print("<td> </td>\n");
				}
				else {
					print("<td valign=\"Top\">$sFieldValue</td>\n");
				}
			}
			print("</tr>\n");
		}
		print("</table>");
		
		odbc_close($oConnect);
				//echo("Invoice No: " .  $invoiceNo. "<br />\n");
				//echo("Invoice Desc: " . $invoiceDescr. "<br />\n");	
			}
}


?>	
(2 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).