/// <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; } } } }