Quickbooks Import QBI Support Thread

Plugin: https://www.zen-cart.com/downloads.php?do=file&id=54

so I'm trying to update this add-on but I need a little help getting the admin page registration working but possibly more importantly this error sorted out to make sure it'll all be worth it:

[07-Jan-2018 01:27:35 America/New_York] Request URI: /spelL-MUd-shoRe/qbi_config.php, IP address: 73.52.227.139
#1 mysqli_real_escape_string() called at [/home/content/62/5902262/html/MY_ADMIN/includes/functions/qbi_functions.php:152]
#2 mysql_escape_string_array()
#3 array_walk() called at [/home/content/62/5902262/html/MY_ADMIN/includes/classes/qbi_classes.php:2610]
#4 Proc_form->proc_cleanData() called at [/home/content/62/5902262/html/MY_ADMIN/qbi_config.php:30]

[08-Jan-2018 15:36:37 America/New_York] PHP Warning: mysqli_real_escape_string() expects parameter 1 to be mysqli, string given in /home/content/62/5902262/html/MY_ADMIN/includes/functions/qbi_functions.php on line 154

here's the guilty function:

function mysql_escape_string_array(&$var, $db) {
    if (is_array($var)) {
        array_walk($var,'mysql_escape_string_array', $db);
    } else {
        $var=mysqli_real_escape_string($db,$var); // This function is deprecated. PHP>=4.3.0 may use mysql_real_escape_string
    }
    return $var;
}

if I switch it to mysql_real_escape_string( and swap the order of the parameters as needed) it php errors about $db not being a resource.

I don't quite understand constructors or classes at this point in my programming experience but this qbi_mysql.php file contains this:

    function & query($sql) {
		$this->dbConn = mysqli_connect($this->host, $this->dbUser, $this->dbPass, $this->dbName);
		
        if (!$queryResource=mysqli_query($this->dbConn,$sql)) {
            trigger_error ('Query failed: '.mysqli_error($this->dbConn).' SQL: '.$sql);
		}

		return new MySQLResult($this,$queryResource);
    }
}

which might help someone suggest a solution possibly?

Best practice in Zen Cart is not to use mysqli directly. This will get you started on understanding the Zen Cart db abstraction layer:
https://www.zen-cart.com/wiki/index.php/Developers_-_Database

Is this for QB online or QB desktop ?

wolfderby:

so I'm trying to update this add-on but I need a little help getting the admin page registration working but possibly more importantly this error sorted out to make sure it'll all be worth it:

[07-Jan-2018 01:27:35 America/New_York] Request URI: /spelL-MUd-shoRe/qbi_config.php, IP address: 73.52.227.139
#1 mysqli_real_escape_string() called at [/home/content/62/5902262/html/MY_ADMIN/includes/functions/qbi_functions.php:152]
#2 mysql_escape_string_array()
#3 array_walk() called at [/home/content/62/5902262/html/MY_ADMIN/includes/classes/qbi_classes.php:2610]
#4 Proc_form->proc_cleanData() called at [/home/content/62/5902262/html/MY_ADMIN/qbi_config.php:30]

[08-Jan-2018 15:36:37 America/New_York] PHP Warning: mysqli_real_escape_string() expects parameter 1 to be mysqli, string given in /home/content/62/5902262/html/MY_ADMIN/includes/functions/qbi_functions.php on line 154

here's the guilty function:

function mysql_escape_string_array(&$var, $db) {
if (is_array($var)) {
array_walk($var,'mysql_escape_string_array', $db);
} else {
$var=mysqli_real_escape_string($db,$var); // This function is deprecated. PHP>=4.3.0 may use mysql_real_escape_string
}
return $var;
}

> 
> if I switch it to mysql_real_escape_string( and swap the order of the parameters as needed) it php errors about $db not being a resource.
> 
> I don't quite understand constructors or classes at this point in my programming experience but this qbi_mysql.php file contains this:
> ```
    function & query($sql) {
		$this->dbConn = mysqli_connect($this->host, $this->dbUser, $this->dbPass, $this->dbName);
		
        if (!$queryResource=mysqli_query($this->dbConn,$sql)) {
            trigger_error ('Query failed: '.mysqli_error($this->dbConn).' SQL: '.$sql);
		}

		return new MySQLResult($this,$queryResource);
    }
}

which might help someone suggest a solution possibly?
If you do stay with the direct mysqli_ related function call (instead of using built-in ZC functions), then the first parameter instead of just $db (database object) would need to be the database link. Such is referenced within the query_factory class file by review of similar functions.

Further, like swguy was suggesting, there is a function within ZC that already supports the action part of that function, but not one that addresses the array aspect. Again that is in the query_factory class file and would make that portion of code flexible/compatible to more versions of ZC/php.

ShopVille:

Is this for QB online or QB desktop ?

This I believe is for QB Desktop to generate an IFF file

So I'm still stuck in the qbi_class.php file

	function find_country_id($country_name) {
		//$country_name=mysqli_real_escape_string($this->db,$country_name);
		//global $db;
		//$this->db=$db;
		//$this->db=$db;
		$country_name=mysql_real_escape_string($country_name,$this->dbConn);
		$country_id=0;
		$sql="SELECT countries_id FROM ".TABLE_COUNTRIES." WHERE countries_name LIKE '".$country_name."'";
		$result=$this->db->query($sql);
		if ($row=$result->fetch()) $country_id=$row['countries_id'];
		return($country_id);
	}

Considering this isn't used in any other part of zen-cart that I can find using developers tool-kit (or google really) i'm thinking there's a better way all together, basically I can't seem to satisfy the db link (2nd parameter part) for mysqli_real_escape_string

so after reading more into the database abstraction I'm convinced I need to go this bindVars route... so I'm trying this...

	function find_country_id($country_name) {
		$country_id=0;
		$sql="SELECT countries_id FROM ".TABLE_COUNTRIES." WHERE countries_name LIKE :country_name";
		$sql = $db->bindVars($sql, ':country_name', $country_name, 'string');
		$result = $db->Execute($sql);
		$country_id = $result->fields['countries_id']; 
		//$result=$this->db->query($sql);
		//if ($row=$result->fetch()) $country_id=$row['countries_id'];
		return($country_id);
	}

but it results in...

[08-Jan-2018 22:01:16 America/New_York] PHP Fatal error: Call to a member function bindVars() on a non-object in

wolfderby:

so after reading more into the database abstraction I'm convinced I need to go this bindVars route... so I'm trying this...

function find_country_id($country_name) {
	$country_id=0;
	$sql="SELECT countries_id FROM ".TABLE_COUNTRIES." WHERE countries_name LIKE :country_name";
	$sql = $db->bindVars($sql, ':country_name', $country_name, 'string');
	$result = $db->Execute($sql);
	$country_id = $result->fields['countries_id']; 
	//$result=$this->db->query($sql);
	//if ($row=$result->fetch()) $country_id=$row['countries_id'];
	return($country_id);
}

> but it results in...
> 
> [08-Jan-2018 22:01:16 America/New_York] PHP Fatal error:  Call to a member function bindVars() on a non-object in
I don't know if the following is cruel or just enough of a carrot. So, considering the things that have been made available to the function, is there anything in that line that seems undefined? Think beyond what is in the line as something you know/expect.

Now again, regarding the use of mysql_/mysqli_ related functions, these are typically abstracted in the ZC code so that a "common" function name can be used through all versions of ZC and the software still work. A concern I have whether valid or not, is that it appears that the software is written to take a sort of snapshot of the database and hold it in time while processing, as compared to constantly using the data of the current database. While it may not be so important in this particular function of whether to use the class' internal $db or the one that is used throughout the ZC software, there may be instances where an operation is performed on the internal $db in one function the results of which are pulled from another with the expectation that the internal $db data hasn't been modified between the two operations. 

It's not something that I necessarily expect, but considering how the internal $db was assigned at one point or another, it seems possible to use it that way and therefore would consider continuing to use in the same way but through migration to the functions available in the database's class.

mc12345678:

I don't know if the following is cruel or just enough of a carrot. So, considering the things that have been made available to the function, is there anything in that line that seems undefined? Think beyond what is in the line as something you know/expect.

Now again, regarding the use of mysql_/mysqli_ related functions, these are typically abstracted in the ZC code so that a "common" function name can be used through all versions of ZC and the software still work. A concern I have whether valid or not, is that it appears that the software is written to take a sort of snapshot of the database and hold it in time while processing, as compared to constantly using the data of the current database. While it may not be so important in this particular function of whether to use the class' internal $db or the one that is used throughout the ZC software, there may be instances where an operation is performed on the internal $db in one function the results of which are pulled from another with the expectation that the internal $db data hasn't been modified between the two operations.

It's not something that I necessarily expect, but considering how the internal $db was assigned at one point or another, it seems possible to use it that way and therefore would consider continuing to use in the same way but through migration to the functions available in the database's class.
Just enough of a carrot, thanks!

added global $db and it seems to be working for at least 1 new order.

attempting to dl 7206 orders internal server error 500'd but that's probably just my hosting, might attempt to add order range constraints.

...seems to be work so far, I'm going to do a bit more testing before uploading the latest version. Thanks.

So basically I got it sort of working, :/ . But it's probably not use-able. I did these rounding error mods (https://www.zen-cart.com/showthread.php?209786-Shopping-cart-Subtotal-not-equal-Checkout-Subtotal-%28rounding-error%29-with-GST-VAT/page2%29 a while back, that I think back-fired w/ the QB import, because it kept throwing an error on import into QB 2016 (The transaction is not in balance. Make sure the amounts in the detail area on the form for this transaction equal the amount at the top of the form.) Then I'd also end up w/ an "error on line XX: can't record invalid transaction error"

I compared the iif w/ qb's latest example but I probably need to dig back further for something to match better.

Long story short I jammed most of my 2017 transactions into a backed up QB company file only to have most of the data look good until I attempted to open the account register within QB and it would just error and crash QB.

If anyone's interested in what I have or thinks I should upload it as it's probably "better" than what I last uploaded let me know. I think I'm bailing on this in the meantime.

wolfderby:

So basically I got it sort of working, :/ . But it's probably not use-able. I did these rounding error mods (https://www.zen-cart.com/showthread.php?209786-Shopping-cart-Subtotal-not-equal-Checkout-Subtotal-%28rounding-error%29-with-GST-VAT/page2%29 a while back, that I think back-fired w/ the QB import, because it kept throwing an error on import into QB 2016 (The transaction is not in balance. Make sure the amounts in the detail area on the form for this transaction equal the amount at the top of the form.) Then I'd also end up w/ an "error on line XX: can't record invalid transaction error"

I compared the iif w/ qb's latest example but I probably need to dig back further for something to match better.

Long story short I jammed most of my 2017 transactions into a backed up QB company file only to have most of the data look good until I attempted to open the account register within QB and it would just error and crash QB.

If anyone's interested in what I have or thinks I should upload it as it's probably "better" than what I last uploaded let me know. I think I'm bailing on this in the meantime.
I say go for the upload, sure identify that there are issues, but at least having before and after code, perhaps something can be identified.

I mean, no real reason for someone else to start from scratch again if all the work has been done. My only "concern" is that way back when there was some other "tool" that had to be obtained in order to communicate with QB. Is that either included in the fileset or an improved reference about where/how to accomplish that part of the communication addressed? I spent days searching for that other software and ended up in some sort of loop where one page referenced another and then click here and just kept going. Couldn't find anything "official" about what the status of that software was...

Hi,

So I have been using this plug-in since ver 3.21 (quite a bit customized for my needs). I am in the process of rebuilding the old ZC site with new 1.5.5f and I simply must have QBI.
I am about to install what you have on my test server and will be happy to collaborate. I was just wondering if you have made any progress since Januiary and what is it that is causing you problems.

I personally use it to export Invoices (as opposed to Sales Order) and I am quite familiar with qbi format (the old enemy), its pitfalls and even use it to output custom qbi from paypal raw data (mostly for fees/payments). Hope we can help each other.

Can this map an item with attributes to a specific item in QB? I am currently using T-Hub but they jacked the price from $350/year to over $1200/year. The thing about T-Hub was that I was able to modify the PHP connector code that resides in ZC admin to be able to map items with attributes to specific items in QB which is a critical function for me. I would be interested in helping develop this if this feature is possible/available.

wolfderby:

so I'm trying to update this add-on but I need a little help getting the admin page registration working but possibly more importantly this error sorted out to make sure it'll all be worth it:

[07-Jan-2018 01:27:35 America/New_York] Request URI: /spelL-MUd-shoRe/qbi_config.php, IP address: 73.52.227.139
#1 mysqli_real_escape_string() called at [/home/content/62/5902262/html/MY_ADMIN/includes/functions/qbi_functions.php:152]
#2 mysql_escape_string_array()
#3 array_walk() called at [/home/content/62/5902262/html/MY_ADMIN/includes/classes/qbi_classes.php:2610]
#4 Proc_form->proc_cleanData() called at [/home/content/62/5902262/html/MY_ADMIN/qbi_config.php:30]

[08-Jan-2018 15:36:37 America/New_York] PHP Warning: mysqli_real_escape_string() expects parameter 1 to be mysqli, string given in /home/content/62/5902262/html/MY_ADMIN/includes/functions/qbi_functions.php on line 154

here's the guilty function:

function mysql_escape_string_array(&$var, $db) {
if (is_array($var)) {
array_walk($var,'mysql_escape_string_array', $db);
} else {
$var=mysqli_real_escape_string($db,$var); // This function is deprecated. PHP>=4.3.0 may use mysql_real_escape_string
}
return $var;
}

> 
> if I switch it to mysql_real_escape_string( and swap the order of the parameters as needed) it php errors about $db not being a resource.
> 
> I don't quite understand constructors or classes at this point in my programming experience but this qbi_mysql.php file contains this:
> ```
    function & query($sql) {
		$this->dbConn = mysqli_connect($this->host, $this->dbUser, $this->dbPass, $this->dbName);
		
        if (!$queryResource=mysqli_query($this->dbConn,$sql)) {
            trigger_error ('Query failed: '.mysqli_error($this->dbConn).' SQL: '.$sql);
		}

		return new MySQLResult($this,$queryResource);
    }
}

which might help someone suggest a solution possibly?

Here is how I rewrote this function:

	function proc_cleanData() {
		$this->config_data['tax_rate']=str_replace('%','',$this->config_data['tax_rate']);
		if (get_magic_quotes_gpc()) stripslashes_array($this->config_data);
		$this->config_data['tax_lookup']=(int)$this->config_data['tax_lookup'];
		array_walk($this->config_data,'self::array_walk_callback');
	}
	function array_walk_callback($value) {
		global $db;
		$db->prepare_input($value);
	}

Still working on rest of the script but steadily making progress. I had to type cast 'tax_lookup' field - guess I am running in strict mode and it was giving me an insert error. Hope this helps.

I was able to successfully modify and install QBI import on Zen Cart 1.5.5f. resolved all the errors and even brought to (somewhat) new standard on installing the plug-ins. I will try to find time to package it and upload it to the Plug in page in the coming weeks.

Great - thank you.

Hello,
We've got the latest version installed onto a version 1.5.6c Zencart installation. I've followed the instructions on getting it configured and I've setup and matched the products/payments/etc. I've been able to create an iif file and download orders to QuickBooks. When I import the file it will create the sales receipt like I want. It includes the payment method (Credit Card) and shipping method (US Mail) on the receipt with all the other information.

Based on what I've read it should also import payments. It does put the correct amounts into the correct accounts, but it does not mark the sales receipts as paid. When I go to customer accounts, it does not show open balances for these receipts and customers. Is there something else I need to configure to have the sales receipts marked as "Paid" so that the "Paid" stamp will print when the invoice is printed? Am I expecting something that this won't do?

Thanks for any help and suggestions!

SblantonMMP:

Hello,
We've got the latest version installed onto a version 1.5.6c Zencart installation. I've followed the instructions on getting it configured and I've setup and matched the products/payments/etc. I've been able to create an iif file and download orders to QuickBooks. When I import the file it will create the sales receipt like I want. It includes the payment method (Credit Card) and shipping method (US Mail) on the receipt with all the other information.

Based on what I've read it should also import payments. It does put the correct amounts into the correct accounts, but it does not mark the sales receipts as paid. When I go to customer accounts, it does not show open balances for these receipts and customers. Is there something else I need to configure to have the sales receipts marked as "Paid" so that the "Paid" stamp will print when the invoice is printed? Am I expecting something that this won't do?

Thanks for any help and suggestions!
Hey, this won't work not because of the data you are importing but because of QuickBooks. I import Invoices but the same deal. No matter how perfectly matched the payments are in a transaction, it just refuses to put them together.

Be prepared for lots of repeat points and clicks to first RECEIVE payments - where you receive the payment already. If you only have a few transactions it's not that bad, but I usually do mine in batches, so if you do the same, here are the steps that go the fastest for me from years of experience.

1. Create memorized report that you would use each time.
   i. Reports > Customers & Receivables > Transactioon List by Customer
   ii. Click Customize report and set Dates: to whatever range you will be using as default (last month, quarter, year, etc. - don't worry, you can change this later each time you use)
   iii. On Display tab, under columns, uncheck everything and only check Date, Name, amount fields
   iv. Filters tab: Remove all but Date, Posting Status, and add Transaction Type > Payment
   v. Click okay and save as Memorized report for future use

2. Pull up the memorised report, set date range if necessary

3. You need to developp click, keystroke routine that works for you to do this fast on multiple items in succession.
   My procedure is double-click name > Click Auto Apply Payment > Click Save & Close > Click Yes.
   Alternative is to use program called "Do It Again" which lets you capture/record mouse click procedure and then replay it multiple times to do large batches of repeat operations.

4. Deposit funds: This is trickier with automation as the payment date needs to be set manually.
   i. Home > Record Deposits (or banking Make Deposits)
   ii. Starting with topmost item: Tick checkmark > Hit Enter
   iii. On next screen set date, make sure Deposit To shows correct Deposit To account and hit Save & New
   iv. Rinse and repeat - you should only have to set correct date for consecutive deposits (umnless account also changes), so procedure is: Checkmark, Enter (optionally change deposit date) + Enter.

When I researched this years ago, there were a couple of third-party "approved" add-ons/applications that directly manipulated the database but they were outrageouslty expensive for what they id, and i think were either subscription model or per-transaction fee-based.

Good luck!