Ejemplo n.º 1
0
        /// <summary>
        /// Returns the Family Members of a Family.
        /// </summary>
        /// <param name="AFamilyPartnerKey">PartnerKey of the FAMILY.</param>
        /// <param name="AReadTransaction">Open DB Transaction.</param>
        /// <returns>An Instance of <see cref="PartnerInfoTDSFamilyMembersTable" />.
        /// If there were Family Members, there will be one DataRow for each Family Member.</returns>
        private static PartnerInfoTDSFamilyMembersTable GetFamilyMembers(Int64 AFamilyPartnerKey,
                                                                         TDBTransaction AReadTransaction)
        {
            OdbcParameter[] ParametersArray;
            DataSet         TmpDS;
            PPersonTable    FamilyPersonsDT;
            PartnerInfoTDSFamilyMembersRow   NewRow;
            PartnerInfoTDSFamilyMembersTable FamilyMembersDT;

            FamilyMembersDT = new PartnerInfoTDSFamilyMembersTable();

            ParametersArray          = new OdbcParameter[1];
            ParametersArray[0]       = new OdbcParameter("", OdbcType.Decimal, 10);
            ParametersArray[0].Value = (System.Object)AFamilyPartnerKey;

            TmpDS = new DataSet();

            FamilyPersonsDT = new PPersonTable();
            TmpDS.Tables.Add(FamilyPersonsDT);

            AReadTransaction.DataBaseObj.Select(TmpDS,
                                                "SELECT " + "PUB_" + PPartnerTable.GetTableDBName() + '.' +
                                                PPartnerTable.GetPartnerKeyDBName() + ", " +
                                                PPersonTable.GetFamilyNameDBName() + ", " +
                                                PPersonTable.GetTitleDBName() + ", " +
                                                PPersonTable.GetFirstNameDBName() + ", " +
                                                PPersonTable.GetMiddleName1DBName() + ", " +
                                                PPersonTable.GetFamilyIdDBName() + ' ' +
                                                "FROM PUB_" + PPersonTable.GetTableDBName() +
                                                " INNER JOIN " + "PUB_" + PPartnerTable.GetTableDBName() + " ON " +
                                                "PUB_" + PPersonTable.GetTableDBName() + '.' +
                                                PPartnerTable.GetPartnerKeyDBName() + " = " +
                                                "PUB_" + PPartnerTable.GetTableDBName() + '.' +
                                                PPartnerTable.GetPartnerKeyDBName() + ' ' +
                                                "WHERE " + PPersonTable.GetFamilyKeyDBName() + " = ? " +
                                                "AND " + PPartnerTable.GetStatusCodeDBName() + " <> '" +
                                                SharedTypes.StdPartnerStatusCodeEnumToString(TStdPartnerStatusCode.spscMERGED) + "' " + // Make sure we don't load MERGED Partners (shouldn't have a p_family_key_n, but just in case.)
                                                "ORDER BY " + PPersonTable.GetFamilyIdDBName() + " ASC",
                                                PPersonTable.GetTableName(), AReadTransaction, ParametersArray, 0, 0);

            // Add Persons to Table
            for (Int32 Counter = 0; Counter <= FamilyPersonsDT.Rows.Count - 1; Counter += 1)
            {
                NewRow                  = FamilyMembersDT.NewRowTyped(false);
                NewRow.PartnerKey       = FamilyPersonsDT[Counter].PartnerKey;
                NewRow.PartnerShortName =
                    Calculations.DeterminePartnerShortName(TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnFamilyName,
                                                                                             FamilyPersonsDT[Counter]), TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnTitle,
                                                                                                                                                          FamilyPersonsDT[Counter]), TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnFirstName,
                                                                                                                                                                                                                       FamilyPersonsDT[Counter]),
                                                           TSaveConvert.StringColumnToString(FamilyPersonsDT.ColumnMiddleName1, FamilyPersonsDT[Counter]));
                NewRow.FamilyId = FamilyPersonsDT[Counter].FamilyId;

                FamilyMembersDT.Rows.Add(NewRow);
            }

            return(FamilyMembersDT);
        }
Ejemplo n.º 2
0
        private static bool GetAllReceivingFields(long AConferenceKey, ref DataTable AFieldsTable)
        {
            TDBTransaction ReadTransaction;
            Boolean        NewTransaction = false;

            TLogging.LogAtLevel(9, "TConferenceOptions.GetAllReceivingFields called!");

            TDataBase db = DBAccess.Connect("GetAllReceivingFields");

            ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
                                                             out NewTransaction);

            try
            {
                OdbcParameter[] ParametersArray;

                ParametersArray = new OdbcParameter[0];

                DataSet TmpDS = ReadTransaction.DataBaseObj.Select(
                    "SELECT PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetPartnerKeyDBName() +
                    ", PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetUnitNameDBName() +
                    " FROM PUB_" + PUnitTable.GetTableDBName() +
                    ", PUB_" + PPartnerTable.GetTableDBName() + ", " +
                    "PUB_" + PPartnerTypeTable.GetTableDBName() +
                    " WHERE PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetPartnerKeyDBName() +
                    " = PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetPartnerKeyDBName() +
                    " AND PUB_" + PPartnerTypeTable.GetTableDBName() + '.' + PPartnerTypeTable.GetPartnerKeyDBName() +
                    " = PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetPartnerKeyDBName() +
                    " AND PUB_" + PPartnerTable.GetTableDBName() + '.' + PPartnerTable.GetStatusCodeDBName() + " = \"ACTIVE\"" +
                    " AND PUB_" + PPartnerTypeTable.GetTableDBName() + '.' + PPartnerTypeTable.GetTypeCodeDBName() + " = \"LEDGER\"" +
                    " ORDER BY PUB_" + PUnitTable.GetTableDBName() + '.' + PUnitTable.GetUnitNameDBName() + " ASC",
                    "TempTable", ReadTransaction, ParametersArray);

                DataTable ResultTale = TmpDS.Tables[0];

                for (int Counter = 0; Counter < ResultTale.Rows.Count; ++Counter)
                {
                    DataRow NewRow = AFieldsTable.NewRow();

                    NewRow[SELECTION]          = false;
                    NewRow[UNIT_KEY]           = ResultTale.Rows[Counter][PUnitTable.GetPartnerKeyDBName()];
                    NewRow[UNIT_NAME]          = ResultTale.Rows[Counter][PUnitTable.GetUnitNameDBName()];
                    NewRow[USED_IN_CONFERENCE] = true;

                    AFieldsTable.Rows.Add(NewRow);
                }
            }
            finally
            {
                if (NewTransaction)
                {
                    ReadTransaction.Commit();
                    TLogging.LogAtLevel(7, "TConferenceOptions.GetReceivingFields: committed own transaction.");
                }
            }
            return(true);
        }
Ejemplo n.º 3
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);
        }
Ejemplo n.º 4
0
 /// <summary>
 /// Loads all available Ledgers and their names into a DataTable
 ///
 /// </summary>
 /// <returns>void</returns>
 public static DataTable GetData(String ATableName, TDBTransaction AReadTransaction)
 {
     return(DBAccess.GDBAccessObj.SelectDT(
                String.Format(
                    "SELECT {0} AS LedgerNumber, {1} AS LedgerName FROM pub_{2}, pub_{3} " +
                    "WHERE pub_{2}.{4} = pub_{3}.{4} " + "AND   pub_{2}.{5} = 1",
                    ALedgerTable.GetLedgerNumberDBName(),
                    PPartnerTable.GetPartnerShortNameDBName(),
                    ALedgerTable.GetTableDBName(),
                    PPartnerTable.GetTableDBName(),
                    PPartnerTable.GetPartnerKeyDBName(),
                    ALedgerTable.GetLedgerStatusDBName()),
                ATableName,
                AReadTransaction));
 }
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 PUnitTable GetActiveFieldUnits()
        {
            PUnitTable UnitTable = new PUnitTable();
            PUnitRow   UnitRow;

            TLogging.LogAtLevel(9, "TPartnerDataReaderWebConnector.GetActiveFieldUnits called!");

            TDBTransaction Transaction = null;

            DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted,
                                                           ref Transaction,
                                                           delegate
            {
                // Load data
                string SqlStmt = "SELECT pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
                                 ", pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetUnitNameDBName() +
                                 " FROM " + PUnitTable.GetTableDBName() + ", " + PPartnerTable.GetTableDBName() +
                                 " WHERE ((" + PUnitTable.GetOutreachCodeDBName() + " IS NULL)" +
                                 "        OR(" + PUnitTable.GetOutreachCodeDBName() + " = ''))" +
                                 " AND " + PUnitTable.GetUnitTypeCodeDBName() + " <> 'KEY-MIN'" +
                                 " AND pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
                                 " = pub_" + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
                                 " AND " + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE'";

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

                DataTable events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "fields", Transaction);

                foreach (DataRow eventRow in events.Rows)
                {
                    UnitRow            = (PUnitRow)UnitTable.NewRow();
                    UnitRow.PartnerKey = Convert.ToInt64(eventRow[0]);
                    UnitRow.UnitName   = Convert.ToString(eventRow[1]);
                    UnitTable.Rows.Add(UnitRow);
                }
            });

            return(UnitTable);
        }
        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.º 8
0
        private void ApplySecurity()
        {
            if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PPartnerTable.GetTableDBName()))
            {
                // need to disable all Fields that are DataBound to p_partner. This continues in the switch statments!
                // timop: I have disabled all controls. usually you have p_partner modify permissions, or none
                CustomEnablingDisabling.DisableControlGroup(pnlContent);
            }

            switch (SharedTypes.PartnerClassStringToEnum(FPartnerClass))
            {
            case TPartnerClass.PERSON:

                if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PPersonTable.GetTableDBName()))
                {
                    // need to disable all Fields that are DataBound to p_partner
                    CustomEnablingDisabling.DisableControl(pnlPerson, cmbPersonAddresseeTypeCode);
                    CustomEnablingDisabling.DisableControl(pnlPerson, chkPersonNoSolicitations);

                    // need to disable all Fields that are DataBound to p_person
                    CustomEnablingDisabling.DisableControlGroup(pnlPerson);

                    cmbPersonAddresseeTypeCode.Focus();
                }

                break;

            case TPartnerClass.FAMILY:

                if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PFamilyTable.GetTableDBName()))
                {
                    // need to disable all Fields that are DataBound to p_partner
                    CustomEnablingDisabling.DisableControl(pnlFamily, cmbFamilyAddresseeTypeCode);
                    CustomEnablingDisabling.DisableControl(pnlFamily, chkFamilyNoSolicitations);

                    // need to disable all Fields that are DataBound to p_family
                    CustomEnablingDisabling.DisableControlGroup(pnlFamily);

                    cmbFamilyAddresseeTypeCode.Focus();
                }

                break;

            case TPartnerClass.CHURCH:

                if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PChurchTable.GetTableDBName()))
                {
                    // need to disable all Fields that are DataBound to p_partner
                    CustomEnablingDisabling.DisableControl(pnlOther2ndLine, cmbOtherAddresseeTypeCode);
                    CustomEnablingDisabling.DisableControl(pnlOther2ndLine, chkOtherNoSolicitations);

                    // need to disable all Fields that are DataBound to p_church
                    CustomEnablingDisabling.DisableControlGroup(pnlOther2ndLine);

                    cmbOtherAddresseeTypeCode.Focus();
                }

                break;

            case TPartnerClass.ORGANISATION:

                if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, POrganisationTable.GetTableDBName()))
                {
                    // need to disable all Fields that are DataBound to p_partner
                    CustomEnablingDisabling.DisableControl(pnlOther2ndLine, cmbOtherAddresseeTypeCode);
                    CustomEnablingDisabling.DisableControl(pnlOther2ndLine, chkOtherNoSolicitations);

                    // need to disable all Fields that are DataBound to p_organisation
                    CustomEnablingDisabling.DisableControlGroup(pnlOther2ndLine);

                    cmbOtherAddresseeTypeCode.Focus();
                }

                break;

            case TPartnerClass.UNIT:

                if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PUnitTable.GetTableDBName()))
                {
                    // need to disable all Fields that are DataBound to p_partner
                    CustomEnablingDisabling.DisableControl(pnlOther2ndLine, cmbOtherAddresseeTypeCode);
                    CustomEnablingDisabling.DisableControl(pnlOther2ndLine, chkOtherNoSolicitations);

                    // need to disable all Fields that are DataBound to p_unit
                    CustomEnablingDisabling.DisableControlGroup(pnlOther2ndLine);

                    cmbOtherAddresseeTypeCode.Focus();
                }

                break;

            case TPartnerClass.BANK:

                if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PBankTable.GetTableDBName()))
                {
                    // need to disable all Fields that are DataBound to p_partner
                    CustomEnablingDisabling.DisableControl(pnlOther2ndLine, cmbOtherAddresseeTypeCode);
                    CustomEnablingDisabling.DisableControl(pnlOther2ndLine, chkOtherNoSolicitations);

                    // need to disable all Fields that are DataBound to p_bank
                    CustomEnablingDisabling.DisableControlGroup(pnlOther2ndLine);

                    cmbOtherAddresseeTypeCode.Focus();
                }

                break;

            case TPartnerClass.VENUE:

                if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PVenueTable.GetTableDBName()))
                {
                    // need to disable all Fields that are DataBound to p_partner
                    CustomEnablingDisabling.DisableControl(pnlOther2ndLine, cmbOtherAddresseeTypeCode);
                    CustomEnablingDisabling.DisableControl(pnlOther2ndLine, chkOtherNoSolicitations);

                    // need to disable all Fields that are DataBound to p_venue
                    CustomEnablingDisabling.DisableControlGroup(pnlOther2ndLine);

                    cmbOtherAddresseeTypeCode.Focus();
                }

                break;

            default:
                MessageBox.Show(String.Format(Catalog.GetString("Unrecognised Partner Class '{0}'!"), FPartnerClass));
                break;
            }
        }
Ejemplo n.º 9
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.º 10
0
        /// <summary>
        /// export all posted invoices of other suppliers in this year
        /// </summary>
        public static void Export(string AOutputPath,
                                  char ACSVSeparator,
                                  string ANewLine,
                                  Int32 ALedgerNumber,
                                  Int32 AFinancialYear,
                                  string ACostCentres)
        {
            string filename = Path.GetFullPath(Path.Combine(AOutputPath, "accountspayable.csv"));

            Console.WriteLine("Writing file: " + filename);

            StringBuilder sb = new StringBuilder();

            TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);

            // get all posted or paid ap_documents by their date issued
            string sql =
                String.Format(
                    "SELECT * FROM PUB_{0} " +
                    "WHERE {1} = {2} AND " +
                    "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
                    "{7} >= ? AND {7} <= ?",
                    AApDocumentTable.GetTableDBName(),
                    AApDocumentTable.GetLedgerNumberDBName(),
                    ALedgerNumber,
                    AApDocumentTable.GetDocumentStatusDBName(),
                    MFinanceConstants.AP_DOCUMENT_POSTED,
                    MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
                    MFinanceConstants.AP_DOCUMENT_PAID,
                    AApDocumentTable.GetDateIssuedDBName());

            List <OdbcParameter> Parameters = new List <OdbcParameter>();
            OdbcParameter        param      = new OdbcParameter("startdate", OdbcType.DateTime);

            param.Value = TAccountingPeriodsWebConnector.GetPeriodStartDate(ALedgerNumber, AFinancialYear, 0, 1);
            Parameters.Add(param);
            param       = new OdbcParameter("enddate", OdbcType.DateTime);
            param.Value = TAccountingPeriodsWebConnector.GetPeriodEndDate(ALedgerNumber, AFinancialYear, 0, 12);
            Parameters.Add(param);

            AApDocumentTable apDocuments = new AApDocumentTable();

            DBAccess.GDBAccessObj.SelectDT(apDocuments, sql, Transaction, Parameters.ToArray(), 0, 0);

            // get all ap details
            sql =
                String.Format(
                    "SELECT Detail.* FROM PUB_{0} AS Doc, PUB_{8} AS Detail " +
                    "WHERE Doc.{1} = {2} AND " +
                    "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
                    "{7} >= ? AND {7} <= ? AND " +
                    "Detail.{1} = Doc.{1} AND Detail.{9} = Doc.{9} AND " +
                    "Detail.{10} IN ({11})",
                    AApDocumentTable.GetTableDBName(),
                    AApDocumentTable.GetLedgerNumberDBName(),
                    ALedgerNumber,
                    AApDocumentTable.GetDocumentStatusDBName(),
                    MFinanceConstants.AP_DOCUMENT_POSTED,
                    MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
                    MFinanceConstants.AP_DOCUMENT_PAID,
                    AApDocumentTable.GetDateIssuedDBName(),
                    AApDocumentDetailTable.GetTableDBName(),
                    AApDocumentTable.GetApDocumentIdDBName(),
                    AApDocumentDetailTable.GetCostCentreCodeDBName(),
                    "'" + ACostCentres.Replace(",", "','") + "'");

            AApDocumentDetailTable apDetails = new AApDocumentDetailTable();

            DBAccess.GDBAccessObj.SelectDT(apDetails, sql, Transaction, Parameters.ToArray(), 0, 0);

            apDetails.DefaultView.Sort = AApDocumentDetailTable.GetApDocumentIdDBName();

            // get all ap payments
            sql =
                String.Format(
                    "SELECT DP.{0}, DP.{1}, P.{2} AS {3}, DP.{7}, DP.{15} FROM PUB_{4} AS Doc, PUB_{5} AS DP, PUB_{6} AS P " +
                    "WHERE Doc.{7} = {8} AND " +
                    "({9} = '{10}' OR {9} = '{11}' OR {9} = '{12}') AND " +
                    "{13} >= ? AND {13} <= ? AND " +
                    "DP.{7} = Doc.{7} AND DP.{14} = Doc.{14} AND " +
                    "P.{7} = Doc.{7} AND P.{15} = DP.{15}",

                    AApDocumentPaymentTable.GetApDocumentIdDBName(),
                    AApDocumentPaymentTable.GetAmountDBName(),
                    AApPaymentTable.GetPaymentDateDBName(),
                    AApDocumentPaymentTable.GetDateCreatedDBName(),
                    AApDocumentTable.GetTableDBName(),
                    AApDocumentPaymentTable.GetTableDBName(),
                    AApPaymentTable.GetTableDBName(),

                    AApDocumentTable.GetLedgerNumberDBName(),
                    ALedgerNumber,
                    AApDocumentTable.GetDocumentStatusDBName(),
                    MFinanceConstants.AP_DOCUMENT_POSTED,
                    MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
                    MFinanceConstants.AP_DOCUMENT_PAID,
                    AApDocumentTable.GetDateIssuedDBName(),

                    AApDocumentTable.GetApDocumentIdDBName(),
                    AApPaymentTable.GetPaymentNumberDBName());

            AApDocumentPaymentTable apPayments = new AApDocumentPaymentTable();

            DBAccess.GDBAccessObj.SelectDT(apPayments, sql, Transaction, Parameters.ToArray(), 0, 0);

            apPayments.DefaultView.Sort = AApDocumentPaymentTable.GetApDocumentIdDBName();

            // get the analysis attributes for the taxes
            sql =
                String.Format(
                    "SELECT Attrib.* FROM PUB_{0} AS Doc, PUB_{8} AS Attrib " +
                    "WHERE Doc.{1} = {2} AND " +
                    "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
                    "{7} >= ? AND {7} <= ? AND " +
                    "Attrib.{1} = Doc.{1} AND Attrib.{9} = Doc.{9}",
                    AApDocumentTable.GetTableDBName(),
                    AApDocumentTable.GetLedgerNumberDBName(),
                    ALedgerNumber,
                    AApDocumentTable.GetDocumentStatusDBName(),
                    MFinanceConstants.AP_DOCUMENT_POSTED,
                    MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
                    MFinanceConstants.AP_DOCUMENT_PAID,
                    AApDocumentTable.GetDateIssuedDBName(),
                    AApAnalAttribTable.GetTableDBName(),
                    AApDocumentTable.GetApDocumentIdDBName());

            AApAnalAttribTable apAttrib = new AApAnalAttribTable();

            DBAccess.GDBAccessObj.SelectDT(apAttrib, sql, Transaction, Parameters.ToArray(), 0, 0);

            apAttrib.DefaultView.Sort = AApAnalAttribTable.GetApDocumentIdDBName() + "," + AApAnalAttribTable.GetDetailNumberDBName();

            // get the supplier name
            sql =
                String.Format(
                    "SELECT DISTINCT p.* FROM PUB_{0} AS Doc, PUB_{8} AS p " +
                    "WHERE Doc.{1} = {2} AND " +
                    "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " +
                    "{7} >= ? AND {7} <= ? AND " +
                    "p.{9} = Doc.{10}",
                    AApDocumentTable.GetTableDBName(),
                    AApDocumentTable.GetLedgerNumberDBName(),
                    ALedgerNumber,
                    AApDocumentTable.GetDocumentStatusDBName(),
                    MFinanceConstants.AP_DOCUMENT_POSTED,
                    MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID,
                    MFinanceConstants.AP_DOCUMENT_PAID,
                    AApDocumentTable.GetDateIssuedDBName(),
                    PPartnerTable.GetTableDBName(),
                    PPartnerTable.GetPartnerKeyDBName(),
                    AApDocumentTable.GetPartnerKeyDBName());

            PPartnerTable suppliers = new PPartnerTable();

            DBAccess.GDBAccessObj.SelectDT(suppliers, sql, Transaction, Parameters.ToArray(), 0, 0);

            foreach (AApDocumentRow doc in apDocuments.Rows)
            {
                PPartnerRow supplier = (PPartnerRow)suppliers.Rows.Find(doc.PartnerKey);

                DataRowView[] detailsRV = apDetails.DefaultView.FindRows(doc.ApDocumentId);

                foreach (DataRowView rv in detailsRV)
                {
                    AApDocumentDetailRow detail = (AApDocumentDetailRow)rv.Row;

                    if (doc.CreditNoteFlag)
                    {
                        detail.Amount *= -1.0m;
                    }

                    DataRowView[] payments = apPayments.DefaultView.FindRows(doc.ApDocumentId);

                    string DatePaid = string.Empty;

                    if (payments.Length > 1)
                    {
                        DatePaid = "Several Payments: ";

                        foreach (DataRowView rvPayment in payments)
                        {
                            AApDocumentPaymentRow payment = ((AApDocumentPaymentRow)rvPayment.Row);
                            DatePaid += payment.DateCreated.Value.ToString("yyyyMMdd") + " ";
                            DatePaid += payment.PaymentNumber.ToString() + "; ";
                        }

                        // for gdpdu, only write dates to this column
                        DatePaid = string.Empty;
                    }
                    else if (payments.Length == 1)
                    {
                        AApDocumentPaymentRow payment = ((AApDocumentPaymentRow)payments[0].Row);
                        DatePaid = payment.DateCreated.Value.ToString("yyyyMMdd");
                    }

                    sb.Append(StringHelper.StrMerge(
                                  new string[] {
                        doc.ApNumber.ToString(),
                        detail.DetailNumber.ToString(),
                        doc.DateIssued.ToString("yyyyMMdd"),
                        DatePaid,
                        doc.PartnerKey.ToString(),
                        supplier.PartnerShortName,
                        detail.CostCentreCode,
                        detail.AccountCode,
                        String.Format("{0:N}", detail.Amount),
                        detail.Narrative
                    }, ACSVSeparator));
                    sb.Append(ANewLine);
                }
            }

            DBAccess.GDBAccessObj.RollbackTransaction();

            StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));

            sw.Write(sb.ToString());
            sw.Close();
        }
Ejemplo n.º 11
0
 private void ApplySecurity()
 {
     if (!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapMODIFY, PPartnerTable.GetTableDBName()))
     {
         // need to disable all Fields that are DataBound to p_partner
         // MessageBox.Show('Disabling p_partner fields...');
         CustomEnablingDisabling.DisableControl(pnlNotes, txtPartnerComment);
     }
 }
Ejemplo n.º 12
0
        public static DataTable GetAvailableSites()
        {
            TDBTransaction ReadTransaction = new TDBTransaction();

            DataTable SitesTable       = new DataTable();
            DataTable UnusedSitesTable = new DataTable();
            DataTable UsedSitesTable   = new DataTable();
            DataRow   SitesRow;
            string    IsPartnerLedger = SharedConstants.SYSMAN_AVAILABLE_SITES_COLUMN_IS_PARTNER_LEDGER;
            string    SiteKey         = PUnitTable.GetPartnerKeyDBName();
            string    SiteShortName   = PUnitTable.GetUnitNameDBName();
            Int64     PartnerKey;

            SitesTable.Columns.Add(new DataColumn(IsPartnerLedger, typeof(bool)));
            SitesTable.Columns.Add(new DataColumn(SiteKey, typeof(Int64)));
            SitesTable.Columns.Add(new DataColumn(SiteShortName, typeof(string)));

            UnusedSitesTable.Columns.Add(new DataColumn(IsPartnerLedger, typeof(bool)));
            UnusedSitesTable.Columns.Add(new DataColumn(SiteKey, typeof(Int64)));
            UnusedSitesTable.Columns.Add(new DataColumn(SiteShortName, typeof(string)));

            UsedSitesTable.Columns.Add(new DataColumn(IsPartnerLedger, typeof(bool)));
            UsedSitesTable.Columns.Add(new DataColumn(SiteKey, typeof(Int64)));
            UsedSitesTable.Columns.Add(new DataColumn(SiteShortName, typeof(string)));

            DBAccess.ReadTransaction(
                ref ReadTransaction,
                delegate
            {
                try
                {
                    // Load data
                    string SqlStmt = "SELECT pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
                                     ", pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetUnitNameDBName() +
                                     " FROM " + PUnitTable.GetTableDBName() + ", " + PPartnerTable.GetTableDBName() +
                                     " WHERE ((" + PUnitTable.GetUnitTypeCodeDBName() + " = 'F')" +
                                     "        OR(" + PUnitTable.GetUnitTypeCodeDBName() + " = 'A'))" +
                                     " AND pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
                                     " = pub_" + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
                                     " AND " + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE'";

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

                    DataTable sites = ReadTransaction.DataBaseObj.SelectDT(SqlStmt, "fields", ReadTransaction);

                    foreach (DataRow tempSiteRow in sites.Rows)
                    {
                        PartnerKey = Convert.ToInt64(tempSiteRow[0]);

                        // check if a site is already used in table p_partner_ledger
                        if (PPartnerLedgerAccess.CountViaPUnit(PartnerKey, ReadTransaction) > 0)
                        {
                            SitesRow = UsedSitesTable.NewRow();
                            SitesRow[IsPartnerLedger] = true;
                            SitesRow[SiteKey]         = PartnerKey;
                            SitesRow[SiteShortName]   = Convert.ToString(tempSiteRow[1]);
                            UsedSitesTable.Rows.Add(SitesRow);
                        }
                        else
                        {
                            SitesRow = UnusedSitesTable.NewRow();
                            SitesRow[IsPartnerLedger] = false;
                            SitesRow[SiteKey]         = PartnerKey;
                            SitesRow[SiteShortName]   = Convert.ToString(tempSiteRow[1]);
                            UnusedSitesTable.Rows.Add(SitesRow);
                        }
                    }

                    // first add used sites to table
                    foreach (DataRow tempSiteRow in UsedSitesTable.Rows)
                    {
                        SitesRow = SitesTable.NewRow();
                        SitesRow[IsPartnerLedger] = tempSiteRow[IsPartnerLedger];
                        SitesRow[SiteKey]         = tempSiteRow[SiteKey];
                        SitesRow[SiteShortName]   = tempSiteRow[SiteShortName];
                        SitesTable.Rows.Add(SitesRow);
                    }

                    // and now add unused sites to table
                    foreach (DataRow tempSiteRow in UnusedSitesTable.Rows)
                    {
                        SitesRow = SitesTable.NewRow();
                        SitesRow[IsPartnerLedger] = tempSiteRow[IsPartnerLedger];
                        SitesRow[SiteKey]         = tempSiteRow[SiteKey];
                        SitesRow[SiteShortName]   = tempSiteRow[SiteShortName];
                        SitesTable.Rows.Add(SitesRow);
                    }
                }
                catch (Exception e)
                {
                    TLogging.Log(e.ToString());
                }
            });

            return(SitesTable);
        }
Ejemplo n.º 13
0
        public static bool SaveSiteKeys(List <Int64> ASiteKeysSetUpForUse, List <Int64> ASiteKeysToRemove)
        {
            TDBTransaction      Transaction        = new TDBTransaction();
            bool                SubmissionOK       = true;
            PPartnerLedgerTable PartnerLedgerTable = new PPartnerLedgerTable();
            PPartnerLedgerRow   PartnerLedgerRow;

            // save site keys that can be used in p_partner_ledger

            DBAccess.WriteTransaction(
                ref Transaction, ref SubmissionOK,
                delegate
            {
                // create new records in p_partner_ledger if not there yet
                foreach (Int64 SiteKey in ASiteKeysSetUpForUse)
                {
                    if (PPartnerLedgerAccess.CountViaPUnit(SiteKey, Transaction) == 0)
                    {
                        PartnerLedgerRow            = PartnerLedgerTable.NewRowTyped();
                        PartnerLedgerRow.PartnerKey = SiteKey;

                        // calculate last partner id, from older uses of this ledger number
                        object MaxExistingPartnerKeyObj = Transaction.DataBaseObj.ExecuteScalar(
                            String.Format("SELECT MAX(" + PPartnerTable.GetPartnerKeyDBName() + ") FROM " + PPartnerTable.GetTableDBName() +
                                          " WHERE " + PPartnerTable.GetPartnerKeyDBName() + " > {0} AND " + PPartnerTable.GetPartnerKeyDBName() +
                                          " < {1}",
                                          SiteKey,
                                          SiteKey + 500000), Transaction);

                        if (MaxExistingPartnerKeyObj.GetType() != typeof(DBNull))
                        {
                            // found a partner key for this site already: set it to last used value
                            PartnerLedgerRow.LastPartnerId = Convert.ToInt32(Convert.ToInt64(MaxExistingPartnerKeyObj) - SiteKey);
                        }
                        else
                        {
                            // in this case there was no partner key for this site yet
                            PartnerLedgerRow.LastPartnerId = 0;
                        }

                        PartnerLedgerTable.Rows.Add(PartnerLedgerRow);
                    }
                }

                // delete records from p_partner_ledger that are no longer needed
                foreach (Int64 SiteKey in ASiteKeysToRemove)
                {
                    PPartnerLedgerAccess.DeleteByPrimaryKey(SiteKey, Transaction);
                }

                PPartnerLedgerAccess.SubmitChanges(PartnerLedgerTable, Transaction);
            });

            // make sure SitesList will be refreshed when called next time
            TPartnerCacheableWebConnector.RefreshCacheableTable(TCacheablePartnerTablesEnum.InstalledSitesList);

            return(SubmissionOK);
        }
        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);
        }
        public static PUnitTable GetActiveFieldUnits(string AFieldName)
        {
            PUnitTable UnitTable = new PUnitTable();
            PUnitRow   UnitRow;

            TDBTransaction ReadTransaction;
            Boolean        NewTransaction = false;

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

            TLogging.LogAtLevel(9, "TPartnerDataReaderWebConnector.GetActiveFieldUnits called!");

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

            try
            {
                // Load data
                string SqlStmt = "SELECT pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
                                 ", pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetUnitNameDBName() +
                                 " FROM " + PUnitTable.GetTableDBName() + ", " + PPartnerTable.GetTableDBName() +
                                 " WHERE ((" + PUnitTable.GetOutreachCodeDBName() + " IS NULL)" +
                                 "        OR(" + PUnitTable.GetOutreachCodeDBName() + " = ''))" +
                                 " AND " + PUnitTable.GetUnitTypeCodeDBName() + " <> 'KEY-MIN'" +
                                 " AND pub_" + PUnitTable.GetTableDBName() + "." + PUnitTable.GetPartnerKeyDBName() +
                                 " = pub_" + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() +
                                 " AND " + PPartnerTable.GetStatusCodeDBName() + " = 'ACTIVE'";

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

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

                DataTable events = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "fields", ReadTransaction);

                foreach (DataRow eventRow in events.Rows)
                {
                    UnitRow            = (PUnitRow)UnitTable.NewRow();
                    UnitRow.PartnerKey = Convert.ToInt64(eventRow[0]);
                    UnitRow.UnitName   = Convert.ToString(eventRow[1]);
                    UnitTable.Rows.Add(UnitRow);
                }
            }
            catch (Exception e)
            {
                TLogging.Log(e.ToString());
            }
            finally
            {
                if (NewTransaction)
                {
                    DBAccess.GDBAccessObj.CommitTransaction();
                    TLogging.LogAtLevel(7, "TPartnerDataReaderWebConnector.GetActiveFieldUnits: committed own transaction.");
                }
            }

            return(UnitTable);
        }