public static bool CanDeleteCorporateExchangeRate(DateTime ADateEffectiveFrom, string AIntlCurrency, string ATransactionCurrency)
{
bool ReturnValue = true;
TDBTransaction ReadTransaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("CanDeleteCorporateExchangeRate");
db.ReadTransaction(
ref ReadTransaction,
delegate
{
// get accounting period for when the exchange rate is effective (if it exists)
string Query = "SELECT * FROM a_accounting_period" +
" WHERE a_accounting_period.a_period_end_date_d >= '" + DataUtilities.DateToSQLString(ADateEffectiveFrom) + "'" +
" AND a_accounting_period.a_period_start_date_d <= '" + DataUtilities.DateToSQLString(ADateEffectiveFrom) + "'";
AAccountingPeriodTable AccountingPeriodTable = new AAccountingPeriodTable();
db.SelectDT(AccountingPeriodTable, Query, ReadTransaction);
// no accounting period if effective in a year other that the current year
if ((AccountingPeriodTable == null) || (AccountingPeriodTable.Rows.Count == 0))
{
return;
}
AAccountingPeriodRow AccountingPeriodRow = AccountingPeriodTable[0];
// search for batches for the found accounting period
string Query2 = "SELECT CASE WHEN EXISTS (" +
"SELECT * FROM a_batch, a_journal, a_ledger" +
" WHERE a_batch.a_date_effective_d <= '" + DataUtilities.DateToSQLString(
AccountingPeriodRow.PeriodEndDate) + "'" +
" AND a_batch.a_date_effective_d >= '" + DataUtilities.DateToSQLString(
AccountingPeriodRow.PeriodStartDate) + "'" +
" AND a_journal.a_ledger_number_i = a_batch.a_ledger_number_i" +
" AND a_journal.a_batch_number_i = a_batch.a_batch_number_i" +
" AND a_ledger.a_ledger_number_i = a_batch.a_ledger_number_i" +
" AND ((a_journal.a_transaction_currency_c = '" + ATransactionCurrency + "'" +
" AND a_ledger.a_intl_currency_c = '" + AIntlCurrency + "')" +
" OR (a_journal.a_transaction_currency_c = '" + AIntlCurrency + "'" +
" AND a_ledger.a_intl_currency_c = '" + ATransactionCurrency + "'))" +
") THEN 'TRUE'" +
" ELSE 'FALSE' END";
DataTable DT = db.SelectDT(Query2, "temp", ReadTransaction);
// a batch has been found
if ((DT != null) && (DT.Rows.Count > 0) && (DT.Rows[0][0].ToString() == "TRUE"))
{
ReturnValue = false;
return;
}
});
return(ReturnValue);
}
public void SpeedTestLoadIntoTypedTable()
{
TDataBase db = DBAccess.Connect("test");
TDBTransaction ReadTransaction = new TDBTransaction();
DateTime before = DateTime.Now;
DateTime after = DateTime.Now;
GiftBatchTDS ds = new GiftBatchTDS();
db.ReadTransaction(
ref ReadTransaction,
delegate
{
string sql = "SELECT PUB_a_gift_detail.*, false AS AlreadyMatched, PUB_a_gift_batch.a_batch_status_c AS BatchStatus " +
"FROM PUB_a_gift_batch, PUB_a_gift_detail " +
"WHERE PUB_a_gift_detail.a_ledger_number_i = PUB_a_gift_batch.a_ledger_number_i AND PUB_a_gift_detail.a_batch_number_i = PUB_a_gift_batch.a_batch_number_i";
before = DateTime.Now;
DataTable untyped = db.SelectDT(sql, "test", ReadTransaction);
after = DateTime.Now;
TLogging.Log(String.Format("loading all {0} gift details into an untyped table took {1} milliseconds",
untyped.Rows.Count,
(after.Subtract(before)).TotalMilliseconds));
GiftBatchTDSAGiftDetailTable typed = new GiftBatchTDSAGiftDetailTable();
before = DateTime.Now;
db.SelectDT(typed, sql, ReadTransaction, new OdbcParameter[0], 0, 0);
after = DateTime.Now;
TLogging.Log(String.Format("loading all {0} gift details into a typed table took {1} milliseconds",
typed.Rows.Count,
(after.Subtract(before)).TotalMilliseconds));
AMotivationDetailAccess.LoadAll(ds, ReadTransaction);
before = DateTime.Now;
db.Select(ds, sql, ds.AGiftDetail.TableName, ReadTransaction);
after = DateTime.Now;
});
TLogging.Log(String.Format("loading all {0} gift details into a typed dataset took {1} milliseconds",
ds.AGiftDetail.Rows.Count,
(after.Subtract(before)).TotalMilliseconds));
before = DateTime.Now;
GiftBatchTDS ds2 = new GiftBatchTDS();
ds2.Merge(ds.AGiftDetail);
after = DateTime.Now;
TLogging.Log(String.Format("merging typed table into other dataset took {0} milliseconds",
(after.Subtract(before)).TotalMilliseconds));
}
/// <summary>
/// Load all GLMP rows for this Cost Centre in this period
/// </summary>
/// <returns></returns>
public Boolean LoadByCostCentreAccountPeriod(String ACostCentreCode, String AAccountCode, Int32 AYear, Int32 APeriod)
{
Boolean LoadedOk = false;
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadByCostCentreAccountPeriod", FDataBase);
FGLMpTable = new AGeneralLedgerMasterPeriodTable();
db.ReadTransaction(ref transaction,
delegate
{
db.SelectDT(
FGLMpTable,
"SELECT a_general_ledger_master_period.* FROM" +
" a_general_ledger_master_period, a_general_ledger_master" +
" WHERE" +
" a_general_ledger_master_period.a_glm_sequence_i=a_general_ledger_master.a_glm_sequence_i" +
" AND a_general_ledger_master.a_ledger_number_i = " + FLedgerNumber +
" AND a_general_ledger_master.a_year_i = " + AYear +
" AND a_general_ledger_master.a_account_code_c = '" + AAccountCode + "'" +
" AND a_general_ledger_master.a_cost_centre_code_c = '" + ACostCentreCode + "'" +
" AND a_general_ledger_master_period.a_period_number_i=" + APeriod,
transaction);
LoadedOk = (FGLMpTable.Rows.Count > 0);
}); // AutoReadTransaction
if (FDataBase == null)
{
db.CloseDBConnection();
}
FGLMpRow = (LoadedOk) ? FGLMpTable[0] : null;
return(LoadedOk);
}
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);
}
private static string GetUserIDFromEmail(string AUserEmail)
{
TDataBase db = DBAccess.Connect("GetUserIDFromEmail");
TDBTransaction Transaction = new TDBTransaction();
string UserID = AUserEmail;
db.ReadTransaction(ref Transaction,
delegate
{
string sql = "SELECT s_user_id_c FROM PUB_s_user WHERE UPPER(s_email_address_c) = ?";
OdbcParameter[] parameters = new OdbcParameter[1];
parameters[0] = new OdbcParameter("EmailAddress", OdbcType.VarChar);
parameters[0].Value = AUserEmail.ToUpper();
DataTable result = db.SelectDT(sql, "user", Transaction, parameters);
if (result.Rows.Count == 1)
{
UserID = result.Rows[0][0].ToString();
}
else
{
TLogging.Log("Login with E-Mail address failed for " + AUserEmail + ". " +
"We found " + result.Rows.Count.ToString() + " matching rows for this address.");
throw new Exception("multiple users are matching this email address");
}
});
db.CloseDBConnection();
return(UserID);
}
public static List <string> GetUniqueTypes(
Int64 APartnerKey
)
{
TDBTransaction T = new TDBTransaction();
TDataBase DB = DBAccess.Connect("Get get unique data types");
List <OdbcParameter> SQLParameter = new List <OdbcParameter>();
List <string> Types = new List <string>();
DB.ReadTransaction(ref T, delegate {
string sql = "SELECT DISTINCT `p_type_c` FROM `p_data_history` " +
"WHERE `p_partner_key_n` = ?";
SQLParameter.Add(new OdbcParameter("PartnerKey", OdbcType.VarChar)
{
Value = APartnerKey.ToString()
});
DataTable AllTypes = DB.SelectDT(sql, "UniqueTypes", T, SQLParameter.ToArray());
foreach (DataRow TypeRow in AllTypes.Rows)
{
Types.Add(TypeRow.Field <string>("p_type_c"));
}
});
return(Types);
}
/// <summary>
/// Direct access to the unposted gifts
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ADateEndOfPeriod"></param>
public GetUnpostedGiftInfo(int ALedgerNumber, DateTime ADateEndOfPeriod)
{
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[3];
ParametersArray[0] = new OdbcParameter("", OdbcType.Int);
ParametersArray[0].Value = ALedgerNumber;
ParametersArray[1] = new OdbcParameter("", OdbcType.Date);
ParametersArray[1].Value = ADateEndOfPeriod;
ParametersArray[2] = new OdbcParameter("", OdbcType.VarChar);
ParametersArray[2].Value = MFinanceConstants.BATCH_UNPOSTED;
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetUnpostedGiftInfo");
db.ReadTransaction(
ref transaction,
delegate
{
string strSQL = "SELECT * FROM PUB_" + AGiftBatchTable.GetTableDBName() +
" WHERE " + AGiftBatchTable.GetLedgerNumberDBName() + " = ?" +
" AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " <= ?" +
" AND " + AGiftBatchTable.GetBatchStatusDBName() + " = ? " +
" ORDER BY " + AGiftBatchTable.GetBatchNumberDBName();
FDataTable = db.SelectDT(
strSQL, AAccountingPeriodTable.GetTableDBName(), transaction, ParametersArray);
});
db.CloseDBConnection();
}
} // Select Gift Recipients
/// <summary>
/// Find all the gifts for a worker, presenting the results in four year columns.
/// NOTE - the user can select the field of the donor.
///
/// All the DB work was previously done by the Select Gift Recipients function above.
/// I only need to filter the table by recipientKey.
/// </summary>
/// <returns></returns>
public static DataTable SelectGiftDonors(TParameterList AParameters, TResultList AResults)
{
Int64 recipientKey = AParameters.Get("RecipientKey").ToInt64();
// TotalGiftsPerRecipient must not be static.
DataTable TotalGiftsPerRecipient = null;
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("SelectGiftDonors");
string SqlQuery = TSession.GetVariable("QueryFinanceReport_SelectGiftRecipients").ToString();
SqlQuery = SqlQuery.Replace("GROUP by ", "AND recipient.p_partner_key_n = " + recipientKey + " GROUP by ");
db.ReadTransaction(
ref Transaction,
delegate
{
TotalGiftsPerRecipient = db.SelectDT(SqlQuery, "result", Transaction);
});
TotalGiftsPerRecipient.DefaultView.RowFilter = "RecipientKey = " + recipientKey.ToString();
DataTable resultTable = TotalGiftsPerRecipient.DefaultView.ToTable(true,
new String[] { "DonorKey", "DonorName", "DonorClass", "YearTotal0", "YearTotal1", "YearTotal2", "YearTotal3" });
return(resultTable);
}
/// <summary>
/// Any foreign account that has a non-zero opening balance should be marked
/// for revaluation.
/// </summary>
private void NoteForexRevalRequired(Int32 ALedgerNumber, Int32 AYear, Int32 ABatchPeriod)
{
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("NoteForexRevalRequired", FDataBase);
Boolean submissionOK = true;
if (ABatchPeriod == FledgerInfo.NumberOfAccountingPeriods)
{
if (FledgerInfo.NumberFwdPostingPeriods == 0)
{
return;
}
else
{
// If we're closing the final period, SetNextPeriod() doesn't update FledgerInfo.CurrentPeriod. But we still want to
// check the next period for foreign forward-posted amounts, so we have to increment the period locally here.
ABatchPeriod++;
}
}
db.WriteTransaction(
ref transaction,
ref submissionOK,
delegate
{
string strSQL = "SELECT Account.a_account_code_c," +
"SUM (GLMP.a_actual_foreign_n) AS Balance" +
" FROM PUB_a_account Account, PUB_a_general_ledger_master GLM, PUB_a_general_ledger_master_period GLMP" +
" WHERE Account.a_ledger_number_i=" + ALedgerNumber +
" AND Account.a_foreign_currency_flag_l=true" +
" AND GLM.a_ledger_number_i=" + ALedgerNumber +
" AND GLM.a_account_code_c=Account.a_account_code_c" +
" AND GLM.a_year_i= " + AYear +
" AND GLMP.a_glm_sequence_i=GLM.a_glm_sequence_i" +
" AND GLMP.a_period_number_i=" + ABatchPeriod +
" GROUP BY Account.a_account_code_c";
DataTable Balance = db.SelectDT(strSQL, "Balance", transaction);
foreach (DataRow Row in Balance.Rows)
{
if (Convert.ToDecimal(Row["Balance"]) != 0)
{
TLedgerInitFlag flag = new TLedgerInitFlag(ALedgerNumber, "", transaction.DataBaseObj);
flag.SetFlagComponent(MFinanceConstants.LEDGER_INIT_FLAG_REVAL,
Row["a_account_code_c"].ToString());
}
}
});
if (FDataBase == null)
{
db.CloseDBConnection();
}
}
/// <summary>
/// Returns the current sequence value for the given Sequence from the DB.
/// </summary>
/// <param name="ASequenceName">Name of the Sequence.</param>
/// <param name="ATransaction">An instantiated Transaction in which the Query
/// to the DB will be enlisted.</param>
/// <param name="ADatabase">the database object that can be used for querying</param>
/// <returns>Sequence Value.</returns>
public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
{
DataTable table = ADatabase.SelectDT(
"SELECT PUB." + ASequenceName + ".CURRVAL FROM PUB.\"_Sequence\" WHERE \"_Seq-Name\" = \"" + ASequenceName + "\"",
"sequence",
ATransaction);
if (table.Rows.Count > 0)
{
return(Convert.ToInt64(table.Rows[0][0]));
}
return(-1);
}
public static List <string> GetUniqueTypes(
Int64 APartnerKey
)
{
TDBTransaction T = new TDBTransaction();
TDataBase DB = DBAccess.Connect("Get get unique data types");
List <OdbcParameter> SQLParameter = new List <OdbcParameter>();
List <string> Types = new List <string>();
DB.ReadTransaction(ref T, delegate {
string sql = "SELECT DISTINCT `p_type_c` FROM `p_consent_history` " +
"WHERE `p_partner_key_n` = ?";
SQLParameter.Add(new OdbcParameter("PartnerKey", OdbcType.BigInt)
{
Value = APartnerKey
});
DataTable AllTypes = DB.SelectDT(sql, "UniqueTypes", T, SQLParameter.ToArray());
foreach (DataRow TypeRow in AllTypes.Rows)
{
Types.Add(TypeRow.Field <string>("p_type_c"));
}
// also get types for existing values of the address that don't have a consent yet
if (!Types.Contains(MPartnerConstants.CONSENT_TYPE_ADDRESS))
{
Types.Add(MPartnerConstants.CONSENT_TYPE_ADDRESS);
}
if (!Types.Contains(MPartnerConstants.CONSENT_TYPE_EMAIL))
{
Types.Add(MPartnerConstants.CONSENT_TYPE_EMAIL);
}
if (!Types.Contains(MPartnerConstants.CONSENT_TYPE_LANDLINE))
{
Types.Add(MPartnerConstants.CONSENT_TYPE_LANDLINE);
}
if (!Types.Contains(MPartnerConstants.CONSENT_TYPE_MOBILE))
{
Types.Add(MPartnerConstants.CONSENT_TYPE_MOBILE);
}
});
return(Types);
}
public static void UpdateUnpostedGiftsTaxDeductiblePct(Int64 ARecipientKey, decimal ANewPct, DateTime ADateFrom)
{
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("UpdateUnpostedGiftsTaxDeductiblePct");
bool SubmissionOK = false;
db.WriteTransaction(
ref Transaction,
ref SubmissionOK,
delegate
{
string Query = "SELECT a_gift_detail.*" +
" FROM a_gift_detail, a_gift_batch, a_gift" +
" WHERE a_gift_detail.p_recipient_key_n = " + ARecipientKey +
" AND a_gift_detail.a_tax_deductible_pct_n <> " + ANewPct +
" AND a_gift_detail.a_modified_detail_l <> true" +
" AND a_gift_detail.a_tax_deductible_l = true" +
" AND a_gift_batch.a_ledger_number_i = a_gift_detail.a_ledger_number_i" +
" AND a_gift_batch.a_batch_number_i = a_gift_detail.a_batch_number_i" +
" AND a_gift_batch.a_batch_status_c = 'Unposted'" +
" AND a_gift.a_ledger_number_i = a_gift_detail.a_ledger_number_i" +
" AND a_gift.a_batch_number_i = a_gift_detail.a_batch_number_i" +
" AND a_gift.a_gift_transaction_number_i = a_gift_detail.a_gift_transaction_number_i" +
" AND a_gift.a_date_entered_d >= '" + ADateFrom.ToString("yyyy-MM-dd") + "'";
AGiftDetailTable Table = new AGiftDetailTable();
db.SelectDT(Table, Query, Transaction);
// update fields for each row
for (int i = 0; i < Table.Rows.Count; i++)
{
AGiftDetailRow Row = Table[i];
Row.TaxDeductiblePct = ANewPct;
TaxDeductibility.UpdateTaxDeductibiltyAmounts(ref Row);
}
AGiftDetailAccess.SubmitChanges(Table, Transaction);
SubmissionOK = true;
});
}
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);
}
}
public static bool IsPartnerARecipient(Int64 APartnerKey, out DataTable AGiftTotals, decimal APct, DateTime ADateFrom)
{
DataTable Table = new DataTable();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("IsPartnerARecipient");
db.ReadTransaction(
ref Transaction,
delegate
{
string Query = "SELECT" +
" a_gift_detail.a_ledger_number_i AS LedgerNumber," +
" SUM(CASE WHEN a_gift_batch.a_batch_status_c = 'Unposted' " +
" AND a_gift.a_date_entered_d >= '" + ADateFrom.ToString("yyyy-MM-dd") + "'" +
" THEN 1 ELSE 0 END) AS Unposted," +
" SUM(CASE WHEN a_gift_batch.a_batch_status_c = 'Posted' " +
" AND a_gift_batch.a_gl_effective_date_d >= '" + ADateFrom.ToString("yyyy-MM-dd") + "'" +
" THEN 1 ELSE 0 END) AS Posted" +
" FROM a_gift_detail, a_gift_batch, a_gift" +
" WHERE a_gift_detail.p_recipient_key_n = " + APartnerKey +
" AND a_gift_detail.a_tax_deductible_pct_n <> " + APct +
" AND a_gift_detail.a_modified_detail_l <> true" +
" AND a_gift_detail.a_tax_deductible_l = true" +
" AND a_gift_batch.a_ledger_number_i = a_gift_detail.a_ledger_number_i" +
" AND a_gift_batch.a_batch_number_i = a_gift_detail.a_batch_number_i" +
" AND a_gift_batch.a_ledger_number_i = a_gift_detail.a_ledger_number_i" +
" AND a_gift.a_ledger_number_i = a_gift_detail.a_ledger_number_i" +
" AND a_gift.a_batch_number_i = a_gift_detail.a_batch_number_i" +
" AND a_gift.a_gift_transaction_number_i = a_gift_detail.a_gift_transaction_number_i" +
" GROUP BY a_gift_detail.a_ledger_number_i";
Table = db.SelectDT(Query, "Gifts", Transaction);
});
AGiftTotals = Table;
return(Table != null && Table.Rows.Count > 0);
}
public static PTypeTable GetChildrenStatusOptions()
{
TDBTransaction t = new TDBTransaction();
TDataBase db = DBAccess.Connect("FindChildrenStatusOptions");
string sql = "SELECT p_type_code_c, p_type_description_c " +
"FROM PUB_p_type " +
"WHERE p_valid_type_l = 1 AND p_category_code_c = 'SPONSORED_CHILD_STATUS'";
PTypeTable result = new PTypeTable();
db.ReadTransaction(ref t,
delegate
{
db.SelectDT(result, sql, t);
});
db.CloseDBConnection();
return(result);
}
private int GetGlmSequenceFromDB(TDataBase databaseConnection,
int pv_ledger_number_i,
String pv_cost_centre_code_c,
String pv_account_code_c,
int pv_year_i)
{
int ReturnValue = -1;
String strSql = "SELECT a_glm_sequence_i FROM PUB_a_general_ledger_master WHERE a_ledger_number_i = " +
pv_ledger_number_i + " AND a_cost_centre_code_c = '" + pv_cost_centre_code_c +
"' AND a_account_code_c = '" +
pv_account_code_c + "' AND a_year_i = " + pv_year_i;
DataTable tab = databaseConnection.SelectDT(strSql, "GetGlmSequenceFromDB_TempTable", databaseConnection.Transaction);
if (tab.Rows.Count == 1)
{
ReturnValue = Convert.ToInt32(tab.Rows[0]["a_glm_sequence_i"]);
}
return(ReturnValue);
}
public static DataConsentTDS LastKnownEntry(
Int64 APartnerKey,
string ADataType
)
{
TDBTransaction T = new TDBTransaction();
TDataBase DB = DBAccess.Connect("Get Last known entry");
DataConsentTDS Set = new DataConsentTDS();
List <OdbcParameter> SQLParameter = new List <OdbcParameter>();
DB.ReadTransaction(ref T, delegate {
string sql = "SELECT " +
"`p_data_history`.*, " +
"GROUP_CONCAT(`p_data_history_permission`.`p_purpose_code_c` SEPARATOR ',') AS `AllowedPurposes` " +
"FROM `p_data_history` " +
"LEFT JOIN `p_data_history_permission` " +
"ON `p_data_history`.`p_entry_id_i` = `p_data_history_permission`.`p_data_history_entry_i` " +
"WHERE `p_data_history`.`p_partner_key_n` = ? " +
"AND `p_data_history`.`p_type_c` = ? " +
"GROUP BY `p_data_history`.`p_entry_id_i` " +
"ORDER BY `p_data_history`.`p_entry_id_i` DESC " +
"LIMIT 1";
SQLParameter.Add(new OdbcParameter("PartnerKey", OdbcType.VarChar)
{
Value = APartnerKey.ToString()
});
SQLParameter.Add(new OdbcParameter("DataType", OdbcType.VarChar)
{
Value = ADataType
});
// DB.SelectDT(Set, sql, T, SQLParameter.ToArray(), 0,0);
DB.SelectDT(Set.PDataHistory, sql, T, SQLParameter.ToArray());
PConsentChannelAccess.LoadAll(Set, T);
PPurposeAccess.LoadAll(Set, T);
});
return(Set);
}
public static DataConsentTDS GetHistory(
Int64 APartnerKey,
string ADataType
)
{
TDBTransaction T = new TDBTransaction();
TDataBase DB = DBAccess.Connect("Get data history for partner");
DataConsentTDS Set = new DataConsentTDS();
List <OdbcParameter> SQLParameter = new List <OdbcParameter>();
DB.ReadTransaction(ref T, delegate {
// prepare for one huge cunk sql
string sql = "" +
"SELECT " +
" `pdh`.*, " +
" GROUP_CONCAT(`pdhp`.`p_purpose_code_c` SEPARATOR ',') AS `AllowedPurposes` " +
"FROM `p_data_history` AS `pdh` " +
"LEFT JOIN `p_data_history_permission` AS `pdhp` " +
" ON `pdh`.`p_entry_id_i` = `pdhp`.`p_data_history_entry_i` " +
"WHERE `pdh`.`p_partner_key_n` = ? " +
" AND `pdh`.`p_type_c` = ? " +
"GROUP BY `pdh`.`p_entry_id_i` " +
"ORDER BY `pdh`.`p_entry_id_i` DESC";
SQLParameter.Add(new OdbcParameter("PartnerKey", OdbcType.BigInt)
{
Value = APartnerKey.ToString()
});
SQLParameter.Add(new OdbcParameter("DataType", OdbcType.VarChar)
{
Value = ADataType
});
Set.PDataHistory.Constraints.Clear(); //mmmm...
DB.SelectDT(Set.PDataHistory, sql, T, SQLParameter.ToArray());
PConsentChannelAccess.LoadAll(Set, T);
PPurposeAccess.LoadAll(Set, T);
});
return(Set);
}
/// <summary>
/// The contructor gets the root information
/// </summary>
public GetBatchInfo(Int32 ALedgerNumber, Int32 AYear, Int32 ABatchPeriod)
{
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetBatchInfo");
db.ReadTransaction(
ref transaction,
delegate
{
string strSQL = "SELECT * FROM PUB_a_batch" +
" WHERE a_ledger_number_i=" + ALedgerNumber +
" AND a_batch_year_i=" + AYear +
" AND a_batch_period_i=" + ABatchPeriod +
" AND a_batch_status_c <> '" + MFinanceConstants.BATCH_POSTED + "'" +
" AND a_batch_status_c <> '" + MFinanceConstants.BATCH_CANCELLED + "'" +
" ORDER BY a_batch_number_i";
db.SelectDT(Fbatches, strSQL, transaction);
});
db.CloseDBConnection();
}
public static SUserTable GetSponsorAdmins()
{
TDBTransaction t = new TDBTransaction();
TDataBase db = DBAccess.Connect("FindSponsorAdmins");
string sql = "SELECT u.s_user_id_c, u.s_first_name_c, u.s_last_name_c " +
"FROM PUB_s_user_module_access_permission p " +
"JOIN PUB_s_user u " +
"ON u.s_user_id_c = p.s_user_id_c AND p.s_module_id_c = 'SPONSORADMIN'";
SUserTable result = new SUserTable();
db.ReadTransaction(ref t,
delegate
{
db.SelectDT(result, sql, t);
});
db.CloseDBConnection();
return(result);
}
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>
/// Get the name for this Ledger
/// </summary>
public string GetLedgerName()
{
#region Validate Arguments
if (FLedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), FLedgerNumber);
}
#endregion Validate Arguments
String ReturnValue = string.Empty;
TDBTransaction ReadTransaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetLedgerName", FDataBase);
db.ReadTransaction(
ref ReadTransaction,
delegate
{
String strSql = "SELECT p_partner_short_name_c FROM PUB_a_ledger, PUB_p_partner WHERE a_ledger_number_i=" +
FLedgerNumber + " AND PUB_a_ledger.p_partner_key_n = PUB_p_partner.p_partner_key_n";
DataTable tab = db.SelectDT(strSql, "GetLedgerName_TempTable", ReadTransaction);
if (tab.Rows.Count > 0)
{
ReturnValue = Convert.ToString(tab.Rows[0][PPartnerTable.GetPartnerShortNameDBName()]); //"p_partner_short_name_c"
}
});
if (FDataBase == null)
{
db.CloseDBConnection();
}
return(ReturnValue);
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="databaseConnection"></param>
/// <param name="pv_ledger_number_i"></param>
/// <param name="pv_account_code_c"></param>
/// <param name="accountType">returns the type of account, ie. Income, Expense, Assets, Liab, Equity</param>
/// <param name="postingAccount">returns true if the account is a posting account
/// </param>
/// <param name="debitCreditIndicator"></param>
/// <returns></returns>
private bool TAccountInfo(TDataBase databaseConnection,
System.Int32 pv_ledger_number_i,
String pv_account_code_c,
out String accountType,
out bool postingAccount,
out bool debitCreditIndicator)
{
bool ReturnValue;
string strSql;
DataTable tab;
ReturnValue = false;
debitCreditIndicator = false;
accountType = "";
List <OdbcParameter> parameters = new List <OdbcParameter>();
OdbcParameter param = new OdbcParameter("accountcode", OdbcType.VarChar);
param.Value = pv_account_code_c;
parameters.Add(param);
postingAccount = false;
strSql = "SELECT a_account_type_c, a_posting_status_l, a_debit_credit_indicator_l FROM PUB_a_account" +
" WHERE a_ledger_number_i = " + pv_ledger_number_i.ToString() +
" AND a_account_code_c = ?";
tab =
databaseConnection.SelectDT(strSql, "AccountType", databaseConnection.Transaction,
parameters.ToArray());
if (tab.Rows.Count > 0)
{
accountType = Convert.ToString(tab.Rows[0]["a_account_type_c"]);
postingAccount = (Boolean)tab.Rows[0]["a_posting_status_l"];
debitCreditIndicator = (Boolean)tab.Rows[0]["a_debit_credit_indicator_l"];
ReturnValue = true;
}
return(ReturnValue);
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="databaseConnection"></param>
/// <param name="pv_ledger_number_i"></param>
/// <param name="pv_account_code_c"></param>
/// <param name="accountType">returns the type of account, ie. Income, Expense, Assets, Liab, Equity</param>
/// <param name="postingAccount">returns true if the account is a posting account
/// </param>
/// <param name="debitCreditIndicator"></param>
/// <returns></returns>
private bool TAccountInfo(TDataBase databaseConnection,
System.Int32 pv_ledger_number_i,
String pv_account_code_c,
out String accountType,
out bool postingAccount,
out bool debitCreditIndicator)
{
bool ReturnValue;
string strSql;
DataTable tab;
ReturnValue = false;
debitCreditIndicator = false;
accountType = "";
List <OdbcParameter>parameters = new List <OdbcParameter>();
OdbcParameter param = new OdbcParameter("accountcode", OdbcType.VarChar);
param.Value = pv_account_code_c;
parameters.Add(param);
postingAccount = false;
strSql = "SELECT a_account_type_c, a_posting_status_l, a_debit_credit_indicator_l FROM PUB_a_account" +
" WHERE a_ledger_number_i = " + pv_ledger_number_i.ToString() +
" AND a_account_code_c = ?";
tab =
databaseConnection.SelectDT(strSql, "AccountType", databaseConnection.Transaction,
parameters.ToArray());
if (tab.Rows.Count > 0)
{
accountType = Convert.ToString(tab.Rows[0]["a_account_type_c"]);
postingAccount = (Boolean)tab.Rows[0]["a_posting_status_l"];
debitCreditIndicator = (Boolean)tab.Rows[0]["a_debit_credit_indicator_l"];
ReturnValue = true;
}
return ReturnValue;
}
/// <summary>
/// Find recipient Partner Key and name for all partners who received gifts in the timeframe.
/// NOTE - the user can select the PartnerType of the recipient.
///
/// With only a little more load on the DB, I can get all the data that the report will need,
/// and store it in a DataTable local to this class, so that when more detailed data is requested below,
/// I don't need another DB query.
/// </summary>
/// <returns>RecipientKey, RecipientName</returns>
public static DataTable SelectGiftRecipients(TParameterList AParameters, TResultList AResults)
{
DataTable TotalGiftsPerRecipient = null;
Int32 LedgerNum = AParameters.Get("param_ledger_number_i").ToInt32();
Boolean onlySelectedTypes = AParameters.Get("param_type_selection").ToString() == "selected_types";
Boolean onlySelectedFields = AParameters.Get("param_field_selection").ToString() == "selected_fields";
Boolean fromExtract = AParameters.Get("param_recipient").ToString() == "Extract";
Boolean oneRecipient = AParameters.Get("param_recipient").ToString() == "One Recipient";
String period0Start = AParameters.Get("param_from_date_0").ToDate().ToString("yyyy-MM-dd");
String period0End = AParameters.Get("param_to_date_0").ToDate().ToString("yyyy-MM-dd");
String period1Start = AParameters.Get("param_from_date_1").ToDate().ToString("yyyy-MM-dd");
String period1End = AParameters.Get("param_to_date_1").ToDate().ToString("yyyy-MM-dd");
String period2Start = AParameters.Get("param_from_date_2").ToDate().ToString("yyyy-MM-dd");
String period2End = AParameters.Get("param_to_date_2").ToDate().ToString("yyyy-MM-dd");
String period3Start = AParameters.Get("param_from_date_3").ToDate().ToString("yyyy-MM-dd");
String period3End = AParameters.Get("param_to_date_3").ToDate().ToString("yyyy-MM-dd");
String amountFieldName = (AParameters.Get("param_currency").ToString() == "International") ?
"detail.a_gift_amount_intl_n" : "detail.a_gift_amount_n";
string SqlQuery = "SELECT DISTINCT " +
"gift.p_donor_key_n AS DonorKey, " +
"donor.p_partner_short_name_c AS DonorName, donor.p_partner_class_c AS DonorClass, " +
"recipient.p_partner_key_n AS RecipientKey, " +
"recipient.p_partner_short_name_c AS RecipientName, " +
"SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period0Start + "' AND '" + period0End + "' " +
"THEN " + amountFieldName + " ELSE 0 END )as YearTotal0, " +
"SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period1Start + "' AND '" + period1End + "' " +
"THEN " + amountFieldName + " ELSE 0 END )as YearTotal1, " +
"SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period2Start + "' AND '" + period2End + "' " +
"THEN " + amountFieldName + " ELSE 0 END )as YearTotal2, " +
"SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period3Start + "' AND '" + period3End + "' " +
"THEN " + amountFieldName + " ELSE 0 END )as YearTotal3 " +
"FROM PUB_a_gift as gift, PUB_a_gift_detail as detail, PUB_a_gift_batch AS GiftBatch, PUB_p_partner AS donor, PUB_p_partner AS recipient ";
if (onlySelectedTypes)
{
SqlQuery += ", PUB_p_partner_type AS RecipientType ";
}
if (fromExtract)
{
String extractName = AParameters.Get("param_extract_name").ToString();
SqlQuery += (", PUB_m_extract AS Extract, PUB_m_extract_master AS ExtractMaster " +
"WHERE " +
"recipient.p_partner_key_n = Extract.p_partner_key_n " +
"AND Extract.m_extract_id_i = ExtractMaster.m_extract_id_i " +
"AND ExtractMaster.m_extract_name_c = '" + extractName + "' " +
"AND "
);
}
else
{
SqlQuery += "WHERE ";
}
SqlQuery += ("detail.a_ledger_number_i = " + LedgerNum + " " +
"AND detail.p_recipient_key_n = recipient.p_partner_key_n " +
"AND gift.p_donor_key_n = donor.p_partner_key_n " +
"AND detail.a_batch_number_i = gift.a_batch_number_i " +
"AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i " +
"AND gift.a_date_entered_d BETWEEN '" + period3Start + "' AND '" + period0End + "' " +
"AND gift.a_ledger_number_i = " + LedgerNum + " " +
"AND GiftBatch.a_batch_status_c = 'Posted' " +
"AND GiftBatch.a_batch_number_i = gift.a_batch_number_i " +
"AND GiftBatch.a_ledger_number_i = " + LedgerNum + " "
);
if (oneRecipient)
{
String recipientKey = AParameters.Get("param_recipient_key").ToString();
SqlQuery += ("AND recipient.p_partner_key_n = " + recipientKey + " ");
}
if (onlySelectedFields)
{
String selectedFieldList = AParameters.Get("param_clbFields").ToString();
selectedFieldList = selectedFieldList.Replace('\'', ' ');
SqlQuery += ("AND detail.a_recipient_ledger_number_n IN (" + selectedFieldList + ") ");
}
if (onlySelectedTypes)
{
String selectedTypeList = "'" + AParameters.Get("param_clbTypes").ToString() + "'";
selectedTypeList = selectedTypeList.Replace(",", "','");
SqlQuery += ("AND RecipientType.p_partner_key_n = detail.p_recipient_key_n " +
"AND RecipientType.p_type_code_c IN (" + selectedTypeList + ") ");
}
SqlQuery +=
(
"GROUP by gift.p_donor_key_n, donor.p_partner_short_name_c, donor.p_partner_class_c, recipient.p_partner_key_n, recipient.p_partner_short_name_c "
+
"ORDER BY recipient.p_partner_short_name_c");
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("SelectGiftRecipients");
// store the query in the session to be called again in SelectGiftDonors
TSession.SetVariable("QueryFinanceReport_SelectGiftRecipients", SqlQuery);
db.ReadTransaction(
ref Transaction,
delegate
{
TotalGiftsPerRecipient = db.SelectDT(SqlQuery, "result", Transaction);
});
//
// Ok, I've got a DataTable with ALL THE DATA I need for the report,
// but for this calculation I only want a list of partner keys and names...
//
DataTable resultTable = new DataTable();
resultTable.Columns.Add("RecipientKey", typeof(String)); // These are the names of the variables
resultTable.Columns.Add("RecipientName", typeof(String)); // returned by this calculation.
Int64 previousPartner = -1;
foreach (DataRow Row in TotalGiftsPerRecipient.Rows)
{
Int64 partnerKey = Convert.ToInt64(Row["RecipientKey"]);
if (partnerKey != previousPartner)
{
previousPartner = partnerKey;
DataRow NewRow = resultTable.NewRow();
NewRow["RecipientKey"] = Row["RecipientKey"];
NewRow["RecipientName"] = Row["RecipientName"];
resultTable.Rows.Add(NewRow);
}
}
return(resultTable);
} // Select Gift Recipients
} // Total Gifts Through Field Month
/// <summary>
/// Find all the gifts for a year, returning "worker", "field" and "total" results.
/// </summary>
public static DataTable TotalGiftsThroughFieldYear(TParameterList AParameters, TResultList AResults)
{
bool TaxDeductiblePercentageEnabled =
new TSystemDefaults().GetBooleanDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, false);
Int32 LedgerNum = AParameters.Get("param_ledger_number_i").ToInt32();
Int32 NumberOfYears = AParameters.Get("param_NumberOfYears").ToInt32();
string SqlQuery = "SELECT batch.a_gl_effective_date_d as Date, motive.a_report_column_c AS ReportColumn, ";
if (AParameters.Get("param_currency").ToString() == "Base")
{
SqlQuery += "detail.a_gift_amount_n AS Amount";
if (TaxDeductiblePercentageEnabled)
{
SqlQuery += ", detail.a_tax_deductible_amount_base_n AS TaxDeductAmount";
}
}
else
{
SqlQuery += "detail.a_gift_amount_intl_n AS Amount";
if (TaxDeductiblePercentageEnabled)
{
SqlQuery += ", detail.a_tax_deductible_amount_intl_n AS TaxDeductAmount";
}
}
SqlQuery += (" FROM PUB_a_gift as gift, PUB_a_gift_detail as detail, PUB_a_gift_batch as batch, PUB_a_motivation_detail AS motive"
+ " WHERE detail.a_ledger_number_i = " + LedgerNum +
" AND batch.a_batch_status_c = 'Posted'" +
" AND batch.a_batch_number_i = gift.a_batch_number_i" +
" AND batch.a_ledger_number_i = " + LedgerNum
+ " AND gift.a_ledger_number_i = " + LedgerNum +
" AND detail.a_batch_number_i = gift.a_batch_number_i" +
" AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i"
+ " AND motive.a_ledger_number_i = " + LedgerNum +
" AND motive.a_motivation_group_code_c = detail.a_motivation_group_code_c" +
" AND motive.a_motivation_detail_code_c = detail.a_motivation_detail_code_c" +
" AND motive.a_receipt_l=true");
DataTable tempTbl = null;
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("TotalGiftsThroughFieldYear");
db.ReadTransaction(
ref Transaction,
delegate
{
tempTbl = db.SelectDT(SqlQuery, "result", Transaction);
});
DataTable resultTable = new DataTable();
resultTable.Columns.Add("SummaryYear", typeof(Int32)); //
resultTable.Columns.Add("YearWorker", typeof(Decimal)); // These are the names of the variables
resultTable.Columns.Add("YearWorkerCount", typeof(Int32)); // returned by this calculation.
resultTable.Columns.Add("YearField", typeof(Decimal)); //
resultTable.Columns.Add("YearFieldCount", typeof(Int32)); //
resultTable.Columns.Add("YearTotal", typeof(Decimal)); //
resultTable.Columns.Add("YearTotalCount", typeof(Int32)); //
resultTable.Columns.Add("YearWorkerTaxDeduct", typeof(Decimal));
resultTable.Columns.Add("YearFieldTaxDeduct", typeof(Decimal));
resultTable.Columns.Add("YearTotalTaxDeduct", typeof(Decimal));
Int32 Year = DateTime.Now.Year;
for (Int32 YearIdx = 0; YearIdx < NumberOfYears; YearIdx++)
{
//TODO: Calendar vs Financial Date Handling - Check if financial year start/end should be used not calendar
string yearStart = String.Format("#{0:0000}-01-01#", Year - YearIdx);
string yearEnd = String.Format("#{0:0000}-12-31#", Year - YearIdx);
tempTbl.DefaultView.RowFilter = "Date >= " + yearStart + " AND Date < " + yearEnd;
Decimal WorkerTotal = 0;
Decimal FieldTotal = 0;
Int32 WorkerCount = 0;
Int32 FieldCount = 0;
Int32 TotalCount = tempTbl.DefaultView.Count;
Decimal WorkerTotalTaxDeduct = 0;
Decimal FieldTotalTaxDeduct = 0;
for (int i = 0; i < TotalCount; i++)
{
DataRow Row = tempTbl.DefaultView[i].Row;
if (Row["ReportColumn"].ToString() == "Worker")
{
WorkerCount++;
WorkerTotal += Convert.ToDecimal(Row["Amount"]);
if (TaxDeductiblePercentageEnabled)
{
WorkerTotalTaxDeduct += Convert.ToDecimal(Row["TaxDeductAmount"]);
}
}
else
{
FieldCount++;
FieldTotal += Convert.ToDecimal(Row["Amount"]);
if (TaxDeductiblePercentageEnabled)
{
FieldTotalTaxDeduct += Convert.ToDecimal(Row["TaxDeductAmount"]);
}
}
}
DataRow resultRow = resultTable.NewRow();
resultRow["SummaryYear"] = Year - YearIdx;
resultRow["YearWorker"] = WorkerTotal;
resultRow["YearWorkerCount"] = WorkerCount;
resultRow["YearField"] = FieldTotal;
resultRow["YearFieldCount"] = FieldCount;
resultRow["YearTotal"] = WorkerTotal + FieldTotal;
resultRow["YearTotalCount"] = TotalCount;
resultRow["YearWorkerTaxDeduct"] = WorkerTotalTaxDeduct;
resultRow["YearFieldTaxDeduct"] = FieldTotalTaxDeduct;
resultRow["YearTotalTaxDeduct"] = WorkerTotalTaxDeduct + FieldTotalTaxDeduct;
resultTable.Rows.Add(resultRow);
}
return(resultTable);
}
private static void CheckModule(TDataBase ADBAccessObj, 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(ADBAccessObj.SelectDT(sql, "temp", null));
}
if (errors.Rows.Count > 0)
{
SendEmailToAdmin(errors);
SendEmailsPerUser(errors);
}
}
/// <summary>
/// generate the key ministries
/// </summary>
/// <param name="AKeyMinCSVFile"></param>
public static void GenerateKeyMinistries(string AKeyMinCSVFile)
{
XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AKeyMinCSVFile, ",");
XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild;
PartnerImportExportTDS PartnerDS = new PartnerImportExportTDS();
TDataBase db = DBAccess.Connect("GenerateKeyMinistries");
TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
// get a list of fields (all class UNIT, with unit type F)
string sqlGetFieldPartnerKeys = "SELECT p_partner_key_n, p_unit_name_c FROM PUB_p_unit WHERE u_unit_type_code_c = 'F'";
DataTable FieldKeys = db.SelectDT(sqlGetFieldPartnerKeys, "keys", Transaction);
Transaction.Rollback();
Int32 NumberOfPartnerKeysReserved = 100;
Int64 NextPartnerKey = TNewPartnerKey.ReservePartnerKeys(-1, ref NumberOfPartnerKeysReserved);
while (RecordNode != null)
{
int FieldID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "field")) % FieldKeys.Rows.Count;
long FieldPartnerKey = Convert.ToInt64(FieldKeys.Rows[FieldID].ItemArray[0]);
PUnitRow UnitRow = PartnerDS.PUnit.NewRowTyped();
if (NumberOfPartnerKeysReserved == 0)
{
NumberOfPartnerKeysReserved = 100;
NextPartnerKey = TNewPartnerKey.ReservePartnerKeys(-1, ref NumberOfPartnerKeysReserved);
}
long UnitPartnerKey = NextPartnerKey;
NextPartnerKey++;
NumberOfPartnerKeysReserved--;
UnitRow.PartnerKey = UnitPartnerKey;
UnitRow.UnitName = FieldKeys.Rows[FieldID].ItemArray[1].ToString() + " - " + TXMLParser.GetAttribute(RecordNode, "KeyMinName");
UnitRow.UnitTypeCode = "KEY-MIN";
PartnerDS.PUnit.Rows.Add(UnitRow);
PPartnerRow PartnerRow = PartnerDS.PPartner.NewRowTyped();
PartnerRow.PartnerKey = UnitRow.PartnerKey;
PartnerRow.PartnerShortName = UnitRow.UnitName;
PartnerRow.PartnerClass = MPartnerConstants.PARTNERCLASS_UNIT;
PartnerRow.StatusCode = MPartnerConstants.PARTNERSTATUS_ACTIVE;
PartnerDS.PPartner.Rows.Add(PartnerRow);
// add empty location so that the partner can be found in the Partner Find screen
PPartnerLocationRow PartnerLocationRow = PartnerDS.PPartnerLocation.NewRowTyped();
PartnerLocationRow.PartnerKey = UnitRow.PartnerKey;
PartnerLocationRow.LocationKey = 0;
PartnerLocationRow.SiteKey = 0;
PartnerDS.PPartnerLocation.Rows.Add(PartnerLocationRow);
// create unit hierarchy
UmUnitStructureRow UnitStructureRow = PartnerDS.UmUnitStructure.NewRowTyped();
UnitStructureRow.ParentUnitKey = FieldPartnerKey;
UnitStructureRow.ChildUnitKey = UnitRow.PartnerKey;
PartnerDS.UmUnitStructure.Rows.Add(UnitStructureRow);
RecordNode = RecordNode.NextSibling;
}
PartnerImportExportTDSAccess.SubmitChanges(PartnerDS);
}
private static GiftBatchTDS CreateGiftBatches(SortedList <DateTime, List <XmlNode> > AGiftsPerDate, int APeriodNumber)
{
GiftBatchTDS MainDS = new GiftBatchTDS();
ALedgerTable LedgerTable = null;
TDBTransaction ReadTransaction = new TDBTransaction();
DBAccess.ReadTransaction(ref ReadTransaction,
delegate
{
TDataBase db = ReadTransaction.DataBaseObj;
// get a list of potential donors (all class FAMILY)
string sqlGetFamilyPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_family";
DataTable FamilyKeys = db.SelectDT(sqlGetFamilyPartnerKeys,
"keys",
ReadTransaction);
// get a list of workers (all class FAMILY, with special type WORKER)
string sqlGetWorkerPartnerKeys =
"SELECT PUB_p_family.p_partner_key_n FROM PUB_p_family, PUB_p_partner_type WHERE PUB_p_partner_type.p_partner_key_n = PUB_p_family.p_partner_key_n AND p_type_code_c = 'WORKER'";
DataTable WorkerKeys = db.SelectDT(sqlGetWorkerPartnerKeys, "keys", ReadTransaction);
// get a list of fields (all class UNIT, with unit type F)
string sqlGetFieldPartnerKeys =
String.Format(
"SELECT U.p_partner_key_n FROM PUB_p_unit U WHERE u_unit_type_code_c = 'F' AND EXISTS (SELECT * FROM PUB_a_valid_ledger_number V WHERE V.a_ledger_number_i = {0} AND V.p_partner_key_n = U.p_partner_key_n)",
FLedgerNumber);
DataTable FieldKeys = db.SelectDT(sqlGetFieldPartnerKeys, "keys", ReadTransaction);
// get a list of key ministries (all class UNIT, with unit type KEY-MIN), and their field ledger number and cost centre code
string sqlGetKeyMinPartnerKeys =
"SELECT u.p_partner_key_n, us.um_parent_unit_key_n, vl.a_cost_centre_code_c " +
"FROM PUB_p_unit u, PUB_um_unit_structure us, PUB_a_valid_ledger_number vl " +
"WHERE u.u_unit_type_code_c = 'KEY-MIN' " +
"AND us.um_child_unit_key_n = u.p_partner_key_n " +
"AND vl.p_partner_key_n = us.um_parent_unit_key_n " +
"AND vl.a_ledger_number_i = " + FLedgerNumber.ToString();
DataTable KeyMinistries = db.SelectDT(sqlGetKeyMinPartnerKeys, "keys", ReadTransaction);
LedgerTable = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, ReadTransaction);
AAccountingPeriodRow AccountingPeriodRow = AAccountingPeriodAccess.LoadByPrimaryKey(FLedgerNumber,
APeriodNumber,
ReadTransaction)[0];
// create a gift batch for each day.
// TODO: could create one batch per month, if there are not so many gifts (less than 100 per month)
foreach (DateTime GlEffectiveDate in AGiftsPerDate.Keys)
{
if ((GlEffectiveDate.CompareTo(AccountingPeriodRow.PeriodStartDate) < 0) ||
(GlEffectiveDate.CompareTo(AccountingPeriodRow.PeriodEndDate) > 0))
{
// only create gifts in that period
continue;
}
AGiftBatchRow giftBatch = TGiftBatchFunctions.CreateANewGiftBatchRow(ref MainDS,
ref ReadTransaction,
ref LedgerTable,
FLedgerNumber,
GlEffectiveDate);
TLogging.LogAtLevel(1, "create gift batch for " + GlEffectiveDate.ToShortDateString());
giftBatch.BatchDescription = "Benerator Batch for " + GlEffectiveDate.ToShortDateString();
giftBatch.BatchTotal = 0.0m;
foreach (XmlNode RecordNode in AGiftsPerDate[GlEffectiveDate])
{
AGiftRow gift = MainDS.AGift.NewRowTyped();
gift.LedgerNumber = giftBatch.LedgerNumber;
gift.BatchNumber = giftBatch.BatchNumber;
gift.GiftTransactionNumber = giftBatch.LastGiftNumber + 1;
gift.DateEntered = GlEffectiveDate;
// set donorKey
int donorID = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "donor")) % FamilyKeys.Rows.Count;
gift.DonorKey = Convert.ToInt64(FamilyKeys.Rows[donorID].ItemArray[0]);
// calculate gift detail information
int countDetails = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "splitgift"));
for (int counter = 1; counter <= countDetails; counter++)
{
AGiftDetailRow giftDetail = MainDS.AGiftDetail.NewRowTyped();
giftDetail.LedgerNumber = gift.LedgerNumber;
giftDetail.BatchNumber = gift.BatchNumber;
giftDetail.GiftTransactionNumber = gift.GiftTransactionNumber;
giftDetail.MotivationGroupCode = "GIFT";
giftDetail.GiftTransactionAmount = Convert.ToDecimal(TXMLParser.GetAttribute(RecordNode, "amount_" + counter.ToString()));
giftDetail.GiftAmount = giftDetail.GiftTransactionAmount;
giftBatch.BatchTotal += giftDetail.GiftAmount;
string motivation = TXMLParser.GetAttribute(RecordNode, "motivation_" + counter.ToString());
if (motivation == "SUPPORT")
{
if (WorkerKeys.Rows.Count == 0)
{
continue;
}
giftDetail.MotivationDetailCode = "SUPPORT";
int recipientID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_support_" +
counter.ToString())) % WorkerKeys.Rows.Count;
giftDetail.RecipientKey = Convert.ToInt64(WorkerKeys.Rows[recipientID].ItemArray[0]);
giftDetail.RecipientLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(giftDetail.RecipientKey,
giftBatch.GlEffectiveDate);
// ignore this gift detail, if there is no valid commitment period for the worker
if (giftDetail.RecipientLedgerNumber == 0)
{
continue;
}
}
else if (motivation == "FIELD")
{
if (FieldKeys.Rows.Count == 0)
{
continue;
}
giftDetail.MotivationDetailCode = "FIELD";
int recipientID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_field_" +
counter.ToString())) % FieldKeys.Rows.Count;
giftDetail.RecipientKey = Convert.ToInt64(FieldKeys.Rows[recipientID].ItemArray[0]);
giftDetail.RecipientLedgerNumber = giftDetail.RecipientKey;
giftDetail.CostCentreCode = (giftDetail.RecipientKey / 10000).ToString("0000");
}
else if (motivation == "KEYMIN")
{
if (KeyMinistries.Rows.Count == 0)
{
continue;
}
giftDetail.MotivationDetailCode = "KEYMIN";
int recipientID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_keymin_" +
counter.ToString())) % KeyMinistries.Rows.Count;
giftDetail.RecipientKey = Convert.ToInt64(KeyMinistries.Rows[recipientID].ItemArray[0]);
giftDetail.RecipientLedgerNumber = Convert.ToInt64(KeyMinistries.Rows[recipientID].ItemArray[1]);
// TTransactionWebConnector.GetRecipientFundNumber(giftDetail.RecipientKey);
giftDetail.CostCentreCode = KeyMinistries.Rows[recipientID].ItemArray[2].ToString();
// TTransactionWebConnector.IdentifyPartnerCostCentre(FLedgerNumber, giftDetail.RecipientLedgerNumber);
}
giftDetail.DetailNumber = gift.LastDetailNumber + 1;
MainDS.AGiftDetail.Rows.Add(giftDetail);
gift.LastDetailNumber = giftDetail.DetailNumber;
}
if (gift.LastDetailNumber > 0)
{
MainDS.AGift.Rows.Add(gift);
giftBatch.LastGiftNumber = gift.GiftTransactionNumber;
}
if (giftBatch.LastGiftNumber >= MaxGiftsPerBatch)
{
break;
}
}
if (TLogging.DebugLevel > 0)
{
TLogging.Log(
GlEffectiveDate.ToShortDateString() + " " + giftBatch.LastGiftNumber.ToString());
}
}
});
// need to save the last gift batch number in a_ledger
if (LedgerTable != null)
{
TDBTransaction WriteTransaction = new TDBTransaction();
bool SubmissionOk = false;
DBAccess.WriteTransaction(ref WriteTransaction, ref SubmissionOk,
delegate
{
ALedgerAccess.SubmitChanges(LedgerTable, WriteTransaction);
SubmissionOk = true;
});
if (!SubmissionOk)
{
TLogging.Log("An Exception occured during the creation of Gift Batches" + Environment.NewLine);
}
}
return(MainDS);
}
/// <summary>
/// Returns the current sequence value for the given Sequence from the DB.
/// </summary>
/// <param name="ASequenceName">Name of the Sequence.</param>
/// <param name="ATransaction">An instantiated Transaction in which the Query
/// to the DB will be enlisted.</param>
/// <param name="ADatabase">the database object that can be used for querying</param>
/// <returns>Sequence Value.</returns>
public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
{
DataTable table = ADatabase.SelectDT(
"SELECT PUB." + ASequenceName + ".CURRVAL FROM PUB.\"_Sequence\" WHERE \"_Seq-Name\" = \"" + ASequenceName + "\"",
"sequence",
ATransaction);
if (table.Rows.Count > 0)
{
return Convert.ToInt64(table.Rows[0][0]);
}
return -1;
}
private int GetGlmSequenceFromDB(TDataBase databaseConnection,
int pv_ledger_number_i,
String pv_cost_centre_code_c,
String pv_account_code_c,
int pv_year_i)
{
int ReturnValue = -1;
String strSql = "SELECT a_glm_sequence_i FROM PUB_a_general_ledger_master WHERE a_ledger_number_i = " +
pv_ledger_number_i + " AND a_cost_centre_code_c = '" + pv_cost_centre_code_c +
"' AND a_account_code_c = '" +
pv_account_code_c + "' AND a_year_i = " + pv_year_i;
DataTable tab = databaseConnection.SelectDT(strSql, "GetGlmSequenceFromDB_TempTable", databaseConnection.Transaction);
if (tab.Rows.Count == 1)
{
ReturnValue = Convert.ToInt32(tab.Rows[0]["a_glm_sequence_i"]);
}
return ReturnValue;
}
public static void Main(string[] args)
{
try
{
// establish connection to database
TCmdOpts settings = new TCmdOpts();
if (!settings.IsFlagSet("Server.ODBC_DSN"))
{
Console.WriteLine(
"sample call: " +
"ExportDataProgress.exe -Server.ODBC_DSN:Petra2_2sa -username:demo_sql -password:demo -sql:\"SELECT * from pub.a_account\" -output:test.xml");
Environment.Exit(-1);
}
new TLogging("debug.log");
TDataBase db = new TDataBase();
TDBType dbtype = TDBType.ProgressODBC;
if (settings.IsFlagSet("Server.RDBMSType"))
{
dbtype = CommonTypes.ParseDBType(settings.GetOptValue("Server.RDBMSType"));
}
if (dbtype != TDBType.ProgressODBC)
{
throw new Exception("at the moment only Progress ODBC db is supported");
}
db.EstablishDBConnection(dbtype,
settings.GetOptValue("Server.ODBC_DSN"),
"",
"",
settings.GetOptValue("username"),
settings.GetOptValue("password"),
"");
DBAccess.GDBAccessObj = db;
TLogging.DebugLevel = 10;
string sqlText = "";
if (!settings.IsFlagSet("sql"))
{
Console.WriteLine("Please enter sql and finish with semicolon: ");
while (!sqlText.Trim().EndsWith(";"))
{
sqlText += " " + Console.ReadLine();
}
sqlText = sqlText.Substring(0, sqlText.Length - 1);
}
else
{
sqlText = settings.GetOptValue("sql");
Console.WriteLine(sqlText);
}
TDBTransaction transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadUncommitted);
DataTable table = db.SelectDT(sqlText, "temp", transaction);
XmlDocument doc = TDataBase.DataTableToXml(table);
if (settings.IsFlagSet("output"))
{
if (settings.GetOptValue("output").EndsWith("yml"))
{
TYml2Xml.Xml2Yml(doc, settings.GetOptValue("output"));
}
else if (settings.GetOptValue("output").EndsWith("csv"))
{
TCsv2Xml.Xml2Csv(doc, settings.GetOptValue("output"));
}
else if (settings.GetOptValue("output").EndsWith("xml"))
{
StreamWriter sw = new StreamWriter(settings.GetOptValue("output"));
sw.Write(TXMLParser.XmlToString2(doc));
sw.Close();
}
}
else
{
TYml2Xml.Xml2Yml(doc, "temp.yml");
StreamReader sr = new StreamReader("temp.yml");
Console.WriteLine(sr.ReadToEnd());
sr.Close();
}
db.RollbackTransaction();
db.CloseDBConnection();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
}
}
