/// <summary>
/// Direct access to the unposted gifts
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ADateEndOfPeriod"></param>
public GetUnpostedGiftInfo(int ALedgerNumber, DateTime ADateEndOfPeriod)
{
//IF CAN-FIND (FIRST a_gift_batch WHERE
// a_gift_batch.a_ledger_number_i EQ pv_ledger_number_i AND
// a_gift_batch.a_gl_effective_date_d LE
// a_accounting_period.a_period_end_date_d AND
// a_gift_batch.a_batch_status_c EQ "Unposted":U) THEN DO:
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[3];
ParametersArray[0] = new OdbcParameter("", OdbcType.Int);
ParametersArray[0].Value = ALedgerNumber;
ParametersArray[1] = new OdbcParameter("", OdbcType.Date);
ParametersArray[1].Value = ADateEndOfPeriod;
ParametersArray[2] = new OdbcParameter("", OdbcType.VarChar);
ParametersArray[2].Value = MFinanceConstants.BATCH_UNPOSTED;
bool NewTransaction;
TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
string strSQL = "SELECT * FROM PUB_" + AGiftBatchTable.GetTableDBName() + " ";
strSQL += "WHERE " + AGiftBatchTable.GetLedgerNumberDBName() + " = ? ";
strSQL += "AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " <= ? ";
strSQL += "AND " + AGiftBatchTable.GetBatchStatusDBName() + " = ? ";
FDataTable = DBAccess.GDBAccessObj.SelectDT(
strSQL, AAccountingPeriodTable.GetTableDBName(), transaction, ParametersArray);
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="APetraUtilsObject">Reference to the PetraUtilsObject for the form</param>
/// <param name="ALedgerNumber">Ledger number</param>
/// <param name="AMainDS">The main data set</param>
/// <param name="AFilterFindPanelObject">The filter panel control object</param>
public TUC_GiftBatches_LoadAndFilter(TFrmPetraEditUtils APetraUtilsObject,
int ALedgerNumber,
GiftBatchTDS AMainDS,
TFilterAndFindPanel AFilterFindPanelObject)
{
FPetraUtilsObject = APetraUtilsObject;
FLedgerNumber = ALedgerNumber;
FMainDS = AMainDS;
FFilterFindPanelObject = AFilterFindPanelObject;
FrbtEditing = (RadioButton)AFilterFindPanelObject.FilterPanelControls.FindControlByName("rbtEditing");
FrbtPosting = (RadioButton)AFilterFindPanelObject.FilterPanelControls.FindControlByName("rbtPosting");
FrbtAll = (RadioButton)AFilterFindPanelObject.FilterPanelControls.FindControlByName("rbtAll");
FcmbYearEnding = (TCmbAutoComplete)AFilterFindPanelObject.FilterPanelControls.FindControlByName("cmbYearEnding");
FcmbPeriod = (TCmbAutoComplete)AFilterFindPanelObject.FilterPanelControls.FindControlByName("cmbPeriod");
FMainDS.AGiftBatch.DefaultView.Sort = String.Format("{0}, {1} DESC",
AGiftBatchTable.GetLedgerNumberDBName(),
AGiftBatchTable.GetBatchNumberDBName()
);
// Populate the Year ComboBox with available years for the specified ledger
TFinanceControls.InitialiseAvailableGiftYearsList(ref FcmbYearEnding, FLedgerNumber);
//TLogging.Log("Gift Years completed");
// Ensure that we start with the status set to 'editing'.
FrbtEditing.Checked = true;
//TLogging.Log("Editing checkbox selected");
}
/// <summary>
/// Direct access to the unposted gifts
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ADateEndOfPeriod"></param>
public GetUnpostedGiftInfo(int ALedgerNumber, DateTime ADateEndOfPeriod)
{
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[3];
ParametersArray[0] = new OdbcParameter("", OdbcType.Int);
ParametersArray[0].Value = ALedgerNumber;
ParametersArray[1] = new OdbcParameter("", OdbcType.Date);
ParametersArray[1].Value = ADateEndOfPeriod;
ParametersArray[2] = new OdbcParameter("", OdbcType.VarChar);
ParametersArray[2].Value = MFinanceConstants.BATCH_UNPOSTED;
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref transaction,
delegate
{
string strSQL = "SELECT * FROM PUB_" + AGiftBatchTable.GetTableDBName() +
" WHERE " + AGiftBatchTable.GetLedgerNumberDBName() + " = ?" +
" AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " <= ?" +
" AND " + AGiftBatchTable.GetBatchStatusDBName() + " = ? " +
" ORDER BY " + AGiftBatchTable.GetBatchNumberDBName();
FDataTable = DBAccess.GDBAccessObj.SelectDT(
strSQL, AAccountingPeriodTable.GetTableDBName(), transaction, ParametersArray);
});
}
/// <summary>
/// Direct access to the unposted gifts
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ADateEndOfPeriod"></param>
public GetUnpostedGiftInfo(int ALedgerNumber, DateTime ADateEndOfPeriod)
{
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[3];
ParametersArray[0] = new OdbcParameter("", OdbcType.Int);
ParametersArray[0].Value = ALedgerNumber;
ParametersArray[1] = new OdbcParameter("", OdbcType.Date);
ParametersArray[1].Value = ADateEndOfPeriod;
ParametersArray[2] = new OdbcParameter("", OdbcType.VarChar);
ParametersArray[2].Value = MFinanceConstants.BATCH_UNPOSTED;
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetUnpostedGiftInfo");
db.ReadTransaction(
ref transaction,
delegate
{
string strSQL = "SELECT * FROM PUB_" + AGiftBatchTable.GetTableDBName() +
" WHERE " + AGiftBatchTable.GetLedgerNumberDBName() + " = ?" +
" AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " <= ?" +
" AND " + AGiftBatchTable.GetBatchStatusDBName() + " = ? " +
" ORDER BY " + AGiftBatchTable.GetBatchNumberDBName();
FDataTable = db.SelectDT(
strSQL, AAccountingPeriodTable.GetTableDBName(), transaction, ParametersArray);
});
db.CloseDBConnection();
}
/// <summary>
/// Select the last Gift and motivation details of the gifts that were given within the time period from one partner.
/// </summary>
/// <param name="ADonorKey">Partner key of the donor</param>
/// <param name="ALedgerNumber">Ledger number</param>
/// <param name="AStartDate">Start date of the period</param>
/// <param name="AEndDate">End date of the period</param>
/// <param name="ACurrency">Currency: Base or International</param>
/// <returns>True if a gift was found; otherwise false</returns>
private bool SelectLastGift(Int64 ADonorKey, Int64 ALedgerNumber, DateTime AStartDate, DateTime AEndDate, String ACurrency)
{
String StrSql = "SELECT " + AGiftTable.GetDateEnteredDBName() + ", " +
AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetMotivationGroupCodeDBName() + ", " +
AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetMotivationDetailCodeDBName();
if (ACurrency == "Base")
{
StrSql = StrSql + ", " + AGiftDetailTable.GetGiftAmountDBName() + " AS CurrentAmount";
}
else
{
StrSql = StrSql + ", " + AGiftDetailTable.GetGiftAmountIntlDBName() + " AS CurrentAmount";
}
StrSql = StrSql +
" FROM " + AGiftTable.GetTableDBName() +
" , " + AGiftDetailTable.GetTableDBName() +
", " + AGiftBatchTable.GetTableDBName() +
" WHERE " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetLedgerNumberDBName() + " = " + ALedgerNumber.ToString() +
" AND " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetLedgerNumberDBName() + " = " +
ALedgerNumber.ToString() +
" AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetLedgerNumberDBName() + " = " + ALedgerNumber.ToString() +
" AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDonorKeyDBName() + " = " + ADonorKey.ToString() +
" AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetBatchNumberDBName() + " = " + AGiftDetailTable.GetTableDBName() +
"." + AGiftDetailTable.GetBatchNumberDBName() +
" AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchNumberDBName() + " = " +
AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetBatchNumberDBName() +
" AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetGiftTransactionNumberDBName() + " = " +
AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetGiftTransactionNumberDBName() +
" AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDateEnteredDBName() + " BETWEEN '" + AStartDate.ToString(
"yyyy-MM-dd") + "' AND '" + AEndDate.ToString("yyyy-MM-dd") + "'" +
" AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchStatusDBName() + " = 'Posted'" +
" ORDER BY " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDateEnteredDBName() + " DESC LIMIT 1";
DataTable Table = situation.GetDatabaseConnection().SelectDT(StrSql, "table",
situation.GetDatabaseConnection().Transaction, new OdbcParameter[] { });
if (Table.Rows.Count > 0)
{
DateTime DateEntered = (DateTime)Table.Rows[0][AGiftTable.GetDateEnteredDBName()];
decimal CurrentAmount = Convert.ToDecimal(Table.Rows[0]["CurrentAmount"]);
String MotivationDetail = (String)Table.Rows[0][AGiftDetailTable.GetMotivationDetailCodeDBName()];
String MotivationGroup = (String)Table.Rows[0][AGiftDetailTable.GetMotivationGroupCodeDBName()];
situation.GetParameters().Add("LastGiftDate", new TVariant(DateEntered));
situation.GetParameters().Add("LastGiftAmount", new TVariant(CurrentAmount));
situation.GetParameters().Add("MotivationDetail", new TVariant(MotivationDetail));
situation.GetParameters().Add("MotivationGroup", new TVariant(MotivationGroup));
return(true);
}
return(false);
}
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;
}
private void InitialiseLedgerControls()
{
// Load Motivation detail in this central place; it will be used by UC_GiftTransactions
AMotivationDetailTable motivationDetail = (AMotivationDetailTable)TDataCache.TMFinance.GetCacheableFinanceTable(
TCacheableFinanceTablesEnum.MotivationList,
FLedgerNumber);
motivationDetail.TableName = FMainDS.AMotivationDetail.TableName;
FMainDS.Merge(motivationDetail);
FMainDS.AcceptChanges();
FMainDS.AGiftBatch.DefaultView.Sort = String.Format("{0}, {1} DESC",
AGiftBatchTable.GetLedgerNumberDBName(),
AGiftBatchTable.GetBatchNumberDBName()
);
SetupExtraGridFunctionality();
FAccountAndCostCentreLogicObject.RefreshBankAccountAndCostCentreData(FLoadAndFilterLogicObject);
// if this form is readonly, then we need all codes, because old codes might have been used
bool ActiveOnly = this.Enabled;
SetupAccountAndCostCentreCombos(ActiveOnly);
cmbDetailMethodOfPaymentCode.AddNotSetRow("", "");
TFinanceControls.InitialiseMethodOfPaymentCodeList(ref cmbDetailMethodOfPaymentCode, ActiveOnly);
TLedgerSelection.GetCurrentPostingRangeDates(FLedgerNumber,
out FStartDateCurrentPeriod,
out FEndDateLastForwardingPeriod,
out FDefaultDate);
lblValidDateRange.Text = String.Format(Catalog.GetString("Valid between {0} and {1}"),
FStartDateCurrentPeriod.ToShortDateString(), FEndDateLastForwardingPeriod.ToShortDateString());
FLoadAndFilterLogicObject.InitialiseDataSources(cmbDetailBankCostCentre, cmbDetailBankAccountCode);
}
/// <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 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 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>
/// 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;
}
}
}
}