mysql — Редактирование инвентарного количества QuickBooks для Windows под рукой с помощью QuickBooks PHP Dev Kit / QuickBooks Web Connector

Резюме

Поэтому я настроил example_mysql_mirror и заставил все это работать, но по какой-то причине он всегда перезаписывает изменения в MySQL независимо от qbsql_modify_timestamp. Я заметил зеркальную вики-страницу http://www.consolibyte.com/wiki/doku.php/quickbooks_integration_php_consolibyte_sqlmirror ссылается на другое поле (qbsql_modify_datetime), но просто записывает это как внутренние изменения, которые никогда не менялись в вики. Я обновляю таблицу «qb_iteminventory» и ключ «QuantityOnHand» для любых / всех строк.

Моя конечная цель — иметь возможность обновлять количество инвентаря для предметов. Зеркальный пример был просто первым рабочим примером того, что это не только возможно, но и легко сделать (вероятно, по той же причине, по которой он был создан).

После дальнейших раскопок я пришел к нескольким выводам, один из которых заключается в том, что PHP Dev Kit QuickBooks пример_mysql_mirror.php не является полным. Я видел ПРЕДУПРЕЖДЕНИЕ, и я использую последний код GitHub.

Я чувствую, что это проблема со временем (внутренняя обработка qbsql_modify_timestamp) или проблема с родителем (qb_iteminventory дочерний элемент / дочерний элемент родительского элемента, который также должен быть обновлен и т. Д.), Или даже отсутствующее поле (я заметил количествоOnHand, но сделал в MySQL не видят Количество Доступных. Возможно, требуется использовать другой отчет для обмена запасами QB (не уверен, нужно ли это?).

КОД

example_mysql_mirror.php

// 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 dirname(__FILE__) . '/../../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/Los_Angeles');
}
// The username and password the Web Connector will use to connect with
$username = '';
$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 = 'mysqli://:@localhost/spray_quickbooks';
// 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,
'deny_reallyfast_logins' => false,
);
//
$driver_options = array();
$ops = array(
QUICKBOOKS_OBJECT_SALESTAXITEM,
QUICKBOOKS_OBJECT_SALESTAXCODE,
QUICKBOOKS_OBJECT_CUSTOMER,
QUICKBOOKS_OBJECT_VENDOR,

QUICKBOOKS_OBJECT_TEMPLATE,

QUICKBOOKS_OBJECT_CUSTOMERTYPE,
QUICKBOOKS_OBJECT_VENDORTYPE,
QUICKBOOKS_OBJECT_ESTIMATE,
QUICKBOOKS_OBJECT_INVOICE,
QUICKBOOKS_OBJECT_CLASS,

QUICKBOOKS_OBJECT_INVOICE,

QUICKBOOKS_OBJECT_INVENTORYITEM,

/* Not quite sure why these are not being used
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);

save.php

<?php
if (count($_POST) == 0) {
dErr("There is nothing to save. Try again later.");
}

// Compile list of rows to update
$updates = array();
foreach ($_POST as $key => $value) {
array_push($updates, array(explode('_', $key)[1], $value));
}
unset($key);
unset($value);

// TODO: Database updating
// IMPORTANT: Add validation and other selective functionality before adding saving
$qb = new mysqli('127.0.0.1', '', '', 'spray_quickbooks');

// Oh no! A connect_errno exists so the connection attempt failed!
if ($qb->connect_errno) {
dErr("Error: Failed to make a MySQL connection, here is why: <br />Errno: " . $qb->connect_errno . "<br />Error: " . $qb->connect_error);
}

$qbe = new mysqli('127.0.0.1', '', '', 'spray_qb_extras');

// Oh no! A connect_errno exists so the connection attempt failed!
if ($qbe->connect_errno) {
dErr("Error: Failed to make a MySQL connection, here is why: <br />Errno: " . $qbe->connect_errno . "<br />Error: " . $qbe->connect_error);
}

foreach ($updates as $update) {
// Perform an SQL query
$sql = "UPDATE qb_iteminventory SET QuantityOnHand='" . $update[1] . "' WHERE qbsql_id='" . $update[0] . "'";
if (!$qb_result = $qb->query($sql)) {
dErr("Error: Our query failed to execute and here is why: <br />Query: " . $sql . "<br />Errno: " . $qb->errno . "<br />Error: " . $qb->error);
}
}
unset($updates);
unset($update);

// Redirect back to where they came from
echo "<meta http-equiv=\"refresh\" content=\"0;url=".$_SERVER['HTTP_REFERER']."\"/>";

// Extra Functions
function dErr($msg) {
echo "<center><b>Sorry, we have encountered an error.</b><br /><br />";
echo $msg;
echo "</center>";
exit;
}
?>

Обновлен save.php (Инвентаризация корректировки)

<?php
if (count($_POST) == 0) {
dErr("There is nothing to save. Please try again later.");
}

// Compile list of rows to update
$updates = array();
foreach ($_POST as $key => $value) {
array_push($updates, array(explode('_', $key)[1], $value));
}
/*unset($key);
unset($value);*/

// IMPORTANT: Add validation and other selective functionality before adding saving
//            and stop saving unchanged items just because we can
$qb = new mysqli('127.0.0.1', '', '', 'spray_quickbooks');

// Oh no! A connect_errno exists so the connection attempt failed!
if ($qb->connect_errno) {
dErr("Error: Failed to make a MySQL connection, here is why: <br />Errno: " . $qb->connect_errno . "<br />Error: " . $qb->connect_error);
}

// IMPORTANT: ONLY UPDATE CHANGED ROWS. WE DONT WANT INVENTORY ADJUSTMENTS FOR UNCHANGED ITEMS!
foreach ($updates as $update) {
// Update QuantityOnHand still so our web interface can easily see the new quantity before QB sync
$sql = "UPDATE qb_iteminventory SET QuantityOnHand='" . $update[1] . "' WHERE qbsql_id='" . $update[0] . "'";
if (!$qb_result = $qb->query($sql)) {
dErr("Error: Our query failed to execute and here is why: <br />Query: " . $sql . "<br />Errno: " . $qb->errno . "<br />Error: " . $qb->error);
}

// Get a newly updated item so we can extract Item's FullName
$sql = "SELECT * FROM qb_iteminventory WHERE qbsql_id='" . $update[0] . "'";
if (!$qb_result = $qb->query($sql)) {
dErr("Error: Our query failed to execute and here is why: <br />Query: " . $sql . "<br />Errno: " . $qbe->errno . "<br />Error: " . $qbe->error);
}

$row = mysqli_fetch_assoc($qb_result);
//print_r($row);

// Generate unique TxnID
// Apparently QuickBooks will overwrite it with the permanent TxnID when it syncs
$tID = rand(1000, 9999);

// Insert new Item Adjustment
$sql = "INSERT INTO `qb_inventoryadjustment` ( `TxnID`, `TimeCreated`, `TimeModified`,  `Account_FullName`, `TxnDate`, `RefNumber`,  `Memo`, `qbsql_discov_datetime`, `qbsql_resync_datetime`, `qbsql_modify_timestamp` ) VALUES ( 'TxnID-" . $tID . "', now(), now(),  'Inventory Adjustments', CURDATE(), '" . $tID . "', NULL, NULL, NULL, now() )";
if (!$qb_result = $qb->query($sql)) {
dErr("Error: Our query failed to execute and here is why: <br />Query: " . $sql . "<br />Errno: " . $qb->errno . "<br />Error: " . $qb->error);
}

// Insert new Item Adjustment Line
$sql = "INSERT INTO `qb_inventoryadjustment_inventoryadjustmentline` ( `InventoryAdjustment_TxnID`, `SortOrder`, `TxnLineID`, `Item_FullName`, `QuantityAdjustment_NewQuantity` ) VALUES ( 'TxnID-" . $tID . "', '0', 'TxnLID-" . $tID . "', '" . $row['FullName'] . "', " . $update[1] . ");";
if (!$qb_result = $qb->query($sql)) {
dErr("Error: Our query failed to execute and here is why: <br />Query: " . $sql . "<br />Errno: " . $qb->errno . "<br />Error: " . $qb->error);
}
}

// TODO: Research whether this is really required, and to what extent
// INFO: Not sure why I feel like this is important
/*$qb_result->free();
$qb->close();
unset($updates);
unset($update);
unset($sql);*/

// Redirect back to where they came from
//echo "<meta http-equiv=\"refresh\" content=\"0;url=".$_SERVER['HTTP_REFERER']."\"/>";

// Extra Functions
function dErr($msg) {
echo "<center><b>Sorry, we have encountered an error.</b><br /><br />";
echo $msg;
echo "</center>";
exit;
}
?>