public static GiftBatchTDS LoadMotivationDetails(Int32 ALedgerNumber)
{
GiftBatchTDS MainDS = new GiftBatchTDS();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
AMotivationGroupAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
AMotivationDetailFeeAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
});
// Accept row changes here so that the Client gets 'unmodified' rows
MainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
MainDS.RemoveEmptyTables();
return(MainDS);
}
public static GiftBatchTDS LoadMotivationDetails(Int32 ALedgerNumber, string AMotivationGroupCode)
{
GiftBatchTDS MainDS = new GiftBatchTDS();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadMotivationDetails");
db.ReadTransaction(
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
if (AMotivationGroupCode.Length > 0)
{
AMotivationGroupAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, AMotivationGroupCode, Transaction);
AMotivationDetailAccess.LoadViaAMotivationGroup(MainDS, ALedgerNumber, AMotivationGroupCode, Transaction);
}
else
{
AMotivationGroupAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
}
AMotivationDetailFeeAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
});
// Accept row changes here so that the Client gets 'unmodified' rows
MainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
MainDS.RemoveEmptyTables();
return(MainDS);
}
public static void ExportGifts(int ALedgerNumber,
string ACostCentre,
string AAcctCode,
string AMonthName,
int APeriodNumber,
DateTime APeriodStartDate,
DateTime APeriodEndDate,
string ACurrencySelect,
int AIchNumber,
DataTable AExportDataTable,
TVerificationResultCollection AVerificationResult)
{
/* Define local variables */
bool FirstLoopFlag = true;
Int32 LastRecipKey = 0;
string LastGroup = string.Empty;
string LastDetail = string.Empty;
string LastDetailDesc = string.Empty;
decimal IndividualDebitTotal = 0;
decimal IndividualCreditTotal = 0;
string ExportDescription = string.Empty;
Int32 tmpLastRecipKey = 0;
string tmpLastGroup = string.Empty;
string tmpLastDetail = string.Empty;
//Find and total each gift transaction
string SQLStmt = TDataBase.ReadSqlFile("ICH.HOSAExportGiftsInner.sql");
TDBTransaction DBTransaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref DBTransaction,
delegate
{
OdbcParameter parameter;
List <OdbcParameter> parameters = new List <OdbcParameter>();
parameter = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameter.Value = ALedgerNumber;
parameters.Add(parameter);
parameter = new OdbcParameter("CostCentre", OdbcType.VarChar);
parameter.Value = ACostCentre;
parameters.Add(parameter);
parameter = new OdbcParameter("ICHNumber", OdbcType.Int);
parameter.Value = AIchNumber;
parameters.Add(parameter);
parameter = new OdbcParameter("BatchStatus", OdbcType.VarChar);
parameter.Value = MFinanceConstants.BATCH_POSTED;
parameters.Add(parameter);
parameter = new OdbcParameter("StartDate", OdbcType.DateTime);
parameter.Value = APeriodStartDate;
parameters.Add(parameter);
parameter = new OdbcParameter("EndDate", OdbcType.DateTime);
parameter.Value = APeriodEndDate;
parameters.Add(parameter);
parameter = new OdbcParameter("AccountCode", OdbcType.VarChar);
parameter.Value = AAcctCode;
parameters.Add(parameter);
DataTable TmpTable = DBAccess.GDBAccessObj.SelectDT(SQLStmt, "table", DBTransaction, parameters.ToArray());
foreach (DataRow untypedTransRow in TmpTable.Rows)
{
/* Print totals etc. found for last recipient */
/* Only do after first loop due to last recipient key check */
tmpLastRecipKey = Convert.ToInt32(untypedTransRow[8]); //a_gift_detail.p_recipient_key_n
tmpLastGroup = untypedTransRow[6].ToString(); //a_motivation_detail.a_motivation_group_code_c
tmpLastDetail = untypedTransRow[7].ToString(); //a_motivation_detail.a_motivation_detail_code_c
if (!FirstLoopFlag &&
((tmpLastRecipKey != LastRecipKey) ||
(tmpLastGroup != LastGroup) ||
(tmpLastDetail != LastDetail)
)
)
{
if ((IndividualCreditTotal != 0) ||
(IndividualDebitTotal != 0))
{
if (LastRecipKey != 0)
{
/* Find partner short name details */
PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(LastRecipKey, DBTransaction);
PPartnerRow PartnerRow = (PPartnerRow)PartnerTable.Rows[0];
LastDetailDesc += " : " + PartnerRow.PartnerShortName;
ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" + LastDetailDesc;
}
else
{
AMotivationGroupTable MotivationGroupTable = AMotivationGroupAccess.LoadByPrimaryKey(ALedgerNumber,
LastGroup,
DBTransaction);
AMotivationGroupRow MotivationGroupRow = (AMotivationGroupRow)MotivationGroupTable.Rows[0];
ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" +
MotivationGroupRow.MotivationGroupDescription.TrimEnd(
new Char[] { (' ') }) + "," + LastDetailDesc;
}
//Add data to export table
DataRow DR = (DataRow)AExportDataTable.NewRow();
DR[0] = ACostCentre;
DR[1] = ConvertAccount(AAcctCode);
DR[2] = ExportDescription;
DR[3] = "ICH-" + APeriodNumber.ToString("00");
DR[4] = APeriodEndDate;
DR[5] = IndividualDebitTotal;
DR[6] = IndividualCreditTotal;
AExportDataTable.Rows.Add(DR);
/* Reset total */
IndividualDebitTotal = 0;
IndividualCreditTotal = 0;
}
}
if (ACurrencySelect == MFinanceConstants.CURRENCY_BASE)
{
Decimal GiftAmount = Convert.ToDecimal(untypedTransRow[4]); //a_gift_detail.a_gift_amount_n
if (GiftAmount < 0)
{
IndividualDebitTotal -= GiftAmount;
}
else
{
IndividualCreditTotal += GiftAmount;
}
}
else
{
Decimal IntlGiftAmount = Convert.ToDecimal(untypedTransRow[5]); //a_gift_detail.a_gift_amount_intl_n
if (IntlGiftAmount < 0)
{
IndividualDebitTotal -= IntlGiftAmount;
}
else
{
IndividualCreditTotal += IntlGiftAmount;
}
}
/* Set loop variables */
LastRecipKey = tmpLastRecipKey;
LastGroup = tmpLastGroup;
LastDetail = tmpLastDetail;
LastDetailDesc = Convert.ToString(untypedTransRow[10]); //a_motivation_detail.a_motivation_detail_desc_c
FirstLoopFlag = false;
} // foreach
/* Print totals etc. found for last recipient */
/* Only do after first loop due to last recipient key check */
if (!FirstLoopFlag && ((IndividualCreditTotal != 0) || (IndividualDebitTotal != 0)))
{
if (LastRecipKey != 0)
{
/* Find partner short name details */
PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(LastRecipKey, DBTransaction);
PPartnerRow PartnerRow = (PPartnerRow)PartnerTable.Rows[0];
LastDetailDesc += ":" + PartnerRow.PartnerShortName;
ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" + LastDetailDesc;
}
else
{
AMotivationGroupTable MotivationGroupTable =
AMotivationGroupAccess.LoadByPrimaryKey(ALedgerNumber, LastGroup, DBTransaction);
AMotivationGroupRow MotivationGroupRow = (AMotivationGroupRow)MotivationGroupTable.Rows[0];
ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" +
MotivationGroupRow.MotivationGroupDescription.TrimEnd() + "," + LastDetailDesc;
}
//Add rows to export table
DataRow DR = (DataRow)AExportDataTable.NewRow();
DR[0] = ACostCentre;
DR[1] = ConvertAccount(AAcctCode);
DR[2] = ExportDescription;
DR[3] = "ICH-" + APeriodNumber.ToString("00");
DR[4] = APeriodEndDate;;
DR[5] = IndividualDebitTotal;
DR[6] = IndividualCreditTotal;
AExportDataTable.Rows.Add(DR);
}
}); // Get NewOrExisting AutoReadTransaction
} // Export Gifts
public static void ExportGifts(int ALedgerNumber,
string ACostCentre,
string AAcctCode,
string AMonthName,
int APeriodNumber,
DateTime APeriodStartDate,
DateTime APeriodEndDate,
string ACurrencySelect,
int AIchNumber,
DataTable AExportDataTable,
TVerificationResultCollection AVerificationResult)
{
/* Define local variables */
bool FirstLoopFlag = true;
Int32 LastRecipKey = 0;
string LastGroup = string.Empty;
string LastDetail = string.Empty;
string LastDetailDesc = string.Empty;
decimal IndividualDebitTotal = 0;
decimal IndividualCreditTotal = 0;
string ExportDescription = string.Empty;
Int32 tmpLastRecipKey = 0;
string tmpLastGroup = string.Empty;
string tmpLastDetail = string.Empty;
//Find and total each gift transaction
TDBTransaction DBTransaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref DBTransaction,
delegate
{
String IchNumberFilter = "";
if (AIchNumber > 0)
{
IchNumberFilter = " AND GiftDetail.a_ich_number_i = " + AIchNumber;
}
String Query = "SELECT " +
" GiftDetail.a_ledger_number_i, " +
" GiftDetail.a_batch_number_i, " +
" GiftDetail.a_gift_transaction_number_i, " +
" GiftDetail.a_detail_number_i, " +
" GiftDetail.a_gift_amount_n, " +
" GiftDetail.a_gift_amount_intl_n, " +
" GiftDetail.a_motivation_group_code_c, " +
" GiftDetail.a_motivation_detail_code_c, " +
" GiftDetail.p_recipient_key_n, " +
" Gift.a_gift_status_c, " +
" MotiviationDetail.a_motivation_detail_desc_c, " +
" GiftBatch.a_batch_description_c " +
" FROM " +
" public.a_gift_detail AS GiftDetail, " +
" public.a_gift_batch AS GiftBatch, " +
" public.a_motivation_detail AS MotiviationDetail, " +
" public.a_gift AS Gift " +
" WHERE " +
" GiftDetail.a_ledger_number_i = GiftBatch.a_ledger_number_i " +
" AND GiftDetail.a_batch_number_i = GiftBatch.a_batch_number_i " +
" AND GiftDetail.a_ledger_number_i = MotiviationDetail.a_ledger_number_i " +
" AND GiftDetail.a_motivation_group_code_c = MotiviationDetail.a_motivation_group_code_c " +
" AND GiftDetail.a_motivation_detail_code_c = MotiviationDetail.a_motivation_detail_code_c " +
" AND GiftDetail.a_ledger_number_i = Gift.a_ledger_number_i " +
" AND GiftDetail.a_batch_number_i = Gift.a_batch_number_i " +
" AND GiftDetail.a_gift_transaction_number_i = Gift.a_gift_transaction_number_i " +
" AND GiftDetail.a_ledger_number_i = " + ALedgerNumber +
" AND GiftDetail.a_cost_centre_code_c = '" + ACostCentre + "'" +
IchNumberFilter +
" AND GiftBatch.a_batch_status_c = '" + MFinanceConstants.BATCH_POSTED + "'" +
" AND GiftBatch.a_gl_effective_date_d >= " + APeriodStartDate.ToString("#yyyy-MM-dd#") +
" AND GiftBatch.a_gl_effective_date_d <= " + APeriodEndDate.ToString("#yyyy-MM-dd#") +
" AND MotiviationDetail.a_account_code_c = '" + AAcctCode + "'" +
" ORDER BY " +
" GiftDetail.p_recipient_key_n ASC, " +
" GiftDetail.a_motivation_group_code_c ASC, " +
" GiftDetail.a_motivation_detail_code_c ASC;";
DataTable TmpTable = DBAccess.GDBAccessObj.SelectDT(Query, "table", DBTransaction);
foreach (DataRow Row in TmpTable.Rows)
{
/* Print totals etc. found for last recipient */
/* Only do after first loop due to last recipient key check */
tmpLastRecipKey = Convert.ToInt32(Row["p_recipient_key_n"]);
tmpLastGroup = Row["a_motivation_group_code_c"].ToString();
tmpLastDetail = Row["a_motivation_detail_code_c"].ToString();
if (!FirstLoopFlag &&
((tmpLastRecipKey != LastRecipKey) ||
(tmpLastGroup != LastGroup) ||
(tmpLastDetail != LastDetail)
)
)
{
if ((IndividualCreditTotal != 0) ||
(IndividualDebitTotal != 0))
{
if (LastRecipKey != 0)
{
/* Find partner short name details */
PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(LastRecipKey, DBTransaction);
PPartnerRow PartnerRow = (PPartnerRow)PartnerTable.Rows[0];
LastDetailDesc += " : " + PartnerRow.PartnerShortName;
ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" + LastDetailDesc;
}
else
{
AMotivationGroupTable MotivationGroupTable = AMotivationGroupAccess.LoadByPrimaryKey(ALedgerNumber,
LastGroup,
DBTransaction);
AMotivationGroupRow MotivationGroupRow = (AMotivationGroupRow)MotivationGroupTable.Rows[0];
ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" +
MotivationGroupRow.MotivationGroupDescription.TrimEnd(
new Char[] { (' ') }) + "," + LastDetailDesc;
}
//Add data to export table
DataRow DR = (DataRow)AExportDataTable.NewRow();
DR[0] = ACostCentre;
DR[1] = ConvertAccount(AAcctCode);
DR[2] = ExportDescription;
DR[3] = "ICH-" + APeriodNumber.ToString("00");
DR[4] = APeriodEndDate;
DR[5] = IndividualDebitTotal;
DR[6] = IndividualCreditTotal;
AExportDataTable.Rows.Add(DR);
/* Reset total */
IndividualDebitTotal = 0;
IndividualCreditTotal = 0;
}
}
if (ACurrencySelect == MFinanceConstants.CURRENCY_BASE)
{
Decimal GiftAmount = Convert.ToDecimal(Row["a_gift_amount_n"]);
if (GiftAmount < 0)
{
IndividualDebitTotal -= GiftAmount;
}
else
{
IndividualCreditTotal += GiftAmount;
}
}
else
{
Decimal IntlGiftAmount = Convert.ToDecimal(Row["a_gift_amount_intl_n"]);
if (IntlGiftAmount < 0)
{
IndividualDebitTotal -= IntlGiftAmount;
}
else
{
IndividualCreditTotal += IntlGiftAmount;
}
}
/* Set loop variables */
LastRecipKey = tmpLastRecipKey;
LastGroup = tmpLastGroup;
LastDetail = tmpLastDetail;
LastDetailDesc = Convert.ToString(Row["a_motivation_detail_desc_c"]);
FirstLoopFlag = false;
} // foreach
/* Print totals etc. found for last recipient */
/* Only do after first loop due to last recipient key check */
if (!FirstLoopFlag && ((IndividualCreditTotal != 0) || (IndividualDebitTotal != 0)))
{
if (LastRecipKey != 0)
{
/* Find partner short name details */
PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(LastRecipKey, DBTransaction);
PPartnerRow PartnerRow = (PPartnerRow)PartnerTable.Rows[0];
LastDetailDesc += ":" + PartnerRow.PartnerShortName;
ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" + LastDetailDesc;
}
else
{
AMotivationGroupTable MotivationGroupTable =
AMotivationGroupAccess.LoadByPrimaryKey(ALedgerNumber, LastGroup, DBTransaction);
AMotivationGroupRow MotivationGroupRow = (AMotivationGroupRow)MotivationGroupTable.Rows[0];
ExportDescription = ALedgerNumber.ToString() + AMonthName + ":" +
MotivationGroupRow.MotivationGroupDescription.TrimEnd() + "," + LastDetailDesc;
}
//Add rows to export table
DataRow DR = (DataRow)AExportDataTable.NewRow();
DR[0] = ACostCentre;
DR[1] = ConvertAccount(AAcctCode);
DR[2] = ExportDescription;
DR[3] = "ICH-" + APeriodNumber.ToString("00");
DR[4] = APeriodEndDate;;
DR[5] = IndividualDebitTotal;
DR[6] = IndividualCreditTotal;
AExportDataTable.Rows.Add(DR);
}
}); // Get NewOrExisting AutoReadTransaction
} // Export Gifts
