Knowledgebase
[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 for downloading Sample Code.

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

Run application.

The application has two functionality:

1. Append existing Invoice with a new Description Line which will add new Description Line in existing Invoice.

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

New Description Line is added to existing Invoice.

Result in QuickBooks Online.

2. Append existing Invoice with a new ItemInventory/ItemService Line which will add new ItemInventory/ItemService Line in existing Invoice.

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

New Item Line is added to 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");	
			}
}


?>	

 

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).