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); }