/// Upgrade to version 2020-06 public static bool UpgradeDatabase202004_202006(TDataBase ADataBase) { // add new tables for data consent, and some values // add permission for p_country TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; ADataBase.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { string[] SqlStmts = TDataBase.ReadSqlFile("Upgrade202004_202006.sql").Split(new char[] { ';' }); foreach (string stmt in SqlStmts) { if (stmt.Trim().Length > 0) { ADataBase.ExecuteNonQuery(stmt, SubmitChangesTransaction); } } SubmitOK = true; }); return(true); }
/// Upgrade to version 2018-09 public static bool UpgradeDatabase201808_201809() { // there are some changes to the database structure TDataBase db = DBAccess.Connect("TDBUpgrade"); TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; db.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { string[] SqlStmts = TDataBase.ReadSqlFile("Upgrade201808_201809.sql").Split(new char[] { ';' }); foreach (string stmt in SqlStmts) { if (stmt.Trim().Length > 0) { db.ExecuteNonQuery(stmt, SubmitChangesTransaction); } } SubmitOK = true; }); return(true); }
/// Upgrade to version 2019-10 public static bool UpgradeDatabase201909_201910(TDataBase ADataBase) { // new module PARTNERSELFSERVICE and new user SELFSERVICE TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; ADataBase.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { string[] SqlStmts = TDataBase.ReadSqlFile("Upgrade201909_201910.sql").Split(new char[] { ';' }); foreach (string stmt in SqlStmts) { if (stmt.Trim().Length > 0) { ADataBase.ExecuteNonQuery(stmt, SubmitChangesTransaction); } } SubmitOK = true; }); return(true); }
/// <summary> /// calculate an extract from a report: all partners who have a Contact Log meeting selected criteria /// </summary> /// <param name="AParameters"></param> /// <param name="AResults"></param> /// <returns></returns> public static bool CalculateExtract(TParameterList AParameters, TResultList AResults) { string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractPartnerByContactLog.sql"); QueryPartnerByContactLog ExtractQuery = new QueryPartnerByContactLog(); return(ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults)); }
/// <summary> /// Given a string list of Partner Keys, return a table with the best address for each partner /// </summary> /// <param name="DonorList">Comma-separated list of partner keys, or SQL query returning partner keys only</param> /// <param name="ATransaction">The current database transaction</param> /// <param name="APartnerDetails">if true: Adds partner short name and partner class columns</param> /// <param name="ASanitizeFieldNames">If true, fieldnames are tweaked for reporting</param> /// <returns></returns> public static DataTable GetBestAddressForPartners(String DonorList, TDBTransaction ATransaction, Boolean APartnerDetails = false, Boolean ASanitizeFieldNames = false) { DataTable ResultTable = new DataTable(); if (DonorList == String.Empty) { return ResultTable; } string Query = TDataBase.ReadSqlFile( ASanitizeFieldNames ? "Partner.CommonAddressTools.GetBestAddressSanitized.sql" : "Partner.CommonAddressTools.GetBestAddress.sql" ); Query = Query.Replace("{DonorList}", DonorList); if (APartnerDetails) { Query = "WITH AddressTable AS (" + Query + ") SELECT DISTINCT AddressTable.*, " + (ASanitizeFieldNames ? " p_partner.p_partner_short_name_c AS ShortName, p_partner.p_partner_class_c AS PartnerClass" : " p_partner.p_partner_short_name_c, p_partner.p_partner_class_c " ) + " FROM" + " AddressTable JOIN p_partner ON p_partner.p_partner_key_n=AddressTable.p_partner_key_n"; } ResultTable = ATransaction.DataBaseObj.SelectDT(Query, "PartnersAddresses", ATransaction); return ResultTable; }
public static bool TypeAheadMotivationDetail(Int32 ALedgerNumber, string ASearch, Int32 ALimit, out DataTable AResult) { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TypeAheadMotivationDetail"); DataTable result = new DataTable(); db.ReadTransaction( ref Transaction, delegate { string SqlStmt = TDataBase.ReadSqlFile("Finance.TypeAheadMotivationDetail.sql"); OdbcParameter[] parameters = new OdbcParameter[3]; parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int); parameters[0].Value = ALedgerNumber; parameters[1] = new OdbcParameter("MotivationDetailCode", OdbcType.VarChar); parameters[1].Value = "%" + ASearch + "%"; parameters[2] = new OdbcParameter("DescDetail", OdbcType.VarChar); parameters[2].Value = "%" + ASearch + "%"; SqlStmt += " LIMIT " + ALimit.ToString(); result = db.SelectDT(SqlStmt, "Search", Transaction, parameters); }); db.CloseDBConnection(); AResult = result; return(result.Rows.Count > 0); }
public static bool TypeAheadMotivationDetail(Int32 ALedgerNumber, string ASearch, Int32 ALimit, out DataTable AResult) { TDBTransaction Transaction = null; DataTable result = new DataTable(); DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { string SqlStmt = TDataBase.ReadSqlFile("Finance.TypeAheadMotivationDetail.sql"); OdbcParameter[] parameters = new OdbcParameter[3]; parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int); parameters[0].Value = ALedgerNumber; parameters[1] = new OdbcParameter("MotivationDetailCode", OdbcType.VarChar); parameters[1].Value = "%" + ASearch + "%"; parameters[2] = new OdbcParameter("DescDetail", OdbcType.VarChar); parameters[2].Value = "%" + ASearch + "%"; SqlStmt += " LIMIT " + ALimit.ToString(); result = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "Search", Transaction, parameters); }); AResult = result; return(result.Rows.Count > 0); }
public static bool GetGiftsForFieldChangeAdjustment(ref GiftBatchTDS AGiftDS, Int32 ALedgerNumber, Int64 ARecipientKey, DateTime AStartDate, DateTime AEndDate, Int64 AOldField, out TVerificationResultCollection AMessages) { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetGiftsForFieldChangeAdjustment"); GiftBatchTDS MainDS = new GiftBatchTDS(); AMessages = new TVerificationResultCollection(); db.ReadTransaction( ref Transaction, delegate { string SqlStmt = TDataBase.ReadSqlFile("Gift.GetGiftsToAdjustField.sql"); List <OdbcParameter> parameters = new List <OdbcParameter>(); OdbcParameter param = new OdbcParameter("LedgerNumber", OdbcType.Int); param.Value = ALedgerNumber; parameters.Add(param); param = new OdbcParameter("StartDate", OdbcType.Date); param.Value = AStartDate; parameters.Add(param); param = new OdbcParameter("EndDate", OdbcType.Date); param.Value = AEndDate; parameters.Add(param); param = new OdbcParameter("RecipientKey", OdbcType.BigInt); param.Value = ARecipientKey; parameters.Add(param); param = new OdbcParameter("OldField", OdbcType.BigInt); param.Value = AOldField; parameters.Add(param); db.Select(MainDS, SqlStmt, MainDS.AGiftDetail.TableName, Transaction, parameters.ToArray()); // get additional data foreach (GiftBatchTDSAGiftDetailRow Row in MainDS.AGiftDetail.Rows) { AGiftBatchAccess.LoadByPrimaryKey(MainDS, Row.LedgerNumber, Row.BatchNumber, Transaction); AGiftRow GiftRow = AGiftAccess.LoadByPrimaryKey(MainDS, Row.LedgerNumber, Row.BatchNumber, Row.GiftTransactionNumber, Transaction); Row.DateEntered = GiftRow.DateEntered; Row.DonorKey = GiftRow.DonorKey; Row.IchNumber = 0; Row.DonorName = PPartnerAccess.LoadByPrimaryKey(Row.DonorKey, Transaction)[0].PartnerShortName; } }); AGiftDS = MainDS; db.CloseDBConnection(); return(CheckGiftsNotPreviouslyReversed(AGiftDS, out AMessages)); }
/// <summary> /// calculate an extract from a report: all partners of a given type (or selection of multiple types) /// </summary> /// <param name="AParameters"></param> /// <param name="AResults"></param> /// <returns></returns> public static bool CalculateExtract(TParameterList AParameters, TResultList AResults) { string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractPartnerByGeneralCriteria.sql"); // create a new object of this class and control extract calculation from base class QueryPartnerByGeneralCriteria ExtractQuery = new QueryPartnerByGeneralCriteria(); return(ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults)); }
/// <summary> /// calculate an extract from a report: all partners living in a given city /// </summary> /// <param name="AParameters"></param> /// <param name="AResults"></param> /// <param name="AExtractId"></param> /// <returns></returns> public static bool CalculateExtract(TParameterList AParameters, TResultList AResults, out int AExtractId) { string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractFamilyMembers.sql"); // create a new object of this class and control extract calculation from base class QueryFamilyMembersExtract ExtractQuery = new QueryFamilyMembersExtract(); return(ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults, out AExtractId)); }
/// <summary> /// calculate an extract from a report: all donors that have given to particular fields (ledgers) /// </summary> /// <param name="AParameters"></param> /// <param name="AResults"></param> /// <returns></returns> public static bool CalculateExtract(TParameterList AParameters, TResultList AResults) { string SqlStmt = TDataBase.ReadSqlFile("Gift.Queries.ExtractDonorByField.sql"); // create a new object of this class and control extract calculation from base class QueryDonorByField ExtractQuery = new QueryDonorByField(); return(ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults)); }
/// <summary> /// get the family keys of the specified persons /// </summary> /// <param name="AParameters"></param> /// <param name="AResults"></param> public static DataTable GetFamilyKeys(TParameterList AParameters, TResultList AResults) { SortedList <string, string> Defines = new SortedList <string, string>(); List <OdbcParameter> SqlParameterList = new List <OdbcParameter>(); try { // prepare the sql statement parameters AddPartnerSelectionParametersToSqlQuery(AParameters, Defines, SqlParameterList); } catch (Exception e) { TLogging.Log("problem while preparing sql statement for birthday report: " + e.ToString()); return(null); } string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.GetFamilyKeyOfPerson.sql", Defines); Boolean NewTransaction; TDataBase db = DBAccess.Connect("GetFamilyKeys"); TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction); try { // now run the database query DataTable resultTable = db.SelectDT(SqlStmt, "result", Transaction, SqlParameterList.ToArray()); // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button if (AParameters.Get("CancelReportCalculation").ToBool() == true) { return(null); } return(resultTable); } catch (Exception e) { TLogging.Log(e.ToString()); return(null); } finally { if (NewTransaction) { Transaction.Rollback(); } } }
private static void CheckModule(TDataBase ADataBaseObj, string AModule) { // get all sql files starting with module string[] sqlfiles = Directory.GetFiles(Path.GetFullPath(TAppSettingsManager.GetValue("SqlFiles.Path", ".")), AModule + "*.sql"); DataTable errors = new DataTable(AModule + "Errors"); foreach (string sqlfile in sqlfiles) { string sql = TDataBase.ReadSqlFile(Path.GetFileName(sqlfile)); // extend the sql to load the s_date_created_d, s_created_by_c, s_date_modified_d, s_modified_by_c // only for the first table in the FROM clause string firstTableAlias = sql.Substring(sql.ToUpper().IndexOf("FROM ") + "FROM ".Length); firstTableAlias = firstTableAlias.Substring(0, firstTableAlias.ToUpper().IndexOf("WHERE")); int indexOfAs = firstTableAlias.ToUpper().IndexOf(" AS "); if (indexOfAs > -1) { firstTableAlias = firstTableAlias.Substring(indexOfAs + " AS ".Length).Trim(); if (firstTableAlias.Contains(",")) { firstTableAlias = firstTableAlias.Substring(0, firstTableAlias.IndexOf(",")).Trim(); } } sql = sql.Replace("FROM ", ", " + firstTableAlias + ".s_date_created_d AS DateCreated, " + firstTableAlias + ".s_created_by_c AS CreatedBy, " + firstTableAlias + ".s_date_modified_d AS DateModified, " + firstTableAlias + ".s_modified_by_c AS ModifiedBy FROM "); errors.Merge(ADataBaseObj.SelectDT(sql, "temp", null)); } if (errors.Rows.Count > 0) { SendEmailToAdmin(errors); SendEmailsPerUser(ADataBaseObj, errors); } }
/// Upgrade to version 2016-12 public static bool UpgradeDatabase201610_201612() { // there are various changes to the database structure TDBTransaction SubmitChangesTransaction = null; TSubmitChangesResult SubmissionResult = TSubmitChangesResult.scrError; DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref SubmitChangesTransaction, ref SubmissionResult, delegate { string[] SqlStmts = TDataBase.ReadSqlFile("Upgrade201610_201612.sql").Split(new char[] { ';' }); foreach (string stmt in SqlStmts) { DBAccess.GDBAccessObj.ExecuteNonQuery(stmt, SubmitChangesTransaction); } SubmissionResult = TSubmitChangesResult.scrOK; }); return(true); }
public static bool TypeAheadAccountCode(Int32 ALedgerNumber, string ASearch, bool APostingOnly, bool AExcludePosting, bool AActiveOnly, bool ABankAccountOnly, Int32 ALimit, out DataTable AResult) { DataTable result = new DataTable(); TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TypeAheadAccountCode"); db.ReadTransaction( ref Transaction, delegate { string SqlStmt = TDataBase.ReadSqlFile("Finance.TypeAheadAccountCode.sql"); OdbcParameter[] parameters = new OdbcParameter[5]; parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int); parameters[0].Value = ALedgerNumber; parameters[1] = new OdbcParameter("AccountCode", OdbcType.VarChar); parameters[1].Value = "%" + ASearch + "%"; parameters[2] = new OdbcParameter("ShortDesc", OdbcType.VarChar); parameters[2].Value = "%" + ASearch + "%"; parameters[3] = new OdbcParameter("LongDesc", OdbcType.VarChar); parameters[3].Value = "%" + ASearch + "%"; parameters[4] = new OdbcParameter("PostingOnly", OdbcType.TinyInt); parameters[4].Value = APostingOnly; SqlStmt += " LIMIT " + ALimit.ToString(); result = db.SelectDT(SqlStmt, "Search", Transaction, parameters); }); db.CloseDBConnection(); AResult = result; return(result.Rows.Count > 0); }
/// <summary> /// calculate an extract from a report: all partners in selected relationships with selected partner /// </summary> /// <param name="AParameters"></param> /// <param name="AResults"></param> /// <returns></returns> public static bool CalculateExtract(TParameterList AParameters, TResultList AResults) { string SqlStmt = ""; if (AParameters.Get("param_selection").ToString() == "an extract") { SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractFromExtractByPartnerRelationship.sql"); } else if (AParameters.Get("param_selection").ToString() == "one partner") { SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractByPartnerRelationship.sql"); } else { throw new ArgumentException("Must supply an extract or partner key."); } // create a new object of this class and control extract calculation from base class QueryPartnerByRelationship ExtractQuery = new QueryPartnerByRelationship(); return(ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults)); }
public static bool TypeAheadAccountCode(Int32 ALedgerNumber, string ASearch, bool APostingOnly, bool AExcludePosting, bool AActiveOnly, bool ABankAccountOnly, Int32 ALimit, out DataTable AResult) { TDBTransaction Transaction = null; DataTable result = new DataTable(); DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { string SqlStmt = TDataBase.ReadSqlFile("Finance.TypeAheadAccountCode.sql"); OdbcParameter[] parameters = new OdbcParameter[5]; parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int); parameters[0].Value = ALedgerNumber; parameters[1] = new OdbcParameter("AccountCode", OdbcType.VarChar); parameters[1].Value = "%" + ASearch + "%"; parameters[2] = new OdbcParameter("ShortDesc", OdbcType.VarChar); parameters[2].Value = "%" + ASearch + "%"; parameters[3] = new OdbcParameter("LongDesc", OdbcType.VarChar); parameters[3].Value = "%" + ASearch + "%"; parameters[4] = new OdbcParameter("PostingOnly", OdbcType.TinyInt); parameters[4].Value = APostingOnly; SqlStmt += " LIMIT " + ALimit.ToString(); result = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "Search", Transaction, parameters); }); AResult = result; return(result.Rows.Count > 0); }
/// Upgrade to version 2020-04 public static bool UpgradeDatabase202003_202004(TDataBase ADataBase) { // add new types for sponsorship TDBTransaction SubmitChangesTransaction = new TDBTransaction(); bool SubmitOK = false; ADataBase.WriteTransaction(ref SubmitChangesTransaction, ref SubmitOK, delegate { string[] SqlStmts = TDataBase.ReadSqlFile("Upgrade202003_202004.sql").Split(new char[] { ';' }); foreach (string stmt in SqlStmts) { if (stmt.Trim().Length > 0) { ADataBase.ExecuteNonQuery(stmt, SubmitChangesTransaction); } } SubmitOK = true; }); return(true); }
public static bool GenerateHOSAFiles(int ALedgerNumber, int APeriodNumber, int AIchNumber, string ACostCentre, String ACurrencySelect, string AFileName, out TVerificationResultCollection AVerificationResult ) { bool Successful = false; GLBatchTDS MainDS = new GLBatchTDS(); TVerificationResultCollection VerificationResult = new TVerificationResultCollection(); AVerificationResult = VerificationResult; TDBTransaction DBTransaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref DBTransaction, delegate { //Load tables needed: AccountingPeriod, Ledger, Account, Cost Centre, Transaction, Gift Batch, ICHStewardship ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, DBTransaction); /* Retrieve info on the ledger. */ ALedgerRow LedgerRow = (ALedgerRow)MainDS.ALedger.Rows[0]; String Currency = (ACurrencySelect == MFinanceConstants.CURRENCY_BASE) ? LedgerRow.BaseCurrency : LedgerRow.IntlCurrency; /* String StoreNumericFormat = "#" + CultureInfo.CurrentCulture.NumberFormat.NumberGroupSeparator + "##0"; * * if (CultureInfo.CurrentCulture.NumberFormat.NumberDecimalDigits > 0) * { * string DecPls = new String('0', CultureInfo.CurrentCulture.NumberFormat.NumberDecimalDigits); * StoreNumericFormat += CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator + DecPls; * } */ AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, DBTransaction); AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0]; String MonthName = AccountingPeriodRow.AccountingPeriodDesc; //Create table definitions DataTable TableForExport = new DataTable(); TableForExport.Columns.Add("CostCentre", typeof(string)); TableForExport.Columns.Add("Account", typeof(string)); TableForExport.Columns.Add("LedgerMonth", typeof(string)); TableForExport.Columns.Add("ICHPeriod", typeof(string)); TableForExport.Columns.Add("Date", typeof(DateTime)); TableForExport.Columns.Add("IndividualDebitTotal", typeof(decimal)); TableForExport.Columns.Add("IndividualCreditTotal", typeof(decimal)); string TableForExportHeader = "/** Header **" + "," + APeriodNumber.ToString() + "," + TLedgerInfo.GetStandardCostCentre(ALedgerNumber) + "," + ACostCentre + "," + DateTime.Today.ToShortDateString() + "," + Currency; //See gi3200.p ln: 170 //Select any gift transactions to export string strSql = TDataBase.ReadSqlFile("ICH.HOSAExportGifts.sql"); OdbcParameter parameter; List <OdbcParameter> parameters = new List <OdbcParameter>(); parameter = new OdbcParameter("LedgerNumber", OdbcType.Int); parameter.Value = ALedgerNumber; parameters.Add(parameter); parameter = new OdbcParameter("Year", OdbcType.Int); parameter.Value = LedgerRow.CurrentFinancialYear; parameters.Add(parameter); parameter = new OdbcParameter("CostCentre", OdbcType.VarChar); parameter.Value = ACostCentre; parameters.Add(parameter); DataTable TmpTable = DBAccess.GDBAccessObj.SelectDT(strSql, "table", DBTransaction, parameters.ToArray()); foreach (DataRow untypedTransRow in TmpTable.Rows) { string gLMAcctCode = untypedTransRow[3].ToString(); string gLMCostCCode = untypedTransRow[4].ToString(); string gLMAcctType = untypedTransRow[5].ToString(); if (gLMAcctType == MFinanceConstants.ACCOUNT_TYPE_INCOME) //a_account.a_account_type_c { DateTime PeriodStartDate = AccountingPeriodRow.PeriodStartDate; DateTime PeriodEndDate = AccountingPeriodRow.PeriodEndDate; /*RUN Export_gifts(INPUT pv_ledger_number_i...*/ //gi3200-1.i ExportGifts(ALedgerNumber, ACostCentre, gLMAcctCode, MonthName, APeriodNumber, PeriodStartDate, PeriodEndDate, ACurrencySelect, AIchNumber, TableForExport, VerificationResult); } /* Then see if there are any GL transactions to export */ //gi3200.i ln:33 /* * This scheme with ODBC parameters consistently causes an "input string is the wrong type" eror: * * strSql = TDataBase.ReadSqlFile("ICH.HOSAExportGLTrans.sql"); * OdbcParameter[] SqlParams = new OdbcParameter[] { * new OdbcParameter("LedgerNumber", (Int32)ALedgerNumber), * new OdbcParameter("Account", (String)gLMAcctCode), * new OdbcParameter("CostCentre", (String)gLMCostCCode), * new OdbcParameter("Narrative", (String)MFinanceConstants.NARRATIVE_YEAR_END_REALLOCATION), * new OdbcParameter("ICHNumber", (Int32)AIchNumber), * new OdbcParameter("ICHNumber2", (Int32)AIchNumber), * new OdbcParameter("PeriodNumber", (Int32)APeriodNumber) * }; * DataTable TmpTransTable = DBAccess.GDBAccessObj.SelectDT(strSql, "Transactions", DBTransaction, SqlParams); */ strSql = "SELECT Trans.a_ledger_number_i, Trans.a_batch_number_i, Trans.a_journal_number_i, Trans.a_transaction_number_i, " + "Trans.a_account_code_c, Trans.a_cost_centre_code_c, Trans.a_transaction_date_d, Trans.a_transaction_amount_n, " + "Trans.a_amount_in_base_currency_n, Trans.a_amount_in_intl_currency_n, Trans.a_ich_number_i, Trans.a_system_generated_l, " + "Trans.a_narrative_c, Trans.a_debit_credit_indicator_l FROM public.a_transaction AS Trans, public.a_journal AS Journal " + "WHERE Trans.a_ledger_number_i = Journal.a_ledger_number_i AND Trans.a_batch_number_i = Journal.a_batch_number_i " + "AND Trans.a_journal_number_i = Journal.a_journal_number_i " + String.Format( "AND Trans.a_ledger_number_i = {0} AND Trans.a_account_code_c = '{1}' AND Trans.a_cost_centre_code_c = '{2}' " + "AND Trans.a_transaction_status_l = true AND NOT (Trans.a_narrative_c LIKE '{3}%' AND Trans.a_system_generated_l = true) " + "AND ((Trans.a_ich_number_i + {4}) = Trans.a_ich_number_i OR Trans.a_ich_number_i = {4}) " + "AND Journal.a_journal_period_i = {5};", ALedgerNumber, gLMAcctCode, gLMCostCCode, MFinanceConstants.NARRATIVE_YEAR_END_REALLOCATION, AIchNumber, APeriodNumber ); DataTable TmpTransTable = DBAccess.GDBAccessObj.SelectDT(strSql, "Transactions", DBTransaction); foreach (DataRow untypedTransactRow in TmpTransTable.Rows) { Decimal DebitTotal = 0; Decimal CreditTotal = 0; bool Debit = Convert.ToBoolean(untypedTransactRow[13]); //a_transaction.a_debit_credit_indicator_l bool SystemGenerated = Convert.ToBoolean(untypedTransactRow[11]); //a_transaction.a_system_generated_l //TODO: Calendar vs Financial Date Handling - Check if number of ledger periods needs to be used here and not 12 assumed string Narrative = untypedTransactRow[12].ToString(); //a_transaction.a_narrative_c DateTime TransactionDate = Convert.ToDateTime(untypedTransactRow[6]); //a_transaction.a_transaction_date_d if (ACurrencySelect == MFinanceConstants.CURRENCY_BASE) { decimal AmountInBaseCurrency = Convert.ToDecimal(untypedTransactRow[8]); //a_transaction.a_amount_in_base_currency_n /* find transaction amount and store as debit or credit */ if (Debit) { DebitTotal += AmountInBaseCurrency; } else { CreditTotal += AmountInBaseCurrency; } } else { decimal AmountInIntlCurrency = Convert.ToDecimal(untypedTransactRow[9]); //a_transaction.a_amount_in_intl_currency_n if (Debit) { DebitTotal += AmountInIntlCurrency; } else { CreditTotal += AmountInIntlCurrency; } } TLogging.LogAtLevel(4, "HOSA-Narrative: " + Narrative); //Check for specific narrative strings bool IsNarrativeGBGiftBatch = false; int LenNarrativeGBGiftBatch = MFinanceConstants.NARRATIVE_GB_GIFT_BATCH.Length; bool IsNarrativeGiftsReceivedGiftBatch = false; int LenNarrativeGiftsReceivedGiftBatch = MFinanceConstants.NARRATIVE_GIFTS_RECEIVED_GIFT_BATCH.Length; if (Narrative.Length >= LenNarrativeGiftsReceivedGiftBatch) { IsNarrativeGiftsReceivedGiftBatch = (Narrative.Substring(0, LenNarrativeGiftsReceivedGiftBatch) == MFinanceConstants.NARRATIVE_GIFTS_RECEIVED_GIFT_BATCH); } if (Narrative.Length >= LenNarrativeGBGiftBatch) { IsNarrativeGBGiftBatch = (Narrative.Substring(0, LenNarrativeGBGiftBatch) == MFinanceConstants.NARRATIVE_GB_GIFT_BATCH); } if ((gLMAcctType.ToUpper() != MFinanceConstants.ACCOUNT_TYPE_INCOME.ToUpper()) || !(SystemGenerated && (IsNarrativeGBGiftBatch || IsNarrativeGiftsReceivedGiftBatch))) { // Put transaction information DataRow DR = (DataRow)TableForExport.NewRow(); DR[0] = gLMCostCCode; DR[1] = ConvertAccount(gLMAcctCode); DR[2] = ALedgerNumber.ToString() + MonthName + ":" + Narrative; DR[3] = "ICH-" + APeriodNumber.ToString("00"); DR[4] = TransactionDate; DR[5] = DebitTotal; DR[6] = CreditTotal; TableForExport.Rows.Add(DR); } } } TableForExport.AcceptChanges(); TLogging.LogAtLevel(4, "HOSA-TableForExport: " + TableForExport.Rows.Count.ToString()); //DataTables to XML to CSV XmlDocument doc = TDataBase.DataTableToXml(TableForExport); TCsv2Xml.Xml2Csv(doc, AFileName); //Replace the default CSV header row with OM specific ReplaceHeaderInFile(AFileName, TableForExportHeader, ref VerificationResult); Successful = true; }); // Get NewOrExisting AutoReadTransaction return(Successful); } // Generate HOSA Files
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
/// <summary> /// return a table with gift details for the given date with donor partner keys and bank account numbers /// </summary> private static bool GetGiftsByDate(Int32 ALedgerNumber, BankImportTDS AMainDS, DateTime ADateEffective, string ABankAccountCode, out List <int> AGiftBatchNumbers) { TDataBase db = DBAccess.Connect("GetGiftsByDate"); TDBTransaction transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted); // first get all gifts, even those that have no bank account associated string stmt = TDataBase.ReadSqlFile("BankImport.GetDonationsByDate.sql"); OdbcParameter[] parameters = new OdbcParameter[3]; parameters[0] = new OdbcParameter("ALedgerNumber", OdbcType.Int); parameters[0].Value = ALedgerNumber; parameters[1] = new OdbcParameter("ADateEffective", OdbcType.Date); parameters[1].Value = ADateEffective; parameters[2] = new OdbcParameter("ABankAccountCode", OdbcType.VarChar); parameters[2].Value = ABankAccountCode; db.SelectDT(AMainDS.AGiftDetail, stmt, transaction, parameters, 0, 0); // calculate the totals of gifts AMainDS.AGift.Clear(); AGiftBatchNumbers = new List <int>(); foreach (BankImportTDSAGiftDetailRow giftdetail in AMainDS.AGiftDetail.Rows) { BankImportTDSAGiftRow giftRow = (BankImportTDSAGiftRow)AMainDS.AGift.Rows.Find(new object[] { giftdetail.LedgerNumber, giftdetail.BatchNumber, giftdetail.GiftTransactionNumber }); if (giftRow == null) { giftRow = AMainDS.AGift.NewRowTyped(true); giftRow.LedgerNumber = giftdetail.LedgerNumber; giftRow.BatchNumber = giftdetail.BatchNumber; giftRow.GiftTransactionNumber = giftdetail.GiftTransactionNumber; giftRow.TotalAmount = 0; giftRow.DonorKey = giftdetail.DonorKey; AMainDS.AGift.Rows.Add(giftRow); } giftRow.TotalAmount += giftdetail.GiftTransactionAmount; if (!AGiftBatchNumbers.Contains(giftRow.BatchNumber)) { AGiftBatchNumbers.Add(giftRow.BatchNumber); } } // get PartnerKey and banking details (most important BankAccountNumber) for all donations on the given date stmt = TDataBase.ReadSqlFile("BankImport.GetBankAccountByDate.sql"); parameters = new OdbcParameter[2]; parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int); parameters[0].Value = ALedgerNumber; parameters[1] = new OdbcParameter("ADateEffective", OdbcType.Date); parameters[1].Value = ADateEffective; // TODO ? parameters[2] = new OdbcParameter("ABankAccountCode", OdbcType.VarChar); //parameters[2].Value = ABankAccountCode; // There can be several donors with the same banking details AMainDS.PBankingDetails.Constraints.Clear(); db.Select(AMainDS, stmt, AMainDS.PBankingDetails.TableName, transaction, parameters); transaction.Rollback(); return(true); }
/// <summary> /// get all gifts for the current costcentre and account /// </summary> public static DataTable HosaCalculateGifts(TParameterList AParameters, TResultList AResults) { SortedList <string, string> Defines = new SortedList <string, string>(); List <OdbcParameter> SqlParameterList = new List <OdbcParameter>(); try { if (AParameters.Get("param_filter_cost_centres").ToString() == "PersonalCostcentres") { Defines.Add("PERSONALHOSA", "true"); } SqlParameterList.Add(new OdbcParameter("ledgernumber", OdbcType.Decimal) { Value = AParameters.Get("param_ledger_number_i").ToDecimal() }); SqlParameterList.Add(new OdbcParameter("costcentre", OdbcType.VarChar) { Value = AParameters.Get("line_a_cost_centre_code_c") }); if (AParameters.Get("param_ich_number").ToInt32() == 0) { Defines.Add("NOT_LIMITED_TO_ICHNUMBER", "true"); } else { SqlParameterList.Add(new OdbcParameter("ichnumber", OdbcType.Int) { Value = AParameters.Get("param_ich_number").ToInt32() }); } SqlParameterList.Add(new OdbcParameter("batchstatus", OdbcType.VarChar) { Value = MFinanceConstants.BATCH_POSTED }); if (AParameters.Get("param_period").ToBool() == true) { Defines.Add("BYPERIOD", "true"); SqlParameterList.Add(new OdbcParameter("batchyear", OdbcType.Int) { Value = AParameters.Get("param_year_i").ToInt32() }); SqlParameterList.Add(new OdbcParameter("batchperiod_start", OdbcType.Int) { Value = AParameters.Get("param_start_period_i").ToInt32() }); SqlParameterList.Add(new OdbcParameter("batchperiod_end", OdbcType.Int) { Value = AParameters.Get("param_end_period_i").ToInt32() }); } else { SqlParameterList.Add(new OdbcParameter("param_start_date", OdbcType.Int) { Value = AParameters.Get("param_start_date").ToInt32() }); SqlParameterList.Add(new OdbcParameter("param_end_date", OdbcType.Int) { Value = AParameters.Get("param_end_date").ToInt32() }); } SqlParameterList.Add(new OdbcParameter("accountcode", OdbcType.VarChar) { Value = AParameters.Get("line_a_account_code_c") }); } catch (Exception e) { TLogging.Log("problem while preparing sql statement for HOSA report: " + e.ToString()); return(null); } string SqlStmt = TDataBase.ReadSqlFile("ICH.HOSAReportGiftSummary.sql", Defines); Boolean NewTransaction; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction); try { // now run the database query DataTable resultTable = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "result", Transaction, SqlParameterList.ToArray()); // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button if (AParameters.Get("CancelReportCalculation").ToBool() == true) { return(null); } resultTable.Columns.Add("a_transaction_amount_n", typeof(Decimal)); resultTable.Columns.Add("a_amount_in_base_currency_n", typeof(Decimal)); resultTable.Columns.Add("a_amount_in_intl_currency_n", typeof(Decimal)); resultTable.Columns.Add("a_reference_c", typeof(string)); resultTable.Columns.Add("a_narrative_c", typeof(string)); Boolean InternationalCurrency = AParameters.Get("param_currency").ToString() == "International"; Double ExchangeRate = 1.00; // TODO Get exchange rate! foreach (DataRow r in resultTable.Rows) { r["a_transaction_amount_n"] = Convert.ToDecimal(r["GiftTransactionAmount"]); r["a_amount_in_base_currency_n"] = Convert.ToDecimal(r["GiftBaseAmount"]); if (InternationalCurrency) { r["a_amount_in_intl_currency_n"] = (Decimal)(Convert.ToDouble(r["GiftBaseAmount"]) * ExchangeRate); } r["a_reference_c"] = StringHelper.PartnerKeyToStr(Convert.ToInt64(r["RecipientKey"])); r["a_narrative_c"] = r["RecipientShortname"].ToString(); } return(resultTable); } catch (Exception e) { TLogging.Log(e.ToString()); return(null); } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
/// <summary> /// This method needs to be implemented by extracts that can't follow the default processing with just one query. /// </summary> /// <param name="AParameters"></param> /// <param name="ATransaction"></param> /// <param name="AExtractId"></param> protected override bool RunSpecialTreatment(TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId) { Boolean ReturnValue = false; Int32 ExtractId = -1; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref Transaction, delegate { DataTable giftdetails; string SqlStmt = TDataBase.ReadSqlFile("Gift.Queries.ExtractDonorByAmount.sql"); List <OdbcParameter> SqlParameterList = new List <OdbcParameter>(); bool AddressFilterAdded; DataTable partnerkeys = new DataTable(); // call to derived class to retrieve parameters specific for extract RetrieveParameters(AParameters, ref SqlStmt, ref SqlParameterList); // add address filter information to sql statement and parameter list AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList); // now run the database query TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar); giftdetails = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", Transaction, SqlParameterList.ToArray()); // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button // TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well??? if (AParameters.Get("CancelReportCalculation").ToBool() == true) { return; } TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar); // With the result of the original query process the data and identify the partner keys for // the extract. partnerkeys.Columns.Add("0", typeof(Int64)); partnerkeys.Columns.Add("1", typeof(string)); partnerkeys.Columns.Add("p_site_key_n", typeof(Int64)); partnerkeys.Columns.Add("p_location_key_i", typeof(Int32)); ProcessGiftDetailRecords(giftdetails, AddressFilterAdded, AParameters, ref partnerkeys); // filter data by postcode (if applicable) ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, Transaction); // create an extract with the given name in the parameters ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys( AParameters.Get("param_extract_name").ToString(), AParameters.Get("param_extract_description").ToString(), out ExtractId, partnerkeys, 0, AddressFilterAdded); }); AExtractId = ExtractId; return(ReturnValue); } // Run Special Treatment
/// <summary> /// get all partners that we want to display on the current birthday report /// </summary> public static DataTable CalculateBirthdays(TParameterList AParameters, TResultList AResults) { SortedList <string, string> Defines = new SortedList <string, string>(); List <OdbcParameter> SqlParameterList = new List <OdbcParameter>(); try { // prepare the sql statement parameters if (AParameters.Exists("FamilyKey")) { SqlParameterList.Add(new OdbcParameter("FamilyKey", OdbcType.Decimal) { Value = AParameters.Get("FamilyKey").ToDecimal() }); Defines.Add("BYFAMILYKEY", string.Empty); } else { AddPartnerSelectionParametersToSqlQuery(AParameters, Defines, SqlParameterList); } if (AParameters.Get("param_chkSelectTypes").ToBool() == true) { string[] types = AParameters.Get("param_typecode").ToString().Split(new char[] { ',' }); string FilterForTypes = string.Empty; foreach (string type in types) { if (FilterForTypes.Length > 0) { FilterForTypes += " OR "; } FilterForTypes += "pptype.p_type_code_c = ?"; SqlParameterList.Add(new OdbcParameter("typecode" + FilterForTypes.Length, OdbcType.VarChar) { Value = type }); } Defines.Add("SELECTTYPES", "(" + FilterForTypes + ")"); } if (AParameters.Get("param_chkUseDate").ToBool() == true) { DateTime FromDate = AParameters.Get("param_dtpFromDate").ToDate(); DateTime ToDate = AParameters.Get("param_dtpToDate").ToDate(); if (FromDate.DayOfYear < ToDate.DayOfYear) { Defines.Add("WITHDATERANGE", string.Empty); SqlParameterList.Add(new OdbcParameter("startdate", OdbcType.Date) { Value = FromDate }); SqlParameterList.Add(new OdbcParameter("enddate", OdbcType.Date) { Value = ToDate }); } else { Defines.Add("WITHOUTDATERANGE", string.Empty); SqlParameterList.Add(new OdbcParameter("enddate", OdbcType.Date) { Value = ToDate }); SqlParameterList.Add(new OdbcParameter("startdate", OdbcType.Date) { Value = FromDate }); } } } catch (Exception e) { TLogging.Log("problem while preparing sql statement for birthday report: " + e.ToString()); return(null); } string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.Birthday.sql", Defines); Boolean NewTransaction; TDataBase db = DBAccess.Connect("CalculateBirthdays"); TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction); try { // now run the database query TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar); DataTable resultTable = db.SelectDT(SqlStmt, "result", Transaction, SqlParameterList.ToArray()); // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button if (AParameters.Get("CancelReportCalculation").ToBool() == true) { return(null); } // if end date is not set, use the end of this year DateTime AgeDay = DateTime.Now; if (AParameters.Get("param_chkUseDate").ToBool() == true) { AgeDay = AParameters.Get("param_dtpToDate").ToDate(); } else { AgeDay = new DateTime(AgeDay.Year, 12, 31); } // Calculate the age, in new column resultTable.Columns.Add(new DataColumn("age", typeof(Int32))); foreach (DataRow r in resultTable.Rows) { int age = 0; if (r["DOB"] != DBNull.Value) { DateTime BDay = Convert.ToDateTime(r["DOB"]); age = AgeDay.Year - BDay.Year; } r["Age"] = age; } // filter by anniversaries? if ((AParameters.Get("param_chkAnniversaries").ToBool() == true) && !AParameters.Get("param_txtAnniversaries").IsZeroOrNull()) { List <string> anniversaries = new List <string>(AParameters.Get("param_txtAnniversaries").ToString().Split(new char[] { ',' })); List <DataRow> RowsToDelete = new List <DataRow>(); foreach (DataRow r in resultTable.Rows) { if (!anniversaries.Contains(r["Age"].ToString())) { RowsToDelete.Add(r); } } foreach (DataRow r in RowsToDelete) { resultTable.Rows.Remove(r); } } return(resultTable); } catch (Exception e) { TLogging.Log(e.ToString()); return(null); } finally { if (NewTransaction) { Transaction.Rollback(); } } }
public static Int32 FieldChangeAdjustment(Int32 ALedgerNumber, Int64 ARecipientKey, DateTime AStartDate, DateTime AEndDate, Int64 AOldField, DateTime ADateCorrection, bool AWithReceipt) { TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted); GiftBatchTDS oldGiftDS = new GiftBatchTDS(); try { // find all gifts that need reversing. // criteria: // posted gift batches only // no adjusted/reversed gifts // date of gift batch in specified date range // recipient field is the old field string SqlStmt = TDataBase.ReadSqlFile("Gift.GetGiftsToReverse.sql"); List <OdbcParameter> parameters = new List <OdbcParameter>(); OdbcParameter param = new OdbcParameter("LedgerNumber", OdbcType.Int); param.Value = ALedgerNumber; parameters.Add(param); param = new OdbcParameter("StartDate", OdbcType.Date); param.Value = AStartDate; parameters.Add(param); param = new OdbcParameter("EndDate", OdbcType.Date); param.Value = AEndDate; parameters.Add(param); param = new OdbcParameter("RecipientKey", OdbcType.BigInt); param.Value = ARecipientKey; parameters.Add(param); param = new OdbcParameter("OldField", OdbcType.BigInt); param.Value = AOldField; parameters.Add(param); DBAccess.GDBAccessObj.Select(oldGiftDS, SqlStmt, oldGiftDS.AGiftDetail.TableName, Transaction, parameters.ToArray()); // load the gift and the gift batch records if they have not been loaded yet foreach (AGiftDetailRow giftdetail in oldGiftDS.AGiftDetail.Rows) { oldGiftDS.AGift.DefaultView.RowFilter = String.Format("{0} = {1} and {2} = {3}", AGiftTable.GetBatchNumberDBName(), giftdetail.BatchNumber, AGiftTable.GetGiftTransactionNumberDBName(), giftdetail.GiftTransactionNumber); if (oldGiftDS.AGift.DefaultView.Count == 0) { AGiftTable tempGiftTable = AGiftAccess.LoadByPrimaryKey(giftdetail.LedgerNumber, giftdetail.BatchNumber, giftdetail.GiftTransactionNumber, Transaction); oldGiftDS.AGift.Merge(tempGiftTable); } oldGiftDS.AGiftBatch.DefaultView.RowFilter = String.Format("{0} = {1}", AGiftTable.GetBatchNumberDBName(), giftdetail.BatchNumber); if (oldGiftDS.AGiftBatch.DefaultView.Count == 0) { AGiftBatchTable tempGiftBatchTable = AGiftBatchAccess.LoadByPrimaryKey(giftdetail.LedgerNumber, giftdetail.BatchNumber, Transaction); oldGiftDS.AGiftBatch.Merge(tempGiftBatchTable); } } DBAccess.GDBAccessObj.RollbackTransaction(); } catch (Exception) { DBAccess.GDBAccessObj.RollbackTransaction(); throw; } // we need to create a gift batch for each set of gifts with the same Currency, BankAccountCode, BankCostCentre, and Gift Type SortedList <string, GiftBatchTDS> NewGiftBatches = new SortedList <string, GiftBatchTDS>(); foreach (GiftBatchTDSAGiftDetailRow oldGiftDetail in oldGiftDS.AGiftDetail.Rows) { // get the gift batch row for this detail oldGiftDS.AGiftBatch.DefaultView.RowFilter = String.Format("{0} = {1}", AGiftTable.GetBatchNumberDBName(), oldGiftDetail.BatchNumber); AGiftBatchRow oldGiftBatch = (AGiftBatchRow)oldGiftDS.AGiftBatch.DefaultView[0].Row; GiftBatchTDS GiftDS = CreateNewGiftBatch(NewGiftBatches, oldGiftBatch, ADateCorrection); AGiftBatchRow giftbatchRow = GiftDS.AGiftBatch[0]; // get the gift row for this detail DataView v = oldGiftDS.AGift.DefaultView; v.RowFilter = String.Format("{0} = {1} and {2} = {3}", AGiftTable.GetBatchNumberDBName(), oldGiftDetail.BatchNumber, AGiftTable.GetGiftTransactionNumberDBName(), oldGiftDetail.GiftTransactionNumber); AGiftRow oldGift = (AGiftRow)v[0].Row; AGiftRow gift = GiftDS.AGift.NewRowTyped(); gift.LedgerNumber = giftbatchRow.LedgerNumber; gift.BatchNumber = giftbatchRow.BatchNumber; gift.GiftTransactionNumber = giftbatchRow.LastGiftNumber + 1; gift.DonorKey = oldGift.DonorKey; gift.DateEntered = ADateCorrection; giftbatchRow.LastGiftNumber++; GiftDS.AGift.Rows.Add(gift); if (!AWithReceipt) { gift.ReceiptLetterCode = "NO*RECET"; } // reverse the original gift GiftBatchTDSAGiftDetailRow detail = GiftDS.AGiftDetail.NewRowTyped(); DataUtilities.CopyAllColumnValues(oldGiftDetail, detail); detail.LedgerNumber = gift.LedgerNumber; detail.BatchNumber = gift.BatchNumber; detail.GiftTransactionNumber = gift.GiftTransactionNumber; detail.DetailNumber = gift.LastDetailNumber + 1; detail.GiftAmount = detail.GiftAmount * -1; detail.GiftAmountIntl = detail.GiftAmountIntl * -1; detail.GiftTransactionAmount = detail.GiftTransactionAmount * -1; gift.LastDetailNumber++; GiftDS.AGiftDetail.Rows.Add(detail); // create the detail for the corrected gift to the new field detail = GiftDS.AGiftDetail.NewRowTyped(); DataUtilities.CopyAllColumnValues(oldGiftDetail, detail); detail.LedgerNumber = gift.LedgerNumber; detail.BatchNumber = gift.BatchNumber; detail.GiftTransactionNumber = gift.GiftTransactionNumber; detail.DetailNumber = gift.LastDetailNumber + 1; detail.GiftCommentOne = String.Format(Catalog.GetString("posted on {0}"), oldGiftBatch.GlEffectiveDate.ToShortDateString()); gift.LastDetailNumber++; // TODO: calculate costcentre code from current commitment; this currently is done only at time of posting // detail.RecipientLedgerNumber = oldGiftDetail.RecipientLedgerNumber; // detail.CostCentreCode = oldGiftDetail.CostCentreCode; GiftDS.AGiftDetail.Rows.Add(detail); // TODO: how to make sure that the gl transaction is marked as System generated? avoid display on HOSA? // mark original gift detail as modified oldGiftDetail.ModifiedDetail = true; } TVerificationResultCollection VerificationResult; TSubmitChangesResult result = TSubmitChangesResult.scrOK; for (Int32 batchCounter = 0; batchCounter < NewGiftBatches.Count; batchCounter++) { if (result == TSubmitChangesResult.scrOK) { GiftBatchTDS GiftDS = NewGiftBatches.Values[batchCounter]; result = TGiftTransactionWebConnector.SaveGiftBatchTDS(ref GiftDS, out VerificationResult); } } if (result == TSubmitChangesResult.scrOK) { result = TGiftTransactionWebConnector.SaveGiftBatchTDS(ref oldGiftDS, out VerificationResult); if ((result == TSubmitChangesResult.scrOK) && (NewGiftBatches.Count > 0)) { return(NewGiftBatches.Values[0].AGiftBatch[0].BatchNumber); } } return(-1); }
/// <summary> /// get all partners and their commitment details /// </summary> public static DataTable GetLengthOfCommitment(TParameterList AParameters, TResultList AResults) { SortedList <string, string> Defines = new SortedList <string, string>(); List <OdbcParameter> SqlParameterList = new List <OdbcParameter>(); try { SqlParameterList.Add(new OdbcParameter("staffdate", OdbcType.Date) { Value = AParameters.Get("param_dtpCurrentStaff").ToDate() }); SqlParameterList.Add(new OdbcParameter("staffdate2", OdbcType.Date) { Value = AParameters.Get("param_dtpCurrentStaff").ToDate() }); } catch (Exception e) { TLogging.Log("problem while preparing sql statement for length of commitment report: " + e.ToString()); return(null); } string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.AllCommitments.sql", Defines); Boolean NewTransaction; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction); try { // now run the database query TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar); LengthOfCommitmentReportTDSPmStaffDataTable CommitmentTable = new LengthOfCommitmentReportTDSPmStaffDataTable(); DBAccess.GDBAccessObj.SelectDT(CommitmentTable, SqlStmt, Transaction, SqlParameterList.ToArray(), 0, 0); // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button if (AParameters.Get("CancelReportCalculation").ToBool() == true) { return(null); } List <Int32> SpecialAnniversaries = new List <int>(); if (AParameters.Get("param_chkAnniversaries").ToBool() == true) { string[] Anniversaries = AParameters.Get("param_txtAnniversaries").ToString().Split(new char[] { ',', ';' }); foreach (string s in Anniversaries) { SpecialAnniversaries.Add(Convert.ToInt32(s.Trim())); } } return(CalculateLengthOfCommitment( CommitmentTable, AParameters.Get("param_dtpFromDate").ToDate(), AParameters.Get("param_dtpToDate").ToDate(), SpecialAnniversaries)); } catch (Exception e) { TLogging.Log(e.ToString()); return(null); } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
/// <summary> /// Run extract in case the user wants to analyze receiving fields. /// </summary> /// <param name="AParameters"></param> /// <param name="ATransaction"></param> /// <param name="AExtractId"></param> private bool ProcessReceivingFields(TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId) { /*Approach: * In case of a specified "Period" only find persons * that have a commitment record. * In case of "Now" or "Ever" also find partners * (persons & families) without such a commitment * record, that match the specified criteria. * In case of "Now" only find partners with a "Worker" type. * (This check is dropped in case of "Ever") * When interested in families only, also find families * for which a member matches the specified criteria.*/ bool ReturnValue = false; // for receiving fields first look at commitments ReturnValue = ProcessCommitments(true, AParameters, ATransaction, out AExtractId); if (ReturnValue == false) { return(ReturnValue); } // if only commitments need to be considered then no need to continue here if (AParameters.Get("param_commitments_and_worker_field").IsZeroOrNull() || (AParameters.Get("param_commitments_and_worker_field").ToString() == "CommitmentsOnly")) { return(ReturnValue); } bool AddressFilterAdded; string SqlStmtWorkerFieldOriginal = TDataBase.ReadSqlFile("Partner.Queries.ExtractPartnerByField.WorkerField.sql"); string SqlStmt; List <OdbcParameter> SqlParameterList = new List <OdbcParameter>(); string TypeCodeParameter; // If date range was specified then only look at staff data. Otherwise look for persons and families seperately. if (AParameters.Get("param_field_dates").ToString() == "DateRange") { return(ReturnValue); } // prepare parameter field for partner type code. if (AParameters.Get("param_field_dates").ToString() == "DateEver") { TypeCodeParameter = ""; } else { TypeCodeParameter = "OMER%"; } // prepare list of selected fields List <String> param_fields = new List <String>(); foreach (TVariant choice in AParameters.Get("param_fields").ToComposite()) { param_fields.Add(choice.ToString()); } if (param_fields.Count == 0) { throw new NoNullAllowedException("At least one option must be checked."); } // now add parameters to sql parameter list SqlParameterList.Add(TDbListParameterValue.OdbcListParameterValue("fields", OdbcType.BigInt, param_fields)); SqlParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit) { Value = AParameters.Get("param_active").ToBool() }); SqlParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit) { Value = AParameters.Get("param_exclude_no_solicitations").ToBool() }); // ---------------------------------------------------------------------------------------- // now start retrieving either families or persons whose worker field is set to given value // ---------------------------------------------------------------------------------------- SqlStmt = SqlStmtWorkerFieldOriginal; SqlStmt = SqlStmt.Replace("##person_or_family_table##", ", pub_p_person"); SqlStmt = SqlStmt.Replace("##person_or_family_table_name##", "pub_p_person"); SqlStmt = SqlStmt.Replace("##exclude_familiy_members_existing_in_extract##", ""); SqlStmt = SqlStmt.Replace("##worker_type##", TypeCodeParameter); if (AParameters.Get("param_families_only").ToBool()) { /* In case that only family records are wanted a join via family key of a person is needed * to find families of persons. */ SqlStmt = SqlStmt.Replace("##join_for_person_or_family##", " AND pub_p_partner.p_partner_key_n = pub_p_person.p_family_key_n"); } else { // in this case there will be person records in the extract SqlStmt = SqlStmt.Replace("##join_for_person_or_family##", " AND pub_p_partner.p_partner_key_n = pub_p_person.p_partner_key_n"); } // add address filter information to sql statement and parameter list AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList); // now run the database query TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar); DataTable partnerkeys = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", ATransaction, SqlParameterList.ToArray()); // filter data by postcode (if applicable) ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, ATransaction); // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button // TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well??? if (AParameters.Get("CancelReportCalculation").ToBool() == true) { return(false); } TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar); // create an extract with the given name in the parameters TExtractsHandling.ExtendExtractFromListOfPartnerKeys( AExtractId, partnerkeys, 0, AddressFilterAdded, false); // ---------------------------------------------------------------------------------------- // Now start retrieving families whose worker field is set to given value and that are not // already contained in the created extract. // ---------------------------------------------------------------------------------------- SqlStmt = SqlStmtWorkerFieldOriginal; // need to rebuild parameter list as statement is also loaded again and filled SqlParameterList.Clear(); SqlParameterList.Add(TDbListParameterValue.OdbcListParameterValue("fields", OdbcType.BigInt, param_fields)); SqlParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit) { Value = AParameters.Get("param_active").ToBool() }); SqlParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit) { Value = AParameters.Get("param_exclude_no_solicitations").ToBool() }); SqlStmt = SqlStmt.Replace("##person_or_family_table##", ", pub_p_family"); SqlStmt = SqlStmt.Replace("##person_or_family_table_name##", "pub_p_family"); SqlStmt = SqlStmt.Replace("##worker_type##", TypeCodeParameter); SqlStmt = SqlStmt.Replace("##join_for_person_or_family##", " AND pub_p_partner.p_partner_key_n = pub_p_family.p_partner_key_n"); SqlStmt = SqlStmt.Replace("##exclude_familiy_members_existing_in_extract##", "AND NOT EXISTS (SELECT pub_p_family.p_partner_key_n " + " FROM pub_p_family, pub_p_person, pub_m_extract " + " WHERE pub_p_person.p_family_key_n = pub_p_family.p_partner_key_n " + " AND pub_m_extract.m_extract_id_i = " + AExtractId.ToString() + " AND pub_m_extract.p_partner_key_n = pub_p_person.p_partner_key_n)"); // add address filter information to sql statement and parameter list AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList); // now run the database query TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar); partnerkeys.Clear(); partnerkeys = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", ATransaction, SqlParameterList.ToArray()); // filter data by postcode (if applicable) ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, ATransaction); // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button // TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well??? if (AParameters.Get("CancelReportCalculation").ToBool() == true) { return(false); } TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar); // create an extract with the given name in the parameters TExtractsHandling.ExtendExtractFromListOfPartnerKeys( AExtractId, partnerkeys, 0, AddressFilterAdded, false); ReturnValue = true; return(ReturnValue); }
public static string CreateAnnualGiftReceipts(Int32 ALedgerNumber, DateTime AStartDate, DateTime AEndDate, string AHTMLTemplate, bool ADeceasedFirst = false, string AExtract = null, Int64 ADonorKey = 0) { TLanguageCulture.LoadLanguageAndCulture(); // get BaseCurrency System.Type typeofTable = null; TCacheable CachePopulator = new TCacheable(); ALedgerTable LedgerTable = (ALedgerTable)CachePopulator.GetCacheableTable(TCacheableFinanceTablesEnum.LedgerDetails, "", false, ALedgerNumber, out typeofTable); string BaseCurrency = LedgerTable[0].BaseCurrency; TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted); try { // get the local country code string LocalCountryCode = TAddressTools.GetCountryCodeFromSiteLedger(Transaction); DataTable donorkeys = new DataTable(); string SqlStmt = ""; if (ADonorKey != 0) { TPartnerClass Class; string ShortName; TPartnerServerLookups.GetPartnerShortName(ADonorKey, out ShortName, out Class); donorkeys.Columns.Add(new DataColumn("DonorKey")); donorkeys.Columns.Add(new DataColumn("DonorName")); DataRow SingleRow = donorkeys.NewRow(); SingleRow[0] = ADonorKey; SingleRow[1] = ShortName; donorkeys.Rows.Add(SingleRow); } else { SortedList <string, string> Defines = new SortedList <string, string>(); if (!string.IsNullOrEmpty(AExtract)) { Defines.Add("BYEXTRACT", string.Empty); } // first get all donors in the given date range SqlStmt = TDataBase.ReadSqlFile("Gift.ReceiptPrinting.GetDonors.sql", Defines); OdbcParameter[] parameters = new OdbcParameter[4]; parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int); parameters[0].Value = ALedgerNumber; parameters[1] = new OdbcParameter("StartDate", OdbcType.Date); parameters[1].Value = AStartDate; parameters[2] = new OdbcParameter("EndDate", OdbcType.Date); parameters[2].Value = AEndDate; parameters[3] = new OdbcParameter("Extract", OdbcType.VarChar); parameters[3].Value = AExtract; donorkeys = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "DonorKeys", Transaction, parameters); // put deceased partner's at the front (still sorted alphabetically) if (ADeceasedFirst) { // create a new datatable with same structure as donorkeys DataTable temp = donorkeys.Clone(); temp.Clear(); // add deceased donors to the temp table and delete from donorkeys for (int i = 0; i < donorkeys.Rows.Count; i++) { if (SharedTypes.StdPartnerStatusCodeStringToEnum(donorkeys.Rows[i][2].ToString()) == TStdPartnerStatusCode.spscDIED) { temp.Rows.Add((object[])donorkeys.Rows[i].ItemArray.Clone()); donorkeys.Rows[i].Delete(); } } // add remaining partners to temp table donorkeys.AcceptChanges(); temp.Merge(donorkeys); donorkeys = temp; } } string ResultDocument = ""; SqlStmt = TDataBase.ReadSqlFile("Gift.ReceiptPrinting.GetDonationsOfDonor.sql"); foreach (DataRow donorrow in donorkeys.Rows) { Int64 donorKey = Convert.ToInt64(donorrow[0]); string donorName = donorrow[1].ToString(); OdbcParameter[] parameters = new OdbcParameter[4]; parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int); parameters[0].Value = ALedgerNumber; parameters[1] = new OdbcParameter("StartDate", OdbcType.Date); parameters[1].Value = AStartDate; parameters[2] = new OdbcParameter("EndDate", OdbcType.Date); parameters[2].Value = AEndDate; parameters[3] = new OdbcParameter("DonorKey", OdbcType.BigInt); parameters[3].Value = donorKey; // TODO: should we print each gift detail, or just one row per gift? DataTable donations = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "Donations", Transaction, parameters); if (donations.Rows.Count > 0) { string letter = FormatLetter(donorKey, donorName, donations, BaseCurrency, AHTMLTemplate, LocalCountryCode, Transaction); if (TFormLettersTools.AttachNextPage(ref ResultDocument, letter)) { // TODO: store somewhere that the receipt has been printed? // TODO also store each receipt with the donor in document management, and in contact management? } } } TFormLettersTools.CloseDocument(ref ResultDocument); return(ResultDocument); } finally { DBAccess.GDBAccessObj.RollbackTransaction(); } }
/// <summary> /// Add persons or families to extract that have commitments to specified receiving/sending fields /// </summary> /// <param name="AProcessReceivingFields"></param> /// <param name="AParameters"></param> /// <param name="ATransaction"></param> /// <param name="AExtractId"></param> private bool ProcessCommitments(bool AProcessReceivingFields, TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId) { bool ReturnValue = false; bool AddressFilterAdded; string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractPartnerByField.Commitment.sql"); List <OdbcParameter> SqlParameterList = new List <OdbcParameter>(); // need to set initial value here in case method needs to return before value is set AExtractId = -1; // prepare list of selected fields List <String> param_fields = new List <String>(); foreach (TVariant choice in AParameters.Get("param_fields").ToComposite()) { param_fields.Add(choice.ToString()); } if (param_fields.Count == 0) { throw new NoNullAllowedException("At least one option must be checked."); } // now add parameters to sql parameter list SqlParameterList.Add(TDbListParameterValue.OdbcListParameterValue("fields", OdbcType.BigInt, param_fields)); SqlParameterList.Add(new OdbcParameter("param_from_date_unset", OdbcType.Bit) { Value = AParameters.Get("param_from_date").IsZeroOrNull() }); SqlParameterList.Add(new OdbcParameter("param_from_date", OdbcType.Date) { Value = AParameters.Get("param_from_date").ToDate() }); SqlParameterList.Add(new OdbcParameter("param_until_date_unset", OdbcType.Bit) { Value = AParameters.Get("param_until_date").IsZeroOrNull() }); SqlParameterList.Add(new OdbcParameter("param_until_date", OdbcType.Date) { Value = AParameters.Get("param_until_date").ToDate() }); SqlParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit) { Value = AParameters.Get("param_active").ToBool() }); SqlParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit) { Value = AParameters.Get("param_exclude_no_solicitations").ToBool() }); if (AProcessReceivingFields) { // for receiving fields target field table field needs to be used SqlStmt = SqlStmt.Replace("##sending_or_receiving_field##", "pm_receiving_field_n"); } else { // for sending fields home office table field needs to be used SqlStmt = SqlStmt.Replace("##sending_or_receiving_field##", "pm_home_office_n"); } if (AParameters.Get("param_families_only").ToBool()) { /* In case that only family records are wanted a join via family key of a person is needed * to find families of persons. */ SqlStmt = SqlStmt.Replace("##person_table##", ", pub_p_person"); SqlStmt = SqlStmt.Replace("##join_for_person_or_family##", " AND pub_p_person.p_partner_key_n = pub_pm_staff_data.p_partner_key_n" + " AND pub_p_partner.p_partner_key_n = pub_p_person.p_family_key_n "); } else { // in this case there will be person records in the extract SqlStmt = SqlStmt.Replace("##person_table##", ""); SqlStmt = SqlStmt.Replace("##join_for_person_or_family##", " AND pub_p_partner.p_partner_key_n = pub_pm_staff_data.p_partner_key_n"); } // add address filter information to sql statement and parameter list AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList); // now run the database query TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar); DataTable partnerkeys = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", ATransaction, SqlParameterList.ToArray()); // filter data by postcode (if applicable) ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, ATransaction); // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button // TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well??? if (AParameters.Get("CancelReportCalculation").ToBool() == true) { return(false); } TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar); // create an extract with the given name in the parameters ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys( AParameters.Get("param_extract_name").ToString(), AParameters.Get("param_extract_description").ToString(), out AExtractId, partnerkeys, 0, AddressFilterAdded); return(ReturnValue); }
/// <summary> /// export all the Data of the batches matching the parameters to a String /// </summary> /// <param name="ARequestParams">Hashtable containing the given params </param> /// <param name="AExportString">Big parts of the export file as a simple String</param> /// <param name="AVerificationMessages">Additional messages to display in a messagebox</param> /// <returns>number of exported batches, -1 if cancelled, -2 if error</returns> public Int32 ExportAllGiftBatchData( Hashtable ARequestParams, out String AExportString, out TVerificationResultCollection AVerificationMessages) { //Return number of exported batches, -1 if cancelled, -2 if error int ReturnGiftBatchCount = 0; FStringWriter = new StringWriter(); FMainDS = new GiftBatchTDS(); FDelimiter = (String)ARequestParams["Delimiter"]; FLedgerNumber = (Int32)ARequestParams["ALedgerNumber"]; FDateFormatString = (String)ARequestParams["DateFormatString"]; bool Summary = (bool)ARequestParams["Summary"]; FUseBaseCurrency = (bool)ARequestParams["bUseBaseCurrency"]; FDateForSummary = (DateTime)ARequestParams["DateForSummary"]; String NumberFormat = (String)ARequestParams["NumberFormat"]; FCultureInfo = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE"); FTransactionsOnly = (bool)ARequestParams["TransactionsOnly"]; FExtraColumns = (bool)ARequestParams["ExtraColumns"]; try { DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref FTransaction, delegate { try { ALedgerAccess.LoadByPrimaryKey(FMainDS, FLedgerNumber, FTransaction); List <OdbcParameter> parameters = new List <OdbcParameter>(); SortedList <String, String> SQLCommandDefines = new SortedList <string, string>(); if ((bool)ARequestParams["IncludeUnposted"]) { SQLCommandDefines.Add("INCLUDEUNPOSTED", string.Empty); } OdbcParameter param = new OdbcParameter("LedgerNumber", OdbcType.Int); param.Value = FLedgerNumber; parameters.Add(param); Int64 recipientNumber = (Int64)ARequestParams["RecipientNumber"]; Int64 fieldNumber = (Int64)ARequestParams["FieldNumber"]; if (recipientNumber != 0) { SQLCommandDefines.Add("BYRECIPIENT", string.Empty); param = new OdbcParameter("RecipientNumber", OdbcType.Int); param.Value = recipientNumber; parameters.Add(param); } if (fieldNumber != 0) { SQLCommandDefines.Add("BYFIELD", string.Empty); param = new OdbcParameter("fieldNumber", OdbcType.Int); param.Value = fieldNumber; parameters.Add(param); } if (ARequestParams.ContainsKey("BatchNumberStart")) { SQLCommandDefines.Add("BYBATCHNUMBER", string.Empty); param = new OdbcParameter("BatchNumberStart", OdbcType.Int); param.Value = (Int32)ARequestParams["BatchNumberStart"]; parameters.Add(param); param = new OdbcParameter("BatchNumberEnd", OdbcType.Int); param.Value = (Int32)ARequestParams["BatchNumberEnd"]; parameters.Add(param); } else { SQLCommandDefines.Add("BYDATERANGE", string.Empty); param = new OdbcParameter("BatchDateFrom", OdbcType.DateTime); param.Value = (DateTime)ARequestParams["BatchDateFrom"]; parameters.Add(param); param = new OdbcParameter("BatchDateTo", OdbcType.DateTime); param.Value = (DateTime)ARequestParams["BatchDateTo"]; parameters.Add(param); } string sqlStatement = TDataBase.ReadSqlFile("Gift.GetGiftsToExport.sql", SQLCommandDefines); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving gift batch records"), 5); if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } DBAccess.GDBAccessObj.Select(FMainDS, "SELECT DISTINCT PUB_a_gift_batch.* " + sqlStatement + " ORDER BY " + AGiftBatchTable.GetBatchNumberDBName(), FMainDS.AGiftBatch.TableName, FTransaction, parameters.ToArray()); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving gift records"), 10); if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } DBAccess.GDBAccessObj.Select(FMainDS, "SELECT DISTINCT PUB_a_gift.* " + sqlStatement + " ORDER BY " + AGiftBatchTable.GetBatchNumberDBName() + ", " + AGiftTable.GetGiftTransactionNumberDBName(), FMainDS.AGift.TableName, FTransaction, parameters.ToArray()); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving gift detail records"), 15); if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } DBAccess.GDBAccessObj.Select(FMainDS, "SELECT DISTINCT PUB_a_gift_detail.* " + sqlStatement, FMainDS.AGiftDetail.TableName, FTransaction, parameters.ToArray()); } catch (ApplicationException ex) { throw ex; } catch (Exception ex) { TLogging.Log("Error in ExportAllGiftBatchData: " + ex.Message); throw ex; } }); TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Exporting Gift Batches"), 100); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Retrieving records"), 5); string BaseCurrency = FMainDS.ALedger[0].BaseCurrency; FCurrencyCode = BaseCurrency; // Depending on FUseBaseCurrency, this will be overwritten for each gift. SortedDictionary <String, AGiftSummaryRow> sdSummary = new SortedDictionary <String, AGiftSummaryRow>(); UInt32 counter = 0; // TProgressTracker Variables UInt32 GiftCounter = 0; AGiftSummaryRow giftSummary = null; FMainDS.AGiftDetail.DefaultView.Sort = AGiftDetailTable.GetLedgerNumberDBName() + "," + AGiftDetailTable.GetBatchNumberDBName() + "," + AGiftDetailTable.GetGiftTransactionNumberDBName(); foreach (AGiftBatchRow giftBatch in FMainDS.AGiftBatch.Rows) { if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } ReturnGiftBatchCount++; TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), string.Format(Catalog.GetString("Batch {0}"), giftBatch.BatchNumber), 20); GiftCounter = 0; if (!FTransactionsOnly & !Summary) { WriteGiftBatchLine(giftBatch); } foreach (AGiftRow gift in FMainDS.AGift.Rows) { if (gift.BatchNumber.Equals(giftBatch.BatchNumber) && gift.LedgerNumber.Equals(giftBatch.LedgerNumber)) { if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw new ApplicationException(Catalog.GetString("Export of Batches was cancelled by user")); } // Update progress tracker every 25 records if (++GiftCounter % 25 == 0) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), string.Format(Catalog.GetString("Batch {0} - Exporting gifts"), giftBatch.BatchNumber), (GiftCounter / 25 + 4) * 5 > 90 ? 90 : (GiftCounter / 25 + 4) * 5); } DataRowView[] selectedRowViews = FMainDS.AGiftDetail.DefaultView.FindRows( new object[] { gift.LedgerNumber, gift.BatchNumber, gift.GiftTransactionNumber }); foreach (DataRowView rv in selectedRowViews) { AGiftDetailRow giftDetail = (AGiftDetailRow)rv.Row; if (Summary) { FCurrencyCode = FUseBaseCurrency ? BaseCurrency : giftBatch.CurrencyCode; decimal mapExchangeRateToBase = FUseBaseCurrency ? 1 : giftBatch.ExchangeRateToBase; counter++; String DictionaryKey = FCurrencyCode + ";" + giftBatch.BankCostCentre + ";" + giftBatch.BankAccountCode + ";" + giftDetail.RecipientKey + ";" + giftDetail.MotivationGroupCode + ";" + giftDetail.MotivationDetailCode; if (sdSummary.TryGetValue(DictionaryKey, out giftSummary)) { giftSummary.GiftTransactionAmount += giftDetail.GiftTransactionAmount; giftSummary.GiftAmount += giftDetail.GiftAmount; } else { giftSummary = new AGiftSummaryRow(); /* * summary_data.a_transaction_currency_c = lv_stored_currency_c * summary_data.a_bank_cost_centre_c = a_gift_batch.a_bank_cost_centre_c * summary_data.a_bank_account_code_c = a_gift_batch.a_bank_account_code_c * summary_data.a_recipient_key_n = a_gift_detail.p_recipient_key_n * summary_data.a_motivation_group_code_c = a_gift_detail.a_motivation_group_code_c * summary_data.a_motivation_detail_code_c = a_gift_detail.a_motivation_detail_code_c * summary_data.a_exchange_rate_to_base_n = lv_exchange_rate_n * summary_data.a_gift_type_c = a_gift_batch.a_gift_type_c */ giftSummary.CurrencyCode = FCurrencyCode; giftSummary.BankCostCentre = giftBatch.BankCostCentre; giftSummary.BankAccountCode = giftBatch.BankAccountCode; giftSummary.RecipientKey = giftDetail.RecipientKey; giftSummary.MotivationGroupCode = giftDetail.MotivationGroupCode; giftSummary.MotivationDetailCode = giftDetail.MotivationDetailCode; giftSummary.GiftTransactionAmount = giftDetail.GiftTransactionAmount; giftSummary.GiftAmount = giftDetail.GiftAmount; sdSummary.Add(DictionaryKey, giftSummary); } //overwrite always because we want to have the last giftSummary.ExchangeRateToBase = mapExchangeRateToBase; } else // not summary { WriteGiftLine(gift, giftDetail); } } } } } if (Summary) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Export Summary"), 95); bool first = true; foreach (KeyValuePair <string, AGiftSummaryRow> kvp in sdSummary) { if (!FTransactionsOnly && first) { WriteGiftBatchSummaryLine(kvp.Value); first = false; } WriteGiftSummaryLine(kvp.Value); } } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Gift batch export successful"), 100); TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); } catch (ApplicationException) { //Show cancel condition ReturnGiftBatchCount = -1; TProgressTracker.CancelJob(DomainManager.GClientID.ToString()); } catch (Exception ex) { TLogging.Log(ex.ToString()); //Show error condition ReturnGiftBatchCount = -2; FMessages.Add(new TVerificationResult( "Exporting Gift Batches Terminated Unexpectedly", ex.Message, "An unexpected error occurred during the export of gift batches", string.Empty, TResultSeverity.Resv_Critical, Guid.Empty)); TProgressTracker.CancelJob(DomainManager.GClientID.ToString()); } if (ReturnGiftBatchCount > 0) { AExportString = FStringWriter.ToString(); } else { AExportString = string.Empty; } AVerificationMessages = FMessages; return(ReturnGiftBatchCount); }