protected function _initialize($init_options = array())
{
$defaults = array('quickbooks_sql_enabled' => false, 'quickbooks_sql_schema' => realpath(dirname(__FILE__) . '/../../data/schema'), 'quickbooks_sql_debug' => false, 'quickbooks_sql_droptable' => false, 'quickbooks_sql_prefix' => QUICKBOOKS_DRIVER_SQL_PREFIX_SQL, 'quickbooks_sql_print' => false, 'quickbooks_integrator_enabled' => false, 'quickbooks_integrator_prefix' => QUICKBOOKS_DRIVER_SQL_PREFIX_INTEGRATOR);
$config = array_merge($defaults, $init_options);
// list of SQL statements to run
$arr_sql = array();
$table = $this->_mapTableName(QUICKBOOKS_DRIVER_SQL_LOGTABLE);
$def = array('quickbooks_log_id' => array(QUICKBOOKS_DRIVER_SQL_SERIAL), 'quickbooks_ticket_id' => array(QUICKBOOKS_DRIVER_SQL_INTEGER, null, 'null'), 'batch' => array(QUICKBOOKS_DRIVER_SQL_INTEGER), 'msg' => array(QUICKBOOKS_DRIVER_SQL_TEXT), 'log_datetime' => array(QUICKBOOKS_DRIVER_SQL_DATETIME));
$primary = 'quickbooks_log_id';
$keys = array('quickbooks_ticket_id', 'batch');
$uniques = array();
$arr_sql = array_merge($arr_sql, $this->_generateCreateTable($table, $def, $primary, $keys, $uniques));
$table = $this->_mapTableName(QUICKBOOKS_DRIVER_SQL_QUEUETABLE);
$def = array('quickbooks_queue_id' => array(QUICKBOOKS_DRIVER_SQL_SERIAL), 'quickbooks_ticket_id' => array(QUICKBOOKS_DRIVER_SQL_INTEGER, null, 'null'), 'qb_username' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 40), 'qb_action' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 32), 'ident' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 40), 'extra' => array(QUICKBOOKS_DRIVER_SQL_TEXT, null, 'null'), 'qbxml' => array(QUICKBOOKS_DRIVER_SQL_TEXT, null, 'null'), 'priority' => array(QUICKBOOKS_DRIVER_SQL_INTEGER, 3, 0), 'qb_status' => array(QUICKBOOKS_DRIVER_SQL_CHAR, 1), 'msg' => array(QUICKBOOKS_DRIVER_SQL_TEXT, null, 'null'), 'enqueue_datetime' => array(QUICKBOOKS_DRIVER_SQL_DATETIME), 'dequeue_datetime' => array(QUICKBOOKS_DRIVER_SQL_DATETIME, null, 'null'));
$primary = 'quickbooks_queue_id';
$keys = array('quickbooks_ticket_id', 'priority', array('qb_username', 'qb_action', 'ident', 'qb_status'), 'qb_status');
$uniques = array();
$arr_sql = array_merge($arr_sql, $this->_generateCreateTable($table, $def, $primary, $keys, $uniques));
$table = $this->_mapTableName(QUICKBOOKS_DRIVER_SQL_RECURTABLE);
$def = array('quickbooks_recur_id' => array(QUICKBOOKS_DRIVER_SQL_SERIAL), 'qb_username' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 40), 'qb_action' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 32), 'ident' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 40), 'extra' => array(QUICKBOOKS_DRIVER_SQL_TEXT, null, 'null'), 'qbxml' => array(QUICKBOOKS_DRIVER_SQL_TEXT, null, 'null'), 'priority' => array(QUICKBOOKS_DRIVER_SQL_INTEGER, 3, 0), 'run_every' => array(QUICKBOOKS_DRIVER_SQL_INTEGER), 'recur_lasttime' => array(QUICKBOOKS_DRIVER_SQL_INTEGER), 'enqueue_datetime' => array(QUICKBOOKS_DRIVER_SQL_DATETIME));
$primary = 'quickbooks_recur_id';
$keys = array(array('qb_username', 'qb_action', 'ident'), 'priority');
$uniques = array();
$arr_sql = array_merge($arr_sql, $this->_generateCreateTable($table, $def, $primary, $keys, $uniques));
$table = $this->_mapTableName(QUICKBOOKS_DRIVER_SQL_TICKETTABLE);
$def = array('quickbooks_ticket_id' => array(QUICKBOOKS_DRIVER_SQL_SERIAL), 'qb_username' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 40), 'ticket' => array(QUICKBOOKS_DRIVER_SQL_CHAR, 36), 'processed' => array(QUICKBOOKS_DRIVER_SQL_INTEGER, null, 0), 'lasterror_num' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 32, 'null'), 'lasterror_msg' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 255, 'null'), 'ipaddr' => array(QUICKBOOKS_DRIVER_SQL_CHAR, 15), 'write_datetime' => array(QUICKBOOKS_DRIVER_SQL_DATETIME), 'touch_datetime' => array(QUICKBOOKS_DRIVER_SQL_DATETIME));
$primary = 'quickbooks_ticket_id';
$keys = array('ticket');
$uniques = array();
$arr_sql = array_merge($arr_sql, $this->_generateCreateTable($table, $def, $primary, $keys, $uniques));
$table = $this->_mapTableName(QUICKBOOKS_DRIVER_SQL_USERTABLE);
$def = array('qb_username' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 40), 'qb_password' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 255), 'qb_company_file' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 255, 'null'), 'qbwc_wait_before_next_update' => array(QUICKBOOKS_DRIVER_SQL_INTEGER, null, 0), 'qbwc_min_run_every_n_seconds' => array(QUICKBOOKS_DRIVER_SQL_INTEGER, null, 0), 'status' => array(QUICKBOOKS_DRIVER_SQL_CHAR, 1), 'write_datetime' => array(QUICKBOOKS_DRIVER_SQL_DATETIME), 'touch_datetime' => array(QUICKBOOKS_DRIVER_SQL_DATETIME));
$primary = 'qb_username';
$keys = array();
$uniques = array();
$arr_sql = array_merge($arr_sql, $this->_generateCreateTable($table, $def, $primary, $keys, $uniques));
/*
$table = $this->_mapTableName(QUICKBOOKS_DRIVER_SQL_IDENTTABLE);
$def = array(
'quickbooks_ident_id' => array( QUICKBOOKS_DRIVER_SQL_SERIAL ),
'qb_username' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 40 ),
'qb_object' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 40 ),
'unique_id' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 40 ),
'qb_ident' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 40 ),
'editsequence' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 40 ),
'extra' => array( QUICKBOOKS_DRIVER_SQL_TEXT, null, 'null' ),
'map_datetime' => array( QUICKBOOKS_DRIVER_SQL_DATETIME ),
);
$primary = 'quickbooks_ident_id';
$keys = array( );
$uniques = array( array( 'qb_username', 'qb_object', 'unique_id' ) );
$arr_sql = array_merge($arr_sql, $this->_generateCreateTable($table, $def, $primary, $keys, $uniques));
*/
$table = $this->_mapTableName(QUICKBOOKS_DRIVER_SQL_CONFIGTABLE);
$def = array('quickbooks_config_id' => array(QUICKBOOKS_DRIVER_SQL_SERIAL), 'qb_username' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 40), 'module' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 40), 'cfgkey' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 40), 'cfgval' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 40), 'cfgtype' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 40), 'cfgopts' => array(QUICKBOOKS_DRIVER_SQL_TEXT, null), 'write_datetime' => array(QUICKBOOKS_DRIVER_SQL_DATETIME), 'mod_datetime' => array(QUICKBOOKS_DRIVER_SQL_DATETIME));
$primary = 'quickbooks_config_id';
$keys = array();
$uniques = array(array('qb_username', 'module', 'cfgkey'));
$arr_sql = array_merge($arr_sql, $this->_generateCreateTable($table, $def, $primary, $keys, $uniques));
/*
$table = $this->_mapTableName(QUICKBOOKS_DRIVER_SQL_NOTIFYTABLE);
$def = array(
'quickbooks_notify_id' => array( QUICKBOOKS_DRIVER_SQL_SERIAL ),
'qb_username' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 40 ),
'qb_object' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 40 ),
'unique_id' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 40 ),
'qb_ident' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 40 ),
'errnum' => array( QUICKBOOKS_DRIVER_SQL_INTEGER, null, 'null' ),
'errmsg' => array( QUICKBOOKS_DRIVER_SQL_TEXT, null ),
'note' => array( QUICKBOOKS_DRIVER_SQL_TEXT, null ),
'priority' => array( QUICKBOOKS_DRIVER_SQL_INTEGER ),
'write_datetime' => array( QUICKBOOKS_DRIVER_SQL_DATETIME ),
'mod_datetime' => array( QUICKBOOKS_DRIVER_SQL_DATETIME ),
);
$primary = 'quickbooks_notify_id';
$keys = array( );
$uniques = array( );
$arr_sql = array_merge($arr_sql, $this->_generateCreateTable($table, $def, $primary, $keys, $uniques));
*/
/*
$table = $this->_mapTableName(QUICKBOOKS_DRIVER_SQL_CONNECTIONTABLE);
$def = array(
'quickbooks_connection_id' => array( QUICKBOOKS_DRIVER_SQL_SERIAL ),
'qb_username' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 40 ),
'certificate' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 255, 'null' ),
'application_id' => array( QUICKBOOKS_DRIVER_SQL_INTEGER ),
'application_login' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 40, 'null' ),
'lasterror_num' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 32, 'null' ),
'lasterror_msg' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 255, 'null' ),
'connection_ticket' => array( QUICKBOOKS_DRIVER_SQL_VARCHAR, 255, 'null' ),
'connection_datetime' => array( QUICKBOOKS_DRIVER_SQL_DATETIME ),
'write_datetime' => array( QUICKBOOKS_DRIVER_SQL_DATETIME ),
'touch_datetime' => array( QUICKBOOKS_DRIVER_SQL_DATETIME ),
);
$primary = 'quickbooks_connection_id';
$keys = array( );
$uniques = array( );
$arr_sql = array_merge($arr_sql, $this->_generateCreateTable($table, $def, $primary, $keys, $uniques));
*/
$table = $this->_mapTableName(QUICKBOOKS_DRIVER_SQL_OAUTHTABLE);
$def = array('quickbooks_oauth_id' => array(QUICKBOOKS_DRIVER_SQL_SERIAL), 'app_username' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 255), 'app_tenant' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 255), 'oauth_request_token' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 255, 'null'), 'oauth_request_token_secret' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 255, 'null'), 'oauth_access_token' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 255, 'null'), 'oauth_access_token_secret' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 255, 'null'), 'qb_realm' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 32, 'null'), 'qb_flavor' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 12, 'null'), 'qb_user' => array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 64, 'null'), 'request_datetime' => array(QUICKBOOKS_DRIVER_SQL_DATETIME), 'access_datetime' => array(QUICKBOOKS_DRIVER_SQL_DATETIME, null, 'null'), 'touch_datetime' => array(QUICKBOOKS_DRIVER_SQL_DATETIME, null, 'null'));
$primary = 'quickbooks_oauth_id';
$keys = array();
$uniques = array(array('app_username', 'app_tenant'));
$arr_sql = array_merge($arr_sql, $this->_generateCreateTable($table, $def, $primary, $keys, $uniques));
//header('Content-Type: text/plain');
//print_r($arr_sql);
//exit;
// Support for mirroring the QuickBooks database in an SQL database
if ($config['quickbooks_sql_enabled']) {
$tables = array();
// Use the QuickBooks_SQL_Schema class
$dh = opendir($defaults['quickbooks_sql_schema']);
while (false !== ($file = readdir($dh))) {
if ($file[0] == '.' or is_dir($defaults['quickbooks_sql_schema'] . '/' . $file)) {
continue;
}
$xml = file_get_contents($defaults['quickbooks_sql_schema'] . '/' . $file);
QuickBooks_SQL_Schema::mapSchemaToSQLDefinition($xml, $tables);
// This times out on some SQL connections because it takes so darn long to generate the
// schema. Thus, we're going to issue a few useless queries here, just so we don't lose
// the connection to the database.
$this->noop();
}
// A table has to be created for each query type, and each table has to have some extra fields added to it
foreach ($tables as $table) {
// @TODO Support other transformations (table names to uppercase, field names to lowercase, etc.)
$name = strtolower($config['quickbooks_sql_prefix'] . $table[0]);
$idfield = array(QUICKBOOKS_DRIVER_SQL_SERIAL, null, 0);
$username_field = array(QUICKBOOKS_DRIVER_SQL_INTEGER, null, 'null');
$external_field = array(QUICKBOOKS_DRIVER_SQL_INTEGER, null, 'null');
$ifield = array(QUICKBOOKS_DRIVER_SQL_DATETIME, null, 'null');
// Date/time when first inserted
$ufield = array(QUICKBOOKS_DRIVER_SQL_DATETIME, null, 'null');
// Date/time when updated (re-sync from QuickBooks)
$mfield = array(QUICKBOOKS_DRIVER_SQL_TIMESTAMP, null, 'null');
// Date/time when modified by a user (needs to be pushed to QB)
$hfield = array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 40, 'null');
$qfield = array(QUICKBOOKS_DRIVER_SQL_TEXT, null, 'null');
//$dfield = array( QUICKBOOKS_DRIVER_SQL_DATETIME, null, 'null' ); // Date/time when deleted by a user (needs to be deleted from QB)
//$cfield = array( QUICKBOOKS_DRIVER_SQL_TIMESTAMP_ON_INSERT, null, 'NOW()' );
//$mfield = array( QUICKBOOKS_DRIVER_SQL_TIMESTAMP_ON_INSERT_OR_UPDATE, null, 'NOW()' );
// This should be an VARCHAR, QuickBooks errors are sometimes in the format "0x12341234"
$enfield = array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 32, 'null');
// Add/mod error number
$emfield = array(QUICKBOOKS_DRIVER_SQL_VARCHAR, 255, 'null');
// Add/mod error message
$enqfield = array(QUICKBOOKS_DRIVER_SQL_DATETIME, null, 'null');
// Add/mod enqueue date/time
$deqfield = array(QUICKBOOKS_DRIVER_SQL_DATETIME, null, 'null');
// Add/mod dequeue date/time
$audit_modified_field = array(QUICKBOOKS_DRIVER_SQL_DATETIME, null, 'null');
$audit_amount_field = array(QUICKBOOKS_DRIVER_SQL_DECIMAL, null, 'null');
$to_delete_field = array(QUICKBOOKS_DRIVER_SQL_BOOLEAN, null, 0);
// Flag it for deletion
$to_void_field = array(QUICKBOOKS_DRIVER_SQL_BOOLEAN, null, 0);
$to_skip_field = array(QUICKBOOKS_DRIVER_SQL_BOOLEAN, null, 0);
// Flag it for skipping
$to_sync_field = array(QUICKBOOKS_DRIVER_SQL_BOOLEAN, null, 0);
$flag_deleted_field = array(QUICKBOOKS_DRIVER_SQL_BOOLEAN, null, 0);
// This has been deleted within QuickBooks
$flag_voided_field = array(QUICKBOOKS_DRIVER_SQL_BOOLEAN, null, 0);
$flag_skipped_field = array(QUICKBOOKS_DRIVER_SQL_BOOLEAN, null, 0);
// This has been skipped within the sync to QuickBooks
$fields = $table[1];
$prepend = array(QUICKBOOKS_DRIVER_SQL_FIELD_ID => $idfield, QUICKBOOKS_DRIVER_SQL_FIELD_USERNAME_ID => $username_field, QUICKBOOKS_DRIVER_SQL_FIELD_EXTERNAL_ID => $external_field);
$fields = array_merge($prepend, $fields);
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_DISCOVER] = $ifield;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_RESYNC] = $ufield;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_MODIFY] = $mfield;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_HASH] = $hfield;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_QBXML] = $qfield;
//$fields[QUICKBOOKS_DRIVER_SQL_FIELD_DELETE] = $dfield;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_ERROR_NUMBER] = $enfield;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_ERROR_MESSAGE] = $emfield;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_ENQUEUE_TIME] = $enqfield;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_DEQUEUE_TIME] = $deqfield;
//$fields[QUICKBOOKS_DRIVER_SQL_FIELD_DELETED_FLAG] = $delflagfield;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_AUDIT_AMOUNT] = $audit_amount_field;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_AUDIT_MODIFIED] = $audit_modified_field;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_TO_SYNC] = $to_sync_field;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_TO_DELETE] = $to_delete_field;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_TO_SKIP] = $to_skip_field;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_TO_VOID] = $to_void_field;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_FLAG_DELETED] = $flag_deleted_field;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_FLAG_SKIPPED] = $flag_skipped_field;
$fields[QUICKBOOKS_DRIVER_SQL_FIELD_FLAG_VOIDED] = $flag_voided_field;
$primary = QUICKBOOKS_DRIVER_SQL_FIELD_ID;
//$keys = array();
//$uniques = array( $table[2] );
//$uniques = array();
$keys = $table[3];
$uniques = $table[4];
// @TODO Fix this to support unique keys
$keys = array_merge($keys, $uniques);
/*
print('keys: ');
print_r($keys);
print("\n\n");
print('uniques: ');
print_r($uniques);
exit;
*/
$arr_sql = array_merge($arr_sql, $this->_generateCreateTable($name, $fields, $primary, $keys));
}
}
// Run each CREATE TABLE statement...
foreach ($arr_sql as $sql) {
if ($config['quickbooks_sql_debug'] or $config['quickbooks_sql_print']) {
print $sql . "\n\n";
} else {
$errnum = 0;
$errmsg = '';
//print($sql);
$this->_query($sql, $errnum, $errmsg);
}
}
//exit;
}
