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; }