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