r16933 - gnucash/branches/gda-dev2/src/backend/gda - 1) Redo query to calculate balances by querying for all accounts at the
Phil Longstaff
plongstaff at cvs.gnucash.org
Sat Feb 9 20:00:48 EST 2008
Author: plongstaff
Date: 2008-02-09 20:00:47 -0500 (Sat, 09 Feb 2008)
New Revision: 16933
Trac: http://svn.gnucash.org/trac/changeset/16933
Modified:
gnucash/branches/gda-dev2/src/backend/gda/gnc-account-gda.c
gnucash/branches/gda-dev2/src/backend/gda/gnc-backend-gda.c
gnucash/branches/gda-dev2/src/backend/gda/gnc-backend-util-gda.c
gnucash/branches/gda-dev2/src/backend/gda/gnc-backend-util-gda.h
gnucash/branches/gda-dev2/src/backend/gda/gnc-slots-gda.c
gnucash/branches/gda-dev2/src/backend/gda/gnc-transaction-gda.c
gnucash/branches/gda-dev2/src/backend/gda/gnc-transaction-gda.h
Log:
1) Redo query to calculate balances by querying for all accounts at the
same time (using SUM() function)
2) Fix bug where gda://abc was not caught as an invalid URL
Modified: gnucash/branches/gda-dev2/src/backend/gda/gnc-account-gda.c
===================================================================
--- gnucash/branches/gda-dev2/src/backend/gda/gnc-account-gda.c 2008-02-08 22:56:03 UTC (rev 16932)
+++ gnucash/branches/gda-dev2/src/backend/gda/gnc-account-gda.c 2008-02-10 01:00:47 UTC (rev 16933)
@@ -154,6 +154,27 @@
}
static void
+load_account_balances_for_list( GncGdaBackend* be, GList* list )
+{
+ GList* balance_list;
+
+ g_return_if_fail( be != NULL );
+
+ if( list == NULL ) return;
+
+ balance_list = gnc_gda_get_account_balances_for_list( be, list );
+ for( ; balance_list != NULL; balance_list = balance_list->next ) {
+ acct_balances_t* acct_balances = (acct_balances_t*)balance_list->data;
+
+ g_object_set( acct_balances->acct,
+ "end-balance", &acct_balances->start_balance,
+ "end-cleared-balance", &acct_balances->cleared_balance,
+ "end-reconciled-balance", &acct_balances->reconciled_balance,
+ NULL);
+ }
+}
+
+static void
load_single_account( GncGdaBackend* be, GdaDataModel* pModel, int row, GList** pList,
GList** l_accounts_needing_parents )
{
@@ -176,7 +197,7 @@
gnc_gda_load_object( be, pModel, row, GNC_ID_ACCOUNT, pAccount, col_table );
*pList = g_list_append( *pList, pAccount );
// gnc_gda_slots_load( be, QOF_INSTANCE(pAccount) );
- load_balances( be, pAccount );
+// load_balances( be, pAccount );
qof_instance_mark_clean( QOF_INSTANCE(pAccount) );
@@ -222,6 +243,7 @@
}
if( list != NULL ) {
+ load_account_balances_for_list( be, list );
gnc_gda_slots_load_for_list( be, list );
}
Modified: gnucash/branches/gda-dev2/src/backend/gda/gnc-backend-gda.c
===================================================================
--- gnucash/branches/gda-dev2/src/backend/gda/gnc-backend-gda.c 2008-02-08 22:56:03 UTC (rev 16932)
+++ gnucash/branches/gda-dev2/src/backend/gda/gnc-backend-gda.c 2008-02-10 01:00:47 UTC (rev 16933)
@@ -85,6 +85,7 @@
static QofLogModule log_module = G_LOG_DOMAIN;
#define SQLITE_PROVIDER_NAME "SQLite"
+#define URI_PREFIX "gda://"
/* ================================================================= */
@@ -102,6 +103,114 @@
}
}
+/*
+ * Parse the gda uri. If successful, return TRUE.
+ */
+
+static gboolean parse_uri( const gchar* book_id,
+ gchar** pProvider, gchar** pDsn, gchar** pUsername, gchar** pPassword )
+{
+ gchar* uri_id;
+ gchar* book_info;
+ gchar* provider;
+ GList* provider_list;
+ gboolean provider_found;
+ gchar* dsn;
+
+ *pProvider = NULL;
+ *pDsn = NULL;
+ *pUsername = NULL;
+ *pPassword = NULL;
+
+ book_info = g_strdup( book_id );
+ uri_id = book_info;
+
+ /* If there is no gda:// prefix, we just have a sqlite file name.
+ * Otherwise, the string will be one of:
+ *
+ * sqlite:<filename>
+ * mysql:<dbname>
+ * pgsql:<dbname>
+ * @<gda_connectionname>
+ */
+ if( g_str_has_prefix( uri_id, URI_PREFIX ) ) {
+ uri_id += strlen( URI_PREFIX );
+
+ if( uri_id[0] == '@' ) {
+ *pDsn = g_strdup( &uri_id[1] );
+ g_free( book_info );
+ return TRUE;
+ }
+
+ provider = uri_id;
+ dsn = strchr( uri_id, ':' );
+ if( dsn == 0 ) {
+ g_free( book_info );
+ return FALSE;
+ }
+ *dsn = '\0';
+ dsn++;
+
+ } else {
+ provider = SQLITE_PROVIDER_NAME;
+ dsn = uri_id;
+ }
+
+ // Get a list of all of the providers. If the requested provider is on the list, use it.
+ // Note that we need a case insensitive comparison here
+ provider_list = gda_config_get_provider_list();
+
+ provider_found = FALSE;
+ for( ; provider_list != NULL; provider_list = provider_list->next ) {
+ GdaProviderInfo* provider_info = (GdaProviderInfo*)provider_list->data;
+
+ if( provider_info != NULL && g_ascii_strcasecmp( provider_info->id, provider ) == 0 ) {
+ provider_found = TRUE;
+ provider = provider_info->id;
+ break;
+ }
+ }
+ if( provider_found ) {
+ gchar* cnc;
+
+ *pProvider = g_strdup( provider );
+
+ // If the provider is SQLite, split the file name into DB_DIR and
+ // DB_NAME
+ if( strcmp( provider, SQLITE_PROVIDER_NAME ) == 0 ) {
+ gchar* dirname;
+ gchar* basename;
+
+ dirname = g_path_get_dirname( dsn );
+
+ basename = g_path_get_basename( dsn );
+
+ // Remove .db from the base name if it exists
+ if( g_str_has_suffix( basename, ".db" ) ) {
+ gchar* bn = g_strdup( basename );
+ gchar* suffix = g_strrstr( bn, ".db" );
+ *suffix = '\0';
+
+ cnc = g_strdup_printf( "DB_DIR=%s;DB_NAME=%s", dirname, bn );
+ g_free( bn );
+ } else {
+ cnc = g_strdup_printf( "DB_DIR=%s;DB_NAME=%s",
+ dirname, basename );
+ }
+ g_free( dirname );
+ g_free( basename );
+ } else {
+ cnc = g_strdup( dsn );
+ }
+ *pDsn = cnc;
+ g_free( book_info );
+ return TRUE;
+ } else {
+ g_free( book_info );
+ return FALSE;
+ }
+}
+
static void
gnc_gda_session_begin( QofBackend *be_start, QofSession *session,
const gchar *book_id,
@@ -110,10 +219,11 @@
{
GncGdaBackend *be = (GncGdaBackend*)be_start;
GError* error = NULL;
- gchar* book_info;
gchar* dsn;
- gchar* username = NULL;
- gchar* password = NULL;
+ gchar* username;
+ gchar* password;
+ gchar* provider;
+ gboolean uriOK;
g_return_if_fail( be_start != NULL );
g_return_if_fail( session != NULL );
@@ -128,125 +238,57 @@
/* Split book_id into provider and connection string. If there's no
provider, use "file" */
- book_info = g_strdup( book_id );
- dsn = strchr( book_info, ':' );
- if( dsn != NULL && *(dsn+1)==*(dsn+2) && *(dsn+1)=='/' ) {
- *dsn = '\0';
- dsn += 3; // Skip '://'
+ uriOK = parse_uri( book_id, &provider, &dsn, &username, &password );
+ if( !uriOK ) {
+ qof_backend_set_error( be_start, ERR_BACKEND_BAD_URL );
- // String will be one of:
- //
- // sqlite:<filename>
- // mysql:<dbname>
- // pgsql:<dbname>
- // @<gda_connectionname>
+ LEAVE( " " );
+ return;
+ }
- if( dsn[0] == '@' ) {
- be->pConnection = gda_client_open_connection( be->pClient,
- &dsn[1],
+ if( provider == NULL ) {
+ be->pConnection = gda_client_open_connection( be->pClient,
+ dsn,
username, password,
0,
&error );
- }
} else {
- dsn = NULL;
- }
-
- if( dsn == NULL || dsn[0] != '@' ) {
- gchar* provider;
- GList* provider_list;
- GList* l;
- gboolean provider_found;
-
- if( dsn != NULL ) {
- provider = dsn;
- dsn = strchr( dsn, ':' );
- *dsn = '\0';
- dsn++;
- } else {
- provider = SQLITE_PROVIDER_NAME;
- dsn = book_info;
- }
-
- // Get a list of all of the providers. If the requested provider is on the list, use it.
- // Note that we need a case insensitive comparison here
- provider_list = gda_config_get_provider_list();
-
- provider_found = FALSE;
- for( l = provider_list; l != NULL; l = l->next ) {
- GdaProviderInfo* provider_info = (GdaProviderInfo*)l->data;
-
- if( provider_info != NULL && g_ascii_strcasecmp( provider_info->id, provider ) == 0 ) {
- provider_found = TRUE;
- provider = provider_info->id;
- break;
- }
- }
-
- if( provider_found ) {
- gchar* cnc;
-
- // If the provider is SQLite, split the file name into DB_DIR and
- // DB_NAME
- if( strcmp( provider, SQLITE_PROVIDER_NAME ) == 0 ) {
- gchar* dirname;
- gchar* basename;
-
- dirname = g_path_get_dirname( dsn );
- basename = g_path_get_basename( dsn );
-
- // Remove .db from the base name if it exists
- if( g_str_has_suffix( basename, ".db" ) ) {
- gchar* bn = g_strdup( basename );
- gchar* suffix = g_strrstr( bn, ".db" );
- *suffix = '\0';
-
- cnc = g_strdup_printf( "DB_DIR=%s;DB_NAME=%s", dirname, bn );
- g_free( bn );
- } else {
- cnc = g_strdup_printf( "DB_DIR=%s;DB_NAME=%s",
- dirname, basename );
- }
- g_free( dirname );
- g_free( basename );
- } else {
- cnc = g_strdup( dsn );
- }
-
- be->pConnection = gda_client_open_connection_from_string( be->pClient,
+ be->pConnection = gda_client_open_connection_from_string( be->pClient,
provider,
- cnc,
+ dsn,
username, password,
0,
&error );
- if( be->pConnection == NULL ) {
- GdaServerOperation* op = gda_client_prepare_create_database(
+ if( be->pConnection == NULL ) {
+ GdaServerOperation* op = gda_client_prepare_create_database(
be->pClient,
dsn,
provider );
- if( op != NULL ) {
- gboolean isOK;
- isOK = gda_client_perform_create_database(
+ if( op != NULL ) {
+ gboolean isOK;
+ isOK = gda_client_perform_create_database(
be->pClient,
op,
&error );
- if( isOK ) {
- be->pConnection = gda_client_open_connection_from_string(
+ if( isOK ) {
+ be->pConnection = gda_client_open_connection_from_string(
be->pClient,
provider,
- cnc,
+ dsn,
username, password,
0,
&error );
- }
}
}
- g_free( cnc );
}
}
- g_free( book_info );
+ if( provider != NULL ) g_free( provider );
+ if( dsn != NULL ) g_free( dsn );
+ if( username != NULL ) g_free( username );
+ if( password != NULL ) g_free( password );
+
if( be->pConnection == NULL ) {
PERR( "SQL error: %s\n", error->message );
qof_backend_set_error( be_start, ERR_BACKEND_NO_SUCH_DB );
@@ -261,6 +303,7 @@
be->pDict = gda_dict_new();
gda_dict_set_connection( be->pDict, be->pConnection );
gda_dict_update_dbms_meta_data( be->pDict, 0, NULL, &error );
+ gda_dict_extend_with_functions( be->pDict );
if( error != NULL ) {
PERR( "gda_dict_update_dbms_meta_data() error: %s\n", error->message );
}
Modified: gnucash/branches/gda-dev2/src/backend/gda/gnc-backend-util-gda.c
===================================================================
--- gnucash/branches/gda-dev2/src/backend/gda/gnc-backend-util-gda.c 2008-02-08 22:56:03 UTC (rev 16932)
+++ gnucash/branches/gda-dev2/src/backend/gda/gnc-backend-util-gda.c 2008-02-10 01:00:47 UTC (rev 16933)
@@ -1474,42 +1474,39 @@
return query;
}
-GdaObject*
+GdaDataModel*
gnc_gda_execute_sql( const GncGdaBackend* be, const gchar* sql )
{
- GdaQuery* query;
+ GdaCommand* cmd;
+ GError* error = NULL;
+ GdaDataModel* model;
g_return_val_if_fail( be != NULL, NULL );
g_return_val_if_fail( sql != NULL, NULL );
- query = gnc_gda_create_query_from_sql( be, sql );
- if( query != NULL ) {
- return gnc_gda_execute_query( be, query );
- } else {
- return NULL;
- }
+ cmd = gda_command_new( sql, GDA_COMMAND_TYPE_SQL, 0 );
+ model = gda_connection_execute_select_command( be->pConnection, cmd, NULL, &error );
+ if( error != NULL ) {
+ PERR( "SQL error: %s\n", error->message );
+ }
+
+ return model;
}
int
gnc_gda_execute_select_get_count( const GncGdaBackend* be, const gchar* sql )
{
- GError* error = NULL;
int count = 0;
- GdaObject* ret;
+ GdaDataModel* model;
g_return_val_if_fail( be != NULL, 0 );
g_return_val_if_fail( sql != NULL, 0 );
- ret = gnc_gda_execute_sql( be, sql );
- if( GDA_IS_DATA_MODEL(ret) ) {
- GdaDataModel* pModel = GDA_DATA_MODEL(ret);
- count = gda_data_model_get_n_rows( pModel );
+ model = gnc_gda_execute_sql( be, sql );
+ if( model != NULL ) {
+ count = gda_data_model_get_n_rows( model );
}
- if( error != NULL ) {
- PERR( "SQL error: %s\n", error->message );
- }
-
return count;
}
@@ -1530,6 +1527,30 @@
return count;
}
+
+void
+gnc_gda_append_guid_list_to_sql( GString* sql, GList* list )
+{
+ gchar guid_buf[GUID_ENCODING_LENGTH+1];
+ gboolean first_guid = TRUE;
+
+ g_return_if_fail( sql != NULL );
+
+ if( list == NULL ) return;
+
+ for( ; list != NULL; list = list->next ) {
+ QofInstance* inst = QOF_INSTANCE(list->data);
+ guid_to_string_buff( qof_instance_get_guid( inst ), guid_buf );
+
+ if( !first_guid ) {
+ g_string_append( sql, "," );
+ }
+ g_string_append( sql, "'" );
+ g_string_append( sql, guid_buf );
+ g_string_append( sql, "'" );
+ first_guid = FALSE;
+ }
+}
/* ================================================================= */
static void
get_col_gvalue_for_query( GncGdaBackend* be, QofIdTypeConst obj_name,
Modified: gnucash/branches/gda-dev2/src/backend/gda/gnc-backend-util-gda.h
===================================================================
--- gnucash/branches/gda-dev2/src/backend/gda/gnc-backend-util-gda.h 2008-02-08 22:56:03 UTC (rev 16932)
+++ gnucash/branches/gda-dev2/src/backend/gda/gnc-backend-util-gda.h 2008-02-10 01:00:47 UTC (rev 16933)
@@ -164,7 +164,7 @@
gpointer pObject,
const col_cvt_t* table );
GdaObject* gnc_gda_execute_query( const GncGdaBackend* pBackend, GdaQuery* pQuery );
-GdaObject* gnc_gda_execute_sql( const GncGdaBackend* pBackend, const gchar* sql );
+GdaDataModel* gnc_gda_execute_sql( const GncGdaBackend* pBackend, const gchar* sql );
GdaQuery* gnc_gda_create_query_from_sql( const GncGdaBackend* pBackend, const gchar* sql );
int gnc_gda_execute_select_get_count( const GncGdaBackend* pBackend, const gchar* sql );
int gnc_gda_execute_query_get_count( const GncGdaBackend* pBackend, GdaQuery* query );
@@ -196,6 +196,7 @@
const gpointer pObject, const col_cvt_t* table_row, GdaQuery* query );
void gnc_gda_create_objectref_guid_col( GdaServerProvider* server, GdaConnection* cnn,
xmlNodePtr array_data, const col_cvt_t* table_row, gboolean pkey );
+void gnc_gda_append_guid_list_to_sql( GString* str, GList* list );
void _retrieve_guid_( gpointer pObject, gpointer pValue );
Modified: gnucash/branches/gda-dev2/src/backend/gda/gnc-slots-gda.c
===================================================================
--- gnucash/branches/gda-dev2/src/backend/gda/gnc-slots-gda.c 2008-02-08 22:56:03 UTC (rev 16932)
+++ gnucash/branches/gda-dev2/src/backend/gda/gnc-slots-gda.c 2008-02-10 01:00:47 UTC (rev 16933)
@@ -519,7 +519,7 @@
GString* sql;
gchar guid_buf[GUID_ENCODING_LENGTH+1];
gboolean first_guid = TRUE;
- GdaObject* ret;
+ GdaDataModel* model;
gboolean single_item;
g_return_if_fail( be != NULL );
@@ -527,6 +527,8 @@
// Ignore empty list
if( list == NULL ) return;
+ coll = qof_instance_get_collection( QOF_INSTANCE(list->data) );
+
// Create the query for all slots for all items on the list
sql = g_string_sized_new( 40+(GUID_ENCODING_LENGTH+3)*g_list_length( list ) );
g_string_append_printf( sql, "SELECT * FROM %s WHERE %s ", TABLE_NAME, obj_guid_col_table[0].col_name );
@@ -537,37 +539,24 @@
g_string_append( sql, "= " );
single_item = TRUE;
}
- for( ; list != NULL; list = list->next ) {
- QofInstance* inst = QOF_INSTANCE(list->data);
- coll = qof_instance_get_collection( inst );
- guid_to_string_buff( qof_instance_get_guid( inst ), guid_buf );
-
- if( !first_guid ) {
- g_string_append( sql, "," );
- }
- g_string_append( sql, "'" );
- g_string_append( sql, guid_buf );
- g_string_append( sql, "'" );
- first_guid = FALSE;
- }
+ gnc_gda_append_guid_list_to_sql( sql, list );
if( !single_item ) {
g_string_append( sql, ")" );
}
// Execute the query and load the slots
query = gnc_gda_create_query_from_sql( be, sql->str );
- g_string_free( sql, TRUE );
- ret = gnc_gda_execute_query( be, query );
- if( GDA_IS_DATA_MODEL( ret ) ) {
- GdaDataModel* pModel = GDA_DATA_MODEL(ret);
- int numRows = gda_data_model_get_n_rows( pModel );
+ model = gnc_gda_execute_sql( be, sql->str );
+ if( model != NULL ) {
+ int numRows = gda_data_model_get_n_rows( model );
int r;
for( r = 0; r < numRows; r++ ) {
- load_slot_for_list_item( be, pModel, r, coll );
+ load_slot_for_list_item( be, model, r, coll );
}
+ g_object_unref( model );
}
- g_object_unref( ret );
+ g_string_free( sql, TRUE );
}
/* ================================================================= */
Modified: gnucash/branches/gda-dev2/src/backend/gda/gnc-transaction-gda.c
===================================================================
--- gnucash/branches/gda-dev2/src/backend/gda/gnc-transaction-gda.c 2008-02-08 22:56:03 UTC (rev 16932)
+++ gnucash/branches/gda-dev2/src/backend/gda/gnc-transaction-gda.c 2008-02-10 01:00:47 UTC (rev 16933)
@@ -209,6 +209,82 @@
return bal;
}
+/*
+ * get_account_balance_from_sql
+ *
+ * Given an SQL query which should return a number of rows of gnc_numeric num/denom pairs,
+ * return the sum.
+ */
+static gnc_numeric
+get_account_balance_from_sql( GncGdaBackend* be, const gchar* sql )
+{
+ gnc_numeric bal = gnc_numeric_zero();
+ GdaDataModel* model;
+
+ g_return_val_if_fail( be != NULL, bal );
+ g_return_val_if_fail( sql != NULL, bal );
+
+ model = gnc_gda_execute_sql( be, sql );
+ if( model != NULL ) {
+ int numRows;
+ int r;
+
+ // Loop for all rows, convert each to a gnc_numeric and sum them
+ numRows = gda_data_model_get_n_rows( model );
+
+ for( r = 0; r < numRows; r++ ) {
+ gnc_numeric val = get_gnc_numeric_from_row( be, model, r );
+ bal = gnc_numeric_add( bal, val, GNC_DENOM_AUTO, GNC_HOW_DENOM_LCD );
+ }
+ }
+ g_object_unref( G_OBJECT(model) );
+
+ return bal;
+}
+
+static void
+get_account_balance_for_list_from_sql( GncGdaBackend* be, GList* result_list, const gchar* sql )
+{
+ GdaDataModel* model;
+
+ g_return_if_fail( be != NULL );
+ g_return_if_fail( sql != NULL );
+
+ model = gnc_gda_execute_sql( be, sql );
+ if( model != NULL ) {
+ for( ; result_list != NULL; result_list = result_list->next ) {
+ acct_balances_t* ab = (acct_balances_t*)result_list->data;
+ GdaDataModel* acct_values_model = gda_data_model_filter_sql_new();
+ gchar guid_buf[GUID_ENCODING_LENGTH+1];
+ gchar* filter_sql;
+ gboolean success;
+ int numRows;
+ int r;
+ gnc_numeric bal = gnc_numeric_zero();
+
+ guid_to_string_buff( qof_instance_get_guid( ab->acct ), guid_buf );
+ filter_sql = g_strdup_printf( "SELECT quantity_num,quantity_denom FROM %s WHERE account_guid='%s'",
+ SPLIT_TABLE, guid_buf );
+ gda_data_model_filter_sql_set_sql( GDA_DATA_MODEL_FILTER_SQL(acct_values_model), filter_sql );
+ g_free( filter_sql );
+ success = gda_data_model_filter_sql_run( GDA_DATA_MODEL_FILTER_SQL(acct_values_model) );
+ if( !success ) {
+ PERR( "Unable to filter SQL data model" );
+ }
+
+ // Loop for all rows, convert each to a gnc_numeric and sum them
+ numRows = gda_data_model_get_n_rows( acct_values_model );
+
+ for( r = 0; r < numRows; r++ ) {
+ gnc_numeric val = get_gnc_numeric_from_row( be, acct_values_model, r );
+ bal = gnc_numeric_add( bal, val, GNC_DENOM_AUTO, GNC_HOW_DENOM_LCD );
+ }
+ ab->start_balance = bal;
+ }
+ }
+}
+
+
void
gnc_gda_get_account_balances( GncGdaBackend* be, Account* pAccount,
gnc_numeric* start_balance,
@@ -236,13 +312,14 @@
* gnc_numerics and then added. With luck, there will only be one entry.
*/
- //sql = g_strdup_printf( "SELECT SUM(QUANTITY_NUM),QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf );
- sql = g_strdup_printf( "SELECT QUANTITY_NUM,QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf );
+ sql = g_strdup_printf( "SELECT SUM(QUANTITY_NUM) AS quantity_num,quantity_denom FROM %s WHERE ACCOUNT_GUID='%s' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf );
+ //sql = g_strdup_printf( "SELECT QUANTITY_NUM,QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf );
/* Create the query */
- query = gnc_gda_create_query_from_sql( be, sql );
- *start_balance = get_account_balance_from_query( be, query );
- g_object_unref( G_OBJECT(query) );
+// query = gnc_gda_create_query_from_sql( be, sql );
+// *start_balance = get_account_balance_from_query( be, query );
+ *start_balance = get_account_balance_from_sql( be, sql );
+// g_object_unref( G_OBJECT(query) );
g_free( sql );
/*
@@ -254,12 +331,13 @@
* This just requires a modification to the query
*/
- //sql = g_strdup_printf( "SELECT SUM(QUANTITY_NUM),QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='%c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf, CREC );
- sql = g_strdup_printf( "SELECT QUANTITY_NUM,QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='%c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf, CREC );
+ sql = g_strdup_printf( "SELECT SUM(QUANTITY_NUM) as quantity_num,quantity_denom FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='%c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf, CREC );
+ //sql = g_strdup_printf( "SELECT QUANTITY_NUM,QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='%c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf, CREC );
- query = gnc_gda_create_query_from_sql( be, sql );
- *cleared_balance = get_account_balance_from_query( be, query );
- g_object_unref( G_OBJECT(query) );
+ //query = gnc_gda_create_query_from_sql( be, sql );
+ //*cleared_balance = get_account_balance_from_query( be, query );
+ *cleared_balance = get_account_balance_from_sql( be, sql );
+ //g_object_unref( G_OBJECT(query) );
g_free( sql );
@@ -272,16 +350,99 @@
* This just requires a small modification to the cleared balance query
*/
- //sql = g_strdup_printf( "SELECT SUM(QUANTITY_NUM),QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='%c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf, YREC );
- sql = g_strdup_printf( "SELECT QUANTITY_NUM,QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='%c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf, YREC );
+ sql = g_strdup_printf( "SELECT SUM(QUANTITY_NUM) as quantity_num,quantity_denom FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='%c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf, YREC );
+ //sql = g_strdup_printf( "SELECT QUANTITY_NUM,QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='%c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf, YREC );
- query = gnc_gda_create_query_from_sql( be, sql );
- *reconciled_balance = get_account_balance_from_query( be, query );
- g_object_unref( G_OBJECT(query) );
+ //query = gnc_gda_create_query_from_sql( be, sql );
+ //*reconciled_balance = get_account_balance_from_query( be, query );
+ *reconciled_balance = get_account_balance_from_sql( be, sql );
+ //g_object_unref( G_OBJECT(query) );
g_free( sql );
}
+GList*
+gnc_gda_get_account_balances_for_list( GncGdaBackend* be, GList* list )
+{
+ GdaQuery* query;
+ GString* sql;
+ GList* result_list = NULL;
+ GList* orig_list = list;
+
+ g_return_val_if_fail( be != NULL, NULL );
+
+ if( list == NULL ) return NULL;
+
+ // Create the result list
+ for( ; list != NULL; list = list->next ) {
+ acct_balances_t* acct_balances = g_new0( acct_balances_t, 1 );
+ acct_balances->acct = GNC_ACCOUNT(list->data);
+ acct_balances->start_balance = gnc_numeric_zero();
+ acct_balances->cleared_balance = gnc_numeric_zero();
+ acct_balances->reconciled_balance = gnc_numeric_zero();
+ result_list = g_list_append( result_list, acct_balances );
+ }
+ list = orig_list;
+
+ //
+ // For start balance,
+ // SELECT SUM(QUANTITY_NUM),QUANTITY_DENOM FROM SPLITS
+ // WHERE ACCOUNT_GUID=<guid> GROUP BY QUANTITY_DENOM
+ //
+ // This will return one entry per denom. These can then be made into
+ // gnc_numerics and then added. With luck, there will only be one entry.
+ //
+
+ sql = g_string_new( "" );
+ g_string_printf( sql, "SELECT account_guid,SUM(QUANTITY_NUM) AS quantity_num,quantity_denom FROM %s WHERE ACCOUNT_GUID IN (", SPLIT_TABLE );
+ gnc_gda_append_guid_list_to_sql( sql, list );
+ g_string_append( sql, ") GROUP BY ACCOUNT_GUID" );
+
+ get_account_balance_for_list_from_sql( be, result_list, sql->str );
+
+#if 0
+ /*
+ * For cleared balance,
+ * SELECT SUM(QUANTITY_NUM),QUANTITY_DENOM FROM SPLITS
+ * WHERE ACCOUNT_GUID=<guid> AND RECONCILE_STATE='c'
+ * GROUP BY QUANTITY_DENOM
+ *
+ * This just requires a modification to the query
+ */
+
+ sql = g_strdup_printf( "SELECT SUM(QUANTITY_NUM) as quantity_num,quantity_denom FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='%c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf, CREC );
+ //sql = g_strdup_printf( "SELECT QUANTITY_NUM,QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='%c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf, CREC );
+
+ //query = gnc_gda_create_query_from_sql( be, sql );
+ //*cleared_balance = get_account_balance_from_query( be, query );
+ *cleared_balance = get_account_balance_from_sql( be, sql );
+ //g_object_unref( G_OBJECT(query) );
+
+ g_free( sql );
+
+ /*
+ * For reconciled balance,
+ * SELECT SUM(QUANTITY_NUM),QUANTITY_DENOM FROM SPLITS
+ * WHERE ACCOUNT_GUID=<guid> AND RECONCILE_STATE='c'
+ * GROUP BY QUANTITY_DENOM
+ *
+ * This just requires a small modification to the cleared balance query
+ */
+
+ sql = g_strdup_printf( "SELECT SUM(QUANTITY_NUM) as quantity_num,quantity_denom FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='%c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf, YREC );
+ //sql = g_strdup_printf( "SELECT QUANTITY_NUM,QUANTITY_DENOM FROM %s WHERE ACCOUNT_GUID='%s' AND RECONCILE_STATE='%c' GROUP BY QUANTITY_DENOM", SPLIT_TABLE, guid_buf, YREC );
+
+ //query = gnc_gda_create_query_from_sql( be, sql );
+ //*reconciled_balance = get_account_balance_from_query( be, query );
+ *reconciled_balance = get_account_balance_from_sql( be, sql );
+ //g_object_unref( G_OBJECT(query) );
+#endif
+
+ g_string_free( sql, TRUE );
+
+ return result_list;
+}
+
static void
load_single_split( GncGdaBackend* be, GdaDataModel* pModel, int row, GList** pList )
{
@@ -356,8 +517,7 @@
GString* sql;
GdaQuery* query;
QofCollection* col;
- GdaObject* ret;
- gchar guid_buf[GUID_ENCODING_LENGTH+1];
+ GdaDataModel* model;
gboolean first_guid = TRUE;
g_return_if_fail( be != NULL );
@@ -366,39 +526,26 @@
sql = g_string_sized_new( 40+(GUID_ENCODING_LENGTH+3)*g_list_length( list ) );
g_string_append_printf( sql, "SELECT * FROM %s WHERE %s IN (", SPLIT_TABLE, guid_col_table[0].col_name );
- for( ; list != NULL; list = list->next ) {
- QofInstance* inst = QOF_INSTANCE(list->data);
- guid_to_string_buff( qof_instance_get_guid( inst ), guid_buf );
-
- if( !first_guid ) {
- g_string_append( sql, "," );
- }
- g_string_append( sql, "'" );
- g_string_append( sql, guid_buf );
- g_string_append( sql, "'" );
- first_guid = FALSE;
- }
+ gnc_gda_append_guid_list_to_sql( sql, list );
g_string_append( sql, ")" );
// Execute the query and load the splits
- query = gnc_gda_create_query_from_sql( be, sql->str );
- g_string_free( sql, TRUE );
- ret = gnc_gda_execute_query( be, query );
- if( GDA_IS_DATA_MODEL( ret ) ) {
- GdaDataModel* pModel = GDA_DATA_MODEL(ret);
- int numRows = gda_data_model_get_n_rows( pModel );
+ model = gnc_gda_execute_sql( be, sql->str );
+ if( model != NULL ) {
+ int numRows = gda_data_model_get_n_rows( model );
int r;
GList* list = NULL;
for( r = 0; r < numRows; r++ ) {
- load_single_split( be, pModel, r, &list );
+ load_single_split( be, model, r, &list );
}
if( list != NULL ) {
gnc_gda_slots_load_for_list( be, list );
}
}
- g_object_unref( ret );
+ g_string_free( sql, TRUE );
+ g_object_unref( model );
}
static void
@@ -665,7 +812,7 @@
const GUID* acct_guid;
gchar guid_buf[GUID_ENCODING_LENGTH+1];
GdaQuery* query;
- GdaObject* results;
+ GdaDataModel* model;
gchar* buf;
g_return_val_if_fail( be != NULL, NULL );
@@ -676,10 +823,9 @@
guid_to_string_buff( acct_guid, guid_buf );
sql = g_string_new( "" );
g_string_printf( sql, "SELECT DISTINCT tx_guid FROM %s WHERE account_guid='%s'", SPLIT_TABLE, guid_buf );
- results = gnc_gda_execute_sql( be, sql->str );
- if( GDA_IS_DATA_MODEL( results ) ) {
- GdaDataModel* pModel = GDA_DATA_MODEL(results);
- int numRows = gda_data_model_get_n_rows( pModel );
+ model = gnc_gda_execute_sql( be, sql->str );
+ if( model != NULL ) {
+ int numRows = gda_data_model_get_n_rows( model );
int r;
sql = g_string_sized_new( 40+(GUID_ENCODING_LENGTH+3)*numRows );
@@ -693,7 +839,7 @@
for( r = 0; r < numRows; r++ ) {
const GUID* guid;
- guid = gnc_gda_load_tx_guid( be, pModel, r );
+ guid = gnc_gda_load_tx_guid( be, model, r );
guid_to_string_buff( guid, guid_buf );
if( r != 0 ) {
g_string_append( sql, "," );
Modified: gnucash/branches/gda-dev2/src/backend/gda/gnc-transaction-gda.h
===================================================================
--- gnucash/branches/gda-dev2/src/backend/gda/gnc-transaction-gda.h 2008-02-08 22:56:03 UTC (rev 16932)
+++ gnucash/branches/gda-dev2/src/backend/gda/gnc-transaction-gda.h 2008-02-10 01:00:47 UTC (rev 16933)
@@ -40,4 +40,12 @@
gnc_numeric* cleared_balance,
gnc_numeric* reconciled_balance );
+typedef struct {
+ Account* acct;
+ gnc_numeric start_balance;
+ gnc_numeric cleared_balance;
+ gnc_numeric reconciled_balance;
+} acct_balances_t;
+GList* gnc_gda_get_account_balances_for_list( GncGdaBackend* be, GList* list );
+
#endif /* GNC_TRANSACTION_GDA_H_ */
More information about the gnucash-changes
mailing list