Ejemplo n.º 1
0
        /// <summary>
        /// Retrieves the Partner ShortName, the PartnerClass and PartnerStatus.
        /// </summary>
        /// <param name="APartnerKey">PartnerKey to identify the Partner.</param>
        /// <param name="APartnerShortName">Returns the ShortName.</param>
        /// <param name="APartnerClass">Returns the PartnerClass (FAMILY, ORGANISATION, etc).</param>
        /// <param name="APartnerStatus">Returns the PartnerStatus (eg. ACTIVE, DIED).</param>
        /// <param name="ATransaction">Open DB Transaction.</param>
        /// <returns>True if partner was found, otherwise false.</returns>
        public static Boolean RetrievePartnerShortName(Int64 APartnerKey,
                                                       out String APartnerShortName,
                                                       out TPartnerClass APartnerClass,
                                                       out TStdPartnerStatusCode APartnerStatus,
                                                       TDBTransaction ATransaction)
        {
            Boolean          ReturnValue;
            StringCollection RequiredColumns;
            PPartnerTable    PartnerTable;

            // initialise out Arguments
            APartnerShortName = "";

            // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
            APartnerClass = TPartnerClass.FAMILY;

            // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
            APartnerStatus = TStdPartnerStatusCode.spscINACTIVE;

            if (APartnerKey != 0)
            {
                // only some fields are needed
                RequiredColumns = new StringCollection();
                RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName());
                RequiredColumns.Add(PPartnerTable.GetPartnerClassDBName());
                RequiredColumns.Add(PPartnerTable.GetStatusCodeDBName());

                PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, RequiredColumns, ATransaction, null, 0, 0);

                if (PartnerTable.Rows.Count == 0)
                {
                    ReturnValue = false;
                }
                else
                {
                    // since we loaded by primary key there must just be one partner row
                    APartnerShortName = PartnerTable[0].PartnerShortName;
                    APartnerClass     = SharedTypes.PartnerClassStringToEnum(PartnerTable[0].PartnerClass);

                    APartnerStatus = SharedTypes.StdPartnerStatusCodeStringToEnum(PartnerTable[0].StatusCode);
                    ReturnValue    = true;
                }
            }
            else
            {
                // Return result as valid if Partner Key is 0.
                ReturnValue = true;
            }

            return(ReturnValue);
        }
Ejemplo n.º 2
0
        private DataTable GetConferenceListTable(TDBTransaction AReadTransaction, string ATableName)
        {
            DataTable Table;

            DataColumn[] Key = new DataColumn[1];

            // Used eg. Select Event Dialog
            Table = DBAccess.GetDBAccessObj(AReadTransaction).SelectDT(
                "SELECT DISTINCT " +
                PPartnerTable.GetPartnerShortNameDBName() +
                ", " + PPartnerTable.GetPartnerClassDBName() +
                ", " + PUnitTable.GetOutreachCodeDBName() +
                ", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() +
                ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() +
                ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName() +
                ", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
                ", " + PUnitTable.GetUnitTypeCodeDBName() +

                " FROM PUB." + PPartnerTable.GetTableDBName() +
                ", PUB." + PUnitTable.GetTableDBName() +
                ", PUB." + PLocationTable.GetTableDBName() +
                ", PUB." + PPartnerLocationTable.GetTableDBName() +
                ", PUB." + PCountryTable.GetTableDBName() +

                " WHERE " +
                PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
                PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND " +
                PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
                PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetPartnerKeyDBName() + " AND " +

                PLocationTable.GetTableDBName() + "." + PLocationTable.GetSiteKeyDBName() + " = " +
                PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + " AND " +
                PLocationTable.GetTableDBName() + "." + PLocationTable.GetLocationKeyDBName() + " = " +
                PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + " AND " +
                PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryCodeDBName() + " = " +
                PLocationTable.GetTableDBName() + "." + PLocationTable.GetCountryCodeDBName() + " AND " +


                PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' AND " +
                PPartnerTable.GetPartnerClassDBName() + " = 'UNIT' AND (" +
                PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONF%' OR " +
                PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONG%')"
                ,
                ATableName, AReadTransaction);

            Key[0]           = Table.Columns[PPartnerTable.GetPartnerKeyDBName()];
            Table.PrimaryKey = Key;

            return(Table);
        }
        /// <summary>
        /// todoComment
        /// </summary>
        /// <returns></returns>
        public String DetermineCurrentPartnerClass()
        {
            DataRow CurrentDR = this.CurrentDataRow;
            String  PartnerClass;

            if (CurrentDR != null)
            {
                // get PartnerClass of current DataRow
                PartnerClass = Convert.ToString(CurrentDR[PPartnerTable.GetPartnerClassDBName()]);
            }
            else
            {
                PartnerClass = "";
            }

            // MessageBox.Show(PartnerClass);
            return(PartnerClass);
        }
        /// <summary>
        /// The screen has been shown
        /// </summary>
        private void RunOnceOnActivationManual()
        {
            // enable autofind in list for first character (so the user can press character to find list entry)
            this.clbAddress.AutoFindColumn = ((Int16)(1));
            this.clbAddress.AutoFindMode   = Ict.Common.Controls.TAutoFindModeEnum.FirstCharacter;

            clbAddress.SpecialKeys =
                ((SourceGrid.GridSpecialKeys)((((((SourceGrid.GridSpecialKeys.Arrows |
                                                   SourceGrid.GridSpecialKeys.PageDownUp) |
                                                  SourceGrid.GridSpecialKeys.Enter) |
                                                 SourceGrid.GridSpecialKeys.Escape) |
                                                SourceGrid.GridSpecialKeys.Control) | SourceGrid.GridSpecialKeys.Shift)));


            string CheckedColumn      = "CHECKED";
            string ShortNameColumn    = PPartnerTable.GetPartnerShortNameDBName();
            string PartnerKeyColumn   = PPartnerLocationTable.GetPartnerKeyDBName();
            string PartnerClassColumn = PPartnerTable.GetPartnerClassDBName();
            string TelephoneColumn    = PPartnerLocationTable.GetTelephoneNumberDBName();
            string LocationTypeColumn = PPartnerLocationTable.GetLocationTypeDBName();

            DataTable NewTable = FPartnerSharingLocationDV.ToTable(true,
                                                                   new string[] { ShortNameColumn, PartnerKeyColumn, PartnerClassColumn, TelephoneColumn, LocationTypeColumn });

            NewTable.Columns.Add(new DataColumn(CheckedColumn, typeof(bool)));

            clbAddress.Columns.Clear();
            clbAddress.AddCheckBoxColumn("", NewTable.Columns[CheckedColumn], 17, false);
            clbAddress.AddTextColumn(Catalog.GetString("Name"), NewTable.Columns[ShortNameColumn], 240);
            clbAddress.AddPartnerKeyColumn(Catalog.GetString("Partner Key"), NewTable.Columns[PartnerKeyColumn], 90);
            clbAddress.AddTextColumn(Catalog.GetString("Partner Class"), NewTable.Columns[PartnerClassColumn], 90);
            clbAddress.AddTextColumn(Catalog.GetString("Telephone"), NewTable.Columns[TelephoneColumn], 130);
            clbAddress.AddTextColumn(Catalog.GetString("Location Type"), NewTable.Columns[LocationTypeColumn], 90);

            clbAddress.DataBindGrid(NewTable, ShortNameColumn, CheckedColumn, PartnerKeyColumn, false, true, false);

            // initialize list of checked items
            clbAddress.SetCheckedStringList("");

            // this is just to avoid compiler warning from FMainDS being initialized but not used
            if (FMainDS.IsInitialized)
            {
            }
        }
Ejemplo n.º 5
0
 /// <summary>
 /// Loads all costcentres that are linked to a partner, with the partner key and partner class
 /// </summary>
 public static DataTable GetData(String ATableName, Int32 ALedgerNumber, TDBTransaction AReadTransaction)
 {
     return(DBAccess.GDBAccessObj.SelectDT(
                String.Format(
                    "SELECT {0}, {3}.{1}, {3}.{5}, {2}, {8} FROM pub_{3} " +
                    "LEFT OUTER JOIN PUB_{7} ON PUB_{7}.{1} = PUB_{3}.{1}, pub_{4} " +
                    "WHERE pub_{3}.{1} = pub_{4}.{1} AND pub_{3}.{5} = {6}",
                    AValidLedgerNumberTable.GetCostCentreCodeDBName(),
                    AValidLedgerNumberTable.GetPartnerKeyDBName(),
                    PPartnerTable.GetPartnerClassDBName(),
                    AValidLedgerNumberTable.GetTableDBName(),
                    PPartnerTable.GetTableDBName(),
                    AValidLedgerNumberTable.GetLedgerNumberDBName(),
                    ALedgerNumber.ToString(),
                    PUnitTable.GetTableDBName(),
                    PUnitTable.GetUnitTypeCodeDBName()),
                ATableName,
                AReadTransaction));
 }
        public static DataTable GetEventUnits()
        {
            List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();

            DataColumn[] Key    = new DataColumn[3];
            DataTable    Events = new DataTable();

            if (TLogging.DL >= 9)
            {
                Console.WriteLine("GetEventUnits called!");
            }

            TDBTransaction Transaction = null;

            DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted,
                                                           ref Transaction,
                                                           delegate
            {
                string SqlStmt =
                    "SELECT DISTINCT " +
                    PPartnerTable.GetPartnerShortNameDBName() +
                    ", " + PPartnerTable.GetPartnerClassDBName() +
                    ", " + PUnitTable.GetOutreachCodeDBName() +
                    ", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() +
                    ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() +
                    ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() +
                    ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() +
                    ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName() +

                    ", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
                    ", " + PUnitTable.GetUnitTypeCodeDBName() +
                    ", " + PUnitTable.GetUnitNameDBName() +

                    " FROM pub_" + PPartnerTable.GetTableDBName() +
                    ", pub_" + PUnitTable.GetTableDBName() +
                    ", pub_" + PLocationTable.GetTableDBName() +
                    ", pub_" + PPartnerLocationTable.GetTableDBName() +
                    ", pub_" + PCountryTable.GetTableDBName() +

                    " WHERE " +
                    PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
                    PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND " +

                    PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
                    PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetPartnerKeyDBName() + " AND " +
                    PLocationTable.GetTableDBName() + "." + PLocationTable.GetSiteKeyDBName() + " = " +
                    PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + " AND " +
                    PLocationTable.GetTableDBName() + "." + PLocationTable.GetLocationKeyDBName() + " = " +
                    PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + " AND " +
                    PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryCodeDBName() + " = " +
                    PLocationTable.GetTableDBName() + "." + PLocationTable.GetCountryCodeDBName() + " AND " +

                    PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' " + " AND " +
                    PPartnerTable.GetPartnerClassDBName() + " = 'UNIT' ";

                // sort rows according to name
                SqlStmt = SqlStmt + " ORDER BY " + PPartnerTable.GetPartnerShortNameDBName();

                Events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "events",
                                                        Transaction, SqlParameterList.ToArray());

                Key[0]            = Events.Columns[PPartnerTable.GetPartnerKeyDBName()];
                Key[1]            = Events.Columns[PPartnerLocationTable.GetSiteKeyDBName()];
                Key[2]            = Events.Columns[PPartnerLocationTable.GetLocationKeyDBName()];
                Events.PrimaryKey = Key;
            });

            return(Events);
        }
Ejemplo n.º 7
0
        /// <summary>
        /// This report considers gifts given between the two specified dates, and can include all gifts or, if
        /// selected, those to a particular motivation, motivation detail or recipient. For the defined set of gifts
        /// and its total value, the donors are sorted into a list, starting with those who gave most, and showing
        /// the percentage that their gifts contributed to the total received (for this motivation or recipient, if
        /// specified) and the cumulative percentage, moving down the list starting with the top donor.
        /// </summary>
        /// <param name="ATotalAmount">Pre calculated value of the total gifts given with these parameters</param>
        /// <param name="ATopXPercent">Upper limit of the percentage to show in the report</param>
        /// <param name="ABottomXPercent">Lower limit of the percentage to show in the report</param>
        /// <param name="AExtract">true to use only partners from an extract</param>
        /// <param name="AExtractName">extract name</param>
        /// <param name="AStartDate">Start date of the gifts given</param>
        /// <param name="AEndDate">End date of the gifts given</param>
        /// <param name="ARecipientKey">Partner key of a specific recipient. If 0 then use all recipients</param>
        /// <param name="AMotivationGroup">Limit gifts to this motivation group. If % use all motivation groups</param>
        /// <param name="AMotivationDetail">Limit gifts to this motivation detail. If % use all motivation details</param>
        /// <returns></returns>
        private bool MakeTopDonor(decimal ATotalAmount, decimal ATopXPercent, decimal ABottomXPercent,
                                  bool AExtract, String AExtractName, DateTime AStartDate, DateTime AEndDate,
                                  Int64 ARecipientKey, String AMotivationGroup, String AMotivationDetail)
        {
            Int64         LedgerNumber = situation.GetParameters().Get("param_ledger_number_i").ToInt64();
            String        CurrencyType = situation.GetParameters().Get("param_currency").ToString();
            StringBuilder SqlString    = new StringBuilder();

            SqlString.Append("SELECT DISTINCT ");
            SqlString.Append("gift.p_donor_key_n AS DonorKey, ");
            SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerShortNameDBName() + " AS ShortName, ");
            SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerClassDBName() + " AS PartnerClass, ");

            if (CurrencyType == "Base")
            {
                SqlString.Append("SUM(detail." + AGiftDetailTable.GetGiftAmountDBName() + ") AS Amount ");
            }
            else
            {
                SqlString.Append("SUM(detail." + AGiftDetailTable.GetGiftAmountIntlDBName() + ") AS Amount ");
            }

            SqlString.Append(
                " FROM " + AGiftTable.GetTableDBName() + " as gift, " + AGiftDetailTable.GetTableDBName() + " as detail, " +
                PPartnerTable.GetTableDBName() + ", " + AGiftBatchTable.GetTableDBName() + " ");

            if (AExtract)
            {
                SqlString.Append(", " + MExtractTable.GetTableDBName() + ", " + MExtractMasterTable.GetTableDBName());
                SqlString.Append(
                    " WHERE gift." + AGiftTable.GetDonorKeyDBName() + " = " + MExtractTable.GetTableDBName() + "." +
                    MExtractTable.GetPartnerKeyDBName());
                SqlString.Append(
                    " AND " + MExtractTable.GetTableDBName() + "." + MExtractTable.GetExtractIdDBName() + " = " +
                    MExtractMasterTable.GetTableDBName() +
                    "." + MExtractMasterTable.GetExtractIdDBName());
                SqlString.Append(" AND " + MExtractMasterTable.GetTableDBName() + "." + MExtractMasterTable.GetExtractNameDBName() + " = '");
                SqlString.Append(AExtractName);
                SqlString.Append("' AND ");
            }
            else
            {
                SqlString.Append(" WHERE ");
            }

            SqlString.Append(" detail." + AGiftDetailTable.GetLedgerNumberDBName() + " = gift." + AGiftTable.GetLedgerNumberDBName());
            SqlString.Append(" AND detail." + AGiftDetailTable.GetBatchNumberDBName() + " = gift." + AGiftTable.GetBatchNumberDBName());
            SqlString.Append(
                " AND detail." + AGiftDetailTable.GetGiftTransactionNumberDBName() + " = gift." + AGiftTable.GetGiftTransactionNumberDBName());
            SqlString.Append(" AND gift." + AGiftTable.GetDateEnteredDBName() + " BETWEEN '");
            SqlString.Append(AStartDate.ToString("yyyy-MM-dd"));
            SqlString.Append("' AND '");
            SqlString.Append(AEndDate.ToString("yyyy-MM-dd"));
            SqlString.Append("' AND gift." + AGiftTable.GetLedgerNumberDBName() + " = ");
            SqlString.Append(LedgerNumber.ToString());
            SqlString.Append(" AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetLedgerNumberDBName() + " = ");
            SqlString.Append(LedgerNumber.ToString());
            SqlString.Append(
                " AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchNumberDBName() + " = gift." +
                AGiftTable.GetBatchNumberDBName());
            SqlString.Append(" AND ( " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchStatusDBName() + " = 'Posted' OR ");
            SqlString.Append(AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchStatusDBName() + " = 'posted' ) ");
            SqlString.Append(
                " AND " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = gift." + AGiftTable.GetDonorKeyDBName());

            if (ARecipientKey != 0)
            {
                SqlString.Append(" AND detail." + AGiftDetailTable.GetRecipientKeyDBName() + " = ");
                SqlString.Append(ARecipientKey.ToString());
            }

            if (AMotivationGroup != "%")
            {
                SqlString.Append(" AND  detail." + AGiftDetailTable.GetMotivationGroupCodeDBName() + " LIKE '");
                SqlString.Append(AMotivationGroup);
                SqlString.Append("' ");
            }

            if (AMotivationDetail != "%")
            {
                SqlString.Append(" AND  detail." + AGiftDetailTable.GetMotivationDetailCodeDBName() + " LIKE '");
                SqlString.Append(AMotivationDetail);
                SqlString.Append("' ");
            }

            SqlString.Append(" GROUP BY gift." + AGiftTable.GetDonorKeyDBName() + ", ");
            SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerShortNameDBName() + ", ");
            SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerClassDBName());
            SqlString.Append(" ORDER BY Amount DESC");

            DataTable Table = situation.GetDatabaseConnection().SelectDT(SqlString.ToString(), "table",
                                                                         situation.GetDatabaseConnection().Transaction, new OdbcParameter[] { });

            decimal CummulativeAmount = 0;
            decimal TopAmount         = ATotalAmount * ATopXPercent / 100;
            decimal BottomAmount      = ATotalAmount * ABottomXPercent / 100;

            int NumColumns = 7;
            int ChildRow   = 1;

            situation.GetResults().Clear();

            for (int Counter = 0; Counter < Table.Rows.Count; ++Counter)
            {
                decimal CurrentAmount = Convert.ToDecimal(Table.Rows[Counter]["Amount"]);

                if (CurrentAmount < 0)
                {
                    continue;
                }

                if ((CummulativeAmount <= TopAmount) &&
                    (CummulativeAmount >= BottomAmount))
                {
                    Int64  DonorKey     = Convert.ToInt64(Table.Rows[Counter]["DonorKey"]);
                    String ShortName    = (String)Table.Rows[Counter]["ShortName"];
                    String PartnerClass = (String)Table.Rows[Counter]["PartnerClass"];

                    CummulativeAmount += CurrentAmount;

                    // Transfer to results
                    TVariant[] Header      = new TVariant[NumColumns];
                    TVariant[] Description =
                    {
                        new TVariant(), new TVariant()
                    };
                    TVariant[] Columns = new TVariant[NumColumns];

                    for (int Counter2 = 0; Counter2 < NumColumns; ++Counter2)
                    {
                        Header[Counter2]  = new TVariant();
                        Columns[Counter2] = new TVariant();
                    }

                    StringBuilder       PartnerAddress = new StringBuilder();
                    PPartnerLocationRow AddressRow;

                    if (Ict.Petra.Server.MReporting.MPartner.TRptUserFunctionsPartner.GetPartnerBestAddressRow(DonorKey, situation, out AddressRow))
                    {
                        PLocationTable LocationTable = PLocationAccess.LoadByPrimaryKey(AddressRow.SiteKey,
                                                                                        AddressRow.LocationKey, situation.GetDatabaseConnection().Transaction);

                        if (LocationTable.Rows.Count > 0)
                        {
                            PLocationRow LocationRow = (PLocationRow)LocationTable.Rows[0];

                            PartnerAddress.Append(LocationRow.Locality);

                            if (LocationRow.Locality.Length > 0)
                            {
                                PartnerAddress.Append(", ");
                            }

                            PartnerAddress.Append(LocationRow.StreetName);

                            if (PartnerAddress.Length > 0)
                            {
                                PartnerAddress.Append(", ");
                            }

                            PartnerAddress.Append(LocationRow.Address3);

                            if (PartnerAddress.Length > 0)
                            {
                                PartnerAddress.Append(", ");
                            }

                            PartnerAddress.Append(LocationRow.PostalCode);
                            PartnerAddress.Append(" ");
                            PartnerAddress.Append(LocationRow.City);

                            if (LocationRow.County.Length > 0)
                            {
                                PartnerAddress.Append(", ");
                                PartnerAddress.Append(LocationRow.County);
                            }

                            PartnerAddress.Append(", ");
                            PartnerAddress.Append(LocationRow.CountryCode);
                        }
                    }

                    Columns[0] = new TVariant(DonorKey.ToString("0000000000"));
                    Columns[1] = new TVariant(PartnerClass);
                    Columns[2] = new TVariant(ShortName);
                    Columns[3] = new TVariant(CurrentAmount, "-#,##0.00;#,##0.00");
                    Columns[4] = new TVariant((CurrentAmount * 100 / ATotalAmount), "-#,##0.00;#,##0.00");
                    Columns[5] = new TVariant((CummulativeAmount * 100 / ATotalAmount), "-#,##0.00;#,##0.00");
                    Columns[6] = new TVariant(PartnerAddress.ToString());

                    situation.GetResults().AddRow(0, ChildRow++, true, 2, "", "", false,
                                                  Header, Description, Columns);
                }
                else
                {
                    CummulativeAmount += CurrentAmount;
                }
            }

            return(true);
        }
Ejemplo n.º 8
0
        public static Boolean MergedPartnerDetails(Int64 AMergedPartnerPartnerKey,
                                                   out String AMergedPartnerPartnerShortName,
                                                   out TPartnerClass AMergedPartnerPartnerClass,
                                                   out Int64 AMergedIntoPartnerKey,
                                                   out String AMergedIntoPartnerShortName,
                                                   out TPartnerClass AMergedIntoPartnerClass,
                                                   out String AMergedBy,
                                                   out DateTime AMergeDate)
        {
            TDBTransaction     ReadTransaction;
            Boolean            NewTransaction;
            StringCollection   RequiredColumns;
            PPartnerTable      MergedPartnerTable;
            PPartnerTable      PartnerMergedIntoTable;
            PPartnerMergeTable PartnerMergeTable;
            Boolean            ReturnValue = false;

            // Initialise out Arguments
            AMergedPartnerPartnerShortName = "";
            AMergedPartnerPartnerClass     = TPartnerClass.FAMILY; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
            AMergedIntoPartnerKey          = -1;
            AMergedIntoPartnerShortName    = "";
            AMergedIntoPartnerClass        = TPartnerClass.FAMILY; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
            AMergedBy  = "";
            AMergeDate = DateTime.MinValue;

            if (AMergedPartnerPartnerKey != 0)
            {
                /*
                 * First we look up the Partner that was Merged to get some details of it.
                 */

                // only some fields are needed
                RequiredColumns = new StringCollection();
                RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName());
                RequiredColumns.Add(PPartnerTable.GetStatusCodeDBName());
                RequiredColumns.Add(PPartnerTable.GetPartnerClassDBName());

                ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
                                                                                    TEnforceIsolationLevel.eilMinimum,
                                                                                    out NewTransaction);

                try
                {
                    MergedPartnerTable = PPartnerAccess.LoadByPrimaryKey(
                        AMergedPartnerPartnerKey, RequiredColumns, ReadTransaction, null, 0, 0);

                    if (MergedPartnerTable.Rows.Count == 0)
                    {
                        ReturnValue = false;
                    }
                    else
                    {
                        if (SharedTypes.StdPartnerStatusCodeStringToEnum(
                                MergedPartnerTable[0].StatusCode) != TStdPartnerStatusCode.spscMERGED)
                        {
                            ReturnValue = false;
                        }
                        else
                        {
                            AMergedPartnerPartnerShortName = MergedPartnerTable[0].PartnerShortName;
                            AMergedPartnerPartnerClass     = SharedTypes.PartnerClassStringToEnum(MergedPartnerTable[0].PartnerClass);

                            /*
                             * Now we look up the Partner that was Merged in the PartnerMerge DB Table
                             * to get the information about the Merged-Into Partner.
                             */
                            PartnerMergeTable = PPartnerMergeAccess.LoadByPrimaryKey(AMergedPartnerPartnerKey, ReadTransaction);

                            if (PartnerMergeTable.Rows.Count == 0)
                            {
                                /*
                                 * Although we didn't find the Merged Partner in the PartnerMerge
                                 * DB Table it is still a valid, Merged Partner, so we return true
                                 * in this case.
                                 */
                                ReturnValue = true;
                            }
                            else
                            {
                                /*
                                 * Now we look up the Merged-Into Partner to get some details of it.
                                 */
                                PartnerMergedIntoTable = PPartnerAccess.LoadByPrimaryKey(
                                    PartnerMergeTable[0].MergeTo, RequiredColumns,
                                    ReadTransaction, null, 0, 0);

                                if (PartnerMergedIntoTable.Rows.Count == 0)
                                {
                                    ReturnValue = false;
                                }
                                else
                                {
                                    AMergedIntoPartnerKey       = PartnerMergeTable[0].MergeTo;
                                    AMergedBy                   = PartnerMergeTable[0].MergedBy;
                                    AMergeDate                  = PartnerMergeTable[0].MergeDate.Value;
                                    AMergedIntoPartnerShortName = PartnerMergedIntoTable[0].PartnerShortName;
                                    AMergedIntoPartnerClass     = SharedTypes.PartnerClassStringToEnum(PartnerMergedIntoTable[0].PartnerClass);

                                    ReturnValue = true;
                                }
                            }
                        }
                    }
                }
                finally
                {
                    if (NewTransaction)
                    {
                        DBAccess.GDBAccessObj.CommitTransaction();
                        TLogging.LogAtLevel(7, "TPartnerServerLookups.MergedPartnerDetails: committed own transaction.");
                    }
                }
            }

            return(ReturnValue);
        }
Ejemplo n.º 9
0
        public static Boolean VerifyPartnerAndGetDetails(Int64 APartnerKey,
                                                         out String APartnerShortName,
                                                         out TPartnerClass APartnerClass,
                                                         out Boolean AIsMergedPartner,
                                                         out Boolean AUserCanAccessPartner)
        {
            APartnerShortName     = "";
            APartnerClass         = TPartnerClass.FAMILY; // Default. This is not really correct but the best compromise if PartnerKey is 0 or Partner isn't found since we have an enum here.
            AIsMergedPartner      = false;
            AUserCanAccessPartner = false;

            TDBTransaction        ReadTransaction;
            Boolean               NewTransaction;
            StringCollection      RequiredColumns;
            PPartnerTable         PartnerTable;
            Boolean               ReturnValue   = true;
            TStdPartnerStatusCode PartnerStatus = TStdPartnerStatusCode.spscACTIVE;

            // initialise outout Arguments
            if (APartnerKey != 0)
            {
                // only some fields are needed
                RequiredColumns = new StringCollection();
                RequiredColumns.Add(PPartnerTable.GetPartnerShortNameDBName());
                RequiredColumns.Add(PPartnerTable.GetPartnerClassDBName());
                RequiredColumns.Add(PPartnerTable.GetStatusCodeDBName());
                RequiredColumns.Add(PPartnerTable.GetRestrictedDBName());
                RequiredColumns.Add(PPartnerTable.GetUserIdDBName());
                RequiredColumns.Add(PPartnerTable.GetGroupIdDBName());

                ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
                                                                                    TEnforceIsolationLevel.eilMinimum,
                                                                                    out NewTransaction);
                try
                {
                    PartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, RequiredColumns, ReadTransaction, null, 0, 0);
                }
                finally
                {
                    if (NewTransaction)
                    {
                        DBAccess.GDBAccessObj.CommitTransaction();
                        TLogging.LogAtLevel(7, "TPartnerServerLookups.VerifyPartner: committed own transaction.");
                    }
                }

                if (PartnerTable.Rows.Count == 0)
                {
                    ReturnValue = false;
                }
                else
                {
                    // since we loaded by primary key there must just be one partner row
                    APartnerShortName = PartnerTable[0].PartnerShortName;
                    APartnerClass     = SharedTypes.PartnerClassStringToEnum(PartnerTable[0].PartnerClass);
                    PartnerStatus     = SharedTypes.StdPartnerStatusCodeStringToEnum(PartnerTable[0].StatusCode);

                    // check if user can access partner
                    if (Ict.Petra.Server.MPartner.Common.TSecurity.CanAccessPartner(PartnerTable[0]) == TPartnerAccessLevelEnum.palGranted)
                    {
                        AUserCanAccessPartner = true;
                    }

                    // check if partner is merged
                    if (PartnerStatus == TStdPartnerStatusCode.spscMERGED)
                    {
                        AIsMergedPartner = true;
                    }
                    else
                    {
                        AIsMergedPartner = false;
                    }

                    ReturnValue = true;
                }
            }
            else
            {
                // Return result as valid if Partner Key is 0.
                ReturnValue = true;
            }

            return(ReturnValue);
        }
Ejemplo n.º 10
0
        /// <summary>
        /// generate the partners from a text file that was generated with Benerator
        /// </summary>
        /// <param name="AInputBeneratorFile"></param>
        public static void GenerateFamilyPartners(string AInputBeneratorFile)
        {
            TDataBase      db          = DBAccess.Connect("GenerateFamilyPartners");
            TDBTransaction Transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);

            // get a list of banks (all class BANK)
            string    sqlGetBankPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_bank";
            DataTable BankKeys = db.SelectDT(sqlGetBankPartnerKeys, "keys", Transaction);

            Transaction.Rollback();

            PartnerEditTDS MainDS = new PartnerEditTDS();

            // AlanP: May 2016 - We may no longer need the UTF8 because the method now automatically discovers the encoding even with no BOM
            XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AInputBeneratorFile, ",", Encoding.UTF8);

            XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild;

            while (RecordNode != null)
            {
                string familySituation = TXMLParser.GetAttribute(RecordNode, "familySituation");

                PFamilyRow  familyRecord     = null;
                PPartnerRow FamilyPartnerRow = null;

                if (familySituation == "singleMan")
                {
                    familyRecord = SampleDataWorkers.GenerateFamilyRecord(RecordNode, "Male", MainDS);
                    SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS);
                }
                else if (familySituation == "singleWoman")
                {
                    familyRecord = SampleDataWorkers.GenerateFamilyRecord(RecordNode, "Female", MainDS);
                    SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS);
                }
                else if (familySituation == "family")
                {
                    familyRecord = SampleDataWorkers.GenerateFamilyRecord(RecordNode, "Male", MainDS);
                    SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Male", MainDS);
                    SampleDataWorkers.GeneratePersonRecord(RecordNode, familyRecord, "Female", MainDS);

                    int NumberOfChildren = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "numberOfChildren"));

                    if (NumberOfChildren > 0)
                    {
                        FamilyPartnerRow = (PPartnerRow)MainDS.PPartner.Rows.Find(familyRecord.PartnerKey);
                        FamilyPartnerRow.AddresseeTypeCode = MPartnerConstants.ADDRESSEETYPE_FAMILY;
                    }
                }

                FamilyPartnerRow = (PPartnerRow)MainDS.PPartner.Rows.Find(familyRecord.PartnerKey);
                FamilyPartnerRow.ReceiptEachGift        = false;
                FamilyPartnerRow.ReceiptLetterFrequency = "Annual";

                SampleDataWorkers.GenerateAddressForFamily(RecordNode, familyRecord, MainDS);

                SampleDataWorkers.GenerateBankDetails(RecordNode, familyRecord, MainDS, BankKeys);

                if (MainDS.PFamily.Rows.Count % 100 == 0)
                {
                    TLogging.Log("created donor " + MainDS.PFamily.Rows.Count.ToString() + " " + familyRecord.FamilyName);
                }

                RecordNode = RecordNode.NextSibling;
            }

            // we do not save person records for normal family partners
            MainDS.PPerson.Clear();

            // need to clear all partner records of the PERSON partners as well
            DataView PersonPartners = new DataView(MainDS.PPartner);

            PersonPartners.RowFilter =
                string.Format("{0} = '{1}'",
                              PPartnerTable.GetPartnerClassDBName(),
                              MPartnerConstants.PARTNERCLASS_PERSON);

            DataView PartnerLocations = new DataView(MainDS.PPartnerLocation);

            foreach (DataRowView rv in PersonPartners)
            {
                PPartnerRow partnerRow = (PPartnerRow)rv.Row;

                PartnerLocations.RowFilter =
                    string.Format("{0} = {1}",
                                  PPartnerLocationTable.GetPartnerKeyDBName(),
                                  partnerRow.PartnerKey);

                PartnerLocations[0].Row.Delete();

                partnerRow.Delete();
            }

            MainDS.ThrowAwayAfterSubmitChanges = true;

            PartnerEditTDSAccess.SubmitChanges(MainDS);

            TLogging.Log("after saving donors");
        }
        public static DataTable GetEventUnits(bool AIncludeConferenceUnits, bool AIncludeOutreachUnits,
                                              string AEventName, bool AIncludeLocationData, bool ACurrentAndFutureEventsOnly)
        {
            TDBTransaction ReadTransaction;
            Boolean        NewTransaction = false;

            List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();

            DataColumn[] Key    = new DataColumn[1];
            DataTable    Events = new DataTable();

            if (AEventName == "*")
            {
                AEventName = "";
            }
            else if (AEventName.EndsWith("*"))
            {
                AEventName = AEventName.Substring(0, AEventName.Length - 1);
            }

            if (TLogging.DL >= 9)
            {
                Console.WriteLine("GetEventUnits called!");
            }

            ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
                                                                                TEnforceIsolationLevel.eilMinimum,
                                                                                out NewTransaction);

            try
            {
                string SqlStmt =
                    "SELECT DISTINCT " +
                    PPartnerTable.GetPartnerShortNameDBName() +
                    ", " + PPartnerTable.GetPartnerClassDBName() +
                    ", " + PUnitTable.GetOutreachCodeDBName();

                if (AIncludeLocationData || ACurrentAndFutureEventsOnly)
                {
                    SqlStmt = SqlStmt +
                              ", " + PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryNameDBName() +
                              ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateEffectiveDBName() +
                              ", " + PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetDateGoodUntilDBName();
                }

                SqlStmt = SqlStmt +
                          ", " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
                          ", " + PUnitTable.GetUnitTypeCodeDBName() +

                          " FROM pub_" + PPartnerTable.GetTableDBName() +
                          ", pub_" + PUnitTable.GetTableDBName();

                if (AIncludeLocationData || ACurrentAndFutureEventsOnly)
                {
                    SqlStmt = SqlStmt +
                              ", pub_" + PLocationTable.GetTableDBName() +
                              ", pub_" + PPartnerLocationTable.GetTableDBName() +
                              ", pub_" + PCountryTable.GetTableDBName();
                }

                SqlStmt = SqlStmt +
                          " WHERE " +
                          PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
                          PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() + " AND ";

                if (AIncludeLocationData || ACurrentAndFutureEventsOnly)
                {
                    SqlStmt = SqlStmt +
                              PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = " +
                              PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetPartnerKeyDBName() + " AND " +
                              PLocationTable.GetTableDBName() + "." + PLocationTable.GetSiteKeyDBName() + " = " +
                              PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetSiteKeyDBName() + " AND " +
                              PLocationTable.GetTableDBName() + "." + PLocationTable.GetLocationKeyDBName() + " = " +
                              PPartnerLocationTable.GetTableDBName() + "." + PPartnerLocationTable.GetLocationKeyDBName() + " AND " +
                              PCountryTable.GetTableDBName() + "." + PCountryTable.GetCountryCodeDBName() + " = " +
                              PLocationTable.GetTableDBName() + "." + PLocationTable.GetCountryCodeDBName() + " AND ";
                }

                SqlStmt = SqlStmt +
                          PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE' " + " AND " +
                          PPartnerTable.GetPartnerClassDBName() + " = 'UNIT' ";

                // add criteria for conference and/or outreach
                String ConferenceWhereClause = "(" +
                                               PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONF%' OR " +
                                               PUnitTable.GetUnitTypeCodeDBName() + " LIKE '%CONG%')";

                String OutreachWhereClause = PUnitTable.GetOutreachCodeDBName() + " IS NOT NULL AND " +
                                             PUnitTable.GetOutreachCodeDBName() + " <> '' AND (" +
                                             PUnitTable.GetUnitTypeCodeDBName() + " NOT LIKE '%CONF%' AND " +
                                             PUnitTable.GetUnitTypeCodeDBName() + " NOT LIKE '%CONG%')";

                if (AIncludeConferenceUnits &&
                    AIncludeOutreachUnits)
                {
                    SqlStmt = SqlStmt + " AND ((" + ConferenceWhereClause + ") OR (" + OutreachWhereClause + "))";
                }
                else if (AIncludeConferenceUnits)
                {
                    SqlStmt = SqlStmt + " AND (" + ConferenceWhereClause + ")";
                }
                else if (AIncludeOutreachUnits)
                {
                    SqlStmt = SqlStmt + " AND (" + OutreachWhereClause + ")";
                }

                // add criteria for event name filter
                if (AEventName.Length > 0)
                {
                    // in case there is a filter set for the event name
                    AEventName = AEventName.Replace('*', '%') + "%";
                    SqlStmt    = SqlStmt + " AND " + PUnitTable.GetUnitNameDBName() +
                                 " LIKE '" + AEventName + "'";
                }

                if (ACurrentAndFutureEventsOnly)
                {
                    SqlStmt = SqlStmt + " AND " + PPartnerLocationTable.GetDateGoodUntilDBName() + " >= ?";

                    SqlParameterList.Add(new OdbcParameter("param_date", OdbcType.Date)
                    {
                        Value = DateTime.Today.Date
                    });
                }

                // sort rows according to name
                SqlStmt = SqlStmt + " ORDER BY " + PPartnerTable.GetPartnerShortNameDBName();

                Events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "events",
                                                        ReadTransaction, SqlParameterList.ToArray());

                Key[0]            = Events.Columns[PPartnerTable.GetPartnerKeyDBName()];
                Events.PrimaryKey = Key;
            }
            catch (Exception e)
            {
                TLogging.Log(e.ToString());
            }
            finally
            {
                if (NewTransaction)
                {
                    DBAccess.GDBAccessObj.CommitTransaction();

                    if (TLogging.DL >= 7)
                    {
                        Console.WriteLine("GetEventUnits: committed own transaction.");
                    }
                }
            }

            return(Events);
        }