private void PrepareTestCaseData()
{
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction();
// Check if some special test data are available - otherwise load ...
bool AccountTestCasesAvailable = AAccountAccess.Exists(LedgerNumber, "6001", Transaction);
bool CostCentreTestCasesAvailable = ACostCentreAccess.Exists(LedgerNumber, "4301", Transaction);
DBAccess.GDBAccessObj.RollbackTransaction();
if (!AccountTestCasesAvailable)
{
CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\GL\\" +
"test-sql\\gl-test-account-data.sql", LedgerNumber);
}
if (!CostCentreTestCasesAvailable)
{
CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\GL\\" +
"test-sql\\gl-test-costcentre-data.sql", LedgerNumber);
}
}
public void Init()
{
TPetraServerConnector.Connect();
FLedgerNumber = CommonNUnitFunctions.CreateNewLedger();
// add costcentre 7300 for gift batch
ACostCentreTable CostCentres = new ACostCentreTable();
ACostCentreRow CCRow = CostCentres.NewRowTyped();
CCRow.LedgerNumber = FLedgerNumber;
CCRow.CostCentreCode = "7300";
CCRow.CostCentreName = "7300";
CCRow.CostCentreType = MFinanceConstants.FOREIGN_CC_TYPE;
CCRow.CostCentreToReportTo = MFinanceConstants.INTER_LEDGER_HEADING;
CCRow.PostingCostCentreFlag = true;
CCRow.CostCentreActiveFlag = true;
CostCentres.Rows.Add(CCRow);
ACostCentreAccess.SubmitChanges(CostCentres, null);
System.Diagnostics.Debug.WriteLine("Init: " + this.ToString());
}
public static Boolean GetPartnerKeyForForeignCostCentreCode(Int32 ALedgerNumber, String ACostCentreCode, out Int64 APartnerKey)
{
Boolean ReturnValue = false;
Int64 PartnerKey = 0;
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref transaction,
delegate
{
ACostCentreTable CostCentreTable;
CostCentreTable = ACostCentreAccess.LoadByPrimaryKey(ALedgerNumber, ACostCentreCode, transaction);
if (CostCentreTable.Count > 0)
{
ACostCentreRow CostCentreRow = (ACostCentreRow)CostCentreTable.Rows[0];
if (CostCentreRow.CostCentreType == MFinanceConstants.FOREIGN_CC_TYPE)
{
AValidLedgerNumberTable ValidLedgerNumberTable;
AValidLedgerNumberRow ValidLedgerNumberRow;
ValidLedgerNumberTable = AValidLedgerNumberAccess.LoadViaACostCentre(ALedgerNumber, ACostCentreCode, transaction);
if (ValidLedgerNumberTable.Count > 0)
{
ValidLedgerNumberRow = (AValidLedgerNumberRow)ValidLedgerNumberTable.Rows[0];
PartnerKey = ValidLedgerNumberRow.PartnerKey;
ReturnValue = true;
}
}
}
});
APartnerKey = PartnerKey;
return(ReturnValue);
}
/// <summary>
/// return a list of costcentres that does not contail any costcentre linked to a person
/// </summary>
public static string WithoutPersonCostCentres(int ALedgerNumber, String ACostCentreList)
{
// remove all costcentres that report to a costcentre which name ends with Personalkosten
ACostCentreTable costcentres = ACostCentreAccess.LoadViaALedger(ALedgerNumber, null);
costcentres.DefaultView.Sort = ACostCentreTable.GetCostCentreCodeDBName();
string[] costcentresList = ACostCentreList.Split(new char[] { ',' });
List <string> newList = new List <string>();
foreach (string cc in costcentresList)
{
ACostCentreRow costcentre = (ACostCentreRow)costcentres.DefaultView.FindRows(cc)[0].Row;
ACostCentreRow parentCC = (ACostCentreRow)costcentres.DefaultView.FindRows(costcentre.CostCentreToReportTo)[0].Row;
if (!parentCC.CostCentreName.EndsWith("Personalkosten"))
{
newList.Add(cc);
}
}
return(String.Join(",", newList.ToArray()));
}
private void PrepareTestCaseData()
{
// new database because period July has to be open
FLedgerNumber = CommonNUnitFunctions.CreateNewLedger();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("PrepareTestCaseData");
bool CostCentreTestCasesAvailable = false;
db.ReadTransaction(ref Transaction,
delegate
{
// Check if some special test data are available - otherwise load ...
CostCentreTestCasesAvailable = ACostCentreAccess.Exists(FLedgerNumber, "4301", Transaction);
});
db.CloseDBConnection();
if (!CostCentreTestCasesAvailable)
{
CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\server\\GL\\" +
"test-sql\\gl-test-costcentre-data.sql", FLedgerNumber);
}
}
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);
}
public static BankImportTDS GetBankStatementTransactionsAndMatches(Int32 AStatementKey, Int32 ALedgerNumber)
{
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
BankImportTDS ResultDataset = new BankImportTDS();
string MyClientID = DomainManager.GClientID.ToString();
TProgressTracker.InitProgressTracker(MyClientID,
Catalog.GetString("Load Bank Statement"),
100.0m);
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("loading statement"),
0);
try
{
AEpStatementAccess.LoadByPrimaryKey(ResultDataset, AStatementKey, Transaction);
if (ResultDataset.AEpStatement[0].BankAccountCode.Length == 0)
{
throw new Exception("Loading Bank Statement: Bank Account must not be empty");
}
ACostCentreAccess.LoadViaALedger(ResultDataset, ALedgerNumber, Transaction);
AMotivationDetailAccess.LoadViaALedger(ResultDataset, ALedgerNumber, Transaction);
AEpTransactionAccess.LoadViaAEpStatement(ResultDataset, AStatementKey, Transaction);
BankImportTDS TempDataset = new BankImportTDS();
AEpTransactionAccess.LoadViaAEpStatement(TempDataset, AStatementKey, Transaction);
AEpMatchAccess.LoadViaALedger(TempDataset, ResultDataset.AEpStatement[0].LedgerNumber, Transaction);
// load all bankingdetails and partner shortnames related to this statement
string sqlLoadPartnerByBankAccount =
"SELECT DISTINCT p.p_partner_key_n AS PartnerKey, " +
"p.p_partner_short_name_c AS ShortName, " +
"t.p_branch_code_c AS BranchCode, " +
"t.a_bank_account_number_c AS BankAccountNumber " +
"FROM PUB_a_ep_transaction t, PUB_p_banking_details bd, PUB_p_bank b, PUB_p_partner_banking_details pbd, PUB_p_partner p " +
"WHERE t.a_statement_key_i = " + AStatementKey.ToString() + " " +
"AND bd.p_bank_account_number_c = t.a_bank_account_number_c " +
"AND b.p_partner_key_n = bd.p_bank_key_n " +
"AND b.p_branch_code_c = t.p_branch_code_c " +
"AND pbd.p_banking_details_key_i = bd.p_banking_details_key_i " +
"AND p.p_partner_key_n = pbd.p_partner_key_n";
DataTable PartnerByBankAccount = DBAccess.GDBAccessObj.SelectDT(sqlLoadPartnerByBankAccount, "partnerByBankAccount", Transaction);
PartnerByBankAccount.DefaultView.Sort = "BranchCode, BankAccountNumber";
// get all recipients that have been merged
string sqlGetMergedRecipients =
string.Format(
"SELECT DISTINCT p.p_partner_key_n AS PartnerKey, p.p_status_code_c AS StatusCode FROM PUB_a_ep_match m, PUB_p_partner p " +
"WHERE (m.p_recipient_key_n = p.p_partner_key_n OR m.p_donor_key_n = p.p_partner_key_n) AND p.p_status_code_c = '{0}'",
MPartnerConstants.PARTNERSTATUS_MERGED);
DataTable MergedPartners = DBAccess.GDBAccessObj.SelectDT(sqlGetMergedRecipients, "mergedPartners", Transaction);
MergedPartners.DefaultView.Sort = "PartnerKey";
DBAccess.GDBAccessObj.RollbackTransaction();
string BankAccountCode = ResultDataset.AEpStatement[0].BankAccountCode;
TempDataset.AEpMatch.DefaultView.Sort = AEpMatchTable.GetMatchTextDBName();
SortedList <string, AEpMatchRow> MatchesToAddLater = new SortedList <string, AEpMatchRow>();
int count = 0;
// load the matches or create new matches
foreach (BankImportTDSAEpTransactionRow row in ResultDataset.AEpTransaction.Rows)
{
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("finding matches") +
" " + count + "/" + ResultDataset.AEpTransaction.Rows.Count.ToString(),
10.0m + (count * 80.0m / ResultDataset.AEpTransaction.Rows.Count));
count++;
BankImportTDSAEpTransactionRow tempTransactionRow =
(BankImportTDSAEpTransactionRow)TempDataset.AEpTransaction.Rows.Find(
new object[] {
row.StatementKey,
row.Order,
row.DetailKey
});
// find a match with the same match text, or create a new one
if (row.IsMatchTextNull() || (row.MatchText.Length == 0) || !row.MatchText.StartsWith(BankAccountCode))
{
row.MatchText = TBankImportMatching.CalculateMatchText(BankAccountCode, row);
tempTransactionRow.MatchText = row.MatchText;
}
DataRowView[] matches = TempDataset.AEpMatch.DefaultView.FindRows(row.MatchText);
if (matches.Length > 0)
{
Decimal sum = 0.0m;
// update the recent date
foreach (DataRowView rv in matches)
{
AEpMatchRow r = (AEpMatchRow)rv.Row;
sum += r.GiftTransactionAmount;
// check if the recipient key is still valid. could be that they have married, and merged into another family record
if ((r.RecipientKey != 0) &&
(MergedPartners.DefaultView.FindRows(r.RecipientKey).Length > 0))
{
TLogging.LogAtLevel(1, "partner has been merged: " + r.RecipientKey.ToString());
r.RecipientKey = 0;
r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
// check if the donor key is still valid. could be that they have married, and merged into another family record
if ((r.DonorKey != 0) &&
(MergedPartners.DefaultView.FindRows(r.DonorKey).Length > 0))
{
TLogging.LogAtLevel(1, "partner has been merged: " + r.DonorKey.ToString());
r.DonorKey = 0;
r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
if (r.RecentMatch < row.DateEffective)
{
r.RecentMatch = row.DateEffective;
}
// do not modify tempRow.MatchAction, because that will not be stored in the database anyway, just costs time
row.MatchAction = r.Action;
if (r.IsDonorKeyNull() || (r.DonorKey <= 0))
{
FindDonorByAccountNumber(r, PartnerByBankAccount.DefaultView, row.BranchCode, row.BankAccountNumber);
}
}
if (sum != row.TransactionAmount)
{
TLogging.Log("we should drop this match since the total is wrong: " + row.Description);
row.MatchAction = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
foreach (DataRowView rv in matches)
{
AEpMatchRow r = (AEpMatchRow)rv.Row;
r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
}
}
else if (!MatchesToAddLater.ContainsKey(row.MatchText))
{
// create new match
AEpMatchRow tempRow = TempDataset.AEpMatch.NewRowTyped(true);
tempRow.EpMatchKey = (TempDataset.AEpMatch.Count + MatchesToAddLater.Count + 1) * -1;
tempRow.Detail = 0;
tempRow.MatchText = row.MatchText;
tempRow.LedgerNumber = ALedgerNumber;
tempRow.GiftTransactionAmount = row.TransactionAmount;
tempRow.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
FindDonorByAccountNumber(tempRow, PartnerByBankAccount.DefaultView, row.BranchCode, row.BankAccountNumber);
#if disabled
// fuzzy search for the partner. only return if unique result
string sql =
"SELECT p_partner_key_n, p_partner_short_name_c FROM p_partner WHERE p_partner_short_name_c LIKE '{0}%' OR p_partner_short_name_c LIKE '{1}%'";
string[] names = row.AccountName.Split(new char[] { ' ' });
if (names.Length > 1)
{
string optionShortName1 = names[0] + ", " + names[1];
string optionShortName2 = names[1] + ", " + names[0];
DataTable partner = DBAccess.GDBAccessObj.SelectDT(String.Format(sql,
optionShortName1,
optionShortName2), "partner", Transaction);
if (partner.Rows.Count == 1)
{
tempRow.DonorKey = Convert.ToInt64(partner.Rows[0][0]);
}
}
#endif
MatchesToAddLater.Add(tempRow.MatchText, tempRow);
// do not modify tempRow.MatchAction, because that will not be stored in the database anyway, just costs time
row.MatchAction = tempRow.Action;
}
}
// for speed reasons, add the new rows after clearing the sort on the view
TempDataset.AEpMatch.DefaultView.Sort = string.Empty;
foreach (AEpMatchRow m in MatchesToAddLater.Values)
{
TempDataset.AEpMatch.Rows.Add(m);
}
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("save matches"),
90.0m);
TempDataset.ThrowAwayAfterSubmitChanges = true;
// only store a_ep_transactions and a_ep_matches, but without additional typed fields (ie MatchAction)
BankImportTDSAccess.SubmitChanges(TempDataset.GetChangesTyped(true));
}
catch (Exception e)
{
TLogging.Log(e.GetType().ToString() + " in BankImport, GetBankStatementTransactionsAndMatches; " + e.Message);
TLogging.Log(e.StackTrace);
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
// drop all matches that do not occur on this statement
ResultDataset.AEpMatch.Clear();
// reloading is faster than deleting all matches that are not needed
string sqlLoadMatchesOfStatement =
"SELECT DISTINCT m.* FROM PUB_a_ep_match m, PUB_a_ep_transaction t WHERE t.a_statement_key_i = ? AND m.a_ledger_number_i = ? AND m.a_match_text_c = t.a_match_text_c";
OdbcParameter param = new OdbcParameter("statementkey", OdbcType.Int);
param.Value = AStatementKey;
OdbcParameter paramLedgerNumber = new OdbcParameter("ledgerNumber", OdbcType.Int);
paramLedgerNumber.Value = ALedgerNumber;
DBAccess.GDBAccessObj.SelectDT(ResultDataset.AEpMatch,
sqlLoadMatchesOfStatement,
null,
new OdbcParameter[] { param, paramLedgerNumber }, -1, -1);
// update the custom field for cost centre name for each match
foreach (BankImportTDSAEpMatchRow row in ResultDataset.AEpMatch.Rows)
{
ACostCentreRow ccRow = (ACostCentreRow)ResultDataset.ACostCentre.Rows.Find(new object[] { row.LedgerNumber, row.CostCentreCode });
if (ccRow != null)
{
row.CostCentreName = ccRow.CostCentreName;
}
}
// remove all rows that we do not need on the client side
ResultDataset.AGiftDetail.Clear();
ResultDataset.AMotivationDetail.Clear();
ResultDataset.ACostCentre.Clear();
ResultDataset.AcceptChanges();
if (TLogging.DebugLevel > 0)
{
int CountMatched = 0;
foreach (BankImportTDSAEpTransactionRow transaction in ResultDataset.AEpTransaction.Rows)
{
if (!transaction.IsMatchActionNull() && (transaction.MatchAction != MFinanceConstants.BANK_STMT_STATUS_UNMATCHED))
{
CountMatched++;
}
}
TLogging.Log(
"Loading bank statement: matched: " + CountMatched.ToString() + " of " + ResultDataset.AEpTransaction.Rows.Count.ToString());
}
TProgressTracker.FinishJob(MyClientID);
return(ResultDataset);
}
public static bool LoadBudgetForConsolidate(Int32 ALedgerNumber)
{
FBudgetTDS = new BudgetTDS();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(FBudgetTDS, ALedgerNumber, Transaction);
string sqlLoadBudgetForThisAndNextYear =
string.Format("SELECT * FROM PUB_{0} WHERE {1}=? AND ({2} = ? OR {2} = ?)",
ABudgetTable.GetTableDBName(),
ABudgetTable.GetLedgerNumberDBName(),
ABudgetTable.GetYearDBName());
List <OdbcParameter> parameters = new List <OdbcParameter>();
OdbcParameter param = new OdbcParameter("ledgernumber", OdbcType.Int);
param.Value = ALedgerNumber;
parameters.Add(param);
param = new OdbcParameter("thisyear", OdbcType.Int);
param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear;
parameters.Add(param);
param = new OdbcParameter("nextyear", OdbcType.Int);
param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear + 1;
parameters.Add(param);
DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetForThisAndNextYear, FBudgetTDS.ABudget.TableName, Transaction,
parameters.ToArray());
string sqlLoadBudgetPeriodForThisAndNextYear =
string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_budget_sequence_i = {1}.a_budget_sequence_i AND " +
"{2}=? AND ({3} = ? OR {3} = ?)",
ABudgetPeriodTable.GetTableDBName(),
ABudgetTable.GetTableDBName(),
ABudgetTable.GetLedgerNumberDBName(),
ABudgetTable.GetYearDBName());
DBAccess.GDBAccessObj.Select(FBudgetTDS,
sqlLoadBudgetPeriodForThisAndNextYear,
FBudgetTDS.ABudgetPeriod.TableName,
Transaction,
parameters.ToArray());
// Accept row changes here so that the Client gets 'unmodified' rows
FBudgetTDS.AcceptChanges();
GLPostingDS = new GLPostingTDS();
AAccountAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction);
AAccountHierarchyDetailAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction);
ACostCentreAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction);
ALedgerAccess.LoadByPrimaryKey(GLPostingDS, ALedgerNumber, Transaction);
// get the glm sequences for this year and next year
for (int i = 0; i <= 1; i++)
{
int Year = GLPostingDS.ALedger[0].CurrentFinancialYear + i;
AGeneralLedgerMasterRow TemplateRow = (AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.Year = Year;
GLPostingDS.AGeneralLedgerMaster.Merge(AGeneralLedgerMasterAccess.LoadUsingTemplate(TemplateRow, Transaction));
}
string sqlLoadGlmperiodForThisAndNextYear =
string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND " +
"{2}=? AND ({3} = ? OR {3} = ?)",
AGeneralLedgerMasterPeriodTable.GetTableDBName(),
AGeneralLedgerMasterTable.GetTableDBName(),
AGeneralLedgerMasterTable.GetLedgerNumberDBName(),
AGeneralLedgerMasterTable.GetYearDBName());
DBAccess.GDBAccessObj.Select(GLPostingDS,
sqlLoadGlmperiodForThisAndNextYear,
GLPostingDS.AGeneralLedgerMasterPeriod.TableName,
Transaction,
parameters.ToArray());
});
GLPostingDS.AcceptChanges();
return(true);
}
private void RevaluateAccount(DataView GLMView, decimal AExchangeRate)
{
foreach (DataRowView RowView in GLMView)
{
AGeneralLedgerMasterRow glmRow = (AGeneralLedgerMasterRow)RowView.Row;
ACostCentreTable tempTbl = null;
AGeneralLedgerMasterPeriodTable glmpTbl = null;
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref transaction,
delegate
{
tempTbl = ACostCentreAccess.LoadByPrimaryKey(F_LedgerNum, glmRow.CostCentreCode, transaction);
glmpTbl = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(glmRow.GlmSequence, F_AccountingPeriod, transaction);
});
if (tempTbl.Rows.Count == 0)
{
continue; // I really don't expect this, but if it does happen, this will prevent a crash!
}
ACostCentreRow tempRow = tempTbl[0];
if (!tempRow.PostingCostCentreFlag)
{
continue; // I do expect this - many rows are not "posting" cost centres.
}
try
{
if (glmpTbl.Rows.Count == 0)
{
continue; // I really don't expect this, but if it does happen, this will prevent a crash!
}
AGeneralLedgerMasterPeriodRow glmpRow = glmpTbl[0];
//
// If ActualForeign has not been set, I can't allow the ORM to even attempt to access them:
// (If ActualForeign is NULL, that's probably a fault, but this has occured in historical data.)
if (glmpRow.IsActualBaseNull() || glmpRow.IsActualForeignNull())
{
continue;
}
decimal delta = AccountDelta(glmpRow.ActualBase,
glmpRow.ActualForeign,
AExchangeRate,
F_BaseCurrencyDigits);
if (delta != 0)
{
// Now we have the relevant Cost Centre ...
RevaluateCostCentre(glmRow.AccountCode, glmRow.CostCentreCode, delta);
}
else
{
string strMessage = String.Format(
Catalog.GetString("The account {1}:{0} does not require revaluation."),
glmRow.AccountCode,
glmRow.CostCentreCode,
AExchangeRate);
FVerificationCollection.Add(new TVerificationResult(
strStatusContent, strMessage, TResultSeverity.Resv_Noncritical));
}
}
catch (EVerificationException terminate)
{
FVerificationCollection = terminate.ResultCollection();
}
catch (DivideByZeroException)
{
FVerificationCollection.Add(new TVerificationResult(
strStatusContent, Catalog.GetString("DivideByZeroException"), TResultSeverity.Resv_Noncritical));
}
catch (OverflowException)
{
FVerificationCollection.Add(new TVerificationResult(
strStatusContent, Catalog.GetString("OverflowException"), TResultSeverity.Resv_Noncritical));
}
}
}
private void CalculateAccountInfo()
{
FaccountInfo = new TAccountInfo(FledgerInfo);
bool blnIncomeFound = false;
bool blnExpenseFound = false;
String strIncomeAccount = TAccountTypeEnum.Income.ToString();
String strExpenseAccount = TAccountTypeEnum.Expense.ToString();
FaccountInfo.Reset();
FAccountList = new List <String>();
while (FaccountInfo.MoveNext())
{
if (FaccountInfo.PostingStatus)
{
if (FaccountInfo.AccountType == strIncomeAccount)
{
FAccountList.Add(FaccountInfo.AccountCode);
blnIncomeFound = true;
}
if (FaccountInfo.AccountType == strExpenseAccount)
{
FAccountList.Add(FaccountInfo.AccountCode);
blnExpenseFound = true;
}
}
}
if (!blnIncomeFound)
{
TVerificationResult tvt =
new TVerificationResult(Catalog.GetString("No Income Account found"),
Catalog.GetString("At least one income account is required."), "",
TPeriodEndErrorAndStatusCodes.PEEC_09.ToString(),
TResultSeverity.Resv_Critical);
FverificationResults.Add(tvt);
FHasCriticalErrors = true;
}
if (!blnExpenseFound)
{
TVerificationResult tvt =
new TVerificationResult(Catalog.GetString("No Expense Account found"),
Catalog.GetString("At least one expense account is required."), "",
TPeriodEndErrorAndStatusCodes.PEEC_10.ToString(),
TResultSeverity.Resv_Critical);
FverificationResults.Add(tvt);
FHasCriticalErrors = true;
}
FaccountInfo.SetSpecialAccountCode(TAccountPropertyEnum.ICH_ACCT);
if (FaccountInfo.IsValid)
{
FAccountList.Add(FaccountInfo.AccountCode);
}
else
{
TVerificationResult tvt =
new TVerificationResult(Catalog.GetString("No ICH_ACCT Account defined"),
Catalog.GetString("An ICH Account must be defined."), "",
TPeriodEndErrorAndStatusCodes.PEEC_11.ToString(),
TResultSeverity.Resv_Critical);
FverificationResults.Add(tvt);
FHasCriticalErrors = true;
}
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
FCostCentreTbl = ACostCentreAccess.LoadViaALedger(FledgerInfo.LedgerNumber, Transaction);
});
FCostCentreTbl.DefaultView.Sort = ACostCentreTable.GetCostCentreCodeDBName();
}
private static Boolean ExportDonations(Int32 ADaySpan)
{
GetGiftBatches(ADaySpan);
StreamWriter sw1 = File.CreateText(FExportFilePath + "donor.csv");
sw1.WriteLine("first_name,last_name,partner_key,email,address,telephone,anonymous,class");
StreamWriter sw2 = File.CreateText(FExportFilePath + "donation.csv");
sw2.WriteLine("donor,recipient,trans_amount,trans_currency,base_amount,admin_gif,admin_ict,admin_other," +
"base_currency,intl_amount,intl_currency,date,id,source_ledger,recipient_field,anonymous,comment1,for1,comment2,for2,comment3,for3");
foreach (BatchKey Batch in GiftBatches)
{
String GiftBatchQuery = "SELECT " +
"PUB_a_gift.p_donor_key_n AS DonorKey, " +
"PUB_a_gift_detail.p_recipient_key_n AS RecipientKey, " +
"PUB_a_gift_detail.a_gift_transaction_amount_n AS TransactionAmount, " +
"PUB_a_gift_batch.a_currency_code_c AS CurrencyCode, " +
"PUB_a_gift_detail.a_gift_amount_n AS GiftAmount, " +
"PUB_a_ledger.a_base_currency_c AS BaseCurrency, " +
"PUB_a_gift_detail.a_gift_amount_intl_n AS IntlAmount, " +
"PUB_a_gift_detail.a_cost_centre_code_c AS CostCentre, " +
"PUB_a_ledger.a_intl_currency_c AS IntlCurrency, " +
"PUB_a_gift_batch.a_gl_effective_date_d AS EffectiveDate, " +
"PUB_a_gift_batch.a_batch_number_i AS BatchNumber, " +
"PUB_a_gift.a_gift_transaction_number_i AS TransactionNumber, " +
"PUB_a_gift_detail.a_detail_number_i AS DetailNumber, " +
"PUB_a_gift_detail.a_confidential_gift_flag_l AS Confidential, " +
"PUB_a_gift_detail.a_gift_comment_one_c AS CommentOne, " +
"PUB_a_gift_detail.a_comment_one_type_c AS CommentOneType, " +
"PUB_a_gift_detail.a_gift_comment_two_c AS CommentTwo, " +
"PUB_a_gift_detail.a_comment_two_type_c AS CommentTwoType, " +
"PUB_a_gift_detail.a_gift_comment_three_c AS CommentThree, " +
"PUB_a_gift_detail.a_comment_three_type_c AS CommentThreeType " +
"FROM PUB_a_gift_batch " +
"LEFT JOIN PUB_a_gift ON PUB_a_gift_batch.a_batch_number_i = PUB_a_gift.a_batch_number_i AND PUB_a_gift_batch.a_ledger_number_i = PUB_a_gift.a_ledger_number_i "
+
"LEFT JOIN PUB_a_gift_detail on PUB_a_gift_detail.a_ledger_number_i = PUB_a_gift.a_ledger_number_i AND PUB_a_gift_detail.a_batch_number_i = PUB_a_gift.a_batch_number_i AND PUB_a_gift_detail.a_gift_transaction_number_i = PUB_a_gift.a_gift_transaction_number_i "
+
"LEFT JOIN PUB_a_motivation_detail ON PUB_a_motivation_detail.a_ledger_number_i = PUB_a_gift_detail.a_ledger_number_i AND PUB_a_motivation_detail.a_motivation_group_code_c = PUB_a_gift_detail.a_motivation_group_code_c AND PUB_a_motivation_detail.a_motivation_detail_code_c = PUB_a_gift_detail.a_motivation_detail_code_c "
+
"LEFT JOIN PUB_p_partner on PUB_a_gift.p_donor_key_n = PUB_p_partner.p_partner_key_n " +
"LEFT JOIN PUB_a_ledger on PUB_a_gift_batch.a_ledger_number_i = PUB_a_ledger.a_ledger_number_i " +
"WHERE PUB_a_gift_batch.a_ledger_number_i = " + Batch.LedgerNumber + " " +
"AND PUB_a_gift_batch.a_batch_number_i = " + Batch.BatchNumber + " " +
"AND PUB_a_motivation_detail.a_export_to_intranet_l = true " +
"ORDER BY PUB_a_gift.p_donor_key_n";
DataSet GiftBatchDS = DBAccess.GDBAccessObj.Select(GiftBatchQuery, "GiftBatchTbl", FTransaction);
foreach (DataRow Row in GiftBatchDS.Tables["GiftBatchTbl"].Rows)
{
Int32 RecipientKey = Convert.ToInt32(Row["RecipientKey"]);
Int32 RecipientFund = Batch.LedgerNumber;
// If no RecipientKey was specified, I need to get one:
if (RecipientKey == 0)
{
// If the Gift is on a foreign ledger,
// then RecipientKey is the PartnerKey of that ledger
ACostCentreTable CostCentreTbl = ACostCentreAccess.LoadByPrimaryKey(Batch.LedgerNumber,
Row["CostCentre"].ToString(), FTransaction);
if ((CostCentreTbl.Rows.Count == 1) && (CostCentreTbl[0].CostCentreType == "Foreign"))
{
AValidLedgerNumberTable ValidLedgerNumberTbl =
AValidLedgerNumberAccess.LoadViaACostCentre(Batch.LedgerNumber, Row["CostCentre"].ToString(), FTransaction);
if (ValidLedgerNumberTbl.Rows.Count == 1)
{
RecipientKey = Convert.ToInt32(ValidLedgerNumberTbl[0].PartnerKey);
RecipientFund = RecipientKey;
}
}
}
// Otherwise, I can derive a RecipientKey from the Ledger Number.
if (RecipientKey == 0)
{
RecipientKey = Batch.LedgerNumber * 1000000;
RecipientFund = RecipientKey;
}
GetDonor(Convert.ToInt32(Row["DonorKey"])); // This adds the Donor to my list if it's not already present.
GetRecipient(RecipientKey); // This adds the recipient to my list if it's not already present.
decimal GIFFee;
decimal ICTFee;
decimal OtherFee;
GetAdminFees(Batch.LedgerNumber,
Convert.ToInt32(Row["BatchNumber"]), Convert.ToInt32(Row["TransactionNumber"]), Convert.ToInt32(Row["DetailNumber"]),
out GIFFee, out ICTFee, out OtherFee);
sw2.WriteLine(String.Format(
"{0:D10},{1:D10},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},\"{12},{13},{14},{15}\",{16:D10},{17:D10},{18},\"{19}\",\"{20}\",\"{21}\",\"{22}\",\"{23}\",\"{24}\"",
Row["DonorKey"], // 0
RecipientKey, // 1
Row["TransactionAmount"], // 2
Row["CurrencyCode"], // 3
Row["GiftAmount"], // 4
GIFFee, // 5
ICTFee, // 6
OtherFee, // 7
Row["BaseCurrency"], // 8
Row["IntlAmount"], // 9
Row["IntlCurrency"], // 10
PutDate(Row["EffectiveDate"]), // 11
Batch.LedgerNumber, // 12
Row["BatchNumber"], // 13
Row["TransactionNumber"], // 14
Row["DetailNumber"], // 15
Batch.LedgerNumber * 1000000, // 16
RecipientFund, // 17
Row["Confidential"], // 18
Row["CommentOne"], // 19
Row["CommentOneType"], // 20
Row["CommentTwo"], // 21
Row["CommentTwoType"], // 22
Row["CommentThree"], // 23
Row["CommentThreeType"] // 24
));
}
}
foreach (Int64 PartnerKey in DonorList.Keys)
{
PartnerDetails Row = DonorList[PartnerKey];
sw1.WriteLine(String.Format("\"{0}\",\"{1}\",{2:D10},\"{3}\",\"{4}\",{5},{6},{7}",
Row.FirstName, Row.LastName, PartnerKey, Row.Email, Row.Address, Row.Telephone, Row.Anonymous ? "true" : "false", Row.Class));
}
sw1.Close();
sw2.Close();
StreamWriter sw3 = File.CreateText(FExportFilePath + "recipient.csv");
sw3.WriteLine("recipient,first_name,last_name,email,class");
foreach (Int64 PartnerKey in RecipientList.Keys)
{
PartnerDetails Row = RecipientList[PartnerKey];
sw3.WriteLine(String.Format("{0},\"{1}\",\"{2}\",\"{3}\",{4}", PartnerKey, Row.FirstName, Row.LastName, Row.Email, Row.Class));
}
sw3.Close();
return(true);
}
