douchen9569
2016-01-08 19:27 阅读 59
已采纳

Quickbooks Windows Sync与PHP MySQL镜像

At bit of foreground knowledge about me and my predicament:

  • I am familiar with PHP and MYSQL
  • I have built a full ecommerce site from scratch (globalsupplies.ca)
  • I now want our large Quickbooks setup to be accessible on the web for inventory info and making invoices on receiving an order among other things.
  • I have chosen the Consolibyte PHP Dev Kit for this task because it was free and quite popular
  • For method of simplicity I want to have a DB Sync to MYSQL from our Quickbooks file
  • For testing I am using a test quickbooks company
  • I have a server with ssl
  • I have spent about 10 hours on this with little to no progress

Ok. So now the issue: I have the web connector up and running and it points to this file (edited to remove the sensitive stuff)

<?php

/**
 * An example of how to mirror parts (or all of) the QuickBooks database to a MySQL database
 * 
 * 
 * *REALLY FREAKING IMPORTANT* WARNING WARNING WARNING WARNING
 * 
 * THE SQL MIRROR CODE IS BETA CODE, AND IS KNOWN TO HAVE BUGS!
 * 
 * With that said:
 * - If you're planning on using it in a production environment, you better be ready to do a lot of testing and debugging.
 * - Use a nightly build. There are known problems with the mirror code in the v1.5.2 or v1.5.3 releases of the code.
 * - There is absolutely no way I can troubleshoot problems for you without you posting your code. Dumps of the quickbooks_queue and quickbooks_log tables and/or phpMyAdmin access to your MySQL database is helpful also.
 * 
 * Nightly builds are available here:
 * https://code.intuit.com/sf/frs/do/viewRelease/projects.php_devkit/frs.php_devkit.latest_sources
 * 
 * IN ALL LIKELIHOOD, YOU SHOULD *NOT* BE USING THIS CODE. YOU SHOULD INSTEAD 
 * LOOK AT THE FOLLOWING SCRIPTS AND IMPLEMENT YOUR REQUEST/RESPONSE HANDLERS 
 * YOURSELF:
 *     docs/example_web_connector.php
 *     docs/example_web_connector_import.php
 * 
 * *REALLY FREAKING IMPORTANT* WARNING WARNING WARNING WARNING
 *
 *  
 * The SQL mirror functionality makes it easy to extract information from 
 * QuickBooks into an SQL database, and, if so desired, write changes to the 
 * SQL records back to QuickBooks automatically. 
 * 
 * You should look at my wiki for more information about mirroring QuickBooks 
 * data into SQL databases:
 *     http://wiki.consolibyte.com/wiki/doku.php/quickbooks_integration_php_consolibyte_sqlmirror
 * 
 * You should also read this forum post before even thinking about using this:
 *     http://consolibyte.com/forum/viewtopic.php?id=20
 * 
 * 
 * @package QuickBooks
 * @subpackage Documentation
 */

// I always program in E_STRICT error mode with error reporting turned on... 
error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', 1);

// Set the include path
require_once '../QuickBooks.php';

// You should make sure this matches the time-zone QuickBooks is running in
if (function_exists('date_default_timezone_set'))
{
    date_default_timezone_set('America/Edmonton');
}

// The username and password the Web Connector will use to connect with
$username = 'quickbooks';
$password = 'password';

// Database connection string
//
// You *MUST* start with a fresh database! If the database you use has any 
//    quickbooks_* or qb_* related tables in it, then the schema *WILL NOT* build 
//    correctly! 
//     
//     Currently, only MySQL is supported/tested. 
$dsn = 'my string removed';

// If the database has not been initialized, we need to initialize it (create 
//    schema and set up the username/password, etc.)
if (!QuickBooks_Utilities::initialized($dsn))
{
    header('Content-Type: text/plain');

    // It takes a really long time to build the schema... 
    set_time_limit(0);

    $driver_options = array(
        );

    $init_options = array(
        'quickbooks_sql_enabled' => true, 
        );

    QuickBooks_Utilities::initialize($dsn, $driver_options, $init_options);
    QuickBooks_Utilities::createUser($dsn, $username, $password);

    exit;
}

// What mode do we want to run the mirror in? 
//$mode = QuickBooks_WebConnector_Server_SQL::MODE_READONLY;        // Read from QuickBooks only (no data will be pushed back to QuickBooks)
//$mode = QuickBooks_WebConnector_Server_SQL::MODE_WRITEONLY;        // Write to QuickBooks only (no data will be copied into the SQL database)
$mode = QuickBooks_WebConnector_Server_SQL::MODE_READWRITE;        // Keep both QuickBooks and the database in sync, reading and writing changes back and forth)

// What should we do if a conflict is found? (a record has been changed by another user or process that we're trying to update)
$conflicts = QuickBooks_WebConnector_Server_SQL::CONFLICT_LOG;

// What should we do with records deleted from QuickBooks? 
$delete = QuickBooks_WebConnector_Server_SQL::DELETE_REMOVE;        // Delete the record from the database too
//$delete = QuickBooks_WebConnector_Server_SQL::DELETE_FLAG;         // Just flag it as deleted

// Hooks (optional stuff)
$hooks = array();

/*
// Hooks (optional stuff)
$hook_obj = new MyHookClass2('Keith Palmer');

$hooks = array(

    // Register a hook which occurs when we perform an INSERT into the SQL database for a record from QuickBooks
    // QuickBooks_SQL::HOOK_SQL_INSERT => 'my_function_name_for_inserts', 
    // QuickBooks_SQL::HOOK_SQL_INSERT => 'MyHookClass::myMethod',

    // Register a hook which occurs when we perform an UPDATE on the SQL database for a record from QuickBooks
    // QuickBooks_SQL::HOOK_SQL_UPDATE => 'my_function_name_for_updates',

    // Example of registering multiple hooks for one hook type 
    // QuickBooks_SQL::HOOK_PREHANDLE => array(
    //    'my_prehandle_function',
    //    array( $hook_obj, 'myMethod' ),
    //    ),

    // Example of using the hook factory to use a pre-defined hook
    // QuickBooks_SQL::HOOK_SQL_INSERT => QuickBooks_Hook_Factory::create(
    //    'Relay_POST',                                 // Relay the hook data to a remote URL via a HTTP POST
    //    'http://localhost:8888/your_script.php'),

    QuickBooks_SQL::SQL_INSERT => array(
        QuickBooks_Hook_Factory::create(
            'Relay_POST', 
            'http://localhost:8888/your_script.php', 
            array( '_secret' => 'J03lsN3at@pplication' ) ), 
        ), 
    );

class MyHookClass
{
    static public function myMethod($requestID, $user, $hook, &$err, $hook_data, $callback_config)
    {
        // do something here...
        return true;
    }
}

function my_prehandle_function($requestID, $user, $hook, &$err, $hook_data, $callback_config)
{
    //print('here we are!');
    return true;
}

class MyHookClass2
{
    protected $_var;

    public function __construct($var)
    {
        $this->_var = $var;
    }

    public function myMethod($requestID, $user, $hook, &$err, $hook_data, $callback_config)
    {
        //print('variable equals: ' . $this->_var);
        return true;
    }
}
*/

// 
$soap_options = array();

// 
$handler_options = array(
    'deny_concurrent_logins' => false,
    );

// 
$driver_options = array();

$ops = array(
    QUICKBOOKS_OBJECT_SALESTAXITEM, 
    QUICKBOOKS_OBJECT_SALESTAXCODE, 
    QUICKBOOKS_OBJECT_CUSTOMER, 
    QUICKBOOKS_OBJECT_VENDOR, 

    QUICKBOOKS_OBJECT_INVENTORYITEM, 

    QUICKBOOKS_OBJECT_TEMPLATE, 

    QUICKBOOKS_OBJECT_CUSTOMERTYPE, 
    QUICKBOOKS_OBJECT_VENDORTYPE, 
    QUICKBOOKS_OBJECT_ESTIMATE, 
    QUICKBOOKS_OBJECT_INVOICE, 
    QUICKBOOKS_OBJECT_CLASS, 

    QUICKBOOKS_OBJECT_INVOICE, 

    /*
    QUICKBOOKS_OBJECT_INVENTORYITEM, 
    QUICKBOOKS_OBJECT_NONINVENTORYITEM, 
    QUICKBOOKS_OBJECT_SERVICEITEM, 
    QUICKBOOKS_OBJECT_SHIPMETHOD, 
    QUICKBOOKS_OBJECT_PAYMENTMETHOD, 
    QUICKBOOKS_OBJECT_TERMS, 
    QUICKBOOKS_OBJECT_PRICELEVEL, 
    QUICKBOOKS_OBJECT_ITEM, 
    */

    QUICKBOOKS_OBJECT_PAYMENTMETHOD, 

    QUICKBOOKS_OBJECT_COMPANY, 
    QUICKBOOKS_OBJECT_HOST, 
    QUICKBOOKS_OBJECT_PREFERENCES,
    );

$ops_misc = array(        // For fetching inventory levels, deleted transactions, etc. 
    QUICKBOOKS_DERIVE_INVENTORYLEVELS,
    QUICKBOOKS_QUERY_DELETEDLISTS,
    QUICKBOOKS_QUERY_DELETEDTRANSACTIONS,
    // 'nothing', 
    );

// 
$sql_options = array(
    'only_import' => $ops,
    'only_add' => $ops, 
    'only_modify' => $ops, 
    'only_misc' => $ops_misc, 
    );

// 
$callback_options = array();

// $dsn_or_conn, $how_often, $mode, $conflicts, $users = null, 
//    $map = array(), $onerror = array(), $hooks = array(), $log_level, $soap = QUICKBOOKS_SOAPSERVER_BUILTIN, $wsdl = QUICKBOOKS_WSDL, $soap_options = array(), $handler_options = array(), $driver_options = array()
$Server = new QuickBooks_WebConnector_Server_SQL(
    $dsn, 
    '1 minute', 
    $mode, 
    $conflicts, 
    $delete,
    $username, 
    array(), 
    array(), 
    $hooks, 
    QUICKBOOKS_LOG_DEVELOP, 
    QUICKBOOKS_SOAPSERVER_BUILTIN, 
    QUICKBOOKS_WSDL,
    $soap_options, 
    $handler_options, 
    $driver_options,
    $sql_options, 
    $callback_options);
$Server->handle(true, true);

When I run it the first time it loads for a while and makes tables in my DB. Then it says:

Version:
PHP QuickBooks SOAP Server v2.0 at /docs/example_mysql_mirror.php

Message:
Invalid password for username: quickbooks

Description:
QBWC1040: Web connector did not provide a valid password for the given username. Job ending.

If I look in the DB it is not the 140 tables that the docs says I have and the quickbooks_user table is empty. My password is put in correct in Quickbooks Web Connector. I am at a loss, I am out of ideas and a google search tells me to modify the users table but it is empty. Anything will help. -Evan

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    doulun1915 doulun1915 2016-01-09 13:13

    For method of simplicity I want to have a DB Sync to MYSQL from our Quickbooks file

    This is the absolutely most complex setup you could have chosen. Having the two DBs sync is NOT simple, by any means. If you want simplicity, DO NOT use the SQL mirror and attempt to sync QuickBooks with an SQL database.

    This is an extremely complex thing to do, for a number of reasons:

    • The code to sync the two is BETA -- hence the big warning in the comments: "THE SQL MIRROR CODE IS BETA CODE, AND IS KNOWN TO HAVE BUGS!"
    • QuickBooks isn't SQL-based at all, and has only a somewhat limited XML-based API, so there really isn't any way to fully sync with QuickBooks cleanly -- a lot of translation has to happen
    • The QuickBooks API doesn't even allow access to all data it contains, so it's tough to get a fully up-to-date and reliable sync

    If you want a simple and reliable approach, follow the quick-start guide:

    If I look in the DB it is not the 140 tables that the docs says I have

    Then something went wrong. Remember, it's beta code. If you have your server properly configured to log PHP/SQL/web server errors, then you can check your error log and find out what the issue is.

    the quickbooks_user table is empty

    This is because something went wrong, per my above comment. You need to drop the tables and run it again, and check your error logs to figure out what's wrong.

    点赞 评论 复制链接分享

相关推荐