/// <summary>
/// builds the SQL command for a list that represents a savings account dataset
/// </summary>
/// <param name="paymentDataSetList">The payment data set list.</param>
/// <param name="balance">The balance.</param>
/// <returns></returns>
public static string MakeTextCommand_Spar(ComparablePaymentDataSetList paymentDataSetList, string balance, bool makeCSV)
{
string primanota = paymentDataSetList.GetValueByName("Spar_Primanota");
string bookingdate = GermanDateToSQLDate(paymentDataSetList.GetValueByName("Spar_Buchungstag"));
string valuedate = GermanDateToSQLDate(paymentDataSetList.GetValueByName("Spar_Wertstellungstag"));
string tmpKey = paymentDataSetList.GetValueByName("Spar_Schlüssel");
string name = paymentDataSetList.GetValueByName("Spar_Absender");
string reasonForTransfer = paymentDataSetList.GetValueByName("Spar_Verwendungszweck").Replace("\r\n", "\\n");
string amount = GermanAmountToEnglishAmount(paymentDataSetList.GetValueByName("Spar_Betrag"));
// needs to be a neutral culture string
balance = balance.Replace(",", "");
amount = amount.Replace(" EUR", "");
Match match = Regex.Match(tmpKey, @"\(([0-9]+)\)$");
string txtKey = "";
if (match.Groups.Count > 1)
{
txtKey = match.Groups[1].ToString();
}
string postingtext = Regex.Replace(tmpKey, @"\s{0,1}\([0-9]+\)$", "");
if (postingtext.Equals(""))
{
postingtext = "NULL";
}
else
{
postingtext = "'" + postingtext + "'";
}
string tmp = "";
if (makeCSV)
{
tmp += QuoteCSVItem(primanota) + ";";
tmp += QuoteCSVItem(bookingdate) + ";";
tmp += QuoteCSVItem(valuedate) + ";";
tmp += QuoteCSVItem(txtKey) + ";";
tmp += QuoteCSVItem(postingtext) + ";";
tmp += QuoteCSVItem(name) + ";";
tmp += QuoteCSVItem(reasonForTransfer) + ";";
tmp += QuoteCSVItem(EnglishAmountToGermanAmount(amount)) + ";";
tmp += QuoteCSVItem(EnglishAmountToGermanAmount(balance)) + "\r\n";
}
else
{
tmp += "INSERT INTO payments SET ";
tmp += "payments_accounts_id = 6, ";
tmp += "primanota = '" + primanota + "', ";
tmp += "bookingdate = '" + bookingdate + "', ";
tmp += "valuedate = '" + valuedate + "', ";
tmp += "txt_key = '" + txtKey + "', ";
tmp += "postingtext = " + postingtext + ", ";
tmp += "name = '" + name + "', ";
tmp += "reason_for_transfer = '" + reasonForTransfer + "', ";
tmp += "amount = '" + amount + "', ";
tmp += "balance = '" + balance + "'";
tmp += ";\r\n";
}
return tmp;
}
/// <summary>
/// Updates the runnung balance. (Form element this.txtSaldo)
/// </summary>
/// <param name="paymentDataSetList">The new captured paymentDataSetList</param>
private void UpdateRunnungBalance(ComparablePaymentDataSetList paymentDataSetList)
{
// for the right formating
CultureInfo cultureInfo = CultureInfo.InvariantCulture;
string amount;
TextBox balanceTextBox;
#region set amount & balanceTextBox
if (boxTyp.Text == "Kreditkartenkonto")
{
amount = paymentDataSetList.GetValueByName("Kredit_Betrag");
balanceTextBox = GetWorkingTextBox("Kredit_Saldo");
}
else if (boxTyp.Text == "Sparkonto")
{
amount = paymentDataSetList.GetValueByName("Spar_Betrag");
amount = amount.Replace(" EUR", "");
balanceTextBox = GetWorkingTextBox("Spar_Saldo");
}
else
{
throw new Exception("unbekannter Konto-Typ");
}
#endregion
if (amount.Equals("")) {
MessageBox.Show("Kein Betrag gefunden!\n" +
"Wurde der korrekte Kontotyp ausgewählt?",
"Warnung",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
return;
}
amount = ToolBox.GermanAmountToEnglishAmount(amount);
// calculates running balance
try
{
decimal balance = Decimal.Parse(balanceTextBox.Text, cultureInfo);
balance += Decimal.Parse(amount, cultureInfo);
balanceTextBox.Text = balance.ToString("N2", cultureInfo);
}
catch (FormatException)
{
MessageBox.Show("Der eingegebene Saldo hat ein ungültiges Format!\n" +
"Korrekte Werte sind z.B. '1002.50' und '-4.99'! (ohne Anführungszeichen)",
"Warnung",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
}
}
/// <summary>
/// builds the SQL command for a list that represents a credit card dataset
/// </summary>
/// <param name="paymentDataSetList">The payment data set list.</param>
/// <param name="balance">The balance.</param>
/// <returns></returns>
public static string MakeTextCommand_Kredit(ComparablePaymentDataSetList paymentDataSetList, string balance, bool makeCSV)
{
string number = paymentDataSetList.GetValueByName("Kredit_Buchungsreferenz");
string bookingdate = GermanDateToSQLDate(paymentDataSetList.GetValueByName("Kredit_Buchungsdatum"));
string valuedate = GermanDateToSQLDate(paymentDataSetList.GetValueByName("Kredit_Belegdatum"));
string reasonForTransfer = paymentDataSetList.GetValueByName("Kredit_Transaktion").Replace("\r\n", "\\n");
string amount = GermanAmountToEnglishAmount(paymentDataSetList.GetValueByName("Kredit_Betrag"));
// needs to be a neutral culture string
balance = balance.Replace(",", "");
if (!paymentDataSetList.GetValueByName("Kredit_Auslandseinsatzentgeld").Equals(""))
{
reasonForTransfer += "\\n" + paymentDataSetList.GetValueByName("Kredit_Auslandseinsatzentgeld");
}
// looks nicer
if (reasonForTransfer.Equals("Einzug des Rechnungsbetra\\nges"))
{
reasonForTransfer = "Einzug des Rechnungsbetrages";
}
string tmp = "";
// MS Excel compatible
if (makeCSV)
{
tmp += QuoteCSVItem(number) + ";";
tmp += QuoteCSVItem(bookingdate) + ";";
tmp += QuoteCSVItem(valuedate) + ";";
tmp += QuoteCSVItem(reasonForTransfer) + ";";
tmp += QuoteCSVItem(EnglishAmountToGermanAmount(amount)) + ";";
tmp += QuoteCSVItem(EnglishAmountToGermanAmount(balance)) + "\r\n";
}
else
{
tmp += "INSERT INTO payments SET ";
tmp += "payments_accounts_id = 7, ";
tmp += "number = '" + number + "', ";
tmp += "bookingdate = '" + bookingdate + "', ";
tmp += "valuedate = '" + valuedate + "', ";
tmp += "reason_for_transfer = '" + reasonForTransfer + "', ";
tmp += "amount = '" + amount + "', ";
tmp += "balance = '" + balance + "'";
tmp += ";\r\n";
}
return tmp;
}
