Example #1
0
        private static void GetLedgerCurrentYear(string ALedgerNumber, out string ALedgerYearCurrentYear)
        {
            // load the file a_ledger.d.gz
            TTable LedgerTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ledger");

            TParseProgressCSV Parser = new TParseProgressCSV(
                TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ledger.d.gz",
                LedgerTableOld.grpTableField.Count);

            StringCollection LedgerColumnNames = GetColumnNames(LedgerTableOld);

            ALedgerYearCurrentYear = "0";

            while (true)
            {
                string[] OldRow = Parser.ReadNextRow();

                if (OldRow == null)
                {
                    break;
                }

                if (GetValue(LedgerColumnNames, OldRow, "a_ledger_number_i") == ALedgerNumber)
                {
                    ALedgerYearCurrentYear = GetValue(LedgerColumnNames, OldRow, "a_current_financial_year_i");
                    break;
                }
            }
        }
        private static string GetSupplierCurrency(Int64 SupplierPartnerKey)
        {
            if (SupplierCurrencies == null)
            {
                SupplierCurrencies = new SortedList <long, string>();

                // read supplier currencies from a_ap_supplier file
                TTable supplierTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ap_supplier");

                TParseProgressCSV Parser = new TParseProgressCSV(
                    TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ap_supplier.d.gz",
                    supplierTableOld.grpTableField.Count);

                StringCollection ColumnNames = GetColumnNames(supplierTableOld);

                while (true)
                {
                    string[] OldRow = Parser.ReadNextRow();

                    if (OldRow == null)
                    {
                        break;
                    }

                    SupplierCurrencies.Add(Convert.ToInt64(GetValue(ColumnNames, OldRow, "p_partner_key_n")),
                                           GetValue(ColumnNames, OldRow, "a_currency_code_c"));
                }
            }

            return(SupplierCurrencies[SupplierPartnerKey]);
        }
        private static void LoadLedgersCurrentFinancialYear()
        {
            LedgersCurrentFinancialYear = new SortedList <string, string>();

            // load a_ledger table and a_accounting_period
            TTable ledgerTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ledger");

            TParseProgressCSV Parser = new TParseProgressCSV(
                TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ledger.d.gz",
                ledgerTableOld.grpTableField.Count);

            StringCollection LedgerColumnNames = GetColumnNames(ledgerTableOld);

            while (true)
            {
                string[] OldRow = Parser.ReadNextRow();

                if (OldRow == null)
                {
                    break;
                }

                string LedgerNumber         = GetValue(LedgerColumnNames, OldRow, "a_ledger_number_i");
                string CurrentFinancialYear = GetValue(LedgerColumnNames, OldRow, "a_current_financial_year_i");
                LedgersCurrentFinancialYear.Add(LedgerNumber, CurrentFinancialYear);
            }

            TTable accountingPeriodTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_accounting_period");

            Parser = new TParseProgressCSV(
                TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_accounting_period.d.gz",
                accountingPeriodTableOld.grpTableField.Count);

            StringCollection PeriodColumnNames = GetColumnNames(accountingPeriodTableOld);

            while (true)
            {
                string[] OldRow = Parser.ReadNextRow();

                if (OldRow == null)
                {
                    break;
                }

                string PeriodNumber = GetValue(PeriodColumnNames, OldRow, "a_accounting_period_number_i");

                if (PeriodNumber == "1")
                {
                    string LedgerNumber = GetValue(PeriodColumnNames, OldRow, "a_ledger_number_i");
                    string RealYear     = GetValue(PeriodColumnNames, OldRow, "a_period_start_date_d").Substring(6, 4);
                    LedgersCurrentFinancialYear[LedgerNumber] += "," + RealYear;
                }
            }
        }
        private static string GetSupplierCurrencyFromPayment(Int32 ALedgerNumber, Int32 APaymentNumber)
        {
            if (CurrencyPerPayment == null)
            {
                SortedList <Int64, string> CurrencyPerDocument = new SortedList <Int64, string>();

                TTable documentTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ap_document");

                TParseProgressCSV Parser = new TParseProgressCSV(
                    TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ap_document.d.gz",
                    documentTableOld.grpTableField.Count);

                StringCollection ColumnNames = GetColumnNames(documentTableOld);

                while (true)
                {
                    string[] OldRow = Parser.ReadNextRow();

                    if (OldRow == null)
                    {
                        break;
                    }

                    Int64 LedgerAndAPNumber = Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ledger_number_i")) * 1000000 +
                                              Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ap_number_i"));

                    CurrencyPerDocument.Add(LedgerAndAPNumber,
                                            GetSupplierCurrency(Convert.ToInt64(GetValue(ColumnNames, OldRow, "p_partner_key_n"))));
                }

                CurrencyPerPayment = new SortedList <long, string>();

                TTable documentpaymentTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_ap_document_payment");

                Parser = new TParseProgressCSV(
                    TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_ap_document_payment.d.gz",
                    documentpaymentTableOld.grpTableField.Count);

                ColumnNames = GetColumnNames(documentpaymentTableOld);

                while (true)
                {
                    string[] OldRow = Parser.ReadNextRow();

                    if (OldRow == null)
                    {
                        break;
                    }

                    Int64 LedgerAndPaymentNumber = Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ledger_number_i")) * 1000000 +
                                                   Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_payment_number_i"));

                    if (!CurrencyPerPayment.ContainsKey(LedgerAndPaymentNumber))
                    {
                        CurrencyPerPayment.Add(LedgerAndPaymentNumber,
                                               CurrencyPerDocument[
                                                   Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ledger_number_i")) * 1000000 +
                                                   Convert.ToInt64(GetValue(ColumnNames, OldRow, "a_ap_number_i"))]);
                    }
                }
            }

            return(CurrencyPerPayment[ALedgerNumber * 1000000 + APaymentNumber]);
        }
Example #5
0
        /// <summary>
        /// Populate the empty table ABudgetRevision using tables ABudget and ABudgetPeriod
        /// </summary>
        public static int PopulateABudgetRevision(StringCollection AColumnNames,
                                                  ref string[] ANewRow,
                                                  StreamWriter AWriter,
                                                  StreamWriter AWriterTest)
        {
            // in Petra 2.x, there never has been a record in this table.
            // so if there is a budget, we need to create a revision 0 for each year

            // load the file a_budget.d.gz so that we can access the values for each budget
            TTable BudgetTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_budget");

            TParseProgressCSV BudgetParser = new TParseProgressCSV(
                TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_budget.d.gz",
                BudgetTableOld.grpTableField.Count);

            StringCollection BudgetColumnNames = GetColumnNames(BudgetTableOld);

            // load the file a_budget_period.d.gz
            TTable BudgetPeriodTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_budget_period");

            TParseProgressCSV BudgetPeriodParser = new TParseProgressCSV(
                TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_budget_period.d.gz",
                BudgetPeriodTableOld.grpTableField.Count);

            StringCollection BudgetPeriodColumnNames = GetColumnNames(BudgetPeriodTableOld);

            // read first row in ABudgetPeriod
            string[] OldBudgetPeriodRow = BudgetPeriodParser.ReadNextRow();

            // [0] last year, [1] current year, [2] next year. Null if budget does not exist for a year.
            string[] BudgetYears = new string[3];

            List <string[]> BudgetPeriodRows = new List <string[]>();
            List <string>   Revisions        = new List <string>();
            string          LedgerNumber     = string.Empty;
            int             RowCounter       = 0;

            SetValue(AColumnNames, ref ANewRow, "a_revision_i", "0");
            SetValue(AColumnNames, ref ANewRow, "a_description_c", "default");
            SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N");
            SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N");
            SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N");
            SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N");
            SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N");

            // one a_budget_period record for each year of each ledger
            while (true)
            {
                BudgetYears[0] = null;
                BudgetYears[2] = null;
                string[] OldRow = BudgetParser.ReadNextRow();

                if (OldRow == null)
                {
                    break;
                }

                if (LedgerNumber != GetValue(BudgetColumnNames, OldRow, "a_ledger_number_i"))
                {
                    LedgerNumber = GetValue(BudgetColumnNames, OldRow, "a_ledger_number_i");

                    // gets the current financial year for the current ledger
                    GetLedgerCurrentYear(LedgerNumber, out BudgetYears[1]);
                }

                // Only three possible years for each ledger. Hence only three a_budget_revision records per ledger.
                // Move on to next ledger if data for all three years is found.
                if (!Revisions.Contains(LedgerNumber + "_" + (Convert.ToInt32(BudgetYears[1]) - 1).ToString()) &&
                    !Revisions.Contains(LedgerNumber + "_" + (Convert.ToInt32(BudgetYears[1])).ToString()) &&
                    !Revisions.Contains(LedgerNumber + "_" + (Convert.ToInt32(BudgetYears[1]) + 1).ToString()))
                {
                    GetBudgetPeriods(BudgetPeriodParser, BudgetPeriodColumnNames, ref OldBudgetPeriodRow,
                                     GetValue(BudgetColumnNames, OldRow, "a_budget_sequence_i"), out BudgetPeriodRows);
                    GetBudgetYears(BudgetPeriodColumnNames, BudgetPeriodRows, ref BudgetYears);

                    foreach (string BudgetYear in BudgetYears)
                    {
                        if ((BudgetYear != null) && !Revisions.Contains(LedgerNumber + "_" + BudgetYear))
                        {
                            SetValue(AColumnNames, ref ANewRow, "a_ledger_number_i", LedgerNumber);
                            SetValue(AColumnNames, ref ANewRow, "a_year_i", BudgetYear);

                            AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString());

                            if (AWriterTest != null)
                            {
                                AWriterTest.WriteLine("BEGIN; " + "COPY a_budget_revision FROM stdin;");
                                AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString());
                                AWriterTest.WriteLine("\\.");
                                AWriterTest.WriteLine("ROLLBACK;");
                            }

                            RowCounter++;

                            Revisions.Add(LedgerNumber + "_" + BudgetYear);
                        }
                    }
                }
            }

            return(RowCounter);
        }
Example #6
0
        /// <summary>
        /// Fix table a_budget_period
        /// </summary>
        /// <remarks>
        /// In Petra, one ABudgetPeriod could cover three years. These must be split up for OP with one record for each year.
        /// </remarks>
        public static int FixABudgetPeriod(StringCollection AColumnNames,
                                           ref string[] ANewRow,
                                           StreamWriter AWriter,
                                           StreamWriter AWriterTest)
        {
            // load the file a_budget.d.gz so that we can access the values for each budget
            TTable BudgetTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_budget");

            TParseProgressCSV BudgetParser = new TParseProgressCSV(
                TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_budget.d.gz",
                BudgetTableOld.grpTableField.Count);

            StringCollection BudgetColumnNames = GetColumnNames(BudgetTableOld);

            // load the file a_budget_period.d.gz
            TTable BudgetPeriodTableOld = TDumpProgressToPostgresql.GetStoreOld().GetTable("a_budget_period");

            TParseProgressCSV BudgetPeriodParser = new TParseProgressCSV(
                TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "a_budget_period.d.gz",
                BudgetPeriodTableOld.grpTableField.Count);

            StringCollection BudgetPeriodColumnNames = GetColumnNames(BudgetPeriodTableOld);

            // read first row in ABudgetPeriod
            string[] OldBudgetPeriodRow = BudgetPeriodParser.ReadNextRow();

            // [0] last year, [1] current year, [2] next year. Null if budget does not exist for a year.
            string[] BudgetYears = new string[3];

            List <string[]> BudgetPeriodRows = new List <string[]>();
            string          LedgerNumber     = string.Empty;
            int             RowCounter       = 0;
            int             SequenceNumber   = 0;

            SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N");
            SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N");
            SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N");
            SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N");
            SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N");

            while (true)
            {
                BudgetYears[0] = null;
                BudgetYears[2] = null;
                string[] OldRow = BudgetParser.ReadNextRow();

                if (OldRow == null)
                {
                    break;
                }

                if (LedgerNumber != GetValue(BudgetColumnNames, OldRow, "a_ledger_number_i"))
                {
                    LedgerNumber = GetValue(BudgetColumnNames, OldRow, "a_ledger_number_i");

                    // gets the current financial year for the current ledger
                    GetLedgerCurrentYear(LedgerNumber, out BudgetYears[1]);
                }

                GetBudgetPeriods(BudgetPeriodParser, BudgetPeriodColumnNames, ref OldBudgetPeriodRow,
                                 GetValue(BudgetColumnNames, OldRow, "a_budget_sequence_i"), out BudgetPeriodRows);
                GetBudgetYears(BudgetPeriodColumnNames, BudgetPeriodRows, ref BudgetYears);

                for (int i = 0; i < 3; i++)
                {
                    if (BudgetYears[i] != null)
                    {
                        SequenceNumber++;

                        foreach (string[] BudgetPeriodRow in BudgetPeriodRows)
                        {
                            RowCounter++;

                            SetValue(AColumnNames, ref ANewRow, "a_budget_sequence_i", SequenceNumber.ToString());
                            SetValue(AColumnNames, ref ANewRow, "a_period_number_i",
                                     GetValue(BudgetPeriodColumnNames, BudgetPeriodRow, "a_period_number_i"));

                            if (i == 0)
                            {
                                SetValue(AColumnNames, ref ANewRow, "a_budget_base_n",
                                         GetValue(BudgetPeriodColumnNames, BudgetPeriodRow, "a_budget_last_year_n"));
                            }
                            else if (i == 1)
                            {
                                SetValue(AColumnNames, ref ANewRow, "a_budget_base_n",
                                         GetValue(BudgetPeriodColumnNames, BudgetPeriodRow, "a_budget_this_year_n"));
                            }
                            else if (i == 2)
                            {
                                SetValue(AColumnNames, ref ANewRow, "a_budget_base_n",
                                         GetValue(BudgetPeriodColumnNames, BudgetPeriodRow, "a_budget_next_year_n"));
                            }

                            AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString());

                            if (AWriterTest != null)
                            {
                                AWriterTest.WriteLine("BEGIN; " + "COPY a_budget_period FROM stdin;");
                                AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString());
                                AWriterTest.WriteLine("\\.");
                                AWriterTest.WriteLine("ROLLBACK;");
                            }
                        }
                    }
                }
            }

            return(RowCounter);
        }
Example #7
0
        /// <summary>
        /// Populate the empty table PPartnerGiftDestination using PmStaffData
        /// </summary>
        public static int PopulatePContactLog(StringCollection AColumnNames,
                                              ref string[] ANewRow,
                                              StreamWriter AWriter,
                                              StreamWriter AWriterTest)
        {
            // load the file p_partner_contact.d.gz
            TTable PartnerContact = TDumpProgressToPostgresql.GetStoreOld().GetTable("p_partner_contact");

            TParseProgressCSV ParserPartnerContact = new TParseProgressCSV(
                TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "p_partner_contact.d.gz",
                PartnerContact.grpTableField.Count);


            TTable           ContactLog            = TDumpProgressToPostgresql.GetStoreNew().GetTable("p_contact_log");
            StringCollection ContactLogColumnNames = GetColumnNames(ContactLog);

            string[] ContactLogRow = new string[ContactLogColumnNames.Count];
            int      RowCounter    = 0;

            string[] OldRow = ParserPartnerContact.ReadNextRow();

            while (OldRow != null)
            {
                //string ContactLogKey = TSequenceWriter.GetNextSequenceValue("seq_contact").ToString();

                for (int i = 0; i < OldRow.Length; i++)
                {
                    OldRow[i] = string.IsNullOrWhiteSpace(OldRow[i]) ? "\\N" : OldRow[i];
                }

                // p_contact_log row
                SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_log_id_i", OldRow[0]); //ContactLogKey);
                SetValue(ContactLogColumnNames, ref ContactLogRow, "s_contact_date_d", OldRow[2]);
                SetValue(ContactLogColumnNames, ref ContactLogRow, "s_contact_time_i", OldRow[3]);

                if ((OldRow[4].Length == 0) || (OldRow[4] == "\\N"))
                {
                    SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_code_c", "UNKNOWN");
                }
                else
                {
                    SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_code_c", OldRow[4].ToUpper());
                }

                SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contactor_c", OldRow[5]);
                SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_message_id_c", OldRow[6]);
                SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_comment_c", OldRow[7]);
                SetValue(ContactLogColumnNames, ref ContactLogRow, "s_module_id_c", OldRow[8]);
                SetValue(ContactLogColumnNames, ref ContactLogRow, "s_user_id_c", OldRow[9].ToUpper());
                SetValue(ContactLogColumnNames, ref ContactLogRow, "p_mailing_code_c", OldRow[10].ToUpper());
                SetValue(ContactLogColumnNames, ref ContactLogRow, "p_restricted_l", OldRow[11]);
                SetValue(ContactLogColumnNames, ref ContactLogRow, "p_contact_location_c", OldRow[12]);
                SetValue(ContactLogColumnNames, ref ContactLogRow, "s_date_created_d", OldRow[13]);
                SetValue(ContactLogColumnNames, ref ContactLogRow, "s_created_by_c", OldRow[14].ToUpper());
                SetValue(ContactLogColumnNames, ref ContactLogRow, "s_date_modified_d", OldRow[15]);
                SetValue(ContactLogColumnNames, ref ContactLogRow, "s_modified_by_c", OldRow[16].ToUpper());
                SetValue(ContactLogColumnNames, ref ContactLogRow, "s_modification_id_t", OldRow[17]);

                // p_partner_contact row
                SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", OldRow[1]);
                SetValue(AColumnNames, ref ANewRow, "p_contact_log_id_i", OldRow[0]); //ContactLogKey);

                // write test records
                if (AWriterTest != null)
                {
                    AWriterTest.WriteLine("BEGIN; " + "COPY p_contact_log FROM stdin;");
                    AWriterTest.WriteLine(StringHelper.StrMerge(ContactLogRow, '\t').Replace("\\\\N", "\\N").ToString());
                    AWriterTest.WriteLine("\\.");
                    AWriterTest.WriteLine("ROLLBACK;");
                }

                if (AWriterTest != null)
                {
                    AWriterTest.WriteLine("BEGIN; " + "COPY p_partner_contact FROM stdin;");
                    AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString());
                    AWriterTest.WriteLine("\\.");
                    AWriterTest.WriteLine("ROLLBACK;");
                }

                OldRow = ParserPartnerContact.ReadNextRow();

                AWriter.WriteLine("COPY p_contact_log FROM stdin;");
                AWriter.WriteLine(StringHelper.StrMerge(ContactLogRow, '\t').Replace("\\\\N", "\\N").ToString());
                AWriter.WriteLine("\\.");
                AWriter.WriteLine("COPY p_partner_contact FROM stdin;");
                AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString());

                if (OldRow != null)
                {
                    AWriter.WriteLine("\\.");
                }

                RowCounter += 2; // ContactLog record and PartnerContact record
            }

            return(RowCounter);
        }
Example #8
0
        /// <summary>
        /// main function
        /// </summary>
        /// <param name="args"></param>
        public static void Main(string[] args)
        {
            new TAppSettingsManager(false);
            new TLogging("Ict.Tools.DataDumpPetra2.log");

            if (!TAppSettingsManager.HasValue("debuglevel"))
            {
                Console.Error.WriteLine("dumps one single table or all tables from Progress Petra 2.3 into Postgresql SQL load format");
                Console.Error.WriteLine(
                    "usage: Ict.Tools.DataDumpPetra2 -debuglevel:<0..10> -table:<single table or all> -oldpetraxml:<path and filename of old petra.xml> -newpetraxml:<path and filename of petra.xml>");
                Console.Error.WriteLine("will default to processing all tables, and using petra23.xml and petra.xml from the current directory");
                Console.Error.WriteLine(
                    "usage for creating fulldump23.p: Ict.Tools.DataDumpPetra2 -operation:createProgressCode");
                Console.Error.WriteLine("");
            }

            try
            {
                TLogging.DebugLevel = TAppSettingsManager.GetInt16("debuglevel", 0);

                if (TAppSettingsManager.GetValue("operation", false) == "createProgressCode")
                {
                    TCreateFulldumpProgressCode createProgressCode = new TCreateFulldumpProgressCode();
                    createProgressCode.GenerateFulldumpCode();
                    return;
                }

                if (TAppSettingsManager.GetValue("clean", "false") == "true")
                {
                    TLogging.Log("deleting all resulting files...");

                    // delete sql.gz files, also _*.txt
                    string[] FilesToDelete = Directory.GetFiles(TAppSettingsManager.GetValue("fulldumpPath", "fulldump"), "*.sql.gz");

                    foreach (string file in FilesToDelete)
                    {
                        File.Delete(file);
                    }

                    FilesToDelete = Directory.GetFiles(TAppSettingsManager.GetValue("fulldumpPath", "fulldump"), "_*.txt");

                    foreach (string file in FilesToDelete)
                    {
                        File.Delete(file);
                    }
                }

                StringCollection tables = StringHelper.StrSplit(TAppSettingsManager.GetValue("table", ""), ",");

                // the upgrade process is split into two steps, to make testing quicker

                // Step 1: dump from Progress Petra 2.3 to CSV files, write gz files to keep size of fulldump small
                // this takes about 7 minutes for the german database
                // use the generated fulldump23.p
                if ((TAppSettingsManager.GetValue("operation", "dump23") == "dump23") && File.Exists("fulldump23.r"))
                {
                    TDumpProgressToPostgresql dumper = new TDumpProgressToPostgresql();

                    if (tables.Count == 0)
                    {
                        dumper.DumpTablesToCSV(String.Empty);
                    }
                    else
                    {
                        foreach (var ProcessTable in tables)
                        {
                            dumper.DumpTablesToCSV(ProcessTable);
                        }
                    }
                }

                // Step 2: produce one or several sql load files for PostgreSQL
                // can be called independant from first step: for all tables or just one table
                // for tables merged into one: append to previous file
                // this takes 50 minutes on my virtual machine on the german server for all tables. on a faster machine, it is only 25 minutes
                if (TAppSettingsManager.GetValue("operation", "load30") == "load30")
                {
                    TDumpProgressToPostgresql dumper = new TDumpProgressToPostgresql();

                    if (tables.Count == 0)
                    {
                        dumper.LoadTablesToPostgresql(String.Empty);
                    }
                    else
                    {
                        foreach (var ProcessTable in tables)
                        {
                            dumper.LoadTablesToPostgresql(ProcessTable);
                        }
                    }
                }

                // Step 3: concatenate all existing sql.gz files into one load sql file, gzipped. in the correct order
                if (TAppSettingsManager.GetValue("operation", "createSQL") == "createSQL")
                {
                    TDumpProgressToPostgresql dumper = new TDumpProgressToPostgresql();

                    if (tables.Count == 0)
                    {
                        dumper.CreateNewSQLFile(String.Empty);
                    }
                    else
                    {
                        foreach (var ProcessTable in tables)
                        {
                            dumper.CreateNewSQLFile(ProcessTable);
                        }
                    }
                }

                // TODO: also anonymize the names of the partners (use random names from external list of names)? what about amounts?
            }
            catch (Exception e)
            {
                TLogging.Log(e.Message);

                if (e.InnerException != null)
                {
                    TLogging.Log(e.InnerException.Message);
                }

                TLogging.Log(e.StackTrace);
            }
        }
Example #9
0
        /// <summary>
        /// Populate the empty table PPartnerGiftDestination using PmStaffData
        /// </summary>
        public static int PopulatePPartnerGiftDestination(StringCollection AColumnNames,
                                                          ref string[] ANewRow,
                                                          StreamWriter AWriter,
                                                          StreamWriter AWriterTest)
        {
            List <string[]> ActiveCommitments = new List <string[]>();
            List <string[]> Persons           = new List <string[]>();
            int             RowCounter        = 0;

            // default for all new records
            SetValue(AColumnNames, ref ANewRow, "p_active_l", "\\N");
            SetValue(AColumnNames, ref ANewRow, "p_default_gift_destination_l", "\\N");
            SetValue(AColumnNames, ref ANewRow, "p_partner_class_c", "\\N");
            SetValue(AColumnNames, ref ANewRow, "p_commitment_site_key_n", "\\N");
            SetValue(AColumnNames, ref ANewRow, "p_commitment_key_n", "\\N");
            SetValue(AColumnNames, ref ANewRow, "p_comment_c", "\\N");
            SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N");
            SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N");
            SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N");
            SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N");
            SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N");

            // load the file pm_staff_data.d.gz
            TTable StaffDataTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("pm_staff_data");

            TParseProgressCSV StaffDataParser = new TParseProgressCSV(
                TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "pm_staff_data.d.gz",
                StaffDataTable.grpTableField.Count);

            StringCollection StaffDataColumnNames = GetColumnNames(StaffDataTable);

            // find which records are currently active
            while (true)
            {
                string[] StaffDataRow = StaffDataParser.ReadNextRow();

                if (StaffDataRow == null)
                {
                    break;
                }

                string strStartOfCommitment = GetValue(StaffDataColumnNames, StaffDataRow, "pm_start_of_commitment_d");
                string strEndOfCommitment   = GetValue(StaffDataColumnNames, StaffDataRow, "pm_end_of_commitment_d");

                try
                {
                    // if commitment is currently active
                    if ((DateTime.ParseExact(strStartOfCommitment, "dd/mm/yyyy", CultureInfo.InvariantCulture) <= DateTime.Today) &&
                        ((strEndOfCommitment == "\\N") ||
                         (DateTime.ParseExact(strEndOfCommitment, "dd/mm/yyyy", CultureInfo.InvariantCulture) >= DateTime.Today)) &&
                        (strStartOfCommitment != strEndOfCommitment))
                    {
                        ActiveCommitments.Add(StaffDataRow);
                    }
                }
                catch
                {
                    TLogging.Log("WARNING: Invalid date in commitment: " +
                                 strStartOfCommitment + " or " + strEndOfCommitment);
                }
            }

            // load the file p_person.d.gz
            TTable PersonTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("p_person");

            TParseProgressCSV PersonParser = new TParseProgressCSV(
                TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "p_person.d.gz",
                PersonTable.grpTableField.Count);

            StringCollection PersonColumnNames = GetColumnNames(PersonTable);
            SortedList <string, List <PersonKeyAndRow> > FamilyKeysWithPersons =
                new SortedList <string, List <PersonKeyAndRow> >();

            // add all Persons to a list
            while (true)
            {
                string[] PersonRow = PersonParser.ReadNextRow();

                if (PersonRow == null)
                {
                    break;
                }

                string familyKey = GetValue(PersonColumnNames, PersonRow, "p_family_key_n");

                if (!FamilyKeysWithPersons.ContainsKey(familyKey))
                {
                    FamilyKeysWithPersons.Add(familyKey, new List <PersonKeyAndRow>());
                }

                FamilyKeysWithPersons[familyKey].Add(
                    new PersonKeyAndRow(
                        GetValue(PersonColumnNames, PersonRow, "p_partner_key_n"), PersonRow));

                Persons.Add(PersonRow);
            }

            // load the file p_family.d.gz
            TTable FamilyTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("p_family");

            TParseProgressCSV FamilyParser = new TParseProgressCSV(
                TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "p_family.d.gz",
                FamilyTable.grpTableField.Count);

            StringCollection FamilyColumnNames = GetColumnNames(FamilyTable);

            // read through each family
            while (true)
            {
                string[] FamilyRow = FamilyParser.ReadNextRow();

                if (FamilyRow == null)
                {
                    break;
                }

                string familykey = GetValue(FamilyColumnNames, FamilyRow, "p_partner_key_n");

                // find Person partners belonging to the family
                bool CommitmentFound = false;
                int  MinimumFamilyId = int.MaxValue;

                // if family contains Persons
                if (FamilyKeysWithPersons.ContainsKey(familykey))
                {
                    // read through each of the Family's Persons
                    foreach (PersonKeyAndRow PersonRecord in FamilyKeysWithPersons[familykey])
                    {
                        // find if the Person has a currently active commitment
                        string[] Commitment = ActiveCommitments.Find(e => GetValue(StaffDataColumnNames, e, "p_partner_key_n") ==
                                                                     PersonRecord.PersonKey);

                        // if currently active commitment exists create a new Gift Destination record
                        if (Commitment != null)
                        {
                            int CurrentFamilyId = Convert.ToInt32(GetValue(PersonColumnNames, PersonRecord.PersonRow, "p_old_omss_family_id_i"));

                            if (CurrentFamilyId < MinimumFamilyId)
                            {
                                SetValue(AColumnNames, ref ANewRow, "p_key_i", RowCounter.ToString());
                                SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", GetValue(FamilyColumnNames, FamilyRow, "p_partner_key_n"));
                                SetValue(AColumnNames, ref ANewRow, "p_field_key_n", GetValue(StaffDataColumnNames, Commitment, "pm_target_field_n"));
                                SetValue(AColumnNames, ref ANewRow, "p_comment_c", "\\N");

                                TTableField tf = new TTableField();
                                tf.strName = "pm_start_of_commitment_d";
                                tf.strType = "DATE";

                                SetValue(AColumnNames, ref ANewRow, "p_date_effective_d",
                                         TFixData.FixValue(GetValue(StaffDataColumnNames, Commitment, "pm_start_of_commitment_d"), tf));
                                tf.strName = "pm_end_of_commitment_d";
                                SetValue(AColumnNames, ref ANewRow, "p_date_expires_d",
                                         TFixData.FixValue(GetValue(StaffDataColumnNames, Commitment, "pm_end_of_commitment_d"), tf));

                                CommitmentFound = true;

                                MinimumFamilyId = CurrentFamilyId;

                                // there can only be one active gift destination per family
                                break;
                            }
                        }
                    }
                }

                // if no active commitment is found then search for a "p_om_field_key_n" and use that to create a gift destination
                if (!CommitmentFound)
                {
                    string OMFieldKey = GetValue(FamilyColumnNames, FamilyRow, "p_om_field_key_n");

                    if ((OMFieldKey != "\\N") && (OMFieldKey != "0"))
                    {
                        SetValue(AColumnNames, ref ANewRow, "p_key_i", RowCounter.ToString());
                        SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", GetValue(FamilyColumnNames, FamilyRow, "p_partner_key_n"));
                        SetValue(AColumnNames, ref ANewRow, "p_field_key_n", GetValue(FamilyColumnNames, FamilyRow, "p_om_field_key_n"));
                        DateTime LastYear = DateTime.Today.AddYears(-1);
                        SetValue(AColumnNames, ref ANewRow, "p_date_effective_d",
                                 string.Format("{0}-{1}-{2}", LastYear.Year, LastYear.Month, LastYear.Day));
                        SetValue(AColumnNames, ref ANewRow, "p_date_expires_d", "\\N");
                        SetValue(AColumnNames, ref ANewRow, "p_comment_c", Catalog.GetString("Copied from Petra's OM Field Key."));

                        CommitmentFound = true;
                    }
                }

                // write gift destination to file
                if (CommitmentFound)
                {
                    AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString());

                    if (AWriterTest != null)
                    {
                        AWriterTest.WriteLine("BEGIN; " + "COPY p_partner_gift_destination FROM stdin;");
                        AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString());
                        AWriterTest.WriteLine("\\.");
                        AWriterTest.WriteLine("ROLLBACK;");
                    }

                    RowCounter++;
                }
            }

            return(RowCounter);
        }