private void ShowDetailsManual(AMotivationDetailRow ARow)
{
string FeesPayable = string.Empty;
string FeesReceivable = string.Empty;
if (ARow != null)
{
FMainDS.AMotivationDetailFee.DefaultView.RowFilter =
String.Format("{0}={1} and {2}='{3}' and {4}='{5}'",
AMotivationDetailFeeTable.GetLedgerNumberDBName(),
ARow.LedgerNumber,
AMotivationDetailFeeTable.GetMotivationGroupCodeDBName(),
ARow.MotivationGroupCode,
AMotivationDetailFeeTable.GetMotivationDetailCodeDBName(),
ARow.MotivationDetailCode);
foreach (DataRowView rv in FMainDS.AMotivationDetailFee.DefaultView)
{
AMotivationDetailFeeRow detailFeeRow = (AMotivationDetailFeeRow)rv.Row;
if (StringHelper.StrSplit(clbDetailFeesPayable.GetAllStringList(), ",").Contains(detailFeeRow.FeeCode))
{
FeesPayable = StringHelper.AddCSV(FeesPayable, detailFeeRow.FeeCode);
}
else
{
FeesReceivable = StringHelper.AddCSV(FeesReceivable, detailFeeRow.FeeCode);
}
}
}
// set the ORDER column to true if row is checked
clbDetailFeesPayable.CheckedColumn = "ORDER";
clbDetailFeesReceivable.CheckedColumn = "ORDER";
clbDetailFeesPayable.SetCheckedStringList(FeesPayable);
clbDetailFeesReceivable.SetCheckedStringList(FeesReceivable);
// set the CHECKED column to true if row is checked
clbDetailFeesPayable.CheckedColumn = "CHECKED";
clbDetailFeesReceivable.CheckedColumn = "CHECKED";
clbDetailFeesPayable.SetCheckedStringList(FeesPayable);
clbDetailFeesReceivable.SetCheckedStringList(FeesReceivable);
if (FTaxDeductiblePercentageEnabled)
{
if (ARow.IsTaxDeductibleAccountNull())
{
cmbDeductibleAccountCode.SelectedIndex = 0;
}
else
{
cmbDeductibleAccountCode.SetSelectedString(ARow.TaxDeductibleAccount);
}
}
}
/// <summary>
/// need to tell the client that there are detail reports available
/// </summary>
/// <returns>void</returns>
protected void InitDetailReports()
{
int Counter = 0;
String detailReportCSV;
// remove all param_detail_report_ parameters first
while (Parameters.Exists("param_detail_report_" + Counter.ToString()) == true)
{
Parameters.RemoveVariable("param_detail_report_" + Counter.ToString());
Counter++;
}
Counter = 0;
if ((CurrentReport != null) && (CurrentReport.rptGrpDetailReport != null))
{
foreach (TRptDetailReport detailReport in CurrentReport.rptGrpDetailReport)
{
detailReportCSV = "";
detailReportCSV = StringHelper.AddCSV(detailReportCSV, detailReport.strId, ",");
detailReportCSV = StringHelper.AddCSV(detailReportCSV, detailReport.strAction, ",");
detailReportCSV = StringHelper.AddCSV(detailReportCSV, detailReport.strQuery, ",");
if (detailReport.rptGrpParameter != null)
{
foreach (TRptParameter parameter in detailReport.rptGrpParameter)
{
detailReportCSV = StringHelper.AddCSV(detailReportCSV, parameter.strName, ",");
detailReportCSV = StringHelper.AddCSV(detailReportCSV, parameter.strValue, ",");
}
}
Parameters.Add("param_detail_report_" + Counter.ToString(), detailReportCSV);
Counter++;
}
}
// add a detail report for each partnerkey column
// use columnFormat, because looking at the format "eInteger:partnerkey:" of the values of a row is not so easy
for (int ColCounter = 0; ColCounter <= Parameters.Get("MaxDisplayColumns").ToInt() - 1; ColCounter++)
{
if (Parameters.Get("ColumnFormat", ColCounter).ToString().ToLower() == "partnerkey")
{
detailReportCSV = "";
detailReportCSV = StringHelper.AddCSV(detailReportCSV, "Open \"" + Parameters.Get("ColumnCaption",
ColCounter).ToString() + "\" in Partner Edit Screen", ",");
detailReportCSV = StringHelper.AddCSV(detailReportCSV, "PartnerEditScreen", ",");
detailReportCSV = StringHelper.AddCSV(detailReportCSV, ColCounter.ToString(), ",");
Parameters.Add("param_detail_report_" + Counter.ToString(), detailReportCSV);
Counter++;
}
}
}
/// <summary>
/// format the XML into CSV so that it can be opened as a spreadsheet;
/// this only works for quite simple files;
/// hierarchical structures are flattened (using childOf column)
/// </summary>
public static string Xml2CsvString(XmlDocument ADoc)
{
// first write the header of the csv file
List <string> AllAttributes = new List <string>();
List <XmlNode> AllNodes = new List <XmlNode>();
GetAllAttributesAndNodes(ADoc.DocumentElement, ref AllAttributes, ref AllNodes);
string separator = TAppSettingsManager.GetValue("CSVSeparator",
System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator);
string headerLine = "";
foreach (string attrName in AllAttributes)
{
if (headerLine.Length > 0)
{
headerLine += separator;
}
headerLine += "\"" + attrName + "\"";
}
string result = headerLine + Environment.NewLine;
foreach (XmlNode node in AllNodes)
{
string line = "";
foreach (string attrName in AllAttributes)
{
if (attrName == "childOf")
{
line = StringHelper.AddCSV(line, TXMLParser.GetAttribute(node.ParentNode, "name"), separator);
}
else
{
line = StringHelper.AddCSV(line, TXMLParser.GetAttribute(node, attrName), separator);
}
}
result += line + Environment.NewLine;
}
return(result);
}
/// <summary>
/// Retrieve an XML attribute. Empty if attribute is not present.
/// Check the parent nodes, if the current node does not have that attribute
/// </summary>
/// <param name="cur">the current node</param>
/// <param name="attrib">the name of the attribute</param>
/// <param name="AInheritAttributes">return all the values of the parent nodes as well</param>
/// <returns>the value of the attribute, or the CSV list of attribute values of the parents and the node,
/// or an empty string if the attribute is not existing
/// </returns>
public static string GetAttributeRecursive(XmlNode cur, string attrib, bool AInheritAttributes)
{
string ReturnValue = "";
bool HasAttrib = HasAttribute(cur, attrib);
if ((!HasAttrib || AInheritAttributes) && (cur.ParentNode != null))
{
ReturnValue = GetAttributeRecursive(cur.ParentNode, attrib, AInheritAttributes);
}
if (HasAttrib)
{
ReturnValue = StringHelper.AddCSV(ReturnValue, GetAttribute(cur, attrib));
}
return(ReturnValue);
}
private static void MarkTransactionMatched(
BankImportTDS AMainDS,
BankImportTDSAEpTransactionRow transactionRow,
BankImportTDSAGiftDetailRow giftDetail)
{
giftDetail.AlreadyMatched = true;
if (giftDetail.RecipientDescription.Length == 0)
{
giftDetail.RecipientDescription = giftDetail.MotivationGroupCode + "/" + giftDetail.MotivationDetailCode;
}
transactionRow.MatchAction = Ict.Petra.Shared.MFinance.MFinanceConstants.BANK_STMT_STATUS_MATCHED;
transactionRow.GiftLedgerNumber = giftDetail.LedgerNumber;
transactionRow.GiftBatchNumber = giftDetail.BatchNumber;
transactionRow.GiftTransactionNumber = giftDetail.GiftTransactionNumber;
transactionRow.GiftDetailNumbers = StringHelper.AddCSV(transactionRow.GiftDetailNumbers, giftDetail.DetailNumber.ToString(), ",");
transactionRow.DonorKey = giftDetail.DonorKey;
}
/// <summary>
/// This function returns the comma separated list of all row,
/// identified by their codes (using FKeyColumns)
///
/// </summary>
/// <returns>String</returns>
public String GetAllStringList()
{
String ReturnValue;
ReturnValue = "";
if (FDataView != null)
{
foreach (DataRowView Row in FDataView)
{
// notice: the value in the string list might be in pairs, comma separated; addCSV will put quotes around it
// eg. motivation group and detail
foreach (String KeyColumn in FKeyColumns)
{
ReturnValue = StringHelper.AddCSV(ReturnValue, Row[KeyColumn].ToString());
}
}
}
return(ReturnValue);
}
private void ShowDetailsManual(SUserRow ARow)
{
string currentPermissions = String.Empty;
if (ARow != null)
{
FMainDS.SUserModuleAccessPermission.DefaultView.RowFilter =
String.Format("{0}='{1}'",
SUserModuleAccessPermissionTable.GetUserIdDBName(),
ARow.UserId);
foreach (DataRowView rv in FMainDS.SUserModuleAccessPermission.DefaultView)
{
SUserModuleAccessPermissionRow permission = (SUserModuleAccessPermissionRow)rv.Row;
if (permission.CanAccess)
{
currentPermissions = StringHelper.AddCSV(currentPermissions, permission.ModuleId);
}
}
// If a password has been saved for a user it can be changed using btnChangePassword.
// If a password has not been saved then it can be added using txtDetailPasswordHash.
if (string.IsNullOrEmpty(ARow.PasswordHash) || (string.IsNullOrEmpty(ARow.PasswordSalt) && (ARow.RowState != DataRowState.Unchanged)))
{
btnChangePassword.Enabled = false;
txtDetailPasswordHash.Enabled = true;
}
else
{
btnChangePassword.Enabled = true;
txtDetailPasswordHash.Enabled = false;
}
}
clbUserGroup.SetCheckedStringList(currentPermissions);
}
/// load data from csv files and sql statements
public static bool LoadData(string ADatabaseName, string AUsername, string APassword, string ALoadSQLFileName)
{
StreamReader sr = null;
DBAccess.GDBAccessObj = new TDataBase(TDBType.MySQL);
try
{
DBAccess.GDBAccessObj.EstablishDBConnection(TDBType.MySQL, "localhost", "", ADatabaseName, AUsername, APassword, "");
sr = new StreamReader(ALoadSQLFileName);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
return(false);
}
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction();
// one command per line.
// file is in postgresql syntax
// either COPY FROM or INSERT
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
if (line.Trim().ToUpper().StartsWith("INSERT"))
{
DBAccess.GDBAccessObj.ExecuteNonQuery(line, Transaction);
}
else if (line.Trim().ToUpper().StartsWith("COPY"))
{
// pgsql: COPY p_language FROM 'c:/p_language.csv' WITH DELIMITER AS ',' NULL AS '?' CSV QUOTE AS '"' ESCAPE AS '"';
// mysql: LOAD DATA LOCAL INFILE 'c:/p_language.csv' INTO TABLE p_language FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"';
// need to fix the NULL value from ? to NULL
string DataFilename = line.Substring(line.IndexOf("'") + 1);
DataFilename = DataFilename.Substring(0, DataFilename.IndexOf("'"));
string TableName = line.Substring(line.IndexOf("COPY ") + 5);
TableName = TableName.Substring(0, TableName.IndexOf(" "));
StreamReader sData = new StreamReader(DataFilename);
StreamWriter sDataWriter = new StreamWriter(DataFilename + ".local");
bool firstRow = true;
while (!sData.EndOfStream)
{
string CSVDataQuestionMark = sData.ReadLine().Trim();
string CSVDataNULL = string.Empty;
while (CSVDataQuestionMark.Length > 0)
{
bool quotedValue = CSVDataQuestionMark.StartsWith("\"");
string value = StringHelper.GetNextCSV(ref CSVDataQuestionMark, ",");
if (value == "?")
{
value = "NULL";
}
// if true or false is written in quotes, do not convert to integer. needed for a_account_property
if ((!quotedValue && (value == "false")) || (value == "no"))
{
value = "0";
}
if ((!quotedValue && (value == "true")) || (value == "yes"))
{
value = "1";
}
CSVDataNULL = StringHelper.AddCSV(CSVDataNULL, value);
}
if (CSVDataNULL.Length > 0)
{
if (firstRow)
{
firstRow = false;
}
else
{
sDataWriter.WriteLine();
}
sDataWriter.Write(CSVDataNULL);
}
}
sData.Close();
sDataWriter.Close();
// see also http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html
string stmt = String.Format(
"LOAD DATA LOCAL INFILE '{0}' INTO TABLE {1} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '"
+
Environment.NewLine + "';",
DataFilename + ".local",
TableName);
DBAccess.GDBAccessObj.ExecuteNonQuery(stmt, Transaction);
}
}
sr.Close();
DBAccess.GDBAccessObj.CommitTransaction();
DBAccess.GDBAccessObj.CloseDBConnection();
return(true);
}
/// <summary>
/// export all GL Transactions in the given year, towards the specified cost centres
/// </summary>
public static void ExportGLTransactions(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
Int32 AFinancialYear,
string ACostCentres,
string AIgnoreAccounts,
string AIgnoreReferences,
ref List <string> ACostCentresInvolved,
ref List <string> AAccountsInvolved)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "transaction.csv"));
Console.WriteLine("Writing file: " + filename);
TDBTransaction Transaction = new TDBTransaction();
ATransactionTable transactions = new ATransactionTable();
ATransAnalAttribTable TransAnalAttrib = new ATransAnalAttribTable();
ATransactionTable allTransactionsInJournal = new ATransactionTable();
AGiftBatchTable giftbatches = new AGiftBatchTable();
AAccountTable accounts = new AAccountTable();
DBAccess.ReadTransaction(ref Transaction,
delegate
{
TDataBase db = Transaction.DataBaseObj;
string sql =
String.Format("SELECT T.*, B.{4} AS a_transaction_date_d " +
"FROM PUB_{8} AS B, PUB_{7} AS T " +
"WHERE B.{9} = {10} AND B.{15} = {16} AND B.{11}='{12}' " +
"AND T.{9} = B.{9} AND T.{0} = B.{0} " +
"AND T.{13} IN ({14}) " +
"AND NOT T.{17} IN ({19}) " +
"AND NOT T.{20} IN ({21}) " +
"ORDER BY {0}, {1}, {2}",
ATransactionTable.GetBatchNumberDBName(),
ATransactionTable.GetJournalNumberDBName(),
ATransactionTable.GetTransactionNumberDBName(),
ATransactionTable.GetTransactionAmountDBName(),
ABatchTable.GetDateEffectiveDBName(),
ATransactionTable.GetNarrativeDBName(),
ATransactionTable.GetReferenceDBName(),
ATransactionTable.GetTableDBName(),
ABatchTable.GetTableDBName(),
ATransactionTable.GetLedgerNumberDBName(),
ALedgerNumber,
ABatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
ATransactionTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
ABatchTable.GetBatchYearDBName(),
AFinancialYear,
ATransactionTable.GetAccountCodeDBName(),
ATransactionTable.GetDebitCreditIndicatorDBName(),
"'" + AIgnoreAccounts.Replace(",", "','") + "'",
ATransactionTable.GetReferenceDBName(),
"'" + AIgnoreReferences.Replace(",", "','") + "'");
transactions = (ATransactionTable)db.SelectDT(transactions, sql, Transaction, null, 0, 0);
// get the analysis attributes
sql =
String.Format("SELECT A.* from PUB_{1} AS B, PUB_{13} AS T, PUB_{0} AS A " +
"WHERE B.{2} = {3} AND B.{4} = {5} AND B.{6}='{7}' " +
"AND T.{2} = B.{2} AND T.{8} = B.{8} " +
"AND T.{9} IN ({10}) " +
"AND A.{2} = T.{2} AND A.{8} = T.{8} AND A.{11} = T.{11} AND A.{12} = T.{12}",
ATransAnalAttribTable.GetTableDBName(),
ABatchTable.GetTableDBName(),
ABatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
ABatchTable.GetBatchYearDBName(),
AFinancialYear,
ABatchTable.GetBatchStatusDBName(),
MFinanceConstants.BATCH_POSTED,
ATransactionTable.GetBatchNumberDBName(),
ATransactionTable.GetCostCentreCodeDBName(),
"'" + ACostCentres.Replace(",", "','") + "'",
ATransactionTable.GetJournalNumberDBName(),
ATransactionTable.GetTransactionNumberDBName(),
ATransactionTable.GetTableDBName(),
ABatchTable.GetBatchYearDBName());
db.SelectDT(TransAnalAttrib, sql, Transaction, null, 0, 0);
TransAnalAttrib.DefaultView.Sort =
ATransAnalAttribTable.GetBatchNumberDBName() + "," +
ATransAnalAttribTable.GetJournalNumberDBName() + "," +
ATransAnalAttribTable.GetTransactionNumberDBName();
// get a list of all batches involved
List <Int64> batches = new List <Int64>();
StringBuilder batchnumbers = new StringBuilder();
foreach (ATransactionRow r in transactions.Rows)
{
if (!batches.Contains(r.BatchNumber))
{
batches.Add(r.BatchNumber);
batchnumbers.Append(r.BatchNumber.ToString() + ",");
}
}
// get the other transactions in the same journal for finding the opposite cc/acc involved
// for performance reasons, get all transactions of the whole batch
sql =
String.Format("SELECT DISTINCT TJ.* " +
"FROM PUB_{0} AS TJ " +
"WHERE TJ.{1} = {2} AND TJ.{3} IN ({4})",
ATransactionTable.GetTableDBName(),
ATransactionTable.GetLedgerNumberDBName(),
ALedgerNumber,
ATransactionTable.GetBatchNumberDBName(),
batchnumbers.ToString() + "-1");
allTransactionsInJournal =
(ATransactionTable)db.SelectDT(allTransactionsInJournal, sql, Transaction, null, 0, 0);
allTransactionsInJournal.DefaultView.Sort =
ATransactionTable.GetBatchNumberDBName() + "," +
ATransactionTable.GetJournalNumberDBName();
// get all names of gift batches
sql =
String.Format("SELECT * FROM PUB_{0} " +
"WHERE {1} = {2} " +
"AND {3} = {4}",
AGiftBatchTable.GetTableDBName(),
AGiftBatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGiftBatchTable.GetBatchYearDBName(),
AFinancialYear);
db.SelectDT(giftbatches, sql, Transaction, null, 0, 0);
giftbatches.DefaultView.Sort = AGiftBatchTable.GetBatchNumberDBName();
sql =
String.Format("SELECT * FROM PUB_{0} " +
"WHERE {1} = {2}",
AAccountTable.GetTableDBName(),
AAccountTable.GetLedgerNumberDBName(),
ALedgerNumber);
db.SelectDT(accounts, sql, Transaction, null, 0, 0);
accounts.DefaultView.Sort = AAccountTable.GetAccountCodeDBName();
});
StringBuilder sb = new StringBuilder();
int rowCounter = 0;
foreach (ATransactionRow row in transactions.Rows)
{
if (row.DebitCreditIndicator)
{
row.TransactionAmount *= -1.0m;
}
StringBuilder attributes = new StringBuilder();
DataRowView[] RelatedTransactions = allTransactionsInJournal.DefaultView.FindRows(new object[] { row.BatchNumber, row.JournalNumber });
ATransactionRow[] OtherTransactions = GetOtherTransactions(row, RelatedTransactions);
string OtherCostCentres = string.Empty;
string OtherAccountCodes = string.Empty;
if (OtherTransactions.Length < 30)
{
foreach (ATransactionRow r in OtherTransactions)
{
OtherCostCentres = StringHelper.AddCSV(OtherCostCentres, r.CostCentreCode);
OtherAccountCodes = StringHelper.AddCSV(OtherAccountCodes, r.AccountCode);
}
}
if (!ACostCentresInvolved.Contains(row.CostCentreCode))
{
ACostCentresInvolved.Add(row.CostCentreCode);
}
if (!AAccountsInvolved.Contains(row.AccountCode))
{
AAccountsInvolved.Add(row.AccountCode);
}
// we are using gift batch for receiving payments
string Narrative = row.Narrative;
if (Narrative.StartsWith("GB - Gift Batch ") && row.Reference.StartsWith("GB"))
{
// find the account and set the account description into the narrative
try
{
DataRowView[] acc = accounts.DefaultView.FindRows(row.AccountCode);
Narrative = ((AAccountRow)acc[0].Row).AccountCodeLongDesc;
}
catch (Exception)
{
}
try
{
DataRowView[] gb = giftbatches.DefaultView.FindRows(Convert.ToInt32(row.Reference.Substring(2)));
Narrative += " " + ((AGiftBatchRow)gb[0].Row).BatchDescription;
}
catch (Exception)
{
}
}
sb.Append(StringHelper.StrMerge(
new string[] {
"B" + row.BatchNumber.ToString() + "_J" + row.JournalNumber.ToString() + "_T" + row.TransactionNumber.ToString(),
row.CostCentreCode,
row.AccountCode,
row.TransactionDate.ToString("yyyyMMdd"),
OtherCostCentres,
OtherAccountCodes,
Narrative,
row.Reference,
String.Format("{0:N}", row.TransactionAmount),
attributes.ToString()
}, ACSVSeparator));
sb.Append(ANewLine);
rowCounter++;
if (rowCounter % 500 == 0)
{
TLogging.Log("Processing transactions " + rowCounter.ToString());
}
}
TLogging.Log("Processing transactions " + rowCounter.ToString());
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
private void InitializeManualCode()
{
DataTable CultureTable = new DataTable();
CultureTable.Columns.Add("Value", typeof(string));
CultureTable.Columns.Add("Display", typeof(string));
foreach (CultureInfo ci in CultureInfo.GetCultures(CultureTypes.AllCultures))
{
if (!ci.IsNeutralCulture)
{
DataRow row = CultureTable.NewRow();
row[0] = ci.Name;
row[1] = ci.EnglishName;
CultureTable.Rows.Add(row);
}
}
CultureTable.DefaultView.Sort = "Display";
cmbCulture.DisplayMember = "Display";
cmbCulture.ValueMember = "Value";
cmbCulture.DataSource = CultureTable.DefaultView;
// load languages from names of language sub directories
string[] LanguageDirectories = Directory.GetDirectories(TAppSettingsManager.ApplicationDirectory);
string LanguagesAvailable = "en-EN";
foreach (String directory in LanguageDirectories)
{
if (File.Exists(directory + Path.DirectorySeparatorChar + "OpenPetra.resources.dll"))
{
LanguagesAvailable = StringHelper.AddCSV(
LanguagesAvailable,
directory.Substring(
directory.LastIndexOf(Path.DirectorySeparatorChar) + 1));
}
}
cmbLanguage.SetDataSourceStringList(LanguagesAvailable);
// for the moment default to english, because translations are not fully supported, and the layout does not adjust
string LanguageCode = "en-EN";
string CultureCode = CultureInfo.CurrentCulture.Name;
TRemote.MSysMan.Maintenance.WebConnectors.GetLanguageAndCulture(ref LanguageCode, ref CultureCode);
cmbCulture.SetSelectedString(CultureCode);
cmbLanguage.SetSelectedString(LanguageCode);
llbLaunchpadLink.Click += LaunchpadLinkClicked;
// Get the number of recent partners that the user has set, if not found take 10 as default value.
nudNumberOfPartners.Value = TUserDefaults.GetInt16Default(MSysManConstants.USERDEFAULT_NUMBEROFRECENTPARTNERS, 10);
nudNumberOfPartners.Maximum = 10;
// Other preferences
chkEscClosesScreen.Checked = TUserDefaults.GetBooleanDefault(TUserDefaults.NamedDefaults.USERDEFAULT_ESC_CLOSES_SCREEN, true);
chkSaveWindowProperties.Checked = TUserDefaults.GetBooleanDefault(TUserDefaults.NamedDefaults.USERDEFAULT_SAVE_WINDOW_POS_AND_SIZE, true);
WasSaveWindowPropertiesInitiallyChecked = chkSaveWindowProperties.Checked;
}
//
// This will be called if the Fast Reports Wrapper loaded OK.
// Returns True if the data apparently loaded OK and the report should be printed.
private bool LoadReportData(TRptCalculator ACalc)
{
Shared.MReporting.TParameterList parameters = ACalc.GetParameters();
String LedgerFilter = "a_ledger_number_i=" + parameters.Get("param_ledger_number_i").ToInt32();
String AccountCodeFilter = ""; // Account Filter, as range or list:
String GlmAccountCodeFilter = "";
DataTable Balances = new DataTable();
ACalc.AddStringParameter("param_linked_partner_cc", ""); // I may want to use this later, for auto_email, but usually it's unused.
if (parameters.Get("param_rgrAccounts").ToString() == "AccountList")
{
String Filter = "'" + parameters.Get("param_account_codes") + "'";
Filter = Filter.Replace(",", "','");
AccountCodeFilter = "AND a_account_code_c in (" + Filter + ")";
GlmAccountCodeFilter = " AND glm.a_account_code_c in (" + Filter + ")";
}
if (parameters.Get("param_rgrAccounts").ToString() == "AccountRange")
{
AccountCodeFilter = "AND a_account_code_c BETWEEN '" + parameters.Get("param_account_code_start") + "' AND '" +
parameters.Get("param_account_code_end") + "'";
GlmAccountCodeFilter = " AND glm.a_account_code_c BETWEEN '" + parameters.Get("param_account_code_start") + "' AND '" +
parameters.Get("param_account_code_end") + "'";
}
String CostCentreFilter = ""; // Cost Centre Filter, as range or list:
String GlmCostCentreFilter = "";
if (parameters.Get("param_rgrCostCentres").ToString() == "CostCentreList")
{
String Filter = "'" + parameters.Get("param_cost_centre_codes") + "'";
Filter = Filter.Replace(",", "','");
CostCentreFilter = " AND a_cost_centre_code_c in (" + Filter + ")";
GlmCostCentreFilter = " AND glm.a_cost_centre_code_c in (" + Filter + ")";
}
if (parameters.Get("param_rgrCostCentres").ToString() == "CostCentreRange")
{
CostCentreFilter = " AND a_cost_centre_code_c BETWEEN '" + parameters.Get("param_cost_centre_code_start") +
"' AND '" + parameters.Get("param_cost_centre_code_end") + "'";
GlmCostCentreFilter = " AND glm.a_cost_centre_code_c BETWEEN '" + parameters.Get("param_cost_centre_code_start") +
"' AND '" + parameters.Get("param_cost_centre_code_end") + "'";
}
String TranctDateFilter = "a_transaction_date_d BETWEEN '" + parameters.Get("param_start_date").DateToString("yyyy-MM-dd") +
"' AND '" + parameters.Get("param_end_date").DateToString("yyyy-MM-dd") + "'";
String ReferenceFilter = "";
String AnalysisTypeFilter = "";
String GroupField = "a_account_code_c, a_cost_centre_code_c";
String Sortby = parameters.Get("param_sortby").ToString();
if (Sortby == "Cost Centre")
{
GroupField = "a_cost_centre_code_c, a_account_code_c";
}
if (Sortby == "Reference")
{
GroupField = "a_reference_c";
String FilterItem = parameters.Get("param_reference_start").ToString();
if (FilterItem != "")
{
ReferenceFilter = " AND a_reference_c >='" + FilterItem + "'";
}
FilterItem = parameters.Get("param_reference_end").ToString();
if (FilterItem != "")
{
ReferenceFilter += " AND a_reference_c <='" + FilterItem + "'";
}
}
if (Sortby == "Analysis Type")
{
GroupField = "a_analysis_type_code_c";
String FilterItem = parameters.Get("param_analyis_type_start").ToString();
if (FilterItem != "")
{
AnalysisTypeFilter = " AND a_analysis_type_code_c >='" + FilterItem + "'";
}
FilterItem = parameters.Get("param_analyis_type_end").ToString();
if (FilterItem != "")
{
AnalysisTypeFilter += " AND a_analysis_type_code_c <='" + FilterItem + "'";
}
}
parameters.Add("param_groupfield", GroupField);
String Csv = "";
Csv = StringHelper.AddCSV(Csv, "ALedger/SELECT * FROM a_ledger WHERE " + LedgerFilter);
Csv = StringHelper.AddCSV(
Csv,
"AAccount/SELECT * FROM a_account WHERE " + LedgerFilter + AccountCodeFilter +
" AND a_posting_status_l=true AND a_account_active_flag_l=true");
Csv = StringHelper.AddCSV(
Csv,
"ACostCentre/SELECT * FROM a_cost_centre WHERE " + LedgerFilter + CostCentreFilter +
" AND a_posting_cost_centre_flag_l=true AND a_cost_centre_active_flag_l=true");
Boolean InternationalCurrencySelected = parameters.Get("param_currency").ToString().StartsWith("Int");
String AmountField = InternationalCurrencySelected ? "a_amount_in_intl_currency_n" : "a_amount_in_base_currency_n";
if (Sortby == "Analysis Type") // To sort by analysis type, I need a different (and more horible) query:
{
Csv = StringHelper.AddCSV(
Csv,
"Transactions/" +
"SELECT a_transaction.a_account_code_c AS AccountCode," +
"a_transaction.a_cost_centre_code_c AS CostCentreCode," +
"a_transaction.a_transaction_date_d AS TransactionDate," +
"a_transaction." + AmountField + " AS Amount," +
"a_transaction.a_debit_credit_indicator_l AS Debit," +
"a_transaction.a_narrative_c AS Narrative," +
"a_transaction.a_reference_c AS Reference," +
"a_trans_anal_attrib.a_analysis_type_code_c AS AnalysisTypeCode," +
"a_analysis_type.a_analysis_type_description_c AS AnalysisTypeDescr," +
"a_trans_anal_attrib.a_analysis_attribute_value_c AS AnalysisValue" +
" FROM a_transaction, a_trans_anal_attrib, a_analysis_type" +
" WHERE a_transaction." + LedgerFilter +
" AND a_trans_anal_attrib.a_ledger_number_i = a_transaction.a_ledger_number_i " +
" AND a_trans_anal_attrib.a_batch_number_i = a_transaction.a_batch_number_i" +
" AND a_trans_anal_attrib.a_journal_number_i = a_transaction.a_journal_number_i" +
" AND a_trans_anal_attrib.a_transaction_number_i = a_transaction.a_transaction_number_i" +
" AND a_trans_anal_attrib.a_analysis_type_code_c = a_analysis_type.a_analysis_type_code_c" +
AnalysisTypeFilter +
AccountCodeFilter + CostCentreFilter + " AND " + TranctDateFilter +
" AND a_transaction_status_l=true AND NOT (a_system_generated_l=true AND a_narrative_c LIKE 'Year end re-allocation%')" +
" ORDER BY " + GroupField + ", a_transaction_date_d");
}
else
{
Csv = StringHelper.AddCSV(Csv, "Transactions/" +
"SELECT a_transaction.a_account_code_c AS AccountCode," +
"a_transaction.a_cost_centre_code_c AS CostCentreCode," +
"a_transaction.a_transaction_date_d AS TransactionDate," +
"a_transaction." + AmountField + " AS Amount," +
"a_journal.a_transaction_currency_c AS Currency," +
"a_transaction.a_debit_credit_indicator_l AS Debit," +
"a_transaction.a_narrative_c AS Narrative," +
"a_transaction.a_reference_c AS Reference," +
"'' AS AnalysisTypeCode," +
"'' AS AnalysisTypeDescr," +
"'' AS AnalysisValue" +
" FROM a_transaction, a_journal WHERE " +
" a_transaction." + LedgerFilter +
" AND a_transaction.a_ledger_number_i = a_journal.a_ledger_number_i " +
" AND a_transaction.a_batch_number_i = a_journal.a_batch_number_i " +
" AND a_transaction.a_journal_number_i = a_journal.a_journal_number_i " +
AccountCodeFilter +
CostCentreFilter + " AND " +
TranctDateFilter + ReferenceFilter +
" AND a_transaction_status_l=true AND NOT (a_system_generated_l=true AND a_narrative_c LIKE 'Year end re-allocation%')" +
" ORDER BY " + GroupField + ", a_transaction_date_d");
}
GLReportingTDS ReportDs = TRemote.MReporting.WebConnectors.GetReportingDataSet(Csv);
if ((this.IsDisposed) || // If the user has pressed Esc the form is closed!
(TRemote.MReporting.WebConnectors.DataTableGenerationWasCancelled()))
{
return(false);
}
//
// I want to include opening and closing balances for each Cost Centre / Account, in the selected currency.
// Following a revision in Oct 2014, this table is the master table, and the transactions are the slave.
Int32 Year = parameters.Get("param_year_i").ToInt32();
Balances = TRemote.MFinance.Reporting.WebConnectors.GetPeriodBalances(
LedgerFilter,
GlmAccountCodeFilter,
GlmCostCentreFilter,
Year,
Sortby,
ReportDs.Tables["Transactions"],
parameters.Get("param_start_period_i").ToInt32(),
parameters.Get("param_end_period_i").ToInt32(),
InternationalCurrencySelected
);
if ((this.IsDisposed) || (Balances == null))
{
return(false);
}
// My report doesn't need a ledger row - only the name of the ledger. And I need the currency formatter..
{
ALedgerRow Row = ReportDs.ALedger[0];
ACalc.AddStringParameter("param_ledger_name", Row.LedgerName);
ACalc.AddStringParameter("param_currency_formatter", "0,0.000");
}
if (TRemote.MReporting.WebConnectors.DataTableGenerationWasCancelled())
{
return(false);
}
//
// If I need to show Analysis Attributes, I need to get the rows that pertain to the Transactions I've selected above.
if (ReportDs.ATransaction.Rows.Count > 0)
{
DataView BatchSorted = new DataView(ReportDs.ATransaction);
BatchSorted.Sort = "a_batch_number_i";
ATransactionRow Row = (ATransactionRow)BatchSorted[0].Row;
Int32 FirstBatch = Row.BatchNumber;
Row = (ATransactionRow)BatchSorted[BatchSorted.Count - 1].Row;
Int32 LastBatch = Row.BatchNumber;
Csv = "";
Csv = StringHelper.AddCSV(
Csv,
"ATransAnalAttrib/SELECT * FROM a_trans_anal_attrib WHERE " + LedgerFilter +
" AND a_batch_number_i >= " + FirstBatch +
" AND a_batch_number_i <= " + LastBatch);
Csv = StringHelper.AddCSV(Csv, "AAnalysisType/SELECT * FROM a_analysis_type");
ReportDs.Merge(TRemote.MReporting.WebConnectors.GetReportingDataSet(Csv));
}
if (TRemote.MReporting.WebConnectors.DataTableGenerationWasCancelled())
{
return(false);
}
FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.ATransAnalAttrib, "a_trans_anal_attrib");
FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.AAnalysisType, "a_analysis_type");
FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.AAccount, "a_account");
FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.ACostCentre, "a_costCentre");
FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.Tables["Transactions"], "Transactions");
FPetraUtilsObject.FFastReportsPlugin.RegisterData(Balances, "balances");
//
// For Account Detail reports that must be sent on email, one page at a time,
// I'm calling the FastReports plugin multiple times,
// and then I'm going to return false, which will prevent the default action using this dataset.
if ((parameters.Get("param_sortby").ToString() == "Cost Centre") &&
(parameters.Get("param_auto_email").ToBool()) &&
!parameters.Get("param_design_template").ToBool()
)
{
FPetraUtilsObject.FFastReportsPlugin.AutoEmailReports(ACalc, FLedgerNumber, CostCentreFilter);
return(false);
}
return(true);
}
/// <summary>
/// This returns the resultlist as lines for a CSV file
/// </summary>
/// <param name="AParameters"></param>
/// <param name="separator">if this has the value FIND_BEST_SEPARATOR,
/// then first the parameters will be checked for CSV_separator, and if that parameter does not exist,
/// then the CurrentCulture is checked, for the local language settings</param>
/// <param name="ADebugging">if true, thent the currency and date values are written encoded, not localized</param>
/// <param name="AExportOnlyLowestLevel">if true, only the lowest level of AParameters are exported (level with higest depth)
/// otherwise all levels in AParameter are exported</param>
/// <returns>the lines to be written to the CSV file</returns>
public List <string> WriteCSVInternal(TParameterList AParameters,
string separator = "FIND_BEST_SEPARATOR",
Boolean ADebugging = false,
Boolean AExportOnlyLowestLevel = false)
{
List <string> lines = new List <string>();
int i;
string strLine;
ArrayList sortedList;
bool display;
bool useIndented;
TParameterList FormattedParameters;
TResultList FormattedResult;
// myEncoding: Encoding;
// bytes: array of byte;
if (separator == "FIND_BEST_SEPARATOR")
{
if (AParameters.Exists("CSV_separator"))
{
separator = AParameters.Get("CSV_separator").ToString();
if (separator.ToUpper() == "TAB")
{
separator = new String((char)9, 1);
}
else if (separator.ToUpper() == "SPACE")
{
separator = " ";
}
}
else
{
separator = CultureInfo.CurrentCulture.TextInfo.ListSeparator;
}
}
if (ADebugging == false)
{
FormattedParameters = AParameters.ConvertToFormattedStrings("CSV");
FormattedResult = ConvertToFormattedStrings(FormattedParameters, "CSV");
}
else
{
FormattedParameters = AParameters;
FormattedResult = this;
}
// write headings
strLine = "";
// for debugging:
// strLine = StringHelper.AddCSV(strLine, "masterRow", separator);
// strLine = StringHelper.AddCSV(strLine, "childRow", separator);
// strLine = StringHelper.AddCSV(strLine, "depth", separator);
strLine = StringHelper.AddCSV(strLine, "id", separator);
if (FormattedParameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT1,
-1, eParameterFit.eBestFit))
{
strLine = StringHelper.AddCSV(strLine, FormattedParameters.Get("ControlSource",
ReportingConsts.HEADERPAGELEFT1,
-1, eParameterFit.eBestFit).ToString(), separator);
}
if (FormattedParameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT2,
-1, eParameterFit.eBestFit))
{
strLine = StringHelper.AddCSV(strLine, FormattedParameters.Get("ControlSource",
ReportingConsts.HEADERPAGELEFT2,
-1, eParameterFit.eBestFit).ToString(), separator);
}
if (FormattedParameters.Exists("ControlSource", ReportingConsts.HEADERCOLUMN,
-1, eParameterFit.eBestFit))
{
strLine = StringHelper.AddCSV(strLine, "header 1", separator);
strLine = StringHelper.AddCSV(strLine, "header 0", separator);
}
useIndented = false;
for (i = 0; i <= FormattedParameters.Get("lowestLevel").ToInt(); i++)
{
if (FormattedParameters.Exists("indented", ReportingConsts.ALLCOLUMNS, i, eParameterFit.eBestFit))
{
useIndented = true;
}
}
MaxDisplayColumns = AParameters.Get("MaxDisplayColumns").ToInt32();
for (i = 0; i < MaxDisplayColumns; i++)
{
if ((!FormattedParameters.Get("ColumnCaption", i, -1, eParameterFit.eBestFit).IsNil()))
{
strLine =
StringHelper.AddCSV(strLine,
(FormattedParameters.Get("ColumnCaption",
i, -1, eParameterFit.eBestFit).ToString() + ' ' +
FormattedParameters.Get("ColumnCaption2",
i, -1,
eParameterFit.eBestFit).ToString(false) + ' ' +
FormattedParameters.Get("ColumnCaption3", i, -1, eParameterFit.eBestFit).ToString(
false)).Trim(), separator);
if (useIndented)
{
strLine = StringHelper.AddCSV(strLine, "", separator);
}
}
}
lines.Add(strLine);
FormattedResult.SortChildren();
sortedList = new ArrayList();
FormattedResult.CreateSortedListByMaster(sortedList, 0);
int LowestLevel = -1;
if (AExportOnlyLowestLevel)
{
// find the highest level
foreach (TResult element in sortedList)
{
if (element.depth > LowestLevel)
{
LowestLevel = element.depth;
}
}
}
// write each row to CSV file
foreach (TResult element in sortedList)
{
if (AExportOnlyLowestLevel &&
(element.depth < LowestLevel))
{
continue;
}
if (element.display)
{
strLine = "";
// for debugging
// strLine = StringHelper.AddCSV(strLine, element.masterRow.ToString(), separator);
// strLine = StringHelper.AddCSV(strLine, element.childRow.ToString(), separator);
// strLine = StringHelper.AddCSV(strLine, element.depth.ToString(), separator);
strLine = StringHelper.AddCSV(strLine, element.code, separator);
if (FormattedParameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT1, -1, eParameterFit.eBestFit))
{
if (ADebugging)
{
strLine = StringHelper.AddCSV(strLine, element.descr[0].EncodeToString(), separator);
}
else
{
strLine = StringHelper.AddCSV(strLine, element.descr[0].ToString(), separator);
}
}
if (FormattedParameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT2, -1, eParameterFit.eBestFit))
{
if (ADebugging)
{
strLine = StringHelper.AddCSV(strLine, element.descr[1].EncodeToString(), separator);
}
else
{
strLine = StringHelper.AddCSV(strLine, element.descr[1].ToString(), separator);
}
}
if (FormattedParameters.Exists("ControlSource", ReportingConsts.HEADERCOLUMN, -1, eParameterFit.eBestFit))
{
if (ADebugging)
{
strLine = StringHelper.AddCSV(strLine, element.header[1].EncodeToString(), separator);
strLine = StringHelper.AddCSV(strLine, element.header[0].EncodeToString(), separator);
}
else
{
strLine = StringHelper.AddCSV(strLine, element.header[1].ToString(), separator);
strLine = StringHelper.AddCSV(strLine, element.header[0].ToString(), separator);
}
}
/* TODO: try to export in the right codepage, to print umlaut and other special characters correctly
* if element.childRow = 7 then
* begin
* myEncoding := System.Text.Encoding.get_ASCII;
* TLogging.Log(Encoding.Default.EncodingName);
* TLogging.Log(element.column[0].ToString());
* SetLength(bytes, Encoding.Default.GetByteCount(element.column[0].ToString()));
* bytes := Encoding.Default.GetBytes(element.column[0].ToString());
* TLogging.Log(myEncoding.GetChars(bytes));
* // this will still not help with Excel
* end;
*/
display = false;
for (i = 0; i <= MaxDisplayColumns - 1; i += 1)
{
if (FormattedParameters.Get("indented", i, element.depth, eParameterFit.eAllColumnFit).ToBool() == true)
{
strLine = StringHelper.AddCSV(strLine, "", separator);
}
if (((element.column[i] != null) && (!element.column[i].IsNil())) || (ADebugging))
{
display = true;
if (ADebugging)
{
strLine = StringHelper.AddCSV(strLine, element.column[i].EncodeToString(), separator);
}
else
{
strLine = StringHelper.AddCSV(strLine, element.column[i].ToString().Trim(), separator);
}
}
else
{
strLine = StringHelper.AddCSV(strLine, "", separator);
}
if ((FormattedParameters.Get("indented", i, element.depth, eParameterFit.eAllColumnFit).ToBool() != true) && useIndented)
{
strLine = StringHelper.AddCSV(strLine, "", separator);
}
}
if (display)
{
lines.Add(strLine);
}
}
}
sortedList = null;
return(lines);
}
//
// New methods using the Fast-reports DLL:
private Boolean LoadReportData(TRptCalculator ACalc)
{
Shared.MReporting.TParameterList pm = ACalc.GetParameters();
pm.Add("param_current_period", uco_GeneralSettings.GetCurrentPeiod());
// 0 = Full Report. Currently the only option for this report.
pm.Add("param_run_number", 0);
String Csv = "";
//
// My "a_transaction" table forms the lower half of the HOSA:
// All transactions for all the "Expense" accounts for the selected Cost Centre within the selected dates or periods.
String LedgerFilter = "a_ledger_number_i=" + pm.Get("param_ledger_number_i").ToInt32();
String TranctDateFilter = ""; // Optional Date Filter, as periods or dates
String CostCentreCodes = pm.Get("param_cost_centre_codes").ToString();
if (CostCentreCodes == String.Empty)
{
MessageBox.Show(Catalog.GetString("Please select one or more Cost Centres."), "HOSA");
return(false);
}
CostCentreCodes = CostCentreCodes.Replace('"', '\'');
ACalc.AddStringParameter("param_cost_centre_codes", CostCentreCodes);
if (pm.Get("param_period").ToBool() == true)
{
DataTable AccountingPeriodTbl =
(AAccountingPeriodTable)TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountingPeriodList,
pm.Get("param_ledger_number_i").ToInt32());
Int32 PeriodStart = pm.Get("param_start_period_i").ToInt32();
Int32 PeriodEnd = pm.Get("param_end_period_i").ToInt32();
AccountingPeriodTbl.DefaultView.RowFilter = LedgerFilter + " AND a_accounting_period_number_i=" + PeriodStart;
DateTime DateStart = Convert.ToDateTime(AccountingPeriodTbl.DefaultView[0].Row["a_period_start_date_d"]);
pm.Add("param_start_date", DateStart);
AccountingPeriodTbl.DefaultView.RowFilter = LedgerFilter + " AND a_accounting_period_number_i=" + PeriodEnd;
DateTime DateEnd = Convert.ToDateTime(AccountingPeriodTbl.DefaultView[0].Row["a_period_end_date_d"]);
pm.Add("param_end_date", DateEnd);
String PeriodTitle = " (" + DateStart.ToString("dd-MMM-yyyy") + " - " + DateEnd.ToString("dd-MMM-yyyy") + ")";
if (PeriodEnd > PeriodStart)
{
PeriodTitle = String.Format("{0} - {1}", PeriodStart, PeriodEnd) + PeriodTitle;
}
else
{
PeriodTitle = String.Format("{0}", PeriodStart) + PeriodTitle;
}
pm.Add("param_date_title", PeriodTitle);
}
else
{
String PeriodTitle = " " + pm.Get("param_start_date").DateToString("yyyy-MM-dd") + " - " +
pm.Get("param_end_date").DateToString("yyyy-MM-dd");
pm.Add("param_date_title", PeriodTitle);
}
TranctDateFilter = "a_transaction_date_d>='" + pm.Get("param_start_date").DateToString("yyyy-MM-dd") +
"' AND a_transaction_date_d<='" + pm.Get("param_end_date").DateToString("yyyy-MM-dd") + "'";
String TranctCostCentreFilter = " AND a_cost_centre_code_c IN (" + CostCentreCodes + ") ";
Csv = StringHelper.AddCSV(Csv,
"AAccount/SELECT * FROM a_account WHERE " + LedgerFilter + " AND a_posting_status_l=true AND a_account_active_flag_l=true");
Csv = StringHelper.AddCSV(Csv,
"ACostCentre/SELECT * FROM a_cost_centre WHERE " + LedgerFilter + " AND a_cost_centre_code_c IN (" + CostCentreCodes +
") AND a_posting_cost_centre_flag_l=true AND a_cost_centre_active_flag_l=true");
Csv = StringHelper.AddCSV(
Csv,
"ATransaction/SELECT * FROM a_transaction WHERE " + LedgerFilter +
TranctCostCentreFilter + " AND " + TranctDateFilter +
" AND NOT (a_system_generated_l = true AND (a_narrative_c LIKE 'Gifts received - Gift Batch%' OR a_narrative_c LIKE 'GB - Gift Batch%' OR a_narrative_c LIKE 'Year end re-allocation%'))"
+
" ORDER BY a_account_code_c, a_transaction_date_d");
GLReportingTDS ReportDs = TRemote.MReporting.WebConnectors.GetReportingDataSet(Csv);
ArrayList reportParam = ACalc.GetParameters().Elems;
Dictionary <String, TVariant> paramsDictionary = new Dictionary <string, TVariant>();
foreach (Shared.MReporting.TParameter p in reportParam)
{
if (p.name.StartsWith("param") && (p.name != "param_calculation"))
{
paramsDictionary.Add(p.name, p.value);
}
}
DataTable ReportTable = TRemote.MReporting.WebConnectors.GetReportDataTable("HOSA", paramsDictionary);
if (this.IsDisposed)
{
return(false);
}
if (ReportTable == null)
{
FPetraUtilsObject.WriteToStatusBar("Report Cancelled.");
return(false);
}
FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportTable, "Gifts");
FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.AAccount, "a_account");
FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.ACostCentre, "a_costCentre");
FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.ATransaction, "a_transaction");
//
// My report doesn't need a ledger row - only the name of the ledger. And I need the currency formatter..
String LedgerName = TRemote.MFinance.Reporting.WebConnectors.GetLedgerName(FLedgerNumber);
ACalc.AddStringParameter("param_ledger_name", LedgerName);
ACalc.AddStringParameter("param_currency_formatter", "0,0.000");
Boolean HasData = (ReportDs.ATransaction.Rows.Count > 0) || (ReportTable.Rows.Count > 0);
if (!HasData)
{
MessageBox.Show(Catalog.GetString("No Transactions found for selected Cost Centres."), "HOSA");
}
return(HasData);
}
public static void Main(string[] args)
{
new TAppSettingsManager(false);
try
{
string filename = TAppSettingsManager.GetValue("extfile");
string newCSVFilename = Path.GetDirectoryName(filename) +
Path.DirectorySeparatorChar +
Path.GetFileNameWithoutExtension(filename) +
".csv";
StreamReader sr = new StreamReader(filename);
StreamWriter sw = new StreamWriter(newCSVFilename);
string newCSVLine = String.Empty;
bool recordData = false;
bool cleanFileEnd = false;
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
if (line.StartsWith("\"APPL-FORM\""))
{
recordData = false;
}
if (line.Trim() == "\"END\"")
{
sw.WriteLine(newCSVLine);
newCSVLine = string.Empty;
recordData = false;
}
if (recordData)
{
while (line.Length > 0)
{
try
{
newCSVLine = StringHelper.AddCSV(newCSVLine, StringHelper.GetNextCSV(ref line, " "));
}
catch (ArgumentOutOfRangeException)
{
// we have the case that a string contains new line characters, so the ending quote cannot be found in the current line
line += " " + sr.ReadLine();
}
}
}
if (line.Trim() == "\"PARTNER\"")
{
recordData = true;
}
if (line.Trim() == "\"END\" \"FORMS\"")
{
recordData = true;
}
if (line.Trim() == "0 \"FINISH\"")
{
cleanFileEnd = true;
}
}
if (!cleanFileEnd)
{
Console.WriteLine("Your file " + filename + " is broken, it does not have the correct finish line. Please export again");
}
Console.WriteLine("File " + newCSVFilename + " has been written.");
sw.Close();
sr.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
Environment.Exit(-1);
}
}
/// <summary>
/// Data loader for HOSA data,
/// Made static so it can be called from Stewardship Reports
/// </summary>
public static Boolean LoadReportDataStaticInner(Form ParentForm,
TFrmPetraReportingUtils UtilsObject,
FastReportsWrapper ReportingEngine,
TRptCalculator ACalc)
{
Shared.MReporting.TParameterList pm = ACalc.GetParameters();
String Csv = "";
//
// My "a_transaction" table forms the lower half of the HOSA:
// All transactions for all the "Expense" accounts for the selected Cost Centre within the selected dates or periods.
String LedgerFilter = "a_ledger_number_i=" + pm.Get("param_ledger_number_i").ToInt32();
String TranctDateFilter = ""; // Optional Date Filter, as periods or dates
String CostCentreCodes = pm.Get("param_cost_centre_codes").ToString();
if (CostCentreCodes == String.Empty)
{
MessageBox.Show(Catalog.GetString("Please select one or more Cost Centres."), "HOSA");
return(false);
}
String CostCentreFilter = "";
if (CostCentreCodes == "ALL")
{
CostCentreFilter = " AND a_cost_centre.a_cost_centre_type_c='Foreign' ";
}
else
{
CostCentreCodes = CostCentreCodes.Replace('"', '\'');
ACalc.AddStringParameter("param_cost_centre_codes", CostCentreCodes);
CostCentreFilter = " AND a_cost_centre.a_cost_centre_code_c IN (" + CostCentreCodes + ") ";
}
if (pm.Get("param_period").ToBool() == true)
{
Int32 PeriodStart = pm.Get("param_start_period_i").ToInt32();
Int32 PeriodEnd = pm.Get("param_end_period_i").ToInt32();
DateTime DateStart = pm.Get("param_start_date").ToDate();
DateTime DateEnd = pm.Get("param_end_date").ToDate();
ALedgerTable LedgerDetailsTable = (ALedgerTable)TDataCache.TMFinance.GetCacheableFinanceTable(
TCacheableFinanceTablesEnum.LedgerDetails);
ALedgerRow LedgerRow = LedgerDetailsTable[0];
Boolean IsClosed = (!pm.Get("param_current_financial_year").ToBool() || (PeriodEnd < LedgerRow.CurrentPeriod));
ACalc.AddParameter("param_period_closed", IsClosed);
Boolean IsCurrent = (pm.Get("param_current_financial_year").ToBool() && (PeriodEnd == LedgerRow.CurrentPeriod));
ACalc.AddParameter("param_period_current", IsCurrent);
String PeriodTitle = " (" + DateStart.ToString("dd-MMM-yyyy") + " - " + DateEnd.ToString("dd-MMM-yyyy") + ")";
if (PeriodEnd > PeriodStart)
{
PeriodTitle = String.Format("{0} - {1}", PeriodStart, PeriodEnd) + PeriodTitle;
}
else
{
PeriodTitle = String.Format("{0}", PeriodStart) + PeriodTitle;
}
pm.Add("param_date_title", PeriodTitle);
}
else
{
String PeriodTitle = " " + pm.Get("param_start_date").DateToString("yyyy-MM-dd") + " - " +
pm.Get("param_end_date").DateToString("yyyy-MM-dd");
pm.Add("param_date_title", PeriodTitle);
}
TranctDateFilter = "a_transaction_date_d>='" + pm.Get("param_start_date").DateToString("yyyy-MM-dd") +
"' AND a_transaction_date_d<='" + pm.Get("param_end_date").DateToString("yyyy-MM-dd") + "'";
Csv = StringHelper.AddCSV(Csv,
"AAccount/SELECT * FROM a_account WHERE " + LedgerFilter + " AND a_posting_status_l=true AND a_account_active_flag_l=true");
Csv = StringHelper.AddCSV(Csv,
"ACostCentre/SELECT * FROM a_cost_centre WHERE " + LedgerFilter + CostCentreFilter +
" AND a_posting_cost_centre_flag_l=true AND a_cost_centre_active_flag_l=true");
Csv = StringHelper.AddCSV(
Csv,
"ATransaction/SELECT a_transaction.* FROM a_transaction, a_cost_centre WHERE a_transaction." + LedgerFilter +
" AND " + TranctDateFilter +
" AND NOT (a_system_generated_l = true AND (a_narrative_c LIKE 'Gifts received - Gift Batch%' OR a_narrative_c LIKE 'GB - Gift Batch%' OR a_narrative_c LIKE 'Year end re-allocation%'))"
+
" AND a_transaction.a_ledger_number_i = a_cost_centre.a_ledger_number_i " +
" AND a_transaction.a_cost_centre_code_c = a_cost_centre.a_cost_centre_code_c " +
CostCentreFilter +
" ORDER BY a_account_code_c, a_transaction_date_d");
GLReportingTDS ReportDs = TRemote.MReporting.WebConnectors.GetReportingDataSet(Csv);
ArrayList reportParam = ACalc.GetParameters().Elems;
Dictionary <String, TVariant> paramsDictionary = new Dictionary <string, TVariant>();
foreach (Shared.MReporting.TParameter p in reportParam)
{
if (p.name.StartsWith("param") && (p.name != "param_calculation"))
{
paramsDictionary.Add(p.name, p.value);
}
}
DataTable GiftsTable = TRemote.MReporting.WebConnectors.GetReportDataTable("HOSA", paramsDictionary);
DataTable KeyMinGiftsTable = TRemote.MReporting.WebConnectors.GetReportDataTable("FieldGifts", paramsDictionary);
//
// I'm going to get rid of any Cost Centres that saw no activity in the requested period:
for (Int32 Idx = ReportDs.ACostCentre.Rows.Count - 1; Idx >= 0; Idx--)
{
ACostCentreRow Row = ReportDs.ACostCentre[Idx];
ReportDs.ATransaction.DefaultView.RowFilter = String.Format("a_cost_centre_code_c='{0}'", Row.CostCentreCode);
GiftsTable.DefaultView.RowFilter = String.Format("CostCentre='{0}'", Row.CostCentreCode);
if ((ReportDs.ATransaction.DefaultView.Count == 0) && (GiftsTable.DefaultView.Count == 0))
{
ReportDs.ACostCentre.Rows.Remove(Row);
}
}
if (ParentForm.IsDisposed)
{
return(false);
}
if (GiftsTable == null)
{
UtilsObject.WriteToStatusBar("Report Cancelled.");
return(false);
}
ReportingEngine.RegisterData(GiftsTable, "Gifts");
ReportingEngine.RegisterData(KeyMinGiftsTable, "FieldGifts");
ReportingEngine.RegisterData(ReportDs.AAccount, "a_account");
ReportingEngine.RegisterData(ReportDs.ACostCentre, "a_costCentre");
ReportingEngine.RegisterData(ReportDs.ATransaction, "a_transaction");
Boolean HasData = (ReportDs.ATransaction.Rows.Count > 0) || (GiftsTable.Rows.Count > 0);
if (!HasData)
{
MessageBox.Show(Catalog.GetString("No Transactions found for selected Cost Centres."), "HOSA");
}
return(HasData);
}