Page 1 of 1

CSV Export

Posted: Mon Oct 05, 2009 6:38 am
by Nikolay
Hi,

I changed the algorithm for export to CSV.
Now the results are unloaded in accordance with the settings of currency. There are negative amounts for withdrawals and transfers from account.
Split transactions unloaded separately. Notes exports in one line and in double quotes.
I will continue to work to improve the code. Any comments, please?

Code: Select all

wxString mmNotes4ExportString(const wxString& orig)
{
    wxString toReturn = orig;
    if (!toReturn.IsEmpty()) 
	{
    //It's should be 1 line for each transaction in the exported file
	toReturn.Replace(wxT("\n"), wxT(" "));
	toReturn.Trim();
	//Double quotas should be doubled in the notes.
	toReturn.Replace(wxT("\""), wxT("\"\""));
	wxString text = wxT("\"") + toReturn + wxT("\"");
	toReturn = text;
	}
 	return toReturn;
}

Code: Select all

void mmExportCSV(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:"), 
        _("CSV Export"), as);

	wxString acctName;

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

	scd->Destroy();

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

	wxString fileName = wxFileSelector(_("Choose CSV data file to Export"),wxT(""), wxT(""), wxT(""), wxT("*.csv"), 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 = ?"
			"ORDER BY transdate"; 

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

			wxSQLite3ResultSet q1 = st.ExecuteQuery();
            int numRecords = 0;
            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 sign = wxT("");
                wxString amount = q1.GetString(wxT("AMOUNT"));
				//Amount should be formated if delimiters is not "." or ","
		        if (delimit != wxT(".") && delimit != wxT(","))
				{
				double value = 0;
				mmCurrencyFormatter::formatCurrencyToDouble(amount, value);
				mmCurrencyFormatter::formatDoubleToCurrencyEdit(value, amount);
				}
                wxString categ = mmDBWrapper::getCategoryName(db_, q1.GetInt(wxT("CATEGID")));
                wxString subcateg = mmDBWrapper::getSubCategoryName(db_, 
                    q1.GetInt(wxT("CATEGID")), q1.GetInt(wxT("SUBCATEGID")));
                wxString transNum = mmNotes4ExportString(q1.GetString(wxT("TRANSACTIONNUMBER")));
                wxString notes = mmNotes4ExportString(q1.GetString(wxT("NOTES")));
				wxString transfer = type;
                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)
                   {
                    //transfer = type;
					type = wxT("Withdrawal");
                    payee = toAccount;
                    }
                }
				//It should be negative amounts for withdrval
				if (type == wxT("Withdrawal")) 
				{
					sign = wxT("-");
				}
            // if categ id is empty the transaction has been splited
			if (categ.IsEmpty() && subcateg.IsEmpty())
			{
			static const char sql4splitedtrx[] = 
            "SELECT SUBCATEGID, CATEGID, SPLITTRANSAMOUNT "
            "FROM splittransactions_v1 "
            "WHERE TRANSID = ?"; 

			wxSQLite3Statement st2 = db_->PrepareStatement(sql4splitedtrx);
            st2.Bind(1, transid);
            
			wxSQLite3ResultSet q2 = st2.ExecuteQuery();
			while (q2.NextRow())
			{
                wxString splitamount = q2.GetString(wxT("SPLITTRANSAMOUNT"));
				//Amount should be formated if delimiters is not "." or ","
		        if (delimit != wxT(".") && delimit != wxT(","))
				{
				double value = 0;
				mmCurrencyFormatter::formatCurrencyToDouble(splitamount, value);
				mmCurrencyFormatter::formatDoubleToCurrencyEdit(value, splitamount);
				}
                   wxString splitcateg = mmDBWrapper::getCategoryName(db_, q2.GetInt(wxT("CATEGID")));
                   wxString splitsubcateg = mmDBWrapper::getSubCategoryName(db_,q2.GetInt(wxT("CATEGID")), q2.GetInt(wxT("SUBCATEGID")));
				   
             text << dateString << delimit << payee << delimit << sign << splitamount << delimit 
				 << splitcateg << delimit << splitsubcateg << delimit << transNum << delimit 
				 << notes << delimit << transfer << endl;

			    }
			q2.Finalize();
			}
			else 
			{
             text << dateString << delimit << payee << delimit << sign << 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 CSV"));

            mmENDSQL_LITE_EXCEPTION;

    scd->Destroy();
}