Ejemplo n.º 1
0
        private DataTable GetCostCentresLinkedToPartnerListTable(TDBTransaction AReadTransaction, System.Int32 ALedgerNumber, string ATableName)
        {
            DataTable CostCentreTable;

            CostCentreTable            = TCostCentresLinkedToPartner.GetData(ATableName, ALedgerNumber, AReadTransaction);
            CostCentreTable.PrimaryKey = new DataColumn[] {
                CostCentreTable.Columns[AValidLedgerNumberTable.GetLedgerNumberDBName()],
                CostCentreTable.Columns[AValidLedgerNumberTable.GetCostCentreCodeDBName()],
                CostCentreTable.Columns[AValidLedgerNumberTable.GetPartnerKeyDBName()]
            };
            return(CostCentreTable);
        }
Ejemplo n.º 2
0
        /// <summary>
        /// check if the partner has a link to a cost centre (eg. a worker)
        /// </summary>
        /// <param name="APartnerKey"></param>
        /// <param name="ACostCentreCode"></param>
        /// <returns></returns>
        public static Boolean HasPartnerCostCentreLink(Int64 APartnerKey, out String ACostCentreCode)
        {
            Boolean                 ReturnValue;
            TDBTransaction          ReadTransaction;
            Boolean                 NewTransaction;
            StringCollection        RequiredColumns;
            AValidLedgerNumberTable ValidLedgerNumberTable;

            ACostCentreCode = "";

            RequiredColumns = new StringCollection();
            RequiredColumns.Add(AValidLedgerNumberTable.GetCostCentreCodeDBName());

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

            ReadTransaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
                                                             out NewTransaction);
            try
            {
                ValidLedgerNumberTable = AValidLedgerNumberAccess.LoadViaPPartnerPartnerKey(
                    APartnerKey,
                    RequiredColumns,
                    ReadTransaction,
                    null,
                    0,
                    0);

                if (ValidLedgerNumberTable.Rows.Count != 0)
                {
                    ACostCentreCode = ValidLedgerNumberTable[0].CostCentreCode;
                    ReturnValue     = true;
                }
                else
                {
                    ReturnValue = false;
                }
            }
            finally
            {
                if (NewTransaction)
                {
                    ReadTransaction.Commit();
                    TLogging.LogAtLevel(7, "HasPartnerCostCentreLink: committed own transaction.");
                }
            }
            return(ReturnValue);
        }
Ejemplo n.º 3
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));
 }
Ejemplo n.º 4
0
        private static Boolean ExportDonations(Int32 ADaySpan)
        {
            GetGiftBatches(ADaySpan);

            StreamWriter sw1 = File.CreateText(FExportFilePath + "donor.csv");

            sw1.WriteLine("first_name,last_name,partner_key,email,address,telephone,anonymous,class");
            StreamWriter sw2 = File.CreateText(FExportFilePath + "donation.csv");

            sw2.WriteLine("donor,recipient,trans_amount,trans_currency,base_amount,admin_gif,admin_ict,admin_other," +
                          "base_currency,intl_amount,intl_currency,date,id,source_ledger,recipient_field,anonymous,comment1,for1,comment2,for2,comment3,for3");

            foreach (BatchKey Batch in GiftBatches)
            {
                String GiftBatchQuery = "SELECT " +
                                        "PUB_a_gift.p_donor_key_n AS DonorKey, " +
                                        "PUB_a_gift_detail.p_recipient_key_n AS RecipientKey, " +
                                        "PUB_a_gift_detail.a_gift_transaction_amount_n AS TransactionAmount, " +
                                        "PUB_a_gift_batch.a_currency_code_c AS CurrencyCode, " +
                                        "PUB_a_gift_detail.a_gift_amount_n AS GiftAmount, " +
                                        "PUB_a_ledger.a_base_currency_c AS BaseCurrency, " +
                                        "PUB_a_gift_detail.a_gift_amount_intl_n AS IntlAmount, " +
                                        "PUB_a_gift_detail.a_cost_centre_code_c AS CostCentre, " +
                                        "PUB_a_ledger.a_intl_currency_c AS IntlCurrency, " +
                                        "PUB_a_gift_batch.a_gl_effective_date_d AS EffectiveDate, " +
                                        "PUB_a_gift_batch.a_batch_number_i AS BatchNumber, " +
                                        "PUB_a_gift.a_gift_transaction_number_i AS TransactionNumber, " +
                                        "PUB_a_gift_detail.a_detail_number_i AS DetailNumber, " +
                                        "PUB_a_gift_detail.a_confidential_gift_flag_l AS Confidential, " +
                                        "PUB_a_gift_detail.a_gift_comment_one_c AS CommentOne, " +
                                        "PUB_a_gift_detail.a_comment_one_type_c AS CommentOneType, " +
                                        "PUB_a_gift_detail.a_gift_comment_two_c AS CommentTwo, " +
                                        "PUB_a_gift_detail.a_comment_two_type_c AS CommentTwoType, " +
                                        "PUB_a_gift_detail.a_gift_comment_three_c AS CommentThree, " +
                                        "PUB_a_gift_detail.a_comment_three_type_c AS CommentThreeType " +
                                        "FROM PUB_a_gift_batch " +
                                        "LEFT JOIN PUB_a_gift ON PUB_a_gift_batch.a_batch_number_i = PUB_a_gift.a_batch_number_i AND PUB_a_gift_batch.a_ledger_number_i = PUB_a_gift.a_ledger_number_i "
                                        +
                                        "LEFT JOIN PUB_a_gift_detail on PUB_a_gift_detail.a_ledger_number_i = PUB_a_gift.a_ledger_number_i AND PUB_a_gift_detail.a_batch_number_i = PUB_a_gift.a_batch_number_i AND PUB_a_gift_detail.a_gift_transaction_number_i  = PUB_a_gift.a_gift_transaction_number_i "
                                        +
                                        "LEFT JOIN PUB_a_motivation_detail ON PUB_a_motivation_detail.a_ledger_number_i = PUB_a_gift_detail.a_ledger_number_i AND PUB_a_motivation_detail.a_motivation_group_code_c = PUB_a_gift_detail.a_motivation_group_code_c AND PUB_a_motivation_detail.a_motivation_detail_code_c = PUB_a_gift_detail.a_motivation_detail_code_c "
                                        +
                                        "LEFT JOIN PUB_p_partner on PUB_a_gift.p_donor_key_n = PUB_p_partner.p_partner_key_n " +
                                        "LEFT JOIN PUB_a_ledger on PUB_a_gift_batch.a_ledger_number_i = PUB_a_ledger.a_ledger_number_i " +
                                        "WHERE PUB_a_gift_batch.a_ledger_number_i = " + Batch.LedgerNumber + " " +
                                        "AND PUB_a_gift_batch.a_batch_number_i = " + Batch.BatchNumber + " " +
                                        "AND PUB_a_motivation_detail.a_export_to_intranet_l = true " +
                                        "ORDER BY PUB_a_gift.p_donor_key_n";
                DataSet GiftBatchDS = DBAccess.GDBAccessObj.Select(GiftBatchQuery, "GiftBatchTbl", FTransaction);

                foreach (DataRow Row in GiftBatchDS.Tables["GiftBatchTbl"].Rows)
                {
                    Int32 RecipientKey  = Convert.ToInt32(Row["RecipientKey"]);
                    Int32 RecipientFund = Batch.LedgerNumber;

                    // If no RecipientKey was specified, I need to get one:
                    if (RecipientKey == 0)
                    {
                        // If the Gift is on a foreign ledger,
                        // then RecipientKey is the PartnerKey of that ledger
                        ACostCentreTable CostCentreTbl = ACostCentreAccess.LoadByPrimaryKey(Batch.LedgerNumber,
                                                                                            Row["CostCentre"].ToString(), FTransaction);

                        if ((CostCentreTbl.Rows.Count == 1) && (CostCentreTbl[0].CostCentreType == "Foreign"))
                        {
                            AValidLedgerNumberTable ValidLedgerNumberTbl =
                                AValidLedgerNumberAccess.LoadViaACostCentre(Batch.LedgerNumber, Row["CostCentre"].ToString(), FTransaction);

                            if (ValidLedgerNumberTbl.Rows.Count == 1)
                            {
                                RecipientKey  = Convert.ToInt32(ValidLedgerNumberTbl[0].PartnerKey);
                                RecipientFund = RecipientKey;
                            }
                        }
                    }

                    // Otherwise, I can derive a RecipientKey from the Ledger Number.
                    if (RecipientKey == 0)
                    {
                        RecipientKey  = Batch.LedgerNumber * 1000000;
                        RecipientFund = RecipientKey;
                    }

                    GetDonor(Convert.ToInt32(Row["DonorKey"])); // This adds the Donor to my list if it's not already present.
                    GetRecipient(RecipientKey);                 // This adds the recipient to my list if it's not already present.

                    decimal GIFFee;
                    decimal ICTFee;
                    decimal OtherFee;
                    GetAdminFees(Batch.LedgerNumber,
                                 Convert.ToInt32(Row["BatchNumber"]), Convert.ToInt32(Row["TransactionNumber"]), Convert.ToInt32(Row["DetailNumber"]),
                                 out GIFFee, out ICTFee, out OtherFee);
                    sw2.WriteLine(String.Format(
                                      "{0:D10},{1:D10},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},\"{12},{13},{14},{15}\",{16:D10},{17:D10},{18},\"{19}\",\"{20}\",\"{21}\",\"{22}\",\"{23}\",\"{24}\"",
                                      Row["DonorKey"],               //  0
                                      RecipientKey,                  //  1
                                      Row["TransactionAmount"],      //  2
                                      Row["CurrencyCode"],           //  3
                                      Row["GiftAmount"],             //  4
                                      GIFFee,                        //  5
                                      ICTFee,                        //  6
                                      OtherFee,                      //  7
                                      Row["BaseCurrency"],           //  8
                                      Row["IntlAmount"],             //  9
                                      Row["IntlCurrency"],           // 10
                                      PutDate(Row["EffectiveDate"]), // 11
                                      Batch.LedgerNumber,            // 12
                                      Row["BatchNumber"],            // 13
                                      Row["TransactionNumber"],      // 14
                                      Row["DetailNumber"],           // 15
                                      Batch.LedgerNumber * 1000000,  // 16
                                      RecipientFund,                 // 17
                                      Row["Confidential"],           // 18
                                      Row["CommentOne"],             // 19
                                      Row["CommentOneType"],         // 20
                                      Row["CommentTwo"],             // 21
                                      Row["CommentTwoType"],         // 22
                                      Row["CommentThree"],           // 23
                                      Row["CommentThreeType"]        // 24
                                      ));
                }
            }

            foreach (Int64 PartnerKey in DonorList.Keys)
            {
                PartnerDetails Row = DonorList[PartnerKey];
                sw1.WriteLine(String.Format("\"{0}\",\"{1}\",{2:D10},\"{3}\",\"{4}\",{5},{6},{7}",
                                            Row.FirstName, Row.LastName, PartnerKey, Row.Email, Row.Address, Row.Telephone, Row.Anonymous ? "true" : "false", Row.Class));
            }

            sw1.Close();
            sw2.Close();

            StreamWriter sw3 = File.CreateText(FExportFilePath + "recipient.csv");

            sw3.WriteLine("recipient,first_name,last_name,email,class");

            foreach (Int64 PartnerKey in RecipientList.Keys)
            {
                PartnerDetails Row = RecipientList[PartnerKey];
                sw3.WriteLine(String.Format("{0},\"{1}\",\"{2}\",\"{3}\",{4}", PartnerKey, Row.FirstName, Row.LastName, Row.Email, Row.Class));
            }

            sw3.Close();

            return(true);
        }