public static Int32 CreateGLBatch(BankImportTDS AMainDS,
Int32 ALedgerNumber,
Int32 AStatementKey,
Int32 AGLBatchNumber,
out TVerificationResultCollection AVerificationResult)
{
AMainDS.AEpTransaction.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpTransactionTable.GetStatementKeyDBName(),
AStatementKey);
AMainDS.AEpStatement.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpStatementTable.GetStatementKeyDBName(),
AStatementKey);
AEpStatementRow stmt = (AEpStatementRow)AMainDS.AEpStatement.DefaultView[0].Row;
AVerificationResult = null;
Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber;
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
if (!TFinancialYear.IsValidPostingPeriod(ALedgerNumber, stmt.Date, out DateEffectivePeriodNumber, out DateEffectiveYearNumber,
Transaction))
{
string msg = String.Format(Catalog.GetString("Cannot create a GL batch for date {0} since it is not in an open period of the ledger."),
stmt.Date.ToShortDateString());
TLogging.Log(msg);
AVerificationResult = new TVerificationResultCollection();
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating GL Batch"), msg, TResultSeverity.Resv_Critical));
DBAccess.GDBAccessObj.RollbackTransaction();
return(-1);
}
Int32 BatchYear, BatchPeriod;
// if DateEffective is outside the range of open periods, use the most fitting date
DateTime DateEffective = stmt.Date;
TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref DateEffective, out BatchYear, out BatchPeriod, Transaction, true);
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
DBAccess.GDBAccessObj.RollbackTransaction();
GLBatchTDS GLDS = TGLTransactionWebConnector.CreateABatch(ALedgerNumber);
ABatchRow glbatchRow = GLDS.ABatch[0];
glbatchRow.BatchPeriod = BatchPeriod;
glbatchRow.DateEffective = DateEffective;
glbatchRow.BatchDescription = String.Format(Catalog.GetString("bank import for date {0}"), stmt.Date.ToShortDateString());
decimal HashTotal = 0.0M;
decimal DebitTotal = 0.0M;
decimal CreditTotal = 0.0M;
// TODO: support several journals
// TODO: support several currencies, support other currencies than the base currency
AJournalRow gljournalRow = GLDS.AJournal.NewRowTyped();
gljournalRow.LedgerNumber = glbatchRow.LedgerNumber;
gljournalRow.BatchNumber = glbatchRow.BatchNumber;
gljournalRow.JournalNumber = glbatchRow.LastJournal + 1;
gljournalRow.TransactionCurrency = LedgerTable[0].BaseCurrency;
glbatchRow.LastJournal++;
gljournalRow.JournalPeriod = glbatchRow.BatchPeriod;
gljournalRow.DateEffective = glbatchRow.DateEffective;
gljournalRow.JournalDescription = glbatchRow.BatchDescription;
gljournalRow.SubSystemCode = CommonAccountingSubSystemsEnum.GL.ToString();
gljournalRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.STD.ToString();
gljournalRow.ExchangeRateToBase = 1.0m;
GLDS.AJournal.Rows.Add(gljournalRow);
foreach (DataRowView dv in AMainDS.AEpTransaction.DefaultView)
{
AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row;
DataView v = AMainDS.AEpMatch.DefaultView;
v.RowFilter = AEpMatchTable.GetActionDBName() + " = '" + MFinanceConstants.BANK_STMT_STATUS_MATCHED_GL + "' and " +
AEpMatchTable.GetMatchTextDBName() + " = '" + transactionRow.MatchText + "'";
if (v.Count > 0)
{
AEpMatchRow match = (AEpMatchRow)v[0].Row;
ATransactionRow trans = GLDS.ATransaction.NewRowTyped();
trans.LedgerNumber = glbatchRow.LedgerNumber;
trans.BatchNumber = glbatchRow.BatchNumber;
trans.JournalNumber = gljournalRow.JournalNumber;
trans.TransactionNumber = gljournalRow.LastTransactionNumber + 1;
trans.AccountCode = match.AccountCode;
trans.CostCentreCode = match.CostCentreCode;
trans.Reference = match.Reference;
trans.Narrative = match.Narrative;
trans.TransactionDate = transactionRow.DateEffective;
if (transactionRow.TransactionAmount < 0)
{
trans.AmountInBaseCurrency = -1 * transactionRow.TransactionAmount;
trans.TransactionAmount = -1 * transactionRow.TransactionAmount;
trans.DebitCreditIndicator = true;
DebitTotal += trans.AmountInBaseCurrency;
}
else
{
trans.AmountInBaseCurrency = transactionRow.TransactionAmount;
trans.TransactionAmount = transactionRow.TransactionAmount;
trans.DebitCreditIndicator = false;
CreditTotal += trans.AmountInBaseCurrency;
}
GLDS.ATransaction.Rows.Add(trans);
gljournalRow.LastTransactionNumber++;
// add one transaction for the bank as well
trans = GLDS.ATransaction.NewRowTyped();
trans.LedgerNumber = glbatchRow.LedgerNumber;
trans.BatchNumber = glbatchRow.BatchNumber;
trans.JournalNumber = gljournalRow.JournalNumber;
trans.TransactionNumber = gljournalRow.LastTransactionNumber + 1;
trans.AccountCode = stmt.BankAccountCode;
trans.CostCentreCode = TLedgerInfo.GetStandardCostCentre(ALedgerNumber);
trans.Reference = match.Reference;
trans.Narrative = match.Narrative;
trans.TransactionDate = transactionRow.DateEffective;
if (transactionRow.TransactionAmount < 0)
{
trans.AmountInBaseCurrency = -1 * transactionRow.TransactionAmount;
trans.TransactionAmount = -1 * transactionRow.TransactionAmount;
trans.DebitCreditIndicator = false;
CreditTotal += trans.AmountInBaseCurrency;
}
else
{
trans.AmountInBaseCurrency = transactionRow.TransactionAmount;
trans.TransactionAmount = transactionRow.TransactionAmount;
trans.DebitCreditIndicator = true;
DebitTotal += trans.AmountInBaseCurrency;
}
GLDS.ATransaction.Rows.Add(trans);
gljournalRow.LastTransactionNumber++;
}
}
gljournalRow.JournalDebitTotal = DebitTotal;
gljournalRow.JournalCreditTotal = CreditTotal;
glbatchRow.BatchDebitTotal = DebitTotal;
glbatchRow.BatchCreditTotal = CreditTotal;
glbatchRow.BatchControlTotal = HashTotal;
TVerificationResultCollection VerificationResult;
TSubmitChangesResult result = TGLTransactionWebConnector.SaveGLBatchTDS(ref GLDS,
out VerificationResult);
if (result == TSubmitChangesResult.scrOK)
{
return(glbatchRow.BatchNumber);
}
TLogging.Log("Problems storing GL Batch");
return(-1);
}
/// <summary>
/// match text should uniquely identify a gift from a certain donor with a certain purpose;
/// use account name, description, and amount;
/// remove umlaut and spaces, because the banks sometimes play around with them
/// </summary>
public static string CalculateMatchText(string ABankAccount, AEpTransactionRow tr)
{
// bank changes tr.AccountName, order of firstname and surname. use account number and blz instead
string AccountNumber = tr.BranchCode.TrimStart(new char[] { '0' }) + tr.BankAccountNumber.TrimStart(new char[] { '0' });
string matchtext = tr.Description.ToUpper();
matchtext = matchtext.
Replace(",", "").
Replace("/", "").
Replace("-", "").
Replace(";", "").
Replace(".", "").
Replace("'", "").
Replace(" ", "");
if (matchtext.Contains("EREF+") && (matchtext.IndexOf("PURP+RINP") > matchtext.IndexOf("EREF+")))
{
matchtext = matchtext.Substring(0, matchtext.IndexOf("EREF+")) + matchtext.Substring(matchtext.IndexOf("PURP+RINP"));
}
else if (matchtext.Contains("EREF+") && (matchtext.IndexOf("SVWZ+") > matchtext.IndexOf("EREF+")))
{
matchtext = matchtext.Substring(0, matchtext.IndexOf("EREF+")) + matchtext.Substring(matchtext.IndexOf("SVWZ+"));
}
else if (matchtext.Contains("EREF+") && (matchtext.LastIndexOf("+") == matchtext.IndexOf("EREF+") + 4) && (matchtext.LastIndexOf(":") <= matchtext.IndexOf("EREF+")))
{
matchtext = matchtext.Substring(0, matchtext.IndexOf("EREF+"));
}
if (matchtext.Contains("IBAN:") && matchtext.Contains("BIC:"))
{
matchtext = matchtext.Substring(0, matchtext.IndexOf("IBAN:"));
if (matchtext.Contains("EREF:"))
{
matchtext = matchtext.Substring(0, matchtext.IndexOf("EREF:"));
}
if (matchtext.Contains("EREF:"))
{
matchtext = matchtext.Substring(0, matchtext.IndexOf("EREF:"));
}
}
matchtext = matchtext.Replace("PURP+RINPRATENZAHLUNG", "");
matchtext = matchtext.Replace("PURP+RINPDauerauftragRate".ToUpper(), "");
// abweichender Zahlungsauftraggeber
matchtext = matchtext.Replace("ABWA+", "");
matchtext = matchtext.Replace("SVWZ+", "");
matchtext = ABankAccount.ToUpper() + AccountNumber + matchtext + tr.TransactionAmount.ToString("0.##").Replace(".", "");
string oldMatchText = String.Empty;
while (oldMatchText != matchtext)
{
oldMatchText = matchtext;
matchtext = matchtext.ToUpper();
matchtext = matchtext.Replace("UE", "");
matchtext = matchtext.Replace("AE", "");
matchtext = matchtext.Replace("OE", "");
matchtext = matchtext.Replace("SS", "");
matchtext = matchtext.Replace("Ü", "");
matchtext = matchtext.Replace("Ä", "");
matchtext = matchtext.Replace("Ö", "");
matchtext = matchtext.Replace("ß", "");
}
if (matchtext.Length > AEpTransactionTable.GetMatchTextLength())
{
// calculate unique check sum which is shorter than the whole match text
MD5CryptoServiceProvider cr = new MD5CryptoServiceProvider();
System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
byte[] matchbytes = encoding.GetBytes(matchtext);
matchtext = BitConverter.ToString(cr.ComputeHash(matchbytes)).Replace("-", "").ToLower();
}
return(matchtext);
}
public static Int32 CreateGiftBatch(
Int32 ALedgerNumber,
Int32 AStatementKey,
Int32 AGiftBatchNumber,
out TVerificationResultCollection AVerificationResult)
{
BankImportTDS MainDS = GetBankStatementTransactionsAndMatches(AStatementKey, ALedgerNumber);
string MyClientID = DomainManager.GClientID.ToString();
TProgressTracker.InitProgressTracker(MyClientID,
Catalog.GetString("Creating gift batch"),
MainDS.AEpTransaction.DefaultView.Count + 10);
AVerificationResult = new TVerificationResultCollection();
MainDS.AEpTransaction.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpTransactionTable.GetStatementKeyDBName(),
AStatementKey);
MainDS.AEpStatement.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpStatementTable.GetStatementKeyDBName(),
AStatementKey);
AEpStatementRow stmt = (AEpStatementRow)MainDS.AEpStatement.DefaultView[0].Row;
// TODO: optional: use the preselected gift batch, AGiftBatchNumber
Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber;
DateTime BatchDateEffective = stmt.Date;
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
if (!TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref BatchDateEffective, out DateEffectiveYearNumber,
out DateEffectivePeriodNumber,
Transaction, true))
{
// just use the latest possible date
string msg =
String.Format(Catalog.GetString("Date {0} is not in an open period of the ledger, using date {1} instead for the gift batch."),
stmt.Date.ToShortDateString(),
BatchDateEffective.ToShortDateString());
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Info));
}
ACostCentreAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
MainDS.AEpMatch.DefaultView.Sort =
AEpMatchTable.GetActionDBName() + ", " +
AEpMatchTable.GetMatchTextDBName();
if (MainDS.AEpTransaction.DefaultView.Count == 0)
{
AVerificationResult.Add(new TVerificationResult(
Catalog.GetString("Creating Gift Batch"),
String.Format(Catalog.GetString("There are no transactions for statement #{0}."), AStatementKey),
TResultSeverity.Resv_Info));
return(-1);
}
foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView)
{
AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row;
DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] {
MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT,
transactionRow.MatchText
});
if (matches.Length > 0)
{
AEpMatchRow match = (AEpMatchRow)matches[0].Row;
if (match.IsDonorKeyNull() || (match.DonorKey == 0))
{
string msg =
String.Format(Catalog.GetString("Cannot create a gift for transaction {0} since there is no valid donor."),
transactionRow.Description);
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Critical));
DBAccess.GDBAccessObj.RollbackTransaction();
return(-1);
}
}
}
string MatchedGiftReference = stmt.Filename;
if (!stmt.IsBankAccountKeyNull())
{
string sqlGetBankSortCode =
"SELECT bank.p_branch_code_c " +
"FROM PUB_p_banking_details details, PUB_p_bank bank " +
"WHERE details.p_banking_details_key_i = ?" +
"AND details.p_bank_key_n = bank.p_partner_key_n";
OdbcParameter param = new OdbcParameter("detailkey", OdbcType.Int);
param.Value = stmt.BankAccountKey;
PBankTable bankTable = new PBankTable();
DBAccess.GDBAccessObj.SelectDT(bankTable, sqlGetBankSortCode, Transaction, new OdbcParameter[] { param }, 0, 0);
MatchedGiftReference = bankTable[0].BranchCode + " " + stmt.Date.Day.ToString();
}
DBAccess.GDBAccessObj.RollbackTransaction();
GiftBatchTDS GiftDS = TGiftTransactionWebConnector.CreateAGiftBatch(
ALedgerNumber,
BatchDateEffective,
String.Format(Catalog.GetString("bank import for date {0}"), stmt.Date.ToShortDateString()));
AGiftBatchRow giftbatchRow = GiftDS.AGiftBatch[0];
giftbatchRow.BankAccountCode = stmt.BankAccountCode;
decimal HashTotal = 0.0M;
MainDS.AEpTransaction.DefaultView.Sort =
AEpTransactionTable.GetNumberOnPaperStatementDBName();
MainDS.AEpMatch.DefaultView.RowFilter = String.Empty;
MainDS.AEpMatch.DefaultView.Sort =
AEpMatchTable.GetActionDBName() + ", " +
AEpMatchTable.GetMatchTextDBName();
int counter = 5;
foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView)
{
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("Preparing the gifts"),
counter++);
AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row;
DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] {
MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT,
transactionRow.MatchText
});
if (matches.Length > 0)
{
AEpMatchRow match = (AEpMatchRow)matches[0].Row;
AGiftRow gift = GiftDS.AGift.NewRowTyped();
gift.LedgerNumber = giftbatchRow.LedgerNumber;
gift.BatchNumber = giftbatchRow.BatchNumber;
gift.GiftTransactionNumber = giftbatchRow.LastGiftNumber + 1;
gift.DonorKey = match.DonorKey;
gift.DateEntered = transactionRow.DateEffective;
gift.Reference = MatchedGiftReference;
GiftDS.AGift.Rows.Add(gift);
giftbatchRow.LastGiftNumber++;
foreach (DataRowView r in matches)
{
match = (AEpMatchRow)r.Row;
AGiftDetailRow detail = GiftDS.AGiftDetail.NewRowTyped();
detail.LedgerNumber = gift.LedgerNumber;
detail.BatchNumber = gift.BatchNumber;
detail.GiftTransactionNumber = gift.GiftTransactionNumber;
detail.DetailNumber = gift.LastDetailNumber + 1;
gift.LastDetailNumber++;
detail.GiftTransactionAmount = match.GiftTransactionAmount;
detail.GiftAmount = match.GiftTransactionAmount;
HashTotal += match.GiftTransactionAmount;
detail.MotivationGroupCode = match.MotivationGroupCode;
detail.MotivationDetailCode = match.MotivationDetailCode;
// do not use the description in comment one, because that could show up on the gift receipt???
// detail.GiftCommentOne = transactionRow.Description;
detail.CommentOneType = MFinanceConstants.GIFT_COMMENT_TYPE_BOTH;
detail.CostCentreCode = match.CostCentreCode;
detail.RecipientKey = match.RecipientKey;
detail.RecipientLedgerNumber = match.RecipientLedgerNumber;
AMotivationDetailRow motivation = (AMotivationDetailRow)MainDS.AMotivationDetail.Rows.Find(
new object[] { ALedgerNumber, detail.MotivationGroupCode, detail.MotivationDetailCode });
if (motivation == null)
{
AVerificationResult.Add(new TVerificationResult(
String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description),
String.Format(Catalog.GetString("Cannot find motivation group '{0}' and motivation detail '{1}'"),
detail.MotivationGroupCode, detail.MotivationDetailCode),
TResultSeverity.Resv_Critical));
}
if (detail.CostCentreCode.Length == 0)
{
// try to retrieve the current costcentre for this recipient
if (detail.RecipientKey != 0)
{
detail.RecipientLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(detail.RecipientKey);
detail.CostCentreCode = TGiftTransactionWebConnector.IdentifyPartnerCostCentre(detail.LedgerNumber,
detail.RecipientLedgerNumber);
}
else
{
if (motivation != null)
{
detail.CostCentreCode = motivation.CostCentreCode;
}
}
}
// check for active cost centre
ACostCentreRow costcentre = (ACostCentreRow)MainDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, detail.CostCentreCode });
if ((costcentre == null) || !costcentre.CostCentreActiveFlag)
{
AVerificationResult.Add(new TVerificationResult(
String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description),
Catalog.GetString("Invalid or inactive cost centre"),
TResultSeverity.Resv_Critical));
}
GiftDS.AGiftDetail.Rows.Add(detail);
}
}
}
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("Submit to database"),
counter++);
if (AVerificationResult.HasCriticalErrors)
{
return(-1);
}
giftbatchRow.HashTotal = HashTotal;
giftbatchRow.BatchTotal = HashTotal;
// do not overwrite the parameter, because there might be the hint for a different gift batch date
TVerificationResultCollection VerificationResultSubmitChanges;
TSubmitChangesResult result = TGiftTransactionWebConnector.SaveGiftBatchTDS(ref GiftDS,
out VerificationResultSubmitChanges);
TProgressTracker.FinishJob(MyClientID);
if (result == TSubmitChangesResult.scrOK)
{
return(giftbatchRow.BatchNumber);
}
return(-1);
}
