public static bool GenerateICHStewardshipBatch(int ALedgerNumber,
int APeriodNumber,
ref TVerificationResultCollection AVerificationResult)
{
string StandardCostCentre = TLedgerInfo.GetStandardCostCentre(ALedgerNumber);
bool IsSuccessful = false;
bool DrCrIndicator = true;
bool IncomeDrCrIndicator;
bool ExpenseDrCrIndicator;
bool AccountDrCrIndicator;
string IncomeAccounts = string.Empty;
string ExpenseAccounts = string.Empty;
//Error handling
string ErrorContext = String.Empty;
string ErrorMessage = String.Empty;
//Set default type as non-critical
TResultSeverity ErrorType = TResultSeverity.Resv_Noncritical;
bool NewTransaction = false;
TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);
//Generating the ICH batch...
try
{
DateTime PeriodStartDate;
DateTime PeriodEndDate;
TFinancialYear.GetStartAndEndDateOfPeriod(ALedgerNumber, APeriodNumber, out PeriodStartDate, out PeriodEndDate, DBTransaction);
String strPeriodStartDate = "#" + PeriodStartDate.ToString("yyyy-MM-dd") + "#";
String strPeriodEndDate = "#" + PeriodEndDate.ToString("yyyy-MM-dd") + "#";
AGiftBatchTable GiftBatchTable = new AGiftBatchTable();
String GiftQuery = "SELECT * FROM a_gift_batch WHERE " +
AGiftBatchTable.GetLedgerNumberDBName() + " = " + ALedgerNumber +
" AND " + AGiftBatchTable.GetBatchStatusDBName() + " = '" + MFinanceConstants.BATCH_POSTED + "'" +
" AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " >= " + strPeriodStartDate +
" AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " <= " + strPeriodEndDate +
" ORDER BY " + AGiftBatchTable.GetBatchNumberDBName();
DBAccess.GDBAccessObj.SelectDT(GiftBatchTable, GiftQuery, DBTransaction);
//Create a new batch. If it turns out I don't need one, I can delete it later.
GLBatchTDS MainDS = TGLPosting.CreateABatch(ALedgerNumber, Catalog.GetString("ICH Stewardship"), 0, PeriodEndDate);
ABatchRow NewBatchRow = MainDS.ABatch[0];
int GLBatchNumber = NewBatchRow.BatchNumber;
//Load tables needed: AccountingPeriod, Ledger, Account, Cost Centre, Transaction, Gift Batch, ICHStewardship
GLPostingTDS PostingDS = new GLPostingTDS();
ALedgerAccess.LoadByPrimaryKey(PostingDS, ALedgerNumber, DBTransaction);
AAccountAccess.LoadViaALedger(PostingDS, ALedgerNumber, DBTransaction);
AIchStewardshipAccess.LoadViaALedger(PostingDS, ALedgerNumber, DBTransaction);
AAccountHierarchyAccess.LoadViaALedger(PostingDS, ALedgerNumber, DBTransaction);
ABatchTable BatchTable = new ABatchTable();
ABatchRow BatchTemplateRow = (ABatchRow)BatchTable.NewRowTyped(false);
BatchTemplateRow.LedgerNumber = ALedgerNumber;
BatchTemplateRow.BatchPeriod = APeriodNumber;
StringCollection Operators0 = StringHelper.InitStrArr(new string[] { "=", "=" });
StringCollection OrderList0 = new StringCollection();
OrderList0.Add("ORDER BY");
OrderList0.Add(ABatchTable.GetBatchNumberDBName() + " DESC");
ABatchTable BatchesInAPeriod = ABatchAccess.LoadUsingTemplate(BatchTemplateRow,
Operators0,
null,
DBTransaction,
OrderList0,
0,
0);
if (BatchesInAPeriod != null)
{
int BatchNumber = 0;
for (int i = 0; i < BatchesInAPeriod.Count; i++)
{
ABatchRow batchRow = (ABatchRow)BatchesInAPeriod.Rows[i];
BatchNumber = batchRow.BatchNumber;
AJournalAccess.LoadViaABatch(MainDS, ALedgerNumber, BatchNumber, DBTransaction);
ATransactionAccess.LoadViaABatch(MainDS, ALedgerNumber, BatchNumber, DBTransaction);
}
}
else
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("No Batches found to process in Ledger: {0}"),
ALedgerNumber);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
ALedgerRow LedgerRow = (ALedgerRow)PostingDS.ALedger.Rows[0];
//Create a new journal in the Batch
//Run gl1120o.p
AJournalRow NewJournalRow = MainDS.AJournal.NewRowTyped();
NewJournalRow.LedgerNumber = ALedgerNumber;
NewJournalRow.BatchNumber = GLBatchNumber;
NewJournalRow.JournalNumber = ++NewBatchRow.LastJournal;
NewJournalRow.JournalDescription = NewBatchRow.BatchDescription;
NewJournalRow.SubSystemCode = MFinanceConstants.SUB_SYSTEM_GL;
NewJournalRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.STD.ToString();
NewJournalRow.TransactionCurrency = LedgerRow.BaseCurrency;
NewJournalRow.ExchangeRateToBase = 1;
NewJournalRow.DateEffective = PeriodEndDate;
NewJournalRow.JournalPeriod = APeriodNumber;
MainDS.AJournal.Rows.Add(NewJournalRow);
int GLJournalNumber = NewJournalRow.JournalNumber;
int GLTransactionNumber = NewJournalRow.LastTransactionNumber + 1;
// ***************************
// Generate the transactions
// ***************************
AAccountRow AccountRow = (AAccountRow)PostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, MFinanceConstants.INCOME_HEADING });
//Process income accounts
if (AccountRow != null)
{
IncomeDrCrIndicator = AccountRow.DebitCreditIndicator;
}
else
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("Income Account header: '{1}' not found in the accounts table for Ledger: {0}."),
ALedgerNumber,
MFinanceConstants.INCOME_HEADING);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
BuildChildAccountList(ALedgerNumber,
AccountRow,
DBTransaction,
ref IncomeAccounts,
ref AVerificationResult);
//Process expense accounts
AccountRow = (AAccountRow)PostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, MFinanceConstants.EXPENSE_HEADING });
if (AccountRow != null)
{
ExpenseDrCrIndicator = AccountRow.DebitCreditIndicator;
}
else
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("Expense Account header: '{1}' not found in the accounts table for Ledger: {0}."),
ALedgerNumber,
MFinanceConstants.EXPENSE_HEADING);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
BuildChildAccountList(ALedgerNumber,
AccountRow,
DBTransaction,
ref ExpenseAccounts,
ref AVerificationResult);
//Process P&L accounts
AccountRow = (AAccountRow)PostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, MFinanceConstants.PROFIT_AND_LOSS_HEADING });
if (AccountRow != null)
{
AccountDrCrIndicator = AccountRow.DebitCreditIndicator;
}
else
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("Profit & Loss Account header: '{1}' not found in the accounts table for Ledger: {0}."),
ALedgerNumber,
MFinanceConstants.PROFIT_AND_LOSS_HEADING);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
// find out the stewardship number - Ln 275
// Increment the Last ICH No.
int ICHProcessing = ++LedgerRow.LastIchNumber;
decimal ICHTotal = 0;
bool PostICHBatch = false;
ACostCentreRow CCTemplateRow = PostingDS.ACostCentre.NewRowTyped(false);
CCTemplateRow.LedgerNumber = ALedgerNumber;
CCTemplateRow.PostingCostCentreFlag = true;
CCTemplateRow.CostCentreType = MFinanceConstants.FOREIGN_CC_TYPE;
ACostCentreAccess.LoadUsingTemplate(PostingDS, CCTemplateRow, DBTransaction);
//Iterate through the cost centres
// string OrderBy = ACostCentreTable.GetCostCentreCodeDBName();
StringDictionary DestinationAccount = GetDestinationAccountCodes(ALedgerNumber, PostingDS.ACostCentre, DBTransaction);
AIchStewardshipTable ICHStewardshipTable = new AIchStewardshipTable();
Boolean NonIchTransactionsIncluded = false;
String JournalRowOrder = "a_journal_number_i";
String TransRowOrder = "a_batch_number_i,a_journal_number_i,a_transaction_number_i";
foreach (ACostCentreRow CostCentreRow in PostingDS.ACostCentre.Rows)
{
string CostCentre = CostCentreRow.CostCentreCode;
//Initialise values for each Cost Centre
decimal SettlementAmount = 0;
decimal IncomeAmount = 0;
decimal ExpenseAmount = 0;
decimal XferAmount = 0;
decimal IncomeAmountIntl = 0;
decimal ExpenseAmountIntl = 0;
decimal XferAmountIntl = 0;
Boolean TransferFound = false;
/* 0008 Go through all of the transactions. Ln:301 */
String WhereClause = "a_cost_centre_code_c = '" + CostCentreRow.CostCentreCode +
"' AND a_transaction_status_l=true AND a_ich_number_i = 0";
DataRow[] FoundTransRows = MainDS.ATransaction.Select(WhereClause, TransRowOrder);
foreach (DataRow UntypedTransRow in FoundTransRows)
{
ATransactionRow TransRow = (ATransactionRow)UntypedTransRow;
DataRow[] FoundJournalRows = MainDS.AJournal.Select(
"a_batch_number_i = " + TransRow.BatchNumber + " AND a_journal_number_i = " + TransRow.JournalNumber,
JournalRowOrder);
if (FoundJournalRows != null)
{
TransferFound = true;
PostICHBatch = true;
TransRow.IchNumber = ICHProcessing;
if (TransRow.DebitCreditIndicator == AccountDrCrIndicator)
{
SettlementAmount -= TransRow.AmountInBaseCurrency;
}
else
{
SettlementAmount += TransRow.AmountInBaseCurrency;
}
//Process Income (ln:333)
if (IncomeAccounts.Contains(TransRow.AccountCode))
{
if (TransRow.DebitCreditIndicator == IncomeDrCrIndicator)
{
IncomeAmount += TransRow.AmountInBaseCurrency;
IncomeAmountIntl += TransRow.AmountInIntlCurrency;
}
else
{
IncomeAmount -= TransRow.AmountInBaseCurrency;
IncomeAmountIntl -= TransRow.AmountInIntlCurrency;
}
}
//process expenses
if (ExpenseAccounts.Contains(TransRow.AccountCode)
&& (TransRow.AccountCode != MFinanceConstants.DIRECT_XFER_ACCT)
&& (TransRow.AccountCode != MFinanceConstants.ICH_ACCT_SETTLEMENT))
{
if (TransRow.DebitCreditIndicator = ExpenseDrCrIndicator)
{
ExpenseAmount += TransRow.AmountInBaseCurrency;
ExpenseAmountIntl += TransRow.AmountInIntlCurrency;
}
else
{
ExpenseAmount -= TransRow.AmountInBaseCurrency;
ExpenseAmountIntl -= TransRow.AmountInIntlCurrency;
}
}
//Process Direct Transfers
if (TransRow.AccountCode == MFinanceConstants.DIRECT_XFER_ACCT)
{
if (TransRow.DebitCreditIndicator == ExpenseDrCrIndicator)
{
XferAmount += TransRow.AmountInBaseCurrency;
XferAmountIntl += TransRow.AmountInIntlCurrency;
}
else
{
XferAmount -= TransRow.AmountInBaseCurrency;
XferAmountIntl -= TransRow.AmountInIntlCurrency;
}
}
}
} //end of foreach transaction
/* now mark all the gifts as processed */
if (TransferFound)
{
AGiftDetailTable GiftDetailTable = new AGiftDetailTable();
AGiftDetailRow GiftDetailTemplateRow = (AGiftDetailRow)GiftDetailTable.NewRowTyped(false);
GiftDetailTemplateRow.LedgerNumber = ALedgerNumber;
GiftDetailTemplateRow.IchNumber = 0;
GiftDetailTemplateRow.CostCentreCode = CostCentreRow.CostCentreCode;
foreach (AGiftBatchRow GiftBatchRow in GiftBatchTable.Rows)
{
GiftDetailTemplateRow.BatchNumber = GiftBatchRow.BatchNumber;
GiftDetailTable = AGiftDetailAccess.LoadUsingTemplate(GiftDetailTemplateRow, DBTransaction);
foreach (AGiftDetailRow GiftDetailRow in GiftDetailTable.Rows)
{
GiftDetailRow.IchNumber = ICHProcessing;
}
}
} // if TransferFound
if ((SettlementAmount == 0) // If there's no activity in this CC,
&& (IncomeAmount == 0) // bail to the next one.
&& (ExpenseAmount == 0)
&& (XferAmount == 0))
{
continue;
}
/* Balance the cost centre by entering an opposite transaction
* to ICH settlement. Use positive amounts only.
*/
/* Increment or decrement the ICH total to be transferred after this loop.
* NOTE - if this is a "non-ICH fund", I need to balance it separately, and I'll do that right here.
*/
DrCrIndicator = AccountRow.DebitCreditIndicator;
if (DestinationAccount[CostCentreRow.CostCentreCode] == MFinanceConstants.ICH_ACCT_ICH)
{
if (DrCrIndicator == MFinanceConstants.IS_DEBIT)
{
ICHTotal += SettlementAmount;
}
else
{
ICHTotal -= SettlementAmount;
}
}
DrCrIndicator = AccountDrCrIndicator;
if (SettlementAmount < 0)
{
DrCrIndicator = !AccountDrCrIndicator;
SettlementAmount = 0 - SettlementAmount;
}
if ((DestinationAccount[CostCentreRow.CostCentreCode] != MFinanceConstants.ICH_ACCT_ICH) && (SettlementAmount != 0))
{
// I'm creating a transaction right here for this "non-ICH" CostCentre.
// This potentially means that there will be multiple transactions to the "non-ICH" account,
// whereas the ICH account has only a single transaction, but that's not big deal:
if (!TGLPosting.CreateATransaction(MainDS, ALedgerNumber, GLBatchNumber, GLJournalNumber,
Catalog.GetString("Non-ICH foreign fund Clearing"),
DestinationAccount[CostCentreRow.CostCentreCode],
StandardCostCentre, SettlementAmount, PeriodEndDate, !DrCrIndicator, Catalog.GetString("Non-ICH"),
true, SettlementAmount,
out GLTransactionNumber))
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("Unable to create a new transaction for Ledger {0}, Batch {1} and Journal {2}."),
ALedgerNumber,
GLBatchNumber,
GLJournalNumber);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
NonIchTransactionsIncluded = true;
}
/* Generate the transaction to 'balance' the foreign fund -
* in the ICH settlement account.
*/
//RUN gl1130o.p ("new":U,
//Create a transaction
if (SettlementAmount > 0)
{
if (!TGLPosting.CreateATransaction(MainDS, ALedgerNumber, GLBatchNumber, GLJournalNumber,
Catalog.GetString("ICH Monthly Clearing"),
MFinanceConstants.ICH_ACCT_SETTLEMENT, // DestinationAccount[CostCentreRow.CostCentreCode],
CostCentreRow.CostCentreCode, SettlementAmount, PeriodEndDate, DrCrIndicator,
Catalog.GetString("ICH Process"), true, SettlementAmount,
out GLTransactionNumber))
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("Unable to create a new transaction for Ledger {0}, Batch {1} and Journal {2}."),
ALedgerNumber,
GLBatchNumber,
GLJournalNumber);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
//Mark as processed
ATransactionRow TransRow =
(ATransactionRow)MainDS.ATransaction.Rows.Find(new object[] { ALedgerNumber, GLBatchNumber, GLJournalNumber,
GLTransactionNumber });
TransRow.IchNumber = ICHProcessing;
}
/* Now create corresponding report row on stewardship table,
* Only for Cost Centres that cleared to ICH
*/
if ((DestinationAccount[CostCentreRow.CostCentreCode] == MFinanceConstants.ICH_ACCT_ICH)
&& ((IncomeAmount != 0)
|| (ExpenseAmount != 0)
|| (XferAmount != 0)))
{
AIchStewardshipRow ICHStewardshipRow = ICHStewardshipTable.NewRowTyped(true);
//MainDS.Tables.Add(IchStewardshipTable);
ICHStewardshipRow.LedgerNumber = ALedgerNumber;
ICHStewardshipRow.PeriodNumber = APeriodNumber;
ICHStewardshipRow.IchNumber = ICHProcessing;
// ICHStewardshipRow.DateProcessed = DateTime.Today; // This would be strictly correct, but the Stewardship Reporting looks for
ICHStewardshipRow.DateProcessed = PeriodEndDate; // rows using a date filter.
ICHStewardshipRow.CostCentreCode = CostCentreRow.CostCentreCode;
ICHStewardshipRow.IncomeAmount = IncomeAmount;
ICHStewardshipRow.ExpenseAmount = ExpenseAmount;
ICHStewardshipRow.DirectXferAmount = XferAmount;
ICHStewardshipRow.IncomeAmountIntl = IncomeAmountIntl;
ICHStewardshipRow.ExpenseAmountIntl = ExpenseAmountIntl;
ICHStewardshipRow.DirectXferAmountIntl = XferAmountIntl;
ICHStewardshipTable.Rows.Add(ICHStewardshipRow);
}
} // for each cost centre
/* Update the balance of the ICH account (like a bank account).
* If the total is negative, it means the ICH batch has a
* credit total so far. Thus, we now balance it with the opposite
* transaction. */
if (ICHTotal < 0)
{
DrCrIndicator = MFinanceConstants.IS_DEBIT;
ICHTotal = -ICHTotal;
}
else if (ICHTotal > 0)
{
DrCrIndicator = MFinanceConstants.IS_CREDIT;
}
/* 0006 - If the balance is 0 then this is ok
* (eg last minute change of a gift from one field to another)
*/
if ((ICHTotal == 0) && !NonIchTransactionsIncluded)
{
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Generating the ICH batch"),
Catalog.GetString("No ICH batch was required."), TResultSeverity.Resv_Status));
// An empty GL Batch now exists, which I need to delete.
//
TVerificationResultCollection BatchCancelResult = new TVerificationResultCollection();
TGLPosting.DeleteGLBatch(
ALedgerNumber,
GLBatchNumber,
out BatchCancelResult);
AVerificationResult.AddCollection(BatchCancelResult);
IsSuccessful = true;
}
else
{
if (ICHTotal != 0)
{
//Create a transaction
if (!TGLPosting.CreateATransaction(MainDS, ALedgerNumber, GLBatchNumber, GLJournalNumber,
Catalog.GetString("ICH Monthly Clearing"),
MFinanceConstants.ICH_ACCT_ICH, StandardCostCentre, ICHTotal, PeriodEndDate, DrCrIndicator, Catalog.GetString("ICH"),
true, ICHTotal,
out GLTransactionNumber))
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("Unable to create a new transaction for Ledger {0}, Batch {1} and Journal {2}."),
ALedgerNumber,
GLBatchNumber,
GLJournalNumber);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
}
//Post the batch
if (PostICHBatch)
{
AIchStewardshipAccess.SubmitChanges(ICHStewardshipTable, DBTransaction);
MainDS.ThrowAwayAfterSubmitChanges = true; // SubmitChanges will not return to me any changes made in MainDS.
GLBatchTDSAccess.SubmitChanges(MainDS);
ALedgerAccess.SubmitChanges(PostingDS.ALedger, DBTransaction); // LastIchNumber has changed.
IsSuccessful = TGLPosting.PostGLBatch(ALedgerNumber, GLBatchNumber, out AVerificationResult);
}
else
{
AVerificationResult.Add(new TVerificationResult(ErrorContext,
Catalog.GetString("No Stewardship batch is required."),
TResultSeverity.Resv_Status));
// An empty GL Batch now exists, which I need to delete.
//
TVerificationResultCollection BatchCancelResult = new TVerificationResultCollection();
TGLPosting.DeleteGLBatch(
ALedgerNumber,
GLBatchNumber,
out BatchCancelResult);
AVerificationResult.AddCollection(BatchCancelResult);
} // else
} // else
} // try
catch (ArgumentException Exc)
{
TLogging.Log("An ArgumentException occured during the generation of the Stewardship Batch:" + Environment.NewLine + Exc.ToString());
if (AVerificationResult == null)
{
AVerificationResult = new TVerificationResultCollection();
}
AVerificationResult.Add(new TVerificationResult(ErrorContext, Exc.Message, ErrorType));
throw;
}
catch (InvalidOperationException Exc)
{
TLogging.Log(
"An InvalidOperationException occured during the generation of the Stewardship Batch:" + Environment.NewLine + Exc.ToString());
if (AVerificationResult == null)
{
AVerificationResult = new TVerificationResultCollection();
}
AVerificationResult.Add(new TVerificationResult(ErrorContext, Exc.Message, ErrorType));
throw;
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the generation of the Stewardship Batch:" + Environment.NewLine + Exc.ToString());
ErrorContext = Catalog.GetString("Calculate Admin Fee");
ErrorMessage = String.Format(Catalog.GetString("Unknown error while generating the ICH batch for Ledger: {0} and Period: {1}" +
Environment.NewLine + Environment.NewLine + Exc.ToString()),
ALedgerNumber,
APeriodNumber);
ErrorType = TResultSeverity.Resv_Critical;
if (AVerificationResult == null)
{
AVerificationResult = new TVerificationResultCollection();
}
AVerificationResult.Add(new TVerificationResult(ErrorContext, ErrorMessage, ErrorType));
throw;
}
finally
{
if (IsSuccessful && NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
else if (!IsSuccessful && NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
return IsSuccessful;
}
/// <summary>
/// export a number of gift batches to a file
/// </summary>
public static void ExportGiftBatches(string AOutputPath)
{
// store all gift batches in first period of current year
string SelectGiftBatchesJanuaryFromAndWhere =
"FROM PUB_a_ledger, PUB_a_gift_batch, PUB_a_gift, PUB_a_gift_detail, " +
" PUB_p_partner donor, PUB_p_partner_banking_details, PUB_p_banking_details_usage, PUB_p_banking_details, PUB_p_bank bank " +
"WHERE PUB_a_ledger.a_ledger_number_i = " + FLedgerNumber.ToString() + " " +
"AND PUB_a_gift_batch.a_ledger_number_i = PUB_a_ledger.a_ledger_number_i " +
"AND PUB_a_gift_batch.a_batch_year_i = PUB_a_ledger.a_current_financial_year_i " +
"AND PUB_a_gift_batch.a_batch_period_i = 1 " +
"AND PUB_a_gift.a_ledger_number_i = PUB_a_gift_batch.a_ledger_number_i " +
"AND PUB_a_gift.a_batch_number_i = PUB_a_gift_batch.a_batch_number_i " +
"AND 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 " +
"AND donor.p_partner_key_n = PUB_a_gift.p_donor_key_n " +
"AND PUB_p_partner_banking_details.p_partner_key_n = PUB_a_gift.p_donor_key_n " +
"AND PUB_p_banking_details_usage.p_partner_key_n = PUB_a_gift.p_donor_key_n " +
"AND PUB_p_banking_details_usage.p_banking_details_key_i = PUB_p_partner_banking_details.p_banking_details_key_i " +
"AND PUB_p_banking_details_usage.p_type_c = 'MAIN' " +
"AND PUB_p_banking_details.p_banking_details_key_i = PUB_p_partner_banking_details.p_banking_details_key_i " +
"AND bank.p_partner_key_n = PUB_p_banking_details.p_bank_key_n ";
BankImportTDS MainDS = new BankImportTDS();
AGiftBatchTable batches = new AGiftBatchTable();
string SelectGiftBatchesJanuary =
"SELECT DISTINCT PUB_a_gift_batch.* " + SelectGiftBatchesJanuaryFromAndWhere;
DBAccess.GDBAccessObj.SelectDT(batches, SelectGiftBatchesJanuary, null, new OdbcParameter[0], 0, 0);
string SelectGiftDetailsJanuary =
"SELECT DISTINCT PUB_a_gift_detail.*, donor.p_partner_key_n AS DonorKey " + SelectGiftBatchesJanuaryFromAndWhere;
DBAccess.GDBAccessObj.Select(MainDS, SelectGiftDetailsJanuary, MainDS.AGiftDetail.TableName, null);
// get all banking details of donors involved in this gift batch
string SelectBankingDetailsJanuary =
"SELECT DISTINCT PUB_p_banking_details.*, bank.p_branch_code_c AS BankSortCode, donor.p_partner_key_n AS PartnerKey " +
SelectGiftBatchesJanuaryFromAndWhere;
DBAccess.GDBAccessObj.Select(MainDS, SelectBankingDetailsJanuary, MainDS.PBankingDetails.TableName, null);
MainDS.AGiftDetail.DefaultView.Sort = AGiftDetailTable.GetBatchNumberDBName();
MainDS.PBankingDetails.DefaultView.Sort = BankImportTDSPBankingDetailsTable.GetPartnerKeyDBName();
int bankStatementCounter = 1000;
decimal balance = 34304.33m;
foreach (AGiftBatchRow batch in batches.Rows)
{
StoreMT940File(MainDS, AOutputPath, batch.BatchNumber, batch.GlEffectiveDate, bankStatementCounter, ref balance);
bankStatementCounter++;
}
}
private int GetChangedRecordCountManual(out string AMessage)
{
//For Gift Batch we will get a mix of some batches, gifts and gift details.
// Only check relevant tables.
List <string> TablesToCheck = new List <string>();
TablesToCheck.Add(FMainDS.AGiftBatch.TableName);
TablesToCheck.Add(FMainDS.AGift.TableName);
TablesToCheck.Add(FMainDS.AGiftDetail.TableName);
List <Tuple <string, int> > TableAndCountList = new List <Tuple <string, int> >();
int AllChangesCount = 0;
if (FMainDS.HasChanges())
{
foreach (DataTable dt in FMainDS.GetChanges().Tables)
{
string currentTableName = dt.TableName;
if ((dt != null) &&
TablesToCheck.Contains(currentTableName) &&
(dt.Rows.Count > 0))
{
int tableChangesCount = 0;
DataTable dtChanges = dt.GetChanges();
foreach (DataRow dr in dtChanges.Rows)
{
if (DataUtilities.DataRowColumnsHaveChanged(dr))
{
tableChangesCount++;
AllChangesCount++;
}
}
if (tableChangesCount > 0)
{
TableAndCountList.Add(new Tuple <string, int>(currentTableName, tableChangesCount));
}
}
}
}
// Now build up a sensible message
AMessage = String.Empty;
if (TableAndCountList.Count > 0)
{
if (TableAndCountList.Count == 1)
{
Tuple <string, int> TableAndCount = TableAndCountList[0];
string tableName = TableAndCount.Item1;
if (TableAndCount.Item1.Equals(AGiftBatchTable.GetTableName()))
{
AMessage = String.Format(Catalog.GetString(" You have made changes to the details of {0} {1}.{2}"),
TableAndCount.Item2,
Catalog.GetPluralString("batch", "batches", TableAndCount.Item2),
Environment.NewLine);
}
else if (TableAndCount.Item1.Equals(AGiftTable.GetTableName()))
{
AMessage = String.Format(Catalog.GetString(" You have made changes to the details of {0} {1}.{2}"),
TableAndCount.Item2,
Catalog.GetPluralString("gift", "gifts", TableAndCount.Item2),
Environment.NewLine);
}
else //if (TableAndCount.Item1.Equals(AGiftDetailTable.GetTableName()))
{
AMessage = String.Format(Catalog.GetString(" You have made changes to {0} {1}.{2}"),
TableAndCount.Item2,
Catalog.GetPluralString("gift detail", "gift details", TableAndCount.Item2),
Environment.NewLine);
}
}
else
{
int nBatches = 0;
int nGifts = 0;
int nGiftDetails = 0;
foreach (Tuple <string, int> TableAndCount in TableAndCountList)
{
if (TableAndCount.Item1.Equals(AGiftBatchTable.GetTableName()))
{
nBatches = TableAndCount.Item2;
}
else if (TableAndCount.Item1.Equals(AGiftTable.GetTableName()))
{
nGifts = TableAndCount.Item2;
}
else //if (TableAndCount.Item1.Equals(AGiftDetailTable.GetTableName()))
{
nGiftDetails = TableAndCount.Item2;
}
}
if ((nBatches > 0) && (nGifts > 0) && (nGiftDetails > 0))
{
AMessage = String.Format(Catalog.GetString(" You have made changes to {0} {1}, {2} {3} and {4} {5}.{6}"),
nBatches,
Catalog.GetPluralString("batch", "batches", nBatches),
nGifts,
Catalog.GetPluralString("gift", "gifts", nGifts),
nGiftDetails,
Catalog.GetPluralString("gift detail", "gift details", nGiftDetails),
Environment.NewLine);
}
else if ((nBatches > 0) && (nGifts > 0) && (nGiftDetails == 0))
{
AMessage = String.Format(Catalog.GetString(" You have made changes to {0} {1} and {2} {3}.{4}"),
nBatches,
Catalog.GetPluralString("batch", "batches", nBatches),
nGifts,
Catalog.GetPluralString("gift", "gifts", nGifts),
Environment.NewLine);
}
else if ((nBatches > 0) && (nGifts == 0) && (nGiftDetails > 0))
{
AMessage = String.Format(Catalog.GetString(" You have made changes to {0} {1} and {2} {3}.{4}"),
nBatches,
Catalog.GetPluralString("batch", "batches", nBatches),
nGiftDetails,
Catalog.GetPluralString("gift detail", "gift details", nGiftDetails),
Environment.NewLine);
}
else if ((nBatches > 0) && (nGifts == 0) && (nGiftDetails == 0))
{
AMessage = String.Format(Catalog.GetString(" You have made changes to {0} {1}.{2}"),
nBatches,
Catalog.GetPluralString("batch", "batches", nBatches),
Environment.NewLine);
}
else if ((nBatches == 0) && (nGifts > 0) && (nGiftDetails > 0))
{
AMessage = String.Format(Catalog.GetString(" You have made changes to {0} {1} and {2} {3}.{4}"),
nGifts,
Catalog.GetPluralString("gift", "gifts", nGifts),
nGiftDetails,
Catalog.GetPluralString("gift detail", "gift details", nGiftDetails),
Environment.NewLine);
}
else if ((nBatches == 0) && (nGifts > 0) && (nGiftDetails == 0))
{
AMessage = String.Format(Catalog.GetString(" You have made changes to {0} {1}.{2}"),
nGifts,
Catalog.GetPluralString("gift", "gifts", nGiftDetails),
Environment.NewLine);
}
else if ((nBatches == 0) && (nGifts == 0) && (nGiftDetails > 0))
{
AMessage = String.Format(Catalog.GetString(" You have made changes to {0} {1}.{2}"),
nGiftDetails,
Catalog.GetPluralString("gift detail", "gift details", nGiftDetails),
Environment.NewLine);
}
}
AMessage += Catalog.GetString("(some of the changes may include related background items)");
AMessage += Environment.NewLine;
AMessage += String.Format(TFrmPetraEditUtils.StrConsequenceIfNotSaved, Environment.NewLine);
}
return(AllChangesCount);
}
/// <summary>
/// export all GL Transactions in the given year, towards the specified cost centres
/// </summary>
public static void ExportGLTransactions(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
Int32 AFinancialYear,
string ACostCentres,
string AIgnoreAccounts,
string AIgnoreReferences,
ref List <string> ACostCentresInvolved,
ref List <string> AAccountsInvolved)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "transaction.csv"));
Console.WriteLine("Writing file: " + filename);
TDBTransaction Transaction = new TDBTransaction();
ATransactionTable transactions = new ATransactionTable();
ATransAnalAttribTable TransAnalAttrib = new ATransAnalAttribTable();
ATransactionTable allTransactionsInJournal = new ATransactionTable();
AGiftBatchTable giftbatches = new AGiftBatchTable();
AAccountTable accounts = new AAccountTable();
DBAccess.ReadTransaction(ref Transaction,
delegate
{
TDataBase db = Transaction.DataBaseObj;
string sql =
String.Format("SELECT T.*, B.{4} AS a_transaction_date_d " +
"FROM PUB_{8} AS B, PUB_{7} AS T " +
"WHERE B.{9} = {10} AND B.{15} = {16} AND B.{11}='{12}' " +
"AND T.{9} = B.{9} AND T.{0} = B.{0} " +
"AND T.{13} IN ({14}) " +
"AND NOT T.{17} IN ({19}) " +
"AND NOT T.{20} IN ({21}) " +
"ORDER BY {0}, {1}, {2}",
ATransactionTable.GetBatchNumberDBName(),
ATransactionTable.GetJournalNumberDBName(),
ATransactionTable.GetTransactionNumberDBName(),
ATransactionTable.GetTransactionAmountDBName(),
ABatchTable.GetDateEffectiveDBName(),
ATransactionTable.GetNarrativeDBName(),
ATransactionTable.GetReferenceDBName(),
ATransactionTable.GetTableDBName(),
ABatchTable.GetTableDBName(),
ATransactionTable.GetLedgerNumberDBName(),
ALedgerNumber,
ABatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
ATransactionTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
ABatchTable.GetBatchYearDBName(),
AFinancialYear,
ATransactionTable.GetAccountCodeDBName(),
ATransactionTable.GetDebitCreditIndicatorDBName(),
"'" + AIgnoreAccounts.Replace(",", "','") + "'",
ATransactionTable.GetReferenceDBName(),
"'" + AIgnoreReferences.Replace(",", "','") + "'");
transactions = (ATransactionTable)db.SelectDT(transactions, sql, Transaction, null, 0, 0);
// get the analysis attributes
sql =
String.Format("SELECT A.* from PUB_{1} AS B, PUB_{13} AS T, PUB_{0} AS A " +
"WHERE B.{2} = {3} AND B.{4} = {5} AND B.{6}='{7}' " +
"AND T.{2} = B.{2} AND T.{8} = B.{8} " +
"AND T.{9} IN ({10}) " +
"AND A.{2} = T.{2} AND A.{8} = T.{8} AND A.{11} = T.{11} AND A.{12} = T.{12}",
ATransAnalAttribTable.GetTableDBName(),
ABatchTable.GetTableDBName(),
ABatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
ABatchTable.GetBatchYearDBName(),
AFinancialYear,
ABatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
ATransactionTable.GetBatchNumberDBName(),
ATransactionTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
ATransactionTable.GetJournalNumberDBName(),
ATransactionTable.GetTransactionNumberDBName(),
ATransactionTable.GetTableDBName(),
ABatchTable.GetBatchYearDBName());
db.SelectDT(TransAnalAttrib, sql, Transaction, null, 0, 0);
TransAnalAttrib.DefaultView.Sort =
ATransAnalAttribTable.GetBatchNumberDBName() + "," +
ATransAnalAttribTable.GetJournalNumberDBName() + "," +
ATransAnalAttribTable.GetTransactionNumberDBName();
// get a list of all batches involved
List <Int64> batches = new List <Int64>();
StringBuilder batchnumbers = new StringBuilder();
foreach (ATransactionRow r in transactions.Rows)
{
if (!batches.Contains(r.BatchNumber))
{
batches.Add(r.BatchNumber);
batchnumbers.Append(r.BatchNumber.ToString() + ",");
}
}
// get the other transactions in the same journal for finding the opposite cc/acc involved
// for performance reasons, get all transactions of the whole batch
sql =
String.Format("SELECT DISTINCT TJ.* " +
"FROM PUB_{0} AS TJ " +
"WHERE TJ.{1} = {2} AND TJ.{3} IN ({4})",
ATransactionTable.GetTableDBName(),
ATransactionTable.GetLedgerNumberDBName(),
ALedgerNumber,
ATransactionTable.GetBatchNumberDBName(),
batchnumbers.ToString() + "-1");
allTransactionsInJournal =
(ATransactionTable)db.SelectDT(allTransactionsInJournal, sql, Transaction, null, 0, 0);
allTransactionsInJournal.DefaultView.Sort =
ATransactionTable.GetBatchNumberDBName() + "," +
ATransactionTable.GetJournalNumberDBName();
// get all names of gift batches
sql =
String.Format("SELECT * FROM PUB_{0} " +
"WHERE {1} = {2} " +
"AND {3} = {4}",
AGiftBatchTable.GetTableDBName(),
AGiftBatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGiftBatchTable.GetBatchYearDBName(),
AFinancialYear);
db.SelectDT(giftbatches, sql, Transaction, null, 0, 0);
giftbatches.DefaultView.Sort = AGiftBatchTable.GetBatchNumberDBName();
sql =
String.Format("SELECT * FROM PUB_{0} " +
"WHERE {1} = {2}",
AAccountTable.GetTableDBName(),
AAccountTable.GetLedgerNumberDBName(),
ALedgerNumber);
db.SelectDT(accounts, sql, Transaction, null, 0, 0);
accounts.DefaultView.Sort = AAccountTable.GetAccountCodeDBName();
});
StringBuilder sb = new StringBuilder();
int rowCounter = 0;
foreach (ATransactionRow row in transactions.Rows)
{
if (row.DebitCreditIndicator)
{
row.TransactionAmount *= -1.0m;
}
StringBuilder attributes = new StringBuilder();
DataRowView[] RelatedTransactions = allTransactionsInJournal.DefaultView.FindRows(new object[] { row.BatchNumber, row.JournalNumber });
ATransactionRow[] OtherTransactions = GetOtherTransactions(row, RelatedTransactions);
string OtherCostCentres = string.Empty;
string OtherAccountCodes = string.Empty;
if (OtherTransactions.Length < 30)
{
foreach (ATransactionRow r in OtherTransactions)
{
OtherCostCentres = StringHelper.AddCSV(OtherCostCentres, r.CostCentreCode);
OtherAccountCodes = StringHelper.AddCSV(OtherAccountCodes, r.AccountCode);
}
}
if (!ACostCentresInvolved.Contains(row.CostCentreCode))
{
ACostCentresInvolved.Add(row.CostCentreCode);
}
if (!AAccountsInvolved.Contains(row.AccountCode))
{
AAccountsInvolved.Add(row.AccountCode);
}
// we are using gift batch for receiving payments
string Narrative = row.Narrative;
if (Narrative.StartsWith("GB - Gift Batch ") && row.Reference.StartsWith("GB"))
{
// find the account and set the account description into the narrative
try
{
DataRowView[] acc = accounts.DefaultView.FindRows(row.AccountCode);
Narrative = ((AAccountRow)acc[0].Row).AccountCodeLongDesc;
}
catch (Exception)
{
}
try
{
DataRowView[] gb = giftbatches.DefaultView.FindRows(Convert.ToInt32(row.Reference.Substring(2)));
Narrative += " " + ((AGiftBatchRow)gb[0].Row).BatchDescription;
}
catch (Exception)
{
}
}
sb.Append(StringHelper.StrMerge(
new string[] {
"B" + row.BatchNumber.ToString() + "_J" + row.JournalNumber.ToString() + "_T" + row.TransactionNumber.ToString(),
row.CostCentreCode,
row.AccountCode,
row.TransactionDate.ToString("yyyyMMdd"),
OtherCostCentres,
OtherAccountCodes,
Narrative,
row.Reference,
String.Format("{0:N}", row.TransactionAmount),
attributes.ToString()
}, ACSVSeparator));
sb.Append(ANewLine);
rowCounter++;
if (rowCounter % 500 == 0)
{
TLogging.Log("Processing transactions " + rowCounter.ToString());
}
}
TLogging.Log("Processing transactions " + rowCounter.ToString());
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
/// <summary>
/// export all GL Transactions in the given year, towards the specified cost centres
/// </summary>
public static void ExportGLTransactions(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
Int32 AFinancialYear,
string ACostCentres,
string AIgnoreAccounts,
string AIgnoreReferences,
SortedList <string, string>ATaxAnalysisAttributes,
ref List <string>ACostCentresInvolved,
ref List <string>AAccountsInvolved)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "transaction.csv"));
Console.WriteLine("Writing file: " + filename);
StringBuilder sb = new StringBuilder();
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
string sql =
String.Format("SELECT T.*, B.{4} AS a_transaction_date_d " +
"FROM PUB_{8} AS B, PUB_{7} AS T " +
"WHERE B.{9} = {10} AND B.{15} = {16} AND B.{11}='{12}' " +
"AND T.{9} = B.{9} AND T.{0} = B.{0} " +
"AND T.{13} IN ({14}) " +
"AND NOT T.{17} IN ({19}) " +
"AND NOT T.{20} IN ({21}) " +
"ORDER BY {0}, {1}, {2}",
ATransactionTable.GetBatchNumberDBName(),
ATransactionTable.GetJournalNumberDBName(),
ATransactionTable.GetTransactionNumberDBName(),
ATransactionTable.GetTransactionAmountDBName(),
ABatchTable.GetDateEffectiveDBName(),
ATransactionTable.GetNarrativeDBName(),
ATransactionTable.GetReferenceDBName(),
ATransactionTable.GetTableDBName(),
ABatchTable.GetTableDBName(),
ATransactionTable.GetLedgerNumberDBName(),
ALedgerNumber,
ABatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
ATransactionTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
ABatchTable.GetBatchYearDBName(),
AFinancialYear,
ATransactionTable.GetAccountCodeDBName(),
ATransactionTable.GetDebitCreditIndicatorDBName(),
"'" + AIgnoreAccounts.Replace(",", "','") + "'",
ATransactionTable.GetReferenceDBName(),
"'" + AIgnoreReferences.Replace(",", "','") + "'");
ATransactionTable transactions = new ATransactionTable();
transactions = (ATransactionTable)DBAccess.GDBAccessObj.SelectDT(transactions, sql, Transaction, null, 0, 0);
// get the analysis attributes
sql =
String.Format("SELECT A.* from PUB_{1} AS B, PUB_{13} AS T, PUB_{0} AS A " +
"WHERE B.{2} = {3} AND B.{4} = {5} AND B.{6}='{7}' " +
"AND T.{2} = B.{2} AND T.{8} = B.{8} " +
"AND T.{9} IN ({10}) " +
"AND A.{2} = T.{2} AND A.{8} = T.{8} AND A.{11} = T.{11} AND A.{12} = T.{12}",
ATransAnalAttribTable.GetTableDBName(),
ABatchTable.GetTableDBName(),
ABatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
ABatchTable.GetBatchYearDBName(),
AFinancialYear,
ABatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
ATransactionTable.GetBatchNumberDBName(),
ATransactionTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
ATransactionTable.GetJournalNumberDBName(),
ATransactionTable.GetTransactionNumberDBName(),
ATransactionTable.GetTableDBName(),
ABatchTable.GetBatchYearDBName());
ATransAnalAttribTable TransAnalAttrib = new ATransAnalAttribTable();
DBAccess.GDBAccessObj.SelectDT(TransAnalAttrib, sql, Transaction, null, 0, 0);
TransAnalAttrib.DefaultView.Sort =
ATransAnalAttribTable.GetBatchNumberDBName() + "," +
ATransAnalAttribTable.GetJournalNumberDBName() + "," +
ATransAnalAttribTable.GetTransactionNumberDBName();
// get a list of all batches involved
List <Int64>batches = new List <Int64>();
StringBuilder batchnumbers = new StringBuilder();
foreach (ATransactionRow r in transactions.Rows)
{
if (!batches.Contains(r.BatchNumber))
{
batches.Add(r.BatchNumber);
batchnumbers.Append(r.BatchNumber.ToString() + ",");
}
}
// get the other transactions in the same journal for finding the opposite cc/acc involved
// for performance reasons, get all transactions of the whole batch
sql =
String.Format("SELECT DISTINCT TJ.* " +
"FROM PUB_{0} AS TJ " +
"WHERE TJ.{1} = {2} AND TJ.{3} IN ({4})",
ATransactionTable.GetTableDBName(),
ATransactionTable.GetLedgerNumberDBName(),
ALedgerNumber,
ATransactionTable.GetBatchNumberDBName(),
batchnumbers.ToString() + "-1");
ATransactionTable allTransactionsInJournal = new ATransactionTable();
allTransactionsInJournal = (ATransactionTable)DBAccess.GDBAccessObj.SelectDT(allTransactionsInJournal, sql, Transaction, null, 0, 0);
allTransactionsInJournal.DefaultView.Sort =
ATransactionTable.GetBatchNumberDBName() + "," +
ATransactionTable.GetJournalNumberDBName();
// get all names of gift batches
sql =
String.Format("SELECT * FROM PUB_{0} " +
"WHERE {1} = {2} " +
"AND {3} = {4}",
AGiftBatchTable.GetTableDBName(),
AGiftBatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGiftBatchTable.GetBatchYearDBName(),
AFinancialYear);
AGiftBatchTable giftbatches = new AGiftBatchTable();
DBAccess.GDBAccessObj.SelectDT(giftbatches, sql, Transaction, null, 0, 0);
giftbatches.DefaultView.Sort = AGiftBatchTable.GetBatchNumberDBName();
sql =
String.Format("SELECT * FROM PUB_{0} " +
"WHERE {1} = {2}",
AAccountTable.GetTableDBName(),
AAccountTable.GetLedgerNumberDBName(),
ALedgerNumber);
AAccountTable accounts = new AAccountTable();
DBAccess.GDBAccessObj.SelectDT(accounts, sql, Transaction, null, 0, 0);
accounts.DefaultView.Sort = AAccountTable.GetAccountCodeDBName();
DBAccess.GDBAccessObj.RollbackTransaction();
int rowCounter = 0;
StringCollection costcentreCollection = StringHelper.StrSplit(ACostCentres, ",");
foreach (ATransactionRow row in transactions.Rows)
{
if (row.DebitCreditIndicator)
{
row.TransactionAmount *= -1.0m;
}
StringBuilder attributes = new StringBuilder();
DataRowView[] attribs = TransAnalAttrib.DefaultView.FindRows(new object[] { row.BatchNumber, row.JournalNumber, row.TransactionNumber });
decimal TaxOnIncome = 0.0m;
decimal TaxOnExpense = 0.0m;
// only mention tax codes, if this transaction was against a costcentre that has to pay taxes
if (costcentreCollection.Contains(row.CostCentreCode))
{
foreach (DataRowView rv in attribs)
{
ATransAnalAttribRow attribRow = (ATransAnalAttribRow)rv.Row;
// also export attribRow.AnalysisTypeCode?
attributes.Append(attribRow.AnalysisAttributeValue);
if (attribRow.AnalysisAttributeValue == "v19")
{
TaxOnExpense = row.TransactionAmount * 0.19m;
}
else if (attribRow.AnalysisAttributeValue == "v7")
{
TaxOnExpense = row.TransactionAmount * 0.07m;
}
else if (attribRow.AnalysisAttributeValue == "70v7")
{
TaxOnExpense = row.TransactionAmount * 0.7m * 0.07m;
}
else if (attribRow.AnalysisAttributeValue == "70v19")
{
TaxOnExpense = row.TransactionAmount * 0.7m * 0.19m;
}
else if (attribRow.AnalysisAttributeValue == "m19")
{
TaxOnIncome = row.TransactionAmount * 0.19m;
}
else if (attribRow.AnalysisAttributeValue == "m7")
{
TaxOnIncome = row.TransactionAmount * 0.07m;
}
}
}
DataRowView[] RelatedTransactions = allTransactionsInJournal.DefaultView.FindRows(new object[] { row.BatchNumber, row.JournalNumber });
ATransactionRow[] OtherTransactions = GetOtherTransactions(row, RelatedTransactions);
string OtherCostCentres = string.Empty;
string OtherAccountCodes = string.Empty;
if (OtherTransactions.Length < 30)
{
foreach (ATransactionRow r in OtherTransactions)
{
OtherCostCentres = StringHelper.AddCSV(OtherCostCentres, r.CostCentreCode);
OtherAccountCodes = StringHelper.AddCSV(OtherAccountCodes, r.AccountCode);
}
}
if (!ACostCentresInvolved.Contains(row.CostCentreCode))
{
ACostCentresInvolved.Add(row.CostCentreCode);
}
if (!AAccountsInvolved.Contains(row.AccountCode))
{
AAccountsInvolved.Add(row.AccountCode);
}
// we are using gift batch for receiving payments
string Narrative = row.Narrative;
if (Narrative.StartsWith("GB - Gift Batch ") && row.Reference.StartsWith("GB"))
{
// find the account and set the account description into the narrative
try
{
DataRowView[] acc = accounts.DefaultView.FindRows(row.AccountCode);
Narrative = ((AAccountRow)acc[0].Row).AccountCodeLongDesc;
}
catch (Exception)
{
}
try
{
DataRowView[] gb = giftbatches.DefaultView.FindRows(Convert.ToInt32(row.Reference.Substring(2)));
Narrative += " " + ((AGiftBatchRow)gb[0].Row).BatchDescription;
}
catch (Exception)
{
}
}
sb.Append(StringHelper.StrMerge(
new string[] {
"B" + row.BatchNumber.ToString() + "_J" + row.JournalNumber.ToString() + "_T" + row.TransactionNumber.ToString(),
row.CostCentreCode,
row.AccountCode,
row.TransactionDate.ToString("yyyyMMdd"),
OtherCostCentres,
OtherAccountCodes,
Narrative,
row.Reference,
String.Format("{0:N}", row.TransactionAmount),
attributes.ToString(),
TaxOnIncome.ToString(),
TaxOnExpense.ToString()
}, ACSVSeparator));
sb.Append(ANewLine);
rowCounter++;
if (rowCounter % 500 == 0)
{
TLogging.Log("Processing transactions " + rowCounter.ToString());
}
}
TLogging.Log("Processing transactions " + rowCounter.ToString());
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
/// <summary>
/// export all posted invoices for conference and seminar participants in this year
/// </summary>
public static void Export(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
Int32 AFinancialYear,
string ACostCentres)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "participants.csv"));
Console.WriteLine("Writing file: " + filename);
TDBTransaction Transaction = new TDBTransaction();
AGiftDetailTable giftdetails = new AGiftDetailTable();
AGiftTable gifts = new AGiftTable();
AGiftBatchTable batches = new AGiftBatchTable();
PPersonTable persons = new PPersonTable();
DBAccess.ReadTransaction(ref Transaction,
delegate
{
TDataBase db = Transaction.DataBaseObj;
// all gift details towards a costcentre that needs to be exported
string sql =
String.Format("SELECT DISTINCT D.* " +
"FROM PUB_{0} AS B, PUB_{1} AS G, PUB_{2} AS D " +
"WHERE B.{3} = {4} AND B.{5} = {6} AND B.{7}='{8}' " +
"AND G.{3} = B.{3} AND G.{9} = B.{9} " +
"AND D.{3} = G.{3} AND D.{9} = G.{9} AND D.{10} = G.{10} " +
"AND D.{11} IN ({12}) " +
"AND NOT D.{13} = '{14}'",
AGiftBatchTable.GetTableDBName(),
AGiftTable.GetTableDBName(),
AGiftDetailTable.GetTableDBName(),
AGiftBatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGiftBatchTable.GetBatchYearDBName(),
AFinancialYear,
AGiftBatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
AGiftBatchTable.GetBatchNumberDBName(),
AGiftTable.GetGiftTransactionNumberDBName(),
AGiftDetailTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
AGiftDetailTable.GetMotivationGroupCodeDBName(),
"GIFT");
db.SelectDT(giftdetails, sql, Transaction, null, 0, 0);
sql = sql.Replace("SELECT DISTINCT D.*", "SELECT DISTINCT G.*");
db.SelectDT(gifts, sql, Transaction, null, 0, 0);
gifts.DefaultView.Sort =
AGiftTable.GetBatchNumberDBName() + "," +
AGiftTable.GetGiftTransactionNumberDBName();
sql = sql.Replace("SELECT DISTINCT G.*", "SELECT DISTINCT B.*");
db.SelectDT(batches, sql, Transaction, null, 0, 0);
batches.DefaultView.Sort = AGiftTable.GetBatchNumberDBName();
sql =
String.Format("SELECT DISTINCT P.* " +
"FROM PUB_{0} AS B, PUB_{1} AS G, PUB_{2} AS D, PUB.{15} AS P " +
"WHERE B.{3} = {4} AND B.{5} = {6} AND B.{7}='{8}' " +
"AND G.{3} = B.{3} AND G.{9} = B.{9} " +
"AND D.{3} = G.{3} AND D.{9} = G.{9} AND D.{10} = G.{10} " +
"AND D.{11} IN ({12}) " +
"AND NOT D.{13} = '{14}' " +
"AND P.{16} = G.{17}",
AGiftBatchTable.GetTableDBName(),
AGiftTable.GetTableDBName(),
AGiftDetailTable.GetTableDBName(),
AGiftBatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGiftBatchTable.GetBatchYearDBName(),
AFinancialYear,
AGiftBatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
AGiftBatchTable.GetBatchNumberDBName(),
AGiftTable.GetGiftTransactionNumberDBName(),
AGiftDetailTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
AGiftDetailTable.GetMotivationGroupCodeDBName(),
"GIFT",
PPersonTable.GetTableDBName(),
PPersonTable.GetPartnerKeyDBName(),
AGiftTable.GetDonorKeyDBName());
db.SelectDT(persons, sql, Transaction, null, 0, 0);
persons.DefaultView.Sort = PPersonTable.GetPartnerKeyDBName();
});
StringBuilder sb = new StringBuilder();
foreach (AGiftDetailRow detail in giftdetails.Rows)
{
AGiftRow gift = (AGiftRow)gifts.DefaultView.FindRows(new object[] { detail.BatchNumber, detail.GiftTransactionNumber })[0].Row;
AGiftBatchRow batch = (AGiftBatchRow)batches.DefaultView.FindRows(detail.BatchNumber)[0].Row;
DataRowView[] personList = persons.DefaultView.FindRows(gift.DonorKey);
PPersonRow person = (personList.Length > 0 ? (PPersonRow)personList[0].Row : null);
sb.Append(StringHelper.StrMerge(
new string[] {
"GB" + detail.BatchNumber.ToString() + "_G" + detail.GiftTransactionNumber.ToString() +
"_D" + detail.DetailNumber.ToString(),
String.Format("{0:N}", detail.GiftTransactionAmount),
batch.GlEffectiveDate.ToString("yyyyMMdd"),
gift.DonorKey.ToString(),
person !=
null ? (person.DateOfBirth.HasValue ? person.DateOfBirth.Value.ToString("yyyyMMdd") : string.Empty) : string.Empty,
detail.CostCentreCode,
batch.BatchDescription,
detail.GiftCommentOne,
detail.GiftCommentTwo
}, ACSVSeparator));
sb.Append(ANewLine);
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
/// <summary>
/// get the default bank account for this ledger
/// </summary>
/// <param name="ALedgerNumber"></param>
public static string GetDefaultBankAccount(int ALedgerNumber)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
#endregion Validate Arguments
string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault(
SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString());
if (BankAccountCode.Length == 0)
{
TDBTransaction readTransaction = null;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, ref readTransaction,
delegate
{
// use the first bank account
AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, readTransaction);
accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";
if (accountProperties.DefaultView.Count > 0)
{
BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
}
else
{
string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c " +
"FROM a_gift_batch " +
"WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
" AND a_gift_batch.a_batch_number_i = (" +
"SELECT max(a_gift_batch.a_batch_number_i) " +
"FROM a_gift_batch " +
"WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
" AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "')";
DataTable LatestAccountCode = DBAccess.GDBAccessObj.SelectDT(SQLQuery, "LatestAccountCode", readTransaction);
// use the Bank Account of the previous Gift Batch
if ((LatestAccountCode != null) && (LatestAccountCode.Rows.Count > 0))
{
BankAccountCode = LatestAccountCode.Rows[0][AGiftBatchTable.GetBankAccountCodeDBName()].ToString(); //"a_bank_account_code_c"
}
// if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
else
{
AAccountTable AccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, readTransaction);
#region Validate Data
if ((AccountTable == null) || (AccountTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
DataView dv = AccountTable.DefaultView;
dv.Sort = AAccountTable.GetAccountCodeDBName() + " ASC"; //a_account_code_c
dv.RowFilter = String.Format("{0} = true AND {1} = true",
AAccountTable.GetAccountActiveFlagDBName(),
AAccountTable.GetPostingStatusDBName()); // "a_account_active_flag_l = true AND a_posting_status_l = true";
DataTable sortedDT = dv.ToTable();
TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo);
List <string> children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);
foreach (DataRow account in sortedDT.Rows)
{
// check if this account reports to the CASH account
if (children.Contains(account["a_account_code_c"].ToString()))
{
BankAccountCode = account["a_account_code_c"].ToString();
break;
}
}
}
}
});
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
}
return(BankAccountCode);
}
public static TSubmitChangesResult SaveData(string ATablename,
ref TTypedDataTable ASubmitTable, out TVerificationResultCollection AVerificationResult,
TDBTransaction AWriteTransaction)
{
AVerificationResult = null;
// TODO: check write permissions
if (ASubmitTable != null)
{
AVerificationResult = new TVerificationResultCollection();
try
{
if (ATablename == AAccountingPeriodTable.GetTableDBName())
{
AAccountingPeriodAccess.SubmitChanges((AAccountingPeriodTable)ASubmitTable, AWriteTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.AccountingPeriodList.ToString());
}
else if (ATablename == ACurrencyTable.GetTableDBName())
{
ACurrencyAccess.SubmitChanges((ACurrencyTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
{
ADailyExchangeRateAccess.SubmitChanges((ADailyExchangeRateTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
{
ACorporateExchangeRateAccess.SubmitChanges((ACorporateExchangeRateTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
{
ACurrencyLanguageAccess.SubmitChanges((ACurrencyLanguageTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == AFeesPayableTable.GetTableDBName())
{
AFeesPayableAccess.SubmitChanges((AFeesPayableTable)ASubmitTable, AWriteTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.FeesPayableList.ToString());
}
else if (ATablename == AFeesReceivableTable.GetTableDBName())
{
AFeesReceivableAccess.SubmitChanges((AFeesReceivableTable)ASubmitTable, AWriteTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.FeesReceivableList.ToString());
}
else if (ATablename == AGiftBatchTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Setup - please do not remove
// The method is not required for changes made to the gift batch screens, which use a TDS
AGiftBatchAccess.SubmitChanges((AGiftBatchTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == AJournalTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Setup - please do not remove
// The method is not required for changes made to the journal screens, which use a TDS
AJournalAccess.SubmitChanges((AJournalTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ARecurringJournalTable.GetTableDBName())
{
// This method is called from Submit Recurring GL Batch form - please do not remove
// The method is not required for changes made to the journal screens, which use a TDS
ARecurringJournalAccess.SubmitChanges((ARecurringJournalTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ALedgerTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Testing - please do not remove
ALedgerAccess.SubmitChanges((ALedgerTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == AAnalysisTypeTable.GetTableDBName())
{
AAnalysisTypeAccess.SubmitChanges((AAnalysisTypeTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ASuspenseAccountTable.GetTableDBName())
{
ASuspenseAccountAccess.SubmitChanges((ASuspenseAccountTable)ASubmitTable, AWriteTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.SuspenseAccountList.ToString());
}
else if (ATablename == PcAttendeeTable.GetTableDBName())
{
PcAttendeeAccess.SubmitChanges((PcAttendeeTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcConferenceTable.GetTableDBName())
{
PcConferenceAccess.SubmitChanges((PcConferenceTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcConferenceCostTable.GetTableDBName())
{
PcConferenceCostAccess.SubmitChanges((PcConferenceCostTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcEarlyLateTable.GetTableDBName())
{
PcEarlyLateAccess.SubmitChanges((PcEarlyLateTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcSupplementTable.GetTableDBName())
{
PcSupplementAccess.SubmitChanges((PcSupplementTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcDiscountTable.GetTableDBName())
{
PcDiscountAccess.SubmitChanges((PcDiscountTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
{
ValidateInternationalPostalType(ref AVerificationResult, ASubmitTable);
ValidateInternationalPostalTypeManual(ref AVerificationResult, ASubmitTable);
if (TVerificationHelper.IsNullOrOnlyNonCritical(AVerificationResult))
{
PInternationalPostalTypeAccess.SubmitChanges((PInternationalPostalTypeTable)ASubmitTable, AWriteTransaction);
}
}
else if (ATablename == PtApplicationTypeTable.GetTableDBName())
{
PtApplicationTypeAccess.SubmitChanges((PtApplicationTypeTable)ASubmitTable, AWriteTransaction);
// mark dependent lists for needing to be refreshed since there was a change in base list
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheablePersonTablesEnum.EventApplicationTypeList.ToString());
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheablePersonTablesEnum.FieldApplicationTypeList.ToString());
}
else if (ATablename == PFormTable.GetTableDBName())
{
PFormAccess.SubmitChanges((PFormTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PFormalityTable.GetTableDBName())
{
PFormalityAccess.SubmitChanges((PFormalityTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PMailingTable.GetTableDBName())
{
PMailingAccess.SubmitChanges((PMailingTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
{
PPartnerGiftDestinationAccess.SubmitChanges((PPartnerGiftDestinationTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PmDocumentTypeTable.GetTableDBName())
{
PmDocumentTypeAccess.SubmitChanges((PmDocumentTypeTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == SGroupTable.GetTableDBName())
{
SGroupAccess.SubmitChanges((SGroupTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == SSystemDefaultsTable.GetTableDBName())
{
SSystemDefaultsAccess.SubmitChanges((SSystemDefaultsTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == SSystemDefaultsGuiTable.GetTableDBName())
{
SSystemDefaultsGuiAccess.SubmitChanges((SSystemDefaultsGuiTable)ASubmitTable, AWriteTransaction);
}
else
{
throw new EOPAppException("TCommonDataReader.SaveData: unknown table '" + ATablename + "'");
}
}
catch (Exception Exc)
{
AVerificationResult.Add(
new TVerificationResult(null, "Cannot SubmitChanges:" + Environment.NewLine +
Exc.Message, "UNDEFINED", TResultSeverity.Resv_Critical));
}
}
if ((AVerificationResult != null) &&
(AVerificationResult.Count > 0))
{
// Downgrade TScreenVerificationResults to TVerificationResults in order to allow
// Serialisation (needed for .NET Remoting).
TVerificationResultCollection.DowngradeScreenVerificationResults(AVerificationResult);
return(AVerificationResult.HasCriticalErrors ? TSubmitChangesResult.scrError : TSubmitChangesResult.scrOK);
}
return(TSubmitChangesResult.scrOK);
}
/// <summary>
/// Get Unsaved Batch Rows in a list
/// </summary>
/// <param name="ABatchToInclude">If > 0 then include in list even if unchanged</param>
/// <returns></returns>
public List <AGiftBatchRow> GetUnsavedBatchRowsList(int ABatchToInclude = 0)
{
List <AGiftBatchRow> RetVal = new List <AGiftBatchRow>();
List <int> BatchesWithChangesList = new List <int>();
string BatchesWithChangesString = string.Empty;
DataView GiftBatchesDV = new DataView(FMainDS.AGiftBatch);
GiftBatchesDV.RowFilter = String.Format("{0}='{1}'",
AGiftBatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_UNPOSTED);
GiftBatchesDV.Sort = AGiftBatchTable.GetBatchNumberDBName() + " ASC";
DataView GiftDV = new DataView(FMainDS.AGift);
DataView GiftDetailsDV = new DataView(FMainDS.AGiftDetail);
GiftDV.Sort = String.Format("{0} ASC, {1} ASC",
AGiftTable.GetBatchNumberDBName(),
AGiftTable.GetGiftTransactionNumberDBName());
GiftDetailsDV.Sort = String.Format("{0} ASC, {1} ASC, {2} ASC",
AGiftDetailTable.GetBatchNumberDBName(),
AGiftDetailTable.GetGiftTransactionNumberDBName(),
AGiftDetailTable.GetDetailNumberDBName());
//Add the batch number(s) of changed gift rows
foreach (DataRowView dRV in GiftDV)
{
AGiftRow gR = (AGiftRow)dRV.Row;
if (!BatchesWithChangesList.Contains(gR.BatchNumber) &&
(gR.RowState != DataRowState.Unchanged))
{
BatchesWithChangesList.Add(gR.BatchNumber);
}
}
//Generate string of all batches found with changes
if (BatchesWithChangesList.Count > 0)
{
BatchesWithChangesString = String.Join(",", BatchesWithChangesList);
//Add any other batch number(s) of changed gift details
GiftDetailsDV.RowFilter = String.Format("{0} NOT IN ({1})",
AGiftDetailTable.GetBatchNumberDBName(),
BatchesWithChangesString);
}
foreach (DataRowView dRV in GiftDetailsDV)
{
AGiftDetailRow gDR = (AGiftDetailRow)dRV.Row;
if (!BatchesWithChangesList.Contains(gDR.BatchNumber) &&
(gDR.RowState != DataRowState.Unchanged))
{
BatchesWithChangesList.Add(gDR.BatchNumber);
}
}
BatchesWithChangesList.Sort();
//Get batch rows
foreach (DataRowView dRV in GiftBatchesDV)
{
AGiftBatchRow giftBatchRow = (AGiftBatchRow)dRV.Row;
if ((giftBatchRow.BatchStatus == MFinanceConstants.BATCH_UNPOSTED) &&
((giftBatchRow.BatchNumber == ABatchToInclude) ||
BatchesWithChangesList.Contains(giftBatchRow.BatchNumber) ||
(giftBatchRow.RowState != DataRowState.Unchanged)))
{
RetVal.Add(giftBatchRow);
}
}
return(RetVal);
}
private static bool CanSubsystemBeDeactivated(Int32 ALedgerNumber, String ASubsystemCode)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
else if (ASubsystemCode.Length == 0)
{
throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - The Subsystem code is empty!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Arguments
Boolean Result = false;
TDBTransaction Transaction = null;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, ref Transaction,
delegate
{
if (ASubsystemCode == CommonAccountingSubSystemsEnum.GR.ToString())
{
// for gift processing don't allow to deactivate if 'Posted' or 'Unposted' gift batches exist
AGiftBatchTable TemplateGiftBatchTable;
AGiftBatchRow TemplateGiftBatchRow;
StringCollection TemplateGiftBatchOperators;
TemplateGiftBatchTable = new AGiftBatchTable();
TemplateGiftBatchRow = TemplateGiftBatchTable.NewRowTyped(false);
TemplateGiftBatchRow.LedgerNumber = ALedgerNumber;
TemplateGiftBatchRow.BatchStatus = MFinanceConstants.BATCH_POSTED;
TemplateGiftBatchOperators = new StringCollection();
TemplateGiftBatchOperators.Add("=");
if (AGiftBatchAccess.CountUsingTemplate(TemplateGiftBatchRow, TemplateGiftBatchOperators, Transaction) == 0)
{
Result = true;
}
if (!Result)
{
TemplateGiftBatchRow.BatchStatus = MFinanceConstants.BATCH_UNPOSTED;
if (AGiftBatchAccess.CountUsingTemplate(TemplateGiftBatchRow, TemplateGiftBatchOperators, Transaction) == 0)
{
Result = true;
}
}
}
if (!Result)
{
AJournalTable TemplateJournalTable;
AJournalRow TemplateJournalRow;
StringCollection TemplateJournalOperators;
TemplateJournalTable = new AJournalTable();
TemplateJournalRow = TemplateJournalTable.NewRowTyped(false);
TemplateJournalRow.LedgerNumber = ALedgerNumber;
TemplateJournalRow.SubSystemCode = ASubsystemCode;
TemplateJournalOperators = new StringCollection();
TemplateJournalOperators.Add("=");
ARecurringJournalTable TemplateRJournalTable;
ARecurringJournalRow TemplateRJournalRow;
StringCollection TemplateRJournalOperators;
TemplateRJournalTable = new ARecurringJournalTable();
TemplateRJournalRow = TemplateRJournalTable.NewRowTyped(false);
TemplateRJournalRow.LedgerNumber = ALedgerNumber;
TemplateRJournalRow.SubSystemCode = ASubsystemCode;
TemplateRJournalOperators = new StringCollection();
TemplateRJournalOperators.Add("=");
// do not allow to deactivate subsystem if journals already exist
if ((AJournalAccess.CountUsingTemplate(TemplateJournalRow, TemplateJournalOperators, Transaction) == 0)
&& (ARecurringJournalAccess.CountUsingTemplate(TemplateRJournalRow, TemplateRJournalOperators, Transaction) == 0))
{
Result = true;
}
}
});
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
return Result;
}
/// <summary>
/// ResetForwardPeriodBatches.RunOperation
///
/// Reset period columns on batch, journal and gift batch tables for periods beyond end of the old year
/// </summary>
public override Int32 RunOperation()
{
Int32 JobSize = 0;
bool NewTransaction;
Boolean ShouldCommit = false;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
String Query =
"SELECT * FROM PUB_a_batch WHERE " +
"a_ledger_number_i=" + FLedgerInfo.LedgerNumber +
" AND a_batch_year_i=" + FOldYearNum +
" AND a_batch_period_i>" + FLedgerInfo.NumberOfAccountingPeriods;
ABatchTable BatchTbl = new ABatchTable();
DBAccess.GDBAccessObj.SelectDT(BatchTbl, Query, Transaction);
if (BatchTbl.Rows.Count > 0)
{
JobSize = BatchTbl.Rows.Count;
if (!FInfoMode)
{
foreach (ABatchRow BatchRow in BatchTbl.Rows)
{
BatchRow.BatchPeriod -= FLedgerInfo.NumberOfAccountingPeriods;
BatchRow.BatchYear += 1;
}
ABatchAccess.SubmitChanges(BatchTbl, Transaction);
ShouldCommit = true;
}
}
Query =
"SELECT PUB_a_journal.* FROM PUB_a_batch, PUB_a_journal WHERE " +
" PUB_a_journal.a_ledger_number_i=" + FLedgerInfo.LedgerNumber +
" AND PUB_a_batch.a_batch_number_i= PUB_a_journal.a_batch_number_i" +
" AND PUB_a_batch.a_batch_year_i=" + FOldYearNum +
" AND a_journal_period_i>" + FLedgerInfo.NumberOfAccountingPeriods;
AJournalTable JournalTbl = new AJournalTable();
DBAccess.GDBAccessObj.SelectDT(JournalTbl, Query, Transaction);
if (JournalTbl.Rows.Count > 0)
{
if (!FInfoMode)
{
foreach (AJournalRow JournalRow in JournalTbl.Rows)
{
JournalRow.JournalPeriod -= FLedgerInfo.NumberOfAccountingPeriods;
}
AJournalAccess.SubmitChanges(JournalTbl, Transaction);
ShouldCommit = true;
}
}
Query =
"SELECT * FROM PUB_a_gift_batch WHERE " +
" a_ledger_number_i=" + FLedgerInfo.LedgerNumber +
" AND a_batch_year_i=" + FOldYearNum +
" AND a_batch_period_i>" + FLedgerInfo.NumberOfAccountingPeriods;
AGiftBatchTable GiftBatchTbl = new AGiftBatchTable();
DBAccess.GDBAccessObj.SelectDT(GiftBatchTbl, Query, Transaction);
if (GiftBatchTbl.Rows.Count > 0)
{
JobSize += GiftBatchTbl.Rows.Count;
if (!FInfoMode)
{
foreach (AGiftBatchRow GiftBatchRow in GiftBatchTbl.Rows)
{
GiftBatchRow.BatchPeriod -= FLedgerInfo.NumberOfAccountingPeriods;
GiftBatchRow.BatchYear += 1;
}
AGiftBatchAccess.SubmitChanges(GiftBatchTbl, Transaction);
ShouldCommit = true;
}
}
} // try
finally
{
if (NewTransaction)
{
if (ShouldCommit)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
else
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
}
return JobSize;
}
/// <summary>
/// this supports the batch export files from Petra 2.x.
/// Each line starts with a type specifier, B for batch, J for journal, T for transaction
/// </summary>
public void ImportBatches(TGiftImportDataSourceEnum AImportSource, GiftBatchTDS AMainDS)
{
bool ImportOK = false;
bool RefreshGUIAfterImport = false;
OpenFileDialog OFileDialog = null;
if (FPetraUtilsObject.HasChanges)
{
// saving failed, therefore do not try to import
MessageBox.Show(Catalog.GetString("Please save before calling this function!"), Catalog.GetString(
"Failure"), MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
ALedgerRow LedgerRow = (ALedgerRow)AMainDS.ALedger.Rows[0];
int CurrentTopBatchNumber = LedgerRow.LastGiftBatchNumber;
try
{
FMyForm.FCurrentGiftBatchAction = Logic.TExtraGiftBatchChecks.GiftBatchAction.IMPORTING;
bool datesMayBeIntegers = TUserDefaults.GetBooleanDefault(MCommonConstants.USERDEFAULT_IMPORTEDDATESMAYBEINTEGERS, false);
FdlgSeparator = new TDlgSelectCSVSeparator(false);
FdlgSeparator.DateMayBeInteger = datesMayBeIntegers;
if (AImportSource == TGiftImportDataSourceEnum.FromClipboard)
{
string ImportString = Clipboard.GetText(TextDataFormat.UnicodeText);
if ((ImportString == null) || (ImportString.Length == 0))
{
MessageBox.Show(Catalog.GetString("Please first copy data from your spreadsheet application!"),
Catalog.GetString("Failure"), MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
FdlgSeparator.CSVData = ImportString;
}
else if (AImportSource == TGiftImportDataSourceEnum.FromFile)
{
OFileDialog = new OpenFileDialog();
string exportPath = TClientSettings.GetExportPath();
string fullPath = TUserDefaults.GetStringDefault("Imp Filename",
exportPath + Path.DirectorySeparatorChar + "import.csv");
TImportExportDialogs.SetOpenFileDialogFilePathAndName(OFileDialog, fullPath, exportPath);
OFileDialog.Title = Catalog.GetString("Import Batches from CSV File");
OFileDialog.Filter = Catalog.GetString("Gift Batch Files(*.csv)|*.csv|Text Files(*.txt)|*.txt");
// This call fixes Windows7 Open File Dialogs. It must be the line before ShowDialog()
TWin7FileOpenSaveDialog.PrepareDialog(Path.GetFileName(fullPath));
if (OFileDialog.ShowDialog() == DialogResult.OK)
{
Boolean fileCanOpen = FdlgSeparator.OpenCsvFile(OFileDialog.FileName);
if (!fileCanOpen)
{
MessageBox.Show(Catalog.GetString("Unable to open file."),
Catalog.GetString("Gift Import"),
MessageBoxButtons.OK,
MessageBoxIcon.Stop);
return;
}
}
else
{
return;
}
}
else
{
// unknown source!!
return;
}
String impOptions = TUserDefaults.GetStringDefault("Imp Options", ";" + TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN);
String dateFormatString = TUserDefaults.GetStringDefault("Imp Date", "MDY");
FdlgSeparator.DateFormat = dateFormatString;
FdlgSeparator.NumberFormat = (impOptions.Length > 1) ? impOptions.Substring(1) : TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN;
FdlgSeparator.SelectedSeparator = StringHelper.GetCSVSeparator(FdlgSeparator.FileContent) ??
((impOptions.Length > 0) ? impOptions.Substring(0, 1) : ";");
if (FdlgSeparator.ShowDialog() == DialogResult.OK)
{
Hashtable requestParams = new Hashtable();
requestParams.Add("ALedgerNumber", FLedgerNumber);
requestParams.Add("Delimiter", FdlgSeparator.SelectedSeparator);
requestParams.Add("DateFormatString", FdlgSeparator.DateFormat);
requestParams.Add("DatesMayBeIntegers", datesMayBeIntegers);
requestParams.Add("NumberFormat", FdlgSeparator.NumberFormat);
requestParams.Add("NewLine", Environment.NewLine);
bool Repeat = true;
while (Repeat)
{
Repeat = false;
TVerificationResultCollection AMessages = new TVerificationResultCollection();
GiftBatchTDSAGiftDetailTable NeedRecipientLedgerNumber = new GiftBatchTDSAGiftDetailTable();
Thread ImportThread = new Thread(() => ImportGiftBatches(
requestParams,
FdlgSeparator.FileContent,
out AMessages,
out ImportOK,
out RefreshGUIAfterImport,
out NeedRecipientLedgerNumber));
using (TProgressDialog ImportDialog = new TProgressDialog(ImportThread))
{
ImportDialog.ShowDialog();
}
// If NeedRecipientLedgerNumber contains data then AMessages will only ever contain
// one message alerting the user that no data has been imported.
// We do not want to show this as we will be displaying another more detailed message.
if (NeedRecipientLedgerNumber.Rows.Count == 0)
{
if (TVerificationHelper.ResultsContainErrorCode(AMessages, PetraErrorCodes.ERR_DB_SERIALIZATION_EXCEPTION))
{
TConcurrentServerTransactions.ShowTransactionSerializationExceptionDialog();
}
else
{
ShowMessages(AMessages);
}
}
// if the import contains gifts with Motivation Group 'GIFT' and that have a Family recipient with no Gift Destination
// then the import will have failed and we need to alert the user
if (NeedRecipientLedgerNumber.Rows.Count > 0)
{
bool OfferToRunImportAgain = true;
bool DoNotShowMessageBoxEverytime = false;
TFrmExtendedMessageBox.TResult Result = TFrmExtendedMessageBox.TResult.embrUndefined;
int count = 1;
// for each gift in which the recipient needs a Git Destination
foreach (GiftBatchTDSAGiftDetailRow Row in NeedRecipientLedgerNumber.Rows)
{
if (!DoNotShowMessageBoxEverytime)
{
string CheckboxText = string.Empty;
// only show checkbox if there is at least one more occurrence of this error
if (NeedRecipientLedgerNumber.Rows.Count - count > 0)
{
CheckboxText = string.Format(
Catalog.GetString(
"Do this for all further occurrences ({0})?"), NeedRecipientLedgerNumber.Rows.Count - count);
}
TFrmExtendedMessageBox extendedMessageBox = new TFrmExtendedMessageBox(FPetraUtilsObject.GetForm());
extendedMessageBox.ShowDialog(string.Format(
Catalog.GetString(
"Gift Import has been cancelled as the recipient '{0}' ({1}) has no Gift Destination assigned."),
Row.RecipientDescription, Row.RecipientKey) +
"\n\r\n\r\n\r" +
Catalog.GetString("Do you want to assign a Gift Destination to this partner now?"),
Catalog.GetString("Import Errors"),
CheckboxText,
TFrmExtendedMessageBox.TButtons.embbYesNo, TFrmExtendedMessageBox.TIcon.embiWarning);
Result = extendedMessageBox.GetResult(out DoNotShowMessageBoxEverytime);
}
if (Result == TFrmExtendedMessageBox.TResult.embrYes)
{
// allow the user to assign a Gift Destingation
TFrmGiftDestination GiftDestinationForm = new TFrmGiftDestination(FPetraUtilsObject.GetForm(), Row.RecipientKey);
GiftDestinationForm.ShowDialog();
}
else
{
OfferToRunImportAgain = false;
if (DoNotShowMessageBoxEverytime)
{
break;
}
}
count++;
}
// if the user has clicked yes to assigning Gift Destinations then offer to restart the import
if (OfferToRunImportAgain &&
(MessageBox.Show(Catalog.GetString("Would you like to import this Gift Batch again?"),
Catalog.GetString("Gift Import"), MessageBoxButtons.YesNo, MessageBoxIcon.Question,
MessageBoxDefaultButton.Button2)
== DialogResult.Yes))
{
Repeat = true;
}
}
}
}
// We save the defaults even if ok is false - because the client will probably want to try and import
// the same file again after correcting any errors
SaveUserDefaults(OFileDialog);
if (ImportOK)
{
MessageBox.Show(Catalog.GetString("Your data was imported successfully!"),
Catalog.GetString("Gift Import"),
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
if (ImportOK)
{
FMyUserControl.LoadBatchesForCurrentYear();
FMyForm.GetBatchControl().SelectRowInBatchGrid(1);
DataView allNewBatches = new DataView(AMainDS.AGiftBatch);
allNewBatches.RowFilter = String.Format("{0} > {1}",
AGiftBatchTable.GetBatchNumberDBName(),
CurrentTopBatchNumber);
foreach (DataRowView drv in allNewBatches)
{
drv.Row.SetModified();
}
FPetraUtilsObject.SetChangedFlag();
//Force initial inactive values check
FMyForm.SaveChangesManual(FMyForm.FCurrentGiftBatchAction);
}
else if (RefreshGUIAfterImport)
{
FMyUserControl.LoadBatchesForCurrentYear();
FMyForm.GetBatchControl().SelectRowInBatchGrid(1);
}
}
finally
{
FMyForm.FCurrentGiftBatchAction = Logic.TExtraGiftBatchChecks.GiftBatchAction.NONE;
}
}
private static bool CanSubsystemBeDeactivated(Int32 ALedgerNumber, String ASubsystemCode)
{
Boolean NewTransaction;
Boolean Result = false;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, out NewTransaction);
if (ASubsystemCode == CommonAccountingSubSystemsEnum.GR.ToString())
{
// for gift processing don't allow to deactivate if 'Posted' or 'Unposted' gift batches exist
AGiftBatchTable TemplateGiftBatchTable;
AGiftBatchRow TemplateGiftBatchRow;
StringCollection TemplateGiftBatchOperators;
TemplateGiftBatchTable = new AGiftBatchTable();
TemplateGiftBatchRow = TemplateGiftBatchTable.NewRowTyped(false);
TemplateGiftBatchRow.LedgerNumber = ALedgerNumber;
TemplateGiftBatchRow.BatchStatus = MFinanceConstants.BATCH_POSTED;
TemplateGiftBatchOperators = new StringCollection();
TemplateGiftBatchOperators.Add("=");
if (AGiftBatchAccess.CountUsingTemplate(TemplateGiftBatchRow, TemplateGiftBatchOperators, Transaction) == 0)
{
Result = true;
}
if (!Result)
{
TemplateGiftBatchRow.BatchStatus = MFinanceConstants.BATCH_UNPOSTED;
if (AGiftBatchAccess.CountUsingTemplate(TemplateGiftBatchRow, TemplateGiftBatchOperators, Transaction) == 0)
{
Result = true;
}
}
}
if (!Result)
{
AJournalTable TemplateJournalTable;
AJournalRow TemplateJournalRow;
StringCollection TemplateJournalOperators;
TemplateJournalTable = new AJournalTable();
TemplateJournalRow = TemplateJournalTable.NewRowTyped(false);
TemplateJournalRow.LedgerNumber = ALedgerNumber;
TemplateJournalRow.SubSystemCode = ASubsystemCode;
TemplateJournalOperators = new StringCollection();
TemplateJournalOperators.Add("=");
ARecurringJournalTable TemplateRJournalTable;
ARecurringJournalRow TemplateRJournalRow;
StringCollection TemplateRJournalOperators;
TemplateRJournalTable = new ARecurringJournalTable();
TemplateRJournalRow = TemplateRJournalTable.NewRowTyped(false);
TemplateRJournalRow.LedgerNumber = ALedgerNumber;
TemplateRJournalRow.SubSystemCode = ASubsystemCode;
TemplateRJournalOperators = new StringCollection();
TemplateRJournalOperators.Add("=");
// do not allow to deactivate subsystem if journals already exist
if ((AJournalAccess.CountUsingTemplate(TemplateJournalRow, TemplateJournalOperators, Transaction) == 0)
&& (ARecurringJournalAccess.CountUsingTemplate(TemplateRJournalRow, TemplateRJournalOperators, Transaction) == 0))
{
Result = true;
}
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return Result;
}
/// <summary>
/// get more details of the last gift of the partner
/// </summary>
/// <param name="APartnerKey"></param>
/// <param name="ALastGiftDate"></param>
/// <param name="ALastGiftAmount"></param>
/// <param name="ALastGiftGivenToPartnerKey"></param>
/// <param name="ALastGiftRecipientLedger"></param>
/// <param name="ALastGiftCurrencyCode"></param>
/// <param name="ALastGiftDisplayFormat"></param>
/// <param name="ALastGiftGivenToShortName"></param>
/// <param name="ALastGiftRecipientLedgerShortName"></param>
/// <param name="ARestrictedOrConfidentialGiftAccessDenied"></param>
/// <returns></returns>
public static Boolean GetLastGiftDetails(Int64 APartnerKey,
out DateTime ALastGiftDate,
out decimal ALastGiftAmount,
out Int64 ALastGiftGivenToPartnerKey,
out Int64 ALastGiftRecipientLedger,
out String ALastGiftCurrencyCode,
out String ALastGiftDisplayFormat,
out String ALastGiftGivenToShortName,
out String ALastGiftRecipientLedgerShortName,
out Boolean ARestrictedOrConfidentialGiftAccessDenied)
{
DataSet LastGiftDS;
AGiftDetailTable GiftDetailDT;
SGroupGiftTable GroupGiftDT;
SUserGroupTable UserGroupDT;
AGiftRow GiftDR;
AGiftBatchRow GiftBatchDR;
AGiftDetailRow GiftDetailDR;
ACurrencyRow CurrencyDR;
Int16 Counter;
Boolean AccessToGift = false;
DataRow[] FoundUserGroups;
ALastGiftAmount = 0;
ALastGiftCurrencyCode = "";
ALastGiftDisplayFormat = "";
ALastGiftDate = DateTime.MinValue;
ALastGiftGivenToPartnerKey = 0;
ALastGiftGivenToShortName = "";
ALastGiftRecipientLedger = 0;
ALastGiftRecipientLedgerShortName = "";
ARestrictedOrConfidentialGiftAccessDenied = false;
DateTime tmpLastGiftDate = ALastGiftDate;
decimal tmpLastGiftAmount = ALastGiftAmount;
Int64 tmpLastGiftGivenToPartnerKey = ALastGiftGivenToPartnerKey;
Int64 tmpLastGiftRecipientLedger = ALastGiftRecipientLedger;
String tmpLastGiftCurrencyCode = ALastGiftCurrencyCode;
String tmpLastGiftDisplayFormat = ALastGiftDisplayFormat;
String tmpLastGiftGivenToShortName = ALastGiftGivenToShortName;
String tmpLastGiftRecipientLedgerShortName = ALastGiftRecipientLedgerShortName;
Boolean tmpRestrictedOrConfidentialGiftAccessDenied = ARestrictedOrConfidentialGiftAccessDenied;
if ((!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapINQUIRE, AGiftTable.GetTableDBName())))
{
// User hasn't got access to a_gift Table in the DB
return(false);
}
// Set up temp DataSet
LastGiftDS = new DataSet("LastGiftDetails");
LastGiftDS.Tables.Add(new AGiftTable());
LastGiftDS.Tables.Add(new AGiftBatchTable());
LastGiftDS.Tables.Add(new AGiftDetailTable());
LastGiftDS.Tables.Add(new ACurrencyTable());
LastGiftDS.Tables.Add(new PPartnerTable());
TDBTransaction Transaction = null;
bool SubmissionOK = true;
// Important: The IsolationLevel here needs to correspond with the IsolationLevel in the
// Ict.Petra.Server.MPartner.Partner.UIConnectors.TPartnerEditUIConnector.LoadData Method
// as otherwise the attempt of taking-out of a DB Transaction here will lead to Bug #4167!
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, ref Transaction, ref SubmissionOK,
delegate
{
try
{
try
{
AGiftAccess.LoadViaPPartner(LastGiftDS, APartnerKey, null, Transaction,
StringHelper.InitStrArr(new String[] { "ORDER BY", AGiftTable.GetDateEnteredDBName() + " DESC" }), 0, 1);
}
catch (ESecurityDBTableAccessDeniedException)
{
// User hasn't got access to a_gift Table in the DB
return;
}
catch (Exception ex)
{
throw ex;
}
if (LastGiftDS.Tables[AGiftTable.GetTableName()].Rows.Count == 0)
{
// Partner hasn't given any Gift so far
return;
}
// Get the last gift
GiftDR = ((AGiftTable)LastGiftDS.Tables[AGiftTable.GetTableName()])[0];
if (GiftDR.Restricted)
{
AccessToGift = false;
GroupGiftDT = SGroupGiftAccess.LoadViaAGift(
GiftDR.LedgerNumber,
GiftDR.BatchNumber,
GiftDR.GiftTransactionNumber,
Transaction);
UserGroupDT = SUserGroupAccess.LoadViaSUser(UserInfo.GUserInfo.UserID, Transaction);
// Loop over all rows of GroupGiftDT
for (Counter = 0; Counter <= GroupGiftDT.Rows.Count - 1; Counter += 1)
{
// To be able to view a Gift, ReadAccess must be granted
if (GroupGiftDT[Counter].ReadAccess)
{
// Find out whether the user has a row in s_user_group with the
// GroupID of the GroupGift row
FoundUserGroups =
UserGroupDT.Select(SUserGroupTable.GetGroupIdDBName() + " = '" + GroupGiftDT[Counter].GroupId + "'");
if (FoundUserGroups.Length != 0)
{
// Access to gift can be granted
AccessToGift = true;
continue;
// don't evaluate further GroupGiftDT rows
}
}
}
}
else
{
AccessToGift = true;
}
if (AccessToGift)
{
tmpLastGiftDate = GiftDR.DateEntered;
// Console.WriteLine('GiftDR.LedgerNumber: ' + GiftDR.LedgerNumber.ToString + '; ' +
// 'GiftDR.BatchNumber: ' + GiftDR.BatchNumber.ToString);
// Load Gift Batch
AGiftBatchAccess.LoadByPrimaryKey(LastGiftDS, GiftDR.LedgerNumber, GiftDR.BatchNumber,
StringHelper.InitStrArr(new String[] { AGiftBatchTable.GetCurrencyCodeDBName() }), Transaction, null, 0, 0);
if (LastGiftDS.Tables[AGiftBatchTable.GetTableName()].Rows.Count != 0)
{
GiftBatchDR = ((AGiftBatchRow)LastGiftDS.Tables[AGiftBatchTable.GetTableName()].Rows[0]);
tmpLastGiftCurrencyCode = GiftBatchDR.CurrencyCode;
// Get Currency
ACurrencyAccess.LoadByPrimaryKey(LastGiftDS, GiftBatchDR.CurrencyCode, Transaction);
if (LastGiftDS.Tables[ACurrencyTable.GetTableName()].Rows.Count != 0)
{
CurrencyDR = (ACurrencyRow)(LastGiftDS.Tables[ACurrencyTable.GetTableName()].Rows[0]);
tmpLastGiftCurrencyCode = CurrencyDR.CurrencyCode;
tmpLastGiftDisplayFormat = CurrencyDR.DisplayFormat;
}
else
{
tmpLastGiftCurrencyCode = "";
tmpLastGiftDisplayFormat = "";
}
}
else
{
// missing Currency
tmpLastGiftCurrencyCode = "";
tmpLastGiftDisplayFormat = "";
}
// Load Gift Detail
AGiftDetailAccess.LoadViaAGift(LastGiftDS,
GiftDR.LedgerNumber,
GiftDR.BatchNumber,
GiftDR.GiftTransactionNumber,
StringHelper.InitStrArr(new String[] { AGiftDetailTable.GetGiftTransactionAmountDBName(),
AGiftDetailTable.GetRecipientKeyDBName(),
AGiftDetailTable.
GetRecipientLedgerNumberDBName(),
AGiftDetailTable.GetConfidentialGiftFlagDBName() }),
Transaction,
null,
0,
0);
GiftDetailDT = (AGiftDetailTable)LastGiftDS.Tables[AGiftDetailTable.GetTableName()];
if (GiftDetailDT.Rows.Count != 0)
{
if (GiftDR.LastDetailNumber > 1)
{
// Gift is a Split Gift
tmpLastGiftAmount = 0;
for (Counter = 0; Counter <= GiftDetailDT.Rows.Count - 1; Counter += 1)
{
GiftDetailDR = (AGiftDetailRow)GiftDetailDT.Rows[Counter];
// Check for confidential gift and whether the current user is allowed to see it
if (GiftDetailDR.ConfidentialGiftFlag)
{
if (!((UserInfo.GUserInfo.IsInGroup(SharedConstants.PETRAGROUP_FINANCE2)) ||
(UserInfo.GUserInfo.IsInGroup(SharedConstants.PETRAGROUP_FINANCE3))))
{
// User isn't allowed to see the gift
tmpRestrictedOrConfidentialGiftAccessDenied = true;
tmpLastGiftAmount = 0;
return;
}
}
tmpLastGiftAmount = tmpLastGiftAmount + GiftDetailDR.GiftTransactionAmount;
}
tmpLastGiftGivenToShortName = "";
tmpLastGiftRecipientLedgerShortName = "";
tmpLastGiftGivenToPartnerKey = -1;
tmpLastGiftRecipientLedger = -1;
}
else
{
// Gift isn't a Split Gift
GiftDetailDR = (AGiftDetailRow)GiftDetailDT.Rows[0];
// Check for confidential gift and whether the current user is allowed to see it
if (GiftDetailDR.ConfidentialGiftFlag)
{
if (!((UserInfo.GUserInfo.IsInGroup(SharedConstants.PETRAGROUP_FINANCE2)) ||
(UserInfo.GUserInfo.IsInGroup(SharedConstants.PETRAGROUP_FINANCE3))))
{
// User isn't allowed to see the gift
tmpRestrictedOrConfidentialGiftAccessDenied = true;
return;
}
}
tmpLastGiftAmount = GiftDetailDR.GiftTransactionAmount;
tmpLastGiftGivenToPartnerKey = GiftDetailDR.RecipientKey;
// Get Partner ShortName
PPartnerAccess.LoadByPrimaryKey(LastGiftDS, GiftDetailDR.RecipientKey,
StringHelper.InitStrArr(new String[] { PPartnerTable.GetPartnerShortNameDBName() }), Transaction, null, 0, 0);
if (LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows.Count != 0)
{
tmpLastGiftGivenToShortName =
((PPartnerRow)(LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows[0])).PartnerShortName;
}
else
{
// missing Partner
tmpLastGiftGivenToShortName = "";
}
// Get rid of last record because we are about to select again into the same DataTable...
LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows.Clear();
// Get Recipient Ledger
PPartnerAccess.LoadByPrimaryKey(LastGiftDS, GiftDetailDR.RecipientLedgerNumber,
StringHelper.InitStrArr(new String[] { PPartnerTable.GetPartnerShortNameDBName() }), Transaction, null, 0, 0);
if (LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows.Count != 0)
{
tmpLastGiftRecipientLedgerShortName =
((PPartnerRow)(LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows[0])).PartnerShortName;
}
else
{
// missing Ledger
tmpLastGiftRecipientLedgerShortName = "";
}
}
}
else
{
// missing Gift Detail
tmpLastGiftAmount = 0;
tmpLastGiftGivenToShortName = "";
tmpLastGiftRecipientLedgerShortName = "";
tmpLastGiftGivenToPartnerKey = -1;
tmpLastGiftRecipientLedger = -1;
}
}
else
{
// Gift is a restriced Gift and the current user isn't allowed to see it
tmpRestrictedOrConfidentialGiftAccessDenied = true;
}
}
finally
{
TLogging.LogAtLevel(7, "TGift.GetLastGiftDetails: committed own transaction.");
}
});
ALastGiftDate = tmpLastGiftDate;
ALastGiftAmount = tmpLastGiftAmount;
ALastGiftGivenToPartnerKey = tmpLastGiftGivenToPartnerKey;
ALastGiftRecipientLedger = tmpLastGiftRecipientLedger;
ALastGiftCurrencyCode = tmpLastGiftCurrencyCode;
ALastGiftDisplayFormat = tmpLastGiftDisplayFormat;
ALastGiftGivenToShortName = tmpLastGiftGivenToShortName;
ALastGiftRecipientLedgerShortName = tmpLastGiftRecipientLedgerShortName;
ARestrictedOrConfidentialGiftAccessDenied = tmpRestrictedOrConfidentialGiftAccessDenied;
return(AccessToGift);
}
private int GetChangedRecordCountManual(out string AMessage)
{
// For Gift Batch we will
// either get a change to N Batches
// or get changes to M transactions in N Batches
List <Tuple <string, int> > TableAndCountList = new List <Tuple <string, int> >();
int allChangesCount = 0;
foreach (DataTable dt in FMainDS.Tables)
{
if (dt != null)
{
int tableChangesCount = 0;
foreach (DataRow dr in dt.Rows)
{
if (dr.RowState != DataRowState.Unchanged)
{
tableChangesCount++;
allChangesCount++;
}
}
if (tableChangesCount > 0)
{
TableAndCountList.Add(new Tuple <string, int>(dt.TableName, tableChangesCount));
}
}
}
// Now build up a sensible message
AMessage = String.Empty;
if (TableAndCountList.Count > 0)
{
if (TableAndCountList.Count == 1)
{
// Only saving changes to batches
Tuple <string, int> TableAndCount = TableAndCountList[0];
AMessage = String.Format(Catalog.GetString(" You have made changes to the details of {0} {1}.{2}"),
TableAndCount.Item2,
Catalog.GetPluralString("batch", "batches", TableAndCount.Item2),
Environment.NewLine);
}
else
{
// Saving changes to transactions as well
int nBatches = 0;
int nTransactions = 0;
foreach (Tuple <string, int> TableAndCount in TableAndCountList)
{
if (TableAndCount.Item1.Equals(AGiftBatchTable.GetTableName()))
{
nBatches = TableAndCount.Item2;
}
else if (TableAndCount.Item2 > nTransactions)
{
nTransactions = TableAndCount.Item2;
}
}
if (nBatches == 0)
{
AMessage = String.Format(Catalog.GetString(" You have made changes to {0} {1}.{2}"),
nTransactions,
Catalog.GetPluralString("transaction", "transactions", nTransactions),
Environment.NewLine);
}
else
{
AMessage = String.Format(Catalog.GetString(" You have made changes to {0} {1} and {2} {3}.{4}"),
nBatches,
Catalog.GetPluralString("batch", "batches", nBatches),
nTransactions,
Catalog.GetPluralString("transaction", "transactions", nTransactions),
Environment.NewLine);
}
}
AMessage += String.Format(TFrmPetraEditUtils.StrConsequenceIfNotSaved, Environment.NewLine);
}
return(allChangesCount);
}
/// <summary>
/// export all the Data of the batches matching the parameters to a String
/// </summary>
/// <param name="ARequestParams">Hashtable containing the given params </param>
/// <param name="AExportString">Big parts of the export file as a simple String</param>
/// <param name="AVerificationMessages">Additional messages to display in a messagebox</param>
/// <returns>number of exported batches, -1 if cancelled, -2 if error</returns>
public Int32 ExportAllGiftBatchData(
Hashtable ARequestParams,
out String AExportString,
out TVerificationResultCollection AVerificationMessages)
{
//Return number of exported batches, -1 if cancelled, -2 if error
int ReturnGiftBatchCount = 0;
FStringWriter = new StringWriter();
FMainDS = new GiftBatchTDS();
FDelimiter = (String)ARequestParams["Delimiter"];
FLedgerNumber = (Int32)ARequestParams["ALedgerNumber"];
FDateFormatString = (String)ARequestParams["DateFormatString"];
bool Summary = (bool)ARequestParams["Summary"];
FUseBaseCurrency = (bool)ARequestParams["bUseBaseCurrency"];
FDateForSummary = (DateTime)ARequestParams["DateForSummary"];
String NumberFormat = (String)ARequestParams["NumberFormat"];
FCultureInfo = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE");
FTransactionsOnly = (bool)ARequestParams["TransactionsOnly"];
FExtraColumns = (bool)ARequestParams["ExtraColumns"];
try
{
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted,
ref FTransaction,
delegate
{
try
{
ALedgerAccess.LoadByPrimaryKey(FMainDS, FLedgerNumber, FTransaction);
List <OdbcParameter> parameters = new List <OdbcParameter>();
SortedList <String, String> SQLCommandDefines = new SortedList <string, string>();
if ((bool)ARequestParams["IncludeUnposted"])
{
SQLCommandDefines.Add("INCLUDEUNPOSTED", string.Empty);
}
OdbcParameter param = new OdbcParameter("LedgerNumber", OdbcType.Int);
param.Value = FLedgerNumber;
parameters.Add(param);
Int64 recipientNumber = (Int64)ARequestParams["RecipientNumber"];
Int64 fieldNumber = (Int64)ARequestParams["FieldNumber"];
if (recipientNumber != 0)
{
SQLCommandDefines.Add("BYRECIPIENT", string.Empty);
param = new OdbcParameter("RecipientNumber", OdbcType.Int);
param.Value = recipientNumber;
parameters.Add(param);
}
if (fieldNumber != 0)
{
SQLCommandDefines.Add("BYFIELD", string.Empty);
param = new OdbcParameter("fieldNumber", OdbcType.Int);
param.Value = fieldNumber;
parameters.Add(param);
}
if (ARequestParams.ContainsKey("BatchNumberStart"))
{
SQLCommandDefines.Add("BYBATCHNUMBER", string.Empty);
param = new OdbcParameter("BatchNumberStart", OdbcType.Int);
param.Value = (Int32)ARequestParams["BatchNumberStart"];
parameters.Add(param);
param = new OdbcParameter("BatchNumberEnd", OdbcType.Int);
param.Value = (Int32)ARequestParams["BatchNumberEnd"];
parameters.Add(param);
}
else
{
SQLCommandDefines.Add("BYDATERANGE", string.Empty);
param = new OdbcParameter("BatchDateFrom", OdbcType.DateTime);
param.Value = (DateTime)ARequestParams["BatchDateFrom"];
parameters.Add(param);
param = new OdbcParameter("BatchDateTo", OdbcType.DateTime);
param.Value = (DateTime)ARequestParams["BatchDateTo"];
parameters.Add(param);
}
string sqlStatement = TDataBase.ReadSqlFile("Gift.GetGiftsToExport.sql", SQLCommandDefines);
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving gift batch records"),
5);
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
DBAccess.GDBAccessObj.Select(FMainDS,
"SELECT DISTINCT PUB_a_gift_batch.* " + sqlStatement + " ORDER BY " + AGiftBatchTable.GetBatchNumberDBName(),
FMainDS.AGiftBatch.TableName,
FTransaction,
parameters.ToArray());
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving gift records"),
10);
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
DBAccess.GDBAccessObj.Select(FMainDS,
"SELECT DISTINCT PUB_a_gift.* " + sqlStatement + " ORDER BY " + AGiftBatchTable.GetBatchNumberDBName() + ", " +
AGiftTable.GetGiftTransactionNumberDBName(),
FMainDS.AGift.TableName,
FTransaction,
parameters.ToArray());
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving gift detail records"),
15);
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
DBAccess.GDBAccessObj.Select(FMainDS,
"SELECT DISTINCT PUB_a_gift_detail.* " + sqlStatement,
FMainDS.AGiftDetail.TableName,
FTransaction,
parameters.ToArray());
}
catch (ApplicationException ex)
{
throw ex;
}
catch (Exception ex)
{
TLogging.Log("Error in ExportAllGiftBatchData: " + ex.Message);
throw ex;
}
});
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(),
Catalog.GetString("Exporting Gift Batches"), 100);
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving records"),
5);
string BaseCurrency = FMainDS.ALedger[0].BaseCurrency;
FCurrencyCode = BaseCurrency; // Depending on FUseBaseCurrency, this will be overwritten for each gift.
SortedDictionary <String, AGiftSummaryRow> sdSummary = new SortedDictionary <String, AGiftSummaryRow>();
UInt32 counter = 0;
// TProgressTracker Variables
UInt32 GiftCounter = 0;
AGiftSummaryRow giftSummary = null;
FMainDS.AGiftDetail.DefaultView.Sort =
AGiftDetailTable.GetLedgerNumberDBName() + "," +
AGiftDetailTable.GetBatchNumberDBName() + "," +
AGiftDetailTable.GetGiftTransactionNumberDBName();
foreach (AGiftBatchRow giftBatch in FMainDS.AGiftBatch.Rows)
{
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
ReturnGiftBatchCount++;
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
string.Format(Catalog.GetString("Batch {0}"), giftBatch.BatchNumber),
20);
GiftCounter = 0;
if (!FTransactionsOnly & !Summary)
{
WriteGiftBatchLine(giftBatch);
}
foreach (AGiftRow gift in FMainDS.AGift.Rows)
{
if (gift.BatchNumber.Equals(giftBatch.BatchNumber) && gift.LedgerNumber.Equals(giftBatch.LedgerNumber))
{
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
// Update progress tracker every 25 records
if (++GiftCounter % 25 == 0)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
string.Format(Catalog.GetString("Batch {0} - Exporting gifts"), giftBatch.BatchNumber),
(GiftCounter / 25 + 4) * 5 > 90 ? 90 : (GiftCounter / 25 + 4) * 5);
}
DataRowView[] selectedRowViews = FMainDS.AGiftDetail.DefaultView.FindRows(
new object[] { gift.LedgerNumber, gift.BatchNumber, gift.GiftTransactionNumber });
foreach (DataRowView rv in selectedRowViews)
{
AGiftDetailRow giftDetail = (AGiftDetailRow)rv.Row;
if (Summary)
{
FCurrencyCode = FUseBaseCurrency ? BaseCurrency : giftBatch.CurrencyCode;
decimal mapExchangeRateToBase = FUseBaseCurrency ? 1 : giftBatch.ExchangeRateToBase;
counter++;
String DictionaryKey = FCurrencyCode + ";" + giftBatch.BankCostCentre + ";" + giftBatch.BankAccountCode + ";" +
giftDetail.RecipientKey + ";" + giftDetail.MotivationGroupCode + ";" +
giftDetail.MotivationDetailCode;
if (sdSummary.TryGetValue(DictionaryKey, out giftSummary))
{
giftSummary.GiftTransactionAmount += giftDetail.GiftTransactionAmount;
giftSummary.GiftAmount += giftDetail.GiftAmount;
}
else
{
giftSummary = new AGiftSummaryRow();
/*
* summary_data.a_transaction_currency_c = lv_stored_currency_c
* summary_data.a_bank_cost_centre_c = a_gift_batch.a_bank_cost_centre_c
* summary_data.a_bank_account_code_c = a_gift_batch.a_bank_account_code_c
* summary_data.a_recipient_key_n = a_gift_detail.p_recipient_key_n
* summary_data.a_motivation_group_code_c = a_gift_detail.a_motivation_group_code_c
* summary_data.a_motivation_detail_code_c = a_gift_detail.a_motivation_detail_code_c
* summary_data.a_exchange_rate_to_base_n = lv_exchange_rate_n
* summary_data.a_gift_type_c = a_gift_batch.a_gift_type_c */
giftSummary.CurrencyCode = FCurrencyCode;
giftSummary.BankCostCentre = giftBatch.BankCostCentre;
giftSummary.BankAccountCode = giftBatch.BankAccountCode;
giftSummary.RecipientKey = giftDetail.RecipientKey;
giftSummary.MotivationGroupCode = giftDetail.MotivationGroupCode;
giftSummary.MotivationDetailCode = giftDetail.MotivationDetailCode;
giftSummary.GiftTransactionAmount = giftDetail.GiftTransactionAmount;
giftSummary.GiftAmount = giftDetail.GiftAmount;
sdSummary.Add(DictionaryKey, giftSummary);
}
//overwrite always because we want to have the last
giftSummary.ExchangeRateToBase = mapExchangeRateToBase;
}
else // not summary
{
WriteGiftLine(gift, giftDetail);
}
}
}
}
}
if (Summary)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Export Summary"),
95);
bool first = true;
foreach (KeyValuePair <string, AGiftSummaryRow> kvp in sdSummary)
{
if (!FTransactionsOnly && first)
{
WriteGiftBatchSummaryLine(kvp.Value);
first = false;
}
WriteGiftSummaryLine(kvp.Value);
}
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Gift batch export successful"),
100);
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
}
catch (ApplicationException)
{
//Show cancel condition
ReturnGiftBatchCount = -1;
TProgressTracker.CancelJob(DomainManager.GClientID.ToString());
}
catch (Exception ex)
{
TLogging.Log(ex.ToString());
//Show error condition
ReturnGiftBatchCount = -2;
FMessages.Add(new TVerificationResult(
"Exporting Gift Batches Terminated Unexpectedly",
ex.Message,
"An unexpected error occurred during the export of gift batches",
string.Empty,
TResultSeverity.Resv_Critical,
Guid.Empty));
TProgressTracker.CancelJob(DomainManager.GClientID.ToString());
}
if (ReturnGiftBatchCount > 0)
{
AExportString = FStringWriter.ToString();
}
else
{
AExportString = string.Empty;
}
AVerificationMessages = FMessages;
return(ReturnGiftBatchCount);
}
public static bool GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable)
{
// TODO: check access permissions for the current user
bool NewTransaction = false;
TDBTransaction ReadTransaction;
TTypedDataTable tempTable = null;
try
{
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
// TODO: auto generate
if (ATablename == AApSupplierTable.GetTableDBName())
{
tempTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == AApDocumentTable.GetTableDBName())
{
tempTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == ATransactionTypeTable.GetTableDBName())
{
tempTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == ACurrencyTable.GetTableDBName())
{
tempTable = ACurrencyAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
{
tempTable = ADailyExchangeRateAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
{
tempTable = ACorporateExchangeRateAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
{
tempTable = ACurrencyLanguageAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AFeesPayableTable.GetTableDBName())
{
tempTable = AFeesPayableAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AFeesReceivableTable.GetTableDBName())
{
tempTable = AFeesReceivableAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AAnalysisTypeTable.GetTableDBName())
{
tempTable = AAnalysisTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AGiftBatchTable.GetTableDBName())
{
tempTable = AGiftBatchAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AJournalTable.GetTableDBName())
{
tempTable = AJournalAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ALedgerTable.GetTableDBName())
{
tempTable = ALedgerAccess.LoadAll(ReadTransaction);
}
else if (ATablename == MExtractMasterTable.GetTableDBName())
{
if (ASearchCriteria == null)
{
tempTable = MExtractMasterAccess.LoadAll(ReadTransaction);
}
else
{
tempTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
}
else if (ATablename == MExtractTable.GetTableDBName())
{
// it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set
if (ASearchCriteria != null)
{
tempTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
}
else if (ATablename == PcAttendeeTable.GetTableDBName())
{
tempTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcConferenceCostTable.GetTableDBName())
{
tempTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcEarlyLateTable.GetTableDBName())
{
tempTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcSupplementTable.GetTableDBName())
{
tempTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcDiscountTable.GetTableDBName())
{
tempTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
{
tempTable = PInternationalPostalTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == PtApplicationTypeTable.GetTableDBName())
{
tempTable = PtApplicationTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == PMailingTable.GetTableDBName())
{
tempTable = PMailingAccess.LoadAll(ReadTransaction);
}
else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
{
tempTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PmDocumentTypeTable.GetTableDBName())
{
tempTable = PmDocumentTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == SGroupTable.GetTableDBName())
{
tempTable = SGroupAccess.LoadAll(ReadTransaction);
}
else
{
throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename);
}
}
catch (Exception Exp)
{
DBAccess.GDBAccessObj.RollbackTransaction();
TLogging.Log("TCommonDataReader.GetData exception: " + Exp.ToString(), TLoggingType.ToLogfile);
TLogging.Log(Exp.StackTrace, TLoggingType.ToLogfile);
throw;
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TCommonDataReader.GetData: committed own transaction.");
}
}
// Accept row changes here so that the Client gets 'unmodified' rows
tempTable.AcceptChanges();
// return the table
AResultTable = tempTable;
return(true);
}
public static void GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable,
TDBTransaction AReadTransaction)
{
AResultTable = null;
// TODO: check access permissions for the current user
// TODO: auto generate
if (ATablename == AApSupplierTable.GetTableDBName())
{
AResultTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == AApDocumentTable.GetTableDBName())
{
AResultTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == ATransactionTypeTable.GetTableDBName())
{
AResultTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == ACurrencyTable.GetTableDBName())
{
AResultTable = ACurrencyAccess.LoadAll(AReadTransaction);
}
else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
{
AResultTable = ADailyExchangeRateAccess.LoadAll(AReadTransaction);
}
else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
{
AResultTable = ACorporateExchangeRateAccess.LoadAll(AReadTransaction);
}
else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
{
AResultTable = ACurrencyLanguageAccess.LoadAll(AReadTransaction);
}
else if (ATablename == AFeesPayableTable.GetTableDBName())
{
AResultTable = AFeesPayableAccess.LoadAll(AReadTransaction);
}
else if (ATablename == AFeesReceivableTable.GetTableDBName())
{
AResultTable = AFeesReceivableAccess.LoadAll(AReadTransaction);
}
else if (ATablename == AAnalysisTypeTable.GetTableDBName())
{
AResultTable = AAnalysisTypeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == AGiftBatchTable.GetTableDBName())
{
AResultTable = AGiftBatchAccess.LoadAll(AReadTransaction);
}
else if (ATablename == AJournalTable.GetTableDBName())
{
AResultTable = AJournalAccess.LoadAll(AReadTransaction);
}
else if (ATablename == ALedgerTable.GetTableDBName())
{
AResultTable = ALedgerAccess.LoadAll(AReadTransaction);
}
else if (ATablename == MExtractMasterTable.GetTableDBName())
{
if (ASearchCriteria == null)
{
AResultTable = MExtractMasterAccess.LoadAll(AReadTransaction);
}
else
{
AResultTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
}
else if (ATablename == MExtractTable.GetTableDBName())
{
// it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set
if (ASearchCriteria != null)
{
AResultTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
}
else if (ATablename == PcAttendeeTable.GetTableDBName())
{
AResultTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PcConferenceCostTable.GetTableDBName())
{
AResultTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PcEarlyLateTable.GetTableDBName())
{
AResultTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PcSupplementTable.GetTableDBName())
{
AResultTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PcDiscountTable.GetTableDBName())
{
AResultTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PFormTable.GetTableDBName())
{
string[] columns = TTypedDataTable.GetColumnStringList(PFormTable.TableId);
StringCollection fieldList = new StringCollection();
for (int i = 0; i < columns.Length; i++)
{
// Do not load the template document - we don't display it and it is big!
if (columns[i] != PFormTable.GetTemplateDocumentDBName())
{
fieldList.Add(columns[i]);
}
}
AResultTable = PFormAccess.LoadAll(fieldList, AReadTransaction);
}
else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
{
AResultTable = PInternationalPostalTypeAccess.LoadAll(AReadTransaction);
}
else if (ATablename == PtApplicationTypeTable.GetTableDBName())
{
AResultTable = PtApplicationTypeAccess.LoadAll(AReadTransaction);
}
else if (ATablename == PFormalityTable.GetTableDBName())
{
AResultTable = PFormalityAccess.LoadAll(AReadTransaction);
}
else if (ATablename == PMailingTable.GetTableDBName())
{
AResultTable = PMailingAccess.LoadAll(AReadTransaction);
}
else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
{
AResultTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PmDocumentTypeTable.GetTableDBName())
{
AResultTable = PmDocumentTypeAccess.LoadAll(AReadTransaction);
}
else if (ATablename == SGroupTable.GetTableDBName())
{
AResultTable = SGroupAccess.LoadAll(AReadTransaction);
}
else if (ATablename == SSystemDefaultsTable.GetTableDBName())
{
AResultTable = SSystemDefaultsAccess.LoadAll(AReadTransaction);
}
else if (ATablename == SSystemDefaultsGuiTable.GetTableDBName())
{
AResultTable = SSystemDefaultsGuiAccess.LoadAll(AReadTransaction);
}
else
{
throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename);
}
// Accept row changes here so that the Client gets 'unmodified' rows
AResultTable.AcceptChanges();
}
public static Int32 FieldChangeAdjustment(Int32 ALedgerNumber,
Int64 ARecipientKey,
DateTime AStartDate,
DateTime AEndDate,
Int64 AOldField,
DateTime ADateCorrection,
bool AWithReceipt)
{
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
GiftBatchTDS oldGiftDS = new GiftBatchTDS();
try
{
// find all gifts that need reversing.
// criteria:
// posted gift batches only
// no adjusted/reversed gifts
// date of gift batch in specified date range
// recipient field is the old field
string SqlStmt = TDataBase.ReadSqlFile("Gift.GetGiftsToReverse.sql");
List <OdbcParameter> parameters = new List <OdbcParameter>();
OdbcParameter param = new OdbcParameter("LedgerNumber", OdbcType.Int);
param.Value = ALedgerNumber;
parameters.Add(param);
param = new OdbcParameter("StartDate", OdbcType.Date);
param.Value = AStartDate;
parameters.Add(param);
param = new OdbcParameter("EndDate", OdbcType.Date);
param.Value = AEndDate;
parameters.Add(param);
param = new OdbcParameter("RecipientKey", OdbcType.BigInt);
param.Value = ARecipientKey;
parameters.Add(param);
param = new OdbcParameter("OldField", OdbcType.BigInt);
param.Value = AOldField;
parameters.Add(param);
DBAccess.GDBAccessObj.Select(oldGiftDS, SqlStmt, oldGiftDS.AGiftDetail.TableName, Transaction, parameters.ToArray());
// load the gift and the gift batch records if they have not been loaded yet
foreach (AGiftDetailRow giftdetail in oldGiftDS.AGiftDetail.Rows)
{
oldGiftDS.AGift.DefaultView.RowFilter = String.Format("{0} = {1} and {2} = {3}",
AGiftTable.GetBatchNumberDBName(),
giftdetail.BatchNumber,
AGiftTable.GetGiftTransactionNumberDBName(),
giftdetail.GiftTransactionNumber);
if (oldGiftDS.AGift.DefaultView.Count == 0)
{
AGiftTable tempGiftTable =
AGiftAccess.LoadByPrimaryKey(giftdetail.LedgerNumber,
giftdetail.BatchNumber,
giftdetail.GiftTransactionNumber,
Transaction);
oldGiftDS.AGift.Merge(tempGiftTable);
}
oldGiftDS.AGiftBatch.DefaultView.RowFilter = String.Format("{0} = {1}",
AGiftTable.GetBatchNumberDBName(),
giftdetail.BatchNumber);
if (oldGiftDS.AGiftBatch.DefaultView.Count == 0)
{
AGiftBatchTable tempGiftBatchTable =
AGiftBatchAccess.LoadByPrimaryKey(giftdetail.LedgerNumber,
giftdetail.BatchNumber,
Transaction);
oldGiftDS.AGiftBatch.Merge(tempGiftBatchTable);
}
}
DBAccess.GDBAccessObj.RollbackTransaction();
}
catch (Exception)
{
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
// we need to create a gift batch for each set of gifts with the same Currency, BankAccountCode, BankCostCentre, and Gift Type
SortedList <string, GiftBatchTDS> NewGiftBatches = new SortedList <string, GiftBatchTDS>();
foreach (GiftBatchTDSAGiftDetailRow oldGiftDetail in oldGiftDS.AGiftDetail.Rows)
{
// get the gift batch row for this detail
oldGiftDS.AGiftBatch.DefaultView.RowFilter =
String.Format("{0} = {1}",
AGiftTable.GetBatchNumberDBName(), oldGiftDetail.BatchNumber);
AGiftBatchRow oldGiftBatch = (AGiftBatchRow)oldGiftDS.AGiftBatch.DefaultView[0].Row;
GiftBatchTDS GiftDS = CreateNewGiftBatch(NewGiftBatches, oldGiftBatch, ADateCorrection);
AGiftBatchRow giftbatchRow = GiftDS.AGiftBatch[0];
// get the gift row for this detail
DataView v = oldGiftDS.AGift.DefaultView;
v.RowFilter =
String.Format("{0} = {1} and {2} = {3}",
AGiftTable.GetBatchNumberDBName(), oldGiftDetail.BatchNumber,
AGiftTable.GetGiftTransactionNumberDBName(), oldGiftDetail.GiftTransactionNumber);
AGiftRow oldGift = (AGiftRow)v[0].Row;
AGiftRow gift = GiftDS.AGift.NewRowTyped();
gift.LedgerNumber = giftbatchRow.LedgerNumber;
gift.BatchNumber = giftbatchRow.BatchNumber;
gift.GiftTransactionNumber = giftbatchRow.LastGiftNumber + 1;
gift.DonorKey = oldGift.DonorKey;
gift.DateEntered = ADateCorrection;
giftbatchRow.LastGiftNumber++;
GiftDS.AGift.Rows.Add(gift);
if (!AWithReceipt)
{
gift.ReceiptLetterCode = "NO*RECET";
}
// reverse the original gift
GiftBatchTDSAGiftDetailRow detail = GiftDS.AGiftDetail.NewRowTyped();
DataUtilities.CopyAllColumnValues(oldGiftDetail, detail);
detail.LedgerNumber = gift.LedgerNumber;
detail.BatchNumber = gift.BatchNumber;
detail.GiftTransactionNumber = gift.GiftTransactionNumber;
detail.DetailNumber = gift.LastDetailNumber + 1;
detail.GiftAmount = detail.GiftAmount * -1;
detail.GiftAmountIntl = detail.GiftAmountIntl * -1;
detail.GiftTransactionAmount = detail.GiftTransactionAmount * -1;
gift.LastDetailNumber++;
GiftDS.AGiftDetail.Rows.Add(detail);
// create the detail for the corrected gift to the new field
detail = GiftDS.AGiftDetail.NewRowTyped();
DataUtilities.CopyAllColumnValues(oldGiftDetail, detail);
detail.LedgerNumber = gift.LedgerNumber;
detail.BatchNumber = gift.BatchNumber;
detail.GiftTransactionNumber = gift.GiftTransactionNumber;
detail.DetailNumber = gift.LastDetailNumber + 1;
detail.GiftCommentOne = String.Format(Catalog.GetString("posted on {0}"), oldGiftBatch.GlEffectiveDate.ToShortDateString());
gift.LastDetailNumber++;
// TODO: calculate costcentre code from current commitment; this currently is done only at time of posting
// detail.RecipientLedgerNumber = oldGiftDetail.RecipientLedgerNumber;
// detail.CostCentreCode = oldGiftDetail.CostCentreCode;
GiftDS.AGiftDetail.Rows.Add(detail);
// TODO: how to make sure that the gl transaction is marked as System generated? avoid display on HOSA?
// mark original gift detail as modified
oldGiftDetail.ModifiedDetail = true;
}
TVerificationResultCollection VerificationResult;
TSubmitChangesResult result = TSubmitChangesResult.scrOK;
for (Int32 batchCounter = 0; batchCounter < NewGiftBatches.Count; batchCounter++)
{
if (result == TSubmitChangesResult.scrOK)
{
GiftBatchTDS GiftDS = NewGiftBatches.Values[batchCounter];
result = TGiftTransactionWebConnector.SaveGiftBatchTDS(ref GiftDS, out VerificationResult);
}
}
if (result == TSubmitChangesResult.scrOK)
{
result = TGiftTransactionWebConnector.SaveGiftBatchTDS(ref oldGiftDS, out VerificationResult);
if ((result == TSubmitChangesResult.scrOK) && (NewGiftBatches.Count > 0))
{
return(NewGiftBatches.Values[0].AGiftBatch[0].BatchNumber);
}
}
return(-1);
}
private void SelectBatchChanged(System.Object sender, EventArgs e)
{
bool isChecked = chkSelect.Checked;
if (isChecked)
{
//First pass FLedgerNumber = 0 so need to add Ledger to the filter when the user first checks the checkbox
if ((FLedgerNumber != 0) && !FMainDS.AGiftBatch.DefaultView.RowFilter.Contains(AGiftBatchTable.GetLedgerNumberDBName()))
{
FMainDS.AGiftBatch.DefaultView.RowFilter = String.Format("{0} = {1} AND {2} = '{3}'",
AGiftBatchTable.GetLedgerNumberDBName(),
FLedgerNumber,
AGiftBatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_UNPOSTED
);
}
DataView myDataView = FMainDS.AGiftBatch.DefaultView;
myDataView.AllowNew = false;
grdDetails.DataSource = new DevAge.ComponentModel.BoundDataView(myDataView);
if (grdDetails.Rows.Count > 1)
{
grdDetails.SelectRowInGrid(1);
}
txtReversalCommentOne.SelectAll();
txtReversalCommentOne.Focus();
dtpEffectiveDate.Enabled = false;
}
else
{
grdDetails.DataSource = null;
//bring enablement of the date textbox here to ensure enabled before setting focus
dtpEffectiveDate.Enabled = true;
dtpEffectiveDate.Focus();
dtpEffectiveDate.SelectAll();
}
grdDetails.Enabled = isChecked;
lblEffectiveDate.Enabled = !isChecked;
lblValidDateRange.Enabled = !isChecked;
}
/// <summary>
/// The main method that handles all filtering. Every change on the filter panel causes this event to fire.
/// It is important to manage the fact that this method may be called recursively and so nesting can be tricky!
/// </summary>
/// <param name="AFilterString">On entry this parameter contains the filter control's best guess for the current filter.
/// The code can modify this string in the light of current control values.</param>
public void ApplyFilterManual(ref string AFilterString)
{
if (!FFilterIsActivated)
{
// use anything until we have been activated.
return;
}
string workingFilter = String.Empty;
string additionalFilter = String.Empty;
bool showingAllPeriods = false;
// Remove the old base filter
if (FPrevBaseFilter.Length > 0)
{
// The additional filter is the part that is coming from the extra filter panel
additionalFilter = AFilterString.Substring(FPrevBaseFilter.Length);
if (additionalFilter.StartsWith(CommonJoinString.JOIN_STRING_SQL_AND))
{
additionalFilter = additionalFilter.Substring(CommonJoinString.JOIN_STRING_SQL_AND.Length);
}
}
int newYear = FcmbYearEnding.GetSelectedInt32();
if (newYear != FPrevYearEnding)
{
FPrevYearEnding = newYear;
// This will trigger a re-entrant call to this method
RefreshPeriods(newYear);
// Apply the last good filter as we unwind the nesting
AFilterString = FPrevFilter;
return;
}
int newPeriod = FcmbPeriod.GetSelectedInt32();
ALedgerRow LedgerRow =
((ALedgerTable)TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.LedgerDetails, FLedgerNumber))[0];
int CurrentLedgerYear = LedgerRow.CurrentFinancialYear;
int CurrentLedgerPeriod = LedgerRow.CurrentPeriod;
if (newYear == -1)
{
newYear = CurrentLedgerYear;
workingFilter = String.Format("{0} = {1}", AGiftBatchTable.GetBatchYearDBName(), newYear);
showingAllPeriods = true;
}
else
{
workingFilter = String.Format("{0} = {1}", AGiftBatchTable.GetBatchYearDBName(), newYear);
if (newPeriod == 0) //All periods for year
{
//Nothing to add to filter
showingAllPeriods = true;
}
else if (newPeriod == -1) //Current and forwarding
{
workingFilter += String.Format(" AND {0} >= {1}", AGiftBatchTable.GetBatchPeriodDBName(), CurrentLedgerPeriod);
}
else if (newPeriod > 0) //Specific period
{
workingFilter += String.Format(" AND {0} = {1}", AGiftBatchTable.GetBatchPeriodDBName(), newPeriod);
}
}
if (!BatchYearIsLoaded(newYear))
{
FMainDS.Merge(TRemote.MFinance.Gift.WebConnectors.LoadAGiftBatchForYearPeriod(FLedgerNumber, newYear, newPeriod));
}
if (FrbtEditing.Checked)
{
StringHelper.JoinAndAppend(ref workingFilter, String.Format("{0} = '{1}'",
AGiftBatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_UNPOSTED),
CommonJoinString.JOIN_STRING_SQL_AND);
}
else if (FrbtPosting.Checked)
{
// note: batches
StringHelper.JoinAndAppend(ref workingFilter, String.Format("({0} = '{1}') AND ({2} > 0) AND (({4} = 0) OR ({4} = {3}))",
AGiftBatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_UNPOSTED,
AGiftBatchTable.GetLastGiftNumberDBName(),
AGiftBatchTable.GetBatchTotalDBName(),
AGiftBatchTable.GetHashTotalDBName()),
CommonJoinString.JOIN_STRING_SQL_AND);
}
else //(FrbtAll.Checked)
{
}
FFilterFindPanelObject.FilterPanelControls.SetBaseFilter(workingFilter, FrbtAll.Checked && showingAllPeriods);
FPrevBaseFilter = workingFilter;
AFilterString = workingFilter;
StringHelper.JoinAndAppend(ref AFilterString, additionalFilter, CommonJoinString.JOIN_STRING_SQL_AND);
FPrevFilter = AFilterString;
//TLogging.Log(String.Format("working filter: {0}", workingFilter));
}
/// <summary>
/// get the default bank account for this ledger
/// </summary>
public string GetDefaultBankAccount()
{
#region Validate Arguments
if (FLedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), FLedgerNumber);
}
#endregion Validate Arguments
TDataBase db = DBAccess.Connect("GetDefaultBankAccount", FDataBase);
TDBTransaction readTransaction = new TDBTransaction();
string BankAccountCode = new TSystemDefaults(db).GetStringDefault(
SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + FLedgerNumber.ToString());
if (BankAccountCode.Length == 0)
{
try
{
db.ReadTransaction(
ref readTransaction,
delegate
{
// use the first bank account
AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(FLedgerNumber, readTransaction);
accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";
if (accountProperties.DefaultView.Count > 0)
{
BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
}
else
{
string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c" +
" FROM a_gift_batch " +
" WHERE a_gift_batch.a_ledger_number_i =" + FLedgerNumber +
" AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "'" +
" ORDER BY a_gift_batch.a_batch_number_i DESC" +
" LIMIT 1;";
DataTable latestAccountCode =
db.SelectDT(SQLQuery, "LatestAccountCode", readTransaction);
// use the Bank Account of the previous Gift Batch
if ((latestAccountCode != null) && (latestAccountCode.Rows.Count > 0))
{
BankAccountCode = latestAccountCode.Rows[0][AGiftBatchTable.GetBankAccountCodeDBName()].ToString(); //"a_bank_account_code_c"
}
// if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
else
{
AAccountTable accountTable = AAccountAccess.LoadViaALedger(FLedgerNumber, readTransaction);
#region Validate Data
if ((accountTable == null) || (accountTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
FLedgerNumber));
}
#endregion Validate Data
DataView dv = accountTable.DefaultView;
dv.Sort = AAccountTable.GetAccountCodeDBName() + " ASC"; //a_account_code_c
dv.RowFilter = String.Format("{0} = true AND {1} = true",
AAccountTable.GetAccountActiveFlagDBName(),
AAccountTable.GetPostingStatusDBName()); // "a_account_active_flag_l = true AND a_posting_status_l = true";
DataTable sortedDT = dv.ToTable();
TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(FLedgerNumber);
List <string> children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);
foreach (DataRow account in sortedDT.Rows)
{
// check if this account reports to the CASH account
if (children.Contains(account["a_account_code_c"].ToString()))
{
BankAccountCode = account["a_account_code_c"].ToString();
break;
}
}
}
}
});
if (FDataBase == null)
{
db.CloseDBConnection();
}
}
catch (Exception ex)
{
TLogging.LogException(ex, Utilities.GetMethodSignature());
throw;
}
}
return(BankAccountCode);
}
/// <summary>
/// Carry out the gift adjustment (field or tax deductible pct)
/// </summary>
/// <param name="AGiftBatchDS">Gift Batch containing GiftDetail rows for all gifts to be adjusted.</param>
/// <param name="ANewPct">New Tax Deductible Percentage (null if not being used)</param>
/// <param name="ANoReceipt">True if no receipt</param>
/// <param name="AParentForm"></param>
public static void GiftAdjustment(GiftBatchTDS AGiftBatchDS, decimal?ANewPct, bool ANoReceipt, Form AParentForm)
{
// sort gift batches so like batches are together
AGiftBatchDS.AGiftBatch.DefaultView.Sort = AGiftBatchTable.GetLedgerNumberDBName() + " ASC, " +
AGiftBatchTable.GetCurrencyCodeDBName() + " ASC, " +
AGiftBatchTable.GetBankCostCentreDBName() + " ASC, " +
AGiftBatchTable.GetBankAccountCodeDBName() + " ASC, " +
AGiftBatchTable.GetGiftTypeDBName() + " ASC";
GiftBatchTDS NewGiftDS = new GiftBatchTDS();
NewGiftDS.AGiftDetail.Merge(new GiftBatchTDSAGiftDetailTable());
for (int i = 0; i < AGiftBatchDS.AGiftBatch.Rows.Count; i++)
{
AGiftBatchRow OldGiftBatch = (AGiftBatchRow)AGiftBatchDS.AGiftBatch.DefaultView[i].Row;
AGiftBatchRow NextGiftBatch = null;
// add batch's gift/s to dataset
DataView Gifts = new DataView(AGiftBatchDS.AGift);
Gifts.RowFilter = string.Format("{0}={1}",
AGiftDetailTable.GetBatchNumberDBName(),
OldGiftBatch.BatchNumber);
foreach (DataRowView giftRows in Gifts)
{
AGiftRow gR = (AGiftRow)giftRows.Row;
NewGiftDS.AGift.ImportRow(gR);
}
// add batch's gift detail/s to dataset
DataView GiftDetails = new DataView(AGiftBatchDS.AGiftDetail);
GiftDetails.RowFilter = string.Format("{0}={1}",
AGiftDetailTable.GetBatchNumberDBName(),
OldGiftBatch.BatchNumber);
foreach (DataRowView giftDetailRows in GiftDetails)
{
AGiftDetailRow gDR = (AGiftDetailRow)giftDetailRows.Row;
NewGiftDS.AGiftDetail.ImportRow(gDR);
}
// if not the last row
if (i != AGiftBatchDS.AGiftBatch.Rows.Count - 1)
{
NextGiftBatch = (AGiftBatchRow)AGiftBatchDS.AGiftBatch.DefaultView[i + 1].Row;
}
// if this is the last batch or if the next batch's gifts need to be added to a different new batch
if ((NextGiftBatch == null) ||
(NextGiftBatch.LedgerNumber != OldGiftBatch.LedgerNumber) ||
(NextGiftBatch.CurrencyCode != OldGiftBatch.CurrencyCode) ||
(NextGiftBatch.BankCostCentre != OldGiftBatch.BankCostCentre) ||
(NextGiftBatch.BankAccountCode != OldGiftBatch.BankAccountCode) ||
(NextGiftBatch.GiftType != OldGiftBatch.GiftType))
{
TFrmGiftRevertAdjust AdjustForm = new TFrmGiftRevertAdjust(AParentForm);
try
{
AParentForm.ShowInTaskbar = false;
AdjustForm.LedgerNumber = OldGiftBatch.LedgerNumber;
AdjustForm.CurrencyCode = OldGiftBatch.CurrencyCode;
AdjustForm.Text = "Adjust Gift";
AdjustForm.AddParam("Function", GiftAdjustmentFunctionEnum.FieldAdjust);
AdjustForm.GiftMainDS = NewGiftDS;
AdjustForm.NoReceipt = ANoReceipt;
AdjustForm.AddBatchDetailsToScreen(OldGiftBatch.LedgerNumber, OldGiftBatch.CurrencyCode,
OldGiftBatch.BankCostCentre, OldGiftBatch.BankAccountCode, OldGiftBatch.GiftType);
if (ANewPct != null)
{
AdjustForm.AddParam("Function", GiftAdjustmentFunctionEnum.TaxDeductiblePctAdjust);
AdjustForm.AddParam("NewPct", ANewPct);
// gift destination must be the original for tax deduct pct adjustments
AdjustForm.AddParam("FixedGiftDestination", true);
// comments will be auto completed
AdjustForm.AutoCompleteComments();
}
else
{
AdjustForm.AddParam("Function", GiftAdjustmentFunctionEnum.FieldAdjust);
}
AdjustForm.GiftDetailRow = NewGiftDS.AGiftDetail[0];
if (AdjustForm.IsDisposed || (AdjustForm.ShowDialog() != DialogResult.OK))
{
continue;
}
}
finally
{
AParentForm.Cursor = Cursors.WaitCursor;
AdjustForm.Dispose();
AParentForm.ShowInTaskbar = true;
NewGiftDS.AGiftDetail.Clear();
NewGiftDS.AGift.Clear();
AParentForm.Cursor = Cursors.Default;
}
}
}
}
public static bool GiftRevertAdjust(
Int32 ALedgerNumber,
Int32 ABatchNumber,
Int32 AGiftDetailNumber,
bool ABatchSelected,
Int32 ANewBatchNumber,
DateTime?ANewGLDateEffective,
GiftAdjustmentFunctionEnum AFunction,
bool ANoReceipt,
Decimal ANewPct,
out int AAdjustmentBatchNumber)
{
AAdjustmentBatchNumber = 0;
int AdjustmentBatchNo = AAdjustmentBatchNumber;
GiftBatchTDS GiftDS = new GiftBatchTDS();
decimal batchGiftTotal = 0;
ANewBatchNumber = ABatchSelected ? ANewBatchNumber : 0;
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GiftRevertAdjust");
bool SubmissionOK = false;
try
{
db.WriteTransaction(
ref Transaction,
ref SubmissionOK,
delegate
{
// load the original gifts and gift details
AGiftAccess.LoadViaAGiftBatch(GiftDS, ALedgerNumber, ABatchNumber, Transaction);
AGiftDetailAccess.LoadViaAGiftBatch(GiftDS, ALedgerNumber, ABatchNumber, Transaction);
ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
AGiftBatchRow giftBatch;
DateTime DateEffective;
if (ANewGLDateEffective.HasValue)
{
DateEffective = ANewGLDateEffective.Value;
}
else
{
AGiftBatchTable OriginalGiftBatch = AGiftBatchAccess.LoadByPrimaryKey(ALedgerNumber, ABatchNumber, Transaction);
DateEffective = OriginalGiftBatch[0].GlEffectiveDate;
}
// if we need to create a new gift batch
if (!ABatchSelected)
{
giftBatch = CreateNewGiftBatch(
ALedgerNumber,
ABatchNumber,
DateEffective,
AFunction,
ref GiftDS, ref ledgerTable, Transaction);
}
else // using an existing gift batch
{
AGiftBatchAccess.LoadByPrimaryKey(GiftDS, ALedgerNumber, ANewBatchNumber, Transaction);
giftBatch = GiftDS.AGiftBatch[0];
DateEffective = giftBatch.GlEffectiveDate;
//If into an existing batch, then retrieve the existing batch total
batchGiftTotal = giftBatch.BatchTotal;
}
AdjustmentBatchNo = giftBatch.BatchNumber;
//assuming new elements are added after these static borders
GiftDS.AGift.DefaultView.Sort = string.Format("{0}, {1}",
AGiftTable.GetBatchNumberDBName(),
AGiftTable.GetGiftTransactionNumberDBName());
GiftDS.AGiftDetail.DefaultView.Sort = string.Format("{0}, {1}, {2}",
AGiftDetailTable.GetBatchNumberDBName(),
AGiftDetailTable.GetGiftTransactionNumberDBName(),
AGiftDetailTable.GetDetailNumberDBName());
foreach (DataRowView giftRow in GiftDS.AGift.DefaultView)
{
int cycle = 0;
// first cycle creates gift reversal; second cycle creates new adjusted gift (if needed)
do
{
AGiftRow oldGift = (AGiftRow)giftRow.Row;
if (oldGift.RowState != DataRowState.Added)
{
AGiftRow gift = GiftDS.AGift.NewRowTyped(true);
DataUtilities.CopyAllColumnValuesWithoutPK(oldGift, gift);
gift.LedgerNumber = giftBatch.LedgerNumber;
gift.BatchNumber = giftBatch.BatchNumber;
// keep the same DateEntered as in the original gift if it is in the same period as the batch
if ((gift.DateEntered.Year != DateEffective.Year) || (gift.DateEntered.Month != DateEffective.Month))
{
gift.DateEntered = DateEffective;
}
gift.GiftTransactionNumber = giftBatch.LastGiftNumber + 1;
giftBatch.LastGiftNumber++;
gift.LinkToPreviousGift = (cycle != 0);
gift.LastDetailNumber = 0;
gift.FirstTimeGift = false;
// do not print a receipt for reversed gifts
if (cycle == 0)
{
gift.ReceiptPrinted = true;
gift.PrintReceipt = false;
}
else
{
gift.ReceiptPrinted = false;
gift.PrintReceipt = !ANoReceipt;
}
GiftDS.AGift.Rows.Add(gift);
foreach (DataRowView giftDetailRow in GiftDS.AGiftDetail.DefaultView)
{
AGiftDetailRow oldGiftDetail = (AGiftDetailRow)giftDetailRow.Row;
// if gift detail belongs to gift
if ((oldGiftDetail.GiftTransactionNumber == oldGift.GiftTransactionNumber) &&
(oldGiftDetail.BatchNumber == oldGift.BatchNumber) &&
(AFunction != GiftAdjustmentFunctionEnum.ReverseGiftDetail) ||
(oldGiftDetail.DetailNumber == AGiftDetailNumber))
{
AddDuplicateGiftDetailToGift(ref GiftDS, ref gift, oldGiftDetail, cycle == 0, Transaction,
AFunction,
ANewPct);
batchGiftTotal += ((cycle == 0) ? 0 : oldGiftDetail.GiftTransactionAmount);
// original gift also gets marked as a reversal
oldGiftDetail.ModifiedDetail = true;
}
}
}
cycle++;
} while ((cycle < 2) &&
(AFunction.Equals(GiftAdjustmentFunctionEnum.AdjustGift) ||
AFunction.Equals(GiftAdjustmentFunctionEnum.FieldAdjust) ||
AFunction.Equals(GiftAdjustmentFunctionEnum.TaxDeductiblePctAdjust)));
}
//When reversing into a new or existing batch, set batch total
giftBatch.BatchTotal = batchGiftTotal;
// save everything at the end
AGiftBatchAccess.SubmitChanges(GiftDS.AGiftBatch, Transaction);
ALedgerAccess.SubmitChanges(ledgerTable, Transaction);
AGiftAccess.SubmitChanges(GiftDS.AGift, Transaction);
AGiftDetailAccess.SubmitChanges(GiftDS.AGiftDetail, Transaction);
GiftDS.AGiftBatch.AcceptChanges();
SubmissionOK = true;
});
}
catch (Exception ex)
{
TLogging.LogException(ex, Utilities.GetMethodSignature());
throw new EOPAppException(Catalog.GetString("Gift Reverse/Adjust failed."), ex);
}
AAdjustmentBatchNumber = AdjustmentBatchNo;
db.CloseDBConnection();
return(SubmissionOK);
}
public static void LoadAll()
{
AGiftBatch = new AGiftBatchTable();
SerialisableDS.LoadAll(AGiftBatch, AGiftBatchTable.GetTableDBName());
}
/// <summary>
/// This report considers gifts given between the two specified dates, and can include all gifts or, if
/// selected, those to a particular motivation, motivation detail or recipient. For the defined set of gifts
/// and its total value, the donors are sorted into a list, starting with those who gave most, and showing
/// the percentage that their gifts contributed to the total received (for this motivation or recipient, if
/// specified) and the cumulative percentage, moving down the list starting with the top donor.
/// </summary>
/// <param name="ATotalAmount">Pre calculated value of the total gifts given with these parameters</param>
/// <param name="ATopXPercent">Upper limit of the percentage to show in the report</param>
/// <param name="ABottomXPercent">Lower limit of the percentage to show in the report</param>
/// <param name="AExtract">true to use only partners from an extract</param>
/// <param name="AExtractName">extract name</param>
/// <param name="AStartDate">Start date of the gifts given</param>
/// <param name="AEndDate">End date of the gifts given</param>
/// <param name="ARecipientKey">Partner key of a specific recipient. If 0 then use all recipients</param>
/// <param name="AMotivationGroup">Limit gifts to this motivation group. If % use all motivation groups</param>
/// <param name="AMotivationDetail">Limit gifts to this motivation detail. If % use all motivation details</param>
/// <returns></returns>
private bool MakeTopDonor(decimal ATotalAmount, decimal ATopXPercent, decimal ABottomXPercent,
bool AExtract, String AExtractName, DateTime AStartDate, DateTime AEndDate,
Int64 ARecipientKey, String AMotivationGroup, String AMotivationDetail)
{
Int64 LedgerNumber = situation.GetParameters().Get("param_ledger_number_i").ToInt64();
String CurrencyType = situation.GetParameters().Get("param_currency").ToString();
StringBuilder SqlString = new StringBuilder();
SqlString.Append("SELECT DISTINCT ");
SqlString.Append("gift.p_donor_key_n AS DonorKey, ");
SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerShortNameDBName() + " AS ShortName, ");
SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerClassDBName() + " AS PartnerClass, ");
if (CurrencyType == "Base")
{
SqlString.Append("SUM(detail." + AGiftDetailTable.GetGiftAmountDBName() + ") AS Amount ");
}
else
{
SqlString.Append("SUM(detail." + AGiftDetailTable.GetGiftAmountIntlDBName() + ") AS Amount ");
}
SqlString.Append(
" FROM " + AGiftTable.GetTableDBName() + " as gift, " + AGiftDetailTable.GetTableDBName() + " as detail, " +
PPartnerTable.GetTableDBName() + ", " + AGiftBatchTable.GetTableDBName() + " ");
if (AExtract)
{
SqlString.Append(", " + MExtractTable.GetTableDBName() + ", " + MExtractMasterTable.GetTableDBName());
SqlString.Append(
" WHERE gift." + AGiftTable.GetDonorKeyDBName() + " = " + MExtractTable.GetTableDBName() + "." +
MExtractTable.GetPartnerKeyDBName());
SqlString.Append(
" AND " + MExtractTable.GetTableDBName() + "." + MExtractTable.GetExtractIdDBName() + " = " +
MExtractMasterTable.GetTableDBName() +
"." + MExtractMasterTable.GetExtractIdDBName());
SqlString.Append(" AND " + MExtractMasterTable.GetTableDBName() + "." + MExtractMasterTable.GetExtractNameDBName() + " = '");
SqlString.Append(AExtractName);
SqlString.Append("' AND ");
}
else
{
SqlString.Append(" WHERE ");
}
SqlString.Append(" detail." + AGiftDetailTable.GetLedgerNumberDBName() + " = gift." + AGiftTable.GetLedgerNumberDBName());
SqlString.Append(" AND detail." + AGiftDetailTable.GetBatchNumberDBName() + " = gift." + AGiftTable.GetBatchNumberDBName());
SqlString.Append(
" AND detail." + AGiftDetailTable.GetGiftTransactionNumberDBName() + " = gift." + AGiftTable.GetGiftTransactionNumberDBName());
SqlString.Append(" AND gift." + AGiftTable.GetDateEnteredDBName() + " BETWEEN '");
SqlString.Append(AStartDate.ToString("yyyy-MM-dd"));
SqlString.Append("' AND '");
SqlString.Append(AEndDate.ToString("yyyy-MM-dd"));
SqlString.Append("' AND gift." + AGiftTable.GetLedgerNumberDBName() + " = ");
SqlString.Append(LedgerNumber.ToString());
SqlString.Append(" AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetLedgerNumberDBName() + " = ");
SqlString.Append(LedgerNumber.ToString());
SqlString.Append(
" AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchNumberDBName() + " = gift." +
AGiftTable.GetBatchNumberDBName());
SqlString.Append(" AND ( " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchStatusDBName() + " = 'Posted' OR ");
SqlString.Append(AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchStatusDBName() + " = 'posted' ) ");
SqlString.Append(
" AND " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = gift." + AGiftTable.GetDonorKeyDBName());
if (ARecipientKey != 0)
{
SqlString.Append(" AND detail." + AGiftDetailTable.GetRecipientKeyDBName() + " = ");
SqlString.Append(ARecipientKey.ToString());
}
if (AMotivationGroup != "%")
{
SqlString.Append(" AND detail." + AGiftDetailTable.GetMotivationGroupCodeDBName() + " LIKE '");
SqlString.Append(AMotivationGroup);
SqlString.Append("' ");
}
if (AMotivationDetail != "%")
{
SqlString.Append(" AND detail." + AGiftDetailTable.GetMotivationDetailCodeDBName() + " LIKE '");
SqlString.Append(AMotivationDetail);
SqlString.Append("' ");
}
SqlString.Append(" GROUP BY gift." + AGiftTable.GetDonorKeyDBName() + ", ");
SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerShortNameDBName() + ", ");
SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerClassDBName());
SqlString.Append(" ORDER BY Amount DESC");
DataTable Table = situation.GetDatabaseConnection().SelectDT(SqlString.ToString(), "table",
situation.GetDatabaseConnection().Transaction, new OdbcParameter[] { });
decimal CummulativeAmount = 0;
decimal TopAmount = ATotalAmount * ATopXPercent / 100;
decimal BottomAmount = ATotalAmount * ABottomXPercent / 100;
int NumColumns = 7;
int ChildRow = 1;
situation.GetResults().Clear();
for (int Counter = 0; Counter < Table.Rows.Count; ++Counter)
{
decimal CurrentAmount = Convert.ToDecimal(Table.Rows[Counter]["Amount"]);
if (CurrentAmount < 0)
{
continue;
}
if ((CummulativeAmount <= TopAmount) &&
(CummulativeAmount >= BottomAmount))
{
Int64 DonorKey = Convert.ToInt64(Table.Rows[Counter]["DonorKey"]);
String ShortName = (String)Table.Rows[Counter]["ShortName"];
String PartnerClass = (String)Table.Rows[Counter]["PartnerClass"];
CummulativeAmount += CurrentAmount;
// Transfer to results
TVariant[] Header = new TVariant[NumColumns];
TVariant[] Description =
{
new TVariant(), new TVariant()
};
TVariant[] Columns = new TVariant[NumColumns];
for (int Counter2 = 0; Counter2 < NumColumns; ++Counter2)
{
Header[Counter2] = new TVariant();
Columns[Counter2] = new TVariant();
}
StringBuilder PartnerAddress = new StringBuilder();
PPartnerLocationRow AddressRow;
if (Ict.Petra.Server.MReporting.MPartner.TRptUserFunctionsPartner.GetPartnerBestAddressRow(DonorKey, situation, out AddressRow))
{
PLocationTable LocationTable = PLocationAccess.LoadByPrimaryKey(AddressRow.SiteKey,
AddressRow.LocationKey, situation.GetDatabaseConnection().Transaction);
if (LocationTable.Rows.Count > 0)
{
PLocationRow LocationRow = (PLocationRow)LocationTable.Rows[0];
PartnerAddress.Append(LocationRow.Locality);
if (LocationRow.Locality.Length > 0)
{
PartnerAddress.Append(", ");
}
PartnerAddress.Append(LocationRow.StreetName);
if (PartnerAddress.Length > 0)
{
PartnerAddress.Append(", ");
}
PartnerAddress.Append(LocationRow.Address3);
if (PartnerAddress.Length > 0)
{
PartnerAddress.Append(", ");
}
PartnerAddress.Append(LocationRow.PostalCode);
PartnerAddress.Append(" ");
PartnerAddress.Append(LocationRow.City);
if (LocationRow.County.Length > 0)
{
PartnerAddress.Append(", ");
PartnerAddress.Append(LocationRow.County);
}
PartnerAddress.Append(", ");
PartnerAddress.Append(LocationRow.CountryCode);
}
}
Columns[0] = new TVariant(DonorKey.ToString("0000000000"));
Columns[1] = new TVariant(PartnerClass);
Columns[2] = new TVariant(ShortName);
Columns[3] = new TVariant(CurrentAmount, "-#,##0.00;#,##0.00");
Columns[4] = new TVariant((CurrentAmount * 100 / ATotalAmount), "-#,##0.00;#,##0.00");
Columns[5] = new TVariant((CummulativeAmount * 100 / ATotalAmount), "-#,##0.00;#,##0.00");
Columns[6] = new TVariant(PartnerAddress.ToString());
situation.GetResults().AddRow(0, ChildRow++, true, 2, "", "", false,
Header, Description, Columns);
}
else
{
CummulativeAmount += CurrentAmount;
}
}
return(true);
}
/// <summary>
/// export all the Data of the batches matching the parameters to a String
/// </summary>
/// <param name="ARequestParams">Hashtable containing the given params </param>
/// <param name="AExportString">Big parts of the export file as a simple String</param>
/// <param name="AVerificationMessages">Additional messages to display in a messagebox</param>
/// <returns>number of exported batches, -1 if cancelled, -2 if error</returns>
public Int32 ExportAllGiftBatchData(
Hashtable ARequestParams,
out String AExportString,
out TVerificationResultCollection AVerificationMessages)
{
//Return number of exported batches, -1 if cancelled, -2 if error
int ReturnGiftBatchCount = 0;
FStringWriter = new StringWriter();
FMainDS = new GiftBatchTDS();
FDelimiter = (String)ARequestParams["Delimiter"];
FLedgerNumber = (Int32)ARequestParams["ALedgerNumber"];
FDateFormatString = (String)ARequestParams["DateFormatString"];
Boolean Summary = (Boolean)ARequestParams["Summary"];
FUseBaseCurrency = (Boolean)ARequestParams["bUseBaseCurrency"];
FDateForSummary = (DateTime)ARequestParams["DateForSummary"];
String NumberFormat = (String)ARequestParams["NumberFormat"];
FCultureInfo = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE");
FTransactionsOnly = (Boolean)ARequestParams["TransactionsOnly"];
FExtraColumns = (Boolean)ARequestParams["ExtraColumns"];
Int64 recipientNumber = (Int64)ARequestParams["RecipientNumber"];
String RecipientFilter = (recipientNumber != 0) ? " AND PUB_a_gift_detail.p_recipient_key_n = " + recipientNumber : "";
Int64 fieldNumber = (Int64)ARequestParams["FieldNumber"];
String FieldFilter = (fieldNumber != 0) ? " AND PUB_a_gift_detail.a_recipient_ledger_number_n = " + fieldNumber : "";
Boolean IncludeUnposted = (Boolean)ARequestParams["IncludeUnposted"];
String StatusFilter =
(IncludeUnposted) ? " AND (PUB_a_gift_batch.a_batch_status_c = 'Posted' OR PUB_a_gift_batch.a_batch_status_c = 'Unposted')"
: " AND PUB_a_gift_batch.a_batch_status_c = 'Posted'";
try
{
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted,
ref FTransaction,
delegate
{
try
{
ALedgerAccess.LoadByPrimaryKey(FMainDS, FLedgerNumber, FTransaction);
String BatchRangeFilter = (ARequestParams.ContainsKey(
"BatchNumberStart")) ?
" AND (PUB_a_gift_batch.a_batch_number_i >= " + (Int32)ARequestParams["BatchNumberStart"] +
" AND PUB_a_gift_batch.a_batch_number_i <= " + (Int32)ARequestParams["BatchNumberEnd"] +
")" : "";
// If I've specified a BatchRange, I can't also have a DateRange:
String DateRangeFilter = (BatchRangeFilter == "") ?
" AND (PUB_a_gift_batch.a_gl_effective_date_d >= '" +
((DateTime)ARequestParams["BatchDateFrom"]).ToString(
"yyyy-MM-dd") +
"' AND PUB_a_gift_batch.a_gl_effective_date_d <= '" +
((DateTime)ARequestParams["BatchDateTo"]).ToString("yyyy-MM-dd") +
"')" : "";
string StatementCore =
" FROM PUB_a_gift_batch, PUB_a_gift, PUB_a_gift_detail" +
" WHERE PUB_a_gift_batch.a_ledger_number_i = " + FLedgerNumber +
RecipientFilter +
FieldFilter +
DateRangeFilter +
BatchRangeFilter +
StatusFilter +
" AND PUB_a_gift.a_ledger_number_i = PUB_a_gift_batch.a_ledger_number_i" +
" AND PUB_a_gift.a_batch_number_i = PUB_a_gift_batch.a_batch_number_i" +
" AND PUB_a_gift_detail.a_ledger_number_i = PUB_a_gift_batch.a_ledger_number_i" +
" AND PUB_a_gift_detail.a_batch_number_i = PUB_a_gift_batch.a_batch_number_i" +
" AND PUB_a_gift_detail.a_gift_transaction_number_i = PUB_a_gift.a_gift_transaction_number_i";
string sqlStatement = "SELECT DISTINCT PUB_a_gift_batch.* " +
StatementCore +
" ORDER BY " + AGiftBatchTable.GetBatchNumberDBName();
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving gift batch records"),
5);
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
DBAccess.GDBAccessObj.Select(FMainDS,
sqlStatement,
FMainDS.AGiftBatch.TableName,
FTransaction
);
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving gift records"),
10);
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
sqlStatement = "SELECT DISTINCT PUB_a_gift.* " +
StatementCore +
" ORDER BY " + AGiftBatchTable.GetBatchNumberDBName() +
", " +
AGiftTable.GetGiftTransactionNumberDBName();
DBAccess.GDBAccessObj.Select(FMainDS,
sqlStatement,
FMainDS.AGift.TableName,
FTransaction);
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving gift detail records"),
15);
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
sqlStatement = "SELECT DISTINCT PUB_a_gift_detail.* " + StatementCore;
DBAccess.GDBAccessObj.Select(FMainDS,
sqlStatement,
FMainDS.AGiftDetail.TableName,
FTransaction);
}
catch (Exception ex)
{
TLogging.LogException(ex, Utilities.GetMethodSignature());
throw;
}
});
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(),
Catalog.GetString("Exporting Gift Batches"), 100);
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving records"),
5);
string BaseCurrency = FMainDS.ALedger[0].BaseCurrency;
FCurrencyCode = BaseCurrency; // Depending on FUseBaseCurrency, this will be overwritten for each gift.
SortedDictionary <String, AGiftSummaryRow> sdSummary = new SortedDictionary <String, AGiftSummaryRow>();
UInt32 counter = 0;
// TProgressTracker Variables
UInt32 GiftCounter = 0;
AGiftSummaryRow giftSummary = null;
FMainDS.AGiftDetail.DefaultView.Sort =
AGiftDetailTable.GetLedgerNumberDBName() + "," +
AGiftDetailTable.GetBatchNumberDBName() + "," +
AGiftDetailTable.GetGiftTransactionNumberDBName();
foreach (AGiftBatchRow giftBatch in FMainDS.AGiftBatch.Rows)
{
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
ReturnGiftBatchCount++;
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
string.Format(Catalog.GetString("Batch {0}"), giftBatch.BatchNumber),
20);
GiftCounter = 0;
if (!FTransactionsOnly & !Summary)
{
WriteGiftBatchLine(giftBatch);
}
foreach (AGiftRow gift in FMainDS.AGift.Rows)
{
if (gift.BatchNumber.Equals(giftBatch.BatchNumber) && gift.LedgerNumber.Equals(giftBatch.LedgerNumber))
{
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user"));
}
// Update progress tracker every 25 records
if (++GiftCounter % 25 == 0)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
string.Format(Catalog.GetString("Batch {0} - Exporting gifts"), giftBatch.BatchNumber),
(GiftCounter / 25 + 4) * 5 > 90 ? 90 : (GiftCounter / 25 + 4) * 5);
}
DataRowView[] selectedRowViews = FMainDS.AGiftDetail.DefaultView.FindRows(
new object[] { gift.LedgerNumber, gift.BatchNumber, gift.GiftTransactionNumber });
foreach (DataRowView rv in selectedRowViews)
{
AGiftDetailRow giftDetail = (AGiftDetailRow)rv.Row;
if (Summary)
{
FCurrencyCode = FUseBaseCurrency ? BaseCurrency : giftBatch.CurrencyCode;
decimal mapExchangeRateToBase = FUseBaseCurrency ? 1 : giftBatch.ExchangeRateToBase;
counter++;
String DictionaryKey = FCurrencyCode + ";" + giftBatch.BankCostCentre + ";" + giftBatch.BankAccountCode + ";" +
giftDetail.RecipientKey + ";" + giftDetail.MotivationGroupCode + ";" +
giftDetail.MotivationDetailCode;
if (sdSummary.TryGetValue(DictionaryKey, out giftSummary))
{
giftSummary.GiftTransactionAmount += giftDetail.GiftTransactionAmount;
giftSummary.GiftAmount += giftDetail.GiftAmount;
}
else
{
giftSummary = new AGiftSummaryRow();
/*
* summary_data.a_transaction_currency_c = lv_stored_currency_c
* summary_data.a_bank_cost_centre_c = a_gift_batch.a_bank_cost_centre_c
* summary_data.a_bank_account_code_c = a_gift_batch.a_bank_account_code_c
* summary_data.a_recipient_key_n = a_gift_detail.p_recipient_key_n
* summary_data.a_motivation_group_code_c = a_gift_detail.a_motivation_group_code_c
* summary_data.a_motivation_detail_code_c = a_gift_detail.a_motivation_detail_code_c
* summary_data.a_exchange_rate_to_base_n = lv_exchange_rate_n
* summary_data.a_gift_type_c = a_gift_batch.a_gift_type_c */
giftSummary.CurrencyCode = FCurrencyCode;
giftSummary.BankCostCentre = giftBatch.BankCostCentre;
giftSummary.BankAccountCode = giftBatch.BankAccountCode;
giftSummary.RecipientKey = giftDetail.RecipientKey;
giftSummary.MotivationGroupCode = giftDetail.MotivationGroupCode;
giftSummary.MotivationDetailCode = giftDetail.MotivationDetailCode;
giftSummary.GiftTransactionAmount = giftDetail.GiftTransactionAmount;
giftSummary.GiftAmount = giftDetail.GiftAmount;
sdSummary.Add(DictionaryKey, giftSummary);
}
//overwrite always because we want to have the last
giftSummary.ExchangeRateToBase = mapExchangeRateToBase;
}
else // not summary
{
WriteGiftLine(gift, giftDetail);
}
}
}
}
}
if (Summary)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Export Summary"),
95);
bool first = true;
foreach (KeyValuePair <string, AGiftSummaryRow> kvp in sdSummary)
{
if (!FTransactionsOnly && first)
{
WriteGiftBatchSummaryLine(kvp.Value);
first = false;
}
WriteGiftSummaryLine(kvp.Value);
}
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Gift batch export successful"),
100);
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
}
catch (ApplicationException)
{
//Show cancel condition
ReturnGiftBatchCount = -1;
TProgressTracker.CancelJob(DomainManager.GClientID.ToString());
}
catch (Exception ex)
{
TLogging.Log(ex.ToString());
//Show error condition
ReturnGiftBatchCount = -2;
FMessages.Add(new TVerificationResult(
"Exporting Gift Batches Terminated Unexpectedly",
ex.Message,
"An unexpected error occurred during the export of gift batches",
string.Empty,
TResultSeverity.Resv_Critical,
Guid.Empty));
TProgressTracker.CancelJob(DomainManager.GClientID.ToString());
}
if (ReturnGiftBatchCount > 0)
{
AExportString = FStringWriter.ToString();
}
else
{
AExportString = string.Empty;
}
AVerificationMessages = FMessages;
return(ReturnGiftBatchCount);
} // ExportAllGiftBatchData
/// <summary>
/// export all posted invoices for conference and seminar participants in this year
/// </summary>
public static void Export(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
Int32 AFinancialYear,
string ACostCentres)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "participants.csv"));
Console.WriteLine("Writing file: " + filename);
TDBTransaction Transaction = null;
AGiftDetailTable giftdetails = new AGiftDetailTable();
AGiftTable gifts = new AGiftTable();
AGiftBatchTable batches = new AGiftBatchTable();
PPersonTable persons = new PPersonTable();
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction,
delegate
{
// all gift details towards a costcentre that needs to be exported
string sql =
String.Format("SELECT DISTINCT D.* " +
"FROM PUB_{0} AS B, PUB_{1} AS G, PUB_{2} AS D " +
"WHERE B.{3} = {4} AND B.{5} = {6} AND B.{7}='{8}' " +
"AND G.{3} = B.{3} AND G.{9} = B.{9} " +
"AND D.{3} = G.{3} AND D.{9} = G.{9} AND D.{10} = G.{10} " +
"AND D.{11} IN ({12}) " +
"AND NOT D.{13} = '{14}'",
AGiftBatchTable.GetTableDBName(),
AGiftTable.GetTableDBName(),
AGiftDetailTable.GetTableDBName(),
AGiftBatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGiftBatchTable.GetBatchYearDBName(),
AFinancialYear,
AGiftBatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
AGiftBatchTable.GetBatchNumberDBName(),
AGiftTable.GetGiftTransactionNumberDBName(),
AGiftDetailTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
AGiftDetailTable.GetMotivationGroupCodeDBName(),
"GIFT");
DBAccess.GDBAccessObj.SelectDT(giftdetails, sql, Transaction, null, 0, 0);
sql = sql.Replace("SELECT DISTINCT D.*", "SELECT DISTINCT G.*");
DBAccess.GDBAccessObj.SelectDT(gifts, sql, Transaction, null, 0, 0);
gifts.DefaultView.Sort =
AGiftTable.GetBatchNumberDBName() + "," +
AGiftTable.GetGiftTransactionNumberDBName();
sql = sql.Replace("SELECT DISTINCT G.*", "SELECT DISTINCT B.*");
DBAccess.GDBAccessObj.SelectDT(batches, sql, Transaction, null, 0, 0);
batches.DefaultView.Sort = AGiftTable.GetBatchNumberDBName();
sql =
String.Format("SELECT DISTINCT P.* " +
"FROM PUB_{0} AS B, PUB_{1} AS G, PUB_{2} AS D, PUB.{15} AS P " +
"WHERE B.{3} = {4} AND B.{5} = {6} AND B.{7}='{8}' " +
"AND G.{3} = B.{3} AND G.{9} = B.{9} " +
"AND D.{3} = G.{3} AND D.{9} = G.{9} AND D.{10} = G.{10} " +
"AND D.{11} IN ({12}) " +
"AND NOT D.{13} = '{14}' " +
"AND P.{16} = G.{17}",
AGiftBatchTable.GetTableDBName(),
AGiftTable.GetTableDBName(),
AGiftDetailTable.GetTableDBName(),
AGiftBatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGiftBatchTable.GetBatchYearDBName(),
AFinancialYear,
AGiftBatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
AGiftBatchTable.GetBatchNumberDBName(),
AGiftTable.GetGiftTransactionNumberDBName(),
AGiftDetailTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
AGiftDetailTable.GetMotivationGroupCodeDBName(),
"GIFT",
PPersonTable.GetTableDBName(),
PPersonTable.GetPartnerKeyDBName(),
AGiftTable.GetDonorKeyDBName());
DBAccess.GDBAccessObj.SelectDT(persons, sql, Transaction, null, 0, 0);
persons.DefaultView.Sort = PPersonTable.GetPartnerKeyDBName();
});
StringBuilder sb = new StringBuilder();
foreach (AGiftDetailRow detail in giftdetails.Rows)
{
AGiftRow gift = (AGiftRow)gifts.DefaultView.FindRows(new object[] { detail.BatchNumber, detail.GiftTransactionNumber })[0].Row;
AGiftBatchRow batch = (AGiftBatchRow)batches.DefaultView.FindRows(detail.BatchNumber)[0].Row;
DataRowView[] personList = persons.DefaultView.FindRows(gift.DonorKey);
PPersonRow person = (personList.Length > 0 ? (PPersonRow)personList[0].Row : null);
sb.Append(StringHelper.StrMerge(
new string[] {
"GB" + detail.BatchNumber.ToString() + "_G" + detail.GiftTransactionNumber.ToString() +
"_D" + detail.DetailNumber.ToString(),
String.Format("{0:N}", detail.GiftTransactionAmount),
batch.GlEffectiveDate.ToString("yyyyMMdd"),
gift.DonorKey.ToString(),
person !=
null ? (person.DateOfBirth.HasValue ? person.DateOfBirth.Value.ToString("yyyyMMdd") : string.Empty) : string.Empty,
detail.CostCentreCode,
batch.BatchDescription,
detail.GiftCommentOne,
detail.GiftCommentTwo
}, ACSVSeparator));
sb.Append(ANewLine);
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
public static void LoadAll()
{
AGiftBatch = new AGiftBatchTable();
SerialisableDS.LoadAll(AGiftBatch, AGiftBatchTable.GetTableDBName());
}
