private void NewRowManual(ref AMotivationGroupRow ARow)
{
// Deal with primary key. MotivationGroupCode is unique and is 8 characters.
string newName = Catalog.GetString("NEWCODE");
Int32 countNewDetail = 0;
if (FMainDS.AMotivationGroup.Rows.Find(new object[] { FLedgerNumber, newName }) != null)
{
while (FMainDS.AMotivationGroup.Rows.Find(new object[] { FLedgerNumber, newName + countNewDetail.ToString() }) != null)
{
countNewDetail++;
}
newName += countNewDetail.ToString();
}
ARow.MotivationGroupCode = newName;
ARow.LedgerNumber = FLedgerNumber;
}
private void NewRowManual(ref AMotivationGroupRow ARow)
{
// Deal with primary key. MotivationGroupCode is unique and is 8 characters.
string newName = Catalog.GetString("NEWCODE");
Int32 countNewDetail = 0;
if (FMainDS.AMotivationGroup.Rows.Find(new object[] { FLedgerNumber, newName }) != null)
{
while (FMainDS.AMotivationGroup.Rows.Find(new object[] { FLedgerNumber, newName + countNewDetail.ToString() }) != null)
{
countNewDetail++;
}
newName += countNewDetail.ToString();
}
ARow.MotivationGroupCode = newName;
ARow.LedgerNumber = FLedgerNumber;
}
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 bool MaintainMotivationGroups(string action, Int32 ALedgerNumber,
String AMotivationGroupCode, String AMotivationGroupDescription, bool AGroupStatus,
out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = new TVerificationResultCollection();
GiftBatchTDS MainDS = new GiftBatchTDS();
if (action == "create")
{
AMotivationGroupRow row = MainDS.AMotivationGroup.NewRowTyped();
row.LedgerNumber = ALedgerNumber;
row.MotivationGroupCode = AMotivationGroupCode.ToUpper();
row.MotivationGroupDescription = AMotivationGroupDescription;
row.GroupStatus = AGroupStatus;
MainDS.AMotivationGroup.Rows.Add(row);
try
{
GiftBatchTDSAccess.SubmitChanges(MainDS);
}
catch (Exception)
{
return(false);
}
}
else if (action == "update")
{
MainDS = LoadMotivationDetails(ALedgerNumber, AMotivationGroupCode);
foreach (AMotivationGroupRow row in MainDS.AMotivationGroup.Rows)
{
if (row.MotivationGroupCode == AMotivationGroupCode)
{
row.MotivationGroupDescription = AMotivationGroupDescription;
row.GroupStatus = AGroupStatus;
}
}
try
{
GiftBatchTDSAccess.SubmitChanges(MainDS);
}
catch (Exception)
{
return(false);
}
}
else if (action == "delete")
{
MainDS = LoadMotivationDetails(ALedgerNumber, AMotivationGroupCode);
foreach (AMotivationGroupRow row in MainDS.AMotivationGroup.Rows)
{
if (row.MotivationGroupCode == AMotivationGroupCode)
{
row.Delete();
}
}
foreach (AMotivationDetailRow row in MainDS.AMotivationDetail.Rows)
{
if (row.MotivationGroupCode == AMotivationGroupCode)
{
row.Delete();
}
}
try
{
GiftBatchTDSAccess.SubmitChanges(MainDS);
}
catch (Exception)
{
return(false);
}
}
else
{
return(false);
}
return(true);
}
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