/// <summary>
/// calculate an extract from a report: all partners who have a Contact Log meeting selected criteria
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
/// <returns></returns>
public static bool CalculateExtract(TParameterList AParameters, TResultList AResults)
{
string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractPartnerByContactLog.sql");
QueryPartnerByContactLog ExtractQuery = new QueryPartnerByContactLog();
return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults);
}
/// <summary>
/// calculate an extract from a report: all partners living in a given city
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
/// <param name="AExtractId"></param>
/// <returns></returns>
public static bool CalculateExtract(TParameterList AParameters, TResultList AResults, out int AExtractId)
{
string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractFamilyMembers.sql");
// create a new object of this class and control extract calculation from base class
QueryFamilyMembersExtract ExtractQuery = new QueryFamilyMembersExtract();
return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults, out AExtractId);
}
/// <summary>
/// calculate an extract from a report: all recipient that have given to particular fields (ledgers)
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
/// <returns></returns>
public static bool CalculateExtract(TParameterList AParameters, TResultList AResults)
{
string SqlStmt = TDataBase.ReadSqlFile("Gift.Queries.ExtractRecipientByField.sql");
// create a new object of this class and control extract calculation from base class
QueryRecipientByField ExtractQuery = new QueryRecipientByField();
return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults);
}
/// <summary>
/// calculate an extract from a report: all partners living in a given city
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
/// <returns></returns>
public static bool CalculateExtract(TParameterList AParameters, TResultList AResults)
{
string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractByPartnerCity.sql");
// create a new object of this class and control extract calculation from base class
QueryPartnerByCity ExtractQuery = new QueryPartnerByCity();
return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults);
}
/// <summary>
/// Copy Constructor
/// creates a copy of another Resultlist;
/// this is required to be able to print and export to CSV with the correctly formatted dates
///
/// </summary>
/// <returns>void</returns>
public TResultList(TResultList copy)
{
MaxDisplayColumns = copy.MaxDisplayColumns;
results = new ArrayList();
foreach (TResult r in copy.results)
{
results.Add(new TResult(r));
}
}
/// <summary>
/// calculate an extract from a report: all donors that have given to particular fields (ledgers)
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
/// <returns></returns>
public static bool CalculateExtract(TParameterList AParameters, TResultList AResults)
{
// Sql statements will be initialized later on in special treatment
string SqlStmt = "";
// create a new object of this class and control extract calculation from base class
QueryDonorByAmount ExtractQuery = new QueryDonorByAmount();
return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults);
}
/// <summary>
/// constructor
/// </summary>
/// <param name="AResult"></param>
/// <param name="AParameters"></param>
/// <param name="APrinter"></param>
public TReportPrinterCommon(TResultList AResult, TParameterList AParameters, TPrinter APrinter)
{
// go through all results and parameters and replace the unformatted and encoded date
// the whole point is to format the dates differently, depending on the output (printer vs. CSV)
FParameters = AParameters.ConvertToFormattedStrings("Localized");
FResultList = AResult.ConvertToFormattedStrings(FParameters, "Localized");
FResults = FResultList.GetResults();
FLowestLevel = FParameters.Get("lowestLevel").ToInt();
FTimePrinted = DateTime.Now;
FPrinter = APrinter;
}
/// <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>
/// constructor
/// </summary>
public TGridPreview(Form APreviewForm,
TFrmPetraUtils APetraUtilsObject,
TPreviewDelegate APreviewDelegate,
TResultList AResultList,
TParameterList AParameters)
{
results = AResultList.ConvertToFormattedStrings(AParameters);
FOrigParameters = AParameters;
parameters = AParameters.ConvertToFormattedStrings();
FPreviewForm = APreviewForm;
FPetraUtilsObject = APetraUtilsObject;
FPreviewDelegate = APreviewDelegate;
FGenerateReportThread = null;
}
/// <summary>
/// constructor
/// </summary>
/// <param name="AResult"></param>
/// <param name="AParameters"></param>
/// <param name="APrinter"></param>
/// <param name="AWrapColumn">True: Wrap text in the column if it is to long. Otherwise cut it</param>
public TReportPrinterLayout(TResultList AResult, TParameterList AParameters, TPrinter APrinter, bool AWrapColumn) : base(AResult, AParameters,
APrinter)
{
FWrapColumn = AWrapColumn;
if (AParameters.Get("ReportWidth").ToDouble() > 20)
{
APrinter.Init(eOrientation.eLandscape, this, eMarginType.eDefaultMargins);
}
else
{
APrinter.Init(eOrientation.ePortrait, this, eMarginType.eDefaultMargins);
}
}
/// <summary>
/// constructor
/// </summary>
/// <param name="AResult"></param>
/// <param name="AParameters"></param>
/// <param name="APrinter"></param>
/// <param name="AWrapColumn">True: Wrap text in the column if it is to long. Otherwise cut it</param>
public TReportPrinterLayout(TResultList AResult, TParameterList AParameters, TPrinter APrinter, bool AWrapColumn) : base(AResult, AParameters,
APrinter)
{
FWrapColumn = AWrapColumn;
if (AParameters.Get("ReportWidth").ToDouble() > 20)
{
APrinter.Init(eOrientation.eLandscape, this, eMarginType.eDefaultMargins);
}
else
{
APrinter.Init(eOrientation.ePortrait, this, eMarginType.eDefaultMargins);
}
}
/// <summary>
/// get the family keys of the specified persons
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
public static DataTable GetFamilyKeys(TParameterList AParameters, TResultList AResults)
{
SortedList <string, string>Defines = new SortedList <string, string>();
List <OdbcParameter>SqlParameterList = new List <OdbcParameter>();
try
{
// prepare the sql statement parameters
AddPartnerSelectionParametersToSqlQuery(AParameters, Defines, SqlParameterList);
}
catch (Exception e)
{
TLogging.Log("problem while preparing sql statement for birthday report: " + e.ToString());
return null;
}
string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.GetFamilyKeyOfPerson.sql", Defines);
Boolean NewTransaction;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
try
{
// now run the database query
DataTable resultTable = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "result", Transaction,
SqlParameterList.ToArray());
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return null;
}
return resultTable;
}
catch (Exception e)
{
TLogging.Log(e.ToString());
return null;
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
}
/// <summary>
/// This formats the dates for different output, for example printing
/// </summary>
/// <param name="AParameters">the current parameters, environmnent variables, for formatting</param>
/// <param name="AOutputType">if this is 'Localized' then the dates are formatted in the format DD-MMM-YYYY</param>
/// <returns>s a new copy of the result, with the correct formatting
/// </returns>
public TResultList ConvertToFormattedStrings(TParameterList AParameters, String AOutputType)
{
TResultList ReturnValue = new TResultList(this);
Int32 i;
foreach (TResult r in ReturnValue.results)
{
for (i = 0; i <= 1; i++)
{
r.header[i] = new TVariant(r.header[i].ToFormattedString("", AOutputType));
}
for (i = 0; i <= 1; i++)
{
r.descr[i] = new TVariant(r.descr[i].ToFormattedString("", AOutputType));
}
for (i = 0; i < r.column.Length; i++)
{
if (r.column[i].TypeVariant == eVariantTypes.eString)
{
r.column[i] = new TVariant(r.column[i].ToString(), true);
}
else
{
// format thousands only or without decimals
if (StringHelper.IsCurrencyFormatString(r.column[i].FormatString) && AParameters.Exists("param_currency_format"))
{
r.column[i] = new TVariant(r.column[i].ToFormattedString(AParameters.Get(
"param_currency_format").ToString(), AOutputType), true);
}
else
{
r.column[i] = new TVariant(r.column[i].ToFormattedString("", AOutputType), true);
}
}
}
}
return(ReturnValue);
}
/// <summary>
/// calculate an extract from a report: all partners in selected relationships with selected partner
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
/// <returns></returns>
public static bool CalculateExtract(TParameterList AParameters, TResultList AResults)
{
string SqlStmt = "";
if (AParameters.Get("param_selection").ToString() == "an extract")
{
SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractFromExtractByPartnerRelationship.sql");
}
else if (AParameters.Get("param_selection").ToString() == "one partner")
{
SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractByPartnerRelationship.sql");
}
else
{
throw new ArgumentException("Must supply an extract or partner key.");
}
// create a new object of this class and control extract calculation from base class
QueryPartnerByRelationship ExtractQuery = new QueryPartnerByRelationship();
return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults);
}
} // Total Gifts Through Field Month
/// <summary>
/// Find all the gifts for a year, returning "worker", "field" and "total" results.
/// </summary>
public static DataTable TotalGiftsThroughFieldYear(TParameterList AParameters, TResultList AResults)
{
bool TaxDeductiblePercentageEnabled = Convert.ToBoolean(
TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, "FALSE"));
Int32 LedgerNum = AParameters.Get("param_ledger_number_i").ToInt32();
Int32 NumberOfYears = AParameters.Get("param_NumberOfYears").ToInt32();
string SqlQuery = "SELECT batch.a_gl_effective_date_d as Date, motive.a_report_column_c AS ReportColumn, ";
if (AParameters.Get("param_currency").ToString() == "Base")
{
SqlQuery += "detail.a_gift_amount_n AS Amount";
if (TaxDeductiblePercentageEnabled)
{
SqlQuery += ", detail.a_tax_deductible_amount_base_n AS TaxDeductAmount";
}
}
else
{
SqlQuery += "detail.a_gift_amount_intl_n AS Amount";
if (TaxDeductiblePercentageEnabled)
{
SqlQuery += ", detail.a_tax_deductible_amount_intl_n AS TaxDeductAmount";
}
}
SqlQuery += (" FROM PUB_a_gift as gift, PUB_a_gift_detail as detail, PUB_a_gift_batch as batch, PUB_a_motivation_detail AS motive"
+ " WHERE detail.a_ledger_number_i = " + LedgerNum +
" AND batch.a_batch_status_c = 'Posted'" +
" AND batch.a_batch_number_i = gift.a_batch_number_i" +
" AND batch.a_ledger_number_i = " + LedgerNum
+ " AND gift.a_ledger_number_i = " + LedgerNum +
" AND detail.a_batch_number_i = gift.a_batch_number_i" +
" AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i"
+ " AND motive.a_ledger_number_i = " + LedgerNum +
" AND motive.a_motivation_group_code_c = detail.a_motivation_group_code_c" +
" AND motive.a_motivation_detail_code_c = detail.a_motivation_detail_code_c" +
" AND motive.a_receipt_l=true");
DataTable tempTbl = null;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
tempTbl = DBAccess.GDBAccessObj.SelectDT(SqlQuery, "result", Transaction);
});
DataTable resultTable = new DataTable();
resultTable.Columns.Add("SummaryYear", typeof(Int32)); //
resultTable.Columns.Add("YearWorker", typeof(Decimal)); // These are the names of the variables
resultTable.Columns.Add("YearWorkerCount", typeof(Int32)); // returned by this calculation.
resultTable.Columns.Add("YearField", typeof(Decimal)); //
resultTable.Columns.Add("YearFieldCount", typeof(Int32)); //
resultTable.Columns.Add("YearTotal", typeof(Decimal)); //
resultTable.Columns.Add("YearTotalCount", typeof(Int32)); //
resultTable.Columns.Add("YearWorkerTaxDeduct", typeof(Decimal));
resultTable.Columns.Add("YearFieldTaxDeduct", typeof(Decimal));
resultTable.Columns.Add("YearTotalTaxDeduct", typeof(Decimal));
Int32 Year = DateTime.Now.Year;
for (Int32 YearIdx = 0; YearIdx < NumberOfYears; YearIdx++)
{
//TODO: Calendar vs Financial Date Handling - Check if financial year start/end should be used not calendar
string yearStart = String.Format("#{0:0000}-01-01#", Year - YearIdx);
string yearEnd = String.Format("#{0:0000}-12-31#", Year - YearIdx);
tempTbl.DefaultView.RowFilter = "Date >= " + yearStart + " AND Date < " + yearEnd;
Decimal WorkerTotal = 0;
Decimal FieldTotal = 0;
Int32 WorkerCount = 0;
Int32 FieldCount = 0;
Int32 TotalCount = tempTbl.DefaultView.Count;
Decimal WorkerTotalTaxDeduct = 0;
Decimal FieldTotalTaxDeduct = 0;
for (int i = 0; i < TotalCount; i++)
{
DataRow Row = tempTbl.DefaultView[i].Row;
if (Row["ReportColumn"].ToString() == "Worker")
{
WorkerCount++;
WorkerTotal += Convert.ToDecimal(Row["Amount"]);
if (TaxDeductiblePercentageEnabled)
{
WorkerTotalTaxDeduct += Convert.ToDecimal(Row["TaxDeductAmount"]);
}
}
else
{
FieldCount++;
FieldTotal += Convert.ToDecimal(Row["Amount"]);
if (TaxDeductiblePercentageEnabled)
{
FieldTotalTaxDeduct += Convert.ToDecimal(Row["TaxDeductAmount"]);
}
}
}
DataRow resultRow = resultTable.NewRow();
resultRow["SummaryYear"] = Year - YearIdx;
resultRow["YearWorker"] = WorkerTotal;
resultRow["YearWorkerCount"] = WorkerCount;
resultRow["YearField"] = FieldTotal;
resultRow["YearFieldCount"] = FieldCount;
resultRow["YearTotal"] = WorkerTotal + FieldTotal;
resultRow["YearTotalCount"] = TotalCount;
resultRow["YearWorkerTaxDeduct"] = WorkerTotalTaxDeduct;
resultRow["YearFieldTaxDeduct"] = FieldTotalTaxDeduct;
resultRow["YearTotalTaxDeduct"] = WorkerTotalTaxDeduct + FieldTotalTaxDeduct;
resultTable.Rows.Add(resultRow);
}
return resultTable;
}
/// <summary>
/// this is where all the calculations take place
/// </summary>
/// <returns>true if the report was successfully generated
/// </returns>
public Boolean GenerateResult(ref TParameterList parameterlist, ref TResultList resultlist, ref String AErrorMessage)
{
Boolean ReturnValue;
if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_REPORTING)
{
// for timing of reports
TLogging.Log("start calculating", TLoggingType.ToLogfile);
}
ReturnValue = false;
AErrorMessage = "";
try
{
this.Parameters = parameterlist;
if (!Parameters.Exists("calculateFromMethod"))
{
LoadReportDefinitionFiles(Parameters.Get("xmlfiles").ToString());
this.CurrentReport = this.ReportStore.Get(Parameters.Get("currentReport").ToString());
if (this.CurrentReport == null)
{
TLogging.Log("report \"" + Parameters.Get("currentReport").ToString() + "\" could not be found. XML file missing?");
return false;
}
InitColumns();
InitColumnsFormat();
}
InitParameterLedgers();
if (Parameters.Get("param_multiperiod").ToBool())
{
InitMultiPeriodColumns();
}
Results.SetMaxDisplayColumns(Parameters.Get("MaxDisplayColumns").ToInt());
if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_REPORTING)
{
Parameters.Save(Path.GetDirectoryName(TSrvSetting.ServerLogFile) + Path.DirectorySeparatorChar + "LogParamAfterPreproc.xml", true);
}
// to avoid still having in the status line: loading common.xml, although he is already working on the report
TLogging.Log("Preparing data for the report... ", TLoggingType.ToStatusBar);
if (Calculate())
{
if (Parameters.Get("CancelReportCalculation").ToBool() == true)
{
AErrorMessage = "Report calculation was cancelled";
return false;
}
resultlist = this.Results;
if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_REPORTING)
{
string FilePath = Path.GetDirectoryName(TSrvSetting.ServerLogFile) + Path.DirectorySeparatorChar;
Parameters.Save(FilePath + "LogParamAfterCalculation.xml", true);
Results.WriteCSV(Parameters, FilePath + Path.DirectorySeparatorChar + "ReportResults.csv", ",", true, false);
}
ReturnValue = true;
}
}
catch (Exception E)
{
TLogging.Log(E.StackTrace);
TLogging.Log(E.Message);
if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_REPORTING)
{
Parameters.Save(Path.GetDirectoryName(TSrvSetting.ServerLogFile) + Path.DirectorySeparatorChar + "LogAfterException.xml", true);
}
System.Console.WriteLine(E.StackTrace);
AErrorMessage = E.Message;
}
if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_REPORTING)
{
// for timing of reports
TLogging.Log("finished calculating", TLoggingType.ToLogfile);
}
return ReturnValue;
}
public static Boolean CreateFamilyExtractForPersons(int ABaseExtractId, ref int AExtractId, String AExtractName, String AExtractDescription)
{
Boolean ResultValue = false;
TParameterList ParameterList = new TParameterList();
TResultList ResultList = new TResultList();
ParameterList.Add("param_base_extract", ABaseExtractId);
ParameterList.Add("param_extract_name", AExtractName);
ParameterList.Add("param_extract_description", AExtractDescription);
ResultValue = Ict.Petra.Server.MPartner.queries.QueryFamilyExtractForPersons.CalculateExtract
(ParameterList, ResultList, out AExtractId);
return ResultValue;
}
/// <summary>
/// calculate an extract from a report: all partners of a given type (or selection of multiple types)
/// </summary>
/// <param name="AParameters"></param>
/// <param name="ASqlStmt"></param>
/// <param name="AResults"></param>
/// <param name="AExtractId"></param>
/// <returns></returns>
protected bool CalculateExtractInternal(TParameterList AParameters, string ASqlStmt, TResultList AResults, out int AExtractId)
{
Boolean ReturnValue = false;
Boolean NewTransaction;
TDBTransaction Transaction;
List <OdbcParameter>SqlParameterList = new List <OdbcParameter>();
bool AddressFilterAdded;
AExtractId = -1;
Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);
// get the partner keys from the database
try
{
if (FSpecialTreatment)
{
ReturnValue = RunSpecialTreatment(AParameters, Transaction, out AExtractId);
}
else
{
// call to derived class to retrieve parameters specific for extract
RetrieveParameters(AParameters, ref ASqlStmt, ref SqlParameterList);
// add address filter information to sql statement and parameter list
AddressFilterAdded = AddAddressFilter(AParameters, ref ASqlStmt, ref SqlParameterList);
// now run the database query
TLogging.Log("getting the data from the database", TLoggingType.ToStatusBar);
DataTable partnerkeys = DBAccess.GDBAccessObj.SelectDT(ASqlStmt, "partners", Transaction,
SqlParameterList.ToArray());
// filter data by postcode (if applicable)
PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, Transaction);
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
// TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return false;
}
TLogging.Log("preparing the extract", TLoggingType.ToStatusBar);
// create an extract with the given name in the parameters
ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys(
AParameters.Get("param_extract_name").ToString(),
AParameters.Get("param_extract_description").ToString(),
out AExtractId,
partnerkeys,
0,
AddressFilterAdded,
true);
}
if (ReturnValue)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
else
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return ReturnValue;
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured in CalculateExtractInternal:" + Environment.NewLine + Exc.ToString());
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
throw;
}
}
/// <summary>
/// constructor
/// </summary>
/// <param name="ACallerForm"></param>
/// <param name="caption">caption of the dialog</param>
/// <param name="duration"></param>
/// <param name="results"></param>
/// <param name="parameters"></param>
/// <param name="AWrapColumn">True: Wrap the text in the column. False: Cut the text that is too long for the column</param>
/// <param name="ACalculator"></param>
public TFrmPrintPreview(Form ACallerForm, String caption, TimeSpan duration, TResultList results,
TParameterList parameters, bool AWrapColumn, TRptCalculator ACalculator)
: base()
{
FPetraUtilsObject = new Ict.Petra.Client.CommonForms.TFrmPetraUtils(ACallerForm, this, stbMain);
FCalculator = ACalculator;
//
// Required for Windows Form Designer support
//
InitializeComponent();
#region CATALOGI18N
// this code has been inserted by GenerateI18N, all changes in this region will be overwritten by GenerateI18N
this.mniFile.Text = Catalog.GetString("&File");
this.mniFileClose.Text = Catalog.GetString("&Close");
this.mniFileClose.ToolTipText = Catalog.GetString("Close the preview");
this.mniHelp.Text = Catalog.GetString("&Help");
this.mniHelpPetraHelp.Text = Catalog.GetString("&Petra Help");
this.mniHelpBugReport.Text = Catalog.GetString("Bug &Report");
this.mniHelpAboutPetra.Text = Catalog.GetString("&About OpenPetra...");
this.mniHelpDevelopmentTeam.Text = Catalog.GetString("&The Development Team...");
this.stbMain.Text = Catalog.GetString("Status Bar");
this.stpInfo.Text = Catalog.GetString("Ready");
this.tbbClose.Text = Catalog.GetString("Close");
this.tbbClose.ToolTipText = Catalog.GetString("Closes this window");
this.Text = Catalog.GetString("Print Preview");
this.tbpText.Text = Catalog.GetString("Text Preview");
this.txtOutput.Text = Catalog.GetString("Text Output");
this.tbpPreview.Text = Catalog.GetString("Print Preview");
this.lblNoPrinter.Text = Catalog.GetString("Unfortunately this function is disabled. Please install a printer to use this page.");
this.CbB_Zoom.Text = Catalog.GetString("Select Zoom");
this.Btn_PreviousPage.Text = Catalog.GetString("Previous Page");
this.Btn_NextPage.Text = Catalog.GetString("Next Page");
this.tbpGridView.Text = Catalog.GetString("Detail Reports");
this.tbtPrint.Text = Catalog.GetString("Print");
this.tbtPrint.ToolTipText = Catalog.GetString("Print the report");
this.tbtExportCSV.Text = Catalog.GetString("Export to CSV");
this.tbtExportCSV.ToolTipText = Catalog.GetString("Export to CSV text file");
this.tbtExportExcelFile.Text = Catalog.GetString("Export to Excel");
this.tbtExportExcelFile.ToolTipText = Catalog.GetString("Export to Excel xlsx file or directly into Excel, if it is available");
this.tbtExportText.Text = Catalog.GetString("Save as Text file");
this.tbtExportText.ToolTipText = Catalog.GetString("Save as a text file (e.g. for email)");
this.tbtSendEmail.Text = Catalog.GetString("Send Email");
this.tbtSendEmail.ToolTipText = Catalog.GetString("Send the Report as an Email with Excel attachment");
this.tbtCreateExtract.Text = Catalog.GetString("Create Extract");
this.tbtCreateExtract.ToolTipText = Catalog.GetString("Create an Extract from this result set");
this.tbtGenerateChart.Text = Catalog.GetString("Generate Chart");
this.tbtGenerateChart.ToolTipText = Catalog.GetString(
"Generates a chart in Excel (not available for all reports)");
this.Text = Catalog.GetString("Print Preview");
#endregion
System.Windows.Forms.TabPage SelectedTab;
this.Text = this.Text + ": " + caption;
this.ReportName = caption;
this.Results = results;
this.Parameters = parameters;
FTxtPrinter = new TTxtPrinter();
this.ReportTxtPrinter = new TReportPrinterLayout(Results, Parameters, FTxtPrinter, AWrapColumn);
ReportTxtPrinter.PrintReport();
this.txtOutput.Lines = FTxtPrinter.GetArrayOfString();
FPrinterInstalled = this.PrintDocument.PrinterSettings.IsValid;
FWrapColumn = AWrapColumn;
if (FPrinterInstalled)
{
this.tabPreview.SelectedTab = tbpPreview;
FGfxPrinter = new TGfxPrinter(this.PrintDocument, TGfxPrinter.ePrinterBehaviour.eReport);
// TReportPrinterLayout ReportGfxPrinter
new TReportPrinterLayout(Results, Parameters, FGfxPrinter, AWrapColumn);
this.PrintPreviewControl.Document = FGfxPrinter.Document;
this.PrintPreviewControl.Zoom = 1; // show 100% by default
this.PrintPreviewControl.UseAntiAlias = true;
this.lblNoPrinter.Visible = false;
}
else
{
// PrintPreviewControl.CalculatePageInfo will throw InvalidPrinterException
this.tabPreview.SelectedTab = tbpText;
this.PrintPreviewControl.Visible = false;
this.CbB_Zoom.Enabled = false;
this.Btn_PreviousPage.Enabled = false;
this.Btn_NextPage.Enabled = false;
}
this.PrintChartProcedure = null;
this.PrintChartProcedureValid = false;
sgGridView.SortableHeaders = false;
sgGridView.SelectionMode = SourceGrid.GridSelectionMode.Cell;
this.sgGridView.DoubleClickCell += new TDoubleClickCellEventHandler(this.SgGridView_DoubleClickCell);
FGridPreview = new TGridPreview(this, FPetraUtilsObject, @PreviewDetailReport, Results, Parameters);
if (!FGridPreview.PopulateResultGrid(sgGridView))
{
SelectedTab = tabPreview.SelectedTab;
this.tabPreview.TabPages.Clear();
this.tabPreview.TabPages.AddRange(new TabPage[] { this.tbpText, this.tbpPreview });
tabPreview.SelectedTab = SelectedTab;
}
else
{
FGridPreview.PopulateGridContextMenu(ContextMenu1);
}
stpInfo.Text = string.Empty;
stbMain.ShowMessage(String.Format(Catalog.GetString("It took {0} to calculate the report"), FormatDuration(duration)));
}
/// <summary>
/// calculate an extract from a report: all partners of a given type (or selection of multiple types)
/// </summary>
/// <param name="AParameters"></param>
/// <param name="ASqlStmt"></param>
/// <param name="AResults"></param>
/// <param name="AExtractId"></param>
/// <returns></returns>
protected bool CalculateExtractInternal(TParameterList AParameters, string ASqlStmt, TResultList AResults, out int AExtractId)
{
Boolean ReturnValue = false;
TDBTransaction Transaction = null;
bool SubmissionOK = false;
List <OdbcParameter>SqlParameterList = new List <OdbcParameter>();
bool AddressFilterAdded;
int ExtractId = -1;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK,
delegate
{
// get the partner keys from the database
if (FSpecialTreatment)
{
ReturnValue = RunSpecialTreatment(AParameters, Transaction, out ExtractId);
}
else
{
// call to derived class to retrieve parameters specific for extract
RetrieveParameters(AParameters, ref ASqlStmt, ref SqlParameterList);
// add address filter information to sql statement and parameter list
AddressFilterAdded = AddAddressFilter(AParameters, ref ASqlStmt, ref SqlParameterList);
// now run the database query
TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);
DataTable partnerkeys = DBAccess.GDBAccessObj.SelectDT(ASqlStmt, "partners", Transaction,
SqlParameterList.ToArray());
// filter data by postcode (if applicable)
PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, Transaction);
// 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)
{
ReturnValue = false;
}
else
{
TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar);
// create an extract with the given name in the parameters
ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys(
AParameters.Get("param_extract_name").ToString(),
AParameters.Get("param_extract_description").ToString(),
out ExtractId,
partnerkeys,
0,
AddressFilterAdded);
}
}
if (ReturnValue)
{
SubmissionOK = true;
}
});
AExtractId = ExtractId;
return ReturnValue;
}
/// <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>
/// get all partners that we want to display on the current birthday report
/// </summary>
public static DataTable CalculateBirthdays(TParameterList AParameters, TResultList AResults)
{
SortedList <string, string>Defines = new SortedList <string, string>();
List <OdbcParameter>SqlParameterList = new List <OdbcParameter>();
try
{
// prepare the sql statement parameters
if (AParameters.Exists("FamilyKey"))
{
SqlParameterList.Add(new OdbcParameter("FamilyKey", OdbcType.Decimal)
{
Value = AParameters.Get("FamilyKey").ToDecimal()
});
Defines.Add("BYFAMILYKEY", string.Empty);
}
else
{
AddPartnerSelectionParametersToSqlQuery(AParameters, Defines, SqlParameterList);
}
if (AParameters.Get("param_chkSelectTypes").ToBool() == true)
{
string[] types = AParameters.Get("param_typecode").ToString().Split(new char[] { ',' });
string FilterForTypes = string.Empty;
foreach (string type in types)
{
if (FilterForTypes.Length > 0)
{
FilterForTypes += " OR ";
}
FilterForTypes += "pptype.p_type_code_c = ?";
SqlParameterList.Add(new OdbcParameter("typecode" + FilterForTypes.Length, OdbcType.VarChar)
{
Value = type
});
}
Defines.Add("SELECTTYPES", "(" + FilterForTypes + ")");
}
if (AParameters.Get("param_chkUseDate").ToBool() == true)
{
DateTime FromDate = AParameters.Get("param_dtpFromDate").ToDate();
DateTime ToDate = AParameters.Get("param_dtpToDate").ToDate();
if (FromDate.DayOfYear < ToDate.DayOfYear)
{
Defines.Add("WITHDATERANGE", string.Empty);
SqlParameterList.Add(new OdbcParameter("startdate", OdbcType.Date)
{
Value = FromDate
});
SqlParameterList.Add(new OdbcParameter("enddate", OdbcType.Date)
{
Value = ToDate
});
}
else
{
Defines.Add("WITHOUTDATERANGE", string.Empty);
SqlParameterList.Add(new OdbcParameter("enddate", OdbcType.Date)
{
Value = ToDate
});
SqlParameterList.Add(new OdbcParameter("startdate", OdbcType.Date)
{
Value = FromDate
});
}
}
}
catch (Exception e)
{
TLogging.Log("problem while preparing sql statement for birthday report: " + e.ToString());
return null;
}
string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.Birthday.sql", Defines);
Boolean NewTransaction;
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);
DataTable resultTable = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "result", Transaction,
SqlParameterList.ToArray());
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return null;
}
// if end date is not set, use the end of this year
DateTime AgeDay = DateTime.Now;
if (AParameters.Get("param_chkUseDate").ToBool() == true)
{
AgeDay = AParameters.Get("param_dtpToDate").ToDate();
}
else
{
AgeDay = new DateTime(AgeDay.Year, 12, 31);
}
// Calculate the age, in new column
resultTable.Columns.Add(new DataColumn("age", typeof(Int32)));
foreach (DataRow r in resultTable.Rows)
{
int age = 0;
if (r["DOB"] != DBNull.Value)
{
DateTime BDay = Convert.ToDateTime(r["DOB"]);
age = AgeDay.Year - BDay.Year;
}
r["Age"] = age;
}
// filter by anniversaries?
if ((AParameters.Get("param_chkAnniversaries").ToBool() == true)
&& !AParameters.Get("param_txtAnniversaries").IsZeroOrNull())
{
List <string>anniversaries = new List <string>(AParameters.Get("param_txtAnniversaries").ToString().Split(new char[] { ',' }));
List <DataRow>RowsToDelete = new List <DataRow>();
foreach (DataRow r in resultTable.Rows)
{
if (!anniversaries.Contains(r["Age"].ToString()))
{
RowsToDelete.Add(r);
}
}
foreach (DataRow r in RowsToDelete)
{
resultTable.Rows.Remove(r);
}
}
return resultTable;
}
catch (Exception e)
{
TLogging.Log(e.ToString());
return null;
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
}
/// <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>
/// 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>
/// this is where all the calculations take place
/// </summary>
/// <returns>
/// true if the report was successfully generated
/// </returns>
public Boolean GenerateResultRemoteClient()
{
Boolean ReturnValue;
Thread ProgressCheckThread;
ReturnValue = false;
FReportingGenerator = TRemote.MReporting.UIConnectors.ReportGenerator();
FKeepUpProgressCheck = true;
try
{
this.Results = new TResultList();
FReportingGenerator.Start(this.Parameters.ToDataTable());
ProgressCheckThread = new Thread(new ThreadStart(AsyncProgressCheckThread));
ProgressCheckThread.Start();
}
catch (Exception e)
{
TLogging.Log(e.Message);
// Release the server object
FReportingGenerator = null;
return false;
}
// todo: allow canceling of the calculation of a report
while (FKeepUpProgressCheck)
{
Thread.Sleep(500);
}
ReturnValue = FReportingGenerator.GetSuccess();
// Do not release the server object, we still might want to send an email...
// FReportingGenerator = null;
if (ReturnValue)
{
if (FCalculatesExtract)
{
TLogging.Log("Extract calculation finished. Look for extract '" +
Parameters.Get("param_extract_name").ToString() +
"' in Extract Master List.", TLoggingType.ToStatusBar);
TFormsMessage BroadcastMessage = new TFormsMessage(TFormsMessageClassEnum.mcExtractCreated);
BroadcastMessage.SetMessageDataName(Parameters.Get("param_extract_name").ToString());
TFormsList.GFormsList.BroadcastFormMessage(BroadcastMessage);
}
else
{
TLogging.Log("Report calculation finished.", TLoggingType.ToStatusBar);
}
}
return ReturnValue;
}
/// <summary>
/// calculate the report and save the result and returned parameters to file
/// </summary>
public static void CalculateReport(string AReportParameterXmlFile, TParameterList ASpecificParameters, int ALedgerNumber = -1)
{
// important: otherwise month names are in different language, etc
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-GB", false);
TReportGeneratorUIConnector ReportGenerator = new TReportGeneratorUIConnector();
TParameterList Parameters = new TParameterList();
string resultFile = AReportParameterXmlFile.Replace(".xml", ".Results.xml");
string parameterFile = AReportParameterXmlFile.Replace(".xml", ".Parameters.xml");
Parameters.Load(AReportParameterXmlFile);
if (ALedgerNumber != -1)
{
Parameters.Add("param_ledger_number_i", ALedgerNumber);
}
Parameters.Add(ASpecificParameters);
ReportGenerator.Start(Parameters.ToDataTable());
while (!ReportGenerator.Progress.JobFinished)
{
Thread.Sleep(500);
}
Assert.IsTrue(ReportGenerator.GetSuccess(), "Report did not run successfully");
TResultList Results = new TResultList();
Results.LoadFromDataTable(ReportGenerator.GetResult());
Parameters.LoadFromDataTable(ReportGenerator.GetParameter());
if (!Parameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT1, -1, eParameterFit.eBestFit))
{
Parameters.Add("ControlSource", new TVariant("Left1"), ReportingConsts.HEADERPAGELEFT1);
}
if (!Parameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT2, -1, eParameterFit.eBestFit))
{
Parameters.Add("ControlSource", new TVariant("Left2"), ReportingConsts.HEADERPAGELEFT2);
}
Parameters.Save(parameterFile, false);
Results.WriteCSV(Parameters, resultFile, ",", false, false);
}
/// <summary>
/// this is where all the calculations take place
/// </summary>
/// <returns>true if the report was successfully generated
/// </returns>
public Boolean GenerateResultRemoteClient()
{
Boolean ReturnValue;
Thread ProgressCheckThread;
ReturnValue = false;
FReportingGenerator = TRemote.MReporting.UIConnectors.ReportGenerator();
FKeepUpProgressCheck = true;
// Register Object with the TEnsureKeepAlive Class so that it doesn't get GC'd
TEnsureKeepAlive.Register(FReportingGenerator);
try
{
this.Results = new TResultList();
FReportingGenerator.Start(this.Parameters.ToDataTable());
ProgressCheckThread = new Thread(new ThreadStart(AsyncProgressCheckThread));
ProgressCheckThread.Start();
}
catch (Exception e)
{
TLogging.Log(e.Message);
// UnRegister Object from the TEnsureKeepAlive Class so that the Object can get GC'd on the PetraServer
TEnsureKeepAlive.UnRegister(FReportingGenerator);
return false;
}
// todo: allow canceling of the calculation of a report
while (FKeepUpProgressCheck)
{
Thread.Sleep(500);
}
ReturnValue = FReportingGenerator.GetSuccess();
// UnRegister Object from the TEnsureKeepAlive Class so that the Object can get GC'd on the PetraServer
TEnsureKeepAlive.UnRegister(FReportingGenerator);
if (ReturnValue)
{
if (FCalculatesExtract)
{
TLogging.Log("Extract calculation finished. Look for extract '" +
Parameters.Get("param_extract_name").ToString() +
"' in Extract Master List.", TLoggingType.ToStatusBar);
TFormsMessage BroadcastMessage = new TFormsMessage(TFormsMessageClassEnum.mcExtractCreated);
BroadcastMessage.SetMessageDataName(Parameters.Get("param_extract_name").ToString());
TFormsList.GFormsList.BroadcastFormMessage(BroadcastMessage);
}
else
{
TLogging.Log("Report calculation finished.", TLoggingType.ToStatusBar);
}
}
return ReturnValue;
}
} // Select Gift Recipients
/// <summary>
/// Find all the gifts for a worker, presenting the results in four year columns.
/// NOTE - the user can select the field of the donor.
///
/// All the DB work was previously done by the Select Gift Recipients function above.
/// I only need to filter the table by recipientKey.
/// </summary>
/// <returns></returns>
public static DataTable SelectGiftDonors(TParameterList AParameters, TResultList AResults)
{
Int64 recipientKey = AParameters.Get("RecipientKey").ToInt64();
TotalGiftsPerRecipient.DefaultView.RowFilter = "RecipientKey = " + recipientKey.ToString();
DataTable resultTable = TotalGiftsPerRecipient.DefaultView.ToTable(true,
new String[] { "DonorKey", "DonorName", "DonorClass", "YearTotal0", "YearTotal1", "YearTotal2", "YearTotal3" });
return resultTable;
}
/// <summary>
/// ...
/// </summary>
public static void PrintTxt(TResultList results, TParameterList parameters, string output)
{
TReportPrinterLayout reportTxtPrinter;
TTxtPrinter txtPrinter;
txtPrinter = new TTxtPrinter();
reportTxtPrinter = new TReportPrinterLayout(results, parameters, txtPrinter, true);
reportTxtPrinter.PrintReport();
txtPrinter.WriteToFile(output);
}
/// <summary>
/// constructor
/// </summary>
/// <param name="AResultList"></param>
/// <param name="AParameters"></param>
public TReportExcel(TResultList AResultList, TParameterList AParameters)
{
results = AResultList.ConvertToFormattedStrings(AParameters, "CSV");
parameters = AParameters.ConvertToFormattedStrings("CSV");
}
/// <summary>
/// calculate an extract from a report: all partners of a given type (or selection of multiple types)
/// </summary>
/// <param name="AParameters"></param>
/// <param name="ASqlStmt"></param>
/// <param name="AResults"></param>
/// <returns></returns>
protected bool CalculateExtractInternal(TParameterList AParameters, string ASqlStmt, TResultList AResults)
{
int ExtractId;
return CalculateExtractInternal(AParameters, ASqlStmt, AResults, out ExtractId);
}
/// <summary>
/// Find the latest gift that was given by a donor
/// </summary>
/// <param name="AParameters">report parameter list</param>
/// <param name="AResults">result list</param>
/// <returns>DataTable with row for last gift by donor</returns>
public static DataTable SelectLatestGiftRow(TParameterList AParameters, TResultList AResults)
{
Int64 DonorKey = AParameters.Get("PartnerKey").ToInt64();
String StrSql = "SELECT DISTINCT " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetLedgerNumberDBName() + ", " +
AGiftTable.GetTableDBName() + "." + AGiftTable.GetBatchNumberDBName() + ", " +
AGiftTable.GetTableDBName() + "." + AGiftTable.GetGiftTransactionNumberDBName() + ", " +
AGiftTable.GetTableDBName() + "." + AGiftTable.GetDateEnteredDBName();
StrSql = StrSql +
" FROM " + AGiftTable.GetTableDBName() +
" WHERE " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDonorKeyDBName() + " = " + DonorKey.ToString() +
" ORDER BY " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDateEnteredDBName() + " DESC";
TDBTransaction Transaction = null;
DataTable tempTbl = new DataTable();
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
tempTbl = DBAccess.GDBAccessObj.SelectDT(StrSql, "result", Transaction);
});
DataTable resultTbl = tempTbl.Clone();
resultTbl.Clear();
if (tempTbl.Rows.Count > 0)
{
resultTbl.Rows.Add((object[])tempTbl.Rows[0].ItemArray.Clone());
}
return resultTbl;
}
/// <summary>
/// Find recipient Partner Key and name for all partners who received gifts in the timeframe.
/// NOTE - the user can select the PartnerType of the recipient.
///
/// With only a little more load on the DB, I can get all the data that the report will need,
/// and store it in a DataTable local to this class, so that when more detailed data is requested below,
/// I don't need another DB query.
/// </summary>
/// <returns>RecipientKey, RecipientName</returns>
public static DataTable SelectGiftRecipients(TParameterList AParameters, TResultList AResults)
{
Int32 LedgerNum = AParameters.Get("param_ledger_number_i").ToInt32();
Boolean onlySelectedTypes = AParameters.Get("param_type_selection").ToString() == "selected_types";
Boolean onlySelectedFields = AParameters.Get("param_field_selection").ToString() == "selected_fields";
Boolean fromExtract = AParameters.Get("param_recipient").ToString() == "Extract";
Boolean oneRecipient = AParameters.Get("param_recipient").ToString() == "One Recipient";
String period0Start = AParameters.Get("param_from_date_0").ToDate().ToString("yyyy-MM-dd");
String period0End = AParameters.Get("param_to_date_0").ToDate().ToString("yyyy-MM-dd");
String period1Start = AParameters.Get("param_from_date_1").ToDate().ToString("yyyy-MM-dd");
String period1End = AParameters.Get("param_to_date_1").ToDate().ToString("yyyy-MM-dd");
String period2Start = AParameters.Get("param_from_date_2").ToDate().ToString("yyyy-MM-dd");
String period2End = AParameters.Get("param_to_date_2").ToDate().ToString("yyyy-MM-dd");
String period3Start = AParameters.Get("param_from_date_3").ToDate().ToString("yyyy-MM-dd");
String period3End = AParameters.Get("param_to_date_3").ToDate().ToString("yyyy-MM-dd");
String amountFieldName = (AParameters.Get("param_currency").ToString() == "International") ?
"detail.a_gift_amount_intl_n" : "detail.a_gift_amount_n";
string SqlQuery = "SELECT DISTINCT " +
"gift.p_donor_key_n AS DonorKey, " +
"donor.p_partner_short_name_c AS DonorName, donor.p_partner_class_c AS DonorClass, " +
"recipient.p_partner_key_n AS RecipientKey, " +
"recipient.p_partner_short_name_c AS RecipientName, " +
"SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period0Start + "' AND '" + period0End + "' " +
"THEN " + amountFieldName + " ELSE 0 END )as YearTotal0, " +
"SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period1Start + "' AND '" + period1End + "' " +
"THEN " + amountFieldName + " ELSE 0 END )as YearTotal1, " +
"SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period2Start + "' AND '" + period2End + "' " +
"THEN " + amountFieldName + " ELSE 0 END )as YearTotal2, " +
"SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period3Start + "' AND '" + period3End + "' " +
"THEN " + amountFieldName + " ELSE 0 END )as YearTotal3 " +
"FROM PUB_a_gift as gift, PUB_a_gift_detail as detail, PUB_a_gift_batch AS GiftBatch, PUB_p_partner AS donor, PUB_p_partner AS recipient ";
if (onlySelectedTypes)
{
SqlQuery += ", PUB_p_partner_type AS RecipientType ";
}
if (fromExtract)
{
String extractName = AParameters.Get("param_extract_name").ToString();
SqlQuery += (", PUB_m_extract AS Extract, PUB_m_extract_master AS ExtractMaster " +
"WHERE " +
"recipient.p_partner_key_n = Extract.p_partner_key_n " +
"AND Extract.m_extract_id_i = ExtractMaster.m_extract_id_i " +
"AND ExtractMaster.m_extract_name_c = '" + extractName + "' " +
"AND "
);
}
else
{
SqlQuery += "WHERE ";
}
SqlQuery += ("detail.a_ledger_number_i = " + LedgerNum + " " +
"AND detail.p_recipient_key_n = recipient.p_partner_key_n " +
"AND gift.p_donor_key_n = donor.p_partner_key_n " +
"AND detail.a_batch_number_i = gift.a_batch_number_i " +
"AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i " +
"AND gift.a_date_entered_d BETWEEN '" + period3Start + "' AND '" + period0End + "' " +
"AND gift.a_ledger_number_i = " + LedgerNum + " " +
"AND GiftBatch.a_batch_status_c = 'Posted' " +
"AND GiftBatch.a_batch_number_i = gift.a_batch_number_i " +
"AND GiftBatch.a_ledger_number_i = " + LedgerNum + " "
);
if (oneRecipient)
{
String recipientKey = AParameters.Get("param_recipient_key").ToString();
SqlQuery += ("AND recipient.p_partner_key_n = " + recipientKey + " ");
}
if (onlySelectedFields)
{
String selectedFieldList = AParameters.Get("param_clbFields").ToString();
selectedFieldList = selectedFieldList.Replace('\'', ' ');
SqlQuery += ("AND detail.a_recipient_ledger_number_n IN (" + selectedFieldList + ") ");
}
if (onlySelectedTypes)
{
String selectedTypeList = "'" + AParameters.Get("param_clbTypes").ToString() + "'";
selectedTypeList = selectedTypeList.Replace(",", "','");
SqlQuery += ("AND RecipientType.p_partner_key_n = detail.p_recipient_key_n " +
"AND RecipientType.p_type_code_c IN (" + selectedTypeList + ") ");
}
SqlQuery +=
(
"GROUP by gift.p_donor_key_n, donor.p_partner_short_name_c, donor.p_partner_class_c, recipient.p_partner_key_n, recipient.p_partner_short_name_c "
+
"ORDER BY recipient.p_partner_short_name_c");
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
TotalGiftsPerRecipient = DBAccess.GDBAccessObj.SelectDT(SqlQuery, "result", Transaction);
});
//
// Ok, I've got a DataTable with ALL THE DATA I need for the report,
// but for this calculation I only want a list of partner keys and names...
//
DataTable resultTable = new DataTable();
resultTable.Columns.Add("RecipientKey", typeof(String)); // These are the names of the variables
resultTable.Columns.Add("RecipientName", typeof(String)); // returned by this calculation.
Int64 previousPartner = -1;
foreach (DataRow Row in TotalGiftsPerRecipient.Rows)
{
Int64 partnerKey = Convert.ToInt64(Row["RecipientKey"]);
if (partnerKey != previousPartner)
{
previousPartner = partnerKey;
DataRow NewRow = resultTable.NewRow();
NewRow["RecipientKey"] = Row["RecipientKey"];
NewRow["RecipientName"] = Row["RecipientName"];
resultTable.Rows.Add(NewRow);
}
}
return resultTable;
} // Select Gift Recipients
/// <summary>
/// constructor
/// </summary>
/// <param name="AResult"></param>
/// <param name="AParameters"></param>
/// <param name="APrinter"></param>
public TReportPrinterCommon(TResultList AResult, TParameterList AParameters, TPrinter APrinter)
{
// go through all results and parameters and replace the unformatted and encoded date
// the whole point is to format the dates differently, depending on the output (printer vs. CSV)
FParameters = AParameters.ConvertToFormattedStrings("Localized");
FResultList = AResult.ConvertToFormattedStrings(FParameters, "Localized");
FResults = FResultList.GetResults();
FLowestLevel = FParameters.Get("lowestLevel").ToInt();
FTimePrinted = DateTime.Now;
FPrinter = APrinter;
}