/// <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);
        }
Esempio n. 5
0
        /// <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);
        }
Esempio n. 7
0
 /// <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;
 }
Esempio n. 8
0
 /// <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)
 {
 }
Esempio n. 9
0
 /// <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;
 }
Esempio n. 10
0
        /// <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);
            }
        }
Esempio n. 11
0
        /// <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);
            }
        }
Esempio n. 12
0
        /// <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();
                }
            }
        }
Esempio n. 13
0
        /// <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);
        }
Esempio n. 15
0
        } // 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;
        }
Esempio n. 16
0
        /// <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;
        }
Esempio n. 17
0
        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;
        }
Esempio n. 18
0
        /// <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;
            }
        }
Esempio n. 19
0
        /// <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)));
        }
Esempio n. 20
0
        /// <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;
        }
Esempio n. 21
0
 /// <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;
 }
Esempio n. 22
0
        /// <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();
                }
            }
        }
Esempio n. 23
0
 /// <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;
 }
Esempio n. 24
0
 /// <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;
 }
Esempio n. 25
0
        /// <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;
        }
Esempio n. 26
0
        /// <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);
        }
Esempio n. 27
0
        /// <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;
        }
Esempio n. 28
0
        } // 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;
        }
Esempio n. 29
0
        /// <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);
        }
Esempio n. 30
0
 /// <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");
 }
Esempio n. 31
0
        /// <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);
        }
Esempio n. 32
0
        /// <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;
        }
Esempio n. 33
0
        /// <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
Esempio n. 34
0
 /// <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;
 }