/// <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) { //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> /// 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 InitializeManualCode() { //FLedger is still zero at this point FMainDS.AGiftBatch.DefaultView.RowFilter = String.Format("{0} = '{1}'", AGiftBatchTable.GetBatchStatusDBName(), MFinanceConstants.BATCH_UNPOSTED ); FMainDS.AGiftBatch.DefaultView.Sort = AGiftBatchTable.GetBatchNumberDBName() + " DESC"; SelectBatchChanged(null, null); rbtNewBatch.Checked = true; }
private void InitializeManualCode() { grdDetails.Visible = false; //FLedger is still zero at this point FMainDS.AGiftBatch.DefaultView.RowFilter = String.Format("{0} = '{1}'", AGiftBatchTable.GetBatchStatusDBName(), MFinanceConstants.BATCH_UNPOSTED ); FMainDS.AGiftBatch.DefaultView.Sort = AGiftBatchTable.GetBatchNumberDBName() + " DESC"; SelectBatchChanged(null, null); //add the focused event temporarily to allow execution of more manual code right at the // end of the initialisation process. this.btnHelp.Enter += new System.EventHandler(this.HelpFocussed); }
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)); // Set the flag on the transaction tab to show the status dialog again when the transactions are loaded for a new year TFrmGiftBatch giftBatchForm = (TFrmGiftBatch)FPetraUtilsObject.GetForm(); giftBatchForm.GetTransactionsControl().ShowStatusDialogOnLoad = true; } 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; }
/// <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 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> /// 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) { StringHelper.JoinAndAppend(ref workingFilter, String.Format("({0} = '{1}') AND ({2} <> 0) AND (({3} = 0) OR ({3} = {2}))", AGiftBatchTable.GetBatchStatusDBName(), MFinanceConstants.BATCH_UNPOSTED, 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 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); }