Knowledgebase: Setup
[QODBC-Online] Sample Code for Inserting InvoiceLine to existing Invoice using PHP
Posted by Jack - QODBC Support on 24 March 2017 11:05 AM

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 an 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 which will add a new Description Line to the existing 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 Online.

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

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, the Item Full 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 Online.

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 Online 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 Online 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");	
			}
}


?>	

 

 

Tags: QuickBooks Online, QBO, QODBC Online, PHP

(1 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).