QIF Export

Money Manager Ex Development related posts
(http://github.moneymanagerex.org/)

Moderator: Renato

Post Reply
Nikolay
MMEX Developer
Posts: 1384
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

QIF Export

Post by Nikolay »

Hi,

I have started to make QIF export functionality for MMEX.

Data were successfully imported into "Family 2009".

Info about QIF format: http://en.wikipedia.org/wiki/Quicken_Interchange_Format
http://linuxfinances.info/info/financeformats.html

Export sample:

Code: Select all

!Type:Cash
D26/09 2009
T100.01
PКоля
L$Доход
Mtest
^
D28/09 2009
T-200.02
PКоля
LАвто:Расходники
Mtest
^
D28/09 2009
T-123.12
P$Raiffeisen MC
LTransfer
M
^
C++ code:

Code: Select all

void mmExportQIF(wxSQLite3Database* db_)
{
 if (mmDBWrapper::getNumAccounts(db_) == 0)
    {
        mmShowErrorMessage(0, _("No Account available! Cannot Export!"), _("Error"));
        return;
    }
    wxArrayString as;
    
    mmBEGINSQL_LITE_EXCEPTION;
    wxSQLite3ResultSet q1 = db_->ExecuteQuery(g_AccountNameSQL);
    while (q1.NextRow())
    {
        as.Add(q1.GetString(wxT("ACCOUNTNAME")));
    }
    q1.Finalize();
    mmENDSQL_LITE_EXCEPTION;

    wxString delimit = mmDBWrapper::getInfoSettingValue(db_, wxT("DELIMITER"), DEFDELIMTER);
    
    wxSingleChoiceDialog* scd = new wxSingleChoiceDialog(0, _("Choose Account to Export from:"), 
        _("QIF Export"), as);
    if (scd->ShowModal() == wxID_OK)
    {
        wxString acctName = scd->GetStringSelection();
        int fromAccountID = mmDBWrapper::getAccountID(db_, acctName);

         wxString fileName = wxFileSelector(_("Choose QIF data file to Export"), 
                wxT(""), wxT(""), wxT(""), wxT("*.qif"), wxSAVE | wxOVERWRITE_PROMPT);
        if ( !fileName.IsEmpty() )
        {
            wxFileOutputStream output( fileName );
            wxTextOutputStream text( output );

            mmBEGINSQL_LITE_EXCEPTION;
            static const char sql[] = 
            "SELECT TRANSDATE, "
                   "TRANSCODE, TRANSAMOUNT,  SUBCATEGID, "
                   "CATEGID, PAYEEID, "
                   "TRANSACTIONNUMBER, NOTES, TOACCOUNTID, ACCOUNTID "
            "FROM CHECKINGACCOUNT_V1 "
            "where ACCOUNTID = ? OR TOACCOUNTID = ?";

			wxSQLite3Statement st = db_->PrepareStatement(sql);
            st.Bind(1, fromAccountID);
            st.Bind(2, fromAccountID);

			wxSQLite3ResultSet q1 = st.ExecuteQuery();
            int numRecords = 0;

			text 
			//	<< wxT("!Account") << endl 
			//	 << acctName <<  endl
			//	 << wxT("TChecking") << endl
			//	 << wxT("^") <<  endl
				 << wxT("!Type:Cash") << endl;

            while (q1.NextRow())
            {
                wxString dateDBString = q1.GetString(wxT("TRANSDATE"));
                wxDateTime dtdt = mmGetStorageStringAsDate(dateDBString);
                wxString dateString = mmGetDateForDisplay(db_, dtdt);

                int sid, cid;
                wxString payee = mmDBWrapper::getPayee(db_, q1.GetInt(wxT("PAYEEID")), sid, cid);
                wxString type = q1.GetString(wxT("TRANSCODE"));
                wxString amount = q1.GetString(wxT("TRANSAMOUNT"));
				//int amount = q1.GetDouble(wxT("TRANSAMOUNT"));
				

       /* wxString displayTransAmtString;
		wxString transAmtString;
        if (mmCurrencyFormatter::formatDoubleToCurrencyEdit(amount, displayTransAmtString))
            transAmtString = displayTransAmtString;*/


                wxString categ = mmDBWrapper::getCategoryName(db_, q1.GetInt(wxT("CATEGID")));
                wxString subcateg = mmDBWrapper::getSubCategoryName(db_, 
                    q1.GetInt(wxT("CATEGID")), q1.GetInt(wxT("SUBCATEGID")));
                wxString transNum = q1.GetString(wxT("TRANSACTIONNUMBER"));
                wxString notes = mmUnCleanString(q1.GetString(wxT("NOTES")));
				notes.Replace(wxT("\n"),wxT(" "));
                wxString transfer = wxT("");
                if (type == wxT("Transfer"))
                {
                   int tAccountID = q1.GetInt(wxT("TOACCOUNTID"));
                   int fAccountID = q1.GetInt(wxT("ACCOUNTID"));

                   wxString fromAccount = mmDBWrapper::getAccountName(db_,  fAccountID);
                   wxString toAccount = mmDBWrapper::getAccountName(db_,  tAccountID );

                   if (tAccountID == fromAccountID)
                   {
                      type = wxT("Deposit");
                      payee = fromAccount;
                   }
                   else if (fAccountID == fromAccountID)
                   {
                      type = wxT("Withdrawal");
                      payee = toAccount;
                      transfer = wxT("T");
                   }
                }
               if (type == wxT("Withdrawal"))
				   amount = wxT("-")+amount;

				text << wxT('D') <<dateString << endl 
					 << wxT('T') << amount << endl 
					 << wxT('P') <<payee << endl
					 << wxT('L') << categ << (subcateg!=wxT("") ? wxT(":") : wxT("")) << subcateg << endl
					 << wxT('M') << notes << endl
					 //mmReadyDisplayString(mmUnCleanString(trans_[selIndex]->notes_))
					 << wxT('^') << endl;
					/* <<payee << delimit << type << delimit << amount
                     << delimit << categ << delimit << subcateg << delimit << transNum 
                     << delimit << notes << delimit << transfer << endl;*/
                numRecords++;
            }
            q1.Finalize();

            wxString msg = wxString::Format(wxT("%d transactions exported"), numRecords);
            mmShowErrorMessage(0, msg, _("Export to QIF"));

            mmENDSQL_LITE_EXCEPTION;
        }// if fileName.empty();

    }// show Modal
    scd->Destroy();
}

Vadim
MMEX Developer
Posts: 142
Joined: Mon Aug 03, 2009 7:35 am
Are you a spam bot?: No

Re: QIF Export

Post by Vadim »

Don't commit this code! You MUST use parameters for SQL.
Never use "select * from ...", explicitly type all required fields.

Never use '\' for string literals, it's uses mostly in shell scripts but not in C++. Use literals concatenation
"select field1, ... "
"from "
"where ...";

The program should become better but not worse!!!

Nikolay
MMEX Developer
Posts: 1384
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: QIF Export

Post by Nikolay »

Hi Vadim,

Thank you wery mach for comments. The code has been copy/pasted from other sourses. :D

Now QIF file includes splited transactions:

Code: Select all

!Type:Cash
D26/09 2009
T100.01
PКоля
N
L$Доход
Mtest
^
D27/09 2009
T-200.02
PКоля
N654321
LАвто:Расходники
Mtest
^
D28/09 2009
T-123.12
P$Raiffeisen MC
N
LTransfer
M1 notes  2 notes Should be minus
^
D28/09 2009
T222
PЗП
N
LTransfer
MSalary
^
D28/09 2009
T-60
PКоля
N543
LSplit
Msplit test
SАвто:Ремонт
$-10
SАвто:Топливо
$-50
^
C++ code:

Code: Select all

void mmExportQIF(wxSQLite3Database* db_)
{
 if (mmDBWrapper::getNumAccounts(db_) == 0)
    {
        mmShowErrorMessage(0, _("No Account available! Cannot Export!"), _("Error"));
        return;
    }
    wxArrayString as;
    
    mmBEGINSQL_LITE_EXCEPTION;
    wxSQLite3ResultSet q1 = db_->ExecuteQuery(g_AccountNameSQL);
    while (q1.NextRow())
    {
        as.Add(q1.GetString(wxT("ACCOUNTNAME")));
    }
    q1.Finalize();
    mmENDSQL_LITE_EXCEPTION;

    wxString delimit = mmDBWrapper::getInfoSettingValue(db_, wxT("DELIMITER"), DEFDELIMTER);
    
    wxSingleChoiceDialog* scd = new wxSingleChoiceDialog(0, _("Choose Account to Export from:"), 
        _("QIF Export"), as);
    if (scd->ShowModal() == wxID_OK)
    {
        wxString acctName = scd->GetStringSelection();
        int fromAccountID = mmDBWrapper::getAccountID(db_, acctName);

         wxString fileName = wxFileSelector(_("Choose QIF data file to Export"), 
                wxT(""), wxT(""), wxT(""), wxT("*.qif"), wxSAVE | wxOVERWRITE_PROMPT);
        if ( !fileName.IsEmpty() )
        {
            wxFileOutputStream output( fileName );
            wxTextOutputStream text( output );

            mmBEGINSQL_LITE_EXCEPTION;
            /*static const char sql[] = 
            "SELECT DATE, "
                   "TRANSACTIONTYPE, AMOUNT,  SUBCATEGID, "
                   "CATEGID, PAYEEID, "
                   "TRANSACTIONNUMBER, NOTES, TOACCOUNTID, ACCOUNTID "
            "FROM alldata "
            "WHERE ACCOUNTID = ? OR TOACCOUNTID = ?"; */
			static const char sql[] = 
            "SELECT transid, transdate as DATE, "
                   "transcode as TRANSACTIONTYPE, transamount as AMOUNT,  SUBCATEGID, "
                   "CATEGID, PAYEEID, "
                   "TRANSACTIONNUMBER, NOTES, TOACCOUNTID, ACCOUNTID "
            "FROM checkingaccount_v1 "
            "WHERE ACCOUNTID = ? OR TOACCOUNTID = ?"; 

			wxSQLite3Statement st = db_->PrepareStatement(sql);
            st.Bind(1, fromAccountID);
            st.Bind(2, fromAccountID);

			wxSQLite3ResultSet q1 = st.ExecuteQuery();
            int numRecords = 0;

			text 
			//	<< wxT("!Account") << endl 
			//	 << acctName <<  endl
			//	 << wxT("TChecking") << endl
			//	 << wxT("^") <<  endl
				 << wxT("!Type:Cash") << endl;

            while (q1.NextRow())
            {
                wxString transid = q1.GetString(wxT("TRANSID"));
				wxString dateDBString = q1.GetString(wxT("DATE"));
                wxDateTime dtdt = mmGetStorageStringAsDate(dateDBString);
                wxString dateString = mmGetDateForDisplay(db_, dtdt);

                int sid, cid;
                wxString payee = mmDBWrapper::getPayee(db_, q1.GetInt(wxT("PAYEEID")), sid, cid);
                wxString type = q1.GetString(wxT("TRANSACTIONTYPE"));
                wxString amount = q1.GetString(wxT("AMOUNT"));
                wxString transNum = q1.GetString(wxT("TRANSACTIONNUMBER"));
                wxString categ = mmDBWrapper::getCategoryName(db_, q1.GetInt(wxT("CATEGID")));
                wxString subcateg = mmDBWrapper::getSubCategoryName(db_, 
                    q1.GetInt(wxT("CATEGID")), q1.GetInt(wxT("SUBCATEGID")));
                wxString notes = mmUnCleanString(q1.GetString(wxT("NOTES")));
				notes.Replace(wxT("\n"),wxT(" "));
                //wxString transfer = wxT(""); 
                if (type == wxT("Transfer"))
                {
                   int tAccountID = q1.GetInt(wxT("TOACCOUNTID"));
                   int fAccountID = q1.GetInt(wxT("ACCOUNTID"));

                   wxString fromAccount = mmDBWrapper::getAccountName(db_,  fAccountID);
                   wxString toAccount = mmDBWrapper::getAccountName(db_,  tAccountID );

                   if (tAccountID == fromAccountID)
                   {
                      type = wxT("Deposit");
                      payee = fromAccount;
                   }
                   else if (fAccountID == fromAccountID)
                   {
                      type = wxT("Withdrawal");
                      payee = toAccount;
                  //    transfer = wxT("T");
                   }
                }
               
				text << wxT('D') <<dateString << endl 
					 << wxT('T') << (type == wxT("Withdrawal") ? wxT("-"): wxT("")) << amount << endl 
					 << wxT('P') << payee << endl
					 << wxT('N') << transNum << endl
					 << wxT('L') << (categ!=wxT("") ? categ : _("Split")) << (subcateg!=wxT("") ? wxT(":") : wxT("")) << subcateg << endl
					 << wxT('M') << notes << endl;
				
			   if (categ == wxT("") && subcateg == wxT(""))
			{
			static const char sql2[] = 
            "SELECT SUBCATEGID, CATEGID, SPLITTRANSAMOUNT "
            "FROM splittransactions_v1 "
            "WHERE TRANSID = ?"; 

			wxSQLite3Statement st2 = db_->PrepareStatement(sql2);
            st2.Bind(1, transid);
            
			wxSQLite3ResultSet q2 = st2.ExecuteQuery();
			while (q2.NextRow())
			   {
                   wxString splitamount = q2.GetString(wxT("SPLITTRANSAMOUNT"));
                   wxString splitcateg = mmDBWrapper::getCategoryName(db_, q2.GetInt(wxT("CATEGID")));
                   wxString splitsubcateg = mmDBWrapper::getSubCategoryName(db_, 
                       q2.GetInt(wxT("CATEGID")), q2.GetInt(wxT("SUBCATEGID")));
				   text << wxT('S') << splitcateg << (splitsubcateg!=wxT("") ? wxT(":") : wxT("")) << splitsubcateg << endl
				   	 << wxT('$') << (type == wxT("Withdrawal") ? wxT("-"): wxT("")) << splitamount << endl;
			   }
			}

				text << wxT('^') << endl;
				numRecords++;
            }
            q1.Finalize();

            wxString msg = wxString::Format(wxT("%d transactions exported"), numRecords);
            mmShowErrorMessage(0, msg, _("Export to QIF"));

            mmENDSQL_LITE_EXCEPTION;
        }// if fileName.empty();

    }// show Modal
    scd->Destroy();
}

Vadim
MMEX Developer
Posts: 142
Joined: Mon Aug 03, 2009 7:35 am
Are you a spam bot?: No

Re: QIF Export

Post by Vadim »

This variant is much more better! :-)
But I recommend you don't do deep nesting in code. For example, you can destroy dialog immediatly after getting result from it.

wxString acctName;

if (scd->ShowModal() == wxID_OK)
{
acctName = scd->GetStringSelection();
}

scd->Destroy();

if (acctName.isEmpty()) {
return;
}

wxString fileName = wxFileSelector(_("Choose QIF data file to Export"),wxT(""), wxT(""), wxT(""), wxT("*.qif"), wxSAVE | wxOVERWRITE_PROMPT);

if ( fileName.IsEmpty() ) {
return;
}
... rest of code here

This will significally improve readability of code.

Nikolay
MMEX Developer
Posts: 1384
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: QIF Export

Post by Nikolay »

Code: Select all

void mmExportQIF(wxSQLite3Database* db_)
{
 if (mmDBWrapper::getNumAccounts(db_) == 0)
    {
        mmShowErrorMessage(0, _("No Account available! Cannot Export!"), _("Error"));
        return;
    }
    wxArrayString as;
    
    mmBEGINSQL_LITE_EXCEPTION;
    wxSQLite3ResultSet q1 = db_->ExecuteQuery(g_AccountNameSQL);
    while (q1.NextRow())
    {
        as.Add(q1.GetString(wxT("ACCOUNTNAME")));
    }
    q1.Finalize();
    mmENDSQL_LITE_EXCEPTION;

    wxString delimit = mmDBWrapper::getInfoSettingValue(db_, wxT("DELIMITER"), DEFDELIMTER);
    
    wxSingleChoiceDialog* scd = new wxSingleChoiceDialog(0, _("Choose Account to Export from:"), 
        _("QIF Export"), as);


	wxString acctName;

	if (scd->ShowModal() == wxID_OK)
	{
	acctName = scd->GetStringSelection();
	}

	scd->Destroy();

	if (acctName.IsEmpty()) {
	return;
	}

	wxString fileName = wxFileSelector(_("Choose QIF data file to Export"),wxT(""), wxT(""), wxT(""), wxT("*.qif"), wxSAVE | wxOVERWRITE_PROMPT);

	if ( fileName.IsEmpty() ) {
	return;
	}
            wxFileOutputStream output( fileName );
            wxTextOutputStream text( output );
			int fromAccountID = mmDBWrapper::getAccountID(db_, acctName);

            mmBEGINSQL_LITE_EXCEPTION;
			static const char sql[] = 
            "SELECT transid, transdate as DATE, "
                   "transcode as TRANSACTIONTYPE, transamount as AMOUNT,  SUBCATEGID, "
                   "CATEGID, PAYEEID, "
                   "TRANSACTIONNUMBER, NOTES, TOACCOUNTID, ACCOUNTID "
            "FROM checkingaccount_v1 "
            "WHERE ACCOUNTID = ? OR TOACCOUNTID = ?"; 

			wxSQLite3Statement st = db_->PrepareStatement(sql);
            st.Bind(1, fromAccountID);
            st.Bind(2, fromAccountID);

			wxSQLite3ResultSet q1 = st.ExecuteQuery();
            int numRecords = 0;

			text 
			//	<< wxT("!Account") << endl 
			//	 << acctName <<  endl
			//	 << wxT("TChecking") << endl
			//	 << wxT("^") <<  endl
				 << wxT("!Type:Cash") << endl;

            while (q1.NextRow())
            {
                wxString transid = q1.GetString(wxT("TRANSID"));
				wxString dateDBString = q1.GetString(wxT("DATE"));
                wxDateTime dtdt = mmGetStorageStringAsDate(dateDBString);
                wxString dateString = mmGetDateForDisplay(db_, dtdt);

                int sid, cid;
                wxString payee = mmDBWrapper::getPayee(db_, q1.GetInt(wxT("PAYEEID")), sid, cid);
                wxString type = q1.GetString(wxT("TRANSACTIONTYPE"));
                wxString amount = q1.GetString(wxT("AMOUNT"));
                wxString transNum = q1.GetString(wxT("TRANSACTIONNUMBER"));
                wxString categ = mmDBWrapper::getCategoryName(db_, q1.GetInt(wxT("CATEGID")));
                wxString subcateg = mmDBWrapper::getSubCategoryName(db_, 
                    q1.GetInt(wxT("CATEGID")), q1.GetInt(wxT("SUBCATEGID")));
                wxString notes = mmUnCleanString(q1.GetString(wxT("NOTES")));
				notes.Replace(wxT("\n"),wxT(" "));
                //wxString transfer = wxT(""); 
                if (type == wxT("Transfer"))
                {
                   int tAccountID = q1.GetInt(wxT("TOACCOUNTID"));
                   int fAccountID = q1.GetInt(wxT("ACCOUNTID"));

                   wxString fromAccount = mmDBWrapper::getAccountName(db_,  fAccountID);
                   wxString toAccount = mmDBWrapper::getAccountName(db_,  tAccountID );

                   if (tAccountID == fromAccountID)
                   {
                      type = wxT("Deposit");
                      payee = fromAccount;
                   }
                   else if (fAccountID == fromAccountID)
                   {
                      type = wxT("Withdrawal");
                      payee = toAccount;
                  //    transfer = wxT("T");
                   }
                }
               
				text << wxT('D') <<dateString << endl 
					 << wxT('T') << (type == wxT("Withdrawal") ? wxT("-"): wxT("")) << amount << endl 
					 << wxT('P') << payee << endl
					 << wxT('N') << transNum << endl
					 << wxT('L') << (categ!=wxT("") ? categ : _("Split")) << (subcateg!=wxT("") ? wxT(":") : wxT("")) << subcateg << endl
					 << wxT('M') << notes << endl;
				
			   if (categ == wxT("") && subcateg == wxT(""))
			{
			static const char sql2[] = 
            "SELECT SUBCATEGID, CATEGID, SPLITTRANSAMOUNT "
            "FROM splittransactions_v1 "
            "WHERE TRANSID = ?"; 

			wxSQLite3Statement st2 = db_->PrepareStatement(sql2);
            st2.Bind(1, transid);
            
			wxSQLite3ResultSet q2 = st2.ExecuteQuery();
			while (q2.NextRow())
			   {
                   wxString splitamount = q2.GetString(wxT("SPLITTRANSAMOUNT"));
                   wxString splitcateg = mmDBWrapper::getCategoryName(db_, q2.GetInt(wxT("CATEGID")));
                   wxString splitsubcateg = mmDBWrapper::getSubCategoryName(db_, 
                       q2.GetInt(wxT("CATEGID")), q2.GetInt(wxT("SUBCATEGID")));
				   text << wxT('S') << splitcateg << (splitsubcateg!=wxT("") ? wxT(":") : wxT("")) << splitsubcateg << endl
				   	 << wxT('$') << (type == wxT("Withdrawal") ? wxT("-"): wxT("")) << splitamount << endl;
			   }
			}

				text << wxT('^') << endl;
				numRecords++;
            }
            q1.Finalize();

            wxString msg = wxString::Format(wxT("%d transactions exported"), numRecords);
            mmShowErrorMessage(0, msg, _("Export to QIF"));

            mmENDSQL_LITE_EXCEPTION;
}

Vadim
MMEX Developer
Posts: 142
Joined: Mon Aug 03, 2009 7:35 am
Are you a spam bot?: No

Re: QIF Export

Post by Vadim »

That's OK, but would be better to reformat source code. The indent should be 4 spaces. I personally always use 8 spaces for indent, but this project uses 4. May be I reformat all sources later :-)

Nikolay
MMEX Developer
Posts: 1384
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: QIF Export

Post by Nikolay »

The patch.
Attachments
qif_export0.9.5.0.patch.zip
(2.83 KiB) Downloaded 339 times

Post Reply