/// <summary>
/// Gets called in regular intervals from a Timer in Class TTimedProcessing.
/// </summary>
/// <param name="ADBAccessObj">Instantiated DB Access object with opened DB connection.</param>
/// <param name="ARunManually">this is true if the process was called manually from the server admin console</param>
public static void Process(TDataBase ADBAccessObj, bool ARunManually)
{
// only check once a day (or as specified in config file), if not manually called
if (!ARunManually)
{
DateTime LastRun =
TVariant.DecodeFromString(
TSystemDefaults.GetSystemDefault(
PROCESSDATACHECK_LAST_RUN,
new TVariant(DateTime.MinValue).EncodeToString())).ToDate();
if (LastRun.AddDays(TAppSettingsManager.GetInt16("DataChecks.RunEveryXDays", 1)) > DateTime.Now)
{
// do not run the data check more than once a day or a week (depending on configuration setting), too many emails
TLogging.LogAtLevel(1, "TProcessDataChecks.Process: not running, since last run was at " + LastRun.ToString());
return;
}
}
Errors_SinceDate = DateTime.Today.AddDays(-1 * SENDREPORTFORDAYS_TOUSERS);
TLogging.LogAtLevel(1, "TProcessDataChecks.Process: Checking Modules");
CheckModule(ADBAccessObj, "DataCheck.MPartner.");
TSystemDefaults.SetSystemDefault(PROCESSDATACHECK_LAST_RUN, new TVariant(DateTime.Now).EncodeToString());
}
/// <summary>
/// @param connection The System.Object that represents the established ODBC connection to the Petra Database
/// </summary>
/// <param name="connection">The System.Object that represents the established ODBC connection to the Petra Database
/// </param>
/// <param name="APathStandardReports"></param>
/// <param name="APathCustomReports"></param>
public TRptDataCalculator(TDataBase connection, String APathStandardReports, String APathCustomReports)
: base(new TParameterList(), new TResultList(), new TReportStore(), null, connection, 0, -1, -1, -1)
{
if (!HasBeenInitialized)
{
InitializeUnit();
}
FPathStandardReports = APathStandardReports;
FPathCustomReports = APathCustomReports;
}
/// <summary>
/// constructor
/// </summary>
/// <param name="parameters"></param>
/// <param name="results"></param>
/// <param name="reportStore"></param>
/// <param name="report"></param>
/// <param name="dataDB"></param>
/// <param name="depth"></param>
/// <param name="column"></param>
/// <param name="lineId"></param>
/// <param name="parentRowId"></param>
public TRptEvaluator(TParameterList parameters,
TResultList results,
TReportStore reportStore,
TRptReport report,
TDataBase dataDB,
int depth,
int column,
int lineId,
int parentRowId)
: base(parameters, results, reportStore, report, dataDB, depth, column, lineId, parentRowId)
{
}
/// <summary>
/// Gets called in regular intervals from a Timer in Class TTimedProcessing.
/// </summary>
/// <param name="ADBAccessObj">Instantiated DB Access object with opened DB connection.</param>
/// <param name="ARunManually">this is true if the process was called manually from the server admin console</param>
public static void Process(TDataBase ADBAccessObj, bool ARunManually)
{
//At the moment this isn't ready for production use
return;
#if TODO
//TODO: create the gpg files from IntranetExport, with parameters for including finance or personnel
string exportpath = TAppSettingsManager.GetValue("OpenPetra.PathExport");
if (Directory.Exists(exportpath) == false)
{
TLogging.Log("Auto Export directory not found : " + exportpath);
return;
}
//if there is a file there, send it
bool foundfile = false;
foreach (FileInfo fi in new DirectoryInfo(exportpath).GetFiles("*.gpg"))
{
foundfile = true;
TLogging.Log("Emailing file: " + fi.FullName);
//TODO: Get the name of the administrator from the system_defaults table
bool result = new TSmtpSender().SendEmail("<*****@*****.**>",
"OpenPetra Server",
"<*****@*****.**>",
"Automatic Intranet Export",
"This is an automatic file upload from OpenPetra",
new string[] { fi.FullName });
if (result)
{
TLogging.Log("SMTP Server accepted file: " + fi.FullName);
fi.Delete();
//TODO: exception handler
}
else
{
TLogging.Log("SMTP Server refused file: " + fi.FullName);
}
}
if (!foundfile)
{
TLogging.Log("No file found to email");
}
#endif
}
// establish connection to database
public static bool InitDBConnection(
string ADBFile, string ADBPassword)
{
db = new TDataBase();
new TLogging("debug.log");
TLogging.DebugLevel = TAppSettingsManager.GetInt16("DebugLevel", 0);
db.EstablishDBConnection(TDBType.SQLite,
ADBFile,
"",
"",
"",
ADBPassword,
"");
DBAccess.GDBAccessObj = db;
return true;
}
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);
}
}
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);
}
}
/// <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">Database object that can be used for querying.</param>
/// <returns>Sequence Value.</returns>
public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
{
return(Convert.ToInt64(ADatabase.ExecuteScalar("SELECT last_value FROM " + ASequenceName + "", ATransaction)));
}
/// <summary>
/// Clears (flushes) all RDMBS Connection Pools and returns the new number of DB Connections after clearing all
/// RDMBS Connection Pools.
/// </summary>
/// <returns>New number of DB Connections after clearing all RDMBS Connection Pools.</returns>
public override int ClearConnectionPoolAndGetNumberOfDBConnections()
{
return(TDataBase.ClearConnectionPoolAndGetNumberOfDBConnections(TSrvSetting.RDMBSType));
}
/// <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">Database object that can be used for querying.</param>
/// <returns>Sequence Value.</returns>
public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
{
string stmt = "SELECT MAX(sequence) FROM " + ASequenceName + ";";
using (SqliteCommand cmd = new SqliteCommand(stmt, (SqliteConnection)ATransaction.Connection))
{
return Convert.ToInt64(cmd.ExecuteScalar());
}
}
/// the given period should not be changed with diffPeriod
public TFinancialPeriod(TDataBase databaseConnection, int realPeriod, int year, TParameterList parameters, int column, bool real)
{
diffPeriod = 0;
FCurrentFinancialYear = parameters.Get("param_current_financial_year_i", column).ToInt();
FNumberAccountingPeriods = parameters.Get("param_number_of_accounting_periods_i", column).ToInt();
FCurrentPeriod = parameters.Get("param_current_period_i", column).ToInt();
FNumberForwardingPeriods = parameters.Get("param_number_of_accounting_periods_i", column).ToInt();
Int32 glmSequenceNumber = parameters.Get("glm_sequence_i", column).ToInt();
realGlmSequence = LedgerStatus.GlmSequencesCache.GetGlmSequence(glmSequenceNumber);
MainConstructor(databaseConnection,
realPeriod,
year,
diffPeriod,
FCurrentFinancialYear,
FCurrentPeriod,
FNumberAccountingPeriods,
FNumberForwardingPeriods,
realGlmSequence);
}
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;
}
private decimal GetCorporateExchangeRateFromDB(TDataBase databaseConnection,
int pv_ledger_number_i,
int pv_year_i,
int pv_period_i,
int currentFinancialYear)
{
ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(pv_ledger_number_i, databaseConnection.Transaction);
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(pv_ledger_number_i,
pv_period_i,
databaseConnection.Transaction);
if (AccountingPeriodTable.Rows.Count < 1)
{
return -1; // This is poor (because the caller can blindly use it!)
// I wonder whether an exception would be better. (Tim Ingham, Oct 2013)
}
if (currentFinancialYear < 0)
{
currentFinancialYear = ledgerTable[0].CurrentFinancialYear;
}
DateTime startOfPeriod = AccountingPeriodTable[0].PeriodStartDate;
DateTime endOfPeriod = AccountingPeriodTable[0].PeriodEndDate;
startOfPeriod = new DateTime(startOfPeriod.Year - (currentFinancialYear - pv_year_i), startOfPeriod.Month, startOfPeriod.Day);
if ((endOfPeriod.Month == 2) && (endOfPeriod.Day == 29)
&& (((currentFinancialYear - pv_year_i)) % 4 != 0))
{
endOfPeriod = endOfPeriod.AddDays(-1);
}
endOfPeriod = new DateTime(endOfPeriod.Year - (currentFinancialYear - pv_year_i), endOfPeriod.Month, endOfPeriod.Day);
// get the corporate exchange rate between base and intl currency for the period
return TExchangeRateTools.GetCorporateExchangeRate(ledgerTable[0].IntlCurrency,
ledgerTable[0].BaseCurrency,
startOfPeriod,
endOfPeriod);
}
private TVerificationResultCollection PostAPDocument(AccountsPayableTDS AMainDS, DateTime APostingDate,
ref List <int> ADocumentIds, bool AReversal = false, TDataBase ADataBase = null)
{
string AssertFailMessage = AReversal ? "Failed to post AP document reversal: " : "Problems posting AP document: ";
TVerificationResultCollection VerificationResult;
if (!AReversal)
{
ADocumentIds.Add(AMainDS.AApDocument[0].ApDocumentId);
}
Int32 glBatchNumber;
if (!TAPTransactionWebConnector.PostAPDocuments(FLedgerNumber,
ADocumentIds,
APostingDate,
AReversal,
out glBatchNumber,
out VerificationResult,
ADataBase))
{
Assert.Fail(AssertFailMessage +
VerificationResult.BuildVerificationResultString());
}
CommonNUnitFunctions.EnsureNullOrEmptyVerificationResult(VerificationResult, AssertFailMessage); // Guard Assert
return(VerificationResult);
}
private AAPInfos PostForeignSupplierAPDocument(decimal AAmount, decimal AExchangeRatePosting, string ADocumentCode, string ANarrative,
out decimal AAPAccountBalanceBefore, out decimal ABankAccountBefore, out decimal AExpAccountBefore,
out decimal ARevalAccountBefore, out List <int> ADocumentIds, TDataBase ADataBase)
{
TVerificationResultCollection VerificationResult;
ADocumentIds = new List <int>();
AAPInfos APInfos = APTestsArrangement(SUPPLIER_FOREIGN_PARTNER_KEY, AAmount, AExchangeRatePosting, ADocumentCode, ANarrative, ADataBase);
// Save the current amount on the AP account
AAPAccountBalanceBefore = new TGet_GLM_Info(FLedgerNumber,
APInfos.ApAccountCode, APInfos.CostCentreCode, ADataBase).YtdActual;
ABankAccountBefore = new TGet_GLM_Info(FLedgerNumber,
APInfos.BankAccount, APInfos.CostCentreCode, ADataBase).YtdForeign;
AExpAccountBefore = new TGet_GLM_Info(FLedgerNumber,
APInfos.ApDS.AApSupplier[0].DefaultExpAccount,
APInfos.CostCentreCode, ADataBase).YtdActual;
ARevalAccountBefore = new TGet_GLM_Info(FLedgerNumber,
APInfos.ForexGainsLossesAccount,
APInfos.CostCentreCode, ADataBase).YtdActual;
VerificationResult = PostAPDocument(APInfos.ApDS, APInfos.PeriodStartDate, ref ADocumentIds, false, ADataBase);
CommonNUnitFunctions.EnsureNullOrEmptyVerificationResult(VerificationResult); // Guard Assert
//
// Guard Assert: Posting OK?
//
decimal ExpAccountAfter = new TGet_GLM_Info(FLedgerNumber,
APInfos.ApDS.AApSupplier[0].DefaultExpAccount,
APInfos.ApDS.AApSupplier[0].DefaultCostCentre, ADataBase).YtdActual;
Assert.AreEqual(Math.Round(AAmount / AExchangeRatePosting, 2), Math.Round(ExpAccountAfter - AExpAccountBefore,
2), "after posting the invoice, the expense account should be debited the amount in base currency (Exchange Rate is " +
AExchangeRatePosting + ")");
return(APInfos);
}
/// <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>
/// Creates a AP document for the supplier specified with APartnerKey.
/// </summary>
/// <param name="APartnerKey"></param>
/// <param name="AAmount"></param>
/// <param name="AExchangeRatePosting"></param>
/// <param name="ADocumentCode"></param>
/// <param name="ANarrative"></param>
/// <param name="AMainDS"></param>
/// <param name="ADataBase"></param>
/// <returns></returns>
private TVerificationResultCollection CreateAPDocument(Int64 APartnerKey, decimal AAmount, decimal?AExchangeRatePosting,
string ADocumentCode, string ANarrative, out AccountsPayableTDS AMainDS, TDataBase ADataBase)
{
string AssertFailMessage = "Problems saving AP document: ";
TSubmitChangesResult SubmRes;
TVerificationResultCollection VerificationResult;
TDataBase db = DBAccess.Connect("CreateAPDocument", ADataBase);
AMainDS = TAPTransactionWebConnector.CreateAApDocument(FLedgerNumber, APartnerKey, false, db);
AccountsPayableTDS MainDS = AMainDS;
TDBTransaction Transaction = new TDBTransaction();
db.ReadTransaction(
ref Transaction,
delegate
{
AApSupplierAccess.LoadByPrimaryKey(MainDS, APartnerKey, Transaction);
});
AMainDS.AApDocument[0].DocumentCode = ADocumentCode + (DocumentCounter++).ToString();
AMainDS.Merge(TAPTransactionWebConnector.CreateAApDocumentDetail(
FLedgerNumber,
AMainDS.AApDocument[0].ApDocumentId,
AMainDS.AApSupplier[0].DefaultExpAccount,
AMainDS.AApSupplier[0].DefaultCostCentre,
AAmount,
AMainDS.AApDocument[0].LastDetailNumber + 1));
AMainDS.AApDocument[0].LastDetailNumber++;
AMainDS.AApDocument[0].TotalAmount = AAmount;
AMainDS.AApDocument[0].DocumentStatus = MFinanceConstants.AP_DOCUMENT_APPROVED;
AMainDS.AApDocumentDetail[0].Narrative = ANarrative;
if (AExchangeRatePosting.HasValue)
{
AMainDS.AApDocument[0].ExchangeRateToBase = AExchangeRatePosting.Value;
}
SubmRes = TAPTransactionWebConnector.SaveAApDocument(ref AMainDS, out VerificationResult, db);
if (SubmRes != TSubmitChangesResult.scrOK)
{
Assert.Fail(AssertFailMessage + String.Format(" - (SaveAApDocument return value: {0}) - ", SubmRes) +
VerificationResult.BuildVerificationResultString());
}
CommonNUnitFunctions.EnsureNullOrEmptyVerificationResult(VerificationResult, AssertFailMessage); // Guard Assert
return(VerificationResult);
}
/// <summary>
/// reserve a number of partner keys, to be used by the calling function.
/// useful to create many partner at once, eg. for the demodata
/// </summary>
/// <param name="AFieldPartnerKey"></param>
/// <param name="ANumberOfKeys"></param>
/// <param name="ADataBase"></param>
/// <returns>the first valid partner key to use</returns>
public static System.Int64 ReservePartnerKeys(System.Int64 AFieldPartnerKey, ref Int32 ANumberOfKeys, TDataBase ADataBase = null)
{
Int64 NextPartnerKey = -1;
Int32 NumberOfKeys = ANumberOfKeys;
if (AFieldPartnerKey == -1)
{
AFieldPartnerKey = DomainManager.GSiteKey;
}
TDBTransaction ReadWriteTransaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("ReservePartnerKeys", ADataBase);
bool SubmissionOK = true;
db.WriteTransaction(ref ReadWriteTransaction,
ref SubmissionOK,
delegate
{
PPartnerLedgerTable PartnerLedgerDT = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadWriteTransaction);
NextPartnerKey = PartnerLedgerDT[0].PartnerKey + PartnerLedgerDT[0].LastPartnerId + 1;
Int64 NextUsedKey =
Convert.ToInt64(db.ExecuteScalar("SELECT MIN(p_partner_key_n) FROM PUB_p_partner WHERE p_partner_key_n >= " +
NextPartnerKey.ToString(), ReadWriteTransaction));
if (NextUsedKey < NextPartnerKey + NumberOfKeys)
{
NumberOfKeys = Convert.ToInt32(NextUsedKey - NextPartnerKey);
}
PartnerLedgerDT[0].LastPartnerId = Convert.ToInt32((NextPartnerKey + NumberOfKeys - 1) - PartnerLedgerDT[0].PartnerKey);
PPartnerLedgerAccess.SubmitChanges(PartnerLedgerDT, ReadWriteTransaction);
SubmissionOK = true;
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
if (!SubmissionOK)
{
throw new Exception("ReservePartnerKeys failed");
}
ANumberOfKeys = NumberOfKeys;
return(NextPartnerKey);
}
/// <summary>
/// this checks if the new key is still available,
/// and makes sure it will not be used as a default key anymore
/// </summary>
/// <param name="AFieldPartnerKey"></param>
/// <param name="AOriginalDefaultKey">this has been previously retrieved from GetNewPartnerKey</param>
/// <param name="ANewPartnerKey">the user proposes this key for a new partner; the function can change it and return a valid value, or -1</param>
/// <param name="ADataBase"></param>
/// <returns>whether or not ANewPartnerKey has a valid new partner key;
/// if it cannot be assigned, the function returns false, and ANewPartnerKey is -1
/// </returns>
public static bool SubmitNewPartnerKey(System.Int64 AFieldPartnerKey, System.Int64 AOriginalDefaultKey, ref System.Int64 ANewPartnerKey, TDataBase ADataBase = null)
{
bool ReturnValue = true;
TDBTransaction ReadTransaction;
TDBTransaction WriteTransaction;
Boolean NewTransaction;
PPartnerLedgerTable PartnerLedgerDT;
TDataBase db = DBAccess.Connect("SubmitNewPartnerKey", ADataBase);
System.Int64 CurrentDefaultPartnerKey;
if (ANewPartnerKey == AOriginalDefaultKey)
{
// The user has selected the default
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
out NewTransaction);
try
{
// Fetch the partner ledger record to update the last key
PartnerLedgerDT = PPartnerLedgerAccess.LoadByPrimaryKey(AFieldPartnerKey, ReadTransaction);
CurrentDefaultPartnerKey = PartnerLedgerDT[0].PartnerKey + PartnerLedgerDT[0].LastPartnerId + 1;
if (ANewPartnerKey != CurrentDefaultPartnerKey)
{
// Someone else has updated this since, so we will use the new default
ANewPartnerKey = CurrentDefaultPartnerKey;
}
// Now check that this does not exist, and increment until we
// find one which does not
while (PPartnerAccess.Exists(ANewPartnerKey, ReadTransaction))
{
ANewPartnerKey = ANewPartnerKey + 1;
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Rollback();
if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_TRACE)
{
Console.WriteLine("TNewPartnerKey.SubmitNewPartnerKey: rolled back own transaction.");
}
}
}
PartnerLedgerDT[0].LastPartnerId = (int)(ANewPartnerKey - PartnerLedgerDT[0].PartnerKey);
WriteTransaction = db.GetNewOrExistingTransaction(IsolationLevel.Serializable,
out NewTransaction);
try
{
PPartnerLedgerAccess.SubmitChanges(PartnerLedgerDT, WriteTransaction);
if (NewTransaction)
{
WriteTransaction.Commit();
}
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the submission of a new PartnerKey:" + Environment.NewLine + Exc.ToString());
if (NewTransaction)
{
WriteTransaction.Rollback();
}
throw;
}
}
// end of: The user has selected the default
else
{
ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
out NewTransaction);
try
{
// check if the Partner Key is already being used
if (PPartnerAccess.Exists(ANewPartnerKey, ReadTransaction))
{
ANewPartnerKey = -1;
ReturnValue = false;
}
}
finally
{
if (NewTransaction)
{
ReadTransaction.Rollback();
if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_TRACE)
{
Console.WriteLine("TNewPartnerKey.SubmitNewPartnerKey: rolled back own transaction.");
}
}
}
}
if (ADataBase == null)
{
db.CloseDBConnection();
}
return(ReturnValue);
}
public static Boolean GetMotivationGroupAndDetailForPartner(Int64 APartnerKey,
ref String AMotivationGroup,
ref String AMotivationDetail)
{
Boolean PartnerKeyIsValid = false;
if (APartnerKey != 0)
{
string motivationGroup = MFinanceConstants.MOTIVATION_GROUP_GIFT;
string motivationDetail = AMotivationDetail;
TDBTransaction readTransaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetMotivationGroupAndDetailForPartner");
db.ReadTransaction(
ref readTransaction,
delegate
{
PPartnerTable myPPartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, readTransaction);
if (myPPartnerTable.Rows.Count == 1)
{
// partnerKey is valid
PartnerKeyIsValid = true;
PPartnerRow partnerRow = myPPartnerTable[0];
// Change motivationDetail if PartnerClass is UNIT
if (partnerRow.PartnerClass.Equals(MPartnerConstants.PARTNERCLASS_UNIT))
{
// AND KEY-MIN
bool KeyMinFound = false;
// first check if a specific motivation detail is linked to this partner
AMotivationDetailTable MotivationDetailTable
= AMotivationDetailAccess.LoadViaPPartner(APartnerKey, readTransaction);
if ((MotivationDetailTable != null) && (MotivationDetailTable.Rows.Count > 0))
{
foreach (AMotivationDetailRow Row in MotivationDetailTable.Rows)
{
if (Row.MotivationStatus)
{
motivationGroup = MotivationDetailTable[0].MotivationGroupCode;
motivationDetail = MotivationDetailTable[0].MotivationDetailCode;
KeyMinFound = true;
break; // Go with the first entry found.
}
}
}
if (!KeyMinFound)
{
// Is this is a key min, or a field?
PUnitTable pUnitTable = PUnitAccess.LoadByPrimaryKey(APartnerKey, readTransaction);
if (pUnitTable.Rows.Count == 1)
{
PUnitRow unitRow = pUnitTable[0];
switch (unitRow.UnitTypeCode)
{
case MPartnerConstants.UNIT_TYPE_AREA:
case MPartnerConstants.UNIT_TYPE_FUND:
case MPartnerConstants.UNIT_TYPE_FIELD:
motivationDetail = MFinanceConstants.GROUP_DETAIL_FIELD;
break;
case MPartnerConstants.UNIT_TYPE_KEYMIN:
motivationDetail = MFinanceConstants.GROUP_DETAIL_KEY_MIN;
break;
case MPartnerConstants.UNIT_TYPE_COUNTRY:
case MPartnerConstants.UNIT_TYPE_CONFERENCE:
case MPartnerConstants.UNIT_TYPE_OTHER:
case MPartnerConstants.UNIT_TYPE_ROOT:
case MPartnerConstants.UNIT_TYPE_TEAM:
case MPartnerConstants.UNIT_TYPE_WORKING_GROUP:
default:
motivationDetail = MFinanceConstants.GROUP_DETAIL_SUPPORT;
break;
}
}
}
}
else
{
motivationDetail = MFinanceConstants.GROUP_DETAIL_SUPPORT;
}
}
});
db.CloseDBConnection();
AMotivationGroup = motivationGroup;
AMotivationDetail = motivationDetail;
}
return(PartnerKeyIsValid);
}
/// <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">Database object that can be used for querying.</param>
/// <returns>Sequence Value.</returns>
public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
{
return Convert.ToInt64(ADatabase.ExecuteScalar("SELECT last_value FROM " + ASequenceName + "", ATransaction));
}
private AAPInfos PostAndPayForeignSupplierAPDocument(string ADocumentCode, out int APaymentNumber, out List <int> ADocumentIDs,
out decimal AAPAccountBalanceBefore, out decimal ABankAccountBefore, out decimal ARevalAccountBefore, TDataBase ADataBase)
{
decimal Amount = 100.0m;
decimal ExchangeRatePosting = 1.2m;
decimal ExchangeRatePayment = 1.1m;
decimal ExpAccountBefore;
TVerificationResultCollection VerificationResult;
AAPInfos APInfos;
APInfos = PostForeignSupplierAPDocument(Amount, ExchangeRatePosting, ADocumentCode, "Detail Item",
out AAPAccountBalanceBefore, out ABankAccountBefore, out ExpAccountBefore, out ARevalAccountBefore, out ADocumentIDs, ADataBase);
//
// Pay the AP document
//
VerificationResult = PayAPDocument(APInfos.ApDS.AApDocument[0].ApDocumentId, Amount,
APInfos.BankAccount, APInfos.CurrencyCode, APInfos.PeriodEndDate, out APaymentNumber, ExchangeRatePayment, ADataBase);
CommonNUnitFunctions.EnsureNullOrEmptyVerificationResult(VerificationResult); // Guard Assert
// Save the current amount on the AP account and Bank Account
decimal APAccountBalanceAfter = new TGet_GLM_Info(FLedgerNumber,
APInfos.ApAccountCode, APInfos.CostCentreCode, ADataBase).YtdActual;
decimal BankAccountAfter = new TGet_GLM_Info(FLedgerNumber,
APInfos.BankAccount, APInfos.CostCentreCode, ADataBase).YtdForeign;
//
// Guard Assert: Paying OK?
//
// Check the amount on the AP account
Assert.AreEqual(0.0m, APAccountBalanceAfter - AAPAccountBalanceBefore, "after paying the invoice, the AP account should be cleared");
Assert.AreEqual((-1.0m) * Amount, BankAccountAfter - ABankAccountBefore, "after paying the invoice, the bank account should be credited");
decimal RevalAccountAfter = new TGet_GLM_Info(FLedgerNumber,
APInfos.ForexGainsLossesAccount, APInfos.CostCentreCode, ADataBase).YtdActual;
Assert.AreEqual(
Math.Round((Amount / ExchangeRatePayment) - (Amount / ExchangeRatePosting), 2),
Math.Round((RevalAccountAfter - ARevalAccountBefore), 2),
"after paying the invoice, the revaluation account should be credited with the forex gain");
return(APInfos);
}
/// <summary>
/// Get the sequence System.Object of a a_general_ledger_master
/// </summary>
/// <returns>the glm sequence System.Object of the a_general_ledger_master row of the given year; nil if there is no available a_general_ledger_master row
/// </returns>
public TGlmSequence GetGlmSequenceCurrentYear(TDataBase databaseConnection,
int pv_ledger_number_i,
String pv_cost_centre_code_c,
String pv_account_code_c,
int pv_current_financial_year_i)
{
TGlmSequence ReturnValue;
int sequenceNumber;
String accountType;
bool postingAccount;
bool debitCreditIndicator;
// first check the local cache
ReturnValue = null;
foreach (TGlmSequence glmSequenceElement in glmSequences)
{
if ((glmSequenceElement.ledger_number == pv_ledger_number_i) && (glmSequenceElement.account_code == pv_account_code_c)
&& (glmSequenceElement.cost_centre_code == pv_cost_centre_code_c) && (glmSequenceElement.year == pv_current_financial_year_i))
{
ReturnValue = glmSequenceElement;
break;
}
}
if (ReturnValue == null)
{
sequenceNumber = GetGlmSequenceFromDB(databaseConnection,
pv_ledger_number_i,
pv_cost_centre_code_c,
pv_account_code_c,
pv_current_financial_year_i);
if (sequenceNumber == -1)
{
// for the summary accounts from alternative account hierarchies,
// which don't have glm records.
sequenceNumber = NextNegativeSequence;
NextNegativeSequence = NextNegativeSequence - 1;
}
if (TAccountInfo(databaseConnection, pv_ledger_number_i, pv_account_code_c, out accountType, out postingAccount,
out debitCreditIndicator))
{
TGlmSequence glmSequenceElement = new TGlmSequence(pv_ledger_number_i,
pv_account_code_c,
pv_cost_centre_code_c,
StringHelper.IsSame(accountType, "income") || StringHelper.IsSame(accountType, "expense"),
postingAccount,
debitCreditIndicator,
sequenceNumber,
pv_current_financial_year_i);
glmSequences.Add(glmSequenceElement);
ReturnValue = glmSequenceElement;
}
else
{
// account could not be found
ReturnValue = null;
}
}
return ReturnValue;
}
private TVerificationResultCollection PayAPDocument(int AApDocumentId, decimal AAmount, string ABankAccount,
string ACurrencyCode, DateTime APeriodEndDate, out int APaymentNumber, decimal?AExchangeRatePayment = null, TDataBase ADataBase = null)
{
string AssertFailMessage = "Problems paying AP document: ";
TVerificationResultCollection VerificationResult;
AccountsPayableTDS MainDS = new AccountsPayableTDS();
AApPaymentRow Payment = MainDS.AApPayment.NewRowTyped();
Payment.LedgerNumber = FLedgerNumber;
Payment.PaymentNumber = -1;
Payment.Amount = AAmount;
Payment.BankAccount = ABankAccount;
Payment.CurrencyCode = ACurrencyCode;
if (AExchangeRatePayment.HasValue)
{
Payment.ExchangeRateToBase = AExchangeRatePayment.Value;
}
MainDS.AApPayment.Rows.Add(Payment);
AApDocumentPaymentRow DocPayment = MainDS.AApDocumentPayment.NewRowTyped();
DocPayment.LedgerNumber = FLedgerNumber;
DocPayment.ApDocumentId = AApDocumentId;
DocPayment.Amount = AAmount;
DocPayment.PaymentNumber = Payment.PaymentNumber;
MainDS.AApDocumentPayment.Rows.Add(DocPayment);
Int32 glBatchNumber;
AccountsPayableTDSAApPaymentTable newPayments;
if (!TAPTransactionWebConnector.PostAPPayments(ref MainDS, APeriodEndDate,
out glBatchNumber,
out newPayments,
out VerificationResult,
ADataBase))
{
Assert.Fail(AssertFailMessage +
VerificationResult.BuildVerificationResultString());
}
CommonNUnitFunctions.EnsureNullOrEmptyVerificationResult(VerificationResult, AssertFailMessage); // Guard Assert
APaymentNumber = DocPayment.PaymentNumber;
return(VerificationResult);
}
/// <summary>
/// generate the partners from a text file that was generated with Benerator
/// </summary>
/// <param name="AInputBeneratorFile"></param>
public static void GenerateWorkers(string AInputBeneratorFile)
{
PartnerEditTDS MainDS = new PartnerEditTDS();
PersonnelTDS PersonnelDS = new PersonnelTDS();
TDataBase db = DBAccess.Connect("GenerateWorkers");
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);
// get a list of banks (all class BANK)
string sqlGetBankPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_bank";
DataTable BankKeys = db.SelectDT(sqlGetBankPartnerKeys, "keys", Transaction);
Transaction.Rollback();
// AlanP: May 2016 - We may no longer need the UTF8 because the method now automatically discovers the encoding even with no BOM
XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AInputBeneratorFile, ",", Encoding.UTF8);
XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild;
while (RecordNode != null)
{
string familySituation = TXMLParser.GetAttribute(RecordNode, "familySituation");
PFamilyRow familyRecord = null;
if (familySituation == "singleMan")
{
familyRecord = GenerateFamilyRecord(RecordNode, "Male", MainDS);
GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS);
}
else if (familySituation == "singleWoman")
{
familyRecord = GenerateFamilyRecord(RecordNode, "Female", MainDS);
GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS);
}
else if (familySituation == "family")
{
familyRecord = GenerateFamilyRecord(RecordNode, "Male", MainDS);
GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS);
GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS);
int AgeDifferenceSpouse = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "AgeDifferenceSpouse"));
DataView FamilyView = new DataView(MainDS.PPerson);
FamilyView.RowFilter = PPersonTable.GetFamilyKeyDBName() + " = " + familyRecord.PartnerKey.ToString();
FamilyView.Sort = PPersonTable.GetFamilyIdDBName();
PPersonRow HusbandPersonRow = (PPersonRow)FamilyView[0].Row;
PPersonRow WifePersonRow = (PPersonRow)FamilyView[1].Row;
WifePersonRow.DateOfBirth =
WifePersonRow.DateOfBirth.Value.AddYears(
AgeDifferenceSpouse - (WifePersonRow.DateOfBirth.Value.Year - HusbandPersonRow.DateOfBirth.Value.Year));
if (DateTime.Today.Year - WifePersonRow.DateOfBirth.Value.Year < 19)
{
WifePersonRow.DateOfBirth.Value.AddYears(
19 - (DateTime.Today.Year - WifePersonRow.DateOfBirth.Value.Year));
}
int NumberOfChildren = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "numberOfChildren"));
for (int countChild = 0; countChild < NumberOfChildren; countChild++)
{
DateTime DateOfBirthChild = Convert.ToDateTime(
TXMLParser.GetAttribute(RecordNode,
"Child" + (countChild + 1).ToString() + "DateOfBirth"));
// mother must have been 19 when the child was born
if (DateOfBirthChild.Year < WifePersonRow.DateOfBirth.Value.Year + 19)
{
continue;
}
GeneratePersonRecord(RecordNode, familyRecord, "Child" + (countChild + 1).ToString(), MainDS);
}
}
GenerateAddressForFamily(RecordNode, familyRecord, MainDS);
GenerateCommitmentRecord(RecordNode, familyRecord, MainDS, PersonnelDS, FieldKeys);
GenerateBankDetails(RecordNode, familyRecord, MainDS, BankKeys);
if (MainDS.PFamily.Rows.Count % 100 == 0)
{
TLogging.Log("created worker " + MainDS.PFamily.Rows.Count.ToString() + " " + familyRecord.FamilyName);
}
RecordNode = RecordNode.NextSibling;
}
MainDS.ThrowAwayAfterSubmitChanges = true;
PartnerEditTDSAccess.SubmitChanges(MainDS);
PersonnelDS.ThrowAwayAfterSubmitChanges = true;
PersonnelTDSAccess.SubmitChanges(PersonnelDS);
TLogging.Log("after saving workers");
}
private TVerificationResultCollection ReversePayment(int APaymentNumber, DateTime APeriodEndDate,
List <int> ADocumentIds, AccountsPayableTDS AApDS, TDataBase ADataBase)
{
string AssertFailMessage = "Failed to reverse AP payment: ";
TVerificationResultCollection VerificationResult;
List <Int32> glBatchNumbers;
// "Un-pay" the specified invoice
if (!TAPTransactionWebConnector.ReversePayment(FLedgerNumber,
APaymentNumber,
APeriodEndDate,
out glBatchNumbers,
out VerificationResult, ADataBase))
{
Assert.Fail(AssertFailMessage +
VerificationResult.BuildVerificationResultString());
}
CommonNUnitFunctions.EnsureNullOrEmptyVerificationResult(VerificationResult, AssertFailMessage); // Guard Assert
// "Un-post" the specified invoice - returning it to "Approved" status!
ADocumentIds[0] += 2; // The invoice I posted was reversed, and a duplicate now exists with an Id 2 greater than the original.
return(PostAPDocument(AApDS, APeriodEndDate, ref ADocumentIds, true, ADataBase));
}
/// <summary>
/// Get the sequence number of another year, using a known sequence number
/// </summary>
/// <param name="databaseConnection"></param>
/// <param name="glmSequence">The known sequence number of a general_ledger_master year</param>
/// <param name="year">The year of the required sequence</param>
/// <returns>the glm sequence System.Object of the a_general_ledger_master row of the another year; null if there is no available a_general_ledger_master row
/// </returns>
///
public TGlmSequence GetOtherYearGlmSequence(TDataBase databaseConnection, TGlmSequence glmSequence, int year)
{
TGlmSequence ReturnValue;
int sequenceNumber;
if (glmSequence == null)
{
return null;
}
// first check the local cache
ReturnValue = null;
foreach (TGlmSequence glmSequenceElement in glmSequences)
{
if ((glmSequenceElement.currentFinancialYearSequence == glmSequence.currentFinancialYearSequence) && (glmSequenceElement.year == year))
{
ReturnValue = glmSequenceElement;
break;
}
}
if ((ReturnValue == null) && (glmSequence != null))
{
sequenceNumber = GetGlmSequenceFromDB(databaseConnection,
glmSequence.ledger_number,
glmSequence.cost_centre_code,
glmSequence.account_code,
year);
if (sequenceNumber == -1)
{
// for the summary accounts from alternative account hierarchies,
// which don't have glm records.
sequenceNumber = NextNegativeSequence;
NextNegativeSequence--;
}
TGlmSequence glmSequenceElement = new TGlmSequence(glmSequence, sequenceNumber, year);
glmSequences.Add(glmSequenceElement);
ReturnValue = glmSequenceElement;
}
return ReturnValue;
}
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,
TDataBase ADataBase)
{
/* 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
TDBTransaction DBTransaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("ExportGifts", ADataBase);
db.ReadTransaction(ref DBTransaction,
delegate
{
String IchNumberFilter = "";
if (AIchNumber > 0)
{
IchNumberFilter = " AND GiftDetail.a_ich_number_i = " + AIchNumber;
}
String Query = "SELECT " +
" GiftDetail.a_ledger_number_i, " +
" GiftDetail.a_batch_number_i, " +
" GiftDetail.a_gift_transaction_number_i, " +
" GiftDetail.a_detail_number_i, " +
" GiftDetail.a_gift_amount_n, " +
" GiftDetail.a_gift_amount_intl_n, " +
" GiftDetail.a_motivation_group_code_c, " +
" GiftDetail.a_motivation_detail_code_c, " +
" GiftDetail.p_recipient_key_n, " +
" Gift.a_gift_status_c, " +
" MotiviationDetail.a_motivation_detail_desc_c, " +
" GiftBatch.a_batch_description_c " +
" FROM " +
" public.a_gift_detail AS GiftDetail, " +
" public.a_gift_batch AS GiftBatch, " +
" public.a_motivation_detail AS MotiviationDetail, " +
" public.a_gift AS Gift " +
" WHERE " +
" GiftDetail.a_ledger_number_i = GiftBatch.a_ledger_number_i " +
" AND GiftDetail.a_batch_number_i = GiftBatch.a_batch_number_i " +
" AND GiftDetail.a_ledger_number_i = MotiviationDetail.a_ledger_number_i " +
" AND GiftDetail.a_motivation_group_code_c = MotiviationDetail.a_motivation_group_code_c " +
" AND GiftDetail.a_motivation_detail_code_c = MotiviationDetail.a_motivation_detail_code_c " +
" AND GiftDetail.a_ledger_number_i = Gift.a_ledger_number_i " +
" AND GiftDetail.a_batch_number_i = Gift.a_batch_number_i " +
" AND GiftDetail.a_gift_transaction_number_i = Gift.a_gift_transaction_number_i " +
" AND GiftDetail.a_ledger_number_i = " + ALedgerNumber +
" AND GiftDetail.a_cost_centre_code_c = '" + ACostCentre + "'" +
IchNumberFilter +
" AND GiftBatch.a_batch_status_c = '" + MFinanceConstants.BATCH_POSTED + "'" +
" AND GiftBatch.a_gl_effective_date_d >= " + APeriodStartDate.ToString("#yyyy-MM-dd#") +
" AND GiftBatch.a_gl_effective_date_d <= " + APeriodEndDate.ToString("#yyyy-MM-dd#") +
" AND MotiviationDetail.a_account_code_c = '" + AAcctCode + "'" +
" ORDER BY " +
" GiftDetail.p_recipient_key_n ASC, " +
" GiftDetail.a_motivation_group_code_c ASC, " +
" GiftDetail.a_motivation_detail_code_c ASC;";
DataTable TmpTable = db.SelectDT(Query, "table", DBTransaction);
foreach (DataRow Row in TmpTable.Rows)
{
/* Print totals etc. found for last recipient */
/* Only do after first loop due to last recipient key check */
tmpLastRecipKey = Convert.ToInt32(Row["p_recipient_key_n"]);
tmpLastGroup = Row["a_motivation_group_code_c"].ToString();
tmpLastDetail = Row["a_motivation_detail_code_c"].ToString();
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(Row["a_gift_amount_n"]);
if (GiftAmount < 0)
{
IndividualDebitTotal -= GiftAmount;
}
else
{
IndividualCreditTotal += GiftAmount;
}
}
else
{
Decimal IntlGiftAmount = Convert.ToDecimal(Row["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(Row["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>
/// Closes the Database connection to the Database
/// for TTimedProcessing.
/// </summary>
/// <returns>void</returns>
private static void CloseDBConnection(TDataBase DBAccessObj)
{
DBAccessObj.CloseDBConnection();
}
/// <summary>
/// export all posted invoices of other suppliers in this year
/// </summary>
public static void Export(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
Int32 AFinancialYear,
string ACostCentres)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "accountspayable.csv"));
Console.WriteLine("Writing file: " + filename);
StringBuilder sb = new StringBuilder();
TDBTransaction Transaction = new TDBTransaction();
DBAccess.ReadTransaction(ref Transaction,
delegate
{
TDataBase db = Transaction.DataBaseObj;
// get all posted or paid ap_documents by their date issued
string sql =
String.Format(
"SELECT * FROM PUB_{0} " +
"WHERE {1} = {2} AND " +
"({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
"{7} >= ? AND {7} <= ?",
AApDocumentTable.GetTableDBName(),
AApDocumentTable.GetLedgerNumberDBName(),
ALedgerNumber,
AApDocumentTable.GetDocumentStatusDBName(),
MFinanceConstants.AP_DOCUMENT_POSTED,
MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
MFinanceConstants.AP_DOCUMENT_PAID,
AApDocumentTable.GetDateIssuedDBName());
List <OdbcParameter> Parameters = new List <OdbcParameter>();
OdbcParameter param = new OdbcParameter("startdate", OdbcType.DateTime);
param.Value = TAccountingPeriodsWebConnector.GetPeriodStartDate(ALedgerNumber, AFinancialYear, 0, 1);
Parameters.Add(param);
param = new OdbcParameter("enddate", OdbcType.DateTime);
//TODO: Calendar vs Financial Date Handling - Check if this should use financial year start/end and not assume calendar
param.Value = TAccountingPeriodsWebConnector.GetPeriodEndDate(ALedgerNumber, AFinancialYear, 0, 12);
Parameters.Add(param);
AApDocumentTable apDocuments = new AApDocumentTable();
db.SelectDT(apDocuments, sql, Transaction, Parameters.ToArray(), 0, 0);
// get all ap details
sql =
String.Format(
"SELECT Detail.* FROM PUB_{0} AS Doc, PUB_{8} AS Detail " +
"WHERE Doc.{1} = {2} AND " +
"({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
"{7} >= ? AND {7} <= ? AND " +
"Detail.{1} = Doc.{1} AND Detail.{9} = Doc.{9} AND " +
"Detail.{10} IN ({11})",
AApDocumentTable.GetTableDBName(),
AApDocumentTable.GetLedgerNumberDBName(),
ALedgerNumber,
AApDocumentTable.GetDocumentStatusDBName(),
MFinanceConstants.AP_DOCUMENT_POSTED,
MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
MFinanceConstants.AP_DOCUMENT_PAID,
AApDocumentTable.GetDateIssuedDBName(),
AApDocumentDetailTable.GetTableDBName(),
AApDocumentTable.GetApDocumentIdDBName(),
AApDocumentDetailTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'");
AApDocumentDetailTable apDetails = new AApDocumentDetailTable();
db.SelectDT(apDetails, sql, Transaction, Parameters.ToArray(), 0, 0);
apDetails.DefaultView.Sort = AApDocumentDetailTable.GetApDocumentIdDBName();
// get all ap payments
sql =
String.Format(
"SELECT DP.{0}, DP.{1}, P.{2} AS {3}, DP.{7}, DP.{15} FROM PUB_{4} AS Doc, PUB_{5} AS DP, PUB_{6} AS P " +
"WHERE Doc.{7} = {8} AND " +
"({9} = '{10}' OR {9} = '{11}' OR {9} = '{12}') AND " +
"{13} >= ? AND {13} <= ? AND " +
"DP.{7} = Doc.{7} AND DP.{14} = Doc.{14} AND " +
"P.{7} = Doc.{7} AND P.{15} = DP.{15}",
AApDocumentPaymentTable.GetApDocumentIdDBName(),
AApDocumentPaymentTable.GetAmountDBName(),
AApPaymentTable.GetPaymentDateDBName(),
AApDocumentPaymentTable.GetDateCreatedDBName(),
AApDocumentTable.GetTableDBName(),
AApDocumentPaymentTable.GetTableDBName(),
AApPaymentTable.GetTableDBName(),
AApDocumentTable.GetLedgerNumberDBName(),
ALedgerNumber,
AApDocumentTable.GetDocumentStatusDBName(),
MFinanceConstants.AP_DOCUMENT_POSTED,
MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
MFinanceConstants.AP_DOCUMENT_PAID,
AApDocumentTable.GetDateIssuedDBName(),
AApDocumentTable.GetApDocumentIdDBName(),
AApPaymentTable.GetPaymentNumberDBName());
AApDocumentPaymentTable apPayments = new AApDocumentPaymentTable();
db.SelectDT(apPayments, sql, Transaction, Parameters.ToArray(), 0, 0);
apPayments.DefaultView.Sort = AApDocumentPaymentTable.GetApDocumentIdDBName();
// get the analysis attributes for the taxes
sql =
String.Format(
"SELECT Attrib.* FROM PUB_{0} AS Doc, PUB_{8} AS Attrib " +
"WHERE Doc.{1} = {2} AND " +
"({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
"{7} >= ? AND {7} <= ? AND " +
"Attrib.{1} = Doc.{1} AND Attrib.{9} = Doc.{9}",
AApDocumentTable.GetTableDBName(),
AApDocumentTable.GetLedgerNumberDBName(),
ALedgerNumber,
AApDocumentTable.GetDocumentStatusDBName(),
MFinanceConstants.AP_DOCUMENT_POSTED,
MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
MFinanceConstants.AP_DOCUMENT_PAID,
AApDocumentTable.GetDateIssuedDBName(),
AApAnalAttribTable.GetTableDBName(),
AApDocumentTable.GetApDocumentIdDBName());
AApAnalAttribTable apAttrib = new AApAnalAttribTable();
db.SelectDT(apAttrib, sql, Transaction, Parameters.ToArray(), 0, 0);
apAttrib.DefaultView.Sort = AApAnalAttribTable.GetApDocumentIdDBName() + "," + AApAnalAttribTable.GetDetailNumberDBName();
// get the supplier name
sql =
String.Format(
"SELECT DISTINCT p.* FROM PUB_{0} AS Doc, PUB_{8} AS p " +
"WHERE Doc.{1} = {2} AND " +
"({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
"{7} >= ? AND {7} <= ? AND " +
"p.{9} = Doc.{10}",
AApDocumentTable.GetTableDBName(),
AApDocumentTable.GetLedgerNumberDBName(),
ALedgerNumber,
AApDocumentTable.GetDocumentStatusDBName(),
MFinanceConstants.AP_DOCUMENT_POSTED,
MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
MFinanceConstants.AP_DOCUMENT_PAID,
AApDocumentTable.GetDateIssuedDBName(),
PPartnerTable.GetTableDBName(),
PPartnerTable.GetPartnerKeyDBName(),
AApDocumentTable.GetPartnerKeyDBName());
PPartnerTable suppliers = new PPartnerTable();
db.SelectDT(suppliers, sql, Transaction, Parameters.ToArray(), 0, 0);
foreach (AApDocumentRow doc in apDocuments.Rows)
{
PPartnerRow supplier = (PPartnerRow)suppliers.Rows.Find(doc.PartnerKey);
DataRowView[] detailsRV = apDetails.DefaultView.FindRows(doc.ApDocumentId);
foreach (DataRowView rv in detailsRV)
{
AApDocumentDetailRow detail = (AApDocumentDetailRow)rv.Row;
if (doc.CreditNoteFlag)
{
detail.Amount *= -1.0m;
}
DataRowView[] payments = apPayments.DefaultView.FindRows(doc.ApDocumentId);
string DatePaid = string.Empty;
if (payments.Length > 1)
{
DatePaid = "Several Payments: ";
foreach (DataRowView rvPayment in payments)
{
AApDocumentPaymentRow payment = ((AApDocumentPaymentRow)rvPayment.Row);
DatePaid += payment.DateCreated.Value.ToString("yyyyMMdd") + " ";
DatePaid += payment.PaymentNumber.ToString() + "; ";
}
// for gdpdu, only write dates to this column
DatePaid = string.Empty;
}
else if (payments.Length == 1)
{
AApDocumentPaymentRow payment = ((AApDocumentPaymentRow)payments[0].Row);
DatePaid = payment.DateCreated.Value.ToString("yyyyMMdd");
}
sb.Append(StringHelper.StrMerge(
new string[] {
doc.ApNumber.ToString(),
detail.DetailNumber.ToString(),
doc.DateIssued.ToString("yyyyMMdd"),
DatePaid,
doc.PartnerKey.ToString(),
supplier.PartnerShortName,
detail.CostCentreCode,
detail.AccountCode,
String.Format("{0:N}", detail.Amount),
detail.Narrative
}, ACSVSeparator));
sb.Append(ANewLine);
}
}
});
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
/// <summary>
/// Returns the next Sequence Value for the given Sequence from the DB. - IMPORTANT: This increasing of the
/// Value of the Sequence PERSISTS in the PostgreSQL implmentation even if the DB Transction gets rolled back!!!
/// --> See https://wiki.openpetra.org/index.php/PostgreSQL:_Sequences_Not_Tied_to_DB_Transactions
/// </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">Database object that can be used for querying.</param>
/// <returns>Sequence Value.</returns>
public System.Int64 GetNextSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
{
return(Convert.ToInt64(ADatabase.ExecuteScalar("SELECT NEXTVAL('" + ASequenceName + "')", ATransaction)));
}
public void TestBankingDetails()
{
TDataBase db = DBAccess.Connect("TestBankingDetails");
TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.Serializable);
TPartnerEditUIConnector connector = new TPartnerEditUIConnector(db);
PartnerEditTDS MainDS = new PartnerEditTDS();
PPartnerRow PartnerRow = TCreateTestPartnerData.CreateNewFamilyPartner(MainDS, db);
TCreateTestPartnerData.CreateNewLocation(PartnerRow.PartnerKey, MainDS);
DataSet ResponseDS = new PartnerEditTDS();
TVerificationResultCollection VerificationResult;
TSubmitChangesResult result = connector.SubmitChanges(ref MainDS, ref ResponseDS, out VerificationResult);
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"There was a critical error when saving:");
Assert.AreEqual(TSubmitChangesResult.scrOK, result, "TPartnerEditUIConnector SubmitChanges return value");
Transaction.Commit();
Transaction = db.BeginTransaction(IsolationLevel.Serializable);
connector = new TPartnerEditUIConnector(PartnerRow.PartnerKey, db);
// add a banking detail
PartnerEditTDSPBankingDetailsRow bankingDetailsRow = MainDS.PBankingDetails.NewRowTyped(true);
bankingDetailsRow.AccountName = "account of " + PartnerRow.PartnerShortName;
bankingDetailsRow.BankAccountNumber = new Random().Next().ToString();
bankingDetailsRow.BankingDetailsKey = (MainDS.PBankingDetails.Count + 1) * -1;
bankingDetailsRow.BankKey = 43005004;
bankingDetailsRow.MainAccount = true;
bankingDetailsRow.BankingType = MPartnerConstants.BANKINGTYPE_BANKACCOUNT;
MainDS.PBankingDetails.Rows.Add(bankingDetailsRow);
PPartnerBankingDetailsRow partnerBankingDetails = MainDS.PPartnerBankingDetails.NewRowTyped(true);
partnerBankingDetails.PartnerKey = PartnerRow.PartnerKey;
partnerBankingDetails.BankingDetailsKey = bankingDetailsRow.BankingDetailsKey;
MainDS.PPartnerBankingDetails.Rows.Add(partnerBankingDetails);
result = connector.SubmitChanges(ref MainDS, ref ResponseDS, out VerificationResult);
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"There was a critical error when saving 2:");
foreach (DataTable t in MainDS.Tables)
{
if ((t == MainDS.PBankingDetails) ||
(t == MainDS.PPartnerBankingDetails) ||
(t == MainDS.PDataLabelValuePartner))
{
int NumRows = t.Rows.Count;
for (int RowIndex = NumRows - 1; RowIndex >= 0; RowIndex -= 1)
{
DataRow InspectDR = t.Rows[RowIndex];
// delete all added Rows.
if (InspectDR.RowState == DataRowState.Added)
{
InspectDR.Delete();
}
}
}
}
MainDS.AcceptChanges();
Transaction.Commit();
Transaction = db.BeginTransaction(IsolationLevel.Serializable);
Assert.AreEqual(1, PBankingDetailsUsageAccess.CountViaPPartner(PartnerRow.PartnerKey, Transaction), "count of main accounts for partner");
// add another account
bankingDetailsRow = MainDS.PBankingDetails.NewRowTyped(true);
bankingDetailsRow.AccountName = "2nd account of " + PartnerRow.PartnerShortName;
bankingDetailsRow.BankAccountNumber = new Random().Next().ToString();
bankingDetailsRow.BankingDetailsKey = (MainDS.PBankingDetails.Count + 1) * -1;
bankingDetailsRow.BankKey = 43005004;
bankingDetailsRow.MainAccount = false;
bankingDetailsRow.BankingType = MPartnerConstants.BANKINGTYPE_BANKACCOUNT;
MainDS.PBankingDetails.Rows.Add(bankingDetailsRow);
partnerBankingDetails = MainDS.PPartnerBankingDetails.NewRowTyped(true);
partnerBankingDetails.PartnerKey = PartnerRow.PartnerKey;
partnerBankingDetails.BankingDetailsKey = bankingDetailsRow.BankingDetailsKey;
MainDS.PPartnerBankingDetails.Rows.Add(partnerBankingDetails);
PartnerEditTDS ChangedDS = MainDS.GetChangesTyped(true);
result = connector.SubmitChanges(ref ChangedDS, ref ResponseDS, out VerificationResult);
MainDS.Merge(ChangedDS);
foreach (DataTable t in MainDS.Tables)
{
if ((t == MainDS.PBankingDetails) ||
(t == MainDS.PPartnerBankingDetails) ||
(t == MainDS.PDataLabelValuePartner))
{
int NumRows = t.Rows.Count;
for (int RowIndex = NumRows - 1; RowIndex >= 0; RowIndex -= 1)
{
DataRow InspectDR = t.Rows[RowIndex];
// delete all added Rows.
if (InspectDR.RowState == DataRowState.Added)
{
InspectDR.Delete();
}
}
}
}
MainDS.AcceptChanges();
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"There was a critical error when saving 3:");
// now delete the main bank account
PartnerEditTDSPBankingDetailsRow toDelete = null;
foreach (PartnerEditTDSPBankingDetailsRow row in MainDS.PBankingDetails.Rows)
{
if (row.MainAccount)
{
toDelete = row;
break;
}
}
Assert.IsNotNull(toDelete, "cannot find main account");
Assert.AreEqual(true, toDelete.MainAccount, "should be the main account");
MainDS.PPartnerBankingDetails.Rows.Find(new object[] { PartnerRow.PartnerKey, toDelete.BankingDetailsKey }).Delete();
toDelete.Delete();
ChangedDS = MainDS.GetChangesTyped(true);
result = connector.SubmitChanges(ref ChangedDS, ref ResponseDS, out VerificationResult);
Assert.AreEqual(1, VerificationResult.Count, "should fail because we have no main account anymore");
Assert.AreEqual(
"One Bank Account of a Partner must be set as the 'Main Account'. Please select the record that should become the 'Main Account' and choose 'Set Main Account'.",
VerificationResult[0].ResultText,
"should fail because we have no main account anymore");
PartnerEditTDSPBankingDetailsRow otherAccount = null;
foreach (PartnerEditTDSPBankingDetailsRow row in MainDS.PBankingDetails.Rows)
{
if ((row.RowState != DataRowState.Deleted) && !row.MainAccount)
{
otherAccount = row;
break;
}
}
otherAccount.MainAccount = true;
ChangedDS = MainDS.GetChangesTyped(true);
result = connector.SubmitChanges(ref ChangedDS, ref ResponseDS, out VerificationResult);
MainDS.Merge(ChangedDS);
MainDS.AcceptChanges();
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"There was a critical error when saving 4:");
Transaction.Commit();
Transaction = db.BeginTransaction(IsolationLevel.Serializable);
// now delete the last remaining bank account
toDelete = MainDS.PBankingDetails[0];
Assert.AreEqual(true, toDelete.MainAccount);
MainDS.PPartnerBankingDetails.Rows.Find(new object[] { PartnerRow.PartnerKey, toDelete.BankingDetailsKey }).Delete();
toDelete.Delete();
ChangedDS = MainDS.GetChangesTyped(true);
result = connector.SubmitChanges(ref ChangedDS, ref ResponseDS, out VerificationResult);
MainDS.Merge(ChangedDS);
MainDS.AcceptChanges();
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"There was a critical error when saving 5:");
Transaction.Commit();
}
/// <summary>
/// Gets called in regular intervals from a Timer in Class TTimedProcessing.
/// </summary>
/// <param name="ADataBaseObj">Already instatiated DB Access object with opened DB connection.</param>
/// <param name="ARunManually">this is true if the process was called manually from the server admin console</param>
public static void Process(TDataBase ADataBaseObj, bool ARunManually)
{
TDBTransaction ReadWriteTransaction;
bool NewTransaction;
bool LastReminderDateAcquired;
DateTime LastReminderDate;
DataSet ReminderResultsDS;
SSystemDefaultsRow SystemDefaultsDR;
PPartnerReminderTable PartnerReminderDT;
int ReminderFreqency;
TSmtpSender Sender = null;
if (TLogging.DebugLevel >= 6)
{
TLogging.Log("Entering TProcessPartnerReminders.Process...");
}
ReadWriteTransaction = ADataBaseObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
out NewTransaction);
/*
* This whole process must either succeed or fail, therefore the whole thing is in a try-catch.
*/
try
{
/*
* Obtain date when PartnerReminders last ran. This is stored in a SystemDefault. If it doesn't exist already,
* a new SystemDefault with an ancient date is created for us.
*/
LastReminderDateAcquired = GetLastReminderDate(out LastReminderDate, out SystemDefaultsDR, ReadWriteTransaction);
if (!LastReminderDateAcquired)
{
TLogging.Log(
TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Could not send Partner Reminders because Petra couldn't create the required SystemDefault setting for the Last Reminder Date!");
ReadWriteTransaction.Rollback();
return;
}
try
{
Sender = new TSmtpSender();
}
catch (ESmtpSenderInitializeException e)
{
TLogging.Log(
TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": " + e.Message);
if (e.InnerException != null)
{
TLogging.Log(e.InnerException.ToString(), TLoggingType.ToLogfile);
}
return;
}
// Retrieve all PartnerReminders we need to process.
ReminderResultsDS = GetRemindersToProcess(LastReminderDate, out PartnerReminderDT,
ADataBaseObj, ReadWriteTransaction);
/*
* We now have a Typed DataTable with the PartnerReminders that we need to process.
* Iterate through the PartnerReminders, update data, and send an email for each PartnerReminder.
*/
if (TLogging.DebugLevel >= 6)
{
TLogging.Log("_---------------------------------_");
TLogging.Log("PartnerReminders data before we start processing all PartnerReminders....");
TLogging.Log(ReminderResultsDS.GetXml().ToString());
}
foreach (PPartnerReminderRow PartnerReminderDR in PartnerReminderDT.Rows)
{
if (TLogging.DebugLevel >= 4)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Processing Reminder ID {0} for Partner {1}.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
}
ReminderFreqency = (PartnerReminderDR.IsReminderFrequencyNull()) ? 0 : PartnerReminderDR.ReminderFrequency;
PartnerReminderDR.BeginEdit();
PartnerReminderDR.LastReminderSent = DateTime.Now.Date;
PartnerReminderDR.NextReminderDate = DateTime.Now.Date.AddDays(ReminderFreqency);
if (!PartnerReminderDR.IsEventDateNull()) // Reminder has an Event Date
{
if (PartnerReminderDR.NextReminderDate > PartnerReminderDR.EventDate)
{
if (TLogging.DebugLevel >= 5)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Deactivating Reminder ID {0} for Partner {1} as its Event Date is in the past.",
PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
}
PartnerReminderDR.ReminderActive = false;
}
}
if (TLogging.DebugLevel >= 4)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Sending email for Reminder ID {0} for Partner {1}.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
}
Boolean emailSentOk = false;
try
{
emailSentOk = SendReminderEmail(PartnerReminderDR, ReadWriteTransaction, Sender);
}
catch (ESmtpSenderInitializeException e) // if an exception was thrown, assume the email didn't go.
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Reminder ID {0} for Partner {1}: {2}", PartnerReminderDR.ReminderId,
PartnerReminderDR.PartnerKey,
e.Message));
if (e.InnerException != null)
{
TLogging.Log(e.InnerException.Message);
}
}
catch (ESmtpSenderSendException e)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Reminder ID {0} for Partner {1}: {2}", PartnerReminderDR.ReminderId,
PartnerReminderDR.PartnerKey,
e.Message));
if (e.InnerException != null)
{
TLogging.Log(e.InnerException.Message);
}
}
catch (Exception e)
{
TLogging.Log(e.Message);
}
if (emailSentOk)
{
// Accept the edit
if (TLogging.DebugLevel >= 4)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Reminder ID {0} for Partner {1} accepted by SMTP server.", PartnerReminderDR.ReminderId,
PartnerReminderDR.PartnerKey));
}
PartnerReminderDR.EndEdit();
}
else
{
// Cancel the edit
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Reminder ID {0} for Partner {1} REJECTED by SMTP server.", PartnerReminderDR.ReminderId,
PartnerReminderDR.PartnerKey));
PartnerReminderDR.CancelEdit();
}
}
if (TLogging.DebugLevel >= 6)
{
TLogging.Log("_---------------------------------_");
TLogging.Log("PartnerReminders data after processing all PartnerReminders, before writing it to DB....");
TLogging.Log(PartnerReminderDT.DataSet.GetXml().ToString());
}
// Update all the changed PartnerReminder Rows
PPartnerReminderAccess.SubmitChanges(PartnerReminderDT, ReadWriteTransaction);
if (TLogging.DebugLevel >= 6)
{
TLogging.Log("_---------------------------------_");
}
/*
* Update the SystemDefault that keeps track of when Partner Reminders last ran.
* (SystemDefaultsDR will point to the row we loaded earlier on, OR the row we added earlier on
* if there wasn't already a SystemDefault row.)
*/
UpdateLastReminderDate(SystemDefaultsDR, ReadWriteTransaction);
if (NewTransaction)
{
ReadWriteTransaction.Commit();
}
TLogging.LogAtLevel(1, TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + " ran succesfully.");
}
catch (Exception Exc)
{
TLogging.Log(
TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + " encountered an Exception:" + Environment.NewLine +
Exc.ToString());
if (NewTransaction)
{
ReadWriteTransaction.Rollback();
}
throw;
}
finally
{
if (Sender != null)
{
Sender.Dispose();
}
}
}
/// <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 = new TDBTransaction();
TDataBase db = DBAccess.Connect("RunSpecialTreatment");
db.ReadTransaction(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 = db.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)
PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, Transaction);
// create an extract with the given name in the parameters
int keyCount;
List <long> ignoredPartnerKeys = null;
ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys(
AParameters.Get("param_extract_name").ToString(),
AParameters.Get("param_extract_description").ToString(),
out ExtractId,
partnerkeys,
0,
AddressFilterAdded,
out keyCount,
out ignoredPartnerKeys);
});
AExtractId = ExtractId;
return(ReturnValue);
} // Run Special Treatment
/// <summary>
/// get the default bank account for this ledger
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ADataBase"></param>
public static string GetDefaultBankAccount(int ALedgerNumber, TDataBase ADataBase = null)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
#endregion Validate Arguments
string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault(
SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString());
if (BankAccountCode.Length == 0)
{
TDBTransaction readTransaction = null;
try
{
DBAccess.GetDBAccessObj(ADataBase).GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, ref readTransaction,
delegate
{
// use the first bank account
AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, readTransaction);
accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";
if (accountProperties.DefaultView.Count > 0)
{
BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
}
else
{
string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c" +
" FROM a_gift_batch " +
" WHERE a_gift_batch.a_ledger_number_i =" + ALedgerNumber +
" AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "'" +
" ORDER BY a_gift_batch.a_batch_number_i DESC" +
" LIMIT 1;";
DataTable latestAccountCode =
DBAccess.GetDBAccessObj(ADataBase).SelectDT(SQLQuery, "LatestAccountCode", readTransaction);
// use the Bank Account of the previous Gift Batch
if ((latestAccountCode != null) && (latestAccountCode.Rows.Count > 0))
{
BankAccountCode = latestAccountCode.Rows[0][AGiftBatchTable.GetBankAccountCodeDBName()].ToString(); //"a_bank_account_code_c"
}
// if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
else
{
AAccountTable accountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, readTransaction);
#region Validate Data
if ((accountTable == null) || (accountTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
DataView dv = accountTable.DefaultView;
dv.Sort = AAccountTable.GetAccountCodeDBName() + " ASC"; //a_account_code_c
dv.RowFilter = String.Format("{0} = true AND {1} = true",
AAccountTable.GetAccountActiveFlagDBName(),
AAccountTable.GetPostingStatusDBName()); // "a_account_active_flag_l = true AND a_posting_status_l = true";
DataTable sortedDT = dv.ToTable();
TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ALedgerNumber);
List <string> children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);
foreach (DataRow account in sortedDT.Rows)
{
// check if this account reports to the CASH account
if (children.Contains(account["a_account_code_c"].ToString()))
{
BankAccountCode = account["a_account_code_c"].ToString();
break;
}
}
}
}
});
}
catch (Exception ex)
{
TLogging.LogException(ex, Utilities.GetMethodSignature());
throw;
}
}
return(BankAccountCode);
}
/// <summary>
/// constructor
/// </summary>
/// <param name="parameters"></param>
/// <param name="results"></param>
/// <param name="reportStore"></param>
/// <param name="report"></param>
/// <param name="dataDB"></param>
/// <param name="depth"></param>
/// <param name="column"></param>
/// <param name="lineId"></param>
/// <param name="parentRowId"></param>
public TRptSituation(TParameterList parameters,
TResultList results,
TReportStore reportStore,
TRptReport report,
TDataBase dataDB,
int depth,
int column,
int lineId,
int parentRowId)
{
TRptSituation.RunningCode = 0;
this.Parameters = parameters;
this.Results = results;
this.ReportStore = reportStore;
this.CurrentReport = report;
this.DatabaseConnection = dataDB;
this.Depth = depth;
this.column = column;
this.LineId = lineId;
this.ParentRowId = parentRowId;
}
/// <summary>
/// Returns the next 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">Database object that can be used for querying.</param>
/// <returns>Sequence Value.</returns>
public System.Int64 GetNextSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
{
// TODO problem: sequence should be committed? separate transaction?
// see also http://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=44
// or use locking? see also http://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=50
return Convert.ToInt64(ADatabase.ExecuteScalar("SELECT NEXTVAL('" + ASequenceName + "')", ATransaction));
}
/// <summary>
/// constructor
/// </summary>
/// <param name="situation"></param>
/// <param name="depth"></param>
/// <param name="column"></param>
/// <param name="lineId"></param>
/// <param name="parentRowId"></param>
public TRptSituation(TRptSituation situation, int depth, int column, int lineId, int parentRowId)
{
this.Parameters = situation.Parameters;
this.Results = situation.Results;
this.ReportStore = situation.ReportStore;
this.CurrentReport = situation.CurrentReport;
this.DatabaseConnection = situation.DatabaseConnection;
this.Depth = depth;
this.column = column;
this.LineId = lineId;
this.ParentRowId = parentRowId;
}
/// <summary>
/// Restart a sequence with the given value.
/// </summary>
public void RestartSequence(String ASequenceName,
TDBTransaction ATransaction,
TDataBase ADatabase,
Int64 ARestartValue)
{
ADatabase.ExecuteScalar(
"SELECT pg_catalog.setval('" + ASequenceName + "', " + ARestartValue.ToString() + ", true);", ATransaction);
}
/// <summary>
/// restart a sequence with the given value has not been implemented
/// </summary>
public void RestartSequence(String ASequenceName,
TDBTransaction ATransaction,
TDataBase ADatabase,
Int64 ARestartValue)
{
// not implemented
}
/// <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>
/// Retrieves all the PartnerReminders that need to be processed.
/// </summary>
/// <param name="ALastReminderDate">Date when PartnerReminders processing last ran.</param>
/// <param name="APartnerReminderDT">PartnerReminders DataRows that need to be processed.</param>
/// <param name="ADBAccessObj">Already instatiated DB Access object with opened DB connection.</param>
/// <param name="AReadTransaction">Already instantiated DB Transaction.</param>
/// <returns>DataSet containing the <paramref name="APartnerReminderDT" /> DataTable.</returns>
private static DataSet GetRemindersToProcess(DateTime ALastReminderDate, out PPartnerReminderTable APartnerReminderDT,
TDataBase ADBAccessObj, TDBTransaction AReadTransaction)
{
List <OdbcParameter>OdbcParams;
string SQLCommand;
// Add Typed DataTable to a DataSet so we get the Partner Reminders in a Typed DataTable
APartnerReminderDT = new PPartnerReminderTable();
DataSet ReminderResultsDS = new DataSet();
ReminderResultsDS.Tables.Add(APartnerReminderDT);
SQLCommand = "SELECT * FROM PUB_p_partner_reminder WHERE ";
SQLCommand += " ( p_next_reminder_date_d > ? OR p_last_reminder_sent_d IS NULL) AND ";
SQLCommand += " p_next_reminder_date_d <= ? AND ";
SQLCommand += " p_reminder_active_l = TRUE AND ";
SQLCommand += " p_email_address_c <> ''";
OdbcParams = new List <OdbcParameter>();
// Parameter 1 = LastReminderDate from SystemDefaults
OdbcParams.Add(new OdbcParameter("LastDate", OdbcType.Date));
OdbcParams[0].Value = ALastReminderDate.Date;
// Parameter 2 = Today's date
OdbcParams.Add(new OdbcParameter("Now", OdbcType.Date));
OdbcParams[1].Value = DateTime.Now.Date;
DBAccess.GDBAccessObj.Select(ReminderResultsDS, SQLCommand, APartnerReminderDT.TableName, AReadTransaction, OdbcParams.ToArray());
// Mark the data as being 'unchanged' (rather than 'new', which it is by default)
ReminderResultsDS.AcceptChanges();
return ReminderResultsDS;
}
/// <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
ICollection <String> param_fields;
param_fields = AParameters.Get("param_fields").ToString().Split(new Char[] { ',', });
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 = ATransaction.DataBaseObj.SelectDT(SqlStmt, "partners", ATransaction,
SqlParameterList.ToArray());
// filter data by postcode (if applicable)
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
int keyCount;
List <long> ignoredPartnerKeys = null;
TExtractsHandling.ExtendExtractFromListOfPartnerKeys(
AExtractId,
partnerkeys,
0,
AddressFilterAdded,
out keyCount,
out ignoredPartnerKeys,
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 = ATransaction.DataBaseObj.SelectDT(SqlStmt, "partners", ATransaction,
SqlParameterList.ToArray());
// filter data by postcode (if applicable)
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,
out keyCount,
out ignoredPartnerKeys,
false);
ReturnValue = true;
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_consent_history`.*, " +
"GROUP_CONCAT(`p_consent_history_permission`.`p_purpose_code_c` SEPARATOR ',') AS `AllowedPurposes` " +
"FROM `p_consent_history` " +
"LEFT JOIN `p_consent_history_permission` " +
"ON `p_consent_history`.`p_entry_id_i` = `p_consent_history_permission`.`p_consent_history_entry_i` " +
"WHERE `p_consent_history`.`p_partner_key_n` = ? " +
"AND `p_consent_history`.`p_type_c` = ? " +
"GROUP BY `p_consent_history`.`p_entry_id_i` " +
"ORDER BY `p_consent_history`.`p_entry_id_i` DESC " +
"LIMIT 1";
SQLParameter.Add(new OdbcParameter("PartnerKey", OdbcType.BigInt)
{
Value = APartnerKey
});
SQLParameter.Add(new OdbcParameter("DataType", OdbcType.VarChar)
{
Value = ADataType
});
DB.SelectDT(Set.PConsentHistory, sql, T, SQLParameter.ToArray());
if (Set.PConsentHistory.Count == 0)
{
// there is no consent yet
// do we have a value at all?
List <string> Subscriptions;
List <string> PartnerTypes;
string DefaultEmailAddress;
string DefaultPhoneMobile;
string DefaultPhoneLandline;
PartnerEditTDS PartnerDS = TSimplePartnerEditWebConnector.GetPartnerDetails(APartnerKey,
out Subscriptions,
out PartnerTypes,
out DefaultEmailAddress,
out DefaultPhoneMobile,
out DefaultPhoneLandline);
if (ADataType == MPartnerConstants.CONSENT_TYPE_ADDRESS)
{
// what about new contact?
PLocationRow locationRow = null;
if (PartnerDS.PLocation.Rows.Count > 0)
{
locationRow = PartnerDS.PLocation[0];
}
else
{
locationRow = PartnerDS.PLocation.NewRowTyped();
}
PConsentHistoryRow row = Set.PConsentHistory.NewRowTyped();
row.EntryId = -1;
row.PartnerKey = APartnerKey;
row.Type = ADataType;
row.Value = locationRow.StreetName + ", " + locationRow.PostalCode + " " + locationRow.City + ", " + locationRow.CountryCode;
row.ConsentDate = DateTime.Today;
Set.PConsentHistory.Rows.Add(row);
}
if (ADataType == MPartnerConstants.CONSENT_TYPE_EMAIL)
{
PConsentHistoryRow row = Set.PConsentHistory.NewRowTyped();
row.EntryId = -1;
row.PartnerKey = APartnerKey;
row.Type = ADataType;
row.Value = DefaultEmailAddress;
row.ConsentDate = DateTime.Today;
Set.PConsentHistory.Rows.Add(row);
}
if (ADataType == MPartnerConstants.CONSENT_TYPE_LANDLINE)
{
PConsentHistoryRow row = Set.PConsentHistory.NewRowTyped();
row.EntryId = -1;
row.PartnerKey = APartnerKey;
row.Type = ADataType;
row.Value = DefaultPhoneLandline;
row.ConsentDate = DateTime.Today;
Set.PConsentHistory.Rows.Add(row);
}
if (ADataType == MPartnerConstants.CONSENT_TYPE_MOBILE)
{
PConsentHistoryRow row = Set.PConsentHistory.NewRowTyped();
row.EntryId = -1;
row.PartnerKey = APartnerKey;
row.Type = ADataType;
row.Value = DefaultPhoneMobile;
row.ConsentDate = DateTime.Today;
Set.PConsentHistory.Rows.Add(row);
}
}
PConsentChannelAccess.LoadAll(Set, T);
PConsentPurposeAccess.LoadAll(Set, T);
});
return(Set);
}
/// <summary>
/// constructor
/// </summary>
/// <param name="situation"></param>
public TRptSituation(TRptSituation situation)
{
this.Parameters = situation.Parameters;
this.Results = situation.Results;
this.ReportStore = situation.ReportStore;
this.CurrentReport = situation.CurrentReport;
this.DatabaseConnection = situation.DatabaseConnection;
this.Depth = situation.Depth;
this.column = situation.column;
this.LineId = situation.LineId;
this.ParentRowId = situation.ParentRowId;
}
/// <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
ICollection <String> param_fields;
param_fields = AParameters.Get("param_fields").ToString().Split(new Char[] { ',', });
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 = ATransaction.DataBaseObj.SelectDT(SqlStmt, "partners", ATransaction,
SqlParameterList.ToArray());
// filter data by postcode (if applicable)
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
int keyCount;
List <long> ignoredPartnerKeys = null;
ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys(
AParameters.Get("param_extract_name").ToString(),
AParameters.Get("param_extract_description").ToString(),
out AExtractId,
partnerkeys,
0,
AddressFilterAdded,
out keyCount,
out ignoredPartnerKeys);
return(ReturnValue);
}
/// <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;
}
/// <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)
{
TDBTransaction transaction = DBAccess.GDBAccessObj.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;
DBAccess.GDBAccessObj.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();
DBAccess.GDBAccessObj.Select(AMainDS, stmt, AMainDS.PBankingDetails.TableName, transaction, parameters);
DBAccess.GDBAccessObj.RollbackTransaction();
return(true);
}
/// <summary>
/// init the exchange rate, to avoid messages "Cannot find exchange rate for EUR USD"
/// </summary>
public static void InitExchangeRate(TDataBase ADataBase = null)
{
TDataBase db = DBAccess.Connect("InitExchangeRate", ADataBase);
TDBTransaction Transaction = new TDBTransaction();
bool SubmitOK = false;
db.WriteTransaction(ref Transaction,
ref SubmitOK,
delegate
{
TAccountPeriodInfo AccountingPeriodInfo =
new TAccountPeriodInfo(FLedgerNumber, 1);
ADailyExchangeRateTable dailyrates = new ADailyExchangeRateTable();
ADailyExchangeRateRow row = dailyrates.NewRowTyped(true);
row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
row.TimeEffectiveFrom = 100;
row.FromCurrencyCode = "USD";
row.ToCurrencyCode = "EUR";
row.RateOfExchange = 1.34m;
dailyrates.Rows.Add(row);
row = dailyrates.NewRowTyped(true);
row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
row.TimeEffectiveFrom = 100;
row.FromCurrencyCode = "USD";
row.ToCurrencyCode = "GBP";
row.RateOfExchange = 1.57m;
dailyrates.Rows.Add(row);
if (!ADailyExchangeRateAccess.Exists(row.FromCurrencyCode, row.ToCurrencyCode, row.DateEffectiveFrom, row.TimeEffectiveFrom, Transaction))
{
ADailyExchangeRateAccess.SubmitChanges(dailyrates, Transaction);
}
ALedgerTable Ledger = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, Transaction);
for (int periodCounter = 1; periodCounter <= Ledger[0].NumberOfAccountingPeriods + Ledger[0].NumberFwdPostingPeriods; periodCounter++)
{
AccountingPeriodInfo = new TAccountPeriodInfo(FLedgerNumber, periodCounter);
ACorporateExchangeRateTable corprates = new ACorporateExchangeRateTable();
ACorporateExchangeRateRow corprow = corprates.NewRowTyped(true);
corprow.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
corprow.TimeEffectiveFrom = 100;
corprow.FromCurrencyCode = "USD";
corprow.ToCurrencyCode = "EUR";
corprow.RateOfExchange = 1.34m;
corprates.Rows.Add(corprow);
corprow = corprates.NewRowTyped(true);
corprow.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
corprow.TimeEffectiveFrom = 100;
corprow.FromCurrencyCode = "USD";
corprow.ToCurrencyCode = "GBP";
corprow.RateOfExchange = 1.57m;
corprates.Rows.Add(corprow);
if (!ACorporateExchangeRateAccess.Exists(corprow.FromCurrencyCode, corprow.ToCurrencyCode, corprow.DateEffectiveFrom, Transaction))
{
ACorporateExchangeRateAccess.SubmitChanges(corprates, Transaction);
}
}
SubmitOK = true;
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
}
/// <summary>
/// Populate ledger with gifts and invoices, post batches, close periods and years, according to FNumberOfClosedPeriods
/// </summary>
public static void PopulateData(string datadirectory, bool smallNumber = false)
{
int periodOverall = 0;
int yearCounter = 0;
int period = 1;
int YearAD = DateTime.Today.Year - (FNumberOfClosedPeriods / 12);
SampleDataGiftBatches.FLedgerNumber = FLedgerNumber;
SampleDataAccountsPayable.FLedgerNumber = FLedgerNumber;
SampleDataGiftBatches.LoadBatches(Path.Combine(datadirectory, "donations.csv"), smallNumber);
SampleDataAccountsPayable.GenerateInvoices(Path.Combine(datadirectory, "invoices.csv"), YearAD, smallNumber);
TDataBase db = DBAccess.Connect("GenerateLedger");
while (periodOverall <= FNumberOfClosedPeriods)
{
TLogging.LogAtLevel(1, "working on year " + yearCounter.ToString() + " / period " + period.ToString());
SampleDataGiftBatches.CreateGiftBatches(period);
SampleDataLedger.InitExchangeRate(db);
if (!SampleDataGiftBatches.PostBatches(yearCounter, period, (periodOverall == FNumberOfClosedPeriods) ? 1 : 0, db))
{
throw new Exception("could not post gift batches");
}
if (!SampleDataAccountsPayable.PostAndPayInvoices(yearCounter, period, (periodOverall == FNumberOfClosedPeriods) ? 1 : 0))
{
throw new Exception("could not post invoices");
}
TLedgerInfo LedgerInfo = new TLedgerInfo(FLedgerNumber, db);
if (periodOverall < FNumberOfClosedPeriods)
{
TAccountPeriodInfo AccountingPeriodInfo =
new TAccountPeriodInfo(FLedgerNumber, period);
TLogging.Log("closing period at " + AccountingPeriodInfo.PeriodEndDate.ToShortDateString());
// run month end
TMonthEnd MonthEndOperator = new TMonthEnd(db, LedgerInfo);
MonthEndOperator.SetNextPeriod(null);
if (period == 12)
{
TDBTransaction transaction = new TDBTransaction();
bool SubmissionOK = false;
db.WriteTransaction(
ref transaction,
ref SubmissionOK,
delegate
{
TYearEnd YearEndOperator = new TYearEnd(LedgerInfo);
// run year end
TVerificationResultCollection verificationResult = new TVerificationResultCollection();
List <Int32> glBatches = new List <int>();
TReallocation reallocation = new TReallocation(LedgerInfo, glBatches, transaction);
reallocation.VerificationResultCollection = verificationResult;
reallocation.IsInInfoMode = false;
reallocation.RunOperation();
TGlmNewYearInit glmNewYearInit = new TGlmNewYearInit(LedgerInfo, yearCounter, YearEndOperator, transaction);
glmNewYearInit.VerificationResultCollection = verificationResult;
glmNewYearInit.IsInInfoMode = false;
glmNewYearInit.RunOperation();
YearEndOperator.SetNextPeriod(transaction);
SampleDataLedger.InitExchangeRate(db);
YearAD++;
yearCounter++;
SampleDataAccountsPayable.GenerateInvoices(Path.Combine(datadirectory, "invoices.csv"), YearAD, smallNumber, db);
period = 0;
SubmissionOK = true;
});
}
}
period++;
periodOverall++;
}
}
/// <summary>
/// Gets called in regular intervals from a Timer in Class TTimedProcessing.
/// </summary>
/// <param name="ADBAccessObj">Already instatiated DB Access object with opened DB connection.</param>
/// <param name="ARunManually">this is true if the process was called manually from the server admin console</param>
public static void Process(TDataBase ADBAccessObj, bool ARunManually)
{
TDBTransaction ReadWriteTransaction;
bool NewTransaction;
bool LastReminderDateAcquired;
DateTime LastReminderDate;
DataSet ReminderResultsDS;
SSystemDefaultsRow SystemDefaultsDR;
PPartnerReminderTable PartnerReminderDT;
int ReminderFreqency;
TDataBase DBAccessObj;
if (TLogging.DebugLevel >= 6)
{
TLogging.Log("Entering TProcessPartnerReminders.Process...");
}
// TODO: it is quite ipossible at the moment to use ADBAccessObj instead of DBAccess.GDBAccessObj due to SubmitChanges etc
//DBAccessObj = ADBAccessObj;
DBAccessObj = DBAccess.GDBAccessObj;
// SubmitChanges references a user
TPetraIdentity PetraIdentity = new TPetraIdentity(
"SYSADMIN", "", "", "", "", DateTime.MinValue,
DateTime.MinValue, DateTime.MinValue, 0, -1, -1, false,
false);
UserInfo.GUserInfo = new TPetraPrincipal(PetraIdentity, null);
ReadWriteTransaction = DBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
out NewTransaction);
/*
* This whole process must either succeed or fail, therefore the whole thing is in a try-catch.
*/
try
{
/*
* Obtain date when PartnerReminders last ran. This is stored in a SystemDefault. If it doesn't exist already,
* a new SystemDefault with an ancient date is created for us.
*/
LastReminderDateAcquired = GetLastReminderDate(out LastReminderDate, out SystemDefaultsDR, ReadWriteTransaction);
if (!LastReminderDateAcquired)
{
TLogging.Log(
TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Could not send Partner Reminders because Petra couldn't create the required SystemDefault setting for the Last Reminder Date!");
DBAccessObj.RollbackTransaction();
return;
}
TSmtpSender Sender = new TSmtpSender();
if (!Sender.FInitOk)
{
TLogging.Log(
TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Could not send Partner Reminders because SMTP server didn't initialise.");
DBAccessObj.RollbackTransaction();
return;
}
// Retrieve all PartnerReminders we need to process.
ReminderResultsDS = GetRemindersToProcess(LastReminderDate, out PartnerReminderDT,
DBAccessObj, ReadWriteTransaction);
/*
* We now have a Typed DataTable with the PartnerReminders that we need to process.
* Iterate through the PartnerReminders, update data, and send an email for each PartnerReminder.
*/
if (TLogging.DebugLevel >= 6)
{
TLogging.Log("_---------------------------------_");
TLogging.Log("PartnerReminders data before we start processing all PartnerReminders....");
TLogging.Log(ReminderResultsDS.GetXml().ToString());
}
foreach (PPartnerReminderRow PartnerReminderDR in PartnerReminderDT.Rows)
{
if (TLogging.DebugLevel >= 4)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Processing Reminder ID {0} for Partner {1}.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
}
ReminderFreqency = (PartnerReminderDR.IsReminderFrequencyNull()) ? 0 : PartnerReminderDR.ReminderFrequency;
PartnerReminderDR.BeginEdit();
PartnerReminderDR.LastReminderSent = DateTime.Now.Date;
PartnerReminderDR.NextReminderDate = DateTime.Now.Date.AddDays(ReminderFreqency);
if (!PartnerReminderDR.IsEventDateNull()) // Reminder has an Event Date
{
if (PartnerReminderDR.NextReminderDate > PartnerReminderDR.EventDate)
{
if (TLogging.DebugLevel >= 5)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Deactivating Reminder ID {0} for Partner {1} as its Event Date is in the past.",
PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
}
PartnerReminderDR.ReminderActive = false;
}
}
if (TLogging.DebugLevel >= 4)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Sending email for Reminder ID {0} for Partner {1}.", PartnerReminderDR.ReminderId, PartnerReminderDR.PartnerKey));
}
if (SendReminderEmail(PartnerReminderDR, ReadWriteTransaction, Sender))
{
// Accept the edit
if (TLogging.DebugLevel >= 4)
{
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Reminder ID {0} for Partner {1} accepted by SMTP server.", PartnerReminderDR.ReminderId,
PartnerReminderDR.PartnerKey));
}
PartnerReminderDR.EndEdit();
}
else
{
// Cancel the edit
TLogging.Log(String.Format(TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing +
": Reminder ID {0} for Partner {1} REJECTED by SMTP server.", PartnerReminderDR.ReminderId,
PartnerReminderDR.PartnerKey));
PartnerReminderDR.CancelEdit();
}
}
if (TLogging.DebugLevel >= 6)
{
TLogging.Log("_---------------------------------_");
TLogging.Log("PartnerReminders data after processing all PartnerReminders, before writing it to DB....");
TLogging.Log(PartnerReminderDT.DataSet.GetXml().ToString());
}
// Update all the changed PartnerReminder Rows
PPartnerReminderAccess.SubmitChanges(PartnerReminderDT, ReadWriteTransaction);
if (TLogging.DebugLevel >= 6)
{
TLogging.Log("_---------------------------------_");
}
/*
* Update the SystemDefault that keeps track of when Partner Reminders last ran.
* (SystemDefaultsDR will point to the row we loaded earlier on, OR the row we added earlier on
* if there wasn't already a SystemDefault row.)
*/
UpdateLastReminderDate(SystemDefaultsDR, ReadWriteTransaction);
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
TLogging.LogAtLevel(1, TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + " ran succesfully.");
}
catch (Exception Exc)
{
TLogging.Log(
TTimedProcessing.StrAutomaticProcessing + StrRemindersProcessing + " encountered an Exception:" + Environment.NewLine +
Exc.ToString());
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
throw;
}
}
/// <summary>
/// Returns the next 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">Database object that can be used for querying.</param>
/// <returns>Sequence Value.</returns>
public System.Int64 GetNextSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
{
string stmt = "INSERT INTO " + ASequenceName + " VALUES(NULL, -1);";
using (SqliteCommand cmd = new SqliteCommand(stmt, (SqliteConnection)ATransaction.Connection))
{
cmd.ExecuteNonQuery();
}
return(GetCurrentSequenceValue(ASequenceName, ATransaction, ADatabase));
}
/// <summary>
/// todoComment
/// </summary>
/// <param name="databaseConnection">The database connection.</param>
/// <param name="pv_ledger_number_i">The pv_ledger_number_i.</param>
/// <param name="pv_year_i">The pv_year_i.</param>
/// <param name="pv_period_i">The pv_period_i.</param>
/// <param name="currentFinancialYear">The current financial year.</param>
/// <returns></returns>
public decimal GetCorporateExchangeRate(TDataBase databaseConnection,
int pv_ledger_number_i,
int pv_year_i,
int pv_period_i,
int currentFinancialYear)
{
if (pv_period_i == 0) // I sometimes get asked for this. There's no period 0.
{
//TODO: Calendar vs Financial Date Handling - Need to confirm this below
pv_period_i = 12; // Perhaps I should look up this value from number of periods?
pv_year_i--;
}
foreach (TExchangeRate exchangeRateElement in exchangeRates)
{
if ((exchangeRateElement.ledger_number_i == pv_ledger_number_i) && (exchangeRateElement.year_i == pv_year_i)
&& (exchangeRateElement.period_i == pv_period_i))
{
return exchangeRateElement.rate_n;
}
}
decimal ReturnValue = GetCorporateExchangeRateFromDB(databaseConnection, pv_ledger_number_i, pv_year_i, pv_period_i, currentFinancialYear);
//
// Cache this for the next time I'm asked...
TExchangeRate exchangeRateElement2 = new TExchangeRate();
exchangeRateElement2.ledger_number_i = pv_ledger_number_i;
exchangeRateElement2.year_i = pv_year_i;
exchangeRateElement2.period_i = pv_period_i;
exchangeRateElement2.rate_n = ReturnValue;
exchangeRates.Add(exchangeRateElement2);
return ReturnValue;
}
/// <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">Database object that can be used for querying.</param>
/// <returns>Sequence Value.</returns>
public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
{
string stmt = "SELECT MAX(sequence) FROM " + ASequenceName + ";";
using (SqliteCommand cmd = new SqliteCommand(stmt, (SqliteConnection)ATransaction.Connection))
{
return(Convert.ToInt64(cmd.ExecuteScalar()));
}
}
/// <summary>
/// Returns the next 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">Database object that can be used for querying.</param>
/// <returns>Sequence Value.</returns>
public System.Int64 GetNextSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
{
string stmt = "INSERT INTO " + ASequenceName + " VALUES(NULL, -1);";
using (SqliteCommand cmd = new SqliteCommand(stmt, (SqliteConnection)ATransaction.Connection))
{
cmd.ExecuteNonQuery();
}
return GetCurrentSequenceValue(ASequenceName, ATransaction, ADatabase);
}
public static void ExportGifts(int ALedgerNumber,
string ACostCentre,
string AAcctCode,
string AMonthName,
int APeriodNumber,
DateTime APeriodStartDate,
DateTime APeriodEndDate,
string ACurrencySelect,
int AIchNumber,
ref DataTable AExportDataTable,
ref TVerificationResultCollection AVerificationResult)
{
/* Define local variables */
bool FirstLoopFlag = true;
Int32 LastRecipKey = 0; //FORMAT "9999999999"
string LastGroup = string.Empty;
string LastDetail = string.Empty;
string LastDetailDesc = string.Empty; //FORMAT "X(15)"
// string CurrentYearTotals = string.Empty;
decimal IndividualDebitTotal = 0; //FORMAT "->>>,>>>,>>>,>>9.99"
decimal IndividualCreditTotal = 0; //FORMAT "->>>,>>>,>>>,>>9.99"
string ExportDescription = string.Empty;
Int32 tmpLastRecipKey = 0;
string tmpLastGroup = string.Empty;
string tmpLastDetail = string.Empty;
//Export Gifts gi3200-1.i
//Find and total each gift transaction
string SQLStmt = TDataBase.ReadSqlFile("ICH.HOSAExportGiftsInner.sql");
//Begin the transaction
bool NewTransaction = false;
TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
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);
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
/// <summary>
/// Restart a sequence with the given value.
/// </summary>
public void RestartSequence(String ASequenceName,
TDBTransaction ATransaction,
TDataBase ADatabase,
Int64 ARestartValue)
{
ADatabase.ExecuteNonQuery("DELETE FROM " + ASequenceName + ";", ATransaction);
ADatabase.ExecuteNonQuery("INSERT INTO " + ASequenceName + " VALUES(" + ARestartValue.ToString() + ", -1);", ATransaction);
}
public static bool GenerateHOSAFiles(int ALedgerNumber,
int APeriodNumber,
int AIchNumber,
string ACostCentre,
String ACurrencySelect,
string AFileName,
out TVerificationResultCollection AVerificationResult
)
{
bool Successful = false;
AVerificationResult = new TVerificationResultCollection();
//Begin the transaction
bool NewTransaction = false;
TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
try
{
GLBatchTDS MainDS = new GLBatchTDS();
//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,
ref TableForExport,
ref AVerificationResult);
}
/* 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
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 AVerificationResult);
/* Change number format back */
//TODO
Successful = true;
}
catch (Exception e)
{
TLogging.Log(e.ToString());
}
// rollback the reading transaction
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(Successful);
}
/// <summary>
/// manage database connections for the ASP webclient
/// </summary>
/// <param name="ADatabaseConnection"></param>
public void AddDBConnection(TDataBase ADatabaseConnection)
{
if (!FDBConnections.Contains(ADatabaseConnection))
{
FDBConnections.Add(ADatabaseConnection);
}
}
/// <summary>
/// constructor
/// </summary>
/// <param name="databaseConnection"></param>
/// <param name="period"></param>
/// <param name="year"></param>
/// <param name="AFinancialPeriod"></param>
public TFinancialPeriod(TDataBase databaseConnection, int period, int year, TFinancialPeriod AFinancialPeriod) :
this(databaseConnection, period, year, AFinancialPeriod.diffPeriod, AFinancialPeriod.FCurrentFinancialYear,
AFinancialPeriod.FCurrentPeriod, AFinancialPeriod.FNumberAccountingPeriods, AFinancialPeriod.FNumberForwardingPeriods,
AFinancialPeriod.realGlmSequence)
{
}
