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